On Apr 15, 2008, at 1:41 PM, Aristedes Maniatis wrote:
On 15/04/2008, at 6:23 PM, Andrus Adamchik wrote:
FYI: I started a simple DB schema evolution management task in
Cayenne SVN sandbox last month (the task still has some
shortcomings, but I am already actively using it in development).
Since it doesn't really belong in Cayenne I spoke to Ant folks [1]
to maybe integrate it to Ant in some form. Will be working towards
making it an Antlib now.
[1]
http://ant.markmail.org/search/#query:+page:1+mid:zgcqr6ls5nawtdvm+state:results
Interestingly we are doing some similar work here internally now
that we are working to support multiple databases against one of our
applications (mySQL, Derby, MS SQL, Oracle). We have a current
process with a folder full of files 1.sql, 2.sql, etc which create
the database schema. Each time we update something in the model we
create a new file in that folder.
Yes, DBPatch (which actually works already) does the same thing.
Currently it requires an index file that lists arbitrarily named SQL
scripts, but we may adopt your approach as an option and use a
numbered file names without an index.
Is your ant task aimed at that sort of thing? Just today we looked
at Cayenne's functionality within the merge factory to add columns,
tables, etc but thought we'd get a bit stuck with things like
setting NOT NULL, creating indexes, etc. In the end we settled on
the simplest hack: four folders (mySQL, Derby, MS SQL, Oracle) each
with their version of the sql commands required to create the
columns, tables, indexes, etc.
Would it be a long term design goal to get more metadata into the
Cayenne model? Indexes for instance... If that was the case, we
thought that we could store a new copy of the model each time we
updated it and our code could use the existing Cayenne merge
functionality to migrate the database schema as needed.
Sure, but I think the fact that we'll get more metadata in Cayenne
will not make DBPatch obsolete. Cayenne schema merge can only operate
on a delta of two schema versions. DBPatch also deals with data
migration. So the two serve different goals. DBPatch is about DB
"versioning" across multiple installations of a DB.
I haven't thought through the cross-DB features just yet... Maybe we
can implement a mechanism based on defaults (e.g. if 90% of dbpatches
do not depend on a DB nature, you create them once, for the remaining
10% you'd create files following naming conventions ...
mypatch.sql.mysql, mypatch.sql.derby, etc.)
Andrus
