+(defun test-connect-to-database (db-type spec)
+ (when (clsql-sys:db-backend-has-create/destroy-db? db-type)
+ (ignore-errors (destroy-database spec :database-type db-type))
+ (ignore-errors (create-database spec :database-type db-type)))
+
+ (setf *test-database-type* db-type)
+ (when (>= (length spec) 3)
+ (setq *test-database-user* (third spec)))
+
+ ;; Connect to the database
+ (clsql:connect spec
+ :database-type db-type
+ :make-default t
+ :if-exists :old)
+
+ ;; Ensure database is empty
+ (truncate-database :database *default-database*)
+
+ (setf *test-database-underlying-type*
+ (clsql-sys:database-underlying-type *default-database*))
+
+ *default-database*)
+
+(defparameter company1 nil)
+(defparameter employee1 nil)
+(defparameter employee2 nil)
+(defparameter employee3 nil)
+(defparameter employee4 nil)
+(defparameter employee5 nil)
+(defparameter employee6 nil)
+(defparameter employee7 nil)
+(defparameter employee8 nil)
+(defparameter employee9 nil)
+(defparameter employee10 nil)
+(defparameter address1 nil)
+(defparameter address2 nil)
+(defparameter employee-address1 nil)
+(defparameter employee-address2 nil)
+(defparameter employee-address3 nil)
+(defparameter employee-address4 nil)
+(defparameter employee-address5 nil)
+
+(defun test-initialise-database ()
+ (test-basic-initialize)
+
+ (let ((*backend-warning-behavior*
+ (if (member *test-database-type* '(:postgresql :postgresql-socket))
+ :ignore
+ :warn)))
+ (clsql:create-view-from-class 'employee)
+ (clsql:create-view-from-class 'company)
+ (clsql:create-view-from-class 'address)
+ (clsql:create-view-from-class 'employee-address))
+
+ (let ((*db-auto-sync* t))
+ (setf company1 (make-instance 'company
+ :presidentid 1
+ :companyid 1
+ :groupid 1
+ :name "Widgets Inc.")
+ employee1 (make-instance 'employee
+ :emplid 1
+ :groupid 1
+ :married t
+ :height (1+ (random 1.00))
+ :birthday (clsql:get-time)
+ :first-name "Vladamir"
+ :last-name "Lenin"
+ :email "lenin@soviet.org"
+ :companyid 1)
+ employee2 (make-instance 'employee
+ :emplid 2
+ :groupid 1
+ :height (1+ (random 1.00))
+ :married t
+ :birthday (clsql:get-time)
+ :first-name "Josef"
+ :last-name "Stalin"
+ :email "stalin@soviet.org"
+ :managerid 1
+ :companyid 1)
+ employee3 (make-instance 'employee
+ :emplid 3
+ :groupid 1
+ :height (1+ (random 1.00))
+ :married t
+ :birthday (clsql:get-time)
+ :first-name "Leon"
+ :last-name "Trotsky"
+ :email "trotsky@soviet.org"
+ :managerid 1
+ :companyid 1)
+ employee4 (make-instance 'employee
+ :emplid 4
+ :groupid 1
+ :height (1+ (random 1.00))
+ :married nil
+ :birthday (clsql:get-time)
+ :first-name "Nikita"
+ :last-name "Kruschev"
+ :email "kruschev@soviet.org"
+ :managerid 1
+ :companyid 1)
+ employee5 (make-instance 'employee
+ :emplid 5
+ :groupid 1
+ :married nil
+ :height (1+ (random 1.00))
+ :birthday (clsql:get-time)
+ :first-name "Leonid"
+ :last-name "Brezhnev"
+ :email "brezhnev@soviet.org"
+ :managerid 1
+ :companyid 1)
+ employee6 (make-instance 'employee
+ :emplid 6
+ :groupid 1
+ :married nil
+ :height (1+ (random 1.00))
+ :birthday (clsql:get-time)
+ :first-name "Yuri"
+ :last-name "Andropov"
+ :email "andropov@soviet.org"
+ :managerid 1
+ :companyid 1)
+ employee7 (make-instance 'employee
+ :emplid 7
+ :groupid 1
+ :height (1+ (random 1.00))
+ :married nil
+ :birthday (clsql:get-time)
+ :first-name "Konstantin"
+ :last-name "Chernenko"
+ :email "chernenko@soviet.org"
+ :managerid 1
+ :companyid 1)
+ employee8 (make-instance 'employee
+ :emplid 8
+ :groupid 1
+ :height (1+ (random 1.00))
+ :married nil
+ :birthday (clsql:get-time)
+ :first-name "Mikhail"
+ :last-name "Gorbachev"
+ :email "gorbachev@soviet.org"
+ :managerid 1
+ :companyid 1)
+ employee9 (make-instance 'employee
+ :emplid 9
+ :groupid 1
+ :married nil
+ :height (1+ (random 1.00))
+ :birthday (clsql:get-time)
+ :first-name "Boris"
+ :last-name "Yeltsin"
+ :email "yeltsin@soviet.org"
+ :managerid 1
+ :companyid 1)
+ employee10 (make-instance 'employee
+ :emplid 10
+ :groupid 1
+ :married nil
+ :height (1+ (random 1.00))
+ :birthday (clsql:get-time)
+ :first-name "Vladamir"
+ :last-name "Putin"
+ :email "putin@soviet.org"
+ :managerid 1
+ :companyid 1)
+ address1 (make-instance 'address
+ :addressid 1
+ :street-number 10
+ :street-name "Park Place"
+ :city "Leningrad"
+ :postal-code 123)
+ address2 (make-instance 'address
+ :addressid 2)
+ employee-address1 (make-instance 'employee-address
+ :emplid 1
+ :addressid 1
+ :verified t)
+ employee-address2 (make-instance 'employee-address
+ :emplid 2
+ :addressid 2
+ :verified t)
+ employee-address3 (make-instance 'employee-address
+ :emplid 3
+ :addressid 1
+ :verified nil)
+ employee-address4 (make-instance 'employee-address
+ :emplid 1
+ :addressid 2
+ :verified nil)
+ employee-address5 (make-instance 'employee-address
+ :emplid 3
+ :addressid 2)
+ ))
+
+ ;; sleep to ensure birthdays are no longer at current time
+ (sleep 1)
+
+ #||
+ ;; Lenin manages everyone
+ (clsql:add-to-relation employee2 'manager employee1)
+ (clsql:add-to-relation employee3 'manager employee1)
+ (clsql:add-to-relation employee4 'manager employee1)
+ (clsql:add-to-relation employee5 'manager employee1)
+ (clsql:add-to-relation employee6 'manager employee1)
+ (clsql:add-to-relation employee7 'manager employee1)
+ (clsql:add-to-relation employee8 'manager employee1)
+ (clsql:add-to-relation employee9 'manager employee1)
+ (clsql:add-to-relation employee10 'manager employee1)
+ ;; Everyone works for Widgets Inc.
+ (clsql:add-to-relation company1 'employees employee1)
+ (clsql:add-to-relation company1 'employees employee2)
+ (clsql:add-to-relation company1 'employees employee3)
+ (clsql:add-to-relation company1 'employees employee4)
+ (clsql:add-to-relation company1 'employees employee5)
+ (clsql:add-to-relation company1 'employees employee6)
+ (clsql:add-to-relation company1 'employees employee7)
+ (clsql:add-to-relation company1 'employees employee8)
+ (clsql:add-to-relation company1 'employees employee9)
+ (clsql:add-to-relation company1 'employees employee10)
+ ;; Lenin is president of Widgets Inc.
+ (clsql:add-to-relation company1 'president employee1)
+ ||#
+
+ ;; store these instances
+ #||
+ (clsql:update-records-from-instance employee1)
+ (clsql:update-records-from-instance employee2)
+ (clsql:update-records-from-instance employee3)
+ (clsql:update-records-from-instance employee4)
+ (clsql:update-records-from-instance employee5)
+ (clsql:update-records-from-instance employee6)
+ (clsql:update-records-from-instance employee7)
+ (clsql:update-records-from-instance employee8)
+ (clsql:update-records-from-instance employee9)
+ (clsql:update-records-from-instance employee10)
+ (clsql:update-records-from-instance company1)
+ (clsql:update-records-from-instance address1)
+ (clsql:update-records-from-instance address2)
+ ||#
+ )