You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Michael Gentry <bl...@gmail.com> on 2006/12/01 14:56:47 UTC
Re: Re: best practice for handling database schema changes
I found this yesterday (although I haven't read it all yet):
http://www.stepwise.com/Articles/2005/DBChanges/index.html
Not sure if it'll be helpful, but it looks similar to some ideas I had
been pondering in the past.
/dev/mrg
On 11/30/06, Michael Gentry <bl...@gmail.com> wrote:
> Currently you have to manually merge the changes just as you states.
> I should probably make an FAQ for this ...
>
> Thanks,
>
> /dev/mrg
>
>
> On 11/30/06, Tobias SCHOESSLER <To...@unvienna.org> wrote:
> >
> > hi,
> > What is the best practice for handling changes to the database schema once a
> > cayenne mapping was created?
> > We use the 'reverse engineer from database' feature to create an initial
> > mapping. But then we usually have to do manual changes like map some pks,
> > add flattened relationships, etc.
> > If then the database schema needs to be changed we usually have a problem as
> > 'reverse engineer from database' overwrites everything. There seems to be
> > the possibility to replace only selected tables but if you do this the
> > relationships are messed up. Is there a way to merge the changes with the
> > current model/mapping?
> >
> > thanks
> >
> > Tobias
>
Re: best practice for handling database schema changes
Posted by Andrus Adamchik <an...@objectstyle.org>.
That's a good one. I wonder if we should adopt something like that as
a Cayenne extension and use for things like prefs DB versioning in
the Modeler?
Andrus
On Dec 1, 2006, at 4:56 PM, Michael Gentry wrote:
> I found this yesterday (although I haven't read it all yet):
>
> http://www.stepwise.com/Articles/2005/DBChanges/index.html
>
> Not sure if it'll be helpful, but it looks similar to some ideas I had
> been pondering in the past.
>
> /dev/mrg
>
>
> On 11/30/06, Michael Gentry <bl...@gmail.com> wrote:
>> Currently you have to manually merge the changes just as you states.
>> I should probably make an FAQ for this ...
>>
>> Thanks,
>>
>> /dev/mrg
>>
>>
>> On 11/30/06, Tobias SCHOESSLER <To...@unvienna.org>
>> wrote:
>> >
>> > hi,
>> > What is the best practice for handling changes to the database
>> schema once a
>> > cayenne mapping was created?
>> > We use the 'reverse engineer from database' feature to create an
>> initial
>> > mapping. But then we usually have to do manual changes like map
>> some pks,
>> > add flattened relationships, etc.
>> > If then the database schema needs to be changed we usually have
>> a problem as
>> > 'reverse engineer from database' overwrites everything. There
>> seems to be
>> > the possibility to replace only selected tables but if you do
>> this the
>> > relationships are messed up. Is there a way to merge the
>> changes with the
>> > current model/mapping?
>> >
>> > thanks
>> >
>> > Tobias
>>
>
Re: best practice for handling database schema changes
Posted by Michael Gentry <bl...@gmail.com>.
I suspect the answer is pretty easy. It takes ~60 seconds to manually add a
new attribute to the model (the DbEntity and ObjEntity), but would take
weeks, if not longer, to write code than can properly reverse engineer the
schema and apply differences. That doesn't mean it wouldn't be a nice
feature, but it just hasn't been high on the radar.
As for commercial ORMs, I just tried in EOModeler to synchronize a schema.
It had been years since I had done so and I recalled it didn't work too
well, but that was an older version, so why not test it again? I imported
one of my test databases, saved the model, added a column (birthday date --
nothing too fancy) at the SQL prompt, then did the synchronize. The UI was
a bit confusing and after it synchronized, it had imported a brand new
column for me, but the name, data type, etc were all missing -- I'd have to
type all the data in. It didn't work so well. And then wouldn't let me
synchronize again. I would've been better off manually adding the
attribute.
I'm not trying to be a party pooper. It really would be a nifty feature,
but I haven't seen a good implementation of it before and it would be fairly
challenging to implement correctly. Also, it seems to me like it would be
most useful at the beginning of a brand new project when the schema is in
flux. On the main application I maintain, we only add a few columns a
year. Manually updating the model would easily be under 10 minutes of real
work. :-)
Just my $0.02 ...
Thanks,
/dev/mrg
On 12/4/06, Tobias SCHOESSLER <To...@unvienna.org> wrote:
>
>
> I see this discussion very interesting, but my initial question was less
> how to manage the schema changes of the database, than how to manage the
> cayenne mapping changes. I wonder what are the difficulties in implementing
> a merge of changes found in the db schema into an existing mapping when
> performing the 'reverse engineer from database function' in the modeller .
> Merging db schema changes into the mapping information seems to be a
> common operation and I wonder why it hasn't been implemented in cayenne just
> yet. Especially as cayenne seems to have a strong emphasis on the usability
> of the mapping process with its integrated modeller. Something I recall
> other ORM tools don't have at all (e.g. hibernate).
>
> (I would never ask this on a commercial help forum, nor expect a true
> answer ; ) ), but what about the competitors ? Is this 'merge schema
> changes into existing mapping' feature so exotic that it is not adhoc
> supported in other ORM tools?
>
> regards
>
> Tobias
>
>
>
> *"Malcolm Edgar" <ma...@gmail.com>*
>
> Sunday, 3 December 2006 23:49 Please respond to
> cayenne-user@incubator.apache.org
>
> To
> cayenne-user@incubator.apache.org cc
>
> Subject
> Re: best practice for handling database schema changes
>
>
>
>
>
>
> I have also used a very similar approach on another project. We used
> Java commands to perform each version update, the advantage of this
> over straight SQL files is that you can migrated data using Java logic
> into the new schema.
>
> regards Malcolm
>
> On 12/4/06, Aristedes Maniatis <ar...@ish.com.au> wrote:
> > Interesting article. We implemented something similar, but without
> > hard coding the SQL to update the schema into Java. Instead we have a
> > folder full of files named 1.sql, 2.sql, etc. Each one contains the
> > SQL required to update the database schema. Every time the server
> > application runs it reads a special table in the database with one
> > column and one record. That value is the 'schema version' from the
> > last launch of the application. If this number is lower that the
> > largest xx.sql file in the folder, then that sql file is executed and
> > the version updated.
> >
> > We do all this before Cayenne is intialised in any way in our
> > application.
> >
> > The only thing we haven't yet done is to write a junit test that
> > validates the sum total of all our incremental updates is the same as
> > a single SQL export from the current Cayenne model.
> >
> > Ari Maniatis
> >
> >
> > On 02/12/2006, at 12:56 AM, Michael Gentry wrote:
> >
> > > I found this yesterday (although I haven't read it all yet):
> > >
> > > http://www.stepwise.com/Articles/2005/DBChanges/index.html
> > >
> > > Not sure if it'll be helpful, but it looks similar to some ideas I had
> > > been pondering in the past.
> > >
> > > /dev/mrg
> > >
> > >
> > > On 11/30/06, Michael Gentry <bl...@gmail.com> wrote:
> > >> Currently you have to manually merge the changes just as you states.
> > >> I should probably make an FAQ for this ...
> > >>
> > >> Thanks,
> > >>
> > >> /dev/mrg
> > >>
> > >>
> > >> On 11/30/06, Tobias SCHOESSLER <To...@unvienna.org>
> > >> wrote:
> > >> >
> > >> > hi,
> > >> > What is the best practice for handling changes to the database
> > >> schema once a
> > >> > cayenne mapping was created?
> > >> > We use the 'reverse engineer from database' feature to create an
> > >> initial
> > >> > mapping. But then we usually have to do manual changes like map
> > >> some pks,
> > >> > add flattened relationships, etc.
> > >> > If then the database schema needs to be changed we usually have
> > >> a problem as
> > >> > 'reverse engineer from database' overwrites everything. There
> > >> seems to be
> > >> > the possibility to replace only selected tables but if you do
> > >> this the
> > >> > relationships are messed up. Is there a way to merge the
> > >> changes with the
> > >> > current model/mapping?
> > >> >
> > >> > thanks
> > >> >
> > >> > Tobias
> > >>
> >
> >
> >
> >
> >
> > -------------------------->
> > 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: best practice for handling database schema changes
Posted by Malcolm Edgar <ma...@gmail.com>.
I have also used a very similar approach on another project. We used
Java commands to perform each version update, the advantage of this
over straight SQL files is that you can migrated data using Java logic
into the new schema.
regards Malcolm
On 12/4/06, Aristedes Maniatis <ar...@ish.com.au> wrote:
> Interesting article. We implemented something similar, but without
> hard coding the SQL to update the schema into Java. Instead we have a
> folder full of files named 1.sql, 2.sql, etc. Each one contains the
> SQL required to update the database schema. Every time the server
> application runs it reads a special table in the database with one
> column and one record. That value is the 'schema version' from the
> last launch of the application. If this number is lower that the
> largest xx.sql file in the folder, then that sql file is executed and
> the version updated.
>
> We do all this before Cayenne is intialised in any way in our
> application.
>
> The only thing we haven't yet done is to write a junit test that
> validates the sum total of all our incremental updates is the same as
> a single SQL export from the current Cayenne model.
>
> Ari Maniatis
>
>
> On 02/12/2006, at 12:56 AM, Michael Gentry wrote:
>
> > I found this yesterday (although I haven't read it all yet):
> >
> > http://www.stepwise.com/Articles/2005/DBChanges/index.html
> >
> > Not sure if it'll be helpful, but it looks similar to some ideas I had
> > been pondering in the past.
> >
> > /dev/mrg
> >
> >
> > On 11/30/06, Michael Gentry <bl...@gmail.com> wrote:
> >> Currently you have to manually merge the changes just as you states.
> >> I should probably make an FAQ for this ...
> >>
> >> Thanks,
> >>
> >> /dev/mrg
> >>
> >>
> >> On 11/30/06, Tobias SCHOESSLER <To...@unvienna.org>
> >> wrote:
> >> >
> >> > hi,
> >> > What is the best practice for handling changes to the database
> >> schema once a
> >> > cayenne mapping was created?
> >> > We use the 'reverse engineer from database' feature to create an
> >> initial
> >> > mapping. But then we usually have to do manual changes like map
> >> some pks,
> >> > add flattened relationships, etc.
> >> > If then the database schema needs to be changed we usually have
> >> a problem as
> >> > 'reverse engineer from database' overwrites everything. There
> >> seems to be
> >> > the possibility to replace only selected tables but if you do
> >> this the
> >> > relationships are messed up. Is there a way to merge the
> >> changes with the
> >> > current model/mapping?
> >> >
> >> > thanks
> >> >
> >> > Tobias
> >>
>
>
>
>
>
> -------------------------->
> 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: best practice for handling database schema changes
Posted by Aristedes Maniatis <ar...@ish.com.au>.
Interesting article. We implemented something similar, but without
hard coding the SQL to update the schema into Java. Instead we have a
folder full of files named 1.sql, 2.sql, etc. Each one contains the
SQL required to update the database schema. Every time the server
application runs it reads a special table in the database with one
column and one record. That value is the 'schema version' from the
last launch of the application. If this number is lower that the
largest xx.sql file in the folder, then that sql file is executed and
the version updated.
We do all this before Cayenne is intialised in any way in our
application.
The only thing we haven't yet done is to write a junit test that
validates the sum total of all our incremental updates is the same as
a single SQL export from the current Cayenne model.
Ari Maniatis
On 02/12/2006, at 12:56 AM, Michael Gentry wrote:
> I found this yesterday (although I haven't read it all yet):
>
> http://www.stepwise.com/Articles/2005/DBChanges/index.html
>
> Not sure if it'll be helpful, but it looks similar to some ideas I had
> been pondering in the past.
>
> /dev/mrg
>
>
> On 11/30/06, Michael Gentry <bl...@gmail.com> wrote:
>> Currently you have to manually merge the changes just as you states.
>> I should probably make an FAQ for this ...
>>
>> Thanks,
>>
>> /dev/mrg
>>
>>
>> On 11/30/06, Tobias SCHOESSLER <To...@unvienna.org>
>> wrote:
>> >
>> > hi,
>> > What is the best practice for handling changes to the database
>> schema once a
>> > cayenne mapping was created?
>> > We use the 'reverse engineer from database' feature to create an
>> initial
>> > mapping. But then we usually have to do manual changes like map
>> some pks,
>> > add flattened relationships, etc.
>> > If then the database schema needs to be changed we usually have
>> a problem as
>> > 'reverse engineer from database' overwrites everything. There
>> seems to be
>> > the possibility to replace only selected tables but if you do
>> this the
>> > relationships are messed up. Is there a way to merge the
>> changes with the
>> > current model/mapping?
>> >
>> > thanks
>> >
>> > Tobias
>>
-------------------------->
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