note 40978 added to ref.mysql
| From: | ScottHolodak at rn2 dot php dot net | Date: | Thu, 25 Mar 2004 06:09:53 +0000 |
| Subject: | note 40978 added to ref.mysql | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-67140@lists.php.net to get a copy of this message | ||
I was working on writing a script that would dump an INSERT query for ever row of data in every
table. I was populating data on the development server and was looking for an easy way to get it
onto the production server.
The task got complicated because of InnoDB referential integrity constraints. The tables needed to
be backed up in a particular order for the bulk INSERTs to work properly.
The following function (ugly as it looks) reads the list of tables from a MySQL database, analyzes
the CREATE TABLE statements, extrapolates the table dependencies and builds a 2-dimensional array
from them. It then runs a scheduling algorithm so that the tables can be backed up. The end result
is an array containing a valid ordering of the tables to be backed up (or an empty array if there
are errors). Pass an empty string to schedule all tables in the current database.
I stripped this straight out of my application, but the required changes should be minimal. Maybe
I'll post the backup function separately.
<?
function scheduleTables($wildcard) {
$tquery = "SHOW TABLES;";
$tables = mysql_query($tquery);
if ($tables) {
$tbldeps = array();
$tblidx = 0;
while ($row = mysql_fetch_row($tables)) {
$local_table = $row[0];
if (substr($local_table, 0, strlen($wildcard)) == $wildcard) {
# Parse the CREATE TABLE statement to determine dependent tables
$query = "SHOW CREATE TABLE $local_table";
if ($result = mysql_query($query)) {
if ($row = mysql_fetch_row($result)) {
$stmt = $row[1];
$frags = preg_split("/[,]+/", $stmt);
$ct = 0;
$deps = array();
for ($i = 0; $i < count($frags); $i++) {
if (substr(trim($frags[$i]), 0, strlen("CONSTRAINT")) == "CONSTRAINT") {
$ct++;
$cstrt = $frags[$i];
preg_match_all("/
\w+/", $cstrt, $matches);
$local_field = substr($matches[0][1], 1, strlen($matches[0][1]) - 2);
$remote_table = substr($matches[0][2], 1, strlen($matches[0][2]) - 2);
$remote_field = substr($matches[0][3], 1, strlen($matches[0][3]) - 2);
$deps = array_merge($deps, array($ct => $remote_table));
}
}
$tbldeps = array_merge($tbldeps, array($local_table => $deps));
} else {
print "/* Unable to retrieve CREATE TABLE statement */\r\n";
return array();
}
} else {
print "/* Unable to retrieve CREATE TABLE statement */\r\n";
return array();
}
}
$tblidx++;
}
$order = array(); # this will hold the final table ordering
# Add all tables which have no dependencies
$kidxs = array_keys($tbldeps);
for ($i = 0; $i < count($kidxs); $i++) {
$tblkey = $kidxs[$i];
if (count($tbldeps[$tblkey]) == 0) {
array_push($order, $tblkey);
unset($tbldeps[$tblkey]);
}
}
# Now the hard part... Handle tables with dependencies
$activity = TRUE;
while(count($tbldeps) > 0) {
$kidxs = array_keys($tbldeps);
$activity = FALSE;
for ($i = 0; $i < count($kidxs); $i++) {
$tblkey = $kidxs[$i];
if (count($tbldeps[$tblkey]) > 0 && isset($tbldeps[$tblkey])) {
$found_all = TRUE;
for ($j = 0; $j < count($tbldeps[$tblkey]); $j++) {
$found = FALSE;
for ($k = 0; $k < count($order); $k++) {
if ($tbldeps[$tblkey][$j] == $order[$k]) {
$found = TRUE;
unset($tbldeps[$tblkey][$j]);
}
}
if (!$found) {
$found_all = FALSE;
break;
}
}
if ($found_all) {
$activity = TRUE;
unset($tbldeps[$tblkey]);
array_push($order, $tblkey);
}
}
}
}
return $order;
} else {
print "/* Unable to retrieve tables */\r\n";
return array();
}
}
?>
Hope this helps (erm, someone)
----
Manual Page -- http://www.php.net/manual/en/ref.mysql.php
Edit -- http://master.php.net/manage/user-notes.php?action=edit+40978
Delete -- http://master.php.net/manage/user-notes.php?action=delete+40978&report=yes
Reject -- http://master.php.net/manage/user-notes.php?action=reject+40978&report=yes
Search -- http://master.php.net/manage/user-notes.php