Django database definition generation from SQL definition file --> integration with ERW

[cooper.me@…]

Immediate Feature Request:

---

Auto-generation of Python database definition code from pre-existing SQL file (could be fresh or could be exported from pre-existing database)

More Far-Sighted Feature Request:

---

ERW integration

Explanation:

---

The college I work for has a huge, intranet web-based administrative database built in PHP. While the PHP parts are hell to maintain, the database part is really quite nice.

Django lets you define a database with Python, but "Garbage In, Garbage Out" if you do not have a good relational design in the first place.

The programmer who built my college's system three years ago, a very talented and very lazy coder, used an early framework called ERW (​http://erw.dsi.unimi.it/), by Sebastiano Vigna, to build the database, administrative forms, documentation, and ER diagrams. He then wrote the reports and customized bits in PHP, which is becoming increasingly difficult to maintain. I am looking to jump ship and learn a real language and use a better framework.

That is why I am looking hard at Ruby on Rails, Zope, Turbo Gears, and Django. At this point, I am closest to going with Django. But there are parts of ERW that I am unwilling to let go of.

ERW is basically two parts: the java-based ERTOOL and the php-based interface. Let's ignore the PHP bits, since even Vigna is not interested in continuing to use PHP and says he is planning to rewrite the project in another language.

ERTOOL, on the other hand, is marvelous. It is a java .jar file that takes a single xml file and creates the following from it:
1 - A fully-normalized SQL database definition file with consistent naming conventions and foreign keys, user and group authentication/authorization, fine-grained permissions, etc.
2 - A linked and easy-to-use set of documentation in many formats that makes managing the database fun and easy.
3 - A .dot file (Graphviz) that creates a graphical representation of the database (an ER diagram), complete with hot links to the documentation.

I know you want to avoid XML situps, but this single xml file is more than justified. You write it to describe your entities and how they are related, paying no attention to how you will realize this in relational tables. You create it once and edit it only when you want to change the database structure. ERTOOL then reifies the xml representation of your data and makes an SQL definition file, a .dot Graphviz ER diagram, and DocBook documentation.

It would be wonderful if one could use ERTOOL to either produce the Django python definitions or get Django to create the Python definitions from the ERTOOL-generated standard SQL file. This would enable the designer to enjoy ERTOOL's reification functionality, documentation-generation, and .dot diagrams for the design of the database itself. Django would be used for the rest.

ERTOOL's reification and entity-to-relational-database translation (which really is stunning) is part of Vigna's research as a professor of Computer Science, and he seems more interested in ERTOOL than in the hustle-and-bustle of creating a responsive and expandable framework, so it seems to me that Django and ERW would be a good coupling. In fact, Vigna himself said that he is planning to rewrite the entire thing in a better language and make it a framework like ROR. It seems silly to duplicate efforts, when his ERTOOL would fit in with Django (or ROR or Turbo Gears, for that matter) really well.

A real integration would have Django calling ERTOOL to generate the database Python definition, the documentation, and the ER diagram from the beginning, all from within Django, but that would be something for the future.

For the more immediate present, I would love to be able to use ERTOOL to do the initial database re-design and then Django to actualize it on the web. The database is already running and filled with crucial data, and I would like to make just a few slight changes in the structure and then rebuild the interface with Django. With such a toolkit, I would feel confident enough to embark on rebuilding this whole database as a Django intranet site.

As it is, I will have to manually translate the SQL into Django database definition, hoping that the resulting database matches ERW's SQL file. From that point, I will have to make modifications by diff'ing the ERW-produced SQL file with the previous SQL file and then translating those changes by hand into the Python DB definition file.

[Adrian Holovaty]

I can tell you right now that we won't be making any special cases for this ERTOOL thing. It's *way* too much of a special-case. That said, if you (or anybody else) comes up with some sort of ERTOOL-Django bridge, we would be happy to link to it.