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, |
a15d87
|
27 |
`failed_login` datetime DEFAULT NULL, |
AM |
28 |
`failed_login_counter` int(10) UNSIGNED DEFAULT NULL, |
debdda
|
29 |
`language` varchar(5), |
b6f730
|
30 |
`preferences` longtext, |
6cb778
|
31 |
PRIMARY KEY(`user_id`), |
565c47
|
32 |
UNIQUE `username` (`username`, `mail_host`) |
2bedfa
|
33 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
aade7b
|
34 |
|
T |
35 |
|
|
36 |
-- Table structure for table `cache` |
|
37 |
|
|
38 |
CREATE TABLE `cache` ( |
83121e
|
39 |
`user_id` int(10) UNSIGNED NOT NULL, |
60b6d7
|
40 |
`cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL, |
37974e
|
41 |
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
60b6d7
|
42 |
`expires` datetime DEFAULT NULL, |
aade7b
|
43 |
`data` longtext NOT NULL, |
94fe9c
|
44 |
CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) |
A |
45 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
60b6d7
|
46 |
INDEX `expires_index` (`expires`), |
8248e4
|
47 |
INDEX `user_cache_index` (`user_id`,`cache_key`) |
2bedfa
|
48 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
aade7b
|
49 |
|
T |
50 |
|
50abd5
|
51 |
-- Table structure for table `cache_shared` |
AM |
52 |
|
|
53 |
CREATE TABLE `cache_shared` ( |
60b6d7
|
54 |
`cache_key` varchar(255) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL, |
50abd5
|
55 |
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
60b6d7
|
56 |
`expires` datetime DEFAULT NULL, |
50abd5
|
57 |
`data` longtext NOT NULL, |
60b6d7
|
58 |
INDEX `expires_index` (`expires`), |
50abd5
|
59 |
INDEX `cache_key_index` (`cache_key`) |
AM |
60 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
|
61 |
|
|
62 |
|
80152b
|
63 |
-- Table structure for table `cache_index` |
A |
64 |
|
|
65 |
CREATE TABLE `cache_index` ( |
48be8f
|
66 |
`user_id` int(10) UNSIGNED NOT NULL, |
80152b
|
67 |
`mailbox` varchar(255) BINARY NOT NULL, |
60b6d7
|
68 |
`expires` datetime DEFAULT NULL, |
609d39
|
69 |
`valid` tinyint(1) NOT NULL DEFAULT '0', |
80152b
|
70 |
`data` longtext NOT NULL, |
A |
71 |
CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`) |
|
72 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
60b6d7
|
73 |
INDEX `expires_index` (`expires`), |
80152b
|
74 |
PRIMARY KEY (`user_id`, `mailbox`) |
A |
75 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
|
76 |
|
|
77 |
|
|
78 |
-- Table structure for table `cache_thread` |
|
79 |
|
|
80 |
CREATE TABLE `cache_thread` ( |
48be8f
|
81 |
`user_id` int(10) UNSIGNED NOT NULL, |
80152b
|
82 |
`mailbox` varchar(255) BINARY NOT NULL, |
60b6d7
|
83 |
`expires` datetime DEFAULT NULL, |
80152b
|
84 |
`data` longtext NOT NULL, |
A |
85 |
CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`) |
|
86 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
60b6d7
|
87 |
INDEX `expires_index` (`expires`), |
80152b
|
88 |
PRIMARY KEY (`user_id`, `mailbox`) |
A |
89 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
|
90 |
|
|
91 |
|
|
92 |
-- Table structure for table `cache_messages` |
|
93 |
|
|
94 |
CREATE TABLE `cache_messages` ( |
48be8f
|
95 |
`user_id` int(10) UNSIGNED NOT NULL, |
80152b
|
96 |
`mailbox` varchar(255) BINARY NOT NULL, |
A |
97 |
`uid` int(11) UNSIGNED NOT NULL DEFAULT '0', |
60b6d7
|
98 |
`expires` datetime DEFAULT NULL, |
80152b
|
99 |
`data` longtext NOT NULL, |
609d39
|
100 |
`flags` int(11) NOT NULL DEFAULT '0', |
80152b
|
101 |
CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`) |
A |
102 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
60b6d7
|
103 |
INDEX `expires_index` (`expires`), |
80152b
|
104 |
PRIMARY KEY (`user_id`, `mailbox`, `uid`) |
A |
105 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
|
106 |
|
|
107 |
|
aade7b
|
108 |
-- Table structure for table `contacts` |
T |
109 |
|
|
110 |
CREATE TABLE `contacts` ( |
|
111 |
`contact_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
37974e
|
112 |
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
aade7b
|
113 |
`del` tinyint(1) NOT NULL DEFAULT '0', |
951c07
|
114 |
`name` varchar(128) NOT NULL DEFAULT '', |
3d4a48
|
115 |
`email` text NOT NULL, |
951c07
|
116 |
`firstname` varchar(128) NOT NULL DEFAULT '', |
A |
117 |
`surname` varchar(128) NOT NULL DEFAULT '', |
09b0e3
|
118 |
`vcard` longtext NULL, |
3e2637
|
119 |
`words` text NULL, |
48be8f
|
120 |
`user_id` int(10) UNSIGNED NOT NULL, |
aade7b
|
121 |
PRIMARY KEY(`contact_id`), |
94fe9c
|
122 |
CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) |
A |
123 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
48be8f
|
124 |
INDEX `user_contacts_index` (`user_id`,`del`) |
2bedfa
|
125 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
aade7b
|
126 |
|
a61bbb
|
127 |
-- Table structure for table `contactgroups` |
T |
128 |
|
|
129 |
CREATE TABLE `contactgroups` ( |
|
130 |
`contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
48be8f
|
131 |
`user_id` int(10) UNSIGNED NOT NULL, |
192413
|
132 |
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
a61bbb
|
133 |
`del` tinyint(1) NOT NULL DEFAULT '0', |
T |
134 |
`name` varchar(128) NOT NULL DEFAULT '', |
|
135 |
PRIMARY KEY(`contactgroup_id`), |
|
136 |
CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`) |
|
137 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
138 |
INDEX `contactgroups_user_index` (`user_id`,`del`) |
|
139 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
|
140 |
|
|
141 |
CREATE TABLE `contactgroupmembers` ( |
|
142 |
`contactgroup_id` int(10) UNSIGNED NOT NULL, |
48be8f
|
143 |
`contact_id` int(10) UNSIGNED NOT NULL, |
192413
|
144 |
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
a61bbb
|
145 |
PRIMARY KEY (`contactgroup_id`, `contact_id`), |
T |
146 |
CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`) |
|
147 |
REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
148 |
CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`) |
3a5476
|
149 |
REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
A |
150 |
INDEX `contactgroupmembers_contact_index` (`contact_id`) |
a61bbb
|
151 |
) /*!40000 ENGINE=INNODB */; |
T |
152 |
|
aade7b
|
153 |
|
T |
154 |
-- Table structure for table `identities` |
|
155 |
|
|
156 |
CREATE TABLE `identities` ( |
|
157 |
`identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
48be8f
|
158 |
`user_id` int(10) UNSIGNED NOT NULL, |
a35062
|
159 |
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', |
aade7b
|
160 |
`del` tinyint(1) NOT NULL DEFAULT '0', |
T |
161 |
`standard` tinyint(1) NOT NULL DEFAULT '0', |
|
162 |
`name` varchar(128) NOT NULL, |
d7f3d7
|
163 |
`organization` varchar(128) NOT NULL DEFAULT '', |
aade7b
|
164 |
`email` varchar(128) NOT NULL, |
d7f3d7
|
165 |
`reply-to` varchar(128) NOT NULL DEFAULT '', |
A |
166 |
`bcc` varchar(128) NOT NULL DEFAULT '', |
f06f4b
|
167 |
`signature` longtext, |
a0109c
|
168 |
`html_signature` tinyint(1) NOT NULL DEFAULT '0', |
94fe9c
|
169 |
PRIMARY KEY(`identity_id`), |
A |
170 |
CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) |
|
171 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
565c47
|
172 |
INDEX `user_identities_index` (`user_id`, `del`), |
AM |
173 |
INDEX `email_identities_index` (`email`, `del`) |
2bedfa
|
174 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
8248e4
|
175 |
|
aade7b
|
176 |
|
66df08
|
177 |
-- Table structure for table `dictionary` |
A |
178 |
|
|
179 |
CREATE TABLE `dictionary` ( |
|
180 |
`user_id` int(10) UNSIGNED DEFAULT NULL, |
|
181 |
`language` varchar(5) NOT NULL, |
|
182 |
`data` longtext NOT NULL, |
|
183 |
CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`) |
|
184 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
185 |
UNIQUE `uniqueness` (`user_id`, `language`) |
|
186 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
|
187 |
|
f8e48d
|
188 |
|
A |
189 |
-- Table structure for table `searches` |
|
190 |
|
|
191 |
CREATE TABLE `searches` ( |
|
192 |
`search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
48be8f
|
193 |
`user_id` int(10) UNSIGNED NOT NULL, |
f8e48d
|
194 |
`type` int(3) NOT NULL DEFAULT '0', |
A |
195 |
`name` varchar(128) NOT NULL, |
|
196 |
`data` text, |
|
197 |
PRIMARY KEY(`search_id`), |
|
198 |
CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`) |
|
199 |
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
200 |
UNIQUE `uniqueness` (`user_id`, `type`, `name`) |
|
201 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
|
202 |
|
|
203 |
|
b7e7c8
|
204 |
-- Table structure for table `system` |
AM |
205 |
|
|
206 |
CREATE TABLE `system` ( |
|
207 |
`name` varchar(64) NOT NULL, |
|
208 |
`value` mediumtext, |
|
209 |
PRIMARY KEY(`name`) |
|
210 |
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; |
|
211 |
|
2bedfa
|
212 |
/*!40014 SET FOREIGN_KEY_CHECKS=1 */; |
b7e7c8
|
213 |
|
a15d87
|
214 |
INSERT INTO system (name, value) VALUES ('roundcube-version', '2015111100'); |