Liquibase and changesets on pure SQL

Not everyone knows, but since version 2.0 Liquibase supports the ability to use files with “pure SQL” as changesets. Under the cut I want to describe a little what they consist of.

Comments are used to define metadata in SQL files, each changeset file starts with a comment:

--liquibase formatted sql 

Each changeset in the file begins with a commentary, in which all necessary parameters are indicated as follows:

 --changeset author:id attribute1:value1 attribute2:value2 [...] 

The following attributes can be set for a changeset:

Attribute	Description
stripComments	If set to true, all comments are deleted before executing SQL statements. The default is true.
splitStatements	If set to false, Liquibase will not separate the SQL expressions on the “;” character, used to describe the subroutines.
endDelimiter	Specifies the SQL statement separator, the default is ";".
runAlways	If set to true, the list of changes will be executed during each build of the project.
runOnChange	If set to true, then when you edit the changeset, it will be executed at the next build of the project.
context	Creating a label for a changeset, which can later be done on request.
runInTransaction	If set to true, all SQL statements will be executed as part of a single transaction, if possible. The default is true.
failOnError	True - the entire changeset will be canceled if errors occur during execution.
dbms	An indication of the type of DBMS for which the changeset is written.

After setting the parameters, preconditions are specified (optional). Next comes a set of SQL statements, separated by a semicolon or character, specified in the endDelimiter attribute.
')
Currently, only one type of precondition is supported in changesets on pure SQL: SQL Check. An example of a precondition:

 --precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM my_table 

The expectedResult parameter is passed a value that the SQL query returns. The query must return a single value.

To set the behavior of the precondition check processing, the syntax is the same as setting changeset parameters:

 --preconditions attribute1:value1 attribute2:value2 [...] 

Attributes can be the following:

Attribute	Description
onFail	Actions in case the changeset cannot be executed.
onError	Actions in case the changeset returns an error.
onUpdateSQL	Actions that will be performed on the changeset if it is executed in updateSQL mode.
onFailMessage	The message that will be returned if the changeset cannot be executed.
onErrorMessage	The message that will be returned if the changeset is executed with an error.

The following values ​​can be passed to the onFail and onError attributes:

Value	Description
HALT	Immediately terminate the changeset.
CONTINUE	The contents of the changeset will be skipped and an attempt will be made to re-execute it the next time.
MARK_RAN	Changeset will be marked as completed.
WARN	A warning will be generated and the changeset will continue to run normally.

Changesets can include SQL statements for rollback. Rollback expressions are described in the comment form:

 --rollback SQL STATEMENT 

Well, in conclusion, a small example of a changeset file:

 --liquibase formatted sql --changeset User1:1 create table test1 ( id int primary key, name varchar(255) ); --rollback drop table test1; --changeset User2:1 --preconditions onFail:CONTINUE onError:CONTINUE --precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM test1 insert into test1 (id, name) values (1, 'User1'); --rollback delete from test1 where id = 1; 

Summarizing all the above, I would like to add that such files are read and written much easier than xml files, but not all the buns are still supported (an example of preconditions, only SQL Check is supported now).