1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196
|
(asdf:operate 'asdf:load-op 'clsql)
(in-package #:clsql-user)
;; You must set these variables to appropriate values.
(defvar *tutorial-database-type* nil
"Possible values are :postgresql :postgresql-socket, :mysql,
:oracle, :odbc, :aodbc or :sqlite")
(defvar *tutorial-database-name* "clsqltut"
"The name of the database we will work in.")
(defvar *tutorial-database-user* ""
"The name of the database user we will work as.")
(defvar *tutorial-database-server* ""
"The name of the database server if required")
(defvar *tutorial-database-password* ""
"The password if required")
(clsql:def-view-class employee ()
((emplid
:db-kind :key
:db-constraints :not-null
:type integer
:initarg :emplid)
(first-name
:accessor first-name
:type (string 30)
:initarg :first-name)
(last-name
:accessor last-name
:type (string 30)
:initarg :last-name)
(email
:accessor employee-email
:type (string 100)
:initarg :email)
(companyid
:type integer
:initarg :companyid)
(company
:accessor employee-company
:db-kind :join
:db-info (:join-class company
:home-key companyid
:foreign-key companyid
:set nil))
(managerid
:type integer
:initarg :managerid)
(manager
:accessor employee-manager
:db-kind :join
:db-info (:join-class employee
:home-key managerid
:foreign-key emplid
:set nil)))
(:base-table employee))
(clsql:def-view-class company ()
((companyid
:db-kind :key
:db-constraints :not-null
:type integer
:initarg :companyid)
(name
:type (string 100)
:initarg :name)
(presidentid
:type integer
:initarg :presidentid)
(president
:reader president
:db-kind :join
:db-info (:join-class employee
:home-key presidentid
:foreign-key emplid
:set nil))
(employees
:reader company-employees
:db-kind :join
:db-info (:join-class employee
:home-key companyid
:foreign-key companyid
:set t)))
(:base-table company))
;; Connect to the database (see the CLSQL documentation for vendor
;; specific connection specs).
(case *tutorial-database-type*
((:mysql :postgresql :postgresql-socket)
(clsql:connect `(,*tutorial-database-server*
,*tutorial-database-name*
,*tutorial-database-user*
,*tutorial-database-password*)
:database-type *tutorial-database-type*))
((:odbc :aodbc :oracle)
(clsql:connect `(,*tutorial-database-name*
,*tutorial-database-user*
,*tutorial-database-password*)
:database-type *tutorial-database-type*))
(:sqlite
(clsql:connect `(,*tutorial-database-name*)
:database-type *tutorial-database-type*)))
;; Record the sql going out, helps us learn what is going
;; on behind the scenes
(clsql:start-sql-recording)
;; Create the tables for our view classes
;; First we drop them, ignoring any errors
(ignore-errors
(clsql:drop-view-from-class 'employee)
(clsql:drop-view-from-class 'company))
(clsql:create-view-from-class 'employee)
(clsql:create-view-from-class 'company)
;; Create some instances of our view classes
(defvar company1 (make-instance 'company
:companyid 1
:name "Widgets Inc."
;; Lenin is president of Widgets Inc.
:presidentid 1))
(defvar employee1 (make-instance 'employee
:emplid 1
:first-name "Vladamir"
:last-name "Lenin"
:email "lenin@soviet.org"
:companyid 1))
(defvar employee2 (make-instance 'employee
:emplid 2
:first-name "Josef"
:last-name "Stalin"
:email "stalin@soviet.org"
:companyid 1
;; Lenin manages Stalin (for now)
:managerid 1))
(clsql:update-records-from-instance employee1)
(clsql:update-records-from-instance employee2)
(clsql:update-records-from-instance company1)
;; lets use the functional sql interface
(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]
:flatp t))))
(format t "His new email is ~A"
(employee-email new-lenin)))
;; Some queries
;; all employees
(clsql:select 'employee)
;; all companies
(clsql:select 'company)
;; employees named Lenin
(clsql:select 'employee :where [= [slot-value 'employee 'last-name]
"Lenin"])
(clsql:select 'company :where [= [slot-value 'company 'name]
"Widgets Inc."])
;; Employees of Widget's Inc.
(clsql:select 'employee
:where [and [= [slot-value 'employee 'companyid]
[slot-value 'company 'companyid]]
[= [slot-value 'company 'name]
"Widgets Inc."]])
;; Same thing, except that we are using the employee
;; relation in the company view class to do the join for us,
;; saving us the work of writing out the SQL!
(company-employees company1)
;; President of Widgets Inc.
(president company1)
;; Manager of Josef Stalin
(employee-manager employee2)
|