diff options
author | alecpl <alec@alec.pl> | 2011-09-06 20:35:14 +0400 |
---|---|---|
committer | alecpl <alec@alec.pl> | 2011-09-06 20:35:14 +0400 |
commit | f8e48df71540b268ceac058d32b8ee848fc2ab6b (patch) | |
tree | d2e8ea19086014e8a89f761a9444e72d84145164 /SQL | |
parent | 66df084203a217ab74a416064c459cc3420a648c (diff) |
- Merge devel-saved_search branch (Addressbook Saved Searches)
Diffstat (limited to 'SQL')
-rw-r--r-- | SQL/mssql.initial.sql | 29 | ||||
-rw-r--r-- | SQL/mssql.upgrade.sql | 30 | ||||
-rw-r--r-- | SQL/mysql.initial.sql | 16 | ||||
-rw-r--r-- | SQL/mysql.update.sql | 12 | ||||
-rw-r--r-- | SQL/postgres.initial.sql | 26 | ||||
-rw-r--r-- | SQL/postgres.update.sql | 16 | ||||
-rw-r--r-- | SQL/sqlite.initial.sql | 15 | ||||
-rw-r--r-- | SQL/sqlite.update.sql | 11 |
8 files changed, 155 insertions, 0 deletions
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); |