/** For every album in the gallery, look for its file. Delete from the database
* if the file does not exist. Do the same for images. Clean up comments that have
* been left orphaned.
*
* Returns true if the operation was interrupted because it was taking too long
*
* @param bool $cascade garbage collect every image and album in the gallery.
* @param bool $complete garbage collect every image and album in the *database* - completely cleans the database.
* @param int $restart Image ID to restart scan from
* @return bool
*/
function garbageCollect($cascade = true, $complete = false, $restart = '')
{
if (empty($restart)) {
// Check for the existence of top-level albums (subalbums handled recursively).
$result = query("SELECT * FROM " . prefix('albums'));
$dead = array();
$live = array('');
// purge the root album if it exists
$deadalbumthemes = array();
// Load the albums from disk
$albumfolder = getAlbumFolder();
while ($row = mysql_fetch_assoc($result)) {
if (!file_exists($albumfolder . UTF8ToFilesystem($row['folder'])) || in_array($row['folder'], $live)) {
$dead[] = $row['id'];
if ($row['album_theme'] !== '') {
// orphaned album theme options table
$deadalbumthemes[$row['id']] = $row['folder'];
}
} else {
$live[] = $row['folder'];
}
}
if (count($dead) > 0) {
/* delete the dead albums from the DB */
$first = array_pop($dead);
$sql1 = "DELETE FROM " . prefix('albums') . " WHERE `id`='{$first}'";
$sql2 = "DELETE FROM " . prefix('images') . " WHERE `albumid`='{$first}'";
$sql3 = "DELETE FROM " . prefix('comments') . " WHERE `type`='albums' AND `ownerid`='{$first}'";
$sql4 = "DELETE FROM " . prefix('obj_to_tag') . " WHERE `type`='albums' AND `objectid`='{$first}'";
foreach ($dead as $albumid) {
$sql1 .= " OR `id` = '{$albumid}'";
$sql2 .= " OR `albumid` = '{$albumid}'";
$sql3 .= " OR `ownerid` = '{$albumid}'";
$sql4 .= " OR `objectid` = '{$albumid}'";
}
$n = query($sql1);
if (!$complete && $n > 0 && $cascade) {
query($sql2);
query($sql3);
query($sql4);
}
}
if (count($deadalbumthemes) > 0) {
// delete the album theme options tables for dead albums
foreach ($deadalbumthemes as $id => $deadtable) {
$sql = 'DELETE FROM ' . prefix('options') . ' WHERE `ownerid`=' . $id;
query($sql, true);
}
}
}
if ($complete) {
if (empty($restart)) {
/* refresh 'metadata' of dynamic albums */
$albumfolder = getAlbumFolder();
$albumids = query_full_array("SELECT `id`, `mtime`, `folder` FROM " . prefix('albums') . " WHERE `dynamic`='1'");
foreach ($albumids as $album) {
if (($mtime = filemtime($albumfolder . UTF8ToFilesystem($album['folder']))) > $album['mtime']) {
// refresh
$data = file_get_contents($albumfolder . UTF8ToFilesystem($album['folder']));
while (!empty($data)) {
$data1 = trim(substr($data, 0, $i = strpos($data, "\n")));
if ($i === false) {
$data1 = $data;
$data = '';
} else {
$data = substr($data, $i + 1);
}
if (strpos($data1, 'WORDS=') !== false) {
$words = "words=" . urlencode(substr($data1, 6));
}
if (strpos($data1, 'THUMB=') !== false) {
$thumb = trim(substr($data1, 6));
}
if (strpos($data1, 'FIELDS=') !== false) {
$fields = "&searchfields=" . trim(substr($data1, 7));
}
}
if (!empty($words)) {
if (empty($fields)) {
$fields = '&searchfields=4';
}
}
$sql = "UPDATE " . prefix('albums') . "SET `search_params`=\"{$words}.{$fields}\", `thumb`=\"{$thumb}\", `mtime`=\"{$mtime}\" WHERE `id`=\"" . $album['id'] . "\"";
query($sql);
}
}
/* Delete all image entries that don't belong to an album at all. */
$albumids = query_full_array("SELECT `id` FROM " . prefix('albums'));
/* all the album IDs */
$idsofalbums = array();
foreach ($albumids as $row) {
$idsofalbums[] = $row['id'];
}
$imageAlbums = query_full_array("SELECT DISTINCT `albumid` FROM " . prefix('images'));
/* albumids of all the images */
$albumidsofimages = array();
foreach ($imageAlbums as $row) {
$albumidsofimages[] = $row['albumid'];
}
$orphans = array_diff($albumidsofimages, $idsofalbums);
/* albumids of images with no album */
if (count($orphans) > 0) {
/* delete dead images from the DB */
$firstrow = array_pop($orphans);
$sql = "DELETE FROM " . prefix('images') . " WHERE `albumid`='" . $firstrow . "'";
foreach ($orphans as $id) {
$sql .= " OR `albumid`='" . $id . "'";
}
query($sql);
// Then go into existing albums recursively to clean them... very invasive.
foreach ($this->getAlbums(0) as $folder) {
$album = new Album($this, $folder);
if (!$album->isDynamic()) {
if (is_null($album->getDateTime())) {
// see if we can get one from an image
$image = $album->getImage(0);
if (is_object($image)) {
$album->setDateTime($image->getDateTime());
}
}
$album->garbageCollect(true);
$album->preLoad();
}
}
}
}
/* Look for image records where the file no longer exists. While at it, check for images with IPTC data to update the DB */
$start = array_sum(explode(" ", microtime()));
// protect against too much processing.
if (!empty($restart)) {
$restartwhere = ' WHERE `id`>' . $restart;
} else {
$restartwhere = '';
}
$sql = 'SELECT `id`, `albumid`, `filename`, `desc`, `title`, `date`, `mtime` FROM ' . prefix('images') . $restartwhere . ' ORDER BY `id`';
$images = query_full_array($sql);
foreach ($images as $image) {
$sql = 'SELECT `folder` FROM ' . prefix('albums') . ' WHERE `id`="' . $image['albumid'] . '";';
$row = query_single_row($sql);
$imageName = UTF8ToFilesystem(getAlbumFolder() . $row['folder'] . '/' . $image['filename']);
if (file_exists($imageName)) {
if ($image['mtime'] != filemtime($imageName)) {
// file has changed since we last saw it
/* check metadata */
$metadata = getImageMetadata($imageName);
$set = '';
/* title */
$defaultTitle = substr($image['filename'], 0, strrpos($image['filename'], '.'));
if (empty($defaultTitle)) {
$defaultTitle = $image['filename'];
}
if ($defaultTitle == $image['title']) {
/* default title */
if (isset($metadata['title'])) {
$set = ',`title`="' . mysql_real_escape_string($metadata['title']) . '"';
}
}
/* description */
if (!isset($row['desc'])) {
if (isset($metadata['desc'])) {
$set .= ', `desc`="' . mysql_real_escape_string($metadata['desc']) . '"';
}
}
/* tags */
if (isset($metadata['tags'])) {
$tags = $metadata['tags'];
storeTags($tags, $image['id'], 'images');
}
/* location, city, state, and country */
if (isset($metadata['location'])) {
$set .= ', `location`="' . mysql_real_escape_string($metadata['location']) . '"';
}
if (isset($metadata['city'])) {
$set .= ', `city`="' . mysql_real_escape_string($metadata['city']) . '"';
}
if (isset($metadata['state'])) {
$set .= ', `state`="' . mysql_real_escape_string($metadata['state']) . '"';
}
if (isset($metadata['country'])) {
$set .= ', `country`="' . mysql_real_escape_string($metadata['country']) . '"';
}
/* credit & copyright */
if (isset($metadata['credit'])) {
$set .= ', `credit`="' . escape($metadata['credit']) . '"';
}
if (isset($metadata['copyright'])) {
$set .= ', `copyright`="' . escape($metadata['copyright']) . '"';
}
/* date (for sorting) */
$newDate = strftime('%Y-%m-%d %T', filemtime($imageName));
if (isset($metadata['date'])) {
$dt = dateTimeConvert($metadata['date']);
if ($dt !== false) {
// flaw in exif/iptc data?
$newDate = $dt;
}
}
$set .= ', `date`="' . $newDate . '"';
/* update DB is necessary */
$sql = "UPDATE " . prefix('images') . " SET `EXIFValid`=0,`mtime`=" . filemtime($imageName) . $set . " WHERE `id`='" . $image['id'] . "'";
query($sql);
}
} else {
$sql = 'DELETE FROM ' . prefix('images') . ' WHERE `id`="' . $image['id'] . '";';
$result = query($sql);
$sql = 'DELETE FROM ' . prefix('comments') . ' WHERE `type` IN (' . zp_image_types('"') . ') AND `ownerid` ="' . $image['id'] . '";';
$result = query($sql);
}
if (array_sum(explode(" ", microtime())) - $start >= 10) {
return $image['id'];
// avoide excessive processing
}
}
/* clean the comments table */
/* do the images */
$imageids = query_full_array('SELECT `id` FROM ' . prefix('images'));
/* all the image IDs */
$idsofimages = array();
foreach ($imageids as $row) {
$idsofimages[] = $row['id'];
}
$commentImages = query_full_array("SELECT DISTINCT `ownerid` FROM " . prefix('comments') . 'WHERE `type` IN (' . zp_image_types('"') . ')');
/* imageids of all the comments */
$imageidsofcomments = array();
foreach ($commentImages as $row) {
$imageidsofcomments[] = $row['ownerid'];
}
$orphans = array_diff($imageidsofcomments, $idsofimages);
/* image ids of comments with no image */
if (count($orphans) > 0) {
/* delete dead comments from the DB */
$firstrow = array_pop($orphans);
$sql = "DELETE FROM " . prefix('comments') . " WHERE `type` IN (" . zp_image_types("'") . ") AND `ownerid`='" . $firstrow . "'";
foreach ($orphans as $id) {
$sql .= " OR `ownerid`='" . $id . "'";
}
query($sql);
}
/* do the same for album comments */
$albumids = query_full_array('SELECT `id` FROM ' . prefix('albums'));
/* all the album IDs */
$idsofalbums = array();
foreach ($albumids as $row) {
$idsofalbums[] = $row['id'];
}
$commentAlbums = query_full_array("SELECT DISTINCT `ownerid` FROM " . prefix('comments') . 'WHERE `type`="albums"');
/* album ids of all the comments */
$albumidsofcomments = array();
foreach ($commentAlbums as $row) {
$albumidsofcomments[] = $row['ownerid'];
}
$orphans = array_diff($albumidsofcomments, $idsofalbums);
/* album ids of comments with no album */
if (count($orphans) > 0) {
/* delete dead comments from the DB */
$firstrow = array_pop($orphans);
$sql = "DELETE FROM " . prefix('comments') . "WHERE `type`='albums' AND `ownerid`='" . $firstrow . "'";
foreach ($orphans as $id) {
$sql .= " OR `ownerid`='" . $id . "'";
}
query($sql);
}
/* clean the tags table */
/* do the images */
$tagImages = query_full_array("SELECT DISTINCT `objectid` FROM " . prefix('obj_to_tag') . 'WHERE `type` IN (' . zp_image_types('"') . ')');
/* imageids of all the comments */
$imageidsoftags = array();
foreach ($tagImages as $row) {
$imageidsoftags[] = $row['objectid'];
}
$orphans = array_diff($imageidsoftags, $idsofimages);
/* image ids of comments with no image */
if (count($orphans) > 0) {
/* delete dead tags from the DB */
$firstrow = array_pop($orphans);
$sql = "DELETE FROM " . prefix('obj_to_tag') . " WHERE `type` IN (" . zp_image_types('"') . ") AND (`objectid`='" . $firstrow . "'";
foreach ($orphans as $id) {
$sql .= " OR `objectid`='" . $id . "'";
}
$sql .= ')';
query($sql);
}
/* do the same for album tags */
$tagAlbums = query_full_array("SELECT DISTINCT `objectid` FROM " . prefix('obj_to_tag') . 'WHERE `type`="albums"');
/* album ids of all the comments */
$albumidsoftags = array();
foreach ($tagAlbums as $row) {
$albumidsoftags[] = $row['objectid'];
}
$orphans = array_diff($albumidsoftags, $idsofalbums);
/* album ids of comments with no album */
if (count($orphans) > 0) {
/* delete dead tags from the DB */
$firstrow = array_pop($orphans);
$sql = "DELETE FROM " . prefix('obj_to_tag') . "WHERE `type`='albums' AND `objectid`='" . $firstrow . "'";
foreach ($orphans as $id) {
$sql .= " OR `objectid`='" . $id . "'";
}
query($sql);
}
}
return false;
}
/**
* Gets an array of comments for the current admin
*
* @param int $number how many comments desired
* @return array
*/
function fetchComments($number)
{
if ($number) {
$limit = " LIMIT {$number}";
} else {
$limit = '';
}
$comments = array();
if (zp_loggedin(ADMIN_RIGHTS | COMMENT_RIGHTS)) {
if (zp_loggedin(ADMIN_RIGHTS | MANAGE_ALL_ALBUM_RIGHTS)) {
$sql = "SELECT *, (date + 0) AS date FROM " . prefix('comments') . " ORDER BY id DESC{$limit}";
$comments = query_full_array($sql);
} else {
$albumlist = getManagedAlbumList();
$albumIDs = array();
foreach ($albumlist as $albumname) {
$subalbums = getAllSubAlbumIDs($albumname);
foreach ($subalbums as $ID) {
$albumIDs[] = $ID['id'];
}
}
if (count($albumIDs) > 0) {
$sql = "SELECT *, (`date` + 0) AS date FROM " . prefix('comments') . " WHERE ";
$sql .= " (`type`='albums' AND (";
$i = 0;
foreach ($albumIDs as $ID) {
if ($i > 0) {
$sql .= " OR ";
}
$sql .= "(" . prefix('comments') . ".ownerid={$ID})";
$i++;
}
$sql .= ")) ";
$sql .= " ORDER BY id DESC{$limit}";
$albumcomments = query($sql);
if ($albumcomments) {
while ($comment = db_fetch_assoc($albumcomments)) {
$comments[$comment['id']] = $comment;
}
db_free_result($albumcomments);
}
$sql = "SELECT *, " . prefix('comments') . ".id as id, " . prefix('comments') . ".name as name, (" . prefix('comments') . ".date + 0) AS date, " . prefix('images') . ".`albumid` as albumid," . prefix('images') . ".`id` as imageid" . " FROM " . prefix('comments') . "," . prefix('images') . " WHERE ";
$sql .= "(`type` IN (" . zp_image_types("'") . ") AND (";
$i = 0;
foreach ($albumIDs as $ID) {
if ($i > 0) {
$sql .= " OR ";
}
$sql .= "(" . prefix('comments') . ".ownerid=" . prefix('images') . ".id AND " . prefix('images') . ".albumid={$ID})";
$i++;
}
$sql .= "))";
$sql .= " ORDER BY " . prefix('images') . ".`id` DESC{$limit}";
$imagecomments = query($sql);
if ($imagecomments) {
while ($comment = db_fetch_assoc($imagecomments)) {
$comments[$comment['id']] = $comment;
}
db_free_result($imagecomments);
}
krsort($comments);
if ($number) {
if ($number < count($comments)) {
$comments = array_slice($comments, 0, $number);
}
}
}
}
}
return $comments;
}
/** For every album in the gallery, look for its file. Delete from the database
* if the file does not exist. Do the same for images. Clean up comments that have
* been left orphaned.
*
* Returns true if the operation was interrupted because it was taking too long
*
* @param bool $cascade garbage collect every image and album in the gallery.
* @param bool $complete garbage collect every image and album in the *database* - completely cleans the database.
* @param int $restart Image ID to restart scan from
* @return bool
*/
function garbageCollect($cascade = true, $complete = false, $restart = '')
{
global $_zp_gallery, $_zp_authority;
if (empty($restart)) {
setOption('last_garbage_collect', time());
/* purge old search cache items */
$sql = 'DELETE FROM ' . prefix('search_cache');
if (!$complete) {
$sql .= ' WHERE `date`<' . db_quote(date('Y-m-d H:m:s', time() - SEARCH_CACHE_DURATION * 60));
}
$result = query($sql);
/* clean the comments table */
$this->commentClean('images');
$this->commentClean('albums');
$this->commentClean('news');
$this->commentClean('pages');
// clean up obj_to_tag
$dead = array();
$result = query("SELECT * FROM " . prefix('obj_to_tag'));
if ($result) {
while ($row = db_fetch_assoc($result)) {
$tbl = $row['type'];
$dbtag = query_single_row("SELECT `id` FROM " . prefix('tags') . " WHERE `id`='" . $row['tagid'] . "'", false);
if (!$dbtag) {
$dead[] = $row['id'];
}
$dbtag = query_single_row("SELECT `id` FROM " . prefix($tbl) . " WHERE `id`='" . $row['objectid'] . "'", false);
if (!$dbtag) {
$dead[] = $row['id'];
}
}
db_free_result($result);
}
if (!empty($dead)) {
$dead = array_unique($dead);
query('DELETE FROM ' . prefix('obj_to_tag') . ' WHERE `id`=' . implode(' OR `id`=', $dead));
}
// clean up admin_to_object
$dead = array();
$result = query("SELECT * FROM " . prefix('admin_to_object'));
if ($result) {
while ($row = db_fetch_assoc($result)) {
if (!$_zp_authority->validID($row['adminid'])) {
$dead[] = $row['id'];
}
$tbl = $row['type'];
$dbtag = query_single_row("SELECT `id` FROM " . prefix($tbl) . " WHERE `id`='" . $row['objectid'] . "'", false);
if (!$dbtag) {
$dead[] = $row['id'];
}
}
db_free_result($result);
}
if (!empty($dead)) {
$dead = array_unique($dead);
query('DELETE FROM ' . prefix('admin_to_object') . ' WHERE `id`=' . implode(' OR `id`=', $dead));
}
// clean up news2cat
$dead = array();
$result = query("SELECT * FROM " . prefix('news2cat'));
if ($result) {
while ($row = db_fetch_assoc($result)) {
$dbtag = query_single_row("SELECT `id` FROM " . prefix('news') . " WHERE `id`='" . $row['news_id'] . "'", false);
if (!$dbtag) {
$dead[] = $row['id'];
}
$dbtag = query_single_row("SELECT `id` FROM " . prefix('news_categories') . " WHERE `id`='" . $row['cat_id'] . "'", false);
if (!$dbtag) {
$dead[] = $row['id'];
}
}
db_free_result($result);
}
if (!empty($dead)) {
$dead = array_unique($dead);
query('DELETE FROM ' . prefix('news2cat') . ' WHERE `id`=' . implode(' OR `id`=', $dead));
}
// Check for the existence albums
$dead = array();
$live = array('');
// purge the root album if it exists
$deadalbumthemes = array();
// Load the albums from disk
$result = query("SELECT * FROM " . prefix('albums'));
while ($row = db_fetch_assoc($result)) {
$albumpath = internalToFilesystem($row['folder']);
$albumpath_valid = preg_replace('~/\\.*/~', '/', $albumpath);
$albumpath_valid = ltrim(trim($albumpath_valid, '/'), './');
$illegal = $albumpath != $albumpath_valid;
$valid = file_exists(ALBUM_FOLDER_SERVERPATH . $albumpath_valid) && (hasDynamicAlbumSuffix($albumpath_valid) || is_dir(ALBUM_FOLDER_SERVERPATH . $albumpath_valid));
if ($valid && $illegal) {
// maybe there is only one record so we can fix it.
$valid = query('UPDATE ' . prefix('albums') . ' SET `folder`=' . db_quote($albumpath_valid) . ' WHERE `id`=' . $row['id'], false);
debugLog(sprintf(gettext('Invalid album folder: %1$s %2$s'), $albumpath, $valid ? gettext('fixed') : gettext('discarded')));
}
if (!$valid || in_array($row['folder'], $live)) {
$dead[] = $row['id'];
if ($row['album_theme'] !== '') {
// orphaned album theme options table
$deadalbumthemes[$row['id']] = $row['folder'];
}
} else {
$live[] = $row['folder'];
}
}
db_free_result($result);
if (count($dead) > 0) {
/* delete the dead albums from the DB */
asort($dead);
$criteria = '(' . implode(',', $dead) . ')';
$first = array_pop($dead);
$sql1 = "DELETE FROM " . prefix('albums') . " WHERE `id` IN {$criteria}";
$n = query($sql1);
if (!$complete && $n && $cascade) {
$sql2 = "DELETE FROM " . prefix('images') . " WHERE `albumid` IN {$criteria}";
query($sql2);
$sql3 = "DELETE FROM " . prefix('comments') . " WHERE `type`='albums' AND `ownerid` IN {$criteria}";
query($sql3);
$sql4 = "DELETE FROM " . prefix('obj_to_tag') . " WHERE `type`='albums' AND `objectid` IN {$criteria}";
query($sql4);
}
}
if (count($deadalbumthemes) > 0) {
// delete the album theme options tables for dead albums
foreach ($deadalbumthemes as $id => $deadtable) {
$sql = 'DELETE FROM ' . prefix('options') . ' WHERE `ownerid`=' . $id;
query($sql, false);
}
}
}
if ($complete) {
if (empty($restart)) {
/* check album parent linkage */
$albums = $_zp_gallery->getAlbums();
foreach ($albums as $album) {
checkAlbumParentid($album, NULL, 'debuglog');
}
/* refresh 'metadata' albums */
$albumids = query("SELECT `id`, `mtime`, `folder`, `dynamic` FROM " . prefix('albums'));
if ($albumids) {
while ($analbum = db_fetch_assoc($albumids)) {
if (($mtime = filemtime(ALBUM_FOLDER_SERVERPATH . internalToFilesystem($analbum['folder']))) > $analbum['mtime']) {
// refresh
$album = newAlbum($analbum['folder']);
$album->set('mtime', $mtime);
if ($this->getAlbumUseImagedate()) {
$album->setDateTime(NULL);
}
if ($album->isDynamic()) {
$data = file_get_contents($album->localpath);
$thumb = getOption('AlbumThumbSelect');
$words = $fields = '';
while (!empty($data)) {
$data1 = trim(substr($data, 0, $i = strpos($data, "\n")));
if ($i === false) {
$data1 = $data;
$data = '';
} else {
$data = substr($data, $i + 1);
}
if (strpos($data1, 'WORDS=') !== false) {
$words = "words=" . urlencode(substr($data1, 6));
}
if (strpos($data1, 'THUMB=') !== false) {
$thumb = trim(substr($data1, 6));
}
if (strpos($data1, 'FIELDS=') !== false) {
$fields = "&searchfields=" . trim(substr($data1, 7));
}
}
if (!empty($words)) {
if (empty($fields)) {
$fields = '&searchfields=tags';
}
}
$album->set('search_params', $words . $fields);
$album->set('thumb', $thumb);
}
$album->save();
zp_apply_filter('album_refresh', $album);
}
}
db_free_result($albumids);
}
/* Delete all image entries that don't belong to an album at all. */
$albumids = query("SELECT `id` FROM " . prefix('albums'));
/* all the album IDs */
$idsofalbums = array();
if ($albumids) {
while ($row = db_fetch_assoc($albumids)) {
$idsofalbums[] = $row['id'];
}
db_free_result($albumids);
}
$imageAlbums = query("SELECT DISTINCT `albumid` FROM " . prefix('images'));
/* albumids of all the images */
$albumidsofimages = array();
if ($imageAlbums) {
while ($row = db_fetch_assoc($imageAlbums)) {
$albumidsofimages[] = $row['albumid'];
}
db_free_result($imageAlbums);
}
$orphans = array_diff($albumidsofimages, $idsofalbums);
/* albumids of images with no album */
if (count($orphans) > 0) {
/* delete dead images from the DB */
$sql = "DELETE FROM " . prefix('images') . " WHERE ";
foreach ($orphans as $id) {
if (is_null($id)) {
$sql .= "`albumid` is NULL OR ";
} else {
$sql .= " `albumid`='" . $id . "' OR ";
}
}
$sql = substr($sql, 0, -4);
query($sql);
// Then go into existing albums recursively to clean them... very invasive.
foreach ($this->getAlbums(0) as $folder) {
$album = newAlbum($folder);
if (!$album->isDynamic()) {
if (is_null($album->getDateTime())) {
// see if we can get one from an image
$images = $album->getImages(0, 0);
if (count($images) > 0) {
$image = newImage($album, array_shift($images));
$album->setDateTime($image->getDateTime());
$album->save();
}
}
$album->garbageCollect(true);
}
zp_apply_filter('album_refresh', $album);
}
}
}
/* Look for image records where the file no longer exists. While at it, check for images with IPTC data to update the DB */
$start = array_sum(explode(" ", microtime()));
// protect against too much processing.
if (!empty($restart)) {
$restartwhere = ' WHERE `id`>' . $restart . ' AND `mtime`=0';
} else {
$restartwhere = ' WHERE `mtime`=0';
}
define('RECORD_LIMIT', 5);
$sql = 'SELECT * FROM ' . prefix('images') . $restartwhere . ' ORDER BY `id` LIMIT ' . (RECORD_LIMIT + 2);
$images = query($sql);
if ($images) {
$c = 0;
while ($image = db_fetch_assoc($images)) {
$albumobj = getItemByID('albums', $image['albumid']);
if ($albumobj->exists && file_exists($imageName = internalToFilesystem(ALBUM_FOLDER_SERVERPATH . $albumobj->name . '/' . $image['filename']))) {
if ($image['mtime'] != ($mtime = filemtime($imageName))) {
// file has changed since we last saw it
$imageobj = newImage($albumobj, $image['filename']);
$imageobj->set('mtime', $mtime);
$imageobj->updateMetaData();
// prime the EXIF/IPTC fields
$imageobj->updateDimensions();
// update the width/height & account for rotation
$imageobj->save();
zp_apply_filter('image_refresh', $imageobj);
}
} else {
$sql = 'DELETE FROM ' . prefix('images') . ' WHERE `id`="' . $image['id'] . '";';
$result = query($sql);
$sql = 'DELETE FROM ' . prefix('comments') . ' WHERE `type` IN (' . zp_image_types('"') . ') AND `ownerid` ="' . $image['id'] . '";';
$result = query($sql);
}
if (++$c >= RECORD_LIMIT) {
return $image['id'];
// avoide excessive processing
}
}
db_free_result($images);
}
// cleanup the tables
$resource = db_show('tables');
if ($resource) {
while ($row = db_fetch_assoc($resource)) {
$tbl = array_shift($row);
query('OPTIMIZE TABLE `' . $tbl . '`');
}
db_free_result($resource);
}
}
return false;
}
/**
* sortImageArray will sort an array of Images based on the given key. The
* key must be one of (filename, title, sort_order) at the moment.
*
* @param array $images The array of filenames to be sorted.
* @param string $sorttype optional sort type
* @param string $sortdirection optional sort direction
* @return array
*/
function sortImageArray($images, $sorttype = NULL, $sortdirection = NULL)
{
$mine = isMyAlbum($this->name, ALL_RIGHTS);
$key = $this->getSortKey($sorttype);
$direction = '';
if ($key != '`sort_order`') {
// manual sort is always ascending
if (!is_null($sortdirection)) {
$direction = ' ' . $sortdirection;
} else {
if ($this->getSortDirection('image')) {
$direction = ' DESC';
}
}
}
$result = query($sql = "SELECT `filename`, `title`, `sort_order`, `title`, `show`, `id` FROM " . prefix("images") . " WHERE `albumid`= '" . $this->id . "' ORDER BY " . $key . $direction);
$loop = 0;
do {
$hidden = array();
$results = array();
while ($row = mysql_fetch_assoc($result)) {
$results[] = $row;
}
if ($key == 'title') {
$results = sortByMultilingual($results, 'title', $direction == ' DESC');
} else {
if ($key == 'filename') {
if ($direction == 'DESC') {
$order = 'dsc';
} else {
$order = 'asc';
}
$results = sortMultiArray($results, 'filename', $order, true, false);
}
}
$i = 0;
$flippedimages = array_flip($images);
$images_to_keys = array();
$images_in_db = array();
$images_invisible = array();
foreach ($results as $row) {
// see what images are in the database so we can check for visible
$filename = $row['filename'];
if (isset($flippedimages[$filename])) {
// ignore db entries for images that no longer exist.
if ($row['show'] || $mine) {
// unpublished content available only to someone with rights on the album
$images_to_keys[$filename] = $i;
$i++;
}
$images_in_db[] = $filename;
} else {
$id = $row['id'];
query("DELETE FROM " . prefix('images') . " WHERE `id`={$id}");
// delete the record
query("DELETE FROM " . prefix('comments') . " WHERE `type` IN (" . zp_image_types("'") . ") AND `ownerid`= '{$id}'");
// remove image comments
}
}
// Place the images not yet in the database before those with sort columns.
// This is consistent with the sort oder of a NULL sort_order key in manual sorts
// but will almost certainly be wrong in all other cases.
$images_not_in_db = array_diff($images, $images_in_db);
if (count($images_not_in_db) > 0) {
$loop++;
foreach ($images_not_in_db as $filename) {
$imgobj = newImage($this, $filename);
// force it into the database
$images_to_keys[$filename] = $i;
$i++;
}
} else {
$loop = 0;
}
} while ($loop == 1);
$images = array_flip($images_to_keys);
ksort($images);
$images_ordered = array();
foreach ($images as $image) {
$images_ordered[] = $image;
}
return $images_ordered;
}
/**
* Gets latest comments for images and albums
*
* @param int $number how many comments you want.
* @param string $type "all" for all latest comments of all images and albums
* "image" for the lastest comments of one specific image
* "album" for the latest comments of one specific album
* @param int $itemID the ID of the element to get the comments for if $type != "all"
*/
function getLatestComments($number, $type = "all", $itemID = "")
{
global $_zp_gallery;
$itemID = sanitize_numeric($itemID);
$passwordcheck1 = "";
$passwordcheck2 = "";
if (!zp_loggedin(ADMIN_RIGHTS)) {
$albumscheck = query_full_array("SELECT * FROM " . prefix('albums') . " ORDER BY title");
foreach ($albumscheck as $albumcheck) {
$album = new Album($_zp_gallery, $albumcheck['folder']);
if ($album->isMyItem(LIST_RIGHTS) || !checkAlbumPassword($albumcheck['folder'])) {
$albumpasswordcheck1 = " AND i.albumid != " . $albumcheck['id'];
$albumpasswordcheck2 = " AND a.id != " . $albumcheck['id'];
$passwordcheck1 = $passwordcheck1 . $albumpasswordcheck1;
$passwordcheck2 = $passwordcheck2 . $albumpasswordcheck2;
}
}
}
switch ($type) {
case "image":
$whereImages = " WHERE i.show = 1 AND i.id = " . $itemID . " AND c.ownerid = " . $itemID . " AND i.albumid = a.id AND c.private = 0 AND c.inmoderation = 0 AND (c.type IN (" . zp_image_types("'") . "))" . $passwordcheck1;
break;
case "album":
$whereAlbums = " WHERE a.show = 1 AND a.id = " . $itemID . " AND c.ownerid = " . $itemID . " AND c.private = 0 AND c.inmoderation = 0 AND c.type = 'albums'" . $passwordcheck2;
break;
case "all":
$whereImages = " WHERE i.show = 1 AND c.ownerid = i.id AND i.albumid = a.id AND c.private = 0 AND c.inmoderation = 0 AND (c.type IN (" . zp_image_types("'") . "))" . $passwordcheck1;
$whereAlbums = " WHERE a.show = 1 AND c.ownerid = a.id AND c.private = 0 AND c.inmoderation = 0 AND c.type = 'albums'" . $passwordcheck2;
break;
}
$comments_images = array();
$comments_albums = array();
if ($type === "all" or $type === "image") {
$comments_images = query_full_array("SELECT c.id, i.title, i.filename, a.folder, a.title AS albumtitle, c.name, c.type, c.website," . " c.date, c.anon, c.comment FROM " . prefix('comments') . " AS c, " . prefix('images') . " AS i, " . prefix('albums') . " AS a " . $whereImages . " ORDER BY c.id DESC LIMIT {$number}");
}
if ($type === "all" or $type === "album") {
$comments_albums = query_full_array("SELECT c.id, a.folder, a.title AS albumtitle, c.name, c.type, c.website," . " c.date, c.anon, c.comment FROM " . prefix('comments') . " AS c, " . prefix('albums') . " AS a " . $whereAlbums . " ORDER BY c.id DESC LIMIT {$number}");
}
$comments = array();
foreach ($comments_albums as $comment) {
$comments[$comment['id']] = $comment;
}
foreach ($comments_images as $comment) {
$comments[$comment['id']] = $comment;
}
krsort($comments);
return array_slice($comments, 0, $number);
}
/** For every album in the gallery, look for its file. Delete from the database
* if the file does not exist. Do the same for images. Clean up comments that have
* been left orphaned.
*
* Returns true if the operation was interrupted because it was taking too long
*
* @param bool $cascade garbage collect every image and album in the gallery.
* @param bool $complete garbage collect every image and album in the *database* - completely cleans the database.
* @param int $restart Image ID to restart scan from
* @return bool
*/
function garbageCollect($cascade = true, $complete = false, $restart = '')
{
if (empty($restart)) {
setOption('last_garbage_collect', time());
/* clean the comments table */
$this->commentClean('images');
$this->commentClean('albums');
$this->commentClean('news');
$this->commentClean('pages');
// clean up obj_to_tag
$dead = array();
$result = query_full_array("SELECT * FROM " . prefix('obj_to_tag'));
if (is_array($result)) {
foreach ($result as $row) {
$dbtag = query_single_row("SELECT * FROM " . prefix('tags') . " WHERE `id`='" . $row['tagid'] . "'");
if (!$dbtag) {
$dead['id'] = $row['id'];
}
switch ($row['type']) {
case 'album':
$tbl = 'albums';
break;
default:
$tbl = $row['type'];
break;
}
$dbtag = query_single_row("SELECT * FROM " . prefix($tbl) . " WHERE `id`='" . $row['objectid'] . "'");
if (!$dbtag) {
$dead['id'] = $row['id'];
}
}
}
if (!empty($dead)) {
query('DELETE FROM ' . prefix('obj_to_tag') . ' WHERE `id`=' . implode(' OR `id`=', $dead));
}
// clean up admin_to_object
$dead = array();
$result = query_full_array("SELECT * FROM " . prefix('admin_to_object'));
if (is_array($result)) {
foreach ($result as $row) {
$dbtag = query_single_row("SELECT * FROM " . prefix('administrators') . " WHERE `id`='" . $row['adminid'] . "'");
if (!$dbtag) {
$dead['id'] = $row['id'];
}
switch ($row['type']) {
case 'album':
$tbl = 'albums';
break;
default:
$tbl = $row['type'];
break;
}
$dbtag = query_single_row("SELECT * FROM " . prefix($tbl) . " WHERE `id`='" . $row['objectid'] . "'");
if (!$dbtag) {
$dead['id'] = $row['id'];
}
}
}
if (!empty($dead)) {
query('DELETE FROM ' . prefix('admin_to_object') . ' WHERE `id`=' . implode(' OR `id`=', $dead));
}
// clean up news2cat
$dead = array();
$result = query_full_array("SELECT * FROM " . prefix('news2cat'));
if (is_array($result)) {
foreach ($result as $row) {
$dbtag = query_single_row("SELECT * FROM " . prefix('news') . " WHERE `id`='" . $row['news_id'] . "'");
if (!$dbtag) {
$dead['id'] = $row['id'];
}
$dbtag = query_single_row("SELECT * FROM " . prefix('news_categories') . " WHERE `id`='" . $row['cat_id'] . "'");
if (!$dbtag) {
$dead['id'] = $row['id'];
}
}
}
if (!empty($dead)) {
query('DELETE FROM ' . prefix('news2cat') . ' WHERE `id`=' . implode(' OR `id`=', $dead));
}
// Check for the existence of top-level albums (subalbums handled recursively).
$sql = "SELECT * FROM " . prefix('albums');
$result = query($sql);
$dead = array();
$live = array('');
// purge the root album if it exists
$deadalbumthemes = array();
// Load the albums from disk
while ($row = db_fetch_assoc($result)) {
$valid = file_exists($albumpath = ALBUM_FOLDER_SERVERPATH . internalToFilesystem($row['folder'])) && (hasDynamicAlbumSuffix($albumpath) || is_dir($albumpath) && strpos($albumpath, '/./') === false && strpos($albumpath, '/../') === false);
if (!$valid || in_array($row['folder'], $live)) {
$dead[] = $row['id'];
if ($row['album_theme'] !== '') {
// orphaned album theme options table
$deadalbumthemes[$row['id']] = $row['folder'];
}
} else {
$live[] = $row['folder'];
}
}
if (count($dead) > 0) {
/* delete the dead albums from the DB */
$first = array_pop($dead);
$sql1 = "DELETE FROM " . prefix('albums') . " WHERE `id`='{$first}'";
$sql2 = "DELETE FROM " . prefix('images') . " WHERE `albumid`='{$first}'";
$sql3 = "DELETE FROM " . prefix('comments') . " WHERE `type`='albums' AND `ownerid`='{$first}'";
$sql4 = "DELETE FROM " . prefix('obj_to_tag') . " WHERE `type`='albums' AND `objectid`='{$first}'";
foreach ($dead as $albumid) {
$sql1 .= " OR `id` = '{$albumid}'";
$sql2 .= " OR `albumid` = '{$albumid}'";
$sql3 .= " OR `ownerid` = '{$albumid}'";
$sql4 .= " OR `objectid` = '{$albumid}'";
}
$n = query($sql1);
if (!$complete && $n && $cascade) {
query($sql2);
query($sql3);
query($sql4);
}
}
if (count($deadalbumthemes) > 0) {
// delete the album theme options tables for dead albums
foreach ($deadalbumthemes as $id => $deadtable) {
$sql = 'DELETE FROM ' . prefix('options') . ' WHERE `ownerid`=' . $id;
query($sql, false);
}
}
}
if ($complete) {
if (empty($restart)) {
/* refresh 'metadata' albums */
$albumids = query_full_array("SELECT `id`, `mtime`, `folder`, `dynamic` FROM " . prefix('albums'));
foreach ($albumids as $analbum) {
if (($mtime = filemtime(ALBUM_FOLDER_SERVERPATH . internalToFilesystem($analbum['folder']))) > $analbum['mtime']) {
// refresh
$album = new Album($this, $analbum['folder']);
$album->set('mtime', $mtime);
if ($album->isDynamic()) {
$data = file_get_contents($album->localpath);
while (!empty($data)) {
$data1 = trim(substr($data, 0, $i = strpos($data, "\n")));
if ($i === false) {
$data1 = $data;
$data = '';
} else {
$data = substr($data, $i + 1);
}
if (strpos($data1, 'WORDS=') !== false) {
$words = "words=" . urlencode(substr($data1, 6));
}
if (strpos($data1, 'THUMB=') !== false) {
$thumb = trim(substr($data1, 6));
}
if (strpos($data1, 'FIELDS=') !== false) {
$fields = "&searchfields=" . trim(substr($data1, 7));
}
}
if (!empty($words)) {
if (empty($fields)) {
$fields = '&searchfields=tags';
}
}
$album->set('search_params', $words . $fields);
$album->set('thumb', $thumb);
}
$album->save();
zp_apply_filter('album_refresh', $album);
}
}
/* Delete all image entries that don't belong to an album at all. */
$albumids = query_full_array("SELECT `id` FROM " . prefix('albums'));
/* all the album IDs */
$idsofalbums = array();
foreach ($albumids as $row) {
$idsofalbums[] = $row['id'];
}
$imageAlbums = query_full_array("SELECT DISTINCT `albumid` FROM " . prefix('images'));
/* albumids of all the images */
$albumidsofimages = array();
foreach ($imageAlbums as $row) {
$albumidsofimages[] = $row['albumid'];
}
$orphans = array_diff($albumidsofimages, $idsofalbums);
/* albumids of images with no album */
if (count($orphans) > 0) {
/* delete dead images from the DB */
$firstrow = array_pop($orphans);
$sql = "DELETE FROM " . prefix('images') . " WHERE `albumid`='" . $firstrow . "'";
foreach ($orphans as $id) {
$sql .= " OR `albumid`='" . $id . "'";
}
query($sql);
// Then go into existing albums recursively to clean them... very invasive.
foreach ($this->getAlbums(0) as $folder) {
$album = new Album($this, $folder);
if (!$album->isDynamic()) {
if (is_null($album->getDateTime())) {
// see if we can get one from an image
$images = $album->getImages(0, 0, 'date', 'DESC');
if (count($images) > 0) {
$image = newImage($album, array_shift($images));
$album->setDateTime($image->getDateTime());
}
}
$album->garbageCollect(true);
$album->preLoad();
}
$album->save();
zp_apply_filter('album_refresh', $album);
}
}
}
/* Look for image records where the file no longer exists. While at it, check for images with IPTC data to update the DB */
$start = array_sum(explode(" ", microtime()));
// protect against too much processing.
if (!empty($restart)) {
$restartwhere = ' WHERE `id`>' . $restart . ' AND `mtime`=0';
} else {
$restartwhere = ' WHERE `mtime`=0';
}
define('RECORD_LIMIT', 5);
$sql = 'SELECT * FROM ' . prefix('images') . $restartwhere . ' ORDER BY `id` LIMIT ' . (RECORD_LIMIT + 2);
$images = query_full_array($sql);
if (count($images) > 0) {
$c = 0;
foreach ($images as $image) {
$sql = 'SELECT `folder` FROM ' . prefix('albums') . ' WHERE `id`="' . $image['albumid'] . '";';
$row = query_single_row($sql);
$imageName = internalToFilesystem(ALBUM_FOLDER_SERVERPATH . $row['folder'] . '/' . $image['filename']);
if (file_exists($imageName)) {
$mtime = filemtime($imageName);
if ($image['mtime'] != $mtime) {
// file has changed since we last saw it
$imageobj = newImage(new Album($this, $row['folder']), $image['filename']);
$imageobj->set('mtime', $mtime);
$imageobj->updateMetaData();
// prime the EXIF/IPTC fields
$imageobj->updateDimensions();
// update the width/height & account for rotation
$imageobj->save();
zp_apply_filter('image_refresh', $imageobj);
}
} else {
$sql = 'DELETE FROM ' . prefix('images') . ' WHERE `id`="' . $image['id'] . '";';
$result = query($sql);
$sql = 'DELETE FROM ' . prefix('comments') . ' WHERE `type` IN (' . zp_image_types('"') . ') AND `ownerid` ="' . $image['id'] . '";';
$result = query($sql);
}
if (++$c >= RECORD_LIMIT) {
return $image['id'];
// avoide excessive processing
}
}
}
}
return false;
}
/**
* Gets an array of comments for the current admin
*
* @param int $number how many comments desired
* @return array
*/
function fetchComments($number)
{
if ($number) {
$limit = " LIMIT {$number}";
} else {
$limit = '';
}
global $_zp_loggedin;
$comments = array();
if ($_zp_loggedin & ADMIN_RIGHTS) {
$sql = "SELECT `id`, `name`, `website`, `type`, `ownerid`," . " (date + 0) AS date, `comment`, `email`, `inmoderation`, `ip`, `private`, `anon` FROM " . prefix('comments') . " ORDER BY id DESC{$limit}";
$comments = query_full_array($sql);
} else {
if ($_zp_loggedin & COMMENT_RIGHTS) {
$albumlist = getManagedAlbumList();
$albumIDs = array();
foreach ($albumlist as $albumname) {
$subalbums = getAllSubAlbumIDs($albumname);
foreach ($subalbums as $ID) {
$albumIDs[] = $ID['id'];
}
}
if (count($albumIDs) > 0) {
$sql = "SELECT `id`, `name`, `website`, `type`, `ownerid`," . " (`date` + 0) AS date, `comment`, `email`, `inmoderation`, `ip` " . " FROM " . prefix('comments') . " WHERE ";
$sql .= " (`type`='albums' AND (";
$i = 0;
foreach ($albumIDs as $ID) {
if ($i > 0) {
$sql .= " OR ";
}
$sql .= "(" . prefix('comments') . ".ownerid={$ID})";
$i++;
}
$sql .= ")) ";
$sql .= " ORDER BY id DESC{$limit}";
$albumcomments = query_full_array($sql);
foreach ($albumcomments as $comment) {
$comments[$comment['id']] = $comment;
}
$sql = "SELECT ." . prefix('comments') . ".id as id, " . prefix('comments') . ".name as name, `website`, `type`, `ownerid`," . " (" . prefix('comments') . ".date + 0) AS date, `comment`, `email`, `inmoderation`, `ip`, " . prefix('images') . ".`albumid` as albumid" . " FROM " . prefix('comments') . "," . prefix('images') . " WHERE ";
$sql .= "(`type` IN (" . zp_image_types("'") . ") AND (";
$i = 0;
foreach ($albumIDs as $ID) {
if ($i > 0) {
$sql .= " OR ";
}
$sql .= "(" . prefix('comments') . ".ownerid=" . prefix('images') . ".id AND " . prefix('images') . ".albumid={$ID})";
$i++;
}
$sql .= "))";
$sql .= " ORDER BY id DESC{$limit}";
$imagecomments = query_full_array($sql);
foreach ($imagecomments as $comment) {
$comments[$comment['id']] = $comment;
}
krsort($comments);
if ($number) {
if ($number < count($comments)) {
$comments = array_slice($comments, 0, $number);
}
}
}
}
}
return $comments;
}
