Automating reverse engineer database model with Visio

We develop and maintain our database schema with a custom tool. SQL scripts and runtime files are generated from that tool. However, it does not provide a very good visual representation.

Lately I been using Visio to reverse engineer the schema. This is working great so far. But to be able to do this, I need to:

create an empty database
execute the generated script
start up visio
choose reverse engineering and choose the created database
select all tables, etc
wait for generation

I really want to automate this process. Step 1 and 2 are easy, but how can I automate the other steps.

I've been looking for a C# library to create Visio diagrams, but they al seem not suitable for this task.

For instance http://visioautomation.codeplex.com/ and http://www.graphviz.org/ look promising, but on a closer look they were not appropriate.

I've tried to record macro, but the macro recorder does not work in conjuction with the reverse engineering tools.

Michiel,

I am using Visio 2007 and SQLite 3.

Tonight I had a crack at reverse engineering my Sqlite3 database with success. The key is to download an ODBC driver for Sqlite. I found some here > http://www.ch-werner.de/sqliteodbc/. I installed the current one at the top of the list "sqliteodbc.exe".

Then the MSVisio set up was a bit muddled so forgive me if I do not explain the details in an exact, repeatable format.

The new installed ODBC driver should appear in Visio when you select: Database > Reverse Engineer > Setup.
Scroll down and check one of the three Sqlite drivers - I chose the "SQLite3 ODBC Driver"
Then click on "New" > Check "System Data Source" > Scroll down select "SQLite3 ODBC Driver" > Click "Finish"
On the dialog that it entitled "ODBC DNS Configuration" is where you enter your Sqlite database settings. The "Database Name" is simply the path and file name of your database. The "Data Source Name" can be anything meaningful to you as it will appear in the Visio dropdown whenever you want to reverse engineer. There are some other settings you can experiment with as well.
When you commence reverse engineering you will be asked to connect to your datasource using your credentials. At this point you will get a warning ...

Warning! You are using a Visio 'ODBC Generic Driver' to connect with a 'SQLite' DBMS datasource. By using an incompatible driver, it is possible that the catalog information retrieved will be incomplete.

Just click 'OK' and ignore this.

Unfortunately, in the next screen the option to select "Views" and "Triggers" are greyed out (you can select Tables: Primary Keys, Indexes, Foreign Keys though). This is a shame as I have defined a lot of these in my schema and I have benefited from rev-enging these in Oracle many times in the past.

That is it. Hope someone can find a fix for rev-enging the views.

Dan

why do you think? we maintain our database diagram not in visio, but we want visualization. Every time that I have to do that manually is to often, I think.

Michiel Overeem 2009-12-12 10:59:46

My feeling is that automating a set of tasks is usually *harder* than just doing the tasks manually. So do automation only if the task is repetitive and you need to do it *very* frequently, or else if it needs to be done much more rapidly than a human could do it.

Bill Karwin 2009-12-12 19:11:10

Also, you probably want to tweak the layout of the diagram to make it more readable, right? That's very hard to automate.

Mike Forman 2010-10-21 01:39:36

you are a DBA. DBA do not understand the progress of developing technology . its mission impossible to do re-factoring in relation db. Fowler wrote a book on re-factoring saying, oop should refactor. Then he wrote another on relation db, and ended in my mind as , give up, the technology is not yet ready, your dba will not agree to it. your thinking that everything should be ready upfront ,is like sending a man to the moon, yes you can do it, but it requires 5000 years of human work. do you want to do that your way?

none 2010-10-21 08:21:34

@none: I am not a DBA, I'm a developer who understands SQL and relational database design. Your comments are full of generalizations, assumptions, and exaggeration.

Bill Karwin 2010-10-21 16:52:15

@Bill, well saying that requiring a re-factoring in the design is a bad design, is saying Agile programing doesn't work. if you don't understand what i write, your DBA persona has limited you mind , and check the book recommended to understand the point before you shoot back. I AM SORRY FOR BEEING OFFENSIVE , sorry.

none 2010-10-22 19:45:57

@none: I did not say that refactoring or agile development were bad, and you shouldn't have inferred that from my answer.

Bill Karwin 2010-10-22 22:15:00

oww? So the question between us is, "Is frequent code change is re-factoring?".

none 2010-10-23 10:28:50

@none: It's a question of degree. Changing is fine, and I support that, but if you need to do it so frequently and rapidly that you think you need to automate your diagrams, then perhaps you should step back from the keyboard a little bit and think about your design first. If you strike a balance between thinking and coding, you can reduce the rate of change. I am not talking about absolutes, I know we have to embrace change in an Agile methodology. I'm talking about balance.

Bill Karwin 2010-10-23 17:32:00

>>If you need to re-generate data model diagrams so frequently that you need to resort to automation, then there's something wrong with your project.<< I'd agree. Because not having a visualize-able data model is not a good thing. How about reverse-engineering the db schema into a physical data model, cleaning that up ONCE, and from then on, maintaining the data model in the design tool ONLY, and generating update scripts to rollout changes? Checkout PowerDesigner, that one really can do it. This way it would deliver what you try to get out of damn (sorry) Visio.

TheBlastOne 2010-10-27 16:54:33

ansaurus

tags:

views:

answers:

Automating reverse engineer database model with Visio

related questions