1. Shantanu Kumar
  2. SQLRat

Wiki

Clone wiki

SQLRat / EntityFunctions

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:

(in-db db
  (println "** Dropping tables **")
  (try
    (drop-table blog-entry-meta)
    (catch Exception e
      (println (str "Error dropping table " (:name blog-entry-meta) ": "
                 (.getMessage e) " [Ignored]"))))
  (try
    (drop-table entry-comment-meta)
    (catch Exception e
      (println (str "Error dropping table " (:name entry-comment-meta) ": "
                 (.getMessage e) " [Ignored]"))))
  (println "** Creating tables **")
  (create-table entry-comment-meta)
  (create-table blog-entry-meta))

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

Pretty-printing entities

This is how you can pretty print the entities:

(defn ppe ;; pretty-print-entities
  ([e]
    (print-entities e))
  ([label e]
    (println label)
    (print-entities e)))

(let [e1 (BlogEntry. {}
           {:title "Test"
            :content "Hello World"
            :whenposted (new java.util.Date)} )
      e2 (BlogEntry. {}
           {:title "Second post"
            :content "Amarnath Trip"
            :whenposted (new java.util.Date)} )]
  (ppe "Entity 1:" e1)
  (ppe "Entity 2:" e2)
  (ppe "Entities 1 and 2:" [e1 e2]))

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:

(in-txn db
  (let [saved (save (BlogEntry. {}
                      {:title "Test"
                       :content "Hello World"
                       :whenposted (new java.util.Date)} ))]
    (ppe "Saved row #1" saved)
    (let [saved-again (save (assoc saved :title "Test Updated"))]
      (ppe "Saved again (updated) row #1" saved-again))))

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 evicted entities

Evicted entities are dissociated from the underlying result set, hence can be easily passed around. Retrieving evicted entities involves find-by-id and find-by-criteria functions, which can be used as follows:

(in-db db
  (let [e1 (find-by-id blog-entry-meta 1)
        ea (find-by-criteria blog-entry-meta) ;; finds all records
        ec (find-by-criteria blog-entry-meta {:cols [:title :content]})
        ew (find-by-criteria blog-entry-meta {:where (as-clause ["title=?" "Hello World"] )})
        eb (find-by-criteria blog-entry-meta
             {:cols [:title :content] :where (as-clause ["title LIKE ?" "Hello%"] )})]
    (ppe "Entry 1:" e1)
    (ppe "All Entries:" ea)
    (ppe "Entries with selected columns:" ec)
    (ppe "Entries with WHERE clause:" ew)
    (ppe "Entries with selected columns and WHERE clause:" eb)))

Warning: Unless the first argument to find-by-criteria is a function (not included in this tutorial, see function reference), using find-by-criteria is not recommended for retrieving large result-sets (several hundred rows or more) as it may lead to excessive JVM heap space consumption. Instead you can use with-find-by-criteria-results macro (see section).

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 <i>read-count-col</i>.

(in-db db
  (let [ea (find-by-criteria blog-entry-meta {:cols [count-col]})
        ew (find-by-criteria blog-entry-meta {:cols [count-col] :where (as-clause ["title=?" "Hello World"] )} )]
    (println "All records:" (read-count-col ea))
    (println "Records with WHERE clause:" (read-count-col ew)))

Retrieve non-evicted entities

Non-evicted entities are still associated with the underlying result-set. As a consequence, (1) you must not return something that processes them lazily and (2) you can freely access a large result set where the JDBC driver fetches rows on on-demand basis.

(in-db db
  (with-find-by-criteria-results entities blog-entry-meta {}
    (println entities))
  (with-find-by-criteria-results entities blog-entry-meta {:where (as-clause ["id IN (?, ?)" 1 2] )}
    (println entities)))

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.

(in-txn db
  (let [e (find-by-id blog-entry-meta 1)
        c (EntryComment. {}
            {:content "Comment #1"
             :whenposted (new java.util.Date)
             :name "Whatever"
             :email "no@spam.com"} )
        saved (save-deps e [c])]
    (ppe "Saved 1 child for entry (ID=1)" saved))
  (let [e (find-by-id blog-entry-meta 2)
        c1 (EntryComment. {}
             {:content "Comment #2"
              :whenposted (new java.util.Date)
              :name "Phil\nNewline and \tTab"
              :email "hey@nospam.com"} )
        c2 (EntryComment. {}
             {:content "Comment #3"
              :whenposted (new java.util.Date)
              :name "Dominic"
              :email "please@nospam.com"} )
        saved (save-deps e [c1 c2])]
    (ppe "Saved 2 children for entry (ID=2)" saved)))

Retrieve-evicted/Count relation entities

Related entities (evicted) 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.

(in-db db
  (let [e (find-by-id blog-entry-meta 2)
        r ((find-rels e entry-comment-meta) e)
        rc ((find-rels e entry-comment-meta {:cols [:content :name :email]}) e)
        rw ((find-rels e entry-comment-meta {:where (as-clause ["email=?" "hey@nospam.com"] )}) e)
        rb ((find-rels e entry-comment-meta {:cols [:content :name :email]
             :where (as-clause ["email=?" "hey@nospam.com"] )}) e)]
    (ppe "\nEntry:" e)
    (ppe "\nRelations:" r)
    (ppe "\nRelations with selected columns:" rc)
    (ppe "\nRelations with WHERE clause:" rw)
    (ppe "\nRelations with selected columns and WHERE clause:" rb))
  ;; Avoiding N+1 selects
  (let [entries (find-by-criteria blog-entry-meta)
        comments (find-rels entries entry-comment-meta)]
    (ppe "\nAll entries:" entries)
    (doseq [each entries]
      (ppe (str "\nComments for entry ID: " (:autoid each))
        (comments each))))
  ;; Avoiding N+1 selects while counting
  (let [entries (find-by-criteria blog-entry-meta)
        comments (find-rels entries entry-comment-meta {:cols [count-col]})]
    (ppe "\nAll entries:" entries)
    (doseq [each entries]
      (println (str "\nComments# for entry ID: " (:autoid each))
        (read-count-col (comments each))))))

Retrieve non-evicted relation entities

The with-find-rels-results macro allows to fetch relation entities as a lazy sequence.

(in-db db
  (with-find-rels-results res [e] entry-comment-meta {}
    (let [all (into [] res)]
      (ppe "Relation entities" all))))

Retrieve-evicted/Count sibling entities

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

(in-db db
  (let [e (find-by-id blog-entry-meta 2)
        r ((find-rels e entry-comment-meta) e)
        c (first r)
        s (find-siblings c e)
        sc (find-siblings c e {:cols [:content :name :email]})
        sw (find-siblings c e {:where (as-clause ["name LIKE ?" "Phi%"] )})
        sb (find-siblings c e {:cols [:content :name :email] :where (as-clause ["name LIKE ?" "Phi%"] )} )]
    (ppe "\nChild: " c)
    (ppe "\nSiblings:" s)
    (ppe "\nSiblings with selected columns:" sc)
    (ppe "\nSiblings with WHERE clause:" sw)
    (ppe "\nSiblings with selected columns and WHERE clause:" sb)))

Retrieve non-evicted sibling entities

(in-db db
  (let [c (find-by-id entry-comment-meta 1)]
    (with-find-siblings-results ses c blog-entry-meta {}
      (let [all (into [] ses)]
        (ppe "Sibling entities" all)))))

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.

(in-txn db
  (let [e (find-by-id blog-entry-meta 3)
        r ((find-rels e entry-comment-meta) e)
        c (first r)]
    (println "** Deleting comment **")
    (delete c)
    (println "** Deleting entry-comment graph **")
    (delete-cascade e)))

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 the db-query function or the with-db-query-results macro.

The with-db-query-results macro 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 with-db-query-results simply because the JDBC driver may be optimized to pull data for ResultSet on on-demand basis, which leads to conserving JVM heap space.

Usually when your result-set is small you can consider using db-query function that readily returns all rows found in the result-set. All find-xxx functions in SQLRat use the db-query function and all with-find-xxx-results macros use the with-db-query-results macro.

Updated