packages/sql-fixture/tests/Integration/Sqlite/SqliteIntegrationTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\Sqlite;
6
7use Faker\Factory;
8use PDO;
9use PHPUnit\Framework\Attributes\CoversClass;
10use PHPUnit\Framework\Attributes\Test;
11use PHPUnit\Framework\Attributes\UsesClass;
12use PHPUnit\Framework\TestCase;
13use SqlFixture\Hydrator\ReflectionHydrator;
14use SqlFixture\Platform\Sqlite\SqliteSchemaFetcher;
15use SqlFixture\Platform\Sqlite\SqliteSchemaParser;
16use SqlFixture\Platform\Sqlite\SqliteTypeMapper;
17use SqlFixture\Provider\DatabaseFixtureProvider;
18use SqlFixture\Provider\FixtureGenerator;
19use SqlFixture\Provider\FixtureProvider;
20use SqlFixture\Provider\PlatformFactory;
21use SqlFixture\Schema\ColumnDefinition;
22use SqlFixture\Schema\TableSchema;
23
24#[CoversClass(DatabaseFixtureProvider::class)]
25#[UsesClass(FixtureProvider::class)]
26#[UsesClass(FixtureGenerator::class)]
27#[UsesClass(PlatformFactory::class)]
28#[UsesClass(SqliteSchemaFetcher::class)]
29#[UsesClass(SqliteSchemaParser::class)]
30#[UsesClass(SqliteTypeMapper::class)]
31#[UsesClass(ColumnDefinition::class)]
32#[UsesClass(TableSchema::class)]
33#[UsesClass(ReflectionHydrator::class)]
34#[CoversClass(\SqlFixture\Provider\DatabaseSchemaCache::class)]
35#[UsesClass(\SqlFixture\Fixture\FixtureSet::class)]
36#[UsesClass(\SqlFixture\Fixture\GenerationRun::class)]
37#[UsesClass(\SqlFixture\Fixture\OverrideRows::class)]
38#[UsesClass(\SqlFixture\Fixture\PlanGenerator::class)]
39#[UsesClass(\SqlFixture\Fixture\PlanSchemaException::class)]
40#[UsesClass(\SqlFixture\Fixture\PlanSchemaValidator::class)]
41#[UsesClass(\SqlFixture\Fixture\RowSpec::class)]
42#[UsesClass(\SqlFixture\Fixture\TableOverrides::class)]
43#[UsesClass(\SqlFixture\Hydrator\HydrationException::class)]
44#[UsesClass(\SqlFixture\Hydrator\HydratorInterface::class)]
45#[UsesClass(\SqlFixture\Plan\ColumnRef::class)]
46#[UsesClass(\SqlFixture\Plan\FixturePlan::class)]
47#[UsesClass(\SqlFixture\Plan\PlanParser::class)]
48#[UsesClass(\SqlFixture\Plan\PlanPrinter::class)]
49#[UsesClass(\SqlFixture\Plan\PlanSyntaxException::class)]
50#[UsesClass(\SqlFixture\Plan\Relation::class)]
51#[UsesClass(\SqlFixture\Plan\RelationKind::class)]
52#[UsesClass(\SqlFixture\Plan\RelationSide::class)]
53#[UsesClass(\SqlFixture\Platform\MySql\MySqlSchemaFetcher::class)]
54#[UsesClass(\SqlFixture\Platform\MySql\MySqlSchemaParser::class)]
55#[UsesClass(\SqlFixture\Platform\MySql\MySqlTypeMapper::class)]
56#[UsesClass(\SqlFixture\Platform\PostgreSql\PostgreSqlSchemaFetcher::class)]
57#[UsesClass(\SqlFixture\Platform\PostgreSql\PostgreSqlSchemaParser::class)]
58#[UsesClass(\SqlFixture\Platform\PostgreSql\PostgreSqlTypeMapper::class)]
59#[UsesClass(\SqlFixture\Schema\SchemaFetcherInterface::class)]
60#[UsesClass(\SqlFixture\Schema\SchemaNotFoundException::class)]
61#[UsesClass(\SqlFixture\Schema\SchemaParseException::class)]
62#[UsesClass(\SqlFixture\Schema\SchemaParserInterface::class)]
63#[UsesClass(\SqlFixture\Schema\SchemaResolverInterface::class)]
64#[UsesClass(\SqlFixture\Schema\StaticSchemaResolver::class)]
65#[UsesClass(\SqlFixture\Schema\TableIdentifier::class)]
66#[UsesClass(\SqlFixture\TypeMapper\TypeMapperInterface::class)]
67#[UsesClass(\SqlFixture\Fixture\Generation\ConnectedTables::class)]
68#[UsesClass(\SqlFixture\Fixture\Generation\OverrideSpecs::class)]
69#[UsesClass(\SqlFixture\Fixture\Generation\RelationCounts::class)]
70#[UsesClass(\SqlFixture\Fixture\Generation\RelationProjection::class)]
71#[UsesClass(\SqlFixture\Fixture\Generation\RowMaterializer::class)]
72#[UsesClass(\SqlFixture\Fixture\RowGeneration::class)]
73#[UsesClass(\SqlFixture\Fixture\Validation\EndpointValidator::class)]
74#[UsesClass(\SqlFixture\Fixture\Validation\OverrideValidator::class)]
75#[UsesClass(\SqlFixture\Hydrator\Reflection\ConstructorHydration::class)]
76#[UsesClass(\SqlFixture\Hydrator\Reflection\PropertyHydration::class)]
77#[UsesClass(\SqlFixture\Hydrator\Reflection\PropertyNames::class)]
78#[UsesClass(\SqlFixture\Hydrator\Reflection\ValueConversion::class)]
79#[UsesClass(\SqlFixture\Fixture\Exception\InvalidOverrideException::class)]
80#[UsesClass(\SqlFixture\Plan\Parsing\PlanStatements::class)]
81#[UsesClass(\SqlFixture\Plan\Parsing\RelationCursor::class)]
82#[UsesClass(\SqlFixture\Plan\Parsing\RelationReader::class)]
83#[UsesClass(\SqlFixture\Plan\PlanStructureException::class)]
84#[UsesClass(\SqlFixture\Plan\Printing\PlanTables::class)]
85#[UsesClass(\SqlFixture\Plan\Printing\RelationGroups::class)]
86#[UsesClass(\SqlFixture\Plan\Printing\StatementPrinter::class)]
87#[UsesClass(\SqlFixture\Plan\Validation\PlanValidation::class)]
88#[UsesClass(\SqlFixture\Plan\Validation\TableName::class)]
89#[UsesClass(\SqlFixture\Platform\MySql\Schema\ColumnParser::class)]
90#[UsesClass(\SqlFixture\Platform\MySql\Schema\CreateTableQuery::class)]
91#[UsesClass(\SqlFixture\Platform\MySql\Schema\DefaultExpression::class)]
92#[UsesClass(\SqlFixture\Platform\MySql\Schema\DefinitionIntegrity::class)]
93#[UsesClass(\SqlFixture\Platform\MySql\Schema\IdentifierQuoter::class)]
94#[UsesClass(\SqlFixture\Platform\MySql\Schema\TableDefinition::class)]
95#[UsesClass(\SqlFixture\Platform\MySql\Schema\TypeParameters::class)]
96#[UsesClass(\SqlFixture\Platform\MySql\Value\ColumnGenerator::class)]
97#[UsesClass(\SqlFixture\Platform\MySql\Value\DecimalGenerator::class)]
98#[UsesClass(\SqlFixture\Platform\MySql\Value\GeometryGenerator::class)]
99#[UsesClass(\SqlFixture\Platform\MySql\Value\IntegerGenerator::class)]
100#[UsesClass(\SqlFixture\Platform\MySql\Value\NumericGenerator::class)]
101#[UsesClass(\SqlFixture\Platform\MySql\Value\SpatialGenerator::class)]
102#[UsesClass(\SqlFixture\Platform\MySql\Value\StringGenerator::class)]
103#[UsesClass(\SqlFixture\Platform\MySql\Value\TextGenerator::class)]
104#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\CatalogColumn::class)]
105#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\CatalogDdl::class)]
106#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\CatalogQuery::class)]
107#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\CatalogSchema::class)]
108#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\ColumnParser::class)]
109#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\DefaultExpression::class)]
110#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\DefinitionList::class)]
111#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\TableSyntax::class)]
112#[UsesClass(\SqlFixture\Platform\PostgreSql\Schema\TypeDeclaration::class)]
113#[UsesClass(\SqlFixture\Platform\PostgreSql\Value\ColumnGenerator::class)]
114#[UsesClass(\SqlFixture\Platform\PostgreSql\Value\DecimalGenerator::class)]
115#[UsesClass(\SqlFixture\Platform\PostgreSql\Value\NumericGenerator::class)]
116#[UsesClass(\SqlFixture\Platform\PostgreSql\Value\StringGenerator::class)]
117#[UsesClass(\SqlFixture\Platform\PostgreSql\Value\StructuredGenerator::class)]
118#[UsesClass(\SqlFixture\Platform\PostgreSql\Value\TemporalGenerator::class)]
119#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\ColumnParser::class)]
120#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\CreateTableQuery::class)]
121#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\DefaultExpression::class)]
122#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\DefinitionList::class)]
123#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\PragmaColumn::class)]
124#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\PragmaSchema::class)]
125#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\TableSyntax::class)]
126#[UsesClass(\SqlFixture\Platform\Sqlite\Schema\TypeDeclaration::class)]
127#[UsesClass(\SqlFixture\Platform\Sqlite\Value\ColumnGenerator::class)]
128#[UsesClass(\SqlFixture\Platform\Sqlite\Value\TypeAffinity::class)]
129#[UsesClass(\SqlFixture\Provider\SqlSchemaProvider::class)]
130#[UsesClass(\SqlFixture\Schema\DefinitionSegments::class)]
131#[UsesClass(\SqlFixture\Schema\TypeShape::class)]
132#[UsesClass(\SqlFixture\TypeMapper\ParagraphGenerator::class)]
133#[UsesClass(\SqlFixture\Platform\MySql\Schema\TableDefinitionInput::class)]
134#[UsesClass(\SqlFixture\Hydrator\Exception\ClassNotFoundException::class)]
135#[UsesClass(\SqlFixture\Hydrator\Exception\MissingConstructorArgumentException::class)]
136#[UsesClass(\SqlFixture\Fixture\Exception\UnknownOverrideColumnException::class)]
137#[UsesClass(\SqlFixture\Fixture\Exception\NullOverrideException::class)]
138#[UsesClass(\SqlFixture\Fixture\Exception\GeneratedColumnOverrideException::class)]
139#[UsesClass(\SqlFixture\Fixture\Exception\GeneratedColumnReferenceException::class)]
140#[UsesClass(\SqlFixture\Fixture\Exception\MissingRelationValueException::class)]
141#[UsesClass(\SqlFixture\Fixture\Exception\UnknownPlanColumnException::class)]
142#[UsesClass(\SqlFixture\Plan\Exception\EmptyPlanException::class)]
143#[UsesClass(\SqlFixture\Plan\Exception\EmptyTableNameException::class)]
144#[UsesClass(\SqlFixture\Plan\Exception\MissingEndpointColumnsException::class)]
145#[UsesClass(\SqlFixture\Plan\Exception\InvalidTableNameException::class)]
146#[UsesClass(\SqlFixture\Plan\Exception\UnbalancedBracketsException::class)]
147#[UsesClass(\SqlFixture\Plan\Exception\UnexpectedPlanTokenException::class)]
148#[UsesClass(\SqlFixture\Plan\Exception\UnsupportedManyToManyException::class)]
149#[UsesClass(\SqlFixture\Plan\Exception\CompositeArityMismatchException::class)]
150#[UsesClass(\SqlFixture\Plan\Exception\DuplicateColumnBindingException::class)]
151#[UsesClass(\SqlFixture\Plan\Exception\CyclicDependencyException::class)]
152#[UsesClass(\SqlFixture\Plan\Exception\UnboundedSelfReferenceException::class)]
153#[UsesClass(\SqlFixture\Schema\Exception\InvalidSqlException::class)]
154#[UsesClass(\SqlFixture\Schema\Exception\ExpectedCreateTableException::class)]
155#[UsesClass(\SqlFixture\Schema\Exception\MissingColumnDefinitionsException::class)]
156#[UsesClass(\SqlFixture\TypeMapper\IntegerRange::class)]
157#[UsesClass(\SqlFixture\TypeMapper\IntegerWidth::class)]
158#[UsesClass(\SqlFixture\TypeMapper\DecimalRange::class)]
159#[UsesClass(\SqlFixture\Hydrator\Reflection\ConversionTarget::class)]
160#[UsesClass(\SqlFixture\Fixture\Choice\InverseRelations::class)]
161#[UsesClass(\SqlFixture\Fixture\Choice\ResolvedRow::class)]
162#[UsesClass(\SqlFixture\Fixture\Choice\RowChoices::class)]
163#[UsesClass(\SqlFixture\Plan\Choice\ChoiceValidation::class)]
164#[UsesClass(\SqlFixture\Plan\Choice\PlanContents::class)]
165#[UsesClass(\SqlFixture\Fixture\Generation\RowBindings::class)]
166#[UsesClass(\SqlFixture\Fixture\Generation\RecursiveRelationException::class)]
167#[UsesClass(\SqlFixture\Fixture\Generation\RelationValueException::class)]
168#[UsesClass(\SqlFixture\Fixture\RowGenerator::class)]
169final class SqliteIntegrationTest extends TestCase
170{
171    #[Test]
172    public function fixtureProviderWithSqliteDialect(): void
173    {
174        $faker = Factory::create();
175        $faker->seed(12345);
176        $provider = new FixtureProvider(
177            $faker,
178            dialect: PlatformFactory::DRIVER_SQLITE
179        );
180
181        $fixture = $provider->fixture(
182            'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT)'
183        );
184        self::assertArrayHasKey('name', $fixture);
185        self::assertArrayHasKey('email', $fixture);
186        self::assertIsString($fixture['name']);
187    }
188
189    #[Test]
190    public function databaseFixtureProviderWithSqlite(): void
191    {
192        $pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
193        $pdo->exec(<<<'SQL'
194            CREATE TABLE users (
195                id INTEGER PRIMARY KEY AUTOINCREMENT,
196                name TEXT NOT NULL,
197                email TEXT,
198                age INTEGER,
199                balance REAL,
200                created_at TEXT DEFAULT CURRENT_TIMESTAMP
201            )
202            SQL);
203
204        $faker = Factory::create();
205        $faker->seed(12345);
206        $provider = new DatabaseFixtureProvider($faker, $pdo);
207
208        self::assertSame(PlatformFactory::DRIVER_SQLITE, $provider->getDriver());
209
210        $fixture = $provider->fixture('users');
211        self::assertArrayNotHasKey('id', $fixture);
212        self::assertArrayHasKey('name', $fixture);
213        self::assertIsString($fixture['name']);
214    }
215
216    #[Test]
217    public function insertAndSelectFixture(): void
218    {
219        $pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
220        $pdo->exec(<<<'SQL'
221            CREATE TABLE products (
222                id INTEGER PRIMARY KEY AUTOINCREMENT,
223                name TEXT NOT NULL,
224                price REAL NOT NULL,
225                quantity INTEGER DEFAULT 0
226            )
227            SQL);
228
229        $faker = Factory::create();
230        $faker->seed(12345);
231        $provider = new DatabaseFixtureProvider($faker, $pdo);
232
233        $fixture = $provider->fixture('products');
234
235        $stmt = $pdo->prepare(
236            'INSERT INTO products (name, price, quantity) VALUES (:name, :price, :quantity)'
237        );
238        $stmt->execute($fixture);
239
240        $id = (int) $pdo->lastInsertId();
241        $stmt = $pdo->prepare('SELECT * FROM products WHERE id = ?');
242        $stmt->execute([$id]);
243        $row = $stmt->fetch(PDO::FETCH_ASSOC);
244
245        self::assertNotFalse($row);
246        self::assertIsArray($row);
247        self::assertSame($fixture['name'], $row['name']);
248        self::assertIsFloat($fixture['price']);
249        self::assertIsNumeric($row['price']);
250        self::assertEqualsWithDelta($fixture['price'], (float) $row['price'], 0.001);
251    }
252
253    #[Test]
254    public function fixtureWithOverrides(): void
255    {
256        $pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
257        $pdo->exec(<<<'SQL'
258            CREATE TABLE users (
259                id INTEGER PRIMARY KEY,
260                name TEXT NOT NULL,
261                email TEXT NOT NULL
262            )
263            SQL);
264
265        $faker = Factory::create();
266        $provider = new DatabaseFixtureProvider($faker, $pdo);
267
268        $fixture = $provider->fixture('users', [
269            'name' => 'John Doe',
270            'email' => 'john@example.com',
271        ]);
272
273        self::assertSame('John Doe', $fixture['name']);
274        self::assertSame('john@example.com', $fixture['email']);
275    }
276
277    #[Test]
278    public function fixtureWithHydration(): void
279    {
280        $target = new class (0, '', '') {
281            public function __construct(
282                public int $id,
283                public string $name,
284                public string $email,
285            ) {
286            }
287        };
288        $pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
289        $pdo->exec(<<<'SQL'
290            CREATE TABLE users (
291                id INTEGER PRIMARY KEY,
292                name TEXT NOT NULL,
293                email TEXT NOT NULL
294            )
295            SQL);
296
297        $faker = Factory::create();
298        $provider = new DatabaseFixtureProvider($faker, $pdo);
299
300        $user = $provider->fixture(
301            'users',
302            ['id' => 1],
303            $target::class
304        );
305
306        self::assertSame(1, $user->id);
307        self::assertNotEmpty($user->name);
308        self::assertNotEmpty($user->email);
309    }
310
311    #[Test]
312    public function fixtureProviderDialectOverride(): void
313    {
314        $faker = Factory::create();
315        $faker->seed(12345);
316
317        $provider = new FixtureProvider($faker);
318
319        $fixture = $provider->fixture(
320            'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)',
321            [],
322            null,
323            PlatformFactory::DRIVER_SQLITE
324        );
325        self::assertArrayHasKey('name', $fixture);
326    }
327
328    #[Test]
329    public function allSqliteTypesFixture(): void
330    {
331        $pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
332        $pdo->exec(<<<'SQL'
333            CREATE TABLE all_types (
334                col_integer INTEGER NOT NULL,
335                col_int INT NOT NULL,
336                col_tinyint TINYINT NOT NULL,
337                col_smallint SMALLINT NOT NULL,
338                col_mediumint MEDIUMINT NOT NULL,
339                col_bigint BIGINT NOT NULL,
340                col_text TEXT NOT NULL,
341                col_varchar VARCHAR(100) NOT NULL,
342                col_char CHAR(10) NOT NULL,
343                col_real REAL NOT NULL,
344                col_float FLOAT NOT NULL,
345                col_double DOUBLE NOT NULL,
346                col_decimal DECIMAL(10, 2) NOT NULL,
347                col_blob BLOB NOT NULL,
348                col_boolean BOOLEAN NOT NULL,
349                col_date DATE NOT NULL,
350                col_time TIME NOT NULL,
351                col_datetime DATETIME NOT NULL
352            )
353            SQL);
354
355        $faker = Factory::create();
356        $faker->seed(12345);
357        $provider = new DatabaseFixtureProvider($faker, $pdo);
358
359        $fixture = $provider->fixture('all_types');
360
361        self::assertIsInt($fixture['col_integer']);
362        self::assertIsInt($fixture['col_int']);
363        self::assertIsInt($fixture['col_tinyint']);
364        self::assertIsInt($fixture['col_smallint']);
365        self::assertIsInt($fixture['col_mediumint']);
366        self::assertIsInt($fixture['col_bigint']);
367        self::assertIsString($fixture['col_text']);
368        self::assertIsString($fixture['col_varchar']);
369        self::assertIsString($fixture['col_char']);
370        self::assertIsFloat($fixture['col_real']);
371        self::assertIsFloat($fixture['col_float']);
372        self::assertIsFloat($fixture['col_double']);
373        self::assertIsFloat($fixture['col_decimal']);
374        self::assertIsString($fixture['col_blob']);
375        self::assertContains($fixture['col_boolean'], [0, 1]);
376        $colDate = $fixture['col_date'];
377        self::assertIsString($colDate);
378        self::assertMatchesRegularExpression('/^\d{4}-\d{2}-\d{2}$/', $colDate);
379        $colTime = $fixture['col_time'];
380        self::assertIsString($colTime);
381        self::assertMatchesRegularExpression('/^\d{2}:\d{2}:\d{2}$/', $colTime);
382        $colDatetime = $fixture['col_datetime'];
383        self::assertIsString($colDatetime);
384        self::assertMatchesRegularExpression('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $colDatetime);
385    }
386}
387