summaryrefslogtreecommitdiff
path: root/SQL
diff options
context:
space:
mode:
Diffstat (limited to 'SQL')
-rw-r--r--SQL/mssql.initial.sql29
-rw-r--r--SQL/mssql.upgrade.sql30
-rw-r--r--SQL/mysql.initial.sql16
-rw-r--r--SQL/mysql.update.sql12
-rw-r--r--SQL/postgres.initial.sql26
-rw-r--r--SQL/postgres.update.sql16
-rw-r--r--SQL/sqlite.initial.sql15
-rw-r--r--SQL/sqlite.update.sql11
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);
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-25 16:41:23 +0000