Changeset 4053 in subversion

Timestamp:

Oct 6, 2010 9:00:12 AM (3 years ago)

Author:

alec

Message:

• Add unique index on users.username+users.mail_host

Location:

trunk/roundcubemail

Files:

• CHANGELOG (modified) (1 diff)
• SQL/mssql.initial.sql (modified) (1 diff)
• SQL/mssql.upgrade.sql (modified) (1 diff)
• SQL/mysql.initial.sql (modified) (1 diff)
• SQL/mysql.update.sql (modified) (1 diff)
• SQL/postgres.initial.sql (modified) (3 diffs)
• SQL/postgres.update.sql (modified) (2 diffs)
• SQL/sqlite.initial.sql (modified) (1 diff)
• SQL/sqlite.update.sql (modified) (1 diff)

trunk/roundcubemail/CHANGELOG

@@ -19 +19 @@
 - Fix keyboard doesn't work with autocomplete list with Chrome (#1487029)
 - Improve tabs to fixed width and add tabs in identities info (#1486974)
+- Add unique index on users.username+users.mail_host
 
 RELEASE 0.4.2

trunk/roundcubemail/SQL/mssql.initial.sql

@@ -256 +256 @@
 GO
 
-CREATE  INDEX [IX_users_username] ON [dbo].[users]([username]) ON [PRIMARY]
+CREATE  UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY]
 GO
 

trunk/roundcubemail/SQL/mssql.upgrade.sql

@@ -88 +88 @@
 GO
 
+-- Updates from version 0.4.2
+
+DROP INDEX [IX_users_username]
+GO
+CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY]
+GO
+

trunk/roundcubemail/SQL/mysql.initial.sql

@@ -29 +29 @@
  `preferences` text,
  PRIMARY KEY(`user_id`),
- INDEX `username_index` (`username`),
+ UNIQUE `username` (`username`, `mail_host`),
  INDEX `alias_index` (`alias`)
 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;

trunk/roundcubemail/SQL/mysql.update.sql

@@ -124 +124 @@
 ALTER TABLE `users` CHANGE `last_login` `last_login` datetime DEFAULT NULL;
 UPDATE `users` SET `last_login` = NULL WHERE `last_login` = '1000-01-01 00:00:00';
+
+-- Updates from version 0.4.2
+
+ALTER TABLE `users` DROP INDEX `username_index`;
+ALTER TABLE `users` ADD UNIQUE `username` (`username`, `mail_host`);
+

trunk/roundcubemail/SQL/postgres.initial.sql

@@ -25 +25 @@
     last_login timestamp with time zone DEFAULT NULL,
     "language" varchar(5),
-    preferences text DEFAULT ''::text NOT NULL
-);
-
-CREATE INDEX users_username_id_idx ON users (username);
+    preferences text DEFAULT ''::text NOT NULL,
+    UNIQUE (username, mail_host)
+);
+
 CREATE INDEX users_alias_id_idx ON users (alias);
 
@@ -147 +147 @@
 -- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres
 --
-                                            
+
 CREATE TABLE contactgroupmembers (
     contactgroup_id integer NOT NULL
@@ -217 +217 @@
     size integer DEFAULT 0 NOT NULL,
     headers text NOT NULL,
-    structure text
-);
-
-ALTER TABLE messages ADD UNIQUE (user_id, cache_key, uid);
+    structure text,
+    UNIQUE (user_id, cache_key, uid)
+);
+
 CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx);
 CREATE INDEX messages_created_idx ON messages (created);

trunk/roundcubemail/SQL/postgres.update.sql

@@ -57 +57 @@
     NO MINVALUE
     CACHE 1;
-                
+
 CREATE TABLE contactgroups (
     contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY,
@@ -82 +82 @@
 ALTER TABLE users ALTER last_login DROP NOT NULL;
 ALTER TABLE users ALTER last_login SET DEFAULT NULL;
+
+-- Updates from version 0.4.2
+
+DROP INDEX users_username_id_idx;
+ALTER TABLE users ADD UNIQUE (username, mail_host);
+

trunk/roundcubemail/SQL/sqlite.initial.sql

@@ -98 +98 @@
 );
 
-CREATE INDEX ix_users_username ON users(username);
+CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
 CREATE INDEX ix_users_alias ON users(alias);
 

trunk/roundcubemail/SQL/sqlite.update.sql

@@ -141 +141 @@
 CREATE INDEX ix_users_alias ON users(alias);
 DROP TABLE tmp_users;
+
+-- Updates from version 0.4.2
+
+DROP INDEX ix_users_username;
+CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
+