squirrelphp/queries

针对数据库查询和事务的简化界面和查询构建器，可以分层/装饰。

维护者

详细信息

github.com/squirrelphp/queries

主页

源代码

问题

安装： 3,545

依赖项: 2

建议者: 1

安全性: 0

星级: 5

关注者: 2

分支: 0

开放性问题: 0

v1.3 2022-05-04 15:26 UTC

Requires

Requires (Dev)

Suggests

MIT 1e6ad6912101018d682d9f840d5e44975c5cf486

  • Andreas Leathley <andreas.leathley.woop@panaxis.ch>

databasephpabstractionmysqlsqlitepgsql

This package is auto-updated.

Last update: 2024-09-13 15:44:40 UTC

README

Build Status Test Coverage PHPStan Packagist Version PHP Version Software License

提供简化接口用于低级数据库查询和事务（DBInterface），以及查询构建器以更轻松、更简洁地创建查询（DBBuilderInterface）。接口限制以避免混淆/误用，并鼓励安全使用。

底层连接（和抽象）处理使用 Doctrine DBAL，我们添加了插入或更新功能（称为UPSERT），结构化查询更容易编写和阅读（并且可以使用查询构建器），以及分层数据库关注点（如实际实现、连接重试、性能测量、日志记录等）的可能性。此库还平滑了一些 MySQL、Postgres 和 SQLite 之间的差异。虽然 DBAL 是当前依赖项，但在使用此库时，您只需在代码中配置/创建必要的 DBAL 连接即可，DBAL 的其他部分不相关。

默认情况下，此库提供两层，一层处理 Doctrine DBAL（传递查询、处理并返回结果），另一层处理错误（DBErrorHandler）。DBErrorHandler 捕获死锁和连接问题，并尝试重复查询或事务，并统一来自 DBAL 的异常，以便提供原始调用到 DBInterface 并轻松找到错误。

安装

composer require squirrelphp/queries

目录

设置

将 Squirrel\Queries\DBInterface 作为类型提示用于您的服务以使用低级接口，以及/或 Squirrel\Queries\DBBuilderInterface 用于查询构建器。低级接口选项基于 Doctrine 和 PDO，并进行了一些调整，而构建器接口是编写结构化（且不太复杂）查询的表达式方式。

如果您了解 Doctrine 或 PDO，应该能够轻松使用此库。您应该特别注意结构化查询和 UPSERT，因为这些是低级接口的补充，帮助您编写可读的查询，并自动处理列字段名称和参数，使编写安全查询变得更容易。

对于与 Symfony 框架轻松集成的解决方案，请查看 squirrelphp/queries-bundle，对于实体和存储库支持，请查看 squirrelphp/entities 和 squirrelphp/entities-bundle。

如果您想自己组装 DBInterface 对象，以下代码可能是一个起点

use Doctrine\DBAL\DriverManager;
use Squirrel\Queries\DBBuilderInterface;
use Squirrel\Queries\DBInterface;
use Squirrel\Queries\Doctrine\DBErrorHandler;
use Squirrel\Queries\Doctrine\DBMySQLImplementation;

// Create a doctrine connection
$dbalConnection = DriverManager::getConnection([
    'url' => 'mysql://user:secret@localhost/mydb',
    'driverOptions' => [
        \PDO::ATTR_EMULATE_PREPARES => false, // Separates query and values
        \PDO::MYSQL_ATTR_FOUND_ROWS => true, // important so MySQL behaves like Postgres/SQLite
        \PDO::MYSQL_ATTR_MULTI_STATEMENTS => false,
    ],
]);

// Create a MySQL implementation layer
$implementationLayer = new DBMySQLImplementation($dbalConnection);

// Create an error handler layer
$errorLayer = new DBErrorHandler();

// Set implementation layer beneath the error layer
$errorLayer->setLowerLayer($implementationLayer);

// Rename our layered service - this is now our database object
$db = $errorLayer;

// $db is now useable and can be injected
// anywhere you need it. Typehint it with
// \Squirrel\Queries\DBInterface

$fetchEntry = function(DBInterface $db): array {
    return $db->fetchOne('SELECT * FROM table');
};

$fetchEntry($db);

// A builder just needs a DBInterface to be created:

$queryBuilder = new DBBuilderInterface($db);

// The query builder generates more readable queries, and
// helps your IDE in terms of type hints / possible options
// depending on the query you are doing
$entries = $queryBuilder
    ->select()
    ->fields([
      'id',
      'name',
    ])
    ->where([
      'name' => 'Robert',
    ])
    ->getAllEntries();

// If you want to add more layers, you can create a
// class which implements DBRawInterface and includes
// the DBPassToLowerLayer trait and then just overwrite
// the functions you want to change, and then connect
// it to the other layers through setLowerLayer

// It is also a good idea to catch \Squirrel\Queries\DBException
// in your application in case of a DB error so it
// can be handled gracefully

数据库支持

此库支持三种主要的开源数据库

  • MySQL，所有版本（至少推荐 5.5+ 版本）
  • MariaDB，所有版本（MariaDB 几乎与 MySQL 行为相同）
  • SQLite，所有版本，尽管原生 UPSERT 查询仅在 SQLite 3.24+ 版本中受支持，但在较低版本中仿真此功能
  • Postgres 版本 9.5 和以上，因为 UPSERT 查询是在 9.5 中实现的

本库的功能已针对这些数据库的真实版本进行测试，以确保其工作正常，尽管可能存在需要调整的边缘情况。如果您发现任何问题，请报告它们。

对于Postgres，有解决方案可以使BLOB类型（在Postgres中称为BYTEA）更容易处理，因此处理BLOB与MySQL/SQLite几乎一样简单。

DBInterface - 低级接口

将SELECT查询作为普通字符串

您可以使用select函数使用给定的参数编写自己的SELECT查询，然后使用fetch函数获取结果，并使用clear函数清除结果

$selectStatement = $db->select('SELECT fieldname FROM tablename WHERE restriction = ? AND restriction2 = ?', [5, 8]);
$firstRow = $db->fetch($selectStatement);
$db->clear($selectStatement);

所有?都替换为第二个参数中的数组值（这些是查询参数），如果没有，您可以省略第二个参数

$selectStatement = $db->select('SELECT fieldname FROM tablename WHERE restriction = 5 AND restriction2 = 8');

建议为任何查询数据使用查询参数，即使它是固定的，因为它无论数据来自何处（如用户输入）都是安全的，并且字符集或类型无关紧要（字符串、整数、布尔值），这意味着不可能发生SQL注入。

可以使用fetchOne和fetchAll代替select函数直接检索SELECT查询的恰好一行（fetchOne）或所有行（fetchAll），例如

$firstRow = $db->fetchOne('SELECT fieldname FROM tablename WHERE restriction = ? AND restriction2 = ?', [5, 8]);
$allRows = $db->fetchAll('SELECT fieldname FROM tablename WHERE restriction = ? AND restriction2 = ?', [5, 8]);

结构化SELECT查询

您可以使用结构化查询而不是编写原始SQL

$selectStatement = $db->select([
    'field' => 'fieldname',
    'table' => 'tablename',
    'where' => [
        'restriction' => 5,
        'restriction2' => 8,
    ],
]);
$firstRow = $db->fetch($selectStatement);
$db->clear($selectStatement);

除了更容易编写或处理之外，它还会转义字段和表名，因此以下字符串查询与上面的结构化查询相同

$selectStatement = $db->select('SELECT ´fieldname´ FROM ´tablename´ WHERE ´restriction´=? AND ´restriction2´=?', [5, 8]);

字段名和表名如何引号取决于Doctrine及其抽象，因此转义字符可能因数据库引擎而异。上面显示了MySQL如何转义。

结构化查询可以替换几乎所有字符串SELECT查询，即使涉及多个表——这是一个更复杂的示例，显示了其选项

$selectStatement = $db->select([
   'fields' => [
       'fufumama',
       'b.lalala',
       'result' => 'a.setting_value',
       'result2' => ':a.setting_value:+:b.blabla_value:',
   ],
   'tables' => [
       'blobs.aa_sexy a',
       ':blobs.aa_blubli: :b: LEFT JOIN :blobs.aa_blubla: :c: ON (:c.field: = :b.field5: AND :b.sexy: = ?)' => 5,
   ],
   'where' => [
       ':a.field: = :b.field:',
       'setting_id' => 'orders_xml_override',
       'boring_field_name' => [5,3,8,13],
       ':setting_value: = ? OR :setting_value2: = ?' => ['one','two'],
   ],
   'group' => [
        'a.field',
   ],
   'order' => [
        'a.field' => 'DESC',
   ],
   'limit' => 10,
   'offset' => 5,
   'lock' => true,
]);
$firstRow = $db->fetch($selectStatement);
$db->clear($selectStatement);

这相当于以下字符串SELECT查询（当使用MySQL时）

$selectStatement = $db->select('SELECT `fufumama`,`b`.`lalala`,`a`.`setting_value` AS "result",(`a`.`setting_value`+`b`.`blabla_value`) AS "result2" FROM `blobs`.`aa_sexy` `a`,`blobs`.`aa_blubli` `b` LEFT JOIN `blobs`.`aa_blubla` `c` ON (`c`.`field` = `b`.`field5` AND `b`.`sexy` = ?) WHERE (`a`.`field` = `b`.`field`) AND `setting_id`=? AND `boring_field_name` IN (?,?,?,?) AND (`setting_value` = ? OR `setting_value2` = ?) GROUP BY `a`.`field` ORDER BY `a`.`field` DESC LIMIT 10 OFFSET 5 FOR UPDATE', [5,'orders_xml_override',5,3,8,13,'one','two']);

重要的转换细节

  • 如果表达式包含类似`:fieldname:`的内容，则假定它是字段或表名，然后将其转义。对于简单的WHERE限制或字段定义，字段名将自动转义。
  • 如果您只有一个字段，则可以使用“field”，对于多个字段则使用“fields”。同样，“table”和“tables”也是如此。
  • 如果您将“lock”设置为true，“FOR UPDATE”将添加到查询中，因此结果将在当前事务中锁定。
  • 尽可能检查参数，如果选项/表达式无效，则抛出DBInvalidOptionException。这不包括SQL错误，因为SQL组件不知道允许的字段名、表名或构成有效SQL表达式的因素。

您可以直接将结构化SELECT查询传递给fetchOne和fetchAll以检索一个或所有结果。

更改查询

可以使用change函数执行自定义INSERT、UPDATE和DELETE查询（或其他自定义查询），这意味着此查询会更改某些内容，与SELECT查询不同

$rowsAffected = $db->change('UPDATE users SET first_name = ?, last_name = ?, login_number = login_number + 1 WHERE user_id = ?', [
    'Liam', // first_name
    'Henry', // last_name
    5, // user_id
]);
$rowsAffected = $db->change('DELETE FROM users WHERE user_id = ? AND first_name = ?', [
    5, // user_id
    'Liam', // first_name
]);
$rowsAffected = $db->change('INSERT INTO users (user_id, first_name) SELECT user_id, first_name FROM users_backup');

不建议使用change函数，除非您没有其他选择——大多数查询可以使用特定的update、insert、insertOrUpdate和delete方法完成。然而，如果您需要子查询或其他高级数据库功能，则change是您的唯一选项。

UPDATE查询

对于更新，您可以使用专门的update方法，以下是一个示例

$rowsAffected = $db->update('tablename', [
    'fieldname' => 'string',
    'locationId' => 5,
], [
    'restriction' => 5,
    'restriction2' => 8,
]);

第一个参数是表名，第二个参数是更改列表（SQL中的SET子句），第三个参数是WHERE限制列表。它与以下字符串查询相同

$rowsAffected = $db->change('UPDATE ´tablename´ SET ´fieldname´=?,`locationId`=? WHERE ´restriction´=? AND ´restriction2´=?', ['string', 5, 5, 8]);

您一次只能更新一行（根据SQL标准），因为MySQL、Postgres和SQLite之间多表更新的选项和语法差异很大，重叠几乎不存在。

INSERT

insert 在一个表中执行INSERT查询，例如

$insertId = $db->insert('yourdatabase.yourtable', [
    'tableId' => 5,
    'column1' => 'Henry',
    'other_column' => 'Liam',
], 'rowId');

第一个参数是表名，第二个参数是插入的列名和值，可选的第三个参数定义数据库为创建自动插入ID（MySQL和SQLite中称为AUTOINCREMENT，Postgres中称为SERIAL）的列名。如果没有AUTOINCREMENT列，或者您显式设置了它，则只需不提供第三个参数。

上述查询将执行以下SQL查询

INSERT INTO `yourdatabase`.`yourtable` (`tableId`,`column1`,`other_column`) VALUES (?,?,?)

带有值 5、Henry 和 Liam。

insertOrUpdate - UPSERT / MERGE

定义

UPSERT（更新或插入）查询是SQL的一个补充，在不同数据库系统中以不同的查询形式存在。

  • MySQL将其实现为“INSERT ... ON DUPLICATE KEY UPDATE”。
  • PostgreSQL和SQLite为“INSERT ... ON CONFLICT (index) DO UPDATE”。
  • ANSI标准称其为MERGE查询，尽管这些可能有所不同。

在这个库中，我们将此类查询称为insertOrUpdate。此类查询尝试插入一行，但如果该行已存在，则执行更新，所有这些都是在数据库中作为一个原子操作完成的。如果不使用UPSERT查询，您至少需要在一个事务中进行一个UPDATE查询，然后可能还需要一个INSERT查询来完成相同的操作。UPSERT存在是为了提供一个更快、更简单的解决方案。

PostgreSQL和SQLite需要构成表中唯一索引的特定列名，用于确定条目是否已存在或是否插入新条目。MySQL会自动执行此操作，但对于所有数据库系统，在UPSERT查询中都需要有唯一索引。

用法和示例

insertOrUpdate函数的前两个参数与常规插入函数相同，第三个参数定义构成数据库表中唯一索引或主键的列。最后一个数组是如果条目已在数据库中存在要执行的更新列表，但它不是必需的。

一个例子可能是

$db->insertOrUpdate('users_visits', [
    'userId' => 5,
    'visit' => 1,
], [
    'userId',
], [
    ':visit: = :visit: + 1'
]);

对于MySQL，此查询将转换为

$db->change('INSERT INTO `users_visits` (`userId`,`visit`) VALUES (?,?) ON DUPLICATE KEY UPDATE `visit` = `visit` + 1', [5, 1]);

对于PostgreSQL/SQLite，将是

$db->change('INSERT INTO "users_visits" ("userId","visit") VALUES (?,?) ON CONFLICT ("userId") DO UPDATE SET "visit" = "visit" + 1', [5, 1]);

如果没有在users_visits中存在条目，则会插入一个设置visit为1的条目。但如果已存在条目，则执行带有visit = visit + 1的UPDATE。

定义UPDATE部分是可选的，如果为空，则UPDATE仅执行INSERT中除索引列之外的同种更改。例如

$db->insertOrUpdate('users_names', [
    'userId' => 5,
    'firstName' => 'Jane',
], [
    'userId',
]);

这将使用userId和firstName进行插入，但如果该行已存在，则仅更新firstName为Jane，因此对于MySQL，它将转换为

$db->change('INSERT INTO `users_names` (`userId`,`firstName`) VALUES (?,?) ON DUPLICATE KEY UPDATE `firstName`=?, [5, 'Jane', 'Jane']);

需要记住的最重要的事情是，您需要一个唯一或主键索引在UPSERT查询中涉及 - 因此您需要知道表的索引。

DELETE

delete函数提供了一个对单个表进行DELETE查询的结构化方法。例如

$rowsAffected = $db->delete('users_names', [
    'userId' => 13,
]);

第一个参数是表的名称，第二个参数是WHERE限制。因此，作为一个纯字符串查询，这将等同于

$rowsAffected = $db->change('DELETE FROM `users_names` WHERE `userId`=?', [13]);

结构化的WHERE条目遵循与结构化SELECT查询和update方法相同的逻辑/规则。

TRANSACTION

只需将可调用的/函数传递给transaction方法，DBInterface将自动处理提交/回滚部分，并尽力使事务成功。

示例

$db->transaction(function() {
    // Do queries in here as much as you want, it will all be one transaction
    // and committed as soon as this function ends
});

一个实际的例子可能是

$db->transaction(function() use ($db) {
    $tableId = $db->insert('myTable', [
        'tableName' => 'Henry',
    ], 'tableId');

    $db->update('otherTable', [
        'tableId' => $tableId,
    ], [
        'tableName' => 'Henry',
    ]);
});

如果在事务函数内部调用 transaction，该函数将只是成为“外部事务”的一部分，并随它一起失败或成功

$db->transaction(function() use ($db) {
    $tableId = $db->insert('myTable', [
        'tableName' => 'Henry',
    ], 'tableId');

    // This still does exactly the same as in the previous example, because the
    // function will be executed without a "new" transaction being started,
    // the existing one just continues
    $db->transaction(function() use ($db, $tableId)) {
        // If this fails, then the error handler will attempt to repeat the outermost
        // transaction function, which is what you would want / expect, so it starts
        // with the Henry insert again
        $db->update('otherTable', [
            'tableId' => $tableId,
        ], [
            'tableName' => 'Henry',
        ]);
    });
});

如果有死锁或连接问题，错误处理程序（DBErrorHandler）将回滚事务并尝试重试10次，每次重试之间的等待时间逐渐增加。只有当大约30秒内有10次失败时，异常才会通过 DBException 升级。

如果您想将参数传递给 $func，这是一个示例（您也可以将其添加到 use 部分）

$db->transaction(function(string $table, string $tableName) use ($db) {
    $tableId = $db->insert('myTable', [
        'tableName' => 'Henry',
    ], 'tableId');

    $db->update('otherTable', [
        'tableId' => $tableId,
    ], [
        'tableName' => $tableName,
    ]);
}, 'myTable', 'Henry');

当在事务中使用 SELECT 查询时，您应该始终记住，结果通常不会被锁定（因此不会受到 UPDATE 或 DELETE 的保护），除非您在字符串 SELECT 查询中应用 "... FOR UPDATE" 或在结构化 SELECT 中将 lock 设置为 true。

引用标识符

如果您想确保安全，建议使用 DBInterface 的 quoteIdentifier 函数引用所有标识符（表名和列名）进行非结构化 select 和 change 查询。

对于 insert 和 insertOrUpdate，引用由您完成，对于结构化查询，大多数引用由您完成，除非您使用表达式，在这种情况下，您可以使用冒号来指定表名或列名。

如果您引用所有标识符，那么更改数据库系统（可能存在不同的保留关键字）或升级数据库（可能保留新关键字）会更容易。

$rowsAffected = $db->change('UPDATE ' . $db->quoteIdentifier('users') . ' SET ' . $db->quoteIdentifier('first_name') . ')=? WHERE ' . $db->quoteIdentifier('user_id') . '=?', ['Sandra', 5]);

引用表达式

在执行自定义 change 或字符串 select 查询时，使用 quoteIdentifier 引用每个标识符可能会很麻烦。相反，您可以在查询中将所有表名和列名用冒号包围，并使用 quoteExpression 处理它们。

这意味着表达式中的冒号只能用于表名和列名，否则结果可能不可预测。通常，您永远不会在 SQL 查询中使用冒号，但请确保不要意外地将包含冒号的内容包含在查询中 - 如果您将查询与值分开，这不是问题。

$rowsAffected = $db->change($db->quoteExpression('UPDATE :users: SET :first_name:=? WHERE :user_id:=?'), ['Sandra', 5]);

DBBuilderInterface - 高级查询构建器

DBBuilderInterface 提供以下函数

  • count
  • select
  • insert
  • update
  • insertOrUpdate (= UPSERT)
  • delete
  • transaction (要在事务中执行函数)
  • getDBInterface (获取底层 DBInterface 对象)

所有这些函数除了最后两个都返回一个构建器对象，该对象可以帮助您轻松创建查询并获取结果。与 DBInterface 相比，您不需要记住结构化查询可以包含哪些数据 - 您的 IDE 将建议所有可用的内容。

查看一些示例可以使用法非常清晰 - 这里为每个 6 个构建器函数提供了示例

Count

// $usersNumber will be an integer
$usersNumber = $dbBuilder
    ->count()
    ->inTables([
        'users u',
        'users_addresses a',
    ])
    ->where([
        ':u.userId: = :a.userId:',
        'u.zipCode' => 33769,
    ])
    ->getNumber();

一个简单的方法来仅计算行数。

Select

Select 查询可能变得非常复杂，因此它们有多个选项 - 这里是一个包含所有选项的示例（尽管许多是可选的！）

$selectQuery = $dbBuilder
    ->select()
    ->fields([
        'u.userId',
        'name' => 'a.firstName',
    ])
    ->inTables([
        'users u',
        'users_addresses a',
    ])
    ->where([
        ':u.userId: = :a.userId:',
        'u.zipCode' => 33769,
    ])
    ->groupBy([
        'u.userId',
    ])
    ->orderBy([
        'u.createDate',
    ])
    ->limitTo(3)
    ->startAt(0)
    ->blocking();

foreach ($selectQuery as $result) {
    echo $result['userId'] . ' => ' . $result['name'];
}

上述查询利用了每个 SELECT 查询构建器可以迭代的优点。一旦 foreach 循环开始，查询就会执行，并逐个检索条目。

如果您想一次性检索所有结果（因为您知道您无论如何都需要它们），这是一个选项

$userResults = $dbBuilder
    ->select()
    ->fields([
        'u.userId',
        'name' => 'a.firstName',
    ])
    ->inTables([
        'users u',
        'users_addresses a',
    ])
    ->where([
        ':u.userId: = :a.userId:',
        'u.zipCode' => 33769,
    ])
    ->groupBy([
        'u.userId',
    ])
    ->orderBy([
        'u.createDate',
    ])
    ->limitTo(3)
    ->startAt(0)
    ->blocking()
    ->getAllEntries();

foreach ($userResults as $result) {
    echo $result['userId'] . ' => ' . $result['name'];
}

或者如果您只需要一个条目

$result = $dbBuilder
    ->select()
    ->fields([
        'u.userId',
        'name' => 'a.firstName',
    ])
    ->inTables([
        'users u',
        'users_addresses a',
    ])
    ->where([
        ':u.userId: = :a.userId:',
        'u.zipCode' => 33769,
    ])
    ->groupBy([
        'u.userId',
    ])
    ->orderBy([
        'u.createDate',
    ])
    ->limitTo(3)
    ->startAt(0)
    ->blocking()
    ->getOneEntry();

echo $result['userId'] . ' => ' . $result['name'];

请注意，如果您只想传递一个值（作为字符串），则可以使用 field 代替 fields 和 inTable 代替 inTables，并且如果只想使用一个字符串值，则可以将字符串传递给 groupBy 和 orderBy。

有四种方法可以从 select 查询构建器获取数据

  • getIterator，获取可以迭代的对象（foreach），以便您可以逐个获取结果 - 这是在第一个示例中隐式使用的，因为构建器实现了 IteratorAggregate，将构建器放入 foreach 循环中
  • getAllEntries，一次性检索所有条目的数组，这是第二个示例
  • getOneEntry，仅获取一个条目，在第三个示例中使用
  • getFlattenedFields，意味着结果是“扁平化”的，其类型特定的变体有 getFlattenedIntegerFields、getFlattenedFloatFields、getFlattenedStringFields 和 getFlattenedBooleanFields

getFlattenedFields 对于某些情况很有用

$userIds = $dbBuilder
    ->select()
    ->field('userId')
    ->inTable('users')
    ->where([
        'u.zipCode' => 33769,
    ])
    ->getFlattenedFields();

foreach ($userIds as $userId) {
    // Do something which each $userId here
}

而不是每个包含一个“userId”字段的数组列表，结果是扁平化的，直接返回用户ID列表。扁平化对于ID或其他简单的值列表非常有用，只需要一个数组而不是数组中的数组。

您可以通过使用 getFlattenedIntegerFields、getFlattenedFloatFields、getFlattenedStringFields 或 getFlattenedBooleanFields 来强制对扁平化字段进行类型检查。这建议您更安全地使用类型，并使静态分析器/IDE更容易理解您的代码。然后，此库将尝试将所有值转换为请求的类型，并在存在任何歧义时抛出 DBInvalidOptionException。

插入

$newUserIdFromDatabase = $dbBuilder
    ->insert()
    ->inTable('users')
    ->set([
      'userName' => 'Kjell',
    ])
    ->writeAndReturnNewId('rowId');

如果您期望/需要插入ID，可以使用 writeAndReturnNewId（您需要指定插入ID的列名），或者仅使用 write 插入条目而不返回值。

更新

$rowsAffected = $dbBuilder
    ->update()
    ->inTable('users')
    ->set([
        'lastLoginDate' => time(),
        ':visits: = :visits: + 1',
    ])
    ->where([
        'userId' => 33,
    ])
    ->writeAndReturnAffectedNumber();

如果您对受影响/更改的行数感兴趣，可以使用 writeAndReturnAffectedNumber，或者如果您不需要该信息，则使用 write。

如果您想更新表中的所有行（并且不使用 WHERE 限制），必须明确指出

$rowsAffected = $dbBuilder
    ->update()
    ->inTable('users')
    ->set([
        'lastLoginDate' => time(),
        ':visits: = :visits: + 1',
    ])
    ->confirmNoWhereRestrictions()
    ->writeAndReturnAffectedNumber();

此明确确认子句是必要的，以避免意外遗漏 WHERE 部分，这在编写/执行查询时是一个常见的错误。

插入或更新

这使得 DBInterface 中的 insertOrUpdate 功能更容易理解，使用相同的信息

$insertBuilder
    ->insertOrUpdate()
    ->inTable('users_visits')
    ->set([
        'userId' => 5,
        'visits' => 1,
    ])
    ->index([
        'userId',
    ])
    ->setOnUpdate([
        ':visits: = :visits: + 1',
    ])
    ->write();

仅 write 可用于执行查询。

对于MySQL，此查询将转换为

INSERT INTO `users_visits` (`userId`,`visit`) VALUES (?,?) ON DUPLICATE KEY UPDATE `visit` = `visit` + 1

使用值 5 和 1 作为查询参数。

对于PostgreSQL/SQLite，将是

INSERT INTO "users_visits" ("userId","visit") VALUES (?,?) ON CONFLICT ("userId") DO UPDATE SET "visit" = "visit" + 1

如果没有在users_visits中存在条目，则会插入一个设置visit为1的条目。但如果已存在条目，则执行带有visit = visit + 1的UPDATE。

删除

$rowsAffected = $dbBuilder
    ->delete()
    ->inTable('users')
    ->where([
        'userId' => 33,
    ])
    ->writeAndReturnAffectedNumber();

如果您对受影响/更改的行数感兴趣，可以使用 writeAndReturnAffectedNumber，或者如果您不需要该信息，则使用 write。

如果您想删除表中的所有行（并且不使用 WHERE 限制），必须明确指出

$rowsAffected = $dbBuilder
    ->delete()
    ->inTable('users')
    ->confirmNoWhereRestrictions()
    ->writeAndReturnAffectedNumber();

此明确确认子句是必要的，以避免意外遗漏 WHERE 部分，这在编写/执行查询时是一个常见的错误。

事务

事务函数与 DBInterface 中的函数工作方式相同 - 实际上，DBBuilderInterface 只是将其原样传递给 DBInterface。

通用语法规则

对于任何查询中的简单列名到值的映射，您可以使用类似于在 PHP 中使用的名称到值语法

$user = $dbBuilder
    ->select()
    ->inTable('users')
    ->where([
        'user_id' => $userId, // user_id must be equal to $userId
    ])
    ->getOneEntry();

// $user now contains all table column and values:
echo $user['user_id'];

值与查询分开，以确保安全性，并且表名和列名会为您进行引号处理。

如果您想使用更复杂的表达式，您当然可以这样做

$user = $dbBuilder
    ->select()
    ->inTable('users')
    ->where([
        ':user_id: BETWEEN ? AND ?' => [15, 55],
        ':create_date: > ?' => time() - 86400,
    ])
    ->getOneEntry();

在这些情况下，请确保将所有表列名/字段名和表名用冒号包围，以便库可以转义它们。您可以使用任何 SQL 语法，并且 WHERE 子句中的每个条目都通过 AND 连接 - 因此，库将 WHERE 部分转换为以下内容

... WHERE (`user_id` BETWEEN ? AND ?) AND (`create_date` > ?) ...

对于自定义表达式，每个表达式都用括号包围，以确保它们不会相互影响，并且参数与查询分开发送，在这种情况下：[15, 55, time() - 86400]

此语法始终用于传递给库的任何数据，并且当该类型的语法可以转换为有效的 SQL 时。因此，一个 UPDATE 查询可能看起来像这样

$rowsAffected = $dbBuilder
    ->update()
    ->inTable('users')
    ->set([
        'last_login_date' => time(),
        ':visits: = :visits: + 1',
        ':full_name: = CONCAT(:first_name:,:last_name:)',
        ':balance: = :balance: + ?' => $balanceIncrease,
    ])
    ->where([
        'user_id' => 33,
        ':last_login_date: < ?' => time()-86400,
    ])
    ->writeAndReturnAffectedNumber();

这应该使查询的阅读和编写变得容易，即使您不太了解 SQL，也不必自己考虑将查询和参数分开 - 库会为您这样做。

Postgres 的 BLOB 处理

对于 MySQL 和 SQLite，检索或插入/更新 BLOB（二进制大对象）的方式与较短的/非二进制字符串字段相同。Postgres 需要一些调整，但此库使这些调整变得更加流畅。

  • 对于SELECT查询，Postgres返回的流会自动转换为字符串，模拟MySQL和SQLite的做法。
  • 对于INSERT/UPDATE查询，您需要使用本库提供的LargeObject实例包装BLOB值。

因此，如果file_data是Postgres中的BYTEA字段，以下代码是有效的。

use Squirrel\Queries\LargeObject;

$rowsAffected = $dbBuilder
    ->update()
    ->inTable('files')
    ->set([
        'file_name' => 'someimage.jpg',
        'file_data' => new LargeObject(file_get_contents('someimage.jpg')),
    ])
    ->where([
        'file_id' => 33,
    ])
    ->write();

检索二进制数据无缝进行。

$file = $dbBuilder
    ->select()
    ->inTable('files')
    ->where([
        'file_id' => 33,
    ])
    ->getOneEntry();

// Use file_data in some way, like showing or writing it - it is a regular string
echo $file['file_data'];

您也可以使用LargeObject类与MySQL/SQLite的UPDATE和INSERT一起使用，使您的代码能在所有系统上运行，尽管没有它也能工作。只有Postgres需要显式使用它来处理BYTEA列。

使用此库的指南

为了充分利用此库，建议遵循以下指南。

使用DBBuilderInterface - 或结构化查询

最简单、最安全的选项是使用构建器（DBBuilderInterface）- 使用IDE，您将轻松完成查询，同时将查询与数据分离变得非常容易且几乎是自动的。

如果您想使用DBInterface，请使用结构化的SELECT和UPDATE查询，因为它们更容易编写和阅读，并且使分离查询与数据变得更加容易，同时基本上包含与“纯”字符串查询相同的信息，所以请使用它们而不是自己编写SQL查询。

INSERT、UPSERT和DELETE查询已经结构化，因为它们的焦点有限。使用这五种查询类型，您应该能够处理99%的查询。

始终将查询与数据分离

为了您的应用程序安全，将查询与数据分离非常重要/有帮助。不要这样做：

$rowsAffected = $db->change('UPDATE sessions SET time_zone = \'Europe/Zurich\' WHERE session_id = \'zzjEe2Jpksrjxsd05m1tOwnc7LJNV4sV\'');

这样做：（或使用结构化查询，参见上面的提示！）

$rowsAffected = $db->change('UPDATE sessions SET time_zone = ? WHERE session_id = ?', [
    'Europe/Zurich',
    'zzjEe2Jpksrjxsd05m1tOwnc7LJNV4sV',
]);

与查询数据分离有许多优点。

  1. 您可以使用来自表单/用户的变量，因为SQL注入是不可能的。
  2. 使用?占位符比引号/转义数据更容易，而且无论数据是字符串、整数还是其他类型都无关紧要。
  3. 查询变得更短、更易读。
  4. 使用不同的数据库系统变得更容易，因为您可能需要在MySQL中使用“”来包装字符串，而在PostgreSQL中则使用‘’（“”用于标识符）。如果您使用?占位符，则不需要为数据使用任何类型的引号，因此您的查询变得更加通用。

使用简单查询

如果可能，请避免使用复杂的查询。查询变得越复杂，就越复杂。

  • 涉及两个以上的表（或对于更改数据的查询，涉及一个以上）
  • 使用GROUP BY或HAVING
  • 使用子查询
  • 使用数据库特定功能（存储过程、触发器、视图等）

有时，用一条查询解决许多问题可能会很有吸引力，但缺点很多。

  • 查询越复杂，性能下降越明显。
  • 多个短查询可以比一个大查询更好地缓存和负载均衡。
  • 将复杂查询移植到不同的数据库系统可能需要许多更改。
  • 理解和更改复杂查询要困难得多，因此错误的可能性更大。

有时，复杂查询可能更有意义，但它应该是不到1%情况下的罕见例外。

使用squirrelphp/queries-bundle和squirrelphp/entities

squirrelphp/queries-bundle 是将此库集成到Symfony中的集成，因此您可以快速开始。

squirrelphp/entities 是建立在squirrelphp/queries之上的库，并提供对类型化实体和存储库的支持，同时遵循上述所有指南。

squirrelphp/entities-bundle 是将实体和存储库集成到Symfony项目中的Symfony捆绑包。