packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/CteDmlTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\Sqlite;
6
7use PDO;
8use PHPUnit\Framework\Attributes\CoversNothing;
9use PHPUnit\Framework\Attributes\Large;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Adapter\Pdo\ZtdPdo;
12
13#[CoversNothing]
14#[Large]
15final class CteDmlTest extends TestCase
16{
17    public function testCteDefinitionsRemainVisibleToRewrittenInsertUpdateAndDelete(): void
18    {
19        $raw = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
20        $raw->exec('CREATE TABLE items (id INTEGER PRIMARY KEY, value TEXT)');
21        $ztdPdo = ZtdPdo::fromPdo($raw);
22
23        self::assertSame(2, $ztdPdo->exec("WITH source(id, value) AS (VALUES (1, 'one'), (2, 'two')) INSERT INTO items SELECT * FROM source"));
24        self::assertSame(1, $ztdPdo->exec("WITH chosen AS (SELECT id FROM items WHERE value = 'two') UPDATE items SET value = 'changed' WHERE id IN (SELECT id FROM chosen)"));
25        self::assertSame(1, $ztdPdo->exec("WITH chosen AS (SELECT id FROM items WHERE value = 'one') DELETE FROM items WHERE id IN (SELECT id FROM chosen)"));
26
27        $statement = $ztdPdo->query('SELECT * FROM items');
28        self::assertNotFalse($statement);
29        self::assertSame([['id' => 2, 'value' => 'changed']], $statement->fetchAll(PDO::FETCH_ASSOC));
30    }
31}
32