packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/SelectJoinTest.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 * @phpstan-type Row array<string, mixed>
17 */
18#[CoversNothing]
19#[Large]
20final class SelectJoinTest extends TestCase
21{
22 public function testInnerJoin(): void
23 {
24 $rawPdo = new PDO('sqlite::memory:', null, null, [
25 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
26 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
27 ]);
28 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)');
29 $rawPdo->exec("INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
30 $rawPdo->exec('CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, amount INTEGER NOT NULL)');
31 $rawPdo->exec('INSERT INTO orders (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)');
32
33 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
34 $ztdPdo->exec("INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
35 $ztdPdo->exec('INSERT INTO orders (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)');
36
37 $sql = 'SELECT users.name, orders.amount FROM users INNER JOIN orders ON users.id = orders.user_id ORDER BY orders.id';
38
39 $stmt = $rawPdo->query($sql);
40 self::assertNotFalse($stmt);
41 /** @var list<Row> $rawRows */
42 $rawRows = $stmt->fetchAll();
43 $stmt = $ztdPdo->query($sql);
44 self::assertNotFalse($stmt);
45 /** @var list<Row> $ztdRows */
46 $ztdRows = $stmt->fetchAll();
47
48 self::assertSame($rawRows, $ztdRows);
49 }
50
51 public function testLeftJoin(): void
52 {
53 $rawPdo = new PDO('sqlite::memory:', null, null, [
54 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
55 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
56 ]);
57 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)');
58 $rawPdo->exec("INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
59 $rawPdo->exec('CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, amount INTEGER NOT NULL)');
60 $rawPdo->exec('INSERT INTO orders (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)');
61
62 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
63 $ztdPdo->exec("INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
64 $ztdPdo->exec('INSERT INTO orders (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)');
65
66 $sql = 'SELECT users.name, orders.amount FROM users LEFT JOIN orders ON users.id = orders.user_id ORDER BY users.id, orders.id';
67
68 $stmt = $rawPdo->query($sql);
69 self::assertNotFalse($stmt);
70 /** @var list<Row> $rawRows */
71 $rawRows = $stmt->fetchAll();
72 $stmt = $ztdPdo->query($sql);
73 self::assertNotFalse($stmt);
74 /** @var list<Row> $ztdRows */
75 $ztdRows = $stmt->fetchAll();
76
77 self::assertSame($rawRows, $ztdRows);
78 }
79
80 public function testCrossJoin(): void
81 {
82 $rawPdo = new PDO('sqlite::memory:', null, null, [
83 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
84 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
85 ]);
86 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)');
87 $rawPdo->exec("INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
88 $rawPdo->exec('CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, amount INTEGER NOT NULL)');
89 $rawPdo->exec('INSERT INTO orders (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)');
90
91 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
92 $ztdPdo->exec("INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
93 $ztdPdo->exec('INSERT INTO orders (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)');
94
95 $sql = 'SELECT users.name, orders.amount FROM users CROSS JOIN orders ORDER BY users.id, orders.id';
96
97 $stmt = $rawPdo->query($sql);
98 self::assertNotFalse($stmt);
99 /** @var list<Row> $rawRows */
100 $rawRows = $stmt->fetchAll();
101 $stmt = $ztdPdo->query($sql);
102 self::assertNotFalse($stmt);
103 /** @var list<Row> $ztdRows */
104 $ztdRows = $stmt->fetchAll();
105
106 self::assertSame($rawRows, $ztdRows);
107 }
108
109 public function testNaturalJoin(): void
110 {
111 $rawPdo = new PDO('sqlite::memory:', null, null, [
112 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
113 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
114 ]);
115 $rawPdo->exec('CREATE TABLE t1 (id INTEGER PRIMARY KEY, val TEXT NOT NULL)');
116 $rawPdo->exec("INSERT INTO t1 (id, val) VALUES (1, 'a'), (2, 'b')");
117 $rawPdo->exec('CREATE TABLE t2 (id INTEGER PRIMARY KEY, score INTEGER NOT NULL)');
118 $rawPdo->exec('INSERT INTO t2 (id, score) VALUES (1, 100), (2, 200)');
119
120 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
121 $ztdPdo->exec("INSERT INTO t1 (id, val) VALUES (1, 'a'), (2, 'b')");
122 $ztdPdo->exec('INSERT INTO t2 (id, score) VALUES (1, 100), (2, 200)');
123
124 $sql = 'SELECT * FROM t1 NATURAL JOIN t2 ORDER BY id';
125
126 $stmt = $rawPdo->query($sql);
127 self::assertNotFalse($stmt);
128 /** @var list<Row> $rawRows */
129 $rawRows = $stmt->fetchAll();
130 $stmt = $ztdPdo->query($sql);
131 self::assertNotFalse($stmt);
132 /** @var list<Row> $ztdRows */
133 $ztdRows = $stmt->fetchAll();
134
135 self::assertSame($rawRows, $ztdRows);
136 }
137}
138