PHP SQLite3::prepare方法代码示例

 protected function extract()
 {
     $this->comment('Extracting definitions ...');
     $db = new \SQLite3($this->versionDb);
     // extract Grimoire definition
     $grimoire = $db->prepare("SELECT json FROM DestinyGrimoireDefinition WHERE id = 0")->execute()->fetchArray(SQLITE3_ASSOC);
     $this->export("Grimoire", 0, json_decode($grimoire['json'], true));
     // extract all the other definitions
     $map = ["ActivityBundle" => "bundleHash", "Activity" => "activityHash", "ActivityType" => "activityTypeHash", "Class" => "classHash", "Combatant" => "combatantHash", "Destination" => "destinationHash", "DirectorBook" => "bookHash", "EnemyRace" => "raceHash", "Faction" => "factionHash", "Gender" => "genderHash", "GrimoireCard" => "cardId", "HistoricalStats" => "statId", "InventoryBucket" => "bucketHash", "InventoryItem" => "itemHash", "Place" => "placeHash", "Progression" => "progressionHash", "Objective" => "objectiveHash", "Race" => "raceHash", "SandboxPerk" => "perkHash", "ScriptedSkull" => "skullHash", "SpecialEvent" => "eventHash", "Stat" => "statHash", "StatGroup" => "statGroupHash", "TalentGrid" => "gridHash", "UnlockFlag" => "flagHash", "VendorCategory" => "categoryHash", "Vendor" => "summary.vendorHash"];
     foreach ($map as $folder => $key) {
         $table = "Destiny{$folder}Definition";
         $result = $db->prepare("SELECT json FROM {$table}")->execute();
         while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
             $json = json_decode($row['json'], true);
             $hash = (string) array_get($json, $key);
             $this->export($folder, $hash, $json);
         }
         $this->line($folder);
     }
     $db->close();
 }

 /**
  * remove session(s) from database
  * @param $sessionids session ids ( or id )
  */
 public function removeSession($sessionids)
 {
     if ($this->handle != null) {
         if (is_array($sessionids)) {
             $tmpids = $sessionids;
         } else {
             $tmpids = array($sessionids);
         }
         $this->handle->begin();
         $stmt = $this->handle->prepare('DELETE FROM captiveportal WHERE sessionid = :sessionid');
         foreach ($tmpids as $session) {
             $this->handle->executePrepared($stmt, array('sessionid' => $session), array("sessionid" => \PDO::PARAM_STR));
             $stmt->execute();
         }
         $this->handle->commit();
     }
 }

function main()
{
    global $G;
    message("PHP testing sandbox (%s) version %s", $G['ME'], VERSION);
    try {
        $db = new SQLite3(DATABASE);
        $db->exec('DROP TABLE IF EXISTS t');
        $db->exec('CREATE TABLE t (a, b, c)');
        message('Table t sucessfully created');
        $sth = $db->prepare('INSERT INTO t VALUES (?, ?, ?)');
        $sth->bindValue(1, 'a');
        $sth->bindValue(2, 'b');
        $sth->bindValue(3, 'c');
        $sth->execute();
        $sth->bindValue(1, 1);
        $sth->bindValue(2, 2);
        $sth->bindValue(3, 3);
        $sth->execute();
        $sth->bindValue(1, 'one');
        $sth->bindValue(2, 'two');
        $sth->bindValue(3, 'three');
        $sth->execute();
        $sth = $db->prepare('SELECT * FROM t');
        $result = $sth->execute();
        while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
            message('%s, %s, %s', $row['a'], $row['b'], $row['c']);
        }
    } catch (Exception $e) {
        message($e->getMessage());
    }
}

 /**
  * @param StatsModel $model
  * @param string $data
  * @param int $count
  * @return \SQLite3Result
  */
 public function insertData(StatsModel $model, $data, $count = 1)
 {
     $statement = $this->conn->prepare(sprintf("INSERT INTO `%s` (`date`, `count`, `data`) VALUES (%d, :count, :data);", $model->getEvent(), intval(date('U'))));
     $statement->bindValue(':data', $data, SQLITE3_TEXT);
     $statement->bindValue(':count', $count, SQLITE3_INTEGER);
     return $statement->execute();
 }

 /**
  *
  *
  * @return: bool whether the user has been registered in the database
  */
 public function isVisitorRegistered($vid, $ip)
 {
     $sql = 'SELECT * FROM visitor WHERE vid=:vid AND ip=:ip';
     $sth = $this->db->prepare($sql);
     $sth->bindValue(':vid', $vid, SQLITE3_TEXT);
     $sth->bindValue(':ip', $ip, SQLITE3_TEXT);
     $result = $sth->execute();
     if (!$result->fetchArray()) {
         return false;
     } else {
         return true;
     }
 }

 /**
  * {@inheritdoc}
  */
 protected function executeQuery($query, array $parameters)
 {
     if (isset($this->statements[$query])) {
         $statement = $this->statements[$query];
     } else {
         // Temporary set the error reporting level to 0 to avoid any warning.
         $errorReportingLevel = error_reporting(0);
         $statement = $this->sqlite3->prepare($query);
         // Restore the original error reporting level.
         error_reporting($errorReportingLevel);
         $errorCode = $this->sqlite3->lastErrorCode();
         if ($errorCode !== 0) {
             $exception = new SQLite3Exception($this->sqlite3->lastErrorMsg(), $errorCode);
             if ($errorCode === 1) {
                 // SQL error cause by a missing function, this must be reported with a GeometryEngineException.
                 throw GeometryEngineException::operationNotSupportedByEngine($exception);
             } else {
                 // Other SQLite3 error; we cannot trigger the original E_WARNING, so we throw this exception instead.
                 throw $exception;
             }
         } else {
             $this->statements[$query] = $statement;
         }
     }
     $index = 1;
     foreach ($parameters as $parameter) {
         if ($parameter instanceof Geometry) {
             if ($parameter->isEmpty()) {
                 $statement->bindValue($index++, $parameter->asText(), SQLITE3_TEXT);
                 $statement->bindValue($index++, $parameter->SRID(), SQLITE3_INTEGER);
             } else {
                 $statement->bindValue($index++, $parameter->asBinary(), SQLITE3_BLOB);
                 $statement->bindValue($index++, $parameter->SRID(), SQLITE3_INTEGER);
             }
         } else {
             if ($parameter === null) {
                 $type = SQLITE3_NULL;
             } elseif (is_int($parameter)) {
                 $type = SQLITE3_INTEGER;
             } elseif (is_float($parameter)) {
                 $type = SQLITE3_FLOAT;
             } else {
                 $type = SQLITE3_TEXT;
             }
             $statement->bindValue($index++, $parameter, $type);
         }
     }
     $result = $statement->execute();
     return $result->fetchArray(SQLITE3_NUM);
 }

 /**
  * @param string[] $headers
  * @param string $body
  * @return bool Success
  */
 public function store($headers, $body)
 {
     if (!is_array($headers)) {
         $headers = [$headers];
     }
     $body = strval($body);
     $tableName = $this->_tableName;
     $statement = $this->_databaseHandle->prepare("INSERT INTO {$tableName} (id, headers, body) VALUES (null, :headers, :body)");
     $statement->bindValue(':headers', json_encode($headers), SQLITE3_TEXT);
     $statement->bindValue(':body', $body, SQLITE3_TEXT);
     $result = $statement->execute();
     $this->_lastNativeResultFromStore = $result;
     $this->_lastSuccessFromStore = $result !== false;
     return $this->_lastSuccessFromStore;
 }

/**
 * Get a list of venues which are whitin $distance of location ($lat, long)
 * and have a category similar to $keyword
 * 
 * returns list of venue information
 */
function locationKeywordSearch($lat, $long, $keyword, $distance, $limit = 25)
{
    $_SESSION['lat'] = $lat;
    $_SESSION['long'] = $long;
    $_SESSION['keyword'] = $keyword;
    global $databaseName;
    $db = new SQLite3($databaseName);
    // Attach methods haversineGreatCircleDistance,stringSimilarity to
    // Database engine so that we can use them in our query
    $db->createFunction("DISTANCE", "haversineGreatCircleDistance");
    $db->createFunction("SIMILARITY", "stringSimilarity");
    // Search query: get venue information for venues close to location, with categories most similar to the keyword/phrase
    $statement = $db->prepare('SELECT venueId, name, lat, long, categories, address, DISTANCE(lat, long) as distance, SIMILARITY(categories)' . " as similarity FROM Venues WHERE distance < :dist ORDER BY similarity DESC LIMIT :limit");
    // Bind some parameters to the query
    $statement->bindValue(':limit', $limit);
    $statement->bindValue(':dist', $distance, SQLITE3_INTEGER);
    // Obtain the venues from the db and put them in a list
    $qry = $statement->execute();
    $venues = [];
    while ($venue = $qry->fetchArray()) {
        $venues[] = $venue;
    }
    $db->close();
    return $venues;
}

 /**
  * Check if cache table exists
  *
  * @return void
  */
 protected function checkTable()
 {
     $tables = [];
     $sql = "SELECT name FROM sqlite_master WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%' " . "UNION ALL SELECT name FROM sqlite_temp_master WHERE type IN ('table', 'view') ORDER BY 1";
     if ($this->isPdo) {
         $sth = $this->sqlite->prepare($sql);
         $sth->execute();
         $result = $sth;
         while (($row = $result->fetch(\PDO::FETCH_ASSOC)) != false) {
             $tables[] = $row['name'];
         }
     } else {
         $result = $this->sqlite->query($sql);
         while (($row = $result->fetchArray(SQLITE3_ASSOC)) != false) {
             $tables[] = $row['name'];
         }
     }
     // If the cache table doesn't exist, create it.
     if (!in_array($this->table, $tables)) {
         $sql = 'CREATE TABLE IF NOT EXISTS "' . $this->table . '" ("id" VARCHAR PRIMARY KEY NOT NULL UNIQUE, "start" INTEGER, "expire" INTEGER, "lifetime" INTEGER, "value" BLOB, "time" INTEGER)';
         if ($this->isPdo) {
             $sth = $this->sqlite->prepare($sql);
             $sth->execute();
         } else {
             $this->sqlite->query($sql);
         }
     }
 }

 /**
  * Wait for a message in the queue and save the message to a safety queue
  *
  * @param int $timeout
  * @return Message
  */
 public function waitAndReserve($timeout = null)
 {
     $timeout = $timeout !== null ? $timeout : $this->defaultTimeout;
     for ($time = 0; $time < $timeout; $time++) {
         $row = @$this->connection->querySingle('SELECT rowid, payload FROM queue ORDER BY rowid ASC LIMIT 1', true);
         if ($row !== []) {
             $message = $this->decodeMessage($row['payload']);
             $message->setIdentifier($row['rowid']);
             $encodedMessage = $this->encodeMessage($message);
             $preparedDelete = $this->connection->prepare('DELETE FROM queue WHERE rowid=:rowid');
             $preparedDelete->bindValue(':rowid', $row['rowid']);
             $preparedInsert = $this->connection->prepare('INSERT INTO processing (rowid, payload) VALUES (:rowid, :payload)');
             $preparedInsert->bindValue(':rowid', $row['rowid']);
             $preparedInsert->bindValue(':payload', $encodedMessage);
             try {
                 $this->connection->query('BEGIN IMMEDIATE TRANSACTION');
                 $preparedDelete->execute();
                 $preparedInsert->execute();
                 $this->connection->query('COMMIT');
             } catch (\Exception $exception) {
                 $this->connection->query('ROLLBACK');
             }
             return $message;
         }
         sleep(1);
     }
     return null;
 }

 public function init($myrole, $drivers)
 {
     $this->_out->logNotice(">>>init " . get_class($this) . " compare driver");
     $this->_out->logDebug("opening DB file: " . $this->_options['file']);
     $this->_open();
     if ($this->_options['prefix']) {
         $this->_prefix = $this->_options['prefix'];
     } else {
         $this->_prefix = $this->_engine->getUniqueKey();
     }
     $this->_out->logDebug("we will use table name prefix: '{$this->_prefix}'");
     // TODO check that sqlite 3 is installed
     // TODO check mandatory options
     // TODO complain about not allowed options
     // TODO $options['prefix'] can't start with _ it would interfear with testing mode
     if ($this->_testing) {
         // TODO maybe using transaction ROLLBACK in shutdown is better
         $this->_prefix = "_" . $this->_prefix;
         $this->_out->logDebug("we are in testing mode so prefix is changed to: '{$this->_prefix}'");
     }
     $this->_createStructure($this->_options['rebuild']);
     $this->_dbIndexOn(false);
     // prepared statements
     $this->_prepFromRemote2 = $this->_db->prepare($this->_sql("INSERT INTO {$this->_prefix} (path, isDir, remote, rmd5, rtime, rsize) VALUES(:path, :isDir, :isRemote, :md5, :time, :size)", "prepare SQL: "));
     $this->_prepFromRemote1 = $this->_db->prepare($this->_sql("UPDATE {$this->_prefix} SET isDir=:isDir, remote=:isRemote, rsize=:size, rmd5=:md5, rtime=:time WHERE path=:path", "prepare SQL: "));
     $this->_prepFromLocalFull2 = $this->_db->prepare($this->_sql("INSERT INTO {$this->_prefix} (path, isDir, local, lmd5, ltime, lsize) VALUES(:path, :isDir, :isLocal, :md5, :time, :size)", "prepare SQL: "));
     $this->_prepFromLocalFull1 = $this->_db->prepare($this->_sql("UPDATE {$this->_prefix} SET isDir=:isDir, local=:isLocal, lsize=:size, lmd5=:md5, ltime=:time WHERE path=:path", "prepare SQL: "));
     $this->_prepFromLocal2 = $this->_db->prepare($this->_sql("INSERT INTO {$this->_prefix} (path, isDir, local, ltime, lsize) VALUES(:path, :isDir, :isLocal, :time, :size)", "prepare SQL: "));
     $this->_prepFromLocal1 = $this->_db->prepare($this->_sql("UPDATE {$this->_prefix} SET isDir=:isDir, local=:isLocal, lsize=:size, ltime=:time WHERE path=:path", "prepare SQL: "));
     $this->_prepFromLocalMd5 = $this->_db->prepare($this->_sql("UPDATE {$this->_prefix} SET lmd5=:md5 WHERE path=:path", "prepare SQL: "));
     $this->_prepFromRemoteMd5 = $this->_db->prepare($this->_sql("UPDATE {$this->_prefix} SET rmd5=:md5, rts=CASE WHEN rts is null THEN ltime ELSE rts END WHERE path=:path", "prepare SQL: "));
     // remote has done updates
     $this->_prepRemoteHasDeleted = $this->_db->prepare($this->_sql("DELETE FROM {$this->_prefix} WHERE path=:path", "prepare SQL: "));
     $this->_prepRemoteHasUploaded = $this->_db->prepare($this->_sql("UPDATE {$this->_prefix} SET remote=1, rmd5=lmd5, rsize=lsize, rts=ltime WHERE path=:path", "prepare SQL: "));
 }

public  function _gc($maxlifetime) {
        $stmt=parent::prepare("delete
                                 from http_sessions
                                 where datetime())-strftime('%s',alive)>:lifetime");
        $stmt->bindValue(':lifetime', ini_get('session.gc_maxlifetime'), SQLITE3_INTEGER);
        $stmt->execute();
        return parent::changes();}

function parseCSV($file)
{
    $db = new SQLite3('band2.db');
    if ($_POST['tableOption'] == 2) {
        echo "Delete table<br>";
        $query = "DELETE FROM " . $_POST['tableSelect'] . ";";
        echo $query;
        $stmt = $db->prepare($query);
        $stmt->execute();
    }
    if ($_POST['tableOption'] !== 0) {
        if (($handle = fopen($file, "r")) !== FALSE) {
            if ($_POST['tableSelect'] === "Student") {
                echo "Student";
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    $stmt = $db->prepare("INSERT INTO Student (section, studentid, firstname, lastname) VALUES(:1, :2, :3, :4);");
                    $stmt->bindValue(':1', $data[0], SQLITE3_TEXT);
                    $stmt->bindValue(':2', $data[1], SQLITE3_INTEGER);
                    $stmt->bindValue(':3', $data[2], SQLITE3_TEXT);
                    $stmt->bindValue(':4', $data[3], SQLITE3_TEXT);
                    $stmt->execute();
                    $stmt = $db->prepare("INSERT INTO Attendance (studentid) VALUES ({$data['1']});");
                    $stmt->execute();
                }
            }
            if ($_POST['tableSelect'] == "Instrument") {
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    $stmt = $db->prepare("INSERT INTO Instrument (instrumentserial, instrumentManufacturer, instrumentname) VALUES(:1, :2, :3);");
                    $stmt->bindValue(':1', $data[0], SQLITE3_TEXT);
                    $stmt->bindValue(':2', $data[1], SQLITE3_TEXT);
                    $stmt->bindValue(':3', $data[2], SQLITE3_TEXT);
                    $stmt->execute();
                }
            }
            if ($_POST['tableSelect'] == "Uniform") {
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    $stmt = $db->prepare("INSERT INTO Uniform (itemtype, itemnumber) VALUES(:1, :2);");
                    $stmt->bindValue(':1', $data[0], SQLITE3_INTEGER);
                    $stmt->bindValue(':2', $data[1], SQLITE3_INTEGER);
                    $stmt->execute();
                }
            }
            fclose($handle);
        }
    }
}

 /**
  * Creates a new Statement that uses the given connection handle and SQL statement.
  *
  * @param \SQLite3 $conn
  * @param string   $prepareString
  */
 public function __construct(\SQLite3 $conn, $prepareString)
 {
     $this->_conn = $conn;
     $this->_stmt = $conn->prepare($prepareString);
     if (false === $this->_stmt) {
         throw new Exception($this->errorInfo(), $this->errorCode());
     }
 }

 /**
  * Clean up outdated entries by checking against the ttl.
  * @return boolean TRUE on success
  * @throws CoreXEngine\Cache\AccessException if we want to write a read-only file
  * @throws CoreXEngine\Cache\Exception for no special typed problem
  */
 public function clean()
 {
     $statement = $this->database->prepare("DELETE FROM {$this->table} WHERE c_ttl IS NOT NULL AND c_ttl < :ttl");
     $statement->bindValue(':ttl', time(), SQLITE3_INTEGER);
     @$statement->execute();
     $this->checkError();
     return true;
 }