phpvv/db

带有查询构建器和数据库结构模型的VV数据库抽象层

维护者

详细信息

github.com/phpvv/db

源代码

问题

安装: 192

依赖者: 4

建议者: 0

安全: 0

星标: 0

关注者: 1

分支: 0

开放问题: 0

0.3.0 2021-11-11 21:40 UTC

Requires

  • php: ^8.0

Requires (Dev)

MIT 1d8c0d8e5c0366994442c52df1f7446a22bd6d36

  • Volodymyr Sarnytskyi <v00v4n.woop@gmail.com>

databasedbalsqldbmysqlpostgresqlAbstraction Layerpdopostgresoraclequery buildermysqlioci8

This package is auto-updated.

Last update: 2024-09-12 03:51:12 UTC

README

带有查询构建器和数据库结构模型的VV数据库抽象层。

安装

这是一个基本包。要与其DBMS一起使用，请使用 Composer 安装以下驱动之一

  • db-mysqli - MySQLi扩展的MySQL驱动程序；
  • db-pdo - PDO扩展的PostgreSQL（和其他）驱动程序；
  • db-oci - oci8扩展的Oracle驱动程序。
composer require phpvv/db-mysqli
# or
composer require phpvv/db-pdo
# or
composer require phpvv/db-oci

大型选择示例

big-select.php 在 DB Playground 中

use App\Db\MainDb;
use VV\Db\Param;
use VV\Db\Sql;

$db = MainDb::instance();

$categoryId = 102;
$stateParam = Param::int(value: 1, name: 'state');

$query = $db->tbl->product
    ->select('title', 'b.title brand', 'price', 'c.title as color') // SELECT p.title, ... FROM tbl_product as p
    ->join($db->tbl->brand)                                         // INNER JOIN tbl_brand as b ON b.brand_id = p.brand_id
    ->left($db->tbl->color, 'p')                                    // LEFT JOIN tbl_color as c ON c.color_id = p.color_id
    // ->left($db->tbl->color, on: 'c.color_id = p.color_id', alias: 'c') // same as above
    ->where( // WHERE ...
        Sql::condition()
            ->exists(                                               // WHERE EXISTS (
                $db->tbl->productsCategories->select('1')               // SELECT 1 FROM tbl_products_categories pc
                ->where('`pc`.`product_id`=`p`.`product_id`')           // WHERE ("pc"."product_id"="p"."product_id")
                ->where([
                    'category_id' => $categoryId,                       // AND pc.category_id = :p1
                    'state' => $stateParam                              // AND pc.state = :state
                ])
            )                                                       // )
            ->and('state')->eq($stateParam)                         // AND p.state = :state
            // ->and('title')->like('Comp%')
            ->and('title')->startsWith('Comp')                      // AND p.title LIKE :p2 -- 'Comp%'
            ->and('price')
                ->gte(1000)                                         // AND p.price >= :p3
                ->and
                ->lte(2000)                                         // AND p.price <= :p4
            ->and('weight')->between(7000, 15000)                   // AND p.weight BETWEEN :p5 AND :p6
            ->and(                                                  // AND (
                Sql::condition('width') // nested condition
                    ->lt(500)                                           // p.width > :p7
                    ->or                                                // OR
                    ->isNull()                                          // p.width IS NULL
            )                                                       // )
            ->and('c.color_id')->in(1, 5, null)                     // AND (c.color_id IN (:p8, :p9) OR c.color_id IS NULL)
            ->and('brand_id')->not->in(3, 4)                        // AND (p.brand_id NOT IN (:p10, :p11) OR p.brand_id IS NULL)
            ->and('height')->isNotNull()                            // AND p.height IS NOT NULL
    )
    // ->groupBy(...)
    // ->having(...)
    ->orderBy(                                                      // ORDER BY
        Sql::case('p.color_id')
            ->when(5)->then(1)                                      // color_id = 5 - first
            ->when(1)->then(2)                                      // color_id = 1 - second
            ->else(3),                                              // other colors at the end - first
        // Sql::case()
        //     ->when(Sql::condition('p.color_id')->eq(5))->then(1)    // same CASE as above
        //     ->when(['p.color_id' => 1])->then(2)
        //     ->else(3),
        '-price',                                                   // price DESC,
        'title'                                                     // title ASC
    )
    ->limit(10);                                                    // LIMIT 10


echo "SQL:\n", $query->toString(), "\n\n";

// $rows = $query->rows;
// $rows = $query->rows(\VV\Db::FETCH_NUM);
// $rows = $query->rows(null, keyColumn: 'product_id', decorator: function (&$row, &$key) => { /*...*/ });
// $row = $query->row;
// $row = $query->row(\VV\Db::FETCH_NUM | \VV\Db::FETCH_LOB_NOT_LOAD);
// $title = $query->column;
// $brand = $query->column(1);

/*
$statement = $query->prepare();
print_r($statement->result()->rows);
$stateParam->setValue(0);
print_r($statement->result()->rows);
$statement->close();
*/

echo "rows:\n";
foreach ($query->result as $row) {
    print_r($row);
}

输出

SQL:
SELECT "p"."title", "b"."title" "brand", "p"."price", "c"."title" "color"
FROM "tbl_product" "p"
    JOIN "tbl_brand" "b" ON ("b"."brand_id" = "p"."brand_id")
    LEFT JOIN "tbl_color" "c" ON ("c"."color_id" = "p"."color_id")
WHERE (
    EXISTS (
        SELECT 1
        FROM "tbl_products_categories" "pc"
        WHERE ("pc"."product_id" = "p"."product_id")
            AND "category_id" = :p1
            AND "state" = :state
    )
    AND "p"."state" = :state
    AND "p"."title" LIKE :p2
    AND "p"."price" >= :p3
    AND "p"."price" <= :p4
    AND "p"."weight" BETWEEN :p5 AND :p6
    AND ("p"."width" < :p7 OR "p"."width" IS NULL)
    AND ("c"."color_id" IN (:p8, :p9) OR "c"."color_id" IS NULL)
    AND ("p"."brand_id" NOT IN (:p10, :p11) OR "p"."brand_id" IS NULL)
    AND "p"."height" IS NOT NULL
)
ORDER BY (
        CASE "p"."color_id"
            WHEN 5 THEN 1
            WHEN 1 THEN 2
            ELSE 3
        END
    ) NULLS FIRST,
    "price" DESC NULLS LAST,
    "title" NULLS FIRST
LIMIT 10

rows:
Array
(
    [title] => Computer 11
    [brand] => Brand 2
    [price] => 1500.00
    [color] => White
)
Array
(
    [title] => Computer 10
    [brand] => Brand 1
    [price] => 1000.00
    [color] => Black
)
Array
(
    [title] => Computer 12
    [brand] => Brand 1
    [price] => 1200.00
    [color] =>
)

大型事务示例

big-transaction.php 在 DB Playground 中

use App\Db\MainDb;
use VV\Db\Param;

$db = MainDb::instance();

$userId = 1;
$cart = [
    // productId => quantity
    10 => rand(1, 2),
    20 => rand(1, 3),
    40 => rand(1, 5),
];


$productIterator = $db->tbl->product->select('product_id', 'price')
    ->whereIdIn(...array_keys($cart))
    ->result(\VV\Db::FETCH_NUM);

$transaction = $db->startTransaction();
try {
    $orderId = $db->tbl->order->insert()
        ->set([
            'user_id' => $userId,
            'date_created' => new \DateTime(),
        ])
        ->insertedId($transaction);

    // variants:
    switch (3) {
        case 1:
            // build and execute queries for each item
            foreach ($productIterator as [$productId, $price]) {
                $db->tbl->orderItem->insert()
                    ->set([
                        'order_id' => $orderId,
                        'product_id' => $productId,
                        'price' => $price,
                        'quantity' => $cart[$productId],
                    ])
                    ->exec($transaction);
            }
            break;
        case 2:
            // build and execute one query for all items
            $insertItemQuery = $db->tbl->orderItem->insert()
                ->columns('order_id', 'product_id', 'price', 'quantity');

            foreach ($productIterator as [$productId, $price]) {
                $insertItemQuery->values($orderId, $productId, $price, $cart[$productId]);
            }
            $insertItemQuery->exec($transaction);
            break;
        case 3:
            // prepare query and execute it for each item
            $prepared = $db->tbl->orderItem->insert()
                ->set([
                    'order_id' => Param::int($orderId),
                    'product_id' => $productIdParam = Param::str(size: 16),
                    'price' => $priceParam = Param::str(size: 16),
                    'quantity' => $quantityParam = Param::str(size: 16),
                ]);

            foreach ($productIterator as [$productId, $price]) {
                $productIdParam->setValue($productId);
                $priceParam->setValue($price);
                $quantityParam->setValue($cart[$productId]);

                $prepared->exec($transaction);
            }
            break;
    }

    $db->tbl->order->update()
        ->set(
            'amount',
            $db->tbl->orderItem->select('SUM(price * quantity)')->where('order_id=o.order_id')
        )
        ->whereId($orderId)
        // ->exec() // throws an exception that you are trying to execute statement
                    // outside of transaction started for current connection
        ->exec($transaction);

    // you can execute important statement in transaction free connection
    $db->tbl->log->insert()
        ->set(['title' => "new order #$orderId"])
        ->exec($db->getFreeConnection());

    // throw new \RuntimeException('Test transactionFreeConnection()');

    $transaction->commit();
} catch (\Throwable $e) {
    $transaction->rollback();
    /** @noinspection PhpUnhandledExceptionInspection */
    throw $e;
}

基础知识

仅使用Connection而不使用模式模型表示

示例（《connection.php》）

use APP\DB\MAIN as CONF;
use VV\Db\Connection;
use VV\Db\Pdo\Driver;

// $driver = new \VV\Db\Oci\Driver;
// $driver = new \VV\Db\Mysqli\Driver;
$driver = new Driver(Driver::DBMS_POSTGRES);

$connection = new Connection($driver, CONF\HOST, CONF\USER, CONF\PASSWD, CONF\DBNAME);
// $connection->connect(); // auto connect on first query is enabled by default

// all variants do same job:
$queryString = 'SELECT product_id, title FROM tbl_product WHERE price > :price';
$result = $connection->query($queryString, ['price' => 100]);
// or
$result = $connection->prepare($queryString)->bind(['price' => 100])->result();
// or
$result = $connection->select('product_id', 'title')->from('tbl_product')->where('price > ', 100)->result();

print_r($result->rows);

使用DB模型

配置

在开始时，需要创建某处的class <MyNameOf>Db extends \VV\Db并实现一个抽象方法createConnection()。示例（《App/Db/MainDb.php》）

namespace App\Db;

use APP\DB\MAIN as CONF;
use VV\Db\Connection;
use VV\Db\Pdo\Driver;

/**
 * @method MainDb\TableList tables()
 * @method MainDb\ViewList views()
 * @property-read MainDb\TableList $tbl
 * @property-read MainDb\TableList $vw
 */
class MainDb extends \VV\Db {

    public function createConnection(): Connection {
        $driver = new Driver(Driver::DBMS_POSTGRES);

        return new Connection($driver, CONF\HOST, CONF\USER, CONF\PASSWD, CONF\DBNAME);
    }
}

模型生成

只需运行此代码（《gen-db-model.php”）

use App\Db\MainDb;
use VV\Db\Model\Generator\ModelGenerator;

(new ModelGenerator(MainDb::instance()))->build();

DB模式表示类将在App\Db\MainDb文件夹中创建。

用法

示例（《db-model.php”）

use App\Db\MainDb;

$db = MainDb::instance();

$products = $db->tbl->product
    ->select('product_id', 'b.title brand', 'title', 'price')
    ->join($db->tbl->brand)
    ->where('brand_id', 1)
    ->where('price >', 100)
    ->rows;

print_r($products);

SELECT

创建 SelectQuery

有几种创建SelectQuery的变体（《create-select-query.php”）

use App\Db\MainDb;

$db = MainDb::instance();
$connection = $db->getConnection(); // or $db->getFreeConnection();
// $connection = new \VV\Db\Connection(...);

$columns = ['product_id', 'b.title brand', 'title', 'price'];

// from `Connection` directly:
$selectQuery = $connection->select(...$columns)->from('tbl_product');
// from `Db`:
$selectQuery = $db->select(...$columns)->from('tbl_product');
// from `Table` (recommended):
$selectQuery = $db->tbl->product->select(...$columns);

获取查询结果

从 Result

获取单行或单元格（《execute-select-query.php”）

$query = $db->tbl->product->select('product_id', 'title')->whereId(10);

$row = $query->result->row/*($flags)*/);
$productId = $query->result->cell/*($columnIndex[, $flags])*/);

获取所有行或列（《execute-select-query.php”）

$query = $db->tbl->product->select('product_id', 'title', 'b.title brand')->join($db->tbl->brand)->limit(3);

$result = $query->result;
while (['product_id' => $productId, 'title' => $title] = $result->fetch()) {
    echo "$productId: $title\n";
}
// or
$rowIterator = $query->result(Db::FETCH_NUM);
foreach ($rowIterator as [$productId, $title, $brand]) {
    echo "$productId: $brand $title\n";
}
// or
$rows = $query->result->rows/*($flags)*/;
// or
$assoc = $query->result()->assoc/*(keyColumn: 'product_id'[, valueColumn: 'title'])*/;
//

您可以将fetch模式标志设置为fetch()、row()、rows()或column()

use VV\Db;

$rows = $query->rows(
    Db::FETCH_ASSOC   // column name as key; default fetch mode (if $flags === null)
    | Db::FETCH_NUM   // column index as key
    | Db::FETCH_LOB_OBJECT // return LOB object instead of LOB content (only for Oracle yet)
);

直接从查询获取结果（《execute-select-query.php”）

$query = $db->tbl->product->select('product_id', 'title', 'brand_id')->limit(3);

$assocRows = $query->rows;
$numRows = $query->rows(Db::FETCH_NUM);

$decoratedRows = $query->rows(decorator: function (&$row, &$key) {
    $key = $row['product_id'] . '-' . $row['brand_id'];
    $row = array_values($row);
});

$assoc = $query->rows(keyColumn: 'product_id', decorator: 'title');
$assoc = $query->assoc;

$assocRow = $query->row;
$bothRow = $query->row(Db::FETCH_NUM | Db::FETCH_ASSOC);

$productIdList = $query->column;
$titleList = $query->column(1);

$productId = $query->cell;
$title = $query->cell(1);

SELECT子句

方法select(...)（见上文）返回 SelectQuery对象。您可以使用SelectQuery::columns()或SelectQuery::addColumns()方法更改列（《select.php”）

$query = $db->tbl->product->select()
    ->columns('product_id', 'brand_id')
    ->addColumns('title', 'price');

所有这些方法都接受 string 或 Expression 接口作为每一列。因此，您可以这样做（select.php）

$query = $db->tbl->product->select(
    'product_id',
    'title product',
    $db->tbl->brand
        ->select('title')
        ->where('b.brand_id = p.brand_id')
        ->as('brand'),
    'price',
    Sql::case()
        ->when(['price >= ' => 1000])->then(1)
        ->else(0)
        ->as('is_expensive'),
);

FROM 子句

要设置查询的表或视图，您可以调用 from() 方法或直接从 Table 或 View 创建查询（from.php）

$query = $db->tbl->product->select(/*...*/);
// or
$query = $db->select(/*...*/)->from($db->tbl->product); // same as above
// or
$query = $db->select(/*...*/)->from('tbl_product'); // not same as above regarding JOIN clause

默认情况下，表（或视图）的别名由表（或视图）名称中每个单词的首字母组成，没有前缀（如tbl_、t_、vw_、v_）。例如：tbl_order -> o，tbl_order_item -> oi。

要更改表别名，请调用查询的 mainTableAs() 方法（from.php）

$query = $db->tbl->product->select(/*...*/)->mainTableAs('prod');

JOIN 子句

要设置 JOIN 子句，请使用以下方法：join()、left()、right()、full()。

示例（join.php）

$query = $db->tbl->orderItem->select(/*...*/)  // SELECT ... FROM "tbl_order_item" "oi"
    ->join($db->tbl->order)                    // JOIN "tbl_order" "o" ON "o"."order_id" = "oi"."order_id"
    ->left($db->tbl->orderState)               // LEFT JOIN "tbl_order_state" "os" ON "os"."state_id" = "o"."state_id"
    ->join($db->tbl->product, 'oi')            // JOIN "tbl_product" "p" ON "p"."product_id" = "oi"."product_id"
    ->where('o.state_id', 1);                  // WHERE "o"."state_id" = ?

默认情况下，表通过主键列与前一个表连接。默认的表别名是表名称中每个单词的首字母。您可以更改 ON 条件（第二个参数）和别名（第三个参数）（join.php）

$query = $db->tbl->orderItem->select(/*...*/)
    ->join(
        $db->tbl->order,
        'order.order_id=oi.order_id', // string
        'order'
    )
    ->left(
        $db->tbl->orderState,
        Sql::condition()              // Condition object
            ->and('os.state_id')->eq(Sql::expression('o.state_id'))
            ->and('os.state_id')->eq(1)
    );

ON 条件快捷方式

指定需要连接的表的别名（join.php）

$query = $db->tbl->orderItem->select(/*...*/)
    ->join($db->tbl->order)
    ->join($db->tbl->product, 'oi'); // join to tbl_order_item (not tbl_order) by product_id field

指定需要连接的表的列（join.php）

$query = $db->tbl->orderItem->select(/*...*/)
    ->join($db->tbl->order, '.foo_id'); // "o"."order_id" = "oi"."foo_id"

指定需要连接的表的别名和列（join.php）

$query = $db->tbl->orderItem->select(/*...*/)
    ->join($db->tbl->order)
    ->join($db->tbl->product, 'oi.foo_id'); // "p"."product_id" = "oi"."foo_id"

joinParent()（join.php）

$query = $db->tbl->productCategory->select(/*...*/)
    //->joinParent('pc2', 'pc', 'parent_id') // same as below
    ->joinParent('pc2'); // JOIN "tbl_product_category" "pc2" ON ("pc2"."category_id" = "pc"."parent_id")

joinBack()（join.php）

$query = $db->tbl->order->select(/*...*/)
    ->joinBack($db->tbl->orderItem); // JOIN "tbl_order_item" "oi" ON ("oi"."item_id" = "o"."order_id")

嵌套列

手动嵌套结果列（nested-columns.php）

$query = $db->tbl->product->select('product_id', 'price', 'weight')
    ->addNestedColumns('brand', 'b.brand_id', 'b.title') // first argument is nesting path: string|string[]
    ->addNestedColumns(['nested', 'color'], 'c.color_id', 'c.title')
    ->addNestedColumns(['nested', 'size'], 'width', 'height', 'depth')
    ->join($db->tbl->brand)
    ->join($db->tbl->color, 'p');

print_r($query->row);

结果

Array
(
    [product_id] => 10
    [price] => 1000.00
    [weight] => 10000
    [brand] => Array
        (
            [brand_id] => 1
            [title] => Brand 1
        )

    [nested] => Array
        (
            [color] => Array
                (
                    [color_id] => 1
                    [title] => Black
                )

            [size] => Array
                (
                    [width] => 250.0
                    [height] => 500.0
                    [depth] => 500.0
                )

        )

)

使用 JOIN 嵌套结果列（nested-columns.php）

$query = $db->tbl->orderItem->select('item_id', 'price', 'quantity')
    ->joinNestedColumns(
        $db->tbl->order->select('order_id', 'amount', 'comment'), // sub query
        'oi.order_id',                                            // ON condition (see above)
        ['my', 'nested', 'order']                                 // nesting path
    )
    ->joinNestedColumns(
        $db->tbl->product->select('product_id', 'title')          // sub query
            ->joinNestedColumns(
                $db->tbl->brand->select('brand_id', 'title'),     // sub sub query
                'p.brand_id'
            )
            ->joinNestedColumns($db->tbl->color, 'p', 'color'),   // just join table - select all its columns
        'oi.product_id',
        'product'
    );

print_r($query->row);

结果

Array
(
    [item_id] => 1
    [price] => 1000.00
    [quantity] => 1
    [my] => Array
        (
            [nested] => Array
                (
                    [order] => Array
                        (
                            [order_id] => 1
                            [amount] => 1133.47
                            [comment] =>
                        )

                )

        )

    [product] => Array
        (
            [brand_id] => Array
                (
                    [brand_id] => 1
                    [title] => Brand 1
                )

            [color] => Array
                (
                    [color_id] => 1
                    [title] => Black
                )

            [product_id] => 10
            [title] => Computer 10
        )

)

WHERE 子句

要设置查询条件，请使用 where() 方法。每个 where() 添加 AND 条件。该方法接受

  • Condition 作为第一个参数（where.php）
$query = $db->tbl->product->select(/*...*/)
    ->where(                                // WHERE
        Sql::condition()
            ->and('color_id')->eq(5)            // ("color_id" = ?
            ->and('price')->lte(2000)           // AND "price" <= ?
            ->and('title')->isNotNull()         // AND "title" IS NOT NULL
            ->and('brand_id')->in(2, 3)         // AND "brand_id" IN (?, ?)
            ->and('width')->between(250, 350)   // AND "width" BETWEEN ? AND ?
            ->and('state=1')->custom()          // AND state=1) -- w/o quotes: custom query not changed
    );
  • Expression|string 作为第一个参数，以及作为第二个参数的比较值（或 Expression）（where.php）
$query = $db->tbl->product->select(/*...*/)
    ->where('color_id', 5)      // same: `->where('color_id =', 5)`
    ->where(
        'price <=', // supported operators: = | != | <> | < | > | <= | >=
        $db->tbl->product->select('AVG(price)') // HEAVING clause described below
    )
    ->where('title !=', null);
  • string 作为自定义 SQL 作为第一个参数，以及作为第二个参数的值数组（where.php）
$query = $db->tbl->product->select(/*...*/)
    ->where('`width` BETWEEN ? AND ?', [250, 350])  // custom sql with binding parameters
    ->where('state=1');                             // custom sql w/o binding parameters
  • 数组作为第一个参数（$expression => $parameter）（where.php）
$query = $db->tbl->product->select(/*...*/)
    ->where([
        'color_id' => 5,
        'price <=' => 2000,
        'title !=' => null,
        Sql::condition('brand_id')->eq(2)->or->eq(3),   // AND ("brand_id" = ? OR "brand_id" = ?)
        '`width` BETWEEN ? AND ?' => [250, 350],
        'state=1',
    ]);

WHERE 快捷方式

查询有一些快捷方法

  • ->whereId(1)（对于 $db->tbl->product->select() - product_id = ?）；
  • ->where[Not]In('brand_id', 1, 2, 3);
  • ->whereId[Not]In(1, 2, 3);
  • ->where[Not]Between('width', 250, 350);
  • ->where[Not]Like('title', 'computer%', caseInsensitive: true).

GROUP BY 和 HAVING 子句

要设置 GROUP BY 子句，使用 groupBy() 方法，其行为类似于 columns() （见 列子句）。

要设置聚合条件的条件，使用 having() 方法，其行为类似于 where() （见 WHERE 子句）。

示例 (group-by-having.php)

$query = $db->tbl->product->select('b.title brand', 'COUNT(*) cnt')
    ->join($db->tbl->brand)
    ->groupBy('b.title')
    ->having('COUNT(*) > ', 1);

ORDER BY 子句

简单的按列排序 (order-by.php)

$query = $db->tbl->product->select('b.title brand', 'p.title product', 'price')
    ->left($db->tbl->brand)
    ->orderBy(      // ORDER BY
        'b.title',  //     "b"."title" NULLS FIRST,
        '-price'    //     "price" DESC NULLS LAST
    );

按表达式排序（例如 CASE）(order-by.php)

$query = $db->tbl->product->select('p.title product', 'color_id')
    ->orderBy(                  // ORDER BY
        Sql::case('color_id')   //     CASE "color_id"
            ->when(5)->then(1)  //         WHEN 5 THEN 1
            ->when(1)->then(2)  //         WHEN 1 THEN 2
            ->else(100)         //         ELSE 100 END
    );

LIMIT 子句

使用 ->limit($count, $offset) （limit.php）

$query = $db->tbl->product->select()->orderBy('product_id')->limit(3, 2);

UNION, INTERSECT, EXCEPT 子句

使用 ->union[All](...$queries)，->intersect[All](...$queries)，->except[All](...$queries) （union-intersect-except）

$query = $db->select()
    ->unionAll(
        $db->tbl->product->select('product_id', 'title')->orderBy('product_id')->limit(2),
        $db->tbl->product->select('product_id', 'title')->orderBy('-product_id')->limit(2),
    )
    ->except(
        $db->tbl->product->select('product_id', 'title')->orderBy('product_id')->limit(5, 1),
    )
    ->orderBy('product_id');

INSERT

创建 InsertQuery

创建 InsertQuery 有几种变体（《create-insert-query.php》）

use App\Db\MainDb;

$db = MainDb::instance();
$connection = $db->getConnection(); // or $db->getFreeConnection();
// $connection = new \VV\Db\Connection(...);

// from Connection directly:
$insertQuery = $connection->insert()->into('tbl_order');
// from Db:
$insertQuery = $db->insert()->into('tbl_order');
// from Table (recommended):
$insertQuery = $db->tbl->order->insert();
// $insertQuery = $connection->insert()->into($db->tbl->order);

最后一个变体更可取，因为它会调整插入值的类型以匹配列类型

$db->tbl->foo->insert([
    'int_column' => true, // inserts `1` (or `0` for `false`)
    'bool_column' => 1, // inserts `true` for `1` and `false` for `0` (exception for other numbers)
    'date_column' => new \DateTimeImmutable(), // inserts date only: `Y-m-d`
    'time_column' => new \DateTimeImmutable(), // inserts time only: `H:i:s`
    'timestamp_column' => new \DateTimeImmutable(), // inserts date and time: `Y-m-d H:i:s`
    'timestamp_tz_column' => new \DateTimeImmutable(), // inserts date and time with time zone: `Y-m-d H:i:s P`
]);

执行 InsertQuery

只需执行

$result = $query->exec();

获取插入 ID（自增）或受影响的行（《execute-insert-query.php》）

$result = $query->initInsertedId()->exec();
$id = $result->insertedId();
$affectedRows = $result->affectedRows();

执行查询并返回插入 ID 或受影响的行（《execute-insert-query.php》）

$id = $query->insertedId();             // executes Insert
$affectedRows = $query->affectedRows(); // executes Insert too

插入单行

常规插入查询（《insert-single-row.php》）

$query = $db->tbl->order->insert()
    ->columns('user_id', 'comment')
    ->values(1, 'my comment');

插入赋值列表（《insert-single-row.php”）

$query = $db->tbl->order->insert()
    // ->set([
    //     'user_id' => 1,
    //     'comment' => 'my comment',
    // ])
    ->set('user_id', 1)
    ->set('comment', 'my comment');

快捷方式（执行查询）（《insert-single-row.php”）

$insertedId = $db->tbl->order->insert([
    'user_id' => 1,
    'date_created' => new \DateTime(),
]);

插入多行

插入值列表（《insert-multiple-rows.php”）

$query = $db->tbl->orderItem->insert()->columns('order_id', 'product_id', 'price', 'quantity');
foreach ($valuesList as $values) {
    $query->values(...$values);
}

从 SELECT 插入（《insert-multiple-rows.php”）

// copy order
$newOrderId = $db->tbl->order->insert([
    'user_id' => $userId,
    'date_created' => new \DateTime(),
]);

$query = $db->tbl->orderItem->insert()
    ->columns('order_id', 'product_id', 'price', 'quantity')
    ->values(
        $db->tbl->orderItem
            ->select((string)$newOrderId, 'product_id', 'price', 'quantity')
            ->where('order_id', $orderId)
    );

执行语句每 N 行插入值列表（《insert-multiple-rows.php”）

$query = $db->tbl->orderItem->insert()
    ->columns('order_id', 'product_id', 'price', 'quantity')
    ->execPer(1000);
foreach ($valuesList as $values) {
    $query->values(...$values);
}
$query->execPerFinish(); // exec last

UPDATE

创建 UpdateQuery

创建 UpdateQuery 有几种变体（《create-update-query.php”）

use App\Db\MainDb;

$db = MainDb::instance();
$connection = $db->getConnection(); // or $db->getFreeConnection();
// $connection = new \VV\Db\Connection(...);

// from Connection directly:
$updateQuery = $connection->update()->table('tbl_order');
// from Db:
$updateQuery = $db->update()->table('tbl_order');
// from Table (recommended):
$updateQuery = $db->tbl->order->update();
// $updateQuery = $connection->update()->table($db->tbl->order);

最后一个变体更可取，因为它会调整更新值的类型以匹配列类型

$db->tbl->foo->update([
    'int_column' => true, // sets `1` (or `0` for `false`)
    'bool_column' => 1, // sets `true` for `1` and `false` for `0` (exception for other numbers)
    'date_column' => new \DateTimeImmutable(), // sets date only: `Y-m-d`
    'time_column' => new \DateTimeImmutable(), // sets time only: `H:i:s`
    'timestamp_column' => new \DateTimeImmutable(), // sets date and time: `Y-m-d H:i:s`
    'timestamp_tz_column' => new \DateTimeImmutable(), // sets date and time with time zone: `Y-m-d H:i:s P`
]);

执行 UpdateQuery

只需执行

$result = $query->exec();

获取受影响的行（《execute-update-query.php”）

$affectedRows = $result->affectedRows();

执行查询并返回受影响的行（《execute-update-query.php”）

$affectedRows = $query->affectedRows();

SET 和 WHERE 子句

方法 set() 接受列名作为第一个参数，值（或 Expression）作为第二个参数或数组 column => value。对于 UpdateQuery，必须要有 WHERE 子句。要设置条件，请使用 where() 方法或其快捷方式（见选择查询 WHERE 子句）。要更新所有行，可以设置类似以下的内容： ->where('1=1')。

示例（update.php）

$query = $db->tbl->order->update()
    // ->set([
    //     'amount' => 1000,
    //     'state_id' => 1,
    // ])
    ->set('amount', rand(10_00, 10000_00) * 0.01)
    ->set('state_id', rand(1, 3))
    ->whereId(2);

快捷方式（执行查询）（update.php）

$affectedRows = $db->tbl->order->update(
    [
        'amount' => rand(1_00, 10_000_00) * 0.01,
        'state_id' => rand(1, 3),
    ],
    3
    // ['order_id' => 3]
    // Sql::condition()->and('order_id')->eq(3)
);

使用 Expression 更新（SelectQuery，CaseExpression，...）（update.php）

$query = $db->tbl->order->update()
    ->set(
        'amount',
        $db->tbl->orderItem
            ->select('SUM(price * quantity)')
            ->where('order_id=o.order_id')
    )
    ->where('amount', null);

DELETE

创建 DeleteQuery

创建 DeleteQuery 有几种变体（create-delete-query.php）

use App\Db\MainDb;

$db = MainDb::instance();
$connection = $db->getConnection(); // or $db->getFreeConnection();
// $connection = new \VV\Db\Connection(...);

// from Connection directly:
$deleteQuery = $connection->delete()->into('tbl_order');
// from Db:
$deleteQuery = $db->delete()->into('tbl_order');
// from Table (recommended):
$deleteQuery = $db->tbl->order->delete();
// $deleteQuery = $connection->delete()->from($db->tbl->order);

执行 DeleteQuery

见执行 UpdateQuery 部分

$result = $query->exec();
$affectedRows = $result->affectedRows();
// or
$affectedRows = $query->affectedRows();

WHERE 子句

对于 DeleteQuery，必须要有 WHERE 子句。要设置条件，请使用 where() 方法或其快捷方式（见WHERE 子句部分）。要删除所有行，可以设置类似以下的内容： ->where('1=1')。

示例（delete.php）

$query = $db->tbl->orderItem->delete()
    ->where(
        'price > ',
        $db->tbl->orderItem
            ->select('AVG(price)')
            ->mainTableAs('oi2')
            ->where('oi2.order_id=oi.order_id')
    );

事务

要为连接开始事务，请使用 startTransaction()

$transaction = $connection->startTransaction();
// or
$transaction = $db->startTransaction();

要提交或回滚更改，请使用 Transaction 的 commit() 或 rollback() 方法

try {
    // ...
    $transaction->commit();
} catch (\Throwable $e) {
    $transaction->rollback();
    // ...
}

要在事务中执行查询，请将 Transaction 对象传递给方法 exec($transaction)（或 affectedRows($transaction)，或 insertedId($transaction)）。对于已开始事务的 Connection，如果没有传递 Transaction 到 exec()，则查询执行将抛出异常。为了克服这一点，请使用无事务连接（$db->getFreeConnection()）。

示例（copy-order.php）

$transaction = $db->startTransaction();
try {
    $newOrderId = $db->tbl->order->insert()
        ->set([
            'user_id' => $userId,
            'date_created' => new \DateTime(),
        ])
        ->insertedId($transaction);

    echo "new Order ID: $newOrderId\n";

    $affectedRows = $db->tbl->orderItem->insert()
        ->columns('order_id', 'product_id', 'price', 'quantity')
        ->values(
            $db->tbl->orderItem
                ->select((string)$newOrderId, 'product_id', 'price', 'quantity')
                ->where('order_id', $orderId)
        )
        ->affectedRows($transaction);

    echo "copied Order items: $affectedRows\n";

    $db->tbl->order->update()
        ->set(
            'amount',
            $db->tbl->orderItem->select('SUM(price * quantity)')->where('order_id=o.order_id')
        )
        ->whereId($newOrderId)
        ->exec($transaction);

    // you can execute important statement in transaction free connection
    $db->tbl->log->insert()
        ->set(['title' => "new order copy #$newOrderId"])
        ->exec($db->getFreeConnection());

    // throw new \RuntimeException('Test transactionFreeConnection()');

    $transaction->commit();
} catch (\Throwable $e) {
    $transaction->rollback();
    /** @noinspection PhpUnhandledExceptionInspection */
    throw $e;
}

条件

创建条件

use VV\Db\Sql;
use VV\Db\Sql\Condition;

$condition = new Condition();
// or
$condition = Sql::condition();

要向条件添加新的谓词，首先需要通过 "连接器" 方法（如 and($expression) 或 or($expression)）设置目标表达式。然后调用 "比较" 方法，如 eq($value)、like($value)、in($value1, $value2) 等。

$condition = Sql::condition()
    ->and('foo')->gte(100)
    ->and/*('foo')*/->lte(1000) // if target expression of next predicate is same as previous one
                                // you may omit argument for "connector" method     
    ->and(
        (new Condition())
            ->or('bar')->eq(1)
            ->or('bar')->isNull()
        // Sql::condition('bar')->eq(1)->or->isNull()
    )

比较方法

eq($param) - = ?
ne($param) - != ?
lt($param) - < ?
lte($param) - <= ?
gt($param) - > ?
gte($param) - >= ?
compare($param, $operator) - $operator ?
between($from, $till) - BETWEEN ? AND ?
in(...$params) - IN (?, ?, ?, ...)
isNull() - IS NULL
isNotNull() - IS NOT NULL
like($pattern, $caseInsensitive = false) - LIKE ?
startsWith($prefix, $caseInsensitive = false) - LIKE ?%
endsWith($suffix, $caseInsensitive = false) - LIKE %?
contains($string, $caseInsensitive = false) - LIKE %?%
exists($db->select(...)->where(...) - EXISTS (SELECT ... FROM ... WHERE ...)
and('MY_FUNC(foo, ?, ?)')->custom($param1, $param2) - MY_FUNC(foo, ?, ?)

Case 表达式

$db->select(
        Sql::case('foo')
            ->when(1)->then('first')
            ->when(2)->then('second')
            ->when(3)->then('third')
            ->else('N-th')
            ->as('placement'),
   )
   ->from('bar');
Sql::case()
    ->when(['foo <' => 10])
        ->then('low')
    ->when(Sql::condition('foo')->between(10, 100))
        ->then('middle')
    ->else('high');