|
0
|
1 -- Roundcube Webmail initial database structure
|
|
|
2
|
|
|
3 --
|
|
|
4 -- Table structure for table contacts and related
|
|
|
5 --
|
|
|
6
|
|
|
7 CREATE TABLE contacts (
|
|
|
8 contact_id integer NOT NULL PRIMARY KEY,
|
|
|
9 user_id integer NOT NULL,
|
|
|
10 changed datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
11 del tinyint NOT NULL default '0',
|
|
|
12 name varchar(128) NOT NULL default '',
|
|
|
13 email text NOT NULL default '',
|
|
|
14 firstname varchar(128) NOT NULL default '',
|
|
|
15 surname varchar(128) NOT NULL default '',
|
|
|
16 vcard text NOT NULL default '',
|
|
|
17 words text NOT NULL default ''
|
|
|
18 );
|
|
|
19
|
|
|
20 CREATE INDEX ix_contacts_user_id ON contacts(user_id, del);
|
|
|
21
|
|
|
22
|
|
|
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
|
|
|
41 CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id);
|
|
|
42
|
|
|
43 --
|
|
|
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',
|
|
|
50 changed datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
51 del tinyint NOT NULL default '0',
|
|
|
52 standard tinyint NOT NULL default '0',
|
|
|
53 name varchar(128) NOT NULL default '',
|
|
|
54 organization varchar(128) default '',
|
|
|
55 email varchar(128) NOT NULL default '',
|
|
|
56 "reply-to" varchar(128) NOT NULL default '',
|
|
|
57 bcc varchar(128) NOT NULL default '',
|
|
|
58 signature text NOT NULL default '',
|
|
|
59 html_signature tinyint NOT NULL default '0'
|
|
|
60 );
|
|
|
61
|
|
|
62 CREATE INDEX ix_identities_user_id ON identities(user_id, del);
|
|
|
63 CREATE INDEX ix_identities_email ON identities(email, del);
|
|
|
64
|
|
|
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',
|
|
|
74 last_login datetime DEFAULT NULL,
|
|
|
75 failed_login datetime DEFAULT NULL,
|
|
|
76 failed_login_counter integer DEFAULT NULL,
|
|
|
77 language varchar(5),
|
|
|
78 preferences text NOT NULL default ''
|
|
|
79 );
|
|
|
80
|
|
|
81 CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
|
|
|
82
|
|
|
83 --
|
|
|
84 -- Table structure for table session
|
|
|
85 --
|
|
|
86
|
|
|
87 CREATE TABLE session (
|
|
|
88 sess_id varchar(128) NOT NULL PRIMARY KEY,
|
|
|
89 changed datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
90 ip varchar(40) NOT NULL default '',
|
|
|
91 vars text NOT NULL
|
|
|
92 );
|
|
|
93
|
|
|
94 CREATE INDEX ix_session_changed ON session (changed);
|
|
|
95
|
|
|
96 --
|
|
|
97 -- Table structure for table dictionary
|
|
|
98 --
|
|
|
99
|
|
|
100 CREATE TABLE dictionary (
|
|
|
101 user_id integer DEFAULT NULL,
|
|
|
102 "language" varchar(5) NOT NULL,
|
|
|
103 data text NOT NULL
|
|
|
104 );
|
|
|
105
|
|
|
106 CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language");
|
|
|
107
|
|
|
108 --
|
|
|
109 -- Table structure for table searches
|
|
|
110 --
|
|
|
111
|
|
|
112 CREATE TABLE searches (
|
|
|
113 search_id integer NOT NULL PRIMARY KEY,
|
|
|
114 user_id integer NOT NULL DEFAULT '0',
|
|
|
115 "type" smallint NOT NULL DEFAULT '0',
|
|
|
116 name varchar(128) NOT NULL,
|
|
|
117 data text NOT NULL
|
|
|
118 );
|
|
|
119
|
|
|
120 CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name);
|
|
|
121
|
|
|
122 --
|
|
|
123 -- Table structure for table cache
|
|
|
124 --
|
|
|
125
|
|
|
126 CREATE TABLE cache (
|
|
|
127 user_id integer NOT NULL default 0,
|
|
|
128 cache_key varchar(128) NOT NULL default '',
|
|
|
129 expires datetime DEFAULT NULL,
|
|
|
130 data text NOT NULL,
|
|
|
131 PRIMARY KEY (user_id, cache_key)
|
|
|
132 );
|
|
|
133
|
|
|
134 CREATE INDEX ix_cache_expires ON cache(expires);
|
|
|
135
|
|
|
136 --
|
|
|
137 -- Table structure for table cache_shared
|
|
|
138 --
|
|
|
139
|
|
|
140 CREATE TABLE cache_shared (
|
|
|
141 cache_key varchar(255) NOT NULL,
|
|
|
142 expires datetime DEFAULT NULL,
|
|
|
143 data text NOT NULL,
|
|
|
144 PRIMARY KEY (cache_key)
|
|
|
145 );
|
|
|
146
|
|
|
147 CREATE INDEX ix_cache_shared_expires ON cache_shared(expires);
|
|
|
148
|
|
|
149 --
|
|
|
150 -- Table structure for table cache_index
|
|
|
151 --
|
|
|
152
|
|
|
153 CREATE TABLE cache_index (
|
|
|
154 user_id integer NOT NULL,
|
|
|
155 mailbox varchar(255) NOT NULL,
|
|
|
156 expires datetime DEFAULT NULL,
|
|
|
157 valid smallint NOT NULL DEFAULT '0',
|
|
|
158 data text NOT NULL,
|
|
|
159 PRIMARY KEY (user_id, mailbox)
|
|
|
160 );
|
|
|
161
|
|
|
162 CREATE INDEX ix_cache_index_expires ON cache_index (expires);
|
|
|
163
|
|
|
164 --
|
|
|
165 -- Table structure for table cache_thread
|
|
|
166 --
|
|
|
167
|
|
|
168 CREATE TABLE cache_thread (
|
|
|
169 user_id integer NOT NULL,
|
|
|
170 mailbox varchar(255) NOT NULL,
|
|
|
171 expires datetime DEFAULT NULL,
|
|
|
172 data text NOT NULL,
|
|
|
173 PRIMARY KEY (user_id, mailbox)
|
|
|
174 );
|
|
|
175
|
|
|
176 CREATE INDEX ix_cache_thread_expires ON cache_thread (expires);
|
|
|
177
|
|
|
178 --
|
|
|
179 -- Table structure for table cache_messages
|
|
|
180 --
|
|
|
181
|
|
|
182 CREATE TABLE cache_messages (
|
|
|
183 user_id integer NOT NULL,
|
|
|
184 mailbox varchar(255) NOT NULL,
|
|
|
185 uid integer NOT NULL,
|
|
|
186 expires datetime DEFAULT NULL,
|
|
|
187 data text NOT NULL,
|
|
|
188 flags integer NOT NULL DEFAULT '0',
|
|
|
189 PRIMARY KEY (user_id, mailbox, uid)
|
|
|
190 );
|
|
|
191
|
|
|
192 CREATE INDEX ix_cache_messages_expires ON cache_messages (expires);
|
|
|
193
|
|
|
194 --
|
|
|
195 -- Table structure for table system
|
|
|
196 --
|
|
|
197
|
|
|
198 CREATE TABLE system (
|
|
|
199 name varchar(64) NOT NULL PRIMARY KEY,
|
|
|
200 value text NOT NULL
|
|
|
201 );
|
|
|
202
|
|
|
203 INSERT INTO system (name, value) VALUES ('roundcube-version', '2016112200');
|
