Liquibase for DB Migrations

I have just now discovered a very nice-looking tool for database migrations: Liquibase

http://www.liquibase.org/

With Liquibase, you can model your DB increments as XML files that will translate to as many as 13 different databases. A sample DB increment (taken from the Liquibase manual):

<!--
  ALTER TABLE PERSON MODIFY COLUMN firstname VARCHAR(5000);
  -->
<modifyColumn tableName="person">
    <column name="firstname" type="varchar(5000)"/>
</modifyColumn>

<!--
  ALTER TABLE ADDRESS ADD CONSTRAINT fk_address_person
  FOREIGN KEY (person_id) REFERENCES person (id);
  -->
<addForeignKeyConstraint constraintName="fk_address_person"
    baseTableName="address" baseColumnNames="person_id"
    referencedTableName="person" referencedColumnNames="id"
/>

<!--
  UPDATE ProductSettings SET property = 'vatCategory'
  WHERE property = 'vat';
  -->
<update tableName="ProductSettings">
    <column name="property" value="vatCategory"/>
    <where>property='vat'</where>
</update>

…and so on. I guess it’s about time to contact the Liquibase folks and ask for cooperation! A fully integrated solution involving database schema management, database schema migration, and jOOQ’s source code generation seem to be the perfect toolset for a Java database developer

jOOQ and Google Cloud SQL Example

This is all too simple. Here’s how you can create an easy jOOQ / Google Cloud SQL integration example:

  1. Sign up with Google App Engine
  2. Sign up with Google Cloud SQL
  3. Create a Google App Engine project (preferably with Eclipse)
  4. Add jOOQ to your project
  5. Add your generated schema to your project
  6. Done

Google Cloud SQL is actually a MySQL database, which you can also install locally on your machine for development purposes. For your jOOQ integration, this means that you will set up the code generation and execution just as if you were using a plain MySQL database. Simple, huh?

See a simple example in action, here:
http://jooq-test.appspot.com/jooq-test

Check out the source code here (libs not included):
https://github.com/lukaseder/jOOQ/blob/master/jOOQ-google-cloud-sql/src/org/jooq/test/JOOQTest.java

And some instructions about Google Cloud SQL:
https://code.google.com/apis/sql/docs/developers_guide_java.html

Use jOOQ to transform java.sql.ResultSet

jOOQ has many uses. The fact that it provides a thin layer of additional abstraction over JDBC can be interesting enough for some users wanting to operate on their own java.sql.ResultSet objects. Let’s say, you prefer using JDBC directly, for query execution, rather than letting jOOQ execute queries for you:

PreparedStatement stmt = connection.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();

// Instead of verbosely iterating over the above ResultSet, use jOOQ:
Factory factory = new Factory(connection, dialect);
Result<Record> result = factory.fetch(rs);

// And use jOOQ's API for more ease:
for (Record record : result) {
  System.out.print(record.getValue("ID"));
  System.out.print(": ");
  System.out.println(record.getValue("NAME"));
}

// Use jOOQ to export the result to XML or other formats:
String xml = result.formatXML();
String csv = result.formatCSV();
String json= result.formatJSON();

// Get a text version of the result for logging or console output
String text= result.format();

// Or use jOOQ to export the result into your own JPA-annotated entities
List<Value> values = result.into(Value.class);

// With Value being
public class Value {
  @Column(name = "ID")
  public Integer id;

  @Column(name = "NAME")
  public String name;
}

If you don’t want jOOQ to be your strategic SQL interface, you can still use it as a utility every now and then for your regular JDBC use.

Debug logging SQL with jOOQ

This nice little feature is not much advertised in the jOOQ manual, but probably it’s something that most of us developers want and love when we have it. When you put log4j or slf4j on the classpath along with jOOQ, then jOOQ will use those frameworks for logging according to your configuration. While jOOQ is pretty quiet on ERROR/WARN/INFO level logging, it becomes quite a chatty tool on DEBUG/TRACE level.

DEBUG level logging example

Check out some sample DEBUG level output when executing this query:

Result<Record> result = create
  .select(TAuthor.LAST_NAME, create.count().as("c"))
  .from(T_BOOK)
  .join(T_AUTHOR)
  .on(TBook.AUTHOR_ID.equal(TAuthor.ID))
  .where(TBook.TITLE.notEqual("1984"))
  .groupBy(TAuthor.LAST_NAME)
  .having(create.count().equal(2))
  .fetch();

And the output is:


Executing query          : select "t_author"."last_name", 
  count(*) as "c" from "t_book" join "t_author" on 
  "t_book"."author_id" = "t_author"."id" where 
  "t_book"."title" <> '1984' group by "t_author"."last_name"
  having count(*) = 2

Fetched result           : +---------+----+
                         : |last_name|   c|
                         : +---------+----+
                         : |Coelho   |   2|
                         : +---------+----+
Statement executed       : Total: 3.911ms


The query text is printed to the log output with inlined parameters (i.e. bind variables are replaced for logging). Then a text table representation of the result’s first 5 rows is printed before the query execution time.

TRACE level logging example

On trace level, you can see even more of jOOQ’s internals, although usually this is only needed when debugging jOOQ:


Executing query          : select "t_author"."last_name", 
  count(*) as "c" from "t_book" join "t_author" on 
  "t_book"."author_id" = "t_author"."id" where 
  "t_book"."title" <> '1984' group by "t_author"."last_name"
  having count(*) = 2

Preparing statement      : select "t_author"."last_name", 
  count(*) as "c" from "t_book" join "t_author" on 
  "t_book"."author_id" = "t_author"."id" where 
  "t_book"."title" <> cast(? as varchar) 
  group by "t_author"."last_name" 
  having count(*) = cast(? as int)

Statement prepared       : Total: 1.451ms, +0.435ms
Binding variable 1       : 1984 (class java.lang.String)
Binding variable 2       : 2 (class java.lang.Integer)
Variables bound          : Total: 1.808ms, +0.356ms
Attaching                : RecordImpl [ RecordImpl [values=[null, null]] ]
Fetching record          : RecordImpl [values=[Coelho, 2]]
Fetched result           : +---------+----+
                         : |last_name|   c|
                         : +---------+----+
                         : |Coelho   |   2|
                         : +---------+----+
Statement executed       : Total: 6.29ms, +4.481ms


In addition to the previously shown DEBUG-level output, you’ll also find the true SQL statement that is executed, including bind variables and some additional type-casts introduced by jOOQ. Time measuring is divided into 3 steps: statement preparing, variable binding, and statement execution. Also, all bind variables are documented, and every fetched record is documented. That’s a lot of logging. Be sure to turn it off when not needed!

Logging configuration

In future versions of jOOQ, logging will become more configurable

A nice summary of NoSQL databases

I stumbled upon a nice summary of NoSQL databases. Apart from the ones that usually pop up in “hip” open source discussion forums, such as Play! Framework (e.g. MongoDB, CouchDB), there are many more worth mentioning and analysing. Read the full article here:

http://cleanclouds.wordpress.com/2011/10/14/”no”sql-cloud-computing/

Cloud Fever now also at Sybase

After SQL Server (SQL Azure) and MySQL (Google Cloud SQL), there is now also a SQL Anywhere database available in the cloud:

http://www.sybase.ch/fujibeta

It’s called Sybase SQL Anywhere OnDemand or code name Fuji. I guess the connotation is that your data might as well be relocated to Fuji. Or your DBA might as well work from Fuji. Who knows ;-)

I don’t know where to start adding integration tests for jOOQ with all those cloud-based SQL solutions. Anyway, exciting times are coming!

What are procedures and functions after all?

Many RDBMS support the concept of “routines”, usually calling them procedures and/or functions. These concepts have been around in programming languages for a while, also outside of databases. Famous languages distinguishing procedures from functions are:

  • Ada
  • BASIC
  • Pascal
  • etc…

The general distinction between (stored) procedures and (stored) functions can be summarised like this:

Procedures:

  • Are called using JDBC CallableStatement
  • Have no return value
  • Usually support OUT parameters

Functions:

  • Can be used in SQL statements
  • Have a return value
  • Usually don’t support OUT parameters

But there are exceptions to these rules:

  • DB2, H2, and HSQLDB don’t allow for JDBC escape syntax when calling functions. Functions must be used in a SELECT statement
  • H2 only knows functions (without OUT parameters)
  • Oracle functions may have OUT parameters
  • Oracle knows functions that mustn’t be used in SQL statements for transactional reasons
  • Postgres only knows functions (with all features combined). OUT parameters can also be interpreted as return values, which is quite elegant/freaky, depending on your taste
  • The Sybase jconn3 JDBC driver doesn’t handle null values correctly when using the JDBC escape syntax on functions

In general, it can be said that the field of routines (procedures / functions) is far from being standardised in modern RDBMS. Every database has its ways and JDBC only provides little abstraction over the great variety of procedures / functions implementations, especially when advanced data types such as cursors / UDT’s / arrays are involved.