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

Maven/Leiningen dependency details are here: [http://clojars.org/org.bituf/sqlrat](http://clojars.org/org.bituf/sqlrat)

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.

You would need to import the namespace as in this example:

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


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


### Database Configuration

You need to define a database configuration as you would do for
<b>clojure.contrib.sql</b> -- 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 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:

* <i>one-to-many</i> (THAT entity is implicitly dependent upon THIS)
* <i>many-to-one</i>
* <i>one-to-one-depends</i> (THAT entity is dependent upon THIS)
* <i>one-to-one</i>

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


### 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 <i>drop-table</i> and <i>create-table</i> accept the entity meta data as
argument.


### 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 <i>find-by-id</i> and <i>find-by-criteria</i>
functions, which can be used as follows:

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

<i>Warning:</i> Using <i>find-by-criteria</i> is not recommended for retrieving
large result-sets (several hundred rows or more) as it may lead to excessive JVM
heap space consumption.


### Count entities

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

To read the count, you can use the function <i>read-count-col</i>.

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


### Save dependent entities (Update/Insert)

Dependent entities can be saved using the <i>save-deps</i> 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 <i>find-rels</i> function, which
avoids <i>N+1 Selects</i> while querying the database. Counting the relation
entities is as easy as specifying the <i>:cols count-col</i> parameter and read
them back using the <i>read-count-col</i> 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

Sibling entities can be fetched using the <i>find-siblings</i> function. As in
other <i>find-xxx</i> functions, you can pass <i>:cols</i> and <i>:where</i>
arguments and count records as well.

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


### Delete/Delete-cascade entities

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

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


### Arbitrary SELECT queries

Sometimes you may need to execute arbitrary SELECT SQL queries for things like
reporting, extraction, data copying etc. During those times you may either use
<b>SQLRat's</b> <i>db-query</i> function (for small result-sets) or the
<i>with-query-results</i> function from <b>clojure.contrib.sql</b> library.

The <i>with-query-results</i> function lets you pass a body of code that can
transform the result-set and do selective extraction which could be efficient.
If you are expecting large result-sets (several hundred rows or more), you
should use <i>with-query-results</i> simply because the JDBC driver may be
optimized to pull data for ResultSet on on-demand basis, which leads to
conserving JVM heap space.

However, if your result-set is small you can consider using <b>SQLRat's</b>
<i>db-query</i> function that readily returns all rows found in the result-set.
All <i>find-xxx</i> functions in SQLRat use the <i>db-query</i> function.
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.