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
40 :accessor employee-company
42 :db-info (:join-class company
44 :foreign-key companyid
50 :accessor employee-manager
52 :db-info (:join-class employee
56 (:base-table employee))
58 (clsql:def-view-class company ()
61 :db-constraints :not-null
69 :initarg :presidentid)
73 :db-info (:join-class employee
78 :reader company-employees
80 :db-info (:join-class employee
82 :foreign-key companyid
84 (:base-table company))
86 ;; Connect to the database (see the CLSQL documentation for vendor
87 ;; specific connection specs).
88 (case *tutorial-database-type*
89 ((:mysql :postgresql :postgresql-socket)
90 (clsql:connect `(,*tutorial-database-server*
91 ,*tutorial-database-name*
92 ,*tutorial-database-user*
93 ,*tutorial-database-password*)
94 :database-type *tutorial-database-type*))
95 ((:odbc :aodbc :oracle)
96 (clsql:connect `(,*tutorial-database-name*
97 ,*tutorial-database-user*
98 ,*tutorial-database-password*)
99 :database-type *tutorial-database-type*))
101 (clsql:connect `(,*tutorial-database-name*)
102 :database-type *tutorial-database-type*)))
104 ;; Record the sql going out, helps us learn what is going
105 ;; on behind the scenes
106 (clsql:start-sql-recording)
108 ;; Create the tables for our view classes
109 ;; First we drop them, ignoring any errors
111 (clsql:drop-view-from-class 'employee)
112 (clsql:drop-view-from-class 'company))
114 (clsql:create-view-from-class 'employee)
115 (clsql:create-view-from-class 'company)
118 ;; Create some instances of our view classes
119 (defvar company1 (make-instance 'company
122 ;; Lenin is president of Widgets Inc.
125 (defvar employee1 (make-instance 'employee
127 :first-name "Vladamir"
129 :email "lenin@soviet.org"
132 (defvar employee2 (make-instance 'employee
136 :email "stalin@soviet.org"
138 ;; Lenin manages Stalin (for now)
141 (clsql:update-records-from-instance employee1)
142 (clsql:update-records-from-instance employee2)
143 (clsql:update-records-from-instance company1)
145 ;; lets use the functional sql interface
146 (clsql:locally-enable-sql-reader-syntax)
148 (format t "The email address of ~A ~A is ~A"
149 (first-name employee1)
150 (last-name employee1)
151 (employee-email employee1))
153 (setf (employee-email employee1) "lenin-nospam@soviets.org")
155 ;; Update the database
156 (clsql:update-records-from-instance employee1)
158 (let ((new-lenin (car
159 (clsql:select 'employee
160 :where [= [slot-value 'employee 'emplid] 1]
162 (format t "His new email is ~A"
163 (employee-email new-lenin)))
169 (clsql:select 'employee)
171 (clsql:select 'company)
173 ;; employees named Lenin
174 (clsql:select 'employee :where [= [slot-value 'employee 'last-name]
177 (clsql:select 'company :where [= [slot-value 'company 'name]
180 ;; Employees of Widget's Inc.
181 (clsql:select 'employee
182 :where [and [= [slot-value 'employee 'companyid]
183 [slot-value 'company 'companyid]]
184 [= [slot-value 'company 'name]
187 ;; Same thing, except that we are using the employee
188 ;; relation in the company view class to do the join for us,
189 ;; saving us the work of writing out the SQL!
190 (company-employees company1)
192 ;; President of Widgets Inc.
195 ;; Manager of Josef Stalin
196 (employee-manager employee2)