X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=SQL%2Fsqlite.update.sql;h=6f2acf91385c7a41b5e129bf8fac900ba63ba51a;hb=d832c6b37ce0cba5a2bc015b1c806ba6990c4253;hp=047fe6731777bd0f743641057f6c830db712384d;hpb=5b5ddde1194997b1012f19da396f2861db3396a2;p=roundcube.git diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql index 047fe67..6f2acf9 100644 --- a/SQL/sqlite.update.sql +++ b/SQL/sqlite.update.sql @@ -1,4 +1,4 @@ --- RoundCube Webmail update script for SQLite databases +-- Roundcube Webmail update script for SQLite databases -- Updates from version 0.1-stable to 0.1.1 DROP TABLE messages; @@ -22,6 +22,163 @@ CREATE TABLE messages ( ); CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid); +CREATE INDEX ix_users_username ON users(username); +CREATE INDEX ix_users_alias ON users(alias); + +-- Updates from version 0.2-alpha + +CREATE INDEX ix_messages_created ON messages (created); + +-- Updates from version 0.2-beta + +CREATE INDEX ix_session_changed ON session (changed); +CREATE INDEX ix_cache_created ON cache (created); + +-- Updates from version 0.3-stable + +DELETE FROM messages; +DROP INDEX ix_messages_user_cache_uid; +CREATE UNIQUE INDEX ix_messages_user_cache_uid ON messages (user_id,cache_key,uid); +CREATE INDEX ix_messages_index ON messages (user_id,cache_key,idx); +DROP INDEX ix_contacts_user_id; +CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); + +-- Updates from version 0.3.1 + +-- ALTER TABLE identities ADD COLUMN changed datetime NOT NULL default '0000-00-00 00:00:00'; -- + +CREATE TABLE temp_identities ( + identity_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + standard tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + organization varchar(128) default '', + email varchar(128) NOT NULL default '', + "reply-to" varchar(128) NOT NULL default '', + bcc varchar(128) NOT NULL default '', + signature text NOT NULL default '', + html_signature tinyint NOT NULL default '0' +); +INSERT INTO temp_identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature) + SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature + FROM identities WHERE del=0; + +DROP INDEX ix_identities_user_id; +DROP TABLE identities; + +CREATE TABLE identities ( + identity_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + standard tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + organization varchar(128) default '', + email varchar(128) NOT NULL default '', + "reply-to" varchar(128) NOT NULL default '', + bcc varchar(128) NOT NULL default '', + signature text NOT NULL default '', + html_signature tinyint NOT NULL default '0' +); +CREATE INDEX ix_identities_user_id ON identities(user_id, del); + +INSERT INTO identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature) + SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature + FROM temp_identities; + +DROP TABLE temp_identities; + +CREATE TABLE contactgroups ( + contactgroup_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '' +); + +CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del); + +CREATE TABLE contactgroupmembers ( + contactgroup_id integer NOT NULL, + contact_id integer NOT NULL default '0', + created datetime NOT NULL default '0000-00-00 00:00:00', + PRIMARY KEY (contactgroup_id, contact_id) +); + +-- Updates from version 0.3.1 + +CREATE TABLE tmp_users ( + user_id integer NOT NULL PRIMARY KEY, + username varchar(128) NOT NULL default '', + mail_host varchar(128) NOT NULL default '', + alias varchar(128) NOT NULL default '', + created datetime NOT NULL default '0000-00-00 00:00:00', + last_login datetime NOT NULL default '0000-00-00 00:00:00', + language varchar(5), + preferences text NOT NULL default '' +); + +INSERT INTO tmp_users (user_id, username, mail_host, alias, created, last_login, language, preferences) + SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM users; + +DROP TABLE users; + +CREATE TABLE users ( + user_id integer NOT NULL PRIMARY KEY, + username varchar(128) NOT NULL default '', + mail_host varchar(128) NOT NULL default '', + alias varchar(128) NOT NULL default '', + created datetime NOT NULL default '0000-00-00 00:00:00', + last_login datetime DEFAULT NULL, + language varchar(5), + preferences text NOT NULL default '' +); + +INSERT INTO users (user_id, username, mail_host, alias, created, last_login, language, preferences) + SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM tmp_users; CREATE INDEX ix_users_username ON users(username); CREATE INDEX ix_users_alias ON users(alias); +DROP TABLE tmp_users; + +-- Updates from version 0.4.2 + +DROP INDEX ix_users_username; +CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host); + +CREATE TABLE contacts_tmp ( + contact_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + email varchar(255) NOT NULL default '', + firstname varchar(128) NOT NULL default '', + surname varchar(128) NOT NULL default '', + vcard text NOT NULL default '' +); + +INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard) + SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts; + +DROP TABLE contacts; +CREATE TABLE contacts ( + contact_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + email varchar(255) NOT NULL default '', + firstname varchar(128) NOT NULL default '', + surname varchar(128) NOT NULL default '', + vcard text NOT NULL default '' +); + +INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard) + SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp; + +CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); +DROP TABLE contacts_tmp; + +DELETE FROM messages; +