Often relevant when working with Oracle SOA Suite; database change management. There are several issues with database scripts which are rolled out over different environments in releases;
Take the following example;
Development environment has version 3 of the database software installed. The test environment has version 2 installed and the acceptance test environment has version one installed. The software hasn't reached production yet. In version 1, a table is created. In version 2 data is inserted in the table and in version 3, the data in the table is updated.
When releases are created and deployment is not automated (or automated using custom code), a DBA would have to install 1 followed by 2, followed by 3 in the production environment if the environment is to be updated to version 3. Also when installation is manual, the DBA has to have an SQL client installed (or run the scripts from the DB server) and most likely the scripts in release 1,2 and 3 have different names and installation instructions such as prerequisite checks. Some scripts such as the release 2 script which inserts data may only be executed once on an environment. A custom registration has to be kept in order to keep track of changes in an environment. Not only is this expensive on DBA time, but it's also prone to errors. And what if the installation went wrong? Then often the rollback is restoring a backup or clone from before the installation started. Creating and restoring these can be cumbersome, especially for relatively small changes.
The open source product Liquibase (
http://www.liquibase.org) provides a solution for automating database change management. I've tried this product only for a couple of days and this post describes some first experiences with it. I must admit I haven't used it yet at a customer but I might in the near future and will certainly suggest it as an option when the question arises.
Because my current customer shuns fancy things like automated builds, I've used batch files and command-lines in the examples below.
Liquibase
What is Liquibase?
Liquibase is a standalone JAR file which can executed using a commandline or from for example Maven using a plugin. A property file can be used to specify database connection settings. Below an example of such a property file;
driver: oracle.jdbc.OracleDriver
classpath: ojdbc6.jar;liquibase-oracle-1.2.0.jar
url: jdbc:oracle:thin:@localhost:1521:XE
username: testuser
password: testuser
contexts: dev
It is also possible to specify these properties on the command-line;
liquibase --driver=oracle.jdbc.OracleDriver \
--classpath=ojdbc6.jar;liquibase-oracle-1.2.0.jar \
--changeLogFile=changelogs\testchangelog.xml \
--url="jdbc:oracle:thin:@localhost:1521:XE" \
--username=testuser \
--password=testuser \
--contexts="dev"
update
Database changes are described using XML files called ChangeLogs. An example is shown below;
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ora="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
<changeSet id="1" author="ms">
<createTable tableName="params">
<column name="paramname" type="varchar(50)">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="paramvalue" type="varchar(50)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="ms" id="2" context="dev">
<insert tableName="params">
<column name="paramname" value="environment"/>
<column name="paramvalue" value="dev"/>
</insert>
<rollback>
<delete tableName="params">
<where>paramname='environment'</where>
</delete>
</rollback>
</changeSet>
<changeSet author="ms" id="3" context="tst">
<insert tableName="params">
<column name="paramname" value="environment"/>
<column name="paramvalue" value="tst"/>
</insert>
<rollback>
<delete tableName="params">
<where>paramname='environment'</where>
</delete>
</rollback>
</changeSet>
</databaseChangeLog>
When a changelog is executed against an environment, a table entry is kept which says the changelog has been executed. When the same changelog is executed again, it refrains from re-execution thus avoiding problems with for example insert statements which are executed repeatedly. Most actions described in the changelogs have an automated rollback action. Some such as insert or update statements don't however. They need to be specified explicitly.
To apply a ChangeLogfile the following can be used (if connection properties are specified in liquibase.properties);
liquibase --changeLogFile=changelogs\testchangelog.xml update
Oracle specific tips
There is a specific tutorial for using Oracle in combination with Liquibase;
http://www.liquibase.org/tutorial-using-oracle. In my opinion you should consider the parts from this tutorial you want to implement. Liquibase can make database change management easier but you can also make the process complicated. In my opinion you should avoid that a small group or even only one developer knows how to deal with the tool and the process. To achieve this, I'd suggest to keep it simple and the manual scripting / steps for releasing to a minimum.
Oracle extensions
The property file in the example above includes a classpath referring to the Oracle extension which can be used to provide support for some Oracle specific database features;
https://liquibase.jira.com/wiki/display/CONTRIB/Oracle+Extensions
These features include amongst others XML tags to enable and disable triggers, to truncate tables, add check constraints, etc.
JDBC drivers
Also you should download the Oracle JDBC driver from for example;
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html
How to deal with packages, grants and synonyms?
Packages, grants and synonyms are more or less Oracle specific and not supported directly using XML tags in ChangeLog files. You can however use the sql tag such as in the example below;
<changeSet author="ms" id="6" context="tst">
<sql>
grant select on params to public
</sql>
<rollback>
<sql>
revoke select on params from public
</sql>
</rollback>
</changeSet>
Developer tips
Test your installation using apply, rollback, apply scenario in your own schema to avoid hindering other developers. This can be done with the updateTestingRollback option (use instead of update). You can change changelogfiles but a checksum is kept in the database and rerunning the changelog will cause errors if the checksum mismatches. If you want the change to be reapplied and avoid checksum failures, you can use the
runOnChange attribute. Also avoid making changes not using Liquibase because that can cause conflicts.
How do you deal with OTAP?
Liquibase has the option to specify contexts as part of the property file. You can see this in the example property file above. In the definition of the ChangeSet a context can be specified. If no context is specified, the changelog is always executed. In the above example, if the environment is dev, the dev environment entry is added to the params table. This will not happen if the context is tst. Then the entry tst will be inserted. If you want to change an environment from dev to tst, you should first rollback 'dev' and then apply 'tst'. Converting dev to tst requires more code; rollback is easier. You can use a precondition to check if an entry is already present and update instead, but if you're loading large datasets, this is not workable.
How to deal with multiple schema's/users?
When using the Oracle database, a database user is also called a schema. Often when the datamodel is implemented, different users/schema's are used to implement different 'modules' of the application or different 'applications'. These schema's are strung together with grants, views, synonyms or even databaselinks when the users are in physically different databases. For the person installing database objects into different schema's, this can be cumbersome. Liquibase can be used with a little bit of scripting to help automate this process.
You can specify commandline which property file is used and which ChangeSets are applied. This can be done with the option defaultsFile like;
liquibase --changeLogFile changelogs\testchangelog.xml --defaultsFile="liq.props" update
You can use this to use a different property file per environment (for example specifying different databases).
builddir
- liquibase_dev.properties
- liquibase_tst.properties
- liquibase_acc.properties
- liquibase_prd.properties
--schema1
---master.xml
---testchangelog.xml
Usually passwords of database users are guarded by DBA-ers and need to be asked during the installation. When using the above directory structure, you can loop over the schema's, ask for the password for the specific schema and use the connection settings specific for the purpose of the environment.
How to deal with errors during the installation
A nice suggestion was done by Jelle Hoekstra. You can create a tag during the start of the release. Next you can execute your changes. You can check the ERRORLEVEL environment variable to determine if a change went successful or has failed. On failure, you can call the rollback action to the previously created tag. This is implemented in the below script.
Example script to run Liquibase
The below example script shows some of the suggestions earlier in this post.
@echo off
set current_dir=%CD%
set liquibase_directory=%current_dir%
set build_directory=%current_dir%\build
set DATESTAMP=%DATE:~3,2%_%DATE:~6,2%_%DATE:~9,4%
set TIMESTAMP=%TIME:~0,2%_%TIME:~3,2%_%TIME:~6,2%
set DATEANDTIME=%DATESTAMP%_%TIMESTAMP%
set SCHEMA=%2%
set PASSWORD=%3%
:BEGIN
IF "%1%" == "" GOTO USAGE
IF "%2%" == "" GOTO USAGE
IF "%3%" == "" GOTO USAGE
SET ENV=%1%
IF "%ENV%" == "" GOTO USAGE
SET filename=%build_directory%\liquibase_%1.properties
echo Property file : %filename% 1> install.log
java -jar %liquibase_directory%/liquibase.jar --defaultsFile=%filename% --changeLogFile=%build_directory%/%SCHEMA%/master.xml --contexts=%ENV% --username=%SCHEMA% --password=%PASSWORD% tag %DATEANDTIME% -Dchangelogpath=%build_directory%/%SCHEMA% 1>>install.log 2>>&1
IF NOT %ERRORLEVEL% == 0 GOTO ERROR
java -jar %liquibase_directory%/liquibase.jar --defaultsFile=%filename% --changeLogFile=%build_directory%/%SCHEMA%/master.xml --contexts=%ENV% --username=%SCHEMA% --password=%PASSWORD% --logLevel=info update -Dchangelogpath=%build_directory%/%SCHEMA% 1>>install.log 2>>&1
IF NOT %ERRORLEVEL% == 0 GOTO ROLLBACK
java -jar %liquibase_directory%/liquibase.jar --defaultsFile=%filename% --changeLogFile=%build_directory%/%SCHEMA%/master.xml --contexts=%ENV% --username=%SCHEMA% --password=%PASSWORD% dbDoc %build_directory%/docs/%SCHEMA% -Dchangelogpath=%build_directory%/%SCHEMA% 1>>install.log 2>>&1
IF NOT %ERRORLEVEL% == 0 GOTO ROLLBACK
ECHO Script executed on %ENV%:SCHEMA %SCHEMA%
GOTO END
:ROLLBACK
java -jar %liquibase_directory%/liquibase.jar --defaultsFile=%filename% --changeLogFile=%build_directory%/%SCHEMA%/master.xml --username=%SCHEMA% --password=%2 rollback %DATEANDTIME% -Dchangelogpath=%build_directory%/%SCHEMA% 1>>install.log 2>>&1
GOTO ERROR
:ERROR
ECHO Errors found during execution of script on %ENV%:SCHEMA %SCHEMA%
ECHO See install.log
GOTO END
:USAGE
ECHO Error during execution of script on %ENV%:SCHEMA %SCHEMA%
ECHO Incorrect parameters
ECHO USAGE : install environment schema password
ECHO environment = dev,tst,acc,prd
ECHO schema = Schema/user for which an update needs to be performed
ECHO password = Schema/user password
GOTO END
:END
You can start the script like;
runlb.bat dev testuser testuser
This executes the builddir\testuser\master.xml changelog file and uses the builddir\liquibase_dev.properties file for it's database connection properties. It executes against user testuser with password testuser. In the master.xml file there is a reference to the changelogdir so it can include other changelogfiles.
In this case, the master.xml script looks as followed;
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ora="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
<include file="${changelogpath}/testchangelog.xml"/>
</databaseChangeLog>
The liquibase-dev.properties looks as follows;
driver: oracle.jdbc.OracleDriver
classpath: ojdbc6.jar;liquibase-oracle-1.2.0.jar
url: jdbc:oracle:thin:@localhost:1521:XE
Notice in the above script the following;
- differentiation between Liquibase location and Build location (they can be taken apart so Liquibase doesn't have to be part of the release itself)
- usage of ERRORLEVEL to perform rollback if needed
- usage of a property to determine the correct changelogpath in the master.xml file
- usage of the contexts parameter
- usage of a log file for the output
Conclusion
Liquibase is very flexible and provides enough options to be able to take care of database change management (with the help of some scripts and version control of course). It provides various integration options but can also be run stand-alone. Also if implemented well, it can reduce the amount of time DBA needs to spend on installations and reduces the amount of human errors during the installation process.
You need to think well about how you are going to implement it because of this flexibility. There are some best practices online which are a good idea to take into account;
Liquibase can do a lot more then what I've mentioned in this post such as generating an initial changelogset from a database or generating diffs. Also how for example version control can be used efficiently together with Liquibase is an important topic I haven't touched yet. These are however customer process specific and should be determined by the person implementing Liquibase at a customer.
