cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Huss <johnth...@gmail.com>
Subject Handling database creation and migration
Date Tue, 20 Dec 2011 20:01:32 GMT
*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