Testing Dockerized SQL Databases

One of the big advantages of using Docker for testing is that you don't need to install the required dependencies of code under tests in all machines where you are going to run these tests. This is really helpful for external services such as database servers, mail services, JMS queues, ... Also one of the big advantages of this approach is that the tests are going to use the same version used in production.

So for persistence tests using Docker is a really good approach to follow. But as usually this approach comes with some drawbacks. 

The first one is that obviously you need to have Docker installed in all machines that needs to run the tests, not a big problem but something to take into consideration, as well as Docker inside Docker problem.

The second one is that you need to automate somehow the starting and stopping of the container.

The third one is that Docker containers are ephemeral. This means that when you start the container, in this case a container with a SQL server, then you need to migrate the database schema there.

The fourth one, and this is not only related to Docker, is that you need to maintain test method execution isolated from test to test execution, by providing known data before execution and cleaning data after the execution so other test finds the environment clean.

First and second problems are fixed with Arquillian Cube (http://arquillian.org/arquillian-cube/). It manages lifecycle of containers by starting and stopping them automatically before and after test class execution. Also it detects when you are running into a DinD situation and configures started containers accordantly.

Arquillian Cube offers three different ways to define container(s).

• Defining a docker-compose file.
• Defining a Container Object.
• Using Container Object DSL.

For this post, Container Object DSL approach is the one used. To define a container to be started before executing tests and stopped after you only need to write next piece of code.

In this case a JUnit Rule is used to define which image should be used in the test (redis:3.2.6) and add as binding port the Redis port (6379).

The third one can be fixed using Flyway. It is an open-source database migration tool for SQL databases that allows you to automate the creation of database schemas.

Flyway is useful here since you can start the Docker container and then apply all migrations to the empty database using Flyway.

The fourth problem can be fixed by using tools like DBUnit. iI puts your database into a known state between test runs by populating database with known data, and cleaning it after the test execution.

Arquillian integrates with both of these tools (Flyway and DBUnit)  among others with its extension called Arquillian Persistence Extension (aka APE),

An example on how to use APE with DBUnit is shown in next snippet:

You can use Arquillian runner as shown in dbunit-ftest-example or as shown in previous snippet using a JUnit Rule. Choosing one or other depends on your test requirements.

So how everything fits together in Arquillian so you can boot up a Docker container with a SQL database, such as PostgreSQL, before test class execution, then migrate SQL schema and populate it with data, execute the test method, then clean the whole database so next test method finds a clean database and finally after test class execution, the Docker container is destroyed?

Let's see it in the next example:

Test is not so much complicated and it is pretty  much self explanatory of what it is doing in each step . You are creating the Docker container using Arquillian Cube DSL, and also you are configuring the populators by just using Arquillian APE DSL.

So thanks of Arquillian Cube and Arquillian APE  you can make your test totally isolated from your runtime, it will be executed always agains the same PostgreSQL database version and each test method execution will be isolated.

You can see full code at https://github.com/arquillian/arquillian-extension-persistence/tree/2.0.0/arquillian-ape-sql/standalone/dbunit-flyway-ftest

We keep learning,
Alex

Ya no me importa nada, Ni el día ni la hora, Si lo he perdido todo, Me has dejado en las sombras (Súbeme la Radio - Enrique Iglésias)

Music: https://www.youtube.com/watch?v=9sg-A-eS6Ig

Testing Polyglot Persistence Done Right [slides]

This week I was at Krakow in the amazing conference called GeeCon. My talk was "Testing Polyglot Persistence Done Right" and it was cospoken with my friend Bartosz Majsak.

The abstract was:

"Data storage is one of the most crucial parts of any applications, and we use many different tools and tricks to keep it in a good shape. We frequently use both old school relational systems with new approaches commonly known as NoSQL. We write sophisticated queries and use optimization techniques to give our end users the greatest possible experience.

So why is persistence very often skipped in the testing efforts? Is it really that complex and painful to setup? During this talk we will have a closer look at Arquillian Persistence Extension together with NoSQLUnit. These tools remove that burden and boilerplate to make you a happy and productive programmer again! Join this session and see for yourself that writing tests for your data storage logic is as easy as writing normal unit tests!"

And here you can see the slides (http://www.slideshare.net/asotobu/testing-polyglot-persistence-done-right). If you have any question don't hesitate to approach us.

Testing Polyglot Persistence Done Right from Alex Soto

We keep learning,
Alex.

En los mapas me pierdo. Por sus hojas navego. Ahora sopla el viento, cuando el mar quedó lejos hace tiempo. (Pájaros de Barro - Manolo García)

Music: https://www.youtube.com/watch?v=9zdEXRKJSNY

Nasha nasha krovatka delala shik - shik, Ya tvo pianino , a ty moi nastroishchik, My tak letali chto ne zametili tvoyu matʹ, Ahaa..I ona skazala chto ya prosto blaz (Mama Lyuba - Serebro))

When we develop any application, after we finish it or when we end up any of its module, we start optimization process. Most applications contain database access, and if you are using an ORM, probably you will use hibernate. Optimizing hibernate persistence layer, requires to be prepared to read, understand and evaluate sql.

If we take an overview on hibernate configuration, two properties, hibernate.format_sql and hibernate.use_sql_comments, should be enabled to print performed sql code through console.

This is a good start but it seems that we need more information to make an accurate diagnosis of performance like connection events, returned data on queries, or parameters binding (hibernate shows parameters values with question mark ?) . Hence, we need another way to inspect generated sql. Log4jdbc is a jdbc driver that can log sql/jdbc calls. In fact log4jdbc is an implementation of proxy pattern which will automatically load popular jdbc drivers (Oracle, Derby, MySql, PostgreSql, H2, Hsqldb, ...), intercept calls, log information, and then send data to "spied" driver.

In log4jdbc, there are 5 loggers that can be used depending on data to monitor:

• jdbc.sqlonly: logs executed sql with binding arguments replaced with bound data.
• jdbc.sqltiming: logs how long a sql took to execute.
• jdbc.audit: logs all jdbc calls except for ResultSets.
• jdbc.resultset: same as jdbc.audit plus ResultsSets.
• jdbc.connection: logs open and close connection events.

In this post we are going to see how to configure log4jdbc-remix, a fork of log4jdbc, which apart from inheriting log4jdbc capabilities, also let us:

• jdbc.resultsettable: log results set in table format.
• configure it as datasource.
• available in maven repository (log4jdbc is not present on maven repositories).

For this example we are going to use the project created by JPA Spring Template which contains two entities Order and Item associated with one-to-many relationship, and one test that executes some database operations.

First thing to do is add log4jdb-remix and slf4j-log4j12 dependencies to project:

Next thing to do is configure active loggers.  Depending on the data we are interested to monitor, we activate the required loggers. As an example let's configure log4j.xml so result set is printed in table format and also time taken to execute each query is shown.

After configuring loggers, run test and inspect the output.


Output is printed in a fashion format, queries contains bind parameters (not a question mark (?)), and process time is also informed.

Notice that logging more or less information is simply a matter of configuring a log. Moreover depending on log level, more or less information will be provided in each case. If logger is configured in DEBUG class name and line number (if available) at which the sql was executed will be included. In INFO will include only sql, and finally ERROR which show stacktraces if any SQLException occurs.

Optimizing hibernate applications can imply touching many parts of an application (JVM configuration, database engine, network, …) but one very important aspect to take care is the number of queries that are sent to RDBMS (for example N+1 problem), and the amount of data that is retrieved from database (Projection problem), and log4jdbc-remix perfectly fits to help in this purpose.

As final note, log4jdbc(-remix) is a jdbc logger, so it is not necessary to use only in hibernate applications, can be used with any framework that uses a datasource.

I wish this library would help you.

Keep Learning,
Alex

Download Code
Music: http://www.youtube.com/watch?v=h9HRHOXfRBI

Moi je pense à l'enfant, Entouré de soldats, Moi je pense à l'enfant, Qui demande pourquoi (Non Non Rien N'a Changé - Les Poppys)

After 8 years developing server and embedded applications using Hibernate as ORM, squeezing my brain seeking solutions to improve Hibernate performance, reading blogs and attending conferences, I decided to share this knowledge acquired during these years with you.

This is the first post of many more posts to come:

Last year I went to Devoxx as speaker but also I attended Patrycja Wegrzynowicz conference about Hibernate Anti-Patterns. In that presentation Patrycja shows us an anti-pattern that shocks me because it proved to expect the unexpected.

We are going to see the effect it has when Hibernate detects a dirty collection and should re-create it.

Let's start with the model we are going to use, only two classes related with one-to-many association:

In previous classes, we should pay attention in three important points:

• we are annotating at property level instead of field level.
• @OneToMany and @ManyToOne uses default options (apart from cascade definition)
• officers getter on Starship class returns an immutable list. 

To test model configuration, we are going to create a test which creates and persists one Starship and seven Officers, and in different Transaction and EntityManager finds created Starship.

Now that we have created this test, we can run it and we are going to observe Hibernate console output.

See the number of queries executed during first commit (persisting objects) and during commit of second transaction (finding a Starship). In total and ignoring sequence generator, we can count 22 inserts, 2 selects and 1 delete, not bad when we are only creating 8 objects and 1 find by primary key.

At this point let's examine why these SQL queries are executed:

First eight inserts are unavoidable; they are required by inserting data into database.

Next seven inserts are required because we have annotated getOfficers property without mappedBy attribute. If we look closely at Hibernate documentation, it points us that “Without describing any physical mapping, a unidirectional one to many with join table is used.”

Next group of queries are even stranger, the first select statement is to find Starship by id, but what are these deletes and inserts of data that we have already created?

During commit Hibernate validates whether collection properties are dirty by comparing object references. When a collection is marked as dirty, Hibernate needs to re-create whole collection, even containing the same objects. In our case when we are getting officers we are returning a different collection instance, concretely an unmodifiable list, so Hibernate considers officers collection as dirty.

Because a join table is used, Starship_Officer table should be re-created, deleting previous inserted tuples and inserting the new ones (although they have the same values).

Let's try to fix this problem. We start by mapping a bidirectional one-to-many association, with many-to-one side as owning side.

And now we rerun the same test again and we inspect the output again.

Although we have reduced the number of SQL statements, from 25 to 10, we still have an unnecessary query, the ones just in commit section of second transaction. Why if officers are lazy by default (JPA specification), and we are not getting officers in transaction, Hibernate executes a select on Officers table?  By the same reason as previously configuration, returned collection has different Java identifier, so Hibernate marks it as newly instantiated collection, but now obviously join table operations are no longer required. We have reduced the number of queries but we still have a performance problem. It is likely that we'll need some other solution, and the solution is not the most obvious one, we are not going to return collection objects returned by Hibernate, we might expand on this later, but we are going to change annotations location.

What we are going to do is to change mapping location from property approach to use field mapping. Simply we are going to move all annotations to class attributes rather than on getters.

And finally we are going to run the test again, and see what's happen:

Why using property mapping Hibernate runs queries during commit and using field mapping are not executed? When a Transaction is committed, Hibernate execute a flush to  synchronize the underlying persistent store with persistable state held in memory. When property mapping is used, Hibernate calls getter/setter methods to synchronize data, and in case of getOfficers method, it returns a dirty collection (because of unmodifiableList call). On the other side when we are using field mapping, Hibernate gets directly the field, so collection is not considered dirty and no re-creation is required.

But we have not finished yet, I suppose you are wondering why we have not removed Collections.unmodifiableList from getter, returning Hibernate collection? Yes I agree with you that we finished quickly, and change would look like @OneToMany(cascade={CascadeType.ALL}) public List<Officer> getOfficers() {officers;} but returning original collection ends up with an encapsulation problem, in fact we are broken encapsulation!. We could add to mutable list anything we like; we could apply uncontrolled changes to the internal state of an object.

Using an  unmodifiableList is an approach to use to avoid breaking encapsulation, but of course we could have used different accessors for public access and hibernate access, and not calling  Collections.unmodifiableList method.

Considering what we have seen today, I suggest you to use always field annotations instead of property mapping, we are going to save from a plenty of surprises.

Hope you have found this post useful.

Screencast of example shown here:



Download code
Music: http://www.youtube.com/watch?v=H14VIsnr6aA

Soy El Capitán De La Nave Tengo El Control, Llamando A La Tierra Esperando Contestación, Soy Un Cowboy Del Espacio Azul Eléctrico (Llamando A La Tierra - M.Clan)

Groovy is an object-oriented programming language for the Java platform and can be used as a scripting language. Most of us but instead of using Groovy alone, we use Grails (web framework based on Groovy) for developing web applications.

But Groovy can be used standalone for developing your internal tools. Let me explain why Groovy scripts have simplified our development of tools for generating data for integration tests.

In my company we create clinical instruments. These instruments are so expensive, and big enough to say that they are not portable. For these reasons each instrument has an emulator, so integration tests are run without having physically any instrument.

Our emulator has an XML file where all required resources for running tests are configured. In summary each file contains a list of blood barcodes, and reagent barcodes. In our case barcodes have a meaning (kind of resource, expiry date, checksum, ...).

So one can think about creating a standard configuration file that is used in integration tests. It is a good idea if you don't know that expiry date is expressed in months. So one day without knowing exactly why your tests begin to fail. The answer is obvious, resources have become expired. We need a (script ?) that updates this XML file so when a resource is becoming expired, its month field is changed.

Moreover not all resources should be updated. Some tests imply working with expired resources. For this reason some barcodes have special format that suggests that update should not be applied.

Because project is developed in Java, one can think about creating a small Java class that do all these work. But wait and think if this class would be small or not, we need a parser (DocumentBuilder), a method that walks all nodes and see if resource is expired or not (NodeList, Element.getAttribute(), ...), and finally writing modifications to file. Furthermore, some barcodes contains special charachtrs that should be detected using a regular expression (Pattern, Matcher) for avoiding its update. Although this class is not difficult, it is far away from a small class with few lines.

But how about polyglot programming? Groovy can be a choice. Let's see how Groovy deals with XML and Regular Expressions.

Imagine next XML file:


Groovy and XML

Meanwhile in Java you create a DocumentBuilderFactory that returns a DocumentBuilder, and then call parse method, in Groovy is as simple as:

and root variable points to root element of XML file.

And now imagine that you want to update all blood barcodes of given holder. With Java you will use an iterator over NodeList or using an XPath expression. See the simplicity with Groovy.


See that with Groovy, nodes are explored as object attributes, and finally we call findAll() method that returns a list of sample nodes belonging to SampleHolder11 tag. For returning an attribute value is as easy as adding @ character before attribute name, in our case barcode.

And for writing an XML is as easy as:


In previous example, output is written to console.

Groovy and Regular Expressions

Remember that some barcodes have special format. In our case if any barcode starts with A, B, C or D, should not be modified. A clean, reusable and maintainable solution is using regular expressions to check if one barcode matches or not special format. In Java the process is not banal, you have to create a Pattern object with a regular expression, a Matcher and use find() method to see if pattern is found on entry or not.

But how we can determine if one string matches a regular expression in Groovy ? ==~ operator does the work for us. Tell me what you think about this expression:


Elementary. You will agree with me that Groovy approach is simpler than Java one.  For specifying a pattern you only have to use ~ character plus a slash (/) then a regular expression and finally a slash to delimit. But Groovy also supports =~ (create a Matcher) and ==~ (return boolean, whether String matches the pattern).

I think that creating a script that reads/parses/writes XML file is so fast and easy, not much classes are involved compared to Java. And not worth making the comparison with regular expression approach, in Groovy is the simplest way one may think.



After the success of previous Groovy script, we decided to create another tool (Groovy script) for manipulating database.

System registers into database every incidence that has occurred to an execution. Some incidences are easy to reproduce with emulator, but others not. In integration tests there is no problem, because a defined-filled database is used, but acceptance tests contains no data and is generated with the execution of tests. But because there are some incidences that are hard to reproduce in emulator, a Groovy script to insert incidences are created.

Groovy and SQL

As Java, Groovy can also access to databases and as you can suppose in a simple way. No Connection object, no PreparedStatement, no ResultSet, ....

Let's see an example of searching data and using them for creating a new registry. Imagine that we have a table called Execution and another one called Incidence, and one-to-many relationship.


Simple, yes? With one line a connection to database is established.

Executing SELECT query and iterate over results are also easy. Using eachRow method, all results are iterated. No ResultSet object is required anymore. Parameters values are passed between brackets ([]), and as XML each row is accessed using Closure. In previous example each row is mapped to execution variable. Moreover see how easy is read a value of each tuple. As in XML you access the value as a class attribute, no more getters of ResultSet methods, in example execution.dboid is used to refer to dboid field.

Finally execute method is used to update database.



Now that I have shown you some of nice features that Groovy offers us, I will explain you how we execute these tools.

GMaven

We use Jenkins with Maven as Continuos Integration System. Before Jenkins starts to execute Integration Tests, Groovy scripts are executed so emulator is configured propertly. The iteresting part of this step is how pom is configured for executing Groovy scripts.

Exists a plugin called gmaven-plugin. This plugin runs Groovy scripts depending on phase and goal.


No problem configuring gmaven plugin, the most important part is where you specify which script should be executed.

As final notes I want to say that I am a huge fan of Java and my intention is not criticize it, but I think that there are some problems that are best suited using other languages rather than Java. My advice is  learning as many kind of languages as you can (Scala, Groovy, ...) so as programmer you can choose the best solution to a given problem.

I wish you find this post useful.

Music: http://www.youtube.com/watch?v=9u54Xs22_SI

Come On Let's Twist Again Like We Did Last Summer Yea, Let's Twist Again Like We Did Last Year (Chubby Checker - Let's Twist Again)

JPA 2 Criteria API allows criteria queries to be constructed in a strongly-typed manner, using meta-model objects to provide type safety. This is a useful feature because when a change occurs in database, for example a rename of a field, your queries will not compile, and you will see the problem in compilation time instead of running time.

Yes I have already talked about in my previous post http://alexsotob.blogspot.com/2011/01/deep-inside-you-cry-cry-cry-dont-let.html but there are an API that do the same as Criteria API, and is called QueryDSL. QueryDSL is a framework which enables the construction of type-safe SQL-like queries for multiple back-ends including JPA, JDO and SQL in Java. Working with QueryDSL and JPA is like working with HibernateMetamodel Generator because QueryDSL has an Annotation Processor that generates Meta-model information from JPA 2 annotated classes. So I suppose you are wondering, why I should use QueryDSL instead of JPA 2 Criteria API? In its site there are interesting posts about that http://source.mysema.com/forum/mvnforum/viewthread_thread,49.

What makes really different QueryDSL from JPA2 Criteria API, is that QueryDSL also works with JDBC applications. Yes you read it right, JDBC applications can take benefit from QueryDSL, and instead of creating queries as plain text, they can be constructed in a strongly-typed manner too.

Some of advantages of using QueryDSL in JDBC are:

• Code Completion in IDE.
• Almost no syntactically invalid queries allowed (type-safe on all levels).
• Domain types and properties can be referenced safely (no Strings involved!).
• Incremental query definition is easier.

If you visit its webpage you could see one example using QueryDSL with JPA, but also one for JDBC. What I am going to explain is how to use QueryDSL with Spring Jdbc Template. Most of us, when we develop a Spring application and want to use JDBC, we use JdbcTemplate class or one of its extensions.  For that reason I will explain how I use JdbcTemplate with QueryDSL.

First of all I implement an extension of JdbcTemplate class. I have called QueryDSLJdbcTemplate. This class has a simple method called queryForList(SQLQuery sqlQuery, RowMapper rowMapper, Expression... expressions) and has three parameters, the first one is a SQLQuery object, this is the main class where you define the query you want to execute (like JPA 2 Criteria), the next one is a RowMapper object as most of JdbcTemplate methods use, and the third one is an array of Expressions; these expressions represents each database fields we want to return as result, like id, name, age, ....

Implementation of this method:

At line 4, we are attaching a connection to given SQLQuery.

At line 6, the query is executed, and result set is returned.

At line 10, the result set is transformed to a list of required objects, using RowMapper.

Next step is creating a query. In this example I will use an Employee class that have two attributes, an id and a name. The query is finding an employee by name. So EmployeeDAO looks like:

Using JPA you specify which database engine is used injecting Database dialect. In our case it is a JDBC application so we should configure SQLQuery object with database engine used, so QueryDSL generates query syntax correctly. This is done with line 1 and 2.

Line 4 is an auto-generated class, similar to classes generated by Hibernate Annotation Processor. It contains all meta-information of an entity.

Line 5 is the query construction. See that we are not creating an SQL string (SELECT * FROM Employee as emp WHERE emp.name=?), but we are creating in a programmatic way. Look this line qEmployee.name.eq(name) because if we change field name to fullName, our code will not compile (qEmployee.name attribute does not exist now) meanwhile SQL string approach will compile, but crashing in runtime.

Line 7 is a simple RowMapper, and there the parameter name is also not specified as string but as an object, where native queries were rs.getString("name") and if name does not exists an SQLExpcetion was thrown, now a compilation error would be thrown too.

After RowMapper definition, we are executing the find method. The last two attributes are the fields we are looking for. Between SQL query and QueryDSL expression you can identify the FROM clause in both and the WHERE clause in both, but not the * of SELECT. This is the place where this information is passed.

Last method is for extracting real column name to result set.

Of course this is a basic implementation, for example Dialect should be injected rather than created each time, EmployeeRowMapper could not be an inner class, and getParameterName should be in a Util class, but because of clarity, all this code has been implemented in the same class.

And I suppose you are still wondering what is QEmployee class. Employee class is a simple DTO object. QEmployee is where all meta-information of Employee class is stored. In JPA usually JPA Providers have an Annotation Provider that read annotated model classes and generates Meta-model classes. In JDBC, there are no annotated model classes, but an alternative mechanism is provided for generating these classes. QueryDSL provides an ANT-task, a Maven-Task, and a Plain API. I will show you plain API configuration and same values should be provided for ANT and Maven tasks.

You must specify as input parameters, a connection to database and which schema contains business model. And as output parameters, which package should be generated meta-model classes, and root source code directory. The best approach for generating these classes is using Maven, so before compile goal is executed, all meta-data model is generated.

And Spring Application Context class looks as simple as:

Now you can take all benefits from using DSL for queries in JDBC applications.

Imba Wimbo Wa Upepo Wakati Unajiwa Na

You never develop code without version control, why do you develop your database without it? Liquibase is database-independent library for tracking, managing and applying database changes.

Liquibase works with XML files, these files contains which changes should apply to database (create, drop, insert, delete, update, and managing constraints...). Also they contain the revision code (id), so when migration from old database to new database is performed, Liquibase knows if changes have already applied or not. If not, database is updated.

Internally, Liquibase creates a DatabaseChangeHistory table where it saves which update files have been imported. If a change in changelog file is not in the DatabaseChangeHistory table, Liquibase executes it and records the change for being skipped next runs. 

Now you know what Liquibase do, what I am doing to explain is how I use in a real use case.

If your company develops server side software, Liquibase is important but you can survive without it because you update only one computer and in theory updates are successive, you would not jump in production server from version 1.0.0 to 1.0.2. Of course this sentence only applies if you don't distribute your server code (your clients connects directly to your server), if not (like Sonar for example), you have the same problem like standalone applications have with database updates.

I develop software that is standalone, every client has its installation (they do not connect to central server), and we have hundreds of clients, where every client have different versions of our product. Although we are constantly developing upgrades, not all clients update at same time, so a typical scenario is that some clients have version 1.0.1, others 1.0.3, and last version is an update to 1.1.0. When a client wants to install a new version, our product is installed correctly, the problem is that usually our database changes too, and our clients want back compatibility with previous versions, and of course they don't want to loose database registries. 


Moreover our changes can imply adding new tables, or adding/removing new "static" information into database. Before we were using Liquibase, we created SQL scripts for jumping between versions, and then depending on client version we sent the required SQL scripts so they could update software and database. As you can imagine this was chaotic, we should create SQL scripts for every version, and those scripts should be sent to client with the human error of sending bad ones or not all required (think about jumping from 1.0.0 to 2.0.0, we should execute one script for each intermediate version) so if a failure was occured, application stopped to work and the database remains inconsistent .

As you can imagine Liquibase solves this problem, first of all it uses XML, which is readable than plain SQL, second, it allows us to define a single file for migrating from one version to another one, knowing exactly which changes have been applied in each version, and last it is automatic, once changelog files are created, Liquibase automatically knows which current database version is installed, and which patches should apply.

Let me show with simple example:

We have one Liquibase master file which imports all changelog files that should be applied for updating database to last version (master file where we add a new include line for each version we release):

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
  http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
  <include file="META-INF/database-change/db.changelog-1.1.xml"/>
  <include file="META-INF/database-change/db.changelog-1.2.xml"/>
</databaseChangeLog>

You can see that two files are imported, first one (META-INF/database-change/db.changelog-1.0.xml) that contains changes for version 1.1, and the second ones that contains changes for migrating from 1.1 to 1.2. When Liquibase reads this file, it starts to execute sequentialy the files. If file have been already imported, Liquibase skip it, and tries next one.

First changelog file:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">

    <changeSet id="1" author="bob">
        <createTable tableName="department">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="active" type="boolean" defaultValueBoolean="true"/>
        </createTable>
    </changeSet>
</databaseChangeLog>

Second changelog file:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
    <changeSet id="2" author="bob">
        <createTable tableName="room">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="active" type="boolean" defaultValueBoolean="true"/>
        </createTable>
<insert tableName="department">
     <column name="id" valueNumeric="2"/>
     <column name="name" value="RD"/>
     <column name="active" valueBoolean="true"/>
</insert>
    </changeSet>
</databaseChangeLog>

In version 1.1 we are creating a table named department and in version 1.2 we also add a table named room and insert a new department.

I have used this example in HSQL database and this is the result script:

CREATE SCHEMA PUBLIC AUTHORIZATION DBA

CREATE MEMORY TABLE DATABASECHANGELOGLOCK(ID INTEGER NOT NULL,LOCKED BOOLEAN NOT NULL,LOCKGRANTED TIMESTAMP,LOCKEDBY VARCHAR(255),CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY(ID))

CREATE MEMORY TABLE DATABASECHANGELOG(ID VARCHAR(63) NOT NULL,AUTHOR VARCHAR(63) NOT NULL,FILENAME VARCHAR(200) NOT NULL,DATEEXECUTED TIMESTAMP NOT NULL,ORDEREXECUTED INTEGER NOT NULL,EXECTYPE VARCHAR(10) NOT NULL,MD5SUM VARCHAR(35),DESCRIPTION VARCHAR(255),COMMENTS VARCHAR(255),TAG VARCHAR(255),LIQUIBASE VARCHAR(20),CONSTRAINT PK_DATABASECHANGELOG PRIMARY KEY(ID,AUTHOR,FILENAME))

CREATE MEMORY TABLE DEPARTMENT(ID INTEGER NOT NULL,NAME VARCHAR(50) NOT NULL,ACTIVE BOOLEAN DEFAULT TRUE,CONSTRAINT PK_DEPARTMENT PRIMARY KEY(ID))

CREATE MEMORY TABLE ROOM(ID INTEGER NOT NULL,NAME VARCHAR(50) NOT NULL,ACTIVE BOOLEAN DEFAULT TRUE,CONSTRAINT PK_ROOM PRIMARY KEY(ID))

CREATE MEMORY TABLE SAIL(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,SERIAL_NUMBER VARCHAR(255) NOT NULL,VERSION INTEGER)

CREATE MEMORY TABLE SAILOR(DTYPE VARCHAR(31) NOT NULL,ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255) NOT NULL,VERSION INTEGER)

CREATE MEMORY TABLE SHIP(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255) NOT NULL,VERSION INTEGER,CAPTAIN BIGINT,CONSTRAINT SYS_CT_57 UNIQUE(NAME),CONSTRAINT FK35DA3CEE2A6862 FOREIGN KEY(CAPTAIN) REFERENCES SAILOR(ID))

CREATE MEMORY TABLE SHIP_SAILS(SHIP BIGINT NOT NULL,SAILS BIGINT NOT NULL,PRIMARY KEY(SHIP,SAILS),CONSTRAINT SYS_CT_61 UNIQUE(SAILS),CONSTRAINT FK617EDEDF2E41089E FOREIGN KEY(SHIP) REFERENCES SHIP(ID),CONSTRAINT FK617EDEDF348D52B9 FOREIGN KEY(SAILS) REFERENCES SAIL(ID))

ALTER TABLE SAIL ALTER COLUMN ID RESTART WITH 1

ALTER TABLE SAILOR ALTER COLUMN ID RESTART WITH 1

ALTER TABLE SHIP ALTER COLUMN ID RESTART WITH 1

CREATE USER SA PASSWORD ""

GRANT DBA TO SA

SET WRITE_DELAY 10

SET SCHEMA PUBLIC

INSERT INTO DATABASECHANGELOGLOCK VALUES(1,FALSE,NULL,NULL)

INSERT INTO DATABASECHANGELOG VALUES('1','bob','META-INF/database-change/db.changelog-1.1.xml','2011-02-24 11:11:30.565000000',1,'EXECUTED','3:5be91851d2c28889a24a98682a3e7f24','Create Table','',NULL,'2.0.1')

INSERT INTO DATABASECHANGELOG VALUES('1','bob','META-INF/database-change/db.changelog-1.2.xml','2011-02-24 11:11:30.565000000',2,'EXECUTED','3:f84a5c1495e724b97a17c9264f6a6c04','Create Table, Insert Row','',NULL,'2.0.1')

INSERT INTO DEPARTMENT VALUES(2,'RD',TRUE)

Take a look that Liquibase creates two tables for managing database versions, and in our case two inserts to Liquibase tables are done, one for each changelog file.

• In red are statements that are executed by Liquibase the first time is executed.
• In orange insertions about updating from 1.0 to 1.1. See that table departments is created, and also is inserted that db.changelog-1.1 has been executed.
• In blue database structure that was already inserted in version 1.0.
• In green are statements that are executing for updating from 1.1 to 1.2. Last line is an insert into a table created in version 1.1. Obviously this insert will always work because Liquibase ensure that 1.1 script is executed before 1.2.

Liquibase supports:

• Structural Refactorings like create table, add column, create view.
• Data Quality Refactorings like unique constraints, null constraints, create sequence.
• Referential Integrity Refactorings primary key and foreign key management.
• Non-Refactoring Transformations like insert data, load data, tag database.
• Architectural Refactorings for creating indexes.
• Custom Refactoring where you specify which SQL sentence you wanted to execute.

Liquibase can be executed as standalone application, but also as Ant script, Maven script, Servlet Listener, Grails or Spring. In my case, I have chosen Spring rules, because I am already using Spring, and because I wanted that each time the application is started, it checks changelog files to see if there was any new upgrade to process.

<bean id="liquibase" class="liquibase.integration.spring.SpringLiquibase"> <property name="dataSource" ref="dataSource"></property> <property name="changeLog" value="classpath:META-INF/database-change/db.changelog-master.xml"></property></bean>

Liquibase not only updates database, but can also rollback changes, makes a diff file between versions, generates documentation about changes in javadoc fashion, or simply just not run update directly but create an SQL file so DBA can check updates.

But wait there are even more. Do you know Spring Profiles released in Spring 3.1? If not take a look, and see how  perfect matches Liquibase context parameter with Spring Profiles. Liquibase context parameter indicates that some changelog should only be applied in case Liquibase is running in defined context. For example you can have a changelog that inserts data for your unit testing queries, and another one that creates some tables. Inserts should be applied only when test context is active, but creation tables should be applied always.

In <changeset></changeset> tag you specify in which context should be applied. If no context is placed, change is always executed. Keep in mind is a good practice that test changesets and production changesets reminds in the same file. Only is a matter of configuring contexts correctly.

<changeSet id="2" author="bob" context="test"/>

Previous changeSet will be executed only when test context is specified. In Spring Liquibase exists a property called contexts where you indicate which contexts are currently active.

<property name="contexts" value="test" /><changeSet id="2" author="bob" context="test"/>

Both changeSet and Spring property supports comma-separated value for configuring more than one context.

Then thanks of Spring 3.1, you defines two SpringLiquibase instances each one in different Spring Profile, one with test contexts, for example test, test-integration, ..., and the other one without contexts.

Now when we release a new version, simply we packed all in a war, and sent to client. When the war is deployed in its server, the new version is installed, with software updates, database updates, and more important, client has not lost its data. No postinstallation steps are required nor SQL scripts.