“No SQL” with Liquibase and jOOQ

Not NoSQL but accessing a conventional database with “no SQL”. I don’t hate SQL but I do avoid writing it if possible.  Java and SQL don’t go well together. Large volumes of SQL can become a maintenance nightmare if you’re changing your domain model around.

Yes, I know. The model should hardly change as it should have been perfectly architected from the very beginning of the project……..don’t get me started.

I have always used Hibernate/iBatis in conjunction with Liquibase but wanted to try something else. Even the infallible Hibernate can become a little tedious sometimes. My friend Google led me to jOOQ. It’s a fully-featured database framework with all the bells and whistles you can think of. Any database architect will appreciate its SQL-centric approach.

I had a whirl with it’s code generation feature and was impressed. With Liquibase versioning the database and jOOQ reverse engineering it to generate Java objects I felt assured I had a solid, fault-free build (as far as mapping Java objects to database tables goes). One minor niggle is that if you’re using Maven you will find that the jooq-codegen-maven plugin will run at the generate-sources phase whereas the liquibase-maven-plugin normally runs at process-resources. The problem being that we want to manipulate the database before reverse engineering it.

This is easily fixed by changing the liquibase-maven-plugin phase to generate-sources and then adding a concise [cough] bit of Maven to copy the required Liquibase files into the target directory (earlier than it would normally):


How to create your own (gas) smart meter

Smart meters are all the rage these days so I thought I would have some fun turning my gas meter into one. As my gas bill is three times as much as my electricity bill this might even have some use (haven’t found much use for my electricity smart meter yet).

Firstly, set up a cheap webcam to look at the meter. Every expense was spared with a £30 Tenvis camera:7894

The quality isn’t that high but it should be good enough for some basic text recognition. Note that I had to carefully position the camera to reduce glare from the IR LEDs reflecting off the meter’s glass. I also increased the contrast on the camera as this would make feature extraction more reliable.

Now all I had to do is run the image through some OCR software and read the characters. Simple, right?…..well….sort of. Although this is a controlled environment I don’t know of any software that will reliably extract text from this image. There were a number of OCR APIs at my disposable (Tesseract, Java OCR) but I first had to simplify this complex scene. I did have the advantage that the meter is under the stairs so the image would not be affected by any lighting changes.

How to extract just the digits from the image? I could have just manually defined a boundary area on the image around the digits. But this didn’t seem particularly robust (if the camera moved position, say) and I was keen to use some computer vision cleverness.

I thought it was most probably a good idea to deskew the image first before running any feature extraction algorithm. An ImageDeskew class (found in Tess4J) provided some Hough transformation goodness. This does rely on your image having some distinct horizontal/veritcal lines in it.deskew

Onto feature extraction, I came across the BoofCV library which had some good examples and got me thinking about the best way to extract the characters from the image. I chose to use its binary segmentation capability to try and find areas of interest in the image – http://boofcv.org/index.php?title=Applet_Binary_Segmentation

I ran the binary image extractor using code taken from this example.contours

The white line represents a bounding region and the red lines internal bounds inside the outer one. From this image you can see that 2 of the characters are part of the outer boundary and 2 are part of the inner (plus some other erroneous regions). But these were not the only contours found in the image so how did I classify this image to be the one of interest? Luckily, the other detected contours were completely implausible and had far too few or far too many internal boundaries.

So taking the maximum area of the outer boundary we can extract a pretty good image of just the digits.


Not perfect but hopefully good enough for Java OCR.

I assumed an OCR extractor would want black text on a white background so I created a negative of the image:


Then it was just a matter of running the OCRScanner‘s scan method (with the necessary training images added of course). Java OCR does have a character extractor but I created my training images manually with IrfanView:


These steps might have come across as being pretty straightforward but it did, in truth, require a bit of code tweaking to make it work for my particular setup (especially to Java OCR). In Hindsight manually specifying the region on the image where the digits are would most probably be more reliable/straightforward.

And what is the current reading? I created a RESTful service to get the webcam image, run the OCR and then provide a JSON result. So assuming my service is able to connect to the web camera at my house and that OCR recognition is working correctly you should get an image and corresponding reading below:

It’s a kinda magic!

Now all I need to do is run the stored values through a graph drawing framework (D3!) and see if I can find anything interesting. Comparing it to inside/outside air temperature might be a good start.