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,
8 :oracle, :odbc, :aodbc or :sqlite")
9 (defvar *tutorial-database-name* "clsqltut"
10 "The name of the database we will work in.")
11 (defvar *tutorial-database-user* ""
12 "The name of the database user we will work as.")
13 (defvar *tutorial-database-server* ""
14 "The name of the database server if required")
15 (defvar *tutorial-database-password* ""
16 "The password if required")
18 (clsql:def-view-class employee ()
21 :db-constraints :not-null
33 :accessor employee-email
39 :accessor employee-company
41 :db-info (:join-class company
43 :foreign-key companyid
49 :accessor employee-manager
51 :db-info (:join-class employee
55 (:base-table employee))
57 (clsql:def-view-class company ()
60 :db-constraints :not-null
68 :initarg :presidentid)
72 :db-info (:join-class employee
77 :reader company-employees
79 :db-info (:join-class employee
81 :foreign-key companyid
83 (:base-table company))
85 ;; Connect to the database (see the CLSQL documentation for vendor
86 ;; specific connection specs).
87 (case *tutorial-database-type*
89 (clsql:connect `(,*tutorial-database-server*
90 ,*tutorial-database-name*
91 ,*tutorial-database-user*
92 ,*tutorial-database-password*)
93 :database-type *tutorial-database-type*))
94 ((:odbc :aodbc :oracle)
95 (clsql:connect `(,*tutorial-database-name*
96 ,*tutorial-database-user*
97 ,*tutorial-database-password*)
98 :database-type *tutorial-database-type*))
100 (clsql:connect `(,*tutorial-database-name*)
101 :database-type *tutorial-database-type*)))
103 ;; Record the sql going out, helps us learn what is going
104 ;; on behind the scenes
105 (clsql:start-sql-recording)
107 ;; Create the tables for our view classes
108 ;; First we drop them, ignoring any errors
110 (clsql:drop-view-from-class 'employee)
111 (clsql:drop-view-from-class 'company))
113 (clsql:create-view-from-class 'employee)
114 (clsql:create-view-from-class 'company)
117 ;; Create some instances of our view classes
118 (defvar company1 (make-instance 'company
121 ;; Lenin is president of Widgets Inc.
124 (defvar employee1 (make-instance 'employee
126 :first-name "Vladamir"
128 :email "lenin@soviet.org"
131 (defvar employee2 (make-instance 'employee
135 :email "stalin@soviet.org"
137 ;; Lenin manages Stalin (for now)
140 (clsql:update-records-from-instance employee1)
141 (clsql:update-records-from-instance employee2)
142 (clsql:update-records-from-instance company1)
144 ;; lets use the functional sql interface
145 (clsql:locally-enable-sql-reader-syntax)
147 (format t "The email address of ~A ~A is ~A"
148 (first-name employee1)
149 (last-name employee1)
150 (employee-email employee1))
152 (setf (employee-email employee1) "lenin-nospam@soviets.org")
154 ;; Update the database
155 (clsql:update-records-from-instance employee1)
157 (let ((new-lenin (car
158 (clsql:select 'employee
159 :where [= [slot-value 'employee 'emplid] 1]
161 (format t "His new email is ~A"
162 (employee-email new-lenin)))
168 (clsql:select 'employee)
170 (clsql:select 'company)
172 ;; employees named Lenin
173 (clsql:select 'employee :where [= [slot-value 'employee 'last-name]
176 (clsql:select 'company :where [= [slot-value 'company 'name]
179 ;; Employees of Widget's Inc.
180 (clsql:select 'employee
181 :where [and [= [slot-value 'employee 'companyid]
182 [slot-value 'company 'companyid]]
183 [= [slot-value 'company 'name]
186 ;; Same thing, except that we are using the employee
187 ;; relation in the company view class to do the join for us,
188 ;; saving us the work of writing out the SQL!
189 (company-employees company1)
191 ;; President of Widgets Inc.
194 ;; Manager of Josef Stalin
195 (employee-manager employee2)