drivers: getForeignKeys() works with multi-column foreign keys

nette · kratkyzobak · May 10, 2024 · May 10, 2024 · May 13, 2024 · May 10, 2024
commit 860a0332c5802bc4c67638ea5b10fd4df39bd73a
diff --git a/src/Database/Drivers/MsSqlDriver.php b/src/Database/Drivers/MsSqlDriver.php
@@ -182,11 +182,12 @@ public function getForeignKeys(string $table): array
 				tab1.name = {$this->pdo->quote($table_name)}
 			X;
 
-		foreach ($this->pdo->query($query) as $id => $row) {
-			$keys[$id]['name'] = $row['fk_name'];
-			$keys[$id]['local'] = $row['column'];
+		foreach ($this->pdo->query($query) as $row) {
+			$id = $row['fk_name'];
+			$keys[$id]['name'] = $id;
+			$keys[$id]['local'][] = $row['column'];
 			$keys[$id]['table'] = $table_schema . '.' . $row['referenced_table'];
-			$keys[$id]['foreign'] = $row['referenced_column'];
+			$keys[$id]['foreign'][] = $row['referenced_column'];
 		}
 
 		return array_values($keys);

diff --git a/src/Database/Drivers/MySqlDriver.php b/src/Database/Drivers/MySqlDriver.php
@@ -199,11 +199,12 @@ public function getForeignKeys(string $table): array
 			WHERE TABLE_SCHEMA = DATABASE()
 			  AND REFERENCED_TABLE_NAME IS NOT NULL
 			  AND TABLE_NAME = {$this->pdo->quote($table)}
-			X) as $id => $row) {
-			$keys[$id]['name'] = $row['CONSTRAINT_NAME'];
-			$keys[$id]['local'] = $row['COLUMN_NAME'];
+			X) as $row) {
+			$id = $row['CONSTRAINT_NAME'];
+			$keys[$id]['name'] = $id;
+			$keys[$id]['local'][] = $row['COLUMN_NAME'];
 			$keys[$id]['table'] = $row['REFERENCED_TABLE_NAME'];
-			$keys[$id]['foreign'] = $row['REFERENCED_COLUMN_NAME'];
+			$keys[$id]['foreign'][] = $row['REFERENCED_COLUMN_NAME'];
 		}
 
 		return array_values($keys);

diff --git a/src/Database/Drivers/PgSqlDriver.php b/src/Database/Drivers/PgSqlDriver.php
@@ -184,7 +184,8 @@ public function getIndexes(string $table): array
 	public function getForeignKeys(string $table): array
 	{
 		/* Doesn't work with multi-column foreign keys */
-		return $this->pdo->query(<<<X
+		$keys = [];
+		foreach ($this->pdo->query(<<<X
 			SELECT
 				co.conname::varchar AS name,
 				al.attname::varchar AS local,
@@ -201,7 +202,15 @@ public function getForeignKeys(string $table): array
 				co.contype = 'f'
 				AND cl.oid = {$this->pdo->quote($this->delimiteFQN($table))}::regclass
 				AND nf.nspname = ANY (pg_catalog.current_schemas(FALSE))
-			X)->fetchAll(\PDO::FETCH_ASSOC);
+			X) as $row) {
+			$id = $row['name'];
+			$keys[$id]['name'] = $id;
+			$keys[$id]['local'][] = $row['local'];
+			$keys[$id]['table'] = $row['table'];
+			$keys[$id]['foreign'][] = $row['foreign'];
+		}
+
+		return array_values($keys);
 	}
 
 

diff --git a/src/Database/Drivers/SqliteDriver.php b/src/Database/Drivers/SqliteDriver.php
@@ -221,9 +221,12 @@ public function getForeignKeys(string $table): array
 		foreach ($this->pdo->query("PRAGMA foreign_key_list({$this->delimite($table)})") as $row) {
 			$id = $row['id'];
 			$keys[$id]['name'] = $id;
-			$keys[$id]['local'] = $row['from'];
+			$keys[$id]['local'][] = $row['from'];
 			$keys[$id]['table'] = $row['table'];
-			$keys[$id]['foreign'] = $row['to'];
+			$keys[$id]['foreign'][] = $row['to'];
+			if ($keys[$id]['foreign'][0] == null) {
+				$keys[$id]['foreign'] = [];
+			}
 		}
 
 		return array_values($keys);

diff --git a/src/Database/Drivers/SqlsrvDriver.php b/src/Database/Drivers/SqlsrvDriver.php
@@ -209,7 +209,11 @@ public function getForeignKeys(string $table): array
 			WHERE
 				tl.name = {$this->pdo->quote($table)}
 			X, \PDO::FETCH_ASSOC) as $row) {
-			$keys[$row['name']] = $row;
+			$id = $row['name'];
+			$keys[$id]['name'] = $id;
+			$keys[$id]['local'][] = $row['local'];
+			$keys[$id]['table'] = $row['table'];
+			$keys[$id]['foreign'][] = $row['column'];
 		}
 
 		return array_values($keys);

diff --git a/src/Database/Structure.php b/src/Database/Structure.php
@@ -235,17 +235,11 @@ protected function analyzeForeignKeys(array &$structure, string $table): void
 
 		$foreignKeys = $this->connection->getDriver()->getForeignKeys($table);
 
-		$fksColumnsCounts = [];
-		foreach ($foreignKeys as $foreignKey) {
-			$tmp = &$fksColumnsCounts[$foreignKey['name']];
-			$tmp++;
-		}
-
-		usort($foreignKeys, fn($a, $b): int => $fksColumnsCounts[$b['name']] <=> $fksColumnsCounts[$a['name']]);
+		usort($foreignKeys, fn($a, $b): int => count($b['local']) <=> count($a['local']));
 
 		foreach ($foreignKeys as $row) {
-			$structure['belongsTo'][$lowerTable][$row['local']] = $row['table'];
-			$structure['hasMany'][strtolower($row['table'])][$table][] = $row['local'];
+			$structure['belongsTo'][$lowerTable][$row['local'][0]] = $row['table'];
+			$structure['hasMany'][strtolower($row['table'])][$table][] = $row['local'][0];
 		}
 
 		if (isset($structure['belongsTo'][$lowerTable])) {

diff --git a/tests/Database/Reflection.postgre.phpt b/tests/Database/Reflection.postgre.phpt
@@ -63,9 +63,9 @@ test('Tables in schema', function () use ($connection) {
 	$foreign = $driver->getForeignKeys('one.slave');
 	Assert::same([
 		'name' => 'one_slave_fk',
-		'local' => 'one_id',
+		'local' => ['one_id'],
 		'table' => 'one.master',
-		'foreign' => 'one_id',
+		'foreign' => ['one_id'],
 	], (array) $foreign[0]);
 
 

diff --git a/tests/Database/Structure.phpt b/tests/Database/Structure.phpt
@@ -74,13 +74,13 @@ class StructureTestCase extends TestCase
 		$this->connection->shouldReceive('getDriver')->times(4)->andReturn($this->driver);
 		$this->driver->shouldReceive('getForeignKeys')->with('authors')->once()->andReturn([]);
 		$this->driver->shouldReceive('getForeignKeys')->with('Books')->once()->andReturn([
-			['local' => 'author_id', 'table' => 'authors', 'foreign' => 'id', 'name' => 'authors_fk1'],
-			['local' => 'translator_id', 'table' => 'authors', 'foreign' => 'id', 'name' => 'authors_fk2'],
+			['local' => ['author_id'], 'table' => 'authors', 'foreign' => ['id'], 'name' => 'authors_fk1'],
+			['local' => ['translator_id'], 'table' => 'authors', 'foreign' => ['id'], 'name' => 'authors_fk2'],
 		]);
 		$this->driver->shouldReceive('getForeignKeys')->with('tags')->once()->andReturn([]);
 		$this->driver->shouldReceive('getForeignKeys')->with('books_x_tags')->once()->andReturn([
-			['local' => 'book_id', 'table' => 'Books', 'foreign' => 'id', 'name' => 'books_x_tags_fk1'],
-			['local' => 'tag_id', 'table' => 'tags', 'foreign' => 'id', 'name' => 'books_x_tags_fk2'],
+			['local' => ['book_id'], 'table' => 'Books', 'foreign' => ['id'], 'name' => 'books_x_tags_fk1'],
+			['local' => ['tag_id'], 'table' => 'tags', 'foreign' => ['id'], 'name' => 'books_x_tags_fk2'],
 		]);
 
 		$this->structure = new StructureMock($this->connection, $this->storage);

diff --git a/tests/Database/Structure.schemas.phpt b/tests/Database/Structure.schemas.phpt
@@ -60,8 +60,8 @@ class StructureSchemasTestCase extends TestCase
 		$this->connection->shouldReceive('getDriver')->times(2)->andReturn($this->driver);
 		$this->driver->shouldReceive('getForeignKeys')->with('authors.authors')->once()->andReturn([]);
 		$this->driver->shouldReceive('getForeignKeys')->with('books.books')->once()->andReturn([
-			['local' => 'author_id', 'table' => 'authors.authors', 'foreign' => 'id', 'name' => 'authors_authors_fk1'],
-			['local' => 'translator_id', 'table' => 'authors.authors', 'foreign' => 'id', 'name' => 'authors_authors_fk2'],
+			['local' => ['author_id'], 'table' => 'authors.authors', 'foreign' => ['id'], 'name' => 'authors_authors_fk1'],
+			['local' => ['translator_id'], 'table' => 'authors.authors', 'foreign' => ['id'], 'name' => 'authors_authors_fk2'],
 		]);
 
 		$this->structure = new StructureMock($this->connection, $this->storage);