Blog of Claudius Hauptmann

Especially in social software, where almost everybody can edit the contents like in Wikipedia, it is important to store the history of the data. OpenStreetMap is also working on a concept of changesets. Some time ago, I was looking for such functionality that can be used in conjunction with the Java Persistence API (JPA) and automatically logs changes. Perhaps you already noticed the Blog of Adam Warksi at my blogroll, who is the founder of the Envers project.

The Envers project aims to enable easy versioning of persistent classes. All that you have to do is annotate your persistent class or some of its properties, that you want to version, with @Audited. For each versioned entity, a table will be created, which will hold the history of changes made to the entity. You can then retrieve and query historical data without much effort. Similarly to Subversion, the library has a concept of revisions. Basically, one transaction is one revision (unless the transaction didn’t modify any versioned entities). As the revisions are global, having a revision number, you can query for various entities at that revision, retrieving a (partial) view of the database at that revision. You can find a revision number having a date, and the other way round, you can get the date at which a revision was commited. [Envers project]

Envers works on top of / integrates into hibernate, which is an implementation of the JPA specification integrated in the JBoss Application Server and since october 2008, envers is an official hibernate module.

My first attempts getting envers work, were very disappointing, since I got many exceptions, after I added envers to a well working project. Yesterday, I made a new attempt and downloaded snapshot versions of hibernate (until now, there is no official release of hibernate, which contains envers) from the MavenSnapshotReporitory. My code was working fine and envers created a new revision for my data for every commit. Really nice! Great job, Adam!

But what about Hibernate Spatial (like in my post about storing map data) and Envers? Do they cooperate? I was dying to write a little test, whether that works. First I, created a class, that stores markers, that have got an id, a caption and a location:

package com.claudiushauptmann.test;

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

import org.hibernate.annotations.Type;
import org.hibernate.envers.Audited;

import com.vividsolutions.jts.geom.Point;

@Entity
@Audited
public class Marker implements Serializable {

    @Id
    @GeneratedValue
    private int id;

    @Type(type="org.hibernatespatial.GeometryUserType")
    private Point location; 

    private String caption;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public Point getLocation() {
        return location;
    }

    public void setLocation(Point location) {
        this.location = location;
    }

    public String getCaption() {
        return caption;
    }

    public void setCaption(String caption) {
        this.caption = caption;
    }
}

Second, I created two tables, one for the current data and one for the history:

CREATE TABLE marker
(
  id integer NOT NULL,
  "location" geometry NOT NULL,
  caption text NOT NULL,
  CONSTRAINT "PK_marker" PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE TABLE marker_aud
(
  id integer NOT NULL,
  "location" geometry NOT NULL,
  caption text NOT NULL,
  rev integer NOT NULL,
  revtype integer NOT NULL
)
WITH (OIDS=FALSE);

Third, there must be a table, that contains meta data about the revisions. This is the minimum of the columns:

CREATE TABLE revinfo
(
  rev integer NOT NULL,
  revtstmp bigint NOT NULL,
  CONSTRAINT "PK_REVINFO" PRIMARY KEY (rev)
)
WITH (OIDS=FALSE);

Don’t forget to add enlist envers in your persistence.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
    <persistence-unit name="test">
        <jta-data-source>java:/test</jta-data-source>
        <properties>
            <property name="hibernate.dialect" value="org.hibernatespatial.postgis.PostgisDialect" />
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.ejb.event.post-insert" value="org.hibernate.envers.event.AuditEventListener" />
            <property name="hibernate.ejb.event.post-update" value="org.hibernate.envers.event.AuditEventListener" />
            <property name="hibernate.ejb.event.post-delete" value="org.hibernate.envers.event.AuditEventListener" />
            <property name="hibernate.ejb.event.pre-collection-update" value="org.hibernate.envers.event.AuditEventListener" />
            <property name="hibernate.ejb.event.pre-collection-remove" value="org.hibernate.envers.event.AuditEventListener" />
            <property name="hibernate.ejb.event.post-collection-recreate" value="org.hibernate.envers.event.AuditEventListener" />
            <property name="org.jboss.envers.warnOnUnsupportedTypes" value="true" />      
        </properties>
    </persistence-unit>
</persistence>

This is working very well! For every commit, a new revision is created in the table revinfo and the new version of the marker is stored into the table marker_aud and the data within the table marker was updated.

Of course, we do not only need to store the history, but also a way to retrieve past data. There is a pretty nice quickstart tutorial about envers, which describes, how to get the data out of the history with the VersionsReader.

OpenStreetMap is a collaborative project to create a free editable map of the world. The maps are created using data from portable GPS devices, aerial photography and other free sources [Wikipedia]. OpenStreetMap data can be used freely under the terms of the Creative Commons Attribution-ShareAlike 2.0 license [OpenStreetMap]. Thus it can be used e.g. in your own applications, mobile devices or web pages, for productive use or testing purposes (This abstract makes no claim to be complete, please read the license before using the data).

The database model is documented here. OpenStreetMap provides two ways for programmatical use/access. First, snapshots and diffs that can be imported in your own database and be used for replication (The files are here). Second, an API (implemented as a REST webservice). This link queries a mountain peak in bavaria, for example (Look at the source code, if the browser displays a blank page). If you are just interested in looking up some points of interest from time to time and internet access is available, using the API is a good idea. Otherwise an export or  replication (full or partial) of the database is the better solution, particularly if you want to run intensive geospatial computings on the data.

You do not have to write your own client code to access the API. JOSM, a java-based map editor for OpenStreetMap is using the web service to load the map content and store your change set. Perhaps it is a good idea to look at the source code and the api calls. I tried to check out the project via eclipse (with subclipse) and I just had to click debug as java application and it worked fine, since the required libraries are included in the svn repository. Then I could debug through the code and watch the API calls. Unfortunately there is much GUI code mixed into the logic, but the code is easy to understand.

If you want to export/replicate the data into your own data store, there is a really great java-based console application: Osmosis. It can be run within cron jobs to periodically to replicate the database. I checked out the source code, but had some troubles debugging the application, since not all libraries are included in the svn repository. It took some time to collect all necessary jar files from the web. I advise you not to grub through the code. There is a lot of functionality out of the box, which can be read here and there is an API to write your own plugins for Osmosis, that can be included in the pipelines, if you want to use your own database schema, e.g. with Hibernate Spatial like in my last post about how to store your map data on the server side. A really great feature is the option –read-change-interval, that calculates, which change sets have to be downloaded to keep your own replication of the database up to date.

Imagine you want to store your markers, polylines and polygons that are currently on a MapWidget on the server side into a database. How would you do that? My first attempt ended with some Java classes that were mapped via Hibernate in a MySQL database. I created an abstract class “overlay” that was inherited by “point”, “polyline” and “polygon”. Map features, e.g. streets, mountain peaks or alpine huts, had properties of the type of the new classes. That was really great. But the first problems came with need for computings like boundaries and distances or testing for spatial relationships like intersects, touches, crosses, within and contains.

Last week there was a talk of a student about his diploma thesis. Like me, he also wanted to store spatial data into a database. He chose PostgreSQL as the database engine with an extension called PostGIS that “spatially enables” the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI’s SDE or Oracle’s Spatial extension. PostGIS follows the OpenGIS ”Simple Features Specification for SQL” and has been certified as compliant with the “Types and Functions” profile. Wow, great! Really nice! MySQL in conrast lacks most of this features.

But how does this work with Hibernate? And, if this is possible, into which classes will my database rows be mapped? Let’s ask Google! I found some slides from Chris Hodgson that issued this topic. In his slides he demonstrates the use of Hibernate Spatial, an extension for Hibernate that maps the data into the JTS Topology Suite. Wow, super! Now I can handle spatial computings in database queries and with my Java code! There is a tutorial for your first steps with Hibernate Spatial that works fine (also with Hibernate 3.4 and JTS 1.10). After I’ve finished this tutorial, I tryed to switch my code to this solution and, for now, it seems that it works stable and fast.