About a month ago, I completed my second Liquibase project, one of a portfolio of four projects I managed for the client CTO, and an extension to the project first mentioned in my third post in this series. The first Liquibase project that I had completed for another client, although sizeable, paled in comparison to this project.
Now that I have two such projects under my belt, I thought the development community might like to hear about some of the technical lessons learned between these two projects, which were similar in some aspects, but each with its own set of challenges.
While the first Liquibase project involved 6 database instances of DB2 9.5, the second Liquibase project involved 2 database instances of Oracle Database 11g R2. Additional constraints associated with the second project are what complicated matters.
I created 30 schemas associated with 8 application environments across these 2 database instances due in part to Oracle licensing costs, whereas the 6 database instances in the earlier project were each only associated with 1 schema each.
Because database schema names, which are actually users in Oracle, need to be obviously unique within a database instance, the hard-coding (something which I typically frown upon as a consultant architect, although in this case it made sense to do so) to which I was accustomed in the first project was no longer possible.
Initially, it had been requested that I use Apache Ant for the Oracle project, and because of this I had to set up an elaborate series of build scripts that corresponded with a lot of in-line properties due to all the different schema names. Later, I was fortunately able to move to Maven, and automated the entire setup with the profile and filter features that Maven provides.
Although it took time to migrate from Apache Ant to Apache Maven due to experimentation and thorough testing to make sure nothing had broken, I was able to successfully redirect the development team using this project with enough time left prior to my roll-off.
During my presentation of this technology while on the architecture board, I had been asked quite a few questions by the various client sites that had dialed in for my talk. Some questions were centered around Liquibase being an open source rather than commercial product, and one in particular asked whether I had run across any issues with the product.
At the time, I had not run across any issues with the product, but during my Mavenization of the Oracle project I ended up encountering 2 issues during my second round of testing, one with Liquibase 2.0 RC 5 (and presumably Liquibase 2.0.1), and one with Liquibase 2.0.1:
- Reverse engineering an Oracle 11g R2 database schema using Liquibase 2.0 RC 5 does not recognize column defaults of SYSDATE.
Because reverse engineering is typically a one-time process, and is obviously only a concern when a database is not initially created via Liquibase, the likelihood that this will be an issue for readers is probably low.
However, this issue needs to be kept in mind for those deciding to incorporate database change management using Liquibase sometime after initial creation. In my case, awareness that this was an issue did not arise until I tested the Java web application against a new database created with the Oracle project, following a data migration from the corresponding environment database I had also performed.
The simple solution for this issue was to add a series of changeSets to the applicable changeLog, each corresponding to a column in the legacy database which had such a default value defined. For example, adding a default value of SYSDATE to table column USER.CREATED_DATE can be performed as follows:
<addDefaultValue columnName="CREATED_DATE" tableName="USER"
defaultValueDate="SYSDATE"/>
This workaround works perfectly, but I needed to fill in the development team why I needed to retroactively insert these changeSets to the project trunk and previous snapshot, especially because it takes some developers getting used to when merging Java code is often done, but in many cases not possible in situations like the one at hand, because Liquibase acts sequentially.
- Creating or updating an Oracle 11g R2 stored procedure or package with Liquibase 2.0.1 is unreliable.
Bundled along with the Oracle project migration from Ant to Maven, I upgraded the JDBC driver to the most recent version, and also upgraded to the latest version of Liquibase, from 2.0 RC 5 to 2.0.1, hoping to move to a "stable" release of the product. While there were no issues with the JDBC driver upgrade, after several arounds of thorough testing there appeared to be no remediation available for the resultant missing segments of PL/SQL, and so I unfortunately settled with Liquibase 2.0 RC 5. This bug was not apparent until I tested the Java web application, because the PL/SQL compiled successfully. It was only after diffs were performed between the code in Subversion and the code in Oracle when the issue was found.
Liquibase in the Enterprise: Part 1
