cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Huss <johnth...@gmail.com>
Subject Re: Handling database creation and migration
Date Thu, 22 Dec 2011 21:54:42 GMT
Sure, that sounds good to me.  Thanks.

On Thu, Dec 22, 2011 at 2:49 PM, Andrus Adamchik <andrus@objectstyle.org>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*
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message