Re: [Gusdev-gusdev] improving GUS install/evolution

[Michael S. <msa...@pc...>]

All,

As Steve mentioned, I've been exploring manageability and administration
of the GUS schema.

As it sits today, the canonical GUS schema is maintained within an
Oracle instance here at CBIL.  The schema is evolved within this
instance-- tables added and removed, etc.  As necessary, the schema is
then "dumped" from this instance to Oracle DDL using a custom script
($GUS_HOME/bin/dumpSchema.pl).  These Oracle DDL scripts comprise the
installation scripts for new GUS installations.

The current approach has several limitations:

* Since the canonical schema is maintained inside an active database, we
have no way of tracking changes, nor do we have any way of validating
the completeness of any given schema.

* All schema changes must be applied in our instance; We cannot support
changes from the community in the same fashion that CVS would allow
(i.e. various contributors checking in changes).

* Our installation is passive in the sense that we generate a large
number of scripts, and a meta-script which calls these scripts, but we
do not detect or attempt to recover from errors.  This has led to
various issues with missed components during installation (i.e. missing
sequences).

* Our schema is represented as database objects (tables, views,
constraints, etc.) and cannot be represented or modeled in an object
orientated fashion (i.e. capturing our subclassing system).

* The generation of a Postgres compatible system is much more
unpleasant, because we first dump the schema from the database, and then
need to parse the dumped schema and translate it to postgres.

In looking to address these limitations, it has become obvious that the
canonical GUS schema should be represented outside of any given
instance, in some file that can be checked into CVS, allowing changes to
be tracked, and serving as the foundation for validation of various GUS
schema installations.

My recent work has led me to believe that his external canonical GUS
schema should be represented as XML, which provides excellent
programmatic access to the schema, and is independent of Oracle or Postgres.

I spent several weeks looking at the SQLFairy
(http://sqlfairy.sourceforge.net/) package, which allows for translation
from many sources (Oracle, Postgres, XML, etc.) to many other sources
(Oracle, Postgres, HTML Documentation, XML, etc.) This would allow us to
generate the GUS XML canonical schema (generally a one-time operation),
maintain the schema in XML, and then transform the XML to Oracle or
Postgres DDL.  Unfortunately, SQLFairy doesn't appear to be ready to
support this task-- it doesn't yet support much of the functionality we
require, the parsers are incomplete.  While I initially thought that
SQLFairy could be modified to add these items, I think that significant
architectural issues prevent it from being a solution to our needs.

I've also examined some of the other packages available, including
Ora2Pg and the Jakarta Sandbox SQL commons project.  No project provides
all of the functionality we require.  As a result, I've begun to
explore implementing our own system to provide this functionality.

I have some early thoughts about how to accomplish these goals, but I'd 
like to solicit feedback and participation from the community.  If 
you're interested on participating in a GUS Management Working Group-- 
at any level-- please let me know.  Once we have a list of interested 
parties, we'll figure out the best way to proceed-- perhaps a conference 
call at some point.

--Mike
CBIL



Steve Fischer wrote:
> folks-
> 
> some people here at cbil and also at uga have begun thinking about how 
> to improve the GUS install, and how to manage schema evolution.
> 
> the two pieces of the gus install are:
>   (1) the database schema and initial data
>   (2) the software
> 
> mike at cbil has made progress on (1), and it seems like alan, ed and 
> haiming at uga have ideas about this.
> 
> mike is promising to write to the group to give a report on his efforts.
> 
> so, maybe that will be a good way to get the discussion rolling....
> 
> if folks have ideas or energy to tackle (2) that would be great.
> 
> steve
> 
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: IT Product Guide on ITManagersJournal
> Use IT products in your business? Tell us what you think of them. Give us
> Your Opinions, Get Free ThinkGeek Gift Certificates! Click to find out more
> http://productguide.itmanagersjournal.com/guidepromo.tmpl
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev