packages/ztd-query-sqlite/tests/Unit/Sql/SqliteParserTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Sql;
6
7use Generator;
8use PHPUnit\Framework\Attributes\CoversClass;
9use PHPUnit\Framework\Attributes\DataProvider;
10use PHPUnit\Framework\Attributes\UsesClass;
11use PHPUnit\Framework\TestCase;
12use ZtdQuery\Platform\Sqlite\Sql\SqliteLexicalMasker;
13use ZtdQuery\Platform\Sqlite\Sql\SqliteParser;
14
15#[CoversClass(SqliteParser::class)]
16#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Expression\AssignmentColumnParser::class)]
17#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Expression\AssignmentParser::class)]
18#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Expression\ValueListParser::class)]
19#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Insert\InsertClauseParser::class)]
20#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\ExpressionSpan::class)]
21#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\IdentifierDecoder::class)]
22#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\LiteralMasker::class)]
23#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\OpaqueSqlSpan::class)]
24#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\QuotedSpan::class)]
25#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\TopLevelKeywordScanner::class)]
26#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Relation\RelationSourceParser::class)]
27#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Statement\StatementClassifier::class)]
28#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Statement\StatementStructure::class)]
29#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Statement\TargetTableParser::class)]
30#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Update\UpdateClauseParser::class)]
31#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\SqliteLexerProfile::class)]
32#[UsesClass(SqliteLexicalMasker::class)]
33#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Relation\SqliteSelectRelationParser::class)]
34final class SqliteParserTest extends TestCase
35{
36 public function testExtractOnConflictUpdateWhereExtractsOnConflictUpdateWhereAfterInsertSelectWhere(): void
37 {
38 $parser = new SqliteParser();
39 $sql = 'INSERT INTO items SELECT * FROM source WHERE active ON CONFLICT (id) DO UPDATE SET score = excluded.score WHERE items.score >= 80 RETURNING id';
40
41 self::assertSame('items.score >= 80', $parser->extractOnConflictUpdateWhere($sql));
42 }
43
44 public function testReturnsNullWithoutOnConflictUpdateWhere(): void
45 {
46 $parser = new SqliteParser();
47
48 self::assertNull($parser->extractOnConflictUpdateWhere(
49 'INSERT INTO items VALUES (1, 90) ON CONFLICT (id) DO UPDATE SET score = excluded.score',
50 ));
51 }
52
53 public function testRequiresCompleteDoUpdateSetAnchor(): void
54 {
55 $parser = new SqliteParser();
56
57 self::assertNull($parser->extractOnConflictUpdateWhere(
58 'INSERT INTO items VALUES (1, 90) ON CONFLICT (id) UPDATE SET score = excluded.score WHERE items.score >= 80',
59 ));
60 }
61
62 public function testClassifySelect(): void
63 {
64 $parser = new SqliteParser();
65 self::assertSame('SELECT', $parser->classifyStatement('SELECT * FROM users'));
66 }
67
68 public function testClassifySelectWithLeadingWhitespace(): void
69 {
70 $parser = new SqliteParser();
71 self::assertSame('SELECT', $parser->classifyStatement(' SELECT * FROM users'));
72 }
73
74 public function testClassifyInsert(): void
75 {
76 $parser = new SqliteParser();
77 self::assertSame('INSERT', $parser->classifyStatement('INSERT INTO users (name) VALUES ("Alice")'));
78 }
79
80 public function testClassifyInsertOrReplace(): void
81 {
82 $parser = new SqliteParser();
83 self::assertSame('INSERT', $parser->classifyStatement('INSERT OR REPLACE INTO users (id, name) VALUES (1, "Alice")'));
84 }
85
86 public function testClassifyReplace(): void
87 {
88 $parser = new SqliteParser();
89 self::assertSame('INSERT', $parser->classifyStatement('REPLACE INTO users (id, name) VALUES (1, "Alice")'));
90 }
91
92 public function testClassifyUpdate(): void
93 {
94 $parser = new SqliteParser();
95 self::assertSame('UPDATE', $parser->classifyStatement('UPDATE users SET name = "Bob" WHERE id = 1'));
96 }
97
98 public function testClassifyDelete(): void
99 {
100 $parser = new SqliteParser();
101 self::assertSame('DELETE', $parser->classifyStatement('DELETE FROM users WHERE id = 1'));
102 }
103
104 public function testClassifyCreateTable(): void
105 {
106 $parser = new SqliteParser();
107 self::assertSame('CREATE_TABLE', $parser->classifyStatement('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)'));
108 }
109
110 public function testClassifyCreateTemporaryTable(): void
111 {
112 $parser = new SqliteParser();
113 self::assertSame('CREATE_TABLE', $parser->classifyStatement('CREATE TEMPORARY TABLE tmp (id INTEGER)'));
114 self::assertSame('CREATE_TABLE', $parser->classifyStatement('CREATE TEMP TABLE tmp (id INTEGER)'));
115 self::assertSame('tmp', $parser->extractTargetTable('CREATE TEMP TABLE tmp (id INTEGER)'));
116 self::assertSame('tmp', $parser->extractTargetTable(' CREATE TEMP TABLE tmp (id INTEGER)'));
117 }
118
119 public function testClassifyDropTable(): void
120 {
121 $parser = new SqliteParser();
122 self::assertSame('DROP_TABLE', $parser->classifyStatement('DROP TABLE users'));
123 }
124
125 public function testClassifyAlterTable(): void
126 {
127 $parser = new SqliteParser();
128 self::assertSame('ALTER_TABLE', $parser->classifyStatement('ALTER TABLE users ADD COLUMN email TEXT'));
129 }
130
131 public function testClassifyUnsupported(): void
132 {
133 $parser = new SqliteParser();
134 self::assertNull($parser->classifyStatement('CREATE INDEX idx ON users (name)'));
135 }
136
137 public function testClassifyEmpty(): void
138 {
139 $parser = new SqliteParser();
140 self::assertNull($parser->classifyStatement(''));
141 }
142
143 public function testClassifyWithCteSelect(): void
144 {
145 $parser = new SqliteParser();
146 self::assertSame('SELECT', $parser->classifyStatement('WITH cte AS (SELECT 1) SELECT * FROM cte'));
147 }
148
149 public function testClassifyWithComment(): void
150 {
151 $parser = new SqliteParser();
152 self::assertSame('SELECT', $parser->classifyStatement('-- comment
153SELECT * FROM users'));
154 }
155
156 public function testSplitSingleStatement(): void
157 {
158 $parser = new SqliteParser();
159 $result = $parser->splitStatements('SELECT * FROM users');
160 self::assertCount(1, $result);
161 self::assertSame('SELECT * FROM users', $result[0]);
162 }
163
164 public function testSplitMultipleStatements(): void
165 {
166 $parser = new SqliteParser();
167 $result = $parser->splitStatements('SELECT 1; SELECT 2');
168 self::assertCount(2, $result);
169 self::assertSame('SELECT 1', $result[0]);
170 self::assertSame('SELECT 2', $result[1]);
171 }
172
173 public function testSplitStatementsIgnoresSemicolonInString(): void
174 {
175 $parser = new SqliteParser();
176 $result = $parser->splitStatements("SELECT 'a;b' FROM t");
177 self::assertCount(1, $result);
178 }
179
180 public function testSplitStatementsIgnoresSemicolonInParentheses(): void
181 {
182 $parser = new SqliteParser();
183 $result = $parser->splitStatements('SELECT (1;2) FROM t');
184 self::assertCount(1, $result);
185 }
186
187 public function testExtractInsertTarget(): void
188 {
189 $parser = new SqliteParser();
190 self::assertSame('users', $parser->extractTargetTable('INSERT INTO users (name) VALUES ("Alice")'));
191 }
192
193 public function testExtractInsertTargetQuoted(): void
194 {
195 $parser = new SqliteParser();
196 self::assertSame('my table', $parser->extractTargetTable('INSERT INTO "my table" (name) VALUES ("Alice")'));
197 }
198
199 public function testExtractUpdateTarget(): void
200 {
201 $parser = new SqliteParser();
202 self::assertSame('users', $parser->extractTargetTable('UPDATE users SET name = "Bob"'));
203 }
204
205 public function testExtractDeleteTarget(): void
206 {
207 $parser = new SqliteParser();
208 self::assertSame('users', $parser->extractTargetTable('DELETE FROM users WHERE id = 1'));
209 }
210
211 public function testExtractUpdateAliasFromStructuredTarget(): void
212 {
213 $parser = new SqliteParser();
214
215 self::assertSame('target', $parser->extractUpdateAlias('UPDATE users AS target SET name = \'Alice\' WHERE target.id = 1'));
216 self::assertSame('target', $parser->extractUpdateAlias('UPDATE users target SET name = \'Alice\' WHERE target.id = 1'));
217 self::assertNull($parser->extractUpdateAlias('UPDATE users SET name = \'Alice\' WHERE id = 1'));
218 }
219
220 public function testExtractUpdateAliasHandlesConflictSchemaAndBracketedTarget(): void
221 {
222 $parser = new SqliteParser();
223
224 self::assertSame(
225 'target',
226 $parser->extractUpdateAlias(
227 'UPDATE OR REPLACE main.[user table] AS target SET name = \'Alice\' WHERE target.id = 1',
228 ),
229 );
230 }
231
232 public function testExtractUpdateAliasHandlesEscapedBracketTargetWithoutAs(): void
233 {
234 $parser = new SqliteParser();
235
236 self::assertSame(
237 'target',
238 $parser->extractUpdateAlias(
239 'UPDATE main.[user]]table] target SET name = \'Alice\' WHERE target.id = 1',
240 ),
241 );
242 self::assertNull(
243 $parser->extractUpdateAlias('UPDATE + ignored ] AS target SET name = \'Alice\''),
244 );
245 }
246
247 public function testExtractUpdateAliasSkipsNestedUpdateKeyword(): void
248 {
249 $parser = new SqliteParser();
250
251 self::assertSame(
252 'target',
253 $parser->extractUpdateAlias(
254 'WITH source AS (SELECT UPDATE FROM audit) UPDATE users AS target SET name = \'Alice\'',
255 ),
256 );
257 }
258
259 public function testExtractUpdateAliasRejectsIncompleteTargets(): void
260 {
261 $parser = new SqliteParser();
262
263 self::assertNull($parser->extractUpdateAlias('UPDATE'));
264 self::assertNull($parser->extractUpdateAlias('UPDATE users'));
265 self::assertNull($parser->extractUpdateAlias('UPDATE [users]'));
266 self::assertNull($parser->extractUpdateAlias('UPDATE users target'));
267 }
268
269 public function testExtractUpdateFromClausePreservesJoinSource(): void
270 {
271 $parser = new SqliteParser();
272
273 self::assertSame(
274 'incoming AS source',
275 $parser->extractUpdateFromClause('UPDATE inventory SET quantity = source.quantity FROM incoming AS source WHERE inventory.id = source.id'),
276 );
277 }
278
279 public function testExtractUpdateFromClauseStopsBeforeOrderedLimit(): void
280 {
281 $parser = new SqliteParser();
282
283 self::assertSame(
284 'incoming AS source',
285 $parser->extractUpdateFromClause(
286 'UPDATE inventory SET quantity = source.quantity FROM incoming AS source ORDER BY source.id LIMIT 1',
287 ),
288 );
289 }
290
291 public function testExtractCreateTableTarget(): void
292 {
293 $parser = new SqliteParser();
294 self::assertSame('users', $parser->extractTargetTable('CREATE TABLE users (id INTEGER)'));
295 }
296
297 public function testExtractDropTableTarget(): void
298 {
299 $parser = new SqliteParser();
300 self::assertSame('users', $parser->extractTargetTable('DROP TABLE users'));
301 }
302
303 public function testExtractAlterTableTarget(): void
304 {
305 $parser = new SqliteParser();
306 self::assertSame('users', $parser->extractTargetTable('ALTER TABLE users ADD COLUMN email TEXT'));
307 }
308
309 public function testExtractSelectTables(): void
310 {
311 $parser = new SqliteParser();
312 $tables = $parser->extractSelectTables('SELECT * FROM users');
313 self::assertSame(['users'], $tables);
314 }
315
316 public function testExtractSelectTablesWithJoin(): void
317 {
318 $parser = new SqliteParser();
319 $tables = $parser->extractSelectTables('SELECT * FROM users JOIN orders ON users.id = orders.user_id');
320 self::assertContains('users', $tables);
321 self::assertContains('orders', $tables);
322 }
323
324 public function testExtractSelectTablesDeduplicatesAndReindexesInFirstSeenOrder(): void
325 {
326 $parser = new SqliteParser();
327
328 self::assertSame(
329 ['users', 'orders'],
330 $parser->extractSelectTables(
331 'SELECT * FROM users UNION SELECT * FROM users UNION SELECT * FROM orders'
332 ),
333 );
334 }
335
336 public function testExtractInsertColumns(): void
337 {
338 $parser = new SqliteParser();
339 $columns = $parser->extractInsertColumns('INSERT INTO users (id, name, email) VALUES (1, "Alice", "a@b.com")');
340 self::assertSame(['id', 'name', 'email'], $columns);
341 }
342
343 public function testExtractInsertColumnsQuoted(): void
344 {
345 $parser = new SqliteParser();
346 $columns = $parser->extractInsertColumns('INSERT INTO users ("id", "name") VALUES (1, "Alice")');
347 self::assertSame(['id', 'name'], $columns);
348 }
349
350 public function testExtractInsertColumnsNoColumns(): void
351 {
352 $parser = new SqliteParser();
353 $columns = $parser->extractInsertColumns('INSERT INTO users VALUES (1, "Alice")');
354 self::assertSame([], $columns);
355 }
356
357 public function testExtractInsertValues(): void
358 {
359 $parser = new SqliteParser();
360 $values = $parser->extractInsertValues("INSERT INTO users (id, name) VALUES (1, 'Alice')");
361 self::assertCount(1, $values);
362 self::assertSame(['1', "'Alice'"], $values[0]);
363 }
364
365 public function testExtractInsertValuesMultipleRows(): void
366 {
367 $parser = new SqliteParser();
368 $values = $parser->extractInsertValues("INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob')");
369 self::assertCount(2, $values);
370 self::assertSame(['1', "'Alice'"], $values[0]);
371 self::assertSame(['2', "'Bob'"], $values[1]);
372 }
373
374 public function testExtractUpdateAssignments(): void
375 {
376 $parser = new SqliteParser();
377 $assignments = $parser->extractUpdateAssignments("UPDATE users SET name = 'Bob', age = 30 WHERE id = 1");
378 self::assertSame("'Bob'", $assignments['name']);
379 self::assertSame('30', $assignments['age']);
380 }
381
382 public function testExtractWhereClause(): void
383 {
384 $parser = new SqliteParser();
385 self::assertSame('id = 1', $parser->extractWhereClause('SELECT * FROM users WHERE id = 1'));
386 }
387
388 public function testExtractWhereClauseWithOrderBy(): void
389 {
390 $parser = new SqliteParser();
391 self::assertSame('id > 0', $parser->extractWhereClause('SELECT * FROM users WHERE id > 0 ORDER BY id'));
392 }
393
394 public function testExtractNoWhereClause(): void
395 {
396 $parser = new SqliteParser();
397 self::assertNull($parser->extractWhereClause('SELECT * FROM users'));
398 }
399
400 public function testHasOnConflict(): void
401 {
402 $parser = new SqliteParser();
403 self::assertTrue($parser->hasOnConflict('INSERT INTO users (id, name) VALUES (1, "Alice") ON CONFLICT (id) DO UPDATE SET name = "Alice"'));
404 self::assertFalse($parser->hasOnConflict('INSERT INTO users (name) VALUES ("Alice")'));
405 }
406
407 public function testIsReplace(): void
408 {
409 $parser = new SqliteParser();
410 self::assertTrue($parser->isReplace('REPLACE INTO users (id, name) VALUES (1, "Alice")'));
411 self::assertTrue($parser->isReplace('INSERT OR REPLACE INTO users (id, name) VALUES (1, "Alice")'));
412 self::assertFalse($parser->isReplace('INSERT INTO users (name) VALUES ("Alice")'));
413 }
414
415 public function testIsInsertIgnore(): void
416 {
417 $parser = new SqliteParser();
418 self::assertTrue($parser->isInsertIgnore('INSERT OR IGNORE INTO users (id, name) VALUES (1, "Alice")'));
419 self::assertFalse($parser->isInsertIgnore('INSERT INTO users (name) VALUES ("Alice")'));
420 }
421
422 public function testExtractOnConflictUpdates(): void
423 {
424 $parser = new SqliteParser();
425 $updates = $parser->extractOnConflictUpdates(
426 'INSERT INTO users (id, name) VALUES (1, "Alice") ON CONFLICT (id) DO UPDATE SET name = excluded.name'
427 );
428 self::assertArrayHasKey('name', $updates);
429 self::assertSame('excluded.name', $updates['name']);
430 }
431
432 public function testExtractOnConflictStopsBeforeReturningWithoutWhere(): void
433 {
434 $parser = new SqliteParser();
435
436 self::assertSame(
437 ['name' => 'excluded.name'],
438 $parser->extractOnConflictUpdates(
439 'INSERT INTO users (id, name) VALUES (1, "Alice") ON CONFLICT (id) DO UPDATE SET name = excluded.name RETURNING id'
440 ),
441 );
442 }
443
444 public function testExtractOnConflictRejectsNonUpdateActionContainingSet(): void
445 {
446 $parser = new SqliteParser();
447
448 self::assertSame(
449 [],
450 $parser->extractOnConflictUpdates(
451 'INSERT INTO users (id, name) VALUES (1, "Alice") ON CONFLICT (id) DO NOTHING SET name = excluded.name'
452 ),
453 );
454 }
455
456 public function testHasInsertSelect(): void
457 {
458 $parser = new SqliteParser();
459 self::assertTrue($parser->hasInsertSelect('INSERT INTO users (id, name) SELECT id, name FROM temp_users'));
460 self::assertFalse($parser->hasInsertSelect("INSERT INTO users (name) VALUES ('Alice')"));
461 }
462
463 public function testExtractInsertSelect(): void
464 {
465 $parser = new SqliteParser();
466 $select = $parser->extractInsertSelect('INSERT INTO users (id, name) SELECT id, name FROM temp_users');
467 self::assertSame('SELECT id, name FROM temp_users', $select);
468 }
469
470 public function testUnquoteDoubleQuoted(): void
471 {
472 $parser = new SqliteParser();
473 self::assertSame('users', $parser->unquoteIdentifier('"users"'));
474 }
475
476 public function testUnquoteBacktickQuoted(): void
477 {
478 $parser = new SqliteParser();
479 self::assertSame('users', $parser->unquoteIdentifier('`users`'));
480 }
481
482 public function testUnquoteBracketQuoted(): void
483 {
484 $parser = new SqliteParser();
485 self::assertSame('users', $parser->unquoteIdentifier('[users]'));
486 }
487
488 public function testUnquoteUnquoted(): void
489 {
490 $parser = new SqliteParser();
491 self::assertSame('users', $parser->unquoteIdentifier('users'));
492 }
493
494 public function testUnquoteEscapedDoubleQuotes(): void
495 {
496 $parser = new SqliteParser();
497 self::assertSame('col"name', $parser->unquoteIdentifier('"col""name"'));
498 }
499
500 public function testStripLineComment(): void
501 {
502 $parser = new SqliteParser();
503 $result = $parser->stripComments("SELECT 1 -- comment\nFROM t");
504 self::assertStringContainsString('SELECT 1', $result);
505 self::assertStringContainsString('FROM t', $result);
506 self::assertStringNotContainsString('comment', $result);
507 }
508
509 public function testStripBlockComment(): void
510 {
511 $parser = new SqliteParser();
512 self::assertSame('SELECT 1 FROM t', $parser->stripComments('SELECT 1 /* comment */ FROM t'));
513 }
514
515 public function testStripBlockCommentPreservesLexicalBoundaryWithoutSurroundingSpaces(): void
516 {
517 $parser = new SqliteParser();
518 self::assertSame('SELECT FROM', $parser->stripComments('SELECT/* comment */FROM'));
519 }
520
521 public function testStripCommentsTrimsOuterWhitespace(): void
522 {
523 $parser = new SqliteParser();
524 self::assertSame('SELECT', $parser->stripComments(" \n/* comment */ SELECT \t"));
525 }
526
527 public function testStripCommentsPreservesQuotedCommentMarkers(): void
528 {
529 $parser = new SqliteParser();
530 $sql = "SELECT '/* value */', \"-- identifier\", `# identifier`, [/* identifier */] FROM users";
531 self::assertSame($sql, $parser->stripComments($sql));
532 }
533
534 public function testExtractSelectTableAfterBlockComment(): void
535 {
536 $parser = new SqliteParser();
537 self::assertSame(['my_table'], $parser->extractSelectTables('SELECT * FROM /* table */ my_table'));
538 }
539
540 public function testExtractSelectTableIgnoresKeywordsInsideLineComment(): void
541 {
542 $parser = new SqliteParser();
543 $sql = "-- SELECT * FROM other_table WHERE DELETE UPDATE INSERT\nSELECT * FROM items ORDER BY id";
544 self::assertSame(['items'], $parser->extractSelectTables($sql));
545 }
546
547 public function testExtractSelectTableIgnoresFromInsideStringLiteral(): void
548 {
549 $parser = new SqliteParser();
550 $sql = "SELECT id, 'from other_table' AS label FROM items LIMIT 1";
551 self::assertSame(['items'], $parser->extractSelectTables($sql));
552 }
553
554 public function testExtractSelectTableIgnoresJoinInsideStringLiteral(): void
555 {
556 $parser = new SqliteParser();
557 $sql = "SELECT id, 'JOIN other_table' AS label FROM items LIMIT 1";
558 self::assertSame(['items'], $parser->extractSelectTables($sql));
559 }
560
561 public function testStringLiteralMaskPreservesOffsetsAndQuotedIdentifiers(): void
562 {
563 $parser = new SqliteParser();
564 $sql = "SELECT 'FROM hidden', \"quoted\", `backtick`, [bracket] FROM items";
565 $masked = $parser->maskStringLiterals($sql);
566 self::assertSame(strlen($sql), strlen($masked));
567 self::assertSame(strpos($sql, 'FROM items'), strpos($masked, 'FROM items'));
568 self::assertStringContainsString('"quoted"', $masked);
569 self::assertStringContainsString('`backtick`', $masked);
570 self::assertStringContainsString('[bracket]', $masked);
571 }
572
573 #[DataProvider('providerStringLiteralMasks')]
574 public function testMaskStringLiteralsUsesSqliteQuoteBoundaries(string $sql, string $expected): void
575 {
576 $parser = new SqliteParser();
577 $masked = $parser->maskStringLiterals($sql);
578 self::assertSame($expected, $masked);
579 self::assertSame(strlen($sql), strlen($masked));
580 }
581
582 /**
583 * @return Generator<string, array{string, string}>
584 */
585 public static function providerStringLiteralMasks(): Generator
586 {
587 yield 'empty query' => ['', ''];
588 yield 'query without strings' => ['SELECT value FROM items', 'SELECT value FROM items'];
589 yield 'empty string' => ["''", ' '];
590 yield 'single quoted string' => ["SELECT 'FROM hidden' FROM items", 'SELECT ' . str_repeat(' ', 13) . ' FROM items'];
591 yield 'doubled single quote' => ["'value''FROM' FROM items", str_repeat(' ', 13) . ' FROM items'];
592 yield 'doubled quote before closing quote' => ["'a''' FROM items", str_repeat(' ', 5) . ' FROM items'];
593 yield 'unterminated string' => ["'FROM hidden", str_repeat(' ', 12)];
594 yield 'multiple strings' => ["'FROM' || 'JOIN' FROM items", str_repeat(' ', 6) . ' || ' . str_repeat(' ', 6) . ' FROM items'];
595 yield 'newline in string' => ["'FROM\nJOIN' FROM items", str_repeat(' ', 11) . ' FROM items'];
596 yield 'double quoted identifier' => ['"FROM" FROM items', '"FROM" FROM items'];
597 yield 'backtick identifier' => ['`FROM` FROM items', '`FROM` FROM items'];
598 yield 'bracket identifier' => ['[FROM] FROM items', '[FROM] FROM items'];
599 yield 'backslash does not escape quote' => ["'closed \\' FROM items", str_repeat(' ', 10) . ' FROM items'];
600 }
601
602 public function testExtractUpdateTargetAcrossBlockComment(): void
603 {
604 $parser = new SqliteParser();
605 self::assertSame('my_table', $parser->extractTargetTable('UPDATE/* table */my_table SET value = 1'));
606 }
607
608 public function testExtractDeleteTargetAcrossBlockComment(): void
609 {
610 $parser = new SqliteParser();
611 self::assertSame('my_table', $parser->extractTargetTable('DELETE FROM/* table */my_table WHERE id = 1'));
612 }
613
614 public function testExtractInsertTargetAcrossBlockComment(): void
615 {
616 $parser = new SqliteParser();
617 self::assertSame('my_table', $parser->extractTargetTable('INSERT INTO/* table */my_table VALUES (1)'));
618 }
619
620 public function testStripHashComment(): void
621 {
622 $parser = new SqliteParser();
623 $result = $parser->stripComments("SELECT 1 # comment\nFROM t");
624 self::assertStringNotContainsString('#', $result);
625 self::assertStringContainsString('SELECT 1', $result);
626 }
627
628 public function testClassifyWithCteInsert(): void
629 {
630 $parser = new SqliteParser();
631 self::assertSame('INSERT', $parser->classifyStatement('WITH cte AS (SELECT 1) INSERT INTO t SELECT * FROM cte'));
632 }
633
634 public function testClassifyWithCteUpdate(): void
635 {
636 $parser = new SqliteParser();
637 self::assertSame('UPDATE', $parser->classifyStatement('WITH cte AS (SELECT 1) UPDATE t SET x = 1'));
638 }
639
640 public function testClassifyWithCteDelete(): void
641 {
642 $parser = new SqliteParser();
643 self::assertSame('DELETE', $parser->classifyStatement('WITH cte AS (SELECT 1) DELETE FROM t WHERE x = 1'));
644 }
645
646 public function testClassifyWithCteReplace(): void
647 {
648 $parser = new SqliteParser();
649 self::assertSame('INSERT', $parser->classifyStatement('WITH cte AS (SELECT 1) REPLACE INTO t (id) VALUES (1)'));
650 }
651
652 public function testClassifyWithCteUnsupported(): void
653 {
654 $parser = new SqliteParser();
655 self::assertNull($parser->classifyStatement('WITH cte AS (SELECT 1) CREATE TABLE t (id INT)'));
656 }
657
658 public function testClassifyWithCteNoBody(): void
659 {
660 $parser = new SqliteParser();
661 self::assertNull($parser->classifyStatement('WITH'));
662 }
663
664 public function testClassifyWithCteQuotedParens(): void
665 {
666 $parser = new SqliteParser();
667 self::assertSame('SELECT', $parser->classifyStatement("WITH cte AS (SELECT '()') SELECT * FROM cte"));
668 }
669
670 public function testClassifyWithReplaceTreatsBlockCommentsAsLexicalSeparators(): void
671 {
672 $parser = new SqliteParser();
673 $sql = '/* lead */WITH/* separator */[select] ([select])/* separator */AS/* separator */(VALUES ("select"))/* separator */REPLACE/* separator */INTO/* separator */`select` DEFAULT/* separator */VALUES';
674 self::assertSame('INSERT', $parser->classifyStatement($sql));
675 }
676
677 public function testClassifyWithUpdateTreatsLineCommentsAsLexicalSeparators(): void
678 {
679 $parser = new SqliteParser();
680 $sql = "WITH-- separator\n\"select\" (`select`) AS (VALUES (name))-- separator\nUPDATE/* separator */name SET name = `select`";
681 self::assertSame('UPDATE', $parser->classifyStatement($sql));
682 }
683
684 public function testClassifyWithDeleteIgnoresQuotedKeywordCteNames(): void
685 {
686 $parser = new SqliteParser();
687 $sql = "WITH name AS (VALUES (name)),-- separator\n[select] AS (VALUES (`select`))-- separator\nDELETE FROM [select]";
688 self::assertSame('DELETE', $parser->classifyStatement($sql));
689 }
690
691 #[DataProvider('providerLexicalClassificationBoundaries')]
692 public function testClassifyStatementUsesLexicalBoundaries(string $sql, ?string $expected): void
693 {
694 $parser = new SqliteParser();
695 self::assertSame($expected, $parser->classifyStatement($sql));
696 }
697
698 /**
699 * @return Generator<string, array{string, ?string}>
700 */
701 public static function providerLexicalClassificationBoundaries(): Generator
702 {
703 yield 'with requires completed group' => ['WITH SELECT', null];
704 yield 'line comment with newline' => ["-- SELECT\nUPDATE name SET value = 1", 'UPDATE'];
705 yield 'line comment with carriage return' => ["-- SELECT\rDELETE FROM name", 'DELETE'];
706 yield 'hash comment' => ["# SELECT\nINSERT INTO name DEFAULT VALUES", 'INSERT'];
707 yield 'line comment at end' => ['-- SELECT', null];
708 yield 'minimal block comment' => ['/**/SELECT 1', 'SELECT'];
709 yield 'overlapping block delimiter is unterminated' => ['/*/SELECT 1', null];
710 yield 'block comment closing boundary' => ['/**/*SELECT 1', 'SELECT'];
711 yield 'unterminated block comment' => ['/* SELECT */ UPDATE /* SELECT', 'UPDATE'];
712 yield 'double quoted keyword' => ['"SELECT" UPDATE name SET value = 1', 'UPDATE'];
713 yield 'backtick quoted keyword' => ['`SELECT` DELETE FROM name', 'DELETE'];
714 yield 'empty single quoted string' => ["''SELECT 1", 'SELECT'];
715 yield 'empty double quoted identifier' => ['""SELECT 1', 'SELECT'];
716 yield 'doubled single quote' => ["'value''SELECT' UPDATE name SET value = 1", 'UPDATE'];
717 yield 'doubled double quote' => ['"value""SELECT" DELETE FROM name', 'DELETE'];
718 yield 'doubled backtick' => ['`value``SELECT` INSERT INTO name DEFAULT VALUES', 'INSERT'];
719 yield 'unterminated single quote' => ["'SELECT UPDATE", null];
720 yield 'unterminated double quote' => ['"SELECT UPDATE', null];
721 yield 'empty bracket identifier' => ['[]SELECT 1', 'SELECT'];
722 yield 'bracket quoted keyword' => ['[SELECT] UPDATE name SET value = 1', 'UPDATE'];
723 yield 'unterminated bracket identifier' => ['[SELECT UPDATE', null];
724 yield 'underscore identifier' => ['_SELECT UPDATE name SET value = 1', 'UPDATE'];
725 yield 'dollar identifier' => ['$SELECT DELETE FROM name', 'DELETE'];
726 yield 'numeric identifier' => ['1SELECT INSERT INTO name DEFAULT VALUES', 'INSERT'];
727 yield 'parenthesized keyword' => ['(SELECT) UPDATE name SET value = 1', 'UPDATE'];
728 yield 'nested parenthesized keyword' => ['((SELECT)) DELETE FROM name', 'DELETE'];
729 yield 'temporary non-table create' => ['CREATE TEMPORARY INDEX name', null];
730 }
731
732 public function testSplitStatementsIgnoresSemicolonInDoubleQuote(): void
733 {
734 $parser = new SqliteParser();
735 $result = $parser->splitStatements('SELECT "a;b" FROM t');
736 self::assertCount(1, $result);
737 }
738
739 public function testSplitStatementsHandlesLineComment(): void
740 {
741 $parser = new SqliteParser();
742 $result = $parser->splitStatements("SELECT 1; -- comment\nSELECT 2");
743 self::assertCount(2, $result);
744 }
745
746 public function testSplitStatementsHandlesBlockComment(): void
747 {
748 $parser = new SqliteParser();
749 $result = $parser->splitStatements('SELECT 1; /* comment */ SELECT 2');
750 self::assertCount(2, $result);
751 }
752
753 public function testSplitStatementsHandlesUnclosedBlockComment(): void
754 {
755 $parser = new SqliteParser();
756 $result = $parser->splitStatements('SELECT 1 /* unclosed comment');
757 self::assertCount(1, $result);
758 }
759
760 public function testSplitStatementsHandlesLineCommentAtEnd(): void
761 {
762 $parser = new SqliteParser();
763 $result = $parser->splitStatements('SELECT 1 -- trailing comment');
764 self::assertCount(1, $result);
765 }
766
767 public function testSplitStatementsHandlesEscapedSingleQuote(): void
768 {
769 $parser = new SqliteParser();
770 $result = $parser->splitStatements("SELECT 'it''s'; SELECT 2");
771 self::assertCount(2, $result);
772 }
773
774 public function testSplitStatementsHandlesEscapedDoubleQuote(): void
775 {
776 $parser = new SqliteParser();
777 $result = $parser->splitStatements('SELECT "col""name"; SELECT 2');
778 self::assertCount(2, $result);
779 }
780
781 public function testSplitStatementsHandlesNestedParens(): void
782 {
783 $parser = new SqliteParser();
784 $result = $parser->splitStatements('SELECT (1 + (2 + 3)); SELECT 2');
785 self::assertCount(2, $result);
786 }
787
788 public function testSplitStatementsEmptyInput(): void
789 {
790 $parser = new SqliteParser();
791 $result = $parser->splitStatements('');
792 self::assertSame([], $result);
793 }
794
795 public function testSplitStatementsTrailingSemicolon(): void
796 {
797 $parser = new SqliteParser();
798 $result = $parser->splitStatements('SELECT 1;');
799 self::assertCount(1, $result);
800 }
801
802 public function testExtractTargetTableForSelect(): void
803 {
804 $parser = new SqliteParser();
805 self::assertNull($parser->extractTargetTable('SELECT * FROM users'));
806 }
807
808 public function testExtractTargetTableForUnsupported(): void
809 {
810 $parser = new SqliteParser();
811 self::assertNull($parser->extractTargetTable('CREATE INDEX idx ON users(name)'));
812 }
813
814 public function testExtractInsertTargetReplaceInto(): void
815 {
816 $parser = new SqliteParser();
817 self::assertSame('users', $parser->extractTargetTable('REPLACE INTO users (id) VALUES (1)'));
818 }
819
820 public function testExtractInsertTargetWithBackticks(): void
821 {
822 $parser = new SqliteParser();
823 self::assertSame('my table', $parser->extractTargetTable('INSERT INTO `my table` (id) VALUES (1)'));
824 }
825
826 public function testExtractInsertTargetWithBrackets(): void
827 {
828 $parser = new SqliteParser();
829 self::assertSame('my table', $parser->extractTargetTable('INSERT INTO [my table] (id) VALUES (1)'));
830 }
831
832 public function testExtractUpdateTargetQuoted(): void
833 {
834 $parser = new SqliteParser();
835 self::assertSame('my table', $parser->extractTargetTable('UPDATE "my table" SET x = 1'));
836 }
837
838 public function testExtractUpdateTargetWithOrReplace(): void
839 {
840 $parser = new SqliteParser();
841 self::assertSame('users', $parser->extractTargetTable('UPDATE OR REPLACE users SET x = 1'));
842 }
843
844 public function testExtractDeleteTargetQuoted(): void
845 {
846 $parser = new SqliteParser();
847 self::assertSame('my table', $parser->extractTargetTable('DELETE FROM "my table" WHERE id = 1'));
848 }
849
850 public function testExtractCreateTableIfNotExists(): void
851 {
852 $parser = new SqliteParser();
853 self::assertSame('users', $parser->extractTargetTable('CREATE TABLE IF NOT EXISTS users (id INT)'));
854 }
855
856 public function testExtractDropTableIfExists(): void
857 {
858 $parser = new SqliteParser();
859 self::assertSame('users', $parser->extractTargetTable('DROP TABLE IF EXISTS users'));
860 }
861
862 public function testExtractSelectTablesWithAlias(): void
863 {
864 $parser = new SqliteParser();
865 $tables = $parser->extractSelectTables('SELECT * FROM users u');
866 self::assertContains('users', $tables);
867 }
868
869 public function testExtractSelectTablesWithAsAlias(): void
870 {
871 $parser = new SqliteParser();
872 $tables = $parser->extractSelectTables('SELECT * FROM users AS u');
873 self::assertContains('users', $tables);
874 }
875
876 public function testExtractSelectTablesMultiple(): void
877 {
878 $parser = new SqliteParser();
879 $tables = $parser->extractSelectTables('SELECT * FROM users, orders');
880 self::assertContains('users', $tables);
881 self::assertContains('orders', $tables);
882 }
883
884 public function testExtractSelectTablesWithWhere(): void
885 {
886 $parser = new SqliteParser();
887 $tables = $parser->extractSelectTables('SELECT * FROM users WHERE id = 1');
888 self::assertSame(['users'], $tables);
889 }
890
891 public function testExtractSelectTablesWithGroupBy(): void
892 {
893 $parser = new SqliteParser();
894 $tables = $parser->extractSelectTables('SELECT * FROM users GROUP BY name');
895 self::assertSame(['users'], $tables);
896 }
897
898 public function testExtractSelectTablesWithOrderBy(): void
899 {
900 $parser = new SqliteParser();
901 $tables = $parser->extractSelectTables('SELECT * FROM users ORDER BY id');
902 self::assertSame(['users'], $tables);
903 }
904
905 public function testExtractSelectTablesWithLimit(): void
906 {
907 $parser = new SqliteParser();
908 $tables = $parser->extractSelectTables('SELECT * FROM users LIMIT 10');
909 self::assertSame(['users'], $tables);
910 }
911
912 public function testExtractSelectTablesQuoted(): void
913 {
914 $parser = new SqliteParser();
915 $tables = $parser->extractSelectTables('SELECT * FROM "users"');
916 self::assertContains('users', $tables);
917 }
918
919 public function testExtractSelectTablesNoFrom(): void
920 {
921 $parser = new SqliteParser();
922 $tables = $parser->extractSelectTables('SELECT 1');
923 self::assertSame([], $tables);
924 }
925
926 public function testExtractInsertValuesNoValues(): void
927 {
928 $parser = new SqliteParser();
929 $values = $parser->extractInsertValues('INSERT INTO users DEFAULT VALUES');
930 self::assertSame([], $values);
931 }
932
933 public function testExtractInsertValuesWithQuotedStrings(): void
934 {
935 $parser = new SqliteParser();
936 $values = $parser->extractInsertValues("INSERT INTO t (a) VALUES ('it''s')");
937 self::assertCount(1, $values);
938 self::assertSame(["'it''s'"], $values[0]);
939 }
940
941 public function testExtractInsertValuesWithNestedParens(): void
942 {
943 $parser = new SqliteParser();
944 $values = $parser->extractInsertValues('INSERT INTO t (a) VALUES ((1 + 2))');
945 self::assertCount(1, $values);
946 self::assertSame(['(1 + 2)'], $values[0]);
947 }
948
949 public function testExtractUpdateAssignmentsQuotedColumn(): void
950 {
951 $parser = new SqliteParser();
952 $assignments = $parser->extractUpdateAssignments('UPDATE users SET "name" = \'Bob\' WHERE id = 1');
953 self::assertArrayHasKey('name', $assignments);
954 self::assertSame("'Bob'", $assignments['name']);
955 }
956
957 public function testExtractUpdateAssignmentsBacktickColumn(): void
958 {
959 $parser = new SqliteParser();
960 $assignments = $parser->extractUpdateAssignments('UPDATE users SET `name` = \'Bob\' WHERE id = 1');
961 self::assertArrayHasKey('name', $assignments);
962 }
963
964 public function testExtractUpdateAssignmentsBracketColumn(): void
965 {
966 $parser = new SqliteParser();
967 $assignments = $parser->extractUpdateAssignments('UPDATE users SET [name] = \'Bob\' WHERE id = 1');
968 self::assertArrayHasKey('name', $assignments);
969 }
970
971 public function testExtractUpdateAssignmentsTableQualified(): void
972 {
973 $parser = new SqliteParser();
974 $assignments = $parser->extractUpdateAssignments('UPDATE users SET users.name = \'Bob\' WHERE id = 1');
975 self::assertArrayHasKey('name', $assignments);
976 }
977
978 public function testExtractUpdateAssignmentsWithFunction(): void
979 {
980 $parser = new SqliteParser();
981 $assignments = $parser->extractUpdateAssignments("UPDATE t SET x = COALESCE(a, 'b') WHERE id = 1");
982 self::assertArrayHasKey('x', $assignments);
983 self::assertStringContainsString('COALESCE', $assignments['x']);
984 }
985
986 public function testExtractUpdateAssignmentsEmpty(): void
987 {
988 $parser = new SqliteParser();
989 $assignments = $parser->extractUpdateAssignments('SELECT * FROM users');
990 self::assertSame([], $assignments);
991 }
992
993 public function testExtractUpdateAssignmentsWithOrderBy(): void
994 {
995 $parser = new SqliteParser();
996 $assignments = $parser->extractUpdateAssignments('UPDATE t SET x = 1 ORDER BY id');
997 self::assertArrayHasKey('x', $assignments);
998 self::assertSame('1', $assignments['x']);
999 }
1000
1001 public function testExtractUpdateAssignmentsWithLimit(): void
1002 {
1003 $parser = new SqliteParser();
1004 $assignments = $parser->extractUpdateAssignments('UPDATE t SET x = 1 LIMIT 5');
1005 self::assertArrayHasKey('x', $assignments);
1006 self::assertSame('1', $assignments['x']);
1007 }
1008
1009 public function testExtractWhereClauseWithGroupBy(): void
1010 {
1011 $parser = new SqliteParser();
1012 self::assertSame('id > 0', $parser->extractWhereClause('SELECT * FROM users WHERE id > 0 GROUP BY name'));
1013 }
1014
1015 public function testExtractWhereClauseWithHaving(): void
1016 {
1017 $parser = new SqliteParser();
1018 self::assertSame('id > 0', $parser->extractWhereClause('SELECT * FROM users WHERE id > 0 HAVING count > 1'));
1019 }
1020
1021 public function testExtractWhereClauseWithLimit(): void
1022 {
1023 $parser = new SqliteParser();
1024 self::assertSame('id > 0', $parser->extractWhereClause('SELECT * FROM users WHERE id > 0 LIMIT 5'));
1025 }
1026
1027 public function testExtractOrderByClause(): void
1028 {
1029 $parser = new SqliteParser();
1030 self::assertSame('id DESC', $parser->extractOrderByClause('SELECT * FROM users ORDER BY id DESC'));
1031 }
1032
1033 public function testExtractOrderByClauseWithLimit(): void
1034 {
1035 $parser = new SqliteParser();
1036 self::assertSame('id', $parser->extractOrderByClause('SELECT * FROM users ORDER BY id LIMIT 5'));
1037 }
1038
1039 public function testExtractOrderByClauseRequiresOrderBeforeBy(): void
1040 {
1041 $parser = new SqliteParser();
1042
1043 self::assertSame(
1044 'value',
1045 $parser->extractOrderByClause('SELECT value AS by FROM users ORDER BY value'),
1046 );
1047 }
1048
1049 public function testExtractOrderByClauseNone(): void
1050 {
1051 $parser = new SqliteParser();
1052 self::assertNull($parser->extractOrderByClause('SELECT * FROM users'));
1053 }
1054
1055 public function testExtractLimitClause(): void
1056 {
1057 $parser = new SqliteParser();
1058 self::assertSame('10', $parser->extractLimitClause('SELECT * FROM users LIMIT 10'));
1059 }
1060
1061 public function testExtractLimitClauseNone(): void
1062 {
1063 $parser = new SqliteParser();
1064 self::assertNull($parser->extractLimitClause('SELECT * FROM users'));
1065 }
1066
1067 public function testExtractOnConflictUpdatesDoNothing(): void
1068 {
1069 $parser = new SqliteParser();
1070 $updates = $parser->extractOnConflictUpdates('INSERT INTO t (id) VALUES (1) ON CONFLICT (id) DO NOTHING');
1071 self::assertSame([], $updates);
1072 }
1073
1074 public function testExtractOnConflictUpdatesNoConflict(): void
1075 {
1076 $parser = new SqliteParser();
1077 $updates = $parser->extractOnConflictUpdates('INSERT INTO t (id) VALUES (1)');
1078 self::assertSame([], $updates);
1079 }
1080
1081 public function testExtractOnConflictUpdatesMultiple(): void
1082 {
1083 $parser = new SqliteParser();
1084 $updates = $parser->extractOnConflictUpdates(
1085 'INSERT INTO t (id, name, age) VALUES (1, \'a\', 2) ON CONFLICT (id) DO UPDATE SET name = excluded.name, age = excluded.age'
1086 );
1087 self::assertArrayHasKey('name', $updates);
1088 self::assertArrayHasKey('age', $updates);
1089 self::assertSame('excluded.name', $updates['name']);
1090 self::assertSame('excluded.age', $updates['age']);
1091 }
1092
1093 public function testHasInsertSelectFalseWithValues(): void
1094 {
1095 $parser = new SqliteParser();
1096 self::assertFalse($parser->hasInsertSelect('INSERT INTO t (id) VALUES (1)'));
1097 }
1098
1099 public function testExtractInsertSelectNull(): void
1100 {
1101 $parser = new SqliteParser();
1102 self::assertNull($parser->extractInsertSelect('INSERT INTO t (id) VALUES (1)'));
1103 }
1104
1105 public function testExtractInsertSelectWithColumns(): void
1106 {
1107 $parser = new SqliteParser();
1108 $select = $parser->extractInsertSelect('INSERT INTO t (id, name) SELECT id, name FROM s');
1109 self::assertSame('SELECT id, name FROM s', $select);
1110 }
1111
1112 public function testUnquoteIdentifierWithLeadingWhitespace(): void
1113 {
1114 $parser = new SqliteParser();
1115 self::assertSame('users', $parser->unquoteIdentifier(' users '));
1116 }
1117
1118 public function testUnquoteIdentifierEscapedBackticks(): void
1119 {
1120 $parser = new SqliteParser();
1121 self::assertSame('col`name', $parser->unquoteIdentifier('`col``name`'));
1122 }
1123
1124 public function testExtractInsertValuesWithQuotedDoubleQuote(): void
1125 {
1126 $parser = new SqliteParser();
1127 $values = $parser->extractInsertValues('INSERT INTO t (a) VALUES ("hello ""world""")');
1128 self::assertCount(1, $values);
1129 }
1130
1131 public function testIsReplaceWithComment(): void
1132 {
1133 $parser = new SqliteParser();
1134 self::assertTrue($parser->isReplace('/* comment */ REPLACE INTO t (id) VALUES (1)'));
1135 }
1136
1137 public function testIsInsertIgnoreWithComment(): void
1138 {
1139 $parser = new SqliteParser();
1140 self::assertTrue($parser->isInsertIgnore('/* comment */ INSERT OR IGNORE INTO t (id) VALUES (1)'));
1141 }
1142
1143 public function testExtractUpdateAssignmentsWithQuotedValue(): void
1144 {
1145 $parser = new SqliteParser();
1146 $assignments = $parser->extractUpdateAssignments("UPDATE t SET name = 'it''s cool' WHERE id = 1");
1147 self::assertArrayHasKey('name', $assignments);
1148 self::assertSame("'it''s cool'", $assignments['name']);
1149 }
1150
1151 public function testExtractSelectTablesWithLeftJoin(): void
1152 {
1153 $parser = new SqliteParser();
1154 $tables = $parser->extractSelectTables('SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id');
1155 self::assertContains('users', $tables);
1156 self::assertContains('orders', $tables);
1157 }
1158
1159 public function testExtractSelectTablesWithInnerJoin(): void
1160 {
1161 $parser = new SqliteParser();
1162 $tables = $parser->extractSelectTables('SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id');
1163 self::assertContains('users', $tables);
1164 self::assertContains('orders', $tables);
1165 }
1166
1167 public function testExtractSelectTablesWithHaving(): void
1168 {
1169 $parser = new SqliteParser();
1170 $tables = $parser->extractSelectTables('SELECT * FROM users HAVING count(*) > 1');
1171 self::assertSame(['users'], $tables);
1172 }
1173
1174 public function testExtractSelectTablesWithUnion(): void
1175 {
1176 $parser = new SqliteParser();
1177 $tables = $parser->extractSelectTables('SELECT * FROM users UNION SELECT * FROM admins');
1178 self::assertContains('users', $tables);
1179 }
1180
1181 public function testSplitStatementsEmpty(): void
1182 {
1183 $parser = new SqliteParser();
1184 $result = $parser->splitStatements(' ; ; ');
1185 self::assertSame([], $result);
1186 }
1187
1188 public function testExtractSelectTablesEmptyExpr(): void
1189 {
1190 $parser = new SqliteParser();
1191 $tables = $parser->extractSelectTables('SELECT * FROM , users');
1192 self::assertContains('users', $tables);
1193 }
1194
1195 public function testExtractInsertValuesMultipleWithWhitespace(): void
1196 {
1197 $parser = new SqliteParser();
1198 $values = $parser->extractInsertValues('INSERT INTO t (a, b) VALUES (1, 2) , (3, 4)');
1199 self::assertCount(2, $values);
1200 self::assertSame(['1', '2'], $values[0]);
1201 self::assertSame(['3', '4'], $values[1]);
1202 }
1203
1204 public function testClassifyWithBlockComment(): void
1205 {
1206 $parser = new SqliteParser();
1207 self::assertSame('SELECT', $parser->classifyStatement('/* comment */ SELECT * FROM users'));
1208 }
1209
1210 public function testClassifyInsertLowercase(): void
1211 {
1212 $parser = new SqliteParser();
1213 self::assertSame('INSERT', $parser->classifyStatement("insert into users (name) values ('Alice')"));
1214 }
1215
1216 public function testClassifyReplaceLowercase(): void
1217 {
1218 $parser = new SqliteParser();
1219 self::assertSame('INSERT', $parser->classifyStatement("replace into users (id, name) values (1, 'Alice')"));
1220 }
1221
1222 public function testClassifyUpdateLowercase(): void
1223 {
1224 $parser = new SqliteParser();
1225 self::assertSame('UPDATE', $parser->classifyStatement("update users set name = 'Bob'"));
1226 }
1227
1228 public function testClassifyDeleteLowercase(): void
1229 {
1230 $parser = new SqliteParser();
1231 self::assertSame('DELETE', $parser->classifyStatement('delete from users where id = 1'));
1232 }
1233
1234 public function testClassifyCreateTableLowercase(): void
1235 {
1236 $parser = new SqliteParser();
1237 self::assertSame('CREATE_TABLE', $parser->classifyStatement('create table users (id integer)'));
1238 }
1239
1240 public function testClassifyDropTableLowercase(): void
1241 {
1242 $parser = new SqliteParser();
1243 self::assertSame('DROP_TABLE', $parser->classifyStatement('drop table users'));
1244 }
1245
1246 public function testClassifyAlterTableLowercase(): void
1247 {
1248 $parser = new SqliteParser();
1249 self::assertSame('ALTER_TABLE', $parser->classifyStatement('alter table users add column email text'));
1250 }
1251
1252 public function testExtractInsertTargetLowercase(): void
1253 {
1254 $parser = new SqliteParser();
1255 self::assertSame('users', $parser->extractTargetTable("insert into users (name) values ('Alice')"));
1256 }
1257
1258 public function testExtractUpdateTargetLowercase(): void
1259 {
1260 $parser = new SqliteParser();
1261 self::assertSame('users', $parser->extractTargetTable("update users set name = 'Bob'"));
1262 }
1263
1264 public function testExtractDeleteTargetLowercase(): void
1265 {
1266 $parser = new SqliteParser();
1267 self::assertSame('users', $parser->extractTargetTable('delete from users where id = 1'));
1268 }
1269
1270 public function testExtractCreateTableNameLowercase(): void
1271 {
1272 $parser = new SqliteParser();
1273 self::assertSame('users', $parser->extractTargetTable('create table users (id integer)'));
1274 }
1275
1276 public function testExtractDropTableNameLowercase(): void
1277 {
1278 $parser = new SqliteParser();
1279 self::assertSame('users', $parser->extractTargetTable('drop table users'));
1280 }
1281
1282 public function testExtractAlterTableNameLowercase(): void
1283 {
1284 $parser = new SqliteParser();
1285 self::assertSame('users', $parser->extractTargetTable('alter table users add column email text'));
1286 }
1287
1288 public function testExtractSelectTablesLowercase(): void
1289 {
1290 $parser = new SqliteParser();
1291 $tables = $parser->extractSelectTables('select * from users where id = 1');
1292 self::assertSame(['users'], $tables);
1293 }
1294
1295 public function testExtractSelectTablesWithJoinLowercase(): void
1296 {
1297 $parser = new SqliteParser();
1298 $tables = $parser->extractSelectTables('select * from users join orders on users.id = orders.user_id');
1299 self::assertContains('users', $tables);
1300 self::assertContains('orders', $tables);
1301 }
1302
1303 public function testExtractInsertColumnsLowercase(): void
1304 {
1305 $parser = new SqliteParser();
1306 $columns = $parser->extractInsertColumns("insert into users (id, name) values (1, 'Alice')");
1307 self::assertSame(['id', 'name'], $columns);
1308 }
1309
1310 public function testExtractInsertValuesLowercase(): void
1311 {
1312 $parser = new SqliteParser();
1313 $values = $parser->extractInsertValues("insert into users (id, name) values (1, 'Alice')");
1314 self::assertCount(1, $values);
1315 }
1316
1317 public function testExtractUpdateAssignmentsLowercase(): void
1318 {
1319 $parser = new SqliteParser();
1320 $assignments = $parser->extractUpdateAssignments("update users set name = 'Bob' where id = 1");
1321 self::assertArrayHasKey('name', $assignments);
1322 }
1323
1324 public function testExtractWhereClauseLowercase(): void
1325 {
1326 $parser = new SqliteParser();
1327 self::assertSame('id = 1', $parser->extractWhereClause('select * from users where id = 1'));
1328 }
1329
1330 public function testExtractOrderByClauseLowercase(): void
1331 {
1332 $parser = new SqliteParser();
1333 self::assertSame('id', $parser->extractOrderByClause('select * from users order by id'));
1334 }
1335
1336 public function testExtractLimitClauseLowercase(): void
1337 {
1338 $parser = new SqliteParser();
1339 self::assertSame('10', $parser->extractLimitClause('select * from users limit 10'));
1340 }
1341
1342 public function testHasOnConflictLowercase(): void
1343 {
1344 $parser = new SqliteParser();
1345 self::assertTrue($parser->hasOnConflict("insert into t (id) values (1) on conflict (id) do update set name = 'x'"));
1346 }
1347
1348 public function testIsReplaceLowercase(): void
1349 {
1350 $parser = new SqliteParser();
1351 self::assertTrue($parser->isReplace('replace into users (id) values (1)'));
1352 }
1353
1354 public function testIsInsertIgnoreLowercase(): void
1355 {
1356 $parser = new SqliteParser();
1357 self::assertTrue($parser->isInsertIgnore('insert or ignore into users (id) values (1)'));
1358 }
1359
1360 public function testExtractOnConflictUpdatesLowercase(): void
1361 {
1362 $parser = new SqliteParser();
1363 $updates = $parser->extractOnConflictUpdates(
1364 "insert into t (id, name) values (1, 'a') on conflict (id) do update set name = excluded.name"
1365 );
1366 self::assertArrayHasKey('name', $updates);
1367 }
1368
1369 public function testHasInsertSelectLowercase(): void
1370 {
1371 $parser = new SqliteParser();
1372 self::assertTrue($parser->hasInsertSelect('insert into t (id) select id from s'));
1373 }
1374
1375 public function testExtractInsertSelectLowercase(): void
1376 {
1377 $parser = new SqliteParser();
1378 $select = $parser->extractInsertSelect('insert into t (id) select id from s');
1379 self::assertNotNull($select);
1380 self::assertStringContainsString('select', $select);
1381 }
1382
1383 public function testExtractUpdateTargetOrRollback(): void
1384 {
1385 $parser = new SqliteParser();
1386 self::assertSame('users', $parser->extractTargetTable('UPDATE OR ROLLBACK users SET x = 1'));
1387 }
1388
1389 public function testExtractUpdateTargetOrAbort(): void
1390 {
1391 $parser = new SqliteParser();
1392 self::assertSame('users', $parser->extractTargetTable('UPDATE OR ABORT users SET x = 1'));
1393 }
1394
1395 public function testExtractUpdateTargetOrFail(): void
1396 {
1397 $parser = new SqliteParser();
1398 self::assertSame('users', $parser->extractTargetTable('UPDATE OR FAIL users SET x = 1'));
1399 }
1400
1401 public function testExtractUpdateTargetOrIgnore(): void
1402 {
1403 $parser = new SqliteParser();
1404 self::assertSame('users', $parser->extractTargetTable('UPDATE OR IGNORE users SET x = 1'));
1405 }
1406
1407 public function testExtractSelectTablesWithGroupByLowercase(): void
1408 {
1409 $parser = new SqliteParser();
1410 $tables = $parser->extractSelectTables('select * from users group by name');
1411 self::assertSame(['users'], $tables);
1412 }
1413
1414 public function testExtractSelectTablesWithOrderByLowercase(): void
1415 {
1416 $parser = new SqliteParser();
1417 $tables = $parser->extractSelectTables('select * from users order by id');
1418 self::assertSame(['users'], $tables);
1419 }
1420
1421 public function testExtractSelectTablesWithLimitLowercase(): void
1422 {
1423 $parser = new SqliteParser();
1424 $tables = $parser->extractSelectTables('select * from users limit 10');
1425 self::assertSame(['users'], $tables);
1426 }
1427
1428 public function testExtractWhereClauseWithOrderByLowercase(): void
1429 {
1430 $parser = new SqliteParser();
1431 self::assertSame('id > 0', $parser->extractWhereClause('select * from users where id > 0 order by id'));
1432 }
1433
1434 public function testExtractWhereClauseWithLimitLowercase(): void
1435 {
1436 $parser = new SqliteParser();
1437 self::assertSame('id > 0', $parser->extractWhereClause('select * from users where id > 0 limit 5'));
1438 }
1439
1440 public function testExtractWhereClauseWithGroupByLowercase(): void
1441 {
1442 $parser = new SqliteParser();
1443 self::assertSame('id > 0', $parser->extractWhereClause('select * from users where id > 0 group by name'));
1444 }
1445
1446 public function testExtractWhereClauseWithHavingLowercase(): void
1447 {
1448 $parser = new SqliteParser();
1449 self::assertSame('id > 0', $parser->extractWhereClause('select * from users where id > 0 having count > 1'));
1450 }
1451
1452 public function testExtractUpdateAssignmentsWithLimitLowercase(): void
1453 {
1454 $parser = new SqliteParser();
1455 $assignments = $parser->extractUpdateAssignments('update t set x = 1 limit 5');
1456 self::assertArrayHasKey('x', $assignments);
1457 }
1458
1459 public function testExtractUpdateAssignmentsWithOrderByLowercase(): void
1460 {
1461 $parser = new SqliteParser();
1462 $assignments = $parser->extractUpdateAssignments('update t set x = 1 order by id');
1463 self::assertArrayHasKey('x', $assignments);
1464 }
1465
1466 public function testExtractOrderByClauseWithLimitLowercase(): void
1467 {
1468 $parser = new SqliteParser();
1469 self::assertSame('id', $parser->extractOrderByClause('select * from users order by id limit 5'));
1470 }
1471
1472 public function testCreateTableIfNotExistsLowercase(): void
1473 {
1474 $parser = new SqliteParser();
1475 self::assertSame('users', $parser->extractTargetTable('create table if not exists users (id integer)'));
1476 }
1477
1478 public function testDropTableIfExistsLowercase(): void
1479 {
1480 $parser = new SqliteParser();
1481 self::assertSame('users', $parser->extractTargetTable('drop table if exists users'));
1482 }
1483
1484 public function testExtractSelectTablesLeftJoinLowercase(): void
1485 {
1486 $parser = new SqliteParser();
1487 $tables = $parser->extractSelectTables('select * from users left join orders on users.id = orders.user_id');
1488 self::assertContains('users', $tables);
1489 self::assertContains('orders', $tables);
1490 }
1491
1492 public function testExtractSelectTablesCrossJoin(): void
1493 {
1494 $parser = new SqliteParser();
1495 $tables = $parser->extractSelectTables('SELECT * FROM users CROSS JOIN orders');
1496 self::assertContains('users', $tables);
1497 }
1498
1499 public function testExtractSelectTablesNaturalJoin(): void
1500 {
1501 $parser = new SqliteParser();
1502 $tables = $parser->extractSelectTables('SELECT * FROM users NATURAL JOIN orders');
1503 self::assertContains('users', $tables);
1504 }
1505
1506 public function testExtractSelectTablesRightJoin(): void
1507 {
1508 $parser = new SqliteParser();
1509 $tables = $parser->extractSelectTables('SELECT * FROM users RIGHT JOIN orders ON 1=1');
1510 self::assertContains('users', $tables);
1511 self::assertContains('orders', $tables);
1512 }
1513
1514 public function testClassifyCreateTemporaryTableLowercase(): void
1515 {
1516 $parser = new SqliteParser();
1517 self::assertSame('CREATE_TABLE', $parser->classifyStatement('create temporary table tmp (id integer)'));
1518 }
1519
1520 public function testExtractCreateTemporaryTableNameLowercase(): void
1521 {
1522 $parser = new SqliteParser();
1523 self::assertSame('tmp', $parser->extractTargetTable('create temporary table tmp (id integer)'));
1524 }
1525
1526 public function testExtractInsertColumnsWithSelectKeyword(): void
1527 {
1528 $parser = new SqliteParser();
1529 $columns = $parser->extractInsertColumns('INSERT INTO t (id, name) SELECT id, name FROM s');
1530 self::assertSame(['id', 'name'], $columns);
1531 }
1532
1533 public function testExtractReplaceTableTarget(): void
1534 {
1535 $parser = new SqliteParser();
1536 self::assertSame('users', $parser->extractTargetTable('REPLACE users (id) VALUES (1)'));
1537 }
1538
1539 public function testStripCommentsPreservesContent(): void
1540 {
1541 $parser = new SqliteParser();
1542 self::assertSame('SELECT 1', $parser->stripComments('SELECT 1'));
1543 }
1544
1545 public function testStripMultipleComments(): void
1546 {
1547 $parser = new SqliteParser();
1548 $result = $parser->stripComments("/* c1 */ SELECT /* c2 */ 1 -- c3\n");
1549 self::assertStringContainsString('SELECT', $result);
1550 self::assertStringContainsString('1', $result);
1551 }
1552
1553 public function testClassifyWithCteDoubleQuoteInBody(): void
1554 {
1555 $parser = new SqliteParser();
1556 self::assertSame('SELECT', $parser->classifyStatement('WITH cte AS (SELECT "col") SELECT * FROM cte'));
1557 }
1558
1559 public function testUnquoteIdentifierSingleCharDoubleQuote(): void
1560 {
1561 $parser = new SqliteParser();
1562 self::assertSame('"', $parser->unquoteIdentifier('"'));
1563 }
1564
1565 public function testUnquoteIdentifierSingleCharBacktick(): void
1566 {
1567 $parser = new SqliteParser();
1568 self::assertSame('`', $parser->unquoteIdentifier('`'));
1569 }
1570
1571 public function testUnquoteIdentifierSingleCharBracket(): void
1572 {
1573 $parser = new SqliteParser();
1574 self::assertSame('[', $parser->unquoteIdentifier('['));
1575 }
1576
1577 public function testUnquoteIdentifierEmptyDoubleQuoted(): void
1578 {
1579 $parser = new SqliteParser();
1580 self::assertSame('', $parser->unquoteIdentifier('""'));
1581 }
1582
1583 public function testUnquoteIdentifierEmptyBacktickQuoted(): void
1584 {
1585 $parser = new SqliteParser();
1586 self::assertSame('', $parser->unquoteIdentifier('``'));
1587 }
1588
1589 public function testUnquoteIdentifierEmptyBracketQuoted(): void
1590 {
1591 $parser = new SqliteParser();
1592 self::assertSame('', $parser->unquoteIdentifier('[]'));
1593 }
1594
1595 public function testUnquoteIdentifierDoubleQuoteWithEscapes(): void
1596 {
1597 $parser = new SqliteParser();
1598 self::assertSame('a"b', $parser->unquoteIdentifier('"a""b"'));
1599 }
1600
1601 public function testUnquoteIdentifierBacktickWithEscapes(): void
1602 {
1603 $parser = new SqliteParser();
1604 self::assertSame('a`b', $parser->unquoteIdentifier('`a``b`'));
1605 }
1606
1607 public function testUnquoteIdentifierBracketContent(): void
1608 {
1609 $parser = new SqliteParser();
1610 self::assertSame('my col', $parser->unquoteIdentifier('[my col]'));
1611 }
1612
1613 public function testUnquoteIdentifierUnquotedReturnsAsIs(): void
1614 {
1615 $parser = new SqliteParser();
1616 self::assertSame('users', $parser->unquoteIdentifier('users'));
1617 }
1618
1619 public function testUnquoteIdentifierWithWhitespace(): void
1620 {
1621 $parser = new SqliteParser();
1622 self::assertSame('users', $parser->unquoteIdentifier(' users '));
1623 }
1624
1625 public function testUnquoteIdentifierMismatchedQuotes(): void
1626 {
1627 $parser = new SqliteParser();
1628 self::assertSame('"abc', $parser->unquoteIdentifier('"abc'));
1629 }
1630
1631 public function testSplitStatementsWithEscapedSingleQuotes(): void
1632 {
1633 $parser = new SqliteParser();
1634 $result = $parser->splitStatements("SELECT 'it''s'; SELECT 2");
1635 self::assertCount(2, $result);
1636 self::assertStringContainsString("it''s", $result[0]);
1637 self::assertSame('SELECT 2', $result[1]);
1638 }
1639
1640 public function testSplitStatementsWithEscapedDoubleQuotes(): void
1641 {
1642 $parser = new SqliteParser();
1643 $result = $parser->splitStatements('SELECT "a""b"; SELECT 2');
1644 self::assertCount(2, $result);
1645 self::assertStringContainsString('a""b', $result[0]);
1646 self::assertSame('SELECT 2', $result[1]);
1647 }
1648
1649 public function testSplitStatementsWithLineComment(): void
1650 {
1651 $parser = new SqliteParser();
1652 $result = $parser->splitStatements("SELECT 1 -- comment\n; SELECT 2");
1653 self::assertCount(2, $result);
1654 }
1655
1656 public function testSplitStatementsWithLineCommentNoNewline(): void
1657 {
1658 $parser = new SqliteParser();
1659 $result = $parser->splitStatements('SELECT 1 -- comment at end');
1660 self::assertCount(1, $result);
1661 self::assertStringContainsString('SELECT 1', $result[0]);
1662 self::assertStringContainsString('-- comment at end', $result[0]);
1663 }
1664
1665 public function testSplitStatementsWithBlockComment(): void
1666 {
1667 $parser = new SqliteParser();
1668 $result = $parser->splitStatements('SELECT /* block */ 1; SELECT 2');
1669 self::assertCount(2, $result);
1670 self::assertStringContainsString('/* block */', $result[0]);
1671 }
1672
1673 public function testSplitStatementsWithUnclosedBlockComment(): void
1674 {
1675 $parser = new SqliteParser();
1676 $result = $parser->splitStatements('SELECT 1 /* unclosed');
1677 self::assertCount(1, $result);
1678 self::assertStringContainsString('/* unclosed', $result[0]);
1679 }
1680
1681 public function testSplitStatementsParenthesesNestedDepth(): void
1682 {
1683 $parser = new SqliteParser();
1684 $result = $parser->splitStatements('SELECT (1, (2, 3)); SELECT 4');
1685 self::assertCount(2, $result);
1686 self::assertStringContainsString('(1, (2, 3))', $result[0]);
1687 }
1688
1689 public function testSplitStatementsClosingParenAtZeroDepth(): void
1690 {
1691 $parser = new SqliteParser();
1692 $result = $parser->splitStatements('SELECT 1) ; SELECT 2');
1693 self::assertCount(2, $result);
1694 }
1695
1696 public function testSplitStatementsEmptyStatements(): void
1697 {
1698 $parser = new SqliteParser();
1699 $result = $parser->splitStatements(';;; ');
1700 self::assertSame([], $result);
1701 }
1702
1703 public function testSplitStatementsSemicolonInsideParens(): void
1704 {
1705 $parser = new SqliteParser();
1706 $result = $parser->splitStatements("SELECT (';')");
1707 self::assertCount(1, $result);
1708 }
1709
1710 public function testParseAssignmentsQuotedColumnName(): void
1711 {
1712 $parser = new SqliteParser();
1713 $result = $parser->extractUpdateAssignments('UPDATE t SET "col name" = 1 WHERE id = 1');
1714 self::assertArrayHasKey('col name', $result);
1715 self::assertSame('1', $result['col name']);
1716 }
1717
1718 public function testParseAssignmentsBacktickColumnName(): void
1719 {
1720 $parser = new SqliteParser();
1721 $result = $parser->extractUpdateAssignments('UPDATE t SET `col` = 1 WHERE id = 2');
1722 self::assertArrayHasKey('col', $result);
1723 self::assertSame('1', $result['col']);
1724 }
1725
1726 public function testParseAssignmentsBracketColumnName(): void
1727 {
1728 $parser = new SqliteParser();
1729 $result = $parser->extractUpdateAssignments('UPDATE t SET [col] = 1 WHERE id = 3');
1730 self::assertArrayHasKey('col', $result);
1731 self::assertSame('1', $result['col']);
1732 }
1733
1734 public function testParseAssignmentsQualifiedColumnName(): void
1735 {
1736 $parser = new SqliteParser();
1737 $result = $parser->extractUpdateAssignments('UPDATE t SET t.name = \'Bob\' WHERE id = 1');
1738 self::assertArrayHasKey('name', $result);
1739 self::assertSame("'Bob'", $result['name']);
1740 }
1741
1742 public function testParseAssignmentsValueWithQuotes(): void
1743 {
1744 $parser = new SqliteParser();
1745 $result = $parser->extractUpdateAssignments("UPDATE t SET a = 'it''s', b = 2 WHERE id = 1");
1746 self::assertSame("'it''s'", $result['a']);
1747 self::assertSame('2', $result['b']);
1748 }
1749
1750 public function testParseAssignmentsValueWithParens(): void
1751 {
1752 $parser = new SqliteParser();
1753 $result = $parser->extractUpdateAssignments('UPDATE t SET a = COALESCE(b, 0) WHERE id = 1');
1754 self::assertSame('COALESCE(b, 0)', $result['a']);
1755 }
1756
1757 public function testParseAssignmentsNoSetClause(): void
1758 {
1759 $parser = new SqliteParser();
1760 $result = $parser->extractUpdateAssignments('UPDATE t WHERE id = 1');
1761 self::assertSame([], $result);
1762 }
1763
1764 public function testParseValueSetsMultipleRows(): void
1765 {
1766 $parser = new SqliteParser();
1767 $result = $parser->extractInsertValues("INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y')");
1768 self::assertCount(2, $result);
1769 self::assertSame(['1', "'x'"], $result[0]);
1770 self::assertSame(['2', "'y'"], $result[1]);
1771 }
1772
1773 public function testParseValueSetsNestedParens(): void
1774 {
1775 $parser = new SqliteParser();
1776 $result = $parser->extractInsertValues('INSERT INTO t (a) VALUES (COALESCE(1, 2))');
1777 self::assertCount(1, $result);
1778 self::assertSame(['COALESCE(1, 2)'], $result[0]);
1779 }
1780
1781 public function testParseValueSetsQuotedStringWithComma(): void
1782 {
1783 $parser = new SqliteParser();
1784 $result = $parser->extractInsertValues("INSERT INTO t (a, b) VALUES ('a,b', 1)");
1785 self::assertCount(1, $result);
1786 self::assertSame(["'a,b'", '1'], $result[0]);
1787 }
1788
1789 public function testParseValueSetsEscapedQuotes(): void
1790 {
1791 $parser = new SqliteParser();
1792 $result = $parser->extractInsertValues("INSERT INTO t (a) VALUES ('it''s')");
1793 self::assertCount(1, $result);
1794 self::assertSame(["'it''s'"], $result[0]);
1795 }
1796
1797 public function testParseValueSetsDoubleQuotedString(): void
1798 {
1799 $parser = new SqliteParser();
1800 $result = $parser->extractInsertValues('INSERT INTO t (a) VALUES ("hello")');
1801 self::assertCount(1, $result);
1802 self::assertSame(['"hello"'], $result[0]);
1803 }
1804
1805 public function testParseValueSetsNoValuesKeyword(): void
1806 {
1807 $parser = new SqliteParser();
1808 $result = $parser->extractInsertValues('INSERT INTO t (a) SELECT 1');
1809 self::assertSame([], $result);
1810 }
1811
1812 public function testParseValueSetsEmptyAfterValues(): void
1813 {
1814 $parser = new SqliteParser();
1815 $result = $parser->extractInsertValues('INSERT INTO t (a) VALUES');
1816 self::assertSame([], $result);
1817 }
1818
1819 public function testClassifyWithCteInsertV2(): void
1820 {
1821 $parser = new SqliteParser();
1822 self::assertSame('INSERT', $parser->classifyStatement('WITH cte AS (SELECT 1) INSERT INTO t (a) SELECT * FROM cte'));
1823 }
1824
1825 public function testClassifyWithCteUpdateV2(): void
1826 {
1827 $parser = new SqliteParser();
1828 self::assertSame('UPDATE', $parser->classifyStatement('WITH cte AS (SELECT 1) UPDATE t SET a = 1'));
1829 }
1830
1831 public function testClassifyWithCteDeleteV2(): void
1832 {
1833 $parser = new SqliteParser();
1834 self::assertSame('DELETE', $parser->classifyStatement('WITH cte AS (SELECT 1) DELETE FROM t'));
1835 }
1836
1837 public function testClassifyWithCteReplaceV2(): void
1838 {
1839 $parser = new SqliteParser();
1840 self::assertSame('INSERT', $parser->classifyStatement('WITH cte AS (SELECT 1) REPLACE INTO t (a) VALUES (1)'));
1841 }
1842
1843 public function testClassifyWithCteUnsupportedV2(): void
1844 {
1845 $parser = new SqliteParser();
1846 self::assertNull($parser->classifyStatement('WITH cte AS (SELECT 1) CREATE TABLE t (a INTEGER)'));
1847 }
1848
1849 public function testClassifyWithCteNoBodyNoClosure(): void
1850 {
1851 $parser = new SqliteParser();
1852 self::assertNull($parser->classifyStatement('WITH'));
1853 }
1854
1855 public function testClassifyWithCteSingleQuoteInBody(): void
1856 {
1857 $parser = new SqliteParser();
1858 self::assertSame('SELECT', $parser->classifyStatement("WITH cte AS (SELECT 'val') SELECT * FROM cte"));
1859 }
1860
1861 public function testClassifyWithCteNestedParens(): void
1862 {
1863 $parser = new SqliteParser();
1864 self::assertSame('SELECT', $parser->classifyStatement('WITH cte AS (SELECT (1 + (2 * 3))) SELECT * FROM cte'));
1865 }
1866
1867 public function testClassifyWithCteEscapedSingleQuote(): void
1868 {
1869 $parser = new SqliteParser();
1870 self::assertSame('SELECT', $parser->classifyStatement("WITH cte AS (SELECT 'it''s') SELECT * FROM cte"));
1871 }
1872
1873 public function testClassifyWithCteEscapedDoubleQuote(): void
1874 {
1875 $parser = new SqliteParser();
1876 self::assertSame('SELECT', $parser->classifyStatement('WITH cte AS (SELECT "a""b") SELECT * FROM cte'));
1877 }
1878
1879 public function testClassifyWithCteClosingParenAtZeroDepth(): void
1880 {
1881 $parser = new SqliteParser();
1882 self::assertSame('SELECT', $parser->classifyStatement('WITH cte AS (SELECT 1)) SELECT * FROM cte'));
1883 }
1884
1885 public function testExtractSelectTablesWithJoinV2(): void
1886 {
1887 $parser = new SqliteParser();
1888 $tables = $parser->extractSelectTables('SELECT * FROM users JOIN orders ON users.id = orders.user_id');
1889 self::assertContains('users', $tables);
1890 self::assertContains('orders', $tables);
1891 }
1892
1893 public function testExtractSelectTablesWithAliasV2(): void
1894 {
1895 $parser = new SqliteParser();
1896 $tables = $parser->extractSelectTables('SELECT * FROM users AS u');
1897 self::assertContains('users', $tables);
1898 }
1899
1900 public function testExtractSelectTablesWithImplicitAlias(): void
1901 {
1902 $parser = new SqliteParser();
1903 $tables = $parser->extractSelectTables('SELECT * FROM users u');
1904 self::assertContains('users', $tables);
1905 }
1906
1907 public function testExtractSelectTablesMultipleV2(): void
1908 {
1909 $parser = new SqliteParser();
1910 $tables = $parser->extractSelectTables('SELECT * FROM users, orders WHERE users.id = orders.user_id');
1911 self::assertContains('users', $tables);
1912 self::assertContains('orders', $tables);
1913 }
1914
1915 public function testExtractSelectTablesEmptyFrom(): void
1916 {
1917 $parser = new SqliteParser();
1918 $tables = $parser->extractSelectTables('SELECT 1');
1919 self::assertSame([], $tables);
1920 }
1921
1922 public function testExtractSelectTablesQuotedTable(): void
1923 {
1924 $parser = new SqliteParser();
1925 $tables = $parser->extractSelectTables('SELECT * FROM "my table"');
1926 self::assertNotEmpty($tables);
1927 }
1928
1929 public function testExtractSelectTablesWithGroupByV2(): void
1930 {
1931 $parser = new SqliteParser();
1932 $tables = $parser->extractSelectTables('SELECT name, COUNT(*) FROM users GROUP BY name');
1933 self::assertContains('users', $tables);
1934 }
1935
1936 public function testExtractSelectTablesWithHavingV2(): void
1937 {
1938 $parser = new SqliteParser();
1939 $tables = $parser->extractSelectTables('SELECT name, COUNT(*) FROM users HAVING COUNT(*) > 1');
1940 self::assertContains('users', $tables);
1941 }
1942
1943 public function testExtractSelectTablesWithOrderByV2(): void
1944 {
1945 $parser = new SqliteParser();
1946 $tables = $parser->extractSelectTables('SELECT * FROM users ORDER BY name');
1947 self::assertContains('users', $tables);
1948 }
1949
1950 public function testExtractSelectTablesWithLimitV2(): void
1951 {
1952 $parser = new SqliteParser();
1953 $tables = $parser->extractSelectTables('SELECT * FROM users LIMIT 10');
1954 self::assertContains('users', $tables);
1955 }
1956
1957 public function testExtractSelectTablesWithUnionV2(): void
1958 {
1959 $parser = new SqliteParser();
1960 $tables = $parser->extractSelectTables('SELECT * FROM users UNION SELECT * FROM admins');
1961 self::assertContains('users', $tables);
1962 }
1963
1964 public function testExtractSelectTablesLeftJoin(): void
1965 {
1966 $parser = new SqliteParser();
1967 $tables = $parser->extractSelectTables('SELECT * FROM users LEFT JOIN orders ON users.id = orders.uid');
1968 self::assertContains('orders', $tables);
1969 }
1970
1971 public function testExtractSelectTablesInnerJoin(): void
1972 {
1973 $parser = new SqliteParser();
1974 $tables = $parser->extractSelectTables('SELECT * FROM users INNER JOIN orders ON 1=1');
1975 self::assertContains('orders', $tables);
1976 }
1977
1978 public function testExtractSelectTablesCrossJoinV2(): void
1979 {
1980 $parser = new SqliteParser();
1981 $tables = $parser->extractSelectTables('SELECT * FROM users CROSS JOIN orders');
1982 self::assertContains('orders', $tables);
1983 }
1984
1985 public function testExtractSelectTablesNaturalJoinV2(): void
1986 {
1987 $parser = new SqliteParser();
1988 $tables = $parser->extractSelectTables('SELECT * FROM users NATURAL JOIN orders');
1989 self::assertContains('orders', $tables);
1990 }
1991
1992 public function testExtractInsertColumnsNoColumnsV2(): void
1993 {
1994 $parser = new SqliteParser();
1995 $result = $parser->extractInsertColumns('INSERT INTO t VALUES (1, 2)');
1996 self::assertSame([], $result);
1997 }
1998
1999 public function testExtractInsertColumnsQuotedV2(): void
2000 {
2001 $parser = new SqliteParser();
2002 $result = $parser->extractInsertColumns('INSERT INTO t ("a", "b") VALUES (1, 2)');
2003 self::assertSame(['a', 'b'], $result);
2004 }
2005
2006 public function testExtractInsertColumnsWithSelect(): void
2007 {
2008 $parser = new SqliteParser();
2009 $result = $parser->extractInsertColumns('INSERT INTO t (a, b) SELECT 1, 2');
2010 self::assertSame(['a', 'b'], $result);
2011 }
2012
2013 public function testExtractWhereClauseWithOrderByV2(): void
2014 {
2015 $parser = new SqliteParser();
2016 $result = $parser->extractWhereClause('DELETE FROM t WHERE a > 1 ORDER BY a');
2017 self::assertSame('a > 1', $result);
2018 }
2019
2020 public function testExtractWhereClauseWithLimitV2(): void
2021 {
2022 $parser = new SqliteParser();
2023 $result = $parser->extractWhereClause('DELETE FROM t WHERE a > 1 LIMIT 5');
2024 self::assertSame('a > 1', $result);
2025 }
2026
2027 public function testExtractWhereClauseWithGroupByV2(): void
2028 {
2029 $parser = new SqliteParser();
2030 $result = $parser->extractWhereClause('SELECT * FROM t WHERE a > 1 GROUP BY a');
2031 self::assertSame('a > 1', $result);
2032 }
2033
2034 public function testExtractWhereClauseWithHavingV2(): void
2035 {
2036 $parser = new SqliteParser();
2037 $result = $parser->extractWhereClause('SELECT * FROM t WHERE a > 1 HAVING COUNT(*) > 0');
2038 self::assertSame('a > 1', $result);
2039 }
2040
2041 public function testExtractWhereClauseNone(): void
2042 {
2043 $parser = new SqliteParser();
2044 self::assertNull($parser->extractWhereClause('SELECT * FROM t'));
2045 }
2046
2047 public function testExtractOrderByClauseNoneV2(): void
2048 {
2049 $parser = new SqliteParser();
2050 self::assertNull($parser->extractOrderByClause('SELECT * FROM t'));
2051 }
2052
2053 public function testExtractOrderByClauseWithLimitV2(): void
2054 {
2055 $parser = new SqliteParser();
2056 self::assertSame('a', $parser->extractOrderByClause('SELECT * FROM t ORDER BY a LIMIT 5'));
2057 }
2058
2059 public function testExtractLimitClauseNoneV2(): void
2060 {
2061 $parser = new SqliteParser();
2062 self::assertNull($parser->extractLimitClause('SELECT * FROM t'));
2063 }
2064
2065 public function testExtractLimitClausePresent(): void
2066 {
2067 $parser = new SqliteParser();
2068 self::assertSame('10', $parser->extractLimitClause('SELECT * FROM t LIMIT 10'));
2069 }
2070
2071 public function testHasOnConflictTrue(): void
2072 {
2073 $parser = new SqliteParser();
2074 self::assertTrue($parser->hasOnConflict('INSERT INTO t (a) VALUES (1) ON CONFLICT (a) DO UPDATE SET a = 2'));
2075 }
2076
2077 public function testHasOnConflictFalse(): void
2078 {
2079 $parser = new SqliteParser();
2080 self::assertFalse($parser->hasOnConflict('INSERT INTO t (a) VALUES (1)'));
2081 }
2082
2083 public function testIsReplaceWithReplace(): void
2084 {
2085 $parser = new SqliteParser();
2086 self::assertTrue($parser->isReplace('REPLACE INTO t (a) VALUES (1)'));
2087 }
2088
2089 public function testIsReplaceWithInsertOrReplace(): void
2090 {
2091 $parser = new SqliteParser();
2092 self::assertTrue($parser->isReplace('INSERT OR REPLACE INTO t (a) VALUES (1)'));
2093 }
2094
2095 public function testIsReplaceWithRegularInsert(): void
2096 {
2097 $parser = new SqliteParser();
2098 self::assertFalse($parser->isReplace('INSERT INTO t (a) VALUES (1)'));
2099 }
2100
2101 public function testIsInsertIgnoreTrue(): void
2102 {
2103 $parser = new SqliteParser();
2104 self::assertTrue($parser->isInsertIgnore('INSERT OR IGNORE INTO t (a) VALUES (1)'));
2105 }
2106
2107 public function testIsInsertIgnoreFalse(): void
2108 {
2109 $parser = new SqliteParser();
2110 self::assertFalse($parser->isInsertIgnore('INSERT INTO t (a) VALUES (1)'));
2111 }
2112
2113 public function testExtractOnConflictUpdatesV2(): void
2114 {
2115 $parser = new SqliteParser();
2116 $result = $parser->extractOnConflictUpdates('INSERT INTO t (a) VALUES (1) ON CONFLICT (a) DO UPDATE SET b = 2, c = 3');
2117 self::assertSame(['b' => '2', 'c' => '3'], $result);
2118 }
2119
2120 public function testExtractOnConflictUpdatesDoNothingV2(): void
2121 {
2122 $parser = new SqliteParser();
2123 $result = $parser->extractOnConflictUpdates('INSERT INTO t (a) VALUES (1) ON CONFLICT (a) DO NOTHING');
2124 self::assertSame([], $result);
2125 }
2126
2127 public function testHasInsertSelectTrue(): void
2128 {
2129 $parser = new SqliteParser();
2130 self::assertTrue($parser->hasInsertSelect('INSERT INTO t (a) SELECT 1'));
2131 }
2132
2133 public function testHasInsertSelectFalse(): void
2134 {
2135 $parser = new SqliteParser();
2136 self::assertFalse($parser->hasInsertSelect('INSERT INTO t (a) VALUES (1)'));
2137 }
2138
2139 public function testExtractInsertSelectPresent(): void
2140 {
2141 $parser = new SqliteParser();
2142 $result = $parser->extractInsertSelect('INSERT INTO t (a) SELECT 1 FROM dual');
2143 self::assertSame('SELECT 1 FROM dual', $result);
2144 }
2145
2146 public function testExtractInsertSelectAbsent(): void
2147 {
2148 $parser = new SqliteParser();
2149 self::assertNull($parser->extractInsertSelect('INSERT INTO t (a) VALUES (1)'));
2150 }
2151
2152 public function testExtractTargetTableSelect(): void
2153 {
2154 $parser = new SqliteParser();
2155 self::assertNull($parser->extractTargetTable('SELECT * FROM users'));
2156 }
2157
2158 public function testExtractTargetTableUnsupported(): void
2159 {
2160 $parser = new SqliteParser();
2161 self::assertNull($parser->extractTargetTable('CREATE INDEX idx ON t (a)'));
2162 }
2163
2164 public function testExtractInsertTableQuoted(): void
2165 {
2166 $parser = new SqliteParser();
2167 self::assertSame('my table', $parser->extractTargetTable('INSERT INTO "my table" (a) VALUES (1)'));
2168 }
2169
2170 public function testExtractUpdateTableWithOrClause(): void
2171 {
2172 $parser = new SqliteParser();
2173 self::assertSame('t', $parser->extractTargetTable('UPDATE OR ROLLBACK t SET a = 1'));
2174 }
2175
2176 public function testExtractUpdateTableWithOrAbort(): void
2177 {
2178 $parser = new SqliteParser();
2179 self::assertSame('t', $parser->extractTargetTable('UPDATE OR ABORT t SET a = 1'));
2180 }
2181
2182 public function testExtractUpdateTableWithOrFail(): void
2183 {
2184 $parser = new SqliteParser();
2185 self::assertSame('t', $parser->extractTargetTable('UPDATE OR FAIL t SET a = 1'));
2186 }
2187
2188 public function testExtractUpdateTableWithOrIgnore(): void
2189 {
2190 $parser = new SqliteParser();
2191 self::assertSame('t', $parser->extractTargetTable('UPDATE OR IGNORE t SET a = 1'));
2192 }
2193
2194 public function testExtractUpdateTableWithOrReplace(): void
2195 {
2196 $parser = new SqliteParser();
2197 self::assertSame('t', $parser->extractTargetTable('UPDATE OR REPLACE t SET a = 1'));
2198 }
2199
2200 public function testExtractDeleteTableQuoted(): void
2201 {
2202 $parser = new SqliteParser();
2203 self::assertSame('my table', $parser->extractTargetTable('DELETE FROM "my table" WHERE id = 1'));
2204 }
2205
2206 public function testExtractCreateTableNameWithIfNotExists(): void
2207 {
2208 $parser = new SqliteParser();
2209 self::assertSame('t', $parser->extractTargetTable('CREATE TABLE IF NOT EXISTS t (a INTEGER)'));
2210 }
2211
2212 public function testExtractDropTableNameWithIfExists(): void
2213 {
2214 $parser = new SqliteParser();
2215 self::assertSame('t', $parser->extractTargetTable('DROP TABLE IF EXISTS t'));
2216 }
2217
2218 public function testExtractAlterTableName(): void
2219 {
2220 $parser = new SqliteParser();
2221 self::assertSame('t', $parser->extractTargetTable('ALTER TABLE t ADD COLUMN a INTEGER'));
2222 }
2223
2224 public function testExtractAlterTableNameQuoted(): void
2225 {
2226 $parser = new SqliteParser();
2227 self::assertSame('my table', $parser->extractTargetTable('ALTER TABLE "my table" ADD COLUMN a INTEGER'));
2228 }
2229
2230 public function testExtractReplaceTableName(): void
2231 {
2232 $parser = new SqliteParser();
2233 self::assertSame('t', $parser->extractTargetTable('REPLACE INTO t (a) VALUES (1)'));
2234 }
2235
2236 public function testExtractReplaceTableNameWithoutInto(): void
2237 {
2238 $parser = new SqliteParser();
2239 self::assertSame('t', $parser->extractTargetTable('REPLACE t (a) VALUES (1)'));
2240 }
2241
2242 public function testSplitStatementsOnlySemicolons(): void
2243 {
2244 $parser = new SqliteParser();
2245 self::assertSame([], $parser->splitStatements(';'));
2246 }
2247
2248 public function testSplitStatementsSingleNoSemicolon(): void
2249 {
2250 $parser = new SqliteParser();
2251 $result = $parser->splitStatements('SELECT 1');
2252 self::assertCount(1, $result);
2253 self::assertSame('SELECT 1', $result[0]);
2254 }
2255
2256 public function testParseAssignmentsMultiple(): void
2257 {
2258 $parser = new SqliteParser();
2259 $result = $parser->extractUpdateAssignments("UPDATE t SET a = 1, b = 'x', c = NULL WHERE id = 1");
2260 self::assertCount(3, $result);
2261 self::assertSame('1', $result['a']);
2262 self::assertSame("'x'", $result['b']);
2263 self::assertSame('NULL', $result['c']);
2264 }
2265
2266 public function testParseAssignmentsValueWithNestedParensAndClose(): void
2267 {
2268 $parser = new SqliteParser();
2269 $result = $parser->extractUpdateAssignments('UPDATE t SET a = (SELECT MAX(b) FROM t2) WHERE id = 1');
2270 self::assertSame('(SELECT MAX(b) FROM t2)', $result['a']);
2271 }
2272
2273 public function testParseAssignmentsDoubleQuotedValueInExpression(): void
2274 {
2275 $parser = new SqliteParser();
2276 $result = $parser->extractUpdateAssignments("UPDATE t SET a = 'x''y' WHERE id = 1");
2277 self::assertSame("'x''y'", $result['a']);
2278 }
2279
2280 public function testExtractTableFromExprEmpty(): void
2281 {
2282 $parser = new SqliteParser();
2283 $tables = $parser->extractSelectTables('SELECT * FROM ');
2284 self::assertSame([], $tables);
2285 }
2286
2287 public function testHasInsertSelectWithColumnsAndSelect(): void
2288 {
2289 $parser = new SqliteParser();
2290 self::assertTrue($parser->hasInsertSelect('INSERT INTO t (a, b) SELECT 1, 2'));
2291 }
2292
2293 public function testExtractInsertSelectWithColumnsV2(): void
2294 {
2295 $parser = new SqliteParser();
2296 $result = $parser->extractInsertSelect('INSERT INTO t (a, b) SELECT 1, 2 FROM dual');
2297 self::assertSame('SELECT 1, 2 FROM dual', $result);
2298 }
2299
2300 public function testExtractSelectTablesWithQuotedJoinTable(): void
2301 {
2302 $parser = new SqliteParser();
2303 $tables = $parser->extractSelectTables('SELECT * FROM t JOIN "other table" ON t.id = "other table".tid');
2304 self::assertContains('other table', $tables);
2305 }
2306
2307 public function testExtractSelectTablesJoinEmptyTable(): void
2308 {
2309 $parser = new SqliteParser();
2310 $tables = $parser->extractSelectTables('SELECT * FROM t');
2311 self::assertContains('t', $tables);
2312 }
2313
2314 public function testParseValueSetsEmptyValueInRow(): void
2315 {
2316 $parser = new SqliteParser();
2317 $result = $parser->extractInsertValues("INSERT INTO t (a, b) VALUES (1, '')");
2318 self::assertCount(1, $result);
2319 self::assertSame(['1', "''"], $result[0]);
2320 }
2321
2322 public function testClassifyWithCteMultipleCtes(): void
2323 {
2324 $parser = new SqliteParser();
2325 self::assertSame('SELECT', $parser->classifyStatement('WITH a AS (SELECT 1), b AS (SELECT 2) SELECT * FROM a, b'));
2326 }
2327
2328 public function testClassifyWithCteKeywordInMiddleOfWord(): void
2329 {
2330 $parser = new SqliteParser();
2331 self::assertSame('SELECT', $parser->classifyStatement('WITH cte AS (SELECT 1) SELECT * FROM cte'));
2332 }
2333
2334 public function testSplitStatementsPreservesDoubleQuoteContent(): void
2335 {
2336 $parser = new SqliteParser();
2337 $result = $parser->splitStatements('SELECT "col;name" FROM t');
2338 self::assertCount(1, $result);
2339 self::assertStringContainsString('"col;name"', $result[0]);
2340 }
2341
2342 public function testSplitStatementsPreservesSingleQuoteContent(): void
2343 {
2344 $parser = new SqliteParser();
2345 $result = $parser->splitStatements("SELECT 'val;ue' FROM t");
2346 self::assertCount(1, $result);
2347 self::assertStringContainsString("'val;ue'", $result[0]);
2348 }
2349
2350 public function testParseAssignmentsValueEndingWithCloseParen(): void
2351 {
2352 $parser = new SqliteParser();
2353 $result = $parser->extractUpdateAssignments('UPDATE t SET a = FUNC(1)');
2354 self::assertSame('FUNC(1)', $result['a']);
2355 }
2356
2357 public function testClassifySelectContainingCreateTableInString(): void
2358 {
2359 $parser = new SqliteParser();
2360 $result = $parser->classifyStatement("SELECT 'CREATE TABLE foo' FROM t");
2361 self::assertSame('SELECT', $result);
2362 }
2363
2364 public function testClassifySelectContainingDropTableInString(): void
2365 {
2366 $parser = new SqliteParser();
2367 $result = $parser->classifyStatement("SELECT 'DROP TABLE foo' FROM t");
2368 self::assertSame('SELECT', $result);
2369 }
2370
2371 public function testClassifySelectContainingAlterTableInString(): void
2372 {
2373 $parser = new SqliteParser();
2374 $result = $parser->classifyStatement("SELECT 'ALTER TABLE foo' FROM t");
2375 self::assertSame('SELECT', $result);
2376 }
2377
2378 public function testClassifyNonKeywordContainingCreateTableReturnsNull(): void
2379 {
2380 $parser = new SqliteParser();
2381 self::assertNull($parser->classifyStatement('PRAGMA CREATE TABLE foo'));
2382 }
2383
2384 public function testClassifyNonKeywordContainingDropTableReturnsNull(): void
2385 {
2386 $parser = new SqliteParser();
2387 self::assertNull($parser->classifyStatement('PRAGMA DROP TABLE foo'));
2388 }
2389
2390 public function testClassifyNonKeywordContainingAlterTableReturnsNull(): void
2391 {
2392 $parser = new SqliteParser();
2393 self::assertNull($parser->classifyStatement('PRAGMA ALTER TABLE foo'));
2394 }
2395
2396 public function testClassifyCreateTableCaseInsensitive(): void
2397 {
2398 $parser = new SqliteParser();
2399 self::assertSame('CREATE_TABLE', $parser->classifyStatement('create table foo (id int)'));
2400 self::assertSame('CREATE_TABLE', $parser->classifyStatement('Create Table foo (id int)'));
2401 }
2402
2403 public function testClassifyDropTableCaseInsensitive(): void
2404 {
2405 $parser = new SqliteParser();
2406 self::assertSame('DROP_TABLE', $parser->classifyStatement('drop table foo'));
2407 self::assertSame('DROP_TABLE', $parser->classifyStatement('Drop Table foo'));
2408 }
2409
2410 public function testClassifyAlterTableCaseInsensitive(): void
2411 {
2412 $parser = new SqliteParser();
2413 self::assertSame('ALTER_TABLE', $parser->classifyStatement('alter table foo add column x int'));
2414 self::assertSame('ALTER_TABLE', $parser->classifyStatement('Alter Table foo add column x int'));
2415 }
2416
2417 public function testIsReplaceCaseInsensitive(): void
2418 {
2419 $parser = new SqliteParser();
2420 self::assertTrue($parser->isReplace('insert or replace into t values (1)'));
2421 self::assertTrue($parser->isReplace('INSERT OR REPLACE INTO t VALUES (1)'));
2422 }
2423
2424 public function testIsReplaceNotMatchedInMiddle(): void
2425 {
2426 $parser = new SqliteParser();
2427 self::assertFalse($parser->isReplace("SELECT 'INSERT OR REPLACE' FROM t"));
2428 }
2429
2430 public function testIsInsertIgnoreCaseInsensitive(): void
2431 {
2432 $parser = new SqliteParser();
2433 self::assertTrue($parser->isInsertIgnore('insert or ignore into t values (1)'));
2434 self::assertTrue($parser->isInsertIgnore('INSERT OR IGNORE INTO t VALUES (1)'));
2435 }
2436
2437 public function testIsInsertIgnoreNotMatchedInMiddle(): void
2438 {
2439 $parser = new SqliteParser();
2440 self::assertFalse($parser->isInsertIgnore("SELECT 'INSERT OR IGNORE' FROM t"));
2441 }
2442
2443 public function testExtractTargetTableReturnsNullForUnclassifiable(): void
2444 {
2445 $parser = new SqliteParser();
2446 self::assertNull($parser->extractTargetTable('PRAGMA table_info(users)'));
2447 }
2448
2449 public function testExtractInsertValuesReturnsEmptyWithoutValuesKeyword(): void
2450 {
2451 $parser = new SqliteParser();
2452 self::assertSame([], $parser->extractInsertValues('INSERT INTO t SELECT * FROM s'));
2453 }
2454
2455 public function testExtractUpdateAssignmentsCaseInsensitive(): void
2456 {
2457 $parser = new SqliteParser();
2458 $result = $parser->extractUpdateAssignments("update t set name = 'bob' where id = 1");
2459 self::assertSame('\'bob\'', $result['name']);
2460 }
2461
2462 public function testExtractWhereClauseCaseInsensitive(): void
2463 {
2464 $parser = new SqliteParser();
2465 $result = $parser->extractWhereClause('select * from t where id = 1 order by id');
2466 self::assertSame('id = 1', $result);
2467 }
2468
2469 public function testExtractOrderByClauseCaseInsensitive(): void
2470 {
2471 $parser = new SqliteParser();
2472 $result = $parser->extractOrderByClause('select * from t order by name limit 10');
2473 self::assertSame('name', $result);
2474 }
2475
2476 public function testExtractLimitClauseCaseInsensitive(): void
2477 {
2478 $parser = new SqliteParser();
2479 $result = $parser->extractLimitClause('select * from t limit 5');
2480 self::assertSame('5', $result);
2481 }
2482
2483 public function testExtractSelectTablesCaseInsensitive(): void
2484 {
2485 $parser = new SqliteParser();
2486 $tables = $parser->extractSelectTables('select * from users where id = 1');
2487 self::assertContains('users', $tables);
2488 }
2489
2490 public function testHasOnConflictCaseInsensitive(): void
2491 {
2492 $parser = new SqliteParser();
2493 self::assertTrue($parser->hasOnConflict('insert into t values (1) on conflict do update set a = 1'));
2494 }
2495
2496 public function testHasInsertSelectCaseInsensitive(): void
2497 {
2498 $parser = new SqliteParser();
2499 self::assertTrue($parser->hasInsertSelect('insert into t (a) select b from s'));
2500 }
2501
2502 public function testExtractInsertSelectCaseInsensitive(): void
2503 {
2504 $parser = new SqliteParser();
2505 $result = $parser->extractInsertSelect('insert into t (a) select b from s');
2506 self::assertNotNull($result);
2507 self::assertStringContainsString('select', $result);
2508 }
2509
2510 public function testExtractInsertSelectReturnsNullWithoutSelect(): void
2511 {
2512 $parser = new SqliteParser();
2513 self::assertNull($parser->extractInsertSelect('INSERT INTO t VALUES (1)'));
2514 }
2515
2516 public function testStripCommentsRemovesBlockComment(): void
2517 {
2518 $parser = new SqliteParser();
2519 $result = $parser->stripComments('SELECT /* comment */ 1');
2520 self::assertStringNotContainsString('/*', $result);
2521 self::assertStringNotContainsString('*/', $result);
2522 }
2523
2524 public function testStripCommentsRemovesLineComment(): void
2525 {
2526 $parser = new SqliteParser();
2527 $result = $parser->stripComments("SELECT 1 -- line comment\nFROM t");
2528 self::assertStringNotContainsString('--', $result);
2529 }
2530
2531 public function testStripCommentsRemovesHashComment(): void
2532 {
2533 $parser = new SqliteParser();
2534 $result = $parser->stripComments("SELECT 1 # hash comment\nFROM t");
2535 self::assertStringNotContainsString('#', $result);
2536 }
2537
2538 public function testUnquoteIdentifierDoubleQuote(): void
2539 {
2540 $parser = new SqliteParser();
2541 self::assertSame('table', $parser->unquoteIdentifier('"table"'));
2542 }
2543
2544 public function testUnquoteIdentifierBacktick(): void
2545 {
2546 $parser = new SqliteParser();
2547 self::assertSame('table', $parser->unquoteIdentifier('`table`'));
2548 }
2549
2550 public function testUnquoteIdentifierBracket(): void
2551 {
2552 $parser = new SqliteParser();
2553 self::assertSame('table', $parser->unquoteIdentifier('[table]'));
2554 }
2555
2556 public function testUnquoteIdentifierUnquoted(): void
2557 {
2558 $parser = new SqliteParser();
2559 self::assertSame('table', $parser->unquoteIdentifier('table'));
2560 }
2561
2562 public function testUnquoteIdentifierSingleChar(): void
2563 {
2564 $parser = new SqliteParser();
2565 self::assertSame('x', $parser->unquoteIdentifier('x'));
2566 }
2567
2568 public function testUnquoteIdentifierEmpty(): void
2569 {
2570 $parser = new SqliteParser();
2571 self::assertSame('', $parser->unquoteIdentifier(''));
2572 }
2573
2574 public function testUnquoteIdentifierEscapedDoubleQuote(): void
2575 {
2576 $parser = new SqliteParser();
2577 self::assertSame('my"table', $parser->unquoteIdentifier('"my""table"'));
2578 }
2579
2580 public function testUnquoteIdentifierEscapedBacktick(): void
2581 {
2582 $parser = new SqliteParser();
2583 self::assertSame('my`table', $parser->unquoteIdentifier('`my``table`'));
2584 }
2585
2586 public function testUnquoteIdentifierOnlyOpenDoubleQuote(): void
2587 {
2588 $parser = new SqliteParser();
2589 self::assertSame('"x', $parser->unquoteIdentifier('"x'));
2590 }
2591
2592 public function testUnquoteIdentifierOnlyOpenBacktick(): void
2593 {
2594 $parser = new SqliteParser();
2595 self::assertSame('`x', $parser->unquoteIdentifier('`x'));
2596 }
2597
2598 public function testUnquoteIdentifierOnlyOpenBracket(): void
2599 {
2600 $parser = new SqliteParser();
2601 self::assertSame('[x', $parser->unquoteIdentifier('[x'));
2602 }
2603
2604 public function testExtractTableFromExprAlias(): void
2605 {
2606 $parser = new SqliteParser();
2607 $tables = $parser->extractSelectTables('SELECT * FROM users AS u');
2608 self::assertContains('users', $tables);
2609 }
2610
2611 public function testExtractTableFromExprSpaceAlias(): void
2612 {
2613 $parser = new SqliteParser();
2614 $tables = $parser->extractSelectTables('SELECT * FROM users u');
2615 self::assertContains('users', $tables);
2616 }
2617
2618 public function testExtractTableFromExprEmptyEntry(): void
2619 {
2620 $parser = new SqliteParser();
2621 $tables = $parser->extractSelectTables('SELECT 1');
2622 self::assertSame([], $tables);
2623 }
2624
2625 public function testSplitStatementsDoubleQuoteEscape(): void
2626 {
2627 $parser = new SqliteParser();
2628 $result = $parser->splitStatements('SELECT "col""name"; SELECT 1');
2629 self::assertCount(2, $result);
2630 }
2631
2632 public function testSplitStatementsSingleQuoteEscape(): void
2633 {
2634 $parser = new SqliteParser();
2635 $result = $parser->splitStatements("SELECT 'it''s'; SELECT 1");
2636 self::assertCount(2, $result);
2637 }
2638
2639 public function testSplitStatementsLineComment(): void
2640 {
2641 $parser = new SqliteParser();
2642 $result = $parser->splitStatements("SELECT 1; -- comment\nSELECT 2");
2643 self::assertCount(2, $result);
2644 }
2645
2646 public function testSplitStatementsBlockComment(): void
2647 {
2648 $parser = new SqliteParser();
2649 $result = $parser->splitStatements('SELECT 1; /* block */ SELECT 2');
2650 self::assertCount(2, $result);
2651 }
2652
2653 public function testSplitStatementsUnclosedBlockComment(): void
2654 {
2655 $parser = new SqliteParser();
2656 $result = $parser->splitStatements('SELECT 1 /* unclosed block');
2657 self::assertCount(1, $result);
2658 }
2659
2660 public function testSplitStatementsUnclosedLineComment(): void
2661 {
2662 $parser = new SqliteParser();
2663 $result = $parser->splitStatements('SELECT 1 -- end');
2664 self::assertCount(1, $result);
2665 }
2666
2667 public function testSplitStatementsParenthesizedSubquery(): void
2668 {
2669 $parser = new SqliteParser();
2670 $result = $parser->splitStatements('SELECT (SELECT 1; SELECT 2)');
2671 self::assertCount(1, $result);
2672 }
2673
2674 public function testParseAssignmentsTablePrefixed(): void
2675 {
2676 $parser = new SqliteParser();
2677 $result = $parser->extractUpdateAssignments('UPDATE t SET t.name = 1');
2678 self::assertSame('1', $result['name']);
2679 }
2680
2681 public function testParseAssignmentsEmptyColumnSkipped(): void
2682 {
2683 $parser = new SqliteParser();
2684 $result = $parser->extractUpdateAssignments('UPDATE t SET = 1');
2685 self::assertSame([], $result);
2686 }
2687
2688 public function testParseAssignmentsQuotedStringValue(): void
2689 {
2690 $parser = new SqliteParser();
2691 $result = $parser->extractUpdateAssignments("UPDATE t SET a = 'it''s', b = 2");
2692 self::assertSame("'it''s'", $result['a']);
2693 self::assertSame('2', $result['b']);
2694 }
2695
2696 public function testParseAssignmentsParenthesizedValue(): void
2697 {
2698 $parser = new SqliteParser();
2699 $result = $parser->extractUpdateAssignments('UPDATE t SET a = (SELECT 1), b = 2');
2700 self::assertSame('(SELECT 1)', $result['a']);
2701 self::assertSame('2', $result['b']);
2702 }
2703
2704 public function testExtractInsertColumnsFromReplace(): void
2705 {
2706 $parser = new SqliteParser();
2707 $result = $parser->extractInsertColumns('REPLACE INTO t (a, b) VALUES (1, 2)');
2708 self::assertSame(['a', 'b'], $result);
2709 }
2710
2711 public function testExtractInsertTableFromReplace(): void
2712 {
2713 $parser = new SqliteParser();
2714 $result = $parser->extractTargetTable('REPLACE INTO t VALUES (1)');
2715 self::assertSame('t', $result);
2716 }
2717
2718 public function testExtractInsertTableFromReplaceNoInto(): void
2719 {
2720 $parser = new SqliteParser();
2721 $result = $parser->extractTargetTable('REPLACE t VALUES (1)');
2722 self::assertSame('t', $result);
2723 }
2724
2725 public function testExtractOnConflictUpdatesCaseInsensitive(): void
2726 {
2727 $parser = new SqliteParser();
2728 $result = $parser->extractOnConflictUpdates('insert into t values (1) on conflict (id) do update set a = excluded.a');
2729 self::assertArrayHasKey('a', $result);
2730 }
2731
2732 public function testClassifyWithSelectCte(): void
2733 {
2734 $parser = new SqliteParser();
2735 self::assertSame('SELECT', $parser->classifyStatement('WITH cte AS (SELECT 1) SELECT * FROM cte'));
2736 }
2737
2738 public function testClassifyWithInsertCte(): void
2739 {
2740 $parser = new SqliteParser();
2741 self::assertSame('INSERT', $parser->classifyStatement('WITH cte AS (SELECT 1) INSERT INTO t SELECT * FROM cte'));
2742 }
2743
2744 public function testClassifyWithUpdateCte(): void
2745 {
2746 $parser = new SqliteParser();
2747 self::assertSame('UPDATE', $parser->classifyStatement('WITH cte AS (SELECT 1) UPDATE t SET a = 1'));
2748 }
2749
2750 public function testClassifyWithDeleteCte(): void
2751 {
2752 $parser = new SqliteParser();
2753 self::assertSame('DELETE', $parser->classifyStatement('WITH cte AS (SELECT 1) DELETE FROM t'));
2754 }
2755
2756 public function testClassifyWithCteQuotedString(): void
2757 {
2758 $parser = new SqliteParser();
2759 $result = $parser->classifyStatement("WITH cte AS (SELECT ')') SELECT * FROM cte");
2760 self::assertSame('SELECT', $result);
2761 }
2762
2763 public function testClassifyWithCteDoubleQuotedIdentifier(): void
2764 {
2765 $parser = new SqliteParser();
2766 $result = $parser->classifyStatement('WITH cte AS (SELECT "col)") SELECT * FROM cte');
2767 self::assertSame('SELECT', $result);
2768 }
2769
2770 public function testParseValueSetsMultiple(): void
2771 {
2772 $parser = new SqliteParser();
2773 $result = $parser->extractInsertValues("INSERT INTO t VALUES (1, 'a'), (2, 'b')");
2774 self::assertCount(2, $result);
2775 }
2776
2777 public function testParseValueSetsWithQuotedParen(): void
2778 {
2779 $parser = new SqliteParser();
2780 $result = $parser->extractInsertValues("INSERT INTO t VALUES ('(1)', 'a')");
2781 self::assertCount(1, $result);
2782 self::assertSame(["'(1)'", "'a'"], $result[0]);
2783 }
2784
2785 public function testExtractSelectTablesFromMultipleTables(): void
2786 {
2787 $parser = new SqliteParser();
2788 $tables = $parser->extractSelectTables('SELECT * FROM users, orders WHERE users.id = orders.user_id');
2789 self::assertContains('users', $tables);
2790 self::assertContains('orders', $tables);
2791 }
2792
2793 public function testExtractSelectTablesWithJoinSkipsJoined(): void
2794 {
2795 $parser = new SqliteParser();
2796 $tables = $parser->extractSelectTables('SELECT * FROM users JOIN orders ON users.id = orders.user_id');
2797 self::assertContains('users', $tables);
2798 }
2799
2800 public function testExtractInsertTableCaseInsensitive(): void
2801 {
2802 $parser = new SqliteParser();
2803 self::assertSame('t', $parser->extractTargetTable('insert into t values (1)'));
2804 }
2805
2806 public function testExtractDeleteTableCaseInsensitive(): void
2807 {
2808 $parser = new SqliteParser();
2809 self::assertSame('t', $parser->extractTargetTable('delete from t where id = 1'));
2810 }
2811
2812 public function testExtractCreateTableName(): void
2813 {
2814 $parser = new SqliteParser();
2815 self::assertSame('users', $parser->extractTargetTable('CREATE TABLE users (id INTEGER)'));
2816 }
2817
2818 public function testExtractDropTableName(): void
2819 {
2820 $parser = new SqliteParser();
2821 self::assertSame('users', $parser->extractTargetTable('DROP TABLE users'));
2822 }
2823
2824 public function testClassifyCommentOnlyReturnsNull(): void
2825 {
2826 $parser = new SqliteParser();
2827 self::assertNull($parser->classifyStatement('-- just a comment'));
2828 }
2829
2830 public function testClassifyBlockCommentOnlyReturnsNull(): void
2831 {
2832 $parser = new SqliteParser();
2833 self::assertNull($parser->classifyStatement('/* block comment only */'));
2834 }
2835
2836 public function testExtractUpdateTableCaseInsensitive(): void
2837 {
2838 $parser = new SqliteParser();
2839 self::assertSame('t', $parser->extractTargetTable('update t set a = 1'));
2840 }
2841
2842 public function testExtractInsertTableWithoutIntoReturnsNull(): void
2843 {
2844 $parser = new SqliteParser();
2845 self::assertNull($parser->extractTargetTable('INSERT (a) VALUES (1)'));
2846 }
2847
2848 public function testParseAssignmentsMultipleCommaValues(): void
2849 {
2850 $parser = new SqliteParser();
2851 $result = $parser->extractUpdateAssignments("UPDATE t SET a = 1, b = 'two', c = 3");
2852 self::assertSame('1', $result['a']);
2853 self::assertSame("'two'", $result['b']);
2854 self::assertSame('3', $result['c']);
2855 }
2856
2857 public function testExtractInsertTableQuotedIdentifier(): void
2858 {
2859 $parser = new SqliteParser();
2860 self::assertSame('my table', $parser->extractTargetTable('INSERT INTO "my table" VALUES (1)'));
2861 }
2862
2863 public function testExtractReplaceTableCaseInsensitive(): void
2864 {
2865 $parser = new SqliteParser();
2866 self::assertSame('t', $parser->extractTargetTable('replace into t values (1)'));
2867 }
2868
2869 public function testExtractSelectTablesReturnsEmptyForSubqueryOnly(): void
2870 {
2871 $parser = new SqliteParser();
2872 $tables = $parser->extractSelectTables('SELECT * FROM (SELECT 1)');
2873 self::assertSame([], $tables);
2874 }
2875
2876 public function testExtractSelectTablesMultiline(): void
2877 {
2878 $parser = new SqliteParser();
2879 $tables = $parser->extractSelectTables("SELECT * FROM users,\norders WHERE id = 1");
2880 self::assertContains('users', $tables);
2881 }
2882
2883 public function testExtractWhereClauseMultiline(): void
2884 {
2885 $parser = new SqliteParser();
2886 $result = $parser->extractWhereClause("SELECT * FROM t WHERE id = 1\nAND name = 'x' ORDER BY id");
2887 self::assertNotNull($result);
2888 self::assertStringContainsString('name', $result);
2889 }
2890
2891 public function testExtractOrderByClauseMultiline(): void
2892 {
2893 $parser = new SqliteParser();
2894 $result = $parser->extractOrderByClause("SELECT * FROM t ORDER BY name,\nid LIMIT 10");
2895 self::assertNotNull($result);
2896 self::assertStringContainsString('id', $result);
2897 }
2898
2899 public function testExtractLimitClauseMultiline(): void
2900 {
2901 $parser = new SqliteParser();
2902 $result = $parser->extractLimitClause("SELECT * FROM t LIMIT 5\nOFFSET 10");
2903 self::assertNotNull($result);
2904 self::assertStringContainsString('5', $result);
2905 }
2906
2907 public function testExtractUpdateAssignmentsMultiline(): void
2908 {
2909 $parser = new SqliteParser();
2910 $result = $parser->extractUpdateAssignments("UPDATE t SET name = 'bob',\nemail = 'x' WHERE id = 1");
2911 self::assertSame("'bob'", $result['name']);
2912 self::assertSame("'x'", $result['email']);
2913 }
2914
2915 public function testExtractOnConflictUpdatesMultiline(): void
2916 {
2917 $parser = new SqliteParser();
2918 $result = $parser->extractOnConflictUpdates("INSERT INTO t VALUES (1) ON CONFLICT (id) DO UPDATE SET name = excluded.name,\nemail = excluded.email");
2919 self::assertArrayHasKey('name', $result);
2920 self::assertArrayHasKey('email', $result);
2921 }
2922
2923 public function testExtractInsertSelectMultiline(): void
2924 {
2925 $parser = new SqliteParser();
2926 $result = $parser->extractInsertSelect("INSERT INTO t (a) SELECT b\nFROM s");
2927 self::assertNotNull($result);
2928 self::assertStringContainsString('SELECT', $result);
2929 }
2930
2931 public function testHasInsertSelectMultiline(): void
2932 {
2933 $parser = new SqliteParser();
2934 self::assertTrue($parser->hasInsertSelect("INSERT INTO t\n(a)\nSELECT b FROM s"));
2935 }
2936
2937 public function testHasOnConflictMultiline(): void
2938 {
2939 $parser = new SqliteParser();
2940 self::assertTrue($parser->hasOnConflict("INSERT INTO t VALUES (1)\nON CONFLICT\n(id) DO UPDATE SET a = 1"));
2941 }
2942
2943 public function testExtractInsertColumnsMultiline(): void
2944 {
2945 $parser = new SqliteParser();
2946 $result = $parser->extractInsertColumns("INSERT INTO t\n(a, b)\nVALUES (1, 2)");
2947 self::assertSame(['a', 'b'], $result);
2948 }
2949
2950 public function testExtractInsertValuesMultiline(): void
2951 {
2952 $parser = new SqliteParser();
2953 $result = $parser->extractInsertValues("INSERT INTO t (a)\nVALUES\n(1)");
2954 self::assertCount(1, $result);
2955 }
2956
2957 public function testIsReplaceCaseInsensitiveOrReplace(): void
2958 {
2959 $parser = new SqliteParser();
2960 self::assertTrue($parser->isReplace('Insert Or Replace INTO t VALUES (1)'));
2961 }
2962
2963 public function testIsInsertIgnoreMixedCase(): void
2964 {
2965 $parser = new SqliteParser();
2966 self::assertTrue($parser->isInsertIgnore('Insert Or Ignore INTO t VALUES (1)'));
2967 }
2968
2969 public function testExtractInsertTableFromReplaceWithoutInto(): void
2970 {
2971 $parser = new SqliteParser();
2972 self::assertSame('t', $parser->extractTargetTable('REPLACE t (a) VALUES (1)'));
2973 }
2974
2975 public function testExtractReplaceCaseInsensitiveWithoutInto(): void
2976 {
2977 $parser = new SqliteParser();
2978 self::assertSame('t', $parser->extractTargetTable('replace t values (1)'));
2979 }
2980
2981 public function testInsertValuesSourceIgnoresQuotedKeywordIdentifiers(): void
2982 {
2983 $parser = new SqliteParser();
2984 $tableSelect = "INSERT INTO \"select\" (id, val) VALUES (1, 'table-select')";
2985 $tableValues = "INSERT INTO \"values\" (id, val) VALUES (2, 'table-values')";
2986 $columnKeywords = "INSERT INTO test (id, \"select\", \"values\") VALUES (3, 'column-select', 'column-values')";
2987
2988 self::assertFalse($parser->hasInsertSelect($tableSelect));
2989 self::assertNull($parser->extractInsertSelect($tableSelect));
2990 self::assertSame([['1', "'table-select'"]], $parser->extractInsertValues($tableSelect));
2991 self::assertFalse($parser->hasInsertSelect($tableValues));
2992 self::assertNull($parser->extractInsertSelect($tableValues));
2993 self::assertSame([['2', "'table-values'"]], $parser->extractInsertValues($tableValues));
2994 self::assertFalse($parser->hasInsertSelect($columnKeywords));
2995 self::assertNull($parser->extractInsertSelect($columnKeywords));
2996 self::assertSame([['3', "'column-select'", "'column-values'"]], $parser->extractInsertValues($columnKeywords));
2997 }
2998
2999 public function testInsertSelectSourceStartsAfterQuotedKeywordIdentifiers(): void
3000 {
3001 $parser = new SqliteParser();
3002 $sql = 'INSERT INTO "select" ("select", "values") SELECT 1, 2';
3003
3004 self::assertTrue($parser->hasInsertSelect($sql));
3005 self::assertSame('SELECT 1, 2', $parser->extractInsertSelect($sql));
3006 }
3007
3008 public function testInsertSourceRequiresInsertOrReplaceStatement(): void
3009 {
3010 $parser = new SqliteParser();
3011
3012 self::assertSame([], $parser->extractInsertValues('SELECT VALUES (1)'));
3013 self::assertSame([], $parser->extractInsertValues('VALUES (1)'));
3014 self::assertFalse($parser->hasInsertSelect('VALUES SELECT 1'));
3015 self::assertNull($parser->extractInsertSelect('VALUES SELECT 1'));
3016 }
3017
3018 public function testReplaceUsesValuesAndSelectSources(): void
3019 {
3020 $parser = new SqliteParser();
3021 $values = 'REPLACE INTO target VALUES (1)';
3022 $select = 'REPLACE INTO target SELECT 1';
3023
3024 self::assertSame([['1']], $parser->extractInsertValues($values));
3025 self::assertFalse($parser->hasInsertSelect($values));
3026 self::assertTrue($parser->hasInsertSelect($select));
3027 self::assertSame('SELECT 1', $parser->extractInsertSelect($select));
3028 }
3029
3030 public function testInsertSelectSourceTrimsTrailingWhitespace(): void
3031 {
3032 $parser = new SqliteParser();
3033
3034 self::assertSame('SELECT 1', $parser->extractInsertSelect("INSERT INTO target SELECT 1 \n\t"));
3035 }
3036
3037 public function testUpdateClausesIgnoreKeywordsInsideExpressionsAndSubqueries(): void
3038 {
3039 $parser = new SqliteParser();
3040 $sql = 'UPDATE products SET 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 ORDER BY products.id LIMIT 1 RETURNING *';
3041
3042 self::assertSame([
3043 'price' => 'CAST(raw AS NUMERIC(10,2))',
3044 'maximum' => '(SELECT MAX(price) FROM products p2 WHERE p2.category_id = products.category_id)',
3045 ], $parser->extractUpdateAssignments($sql));
3046 self::assertSame('products.category_id = c.id', $parser->extractWhereClause($sql));
3047 self::assertSame('products.id', $parser->extractOrderByClause($sql));
3048 self::assertSame('1', $parser->extractLimitClause($sql));
3049 }
3050
3051 public function testConflictAssignmentsIgnoreNestedWhereAndReturningKeywords(): void
3052 {
3053 $parser = new SqliteParser();
3054 $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), note = \'returning where\' WHERE target.active RETURNING *';
3055
3056 self::assertSame([
3057 'value' => '(SELECT value FROM source WHERE source.id = excluded.id)',
3058 'note' => "'returning where'",
3059 ], $parser->extractOnConflictUpdates($sql));
3060 }
3061
3062 public function testSelectTablesComeFromSelectScopesOnly(): void
3063 {
3064 $parser = new SqliteParser();
3065 $sql = 'SELECT printf(\'from %s\', name) FROM events WHERE id IN (SELECT event_id FROM archived_events)';
3066
3067 self::assertSame(['events', 'archived_events'], $parser->extractSelectTables($sql));
3068 }
3069
3070 public function testExtractTargetTableUsesTheTopLevelDmlTailAfterCtesAndComments(): void
3071 {
3072 $parser = new SqliteParser();
3073
3074 self::assertSame(
3075 'insert_target',
3076 $parser->extractTargetTable(
3077 "WITH source AS (SELECT 'INTO decoy') /* boundary */ INSERT/* keyword */INTO insert_target VALUES (1)",
3078 ),
3079 );
3080 self::assertSame(
3081 'replace_target',
3082 $parser->extractTargetTable(
3083 "WITH source AS (SELECT 'INTO decoy') /* boundary */ REPLACE/* keyword */INTO replace_target VALUES (1)",
3084 ),
3085 );
3086 self::assertSame(
3087 'update_target',
3088 $parser->extractTargetTable(
3089 'WITH source AS (SELECT 1 AS UPDATE) /* boundary */ UPDATE/* keyword */update_target SET value = 1',
3090 ),
3091 );
3092 self::assertSame(
3093 'delete_target',
3094 $parser->extractTargetTable(
3095 'WITH source AS (SELECT * FROM decoy) /* boundary */ DELETE/* keyword */FROM delete_target',
3096 ),
3097 );
3098 }
3099}
3100