packages/ztd-query-pdo-adapter/tests/Integration/MySql/NativeUpsertExpressionTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\MySql;
6
7use Container\MySql80Container;
8use Container\MySql84Container;
9use PDO;
10use PHPUnit\Framework\Attributes\CoversNothing;
11use PHPUnit\Framework\Attributes\Large;
12use PHPUnit\Framework\TestCase;
13use ZtdQuery\Adapter\Pdo\ZtdPdo;
14
15/**
16 * @requires extension pdo_mysql
17 * @group integration
18 * @group mysql
19 */
20#[CoversNothing]
21#[Large]
22final class NativeUpsertExpressionTest extends TestCase
23{
24 public function testDatabaseEvaluatesSubqueryUpsertExpression(): void
25 {
26 $containerInstance = \Testcontainers\Testcontainers::run(getenv('MYSQL_VERSION') === '8.4.7' ? MySql84Container::class : MySql80Container::class);
27 /** @var PDO $rawPdo */
28 $rawPdo = new PDO(
29 sprintf('mysql:host=%s;port=%d;dbname=test;charset=utf8mb4', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(3306)),
30 'root',
31 'root',
32 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
33 );
34
35 $databaseName = 'ztd_' . bin2hex(random_bytes(8));
36 $rawPdo->exec(sprintf('CREATE DATABASE `%s` CHARACTER SET utf8mb4', $databaseName));
37 $rawPdo->exec(sprintf('USE `%s`', $databaseName));
38
39
40 try {
41 $rawPdo->exec('CREATE TABLE prices (product_id INT PRIMARY KEY, price DECIMAL(10,2))');
42 $rawPdo->exec('CREATE TABLE price_list (product_id INT PRIMARY KEY, new_price DECIMAL(10,2))');
43 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
44 $rawPdo->exec('INSERT INTO prices VALUES (1, 10.00)');
45 $ztdPdo->exec('INSERT INTO prices VALUES (1, 10.00)');
46 $rawPdo->exec('INSERT INTO price_list VALUES (1, 15.00)');
47 $ztdPdo->exec('INSERT INTO price_list VALUES (1, 15.00)');
48
49 $sql = 'INSERT INTO prices VALUES (1, 0) ON DUPLICATE KEY UPDATE price = (SELECT new_price FROM price_list WHERE product_id = 1)';
50 $rawPdo->exec($sql);
51 $ztdPdo->exec($sql);
52
53 $rawStatement = $rawPdo->query('SELECT price FROM prices');
54 $ztdStatement = $ztdPdo->query('SELECT price FROM prices');
55 self::assertNotFalse($rawStatement);
56 self::assertNotFalse($ztdStatement);
57 self::assertSame($rawStatement->fetchAll(), $ztdStatement->fetchAll());
58 } finally {
59 $rawPdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
60 }
61 }
62
63 public function testDatabaseEvaluatesJsonUpsertExpression(): void
64 {
65 $containerInstance = \Testcontainers\Testcontainers::run(getenv('MYSQL_VERSION') === '8.4.7' ? MySql84Container::class : MySql80Container::class);
66 /** @var PDO $rawPdo */
67 $rawPdo = new PDO(
68 sprintf('mysql:host=%s;port=%d;dbname=test;charset=utf8mb4', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(3306)),
69 'root',
70 'root',
71 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
72 );
73
74 $databaseName = 'ztd_' . bin2hex(random_bytes(8));
75 $rawPdo->exec(sprintf('CREATE DATABASE `%s` CHARACTER SET utf8mb4', $databaseName));
76 $rawPdo->exec(sprintf('USE `%s`', $databaseName));
77
78
79 try {
80 $rawPdo->exec('CREATE TABLE items (id INT PRIMARY KEY, meta JSON)');
81 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
82 $seed = "INSERT INTO items VALUES (1, '{\"color\":\"red\"}')";
83 $rawPdo->exec($seed);
84 $ztdPdo->exec($seed);
85
86 $sql = "INSERT INTO items VALUES (1, '{\"color\":\"purple\"}') ON DUPLICATE KEY UPDATE meta = JSON_SET(meta, '$.color', 'purple')";
87 $rawPdo->exec($sql);
88 $ztdPdo->exec($sql);
89
90 $rawStatement = $rawPdo->query('SELECT CAST(meta AS CHAR) AS meta FROM items');
91 $ztdStatement = $ztdPdo->query('SELECT CAST(meta AS CHAR) AS meta FROM items');
92 self::assertNotFalse($rawStatement);
93 self::assertNotFalse($ztdStatement);
94 self::assertSame($rawStatement->fetchAll(), $ztdStatement->fetchAll());
95 } finally {
96 $rawPdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
97 }
98 }
99
100 public function testDatabaseEvaluatesConditionalUpsertExpression(): void
101 {
102 $containerInstance = \Testcontainers\Testcontainers::run(getenv('MYSQL_VERSION') === '8.4.7' ? MySql84Container::class : MySql80Container::class);
103 /** @var PDO $rawPdo */
104 $rawPdo = new PDO(
105 sprintf('mysql:host=%s;port=%d;dbname=test;charset=utf8mb4', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(3306)),
106 'root',
107 'root',
108 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
109 );
110
111 $databaseName = 'ztd_' . bin2hex(random_bytes(8));
112 $rawPdo->exec(sprintf('CREATE DATABASE `%s` CHARACTER SET utf8mb4', $databaseName));
113 $rawPdo->exec(sprintf('USE `%s`', $databaseName));
114
115
116 try {
117 $rawPdo->exec('CREATE TABLE products (id INT PRIMARY KEY, price DECIMAL(10,2), version INT)');
118 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
119 $rawPdo->exec('INSERT INTO products VALUES (1, 50.00, 5)');
120 $ztdPdo->exec('INSERT INTO products VALUES (1, 50.00, 5)');
121
122 $sql = 'INSERT INTO products VALUES (1, 15.00, 2) ON DUPLICATE KEY UPDATE price = IF(VALUES(version) > version, VALUES(price), price)';
123 $rawPdo->exec($sql);
124 $ztdPdo->exec($sql);
125
126 $rawStatement = $rawPdo->query('SELECT price, version FROM products');
127 $ztdStatement = $ztdPdo->query('SELECT price, version FROM products');
128 self::assertNotFalse($rawStatement);
129 self::assertNotFalse($ztdStatement);
130 self::assertSame($rawStatement->fetchAll(), $ztdStatement->fetchAll());
131 } finally {
132 $rawPdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
133 }
134 }
135}
136