Quick and Powerful Database with OpenOffice.org and Postgres

Unless you've been living under a rock for the past 3 years, you've heard of OpenOffice.org.  Like most people, I've used the software a few times to edit some documents and haven't really given much thought to it.  During one of my last plane trips, I decided that the small database I keep (in PostgreSQL) needed a facelift, err, well, a face actually.  I built the database completely on the CLI with the postgres 'psql' utility and all my access to it is via the CLI.  This is nice, but I figured that there had to be something easier.  I fired up OO.org and started a 'New Database' just to see what was possible.  

I was immediately beside myself with excitement, if OpenOffice base had the ability to draw forms like MS Access, and will let me hook it up to my existing postgres DB, then I may be done with an interface before the flight deck signals 'initial approach'.

First step was to get the JDBC driver registered with OpenOffice base, and then to get connected to my existing database.  I had a feeling that somewhere along the way, Alice and her long eared friend might be waiting, but I figured that company might be welcome so, what they heck, let's go!

Choosing JDBC from the drop-down and hitting next took me to the 'Database Wizard

My first test, just adding the postgres jdbc driver class 'org.postgresql.Driver' (note the capital 'D') was a failure, so I figured I'd have to get the postgres jdbc driver added to the classpath.

I canceled the wizard, and opened up OpenOffice writer, went to my options page (On the mac, this is 'OpenOffice.org' menu -> Preferences ; everywhere else, it's 'Tools -> Options').  Next, I went to 'java' and clicked the 'Class Path' button, the window that popped up let me add the postgres jdbc jar file without any problems:

I went back to 'base' , and when I tested my driver again, the test was a success!

Now, continuing on our journey, I added the connection string for my database (as per the usual postgres connection string: http://jdbc.postgresql.org/documentation/83/connect.html)

On the next screen, I entered my authentication information (for info on postgres authentication and the pg_hba.conf file, see http://www.postgresql.org/docs/8.3/static/auth-methods.html).  I successfully tested my connection, and on the last screen let OpenOffice register the database for me (aka 'Saved an ODB file) and opened it for editing.

Once opened, I was amazed that not only did OpenOffice.org successfully get my existing tables, it organized them by postgres schema!  I was sure that my somewhat overly organizational nature (i.e. keeping all my tables grouped by postgres schema) would not translate well, but the OO.org team did not let me down, incredible!

So far, I've had some great luck, but my skeptical nature still told me that there would be some problem coming up, and as always, the universe didn't disappoint.  I double clicked one of my tables and was ready to start entering data 'Access style' and found that my tables were all read-only.

I always need that my head-first dive down the rabbit hole would end-up with me staring into the floating eyes of the cheshire cat, I just hoped that it wouldn't be this time.  Oh well.  Needless to say, I didn't meet the self-imposed deadline of 'initial approach', and I actually ended up receiving the 'scorn of the flight attendant' when I did not swiftly stow the laptop until I felt the ka-chunk of the landing gear deploying.

Once on the ground at EWR, I found my way into the Terminal C food court, got my chicken nuggets and whipped out my good 'ol air card.  Once online, my googling for the read-only table fiasco came about with mixed results.  It seemed that the majority of links dealt with some strange issue with the openoffice JDBC system needing to be instructed to 'ignoreDriverPrivileges' (http://dba.openoffice.org/howto/IgnoreDriverPrivileges.html).  None of these magic 'macros' seemed to work.  So, I posted in the OpenOffice forums (http://user.services.openoffice.org/en/forum/viewtopic.php?f=40&t=21036) and within 45 minutes, had my answer, it seems that your external database (here, PostgreSQL) should have primary key on each table in order for it to be updateable via OO.org's base.

I pulled up my psql window and ran:

postgres=# alter table a.bob add primary key (id); NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "bob_pkey" for table "bob" ALTER TABLEI re-opened the a.bob table in OO.org (didn't even have to close the ODB file) and voila!

I was slightly worried that I'd be required to have a surrogate key on each of my tables, which isn't always bad, but in trying to stick with (http://hosting3.epresence.tv/fosslc/1/watch/125.aspx (use the TOC and go to slide 285)), I wanted to make sure that OO.org could deal with multi-column primary keys.  I ran the following through psql (yea, I could be using the designer at this point, but old habits...): create table a.test (name varchar(25), data text); alter table a.test add primary key (name,data); I closed the odb file, re-opened it and sure enough,

it's editable.  Great!  I'm connected

Hopefully the above is enough to get you started on your own chase.  Consider the above part I, I'll eventually get around to a part II where I'll talk about my forms and how I use them.  Happy context switching!