SQL scheme conversion script for MySQL, PostgreSQL, SQLite …

Question

I’m looking for a script that allows me to define my database relations in a simple model file and have the possibility to convert this scheme definition to the explicit formats for MySQL, PostgreSQL and SQLite. The format could be similar to the definitions you would do in activerecord, so if all fails, I will somehow go with AR if I can manage it. But maybe there is an even better solution for the problem.

The main issue so far is with the autoincrement fields. I don’t do any overly complicated stuff in my db scheme.

Answer 1

DBAs sometimes have a data modeling tool that keeps the database model documented and that can create (and maintain) the database script in different database engine syntaxes.

One such (less expensive) examples is toad modeler. More expensive tools can go in the thousands of dollars - ERWin comes to mind.

But you seem to be looking for something way simpler. And I think you already have your answer - ActiveRecord would do it just fine (and give you migrations while at it). Django's models are another possibility.

---

Edit:

I'm not an Active Record guy... But in Django you would do ./manage.py sqlall <appname> - that would spill out all the create statements, including for indexes.

In your case, <appname> will probably be the sole app you need. The only two files you will ever touch are settings.py (to define the database configuration) and <appname>/models.py (to define the database models).

The Django tutorial will be a good place to start; particular emphasis on creating models.

Hope that helps.

---

Edit 2:

Hmmm... Your mention of "Free" made me thing of "... as in beer" and I googled "open source data modeling". Found this:

http://www.sqlpower.ca/page/architect

I know nothing about it other than it claims to do forward/reverse engineers to PostgreSQL and MySQL.

I think it is worth you taking a look - much better for your purposes than trying to learn Django's ORM layer. Especially if it is indeed free. (Actually, I'm going to check it out myself).

Answer 2

SQL::Translator is a great tool for this. You would probably have to write some frontend code to understand whatever kind of source file format you have in mind and turn it into a Schema object (or at least a YAML representation of one) but once that's done, sqlt can do the rest.

Answer 3

For general information: The method that I’ve now chosen to use is with the Ruby Library Datamapper which I found easier to setup than most of the other solutions.