summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--application/controllers/StatsController.php10
-rw-r--r--library/Pbs/Graph.php10
-rw-r--r--mysqlGraph.php76
-rw-r--r--mysqlGraph.pngbin0 -> 282021 bytes
-rw-r--r--pbs.sql22
5 files changed, 87 insertions, 31 deletions
diff --git a/application/controllers/StatsController.php b/application/controllers/StatsController.php
index 024eacd..3aee1d2 100644
--- a/application/controllers/StatsController.php
+++ b/application/controllers/StatsController.php
@@ -14,14 +14,10 @@ class StatsController extends Zend_Controller_Action
$userIDsNamespace = Zend_Session::namespaceGet('userIDs');
if($userIDsNamespace['groupID'] !=''){
- header("Content-Type: image/png");
-
+ header("Content-Type: image/png");
$n = new Pbs_Graph();
- $str = 'echo "';
- $str .= $n->graph($userIDsNamespace['groupID']);
- $str .= '" | dot -Tpng ';
-
- passthru($str);
+ $str = $n->graph($userIDsNamespace['groupID']);
+ echo $str;
}
}
}
diff --git a/library/Pbs/Graph.php b/library/Pbs/Graph.php
index 7711dce..ae3ab16 100644
--- a/library/Pbs/Graph.php
+++ b/library/Pbs/Graph.php
@@ -18,8 +18,14 @@ class Pbs_Graph{
$this->getParentGroups($groupID);
$this->getChildGroups($groupID);
$this->graphstring .= '}';
- $this->graphstring = str_replace(array("\t","\n"),"",$this->graphstring);
- return str_replace('"','\"',$this->graphstring);
+ $this->graphstring = str_replace(array("\t","\n"),"",$this->graphstring);
+ $this->graphstring = str_replace('"','\"',$this->graphstring);
+
+ $str = 'echo "';
+ $str .= $this->graphstring;
+ $str .= '" | dot -Tpng ';
+ passthru($str,$end);
+ return $end;
}
private function getGroupTitle($groupID){
$group = new Application_Model_Group();
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";
diff --git a/mysqlGraph.png b/mysqlGraph.png
new file mode 100644
index 0000000..81468b8
--- /dev/null
+++ b/mysqlGraph.png
Binary files differ
diff --git a/pbs.sql b/pbs.sql
index 1cbc9c3..9d5d57d 100644
--- a/pbs.sql
+++ b/pbs.sql
@@ -295,29 +295,7 @@ ALTER TABLE `pbs_filter`
ADD CONSTRAINT `pbs_filter_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
ADD CONSTRAINT `pbs_filter_ibfk_3` FOREIGN KEY (`bootmenuID`) REFERENCES `pbs_bootmenu` (`bootmenuID`) ON DELETE CASCADE;
-CREATE TABLE IF NOT EXISTS `pbs_membershipfilters` (
- `membershipID` int(11) NOT NULL,
- `filterID` int(11) NOT NULL,
- PRIMARY KEY (`membershipID`,`filterID`),
- KEY `membershipID` (`membershipID`),
- KEY `filterID` (`filterID`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
-
-ALTER TABLE `pbs_membershipfilters`
- ADD CONSTRAINT `pbs_membershipfilters_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE CASCADE,
- ADD CONSTRAINT `pbs_membershipfilters_ibfk_2` FOREIGN KEY (`filterID`) REFERENCES `pbs_filter` (`filterID`) ON DELETE CASCADE;
-
-CREATE TABLE IF NOT EXISTS `pbs_poolfilters` (
- `poolID` int(11) NOT NULL,
- `filterID` int(11) NOT NULL,
- PRIMARY KEY (`poolID`,`filterID`),
- KEY `poolID` (`poolID`),
- KEY `filterID` (`filterID`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
-ALTER TABLE `pbs_poolfilters`
- ADD CONSTRAINT `pbs_poolfilters_ibfk_1` FOREIGN KEY (`poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE,
- ADD CONSTRAINT `pbs_poolfilters_ibfk_2` FOREIGN KEY (`filterID`) REFERENCES `pbs_filter` (`filterID`) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS `pbs_filtertype` (
`filtertypeID` int(11) NOT NULL AUTO_INCREMENT,