Liquibase and Datical

For those of you who don’t know, I’ve been working for Datical for the last year and a half as “Benevolent Dictator for Life.” I often joke that I feel like I’m retired because after years of working on Liquibase as a hobby on nights and weekends I’m now spending my days working on my “hobby”.

The rest of the Datical team works on Datical DB which uses Liquibase. Datical DB wraps additional functionality around Liquibase such as:

  • Database specific objects such as Oracle Packages & SQL Server Functions
  • Forecast to simulate deployments
  • HTML reporting
  • Out-of-the-box integrations with Jenkins, IBM UrbanCode, CA Release Automation, and others
  • Rules Engine (using Drools) to help you control specific objects in your deployments.

I’ve always considered the scope of Liquibase to be “git for your database”–flexible but with a single, vendor neutral purpose that is part of an overall application deployment process. Datical DB builds out the rest of toolset for those who need it.

To help highlight and differentiate Liquibase and Datical, I’ve made a few updates to liquibase.org including a feature comparison on the download page and a more descriptive “Enterprise Version” menu link. Liquibase continues to be a separate product from Datical and will always remain open source. The changes are simply to point people to Datical DB if they are interested without getting in the way of those who simply use Liquibase.

Liquibase 3.3.3 Released

Liquibase 3.3.3 is primarily a bugfix release

As always, Liquibase can be downloaded from the Liquibase download page and is available in the Maven repository as org.liquibase/liquibase-core.

Fixed Issues:

  • [CORE-1768] – Oracle dropAll fails on spatial tables and sequences
  • [CORE-1840] – Liquibase fails when run on a computer that can’t connect to the internet
  • [CORE-1857] – Wrong column size detection on varchar2 fields with char as datatype
  • [CORE-1866] – Filtering changelog list by includeAll tag is not working
  • [CORE-1943] – Handle Error: InetAddress.getLocalHost().getHostName() UnknownHostException results in NoClassDefFoundError
  • [CORE-1958] – Column type of “TIMESTAMP(6)” under MySql converted to TIMESTAMP dropping fractional seconds
  • [CORE-1967] – includeAll uses full file path for sql changelogs
  • [CORE-2023] – Problem using includeAll with SpringLiquibase
  • [CORE-2126] – Postgres 9.3 – Drop table With Cascade – Not Supported
  • [CORE-2156] – Resource loader can’t load changelog file
  • [CORE-2186] – AbstractResourceAccessor#convertToPath(String, String) fails for processing includeAll from Classpath
  • [CORE-2192] – NoSuchMethodException when generating offline Oracle migration script
  • [CORE-2199] – Liquibase adds a semicolon after a stored proc definition making the stored proc unusable
  • [CORE-2202] – liquibase.should.run inverted boolean
  • [CORE-2204] – valueNumeric not being set when using prepared statements
  • [CORE-2206] – diffChangeLog with JPA-annotated entities causes ConcurrentModificationException
  • [CORE-2208] – Typo in message
  • [CORE-2210] – java.lang.NullPointerException when file is empty
  • [CORE-2214] – When inserting string value starting and ending with apostrophes (quotes) the value is not quoted in the generated SQL
  • [CORE-2218] – Regression on modifyDataType : VARCHAR2 was supported on 3.2…and fails on 3.3
  • [CORE-2239] – Remarks attribute in renameColumn causes parse error
  • [CORE-2240] – setDropFirst(true) still broken on empty database
  • [CORE-2262] – 3.3.2 ant task dies on NPE in ChangeLogParameters
  • [CORE-2263] – Index Snapshot – doesn’t include upper cased name indexes when db is NOT case sensitive
  • [CORE-2274] – Ant Upade Task does not consider changeLogFile correctly if it is contained in a JAR
  • [CORE-2279] – Rollback fails in MS SQL 2008 using liquibase 3.3.2
  • [CORE-2284] – Creating a DatabaseChangeLog() results in NPE
  • [CORE-2290] – Liquibase gives different results from Ant and the command line
  • [CORE-2301] – Regression from 3.2.3 in mssql 2000 unsupported usage of varchar(max) and sys.extenden_properties
  • [CORE-2304] – Autoincrement on type INT4 fails
  • [CORE-2310] – IncludeAll Fails with Unknown Reason Error
  • [CORE-2315] – NPE in CommandlineResourceAccessor
  • [CORE-2325] – Liquibase – New versions break DB create
  • [CORE-2329] – Escaped reserved keywords in HSQL are stored in lower case instead of upper case.
  • [CORE-2330] – includeAll uses full file path with includeAll
  • [CORE-2261] – UpdateSQL needs to append a “/” to the end of createProcedure for Oracle
  • [CORE-2287] – Improve support for Groovy-based tests in Eclipse
  • [CORE-2296] – Upgrade Groovy and Spock to maintained versions
  • [CORE-2318] – Add support for converting BigDecimal objects to a SQL string via DataTypeFactory

Liquibase 3.3.2 Released

Liquibase 3.3.2 is officially released. It is primarily a bugfix release, but has one major new feature: object diffChangeLog/generateChangeLog object filtering.

includeObjects/excludeObjects logic

You can now set an includeObjects or excludeObjects paramter on the command line or Ant. For maven, the parameteres are diffExcludeObjects  and diffIncludeObjects. The format for these parameters are:

  • An object name (actually a regexp) will match any object whose name matches the regexp.
  • A type:name syntax that matches the regexp name for objects of the given type
  • If you want multiple expressions, comma separate them
  • The type:name logic will be applied to the tables containing columns, indexes, etc.

NOTE: name comparison is case sensitive. If you want insensitive logic, use the `(?i)` regexp flag.

Example Filters:

  • “table_name” will match a table called “table_name” but not “other_table” or “TABLE_NAME”
  • “(i?)table_name” will match a table called “table_name” and “TABLE_NAME”
  • “table_name” will match all columns in the table table_name
  • “table:table_name” will match a table called table_name but not a column named table_name
  • “table:table_name, column:*._lock” will match a table called table_name and all columns that end with “_lock”

Full 3.3.2 Change Log:

  • [CORE-875] – Ignore tables for diffs and generateChangelog
  • [CORE-1877] – SQLOutput prints endDelimiter regexes
  • [CORE-2114] – AddAutoIncrement on Postgres does not work when changes are applied on a specific schema
  • [CORE-2141] – handling dependencies and WAR as classpath
  • [CORE-2166] – SpringLiquibase: includeAll within jar causes SetupException
  • [CORE-2172] – dropPrimaryKey without constraint name on sql server doesn’t honour schema information
  • [CORE-2174] – Bad exception handling in OracleDatabase.setConnection
  • [CORE-2180] – NPE with bad name
  • [CORE-2182] – ClassLoader leak due to shutdown hooks

Since the 3.3.0 announcement, 3.3.1 was also released in December as a bugfix release with the following changes:

  • [CORE-1920] – SpringLiqubase includeAll is not including files
  • [CORE-2009] – ClassCastException when executing a custom task change (AntClassLoader problem)
  • [CORE-2097] – “mvn liquibase:futureRollbackSQL” asks for tag, count or date
  • [CORE-2099] – SQLAnywhere support (Driver not capable)
  • [CORE-2103] – changelogSchemaName/changelogCatalogName configuration options will not work on Oracle DB
  • [CORE-2104] – ConcurrentModificationException iterating over System.getProperties().entrySet()
  • [CORE-2105] – Maven profile performing dropAll and update on Oracle failing with an error on populated database.
  • [CORE-2107] – LOWER() keyword fails on Postgres createIndex task
  • [CORE-2108] – dropAll command trying to drop column on table that has already been dropped
  • [CORE-2116] – Could not find implementation of liquibase.logging.Logger
  • [CORE-2118] – Change default diffChangeLog/generateChangeLog objectQuotingStrategy back to LEGACY
  • [CORE-2119] – Bad finally block in SpringLiquibase.afterPropertiesSet()
  • [CORE-2120] – LoadUpdateData with value=NUMERIC quoting values
  • [CORE-2121] – DB2: DiffChangeLog/GenerateChangeLog/DropAll sees alias column and tries to drop/add them
  • [CORE-2127] – updateSQL creates duplicate DATABASECHANGELOGLOCK tables
  • [CORE-2130] – setFetchSize to a negative value breaks Oracle JDBC Driver
  • [CORE-2134] – ExecuteCommand won’t run with no os attribute.
  • [CORE-2136] – Mysql must quote PARTITION as a keyword
  • [CORE-2137] – Special characters (
) copied during generateChangelog on DB2/400
  • [CORE-2139] – H2Database.supportsDropTableCascadeConstraints() returns false
  • [CORE-2142] – generateChangeLog not including all columns in a table
  • [CORE-2146] – snakeyaml is pulled in as transitive dependency for using projects
  • [CORE-2149] – Liquibase command line fails
  • [CORE-2150] – On the 3.3.0-SNAPSHOT, liquibase –version returns 3.2.0
  • [CORE-2153] – Liquibase 3.2.1 is no longer compatible with Oracle 9
  • [CORE-2155] – diffTypes=data fails with java.sql.SQLException: Attribute value not valid (dataOutputDirectory attribute causes build to fail)
  • [CORE-2156] – Resource loader can’t load changelog file
  • [CORE-2157] – SQLException if there are single quotes in ChangeSet
  • [CORE-2159] – Datetime2 no longer used for MSSQL
  • [CORE-2161] – includeAll relativeToChangelogFile=”true” doesn’t work
  • [CORE-2164] – SpringLiquibase: includeAll within jar causes NullPointerException
  • [CORE-2179] – Creating functional indexes
  • [CORE-2115] – Really slow when using fat jars
  • [CORE-2125] – Make DatabaseChangeLog#include(String, boolean, ResourceAccessor) public
  • [CORE-2148] – Build failure on jdk-1.8
  • [CORE-2152] – Change logs in json format not processed by liquibase – parsing errors

 Updated Exensions

The following extensions have also been recently updated with bugfixes, new features and support for Liquibase 3.3.x

Download

As always, Liquibase can be downloaded from the Liquibase download page and is available in the Maven repository as org.liquibase/liquibase-core. The extensions can be downloaded from their corresponding github repository “Release” pages.

Contexts vs. Labels

A new feature with Liquibase 3.3 is “labels”. Labels are similar to contexts in that both allow you to chose a subset of changeSets to execute at runtime. Labels are also similar to contexts in that both are purposely vague terms because they are fairly generic features can enable many different use cases. Where they differ is in who has the power to specify complex logic: the changeSet author or the deployment manager.

Contexts

Contexts in Liquiase have been available for quite a while, and they started out primarily as a way of “tagging” changeSets so they can be chosen at runtime. One common use is to mark changeSets that insert test data as context=”test” so that in your development and QA environments you you can run liquibase with –contexts=test to get the test data and in production you run with –contexts=prod to not have test data. Contexts are also helpful for marking changeSets based on feature sets to include (context=”shoppingCart”) or bundle (context=”pro”) or even customer (context=”acme_inc”). For complex cases, multiple contexts can be applied to a changeSet such as context=”acme_inc, pro” and multiple contexs can be chosen at runtime such as –contexts=free,qa.

With Liquibase 3.2, support was added to for context expressions in changeSets. Now, when you are defining your changeSet you can specify complex logic such as context=”!test” or context=”qa or (acme_inc and dev)”. The context logic can only be specified in your changeSet definition, however. When running Liquibase, you can still specify multiple contexts, but you are just listing out all the contexts that apply to the current Liquibase run.

Labels

Labels were added in Liquibase 3.3 to work like contexts, but “backwards” in who can specify logical expressions. In your changeSet you can only specify a simple list of “labels” that apply to the changeSet but at runtime you can write a complex expression to chose the labels you want to execute. This allows you to specify a changeSet with labels=”qa, acme_inc” and then at runtime use expressions such as –labels=”!acme_inc” or –labels=”pro or (free and beta)”.

Which is right for you? 

Whether you should use contexts or labels comes down to whether the changeSet writer or the Liquibase executor best understands and/or needs the most control over which changeSets to execute.

  • If the changeSet author needs to be able to specify complex logic based on the kind of environment that Liquibase will run in, use contexts.
  • If the person executing Liquibase needs to specify complex logic to chose changeSets to run, use labels.
  • If you do not use complex expressions, there is no functional difference between them.

Remember: you can use both.

Example Use Cases

Contexts work best when you can simply enumerate/describe features of the runtime environment:

  • Free vs Pro versions
  • QA vs. Dev. vs. Prod environments
  • Customer A vs. Customer B

Labels work best when you can simply enumerate/describe what a changeSet is for, but the deployment time environment is complex to describe. An example of when labels would work well is when you can describe changeSets as for a particular feature or version such as “1.0″ and/or “shopping_cart” but the decision on which features and/or versions needs to run is complex and chosen at deployment time. Labels in this case would allow you to run with –labels=”1.0 or (1.1 and shopping_cart)” to deploy the 1.0 changeSets and only the 1.1. features related to the shopping cart to one database and –labels=”1.0 or (1.1 and !shopping_cart)” to another database.

When in doubt, I usually go with contexts because that will simplify deployment configuration (to minimize release-day problems) while giving changeSet authors the option to handle complex logic if needed.

 

Liquibase 3.3.0 and 3.2.3 Released

Liquibase 3.2.3 and 3.3.0 have been released. As usual, they can be downloaded from the Liquibase download page and are available in the Maven repository as org.liquibase/liquibase-core.

Both 3.2.3 and 3.3.0 should be drop-in replacements for 3.2.2. A new batch of Liquibase extensions will be released over the next few days.

New “label” attribute on changeSet

Labels are general purpose 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.

The most common time you would use labels instead of contexts is when the person doing the liquibase update has the knowledge of the types of changeSets to run, not the person writing the changeSet.

Labels can also be applied to modifySql

New change log commands and attributes

  • New “empty” tag for explicitly marking a changeSet as unused
  • New “output” tag for outputting a message during Liquibase update.
  • New relativeToChangeLogFile attribute for loadData and loadUpdateDate
  • New  fullDefinition=true|false attribute on createView to support defining an entire view definition (including “column” names)

Support for clustered/nonclustered indexes and primary keys

A new “clustered=’true|false’” attribute is now avaiable on createIndex and createPrimaryKey to control whether they should be created as clustered or not.

And More

  • Saving of “remarks” in MySQL and MSSQL
  • Improved data type handling
  • Performance improvements
  • Official RPM and DEB packages built with release
  • Major refactoring and updating of Ant integration
  • Full release notes below

3.2.3 is a patch release with smaller bug fixes. Even if not explicitly listed in the changelogs below, anything in 3.2.3 will be in 3.3.0.

3.2.3 Change Log

  • [CORE-1919] – SpringLiquibase fails when dropFirst is true
  • [CORE-1987] – “mvn liquibase:diff” does not find any differences between databases
  • [CORE-1988] – Reported size for Oracle NVARCHAR2 columns is wrong
  • [CORE-1989] – Cannot set objectQuotingStrategy on root databaseChangeLog node
  • [CORE-2002] – AbstractResourceAccessor generates path in a unpredictable way
  • [CORE-2003] – Could not find implementation of liquibase.logging.Logger
  • [CORE-2042] – If liquibase.jar is nested in another jar/war/ear, it fails to start with a “cannot find implementation of liquibase.logging.Logger” error
  • [CORE-2058] – Load/Update tags should use “is null” not “= null” for null comparisons
  • [CORE-2070] – dropAllForeignKeyConstraints does not work on Firebird databases
  • [CORE-2075] – generateChangelog generates bad definition for TIME type
  • [CORE-2080] – Liquibase “empty” change not present in XSD version 3.2
  • [CORE-2065] – Use DOUBLE PRECISION for DOUBLE with Firebird
  • [CORE-54] – Support System Properties in Maven Plugin

 

3.3.0 Change Log

New Features

  • [CORE-16] – Support for “nonclustered” primary keys in mssql
  • [CORE-54] – Support System Properties in Maven Plugin
  • [CORE-1528] – Installer for Liquibase
  • [CORE-1598] – support for rename sequence
  • [CORE-1914] – New Change function: output
  • [CORE-1942] – Support for changeSet “labels”

Improvements

  • [CORE-549] – relativeToChangelogFile for loadData, loadUpdateData, sqlFile
  • [CORE-1438] – createView should support having the entire view definition in the change body
  • [CORE-1502] – CLONE – UpdateSQL needs to append a “/” to the end of createProcedure for Oracle
  • [CORE-1654] – logicalFilePath support in formatted sql
  • [CORE-1660] – “remarks” attribute is ignored in MSSQL
  • [CORE-1932] – support for encrypted passwords / custom properties
  • [CORE-1946] – Have a rpm package for liquibase (built with maven)
  • [CORE-1963] – Ability to define full CREATE VIEW statement in <createView> change.
  • [CORE-1990] – Preserve inline comments in view snapshots in mssql
  • [CORE-2060] – Support liquibase.properties files with unknown properties
  • [CORE-2061] – Improvements to Informix support
  • [CORE-2062] – Add onlyUpdate flag to loadUpdateData
  • [CORE-2064] – Use ignoreClassPathPrefix for rollback as well
  • [CORE-2065] – Use DOUBLE PRECISION for DOUBLE with Firebird
  • [CORE-2066] – Support for –outputFile in command line
  • [CORE-2067] – Refactor Ant Task codebase
  • [CORE-2068] – New liquibase.hostDescription property for additional details in the DATABASECHANGELOGLOCK table
  • [CORE-2069] – Use prepared statement in <update> change whenever a clob type is used
  • [CORE-2072] – Do not include Oracle internal tables in snapshot/diff

Bugs

  • [CORE-870] – Postgres, in an ALTER TABLE ALTER COLUMN statement, sometimes needs USING clause
  • [CORE-945] – Oracle : Temporary tables are created as regular tables
  • [CORE-1463] – Views not generated correctly with generateChangelog
  • [CORE-1556] – remarks attribute ignored for mysql
  • [CORE-1723] – unable to update on DB2/400, version V6R1, on jt400-6.7.jar
  • [CORE-1745] – afterColumn not working in MySQL
  • [CORE-1774] – Autocommit not restored on close in SpringLiquibase
  • [CORE-1882] – NullPointerException when MySQL foreign key points to an invalid table
  • [CORE-1919] – SpringLiquibase fails when dropFirst is true
  • [CORE-1922] – Sequence is not a reserved object name in HSQLDB
  • [CORE-1925] – liquibase scripts can not represent clustered indexes
  • [CORE-1937] – Oracle Float and VARCHAR precisions in changelog generated by generateChangeLog are incorrect
  • [CORE-1952] – liquibase loadData does not properly load numeric field in boolean always as false
  • [CORE-1956] – Double and float converted to FLOAT8(*, 17) and FLOAT4(*, 8) in PostgreSQL
  • [CORE-1958] – Column type of “TIMESTAMP(6)” under MySql converted to TIMESTAMP dropping fractional seconds
  • [CORE-1974] – dbchangelog-3.1.xsd missing <empty>
  • [CORE-1977] – CreateSequence with cacheSize=0 failing on Oracle
  • [CORE-1979] – MSSQL should not include parameters in SYSNAME data types
  • [CORE-1981] – Parameters set in included file are no longer set in 3.2.0
  • [CORE-1982] – Snapshot outputs defautlValueDate as defaultValueComputed on MSSQL for dates not in ISO format with a T in the middle
  • [CORE-1986] – includeAll from changeLogs within a jar is not working
  • [CORE-1988] – Reported size for Oracle NVARCHAR2 columns is wrong
  • [CORE-1993] – Drop table with cascade is not supported by Sybase
  • [CORE-1996] – addNotNullConstraint on h2 database has unexpected side effects
  • [CORE-1997] – Bit changelog default value of 1 executed as 0
  • [CORE-2002] – AbstractResourceAccessor generates path in a unpredictable way
  • [CORE-2010] – Oracle data type SDO_GEOMETRY snapshotted as SDO_GEOMETRY(1)
  • [CORE-2014] – applyToRollback property ignored when rollback changes are specified
  • [CORE-2015] – DiffChangeLog writes to the wrong point in the file on windows if file uses \n not \r\n
  • [CORE-2020] – Oracle default value current_timestamp converted to systimestamp
  • [CORE-2021] – Column remarks not snapshotted in mssql
  • [CORE-2026] – Oracle columns of type ANYDATA are snapshotted with a size
  • [CORE-2028] – generateChangeLog on SQL Anywhere 11.0.1 throws DatabaseException Driver Not Capable
  • [CORE-2032] – Snapshot incorrectly including clob/blob sizes on diff
  • [CORE-2051] – Not quoting VIEW params with spaces when snapshotting
  • [CORE-2054] – Add new “computed” column attribute to differentiate between an actual column name and a function as a column
  • [CORE-2063] – Fix for H2 autoincrement “start with” and “increment by” syntax
  • [CORE-2070] – dropAllForeignKeyConstraints does not work on Firebird databases
  • [CORE-2075] – generateChangelog generates bad definition for TIME type
  • [CORE-2080] – Liquibase “empty” change not present in XSD version 3.2
  • [CORE-2081] – PrimaryKeyExists precondition without tableName is broken
  • [CORE-2082] – Column snapshot on PostgreSQL does not include precision information for numeric data type
  • [CORE-2087] – Executing against Oracle doesn’t respect liquibaseSchemaName or liquibaseCatalogName
  • [CORE-2088] – outputDefaultSchema and outputDefaultCatalog command line parameters not respected
  • [CORE-2093] – Error: Property ‘relativeToChangelogFile’ not found on object type liquibase.change.core.LoadDataChange
  • [CORE-2094] – Liquibase.dropAll() should reset the lock service
  • [CORE-2095] – Invalid generated changeset for mysql bit with defaultValue 0

Liquibase 3.2.2 Released

Liquibase 3.2.2 has been released. It is a small bugfix release to fix a checksum regression from 3.1 -> 3.2 that was not fixed in 3.2.1.

Issues Resolved:

  • [CORE-1938] – defaultValueNumeric=”0″ or defaultValue=”0″ is translated to 0.0
  • [CORE-1950] – Checksum validation failed after Liquibase upgrade (3.1.1 -> 3.2.0)
  • [CORE-1959] – generateChangeLog without changeLogFile – better error message

As usual, it can be downloaded from the Liquibase download page and is available in the Maven repository as org.liquibase/liquibase-core.

 

Liquibase 3.2.1 Released

Liquibase 3.2.1 has been released. As usual, it can be downloaded from the Liquibase download page and is available in the Maven repository as org.liquibase/liquibase-core.

3.2.1 is purely a bugfix release. The only potential update issue could be a fix for incorrect checksums introduced in 3.2.0. If you used defaultValueNumeric, defaultValue or used createProcedure or sql tags you may have seen unexpectedly changed checksums with 3.2.0. With 3.2.1, those have been reverted back to the correct 3.1.x version. If you are updating from 3.1.x it will be a smoother update from 3.2.0. If you already updated to 3.2.1 and manually fixed your checksums in the databasechangelog table, they will need to be updated again.

Full change log:

  • [CORE-1844] – bulkFetch of foreign keys on Oracle is very slow
  • [CORE-1918] – Multiple comment blocks in changesets no longer works
  • [CORE-1920] – SpringLiqubase includeAll is not including files
  • [CORE-1922] – 3.2.0 Regression due to CORE-1721
  • [CORE-1923] – Error raised: Unknown Reason when doing tagging via command line
  • [CORE-1930] – Snapshot command returns no information on MSSQL
  • [CORE-1933] – [3.0.8 -> 3.2.0] pom.xml: 2Mb of superfluous dependencies
  • [CORE-1934] – file path in databasechangelog is absoulte since liquibase 3.2.0 when using includeAll inside a jar
  • [CORE-1936] – NullPointerException while diffing database against hibernate.cfg.xml
  • [CORE-1938] – defaultValueNumeric=”0″ or defaultValue=”0″ is translated to 0.0
  • [CORE-1940] – Maximum Oracle Sequence fails to be parsed as a BigInteger
  • [CORE-1944] – NullPointerException when tagging through Ant before applying changesets
  • [CORE-1947] – Liquibase dependency to jetty-servlet/velocity should be <optional>true</optional>
  • [CORE-1950] – Checksum validation failed after Liquibase upgrade (3.1.1 -> 3.2.0)
  • [CORE-1957] – Using VARCHAR2 column type fails for Hsqldb running in oracle syntax mode
  • [CORE-1960] – “Could not find implementation of liquibase.logging.Logger” starts appearing after migration from 3.1.1 to 3.2.0
  • [CORE-1970] – NullPointerException on rollback with <sqlFile> tag
  • [CORE-1746] – Support <sqlFile> parameters
  • [CORE-1951] – Regression on 3.2.0 : –diffTypes=data generates Unknown Reason Exception

UPDATE: There is still a couple checksum issues updating from 3.1.1 to 3.2.1. I’ll get those fixed up in a 3.2.2 release today or tomorrow.

Adding Liquibase on an Existing project

The Quick Start Guide works well for starting Liquibase on a new project because your empty changelog file matches your empty database. However, when you have an existing project with an existing database things are more complicated.

Unfortunately, there is no simple “this is how you do it” answer because there is so much variations in projects, processes and requirements. Liquibase provides many tools to help the process, but it is up to you to decide the best way to combine them for your particular situation.

When adding Liquibase to an existing project there are basically two approaches: “Make it look like you’ve always been using Liquibase” and “Just start using Liquibase”

Make it look like you’ve always been using Liquibase

The goal of this approach is to have a changelog file that matches the current state of your database. You can run this changeLog against a blank database and the final result will be indistinguishable from your existing databases–as if you used Liquibase from the beginning. This approach is usually the best long term, but it can be more work up front.

Create ChangeLog

Creating the changelog to match your database can be done automatically using the generateChangeLog command or be done manually. For any database larger than a few tables, the generateChangeLog command is usually a good idea but make sure you go through the generated changeSets to ensure they are correct. Liquibase does not always detect more complex structures like stored procedures or details like if an index is not clustered. Also, ensure data types are as you expected them.

Populate the DatabaseChangeLog table

Once you have your changeLog, you need a way to ensure that the pre-Liquibase changeSets are only ran on new, empty databases. The easiest way to do this is generally to use the changeLogSync or changeLogSyncSQL command to execute (or generate) the SQL that marks the starting changeSets as already ran without actually executing them.

As an alternative to the changeLogSync command, you can add contexts on the pre-Liquibase changeSets such as <changeSet ... context="legacy"> and when you run Liquibase on a new database you run with liquibase --contexts=legacy update and on an existing database you run with liquibase --contexts=non-legacy.

Finally, you can add <precondition onFail="MARK_RAN"> tags to the generated changeSets. For example, if you have a <createTable tableName="person"> changeSet, you would add <preconditions onFail="MARK_RAN"><not><tableExists tableName="person"/></not></preconditions> tag. Adding preconditions requires more changes to the changeLog file and introduces a performance penalty because Liquibase must check the database metadata for each changeSet the first run through, this approach is usually best used in isolated cases only.

What is the current state?

Often times a part of the reason to move to Liquibase is because your schemas have diverged over time, so an important question to answer is “If I’m making the changelog file match the current state, what is the current state?” Usually the best answer to that question is “the production database” but it can vary.

How divergent your schemas are will also affect which of the above techniques you use to populate the DatabaseChangeLog table, and it will often times make sense to use multiple approaches. For example, you may want to generate your base changeLogs from the production database and use changeLogSyncSQL to be able to mark them ran on everything from production down. Then you can add your non-released changeSets to the changeLog file with a precondition checking if it has already ran. That will allow Liquibase to automatically figure out the correct state for all your databases from development through production.

We are going to use Liquibase starting…..NOW!

Instead of building up a changeLog to match your existing database, you can instead just declare “from now on we are using Liquibase”. The advantage to this is that it much easier to set up because it is just a mandate. Usually this works best going from one version to the next because your databases are all in a reasonably consistent state and you simply start tracking database changes in your next version using Liquibase. Because Liquibase only looks at the DatabaseChangeLog table to determine what needs to run, it doesn’t care what else might be in your database and so it will leave all your existing tables alone and just run the new changeSets.

The biggest disadvantage to this approach is that you cannot bootstrap an empty database with Liquibase alone. A work-around is to take a pre-Liquibase snapshot using your database backup tool and use that as your database seed. Any time you need to create a new database, you first load in the seed and then run Liquibase update.

Depending on how much variation you have between your schemas, even with this approach you may need to rely on preconditions or a “mark changes ran” script in order to standardize and handle those variations.

People and Processes

Finally, remember that starting to use Liquibase–especially on an existing project–isn’t just about how you bootstrap your changeLog file. It is also a question of how you introduce Liquibase into your existing processes and culture.

For many companies and projects, everyone realizes the problems that need fixing and are on board with the advantages of change. For others, however, there can be entrenched interests and strong resistance similar to any other process change. Liquibase provides many tools and approaches that can be used to ease it into an existing process such as SQL outputSQL formatted changelogsdiffChangeLog and more that can be combined in ways that works best for your group.

If you know that introducing Liquibase is going to be complex, either from a technical or processes standpoint, it is usually best to introduce it slowly. Start with it on a new project as a trial run and once you have a good grasp of how it works and available options, apply it to other existing projects.

NOTE: This post has been incorporated into the documentation at http://www.liquibase.org/documentation/existing_project.html

Liquibase 3.2.0 Released

Liquibase 3.2.0 has been released. As usual, it can be downloaded from the Liquibase download page and is available in the Maven repository as org.liquibase/liquibase-core.

For most users, upgrading will be seamless but if you have written Liquibase extensions there have been some API changes. See http://www.liquibase.org/v3_2_upgrade.html for details.

Updated Extensions

The following extensions have been updated to work with 3.2.0 and/or include bugfixes:

Major new features and changes in 3.2.0 include:

And/Or/Not logic in context expressions

Context attributes can now include complex expressions such as “qa or (production and master)”. See the context documentation for more information

Improved JSON and YAML changelog parsing

The changelog parsing logic has been greatly refactored to provide full feature parity between the XML, JSON, and YAML parsers.

New Command: Snapshot

The command line interface supports a new “snapshot” command that will output a report of the objects Liquibase sees in a database. This is the model that would be fed into diff/diffChangeLog operations but doesn’t perform any comparison logic, is simply reports on what it sees.

Liquibase SDK

The Liquibase SDK will provide utilities and features not needed for standard Liquibase usage such as testing tools and extension writing help. The 3.2.0 release provides the first pieces of the SDK:

  • Offline Javadoc
  • A starter/example workspace
  • Ability to generate vagrant configurations for various databases
  • “Watch” command to provide a simple real-time view of the database schema

For more information on the SDK, see the SDK Documentation.

Improved Performance

Various improvements in memory usage and optimizations in database interactions

DatabaseChangeLog SQL available with Offline Database

New “outputLiquibaseSql” flag allows inclusion of DatabaseChangeLog table create/insert statements with updateSql command

And Much More:

  • [CORE-209] – Double Create Database Lock Table using updateSQL
  • [CORE-421] – NPE in MySqlDatabaseSnapshot during diff operation when “enum” or “timestamp” column was removed from table
  • [CORE-461] – Wrong datatypes for timestamps generated with generateChangeLog
  • [CORE-871] – Unable to use changeSetPath in rollback tag to refer to another change log file
  • [CORE-877] – Bug with column type “TIMESTAMP WITHOUT TIME ZONE”
  • [CORE-976] – GenerateChangeLog with data: java heap space error
  • [CORE-1097] – Liquibase adds a semicolon after a stored proc definition making the stored proc unusable
  • [CORE-1108] – Oracle : Unable to create complex primary key for loadUpdateData
  • [CORE-1284] – Memory leak in ExecutorService
  • [CORE-1563] – dropAll does not delete Sequences on Derby
  • [CORE-1572] – Regression: diff generates full schema instead of changes when using liquibase-hibernate4
  • [CORE-1578] – changeset checksum calculates differently on linux and windows
  • [CORE-1601] – updateSql generates SQL with unexpected linefeeds, breaks SQLPlus
  • [CORE-1642] – Special character issue after upgrade
  • [CORE-1643] – DB2: defaultSchemaName and changelogSchemaName are not respected
  • [CORE-1650] – dropAll doesn’t work on Informix
  • [CORE-1668] – defaultSchemaName ignored on createTable in H2
  • [CORE-1673] – Empty default value is not recorded for MySQL
  • [CORE-1676] – Colons in changeSet IDs no longer supported
  • [CORE-1688] – The ‘valuum’ extension seems not to be working under version 3.0.8 of liquibase
  • [CORE-1701] – Oracle: snapshot of default date/time values come back as to_date functions
  • [CORE-1714] – 2->3.1 migration error
  • [CORE-1715] – 2 -> 3.1 migration, 3.1 errors if there are single quotes in comments
  • [CORE-1718] – foreignKeyConstraintExists precondition fails with constraints on unique keys
  • [CORE-1721] – HsqlDatabase.escapeObjectName(…) ignores QUOTE_ALL_OBJECTS
  • [CORE-1727] – Drop default constraint syntax for DB2 not correct
  • [CORE-1728] – Only sequence name is captured by snapshot process and used in generateChangeLog
  • [CORE-1733] – Data in diffChangeLog coming through as one row per changeSet
  • [CORE-1734] – updateSQL is not including content from sqlFile (at least not when ran from Linux)
  • [CORE-1739] – Liquibase ignores Oracle varchar precision
  • [CORE-1743] – Snapshot VARCHAR(MAX) correctly on sqlserver
  • [CORE-1744] – Derby: UpdateSQL not including creating databasechangeloglock table if needed
  • [CORE-1748] – Maven Plugin does not use outputDefaultCatalog property
  • [CORE-1750] – liquibase-modify-column 3.0 broken with liquibase-core 3.1.1
  • [CORE-1752] – Oracle XML Datatype snapshot containing size
  • [CORE-1753] – HSQLDB 1.8 does not support catalogs
  • [CORE-1754] – Default value functions on Oracle snapshoted as “defaultValue”
  • [CORE-1755] – DefaultValueComputed is quoted for Char, Varchar, Clob, MediumInt and SmallInt types
  • [CORE-1756] – Oracle indexes with functions not returned correctly in generateChangeLog
  • [CORE-1765] – Failed to drop a VIEW pointing on not existing table from another schema
  • [CORE-1767] – Oracle snapshot not capturing all indexes
  • [CORE-1772] – Informix cannot drop constraints
  • [CORE-1774] – Autocommit not restored on close in SpringLiquibase
  • [CORE-1775] – Informix error when creating primary key
  • [CORE-1779] – User Defined Types Come back with Unnecessary Size specifier in diff
  • [CORE-1782] – dropALL fails when tables are referenced by other tables in another schema
  • [CORE-1784] – GenerateChangeLog with objects in multiple schemas returns objects from multiple schemas and empty createTable statements
  • [CORE-1788] – dropAll does not work in not default schemas using postgresql
  • [CORE-1794] – Drop index failing for sybase
  • [CORE-1797] – Autoincrement on type Serial8 fails
  • [CORE-1798] – Invalid type syntax in Informix for Int(10) and SMALLINT(5) and FRACTION(3)
  • [CORE-1799] – Attempted recreation of DATABASECHANGELOG in informix
  • [CORE-1817] – Foreign Key Constraints Built on Unique Constraints are not captured in generateChangeLog
  • [CORE-1818] – SQL Server “smalldatetime” converted to “datetime” on update
  • [CORE-1824] – GUID Default values not quoted correctly in MSSQL
  • [CORE-1828] – sqlFile should fail when file does not exists
  • [CORE-1831] – CREATE/DROP INDEX issue: Sybase ASE 15.7.0
  • [CORE-1834] – generateChangeLog creates DOUBLE(22) instead of double in MySql
  • [CORE-1840] – Liquibase fails when run on a computer that can’t connect to the internet
  • [CORE-1843] – includeAll does not alphabetize classpath loaded files
  • [CORE-1853] – Liquibase generates invalid SQL for ‘character varying’ type
  • [CORE-1856] – Ability for Change classes to verify update and rollback succeeded
  • [CORE-1859] – DataTypeFactory doesn’t take database into account
  • [CORE-1861] – MSSQLDatabase.isCaseSensitive() provides a wrong information
  • [CORE-1878] – Maven plugin behaviour different than command line with the same parameters
  • [CORE-1881] – Case sensitivity issue with tableExists precondition
  • [CORE-1893] – type=”DATETIME” in changelog is converted to TIMESTAMP in MySql
  • [CORE-1899] – Non-existing referenced sql files doesn’t report error
  • [CORE-1901] – DropPrimaryKey – Fails in Postgres
  • [CORE-1906] – Diff between objects in different named default schemas doesn’t see them as the same object
  • [CORE-1912] – Unit tests fail due to dependency on system time zone
  • [CORE-1116] – Allow for both “AND” and “OR” specifications of which contexts to run
  • [CORE-1422] – Support changeset comments in formatted SQL
  • [CORE-1536] – Support specifying the referenceDefaultSchemaName for diff operations
  • [CORE-1635] – Generated changelog missing unique constraints should be output before foreign keys
  • [CORE-1682] – More machine-independent handling of filesystem-located resources
  • [CORE-1695] – Better handling of Postgres timestamp with/without time zone
  • [CORE-1706] – Excessive reading from databasechangelog table
  • [CORE-1726] – Added offline parameter to generate insert databasechangelog statements
  • [CORE-1758] – Add outputDefaultSchema flags to ant
  • [CORE-1776] – System-independent checksums
  • [CORE-1823] – Search for liquibase.properties in classpath in additional to local filesystem
  • [CORE-1874] – Improve XML and other parsers for better extensiblity
  • [CORE-1905] – Yaml parser supports .yml extension as well as .yaml
  • [CORE-1686] – Add Support for Pivotal’s SqlFire database to Liquibase
  • [CORE-1742] – Standardized system for managing configuration options in core and extensions
  • [CORE-1751] – More detailed API for returning changeSet statuses
  • [CORE-1783] – Command line option: snapshot
  • [CORE-1815] – Liquibase SDK command to easily watch database
  • [CORE-1821] – Snapshot listener interface

Slides From Percona Live Talk

I posted the slides from my Percona Live talk at https://www.percona.com/live/mysql-conference-2014/sessions/liquibase-source-control-your-schema.

I modified them from the actual talk slides to also include my speaking notes so they will be more helpful for those who were not able to make it to the talk or if you wish you took better notes.

I’m hoping to post a recorded version of the talk in the next few weeks as well.