PHP Db::query方法代码示例

 public static function setUpBeforeClass()
 {
     $dbName = false;
     if (!empty($GLOBALS['PIWIK_BENCHMARK_DATABASE'])) {
         $dbName = $GLOBALS['PIWIK_BENCHMARK_DATABASE'];
     }
     // connect to database
     self::createTestConfig();
     self::connectWithoutDatabase();
     // create specified fixture (global var not set, use default no-data fixture (see end of this file))
     if (empty($GLOBALS['PIWIK_BENCHMARK_FIXTURE'])) {
         $fixtureName = 'Piwik_Test_Fixture_EmptyOneSite';
     } else {
         $fixtureName = 'Piwik_Test_Fixture_' . $GLOBALS['PIWIK_BENCHMARK_FIXTURE'];
     }
     self::$fixture = new $fixtureName();
     // figure out if the desired fixture has already been setup, and if not empty the database
     $installedFixture = false;
     try {
         if (isset(self::$fixture->tablesPrefix)) {
             Config::getInstance()->database['tables_prefix'] = self::$fixture->tablesPrefix;
         }
         Db::query("USE " . $dbName);
         $installedFixture = \Piwik\Option::get('benchmark_fixture_name');
     } catch (Exception $ex) {
         // ignore
     }
     $createEmptyDatabase = $fixtureName != $installedFixture;
     parent::_setUpBeforeClass($dbName, $createEmptyDatabase);
     // if we created an empty database, setup the fixture
     if ($createEmptyDatabase) {
         self::$fixture->setUp();
         \Piwik\Option::set('benchmark_fixture_name', $fixtureName);
     }
 }

 public static function setUpBeforeClass()
 {
     parent::setUpBeforeClass();
     // note: not sure why I have to manually install plugin
     \Piwik\Plugin\Manager::getInstance()->loadPlugin('CustomAlerts')->install();
     $result = Fixture::updateDatabase();
     if ($result === false) {
         throw new \Exception("Failed to update pre-2.0 database (nothing to update).");
     }
     // truncate log tables so old data won't be re-archived
     foreach (array('log_visit', 'log_link_visit_action', 'log_conversion', 'log_conversion_item') as $table) {
         Db::query("TRUNCATE TABLE " . Common::prefixTable($table));
     }
     // add two visits from same visitor on dec. 29
     $t = Fixture::getTracker(1, '2012-12-29 01:01:30', $defaultInit = true);
     $t->setUrl('http://site.com/index.htm');
     $t->setIp('136.5.3.2');
     Fixture::checkResponse($t->doTrackPageView('incredible title!'));
     $t->setForceVisitDateTime('2012-12-29 03:01:30');
     $t->setUrl('http://site.com/other/index.htm');
     $t->DEBUG_APPEND_URL = '&_idvc=2';
     // make sure visit is marked as returning
     Fixture::checkResponse($t->doTrackPageView('other incredible title!'));
     // launch archiving
     VisitFrequencyApi::getInstance()->get(1, 'year', '2012-12-29');
 }

 private function insertInto($table, $row)
 {
     $columns = implode(', ', array_keys($row));
     $columnsPlaceholders = Common::getSqlStringFieldsArray($row);
     $values = array_values($row);
     Db::query("INSERT INTO " . Common::prefixTable($table) . " ({$columns}) VALUES ({$columnsPlaceholders})", $values);
 }

 /**
  * Sets the language for the user
  *
  * @param string $login
  * @param string $languageCode
  * @return bool
  */
 public function setLanguageForUser($login, $languageCode)
 {
     $query = 'INSERT INTO ' . $this->table . ' (login, language) VALUES (?,?) ON DUPLICATE KEY UPDATE language=?';
     $bind = array($login, $languageCode, $languageCode);
     Db::query($query, $bind);
     return true;
 }

 public static function update($idvisitor, $name = false, $email = false, $phone = false, $comments = false)
 {
     if ($name == false && $email == false && $phone == false && $comments == false) {
         return false;
     }
     $buildQuery = "";
     $argSet = array();
     $argOnUpdate = array();
     $argSet[] = @Common::hex2bin($idvisitor);
     if ($name) {
         $argSet[] = $name;
         $argOnUpdate[] = $name;
         $buildQuery .= " name = ?,";
     }
     if ($email) {
         $argSet[] = $email;
         $argOnUpdate[] = $email;
         $buildQuery .= " email = ?,";
     }
     if ($phone) {
         $argSet[] = $phone;
         $argOnUpdate[] = $phone;
         $buildQuery .= " phone = ?,";
     }
     if ($comments) {
         $argSet[] = $comments;
         $argOnUpdate[] = $comments;
         $buildQuery .= " comments = ?,";
     }
     $buildQuery = trim(substr_replace($buildQuery, "", -1));
     $arguments = array_merge($argSet, $argOnUpdate);
     Db::query("INSERT INTO " . Common::prefixTable('chat_personnal_informations') . " SET idvisitor = ?, {$buildQuery} ON DUPLICATE KEY UPDATE {$buildQuery}", $arguments);
     return true;
 }

 /**
  * Sets whether the given user wants to use 12 hout clock
  *
  * @param string $login
  * @param string $use12HourClock
  * @return bool
  */
 public function set12HourClock($login, $use12HourClock)
 {
     $query = 'INSERT INTO ' . $this->table . ' (login, use_12_hour_clock) VALUES (?,?) ON DUPLICATE KEY UPDATE use_12_hour_clock=?';
     $bind = array($login, $use12HourClock, $use12HourClock);
     Db::query($query, $bind);
     return true;
 }

 protected function write(array $record)
 {
     $sql = sprintf('INSERT INTO %s (tag, timestamp, level, message) VALUES (?, ?, ?, ?)', Common::prefixTable('logger_message'));
     $queryLog = Db::isQueryLogEnabled();
     Db::enableQueryLog(false);
     Db::query($sql, array($record['extra']['class'], $record['datetime']->format('Y-m-d H:i:s'), $record['level_name'], trim($record['formatted'])));
     Db::enableQueryLog($queryLog);
 }

 public static function setLastSent($idsite, $idvisitor, $microtime)
 {
     foreach (ChatCommon::getUsersBySite($idsite) as $user) {
         $arguments = array($user['login'], @Common::hex2bin($idvisitor), $microtime, $microtime);
         Db::query("INSERT INTO " . Common::prefixTable('chat_history_admin') . " SET login = ?, idvisitor = ?, lastsent = ? ON DUPLICATE KEY UPDATE lastsent = ?", $arguments);
     }
     return true;
 }

 public function test_getSitesAndDatesOfRowsUsingDuplicates_ReturnsTheServerTimeAndIdSite_OfRowsUsingSpecifiedActionIds()
 {
     $row = array('idsite' => 3, 'idvisitor' => pack("H*", DuplicateActions::DUMMY_IDVISITOR), 'server_time' => '2012-02-13 00:00:00', 'idvisit' => 5, 'idorder' => 6, 'price' => 15, 'quantity' => 21, 'deleted' => 1, 'idaction_sku' => 3, 'idaction_name' => 3, 'idaction_category' => 12, 'idaction_category2' => 3, 'idaction_category3' => 3, 'idaction_category4' => 3, 'idaction_category5' => 3);
     Db::query("INSERT INTO " . Common::prefixTable('log_conversion_item') . " (" . implode(", ", array_keys($row)) . ") VALUES ('" . implode("', '", array_values($row)) . "')");
     $expectedResult = array(array('idsite' => 1, 'server_time' => '2012-02-01'), array('idsite' => 3, 'server_time' => '2012-02-13'));
     $actualResult = $this->duplicateActionRemover->getSitesAndDatesOfRowsUsingDuplicates('log_conversion_item', array(4, 6, 12));
     $this->assertEquals($expectedResult, $actualResult);
 }

 /**
  * Performs a batch insert into a specific table by iterating through the data
  *
  * NOTE: you should use tableInsertBatch() which will fallback to this function if LOAD DATA INFILE not available
  *
  * @param string $tableName PREFIXED table name! you must call Common::prefixTable() before passing the table name
  * @param array $fields array of unquoted field names
  * @param array $values array of data to be inserted
  * @param bool $ignoreWhenDuplicate Ignore new rows that contain unique key values that duplicate old rows
  */
 public static function tableInsertBatchIterate($tableName, $fields, $values, $ignoreWhenDuplicate = true)
 {
     $fieldList = '(' . join(',', $fields) . ')';
     $ignore = $ignoreWhenDuplicate ? 'IGNORE' : '';
     foreach ($values as $row) {
         $query = "INSERT {$ignore}\n\t\t\t\t\tINTO " . $tableName . "\n\t\t\t\t\t{$fieldList}\n\t\t\t\t\tVALUES (" . Common::getSqlStringFieldsArray($row) . ")";
         Db::query($query, $row);
     }
 }

 /**
  * Removes a list of actions from the log_action table by ID.
  *
  * @param int[] $idActions
  */
 public function delete($idActions)
 {
     foreach ($idActions as &$id) {
         $id = (int) $id;
     }
     $table = Common::prefixTable('log_action');
     $sql = "DELETE FROM {$table} WHERE idaction IN (" . implode(",", $idActions) . ")";
     Db::query($sql);
 }

 /**
  * Saves (persists) the current setting values in the database.
  */
 public function save()
 {
     $table = $this->getTableName();
     foreach ($this->toBeDeleted as $name => $delete) {
         if ($delete) {
             $sql = "DELETE FROM {$table} WHERE `idsite` = ? and `setting_name` = ?";
             $bind = array($this->idSite, $name);
             $this->db->query($sql, $bind);
         }
     }
     $this->toBeDeleted = array();
     foreach ($this->settingsValues as $name => $value) {
         $value = serialize($value);
         $sql = "INSERT INTO {$table} (`idsite`, `setting_name`, `setting_value`) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE `setting_value` = ?";
         $bind = array($this->idSite, $name, $value, $value);
         $this->db->query($sql, $bind);
     }
 }

 public static function setUpBeforeClass()
 {
     parent::setUpBeforeClass();
     // add duplicates for every action
     $table = Common::prefixTable('log_action');
     foreach (Db::fetchAll("SELECT * FROM {$table}") as $row) {
         $insertSql = "INSERT INTO {$table} (name, type, hash, url_prefix)\n                               VALUES (?, ?, CRC32(?), ?)";
         Db::query($insertSql, array($row['name'], $row['type'], $row['name'], $row['url_prefix']));
     }
 }

 protected static function deleteArchiveIds(Date $date, $idArchivesToDelete)
 {
     $batches = array_chunk($idArchivesToDelete, 1000);
     foreach ($batches as $idsToDelete) {
         $query = "DELETE FROM %s WHERE idarchive IN (" . implode(',', $idsToDelete) . ")";
         Db::query(sprintf($query, ArchiveTableCreator::getNumericTable($date)));
         try {
             Db::query(sprintf($query, ArchiveTableCreator::getBlobTable($date)));
         } catch (Exception $e) {
             // Individual blob tables could be missing
         }
     }
 }

 /**
  * @group Core
  */
 public function testGetOption()
 {
     // empty table, expect false (i.e., not found)
     $this->assertFalse(Option::get('anonymous_defaultReport'));
     // populate table, expect '1' (i.e., found)
     Db::query("INSERT INTO `" . Common::prefixTable('option') . "` VALUES ('anonymous_defaultReport', '1',true)");
     $this->assertSame('1', Option::get('anonymous_defaultReport'));
     // delete row (bypassing API), expect '1' (i.e., from cache)
     Db::query("DELETE FROM `" . Common::prefixTable('option') . "` WHERE option_name = ?", array('anonymous_defaultReport'));
     $this->assertSame('1', Option::get('anonymous_defaultReport'));
     // force cache reload, expect false (i.e., not found)
     Option::clearCache();
     $this->assertFalse(Option::get('anonymous_defaultReport'));
 }