[ANNOUNCE] Scala Migrations 0.9.0

Given that "It is not a goal of Scala Migrations to check and report
on the compatibility of a Scala Migrations specific feature with a
database."

What is the benefit of wrapping the SQL in all those methods instead
of just Strings?

BR,
John

On Sun, Aug 2, 2009 at 12:58 AM, Blair Zajac wrote:
> Scala Migrations is a library to manage upgrades and rollbacks to database
> schemas.  Migrations allow a source control system to manage together the
> database schema and the code using the schema.  It is designed to allow
> multiple developers working on a project with a database backend to design
> schema modifications independently, apply the migrations to their local
> database for debugging and when complete, check them into a source control
> system to manage as one manages normal source code.  Other developers then
> check out the new migrations and apply them to their local database.
>  Finally, the migrations are used to migrate the production databases to the
> latest schema version.
>
> The package is based off Ruby on Rails Migrations and in fact shares the
> exact same schema_migrations table to manage the list of installed
> migrations.  The Scala Migrations library is written in Scala and makes use
> of the clean Scala language to write easy to understand migrations, which
> are also written in Scala.  Scala Migrations provides a database abstraction
> layer that allows migrations to target any supported database vendor.
>
> http://code.google.com/p/scala-migrations/
> http://scala-migrations.googlecode.com/files/scala-migrations-0.9.0.jar
>
> As a personal note, this is one of the first projects we got to open-source
> at Sony.  It took six months of work by the technical and legal teams at
> Imageworks, which is part of Sony Pictures, to go all the way to Sony Japan
> to get buyoff and approval for this.  Hopefully we'll see more projects in
> the future being open-sourced.
>
> A sample Scala migration looks like
>
> class Migrate_20081118201742_CreatePeopleTable
>  extends Migration
> {
>  def up() : Unit =
>  {
>    createTable("people") { t =>
>      t.varbinary("pk_people", PrimaryKey, Limit(16))
>      t.varbinary("pk_location", Limit(16), NotNull)
>      t.integer("employee_id", Unique)
>      t.integer("ssn", NotNull)
>      t.varchar("first_name", Limit(255), NotNull,
>                CharacterSet(Unicode))
>      t.char("middle_initial", Limit(1), Nullable,
>             CharacterSet(Unicode))
>      t.varchar("last_name", Limit(255), NotNull, CharacterSet(Unicode))
>      t.timestamp("birthdate", Limit(0), NotNull)
>      t.smallint("height", NotNull, Check("height > 0"))
>      t.smallint("weight", NotNull, Check("weight > 0"))
>      t.integer("vacation_days", NotNull, Default("0"))
>      t.bigint("hire_time_micros", NotNull)
>      t.decimal("salary", Precision(7), Scale(2), Check("salary > 0"))
>      t.blob("image")
>    }
>
>    addIndex("people", "ssn", Unique)
>
>    addForeignKey(on("people" -> "pk_location"),
>                  references("location" -> "pk_location"),
>                  OnDelete(Cascade),
>                  OnUpdate(Restrict))
>
>    addCheck(on("people" -> "vacation_days"), "vacation_days >= 0")
>  }
>
>  def down() : Unit =
>  {
>    dropTable("people")
>  }
> }
>
> And to install all migrations on a database:
>
> object Test
> {
>  def main(args: Array[String]) : Unit = {
>    val driver_class_name = "org.postgresql.Driver"
>    val vendor = Vendor.forDriver(driver_class_name)
>    val migration_adapter = DatabaseAdapter.forVendor(vendor, None)
>    val data_source: javax.sql.DataSource = ...
>    val migrator = new Migrator(data_source, migration_adapter)
>
>    // Now apply all migrations that are in the
>    // com.imageworks.vnp.dao.migrations package.
>    migrator.migrate(InstallAllMigrations,
>                     "com.imageworks.vnp.dao.migrations",
>                     false)
>  }
> }
>
> Regards,
> Blair
>
>

There's a long way from using strings which are not necessarily portable at all to the database abstraction Scala Migrations provides. For example, needing a VARBINARY column is something we support for all databases which otherwise you would need to special case the string code.

As the remaining section of the README stated, we didn't want to have to do the work to maintain that information for each database and potentially each database version as databases added new features and support. But now that the project is public it's much better for the compatibility knowledge to live in the code than in people's heads. So we could definitely remove that paragraph from the README and have the code warn developers. It already does warn and ignore some features, such as trying to use non-Unicode character sets in Derby.

If we did want to do database version level compatibility analysis, the code would have to expand, potentially to use java.sql. DatabaseMetaData#getDatabaseMajorVersion(), getDatabaseMinorVersion(), getDatabaseProductName() and getDatabaseProductVersion().

Patches are very welcome to expand the capabilities of the project, such as adding MySQL support and compatibility warnings.

Regards,
Blair

________________________________________
From: John Nilsson [john@milsson.nu]
Sent: Sunday, August 02, 2009 1:45 AM
To: Blair Zajac
Cc: scala@listes.epfl.ch
Subject: Re: [scala] [ANNOUNCE] Scala Migrations 0.9.0

Given that "It is not a goal of Scala Migrations to check and report
on the compatibility of a Scala Migrations specific feature with a
database."

What is the benefit of wrapping the SQL in all those methods instead
of just Strings?

BR,
John

On Sun, Aug 2, 2009 at 12:58 AM, Blair Zajac wrote:
> Scala Migrations is a library to manage upgrades and rollbacks to database
> schemas. Migrations allow a source control system to manage together the
> database schema and the code using the schema. It is designed to allow
> multiple developers working on a project with a database backend to design
> schema modifications independently, apply the migrations to their local
> database for debugging and when complete, check them into a source control
> system to manage as one manages normal source code. Other developers then
> check out the new migrations and apply them to their local database.
> Finally, the migrations are used to migrate the production databases to the
> latest schema version.
>
> The package is based off Ruby on Rails Migrations and in fact shares the
> exact same schema_migrations table to manage the list of installed
> migrations. The Scala Migrations library is written in Scala and makes use
> of the clean Scala language to write easy to understand migrations, which
> are also written in Scala. Scala Migrations provides a database abstraction
> layer that allows migrations to target any supported database vendor.
>
> http://code.google.com/p/scala-migrations/
> http://scala-migrations.googlecode.com/files/scala-migrations-0.9.0.jar
>
> As a personal note, this is one of the first projects we got to open-source
> at Sony. It took six months of work by the technical and legal teams at
> Imageworks, which is part of Sony Pictures, to go all the way to Sony Japan
> to get buyoff and approval for this. Hopefully we'll see more projects in
> the future being open-sourced.
>
> A sample Scala migration looks like
>
> class Migrate_20081118201742_CreatePeopleTable
> extends Migration
> {
> def up() : Unit =
> {
> createTable("people") { t =>
> t.varbinary("pk_people", PrimaryKey, Limit(16))
> t.varbinary("pk_location", Limit(16), NotNull)
> t.integer("employee_id", Unique)
> t.integer("ssn", NotNull)
> t.varchar("first_name", Limit(255), NotNull,
> CharacterSet(Unicode))
> t.char("middle_initial", Limit(1), Nullable,
> CharacterSet(Unicode))
> t.varchar("last_name", Limit(255), NotNull, CharacterSet(Unicode))
> t.timestamp("birthdate", Limit(0), NotNull)
> t.smallint("height", NotNull, Check("height > 0"))
> t.smallint("weight", NotNull, Check("weight > 0"))
> t.integer("vacation_days", NotNull, Default("0"))
> t.bigint("hire_time_micros", NotNull)
> t.decimal("salary", Precision(7), Scale(2), Check("salary > 0"))
> t.blob("image")
> }
>
> addIndex("people", "ssn", Unique)
>
> addForeignKey(on("people" -> "pk_location"),
> references("location" -> "pk_location"),
> OnDelete(Cascade),
> OnUpdate(Restrict))
>
> addCheck(on("people" -> "vacation_days"), "vacation_days >= 0")
> }
>
> def down() : Unit =
> {
> dropTable("people")
> }
> }
>
> And to install all migrations on a database:
>
> object Test
> {
> def main(args: Array[String]) : Unit = {
> val driver_class_name = "org.postgresql.Driver"
> val vendor = Vendor.forDriver(driver_class_name)
> val migration_adapter = DatabaseAdapter.forVendor(vendor, None)
> val data_source: javax.sql.DataSource = ...
> val migrator = new Migrator(data_source, migration_adapter)
>
> // Now apply all migrations that are in the
> // com.imageworks.vnp.dao.migrations package.
> migrator.migrate(InstallAllMigrations,
> "com.imageworks.vnp.dao.migrations",
> false)
> }
> }
>
> Regards,
> Blair
>
>

On Mon, Aug 3, 2009 at 12:55 AM, Blair Zajac wrote:
> There's a long way from using strings which are not necessarily portable at all to the database abstraction Scala Migrations provides.

Ok. So database abstraction was one of the goals. Just me getting confused then.

BR,
John

This looks extremely cool. How does it compare in functionality to
Liquibase?

--Dave Griffith