LiquiBase

@leopinheiro: I want to use Liquibase with Rails (like a plugin), any idea?

When I started LiquiBase, I quickly threw out the idea of creating a Java port of Rail’s Migrations. Over the years, a common question is “why doesn’t this work like Migrations?” It is nice to see at least one person in the Rails community that sees the light

Thanks to the generous people at Atlassian, we now have a much nicer issue tracker and source browser than before. You can check out our Jira instance at http://liquibase.jira.com/browse/CORE and our svn repository at http://liquibase.jira.com/source/browse/CORE.

The issues were bulk moved from the sourceforge tracker, so I will be going over them to recategorize and prioritize them over the next week.

We also now have a message board at http://www.liquibase.org/forum in place of the old liquibase-user, liquibase-devel, and liquibase-doc mailing lists. A copy of all top-level (non-reply) posts are being forwarded to the mailing lists to help with the transition, but please use the forum from now on.

If you have any comments or suggestions on the new setup, please let me know.

Someone updated the the LiquiBase wiki documentation about  preconditions today.  It had been:

<preConditions>
<dbms type="oracle" />
<dbms type="mssql" />
</preConditions>
Will require running on Oracle AND MS-SQL, which will always be false, unless a huge and unexpected merger takes place.

and they changed it to:

<preConditions>
<dbms type="oracle" />
<dbms type="mysql" />
</preConditions>
Will require running on Oracle AND MySQL, which will always be false, unless a huge and unexpected merger takes place.

“just for the pure irony value.”

I am starting the extensibility refactoring at the *Statement class level and moving up from there.  I committed an initial version of what I am thinking to trunk, and am looking for feedback (either in comments, or directly to me at nathan [at] voxland.net).

Renamed liquibase.database.sql package to liquibase.database.statement.
We may want to support non-”sql” statements in the future, so the name change seemeded best

Separated out the creation of SQL from the *Statement class.
Before each Statement class had a “String getSqlStatement(Database)” method that would create a string containing the database specific sql required to run the statement.  Now, there is a liquibase.database.statement.generator package that contains classes that take a Statement instance containing the information about a statement (table name, column names, etc) and converts them into SQL strings.

There is now a  SqlGeneratorFactory singleton with a  “SqlGenerator getBestGenerator(SqlStatement statement, Database database)”  method.  When we get to the point where we have Statement classes that need to be executed, liquibase will call this method to get the correct SqlGenerator instance for a given statement and database and use that instance to create the sql to execute.  (this change has not been made yet)  .

The SqlGeneratorFactory has a list of all SqlGenerators available.  That list is built up via a reflection package search in the liquibase.database.statement.generator package and sub-packages, or via a SqlGeneratorFactory.register() method.  There will be a standard package of liquibase.database.statement.generator.ext that can be used by 3rd parties to have their statements registered automatically without using the register ethod.

I have started breaking up the Statement classes (although I haven’t removed the old getSqlStatement methods yet).  In the liquibase.database.statement.generator package there is an SqlGenerator interface which provides a getSpecializationLevel method, an isValid method, and a generateSql method.   In the getBestGenerator() method, the SqlGeneratorFactory will loop through all known generators and call the isValid method to determine if a given SqlGenerator would work for that combination of database and statement.  Once it has a list of good generators, it will call the getSpecialiazationLevel() method which returns an int corresponding to how “good” that generator is for that statement/database combo.  There should usually be a default generator for each statement that will have a level of 1.  There will often be database-specific versions of the generator that return a level of 5.  3rd party Statement generators can return any level they want to override the built in ones or other 3rd party generators.  If no generators are found for a database/statement combo, tyere is a NotImplementedGenerator that is returned that simply throws an exception.

New generateSql  method returns an array.
Before, there was a one-to-one correspondence between a Statement class, and the SQL generated.  I think there are situations, however, where a Statement may generate multiple SQL commands and so we should allow the return of an array.

generateSql returns instances of “Sql”, not Strings
This is the part I go back and forth on.  Since the goal of this refactoring is to give 3rd party developers an API to code against, I want to do my best to make sure that API is not going to change.  There has been talk in the past about building more flexibility into the system by giving back an syntax tree that can be modified before being executed.  The idea with the Sql interface, is that we would only have an “UnparsedSql” implementation at first, but may make smarter implementations in the future.

The question is: is this too much?  We now have a mechanism where we can override what sql string is generated for a particular database and/or statement,  is that going to cover all the low-level-sql-tweaking that we would need?  I feel like the Sql interface may allow some sort of cross-cutting sql modification, or similar, but it is just a half-thought out idea.

Improving tests
I am going to repurpose the test framework around the Statement classes that actually run them against the database.  This has not been done yet.

My plan for 1.10 is to focus on extensibility. I wanted to make sure I have good goals for that in mind, and that what we are planning to do will match up with those goals.

1.    More Extensibile API:
a.    Ability to override what SQL is sent to the database for standard databases and refactorings (Add engine innodb to all create table statements, LoadData on MSSQL should call “set identity insert on/off”, etc)
b.    Ability to add custom Change classes and use them from the changeset
c.    Ability to create a custom changelog parser as an alternative to the standard XML
d.    Better documentation of how the liquibase API can be used within an application
e.    Ensure that all liquibase APIs will remain stable for the foreseeable future

2.    Create 3rd party community. There are many refactorings that we do not want to support in the core because they are database specific or limited in scope (vacuum database, set identity_insert on, etc).
a.    Create a “contrib” area on the site that people can upload useful extensions that are not in the core
b.    Developer documentation on extending liquibase
c.    Better issue/feature tracker that supports extensions?
d.    Better mailing list/newsgroup setup
e.    Ensure that all documented extension points will remain stable for the foreseeable future

3.    Make core codebase more approachable to submissions:
a.    Better developer documentation
b.    Document migration path from 3rd party extension to core functionality
c.    Refactoring of codebase to make it easier to add new Database, Change, and Statement classes without touching as much other code
d.    Better unit test coverage to stop regression errors

Is there any particular use cases related to extensibility you want to make sure are addressed?  What am I missing?

I plan to follow up with requests for in-depth comments on each of these steps as we get to them and can say exactly what we are implementing.

While the main goal of LiquiBase is database change tracking, I found a very different use for it lately: moving test datasets.

I have an application that has been traditionally developed on MySQL, but am in the process adding MS SQL support as well. The application uses Hibernate so it should work in theory, but does it really work on SqlServer?

Since I have unit tests already set up that actually execute the data access layer against the database, I can run them against MS SQL instead, but the tests require data (and often specific data).

I wasn’t looking forward to duplicating my test data management effort, and so I started looking for a way to move the data from MySQL to SqlServer.  I tried mysqldump (even with the “mssql compatability mode”) but it was still too MySQL specific.  There are MS SQL migration tools, but I wanted it to be automated and be flexible enough to move the app to Oracle, DB2, etc. in the future.  In particular, I was also wondering if running the DAO tests against H2 would be faster, but that is another blog post…

What I ended up doing was using LiquiBase’s generateChangeLog functionality to output the database schema and data.  The steps I run are:

1. Call generateChangeLog telling liquibase to export data as well.  I used the LiquiBase API to integrate it into my script easier.
2. Load the changelog into a DOM and update any mysql specific or SqlServer unsupported functionality using xpath.  For example, convert BIGINT(20) to BIGINT.
3. Run the changeLog against the target database.  This may take a bit of trial and error to find all your particular database incompatabilities.
4. Delete all rows from the target database’s databasechangelog table
5. Select all rows from the base database’s databasechangelog table and insert the values into the new database’s databasechangelog table.  This will make sure that the database history liquibase sees in future attempts at updating is correct.

Now, I can continue to manage my dataset in MySQL like I always have but still magically have my test database and dataset available for testing against using MS SQL.

While I would not recommend using this for a production backup/restore scenario (the LiquiBase diff tool does not export everything), it has been working great for managing and migrating the test dataset.

LiquiBase 1.9.2 has been released.  It is a bugfix release, mainly around issues with the includeAll tag.

Like always, it can be downloaded from http://www.liquibase.org/download