X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=examples%2Fclsql-tutorial.lisp;h=1980e3db0f4acd70fe0b8f76406af4dcae0cf1b2;hp=bb976a7883f64c085c81cb43c72e170fdc1040a1;hb=refs%2Ftags%2Fv3.8.6;hpb=9dd1aaa4e36d0d9a13e68cb501f1d17dbb10c209 diff --git a/examples/clsql-tutorial.lisp b/examples/clsql-tutorial.lisp index bb976a7..1980e3d 100644 --- a/examples/clsql-tutorial.lisp +++ b/examples/clsql-tutorial.lisp @@ -2,23 +2,23 @@ (in-package #:clsql-user) -;; You must set these variables to appropriate values. -(defvar *tutorial-database-type* nil - "Possible values are :postgresql,:postgresql-socket :mysql or :sqlite") -(defvar *tutorial-database-name* "" +;; 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 () ((emplid :db-kind :key :db-constraints :not-null - :nulls-ok nil :type integer :initarg :emplid) (first-name @@ -32,27 +32,27 @@ (email :accessor employee-email :type (string 100) - :nulls-ok t :initarg :email) (companyid - :type integer) + :type integer + :initarg :companyid) (company :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 - :nulls-ok t) + :initarg :managerid) (manager :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 () @@ -65,30 +65,41 @@ :type (string 100) :initarg :name) (presidentid - :type integer) + :type integer + :initarg :presidentid) (president :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). -(clsql:connect `(,*tutorial-database-server* - ,*tutorial-database-name* - ,*tutorial-database-user* - ,*tutorial-database-password*) - :database-type *tutorial-database-type*) +(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 @@ -105,46 +116,39 @@ ;; Create some instances of our view classes -(defvar employee1 (make-instance 'employee - :emplid 1 - :first-name "Vladamir" - :last-name "Lenin" - :email "lenin@soviet.org")) - (defvar company1 (make-instance 'company - :companyid 1 - :name "Widgets Inc.")) - + :companyid 1 + :name "Widgets Inc." + ;; Lenin is president of Widgets Inc. + :presidentid 1)) -(defvar employee2 (make-instance 'employee - :emplid 2 - :first-name "Josef" - :last-name "Stalin" - :email "stalin@soviet.org")) - -;; Lenin manages Stalin (for now) -(clsql:add-to-relation employee2 'manager employee1) - -;; Lenin and Stalin both work for Widgets Inc. -(clsql:add-to-relation company1 'employees employee1) -(clsql:add-to-relation company1 'employees employee2) +(defvar employee1 (make-instance 'employee + :emplid 1 + :first-name "Vladamir" + :last-name "Lenin" + :email "lenin@soviet.org" + :companyid 1)) -;; Lenin is president of Widgets Inc. -(clsql:add-to-relation company1 'president employee1) +(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 us 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") @@ -152,10 +156,11 @@ (clsql:update-records-from-instance employee1) (let ((new-lenin (car - (clsql:select 'employee - :where [= [slot-value 'employee 'emplid] 1])))) + (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 @@ -167,17 +172,17 @@ ;; 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,