From 565c472918e7f9707cd8d7909ad5dbcc5a921fdf Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Mon, 6 Aug 2012 11:08:58 +0200 Subject: Removed users.alias column, added option ('user_aliases') to use email address from identities as username (#1488581) --- SQL/mssql.initial.sql | 7 ++----- SQL/mssql.upgrade.sql | 9 +++++++++ SQL/mysql.initial.sql | 7 +++---- SQL/mysql.update.sql | 4 +++- SQL/postgres.initial.sql | 4 +--- SQL/postgres.update.sql | 3 +++ SQL/sqlite.initial.sql | 3 +-- SQL/sqlite.update.sql | 32 ++++++++++++++++++++++++++++++++ 8 files changed, 54 insertions(+), 15 deletions(-) (limited to 'SQL') 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); -- cgit v1.2.3