A data analyst makes changes to SQL queries and uses these comments to create a changelog

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:

--liquibase formatted sql

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:

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_table

Rollback actions

changesets may include statements to be applied when rolling back the changeset. Rollback statements have the following format of the comments:

--rollback SQL STATEMENT

The 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.

--comment: some comment

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

Ignore 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

What point during the analysis process does a data analyst use a changelog?

What does a SQL data analyst do?

Which of the following SQL functions can data analyst use to clean string variables?

What is data analytics in SQL?