Using JDBI with Postgres JSON data

I’ve been migrating some raw JDBC code over to JDBI, and joyfully stripping out lines of boilerplate code for preparing statements, opening record sets, sometimes remembering to close them, handling SQL exceptions which won’t ever occur anyway, and so on. Using the SQL Object API means the only code you have to write is the SQL and a ResultSetMapper to determine how to create your domain objects from the resultset. It really promotes adherence to the single responsibility principle and discourages you from mixing logic in with your database access code.

The database in question has a number of fields containing JSON data. More specifically, they use the PostgreSQL jsonb data type. This has required a little more tinkering to get working.

Inserting jsonb data

Out of the box, JDBI provides two annotations for binding parameters. The @Bind annotation binds a single named argument, and @BindBean binds bean properties with matching names. However, to insert jsonb data, you need to first create an PGobject instance and bind that. To do this, I created a new Binding annotation, following the guidance here: http://jdbi.org/sql_object_api_argument_binding/

The annotation code looks like this:

BindingAnnotation(BindJson.JsonBinderFactory.class)
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.PARAMETER})
public @interface BindJson {
    String value();

    public static class JsonBinderFactory implements BinderFactory {
        @Override
        public Binder build(Annotation annotation) {
            return new Binder<BindJson, String>() {                
                @Override
                public void bind(SQLStatement q, BindJson bind, String jsonString) {
                    try {
                        PGobject data = new PGobject();
                        data.setType("jsonb");
                        data.setValue(jsonString);
                        q.bind(bind.value(), data);                        
                    } catch (SQLException ex) {
                        throw new IllegalStateException("Error Binding JSON",ex);
                    }
                }
            };
        }
    }
}

To use it, annotate the json parameter with the new annotation:

@SqlUpdate("insert into my_table (id,data) VALUES (:id,:data)")
void insertJson(@Bind("id") int id, @BindJson("data") String jsonString);

And that’s it; it just works.

Querying json dynamically

I had a requirement where the parameter supplied to the query was the name of the json element to return. For example, consider the json below. I wanted to be able to paramterise a query to return either one of the key values.

{
   "element": {
      "key1": "value1",
      "key2": "value2",
      "key3": "value3"
   }
}

Using raw JDBC it was possible (although not very pretty) to concatenate a suitable sql statement and then execute it:

String sql = "select data->'element1'->'" + subKeyName + "' as value from mytable"
...

This isn’t possible when the SQL string is specified as a JDBI annotation. I found some useful Postgres json processing functions, including jsonb_extract_path_text which allows you to bind parameters normally:

@SqlQuery("select jsonb_extract_path_text(data,'element1',:subKeyName) as value from mytable")
List<String> getSubKey(@Bind("subKeyName") String subKeyName)

So far I haven’t come across any other issues using JDBI with a PostgreSQL JSON data store. I’m looking forward to trying out the new jsonb functionality in PostgreSQL 9.5 which supports writing partial updates to json fields, yippee!

  

Enabling hibernate in Ubuntu 14.04

The new laptop (Dell Inspiron 5000) has one or two teething issues. Which is disappointing as I had installed Ubuntu on the XPS I had previously (at work) with no problems at all, so thought this laptop would be a safe bet.

At present I can’t use suspend. Although the laptop sounds like it is resuming, the screen remains blank and I have to do a hard reset. It’s a pain starting everything up again whenever I shut down, so I thought I’d try hibernate instead.

For some reason this is not available on the system tray menu. To hibernate from the terminal the command is:

sudo pm-hibernate

My laptop resumes fine after this. To add the option to the system tray menu, I edited the file

/var/lib/polkit-1/localauthority/10-vendor.d/com.ubuntu.desktop.pkla

I changed the setting ResultActive to yes in the two places below:

...
[Disable hibernate by default in upower]
Identity=unix-user:*
Action=org.freedesktop.upower.hibernate
ResultActive=yes

[Disable hibernate by default in logind]
Identity=unix-user:*
Action=org.freedesktop.login1.hibernate
ResultActive=yes
...

I also decided it would be useful for the laptop to hibernate if I shut the lid. To do this I edited the file

/etc/systemd/logind.conf

I replaced the line below:

#HandleLidSwitch=suspend

with

HandleLidSwitch=hibernate

Reboot required after these changed. Now it will at least hibernate. Thanks to this blog post (and the comments) for the guidance: http://ubuntuhandbook.org/index.php/2014/04/enable-hibernate-ubuntu-14-04/

Next I need to get the audio working…

  

Super quick Sonar/Postgres setup with docker

Maybe I am easily impressed but wow! Here is how I used docker to setup sonar on my (Ubuntu) laptop superquick.

Postgres

First, set up a postgres container. The command below creates and starts a container called sonar-postgres, using the official docker postgres image.

docker run --name sonar-postgres -e POSTGRES_USER=sonar -e POSTGRES_PASSWORD=secret -d postgres

The container is created containing a sonar database and user with the supplied password. There are various options to the run command, for example to restart the container automatically. See docker run for more details. -d means detach from the container and run it in the background.

The command above does not publish any ports to the host, so we can’t psql to localhost port 5432 to see the database. However, the postgres container does “expose” port 5432 to linked containers. To have a quick peek inside, the following command creates a temporary container which executes the psql command. After entering the password you chose earlier, you are logged into the sonar database. When you exit, the container is gone.

docker run -it --link sonar-postgres:postgres --rm postgres sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U sonar'

An alternative would have been to modify the original run command to include -p 5432:5432 which would publish the ports to the host and allow direct access via psql. This was handy for me when getting started but obviously not ideal in an environment where you might have several postgres containers.

Sonar

The default command to start sonar uses the built in h2 database:

docker run -d --name sonarqube -p 9000:9000 -p 9092:9092 sonarqube:5.1

It’s not good practice to use the embedded database for continued use. To set up a container which uses the postgres container created above, use the following command:

docker run -d --name sonarqube --link sonar-postgres:pgsonar -p 9000:9000 -e SONARQUBE_JDBC_USERNAME=sonar -e SONARQUBE_JDBC_PASSWORD=secret -e SONARQUBE_JDBC_URL=jdbc:postgresql://pgsonar:5432/sonar sonarqube:5.1

In the above command, the –link option links the postgres container with a hostname of “pgsonar”, which is used again in the SONARQUBE_JDBC_URL setting to tell sonar where to find the postgres database.

Tip! If the container won’t start, run the command without the -d switch, so it remains in the foreground and you can see the log output.

Once the container is started, navigate to http://localhost:9000/ and you should see the familiar sonarqube dashboard. This process felt quicker than the usual sonar install done properly, and I can definitely see myself using docker more to supercharge my dev environment!

  

ACPI PCC probe failed

I had one problem getting Ubuntu 14.04 to install on a Dell Inspiron 5000 series. Booting from the USB was no problem – hit F12 at startup to go to boot menu, and chose to boot from the USB drive. I didn’t have to disable Secure Boot or UEFI mode.

However, after selecting “Try Ubuntu”, all went blank. When I tried a second time in Legacy boot mode, I got a more helpful error message:

ACPI PCC probe failed

A quick google suggested this was a problem introduced Ubuntu 14.04.3. Some scary sounding fixes were suggested, but I took the easy option of downloading the 14.04.1 iso instead, which installed fine.

  

What I’m reading

The more time I spend working in Software Development, the more I realise how little I knew before. I remember going on a Skills Matter Spring course about 10 years ago, and realising that until that point I wasn’t really getting OO. (To excuse myself, I was self taught and relatively new to Java, having previously hacked around in ASP amongst other things).

I am a fan of software books and recently I have been re-reading a couple of old favourites – hence the new Recommended Reading page.

I’ve also realised there are plenty of classics I haven’t read yet, so it’s time to work my way through a few of these. This Recommended Reading for Developers looks like a fairly comprehensive list, so I’m starting at number one. See you in 960 pages…

  

Reading the timestamp data from the hibernate envers revinfo table

A short blog post in case this helps anyone!

We’re using Hibernate Envers to keep an audit trail of data changes. I thought I’d have a look into the (Postgres) database to get some stats on usage – how many records of a certain type we’re creating by month for example.

The “data_aud” tables have a “rev” field which joins to the “revinfo” table, which looks like this:

             Table "public.revinfo"
  Column   |          Type          | Modifiers 
-----------+------------------------+-----------
 id        | integer                | not null
 version   | bigint                 | not null
 timestamp | bigint                 | not null
 user_name | character varying(255) | not null

I was surprised to see that the timestamp was of type bigint – the DDL was generated by Envers, not us!

Attempting to get realistic dates out of the field using Postgres functions such as

to_timestamp(revinfo.timestamp)

failed dismally.

I found this post which gives a working answer for Oracle: SQL query to get a human readable display of the *number* REVINFO.timestamp ?

With a bit of tweaking, here is a useful Postgres view which I created to make future querying a bit easier:

select id, version, user_name, timestamp,
  to_char(
    TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS')
    + (r.timestamp /1000/60/60/24 ||' day')::interval, 'YYYY/MM/DD'
  ) as rev_date, 
  to_char(
    TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS') 
    + (r.timestamp /1000/60/60/24 ||' day')::interval, 'YYYY/MM'
  ) as rev_month 
from revinfo r;
  

Gradle and the “Parent Pom”

Coincidentally, something came up I wanted to blog about, and it turns out it’s a follow up to the last blog post I wrote 9 months ago.

I’ve been increasingly unhappy with using “apply from” to apply a script plugin from a hard coded location. It’s no use if you’re offline, or not on the right VPN to access the file, or whatever. What I didn’t realise is that I could do apply all the same configuration defaults from a binary plugin. Then you get proper dependency management like you would with a Maven parent pom.

Here’s the code for the Plugin, which is just a standard gradle plugin

import org.gradle.api.Project
import org.gradle.api.Plugin
import org.apache.log4j.Logger
import org.gradle.api.GradleException

public final class CommonConfigPlugin implements Plugin<Project>{

    @Override
    void apply(Project project) {
        addDependencies(project)
        applyPlugins(project)
        configureReleasePlugin(project)
    }
    
    private void configureReleasePlugin(Project project) {

        if (!project.parent) {
            project.createReleaseTag.dependsOn { project.allprojects.uploadArchives }
        }

        //do this for all projects
        project.uploadArchives {
            ... config here ...
        }               
    }

    private void addDependencies(Project project) {
        project.afterEvaluate {            
            project.dependencies.add("testCompile", "junit:junit:4.11")
            project.dependencies.add("compile", "log4j:log4j:1.2.14")
        }
    }

    private void applyPlugins(Project project) {
        project.configure(project){
            apply plugin: 'java'
            apply plugin: 'maven'               
        }

        //apply these to parent project only
        if (!project.parent) {
            project.configure(project){
                apply plugin: 'sonar-runner'
                apply plugin: 'release'
            }
        }
    }
}

Then in the main build.gradle file for any project, I add the jar as a dependency and apply the plugin.

allprojects {
    repositories {
        mavenLocal()
        maven { url "http://my.repo"}
    }
    apply plugin: "my-common-config"

}
buildscript {

    repositories {
        mavenLocal()
        maven { url "http://my.repo"}
    }
    dependencies {
        classpath 'uk.co.anorakgirl.gradle:my-common-config-plugin:1.0.0'
    }
}

Any dependencies which the plugin has end up as dependencies in the buildscript of the project, meaning I don’t have to repeat the dependency on the release plugin, because my common config plugin already depends on it.

I’m going to put more in this common-config-plugin.

  

Gradle release ramblings

Over the last couple of weeks I had to get up to speed on gradle. We got inspired to try out Gradle at work following an great talk on Groovy for SysAdmins by Dan Woods at the Groovy & Grails eXchange 2013, during which Groovy and Gradle were used as a mechanism for building and deploying VMs.

First off, I seem to love Gradle! The build.gradle files are so slimline and readable. The challenges for the last couple of weeks were:

  • How to manage releases in a similar way to maven
  • How to include common elements in our builds (parent pom functionality

Gradle Release plugin

We decided to use the townsfolk gradle-release plugin as it seems to do pretty much what Maven release has been doing for us: check for snapshot dependencies, check version control is up to date, set version, build, tag, set next snapshot and commit.

To use the plugin, the dependency has to be made available to the build.gradle file itself. Standard dependencies are only available to the project being built. This is where the buildscript block comes in – dependencies declared here can be used in the groovy script itself.

buildscript{
    repositories {
        mavenCentral()
        maven { url "https://oss.sonatype.org/content/groups/public"}
    }
    dependencies {
        classpath 'com.github.townsfolk:gradle-release:1.2'
    }
}

The one thing that the plugin does not do is upload artifacts to a maven repository, but this is easy as Gradle allows you to declare dependencies between tasks. We add a dependency on the uploadArchives task, which comes from the maven plugin:

createReleaseTag.dependsOn uploadArchives

A couple of gotchas here. When using this with a multi-module project, you can only apply the plugin to the parent project, and all modules have to have the same version. We found initially that only the parent project was getting deployed to artifactory. The task dependency must be modified as follows for multi-module projects:

createReleaseTag.dependsOn allprojects.uploadArchives

Extracting common gradle elements to use across projects

The parent pom we use for maven projects has common plugin configurations, standard dependencies and so on. We decided to try and create a similar “parent” gradle file, to use across all our projects. The apply from syntax can be used to configure the project with an external script:

apply from: 'https://artifactory.url/../gradle-common-1.0.gradle'

Notice that the apply from is coming from a maven repository. We used the release plugin described above to release a common gradle file as a maven artifact, so this can managed following all our standard release practices too!

The common gradle file can contain all of the gradle build language, including applying other scripts. One thing it cannot contain is a buildscript block. To include common configuration in a buildscript block, you must create a second common file, containing a project.buildscript block:

project.buildscript {
    repositories {
        mavenCentral()
        maven { url "https://oss.sonatype.org/content/groups/public"}
    }
    dependencies {
        classpath 'com.github.townsfolk:gradle-release:1.2'
    }
}

Within the project, apply this script within the project buildscript block:

buildscript {
	apply from: 'https://artifactory.url/../gradle-common-buildscript-1.0.gradle'
}

This seems like doubling up to me, but is how it appears to work. It means we have two common gradle files to apply separately.

One final gotcha I found when moving the release plugin configuration into our common gradle files – when I tried to configure the task dependency in the external script, I got this error when building multi-module projects:

Could not find property 'uploadArchives' on project ':submodule'.

This seems to be because the parent is trying to configure the dependency on the subproject uploadArchives task, but the subproject does not yet have that task. It must be to do with the order in which gradle applies external scripts? Anyway, the solution was to delay evaluation of the dependency (those clever gradle folk think of everything!). Putting the dependency in a closure does this (Found this at #3 here 12-new-things-i-learned-from-a-three-day-gradle-training. So in the main common gradle file I have these lines, which apply the release plugin to the parent project only and create a lazy dependency on the uploadArchives task for all modules.

if (!project.parent) {
	apply plugin: 'release'
	project.createReleaseTag.dependsOn { allprojects.uploadArchives }
}

And that’s it, infrastructure in place!

(Credit for setting a lot of this up goes to JCDubs but I was so excited when I started working with it I had to write up what we learned on the way. I’m looking forward to lots more XML free building…)

  

My First Groovy DSL

I decided to have a go at automating the management of our Jenkins jobs. Basically most of our projects have similar builds, and I want to keep the job configs standard – they tend to diverge when we manage them via the UI. I’ve previously played with the Jenkins API using HttpBuilder to post XML config files. My DSL doesn’t do anything clever to generate the XML, but it makes the Jenkins config very readable:

jenkins {   
    url = "http://jenkins.url/"
    username = "jenkins"
    apiKey = "asdfadsgdsfshgdsfg"

    buildGroup {
        name = "Maven Builds"
        xml {
            feature = "release/xml/maven-test-config.xml"
            develop = "release/xml/maven-install-config.xml"
        }
        repos {
            project1 = "ssh://git.repo/project1.git"
            project2 = "ssh://git.repo/project2.git"
        }
    }

    buildGroup {
        name = "Grails Builds"
        xml {
            feature = "release/xml/grails-test-config.xml"
            develop = "release/xml/grails-install-config.xml"
        }
        repos {
            project4 = "ssh://git.repo/project4.git"
            project5 = "ssh://git.repo/project5.git"
        }
    }

}

I think this is so easy to read.

I’m not really sure if I’ve gone the right way about writing this, but here goes:

First, I have a main method, which parses the file. It binds the “jenkins” method to an instance of my JenkinsDsl class.

    Binding binding = new Binding();
    JenkinsDsl jenkinsDsl = new JenkinsDsl();
    binding.setVariable("jenkins",jenkinsDsl);
    GroovyShell shell = new GroovyShell(binding);
    Script script = shell.parse(file);
    script.run();

The JenkinsDsl class overrides the `call` method, and uses `methodMissing` to define how we handle the buildGroups. The really cool bit is I didn’t have to write anything special to get the url, username and password from the jenkins config file – groovy is automatically calling a ‘setProperty’ method on the delegate.

class JenkinsDsl {

    def call(Closure cl) {
        log.info "Processing Main Configuration"

        cl.setDelegate(this);
        cl.setResolveStrategy(Closure.DELEGATE_ONLY)
        cl.call();
    }

    def url
    def apiKey
    def username

    def jenkinsHttp

    def methodMissing(String methodName, args) {

        if (methodName.equals("buildGroup")) {

            Closure closure = args[0]
            closure.setDelegate(new JenkinsBuildGroup())
            closure.setResolveStrategy(Closure.DELEGATE_ONLY)
            closure()
            closure.delegate.updateJenkins(getJenkinsHttp())
        } else {
            log.error "Unsupported option: " + methodName
        }
    }

    def getJenkinsHttp() {
        if (!jenkinsHttp) {
            jenkinsHttp = new JenkinsHttp(url, username, apiKey)
        }
        jenkinsHttp
    }   
}

The JenkinsBuildGroup class is very similar – to save time I’ve used the ‘Expando’ class to collect the xml and repository details.

class JenkinsBuildGroup {

    def name
    def xml
    def repoList

    /**
     * Use methodMissing to load the xml and repos closures
     * @param methodName
     * @param args
     */
    def methodMissing(String methodName, args) {

        if (methodName.equals("xml")) {
            xml = new Expando()
            Closure closure = args[0]
            closure.setDelegate(xml)
            closure.setResolveStrategy(Closure.DELEGATE_ONLY)
            closure()
        } else if (methodName.equals("repos")) {
            repoList = new Expando()
            Closure closure = args[0]
            closure.setDelegate(repoList)
            closure.setResolveStrategy(Closure.DELEGATE_ONLY)
            closure()
        } else {
            log.error "Unsupported option: " + methodName
        }
    }

    def updateJenkins(def jenkinsHttp) {
        xml.getProperties().each { String jobType, String filePath ->

            def configXml = new File(filePath).text

            repoList.getProperties().each { String jobName, String repo ->
                String jobXml = configXml.replaceAll("REPOSITORY_URL", repo)
                jenkinsHttp.createOrUpdate(jobName + "-" + jobType, jobXml)
            }
        }
    }
}

To actually update jenkins, I substitute the correct repository in the template XML file and post it to the Jenkins API. The JenkinsHttp class is just a wrapper for HTTP Builder. It checks if a job exists so that it can correctly create or update.

I’m not sure if this is the right way to go about writing a groovy DSL, but it works! And once I’ve finalised the template XML files, Jenkins is going to be a vision of consistency.

  

Continuous Build Woes

I’ve not been happy with our Jenkins continuous build setup for a while. It was fine back in the day when: we had about 3 projects; we were still using Subversion(!); and we didn’t work on branches. We now have lots of projects, we use git, we follow a git flow based branching strategy, and we use Atlassian Stash to review pull requests before merging. I’ve tweaked the Jenkins config a bit, and we wrote a Stash plugin to prompt builds on commit, but it’s become harder and harder to maintain to the point that we’re not really using it, so deploying is never as easy as it should be, and test failures get where they shouldn’t.

Here are my key requirements; I am sure they aren’t that unusual.

1. Job Templates. We have various types of project (Maven, Gradle, Grails) but for each type, the build process is pretty much the same, with a different repository URL. What I want to do is create a template for a certain type of project, and then create jobs using that template. If I want to change the build process, I just change the template and all the jobs should change.

2. Stash Integration. I want to prevent merging to develop or master if there are failing tests.

3. Branch specific configuration. At the moment we have one job for building develop and feature branches, and a second for releasing from master. I’d like all builds associated with a project to be configured in the same place, with some conditional behaviour based on the branch. Feature branches just get compiled and tested. Develop branch also gets deployed to a SNAPSHOT release (maven), or a staging environment (grails). Master branch gets released.

Bamboo Evaluation

We use Atlassian Jira, Confluence and Stash (all of which I think are brilliant!) so my first thought was to move to Bamboo. I spent yesterday setting up an evaluation and trying out a few builds.

First off, it looks great – nice familiar UI, well laid out etc. However, it wasn’t as quick to get started as I’d hoped. Here’s my initial thoughts:

1. Too complicated! Each plan (which equates to a source code repository) can have multiple sequential Stages, containing multiple Jobs (which can run in parallel), each containing sequential Tasks. This might be great if you have a big and complex build process, but all our projects are pretty straightforward, so we’d always be using just the “Default Stage”, leaving lots of navigation with not much in it.

2. No instant Stash Integration. I sort of expected to be provided with some sort of “Add builds from Stash” function, which would automatically set up build triggers from Stash, and successful build notifications back again. But it seems I have to install a plugin on Stash, and for each repository enable it and paste in the notification URL for the correct plan.

3. No templating or hierarchical configuration. Configuring and maintaining lots of identical jobs for different repositories is going to be just as time consuming as in Jenkins.

4. No conditional Branch configuration. Bamboo offers “Branch Plans” so your plan can run on multiple branches (although I couldn’t seem to get it to pick up branches automatically as described). However, there doesn’t seem to be a way to conditionally run stages (or jobs/tasks) based on the branch. So it looks like I’d still have to have separate jobs for building develop (including deploying to staging) and feature branches (test only).

Back to Jenkins

So, I’m a bit disappointed that Bamboo doesn’t seem to solve the main problems I’m facing with Jenkins. It looks like I might as well spend some time working out how to manage Jenkins a little better (as I’m a lot more familiar with it, and we already have it).

I’ve already solved one of the problems, as I’ve found the Jenkins Stash Notifier plugin. And I’m working on some scripts to use the Jenkins rest api to manage jobs. I’ll post an update if I get to a solution I’m happy with!