(in-package #:clsql-user)
-;; You must set these variables to appropriate values.
-(defvar *tutorial-database-type* nil
+;; 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* ""
+(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* ""
+(defvar *tutorial-database-password* ""
"The password if required")
(clsql:def-view-class employee ()
:accessor employee-company
:db-kind :join
:db-info (:join-class company
- :home-key companyid
- :foreign-key companyid
- :set nil))
+ :home-key companyid
+ :foreign-key companyid
+ :set nil))
(managerid
:type integer
:initarg :managerid)
:accessor employee-manager
:db-kind :join
:db-info (:join-class employee
- :home-key managerid
- :foreign-key emplid
- :set nil)))
+ :home-key managerid
+ :foreign-key emplid
+ :set nil)))
(:base-table employee))
(clsql:def-view-class company ()
:reader president
:db-kind :join
:db-info (:join-class employee
- :home-key presidentid
- :foreign-key emplid
- :set nil))
+ :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)))
+ :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*))
+ (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*))
+ (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*)))
+ :database-type *tutorial-database-type*)))
;; Record the sql going out, helps us learn what is going
;; on behind the scenes
;; 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))
+ :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))
+ :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))
+ :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
+;; 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))
+ (first-name employee1)
+ (last-name employee1)
+ (employee-email employee1))
(setf (employee-email employee1) "lenin-nospam@soviets.org")
(clsql:update-records-from-instance employee1)
(let ((new-lenin (car
- (clsql:select 'employee
- :where [= [slot-value 'employee 'emplid] 1]
- :flatp t))))
+ (clsql:select 'employee
+ :where [= [slot-value 'employee 'emplid] 1]
+ :flatp t))))
(format t "His new email is ~A"
- (employee-email new-lenin)))
+ (employee-email new-lenin)))
;; Some queries
;; employees named Lenin
(clsql:select 'employee :where [= [slot-value 'employee 'last-name]
- "Lenin"])
+ "Lenin"])
(clsql:select 'company :where [= [slot-value 'company 'name]
- "Widgets Inc."])
+ "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."]])
+ :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,
projects / clsql.git / blobdiff