From 23362230b59e89c63743c8d174f0fe11533e59b2 Mon Sep 17 00:00:00 2001 From: thomascube Date: Tue, 20 Apr 2010 07:26:11 +0000 Subject: Replace ALTER TABLE statement in Sqlite schema update script; more precise instructions for db updates and about new cleaning script --- SQL/sqlite.update.sql | 42 ++++++++++++++++++++++++++++++++++++++++-- 1 file changed, 40 insertions(+), 2 deletions(-) (limited to 'SQL') diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql index dd2887ca2..4b9b2f92e 100644 --- a/SQL/sqlite.update.sql +++ b/SQL/sqlite.update.sql @@ -45,10 +45,48 @@ 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; -CREATE INDEX ix_identities_user_id ON identities (user_id, del); +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; -ALTER TABLE identities ADD COLUMN changed datetime NOT NULL default '0000-00-00 00:00:00'; +DROP TABLE temp_identities; CREATE TABLE contactgroups ( contactgroup_id integer NOT NULL PRIMARY KEY, -- cgit v1.2.3