Now that we have our model laid out, we should create some object. Let us assume that we have a database connect set up already. We first need to create our tables in the database:
Note: the file doc/clsql-tutorial.lisp contains view class definitions which you can load into your list at this point in order to play along at home.
(clsql:create-view-from-class 'employee) (clsql:create-view-from-class 'company)
Then we will create our objects. We create them just like you would any other CLOS object:
(defvar employee1 (make-instance 'employee :emplid 1 :first-name "Vladamir" :last-name "Lenin" :email "lenin@soviet.org")) (defvar company1 (make-instance 'company :companyid 1 :name "Widgets Inc.")) (defvar employee2 (make-instance 'employee :emplid 2 :first-name "Josef" :last-name "Stalin" :email "stalin@soviet.org"))
In order to insert an objects into the database we use the UPDATE-RECORDS-FROM-INSTANCE function as follows:
(clsql:update-records-from-instance employee1) (clsql:update-records-from-instance employee2) (clsql:update-records-from-instance company1)
Now we can set up some of the relations between employees and companies, and their managers. The ADD-TO-RELATION method provides us with an easy way of doing that. It will update both the relation slot, as well as the home-key and foreign-key slots in both objects in the relation.
;; Lenin manages Stalin (for now) (clsql:add-to-relation employee2 'manager employee1) ;; Lenin and Stalin both work for Widgets Inc. (clsql:add-to-relation company1 'employees employee1) (clsql:add-to-relation company1 'employees employee2) ;; Lenin is president of Widgets Inc. (clsql:add-to-relation company1 'president employee1)
After you make any changes to an object, you have to specifically tell CLSQL to update the SQL database. The UPDATE-RECORDS-FROM-INSTANCE method will write all of the changes you have made to the object into the database.
Since CLSQL objects are just normal CLOS objects, we can manipulate their slots just like any other object. For instance, let's say that Lenin changes his email because he was getting too much spam from the German Socialists.
;; Print Lenin's current email address, change it and save it to the
;; database. Get a new object representing Lenin from the database
;; and print the email
;; This lets us use the functional CLSQL interface with [] syntax
(clsql:locally-enable-sql-reader-syntax)
(format t "The email address of ~A ~A is ~A"
(first-name employee1)
(last-name employee1)
(employee-email employee1))
(setf (employee-email employee1) "lenin-nospam@soviets.org")
;; Update the database
(clsql:update-records-from-instance employee1)
(let ((new-lenin (car (clsql:select 'employee
:where [= [slot-value 'employee 'emplid] 1]))))
(format t "His new email is ~A"
(employee-email new-lenin)))
Everything except for the last LET expression is already familiar to us by now. To understand the call to CLSQL:SELECT we need to discuss the Functional SQL interface and it's integration with the Object Oriented interface of CLSQL.