README

此库提供与特定数据库连接库无关的查询构建器。

主要功能

支持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中存在的三种不同类型的值。

需要转义的表/字段名称。
需要引号的值，如字符串值。
普通表达式。

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

':name'：转义表/字段名称。
':value'：引号字符串值。
':plain'：不执行任何操作（警告：':plain' 可能会受到SQL注入的影响）。

由于大多数查询都依赖于以下类型的条件：字段 = 值，因此您无需在所有地方指定格式。例如，您可以像以下这样简单地编写您的选择条件

$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

测试

可以使用以下命令运行spec套件

cd sql
composer install
./bin/kahlan

crysalead / sql-dialect

维护者

详细信息