Changeset 1456

Timestamp:

03/14/10 18:42:49 (2 years ago)

Author:

gbastien

Message:

Performance enhancement: better queries and indexes for case-insensitivity. One connection/request to server, content logging can now be turned off if desired.

Location:

trunk/wifidog-auth/wifidog

Files:

• classes/AbstractDb.php (modified) (3 diffs)
• classes/Authenticators/AuthenticatorLocalUser.php (modified) (2 diffs)
• classes/Content.php (modified) (1 diff)
• classes/User.php (modified) (4 diffs)
• config.php (modified) (1 diff)
• include/schema_validate.php (modified) (2 diffs)
• install.php (modified) (1 diff)

trunk/wifidog-auth/wifidog/classes/AbstractDb.php

@@ -64 +64 @@
     private $sql_update_total_time;
     private $sql_executed_queries_array;
+    private $_ptrconnection = null;
 
     private static $object;
@@ -83 +84 @@
     function connect($db_name)
     {
+        if (!is_null($this->_ptrconnection))
+            return $this->_ptrconnection;
+            
         // Grab default database name from config file
         if ($db_name == NULL)
@@ -97 +101 @@
         // Throw an exception if anything went wrong
         if ($ptr_connexion == FALSE)
-        throw new Exception(sprintf(_("Unable to connect to the database at %s"), "host=".CONF_DATABASE_HOST." port=".CONF_DATABASE_PORT." dbname=$db_name user=".CONF_DATABASE_USER." password=*********"));
-
+            throw new Exception(sprintf(_("Unable to connect to the database at %s"), "host=".CONF_DATABASE_HOST." port=".CONF_DATABASE_PORT." dbname=$db_name user=".CONF_DATABASE_USER." password=*********"));
+
+        $this->_ptrconnection = $ptr_connexion;
         return $ptr_connexion;
     }

trunk/wifidog-auth/wifidog/classes/Authenticators/AuthenticatorLocalUser.php

@@ -126 +126 @@
             $password = $db->escapeString($password);
             
-            $comparison = ($this->getNetwork()->getUsernamesCaseSensitive()? ' = ': ' ILike ');
-            $sql = "SELECT user_id FROM users WHERE (username$comparison'$username' OR email$comparison'$username') AND account_origin='".$this->getNetwork()->getId()."' AND pass='$password_hash'";
+            $username = ($this->getNetwork()->getUsernamesCaseSensitive()? $username: strtolower($username));
+            $compareto = ($this->getNetwork()->getUsernamesCaseSensitive()? 'username' : 'lower(username)');
+            $sql = "SELECT user_id FROM users WHERE ($compareto = '$username' OR lower(email) = '$username') AND account_origin='".$this->getNetwork()->getId()."' AND pass='$password_hash'";
             $db->execSqlUniqueRes($sql, $user_info, false);
 
@@ -146 +147 @@
                  */
                 $user_info = null;
-                $db->execSqlUniqueRes("SELECT * FROM users WHERE (username$comparison'$username' OR email$comparison'$username') AND account_origin='".$this->getNetwork()->getId()."'", $user_info, false);
+                $db->execSqlUniqueRes("SELECT * FROM users WHERE ($compareto = '$username' OR lower(email) = '$username') AND account_origin='".$this->getNetwork()->getId()."'", $user_info, false);
 
                 if ($user_info == null) {

trunk/wifidog-auth/wifidog/classes/Content.php

@@ -137 +137 @@
         }
         // By default content display logging is enabled
-        $this->setLoggingStatus(true);
+        $this->setLoggingStatus(LOG_CONTENT_DISPLAY);
         $this->log_as_content = & $this;
     }

trunk/wifidog-auth/wifidog/classes/User.php

@@ -159 +159 @@
         $object = null;
 
-        $username_str = $db->escapeString($username);
-        $comparison = ($account_origin->getUsernamesCaseSensitive()? '=': 'ILike');
+        $username_str = ($account_origin->getUsernamesCaseSensitive()? $db->escapeString($username): strtolower($db->escapeString($username)));
+        $compareto = ($account_origin->getUsernamesCaseSensitive()? 'username': 'lower(username)');
         $account_origin_str = $db->escapeString($account_origin->getId());
-        $db->execSqlUniqueRes("SELECT user_id FROM users WHERE username {$comparison} '$username_str' AND account_origin = '$account_origin_str'", $user_info, false);
+        $db->execSqlUniqueRes("SELECT user_id FROM users WHERE $compareto = '$username_str' AND account_origin = '$account_origin_str'", $user_info, false);
 
         if ($user_info != null) {
@@ -183 +183 @@
         $object = null;
 
-        $username_str = $db->escapeString($usernameOrEmail);
-        $comparison = ($account_origin->getUsernamesCaseSensitive()? '=': 'ILike');
+        $username_str = ($account_origin->getUsernamesCaseSensitive()? $db->escapeString($username): strtolower($db->escapeString($username)));
+        $compareto = ($account_origin->getUsernamesCaseSensitive()? 'username': 'lower(username)');
         $account_origin_str = $db->escapeString($account_origin->getId());
-        $db->execSqlUniqueRes("SELECT user_id FROM users WHERE (username {$comparison} '$username_str' OR email ILike '$username_str') AND account_origin = '$account_origin_str'", $user_info, false);
+        $db->execSqlUniqueRes("SELECT user_id FROM users WHERE ($compareto = '$username_str' OR lower(email) = '".strtolower($username_str)."') AND account_origin = '$account_origin_str'", $user_info, false);
 
         if ($user_info != null) {
@@ -206 +206 @@
         $object = null;
 
-        $usernameOrEmail_str = $db->escapeString($usernameOrEmail);
-        $db->execSqlUniqueRes("SELECT user_id FROM users WHERE username = '$usernameOrEmail_str' OR email ILike '$usernameOrEmail_str'", $user_info, false);
+        $db->execSqlUniqueRes("SELECT user_id FROM users WHERE username = '$usernameOrEmail_str' OR lower(email) = '".strtolower($usernameOrEmail_str)."'", $user_info, false);
 
         if ($user_info != null) {
@@ -244 +243 @@
         $object = null;
 
-        $email_str = $db->escapeString($email);
+        $email_str = strtolower($db->escapeString($email));
         $account_origin_str = $db->escapeString($account_origin->getId());
-        $db->execSqlUniqueRes("SELECT user_id FROM users WHERE email ILike '$email_str' AND account_origin = '$account_origin_str'", $user_info, false);
+        $db->execSqlUniqueRes("SELECT user_id FROM users WHERE lower(email) = '$email_str' AND account_origin = '$account_origin_str'", $user_info, false);
 
         if ($user_info != null)

trunk/wifidog-auth/wifidog/config.php

@@ -103 +103 @@
 define('LOG_SQL_QUERIES', false);
 
+/**
+ * Log content display.  If set to true, every piece of content displayed to a user will
+ * be logged in a log table.
+ */
+
+define('LOG_CONTENT_DISPLAY', false);
+
 /********************************************************************\
  * WEBSERVER CONFIGURATION                                          *

trunk/wifidog-auth/wifidog/include/schema_validate.php

@@ -48 +48 @@
  * Define current database schema version
  */
-define('REQUIRED_SCHEMA_VERSION', 70);
+define('REQUIRED_SCHEMA_VERSION', 71);
 /** Used to test a new shecma version before modyfying the database */
 define('SCHEMA_UPDATE_TEST_MODE', false);
@@ -1541 +1541 @@
         $sql .= "\nALTER TABLE nodes ADD COLUMN show_node_on_map boolean NOT NULL DEFAULT true;";
     }
+    
+    $new_schema_version = 71;
+    if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
+        printUpdateVersion($new_schema_version);
+        $sql .= "\n\nUPDATE schema_info SET value='$new_schema_version' WHERE tag='schema_version';\n";
+        $sql .= "\nCREATE INDEX lower_username ON users USING btree (lower(username));";
+        $sql .= "\nCREATE INDEX lower_email ON users USING btree (lower(email));";
+    }
+    
    
     /*

trunk/wifidog-auth/wifidog/install.php

@@ -228 +228 @@
 'GMAPS_HOTSPOTS_MAP_ENABLED' => array (
 'title' => 'Google Maps Support',
+'depend' => 'return 1;',
+'message' => ' '
+),
+'LOG_CONTENT_DISPLAY' => array (
+'title' => 'Log what content is displayed to users',
 'depend' => 'return 1;',
 'message' => ' '