CODEWITHSUNDEEP
liquibase
Martin M
Martin M

Reputation: 13

Generating Liquibase metadata offline

I'm currently experimenting with Liquibase to generate SQL for our database migrations. Due to some constraints within our environment, we need to generate the SQL "offline" and then have that executed against the target database(s) by a DBA.

I've been able to use updateSQL / rollbackSQL with the Maven plugin to generate the SQL and that seems to work fine.

However, the output does not include any of the metadata information - i.e. there are no creates for the DATABASECHANGELOG table and none of the inserts for that table are included in the generated script.

Is it possible to include the metadata information in the generated SQL?

I'm using Liquibase 3.1.1 (Maven plugin is the same version). I've also tried this from the command line and the behaviour is consistent - i.e. I get the actual changes generated, but not the metadata.

Upvotes: 1

Views: 2154

Answers (3)

jgolebiewski
jgolebiewski

Reputation: 31

Extending previous responses:

There wasn't support for it until version 3.2, but now you can decide if you want DATABASECHANGELOG inserts to be included using query param in your connection string. (encountered this need myself, and confirmed working in version 4.15.0)

Example usage: url=offline:oracle?outputLiquibaseSql=all

outputLiquibaseSql parameter mentioned by simbo1905 is for offline connection type and has following acceptable values:

  • "none" will output no DATABASECHANGELOG statements
  • “data_only” will output only INSERT INTO DATABASECHANGELOG statements
  • “all” will output CREATE TABLE DATABASECHANGELOG if the CSV file does not exist as well as INSERT statements (default value)

Source:

Upvotes: 2

simbo1905
simbo1905

Reputation: 6862

Running the main method with the option "outputLiquibaseSql=true" as shown here: 

liquibase.integration.commandline.Main.main(new String[]{"--changeLogFile=src/test/resources/db.changelog.xml"
                ,"--outputFile=target/updateSql.txt"
                ,"--url=offline:unknown?outputLiquibaseSql=true"
                , "updateSQL"});

Generates SQL like:

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: src/test/resources/db.changelog.xml
-- Ran at: 12/04/20 11:51
-- Against: null@offline:unknown?outputLiquibaseSql=true
-- Liquibase version: 3.8.9
-- *********************************************************************

CREATE TABLE DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10));

-- Changeset src/test/resources/db.changelog.xml::createTable-example::liquibase-docs
CREATE TABLE public.person (address VARCHAR(255));

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('createTable-example', 'liquibase-docs', 'src/test/resources/db.changelog.xml', CURRENT_TIMESTAMP, 1, '8:49e8eb557129b33d282c4ad2fdc5d4d9', 'createTable tableName=person', '', 'EXECUTED', NULL, NULL, '3.8.9', '6688703163');

As it is running in "offline:unknown" mode it also outputs CSV which are the entries to put into the DATABASECHANGELOG table:

"ID","AUTHOR","FILENAME","DATEEXECUTED","ORDEREXECUTED","EXECTYPE","MD5SUM","DESCRIPTION","COMMENTS","TAG","LIQUIBASE","CONTEXTS","LABELS","DEPLOYMENT_ID"
"createTable-example","liquibase-docs","src/test/resources/db.changelog.xml","2020-04-12T11:51:43.178","2","EXECUTED","8:49e8eb557129b33d282c4ad2fdc5d4d9","createTable tableName=person",,"","3.8.9","()","","6688703163"

Upvotes: 0

Nathan Voxland
Nathan Voxland

Reputation: 15773

There is not support currently in 3.1.1. It will hopefully be added as a feature in 3.2. https://liquibase.jira.com/browse/CORE-1726.

Are you able to run updateSQL against a backup database that matches production? That will still not execute anything but will include the metadata statements as well. The backup would actually just need the databasechangelog table because that is all liquibase reads unless you are using preconditions.

Upvotes: 0

Related Questions