SQLite, JDBC, Clojure, and Booleans

Most RDBMS' do not support a native Boolean type; Postgres is one of the few that actually does, allowing the programmer use a native Boolean type in both their application code and database code. Oracle, and many others, does not at all, and so most programmers resort to some sort of convention whereby the set of values of a Boolean-like column is restricted to 1 or 0, or 'T' or 'F', or 'Y', or 'N'.

SQLite is in a third category; it does support a Boolean type but the boolean keyword is actually a synonym for the tinyint type, and the values are actually managed in the database as 1 for true, and 0 for false. Moreover, when you query the database, you only get back out the tinyint representations. This complicates matters if you wish to take advantage of the boolean type in the database and in your application code without having to constantly transform values both in and out of it.

Here's an example; let's create a brand new database:

daniellek@Jumpclouds-MacBook-Pro ~/p/sqlite-test> sqlite3 test.db
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite>

... and then create a table with a boolean type:

sqlite> create table fabulous
   ...>        (name varchar,
   ...>         is_fabulous boolean);

... and then create some data:

sqlite> insert into fabulous
   ...> values ('Danielle', 1);
sqlite> insert into fabulous
   ...> values ('JB', 0);

... and finally retrieve what we just put in:

sqlite> select * from fabulous;
Danielle|1
JB|0

We're burdened above by having to use tinyint representations of boolean values even when inserting them at the database level. Let's see what happens in a Clojure REPL. Let's use the following project.clj:

(defproject sqlite-test "0.1.0-SNAPSHOT"
  :description "FIXME: write description"
  :url "http://example.com/FIXME"
  :license {:name "Eclipse Public License"
            :url "http://www.eclipse.org/legal/epl-v10.html"}
  :dependencies [[org.clojure/clojure "1.9.0"]
                 [org.clojure/java.jdbc "0.7.8"]
                 [org.xerial/sqlite-jdbc "3.23.1"]])

... and then start a REPL:

daniellek@Jumpclouds-MacBook-Pro ~/p/sqlite-test> lein repl
nREPL server started on port 55855 on host 127.0.0.1 - nrepl://127.0.0.1:55855
REPL-y 0.3.7, nREPL 0.2.12
Clojure 1.9.0
Java HotSpot(TM) 64-Bit Server VM 1.8.0_74-b02
    Docs: (doc function-name-here)
          (find-doc "part-of-name-here")
  Source: (source function-name-here)
 Javadoc: (javadoc java-object-or-class-here)
    Exit: Control+D or (exit) or (quit)
 Results: Stored in vars *1, *2, *3, an exception in *e

user=>

First we need to require the namespace that allows us to easily query the database:

user=> (require '[clojure.java.jdbc :as jdbc])
nil

... then we need to define a so-called database spec to describe the data source we want to use:

user=> (def db-spec {:dbtype "sqlite" :dbname "test.db"})
#'user/db-spec

... and then issue a query:

({:name "Danielle", :is_fabulous 1} {:name "JB", :is_fabulous 0})

This wasn't too surprising as we got back integers even within the SQLite prompt. But why is this? Shouldn't either clojure.java.jdbc or the SQLite driver (org.xerial/sqlite-jdbc) take care of this? This turns out to require a very labyrinthine explanation.

So, we chose to use clojure.java.jdbc/query; it's a convenience method to execute a query for a given database spec and SQL parameters. If we inspect the call chain to see what goes on inside, this is what happens: ¹

query calls a private method, db-query-with-resultset*, with process-result-set, a reference to another method, as one of its parameters
db-query-with-resultset* then eventually calls execute-query-with-params, with process-result-set unmodified
execute-query-with-params then calls the database driver's implementation of java.sql.Statement/executeQuery to produce a ResultSet...
... and then with that ResultSet open, processes it, finally using the function threaded through from query, process-result-set
process-result-set does what it says, taking a set of options and calling result-set-seq
result-set-seq's job is to produce a lazy sequence of Clojure maps representing the result set, and among several other things, defers to dft-read-columns to actually read the data from the database
in order to do that, dft-read-columns calls .getObject for each column in the ResultSet

And here's where things get interesting. If we look at the actual implementation in the SQLite driver for getObject, we see this: ²

    public Object getObject(int col) throws SQLException {
        switch (getDatabase().column_type(stmt.pointer, markCol(col))) {
        case SQLITE_INTEGER:
            long val = getLong(col);
            if (val > Integer.MAX_VALUE || val < Integer.MIN_VALUE) {
                return new Long(val);
            }
            else {
                return new Integer((int) val);
            }
        case SQLITE_FLOAT:
            return new Double(getDouble(col));
        case SQLITE_BLOB:
            return getBytes(col);
        case SQLITE_NULL:
            return null;
        case SQLITE_TEXT:
        default:
            return getString(col);
        }
    }

So, if the raw column type from the database is a SQLITE_INTEGER, we'll either get back a Java Integer or Long. Notice that there is no branch to handle the SQLlite boolean case.

Returning all the way back to the call from dft-read-columns, we see this:

(defn- dft-read-columns
  "Default implementation of reading row values from result set, given the
  result set metadata and the indices."
  [^ResultSet rs rsmeta idxs]
  (mapv (fn [^Integer i] (result-set-read-column (.getObject rs i) rsmeta i)) idxs))

We now know that getObject for SQLite's boolean columns is going to return either an Integer or a Long. If we look just above the method, we will see that result-set-read-column is part of a protocol extension:

(extend-protocol IResultSetReadColumn
  Object
  (result-set-read-column [x _2 _3] x)

  Boolean
  (result-set-read-column [x _2 _3] (if (= true x) true false))

  nil
  (result-set-read-column [_1 _2 _3] nil))

AHA! Even though there is actually handling in this protocol for a Java Boolean type, it's never used because we never get one from getObject, which is what is used as the dispatch type for this protocol. Soooooooo... what happens if we extend Integer in the following way?

(extend-protocol jdbc/IResultSetReadColumn
  Integer
  (result-set-read-column [val resultset-metadata idx]
    (if (= "BOOLEAN" (.getColumnTypeName resultset-metadata idx))
      (if (= val 1) true false)
      val)))

... and then rerun the query:

user=> (jdbc/query db-spec ["select * from fabulous"])
({:name "Danielle", :is_fabulous true} {:name "JB", :is_fabulous false})

ZOMG IT WORKS!!!!

I can't decide if this is a bug with clojure.java.jdbc in that is only relies on getObject, or in the SQLite driver in that it doesn't actually fully expose all of the possibilites. Nonetheless, this was something that I needed a workaround for at $DAY_JOB and this works.

¹ Source for clojure.java.jdbc: https://github.com/clojure/java.jdbc/blob/master/src/main/clojure/clojure/java/jdbc.clj
² Source for org.sqlite.jdbc3.JDBC3ResultSet: https://github.com/xerial/sqlite-jdbc/blob/master/src/main/java/org/sqlite/jdbc3/JDBC3ResultSet.java

quephird/sqlite_jdbc_clojure_and_booleans.md

SQLite, JDBC, Clojure, and Booleans