Nicht benötigte Tabellen entfernt, Diagramm wird automatisch generiert, Stats-Controller um Url für Bilder

1 files changed, 76 insertions, 0 deletions

diff --git a/mysqlGraph.php b/mysqlGraph.php
new file mode 100644
index 0000000..c708060
--- /dev/null
+++ b/mysqlGraph.php
@@ -0,0 +1,76 @@
+<?php
+// 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";