SQLRat uses the concept of
- database entity (every entity of same type must have the same meta data)
- entity meta data (name, ID column and other details - described in the "Defining the Entity Meta Data" section below)
- relation meta data (links to entity meta data - described in the "Defining the Relation Meta Data" section below)
You need to define a database configuration as you would do for clojure.contrib.sql -- the following example is for MySQL:
(ns example.blog (:use org.bituf.clj-dbcp)) ; using Clj-DBCP to create data source (def db-mysql (db-spec (mysql-datasource "localhost" "sqlrat" "root" "")))
Defining the Entities
Defining entities is as simple as defining a data type (defrecord):
(defrecord BlogEntry ) (defrecord EntryComment )
Defining the entities as parameter-less lets you reuse them for SQL statements involving variable number of columns and count queries.
Defining the Entity Meta Data
Next comes defining the meta data for entities, which includes
- :name - Entity name
- :id - ID column
- :from-row-fn - Function that converts a row (map) to entity (record)
and optional fields
- :cols - Column definitions (for the create-table function)
- :to-row-fn - Function that converts entity (record) to row (map)
(def blog-entry-meta (entity-meta :entry :autoid (from-row BlogEntry.) :cols [[:autoid :int "NOT NULL PRIMARY KEY AUTO_INCREMENT"] [:title "varchar(30)" "NOT NULL"] [:content "varchar(500)" "NOT NULL"] [:whenposted "DATETIME" "NOT NULL"] [:isdeleted "BOOLEAN" "NOT NULL DEFAULT false"]] )) (def entry-comment-meta (entity-meta :comment :autoid (from-row EntryComment.) :cols [[:autoid :int "NOT NULL PRIMARY KEY AUTO_INCREMENT"] [:entryid :int "NOT NULL"] [:content "varchar(500)" "NOT NULL"] [:name "varchar(50)" "NOT NULL"] [:email "varchar(50)" "NOT NULL"] [:url "varchar(100)"] [:whenposted "DATETIME" "NOT NULL"] [:isdeleted "BOOLEAN" "NOT NULL DEFAULT false"]] ))
If you intend to work on a created/populated database, you need not supply the :cols field. 'entity-meta' is a function that lets you easily build meta data for an entity.
Defining the Relation Meta Data
A blog entry has many comments, which translates into ENTRY table having a 1-to-many relation with the COMMENT table. Conversely the COMMENT table has many-to-1 relation with the ENTRY table.
These shortcut functions let you define a relation each:
- one-to-many (THAT entity is implicitly dependent upon THIS)
- one-to-one-depends (THAT entity is dependent upon THIS)
and each of them take the following parameters (where THIS means the current entity-type/table and THAT means the related entity-type/table):
- this-col (column in THIS entity)
- that-ent-meta (THAT entity meta data)
- that-col (column in THAT entity)
Associate the entity data type with Entity meta data and Relation meta data
This example shows how to do just that:
(extend-entity BlogEntry blog-entry-meta [(one-to-many :autoid entry-comment-meta :entryid)] ) (extend-entity EntryComment entry-comment-meta [(many-to-one :entryid blog-entry-meta :autoid)] )
An entity type is associated with exactly one entity meta data, and zero or more elements of relation meta data.