/** 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; }