summaryrefslogtreecommitdiff
path: root/SQL
diff options
context:
space:
mode:
Diffstat (limited to 'SQL')
-rw-r--r--SQL/mssql.initial.sql7
-rw-r--r--SQL/mssql.upgrade.sql9
-rw-r--r--SQL/mysql.initial.sql7
-rw-r--r--SQL/mysql.update.sql4
-rw-r--r--SQL/postgres.initial.sql4
-rw-r--r--SQL/postgres.update.sql3
-rw-r--r--SQL/sqlite.initial.sql3
-rw-r--r--SQL/sqlite.update.sql32
8 files changed, 54 insertions, 15 deletions
diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql
index 9aa6e6321..85b8e4ef6 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -92,7 +92,6 @@ CREATE TABLE [dbo].[users] (
[user_id] [int] IDENTITY (1, 1) NOT NULL ,
[username] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[mail_host] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
- [alias] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[created] [datetime] NOT NULL ,
[last_login] [datetime] NULL ,
[language] [varchar] (5) COLLATE Latin1_General_CI_AI NULL ,
@@ -274,6 +273,8 @@ GO
CREATE INDEX [IX_identities_user_id] ON [dbo].[identities]([user_id]) ON [PRIMARY]
GO
+CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
+GO
ALTER TABLE [dbo].[session] ADD
CONSTRAINT [DF_session_sess_id] DEFAULT ('') FOR [sess_id],
@@ -287,16 +288,12 @@ GO
ALTER TABLE [dbo].[users] ADD
CONSTRAINT [DF_users_username] DEFAULT ('') FOR [username],
CONSTRAINT [DF_users_mail_host] DEFAULT ('') FOR [mail_host],
- CONSTRAINT [DF_users_alias] DEFAULT ('') FOR [alias],
CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created]
GO
CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY]
GO
-CREATE INDEX [IX_users_alias] ON [dbo].[users]([alias]) ON [PRIMARY]
-GO
-
CREATE UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY]
GO
diff --git a/SQL/mssql.upgrade.sql b/SQL/mssql.upgrade.sql
index d111ef3e7..26001e713 100644
--- a/SQL/mssql.upgrade.sql
+++ b/SQL/mssql.upgrade.sql
@@ -261,4 +261,13 @@ ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (8000) COLLATE Latin
GO
ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
GO
+
+-- Updates from version 0.8
+
+ALTER TABLE [dbo].[cache] DROP COLUMN [cache_id]
+GO
+ALTER TABLE [dbo].[users] DROP COLUMN [alias]
+GO
+CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
+GO
\ No newline at end of file
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index 46fa2c3cf..47d9db4a2 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -22,14 +22,12 @@ CREATE TABLE `users` (
`user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` varchar(128) BINARY NOT NULL,
`mail_host` varchar(128) NOT NULL,
- `alias` varchar(128) BINARY NOT NULL,
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`last_login` datetime DEFAULT NULL,
`language` varchar(5),
`preferences` text,
PRIMARY KEY(`user_id`),
- UNIQUE `username` (`username`, `mail_host`),
- INDEX `alias_index` (`alias`)
+ UNIQUE `username` (`username`, `mail_host`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
@@ -156,7 +154,8 @@ CREATE TABLE `identities` (
PRIMARY KEY(`identity_id`),
CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `user_identities_index` (`user_id`, `del`)
+ INDEX `user_identities_index` (`user_id`, `del`),
+ INDEX `email_identities_index` (`email`, `del`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql
index 3be65897d..237aa3e38 100644
--- a/SQL/mysql.update.sql
+++ b/SQL/mysql.update.sql
@@ -242,4 +242,6 @@ ALTER TABLE `searches` ALTER `user_id` DROP DEFAULT;
-- Updates from version 0.8
-ALTER TABLE cache DROP COLUMN cache_id;
+ALTER TABLE `cache` DROP COLUMN `cache_id`;
+ALTER TABLE `users` DROP COLUMN `alias`;
+ALTER TABLE `identities` ADD INDEX `email_identities_index` (`email`, `del`);
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index a47bec2ca..f7b2d96d9 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -20,7 +20,6 @@ CREATE TABLE users (
user_id integer DEFAULT nextval('user_ids'::text) PRIMARY KEY,
username varchar(128) DEFAULT '' NOT NULL,
mail_host varchar(128) DEFAULT '' NOT NULL,
- alias varchar(128) DEFAULT '' NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
last_login timestamp with time zone DEFAULT NULL,
"language" varchar(5),
@@ -28,9 +27,7 @@ CREATE TABLE users (
CONSTRAINT users_username_key UNIQUE (username, mail_host)
);
-CREATE INDEX users_alias_id_idx ON users (alias);
-
--
-- Table "session"
-- Name: session; Type: TABLE; Schema: public; Owner: postgres
@@ -81,6 +78,7 @@ CREATE TABLE identities (
);
CREATE INDEX identities_user_id_idx ON identities (user_id, del);
+CREATE INDEX identities_email_idx ON identities (email, del);
--
diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql
index a8a9cdda8..11ab93bfc 100644
--- a/SQL/postgres.update.sql
+++ b/SQL/postgres.update.sql
@@ -180,3 +180,6 @@ ALTER TABLE contacts ALTER email TYPE text;
ALTER TABLE cache DROP COLUMN cache_id;
DROP SEQUENCE cache_ids;
+
+ALTER TABLE users DROP COLUMN alias;
+CREATE INDEX identities_email_idx ON identities (email, del);
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index a3c38b939..f5b5615d8 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -80,6 +80,7 @@ CREATE TABLE identities (
);
CREATE INDEX ix_identities_user_id ON identities(user_id, del);
+CREATE INDEX ix_identities_email ON identities(email, del);
-- --------------------------------------------------------
@@ -92,7 +93,6 @@ CREATE TABLE users (
user_id integer NOT NULL PRIMARY KEY,
username varchar(128) NOT NULL default '',
mail_host varchar(128) NOT NULL default '',
- alias varchar(128) NOT NULL default '',
created datetime NOT NULL default '0000-00-00 00:00:00',
last_login datetime DEFAULT NULL,
language varchar(5),
@@ -100,7 +100,6 @@ CREATE TABLE users (
);
CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
-CREATE INDEX ix_users_alias ON users(alias);
-- --------------------------------------------------------
diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql
index ddadde3ea..72a29e9ae 100644
--- a/SQL/sqlite.update.sql
+++ b/SQL/sqlite.update.sql
@@ -346,3 +346,35 @@ CREATE TABLE cache (
CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
CREATE INDEX ix_cache_created ON cache(created);
+
+CREATE TABLE tmp_users (
+ user_id integer NOT NULL PRIMARY KEY,
+ username varchar(128) NOT NULL default '',
+ mail_host varchar(128) NOT NULL default '',
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ last_login datetime DEFAULT NULL,
+ language varchar(5),
+ preferences text NOT NULL default ''
+);
+
+INSERT INTO tmp_users (user_id, username, mail_host, created, last_login, language, preferences)
+ SELECT user_id, username, mail_host, created, last_login, language, preferences FROM users;
+
+DROP TABLE users;
+
+CREATE TABLE users (
+ user_id integer NOT NULL PRIMARY KEY,
+ username varchar(128) NOT NULL default '',
+ mail_host varchar(128) NOT NULL default '',
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ last_login datetime DEFAULT NULL,
+ language varchar(5),
+ preferences text NOT NULL default ''
+);
+
+INSERT INTO users (user_id, username, mail_host, created, last_login, language, preferences)
+ SELECT user_id, username, mail_host, created, last_login, language, preferences FROM tmp_users;
+
+CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
+
+CREATE INDEX ix_identities_email ON identities(email, del);
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-02 10:48:44 +0000