LiquiBase Formatted SQL

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.

17 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?

  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.

  3. Alwyn Schoeman:

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

  4. Nathan Voxland:

    Yes, it is in there

  5. Anthony:

    How do you declare the rollback command?

  6. Nathan Voxland:

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

  7. Bob:

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

  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.

  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:\>

  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;

  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!

  12. David dsadub:

    (know it’s an old post but…)
    In the example of this post, you use parenthesis for the changeset attributes (in “–changeset nvoxland:3 (dbms:oracle)”)

    In the official documentation (http://www.liquibase.org/documentation/sql_format.html), there is no parenthesis.

    I’ve faced situations (chandeset declaring procedures for Oracle) where the format of the SQL changelog can be sensible.
    Are thoses parenthesis required or not?

    Thank you for the nice job!
    David

  13. Nathan Voxland:

    Looking at the code, it looks like it doesn’t matter in this case. Are you running into problems with it one way or the other?

  14. Anuvinay:

    I’m working with formatted SQL.I successfully did rollback using custom rollback SQL ,But how can I do automatic rollback without using custom rollback SQL ?
    Anybody please explain or provide any links that contains a sample on this feature.

    Thanks in advance.
    Anuvinaya

  15. Nathan Voxland:

    Formatted sql is designed to only support raw SQL both for defining the changeSets and also the rollback logic. If you want to work a higher level than the raw sql you will have to use XML, YAML or JSON changeLog formats.

    Note: you can mix and match formatted SQL and other formats using the tag.

  16. kulshresht:

    Please let me know what is wrong with below format. It was working fine earlier but not working now. Can anyone please help. Getting below error::

    –liquibase formatted sql

    –changeset kulshresht:1
    CREATE TABLE Employee
    (
    EmployeeId int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    Mobile varchar(255)
    );
    –rollback DROP TABLE Employee;

    Liquibase rollback Failed: liquibase.exception.RollbackImpossibleException: No inverse to liquibase.change.core.RawSQLChange created
    SEVERE 4/16/14 4:39 PM:liquibase: /Users/kulshreshtg/Desktop/liquibase-3.1.1-bin/changelog.sql::2::BugId: liquibase.exception.RollbackImpossibleException: 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:452)
    at liquibase.changelog.visitor.RollbackVisitor.visit(RollbackVisitor.java:32)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:64)
    at liquibase.Liquibase.rollback(Liquibase.java:437)
    at liquibase.Liquibase.rollback(Liquibase.java:414)
    at liquibase.integration.commandline.Main.doMigration(Main.java:899)
    at liquibase.integration.commandline.Main.main(Main.java:133)
    Caused by: liquibase.exception.RollbackImpossibleException: No inverse to liquibase.change.core.RawSQLChange created
    at liquibase.change.AbstractChange.generateRollbackStatementsFromInverse(AbstractChange.java:400)
    at liquibase.change.AbstractChange.generateRollbackStatements(AbstractChange.java:373)
    at liquibase.database.AbstractJdbcDatabase.executeRollbackStatements(AbstractJdbcDatabase.java:1206)
    at liquibase.changelog.ChangeSet.rollback(ChangeSet.java:438)
    … 6 more

  17. kulshresht:

    I got the response from Nathan. Actually, my changelog file has a second changeset which does not have a rollback block.Adding an empty –rollback block for second changeset solved the issue for me.

    Thanks Again Nathan :)

Leave a comment