Documenting a MySQL database
Today; a quick script I whipped together to create a nice HTML page out of a
mysqldump --no-dataoutput. Use it from the command line, and give the mysqldump filename as argument. The output is sent to stdout.
bash# php document_mysql_dump.php my_db_dump.sql >my_db_documentation.html
Have fun!
<?
/**
* MySQL table definition documenting script.
* Converts mysqldump files to HTML documentation.
*
* Freely Available For Now
*
* Author: mrOrigo (mrorigo@gmail.com) 2008-09-17
*
*/
if($argc != 2)
die("ERROR: Need one argument; the MySQL Dump file\n");
?>
<html>
<head>
<style type="text/css">
body { font-family: Verdana, Arial; font-size: 0.75em;}
table { font-size: 1.0em;}
table.table_columns {}
table.table_columns tr.columns { background: #eeeeee;}
table.table_columns tr.columns td.name { background: #dddddd;}
table.table_columns tr.columns td.extra { color: #555555; background: #dfdfdf;}
table.table_keys {}
table.table_keys tr.keys { background: #eeeeee;}
table.table_keys tr.keys td.name { background: #dddddd;}
table.table_constraints {}
table.table_constraints tr.constraints { background: #eeeeee;}
table.table_constraints tr.constraints td.name { background: #dddddd;}
table.table_extras {}
table.table_extras tr.extras { background: #eeeeee;}
table.table_extras tr.extras td.value { background: pink; }
</style>
</head>
<body>
<?
$file = $argv[1];
$text = file_get_contents($file);
if(!$text)
die("File not found: $argv[1]\n");
$nm = preg_match_all("/CREATE TABLE `(.*?)` \(.(.*?) (ENGINE=.*?);/ms", $text, $matches);
$tables = Array();
for($i=0; $i<$nm; $i++) {
$definition = $matches[2][$i];
// "Cheat" the spaces in comments to be , for a few regexps to work
$definition = preg_replace_callback("/COMMENT '(.*)'/im",
create_function('$a',
'return str_replace(" ", " ", $a[0]);'),
$definition);
$tables[] = Array("name" => $matches[1][$i],
"definition" => parseDefinition($definition),
"extra" => $matches[3][$i]);
}
foreach($tables as $table) {
print "<p>";
print "<a name='table_".$table["name"]."'>";
print "<h3>Table \"".$table["name"]."\"</h3></a>";
print "<b>Definition</b>";
print "<table cellspacing='0' cellpadding='2' border='1' class='table_columns'>";
print "<tr><th>Column name</th><th>Column type</th><th>Size/Options</th><th>Signed</th><th>Extra</th></tr>";
foreach($table["definition"]["columns"] as $cname => $c)
print "<tr class='columns'><td class='name'>$c[name]</td><td class='type'> $c[type]</td><td class='size'> ".($c["size"]?$c["size"]:" ")."</td><td class='signed'> ".($c["signed"] ? $c["signed"] : " "). "</td><td class='extra'> $c[extra]</td></tr>";
print "</table>";
print "<b>Indices</b>";
print "<table cellspacing='0' cellpadding='2' border='1' class='table_keys'>";
print "<tr><th>Index name</th><th>Columns</th></tr>";
foreach($table["definition"]["keys"] as $kname => $k)
print "<tr class='keys'><td class='name'>$k[name]</td><td class='columns'>$k[columns]</td></tr>";
print "</table>";
if(count($table["definition"]["constraints"]) > 0) {
print "<b>Constraints</b>";
print "<table cellspacing='0' cellpadding='2' border='1' class='table_constraints'>";
print "<tr><th>Name</th><th>Column</th><th>Foreign Table</th><th>Foreign Column</th><th>Extra</th></tr>";
foreach($table["definition"]["constraints"] as $kname => $k) {
$ftlink = "<a href='#table_".$k["foreignTable"]."'>".$k["foreignTable"]."</a>";
print "<tr class='constraints'><td class='name'>$k[name]</td><td class='column'>$k[column]</td><td class='foreign_table'>$ftlink</td><td class='foreign_column'>$k[foreignColumn]</td><td class='extra'>$k[extra]</td></tr>";
}
print "</table>";
}
print "<b>Table extras</b>";
print "<table cellspacing='0' cellpadding='2' border='1' class='table_extras'>";
$extras = parseTableExtras($table["extra"]);
foreach($extras as $en => $ev)
print "<tr class='extras'><td class='name'>$en</td><td class='value' colspan=3>$ev</td></tr>";
print "</table>";
}
function parseTableExtras($extra)
{
$extra = preg_replace_callback("/COMMENT='(.*)'/im",
create_function('$a',
'return str_replace(" ", " ", $a[0]);'),
$extra);
// Split out the extras
preg_match_all("/(ENGINE|AUTO_INCREMENT|DEFAULT CHARSET|COMMENT)=([a-zA-Z0-9_'&;!\?]*)/", $extra, $matches);
// Make into usable array
$extras = Array();
for($i=0;$i<count($matches[0]);$i++)
$extras[$matches[1][$i]] = str_replace(" ", " ", $matches[2][$i]);
return $extras;
}
function parseDefinition($definition)
{
$mysql_types = Array("bit", "bool",
"boolean", "tinyint",
"smallint", "mediumint",
"int", "integer",
"bigint", "serial",
"float", "double",
"decimal",
"datetime", "date",
"timestamp", "time", "year",
"blob", "char",
"varchar", "enum",
"set", "binary",
"varbinary", "tinyblob",
"tinytext", "blob",
"text", "mediumtext",
"mediumblob", "longtext",
"longblob");
// Split into columns, keys and constraints
preg_match("/^(.*?)(PRIMARY?\ ?KEY.*?)(CONSTRAINT.*)/si",
$definition,
$matches);
if(!$matches[3]) // Some tables don't have constraints..
preg_match("/^(.*?)(PRIMARY?\ KEY.*)(CONSTRAINT.*)?/si",
$definition,
$matches);
$columns = $matches[1];
$keys = $matches[2];
$constraints = $matches[3];
// Split the columns
preg_match_all("/(.*)\s+(".join("|",$mysql_types).")\(?([a-zA-Z0-9',]*?)\)?\s+(unsigned)?\s?([^,]*)/Si",
$columns,
$matches);
$_c=Array("names" => &$matches[1],
"types" => &$matches[2],
"sizes" => &$matches[3],
"signed" => &$matches[4],
"extra" => &$matches[5]);
// Transform columns into a more usable array
$columns = Array();
for($i=0; $i<count($_c); $i++) {
$n = trim($_c["names"][$i], " `");
if($n != "")
$columns[$n] = Array("type" => $_c["types"][$i],
"name" => $n,
"size" => $_c["sizes"][$i],
"signed"=> $_c["signed"][$i],
"extra" => $_c["extra"][$i]);
}
// Parse and tidy up keys
preg_match_all("/(PRIMARY)?.*?(KEY).*?(.*)?(\(.*\))(.*)/", $keys, $matches);
$keys = Array();
for($i=0; $i<count($matches[0]); $i++) {
$n = trim($matches[3][$i]," (`)");
if($n == "")
$n = "PRIMARY";
$keys[ $n ] = Array("name" => $n,
"columns" => trim($matches[4][$i], " (`)"));
};
// Parse and tidy up constraints
preg_match_all("/(CONSTRAINT)\s+(.*)\s+FOREIGN KEY \((.*)\)\s+REFERENCES\s+(.*)\s+\((.*)\)\s+([^,\)]*)/", $constraints, $matches);
$constraints = Array();
for($i=0; $i<count($matches[0]); $i++) {
$n = trim($matches[2][$i]," (`)");
$constraints[ $n ] = Array("name" => $n,
"column" => trim($matches[3][$i]," (`)"),
"foreignTable" => trim($matches[4][$i]," (`)"),
"foreignColumn" => trim($matches[5][$i]," (`)"),
"extra" => $matches[6][$i]);
}
return Array("columns" => $columns,
"keys" => $keys,
"constraints" => $constraints);
}
?>
