packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/IndexHintTest.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/**
14 * @requires extension pdo_sqlite
15 */
16#[CoversNothing]
17#[Large]
18final class IndexHintTest extends TestCase
19{
20    public function testSelectIndexedByReadsShadowRows(): void
21    {
22        $rawPdo = new PDO('sqlite::memory:', null, null, [
23            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
24            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
25        ]);
26        $rawPdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL)');
27        $rawPdo->exec('CREATE INDEX idx_category ON products (category)');
28        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
29        $ztdPdo->exec("INSERT INTO products VALUES (1, 'Widget', 'tools', 9.99)");
30
31        $statement = $ztdPdo->query("SELECT name FROM products INDEXED BY idx_category WHERE category = 'tools'");
32
33        self::assertNotFalse($statement);
34        self::assertSame([['name' => 'Widget']], $statement->fetchAll());
35    }
36
37    public function testPreparedSelectIndexedByReadsShadowRows(): void
38    {
39        $rawPdo = new PDO('sqlite::memory:', null, null, [
40            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
41            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
42        ]);
43        $rawPdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL)');
44        $rawPdo->exec('CREATE INDEX idx_category ON products (category)');
45        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
46        $ztdPdo->exec("INSERT INTO products VALUES (1, 'Widget', 'tools', 9.99)");
47
48        $statement = $ztdPdo->prepare('SELECT name FROM products INDEXED BY idx_category WHERE category = ?');
49        self::assertNotFalse($statement);
50        $statement->execute(['tools']);
51
52        self::assertSame([['name' => 'Widget']], $statement->fetchAll());
53    }
54
55    public function testSelectNotIndexedReadsShadowRows(): void
56    {
57        $rawPdo = new PDO('sqlite::memory:', null, null, [
58            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
59            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
60        ]);
61        $rawPdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL)');
62        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
63        $ztdPdo->exec("INSERT INTO products VALUES (1, 'Widget', 'tools', 9.99)");
64
65        $statement = $ztdPdo->query("SELECT name FROM products NOT INDEXED WHERE category = 'tools'");
66
67        self::assertNotFalse($statement);
68        self::assertSame([['name' => 'Widget']], $statement->fetchAll());
69    }
70
71    public function testUpdateIndexedByMutatesShadowRows(): void
72    {
73        $rawPdo = new PDO('sqlite::memory:', null, null, [
74            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
75            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
76        ]);
77        $rawPdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL)');
78        $rawPdo->exec('CREATE INDEX idx_category ON products (category)');
79        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
80        $ztdPdo->exec("INSERT INTO products VALUES (1, 'Widget', 'tools', 9.99)");
81
82        self::assertSame(1, $ztdPdo->exec("UPDATE products INDEXED BY idx_category SET price = 19.99 WHERE category = 'tools'"));
83        $statement = $ztdPdo->query('SELECT price FROM products WHERE id = 1');
84
85        self::assertNotFalse($statement);
86        self::assertSame([['price' => 19.99]], $statement->fetchAll());
87    }
88
89    public function testDeleteIndexedByMutatesShadowRows(): void
90    {
91        $rawPdo = new PDO('sqlite::memory:', null, null, [
92            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
93            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
94        ]);
95        $rawPdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL)');
96        $rawPdo->exec('CREATE INDEX idx_category ON products (category)');
97        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
98        $ztdPdo->exec("INSERT INTO products VALUES (1, 'Widget', 'tools', 9.99)");
99
100        self::assertSame(1, $ztdPdo->exec("DELETE FROM products INDEXED BY idx_category WHERE category = 'tools'"));
101        $statement = $ztdPdo->query('SELECT * FROM products');
102
103        self::assertNotFalse($statement);
104        self::assertSame([], $statement->fetchAll());
105    }
106}
107