Salmon Run: Writing a Clojure DSL for SQLUnit

Last week, at our monthly eBig Java SIG meeting, Anne Ogborne spoke to us about her experiences using Clojure as a Second Life programmer. In the course of the talk, some one asked her why and when a typical Java programmer would use Clojure - unfortunately, as expected, her answer and the ensuing discussion eventually devolved into a bit of a language flame fest.

As it turns out, I have been debating this question myself to decide whether learning Clojure was worth spending time on. Clojure is a dialect of Lisp, a general purpose programming language that runs on the JVM, and can access Java classes loaded in the JVM, so it can do whatever I can do with Java and/or Lisp. However, as someone who mainly does Java and is fairly happy with it, I do not see myself doing in Clojure things I can already do easily in Java (or Jython/Python).

However, I think there is (at least) one area that Clojure is fantastic for - as a language for writing DSLs. Pragmatic Java programmers typically write their DSLs as a XML or JSON dialect, using one of the many XML/JSON parsers available in the Java world. Some examples that come to mind are Ant's build.xml and Spring's applicationContext.xml. More adventurous ones (or those working in situations where XML/JSON is not an option) would use something like JavaCC or AntLR to build a language and its parser from scratch, for example the Lucene query language.

With Clojure, you can write your DSL as as extension of Clojure. So all you do is implement (in Clojure or Clojure/Java) the bits that are specific to your DSL - things like control flow and variable declaration, assignments, etc. can be left to Clojure. Because of Clojure's (and Lisp's) homoiconic nature, functions that you provide are represented the same as functions provided by Clojure. The DSL user would basically write the DSLs in a "dialect" of Clojure that includes these extra functions.

SQLUnit in Clojure

A few years ago, I built a little tool that allowed developers to unit test stored procedures. Tests were specified as a stored procedure call and expected results in an XML dialect (see here for an example), and the tool would execute the procedure, and match the result with the one provided. Different styles of matching were provided, such as exact equality, row count equality, failure, etc. However, because it grew organically as a result of user feedback, and because of my own lack of design foresight, it eventually became really complex, and began to suck up almost all my free time, so I handed the project off to someone else and moved on to more interesting things.

The first draft of my Clojure version of the XML example is shown below. If you compare the XML with this version, you will see that there is a 1:1 correspondence between the two files. However, this is written in a real programming language, meaning that the DSL user has much more flexibility. For example, if you now wanted to compare the results from the same stored procedures from two different databases (an actual request which required a change to SQLUnit), then you could simply define another Connection object and replace the fourth argument to run-test with another run-select instead of from-string.

; Source: src/sqlunit/pgsql-test.clj
(ns sqlunit.pgsql-test
  (:import
    (java.sql Types)
  )
  (:use [sqlunit.core])
)

(defn main
  "This is the main test program that will be provided by the caller"
  []
  (def conn (
    connect-to-db "org.postgresql.Driver"
      "jdbc:postgresql://localhost:5432/sqlunitdb"
      "sujit" "")
  )
  (setup-suite conn (to-array [
    "delete from timecard where 1=1"
    "delete from employee where 1=1"
    "delete from department where 1=1"])
  )
  (run-test "Adding department HR"
    assert-results-equal?
    (run-select conn "select AddDept(?)"
      (struct sql-arg 1 "Human Resources" Types/VARCHAR :in))
    (from-string
      "[[{:idx 1 :name \"adddept\" :value \"$deptId_HR\"}]]")
  )
  (run-test "Adding department Infotech"
    assert-results-equal?
    (run-select conn "select AddDept(?)"
      (struct sql-arg 1 "Information Technology" Types/VARCHAR :in))
    (from-string
      "[[{:idx 1 :name \"adddept\" :value \"$deptId_IT\"}]]")
  )
  (run-test "Adding Employee John Doe to InfoTech"
    assert-results-equal?
    (run-select conn "select AddEmployee(?,?,?,?,?,?,?)"
      (struct sql-arg 1 "Information Technology" Types/VARCHAR :in)
      (struct sql-arg 2 "John Doe" Types/VARCHAR :in)
      (struct sql-arg 3 "1900-01-01" Types/DATE :in)
      (struct sql-arg 4 "123456789" Types/VARCHAR :in)
      (struct sql-arg 5 40.00 Types/NUMERIC :in)
      (struct sql-arg 6 10.00 Types/FLOAT :in)
      (struct sql-arg 7 20.00 Types/FLOAT :in))
    (from-string
      "[[{:idx 1 :name \"addemployee\" :value \"$empId_John\"}]]")
  )
  (run-test "Adding Employee John Doe again (expect exception)"
    assert-fail?
    (run-select conn "select AddEmployee(?,?,?,?,?,?,?)"
      (struct sql-arg 1 "Information Technology" Types/VARCHAR :in)
      (struct sql-arg 2 "John Doe" Types/VARCHAR :in)
      (struct sql-arg 3 "1900-01-01" Types/DATE :in)
      (struct sql-arg 4 "123456789" Types/VARCHAR :in)
      (struct sql-arg 5 40.00 Types/NUMERIC :in)
      (struct sql-arg 6 10.00 Types/FLOAT :in)
      (struct sql-arg 7 20.00 Types/FLOAT :in))
    (from-string "[[{}]]") ; we ignore this
  )
  (run-test "Adding Employee Jane Doe to HR"
    assert-results-equal?
    (run-select conn "select AddEmployee(?,?,?,?,?,?,?)"
      (struct sql-arg 1 "Human Resources" Types/VARCHAR :in)
      (struct sql-arg 2 "Jane Doe" Types/VARCHAR :in)
      (struct sql-arg 3 "1910-01-01" Types/DATE :in)
      (struct sql-arg 4 "143456789" Types/VARCHAR :in)
      (struct sql-arg 5 45.00 Types/NUMERIC :in)
      (struct sql-arg 6 10.0 Types/FLOAT :in)
      (struct sql-arg 7 20.0 Types/FLOAT :in))
    (from-string
      "[[{:idx 1 :name \"addemployee\" :value \"$empId_Jane\"}]]")
  )
  (run-test "Adding Employee Dick Tracy to InfoTech"
    assert-results-equal?
    (run-select conn "select AddEmployee(?,?,?,?,?,?,?)"
      (struct sql-arg 1 "Information Technology" Types/VARCHAR :in)
      (struct sql-arg 2 "Dick Tracy" Types/VARCHAR :in)
      (struct sql-arg 3 "1920-01-01" Types/DATE :in)
      (struct sql-arg 4 "144456789" Types/VARCHAR :in)
      (struct sql-arg 5 50.00 Types/NUMERIC :in)
      (struct sql-arg 6 10.0 Types/FLOAT :in)
      (struct sql-arg 7 20.0 Types/FLOAT :in))
    (from-string
      "[[{:idx 1 :name \"addemployee\" :value \"$empId_Dick\"}]]")
  )
  (run-test "Updating Hourly Rate for John"
    assert-results-equal?
    (run-select conn "select UpdateRate(?,?)"
      (struct sql-arg 1 "John Doe" Types/VARCHAR :in)
      (struct sql-arg 2 55.00 Types/NUMERIC :in))
    (from-string
      "[[{:idx 1 :name \"updaterate\" :value 40.00}]]")
  )
  (run-test "Looking up John Doe by name"
    assert-results-equal?
    (run-select conn "select * from GetEmployeeByName(?)"
      (struct sql-arg 1 "John Doe" Types/VARCHAR :in))
    (from-string
      "[[{:idx 1 :name \"emp_id\" :value \"$empId_John\"}
         {:idx 2 :name \"emp_name\" :value \"John Doe\"}
         {:idx 3 :name \"dept_name\" :value \"Information Technology\"}
         {:idx 4 :name \"emp_dob\" :value \"1900-01-01\"}
         {:idx 5 :name \"emp_ssn\" :value \"123456789\"}
         {:idx 6 :name \"rate_per_hour\" :value 55.00}]]")
  )
  (run-test "Looking up all employees in InfoTech"
    assert-results-equal?
    (run-select conn "select * from GetAllEmpForDept(?)"
      (struct sql-arg 1 "Information Technology" Types/VARCHAR :in))
    (from-string
      "[[{:idx 1 :name \"dept_id\" :value \"$deptId_IT\"}
         {:idx 2 :name \"emp_id\" :value \"$empId_John\"}
         {:idx 3 :name \"emp_name\" :value \"John Doe\"}
         {:idx 4 :name \"emp_dob\" :value \"1900-01-01\"}
         {:idx 5 :name \"emp_ssn\" :value \"123456789\"}
         {:idx 6 :name \"rate_per_hour\" :value 55.00}
         {:idx 7 :name \"location\" :value \"(10.0,20.0)\"}]
        [{:idx 1 :name \"dept_id\" :value \"$deptId_IT\"}
         {:idx 2 :name \"emp_id\" :value \"$empId_Dick\"}
         {:idx 3 :name \"emp_name\" :value \"Dick Tracy\"}
         {:idx 4 :name \"emp_dob\" :value \"1920-01-01\"}
         {:idx 5 :name \"emp_ssn\" :value \"144456789\"}
         {:idx 6 :name \"rate_per_hour\" :value 50.00}
         {:idx 7 :name \"location\" :value \"(10.0,20.0)\"}]]")
  )
  (run-test "Adding timecard for John"
    assert-results-equal?
    (run-select conn "select AddTimecard(?,?,?,?)"
      (struct sql-arg 1 "John Doe" Types/VARCHAR :in)
      (struct sql-arg 2 "2000-01-01" Types/DATE :in)
      (struct sql-arg 3 "09:00:00" Types/VARCHAR :in)
      (struct sql-arg 4 "17:00:00" Types/VARCHAR :in))
    (from-string
      "[[{:idx 1 :name \"addtimecard\" :value \"$empId_John\"}]]")
  )
  (run-test "Adding another timecard for John"
    assert-results-equal?
    (run-select conn "select AddTimecard(?,?,?,?)"
      (struct sql-arg 1 "John Doe" Types/VARCHAR :in)
      (struct sql-arg 2 "2000-01-02" Types/DATE :in)
      (struct sql-arg 3 "09:00:00" Types/VARCHAR :in)
      (struct sql-arg 4 "17:00:00" Types/VARCHAR :in))
    (from-string
      "[[{:idx 1 :name \"addtimecard\" :value \"$empId_John\"}]]")
  )
  (run-test "Adding timecard for Dick"
    assert-results-equal?
    (run-select conn "select AddTimecard(?,?,?,?)"
      (struct sql-arg 1 "Dick Tracy" Types/VARCHAR :in)
      (struct sql-arg 2 "2000-01-01" Types/DATE :in)
      (struct sql-arg 3 "10:00:00" Types/VARCHAR :in)
      (struct sql-arg 4 "23:00:00" Types/VARCHAR :in))
    (from-string
      "[[{:idx 1 :name \"addtimecard\" :value \"$empId_Dick\"}]]")
  )
  (run-test "Getting monthly report for InfoTech"
    assert-results-equal?
    (run-select conn "select * from GetMonthlyDeptReport(?,?,?)"
      (struct sql-arg 1 "Information Technology" Types/VARCHAR :in)
      (struct sql-arg 2 "2000-01-01" Types/DATE :in)
      (struct sql-arg 3 "2000-02-01" Types/DATE :in))
    (from-string
      "[[{:idx 1 :name \"dept_name\" :value \"Information Technology\"}
         {:idx 2 :name \"emp_name\" :value \"Dick Tracy\"}
         {:idx 3 :name \"hours_worked\" :value 13}
         {:idx 4 :name \"pay_rate\" :value 50.00}
         {:idx 5 :name \"total_pay\" :value 650.00}]
        [{:idx 1 :name \"dept_name\" :value \"Information Technology\"}
         {:idx 2 :name \"emp_name\" :value \"John Doe\"}
         {:idx 3 :name \"hours_worked\" :value 16}
         {:idx 4 :name \"pay_rate\" :value 55.00}
         {:idx 5 :name \"total_pay\" :value 880.00}]]")
  )
  (teardown-suite conn)
)

(main)

The core.clj file that is imported in the above code via the :use call is shown below. Ideally, this would be a small bit of Clojure glue code that would link up the test with some Java code, but I was trying to learn Clojure at this point, so I decided to do the whole thing in Clojure. I will try the Clojure/Java approach and write about at some point in the future. In any case, here is the Clojure code for a rudimentary version of SQLUnit.

; Source: pgsql-test.clj
(ns sqlunit.core
  (:import
    (java.lang Class String)
    (java.sql DriverManager Connection Statement PreparedStatement
      ResultSet ResultSetMetaData Types SQLException)
  )
)

; Represents a stored procedure IN/OUT/INOUT parameter. The :idx
; is the (1-based) index, :value is the string or numeric value
; of the argument, :sqltype is the Types value and :paramtype is
; :in/:out/:inout.
(defstruct sql-arg :idx :value :sqltype :paramtype)
; Represents a single column in the database. The :idx is the
; (1-based) index, :name is the column name and :value is the
; value of the column. Note that only pure numeric values are
; left unquoted, all others are stringified and quoted.
(defstruct sql-col :idx :name :value)

; global variables
; symbol table
(def *syms* (ref {}))

(defn connect-to-db
  "Create and return a JDBC Connection using the DriverManager approach.
   Param: driver - the class name of the driver to use.
   Param: url - the JDBC url.
   Param: user - the user name.
   Param: password - the JDBC password.
   Return: a reference to a JDBC connection"
  [driver, url, user, password]
  (Class/forName driver)
  (def connection (DriverManager/getConnection url user password))
  connection
)

(defn get-metadata
  "Returns the metadata for the provided ResultSet object as a sequence
   of sql-arg data.
   Param: resultset - the resultset to examine.
   Return: the resultset metadata as a sequence of sql-args"
  [resultset]
  (def rsmd (.getMetaData resultset))
  (def colcount (.getColumnCount rsmd))
  (map #(struct sql-arg % (.getColumnName rsmd %) (.getColumnType rsmd %))
    (range 1 (inc colcount))
  )
)

(defn from-resultset
  "Converts a JDBC ResultSet object to the internal form, ie, a Sequence
   of Sequence of col structs.
   Param: resultset - the JDBC resultset.
   Return: A Seq of Seq of col structs."
  [resultset]
  (def resultmetas (get-metadata resultset))
  (def result [])
  (while (.next resultset)
    (def row [])
    (doseq [resultmeta resultmetas]
      (def col (struct sql-col
        (:idx resultmeta) (:value resultmeta)
        (String/valueOf (.getObject resultset (:idx resultmeta)))))
      (def row (conj row col))
    )
    (def result (conj result row))
  )
  result
)

(defn from-string
  "Converts a user-supplied raw result string to the internal form, ie,
   a Sequence of Sequence of col structs. Non-numeric values in
   the raw string should be quoted, otherwise this method will fail.
   Param: raw-string - the raw string.
   Return: the Seq of Seq of cols."
  [raw-string]
  (load-string raw-string)
)

(defn no-errors?
  "Return true if the current test already has an error, in which case
   calling code will typically short-circuit and return. The method just
   checks for the existence of the :message key in the global symbol table
   *syms*.
   Param: none
   Return: true or false"
  []
  (not (contains? (deref *syms*) :message))
)

(defn is-symbol?
  "Return true if the value is a symbol, ie, preceded by a $ sign.
   Param: value - the value to check.
   Return: true or false"
  [value]
  (.startsWith (str value) "$")
)

(defn assert-cols-equal?
  "Predicate that returns true if the string value of the columns are
   identical. Side effect is that if the LHS (col-1) value is a symbol,
   then the value is looked up from the symbol table. If the RHS (col-2)
   is a symbol, then the value is looked up from the LHS and the RHS
   and symbol table values updated with this value. Additionally, if
   the string values of the columns don't match after this, then an
   error message is written to the symbol table.
   Param: row-id - the row id of the column (0-based)
   Param: col-id - the column id of the column (0-based)
   Param: col-1 - the col struct representing the first column.
   Param: col-2 - the col struct representing the second column.
   Return: true or false"
  [row-id col-id col-1 col-2]
  (doseq [k (keys col-1)]
    ; if one of the values is a symbol, ie of the form $..., then we
    ; just replace one by the other
    (def col-value-1 (get col-1 k))
    (def col-value-2 (get col-2 k))
    (if (is-symbol? col-value-1)
      ; if the symbol is on the LHS, then check to see if it is defined
      ; in the symbol table
      (def col-value-1 (get *syms* col-value-1))
    )
    (if (is-symbol? col-value-2)
      ; if the symbol is on the RHS, then replace the symbol with the
      ; value on the LHS, and update the symbol table
      (do
        (dosync (alter *syms* assoc col-value-2 col-value-1))
        (def col-value-2 col-value-1)
      )
    )
    (def retval (and no-errors? (= (str col-value-1) (str col-value-2))))
    (if (not retval)
      (dosync (alter *syms* assoc :message (format
        "mismatch at (row,col):(%d,%d) for %s (%s != %s)"
        (inc row-id) (inc col-id) k col-value-1 col-value-2)))
    )
    retval
  )
)

(defn assert-rows-equal?
  "Predicate that compares two rows for equality. A row is a Seq of 
   col structs. The function iterates through the columns and
   delegates to the assert-cols-equal? predicate for each of
   them. If there is an error in the previous call, then the test is
   skipped. First checks for equality of number of rows, and if they
   are not equal, writes out the error message to the symbol table.
   Param: row-id - the row id (0-based).
   Param: row-1 - the LHS row Seq of cols.
   Param: row-2 - the RHS row Seq of cols.
   Return: true or false."
  [row-id row-1 row-2]
  (def count-1 (count row-1))
  (def count-2 (count row-2))
  (if (not (= count-1 count-2))
    (dosync (alter *syms* assoc :message (
      format "row count mismatch at row: %d (%d != %d)"
      (inc row-id) (count-1) (count-2)))
    )
  )
  (if no-errors?
    (dotimes [i count-1]
      ; although assert-cols-equal? produces a retval, there
      ; does not seem to be a way of breaking out on a condition, so we
      ; just let it do the full run, and then check for the no-error?
      ; side effect at the end.
      (assert-cols-equal? row-id i (get row-1 i) (get row-2 i))
    )
  )
  no-errors?
)

(defn assert-results-equal?
  "Predicate that compares two results for equality. A result is a Seq of
   rows. The function loops through the rows in the result and delegates
   to the assert-rows-equal? predicate. First checks for equality
   of results, if not equal, writes an error into the symbol table.
   Param: result-1 - the first result.
   Param: result-2 - the second result.
   Return: true or false."
  [result-1 result-2]
  (def count-1 (count result-1))
  (def count-2 (count result-2))
  (if (not (= count-1 count-2))
    (dosync (alter *syms* assoc :message (
      format "result count mismatch (%d != %d)"
      (count-1) (count-2))))
  )
  (if no-errors?
    (dotimes [i count-1]
      (assert-rows-equal? i (get result-1 i) (get result-2 i))
    )
  )
  no-errors?
)

(defn assert-fail?
  "Checks to see if a failure message was logged as a result of the
   run-select function going into the catch block because of an
   exception in running.
   Params: None
   Return: true or false"
  [& junk]
  (contains? (deref *syms*) :failure)
)

(defn run-select
  "Runs a SELECT SQL query. Returns the result, which is the JDBC
   resultset in internal form.
   Param: connection - the JDBC connection.
   Param: sql - the SQL to run.
   Param: & sql-arg - variable number of sql-arg structs.
   Return: result - the JDBC resultset converted to internal format."
  [connection sql & sql-args]
  (def ps (.prepareStatement connection sql))
  (doseq [sql-arg sql-args]
    (.setObject ps (:idx sql-arg) (:value sql-arg) (:sqltype sql-arg))
  )
  (try
    (do
      (def rs (.executeQuery ps))
      (def result (from-resultset rs))
      (.close rs)
      (.close ps)
      result
    )
    (catch SQLException e
      (dosync (alter *syms* assoc :failure (.getErrorCode e)))
    )
  )
)

(defn run-update
  "Runs a SQL INSERT/UPDATE query. This is used here when setting up
   the test, to remove artifacts of previous tests, for example.
   Param: connection - the JDBC connection.
   Param: sql - the INSERT/UPDATE SQL.
   Param: & sql-args - a variable number of sql-arg structs.
   Returns: none"
  [connection sql & sql-args]
  (def ps (.prepareStatement connection sql))
  (doseq [sql-arg sql-args]
    (.setObject ps (:idx sql-arg) (:value sql-arg) (:sqltype sql-arg))
  )
  (.executeUpdate ps)
  (.close ps)
)

(defn setup-suite
  "Run setup commands (SQL UPDATE/INSERT) commands in preparation
   for running a suite of tests.
   Param: connection - the JDBC connection
   Param: sqls a Seq of SQL UPDATE/INSERT calls.
   Return: None."
  [connection sqls]
  (doseq [sql sqls]
    (run-update connection sql)
  )
)

(defn teardown-suite
  "Run teardown commands (usually closing connection, etc) once the
   suite of tests are completed.
   Param: connection - the JDBC connection.
   Return: None."
  [connection]
  (.close connection)
)

(defn run-test
  "The test function is a super-function which the caller calls to
   run the test.
   Param: test-name - the name of the test.
   Param: assertion - the assertion to check.
   Param: result-1 - the LHS result.
   Param: result-2 - the RHS result.
   Return: None."
  [test-name assertion result-1 result-2]
  (print "Running Test:" test-name)
  (def retval (apply assertion [result-1 result-2]))
  (dosync
    (alter *syms* dissoc :message)
    (alter *syms* dissoc :failure)
  )
  (def test-result
    (if retval "Ok"
      (format "Failed\n..Message: %s" (*syms* :message))
  ))
  (println "..." test-result)
)

The indentation may look a bit funky to experienced Lisp/Clojure programmers, but I come from a Java/Python background, and it helps if I line up the parenthesis like I have above, as well as make the introduction and removal of debugging (println "foo") calls less invasive.

Running this test in the REPL produces the following output, which is similar to what SQLUnit returns as well:

user=> 
Running Test: Adding department HR... Ok
Running Test: Adding department Infotech... Ok
Running Test: Adding Employee John Doe to InfoTech... Ok
Running Test: Adding Employee John Doe again (expect exception)... Ok
Running Test: Adding Employee Jane Doe to HR... Ok
Running Test: Adding Employee Dick Tracy to InfoTech... Ok
Running Test: Updating Hourly Rate for John... Ok
Running Test: Looking up John Doe by name... Ok
Running Test: Looking up all employees in InfoTech... Ok
Running Test: Adding timecard for John... Ok
Running Test: Adding another timecard for John... Ok
Running Test: Adding timecard for Dick... Ok
Running Test: Getting monthly report for InfoTech... Ok
nil
user=>

Conclusions

People (including yours truly in the past) often complain about Lisp's prefix notation and all the parenthesis, but I think thats just a function of usage. It took me approximately 4 (4-6 hours per day) days to write the code in this post, and that was starting with zero knowledge of Clojure. Stuart Holloway's Programming Clojure book helped a lot, as did the four part Clojure tutorial (parts 1, 2, 3 and 4) by the author of the Occasionally sane blog. But as a result of this, I now find Clojure code almost as easy to read as Java or Python code.

The point I am trying to make is that you may get pushback along similar lines from the DSL user, but if you point out to him that (a) the problem is temporary and will go away in 3-4 days, (b) the Clojure code is not much different from the XML version, and (c) that he is programming in a real language, so he does not have to depend on you for every little change, he will probably buy into the idea as well. As a DSL writer, you can concentrate on coding only the actual behavior and thus be more productive. I believe that this alone may be a compelling reason for a Java programmer to learn Clojure.

Other information

I used the Netbeans IDE with the enclojure plugin to write the code. Along with the standard paren-matching and syntax highlighting, enclojure offers a built in REPL which is very convenient - I use the Emacs key-binding, so to run the code within the built-in REPL, the keystroke is ALT+L (not CTRL+L as advertised, perhaps for the default key-binding, on the enclojure docs).

I also had a bit of re-learning to do when setting up PostgreSQL for the test. Last I used it was over 3 years (and 1 machine) ago, so I had to install everything from scratch. Here is a quick cheat sheet if you want to do something similar.

Install PostgreSQL: I used yum to install postgresql, postgresql-libs and postgresql-server. This automatically creates a Unix user called "postgres".
Start the server: Run "sudo /etc/init.d/postgresql restart" from the unix prompt. The first time it will ask you to set up the cluster - do that and retry the command. On my machine, the cluster is created under /var/lib/pgsql/data. Note this directory, you will need it later.
Create database: As postgres, run "createdb sqlunit" from the unix prompt. This will create your database.
Create user: As postgres, run "createuser sujit" from the unix prompt. When prompted, give it PostgresSQL super-user privileges.
Create language: Allow sqlunitdb to use plpgsql as a stored procedure language. As postgres, run "createlang plpgsql sqlunitdb" from the unix prompt.
Load schema and procedures: There is a reload.sh that would do this for you. You will have to edit out the "drop table" calls the first time you use it.
Enable JDBC access: You need to add the line in pg_hba.conf (in your /var/lib/pgsql/data directory) as described in the PostgreSQL JDBC FAQ here. The update to postgres.conf described here did not work and turned out to be unnecessary for me. Restart the server after updating pg_hba.conf.