Changeset 565c472 in github

Timestamp:

Aug 6, 2012 5:08:58 AM (10 months ago)

Author:

Aleksander Machniak <alec@…>

Children:

c41a86e5

Parents:

83121ec

Message:

Removed users.alias column, added option ('user_aliases')
to use email address from identities as username (#1488581)

Files:

• CHANGELOG (modified) (1 diff)
• SQL/mssql.initial.sql (modified) (3 diffs)
• SQL/mssql.upgrade.sql (modified) (1 diff)
• SQL/mysql.initial.sql (modified) (3 diffs)
• SQL/mysql.update.sql (modified) (1 diff)
• SQL/postgres.initial.sql (modified) (3 diffs)
• SQL/postgres.update.sql (modified) (1 diff)
• SQL/sqlite.initial.sql (modified) (3 diffs)
• SQL/sqlite.update.sql (modified) (1 diff)
• config/main.inc.php.dist (modified) (1 diff)
• program/include/rcube_user.php (modified) (1 diff)

CHANGELOG

@@ -2 +2 @@
 ===========================
 
+- Removed users.alias column, added option ('user_aliases')
+  to use email address from identities as username (#1488581)
 - Removed redundant cache.cache_id column (#1488528)
 - Fix order of attachments in sent mail (#1488423)

SQL/mssql.initial.sql

@@ -93 +93 @@
         [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 ,
@@ -275 +274 @@
 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 
@@ -288 +289 @@
         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
 

SQL/mssql.upgrade.sql

@@ -263 +263 @@
 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
+

SQL/mysql.initial.sql

@@ -23 +23 @@
  `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,
@@ -29 +28 @@
  `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 */;
 
@@ -157 +155 @@
  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 */;
 

SQL/mysql.update.sql

@@ -243 +243 @@
 -- 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`);

SQL/postgres.initial.sql

@@ -21 +21 @@
     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,
@@ -29 +28 @@
 );
 
-CREATE INDEX users_alias_id_idx ON users (alias);
-
-  
+
 --
 -- Table "session"
@@ -82 +79 @@
 
 CREATE INDEX identities_user_id_idx ON identities (user_id, del);
+CREATE INDEX identities_email_idx ON identities (email, del);
 
 

SQL/postgres.update.sql

@@ -181 +181 @@
 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);

SQL/sqlite.initial.sql

@@ -81 +81 @@
 
 CREATE INDEX ix_identities_user_id ON identities(user_id, del);
+CREATE INDEX ix_identities_email ON identities(email, del);
 
 
@@ -93 +94 @@
   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,
@@ -101 +101 @@
 
 CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
-CREATE INDEX ix_users_alias ON users(alias);
 
 -- --------------------------------------------------------

SQL/sqlite.update.sql

@@ -347 +347 @@
 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);

config/main.inc.php.dist

@@ -195 +195 @@
 // set to false if only registered users can use this service
 $rcmail_config['auto_create_user'] = true;
+
+// Enables possibility to log in using email address from user identities
+$rcmail_config['user_aliases'] = false;
 
 // use this folder to store log files (must be writeable for apache user)

program/include/rcube_user.php

@@ -398 +398 @@
     static function query($user, $host)
     {
-        $dbh = rcube::get_instance()->get_dbh();
+        $dbh    = rcube::get_instance()->get_dbh();
+        $config = rcube::get_instance()->config;
 
         // query for matching user name
-        $query = "SELECT * FROM ".$dbh->table_name('users')." WHERE mail_host = ? AND %s = ?";
-        $sql_result = $dbh->query(sprintf($query, 'username'), $host, $user);
-
-        // query for matching alias
-        if (!($sql_arr = $dbh->fetch_assoc($sql_result))) {
-            $sql_result = $dbh->query(sprintf($query, 'alias'), $host, $user);
+        $sql_result = $dbh->query("SELECT * FROM " . $dbh->table_name('users')
+            ." WHERE mail_host = ? AND username = ?", $host, $user);
+
+        $sql_arr = $dbh->fetch_assoc($sql_result);
+
+        // username not found, try aliases from identities
+        if (empty($sql_arr) && $config->get('user_aliases') && strpos($user, '@')) {
+            $sql_result = $dbh->limitquery("SELECT u.*"
+                ." FROM " . $dbh->table_name('users') . " u"
+                ." JOIN " . $dbh->table_name('identities') . " i ON (i.user_id = u.user_id)"
+                ." WHERE email = ? AND del <> 1", 0, 1, $user);
+
             $sql_arr = $dbh->fetch_assoc($sql_result);
         }