aboutsummaryrefslogtreecommitdiffstats
path: root/admin
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--admin/cat_list.php2
-rw-r--r--admin/include/functions.php225
-rw-r--r--admin/include/functions_metadata.php149
-rw-r--r--admin/update.php43
4 files changed, 218 insertions, 201 deletions
diff --git a/admin/cat_list.php b/admin/cat_list.php
index eac3869b3..b5404ea0c 100644
--- a/admin/cat_list.php
+++ b/admin/cat_list.php
@@ -208,6 +208,7 @@ UPDATE '.CATEGORIES_TABLE.'
array_push($categories, $current);
array_shift($categories);
}
+ update_global_rank($_GET['parent_id']);
}
else if (isset($_GET['down']) and is_numeric($_GET['down']))
{
@@ -267,6 +268,7 @@ UPDATE '.CATEGORIES_TABLE.'
array_unshift($categories, $current);
array_pop($categories);
}
+ update_global_rank($_GET['parent_id']);
}
reset($categories);
// +-----------------------------------------------------------------------+
diff --git a/admin/include/functions.php b/admin/include/functions.php
index c78df307e..9df0303c0 100644
--- a/admin/include/functions.php
+++ b/admin/include/functions.php
@@ -466,66 +466,59 @@ SELECT category_id, COUNT(image_id) AS count, max(date_available) AS date_last
GROUP BY category_id
;';
$result = pwg_query( $query );
+ $datas = array();
while ( $row = mysql_fetch_array( $result ) )
{
array_push($cat_ids, $row['category_id']);
- $query = '
-UPDATE '.CATEGORIES_TABLE.'
- SET date_last = \''.$row['date_last'].'\'
- , nb_images = '.$row['count'].'
- WHERE id = '.$row['category_id'].'
-;';
- pwg_query($query);
+ array_push($datas, array('id' => $row['category_id'],
+ 'date_last' => $row['date_last'],
+ 'count' => $row['count']));
}
+ $fields = array('primary' => array('id'),
+ 'update' => array('date_last', 'count'));
+ mass_updates(CATEGORIES_TABLE, $fields, $datas);
if (count($cat_ids) > 0)
{
+ // find all categories where the setted representative is not possible
$query = '
-SELECT id, representative_picture_id
- FROM '.CATEGORIES_TABLE.'
+SELECT id
+ FROM '.CATEGORIES_TABLE.' LEFT JOIN '.IMAGE_CATEGORY_TABLE.'
+ ON id = category_id AND representative_picture_id = image_id
WHERE representative_picture_id IS NOT NULL
AND id IN ('.implode(',', $cat_ids).')
+ AND category_id IS NULL
;';
$result = pwg_query($query);
while ($row = mysql_fetch_array($result))
{
+ // set a new representative element for this category
$query = '
SELECT image_id
FROM '.IMAGE_CATEGORY_TABLE.'
WHERE category_id = '.$row['id'].'
- AND image_id = '.$row['representative_picture_id'].'
+ ORDER BY RAND()
+ LIMIT 0,1
;';
$sub_result = pwg_query($query);
- if (mysql_num_rows($sub_result) == 0)
+ if (mysql_num_rows($sub_result) > 0)
{
- // set a new representative element for this category
+ list($representative) = mysql_fetch_array($sub_result);
$query = '
-SELECT image_id
- FROM '.IMAGE_CATEGORY_TABLE.'
- WHERE category_id = '.$row['id'].'
- ORDER BY RAND()
- LIMIT 0,1
-;';
- $sub_sub_result = pwg_query($query);
- if (mysql_num_rows($sub_sub_result) > 0)
- {
- list($representative) = mysql_fetch_array(pwg_query($query));
- $query = '
UPDATE '.CATEGORIES_TABLE.'
SET representative_picture_id = '.$representative.'
WHERE id = '.$row['id'].'
;';
- pwg_query($query);
- }
- else
- {
- $query = '
+ pwg_query($query);
+ }
+ else
+ {
+ $query = '
UPDATE '.CATEGORIES_TABLE.'
SET representative_picture_id = NULL
WHERE id = '.$row['id'].'
;';
- pwg_query($query);
- }
+ pwg_query($query);
}
}
}
@@ -790,18 +783,18 @@ function my_error($header, $echo = true)
* inserts multiple lines in a table
*
* @param string table_name
- * @param array dbields
+ * @param array dbfields
* @param array inserts
* @return void
*/
-function mass_inserts($table_name, $dbfields, $inserts)
+function mass_inserts($table_name, $dbfields, $datas)
{
// inserts all found categories
$query = '
INSERT INTO '.$table_name.'
('.implode(',', $dbfields).')
VALUES';
- foreach ($inserts as $insert_id => $insert)
+ foreach ($datas as $insert_id => $insert)
{
$query.= '
';
@@ -832,4 +825,170 @@ INSERT INTO '.$table_name.'
;';
pwg_query($query);
}
+
+/**
+ * updates multiple lines in a table
+ *
+ * @param string table_name
+ * @param array dbfields
+ * @param array datas
+ * @return void
+ */
+function mass_updates($tablename, $dbfields, $datas)
+{
+ // depending on the MySQL version, we use the multi table update or N
+ // update queries
+ $query = 'SELECT VERSION() AS version;';
+ $row = mysql_fetch_array(pwg_query($query));
+ if (version_compare($row['version'],'4.0.4') < 0)
+ {
+ // MySQL is prior to version 4.0.4, multi table update feature is not
+ // available
+ echo 'MySQL is prior to version 4.0.4, multi table update feature is not available<br />';
+ foreach ($datas as $data)
+ {
+ $query = '
+UPDATE '.$tablename.'
+ SET ';
+ foreach ($dbfields['update'] as $num => $key)
+ {
+ if ($num >= 1)
+ {
+ $query.= ",\n ";
+ }
+ $query.= $key.' = ';
+ if (isset($data[$key]))
+ {
+ $query.= '\''.$data[$key].'\'';
+ }
+ else
+ {
+ $query.= 'NULL';
+ }
+ }
+ $query.= '
+ WHERE ';
+ foreach ($dbfields['primary'] as $num => $key)
+ {
+ if ($num > 1)
+ {
+ $query.= ' AND ';
+ }
+ $query.= $key.' = \''.$data[$key].'\'';
+ }
+ $query.= '
+;';
+ pwg_query($query);
+ }
+ }
+ else
+ {
+ // creation of the temporary table
+ $query = '
+DESCRIBE '.$tablename.'
+;';
+ $result = pwg_query($query);
+ $columns = array();
+ $all_fields = array_merge($dbfields['primary'], $dbfields['update']);
+ while ($row = mysql_fetch_array($result))
+ {
+ if (in_array($row['Field'], $all_fields))
+ {
+ $column = $row['Field'];
+ $column.= ' '.$row['Type'];
+ if (!isset($row['Null']) or $row['Null'] == '')
+ {
+ $column.= ' NOT NULL';
+ }
+ if (isset($row['Default']))
+ {
+ $column.= " default '".$row['Default']."'";
+ }
+ array_push($columns, $column);
+ }
+ }
+ $query = '
+CREATE TEMPORARY TABLE '.$tablename.'_temporary
+(
+'.implode(",\n", $columns).',
+PRIMARY KEY (id)
+)
+;';
+ pwg_query($query);
+ mass_inserts($tablename, $all_fields, $datas);
+ // update of images table by joining with temporary table
+ $query = '
+UPDATE '.$tablename.' AS t1, '.$tablename.'_temporary AS t2
+ SET '.implode("\n , ",
+ array_map(
+ create_function('$s', 'return "t1.$s = t2.$s";')
+ , $dbfields['update'])).'
+ WHERE '.implode("\n AND ",
+ array_map(
+ create_function('$s', 'return "t1.$s = t2.$s";')
+ , $dbfields['primary'])).'
+;';
+ pwg_query($query);
+ $query = '
+DROP TABLE '.$tablename.'_temporary
+;';
+ pwg_query($query);
+ }
+}
+
+/**
+ * updates the global_rank of categories under the given id_uppercat
+ *
+ * @param int id_uppercat
+ * @return void
+ */
+function update_global_rank($id_uppercat = 'all')
+{
+ $query = '
+SELECT id,rank
+ FROM '.CATEGORIES_TABLE.'
+;';
+ $result = pwg_query($query);
+ $ranks_array = array();
+ while ($row = mysql_fetch_array($result))
+ {
+ $ranks_array[$row['id']] = $row['rank'];
+ }
+
+ // which categories to update ?
+ $uppercats_array = array();
+
+ $query = '
+SELECT id,uppercats
+ FROM '.CATEGORIES_TABLE;
+ if (is_numeric($id_uppercat))
+ {
+ $query.= '
+ WHERE uppercats REGEXP \'(^|,)'.$id_uppercat.'(,|$)\'
+ AND id != '.$id_uppercat.'
+';
+ }
+ $query.= '
+;';
+ $result = pwg_query($query);
+ while ($row = mysql_fetch_array($result))
+ {
+ $uppercats_array[$row['id']] = $row['uppercats'];
+ }
+
+ $datas = array();
+ foreach ($uppercats_array as $id => $uppercats)
+ {
+ $data = array();
+ $data['id'] = $id;
+ $global_rank = preg_replace('/(\d+)/e',
+ "\$ranks_array['$1']",
+ str_replace(',', '.', $uppercats));
+ $data['global_rank'] = $global_rank;
+ array_push($datas, $data);
+ }
+
+ $fields = array('primary' => array('id'), 'update' => array('global_rank'));
+ mass_updates(CATEGORIES_TABLE, $fields, $datas);
+}
?>
diff --git a/admin/include/functions_metadata.php b/admin/include/functions_metadata.php
index 2e66606d2..023ab219e 100644
--- a/admin/include/functions_metadata.php
+++ b/admin/include/functions_metadata.php
@@ -68,18 +68,18 @@ function update_metadata($files)
define('CURRENT_DATE', date('Y-m-d'));
}
- $inserts = array();
+ $datas = array();
foreach ($files as $id => $file)
{
- $insert = array();
- $insert['id'] = $id;
- $insert['filesize'] = floor(filesize($file)/1024);
+ $data = array();
+ $data['id'] = $id;
+ $data['filesize'] = floor(filesize($file)/1024);
if ($image_size = @getimagesize($file))
{
- $insert['width'] = $image_size[0];
- $insert['height'] = $image_size[1];
+ $data['width'] = $image_size[0];
+ $data['height'] = $image_size[1];
}
if ($conf['use_exif'])
@@ -88,11 +88,8 @@ function update_metadata($files)
{
if (isset($exif['DateTime']))
{
- preg_match('/^(\d{4}).(\d{2}).(\d{2})/'
- ,$exif['DateTime']
- ,$matches);
- $insert['date_creation'] =
- "'".$matches[1].'-'.$matches[2].'-'.$matches[3]."'";
+ preg_match('/^(\d{4}).(\d{2}).(\d{2})/',$exif['DateTime'],$matches);
+ $data['date_creation'] = $matches[1].'-'.$matches[2].'-'.$matches[3];
}
}
}
@@ -104,135 +101,23 @@ function update_metadata($files)
{
foreach (array_keys($iptc) as $key)
{
- $insert[$key] = "'".addslashes($iptc[$key])."'";
+ $data[$key] = "'".addslashes($iptc[$key])."'";
}
}
}
- $insert['date_metadata_update'] = "'".CURRENT_DATE."'";
+ $data['date_metadata_update'] = CURRENT_DATE;
- array_push($inserts, $insert);
+ array_push($datas, $data);
}
- if (count($inserts) > 0)
+ if (count($datas) > 0)
{
- $dbfields = array(
- 'id','filesize','width','height','name','author','comment'
- ,'date_creation','keywords','date_metadata_update'
- );
-
- // depending on the MySQL version, we use the multi table update or N
- // update queries
- $query = 'SELECT VERSION() AS version;';
- $row = mysql_fetch_array(pwg_query($query));
- if (version_compare($row['version'],'4.0.4') < 0)
- {
- // MySQL is prior to version 4.0.4, multi table update feature is not
- // available
- echo 'MySQL is prior to version 4.0.4, multi table update feature is not available<br />';
- foreach ($inserts as $insert)
- {
- $query = '
-UPDATE '.IMAGES_TABLE.'
- SET ';
- foreach (array_diff(array_keys($insert),array('id')) as $num => $key)
- {
- if ($num > 1)
- {
- $query.= ', ';
- }
- $query.= $key.' = '.$insert[$key];
- }
- $query.= '
- WHERE id = '.$insert['id'].'
-;';
- // echo '<pre>'.$query.'</pre>';
- pwg_query($query);
- }
- }
- else
- {
- // creation of the temporary table
- $query = '
-DESCRIBE '.IMAGES_TABLE.'
-;';
- $result = pwg_query($query);
- $columns = array();
- while ($row = mysql_fetch_array($result))
- {
- if (in_array($row['Field'], $dbfields))
- {
- $column = $row['Field'];
- $column.= ' '.$row['Type'];
- if (!isset($row['Null']) or $row['Null'] == '')
- {
- $column.= ' NOT NULL';
- }
- if (isset($row['Default']))
- {
- $column.= " default '".$row['Default']."'";
- }
- array_push($columns, $column);
- }
- }
- $query = '
-CREATE TEMPORARY TABLE '.IMAGE_METADATA_TABLE.'
-(
-'.implode(",\n", $columns).',
-PRIMARY KEY (id)
-)
-;';
- // echo '<pre>'.$query.'</pre>';
- pwg_query($query);
- // inserts all found pictures
- $query = '
-INSERT INTO '.IMAGE_METADATA_TABLE.'
- ('.implode(',', $dbfields).')
- VALUES
- ';
- foreach ($inserts as $insert_id => $insert)
- {
- $query.= '
-';
- if ($insert_id > 0)
- {
- $query.= ',';
- }
- $query.= '(';
- foreach ($dbfields as $field_id => $dbfield)
- {
- if ($field_id > 0)
- {
- $query.= ',';
- }
-
- if (!isset($insert[$dbfield]) or $insert[$dbfield] == '')
- {
- $query.= 'NULL';
- }
- else
- {
- $query.= $insert[$dbfield];
- }
- }
- $query.=')';
- }
- $query.= '
-;';
- // echo '<pre>'.$query.'</pre>';
- pwg_query($query);
- // update of images table by joining with temporary table
- $query = '
-UPDATE '.IMAGES_TABLE.' AS images, '.IMAGE_METADATA_TABLE.' as metadata
- SET '.implode("\n , ",
- array_map(
- create_function('$s', 'return "images.$s = metadata.$s";')
- , array_diff($dbfields, array('id')))).'
- WHERE images.id = metadata.id
-;';
- echo '<pre>'.$query.'</pre>';
- pwg_query($query);
- }
+ $fields = array('primary' => array('id'),
+ 'update' => array('filesize','width','height','name',
+ 'author','comment','date_creation',
+ 'keywords','date_metadata_update'));
+ mass_updates(IMAGES_TABLE, $fields, $datas);
}
}
diff --git a/admin/update.php b/admin/update.php
index ac364e465..45bef3bce 100644
--- a/admin/update.php
+++ b/admin/update.php
@@ -42,8 +42,7 @@ define('CURRENT_DATE', date('Y-m-d'));
* the purpose of this function is to give a rank for all categories
* (insides its sub-category), even the newer that have none at te
* beginning. For this, ordering function selects all categories ordered by
- * rank ASC then name ASC for each uppercat. It also updates the global rank
- * which is able to order any two categorie in the whole tree
+ * rank ASC then name ASC for each uppercat.
*
* @returns void
*/
@@ -58,6 +57,7 @@ SELECT id, if(id_uppercat is null,\'\',id_uppercat) AS id_uppercat
ORDER BY id_uppercat,rank,name
;';
$result = pwg_query($query);
+ $datas = array();
while ($row = mysql_fetch_array($result))
{
if ($row['id_uppercat'] != $current_uppercat)
@@ -65,42 +65,12 @@ SELECT id, if(id_uppercat is null,\'\',id_uppercat) AS id_uppercat
$current_rank = 0;
$current_uppercat = $row['id_uppercat'];
}
- $query = '
-UPDATE '.CATEGORIES_TABLE.'
- SET rank = '.++$current_rank.'
- WHERE id = '.$row['id'].'
-;';
- pwg_query($query);
+ $data = array('id' => $row['id'], 'rank' => ++$current_rank);
+ array_push($datas, $data);
}
- // global rank update
- $query = '
-UPDATE '.CATEGORIES_TABLE.'
- SET global_rank = rank
- WHERE id_uppercat IS NULL
-;';
- pwg_query($query);
- $query = '
-SELECT DISTINCT(id_uppercat)
- FROM '.CATEGORIES_TABLE.'
- WHERE id_uppercat IS NOT NULL
-;';
- $result = pwg_query($query);
- while ($row = mysql_fetch_array($result))
- {
- $query = '
-SELECT global_rank
- FROM '.CATEGORIES_TABLE.'
- WHERE id = '.$row['id_uppercat'].'
-;';
- list($uppercat_global_rank) = mysql_fetch_array(pwg_query($query));
- $query = '
-UPDATE '.CATEGORIES_TABLE.'
- SET global_rank = CONCAT(\''.$uppercat_global_rank.'\', \'.\', rank)
- WHERE id_uppercat = '.$row['id_uppercat'].'
-;';
- pwg_query($query);
- }
+ $fields = array('primary' => array('id'), 'update' => array('rank'));
+ mass_updates(CATEGORIES_TABLE, $fields, $datas);
}
function insert_local_category($id_uppercat)
@@ -650,6 +620,7 @@ else if (isset($_POST['submit'])
echo get_elapsed_time($start,get_moment()).' for update_category(all)<br />';
$start = get_moment();
ordering();
+ update_global_rank();
echo get_elapsed_time($start, get_moment()).' for ordering categories<br />';
}
// +-----------------------------------------------------------------------+
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-07 07:12:24 +0000