Drawing your ER diagram using myDBR

(9 posts) (4 voices)

Tags:

  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.