Skip to content

Commit 807ffb6

Browse files
TigrovTigrov
authored
Fix foreign keys (#268)
1 parent aa85945 commit 807ffb6

6 files changed

Lines changed: 96 additions & 23 deletions

File tree

‎CHANGELOG.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22

33
## 1.0.2 under development
44

5-
- no changes in this release.
5+
- Bug #268: Fix foreign keys: support multiple foreign keys referencing to one table and possible null columns for reference (@Tigrov)
66

77
## 1.0.1 July 24, 2023
88

‎src/Schema.php‎

Lines changed: 41 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020
use Yiisoft\Db\Schema\ColumnSchemaInterface;
2121
use Yiisoft\Db\Schema\TableSchemaInterface;
2222

23+
use function array_column;
2324
use function array_merge;
2425
use function count;
2526
use function explode;
@@ -39,7 +40,7 @@
3940
* seq:string,
4041
* table:string,
4142
* from:string,
42-
* to:string,
43+
* to:string|null,
4344
* on_update:string,
4445
* on_delete:string
4546
* }
@@ -204,15 +205,35 @@ protected function loadTableForeignKeys(string $tableName): array
204205
DbArrayHelper::multisort($foreignKeysList, 'seq');
205206

206207
/** @psalm-var GroupedForeignKeyInfo $foreignKeysList */
207-
foreach ($foreignKeysList as $table => $foreignKey) {
208-
$fk = (new ForeignKeyConstraint())
209-
->columnNames(DbArrayHelper::getColumn($foreignKey, 'from'))
210-
->foreignTableName($table)
211-
->foreignColumnNames(DbArrayHelper::getColumn($foreignKey, 'to'))
212-
->onDelete($foreignKey[0]['on_delete'] ?? null)
213-
->onUpdate($foreignKey[0]['on_update'] ?? null);
214-
215-
$result[] = $fk;
208+
foreach ($foreignKeysList as $table => $foreignKeys) {
209+
$foreignKeysById = DbArrayHelper::index($foreignKeys, null, ['id']);
210+
211+
/**
212+
* @psalm-var GroupedForeignKeyInfo $foreignKeysById
213+
* @psalm-var int $id
214+
*/
215+
foreach ($foreignKeysById as $id => $foreignKey) {
216+
if ($foreignKey[0]['to'] === null) {
217+
$primaryKey = $this->getTablePrimaryKey($table);
218+
219+
if ($primaryKey !== null) {
220+
/** @psalm-var string $primaryKeyColumnName */
221+
foreach ((array) $primaryKey->getColumnNames() as $i => $primaryKeyColumnName) {
222+
$foreignKey[$i]['to'] = $primaryKeyColumnName;
223+
}
224+
}
225+
}
226+
227+
$fk = (new ForeignKeyConstraint())
228+
->name((string) $id)
229+
->columnNames(array_column($foreignKey, 'from'))
230+
->foreignTableName($table)
231+
->foreignColumnNames(array_column($foreignKey, 'to'))
232+
->onDelete($foreignKey[0]['on_delete'])
233+
->onUpdate($foreignKey[0]['on_update']);
234+
235+
$result[] = $fk;
236+
}
216237
}
217238

218239
return $result;
@@ -374,19 +395,18 @@ protected function findColumns(TableSchemaInterface $table): bool
374395
*/
375396
protected function findConstraints(TableSchemaInterface $table): void
376397
{
377-
/** @psalm-var ForeignKeyInfo[] $foreignKeysList */
378-
$foreignKeysList = $this->getPragmaForeignKeyList($table->getName());
398+
/** @psalm-var ForeignKeyConstraint[] $foreignKeysList */
399+
$foreignKeysList = $this->getTableForeignKeys($table->getName(), true);
379400

380401
foreach ($foreignKeysList as $foreignKey) {
381-
$id = (int) $foreignKey['id'];
382-
$fk = $table->getForeignKeys();
383-
384-
if (!isset($fk[$id])) {
385-
$table->foreignKey($id, [$foreignKey['table'], $foreignKey['from'] => $foreignKey['to']]);
386-
} else {
387-
/** composite FK */
388-
$table->compositeForeignKey($id, $foreignKey['from'], $foreignKey['to']);
389-
}
402+
/** @var array<string> $columnNames */
403+
$columnNames = (array) $foreignKey->getColumnNames();
404+
$columnNames = array_combine($columnNames, $foreignKey->getForeignColumnNames());
405+
406+
$foreignReference = array_merge([$foreignKey->getForeignTableName()], $columnNames);
407+
408+
/** @psalm-suppress InvalidCast */
409+
$table->foreignKey((string) $foreignKey->getName(), $foreignReference);
390410
}
391411
}
392412

‎src/TableSchema.php‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,9 @@
1111
*/
1212
final class TableSchema extends AbstractTableSchema
1313
{
14+
/**
15+
* @deprecated will be removed in version 2.0.0
16+
*/
1417
public function compositeForeignKey(int $id, string $from, string $to): void
1518
{
1619
$this->foreignKeys[$id][$from] = $to;

‎tests/Provider/SchemaProvider.php‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -405,7 +405,7 @@ public static function constraints(): array
405405
$constraints['2: unique'][2][0]->name(AnyValue::getInstance());
406406
$constraints['2: index'][2][2]->name(AnyValue::getInstance());
407407

408-
$constraints['3: foreign key'][2][0]->name(null);
408+
$constraints['3: foreign key'][2][0]->name('0');
409409
$constraints['3: index'][2] = [];
410410

411411
$constraints['4: primary key'][2]->name(null);

‎tests/SchemaTest.php‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -162,6 +162,33 @@ public function testForeingKey(): void
162162
$this->assertSame('NO ACTION', $foreingKeys[1]->getOnUpdate());
163163
}
164164

165+
public function testMultiForeingKeys(): void
166+
{
167+
$db = $this->getConnection(true);
168+
$schema = $db->getSchema();
169+
$tableSchema = $schema->getTableSchema('foreign_keys_child');
170+
171+
$this->assertNotNull($tableSchema);
172+
173+
$foreignKeys = $tableSchema->getForeignKeys();
174+
175+
$this->assertSame(
176+
[
177+
[
178+
'foreign_keys_parent',
179+
'y' => 'b',
180+
'z' => 'c',
181+
],
182+
[
183+
'foreign_keys_parent',
184+
'x' => 'a',
185+
'y' => 'b',
186+
],
187+
],
188+
$foreignKeys
189+
);
190+
}
191+
165192
/**
166193
* @throws Exception
167194
* @throws InvalidConfigException
@@ -242,6 +269,9 @@ public function testGetTableForeignKeys(): void
242269
$this->assertSame(['C_id_1', 'C_id_2'], $tableForeingKeys[0]->getForeignColumnNames());
243270
$this->assertSame('CASCADE', $tableForeingKeys[0]->getOnDelete());
244271
$this->assertSame('CASCADE', $tableForeingKeys[0]->getOnUpdate());
272+
273+
$tableTwoForeignKeys = $schema->getTableForeignKeys('foreign_keys_child');
274+
$this->assertCount(2, $tableTwoForeignKeys);
245275
}
246276

247277
/**

‎tests/Support/Fixture/sqlite.sql‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,8 @@ DROP TABLE IF EXISTS "T_constraints_1";
2323
DROP TABLE IF EXISTS "T_upsert";
2424
DROP TABLE IF EXISTS "T_upsert_1";
2525
DROP TABLE IF EXISTS "T_constraints_check";
26+
DROP TABLE IF EXISTS "foreign_keys_parent";
27+
DROP TABLE IF EXISTS "foreign_keys_child";
2628

2729
CREATE TABLE "profile" (
2830
id INTEGER NOT NULL,
@@ -289,3 +291,21 @@ CREATE TABLE "T_constraints_check"
289291
"C_check_2" INT NOT NULL CHECK ("C_check_2" > 0),
290292
CONSTRAINT "CN_constraints_check" CHECK ("C_check_1" > "C_check_2")
291293
);
294+
295+
CREATE TABLE foreign_keys_parent
296+
(
297+
a INTEGER,
298+
b INTEGER,
299+
c INTEGER,
300+
PRIMARY KEY(a, b),
301+
UNIQUE (b, c)
302+
);
303+
304+
CREATE TABLE foreign_keys_child
305+
(
306+
x INTEGER,
307+
y INTEGER,
308+
z INTEGER,
309+
FOREIGN KEY(x, y) REFERENCES foreign_keys_parent,
310+
FOREIGN KEY(y, z) REFERENCES foreign_keys_parent(b, c)
311+
);

0 commit comments

Comments
 (0)