contribute: db diagram for web2py appadmin

[Paolo Caruccio]

I was not able to succesfully install pygraphviz on my windows7 64bit enviroment but I liked Jose's idea https://groups.google.com/d/topic/web2py/cFqD1M6rkc8/discussion, so I wrote a simple addendum to appadmin in order to show a graph representation of a database. (update 3 Feb 2013: after Jose suggestions I was able to succesfully install pygraphviz)  

It's not alternative to or a replacement of Jose's graph layout because there are substantial differences.

"db diagram" is interactive (nodes are draggable and clickable) but you cannot save it as an image (anyway it's possible to use a third party application for screenshoots).

Moreover it is customizable via css (you will find here attached a css theme for reference) and it's based on jqueryUI framewok.

The nodes contain only the table name, so we can draw the layout of a database with (moderately) numerous tables.

In order to see table data you have to click on the node. I added some infos like indexed columns (currently available only for sqlite but I think it's not hard to implement for other database engines), the list of other affected  tables when we delete a row in cascade mode.

The layout (the position of nodes on the screen) is generated trough a force-directed spring algorythm. Therefore the layout is generated dinamically and it will be different each time you refresh the page.

For this scope, I adapted and translated to jQuery the original prototype source code freely distributed under the terms of a MIT-style license from http://snipplr.com/view/1950/graph-javascript-framework-version-001/

For a jquery version of this original source code you could visit http://www.graphdracula.net/

The edges are drawn by jsPlumb library ( http://www.jsplumb.org/jquery/demo.html ). All 1.x.x versions of jsPlumb are dual-licensed under both MIT and GPL version 2.

Current main limitation (update 3 Feb 2013: limitation removed and support to multiple databases in the same application): the database must have 'db' key in application databases dictionary. In other words in our model we must have 

db = DAL('sqlite://storage.sqlite',pool_size=1,check_reserved=['all'])

and not, for example, 

mydb = DAL('sqlite://storage.sqlite',pool_size=1,check_reserved=['all'])

The screen dimensions are important too: the layout will be messed on small screens.

The code has been tested on latest versions of Firefox, Chrome, Opera, IE(7,8,9) and on a very limited number of databases. So please check for errors and bugs.

Installation:

1) append the code within in the attached db_diagram.py  in your_application/controllers/appadmin.py file

2) put attached db_diagram.html in to your_application/views folder

3) create a new folder in your_application/static folder and name it "db_diagram"

4) in to latter put db_diagram.css, db_diagram_print.css, jquery.dbdiagram.js, pencildiagonals.png (an image create by me only for the css theme in bundle) (all these files are here attached)

5) download jsPlumb (jQuery release) from http://code.google.com/p/jsplumb/downloads/list and put jquery.jsPlumb-1.3.16-all-min.js file in your_application/static/db_diagram folder

update 3 Feb 2013: all the needed files, togheter with a readme, are in the attached compressed archive. I will continue to update this post, while Massimo includes "db diagram" in admin interface.

Usage:

In your appadmin page you should see a new menu item "diagram" (see image 1), click on it and you should see the layout of your "db".

That's all Folks!

[Massimo Di Pierro]

This is really nice. How about we move all the static files and the view into admin and he have the db_diagram.py code in appadmin just include form admin? we can do that easily. 

you can do

dbs = [db in globals().values() if isinstance(db.DAL)]

to get databases. There is a more efficient way:

from gluon.dal import THREAD_LOCA

mdbs = getattr(THREAD_LOCAL,'db_instances',{}).items()

dbs = []

for db_uid, db_group in mdbs: dbs += [db for db in db_group]

           

On Wednesday, January 30, 2013 10:23:21 AM UTC-6, Paolo Caruccio wrote:

I was not able to succesfully install pygraphviz on my windows7 64bit enviroment but I liked Jose's idea https://groups.google.com/d/topic/web2py/cFqD1M6rkc8/discussion, so I wrote a simple addendum to appadmin in order to show a graph representation of a database.

It's not alternative to or a replacement of Jose's graph layout because there are substantial differences.

"db diagram" is interactive (nodes are draggable and clickable) but you cannot save it as an image (anyway it's possible to use a third party application for screenshoots).

Moreover it is customizable via css (you will find here attached a css theme for reference) and it's based on jqueryUI framewok.

The nodes contain only the table name, so we can draw the layout of a database with (moderately) numerous tables.

In order to see table data you have to click on the node. I added some infos like indexed columns (currently available only for sqlite but I think it's not hard to implement for other database engines), the list of other affected  tables when we delete a row in cascade mode.

The layout (the position of nodes on the screen) is generated trough a force-directed spring algorythm. Therefore the layout is generated dinamically and it will be different each time you refresh the page.

For this scope, I adapted and translated to jQuery the original prototype source code freely distributed under the terms of a MIT-style license from http://snipplr.com/view/1950/graph-javascript-framework-version-001/

For a jquery version of this original source code you could visit http://www.graphdracula.net/

The edges are drawn by jsPlumb library ( http://www.jsplumb.org/jquery/demo.html ). All 1.x.x versions of jsPlumb are dual-licensed under both MIT and GPL version 2.

Current main limitation: the database must have 'db' key in application databases dictionary. In other words in our model we must have 

db = DAL('sqlite://storage.sqlite',pool_size=1,check_reserved=['all'])

and not, for example, 

mydb = DAL('sqlite://storage.sqlite',pool_size=1,check_reserved=['all'])

The screen dimensions are important too: the layout will be messed on small screens.

The code has been tested on latest versions of Firefox, Chrome, Opera, IE(7,8,9) and on a very limited number of databases. So please check for errors and bugs.

Installation:

1) append the code within in the attached db_diagram.py  in your_application/controllers/appadmin.py file

2) put attached db_diagram.html in to your_application/views folder

3) create a new folder in your_application/static folder and name it "db_diagram"

4) in to latter put db_diagram.css, db_diagram_print.css, jquery.dbdiagram.js, pencildiagonals.png (an image create by me only for the css theme in bundle) (all these files are here attached)

5) download jsPlumb (jQuery release) from http://code.google.com/p/jsplumb/downloads/list and put jquery.jsPlumb-1.3.16-all-min.js file in your_application/static/db_diagram folder

[Paolo Caruccio]

Massimo,

thanks for the compliments and for the suggestions.

Actually, in my mind the posted code is a sort of appadmin plugin. For this reason I separated db diagram static files from web2py ones.

About the 'db' limitation, it's due to lack of time for testing.

I'm attaching a w2p application (modified hotel management appliance) to show better how it works.

[Paolo Caruccio]

first post updated: replaced following files

db_diagram.html

corrected CDN links. Now the diagram works on https too

db_diagram.py  [please note that you must copy the code within this file and paste at the bottom of web2py_app/controllers/appadmin.py]

deleted a print statement

[LightDot]

Works nicely!

+1 to adding this into appadmin and moving the static/view files into admin. It wouldn't need to replace the existing "graph model", there is plenty of space for another button next to it ;)

Regards,
Ales

[Jose]

Very nice. Excellent work Paolo.

Jose

[Massimo Di Pierro]

Would you be opposed to turn this into an admin plugin and allow appadmin to access it?

On Wednesday, 30 January 2013 15:04:06 UTC-6, Paolo Caruccio wrote:

Massimo,

thanks for the compliments and for the suggestions.

Actually, in my mind the posted code is a sort of appadmin plugin. For this reason I separated db diagram static files from web2py ones.

About the 'db' limitation, it's due to lack of time for testing.

I'm attaching a w2p application (modified hotel management appliance) to show better how it works.

Il giorno mercoledì 30 gennaio 2013 19:55:53 UTC+1, Massimo Di Pierro ha scritto:

[Paolo Caruccio]

Absolutly not, you are free to do what do you want.

Only one note. The db_diagram.css file is not optimized because it is a reference for the users which would like customize the theme.

[Paolo Caruccio]

Jose. thank you too for the amazing idea to draw a graph of the database.

I take the opportunity to ask you where I can download a working pygraphviz build for windows.

[Massimo Di Pierro]

Can you post a link to the latest?

[Paolo Caruccio]

Here attached the latest db_diagram files in a compressed archive.

[Jose]

El viernes, 1 de febrero de 2013 15:50:43 UTC-3, Paolo Caruccio escribió:

Jose. thank you too for the amazing idea to draw a graph of the database.

I take the opportunity to ask you where I can download a working pygraphviz build for windows.

Tried with this [1] or [2]?

[1] http://pypi.python.org/pypi/pygraphviz/1.1

[2] https://groups.google.com/forum/?fromgroups=#!topic/networkx-discuss/Xy1lWfMdB6U 

[Arnon Marcus]

It may be not directly related to this topic, but I have been thinking about this for a long time now:

Writing database schemas in python in the model is very simple and decorative.

Why not make it a declarative format?
This way, it can be designed and updated with this kind of GUI tooling, as a 2-way binding.

This can even be layered on-top of the currently existing pythonic-way of writing it, using a simple parser.

It's just a matter of choosing a declarative format/protocol - preferably something SQL-related and open standard.

I am unfamiliar with this field, but I am sure there are many such formats that already exist.

This way, the schema can even be edited in whatever other external tool that supports that format.

Just as an example, a GUI graphical designer could produce something like:

"

<--! db.xml -->

<database name="MyDatabase" port="5432" type="postgres" user="postgres" host="myhost">

  <table name="MyTable">

    <field name="MyField" type="string">

  </table>

</database>
"

And then this can be used in the model as such:

"

...

# prepare a 'databases' list from the xml document that was parsed

...

for database in databases:

  db = DAL()

  ...

  # prepare a 'tables' list from the 'database' node's children...

  ...

  for table in tables:

    fields = []

    ...

    # prepare a 'tableFields' list from the 'table' node's children...

    ...

    for tableField in tableFfields:

      ...

      # convert the tableFiled node into a dictionary with it's attributes

      ...

      field = db.defineField( tableField['Name'], tableFiled[''type'] )

      fields.append(field)

   db.defineTable(table['name'], fields)

"

This can be further integrated into the DAL class itself, of course...

[Paolo Caruccio]

thank you.

package downloaded from [1]

setup.py modified following istructions on [2]

After setup.py install command all worked fine.

[Jose]

It exists [1]

Jose

[1]  https://groups.google.com/forum/?fromgroups=#!topic/web2py/DdS31FtNCjc

[Arnon Marcus]

There are many problems I can detect in this solution:

1. It is a code-generator - I was aiming for a file-format parser that implements the code, not generate it, and one that is built-into the DAL constructor, and uses conventions. I don't really care for auto-generated-comments, but I would like to be able to just include a line like:

  db = DAL('db')

and then have the constructor look for a file named "db.schema.yaml" or something - then pars it while executing the necessary directive on the spot.

There shouldn't be any declerative code at all - it is not needed - code is for imperative processing - not declerative.

2. It does not adhere to any standard that has support for in existing database-schema visual designers that have a node-based GUI.

This goes with the first point - the goal should be to not have to deal with code at all when designing the database, as again - that is a decorative process, not an imperative one... By having the entire DAL definition completely declarative, you can just use a GUI tool of your choice for schema design. It can then also enable web2py to come built-in with its own web-based application and/or appliance within the admin application, to use for managing database schemas visually for the hosted applications.

3. It is old and deprecated - it uses SQLFIELD... 

4. The way "db" is at the same level in the hierarchy as the tabled is un-intuitive

--
 
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Paolo Caruccio]

first post updated with latest version.

Il giorno venerdì 1 febbraio 2013 21:21:28 UTC+1, Massimo Di Pierro ha scritto: