Hi Robert,
IMO generic full-featured database upgrade and versioning management
system is a problem with scope at least as big as Cayenne or maybe
even bigger. I think this is a good idea for a new open source (and
even commercial) product. I wonder what's already out there in this
area?
I observed DB evolution on a number of projects, and automating db
synchronization is anything but simple. There are two groups of
problems to be solved - upgrading schema and migrating data to the
new version.
Cayenne can be a starting point for the first problem - building
schema diffs and constructing SQL to incrementally change the schema.
DbLoader class can provide a DataMap view of the actual schema on the
fly, so you can build some merge algorithm based on that. But there
is a number of serious limitations. First is JDBC - drivers may not
provide the information about certain DB features. Second (something
that we can address to some point in the future) DbLoader ignores a
number of metadata parameters that are not relevant to Cayenne. Such
as the distinction between tables and views, indexes, triggers, etc.
Views and stored procedure schemas are reengineered, but their actual
implementations are treated as black box.
Now the second part - upgrading the data... This is the worst part. I
don't think you can automate this at all. DBAs are charging their
fees for a reason. You can have giga or even terabytes of data and
you'll have to apply changes that are pretty much arbitrary (say you
normalized one table into multiple tables, or split data in one
column in two or more other columns, etc.)... DB-specific SQL that
performs such transformation is unavoidable.
So if I am to approach writing such tool, I guess I'd go with the
general paradigm used in business software - if you can't completely
replace humans with a computer program, write a program that helps
human to do what they are already doing. In this case it probably
means management of a repository of version-specific SQL scripts and
some Cayenne-based intelligence to auto-detect version that user is
running and building the right script from repository. Something you
can manually do now with CVS... As for the version detection, I guess
the simplest thing would be a DB table that stores version explicitly.
Andrus
On Jul 21, 2005, at 2:30 AM, Robert Zeigler wrote:
> Hi all,
>
> I'm looking into the possibility of writing some stuff to "gracefully"
> handle upgrading "older" versions of a database to the current version
> of a database. Part of the core problem lies in not knowing where an
> "old" database might be starting from... it could be a "fresh install"
> scenario, where the db is starting from scratch. It could also be an
> update scenario from any of a number of previous versions of the
> database. I know the subject has come up briefly here before, and one
> suggestion was to record changes in a sort of incremental file.
> Good idea. :) But... there are a couple of... issues I have with that.
> That would basically be recording raw-sql; one of the things I like
> about cayenne is the ease with with different databases can be
> used. I'd
> rather stay away from raw sql if possible...
> So... I've started looking at what sort of api cayenne exposes for
> examining the database-structure as it is supposed to be (ie, as
> defined
> in the mapping file), and also the api, if any, for manipulating
> and/or
> querying table meta-data. I would appreciate any feedback on this
> subject before I embark down a potentially long, dead-end road. :)
> (And
> yes, I will be looking closely at the cgen ant task. :) Thanks in
> advance!!!
This archive was generated by hypermail 2.0.0 : Thu Jul 21 2005 - 03:41:37 EDT