Sure, that sounds good to me. Thanks. On Thu, Dec 22, 2011 at 2:49 PM, Andrus Adamchik <[email protected]>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* > >
