crysalead/sql

该包已被弃用，不再维护。作者建议使用crysalead/sql-dialect包。

SQL查询构建器。

维护者

详细信息

github.com/crysalead/sql

源代码

问题

安装: 389

依赖: 0

建议者: 0

安全性: 0

星星: 5

关注者: 4

分支: 0

开放问题: 0

dev-master 2019-02-07 19:42 UTC

Requires

Requires (Dev)

MIT 867a768086fb3eb539752671a0dd54b949fe9d79

databasesqlmysqlpostgresqlquery

This package is not auto-updated.

Last update: 2019-02-20 18:38:24 UTC

README

Build Status Build Status Scrutinizer Coverage Status

该库提供独立于任何特定数据库连接库的查询构建器。

主要功能

  • 支持MySQL和PostgreSQL
  • 使用前缀表示法构建查询
  • 支持SELECT、UPDATE、INSERT、DELETE和TRUNCATE
  • 支持CREATE TABLE和DROP TABLE

社区

要提问、提供反馈或与团队进行其他沟通，请加入Freenode上的#chaos。

文档

方言类

Dialect类用于生成SQL查询。可用的Dialect类有

  • sql\dialect\MySql：利用MySql的特性
  • sql\dialect\PostgreSql：利用PostgreSql的特性
  • sql\Dialect：通用的SQL类

首先实例化一个MySql方言类

use Lead\Sql\Dialect\MySql;

$dialect = new MySql();

实例化后，可以使用方言的statement()方法创建查询实例，如下所示

<?php
$select = $dialect->statement('select');
$insert = $dialect->statement('insert');
$update = $dialect->statement('update');
$delete = $dialect->statement('delete');
$delete = $dialect->statement('truncate');

$createTable = $dialect->statement('create table');
$dropTable = $dialect->statement('drop table');
?>

然后，要生成相应的SQL，需要使用toString()或__toString()

$select->from('mytable');
echo $select->toString(); // SELECT * FROM "mytable"
echo (string) $select;    // SELECT * FROM "mytable"

注意：如果查询配置不正确，可能会抛出异常。由于PHP的一些限制，从__toString()抛出异常会产生致命错误。因此，为了使错误消息更友好，建议始终使用toString()而不是魔术方法。

由于生成的SQL独立于任何特定的数据库连接库，因此您可以使用PDO或其他您选择的任何数据库连接库来执行查询。

引号

默认情况下，字符串值会自动加引号，但一些数据库连接提供了自己的内置引号方法。如果您不使用PDO，您可能需要覆盖默认的引号处理程序。为此，您需要将处理程序注入到方言实例中，如下所示

use PDO;
use Lead\Sql\Dialect\PostgreSql;

$connection = new DBConnection($dsn, $user, $password);
$dialect = new PostgreSql(['quoter' => function($string) use ($connection){
    return $connection->quoteTheString($string);
}]);

注意：为了防止SQL注入，表/字段名称也默认转义。

SELECT

SELECT查询示例

<?php
$select
    ->distinct()                    // SELECT DISTINCT
    ->fields([
        'id',                       // a field name
        'fielname' => 'alias'       // a aliased field name
    ])
    ->from('table')                 // FROM
    ->join(                         // JOIN
        'other',                    // a table name
        ['other.table_id' => [      // join conditions, (more information on
            ':name' => 'table.id'   // [':name' => ...] in the Prefix Notation section)
        ]],
        'LEFT'                      // type of join
    )
    ->where([                       // WHERE `fieldname` === 'value'
        'fielname' => 'value'
    ])
    ->group('foo')                  // GROUP BY
    ->having([                      // HAVING `fieldname` === 'value'
        'fielname' => 'value'
    ])
    ->order('bar')                  // ORDER BY
    ->limit(10)                     // LIMIT
    ->offset(40)                    // OFFSET
    ->forUpdate()                   // FOR UPDATE
?>

前缀表示法（或波兰表示法）

为了能够编写复杂的SQL查询，选择了前缀表示法，而不是依赖于具有许多方法的详尽API（即方法如orWhere、andWhere、whereNull()等），而这些方法通常最终会缺少方法。

中缀表示法是最常见的算术表示法。它以操作符在操作数之间的位置为特征（例如 3 + 4）。而在前缀表示法中，操作符位于其操作数 左侧（例如 + 3 4）。

对于开发者来说，这种表示法非常直观，因为它与函数定义的方式非常相似。在这里，+ 可以看作是函数名，而 3 和 4 则是参数。

因此，为了能够构建复杂的查询而不会受到 API 的限制，这个库允许使用类似于以下的前缀表示法

$select->fields(['*' => [
    ['+' => [1, 2]], 3
]]);
echo $select->toString();            // SELECT 1 + 2 * 3

$select->fields(['*' => [
    [':()' => ['+' => [1, 2]]], 3
]]);
echo $select->toString();            // SELECT (1 + 2) * 3

你可能不得不读这个例子两遍。但如果你第一眼看上去很困惑，前缀表示法有几个优点：*

  • 它根据定义可以表示任何类型的表达式。
  • 它不是针对 SQL 的，可以在更高的抽象级别中使用。
  • 它作为数学抽象，在程序处理上比解析/反解析 SQL 字符串要简单。

注意：所有命名操作符都以前缀冒号 : 开头（例如 :or、:and、:like、:in 等）。然而，数学符号如 +、-、<= 等则不需要冒号。

格式化器

格式化器用于处理 SQL 中存在的三种不同类型的值

  1. 需要转义的表/字段名。
  2. 需要引号（如字符串值）的值。
  3. 普通表达式。

因此，为了能够选择格式，引入了以下格式化器

  • ':name'：转义表/字段名。
  • ':value'：引号字符串值。
  • ':plain'：不执行任何操作（警告：':plain' 易受 SQL 注入攻击）

由于大多数查询都依赖于以下类型的条件：field = value，所以你不需要在每个地方都指定格式。例如，你可以这样简单地编写你的选择条件

$select->from('table')->where([
    'field1' => 'value1',
    'field2' => 'value2'
]);
echo $select->toString();
// SELECT * FROM `table` WHERE `field1` = 'value1' AND `field2` = 'value2'

这可以改写为

$select->from('table')->where([
    'field' => [':value' => 'value']
]);

这也可以改写为

$select->from('table')->where([
    ['=' => [[':name' => 'field'], [':value' => 'value']]]
]);

所以，大多数情况下，['field' => 'value'] 语法将完美地满足你的需求。然而，如果你想创建一个 field1 = field2 的条件，其中两个部分都需要转义，前缀表示法可以帮到你

$select->from('table')->where([
    'field1' => [':name' => 'field2']
]);

常见操作符

以下是一个包含常见操作符的详尽列表，这些操作符适用于 MySQL 和 PostgreSQL

  • '='
  • '<=>'
  • '<'
  • '>'
  • '<='
  • '>='
  • '!='
  • '<>'
  • '-'
  • '+'
  • '*'
  • '/'
  • '%'
  • '>>'
  • '<<'
  • ':='
  • '&'
  • '|'
  • ':mod'
  • ':div'
  • ':like'
  • ':not like'
  • ':is'
  • ':is not'
  • ':distinct'
  • '~'
  • ':between'
  • ':not between'
  • ':in'
  • ':not in'
  • ':exists'
  • ':not exists'
  • ':all'
  • ':any'
  • ':some'
  • ':as'
  • ':not'
  • ':and'
  • ':or'
  • ':xor'
  • '()' 还可以使用一些“免费”的操作符。所有不在上述列表中的操作符都将被视为 SQL 函数，例如 :concat、:sum、:min、:max 等，并将生成为 FUNCTION(...)。

MySQL 专用操作符

  • '#'
  • ':regex'
  • ':rlike'
  • ':sounds like'
  • ':union'
  • ':union all''
  • ':minus'
  • ':except'

PostgreSQL 专用操作符

  • ':regex'
  • ':regexi'
  • ':not regex'
  • ':not regexi'
  • ':similar to'
  • ':not similar to'
  • ':square root'
  • ':cube root'
  • ':fact'
  • '|/'
  • '||/'
  • '!!'
  • ':concat'
  • ':pow'
  • '#'
  • '@'
  • '<@'
  • '@>'
  • ':union'
  • ':union all'
  • ':except'
  • ':except all'
  • ':intersect'
  • ':intersect all'

自定义专用操作符

还可以使用处理程序创建自己的运算符。

示例

$dialect = new PostgreSql([
    'builders' => [
        'braces' => function ($operator, $parts) {
            return "{" . array_shift($parts)  ."}";
        }
    ],
    'operators' => [
        '{}' => ['builder' => 'braces']
        // Note: ['format' => '{%s}'] would also be enough here.
    ]
]);

$select = $dialect->statement('select');
$select->fields(['{}' => [1]]); // SELECT {1}
]]);

上述示例允许使用 '{}' 作为运算符，并提供以下格式化 '{%s}'。

子查询

要在另一个查询内部使用子查询或对查询进行一些代数运算（例如 UNION、INTERSECT 等），您可以简单地将它们混合在一起

JOIN 子查询的示例

$subquery = $dialect->statement('select')
$subquery->from('table2')->alias('t2');

$select->from('table')->join($subquery);

echo $select->toString();
// SELECT * FROM "table" LEFT JOIN (SELECT * FROM "table2") AS "t2"

UNION 查询的示例

$select1 = $dialect->statement('select')->from('table1');
$select2 = $dialect->statement('select')->from('table2');

echo $dialect->conditions([
    ':union' => [$select1, $select2]
]);
// SELECT * FROM `table1` UNION SELECT * FROM `table2`

INSERT

INSERT 查询的示例

$insert = $dialect->statement('insert');

$insert
    ->into('table')               // INTO
    ->values([                    // (field1, ...) VALUES (value1, ...)"
        'field1' => 'value1',
        'field2' => 'value2'
    ]);

values() 方法允许您传递一个键值对数组，其中键是字段名，值是字段值。

UPDATE

UPDATE 查询的示例

$update = $dialect->statement('update');

$update
    ->table('table')              // TABLE
    ->values([                    // (field1, ...) VALUES (value1, ...)"
        'field1' => 'value1',
        'field2' => 'value2'
    ])
    ->where(['id' => 123]);       // WHERE

values() 方法允许您传递一个键值对数组，其中键是字段名，值是字段值。

DELETE

DELETE 查询的示例

$delete = $dialect->statement('delete');

$delete
    ->from('table')                 // FROM
    ->where(['id' => 123]);         // WHERE

TRUNCATE

TRUNCATE 查询的示例

$truncate = $dialect->statement('truncate');

$truncate->table('table');           // TABLE

CREATE TABLE

CREATE TABLE 查询的示例

$createTable = $dialect->statement('create table');
$createTable
    ->table('table')                 // TABLE
    ->columns([])                    // columns definition
    ->meta([])                       // table meta definition
    ->constraints([]);               // constraints definition

以下是一个创建MySQL表的示例

$createTable = $dialect->statement('create table');
$createTable
    ->table('table')
    ->columns([
        'id' => ['type' => 'serial'],
        'table_id' => ['type' => 'integer'],
        'published' => [
            'type' => 'datetime',
            'null' => false,
            'default' => [':plain' => 'CURRENT_TIMESTAMP']
        ],
        'decimal' => [
            'type' => 'float',
            'length' => 10,
            'precision' => 2
        ],
        'integer' => [
            'type' => 'integer',
            'use' => 'numeric',
            'length' => 10,
            'precision' => 2
        ],
        'date' => [
            'type' => 'date',
            'null' => false,
        ],
        'text' => [
            'type' => 'text',
            'null' => false,
        ]
    ])
    ->meta([
        'charset' => 'utf8',
        'collate' => 'utf8_unicode_ci',
        'engine' => 'InnoDB'
    ])
    ->constraints([
        [
            'type' => 'check',
            'expr' => [
               'integer' => ['<' => 10]
            ]
        ],
        [
            'type' => 'foreign key',
            'foreignKey' => 'table_id',
            'to' => 'other_table',
            'primaryKey' => 'id',
            'on' => 'DELETE NO ACTION'
        ]
    ]);

echo $this->create->toString();
// CREATE TABLE `table` (
// `id` int NOT NULL AUTO_INCREMENT,
// `table_id` int,
// `published` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
// `decimal` decimal(10,2),
// `integer` numeric(10,2),
// `date` date NOT NULL,
// `text` text NOT NULL,
// CHECK (`integer` < 10),
// FOREIGN KEY (`table_id`) REFERENCES `other_table` (`id`) ON DELETE NO ACTION,
// PRIMARY KEY (`id`))
// DEFAULT CHARSET utf8 COLLATE utf8_unicode_ci ENGINE InnoDB

抽象类型

数据库对于类型使用不同的命名约定，可能会引起混淆。为了尽可能通用，可以使用一些抽象的 'type' 定义来定义列。默认情况下，以下类型被支持

  • 'id'：外键ID
  • 'serial'：自动增长的序列主键
  • 'string'：字符串值
  • 'text'：文本值
  • 'integer'：整数值
  • 'boolean'：布尔值
  • 'float'：浮点值
  • 'decimal'：两位小数的十进制值
  • 'date'：日期值
  • 'time'：时间值
  • 'datetime'：日期时间值
  • 'binary'：二进制值

例如，使用MySQL时，'serial' 类型将生成以下查询

$createTable = $dialect->statement('create table');
$createTable
    ->table('table')
    ->columns([
        'id' =>  ['type' => 'serial']
    ]);

echo $this->create->toString();
// CREATE TABLE `table` (`id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`))

而PostgreSQL将生成

$createTable = $dialect->statement('create table');
$createTable
    ->table('table')
    ->columns([
        'id' =>  ['type' => 'serial']
    ]);

echo $this->create->toString();
// CREATE TABLE "table" ("id" serial NOT NULL, PRIMARY KEY ("id"))

但是，您可以添加自己的抽象类型。例如，为了使'uuid' 代表 char(30) 列，我们可以编写

$dialect = new MySql();
$dialect->type('uuid', ['use' => 'char', 'length' => 30]);

如果您不想处理抽象类型，可以直接使用 'use' 而不是 'type' 来定义列

$createTable = $dialect->statement('create table');
$createTable
    ->table('table')
    ->columns([
        'id'   =>  ['type' => 'serial'],
        'data' =>  ['use' => 'blob']
    ]);

抽象类型自动检测

当您使用抽象类型时，能够将数据库类型映射到相应的抽象类型将非常有用。

示例

$dialect->map('tinyint', 'boolean', ['length' => 1]);
$dialect->map('tinyint', 'integer');

echo $dialect->mapped('tinyint'); // integer
echo $dialect->mapped([           // boolean
    'use'    => 'tinyint'
    'length' => 1
]);

注意：对于像SQLite这样的数据库，这帮助不大，因为类型区分度不足，但此功能对于PostgreSQL或MySQL可能很有用。

DROP TABLE

DROP TABLE 查询的示例

$dropTable = $dialect->statement('drop table');

$dropTable->table('table');         // TABLE

测试

可以使用以下命令运行规范套件

cd sql
composer install
./bin/kahlan