commit | author | age
|
e019f2
|
1 |
-- Roundcube Webmail initial database structure |
aade7b
|
2 |
|
T |
3 |
|
2bedfa
|
4 |
/*!40014 SET FOREIGN_KEY_CHECKS=0 */; |
aade7b
|
5 |
|
T |
6 |
-- Table structure for table `session` |
|
7 |
|
|
8 |
CREATE TABLE `session` ( |
b8ae0e
|
9 |
`sess_id` varchar(128) NOT NULL, |
37974e
|
10 |
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
A |
11 |
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
84d06e
|
12 |
`ip` varchar(40) NOT NULL, |
b2b8b1
|
13 |
`vars` mediumtext NOT NULL, |
3e48d2
|
14 |
PRIMARY KEY(`sess_id`), |
A |
15 |
INDEX `changed_index` (`changed`) |
2bedfa
|
16 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
aade7b
|
17 |
|
T |
18 |
|
|
19 |
-- Table structure for table `users` |
|
20 |
|
|
21 |
CREATE TABLE `users` ( |
|
22 |
`user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
d5ae97
|
23 |
`username` varchar(128) BINARY NOT NULL, |
aade7b
|
24 |
`mail_host` varchar(128) NOT NULL, |
37974e
|
25 |
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
e2402e
|
26 |
`last_login` datetime DEFAULT NULL, |
debdda
|
27 |
`language` varchar(5), |
08953a
|
28 |
`preferences` longtext, |
6cb778
|
29 |
PRIMARY KEY(`user_id`), |
565c47
|
30 |
UNIQUE `username` (`username`, `mail_host`) |
2bedfa
|
31 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
aade7b
|
32 |
|
T |
33 |
|
|
34 |
-- Table structure for table `cache` |
|
35 |
|
|
36 |
CREATE TABLE `cache` ( |
83121e
|
37 |
`user_id` int(10) UNSIGNED NOT NULL, |
60b6d7
|
38 |
`cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL, |
37974e
|
39 |
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
60b6d7
|
40 |
`expires` datetime DEFAULT NULL, |
aade7b
|
41 |
`data` longtext NOT NULL, |
94fe9c
|
42 |
CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) |
A |
43 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
60b6d7
|
44 |
INDEX `expires_index` (`expires`), |
8248e4
|
45 |
INDEX `user_cache_index` (`user_id`,`cache_key`) |
2bedfa
|
46 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
aade7b
|
47 |
|
T |
48 |
|
50abd5
|
49 |
-- Table structure for table `cache_shared` |
AM |
50 |
|
|
51 |
CREATE TABLE `cache_shared` ( |
60b6d7
|
52 |
`cache_key` varchar(255) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL, |
50abd5
|
53 |
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
60b6d7
|
54 |
`expires` datetime DEFAULT NULL, |
50abd5
|
55 |
`data` longtext NOT NULL, |
60b6d7
|
56 |
INDEX `expires_index` (`expires`), |
50abd5
|
57 |
INDEX `cache_key_index` (`cache_key`) |
AM |
58 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
|
59 |
|
|
60 |
|
80152b
|
61 |
-- Table structure for table `cache_index` |
A |
62 |
|
|
63 |
CREATE TABLE `cache_index` ( |
48be8f
|
64 |
`user_id` int(10) UNSIGNED NOT NULL, |
80152b
|
65 |
`mailbox` varchar(255) BINARY NOT NULL, |
60b6d7
|
66 |
`expires` datetime DEFAULT NULL, |
609d39
|
67 |
`valid` tinyint(1) NOT NULL DEFAULT '0', |
80152b
|
68 |
`data` longtext NOT NULL, |
A |
69 |
CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`) |
|
70 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
60b6d7
|
71 |
INDEX `expires_index` (`expires`), |
80152b
|
72 |
PRIMARY KEY (`user_id`, `mailbox`) |
A |
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` ( |
48be8f
|
79 |
`user_id` int(10) UNSIGNED NOT NULL, |
80152b
|
80 |
`mailbox` varchar(255) BINARY NOT NULL, |
60b6d7
|
81 |
`expires` datetime DEFAULT NULL, |
80152b
|
82 |
`data` longtext NOT NULL, |
A |
83 |
CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`) |
|
84 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
60b6d7
|
85 |
INDEX `expires_index` (`expires`), |
80152b
|
86 |
PRIMARY KEY (`user_id`, `mailbox`) |
A |
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` ( |
48be8f
|
93 |
`user_id` int(10) UNSIGNED NOT NULL, |
80152b
|
94 |
`mailbox` varchar(255) BINARY NOT NULL, |
A |
95 |
`uid` int(11) UNSIGNED NOT NULL DEFAULT '0', |
60b6d7
|
96 |
`expires` datetime DEFAULT NULL, |
80152b
|
97 |
`data` longtext NOT NULL, |
609d39
|
98 |
`flags` int(11) NOT NULL DEFAULT '0', |
80152b
|
99 |
CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`) |
A |
100 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
60b6d7
|
101 |
INDEX `expires_index` (`expires`), |
80152b
|
102 |
PRIMARY KEY (`user_id`, `mailbox`, `uid`) |
A |
103 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
|
104 |
|
|
105 |
|
aade7b
|
106 |
-- Table structure for table `contacts` |
T |
107 |
|
|
108 |
CREATE TABLE `contacts` ( |
|
109 |
`contact_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
37974e
|
110 |
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
aade7b
|
111 |
`del` tinyint(1) NOT NULL DEFAULT '0', |
951c07
|
112 |
`name` varchar(128) NOT NULL DEFAULT '', |
3d4a48
|
113 |
`email` text NOT NULL, |
951c07
|
114 |
`firstname` varchar(128) NOT NULL DEFAULT '', |
A |
115 |
`surname` varchar(128) NOT NULL DEFAULT '', |
09b0e3
|
116 |
`vcard` longtext NULL, |
3e2637
|
117 |
`words` text NULL, |
48be8f
|
118 |
`user_id` int(10) UNSIGNED NOT NULL, |
aade7b
|
119 |
PRIMARY KEY(`contact_id`), |
94fe9c
|
120 |
CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) |
A |
121 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
48be8f
|
122 |
INDEX `user_contacts_index` (`user_id`,`del`) |
2bedfa
|
123 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
aade7b
|
124 |
|
a61bbb
|
125 |
-- Table structure for table `contactgroups` |
T |
126 |
|
|
127 |
CREATE TABLE `contactgroups` ( |
|
128 |
`contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
48be8f
|
129 |
`user_id` int(10) UNSIGNED NOT NULL, |
192413
|
130 |
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
a61bbb
|
131 |
`del` tinyint(1) NOT NULL DEFAULT '0', |
T |
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, |
48be8f
|
141 |
`contact_id` int(10) UNSIGNED NOT NULL, |
192413
|
142 |
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
a61bbb
|
143 |
PRIMARY KEY (`contactgroup_id`, `contact_id`), |
T |
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`) |
3a5476
|
147 |
REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
A |
148 |
INDEX `contactgroupmembers_contact_index` (`contact_id`) |
a61bbb
|
149 |
) /*!40000 ENGINE=INNODB */; |
T |
150 |
|
aade7b
|
151 |
|
T |
152 |
-- Table structure for table `identities` |
|
153 |
|
|
154 |
CREATE TABLE `identities` ( |
|
155 |
`identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
48be8f
|
156 |
`user_id` int(10) UNSIGNED NOT NULL, |
a35062
|
157 |
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
aade7b
|
158 |
`del` tinyint(1) NOT NULL DEFAULT '0', |
T |
159 |
`standard` tinyint(1) NOT NULL DEFAULT '0', |
|
160 |
`name` varchar(128) NOT NULL, |
d7f3d7
|
161 |
`organization` varchar(128) NOT NULL DEFAULT '', |
aade7b
|
162 |
`email` varchar(128) NOT NULL, |
d7f3d7
|
163 |
`reply-to` varchar(128) NOT NULL DEFAULT '', |
A |
164 |
`bcc` varchar(128) NOT NULL DEFAULT '', |
f18911
|
165 |
`signature` text, |
a0109c
|
166 |
`html_signature` tinyint(1) NOT NULL DEFAULT '0', |
94fe9c
|
167 |
PRIMARY KEY(`identity_id`), |
A |
168 |
CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) |
|
169 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
565c47
|
170 |
INDEX `user_identities_index` (`user_id`, `del`), |
AM |
171 |
INDEX `email_identities_index` (`email`, `del`) |
2bedfa
|
172 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
8248e4
|
173 |
|
aade7b
|
174 |
|
66df08
|
175 |
-- Table structure for table `dictionary` |
A |
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 |
|
f8e48d
|
186 |
|
A |
187 |
-- Table structure for table `searches` |
|
188 |
|
|
189 |
CREATE TABLE `searches` ( |
|
190 |
`search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
48be8f
|
191 |
`user_id` int(10) UNSIGNED NOT NULL, |
f8e48d
|
192 |
`type` int(3) NOT NULL DEFAULT '0', |
A |
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 |
|
b7e7c8
|
202 |
-- Table structure for table `system` |
AM |
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 |
|
2bedfa
|
210 |
/*!40014 SET FOREIGN_KEY_CHECKS=1 */; |
b7e7c8
|
211 |
|
08953a
|
212 |
INSERT INTO system (name, value) VALUES ('roundcube-version', '2014042900'); |