#!/usr/bin/php
<?

# mysqlviz $Rev$ / $Date$

# used for automated guessing of table relationships
$standard_table_prefixes=array('','tbl','tbl_','table','table_');
$standard_table_suffixes=array('','s','s_table','_table','table');

# command line options
$opts = getopt('f:rh:');

# input validation
if(isset($opts['h']) || !isset($opts['f'])) {
 usage();
}

$file = $opts['f'];
if(!file_exists($file)) { error("$file does not exist."); }
if(!is_file($file)) { error("$file is not a file."); }
if(!is_readable($file)) { error("$file is not readable."); }

# read the table definitions out of the file using sed to capture any
# whole paragraph containing "CREATE TABLE `"
$cmd = "sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `/!d;' " . escapeshellarg($file);
exec($cmd,$output);
# for sqlite, this fails, so we just read the file
if(count($output) == 0) {
 $output = preg_split("/\n/",file_get_contents($file));
}

# split in to tables
$tables = array();
$current_table = '';
$current_table_definition = '';
foreach($output as $line) {
 # if this line begins a table definition
 if(strpos($line,'CREATE TABLE') === 0) {
  # extract table name
  list($x,$table_name) = split('`',$line,3);
  if($table_name=='') { list($x,$table_name) = split('"',$line,3); }
  # store last table definition if necessary
  if($current_table!='') {
   $tables[$current_table]['create'] = $current_table_definition;
   $current_table_definition='';
  }
  $current_table = $table_name;
  unset($table_name);
 }
 # otherwise, if we're on the primary key...
 elseif(strpos($line,'PRIMARY KEY')>0) {
  list($x,$field,$remnants) = split('`',$line,3);
  if($field == '') { list($x,$field,$remnants) = split('"',$line,3); }
  $tables[$current_table]['pk'] = $field;
  $tables[$current_table]['fields'][$field] = preg_replace('/,\s*$/','',ltrim($remnants));
 }
 # some other line...
 else {
  # if it starts with ` (maybe after whitespace), it's a field.
  $line = ltrim($line);
  if(strpos($line,'`')===0 || strpos($line,'"')===0) {
   list($x,$field,$remnants) = split('`',$line,3);
   if($field=='') {
    list($x,$field,$remnants) = split('"',$line,3);
   }
   $tables[$current_table]['fields'][$field] = preg_replace('/,\s*$/','',ltrim($remnants));
   # detect sqlite3 foreign key definitions
   if(preg_match('/ REFERENCES "(.*?)" \(\"(.*?)\"\)/',$remnants,$matches)) {
    array_shift($matches);
    $local_column = $field;
    list($foreign_table,$foreign_column) = $matches;
    # store
    $tables[$current_table]['fk'][$local_column] = array(
     'table' => $foreign_table,
     'column' => $foreign_column
    );
   }
  }
  # foreign key
  #   eg: CONSTRAINT `sometable__parent_fk` FOREIGN KEY (`parent`) 
  #       REFERENCES `sometable` (`id`) ON DELETE SET NULL 
  #                                     ON UPDATE CASCADE
  elseif(strpos($line,'` FOREIGN KEY (`')>1) {
   # get fk name, fk field, referenced table, referenced column
   list($x,$fk_name,$x,$local_column,$x,$foreign_table,$x,$foreign_column,$remnants) = split('`',$line,9);
   # parse remnants
   preg_match_all('/ON (\w+?) ((?:\w+|SET \w+))/',$remnants,$matches);
   array_shift($matches);
   $cases = array();
   for($i=0;$i<count($matches);$i++) {
    $cases[$matches[0][$i]] = $matches[1][$i];
   }
   # store
   $tables[$current_table]['fk'][$local_column] = array(
    	'table' => $foreign_table,
    	'column' => $foreign_column,
    	'cases' => $cases
    );
  }
 }
 # append just-processed line to current table definition
 $current_table_definition .= $line . "\n";
}
$tables[$current_table]['create'] = $current_table_definition;

# redump mode
if(isset($opts['r'])) {
 # extract hostname and database from file
 $cmd = "grep -m1 -i '^-- Host: ' ".escapeshellarg($file)."|cut -d ':' -f2,3 |cut -d ' ' -f2- |sed 's/ *Database://'";
 $result = exec($cmd);
 list($hostname,$database) = split(' ',$result,2);
 print "mysqldump -h " . escapeshellarg($hostname) . " -u root -p " . escapeshellarg($database) . " " . join(' ',array_keys($tables)) . "\n";
 exit(0);
}

######## actually generate DOT

 $relations = '';
 $extra_tables = '';
 print "// generated by mysqlviz - http://code.google.com/p/mysqlviz\n";
 print "//   dedicated to unix hackers with a conscience who refuse to work for morally\n";
 print "//   bankrupt governments and corporations: use your powers for good!\n";
 print "digraph g {\n";
 print " rankdir=LR\n";
 print " ranksep=3\n";
 foreach(array_keys($tables) as $table) {
  print " $table [\n  shape=record\n  width=4\n  label=\"" . strtoupper($table);
  foreach(array_keys($tables[$table]['fields']) as $field) {
   print "|<$field>$field";
  }
  if(is_array($tables[$table]['fk'])) {
   foreach(array_keys($tables[$table]['fk']) as $fk_field) {
    # only print if foreign table is known
    #if(is_array($tables[$tables[$table]['fk'][$fk_field]['table']])) {
     $relations .= ' ' . $tables[$table]['fk'][$fk_field]['table'] . ':' . $tables[$table]['fk'][$fk_field]['column'] . " -> $table:$fk_field;\n";
    #}

    if(!is_array($tables[$tables[$table]['fk'][$fk_field]['table']])) {
     $extra_tables .= ' ' . $tables[$table]['fk'][$fk_field]['table'] . " [\n  shape=record\n  width=4\n  label=\"".strtoupper($tables[$table]['fk'][$fk_field]['table'])."|<".$tables[$table]['fk'][$fk_field]['column'].'>'.$tables[$table]['fk'][$fk_field]['column']."|...\"\n ]\n";
    }
   }
  }
  print "}\"\n ]\n";
 }
 print $extra_tables;

 # check that we have some relations
 if($relations=='') { 
  warning("No foreign keys found: attempting to guess table relationships!");
  foreach(array_keys($tables) as $table) {
   foreach(array_keys($tables[$table]['fields']) as $field) {
    # if the column isn't primary key, and ends with '_id' or similar...
    if($field!=$tables[$table]['pk'] && preg_match('/^(.+)_?id$/i',$field,$matches)) {
     $done=0;
     # loop through common prefixes/suffixes and attempt to auto-match
     for($i=0;$i<count($standard_table_prefixes)&&!$done;$i++) {
      $prefix=$standard_table_prefixes[$i];
      for($j=0;$j<count($standard_table_suffixes)&&!$done;$j++) {
       $suffix=$standard_table_suffixes[$j];
       # first try direct concatenation...
       $maybetable=$prefix.$matches[1].$suffix;
       if(is_array($tables[$maybetable])) {
        $relations .= " $table:$field -> $maybetable:" . $tables[$maybetable]['pk'] . ";\n";
        $done=1;
       }
       # otherwise try ucfirst()'d version
       else {
        $maybetable=$prefix.ucfirst($matches[1].$suffix);
        if(is_array($tables[$maybetable])) {
         $relations .= " $table:$field -> $maybetable:" . $tables[$maybetable]['pk'] . ";\n";
         $done=1;
	}
       }
      }
     }
    }
   }
  }
 }
 print $relations;
 print "}\n";

exit(0);

# usage information
function usage() {
 global $argv;
 print '[' . basename($argv[0]) . " - mysql + sqlite database visualisation tool]\n\n";
 print "usage:\n";
 print "  " . $argv[0] . " -f <sqldumpfile> [-r]\n";
 print "                                 ^--- 'redump' mode: generates a\n";
 print "                                      mysqldump command line to redump.\n";
 print "toolchain:\n";
 print " $ mysqldump -d db >db.sql          # MySQL: -d = 'no data', only structure\n";
 print "    - OR -\n";
 print " $ sqlite database.db .dump >db.sql # SQLite (also: 'sqlite3 ...')\n";
 print " $ $argv[0] -f ./db.sql >./db.dot # 'dot' is a graphviz format.\n";
 print " $ dot -Tpng db.dot >db.png         # generate image with graphviz\n\n";
 print "notes:\n";
 print " if you do not have any foreign keys defined, relationships will be\n";
 print " assumed in cases where a column name ends in one of (id/ID/_id/_ID) and\n";
 print " there is a matching table (for example, onetable.othertable_ID column, and\n";
 print " othertable is defined).  the program will also match tables with\n";
 print " various prefixes and suffixes.\n";
 exit(0);
}

# display error and exit
function error($error) {
 print "ERROR: $error\n";
 exit(1);
}

# display warning and continue
function warning($warning) {
 $stderr = fopen('php://stderr','w');
 fwrite($stderr,"(WARNING: $warning)\n");
 fclose($stderr);
}

?>