diff options
| -rw-r--r-- | admin/include/functions.php | 49 | ||||
| -rw-r--r-- | admin/stats.php | 117 | ||||
| -rw-r--r-- | include/functions.inc.php | 8 | ||||
| -rw-r--r-- | install/db/71-database.php | 58 | ||||
| -rw-r--r-- | install/phpwebgallery_structure.sql | 7 |
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; -- |