- remove some unnecessary db columns (#history_summary.id, #history.year, month, day and hour)

git-svn-id: http://piwigo.org/svn/trunk@2333 68402e56-0260-453c-a942-63ccdbb3a9ee

5 files changed, 145 insertions, 94 deletions

diff --git a/admin/include/functions.php b/admin/include/functions.php
index 5cb81e9cb..0bf9db6f0 100644
--- a/admin/include/functions.php
+++ b/admin/include/functions.php
@@ -277,7 +277,7 @@ function update_category($ids = 'all')
     }
     $where_cats = '%s IN('.wordwrap(implode(', ', $ids), 120, "\n").')';
   }
-  
+
   // find all categories where the setted representative is not possible :
   // the picture does not exist
   $query = '
@@ -394,15 +394,15 @@ function mass_inserts($table_name, $dbfields, $datas)
       if ($first)
       {
         $query = '
-  INSERT INTO '.$table_name.'
-    ('.implode(',', $dbfields).')
-     VALUES';
+INSERT INTO '.$table_name.'
+  ('.implode(',', $dbfields).')
+  VALUES';
         $first = false;
       }
       else
       {
         $query .= '
-    , ';
+  , ';
       }
 
       $query .= '(';
@@ -452,8 +452,8 @@ function mass_updates($tablename, $dbfields, $datas)
       foreach ($datas as $data)
       {
         $query = '
-  UPDATE '.$tablename.'
-    SET ';
+UPDATE '.$tablename.'
+  SET ';
         $is_first = true;
         foreach ($dbfields['update'] as $key)
         {
@@ -473,7 +473,7 @@ function mass_updates($tablename, $dbfields, $datas)
           $is_first = false;
         }
         $query.= '
-    WHERE ';
+  WHERE ';
 
         $is_first = true;
         foreach ($dbfields['primary'] as $key)
@@ -482,11 +482,18 @@ function mass_updates($tablename, $dbfields, $datas)
           {
             $query.= ' AND ';
           }
-          $query.= $key.' = \''.$data[$key].'\'';
+          if ( isset($data[$key]) )
+          {
+            $query.= $key.' = \''.$data[$key].'\'';
+          }
+          else
+          {
+            $query.= $key.' IS NULL';
+          }
           $is_first = false;
         }
         $query.= '
-  ;';
+;';
         pwg_query($query);
       }
     }
@@ -494,7 +501,7 @@ function mass_updates($tablename, $dbfields, $datas)
     {
       // creation of the temporary table
       $query = '
-  SHOW FULL COLUMNS FROM '.$tablename.'
+SHOW FULL COLUMNS FROM '.$tablename.'
 ;';
       $result = pwg_query($query);
       $columns = array();
@@ -505,14 +512,21 @@ function mass_updates($tablename, $dbfields, $datas)
         {
           $column = $row['Field'];
           $column.= ' '.$row['Type'];
-          if (!isset($row['Null']) or $row['Null'] == '')
+
+          $nullable = true;
+          if (!isset($row['Null']) or $row['Null'] == '' or $row['Null']=='NO')
           {
             $column.= ' NOT NULL';
+            $nullable = false;
           }
           if (isset($row['Default']))
           {
             $column.= " default '".$row['Default']."'";
           }
+          elseif ($nullable)
+          {
+            $column.= " default NULL";
+          }
           if (isset($row['Collation']) and $row['Collation'] != 'NULL')
           {
             $column.= " collate '".$row['Collation']."'";
@@ -527,15 +541,16 @@ function mass_updates($tablename, $dbfields, $datas)
   CREATE TABLE '.$temporary_tablename.'
   (
   '.implode(",\n", $columns).',
-  PRIMARY KEY ('.implode(',', $dbfields['primary']).')
+  UNIQUE KEY the_key ('.implode(',', $dbfields['primary']).')
   )
 ;';
+
       pwg_query($query);
       mass_inserts($temporary_tablename, $all_fields, $datas);
       // update of images table by joining with temporary table
       $query = '
-  UPDATE '.$tablename.' AS t1, '.$temporary_tablename.' AS t2
-    SET '.
+UPDATE '.$tablename.' AS t1, '.$temporary_tablename.' AS t2
+  SET '.
         implode(
           "\n    , ",
           array_map(
@@ -543,7 +558,7 @@ function mass_updates($tablename, $dbfields, $datas)
             $dbfields['update']
             )
           ).'
-    WHERE '.
+  WHERE '.
         implode(
           "\n    AND ",
           array_map(
@@ -554,7 +569,7 @@ function mass_updates($tablename, $dbfields, $datas)
   ;';
       pwg_query($query);
       $query = '
-  DROP TABLE '.$temporary_tablename.'
+DROP TABLE '.$temporary_tablename.'
 ;';
       pwg_query($query);
     }
diff --git a/admin/stats.php b/admin/stats.php
index b0f85317e..9892127c8 100644
--- a/admin/stats.php
+++ b/admin/stats.php
@@ -115,19 +115,15 @@ check_status(ACCESS_ADMINISTRATOR);
 
 $query = '
 SELECT
-    year,
-    month,
-    day,
-    hour,
-    max(id) AS max_id,
+    date,
+    HOUR(time) AS hour,
+    MAX(id) AS max_id,
     COUNT(*) AS nb_pages
   FROM '.HISTORY_TABLE.'
   WHERE summarized = \'false\'
   GROUP BY
-    year ASC,
-    month ASC,
-    day ASC,
-    hour ASC
+    date ASC,
+    HOUR(time) ASC
 ;';
 $result = pwg_query($query);
 
@@ -140,21 +136,12 @@ $first_time_key = null;
 while ($row = mysql_fetch_array($result))
 {
   $time_keys = array(
+    substr($row['date'], 0, 4), //yyyy
+    substr($row['date'], 0, 7), //yyyy-mm
+    substr($row['date'], 0, 10),//yyyy-mm-dd
     sprintf(
-      '%4u',
-      $row['year']
-      ),
-    sprintf(
-      '%4u.%02u',
-      $row['year'], $row['month']
-      ),
-    sprintf(
-      '%4u.%02u.%02u',
-      $row['year'], $row['month'], $row['day']
-      ),
-    sprintf(
-      '%4u.%02u.%02u.%02u',
-      $row['year'], $row['month'], $row['day'], $row['hour']
+      '%s-%02u',
+      $row['date'], $row['hour']
       ),
     );
 
@@ -189,69 +176,73 @@ while ($row = mysql_fetch_array($result))
 // | id            | nb_pages |
 // +---------------+----------+
 // | 2005          |   241109 |
-// | 2005.08       |    20133 |
-// | 2005.08.25    |      620 |
-// | 2005.08.25.21 |      151 |
+// | 2005-08       |    20133 |
+// | 2005-08-25    |      620 |
+// | 2005-08-25-21 |      151 |
 // +---------------+----------+
 
-$existing_time_keys = array();
+
+$updates = array();
+$inserts = array();
 
 if (isset($first_time_key))
 {
-  list($year, $month, $day, $hour) = explode('.', $first_time_key);
-
-  $time_keys = array(
-    sprintf('%4u',                $year),
-    sprintf('%4u.%02u',           $year, $month),
-    sprintf('%4u.%02u.%02u',      $year, $month, $day),
-    sprintf('%4u.%02u.%02u.%02u', $year, $month, $day, $hour),
-    );
+  list($year, $month, $day, $hour) = explode('-', $first_time_key);
 
   $query = '
-SELECT
-    id,
-    nb_pages
+SELECT *
   FROM '.HISTORY_SUMMARY_TABLE.'
-  WHERE id IN (\''.implode("', '", $time_keys).'\')
+  WHERE year='.$year.'
+    AND ( month IS NULL
+      OR ( month='.$month.'
+        AND ( day is NULL 
+          OR (day='.$day.'
+            AND (hour IS NULL OR hour='.$hour.')
+          )
+        )
+      )
+    )
 ;';
   $result = pwg_query($query);
-  while ($row = mysql_fetch_array($result))
+  while ($row = mysql_fetch_assoc($result))
   {
-    $existing_time_keys[ $row['id'] ] = $row['nb_pages'];
+    $key = sprintf('%4u', $row['year']);
+    if ( isset($row['month']) )
+    {
+      $key .= sprintf('-%02u', $row['month']);
+      if ( isset($row['day']) )
+      {
+        $key .= sprintf('-%02u', $row['day']);
+        if ( isset($row['hour']) )
+        {
+          $key .= sprintf('-%02u', $row['hour']);
+        }
+      }
+    }
+
+    if (isset($need_update[$key]))
+    {
+      $row['nb_pages'] += $need_update[$key];
+      array_push($updates, $row);
+      unset($need_update[$key]);
+    }
   }
 }
 
-$updates = array();
-$inserts = array();
-
-foreach (array_keys($need_update) as $time_key)
+foreach ($need_update as $time_key => $nb_pages)
 {
-  $time_tokens = explode('.', $time_key);
+  $time_tokens = explode('-', $time_key);
 
-  if (isset($existing_time_keys[$time_key]))
-  {
-    array_push(
-      $updates,
-      array(
-        'id'       => $time_key,
-        'nb_pages' => $existing_time_keys[$time_key] + $need_update[$time_key],
-        )
-      );
-  }
-  else
-  {
-    array_push(
+  array_push(
       $inserts,
       array(
-        'id'       => $time_key,
         'year'     => $time_tokens[0],
         'month'    => @$time_tokens[1],
         'day'      => @$time_tokens[2],
         'hour'     => @$time_tokens[3],
-        'nb_pages' => $need_update[$time_key],
+        'nb_pages' => $nb_pages,
         )
       );
-  }
 }
 
 if (count($updates) > 0)
@@ -259,7 +250,7 @@ if (count($updates) > 0)
   mass_updates(
     HISTORY_SUMMARY_TABLE,
     array(
-      'primary' => array('id'),
+      'primary' => array('year','month','day','hour'),
       'update'  => array('nb_pages'),
       ),
     $updates
diff --git a/include/functions.inc.php b/include/functions.inc.php
index 6a4948aa9..13aa3d8f4 100644
--- a/include/functions.inc.php
+++ b/include/functions.inc.php
@@ -569,10 +569,6 @@ INSERT INTO '.HISTORY_TABLE.'
   (
     date,
     time,
-    year,
-    month,
-    day,
-    hour,
     user_id,
     IP,
     section,
@@ -585,10 +581,6 @@ INSERT INTO '.HISTORY_TABLE.'
   (
     CURDATE(),
     CURTIME(),
-    YEAR( CURDATE() ),
-    MONTH( CURDATE() ),
-    DAYOFMONTH( CURDATE() ),
-    HOUR( CURTIME() ),
     '.$user['id'].',
     \''.$_SERVER['REMOTE_ADDR'].'\',
     '.(isset($page['section']) ? "'".$page['section']."'" : 'NULL').',
diff --git a/install/db/71-database.php b/install/db/71-database.php
new file mode 100644
index 000000000..ef453509f
--- /dev/null
+++ b/install/db/71-database.php
@@ -0,0 +1,58 @@
+<?php
+// +-----------------------------------------------------------------------+
+// | Piwigo - a PHP based picture gallery                                  |
+// +-----------------------------------------------------------------------+
+// | Copyright(C) 2008      Piwigo Team                  http://piwigo.org |
+// | Copyright(C) 2003-2008 PhpWebGallery Team    http://phpwebgallery.net |
+// | Copyright(C) 2002-2003 Pierrick LE GALL   http://le-gall.net/pierrick |
+// +-----------------------------------------------------------------------+
+// | This program is free software; you can redistribute it and/or modify  |
+// | it under the terms of the GNU General Public License as published by  |
+// | the Free Software Foundation                                          |
+// |                                                                       |
+// | This program is distributed in the hope that it will be useful, but   |
+// | WITHOUT ANY WARRANTY; without even the implied warranty of            |
+// | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU      |
+// | General Public License for more details.                              |
+// |                                                                       |
+// | You should have received a copy of the GNU General Public License     |
+// | along with this program; if not, write to the Free Software           |
+// | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, |
+// | USA.                                                                  |
+// +-----------------------------------------------------------------------+
+
+if (!defined('PHPWG_ROOT_PATH'))
+{
+  die('Hacking attempt!');
+}
+
+$upgrade_description = 'Delete unnecessary #history_summary.id, #history.year, #history.month, #history.day and #history.hour';
+
+include_once(PHPWG_ROOT_PATH.'include/constants.php');
+
+// +-----------------------------------------------------------------------+
+// |                            Upgrade content                            |
+// +-----------------------------------------------------------------------+
+
+$query = 'ALTER TABLE '.HISTORY_SUMMARY_TABLE.'
+DROP PRIMARY KEY,
+DROP COLUMN id,
+ADD UNIQUE KEY history_summary_ymdh (`year`, `month`, `day`, `hour`)
+;';
+pwg_query($query);
+
+$query = 'ALTER TABLE '.HISTORY_TABLE.'
+DROP COLUMN year,
+DROP COLUMN month,
+DROP COLUMN day,
+DROP COLUMN hour
+;';
+pwg_query($query);
+
+echo
+"\n"
+.'"'.$upgrade_description.'"'.' ended'
+."\n"
+;
+
+?>
diff --git a/install/phpwebgallery_structure.sql b/install/phpwebgallery_structure.sql
index 452d9f7a7..f34b2f571 100644
--- a/install/phpwebgallery_structure.sql
+++ b/install/phpwebgallery_structure.sql
@@ -116,10 +116,6 @@ CREATE TABLE `phpwebgallery_history` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `date` date NOT NULL default '0000-00-00',
   `time` time NOT NULL default '00:00:00',
-  `year` smallint(4) NOT NULL default '0',
-  `month` tinyint(2) NOT NULL default '0',
-  `day` tinyint(2) NOT NULL default '0',
-  `hour` tinyint(2) NOT NULL default '0',
   `user_id` smallint(5) NOT NULL default '0',
   `IP` varchar(15) NOT NULL default '',
   `section` enum('categories','tags','search','list','favorites','most_visited','best_rated','recent_pics','recent_cats') default NULL,
@@ -138,13 +134,12 @@ CREATE TABLE `phpwebgallery_history` (
 
 DROP TABLE IF EXISTS `phpwebgallery_history_summary`;
 CREATE TABLE `phpwebgallery_history_summary` (
-  `id` varchar(13) NOT NULL default '',
   `year` smallint(4) NOT NULL default '0',
   `month` tinyint(2) default NULL,
   `day` tinyint(2) default NULL,
   `hour` tinyint(2) default NULL,
   `nb_pages` int(11) default NULL,
-  PRIMARY KEY  (`id`)
+  UNIQUE KEY history_summary_ymdh (`year`,`month`,`day`,`hour`)
 ) TYPE=MyISAM;
 
 --