packages/ztd-query-mysql/tests/Unit/Rewrite/LoadData/MySqlLoadDataProjectorTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Rewrite\LoadData;
6
7use PhpMyAdmin\SqlParser\Statements\LoadStatement;
8use PHPUnit\Framework\Attributes\CoversClass;
9use PHPUnit\Framework\Attributes\TestWith;
10use PHPUnit\Framework\Attributes\UsesClass;
11use PHPUnit\Framework\TestCase;
12use ZtdQuery\Exception\UnsupportedSqlException;
13use ZtdQuery\Platform\MySql\Rewrite\LoadData\MySqlLoadDataProjector;
14use ZtdQuery\Platform\MySql\Schema\MySqlSchemaParser;
15use ZtdQuery\Platform\MySql\Sql\MySqlParser;
16use ZtdQuery\Schema\TableDefinitionRegistry;
17
18#[UsesClass(\ZtdQuery\Platform\MySql\Schema\Partition\MySqlPartitioningParser::class)]
19#[UsesClass(\ZtdQuery\Platform\MySql\Sql\OptionalInsertIntoNormalizer::class)]
20
21
22
23
24
25
26#[UsesClass(\ZtdQuery\Platform\MySql\Schema\DefinitionBuilder::class)]
27#[UsesClass(\ZtdQuery\Platform\MySql\Schema\Key\DefinitionReader::class)]
28#[UsesClass(\ZtdQuery\Platform\MySql\Schema\Key\TokenReader::class)]
29#[UsesClass(\ZtdQuery\Platform\MySql\Schema\Partition\PredicateCompiler::class)]
30#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\CastTypeResolver::class)]
31#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\ScalarExpression::class)]
32#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\StringCoercion::class)]
33#[CoversClass(MySqlLoadDataProjector::class)]
34#[UsesClass(\ZtdQuery\Platform\MySql\Schema\MySqlColumnTypeMapper::class)]
35#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\MySqlCastRenderer::class)]
36#[UsesClass(\ZtdQuery\Platform\MySql\Sql\MySqlIdentifierQuoter::class)]
37#[UsesClass(MySqlParser::class)]
38#[UsesClass(MySqlSchemaParser::class)]
39#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\MySqlValueRenderer::class)]
40#[UsesClass(\ZtdQuery\Platform\MySql\Schema\Key\MySqlForeignKeyDefinitionParser::class)]
41#[UsesClass(\ZtdQuery\Platform\MySql\Sql\MySqlLexerProfile::class)]
42#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\LoadData\ColumnMapping::class)]
43#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\LoadData\InputFile::class)]
44#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\LoadData\InputFormat::class)]
45#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\LoadData\InsertQuery::class)]
46#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\LoadData\RecordParser::class)]
47#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\LoadData\RowProjector::class)]
48final class MySqlLoadDataProjectorTest extends TestCase
49{
50 public function testProjectsDefaultTabSeparatedInputAndNullMarker(): void
51 {
52 $stream = tmpfile();
53 self::assertIsResource($stream);
54 self::assertSame(20, fwrite($stream, "1\tAlice\n2\t\\N\n3\tA\\tB\n"));
55 $metadata = stream_get_meta_data($stream);
56 $path = $metadata['uri'] ?? null;
57 self::assertIsString($path);
58
59 $parser = new MySqlParser();
60 $registry = new TableDefinitionRegistry();
61 $definition = (new MySqlSchemaParser($parser))->parse(
62 'CREATE TABLE items (id INT PRIMARY KEY, name VARCHAR(50), slug VARCHAR(50) AS (LOWER(name)))',
63 );
64 self::assertNotNull($definition);
65 $registry->register('items', $definition);
66 $sql = "LOAD DATA LOCAL INFILE '" . str_replace("'", "''", $path) . "' INTO TABLE items IGNORE 0 LINES";
67 $statement = $parser->parseSingleLogicalStatement($sql);
68 self::assertInstanceOf(LoadStatement::class, $statement);
69
70 $projected = (new MySqlLoadDataProjector($registry))->project($sql, $statement);
71
72 self::assertStringStartsWith('INSERT IGNORE INTO `items` (`id`, `name`) VALUES ', $projected);
73 self::assertStringContainsString("CAST('Alice' AS CHAR)", $projected);
74 self::assertStringContainsString('NULL', $projected);
75 self::assertStringContainsString("CAST('A", $projected);
76 self::assertStringContainsString("B' AS CHAR)", $projected);
77 self::assertStringNotContainsString('`slug`', $projected);
78 self::assertSame(2, substr_count($projected, '), ('));
79 }
80
81 public function testProjectsCsvPrefixesIgnoredRowsUserVariablesAndSetExpressions(): void
82 {
83 $stream = tmpfile();
84 self::assertIsResource($stream);
85 $contents = "header\r\n>1,\"Alice, A\"\r\nignored\r\n>2,\"NULL\"\r\n";
86 self::assertSame(strlen($contents), fwrite($stream, $contents));
87 $metadata = stream_get_meta_data($stream);
88 $path = $metadata['uri'] ?? null;
89 self::assertIsString($path);
90
91 $parser = new MySqlParser();
92 $registry = new TableDefinitionRegistry();
93 $definition = (new MySqlSchemaParser($parser))->parse(
94 'CREATE TABLE items (id INT PRIMARY KEY, name VARCHAR(50), source VARCHAR(50) DEFAULT \'load\')',
95 );
96 self::assertNotNull($definition);
97 $registry->register('items', $definition);
98 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path)
99 . "' INTO TABLE items FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\'"
100 . " LINES STARTING BY '>' TERMINATED BY '\\r\\n' IGNORE 1 LINES (id, @raw)"
101 . ' SET name = UPPER(@raw)';
102 $statement = $parser->parseSingleLogicalStatement($sql);
103 self::assertInstanceOf(LoadStatement::class, $statement);
104
105 $projected = (new MySqlLoadDataProjector($registry))->project($sql, $statement);
106
107 self::assertStringStartsWith('INSERT INTO `items` (`id`, `name`) VALUES ', $projected);
108 self::assertStringContainsString("UPPER(CAST('Alice, A' AS CHAR))", $projected);
109 self::assertStringContainsString("UPPER(CAST('NULL' AS CHAR))", $projected);
110 self::assertStringContainsString("(CAST('1' AS CHAR)", $projected);
111 self::assertStringNotContainsString("CAST('>1' AS CHAR)", $projected);
112 self::assertStringNotContainsString('header', $projected);
113 self::assertStringNotContainsString('ignored', $projected);
114 self::assertSame(1, substr_count($projected, '), ('));
115 }
116
117 public function testProjectsReplaceAndMissingFieldsAsDefault(): void
118 {
119 $stream = tmpfile();
120 self::assertIsResource($stream);
121 self::assertSame(2, fwrite($stream, "7\n"));
122 $metadata = stream_get_meta_data($stream);
123 $path = $metadata['uri'] ?? null;
124 self::assertIsString($path);
125
126 $parser = new MySqlParser();
127 $registry = new TableDefinitionRegistry();
128 $definition = (new MySqlSchemaParser($parser))->parse(
129 'CREATE TABLE items (id INT PRIMARY KEY, name VARCHAR(50) DEFAULT \'unknown\')',
130 );
131 self::assertNotNull($definition);
132 $registry->register('items', $definition);
133 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path)
134 . "' REPLACE INTO TABLE items (id, name)";
135 $statement = $parser->parseSingleLogicalStatement($sql);
136 self::assertInstanceOf(LoadStatement::class, $statement);
137
138 $projected = (new MySqlLoadDataProjector($registry))->project($sql, $statement);
139
140 self::assertStringStartsWith('REPLACE INTO `items` (`id`, `name`) VALUES ', $projected);
141 self::assertStringEndsWith("(CAST('7' AS CHAR), DEFAULT)", $projected);
142 }
143
144 public function testIgnoreRowsSkipsCompleteRecordsWithoutARequiredPrefix(): void
145 {
146 $stream = tmpfile();
147 self::assertIsResource($stream);
148 $contents = "header\n1\tAlice\n2\tBob\n";
149 self::assertSame(strlen($contents), fwrite($stream, $contents));
150 $metadata = stream_get_meta_data($stream);
151 $path = $metadata['uri'] ?? null;
152 self::assertIsString($path);
153
154 $parser = new MySqlParser();
155 $registry = new TableDefinitionRegistry();
156 $definition = (new MySqlSchemaParser($parser))->parse(
157 'CREATE TABLE items (id INT PRIMARY KEY, name VARCHAR(50))',
158 );
159 self::assertNotNull($definition);
160 $registry->register('items', $definition);
161 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path)
162 . "' INTO TABLE items IGNORE 1 LINES";
163 $statement = $parser->parseSingleLogicalStatement($sql);
164 self::assertInstanceOf(LoadStatement::class, $statement);
165
166 $projected = (new MySqlLoadDataProjector($registry))->project($sql, $statement);
167
168 self::assertStringNotContainsString('header', $projected);
169 self::assertStringContainsString("CAST('1' AS CHAR), CAST('Alice' AS CHAR)", $projected);
170 self::assertStringContainsString("CAST('2' AS CHAR), CAST('Bob' AS CHAR)", $projected);
171 self::assertSame(1, substr_count($projected, '), ('));
172 }
173
174 public function testNoIgnoreClauseKeepsEveryRecord(): void
175 {
176 $stream = tmpfile();
177 self::assertIsResource($stream);
178 $contents = "1\tAlice\n2\tBob\n";
179 self::assertSame(strlen($contents), fwrite($stream, $contents));
180 $metadata = stream_get_meta_data($stream);
181 $path = $metadata['uri'] ?? null;
182 self::assertIsString($path);
183
184 $parser = new MySqlParser();
185 $registry = new TableDefinitionRegistry();
186 $definition = (new MySqlSchemaParser($parser))->parse(
187 'CREATE TABLE items (id INT PRIMARY KEY, name VARCHAR(50))',
188 );
189 self::assertNotNull($definition);
190 $registry->register('items', $definition);
191 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path) . "' INTO TABLE items";
192 $statement = $parser->parseSingleLogicalStatement($sql);
193 self::assertInstanceOf(LoadStatement::class, $statement);
194
195 $projected = (new MySqlLoadDataProjector($registry))->project($sql, $statement);
196
197 self::assertStringContainsString("CAST('1' AS CHAR), CAST('Alice' AS CHAR)", $projected);
198 self::assertStringContainsString("CAST('2' AS CHAR), CAST('Bob' AS CHAR)", $projected);
199 self::assertSame(1, substr_count($projected, '), ('));
200 }
201
202 public function testDefaultTargetsPreserveValuePositionsAroundGeneratedColumns(): void
203 {
204 $stream = tmpfile();
205 self::assertIsResource($stream);
206 self::assertSame(8, fwrite($stream, "1\tAlice\n"));
207 $metadata = stream_get_meta_data($stream);
208 $path = $metadata['uri'] ?? null;
209 self::assertIsString($path);
210
211 $parser = new MySqlParser();
212 $registry = new TableDefinitionRegistry();
213 $definition = (new MySqlSchemaParser($parser))->parse(
214 'CREATE TABLE items (id INT PRIMARY KEY, doubled INT AS (id * 2), name VARCHAR(50))',
215 );
216 self::assertNotNull($definition);
217 $registry->register('items', $definition);
218 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path) . "' INTO TABLE items";
219 $statement = $parser->parseSingleLogicalStatement($sql);
220 self::assertInstanceOf(LoadStatement::class, $statement);
221
222 $projected = (new MySqlLoadDataProjector($registry))->project($sql, $statement);
223
224 self::assertStringStartsWith('INSERT INTO `items` (`id`, `name`) VALUES ', $projected);
225 self::assertStringEndsWith("(CAST('1' AS CHAR), CAST('Alice' AS CHAR))", $projected);
226 self::assertStringNotContainsString('`doubled`', $projected);
227 }
228
229 public function testProjectsEmptyInputAsAnEmptySelect(): void
230 {
231 $stream = tmpfile();
232 self::assertIsResource($stream);
233 $metadata = stream_get_meta_data($stream);
234 $path = $metadata['uri'] ?? null;
235 self::assertIsString($path);
236
237 $parser = new MySqlParser();
238 $registry = new TableDefinitionRegistry();
239 $definition = (new MySqlSchemaParser($parser))->parse('CREATE TABLE items (id INT PRIMARY KEY)');
240 self::assertNotNull($definition);
241 $registry->register('items', $definition);
242 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path) . "' IGNORE INTO TABLE items";
243 $statement = $parser->parseSingleLogicalStatement($sql);
244 self::assertInstanceOf(LoadStatement::class, $statement);
245
246 $projected = (new MySqlLoadDataProjector($registry))->project($sql, $statement);
247
248 self::assertSame('INSERT IGNORE INTO `items` (`id`) SELECT NULL AS `id` WHERE FALSE', $projected);
249 }
250
251 public function testRejectsUnreadableInputBeforeProjection(): void
252 {
253 $parser = new MySqlParser();
254 $registry = new TableDefinitionRegistry();
255 $definition = (new MySqlSchemaParser($parser))->parse('CREATE TABLE items (id INT PRIMARY KEY)');
256 self::assertNotNull($definition);
257 $registry->register('items', $definition);
258 $sql = "LOAD DATA LOCAL INFILE '/path/that/does/not/exist' INTO TABLE items";
259 $statement = $parser->parseSingleLogicalStatement($sql);
260 self::assertInstanceOf(LoadStatement::class, $statement);
261
262 self::expectException(UnsupportedSqlException::class);
263 self::expectExceptionMessage('input file is not readable');
264
265 (new MySqlLoadDataProjector($registry))->project($sql, $statement);
266 }
267
268 public function testRejectsGeneratedColumnTargets(): void
269 {
270 $stream = tmpfile();
271 self::assertIsResource($stream);
272 self::assertSame(2, fwrite($stream, "1\n"));
273 $metadata = stream_get_meta_data($stream);
274 $path = $metadata['uri'] ?? null;
275 self::assertIsString($path);
276
277 $parser = new MySqlParser();
278 $registry = new TableDefinitionRegistry();
279 $definition = (new MySqlSchemaParser($parser))->parse(
280 'CREATE TABLE items (id INT PRIMARY KEY, doubled INT AS (id * 2))',
281 );
282 self::assertNotNull($definition);
283 $registry->register('items', $definition);
284 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path) . "' INTO TABLE items (doubled)";
285 $statement = $parser->parseSingleLogicalStatement($sql);
286 self::assertInstanceOf(LoadStatement::class, $statement);
287
288 self::expectException(UnsupportedSqlException::class);
289 self::expectExceptionMessage('generated column');
290
291 (new MySqlLoadDataProjector($registry))->project($sql, $statement);
292 }
293
294 public function testDecodesControlEscapesAndEscapedRecordTerminators(): void
295 {
296 $stream = tmpfile();
297 self::assertIsResource($stream);
298 $encoded = "1\tA\\0B\\bC\\nD\\rE\\tF\\ZG\\\\H\\\nJ\n";
299 self::assertSame(strlen($encoded), fwrite($stream, $encoded));
300 $metadata = stream_get_meta_data($stream);
301 $path = $metadata['uri'] ?? null;
302 self::assertIsString($path);
303
304 $parser = new MySqlParser();
305 $registry = new TableDefinitionRegistry();
306 $definition = (new MySqlSchemaParser($parser))->parse(
307 'CREATE TABLE items (id INT PRIMARY KEY, payload VARCHAR(255))',
308 );
309 self::assertNotNull($definition);
310 $registry->register('items', $definition);
311 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path) . "' INTO TABLE items";
312 $statement = $parser->parseSingleLogicalStatement($sql);
313 self::assertInstanceOf(LoadStatement::class, $statement);
314
315 $projected = (new MySqlLoadDataProjector($registry))->project($sql, $statement);
316 $decoded = "A\0B\x08C\nD\rE\tF\x1aG\\H\nJ";
317
318 self::assertStringContainsString("CONVERT(X'" . bin2hex($decoded) . "' USING utf8mb4)", $projected);
319 self::assertSame(0, substr_count($projected, '), ('));
320 }
321
322 public function testDistinguishesQuotedNullAndDoubledEnclosures(): void
323 {
324 $stream = tmpfile();
325 self::assertIsResource($stream);
326 $encoded = "1,\"A\"\"B\",\"NULL\",NULL,\\N,A\\,B\n";
327 self::assertSame(strlen($encoded), fwrite($stream, $encoded));
328 $metadata = stream_get_meta_data($stream);
329 $path = $metadata['uri'] ?? null;
330 self::assertIsString($path);
331
332 $parser = new MySqlParser();
333 $registry = new TableDefinitionRegistry();
334 $definition = (new MySqlSchemaParser($parser))->parse(
335 'CREATE TABLE items (id INT, a VARCHAR(20), b VARCHAR(20), c VARCHAR(20), d VARCHAR(20), e VARCHAR(20))',
336 );
337 self::assertNotNull($definition);
338 $registry->register('items', $definition);
339 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path)
340 . "' INTO TABLE items FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\'";
341 $statement = $parser->parseSingleLogicalStatement($sql);
342 self::assertInstanceOf(LoadStatement::class, $statement);
343
344 $projected = (new MySqlLoadDataProjector($registry))->project($sql, $statement);
345
346 self::assertStringContainsString("CAST('A\"B' AS CHAR)", $projected);
347 self::assertStringContainsString("CAST('NULL' AS CHAR)", $projected);
348 self::assertSame(2, substr_count($projected, ', NULL'));
349 self::assertStringContainsString("CAST('A,B' AS CHAR)", $projected);
350 }
351
352 public function testProjectsEverySetTarget(): void
353 {
354 $stream = tmpfile();
355 self::assertIsResource($stream);
356 $contents = "1\talice\timport\n";
357 self::assertSame(strlen($contents), fwrite($stream, $contents));
358 $metadata = stream_get_meta_data($stream);
359 $path = $metadata['uri'] ?? null;
360 self::assertIsString($path);
361
362 $parser = new MySqlParser();
363 $registry = new TableDefinitionRegistry();
364 $definition = (new MySqlSchemaParser($parser))->parse(
365 'CREATE TABLE items (id INT, name VARCHAR(20), note VARCHAR(20), source VARCHAR(20))',
366 );
367 self::assertNotNull($definition);
368 $registry->register('items', $definition);
369 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path)
370 . "' INTO TABLE items (id, @Raw, source) SET name = UPPER(@RAW), note = CONCAT(@raw, '!')";
371 $statement = $parser->parseSingleLogicalStatement($sql);
372 self::assertInstanceOf(LoadStatement::class, $statement);
373
374 $projected = (new MySqlLoadDataProjector($registry))->project($sql, $statement);
375
376 self::assertStringContainsString('`id`, `name`, `note`, `source`', $projected);
377 self::assertStringContainsString("UPPER(CAST('alice' AS CHAR))", $projected);
378 self::assertStringContainsString("CONCAT(CAST('alice' AS CHAR), '!')", $projected);
379 self::assertStringEndsWith(", CAST('import' AS CHAR))", $projected);
380 }
381
382 public function testRejectsAMissingTargetBeforeReadingTheInput(): void
383 {
384 $parser = new MySqlParser();
385 $statement = $parser->parseSingleLogicalStatement(
386 "LOAD DATA INFILE '/path/that/does/not/exist' INTO TABLE items",
387 );
388 self::assertInstanceOf(LoadStatement::class, $statement);
389 $statement->table = null;
390
391 self::expectException(UnsupportedSqlException::class);
392 self::expectExceptionMessage('Cannot resolve LOAD DATA target');
393
394 (new MySqlLoadDataProjector(new TableDefinitionRegistry()))->project('LOAD DATA', $statement);
395 }
396
397 public function testRejectsAReadableDirectoryAsANonFileInput(): void
398 {
399 $parser = new MySqlParser();
400 $registry = new TableDefinitionRegistry();
401 $definition = (new MySqlSchemaParser($parser))->parse('CREATE TABLE items (id INT PRIMARY KEY)');
402 self::assertNotNull($definition);
403 $registry->register('items', $definition);
404 $path = sys_get_temp_dir();
405 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path) . "' INTO TABLE items";
406 $statement = $parser->parseSingleLogicalStatement($sql);
407 self::assertInstanceOf(LoadStatement::class, $statement);
408
409 self::expectException(UnsupportedSqlException::class);
410 self::expectExceptionMessage('LOAD DATA input file is not readable');
411
412 (new MySqlLoadDataProjector($registry))->project($sql, $statement);
413 }
414
415 public function testEmptyExplicitColumnListHasNoTargetColumns(): void
416 {
417 $stream = tmpfile();
418 self::assertIsResource($stream);
419 self::assertSame(2, fwrite($stream, "1\n"));
420 $metadata = stream_get_meta_data($stream);
421 $path = $metadata['uri'] ?? null;
422 self::assertIsString($path);
423
424 $parser = new MySqlParser();
425 $registry = new TableDefinitionRegistry();
426 $definition = (new MySqlSchemaParser($parser))->parse('CREATE TABLE items (id INT PRIMARY KEY)');
427 self::assertNotNull($definition);
428 $registry->register('items', $definition);
429 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path) . "' INTO TABLE items ()";
430 $statement = $parser->parseSingleLogicalStatement($sql);
431 self::assertInstanceOf(LoadStatement::class, $statement);
432
433 self::expectException(UnsupportedSqlException::class);
434 self::expectExceptionMessage('LOAD DATA has no target columns');
435
436 (new MySqlLoadDataProjector($registry))->project($sql, $statement);
437 }
438
439 public function testFieldStateHandlesFirstFieldEnclosuresAndFinalEscapeBoundaries(): void
440 {
441 $stream = tmpfile();
442 self::assertIsResource($stream);
443 $contents = "\"A\"\"\nB\",\"x\ny\"\n\"P\nQ\",r\nA\"B\nNULL,z\nA\\N,z\nE\\t\"F\nC\\t\nD\\";
444 self::assertSame(strlen($contents), fwrite($stream, $contents));
445 $metadata = stream_get_meta_data($stream);
446 $path = $metadata['uri'] ?? null;
447 self::assertIsString($path);
448
449 $parser = new MySqlParser();
450 $registry = new TableDefinitionRegistry();
451 $definition = (new MySqlSchemaParser($parser))->parse(
452 'CREATE TABLE items (first_value VARCHAR(50), second_value VARCHAR(50))',
453 );
454 self::assertNotNull($definition);
455 $registry->register('items', $definition);
456 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path)
457 . "' INTO TABLE items FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\'";
458 $statement = $parser->parseSingleLogicalStatement($sql);
459 self::assertInstanceOf(LoadStatement::class, $statement);
460
461 $projected = (new MySqlLoadDataProjector($registry))->project($sql, $statement);
462
463 self::assertStringContainsString("CAST('A\"\nB' AS CHAR), CAST('x\ny' AS CHAR)", $projected);
464 self::assertStringContainsString("CAST('P\nQ' AS CHAR), CAST('r' AS CHAR)", $projected);
465 self::assertStringContainsString("CAST('A\"B' AS CHAR), DEFAULT", $projected);
466 self::assertStringContainsString('VALUES ', $projected);
467 self::assertStringContainsString('(NULL, CAST(\'z\' AS CHAR))', $projected);
468 self::assertStringContainsString("CAST('AN' AS CHAR), CAST('z' AS CHAR)", $projected);
469 self::assertStringContainsString("CAST('E\t\"F' AS CHAR), DEFAULT", $projected);
470 self::assertStringContainsString("CAST('C\t' AS CHAR), DEFAULT", $projected);
471 self::assertStringContainsString("CONVERT(X'445c' USING utf8mb4) AS CHAR), DEFAULT", $projected);
472 self::assertSame(7, substr_count($projected, '), ('));
473 }
474
475 public function testSetVariableSubstitutionIsForwardOnlyAndPreservesSystemVariables(): void
476 {
477 $stream = tmpfile();
478 self::assertIsResource($stream);
479 $contents = "1\talice\tv1\n";
480 self::assertSame(strlen($contents), fwrite($stream, $contents));
481 $metadata = stream_get_meta_data($stream);
482 $path = $metadata['uri'] ?? null;
483 self::assertIsString($path);
484
485 $parser = new MySqlParser();
486 $registry = new TableDefinitionRegistry();
487 $definition = (new MySqlSchemaParser($parser))->parse(
488 'CREATE TABLE items (id INT, name VARCHAR(100))',
489 );
490 self::assertNotNull($definition);
491 $registry->register('items', $definition);
492 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path)
493 . "' INTO TABLE items (id, @raw, @version)"
494 . ' SET name = CONCAT(@@version, @raw, @ + @raw, @missing, @raw)';
495 $statement = $parser->parseSingleLogicalStatement($sql);
496 self::assertInstanceOf(LoadStatement::class, $statement);
497
498 $projected = (new MySqlLoadDataProjector($registry))->project($sql, $statement);
499
500 self::assertStringContainsString(
501 "CONCAT(@@version, CAST('alice' AS CHAR), @+ CAST('alice' AS CHAR),"
502 . " @missing, CAST('alice' AS CHAR))",
503 $projected,
504 );
505 }
506
507 #[TestWith(["FIELDS TERMINATED BY ''", 'fixed-row'])]
508 #[TestWith(["LINES TERMINATED BY ''", 'fixed-row'])]
509 #[TestWith(["FIELDS ENCLOSED BY 'xx'", 'single-byte'])]
510 #[TestWith(["FIELDS ESCAPED BY 'xx'", 'single-byte'])]
511 #[TestWith(['PARTITION (p0)', 'PARTITION'])]
512 #[TestWith(['CHARACTER SET latin1', 'CHARACTER SET'])]
513 #[TestWith(['(id, ID)', 'Duplicate'])]
514 #[TestWith(['(missing)', 'Unknown'])]
515 public function testRejectsUnsupportedLoadDataShapes(string $clause, string $message): void
516 {
517 $stream = tmpfile();
518 self::assertIsResource($stream);
519 self::assertSame(2, fwrite($stream, "1\n"));
520 $metadata = stream_get_meta_data($stream);
521 $path = $metadata['uri'] ?? null;
522 self::assertIsString($path);
523
524 $parser = new MySqlParser();
525 $registry = new TableDefinitionRegistry();
526 $definition = (new MySqlSchemaParser($parser))->parse('CREATE TABLE items (id INT, name VARCHAR(20))');
527 self::assertNotNull($definition);
528 $registry->register('items', $definition);
529 $sql = "LOAD DATA INFILE '" . str_replace("'", "''", $path) . "' INTO TABLE items " . $clause;
530 $statement = $parser->parseSingleLogicalStatement($sql);
531 self::assertInstanceOf(LoadStatement::class, $statement);
532
533 self::expectException(UnsupportedSqlException::class);
534 self::expectExceptionMessage($message);
535
536 (new MySqlLoadDataProjector($registry))->project($sql, $statement);
537 }
538}
539