Example #1
0
function restore_listings($cacheids, $rdate, $roptions, $simulate)
{
    global $opt, $login;
    sql("SET @restoredby='&1'", $login->userid);
    // is evaluated by trigger functions
    sql_slave("SET @restoredby='&1'", $login->userid);
    $restored = array();
    foreach ($cacheids as $cacheid) {
        $modified = false;
        // get current cache data
        $rs = sql("SELECT * FROM `caches` WHERE `cache_id`='&1'", $cacheid);
        $cache = sql_fetch_assoc($rs);
        sql_free_result($rs);
        $wp = $cache['wp_oc'];
        $user_id = $cache['user_id'];
        // coordinates
        if (in_array("coords", $roptions) && sql_value("SELECT `cache_id` FROM `cache_coordinates`\n                WHERE `cache_id`='&1' AND `date_created`>='&2'", 0, $cacheid, $rdate)) {
            $rs = sql("SELECT `latitude`, `longitude` FROM `cache_coordinates`\n                WHERE `cache_id`='&1' AND `date_created` < '&2'\n                ORDER BY `date_created` DESC\n                LIMIT 1", $cacheid, $rdate);
            if ($r = sql_fetch_assoc($rs)) {
                // should always be true ...
                if (!$simulate) {
                    sql("UPDATE `caches` SET `latitude`='&1', `longitude`='&2' WHERE `cache_id`='&3'", $r['latitude'], $r['longitude'], $cacheid);
                }
                $restored[$wp]['coords'] = true;
            }
            sql_free_result($rs);
        }
        // country
        if (in_array("coords", $roptions) && sql_value("SELECT `cache_id` FROM `cache_countries`\n                WHERE `cache_id`='&1' AND `date_created`>='&2'", 0, $cacheid, $rdate)) {
            $rs = sql("SELECT `country` FROM `cache_countries`\n                WHERE `cache_id`='&1' AND `date_created` < '&2'\n                ORDER BY `date_created` DESC\n                LIMIT 1", $cacheid, $rdate);
            if ($r = sql_fetch_assoc($rs)) {
                // should always be true ...
                if (!$simulate) {
                    sql("UPDATE `caches` SET `country`='&1'  WHERE `cache_id`='&2'", $r['country'], $cacheid);
                }
                $restored[$wp]['country'] = true;
            }
            sql_free_result($rs);
        }
        // other cache data
        $rs = sql("SELECT * FROM `caches_modified`\n            WHERE `cache_id`='&1' AND `date_modified` >='&2'\n            ORDER BY `date_modified` ASC\n            LIMIT 1", $cacheid, $rdate);
        $fields = ['name' => 'settings', 'type' => 'settings', 'size' => 'settings', 'date_hidden' => 'settings', 'difficulty' => 'settings', 'terrain' => 'settings', 'search_time' => 'settings', 'way_length' => 'settings', 'wp_gc' => 'waypoints', 'wp_nc' => 'waypoints'];
        if ($r = sql_fetch_assoc($rs)) {
            // can be false
            $setfields = "";
            foreach ($fields as $field => $ropt) {
                if (in_array($ropt, $roptions) && $r[$field] != $cache[$field]) {
                    if ($setfields != "") {
                        $setfields .= ",";
                    }
                    $setfields .= "`{$field}`='" . sql_escape($r[$field]) . "'";
                    $restored[$wp][$field] = true;
                }
            }
            if ($setfields != "" && !$simulate) {
                sql("UPDATE `caches` SET " . $setfields . " WHERE `cache_id`='&1'", $cacheid);
            }
        }
        sql_free_result($rs);
        // attributes
        if (in_array('settings', $roptions)) {
            $rs = sql("SELECT * FROM `caches_attributes_modified`\n                WHERE `cache_id`='&1' AND `date_modified`>='&2' AND `attrib_id` != 6 /* OConly */\n                ORDER BY `date_modified` DESC", $cacheid, $rdate);
            // revert all attribute changes in reverse order.
            // recording limit of one change per attribute, cache and day ensures that no exponentially
            // growing list of recording entries can emerge from multiple reverts.
            while ($r = sql_fetch_assoc($rs)) {
                if (!$simulate) {
                    if ($r['was_set']) {
                        sql("INSERT IGNORE INTO `caches_attributes` (`cache_id`,`attrib_id`)\n                            VALUES ('&1','&2')", $cacheid, $r['attrib_id']);
                    } else {
                        sql("DELETE FROM `caches_attributes` WHERE `cache_id`='&1' AND `attrib_id`='&2'", $cacheid, $r['attrib_id']);
                    }
                }
                $restored[$wp]['attributes'] = true;
            }
            sql_free_result($rs);
        }
        // descriptions
        if (in_array('desc', $roptions)) {
            $rs = sql("SELECT * FROM `cache_desc_modified`\n                WHERE `cache_id`='&1' AND `date_modified`>='&2'\n                ORDER BY `date_modified` DESC", $cacheid, $rdate);
            // revert all desc changes in reverse order.
            // recording limit of one change per language, cache and day ensures that no exponentially
            // growing list of recording entries can emerge from restore-reverts.
            while ($r = sql_fetch_assoc($rs)) {
                if (!$simulate) {
                    if ($r['desc'] === null) {
                        // was newly created -> delete
                        sql("DELETE FROM `cache_desc` WHERE `cache_id`='&1' AND `language`='&2'", $cacheid, $r['language']);
                    } else {
                        // id, uuid, date_created and last_modified are set automatically
                        sql("INSERT INTO `cache_desc`\n                            (`node`, `cache_id`, `language`, `desc`, `desc_html`, `desc_htmledit`, `hint`, `short_desc`)\n                            VALUES ('&1','&2','&3','&4','&5','&6','&7','&8')\n                            ON DUPLICATE KEY UPDATE\n                            `desc`='&4', `desc_html`='&5', `desc_htmledit`='&6', `hint`='&7', `short_desc`='&8'", $opt['logic']['node']['id'], $cacheid, $r['language'], $r['desc'], $r['desc_html'], $r['desc_htmledit'], $r['hint'], $r['short_desc']);
                    }
                }
                $restored[$wp]['description(s)'] = true;
            }
            sql_free_result($rs);
        }
        // logs
        // ... before pictures, so that restored logpics have a parent
        if (in_array('logs', $roptions)) {
            $rs = sql("\n                SELECT * FROM (\n                    SELECT\n                        `id`,\n                        -1 AS `node`,\n                        `date_modified`,\n                        `cache_id`,\n                        0 AS `user_id`,\n                        0 AS `type`,\n                        '0' AS `oc_team_comment`,\n                        '0' AS `date`,\n                        '' AS `text`,\n                        0 AS `text_html`,\n                        0 AS `text_htmledit`,\n                        0 AS `needs_maintenance`,\n                        0 AS `listing_outdated`,\n                        `original_id`\n                    FROM `cache_logs_restored`\n                    WHERE `cache_id`='&1' AND `date_modified` >= '&2'\n                    UNION\n                    SELECT\n                        `id`,\n                        `node`,\n                        `deletion_date`,\n                        `cache_id`,\n                        `user_id`,\n                        `type`,\n                        `oc_team_comment`,\n                        `date`,\n                        `text`,\n                        `text_html`,\n                        `text_htmledit`,\n                        `needs_maintenance`,\n                        `listing_outdated`,\n                        0 AS `original_id`\n                    FROM `cache_logs_archived`\n                    WHERE\n                        `cache_id`='&1'\n                        AND `deletion_date` >= '&2'\n                        AND `deleted_by`='&3'\n                        AND `user_id` != '&3'\n                ) `logs`\n                ORDER BY `date_modified` ASC", $cacheid, $rdate, $user_id);
            // We start with the oldest entry and will touch each log ony once:
            // After restoring its state, it is added to $logs_processed (by its last known id),
            // and all further operations on the same log are ignored. This prevents unnecessary
            // operations and flooding pictures_modified on restore-reverts.
            $logs_processed = array();
            while ($r = sql_fetch_assoc($rs)) {
                $error = "";
                $logs_restored = false;
                // the log's id may have changed by multiple delete-and-restores
                $revert_logid = get_current_logid($r['id']);
                if (!in_array($revert_logid, $logs_processed)) {
                    if ($r['node'] == -1) {
                        // if it was not already deleted by a later restore operation ...
                        if (sql_value("SELECT `id` FROM `cache_logs` WHERE `id`='&1'", 0, $revert_logid) != 0) {
                            if (!$simulate) {
                                sql("INSERT INTO `cache_logs_archived`\n                                    SELECT *, '0', '&2', '&3' FROM `cache_logs` WHERE `id`='&1'", $revert_logid, $user_id, $login->userid);
                                sql("DELETE FROM `cache_logs` WHERE `id`='&1'", $revert_logid);
                                // This triggers an okapi_syncbase update, if OKAPI is installed:
                                sql("UPDATE `cache_logs_archived` SET `deletion_date`=NOW() WHERE `id`='&1'", $revert_logid);
                            }
                            $logs_restored = true;
                        }
                        // if it was not already restored by a later restore operation ...
                    } elseif (sql_value("SELECT `id` FROM `cache_logs` WHERE `id`='&1'", 0, $revert_logid) == 0) {
                        // id, uuid, date_created and last_modified are set automatically;
                        // picture will be updated automatically on picture-restore
                        $log = new cachelog();
                        $log->setNode($r['node']);
                        // cachelog class currently does not initialize node field
                        $log->setCacheId($r['cache_id']);
                        $log->setUserId($r['user_id']);
                        $log->setType($r['type'], true);
                        $log->setOcTeamComment($r['oc_team_comment']);
                        $log->setDate($r['date']);
                        $log->setText($r['text']);
                        $log->setTextHtml($r['text_html']);
                        $log->setTextHtmlEdit($r['text_htmledit']);
                        $log->setNeedsMaintenance($r['needs_maintenance']);
                        $log->setListingOutdated($r['listing_outdated']);
                        $log->setOwnerNotified(1);
                        if ($simulate) {
                            $logs_restored = true;
                        } else {
                            if (!$log->save()) {
                                $error = "restore";
                            } else {
                                sql("INSERT IGNORE INTO `cache_logs_restored`\n                                      (`id`, `date_modified`, `cache_id`, `original_id`, `restored_by`)\n                                    VALUES ('&1', NOW(), '&2', '&3', '&4')", $log->getLogId(), $log->getCacheId(), $revert_logid, $login->userid);
                                sql("DELETE FROM `watches_logqueue` WHERE `log_id`='&1'", $log->getLogId());
                                // watches_logqueue entry was created by trigger
                                $logs_processed[] = $log->getLogId();
                                /* no longer needed after implementing picture deletion in removelog.php
                                
                                                                // log pic deleting is not completely implemented, orphan pictures are    [*p]
                                                                // left over when directly deleting the log. We try to recover them ...
                                                                sql("UPDATE `pictures` SET `object_id`='&1' WHERE `object_type`=1 AND `object_id`='&2'",
                                                                    $log->getLogId(), $revert_logid);
                                
                                                                // ... and then update the stats:
                                                                $log->updatePictureStat();
                                                                 */
                                $logs_restored = true;
                            }
                        }
                    }
                    // restore deleted
                    $logs_processed[] = $revert_logid;
                }
                // not already processed
                if ($error != "") {
                    $restored[$wp]['internal error - could not $error log ' + $r['id'] + "/" + $logid];
                }
                if ($logs_restored) {
                    $restored[$wp]['logs'] = true;
                }
            }
            // while (all relevant log records)
            sql_free_result($rs);
        }
        // if logs enabled per roptions
        // pictures
        if (in_array("desc", $roptions) || in_array("logs", $roptions)) {
            $rs = sql("SELECT * FROM `pictures_modified`\n                        WHERE ((`object_type`=2 AND '&2' AND `object_id`='&3') OR\n                                           (`object_type`=1 AND '&1'\n                                                  AND IFNULL((SELECT `user_id` FROM `cache_logs` WHERE `id`=`object_id`),(SELECT `user_id` FROM `cache_logs_archived` WHERE `id`=`object_id`)) != '&5'\n                                                  /* ^^ ignore changes of own log pics (shouldnt be in pictures_modified, anyway) */\n                                                  AND IFNULL((SELECT `cache_id` FROM `cache_logs` WHERE `id`=`object_id`),(SELECT `cache_id` FROM `cache_logs_archived` WHERE `id`=`object_id`)) = '&3'))\n                          AND `date_modified`>='&4'\n                                    ORDER BY `date_modified` ASC", in_array("logs", $roptions) ? 1 : 0, in_array("desc", $roptions) ? 1 : 0, $cacheid, $rdate, $user_id);
            // We start with the oldest entry and will touch each picture ony once:
            // After restoring its state, it is added to $pics_processed (by its last known id),
            // and all further operations on the same pic are ignored. This prevents unnecessary
            // operations and flooding the _modified table on restore-reverts.
            $pics_processed = array();
            while ($r = sql_fetch_assoc($rs)) {
                $pics_restored = false;
                // the picture id may have changed by multiple delete-and-restores
                $revert_picid = get_current_picid($r['id']);
                if (!in_array($revert_picid, $pics_processed)) {
                    // .. as may have its uuid-based url
                    $revert_url = sql_value("SELECT `url` FROM `pictures_modified` WHERE `id`='&1'", $r['url'], $revert_picid);
                    $error = "";
                    switch ($r['operation']) {
                        case 'I':
                            if (sql_value("SELECT `id` FROM `pictures` WHERE `id`='&1'", 0, $revert_picid) != 0) {
                                // if it was not already deleted by a later restore operation:
                                // delete added (cache) picture
                                $pic = new picture($revert_picid);
                                if ($simulate) {
                                    $pics_restored = true;
                                } else {
                                    if ($pic->delete(true)) {
                                        $pics_restored = true;
                                    } else {
                                        $error = "delete";
                                    }
                                }
                            }
                            break;
                        case 'U':
                            if (sql_value("SELECT `id` FROM `pictures` WHERE `id`='&1'", 0, $revert_picid) != 0) {
                                // if it was not deleted by a later restore operation:
                                // restore modified (cache) picture properties
                                $pic = new picture($revert_picid);
                                $pic->setTitle($r['title']);
                                $pic->setSpoiler($r['spoiler']);
                                $pic->setDisplay($r['display']);
                                // mappreview flag is not restored, because it seems unappropriate to
                                // advertise for the listing of a vandalizing owner
                                if ($simulate) {
                                    $pics_restored = true;
                                } else {
                                    if ($pic->save(true)) {
                                        $pics_restored = true;
                                    } else {
                                        $error = "update";
                                    }
                                }
                            }
                            break;
                        case 'D':
                            if (sql_value("SELECT `id` FROM `pictures` WHERE `id`='&1'", 0, $revert_picid) == 0) {
                                // if it was not already restored by a later restore operation:
                                // restore deleted picture
                                // id, uuid, date_created and last_modified are set automatically
                                // the referring log's id  may have changed by [multiple] delete-and-restore
                                if ($r['object_type'] == 1) {
                                    $r['object_id'] = get_current_logid($r['object_id']);
                                }
                                // id, uuid, node, date_created, date_modified are automatically set;
                                // url will be set on save;
                                // last_url_check and thumb_last_generated stay at defaults until checked;
                                // thumb_url will be set on thumb creation (old thumb was deleted)
                                $pic = new picture();
                                $pic->setTitle($r['title']);
                                $pic->setObjectId($r['object_id']);
                                $pic->setObjectType($r['object_type']);
                                $pic->setSpoiler($r['spoiler']);
                                $pic->setLocal(1);
                                $pic->setUnknownFormat($r['unknown_format']);
                                $pic->setDisplay($r['display']);
                                // mappreview flag is not restored, because it seems unappropriate to
                                // advertise for the listing of a vandalizing owner
                                if ($simulate) {
                                    $pics_restored = true;
                                } else {
                                    if ($pic->save(true, $revert_picid, $revert_url)) {
                                        $pics_restored = true;
                                        $pics_processed[] = $pic->getPictureId();
                                    } else {
                                        $error = "restore";
                                    }
                                }
                            }
                            break;
                    }
                    // switch
                    $pics_processed[] = $revert_picid;
                }
                // not already processed
                if ($error != "") {
                    $restored[$wp]['internal error - could not $error picture ' . $r['id'] + "/" + $picid] = true;
                }
                if ($pics_restored) {
                    $restored[$wp]['pictures'] = true;
                }
            }
            // while (all relevant pic records)
            sql_free_result($rs);
        }
        // if pics enabled per roptions
    }
    // foreach cache(id)
    sql("SET @restoredby=0");
    sql_slave("SET @restoredby=0");
    return $restored;
}