forked from php-telegram-bot/core
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathstructure.sql
148 lines (129 loc) · 8.42 KB
/
structure.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
CREATE TABLE IF NOT EXISTS `user` (
`id` bigint COMMENT 'Unique user identifier',
`first_name` CHAR(255) NOT NULL DEFAULT '' COMMENT 'User first name',
`last_name` CHAR(255) DEFAULT NULL COMMENT 'User last name',
`username` CHAR(255) DEFAULT NULL COMMENT 'User username',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date update',
PRIMARY KEY (`id`),
KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE IF NOT EXISTS `chat` (
`id` bigint COMMENT 'Unique user or chat identifier',
`type` ENUM('private', 'group', 'supergroup', 'channel') NOT NULL COMMENT 'chat type private, group, supergroup or channel',
`title` CHAR(255) DEFAULT '' COMMENT 'chat title null if case of single chat with the bot',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date update',
`old_id` bigint DEFAULT NULL COMMENT 'Unique chat identifieri this is filled when a chat is converted to a superchat',
PRIMARY KEY (`id`),
KEY `old_id` (`old_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE IF NOT EXISTS `user_chat` (
`user_id` bigint COMMENT 'Unique user identifier',
`chat_id` bigint COMMENT 'Unique user or chat identifier',
PRIMARY KEY (`user_id`, `chat_id`),
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`chat_id`) REFERENCES `chat` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE IF NOT EXISTS `inline_query` (
`id` bigint UNSIGNED COMMENT 'Unique identifier for this query.',
`user_id` bigint NULL COMMENT 'Sender',
`query` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Text of the query',
`offset` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Offset of the result',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
FOREIGN KEY (`user_id`)
REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE IF NOT EXISTS `chosen_inline_query` (
`id` bigint UNSIGNED AUTO_INCREMENT COMMENT 'Unique identifier for chosen query.',
`result_id` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Id of the chosen result',
`user_id` bigint NULL COMMENT 'Sender',
`query` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Text of the query',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
FOREIGN KEY (`user_id`)
REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE IF NOT EXISTS `message` (
`chat_id` bigint NULL DEFAULT NULL COMMENT 'Chat identifier.',
`id` bigint UNSIGNED COMMENT 'Unique message identifier',
`user_id` bigint NULL COMMENT 'User identifier',
`date` timestamp NULL DEFAULT NULL COMMENT 'Date the message was sent in timestamp format',
`forward_from` bigint NULL DEFAULT NULL COMMENT 'User id. For forwarded messages, sender of the original message',
`forward_date` timestamp NULL DEFAULT NULL COMMENT 'For forwarded messages, date the original message was sent in Unix time',
`reply_to_chat` bigint NULL DEFAULT NULL COMMENT 'Chat identifier.',
`reply_to_message` bigint UNSIGNED DEFAULT NULL COMMENT 'Message is a reply to another message.',
`text` TEXT DEFAULT NULL COMMENT 'For text messages, the actual UTF-8 text of the message max message length 4096 char utf8',
`audio` TEXT DEFAULT NULL COMMENT 'Audio object. Message is an audio file, information about the file',
`document` TEXT DEFAULT NULL COMMENT 'Document object. Message is a general file, information about the file',
`photo` TEXT DEFAULT NULL COMMENT 'Array of PhotoSize objects. Message is a photo, available sizes of the photo',
`sticker` TEXT DEFAULT NULL COMMENT 'Sticker object. Message is a sticker, information about the sticker',
`video` TEXT DEFAULT NULL COMMENT 'Video object. Message is a video, information about the video',
`voice` TEXT DEFAULT NULL COMMENT 'Voice Object. Message is a Voice, information about the Voice',
`caption` TEXT DEFAULT NULL COMMENT 'For message with caption, the actual UTF-8 text of the caption',
`contact` TEXT DEFAULT NULL COMMENT 'Contact object. Message is a shared contact, information about the contact',
`location` TEXT DEFAULT NULL COMMENT 'Location object. Message is a shared location, information about the location',
`new_chat_participant` bigint NULL DEFAULT NULL COMMENT 'User id. A new member was added to the group, information about them (this member may be bot itself)',
`left_chat_participant` bigint NULL DEFAULT NULL COMMENT 'User id. A member was removed from the group, information about them (this member may be bot itself)',
`new_chat_title` CHAR(255) DEFAULT NULL COMMENT 'A group title was changed to this value',
`new_chat_photo` TEXT DEFAULT NULL COMMENT 'Array of PhotoSize objects. A group photo was change to this value',
`delete_chat_photo` tinyint(1) DEFAULT 0 COMMENT 'Informs that the group photo was deleted',
`group_chat_created` tinyint(1) DEFAULT 0 COMMENT 'Informs that the group has been created',
`supergroup_chat_created` tinyint(1) DEFAULT 0 COMMENT 'Informs that the supergroup has been created',
`channel_chat_created` tinyint(1) DEFAULT 0 COMMENT 'Informs that the channel chat has been created',
`migrate_from_chat_id` bigint NULL DEFAULT NULL COMMENT 'Migrate from chat identifier.',
`migrate_to_chat_id` bigint NULL DEFAULT NULL COMMENT 'Migrate to chat identifier.',
PRIMARY KEY (`chat_id`, `id`),
KEY `user_id` (`user_id`),
KEY `forward_from` (`forward_from`),
KEY `reply_to_chat` (`reply_to_chat`),
KEY `reply_to_message` (`reply_to_message`),
KEY `new_chat_participant` (`new_chat_participant`),
KEY `left_chat_participant` (`left_chat_participant`),
KEY `migrate_from_chat_id` (`migrate_from_chat_id`),
KEY `migrate_to_chat_id` (`migrate_to_chat_id`),
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
FOREIGN KEY (`chat_id`) REFERENCES `chat` (`id`),
FOREIGN KEY (`forward_from`) REFERENCES `user` (`id`),
FOREIGN KEY (`reply_to_chat`, `reply_to_message`) REFERENCES `message` (`chat_id`,`id`),
FOREIGN KEY (`forward_from`) REFERENCES `user` (`id`),
FOREIGN KEY (`new_chat_participant`) REFERENCES `user` (`id`),
FOREIGN KEY (`left_chat_participant`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE IF NOT EXISTS `telegram_update` (
`id` bigint UNSIGNED COMMENT 'The update\'s unique identifier.',
`chat_id` bigint NULL DEFAULT NULL COMMENT 'Chat identifier.',
`message_id` bigint UNSIGNED DEFAULT NULL COMMENT 'Unique message identifier',
`inline_query_id` bigint UNSIGNED DEFAULT NULL COMMENT 'The query unique identifier.',
`chosen_inline_query_id` bigint UNSIGNED DEFAULT NULL COMMENT 'The chosen query unique identifier.',
PRIMARY KEY (`id`),
KEY `message_id` (`chat_id`, `message_id`),
KEY `inline_query_id` (`inline_query_id`),
KEY `chosen_inline_query_id` (`chosen_inline_query_id`),
FOREIGN KEY (`chat_id`, `message_id`) REFERENCES `message` (`chat_id`,`id`),
FOREIGN KEY (`inline_query_id`) REFERENCES `inline_query` (`id`),
FOREIGN KEY (`chosen_inline_query_id`) REFERENCES `chosen_inline_query` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE IF NOT EXISTS `conversation` (
`id` bigint(20) unsigned AUTO_INCREMENT COMMENT 'Row unique id',
`user_id` bigint NULL DEFAULT NULL COMMENT 'User id',
`chat_id` bigint NULL DEFAULT NULL COMMENT 'Telegram chat_id can be a the user id or the chat id ',
`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',
`command` varchar(160) DEFAULT '' COMMENT 'Default Command to execute',
`notes` varchar(1000) DEFAULT 'NULL' COMMENT 'Data stored from command',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date update',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `chat_id` (`chat_id`),
KEY `status` (`status`),
FOREIGN KEY (`user_id`)
REFERENCES `user` (`id`),
FOREIGN KEY (`chat_id`)
REFERENCES `chat` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;