path: root/scripts/mysqlGraph.php

<?php
/*
 * Copyright (c) 2011 - OpenSLX GmbH, RZ Uni Freiburg
 * This program is free software distributed under the GPL version 2.
 * See http://gpl.openslx.org/
 *
 * If you have any feedback please consult http://feedback.openslx.org/ and
 * send your suggestions, praise, or complaints to feedback@openslx.org
 *
 * General information about OpenSLX can be found at http://openslx.org/
 */
// generate mysql graph
echo "Opening Database Connection...\n";
$link = mysql_connect('localhost', 'root', '123456');
if (!$link) {
  die('keine Verbindung möglich: ' . mysql_error());
}
#mysql_close($link);
mysql_select_db('pbs');


$result = mysql_list_tables('pbs');
echo "Get Tables ...\n";
while ($row = mysql_fetch_row($result)) {
  $tables[$row[0]] = array();
}
mysql_free_result($result);

// Save all table-names
foreach($tables as $tablename => $data) {
  $result = mysql_query("SHOW COLUMNS FROM $tablename");
  while ($row = mysql_fetch_assoc($result)) {
    $tables[$tablename][] = $row;
  }
}
mysql_free_result($result);
echo "Get Fieldlist from tables ...\n";
// Prepare dot-string
$b = "\n";
$str = 'digraph x {
       node [shape=record];
       ranksep=3;
       size="20,20";'.$b;

// get all table-fields
foreach($tables as $tablename => $data) {
  $str .= $tablename .' [label= "{';
  $array = array();
  $array[] = strtoupper($tablename);
  foreach($data as $d) {
    $array[] = "<".$d['Field'].">".$d['Field'];
  }
  $str .= implode("|", $array);
  $str .= '}",width=3];'.$b;
}

echo "Generate the Links ...\n";
// link the database-items (foreign-keys)
foreach($tables as $tablename1 => $table1) {
  foreach($table1 as $field1) {
    $f1 = $field1['Field'];
    foreach($tables as $tablename2 => $table2) {
      foreach($table2 as $field2) {
        $f2 = $field2['Field'];
        if( $tablename1 != $tablename2 && $f1 == $f2 && strstr($f1, 'ID') && "pbs_".str_replace("ID", '', $f1) == $tablename1) {
          // Draw lines to tables
          $str .= $tablename1 ." -> ".$tablename2.";".$b;
          // draw lines to table-items
#$str .= $tablename1.":".$f1 ." -> ".$tablename2.":".$f2 .";".$b;
        }
      }
    }
  }
}
$str .= "}";
echo "Write to file ...\n";
// Create the dot file
$fp = fopen("mysqlGraph.dot", "w");
fputs ($fp, $str);
fclose ($fp);
// Generate the image
echo "Generate the image ...\n";
exec("dot -Tpng mysqlGraph.dot >mysqlGraph.png");
// delete the dot file
unlink ('mysqlGraph.dot');
echo "Ready!\n";