usql/doc/usql-tutorial.lisp

   1
   2 (in-package :cl-user)
   3
   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")
  15
  16 (sql:def-view-class employee ()
  17   ((emplid
  18     :db-kind :key
  19     :db-constraints :not-null
  20     :nulls-ok nil
  21     :type integer
  22     :initarg :emplid)
  23    (first-name
  24     :accessor first-name
  25     :type (string 30)
  26     :initarg :first-name)
  27    (last-name
  28     :accessor last-name
  29     :type (string 30)
  30     :initarg :last-name)
  31    (email
  32     :accessor employee-email
  33     :type (string 100)
  34     :nulls-ok t
  35     :initarg :email)
  36    (companyid
  37     :type integer)
  38    (company
  39     :accessor employee-company
  40     :db-kind :join
  41     :db-info (:join-class company
  42                           :home-key companyid
  43                           :foreign-key companyid
  44                           :set nil))
  45    (managerid
  46     :type integer
  47     :nulls-ok t)
  48    (manager
  49     :accessor employee-manager
  50     :db-kind :join
  51     :db-info (:join-class employee
  52                           :home-key managerid
  53                           :foreign-key emplid
  54                           :set nil)))
  55   (:base-table employee))
  56
  57 (sql:def-view-class company ()
  58   ((companyid
  59     :db-type :key
  60     :db-constraints :not-null
  61     :type integer
  62     :initarg :companyid)
  63    (name
  64     :type (string 100)
  65     :initarg :name)
  66    (presidentid
  67     :type integer)
  68    (president
  69     :reader president
  70     :db-kind :join
  71     :db-info (:join-class employee
  72                           :home-key presidentid
  73                           :foreign-key emplid
  74                           :set nil))
  75    (employees
  76     :reader company-employees
  77     :db-kind :join
  78     :db-info (:join-class employee
  79                           :home-key companyid
  80                           :foreign-key companyid
  81                           :set t)))
  82   (:base-table company))
  83
  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*)
  91
  92 ;; Record the sql going out, helps us learn what is going
  93 ;; on behind the scenes
  94 (sql:start-sql-recording)
  95
  96 ;; Create the tables for our view classes
  97 ;; First we drop them, ignoring any errors
  98 (ignore-errors
  99  (sql:drop-view-from-class 'employee)
 100  (sql:drop-view-from-class 'company))
 101
 102 (sql:create-view-from-class 'employee)
 103 (sql:create-view-from-class 'company)
 104
 105
 106 ;; Create some instances of our view classes
 107 (defvar employee1 (make-instance 'employee
 108                                :emplid 1
 109                                :first-name "Vladamir"
 110                                :last-name "Lenin"
 111                                :email "lenin@soviet.org"))
 112
 113 (defvar company1 (make-instance 'company
 114                               :companyid 1
 115                               :name "Widgets Inc."))
 116
 117
 118 (defvar employee2 (make-instance 'employee
 119                                :emplid 2
 120                                :first-name "Josef"
 121                                :last-name "Stalin"
 122                                :email "stalin@soviet.org"))
 123
 124 ;; Lenin manages Stalin (for now)
 125 (sql:add-to-relation employee2 'manager employee1)
 126
 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)
 130
 131 ;; Lenin is president of Widgets Inc.
 132 (sql:add-to-relation company1 'president employee1)
 133
 134 (sql:update-records-from-instance employee1)
 135 (sql:update-records-from-instance employee2)
 136 (sql:update-records-from-instance company1)
 137
 138 ;; lets us use the functional
 139 ;; sql interface
 140 (sql:locally-enable-sql-reader-syntax)
 141
 142
 143 (format t "The email address of ~A ~A is ~A"
 144         (first-name employee1)
 145         (last-name employee1)
 146         (employee-email employee1))
 147
 148 (setf (employee-email employee1) "lenin-nospam@soviets.org")
 149
 150 ;; Update the database
 151 (sql:update-records-from-instance employee1)
 152
 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)))
 158
 159
 160 ;; Some queries
 161
 162 ;; all employees
 163 (sql:select 'employee)
 164 ;; all companies
 165 (sql:select 'company)
 166
 167 ;; employees named Lenin
 168 (sql:select 'employee :where [= [slot-value 'employee 'last-name]
 169                                 "Lenin"])
 170
 171 (sql:select 'company :where [= [slot-value 'company 'name]
 172                                "Widgets Inc."])
 173
 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]
 179                            "Widgets Inc."]])
 180
 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)
 185
 186 ;; President of Widgets Inc.
 187 (president company1)
 188
 189 ;; Manager of Josef Stalin
 190 (employee-manager employee2)