Moving your spring-roo hibernate project from Hypersonic to Postgresql

I’ve always been an ORM hater. However, recently the decision to use ORM on a project I am working on was made for me. So I borrowed a pair of my pro-ORM’s colleague’s moccasins so I could walk a few miles in them. To throw me further out of my comfort zone, this project is being done in java, a language I am quite rusty in.

The first thing I learned about ORMs is that at least in java, you don’t pick your database right away. You start off using an in-memory database like Hypersonic until your data model is fleshed out a bit. Our project is not quite there yet, but we wanted to get familiar with the procedure for doing that. I made an attempt to use SQL Server Denali CTP1 as our database. However, that was proving difficult and another colleague suggested I use a database that might be more “java friendly.” I decided to go with my first true love Postgres.

Our ORM was hibernate. We were using the SpringSource Tool Suite or STS. This includes the development tool spring roo. Amongst many other things, roo manages your maven dependencies, and configures your hibernate persistance layer. These are the particular tasks I will concentrate on for this howto.

Installing postgres

How to do this on your particular operating system is beyond the scope of this document. I went with postgres 9.1 beta 1 just because. The lastest version of postgres 8 should be fine as well. In the examples below I am assuming your database server is running on localhost, and the user name and password you use to connect to it are both postgres. Never do this on anything besides your local laptop, and don’t even do that on your laptop if your postgres port (5432 TCP) is opened to the world.

Installing the driver

According to a comment on this stackoverflow answer, You have to use the jdbc3 postgres driver and not the jdbc4 version for hibernate to be able to generate the DDL to make the tables. My experiments have confirmed this. To install it, startup the roo shell. The roo shell can either be started from your operating system shell via roo.sh/roo.bat, or from inside the SpringSource IDE, which is a very polished eclipse distro. If you are starting up the roo shell from a terminal or command prompt, first cd to the folder where your project is located. The command to install the postgres jdbc driver is as follows:

roo> dependency add --groupId postgresql --artifactId postgresql --version 9.0-801.jdbc3
Updated ROOTpom.xml [added dependency postgresql:postgresql:9.0-801.jdbc3]
roo>

This will take care of fetching the dependency and editing the pom.xml for you. If you want to attempt with a different version of the postgres driver, browse the different versions on mvnrepository.

Switching the persistence layer

Persistence layer is hibernate speak for database. This makes sense, because data persists in your database, as opposed to ram, cache, etc which is all meant to be temporary. Switching persistence layers in hibernate requires editing two files, persistence.xml and database.info. Editing these two by hand would violate the don’t repeat yourself rule. Luckily we can edit both with one command in the roo shell.

roo> persistence setup --database POSTGRES --provider HIBERNATE --databaseName myproject --userName postgres --password postgres
Updated ROOTpom.xml [added dependency postgresql:postgresql:8.4-701.jdbc3]
roo>

One thing to be aware of here. The database name needs to be lower case. If you used uppercase characters, your app will not be able to connect to it.

Your persistance.xml file will now look like this:

And your database.properties will look like this:

One manual step

I have not figured out how to get hibernate to execute CREATE DATABASE if the database does not exist. So connect to postgres, and execute the following:

CREATE DATABASE myproject;

Conclusion

Hopefully, all you will need to do is debug your project as a web applicatin and the tables in your application get built. Happy coding!

More Windows command line PATH goodness pathed.exe

Readers of this blog probably think I have an obsession with editing my system path. That belief is absolutely correct. I even added a tag on this blog for the articles about path manipulation. I am a command line junkie who is constantly trying out new tools so I have to add them to my path. I’ve written about doing this from powershell here and here, as well as doing it with setx. While these methods are good, I wanted something better. I got better with pathed.exe.

pathed.exe is a program that lets you edit both your user and the system path. It only manipulates the path, not other environmental variables. The reason for this extreme specialization is that pathed is specifically designed for appending to and removing from the path. It treats the path as a semicolon delimited array, which is of course what it is. For example, I just ran it now on my machine as I was writing this article (note: live coding is less embarrassing when you do it on a blog).

If you notice, their happen to be two copies of the path to mercurial on my path. Well lets fix it right now:

Wasn’t that easy?

UnSQL #4 Lessons learned from PHP

Jen McCrown has decreed this UnSQL Friday theme Speaker Lessons Learned. Well what could be more a more UnSQL blog than a PHP talk that I bombed at NYPHP!

So a little back story. One day at work I discovered a problem in PHP, so I filed a bug report. I eventually fixed the bug, and my fix was included in PHP 5.3.3 and 5.2.14. I figured, “Hey this would be a great topic for a  talk!” So I gave a talk at NYPHP about how to file and fix a bug in PHP. It bombed.

It bombed for a few reasons. So let me turn my bad talk into a hopefully good blog post, about things not to do at a user group talk.

Lesson number one is don’t give an advanced talk at a user group. The problem with advanced talks at user groups is that there is one session and one talk at a user group. Not everyone in the audience is capable of digesting a one hour talk on that particular advanced topic. This is not an insult to anyone’s intelligence, most people at a given user group could eventually be made to understand your topic, most probably lack the prerequisite knowledge. My talk would have gone much better at a multi track PHP conference, because I’d only get people  that cared about my topic.

Lesson number two is giving a talk that involves mastery of an “off topic” language or technology at a user group is a bad idea. Note that I say mastery not knowledge or awareness. PHP is written in C. You need to really grok C (read: be able to clean up your pointers) to contribute to PHP. However, most PHP programmers don’t know C. I even stated in my slides “I cannot make you a C programmer in one night.” I really should have known better. To use another example, I’d be very wary about talking about CLR procs at an SQL user group because most DBAs don’t know C#, or another CLR language. You can’t write a useful CLR proc without mastering .NET. Giving a talk on CLR procs to a .NET user group could work because most .NET programmers have a basic understanding of T-SQL. As another example, you can probably give a beginner level powershell talk to a SQL user group. Its pretty easy to get a DBA from never used powershell before to making ADO.NET calls in an hour, especially if you focus on doing it with SQLPSX.

Lesson number three is be careful about a war story inspired talk. You might come off as just ranting, or even worse, like I did. Some people, like Sean McCrown, can rant well. People clamor to hear people like Sean rant. People don’t like to hear me rant. I can’t deliver a rant in a way that makes my audience feel involved like Sean does. I realized this at the time I was preparing for my talk. I said things like “the PHP SOAP module is a pile of crap,” and “the PHP community ignores people who submit actual patches.” However, I tried to do it in a non-ranty Dale Carnigieish way.  However, all that emotion lurked below the surface during the talk. When I rant people normally see an angry little nerd getting caught up in some stupid detail that doesn’t matter. In this case I spent my talk actively avoiding going into rant mode, like your friends puppy fresh from obedience school that fidgets anxiously in front of you at a slight distance, obviously wanting in his cute little puppy heart of hearts to jump on you and give you a proper puppy greeting. Since I lacked the time to emotionally distance myself from the BS I had to go through to get this bug fixed, my audience saw an little nerd that was angry about something. They were kinda confused.  Lessons learned, if you can rant like Sean, rant away. If you rant like me, make sure you can emotionally distance yourself from the topic at hand.

Could I make this talk work if I tried again? Perhaps, but I am not that interested. I gave a non-technical talk on contributing to MongoDB at MongoDC. (This post is now NoSQL as well as UnSQL. Double Rainbow all the way!!) That was well received, but had a small audience, and one of my audience members felt 10gen wasn’t open source enough. So I guess lesson number four is no one wants to hear a talk about how to contribute to an open source project. If they want to contribute, they will.