SQLite from Clojure

I thought this was cute, and it's the entire reason I started this blog a few minutes ago. The first function here is a pretty ditzy port of some Java code to make sure the SQLite JDBC driver is working. The second is some much lispier code that does the same thing.

Here's the Java code (from http://www.zentus.com/sqlitejdbc/)

import java.sql.*;

public class Test {
public static void main(String[] args) throws Exception {
    Class.forName("org.sqlite.JDBC");
    Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
    Statement stat = conn.createStatement();
    stat.executeUpdate("drop table if exists people;");
    stat.executeUpdate("create table people (name, occupation);");
    PreparedStatement prep = conn.prepareStatement(
        "insert into people values (?, ?);");

    prep.setString(1, "Gandhi");
    prep.setString(2, "politics");
    prep.addBatch();
    prep.setString(1, "Turing");
    prep.setString(2, "computers");
    prep.addBatch();
    prep.setString(1, "Wittgenstein");
    prep.setString(2, "smartypants");
    prep.addBatch();

    conn.setAutoCommit(false);
    prep.executeBatch();
    conn.setAutoCommit(true);
    ResultSet rs = stat.executeQuery("select * from people;");      
    while (rs.next()) {
        System.out.println("name = " + rs.getString("name"));
        System.out.println("job = " + rs.getString("occupation"));
    }
    rs.close();
    conn.close();
  }
}

And here's the two Clojure versions of it.

(import '(java.sql DriverManager SQLException))
(use 'clojure.contrib.sql)
(defn direct-port []
(Class/forName "org.sqlite.JDBC")

(let [conn (DriverManager/getConnection "jdbc:sqlite:test.db")
      stat (doto (.createStatement conn)
             (.executeUpdate "drop table if exists people")
             (.executeUpdate "create table people (name, occupation);"))
      prep (doto (.prepareStatement conn "insert into people values (?, ?);")
             (.setString 1 "Gandhi")
             (.setString 2 "politics")
             (.addBatch)
             (.setString 1 "Turing")
             (.setString 2 "computers")
             (.addBatch)
             (.setString 1, "Wittgenstein")
             (.setString 2, "smartypants")
             (.addBatch))]
  (.setAutoCommit conn false)
  (.executeBatch prep)
  (.setAutoCommit conn true)
  (let [results (.executeQuery stat "select * from people")]
    (loop []
      (when (.next results)
        (println "name =" (.getString results "name"))
        (println "job  ="  (.getString results "occupation"))
        (recur))))
  (.close conn)))

(defn contrib-sql []
(with-connection {:classname   "org.sqlite.JDBC"
                  :subprotocol "sqlite"
                  :subname     "test.db"}
  (try
    (drop-table :people)
    (catch SQLException e))
  (create-table :people [:name :text] [:occupation :text])
  (insert-values :people
    [:name          :occupation]
    ["Gandhi"       "politics"]
    ["Turing"       "computers"]
    ["Wittgenstein" "smartypants"])
  (with-results results "select * from people"
    (doseq [record results]
      (println "name =" (:name record))
      (println "job  =" (:occupation record))))))

(println "Java-y version") (direct-port)
(println "Lispy version")  (contrib-sql)

Note that you have to have the SQLite driver installed for these examples to work - you can find some form of it and instructions on how to install it here.

Lazy Lists are awesome

Okay, first off, Clojure's seq abstraction is awesome. This lets you take anything that can be turned into a list-like thing (vectors, Java iterables, maps, whatever) and iterate over it like a list. This means that all the functions that deal with lists (map, filter, so forth) work on everything else. Super cool.

One of the things you can treat like any other seq in Clojure is a lazy list. You can think of this as a generator if you're from C# or Python or such, and you wouldn't be too far off. What it is conceptually is a list in which every element is generated just in time when you ask for it. You give the first item in the list (grabbed by clojure's "first" function) and a function which, when called, returns the rest of the list. The rest of the list is one item and (you guessed it) a function which, when called, returns the rest of the list. Wow, I just felt some deja-vu...

Here's an example - the list of all the fibonnaci numbers. No, really.

(defn make-fibonacci-seq []
  "returns an lazy, infinite seq of the fibonacci numbers"
  ((fn next-fib [one two]
     (lazy-cons one (next-fib two (+ one two))))
    1 1))

(def fibonacci-numbers (make-fibonacci-seq))

(take 10 fibonacci-numbers) => (1 1 2 3 5 8 13 21 34 55)

Yeah. Sweet. lazy-cons is a macro that does the value-and-function magic I mentioned above. Ah, macros. Ah, lisp. Ah, clojure!

FIRST

Omg, first post!