FAQ
*Hopefully the list supports HTML email; if not view it
here<https://docs.google.com/document/d/1-ZGTCZdw5sksamJCKMT4bdw0zhrftKQptIgozk3ojbc/edit>
.*
*
*
*I’m interested in having more flexible support for database migrations in
Cayenne, so I wrote a new migrations API on top of the existing DbMerger
constructs. The API is intended to be a fairly direct mapping of SQL DDL
statements into Java. This approach was inspired by the Migrations API in
Project Wonder that is used with WebObjects (where it is the only option,
and is widely used). It uses database versioning.

Creating a table with the API looks like this:

MigrationTableNew artist = db.createTable("Artist");
artist.addIntegerColumn("id", true, null); // “true” for isMandatory, null
for defaultValue
artist.addVarcharColumn("name", 100, true, null);
cardProcessor.addPrimaryKey("id");

Altering a table looks like this:

MigrationTableExisting artist = db.alterTable("Artist");
artist.addDateColumn("dateOfBirth");
artist.alterColumn("name").setDataType(Types.VARCHAR, 200);

This code is not written, but rather is generated directly from the DataMap.

Question: Why would you want this rather than a plain raw SQL script?
Answer: To maintain database independence (because syntax can differ
between databases).

Question: Why not use the existing migration features, such as DbGenerator
or DbMerger?
Answer: DbGenerator can’t modify existing schema, it just creates new
databases. DbMerger is a bit too “magic” because you don’t know what
operations it will actually perform since it’s operation varies based on
the database’s current state. But there is more; below is a comparison of
all the available options

1) org.apache.cayenne.access.DbGenerator

Automatically creates database from DataMap

2) org.apache.cayenne.merge.DbMerger

Automatically creates or modifies database to match DataMap

3) org.apache.cayenne.migration.Migrator (proposed)

API for creating or modifying a database with explicit code


FeatureDbGeneratorDbMergerMigrator (new)Amount of Code requiredNoneSingle
short method (see example below)Explicit code needed for each table,
column, etc. for each version. But this code is generated, so it doesn’t
need to be written, just generated and kept around.Can modify existing
schema?NoYesYesPredictable?Yes, (mostly). It only does a complete creation
of the DB.No, operations depend on DB state.Yes, operations are explicitly
defined and do not vary with state.Complete, i.e. can perform any type of
DDL operation.NoNoYes, arbitrary SQL statements can easily be inserted and
run at any point during the migration.Database independent (portable)YesYes
Yes, uses DbTokens for common operations and defines mechanism for handling
DB-specific SQL for custom operations.CustomizableNoYes, somewhat. You can
examine the MergerTokens and decide to skip them or reorder them, etc.Yes,
explicit step by step execution allows customization at any point.Guarantees
consist column order between databases created at different timesNoNoYes


Example code using the new Migration package (this would be generated):

public class Artist0 extends Migration { // version zero is the initial
database creation
public Artist0(DataNode node) {
super(node);
}
public void upgrade(MigrationDatabase db) {
MigrationTableNew artist = db.createTable("Artist");
artist.addIntegerColumn("id", true, null); // “true” for isMandatory, null
for defaultValue
artist.addVarcharColumn("name", 100, true, null);
cardProcessor.addPrimaryKey("id");

MigrationTableNew painting = db.createTable("Painting");
painting.addIntegerColumn("id", true, null);
painting.addIntegerColumn("artist_id", true, null);
painting.addVarcharColumn("name", 100, true, null);
painting.addPrimaryKey("id");

painting.addForeignKey("artist_id", "Artist", "id");
}
}

public class Artist1 extends Migration { // version 1 is a later revision
of the schema
public Artist1(DataNode node) {
super(node);
}
public void upgrade(MigrationDatabase db) {
MigrationTableExisting artist = db.alterTable("Artist");
artist.addDateColumn("dateOfBirth");
artist.alterColumn("name").setDataType(Types.VARCHAR, 200);
}
}

So ideally modeler would have a button to generate a migration, which would
spit out a Migration subclass to create you database from scratch, OR to
generate a Migration subclass with just the differences between your
DataMap and the database when you are working with an existing DB. Right
now I already have a command-line class that can generate the initial
migration.*
*
*
*Is there any interest in making this part of Cayenne? What questions do
you have?*
*
*
*Thanks,*
*John Huss*

Search Discussions

  • Aristedes Maniatis at Dec 21, 2011 at 2:48 am

    On 21/12/11 7:01 AM, John Huss wrote:
    *I’m interested in having more flexible support for database migrations in
    Cayenne, so I wrote a new migrations API on top of the existing DbMerger
    constructs.
    My question: why not use a third party tool like liquibase?

    Or even write tighter connections between liquibase and Cayenne by allowing Cayenne to output the liquibase migration script. Liquibase solves lots of problems, like knowing which migrations have run, conditional migrations, logging, locking, etc.


    Ari



    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Andrus Adamchik at Dec 21, 2011 at 1:06 pm
    This was the point that I was making in my comment in https://issues.apache.org/jira/browse/CAY-1633 . A DB migrations mechanism is very important to Cayenne users (as of course their apps are using some DB). But IMO it is a concern that can't be adequately addressed by Cayenne abstractions, and hence IMO should be kept separate.

    John, you are comparing your API with DbGenerator and DbMerger, and there's no question that it is more advanced than those two. I'd be comparing it with the existing migrations tools, such as http://code.google.com/p/c5-db-migration/ . This may sound odd coming from a Cayenne guy, but I like c5 approach (and similar home-grown approach that I've been using on other projects) precisely because migrations are written in SQL.

    However regardless of the use case for migrations (controlled DB vs unknown DB; migration scripts embedded in the app and run on startup vs. independently from the app during deployment), you'd have to write advanced SQL that can't be mapped to Cayenne model. E.g. you move the data to a new column, transforming the data in the process, generate a table aggregate from subsets of other tables, etc. etc. Wrapping such SQL in Java doesn't give you much, just obfuscates things.

    Andrus

    On Dec 20, 2011, at 9:47 PM, Aristedes Maniatis wrote:
    On 21/12/11 7:01 AM, John Huss wrote:
    *I’m interested in having more flexible support for database migrations in
    Cayenne, so I wrote a new migrations API on top of the existing DbMerger
    constructs.
    My question: why not use a third party tool like liquibase?

    Or even write tighter connections between liquibase and Cayenne by allowing Cayenne to output the liquibase migration script. Liquibase solves lots of problems, like knowing which migrations have run, conditional migrations, logging, locking, etc.


    Ari
  • Michael Gentry at Dec 21, 2011 at 2:30 pm

    On Wed, Dec 21, 2011 at 8:06 AM, Andrus Adamchik wrote:
    John, you are comparing your API with DbGenerator and DbMerger, and there's no question that it is more advanced than those two. I'd be comparing it with the existing migrations tools, such as http://code.google.com/p/c5-db-migration/ . This may sound odd coming from a Cayenne guy, but I like c5 approach (and similar home-grown approach that I've been using on other projects) precisely because migrations are written in SQL.
    Seems similar to Flyway:

    https://code.google.com/p/flyway/


    mrg
  • John Huss at Dec 21, 2011 at 7:17 pm

    On Wed, Dec 21, 2011 at 7:06 AM, Andrus Adamchik wrote:

    This was the point that I was making in my comment in
    https://issues.apache.org/jira/browse/CAY-1633 . A DB migrations
    mechanism is very important to Cayenne users (as of course their apps are
    using some DB). But IMO it is a concern that can't be adequately addressed
    by Cayenne abstractions, and hence IMO should be kept separate.
    The thing is, Cayenne already comes very close to doing what I need. It
    can already generate all of the most common DDL statements for all
    supported databases. It can already generate a list of differences between
    the DataMap and the DB. All I want is more explicit control over this
    existing functionality. So what I wrote is really just a small wrapper on
    top of the existing Cayenne code.

    One advantage to doing it this way versus using a third-party tool is that
    the SQL eventually outputted can match exactly with the SQL that is
    generated using the "Generate Database Schema" button in CayenneModeler
    (since it uses the same code under the hood).

    John, you are comparing your API with DbGenerator and DbMerger, and
    there's no question that it is more advanced than those two. I'd be
    comparing it with the existing migrations tools, such as
    http://code.google.com/p/c5-db-migration/ . This may sound odd coming
    from a Cayenne guy, but I like c5 approach (and similar home-grown approach
    that I've been using on other projects) precisely because migrations are
    written in SQL.
    For many things, multiple databases can be supported without needing
    separate migration scripts for each DB (every operation that has a
    MergerToken subclass). This is an advantage over the pure SQL approach.

    However regardless of the use case for migrations (controlled DB vs unknown
    DB; migration scripts embedded in the app and run on startup vs.
    independently from the app during deployment), you'd have to write advanced
    SQL that can't be mapped to Cayenne model. E.g. you move the data to a new
    column, transforming the data in the process, generate a table aggregate
    from subsets of other tables, etc. etc. Wrapping such SQL in Java doesn't
    give you much, just obfuscates things.

    For things that aren't supported (or for people that prefer raw SQL) this
    lets you use raw SQL easily so you can have a hybrid approach. There would
    be no attempt to have a Java API around this sort of thing. Although,
    something like SQLTemplate could be very helpful here in reducing
    duplication. The API would only support the operations available via the
    MergerTokens.
  • Andrus Adamchik at Dec 21, 2011 at 8:03 pm
    I am checking my migrations archive, and "Cayenne-friendly" schema alterations or simple INSERT/UPDATE/DELETE make up probably 50% of those. The rest are things like these:

    (1)

    LOAD DATA LOCAL INFILE './patches-5.0/some.csv' INTO TABLE some_table
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (C1,C2,C3);

    (2)

    SET unique_checks=0;

    ALTER TABLE t1 DROP INDEX keyword_index;
    ALTER TABLE t1 DROP INDEX headline_index;
    ALTER TABLE t1 ENGINE=InnoDB;

    SET unique_checks=1;

    (3)

    set @AAA = (select max(ID) from nhldb.xxx);
    insert into nhldb.xxx (ID, NAME, DESCRIPTION, URL)
    values((@AAA+1), 's1', 's2', 's3');

    So how would those be handled if we are to go this route?

    Andrus
  • John Huss at Dec 21, 2011 at 8:22 pm

    On Wed, Dec 21, 2011 at 2:02 PM, Andrus Adamchik wrote:

    I am checking my migrations archive, and "Cayenne-friendly" schema
    alterations or simple INSERT/UPDATE/DELETE make up probably 50% of those.
    The rest are things like these:

    (1)

    LOAD DATA LOCAL INFILE './patches-5.0/some.csv' INTO TABLE some_table
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY
    '\n' (C1,C2,C3);

    (2)

    SET unique_checks=0;

    ALTER TABLE t1 DROP INDEX keyword_index;
    ALTER TABLE t1 DROP INDEX headline_index;
    ALTER TABLE t1 ENGINE=InnoDB;

    SET unique_checks=1;

    (3)

    set @AAA = (select max(ID) from nhldb.xxx);
    insert into nhldb.xxx (ID, NAME, DESCRIPTION, URL)
    values((@AAA+1), 's1', 's2', 's3');

    So how would those be handled if we are to go this route?

    There is a built-in Migration subclass called SqlFileMigration that makes
    it easy to run a raw SQL script. It uses filename conventions to locate
    SQL scripts. For version zero of the DataMap named 'MyMap' using
    Postgresql it would look for a file named 'MyMap0-Postgres.sql' and use
    that, or if not found it would look for a generic one with the
    name 'MyMap0.sql'.

    If you need to run commands from more than one file or you need more
    complicated logic for choosing a script, you can just call
    executeSqlScript(filename). Or if you just need to run a single command
    you can call executeSqlStatement(sql).

    So you have the ability to mix the raw SQL script execution with the API,
    which I often do. I'll subclass SqlFileMigration and use the API to add
    new tables etc, and then put things like index creation in a corresponding
    SQL file that will be run after the API calls when it calls super.upgrade.
  • John Huss at Dec 21, 2011 at 7:05 pm

    On Tue, Dec 20, 2011 at 8:47 PM, Aristedes Maniatis wrote:
    On 21/12/11 7:01 AM, John Huss wrote:

    *I’m interested in having more flexible support for database migrations in

    Cayenne, so I wrote a new migrations API on top of the existing DbMerger
    constructs.
    My question: why not use a third party tool like liquibase?

    Or even write tighter connections between liquibase and Cayenne by
    allowing Cayenne to output the liquibase migration script.

    Yes, you would have to do this to make it worthwhile; you'd need to
    generate the liquibase script from the DataMap and/or database.

    Liquibase solves lots of problems, like knowing which migrations have run,
    conditional migrations, logging, locking, etc.
    Liquibase looks like it would do a decent job. I'm not sure I like the XML
    format though.
  • Aristedes Maniatis at Dec 21, 2011 at 11:26 pm

    On 22/12/11 6:04 AM, John Huss wrote:

    Liquibase looks like it would do a decent job. I'm not sure I like the XML
    format though.
    Yes, ever since Ant tried to write a scripting language in XML, this approach has been ruined for most people.

    However in this case (using it for several months now), liquibase is really nice. We support three different database engines and used to have three sets of files for the schema updates. Now we have one and it is much easier to look after.

    We also have a unit test which runs in Jenkins nightly to apply all the database schema migrations and then verify that the result matches the current Cayenne schema. That catches most problems (except of course anything with constraints or indexes which Cayenne doesn't know about).

    If you implement this yourself or through Cayenne, don't forget to create some sort of database lock mechanism so that two instances of the application (eg. two war files) deployed simultaneously in a cluster don't both try to update the database at the same time.


    Regards

    Ari



    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Mike Kienenberger at Dec 21, 2011 at 5:55 pm
    If you still want to maintain this as related to Cayenne yet not part
    of Cayenne, there is a google-sponsored site which is set up for that.

    http://community.apache.org/apache-extras/faq.html

    On Tue, Dec 20, 2011 at 3:01 PM, John Huss wrote:
    *Hopefully the list supports HTML email; if not view it
    here<https://docs.google.com/document/d/1-ZGTCZdw5sksamJCKMT4bdw0zhrftKQptIgozk3ojbc/edit>
    .*
    *
    *
    *I’m interested in having more flexible support for database migrations in
    Cayenne, so I wrote a new migrations API on top of the existing DbMerger
    constructs.  The API is intended to be a fairly direct mapping of SQL DDL
    statements into Java.  This approach was inspired by the Migrations API in
    Project Wonder that is used with WebObjects (where it is the only option,
    and is widely used).  It uses database versioning.

    Creating a table with the API looks like this:

    MigrationTableNew artist = db.createTable("Artist");
    artist.addIntegerColumn("id", true, null); // “true” for isMandatory, null
    for defaultValue
    artist.addVarcharColumn("name", 100, true, null);
    cardProcessor.addPrimaryKey("id");

    Altering a table looks like this:

    MigrationTableExisting artist = db.alterTable("Artist");
    artist.addDateColumn("dateOfBirth");
    artist.alterColumn("name").setDataType(Types.VARCHAR, 200);

    This code is not written, but rather is generated directly from the DataMap.

    Question: Why would you want this rather than a plain raw SQL script?
    Answer: To maintain database independence (because syntax can differ
    between databases).

    Question: Why not use the existing migration features, such as DbGenerator
    or DbMerger?
    Answer: DbGenerator can’t modify existing schema, it just creates new
    databases.  DbMerger is a bit too “magic” because you don’t know what
    operations it will actually perform since it’s operation varies based on
    the database’s current state.  But there is more; below is a comparison of
    all the available options

    1) org.apache.cayenne.access.DbGenerator

    Automatically creates database from DataMap

    2) org.apache.cayenne.merge.DbMerger

    Automatically creates or modifies database to match DataMap

    3) org.apache.cayenne.migration.Migrator (proposed)

    API for creating or modifying a database with explicit code


    FeatureDbGeneratorDbMergerMigrator (new)Amount of Code requiredNoneSingle
    short method (see example below)Explicit code needed for each table,
    column, etc. for each version.  But this code is generated, so it doesn’t
    need to be written, just generated and kept around.Can modify existing
    schema?NoYesYesPredictable?Yes, (mostly). It only does a complete creation
    of the DB.No, operations depend on DB state.Yes, operations are explicitly
    defined and do not vary with state.Complete, i.e. can perform any type of
    DDL operation.NoNoYes, arbitrary SQL statements can easily be inserted and
    run at any point during the migration.Database independent (portable)YesYes
    Yes, uses DbTokens for common operations and defines mechanism for handling
    DB-specific SQL for custom operations.CustomizableNoYes, somewhat. You can
    examine the MergerTokens and decide to skip them or reorder them, etc.Yes,
    explicit step by step execution allows customization at any point.Guarantees
    consist column order between databases created at different timesNoNoYes


    Example code using the new Migration package (this would be generated):

    public class Artist0 extends Migration { // version zero is the initial
    database creation
    public Artist0(DataNode node) {
    super(node);
    }
    public void upgrade(MigrationDatabase db) {
    MigrationTableNew artist = db.createTable("Artist");
    artist.addIntegerColumn("id", true, null); // “true” for isMandatory, null
    for defaultValue
    artist.addVarcharColumn("name", 100, true, null);
    cardProcessor.addPrimaryKey("id");

    MigrationTableNew painting = db.createTable("Painting");
    painting.addIntegerColumn("id", true, null);
    painting.addIntegerColumn("artist_id", true, null);
    painting.addVarcharColumn("name", 100, true, null);
    painting.addPrimaryKey("id");

    painting.addForeignKey("artist_id", "Artist", "id");
    }
    }

    public class Artist1 extends Migration { // version 1 is a later revision
    of the schema
    public Artist1(DataNode node) {
    super(node);
    }
    public void upgrade(MigrationDatabase db) {
    MigrationTableExisting artist = db.alterTable("Artist");
    artist.addDateColumn("dateOfBirth");
    artist.alterColumn("name").setDataType(Types.VARCHAR, 200);
    }
    }

    So ideally modeler would have a button to generate a migration, which would
    spit out a Migration subclass to create you database from scratch, OR to
    generate a Migration subclass with just the differences between your
    DataMap and the database when you are working with an existing DB.  Right
    now I already have a command-line class that can generate the initial
    migration.*
    *
    *
    *Is there any interest in making this part of Cayenne? What questions do
    you have?*
    *
    *
    *Thanks,*
    *John Huss*
  • Andrus Adamchik at Dec 22, 2011 at 8:49 pm
    I spent some more time on this thinking were the new approach might fit (and also found a way to combine DbMerger with my SQL migrations tools - DbMerger would generate DDL SQL for me that I can later edit ... But unfortunately it suggests too many unneeded changes because of the legacy schema with its non-standard datatypes, a bunch of unmapped tables, etc. ... So still not usable for me).

    My thoughts are the following... While I have my own reservations that I mentioned elsewhere, I don't think they are irreconcilable with this design. E.g. some of those may be the result of the current featureset and as the toolset around it evolves, it will become more and more attractive. I would personally want tools for managing migration folders where I can drop SQL without thinking of the Java wrappers; also tools for packaging migrations in a runnable jar that can be deployed via Maven and used without source checkout. None of this is too far fetched.

    So I don't see why we shouldn't let the development happen inside Cayenne, but maybe initially it should be done in the sandbox at https://svn.apache.org/repos/asf/cayenne/sandbox/ . There we can create a separate Maven module and let it evolve. I am sure we'll find more proponents of Java-based migrations and decide to move it from sandbox to the core at some point.

    John, what do you think about this?

    If we are to go this way, maybe you can rework this patch to extract things not related to migrations and organize it in the separate module ("cayenne-migrations" or something)? By unrelated changes I mean DbAttribute.getDefaultValue(). This has to be discussed separately. Maybe there are more such changes that I overlooked.

    Cheers,
    Andrus

    On Dec 20, 2011, at 3:01 PM, John Huss wrote:
    *Hopefully the list supports HTML email; if not view it
    here<https://docs.google.com/document/d/1-ZGTCZdw5sksamJCKMT4bdw0zhrftKQptIgozk3ojbc/edit>
    .*
    *
    *
    *I’m interested in having more flexible support for database migrations in
    Cayenne, so I wrote a new migrations API on top of the existing DbMerger
    constructs. The API is intended to be a fairly direct mapping of SQL DDL
    statements into Java. This approach was inspired by the Migrations API in
    Project Wonder that is used with WebObjects (where it is the only option,
    and is widely used). It uses database versioning.

    Creating a table with the API looks like this:

    MigrationTableNew artist = db.createTable("Artist");
    artist.addIntegerColumn("id", true, null); // “true” for isMandatory, null
    for defaultValue
    artist.addVarcharColumn("name", 100, true, null);
    cardProcessor.addPrimaryKey("id");

    Altering a table looks like this:

    MigrationTableExisting artist = db.alterTable("Artist");
    artist.addDateColumn("dateOfBirth");
    artist.alterColumn("name").setDataType(Types.VARCHAR, 200);

    This code is not written, but rather is generated directly from the DataMap.

    Question: Why would you want this rather than a plain raw SQL script?
    Answer: To maintain database independence (because syntax can differ
    between databases).

    Question: Why not use the existing migration features, such as DbGenerator
    or DbMerger?
    Answer: DbGenerator can’t modify existing schema, it just creates new
    databases. DbMerger is a bit too “magic” because you don’t know what
    operations it will actually perform since it’s operation varies based on
    the database’s current state. But there is more; below is a comparison of
    all the available options

    1) org.apache.cayenne.access.DbGenerator

    Automatically creates database from DataMap

    2) org.apache.cayenne.merge.DbMerger

    Automatically creates or modifies database to match DataMap

    3) org.apache.cayenne.migration.Migrator (proposed)

    API for creating or modifying a database with explicit code


    FeatureDbGeneratorDbMergerMigrator (new)Amount of Code requiredNoneSingle
    short method (see example below)Explicit code needed for each table,
    column, etc. for each version. But this code is generated, so it doesn’t
    need to be written, just generated and kept around.Can modify existing
    schema?NoYesYesPredictable?Yes, (mostly). It only does a complete creation
    of the DB.No, operations depend on DB state.Yes, operations are explicitly
    defined and do not vary with state.Complete, i.e. can perform any type of
    DDL operation.NoNoYes, arbitrary SQL statements can easily be inserted and
    run at any point during the migration.Database independent (portable)YesYes
    Yes, uses DbTokens for common operations and defines mechanism for handling
    DB-specific SQL for custom operations.CustomizableNoYes, somewhat. You can
    examine the MergerTokens and decide to skip them or reorder them, etc.Yes,
    explicit step by step execution allows customization at any point.Guarantees
    consist column order between databases created at different timesNoNoYes


    Example code using the new Migration package (this would be generated):

    public class Artist0 extends Migration { // version zero is the initial
    database creation
    public Artist0(DataNode node) {
    super(node);
    }
    public void upgrade(MigrationDatabase db) {
    MigrationTableNew artist = db.createTable("Artist");
    artist.addIntegerColumn("id", true, null); // “true” for isMandatory, null
    for defaultValue
    artist.addVarcharColumn("name", 100, true, null);
    cardProcessor.addPrimaryKey("id");

    MigrationTableNew painting = db.createTable("Painting");
    painting.addIntegerColumn("id", true, null);
    painting.addIntegerColumn("artist_id", true, null);
    painting.addVarcharColumn("name", 100, true, null);
    painting.addPrimaryKey("id");

    painting.addForeignKey("artist_id", "Artist", "id");
    }
    }

    public class Artist1 extends Migration { // version 1 is a later revision
    of the schema
    public Artist1(DataNode node) {
    super(node);
    }
    public void upgrade(MigrationDatabase db) {
    MigrationTableExisting artist = db.alterTable("Artist");
    artist.addDateColumn("dateOfBirth");
    artist.alterColumn("name").setDataType(Types.VARCHAR, 200);
    }
    }

    So ideally modeler would have a button to generate a migration, which would
    spit out a Migration subclass to create you database from scratch, OR to
    generate a Migration subclass with just the differences between your
    DataMap and the database when you are working with an existing DB. Right
    now I already have a command-line class that can generate the initial
    migration.*
    *
    *
    *Is there any interest in making this part of Cayenne? What questions do
    you have?*
    *
    *
    *Thanks,*
    *John Huss*
  • John Huss at Dec 22, 2011 at 9:55 pm
    Sure, that sounds good to me. Thanks.
    On Thu, Dec 22, 2011 at 2:49 PM, Andrus Adamchik wrote:

    I spent some more time on this thinking were the new approach might fit
    (and also found a way to combine DbMerger with my SQL migrations tools -
    DbMerger would generate DDL SQL for me that I can later edit ... But
    unfortunately it suggests too many unneeded changes because of the legacy
    schema with its non-standard datatypes, a bunch of unmapped tables, etc.
    ... So still not usable for me).

    My thoughts are the following... While I have my own reservations that I
    mentioned elsewhere, I don't think they are irreconcilable with this
    design. E.g. some of those may be the result of the current featureset and
    as the toolset around it evolves, it will become more and more attractive.
    I would personally want tools for managing migration folders where I can
    drop SQL without thinking of the Java wrappers; also tools for packaging
    migrations in a runnable jar that can be deployed via Maven and used
    without source checkout. None of this is too far fetched.

    So I don't see why we shouldn't let the development happen inside Cayenne,
    but maybe initially it should be done in the sandbox at
    https://svn.apache.org/repos/asf/cayenne/sandbox/ . There we can create a
    separate Maven module and let it evolve. I am sure we'll find more
    proponents of Java-based migrations and decide to move it from sandbox to
    the core at some point.

    John, what do you think about this?

    If we are to go this way, maybe you can rework this patch to extract
    things not related to migrations and organize it in the separate module
    ("cayenne-migrations" or something)? By unrelated changes I mean
    DbAttribute.getDefaultValue(). This has to be discussed separately. Maybe
    there are more such changes that I overlooked.

    Cheers,
    Andrus

    On Dec 20, 2011, at 3:01 PM, John Huss wrote:
    *Hopefully the list supports HTML email; if not view it
    here<
    https://docs.google.com/document/d/1-ZGTCZdw5sksamJCKMT4bdw0zhrftKQptIgozk3ojbc/edit
    .*
    *
    *
    *I’m interested in having more flexible support for database migrations in
    Cayenne, so I wrote a new migrations API on top of the existing DbMerger
    constructs. The API is intended to be a fairly direct mapping of SQL DDL
    statements into Java. This approach was inspired by the Migrations API in
    Project Wonder that is used with WebObjects (where it is the only option,
    and is widely used). It uses database versioning.

    Creating a table with the API looks like this:

    MigrationTableNew artist = db.createTable("Artist");
    artist.addIntegerColumn("id", true, null); // “true” for isMandatory, null
    for defaultValue
    artist.addVarcharColumn("name", 100, true, null);
    cardProcessor.addPrimaryKey("id");

    Altering a table looks like this:

    MigrationTableExisting artist = db.alterTable("Artist");
    artist.addDateColumn("dateOfBirth");
    artist.alterColumn("name").setDataType(Types.VARCHAR, 200);

    This code is not written, but rather is generated directly from the DataMap.
    Question: Why would you want this rather than a plain raw SQL script?
    Answer: To maintain database independence (because syntax can differ
    between databases).

    Question: Why not use the existing migration features, such as
    DbGenerator
    or DbMerger?
    Answer: DbGenerator can’t modify existing schema, it just creates new
    databases. DbMerger is a bit too “magic” because you don’t know what
    operations it will actually perform since it’s operation varies based on
    the database’s current state. But there is more; below is a comparison of
    all the available options

    1) org.apache.cayenne.access.DbGenerator

    Automatically creates database from DataMap

    2) org.apache.cayenne.merge.DbMerger

    Automatically creates or modifies database to match DataMap

    3) org.apache.cayenne.migration.Migrator (proposed)

    API for creating or modifying a database with explicit code


    FeatureDbGeneratorDbMergerMigrator (new)Amount of Code requiredNoneSingle
    short method (see example below)Explicit code needed for each table,
    column, etc. for each version. But this code is generated, so it doesn’t
    need to be written, just generated and kept around.Can modify existing
    schema?NoYesYesPredictable?Yes, (mostly). It only does a complete creation
    of the DB.No, operations depend on DB state.Yes, operations are
    explicitly
    defined and do not vary with state.Complete, i.e. can perform any type of
    DDL operation.NoNoYes, arbitrary SQL statements can easily be inserted and
    run at any point during the migration.Database independent
    (portable)YesYes
    Yes, uses DbTokens for common operations and defines mechanism for handling
    DB-specific SQL for custom operations.CustomizableNoYes, somewhat. You can
    examine the MergerTokens and decide to skip them or reorder them, etc.Yes,
    explicit step by step execution allows customization at any
    point.Guarantees
    consist column order between databases created at different timesNoNoYes


    Example code using the new Migration package (this would be generated):

    public class Artist0 extends Migration { // version zero is the initial
    database creation
    public Artist0(DataNode node) {
    super(node);
    }
    public void upgrade(MigrationDatabase db) {
    MigrationTableNew artist = db.createTable("Artist");
    artist.addIntegerColumn("id", true, null); // “true” for isMandatory, null
    for defaultValue
    artist.addVarcharColumn("name", 100, true, null);
    cardProcessor.addPrimaryKey("id");

    MigrationTableNew painting = db.createTable("Painting");
    painting.addIntegerColumn("id", true, null);
    painting.addIntegerColumn("artist_id", true, null);
    painting.addVarcharColumn("name", 100, true, null);
    painting.addPrimaryKey("id");

    painting.addForeignKey("artist_id", "Artist", "id");
    }
    }

    public class Artist1 extends Migration { // version 1 is a later revision
    of the schema
    public Artist1(DataNode node) {
    super(node);
    }
    public void upgrade(MigrationDatabase db) {
    MigrationTableExisting artist = db.alterTable("Artist");
    artist.addDateColumn("dateOfBirth");
    artist.alterColumn("name").setDataType(Types.VARCHAR, 200);
    }
    }

    So ideally modeler would have a button to generate a migration, which would
    spit out a Migration subclass to create you database from scratch, OR to
    generate a Migration subclass with just the differences between your
    DataMap and the database when you are working with an existing DB. Right
    now I already have a command-line class that can generate the initial
    migration.*
    *
    *
    *Is there any interest in making this part of Cayenne? What questions do
    you have?*
    *
    *
    *Thanks,*
    *John Huss*

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdev @
categoriescayenne
postedDec 20, '11 at 8:02p
activeDec 22, '11 at 9:55p
posts12
users5
websitecayenne.apache.org

People

Translate

site design / logo © 2022 Grokbase