Cayenne and migrations

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

Cayenne and migrations

Hugi Thordarson
Hi all.
In EOF/WOnder we have the most swesome ERXMigrations to manage changes in the data model between versions, i.e. upgrades of the schema (and downgrades, if applicable).

I see that some years ago there was discussion of an API to handle this in Cayenne ( http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-migrations/ ). but how’s the situation today? Is there something in/for Cayenne to do this, and if not, what tools are people using to manage versioning of their DB schemas?

Cheers,
- hugi
Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

Michael Gentry-2
Hi Hugi,

We manage schema changes outside of Cayenne using Flyway (could also use
Liquibase).  Any schema changes we make are updated by hand in Cayenne
Modeler.  This works fairly well for us and fits in with our automated
builds/etc.  Perhaps not the answer you were looking for, though!

mrg


On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]> wrote:

> Hi all.
> In EOF/WOnder we have the most swesome ERXMigrations to manage changes in
> the data model between versions, i.e. upgrades of the schema (and
> downgrades, if applicable).
>
> I see that some years ago there was discussion of an API to handle this in
> Cayenne ( http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
> migrations/ ). but how’s the situation today? Is there something in/for
> Cayenne to do this, and if not, what tools are people using to manage
> versioning of their DB schemas?
>
> Cheers,
> - hugi
Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

John Huss
I'm developing and using cayenne-migrations. It works fine for me and has a
very similar approach to ERXMigrations.  I don't think others are using it
though.  It has the advantage of being able to auto-generate the migration
code from your cayenne model (DataMap), where I think the others require
hand coding.  On the other hand, sometimes having all pure SQL statements
instead of mostly java code is useful.  Good luck!

John

On Thu, Feb 9, 2017 at 9:15 AM Michael Gentry <[hidden email]> wrote:

> Hi Hugi,
>
> We manage schema changes outside of Cayenne using Flyway (could also use
> Liquibase).  Any schema changes we make are updated by hand in Cayenne
> Modeler.  This works fairly well for us and fits in with our automated
> builds/etc.  Perhaps not the answer you were looking for, though!
>
> mrg
>
>
> On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]> wrote:
>
> > Hi all.
> > In EOF/WOnder we have the most swesome ERXMigrations to manage changes in
> > the data model between versions, i.e. upgrades of the schema (and
> > downgrades, if applicable).
> >
> > I see that some years ago there was discussion of an API to handle this
> in
> > Cayenne ( http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
> > migrations/ ). but how’s the situation today? Is there something in/for
> > Cayenne to do this, and if not, what tools are people using to manage
> > versioning of their DB schemas?
> >
> > Cheers,
> > - hugi
>
Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

Michael Gentry-2
Yeah, all of our migration scripts are hand-coded SQL that we let Flyway
then manage.  We don't have the option of having the application run the
migration scripts.  We have to get one of our DBAs to do it using an admin
account so they can create/drop/etc anything required, so we have set them
up to be able to run Flyway migrations for us.



On Thu, Feb 9, 2017 at 10:36 AM, John Huss <[hidden email]> wrote:

> I'm developing and using cayenne-migrations. It works fine for me and has a
> very similar approach to ERXMigrations.  I don't think others are using it
> though.  It has the advantage of being able to auto-generate the migration
> code from your cayenne model (DataMap), where I think the others require
> hand coding.  On the other hand, sometimes having all pure SQL statements
> instead of mostly java code is useful.  Good luck!
>
> John
>
> On Thu, Feb 9, 2017 at 9:15 AM Michael Gentry <[hidden email]>
> wrote:
>
> > Hi Hugi,
> >
> > We manage schema changes outside of Cayenne using Flyway (could also use
> > Liquibase).  Any schema changes we make are updated by hand in Cayenne
> > Modeler.  This works fairly well for us and fits in with our automated
> > builds/etc.  Perhaps not the answer you were looking for, though!
> >
> > mrg
> >
> >
> > On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]>
> wrote:
> >
> > > Hi all.
> > > In EOF/WOnder we have the most swesome ERXMigrations to manage changes
> in
> > > the data model between versions, i.e. upgrades of the schema (and
> > > downgrades, if applicable).
> > >
> > > I see that some years ago there was discussion of an API to handle this
> > in
> > > Cayenne ( http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
> > > migrations/ ). but how’s the situation today? Is there something in/for
> > > Cayenne to do this, and if not, what tools are people using to manage
> > > versioning of their DB schemas?
> > >
> > > Cheers,
> > > - hugi
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

Hugi Thordarson
In reply to this post by Michael Gentry-2
Thanks Michael, we might look into that.

- hugi


> On 9. feb. 2017, at 15:15, Michael Gentry <[hidden email]> wrote:
>
> Hi Hugi,
>
> We manage schema changes outside of Cayenne using Flyway (could also use
> Liquibase).  Any schema changes we make are updated by hand in Cayenne
> Modeler.  This works fairly well for us and fits in with our automated
> builds/etc.  Perhaps not the answer you were looking for, though!
>
> mrg
>
>
> On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]> wrote:
>
>> Hi all.
>> In EOF/WOnder we have the most swesome ERXMigrations to manage changes in
>> the data model between versions, i.e. upgrades of the schema (and
>> downgrades, if applicable).
>>
>> I see that some years ago there was discussion of an API to handle this in
>> Cayenne ( http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
>> migrations/ ). but how’s the situation today? Is there something in/for
>> Cayenne to do this, and if not, what tools are people using to manage
>> versioning of their DB schemas?
>>
>> Cheers,
>> - hugi

Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

Hugi Thordarson
In reply to this post by John Huss
Hi John,
that’s very interesting. Is your current work public or is the most recent public work in the SVN-repo I mentioned?

Cheers,
- hugi


> On 9. feb. 2017, at 15:36, John Huss <[hidden email]> wrote:
>
> I'm developing and using cayenne-migrations. It works fine for me and has a
> very similar approach to ERXMigrations.  I don't think others are using it
> though.  It has the advantage of being able to auto-generate the migration
> code from your cayenne model (DataMap), where I think the others require
> hand coding.  On the other hand, sometimes having all pure SQL statements
> instead of mostly java code is useful.  Good luck!
>
> John
>
> On Thu, Feb 9, 2017 at 9:15 AM Michael Gentry <[hidden email]> wrote:
>
>> Hi Hugi,
>>
>> We manage schema changes outside of Cayenne using Flyway (could also use
>> Liquibase).  Any schema changes we make are updated by hand in Cayenne
>> Modeler.  This works fairly well for us and fits in with our automated
>> builds/etc.  Perhaps not the answer you were looking for, though!
>>
>> mrg
>>
>>
>> On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]> wrote:
>>
>>> Hi all.
>>> In EOF/WOnder we have the most swesome ERXMigrations to manage changes in
>>> the data model between versions, i.e. upgrades of the schema (and
>>> downgrades, if applicable).
>>>
>>> I see that some years ago there was discussion of an API to handle this
>> in
>>> Cayenne ( http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
>>> migrations/ ). but how’s the situation today? Is there something in/for
>>> Cayenne to do this, and if not, what tools are people using to manage
>>> versioning of their DB schemas?
>>>
>>> Cheers,
>>> - hugi
>>

Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

John Huss
It's current except for a single small change.  I seem to have lost the
push url, so I need to get it working again to update it.  But it would be
fine for playing with as is.

On Thu, Feb 9, 2017 at 9:45 AM Hugi Thordarson <[hidden email]> wrote:

> Hi John,
> that’s very interesting. Is your current work public or is the most recent
> public work in the SVN-repo I mentioned?
>
> Cheers,
> - hugi
>
>
> > On 9. feb. 2017, at 15:36, John Huss <[hidden email]> wrote:
> >
> > I'm developing and using cayenne-migrations. It works fine for me and
> has a
> > very similar approach to ERXMigrations.  I don't think others are using
> it
> > though.  It has the advantage of being able to auto-generate the
> migration
> > code from your cayenne model (DataMap), where I think the others require
> > hand coding.  On the other hand, sometimes having all pure SQL statements
> > instead of mostly java code is useful.  Good luck!
> >
> > John
> >
> > On Thu, Feb 9, 2017 at 9:15 AM Michael Gentry <[hidden email]>
> wrote:
> >
> >> Hi Hugi,
> >>
> >> We manage schema changes outside of Cayenne using Flyway (could also use
> >> Liquibase).  Any schema changes we make are updated by hand in Cayenne
> >> Modeler.  This works fairly well for us and fits in with our automated
> >> builds/etc.  Perhaps not the answer you were looking for, though!
> >>
> >> mrg
> >>
> >>
> >> On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]>
> wrote:
> >>
> >>> Hi all.
> >>> In EOF/WOnder we have the most swesome ERXMigrations to manage changes
> in
> >>> the data model between versions, i.e. upgrades of the schema (and
> >>> downgrades, if applicable).
> >>>
> >>> I see that some years ago there was discussion of an API to handle this
> >> in
> >>> Cayenne ( http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
> >>> migrations/ ). but how’s the situation today? Is there something in/for
> >>> Cayenne to do this, and if not, what tools are people using to manage
> >>> versioning of their DB schemas?
> >>>
> >>> Cheers,
> >>> - hugi
> >>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

Musall, Maik
Hi John,

how do you (and everyone else) feel about including this in the main repo after polishing?

I'm working with Hugi here on a project and would like to continue using this style of
migrations because our entire environment is geared towards it. I'd love for this to be in
the main cayenne repo so we can submit our improvements.

Maik

> Am 09.02.2017 um 15:59 schrieb John Huss <[hidden email]>:
>
> It's current except for a single small change.  I seem to have lost the
> push url, so I need to get it working again to update it.  But it would be
> fine for playing with as is.
>
> On Thu, Feb 9, 2017 at 9:45 AM Hugi Thordarson <[hidden email]> wrote:
>
>> Hi John,
>> that’s very interesting. Is your current work public or is the most recent
>> public work in the SVN-repo I mentioned?
>>
>> Cheers,
>> - hugi
>>
>>
>>> On 9. feb. 2017, at 15:36, John Huss <[hidden email]> wrote:
>>>
>>> I'm developing and using cayenne-migrations. It works fine for me and
>> has a
>>> very similar approach to ERXMigrations.  I don't think others are using
>> it
>>> though.  It has the advantage of being able to auto-generate the
>> migration
>>> code from your cayenne model (DataMap), where I think the others require
>>> hand coding.  On the other hand, sometimes having all pure SQL statements
>>> instead of mostly java code is useful.  Good luck!
>>>
>>> John
>>>
>>> On Thu, Feb 9, 2017 at 9:15 AM Michael Gentry <[hidden email]>
>> wrote:
>>>
>>>> Hi Hugi,
>>>>
>>>> We manage schema changes outside of Cayenne using Flyway (could also use
>>>> Liquibase).  Any schema changes we make are updated by hand in Cayenne
>>>> Modeler.  This works fairly well for us and fits in with our automated
>>>> builds/etc.  Perhaps not the answer you were looking for, though!
>>>>
>>>> mrg
>>>>
>>>>
>>>> On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]>
>> wrote:
>>>>
>>>>> Hi all.
>>>>> In EOF/WOnder we have the most swesome ERXMigrations to manage changes
>> in
>>>>> the data model between versions, i.e. upgrades of the schema (and
>>>>> downgrades, if applicable).
>>>>>
>>>>> I see that some years ago there was discussion of an API to handle this
>>>> in
>>>>> Cayenne ( http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
>>>>> migrations/ ). but how’s the situation today? Is there something in/for
>>>>> Cayenne to do this, and if not, what tools are people using to manage
>>>>> versioning of their DB schemas?
>>>>>
>>>>> Cheers,
>>>>> - hugi
>>>>
>>
>>

Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

John Huss
I pushed the changes I had pending - there was more than I thought.

I'm fine with it going in, but I'm not sure that the community agrees.
Since this can live as an independent project / jar there isn't really a
need to merge it into the main cayenne repo.  But if we are going to keep
it separate we should move it to github or something where participation is
easier.

Another issue - I'm pretty sure this won't build or run against cayenne's
master anymore due the to refactoring of DbMerger stuff.  But I haven't
tried.

On Thu, Feb 9, 2017 at 1:14 PM Musall, Maik <[hidden email]> wrote:

> Hi John,
>
> how do you (and everyone else) feel about including this in the main repo
> after polishing?
>
> I'm working with Hugi here on a project and would like to continue using
> this style of
> migrations because our entire environment is geared towards it. I'd love
> for this to be in
> the main cayenne repo so we can submit our improvements.
>
> Maik
>
> > Am 09.02.2017 um 15:59 schrieb John Huss <[hidden email]>:
> >
> > It's current except for a single small change.  I seem to have lost the
> > push url, so I need to get it working again to update it.  But it would
> be
> > fine for playing with as is.
> >
> > On Thu, Feb 9, 2017 at 9:45 AM Hugi Thordarson <[hidden email]> wrote:
> >
> >> Hi John,
> >> that’s very interesting. Is your current work public or is the most
> recent
> >> public work in the SVN-repo I mentioned?
> >>
> >> Cheers,
> >> - hugi
> >>
> >>
> >>> On 9. feb. 2017, at 15:36, John Huss <[hidden email]> wrote:
> >>>
> >>> I'm developing and using cayenne-migrations. It works fine for me and
> >> has a
> >>> very similar approach to ERXMigrations.  I don't think others are using
> >> it
> >>> though.  It has the advantage of being able to auto-generate the
> >> migration
> >>> code from your cayenne model (DataMap), where I think the others
> require
> >>> hand coding.  On the other hand, sometimes having all pure SQL
> statements
> >>> instead of mostly java code is useful.  Good luck!
> >>>
> >>> John
> >>>
> >>> On Thu, Feb 9, 2017 at 9:15 AM Michael Gentry <[hidden email]>
> >> wrote:
> >>>
> >>>> Hi Hugi,
> >>>>
> >>>> We manage schema changes outside of Cayenne using Flyway (could also
> use
> >>>> Liquibase).  Any schema changes we make are updated by hand in Cayenne
> >>>> Modeler.  This works fairly well for us and fits in with our automated
> >>>> builds/etc.  Perhaps not the answer you were looking for, though!
> >>>>
> >>>> mrg
> >>>>
> >>>>
> >>>> On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]>
> >> wrote:
> >>>>
> >>>>> Hi all.
> >>>>> In EOF/WOnder we have the most swesome ERXMigrations to manage
> changes
> >> in
> >>>>> the data model between versions, i.e. upgrades of the schema (and
> >>>>> downgrades, if applicable).
> >>>>>
> >>>>> I see that some years ago there was discussion of an API to handle
> this
> >>>> in
> >>>>> Cayenne ( http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
> >>>>> migrations/ ). but how’s the situation today? Is there something
> in/for
> >>>>> Cayenne to do this, and if not, what tools are people using to manage
> >>>>> versioning of their DB schemas?
> >>>>>
> >>>>> Cheers,
> >>>>> - hugi
> >>>>
> >>
> >>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

Andrus Adamchik
FWIW, the workflow I am using is "DB-first", and Cayenne 4.0 is providing the tools to make it practical and mostly automated:

1. Use a migration tool like Flyway or Liquibase to code your migrations in SQL (even more so when wrapped in bootique-liquibase / bootique-flyway).
2. Run cdbimport from Maven/Ant/Gradle to sync Cayenne model from DB.
3. In the modeler fix any object naming discrepancies, map custom value objects, map inheritance.
4. Run cgen to sync Java classes from Cayenne model
5. Rinse and repeat.

Can someone please explain the workflow with ERX|cayenne migrations? What are the advantages over the approach above? Does it handle data migrations or only the schema?

Andrus


> On Feb 10, 2017, at 7:06 AM, John Huss <[hidden email]> wrote:
>
> I pushed the changes I had pending - there was more than I thought.
>
> I'm fine with it going in, but I'm not sure that the community agrees.
> Since this can live as an independent project / jar there isn't really a
> need to merge it into the main cayenne repo.  But if we are going to keep
> it separate we should move it to github or something where participation is
> easier.
>
> Another issue - I'm pretty sure this won't build or run against cayenne's
> master anymore due the to refactoring of DbMerger stuff.  But I haven't
> tried.
>
> On Thu, Feb 9, 2017 at 1:14 PM Musall, Maik <[hidden email]> wrote:
>
>> Hi John,
>>
>> how do you (and everyone else) feel about including this in the main repo
>> after polishing?
>>
>> I'm working with Hugi here on a project and would like to continue using
>> this style of
>> migrations because our entire environment is geared towards it. I'd love
>> for this to be in
>> the main cayenne repo so we can submit our improvements.
>>
>> Maik
>>
>>> Am 09.02.2017 um 15:59 schrieb John Huss <[hidden email]>:
>>>
>>> It's current except for a single small change.  I seem to have lost the
>>> push url, so I need to get it working again to update it.  But it would
>> be
>>> fine for playing with as is.
>>>
>>> On Thu, Feb 9, 2017 at 9:45 AM Hugi Thordarson <[hidden email]> wrote:
>>>
>>>> Hi John,
>>>> that’s very interesting. Is your current work public or is the most
>> recent
>>>> public work in the SVN-repo I mentioned?
>>>>
>>>> Cheers,
>>>> - hugi
>>>>
>>>>
>>>>> On 9. feb. 2017, at 15:36, John Huss <[hidden email]> wrote:
>>>>>
>>>>> I'm developing and using cayenne-migrations. It works fine for me and
>>>> has a
>>>>> very similar approach to ERXMigrations.  I don't think others are using
>>>> it
>>>>> though.  It has the advantage of being able to auto-generate the
>>>> migration
>>>>> code from your cayenne model (DataMap), where I think the others
>> require
>>>>> hand coding.  On the other hand, sometimes having all pure SQL
>> statements
>>>>> instead of mostly java code is useful.  Good luck!
>>>>>
>>>>> John
>>>>>
>>>>> On Thu, Feb 9, 2017 at 9:15 AM Michael Gentry <[hidden email]>
>>>> wrote:
>>>>>
>>>>>> Hi Hugi,
>>>>>>
>>>>>> We manage schema changes outside of Cayenne using Flyway (could also
>> use
>>>>>> Liquibase).  Any schema changes we make are updated by hand in Cayenne
>>>>>> Modeler.  This works fairly well for us and fits in with our automated
>>>>>> builds/etc.  Perhaps not the answer you were looking for, though!
>>>>>>
>>>>>> mrg
>>>>>>
>>>>>>
>>>>>> On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]>
>>>> wrote:
>>>>>>
>>>>>>> Hi all.
>>>>>>> In EOF/WOnder we have the most swesome ERXMigrations to manage
>> changes
>>>> in
>>>>>>> the data model between versions, i.e. upgrades of the schema (and
>>>>>>> downgrades, if applicable).
>>>>>>>
>>>>>>> I see that some years ago there was discussion of an API to handle
>> this
>>>>>> in
>>>>>>> Cayenne ( http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
>>>>>>> migrations/ ). but how’s the situation today? Is there something
>> in/for
>>>>>>> Cayenne to do this, and if not, what tools are people using to manage
>>>>>>> versioning of their DB schemas?
>>>>>>>
>>>>>>> Cheers,
>>>>>>> - hugi
>>>>>>
>>>>
>>>>
>>
>>

Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

Andrus Adamchik
> 1. Use a migration tool like Flyway or Liquibase to code your migrations in SQL (even more so when wrapped in bootique-liquibase / bootique-flyway).

"even more so" -> "especially easy to run"


> On Feb 10, 2017, at 9:41 AM, Andrus Adamchik <[hidden email]> wrote:
>
> FWIW, the workflow I am using is "DB-first", and Cayenne 4.0 is providing the tools to make it practical and mostly automated:
>
> 1. Use a migration tool like Flyway or Liquibase to code your migrations in SQL (even more so when wrapped in bootique-liquibase / bootique-flyway).
> 2. Run cdbimport from Maven/Ant/Gradle to sync Cayenne model from DB.
> 3. In the modeler fix any object naming discrepancies, map custom value objects, map inheritance.
> 4. Run cgen to sync Java classes from Cayenne model
> 5. Rinse and repeat.
>
> Can someone please explain the workflow with ERX|cayenne migrations? What are the advantages over the approach above? Does it handle data migrations or only the schema?
>
> Andrus
>
>
>> On Feb 10, 2017, at 7:06 AM, John Huss <[hidden email]> wrote:
>>
>> I pushed the changes I had pending - there was more than I thought.
>>
>> I'm fine with it going in, but I'm not sure that the community agrees.
>> Since this can live as an independent project / jar there isn't really a
>> need to merge it into the main cayenne repo.  But if we are going to keep
>> it separate we should move it to github or something where participation is
>> easier.
>>
>> Another issue - I'm pretty sure this won't build or run against cayenne's
>> master anymore due the to refactoring of DbMerger stuff.  But I haven't
>> tried.
>>
>> On Thu, Feb 9, 2017 at 1:14 PM Musall, Maik <[hidden email]> wrote:
>>
>>> Hi John,
>>>
>>> how do you (and everyone else) feel about including this in the main repo
>>> after polishing?
>>>
>>> I'm working with Hugi here on a project and would like to continue using
>>> this style of
>>> migrations because our entire environment is geared towards it. I'd love
>>> for this to be in
>>> the main cayenne repo so we can submit our improvements.
>>>
>>> Maik
>>>
>>>> Am 09.02.2017 um 15:59 schrieb John Huss <[hidden email]>:
>>>>
>>>> It's current except for a single small change.  I seem to have lost the
>>>> push url, so I need to get it working again to update it.  But it would
>>> be
>>>> fine for playing with as is.
>>>>
>>>> On Thu, Feb 9, 2017 at 9:45 AM Hugi Thordarson <[hidden email]> wrote:
>>>>
>>>>> Hi John,
>>>>> that’s very interesting. Is your current work public or is the most
>>> recent
>>>>> public work in the SVN-repo I mentioned?
>>>>>
>>>>> Cheers,
>>>>> - hugi
>>>>>
>>>>>
>>>>>> On 9. feb. 2017, at 15:36, John Huss <[hidden email]> wrote:
>>>>>>
>>>>>> I'm developing and using cayenne-migrations. It works fine for me and
>>>>> has a
>>>>>> very similar approach to ERXMigrations.  I don't think others are using
>>>>> it
>>>>>> though.  It has the advantage of being able to auto-generate the
>>>>> migration
>>>>>> code from your cayenne model (DataMap), where I think the others
>>> require
>>>>>> hand coding.  On the other hand, sometimes having all pure SQL
>>> statements
>>>>>> instead of mostly java code is useful.  Good luck!
>>>>>>
>>>>>> John
>>>>>>
>>>>>> On Thu, Feb 9, 2017 at 9:15 AM Michael Gentry <[hidden email]>
>>>>> wrote:
>>>>>>
>>>>>>> Hi Hugi,
>>>>>>>
>>>>>>> We manage schema changes outside of Cayenne using Flyway (could also
>>> use
>>>>>>> Liquibase).  Any schema changes we make are updated by hand in Cayenne
>>>>>>> Modeler.  This works fairly well for us and fits in with our automated
>>>>>>> builds/etc.  Perhaps not the answer you were looking for, though!
>>>>>>>
>>>>>>> mrg
>>>>>>>
>>>>>>>
>>>>>>> On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]>
>>>>> wrote:
>>>>>>>
>>>>>>>> Hi all.
>>>>>>>> In EOF/WOnder we have the most swesome ERXMigrations to manage
>>> changes
>>>>> in
>>>>>>>> the data model between versions, i.e. upgrades of the schema (and
>>>>>>>> downgrades, if applicable).
>>>>>>>>
>>>>>>>> I see that some years ago there was discussion of an API to handle
>>> this
>>>>>>> in
>>>>>>>> Cayenne ( http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
>>>>>>>> migrations/ ). but how’s the situation today? Is there something
>>> in/for
>>>>>>>> Cayenne to do this, and if not, what tools are people using to manage
>>>>>>>> versioning of their DB schemas?
>>>>>>>>
>>>>>>>> Cheers,
>>>>>>>> - hugi
>>>>>>>
>>>>>
>>>>>
>>>
>>>
>

Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

John Huss
> Can someone please explain the workflow with ERX|cayenne migrations? What
are the advantages over the approach above? Does it handle data migrations
or only the schema?

Mainly schema migrations.

I think the advantages are:
1) Cross-database support from a single representation. So you can run the
same migrations on different databases without having to code specifically
to each DB. This seems to fit well within an ORM.
2) Auto-generation of java migration code from DataMap. This is currently
only supported for the initial migration (whole database), but you can
still use that to copy/paste code for new tables.  In the future it could
connect to your DB to generate a delta migration automatically, but I'm not
sure it would be worth doing.
3) You retain the ability to write raw SQL as part of the migration, and
this can be targeted at a specific database vendor if needed.  This is how
data migrations would be handled.
4) In the future the library could optimize DB changes, like combining
multiple add column statements into a single statement, which could provide
better performance.

On Thu, Feb 9, 2017 at 4:45 PM Andrus Adamchik <[hidden email]>
wrote:

> > 1. Use a migration tool like Flyway or Liquibase to code your migrations
> in SQL (even more so when wrapped in bootique-liquibase / bootique-flyway).
>
> "even more so" -> "especially easy to run"
>
>
> > On Feb 10, 2017, at 9:41 AM, Andrus Adamchik <[hidden email]>
> wrote:
> >
> > FWIW, the workflow I am using is "DB-first", and Cayenne 4.0 is
> providing the tools to make it practical and mostly automated:
> >
> > 1. Use a migration tool like Flyway or Liquibase to code your migrations
> in SQL (even more so when wrapped in bootique-liquibase / bootique-flyway).
> > 2. Run cdbimport from Maven/Ant/Gradle to sync Cayenne model from DB.
> > 3. In the modeler fix any object naming discrepancies, map custom value
> objects, map inheritance.
> > 4. Run cgen to sync Java classes from Cayenne model
> > 5. Rinse and repeat.
> >
> > Can someone please explain the workflow with ERX|cayenne migrations?
> What are the advantages over the approach above? Does it handle data
> migrations or only the schema?
> >
> > Andrus
> >
> >
> >> On Feb 10, 2017, at 7:06 AM, John Huss <[hidden email]> wrote:
> >>
> >> I pushed the changes I had pending - there was more than I thought.
> >>
> >> I'm fine with it going in, but I'm not sure that the community agrees.
> >> Since this can live as an independent project / jar there isn't really a
> >> need to merge it into the main cayenne repo.  But if we are going to
> keep
> >> it separate we should move it to github or something where
> participation is
> >> easier.
> >>
> >> Another issue - I'm pretty sure this won't build or run against
> cayenne's
> >> master anymore due the to refactoring of DbMerger stuff.  But I haven't
> >> tried.
> >>
> >> On Thu, Feb 9, 2017 at 1:14 PM Musall, Maik <[hidden email]>
> wrote:
> >>
> >>> Hi John,
> >>>
> >>> how do you (and everyone else) feel about including this in the main
> repo
> >>> after polishing?
> >>>
> >>> I'm working with Hugi here on a project and would like to continue
> using
> >>> this style of
> >>> migrations because our entire environment is geared towards it. I'd
> love
> >>> for this to be in
> >>> the main cayenne repo so we can submit our improvements.
> >>>
> >>> Maik
> >>>
> >>>> Am 09.02.2017 um 15:59 schrieb John Huss <[hidden email]>:
> >>>>
> >>>> It's current except for a single small change.  I seem to have lost
> the
> >>>> push url, so I need to get it working again to update it.  But it
> would
> >>> be
> >>>> fine for playing with as is.
> >>>>
> >>>> On Thu, Feb 9, 2017 at 9:45 AM Hugi Thordarson <[hidden email]>
> wrote:
> >>>>
> >>>>> Hi John,
> >>>>> that’s very interesting. Is your current work public or is the most
> >>> recent
> >>>>> public work in the SVN-repo I mentioned?
> >>>>>
> >>>>> Cheers,
> >>>>> - hugi
> >>>>>
> >>>>>
> >>>>>> On 9. feb. 2017, at 15:36, John Huss <[hidden email]> wrote:
> >>>>>>
> >>>>>> I'm developing and using cayenne-migrations. It works fine for me
> and
> >>>>> has a
> >>>>>> very similar approach to ERXMigrations.  I don't think others are
> using
> >>>>> it
> >>>>>> though.  It has the advantage of being able to auto-generate the
> >>>>> migration
> >>>>>> code from your cayenne model (DataMap), where I think the others
> >>> require
> >>>>>> hand coding.  On the other hand, sometimes having all pure SQL
> >>> statements
> >>>>>> instead of mostly java code is useful.  Good luck!
> >>>>>>
> >>>>>> John
> >>>>>>
> >>>>>> On Thu, Feb 9, 2017 at 9:15 AM Michael Gentry <
> [hidden email]>
> >>>>> wrote:
> >>>>>>
> >>>>>>> Hi Hugi,
> >>>>>>>
> >>>>>>> We manage schema changes outside of Cayenne using Flyway (could
> also
> >>> use
> >>>>>>> Liquibase).  Any schema changes we make are updated by hand in
> Cayenne
> >>>>>>> Modeler.  This works fairly well for us and fits in with our
> automated
> >>>>>>> builds/etc.  Perhaps not the answer you were looking for, though!
> >>>>>>>
> >>>>>>> mrg
> >>>>>>>
> >>>>>>>
> >>>>>>> On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]>
> >>>>> wrote:
> >>>>>>>
> >>>>>>>> Hi all.
> >>>>>>>> In EOF/WOnder we have the most swesome ERXMigrations to manage
> >>> changes
> >>>>> in
> >>>>>>>> the data model between versions, i.e. upgrades of the schema (and
> >>>>>>>> downgrades, if applicable).
> >>>>>>>>
> >>>>>>>> I see that some years ago there was discussion of an API to handle
> >>> this
> >>>>>>> in
> >>>>>>>> Cayenne (
> http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
> >>>>>>>> migrations/ ). but how’s the situation today? Is there something
> >>> in/for
> >>>>>>>> Cayenne to do this, and if not, what tools are people using to
> manage
> >>>>>>>> versioning of their DB schemas?
> >>>>>>>>
> >>>>>>>> Cheers,
> >>>>>>>> - hugi
> >>>>>>>
> >>>>>
> >>>>>
> >>>
> >>>
> >
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

Aristedes Maniatis-2
In reply to this post by Andrus Adamchik
We use liquibase to execute SQL combined with special liquibase functions to run groovy/Java classes which are needed to do more complex migrations in code (within the Cayenne model). Those complex migrations might include adding new data to the DB or modifying existing records in ways that SQL is too cumbersome for.

Where we come unstuck is that if a bug was introduced 20 versions ago, it can be hard to recover. Phabricator (an open source project management, code review solution written by Facebook) has a neat feature: it verifies your model each time you run the upgrade. So if an index is missing, then the code will just add it. If a collation is wrong, then it is fixed.

I'd be pleased to have something similar as a library for Cayenne. Obviously we'd need to add meta-data to the model like indices, but having a 'please make the DB look like my model' feature would be great. Obviously it isn't going to be able to fix everything, but for non-destructive changes like indices it would be really useful.

Ari


On 10/2/17 9:41am, Andrus Adamchik wrote:

> FWIW, the workflow I am using is "DB-first", and Cayenne 4.0 is providing the tools to make it practical and mostly automated:
>
> 1. Use a migration tool like Flyway or Liquibase to code your migrations in SQL (even more so when wrapped in bootique-liquibase / bootique-flyway).
> 2. Run cdbimport from Maven/Ant/Gradle to sync Cayenne model from DB.
> 3. In the modeler fix any object naming discrepancies, map custom value objects, map inheritance.
> 4. Run cgen to sync Java classes from Cayenne model
> 5. Rinse and repeat.
>
> Can someone please explain the workflow with ERX|cayenne migrations? What are the advantages over the approach above? Does it handle data migrations or only the schema?
>
> Andrus
>
>
>> On Feb 10, 2017, at 7:06 AM, John Huss <[hidden email]> wrote:
>>
>> I pushed the changes I had pending - there was more than I thought.
>>
>> I'm fine with it going in, but I'm not sure that the community agrees.
>> Since this can live as an independent project / jar there isn't really a
>> need to merge it into the main cayenne repo.  But if we are going to keep
>> it separate we should move it to github or something where participation is
>> easier.
>>
>> Another issue - I'm pretty sure this won't build or run against cayenne's
>> master anymore due the to refactoring of DbMerger stuff.  But I haven't
>> tried.
>>
>> On Thu, Feb 9, 2017 at 1:14 PM Musall, Maik <[hidden email]> wrote:
>>
>>> Hi John,
>>>
>>> how do you (and everyone else) feel about including this in the main repo
>>> after polishing?
>>>
>>> I'm working with Hugi here on a project and would like to continue using
>>> this style of
>>> migrations because our entire environment is geared towards it. I'd love
>>> for this to be in
>>> the main cayenne repo so we can submit our improvements.
>>>
>>> Maik
>>>
>>>> Am 09.02.2017 um 15:59 schrieb John Huss <[hidden email]>:
>>>>
>>>> It's current except for a single small change.  I seem to have lost the
>>>> push url, so I need to get it working again to update it.  But it would
>>> be
>>>> fine for playing with as is.
>>>>
>>>> On Thu, Feb 9, 2017 at 9:45 AM Hugi Thordarson <[hidden email]> wrote:
>>>>
>>>>> Hi John,
>>>>> that’s very interesting. Is your current work public or is the most
>>> recent
>>>>> public work in the SVN-repo I mentioned?
>>>>>
>>>>> Cheers,
>>>>> - hugi
>>>>>
>>>>>
>>>>>> On 9. feb. 2017, at 15:36, John Huss <[hidden email]> wrote:
>>>>>>
>>>>>> I'm developing and using cayenne-migrations. It works fine for me and
>>>>> has a
>>>>>> very similar approach to ERXMigrations.  I don't think others are using
>>>>> it
>>>>>> though.  It has the advantage of being able to auto-generate the
>>>>> migration
>>>>>> code from your cayenne model (DataMap), where I think the others
>>> require
>>>>>> hand coding.  On the other hand, sometimes having all pure SQL
>>> statements
>>>>>> instead of mostly java code is useful.  Good luck!
>>>>>>
>>>>>> John
>>>>>>
>>>>>> On Thu, Feb 9, 2017 at 9:15 AM Michael Gentry <[hidden email]>
>>>>> wrote:
>>>>>>
>>>>>>> Hi Hugi,
>>>>>>>
>>>>>>> We manage schema changes outside of Cayenne using Flyway (could also
>>> use
>>>>>>> Liquibase).  Any schema changes we make are updated by hand in Cayenne
>>>>>>> Modeler.  This works fairly well for us and fits in with our automated
>>>>>>> builds/etc.  Perhaps not the answer you were looking for, though!
>>>>>>>
>>>>>>> mrg
>>>>>>>
>>>>>>>
>>>>>>> On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]>
>>>>> wrote:
>>>>>>>
>>>>>>>> Hi all.
>>>>>>>> In EOF/WOnder we have the most swesome ERXMigrations to manage
>>> changes
>>>>> in
>>>>>>>> the data model between versions, i.e. upgrades of the schema (and
>>>>>>>> downgrades, if applicable).
>>>>>>>>
>>>>>>>> I see that some years ago there was discussion of an API to handle
>>> this
>>>>>>> in
>>>>>>>> Cayenne ( http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
>>>>>>>> migrations/ ). but how’s the situation today? Is there something
>>> in/for
>>>>>>>> Cayenne to do this, and if not, what tools are people using to manage
>>>>>>>> versioning of their DB schemas?
>>>>>>>>
>>>>>>>> Cheers,
>>>>>>>> - hugi
>>>>>>>
>>>>>
>>>>>
>>>
>>>
>

--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A
Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

Musall, Maik
In reply to this post by John Huss
Hi,

I'm coming from Wonder migrations, as I'm currently migrating a large project from EOF to Cayenne (with Hugi). ERXMigration keeps a table indicating the current schema version, and then the application itself will upgrade it to the current point upon startup, every new version being defined by one class with a name containing the corresponding version number (like MYMODEL139.java).

Here's an example of the main method of one migration. It will rename one column and change it's length, add an index, and do a small data migration in SQL as well. Note that this enables me to reference classes and enums in the project ("PaymentMethod" in this case) to eliminate spelling errors.

        @Override
        public void upgrade( EOEditingContext editingContext, ERXMigrationDatabase database ) throws Throwable {
                ERXMigrationTable paymentTransactionTable = database.existingTableNamed( PDCExternalPaymentTransaction.ENTITY_NAME );

                ERXMigrationColumn paymentIdCol = paymentTransactionTable.existingColumnNamed( "providerResponsePaymentId" );
                paymentIdCol.renameTo( PDCExternalPaymentTransaction.PROVIDER_PAYMENT_ID.key() );
                paymentIdCol.setWidthType( Types.VARCHAR, 80, null );
                paymentTransactionTable.addIndex( "providerPaymentId_idx", paymentIdCol );

                String ptName = PaymentProvider.PayTool.name();
                String paypalMethodName = PaymentMethod.PayPal.name();
                execSql( database, new String[]{
                                "UPDATE PDCExternalPaymentTransaction SET providerName = '" + ptName + "' WHERE providerName = 'paytool'",
                                "UPDATE PDCExternalPaymentTransaction SET paymentMethod = '" + paypalMethodName + "' WHERE paymentMethod = 'PAYPAL'"
                } );
        }

We operate various testing/staging/development instances of this application. If we need current data in testing to test a new feature against recent production data, I can just restore a db dump in testing, not caring about the schema version of that, and deploy the new code, and the application will take care of upgrading the schema to current version. Generally I can just push my changes, jenkins will deploy, and the only thing I have to take care of is to include my migration code in the same commit as the model change. No 3rd party tool, no manual steps at all.

A small drawback of this way is when merging branches that both contain a schema update, you have to first rename your own migration to a higher number to keep them in sequence. This is easy to do, but I could imagine a more elegant way to manage the order of migrations to execute.

As I understand, John's implementation is very much like this, right?

Maik



> Am 09.02.2017 um 23:17 schrieb John Huss <[hidden email]>:
>
>> Can someone please explain the workflow with ERX|cayenne migrations? What
> are the advantages over the approach above? Does it handle data migrations
> or only the schema?
>
> Mainly schema migrations.
>
> I think the advantages are:
> 1) Cross-database support from a single representation. So you can run the
> same migrations on different databases without having to code specifically
> to each DB. This seems to fit well within an ORM.
> 2) Auto-generation of java migration code from DataMap. This is currently
> only supported for the initial migration (whole database), but you can
> still use that to copy/paste code for new tables.  In the future it could
> connect to your DB to generate a delta migration automatically, but I'm not
> sure it would be worth doing.
> 3) You retain the ability to write raw SQL as part of the migration, and
> this can be targeted at a specific database vendor if needed.  This is how
> data migrations would be handled.
> 4) In the future the library could optimize DB changes, like combining
> multiple add column statements into a single statement, which could provide
> better performance.
>
> On Thu, Feb 9, 2017 at 4:45 PM Andrus Adamchik <[hidden email]>
> wrote:
>
>>> 1. Use a migration tool like Flyway or Liquibase to code your migrations
>> in SQL (even more so when wrapped in bootique-liquibase / bootique-flyway).
>>
>> "even more so" -> "especially easy to run"
>>
>>
>>> On Feb 10, 2017, at 9:41 AM, Andrus Adamchik <[hidden email]>
>> wrote:
>>>
>>> FWIW, the workflow I am using is "DB-first", and Cayenne 4.0 is
>> providing the tools to make it practical and mostly automated:
>>>
>>> 1. Use a migration tool like Flyway or Liquibase to code your migrations
>> in SQL (even more so when wrapped in bootique-liquibase / bootique-flyway).
>>> 2. Run cdbimport from Maven/Ant/Gradle to sync Cayenne model from DB.
>>> 3. In the modeler fix any object naming discrepancies, map custom value
>> objects, map inheritance.
>>> 4. Run cgen to sync Java classes from Cayenne model
>>> 5. Rinse and repeat.
>>>
>>> Can someone please explain the workflow with ERX|cayenne migrations?
>> What are the advantages over the approach above? Does it handle data
>> migrations or only the schema?
>>>
>>> Andrus
>>>
>>>
>>>> On Feb 10, 2017, at 7:06 AM, John Huss <[hidden email]> wrote:
>>>>
>>>> I pushed the changes I had pending - there was more than I thought.
>>>>
>>>> I'm fine with it going in, but I'm not sure that the community agrees.
>>>> Since this can live as an independent project / jar there isn't really a
>>>> need to merge it into the main cayenne repo.  But if we are going to
>> keep
>>>> it separate we should move it to github or something where
>> participation is
>>>> easier.
>>>>
>>>> Another issue - I'm pretty sure this won't build or run against
>> cayenne's
>>>> master anymore due the to refactoring of DbMerger stuff.  But I haven't
>>>> tried.
>>>>
>>>> On Thu, Feb 9, 2017 at 1:14 PM Musall, Maik <[hidden email]>
>> wrote:
>>>>
>>>>> Hi John,
>>>>>
>>>>> how do you (and everyone else) feel about including this in the main
>> repo
>>>>> after polishing?
>>>>>
>>>>> I'm working with Hugi here on a project and would like to continue
>> using
>>>>> this style of
>>>>> migrations because our entire environment is geared towards it. I'd
>> love
>>>>> for this to be in
>>>>> the main cayenne repo so we can submit our improvements.
>>>>>
>>>>> Maik
>>>>>
>>>>>> Am 09.02.2017 um 15:59 schrieb John Huss <[hidden email]>:
>>>>>>
>>>>>> It's current except for a single small change.  I seem to have lost
>> the
>>>>>> push url, so I need to get it working again to update it.  But it
>> would
>>>>> be
>>>>>> fine for playing with as is.
>>>>>>
>>>>>> On Thu, Feb 9, 2017 at 9:45 AM Hugi Thordarson <[hidden email]>
>> wrote:
>>>>>>
>>>>>>> Hi John,
>>>>>>> that’s very interesting. Is your current work public or is the most
>>>>> recent
>>>>>>> public work in the SVN-repo I mentioned?
>>>>>>>
>>>>>>> Cheers,
>>>>>>> - hugi
>>>>>>>
>>>>>>>
>>>>>>>> On 9. feb. 2017, at 15:36, John Huss <[hidden email]> wrote:
>>>>>>>>
>>>>>>>> I'm developing and using cayenne-migrations. It works fine for me
>> and
>>>>>>> has a
>>>>>>>> very similar approach to ERXMigrations.  I don't think others are
>> using
>>>>>>> it
>>>>>>>> though.  It has the advantage of being able to auto-generate the
>>>>>>> migration
>>>>>>>> code from your cayenne model (DataMap), where I think the others
>>>>> require
>>>>>>>> hand coding.  On the other hand, sometimes having all pure SQL
>>>>> statements
>>>>>>>> instead of mostly java code is useful.  Good luck!
>>>>>>>>
>>>>>>>> John
>>>>>>>>
>>>>>>>> On Thu, Feb 9, 2017 at 9:15 AM Michael Gentry <
>> [hidden email]>
>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> Hi Hugi,
>>>>>>>>>
>>>>>>>>> We manage schema changes outside of Cayenne using Flyway (could
>> also
>>>>> use
>>>>>>>>> Liquibase).  Any schema changes we make are updated by hand in
>> Cayenne
>>>>>>>>> Modeler.  This works fairly well for us and fits in with our
>> automated
>>>>>>>>> builds/etc.  Perhaps not the answer you were looking for, though!
>>>>>>>>>
>>>>>>>>> mrg
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <[hidden email]>
>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> Hi all.
>>>>>>>>>> In EOF/WOnder we have the most swesome ERXMigrations to manage
>>>>> changes
>>>>>>> in
>>>>>>>>>> the data model between versions, i.e. upgrades of the schema (and
>>>>>>>>>> downgrades, if applicable).
>>>>>>>>>>
>>>>>>>>>> I see that some years ago there was discussion of an API to handle
>>>>> this
>>>>>>>>> in
>>>>>>>>>> Cayenne (
>> http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
>>>>>>>>>> migrations/ ). but how’s the situation today? Is there something
>>>>> in/for
>>>>>>>>>> Cayenne to do this, and if not, what tools are people using to
>> manage
>>>>>>>>>> versioning of their DB schemas?
>>>>>>>>>>
>>>>>>>>>> Cheers,
>>>>>>>>>> - hugi
>>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>
>>
>>

Reply | Threaded
Open this post in threaded view
|

Re: Cayenne and migrations

John Huss
Yes, that's right.  Basically all database migration libraries work this
way.  The difference is in the language/API used to specify the changes to
make.

On Fri, Feb 10, 2017 at 3:23 AM Musall, Maik <[hidden email]> wrote:

> Hi,
>
> I'm coming from Wonder migrations, as I'm currently migrating a large
> project from EOF to Cayenne (with Hugi). ERXMigration keeps a table
> indicating the current schema version, and then the application itself will
> upgrade it to the current point upon startup, every new version being
> defined by one class with a name containing the corresponding version
> number (like MYMODEL139.java).
>
> Here's an example of the main method of one migration. It will rename one
> column and change it's length, add an index, and do a small data migration
> in SQL as well. Note that this enables me to reference classes and enums in
> the project ("PaymentMethod" in this case) to eliminate spelling errors.
>
>         @Override
>         public void upgrade( EOEditingContext editingContext,
> ERXMigrationDatabase database ) throws Throwable {
>                 ERXMigrationTable paymentTransactionTable =
> database.existingTableNamed( PDCExternalPaymentTransaction.ENTITY_NAME );
>
>                 ERXMigrationColumn paymentIdCol =
> paymentTransactionTable.existingColumnNamed( "providerResponsePaymentId" );
>                 paymentIdCol.renameTo(
> PDCExternalPaymentTransaction.PROVIDER_PAYMENT_ID.key() );
>                 paymentIdCol.setWidthType( Types.VARCHAR, 80, null );
>                 paymentTransactionTable.addIndex( "providerPaymentId_idx",
> paymentIdCol );
>
>                 String ptName = PaymentProvider.PayTool.name();
>                 String paypalMethodName = PaymentMethod.PayPal.name();
>                 execSql( database, new String[]{
>                                 "UPDATE PDCExternalPaymentTransaction SET
> providerName = '" + ptName + "' WHERE providerName = 'paytool'",
>                                 "UPDATE PDCExternalPaymentTransaction SET
> paymentMethod = '" + paypalMethodName + "' WHERE paymentMethod = 'PAYPAL'"
>                 } );
>         }
>
> We operate various testing/staging/development instances of this
> application. If we need current data in testing to test a new feature
> against recent production data, I can just restore a db dump in testing,
> not caring about the schema version of that, and deploy the new code, and
> the application will take care of upgrading the schema to current version.
> Generally I can just push my changes, jenkins will deploy, and the only
> thing I have to take care of is to include my migration code in the same
> commit as the model change. No 3rd party tool, no manual steps at all.
>
> A small drawback of this way is when merging branches that both contain a
> schema update, you have to first rename your own migration to a higher
> number to keep them in sequence. This is easy to do, but I could imagine a
> more elegant way to manage the order of migrations to execute.
>
> As I understand, John's implementation is very much like this, right?
>
> Maik
>
>
>
> > Am 09.02.2017 um 23:17 schrieb John Huss <[hidden email]>:
> >
> >> Can someone please explain the workflow with ERX|cayenne migrations?
> What
> > are the advantages over the approach above? Does it handle data
> migrations
> > or only the schema?
> >
> > Mainly schema migrations.
> >
> > I think the advantages are:
> > 1) Cross-database support from a single representation. So you can run
> the
> > same migrations on different databases without having to code
> specifically
> > to each DB. This seems to fit well within an ORM.
> > 2) Auto-generation of java migration code from DataMap. This is currently
> > only supported for the initial migration (whole database), but you can
> > still use that to copy/paste code for new tables.  In the future it could
> > connect to your DB to generate a delta migration automatically, but I'm
> not
> > sure it would be worth doing.
> > 3) You retain the ability to write raw SQL as part of the migration, and
> > this can be targeted at a specific database vendor if needed.  This is
> how
> > data migrations would be handled.
> > 4) In the future the library could optimize DB changes, like combining
> > multiple add column statements into a single statement, which could
> provide
> > better performance.
> >
> > On Thu, Feb 9, 2017 at 4:45 PM Andrus Adamchik <[hidden email]>
> > wrote:
> >
> >>> 1. Use a migration tool like Flyway or Liquibase to code your
> migrations
> >> in SQL (even more so when wrapped in bootique-liquibase /
> bootique-flyway).
> >>
> >> "even more so" -> "especially easy to run"
> >>
> >>
> >>> On Feb 10, 2017, at 9:41 AM, Andrus Adamchik <[hidden email]>
> >> wrote:
> >>>
> >>> FWIW, the workflow I am using is "DB-first", and Cayenne 4.0 is
> >> providing the tools to make it practical and mostly automated:
> >>>
> >>> 1. Use a migration tool like Flyway or Liquibase to code your
> migrations
> >> in SQL (even more so when wrapped in bootique-liquibase /
> bootique-flyway).
> >>> 2. Run cdbimport from Maven/Ant/Gradle to sync Cayenne model from DB.
> >>> 3. In the modeler fix any object naming discrepancies, map custom value
> >> objects, map inheritance.
> >>> 4. Run cgen to sync Java classes from Cayenne model
> >>> 5. Rinse and repeat.
> >>>
> >>> Can someone please explain the workflow with ERX|cayenne migrations?
> >> What are the advantages over the approach above? Does it handle data
> >> migrations or only the schema?
> >>>
> >>> Andrus
> >>>
> >>>
> >>>> On Feb 10, 2017, at 7:06 AM, John Huss <[hidden email]> wrote:
> >>>>
> >>>> I pushed the changes I had pending - there was more than I thought.
> >>>>
> >>>> I'm fine with it going in, but I'm not sure that the community agrees.
> >>>> Since this can live as an independent project / jar there isn't
> really a
> >>>> need to merge it into the main cayenne repo.  But if we are going to
> >> keep
> >>>> it separate we should move it to github or something where
> >> participation is
> >>>> easier.
> >>>>
> >>>> Another issue - I'm pretty sure this won't build or run against
> >> cayenne's
> >>>> master anymore due the to refactoring of DbMerger stuff.  But I
> haven't
> >>>> tried.
> >>>>
> >>>> On Thu, Feb 9, 2017 at 1:14 PM Musall, Maik <[hidden email]>
> >> wrote:
> >>>>
> >>>>> Hi John,
> >>>>>
> >>>>> how do you (and everyone else) feel about including this in the main
> >> repo
> >>>>> after polishing?
> >>>>>
> >>>>> I'm working with Hugi here on a project and would like to continue
> >> using
> >>>>> this style of
> >>>>> migrations because our entire environment is geared towards it. I'd
> >> love
> >>>>> for this to be in
> >>>>> the main cayenne repo so we can submit our improvements.
> >>>>>
> >>>>> Maik
> >>>>>
> >>>>>> Am 09.02.2017 um 15:59 schrieb John Huss <[hidden email]>:
> >>>>>>
> >>>>>> It's current except for a single small change.  I seem to have lost
> >> the
> >>>>>> push url, so I need to get it working again to update it.  But it
> >> would
> >>>>> be
> >>>>>> fine for playing with as is.
> >>>>>>
> >>>>>> On Thu, Feb 9, 2017 at 9:45 AM Hugi Thordarson <[hidden email]>
> >> wrote:
> >>>>>>
> >>>>>>> Hi John,
> >>>>>>> that’s very interesting. Is your current work public or is the most
> >>>>> recent
> >>>>>>> public work in the SVN-repo I mentioned?
> >>>>>>>
> >>>>>>> Cheers,
> >>>>>>> - hugi
> >>>>>>>
> >>>>>>>
> >>>>>>>> On 9. feb. 2017, at 15:36, John Huss <[hidden email]> wrote:
> >>>>>>>>
> >>>>>>>> I'm developing and using cayenne-migrations. It works fine for me
> >> and
> >>>>>>> has a
> >>>>>>>> very similar approach to ERXMigrations.  I don't think others are
> >> using
> >>>>>>> it
> >>>>>>>> though.  It has the advantage of being able to auto-generate the
> >>>>>>> migration
> >>>>>>>> code from your cayenne model (DataMap), where I think the others
> >>>>> require
> >>>>>>>> hand coding.  On the other hand, sometimes having all pure SQL
> >>>>> statements
> >>>>>>>> instead of mostly java code is useful.  Good luck!
> >>>>>>>>
> >>>>>>>> John
> >>>>>>>>
> >>>>>>>> On Thu, Feb 9, 2017 at 9:15 AM Michael Gentry <
> >> [hidden email]>
> >>>>>>> wrote:
> >>>>>>>>
> >>>>>>>>> Hi Hugi,
> >>>>>>>>>
> >>>>>>>>> We manage schema changes outside of Cayenne using Flyway (could
> >> also
> >>>>> use
> >>>>>>>>> Liquibase).  Any schema changes we make are updated by hand in
> >> Cayenne
> >>>>>>>>> Modeler.  This works fairly well for us and fits in with our
> >> automated
> >>>>>>>>> builds/etc.  Perhaps not the answer you were looking for, though!
> >>>>>>>>>
> >>>>>>>>> mrg
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>> On Thu, Feb 9, 2017 at 9:21 AM, Hugi Thordarson <
> [hidden email]>
> >>>>>>> wrote:
> >>>>>>>>>
> >>>>>>>>>> Hi all.
> >>>>>>>>>> In EOF/WOnder we have the most swesome ERXMigrations to manage
> >>>>> changes
> >>>>>>> in
> >>>>>>>>>> the data model between versions, i.e. upgrades of the schema
> (and
> >>>>>>>>>> downgrades, if applicable).
> >>>>>>>>>>
> >>>>>>>>>> I see that some years ago there was discussion of an API to
> handle
> >>>>> this
> >>>>>>>>> in
> >>>>>>>>>> Cayenne (
> >> http://svn.apache.org/repos/asf/cayenne/sandbox/cayenne-
> >>>>>>>>>> migrations/ ). but how’s the situation today? Is there something
> >>>>> in/for
> >>>>>>>>>> Cayenne to do this, and if not, what tools are people using to
> >> manage
> >>>>>>>>>> versioning of their DB schemas?
> >>>>>>>>>>
> >>>>>>>>>> Cheers,
> >>>>>>>>>> - hugi
> >>>>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>
> >>>>>
> >>>
> >>
> >>
>
>