From f8e48df71540b268ceac058d32b8ee848fc2ab6b Mon Sep 17 00:00:00 2001 From: alecpl Date: Tue, 6 Sep 2011 16:35:14 +0000 Subject: - Merge devel-saved_search branch (Addressbook Saved Searches) --- SQL/mssql.initial.sql | 29 +++++++++++++++++++++++++++++ SQL/mssql.upgrade.sql | 30 ++++++++++++++++++++++++++++++ SQL/mysql.initial.sql | 16 ++++++++++++++++ SQL/mysql.update.sql | 12 ++++++++++++ SQL/postgres.initial.sql | 26 ++++++++++++++++++++++++++ SQL/postgres.update.sql | 16 ++++++++++++++++ SQL/sqlite.initial.sql | 15 +++++++++++++++ SQL/sqlite.update.sql | 11 +++++++++++ 8 files changed, 155 insertions(+) (limited to 'SQL') diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql index 321da7cbb..8e103eb55 100644 --- a/SQL/mssql.initial.sql +++ b/SQL/mssql.initial.sql @@ -100,6 +100,15 @@ CREATE TABLE [dbo].[dictionary] ( ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO +CREATE TABLE [dbo].[searches] ( + [search_id] [int] IDENTITY (1, 1) NOT NULL , + [user_id] [int] NOT NULL , + [type] [tinyint] NOT NULL , + [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , + [data] [text] COLLATE Latin1_General_CI_AI NOT NULL +) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] +GO + ALTER TABLE [dbo].[cache] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( @@ -156,6 +165,13 @@ ALTER TABLE [dbo].[users] WITH NOCHECK ADD ) ON [PRIMARY] GO +ALTER TABLE [dbo].[searches] WITH NOCHECK ADD + CONSTRAINT [PK_searches_search_id] PRIMARY KEY CLUSTERED + ( + [search_id] + ) ON [PRIMARY] +GO + ALTER TABLE [dbo].[cache] ADD CONSTRAINT [DF_cache_user_id] DEFAULT ('0') FOR [user_id], CONSTRAINT [DF_cache_cache_key] DEFAULT ('') FOR [cache_key], @@ -274,6 +290,14 @@ GO CREATE UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY] GO +ALTER TABLE [dbo].[searches] ADD + CONSTRAINT [DF_searches_user] DEFAULT (0) FOR [user_id], + CONSTRAINT [DF_searches_type] DEFAULT (0) FOR [type], +GO + +CREATE UNIQUE INDEX [IX_searches_user_type_name] ON [dbo].[searches]([user_id],[type],[name]) ON [PRIMARY] +GO + ALTER TABLE [dbo].[identities] ADD CONSTRAINT [FK_identities_user_id] FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) ON DELETE CASCADE ON UPDATE CASCADE @@ -304,6 +328,11 @@ ALTER TABLE [dbo].[contactgroupmembers] ADD CONSTRAINT [FK_contactgroupmembers_c ON DELETE CASCADE ON UPDATE CASCADE GO +ALTER TABLE [dbo].[searches] ADD CONSTRAINT [FK_searches_user_id] + FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) + ON DELETE CASCADE ON UPDATE CASCADE +GO + -- Use trigger instead of foreign key (#1487112) -- "Introducing FOREIGN KEY constraint ... may cause cycles or multiple cascade paths." CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts] diff --git a/SQL/mssql.upgrade.sql b/SQL/mssql.upgrade.sql index a77362ac4..258b1d78a 100644 --- a/SQL/mssql.upgrade.sql +++ b/SQL/mssql.upgrade.sql @@ -121,3 +121,33 @@ CREATE TABLE [dbo].[dictionary] ( GO CREATE UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY] GO + +CREATE TABLE [dbo].[searches] ( + [search_id] [int] IDENTITY (1, 1) NOT NULL , + [user_id] [int] NOT NULL , + [type] [tinyint] NOT NULL , + [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , + [data] [text] COLLATE Latin1_General_CI_AI NOT NULL +) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] +GO + +ALTER TABLE [dbo].[searches] WITH NOCHECK ADD + CONSTRAINT [PK_searches_search_id] PRIMARY KEY CLUSTERED + ( + [search_id] + ) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[searches] ADD + CONSTRAINT [DF_searches_user] DEFAULT (0) FOR [user_id], + CONSTRAINT [DF_searches_type] DEFAULT (0) FOR [type], +GO + +CREATE UNIQUE INDEX [IX_searches_user_type_name] ON [dbo].[searches]([user_id],[type],[name]) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[searches] ADD CONSTRAINT [FK_searches_user_id] + FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) + ON DELETE CASCADE ON UPDATE CASCADE +GO + diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index 4cabb8132..6c2669009 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -155,4 +155,20 @@ CREATE TABLE `dictionary` ( UNIQUE `uniqueness` (`user_id`, `language`) ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + +-- Table structure for table `searches` + +CREATE TABLE `searches` ( + `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `type` int(3) NOT NULL DEFAULT '0', + `name` varchar(128) NOT NULL, + `data` text, + PRIMARY KEY(`search_id`), + CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + UNIQUE `uniqueness` (`user_id`, `type`, `name`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + + /*!40014 SET FOREIGN_KEY_CHECKS=1 */; diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql index ee3929c4c..afeb3a528 100644 --- a/SQL/mysql.update.sql +++ b/SQL/mysql.update.sql @@ -155,3 +155,15 @@ CREATE TABLE `dictionary` ( REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE `uniqueness` (`user_id`, `language`) ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + +CREATE TABLE `searches` ( + `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `type` int(3) NOT NULL DEFAULT '0', + `name` varchar(128) NOT NULL, + `data` text, + PRIMARY KEY(`search_id`), + CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + UNIQUE `uniqueness` (`user_id`, `type`, `name`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index c801a773c..01221c4e3 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -238,3 +238,29 @@ CREATE TABLE dictionary ( data text NOT NULL, CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language") ); + +-- +-- Sequence "searches_ids" +-- Name: searches_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE search_ids + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + +-- +-- Table "searches" +-- Name: searches; Type: TABLE; Schema: public; Owner: postgres +-- + +CREATE TABLE searches ( + search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY, + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + "type" smallint DEFAULT 0 NOT NULL, + name varchar(128) NOT NULL, + data text NOT NULL, + CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name) +); diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql index 0a2ed99dd..e316ff540 100644 --- a/SQL/postgres.update.sql +++ b/SQL/postgres.update.sql @@ -110,3 +110,19 @@ CREATE TABLE dictionary ( data text NOT NULL, CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language") ); + +CREATE SEQUENCE search_ids + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + +CREATE TABLE searches ( + search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY, + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + "type" smallint DEFAULT 0 NOT NULL, + name varchar(128) NOT NULL, + data text NOT NULL, + CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name) +); diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index 337dfbe8d..46ee5301b 100644 --- a/SQL/sqlite.initial.sql +++ b/SQL/sqlite.initial.sql @@ -161,3 +161,18 @@ CREATE TABLE dictionary ( CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language"); +-- -------------------------------------------------------- + +-- +-- Table structure for table searches +-- + +CREATE TABLE searches ( + search_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL DEFAULT '0', + "type" smallint NOT NULL DEFAULT '0', + name varchar(128) NOT NULL, + data text NOT NULL +); + +CREATE UNIQUE INDEX ix_searches_user_type_name (user_id, type, name); diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql index 8d5163f47..41ab0200d 100644 --- a/SQL/sqlite.update.sql +++ b/SQL/sqlite.update.sql @@ -227,6 +227,7 @@ DELETE FROM messages; DELETE FROM cache; CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id); + -- Updates from version 0.6-stable CREATE TABLE dictionary ( @@ -236,3 +237,13 @@ CREATE TABLE dictionary ( ); CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language"); + +CREATE TABLE searches ( + search_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL DEFAULT '0', + "type" smallint NOT NULL DEFAULT '0', + name varchar(128) NOT NULL, + data text NOT NULL +); + +CREATE UNIQUE INDEX ix_searches_user_type_name (user_id, type, name); -- cgit v1.2.3