PHP Db::exec方法代码示例

 private function _initSessionTable()
 {
     $sql = 'CREATE TABLE `' . $this->_tableName . '` (
        `session_id` varchar(100) NOT NULL default "",
        `session_data` text NOT NULL,
        `expires` int(11) NOT NULL default "0",
         PRIMARY KEY  (`session_id`)
         ) ENGINE = MyIsam DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;';
     return Db::exec($sql, $this->_db) == 1;
 }

    protected static function write($msg)
    {
        $config = self::config();
        if (!isset($config['db']['log'])) {
            $config['db']['log'] = array('type' => 'sqlite', 'file' => 'alm.log.sqlite');
            self::config('set', $config);
        }
        $sql = 'CREATE TABLE IF NOT EXISTS "log" (
			"id"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
			"date"  TEXT NOT NULL,
			"alert"  TEXT
		);';
        Db::query($sql, 1, 'log');
        $sql = 'INSERT INTO "log" ("id", "date", "alert") VALUES (null, :date, :alert);';
        $var = array(':date' => date("Y-m-d H:i:s"), ':alert' => $msg);
        return Db::exec($sql, $var, 0, 'log');
    }

 /** ajout d'un utilisateur à la base et auto login
  * @param $pseudo String pseudo unique
  * @param $pass String pass non encodé
  * @return string
  */
 public static function add_user($pseudo, $pass)
 {
     if (!self::exists_in_database('pseudo', $pseudo, 'users')) {
         $sql = "INSERT INTO users (pseudo, pass, last_refresh) VALUES (:pseudo, :pass, NOW())";
         $req = Db::prepare($sql);
         $req->bindValue(':pseudo', trim($pseudo), PDO::PARAM_STR);
         $req->bindValue(':pass', sha1(trim($pass)), PDO::PARAM_STR);
         $req->execute();
         $user_id = Db::getLastInsertId();
         // on crée la ligne des modifiers en fonction
         Db::exec("INSERT INTO modifiers SET user_id = {$user_id}");
         $_SESSION['user'] = ['pseudo' => htmlentities($pseudo), 'id' => $user_id];
         $url = 'Location:' . _ROOT_ . 'empire';
         header("{$url}");
         die;
     }
     return "un utilisateur porte déjà ce nom";
 }

 public function delete()
 {
     $sql = "DELETE FROM {$this->table} WHERE {$this->primary} = {$this->id}";
     Db::exec($sql);
 }

 /**
  * test_ExecAndTransaction_ExecWithInTrans()
  *
  * exec()のテスト（トランザクションあり）
  */
 public function test_ExecAndTransaction_ExecWithInTrans()
 {
     $params = ["driver" => $GLOBALS['DB_DRIVER'], "user" => $GLOBALS['DB_USER'], "pass" => $GLOBALS['DB_PASSWORD'], "dbname" => $GLOBALS['DB_DBNAME'], "host" => $GLOBALS['DB_HOST'], "persistent" => false];
     $instance = new Db();
     $instance->connect($params);
     // commit pattern
     $this->assertTrue($instance->beginTransaction());
     $this->assertTrue($instance->inTransaction());
     $this->assertEquals(1, $instance->exec('INSERT INTO risoluto_db_test(id, column1, column2) values ("10", "TEST_A", "TEST_B");'));
     $this->assertEquals(2, $this->getconnection()->getRowCount('risoluto_db_test'));
     $this->assertEquals(10, $instance->lastInsertId());
     $this->assertEquals(10, $instance->lastInsertId('id'));
     $this->assertTrue($instance->commit());
     $this->assertFalse($instance->inTransaction());
     $this->assertEquals(3, $this->getconnection()->getRowCount('risoluto_db_test'));
     // Rollback pattern
     $before_val = $this->getconnection()->createQueryTable('risoluto_db_test', 'SELECT id, column1, column2 FROM risoluto_db_test WHERE id="10";');
     $this->assertTrue($instance->beginTransaction());
     $this->assertTrue($instance->inTransaction());
     $this->assertEquals(1, $instance->exec('UPDATE risoluto_db_test SET column1="TEST_C", column2="TEST_C" WHERE id="10";'));
     $this->assertEquals(3, $this->getconnection()->getRowCount('risoluto_db_test'));
     $this->assertEquals(1, $instance->exec('DELETE FROM risoluto_db_test WHERE id="10";'));
     $this->assertEquals(3, $this->getconnection()->getRowCount('risoluto_db_test'));
     $this->assertTrue($instance->rollBack());
     $this->assertFalse($instance->inTransaction());
     $after_val = $this->getconnection()->createQueryTable('risoluto_db_test', 'SELECT id, column1, column2 FROM risoluto_db_test WHERE id="10";');
     $this->assertEquals(3, $this->getconnection()->getRowCount('risoluto_db_test'));
     $this->assertTablesEqual($before_val, $after_val);
     // Cleaning
     $this->assertEquals(1, $instance->exec('DELETE FROM risoluto_db_test WHERE id="10";'));
     $this->assertEquals(2, $this->getconnection()->getRowCount('risoluto_db_test'));
     $instance->disConnect();
     unset($instance);
 }

 public function delete()
 {
     $sql = 'DELETE FROM ' . static::$table . ' WHERE ' . static::$idFieldName . '=:id';
     $db = new Db();
     $idFieldName = static::$idFieldName;
     $db->exec($sql, [':id' => $this->{$idFieldName}]);
 }

 /**
  * Executes a database update query.
  *
  * @param string $updateSql Update SQL query.
  * @param int|false $errorToIgnore A MySQL error code to ignore.
  * @param string $file The Update file that's calling this method.
  */
 public static function executeMigrationQuery($updateSql, $errorToIgnore, $file)
 {
     try {
         Db::exec($updateSql);
     } catch (\Exception $e) {
         self::handleQueryError($e, $updateSql, $errorToIgnore, $file);
     }
 }

 /**
  * Perform cleanup of all items in arrays.
  */
 private function doClean()
 {
     $this->numnuked += count($this->cleanup['nuke']);
     $this->nummiscd += count($this->cleanup['misc']);
     if (!$this->echoonly) {
         $releases = new Releases();
         $db = new Db();
         foreach (array_keys($this->cleanup['nuke']) as $id) {
             $releases->delete($id);
         }
         if (count($this->cleanup['misc'])) {
             $sql = 'update releases set categoryID = ' . Category::CAT_MISC_OTHER . ' where categoryID != ' . Category::CAT_MISC_OTHER . ' and id in (' . implode(array_keys($this->cleanup['misc']), ',') . ')';
             $db->exec($sql);
         }
     }
     $this->cleanup = array('nuke' => array(), 'misc' => array());
 }

 public static function delete($table, $condition = array())
 {
     $sql = "delete from `" . $table . "`";
     if (count($condition)) {
         $where = array();
         foreach ($condition as $key => $value) {
             $where[] = '`' . $key . '` = ?';
             $args[] = $value;
         }
         $sql .= " where " . implode(' and ', $where);
     }
     return Db::exec($sql, $args);
 }

 /**
  * Performs database update(s)
  *
  * @param string $file Update script filename
  * @param array $sqlarray An array of SQL queries to be executed
  * @throws UpdaterErrorException
  */
 static function updateDatabase($file, $sqlarray)
 {
     foreach ($sqlarray as $update => $ignoreError) {
         try {
             Db::exec($update);
         } catch (\Exception $e) {
             if ($ignoreError === false || !Db::get()->isErrNo($e, $ignoreError)) {
                 $message = $file . ":\nError trying to execute the query '" . $update . "'.\nThe error was: " . $e->getMessage();
                 throw new UpdaterErrorException($message);
             }
         }
     }
 }

         if (!file_exists($nogrouppath)) {
             mkdir($nogrouppath);
         }
         if (!file_exists($nogrouppath . basename($nzbFile))) {
             rename($nzbFile, $nogrouppath . basename($nzbFile));
         }
         $missinggroups = array_merge($missinggroups, $nzbInfo->groups);
     }
     continue;
 } else {
     if ($usefilename) {
         $relguid = md5(uniqid());
         $name = $releases->cleanReleaseName(str_replace(".nzb", "", basename($nzbFile)));
         $catId = $cat->determineCategory($groupName, $name);
         $relid = $releases->insertRelease($name, $nzbInfo->filecount, $groupID, $relguid, $catId, "", date("Y-m-d H:i:s", $nzbInfo->postedlast), $nzbInfo->poster, "", $page->site);
         $db->exec(sprintf("update releases set totalpart = %d, size = %s, completion = %d, GID=%s where ID = %d", $nzbInfo->filecount, $nzbInfo->filesize, $nzbInfo->completion, $db->escapeString($nzbInfo->gid), $relid));
         $nzbfilename = $nzb->getNZBPath($relguid, $page->site->nzbpath, true);
         $fp = _gzopen($nzbfilename, "w");
         if ($fp) {
             gzwrite($fp, $nzbInfo->toNzb());
             gzclose($fp);
             echo sprintf("%0" . $digits . "d %.2f%% Imported %s\n", $items - $num, $num / $items * 100, $name);
             if ($movefiles) {
                 if (!file_exists($importedpath)) {
                     mkdir($importedpath);
                 }
                 if (!file_exists($importedpath . basename($nzbFile))) {
                     rename($nzbFile, $importedpath . basename($nzbFile));
                 }
             }
         } else {

//.........这里部分代码省略.........
         if ($type != 'partrepair') {
             echo "Received " . sizeof($msgsreceived) . " articles of " . ($last - $first + 1) . " requested, " . sizeof($msgsignored) . " not binaries {$n}";
         }
         if ($type == 'update' && sizeof($msgsreceived) == 0) {
             echo "Error: Server did not return any articles.{$n}";
             echo "Skipping group{$n}";
             return false;
         }
         if (sizeof($rangenotreceived) > 0) {
             switch ($type) {
                 case 'backfill':
                     //don't add missing articles
                     break;
                 case 'partrepair':
                 case 'update':
                 default:
                     $this->addMissingParts($rangenotreceived, $groupArr['ID']);
                     break;
             }
             echo "Server did not return " . count($rangenotreceived) . " article(s).{$n}";
         }
         if (isset($this->message) && count($this->message)) {
             $groupRegexes = $releaseRegex->getForGroup($groupArr['name']);
             //insert binaries and parts into database. when binary already exists; only insert new parts
             foreach ($this->message as $subject => $data) {
                 //Filter binaries based on black/white list
                 if ($this->isBlackListed($data, $groupArr['name'])) {
                     $msgsblacklisted[] = count($data['Parts']);
                     if ($type == 'partrepair') {
                         $partIds = array();
                         foreach ($data['Parts'] as $partdata) {
                             $partIds[] = $partdata['number'];
                         }
                         $db->exec(sprintf("DELETE FROM partrepair WHERE numberID IN (%s) AND groupID=%d", implode(',', $partIds), $groupArr['ID']));
                     }
                     continue;
                 }
                 if (isset($data['Parts']) && count($data['Parts']) > 0 && $subject != '') {
                     //Check for existing binary
                     $binaryID = 0;
                     $binaryHash = md5($subject . $data['From'] . $groupArr['ID']);
                     $res = $db->queryOneRow(sprintf("SELECT ID FROM binaries WHERE binaryhash = %s", $db->escapeString($binaryHash)));
                     if (!$res) {
                         //Apply Regexes
                         $regexMatches = array();
                         foreach ($groupRegexes as $groupRegex) {
                             $regexCheck = $releaseRegex->performMatch($groupRegex, $subject);
                             if ($regexCheck !== false) {
                                 $regexMatches = $regexCheck;
                                 break;
                             }
                         }
                         $sql = '';
                         if (!empty($regexMatches)) {
                             $relparts = explode("/", $regexMatches['parts']);
                             $sql = sprintf("INSERT INTO binaries (name, fromname, date, xref, totalparts, groupID, procstat, categoryID, regexID, reqID, relpart, reltotalpart, binaryhash, relname, dateadded) VALUES (%s, %s, FROM_UNIXTIME(%s), %s, %s, %d, %d, %s, %d, %s, %d, %d, %s, %s, now())", $db->escapeString($subject), $db->escapeString(utf8_encode($data['From'])), $db->escapeString($data['Date']), $db->escapeString($data['Xref']), $db->escapeString($data['MaxParts']), $groupArr['ID'], Releases::PROCSTAT_TITLEMATCHED, $regexMatches['regcatid'], $regexMatches['regexID'], $db->escapeString($regexMatches['reqID']), $relparts[0], $relparts[1], $db->escapeString($binaryHash), $db->escapeString(str_replace('_', ' ', $regexMatches['name'])));
                         } elseif ($this->onlyProcessRegexBinaries === false) {
                             $sql = sprintf("INSERT INTO binaries (name, fromname, date, xref, totalparts, groupID, binaryhash, dateadded) VALUES (%s, %s, FROM_UNIXTIME(%s), %s, %s, %d, %s, now())", $db->escapeString($subject), $db->escapeString(utf8_encode($data['From'])), $db->escapeString($data['Date']), $db->escapeString($data['Xref']), $db->escapeString($data['MaxParts']), $groupArr['ID'], $db->escapeString($binaryHash));
                         } elseif ($type == 'partrepair') {
                             $partIds = array();
                             foreach ($data['Parts'] as $partdata) {
                                 $partIds[] = $partdata['number'];
                             }
                             $db->exec(sprintf("DELETE FROM partrepair WHERE numberID IN (%s) AND groupID=%d", implode(',', $partIds), $groupArr['ID']));
                             continue;
                         }

 /**
  * Bulk updates objects in collection
  *
  * @param array $field_values
  * @return mixed
  */
 public function update(array $field_values)
 {
     $this->remove('select');
     $this->sql_accum[] = array('stmt' => 'update', 'sql' => null, 'sort' => 1, 'val' => $field_values);
     $sql = $this->compile();
     $tbl = $this->getStatement('table');
     OrmCache::clearCacheForTable($tbl['sql']);
     return Db::exec($sql, $this->values_accum);
 }

 public function testExecFailThrowsQueryException()
 {
     $this->mockPdo->method('prepare')->willThrowException(new \PDOException());
     $this->expectException(\Starlit\Db\Exception\QueryException::class);
     $this->db->exec('NO SQL');
 }