Liquibase supports "plain SQL" changelog files. You can add the formatted SQL changelog to an XML root changelog if you have more than one formatted SQL changelog or just use a formatted SQL changelog directly without the root XML changelog.
Note: When creating an SQL changelog file
with the generate-changelog and diff-changelog commands, add your database type name when you specify the changelog file. See the example below. If using the example, replace .oracle.sql with your database type. When in doubt about your database type name, check Supported Databases.
Example: liquibase --changelog-file=mychangelog.oracle.sql generate-changelog
liquibase --changelog-file=mychangelog.oracle.sql diff-changelog
The changelog files may contain arbitrary SQL statements. The statements are converted to custom sql Change Types.
Formatted SQL files use comments to provide Liquibase with metadata. Each SQL file must begin with the following comment:
changeset
Each changeset in a formatted SQL file begins with a comment of the form:
--changeset author:id attribute1:value1 attribute2:value2 [...]The changeset comment is followed by one or more SQL statements, separated by semicolons or by the value of the <endDelimiter> attribute).
If you use the Liquibase Pro feature runWith=sqlPlus, follow these rules with formatted SQL changelogs:
- Do not set the endDelimiter property on the changeset. This affects SQLPlus.
- Ensure that the SplitStatements attribute is set to true, which is the default value.
changeset attributes
Use the following attributes for your changesets:
stripComments | Removes any comments in the SQL before executing when it is set to true. Otherwise, set it to false. Default value is: true. |
splitStatements | Removes Liquibase split statements on ;'s and GO's when it is set to false. Default value is: true. |
rollbackSplitStatements | Removes Liquibase split statements on ;'s and GO's for rollback SQL when it is set to false. Default value is: true. |
endDelimiter | Specifies delimiter to apply to the end of the statement. Default value is: ";". It can also be set to "". |
rollbackEndDelimiter | Specifies delimiter to apply to the end of the statement for rollback SQL. Default value is: ";". It can also be set to "". |
runAlways | Executes the changeset on every run, even if it has been run before. |
runOnChange | Executes the change the first time it is seen and each time the changeset has been changed. |
context | Executes the change if the particular context was passed at runtime. Any string can be used for the context name and they are checked case-insensitively. |
logicalFilePath | Overrides the file name and path when creating the unique identifier of changesets. Required when moving or renaming changelogs. |
labels | Specifies labels that are a general way to categorize changesets like contexts, but working in the opposite way. Instead of defining a set of contexts at runtime and then a match expression in the changeset, you define a set of labels in the context and a match expression at runtime. |
runInTransaction | Specifies whether the changeset can be run as a single transaction (if possible). Default value is: true. |
failOnError | Specifies whether the migration fails if an error occurs while executing the changeset. |
dbms | Specifies the type of a database which that changeset is to be used for. When the migration step is running, it checks the database type against this attribute. Valid database type names are listed on the supported databases page. It is possible to list multiple databases separated by commas. You can also specify that a changeset is not applicable to a particular database type by prefixing with !. The keywords all and none are also available. |
Preconditions
Preconditions can be specified for each changeset. Currently, only the SQL check precondition is supported.
--preconditions onFail:HALT onError:HALT --precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM my_tableRollback actions
changesets may include statements to be applied when rolling back the changeset. Rollback statements have the following format of the comments:
--rollback SQL STATEMENTThe comment is a description of the changeset. The format of the comment can be one of the following:
- A multi-line comment that starts with /* and ends with */.
- A single-line comment starting with <space>–<space> and finishing at the end of the line.
Note: By default, the statements will be split on a ‘;' or ‘go' at the end of lines. Because of this, if you have a comment or other non-statement ending ‘;' or ‘go', don't put it at the end of a line or you will get an invalid SQL.
Future releases of Liquibase may use comments to generate documentation.
Note: When you add a comment to your changeset and deploy this changeset, the comment will not be applied to the DATABASECHANGELOG tracking table.
Valid checksum
Valid checksum is a checksum which is valid for a specific changeset, regardless of what is stored in the database. It is typically used when you need to change a changeset and don't want errors to be thrown on databases on which it has been already run. Nevertheless, it is not a recommended procedure.(Since 3.5).
--validCheckSum: 3:098f6bcd4621d373cade4e832627b4f6 --validCheckSum: 7:ad0234829205b9033196ba818f7a872bIgnore lines
The ignoreLines attribute allows you to ignore the specific lines. It is typically used when you run the same script with the other SQL tools. Since 3.7
Marking two lines to be ignored:
--changeset author:id1 CREATE OR REPLACE PACKAGE ... --ignoreLines:2 / show errors; --changeset author:id2 CREATE OR REPLACE PACKAGE BODY ...Making two lines to be ignored by using start-end syntax:
--changeset author:id1 CREATE OR REPLACE PACKAGE ... --ignoreLines:start / show errors>; --ignoreLines:end --changeset author:id2 CREATE OR REPLACE PACKAGE BODY ...Sample changelog
--liquibase formatted sql --changeset nvoxland:1 create table test1 ( id int primary key, name varchar(255) ); --rollback drop table test1; --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;Related Links
- Liquibase Works with Plain Old SQL