packages/ztd-query-postgres/tests/Unit/Sql/PgSqlParserTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Sql;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\TestWith;
9use PHPUnit\Framework\Attributes\UsesClass;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Platform\Postgres\Sql\Conflict\PgSqlConflictTarget;
12use ZtdQuery\Platform\Postgres\Sql\PgSqlParser;
13use ZtdQuery\Platform\Postgres\Sql\PostgreSqlLexicalMasker;
14
15#[CoversClass(PgSqlParser::class)]
16#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\PgSqlSelectRelationParser::class)]
17#[UsesClass(PostgreSqlLexicalMasker::class)]
18#[UsesClass(PgSqlConflictTarget::class)]
19#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\PgSqlLexerProfile::class)]
20#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Statement\StatementClassifier::class)]
21#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Statement\ConflictClause::class)]
22#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Lexing\IdentifierDecoder::class)]
23#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Statement\TargetTableParser::class)]
24#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Dml\Update\UpdateClauseParser::class)]
25#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Dml\Delete\DeleteClauseParser::class)]
26#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Dml\Insert\InsertClauseParser::class)]
27#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Dml\Insert\InsertClauseParser::class)]
28#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Statement\TableDefinitionClauses::class)]
29#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Conflict\ColumnSet::class)]
30#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\FromClause::class)]
31#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\RelationReference::class)]
32#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Lexing\CommentSpan::class)]
33#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Lexing\QuotedSpan::class)]
34final class PgSqlParserTest extends TestCase
35{
36 public function testClassifiesAnonymousDoBlockWithoutSplittingItsBody(): void
37 {
38 $parser = new PgSqlParser();
39 $sql = 'DO $body$ BEGIN INSERT INTO users VALUES (1); UPDATE users SET id = 2; END $body$';
40
41 self::assertSame('DO', $parser->classifyStatement($sql));
42 self::assertSame([$sql], $parser->splitStatements($sql));
43 self::assertSame('DO', $parser->classifyStatement('do language plpgsql $$ begin null; end $$'));
44 self::assertNull($parser->classifyStatement('DOUBLE PRECISION'));
45 self::assertNull($parser->classifyStatement('INVALID DO $$ BEGIN NULL; END $$'));
46 }
47
48 public function testExtractOnConflictUpdateWhereExtractsOnConflictUpdateWhereAfterInsertSelectWhere(): void
49 {
50 $parser = new PgSqlParser();
51 $sql = 'INSERT INTO items SELECT * FROM source WHERE active ON CONFLICT (id) DO UPDATE SET score = EXCLUDED.score WHERE items.score >= 80 RETURNING id';
52
53 self::assertSame('items.score >= 80', $parser->extractOnConflictUpdateWhere($sql));
54 }
55
56 public function testReturnsNullWithoutOnConflictUpdateWhere(): void
57 {
58 $parser = new PgSqlParser();
59
60 self::assertNull($parser->extractOnConflictUpdateWhere(
61 'INSERT INTO items VALUES (1, 90) ON CONFLICT (id) DO UPDATE SET score = EXCLUDED.score',
62 ));
63 }
64
65 public function testRequiresCompleteDoUpdateSetAnchor(): void
66 {
67 $parser = new PgSqlParser();
68
69 self::assertNull($parser->extractOnConflictUpdateWhere(
70 'INSERT INTO items VALUES (1, 90) ON CONFLICT (id) UPDATE SET score = excluded.score WHERE items.score >= 80',
71 ));
72 }
73
74 public function testClassifySelect(): void
75 {
76 $parser = new PgSqlParser();
77 self::assertSame('SELECT', $parser->classifyStatement('SELECT * FROM users'));
78 }
79
80 public function testClassifyInsert(): void
81 {
82 $parser = new PgSqlParser();
83 self::assertSame('INSERT', $parser->classifyStatement('INSERT INTO users (id) VALUES (1)'));
84 }
85
86 public function testClassifyUpdate(): void
87 {
88 $parser = new PgSqlParser();
89 self::assertSame('UPDATE', $parser->classifyStatement("UPDATE users SET name = 'x'"));
90 }
91
92 public function testClassifyDelete(): void
93 {
94 $parser = new PgSqlParser();
95 self::assertSame('DELETE', $parser->classifyStatement('DELETE FROM users WHERE id = 1'));
96 }
97
98 public function testClassifiesMergeWithAndWithoutCtePrefix(): void
99 {
100 $parser = new PgSqlParser();
101
102 self::assertSame('MERGE', $parser->classifyStatement(
103 'MERGE INTO users USING source ON users.id = source.id WHEN MATCHED THEN DELETE',
104 ));
105 self::assertSame('MERGE', $parser->classifyStatement(
106 'WITH source AS (SELECT 1 AS id) '
107 . 'MERGE INTO users USING source ON users.id = source.id WHEN MATCHED THEN DELETE',
108 ));
109 self::assertSame('MERGE', $parser->classifyStatement(
110 'merge into users using source on users.id = source.id when matched then delete',
111 ));
112 self::assertSame('SELECT', $parser->classifyStatement('SELECT 1 AS merge_marker'));
113 self::assertNull($parser->classifyStatement('INVALID MERGE'));
114 self::assertNull($parser->classifyStatement('WITH source AS (SELECT 1) INVALID'));
115 }
116
117 public function testClassifyTruncate(): void
118 {
119 $parser = new PgSqlParser();
120 self::assertSame('TRUNCATE', $parser->classifyStatement('TRUNCATE TABLE users'));
121 }
122
123 public function testClassifyCreateTable(): void
124 {
125 $parser = new PgSqlParser();
126 self::assertSame('CREATE_TABLE', $parser->classifyStatement('CREATE TABLE users (id INTEGER)'));
127 }
128
129 public function testClassifyDropTable(): void
130 {
131 $parser = new PgSqlParser();
132 self::assertSame('DROP_TABLE', $parser->classifyStatement('DROP TABLE users'));
133 }
134
135 public function testClassifyAlterTable(): void
136 {
137 $parser = new PgSqlParser();
138 self::assertSame('ALTER_TABLE', $parser->classifyStatement('ALTER TABLE users ADD COLUMN name TEXT'));
139 }
140
141 public function testClassifyWithSelect(): void
142 {
143 $parser = new PgSqlParser();
144 self::assertSame('SELECT', $parser->classifyStatement('WITH cte AS (SELECT 1) SELECT * FROM cte'));
145 }
146
147 public function testClassifyWithInsert(): void
148 {
149 $parser = new PgSqlParser();
150 self::assertSame('INSERT', $parser->classifyStatement('WITH vals AS (SELECT 1) INSERT INTO users SELECT * FROM vals'));
151 }
152
153 public function testClassifyTcl(): void
154 {
155 $parser = new PgSqlParser();
156 self::assertSame('TCL', $parser->classifyStatement('BEGIN'));
157 self::assertSame('TCL', $parser->classifyStatement('COMMIT'));
158 self::assertSame('TCL', $parser->classifyStatement('ROLLBACK'));
159 }
160
161 public function testClassifyUnknownReturnsNull(): void
162 {
163 $parser = new PgSqlParser();
164 self::assertNull($parser->classifyStatement('CREATE DATABASE test'));
165 }
166
167 public function testClassifyWithLeadingComments(): void
168 {
169 $parser = new PgSqlParser();
170 self::assertSame('SELECT', $parser->classifyStatement('-- comment
171SELECT * FROM users'));
172 }
173
174 public function testSplitSingleStatement(): void
175 {
176 $parser = new PgSqlParser();
177 $result = $parser->splitStatements('SELECT 1');
178 self::assertCount(1, $result);
179 self::assertSame('SELECT 1', $result[0]);
180 }
181
182 public function testSplitMultipleStatements(): void
183 {
184 $parser = new PgSqlParser();
185 $result = $parser->splitStatements('SELECT 1; SELECT 2');
186 self::assertCount(2, $result);
187 }
188
189 public function testSplitHandlesStringLiterals(): void
190 {
191 $parser = new PgSqlParser();
192 $result = $parser->splitStatements("SELECT 'a;b'");
193 self::assertCount(1, $result);
194 }
195
196 public function testSplitHandlesDollarQuoting(): void
197 {
198 $parser = new PgSqlParser();
199 $result = $parser->splitStatements('SELECT $$hello;world$$');
200 self::assertCount(1, $result);
201 }
202
203 public function testExtractInsertTable(): void
204 {
205 $parser = new PgSqlParser();
206 self::assertSame('users', $parser->extractInsertTable('INSERT INTO users (id) VALUES (1)'));
207 }
208
209 public function testExtractInsertTableQuoted(): void
210 {
211 $parser = new PgSqlParser();
212 self::assertSame('users', $parser->extractInsertTable('INSERT INTO "users" (id) VALUES (1)'));
213 }
214
215 public function testExtractInsertTableSchemaQualified(): void
216 {
217 $parser = new PgSqlParser();
218 self::assertSame('users', $parser->extractInsertTable('INSERT INTO public.users (id) VALUES (1)'));
219 }
220
221 public function testExtractInsertColumns(): void
222 {
223 $parser = new PgSqlParser();
224 $cols = $parser->extractInsertColumns("INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'a@b.com')");
225 self::assertSame(['id', 'name', 'email'], $cols);
226 }
227
228 public function testExtractInsertColumnsQuoted(): void
229 {
230 $parser = new PgSqlParser();
231 $cols = $parser->extractInsertColumns('INSERT INTO users ("id", "name") VALUES (1, \'x\')');
232 self::assertSame(['id', 'name'], $cols);
233 }
234
235 public function testExtractInsertValuesSingle(): void
236 {
237 $parser = new PgSqlParser();
238 $rows = $parser->extractInsertValues("INSERT INTO users (id, name) VALUES (1, 'Alice')");
239 self::assertCount(1, $rows);
240 self::assertSame(['1', "'Alice'"], $rows[0]);
241 }
242
243 public function testExtractInsertValuesMultiple(): void
244 {
245 $parser = new PgSqlParser();
246 $rows = $parser->extractInsertValues('INSERT INTO users (id) VALUES (1), (2), (3)');
247 self::assertCount(3, $rows);
248 }
249
250 public function testHasOnConflict(): void
251 {
252 $parser = new PgSqlParser();
253 self::assertTrue($parser->hasOnConflict('INSERT INTO users (id) VALUES (1) ON CONFLICT (id) DO NOTHING'));
254 }
255
256 public function testHasOnConflictFalse(): void
257 {
258 $parser = new PgSqlParser();
259 self::assertFalse($parser->hasOnConflict('INSERT INTO users (id) VALUES (1)'));
260 }
261
262 public function testExtractOnConflictUpdateColumns(): void
263 {
264 $parser = new PgSqlParser();
265 $result = $parser->extractOnConflictUpdateColumns(
266 "INSERT INTO users (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name"
267 );
268 self::assertSame(['name'], $result['columns']);
269 self::assertSame('EXCLUDED.name', $result['values']['name']);
270 }
271
272 public function testExtractUpdateTable(): void
273 {
274 $parser = new PgSqlParser();
275 self::assertSame('users', $parser->extractUpdateTable("UPDATE users SET name = 'x'"));
276 }
277
278 public function testExtractUpdateTableSchemaQualified(): void
279 {
280 $parser = new PgSqlParser();
281 self::assertSame('users', $parser->extractUpdateTable("UPDATE public.users SET name = 'x'"));
282 }
283
284 public function testExtractUpdateSets(): void
285 {
286 $parser = new PgSqlParser();
287 $sets = $parser->extractUpdateSets("UPDATE users SET name = 'Bob', age = 30 WHERE id = 1");
288 self::assertSame("'Bob'", $sets['name']);
289 self::assertSame('30', $sets['age']);
290 }
291
292 public function testExtractDeleteTable(): void
293 {
294 $parser = new PgSqlParser();
295 self::assertSame('users', $parser->extractDeleteTable('DELETE FROM users WHERE id = 1'));
296 }
297
298 public function testExtractDeleteTableSchemaQualified(): void
299 {
300 $parser = new PgSqlParser();
301 self::assertSame('users', $parser->extractDeleteTable('DELETE FROM public.users WHERE id = 1'));
302 }
303
304 public function testExtractTruncateTable(): void
305 {
306 $parser = new PgSqlParser();
307 self::assertSame('users', $parser->extractTruncateTable('TRUNCATE TABLE users'));
308 }
309
310 public function testExtractTruncateTableWithCascade(): void
311 {
312 $parser = new PgSqlParser();
313 self::assertSame('users', $parser->extractTruncateTable('TRUNCATE TABLE users CASCADE'));
314 }
315
316 public function testExtractTruncateTablesPreservesEveryTarget(): void
317 {
318 $parser = new PgSqlParser();
319
320 self::assertSame(
321 ['Alpha', 'beta', 'gamma'],
322 $parser->extractTruncateTables(
323 'TRUNCATE TABLE ONLY public."Alpha" *, /* next */ ONLY beta, gamma RESTART IDENTITY CASCADE',
324 ),
325 );
326 }
327
328 public function testExtractTruncateTablesSupportsUnicodeQuotedIdentifiers(): void
329 {
330 $parser = new PgSqlParser();
331
332 self::assertSame(
333 ['select', 'second', 'third'],
334 $parser->extractTruncateTables('TRUNCATE TABLE U&"select", public.U&"second", "third"'),
335 );
336 }
337
338 public function testExtractTruncateTablesRejectsNonTruncateAndIncompleteTargets(): void
339 {
340 $parser = new PgSqlParser();
341
342 self::assertSame([], $parser->extractTruncateTables('SELECT users'));
343 self::assertSame([], $parser->extractTruncateTables('TRUNCATE TABLE'));
344 self::assertSame(['u'], $parser->extractTruncateTables('TRUNCATE u'));
345 self::assertSame([], $parser->extractTruncateTables('TRUNCATE TABLE U&'));
346 self::assertSame([], $parser->extractTruncateTables('TRUNCATE TABLE U&users'));
347 }
348
349 public function testExtractCreateTableName(): void
350 {
351 $parser = new PgSqlParser();
352 self::assertSame('users', $parser->extractCreateTableName('CREATE TABLE users (id INTEGER)'));
353 }
354
355 public function testExtractCreateTableNameIfNotExists(): void
356 {
357 $parser = new PgSqlParser();
358 self::assertSame('users', $parser->extractCreateTableName('CREATE TABLE IF NOT EXISTS users (id INTEGER)'));
359 }
360
361 public function testHasIfNotExists(): void
362 {
363 $parser = new PgSqlParser();
364 self::assertTrue($parser->hasIfNotExists('CREATE TABLE IF NOT EXISTS users (id INTEGER)'));
365 self::assertFalse($parser->hasIfNotExists('CREATE TABLE users (id INTEGER)'));
366 }
367
368 public function testHasCreateTableAsSelect(): void
369 {
370 $parser = new PgSqlParser();
371 self::assertTrue($parser->hasCreateTableAsSelect('CREATE TABLE users AS SELECT * FROM old_users'));
372 self::assertFalse($parser->hasCreateTableAsSelect('CREATE TABLE users (id INTEGER)'));
373 }
374
375 public function testExtractDropTableName(): void
376 {
377 $parser = new PgSqlParser();
378 self::assertSame('users', $parser->extractDropTableName('DROP TABLE users'));
379 }
380
381 public function testHasDropTableIfExists(): void
382 {
383 $parser = new PgSqlParser();
384 self::assertTrue($parser->hasDropTableIfExists('DROP TABLE IF EXISTS users'));
385 self::assertFalse($parser->hasDropTableIfExists('DROP TABLE users'));
386 }
387
388 public function testExtractAlterTableName(): void
389 {
390 $parser = new PgSqlParser();
391 self::assertSame('users', $parser->extractAlterTableName('ALTER TABLE users ADD COLUMN email TEXT'));
392 }
393
394 public function testStripSchemaPrefix(): void
395 {
396 $parser = new PgSqlParser();
397 self::assertSame('users', $parser->stripSchemaPrefix('public.users'));
398 self::assertSame('"users"', $parser->stripSchemaPrefix('"public"."users"'));
399 self::assertSame('users', $parser->stripSchemaPrefix('users'));
400 }
401
402 public function testUnquoteIdentifier(): void
403 {
404 $parser = new PgSqlParser();
405 self::assertSame('users', $parser->unquoteIdentifier('"users"'));
406 self::assertSame('users', $parser->unquoteIdentifier('users'));
407 }
408
409 public function testExtractSelectTableNames(): void
410 {
411 $parser = new PgSqlParser();
412 $tables = $parser->extractSelectTableNames('SELECT * FROM users JOIN orders ON users.id = orders.user_id');
413 self::assertContains('users', $tables);
414 self::assertContains('orders', $tables);
415 }
416
417 public function testExtractWhereClause(): void
418 {
419 $parser = new PgSqlParser();
420 $where = $parser->extractWhereClause('DELETE FROM users WHERE id = 1');
421 self::assertSame('id = 1', $where);
422 }
423
424 public function testClassifyWithLowercase(): void
425 {
426 $parser = new PgSqlParser();
427 self::assertSame('SELECT', $parser->classifyStatement('with cte as (select 1) select * from cte'));
428 }
429
430 public function testClassifySelectNotMatchedByWith(): void
431 {
432 $parser = new PgSqlParser();
433 self::assertSame('SELECT', $parser->classifyStatement('SELECT * FROM with_table'));
434 }
435
436 public function testClassifyWithUpdate(): void
437 {
438 $parser = new PgSqlParser();
439 self::assertSame('UPDATE', $parser->classifyStatement('WITH cte AS (SELECT 1) UPDATE users SET x = 1'));
440 }
441
442 public function testClassifyWithDelete(): void
443 {
444 $parser = new PgSqlParser();
445 self::assertSame('DELETE', $parser->classifyStatement('WITH cte AS (SELECT 1) DELETE FROM users'));
446 }
447
448 public function testClassifyWithNoTerminalKeyword(): void
449 {
450 $parser = new PgSqlParser();
451 self::assertNull($parser->classifyStatement('WITH cte AS (SELECT 1)'));
452 }
453
454 public function testClassifyWithBlockComment(): void
455 {
456 $parser = new PgSqlParser();
457 self::assertSame('SELECT', $parser->classifyStatement("/* comment */\nSELECT 1"));
458 }
459
460 public function testClassifyWithBlockCommentNoNewline(): void
461 {
462 $parser = new PgSqlParser();
463 self::assertSame('SELECT', $parser->classifyStatement('/* comment */ SELECT 1'));
464 }
465
466 public function testExtractSelectTableAfterBlockComment(): void
467 {
468 $parser = new PgSqlParser();
469 self::assertSame(['my_table'], $parser->extractSelectTableNames('SELECT * FROM/* table */my_table'));
470 }
471
472 public function testExtractSelectTableIgnoresKeywordsInsideLineComment(): void
473 {
474 $parser = new PgSqlParser();
475 $sql = "-- SELECT * FROM other_table WHERE DELETE UPDATE INSERT\nSELECT * FROM items ORDER BY id";
476 self::assertSame(['items'], $parser->extractSelectTableNames($sql));
477 }
478
479 public function testExtractUpdateTableAfterNestedBlockComment(): void
480 {
481 $parser = new PgSqlParser();
482 self::assertSame('my_table', $parser->extractUpdateTable('UPDATE/* outer /* inner */ outer */my_table SET value = 1'));
483 }
484
485 public function testExtractDeleteTableAfterBlockComment(): void
486 {
487 $parser = new PgSqlParser();
488 self::assertSame('my_table', $parser->extractDeleteTable('DELETE FROM/* table */my_table WHERE id = 1'));
489 }
490
491 public function testExtractInsertTableAfterBlockComment(): void
492 {
493 $parser = new PgSqlParser();
494 self::assertSame('my_table', $parser->extractInsertTable('INSERT INTO/* table */my_table VALUES (1)'));
495 }
496
497 public function testCommentMarkersInsideQuotedValuesRemainData(): void
498 {
499 $parser = new PgSqlParser();
500 $sql = 'UPDATE users SET note = \'/* not comment */\', payload = $tag$-- not comment$tag$';
501 $sets = $parser->extractUpdateSets($sql);
502 self::assertSame("'/* not comment */'", $sets['note']);
503 self::assertSame('$tag$-- not comment$tag$', $sets['payload']);
504 }
505
506 public function testExtractWhereClauseAfterDoubledQuoteString(): void
507 {
508 $parser = new PgSqlParser();
509 $where = $parser->extractWhereClause("UPDATE test SET body = 'it''s updated' WHERE id = 1");
510 self::assertSame('id = 1', $where);
511 }
512
513 public function testExtractWhereClauseAfterDollarQuotedString(): void
514 {
515 $parser = new PgSqlParser();
516 $where = $parser->extractWhereClause('UPDATE notes SET body = $$reference to notes table$$ WHERE id = 1');
517 self::assertSame('id = 1', $where);
518 }
519
520 public function testExtractWhereClauseAfterTaggedDollarQuotedString(): void
521 {
522 $parser = new PgSqlParser();
523 $where = $parser->extractWhereClause('UPDATE notes SET body = $text$WHERE id = fake$text$ WHERE id = 1');
524 self::assertSame('id = 1', $where);
525 }
526
527 public function testExtractWhereClauseAfterEscapeString(): void
528 {
529 $parser = new PgSqlParser();
530 $where = $parser->extractWhereClause("UPDATE docs SET content = E'line1\\nline2' WHERE id = 1");
531 self::assertSame('id = 1', $where);
532 }
533
534 public function testClassifyCreateTempTable(): void
535 {
536 $parser = new PgSqlParser();
537 self::assertSame('CREATE_TABLE', $parser->classifyStatement('CREATE TEMPORARY TABLE t (id INT)'));
538 self::assertSame('CREATE_TABLE', $parser->classifyStatement('CREATE TEMP TABLE t (id INT)'));
539 self::assertSame('CREATE_TABLE', $parser->classifyStatement('CREATE UNLOGGED TABLE t (id INT)'));
540 }
541
542 public function testClassifyTclVariants(): void
543 {
544 $parser = new PgSqlParser();
545 self::assertSame('TCL', $parser->classifyStatement('START TRANSACTION'));
546 self::assertSame('TCL', $parser->classifyStatement('SAVEPOINT sp1'));
547 self::assertSame('TCL', $parser->classifyStatement('RELEASE SAVEPOINT sp1'));
548 self::assertSame('TCL', $parser->classifyStatement('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'));
549 }
550
551 public function testSplitMultipleExactContent(): void
552 {
553 $parser = new PgSqlParser();
554 $result = $parser->splitStatements('SELECT 1; SELECT 2');
555 self::assertSame(['SELECT 1', 'SELECT 2'], $result);
556 }
557
558 public function testSplitEmptyInput(): void
559 {
560 $parser = new PgSqlParser();
561 self::assertSame([], $parser->splitStatements(''));
562 }
563
564 public function testSplitWhitespaceOnly(): void
565 {
566 $parser = new PgSqlParser();
567 self::assertSame([], $parser->splitStatements(' '));
568 }
569
570 public function testSplitTrailingSemicolon(): void
571 {
572 $parser = new PgSqlParser();
573 $result = $parser->splitStatements('SELECT 1;');
574 self::assertSame(['SELECT 1'], $result);
575 }
576
577 public function testSplitEscapedSingleQuote(): void
578 {
579 $parser = new PgSqlParser();
580 $result = $parser->splitStatements("SELECT 'it''s'; SELECT 2");
581 self::assertSame(2, count($result));
582 self::assertStringContainsString("it''s", $result[0]);
583 self::assertSame('SELECT 2', $result[1]);
584 }
585
586 public function testSplitDoubleQuotedIdentifier(): void
587 {
588 $parser = new PgSqlParser();
589 $result = $parser->splitStatements('SELECT "col;name" FROM t; SELECT 2');
590 self::assertSame(2, count($result));
591 self::assertStringContainsString('"col;name"', $result[0]);
592 }
593
594 public function testSplitEscapedDoubleQuote(): void
595 {
596 $parser = new PgSqlParser();
597 $result = $parser->splitStatements('SELECT "a""b" FROM t; SELECT 2');
598 self::assertSame(2, count($result));
599 self::assertStringContainsString('"a""b"', $result[0]);
600 }
601
602 public function testSplitLineComment(): void
603 {
604 $parser = new PgSqlParser();
605 $result = $parser->splitStatements("SELECT 1 -- comment;\n; SELECT 2");
606 self::assertSame(2, count($result));
607 }
608
609 public function testSplitBlockComment(): void
610 {
611 $parser = new PgSqlParser();
612 $result = $parser->splitStatements('SELECT /* ; */ 1; SELECT 2');
613 self::assertSame(2, count($result));
614 self::assertStringContainsString('/* ; */', $result[0]);
615 }
616
617 public function testSplitDollarQuoteWithTag(): void
618 {
619 $parser = new PgSqlParser();
620 $result = $parser->splitStatements('SELECT $tag$hello;world$tag$; SELECT 2');
621 self::assertSame(2, count($result));
622 self::assertStringContainsString('hello;world', $result[0]);
623 }
624
625 public function testExtractInsertTableOnly(): void
626 {
627 $parser = new PgSqlParser();
628 self::assertSame('t', $parser->extractInsertTable('INSERT INTO ONLY t (id) VALUES (1)'));
629 }
630
631 public function testExtractInsertTableWithAlias(): void
632 {
633 $parser = new PgSqlParser();
634 self::assertSame('users', $parser->extractInsertTable('INSERT INTO users AS u (id) VALUES (1)'));
635 }
636
637 public function testExtractInsertTableReturnsNullOnInvalid(): void
638 {
639 $parser = new PgSqlParser();
640 self::assertNull($parser->extractInsertTable('SELECT 1'));
641 }
642
643 public function testExtractInsertColumnsEmpty(): void
644 {
645 $parser = new PgSqlParser();
646 self::assertSame([], $parser->extractInsertColumns('INSERT INTO users VALUES (1)'));
647 }
648
649 public function testExtractInsertColumnsWithSelect(): void
650 {
651 $parser = new PgSqlParser();
652 $cols = $parser->extractInsertColumns('INSERT INTO users (id, name) SELECT id, name FROM old');
653 self::assertSame(['id', 'name'], $cols);
654 }
655
656 public function testExtractInsertValuesEmpty(): void
657 {
658 $parser = new PgSqlParser();
659 self::assertSame([], $parser->extractInsertValues('INSERT INTO users SELECT * FROM old'));
660 }
661
662 public function testExtractInsertValuesWithSubquery(): void
663 {
664 $parser = new PgSqlParser();
665 $rows = $parser->extractInsertValues('INSERT INTO t (id, v) VALUES (1, (SELECT max(id) FROM t2))');
666 self::assertSame(1, count($rows));
667 self::assertSame('1', $rows[0][0]);
668 self::assertSame('(SELECT max(id) FROM t2)', $rows[0][1]);
669 }
670
671 public function testExtractInsertValuesWithEscapedQuote(): void
672 {
673 $parser = new PgSqlParser();
674 $rows = $parser->extractInsertValues("INSERT INTO t (v) VALUES ('it''s')");
675 self::assertSame(1, count($rows));
676 self::assertSame("'it''s'", $rows[0][0]);
677 }
678
679 public function testExtractOnConflictUpdateColumnsNoConflict(): void
680 {
681 $parser = new PgSqlParser();
682 $result = $parser->extractOnConflictUpdateColumns('INSERT INTO t (id) VALUES (1)');
683 self::assertSame([], $result['columns']);
684 self::assertSame([], $result['values']);
685 }
686
687 public function testExtractOnConflictUpdateColumnsMultiple(): void
688 {
689 $parser = new PgSqlParser();
690 $result = $parser->extractOnConflictUpdateColumns(
691 'INSERT INTO t (id, a, b) VALUES (1, 2, 3) ON CONFLICT (id) DO UPDATE SET a = EXCLUDED.a, b = EXCLUDED.b'
692 );
693 self::assertSame(['a', 'b'], $result['columns']);
694 self::assertSame('EXCLUDED.a', $result['values']['a']);
695 self::assertSame('EXCLUDED.b', $result['values']['b']);
696 }
697
698 public function testExtractOnConflictWithWhereClause(): void
699 {
700 $parser = new PgSqlParser();
701 $result = $parser->extractOnConflictUpdateColumns(
702 'INSERT INTO t (id, v) VALUES (1, 2) ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v WHERE t.v < EXCLUDED.v'
703 );
704 self::assertSame(['v'], $result['columns']);
705 self::assertSame('EXCLUDED.v', $result['values']['v']);
706 }
707
708 public function testExtractOnConflictStopsBeforeReturningWithoutWhere(): void
709 {
710 $parser = new PgSqlParser();
711 $result = $parser->extractOnConflictUpdateColumns(
712 'INSERT INTO t (id, v) VALUES (1, 2) ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v RETURNING id'
713 );
714
715 self::assertSame(['v'], $result['columns']);
716 self::assertSame('EXCLUDED.v', $result['values']['v']);
717 }
718
719 public function testExtractOnConflictRejectsNonUpdateActionContainingSet(): void
720 {
721 $parser = new PgSqlParser();
722
723 self::assertSame(
724 ['columns' => [], 'values' => []],
725 $parser->extractOnConflictUpdateColumns(
726 'INSERT INTO t (id, v) VALUES (1, 2) ON CONFLICT (id) DO NOTHING SET v = EXCLUDED.v'
727 ),
728 );
729 }
730
731 public function testExtractOnConflictQuotedColumn(): void
732 {
733 $parser = new PgSqlParser();
734 $result = $parser->extractOnConflictUpdateColumns(
735 'INSERT INTO t (id, "Name") VALUES (1, \'x\') ON CONFLICT (id) DO UPDATE SET "Name" = EXCLUDED."Name"'
736 );
737 self::assertSame(['Name'], $result['columns']);
738 }
739
740 public function testHasInsertSelect(): void
741 {
742 $parser = new PgSqlParser();
743 self::assertTrue($parser->hasInsertSelect('INSERT INTO t SELECT * FROM old'));
744 self::assertFalse($parser->hasInsertSelect('INSERT INTO t VALUES (1)'));
745 }
746
747 public function testHasInsertSelectWithValueLookingLikeSelect(): void
748 {
749 $parser = new PgSqlParser();
750 self::assertFalse($parser->hasInsertSelect("INSERT INTO t (v) VALUES ('SELECT')"));
751 }
752
753 public function testExtractInsertSelectSql(): void
754 {
755 $parser = new PgSqlParser();
756 $sql = $parser->extractInsertSelectSql('INSERT INTO t (id) SELECT id FROM old WHERE id > 5');
757 self::assertSame('SELECT id FROM old WHERE id > 5', $sql);
758 }
759
760 public function testExtractInsertSelectSqlReturnsNull(): void
761 {
762 $parser = new PgSqlParser();
763 self::assertNull($parser->extractInsertSelectSql('INSERT INTO t (id) VALUES (1)'));
764 }
765
766 public function testExtractUpdateTableQuoted(): void
767 {
768 $parser = new PgSqlParser();
769 self::assertSame('My Table', $parser->extractUpdateTable('UPDATE "My Table" SET x = 1'));
770 }
771
772 public function testExtractUpdateTableOnly(): void
773 {
774 $parser = new PgSqlParser();
775 self::assertSame('t', $parser->extractUpdateTable('UPDATE ONLY t SET x = 1'));
776 }
777
778 public function testExtractUpdateTableReturnsNull(): void
779 {
780 $parser = new PgSqlParser();
781 self::assertNull($parser->extractUpdateTable('SELECT 1'));
782 }
783
784 public function testExtractUpdateAlias(): void
785 {
786 $parser = new PgSqlParser();
787 self::assertSame('u', $parser->extractUpdateAlias('UPDATE users u SET name = \'x\' WHERE id = 1'));
788 }
789
790 public function testExtractUpdateAliasAs(): void
791 {
792 $parser = new PgSqlParser();
793 self::assertSame('u', $parser->extractUpdateAlias('UPDATE users AS u SET name = \'x\''));
794 }
795
796 public function testExtractUpdateAliasReturnsNull(): void
797 {
798 $parser = new PgSqlParser();
799 self::assertNull($parser->extractUpdateAlias('UPDATE users SET name = \'x\''));
800 }
801
802 public function testExtractUpdateSetsWithFrom(): void
803 {
804 $parser = new PgSqlParser();
805 $sets = $parser->extractUpdateSets('UPDATE users SET name = o.name FROM other o WHERE users.id = o.id');
806 self::assertSame(['name' => 'o.name'], $sets);
807 }
808
809 public function testExtractUpdateSetsReturning(): void
810 {
811 $parser = new PgSqlParser();
812 $sets = $parser->extractUpdateSets("UPDATE users SET name = 'x' RETURNING *");
813 self::assertSame(['name' => "'x'"], $sets);
814 }
815
816 public function testExtractUpdateSetsEmpty(): void
817 {
818 $parser = new PgSqlParser();
819 self::assertSame([], $parser->extractUpdateSets('SELECT 1'));
820 }
821
822 public function testExtractUpdateSetsQuotedColumn(): void
823 {
824 $parser = new PgSqlParser();
825 $sets = $parser->extractUpdateSets('UPDATE t SET "Column" = 1 WHERE id = 1');
826 self::assertArrayHasKey('Column', $sets);
827 self::assertSame('1', $sets['Column']);
828 }
829
830 public function testExtractWhereClauseReturnsNull(): void
831 {
832 $parser = new PgSqlParser();
833 self::assertNull($parser->extractWhereClause('DELETE FROM users'));
834 }
835
836 public function testExtractWhereClauseWithReturning(): void
837 {
838 $parser = new PgSqlParser();
839 $where = $parser->extractWhereClause('DELETE FROM users WHERE id = 1 RETURNING *');
840 self::assertSame('id = 1', $where);
841 }
842
843 public function testExtractWhereClauseWithStringLiteral(): void
844 {
845 $parser = new PgSqlParser();
846 $where = $parser->extractWhereClause("DELETE FROM users WHERE name = 'WHERE test'");
847 self::assertNotNull($where);
848 self::assertStringContainsString("'WHERE test'", $where);
849 }
850
851 public function testExtractWhereClauseWithOrderBy(): void
852 {
853 $parser = new PgSqlParser();
854 $where = $parser->extractWhereClause('DELETE FROM users WHERE active = true ORDER BY id');
855 self::assertNotNull($where);
856 self::assertStringNotContainsString('ORDER', $where);
857 }
858
859 public function testExtractWhereClauseWithLimit(): void
860 {
861 $parser = new PgSqlParser();
862 $where = $parser->extractWhereClause('DELETE FROM users WHERE active = true LIMIT 10');
863 self::assertNotNull($where);
864 self::assertStringNotContainsString('LIMIT', $where);
865 }
866
867 public function testExtractUpdateFromClause(): void
868 {
869 $parser = new PgSqlParser();
870 $from = $parser->extractUpdateFromClause('UPDATE users SET name = o.name FROM other o WHERE users.id = o.id');
871 self::assertSame('other o', $from);
872 }
873
874 public function testExtractUpdateFromClauseReturnsNull(): void
875 {
876 $parser = new PgSqlParser();
877 self::assertNull($parser->extractUpdateFromClause("UPDATE users SET name = 'x' WHERE id = 1"));
878 }
879
880 public function testExtractDeleteTableQuoted(): void
881 {
882 $parser = new PgSqlParser();
883 self::assertSame('users', $parser->extractDeleteTable('DELETE FROM "users" WHERE id = 1'));
884 }
885
886 public function testExtractDeleteTableOnly(): void
887 {
888 $parser = new PgSqlParser();
889 self::assertSame('t', $parser->extractDeleteTable('DELETE FROM ONLY t WHERE id = 1'));
890 }
891
892 public function testExtractDeleteTableReturnsNull(): void
893 {
894 $parser = new PgSqlParser();
895 self::assertNull($parser->extractDeleteTable('SELECT 1'));
896 }
897
898 public function testExtractDeleteAlias(): void
899 {
900 $parser = new PgSqlParser();
901 self::assertSame('u', $parser->extractDeleteAlias('DELETE FROM users u WHERE u.id = 1'));
902 }
903
904 public function testExtractDeleteAliasAs(): void
905 {
906 $parser = new PgSqlParser();
907 self::assertSame('u', $parser->extractDeleteAlias('DELETE FROM users AS u WHERE u.id = 1'));
908 }
909
910 public function testExtractDeleteAliasReturnsNull(): void
911 {
912 $parser = new PgSqlParser();
913 self::assertNull($parser->extractDeleteAlias('DELETE FROM users WHERE id = 1'));
914 }
915
916 public function testExtractDeleteUsingClause(): void
917 {
918 $parser = new PgSqlParser();
919 $using = $parser->extractDeleteUsingClause('DELETE FROM users USING orders WHERE users.id = orders.user_id');
920 self::assertSame('orders', $using);
921 }
922
923 public function testExtractDeleteUsingClauseReturnsNull(): void
924 {
925 $parser = new PgSqlParser();
926 self::assertNull($parser->extractDeleteUsingClause('DELETE FROM users WHERE id = 1'));
927 }
928
929 public function testExtractTruncateTableSchemaQualified(): void
930 {
931 $parser = new PgSqlParser();
932 self::assertSame('users', $parser->extractTruncateTable('TRUNCATE TABLE public.users'));
933 }
934
935 public function testExtractTruncateTableOnly(): void
936 {
937 $parser = new PgSqlParser();
938 self::assertSame('t', $parser->extractTruncateTable('TRUNCATE ONLY t'));
939 }
940
941 public function testExtractTruncateTableReturnsNull(): void
942 {
943 $parser = new PgSqlParser();
944 self::assertNull($parser->extractTruncateTable('SELECT 1'));
945 }
946
947 public function testExtractCreateTableNameSchemaQualified(): void
948 {
949 $parser = new PgSqlParser();
950 self::assertSame('users', $parser->extractCreateTableName('CREATE TABLE public.users (id INT)'));
951 }
952
953 public function testExtractCreateTableNameTemp(): void
954 {
955 $parser = new PgSqlParser();
956 self::assertSame('t', $parser->extractCreateTableName('CREATE TEMPORARY TABLE t (id INT)'));
957 self::assertSame('t', $parser->extractCreateTableName('CREATE TEMP TABLE t (id INT)'));
958 }
959
960 public function testExtractCreateTableNameReturnsNull(): void
961 {
962 $parser = new PgSqlParser();
963 self::assertNull($parser->extractCreateTableName('SELECT 1'));
964 }
965
966 public function testExtractCreateTableSelectSql(): void
967 {
968 $parser = new PgSqlParser();
969 $sql = $parser->extractCreateTableSelectSql('CREATE TABLE t AS SELECT id FROM old');
970 self::assertSame('SELECT id FROM old', $sql);
971 }
972
973 public function testExtractCreateTableSelectSqlReturnsNull(): void
974 {
975 $parser = new PgSqlParser();
976 self::assertNull($parser->extractCreateTableSelectSql('CREATE TABLE t (id INT)'));
977 }
978
979 public function testHasCreateTableLike(): void
980 {
981 $parser = new PgSqlParser();
982 self::assertTrue($parser->hasCreateTableLike('CREATE TABLE t (LIKE old_t)'));
983 self::assertFalse($parser->hasCreateTableLike('CREATE TABLE t (id INT)'));
984 }
985
986 public function testExtractCreateTableLikeSource(): void
987 {
988 $parser = new PgSqlParser();
989 self::assertSame('old_t', $parser->extractCreateTableLikeSource('CREATE TABLE t (LIKE old_t)'));
990 }
991
992 public function testExtractCreateTableLikeSourceSchemaQualified(): void
993 {
994 $parser = new PgSqlParser();
995 self::assertSame('old_t', $parser->extractCreateTableLikeSource('CREATE TABLE t (LIKE public.old_t)'));
996 }
997
998 public function testExtractCreateTableLikeSourceReturnsNull(): void
999 {
1000 $parser = new PgSqlParser();
1001 self::assertNull($parser->extractCreateTableLikeSource('CREATE TABLE t (id INT)'));
1002 }
1003
1004 public function testExtractDropTableNameIfExists(): void
1005 {
1006 $parser = new PgSqlParser();
1007 self::assertSame('users', $parser->extractDropTableName('DROP TABLE IF EXISTS users'));
1008 }
1009
1010 public function testExtractDropTableNameSchemaQualified(): void
1011 {
1012 $parser = new PgSqlParser();
1013 self::assertSame('users', $parser->extractDropTableName('DROP TABLE public.users'));
1014 }
1015
1016 public function testExtractDropTableNameReturnsNull(): void
1017 {
1018 $parser = new PgSqlParser();
1019 self::assertNull($parser->extractDropTableName('SELECT 1'));
1020 }
1021
1022 public function testExtractAlterTableNameIfExists(): void
1023 {
1024 $parser = new PgSqlParser();
1025 self::assertSame('users', $parser->extractAlterTableName('ALTER TABLE IF EXISTS users ADD COLUMN x INT'));
1026 }
1027
1028 public function testExtractAlterTableNameOnly(): void
1029 {
1030 $parser = new PgSqlParser();
1031 self::assertSame('users', $parser->extractAlterTableName('ALTER TABLE ONLY users ADD COLUMN x INT'));
1032 }
1033
1034 public function testExtractAlterTableNameReturnsNull(): void
1035 {
1036 $parser = new PgSqlParser();
1037 self::assertNull($parser->extractAlterTableName('SELECT 1'));
1038 }
1039
1040 public function testUnquoteIdentifierEscapedQuotes(): void
1041 {
1042 $parser = new PgSqlParser();
1043 self::assertSame('a"b', $parser->unquoteIdentifier('"a""b"'));
1044 }
1045
1046 public function testExtractSelectTableNamesSubquery(): void
1047 {
1048 $parser = new PgSqlParser();
1049 $tables = $parser->extractSelectTableNames('SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)');
1050 self::assertContains('users', $tables);
1051 }
1052
1053 public function testExtractSelectTableNamesMultipleFrom(): void
1054 {
1055 $parser = new PgSqlParser();
1056 $tables = $parser->extractSelectTableNames('SELECT * FROM users, orders WHERE users.id = orders.user_id');
1057 self::assertContains('users', $tables);
1058 self::assertContains('orders', $tables);
1059 }
1060
1061 public function testExtractSelectTableNamesNoFrom(): void
1062 {
1063 $parser = new PgSqlParser();
1064 self::assertSame([], $parser->extractSelectTableNames('SELECT 1'));
1065 }
1066
1067 public function testExtractSelectTableNamesSchemaQualified(): void
1068 {
1069 $parser = new PgSqlParser();
1070 $tables = $parser->extractSelectTableNames('SELECT * FROM public.users');
1071 self::assertContains('users', $tables);
1072 }
1073
1074 public function testExtractSelectTableNamesDeduplication(): void
1075 {
1076 $parser = new PgSqlParser();
1077 $tables = $parser->extractSelectTableNames('SELECT * FROM users JOIN users ON 1=1');
1078 self::assertSame(['users'], $tables);
1079 }
1080
1081 public function testClassifyWithNestedParens(): void
1082 {
1083 $parser = new PgSqlParser();
1084 self::assertSame('SELECT', $parser->classifyStatement('WITH cte AS (SELECT (1+2)) SELECT * FROM cte'));
1085 }
1086
1087 public function testClassifyLineCommentNoNewline(): void
1088 {
1089 $parser = new PgSqlParser();
1090 self::assertNull($parser->classifyStatement('-- only a comment'));
1091 }
1092
1093 public function testClassifyBlockCommentOnlyNoClose(): void
1094 {
1095 $parser = new PgSqlParser();
1096 self::assertNull($parser->classifyStatement('/* unclosed comment'));
1097 }
1098
1099 public function testStripSchemaPrefixQuotedBoth(): void
1100 {
1101 $parser = new PgSqlParser();
1102 self::assertSame('"My Table"', $parser->stripSchemaPrefix('"myschema"."My Table"'));
1103 }
1104
1105 public function testExtractInsertTableQuotedSchemaQualified(): void
1106 {
1107 $parser = new PgSqlParser();
1108 self::assertSame('users', $parser->extractInsertTable('INSERT INTO public."users" (id) VALUES (1)'));
1109 }
1110
1111 public function testExtractUpdateSetsWithSubquery(): void
1112 {
1113 $parser = new PgSqlParser();
1114 $sets = $parser->extractUpdateSets('UPDATE t SET v = (SELECT max(id)) WHERE id = 1');
1115 self::assertArrayHasKey('v', $sets);
1116 self::assertSame('(SELECT max(id))', $sets['v']);
1117 }
1118
1119 public function testExtractDeleteAliasUsing(): void
1120 {
1121 $parser = new PgSqlParser();
1122 self::assertSame('u', $parser->extractDeleteAlias('DELETE FROM users AS u USING orders WHERE u.id = orders.user_id'));
1123 }
1124
1125 public function testExtractDeleteAliasReturning(): void
1126 {
1127 $parser = new PgSqlParser();
1128 self::assertSame('u', $parser->extractDeleteAlias('DELETE FROM users u RETURNING *'));
1129 }
1130
1131 public function testExtractDeleteUsingWithReturning(): void
1132 {
1133 $parser = new PgSqlParser();
1134 $using = $parser->extractDeleteUsingClause('DELETE FROM users USING orders WHERE 1=1 RETURNING *');
1135 self::assertSame('orders', $using);
1136 }
1137
1138 public function testExtractTruncateTableNoTableKeyword(): void
1139 {
1140 $parser = new PgSqlParser();
1141 self::assertSame('users', $parser->extractTruncateTable('TRUNCATE users'));
1142 }
1143
1144 public function testExtractUpdateFromClauseWithReturning(): void
1145 {
1146 $parser = new PgSqlParser();
1147 $from = $parser->extractUpdateFromClause('UPDATE users SET name = o.name FROM other o WHERE 1=1 RETURNING *');
1148 self::assertSame('other o', $from);
1149 }
1150
1151 public function testExtractInsertValuesWithWhitespace(): void
1152 {
1153 $parser = new PgSqlParser();
1154 $rows = $parser->extractInsertValues('INSERT INTO t (id) VALUES (1) , (2)');
1155 self::assertSame(2, count($rows));
1156 self::assertSame(['1'], $rows[0]);
1157 self::assertSame(['2'], $rows[1]);
1158 }
1159
1160 public function testSplitDollarQuoteEmpty(): void
1161 {
1162 $parser = new PgSqlParser();
1163 $result = $parser->splitStatements('SELECT $$;$$; SELECT 2');
1164 self::assertSame(2, count($result));
1165 }
1166
1167 public function testExtractSelectTableNamesWithAlias(): void
1168 {
1169 $parser = new PgSqlParser();
1170 $tables = $parser->extractSelectTableNames('SELECT * FROM users u WHERE u.id = 1');
1171 self::assertContains('users', $tables);
1172 }
1173
1174 public function testExtractSelectTableNamesLeftJoin(): void
1175 {
1176 $parser = new PgSqlParser();
1177 $tables = $parser->extractSelectTableNames('SELECT * FROM users LEFT JOIN orders ON users.id = orders.uid');
1178 self::assertContains('users', $tables);
1179 self::assertContains('orders', $tables);
1180 }
1181
1182 public function testClassifyWithQuotedIdentifier(): void
1183 {
1184 $parser = new PgSqlParser();
1185 self::assertSame('SELECT', $parser->classifyStatement('WITH "my cte" AS (SELECT 1) SELECT * FROM "my cte"'));
1186 }
1187
1188 public function testExtractOnConflictDoNothing(): void
1189 {
1190 $parser = new PgSqlParser();
1191 $result = $parser->extractOnConflictUpdateColumns(
1192 'INSERT INTO t (id) VALUES (1) ON CONFLICT (id) DO NOTHING'
1193 );
1194 self::assertSame([], $result['columns']);
1195 }
1196
1197 public function testExtractInsertColumnsWithDefault(): void
1198 {
1199 $parser = new PgSqlParser();
1200 $cols = $parser->extractInsertColumns('INSERT INTO t (id) DEFAULT VALUES');
1201 self::assertSame(['id'], $cols);
1202 }
1203
1204 public function testClassifySelectLowercase(): void
1205 {
1206 $parser = new PgSqlParser();
1207 self::assertSame('SELECT', $parser->classifyStatement('select * from users'));
1208 }
1209
1210 public function testClassifyInsertLowercase(): void
1211 {
1212 $parser = new PgSqlParser();
1213 self::assertSame('INSERT', $parser->classifyStatement('insert into users (id) values (1)'));
1214 }
1215
1216 public function testClassifyUpdateLowercase(): void
1217 {
1218 $parser = new PgSqlParser();
1219 self::assertSame('UPDATE', $parser->classifyStatement("update users set name = 'x'"));
1220 }
1221
1222 public function testClassifyDeleteLowercase(): void
1223 {
1224 $parser = new PgSqlParser();
1225 self::assertSame('DELETE', $parser->classifyStatement('delete from users where id = 1'));
1226 }
1227
1228 public function testClassifyTruncateLowercase(): void
1229 {
1230 $parser = new PgSqlParser();
1231 self::assertSame('TRUNCATE', $parser->classifyStatement('truncate table users'));
1232 }
1233
1234 public function testClassifyCreateTableLowercase(): void
1235 {
1236 $parser = new PgSqlParser();
1237 self::assertSame('CREATE_TABLE', $parser->classifyStatement('create table users (id int)'));
1238 }
1239
1240 public function testClassifyDropTableLowercase(): void
1241 {
1242 $parser = new PgSqlParser();
1243 self::assertSame('DROP_TABLE', $parser->classifyStatement('drop table users'));
1244 }
1245
1246 public function testClassifyAlterTableLowercase(): void
1247 {
1248 $parser = new PgSqlParser();
1249 self::assertSame('ALTER_TABLE', $parser->classifyStatement('alter table users add column x int'));
1250 }
1251
1252 public function testClassifyTclLowercase(): void
1253 {
1254 $parser = new PgSqlParser();
1255 self::assertSame('TCL', $parser->classifyStatement('begin'));
1256 self::assertSame('TCL', $parser->classifyStatement('commit'));
1257 self::assertSame('TCL', $parser->classifyStatement('rollback'));
1258 self::assertSame('TCL', $parser->classifyStatement('start transaction'));
1259 self::assertSame('TCL', $parser->classifyStatement('savepoint sp1'));
1260 self::assertSame('TCL', $parser->classifyStatement('release savepoint sp1'));
1261 self::assertSame('TCL', $parser->classifyStatement('set transaction read only'));
1262 }
1263
1264 public function testExtractInsertTableLowercase(): void
1265 {
1266 $parser = new PgSqlParser();
1267 self::assertSame('users', $parser->extractInsertTable('insert into users (id) values (1)'));
1268 }
1269
1270 public function testExtractInsertColumnsLowercase(): void
1271 {
1272 $parser = new PgSqlParser();
1273 $cols = $parser->extractInsertColumns("insert into users (id, name) values (1, 'x')");
1274 self::assertSame(['id', 'name'], $cols);
1275 }
1276
1277 public function testExtractInsertValuesLowercase(): void
1278 {
1279 $parser = new PgSqlParser();
1280 $rows = $parser->extractInsertValues('insert into t (id) values (1)');
1281 self::assertSame(1, count($rows));
1282 self::assertSame(['1'], $rows[0]);
1283 }
1284
1285 public function testHasOnConflictLowercase(): void
1286 {
1287 $parser = new PgSqlParser();
1288 self::assertTrue($parser->hasOnConflict('insert into t (id) values (1) on conflict (id) do nothing'));
1289 }
1290
1291 public function testExtractOnConflictLowercase(): void
1292 {
1293 $parser = new PgSqlParser();
1294 $result = $parser->extractOnConflictUpdateColumns(
1295 'insert into t (id, v) values (1, 2) on conflict (id) do update set v = excluded.v'
1296 );
1297 self::assertSame(['v'], $result['columns']);
1298 }
1299
1300 public function testHasInsertSelectLowercase(): void
1301 {
1302 $parser = new PgSqlParser();
1303 self::assertTrue($parser->hasInsertSelect('insert into t select * from old'));
1304 }
1305
1306 public function testExtractInsertSelectSqlLowercase(): void
1307 {
1308 $parser = new PgSqlParser();
1309 $sql = $parser->extractInsertSelectSql('insert into t (id) select id from old');
1310 self::assertNotNull($sql);
1311 self::assertStringStartsWith('select', $sql);
1312 }
1313
1314 public function testExtractUpdateTableLowercase(): void
1315 {
1316 $parser = new PgSqlParser();
1317 self::assertSame('users', $parser->extractUpdateTable("update users set name = 'x'"));
1318 }
1319
1320 public function testExtractUpdateAliasLowercase(): void
1321 {
1322 $parser = new PgSqlParser();
1323 self::assertSame('u', $parser->extractUpdateAlias("update users as u set name = 'x'"));
1324 }
1325
1326 public function testExtractUpdateSetsLowercase(): void
1327 {
1328 $parser = new PgSqlParser();
1329 $sets = $parser->extractUpdateSets('update t set v = 1 where id = 2');
1330 self::assertSame(['v' => '1'], $sets);
1331 }
1332
1333 public function testExtractWhereClauseLowercase(): void
1334 {
1335 $parser = new PgSqlParser();
1336 $where = $parser->extractWhereClause('delete from users where id = 1 returning *');
1337 self::assertSame('id = 1', $where);
1338 }
1339
1340 public function testExtractUpdateFromClauseLowercase(): void
1341 {
1342 $parser = new PgSqlParser();
1343 $from = $parser->extractUpdateFromClause('update users set name = o.name from other o where users.id = o.id');
1344 self::assertSame('other o', $from);
1345 }
1346
1347 public function testExtractDeleteTableLowercase(): void
1348 {
1349 $parser = new PgSqlParser();
1350 self::assertSame('users', $parser->extractDeleteTable('delete from users where id = 1'));
1351 }
1352
1353 public function testExtractDeleteAliasLowercase(): void
1354 {
1355 $parser = new PgSqlParser();
1356 self::assertSame('u', $parser->extractDeleteAlias('delete from users as u where u.id = 1'));
1357 }
1358
1359 public function testExtractDeleteUsingClauseLowercase(): void
1360 {
1361 $parser = new PgSqlParser();
1362 $using = $parser->extractDeleteUsingClause('delete from t using other where t.id = other.id');
1363 self::assertSame('other', $using);
1364 }
1365
1366 public function testExtractTruncateTableLowercase(): void
1367 {
1368 $parser = new PgSqlParser();
1369 self::assertSame('users', $parser->extractTruncateTable('truncate table users'));
1370 }
1371
1372 public function testExtractCreateTableNameLowercase(): void
1373 {
1374 $parser = new PgSqlParser();
1375 self::assertSame('users', $parser->extractCreateTableName('create table users (id int)'));
1376 }
1377
1378 public function testHasIfNotExistsLowercase(): void
1379 {
1380 $parser = new PgSqlParser();
1381 self::assertTrue($parser->hasIfNotExists('create table if not exists t (id int)'));
1382 }
1383
1384 public function testHasCreateTableAsSelectLowercase(): void
1385 {
1386 $parser = new PgSqlParser();
1387 self::assertTrue($parser->hasCreateTableAsSelect('create table t as select * from old'));
1388 }
1389
1390 public function testExtractCreateTableSelectSqlLowercase(): void
1391 {
1392 $parser = new PgSqlParser();
1393 $sql = $parser->extractCreateTableSelectSql('create table t as select id from old');
1394 self::assertNotNull($sql);
1395 self::assertStringStartsWith('select', $sql);
1396 }
1397
1398 public function testHasCreateTableLikeLowercase(): void
1399 {
1400 $parser = new PgSqlParser();
1401 self::assertTrue($parser->hasCreateTableLike('create table t (like old_t)'));
1402 }
1403
1404 public function testExtractCreateTableLikeSourceLowercase(): void
1405 {
1406 $parser = new PgSqlParser();
1407 self::assertSame('old_t', $parser->extractCreateTableLikeSource('create table t (like old_t)'));
1408 }
1409
1410 public function testExtractDropTableNameLowercase(): void
1411 {
1412 $parser = new PgSqlParser();
1413 self::assertSame('users', $parser->extractDropTableName('drop table users'));
1414 }
1415
1416 public function testHasDropTableIfExistsLowercase(): void
1417 {
1418 $parser = new PgSqlParser();
1419 self::assertTrue($parser->hasDropTableIfExists('drop table if exists users'));
1420 }
1421
1422 public function testExtractAlterTableNameLowercase(): void
1423 {
1424 $parser = new PgSqlParser();
1425 self::assertSame('users', $parser->extractAlterTableName('alter table users add column x int'));
1426 }
1427
1428 public function testExtractSelectTableNamesLowercase(): void
1429 {
1430 $parser = new PgSqlParser();
1431 $tables = $parser->extractSelectTableNames('select * from users join orders on users.id = orders.uid');
1432 self::assertContains('users', $tables);
1433 self::assertContains('orders', $tables);
1434 }
1435
1436 public function testExtractInsertTableOnlyLowercase(): void
1437 {
1438 $parser = new PgSqlParser();
1439 self::assertSame('t', $parser->extractInsertTable('insert into only t (id) values (1)'));
1440 }
1441
1442 public function testExtractUpdateTableOnlyLowercase(): void
1443 {
1444 $parser = new PgSqlParser();
1445 self::assertSame('t', $parser->extractUpdateTable('update only t set x = 1'));
1446 }
1447
1448 public function testExtractDeleteTableOnlyLowercase(): void
1449 {
1450 $parser = new PgSqlParser();
1451 self::assertSame('t', $parser->extractDeleteTable('delete from only t where id = 1'));
1452 }
1453
1454 public function testExtractTruncateTableOnlyLowercase(): void
1455 {
1456 $parser = new PgSqlParser();
1457 self::assertSame('t', $parser->extractTruncateTable('truncate only t'));
1458 }
1459
1460 public function testSplitStatementsExactOutput(): void
1461 {
1462 $parser = new PgSqlParser();
1463 $result = $parser->splitStatements('SELECT 1; SELECT 2; SELECT 3');
1464 self::assertSame(['SELECT 1', 'SELECT 2', 'SELECT 3'], $result);
1465 }
1466
1467 public function testSplitStatementsPreservesContent(): void
1468 {
1469 $parser = new PgSqlParser();
1470 $result = $parser->splitStatements("SELECT 'a;b'; SELECT 2");
1471 self::assertSame(["SELECT 'a;b'", 'SELECT 2'], $result);
1472 }
1473
1474 public function testExtractInsertValuesExactMultiple(): void
1475 {
1476 $parser = new PgSqlParser();
1477 $rows = $parser->extractInsertValues("INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y')");
1478 self::assertSame([['1', "'x'"], ['2', "'y'"]], $rows);
1479 }
1480
1481 public function testExtractOnConflictWithTrailingSemicolon(): void
1482 {
1483 $parser = new PgSqlParser();
1484 $result = $parser->extractOnConflictUpdateColumns(
1485 'INSERT INTO t (id, v) VALUES (1, 2) ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v;'
1486 );
1487 self::assertSame(['v'], $result['columns']);
1488 self::assertSame('EXCLUDED.v', $result['values']['v']);
1489 }
1490
1491 public function testExtractUpdateSetsMultipleColumns(): void
1492 {
1493 $parser = new PgSqlParser();
1494 $sets = $parser->extractUpdateSets("UPDATE t SET a = 1, b = 'x', c = NULL WHERE id = 1");
1495 self::assertSame('1', $sets['a']);
1496 self::assertSame("'x'", $sets['b']);
1497 self::assertSame('NULL', $sets['c']);
1498 }
1499
1500 public function testExtractWhereClauseExactFromUpdate(): void
1501 {
1502 $parser = new PgSqlParser();
1503 $where = $parser->extractWhereClause("UPDATE t SET x = 1 WHERE id = 42 AND name = 'Bob'");
1504 self::assertSame("id = 42 AND name = 'Bob'", $where);
1505 }
1506
1507 public function testExtractSelectTableNamesWithGroupBy(): void
1508 {
1509 $parser = new PgSqlParser();
1510 $tables = $parser->extractSelectTableNames('SELECT count(*) FROM users GROUP BY name');
1511 self::assertContains('users', $tables);
1512 }
1513
1514 public function testExtractSelectTableNamesWithLimit(): void
1515 {
1516 $parser = new PgSqlParser();
1517 $tables = $parser->extractSelectTableNames('SELECT * FROM users LIMIT 10');
1518 self::assertContains('users', $tables);
1519 }
1520
1521 public function testExtractSelectTableNamesWithOffset(): void
1522 {
1523 $parser = new PgSqlParser();
1524 $tables = $parser->extractSelectTableNames('SELECT * FROM users OFFSET 5');
1525 self::assertContains('users', $tables);
1526 }
1527
1528 public function testExtractSelectTableNamesWithHaving(): void
1529 {
1530 $parser = new PgSqlParser();
1531 $tables = $parser->extractSelectTableNames('SELECT count(*) FROM users GROUP BY name HAVING count(*) > 1');
1532 self::assertContains('users', $tables);
1533 }
1534
1535 public function testExtractSelectTableNamesUnion(): void
1536 {
1537 $parser = new PgSqlParser();
1538 $tables = $parser->extractSelectTableNames('SELECT * FROM users UNION SELECT * FROM admins');
1539 self::assertContains('users', $tables);
1540 self::assertContains('admins', $tables);
1541 }
1542
1543 public function testExtractSelectTableNamesForUpdate(): void
1544 {
1545 $parser = new PgSqlParser();
1546 $tables = $parser->extractSelectTableNames('SELECT * FROM users FOR UPDATE');
1547 self::assertContains('users', $tables);
1548 }
1549
1550 public function testStripLeadingBlockCommentOnly(): void
1551 {
1552 $parser = new PgSqlParser();
1553 self::assertSame('SELECT', $parser->classifyStatement('/* block */ SELECT 1'));
1554 }
1555
1556 public function testStripLeadingMixedComments(): void
1557 {
1558 $parser = new PgSqlParser();
1559 self::assertSame('SELECT', $parser->classifyStatement("-- line\n/* block */\nSELECT 1"));
1560 }
1561
1562 public function testClassifyWithMixedCase(): void
1563 {
1564 $parser = new PgSqlParser();
1565 self::assertSame('SELECT', $parser->classifyStatement('Select * From users'));
1566 self::assertSame('INSERT', $parser->classifyStatement('Insert Into t (id) Values (1)'));
1567 self::assertSame('UPDATE', $parser->classifyStatement('Update t Set x = 1'));
1568 self::assertSame('DELETE', $parser->classifyStatement('Delete From t Where id = 1'));
1569 }
1570
1571 public function testClassifyWithSemicolon(): void
1572 {
1573 $parser = new PgSqlParser();
1574 self::assertSame('SELECT', $parser->classifyStatement('SELECT 1;'));
1575 }
1576
1577 public function testExtractInsertColumnsWithSelectKeyword(): void
1578 {
1579 $parser = new PgSqlParser();
1580 $cols = $parser->extractInsertColumns('INSERT INTO t (id, name) SELECT id, name FROM old');
1581 self::assertSame(['id', 'name'], $cols);
1582 }
1583
1584 public function testExtractSelectTableNamesWithSubqueryInWhere(): void
1585 {
1586 $parser = new PgSqlParser();
1587 $tables = $parser->extractSelectTableNames('SELECT * FROM users WHERE id IN (SELECT uid FROM orders)');
1588 self::assertContains('users', $tables);
1589 }
1590
1591 public function testSplitLineCommentAtEnd(): void
1592 {
1593 $parser = new PgSqlParser();
1594 $result = $parser->splitStatements('SELECT 1 -- comment');
1595 self::assertSame(['SELECT 1 -- comment'], $result);
1596 }
1597
1598 public function testExtractOnConflictSetWithQuotedColumnsLowercase(): void
1599 {
1600 $parser = new PgSqlParser();
1601 $result = $parser->extractOnConflictUpdateColumns(
1602 'insert into t (id, "Col") values (1, 2) on conflict (id) do update set "Col" = excluded."Col"'
1603 );
1604 self::assertSame(['Col'], $result['columns']);
1605 }
1606
1607 public function testExtractWhereClauseFromUpdateWithFrom(): void
1608 {
1609 $parser = new PgSqlParser();
1610 $where = $parser->extractWhereClause('UPDATE t SET v = o.v FROM other o WHERE t.id = o.id');
1611 self::assertNotNull($where);
1612 self::assertStringContainsString('t.id = o.id', $where);
1613 }
1614
1615 public function testExtractDeleteAliasEndOfStatement(): void
1616 {
1617 $parser = new PgSqlParser();
1618 self::assertNull($parser->extractDeleteAlias('DELETE FROM users'));
1619 }
1620
1621 public function testHasInsertSelectWithValuesFalse(): void
1622 {
1623 $parser = new PgSqlParser();
1624 self::assertFalse($parser->hasInsertSelect('INSERT INTO t (id) VALUES (1)'));
1625 }
1626
1627 public function testExtractUpdateSetsLowercaseReturning(): void
1628 {
1629 $parser = new PgSqlParser();
1630 $sets = $parser->extractUpdateSets('update t set v = 1 returning *');
1631 self::assertSame(['v' => '1'], $sets);
1632 }
1633
1634 public function testExtractInsertTableWithAsAlias(): void
1635 {
1636 $parser = new PgSqlParser();
1637 self::assertSame('t', $parser->extractInsertTable('insert into t as alias (id) values (1)'));
1638 }
1639
1640 public function testExtractCreateTableNameUnlogged(): void
1641 {
1642 $parser = new PgSqlParser();
1643 self::assertSame('t', $parser->extractCreateTableName('CREATE UNLOGGED TABLE t (id INT)'));
1644 }
1645
1646 public function testExtractAlterTableNameSchemaQualified(): void
1647 {
1648 $parser = new PgSqlParser();
1649 self::assertSame('users', $parser->extractAlterTableName('ALTER TABLE public.users ADD COLUMN x INT'));
1650 }
1651
1652 public function testExtractDropTableNameQuoted(): void
1653 {
1654 $parser = new PgSqlParser();
1655 self::assertSame('My Table', $parser->extractDropTableName('DROP TABLE "My Table"'));
1656 }
1657
1658 public function testSplitDollarQuoteAlphanumTag(): void
1659 {
1660 $parser = new PgSqlParser();
1661 $result = $parser->splitStatements('SELECT $fn$body;here$fn$; SELECT 2');
1662 self::assertSame(2, count($result));
1663 self::assertStringContainsString('body;here', $result[0]);
1664 }
1665
1666 public function testExtractSelectTableNamesIntersect(): void
1667 {
1668 $parser = new PgSqlParser();
1669 $tables = $parser->extractSelectTableNames('SELECT id FROM users INTERSECT SELECT id FROM admins');
1670 self::assertContains('users', $tables);
1671 }
1672
1673 public function testExtractSelectTableNamesExcept(): void
1674 {
1675 $parser = new PgSqlParser();
1676 $tables = $parser->extractSelectTableNames('SELECT id FROM users EXCEPT SELECT id FROM banned');
1677 self::assertContains('users', $tables);
1678 }
1679
1680 public function testExtractUpdateSetsWithSingleQuoteInValue(): void
1681 {
1682 $parser = new PgSqlParser();
1683 $sets = $parser->extractUpdateSets("UPDATE t SET name = 'it''s' WHERE id = 1");
1684 self::assertSame("'it''s'", $sets['name']);
1685 }
1686
1687 public function testExtractSelectTableNamesWithAs(): void
1688 {
1689 $parser = new PgSqlParser();
1690 $tables = $parser->extractSelectTableNames('SELECT * FROM users AS u');
1691 self::assertContains('users', $tables);
1692 }
1693
1694 public function testExtractInsertValuesMultiLine(): void
1695 {
1696 $parser = new PgSqlParser();
1697 $values = $parser->extractInsertValues("INSERT INTO t (a)\nVALUES\n(1)");
1698 self::assertSame([['1']], $values);
1699 }
1700
1701 public function testExtractOnConflictUpdateColumnsLowercase(): void
1702 {
1703 $parser = new PgSqlParser();
1704 $info = $parser->extractOnConflictUpdateColumns(
1705 "insert into t (id, name) values (1, 'a') on conflict (id) do update set name = excluded.name"
1706 );
1707 self::assertSame(['name'], $info['columns']);
1708 self::assertSame(['name' => 'excluded.name'], $info['values']);
1709 }
1710
1711 public function testExtractOnConflictUpdateColumnsMultiLine(): void
1712 {
1713 $parser = new PgSqlParser();
1714 $info = $parser->extractOnConflictUpdateColumns(
1715 "INSERT INTO t (id, name) VALUES (1, 'a')\nON CONFLICT (id)\nDO UPDATE SET\nname = EXCLUDED.name"
1716 );
1717 self::assertSame(['name'], $info['columns']);
1718 }
1719
1720 public function testHasInsertSelectMultiLine(): void
1721 {
1722 $parser = new PgSqlParser();
1723 self::assertTrue($parser->hasInsertSelect("INSERT INTO t\nSELECT * FROM s"));
1724 }
1725
1726 public function testExtractUpdateSetsMultiLine(): void
1727 {
1728 $parser = new PgSqlParser();
1729 $sets = $parser->extractUpdateSets("UPDATE t\nSET\nname = 'Bob'\nWHERE id = 1");
1730 self::assertSame("'Bob'", $sets['name']);
1731 }
1732
1733 public function testExtractWhereClauseMultiLine(): void
1734 {
1735 $parser = new PgSqlParser();
1736 self::assertSame('id = 1', $parser->extractWhereClause("UPDATE t SET name = 'Bob'\nWHERE id = 1"));
1737 }
1738
1739 public function testExtractUpdateFromClauseMultiLine(): void
1740 {
1741 $parser = new PgSqlParser();
1742 self::assertSame('orders', $parser->extractUpdateFromClause("UPDATE users SET name = orders.name\nFROM orders\nWHERE users.id = orders.user_id"));
1743 }
1744
1745 public function testExtractDeleteUsingClauseMultiLine(): void
1746 {
1747 $parser = new PgSqlParser();
1748 self::assertSame('orders', $parser->extractDeleteUsingClause("DELETE FROM users\nUSING orders\nWHERE users.id = orders.user_id"));
1749 }
1750
1751 public function testHasCreateTableAsSelectMultiLine(): void
1752 {
1753 $parser = new PgSqlParser();
1754 self::assertTrue($parser->hasCreateTableAsSelect("CREATE TABLE t\nAS SELECT 1"));
1755 }
1756
1757 public function testExtractCreateTableSelectSqlMultiLine(): void
1758 {
1759 $parser = new PgSqlParser();
1760 self::assertSame('SELECT 1', $parser->extractCreateTableSelectSql("CREATE TABLE t\nAS SELECT 1"));
1761 }
1762
1763 public function testHasCreateTableLikeMultiLine(): void
1764 {
1765 $parser = new PgSqlParser();
1766 self::assertTrue($parser->hasCreateTableLike("CREATE TABLE t (\nLIKE s)"));
1767 }
1768
1769 public function testExtractSelectTableNamesWithJoinLowercase(): void
1770 {
1771 $parser = new PgSqlParser();
1772 $tables = $parser->extractSelectTableNames('select * from users inner join orders on users.id = orders.user_id');
1773 self::assertContains('users', $tables);
1774 }
1775
1776 public function testExtractSelectTableNamesMultiLine(): void
1777 {
1778 $parser = new PgSqlParser();
1779 $tables = $parser->extractSelectTableNames("SELECT *\nFROM users\nINNER JOIN orders\nON users.id = orders.user_id");
1780 self::assertContains('users', $tables);
1781 }
1782
1783 public function testExtractUpdateSetsWithNewlineInValue(): void
1784 {
1785 $parser = new PgSqlParser();
1786 $sets = $parser->extractUpdateSets("UPDATE t SET bio = 'line1\nline2' WHERE id = 1");
1787 self::assertArrayHasKey('bio', $sets);
1788 }
1789
1790 public function testExtractOnConflictAssignmentMultiLine(): void
1791 {
1792 $parser = new PgSqlParser();
1793 $info = $parser->extractOnConflictUpdateColumns(
1794 "INSERT INTO t (id, a, b) VALUES (1, 2, 3)\nON CONFLICT (id) DO UPDATE SET\na = EXCLUDED.a,\nb = EXCLUDED.b"
1795 );
1796 self::assertSame(['a', 'b'], $info['columns']);
1797 }
1798
1799 public function testExtractInsertValuesWithLeadingWhitespace(): void
1800 {
1801 $parser = new PgSqlParser();
1802 $values = $parser->extractInsertValues('INSERT INTO t (a) VALUES ( 1 )');
1803 self::assertSame([['1']], $values);
1804 }
1805
1806 public function testExtractInsertValuesMultipleRows(): void
1807 {
1808 $parser = new PgSqlParser();
1809 $values = $parser->extractInsertValues('INSERT INTO t (a) VALUES (1), (2)');
1810 self::assertSame([['1'], ['2']], $values);
1811 }
1812
1813 public function testExtractUpdateSetsOnlySetColumns(): void
1814 {
1815 $parser = new PgSqlParser();
1816 $sets = $parser->extractUpdateSets('UPDATE t SET a = 1, b = 2 FROM s WHERE t.id = s.id');
1817 self::assertSame(['a' => '1', 'b' => '2'], $sets);
1818 }
1819
1820 public function testExtractWhereClauseTrimmed(): void
1821 {
1822 $parser = new PgSqlParser();
1823 self::assertSame('id = 1', $parser->extractWhereClause('DELETE FROM t WHERE id = 1 '));
1824 }
1825
1826 public function testExtractUpdateFromClauseTrimmed(): void
1827 {
1828 $parser = new PgSqlParser();
1829 self::assertSame('orders', $parser->extractUpdateFromClause('UPDATE users SET name = orders.name FROM orders WHERE users.id = orders.user_id'));
1830 }
1831
1832 public function testExtractDeleteUsingClauseTrimmed(): void
1833 {
1834 $parser = new PgSqlParser();
1835 self::assertSame('orders', $parser->extractDeleteUsingClause('DELETE FROM users USING orders WHERE users.id = orders.user_id'));
1836 }
1837
1838 public function testHasCreateTableAsSelectTemporary(): void
1839 {
1840 $parser = new PgSqlParser();
1841 self::assertTrue($parser->hasCreateTableAsSelect('CREATE TEMPORARY TABLE t AS SELECT 1'));
1842 }
1843
1844 public function testHasCreateTableAsSelectTemp(): void
1845 {
1846 $parser = new PgSqlParser();
1847 self::assertTrue($parser->hasCreateTableAsSelect('CREATE TEMP TABLE t AS SELECT 1'));
1848 }
1849
1850 public function testHasCreateTableAsSelectUnlogged(): void
1851 {
1852 $parser = new PgSqlParser();
1853 self::assertTrue($parser->hasCreateTableAsSelect('CREATE UNLOGGED TABLE t AS SELECT 1'));
1854 }
1855
1856 public function testHasCreateTableLikeTemporary(): void
1857 {
1858 $parser = new PgSqlParser();
1859 self::assertTrue($parser->hasCreateTableLike('CREATE TEMPORARY TABLE t (LIKE s)'));
1860 }
1861
1862 public function testExtractOnConflictUpdateColumnsWithWhere(): void
1863 {
1864 $parser = new PgSqlParser();
1865 $info = $parser->extractOnConflictUpdateColumns(
1866 "INSERT INTO t (id, name) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name WHERE t.id > 0"
1867 );
1868 self::assertSame(['name'], $info['columns']);
1869 }
1870
1871 public function testExtractInsertValuesNewlineInsideParens(): void
1872 {
1873 $parser = new PgSqlParser();
1874 $values = $parser->extractInsertValues("INSERT INTO t (a, b) VALUES (\n1,\n2\n)");
1875 self::assertSame([['1', '2']], $values);
1876 }
1877
1878 public function testExtractOnConflictNewlineInsideSetClause(): void
1879 {
1880 $parser = new PgSqlParser();
1881 $info = $parser->extractOnConflictUpdateColumns(
1882 "INSERT INTO t (id, name) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET name =\nEXCLUDED.name"
1883 );
1884 self::assertSame(['name'], $info['columns']);
1885 self::assertSame('EXCLUDED.name', $info['values']['name']);
1886 }
1887
1888 public function testExtractUpdateSetsNewlineInAssignment(): void
1889 {
1890 $parser = new PgSqlParser();
1891 $sets = $parser->extractUpdateSets("UPDATE t SET name =\n'Bob' WHERE id = 1");
1892 self::assertSame("'Bob'", $sets['name']);
1893 }
1894
1895 public function testExtractUpdateSetsMultiLineFromClause(): void
1896 {
1897 $parser = new PgSqlParser();
1898 $sets = $parser->extractUpdateSets("UPDATE t SET a = 1\nFROM other\nWHERE t.id = other.id");
1899 self::assertSame(['a' => '1'], $sets);
1900 }
1901
1902 public function testExtractWhereClauseNewlineBeforeReturning(): void
1903 {
1904 $parser = new PgSqlParser();
1905 self::assertSame('id = 1', $parser->extractWhereClause("UPDATE t SET name = 'Bob' WHERE id = 1\nRETURNING *"));
1906 }
1907
1908 public function testExtractWhereClauseNewlineBeforeOrderBy(): void
1909 {
1910 $parser = new PgSqlParser();
1911 self::assertSame('id > 0', $parser->extractWhereClause("DELETE FROM t WHERE id > 0\nORDER BY id"));
1912 }
1913
1914 public function testExtractWhereClauseNewlineBeforeLimit(): void
1915 {
1916 $parser = new PgSqlParser();
1917 self::assertSame('id > 0', $parser->extractWhereClause("DELETE FROM t WHERE id > 0\nLIMIT 10"));
1918 }
1919
1920 public function testExtractUpdateFromClauseNewlineAfterFrom(): void
1921 {
1922 $parser = new PgSqlParser();
1923 self::assertSame('orders', $parser->extractUpdateFromClause("UPDATE users SET name = orders.name\nFROM\norders\nWHERE users.id = orders.user_id"));
1924 }
1925
1926 public function testExtractDeleteUsingClauseNewlineAfterUsing(): void
1927 {
1928 $parser = new PgSqlParser();
1929 self::assertSame('orders', $parser->extractDeleteUsingClause("DELETE FROM users\nUSING\norders\nWHERE users.id = orders.user_id"));
1930 }
1931
1932 public function testExtractCreateTableSelectSqlNewlineBeforeSelect(): void
1933 {
1934 $parser = new PgSqlParser();
1935 self::assertSame("SELECT\n1", $parser->extractCreateTableSelectSql("CREATE TABLE t AS\nSELECT\n1"));
1936 }
1937
1938 public function testHasCreateTableLikeNewlineBeforeLike(): void
1939 {
1940 $parser = new PgSqlParser();
1941 self::assertTrue($parser->hasCreateTableLike("CREATE TABLE t (\nLIKE s\n)"));
1942 }
1943
1944 public function testHasInsertSelectNewlineBeforeSelect(): void
1945 {
1946 $parser = new PgSqlParser();
1947 self::assertTrue($parser->hasInsertSelect("INSERT INTO t\nSELECT * FROM s"));
1948 }
1949
1950 public function testExtractSelectTableNamesNewlineSeparatedFromClause(): void
1951 {
1952 $parser = new PgSqlParser();
1953 $tables = $parser->extractSelectTableNames("SELECT *\nFROM\nusers\nWHERE id = 1");
1954 self::assertContains('users', $tables);
1955 }
1956
1957 public function testHasCreateTableAsSelectNewlineBeforeAs(): void
1958 {
1959 $parser = new PgSqlParser();
1960 self::assertTrue($parser->hasCreateTableAsSelect("CREATE TABLE t\nAS\nSELECT 1"));
1961 }
1962
1963 public function testExtractUpdateFromClauseNewlineBeforeReturning(): void
1964 {
1965 $parser = new PgSqlParser();
1966 self::assertSame('orders', $parser->extractUpdateFromClause("UPDATE users SET name = orders.name FROM orders\nRETURNING *\nWHERE users.id = orders.user_id"));
1967 }
1968
1969 public function testExtractDeleteUsingClauseNewlineBeforeReturning(): void
1970 {
1971 $parser = new PgSqlParser();
1972 self::assertSame('orders', $parser->extractDeleteUsingClause("DELETE FROM users USING orders\nRETURNING *\nWHERE users.id = orders.user_id"));
1973 }
1974
1975 public function testSplitStatementsNewlineInDollarQuote(): void
1976 {
1977 $parser = new PgSqlParser();
1978 $stmts = $parser->splitStatements('SELECT $$hello' . "\n" . 'world$$');
1979 self::assertCount(1, $stmts);
1980 self::assertSame('SELECT $$hello' . "\n" . 'world$$', $stmts[0]);
1981 }
1982
1983 public function testSplitStatementsNewlineInSingleQuote(): void
1984 {
1985 $parser = new PgSqlParser();
1986 $stmts = $parser->splitStatements("SELECT 'hello\nworld'");
1987 self::assertCount(1, $stmts);
1988 self::assertSame("SELECT 'hello\nworld'", $stmts[0]);
1989 }
1990
1991 public function testExtractInsertValuesWithEscapedSingleQuoteInValue(): void
1992 {
1993 $parser = new PgSqlParser();
1994 $values = $parser->extractInsertValues("INSERT INTO t (name) VALUES ('it''s ok')");
1995 self::assertSame([["'it''s ok'"]], $values);
1996 }
1997
1998 public function testExtractOnConflictSetMultipleColumnsExact(): void
1999 {
2000 $parser = new PgSqlParser();
2001 $info = $parser->extractOnConflictUpdateColumns(
2002 'INSERT INTO t (id, a, b) VALUES (1, 2, 3) ON CONFLICT (id) DO UPDATE SET a = EXCLUDED.a, b = EXCLUDED.b'
2003 );
2004 self::assertSame(['a', 'b'], $info['columns']);
2005 self::assertSame(['a' => 'EXCLUDED.a', 'b' => 'EXCLUDED.b'], $info['values']);
2006 }
2007
2008 public function testExtractSelectTableNamesWithInlineJoin(): void
2009 {
2010 $parser = new PgSqlParser();
2011 $tables = $parser->extractSelectTableNames("SELECT * FROM users\nINNER JOIN orders ON users.id = orders.uid");
2012 self::assertContains('users', $tables);
2013 self::assertContains('orders', $tables);
2014 }
2015
2016 public function testExtractSelectTableNamesWithCrossJoin(): void
2017 {
2018 $parser = new PgSqlParser();
2019 $tables = $parser->extractSelectTableNames('SELECT * FROM users CROSS JOIN orders');
2020 self::assertContains('users', $tables);
2021 }
2022
2023 public function testExtractSelectTableNamesWithNaturalJoin(): void
2024 {
2025 $parser = new PgSqlParser();
2026 $tables = $parser->extractSelectTableNames('SELECT * FROM users NATURAL JOIN orders');
2027 self::assertContains('users', $tables);
2028 }
2029
2030 public function testExtractOnConflictWithTrailingWhitespace(): void
2031 {
2032 $parser = new PgSqlParser();
2033 $info = $parser->extractOnConflictUpdateColumns(
2034 "INSERT INTO t (id, name) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name "
2035 );
2036 self::assertSame('EXCLUDED.name', $info['values']['name']);
2037 }
2038
2039 public function testClassifyWithLeadingBlockCommentAndLineComment(): void
2040 {
2041 $parser = new PgSqlParser();
2042 self::assertSame('SELECT', $parser->classifyStatement("/* comment */\n-- line\nSELECT 1"));
2043 }
2044
2045 public function testExtractInsertTableWithSchemaLowercase(): void
2046 {
2047 $parser = new PgSqlParser();
2048 self::assertSame('users', $parser->extractInsertTable('insert into public.users (id) values (1)'));
2049 }
2050
2051 public function testExtractUpdateTableLowercaseOnly(): void
2052 {
2053 $parser = new PgSqlParser();
2054 self::assertSame('users', $parser->extractUpdateTable("update only users set name = 'x'"));
2055 }
2056
2057 public function testExtractDeleteTableLowercaseOnly(): void
2058 {
2059 $parser = new PgSqlParser();
2060 self::assertSame('users', $parser->extractDeleteTable('delete from only users where id = 1'));
2061 }
2062
2063 public function testExtractSelectTableNamesWithFullOuterJoin(): void
2064 {
2065 $parser = new PgSqlParser();
2066 $tables = $parser->extractSelectTableNames('SELECT * FROM users FULL OUTER JOIN orders ON users.id = orders.uid');
2067 self::assertContains('users', $tables);
2068 self::assertContains('orders', $tables);
2069 }
2070
2071 public function testExtractSelectTableNamesWithRightJoin(): void
2072 {
2073 $parser = new PgSqlParser();
2074 $tables = $parser->extractSelectTableNames('SELECT * FROM users RIGHT JOIN orders ON users.id = orders.uid');
2075 self::assertContains('users', $tables);
2076 self::assertContains('orders', $tables);
2077 }
2078
2079 public function testExtractOnConflictColumnTrimmed(): void
2080 {
2081 $parser = new PgSqlParser();
2082 $info = $parser->extractOnConflictUpdateColumns(
2083 "INSERT INTO t (id, name) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name "
2084 );
2085 self::assertSame(['name'], $info['columns']);
2086 self::assertSame('EXCLUDED.name', $info['values']['name']);
2087 }
2088
2089 public function testClassifyWithDeleteTerminal(): void
2090 {
2091 $parser = new PgSqlParser();
2092 self::assertSame('DELETE', $parser->classifyStatement('WITH old AS (SELECT 1) DELETE FROM users WHERE id IN (SELECT * FROM old)'));
2093 }
2094
2095 public function testClassifyWithUpdateTerminal(): void
2096 {
2097 $parser = new PgSqlParser();
2098 self::assertSame('UPDATE', $parser->classifyStatement("WITH vals AS (SELECT 1) UPDATE users SET name = 'x' WHERE id IN (SELECT * FROM vals)"));
2099 }
2100
2101 public function testExtractWhereClauseFromDeleteWithUsing(): void
2102 {
2103 $parser = new PgSqlParser();
2104 self::assertNotNull($parser->extractWhereClause('DELETE FROM users USING orders WHERE users.id = orders.user_id'));
2105 }
2106
2107 public function testExtractUpdateSetsReturningSensitive(): void
2108 {
2109 $parser = new PgSqlParser();
2110 $sets = $parser->extractUpdateSets('UPDATE t SET a = 1 RETURNING *');
2111 self::assertSame(['a' => '1'], $sets);
2112 }
2113
2114 public function testExtractSelectTableNamesCommaJoined(): void
2115 {
2116 $parser = new PgSqlParser();
2117 $tables = $parser->extractSelectTableNames('SELECT * FROM users, orders WHERE users.id = orders.uid');
2118 self::assertContains('users', $tables);
2119 self::assertContains('orders', $tables);
2120 }
2121
2122 public function testExtractSelectTableNamesQuotedTable(): void
2123 {
2124 $parser = new PgSqlParser();
2125 $tables = $parser->extractSelectTableNames('SELECT * FROM "Users" WHERE id = 1');
2126 self::assertContains('Users', $tables);
2127 }
2128
2129 public function testHasOnConflictDoNothingLowercase(): void
2130 {
2131 $parser = new PgSqlParser();
2132 self::assertTrue($parser->hasOnConflict('insert into t (id) values (1) on conflict do nothing'));
2133 }
2134
2135 public function testExtractOnConflictDoNothingReturnsEmptyColumns(): void
2136 {
2137 $parser = new PgSqlParser();
2138 $info = $parser->extractOnConflictUpdateColumns('INSERT INTO t (id) VALUES (1) ON CONFLICT (id) DO NOTHING');
2139 self::assertSame([], $info['columns']);
2140 }
2141
2142 public function testStripSchemaPrefixUnquotedSchema(): void
2143 {
2144 $parser = new PgSqlParser();
2145 self::assertSame('users', $parser->stripSchemaPrefix('public.users'));
2146 }
2147
2148 public function testStripSchemaPrefixQuotedSchemaQuotedTable(): void
2149 {
2150 $parser = new PgSqlParser();
2151 self::assertSame('"users"', $parser->stripSchemaPrefix('"public"."users"'));
2152 }
2153
2154 public function testStripSchemaPrefixNoSchema(): void
2155 {
2156 $parser = new PgSqlParser();
2157 self::assertSame('users', $parser->stripSchemaPrefix('users'));
2158 }
2159
2160 public function testUnquoteIdentifierNoQuotes(): void
2161 {
2162 $parser = new PgSqlParser();
2163 self::assertSame('users', $parser->unquoteIdentifier('users'));
2164 }
2165
2166 public function testUnquoteIdentifierWithDoubleQuotes(): void
2167 {
2168 $parser = new PgSqlParser();
2169 self::assertSame('Users', $parser->unquoteIdentifier('"Users"'));
2170 }
2171
2172 public function testUnquoteIdentifierEscapedDoubleQuotes(): void
2173 {
2174 $parser = new PgSqlParser();
2175 self::assertSame('col"name', $parser->unquoteIdentifier('"col""name"'));
2176 }
2177
2178 public function testExtractInsertColumnsFromMultilineInsert(): void
2179 {
2180 $parser = new PgSqlParser();
2181 $cols = $parser->extractInsertColumns("INSERT INTO t\n(a, b)\nVALUES (1, 2)");
2182 self::assertSame(['a', 'b'], $cols);
2183 }
2184
2185 public function testExtractWhereClauseWithOrderByMultiWord(): void
2186 {
2187 $parser = new PgSqlParser();
2188 self::assertSame('id > 0', $parser->extractWhereClause('DELETE FROM t WHERE id > 0 ORDER BY id'));
2189 }
2190
2191 public function testExtractSelectTableNamesSchemaQualifiedInFrom(): void
2192 {
2193 $parser = new PgSqlParser();
2194 $tables = $parser->extractSelectTableNames('SELECT * FROM public.users');
2195 self::assertContains('users', $tables);
2196 }
2197
2198 public function testExtractInsertValuesWithNewlineAfterValues(): void
2199 {
2200 $parser = new PgSqlParser();
2201 $values = $parser->extractInsertValues("INSERT INTO t (a) VALUES\n(1)");
2202 self::assertSame([['1']], $values);
2203 }
2204
2205 public function testHasInsertSelectWithColumnsBeforeSelect(): void
2206 {
2207 $parser = new PgSqlParser();
2208 self::assertTrue($parser->hasInsertSelect('INSERT INTO t (a, b) SELECT a, b FROM s'));
2209 }
2210
2211 public function testExtractInsertSelectSqlExact(): void
2212 {
2213 $parser = new PgSqlParser();
2214 self::assertSame('SELECT a, b FROM s', $parser->extractInsertSelectSql('INSERT INTO t (a, b) SELECT a, b FROM s'));
2215 }
2216
2217 public function testClassifyWithRecursive(): void
2218 {
2219 $parser = new PgSqlParser();
2220 self::assertSame('SELECT', $parser->classifyStatement('WITH RECURSIVE cte AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 10) SELECT * FROM cte'));
2221 }
2222
2223 public function testSplitStatementsPreservesEscapedBackslash(): void
2224 {
2225 $parser = new PgSqlParser();
2226 $stmts = $parser->splitStatements("SELECT E'a\\\\b'");
2227 self::assertCount(1, $stmts);
2228 self::assertSame("SELECT E'a\\\\b'", $stmts[0]);
2229 }
2230
2231 public function testExtractSelectTableNamesSubqueryNotIncluded(): void
2232 {
2233 $parser = new PgSqlParser();
2234 $tables = $parser->extractSelectTableNames('SELECT * FROM (SELECT 1) AS sub');
2235 self::assertNotContains('sub', $tables);
2236 }
2237
2238 public function testExtractUpdateSetsWithSubqueryInValue(): void
2239 {
2240 $parser = new PgSqlParser();
2241 $sets = $parser->extractUpdateSets('UPDATE t SET count = (SELECT COUNT(*) FROM other) WHERE id = 1');
2242 self::assertArrayHasKey('count', $sets);
2243 }
2244
2245 public function testExtractOnConflictWithQuotedColumnInSet(): void
2246 {
2247 $parser = new PgSqlParser();
2248 $info = $parser->extractOnConflictUpdateColumns(
2249 'INSERT INTO t (id, "Name") VALUES (1, \'a\') ON CONFLICT (id) DO UPDATE SET "Name" = EXCLUDED."Name"'
2250 );
2251 self::assertSame(['Name'], $info['columns']);
2252 }
2253
2254 public function testExtractDeleteAliasWithUsing(): void
2255 {
2256 $parser = new PgSqlParser();
2257 self::assertSame('u', $parser->extractDeleteAlias('DELETE FROM users u USING orders WHERE u.id = orders.user_id'));
2258 }
2259
2260 public function testExtractDeleteAliasWithWhere(): void
2261 {
2262 $parser = new PgSqlParser();
2263 self::assertSame('u', $parser->extractDeleteAlias('DELETE FROM users u WHERE u.id = 1'));
2264 }
2265
2266 public function testExtractDeleteAliasWithReturning(): void
2267 {
2268 $parser = new PgSqlParser();
2269 self::assertSame('u', $parser->extractDeleteAlias('DELETE FROM users u RETURNING *'));
2270 }
2271
2272 public function testClassifySetTransactionAsTcl(): void
2273 {
2274 $parser = new PgSqlParser();
2275 self::assertSame('TCL', $parser->classifyStatement('SET TRANSACTION ISOLATION LEVEL READ COMMITTED'));
2276 }
2277
2278 public function testClassifySavepointAsTcl(): void
2279 {
2280 $parser = new PgSqlParser();
2281 self::assertSame('TCL', $parser->classifyStatement('SAVEPOINT my_save'));
2282 }
2283
2284 public function testClassifyReleaseSavepointAsTcl(): void
2285 {
2286 $parser = new PgSqlParser();
2287 self::assertSame('TCL', $parser->classifyStatement('RELEASE SAVEPOINT my_save'));
2288 }
2289
2290 public function testClassifyStartTransactionAsTcl(): void
2291 {
2292 $parser = new PgSqlParser();
2293 self::assertSame('TCL', $parser->classifyStatement('START TRANSACTION'));
2294 }
2295
2296 public function testExtractInsertValuesMultipleRowsWithSpaces(): void
2297 {
2298 $parser = new PgSqlParser();
2299 $values = $parser->extractInsertValues('INSERT INTO t (a) VALUES ( 1 ) , ( 2 )');
2300 self::assertSame([['1'], ['2']], $values);
2301 }
2302
2303 public function testExtractUpdateAliasWithoutAs(): void
2304 {
2305 $parser = new PgSqlParser();
2306 self::assertSame('u', $parser->extractUpdateAlias("UPDATE users u SET name = 'x'"));
2307 }
2308
2309 public function testExtractUpdateAliasReturnsNullNoAlias(): void
2310 {
2311 $parser = new PgSqlParser();
2312 self::assertNull($parser->extractUpdateAlias("UPDATE users SET name = 'x'"));
2313 }
2314
2315 public function testExtractInsertSelectSqlReturnsNullNoSelect(): void
2316 {
2317 $parser = new PgSqlParser();
2318 self::assertNull($parser->extractInsertSelectSql('INSERT INTO t (a) VALUES (1)'));
2319 }
2320
2321 public function testExtractCreateTableLikeSourceReturnsNullNoLike(): void
2322 {
2323 $parser = new PgSqlParser();
2324 self::assertNull($parser->extractCreateTableLikeSource('CREATE TABLE t (id INTEGER)'));
2325 }
2326
2327 public function testClassifyWithLeadingLineCommentNoNewline(): void
2328 {
2329 $parser = new PgSqlParser();
2330 self::assertNull($parser->classifyStatement('-- comment only'));
2331 }
2332
2333 public function testExtractSelectTableNamesMultipleFromClauses(): void
2334 {
2335 $parser = new PgSqlParser();
2336 $tables = $parser->extractSelectTableNames('SELECT * FROM users WHERE id IN (SELECT uid FROM orders)');
2337 self::assertContains('users', $tables);
2338 self::assertContains('orders', $tables);
2339 }
2340
2341 public function testExtractWhereClauseFromMultilineUpdate(): void
2342 {
2343 $parser = new PgSqlParser();
2344 self::assertSame('id = 1', $parser->extractWhereClause("UPDATE t\nSET name = 'Bob'\nWHERE\nid = 1"));
2345 }
2346
2347 public function testHasCreateTableAsSelectIfNotExists(): void
2348 {
2349 $parser = new PgSqlParser();
2350 self::assertTrue($parser->hasCreateTableAsSelect('CREATE TABLE IF NOT EXISTS t AS SELECT 1'));
2351 }
2352
2353 public function testExtractDeleteAliasAtEndOfStatement(): void
2354 {
2355 $parser = new PgSqlParser();
2356 self::assertNull($parser->extractDeleteAlias('DELETE FROM users AS u'));
2357 }
2358
2359 public function testExtractOnConflictWhereClauseRemoved(): void
2360 {
2361 $parser = new PgSqlParser();
2362 $info = $parser->extractOnConflictUpdateColumns(
2363 "INSERT INTO t (id, name) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name WHERE t.active = true"
2364 );
2365 self::assertSame(['name'], $info['columns']);
2366 self::assertSame('EXCLUDED.name', $info['values']['name']);
2367 }
2368
2369 public function testExtractInsertValuesEscapedQuoteMiddle(): void
2370 {
2371 $parser = new PgSqlParser();
2372 $values = $parser->extractInsertValues("INSERT INTO t (a, b) VALUES ('it''s', 'ok')");
2373 self::assertCount(1, $values);
2374 self::assertCount(2, $values[0]);
2375 }
2376
2377 public function testExtractInsertTableReturnsNullOnEmpty(): void
2378 {
2379 $parser = new PgSqlParser();
2380 self::assertNull($parser->extractInsertTable('NOT AN INSERT'));
2381 }
2382
2383 public function testExtractDeleteTableReturnsNullOnNonDelete(): void
2384 {
2385 $parser = new PgSqlParser();
2386 self::assertNull($parser->extractDeleteTable('SELECT * FROM users'));
2387 }
2388
2389 public function testExtractCreateTableNameReturnsNullNonCreate(): void
2390 {
2391 $parser = new PgSqlParser();
2392 self::assertNull($parser->extractCreateTableName('SELECT 1'));
2393 }
2394
2395 public function testExtractDropTableNameReturnsNullNonDrop(): void
2396 {
2397 $parser = new PgSqlParser();
2398 self::assertNull($parser->extractDropTableName('SELECT 1'));
2399 }
2400
2401 public function testExtractTruncateTableReturnsNullNonTruncate(): void
2402 {
2403 $parser = new PgSqlParser();
2404 self::assertNull($parser->extractTruncateTable('SELECT 1'));
2405 }
2406
2407 public function testExtractAlterTableNameReturnsNullNonAlter(): void
2408 {
2409 $parser = new PgSqlParser();
2410 self::assertNull($parser->extractAlterTableName('SELECT 1'));
2411 }
2412
2413 public function testSplitByTopLevelCommaViaUpdateSetsWithParentheses(): void
2414 {
2415 $parser = new PgSqlParser();
2416 $sets = $parser->extractUpdateSets('UPDATE t SET a = fn(1, 2), b = 3 WHERE id = 1');
2417 self::assertSame(['a' => 'fn(1, 2)', 'b' => '3'], $sets);
2418 }
2419
2420 public function testSplitByTopLevelCommaViaSetsWithSingleQuotedComma(): void
2421 {
2422 $parser = new PgSqlParser();
2423 $sets = $parser->extractUpdateSets("UPDATE t SET name = 'a,b', val = 1 WHERE id = 1");
2424 self::assertSame(['name' => "'a,b'", 'val' => '1'], $sets);
2425 }
2426
2427 public function testSplitByTopLevelCommaViaSetsWithDoubleQuotedComma(): void
2428 {
2429 $parser = new PgSqlParser();
2430 $sets = $parser->extractUpdateSets('UPDATE t SET "a,b" = 1, c = 2 WHERE id = 1');
2431 self::assertSame(['a,b' => '1', 'c' => '2'], $sets);
2432 }
2433
2434 public function testSplitByTopLevelCommaViaSetsWithEscapedQuote(): void
2435 {
2436 $parser = new PgSqlParser();
2437 $sets = $parser->extractUpdateSets("UPDATE t SET name = 'it''s', val = 1 WHERE id = 1");
2438 self::assertSame(['name' => "'it''s'", 'val' => '1'], $sets);
2439 }
2440
2441 public function testSplitByTopLevelCommaViaSetsWithQuotedColumn(): void
2442 {
2443 $parser = new PgSqlParser();
2444 $sets = $parser->extractUpdateSets('UPDATE t SET "col_a" = 1, b = 2 WHERE id = 1');
2445 self::assertSame(['col_a' => '1', 'b' => '2'], $sets);
2446 }
2447
2448 public function testExtractSelectTableNamesSkipsSubqueryInFrom(): void
2449 {
2450 $parser = new PgSqlParser();
2451 $tables = $parser->extractSelectTableNames('SELECT * FROM users, (SELECT 1) sub');
2452 self::assertContains('users', $tables);
2453 self::assertNotContains('1', $tables);
2454 }
2455
2456 public function testExtractSelectTableNamesWithLateralJoin(): void
2457 {
2458 $parser = new PgSqlParser();
2459 $tables = $parser->extractSelectTableNames('SELECT * FROM users JOIN orders ON users.id = orders.uid WHERE 1=1');
2460 self::assertContains('users', $tables);
2461 self::assertContains('orders', $tables);
2462 }
2463
2464 public function testClassifyWithStatementQuotedIdentifier(): void
2465 {
2466 $parser = new PgSqlParser();
2467 self::assertSame('SELECT', $parser->classifyStatement('WITH "my cte" AS (SELECT 1) SELECT * FROM "my cte"'));
2468 }
2469
2470 public function testHasInsertSelectFalseForInsertValues(): void
2471 {
2472 $parser = new PgSqlParser();
2473 self::assertFalse($parser->hasInsertSelect('INSERT INTO t (id) VALUES (1)'));
2474 }
2475
2476 public function testHasOnConflictFalseForPlainInsert(): void
2477 {
2478 $parser = new PgSqlParser();
2479 self::assertFalse($parser->hasOnConflict('INSERT INTO t (id) VALUES (1)'));
2480 }
2481
2482 public function testHasIfNotExistsFalse(): void
2483 {
2484 $parser = new PgSqlParser();
2485 self::assertFalse($parser->hasIfNotExists('CREATE TABLE t (id INTEGER)'));
2486 }
2487
2488 public function testHasDropTableIfExistsFalse(): void
2489 {
2490 $parser = new PgSqlParser();
2491 self::assertFalse($parser->hasDropTableIfExists('DROP TABLE t'));
2492 }
2493
2494 public function testHasCreateTableAsSelectFalse(): void
2495 {
2496 $parser = new PgSqlParser();
2497 self::assertFalse($parser->hasCreateTableAsSelect('CREATE TABLE t (id INTEGER)'));
2498 }
2499
2500 public function testHasCreateTableLikeFalse(): void
2501 {
2502 $parser = new PgSqlParser();
2503 self::assertFalse($parser->hasCreateTableLike('CREATE TABLE t (id INTEGER)'));
2504 }
2505
2506 public function testExtractUpdateSetsEmptySetClause(): void
2507 {
2508 $parser = new PgSqlParser();
2509 $sets = $parser->extractUpdateSets('UPDATE t WHERE id = 1');
2510 self::assertSame([], $sets);
2511 }
2512
2513 public function testExtractInsertColumnsNoParens(): void
2514 {
2515 $parser = new PgSqlParser();
2516 $cols = $parser->extractInsertColumns('INSERT INTO t VALUES (1)');
2517 self::assertSame([], $cols);
2518 }
2519
2520 public function testExtractInsertValuesNoValues(): void
2521 {
2522 $parser = new PgSqlParser();
2523 $values = $parser->extractInsertValues('INSERT INTO t SELECT * FROM s');
2524 self::assertSame([], $values);
2525 }
2526
2527 public function testClassifyWithCteAlias(): void
2528 {
2529 $parser = new PgSqlParser();
2530 self::assertSame('INSERT', $parser->classifyStatement('WITH data AS (SELECT 1 AS id) INSERT INTO t SELECT * FROM data'));
2531 }
2532
2533 public function testExtractInsertValuesWithNewlineInStringValue(): void
2534 {
2535 $parser = new PgSqlParser();
2536 $values = $parser->extractInsertValues("INSERT INTO t (note) VALUES ('line1\nline2')");
2537 self::assertCount(1, $values);
2538 }
2539
2540 public function testExtractSelectTableNamesForUpdateClause(): void
2541 {
2542 $parser = new PgSqlParser();
2543 $tables = $parser->extractSelectTableNames('SELECT * FROM users FOR UPDATE');
2544 self::assertContains('users', $tables);
2545 }
2546
2547 public function testExtractUpdateFromClauseWithMultipleTables(): void
2548 {
2549 $parser = new PgSqlParser();
2550 $from = $parser->extractUpdateFromClause('UPDATE t SET a = s.a FROM s, r WHERE t.id = s.id');
2551 self::assertNotNull($from);
2552 self::assertStringContainsString('s', $from);
2553 }
2554
2555 public function testExtractWhereClauseExactTrimming(): void
2556 {
2557 $parser = new PgSqlParser();
2558 $where = $parser->extractWhereClause('DELETE FROM t WHERE id = 1 ');
2559 self::assertSame('id = 1', $where);
2560 }
2561
2562 public function testExtractUpdateFromClauseExactTrimming(): void
2563 {
2564 $parser = new PgSqlParser();
2565 $from = $parser->extractUpdateFromClause('UPDATE t SET a = s.a FROM other_table WHERE t.id = other_table.id');
2566 self::assertSame('other_table', $from);
2567 }
2568
2569 public function testExtractDeleteUsingClauseExactTrimming(): void
2570 {
2571 $parser = new PgSqlParser();
2572 $using = $parser->extractDeleteUsingClause('DELETE FROM t USING other_table WHERE t.id = other_table.id');
2573 self::assertSame('other_table', $using);
2574 }
2575
2576 public function testExtractCreateTableSelectSqlExactTrimming(): void
2577 {
2578 $parser = new PgSqlParser();
2579 self::assertSame('SELECT 1', $parser->extractCreateTableSelectSql('CREATE TABLE t AS SELECT 1 '));
2580 }
2581
2582 public function testClassifyEmptyStringReturnsNull(): void
2583 {
2584 $parser = new PgSqlParser();
2585 self::assertNull($parser->classifyStatement(''));
2586 }
2587
2588 public function testExtractSelectTableNamesWithGroupByClause(): void
2589 {
2590 $parser = new PgSqlParser();
2591 $tables = $parser->extractSelectTableNames('SELECT count(*) FROM users GROUP BY name');
2592 self::assertContains('users', $tables);
2593 }
2594
2595 public function testExtractSelectTableNamesWithHavingClause(): void
2596 {
2597 $parser = new PgSqlParser();
2598 $tables = $parser->extractSelectTableNames('SELECT count(*) FROM users GROUP BY name HAVING count(*) > 1');
2599 self::assertContains('users', $tables);
2600 }
2601
2602 public function testExtractSelectTableNamesWithOffsetClause(): void
2603 {
2604 $parser = new PgSqlParser();
2605 $tables = $parser->extractSelectTableNames('SELECT * FROM users LIMIT 10 OFFSET 5');
2606 self::assertContains('users', $tables);
2607 }
2608
2609 public function testExtractOnConflictUpdateColumnsMultilineAssignment(): void
2610 {
2611 $parser = new PgSqlParser();
2612 $sql = "INSERT INTO users (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO UPDATE SET name =\n'Bob'";
2613 $result = $parser->extractOnConflictUpdateColumns($sql);
2614 self::assertSame(['name'], $result['columns']);
2615 }
2616
2617 public function testHasInsertSelectLowercaseValues(): void
2618 {
2619 $parser = new PgSqlParser();
2620 self::assertFalse($parser->hasInsertSelect('INSERT INTO users (id) values (1)'));
2621 }
2622
2623 public function testExtractWhereClauseWithMultilineReturning(): void
2624 {
2625 $parser = new PgSqlParser();
2626 $sql = "UPDATE users SET name = 'Bob' WHERE id = 1\nRETURNING *";
2627 $where = $parser->extractWhereClause($sql);
2628 self::assertSame('id = 1', $where);
2629 }
2630
2631 public function testExtractWhereClauseWithMultilineOrderBy(): void
2632 {
2633 $parser = new PgSqlParser();
2634 $sql = "DELETE FROM users WHERE active = false\nORDER BY id";
2635 $where = $parser->extractWhereClause($sql);
2636 self::assertSame('active = false', $where);
2637 }
2638
2639 public function testExtractWhereClauseWithMultilineLimit(): void
2640 {
2641 $parser = new PgSqlParser();
2642 $sql = "DELETE FROM users WHERE active = false\nLIMIT 10";
2643 $where = $parser->extractWhereClause($sql);
2644 self::assertSame('active = false', $where);
2645 }
2646
2647 public function testExtractUpdateFromClauseMultilineFrom(): void
2648 {
2649 $parser = new PgSqlParser();
2650 $sql = "UPDATE users SET name = orders.name\nFROM\norders WHERE users.id = orders.user_id";
2651 $from = $parser->extractUpdateFromClause($sql);
2652 self::assertNotNull($from);
2653 self::assertStringContainsString('orders', $from);
2654 }
2655
2656 public function testExtractSelectTableNamesLowercaseTableRef(): void
2657 {
2658 $parser = new PgSqlParser();
2659 $tables = $parser->extractSelectTableNames('select * from users join orders on users.id = orders.user_id');
2660 self::assertContains('users', $tables);
2661 self::assertContains('orders', $tables);
2662 }
2663
2664 public function testExtractUpdateSetsMultilineAssignment(): void
2665 {
2666 $parser = new PgSqlParser();
2667 $sql = "UPDATE users SET bio =\n'hello\nworld' WHERE id = 1";
2668 $sets = $parser->extractUpdateSets($sql);
2669 self::assertArrayHasKey('bio', $sets);
2670 }
2671
2672 public function testExtractInsertValuesMultilineInsideParens(): void
2673 {
2674 $parser = new PgSqlParser();
2675 $sql = "INSERT INTO users (id, name) VALUES (1,\n'Alice')";
2676 $vals = $parser->extractInsertValues($sql);
2677 self::assertCount(1, $vals);
2678 self::assertCount(2, $vals[0]);
2679 }
2680
2681 public function testClassifyInsertSelectNotMisclassifiedAsSelect(): void
2682 {
2683 $parser = new PgSqlParser();
2684 self::assertSame('INSERT', $parser->classifyStatement('INSERT INTO users SELECT 1'));
2685 }
2686
2687 public function testClassifyUpdateContainingSelectNotMisclassified(): void
2688 {
2689 $parser = new PgSqlParser();
2690 self::assertSame('UPDATE', $parser->classifyStatement("UPDATE users SET note = 'SELECT me' WHERE id = 1"));
2691 }
2692
2693 public function testClassifyDeleteContainingInsertNotMisclassified(): void
2694 {
2695 $parser = new PgSqlParser();
2696 self::assertSame('DELETE', $parser->classifyStatement("DELETE FROM users WHERE note = 'INSERT this'"));
2697 }
2698
2699 public function testClassifyTruncateWithDeleteInComment(): void
2700 {
2701 $parser = new PgSqlParser();
2702 self::assertSame('TRUNCATE', $parser->classifyStatement('TRUNCATE TABLE t /* DELETE FROM t */'));
2703 }
2704
2705 public function testClassifyCreateTableWithTruncateInDefault(): void
2706 {
2707 $parser = new PgSqlParser();
2708 self::assertSame('CREATE_TABLE', $parser->classifyStatement("CREATE TABLE t (note TEXT DEFAULT 'TRUNCATE TABLE x')"));
2709 }
2710
2711 public function testClassifyDropTableWithCreateTableInComment(): void
2712 {
2713 $parser = new PgSqlParser();
2714 self::assertSame('DROP_TABLE', $parser->classifyStatement('DROP TABLE t /* CREATE TABLE backup (id INT) */'));
2715 }
2716
2717 public function testClassifyAlterTableWithDropTableInComment(): void
2718 {
2719 $parser = new PgSqlParser();
2720 self::assertSame('ALTER_TABLE', $parser->classifyStatement('ALTER TABLE t ADD COLUMN x TEXT /* DROP TABLE t */'));
2721 }
2722
2723 public function testClassifyTclWithAlterTableInComment(): void
2724 {
2725 $parser = new PgSqlParser();
2726 self::assertSame('TCL', $parser->classifyStatement('BEGIN /* ALTER TABLE t ADD COLUMN x TEXT */'));
2727 }
2728
2729 public function testClassifyWithStatementStartAnchor(): void
2730 {
2731 $parser = new PgSqlParser();
2732 self::assertSame('SELECT', $parser->classifyStatement('SELECT * FROM with_data'));
2733 }
2734
2735 public function testExtractOnConflictColumnsLowercaseAs(): void
2736 {
2737 $parser = new PgSqlParser();
2738 $sql = "INSERT INTO users (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO UPDATE SET name = excluded.name";
2739 $result = $parser->extractOnConflictUpdateColumns($sql);
2740 self::assertSame(['name'], $result['columns']);
2741 }
2742
2743 public function testExtractUpdateSetsAssignmentWithNewlineInValue(): void
2744 {
2745 $parser = new PgSqlParser();
2746 $sql = "UPDATE users SET name = 'line1\nline2' WHERE id = 1";
2747 $sets = $parser->extractUpdateSets($sql);
2748 self::assertArrayHasKey('name', $sets);
2749 self::assertStringContainsString('line1', $sets['name']);
2750 }
2751
2752 public function testExtractWhereClauseLowercaseReturning(): void
2753 {
2754 $parser = new PgSqlParser();
2755 $sql = "UPDATE users SET name = 'Bob' WHERE id = 1 returning *";
2756 $where = $parser->extractWhereClause($sql);
2757 self::assertSame('id = 1', $where);
2758 }
2759
2760 public function testExtractWhereClauseLowercaseOrderBy(): void
2761 {
2762 $parser = new PgSqlParser();
2763 $sql = 'DELETE FROM users WHERE active = false order by id';
2764 $where = $parser->extractWhereClause($sql);
2765 self::assertSame('active = false', $where);
2766 }
2767
2768 public function testExtractWhereClauseLowercaseLimit(): void
2769 {
2770 $parser = new PgSqlParser();
2771 $sql = 'DELETE FROM users WHERE active = false limit 10';
2772 $where = $parser->extractWhereClause($sql);
2773 self::assertSame('active = false', $where);
2774 }
2775
2776 public function testExtractDeleteUsingClauseLowercaseUsing(): void
2777 {
2778 $parser = new PgSqlParser();
2779 $sql = 'delete from users using orders where users.id = orders.user_id';
2780 $using = $parser->extractDeleteUsingClause($sql);
2781 self::assertSame('orders', $using);
2782 }
2783
2784 public function testExtractSelectTableNamesWithMultilineJoin(): void
2785 {
2786 $parser = new PgSqlParser();
2787 $sql = "SELECT u.id\nFROM users u\nJOIN orders o ON u.id = o.user_id";
2788 $tables = $parser->extractSelectTableNames($sql);
2789 self::assertContains('users', $tables);
2790 }
2791
2792 public function testExtractSelectTableNamesLowercaseFrom(): void
2793 {
2794 $parser = new PgSqlParser();
2795 $tables = $parser->extractSelectTableNames('select id from users');
2796 self::assertContains('users', $tables);
2797 }
2798
2799 public function testExtractOnConflictColumnsMultilineSetWithWhereFilter(): void
2800 {
2801 $parser = new PgSqlParser();
2802 $sql = "INSERT INTO users (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name\nWHERE users.id > 0";
2803 $result = $parser->extractOnConflictUpdateColumns($sql);
2804 self::assertSame(['name'], $result['columns']);
2805 }
2806
2807 public function testExtractSelectTableNamesCaseInsensitiveJoin(): void
2808 {
2809 $parser = new PgSqlParser();
2810 $tables = $parser->extractSelectTableNames('select u.id from users u inner join orders o on u.id = o.user_id');
2811 self::assertContains('users', $tables);
2812 self::assertContains('orders', $tables);
2813 }
2814
2815 public function testExtractDeleteUsingClauseWithNewlineBeforeWhere(): void
2816 {
2817 $parser = new PgSqlParser();
2818 $sql = "DELETE FROM users USING orders\nWHERE users.id = orders.user_id";
2819 $using = $parser->extractDeleteUsingClause($sql);
2820 self::assertNotNull($using);
2821 self::assertStringContainsString('orders', $using);
2822 }
2823
2824 public function testClassifyDeleteContainingUpdateKeyword(): void
2825 {
2826 $parser = new PgSqlParser();
2827 self::assertSame('DELETE', $parser->classifyStatement("DELETE FROM users WHERE action = 'UPDATE'"));
2828 }
2829
2830 public function testClassifyCreateTableContainingSelectKeyword(): void
2831 {
2832 $parser = new PgSqlParser();
2833 self::assertSame('CREATE_TABLE', $parser->classifyStatement('CREATE TABLE select_log (id INTEGER, query TEXT)'));
2834 }
2835
2836 public function testHasInsertSelectCaseInsensitive(): void
2837 {
2838 $parser = new PgSqlParser();
2839 self::assertTrue($parser->hasInsertSelect('INSERT INTO archive SELECT * FROM users'));
2840 self::assertFalse($parser->hasInsertSelect('INSERT INTO users (id) VALUES (1)'));
2841 }
2842
2843 public function testSplitStatementsSemicolonInsideLineComment(): void
2844 {
2845 $parser = new PgSqlParser();
2846 $stmts = $parser->splitStatements("SELECT 1 -- comment with ; semicolon\n; SELECT 2");
2847 self::assertCount(2, $stmts);
2848 self::assertStringContainsString('SELECT 1', $stmts[0]);
2849 self::assertStringContainsString('SELECT 2', $stmts[1]);
2850 }
2851
2852 public function testClassifyStatementWithTwoLeadingLineComments(): void
2853 {
2854 $parser = new PgSqlParser();
2855 $sql = "-- first comment\n-- second comment\nSELECT 1";
2856 self::assertSame('SELECT', $parser->classifyStatement($sql));
2857 }
2858
2859 public function testClassifyStatementLeadingLineAndBlockComments(): void
2860 {
2861 $parser = new PgSqlParser();
2862 $sql = "-- line comment\n/* block comment */\nSELECT 1";
2863 self::assertSame('SELECT', $parser->classifyStatement($sql));
2864 }
2865
2866 public function testClassifyWithStatementQuotedIdentifierWithParen(): void
2867 {
2868 $parser = new PgSqlParser();
2869 $sql = 'WITH "cte(test)" AS (SELECT 1 AS id) SELECT * FROM "cte(test)"';
2870 self::assertSame('SELECT', $parser->classifyStatement($sql));
2871 }
2872
2873 public function testExtractOnConflictUpdateColumnsWithLeadingWhitespace(): void
2874 {
2875 $parser = new PgSqlParser();
2876 $sql = "INSERT INTO t (id, name) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET name = 'b' ";
2877 $result = $parser->extractOnConflictUpdateColumns($sql);
2878 self::assertSame(['name'], $result['columns']);
2879 self::assertSame('b', trim($result['values']['name'], "' "));
2880 }
2881
2882 public function testExtractUpdateSetsWithLeadingWhitespace(): void
2883 {
2884 $parser = new PgSqlParser();
2885 $result = $parser->extractUpdateSets("UPDATE t SET name = 'Bob' WHERE id = 1");
2886 self::assertSame(['name'], array_keys($result));
2887 }
2888
2889 public function testExtractUpdateSetsWithNewlineInSetClause(): void
2890 {
2891 $parser = new PgSqlParser();
2892 $result = $parser->extractUpdateSets("UPDATE t SET name = 'Bob',\nemail = 'b@b.com' WHERE id = 1");
2893 self::assertSame(['name', 'email'], array_keys($result));
2894 }
2895
2896 public function testExtractWhereClauseReturnsTrimmedResult(): void
2897 {
2898 $parser = new PgSqlParser();
2899 $where = $parser->extractWhereClause('UPDATE t SET name = 1 WHERE id = 1 ');
2900 self::assertSame('id = 1', $where);
2901 }
2902
2903 public function testExtractUpdateFromClauseReturnsTrimmedResult(): void
2904 {
2905 $parser = new PgSqlParser();
2906 $from = $parser->extractUpdateFromClause('UPDATE t SET name = o.name FROM orders o WHERE t.id = o.id');
2907 self::assertNotNull($from);
2908 self::assertSame('orders o', $from);
2909 }
2910
2911 public function testExtractDeleteUsingClauseReturnsTrimmedResult(): void
2912 {
2913 $parser = new PgSqlParser();
2914 $using = $parser->extractDeleteUsingClause('DELETE FROM t USING orders WHERE t.id = orders.id');
2915 self::assertNotNull($using);
2916 self::assertSame('orders', $using);
2917 }
2918
2919 public function testExtractCreateTableSelectSqlWithDollarAnchor(): void
2920 {
2921 $parser = new PgSqlParser();
2922 $sql = 'CREATE TABLE t AS SELECT id, name FROM users';
2923 $selectSql = $parser->extractCreateTableSelectSql($sql);
2924 self::assertSame('SELECT id, name FROM users', $selectSql);
2925 }
2926
2927 public function testExtractSelectTableNamesFromClauseWithInnerJoin(): void
2928 {
2929 $parser = new PgSqlParser();
2930 $tables = $parser->extractSelectTableNames('SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id');
2931 self::assertContains('users', $tables);
2932 self::assertContains('orders', $tables);
2933 }
2934
2935 public function testExtractSelectTableNamesLowercaseAs(): void
2936 {
2937 $parser = new PgSqlParser();
2938 $tables = $parser->extractSelectTableNames('SELECT * FROM users as u');
2939 self::assertContains('users', $tables);
2940 self::assertNotContains('u', $tables);
2941 }
2942
2943 public function testExtractSelectTableNamesFromClauseStripLeftJoin(): void
2944 {
2945 $parser = new PgSqlParser();
2946 $tables = $parser->extractSelectTableNames('SELECT * FROM users LEFT JOIN orders ON TRUE');
2947 self::assertContains('users', $tables);
2948 self::assertContains('orders', $tables);
2949 }
2950
2951 public function testExtractSelectTableNamesSkipsSubquery(): void
2952 {
2953 $parser = new PgSqlParser();
2954 $tables = $parser->extractSelectTableNames('SELECT * FROM (SELECT 1) AS sub');
2955 self::assertSame([], $tables);
2956 }
2957
2958 public function testExtractSelectTableNamesSchemaQualifiedUnquoted(): void
2959 {
2960 $parser = new PgSqlParser();
2961 $tables = $parser->extractSelectTableNames('SELECT * FROM public.users');
2962 self::assertContains('users', $tables);
2963 }
2964
2965 public function testExtractSelectTableNamesSchemaQualifiedUnquotedDotQuoted(): void
2966 {
2967 $parser = new PgSqlParser();
2968 $tables = $parser->extractSelectTableNames('SELECT * FROM public."users"');
2969 self::assertContains('users', $tables);
2970 }
2971
2972 public function testStripSchemaPrefixQuoted(): void
2973 {
2974 $parser = new PgSqlParser();
2975 self::assertSame('"users"', $parser->stripSchemaPrefix('"public"."users"'));
2976 }
2977
2978 public function testStripSchemaPrefixUnquoted(): void
2979 {
2980 $parser = new PgSqlParser();
2981 self::assertSame('users', $parser->stripSchemaPrefix('public.users'));
2982 }
2983
2984 public function testIsSqlKeywordBlocksKeywordAsTableName(): void
2985 {
2986 $parser = new PgSqlParser();
2987 $tables = $parser->extractSelectTableNames('SELECT * FROM select');
2988 self::assertNotContains('select', $tables);
2989 }
2990
2991 public function testClassifySimpleStatementWithLeadingWhitespace(): void
2992 {
2993 $parser = new PgSqlParser();
2994 self::assertSame('SELECT', $parser->classifyStatement(' SELECT 1'));
2995 }
2996
2997 public function testExtractSelectTableNamesWithLeadingWhitespaceInPart(): void
2998 {
2999 $parser = new PgSqlParser();
3000 $tables = $parser->extractSelectTableNames('SELECT * FROM users ');
3001 self::assertContains('users', $tables);
3002 }
3003
3004
3005
3006
3007
3008 public function testClassifyTclBEGINWithAlterTableComment(): void
3009 {
3010 $parser = new PgSqlParser();
3011 self::assertSame('TCL', $parser->classifyStatement('BEGIN'));
3012 }
3013
3014 public function testExtractOnConflictColumnsWithLowercaseDoUpdateSet(): void
3015 {
3016 $parser = new PgSqlParser();
3017 $sql = "INSERT INTO t (id, name) VALUES (1, 'a') on conflict (id) do update set name = 'b'";
3018 $result = $parser->extractOnConflictUpdateColumns($sql);
3019 self::assertSame(['name'], $result['columns']);
3020 }
3021
3022 public function testExtractOnConflictColumnsMultilineAssignment(): void
3023 {
3024 $parser = new PgSqlParser();
3025 $sql = "INSERT INTO t (id, name) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET\nname = 'b'";
3026 $result = $parser->extractOnConflictUpdateColumns($sql);
3027 self::assertSame(['name'], $result['columns']);
3028 }
3029
3030 public function testExtractUpdateSetsLowercaseKeywords(): void
3031 {
3032 $parser = new PgSqlParser();
3033 $result = $parser->extractUpdateSets("update t set name = 'Bob' where id = 1");
3034 self::assertSame(['name'], array_keys($result));
3035 }
3036
3037 public function testExtractUpdateSetsMultilineSetToFrom(): void
3038 {
3039 $parser = new PgSqlParser();
3040 $result = $parser->extractUpdateSets("UPDATE t SET name = 'Bob'\nFROM other WHERE t.id = other.id");
3041 self::assertSame(['name'], array_keys($result));
3042 }
3043
3044 public function testSplitStatementsPreservesSemicolonInsideSingleQuote(): void
3045 {
3046 $parser = new PgSqlParser();
3047 $stmts = $parser->splitStatements("SELECT 'a;b'; SELECT 2");
3048 self::assertCount(2, $stmts);
3049 self::assertStringContainsString("'a;b'", $stmts[0]);
3050 }
3051
3052 public function testSplitStatementsPreservesSemicolonInsideDoubleQuote(): void
3053 {
3054 $parser = new PgSqlParser();
3055 $stmts = $parser->splitStatements('SELECT "col;name" FROM t; SELECT 2');
3056 self::assertCount(2, $stmts);
3057 self::assertStringContainsString('"col;name"', $stmts[0]);
3058 }
3059
3060 public function testSplitStatementsPreservesSemicolonInsideDollarQuote(): void
3061 {
3062 $parser = new PgSqlParser();
3063 $stmts = $parser->splitStatements('SELECT $$a;b$$; SELECT 2');
3064 self::assertCount(2, $stmts);
3065 }
3066
3067 public function testSplitStatementsPreservesSemicolonInsideBlockComment(): void
3068 {
3069 $parser = new PgSqlParser();
3070 $stmts = $parser->splitStatements('SELECT 1 /* comment ; here */; SELECT 2');
3071 self::assertCount(2, $stmts);
3072 }
3073
3074 public function testExtractSelectTableNamesStripsCrossJoinFromClause(): void
3075 {
3076 $parser = new PgSqlParser();
3077 $tables = $parser->extractSelectTableNames('SELECT * FROM users CROSS JOIN orders');
3078 self::assertContains('users', $tables);
3079 self::assertContains('orders', $tables);
3080 }
3081
3082 public function testExtractSelectTableNamesStripsNaturalJoinFromClause(): void
3083 {
3084 $parser = new PgSqlParser();
3085 $tables = $parser->extractSelectTableNames('SELECT * FROM users NATURAL JOIN orders');
3086 self::assertContains('users', $tables);
3087 self::assertContains('orders', $tables);
3088 }
3089
3090
3091 public function testExtractWhereClauseWithNewlineBeforeReturning(): void
3092 {
3093 $parser = new PgSqlParser();
3094 $where = $parser->extractWhereClause("DELETE FROM t WHERE id = 1\nRETURNING *");
3095 self::assertSame('id = 1', $where);
3096 }
3097
3098 public function testHasCreateTableLikeLowercaseLike(): void
3099 {
3100 $parser = new PgSqlParser();
3101 self::assertTrue($parser->hasCreateTableLike('CREATE TABLE t (like other_table)'));
3102 }
3103
3104 public function testMapStrippedOffsetWhenLengthsMatch(): void
3105 {
3106 $parser = new PgSqlParser();
3107 $where = $parser->extractWhereClause('DELETE FROM t WHERE id = 1');
3108 self::assertSame('id = 1', $where);
3109 }
3110
3111 public function testMapStrippedOffsetWithStringLiteral(): void
3112 {
3113 $parser = new PgSqlParser();
3114 $where = $parser->extractWhereClause("DELETE FROM t WHERE name = 'hello' AND id = 1");
3115 self::assertNotNull($where);
3116 self::assertStringContainsString('hello', $where);
3117 self::assertStringContainsString('id = 1', $where);
3118 }
3119
3120 public function testReturnRemovalFromMapStrippedOffsetToOriginal(): void
3121 {
3122 $parser = new PgSqlParser();
3123 $where = $parser->extractWhereClause("UPDATE t SET x = 1 WHERE name = 'foo' RETURNING id");
3124 self::assertNotNull($where);
3125 self::assertStringNotContainsString('RETURNING', $where);
3126 }
3127
3128 public function testExtractSelectTableNamesEmptyTrimmedPartSkipped(): void
3129 {
3130 $parser = new PgSqlParser();
3131 $tables = $parser->extractSelectTableNames('SELECT * FROM users');
3132 self::assertContains('users', $tables);
3133 }
3134
3135 public function testClassifyStatementSETTransactionTCL(): void
3136 {
3137 $parser = new PgSqlParser();
3138 self::assertSame('TCL', $parser->classifyStatement('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'));
3139 }
3140
3141 public function testClassifyStatementStartTransaction(): void
3142 {
3143 $parser = new PgSqlParser();
3144 self::assertSame('TCL', $parser->classifyStatement('START TRANSACTION'));
3145 }
3146
3147 public function testExtractOnConflictColumnsValueWithNewline(): void
3148 {
3149 $parser = new PgSqlParser();
3150 $sql = "INSERT INTO t (id, name) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name\n|| ' updated'";
3151 $result = $parser->extractOnConflictUpdateColumns($sql);
3152 self::assertSame(['name'], $result['columns']);
3153 self::assertStringContainsString('updated', $result['values']['name']);
3154 }
3155
3156 public function testExtractOnConflictColumnsAssignmentWithPaddedEquals(): void
3157 {
3158 $parser = new PgSqlParser();
3159 $sql = 'INSERT INTO t (id, x) VALUES (1, 2) ON CONFLICT (id) DO UPDATE SET x = 3 ';
3160 $result = $parser->extractOnConflictUpdateColumns($sql);
3161 self::assertSame(['x'], $result['columns']);
3162 self::assertSame('3', $result['values']['x']);
3163 }
3164
3165 public function testHasInsertSelectLowercaseKeywords(): void
3166 {
3167 $parser = new PgSqlParser();
3168 self::assertTrue($parser->hasInsertSelect('insert into archive select * from users'));
3169 }
3170
3171 public function testHasInsertSelectWithNewlineBeforeSelect(): void
3172 {
3173 $parser = new PgSqlParser();
3174 self::assertTrue($parser->hasInsertSelect("INSERT INTO archive\nSELECT * FROM users"));
3175 }
3176
3177 public function testHasInsertSelectWithValuesReturnsFalse(): void
3178 {
3179 $parser = new PgSqlParser();
3180 self::assertFalse($parser->hasInsertSelect('INSERT INTO t (id) VALUES (1)'));
3181 }
3182
3183 public function testExtractUpdateSetsWithPaddedAssignment(): void
3184 {
3185 $parser = new PgSqlParser();
3186 $result = $parser->extractUpdateSets('UPDATE t SET x = 1 , y = 2 WHERE id = 3');
3187 self::assertSame(['x', 'y'], array_keys($result));
3188 self::assertSame('1', $result['x']);
3189 self::assertSame('2', $result['y']);
3190 }
3191
3192 public function testExtractUpdateSetsNewlineInValue(): void
3193 {
3194 $parser = new PgSqlParser();
3195 $result = $parser->extractUpdateSets("UPDATE t SET x = 1\n+ 2 WHERE id = 1");
3196 self::assertSame(['x'], array_keys($result));
3197 }
3198
3199 public function testExtractWhereClauseLowercaseWhere(): void
3200 {
3201 $parser = new PgSqlParser();
3202 $where = $parser->extractWhereClause('delete from t where id = 1');
3203 self::assertSame('id = 1', $where);
3204 }
3205
3206 public function testExtractUpdateFromClauseLowercaseFromAndSet(): void
3207 {
3208 $parser = new PgSqlParser();
3209 $from = $parser->extractUpdateFromClause('update t set name = o.name from orders o where t.id = o.id');
3210 self::assertNotNull($from);
3211 self::assertStringContainsString('orders', $from);
3212 }
3213
3214 public function testExtractDeleteUsingClauseLowercaseUsingAndWhere(): void
3215 {
3216 $parser = new PgSqlParser();
3217 $using = $parser->extractDeleteUsingClause('delete from t using orders where t.id = orders.id');
3218 self::assertNotNull($using);
3219 self::assertStringContainsString('orders', $using);
3220 }
3221
3222 public function testHasCreateTableLikeLowercaseKeywords(): void
3223 {
3224 $parser = new PgSqlParser();
3225 self::assertTrue($parser->hasCreateTableLike('create table t (like other_table)'));
3226 }
3227
3228 public function testHasCreateTableLikeWithNewlineBeforeLike(): void
3229 {
3230 $parser = new PgSqlParser();
3231 self::assertTrue($parser->hasCreateTableLike("CREATE TABLE t (\nLIKE other_table)"));
3232 }
3233
3234 public function testExtractCreateTableSelectSqlLowercaseAs(): void
3235 {
3236 $parser = new PgSqlParser();
3237 $sql = 'CREATE TABLE t as SELECT id FROM users';
3238 $selectSql = $parser->extractCreateTableSelectSql($sql);
3239 self::assertNotNull($selectSql);
3240 self::assertStringStartsWith('SELECT', $selectSql);
3241 }
3242
3243 public function testExtractSelectTableNamesLowercaseJoin(): void
3244 {
3245 $parser = new PgSqlParser();
3246 $tables = $parser->extractSelectTableNames('SELECT * FROM users join orders ON TRUE');
3247 self::assertContains('users', $tables);
3248 self::assertContains('orders', $tables);
3249 }
3250
3251 public function testExtractSelectTableNamesLowercaseAlias(): void
3252 {
3253 $parser = new PgSqlParser();
3254 $tables = $parser->extractSelectTableNames('SELECT * FROM users as u WHERE u.id = 1');
3255 self::assertContains('users', $tables);
3256 }
3257
3258 public function testClassifySimpleStatementWithLtrimNeeded(): void
3259 {
3260 $parser = new PgSqlParser();
3261 self::assertSame('INSERT', $parser->classifyStatement("\t INSERT INTO t VALUES (1)"));
3262 }
3263
3264 public function testSplitByTopLevelCommaTrimsResults(): void
3265 {
3266 $parser = new PgSqlParser();
3267 $result = $parser->extractUpdateSets('UPDATE t SET x = 1 , y = 2 WHERE id = 1');
3268 self::assertSame('1', $result['x']);
3269 self::assertSame('2', $result['y']);
3270 }
3271
3272 public function testSplitByTopLevelCommaTrimsLastItem(): void
3273 {
3274 $parser = new PgSqlParser();
3275 $result = $parser->extractUpdateSets('UPDATE t SET x = 1 WHERE id = 1');
3276 self::assertSame('1', $result['x']);
3277 }
3278
3279 public function testExtractSelectTableNamesWithFromClauseContainingLeftJoin(): void
3280 {
3281 $parser = new PgSqlParser();
3282 $tables = $parser->extractSelectTableNames('SELECT * FROM users LEFT OUTER JOIN orders ON TRUE');
3283 self::assertContains('users', $tables);
3284 self::assertContains('orders', $tables);
3285 }
3286
3287 public function testExtractSelectTableNamesWithLowercaseAs(): void
3288 {
3289 $parser = new PgSqlParser();
3290 $tables = $parser->extractSelectTableNames('SELECT u.id FROM users as u');
3291 self::assertContains('users', $tables);
3292 }
3293
3294 public function testClassifyTruncateContainsDeleteInComment(): void
3295 {
3296 $parser = new PgSqlParser();
3297 self::assertSame('TRUNCATE', $parser->classifyStatement('TRUNCATE TABLE t'));
3298 }
3299
3300 public function testExtractWhereClauseWithStringLiteralContainingWhere(): void
3301 {
3302 $parser = new PgSqlParser();
3303 $where = $parser->extractWhereClause("DELETE FROM t WHERE name = 'WHERE clause'");
3304 self::assertNotNull($where);
3305 self::assertStringContainsString("'WHERE clause'", $where);
3306 }
3307
3308 public function testHasInsertSelectWithLowercaseValues(): void
3309 {
3310 $parser = new PgSqlParser();
3311 self::assertFalse($parser->hasInsertSelect('INSERT INTO t (id) values ((SELECT 1))'));
3312 }
3313
3314 public function testExtractWhereClauseNewlineBeforeReturningKeyword(): void
3315 {
3316 $parser = new PgSqlParser();
3317 $where = $parser->extractWhereClause("UPDATE t SET x = 1 WHERE id = 5\nRETURNING *");
3318 self::assertNotNull($where);
3319 self::assertStringNotContainsString('RETURNING', $where);
3320 self::assertStringContainsString('id = 5', $where);
3321 }
3322
3323 public function testExtractUpdateFromClauseNewlineBeforeWhere(): void
3324 {
3325 $parser = new PgSqlParser();
3326 $from = $parser->extractUpdateFromClause("UPDATE t SET x = s.x FROM src s\nWHERE t.id = s.id");
3327 self::assertNotNull($from);
3328 self::assertStringContainsString('src', $from);
3329 self::assertStringNotContainsString('WHERE', $from);
3330 }
3331
3332 public function testExtractDeleteUsingClauseNewlineBeforeWhere(): void
3333 {
3334 $parser = new PgSqlParser();
3335 $using = $parser->extractDeleteUsingClause("DELETE FROM t USING src\nWHERE t.id = src.id");
3336 self::assertNotNull($using);
3337 self::assertStringContainsString('src', $using);
3338 self::assertStringNotContainsString('WHERE', $using);
3339 }
3340
3341 public function testHasCreateTableLikeWithNewlineBetweenParenAndLike(): void
3342 {
3343 $parser = new PgSqlParser();
3344 self::assertTrue($parser->hasCreateTableLike("CREATE TABLE t (\nLIKE src)"));
3345 }
3346
3347 public function testExtractTableRefsLowercaseAsKeyword(): void
3348 {
3349 $parser = new PgSqlParser();
3350 $tables = $parser->extractSelectTableNames('SELECT u.id FROM users as u');
3351 self::assertContains('users', $tables);
3352 self::assertNotContains('u', $tables);
3353 }
3354
3355 public function testExtractUpdateFromClauseTrimmedResult(): void
3356 {
3357 $parser = new PgSqlParser();
3358 $from = $parser->extractUpdateFromClause('UPDATE t SET x = s.x FROM src s WHERE t.id = s.id');
3359 self::assertNotNull($from);
3360 self::assertSame('src s', $from);
3361 }
3362
3363 public function testExtractDeleteUsingClauseTrimmedOutput(): void
3364 {
3365 $parser = new PgSqlParser();
3366 $using = $parser->extractDeleteUsingClause('DELETE FROM t USING src s WHERE t.id = src.id');
3367 self::assertNotNull($using);
3368 self::assertSame('src s', $using);
3369 }
3370
3371 public function testExtractUpdateSetsValueWithTrailingNewline(): void
3372 {
3373 $parser = new PgSqlParser();
3374 $sets = $parser->extractUpdateSets("UPDATE t SET x = 1\n");
3375 self::assertNotEmpty($sets);
3376 self::assertSame('1', $sets['x']);
3377 }
3378
3379 public function testExtractOnConflictUpdateColumnsValueTrimmed(): void
3380 {
3381 $parser = new PgSqlParser();
3382 $info = $parser->extractOnConflictUpdateColumns("INSERT INTO t (id, name) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name ");
3383 self::assertNotEmpty($info['columns']);
3384 self::assertSame('EXCLUDED.name', $info['values']['name']);
3385 }
3386
3387 public function testClassifyStatementWithInternalWithKeyword(): void
3388 {
3389 $parser = new PgSqlParser();
3390 self::assertSame('SELECT', $parser->classifyStatement("SELECT * FROM t WHERE name = 'WITH'"));
3391 }
3392
3393 public function testClassifyStatementTruncateContainsTclKeywordInBody(): void
3394 {
3395 $parser = new PgSqlParser();
3396 self::assertSame('TRUNCATE', $parser->classifyStatement('TRUNCATE TABLE begin_backup'));
3397 }
3398
3399 public function testStripSchemaPrefixUnquotedWithTrailingDot(): void
3400 {
3401 $parser = new PgSqlParser();
3402 self::assertSame('"users"', $parser->stripSchemaPrefix('public."users"'));
3403 }
3404
3405 public function testExtractCreateTableSelectSqlDollarRemovedMatchesCorrectly(): void
3406 {
3407 $parser = new PgSqlParser();
3408 $selectSql = $parser->extractCreateTableSelectSql('CREATE TABLE t AS SELECT 1 AS id');
3409 self::assertNotNull($selectSql);
3410 self::assertSame('SELECT 1 AS id', $selectSql);
3411 }
3412
3413 public function testUnquoteIdentifierWithOnlyStartingQuote(): void
3414 {
3415 $parser = new PgSqlParser();
3416 self::assertSame('"test', $parser->unquoteIdentifier('"test'));
3417 }
3418
3419 public function testUnquoteIdentifierWithOnlyEndingQuote(): void
3420 {
3421 $parser = new PgSqlParser();
3422 self::assertSame('test"', $parser->unquoteIdentifier('test"'));
3423 }
3424
3425 public function testExtractSelectTableNamesFromWithGreaterThanZero(): void
3426 {
3427 $parser = new PgSqlParser();
3428 $tables = $parser->extractSelectTableNames('SELECT 1');
3429 self::assertSame([], $tables);
3430 }
3431
3432 public function testClassifyWithStatementDepthNeverNegative(): void
3433 {
3434 $parser = new PgSqlParser();
3435 $result = $parser->classifyStatement('WITH cte AS (SELECT (1)) SELECT * FROM cte');
3436 self::assertSame('SELECT', $result);
3437 }
3438
3439 public function testExtractUpdateSetsAssignmentValueTrimmed(): void
3440 {
3441 $parser = new PgSqlParser();
3442 $sets = $parser->extractUpdateSets("UPDATE t SET name = 'Alice' ");
3443 self::assertNotEmpty($sets);
3444 self::assertSame("'Alice'", $sets['name']);
3445 }
3446
3447 public function testStripSchemaPrefixQuotedWithDollarAnchor(): void
3448 {
3449 $parser = new PgSqlParser();
3450 self::assertSame('users', $parser->stripSchemaPrefix('"public".users'));
3451 }
3452
3453 public function testStripSchemaPrefixUnquotedDollarAnchor(): void
3454 {
3455 $parser = new PgSqlParser();
3456 self::assertSame('users', $parser->stripSchemaPrefix('public.users'));
3457 }
3458
3459 public function testExtractOnConflictUpdateColumnsAssignmentPregDollar(): void
3460 {
3461 $parser = new PgSqlParser();
3462 $info = $parser->extractOnConflictUpdateColumns('INSERT INTO t (id, x) VALUES (1, 2) ON CONFLICT (id) DO UPDATE SET x = t.x + 1');
3463 self::assertContains('x', $info['columns']);
3464 self::assertSame('t.x + 1', $info['values']['x']);
3465 }
3466
3467 public function testExtractUpdateSetsPregDollarAnchor(): void
3468 {
3469 $parser = new PgSqlParser();
3470 $sets = $parser->extractUpdateSets('UPDATE t SET x = y + 1');
3471 self::assertNotEmpty($sets);
3472 self::assertSame('y + 1', $sets['x']);
3473 }
3474
3475 public function testSplitStatementsSubtractionNotTreatedAsComment(): void
3476 {
3477 $parser = new PgSqlParser();
3478 $stmts = $parser->splitStatements('SELECT 5-3; SELECT 1');
3479 self::assertCount(2, $stmts);
3480 self::assertSame('SELECT 5-3', $stmts[0]);
3481 self::assertSame('SELECT 1', $stmts[1]);
3482 }
3483
3484 public function testSplitStatementsDivisionNotTreatedAsBlockComment(): void
3485 {
3486 $parser = new PgSqlParser();
3487 $stmts = $parser->splitStatements('SELECT 6/2; SELECT 1');
3488 self::assertCount(2, $stmts);
3489 self::assertSame('SELECT 6/2', $stmts[0]);
3490 self::assertSame('SELECT 1', $stmts[1]);
3491 }
3492
3493 public function testExtractInsertValuesCommaInsideSingleQuote(): void
3494 {
3495 $parser = new PgSqlParser();
3496 $rows = $parser->extractInsertValues("INSERT INTO t (name, age) VALUES ('hello, world', 5)");
3497 self::assertCount(1, $rows);
3498 self::assertCount(2, $rows[0]);
3499 self::assertSame("'hello, world'", $rows[0][0]);
3500 self::assertSame('5', $rows[0][1]);
3501 }
3502
3503 public function testExtractInsertValuesKeepsArrayConstructorTogether(): void
3504 {
3505 $parser = new PgSqlParser();
3506
3507 self::assertSame(
3508 [['1', 'ARRAY[90,85,92]', 'NULL']],
3509 $parser->extractInsertValues('INSERT INTO scores (id, values, payload) VALUES (1, ARRAY[90,85,92], NULL)'),
3510 );
3511 }
3512
3513 public function testHasInsertSelectWithSubqueryInValues(): void
3514 {
3515 $parser = new PgSqlParser();
3516 self::assertFalse($parser->hasInsertSelect('INSERT INTO t (id) VALUES ((SELECT 1))'));
3517 }
3518
3519 public function testExtractUpdateSetsCommaInSingleQuotedValue(): void
3520 {
3521 $parser = new PgSqlParser();
3522 $sets = $parser->extractUpdateSets("UPDATE t SET name = 'a, b', age = 5");
3523 self::assertCount(2, $sets);
3524 self::assertSame("'a, b'", $sets['name']);
3525 self::assertSame('5', $sets['age']);
3526 }
3527
3528 public function testExtractOnConflictUpdateColumnsCommaInValue(): void
3529 {
3530 $parser = new PgSqlParser();
3531 $info = $parser->extractOnConflictUpdateColumns("INSERT INTO t (id, name) VALUES (1, 'x') ON CONFLICT (id) DO UPDATE SET name = 'hello, world'");
3532 self::assertContains('name', $info['columns']);
3533 self::assertSame("'hello, world'", $info['values']['name']);
3534 }
3535
3536 public function testExtractSelectTableNamesUnionMultipleFromClauses(): void
3537 {
3538 $parser = new PgSqlParser();
3539 $tables = $parser->extractSelectTableNames('SELECT * FROM users UNION SELECT * FROM orders');
3540 self::assertContains('users', $tables);
3541 self::assertContains('orders', $tables);
3542 }
3543
3544 public function testExtractSelectTableNamesCommaInDoubleQuotedIdentifier(): void
3545 {
3546 $parser = new PgSqlParser();
3547 $tables = $parser->extractSelectTableNames('SELECT * FROM users, orders');
3548 self::assertContains('users', $tables);
3549 self::assertContains('orders', $tables);
3550 }
3551
3552 public function testSplitByTopLevelCommaDoubleQuotedStringWithComma(): void
3553 {
3554 $parser = new PgSqlParser();
3555 $sets = $parser->extractUpdateSets('UPDATE t SET "a,b" = 1, c = 2');
3556 self::assertCount(2, $sets);
3557 self::assertSame('1', $sets['a,b']);
3558 self::assertSame('2', $sets['c']);
3559 }
3560
3561 public function testExtractOnConflictColumnsCommaInDoubleQuotedCol(): void
3562 {
3563 $parser = new PgSqlParser();
3564 $info = $parser->extractOnConflictUpdateColumns('INSERT INTO t (id, "a,b") VALUES (1, 2) ON CONFLICT (id) DO UPDATE SET "a,b" = EXCLUDED."a,b"');
3565 self::assertContains('a,b', $info['columns']);
3566 }
3567
3568 public function testSplitStatementsAsteriskNotTreatedAsBlockComment(): void
3569 {
3570 $parser = new PgSqlParser();
3571 $stmts = $parser->splitStatements('SELECT *FROM t; SELECT 1');
3572 self::assertCount(2, $stmts);
3573 }
3574
3575 public function testExtractInsertValuesCommaInsideDoubleQuotedDefault(): void
3576 {
3577 $parser = new PgSqlParser();
3578 $rows = $parser->extractInsertValues("INSERT INTO t (a, b) VALUES (1, 'x,y')");
3579 self::assertCount(1, $rows);
3580 self::assertCount(2, $rows[0]);
3581 }
3582
3583 public function testInsertValuesSourceIgnoresQuotedKeywordIdentifiers(): void
3584 {
3585 $parser = new PgSqlParser();
3586 $tableSelect = "INSERT INTO \"select\" (id, val) VALUES (1, 'table-select')";
3587 $tableValues = "INSERT INTO \"values\" (id, val) VALUES (2, 'table-values')";
3588 $columnKeywords = "INSERT INTO test (id, \"select\", \"values\") VALUES (3, 'column-select', 'column-values')";
3589
3590 self::assertFalse($parser->hasInsertSelect($tableSelect));
3591 self::assertNull($parser->extractInsertSelectSql($tableSelect));
3592 self::assertSame([['1', "'table-select'"]], $parser->extractInsertValues($tableSelect));
3593 self::assertFalse($parser->hasInsertSelect($tableValues));
3594 self::assertNull($parser->extractInsertSelectSql($tableValues));
3595 self::assertSame([['2', "'table-values'"]], $parser->extractInsertValues($tableValues));
3596 self::assertFalse($parser->hasInsertSelect($columnKeywords));
3597 self::assertNull($parser->extractInsertSelectSql($columnKeywords));
3598 self::assertSame([['3', "'column-select'", "'column-values'"]], $parser->extractInsertValues($columnKeywords));
3599 }
3600
3601 public function testInsertSelectSourceStartsAfterQuotedKeywordIdentifiers(): void
3602 {
3603 $parser = new PgSqlParser();
3604 $sql = 'INSERT INTO "select" ("select", "values") SELECT 1, 2';
3605
3606 self::assertTrue($parser->hasInsertSelect($sql));
3607 self::assertSame('SELECT 1, 2', $parser->extractInsertSelectSql($sql));
3608 }
3609
3610 public function testInsertSourceRequiresInsertStatement(): void
3611 {
3612 $parser = new PgSqlParser();
3613
3614 self::assertSame([], $parser->extractInsertValues('SELECT VALUES (1)'));
3615 self::assertSame([], $parser->extractInsertValues('VALUES (1)'));
3616 self::assertFalse($parser->hasInsertSelect('VALUES SELECT 1'));
3617 self::assertNull($parser->extractInsertSelectSql('VALUES SELECT 1'));
3618 }
3619
3620 public function testInsertSourceHandlesQuotedIdentifierBoundaries(): void
3621 {
3622 $parser = new PgSqlParser();
3623 $escapedIdentifier = 'INSERT INTO "quoted""select" ("values""column") SELECT 1';
3624 $unterminatedIdentifier = 'INSERT INTO "unterminated SELECT 1';
3625
3626 self::assertTrue($parser->hasInsertSelect($escapedIdentifier));
3627 self::assertSame('SELECT 1', $parser->extractInsertSelectSql($escapedIdentifier));
3628 self::assertFalse($parser->hasInsertSelect($unterminatedIdentifier));
3629 self::assertNull($parser->extractInsertSelectSql($unterminatedIdentifier));
3630 self::assertSame([], $parser->extractInsertValues($unterminatedIdentifier));
3631 self::assertFalse($parser->hasInsertSelect(''));
3632 }
3633
3634 public function testInsertSourceDoesNotUnderflowParenthesisDepth(): void
3635 {
3636 $parser = new PgSqlParser();
3637 $sql = ') INSERT INTO target SELECT 1';
3638
3639 self::assertTrue($parser->hasInsertSelect($sql));
3640 self::assertSame('SELECT 1', $parser->extractInsertSelectSql($sql));
3641 }
3642
3643 public function testInsertSourceIgnoresNestedKeywordBeforeValues(): void
3644 {
3645 $parser = new PgSqlParser();
3646 $sql = 'INSERT INTO target (SELECT ignored) VALUES (1)';
3647
3648 self::assertFalse($parser->hasInsertSelect($sql));
3649 self::assertNull($parser->extractInsertSelectSql($sql));
3650 self::assertSame([['1']], $parser->extractInsertValues($sql));
3651 }
3652
3653 public function testInsertValuesDoesNotUnderflowArrayBracketDepth(): void
3654 {
3655 $parser = new PgSqlParser();
3656
3657 self::assertSame(
3658 [['1]', '2']],
3659 $parser->extractInsertValues('INSERT INTO target (a, b) VALUES (1], 2)'),
3660 );
3661 }
3662
3663 public function testUpdateClausesIgnoreKeywordsInsideExpressionsAndSubqueries(): void
3664 {
3665 $parser = new PgSqlParser();
3666 $sql = "UPDATE products SET name = TRIM(BOTH 'x' FROM name), price = CAST(raw AS NUMERIC(10,2)), maximum = (SELECT MAX(price) FROM products p2 WHERE p2.category_id = products.category_id) FROM categories c WHERE products.category_id = c.id RETURNING *";
3667
3668 self::assertSame([
3669 'name' => "TRIM(BOTH 'x' FROM name)",
3670 'price' => 'CAST(raw AS NUMERIC(10,2))',
3671 'maximum' => '(SELECT MAX(price) FROM products p2 WHERE p2.category_id = products.category_id)',
3672 ], $parser->extractUpdateSets($sql));
3673 self::assertSame('categories c', $parser->extractUpdateFromClause($sql));
3674 self::assertSame('products.category_id = c.id', $parser->extractWhereClause($sql));
3675 }
3676
3677 public function testConflictAssignmentsIgnoreNestedWhereAndReturningKeywords(): void
3678 {
3679 $parser = new PgSqlParser();
3680 $sql = 'INSERT INTO target (id, value) VALUES (1, 2) ON CONFLICT (id) DO UPDATE SET value = (SELECT value FROM source WHERE source.id = excluded.id), meta = jsonb_build_object(\'returning\', excluded.value) WHERE target.active RETURNING *';
3681
3682 self::assertSame([
3683 'columns' => ['value', 'meta'],
3684 'values' => [
3685 'value' => '(SELECT value FROM source WHERE source.id = excluded.id)',
3686 'meta' => "jsonb_build_object('returning', excluded.value)",
3687 ],
3688 ], $parser->extractOnConflictUpdateColumns($sql));
3689 }
3690
3691 public function testSelectTablesIgnoreFromKeywordInsideExtract(): void
3692 {
3693 $parser = new PgSqlParser();
3694 $sql = 'SELECT EXTRACT(YEAR FROM event_date) FROM events WHERE id IN (SELECT event_id FROM archived_events)';
3695
3696 self::assertSame(['events', 'archived_events'], $parser->extractSelectTableNames($sql));
3697 }
3698
3699 public function testExtractOnConflictTargetExtractsPartialConflictTargetStructurally(): void
3700 {
3701 $target = (new PgSqlParser())->extractOnConflictTarget(
3702 "INSERT INTO users (email, status) VALUES ('a@example.com', 'active') "
3703 . "ON CONFLICT (email, tenant_id) WHERE status = 'active' "
3704 . 'DO UPDATE SET status = EXCLUDED.status',
3705 );
3706
3707 self::assertInstanceOf(PgSqlConflictTarget::class, $target);
3708 self::assertTrue($target->specified);
3709 self::assertSame(['email', 'tenant_id'], $target->columns);
3710 self::assertSame("status = 'active'", $target->predicate);
3711 self::assertNull($target->constraint);
3712 }
3713
3714 public function testExtractsNamedAndUnspecifiedConflictTargets(): void
3715 {
3716 $parser = new PgSqlParser();
3717 $named = $parser->extractOnConflictTarget(
3718 'INSERT INTO users VALUES (1) ON CONFLICT ON CONSTRAINT "Users_Email" DO NOTHING',
3719 );
3720 $unspecified = $parser->extractOnConflictTarget(
3721 'INSERT INTO users VALUES (1) ON CONFLICT DO NOTHING',
3722 );
3723
3724 self::assertInstanceOf(PgSqlConflictTarget::class, $named);
3725 self::assertTrue($named->specified);
3726 self::assertSame('Users_Email', $named->constraint);
3727 self::assertInstanceOf(PgSqlConflictTarget::class, $unspecified);
3728 self::assertFalse($unspecified->specified);
3729 self::assertNull($parser->extractOnConflictTarget('INSERT INTO users VALUES (1)'));
3730 }
3731
3732 public function testConflictTargetSkipsNestedAnchorsAndStopsAtTheActionBoundary(): void
3733 {
3734 $target = (new PgSqlParser())->extractOnConflictTarget(
3735 'INSERT INTO users VALUES ((on conflict)) ON CONFLICT (email)'
3736 . " WHERE coalesce((status = 'active'), false)"
3737 . ' DO UPDATE SET do = lower(EXCLUDED.email)',
3738 );
3739
3740 self::assertInstanceOf(PgSqlConflictTarget::class, $target);
3741 self::assertSame(['email'], $target->columns);
3742 self::assertSame("coalesce((status = 'active'), false)", $target->predicate);
3743 }
3744
3745 #[TestWith(['INSERT INTO users VALUES (1) ON'])]
3746 #[TestWith(['INSERT INTO users VALUES (1) ON CONFLICT'])]
3747 #[TestWith(['INSERT INTO users VALUES (1) ON CONFLICT ON'])]
3748 #[TestWith(['INSERT INTO users VALUES (1) ON CONFLICT ON INVALID users_email DO NOTHING'])]
3749 #[TestWith(['INSERT INTO users VALUES (1) ON CONFLICT ON CONSTRAINT'])]
3750 #[TestWith(['INSERT INTO users VALUES (1) ON CONFLICT ON CONSTRAINT 123 DO NOTHING'])]
3751 #[TestWith(['INSERT INTO users VALUES (1) ON CONFLICT email DO NOTHING'])]
3752 #[TestWith(['INSERT INTO users VALUES (1) ON CONFLICT email) DO NOTHING'])]
3753 #[TestWith(['INSERT INTO users VALUES (1) ON CONFLICT [email] DO NOTHING'])]
3754 #[TestWith(['INSERT INTO users VALUES (1) ON CONFLICT (email DO NOTHING'])]
3755 #[TestWith(['INSERT INTO users VALUES (1) ON CONFLICT (email + tenant_id) DO NOTHING'])]
3756 #[TestWith(['INSERT INTO users VALUES (1) ON CONFLICT (email)'])]
3757 #[TestWith(['INSERT INTO users VALUES (1) ON CONFLICT (email) WHERE DO UPDATE SET email = EXCLUDED.email'])]
3758 public function testRejectsMalformedConflictTargets(string $sql): void
3759 {
3760 self::assertNull((new PgSqlParser())->extractOnConflictTarget($sql));
3761 }
3762}
3763