From b7e7c8f9501850a38705e0a1f18a8ae6e25f1be1 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Wed, 9 Jan 2013 15:57:29 +0100 Subject: Added new database upgrade script, converted DDL scripts to new format --- SQL/sqlite/2008030300.sql | 25 +++++++++++++++++++ SQL/sqlite/2008060900.sql | 3 +++ SQL/sqlite/2008092100.sql | 4 ++++ SQL/sqlite/2009090400.sql | 8 +++++++ SQL/sqlite/2009103100.sql | 61 +++++++++++++++++++++++++++++++++++++++++++++++ SQL/sqlite/2010042300.sql | 35 +++++++++++++++++++++++++++ SQL/sqlite/2010100600.sql | 40 +++++++++++++++++++++++++++++++ SQL/sqlite/2011011200.sql | 41 +++++++++++++++++++++++++++++++ SQL/sqlite/2011092800.sql | 54 +++++++++++++++++++++++++++++++++++++++++ SQL/sqlite/2011111600.sql | 11 +++++++++ SQL/sqlite/2011121400.sql | 38 +++++++++++++++++++++++++++++ SQL/sqlite/2012080700.sql | 44 ++++++++++++++++++++++++++++++++++ SQL/sqlite/2013011000.sql | 6 +++++ 13 files changed, 370 insertions(+) create mode 100644 SQL/sqlite/2008030300.sql create mode 100644 SQL/sqlite/2008060900.sql create mode 100644 SQL/sqlite/2008092100.sql create mode 100644 SQL/sqlite/2009090400.sql create mode 100644 SQL/sqlite/2009103100.sql create mode 100644 SQL/sqlite/2010042300.sql create mode 100644 SQL/sqlite/2010100600.sql create mode 100644 SQL/sqlite/2011011200.sql create mode 100644 SQL/sqlite/2011092800.sql create mode 100644 SQL/sqlite/2011111600.sql create mode 100644 SQL/sqlite/2011121400.sql create mode 100644 SQL/sqlite/2012080700.sql create mode 100644 SQL/sqlite/2013011000.sql (limited to 'SQL/sqlite') diff --git a/SQL/sqlite/2008030300.sql b/SQL/sqlite/2008030300.sql new file mode 100644 index 000000000..b5b4890bf --- /dev/null +++ b/SQL/sqlite/2008030300.sql @@ -0,0 +1,25 @@ +-- Updates from version 0.1-stable to 0.1.1 + +DROP TABLE messages; + +CREATE TABLE messages ( + message_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + del tinyint NOT NULL default '0', + cache_key varchar(128) NOT NULL default '', + created datetime NOT NULL default '0000-00-00 00:00:00', + idx integer NOT NULL default '0', + uid integer NOT NULL default '0', + subject varchar(255) NOT NULL default '', + "from" varchar(255) NOT NULL default '', + "to" varchar(255) NOT NULL default '', + "cc" varchar(255) NOT NULL default '', + "date" datetime NOT NULL default '0000-00-00 00:00:00', + size integer NOT NULL default '0', + headers text NOT NULL, + structure text +); + +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); diff --git a/SQL/sqlite/2008060900.sql b/SQL/sqlite/2008060900.sql new file mode 100644 index 000000000..157029c28 --- /dev/null +++ b/SQL/sqlite/2008060900.sql @@ -0,0 +1,3 @@ +-- Updates from version 0.2-alpha + +CREATE INDEX ix_messages_created ON messages (created); diff --git a/SQL/sqlite/2008092100.sql b/SQL/sqlite/2008092100.sql new file mode 100644 index 000000000..8bc081c79 --- /dev/null +++ b/SQL/sqlite/2008092100.sql @@ -0,0 +1,4 @@ +-- Updates from version 0.2-beta + +CREATE INDEX ix_session_changed ON session (changed); +CREATE INDEX ix_cache_created ON cache (created); diff --git a/SQL/sqlite/2009090400.sql b/SQL/sqlite/2009090400.sql new file mode 100644 index 000000000..c8a53edf1 --- /dev/null +++ b/SQL/sqlite/2009090400.sql @@ -0,0 +1,8 @@ +-- 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); diff --git a/SQL/sqlite/2009103100.sql b/SQL/sqlite/2009103100.sql new file mode 100644 index 000000000..bd7b1741c --- /dev/null +++ b/SQL/sqlite/2009103100.sql @@ -0,0 +1,61 @@ +-- 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) +); diff --git a/SQL/sqlite/2010042300.sql b/SQL/sqlite/2010042300.sql new file mode 100644 index 000000000..1a9023cfc --- /dev/null +++ b/SQL/sqlite/2010042300.sql @@ -0,0 +1,35 @@ +-- Updates from version 0.4-beta + +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; diff --git a/SQL/sqlite/2010100600.sql b/SQL/sqlite/2010100600.sql new file mode 100644 index 000000000..5c7259f37 --- /dev/null +++ b/SQL/sqlite/2010100600.sql @@ -0,0 +1,40 @@ +-- 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; diff --git a/SQL/sqlite/2011011200.sql b/SQL/sqlite/2011011200.sql new file mode 100644 index 000000000..4cca74076 --- /dev/null +++ b/SQL/sqlite/2011011200.sql @@ -0,0 +1,41 @@ +-- Updates from version 0.5.x + +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 '', + words 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; +DELETE FROM cache; +CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id); diff --git a/SQL/sqlite/2011092800.sql b/SQL/sqlite/2011092800.sql new file mode 100644 index 000000000..27dbffc1d --- /dev/null +++ b/SQL/sqlite/2011092800.sql @@ -0,0 +1,54 @@ +-- Updates from version 0.6 + +CREATE TABLE dictionary ( + user_id integer DEFAULT NULL, + "language" varchar(5) NOT NULL, + data text NOT NULL +); + +CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language"); + +CREATE TABLE searches ( + search_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL DEFAULT '0', + "type" smallint NOT NULL DEFAULT '0', + name varchar(128) NOT NULL, + data text NOT NULL +); + +CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name); + +DROP TABLE messages; + +CREATE TABLE cache_index ( + user_id integer NOT NULL, + mailbox varchar(255) NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + valid smallint NOT NULL DEFAULT '0', + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX ix_cache_index_changed ON cache_index (changed); + +CREATE TABLE cache_thread ( + user_id integer NOT NULL, + mailbox varchar(255) NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX ix_cache_thread_changed ON cache_thread (changed); + +CREATE TABLE cache_messages ( + user_id integer NOT NULL, + mailbox varchar(255) NOT NULL, + uid integer NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + data text NOT NULL, + flags integer NOT NULL DEFAULT '0', + PRIMARY KEY (user_id, mailbox, uid) +); + +CREATE INDEX ix_cache_messages_changed ON cache_messages (changed); diff --git a/SQL/sqlite/2011111600.sql b/SQL/sqlite/2011111600.sql new file mode 100644 index 000000000..cea0d129e --- /dev/null +++ b/SQL/sqlite/2011111600.sql @@ -0,0 +1,11 @@ +-- Updates from version 0.7-beta + +DROP TABLE session; +CREATE TABLE session ( + sess_id varchar(128) NOT NULL PRIMARY KEY, + created datetime NOT NULL default '0000-00-00 00:00:00', + changed datetime NOT NULL default '0000-00-00 00:00:00', + ip varchar(40) NOT NULL default '', + vars text NOT NULL +); +CREATE INDEX ix_session_changed ON session (changed); diff --git a/SQL/sqlite/2011121400.sql b/SQL/sqlite/2011121400.sql new file mode 100644 index 000000000..9d9227673 --- /dev/null +++ b/SQL/sqlite/2011121400.sql @@ -0,0 +1,38 @@ +-- Updates from version 0.7 + +CREATE TABLE contacts_tmp ( + contact_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + email text NOT NULL default '', + firstname varchar(128) NOT NULL default '', + surname varchar(128) NOT NULL default '', + vcard text NOT NULL default '', + words text NOT NULL default '' +); + +INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words) + SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts; + +DROP TABLE contacts; + +CREATE TABLE contacts ( + contact_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + email text NOT NULL default '', + firstname varchar(128) NOT NULL default '', + surname varchar(128) NOT NULL default '', + vcard text NOT NULL default '', + words text NOT NULL default '' +); + +INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words) + SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts_tmp; + +CREATE INDEX ix_contacts_user_id ON contacts(user_id, del); +DROP TABLE contacts_tmp; diff --git a/SQL/sqlite/2012080700.sql b/SQL/sqlite/2012080700.sql new file mode 100644 index 000000000..c6ede89e8 --- /dev/null +++ b/SQL/sqlite/2012080700.sql @@ -0,0 +1,44 @@ +-- Updates from version 0.8 + +DROP TABLE cache; +CREATE TABLE cache ( + user_id integer NOT NULL default 0, + cache_key varchar(128) NOT NULL default '', + created datetime NOT NULL default '0000-00-00 00:00:00', + data text NOT NULL +); + +CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key); +CREATE INDEX ix_cache_created ON cache(created); + +CREATE TABLE tmp_users ( + user_id integer NOT NULL PRIMARY KEY, + username varchar(128) NOT NULL default '', + mail_host 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 tmp_users (user_id, username, mail_host, created, last_login, language, preferences) + SELECT user_id, username, mail_host, 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 '', + 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, created, last_login, language, preferences) + SELECT user_id, username, mail_host, created, last_login, language, preferences FROM tmp_users; + +CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host); + +CREATE INDEX ix_identities_email ON identities(email, del); diff --git a/SQL/sqlite/2013011000.sql b/SQL/sqlite/2013011000.sql new file mode 100644 index 000000000..2c66ef95e --- /dev/null +++ b/SQL/sqlite/2013011000.sql @@ -0,0 +1,6 @@ +-- Updates from version 0.9-beta + +CREATE TABLE system ( + name varchar(64) NOT NULL PRIMARY KEY, + value text NOT NULL +); -- cgit v1.2.3