liquibase

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.

How To: Execute Liquibase Database Change Log file (a.k.a. "driver file") from Visual Studio

The Liquibase Database Change Log file or as we refer to it, the “driver” file, is the root of all changesets. This is the file that is passed to Liquibase during execution as the changeLogFile parameter which lists all changesets that needs to be executed in order. And because we love Visual Studio, we’d like to execute the driver file right from the IDE.

First step is to create a batch file with the following contents. Place this file in a folder that you can easily remember (c:\Dev\Liquibase\LBUpdateSQL.bat):

C:\Dev\liquibase-3.3.3-bin\liquibase ^
--classpath="C:\Dev\liquibase-3.3.3-bin\lib\sqljdbc41.jar" ^
--driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" ^
--url=jdbc:"sqlserver://127.0.0.1;databaseName=TARGET_DB" ^
--defaultSchemaName=dbo ^
--username=******** ^
--password=******** ^
--changeLogFile=%1 ^
--logLevel=info ^
--logFile="C:\Dev\LiquiBase\logs\output.log" ^
update

Don’t forget to change the values for TARGET_DB, username and password. Also make sure that the paths are valid and appropriate (lines 1, 2 and 10).

Next step is to open Visual Studio and click on TOOLS > External Tools… and click the Add button. Fill-out all the fields as in below and make sure Close on exit is not checked:

liquibase-external-tool

Now this part is important. Make sure that the Initial directory points to the location of the driver file (in this case its C:\Dev\git-repos\DatabaseScripts\(project_name)\updates). This will ensure that the filename field in the DATABASECHANGELOG table only contains the filename of the changeset and not the full path. To understand why this is important, read more here.

Click on Ok to close the External Tools window.

Lastly,open the driver file in Visual Studio and click on TOOLS > Liquibase (this is what you placed in the Title field in the screenshot above).

run-driver-run

There should be a command window that pops up that shows if the execution has failed or succeeded. The equivalent of all of this is like executing the batch file via command window while on the folder that contains the driver file with the driver file as an argument.

How Liquibase Considers A Changeset As Unique

We have a sandbox here in the office that has a database that we run the Liquibase Database Change Log file (a.k.a. “driver file”) against every now and then. We do not develop against this database since one of its purpose is to ensure that the latest version of the driver file runs without problems.

A few days ago, I took a backup of that database and restored it to my local machine. According to the DATABASECHANGELOG table, the driver file was last ran on 17 JUN (see screenshot below). However, when I tried running the latest Liquibase driver file from /develop against my now local db, I got a variety of errors ranging from duplicate key values to tables/columns already existing.

databasechangelog-table

I might have missed the email but last time I checked, the idea was to allow running and re-running of the changesets without having a negative effect and without having the execution error out. I had to exclude a bunch of changeset entries in the driver file to get it to finish without reporting any errors. Below is the list with the reason why it failed:

liquibase-problem-files

An officemate tried to run the latest driver file on his local machine and worked without any problems. We checked the DATABASECHANGELOG table for the existence of the IDs of the changesets and to our surprise, they were already there. The errors being thrown due to the lack of precondition in the changesets are just a manifestation of a different problem altogether (although the lack of precondition on the changeset is a problem on its own). We were able to come to that conclusion because Liquibase will only try and run the changeset if that changeset’s ID is not on the DATABASECHANGELOG table. So why is Liquibase trying to execute a changeset when the ID of the changeset is already in the DATABASECHANGELOG table?

Upon further investigation, we came upon this question on StackOverflow: Liquibase tried to apply all changeset, even if database is present‌
The take away is that Liquibase tracks each changeset as a row in the DATABASECHANGELOG table which is composed of the combination of the “id”, “author” and a “filename” column.

If we go back to the first screenshot above, you’ll notice that the value for the FILENAME column after 17 JUN 2015 has changed. At this point, it means that I’ve restored a backup of that database to my local machine and the path where I’ve been executing Liquibase is different from the path where Liquibase was being executed on the sandbox in the office.

So how did we solve the problem?

1.) We cleaned up the filename column so that it only contains the actual filename of the changeset.*

2.) Made modifications on the way we were executing the driver file moving forward so that the FILENAME column only contains that – the filename without paths. (How To: Execute Liquibase Database Change Log file (a.k.a. “driver file”) from Visual Studio).

fixed-filename-only

.* truncated the DATABASECHANGELOG table, dropped all affected tables and ran the driver file.