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 <m...@selbstdenker.ag> 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 <johnth...@gmail.com>:
> >
> >> 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 <and...@objectstyle.org>
> > 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 <and...@objectstyle.org>
> >> 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 <johnth...@gmail.com> 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 <m...@selbstdenker.ag>
> >> 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 <johnth...@gmail.com>:
> >>>>>>
> >>>>>> 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 <h...@karlmenn.is>
> >> 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 <johnth...@gmail.com> 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 <
> >> mgen...@masslight.net>
> >>>>>>> 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 <
> h...@karlmenn.is>
> >>>>>>> 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
> >>>>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>
> >>>>>
> >>>
> >>
> >>
>
>
