Identify foreign keys by reading information_schema

Correctly identify foreign keys by reading information_schema database.

 - Replace regexp parsing of the table "CREATE TABLE" statement.
 - Fix casing issues
 - Fix transposed column names fro target to source tables

Fixes #1186

1 files changed, 91 insertions, 48 deletions

diff --git a/drivers/adodb-mysqli.inc.php b/drivers/adodb-mysqli.inc.php
index 8e194352..23fb15fd 100644
--- a/drivers/adodb-mysqli.inc.php
+++ b/drivers/adodb-mysqli.inc.php
@@ -918,71 +918,114 @@ class ADODB_mysqli extends ADOConnection {
 		global $ADODB_FETCH_MODE;
 
 		if ($ADODB_FETCH_MODE == ADODB_FETCH_ASSOC
-		|| $this->fetchMode == ADODB_FETCH_ASSOC)
+		|| $this->fetchMode == ADODB_FETCH_ASSOC) {
 			$associative = true;
+		}
+		$saveModes = [
+			$ADODB_FETCH_MODE,
+			$this->fetchMode
+		];
+
+		$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
+		if ($this->fetchMode !== false) {
+			$this->setFetchMode(ADODB_FETCH_ASSOC);
+		}
 
-		$savem = $ADODB_FETCH_MODE;
-		$this->setFetchMode(ADODB_FETCH_ASSOC);
+		$referenceSchema = $this->database;
 
 		if ( !empty($owner) ) {
-			$table = "$owner.$table";
+			/*
+			* MySQL is using this for schema, so call metaDatabases to see if
+			* the schema exists
+			*/
+			$alternateDb = array_map('strtolower', $this->MetaDatabases());
+			if (!in_array(strtolower($owner), $alternateDb)) {
+				$ADODB_FETCH_MODE = $saveModes[0];
+				if ($saveModes[1] !== false) {
+					$this->SetFetchMode($saveModes[1]);
+				}
+				return false;
+			}
+			$referenceSchema = $owner;
 		}
 
-		$showCreate = $this->getRow(
-				sprintf('SHOW CREATE TABLE `%s`', $table)
-		);
+		$p1 = $this->param('p1');
+		$p2 = $this->param('p2');
+
+		$bind = [
+			'p1' => $referenceSchema,
+			'p2' => $table
+		];
+
+		$sql = "
+SELECT *
+  FROM information_schema.key_column_usage
+ WHERE referenced_table_schema=$p1
+   AND referenced_table_name IS NOT NULL
+   AND table_name = $p2
+   ORDER BY referenced_table_name,constraint_name, position_in_unique_constraint";
+
+        $constraintList = $this->getAll($sql, $bind);
+
+		$ADODB_FETCH_MODE = $saveModes[0];
+        if ($saveModes[1] !== false) {
+            $this->SetFetchMode($saveModes[1]);
+        }
+
+        if (!$constraintList || count($constraintList) == 0) {
+            return false;
+        }
+
+        $sortKeys    = [];
+
+        foreach ($constraintList as $element) {
 
-		if ( !$showCreate || !is_array($showCreate) ) {
 			/*
-			* Invalid table or owner provided
+			* Standardize the returned data keys
 			*/
-			$this->setFetchMode($savem);
-			return false;
-		}
+			$element = array_change_key_case($element, CASE_UPPER);
 
-		$a_create_table = array_change_key_case($showCreate, CASE_UPPER);
+			/*
+			* Change the values to match the requested return type
+			*/
+            if ($upper) {
+                $element = array_map('strtoupper', $element);
+            } else {
+                $element = array_map('strtolower', $element);
+            }
 
-		$this->setFetchMode($savem);
+            $id = $element['CONSTRAINT_NAME'];
 
-		$create_sql = $a_create_table["CREATE TABLE"] ?? $a_create_table["CREATE VIEW"];
+            if (!array_key_exists($id, $sortKeys)) {
+                $sortKeys[$id] = new \stdClass();
+                $sortKeys[$id]->tableName = $element['REFERENCED_TABLE_NAME'];
+                $sortKeys[$id]->assocKeys = [];
+                $sortKeys[$id]->numKeys   = [];
+            }
 
-		$matches = array();
+            $sortKeys[$id]->assocKeys[$element['COLUMN_NAME']] = $element['REFERENCED_COLUMN_NAME'];
+            $sortKeys[$id]->numKeys[] = sprintf(
+                '%s=%s',
+                $element['COLUMN_NAME'],
+                $element['REFERENCED_COLUMN_NAME']
+            );
+        }
 
-		if (!preg_match_all(
-			"/FOREIGN KEY \(`(.*?)`\) REFERENCES `(.*?)` \(`(.*?)`\)/", 
-			$create_sql, 
-			$matches
-			)
-		) {
-				return false;
-		}
-		
-		$foreign_keys = array();
-		$num_keys = count($matches[0]);
-		for ( $i = 0; $i < $num_keys; $i ++ ) {
-			$my_field  = explode('`, `', $matches[1][$i]);
-			$ref_table = $matches[2][$i];
-			$ref_field = explode('`, `', $matches[3][$i]);
+        /*
+		* Now the array is built, pick off the right elements
+		*/
+		$foreignKeys = [];
+        foreach ($sortKeys as $sortObject) {
+            if ($associative) {
+                $foreignKeys[$sortObject->tableName] = $sortObject->assocKeys;
+            } else {
+                $foreignKeys[$sortObject->tableName] = $sortObject->numKeys;
+            }
+        }
 
-			if ( $upper ) {
-				$ref_table = strtoupper($ref_table);
-			}
 
-			// see https://sourceforge.net/p/adodb/bugs/100/
-			if (!isset($foreign_keys[$ref_table])) {
-				$foreign_keys[$ref_table] = array();
-			}
-			$num_fields = count($my_field);
-			for ( $j = 0; $j < $num_fields; $j ++ ) {
-				if ( $associative ) {
-					$foreign_keys[$ref_table][$ref_field[$j]] = $my_field[$j];
-				} else {
-					$foreign_keys[$ref_table][] = $my_field[$j] . '=' . $ref_field[$j];
-				}
-			}
-		}
+        return $foreignKeys;
 
-		return $foreign_keys;
 	}
 
 	/**