README

Virtual SQL 是一个基于类的 MySQL 抽象层，旨在帮助以编程方式编写 MySQL 查询。
它允许通过单个 VirtualSqlQuery 类与查询结构交互，将实际的 MySQL 语法抽象化。

安装

composer require zethika/virtual-sql

表定义

Virtual SQL 提供了一个名为 VirtualSqlTable 的类，用于定义数据库表及其列，当与这些表交互查询时需要使用它。
这些表定义可以在运行时生成，通过将 PDO 实例提供给 VirtualSqlTableDefinitionGenerator 单例。
然后可以直接从数据库提取 CREATE TABLE 定义，并提供一个表示给定表的实例。
从数据库提取的 CREATE TABLE 语句保留在内存中，以减少对同一表的重复调用产生的多余 SQL 查询。

use VirtualSql\Generator\VirtualSqlTableDefinitionGenerator;

try {
    $pdo = new PDO('mysql:host='.$_ENV['DB_HOST'].';dbname='.$_ENV['DB_NAME'], $_ENV['DB_USER'], $_ENV['DB_PASS']);
    $pdo->exec("SET NAMES utf8");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    trigger_error($e->getMessage(),E_USER_ERROR);
}

$generator = VirtualSqlTableDefinitionGenerator::getInstance();
$generator->init($pdo);

// A VirtualSql\Definition\VirtualSqlTable instance
$tableDefinition = $generator->generateTableDefinition('table_name');

查询

查询是通过 VirtualSqlQuery 的子类构建的，是程序接口，运行时通过它构建查询。
它们提供了一系列辅助函数，根据正在构建的查询类型，以便更容易地操作查询部分。

可以通过向 VirtualSqlQuery::factory 方法提供一个类型常量和一个基础表的 VirtualSqlTable 实例来生成查询实例。
支持的类型有 TYPE_SELECT、TYPE_INSERT、TYPE_UPDATE 和 TYPE_DELETE

use VirtualSql\Query\VirtualSqlQuery;
$query = VirtualSqlQuery::factory(VirtualSqlQuery::TYPE_SELECT,$table);

查询配置参数

可以向工厂方法提供一个第三个参数，一个关联数组 $config。
这可以用来在实例化时设置查询的大部分值。

可能的键取决于特定的查询类型。通过 $config 参数设置的值也可以在实例化后通过方法调用进行操作。

SELECT

$query = VirtualSqlQuery::factory(VirtualSqlQuery::TYPE_SELECT,$table, [
    // Array of VirtualSqlColumn instances, describing which columns to select
    'selects' => [
        $table->getColumn('some_column'),
        $table->getColumn('another_column')
    ],
    // Array of VirtualSqlJoin instances, describing which joins to perform
    // It's recommended using the query helper methods instead
    'joins' => [
        new VirtualSqlJoin($fromColumn, $toColumn)
    ],
    // VirtualSqlConditionSet instance representing the base condition set
    'where' => new VirtualSqlConditionSet(VirtualSqlConstant::OPERATOR_AND, $conditions),
    // int|null representing the LIMIT parameter
    'limit' => 10,
    // int|null representing the OFFSET parameter
    'offset' => 10
]);

INSERT

$query = VirtualSqlQuery::factory(VirtualSqlQuery::TYPE_INSERT,$table, [
    // Array of VirtualSqlColumn instances, describing which columns to insert into
    'columns' => $table->getColumns(),
    // Array of associative arrays describing the value sets being inserted
    'valueSets' => [
        [
            'column_1' => 'some_value',
            'column_2' => 'some_value,
        ],
        [
            'column_1' => 'some_value',
            'column_2' => 'some_value,
        ]
    ],
    // Array of VirtualSqlColumn instances, describing which columns should be updated in the ON DUPLICATE KEY UPDATE part
    // If none are provided, this query part will not be generated
    'onDuplicateUpdateColumns' => [
        $uuidTable->getColumn('blog_id'),
        $uuidTable->getColumn('resource_id'),
    ]
]);

UPDATE

$query = VirtualSqlQuery::factory(VirtualSqlQuery::TYPE_UPDATE,$table, [
    // Array of VirtualSqlColumn instances, describing which columns to update
    'columns' => $table->getColumns(),
    // Associative array describing the values being updated
    'values' => [
        'column_1' => 'some_value',
        'column_2' => 'some_value,
    ],
    // Array of VirtualSqlJoin instances, describing which joins to perform
    // It's recommended using the query helper methods instead
    'joins' => [
        new VirtualSqlJoin($fromColumn, $toColumn)
    ],
    // VirtualSqlConditionSet instance representing the base condition set
    'where' => new VirtualSqlConditionSet(VirtualSqlConstant::OPERATOR_AND, $conditions),
]);

DELETE

$query = VirtualSqlQuery::factory(VirtualSqlQuery::TYPE_DELETE,$table, [
    // Array of VirtualSqlJoin instances, describing which joins to perform
    // It's recommended using the query helper methods instead
    'joins' => [
        new VirtualSqlJoin($fromColumn, $toColumn)
    ],
    // VirtualSqlConditionSet instance representing the base condition set
    'where' => new VirtualSqlConditionSet(VirtualSqlConstant::OPERATOR_AND, $conditions),
    // int|null representing the LIMIT parameter
    'limit' => 10,
]);

完整示例

<?php
use VirtualSql\Generator\VirtualSqlTableDefinitionGenerator;
use VirtualSql\Query\VirtualSqlQuery;
use VirtualSql\QueryParts\VirtualSqlConditionSetBuilder;

require_once __DIR__.'/vendor/autoload.php';

try {
    $pdo = new PDO('mysql:host='.$_ENV['DB_HOST'].';dbname='.$_ENV['DB_NAME'], $_ENV['DB_USER'], $_ENV['DB_PASS']);
    $pdo->exec("SET NAMES utf8");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    trigger_error($e->getMessage(),E_USER_ERROR);
}

$generator = VirtualSqlTableDefinitionGenerator::getInstance();
$generator->init($pdo);

$table1 = $generator->generateTableDefinition('table_name');
$table2 = $generator->generateTableDefinition('another_table_name');

$query = VirtualSqlQuery::factory(VirtualSqlQuery::TYPE_SELECT,$table1);

// Adding an INNER JOIN statement, with a multidimensional condition on it.
$query->innerJoin(
    $table1->getColumn('id'),
    $table2->getColumn('table1_id'),
    $query->andX(
        $query->condition($table1->getColumn('some_column'),'?'),
        $query->condition($table1->getColumn('another_column'),'?'),
        $query->orX(
            $query->condition($table2->getColumn('a_third_column'),'?'),
            $query->condition($table2->getColumn('a_fourth_column'),'?')
        )
    )
);

// Add an additional, seperate where statement
$query->addWhere($query->andX(
    $query->condition($table2->getColumn('second_table_column'),'some_value'),
    $query->condition($table1->getColumn('another_table_column'),'some_other_value')
));

// Define the selects by referencing specific columns via their table instance.
// If no selects are defined or this method is never called, "*" is the default
$query->setSelects(
    [
        $table1->getColumn('id'),
        $table2->getColumn('the_value_column_on_second_table')
    ]
);

// The actual SQL query string
$sql = $query->getSql();

// An associative array of all the named parameters used in the SQL
$parameters = $query->getNamedParameters();

辅助工具

根据正在处理的 VirtualSqlQuery 类型，实例上存在多个辅助方法来添加/操作各种部分。
例如，为了在 SELECT 查询上创建连接，有 "innerJoin"、"leftJoin"、"rightJoin" 和 "outerJoin" 方法，它们接受一个 $from & $to 列，以及可选的条件集

条件

Virtual SQL 使用 VirtualSqlCondition 实例来描述单个条件，使用 VirtualSqlConditionSet 来描述条件集。
为了帮助构建它们，VirtualSqlQuery 有 3 个辅助方法 "andX"、"orX" 和 "condition"，"andX" 和 "orX" 接受任意数量的参数，这些参数是 VirtualSqlCondition 或 VirtualSqlConditionSet。

$conditionSet = $query->andX(
    $query->condition($table1->getColumn('some_column'),'?'),
    $query->condition($table1->getColumn('another_column'),'?'),
    $query->orX(
        $query->condition($table2->getColumn('a_third_column'),'?'),
        $query->condition($table2->getColumn('a_fourth_column'),'?')
    )
)

实例化后，条件仍然可以操作和添加/删除，正如预期的那样。