Mercurial > hg > rc1

comparison SQL/mysql.initial.sql @ 0:1e000243b222

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
vanilla 1.3.3 distro, I hope
author Charlie Root
date Thu, 04 Jan 2018 15:50:29 -0500
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:1e000243b222
1 -- Roundcube Webmail initial database structure
2
3
4 /*!40014 SET FOREIGN_KEY_CHECKS=0 */;
5
6 -- Table structure for table `session`
7
8 CREATE TABLE `session` (
9 `sess_id` varchar(128) NOT NULL,
10 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
11 `ip` varchar(40) NOT NULL,
12 `vars` mediumtext NOT NULL,
13 PRIMARY KEY(`sess_id`),
14 INDEX `changed_index` (`changed`)
15 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
16
17
18 -- Table structure for table `users`
19
20 CREATE TABLE `users` (
21 `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
22 `username` varchar(128) BINARY NOT NULL,
23 `mail_host` varchar(128) NOT NULL,
24 `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
25 `last_login` datetime DEFAULT NULL,
26 `failed_login` datetime DEFAULT NULL,
27 `failed_login_counter` int(10) UNSIGNED DEFAULT NULL,
28 `language` varchar(5),
29 `preferences` longtext,
30 PRIMARY KEY(`user_id`),
31 UNIQUE `username` (`username`, `mail_host`)
32 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
33
34
35 -- Table structure for table `cache`
36
37 CREATE TABLE `cache` (
38 `user_id` int(10) UNSIGNED NOT NULL,
39 `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL,
40 `expires` datetime DEFAULT NULL,
41 `data` longtext NOT NULL,
42 PRIMARY KEY (`user_id`, `cache_key`),
43 CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
44 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
45 INDEX `expires_index` (`expires`)
46 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
47
48
49 -- Table structure for table `cache_shared`
50
51 CREATE TABLE `cache_shared` (
52 `cache_key` varchar(255) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL,
53 `expires` datetime DEFAULT NULL,
54 `data` longtext NOT NULL,
55 PRIMARY KEY (`cache_key`),
56 INDEX `expires_index` (`expires`)
57 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
58
59
60 -- Table structure for table `cache_index`
61
62 CREATE TABLE `cache_index` (
63 `user_id` int(10) UNSIGNED NOT NULL,
64 `mailbox` varchar(255) BINARY NOT NULL,
65 `expires` datetime DEFAULT NULL,
66 `valid` tinyint(1) NOT NULL DEFAULT '0',
67 `data` longtext NOT NULL,
68 CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`)
69 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
70 INDEX `expires_index` (`expires`),
71 PRIMARY KEY (`user_id`, `mailbox`)
72 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
73
74
75 -- Table structure for table `cache_thread`
76
77 CREATE TABLE `cache_thread` (
78 `user_id` int(10) UNSIGNED NOT NULL,
79 `mailbox` varchar(255) BINARY NOT NULL,
80 `expires` datetime DEFAULT NULL,
81 `data` longtext NOT NULL,
82 CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`)
83 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
84 INDEX `expires_index` (`expires`),
85 PRIMARY KEY (`user_id`, `mailbox`)
86 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
87
88
89 -- Table structure for table `cache_messages`
90
91 CREATE TABLE `cache_messages` (
92 `user_id` int(10) UNSIGNED NOT NULL,
93 `mailbox` varchar(255) BINARY NOT NULL,
94 `uid` int(11) UNSIGNED NOT NULL DEFAULT '0',
95 `expires` datetime DEFAULT NULL,
96 `data` longtext NOT NULL,
97 `flags` int(11) NOT NULL DEFAULT '0',
98 CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`)
99 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
100 INDEX `expires_index` (`expires`),
101 PRIMARY KEY (`user_id`, `mailbox`, `uid`)
102 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
103
104
105 -- Table structure for table `contacts`
106
107 CREATE TABLE `contacts` (
108 `contact_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
109 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
110 `del` tinyint(1) NOT NULL DEFAULT '0',
111 `name` varchar(128) NOT NULL DEFAULT '',
112 `email` text NOT NULL,
113 `firstname` varchar(128) NOT NULL DEFAULT '',
114 `surname` varchar(128) NOT NULL DEFAULT '',
115 `vcard` longtext NULL,
116 `words` text NULL,
117 `user_id` int(10) UNSIGNED NOT NULL,
118 PRIMARY KEY(`contact_id`),
119 CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
120 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
121 INDEX `user_contacts_index` (`user_id`,`del`)
122 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
123
124 -- Table structure for table `contactgroups`
125
126 CREATE TABLE `contactgroups` (
127 `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
128 `user_id` int(10) UNSIGNED NOT NULL,
129 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
130 `del` tinyint(1) NOT NULL DEFAULT '0',
131 `name` varchar(128) NOT NULL DEFAULT '',
132 PRIMARY KEY(`contactgroup_id`),
133 CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`)
134 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
135 INDEX `contactgroups_user_index` (`user_id`,`del`)
136 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
137
138 CREATE TABLE `contactgroupmembers` (
139 `contactgroup_id` int(10) UNSIGNED NOT NULL,
140 `contact_id` int(10) UNSIGNED NOT NULL,
141 `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
142 PRIMARY KEY (`contactgroup_id`, `contact_id`),
143 CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`)
144 REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
145 CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`)
146 REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE,
147 INDEX `contactgroupmembers_contact_index` (`contact_id`)
148 ) /*!40000 ENGINE=INNODB */;
149
150
151 -- Table structure for table `identities`
152
153 CREATE TABLE `identities` (
154 `identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
155 `user_id` int(10) UNSIGNED NOT NULL,
156 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
157 `del` tinyint(1) NOT NULL DEFAULT '0',
158 `standard` tinyint(1) NOT NULL DEFAULT '0',
159 `name` varchar(128) NOT NULL,
160 `organization` varchar(128) NOT NULL DEFAULT '',
161 `email` varchar(128) NOT NULL,
162 `reply-to` varchar(128) NOT NULL DEFAULT '',
163 `bcc` varchar(128) NOT NULL DEFAULT '',
164 `signature` longtext,
165 `html_signature` tinyint(1) NOT NULL DEFAULT '0',
166 PRIMARY KEY(`identity_id`),
167 CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`)
168 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
169 INDEX `user_identities_index` (`user_id`, `del`),
170 INDEX `email_identities_index` (`email`, `del`)
171 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
172
173
174 -- Table structure for table `dictionary`
175
176 CREATE TABLE `dictionary` (
177 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, -- redundant, for compat. with Galera Cluster
178 `user_id` int(10) UNSIGNED DEFAULT NULL, -- NULL here is for "shared dictionaries"
179 `language` varchar(5) NOT NULL,
180 `data` longtext NOT NULL,
181 CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`)
182 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
183 UNIQUE `uniqueness` (`user_id`, `language`)
184 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
185
186
187 -- Table structure for table `searches`
188
189 CREATE TABLE `searches` (
190 `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
191 `user_id` int(10) UNSIGNED NOT NULL,
192 `type` int(3) NOT NULL DEFAULT '0',
193 `name` varchar(128) NOT NULL,
194 `data` text,
195 PRIMARY KEY(`search_id`),
196 CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`)
197 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
198 UNIQUE `uniqueness` (`user_id`, `type`, `name`)
199 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
200
201
202 -- Table structure for table `system`
203
204 CREATE TABLE `system` (
205 `name` varchar(64) NOT NULL,
206 `value` mediumtext,
207 PRIMARY KEY(`name`)
208 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
209
210 /*!40014 SET FOREIGN_KEY_CHECKS=1 */;
211
212 INSERT INTO system (name, value) VALUES ('roundcube-version', '2016112200');