1. 

   myDBR Team, Key Master

   Hi,
   Here is a sample report which will draw an entity-relationship diagram from your database using system views. The output of the report looks like this:

   Sample report from sakila database

   Views and tables with no referential integrity constrains will show up with no connections.

   CREATE PROCEDURE sp_DBR_schema( in_schema varchar(64 ) )
   
   BEGIN
   
   select 'dbr.chart', 'Hierarchy', CONCAT( in_schema, ' @ ', CURDATE(), ' ', CURTIME() );
   
   select 'dbr.chart.options', 'format', 'pdf';
   
   select 'dbr.chart.gv.style', 'graph', 'compound=true, 	fontname="Arial", ranksep=1.0, ratio=0.8, rankdir="LR"';
   
   select 'dbr.chart.gv.style', 'node', 'shape="plaintext", fontname="Arial", fontsize=10';
   
   select 'dbr.chart.gv.style', 'cluster', 'shape=none, color=white';
   
   select 'dbr.chart.gv.style', 'edge', 'arrowhead=none, arrowtail=normal, style=dashed, color="#444444", ';
   
   select 'dbr.chart.gv.node', table_name, fn_table_info( in_schema, table_name ), '', concat('c_', t.table_name)
   
   from information_schema.tables t
   
   where t.table_schema = in_schema;
   
   select 'dbr.chart.gv.cluster', concat('c_', t.table_name), '', 'color=white'
   
   from information_schema.tables t
   
   where t.table_schema = in_schema;
   
   select table_name, null
   
   from information_schema.tables t
   
   where t.table_schema = in_schema
   
   union
   
   select concat(referenced_table_name, ':', referenced_column_name), concat(table_name, ':', column_name)
   
   from information_schema.key_column_usage
   
   where table_schema=in_schema and referenced_table_name is not null;
   
   END
   
   

   --
   myDBR Team
2. 

   fib, Member

   Thanks for this, but when I tried this report code, I am getting:

   "FUNCTION database name.fn_table_info does not exist"

   Why is that, excuse me for this question, but I really do not get it.

   Appreciate your help.
3. 

   myDBR Team, Key Master

   We have two version of the report: one with columns, one without. The one with columns requires a function that was not included. Here it is:

   fn_table_info.sql

   --
   myDBR Team
4. 

   mleary2001, Member

   The link above:

   Sample report from sakila database

   returns License.txt, not an example of this report

   Mike
5. 

   myDBR Team, Key Master

   The file has now been restored.

   --
   myDBR Team
6. 

   john, Member

   i checked that graphiz was installed, and created the function. this is the output of the report under the schema/date/time

   Graphviz graph creation failed.

   Command:
   dot -T'pdf' -o'/tmp/graph_tYXetn.pdf' '/tmp/graph_tYXetn' 2>&1

   Error message sh: 1: dot: not found

   note: if i change chart type from Hierarchy to Diagram i get unkown chart type. maybe something is broke with my system
7. 

   myDBR Team, Key Master

   Graphviz installation consist of two separate things. First is the graphviz application itself and the secong is the Graphviz PEAR library.

   The error message states that the 'dot' command cannot be found. The dot-command comes as part of the graphviz application. You can download the application from graphviz.org or use the package management from your OS.

   For example to install graphviz in Ubuntu, you do:

   $ sudo apt-get install graphviz

   --
   myDBR Team
8. 

   john, Member

   sorry the apt-get does work.
   The report fails on creating a pdf, this is fine -probably a size issue.
   when i delete this line the chart appears in the browser but it is massive as there are alot of tables without joins
   #select 'dbr.chart.options', 'format', 'pdf';
9. 

   myDBR Team, Key Master

   apt-get example was for Ubuntu Linux which uses the apt-get package management. Choose the package management system for your operating system / distro to install the Graphviz.

   PDF format in charts should only be used when you generate only the chart, not any HTML, and you almost never need to do this. Using SVG-charts usually gives you the best result.

   --
   myDBR Team

Reply

You must log in to post.