graphviz ERM generator

<?php

define("DB_SERVER", "localhost");
define("DB_USER", "root");
define("DB_PASS", "");
define("DB_NAME", "easy");

$dbhandle   = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die("Unable to connect to MySQL");
$result_tbl = mysql_query('SHOW TABLES FROM ' . DB_NAME, $dbhandle );
$tables     = array();

while($row = mysql_fetch_row($result_tbl))
{
   $tables[] = $row[0];
}

$selected = mysql_select_db(DB_NAME, $dbhandle) or die('Could not select examples');

$output = "digraph G {\n";

// iterate over each table and return the fields for each table
foreach($tables as $table)
{
   $result_fld = mysql_query('SHOW FIELDS FROM ' . $table, $dbhandle );
   $output    .= $table . ' [shape=none, label=<<TABLE CELLSPACING="0" CELLPADDING="4"><TR><TD COLSPAN="5" BGCOLOR="lightgrey">'.$table.'</TD></TR>';

   while($row1 = mysql_fetch_assoc($result_fld))
   {
      $output   .= '<TR>';
      $fieldname = $row1['Field'];
      $fieldtype = $row1['Type'];
      $nullable  = $row1['Null'];
      $default   = $row1['Default'];
      $pri       = '';
      $fk        = false;

      if($row1['Key'] == 'PRI')
      {
          $pri = 'PK';
      }

      if(isset($foreign_keys[$fieldname]))
      {
          $fk = $foreign_keys[$fieldname]['referenced_table'] . ':' . $foreign_keys[$fieldname]['referenced_column'];
      }

      $output .= '<TD>' . $pri . '</TD>';
      $output .= '<TD>' . $fieldname . '</TD>';
      $output .= '<TD>' . $fieldtype . '</TD>';
      $output .= '<TD>' . $nullable . '</TD>';
      $output .= '<TD PORT="' . $table . '_' . $fieldname . '">' . $default . '</TD>';
      $output .= '</TR>';
   }

   $output .= "</TABLE>>];\n\n";
}

$selected = mysql_select_db('INFORMATION_SCHEMA', $dbhandle) or die('Could not select examples');

foreach($tables as $table)
{
   $foreign_keys_res = mysql_query('SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM KEY_COLUMN_USAGE WHERE TABLE_NAME = \'' . $table . '\' AND CONSTRAINT_NAME NOT LIKE \'PRIMARY\' AND REFERENCED_TABLE_NAME IS NOT NULL AND CONSTRAINT_SCHEMA LIKE \'' . DB_NAME  . '\'', $dbhandle);

   while($row1 = mysql_fetch_assoc($foreign_keys_res))
   {
       $output .= $table . ':' . $table . '_' . $row1['COLUMN_NAME'] . ' -> ' . $row1['REFERENCED_TABLE_NAME'] . ':' . $row1['REFERENCED_TABLE_NAME'] . '_' . $row1['REFERENCED_COLUMN_NAME'];
       $output .= "\n";
   }
}

$output .= "}\n";

mysql_close($dbhandle); 

echo $output;

Powered by klikics.de