コード例 #1
0
ファイル: ddl.php プロジェクト: rboyatt/mahara
/**
 * 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;
}