1 -- Roundcube Webmail update script for SQLite databases
2 -- Updates from version 0.1-stable to 0.1.1
6 CREATE TABLE messages (
7 message_id integer NOT NULL PRIMARY KEY,
8 user_id integer NOT NULL default '0',
9 del tinyint NOT NULL default '0',
10 cache_key varchar(128) NOT NULL default '',
11 created datetime NOT NULL default '0000-00-00 00:00:00',
12 idx integer NOT NULL default '0',
13 uid integer NOT NULL default '0',
14 subject varchar(255) NOT NULL default '',
15 "from" varchar(255) NOT NULL default '',
16 "to" varchar(255) NOT NULL default '',
17 "cc" varchar(255) NOT NULL default '',
18 "date" datetime NOT NULL default '0000-00-00 00:00:00',
19 size integer NOT NULL default '0',
20 headers text NOT NULL,
24 CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid);
25 CREATE INDEX ix_users_username ON users(username);
26 CREATE INDEX ix_users_alias ON users(alias);
28 -- Updates from version 0.2-alpha
30 CREATE INDEX ix_messages_created ON messages (created);
32 -- Updates from version 0.2-beta
34 CREATE INDEX ix_session_changed ON session (changed);
35 CREATE INDEX ix_cache_created ON cache (created);
37 -- Updates from version 0.3-stable
40 DROP INDEX ix_messages_user_cache_uid;
41 CREATE UNIQUE INDEX ix_messages_user_cache_uid ON messages (user_id,cache_key,uid);
42 CREATE INDEX ix_messages_index ON messages (user_id,cache_key,idx);
43 DROP INDEX ix_contacts_user_id;
44 CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
46 -- Updates from version 0.3.1
48 -- ALTER TABLE identities ADD COLUMN changed datetime NOT NULL default '0000-00-00 00:00:00'; --
50 CREATE TABLE temp_identities (
51 identity_id integer NOT NULL PRIMARY KEY,
52 user_id integer NOT NULL default '0',
53 standard tinyint NOT NULL default '0',
54 name varchar(128) NOT NULL default '',
55 organization varchar(128) default '',
56 email varchar(128) NOT NULL default '',
57 "reply-to" varchar(128) NOT NULL default '',
58 bcc varchar(128) NOT NULL default '',
59 signature text NOT NULL default '',
60 html_signature tinyint NOT NULL default '0'
62 INSERT INTO temp_identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature)
63 SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature
64 FROM identities WHERE del=0;
66 DROP INDEX ix_identities_user_id;
67 DROP TABLE identities;
69 CREATE TABLE identities (
70 identity_id integer NOT NULL PRIMARY KEY,
71 user_id integer NOT NULL default '0',
72 changed datetime NOT NULL default '0000-00-00 00:00:00',
73 del tinyint NOT NULL default '0',
74 standard tinyint NOT NULL default '0',
75 name varchar(128) NOT NULL default '',
76 organization varchar(128) default '',
77 email varchar(128) NOT NULL default '',
78 "reply-to" varchar(128) NOT NULL default '',
79 bcc varchar(128) NOT NULL default '',
80 signature text NOT NULL default '',
81 html_signature tinyint NOT NULL default '0'
83 CREATE INDEX ix_identities_user_id ON identities(user_id, del);
85 INSERT INTO identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature)
86 SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature
89 DROP TABLE temp_identities;
91 CREATE TABLE contactgroups (
92 contactgroup_id integer NOT NULL PRIMARY KEY,
93 user_id integer NOT NULL default '0',
94 changed datetime NOT NULL default '0000-00-00 00:00:00',
95 del tinyint NOT NULL default '0',
96 name varchar(128) NOT NULL default ''
99 CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del);
101 CREATE TABLE contactgroupmembers (
102 contactgroup_id integer NOT NULL,
103 contact_id integer NOT NULL default '0',
104 created datetime NOT NULL default '0000-00-00 00:00:00',
105 PRIMARY KEY (contactgroup_id, contact_id)
108 -- Updates from version 0.3.1
110 CREATE TABLE tmp_users (
111 user_id integer NOT NULL PRIMARY KEY,
112 username varchar(128) NOT NULL default '',
113 mail_host varchar(128) NOT NULL default '',
114 alias varchar(128) NOT NULL default '',
115 created datetime NOT NULL default '0000-00-00 00:00:00',
116 last_login datetime NOT NULL default '0000-00-00 00:00:00',
118 preferences text NOT NULL default ''
121 INSERT INTO tmp_users (user_id, username, mail_host, alias, created, last_login, language, preferences)
122 SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM users;
127 user_id integer NOT NULL PRIMARY KEY,
128 username varchar(128) NOT NULL default '',
129 mail_host varchar(128) NOT NULL default '',
130 alias varchar(128) NOT NULL default '',
131 created datetime NOT NULL default '0000-00-00 00:00:00',
132 last_login datetime DEFAULT NULL,
134 preferences text NOT NULL default ''
137 INSERT INTO users (user_id, username, mail_host, alias, created, last_login, language, preferences)
138 SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM tmp_users;
140 CREATE INDEX ix_users_username ON users(username);
141 CREATE INDEX ix_users_alias ON users(alias);
142 DROP TABLE tmp_users;
144 -- Updates from version 0.4.2
146 DROP INDEX ix_users_username;
147 CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
149 CREATE TABLE contacts_tmp (
150 contact_id integer NOT NULL PRIMARY KEY,
151 user_id integer NOT NULL default '0',
152 changed datetime NOT NULL default '0000-00-00 00:00:00',
153 del tinyint NOT NULL default '0',
154 name varchar(128) NOT NULL default '',
155 email varchar(255) NOT NULL default '',
156 firstname varchar(128) NOT NULL default '',
157 surname varchar(128) NOT NULL default '',
158 vcard text NOT NULL default ''
161 INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
162 SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts;
165 CREATE TABLE contacts (
166 contact_id integer NOT NULL PRIMARY KEY,
167 user_id integer NOT NULL default '0',
168 changed datetime NOT NULL default '0000-00-00 00:00:00',
169 del tinyint NOT NULL default '0',
170 name varchar(128) NOT NULL default '',
171 email varchar(255) NOT NULL default '',
172 firstname varchar(128) NOT NULL default '',
173 surname varchar(128) NOT NULL default '',
174 vcard text NOT NULL default ''
177 INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
178 SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp;
180 CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
181 DROP TABLE contacts_tmp;
183 DELETE FROM messages;
186 -- Updates from version 0.5.1
187 -- Updates from version 0.5.2
188 -- Updates from version 0.5.3
189 -- Updates from version 0.5.4
191 CREATE TABLE contacts_tmp (
192 contact_id integer NOT NULL PRIMARY KEY,
193 user_id integer NOT NULL default '0',
194 changed datetime NOT NULL default '0000-00-00 00:00:00',
195 del tinyint NOT NULL default '0',
196 name varchar(128) NOT NULL default '',
197 email varchar(255) NOT NULL default '',
198 firstname varchar(128) NOT NULL default '',
199 surname varchar(128) NOT NULL default '',
200 vcard text NOT NULL default ''
203 INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
204 SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts;
207 CREATE TABLE contacts (
208 contact_id integer NOT NULL PRIMARY KEY,
209 user_id integer NOT NULL default '0',
210 changed datetime NOT NULL default '0000-00-00 00:00:00',
211 del tinyint NOT NULL default '0',
212 name varchar(128) NOT NULL default '',
213 email varchar(255) NOT NULL default '',
214 firstname varchar(128) NOT NULL default '',
215 surname varchar(128) NOT NULL default '',
216 vcard text NOT NULL default '',
217 words text NOT NULL default ''
220 INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
221 SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp;
223 CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
224 DROP TABLE contacts_tmp;
227 DELETE FROM messages;
229 CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id);
231 -- Updates from version 0.6
233 CREATE TABLE dictionary (
234 user_id integer DEFAULT NULL,
235 "language" varchar(5) NOT NULL,
239 CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language");
241 CREATE TABLE searches (
242 search_id integer NOT NULL PRIMARY KEY,
243 user_id integer NOT NULL DEFAULT '0',
244 "type" smallint NOT NULL DEFAULT '0',
245 name varchar(128) NOT NULL,
249 CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name);
253 CREATE TABLE cache_index (
254 user_id integer NOT NULL,
255 mailbox varchar(255) NOT NULL,
256 changed datetime NOT NULL default '0000-00-00 00:00:00',
257 valid smallint NOT NULL DEFAULT '0',
259 PRIMARY KEY (user_id, mailbox)
262 CREATE INDEX ix_cache_index_changed ON cache_index (changed);
264 CREATE TABLE cache_thread (
265 user_id integer NOT NULL,
266 mailbox varchar(255) NOT NULL,
267 changed datetime NOT NULL default '0000-00-00 00:00:00',
269 PRIMARY KEY (user_id, mailbox)
272 CREATE INDEX ix_cache_thread_changed ON cache_thread (changed);
274 CREATE TABLE cache_messages (
275 user_id integer NOT NULL,
276 mailbox varchar(255) NOT NULL,
277 uid integer NOT NULL,
278 changed datetime NOT NULL default '0000-00-00 00:00:00',
280 flags integer NOT NULL DEFAULT '0',
281 PRIMARY KEY (user_id, mailbox, uid)
284 CREATE INDEX ix_cache_messages_changed ON cache_messages (changed);
286 -- Updates from version 0.7-beta
289 CREATE TABLE session (
290 sess_id varchar(128) NOT NULL PRIMARY KEY,
291 created datetime NOT NULL default '0000-00-00 00:00:00',
292 changed datetime NOT NULL default '0000-00-00 00:00:00',
293 ip varchar(40) NOT NULL default '',
296 CREATE INDEX ix_session_changed ON session (changed);
298 -- Updates from version 0.7
300 CREATE TABLE contacts_tmp (
301 contact_id integer NOT NULL PRIMARY KEY,
302 user_id integer NOT NULL,
303 changed datetime NOT NULL default '0000-00-00 00:00:00',
304 del tinyint NOT NULL default '0',
305 name varchar(128) NOT NULL default '',
306 email text NOT NULL default '',
307 firstname varchar(128) NOT NULL default '',
308 surname varchar(128) NOT NULL default '',
309 vcard text NOT NULL default '',
310 words text NOT NULL default ''
313 INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words)
314 SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts;
318 CREATE TABLE contacts (
319 contact_id integer NOT NULL PRIMARY KEY,
320 user_id integer NOT NULL,
321 changed datetime NOT NULL default '0000-00-00 00:00:00',
322 del tinyint NOT NULL default '0',
323 name varchar(128) NOT NULL default '',
324 email text NOT NULL default '',
325 firstname varchar(128) NOT NULL default '',
326 surname varchar(128) NOT NULL default '',
327 vcard text NOT NULL default '',
328 words text NOT NULL default ''
331 INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words)
332 SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts_tmp;
334 CREATE INDEX ix_contacts_user_id ON contacts(user_id, del);
335 DROP TABLE contacts_tmp;