PHP SQLite3::exec方法代码示例

 protected function _exec($sql)
 {
     if (!$this->_db->exec($this->_sql($sql))) {
         // TODO send error message to out
         $this->_out->stop("Sqlite exec error");
     }
 }

 /**
  *
  * Constructor for RegisterVisitor class
  * @param \SQLite3 $db: SQLite3 Database object
  *
  */
 public function __construct($db)
 {
     $this->db = $db;
     $this->db->exec('CREATE TABLE IF NOT EXISTS visitor (vid TEXT, ip TEXT, httpUserAgent TEXT, dateVisited INTEGER, PRIMARY KEY(vid, ip))');
     $this->visitorID = $this->generateToken();
     $this->ip = $this->get_ip();
 }

 /**
  * Cleans entries from journal.
  * @param  array  $conditions
  * @return array of removed items or NULL when performing a full cleanup
  */
 public function clean(array $conditions)
 {
     if (!empty($conditions[Cache::ALL])) {
         $this->database->exec('DELETE FROM CACHE;');
         return;
     }
     $query = array();
     if (!empty($conditions[Cache::TAGS])) {
         $tags = array();
         foreach ((array) $conditions[Cache::TAGS] as $tag) {
             $tags[] = "'" . $this->database->escapeString($tag) . "'";
         }
         $query[] = 'tag IN(' . implode(', ', $tags) . ')';
     }
     if (isset($conditions[Cache::PRIORITY])) {
         $query[] = 'priority <= ' . (int) $conditions[Cache::PRIORITY];
     }
     $entries = array();
     if (!empty($query)) {
         $query = implode(' OR ', $query);
         $result = $this->database->query("SELECT entry FROM cache WHERE {$query}");
         if ($result instanceof SQLiteResult) {
             while ($entry = $result->fetchSingle()) {
                 $entries[] = $entry;
             }
         } else {
             while ($entry = $result->fetchArray(SQLITE3_NUM)) {
                 $entries[] = $entry[0];
             }
         }
         $this->database->exec("DELETE FROM cache WHERE {$query}");
     }
     return $entries;
 }

 public function mainAction()
 {
     $this->dbconf = Phalcon\DI::getDefault()->getConfig()->database;
     $file = $this->getDI()->getConfig()->dirs->config . DIRECTORY_SEPARATOR . 'schema' . DIRECTORY_SEPARATOR . $this->dbconf->adapter . '.sql';
     $this->schema = realpath($file);
     if ($this->schema === false) {
         throw new \Exception('Unsupported database adapter: ' . $this->dbconf->adapter);
     }
     echo "{$this->dbconf->adapter}\n";
     echo $this->dropDatabase();
     switch ($this->dbconf->adapter) {
         case 'mysql':
             echo $this->createDatabase();
             echo $this->loadSchema();
             echo $this->loadFixtures();
             break;
         case 'sqlite':
             $dbfile = $this->di->getConfig()->dirs->data . DIRECTORY_SEPARATOR . $this->di->get('config')->database->dbname . '.sqlite';
             $db = new SQLite3($dbfile);
             chmod($dbfile, 0664);
             $db->createFunction('MD5', 'md5');
             $db->exec(file_get_contents($this->schema));
             $db->exec(file_get_contents(preg_replace('/sqlite.sql$/', 'fixtures.sql', $this->schema)));
             break;
         default:
             throw new \Exception('Unsupported database adapter: ' . $this->dbconf->adapter);
             break;
     }
 }

 public function __construct($namespace = null)
 {
     if (!extension_loaded('sqlite3')) {
         throw new Exception('sqlite3 extension not loaded');
     }
     // force an alphanumeric namespace
     if ($namespace and !preg_match('/^[a-z0-9]+$/i', $namespace)) {
         throw new Exception('$namespace must be alphanumeric');
     }
     // get a nice filepath for the database
     $file = VAR_DIR . '/index' . $namespace . '.sqlite';
     // create the database if needed
     if (!file_exists($file)) {
         $db = new SQLite3($file);
         // make the map of tags to ids -- forcing the relationship to be unique
         $db->exec("CREATE TABLE map (id TEXT,tag TEXT, PRIMARY KEY (id,tag))");
         // make the object store
         $db->exec("CREATE TABLE objects (id TEXT PRIMARY KEY,object BLOB)");
         // indicies are created on the destructor for performance reasons
     } else {
         $db = new SQLite3($file);
     }
     // not running a bank, so don't care if a power failure causes corruption
     $db->exec("PRAGMA journal_mode = OFF");
     $db->exec("PRAGMA synchronous = OFF");
     // do everything in a batch for speed
     // this is particularly useful for indexing
     $db->exec("BEGIN TRANSACTION");
     $this->db = $db;
     $this->file = $file;
     $this->namespace = $namespace;
 }

 /**
  * Query
  * 
  * @param mixed $query
  */
 function sql_query($query)
 {
     if (empty($query)) {
         core::dprint('Empty sql_query call');
         return false;
     }
     if (is_array($query)) {
         $query = vsprintf($query[0], array_slice($query, 1));
     }
     $query = trim($query);
     if (empty($query)) {
         return false;
     }
     if (!$this->_connect()) {
         return false;
     }
     ++$this->_counter;
     $this->_sqlite_fixes($query);
     $this->_last_query = $query;
     $is_select = preg_match('@^(SELECT|PRAGMA)@i', $query);
     $microtime = microtime(1);
     // how the fuck to catch errors in query?
     $this->query_result = $is_select ? @$this->_connect_id->query($query) : @$this->_connect_id->exec($query);
     $this->_last_query_time = microtime(1) - $microtime;
     core::dprint(array('[SQL%0d|%.5f : %s', $this->_counter, $this->_last_query_time, $query), core::E_SQL);
     if (!$this->query_result) {
         $err = $this->sql_error();
         core::dprint('[SQL FAIL] ' . $err['message'] . ' : ' . $err['code'], core::E_SQL);
     }
     return $this->query_result;
 }

    public function OpenLogFile()
    {
        $db = null;
        $filename = $this->logdir . "/" . $this->logfile;
        if (!file_exists($this->logdir)) {
            mkdir($this->logdir, 0755, true);
        }
        $db = new SQLite3($filename);
        $sql = <<<SQL
CREATE TABLE IF NOT EXISTS account_attempt (
  time_attempted date not null default CURENT_TIMESTAMP,
  username text not null default '',
  email text not null default '',
  ip text not null default '',
  trigger text not null default '',
  confidence float not null default 0.0,
  accepted text default 'Y',
  verified text default 'N',
  primary key (time_attempted, username, email, ip)
)
SQL;
        $db->exec($sql);
        $sql = <<<IDXSQL
CREATE INDEX IF NOT EXISTS account_verification
ON account_attempt (accepted, verified)
IDXSQL;
        $db->exec($sql);
        return $db;
    }

 public function gc($maxlifetime)
 {
     $stmt = $this->sdb->prepare('DELETE FROM dalmp_sessions WHERE expiry < :expiry');
     $stmt->bindValue(':expiry', time(), SQLITE3_INTEGER);
     $stmt->execute();
     return $this->sdb->exec('VACUUM');
 }

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());
    }
}

 public static function setUpBeforeClass()
 {
     // Create the test DB.
     self::$dbConnection = Database::getConnection();
     $schemaPath = APPLICATION_PATH . '/../data/' . MerchantApplication::app()->getConfig()->db->schema;
     self::$dbConnection->exec(file_get_contents($schemaPath));
 }

function ConvertDB($fname)
{
    //	echo "=> {$fname}\n";
    if (!file_exists($fname)) {
        exit(10);
    }
    $db = new SQLite3('base.sqlite');
    $db->exec("pragma synchronous = off;");
    $tablename = pathinfo($fname, PATHINFO_FILENAME);
    $pdx = new Paradox();
    $pdx->Open($fname);
    $db->exec(createdb_schema($tablename, $pdx->GetSchema()));
    $db->exec('DELETE FROM ' . $tablename);
    if ($records = $pdx->GetNumRows()) {
        $schema = $pdx->GetSchema();
        //		print_r($schema);
        for ($rec = 0; $rec < $records; $rec++) {
            $pdx->GetRow($rec);
            //			if ($rec > 2) break;
            $query = 'INSERT INTO `' . $tablename . '` VALUES (';
            $values = '';
            foreach ($pdx->row as $fieldName => $value) {
                switch ($schema[$fieldName]['type']) {
                    case 1:
                        $value = brackets(iconv('windows-1251', 'UTF-8', $value));
                        break;
                    case 2:
                        $value = brackets($pdx->GetStringfromDate($value));
                        break;
                    case 21:
                        $value = brackets($pdx->GetStringfromTimestamp($value));
                        break;
                    case 4:
                        $value = (int) $value;
                        break;
                    case 6:
                        $value = (double) $value;
                        break;
                    case 9:
                        $value = (int) $value;
                        break;
                    case 13:
                        $value = "X" . brackets(bin2hex($value));
                        break;
                    default:
                        $value;
                        break;
                }
                $values .= $value . ', ';
                //				print "{$schema[$fieldName]['type']}\t{$fieldName}\t{$value}\n";
            }
            $values = rtrim($values, ', ');
            $query .= $values . ");\n";
            //			print trim($query).PHP_EOL;
            $db->exec($query);
        }
        return true;
    }
    $pdx->Close();
}

 protected function InitDB()
 {
     $handle = new SQLite3($this->filename);
     $handle->exec("PRAGMA synchronous = OFF");
     $handle->exec("PRAGMA journal_mode = OFF");
     $handle->exec("PRAGMA cache_size = 1");
     return $handle;
 }

 public function setUp()
 {
     $app = (require __DIR__ . '/../../../../src/app.php');
     $this->path = sys_get_temp_dir() . '/sismo.db';
     @unlink($this->path);
     $this->db = new \SQLite3($this->path);
     $this->db->busyTimeout(1000);
     $this->db->exec($app['db.schema']);
 }

function createSqliteTestTable($tmp_sqlite)
{
    unlink($tmp_sqlite);
    $db = new SQLite3($tmp_sqlite);
    $db->exec("CREATE TABLE foo (bar STRING)");
    $db->exec("INSERT INTO foo VALUES ('ABC')");
    $db->exec("INSERT INTO foo VALUES ('DEF')");
    VS($db->lasterrorcode(), 0);
}

 public function deleteEntity($uniqueName)
 {
     list($type, $name) = explode("/", $uniqueName);
     if ($this->existsEntity($type, $name)) {
         $this->db->exec("DELETE FROM ents WHERE ent_type = '{$this->db->escapeString($type)}'\n\t\t\t\tAND ent_name = '{$this->db->escapeString($name)}';");
         $this->db->exec("DELETE FROM ent_accounts WHERE ent_type = '{$this->db->escapeString($type)}'\n\t\t\t\tAND ent_name = '{$this->db->escapeString($name)}';");
         $this->db->exec("DELETE FROM ent_loans WHERE ent_type = '{$this->db->escapeString($type)}'\n\t\t\t\tAND ent_name = '{$this->db->escapeString($name)}';");
     }
 }