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, |
debdda
|
75 |
language varchar(5), |
79fe17
|
76 |
preferences text NOT NULL default '' |
T |
77 |
); |
1cded8
|
78 |
|
ace511
|
79 |
CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host); |
1cded8
|
80 |
|
T |
81 |
-- |
|
82 |
-- Table structure for table session |
|
83 |
-- |
|
84 |
|
|
85 |
CREATE TABLE session ( |
b8ae0e
|
86 |
sess_id varchar(128) NOT NULL PRIMARY KEY, |
1cded8
|
87 |
created datetime NOT NULL default '0000-00-00 00:00:00', |
T |
88 |
changed datetime NOT NULL default '0000-00-00 00:00:00', |
84d06e
|
89 |
ip varchar(40) NOT NULL default '', |
1cded8
|
90 |
vars text NOT NULL |
T |
91 |
); |
|
92 |
|
3e48d2
|
93 |
CREATE INDEX ix_session_changed ON session (changed); |
1cded8
|
94 |
|
66df08
|
95 |
-- |
A |
96 |
-- Table structure for table dictionary |
|
97 |
-- |
|
98 |
|
|
99 |
CREATE TABLE dictionary ( |
|
100 |
user_id integer DEFAULT NULL, |
|
101 |
"language" varchar(5) NOT NULL, |
|
102 |
data text NOT NULL |
|
103 |
); |
|
104 |
|
|
105 |
CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language"); |
f8e48d
|
106 |
|
A |
107 |
-- |
|
108 |
-- Table structure for table searches |
|
109 |
-- |
|
110 |
|
|
111 |
CREATE TABLE searches ( |
|
112 |
search_id integer NOT NULL PRIMARY KEY, |
|
113 |
user_id integer NOT NULL DEFAULT '0', |
|
114 |
"type" smallint NOT NULL DEFAULT '0', |
|
115 |
name varchar(128) NOT NULL, |
|
116 |
data text NOT NULL |
|
117 |
); |
|
118 |
|
bd9190
|
119 |
CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name); |
80152b
|
120 |
|
50abd5
|
121 |
-- |
AM |
122 |
-- Table structure for table cache |
|
123 |
-- |
|
124 |
|
|
125 |
CREATE TABLE cache ( |
|
126 |
user_id integer NOT NULL default 0, |
|
127 |
cache_key varchar(128) NOT NULL default '', |
|
128 |
created datetime NOT NULL default '0000-00-00 00:00:00', |
8483de
|
129 |
expires datetime DEFAULT NULL, |
50abd5
|
130 |
data text NOT NULL |
AM |
131 |
); |
|
132 |
|
|
133 |
CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key); |
60b6d7
|
134 |
CREATE INDEX ix_cache_expires ON cache(expires); |
50abd5
|
135 |
|
AM |
136 |
-- |
|
137 |
-- Table structure for table cache_shared |
|
138 |
-- |
|
139 |
|
|
140 |
CREATE TABLE cache_shared ( |
|
141 |
cache_key varchar(255) NOT NULL, |
|
142 |
created datetime NOT NULL default '0000-00-00 00:00:00', |
60b6d7
|
143 |
expires datetime DEFAULT NULL, |
50abd5
|
144 |
data text NOT NULL |
AM |
145 |
); |
|
146 |
|
|
147 |
CREATE INDEX ix_cache_shared_cache_key ON cache_shared(cache_key); |
60b6d7
|
148 |
CREATE INDEX ix_cache_shared_expires ON cache_shared(expires); |
80152b
|
149 |
|
A |
150 |
-- |
|
151 |
-- Table structure for table cache_index |
|
152 |
-- |
|
153 |
|
|
154 |
CREATE TABLE cache_index ( |
|
155 |
user_id integer NOT NULL, |
|
156 |
mailbox varchar(255) NOT NULL, |
60b6d7
|
157 |
expires datetime DEFAULT NULL, |
609d39
|
158 |
valid smallint NOT NULL DEFAULT '0', |
80152b
|
159 |
data text NOT NULL, |
A |
160 |
PRIMARY KEY (user_id, mailbox) |
|
161 |
); |
|
162 |
|
60b6d7
|
163 |
CREATE INDEX ix_cache_index_expires ON cache_index (expires); |
80152b
|
164 |
|
A |
165 |
-- |
|
166 |
-- Table structure for table cache_thread |
|
167 |
-- |
|
168 |
|
|
169 |
CREATE TABLE cache_thread ( |
|
170 |
user_id integer NOT NULL, |
|
171 |
mailbox varchar(255) NOT NULL, |
60b6d7
|
172 |
expires datetime DEFAULT NULL, |
80152b
|
173 |
data text NOT NULL, |
A |
174 |
PRIMARY KEY (user_id, mailbox) |
|
175 |
); |
|
176 |
|
60b6d7
|
177 |
CREATE INDEX ix_cache_thread_expires ON cache_thread (expires); |
80152b
|
178 |
|
A |
179 |
-- |
|
180 |
-- Table structure for table cache_messages |
|
181 |
-- |
|
182 |
|
|
183 |
CREATE TABLE cache_messages ( |
|
184 |
user_id integer NOT NULL, |
|
185 |
mailbox varchar(255) NOT NULL, |
|
186 |
uid integer NOT NULL, |
60b6d7
|
187 |
expires datetime DEFAULT NULL, |
80152b
|
188 |
data text NOT NULL, |
609d39
|
189 |
flags integer NOT NULL DEFAULT '0', |
80152b
|
190 |
PRIMARY KEY (user_id, mailbox, uid) |
A |
191 |
); |
|
192 |
|
60b6d7
|
193 |
CREATE INDEX ix_cache_messages_expires ON cache_messages (expires); |
b7e7c8
|
194 |
|
AM |
195 |
-- |
|
196 |
-- Table structure for table system |
|
197 |
-- |
|
198 |
|
|
199 |
CREATE TABLE system ( |
|
200 |
name varchar(64) NOT NULL PRIMARY KEY, |
|
201 |
value text NOT NULL |
|
202 |
); |
|
203 |
|
08953a
|
204 |
INSERT INTO system (name, value) VALUES ('roundcube-version', '2014042900'); |