commit | author | age
|
e019f2
|
1 |
-- Roundcube Webmail initial database structure |
79fe17
|
2 |
|
T |
3 |
-- |
a61bbb
|
4 |
-- Table structure for table contacts and related |
79fe17
|
5 |
-- |
T |
6 |
|
|
7 |
CREATE TABLE contacts ( |
|
8 |
contact_id integer NOT NULL PRIMARY KEY, |
48be8f
|
9 |
user_id integer NOT NULL, |
58e360
|
10 |
changed datetime NOT NULL default '0000-00-00 00:00:00', |
1cded8
|
11 |
del tinyint NOT NULL default '0', |
79fe17
|
12 |
name varchar(128) NOT NULL default '', |
48be8f
|
13 |
email text NOT NULL default '', |
79fe17
|
14 |
firstname varchar(128) NOT NULL default '', |
T |
15 |
surname varchar(128) NOT NULL default '', |
3e2637
|
16 |
vcard text NOT NULL default '', |
T |
17 |
words text NOT NULL default '' |
79fe17
|
18 |
); |
T |
19 |
|
48be8f
|
20 |
CREATE INDEX ix_contacts_user_id ON contacts(user_id, del); |
79fe17
|
21 |
|
a61bbb
|
22 |
|
T |
23 |
CREATE TABLE contactgroups ( |
|
24 |
contactgroup_id integer NOT NULL PRIMARY KEY, |
|
25 |
user_id integer NOT NULL default '0', |
|
26 |
changed datetime NOT NULL default '0000-00-00 00:00:00', |
|
27 |
del tinyint NOT NULL default '0', |
|
28 |
name varchar(128) NOT NULL default '' |
|
29 |
); |
|
30 |
|
|
31 |
CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del); |
|
32 |
|
|
33 |
|
|
34 |
CREATE TABLE contactgroupmembers ( |
|
35 |
contactgroup_id integer NOT NULL, |
|
36 |
contact_id integer NOT NULL default '0', |
|
37 |
created datetime NOT NULL default '0000-00-00 00:00:00', |
|
38 |
PRIMARY KEY (contactgroup_id, contact_id) |
|
39 |
); |
|
40 |
|
3a5476
|
41 |
CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id); |
A |
42 |
|
79fe17
|
43 |
-- |
T |
44 |
-- Table structure for table identities |
|
45 |
-- |
|
46 |
|
|
47 |
CREATE TABLE identities ( |
|
48 |
identity_id integer NOT NULL PRIMARY KEY, |
|
49 |
user_id integer NOT NULL default '0', |
a35062
|
50 |
changed datetime NOT NULL default '0000-00-00 00:00:00', |
1cded8
|
51 |
del tinyint NOT NULL default '0', |
T |
52 |
standard tinyint NOT NULL default '0', |
79fe17
|
53 |
name varchar(128) NOT NULL default '', |
1cded8
|
54 |
organization varchar(128) default '', |
79fe17
|
55 |
email varchar(128) NOT NULL default '', |
T |
56 |
"reply-to" varchar(128) NOT NULL default '', |
|
57 |
bcc varchar(128) NOT NULL default '', |
a0109c
|
58 |
signature text NOT NULL default '', |
S |
59 |
html_signature tinyint NOT NULL default '0' |
79fe17
|
60 |
); |
T |
61 |
|
94fe9c
|
62 |
CREATE INDEX ix_identities_user_id ON identities(user_id, del); |
565c47
|
63 |
CREATE INDEX ix_identities_email ON identities(email, del); |
79fe17
|
64 |
|
T |
65 |
-- |
|
66 |
-- Table structure for table users |
|
67 |
-- |
|
68 |
|
|
69 |
CREATE TABLE users ( |
|
70 |
user_id integer NOT NULL PRIMARY KEY, |
|
71 |
username varchar(128) NOT NULL default '', |
|
72 |
mail_host varchar(128) NOT NULL default '', |
|
73 |
created datetime NOT NULL default '0000-00-00 00:00:00', |
e2402e
|
74 |
last_login datetime DEFAULT NULL, |
a15d87
|
75 |
failed_login datetime DEFAULT NULL, |
AM |
76 |
failed_login_counter integer DEFAULT NULL, |
debdda
|
77 |
language varchar(5), |
79fe17
|
78 |
preferences text NOT NULL default '' |
T |
79 |
); |
1cded8
|
80 |
|
ace511
|
81 |
CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host); |
1cded8
|
82 |
|
T |
83 |
-- |
|
84 |
-- Table structure for table session |
|
85 |
-- |
|
86 |
|
|
87 |
CREATE TABLE session ( |
b8ae0e
|
88 |
sess_id varchar(128) NOT NULL PRIMARY KEY, |
1cded8
|
89 |
created datetime NOT NULL default '0000-00-00 00:00:00', |
T |
90 |
changed datetime NOT NULL default '0000-00-00 00:00:00', |
84d06e
|
91 |
ip varchar(40) NOT NULL default '', |
1cded8
|
92 |
vars text NOT NULL |
T |
93 |
); |
|
94 |
|
3e48d2
|
95 |
CREATE INDEX ix_session_changed ON session (changed); |
1cded8
|
96 |
|
66df08
|
97 |
-- |
A |
98 |
-- Table structure for table dictionary |
|
99 |
-- |
|
100 |
|
|
101 |
CREATE TABLE dictionary ( |
|
102 |
user_id integer DEFAULT NULL, |
|
103 |
"language" varchar(5) NOT NULL, |
|
104 |
data text NOT NULL |
|
105 |
); |
|
106 |
|
|
107 |
CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language"); |
f8e48d
|
108 |
|
A |
109 |
-- |
|
110 |
-- Table structure for table searches |
|
111 |
-- |
|
112 |
|
|
113 |
CREATE TABLE searches ( |
|
114 |
search_id integer NOT NULL PRIMARY KEY, |
|
115 |
user_id integer NOT NULL DEFAULT '0', |
|
116 |
"type" smallint NOT NULL DEFAULT '0', |
|
117 |
name varchar(128) NOT NULL, |
|
118 |
data text NOT NULL |
|
119 |
); |
|
120 |
|
bd9190
|
121 |
CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name); |
80152b
|
122 |
|
50abd5
|
123 |
-- |
AM |
124 |
-- Table structure for table cache |
|
125 |
-- |
|
126 |
|
|
127 |
CREATE TABLE cache ( |
|
128 |
user_id integer NOT NULL default 0, |
|
129 |
cache_key varchar(128) NOT NULL default '', |
|
130 |
created datetime NOT NULL default '0000-00-00 00:00:00', |
8483de
|
131 |
expires datetime DEFAULT NULL, |
50abd5
|
132 |
data text NOT NULL |
AM |
133 |
); |
|
134 |
|
|
135 |
CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key); |
60b6d7
|
136 |
CREATE INDEX ix_cache_expires ON cache(expires); |
50abd5
|
137 |
|
AM |
138 |
-- |
|
139 |
-- Table structure for table cache_shared |
|
140 |
-- |
|
141 |
|
|
142 |
CREATE TABLE cache_shared ( |
|
143 |
cache_key varchar(255) NOT NULL, |
|
144 |
created datetime NOT NULL default '0000-00-00 00:00:00', |
60b6d7
|
145 |
expires datetime DEFAULT NULL, |
50abd5
|
146 |
data text NOT NULL |
AM |
147 |
); |
|
148 |
|
|
149 |
CREATE INDEX ix_cache_shared_cache_key ON cache_shared(cache_key); |
60b6d7
|
150 |
CREATE INDEX ix_cache_shared_expires ON cache_shared(expires); |
80152b
|
151 |
|
A |
152 |
-- |
|
153 |
-- Table structure for table cache_index |
|
154 |
-- |
|
155 |
|
|
156 |
CREATE TABLE cache_index ( |
|
157 |
user_id integer NOT NULL, |
|
158 |
mailbox varchar(255) NOT NULL, |
60b6d7
|
159 |
expires datetime DEFAULT NULL, |
609d39
|
160 |
valid smallint NOT NULL DEFAULT '0', |
80152b
|
161 |
data text NOT NULL, |
A |
162 |
PRIMARY KEY (user_id, mailbox) |
|
163 |
); |
|
164 |
|
60b6d7
|
165 |
CREATE INDEX ix_cache_index_expires ON cache_index (expires); |
80152b
|
166 |
|
A |
167 |
-- |
|
168 |
-- Table structure for table cache_thread |
|
169 |
-- |
|
170 |
|
|
171 |
CREATE TABLE cache_thread ( |
|
172 |
user_id integer NOT NULL, |
|
173 |
mailbox varchar(255) NOT NULL, |
60b6d7
|
174 |
expires datetime DEFAULT NULL, |
80152b
|
175 |
data text NOT NULL, |
A |
176 |
PRIMARY KEY (user_id, mailbox) |
|
177 |
); |
|
178 |
|
60b6d7
|
179 |
CREATE INDEX ix_cache_thread_expires ON cache_thread (expires); |
80152b
|
180 |
|
A |
181 |
-- |
|
182 |
-- Table structure for table cache_messages |
|
183 |
-- |
|
184 |
|
|
185 |
CREATE TABLE cache_messages ( |
|
186 |
user_id integer NOT NULL, |
|
187 |
mailbox varchar(255) NOT NULL, |
|
188 |
uid integer NOT NULL, |
60b6d7
|
189 |
expires datetime DEFAULT NULL, |
80152b
|
190 |
data text NOT NULL, |
609d39
|
191 |
flags integer NOT NULL DEFAULT '0', |
80152b
|
192 |
PRIMARY KEY (user_id, mailbox, uid) |
A |
193 |
); |
|
194 |
|
60b6d7
|
195 |
CREATE INDEX ix_cache_messages_expires ON cache_messages (expires); |
b7e7c8
|
196 |
|
AM |
197 |
-- |
|
198 |
-- Table structure for table system |
|
199 |
-- |
|
200 |
|
|
201 |
CREATE TABLE system ( |
|
202 |
name varchar(64) NOT NULL PRIMARY KEY, |
|
203 |
value text NOT NULL |
|
204 |
); |
|
205 |
|
a15d87
|
206 |
INSERT INTO system (name, value) VALUES ('roundcube-version', '2015111100'); |