aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorrvelices <rv-github@modusoptimus.com>2008-05-06 01:04:58 +0000
committerrvelices <rv-github@modusoptimus.com>2008-05-06 01:04:58 +0000
commit5dbad41e2e125b6a55e85c5588d96b68f6486ef4 (patch)
tree9203a53ed03f05d6d6c9ca53efb4c466d814ef41
parentb623814d9b803ac8dee3df6703e4b4b493bc2799 (diff)
- 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
-rw-r--r--admin/include/functions.php49
-rw-r--r--admin/stats.php117
-rw-r--r--include/functions.inc.php8
-rw-r--r--install/db/71-database.php58
-rw-r--r--install/phpwebgallery_structure.sql7
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;
--