cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Handling database creation and migration
Date Thu, 22 Dec 2011 20:49:24 GMT
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*


Mime
View raw message