Fulltext search over contact fields. Attention: DATABASE SCHEMA CHANGED\!

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;