Changeset 1260

Timestamp:

07/19/07 16:29:48 (5 years ago)

Author:

benoitg

Message:

UIUserList fixes:

• Node::getRecentUsers(): Get the 5 most recent users, not some random users in the last week. Also, exclude users currently logged-in.
• Node::getActiveUsers(): Function was unbearably slow, now limit the search space to the last 3 months. Get the 5 user who connected on the most differents days, instead of users with the worst wireless card ;)
• schema_validate.php: Add index for timestamp_in on connection table.

Location:

trunk/wifidog-auth

Files:

• CHANGELOG (modified) (1 diff)
• wifidog/classes/Node.php (modified) (8 diffs)
• wifidog/include/schema_validate.php (modified) (2 diffs)

trunk/wifidog-auth/CHANGELOG

@@ -1 +1 @@
 # $Id: $
 
+2007-07-19 Benoit Grégoire  <bock@step.polymtl.ca>
+        UIUserList fixes:
+        * Node::getRecentUsers():  Get the 5 most recent users, not some random users in the last week.  Also, exclude users currently logged-in.
+        * Node::getActiveUsers():  Function was unbearably slow, now limit the search space to the last 3 months.  Get the 5 user who connected on the most differents days, instead of users with the worst wireless card ;) 
+        * schema_validate.php: Add index for timestamp_in on connection table.
+        
 2007-07-17 Benoit Grégoire  <bock@step.polymtl.ca>
         * Use last_heartbeat_sys_uptime untill #354 is fixed

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

@@ -149 +149 @@
         if(!isset($currentRealNodeComputed))
         {
+            //echo "getCurrentTealNode(): Computing for IP $currentIp<br/>";
             $currentRealNodeComputed=true;
             $db = AbstractDb::getObject();
@@ -813 +814 @@
         return $this->_row['last_heartbeat_wifidog_uptime'];
     }
-    
+
     function getLastHeartbeatSysUptime()
     {
         return $this->_row['last_heartbeat_sys_uptime'];
     }
-    
+
     function getLastHeartbeatSysLoad()
     {
         return $this->_row['last_heartbeat_sys_load'];
     }
-    
+
     function getLastHeartbeatSysMemfree()
     {
         return $this->_row['last_heartbeat_sys_memfree'];
     }
-      
+
     function getLastHeartbeatTimestamp()
     {
@@ -1314 +1315 @@
 
     /**
-     * The list of the 5 most recent users who have logged into this node in the past week
+     * The list of the 5 most recent users who have logged into this node in the past week,
+     * excluding those that are currently connected.
      *
      * @return array An array of User object, or an empty array
@@ -1322 +1324 @@
     public function getRecentUsers()
     {
-         
+        $numUsers = 5;
         $db = AbstractDb::getObject();
 
@@ -1331 +1333 @@
         $weekAgoDate = strftime("%Y-%m-%d 00:00", strtotime("-1 week"));
 
-        $db->execSql("SELECT DISTINCT users.user_id FROM users,connections WHERE connections.timestamp_in>'{$weekAgoDate}' AND users.user_id=connections.user_id AND connections.node_id='{$this->id}' LIMIT 5", $users, false);
+        $sql = null;
+        $sql .= "SELECT user_id, timestamp_in FROM connections \n";
+        $sql .= "WHERE connections.node_id='{$this->id}' \n";
+        $sql .= "AND connections.user_id NOT IN (".$this->getOnlineUsersSql().")  \n";
+        $sql .= "AND connections.timestamp_in>'{$weekAgoDate}' \n";
+
+        $sql .= "ORDER BY connections.timestamp_in DESC\n";
+        $sql .= "LIMIT $numUsers * 4 \n";
+        $db->execSql($sql, $users, false);
+
+        if ($users != null) {
+            $alreadyPresentArray[] = array(); //Only keep the top $num
+            $count = 0;
+            foreach ($users as $user_row) {
+                if(empty($alreadyPresentArray[$user_row['user_id']])) {
+                    $retval[] = User::getObject($user_row['user_id']);
+                    $alreadyPresentArray[$user_row['user_id']]=true;
+                    $count++;
+                    if($count>=$numUsers) {
+                        break;
+                    }
+                }
+            }
+        }
+
+        return $retval;
+    }
+
+    /**
+     * The list of the 5 users who have logged into this node the most different days during the last 3 months
+     *
+     * @return array An array of User object, or an empty array
+     *
+     * @access public
+     */
+    public function getActiveUsers()
+    {
+        $numUsers = 5;
+        $db = AbstractDb::getObject();
+
+        // Init values
+        $retval = array();
+        $users = null;
+        $anonUsers = 0;
+        $sql = null;
+        $sql .= "SELECT DISTINCT connections.user_id, count(distinct date_trunc('day', timestamp_in)) as connections FROM connections \n";
+        $sql .= " WHERE connections.node_id='{$this->id}' \n";
+        $sql .= " AND timestamp_in > (CURRENT_TIMESTAMP - interval '3 month') \n";
+        $sql .= " GROUP BY connections.user_id  \n";
+        $sql .= "ORDER BY connections desc \n";
+        $sql .= " LIMIT $numUsers\n";
+        $db->execSql($sql, $users, false);
 
         if ($users != null) {
@@ -1342 +1395 @@
     }
 
+    private function getOnlineUsersSql() {
+        return "SELECT users.user_id FROM users,connections WHERE connections.token_status='".TOKEN_INUSE."' AND users.user_id=connections.user_id AND connections.node_id='{$this->id}'";
+    }
     /**
-     * The list of the 5 users who have logged into this node most often
+     * The list of users online at this node
      *
      * @return array An array of User object, or an empty array
@@ -1349 +1405 @@
      * @access public
      */
-    public function getActiveUsers()
+    public function getOnlineUsers()
     {
          
@@ -1358 +1414 @@
         $users = null;
         $anonUsers = 0;
-
-        $db->execSql("SELECT DISTINCT users.user_id, count(users.user_id) as connections FROM users, connections WHERE users.user_id=connections.user_id AND connections.node_id='{$this->id}' GROUP BY users.user_id ORDER BY connections desc LIMIT 5", $users, false);
-
-        if ($users != null) {
-            foreach ($users as $user_row) {
-                $retval[] = User::getObject($user_row['user_id']);
-            }
-        }
-
-        return $retval;
-    }
-
-    /**
-     * The list of users online at this node
-     *
-     * @return array An array of User object, or an empty array
-     *
-     * @access public
-     */
-    public function getOnlineUsers()
-    {
-         
-        $db = AbstractDb::getObject();
-
-        // Init values
-        $retval = array();
-        $users = null;
-        $anonUsers = 0;
-
-        $db->execSql("SELECT users.user_id FROM users,connections WHERE connections.token_status='".TOKEN_INUSE."' AND users.user_id=connections.user_id AND connections.node_id='{$this->id}'", $users, false);
-
+        $db->execSql($this->getOnlineUsersSql(), $users, false);
         if ($users != null) {
             foreach ($users as $user_row) {

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

@@ -48 +48 @@
  * Define current database schema version
  */
-define('REQUIRED_SCHEMA_VERSION', 55);
+define('REQUIRED_SCHEMA_VERSION', 56);
 /** Used to test a new shecma version before modyfying the database */
 define('SCHEMA_UPDATE_TEST_MODE', false);
@@ -1283 +1283 @@
         $sql .= "ALTER TABLE nodes ALTER COLUMN last_heartbeat_sys_load SET DEFAULT NULL;\n";
      }
+     
+     $new_schema_version = 56;
+     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 .= "CREATE INDEX idx_connections_timestamp_in ON connections (timestamp_in);\n";
+     }
+     
     /*
      $new_schema_version = ;