README

🍗 dbhelper 🍗

dbhelper 是 mysql/postgres/sqlite 数据库的小型 PHP 包装器。

安装

使用 composer 安装一次

composer require vielhuber/dbhelper

然后将其添加到您的项目中

require __DIR__ . '/vendor/autoload.php';
use vielhuber\dbhelper\dbhelper;
$db = new dbhelper();

使用方法

/* connect to database */
$db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', 'database', 3306);
$db->connect('pdo', 'postgres', '127.0.0.1', 'username', 'password', 'database', 5432);
$db->connect('pdo', 'sqlite', 'database.db');
$db->connect('pdo', 'sqlite', 'database.db', null, null, null, null, 120); // specify a manual timeout of 120 seconds
$db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', null, 3306); // database must not be available

/* disconnect from database */
$db->disconnect();

/* insert/update/delete */
$id = $db->insert('tablename', ['col1' => 'foo']);
$db->update('tablename', ['col1' => 'bar'], ['id' => $id]);
$db->delete('tablename', ['id' => $id]);

/* select */
$db->fetch_all('SELECT * FROM tablename WHERE name = ? AND number > ?', 'foo', 42);
$db->fetch_row('SELECT * FROM tablename WHERE ID = ?', 1);
$db->fetch_col('SELECT col FROM tablename WHERE ID > ?', 1);
$db->fetch_var('SELECT col FROM tablename WHERE ID = ?', 1);

/* count */
$db->count('tablename') // 42
$db->count('tablename', ['col1' => 'foo']) // 7

/* automatic flattened arguments */
$db->fetch_all('SELECT * FROM tablename WHERE ID = ?', [1], 2, [3], [4,[5,6]]);
// gets transformed to
$db->fetch_all('SELECT * FROM tablename WHERE ID = ?', 1, 2, 3, 4, 5, 6);

/* automatic in-expansion */
$db->fetch_all('SELECT * FROM tablename WHERE col1 = ? AND col2 IN (?)', 1, [2,3,4]);

/* support for null values */
$db->query('UPDATE tablename SET col1 = ? WHERE col2 = ? AND col3 != ?', null, null, null);
// gets transformed to
$db->query('UPDATE tablename SET col1 = NULL WHERE col2 IS NULL AND col3 IS NOT NULL');

/* clean up */
$db->clear(); // delete all tables (without dropping the whole database)
$db->clear('tablename'); // delete all rows in a table

/* delete table */
$db->delete_table('tablename');

/* create table */
$db->create_table('tablename', [
    'id' => 'SERIAL PRIMARY KEY', // use INTEGER instead of SERIAL on sqlite to get auto ids
    'col1' => 'varchar(255)',
    'col2' => 'varchar(255)',
    'col3' => 'varchar(255)'
]);

/* create if not exists and connect to database */
$db->connect_with_create('pdo', 'mysql', '127.0.0.1', 'username', 'password', 'database', 3306);
    // this is a shorthand for
    $db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', null, 3306);
    $db->create_database('database');
    $db->disconnect();
    $db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', 'database', 3306);

/* delete database */
$db->disconnect_with_delete();
    // this is a shorthand for
    $db->disconnect();
    $db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', null, 3306);
    $db->delete_database('database');
    $db->disconnect();

/* raw queries */
$db->query('INSERT INTO tablename(row1, row2) VALUES(?, ?, ?)', 1, 2, 3);
$db->query('UPDATE tablename SET row1 = ? WHERE ID = ?', 1, 2);
$db->query('DELETE FROM tablename WHERE ID = ?', 1);

/* quickly debug raw queries */
$db->debug('DELETE FROM tablename WHERE row1 = ?', null); // DELETE FROM tablename WHERE row1 IS NULL

/* last insert id */
$db->insert('tablename', ['col1' => 'foo']);
$db->last_insert_id();

/* some more little helpers */
$db->get_tables() // ['tablename', ...]
$db->has_table('tablename') // true
$db->get_columns('tablename') // ['col1', 'col2', ...]
$db->has_column('tablename', 'col1') // true
$db->get_datatype('tablename', 'col1') // varchar
$db->get_primary_key('tablename') // id
$db->uuid() // generate uuid (v4) from inside the database
$db->get_foreign_keys('users') // [['address_id' => ['addresses','id'], ...]
$db->is_foreign_key('users', 'address_id') // true
$db->get_foreign_tables_out('users') // [['addresses' => [['address_id','id']], ...]
$db->get_foreign_tables_in('addresses') // [['users' => [['address_id','id']], ...]

/* handle duplicates */
$db->get_duplicates() // ['count' => ['tbl1' => 3, 'tbl2' => 17], 'data' => ['tbl1' => [...], 'tbl2' => [...]]
$db->delete_duplicates('tablename') // delete duplicates based on all columns except the primary key
$db->delete_duplicates('tablename', ['common_col1','common_col1','common_col1']) // based on specific columns
$db->delete_duplicates('tablename', ['common_col1','common_col1','common_col1'], false) // null values are considered equal by default; you can disable this untypical behaviour for sql with "false"
$db->delete_duplicates('tablename', ['common_col1','common_col1','common_col1'], true, ['id' => 'asc']) // keep row with lowest primary key "id" (normally this is 'id' => 'desc')
$db->delete_duplicates('tablename', ['common_col1','common_col1','common_col1'], true, ['id' => 'asc'], false) // case insensitive match (normally this is case sensitive)

/* globally trim values */
$db->trim_values() // [['table' => 'tbl1', 'column' => 'col1', 'id' => 1, 'before' => ' foo', 'after' => 'foo'], ...]
$db->trim_values(false) // by default trim_values does a dry run (no updates)
$db->trim_values(true) // do real updates
$db->trim_values(false, ['table1', 'table2' => ['col1', 'col2']]) // ignore tables and columns

/* batch functions (they create only one query) */
$db->insert('tablename', [
    ['id' => 1, 'name' => 'foo1'],
    ['id' => 2, 'name' => 'foo2'],
    ['id' => 3, 'name' => 'foo3']
]);
$db->delete('tablename', [
    ['id' => 1],
    ['id' => 7],
    ['id' => 42]
]);
$db->update('tablename', [
    [['col1' => 'var1', 'col2' => 1], ['id' => 1, 'key' => '1']],
    [['col1' => 'var2', 'col2' => 2], ['id' => 2, 'key' => '2']],
    [['col1' => 'var3', 'col2' => 3], ['id' => 3, 'key' => '3']]
]);
/*
this generates the following query:
UPDATE tablename SET
col1 = CASE WHEN (id = 1 AND key = '1') THEN 'var1' WHEN (id = 2 AND key = '2') THEN 'var2' WHEN (id = 3 AND key = '3') THEN 'var3' END,
col2 = CASE WHEN (id = 1 AND key = '1') THEN 1 WHEN (id = 2 AND key = '2') THEN 2 WHEN (id = 3 AND key = '3') THEN 3 END
WHERE id IN (1,2,3) AND key IN ('1','2','3');
*/

日志记录

dbhelper 可以在 mysql/postgres 数据库上设置成熟的日志系统。

$db = new dbhelper([
    'logging_table' => 'logs',
    'exclude' => [
        'tables' => ['table1'],
        'columns' => ['table2' => ['col1', 'col2', 'col3']]
    ],
    'delete_older' => 12, // months
    'updated_by' => get_current_user_id()
]);
$db->connect('...');
$db->setup_logging();

setup_logging() 执行以下四个操作

• 创建一个日志表（如果不存在的话）
• 将单个列 updated_by 添加到数据库中的每个表中（如果不存在的话）
• 为所有插入/更新/删除事件创建触发器（如果不存在的话）
• 基于 delete_older 选项删除旧日志条目

您应该在架构更改后（例如在迁移中）运行此方法，并且也可以通过 cron 每日运行它。建议排除 blob/bytea 列。

日志表具有以下架构

• id：该单个更改的唯一标识符
• log_event：插入/更新/删除
• log_table：修改行的表名
• log_key：修改行的键
• log_column：修改行的列
• log_value：修改行的值
• log_uuid：该行更改的唯一标识符
• updated_by：谁进行了更改
• updated_at：事件的时间和日期

现在我们必须调整我们的查询。 updated_by 必须在所有插入/更新查询中由网络应用程序填充，并且在我们执行删除查询之前，必须手动填充我们的日志表

$db->insert('tablename', ['col1' => 'foo', 'updated_by' => get_current_user_id()]);

$db->update('tablename', ['col1' => 'foo', 'updated_by' => get_current_user_id()], ['id' => 42]);

$db->insert('logs', [
    'log_event' => 'delete',
    'log_table' => 'tablename',
    'log_key' => 42,
    'log_uuid' => $db->uuid(),
    'updated_by' => get_current_user_id()
]);
$db->delete('tablename', ['id' => 42]);

我们可以让 dbhelper 自动为我们处理每个插入/更新/删除的重活

$db->enable_auto_inject();

dbhelper 然后自动在所有插入/更新语句中注入 updated_by 列，并在每个删除查询之前插入一条日志条目（处理所有查询，即使是那些通过 $db->query 发送的查询）。

重要提示：如果我们在外部操作数据，触发器也会工作，但 updated_by 中的值可能不准确。这对于删除语句尤其如此（它们也可以在没有手动插入查询的情况下工作）。

调用以下辅助函数，如果您（暂时）需要通过触发器禁用日志记录

$db->disable_logging();
$db->query('DELETE * FROM mega_big_table');
$db->enable_logging();

就这样 – 开心记录。

WordPress 支持

这同样适用于 WordPress（在底层使用 wpdb、预处理语句和 stripslashes_deep）

$db->connect('wordpress');
$db->fetch_var('SELECT col FROM tablename WHERE ID = ?', 1);

SQLite 锁定

SQLite 很好，但数据库锁定可能很棘手。
dbhelper 提供了默认的超时时间 60 秒，这可以防止大多数数据库锁定。
您可以在 connect() 函数中手动定义超时。
查看以下 SQLite 锁定测试

• php tests/lock/run.php 1：遇到数据库锁定
• php tests/lock/run.php 120：没有遇到数据库锁定

还可以考虑通过 $db->query('PRAGMA journal_mode=WAL;'); 启用 wal。

返回值

在检索结果后，dbhelper 通常返回关联数组。
如果您与 WordPress 一起使用，则返回对象。
dbhelper 在所有发生的错误上抛出异常。
在 insert 操作中，返回主键（id）。
在任何 delete、update 或甚至 query 操作中，返回受影响的行数。

静态版本

还有一个静态版本，其中包含静态函数调用（如果您使用单个 dbhelper 实例，则这样做是有意义的）

$db = new dbhelper();
require_once $_SERVER['DOCUMENT_ROOT'] . '/vendor/vielhuber/dbhelper/src/static.php';
db_connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', 'database', 3306);
db_fetch_var('SELECT col FROM tablename WHERE ID = ?', 1);