packages/sql-fixture/tests/Integration/PostgreSql/PostgreSqlEndToEndTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\PostgreSql;
6
7use Faker\Factory;
8use PHPUnit\Framework\Attributes\CoversClass;
9use PHPUnit\Framework\Attributes\Test;
10use PHPUnit\Framework\Attributes\UsesClass;
11use PHPUnit\Framework\TestCase;
12use SqlFixture\Platform\PostgreSql\PostgreSqlSchemaParser;
13use SqlFixture\Platform\PostgreSql\PostgreSqlTypeMapper;
14use SqlFixture\Provider\FixtureGenerator;
15use SqlFixture\Provider\FixtureProvider;
16use SqlFixture\Provider\PlatformFactory;
17use SqlFixture\Schema\ColumnDefinition;
18use SqlFixture\Schema\TableSchema;
19
20#[CoversClass(FixtureProvider::class)]
21#[UsesClass(FixtureGenerator::class)]
22#[UsesClass(PlatformFactory::class)]
23#[UsesClass(PostgreSqlSchemaParser::class)]
24#[UsesClass(PostgreSqlTypeMapper::class)]
25#[UsesClass(ColumnDefinition::class)]
26#[UsesClass(TableSchema::class)]
27#[CoversClass(\SqlFixture\Provider\SqlSchemaProvider::class)]
28#[UsesClass(\SqlFixture\Fixture\FixtureSet::class)]
29#[UsesClass(\SqlFixture\Fixture\GenerationRun::class)]
30#[UsesClass(\SqlFixture\Fixture\OverrideRows::class)]
31#[UsesClass(\SqlFixture\Fixture\PlanGenerator::class)]
32#[UsesClass(\SqlFixture\Fixture\PlanSchemaException::class)]
33#[UsesClass(\SqlFixture\Fixture\PlanSchemaValidator::class)]
34#[UsesClass(\SqlFixture\Fixture\RowSpec::class)]
35#[UsesClass(\SqlFixture\Fixture\TableOverrides::class)]
36#[UsesClass(\SqlFixture\Hydrator\HydrationException::class)]
37#[UsesClass(\SqlFixture\Hydrator\HydratorInterface::class)]
38#[UsesClass(\SqlFixture\Hydrator\ReflectionHydrator::class)]
39#[UsesClass(\SqlFixture\Plan\ColumnRef::class)]
40#[UsesClass(\SqlFixture\Plan\FixturePlan::class)]
41#[UsesClass(\SqlFixture\Plan\PlanParser::class)]
42#[UsesClass(\SqlFixture\Plan\PlanPrinter::class)]
43#[UsesClass(\SqlFixture\Plan\PlanSyntaxException::class)]
44#[UsesClass(\SqlFixture\Plan\Relation::class)]
45#[UsesClass(\SqlFixture\Plan\RelationKind::class)]
46#[UsesClass(\SqlFixture\Plan\RelationSide::class)]
47#[UsesClass(\SqlFixture\Platform\MySql\MySqlSchemaFetcher::class)]
48#[UsesClass(\SqlFixture\Platform\MySql\MySqlSchemaParser::class)]
49#[UsesClass(\SqlFixture\Platform\MySql\MySqlTypeMapper::class)]
50#[UsesClass(\SqlFixture\Platform\PostgreSql\PostgreSqlSchemaFetcher::class)]
51#[UsesClass(\SqlFixture\Platform\Sqlite\SqliteSchemaFetcher::class)]
52#[UsesClass(\SqlFixture\Platform\Sqlite\SqliteSchemaParser::class)]
53#[UsesClass(\SqlFixture\Platform\Sqlite\SqliteTypeMapper::class)]
54#[UsesClass(\SqlFixture\Schema\SchemaFetcherInterface::class)]
55#[UsesClass(\SqlFixture\Schema\SchemaNotFoundException::class)]
56#[UsesClass(\SqlFixture\Schema\SchemaParseException::class)]
57#[UsesClass(\SqlFixture\Schema\SchemaParserInterface::class)]
58#[UsesClass(\SqlFixture\Schema\SchemaResolverInterface::class)]
59#[UsesClass(\SqlFixture\Schema\StaticSchemaResolver::class)]
60#[UsesClass(\SqlFixture\Schema\TableIdentifier::class)]
61#[UsesClass(\SqlFixture\TypeMapper\TypeMapperInterface::class)]
62#[UsesClass(\SqlFixture\Fixture\Generation\ConnectedTables::class)]
63#[UsesClass(\SqlFixture\Fixture\Generation\OverrideSpecs::class)]
64#[UsesClass(\SqlFixture\Fixture\Generation\RelationCounts::class)]
65#[UsesClass(\SqlFixture\Fixture\Generation\RelationProjection::class)]
66#[UsesClass(\SqlFixture\Fixture\Generation\RowMaterializer::class)]
67#[UsesClass(\SqlFixture\Fixture\RowGeneration::class)]
68#[UsesClass(\SqlFixture\Fixture\Validation\EndpointValidator::class)]
69#[UsesClass(\SqlFixture\Fixture\Validation\OverrideValidator::class)]
70#[UsesClass(\SqlFixture\Hydrator\Reflection\ConstructorHydration::class)]
71#[UsesClass(\SqlFixture\Hydrator\Reflection\PropertyHydration::class)]
72#[UsesClass(\SqlFixture\Hydrator\Reflection\PropertyNames::class)]
73#[UsesClass(\SqlFixture\Hydrator\Reflection\ValueConversion::class)]
74#[UsesClass(\SqlFixture\Fixture\Exception\InvalidOverrideException::class)]
75#[UsesClass(\SqlFixture\Plan\Parsing\PlanStatements::class)]
76#[UsesClass(\SqlFixture\Plan\Parsing\RelationCursor::class)]
77#[UsesClass(\SqlFixture\Plan\Parsing\RelationReader::class)]
78#[UsesClass(\SqlFixture\Plan\PlanStructureException::class)]
79#[UsesClass(\SqlFixture\Plan\Printing\PlanTables::class)]
80#[UsesClass(\SqlFixture\Plan\Printing\RelationGroups::class)]
81#[UsesClass(\SqlFixture\Plan\Printing\StatementPrinter::class)]
82#[UsesClass(\SqlFixture\Plan\Validation\PlanValidation::class)]
83#[UsesClass(\SqlFixture\Plan\Validation\TableName::class)]
84#[UsesClass(\SqlFixture\Platform\MySql\Schema\ColumnParser::class)]
85#[UsesClass(\SqlFixture\Platform\MySql\Schema\CreateTableQuery::class)]
86#[UsesClass(\SqlFixture\Platform\MySql\Schema\DefaultExpression::class)]
87#[UsesClass(\SqlFixture\Platform\MySql\Schema\DefinitionIntegrity::class)]
88#[UsesClass(\SqlFixture\Platform\MySql\Schema\IdentifierQuoter::class)]
89#[UsesClass(\SqlFixture\Platform\MySql\Schema\TableDefinition::class)]
90#[UsesClass(\SqlFixture\Platform\MySql\Schema\TypeParameters::class)]
91#[UsesClass(\SqlFixture\Platform\MySql\Value\ColumnGenerator::class)]
92#[UsesClass(\SqlFixture\Platform\MySql\Value\DecimalGenerator::class)]
93#[UsesClass(\SqlFixture\Platform\MySql\Value\GeometryGenerator::class)]
94#[UsesClass(\SqlFixture\Platform\MySql\Value\IntegerGenerator::class)]
95#[UsesClass(\SqlFixture\Platform\MySql\Value\NumericGenerator::class)]
96#[UsesClass(\SqlFixture\Platform\MySql\Value\SpatialGenerator::class)]
97#[UsesClass(\SqlFixture\Platform\MySql\Value\StringGenerator::class)]
98#[UsesClass(\SqlFixture\Platform\MySql\Value\TextGenerator::class)]
99#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\CatalogColumn::class)]
100#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\CatalogDdl::class)]
101#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\CatalogQuery::class)]
102#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\CatalogSchema::class)]
103#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\ColumnParser::class)]
104#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\DefaultExpression::class)]
105#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\DefinitionList::class)]
106#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\TableSyntax::class)]
107#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\TypeDeclaration::class)]
108#[UsesClass(\SqlFixture\Platform\PostgreSql\Value\ColumnGenerator::class)]
109#[UsesClass(\SqlFixture\Platform\PostgreSql\Value\DecimalGenerator::class)]
110#[UsesClass(\SqlFixture\Platform\PostgreSql\Value\NumericGenerator::class)]
111#[UsesClass(\SqlFixture\Platform\PostgreSql\Value\StringGenerator::class)]
112#[UsesClass(\SqlFixture\Platform\PostgreSql\Value\StructuredGenerator::class)]
113#[UsesClass(\SqlFixture\Platform\PostgreSql\Value\TemporalGenerator::class)]
114#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\ColumnParser::class)]
115#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\CreateTableQuery::class)]
116#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\DefaultExpression::class)]
117#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\DefinitionList::class)]
118#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\PragmaColumn::class)]
119#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\PragmaSchema::class)]
120#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\TableSyntax::class)]
121#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\TypeDeclaration::class)]
122#[UsesClass(\SqlFixture\Platform\Sqlite\Value\ColumnGenerator::class)]
123#[UsesClass(\SqlFixture\Platform\Sqlite\Value\TypeAffinity::class)]
124#[UsesClass(\SqlFixture\Schema\DefinitionSegments::class)]
125#[UsesClass(\SqlFixture\Schema\TypeShape::class)]
126#[UsesClass(\SqlFixture\TypeMapper\ParagraphGenerator::class)]
127#[UsesClass(\SqlFixture\Platform\MySql\Schema\TableDefinitionInput::class)]
128#[UsesClass(\SqlFixture\Hydrator\Exception\ClassNotFoundException::class)]
129#[UsesClass(\SqlFixture\Hydrator\Exception\MissingConstructorArgumentException::class)]
130#[UsesClass(\SqlFixture\Fixture\Exception\UnknownOverrideColumnException::class)]
131#[UsesClass(\SqlFixture\Fixture\Exception\NullOverrideException::class)]
132#[UsesClass(\SqlFixture\Fixture\Exception\GeneratedColumnOverrideException::class)]
133#[UsesClass(\SqlFixture\Fixture\Exception\GeneratedColumnReferenceException::class)]
134#[UsesClass(\SqlFixture\Fixture\Exception\MissingRelationValueException::class)]
135#[UsesClass(\SqlFixture\Fixture\Exception\UnknownPlanColumnException::class)]
136#[UsesClass(\SqlFixture\Plan\Exception\EmptyPlanException::class)]
137#[UsesClass(\SqlFixture\Plan\Exception\EmptyTableNameException::class)]
138#[UsesClass(\SqlFixture\Plan\Exception\MissingEndpointColumnsException::class)]
139#[UsesClass(\SqlFixture\Plan\Exception\InvalidTableNameException::class)]
140#[UsesClass(\SqlFixture\Plan\Exception\UnbalancedBracketsException::class)]
141#[UsesClass(\SqlFixture\Plan\Exception\UnexpectedPlanTokenException::class)]
142#[UsesClass(\SqlFixture\Plan\Exception\UnsupportedManyToManyException::class)]
143#[UsesClass(\SqlFixture\Plan\Exception\CompositeArityMismatchException::class)]
144#[UsesClass(\SqlFixture\Plan\Exception\DuplicateColumnBindingException::class)]
145#[UsesClass(\SqlFixture\Plan\Exception\CyclicDependencyException::class)]
146#[UsesClass(\SqlFixture\Plan\Exception\UnboundedSelfReferenceException::class)]
147#[UsesClass(\SqlFixture\Schema\Exception\InvalidSqlException::class)]
148#[UsesClass(\SqlFixture\Schema\Exception\ExpectedCreateTableException::class)]
149#[UsesClass(\SqlFixture\Schema\Exception\MissingColumnDefinitionsException::class)]
150#[UsesClass(\SqlFixture\TypeMapper\IntegerRange::class)]
151#[UsesClass(\SqlFixture\TypeMapper\IntegerWidth::class)]
152#[UsesClass(\SqlFixture\TypeMapper\DecimalRange::class)]
153#[UsesClass(\SqlFixture\Hydrator\Reflection\ConversionTarget::class)]
154#[UsesClass(\SqlFixture\Fixture\Choice\InverseRelations::class)]
155#[UsesClass(\SqlFixture\Fixture\Choice\ResolvedRow::class)]
156#[UsesClass(\SqlFixture\Fixture\Choice\RowChoices::class)]
157#[UsesClass(\SqlFixture\Plan\Choice\ChoiceValidation::class)]
158#[UsesClass(\SqlFixture\Plan\Choice\PlanContents::class)]
159#[UsesClass(\SqlFixture\Fixture\Generation\RowBindings::class)]
160#[UsesClass(\SqlFixture\Fixture\Generation\RecursiveRelationException::class)]
161#[UsesClass(\SqlFixture\Fixture\Generation\RelationValueException::class)]
162#[UsesClass(\SqlFixture\Fixture\RowGenerator::class)]
163final class PostgreSqlEndToEndTest extends TestCase
164{
165    #[Test]
166    public function fixtureWithCommonTypes(): void
167    {
168        $faker = Factory::create();
169        $faker->seed(12345);
170        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
171        $data = $provider->fixture(<<<'SQL'
172            CREATE TABLE users (
173                id SERIAL PRIMARY KEY,
174                name VARCHAR(100) NOT NULL,
175                email TEXT NOT NULL,
176                age INTEGER,
177                balance NUMERIC(10, 2),
178                active BOOLEAN DEFAULT TRUE,
179                created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
180            )
181            SQL);
182        self::assertArrayNotHasKey('id', $data);
183        self::assertArrayHasKey('name', $data);
184        self::assertIsString($data['name']);
185        self::assertLessThanOrEqual(100, strlen($data['name']));
186        self::assertArrayHasKey('email', $data);
187        self::assertIsString($data['email']);
188    }
189
190    #[Test]
191    public function fixtureWithPgSpecificTypes(): void
192    {
193        $faker = Factory::create();
194        $faker->seed(12345);
195        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
196        $data = $provider->fixture(<<<'SQL'
197            CREATE TABLE pg_types (
198                col_uuid UUID NOT NULL,
199                col_jsonb JSONB NOT NULL,
200                col_json JSON NOT NULL,
201                col_bytea BYTEA NOT NULL,
202                col_inet INET NOT NULL,
203                col_cidr CIDR NOT NULL,
204                col_macaddr MACADDR NOT NULL,
205                col_money MONEY NOT NULL,
206                col_interval INTERVAL NOT NULL,
207                col_xml XML NOT NULL
208            )
209            SQL);
210        $colUuid = $data['col_uuid'];
211        self::assertIsString($colUuid);
212        self::assertMatchesRegularExpression(
213            '/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/',
214            $colUuid
215        );
216        $colJsonb = $data['col_jsonb'];
217        self::assertIsString($colJsonb);
218        $decoded = json_decode($colJsonb, true);
219        self::assertIsArray($decoded);
220        $colJson = $data['col_json'];
221        self::assertIsString($colJson);
222        $decoded = json_decode($colJson, true);
223        self::assertIsArray($decoded);
224        $colBytea = $data['col_bytea'];
225        self::assertIsString($colBytea);
226        self::assertStringStartsWith('\\x', $colBytea);
227        $colInet = $data['col_inet'];
228        self::assertIsString($colInet);
229        self::assertNotFalse(filter_var($colInet, FILTER_VALIDATE_IP));
230        $colCidr = $data['col_cidr'];
231        self::assertIsString($colCidr);
232        self::assertStringContainsString('/24', $colCidr);
233        $colMacaddr = $data['col_macaddr'];
234        self::assertIsString($colMacaddr);
235        self::assertMatchesRegularExpression('/^([0-9A-Fa-f]{2}[:-]){5}[0-9A-Fa-f]{2}$/', $colMacaddr);
236        self::assertIsFloat($data['col_money']);
237        self::assertIsString($data['col_interval']);
238        $colXml = $data['col_xml'];
239        self::assertIsString($colXml);
240        self::assertStringStartsWith('<root>', $colXml);
241    }
242
243    #[Test]
244    public function fixtureWithSerialTypes(): void
245    {
246        $faker = Factory::create();
247        $faker->seed(12345);
248        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
249        $data = $provider->fixture(<<<'SQL'
250            CREATE TABLE serials (
251                id SERIAL PRIMARY KEY,
252                big_id BIGSERIAL NOT NULL,
253                small_id SMALLSERIAL NOT NULL,
254                name TEXT NOT NULL
255            )
256            SQL);
257
258        self::assertArrayNotHasKey('id', $data);
259        self::assertArrayNotHasKey('big_id', $data);
260        self::assertArrayNotHasKey('small_id', $data);
261        self::assertArrayHasKey('name', $data);
262    }
263
264    #[Test]
265    public function fixtureWithTimestampVariants(): void
266    {
267        $faker = Factory::create();
268        $faker->seed(12345);
269        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
270        $data = $provider->fixture(<<<'SQL'
271            CREATE TABLE times (
272                col_ts TIMESTAMP NOT NULL,
273                col_tstz TIMESTAMPTZ NOT NULL,
274                col_date DATE NOT NULL,
275                col_time TIME NOT NULL,
276                col_timetz TIMETZ NOT NULL
277            )
278            SQL);
279
280        $colTs = $data['col_ts'];
281        self::assertIsString($colTs);
282        self::assertMatchesRegularExpression('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $colTs);
283        self::assertIsString($data['col_tstz']);
284        $colDate = $data['col_date'];
285        self::assertIsString($colDate);
286        self::assertMatchesRegularExpression('/^\d{4}-\d{2}-\d{2}$/', $colDate);
287        $colTime = $data['col_time'];
288        self::assertIsString($colTime);
289        self::assertMatchesRegularExpression('/^\d{2}:\d{2}:\d{2}$/', $colTime);
290        self::assertIsString($data['col_timetz']);
291    }
292
293    #[Test]
294    public function fixtureWithArrayTypes(): void
295    {
296        $faker = Factory::create();
297        $faker->seed(12345);
298        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
299        $data = $provider->fixture(<<<'SQL'
300            CREATE TABLE arrays (
301                tags TEXT[] NOT NULL,
302                ids INTEGER[] NOT NULL
303            )
304            SQL);
305
306        $tags = $data['tags'];
307        self::assertIsString($tags);
308        self::assertStringStartsWith('{', $tags);
309        self::assertStringEndsWith('}', $tags);
310
311        $ids = $data['ids'];
312        self::assertIsString($ids);
313        self::assertStringStartsWith('{', $ids);
314        self::assertStringEndsWith('}', $ids);
315    }
316
317    #[Test]
318    public function fixtureWithOverrides(): void
319    {
320        $faker = Factory::create();
321        $faker->seed(12345);
322        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
323        $data = $provider->fixture(
324            'CREATE TABLE test (id SERIAL PRIMARY KEY, name TEXT NOT NULL)',
325            ['id' => 42, 'name' => 'Custom'],
326        );
327
328        self::assertSame(42, $data['id']);
329        self::assertSame('Custom', $data['name']);
330    }
331
332    #[Test]
333    public function fixtureWithGeneratedColumn(): void
334    {
335        $faker = Factory::create();
336        $faker->seed(12345);
337        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
338        $data = $provider->fixture(<<<'SQL'
339            CREATE TABLE test (
340                a INTEGER NOT NULL,
341                b INTEGER NOT NULL,
342                c INTEGER GENERATED ALWAYS AS (a + b) STORED
343            )
344            SQL);
345
346        self::assertArrayHasKey('a', $data);
347        self::assertArrayHasKey('b', $data);
348        self::assertArrayNotHasKey('c', $data);
349    }
350
351    #[Test]
352    public function fixtureWithNumericPrecision(): void
353    {
354        $faker = Factory::create();
355        $faker->seed(12345);
356        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
357        $data = $provider->fixture(<<<'SQL'
358            CREATE TABLE test (
359                price NUMERIC(5, 2) NOT NULL,
360                qty INTEGER NOT NULL,
361                rate DOUBLE PRECISION NOT NULL
362            )
363            SQL);
364
365        self::assertIsFloat($data['price']);
366        self::assertGreaterThanOrEqual(-999.99, $data['price']);
367        self::assertLessThanOrEqual(999.99, $data['price']);
368        self::assertIsInt($data['qty']);
369        self::assertIsFloat($data['rate']);
370    }
371
372    #[Test]
373    public function fixtureReproducibleWithSeed(): void
374    {
375        $sql = 'CREATE TABLE test (name TEXT NOT NULL, age INTEGER NOT NULL)';
376
377        $faker1 = Factory::create();
378        $faker1->seed(99999);
379        $provider1 = new FixtureProvider($faker1, dialect: PlatformFactory::DRIVER_PGSQL);
380        $data1 = $provider1->fixture($sql);
381
382        $faker2 = Factory::create();
383        $faker2->seed(99999);
384        $provider2 = new FixtureProvider($faker2, dialect: PlatformFactory::DRIVER_PGSQL);
385        $data2 = $provider2->fixture($sql);
386
387        self::assertSame($data1['name'], $data2['name']);
388        self::assertSame($data1['age'], $data2['age']);
389    }
390}
391