1 (asdf:operate 'asdf:load-op 'clsql)
3 (in-package #:clsql-user)
5 ;; You must set these variables to appropriate values.
6 (defvar *tutorial-database-type* nil
7 "Possible values are :postgresql,:postgresql-socket :mysql, :oracle, :odbc or :sqlite")
8 (defvar *tutorial-database-name* "clsqltut"
9 "The name of the database we will work in.")
10 (defvar *tutorial-database-user* ""
11 "The name of the database user we will work as.")
12 (defvar *tutorial-database-server* ""
13 "The name of the database server if required")
14 (defvar *tutorial-database-password* ""
15 "The password if required")
17 (clsql:def-view-class employee ()
20 :db-constraints :not-null
32 :accessor employee-email
38 :accessor employee-company
40 :db-info (:join-class company
42 :foreign-key companyid
48 :accessor employee-manager
50 :db-info (:join-class employee
54 (:base-table employee))
56 (clsql:def-view-class company ()
59 :db-constraints :not-null
67 :initarg :presidentid)
71 :db-info (:join-class employee
76 :reader company-employees
78 :db-info (:join-class employee
80 :foreign-key companyid
82 (:base-table company))
84 ;; Connect to the database (see the CLSQL documentation for vendor
85 ;; specific connection specs).
86 (case *tutorial-database-type*
88 (clsql:connect `(,*tutorial-database-server*
89 ,*tutorial-database-name*
90 ,*tutorial-database-user*
91 ,*tutorial-database-password*)
92 :database-type *tutorial-database-type*))
94 (clsql:connect `(,*tutorial-database-name*
95 ,*tutorial-database-user*
96 ,*tutorial-database-password*)
97 :database-type *tutorial-database-type*))
99 (clsql:connect `(,*tutorial-database-name*)
100 :database-type *tutorial-database-type*)))
102 ;; Record the sql going out, helps us learn what is going
103 ;; on behind the scenes
104 (clsql:start-sql-recording)
106 ;; Create the tables for our view classes
107 ;; First we drop them, ignoring any errors
109 (clsql:drop-view-from-class 'employee)
110 (clsql:drop-view-from-class 'company))
112 (clsql:create-view-from-class 'employee)
113 (clsql:create-view-from-class 'company)
116 ;; Create some instances of our view classes
117 (defvar company1 (make-instance 'company
120 ;; Lenin is president of Widgets Inc.
123 (defvar employee1 (make-instance 'employee
125 :first-name "Vladamir"
127 :email "lenin@soviet.org"
130 (defvar employee2 (make-instance 'employee
134 :email "stalin@soviet.org"
136 ;; Lenin manages Stalin (for now)
139 (clsql:update-records-from-instance employee1)
140 (clsql:update-records-from-instance employee2)
141 (clsql:update-records-from-instance company1)
143 ;; lets use the functional sql interface
144 (clsql:locally-enable-sql-reader-syntax)
146 (format t "The email address of ~A ~A is ~A"
147 (first-name employee1)
148 (last-name employee1)
149 (employee-email employee1))
151 (setf (employee-email employee1) "lenin-nospam@soviets.org")
153 ;; Update the database
154 (clsql:update-records-from-instance employee1)
156 (let ((new-lenin (car
157 (clsql:select 'employee
158 :where [= [slot-value 'employee 'emplid] 1]))))
159 (format t "His new email is ~A"
160 (employee-email new-lenin)))
166 (clsql:select 'employee)
168 (clsql:select 'company)
170 ;; employees named Lenin
171 (clsql:select 'employee :where [= [slot-value 'employee 'last-name]
174 (clsql:select 'company :where [= [slot-value 'company 'name]
177 ;; Employees of Widget's Inc.
178 (clsql:select 'employee
179 :where [and [= [slot-value 'employee 'companyid]
180 [slot-value 'company 'companyid]]
181 [= [slot-value 'company 'name]
184 ;; Same thing, except that we are using the employee
185 ;; relation in the company view class to do the join for us,
186 ;; saving us the work of writing out the SQL!
187 (company-employees company1)
189 ;; President of Widgets Inc.
192 ;; Manager of Josef Stalin
193 (employee-manager employee2)