4 ;; You must set these variables to appropriate values.
5 (defvar *tutorial-database-type* nil
6 "Possible values are :postgresql,:postgresql-socket :mysql or :sqlite")
7 (defvar *tutorial-database-name* ""
8 "The name of the database we will work in.")
9 (defvar *tutorial-database-user* ""
10 "The name of the database user we will work as.")
11 (defvar *tutorial-database-server* ""
12 "The name of the database server if required")
13 (defvar *tutorial-database-password* ""
14 "The password if required")
16 (sql:def-view-class employee ()
19 :db-constraints :not-null
32 :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 (sql:def-view-class company ()
60 :db-constraints :not-null
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 (sql:connect `(,*tutorial-database-server*
87 ,*tutorial-database-name*
88 ,*tutorial-database-user*
89 ,*tutorial-database-password*)
90 :database-type *tutorial-database-type*)
92 ;; Record the sql going out, helps us learn what is going
93 ;; on behind the scenes
94 (sql:start-sql-recording)
96 ;; Create the tables for our view classes
97 ;; First we drop them, ignoring any errors
99 (sql:drop-view-from-class 'employee)
100 (sql:drop-view-from-class 'company))
102 (sql:create-view-from-class 'employee)
103 (sql:create-view-from-class 'company)
106 ;; Create some instances of our view classes
107 (defvar employee1 (make-instance 'employee
109 :first-name "Vladamir"
111 :email "lenin@soviet.org"))
113 (defvar company1 (make-instance 'company
115 :name "Widgets Inc."))
118 (defvar employee2 (make-instance 'employee
122 :email "stalin@soviet.org"))
124 ;; Lenin manages Stalin (for now)
125 (sql:add-to-relation employee2 'manager employee1)
127 ;; Lenin and Stalin both work for Widgets Inc.
128 (sql:add-to-relation company1 'employees employee1)
129 (sql:add-to-relation company1 'employees employee2)
131 ;; Lenin is president of Widgets Inc.
132 (sql:add-to-relation company1 'president employee1)
134 (sql:update-records-from-instance employee1)
135 (sql:update-records-from-instance employee2)
136 (sql:update-records-from-instance company1)
138 ;; lets us use the functional
140 (sql:locally-enable-sql-reader-syntax)
143 (format t "The email address of ~A ~A is ~A"
144 (first-name employee1)
145 (last-name employee1)
146 (employee-email employee1))
148 (setf (employee-email employee1) "lenin-nospam@soviets.org")
150 ;; Update the database
151 (sql:update-records-from-instance employee1)
153 (let ((new-lenin (car
154 (sql:select 'employee
155 :where [= [slot-value 'employee 'emplid] 1]))))
156 (format t "His new email is ~A"
157 (employee-email new-lenin)))
163 (sql:select 'employee)
165 (sql:select 'company)
167 ;; employees named Lenin
168 (sql:select 'employee :where [= [slot-value 'employee 'last-name]
171 (sql:select 'company :where [= [slot-value 'company 'name]
174 ;; Employees of Widget's Inc.
175 (sql:select 'employee
176 :where [and [= [slot-value 'employee 'companyid]
177 [slot-value 'company 'companyid]]
178 [= [slot-value 'company 'name]
181 ;; Same thing, except that we are using the employee
182 ;; relation in the company view class to do the join for us,
183 ;; saving us the work of writing out the SQL!
184 (company-employees company1)
186 ;; President of Widgets Inc.
189 ;; Manager of Josef Stalin
190 (employee-manager employee2)