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;