packages/ztd-query-postgres/tests/Unit/Rewrite/PgSqlQueryGuardTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Rewrite;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use ZtdQuery\Platform\Postgres\Rewrite\PgSqlQueryGuard;
10use ZtdQuery\Platform\Postgres\Sql\PgSqlParser;
11use ZtdQuery\Rewrite\QueryKind;
12
13#[CoversClass(PgSqlQueryGuard::class)]
14#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Diagnostic\PgSqlReadOnlyDiagnosticStatement::class)]
15#[UsesClass(PgSqlParser::class)]
16#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\PostgreSqlLexicalMasker::class)]
17#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\PgSqlLexerProfile::class)]
18#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Conflict\ColumnSet::class)]
19#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Diagnostic\KeywordSearch::class)]
20#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\FromClause::class)]
21#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\RelationReference::class)]
22#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Statement\StatementClassifier::class)]
23#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Statement\ConflictClause::class)]
24#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Lexing\IdentifierDecoder::class)]
25#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Statement\TargetTableParser::class)]
26#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Dml\Update\UpdateClauseParser::class)]
27#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Dml\Delete\DeleteClauseParser::class)]
28#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Dml\Insert\InsertClauseParser::class)]
29#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Dml\Insert\InsertClauseParser::class)]
30#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Statement\TableDefinitionClauses::class)]
31#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Conflict\PgSqlConflictTarget::class)]
32#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\PgSqlSelectRelationParser::class)]
33#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Lexing\CommentSpan::class)]
34#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Lexing\QuotedSpan::class)]
35final class PgSqlQueryGuardTest extends \PHPUnit\Framework\TestCase
36{
37 public function testSelectClassifiesAsRead(): void
38 {
39 $guard = new PgSqlQueryGuard(new PgSqlParser());
40 self::assertSame(QueryKind::READ, $guard->classify('SELECT * FROM users'));
41 }
42
43 public function testInsertClassifiesAsWriteSimulated(): void
44 {
45 $guard = new PgSqlQueryGuard(new PgSqlParser());
46 self::assertSame(QueryKind::WRITE_SIMULATED, $guard->classify("INSERT INTO users (id, name) VALUES (1, 'Alice')"));
47 }
48
49 public function testUpdateClassifiesAsWriteSimulated(): void
50 {
51 $guard = new PgSqlQueryGuard(new PgSqlParser());
52 self::assertSame(QueryKind::WRITE_SIMULATED, $guard->classify("UPDATE users SET name = 'Bob' WHERE id = 1"));
53 }
54
55 public function testDeleteClassifiesAsWriteSimulated(): void
56 {
57 $guard = new PgSqlQueryGuard(new PgSqlParser());
58 self::assertSame(QueryKind::WRITE_SIMULATED, $guard->classify('DELETE FROM users WHERE id = 1'));
59 }
60
61 public function testCreateTableClassifiesAsDdlSimulated(): void
62 {
63 $guard = new PgSqlQueryGuard(new PgSqlParser());
64 self::assertSame(QueryKind::DDL_SIMULATED, $guard->classify('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)'));
65 }
66
67 public function testDropTableClassifiesAsDdlSimulated(): void
68 {
69 $guard = new PgSqlQueryGuard(new PgSqlParser());
70 self::assertSame(QueryKind::DDL_SIMULATED, $guard->classify('DROP TABLE users'));
71 }
72
73 public function testGarbageInputReturnsNull(): void
74 {
75 self::assertNull((new PgSqlQueryGuard(new PgSqlParser()))->classify('NOT VALID SQL %%% @@@'), 'Garbage input should return null if it is not refused');
76 }
77 public function testMergeClassifiesAsWriteSimulated(): void
78 {
79 $guard = new PgSqlQueryGuard(new PgSqlParser());
80 self::assertSame(QueryKind::WRITE_SIMULATED, $guard->classify('MERGE INTO users USING source ON users.id = source.id WHEN MATCHED THEN DELETE'));
81 }
82 public function testTruncateClassifiesAsWriteSimulated(): void
83 {
84 $guard = new PgSqlQueryGuard(new PgSqlParser());
85 self::assertSame(QueryKind::WRITE_SIMULATED, $guard->classify('TRUNCATE TABLE users'));
86 }
87 public function testAlterTableClassifiesAsDdlSimulated(): void
88 {
89 $guard = new PgSqlQueryGuard(new PgSqlParser());
90 self::assertSame(QueryKind::DDL_SIMULATED, $guard->classify('ALTER TABLE users ADD COLUMN email TEXT'));
91 }
92 public function testBeginClassifiesAsSkipped(): void
93 {
94 $guard = new PgSqlQueryGuard(new PgSqlParser());
95 self::assertSame(QueryKind::SKIPPED, $guard->classify('BEGIN'));
96 }
97 public function testCommitClassifiesAsSkipped(): void
98 {
99 $guard = new PgSqlQueryGuard(new PgSqlParser());
100 self::assertSame(QueryKind::SKIPPED, $guard->classify('COMMIT'));
101 }
102 public function testRollbackClassifiesAsSkipped(): void
103 {
104 $guard = new PgSqlQueryGuard(new PgSqlParser());
105 self::assertSame(QueryKind::SKIPPED, $guard->classify('ROLLBACK'));
106 }
107 public function testUnsupportedReturnsNull(): void
108 {
109 $guard = new PgSqlQueryGuard(new PgSqlParser());
110 self::assertNull($guard->classify('CREATE DATABASE test'));
111 }
112 public function testDoBlockClassifiesAsPassthroughRead(): void
113 {
114 $guard = new PgSqlQueryGuard(new PgSqlParser());
115 self::assertSame(QueryKind::READ, $guard->classify('DO $$ BEGIN INSERT INTO users VALUES (1); END $$'));
116 }
117 public function testWithSelectClassifiesAsRead(): void
118 {
119 $guard = new PgSqlQueryGuard(new PgSqlParser());
120 self::assertSame(QueryKind::READ, $guard->classify('WITH cte AS (SELECT 1) SELECT * FROM cte'));
121 }
122 public function testWithInsertClassifiesAsWriteSimulated(): void
123 {
124 $guard = new PgSqlQueryGuard(new PgSqlParser());
125 self::assertSame(QueryKind::WRITE_SIMULATED, $guard->classify('WITH vals AS (SELECT 1 AS id) INSERT INTO users SELECT * FROM vals'));
126 }
127 public function testGarbageReturnsNull(): void
128 {
129 $guard = new PgSqlQueryGuard(new PgSqlParser());
130 self::assertNull($guard->classify('GIBBERISH NONSENSE'));
131 }
132 public function testCreateTemporaryTableClassifiesAsDdlSimulated(): void
133 {
134 $guard = new PgSqlQueryGuard(new PgSqlParser());
135 self::assertSame(QueryKind::DDL_SIMULATED, $guard->classify('CREATE TEMPORARY TABLE tmp (id INTEGER)'));
136 }
137 public function testDropTableIfExistsClassifiesAsDdlSimulated(): void
138 {
139 $guard = new PgSqlQueryGuard(new PgSqlParser());
140 self::assertSame(QueryKind::DDL_SIMULATED, $guard->classify('DROP TABLE IF EXISTS users'));
141 }
142 public function testEmptyStringReturnsNull(): void
143 {
144 $guard = new PgSqlQueryGuard(new PgSqlParser());
145 self::assertNull($guard->classify(''));
146 }
147 public function testSetCommandReturnsNull(): void
148 {
149 $guard = new PgSqlQueryGuard(new PgSqlParser());
150 self::assertNull($guard->classify('SET search_path TO public'));
151 }
152 public function testShowCommandClassifiesAsRead(): void
153 {
154 $guard = new PgSqlQueryGuard(new PgSqlParser());
155 self::assertSame(QueryKind::READ, $guard->classify('SHOW server_version'));
156 }
157 public function testSavepointClassifiesAsSkipped(): void
158 {
159 $guard = new PgSqlQueryGuard(new PgSqlParser());
160 self::assertSame(QueryKind::SKIPPED, $guard->classify('SAVEPOINT sp1'));
161 }
162 public function testReleaseSavepointClassifiesAsSkipped(): void
163 {
164 $guard = new PgSqlQueryGuard(new PgSqlParser());
165 self::assertSame(QueryKind::SKIPPED, $guard->classify('RELEASE SAVEPOINT sp1'));
166 }
167 public function testNullReturnFromClassifyIsDistinctFromSkipped(): void
168 {
169 $guard = new PgSqlQueryGuard(new PgSqlParser());
170 $result = $guard->classify('GRANT ALL ON users TO admin');
171 self::assertNull($result);
172 self::assertNotSame(QueryKind::SKIPPED, $result);
173 }
174 public function testClassifiesSafeExplainAndRejectsExecutingWrite(): void
175 {
176 $guard = new PgSqlQueryGuard(new PgSqlParser());
177 self::assertSame(QueryKind::READ, $guard->classify('EXPLAIN SELECT 1'));
178 self::assertSame(QueryKind::READ, $guard->classify('EXPLAIN UPDATE users SET active = FALSE'));
179 self::assertSame(QueryKind::READ, $guard->classify('EXPLAIN (ANALYZE TRUE, FORMAT JSON) SELECT 1'));
180 self::assertNull($guard->classify('EXPLAIN ANALYZE UPDATE users SET active = FALSE'));
181 }
182 public function testClassifySelectLowercaseIsRead(): void
183 {
184 $guard = new PgSqlQueryGuard(new PgSqlParser());
185 self::assertSame(QueryKind::READ, $guard->classify('select * from users'));
186 }
187 public function testClassifySetTransactionIsSkipped(): void
188 {
189 $guard = new PgSqlQueryGuard(new PgSqlParser());
190 self::assertSame(QueryKind::SKIPPED, $guard->classify('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'));
191 }
192 public function testClassifyStartTransactionIsSkipped(): void
193 {
194 $guard = new PgSqlQueryGuard(new PgSqlParser());
195 self::assertSame(QueryKind::SKIPPED, $guard->classify('START TRANSACTION'));
196 }
197 public function testClassifyWithSelectIsRead(): void
198 {
199 $guard = new PgSqlQueryGuard(new PgSqlParser());
200 self::assertSame(QueryKind::READ, $guard->classify('WITH cte AS (SELECT 1) SELECT * FROM cte'));
201 }
202 public function testClassifyWithDeleteIsWrite(): void
203 {
204 $guard = new PgSqlQueryGuard(new PgSqlParser());
205 self::assertSame(QueryKind::WRITE_SIMULATED, $guard->classify('WITH old AS (SELECT id FROM users) DELETE FROM users WHERE id IN (SELECT id FROM old)'));
206 }
207 public function testClassifyAlterTableLowercaseIsDdl(): void
208 {
209 $guard = new PgSqlQueryGuard(new PgSqlParser());
210 self::assertSame(QueryKind::DDL_SIMULATED, $guard->classify('alter table users add column email text'));
211 }
212 public function testClassifyDropTableLowercaseIsDdl(): void
213 {
214 $guard = new PgSqlQueryGuard(new PgSqlParser());
215 self::assertSame(QueryKind::DDL_SIMULATED, $guard->classify('drop table users'));
216 }
217 public function testClassifyTruncateLowercaseIsWrite(): void
218 {
219 $guard = new PgSqlQueryGuard(new PgSqlParser());
220 self::assertSame(QueryKind::WRITE_SIMULATED, $guard->classify('truncate table users'));
221 }
222}
223