1. Shantanu Kumar
  2. SQLRat



-*- markdown -*-

# SQLRat

SQLRat is a Clojure (v1.2 or later) library to access relational
databases using entity objects and to navigate entity relations
in a stateless manner. Easy to use and flexible - you can also
pass in native SQL for accessing the database.

## Usage

FIXME: write Maven/Lein dependency details here

Examples for defining entities/relations and using them can be
found in the unit test cases. There is a bunch of API functions
to work using the entity definitions.

Unit tests:

Database configuration:

## Building/Installation

If you want to build from sources, you may need to setup a database
first. The default configuration points to MySQL database "sqlrat".

You will need Maven 2 to build from sources. Execute the following:

    $ mvn clean package  # packages up a JAR in "target" dir
    $ mvn install        # to install to your local Maven repo
    $ mvn clojure:gendoc # generate Clojure API documentation

## License

Copyright (C) 2010 Shantanu Kumar (kumar.shantanu at gmail dot com)

Distributed under the Apache 2 License.

# Tutorial

SQLRat uses Clojure 1.2.0 and Clojure-contrib 1.2.0 and is built on the top of
clojure.contrib.sql library. In this tutorial we will take the example of a tiny
blog with two tables (ENTRY and COMMENT) and proceed with the various database
use cases.

## Definitions

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 - see meta data section)
* relation meta data (links to entity meta data - see relevant section)

### Database Configuration

You need to define a database configuration as for clojure.contrib.sql -- the
following example is for MySQL: 

<script src="http://gist.github.com/548099.js"> </script>

More examples here [http://en.wikibooks.org/wiki/Clojure_Programming/Examples/JDBC_Examples](http://en.wikibooks.org/wiki/Clojure_Programming/Examples/JDBC_Examples)

### Defining the Entities

Defining entities is as simple as defining a data type (defrecord):

<script src="http://gist.github.com/548153.js"> </script>

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)

<script src="http://gist.github.com/548180.js"> </script>

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 an
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)
* many-to-one
* one-to-one-depends (THAT entity is dependent upon THIS)
* one-to-one

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:

<script src="http://gist.github.com/548250.js"> </script>

An entity type is associated with exactly one entity meta data, and zero or more
sets of relation meta data.

## Functions that operate on entities

There are several functions to carry out different database tasks. They accept
entity, entity meta data and other data as appropriate. The following sections
describe what to use for each use case. To use the SQLRat entities, you need

    (ns example.test
      (:use org.bituf.sqlrat.entity))

### in-db and in-txn wrappers

All database operations either need a database context, or a database
transaction context. Example is below:

    (def db db-mysql)
    (in-db db
      ;; db operations here
    ;; or
    (in-txn db
      ;; transaction operations here

### Create and drop tables

This is how you can drop and create tables for blog entries and comments:

<script src="http://gist.github.com/550393.js"> </script>

Both drop-table and create-table accept the entity meta data as arguments.

### Pretty-printing entities

This is how you can pretty print the entities:

<script src="http://gist.github.com/550420.js"> </script>

The output should look like this:

    Entity 1:
    title | content     | whenposted                  
    ----- | ----------- | ----------------------------
    Test  | Hello World | Thu Aug 26 03:57:59 IST 2010
    Entity 2:
    title       | content       | whenposted                  
    ----------- | ------------- | ----------------------------
    Second post | Amarnath Trip | Thu Aug 26 03:57:59 IST 2010
    Entities 1 and 2:
    title       | content       | whenposted                  
    ----------- | ------------- | ----------------------------
    Test        | Hello World   | Thu Aug 26 03:57:59 IST 2010
    Second post | Amarnath Trip | Thu Aug 26 03:57:59 IST 2010

### Save entities (Update/Insert)

The example of saving a blog entry is as follows:

<script src="http://gist.github.com/550433.js"> </script>

The important thing to note is that the generated ID on first save is returned
with the entity, which you can verify from the pretty-print output.

### Retrieve entities

Retrieving entities involves find-by-id and find-by-criteria functions, which
can be used as follows:

<script src="http://gist.github.com/558419.js"> </script>

### Count entities

Retrieving count of records is same as using the find-by-criteria function, but
specifying "count-col" as the chosen column.

To read the count, you can use the function read-count-col.

<script src="http://gist.github.com/558428.js"> </script>

### Save dependent entities (Update/Insert)

Dependent entities can be saved using the save-deps function, where it
automatically populates the foreign key in the dependent records.

<script src="http://gist.github.com/558449.js"> </script>

### Retrieve/Count relation entities

Related entities can be fetched using the find-rels function, which avoids "N+1
Selects" while querying the database. Counting the relation entities is as easy
as specifying the ":cols count-col" parameter and read them back using the
read-count-col function. The count operation automatically adds GROUP BY clause
to the SQL.

<script src="http://gist.github.com/558467.js"> </script>

### Retrieve/Count sibling entities

Singling entities can be fetches using the find-siblings function. As in other
find-** functions, you can pass :cols and :where arguments and count records as

<script src="http://gist.github.com/558488.js"> </script>

### Delete/Delete-cascade entities

The delete function is used to delete an entity. Similarly, the delete-cascade
function is used to delete the entire parent-child tree of entities.

<script src="http://gist.github.com/558500.js"> </script>