SQL Diagram creation: Difference between revisions

From OpenPetra Wiki
Jump to navigation Jump to search
No edit summary
 
(22 intermediate revisions by 2 users not shown)
Line 1: Line 1:
==THIS TASK IS DONE==
What we wanted to achieve with this task has now been achieved by other means (using the open source SchemaSpy tool). The output can be found here: [http://openpetraorg.sourceforge.net/schemaSpy/index.html]
'''This wiki page is therefore left for reference information only.'''
== Summary ==
This page describes the sql2diagram tool, and how to use it for OpenPetra.org.
We used to have this project description:
* Design some database diagrams
** Please also have a look at the old Petra 2.1 diagrams ([http://openpetraorg.sourceforge.net/Petra21DBDiagrams.zip download 9 png files in a zip file]). The diagrams might need to be a bit smaller and more specific, otherwise they get too big and complicated.
Now that we are using SchemaSpy which autogenerates diagrams depending on their first and second degree related tables, we won't need any manual diagrams anymore.
== sql2diagram ==
== sql2diagram ==
We wrote this tool a while ago in C/C++ and uploaded it to sourceforge: http://sql2diagram.wiki.sourceforge.net/
We wrote this tool a while ago in C/C++ and uploaded it to sourceforge: http://sql2diagram.wiki.sourceforge.net/
Line 6: Line 21:
* it generates HTML documentation
* it generates HTML documentation
* it is able to generate diagrams for the DIA program (http://live.gnome.org/Dia)
* it is able to generate diagrams for the DIA program (http://live.gnome.org/Dia)
* it generates an HTML map for the diagram png file, and links to it from the HTML documentation


The generation of the HTML documentation works fine.
The generation of the HTML documentation works fine.
For the current results, check http://openpetraorg.sourceforge.net/dbdoc; There is a diagram for the Accounts Payable sub system: http://openpetraorg.sourceforge.net/dbdoc/img/img_AccountsPayable.html?a_ap_document


=== diagrams ===
=== diagrams ===
There are some problems with generating the diagrams. On the one hand it allows manual moving of the tables and connections in the dia editor, and the sql2diagram tool should pick up the manual positions and reuse them. But on the other hand, it is quite tedious to move the tables and foreign key constraints all by hand.
There are some problems with generating the diagrams. On the one hand it allows manual moving of the tables and connections in the dia editor, and the sql2diagram tool should pick up the manual positions and reuse them. But on the other hand, it is quite tedious to move the tables and foreign key constraints all by hand.


Task 1:
==== Task 1: Use the new Database tools, instead of the ER shapes ====
* Use the new Database tools, instead of the ER shapes.
Database shapes were only added recently to dia, and might look better than the ER shapes we are using currently.


Task 2:
Issues to consider: how to show which fields are part of a foreign key? At the moment, we print FK at the back of the row that describes the field name and type.
* Do an automatic layout of the tables and connections.


Alternatives:
==== Task 2: Do an automatic layout of the tables and connections ====
* I had a look at [http://www.aarontrevena.co.uk/opensource/autodia/ autodia], but it seems you still have to rearrange the result in the dia editor.
* http://www.rbt.ca/autodoc/output-dia.html states on the website that they do not do the positioning.


Some discussions and first hints:
Some discussions and first hints:
Line 28: Line 43:
** (I was not able to get the python plugin to work with dia on windows)
** (I was not able to get the python plugin to work with dia on windows)
* the discussion on the mailing list mentions [http://en.wikipedia.org/wiki/Force-based_algorithms Force based algorithms] for the positioning
* the discussion on the mailing list mentions [http://en.wikipedia.org/wiki/Force-based_algorithms Force based algorithms] for the positioning
So my approach to Task 2 would be to use some good algorithms when arranging the diagram, and make sure the generated diagram has the tables in the best position (most referenced tables in the centre, tables that are most linked to each other closest to each other), and the linking constraints are not crossing any tables, and are still possible to be read.
* For the maths side: [http://www.boost.org/doc/libs/1_39_0/libs/graph/doc/table_of_contents.html The Boost Graph Library (BGL)] might be helpful.
** see explanation of shortest path algorithms: http://www.boost.org/doc/libs/1_39_0/libs/graph/doc/graph_theory_review.html#sec:shortest-paths-algorithms (I am not sure if that is really the right answer to the problem; this is a bit too complicated for me...)
* path finding: Once the tables are positioned, we could use a pathfinding algorithm for the constraints:
** http://ai-depot.com/Tutorial/PathFinding.html Good Tutorial about path finding
** http://en.wikipedia.org/wiki/Pathfinding
** http://www.gamedev.net/reference/programming/features/astar/ A* Pathfinding for Beginners
** a GPL path finding implementation in C: http://marathon.svn.sourceforge.net/viewvc/marathon/trunk/Source_Files/GameWorld/pathfinding.cpp
** see also http://lib2dp.sourceforge.net/ 2DPathfinder is a C library for performing fast pathfinding on 2D maps.
== How to use sql2diagram for OpenPetra.org ==
See a sample result here: [http://openpetraorg.sourceforge.net/dbdoc/img/img_AccountsPayable.html?a_ap_document diagram for the Accounts Payable sub system]
See the file themed.prj in OpenPetra\db\doc; that contains already a collection of tables. You might also copy the table names from the file master_alltables.prj in the same directory.
Then you run <code>nant dbdoc</code> to generate the diagram file (extension .dia). You then can edit that with the [http://live.gnome.org/Dia/Download dia editor] (available for Linux and Windows). First, the tables are all on the invisible layer. See the instructions on the [http://sql2diagram.wiki.sourceforge.net/ sql2diagram page] how to copy the tables to the front layer, and then to arrange the tables. Next time you run <code>nant dbdoc</code> the constraint lines will be inserted. Please try to make them look ok by moving the lines and inserting new segments.
== List of alternatives to sql2diagram ==
The following list of alternatives (from [http://stackoverflow.com/questions/185967/generate-an-e-r-diagram-by-reverse-engineering-a-database here] and [http://stackoverflow.com/questions/117774/decipher-database-schema#117919 here]) could be evaluated for ease of use instead of sql2diagram:
* [http://schemaspy.sourceforge.net/ SchemaSpy] - seems to be recommended often (apart from Visio)
* [http://www.joachim-uhl.de/projekte/schemaspygui SchemaSpy GUI] GUI version of the above
* Visio (obviously)
* [http://www.minq.se/products/dbvis/ DB Vis] (is commercial, but has a free and possibly sufficient version available)
* [http://schemabank.com/ SchemaBank (webbased, no need for install)]
* [http://www.myopensource.org/development/database+design+tool/download-review DDT (Database Design Tool)]
Listed earlier:
* I had a look at [http://www.aarontrevena.co.uk/opensource/autodia/ autodia], but it seems you still have to rearrange the result in the dia editor.
* http://www.rbt.ca/autodoc/output-dia.html states on the website that they do not do the positioning.
* MS Visio can do it ;) http://soccerproject.wordpress.com/2008/12/05/creating-a-postgresql-database-diagram/
* http://sqldeveloper.solyp.com/index.html: works against a Mysql database; automatic layout of diagram

Latest revision as of 08:44, 1 February 2012

THIS TASK IS DONE

What we wanted to achieve with this task has now been achieved by other means (using the open source SchemaSpy tool). The output can be found here: [1]

This wiki page is therefore left for reference information only.


Summary

This page describes the sql2diagram tool, and how to use it for OpenPetra.org.

We used to have this project description:

  • Design some database diagrams
    • Please also have a look at the old Petra 2.1 diagrams (download 9 png files in a zip file). The diagrams might need to be a bit smaller and more specific, otherwise they get too big and complicated.

Now that we are using SchemaSpy which autogenerates diagrams depending on their first and second degree related tables, we won't need any manual diagrams anymore.

sql2diagram

We wrote this tool a while ago in C/C++ and uploaded it to sourceforge: http://sql2diagram.wiki.sourceforge.net/

functionality:

  • it reads the SQL tables and attributes from the SQL file with all the CREATE TABLE statements
  • it generates HTML documentation
  • it is able to generate diagrams for the DIA program (http://live.gnome.org/Dia)
  • it generates an HTML map for the diagram png file, and links to it from the HTML documentation

The generation of the HTML documentation works fine.

For the current results, check http://openpetraorg.sourceforge.net/dbdoc; There is a diagram for the Accounts Payable sub system: http://openpetraorg.sourceforge.net/dbdoc/img/img_AccountsPayable.html?a_ap_document

diagrams

There are some problems with generating the diagrams. On the one hand it allows manual moving of the tables and connections in the dia editor, and the sql2diagram tool should pick up the manual positions and reuse them. But on the other hand, it is quite tedious to move the tables and foreign key constraints all by hand.

Task 1: Use the new Database tools, instead of the ER shapes

Database shapes were only added recently to dia, and might look better than the ER shapes we are using currently.

Issues to consider: how to show which fields are part of a foreign key? At the moment, we print FK at the back of the row that describes the field name and type.

Task 2: Do an automatic layout of the tables and connections

Some discussions and first hints:

So my approach to Task 2 would be to use some good algorithms when arranging the diagram, and make sure the generated diagram has the tables in the best position (most referenced tables in the centre, tables that are most linked to each other closest to each other), and the linking constraints are not crossing any tables, and are still possible to be read.

How to use sql2diagram for OpenPetra.org

See a sample result here: diagram for the Accounts Payable sub system

See the file themed.prj in OpenPetra\db\doc; that contains already a collection of tables. You might also copy the table names from the file master_alltables.prj in the same directory.

Then you run nant dbdoc to generate the diagram file (extension .dia). You then can edit that with the dia editor (available for Linux and Windows). First, the tables are all on the invisible layer. See the instructions on the sql2diagram page how to copy the tables to the front layer, and then to arrange the tables. Next time you run nant dbdoc the constraint lines will be inserted. Please try to make them look ok by moving the lines and inserting new segments.

List of alternatives to sql2diagram

The following list of alternatives (from here and here) could be evaluated for ease of use instead of sql2diagram:

Listed earlier: