From 6f096812c9fd460fddd21ff1cef55542cb79a890 Mon Sep 17 00:00:00 2001 From: alecpl Date: Tue, 2 Nov 2010 09:27:03 +0000 Subject: - Support contact's email addresses up to 255 characters long (#1487095) - Added email format checks when saving contacts data --- SQL/mssql.initial.sql | 2 +- SQL/mssql.upgrade.sql | 4 +++- SQL/mysql.initial.sql | 2 +- SQL/mysql.update.sql | 2 ++ SQL/postgres.initial.sql | 2 +- SQL/postgres.update.sql | 1 + SQL/sqlite.initial.sql | 2 +- SQL/sqlite.update.sql | 34 ++++++++++++++++++++++++++++++++++ 8 files changed, 44 insertions(+), 5 deletions(-) (limited to 'SQL') diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql index 6b031a256..36788585f 100644 --- a/SQL/mssql.initial.sql +++ b/SQL/mssql.initial.sql @@ -13,7 +13,7 @@ CREATE TABLE [dbo].[contacts] ( [changed] [datetime] NOT NULL , [del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL , [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , - [email] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , + [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , [firstname] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , [surname] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , [vcard] [text] COLLATE Latin1_General_CI_AI NULL diff --git a/SQL/mssql.upgrade.sql b/SQL/mssql.upgrade.sql index 6b3cd6d03..cf0c17157 100644 --- a/SQL/mssql.upgrade.sql +++ b/SQL/mssql.upgrade.sql @@ -22,7 +22,7 @@ ALTER TABLE [dbo].[identities] ADD CONSTRAINT [FK_identities_user_id] ON DELETE CASCADE ON UPDATE CASCADE GO -ALTER TABLE [dbo].[identities] add [changed] [datetime] NULL +ALTER TABLE [dbo].[identities] ADD [changed] [datetime] NULL GO CREATE TABLE [dbo].[contactgroups] ( @@ -93,4 +93,6 @@ DROP INDEX [IX_users_username] GO CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY] GO +ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL +GO diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index 456a48313..6e2c24731 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -83,7 +83,7 @@ CREATE TABLE `contacts` ( `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', `del` tinyint(1) NOT NULL DEFAULT '0', `name` varchar(128) NOT NULL DEFAULT '', - `email` varchar(128) NOT NULL, + `email` varchar(255) NOT NULL, `firstname` varchar(128) NOT NULL DEFAULT '', `surname` varchar(128) NOT NULL DEFAULT '', `vcard` text NULL, diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql index b9ab59ea9..2af0a47e6 100644 --- a/SQL/mysql.update.sql +++ b/SQL/mysql.update.sql @@ -129,3 +129,5 @@ UPDATE `users` SET `last_login` = NULL WHERE `last_login` = '1000-01-01 00:00:00 ALTER TABLE `users` DROP INDEX `username_index`; ALTER TABLE `users` ADD UNIQUE `username` (`username`, `mail_host`); +ALTER TABLE `contacts` MODIFY `email` varchar(255) NOT NULL; + diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index 84a9afd40..089cae036 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -107,7 +107,7 @@ CREATE TABLE contacts ( changed timestamp with time zone DEFAULT now() NOT NULL, del smallint DEFAULT 0 NOT NULL, name varchar(128) DEFAULT '' NOT NULL, - email varchar(128) DEFAULT '' NOT NULL, + email varchar(255) DEFAULT '' NOT NULL, firstname varchar(128) DEFAULT '' NOT NULL, surname varchar(128) DEFAULT '' NOT NULL, vcard text diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql index 99b9c0e29..444caf6f6 100644 --- a/SQL/postgres.update.sql +++ b/SQL/postgres.update.sql @@ -86,4 +86,5 @@ ALTER TABLE users ALTER last_login SET DEFAULT NULL; DROP INDEX users_username_id_idx; ALTER TABLE users ADD UNIQUE (username, mail_host); +ALTER TABLE contacts ALTER email TYPE varchar(255); diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index ea7650cb7..875b3cba0 100644 --- a/SQL/sqlite.initial.sql +++ b/SQL/sqlite.initial.sql @@ -28,7 +28,7 @@ CREATE TABLE contacts ( 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(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 '' diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql index 78e283e2d..328693944 100644 --- a/SQL/sqlite.update.sql +++ b/SQL/sqlite.update.sql @@ -146,3 +146,37 @@ DROP TABLE tmp_users; 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; + -- cgit v1.2.3