From 3e2637351da9559a4aa420004ac90e9fe30477ef Mon Sep 17 00:00:00 2001 From: thomascube Date: Mon, 14 Feb 2011 20:46:48 +0000 Subject: Fulltext search over contact fields. Attention: DATABASE SCHEMA CHANGED\! --- SQL/mssql.initial.sql | 3 ++- SQL/mssql.upgrade.sql | 6 +++++- SQL/mysql.initial.sql | 1 + SQL/mysql.update.sql | 3 +++ SQL/postgres.initial.sql | 5 +++-- SQL/postgres.update.sql | 6 ++++++ SQL/sqlite.initial.sql | 3 ++- SQL/sqlite.update.sql | 39 +++++++++++++++++++++++++++++++++++++++ 8 files changed, 61 insertions(+), 5 deletions(-) (limited to 'SQL') 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; -- cgit v1.2.3