Clementson's Blog

CLSQL - Part 2

Tuesday, June 8, 2004

In my previous post, I discussed some of the advantages of CLSQL and how to install it. In this posting, I'll walk through an interactive, annotated session using CLSQL in order to illustrate some of the characteristics of working with CLSQL. My example will use a Sales Order header file (typically containing the customer and generic order information) and a Sales Order detail file (typically containing the individual items that are being purchased). There is a 1:n relationship between the header and the detail files and the columns in the tables are kept to a minimum for the purposes of this walk-through in order to keep things simple.

• I'll start off by connecting to my "test" database. For this example, I'll be using MySQL as the database back-end. However, this example has been tested with SQLite as well and should work with any of the databases supported by CLSQL.

CL-USER> (use-package :clsql-user)
T
CL-USER> (connect `("" "test" "" "") :database-type :mysql)
#<CLSQL-MYSQL:MYSQL-DATABASE /test/ OPEN @ #x20a6b1da>

• In order to see the SQL that CLSQL sends to the database, I turn on SQL recording. This will output the generated SQL statements and is a useful debugging aid. By enabling SQL reader syntax, I'm saying that I want the Lisp reader to interpret certain characters differently for CLSQL.

CL-USER> (start-sql-recording)
; No value
CL-USER> (enable-sql-reader-syntax)
; No value

• First, I create the Sales Order Header and Detail tables. I could use the CLSQL "execute-command" method and do something like:

CL-USER> (execute-command "create table `header` (`id` int primary key, 
                                         `customer` varchar(50), 
                                         `comments` varchar(50))")

• However, that particular "create table" SQL statement would be MySQL-specific and would not work for SQLite and some of the other databases. Instead, I will use the generic CLSQL "create-table" function to create the Sales Order Header and Detail tables. This allows one to create tables in a database-neutral manner. In fact, for the remainder of this walk-through, I will use the "generic" CLSQL commands. If I subsequently wanted to optimize specific SQL commands for performance reasons, I might later come back to the code and conditionalize certain commands depending on the database being used.

CL-USER> (create-table  [header]
			'(([id] integer primary key)
			  ([customer] (string 50))
			  ([comments] (string 50))))
;; 2004-06-08 20:18:01 /test/ => CREATE TABLE HEADER (ID INT PRIMARY KEY, CUSTOMER CHAR(50), 
                                                      COMMENTS CHAR(50)) Type=InnoDB
; No value
CL-USER> (create-table  [detail]
			'(([id] integer)
			  ([item] (string 25))
			  ([quantity] integer)))
;; 2004-06-08 20:18:13 /test/ => CREATE TABLE DETAIL (ID INT, ITEM CHAR(25), 
                                                      QUANTITY INT) Type=InnoDB
; No value

• Having said that, there is nothing to prevent me from mixing a combination of approaches. For example, I'll insert a header record using "execute-command". In this instance, the "insert" SQL statement is identical for both MySQL and SQLite, so there isn't a compatibility problem.

CL-USER> (execute-command "insert into header (id, customer) values (1, 'Joe Bloggs')")
;; 2004-06-08 20:18:18 /test/ => insert into header (id, customer) 
                                                    values (1, 'Joe Bloggs')
; No value

• Since you normally want to make certain that a Sales Order's detail lines are successfully written to the database with the header in order to maintain application integrity, you typically wrap the entire order in a "transaction" than ensures that either all records are written or none are. I'll add a complete Sales Order in this manner:

CL-USER> (with-transaction
	     ()
	   (insert-records :into [header]
			   :attributes '([id] [customer])
			   :values '(2 "Mary Smith"))
	   (insert-records :into [detail]
			   :attributes '([id] [item] [quantity])
			   :values '(2 "BMW" 3)))
;; 2004-06-08 20:18:42 /test/ => BEGIN
;; 2004-06-08 20:18:42 /test/ => INSERT INTO HEADER (ID,CUSTOMER) VALUES (2,'Mary Smith')
;; 2004-06-08 20:18:42 /test/ => INSERT INTO DETAIL (ID,ITEM,QUANTITY) VALUES (2,'BMW',3)
;; 2004-06-08 20:18:42 /test/ => COMMIT
NIL

• Let's query the order that was just written away (notice how the earlier "start-sql-recording" statement shows what the generated "select" looks like):

CL-USER> (select [customer] [item] [quantity] :from '([header] [detail])
		 :where [= [header id] [detail id]])
;; 2004-06-08 20:18:50 /test/ => SELECT CUSTOMER,ITEM,QUANTITY FROM HEADER,DETAIL 
                                        WHERE (HEADER.ID = DETAIL.ID)
(("Mary Smith" "BMW" 3))
("CUSTOMER" "ITEM" "QUANTITY")

• So far, we've only been using the "functional" API in CLSQL. This API allows us to interact with database records using standard SQL. However, CLSQL also has an "object-oriented" API that allows access to relational records via CLOS objects (sort of like a poor man's object-relational mapping). The "def-view-class" macro wraps "defclass" and establishes a mapping between class "slots" and columns in a database. Let's create classes that map to the Sales Order header and detail tables that we created earlier. We'll create a "header" class that maps directly to the "header" table and a "so-detail" class that maps directly to the "detail" table. Since we'll be using the "so-detail" class to subsequently update the "detail" table, I'll define ":initarg" attributes for the slots in the class:

CL-USER> (def-view-class header ()
	   ((id :type integer :db-kind :key)
	    (customer :type (string 50))
	    (comments :type (string 50))))
#<CLSQL-SYS::STANDARD-DB-CLASS HEADER>
CL-USER> (def-view-class so-detail ()
	   ((id  :db-kind :key
		 :column id
		 :type integer
		 :initarg :id)
	    (item :column item
		  :type (string 25)
		  :initarg :item)
	    (quantity :column quantity
		      :type integer
		      :initarg :quantity))
	   (:base-table detail))
#<CLSQL-SYS::STANDARD-DB-CLASS SO-DETAIL>

• Now, let's create a class that represents a "join" between the columns in the header table and the columns in the detail table:

CL-USER> (def-view-class expanded-order-line (so-detail)
	   ((header-info  :db-kind :join
			  :db-info (:home-key id
				    :foreign-key id
				    :join-class header)))
	   (:base-table detail))
#CLSQL-SYS::STANDARD-DB-CLASS EXPANDED-ORDER-LINE>

• If we "select" based on the "joined" class, we see that we get back slots for the detail table and a slot that contains the header information.

CL-USER> (select 'expanded-order-line)
;; 2004-06-08 20:19:59 /test/ => SELECT DETAIL.QUANTITY,DETAIL.ITEM,DETAIL.ID FROM DETAIL
((#<EXPANDED-ORDER-LINE @ #x207d60ca>))
CL-USER> (describe (caar *))
#<EXPANDED-ORDER-LINE @ #x207d60ca> is an instance of
#<CLSQL-SYS::STANDARD-DB-CLASS EXPANDED-ORDER-LINE>:
The following slots have :INSTANCE allocation:
HEADER-INFO     <unbound>
ID              2
ITEM            "BMW"
QUANTITY        3
VIEW-DATABASE   #<CLSQL-MYSQL:MYSQL-DATABASE /test/ OPEN @ #x2076097a>
; No value

• Now, create an instance of the "joined" records that represents all the header and detail information for any orders that are for a "BMW":

CL-USER> (setf bmw-order-detail
	       (car
		(select 'expanded-order-line
			:flatp t
			:where [= [slot-value 'expanded-order-line 'item] "BMW"])))
;; 2004-06-08 20:20:19 /test/ => SELECT DETAIL.QUANTITY,DETAIL.ITEM,DETAIL.ID 
                                        FROM DETAIL WHERE (DETAIL.ITEM = 'BMW')
#<EXPANDED-ORDER-LINE @ #x2080eef2>

• We can view header slots in the newly-created object:

CL-USER> (slot-value (slot-value bmw-order-detail 'header-info) 'customer)
;; 2004-06-08 20:20:23 /test/ => SELECT HEADER.COMMENTS,HEADER.CUSTOMER,HEADER.ID 
                                        FROM HEADER WHERE (HEADER.ID = 2)
"Mary Smith"

• It is also easy to use the OO API to update the database. Here, I'll create another order detail line object to add to the existing order and update the table from the object:

CL-USER> (setq new-line (make-instance 'so-detail
				       :id 2
				       :item "VW"
				       :quantity 1))
#<SO-DETAIL @ #x2082286a>
CL-USER> (update-records-from-instance new-line)
;; 2004-06-08 20:20:39 /test/ => INSERT INTO DETAIL (ID,ITEM,QUANTITY) VALUES (2,'VW',1)
; No value
CL-USER> (select [customer] [item] [quantity] :from '([header] [detail])
		 :where [= [header id] [detail id]])
;; 2004-06-08 20:20:51 /test/ => SELECT CUSTOMER,ITEM,QUANTITY FROM HEADER,DETAIL 
                                        WHERE (HEADER.ID = DETAIL.ID)
(("Mary Smith" "BMW" 3) ("Mary Smith" "VW" 1))
("CUSTOMER" "ITEM" "QUANTITY")

• Ok, we're done for now!

CL-USER> (disconnect)
T
CL-USER>