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\FixtureGenerator;
13use SqlFixture\FixtureProvider;
14use SqlFixture\Platform\PlatformFactory;
15use SqlFixture\Platform\PostgreSql\PostgreSqlSchemaParser;
16use SqlFixture\Platform\PostgreSql\PostgreSqlTypeMapper;
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\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)]
154final class PostgreSqlEndToEndTest extends TestCase
155{
156    #[Test]
157    public function fixtureWithCommonTypes(): void
158    {
159        $faker = Factory::create();
160        $faker->seed(12345);
161        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
162        $data = $provider->fixture(<<<'SQL'
163            CREATE TABLE users (
164                id SERIAL PRIMARY KEY,
165                name VARCHAR(100) NOT NULL,
166                email TEXT NOT NULL,
167                age INTEGER,
168                balance NUMERIC(10, 2),
169                active BOOLEAN DEFAULT TRUE,
170                created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
171            )
172            SQL);
173        self::assertArrayNotHasKey('id', $data);
174        self::assertArrayHasKey('name', $data);
175        self::assertIsString($data['name']);
176        self::assertLessThanOrEqual(100, strlen($data['name']));
177        self::assertArrayHasKey('email', $data);
178        self::assertIsString($data['email']);
179    }
180
181    #[Test]
182    public function fixtureWithPgSpecificTypes(): void
183    {
184        $faker = Factory::create();
185        $faker->seed(12345);
186        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
187        $data = $provider->fixture(<<<'SQL'
188            CREATE TABLE pg_types (
189                col_uuid UUID NOT NULL,
190                col_jsonb JSONB NOT NULL,
191                col_json JSON NOT NULL,
192                col_bytea BYTEA NOT NULL,
193                col_inet INET NOT NULL,
194                col_cidr CIDR NOT NULL,
195                col_macaddr MACADDR NOT NULL,
196                col_money MONEY NOT NULL,
197                col_interval INTERVAL NOT NULL,
198                col_xml XML NOT NULL
199            )
200            SQL);
201        $colUuid = $data['col_uuid'];
202        self::assertIsString($colUuid);
203        self::assertMatchesRegularExpression(
204            '/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/',
205            $colUuid
206        );
207        $colJsonb = $data['col_jsonb'];
208        self::assertIsString($colJsonb);
209        $decoded = json_decode($colJsonb, true);
210        self::assertIsArray($decoded);
211        $colJson = $data['col_json'];
212        self::assertIsString($colJson);
213        $decoded = json_decode($colJson, true);
214        self::assertIsArray($decoded);
215        $colBytea = $data['col_bytea'];
216        self::assertIsString($colBytea);
217        self::assertStringStartsWith('\\x', $colBytea);
218        $colInet = $data['col_inet'];
219        self::assertIsString($colInet);
220        self::assertNotFalse(filter_var($colInet, FILTER_VALIDATE_IP));
221        $colCidr = $data['col_cidr'];
222        self::assertIsString($colCidr);
223        self::assertStringContainsString('/24', $colCidr);
224        $colMacaddr = $data['col_macaddr'];
225        self::assertIsString($colMacaddr);
226        self::assertMatchesRegularExpression('/^([0-9A-Fa-f]{2}[:-]){5}[0-9A-Fa-f]{2}$/', $colMacaddr);
227        self::assertIsFloat($data['col_money']);
228        self::assertIsString($data['col_interval']);
229        $colXml = $data['col_xml'];
230        self::assertIsString($colXml);
231        self::assertStringStartsWith('<root>', $colXml);
232    }
233
234    #[Test]
235    public function fixtureWithSerialTypes(): void
236    {
237        $faker = Factory::create();
238        $faker->seed(12345);
239        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
240        $data = $provider->fixture(<<<'SQL'
241            CREATE TABLE serials (
242                id SERIAL PRIMARY KEY,
243                big_id BIGSERIAL NOT NULL,
244                small_id SMALLSERIAL NOT NULL,
245                name TEXT NOT NULL
246            )
247            SQL);
248
249        self::assertArrayNotHasKey('id', $data);
250        self::assertArrayNotHasKey('big_id', $data);
251        self::assertArrayNotHasKey('small_id', $data);
252        self::assertArrayHasKey('name', $data);
253    }
254
255    #[Test]
256    public function fixtureWithTimestampVariants(): void
257    {
258        $faker = Factory::create();
259        $faker->seed(12345);
260        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
261        $data = $provider->fixture(<<<'SQL'
262            CREATE TABLE times (
263                col_ts TIMESTAMP NOT NULL,
264                col_tstz TIMESTAMPTZ NOT NULL,
265                col_date DATE NOT NULL,
266                col_time TIME NOT NULL,
267                col_timetz TIMETZ NOT NULL
268            )
269            SQL);
270
271        $colTs = $data['col_ts'];
272        self::assertIsString($colTs);
273        self::assertMatchesRegularExpression('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $colTs);
274        self::assertIsString($data['col_tstz']);
275        $colDate = $data['col_date'];
276        self::assertIsString($colDate);
277        self::assertMatchesRegularExpression('/^\d{4}-\d{2}-\d{2}$/', $colDate);
278        $colTime = $data['col_time'];
279        self::assertIsString($colTime);
280        self::assertMatchesRegularExpression('/^\d{2}:\d{2}:\d{2}$/', $colTime);
281        self::assertIsString($data['col_timetz']);
282    }
283
284    #[Test]
285    public function fixtureWithArrayTypes(): void
286    {
287        $faker = Factory::create();
288        $faker->seed(12345);
289        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
290        $data = $provider->fixture(<<<'SQL'
291            CREATE TABLE arrays (
292                tags TEXT[] NOT NULL,
293                ids INTEGER[] NOT NULL
294            )
295            SQL);
296
297        $tags = $data['tags'];
298        self::assertIsString($tags);
299        self::assertStringStartsWith('{', $tags);
300        self::assertStringEndsWith('}', $tags);
301
302        $ids = $data['ids'];
303        self::assertIsString($ids);
304        self::assertStringStartsWith('{', $ids);
305        self::assertStringEndsWith('}', $ids);
306    }
307
308    #[Test]
309    public function fixtureWithOverrides(): void
310    {
311        $faker = Factory::create();
312        $faker->seed(12345);
313        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
314        $data = $provider->fixture(
315            'CREATE TABLE test (id SERIAL PRIMARY KEY, name TEXT NOT NULL)',
316            ['id' => 42, 'name' => 'Custom'],
317        );
318
319        self::assertSame(42, $data['id']);
320        self::assertSame('Custom', $data['name']);
321    }
322
323    #[Test]
324    public function fixtureWithGeneratedColumn(): void
325    {
326        $faker = Factory::create();
327        $faker->seed(12345);
328        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
329        $data = $provider->fixture(<<<'SQL'
330            CREATE TABLE test (
331                a INTEGER NOT NULL,
332                b INTEGER NOT NULL,
333                c INTEGER GENERATED ALWAYS AS (a + b) STORED
334            )
335            SQL);
336
337        self::assertArrayHasKey('a', $data);
338        self::assertArrayHasKey('b', $data);
339        self::assertArrayNotHasKey('c', $data);
340    }
341
342    #[Test]
343    public function fixtureWithNumericPrecision(): void
344    {
345        $faker = Factory::create();
346        $faker->seed(12345);
347        $provider = new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_PGSQL);
348        $data = $provider->fixture(<<<'SQL'
349            CREATE TABLE test (
350                price NUMERIC(5, 2) NOT NULL,
351                qty INTEGER NOT NULL,
352                rate DOUBLE PRECISION NOT NULL
353            )
354            SQL);
355
356        self::assertIsFloat($data['price']);
357        self::assertGreaterThanOrEqual(-999.99, $data['price']);
358        self::assertLessThanOrEqual(999.99, $data['price']);
359        self::assertIsInt($data['qty']);
360        self::assertIsFloat($data['rate']);
361    }
362
363    #[Test]
364    public function fixtureReproducibleWithSeed(): void
365    {
366        $sql = 'CREATE TABLE test (name TEXT NOT NULL, age INTEGER NOT NULL)';
367
368        $faker1 = Factory::create();
369        $faker1->seed(99999);
370        $provider1 = new FixtureProvider($faker1, dialect: PlatformFactory::DRIVER_PGSQL);
371        $data1 = $provider1->fixture($sql);
372
373        $faker2 = Factory::create();
374        $faker2->seed(99999);
375        $provider2 = new FixtureProvider($faker2, dialect: PlatformFactory::DRIVER_PGSQL);
376        $data2 = $provider2->fixture($sql);
377
378        self::assertSame($data1['name'], $data2['name']);
379        self::assertSame($data1['age'], $data2['age']);
380    }
381}
382