Changeset 17758 for extensions/EStat/lib/statDBMonth.class.inc.php

Timestamp:

Sep 5, 2012, 4:15:54 PM (12 years ago)

Author:

grum

Message:

version 0.1.0b

. Fix install bugs
. Manage anonymous directories
. Manage CSV export options settings
. Fix IPadress<=>country association bug & improve join performances
. Fix bug on IP filter
. Improve performances for history consult

File:

• extensions/EStat/lib/statDBMonth.class.inc.php (modified) (10 diffs)

extensions/EStat/lib/statDBMonth.class.inc.php

@@ -78 +78 @@
    *
    * missing values are set to default value
+   * country is defined automatically after buildIPCountry call; set to '' while
+   * call is not made
    *
    * @param Array $log : a record
@@ -109 +111 @@
       // insert log
       $sql="INSERT INTO logs
-            (date, IPadress, userId, catId, imageId, tagsId, section,
+            (date, IPadress, country, userId, catId, imageId, tagsId, section,
              userAgent, uaBrowser, uaBrowserVersion, uaEngine, uaEngineVersion,
              uaOS, uaOSVersion, uaType, screenSizeW, screenSizeH)
@@ -115 +117 @@
                       //"'".$this->dbHandle->escapeString($value['IPadress'])."', ".
                       ":IP, ".
+                      "'', ".
                       $value['userId'].", ".
                       $value['catId'].", ".
@@ -254 +257 @@
     if(!isset($filter['uaType'])) $filter['uaType']=null;
 
+    $filter['IPadress']=$this->checkIPFilter($filter['IPadress']);
     // check filter values - getOperator check and 'clean' the filter
     $filter['date']=$this->getOperator($filter['date'], 'date');
@@ -296 +300 @@
         array('id'=>'id', 'direction'=>'A')
       );
+    // to optimize query, if date is the first field selected for order by,
+    // all other fields are removed; exception: if IPadress is the second one this
+    // field is kept
+    $tmp=$orderBy;
+    if($tmp[0]['id']=='date')
+    {
+      $orderBy=array($tmp[0]);
+      if($tmp[1]['id']=='IPadress')
+      {
+        $orderBy[]=$tmp[1];
+      }
+    }
+
     //build ORDER BY clause
     $orderBy=$this->buildOrderByClause($orderBy);
@@ -310 +327 @@
     {
       case ASDF_GET_ROWS:
-          $sql="SELECT id, date, IPadress, userId, catId, imageId, tagsId, section,
+          $sql="SELECT id, date, IPadress, country, userId, catId, imageId, tagsId, section,
                        userAgent, uaBrowser, uaBrowserVersion, uaEngine, uaEngineVersion,
                        uaOS, uaOSVersion, uaType, screenSizeW, screenSizeH
                 FROM logs $where $orderBy $limit;";
+
           $sqlStm=$this->dbHandle->prepare($sql);
           foreach($IPList as $num=>$IP)
@@ -576 +594 @@
     if(!isset($filter['country'])) $filter['country']=null;
 
+    $filter['IPadress']=$this->checkIPFilter($filter['IPadress']);
     // check filter values - getOperator check and 'clean' the filter
     $filter['IPadress']=$this->getOperator($filter['IPadress'], 'IP');
@@ -1248 +1267 @@
         DROP TABLE IF EXISTS tmpIp;
 
+        -- update the logs table for rows with unset country
+        -- use a simple sub select here, assuming that it's quick enough for the update
+        UPDATE logs
+        SET country=(SELECT sip.country FROM statip sip WHERE logs.IPadress = sip.IPadress)
+        WHERE country='';
+
         -- commit changes
         COMMIT TRANSACTION;
@@ -1285 +1310 @@
                   'date' INTEGER,
                   'IPadress' BLOB,
+                  'country' TEXT,
                   'userId' INTEGER,
                   'catId' INTEGER,
@@ -1302 +1328 @@
               );"
             );
-          $this->dbHandle->exec("CREATE INDEX iDate ON logs ('date');");
-          $this->dbHandle->exec("CREATE INDEX iByIP ON logs ('IPadress');");
+          $this->dbHandle->exec("CREATE INDEX iDateIP ON logs ('date', 'IPadress');");
+          $this->dbHandle->exec("CREATE INDEX iIPCountry ON logs ('IPadress', 'country');");
+          $this->dbHandle->exec("CREATE INDEX iUAB ON logs ('uaType', 'uaBrowser', 'uaEngine');");
+          $this->dbHandle->exec("CREATE INDEX iUAO ON logs ('uaOS');");
         }