summaryrefslogtreecommitdiff
path: root/SQL
diff options
context:
space:
mode:
Diffstat (limited to 'SQL')
-rw-r--r--SQL/mssql.initial.sql3
-rw-r--r--SQL/mssql.upgrade.sql6
-rw-r--r--SQL/mysql.initial.sql1
-rw-r--r--SQL/mysql.update.sql3
-rw-r--r--SQL/postgres.initial.sql5
-rw-r--r--SQL/postgres.update.sql6
-rw-r--r--SQL/sqlite.initial.sql3
-rw-r--r--SQL/sqlite.update.sql39
8 files changed, 61 insertions, 5 deletions
diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql
index 823d1b3ae..4e31c07d0 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -16,7 +16,8 @@ CREATE TABLE [dbo].[contacts] (
[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
+ [vcard] [text] COLLATE Latin1_General_CI_AI NULL ,
+ [words] [text] COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
diff --git a/SQL/mssql.upgrade.sql b/SQL/mssql.upgrade.sql
index 4072c25ff..3d067924c 100644
--- a/SQL/mssql.upgrade.sql
+++ b/SQL/mssql.upgrade.sql
@@ -96,4 +96,8 @@ CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host])
GO
ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL
GO
-
+
+-- Updates from version 0.5.x
+
+ALTER TABLE [dbo].[contacts] ADD [words] [text] COLLATE Latin1_General_CI_AI NULL
+GO
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index 6e2c24731..accf212d4 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -87,6 +87,7 @@ CREATE TABLE `contacts` (
`firstname` varchar(128) NOT NULL DEFAULT '',
`surname` varchar(128) NOT NULL DEFAULT '',
`vcard` text NULL,
+ `words` text NULL,
`user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY(`contact_id`),
CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql
index aaab43fb2..f6c8d7869 100644
--- a/SQL/mysql.update.sql
+++ b/SQL/mysql.update.sql
@@ -133,3 +133,6 @@ ALTER TABLE `contacts` MODIFY `email` varchar(255) NOT NULL;
TRUNCATE TABLE `messages`;
+-- Updates from version 0.5.*
+
+ALTER TABLE `contacts` ADD `words` TEXT NULL AFTER `vcard`;
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index 089cae036..f52ebd572 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -103,14 +103,15 @@ CREATE SEQUENCE contact_ids
CREATE TABLE contacts (
contact_id integer DEFAULT nextval('contact_ids'::text) PRIMARY KEY,
user_id integer NOT NULL
- REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
changed timestamp with time zone DEFAULT now() NOT NULL,
del smallint DEFAULT 0 NOT NULL,
name 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
+ vcard text,
+ words text
);
CREATE INDEX contacts_user_id_idx ON contacts (user_id, email);
diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql
index 2f4498d36..6c3838868 100644
--- a/SQL/postgres.update.sql
+++ b/SQL/postgres.update.sql
@@ -89,3 +89,9 @@ ALTER TABLE users ADD UNIQUE (username, mail_host);
ALTER TABLE contacts ALTER email TYPE varchar(255);
TRUNCATE messages;
+
+
+-- Updates from version 0.5.x
+
+ALTER TABLE contacts ADD words TEXT NULL;
+
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index 875b3cba0..3aacccb95 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -31,7 +31,8 @@ CREATE TABLE contacts (
email varchar(255) NOT NULL default '',
firstname varchar(128) NOT NULL default '',
surname varchar(128) NOT NULL default '',
- vcard text NOT NULL default ''
+ vcard text NOT NULL default '',
+ words text NOT NULL default ''
);
CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql
index 6f2acf913..46373d8b0 100644
--- a/SQL/sqlite.update.sql
+++ b/SQL/sqlite.update.sql
@@ -182,3 +182,42 @@ DROP TABLE contacts_tmp;
DELETE FROM messages;
+
+-- 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;