«
»

LiquiBase Formatted SQL

12th May 2010, 11:42 am

Part of the changes made in the upcoming 2.0 release is supporting the ability to specify changelog files in formats other than XML.

As a proof of concept, I added the ability to write your changelog files in specially formatted  SQL format rather than XML.

You can now write your changelogs like this:

–liquibase formatted sql

–changeset nvoxland:1
create table test1 (
id int primary key,
name varchar(255)
);

–changeset nvoxland:2
insert into test1 (id, name) values (1, ‘name 1′);
insert into test1 (id, name) values (2, ‘name 2′);

–changeset nvoxland:3 (dbms:oracle)
create sequence seq_test

which, when run, will run three separate changeSets on oracle, and two changesets on all other databases.  Note that this is specifying raw SQL, not abstracted liqubase changes like “createTable” that generate different SQL depending on the target database.

You do need to have your file contain “–liquibase formatted sql” on the first line, and delineate your changelogs with the “–changeset AUTHOR:ID” lines.

After the AUTHOR:ID, you can specify any attribute normally available on the or XML tags, including:

    stripComments
    splitStatements
    endDelimiter
    runOnChange
    runAlways
    context
    dbms
    runInTransaction
    failOnError

Since the formatted SQL builds the same internal changelog structure as the XML changelogs do, all the normal liquibase functionality (rollback, tag, dbdoc, updateCount, updateSQL, changelog parameters, etc.) are still available.

You can try out this new feature from the current 2.0 snapshot (http://liquibase.org/ci/latest).  Let me know if you have any suggestions or problems.   I am considering it an early access feature until 2.0 final is released, and there may be changes in the format of this file based on user feedback.

Category: Uncategorized  |  Comment (RSS)  |  Trackback

11 Comments

  1. Ron Piterman:

    Cool thing, looks very elegant and usefull, great !

    Also it is great to hear there is something going on in liquibase, had the impression the projekt is dead (or at least sleeping), the 2.0 snapshot is already there for quite some time, any plans or ideas when 2.0 final is going to be released?

    14 May 2010, 11:09 am

  2. Nathan Voxland:

    Definitely still active. 2.0 has taken longer to finish than I anticipated, but I am still working hard on it. I keep hoping to have another RC in a week or so and a final version soon after that, but minor issues keep coming up that I would like resolved for the 2.0 release.

    14 May 2010, 3:32 pm

  3. Alwyn Schoeman:

    Nathan, this looks like a really nice feature. Can we depend on it being part of 2.0?

    26 July 2010, 4:17 am

  4. Nathan Voxland:

    Yes, it is in there

    26 July 2010, 11:41 pm

  5. Anthony:

    How do you declare the rollback command?

    31 August 2010, 8:26 pm

  6. Nathan Voxland:

    add –rollback to the beginning of lines that denote the rollback sql

    1 September 2010, 11:40 pm

  7. Bob:

    Really cool is there anyway to put a rollback section in a SQL file?

    3 March 2011, 9:42 am

  8. Bob:

    Ah I see this was asked and answered! I was on small screen when I sent this and scrolled to the bottom and missed the previous post.

    3 March 2011, 9:43 am

  9. Bob:

    Ok I tried the rollback comment and have issues. Here is my script.

    –liquibase formatted sql

    –changeset henkbl:PROD111111
    CREATE VIEW all_employees AS
    SELECT *
    FROM employee;

    –rollback
    DROP VIEW all_employees;
    –rollback

    I also tried it with only the first rollback comment with same result. Here is the error I get when running LiquiBase from command line on Windows. Also note the view gets dropped but the error only occurs when I have the above SQL

    INFO 3/3/11 9:58 AM:liquibase: Successfully released change log lock
    Liquibase Update Failed: No inverse to liquibase.change.core.RawSQLChange created
    SEVERE 3/3/11 9:58 AM:liquibase: No inverse to liquibase.change.core.RawSQLChange created
    liquibase.exception.RollbackFailedException: liquibase.exception.RollbackImpossibleException: No inverse to liquibase.change.core.RawSQLChange created

    at liquibase.changelog.ChangeSet.rollback(ChangeSet.java:401)
    at liquibase.changelog.visitor.RollbackVisitor.visit(RollbackVisitor.java:23)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
    at liquibase.Liquibase.rollback(Liquibase.java:250)
    at liquibase.integration.commandline.Main.doMigration(Main.java:710)
    at liquibase.integration.commandline.Main.main(Main.java:116)
    Caused by: liquibase.exception.RollbackImpossibleException: No inverse to liquibase.change.core.RawSQLChange created
    at liquibase.change.AbstractChange.generateRollbackStatementsFromInverse(AbstractChange.java:145)
    at liquibase.change.AbstractChange.generateRollbackStatements(AbstractChange.java:115)
    at liquibase.database.AbstractDatabase.executeRollbackStatements(AbstractDatabase.java:1029)
    at liquibase.changelog.ChangeSet.rollback(ChangeSet.java:388)
    … 5 more

    For more information, use the –logLevel flag)

    C:\>

    3 March 2011, 11:02 am

  10. Nathan Voxland:

    The rollback comment needs to be put on each line of your rollback script. Like this:

    –liquibase formatted sql

    –changeset henkbl:PROD111111
    CREATE VIEW all_employees AS
    SELECT *
    FROM employee;

    –rollback DROP VIEW all_employees;

    23 March 2011, 4:03 pm

  11. Ben:

    Perhaps a little late to this discussion but can someone explain the “slpitStatements” construct to me? What happens with multiple SQL statements when this flag is set to “true” and what changes when set to “false”? Are these the only valid values?

    Thanks for any assistance or information!

    6 June 2012, 6:03 pm

Leave a comment