PHP Db::execute方法代码示例

 public static function call(OkapiRequest $request)
 {
     # User is already verified (via OAuth), but we need to verify the
     # cache code (check if it exists). We will simply call a geocache method
     # on it - this will also throw a proper exception if it doesn't exist.
     $cache_code = $request->get_parameter('cache_code');
     if ($cache_code == null) {
         throw new ParamMissing('cache_code');
     }
     $geocache = OkapiServiceRunner::call('services/caches/geocache', new OkapiInternalRequest($request->consumer, $request->token, array('cache_code' => $cache_code, 'fields' => 'internal_id')));
     # watched
     if ($tmp = $request->get_parameter('watched')) {
         if (!in_array($tmp, array('true', 'false', 'unchanged'))) {
             throw new InvalidParam('watched', $tmp);
         }
         if ($tmp == 'true') {
             Db::execute("\n                    insert ignore into cache_watches (cache_id, user_id)\n                    values (\n                        '" . Db::escape_string($geocache['internal_id']) . "',\n                        '" . Db::escape_string($request->token->user_id) . "'\n                    );\n                ");
         } elseif ($tmp == 'false') {
             Db::execute("\n                    delete from cache_watches\n                    where\n                        cache_id = '" . Db::escape_string($geocache['internal_id']) . "'\n                        and user_id = '" . Db::escape_string($request->token->user_id) . "';\n                ");
         }
     }
     # ignored
     if ($tmp = $request->get_parameter('ignored')) {
         if (!in_array($tmp, array('true', 'false', 'unchanged'))) {
             throw new InvalidParam('ignored', $tmp);
         }
         if ($tmp == 'true') {
             Db::execute("\n                    insert ignore into cache_ignore (cache_id, user_id)\n                    values (\n                        '" . Db::escape_string($geocache['internal_id']) . "',\n                        '" . Db::escape_string($request->token->user_id) . "'\n                    );\n                ");
         } elseif ($tmp == 'false') {
             Db::execute("\n                    delete from cache_ignore\n                    where\n                        cache_id = '" . Db::escape_string($geocache['internal_id']) . "'\n                        and user_id = '" . Db::escape_string($request->token->user_id) . "'\n                ");
         }
     }
     $result = array('success' => true);
     return Okapi::formatted_response($request, $result);
 }

 /**
  * OCDE supports arbitrary ordering of log images. The pictures table
  * contains sequence numbers, which are always > 0 and need not to be
  * consecutive (may have gaps). There is a unique index which prevents
  * inserting duplicate seq numbers for the same log.
  *
  * OCPL sequence numbers currently are always = 1.
  *
  * The purpose of this function is to bring the supplied 'position'
  * parameter into bounds, and to calculate an appropriate sequence number
  * from it.
  *
  * This function is always called when adding images. When editing images,
  * it is called only for OCDE and if the position parameter was supplied.
  */
 static function prepare_position($log_internal_id, $position, $end_offset)
 {
     if (Settings::get('OC_BRANCH') == 'oc.de' && $position !== null) {
         # Prevent race conditions when creating sequence numbers if a
         # user tries to upload multiple images simultaneously. With a
         # few picture uploads per hour - most of them probably witout
         # a 'position' parameter - the lock is neglectable.
         Db::execute('lock tables pictures write');
     }
     $log_images_count = Db::select_value("\n            select count(*)\n            from pictures\n            where object_type = 1 and object_id = '" . Db::escape_string($log_internal_id) . "'\n        ");
     if (Settings::get('OC_BRANCH') == 'oc.pl') {
         # Ignore the position parameter, always insert at end.
         # Remember that this function is NOT called when editing OCPL images.
         $position = $log_images_count;
         $seq = 1;
     } else {
         if ($position === null || $position >= $log_images_count) {
             $position = $log_images_count - 1 + $end_offset;
             $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                ") + $end_offset;
         } else {
             if ($position <= 0) {
                 $position = 0;
                 $seq = 1;
             } else {
                 $seq = Db::select_value("\n                    select seq\n                    from pictures\n                    where object_type = 1 and object_id = '" . Db::escape_string($log_internal_id) . "'\n                    order by seq\n                    limit " . ($position + 0) . ", 1\n                ");
             }
         }
     }
     # $position may have become a string, as returned by database queries.
     return array($position + 0, $seq, $log_images_count);
 }

 static function call(OkapiRequest $request)
 {
     require_once 'log_images_common.inc.php';
     list($image_uuid, $log_internal_id) = LogImagesCommon::validate_image_uuid($request);
     $image_uuid_escaped = Db::escape_string($image_uuid);
     Db::execute('start transaction');
     $image_row = Db::select_row("\n            select id, node, url, local\n            from pictures\n            where uuid = '" . $image_uuid_escaped . "'\n        ");
     Db::execute("\n            delete from pictures where uuid = '" . $image_uuid_escaped . "'\n        ");
     # Remember that OCPL picture sequence numbers are always 1, and
     # OCDE sequence numbers may have gaps. So we do not need to adjust
     # any numbers after deleting from table 'pictures'.
     if (Settings::get('OC_BRANCH') == 'oc.de') {
         # OCDE does all the housekeeping by triggers
     } else {
         Db::execute("\n                INSERT INTO removed_objects (\n                    localID, uuid, type, removed_date, node\n                )\n                VALUES (\n                    " . $image_row['id'] . "\n                    '" . $image_uuid_escaped . "',\n                    6,\n                    NOW(),\n                    " . $image_row['node'] . "\n                )\n            ");
         # This will also update cache_logs.okapi_syncbase, so that replication
         # can output the updated log entry with one image less. For OCDE
         # that's done by DB trigges.
         Db::execute("\n                update cache_logs\n                set\n                    picturescount = greatest(0, picturescount - 1),\n                    last_modified = NOW()\n                where id = '" . Db::escape_string($log_internal_id) . "'\n            ");
     }
     Db::execute('commit');
     if ($image_row['local']) {
         $filename = basename($image_row['url']);
         unlink(Settings::get('IMAGES_DIR') . '/' . $filename);
     }
     $result = array('success' => true);
     return Okapi::formatted_response($request, $result);
 }

 public static function call()
 {
     # Determine which user is logged in to OC.
     require_once $GLOBALS['rootpath'] . "okapi/lib/oc_session.php";
     $OC_user_id = OCSession::get_user_id();
     # Ensure a user is logged in.
     if ($OC_user_id == null) {
         $after_login = "okapi/apps/";
         # it is correct, if you're wondering
         $login_url = Settings::get('SITE_URL') . "login.php?target=" . urlencode($after_login);
         return new OkapiRedirectResponse($login_url);
     }
     $consumer_key = isset($_REQUEST['consumer_key']) ? $_REQUEST['consumer_key'] : '';
     # Just remove app (if it doesn't exist - nothing wrong will happen anyway).
     Db::execute("\n            delete from okapi_tokens\n            where\n                user_id = '" . Db::escape_string($OC_user_id) . "'\n                and consumer_key = '" . Db::escape_string($consumer_key) . "'\n        ");
     Db::execute("\n            delete from okapi_authorizations\n            where\n                user_id = '" . Db::escape_string($OC_user_id) . "'\n                and consumer_key = '" . Db::escape_string($consumer_key) . "'\n        ");
     # Redirect back to the apps page.
     return new OkapiRedirectResponse(Settings::get('SITE_URL') . "okapi/apps/");
 }

 public function cleanup()
 {
     Db::execute("\n            delete from okapi_nonces\n            where\n                timestamp < unix_timestamp(date_add(now(), interval -6 minute))\n                or timestamp > unix_timestamp(date_add(now(), interval 6 minute))\n        ");
     Db::execute("\n            delete from okapi_tokens\n            where\n                token_type = 'request'\n                and timestamp < unix_timestamp(date_add(now(), interval -2 hour))\n        ");
 }

 private static function remove_notes($cache_id, $user_id)
 {
     if (Settings::get('OC_BRANCH') == 'oc.de') {
         # we can delete row if and only if there are no coords in it
         $affected_row_count = Db::execute("\n                delete from coordinates\n                where\n                    type = 2  -- personal note\n                    and cache_id = '" . Db::escape_string($cache_id) . "'\n                    and user_id = '" . Db::escape_string($user_id) . "'\n                    and longitude = 0\n                    and latitude = 0\n            ");
         if ($affected_row_count <= 0) {
             # no rows deleted - record either doesn't exist, or has coords
             # remove only description
             Db::execute("\n                    update coordinates\n                    set description = null\n                    where\n                        type = 2\n                        and cache_id = '" . Db::escape_string($cache_id) . "'\n                        and user_id = '" . Db::escape_string($user_id) . "'\n                ");
         }
     } else {
         # oc.pl branch
         Db::execute("\n                delete from cache_notes\n                where\n                    cache_id = '" . Db::escape_string($cache_id) . "'\n                    and user_id = '" . Db::escape_string($user_id) . "'\n            ");
     }
 }

 private static function ver93()
 {
     Db::execute("alter table okapi_tile_caches add column name_crc int(10) unsigned not null default 0;");
 }

 private static function ver94()
 {
     Db::execute("\n            CREATE TABLE okapi_stats_monthly (\n                consumer_key varchar(32) NOT NULL,\n                user_id int(10) NOT NULL,\n                period_start datetime NOT NULL,\n                service_name varchar(80) NOT NULL,\n                total_calls int(10) NOT NULL,\n                http_calls int(10) NOT NULL,\n                total_runtime float NOT NULL DEFAULT '0',\n                http_runtime float NOT NULL DEFAULT '0',\n                PRIMARY KEY (consumer_key,user_id,period_start,service_name)\n            ) ENGINE=MyISAM DEFAULT CHARSET=utf8;\n        ");
 }

 public function execute()
 {
     require_once $GLOBALS['rootpath'] . "okapi/services/replicate/replicate_common.inc.php";
     $max_revision = ReplicateCommon::get_revision();
     $cache_key = 'clog_revisions_daily';
     $data = Cache::get($cache_key);
     if ($data == null) {
         $data = array();
     }
     $data[time()] = $max_revision;
     $new_min_revision = 1;
     $new_data = array();
     foreach ($data as $time => $r) {
         if ($time < time() - 10 * 86400) {
             $new_min_revision = max($new_min_revision, $r);
         } else {
             $new_data[$time] = $r;
         }
     }
     Db::execute("\n            delete from okapi_clog\n            where id < '" . mysql_real_escape_string($new_min_revision) . "'\n        ");
     Cache::set($cache_key, $new_data, 10 * 86400);
     Db::query("optimize table okapi_clog");
 }

 private function loadSearchData($searchData)
 {
     \okapi\OkapiErrorHandler::reenable();
     // We need to transform OC's "searchdata" into OKAPI's "search set".
     // First, we need to determine if we ALREADY did that.
     // Note, that this is not exactly thread-efficient. Multiple threads may
     // do this transformation in the same time. However, this is done only once
     // for each searchdata, so we will ignore it.
     $cache_key = "OC_searchdata_" . $searchData;
     $set_id = \okapi\Cache::get($cache_key);
     if ($set_id === null) {
         // Read the searchdata file into a temporary table.
         $filepath = \okapi\Settings::get('VAR_DIR') . "/searchdata/" . $searchData;
         \okapi\Db::execute("\n            create temporary table temp_" . $searchData . " (\n                cache_id integer primary key\n            ) engine=memory\n        ");
         if (file_exists($filepath)) {
             \okapi\Db::execute("\n                        load data local infile '{$filepath}'\n                        into table temp_" . $searchData . "\n                fields terminated by ' '\n                lines terminated by '\\n'\n                (cache_id)\n            ");
         }
         // Tell OKAPI to import the table into its own internal structures.
         // Cache it for two hours.
         $set_info = \okapi\Facade::import_search_set("temp_" . $searchData, 7200, 7200);
         $set_id = $set_info['set_id'];
         \okapi\Cache::set($cache_key, $set_id, 7200);
     }
     $this->search_params['set_and'] = $set_id;
     $this->search_params['status'] = "Available|Temporarily unavailable|Archived";
     \okapi\OkapiErrorHandler::disable();
     return true;
 }

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

 private static function ver90()
 {
     Db::execute("alter table okapi_consumers change column admin bflags tinyint not null default 0;");
 }

 private static function ver88()
 {
     Db::execute("alter table okapi_consumers add column admin tinyint not null default 0;");
 }

 private static function ver95()
 {
     # See comments on ver7.
     Db::execute("\n            CREATE TABLE okapi_submitted_objects (\n                object_type tinyint(2) NOT NULL,\n                object_id int(11) NOT NULL,\n                consumer_key varchar(20) charset ascii collate ascii_bin NOT NULL,\n                PRIMARY KEY  (object_type, object_id),\n                KEY by_consumer (consumer_key, object_type)\n            ) ENGINE=MyISAM DEFAULT CHARSET=utf8\n            (\n                SELECT\n                    " . Okapi::OBJECT_TYPE_CACHE_LOG . " object_type,\n                    log_id object_id,\n                    consumer_key\n                FROM okapi_cache_logs\n            )\n        ");
     Db::execute("\n            DROP TABLE okapi_cache_logs\n        ");
 }

 /**
  * Remove all OAuth Access Tokens bound to a certain user. This method
  * should be called (once) e.g. after a user is banned. It will disable his
  * ability to submit cache logs, etc.
  *
  * Note, that OKAPI will *automatically* remove Access Tokens of banned
  * users, but it will perform this action with a slight delay. This method
  * can be used to do this immediatelly. See #432 for details.
  */
 public static function remove_user_tokens($user_id)
 {
     Db::execute("\n            delete from okapi_tokens\n            where user_id = '" . Db::escape_string($user_id) . "'\n        ");
 }