Re: [GeneX-dev] Re: ERWin File on website

[<ja...@op...>]

"Hilmar Lapp" <la...@gn...> writes:

> Quoting "Jason E. Stewart" <ja...@op...>:
> 
> > 
> > All the DB interfaces are created to be SQL generic. Instead of having
> > a bunch of hard-coded SQL ('SELECT foo FROM bar WHERE baz="NAN"') I
> > created an SQL abstraction layer using three functions
> > create_select_sql(), create_insert_sql(), and
> > create_update_sql(). With these you can plug in backends for any
> > relational DB to produce the SQL you need. 
> > 
> 
> Is this framework already available already? If so, from the GeneX repository
> or MAGEstk?

So all of the create_* functions are all in Genex-1.x, the piece that
isn't there is the XML based schema description.

> I'm trying to reverse engineer the ERD, unsuccessful so far. ERwin would
> not reverse engineer from SQL, and doesn't have an idea about Postgres.
> My next idea was port to Oracle, and reverse engineer with Designer.

One of the reasons I detested ERWin.

> Can the tool framework you mentioned reverse engineer from SQL DDL?

It's an API for helping applications build DBMS-independent queries, I
have no idea if it would be of any use for reverse engineering. I'm
actually hoping to replace the system I wrote with some of the
existing CPAN modules that do very similar things,
e.g. SQL::QueryBuilder::Simple. 

> BTW the DDL I was able to dump did not contain FK constraints (bad
> for reverse engineering). Was I missing something, or does the
> schema not contain FK constraints? (If the latter, was this a design
> decision?)

Sorry, DDL = ???

That is one of the irritating consequences of the incident I mentioned
the other day. When management of the DB and schema were taken over by
another individual, he began using pg_dump instead of maintaining the
table definition scripts. Well, GeneX was originally built on
Postgres-6.5, which didn't support foreign key constraints. By the
time we switched to Postgres-7.x which does, it was too late. The
current DB dump had thrown out all the FK constraints :-(

The good news is that in Genex-2.x, they are all back. As well as a
number of other useful constraints for column uniqueness that makes it
a lot easier to check if a given piece of data, e.g. an array design,
is already in the DB or not. This proved very useful for controlling
the insertion and updating of data.

jas.