/**
* Return the DB name of the key described in XMLDBKey, if it exists.
*
* @uses, $db
* @param XMLDBTable the table to be searched
* @param XMLDBKey the key to be searched
* @return string key name of false
*/
function find_key_name(XMLDBTable $table, XMLDBKey $key)
{
/* @var $db ADOConnection */
global $CFG, $db;
// Do this function silently to avoid output during the install/upgrade process
$olddbdebug = $db->debug;
$db->debug = false;
if (!table_exists($table)) {
$db->debug = $olddbdebug;
return false;
}
$tablename = get_config('dbprefix') . $table->getName();
$dbname = get_config('dbname');
// TODO: upstream this to ADODB?
// Postgres puts the database name in the "catalog" field. Mysql puts it in "schema"
if (is_postgres()) {
$dbfield = 'catalog';
// The query to find all the columns for a foreign key constraint
$fkcolsql = "\n SELECT\n ku.column_name,\n ccu.column_name AS refcolumn_name\n FROM\n information_schema.key_column_usage ku\n INNER JOIN information_schema.constraint_column_usage ccu\n ON ku.constraint_name = ccu.constraint_name\n AND ccu.constraint_schema = ku.constraint_schema\n AND ccu.constraint_catalog = ku.constraint_catalog\n AND ccu.table_catalog = ku.constraint_catalog\n AND ccu.table_schema = ku.constraint_schema\n WHERE\n ku.constraint_catalog = ?\n AND ku.constraint_name = ?\n AND ku.table_name = ?\n AND ku.table_catalog = ?\n AND ccu.table_name = ?\n ORDER BY ku.ordinal_position, ku.position_in_unique_constraint\n ";
} else {
$dbfield = 'schema';
// The query to find all the columns for a foreign key constraint
$fkcolsql = '
SELECT
ku.column_name,
ku.referenced_column_name AS refcolumn_name
FROM information_schema.key_column_usage ku
WHERE
ku.constraint_schema = ?
AND ku.constraint_name = ?
AND ku.table_name = ?
AND ku.table_schema = ?
AND ku.referenced_table_name = ?
ORDER BY ku.ordinal_position, ku.position_in_unique_constraint
';
}
// Foreign keys have slightly different logic than primary and unique
$isfk = $key->getType() == XMLDB_KEY_FOREIGN || $key->getType() == XMLDB_KEY_FOREIGN_UNIQUE;
$fields = $key->getFields();
if ($isfk) {
$reffields = $key->getRefFields();
$reftable = get_config('dbprefix') . $key->getRefTable();
// If the XMLDBKey is a foreign key without a ref table, or non-matching fields & ref fields,
// then it's an invalid XMLDBKey and we know it won't match
if (!$key->getRefTable() || count($fields) != count($reffields)) {
log_debug('Invalid XMLDBKey foreign key passed to find_key_name()');
$db->debug = $olddbdebug;
return false;
}
}
// Get the main record for the constraint
$sql = "\n SELECT tc.constraint_name\n FROM\n information_schema.table_constraints tc\n WHERE\n tc.table_name = ?\n AND tc.table_{$dbfield} = ?\n AND tc.constraint_{$dbfield} = ?\n AND tc.constraint_type = ?\n ";
$keytypes = array(XMLDB_KEY_PRIMARY => 'PRIMARY KEY', XMLDB_KEY_UNIQUE => 'UNIQUE', XMLDB_KEY_FOREIGN => 'FOREIGN KEY', XMLDB_KEY_FOREIGN_UNIQUE => 'FOREIGN KEY');
$params = array($tablename, $dbname, $dbname, $keytypes[$key->getType()]);
$constraintrec = get_records_sql_array($sql, $params);
// No constraints of the correct type on this table
if (!$constraintrec) {
$db->debug = $olddbdebug;
return false;
}
// Check each constraint to see if it has the right columns
foreach ($constraintrec as $c) {
if ($isfk) {
$colsql = $fkcolsql;
$colparams = array($dbname, $c->constraint_name, $tablename, $dbname, $reftable);
} else {
$colsql = "SELECT ku.column_name\n FROM information_schema.key_column_usage ku\n WHERE\n ku.table_name = ?\n AND ku.table_{$dbfield} = ?\n AND ku.constraint_{$dbfield} = ?\n AND ku.constraint_name = ?\n ORDER BY ku.ordinal_position, ku.position_in_unique_constraint\n ";
$colparams = array($tablename, $dbname, $dbname, $c->constraint_name);
}
$colrecs = get_records_sql_array($colsql, $colparams);
// Make sure they've got the same number of columns
if (!$colrecs || count($fields) != count($colrecs)) {
// No match, try the next one
continue;
}
// Make sure the columns match.
reset($fields);
reset($colrecs);
if ($isfk) {
reset($reffields);
}
while (($field = current($fields)) && ($col = current($colrecs))) {
if (!$field == $col->column_name) {
// This constraint has a non-matching column; try the next constraint
continue 2;
}
if ($isfk) {
$reffield = current($reffields);
if (!$reffield == $col->refcolumn_name) {
// This constraint has a non-matching column; try the next constraint
continue 2;
}
next($reffields);
}
next($fields);
next($colrecs);
}
// If they made it this far, then it's a match!
$db->debug = $olddbdebug;
return $c->constraint_name;
}
// None matched, so return false
$db->debug = $olddbdebug;
return false;
}
