summaryrefslogtreecommitdiff
path: root/SQL
diff options
context:
space:
mode:
authoralecpl <alec@alec.pl>2010-11-02 09:27:03 +0000
committeralecpl <alec@alec.pl>2010-11-02 09:27:03 +0000
commit6f096812c9fd460fddd21ff1cef55542cb79a890 (patch)
treee69c88fc0b3da2cf8da7e19f3b1e24b230c1d59c /SQL
parentd3664623169c8db7a83b4049e4d3ef7b8714a923 (diff)
- Support contact's email addresses up to 255 characters long (#1487095)
- Added email format checks when saving contacts data
Diffstat (limited to 'SQL')
-rw-r--r--SQL/mssql.initial.sql2
-rw-r--r--SQL/mssql.upgrade.sql4
-rw-r--r--SQL/mysql.initial.sql2
-rw-r--r--SQL/mysql.update.sql2
-rw-r--r--SQL/postgres.initial.sql2
-rw-r--r--SQL/postgres.update.sql1
-rw-r--r--SQL/sqlite.initial.sql2
-rw-r--r--SQL/sqlite.update.sql34
8 files changed, 44 insertions, 5 deletions
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;
+