X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=tests%2Ftest-fdml.lisp;h=342576fd536a920bea0f3cb10b1a16cae2d76785;hp=ae986faa832929b443595b9700af2f74bbc7a6d8;hb=5a6f424f3c8920f8f11bbf1e3aed6b4c2c7e6af8;hpb=cd2d370ae162457d420af3c1e9589ef0145ee5a1 diff --git a/tests/test-fdml.lisp b/tests/test-fdml.lisp index ae986fa..342576f 100644 --- a/tests/test-fdml.lisp +++ b/tests/test-fdml.lisp @@ -3,100 +3,100 @@ ;;;; File: test-fdml.lisp ;;;; Author: Marcus Pearce ;;;; Created: 30/03/2004 -;;;; Updated: <04/04/2004 11:52:39 marcusp> +;;;; Updated: $Id: $ ;;;; ====================================================================== ;;;; ;;;; Description ========================================================== ;;;; ====================================================================== ;;;; -;;;; Tests for the CLSQL-USQL Functional Data Manipulation Language +;;;; Tests for the CLSQL Functional Data Manipulation Language ;;;; (FDML). ;;;; ;;;; ====================================================================== -(in-package :clsql-usql-tests) +(in-package #:clsql-tests) -#.(usql:locally-enable-sql-reader-syntax) +#.(clsql:locally-enable-sql-reader-syntax) ;; inserts a record using all values only and then deletes it (deftest :fdml/insert/1 (progn - (usql:insert-records :into [employee] + (clsql:insert-records :into [employee] :values `(11 1 "Yuri" "Gagarin" "gagarin@soviet.org" 1 1 1.85 t ,(clsql-base:get-time))) (values - (usql:select [first-name] [last-name] [email] + (clsql:select [first-name] [last-name] [email] :from [employee] :where [= [emplid] 11]) - (progn (usql:delete-records :from [employee] :where [= [emplid] 11]) - (usql:select [*] :from [employee] :where [= [emplid] 11])))) + (progn (clsql:delete-records :from [employee] :where [= [emplid] 11]) + (clsql:select [*] :from [employee] :where [= [emplid] 11])))) (("Yuri" "Gagarin" "gagarin@soviet.org")) nil) ;; inserts a record using attributes and values and then deletes it (deftest :fdml/insert/2 (progn - (usql:insert-records :into [employee] + (clsql:insert-records :into [employee] :attributes '(emplid groupid first_name last_name email companyid managerid) :values '(11 1 "Yuri" "Gagarin" "gagarin@soviet.org" 1 1)) (values - (usql:select [first-name] [last-name] [email] :from [employee] + (clsql:select [first-name] [last-name] [email] :from [employee] :where [= [emplid] 11]) - (progn (usql:delete-records :from [employee] :where [= [emplid] 11]) - (usql:select [*] :from [employee] :where [= [emplid] 11])))) + (progn (clsql:delete-records :from [employee] :where [= [emplid] 11]) + (clsql:select [*] :from [employee] :where [= [emplid] 11])))) (("Yuri" "Gagarin" "gagarin@soviet.org")) nil) ;; inserts a record using av-pairs and then deletes it (deftest :fdml/insert/3 (progn - (usql:insert-records :into [employee] + (clsql:insert-records :into [employee] :av-pairs'((emplid 11) (groupid 1) (first_name "Yuri") (last_name "Gagarin") (email "gagarin@soviet.org") (companyid 1) (managerid 1))) (values - (usql:select [first-name] [last-name] [email] :from [employee] + (clsql:select [first-name] [last-name] [email] :from [employee] :where [= [emplid] 11]) - (progn (usql:delete-records :from [employee] :where [= [emplid] 11]) - (usql:select [first-name] [last-name] [email] :from [employee] + (progn (clsql:delete-records :from [employee] :where [= [emplid] 11]) + (clsql:select [first-name] [last-name] [email] :from [employee] :where [= [emplid] 11])))) (("Yuri" "Gagarin" "gagarin@soviet.org")) nil) ;; inserts a records using a query from another table (deftest :fdml/insert/4 (progn - (usql:create-table [employee2] '(([forename] string) + (clsql:create-table [employee2] '(([forename] string) ([surname] string) ([email] string))) - (usql:insert-records :into [employee2] + (clsql:insert-records :into [employee2] :query [select [first-name] [last-name] [email] :from [employee]] :attributes '(forename surname email)) (prog1 - (equal (usql:select [*] :from [employee2]) - (usql:select [first-name] [last-name] [email] + (equal (clsql:select [*] :from [employee2]) + (clsql:select [first-name] [last-name] [email] :from [employee])) - (usql:drop-table [employee2] :if-does-not-exist :ignore))) + (clsql:drop-table [employee2] :if-does-not-exist :ignore))) t) ;; updates a record using attributes and values and then deletes it (deftest :fdml/update/1 (progn - (usql:update-records [employee] + (clsql:update-records [employee] :attributes '(first_name last_name email) :values '("Yuri" "Gagarin" "gagarin@soviet.org") :where [= [emplid] 1]) (values - (usql:select [first-name] [last-name] [email] :from [employee] + (clsql:select [first-name] [last-name] [email] :from [employee] :where [= [emplid] 1]) (progn - (usql:update-records [employee] + (clsql:update-records [employee] :av-pairs'((first_name "Vladamir") (last_name "Lenin") (email "lenin@soviet.org")) :where [= [emplid] 1]) - (usql:select [first-name] [last-name] [email] :from [employee] + (clsql:select [first-name] [last-name] [email] :from [employee] :where [= [emplid] 1])))) (("Yuri" "Gagarin" "gagarin@soviet.org")) (("Vladamir" "Lenin" "lenin@soviet.org"))) @@ -104,32 +104,32 @@ ;; updates a record using av-pairs and then deletes it (deftest :fdml/update/2 (progn - (usql:update-records [employee] + (clsql:update-records [employee] :av-pairs'((first_name "Yuri") (last_name "Gagarin") (email "gagarin@soviet.org")) :where [= [emplid] 1]) (values - (usql:select [first-name] [last-name] [email] :from [employee] + (clsql:select [first-name] [last-name] [email] :from [employee] :where [= [emplid] 1]) (progn - (usql:update-records [employee] + (clsql:update-records [employee] :av-pairs'((first_name "Vladamir") (last_name "Lenin") (email "lenin@soviet.org")) :where [= [emplid] 1]) - (usql:select [first-name] [last-name] [email] + (clsql:select [first-name] [last-name] [email] :from [employee] :where [= [emplid] 1])))) (("Yuri" "Gagarin" "gagarin@soviet.org")) (("Vladamir" "Lenin" "lenin@soviet.org"))) (deftest :fdml/query/1 - (usql:query "SELECT COUNT(*) FROM EMPLOYEE WHERE (EMAIL LIKE '%org')") + (clsql:query "SELECT COUNT(*) FROM EMPLOYEE WHERE (EMAIL LIKE '%org')") (("10"))) (deftest :fdml/query/2 - (usql:query + (clsql:query "SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEE WHERE (EMPLID <= 5) ORDER BY LAST_NAME") (("Leonid" "Brezhnev") ("Nikita" "Kruschev") ("Vladamir" "Lenin") ("Josef" "Stalin") ("Leon" "Trotsky"))) @@ -137,13 +137,13 @@ (deftest :fdml/execute-command/1 (values - (usql:table-exists-p [foo] :owner *test-database-user*) + (clsql:table-exists-p [foo] :owner *test-database-user*) (progn - (usql:execute-command "create table foo (bar integer)") - (usql:table-exists-p [foo] :owner *test-database-user*)) + (clsql:execute-command "create table foo (bar integer)") + (clsql:table-exists-p [foo] :owner *test-database-user*)) (progn - (usql:execute-command "drop table foo") - (usql:table-exists-p [foo] :owner *test-database-user*))) + (clsql:execute-command "drop table foo") + (clsql:table-exists-p [foo] :owner *test-database-user*))) nil t nil) @@ -151,15 +151,15 @@ ;; these guys!) -- only works with pgsql (deftest :fdml/select/1 (if (member *test-database-type* '(:postgresql-socket :postgresql)) - (let ((max (usql:select [function "floor" + (let ((max (clsql:select [function "floor" [/ [* [max [height]] 100] 2.54]] :from [employee] :flatp t)) - (min (usql:select [function "floor" + (min (clsql:select [function "floor" [/ [* [min [height]] 100] 2.54]] :from [employee] :flatp t)) - (avg (usql:select [function "floor" + (avg (clsql:select [function "floor" [avg [/ [* [height] 100] 2.54]]] :from [employee] :flatp t))) @@ -168,27 +168,27 @@ t) (deftest :fdml/select/2 - (usql:select [first-name] :from [employee] :flatp t :distinct t + (clsql:select [first-name] :from [employee] :flatp t :distinct t :order-by [first-name]) ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir" "Yuri")) (deftest :fdml/select/3 - (usql:select [first-name] [count [*]] :from [employee] + (clsql:select [first-name] [count [*]] :from [employee] :group-by [first-name] :order-by [first-name]) (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1") ("Mikhail" "1") ("Nikita" "1") ("Vladamir" "2") ("Yuri" "1"))) (deftest :fdml/select/4 - (usql:select [last-name] :from [employee] :where [like [email] "%org"] + (clsql:select [last-name] :from [employee] :where [like [email] "%org"] :order-by [last-name] :flatp t) ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin" "Stalin" "Trotsky" "Yeltsin")) (deftest :fdml/select/5 - (usql:select [email] :from [employee] :flatp t + (clsql:select [email] :from [employee] :flatp t :where [in [employee emplid] [select [managerid] :from [employee]]]) ("lenin@soviet.org")) @@ -196,10 +196,10 @@ (deftest :fdml/select/6 (if (member *test-database-type* '(:postgresql-socket :postgresql)) (mapcar #'parse-integer - (usql:select [function "trunc" [height]] :from [employee] + (clsql:select [function "trunc" [height]] :from [employee] :flatp t)) (mapcar #'(lambda (s) (truncate (parse-integer s :junk-allowed t))) - (usql:select [height] :from [employee] :flatp t))) + (clsql:select [height] :from [employee] :flatp t))) (1 1 1 1 1 1 1 1 1 1)) (deftest :fdml/select/7 @@ -224,13 +224,13 @@ "Trotsky" "Yeltsin")) (deftest :fdml/select/11 - (usql:select [last-name] :from [employee] :where [married] :flatp t + (clsql:select [last-name] :from [employee] :where [married] :flatp t :order-by [emplid]) ("Lenin" "Stalin" "Trotsky")) (deftest :fdml/select/12 (let ((v 1)) - (usql:select [last-name] :from [employee] :where [= [emplid] v])) + (clsql:select [last-name] :from [employee] :where [= [emplid] v])) (("Lenin"))) ;(deftest :fdml/select/11 @@ -241,7 +241,7 @@ (deftest :fdml/do-query/1 (let ((result '())) - (usql:do-query ((name) [select [last-name] :from [employee] + (clsql:do-query ((name) [select [last-name] :from [employee] :order-by [last-name]]) (push name result)) result) @@ -249,14 +249,14 @@ "Chernenko" "Brezhnev" "Andropov")) (deftest :fdml/map-query/1 - (usql:map-query 'list #'identity + (clsql:map-query 'list #'identity [select [last-name] :from [employee] :flatp t :order-by [last-name]]) ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin" "Stalin" "Trotsky" "Yeltsin")) (deftest :fdml/map-query/2 - (usql:map-query 'vector #'identity + (clsql:map-query 'vector #'identity [select [last-name] :from [employee] :flatp t :order-by [last-name]]) #("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin" @@ -275,24 +275,24 @@ (deftest :fdml/transaction/1 (let ((results '())) ;; test if we are in a transaction - (push (usql:in-transaction-p) results) + (push (clsql:in-transaction-p) results) ;;start a transaction - (usql:start-transaction) + (clsql:start-transaction) ;; test if we are in a transaction - (push (usql:in-transaction-p) results) + (push (clsql:in-transaction-p) results) ;;Putin has got to go (unless (eql *test-database-type* :mysql) - (usql:delete-records :from [employee] :where [= [last-name] "Putin"])) + (clsql:delete-records :from [employee] :where [= [last-name] "Putin"])) ;;Should be nil (push - (usql:select [*] :from [employee] :where [= [last-name] "Putin"]) + (clsql:select [*] :from [employee] :where [= [last-name] "Putin"]) results) ;;Oh no, he's still there - (usql:rollback) + (clsql:rollback) ;; test that we are out of the transaction - (push (usql:in-transaction-p) results) + (push (clsql:in-transaction-p) results) ;; Check that we got him back alright - (push (usql:select [email] :from [employee] :where [= [last-name] "Putin"] + (push (clsql:select [email] :from [employee] :where [= [last-name] "Putin"] :flatp t) results) (apply #'values (nreverse results))) @@ -302,27 +302,27 @@ (deftest :fdml/transaction/2 (let ((results '())) ;; test if we are in a transaction - (push (usql:in-transaction-p) results) + (push (clsql:in-transaction-p) results) ;;start a transaction - (usql:start-transaction) + (clsql:start-transaction) ;; test if we are in a transaction - (push (usql:in-transaction-p) results) + (push (clsql:in-transaction-p) results) ;;Putin has got to go (unless (eql *test-database-type* :mysql) - (usql:update-records [employee] + (clsql:update-records [employee] :av-pairs '((email "putin-nospam@soviet.org")) :where [= [last-name] "Putin"])) ;;Should be new value - (push (usql:select [email] :from [employee] + (push (clsql:select [email] :from [employee] :where [= [last-name] "Putin"] :flatp t) results) ;;Oh no, he's still there - (usql:rollback) + (clsql:rollback) ;; test that we are out of the transaction - (push (usql:in-transaction-p) results) + (push (clsql:in-transaction-p) results) ;; Check that we got him back alright - (push (usql:select [email] :from [employee] :where [= [last-name] "Putin"] + (push (clsql:select [email] :from [employee] :where [= [last-name] "Putin"] :flatp t) results) (apply #'values (nreverse results))) @@ -332,31 +332,31 @@ (deftest :fdml/transaction/3 (let ((results '())) ;; check status - (push (usql:in-transaction-p) results) + (push (clsql:in-transaction-p) results) ;; update records (push - (usql:with-transaction () - (usql:update-records [employee] + (clsql:with-transaction () + (clsql:update-records [employee] :av-pairs '((email "lenin-nospam@soviet.org")) :where [= [emplid] 1])) results) ;; check status - (push (usql:in-transaction-p) results) + (push (clsql:in-transaction-p) results) ;; check that was committed - (push (usql:select [email] :from [employee] :where [= [emplid] 1] + (push (clsql:select [email] :from [employee] :where [= [emplid] 1] :flatp t) results) ;; undo the changes (push - (usql:with-transaction () - (usql:update-records [employee] + (clsql:with-transaction () + (clsql:update-records [employee] :av-pairs '((email "lenin@soviet.org")) :where [= [emplid] 1])) results) ;; and check status - (push (usql:in-transaction-p) results) + (push (clsql:in-transaction-p) results) ;; check that was committed - (push (usql:select [email] :from [employee] :where [= [emplid] 1] + (push (clsql:select [email] :from [employee] :where [= [emplid] 1] :flatp t) results) (apply #'values (nreverse results))) @@ -368,28 +368,28 @@ (deftest :fdml/transaction/4 (let ((results '())) ;; check status - (push (usql:in-transaction-p) results) + (push (clsql:in-transaction-p) results) (unless (eql *test-database-type* :mysql) (handler-case - (usql:with-transaction () + (clsql:with-transaction () ;; valid update - (usql:update-records [employee] + (clsql:update-records [employee] :av-pairs '((email "lenin-nospam@soviet.org")) :where [= [emplid] 1]) ;; invalid update which generates an error - (usql:update-records [employee] + (clsql:update-records [employee] :av-pairs '((emale "lenin-nospam@soviet.org")) :where [= [emplid] 1])) - (usql:clsql-sql-error () + (clsql:clsql-sql-error () (progn ;; check status - (push (usql:in-transaction-p) results) + (push (clsql:in-transaction-p) results) ;; and check nothing done - (push (usql:select [email] :from [employee] :where [= [emplid] 1] + (push (clsql:select [email] :from [employee] :where [= [emplid] 1] :flatp t) results) (apply #'values (nreverse results))))))) nil nil ("lenin@soviet.org")) -#.(usql:restore-sql-reader-syntax-state) +#.(clsql:restore-sql-reader-syntax-state)