packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/SelectTableScopeTest.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 SelectTableScopeTest extends TestCase
19{
20 public function testDerivedAggregateReadsShadowRows(): 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 native_employees (id INTEGER PRIMARY KEY, department TEXT, salary INTEGER)');
27 $rawPdo->exec('CREATE TABLE shadow_employees (id INTEGER PRIMARY KEY, department TEXT, salary INTEGER)');
28 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
29 $rawPdo->exec("INSERT INTO native_employees VALUES (1, 'Engineering', 120000), (2, 'Engineering', 110000)");
30 $ztdPdo->exec("INSERT INTO shadow_employees VALUES (1, 'Engineering', 120000), (2, 'Engineering', 110000)");
31
32 $native = $rawPdo->query('SELECT * FROM (SELECT department, AVG(salary) AS average_salary FROM native_employees GROUP BY department) AS summary WHERE average_salary > 100000');
33 $shadow = $ztdPdo->query('SELECT * FROM (SELECT department, AVG(salary) AS average_salary FROM shadow_employees GROUP BY department) AS summary WHERE average_salary > 100000');
34
35 self::assertNotFalse($native);
36 self::assertNotFalse($shadow);
37 self::assertSame($native->fetchAll(), $shadow->fetchAll());
38 }
39
40 public function testTableValuedFunctionIsNotTreatedAsUnknownTable(): void
41 {
42 $rawPdo = new PDO('sqlite::memory:', null, null, [
43 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
44 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
45 ]);
46 $rawPdo->exec('CREATE TABLE native_items (id INTEGER PRIMARY KEY, tags TEXT)');
47 $rawPdo->exec('CREATE TABLE shadow_items (id INTEGER PRIMARY KEY, tags TEXT)');
48 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
49 $rawPdo->exec('INSERT INTO native_items VALUES (1, \'["php","sql"]\')');
50 $ztdPdo->exec('INSERT INTO shadow_items VALUES (1, \'["php","sql"]\')');
51
52 $native = $rawPdo->query('SELECT native_items.id, json_each.value FROM native_items JOIN json_each(native_items.tags) ORDER BY json_each.value');
53 $shadow = $ztdPdo->query('SELECT shadow_items.id, json_each.value FROM shadow_items JOIN json_each(shadow_items.tags) ORDER BY json_each.value');
54
55 self::assertNotFalse($native);
56 self::assertNotFalse($shadow);
57 self::assertSame($native->fetchAll(), $shadow->fetchAll());
58 }
59
60 public function testDerivedJoinReadsEveryNestedShadowRelation(): void
61 {
62 $rawPdo = new PDO('sqlite::memory:', null, null, [
63 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
64 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
65 ]);
66 $rawPdo->exec('CREATE TABLE native_users (id INTEGER PRIMARY KEY, name TEXT)');
67 $rawPdo->exec('CREATE TABLE native_orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount INTEGER)');
68 $rawPdo->exec('CREATE TABLE shadow_users (id INTEGER PRIMARY KEY, name TEXT)');
69 $rawPdo->exec('CREATE TABLE shadow_orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount INTEGER)');
70 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
71 $rawPdo->exec("INSERT INTO native_users VALUES (1, 'Alice'), (2, 'Bob')");
72 $rawPdo->exec('INSERT INTO native_orders VALUES (1, 1, 30), (2, 1, 20), (3, 2, 10)');
73 $ztdPdo->exec("INSERT INTO shadow_users VALUES (1, 'Alice'), (2, 'Bob')");
74 $ztdPdo->exec('INSERT INTO shadow_orders VALUES (1, 1, 30), (2, 1, 20), (3, 2, 10)');
75
76 $native = $rawPdo->query('SELECT * FROM (SELECT native_users.name, SUM(native_orders.amount) AS total FROM native_users JOIN native_orders ON native_orders.user_id = native_users.id GROUP BY native_users.name) AS totals ORDER BY name');
77 $shadow = $ztdPdo->query('SELECT * FROM (SELECT shadow_users.name, SUM(shadow_orders.amount) AS total FROM shadow_users JOIN shadow_orders ON shadow_orders.user_id = shadow_users.id GROUP BY shadow_users.name) AS totals ORDER BY name');
78
79 self::assertNotFalse($native);
80 self::assertNotFalse($shadow);
81 self::assertSame($native->fetchAll(), $shadow->fetchAll());
82 }
83
84 public function testNestedUnionBranchesReadShadowRows(): void
85 {
86 $rawPdo = new PDO('sqlite::memory:', null, null, [
87 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
88 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
89 ]);
90 $rawPdo->exec('CREATE TABLE native_items (id INTEGER PRIMARY KEY, score INTEGER)');
91 $rawPdo->exec('CREATE TABLE shadow_items (id INTEGER PRIMARY KEY, score INTEGER)');
92 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
93 $rawPdo->exec('INSERT INTO native_items VALUES (1, 10), (2, 20), (3, 30)');
94 $ztdPdo->exec('INSERT INTO shadow_items VALUES (1, 10), (2, 20), (3, 30)');
95
96 $native = $rawPdo->query('SELECT * FROM (SELECT id FROM native_items WHERE score = 10 UNION ALL SELECT id FROM native_items WHERE score = 20 UNION ALL SELECT id FROM native_items WHERE score = 30) AS selected ORDER BY id');
97 $shadow = $ztdPdo->query('SELECT * FROM (SELECT id FROM shadow_items WHERE score = 10 UNION ALL SELECT id FROM shadow_items WHERE score = 20 UNION ALL SELECT id FROM shadow_items WHERE score = 30) AS selected ORDER BY id');
98
99 self::assertNotFalse($native);
100 self::assertNotFalse($shadow);
101 self::assertSame($native->fetchAll(), $shadow->fetchAll());
102 }
103
104 public function testScalarSubqueryAndUserCteReadNestedShadowRelation(): void
105 {
106 $rawPdo = new PDO('sqlite::memory:', null, null, [
107 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
108 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
109 ]);
110 $rawPdo->exec('CREATE TABLE native_users (id INTEGER PRIMARY KEY, name TEXT)');
111 $rawPdo->exec('CREATE TABLE native_orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount INTEGER)');
112 $rawPdo->exec('CREATE TABLE shadow_users (id INTEGER PRIMARY KEY, name TEXT)');
113 $rawPdo->exec('CREATE TABLE shadow_orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount INTEGER)');
114 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
115 $rawPdo->exec("INSERT INTO native_users VALUES (1, 'Alice'), (2, 'Bob')");
116 $rawPdo->exec('INSERT INTO native_orders VALUES (1, 1, 30), (2, 1, 20), (3, 2, 10)');
117 $ztdPdo->exec("INSERT INTO shadow_users VALUES (1, 'Alice'), (2, 'Bob')");
118 $ztdPdo->exec('INSERT INTO shadow_orders VALUES (1, 1, 30), (2, 1, 20), (3, 2, 10)');
119
120 $native = $rawPdo->query('WITH totals AS (SELECT user_id, SUM(amount) AS total FROM native_orders GROUP BY user_id) SELECT native_users.name, (SELECT total FROM totals WHERE totals.user_id = native_users.id) AS total FROM native_users ORDER BY native_users.id');
121 $shadow = $ztdPdo->query('WITH totals AS (SELECT user_id, SUM(amount) AS total FROM shadow_orders GROUP BY user_id) SELECT shadow_users.name, (SELECT total FROM totals WHERE totals.user_id = shadow_users.id) AS total FROM shadow_users ORDER BY shadow_users.id');
122
123 self::assertNotFalse($native);
124 self::assertNotFalse($shadow);
125 self::assertSame($native->fetchAll(), $shadow->fetchAll());
126 }
127
128 public function testExistsSubqueryInSelectListReadsShadowRows(): void
129 {
130 $rawPdo = new PDO('sqlite::memory:', null, null, [
131 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
132 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
133 ]);
134 $rawPdo->exec('CREATE TABLE native_items (id INTEGER PRIMARY KEY)');
135 $rawPdo->exec('CREATE TABLE native_tags (item_id INTEGER, name TEXT)');
136 $rawPdo->exec('CREATE TABLE shadow_items (id INTEGER PRIMARY KEY)');
137 $rawPdo->exec('CREATE TABLE shadow_tags (item_id INTEGER, name TEXT)');
138 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
139 $rawPdo->exec('INSERT INTO native_items VALUES (1), (2)');
140 $rawPdo->exec("INSERT INTO native_tags VALUES (1, 'featured')");
141 $ztdPdo->exec('INSERT INTO shadow_items VALUES (1), (2)');
142 $ztdPdo->exec("INSERT INTO shadow_tags VALUES (1, 'featured')");
143
144 $native = $rawPdo->query("SELECT id, EXISTS(SELECT 1 FROM native_tags WHERE native_tags.item_id = native_items.id AND name = 'featured') AS featured FROM native_items ORDER BY id");
145 $shadow = $ztdPdo->query("SELECT id, EXISTS(SELECT 1 FROM shadow_tags WHERE shadow_tags.item_id = shadow_items.id AND name = 'featured') AS featured FROM shadow_items ORDER BY id");
146
147 self::assertNotFalse($native);
148 self::assertNotFalse($shadow);
149 self::assertSame($native->fetchAll(), $shadow->fetchAll());
150 }
151}
152