You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by Andrus Adamchik <an...@objectstyle.org> on 2008/04/15 10:23:11 UTC
DBPatch
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
Andrus
Re: DBPatch
Posted by Ahmed Mohombe <am...@yahoo.com>.
> 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
IMHO a much better place for such a tool (even if it's not cayenne dependent), would
be a simple Cayenne subproject, e.g. "cayenne-tools" where several such tools
could be hosted.
If these tools are helpful for many users (and get high usage), they'll raise the the
view/usage of the parent project(Cayenne) as well.
It would be also less bureaucratic to do it under Cayenne since no "a least 3 developers" would be
required, and you could simply reuse the cayenne infrastructure the way it is.
just my 2 cents,
Ahmed.
Re: DBPatch
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Apr 16, 2008, at 10:17 AM, Aristedes Maniatis wrote:
> I'd be interested in looking to see how DBPatch relates to what
> we've already done. What is the URI of the sandbox?
https://svn.apache.org/repos/asf/cayenne/sandbox/DBPatch/
Andrus
Re: DBPatch
Posted by Aristedes Maniatis <ar...@ish.com.au>.
On 16/04/2008, at 4:07 PM, Andrus Adamchik wrote:
>
> On Apr 16, 2008, at 3:08 AM, Aristedes Maniatis wrote:
>
>> My only other comment here is that ant may not be the best tool for
>> deployed systems.
>
> Let's elaborate on that a bit. I'd like to have a better
> understanding of the scenarios we may be dealing with. I have two in
> mind:
>
> 1. A group of developers works on the same project, each with his
> own local copy of the DB. Each developer can introduce schema/data
> changes as a part of coding. DBPatch would serve a purpose of
> bringing all local DB's in sync with the state of the code on trunk.
>
> 2. After a cycle of development, a deployment is done to an existing
> production database. DBPatch is run sometime after .war assembly
> (assuming that's a web app), and before deploying the wars to the
> server. Here the assumption is that a (remote) DB server is
> accessible from the build machine.
3. The application is given to a customer and it automatically updates
the production database when it first runs.
> What other scenarios I'm missing that won't allow using Ant? Shrink-
> wrapped software? As for DBPatch specifically, the plan to code it
> in such a way that Ant interface is simply a frontend to a Java
> class that can be run standalone.
I see, so it isn't so much an ant task as a useful library which is
not specifically ant or Cayenne centric but works nicely alongside
either. The way we use this workflow is on server startup, after the
database is started, but before Cayenne's model is initialised. It is
built into the application itself. For us, that means both 1 and 3
above. I'd be interested in looking to see how DBPatch relates to what
we've already done. What is the URI of the sandbox?
Ari Maniatis
-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: DBPatch
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Apr 16, 2008, at 3:08 AM, Aristedes Maniatis wrote:
> My only other comment here is that ant may not be the best tool for
> deployed systems.
Let's elaborate on that a bit. I'd like to have a better understanding
of the scenarios we may be dealing with. I have two in mind:
1. A group of developers works on the same project, each with his own
local copy of the DB. Each developer can introduce schema/data changes
as a part of coding. DBPatch would serve a purpose of bringing all
local DB's in sync with the state of the code on trunk.
2. After a cycle of development, a deployment is done to an existing
production database. DBPatch is run sometime after .war assembly
(assuming that's a web app), and before deploying the wars to the
server. Here the assumption is that a (remote) DB server is accessible
from the build machine.
What other scenarios I'm missing that won't allow using Ant? Shrink-
wrapped software? As for DBPatch specifically, the plan to code it in
such a way that Ant interface is simply a frontend to a Java class
that can be run standalone.
Andrus
Re: DBPatch
Posted by Aristedes Maniatis <ar...@ish.com.au>.
On 15/04/2008, at 10:07 PM, Andrus Adamchik wrote:
> 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.
Yes, we thought that if we ever reached a goal of versioning through
storing multiple Cayenne models, that we'd still need a post-schema-
update hook to run SQL needed for the data upgrade.
> 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.)
We found that lots of things had to be changed between DBs. VARCHAR ->
VARCHAR2 in Oracle, VARCHAR(5000) in Derby -> TEXT in mySQL. Different
ways of dropping and creating indexes, etc. The differences are
substantial. Our initial idea was preprocessing statements:
#mysql
CREATE TABLE....
#end
But it seemed like too much work for the reward, so we just settled on
folders full of (almost) duplicate files. Keep an eye out for a commit
from Matthias later this week to onCourse which will implement all this.
My only other comment here is that ant may not be the best tool for
deployed systems.
Regards
Ari
-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: DBPatch
Posted by Andrus Adamchik <an...@objectstyle.org>.
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
Re: DBPatch
Posted by Aristedes Maniatis <ar...@ish.com.au>.
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.
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.
Cheers
Ari
-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A