PHP Db::select_column方法代码示例

 public static function call(OkapiRequest $request)
 {
     $cache_code = $request->get_parameter('cache_code');
     if (!$cache_code) {
         throw new ParamMissing('cache_code');
     }
     $fields = $request->get_parameter('fields');
     if (!$fields) {
         $fields = "uuid|date|user|type|comment";
     }
     $offset = $request->get_parameter('offset');
     if (!$offset) {
         $offset = "0";
     }
     if ((int) $offset != $offset || (int) $offset < 0) {
         throw new InvalidParam('offset', "Expecting non-negative integer.");
     }
     $limit = $request->get_parameter('limit');
     if (!$limit) {
         $limit = "none";
     }
     if ($limit == "none") {
         $limit = "999999999";
     }
     if ((int) $limit != $limit || (int) $limit < 0) {
         throw new InvalidParam('limit', "Expecting non-negative integer or 'none'.");
     }
     # Check if code exists and retrieve cache ID (this will throw
     # a proper exception on invalid code).
     $cache = OkapiServiceRunner::call('services/caches/geocache', new OkapiInternalRequest($request->consumer, null, array('cache_code' => $cache_code, 'fields' => 'internal_id')));
     # Cache exists. Getting the uuids of its logs.
     $log_uuids = Db::select_column("\n            select uuid\n            from cache_logs\n            where\n                cache_id = '" . Db::escape_string($cache['internal_id']) . "'\n                and " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "deleted = 0" : "true") . "\n            order by date desc\n            limit {$offset}, {$limit}\n        ");
     # Getting the logs themselves. Formatting as an ordered list.
     $internal_request = new OkapiInternalRequest($request->consumer, $request->token, array('log_uuids' => implode("|", $log_uuids), 'fields' => $fields));
     $internal_request->skip_limits = true;
     $logs = OkapiServiceRunner::call('services/logs/entries', $internal_request);
     $results = array();
     foreach ($log_uuids as $log_uuid) {
         $results[] = $logs[$log_uuid];
     }
     /* Handle OCPL's "access logs" feature. */
     if (Settings::get('OC_BRANCH') == 'oc.pl' && Settings::get('OCPL_ENABLE_GEOCACHE_ACCESS_LOGS') && count($log_uuids) > 0) {
         require_once $GLOBALS['rootpath'] . 'okapi/lib/ocpl_access_logs.php';
         \okapi\OCPLAccessLogs::log_geocache_access($request, $cache['internal_id']);
     }
     return Okapi::formatted_response($request, $results);
 }

 /**
  * Get the list of cache IDs which were found by given user.
  * Parameter needs to be *internal* user id, not uuid.
  */
 private static function get_found_cache_ids($internal_user_id)
 {
     return Db::select_column("\n            select cache_id\n            from cache_logs\n            where\n                user_id = '" . mysql_real_escape_string($internal_user_id) . "'\n                and type in (\n                    '" . mysql_real_escape_string(Okapi::logtypename2id("Found it")) . "',\n                    '" . mysql_real_escape_string(Okapi::logtypename2id("Attended")) . "'\n                )\n                and " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "deleted = 0" : "true") . "\n        ");
 }

 /**
  * Generate a new fulldump file and put it into the OKAPI cache table.
  * Return the cache key.
  */
 public static function generate_fulldump()
 {
     # First we will create temporary files, then compress them in the end.
     $revision = self::get_revision();
     $generated_at = date('c', time());
     $dir = Okapi::get_var_dir() . "/okapi-db-dump";
     $i = 1;
     $json_files = array();
     # Cleanup (from a previous, possibly unsuccessful, execution)
     shell_exec("rm -f {$dir}/*");
     shell_exec("rmdir {$dir}");
     shell_exec("mkdir {$dir}");
     shell_exec("chmod 777 {$dir}");
     # Geocaches
     $cache_codes = Db::select_column("select wp_oc from caches");
     $cache_code_groups = Okapi::make_groups($cache_codes, self::$chunk_size);
     unset($cache_codes);
     foreach ($cache_code_groups as $cache_codes) {
         $basename = "part" . str_pad($i, 5, "0", STR_PAD_LEFT);
         $json_files[] = $basename . ".json";
         $entries = self::generate_changelog_entries('services/caches/geocaches', 'geocache', 'cache_codes', 'code', $cache_codes, self::$logged_cache_fields, true, false);
         $filtered = array();
         foreach ($entries as $entry) {
             if ($entry['change_type'] == 'replace') {
                 $filtered[] = $entry;
             }
         }
         unset($entries);
         file_put_contents("{$dir}/{$basename}.json", json_encode($filtered));
         unset($filtered);
         $i++;
     }
     unset($cache_code_groups);
     # Log entries. We cannot load all the uuids at one time, this would take
     # too much memory. Hence the offset/limit loop.
     $offset = 0;
     while (true) {
         $log_uuids = Db::select_column("\n                select uuid\n                from cache_logs\n                where " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "deleted = 0" : "true") . "\n                order by uuid\n                limit {$offset}, 10000\n            ");
         if (count($log_uuids) == 0) {
             break;
         }
         $offset += 10000;
         $log_uuid_groups = Okapi::make_groups($log_uuids, 500);
         unset($log_uuids);
         foreach ($log_uuid_groups as $log_uuids) {
             $basename = "part" . str_pad($i, 5, "0", STR_PAD_LEFT);
             $json_files[] = $basename . ".json";
             $entries = self::generate_changelog_entries('services/logs/entries', 'log', 'log_uuids', 'uuid', $log_uuids, self::$logged_log_entry_fields, true, false);
             $filtered = array();
             foreach ($entries as $entry) {
                 if ($entry['change_type'] == 'replace') {
                     $filtered[] = $entry;
                 }
             }
             unset($entries);
             file_put_contents("{$dir}/{$basename}.json", json_encode($filtered));
             unset($filtered);
             $i++;
         }
     }
     # Package data.
     $metadata = array('revision' => $revision, 'data_files' => $json_files, 'meta' => array('site_name' => Okapi::get_normalized_site_name(), 'okapi_version_number' => Okapi::$version_number, 'okapi_revision' => Okapi::$version_number, 'okapi_git_revision' => Okapi::$git_revision, 'generated_at' => $generated_at));
     file_put_contents("{$dir}/index.json", json_encode($metadata));
     # Compute uncompressed size.
     $size = filesize("{$dir}/index.json");
     foreach ($json_files as $filename) {
         $size += filesize("{$dir}/{$filename}");
     }
     # Create JSON archive. We use tar options: -j for bzip2, -z for gzip
     # (bzip2 is MUCH slower).
     $use_bzip2 = true;
     $dumpfilename = "okapi-dump.tar." . ($use_bzip2 ? "bz2" : "gz");
     shell_exec("tar --directory {$dir} -c" . ($use_bzip2 ? "j" : "z") . "f {$dir}/{$dumpfilename} index.json " . implode(" ", $json_files) . " 2>&1");
     # Delete temporary files.
     shell_exec("rm -f {$dir}/*.json");
     # Move the archive one directory upwards, replacing the previous one.
     # Remove the temporary directory.
     shell_exec("mv -f {$dir}/{$dumpfilename} " . Okapi::get_var_dir());
     shell_exec("rmdir {$dir}");
     # Update the database info.
     $metadata['meta']['filepath'] = Okapi::get_var_dir() . '/' . $dumpfilename;
     $metadata['meta']['content_type'] = $use_bzip2 ? "application/octet-stream" : "application/x-gzip";
     $metadata['meta']['public_filename'] = 'okapi-dump-r' . $metadata['revision'] . '.tar.' . ($use_bzip2 ? "bz2" : "gz");
     $metadata['meta']['uncompressed_size'] = $size;
     $metadata['meta']['compressed_size'] = filesize($metadata['meta']['filepath']);
     Cache::set("last_fulldump", $metadata, 10 * 86400);
 }

//.........这里部分代码省略.........
                 case 'internal_id':
                     $entry['internal_id'] = $row['cache_id'];
                     break;
                 case 'attribution_note':
                     /* handled separately */
                     break;
                 case 'protection_areas':
                     /* handled separately */
                     break;
                 default:
                     throw new Exception("Missing field case: " . $field);
             }
         }
         $results[$row['wp_oc']] = $entry;
     }
     mysql_free_result($rs);
     # owner
     if (in_array('owner', $fields) && count($results) > 0) {
         $rs = Db::query("\n                select user_id, uuid, username\n                from user\n                where user_id in ('" . implode("','", array_map('mysql_real_escape_string', array_values($owner_ids))) . "')\n            ");
         $tmp = array();
         while ($row = mysql_fetch_assoc($rs)) {
             $tmp[$row['user_id']] = $row;
         }
         foreach ($results as $cache_code => &$result_ref) {
             $row = $tmp[$owner_ids[$cache_code]];
             $result_ref['owner'] = array('uuid' => $row['uuid'], 'username' => $row['username'], 'profile_url' => Settings::get('SITE_URL') . "viewprofile.php?userid=" . $row['user_id']);
         }
     }
     # is_found
     if (in_array('is_found', $fields)) {
         if ($user_id == null) {
             throw new BadRequest("Either 'user_uuid' parameter OR Level 3 Authentication is required to access 'is_found' field.");
         }
         $tmp = Db::select_column("\n                select c.wp_oc\n                from\n                    caches c,\n                    cache_logs cl\n                where\n                    c.cache_id = cl.cache_id\n                    and cl.type in (\n                        '" . mysql_real_escape_string(Okapi::logtypename2id("Found it")) . "',\n                        '" . mysql_real_escape_string(Okapi::logtypename2id("Attended")) . "'\n                    )\n                    and cl.user_id = '" . mysql_real_escape_string($user_id) . "'\n                    " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "and cl.deleted = 0" : "") . "\n            ");
         $tmp2 = array();
         foreach ($tmp as $cache_code) {
             $tmp2[$cache_code] = true;
         }
         foreach ($results as $cache_code => &$result_ref) {
             $result_ref['is_found'] = isset($tmp2[$cache_code]);
         }
     }
     # is_not_found
     if (in_array('is_not_found', $fields)) {
         if ($user_id == null) {
             throw new BadRequest("Either 'user_uuid' parameter OR Level 3 Authentication is required to access 'is_not_found' field.");
         }
         $tmp = Db::select_column("\n                select c.wp_oc\n                from\n                    caches c,\n                    cache_logs cl\n                where\n                    c.cache_id = cl.cache_id\n                    and cl.type = '" . mysql_real_escape_string(Okapi::logtypename2id("Didn't find it")) . "'\n                    and cl.user_id = '" . mysql_real_escape_string($user_id) . "'\n                    " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "and cl.deleted = 0" : "") . "\n            ");
         $tmp2 = array();
         foreach ($tmp as $cache_code) {
             $tmp2[$cache_code] = true;
         }
         foreach ($results as $cache_code => &$result_ref) {
             $result_ref['is_not_found'] = isset($tmp2[$cache_code]);
         }
     }
     # is_watched
     if (in_array('is_watched', $fields)) {
         if ($request->token == null) {
             throw new BadRequest("Level 3 Authentication is required to access 'is_watched' field.");
         }
         $tmp = Db::select_column("\n                select c.wp_oc\n                from\n                    caches c,\n                    cache_watches cw\n                where\n                    c.cache_id = cw.cache_id\n                    and cw.user_id = '" . mysql_real_escape_string($request->token->user_id) . "'\n            ");
         $tmp2 = array();
         foreach ($tmp as $cache_code) {
             $tmp2[$cache_code] = true;
         }

 /**
  * Get the list of cache IDs which were found by given user.
  * Parameter needs to be *internal* user id, not uuid.
  */
 private static function get_found_cache_ids($internal_user_ids)
 {
     return Db::select_column("\n            select cache_id\n            from cache_logs\n            where\n                user_id in ('" . implode("','", array_map('\\okapi\\Db::escape_string', $internal_user_ids)) . "')\n                and type in (\n                    '" . Db::escape_string(Okapi::logtypename2id("Found it")) . "',\n                    '" . Db::escape_string(Okapi::logtypename2id("Attended")) . "'\n                )\n                and " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "deleted = 0" : "true") . "\n        ");
 }

 /**
  * Edit an log entry image and return its (new) position.
  * Throws CannotPublishException or BadRequest on errors.
  */
 private static function _call(OkapiRequest $request)
 {
     # Developers! Please notice the fundamental difference between throwing
     # CannotPublishException and the "standard" BadRequest/InvalidParam
     # exceptions. CannotPublishException will be caught by the service's
     # call() function and returns a message to be displayed to the user.
     require_once 'log_images_common.inc.php';
     # validate the 'image_uuid' parameter
     list($image_uuid, $log_internal_id) = LogImagesCommon::validate_image_uuid($request);
     # validate the 'caption', 'is_spoiler' and 'position' parameters
     $caption = $request->get_parameter('caption');
     if ($caption !== null && $caption == '') {
         throw new CannotPublishException(sprintf(_("Please enter an image caption."), Okapi::get_normalized_site_name()));
     }
     $is_spoiler = $request->get_parameter('is_spoiler');
     if ($is_spoiler !== null) {
         if (!in_array($is_spoiler, array('true', 'false'))) {
             throw new InvalidParam('is_spoiler');
         }
     }
     $position = LogImagesCommon::validate_position($request);
     if ($caption === null && $is_spoiler === null && $position === null) {
         # If no-params were allowed, what would be the success message?
         # It's more reasonable to assume that this was a developer's error.
         throw new BadRequest("At least one of the parameters 'caption', 'is_spoiler' and 'position' must be supplied");
     }
     $image_uuid_escaped = Db::escape_string($image_uuid);
     $log_entry_modified = false;
     # update caption
     if ($caption !== null) {
         Db::execute("\n                update pictures\n                set title = '" . Db::escape_string($caption) . "'\n                where uuid = '" . $image_uuid_escaped . "'\n            ");
         $log_entry_modified = true;
     }
     # update spoiler flag
     if ($is_spoiler !== null) {
         Db::execute("\n                update pictures\n                set spoiler = " . ($is_spoiler == 'true' ? 1 : 0) . "\n                where uuid = '" . $image_uuid_escaped . "'\n            ");
         $log_entry_modified = true;
     }
     # update position
     if ($position !== null) {
         if (Settings::get('OC_BRANCH') == 'oc.pl') {
             # OCPL as no arbitrary log picture ordering => ignore position parameter
             # and return the picture's current position.
             $image_uuids = Db::select_column("\n                    select uuid from pictures\n                    where object_type = 1 and object_id = '" . Db::escape_string($log_internal_id) . "'\n                    order by date_created\n                ");
             $position = array_search($image_uuid, $image_uuids);
         } else {
             list($position, $seq) = LogImagesCommon::prepare_position($log_internal_id, $position, 0);
             # For OCDE the pictures table is write locked now.
             $old_seq = DB::select_value("\n                    select seq from pictures where uuid = '" . $image_uuid_escaped . "'\n                ");
             if ($seq != $old_seq) {
                 # First move the edited picture to the end, to make space for rotating.
                 # Remember that we have no transactions at OC.de. If something goes wrong,
                 # the image will stay at the end of the list.
                 $max_seq = Db::select_value("\n                        select max(seq)\n                        from pictures\n                        where object_type = 1 and object_id = '" . Db::escape_string($log_internal_id) . "'\n                    ");
                 Db::query("\n                        update pictures\n                        set seq = '" . Db::escape_string($max_seq + 1) . "'\n                        where uuid = '" . $image_uuid_escaped . "'\n                    ");
                 # now move the pictures inbetween
                 if ($seq < $old_seq) {
                     Db::execute("\n                            update pictures\n                            set seq = seq + 1\n                            where\n                                object_type = 1\n                                and object_id = '" . Db::escape_string($log_internal_id) . "'\n                                and seq >= '" . Db::escape_string($seq) . "'\n                                and seq < '" . Db::escape_string($old_seq) . "'\n                            order by seq desc\n                        ");
                 } else {
                     Db::execute("\n                            update pictures\n                            set seq = seq - 1\n                            where\n                                object_type = 1\n                                and object_id = '" . Db::escape_string($log_internal_id) . "'\n                                and seq <= '" . Db::escape_string($seq) . "'\n                                and seq > '" . Db::escape_string($old_seq) . "'\n                            order by seq asc\n                        ");
                 }
                 # and finally move the edited picture into place
                 Db::query("\n                        update pictures\n                        set seq = '" . Db::escape_string($seq) . "'\n                        where uuid = '" . $image_uuid_escaped . "'\n                    ");
             }
             Db::execute('unlock tables');
             $log_entry_modified = true;
         }
     }
     if (Settings::get('OC_BRANCH') == 'oc.pl' && $log_entry_modified) {
         # OCDE touches the log entry via trigger, OCPL needs an explicit update.
         # This will also update okapi_syncbase.
         Db::query("\n                update cache_logs\n                set last_modified = NOW()\n                where id = '" . Db::escape_string($log_internal_id) . "'\n            ");
         # OCPL code currently does not update pictures.last_modified when
         # editing, but that is a bug, see
         # https://github.com/opencaching/opencaching-pl/issues/341.
     }
     return $position;
 }

 /**
  * Return the list of email addresses of developers who used any of the given
  * method names at least once. If $days is not null, then only consumers which
  * used the method in last X $days will be returned.
  */
 public static function get_consumers_of($service_names, $days = null)
 {
     return Db::select_column("\n            select distinct c.email\n            from\n                okapi_consumers c,\n                okapi_stats_hourly sh\n            where\n                sh.consumer_key = c.`key`\n                and sh.service_name in ('" . implode("','", array_map('mysql_real_escape_string', $service_names)) . "')\n                " . ($days != null ? "and sh.period_start > date_add(now(), interval '" . mysql_real_escape_string(-$days) . "' day)" : "") . "\n        ");
 }

//.........这里部分代码省略.........
             # NOTICE: We are including EXTERNAL OCDE library here! This
             # code does not belong to OKAPI!
             $opt['rootpath'] = $GLOBALS['rootpath'];
             $opt['html_purifier'] = Settings::get('OCDE_HTML_PURIFIER_SETTINGS');
             require_once $GLOBALS['rootpath'] . 'lib2/OcHTMLPurifier.class.php';
             $purifier = new \OcHTMLPurifier($opt);
             $formatted_comment = $purifier->purify($formatted_comment);
         } else {
             # TODO: Add OCPL HTML filtering.
             # See https://github.com/opencaching/okapi/issues/412.
         }
         $value_for_text_html_field = 1;
     }
     if (Settings::get('OC_BRANCH') == 'oc.pl') {
         # The HTML processing in OCPL code is broken. Effectively, it
         # will decode < > and & (and maybe other things?)
         # before display so that text contents may be interpreted as HTML.
         # We work around this by applying a double-encoding for & < >:
         $formatted_comment = str_replace("&amp;", "&amp;#38;", $formatted_comment);
         $formatted_comment = str_replace("&lt;", "&amp;#60;", $formatted_comment);
         $formatted_comment = str_replace("&gt;", "&amp;#62;", $formatted_comment);
         # Note: This problem also exists when submitting logs on OCPL websites.
         # If you e.g. enter "<text>" in the editor, it will get lost.
         # See https://github.com/opencaching/opencaching-pl/issues/469.
     }
     unset($comment);
     # Prevent bug #367. Start the transaction and lock all the rows of this
     # (user, cache) pair. In theory, we want to lock even smaller number of
     # rows here (user, cache, type=1), but this wouldn't work, because there's
     # no index for this.
     #
     # http://stackoverflow.com/questions/17068686/
     Db::execute("start transaction");
     Db::select_column("\n            select 1\n            from cache_logs\n            where\n                user_id = '" . Db::escape_string($request->token->user_id) . "'\n                and cache_id = '" . Db::escape_string($cache['internal_id']) . "'\n            for update\n        ");
     # Duplicate detection.
     if ($on_duplicate != 'continue') {
         # Attempt to find a log entry made by the same user, for the same cache, with
         # the same date, type, comment, etc. Note, that these are not ALL the fields
         # we could check, but should work ok in most cases. Also note, that we
         # DO NOT guarantee that duplicate detection will succeed. If it doesn't,
         # nothing bad happens (user will just post two similar log entries).
         # Keep this simple!
         $duplicate_uuid = Db::select_value("\n                select uuid\n                from cache_logs\n                where\n                    user_id = '" . Db::escape_string($request->token->user_id) . "'\n                    and cache_id = '" . Db::escape_string($cache['internal_id']) . "'\n                    and type = '" . Db::escape_string(Okapi::logtypename2id($logtype)) . "'\n                    and date = from_unixtime('" . Db::escape_string($when) . "')\n                    and text = '" . Db::escape_string($formatted_comment) . "'\n                    " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "and deleted = 0" : "") . "\n                limit 1\n            ");
         if ($duplicate_uuid != null) {
             if ($on_duplicate == 'silent_success') {
                 # Act as if the log has been submitted successfully.
                 return $duplicate_uuid;
             } elseif ($on_duplicate == 'user_error') {
                 throw new CannotPublishException(_("You have already submitted a log entry with exactly " . "the same contents."));
             }
         }
     }
     # Check if already found it (and make sure the user is not the owner).
     #
     # OCPL forbids logging 'Found it' or "Didn't find" for an already found cache,
     # while OCDE allows all kinds of duplicate logs.
     if (Settings::get('OC_BRANCH') == 'oc.pl' && ($logtype == 'Found it' || $logtype == "Didn't find it")) {
         $has_already_found_it = Db::select_value("\n                select 1\n                from cache_logs\n                where\n                    user_id = '" . Db::escape_string($user['internal_id']) . "'\n                    and cache_id = '" . Db::escape_string($cache['internal_id']) . "'\n                    and type = '" . Db::escape_string(Okapi::logtypename2id("Found it")) . "'\n                    and " . (Settings::get('OC_BRANCH') == 'oc.pl' ? "deleted = 0" : "true") . "\n            ");
         if ($has_already_found_it) {
             throw new CannotPublishException(_("You have already submitted a \"Found it\" log entry once. " . "Now you may submit \"Comments\" only!"));
         }
         if ($user['uuid'] == $cache['owner']['uuid']) {
             throw new CannotPublishException(_("You are the owner of this cache. You may submit " . "\"Comments\" only!"));
         }
     }
     # Check if the user has already rated the cache. BTW: I don't get this one.