Book/sqlite3-Phpdoc专题

SQLite3

简介
安装／配置
预定义常量
SQLite3 — SQLite3 类
- SQLite3::backup — Backup one database to another database
- SQLite3::busyTimeout — Sets the busy connection handler
- SQLite3::changes — Returns the number of database rows that were changed (or inserted or deleted) by the most recent SQL statement
- SQLite3::close — Closes the database connection
- SQLite3::__construct — Instantiates an SQLite3 object and opens an SQLite 3 database
- SQLite3::createAggregate — Registers a PHP function for use as an SQL aggregate function
- SQLite3::createCollation — Registers a PHP function for use as an SQL collating function
- SQLite3::createFunction — Registers a PHP function for use as an SQL scalar function
- SQLite3::enableExceptions — Enable throwing exceptions
- SQLite3::escapeString — Returns a string that has been properly escaped
- SQLite3::exec — Executes a result-less query against a given database
- SQLite3::lastErrorCode — Returns the numeric result code of the most recent failed SQLite request
- SQLite3::lastErrorMsg — Returns English text describing the most recent failed SQLite request
- SQLite3::lastInsertRowID — Returns the row ID of the most recent INSERT into the database
- SQLite3::loadExtension — Attempts to load an SQLite extension library
- SQLite3::open — Opens an SQLite database
- SQLite3::openBlob — Opens a stream resource to read a BLOB
- SQLite3::prepare — Prepares an SQL statement for execution
- SQLite3::query — Executes an SQL query
- SQLite3::querySingle — Executes a query and returns a single result
- SQLite3::setAuthorizer — Configures a callback to be used as an authorizer to limit what a statement can do
- SQLite3::version — Returns the SQLite3 library version as a string constant and as a number
SQLite3Stmt — SQLite3Stmt 类
- SQLite3Stmt::bindParam — Binds a parameter to a statement variable
- SQLite3Stmt::bindValue — Binds the value of a parameter to a statement variable
- SQLite3Stmt::clear — Clears all current bound parameters
- SQLite3Stmt::close — Closes the prepared statement
- SQLite3Stmt::execute — Executes a prepared statement and returns a result set object
- SQLite3Stmt::getSQL — Get the SQL of the statement
- SQLite3Stmt::paramCount — Returns the number of parameters within the prepared statement
- SQLite3Stmt::readOnly — Returns whether a statement is definitely read only
- SQLite3Stmt::reset — Resets the prepared statement
SQLite3Result — SQLite3Result 类
- SQLite3Result::columnName — Returns the name of the nth column
- SQLite3Result::columnType — Returns the type of the nth column
- SQLite3Result::fetchArray — Fetches a result row as an associative or numerically indexed array or both
- SQLite3Result::finalize — Closes the result set
- SQLite3Result::numColumns — Returns the number of columns in the result set
- SQLite3Result::reset — Resets the result set back to the first row

对 SQLite v3 数据库的支持信息。

安装／配置

需求

构建此扩展不需要其他扩展。

安装

SQLite3 扩展自 PHP 5.3.0 起已默认启用。允许在编译时使用 --without-sqlite3 禁用之。

Windows 用户必须启用 php_sqlite3.dll 方可使用该扩展。自 PHP 5.3.0 起，此扩展的 DLL 文件已包含于 Windows 版的 PHP 发行包中。

Note:

该扩展是 PECL 扩展的简化版本，但后者仅建议用于实验性用途。

运行时配置

这些函数的行为受 php.ini 中的设置影响。

名字	默认	可修改范围	更新日志
sqlite3.extension_dir	""	PHP_INI_SYSTEM	PHP 5.3.11 起可用
sqlite3.defensive	1	PHP_INI_SYSTEM	PHP 7.2.17 和 7.3.4 起可用，libsqlite ≥ 3.26.0。

这是配置指令的简短说明。

sqlite3.extension_dir string
Path to the directory where the loadable extensions for SQLite reside.

sqlite3.defensive bool
When the defensive flag is enabled, language features that allow ordinary SQL to deliberately corrupt the database file are disabled. This forbids writing directly to the schema, shadow tables (eg. FTS data tables), or the sqlite_dbpage virtual table. This php.ini setting is only effective for libsqlite ≥ 3.26.0.

资源类型

此扩展没有定义资源类型。

预定义常量

下列常量由此扩展定义，且仅在此扩展编译入 PHP 或在运行时动态载入时可用。

SQLITE3_ASSOC (integer)
指定 Sqlite3Result::fetchArray 方法返回按列名称索引的数组，其中列名即相应结果集返回的列名。

SQLITE3_NUM (integer)
指定 Sqlite3Result::fetchArray 方法返回按列序号索引的数组，其中列号即相应结果集返回的列号，从第 0 列开始计数。

SQLITE3_BOTH (integer)
指定 Sqlite3Result::fetchArray 方法返回同时按列名称与列序号索引的数组，其中列名即相应结果集返回的列名，列号即相应结果集返回的列号，从第 0 列开始计数。

SQLITE3_INTEGER (integer)
表示 SQLite3 INTEGER (整型) 存储类。

SQLITE3_FLOAT (integer)
表示 SQLite3 REAL (FLOAT) (实型) 存储类。

SQLITE3_TEXT (integer)
表示 SQLite3 TEXT (文本) 存储类。

SQLITE3_BLOB (integer)
表示 SQLite3 BLOB (二进制对象) 存储类。

SQLITE3_NULL (integer)
表示 SQLite3 NULL 存储类。

SQLITE3_OPEN_READONLY (integer)
指定 SQLite3 数据库以只读模式打开。

SQLITE3_OPEN_READWRITE (integer)
指定 SQLite3 数据库以读写模式打开。

SQLITE3_OPEN_CREATE (integer)
指定 SQLite3 数据库若不存在，则创建并打开。

简介

实现与 SQLite 3 数据库对接的类。

类摘要

SQLite3

class SQLite3 {

/* 方法 */

public bool backup ( SQLite3 $destination_db [, string $source_dbname = "main" [, string $destination_dbname = "main" ]] )

public bool busyTimeout ( int $msecs )

public int changes ( void )

public bool close ( void )

public __construct ( string $filename [, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE [, string $encryption_key = "" ]] )

public bool createAggregate ( string $name , mixed $step_callback , mixed $final_callback [, int $argument_count = -1 ] )

public bool createCollation ( string $name , callable $callback )

public bool createFunction ( string $name , mixed $callback [, int $argument_count = -1 [, int $flags = 0 ]] )

bool enableExceptions ([ bool $enableExceptions = false ] )

public static string escapeString ( string $value )

public bool exec ( string $query )

public int lastErrorCode ( void )

public string lastErrorMsg ( void )

public int lastInsertRowID ( void )

public bool loadExtension ( string $shared_library )

public void open ( string $filename [, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE [, string $encryption_key = "" ]] )

public resourcefalse openBlob ( string $table , string $column , int $rowid [, string $dbname = "main" [, int $flags = SQLITE3_OPEN_READONLY ]] )

public SQLite3Stmtfalse prepare ( string $query )

public SQLite3Resultfalse query ( string $query )

public mixed querySingle ( string $query [, bool $entire_row = false ] )

public bool setAuthorizer ( callablenull $callback )

public static array version ( void )

}

SQLite3::backup

Backup one database to another database

说明

public bool SQLite3::backup ( SQLite3 $destination_db [, string $source_dbname = "main" [, string $destination_dbname = "main" ]] )

SQLite3::backup copies the contents of one database into another, overwriting the contents of the destination database. It is useful either for creating backups of databases or for copying in-memory databases to or from persistent files.

参数

destination_db
A database connection opened with SQLite3::open.

source_dbname
The database name is "main" for the main database, "temp" for the temporary database, or the name specified after the AS keyword in an ATTACH statement for an attached database.

destination_dbname
Analogous to source_dbname but for the destination_db.

返回值

成功时返回 true，或者在失败时返回 false。

范例

示例 #1 Backup an existing database

<?php
// $conn is a connection to an already opened sqlite3 database

$backup = new SQLite3('backup.sqlite');
$conn->backup($backup);
?>

SQLite3::busyTimeout

Sets the busy connection handler

说明

public bool SQLite3::busyTimeout ( int $msecs )

Sets a busy handler that will sleep until the database is not locked or the timeout is reached.

参数

msecs
The milliseconds to sleep. Setting this value to a value less than or equal to zero, will turn off an already set timeout handler.

返回值

Returns true on success, 或者在失败时返回 false.

SQLite3::changes

Returns the number of database rows that were changed (or inserted or deleted) by the most recent SQL statement

说明

public int SQLite3::changes ( void )

Returns the number of database rows that were changed (or inserted or deleted) by the most recent SQL statement.

参数

此函数没有参数。

返回值

Returns an int value corresponding to the number of database rows changed (or inserted or deleted) by the most recent SQL statement.

范例

示例 #1 SQLite3::changes example

<?php
$db = new SQLite3('mysqlitedb.db');

$query = $db->exec('UPDATE counter SET views=0 WHERE page="test"');
if ($query) {
    echo 'Number of rows modified: ', $db->changes();
}
?>

SQLite3::close

Closes the database connection

说明

public bool SQLite3::close ( void )

Closes the database connection.

参数

此函数没有参数。

返回值

成功时返回 true，或者在失败时返回 false。

范例

示例 #1 SQLite3::close example

<?php
$db = new SQLite3('mysqlitedb.db');
$db->close();
?>

SQLite3::__construct

Instantiates an SQLite3 object and opens an SQLite 3 database

说明

public SQLite3::__construct ( string $filename [, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE [, string $encryption_key = "" ]] )

Instantiates an SQLite3 object and opens a connection to an SQLite 3 database. If the build includes encryption, then it will attempt to use the key.

参数

filename
Path to the SQLite database, or :memory: to use in-memory database. If filename is an empty string, then a private, temporary on-disk database will be created. This private database will be automatically deleted as soon as the database connection is closed.

flags
Optional flags used to determine how to open the SQLite database. By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.

SQLITE3_OPEN_READONLY: Open the database for reading only.
SQLITE3_OPEN_READWRITE: Open the database for reading and writing.
SQLITE3_OPEN_CREATE: Create the database if it does not exist.

encryption_key
An optional encryption key used when encrypting and decrypting an SQLite database. If the SQLite encryption module is not installed, this parameter will have no effect.

返回值

Returns an SQLite3 object on success.

错误／异常

Throws an Exception on failure.

更新日志

版本	说明
7.0.10	The `filename` can now be empty to use a private, temporary on-disk database.

范例

示例 #1 SQLite3::__construct example

<?php
$db = new SQLite3('mysqlitedb.db');

$db->exec('CREATE TABLE foo (bar TEXT)');
$db->exec("INSERT INTO foo (bar) VALUES ('This is a test')");

$result = $db->query('SELECT bar FROM foo');
var_dump($result->fetchArray());
?>

SQLite3::createAggregate

Registers a PHP function for use as an SQL aggregate function

说明

public bool SQLite3::createAggregate ( string $name , mixed $step_callback , mixed $final_callback [, int $argument_count = -1 ] )

Registers a PHP function or user-defined function for use as an SQL aggregate function for use within SQL statements.

参数

name
Name of the SQL aggregate to be created or redefined.

step_callback
Callback function called for each row of the result set. Your PHP function should accumulate the result and store it in the aggregation context.

This function need to be defined as:

mixed step ( mixed $context , int $rownumber , mixed $value , mixed $values )

context
null for the first row; on subsequent rows it will have the value that was previously returned from the step function; you should use this to maintain the aggregate state.

rownumber
The current row number.

value
The first argument passed to the aggregate.

values
Further arguments passed to the aggregate.

The return value of this function will be used as the context argument in the next call of the step or finalize functions.

final_callback
Callback function to aggregate the "stepped" data from each row. Once all the rows have been processed, this function will be called and it should then take the data from the aggregation context and return the result. This callback function should return a type understood by SQLite (i.e. scalar type).

This function need to be defined as:

mixed fini ( mixed $context , int $rownumber )

context
Holds the return value from the very last call to the step function.

rownumber
Always 0.

The return value of this function will be used as the return value for the aggregate.

argument_count
The number of arguments that the SQL aggregate takes. If this parameter is negative, then the SQL aggregate may take any number of arguments.

返回值

Returns true upon successful creation of the aggregate, 或者在失败时返回 false.

范例

示例 #1 max_length aggregation function example

<?php
$data = array(
   'one',
   'two',
   'three',
   'four',
   'five',
   'six',
   'seven',
   'eight',
   'nine',
   'ten',
   );
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE strings(a)");
$insert = $db->prepare('INSERT INTO strings VALUES (?)');
foreach ($data as $str) {
    $insert->bindValue(1, $str);
    $insert->execute();
}
$insert = null;

function max_len_step($context, $rownumber, $string)
{
    if (strlen($string) > $context) {
        $context = strlen($string);
    }
    return $context;
}

function max_len_finalize($context, $rownumber)
{
    return $context === null ? 0 : $context;
}

$db->createAggregate('max_len', 'max_len_step', 'max_len_finalize');

var_dump($db->querySingle('SELECT max_len(a) from strings'));
?>

以上例程会输出：

int(5)

In this example, we are creating an aggregating function that will calculate the length of the longest string in one of the columns of the table. For each row, the max_len_step function is called and passed a $context parameter. The context parameter is just like any other PHP variable and be set to hold an array or even an object value. In this example, we are simply using it to hold the maximum length we have seen so far; if the $string has a length longer than the current maximum, we update the context to hold this new maximum length.

After all of the rows have been processed, SQLite calls the max_len_finalize function to determine the aggregate result. Here, we could perform some kind of calculation based on the data found in the $context. In our simple example though, we have been calculating the result as the query progressed, so we simply need to return the context value.

小贴士

It is NOT recommended for you to store a copy of the values in the context and then process them at the end, as you would cause SQLite to use a lot of memory to process the query - just think of how much memory you would need if a million rows were stored in memory, each containing a string 32 bytes in length.

小贴士

You can use SQLite3::createAggregate to override SQLite native SQL functions.

SQLite3::createCollation

Registers a PHP function for use as an SQL collating function

说明

public bool SQLite3::createCollation ( string $name , callable $callback )

Registers a PHP function or user-defined function for use as a collating function within SQL statements.

参数

name
Name of the SQL collating function to be created or redefined

callback
The name of a PHP function or user-defined function to apply as a callback, defining the behavior of the collation. It should accept two values and return as strcmp does, i.e. it should return -1, 1, or 0 if the first string sorts before, sorts after, or is equal to the second.

This function need to be defined as:

int collation ( mixed $value1 , mixed $value2 )

返回值

成功时返回 true，或者在失败时返回 false。

范例

示例 #1 SQLite3::createCollation example

<?php

$db = new SQLite3(":memory:");
$db->exec("CREATE TABLE test (col1 string)");
$db->exec("INSERT INTO test VALUES ('a1')");
$db->exec("INSERT INTO test VALUES ('a10')");
$db->exec("INSERT INTO test VALUES ('a2')");

$db->createCollation('NATURAL_CMP', 'strnatcmp');

$defaultSort = $db->query("SELECT col1 FROM test ORDER BY col1");
$naturalSort = $db->query("SELECT col1 FROM test ORDER BY col1 COLLATE NATURAL_CMP");

echo "default:\n";
while ($row = $defaultSort->fetchArray()){
    echo $row['col1'], "\n";
}

echo "\nnatural:\n";
while ($row = $naturalSort->fetchArray()){
    echo $row['col1'], "\n";
}

$db->close();

?>

以上例程会输出：

default:
a1
a10
a2

natural:
a1
a2
a10

参见

The SQLite collation documentation: » http://sqlite.org/datatype3.html#collation

SQLite3::createFunction

Registers a PHP function for use as an SQL scalar function

说明

public bool SQLite3::createFunction ( string $name , mixed $callback [, int $argument_count = -1 [, int $flags = 0 ]] )

Registers a PHP function or user-defined function for use as an SQL scalar function for use within SQL statements.

参数

name
Name of the SQL function to be created or redefined.

callback
The name of a PHP function or user-defined function to apply as a callback, defining the behavior of the SQL function.

This function need to be defined as:

mixed callback ( mixed $value , mixed $values )

value
The first argument passed to the SQL function.

values
Further arguments passed to the SQL function.

argument_count
The number of arguments that the SQL function takes. If this parameter is -1, then the SQL function may take any number of arguments.

flags
A bitwise conjunction of flags. Currently, only SQLITE3_DETERMINISTIC is supported, which specifies that the function always returns the same result given the same inputs within a single SQL statement.

返回值

Returns true upon successful creation of the function, false on failure.

更新日志

版本	说明
7.1.4	The `flags` parameter has been added.

范例

示例 #1 SQLite3::createFunction example

<?php
function my_udf_md5($string) {
    return md5($string);
}

$db = new SQLite3('mysqlitedb.db');
$db->createFunction('my_udf_md5', 'my_udf_md5');

var_dump($db->querySingle('SELECT my_udf_md5("test")'));
?>

以上例程的输出类似于：

string(32) "098f6bcd4621d373cade4e832627b4f6"

SQLite3::enableExceptions

Enable throwing exceptions

说明

bool SQLite3::enableExceptions ([ bool $enableExceptions = false ] )

Controls whether the SQLite3 instance will throw exceptions or warnings on error.

参数

enable
When true, the SQLite3 instance, and SQLite3Stmt and SQLite3Result instances derived from it, will throw exceptions on error.

When false, the SQLite3 instance, and SQLite3Stmt and SQLite3Result instances derived from it, will raise warnings on error.

For either mode, the error code and message, if any, will be available via SQLite3::lastErrorCode and SQLite3::lastErrorMsg respectively.

返回值

Returns the old value; true if exceptions were enabled, false otherwise.

范例

示例 #1 SQLite3::enableExceptions example

<?php
$sqlite = new SQLite3(':memory:');
try {
    $sqlite->exec('create table foo');
    $sqlite->enableExceptions(true);
    $sqlite->exec('create table bar');
} catch (Exception $e) {
    echo 'Caught exception: ' . $e->getMessage();
}
?>

以上例程的输出类似于：

Warning: SQLite3::exec(): near "foo": syntax error in example.php on line 4
Caught exception: near "bar": syntax error

SQLite3::escapeString

Returns a string that has been properly escaped

说明

public static string SQLite3::escapeString ( string $value )

Returns a string that has been properly escaped for safe inclusion in an SQL statement.

Warning

此函数（还）不能安全地适用于二进制对象！

To properly handle BLOB fields which may contain NUL characters, use SQLite3Stmt::bindParam instead.

参数

value
The string to be escaped.

返回值

Returns a properly escaped string that may be used safely in an SQL statement.

注释

Warning

addslashes should NOT be used to quote your strings for SQLite queries; it will lead to strange results when retrieving your data.

SQLite3::exec

Executes a result-less query against a given database

说明

public bool SQLite3::exec ( string $query )

Executes a result-less query against a given database.

Note: SQLite3 may need to create » temporary files during the execution of queries, so the respective directories may have to be writable.

参数

query
The SQL query to execute (typically an INSERT, UPDATE, or DELETE query).

返回值

Returns true if the query succeeded, false on failure.

范例

示例 #1 SQLite3::exec example

<?php
$db = new SQLite3('mysqlitedb.db');

$db->exec('CREATE TABLE bar (bar STRING)');
?>

SQLite3::lastErrorCode

Returns the numeric result code of the most recent failed SQLite request

说明

public int SQLite3::lastErrorCode ( void )

Returns the numeric result code of the most recent failed SQLite request.

参数

此函数没有参数。

返回值

Returns an integer value representing the numeric result code of the most recent failed SQLite request.

SQLite3::lastErrorMsg

Returns English text describing the most recent failed SQLite request

说明

public string SQLite3::lastErrorMsg ( void )

Returns English text describing the most recent failed SQLite request.

参数

此函数没有参数。

返回值

Returns an English string describing the most recent failed SQLite request.

SQLite3::lastInsertRowID

Returns the row ID of the most recent INSERT into the database

说明

public int SQLite3::lastInsertRowID ( void )

Returns the row ID of the most recent INSERT into the database.

参数

此函数没有参数。

返回值

Returns the row ID of the most recent INSERT into the database

SQLite3::loadExtension

Attempts to load an SQLite extension library

说明

public bool SQLite3::loadExtension ( string $shared_library )

Attempts to load an SQLite extension library.

参数

shared_library
The name of the library to load. The library must be located in the directory specified in the configure option sqlite3.extension_dir.

返回值

Returns true if the extension is successfully loaded, false on failure.

范例

示例 #1 SQLite3::loadExtension example

<?php
$db = new SQLite3('mysqlitedb.db');
$db->loadExtension('libagg.so');
?>

SQLite3::open

Opens an SQLite database

说明

public void SQLite3::open ( string $filename [, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE [, string $encryption_key = "" ]] )

Opens an SQLite 3 Database. If the build includes encryption, then it will attempt to use the key.

参数

filename
Path to the SQLite database, or :memory: to use in-memory database.

flags
Optional flags used to determine how to open the SQLite database. By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.

SQLITE3_OPEN_READONLY: Open the database for reading only.
SQLITE3_OPEN_READWRITE: Open the database for reading and writing.
SQLITE3_OPEN_CREATE: Create the database if it does not exist.

encryption_key
An optional encryption key used when encrypting and decrypting an SQLite database. If the SQLite encryption module is not installed, this parameter will have no effect.

返回值

没有返回值。

范例

示例 #1 SQLite3::open example

<?php
/**
 * Simple example of extending the SQLite3 class and changing the __construct
 * parameters, then using the open method to initialize the DB.
 */
class MyDB extends SQLite3
{
    function __construct()
    {
        $this->open('mysqlitedb.db');
    }
}

$db = new MyDB();

$db->exec('CREATE TABLE foo (bar STRING)');
$db->exec("INSERT INTO foo (bar) VALUES ('This is a test')");

$result = $db->query('SELECT bar FROM foo');
var_dump($result->fetchArray());
?>

SQLite3::openBlob

Opens a stream resource to read a BLOB

说明

public resourcefalse SQLite3::openBlob ( string $table , string $column , int $rowid [, string $dbname = "main" [, int $flags = SQLITE3_OPEN_READONLY ]] )

Opens a stream resource to read or write a BLOB, which would be selected by:

SELECT column FROM dbname.table WHERE rowid = rowid

Note: It is not possible to change the size of a BLOB by writing to the stream. Instead, an UPDATE statement has to be executed, possibly using SQLite's zeroblob() function to set the desired BLOB size.

参数

table
The table name.

column
The column name.

rowid
The row ID.

dbname
The symbolic name of the DB

flags
Either SQLITE3_OPEN_READONLY or SQLITE3_OPEN_READWRITE to open the stream for reading only, or for reading and writing, respectively.

返回值

Returns a stream resource, 或者在失败时返回 false.

更新日志

版本	说明
7.2.0	The `flags` parameter has been added, allowing to write BLOBs; formerly only reading was supported.

范例

示例 #1 SQLite3::openBlob example

<?php
$conn = new SQLite3(':memory:');
$conn->exec('CREATE TABLE test (text text)');
$conn->exec("INSERT INTO test VALUES ('Lorem ipsum')");
$stream = $conn->openBlob('test', 'text', 1);
echo stream_get_contents($stream);
fclose($stream); // mandatory, otherwise the next line would fail
$conn->close();
?>

以上例程会输出：

Lorem ipsum

示例 #2 Incrementally writing a BLOB

<?php
$conn = new SQLite3(':memory:');
$conn->exec('CREATE TABLE test (text text)');
$conn->exec("INSERT INTO test VALUES (zeroblob(36))");
$stream = $conn->openBlob('test', 'text', 1, 'main', SQLITE3_OPEN_READWRITE);
for ($i = 0; $i < 3; $i++) {
    fwrite($stream,  "Lorem ipsum\n");
}
fclose($stream);
echo $conn->querySingle("SELECT text FROM test");
$conn->close();
?>

以上例程会输出：

Lorem ipsum
Lorem ipsum
Lorem ipsum

SQLite3::prepare

Prepares an SQL statement for execution

说明

public SQLite3Stmtfalse SQLite3::prepare ( string $query )

Prepares an SQL statement for execution and returns an SQLite3Stmt object.

参数

query
The SQL query to prepare.

返回值

Returns an SQLite3Stmt object on success 或者在失败时返回 false.

范例

示例 #1 SQLite3::prepare example

<?php
unlink('mysqlitedb.db');
$db = new SQLite3('mysqlitedb.db');

$db->exec('CREATE TABLE foo (id INTEGER, bar STRING)');
$db->exec("INSERT INTO foo (id, bar) VALUES (1, 'This is a test')");

$stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);

$result = $stmt->execute();
var_dump($result->fetchArray());
?>

参见

SQLite3Stmt::paramCount
SQLite3Stmt::bindValue
SQLite3Stmt::bindParam

SQLite3::query

Executes an SQL query

说明

public SQLite3Resultfalse SQLite3::query ( string $query )

Executes an SQL query, returning an SQLite3Result object. If the query does not yield a result (such as DML statements) the returned SQLite3Result object is not really usable. Use SQLite3::exec for such queries instead.

参数

query
The SQL query to execute.

返回值

Returns an SQLite3Result object, 或者在失败时返回 false.

范例

示例 #1 SQLite3::query example

<?php
$db = new SQLite3('mysqlitedb.db');

$results = $db->query('SELECT bar FROM foo');
while ($row = $results->fetchArray()) {
    var_dump($row);
}
?>

SQLite3::querySingle

Executes a query and returns a single result

说明

public mixed SQLite3::querySingle ( string $query [, bool $entire_row = false ] )

Executes a query and returns a single result.

参数

query
The SQL query to execute.

entire_row
By default, querySingle returns the value of the first column returned by the query. If entire_row is true, then it returns an array of the entire first row.

返回值

Returns the value of the first column of results or an array of the entire first row (if entire_row is true).

If the query is valid but no results are returned, then null will be returned if entire_row is false, otherwise an empty array is returned.

Invalid or failing queries will return false.

范例

示例 #1 SQLite3::querySingle example

<?php
$db = new SQLite3('mysqlitedb.db');

var_dump($db->querySingle('SELECT username FROM user WHERE userid=1'));
print_r($db->querySingle('SELECT username, email FROM user WHERE userid=1', true));
?>

以上例程的输出类似于：

string(5) "Scott"
Array
(
    [username] => Scott
    [email] => [email protected]
)

SQLite3::setAuthorizer

Configures a callback to be used as an authorizer to limit what a statement can do

说明

public bool SQLite3::setAuthorizer ( callablenull $callback )

Sets a callback that will be called by SQLite every time an action is performed (reading, deleting, updating, etc.). This is used when preparing a SQL statement from an untrusted source to ensure that the SQL statements do not try to access data they are not allowed to see, or that they do not try to execute malicious statements that damage the database. For example, an application may allow a user to enter arbitrary SQL queries for evaluation by a database. But the application does not want the user to be able to make arbitrary changes to the database. An authorizer could then be put in place while the user-entered SQL is being prepared that disallows everything except SELECT statements.

The authorizer callback may be called multiple times for each statement prepared by SQLite. A SELECT or UPDATE query will call the authorizer for every column that would be read or updated.

The authorizer is called with up to five parameters. The first parameter is always given, and is an int (action code) matching a constant from SQLite3. The other parameters are only passed for some actions. The following table describes the second and third parameters according to the action:

Action	Second parameter	Third parameter
`SQLite3::CREATE_INDEX`	Index Name	Table Name
`SQLite3::CREATE_TABLE`	Table Name	`null`
`SQLite3::CREATE_TEMP_INDEX`	Index Name	Table Name
`SQLite3::CREATE_TEMP_TABLE`	Table Name	`null`
`SQLite3::CREATE_TEMP_TRIGGER`	Trigger Name	Table Name
`SQLite3::CREATE_TEMP_VIEW`	View Name	`null`
`SQLite3::CREATE_TRIGGER`	Trigger Name	Table Name
`SQLite3::CREATE_VIEW`	View Name	`null`
`SQLite3::DELETE`	Table Name	`null`
`SQLite3::DROP_INDEX`	Index Name	Table Name
`SQLite3::DROP_TABLE`	Table Name	`null`
`SQLite3::DROP_TEMP_INDEX`	Index Name	Table Name
`SQLite3::DROP_TEMP_TABLE`	Table Name	`null`
`SQLite3::DROP_TEMP_TRIGGER`	Trigger Name	Table Name
`SQLite3::DROP_TEMP_VIEW`	View Name	`null`
`SQLite3::DROP_TRIGGER`	Trigger Name	Table Name
`SQLite3::DROP_VIEW`	View Name	`null`
`SQLite3::INSERT`	Table Name	`null`
`SQLite3::PRAGMA`	Pragma Name	First argument passed to the pragma, or `null`
`SQLite3::READ`	Table Name	Column Name
`SQLite3::SELECT`	`null`	`null`
`SQLite3::TRANSACTION`	Operation	`null`
`SQLite3::UPDATE`	Table Name	Column Name
`SQLite3::ATTACH`	Filename	`null`
`SQLite3::DETACH`	Database Name	`null`
`SQLite3::ALTER_TABLE`	Database Name	Table Name
`SQLite3::REINDEX`	Index Name	`null`
`SQLite3::ANALYZE`	Table Name	`null`
`SQLite3::CREATE_VTABLE`	Table Name	Module Name
`SQLite3::DROP_VTABLE`	Table Name	Module Name
`SQLite3::FUNCTION`	`null`	Function Name
`SQLite3::SAVEPOINT`	Operation	Savepoint Name
`SQLite3::RECURSIVE`	`null`	`null`

The 5th parameter will be the name of the database ("main", "temp", etc.) if applicable.

The 6th parameter to the authorizer callback is the name of the inner-most trigger or view that is responsible for the access attempt or null if this access attempt is directly from top-level SQL code.

When the callback returns SQLite3::OK, that means the operation requested is accepted. When the callback returns SQLite3::DENY, the call that triggered the authorizer will fail with an error message explaining that access is denied.

If the action code is SQLite3::READ and the callback returns SQLite3::IGNORE then the prepared statement statement is constructed to substitute a null value in place of the table column that would have been read if SQLite3::OK had been returned. The SQLite3::IGNORE return can be used to deny an untrusted user access to individual columns of a table.

When a table is referenced by a SELECT but no column values are extracted from that table (for example in a query like "SELECT count(*) FROM table") then the SQLite3::READ authorizer callback is invoked once for that table with a column name that is an empty string.

If the action code is SQLite3::DELETE and the callback returns SQLite3::IGNORE then the DELETE operation proceeds but the truncate optimization is disabled and all rows are deleted individually.

Only a single authorizer can be in place on a database connection at a time. Each call to SQLite3::setAuthorizer overrides the previous call. Disable the authorizer by installing a null callback. The authorizer is disabled by default.

The authorizer callback must not do anything that will modify the database connection that invoked the authorizer callback.

Note that the authorizer is only called when a statement is prepared, not when it's executed.

More details can be found in the » SQLite3 documentation.

参数

callback
The callable to be called.

If null is passed instead, this will disable the current authorizer callback.

返回值

成功时返回 true，或者在失败时返回 false。

错误／异常

This method doesn't throw any error, but if an authorizer is enabled and returns an invalid value, the statement preparation will throw an error (or exception, depending on the use of the SQLite3::enableExceptions method).

范例

示例 #1 SQLite3::setAuthorizer example

This only allows access to reading, and only some columns of the users table will be returned. Other columns will be replaced with null.

<?php
$db = new SQLite3('data.sqlite');
$db->exec('CREATE TABLE users (id, name, password);');
$db->exec('INSERT INTO users VALUES (1, \'Pauline\', \'Snails4eva\');');

$allowed_columns = ['id', 'name'];

$db->setAuthorizer(function (int $action, ...$args) use ($allowed_columns) {
    if ($action === SQLite3::READ) {
        list($table, $column) = $args;

        if ($table === 'users' && in_array($column, $allowed_columns) {
            return SQLite3::OK;
        }

        return SQLite3::IGNORE;
    }

    return SQLite3::DENY;
});

print_r($db->querySingle('SELECT * FROM users WHERE id = 1;'));

以上例程会输出：

Array
(
    [id] => 1
    [name] => Pauline
    [password] =>
)

SQLite3::version

Returns the SQLite3 library version as a string constant and as a number

说明

public static array SQLite3::version ( void )

Returns the SQLite3 library version as a string constant and as a number.

参数

此函数没有参数。

返回值

Returns an associative array with the keys "versionString" and "versionNumber".

范例

示例 #1 SQLite3::version example

<?php
print_r(SQLite3::version());
?>

以上例程的输出类似于：

Array
(
    [versionString] => 3.5.9
    [versionNumber] => 3005009
)

简介

处理 SQLite 3 扩展语句模板的类。

类摘要

SQLite3Stmt

class SQLite3Stmt {

/* 方法 */

public bool bindParam ( mixed $sql_param , mixed &$param [, int $type ] )

public bool bindValue ( mixed $sql_param , mixed $value [, int $type ] )

public bool clear ( void )

public bool close ( void )

public SQLite3Result execute ( void )

public stringfalse getSQL ([ bool $expanded = false ] )

public int paramCount ( void )

public bool readOnly ( void )

public bool reset ( void )

}

SQLite3Stmt::bindParam

Binds a parameter to a statement variable

说明

public bool SQLite3Stmt::bindParam ( mixed $sql_param , mixed &$param [, int $type ] )

Binds a parameter to a statement variable.

Caution

Before PHP 7.2.14 and 7.3.0, respectively, SQLite3Stmt::reset must be called after the first call to SQLite3Stmt::execute if the bound value should be properly updated on following calls to SQLite3Stmt::execute. If SQLite3Stmt::reset is not called, the bound value will not change, even if the value assigned to the variable passed to SQLite3Stmt::bindParam has changed, or SQLite3Stmt::bindParam has been called again.

参数

sql_param
Either a string (for named parameters) or an int (for positional parameters) identifying the statement variable to which the value should be bound. If a named parameter does not start with a colon (:) or an at sign (@), a colon (:) is automatically preprended. Positional parameters start with 1.

param
The parameter to bind to a statement variable.

type
The data type of the parameter to bind.

SQLITE3_INTEGER: The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
SQLITE3_FLOAT: The value is a floating point value, stored as an 8-byte IEEE floating point number.
SQLITE3_TEXT: The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
SQLITE3_BLOB: The value is a blob of data, stored exactly as it was input.
SQLITE3_NULL: The value is a NULL value.

As of PHP 7.0.7, if type is omitted, it is automatically detected from the type of the param: bool and int are treated as SQLITE3_INTEGER, float as SQLITE3_FLOAT, null as SQLITE3_NULL and all others as SQLITE3_TEXT. Formerly, if type has been omitted, it has defaulted to SQLITE3_TEXT.

Note:

If param is null, it is always treated as SQLITE3_NULL, regardless of the given type.

返回值

Returns true if the parameter is bound to the statement variable, false on failure.

更新日志

版本	说明
7.4.0	`sql_param` now also supports the @param notation.

范例

示例 #1 SQLite3Stmt::bindParam Usage

This example shows how a single prepared statement with a single parameter binding can be used to insert multiple rows with different values.

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE foo (bar TEXT)");

$stmt = $db->prepare("INSERT INTO foo VALUES (:bar)");
$stmt->bindParam(':bar', $bar, SQLITE3_TEXT);

$bar = 'baz';
$stmt->execute();

$bar = 42;
$stmt->execute();

$res = $db->query("SELECT * FROM foo");
while (($row = $res->fetchArray(SQLITE3_ASSOC))) {
    var_dump($row);
}
?>

以上例程会输出：

array(1) {
  ["bar"]=>
  string(3) "baz"
}
array(1) {
  ["bar"]=>
  string(2) "42"
}

参见

SQLite3Stmt::bindValue
SQLite3::prepare

SQLite3Stmt::bindValue

Binds the value of a parameter to a statement variable

说明

public bool SQLite3Stmt::bindValue ( mixed $sql_param , mixed $value [, int $type ] )

Binds the value of a parameter to a statement variable.

Caution

Before PHP 7.2.14 and 7.3.0, respectively, once the statement has been executed, SQLite3Stmt::reset needs to be called to be able to change the value of bound parameters.

参数

value
The value to bind to a statement variable.

type
The data type of the value to bind.

SQLITE3_INTEGER: The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
SQLITE3_FLOAT: The value is a floating point value, stored as an 8-byte IEEE floating point number.
SQLITE3_TEXT: The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
SQLITE3_BLOB: The value is a blob of data, stored exactly as it was input.
SQLITE3_NULL: The value is a NULL value.

As of PHP 7.0.7, if type is omitted, it is automatically detected from the type of the value: bool and int are treated as SQLITE3_INTEGER, float as SQLITE3_FLOAT, null as SQLITE3_NULL and all others as SQLITE3_TEXT. Formerly, if type has been omitted, it has defaulted to SQLITE3_TEXT.

Note:

If value is null, it is always treated as SQLITE3_NULL, regardless of the given type.

返回值

Returns true if the value is bound to the statement variable, 或者在失败时返回 false.

更新日志

版本	说明
7.4.0	`sql_param` now also supports the @param notation.

范例

示例 #1 SQLite3Stmt::bindValue example

<?php
$db = new SQLite3(':memory:');

$db->exec('CREATE TABLE foo (id INTEGER, bar STRING)');
$db->exec("INSERT INTO foo (id, bar) VALUES (1, 'This is a test')");

$stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);

$result = $stmt->execute();
var_dump($result->fetchArray(SQLITE3_ASSOC));
?>

以上例程会输出：

array(1) {
  ["bar"]=>
  string(14) "This is a test"
}

参见

SQLite3Stmt::bindParam
SQLite3::prepare

SQLite3Stmt::clear

Clears all current bound parameters

说明

public bool SQLite3Stmt::clear ( void )

Clears all current bound parameters (sets them to null).

Caution

This method needs to be used with SQLite3Stmt::reset. If used alone, any call to SQLite3Stmt::bindValue or SQLite3Stmt::bindParam will be of no effect and all bound parameters will have the null value.

参数

此函数没有参数。

返回值

Returns true on successful clearing of bound parameters, false on failure.

SQLite3Stmt::close

Closes the prepared statement

说明

public bool SQLite3Stmt::close ( void )

Closes the prepared statement.

Note: Note that all SQLite3Results that have been retrieved by executing this statement will be invalidated when the statement is closed.

参数

此函数没有参数。

返回值

Returns true

SQLite3Stmt::execute

Executes a prepared statement and returns a result set object

说明

public SQLite3Result SQLite3Stmt::execute ( void )

Executes a prepared statement and returns a result set object.

Caution

Result set objects retrieved by calling this method on the same statement object are not independent, but rather share the same underlying structure. Therefore it is recommended to call SQLite3Result::finalize, before calling SQLite3Stmt::execute on the same statement object again.

参数

此函数没有参数。

返回值

Returns an SQLite3Result object on successful execution of the prepared statement, false on failure.

参见

SQLite3::prepare
SQLite3Stmt::bindValue
SQLite3Stmt::bindParam

SQLite3Stmt::getSQL

Get the SQL of the statement

说明

public stringfalse SQLite3Stmt::getSQL ([ bool $expanded = false ] )

Retrieves the SQL of the prepared statement. If expanded is false, the unmodified SQL is retrieved. If expanded is true, all query parameters are replaced with their bound values, or with an SQL NULL, if not already bound.

参数

expanded
Whether to retrieve the expanded SQL. Passing true is only supported as of libsqlite 3.14.

返回值

Returns the SQL of the prepared statement, 或者在失败时返回 false.

错误／异常

If expanded is true, but the libsqlite version is less than 3.14, an error of level E_WARNING or an Exception is issued, according to SQLite3::enableExceptions.

范例

示例 #1 Inspecting the expanded SQL

<?php
$db = new SQLite3(':memory:');
$stmt = $db->prepare("SELECT :a, ?, :c");
$stmt->bindValue(':a', 'foo');
$answer = 42;
$stmt->bindParam(2, $answer);
var_dump($stmt->getSQL(true));
?>

以上例程的输出类似于：

string(24) "SELECT 'foo', '42', NULL"

SQLite3Stmt::paramCount

Returns the number of parameters within the prepared statement

说明

public int SQLite3Stmt::paramCount ( void )

Returns the number of parameters within the prepared statement.

参数

此函数没有参数。

返回值

Returns the number of parameters within the prepared statement.

参见

SQLite3::prepare

SQLite3Stmt::readOnly

Returns whether a statement is definitely read only

说明

public bool SQLite3Stmt::readOnly ( void )

Returns whether a statement is definitely read only. A statement is considered read only, if it makes no direct changes to the content of the database file. Note that user defined SQL functions might change the database indirectly as a side effect.

参数

此函数没有参数。

返回值

Returns true if a statement is definitely read only, false otherwise.

SQLite3Stmt::reset

Resets the prepared statement

说明

public bool SQLite3Stmt::reset ( void )

Resets the prepared statement to its state prior to execution. All bindings remain intact after reset.

参数

此函数没有参数。

返回值

Returns true if the statement is successfully reset, 或者在失败时返回 false.

简介

处理 SQLite 3 扩展返回结果集的类。

类摘要

SQLite3Result

class SQLite3Result {

/* 方法 */

public string columnName ( int $column_number )

public int columnType ( int $column_number )

public array fetchArray ([ int $mode = SQLITE3_BOTH ] )

public bool finalize ( void )

public int numColumns ( void )

public bool reset ( void )

}

SQLite3Result::columnName

Returns the name of the nth column

说明

public string SQLite3Result::columnName ( int $column_number )

Returns the name of the column specified by the column_number. Note that the name of a result column is the value of the AS clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of libsqlite3 to the next.

参数

column_number
The numeric zero-based index of the column.

返回值

Returns the string name of the column identified by column_number.

SQLite3Result::columnType

Returns the type of the nth column

说明

public int SQLite3Result::columnType ( int $column_number )

Returns the type of the column identified by column_number.

参数

column_number
The numeric zero-based index of the column.

返回值

Returns the data type index of the column identified by column_number (one of SQLITE3_INTEGER, SQLITE3_FLOAT, SQLITE3_TEXT, SQLITE3_BLOB, or SQLITE3_NULL).

SQLite3Result::fetchArray

Fetches a result row as an associative or numerically indexed array or both

说明

public array SQLite3Result::fetchArray ([ int $mode = SQLITE3_BOTH ] )

Fetches a result row as an associative or numerically indexed array or both. By default, fetches as both.

参数

mode
Controls how the next row will be returned to the caller. This value must be one of either SQLITE3_ASSOC, SQLITE3_NUM, or SQLITE3_BOTH.

SQLITE3_ASSOC: returns an array indexed by column name as returned in the corresponding result set
SQLITE3_NUM: returns an array indexed by column number as returned in the corresponding result set, starting at column 0
SQLITE3_BOTH: returns an array indexed by both column name and number as returned in the corresponding result set, starting at column 0

返回值

Returns a result row as an associatively or numerically indexed array or both. Alternately will return false if there are no more rows.

The types of the values of the returned array are mapped from SQLite3 types as follows: integers are mapped to int if they fit into the range PHP_INT_MIN..PHP_INT_MAX, and to string otherwise. Floats are mapped to float, NULL values are mapped to null, and strings and blobs are mapped to string.

SQLite3Result::finalize

Closes the result set

说明

public bool SQLite3Result::finalize ( void )

Closes the result set.

参数

此函数没有参数。

返回值

Returns true.

SQLite3Result::numColumns

Returns the number of columns in the result set

说明

public int SQLite3Result::numColumns ( void )

Returns the number of columns in the result set.

参数

此函数没有参数。

返回值

Returns the number of columns in the result set.

SQLite3Result::reset

Resets the result set back to the first row

说明

public bool SQLite3Result::reset ( void )

Resets the result set back to the first row.

参数

此函数没有参数。

返回值

Returns true if the result set is successfully reset back to the first row, false on failure.