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 <[email protected]> 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*
