1. Shantanu Kumar
  2. SQLRat

Source

SQLRat / README

-*- 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:
src/test/clj/org/bituf/sqlrat/test/dbblog.clj

Database configuration:
src/test/clj/org/bituf/sqlrat/test/dbconfig.clj


## 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:

* 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.

#### Create and drop tables

#### Save entities (Update/Insert)

#### Save dependent entities (Update/Insert)

#### Retrieve entities

#### Count entities

#### Retrieve relation entities

#### Count relation entities

#### Delete entities

#### Delete cascaded (dependent entities)