|
0
|
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 `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
|
|
11 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
|
|
12 `ip` varchar(40) NOT NULL,
|
|
|
13 `vars` mediumtext NOT NULL,
|
|
|
14 PRIMARY KEY(`sess_id`),
|
|
|
15 INDEX `changed_index` (`changed`)
|
|
|
16 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
|
|
|
17
|
|
|
18
|
|
|
19 -- Table structure for table `users`
|
|
|
20
|
|
|
21 CREATE TABLE `users` (
|
|
|
22 `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
23 `username` varchar(128) BINARY NOT NULL,
|
|
|
24 `mail_host` varchar(128) NOT NULL,
|
|
|
25 `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
|
|
26 `last_login` datetime DEFAULT NULL,
|
|
|
27 `language` varchar(5),
|
|
|
28 `preferences` longtext,
|
|
|
29 PRIMARY KEY(`user_id`),
|
|
|
30 UNIQUE `username` (`username`, `mail_host`)
|
|
|
31 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
|
|
|
32
|
|
|
33
|
|
|
34 -- Table structure for table `cache`
|
|
|
35
|
|
|
36 CREATE TABLE `cache` (
|
|
|
37 `user_id` int(10) UNSIGNED NOT NULL,
|
|
|
38 `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL,
|
|
|
39 `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
|
|
40 `expires` datetime DEFAULT NULL,
|
|
|
41 `data` longtext NOT NULL,
|
|
|
42 CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
|
|
|
43 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
|
44 INDEX `expires_index` (`expires`),
|
|
|
45 INDEX `user_cache_index` (`user_id`,`cache_key`)
|
|
|
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 `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
|
|
54 `expires` datetime DEFAULT NULL,
|
|
|
55 `data` longtext NOT NULL,
|
|
|
56 INDEX `expires_index` (`expires`),
|
|
|
57 INDEX `cache_key_index` (`cache_key`)
|
|
|
58 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
|
|
|
59
|
|
|
60
|
|
|
61 -- Table structure for table `cache_index`
|
|
|
62
|
|
|
63 CREATE TABLE `cache_index` (
|
|
|
64 `user_id` int(10) UNSIGNED NOT NULL,
|
|
|
65 `mailbox` varchar(255) BINARY NOT NULL,
|
|
|
66 `expires` datetime DEFAULT NULL,
|
|
|
67 `valid` tinyint(1) NOT NULL DEFAULT '0',
|
|
|
68 `data` longtext NOT NULL,
|
|
|
69 CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`)
|
|
|
70 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
|
71 INDEX `expires_index` (`expires`),
|
|
|
72 PRIMARY KEY (`user_id`, `mailbox`)
|
|
|
73 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
|
|
|
74
|
|
|
75
|
|
|
76 -- Table structure for table `cache_thread`
|
|
|
77
|
|
|
78 CREATE TABLE `cache_thread` (
|
|
|
79 `user_id` int(10) UNSIGNED NOT NULL,
|
|
|
80 `mailbox` varchar(255) BINARY NOT NULL,
|
|
|
81 `expires` datetime DEFAULT NULL,
|
|
|
82 `data` longtext NOT NULL,
|
|
|
83 CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`)
|
|
|
84 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
|
85 INDEX `expires_index` (`expires`),
|
|
|
86 PRIMARY KEY (`user_id`, `mailbox`)
|
|
|
87 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
|
|
|
88
|
|
|
89
|
|
|
90 -- Table structure for table `cache_messages`
|
|
|
91
|
|
|
92 CREATE TABLE `cache_messages` (
|
|
|
93 `user_id` int(10) UNSIGNED NOT NULL,
|
|
|
94 `mailbox` varchar(255) BINARY NOT NULL,
|
|
|
95 `uid` int(11) UNSIGNED NOT NULL DEFAULT '0',
|
|
|
96 `expires` datetime DEFAULT NULL,
|
|
|
97 `data` longtext NOT NULL,
|
|
|
98 `flags` int(11) NOT NULL DEFAULT '0',
|
|
|
99 CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`)
|
|
|
100 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
|
101 INDEX `expires_index` (`expires`),
|
|
|
102 PRIMARY KEY (`user_id`, `mailbox`, `uid`)
|
|
|
103 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
|
|
|
104
|
|
|
105
|
|
|
106 -- Table structure for table `contacts`
|
|
|
107
|
|
|
108 CREATE TABLE `contacts` (
|
|
|
109 `contact_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
110 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
|
|
111 `del` tinyint(1) NOT NULL DEFAULT '0',
|
|
|
112 `name` varchar(128) NOT NULL DEFAULT '',
|
|
|
113 `email` text NOT NULL,
|
|
|
114 `firstname` varchar(128) NOT NULL DEFAULT '',
|
|
|
115 `surname` varchar(128) NOT NULL DEFAULT '',
|
|
|
116 `vcard` longtext NULL,
|
|
|
117 `words` text NULL,
|
|
|
118 `user_id` int(10) UNSIGNED NOT NULL,
|
|
|
119 PRIMARY KEY(`contact_id`),
|
|
|
120 CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
|
|
|
121 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
|
122 INDEX `user_contacts_index` (`user_id`,`del`)
|
|
|
123 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
|
|
|
124
|
|
|
125 -- Table structure for table `contactgroups`
|
|
|
126
|
|
|
127 CREATE TABLE `contactgroups` (
|
|
|
128 `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
129 `user_id` int(10) UNSIGNED NOT NULL,
|
|
|
130 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
|
|
131 `del` tinyint(1) NOT NULL DEFAULT '0',
|
|
|
132 `name` varchar(128) NOT NULL DEFAULT '',
|
|
|
133 PRIMARY KEY(`contactgroup_id`),
|
|
|
134 CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`)
|
|
|
135 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
|
136 INDEX `contactgroups_user_index` (`user_id`,`del`)
|
|
|
137 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
|
|
|
138
|
|
|
139 CREATE TABLE `contactgroupmembers` (
|
|
|
140 `contactgroup_id` int(10) UNSIGNED NOT NULL,
|
|
|
141 `contact_id` int(10) UNSIGNED NOT NULL,
|
|
|
142 `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
|
|
143 PRIMARY KEY (`contactgroup_id`, `contact_id`),
|
|
|
144 CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`)
|
|
|
145 REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
|
146 CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`)
|
|
|
147 REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
|
148 INDEX `contactgroupmembers_contact_index` (`contact_id`)
|
|
|
149 ) /*!40000 ENGINE=INNODB */;
|
|
|
150
|
|
|
151
|
|
|
152 -- Table structure for table `identities`
|
|
|
153
|
|
|
154 CREATE TABLE `identities` (
|
|
|
155 `identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
156 `user_id` int(10) UNSIGNED NOT NULL,
|
|
|
157 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
|
|
158 `del` tinyint(1) NOT NULL DEFAULT '0',
|
|
|
159 `standard` tinyint(1) NOT NULL DEFAULT '0',
|
|
|
160 `name` varchar(128) NOT NULL,
|
|
|
161 `organization` varchar(128) NOT NULL DEFAULT '',
|
|
|
162 `email` varchar(128) NOT NULL,
|
|
|
163 `reply-to` varchar(128) NOT NULL DEFAULT '',
|
|
|
164 `bcc` varchar(128) NOT NULL DEFAULT '',
|
|
|
165 `signature` longtext,
|
|
|
166 `html_signature` tinyint(1) NOT NULL DEFAULT '0',
|
|
|
167 PRIMARY KEY(`identity_id`),
|
|
|
168 CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`)
|
|
|
169 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
|
170 INDEX `user_identities_index` (`user_id`, `del`),
|
|
|
171 INDEX `email_identities_index` (`email`, `del`)
|
|
|
172 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
|
|
|
173
|
|
|
174
|
|
|
175 -- Table structure for table `dictionary`
|
|
|
176
|
|
|
177 CREATE TABLE `dictionary` (
|
|
|
178 `user_id` int(10) UNSIGNED DEFAULT NULL,
|
|
|
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', '2015030800');
|
