I wrote about the new formatted sql option on the liquibase blog (http://blog.liquibase.org/2010/05/liquibase-formatted-sql.html)
If you could try out the newest 2.0 build from http://liquibase.org/ci/latest and let me know (in the blog comments, so everything is in one place) how it works for you, that would be great.
Nathan
Nathan, I’m going to give this a try.
Will it be possible to use a master xml file and then include sql formatted files?
Yes, you should be able to.
Nathan
Are the sql tag attributes like splitStatements, endDelimiter, etc still valid?
E.g. --sql (splitStatements:true,endDelimiter:GO)
I’m making some assumptions on the attribute use…
Yes. Although you don’t need the --sql portion. Since the formattedSql just supports hard-coded sql, you can include the sql tag paramters in the --changeset line.
Like: --changeset 1:nvoxland splitStatement:false endDelimiter:GO context:test runOnChange:true
Nathan
will the changesets still be automatically wrapped in a transaction?
Reason I’m asking is that my very first changeset adds a column to a table, the log says it is successful. Then a create stored procedure that refers to that column fails because the column doesn’t exist and looking at the database it doesn’t.
Yes, it should still be wrapping each changeSet in a transaction. What database are you using? Most databases will autocommit on add column, so if there is a transaction or not should not actually matter.
Can you post (at least part) of your file?
Nathan
Hi Nathan,
Database is SQL Server 2005. I’m suspecting that my rollback tag is ignored and the rollback sql executed. Will check with SQL Profiler.
The following is the relevant code for the column:
–changeset s1:add_col_cust_can_config_statement_delivery endDelimiter:GO
IF NOT EXISTS (
SELECT 1 FROM syscolumns
WHERE name = ‘cust_can_config_statement_delivery’
AND id = object_id(‘ssb_fi_sso_provider’))
BEGIN
ALTER TABLE [dbo].ssb_fi_sso_provider
ADD cust_can_config_statement_delivery BIT NOT NULL DEFAULT 0
END
GO
–rollback endDelimiter:GO
IF EXISTS (
SELECT 1 FROM syscolumns
WHERE name = ‘cust_can_config_statement_delivery’
AND id = object_id(‘ssb_fi_sso_provider’))
BEGIN
DECLARE @default_constraint_name VARCHAR(200)
SELECT @default_constraint_name = so.name
FROM sys.syscolumns AS sc INNER JOIN sys.objects AS so ON
sc.cdefault = so.object_id
WHERE sc.name = 'cust_can_config_statement_delivery'
AND sc.id = object_id('ssb_fi_sso_provider')
SET @default_constraint_name =
'ALTER TABLE ssb_fi_sso_provider DROP CONSTRAINT ' + @default_constraint_name
EXEC (@default_constraint_name)
ALTER TABLE ssb_fi_sso_provider
DROP COLUMN cust_can_config_statement_delivery
END
Apparently I hadn’t thought of including rollback SQL in the file. It’s a good idea, so I added it. I did make it look for a “–rollback changeSet” line to ensure that you really mean to start delineating what is for rollback.
Nathan
I’m having some issues with rollback.
If a changeset doesn’t have a rollback comment, or the rollback comment has no body, the rollback fails with:
SEVERE: ROLLBACK failed: liquibase.exception.RollbackImpossibleException: No inverse to liquibase.change.core.RawSQLChange created
That does make sense, unfortunately. We don’t have anything like an sql parser to understand what you are doing with the update SQL to know how to roll the changes back. With the normal XML format, if you use the change tags we can know what you are doing and often know how to undo it. Even in that format, there are changes that we don’t know how to automatically undo, such as drop table and raw sql.
In the end, that means that if you want rollback support using liquibase formatted sql, you are going to have to specify the rollback sql for each changeset.
The plan is to eventually have additional changelog formats as well, including more DSL-style one(s) that will specify liquibase changes, not sql, so we can auto-generate rollback statements.
Thinking about rollback support last night and tonight, I decided to make the change so you need to add --rollback at the beginning of each line that is part of the rollback block. SO you end up with something like:
–changeset nvoxland:1
select * from table1;
–changeset nvoxland:2 (stripComments:false splitStatements:false endDelimiter:X runOnChange:true runAlways:true context:y dbms:mysql runInTransaction:false failOnError:false)
create table table1
id int primary key
);
--rollback delete from table1;
--rollback drop table table1;
–ChangeSet nvoxland:3
create table table2 (
id int primary key
);
create table table3 (
id int primary key
);
--rollback drop table table2;
it makes you have to specify more --rollback lines, but I think it better points out the fact that they are SQL only executed during rollback. More importantly, it keeps the sql file runnable outside of liquibase as well. One of the goals I had with this format is to have it be a format that DBAs that want full control over their database and SQL to be able to feel comfortable with, and I think if they can keep their changelog in a format that they can simply paste into their normal SQL editor and execute and have it do the same thing (without the liquibase changelog table control etc.) that would be appreciated. If we have a --rollback comment then more sql that should only be run during a rollback, that would not work in this use case.
Nathan
I understand and support your point on the SQL still being executable if we have a rollback on every line. It will be a real pain with stored procs and we have lots and lots :) When will this be checked in?
The functionality I need is to be able to tell Liquibase that I don’t have a valid rollback for the specific SQL (nothing makes sense) and that it shouldn’t expect it and fail. Like the tag. Current behavior is to fail which is not ideal. If we can have that incorporated my formatted sql files should work.
I thought about the option of when you have a lot of lines in your rollback. It will be a pain, but at the same time, it is in that case that you will be most likely to get lost between what is rollback sql and what is update sql.
The change to require --rollback on all lines should be in trunk now.
I was going to implement the no rollback required logic but have a question: I’m thinking for that case we should look for a string like “–rollback not required” so we can support other rollback styles in the future, and make it obvious fro reading the file what it is saying. Does that string make sense? Does it seem like overkill and just and empty --rollback line should be fine?
Nathan
I think you are right to have additional text to specify no rollback for clarity purposes. Another developer might think the file incomplete if he just sees the empty rollback tag.
I’m struggling to find a better term than “not required”, all I could come up with is “no op/noop” for no operation, but that will probably be more confusing. In any case SQL should be tested before production so if somebody has typos in liquibase commands they should catch it early.
Btw. If every line starts with --rollback, how do we specify options for the --rollback like endDelimiter, etc. or will the options specified on the changeset line also apply for all rollback tags?
I was trying to think of a better option than “not required” as well but that was the best I could come up with. What it needs to get across is “there is no sql needed to roll this change back”. “No required” makes it sounds like it’s just optional or something. But “none” sounds like you shouldn’t roll the statement back and I don’t think “empty” would be any better.
The endDelimiter etc options should be taken from the changeSet parameters. That is how it works with the xml version currently, there are no options in the tag. I think we sould be able to fit parameters in down the road if we need to.
Nathan
Do we have the “not required” functionality in trunk yet?
I just committed it now. If you think of something better than not required in the next day or two, let me know.
I have it search for strings beginning with “not required” so you can have a line like “–rollback not required because nothing should be inserted into this table”
Nathan
Cool I’ll give it a go today or tomorrow.
Are you hinting that a 2.0 release is on the cards soon? 
It’s been in the cards for the last 8 months :)
I’m really hoping to get it wrapped up this week, I’m on vacation for a week the end of august, and if it gets pushed off much more,I’ll probably need to wait until after in case things go horribly wrong.
Nathan
On our side we would probably like to start using 2.0 in production early in September for patches. Our next major version of our product will go out around that time and although itself has not been ported to Liquibase yet, the patches will. RC6 would probably do initially.