Changeset 1123

Timestamp:

11/07/06 20:01:55 (7 years ago)

Author:

benoitg

Message:

* Fix # 268, #269 as well as greg's problems by moving all token and
registration date arithmetics

to the database, and standardize on SQL-standard

CURRENT_TIMESTAMP.

Fixes operational problems when PHP and PostGres? don't have the

same timezone

(which appears to be the case on Fedora by default).
Note that the page.php script still has PHP date arithmetics,

but at least should be self-consistent.

Location:

trunk/wifidog-auth

Files:

• CHANGELOG (modified) (1 diff)
• wifidog/admin/import_user_database.php (modified) (1 diff)
• wifidog/auth/index.php (modified) (1 diff)
• wifidog/classes/Authenticator.php (modified) (5 diffs)
• wifidog/classes/Authenticators/AuthenticatorRadius.php (modified) (3 diffs)
• wifidog/classes/Content.php (modified) (1 diff)
• wifidog/classes/Content/File/File.php (modified) (1 diff)
• wifidog/classes/Content/FlickrPhotostream/FlickrPhotostream.php (modified) (2 diffs)
• wifidog/classes/Network.php (modified) (1 diff)
• wifidog/classes/Node.php (modified) (1 diff)
• wifidog/classes/NodeLists/NodeListHTML.php (modified) (1 diff)
• wifidog/classes/NodeLists/NodeListJiWireCSV.php (modified) (1 diff)
• wifidog/classes/NodeLists/NodeListKML.php (modified) (1 diff)
• wifidog/classes/NodeLists/NodeListPDF.php (modified) (1 diff)
• wifidog/classes/NodeLists/NodeListRSS.php (modified) (1 diff)
• wifidog/classes/NodeLists/NodeListXML.php (modified) (1 diff)
• wifidog/classes/StatisticReport/NodeStatus.php (modified) (2 diffs)
• wifidog/classes/User.php (modified) (4 diffs)
• wifidog/include/common.php (modified) (2 diffs)
• wifidog/node_list.php (modified) (1 diff)
• wifidog/ping/index.php (modified) (1 diff)

trunk/wifidog-auth/CHANGELOG

@@ +1 @@
+2006-11-07 Benoit Grégoire  <bock@step.polymtl.ca>
+        * Fix # 268, #269 as well as greg's problems by moving all token and registration date arithmetics
+         to the database, and standardize on SQL-standard CURRENT_TIMESTAMP.  
+        Fixes operational problems when PHP and PostGres don't have the same timezone 
+        (which appears to be the case on Fedora by default). 
+        Note that the page.php script still has PHP date arithmetics, but at least should be self-consistent.
+
 2006-10-24 Benoit Grégoire  <bock@step.polymtl.ca>
         * auth/index.php: Add some defensive coding and debug output when the token is invalid.

trunk/wifidog-auth/wifidog/admin/import_user_database.php

@@ -196 +196 @@
           $status = ACCOUNT_STATUS_ALLOWED;
           $token = User::generateToken();
-          $reg_date = iso8601_date(time());
           $password_hash = $db->escapeString($user['passwd_hash']);
           $username =  $db->escapeString($username);
           $email =  $db->escapeString($user['email']);
-          $sql = "INSERT INTO users (user_id, username,email,pass,account_status,validation_token,reg_date) VALUES ('".get_guid()."','$username','$email','$password_hash','{$status}','{$token}','{$reg_date}')";
+          $sql = "INSERT INTO users (user_id, username,email,pass,account_status,validation_token,reg_date) VALUES ('".get_guid()."','$username','$email','$password_hash','{$status}','{$token}',CURRENT_TIMESTAMP)";
           $update_successful = $db->execSqlUpdate($sql);
           if ($update_successful)

trunk/wifidog-auth/wifidog/auth/index.php

@@ -61 +61 @@
 }
 
-$db->execSqlUniqueRes("SELECT NOW(), *, CASE WHEN ((NOW() - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired FROM connections JOIN users ON (users.user_id=connections.user_id) JOIN networks ON (users.account_origin = networks.network_id) WHERE connections.token='$token'", $info, false);
+$db->execSqlUniqueRes("SELECT CURRENT_TIMESTAMP, *, CASE WHEN ((CURRENT_TIMESTAMP - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired FROM connections JOIN users ON (users.user_id=connections.user_id) JOIN networks ON (users.account_origin = networks.network_id) WHERE connections.token='$token'", $info, false);
 
 if ($info != null)

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

@@ -127 +127 @@
 
         if (!empty ($conn_id)) {
-            $db->execSqlUniqueRes("SELECT NOW(), *, CASE WHEN ((NOW() - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired FROM connections JOIN users ON (users.user_id=connections.user_id) JOIN networks ON (users.account_origin = networks.network_id) WHERE connections.conn_id='$conn_id'", $info, false);
+            $db->execSqlUniqueRes("SELECT CURRENT_TIMESTAMP, *, CASE WHEN ((CURRENT_TIMESTAMP - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired FROM connections JOIN users ON (users.user_id=connections.user_id) JOIN networks ON (users.account_origin = networks.network_id) WHERE connections.conn_id='$conn_id'", $info, false);
 
             $user = User::getObject($info['user_id']);
@@ -188 +188 @@
 
         $conn_id = $db->escapeString($conn_id);
-        $db->execSqlUniqueRes("SELECT NOW(), *, CASE WHEN ((NOW() - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired FROM connections JOIN users ON (users.user_id=connections.user_id) JOIN networks ON (users.account_origin = networks.network_id) WHERE connections.conn_id='$conn_id'", $info, false);
+        $db->execSqlUniqueRes("SELECT CURRENT_TIMESTAMP, *, CASE WHEN ((CURRENT_TIMESTAMP - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired FROM connections JOIN users ON (users.user_id=connections.user_id) JOIN networks ON (users.account_origin = networks.network_id) WHERE connections.conn_id='$conn_id'", $info, false);
         $network = Network::getObject($info['network_id']);
         $splash_user_id = $network->getSplashOnlyUser()->getId();
@@ -196 +196 @@
         $mac = $db->escapeString($_REQUEST['mac']);
         $ip = $db->escapeString($_REQUEST['ip']);
-        $sql = "UPDATE connections SET "."token_status='".TOKEN_INUSE."',"."user_mac='$mac',"."user_ip='$ip',"."last_updated=NOW()"."WHERE conn_id='{$conn_id}';";
+        $sql = "UPDATE connections SET "."token_status='".TOKEN_INUSE."',"."user_mac='$mac',"."user_ip='$ip',"."last_updated=CURRENT_TIMESTAMP"."WHERE conn_id='{$conn_id}';";
         $db->execSqlUpdate($sql, false);
 
@@ -241 +241 @@
         // Write traffic counters to database
         $conn_id = $db->escapeString($conn_id);
-        $db->execSqlUpdate("UPDATE connections SET "."incoming='$incoming',"."outgoing='$outgoing',"."last_updated=NOW() "."WHERE conn_id='{$conn_id}'");
+        $db->execSqlUpdate("UPDATE connections SET "."incoming='$incoming',"."outgoing='$outgoing',"."last_updated=CURRENT_TIMESTAMP "."WHERE conn_id='{$conn_id}'");
     }
 
@@ -259 +259 @@
         // Stop traffic counters update
         $conn_id = $db->escapeString($conn_id);
-        $db->execSqlUpdate("UPDATE connections SET "."timestamp_out=NOW(),"."token_status='".TOKEN_USED."' "."WHERE conn_id='{$conn_id}';\n", false);
+        $db->execSqlUpdate("UPDATE connections SET "."timestamp_out=CURRENT_TIMESTAMP,"."token_status='".TOKEN_USED."' "."WHERE conn_id='{$conn_id}';\n", false);
     }
 

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

@@ -316 +316 @@
         if (Dependencies::check("Radius", $errmsg)) {
             $conn_id = $db->escapeString($conn_id);
-            $db->execSqlUniqueRes("SELECT NOW(), *, CASE WHEN ((NOW() - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired FROM connections JOIN users ON (users.user_id=connections.user_id) JOIN networks ON (users.account_origin = networks.network_id) WHERE connections.conn_id=$conn_id", $info, false);
+            $db->execSqlUniqueRes("SELECT CURRENT_TIMESTAMP, *, CASE WHEN ((CURRENT_TIMESTAMP - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired FROM connections JOIN users ON (users.user_id=connections.user_id) JOIN networks ON (users.account_origin = networks.network_id) WHERE connections.conn_id=$conn_id", $info, false);
 
             // RADIUS accounting start
@@ -386 +386 @@
 
         if (Dependencies::check("Radius", $errmsg)) {
-            $db->execSqlUniqueRes("SELECT NOW(), *, CASE WHEN ((NOW() - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired FROM connections JOIN users ON (users.user_id=connections.user_id) JOIN networks ON (users.account_origin = networks.network_id) WHERE connections.conn_id=$conn_id", $info, false);
+            $db->execSqlUniqueRes("SELECT CURRENT_TIMESTAMP, *, CASE WHEN ((CURRENT_TIMESTAMP - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired FROM connections JOIN users ON (users.user_id=connections.user_id) JOIN networks ON (users.account_origin = networks.network_id) WHERE connections.conn_id=$conn_id", $info, false);
 
             // RADIUS accounting ping
@@ -456 +456 @@
 
         if (Dependencies::check("Radius", $errmsg)) {
-            $db->execSqlUniqueRes("SELECT NOW(), *, CASE WHEN ((NOW() - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired FROM connections JOIN users ON (users.user_id=connections.user_id) JOIN networks ON (users.account_origin = networks.network_id) WHERE connections.conn_id=$conn_id", $info, false);
+            $db->execSqlUniqueRes("SELECT CURRENT_TIMESTAMP, *, CASE WHEN ((CURRENT_TIMESTAMP - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired FROM connections JOIN users ON (users.user_id=connections.user_id) JOIN networks ON (users.account_origin = networks.network_id) WHERE connections.conn_id=$conn_id", $info, false);
 
             // RADIUS accounting stop

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

@@ -1125 +1125 @@
                 $db->execSql($sql, $log_rows, false);
                 if ($log_rows != null) {
-                    $sql = "UPDATE content_display_log SET last_display_timestamp = NOW() WHERE user_id='$user_id' AND content_id='$this->id' AND node_id='$node_id'";
+                    $sql = "UPDATE content_display_log SET last_display_timestamp = CURRENT_TIMESTAMP WHERE user_id='$user_id' AND content_id='$this->id' AND node_id='$node_id'";
                 } else {
                     $sql = "INSERT INTO content_display_log (user_id, content_id, node_id) VALUES ('$user_id', '$this->id', '$node_id')";

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

@@ -233 +233 @@
     public function touch()
     {
-                $this->mBd->execSqlUpdate("UPDATE content_file SET last_update_date = NOW() WHERE files_id='".$this->getId()."'", false);
+                $this->mBd->execSqlUpdate("UPDATE content_file SET last_update_date = CURRENT_TIMESTAMP WHERE files_id='".$this->getId()."'", false);
         $this->refresh();
     }

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

@@ -117 +117 @@
             $content_id = $db->escapeString($content_id);
 
-            $sql = "SELECT *, EXTRACT(EPOCH FROM AGE(NOW(), cache_update_timestamp)) as cache_age FROM content_flickr_photostream WHERE flickr_photostream_id='$content_id'";
+            $sql = "SELECT *, EXTRACT(EPOCH FROM AGE(CURRENT_TIMESTAMP, cache_update_timestamp)) as cache_age FROM content_flickr_photostream WHERE flickr_photostream_id='$content_id'";
             $db->execSqlUniqueRes($sql, $row, false);
             if ($row == null)
@@ -163 +163 @@
             $age = is_null($this->flickr_photostream_row['cache_age']) ? self :: MAX_CACHE_AGE : $this->flickr_photostream_row['cache_age'];
             if ($force_overwrite === true || ($age >= self :: MAX_CACHE_AGE) || ($new_cache !== $old_cache))
-                $this->mBd->execSqlUpdate("UPDATE content_flickr_photostream SET cache_update_timestamp = NOW(), requests_cache = '".$this->mBd->escapeBinaryString($new_cache)."' WHERE flickr_photostream_id = '".$this->getId()."'", false);
+                $this->mBd->execSqlUpdate("UPDATE content_flickr_photostream SET cache_update_timestamp = CURRENT_TIMESTAMP, requests_cache = '".$this->mBd->escapeBinaryString($new_cache)."' WHERE flickr_photostream_id = '".$this->getId()."'", false);
         }
     }

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

@@ -1357 +1357 @@
 
             if ($nonMonitoredOnly) {
-                $db->execSqlUniqueRes("SELECT COUNT(node_id) FROM nodes WHERE network_id = '$_network_id' AND node_deployment_status = 'NON_WIFIDOG_NODE' AND ((NOW()-last_heartbeat_timestamp) >= interval '5 minutes')", $_row, false);
+                $db->execSqlUniqueRes("SELECT COUNT(node_id) FROM nodes WHERE network_id = '$_network_id' AND node_deployment_status = 'NON_WIFIDOG_NODE' AND ((CURRENT_TIMESTAMP-last_heartbeat_timestamp) >= interval '5 minutes')", $_row, false);
             } else {
-                $db->execSqlUniqueRes("SELECT COUNT(node_id) FROM nodes WHERE network_id = '$_network_id' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') AND ((NOW()-last_heartbeat_timestamp) < interval '5 minutes')", $_row, false);
+                $db->execSqlUniqueRes("SELECT COUNT(node_id) FROM nodes WHERE network_id = '$_network_id' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') AND ((CURRENT_TIMESTAMP-last_heartbeat_timestamp) < interval '5 minutes')", $_row, false);
             }
 

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

@@ -232 +232 @@
             }
 
-            $sql = "INSERT INTO nodes (node_id, network_id, creation_date, node_deployment_status, name) VALUES ('$node_id', '$network_id', NOW(),'$node_deployment_status', '$node_name')";
+            $sql = "INSERT INTO nodes (node_id, network_id, creation_date, node_deployment_status, name) VALUES ('$node_id', '$network_id', CURRENT_TIMESTAMP,'$node_deployment_status', '$node_name')";
 
             if (!$db->execSqlUpdate($sql, false)) {

trunk/wifidog-auth/wifidog/classes/NodeLists/NodeListHTML.php

@@ -126 +126 @@
 
         // Query the database, sorting by node name
-        $db->execSql("SELECT *, (NOW()-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((NOW()-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(name)", $this->_nodes, false);
+        $db->execSql("SELECT *, (CURRENT_TIMESTAMP-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((CURRENT_TIMESTAMP-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(name)", $this->_nodes, false);
     }
 

trunk/wifidog-auth/wifidog/classes/NodeLists/NodeListJiWireCSV.php

@@ -96 +96 @@
 
         // Query the database, sorting by node name
-        $db->execSql("SELECT *, (NOW()-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((NOW()-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(name)", $this->_nodes, false);
+        $db->execSql("SELECT *, (CURRENT_TIMESTAMP-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((CURRENT_TIMESTAMP-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(name)", $this->_nodes, false);
     }
 

trunk/wifidog-auth/wifidog/classes/NodeLists/NodeListKML.php

@@ -103 +103 @@
 
         // Query the database, sorting by node name
-        $db->execSql("SELECT *, (NOW()-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((NOW()-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(name)", $this->_nodes, false);
+        $db->execSql("SELECT *, (CURRENT_TIMESTAMP-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((CURRENT_TIMESTAMP-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(name)", $this->_nodes, false);
     }
 

trunk/wifidog-auth/wifidog/classes/NodeLists/NodeListPDF.php

@@ -978 +978 @@
 
         // Query the database, sorting by node name
-        $db->execSql("SELECT *, (NOW()-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((NOW()-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(" . $this->_pdfSort . ")", $this->_nodes, false);
+        $db->execSql("SELECT *, (CURRENT_TIMESTAMP-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((CURRENT_TIMESTAMP-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(" . $this->_pdfSort . ")", $this->_nodes, false);
     }
 

trunk/wifidog-auth/wifidog/classes/NodeLists/NodeListRSS.php

@@ -102 +102 @@
 
         // Query the database, sorting by node name
-        $db->execSql("SELECT *, (NOW()-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((NOW()-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(name)", $this->_nodes, false);
+        $db->execSql("SELECT *, (CURRENT_TIMESTAMP-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((CURRENT_TIMESTAMP-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(name)", $this->_nodes, false);
     }
 

trunk/wifidog-auth/wifidog/classes/NodeLists/NodeListXML.php

@@ -101 +101 @@
 
         // Query the database, sorting by node name
-        $db->execSql("SELECT *, (NOW()-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((NOW()-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(name)", $this->_nodes, false);
+        $db->execSql("SELECT *, (CURRENT_TIMESTAMP-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((CURRENT_TIMESTAMP-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(name)", $this->_nodes, false);
     }
 

trunk/wifidog-auth/wifidog/classes/StatisticReport/NodeStatus.php

@@ -100 +100 @@
                 $html .= "<table>";
 
-                $db->execSql("SELECT node_id, name, (NOW()-last_heartbeat_timestamp) AS since_last_heartbeat, last_heartbeat_ip, CASE WHEN ((NOW()-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up, creation_date FROM nodes WHERE node_id = '{$node_id}'", $rows, false);
+                $db->execSql("SELECT node_id, name, (CURRENT_TIMESTAMP-last_heartbeat_timestamp) AS since_last_heartbeat, last_heartbeat_ip, CASE WHEN ((CURRENT_TIMESTAMP-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up, creation_date FROM nodes WHERE node_id = '{$node_id}'", $rows, false);
 
                 $html .= ($rows[0]['is_up'] == 't') ? "<tr class='even'>" : "<tr class='red'>";
@@ -205 +205 @@
                 $html .= "<table>";
                 $date_constraint = $this->stats->getSqlDateConstraint();
-                $db->execSql("SELECT round(CAST( (SELECT SUM(daily_connections) FROM (SELECT COUNT(DISTINCT user_id) AS daily_connections, date_trunc('day', timestamp_in) FROM connections WHERE node_id='${node_id}' AND (incoming!=0 OR outgoing!=0) {$date_constraint} GROUP BY date_trunc('day', timestamp_in)) AS daily_connections_table) / (EXTRACT(EPOCH FROM (NOW()-(SELECT timestamp_in FROM connections WHERE node_id='${node_id}' AND (incoming!=0 OR outgoing!=0) ORDER BY timestamp_in LIMIT 1)) )/(3600*24)) AS numeric),2) AS connections_per_day", $rows, false);
+                $db->execSql("SELECT round(CAST( (SELECT SUM(daily_connections) FROM (SELECT COUNT(DISTINCT user_id) AS daily_connections, date_trunc('day', timestamp_in) FROM connections WHERE node_id='${node_id}' AND (incoming!=0 OR outgoing!=0) {$date_constraint} GROUP BY date_trunc('day', timestamp_in)) AS daily_connections_table) / (EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP-(SELECT timestamp_in FROM connections WHERE node_id='${node_id}' AND (incoming!=0 OR outgoing!=0) ORDER BY timestamp_in LIMIT 1)) )/(3600*24)) AS numeric),2) AS connections_per_day", $rows, false);
                 $html .= "<tr class='even'>";
                 $html .= "  <th>"._("Average visits per day").":</th>";

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

@@ -206 +206 @@
         $token = User :: generateToken();
 
-        $db->execSqlUpdate("INSERT INTO users (user_id,username, account_origin,email,pass,account_status,validation_token,reg_date) VALUES ('$id_str','$username_str','$account_origin_str','$email_str','$password_hash','$status','$token',NOW())");
+        $db->execSqlUpdate("INSERT INTO users (user_id,username, account_origin,email,pass,account_status,validation_token,reg_date) VALUES ('$id_str','$username_str','$account_origin_str','$email_str','$password_hash','$status','$token',CURRENT_TIMESTAMP)");
 
         $object = new self($id);
@@ -217 +217 @@
             $days_since_creation = $db->escapeString($days_since_creation);
     
-            //$db->execSqlUpdate("INSERT INTO users (user_id,username, account_origin,email,pass,account_status,validation_token,reg_date) VALUES ('$id_str','$username_str','$account_origin_str','$email_str','$password_hash','$status','$token',NOW())");
+            //$db->execSqlUpdate("INSERT INTO users (user_id,username, account_origin,email,pass,account_status,validation_token,reg_date) VALUES ('$id_str','$username_str','$account_origin_str','$email_str','$password_hash','$status','$token',CURRENT_TIMESTAMP)");
         }*/
 
@@ -371 +371 @@
         } else
             if ($account_status == ACCOUNT_STATUS_VALIDATION) {
-                $sql = "SELECT CASE WHEN ((NOW() - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired, networks.validation_grace_time FROM users  JOIN networks ON (users.account_origin = networks.network_id) WHERE (user_id='{$this->id}')";
+                $sql = "SELECT CASE WHEN ((CURRENT_TIMESTAMP - reg_date) > networks.validation_grace_time) THEN true ELSE false END AS validation_grace_time_expired, networks.validation_grace_time FROM users  JOIN networks ON (users.account_origin = networks.network_id) WHERE (user_id='{$this->id}')";
                 $db->execSqlUniqueRes($sql, $user_info, false);
 
@@ -449 +449 @@
             if ($session && $node_ip && $session->get(SESS_GW_ID_VAR)) {
                 $node_id = $db->escapeString($session->get(SESS_GW_ID_VAR));
-                $db->execSqlUpdate("INSERT INTO connections (user_id, token, token_status, timestamp_in, node_id, node_ip, last_updated) VALUES ('" . $this->getId() . "', '$token', '" . TOKEN_UNUSED . "', NOW(), '$node_id', '$node_ip', NOW())", false);
+                $db->execSqlUpdate("INSERT INTO connections (user_id, token, token_status, timestamp_in, node_id, node_ip, last_updated) VALUES ('" . $this->getId() . "', '$token', '" . TOKEN_UNUSED . "', CURRENT_TIMESTAMP, '$node_id', '$node_ip', CURRENT_TIMESTAMP)", false);
                 $retval = $token;
             } else

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

@@ -193 +193 @@
 }
 
-function iso8601_date($unix_timestamp) {
-    $tzd = date('O', $unix_timestamp);
-    $tzd = substr(chunk_split($tzd, 3, ':'), 0, 6);
-    $date = date('Y-m-d\TH:i:s', $unix_timestamp) . $tzd;
-    return $date;
-}
-
 /** Cleanup dangling tokens and connections from the database, left if a gateway crashed, etc. */
 function garbage_collect() {
@@ -205 +198 @@
 
     // 10 minutes
-    $expiration = time() - 60 * 10;
-    $expiration = iso8601_date($expiration);
-    $db->execSqlUpdate("UPDATE connections SET token_status='" . TOKEN_USED . "' WHERE last_updated < '$expiration' AND token_status = '" . TOKEN_INUSE . "'", false);
+    $expiration = '10 minutes';
+    $db->execSqlUpdate("UPDATE connections SET token_status='" . TOKEN_USED . "' WHERE last_updated < (CURRENT_TIMESTAMP - interval '$expiration') AND token_status = '" . TOKEN_INUSE . "';", false);
 }
 

trunk/wifidog-auth/wifidog/node_list.php

@@ -98 +98 @@
 // Sort according to above instructions
 if ($sort_by_using_sql === true)
-    $sql = "SELECT node_id, name, last_heartbeat_user_agent, (NOW()-last_heartbeat_timestamp) AS since_last_heartbeat, last_heartbeat_ip, CASE WHEN ((NOW()-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS online, creation_date, node_deployment_status FROM nodes WHERE node_deployment_status != 'PERMANENTLY_CLOSED' ORDER BY {$sort_by_param}";
+    $sql = "SELECT node_id, name, last_heartbeat_user_agent, (CURRENT_TIMESTAMP-last_heartbeat_timestamp) AS since_last_heartbeat, last_heartbeat_ip, CASE WHEN ((CURRENT_TIMESTAMP-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS online, creation_date, node_deployment_status FROM nodes WHERE node_deployment_status != 'PERMANENTLY_CLOSED' ORDER BY {$sort_by_param}";
 else
-    $sql = "SELECT node_id, name, last_heartbeat_user_agent, (NOW()-last_heartbeat_timestamp) AS since_last_heartbeat, last_heartbeat_ip, CASE WHEN ((NOW()-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS online, creation_date, node_deployment_status FROM nodes WHERE node_deployment_status != 'PERMANENTLY_CLOSED' ORDER BY ".DEFAULT_SORT_BY_PARAM;
+    $sql = "SELECT node_id, name, last_heartbeat_user_agent, (CURRENT_TIMESTAMP-last_heartbeat_timestamp) AS since_last_heartbeat, last_heartbeat_ip, CASE WHEN ((CURRENT_TIMESTAMP-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS online, creation_date, node_deployment_status FROM nodes WHERE node_deployment_status != 'PERMANENTLY_CLOSED' ORDER BY ".DEFAULT_SORT_BY_PARAM;
 $nodes_results = null;
 $db->execSql($sql, $nodes_results, false);

trunk/wifidog-auth/wifidog/ping/index.php

@@ -54 +54 @@
 $node_id = $db->escapeString($_REQUEST['gw_id']);
 $user_agent =  $db->escapeString($_SERVER['HTTP_USER_AGENT']);
-$db->execSqlUpdate("UPDATE nodes SET last_heartbeat_ip='$_SERVER[REMOTE_ADDR]', last_heartbeat_timestamp=NOW(), last_heartbeat_user_agent='$user_agent' WHERE node_id='$node_id'");
+$db->execSqlUpdate("UPDATE nodes SET last_heartbeat_ip='$_SERVER[REMOTE_ADDR]', last_heartbeat_timestamp=CURRENT_TIMESTAMP, last_heartbeat_user_agent='$user_agent' WHERE node_id='$node_id'");
 
 /*