Database schema tool idea
One of the things I've been pretty busy with at work is making sure our code is no longer Oracle specific. We want to be able to dump a copy of MySQL on a box and test on that.. since administering a copy of Oracle just for a team of 4 people is asinine.. (right now we share Oracle with another team, we step on each others toes a lot).
So in the process of doing this I discovered the only copy of our schema definition (it's a big one too) lives in Erwin. Yuck. So it's either create a version for MySQL in Erwin (no way, no how am I doing that) or a flat file SQL script. Of course this is about hm, 3K lines or so.. a maintenance nightmare.. not to mention having to maintain two separate definitions of the same schema.. one for Oracle, one for MySQL.
So this is the idea my boss and I discussed: A tool that will store the defintions of tables, views, primary keys, indexes, etc.. and the same tool will either create, update, drop, insert.. whatever is required to keep a schema in synch with a definition. This way I can make it non-database specific, as the tool will know which db it's working on. Schema look ups become simpler as only need to use grep.. and only one copy of the defition needs to be maintained. Need to create a new copy of the db? Just run the tool.. Neat, no?
Comments
very.
are we talking a middle man for the program, or a sort of configurator which you tell "I want X, for database type y", and it outputs the statements nessecary for you to copy and paste?
Posted by: Apokalyptik | September 27, 2002 10:53 AM
Oh, this would make a db connection and do all the work...
Posted by: kasia | September 27, 2002 10:56 AM
PowerDesigner should do everything you're talking about..reverse-engineer the logical model, then forward-engineer to other physical models, looking at the actual DB objects to apply the necessary changes. I found the UI to be much better than Erwin (if memory serves) Haven't used it with MySQL. There's a 30-day demo for occasional migrations.
Posted by: Jeff Winkler | September 28, 2002 04:48 PM
Turns out that one of my employees (Michael T. Sullivan) wrote a tool like this in probably 1987. We were maintaining Informix databases, and we were desperate to keep the "schema" files as source code. So 'dbbuild' read the schema file, the system catalogs, and generated the proper SQL code to do what you suggested. Michael was ahead of his time.
Posted by: Steve Friedl | September 30, 2002 01:21 AM