ufee/sqlite3

Sqlite3 PHP类（https://www.sqlite.org/）

维护者

详细信息

github.com/ufee/Sqlite3

主页

源代码

问题

安装数: 1,733

依赖: 1

建议者: 0

安全性: 0

星星: 12

关注者: 0

分支: 4

1.0.2 2023-07-18 10:27 UTC

Requires

  • php: >=5.6

Requires (Dev)

MIT 513f9dee69b8bd1edc7907eba4c1aa97a41fe5c3

  • Vlad Ionov <vlad.woop@ufee.ru>

This package is auto-updated.

Last update: 2024-09-18 13:19:57 UTC

README

要正常工作，需要支持 SQLITE_ENABLE_UPDATE_DELETE_LIMIT 的 Sqlite3 版本
已测试版本 3.11.0

安装

composer require ufee/sqlite3

结构

数据库对象

\Ufee\Sqlite3\Database;
$db = \Ufee\Sqlite3\Sqlite::database(string $path, array $options = [
	'flags' => SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE,
	'encryption_key' => null,
	'busy_timeout' => 15,
	'journal_mode' => 'WAL',
	'synchronous' => 'NORMAL',
	'exceptions' => true
]);

表对象

\Ufee\Sqlite3\Table;
$table = $db->table(string $name);

查询对象

\Ufee\Sqlite3\Query\Insert;
$insert = $table->insert(array $columns);

\Ufee\Sqlite3\Query\Select;
$select = $table->select(array $columns);

\Ufee\Sqlite3\Query\Update;
$update = $table->update(array $columns);

\Ufee\Sqlite3\Query\Delete;
$delete = $table->delete();

查询集合对象

\Ufee\Sqlite3\Queries;
$queries = $db->queries();
$queries = $table->queries();

查询调试

$db->queries()->listen(function($data) {
	echo 'Table: '.$data['table']."\n";
	echo '  Sql: '.$data['sql']."\n";
	echo ' Time: '.$data['time']."\n";
});

使用类

获取数据库对象

$db = Sqlite::database('path/to/file.db');

$temp_db_path = tempnam(sys_get_temp_dir(), 'Sqlite3');
$db = Sqlite::database($temp_db_path);

$db = Sqlite::database(':memory:');

检查数据库是否存在并创建

if (!$db->exists()) {
	$db->create();
}

或者检查文件是否存在（更快）

if (!$db->fileExists()) {
	$db->create();
}

打开数据库连接
在实际情况中不需要，自动执行

$db->open();

执行任意查询和命令

$result = $db->query($query); // return Query\Result
$rows = $result->getRows($mode = SQLITE3_ASSOC);

$result = $db->single($query, $entire = true); // return array
$result = $db->exec($command); // return bool
$result = $db->pragma($key, $val); // return bool

关闭数据库连接

$db->close();

获取表对象

$tables = $db->tables();
$table = $db->table('test_table');

检查存在并创建

if (!$table->exists()) {
	// с указанием типов данных
	$table->create([
		'id' => 'INTEGER PRIMARY KEY', 
		'amount' => 'REAL', 
		'data1' => 'TEXT', 
		'data2' => 'BLOB', 
		'other' => 'INTEGER DEFAULT 5'
	]);
	// или без привязки к типу данных
	$table->create([
		'id, 
		'data', 
		'other'
	]);
}

获取表信息

$info = $table->info($key = null);
// [type, name, tbl_name, rootpage, sql]

获取列信息

$culumns = $table->columns($name = null);
// [name => [cid, name, type, notnull, dflt_value, pk]]

为后续查询指定列的数据类型

$table->setColumnType(string $name, string $type); // integer, real, text, blob, null
$table->setColumnType('category', 'integer');
$table->setColumnType('title', 'text');

删除表

$table->drop();

查询示例

查询使用预处理语句执行（自动）。
条件运算符：[=|>|<|<=|>=|!=|BETWEEN|NOT BETWEEN|IN|NOT IN|LIKE|NOT LIKE|GLOB|NOT GLOB]
以下示例创建数据库和表

$db = \Ufee\Sqlite3\Sqlite::database(
	tempnam(sys_get_temp_dir(), 'Sqlite3')
);
$goods = $db->table('goods');
$meta = $db->table('goods_meta');

if (!$goods->exists()) {
	$goods->create([
		'id' => 'INTEGER PRIMARY KEY', 
		'category' => 'INTEGER DEFAULT 1', 
		'price' => 'REAL DEFAULT 0.00', 
		'title' => 'TEXT', 
		'hot' => 'INTEGER DEFAULT NULL',
		'created_at' => 'INTEGER DEFAULT (DATETIME(\'now\'))'
	]);
	$meta->create([
		'good_id' => 'INTEGER UNIQUE', 
		'sale' => 'REAL DEFAULT 0.00',
		'descr' => 'TEXT', 
		'star' => 'INTEGER DEFAULT 0', 
	]);
}

Insert查询

$insert = $table->insert('id, category, data')
	->orRollback()
	->orAbort() // is default 
	->orFail()
	->orIgnore()
	->orRreplace();
$insert->rows(array $rows); // return bool
// or
$insert->row(array $row); // return bool|integer

插入一行数据

$insert = $goods->insert('category, price, title');

$increment_id = $insert->orFail()->row([2, 7299.50, 'Notebook Pro']);
// INSERT OR FAIL INTO goods (category, price, title) VALUES (..., ..., ...)

$meta->insert([
	'good_id' => $increment_id, 
	'descr' => 'Professional device',
	'star' => 4
]);
// INSERT INTO goods_meta (good_id, descr, star) VALUES (..., ..., ...)

$increment_id =$insert->orAbort()->row([2, 6999.70, 'Notebook Adv']);
// INSERT OR ABORT INTO goods (category, price, title) VALUES (..., ..., ...)

$meta->insert([
	'good_id' => $increment_id, 
	'descr' => 'Advanced device',
	'star' => 5
]);
// INSERT INTO goods_meta (good_id, descr, star) VALUES (..., ..., ...)

$increment_id = $goods->insert([
	'category' => 3, 
	'title' => 'Mobile Pro',
	'price' => 3799.90
]);
// INSERT INTO goods (category, price, title) VALUES (..., ..., ...)

$meta->insert([
	'good_id' => $increment_id, 
	'sale' => 499.90,
	'descr' => 'Professional device',
	'star' => 3
]);
// INSERT INTO goods_meta (good_id, descr, star) VALUES (..., ..., ..., ....)

插入多行数据

$insert = $goods->insert('category, title');
$result = $insert->rows([
	[4, 'TV 1000'],
	[4, 'TV 2000'],
	[4, 'TV 3000']
]);
// INSERT INTO goods (category, title) VALUES (..., ...), (..., ...), (..., ...)

Select查询

$select = $table->select()
	->distinct() // for unique rows
	->where($column, $value = false, $operator = '=')
	->orWhere($column, $value = false, $operator = '=')
	->short($short_table_name)
	->join($join_table_name, $on, $type = '')
	->leftJoin($join_table_name, $on)
	->innerJoin($join_table_name, $on)
	->groupBy($columns)
	->having($column, $value = false, $operator = '=')
	->orHaving($column, $value = false, $operator = '=')
	->orderBy($column, $by = 'DESC');
$count = $select->count(); // return integer
$row = $select->row($column = null); // return array row or string|integer column value
// or
$rows = $select->rows($limit = null, $offset = null); // return array

任意条件（不使用预处理语句）

$select->where('id != another OR ...')
$select->having('other > another AND ...')

获取行数

$count = $goods->select()->count();
// SELECT COUNT(*) as rows_count FROM goods LIMIT 1

获取行数和满足条件的行数

$select = $goods->select()
	->where('id', 1, '>')
	->orderBy('hot');
$count = $select->count();
// SELECT COUNT(*) as rows_count FROM goods WHERE id > ... ORDER BY hot DESC LIMIT 1
$rows = $select->rows(3);
// SELECT * FROM goods WHERE id > ... ORDER BY hot DESC LIMIT 3

$select = $goods->select('id, category, title')
	->where('hot', null, 'IS NOT')
	->orderBy('hot');
$rows = $select->rows(2,2);
// SELECT id,category,title FROM goods WHERE hot IS NOT NULL ORDER BY hot DESC LIMIT 2 OFFSET 2

获取单行数据

$select = $goods->select()->where('id', 1);
$row = $select->row();
// SELECT * FROM goods WHERE id = ... LIMIT 1

从单行获取单个值

$select = $goods->select('title')->where('id', 1);
$title = $select->row('title');
// SELECT * FROM goods WHERE id = ... LIMIT 1

使用JOIN获取数据

$select = $goods->select('g.id, g.category, g.title, m.descr, m.sale, g.hot, m.star, g.created_at')
	->short('g')->innerJoin('goods_meta AS m', 'm.good_id=g.id')
	->where('g.category', [1,2,3,4,5], 'IN')
	->where('m.star', 1, '>')
	->orderBy('m.star');
$count = $select->count();
// SELECT COUNT(*) as rows_count FROM goods AS g INNER JOIN goods_meta AS m ON m.good_id=g.id WHERE g.category IN (...,...,...,...,...) AND m.star > ... ORDER BY m.star DESC LIMIT 1
$rows = $select->rows();
// SELECT g.id,g.category,g.title,m.descr,m.sale,g.hot,m.star,g.created_at FROM goods AS g INNER JOIN goods_meta AS m ON m.good_id=g.id WHERE g.category IN (...,...,...,...,...) AND m.star > ... ORDER BY m.star DESC

Update查询

$update = $table->update('category, data')
	->where($column, $value = false, $operator = '=')
	->orWhere($column, $value = false, $operator = '=')
	->orderBy($column, $by = 'DESC')
	->set([$category, $data]);
$update->rows($limit = null, $offset = null); return integer changed rows
// or
$update->row(); return bool

更新一行数据

$update = $goods->update('price, title, hot')
	->where('id', 1)
	->set([6950.10, 'Notebook Pro (hot)', 1]);
$update->row();
// UPDATE goods SET price=..., title = ..., hot = ... WHERE id = ... LIMIT 1

更新多行数据

$update = $goods->update('hot')
	->where('category', 3)
	->orWhere('price', 5000, '<')
	->set(1);
$update->rows();
// UPDATE goods SET hot = ... WHERE category = ... OR price < ...
$update->rows(3,2);
// UPDATE goods SET hot = ... WHERE category = ... OR price < ... LIMIT 3 OFFSET 2

Delete查询

$delete = $table->delete()
	->where($column, $value = false, $operator = '=')
	->orWhere($column, $value = false, $operator = '=')
	->orderBy($column, $by = 'DESC');
$delete->rows($limit = null, $offset = null); return integer changed rows
// or
$delete->row(); return bool

删除一行数据

$result = $goods->delete()->where('id', 5)->row();
// DELETE FROM goods WHERE id = ... LIMIT 1

$delete = $goods->delete()
	->where('category', 4)
	->orderBy('id');
$result = $delete->row();
// DELETE FROM goods WHERE category = ... ORDER BY id DESC LIMIT 1

删除多行数据

$delete = $goods->delete()
	->where('category', 4)
	->orderBy('id');
$delete->rows(3);
// DELETE FROM goods WHERE category = ... ORDER BY id DESC LIMIT 3
$delete->rows(3, 3);
// DELETE FROM goods WHERE category = ... ORDER BY id DESC LIMIT 3 OFFSET 3
$delete->rows(3, 6);
// DELETE FROM goods WHERE category = ... ORDER BY id DESC LIMIT 3 OFFSET 6

事务

// DEFERRED|IMMEDIATE|EXCLUSIVE
$table->database()->transactionBegin($type = 'DEFERRED', $name = '');
$table->database()->transactionCommit($name = '');
$table->database()->transactionRollback($name = '');
$table->database()->transactionEnd($name = '');

使用事务插入数据

$insert = $goods->insert('category, title');
$goods->database()->transactionBegin('IMMEDIATE');
// BEGIN IMMEDIATE TRANSACTION
$insert->row([5, 'PC Intel']);
// INSERT INTO goods (category, title) VALUES (..., ...)
$insert->row([5, 'PC AMD']);
// INSERT INTO goods (category, title) VALUES (..., ...)
$insert->row([5, 'PC Intel']);
// INSERT INTO goods (category, title) VALUES (..., ...)
$goods->database()->transactionCommit();
// COMMIT TRANSACTION