I'm having some problems that maybe someone can assist me with.
I spent some time investigating some of my issues before I decided to
finally send an email to the list. My problem exists when it comes to
using ERWin 3.5.2 (a graphical database modeller that can generate ERDs
(entity relationship diagrams) for a given schema, modify a live
database's structure, and even "reverse engineer" a database so you can
still have ERDs) with ODBC to PostgreSQL.
I generally use ERWin for the following things:
1) Ease of table creation and relationship identification
* I can visually see how the schema looks, and I often
make a million changes to the structure before I
actually want the database touched. This is great!
2) Apply changes in the schema to the live database
* I work in a mixed environment of development and live
servers, and often, I really have a lot of changes to
apply to the live database. ERWin will make temporary
tables, copy the data over, then create the new table,
and loading the data in the previous columns very
precisely. I have never lost data due to table changes.
3) Entity Relationship Diagrams
* Not only is having a half wall sized schema print out
valuable for development (no more \d <tablename> in psql
every 15 seconds) but it makes great wall art for your
company's engineering rooms.
These are what are important to me.
Things have been working fine. That is, until I needed to create a column
that needs a TIMESTAMP. ERWin provides me with DATE, DATETIME, and TIME.
Well, according to the known ODBC to PostgreSQL mappings, DATETIME should
translate over to TIMESTAMP with ease. One problem with that... When I
choose DATETIME, Erwin _automatically_ switches back over to TIME. The
program inhibits the use of DATETIME (why it even shows it is odd; if you
are gonna show me, _let me use it_ :)).
So now, I either 1) can't use erwin, or 2) i need to find a way to have
the database intercept any targetting of DATE (or maybe TIME) and treat it
as though it was a TIMESTAMP.
If the first option is the one I need to take, then I need to find some
ways to replace my 3 needs above. I unfortunately have to make changes to
a live database, quite frequently... This is due to a poor foundation
written by people before myself. So are there any tools that can assist me
changes the schema of a live db, without having to make 30 long sql
queries everytime i want to make a little change.
But if the second option is a possibility, then where should I attempt to
make this mapping? Should I modify the ODBC drivers in the postgresql
source, and anywhere it mentions DATE, identify where it actually creates
the object in postgresql?
I'm very curious to see responses for some of these issues. I spent some
time searching documentation and mailing lists, but something like this is
hard to target directly. No one really discussed these issues.
Regards,
Knight
