Published on
I was interacting with a SQL database using Clojure and needed to create a table so I turned to create-table
from clojure.contrib.sql. Looking at the docs for create-table
it seemed pretty straight forward. To create a table with columns date, id, symbol, price, and quantity you would write the following.
(create-table "orders"
[:date "date"]
[:id "integer"]
[:symbol "char(10)"]
[:price "integer"]
[:quantity "integer"])
The above works. I also wanted to specify that columns date and id to form a composite primary key. I wasn't sure how to specify a composite primary key with create-table
and ended up diving into its code.
(defn create-table
"Creates a table on the open database connection given a table name and
specs. Each spec is either a column spec: a vector containing a column
name and optionally a type and other constraints, or a table-level
constraint: a vector containing words that express the constraint. All
words used to describe the table may be supplied as strings or keywords."
[name & specs]
(do-commands
(format "CREATE TABLE %s (%s)"
(as-str name)
(apply str
(map as-str
(apply concat
(interpose [", "]
(map (partial interpose " ") specs))))))))
Looking at create-table
we can see it creates a SQL statement which is then executed by do-commands
. In order to have a composite key we need do-commands
to execute a SQL statement that looks similar to below.
CREATE TABLE track(
date date,
id integer,
symbol char(10),
price integer,
quantity integer,
PRIMARY KEY (date, id)
)
Let's break down create-table
to figure out what we need to pass it to make do-commands
run the above statement. The code for create-table
is repeated below with comments pointing out what step lines up the code.
(defn create-table
[name & specs]
(do-commands ; step 7
(format "CREATE TABLE %s (%s)" ; step 6
(as-str name)
(apply str ; step 5
(map as-str ; step 4
(apply concat ; step 3
(interpose [", "] ; step 2
(map (partial interpose " ") specs)))))))) ; step 1
create-table
takes the sequences in specs
and puts a space between each element in each sequence.concat
combined with apply
is used to combine each element of the result of step 2 into a single sequence.as-str
(from c.c.string) is mapped over the result of step 3 to make sure every element is a string.str
is used to make one string out of the sequence of strings from step 4.format
is used to substitute in name
and the result of step 5 to create the SQL statement.do-commands
executes the statement created in step 6.Knowing how create-table
works now allows us to specify the arguments that will create the orders table with the composite primary key of date and id.
(create-table "orders"
[:date "date"]
[:id "integer"]
[:symbol "char(10)"]
[:price "integer"]
[:quantity "integer"]
["PRIMARY KEY" "(date, id)")