Monday, November 07, 2005

PHP and Foreign Key Constraints

I hate having a Really Big Database that complains when you do a DELETE FROM table_foo with something obscure like Cannot delete or update a parent row: a foreign key constraint fails.

Great, mysql, just $##%ing wonderful. There's 17 tables that reference table_foo, which one is broken?

I think to myself: surely there's something that will show me all of the keys pointing to this table, yeah?

No. Not even a hint. I was stuck using DB_Visualiser and inspecting tables. Ugh!

Below is a solution I bothered to whip up at work today. $db is an instance of PEAR::DB, CLS_html::Form_Start() is merely a FORM tag with some extras. Ditto CLS_html::input(). By default, PEAR::DB fetchs row names (Fetch Mode Assoc?) rather than numbers.


<?php

/**
* What tables reference $target?
*/
function references($target, $keys) {
$matches = array();

foreach ($keys as $key => $tgt) {
if ($target == $tgt) {
$matches[] = $key;
}
}

return $matches;
}

function findKeys() {
global $db;

$pattern = "/CONSTRAINT `.*` FOREIGN KEY \(`(.*)`\) REFERENCES `(.*)` \(`(.*)`\)/i";

$sql = "SHOW TABLES";
$result = $db->query($sql);

$matches = array();
$keys = array();

while ($row = $result->fetchRow()) {
foreach ($row as $table) {
$syntax = $db->query(sprintf("SHOW CREATE TABLE %s", $table))->fetchRow();

preg_match_all($pattern, $syntax["Create Table"], $matches);

for ($i = 0; $i < count($matches[0]); $i++) {
$keys[$syntax["Table"] . "." . $matches[1][$i]] = $matches[2][$i] .
"." . $matches[3][$i];
}
}
}

return $keys;
}

if (!isset($_REQUEST["field"]) || !isset($_REQUEST["value"])) {
print CLS_html::Form_Start();

print '<p>';
print 'Field<br />';
print CLS_html::input("text","field");
print 'Value<br />';
print CLS_html::input("text","value");
print CLS_html::input("submit","","Describe");
print '</p>';

print CLS_html::Form_End();
} else {
$keys = findKeys();
$references = references($_REQUEST["field"],$keys);
$args = $_REQUEST["value"];


print '<pre>';

for ($i=0;$i<count($references); $i++) {
$parts = explode(".", $references[$i]);
$sql = sprintf("SELECT * FROM %s
WHERE %s = ? LIMIT 1",
$parts[0],
$references[$i]);
$result = $db->query($sql, $args);


if ($result->numRows() > 0) {
print str_replace("?",$_REQUEST["value"],$sql) . ";\n";
}
}
print '</pre>';
}




?>


No comments: