Thanks for the thoughts. Definitely gives me something to chew on. :)
Since I'm dealing with a bit of a time crunch, I will probably look
further into the idea of a repository of sql scripts to be executed.
Something to think about, in any event.
Thanks again.
Robert
Andrus Adamchik wrote:
> 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 - 08:58:25 EDT