Month: October 2015

How To: Generate SQL scripts from Liquibase changesets

There is a useful feature in Liquibase called Offline Database Support that allows you to generate the SQL scripts from the changesets without actually updating the database. This Offline Database Support functionality is called updateSql. This becomes handy when you cannot directly run Liquibase changesets on the target database or if the output of the changesets needs to be reviewed.

Note that updateSql checks the transactions within the DATABASECHANGELOG table to determine what SQL statements to generate. With that said, in the event that you cannot directly run the changesets against the target database, you can ask for the DATABASECHANGELOG table and use that to keep the history correct.

To streamline the process of generating SQL scripts, it would be useful to create a batch file that would contain the command and parameters. Let’s say that the filename is GenerateSQL.bat which currently contains the following values targeting an Oracle database:

C:\Dev\liquibase-3.3.2-bin\liquibase ^
--classpath="C:\Dev\liquibase-3.3.2-bin\lib\ojdbc7.jar" ^
--driver="oracle.jdbc.driver.OracleDriver" ^
--url=jdbc:"oracle:thin:@ORA-DBDEV:1521:DEVORA11G" ^
--username=DEV_LIQUIBASE_TEST ^
--password=asd123 ^
--changeLogFile="C:\Dev\LiquiBase\ORACLE\%1" ^
--logLevel=debug ^
--logFile="C:\Dev\LiquiBase\ORACLE\output.oracle.log" ^
updateSQL > C:\Dev\LiquiBase\ORACLE\output.oracle.%1.sql

where…
Line 01: Location of Liquibase binary
Line 02: Location of JDBC driver. Valid values are:

  • ojdbc7.jar (ORACLE)
  • sqljdbc41.jar (MSSQL)

Line 03: Name of the JDBC driver. Valid values are:

  • oracle.jdbc.driver.OracleDriver (ORACLE)
  • com.microsoft.sqlserver.jdbc.SQLServerDriver (MSSQL)

Line 04: Details of the database server. Valid values are:

  • jdbc:”oracle:thin:@<SERVER_NAME>:<PORT>:<SERVICE_ID>” (ORACLE)
    • jdbc:”oracle:thin:@SERVER01:1521:DEVORA11G”
  • jdbc:”sqlserver://<SERVER_NAME><SERVER_INSTANCE>;databaseName=<DB_NAME>” (MSSQL)
    • jdbc:”sqlserver://SERVER01SQL2014;databaseName=DEVSQL”

Line 05: Database server username
Line 06: Database server password
Line 07: Location of the Liquibase change set file (*.xml)
Line 09: The log file for the operation
Line 10: The file where the generated SQL scripts will be stored.

Lines 07 and 10 have “%1” which represents an argument passed during execution of the batch file. This batch file can be used as follows:

C:\Dev\Liquibase\GenerateSQL.bat ChangeSetFileName.xml

This command will create output.oracle.log and output.oracle.ChangeSetFileName.xml.sql

You can integrate this process in Visual Studio by following this article. Note that you need to change the contents of the batch file to use updateSql.

How To: Install Liquibase On Your Local Machine

STEP 1

Download all pre-requisites and dependencies:

* – Download sqljdbc_4.1.5605.100_enu.tar.gz then extract sqljdbc41.jar
** – Download ojdbc7.jar

STEP 2

  • Install Java JDK
  • Extract Liquibase to a folder on your local machine (e.g. c:devliquibase-3.3.3-bin)
  • Copy sqljdbc41.jar and ojdbc7.jar to the lib folder of Liquibase (e.g. c:devliquibase-3.3.3-binlib)
  • Modify the liquibase shell file located on the root folder (e.g. c:devliquibase-3.3.3-binliquibase.bat) by adding “-Xmx1024m” after JAVA_OPTS=
IF NOT DEFINED JAVA_OPTS set JAVA_OPTS="-Xmx1024m"

java -cp "%CP%" %JAVA_OPTS% liquibase.integration.commandline.Main %CMD_LINE_ARGS%

OPTIONAL:

  • Add Liquibase to your PATH environment variable by:
  • Right-clicking Computer > Properties > Advanced System Settings > Advanced tab
  • Click Environment Variables button
  • Edit PATH variable and put in the Liquibase folder (e.g. c:\dev\liquibase-3.3.3-bin)

Congratulations! You are now a proud owner of a computer with Liquibase.