· 10 years ago · Aug 30, 2016, 12:14 AM
1CREATE TABLE IF NOT EXISTS `user` (
2 `id` bigint COMMENT 'Unique user identifier',
3 `first_name` CHAR(255) NOT NULL DEFAULT '' COMMENT 'User first name',
4 `last_name` CHAR(255) DEFAULT NULL COMMENT 'User last name',
5 `username` CHAR(255) DEFAULT NULL COMMENT 'User username',
6 `created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
7 `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date update',
8 PRIMARY KEY (`id`),
9 KEY `username` (`username`)
10) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
11
12CREATE TABLE IF NOT EXISTS `chat` (
13 `id` bigint COMMENT 'Unique user or chat identifier',
14 `type` ENUM('private', 'group', 'supergroup', 'channel') NOT NULL COMMENT 'chat type private, group, supergroup or channel',
15 `title` CHAR(255) DEFAULT '' COMMENT 'chat title null if case of single chat with the bot',
16 `created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
17 `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date update',
18 `old_id` bigint DEFAULT NULL COMMENT 'Unique chat identifieri this is filled when a chat is converted to a superchat',
19 PRIMARY KEY (`id`),
20 KEY `old_id` (`old_id`)
21) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
22
23CREATE TABLE IF NOT EXISTS `user_chat` (
24 `user_id` bigint COMMENT 'Unique user identifier',
25 `chat_id` bigint COMMENT 'Unique user or chat identifier',
26 PRIMARY KEY (`user_id`, `chat_id`),
27 FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
28 ON DELETE CASCADE ON UPDATE CASCADE,
29 FOREIGN KEY (`chat_id`) REFERENCES `chat` (`id`)
30 ON DELETE CASCADE ON UPDATE CASCADE
31) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
32
33CREATE TABLE IF NOT EXISTS `inline_query` (
34 `id` bigint UNSIGNED COMMENT 'Unique identifier for this query.',
35 `user_id` bigint NULL COMMENT 'Sender',
36 `location` CHAR(255) NULL DEFAULT NULL COMMENT 'Location of the sender',
37 `query` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Text of the query',
38 `offset` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Offset of the result',
39 `created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
40 PRIMARY KEY (`id`),
41 KEY `user_id` (`user_id`),
42
43 FOREIGN KEY (`user_id`)
44 REFERENCES `user` (`id`)
45
46) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
47
48CREATE TABLE IF NOT EXISTS `chosen_inline_query` (
49 `id` bigint UNSIGNED AUTO_INCREMENT COMMENT 'Unique identifier for chosen query.',
50 `result_id` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Id of the chosen result',
51 `user_id` bigint NULL COMMENT 'Sender',
52 `location` CHAR(255) NULL DEFAULT NULL COMMENT 'Location object, senders\'s location.',
53 `inline_message_id` CHAR(255) NULL DEFAULT NULL COMMENT 'Identifier of the message sent via the bot in inline mode, that originated the query',
54 `query` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Text of the query',
55 `created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
56 PRIMARY KEY (`id`),
57 KEY `user_id` (`user_id`),
58
59 FOREIGN KEY (`user_id`)
60 REFERENCES `user` (`id`)
61
62) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
63
64CREATE TABLE IF NOT EXISTS `callback_query` (
65 `id` bigint UNSIGNED COMMENT 'Unique identifier for this query.',
66 `user_id` bigint NULL COMMENT 'Sender',
67 `message` text NULL DEFAULT NULL COMMENT 'Message',
68 `inline_message_id` CHAR(255) NULL DEFAULT NULL COMMENT 'Identifier of the message sent via the bot in inline mode, that originated the query',
69 `data` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Data associated with the callback button.',
70 `created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
71 PRIMARY KEY (`id`),
72 KEY `user_id` (`user_id`),
73
74 FOREIGN KEY (`user_id`)
75 REFERENCES `user` (`id`)
76
77) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
78
79CREATE TABLE IF NOT EXISTS `message` (
80 `chat_id` bigint COMMENT 'Chat identifier.',
81 `id` bigint UNSIGNED COMMENT 'Unique message identifier',
82 `user_id` bigint NULL COMMENT 'User identifier',
83 `date` timestamp NULL DEFAULT NULL COMMENT 'Date the message was sent in timestamp format',
84 `forward_from` bigint NULL DEFAULT NULL COMMENT 'User id. For forwarded messages, sender of the original message',
85 `forward_from_chat` bigint NULL DEFAULT NULL COMMENT 'Chat id. For forwarded messages from channel',
86 `forward_date` timestamp NULL DEFAULT NULL COMMENT 'For forwarded messages, date the original message was sent in Unix time',
87 `reply_to_chat` bigint NULL DEFAULT NULL COMMENT 'Chat identifier.',
88 `reply_to_message` bigint UNSIGNED DEFAULT NULL COMMENT 'Message is a reply to another message.',
89 `text` TEXT DEFAULT NULL COMMENT 'For text messages, the actual UTF-8 text of the message max message length 4096 char utf8',
90 `entities` TEXT DEFAULT NULL COMMENT 'For text messages, special entities like usernames, URLs, bot commands, etc. that appear in the text',
91 `audio` TEXT DEFAULT NULL COMMENT 'Audio object. Message is an audio file, information about the file',
92 `document` TEXT DEFAULT NULL COMMENT 'Document object. Message is a general file, information about the file',
93 `photo` TEXT DEFAULT NULL COMMENT 'Array of PhotoSize objects. Message is a photo, available sizes of the photo',
94 `sticker` TEXT DEFAULT NULL COMMENT 'Sticker object. Message is a sticker, information about the sticker',
95 `video` TEXT DEFAULT NULL COMMENT 'Video object. Message is a video, information about the video',
96 `voice` TEXT DEFAULT NULL COMMENT 'Voice Object. Message is a Voice, information about the Voice',
97 `caption` TEXT DEFAULT NULL COMMENT 'For message with caption, the actual UTF-8 text of the caption',
98 `contact` TEXT DEFAULT NULL COMMENT 'Contact object. Message is a shared contact, information about the contact',
99 `location` TEXT DEFAULT NULL COMMENT 'Location object. Message is a shared location, information about the location',
100 `venue` TEXT DEFAULT NULL COMMENT 'Venue object. Message is a Venue, information about the Venue',
101 `new_chat_member` bigint NULL DEFAULT NULL COMMENT 'User id. A new member was added to the group, information about them (this member may be bot itself)',
102 `left_chat_member` bigint NULL DEFAULT NULL COMMENT 'User id. A member was removed from the group, information about them (this member may be bot itself)',
103 `new_chat_title` CHAR(255) DEFAULT NULL COMMENT 'A group title was changed to this value',
104 `new_chat_photo` TEXT DEFAULT NULL COMMENT 'Array of PhotoSize objects. A group photo was change to this value',
105 `delete_chat_photo` tinyint(1) DEFAULT 0 COMMENT 'Informs that the group photo was deleted',
106 `group_chat_created` tinyint(1) DEFAULT 0 COMMENT 'Informs that the group has been created',
107 `supergroup_chat_created` tinyint(1) DEFAULT 0 COMMENT 'Informs that the supergroup has been created',
108 `channel_chat_created` tinyint(1) DEFAULT 0 COMMENT 'Informs that the channel chat has been created',
109 `migrate_from_chat_id` bigint NULL DEFAULT NULL COMMENT 'Migrate from chat identifier.',
110 `migrate_to_chat_id` bigint NULL DEFAULT NULL COMMENT 'Migrate to chat identifier.',
111 `pinned_message` TEXT NULL DEFAULT NULL COMMENT 'Pinned message, Message object.',
112 PRIMARY KEY (`chat_id`, `id`),
113 KEY `user_id` (`user_id`),
114 KEY `forward_from` (`forward_from`),
115 KEY `forward_from_chat` (`forward_from_chat`),
116 KEY `reply_to_chat` (`reply_to_chat`),
117 KEY `reply_to_message` (`reply_to_message`),
118 KEY `new_chat_member` (`new_chat_member`),
119 KEY `left_chat_member` (`left_chat_member`),
120 KEY `migrate_from_chat_id` (`migrate_from_chat_id`),
121 KEY `migrate_to_chat_id` (`migrate_to_chat_id`),
122
123 FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
124 FOREIGN KEY (`chat_id`) REFERENCES `chat` (`id`),
125 FOREIGN KEY (`forward_from`) REFERENCES `user` (`id`),
126 FOREIGN KEY (`forward_from_chat`) REFERENCES `chat` (`id`),
127 FOREIGN KEY (`reply_to_chat`, `reply_to_message`) REFERENCES `message` (`chat_id`,`id`),
128 FOREIGN KEY (`forward_from`) REFERENCES `user` (`id`),
129 FOREIGN KEY (`new_chat_member`) REFERENCES `user` (`id`),
130 FOREIGN KEY (`left_chat_member`) REFERENCES `user` (`id`)
131
132) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
133
134CREATE TABLE IF NOT EXISTS `telegram_update` (
135 `id` bigint UNSIGNED COMMENT 'The update\'s unique identifier.',
136 `chat_id` bigint NULL DEFAULT NULL COMMENT 'Chat identifier.',
137 `message_id` bigint UNSIGNED DEFAULT NULL COMMENT 'Unique message identifier',
138 `inline_query_id` bigint UNSIGNED DEFAULT NULL COMMENT 'The inline query unique identifier.',
139 `chosen_inline_query_id` bigint UNSIGNED DEFAULT NULL COMMENT 'The chosen query unique identifier.',
140 `callback_query_id` bigint UNSIGNED DEFAULT NULL COMMENT 'The callback query unique identifier.',
141
142 PRIMARY KEY (`id`),
143 KEY `message_id` (`chat_id`, `message_id`),
144 KEY `inline_query_id` (`inline_query_id`),
145 KEY `chosen_inline_query_id` (`chosen_inline_query_id`),
146 KEY `callback_query_id` (`callback_query_id`),
147
148 FOREIGN KEY (`chat_id`, `message_id`) REFERENCES `message` (`chat_id`,`id`),
149 FOREIGN KEY (`inline_query_id`) REFERENCES `inline_query` (`id`),
150 FOREIGN KEY (`chosen_inline_query_id`) REFERENCES `chosen_inline_query` (`id`),
151 FOREIGN KEY (`callback_query_id`) REFERENCES `callback_query` (`id`)
152) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
153
154CREATE TABLE IF NOT EXISTS `conversation` (
155 `id` bigint(20) unsigned AUTO_INCREMENT COMMENT 'Row unique id',
156 `user_id` bigint NULL DEFAULT NULL COMMENT 'User id',
157 `chat_id` bigint NULL DEFAULT NULL COMMENT 'Telegram chat_id can be a the user id or the chat id ',
158 `status` ENUM('active', 'cancelled', 'stopped') NOT NULL DEFAULT 'active' COMMENT 'active conversation is active, cancelled conversation has been truncated before end, stopped conversation has end',
159 `command` varchar(160) DEFAULT '' COMMENT 'Default Command to execute',
160 `notes` varchar(1000) DEFAULT 'NULL' COMMENT 'Data stored from command',
161 `created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
162 `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date update',
163
164 PRIMARY KEY (`id`),
165 KEY `user_id` (`user_id`),
166 KEY `chat_id` (`chat_id`),
167 KEY `status` (`status`),
168
169 FOREIGN KEY (`user_id`)
170 REFERENCES `user` (`id`),
171 FOREIGN KEY (`chat_id`)
172 REFERENCES `chat` (`id`)
173) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;