Working with SQLite

Neko brings you a set of tools to interact with SQLite databases with neko.data.sqlite namespace. It includes functions to create a database, execute queries against it, insert and update data and seamlessly convert received results into Clojure’s data types and structures.

Tools provided are really subpar to mature SQL wrappers like clojure.java.jdbc or Korma. Currently they are very limited in their capabilities (for example, you can use only one table in SELECT). In future they should be either extended, or some compatibility work should be done to leverage existing SQL wrappers.

Please keep in mind that this feature is in its alpha stage and may be changing a lot until the release.

Define a schema

Schema is an ordinary map that contains a database description, such as what is the name of the file database will be stored in, what tables with what columns does it have and so on.

To create a schema use make-schema function that takes a list of optional arguments passed in key-value fashion (so you just passed a map to it without top-level curly braces).

The following rules apply to a correct schema:

• :name should be a string that represents database file name;
• :version should be a number that represents the version of schema;
• :tables should be a map where keys are table names presented as keywords, and values are also maps;
• each table map should contain a :columns value which should also be a map;
• in a columns map keys are column names presented as keywords, and values are once again maps;
• each column value should have :type value of the following: Integer, Double, String, Boolean, Byte; and a :sql-type string value that will be used when creating the table.

Note that Byte type actually means byte[], which is respective to SQLite’s Blob type.

:sql-type is a string to be inserted after the column name in CREATE statement. So, for example, a column with String type may have "text" or "text not null" sql-type.

Example:

(def schema
  (make-schema
   :name "local.db"
   :version 1
   :tables {:employees
            {:columns {:_id         {:type Integer, :sql-type "integer primary key"}
                       :name        {:type String,  :sql-type "text not null"}
                       :vacation    {:type Boolean, :sql-type "boolean"}
                       :certificate {:type Byte,    :sql-type "blob"}}}}))

Get database

Now that you have a schema you can create a database from it. get-dabase function is exactly what you need. It takes two arguments - a schema and an access mode (either :read or :write). Inside the function a helper object creates tables if they don’t exist yet, recreates them if schema’s and current database’s versions mismatch, and returns a TaggedDatabase instance.

TaggedDatabase is actually an object that keeps SQLiteDatabase instance and a schema. You can get the former by addressing .db field and the latter - with .schema field. You’ll only need to do this when executing Java code directly since all neko.data.sqlite functions work with TaggedDatabase as argument.

Example:

(def db (atom nil))

;; Somewhere in the application code
(reset! db (get-database schema :write))

SQL operations

neko.data.sqlite contains functions to perform INSERT, UPDATE and SELECT queries.

Insert data

You can use db-insert function to INSERT the data into the database. It takes three arguments: a TaggedDatabase, a table name (as stated in schema) and a data map, in which keys are column names and values are actual values to be inserted.

Example:

(db-insert @db :employees {:name "John Doe"
                           :vacation false
                           :certificate (.getBytes "quick brown fox")})

Update data

db-update function is used to run UPDATE queries against the database. It takes four arguments: a TaggedDatabase, a table name, data-map (same as in INSERT) and a WHERE clause. WHERE clause could be a simple SQL string or a map as well where key-value pairs are treated as comparison operations.

For example, {:_id 1, :vacation false} turns into WHERE _id = 1 AND vacation = false.

You can also use a vector as WHERE clause value that has the following form: [op-keyword & values]. Op-keyword will be transformed into a string and interpose the values.

For instance, {:_id [:or 1 2], :name "John Doe"} turns into WHERE (_id = 1 OR _id = 2) AND name = 'John';.

Example:

;; Send employees with IDs 1, 5 and 7 on vacation
(db-update @db :employees
           {:vacation true}
           {:_id [:or 1 5 7]})

Retrieve data

A function called db-query allows you to execute a SELECT query and get the result in a Cursor object (the same behavior as .query method shows). It takes a TaggedDatabase, a table-name and a where clause (same as in db-update).

Example:

(db-query @db :employees {:vacation false})

You may then use the retrieved Cursor for other Android tasks like put it into CursorAdapter. Though if you want to get the real data, you can use db-query-seq which after retrieving the cursor creates a lazy sequence of real data from it. It has the same syntax as db-query.

(db-query-seq @db :employees {:vacation false})

=> ({:_id 5, :name "John Doe", :certificate #<byte[] [B@64dfeb>}
    {:_id 17, :name "Don Joe", :certificate #<byte[] [B@a8c19b>})