cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Kienenberger <mkien...@gmail.com>
Subject Re: Handling database creation and migration
Date Wed, 21 Dec 2011 17:54:42 GMT
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 <johnthuss@gmail.com> 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*

Mime
View raw message