(module test-sql mzscheme ;;; ; TODO: ; - finish the tests from ; - tests for SELECT (require (require (planet "78.ss" ("soegaard" "srfi.plt"))) ;(planet "srfi-check.ss" ("soegaard" "srfi.plt" 1 1)) (planet "sqlite.ss" ("jaymccarthy" "sqlite.plt")) "sql.scm") (define db (open (string->path "test-sql.db"))) ;(create-expr-test-table) ;(create-expr-test-table2) ; ; ###### ### ### ##### ##### ###### ### # ### # ##### ### ### ### ; # # # # # # # # # # # ## # ## # # # ## # ; # # # # # # # # # # # # # # # # # # ; ### # # # # # ### #### #### # # # # # # ; # # # #### #### # # # # # # # # # # ; # # # # # # # # # # # # # # # ; # # # # # # # # # ## # ## # # # # # ## ; ###### ### ### ### ### # ###### # ### # ### ##### ### ### ## ; ; (define (create-expr-test-table) (exec/ignore db "CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)") (exec/ignore db "INSERT INTO test1 VALUES(1,2,1.1,2.2,'hello','world')")) (define (create-expr-test-table2) (exec/ignore db "CREATE TABLE test2(a int, b int)") (do ((i 1 (add1 i))) ((= i 21) (void)) (exec/ignore db (format "INSERT INTO test2 VALUES(~a,~a)" i (expt 2 i))))) (define (run-sql-expr sql-str) (let ([result #f]) (exec db sql-str (lambda (colnames rows) (set! result (vector-ref rows 0)) 0)) result)) (define-syntax check-expr (syntax-rules () [(check-expr name (assignment ...) expr expected-result) (check (begin 'name 'expr (run-sql-expr (string-append "BEGIN; " (sql (UPDATE test1 SET assignment ...)) "; " (sql (SELECT expr FROM test1)) "; " "ROLLBACK;"))) => expected-result)])) ;(exec db ; (check-expr 'expr-1.1 ((i1 = 10) (i2 = 20)) ((+ i1 i2)) 30) ; (lambda (col-names rows) ; (display (list col-names rows)) ; 0)) ; => (#(i1 + i2) #(30)) ;(create-expr-test-table) (check-expr expr-1.1 ((i1 = 10) (i2 = 20)) ((+ i1 i2)) "30") (check-expr expr-1.2 ((i1 = 10) (i2 = 20)) ((- i1 i2)) "-10") (check-expr expr-1.3 ((i1 = 10) (i2 = 20)) ((* i1 i2)) "200") (check-expr expr-1.4 ((i1 = 10) (i2 = 20)) ((/ i1 i2)) "0") (check-expr expr-1.5 ((i1 = 10) (i2 = 20)) ((/ i2 i1)) "2") (check-expr expr-1.6 ((i1 = 10) (i2 = 20)) ((< i2 i1)) "0") (check-expr expr-1.7 ((i1 = 10) (i2 = 20)) ((<= i2 i1)) "0") (check-expr expr-1.8 ((i1 = 10) (i2 = 20)) ((> i2 i1)) "1") (check-expr expr-1.9 ((i1 = 10) (i2 = 20)) ((>= i2 i1)) "1") (check-expr expr-1.10 ((i1 = 10) (i2 = 20)) ((!= i2 i1)) "1") (check-expr expr-1.11 ((i1 = 10) (i2 = 20)) ((= i2 i1)) "0") (check-expr expr-1.12 ((i1 = 10) (i2 = 20)) ((<> i2 i1)) "1") (check-expr expr-1.13 ((i1 = 10) (i2 = 20)) ((== i2 i1)) "0") (check-expr expr-1.14 ((i1 = 20) (i2 = 20)) ((< i2 i1)) "0") (check-expr expr-1.15 ((i1 = 20) (i2 = 20)) ((<= i2 i1)) "1") (check-expr expr-1.16 ((i1 = 20) (i2 = 20)) ((> i2 i1)) "0") (check-expr expr-1.17 ((i1 = 20) (i2 = 20)) ((>= i2 i1)) "1") (check-expr expr-1.18 ((i1 = 20) (i2 = 20)) ((!= i2 i1)) "0") (check-expr expr-1.19 ((i1 = 20) (i2 = 20)) ((= i2 i1)) "1") (check-expr expr-1.20 ((i1 = 20) (i2 = 20)) ((> i2 i1)) "0") (check-expr expr-1.21 ((i1 = 20) (i2 = 20)) ((>= i2 i1)) "1") (check-expr expr-1.22 ((i1 = 1) (i2 = 2) (r1 = 3.0)) ((+ i1 (* i2 r1))) "7.0") (check-expr expr-1.23 ((i1 = 1) (i2 = 2) (r1 = 3.0)) ((* (+ i1 i2) r1)) "9.0") (check-expr expr-1.24 ((i1 = 1) (i2 = 2)) ((min i1 i2 (+ i1 i2) (- i1 i2))) "-1") (check-expr expr-1.25 ((i1 = 1) (i2 = 2)) ((max i1 i2 (+ i1 i2) (- i1 i2))) "3") (check-expr expr-1.26 ((i1 = 1) (i2 = 2)) ((max i1 i2 (+ i1 i2) (- i1 i2))) "3") (check-expr expr-1.27 ((i1 = 1) (i2 = 2)) ((AND (== i1 1) (= i2 2))) "1") (check-expr expr-1.28 ((i1 = 1) (i2 = 2)) ((AND (= i1 2) (= i2 1))) "0") (check-expr expr-1.29 ((i1 = 1) (i2 = 2)) ((AND (= i1 1) (= i2 1))) "0") (check-expr expr-1.30 ((i1 = 1) (i2 = 2)) ((AND (= i1 2) (= i2 2))) "0") (check-expr expr-1.31 ((i1 = 1) (i2 = 2)) ((AND (== i1 1) (= i2 2))) "1") (check-expr expr-1.32 ((i1 = 1) (i2 = 2)) ((OR (= i1 2) (= i2 1))) "0") (check-expr expr-1.33 ((i1 = 1) (i2 = 2)) ((OR (= i1 1) (= i2 1))) "1") (check-expr expr-1.34 ((i1 = 1) (i2 = 2)) ((OR (= i1 2) (= i2 2))) "1") (check-expr expr-1.35 ((i1 = 1) (i2 = 2)) ((= (- i1 i2) -1)) "1") (check-expr expr-1.36 ((i1 = 1) (i2 = 0)) ((NOT i1)) "0") (check-expr expr-1.37 ((i1 = 1) (i2 = 0)) ((NOT i2)) "1") (check-expr expr-1.38 ((i1 = 1)) ((- i1)) "-1") (check-expr expr-1.39 ((i1 = 1)) ((+ i1)) "1") (check-expr expr-1.40 ((i1 = 1) (i2 = 2)) ((+ (+ i2 i1))) "3") (check-expr expr-1.41 ((i1 = 1) (i2 = 2)) ((- (+ i2 i1))) "-3") (check-expr expr-1.42 ((i1 = 1) (i2 = 2)) ((\| i1 i2)) "3") ; | needs escaping on the Scheme side (check-expr expr-1.42b ((i1 = 1) (i2 = 2)) ((\| 4 2)) "6") (check-expr expr-1.43 ((i1 = 1) (i2 = 2)) ((& i1 i2)) "0") (check-expr expr-1.43b ((i1 = 1) (i2 = 2)) ((& 4 5)) "4") (check-expr expr-1.44 ((i1 = 1)) ((~ i1)) "-2") (check-expr expr-1.45 ((i1 = 1) (i2 = 3)) ((<< i1 i2)) "8") (check-expr expr-1.46 ((i1 = 32) (i2 = 3)) ((>> i1 i2)) "4") (check-expr expr-1.47 ((i1 = 9999999999) (i2 = 8888888888)) ((< i1 i2)) "0") (check-expr expr-1.48 ((i1 = 9999999999) (i2 = 8888888888)) ((= i1 i2)) "0") (check-expr expr-1.49 ((i1 = 9999999999) (i2 = 8888888888)) ((> i1 i2)) "1") (check-expr expr-1.50 ((i1 = 99999999999) (i2 = 99999999998)) ((< i1 i2)) "0") (check-expr expr-1.51 ((i1 = 99999999999) (i2 = 99999999998)) ((= i1 i2)) "0") (check-expr expr-1.52 ((i1 = 99999999999) (i2 = 99999999998)) ((> i1 i2)) "1") (check-expr expr-1.53 ((i1 = 099999999999) (i2 = 99999999999)) ((< i1 i2)) "0") (check-expr expr-1.54 ((i1 = 099999999999) (i2 = 99999999999)) ((= i1 i2)) "1") (check-expr expr-1.55 ((i1 = 099999999999) (i2 = 99999999999)) ((> i1 i2)) "0") (check-expr expr-1.56 ((i1 = 25) (i2 = 11)) ((% i1 i2)) "3") (check-expr expr-1.58 ((i1 = NULL) (i2 = 1)) ((coalesce (+ i1 i2) 99)) "99") (check-expr expr-1.59 ((i1 = 1) (i2 = NULL)) ((coalesce (+ i1 i2) 99)) "99") (check-expr expr-1.60 ((i1 = NULL) (i2 = NULL)) ((coalesce (+ i1 i2) 99)) "99") (check-expr expr-1.61 ((i1 = NULL) (i2 = 11)) ((coalesce (- i1 i2) 99)) "99") (check-expr expr-1.62 ((i1 = 1) (i2 = NULL)) ((coalesce (- i1 i2) 99)) "99") (check-expr expr-1.63 ((i1 = NULL) (i2 = NULL)) ((coalesce (- i1 i2) 99)) "99") (check-expr expr-1.64 ((i1 = NULL) (i2 = 1)) ((coalesce (* i1 i2) 99)) "99") (check-expr expr-1.65 ((i1 = 1) (i2 = NULL)) ((coalesce (* i1 i2) 99)) "99") (check-expr expr-1.66 ((i1 = NULL) (i2 = NULL)) ((coalesce (* i1 i2) 99)) "99") (check-expr expr-1.67 ((i1 = NULL) (i2 = 1)) ((coalesce (/ i1 i2) 99)) "99") (check-expr expr-1.68 ((i1 = 1) (i2 = NULL)) ((coalesce (/ i1 i2) 99)) "99") (check-expr expr-1.69 ((i1 = NULL) (i2 = NULL)) ((coalesce (/ i1 i2) 99)) "99") (check-expr expr-1.70 ((i1 = NULL) (i2 = 1)) ((coalesce (< i1 i2) 99)) "99") (check-expr expr-1.71 ((i1 = 1) (i2 = NULL)) ((coalesce (> i1 i2) 99)) "99") (check-expr expr-1.72 ((i1 = NULL) (i2 = NULL)) ((coalesce (<= i1 i2) 99)) "99") (check-expr expr-1.73 ((i1 = NULL) (i2 = 1)) ((coalesce (>= i1 i2) 99)) "99") (check-expr expr-1.74 ((i1 = 1) (i2 = NULL)) ((coalesce (!= i1 i2) 99)) "99") (check-expr expr-1.75 ((i1 = NULL) (i2 = NULL)) ((coalesce (== i1 i2) 99)) "99") (check-expr expr-1.76 ((i1 = NULL) (i2 = NULL)) ((coalesce (NOT i1) 99)) "99") (check-expr expr-1.77 ((i1 = NULL) (i2 = NULL)) ((coalesce (AND (ISNULL i1) (= i2 5)) 99)) "99") (check-expr expr-1.78 ((i1 = NULL) (i2 = NULL)) ((coalesce (AND (ISNULL i1) (= i2 5)) 99)) "99") (check-expr expr-1.79 ((i1 = NULL) (i2 = NULL)) ((coalesce (OR (ISNULL i1) (= i2 5)) 99)) "1") (check-expr expr-1.80 ((i1 = NULL) (i2 = NULL)) ((coalesce (AND (= i1 5) (ISNULL i2) ) 99)) "99") (check-expr expr-1.81 ((i1 = NULL) (i2 = NULL)) ((coalesce (OR (= i1 5) (ISNULL i2) ) 99)) "1") (check-expr expr-1.82 ((i1 = NULL) (i2 = 3)) ((coalesce (min i1 i2 1) 99)) "99") (check-expr expr-1.83 ((i1 = NULL) (i2 = 3)) ((coalesce (max i1 i2 1) 99)) "99") (check-expr expr-1.84 ((i1 = 3) (i2 = NULL)) ((coalesce (min i1 i2 1) 99)) "99") (check-expr expr-1.85 ((i1 = 3) (i2 = NULL)) ((coalesce (max i1 i2 1) 99)) "99") (check-expr expr-1.86 ((i1 = 3) (i2 = 8)) ((BETWEEN 5 i1 i2)) "1") (check-expr expr-1.87 ((i1 = 3) (i2 = 8)) ((NOT BETWEEN 5 i1 i2)) "0") (check-expr expr-1.88 ((i1 = 3) (i2 = 8)) ((BETWEEN 55 i1 i2)) "0") (check-expr expr-1.89 ((i1 = 3) (i2 = 8)) ((NOT BETWEEN 55 i1 i2)) "1") (check-expr expr-1.90 ((i1 = 3) (i2 = NULL)) ((BETWEEN 5 i1 i2)) #f) (check-expr expr-1.91 ((i1 = 3) (i2 = NULL)) ((NOT BETWEEN 5 i1 i2)) #f) (check-expr expr-1.92 ((i1 = 3) (i2 = NULL)) ((BETWEEN 2 i1 i2)) "0") (check-expr expr-1.93 ((i1 = 3) (i2 = NULL)) ((NOT BETWEEN 2 i1 i2)) "1") (check-expr expr-1.94 ((i1 = NULL) (i2 = 8)) ((BETWEEN 2 i1 i2)) #f) (check-expr expr-1.95 ((i1 = NULL) (i2 = 8)) ((NOT BETWEEN 2 i1 i2)) #f) (check-expr expr-1.95b ((i1 = NULL) (i2 = 8)) ((BETWEEN 55 i1 i2)) "0") (check-expr expr-1.95c ((i1 = NULL) (i2 = 8)) ((NOT BETWEEN 55 i1 i2)) "1") (check-expr expr-1.96 ((i1 = NULL) (i2 = 3)) ((coalesce (<< i1 i2) 99)) "99") (check-expr expr-1.97 ((i1 = 32) (i2 = NULL)) ((coalesce (>> i1 i2) 99)) "99") (check-expr expr-1.98 ((i1 = NULL) (i2 = NULL)) ((coalesce (\| i1 i2) 99)) "99") (check-expr expr-1.99 ((i1 = 32) (i2 = NULL)) ((coalesce (& i1 i2) 99)) "99") (check-expr expr-1.100 ((i1 = 1) (i2 = "")) ((= i1 i2)) "0") (check-expr expr-1.101 ((i1 = 0) (i2 = "")) ((= i1 i2)) "0") (check-expr expr-1.102 ((i1 = 40) (i2 = 1)) ((<< i2 i1)) "1099511627776") (check-expr expr-2.1 ((r1 = 1.23) (r2 = 2.34)) ((+ r1 r2)) "3.57") (check-expr expr-2.2 ((r1 = 1.23) (r2 = 2.34)) ((- r1 r2)) "-1.11") (check-expr expr-2.3 ((r1 = 1.23) (r2 = 2.34)) ((* r1 r2)) "2.8782") (check-expr expr-2.4 ((r1 = 1.23) (r2 = 2.34)) ((/ r1 r2)) "0.525641025641026") (check-expr expr-2.5 ((r1 = 1.23) (r2 = 2.34)) ((/ r2 r1)) "1.90243902439024") (check-expr expr-2.6 ((r1 = 1.23) (r2 = 2.34)) ((< r2 r1)) "0") (check-expr expr-2.7 ((r1 = 1.23) (r2 = 2.34)) ((<= r2 r1)) "0") (check-expr expr-2.8 ((r1 = 1.23) (r2 = 2.34)) ((> r2 r1)) "1") (check-expr expr-2.9 ((r1 = 1.23) (r2 = 2.34)) ((>= r2 r1)) "1") (check-expr expr-2.10 ((r1 = 1.23) (r2 = 2.34)) ((!= r2 r1)) "1") (check-expr expr-2.11 ((r1 = 1.23) (r2 = 2.34)) ((= r2 r1)) "0") (check-expr expr-2.12 ((r1 = 1.23) (r2 = 2.34)) ((<> r2 r1)) "1") (check-expr expr-2.13 ((r1 = 1.23) (r2 = 2.34)) ((== r2 r1)) "0") (check-expr expr-2.14 ((r1 = 2.34) (r2 = 2.34)) ((< r2 r1)) "0") (check-expr expr-2.15 ((r1 = 2.34) (r2 = 2.34)) ((<= r2 r1)) "1") (check-expr expr-2.16 ((r1 = 2.34) (r2 = 2.34)) ((> r2 r1)) "0") (check-expr expr-2.17 ((r1 = 2.34) (r2 = 2.34)) ((>= r2 r1)) "1") (check-expr expr-2.18 ((r1 = 2.34) (r2 = 2.34)) ((!= r2 r1)) "0") (check-expr expr-2.19 ((r1 = 2.34) (r2 = 2.34)) ((= r2 r1)) "1") (check-expr expr-2.20 ((r1 = 2.34) (r2 = 2.34)) ((<> r2 r1)) "0") (check-expr expr-2.21 ((r1 = 2.34) (r2 = 2.34)) ((== r2 r1)) "1") (check-expr expr-2.22 ((r1 = 1.23) (r2 = 2.34)) ((min r1 r2 (+ r1 r2) (- r1 r2))) "-1.11") (check-expr expr-2.23 ((r1 = 1.23) (r2 = 2.34)) ((max r1 r2 (+ r1 r2) (- r1 r2))) "3.57") (check-expr expr-2.24 ((r1 = 25.0) (r2 = 11.0)) ((% r1 r2)) "3.0") (check-expr expr-2.25 ((r1 = 1.23) (r2 = NULL)) ((coalesce (+ r1 r2) 99.0)) "99.0") (check-expr expr-3.1 ((t1 = "abc") (t2 = "xyz")) ((< t1 t2)) "1") (check-expr expr-3.2 ((t1 = "xyz") (t2 = "abc")) ((< t1 t2)) "0") (check-expr expr-3.3 ((t1 = "abc") (t2 = "abc")) ((< t1 t2)) "0") (check-expr expr-3.4 ((t1 = "abc") (t2 = "xyz")) ((<= t1 t2)) "1") (check-expr expr-3.5 ((t1 = "xyz") (t2 = "abc")) ((<= t1 t2)) "0") (check-expr expr-3.6 ((t1 = "abc") (t2 = "abc")) ((<= t1 t2)) "1") (check-expr expr-3.7 ((t1 = "abc") (t2 = "xyz")) ((> t1 t2)) "0") (check-expr expr-3.8 ((t1 = "xyz") (t2 = "abc")) ((> t1 t2)) "1") (check-expr expr-3.9 ((t1 = "abc") (t2 = "abc")) ((> t1 t2)) "0") (check-expr expr-3.10 ((t1 = "abc") (t2 = "xyz")) ((>= t1 t2)) "0") (check-expr expr-3.11 ((t1 = "xyz") (t2 = "abc")) ((>= t1 t2)) "1") (check-expr expr-3.12 ((t1 = "abc") (t2 = "abc")) ((>= t1 t2)) "1") (check-expr expr-3.13 ((t1 = "abc") (t2 = "xyz")) ((= t1 t2)) "0") (check-expr expr-3.14 ((t1 = "xyz") (t2 = "abc")) ((= t1 t2)) "0") (check-expr expr-3.15 ((t1 = "abc") (t2 = "abc")) ((= t1 t2)) "1") (check-expr expr-3.16 ((t1 = "abc") (t2 = "xyz")) ((== t1 t2)) "0") (check-expr expr-3.17 ((t1 = "xyz") (t2 = "abc")) ((== t1 t2)) "0") (check-expr expr-3.18 ((t1 = "abc") (t2 = "abc")) ((== t1 t2)) "1") (check-expr expr-3.19 ((t1 = "abc") (t2 = "xyz")) ((<> t1 t2)) "1") (check-expr expr-3.20 ((t1 = "xyz") (t2 = "abc")) ((<> t1 t2)) "1") (check-expr expr-3.21 ((t1 = "abc") (t2 = "abc")) ((<> t1 t2)) "0") (check-expr expr-3.22 ((t1 = "abc") (t2 = "xyz")) ((!= t1 t2)) "1") (check-expr expr-3.23 ((t1 = "xyz") (t2 = "abc")) ((!= t1 t2)) "1") (check-expr expr-3.24 ((t1 = "abc") (t2 = "abc")) ((!= t1 t2)) "0") (check-expr expr-3.25 ((t1 = NULL) (t2 = "hi")) ((ISNULL t1)) "1") (check-expr expr-3.25b ((t1 = NULL) (t2 = "hi")) ((ISNULL t1)) "1") (check-expr expr-3.26 ((t1 = NULL) (t2 = "hi")) ((ISNULL t2)) "0") (check-expr expr-3.27 ((t1 = NULL) (t2 = "hi")) ((NOTNULL t1)) "0") (check-expr expr-3.28 ((t1 = NULL) (t2 = "hi")) ((NOTNULL t2)) "1") (check-expr expr-3.28b ((t1 = NULL) (t2 = "hi")) ((NOTNULL t2)) "1") (check-expr expr-3.29 ((t1 = "xyz") (t2 = "abc")) ((\|\| t1 t2)) "xyzabc") ; || double bar (check-expr expr-3.30 ((t1 = NULL) (t2 = "abc")) ((\|\| t1 t2)) #f) (check-expr expr-3.31 ((t1 = "xyz") (t2 = NULL)) ((\|\| t1 t2)) #f) (check-expr expr-3.32 ((t1 = "xyz") (t2 = "abc")) ((\|\| t1 (\|\| " hi " t2))) "xyz hi abc") (check-expr expr-3.33 ((t1 = "abc") (t2 = NULL)) ((coalesce (< t1 t2) 99)) "99") (check-expr expr-3.34 ((t1 = "abc") (t2 = NULL)) ((coalesce (< t2 t1) 99)) "99") (check-expr expr-3.35 ((t1 = "abc") (t2 = NULL)) ((coalesce (> t1 t2) 99)) "99") (check-expr expr-3.36 ((t1 = "abc") (t2 = NULL)) ((coalesce (> t2 t1) 99)) "99") (check-expr expr-3.37 ((t1 = "abc") (t2 = NULL)) ((coalesce (<= t1 t2) 99)) "99") (check-expr expr-3.38 ((t1 = "abc") (t2 = NULL)) ((coalesce (<= t2 t1) 99)) "99") (check-expr expr-3.39 ((t1 = "abc") (t2 = NULL)) ((coalesce (>= t1 t2) 99)) "99") (check-expr expr-3.40 ((t1 = "abc") (t2 = NULL)) ((coalesce (>= t2 t1) 99)) "99") (check-expr expr-3.41 ((t1 = "abc") (t2 = NULL)) ((coalesce (== t1 t2) 99)) "99") (check-expr expr-3.42 ((t1 = "abc") (t2 = NULL)) ((coalesce (== t2 t1) 99)) "99") (check-expr expr-3.43 ((t1 = "abc") (t2 = NULL)) ((coalesce (!= t1 t2) 99)) "99") (check-expr expr-3.44 ((t1 = "abc") (t2 = NULL)) ((coalesce (!= t2 t1) 99)) "99") (check-expr expr-4.1 ((t1 = "abc") (t2 = "Abc")) ((< t1 t2)) "0") (check-expr expr-4.2 ((t1 = "abc") (t2 = "Abc")) ((> t1 t2)) "1") (check-expr expr-4.3 ((t1 = "abc") (t2 = "Bbc")) ((< t1 t2)) "0") (check-expr expr-4.4 ((t1 = "abc") (t2 = "Bbc")) ((> t1 t2)) "1") (check-expr expr-4.5 ((t1 = "0") (t2 = "0.0")) ((== t1 t2)) "0") (check-expr expr-4.6 ((t1 = "0.000") (t2 = "0.0")) ((== t1 t2)) "0") (check-expr expr-4.7 ((t1 = " 0.000") (t2 = "0.0")) ((== t1 t2)) "0") (check-expr expr-4.8 ((t1 = "0.0") (t2 = "abc")) ((< t1 t2)) "1") (check-expr expr-4.9 ((t1 = "0.0") (t2 = "abc")) ((== t1 t2)) "0") (check-expr expr-4.10 ((r1 = "0.0") (r2 = "abc")) ((> r1 r2)) "0") (check-expr expr-4.11 ((r1 = "abc") (r2 = "Abc")) ((< r1 r2)) "0") (check-expr expr-4.12 ((r1 = "abc") (r2 = "Abc")) ((> r1 r2)) "1") (check-expr expr-4.13 ((r1 = "abc") (r2 = "Bbc")) ((< r1 r2)) "0") (check-expr expr-4.14 ((r1 = "abc") (r2 = "Bbc")) ((> r1 r2)) "1") (check-expr expr-4.15 ((r1 = "0") (r2 = "0.0")) ((== r1 r2)) "1") (check-expr expr-4.16 ((r1 = "0.000") (r2 = "0.0")) ((== r1 r2)) "1") (check-expr expr-4.17 ((r1 = " 0.000") (r2 = " 0.0")) ((== r1 r2)) "0") (check-expr expr-4.18 ((r1 = "0.0") (r2 = "abc")) ((< r1 r2)) "1") (check-expr expr-4.19 ((r1 = "0.0") (r2 = "abc")) ((== r1 r2)) "0") (check-expr expr-4.20 ((r1 = "0.0") (r2 = "abc")) ((> r1 r2)) "0") ;# CSL is true if LIKE is case sensitive and false if not. ;# NCSL is the opposite. Use these variables as the result (define CSL "0") (define NCSL "1") (check-expr expr-5.1 ((t1 = "abc") (t2 = "xyz")) ((LIKE t1 t2)) "0") (check-expr expr-5.2a ((t1 = "abc") (t2 = "abc")) ((LIKE t1 t2)) "1") (check-expr expr-5.2b ((t1 = "abc") (t2 = "ABC")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.2c ((t1 = "abc") (t2 = "a_c")) ((LIKE t1 t2)) "1") (check-expr expr-5.3b ((t1 = "abc") (t2 = "A_C")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.4 ((t1 = "abc") (t2 = "abc_")) ((LIKE t1 t2)) "0") (check-expr expr-5.5a ((t1 = "abc") (t2 = "a%c")) ((LIKE t1 t2)) "1") (check-expr expr-5.5b ((t1 = "abc") (t2 = "A%C")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.5c ((t1 = "abdc") (t2 = "a%c")) ((LIKE t1 t2)) "1") (check-expr expr-5.5d ((t1 = "ac") (t2 = "a%c")) ((LIKE t1 t2)) "1") (check-expr expr-5.5e ((t1 = "ac") (t2 = "A%C")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.6a ((t1 = "abxyzzyc") (t2 = "a%c")) ((LIKE t1 t2)) "1") (check-expr expr-5.6b ((t1 = "abxyzzyc") (t2 = "A%C")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.7a ((t1 = "abxyzzy") (t2 = "a%c")) ((LIKE t1 t2)) "0") (check-expr expr-5.7b ((t1 = "abxyzzy") (t2 = "A%C")) ((LIKE t1 t2)) "0") (check-expr expr-5.8a ((t1 = "abxyzzycx") (t2 = "a%c")) ((LIKE t1 t2)) "0") (check-expr expr-5.8b ((t1 = "abxyzzycy") (t2 = "a%cx")) ((LIKE t1 t2)) "0") (check-expr expr-5.8c ((t1 = "abxyzzycx") (t2 = "A%C")) ((LIKE t1 t2)) "0") (check-expr expr-5.8d ((t1 = "abxyzzycy") (t2 = "A%CX")) ((LIKE t1 t2)) "0") (check-expr expr-5.9a ((t1 = "abc") (t2 = "a%_c")) ((LIKE t1 t2)) "1") (check-expr expr-5.9b ((t1 = "ac") (t2 = "a%_c")) ((LIKE t1 t2)) "0") (check-expr expr-5.9c ((t1 = "abc") (t2 = "A%_C")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.9d ((t1 = "ac") (t2 = "A%_C")) ((LIKE t1 t2)) "0") (check-expr expr-5.10a ((t1 = "abxyzzyc") (t2 = "a%_c")) ((LIKE t1 t2)) "1") (check-expr expr-5.10b ((t1 = "abxyzzyc") (t2 = "A%_C")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.11 ((t1 = "abc") (t2 = "xyz")) ((NOT LIKE t1 t2)) "1") (check-expr expr-5.12a ((t1 = "abc") (t2 = "abc")) ((NOT LIKE t1 t2)) "0") (check-expr expr-5.12b ((t1 = "abc") (t2 = "ABC")) ((NOT LIKE t1 t2)) CSL) ; The following tests only work on versions of TCL that support Unicode ; { (check-expr expr-5.13a ((t1 = "a\u0080c") (t2 = "a_c")) ((LIKE t1 t2)) "1") (check-expr expr-5.13b ((t1 = "a\u0080c") (t2 = "A_C")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.14a ((t1 = "a\u07FFc") (t2 = "a_c")) ((LIKE t1 t2)) "1") (check-expr expr-5.14b ((t1 = "a\u07FFc") (t2 = "A_C")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.15a ((t1 = "a\u0800c") (t2 = "a_c")) ((LIKE t1 t2)) "1") (check-expr expr-5.15b ((t1 = "a\u0800c") (t2 = "A_C")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.16a ((t1 = "a\uFFFFc") (t2 = "a_c")) ((LIKE t1 t2)) "1") (check-expr expr-5.16b ((t1 = "a\uFFFFc") (t2 = "A_C")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.17 ((t1 = "a\u0080") (t2 = "A__")) ((LIKE t1 t2)) "0") (check-expr expr-5.18 ((t1 = "a\u07FF") (t2 = "A__")) ((LIKE t1 t2)) "0") (check-expr expr-5.19 ((t1 = "a\u0800") (t2 = "A__")) ((LIKE t1 t2)) "0") (check-expr expr-5.20 ((t1 = "a\uFFFF") (t2 = "A__")) ((LIKE t1 t2)) "0") (check-expr expr-5.21a ((t1 = "ax\uABCD") (t2 = "a_\uABCD")) ((LIKE t1 t2)) "1") (check-expr expr-5.21b ((t1 = "ax\uABCD") (t2 = "A_\uABCD")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.22a ((t1 = "ax\u1234") (t2 = "a%\u1234")) ((LIKE t1 t2)) "1") (check-expr expr-5.22b ((t1 = "ax\u1234") (t2 = "A%\u1234")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.23a ((t1 = "ax\uFEDC") (t2 = "a_%")) ((LIKE t1 t2)) "1") (check-expr expr-5.23b ((t1 = "ax\uFEDC") (t2 = "A_%")) ((LIKE t1 t2)) NCSL) (check-expr expr-5.24a ((t1 = "ax\uFEDCy\uFEDC") (t2 = "a%\uFEDC")) ((LIKE t1 t2)) "1") (check-expr expr-5.24b ((t1 = "ax\uFEDCy\uFEDC") (t2 = "A%\uFEDC")) ((LIKE t1 t2)) NCSL) ; } (check-expr expr-5.54 ((t1 = "abc") (t2 = NULL)) ((LIKE t1 t2)) #f) (check-expr expr-5.55 ((t1 = "abc") (t2 = NULL)) ((NOT LIKE t1 t2)) #f) (check-expr expr-5.56 ((t1 = "abc") (t2 = NULL)) ((LIKE t2 t1)) #f) (check-expr expr-5.57 ((t1 = "abc") (t2 = NULL)) ((NOT LIKE t2 t1)) #f) ; LIKE expressions that use ESCAPE characters. (check-expr expr-5.58a ((t1 = "abc") (t2 = "a_c")) ((LIKE t1 t2 ESCAPE "7")) "1") (check-expr expr-5.58b ((t1 = "abc") (t2 = "A_C")) ((LIKE t1 t2 ESCAPE "7")) NCSL) (check-expr expr-5.59b ((t1 = "a_c") (t2 = "a7_c")) ((LIKE t1 t2 ESCAPE "7")) "1") (check-expr expr-5.60a ((t1 = "a_c") (t2 = "A7_C")) ((LIKE t1 t2 ESCAPE "7")) NCSL) (check-expr expr-5.60a ((t1 = "abc") (t2 = "a7_c")) ((LIKE t1 t2 ESCAPE "7")) "0") (check-expr expr-5.60b ((t1 = "abc") (t2 = "A7_C")) ((LIKE t1 t2 ESCAPE "7")) "0") (check-expr expr-5.61a ((t1 = "a7Xc") (t2 = "a7_c")) ((LIKE t1 t2 ESCAPE "7")) "0") (check-expr expr-5.61b ((t1 = "a7Xc") (t2 = "A7_C")) ((LIKE t1 t2 ESCAPE "7")) "0") (check-expr expr-5.62a ((t1 = "abcde") (t2 = "a%e")) ((LIKE t1 t2 ESCAPE "7")) "1") (check-expr expr-5.62b ((t1 = "abcde") (t2 = "A%E")) ((LIKE t1 t2 ESCAPE "7")) NCSL) (check-expr expr-5.63a ((t1 = "abcde") (t2 = "a7_e")) ((LIKE t1 t2 ESCAPE "7")) "0") (check-expr expr-5.61b ((t1 = "abcde") (t2 = "A7_E")) ((LIKE t1 t2 ESCAPE "7")) "0") (check-expr expr-5.64a ((t1 = "a7cde") (t2 = "a7%e")) ((LIKE t1 t2 ESCAPE "7")) "0") (check-expr expr-5.64b ((t1 = "a7cde") (t2 = "A7%E")) ((LIKE t1 t2 ESCAPE "7")) "0") (check-expr expr-5.65a ((t1 = "a7cde") (t2 = "a77%e")) ((LIKE t1 t2 ESCAPE "7")) "1") (check-expr expr-5.65b ((t1 = "a7cde") (t2 = "A77%E")) ((LIKE t1 t2 ESCAPE "7")) NCSL) (check-expr expr-5.66a ((t1 = "abc7") (t2 = "a%77")) ((LIKE t1 t2 ESCAPE "7")) "1") (check-expr expr-5.66b ((t1 = "abc7") (t2 = "A%77")) ((LIKE t1 t2 ESCAPE "7")) NCSL) (check-expr expr-5.67a ((t1 = "abc_") (t2 = "a%7_")) ((LIKE t1 t2 ESCAPE "7")) "1") (check-expr expr-5.67b ((t1 = "abc_") (t2 = "A%7_")) ((LIKE t1 t2 ESCAPE "7")) NCSL) (check-expr expr-5.68a ((t1 = "abc7") (t2 = "a%7_")) ((LIKE t1 t2 ESCAPE "7")) "0") (check-expr expr-5.68b ((t1 = "abc7") (t2 = "A%7_")) ((LIKE t1 t2 ESCAPE "7")) "0") ; skipped 5.69a to 5.79b (check-expr expr-6.1 ((t1 = "abc") (t2 = "xyz")) ((GLOB t1 t2)) "0") (check-expr expr-6.2 ((t1 = "abc") (t2 = "ABC")) ((GLOB t1 t2)) "0") (check-expr expr-6.3 ((t1 = "abc") (t2 = "A?C")) ((GLOB t1 t2)) "0") (check-expr expr-6.4 ((t1 = "abc") (t2 = "a?c")) ((GLOB t1 t2)) "1") (check-expr expr-6.5 ((t1 = "abc") (t2 = "abc?")) ((GLOB t1 t2)) "0") (check-expr expr-6.6 ((t1 = "abc") (t2 = "A*C")) ((GLOB t1 t2)) "0") (check-expr expr-6.7 ((t1 = "abc") (t2 = "a*c")) ((GLOB t1 t2)) "1") (check-expr expr-6.8 ((t1 = "abxyzzyc") (t2 = "a*c")) ((GLOB t1 t2)) "1") (check-expr expr-6.9 ((t1 = "abxyzzy") (t2 = "a*c")) ((GLOB t1 t2)) "0") (check-expr expr-6.10 ((t1 = "abxyzzycx") (t2 = "a*c")) ((GLOB t1 t2)) "0") (check-expr expr-6.11 ((t1 = "abc") (t2 = "xyz")) ((NOT GLOB t1 t2)) "1") (check-expr expr-6.12 ((t1 = "abc") (t2 = "abc")) ((NOT GLOB t1 t2)) "0") (check-expr expr-6.13 ((t1 = "abc") (t2 = "a[bx]c")) ((GLOB t1 t2)) "1") (check-expr expr-6.14 ((t1 = "abc") (t2 = "a[cx]c")) ((GLOB t1 t2)) "0") (check-expr expr-6.15 ((t1 = "abc") (t2 = "a[a-d]c")) ((GLOB t1 t2)) "1") (check-expr expr-6.16 ((t1 = "abc") (t2 = "a[^a-d]c")) ((GLOB t1 t2)) "0") (check-expr expr-6.17 ((t1 = "abc") (t2 = "a[A-Dc]c")) ((GLOB t1 t2)) "0") (check-expr expr-6.18 ((t1 = "abc") (t2 = "a[^A-Dc]c")) ((GLOB t1 t2)) "1") (check-expr expr-6.19 ((t1 = "abc") (t2 = "a[]b]c")) ((GLOB t1 t2)) "1") (check-expr expr-6.20 ((t1 = "abc") (t2 = "a[^]b]c")) ((GLOB t1 t2)) "0") (check-expr expr-6.21a ((t1 = "abcdefg") (t2 = "a*[de]g")) ((GLOB t1 t2)) "0") (check-expr expr-6.21b ((t1 = "abcdefg") (t2 = "a*[df]g")) ((GLOB t1 t2)) "1") (check-expr expr-6.21c ((t1 = "abcdefg") (t2 = "a*[d-h]g")) ((GLOB t1 t2)) "1") (check-expr expr-6.21d ((t1 = "abcdefg") (t2 = "a*[b-e]g")) ((GLOB t1 t2)) "0") (check-expr expr-6.22a ((t1 = "abcdefg") (t2 = "a*[^de]g")) ((GLOB t1 t2)) "1") (check-expr expr-6.22b ((t1 = "abcdefg") (t2 = "a*[^def]g")) ((GLOB t1 t2)) "0") (check-expr expr-6.23 ((t1 = "abcdefg") (t2 = "a*?g")) ((GLOB t1 t2)) "1") (check-expr expr-6.24 ((t1 = "ac") (t2 = "a*c")) ((GLOB t1 t2)) "1") (check-expr expr-6.25 ((t1 = "ac") (t2 = "a*?c")) ((GLOB t1 t2)) "0") (check-expr expr-6.26 ((t1 = "a*c") (t2 = "a[*]c")) ((GLOB t1 t2)) "1") (check-expr expr-6.27 ((t1 = "a?c") (t2 = "a[?]c")) ((GLOB t1 t2)) "1") (check-expr expr-6.28 ((t1 = "a[c") (t2 = "a[[]c")) ((GLOB t1 t2)) "1") ; skipped 6.26-6.66 (check-expr expr-6.67 ((t1 = "01") (t2 = 1)) ((= t1 t2)) "0") (check-expr expr-6.68 ((t1 = "1") (t2 = 1)) ((= t1 t2)) "1") (check-expr expr-6.69 ((t1 = "01") (t2 = 1)) ((= (CAST t1 AS INTEGER) t2)) "1") (check-expr expr-case.1 ((i1 = 1) (i2 = 2)) ((CASE (WHEN (= i1 i2) THEN "eq") (ELSE "ne"))) "ne") (check-expr expr-case.2 ((i1 = 2) (i2 = 2)) ((CASE (WHEN (= i1 i2) THEN "eq") (ELSE "ne"))) "eq") (check-expr expr-case.3 ((i1 = NULL) (i2 = 2)) ((CASE (WHEN (= i1 i2) THEN "eq") (ELSE "ne"))) "ne") (check-expr expr-case.4 ((i1 = 2) (i2 = NULL)) ((CASE (WHEN (= i1 i2) THEN "eq") (ELSE "ne"))) "ne") (check-expr expr-case.5 ((i1 = 2) (i2 = 2)) ((CASE i1 (WHEN 1 THEN "one") (WHEN 2 THEN "two") (ELSE "error"))) "two") (check-expr expr-case.6 ((i1 = 1)) ((CASE i1 (WHEN 1 THEN "one") (WHEN NULL THEN "two") (ELSE "error"))) "one") (check-expr expr-case.7 ((i1 = 2)) ((CASE i1 (WHEN 1 THEN "one") (WHEN NULL THEN "two") (ELSE "error"))) "error") (check-expr expr-case.8 ((i1 = 3)) ((CASE i1 (WHEN 1 THEN "one") (WHEN NULL THEN "two") (ELSE "error"))) "error") (check-expr expr-case.9 ((i1 = 3)) ((CASE i1 (WHEN 1 THEN "one") (WHEN 2 THEN "two") (ELSE "error"))) "error") (check-expr expr-case.10 ((i1 = 3)) ((CASE i1 (WHEN 1 THEN "one") (WHEN 2 THEN "two") )) #f) (check-expr expr-case.11 ((i1 = NULL)) ((CASE i1 (WHEN 1 THEN "one") (WHEN 2 THEN "two") (ELSE 3))) "3") (check-expr expr-case.12 ((i1 = 1)) ((CASE i1 (WHEN 1 THEN NULL) (WHEN 2 THEN "two") (ELSE 3))) #f) (check-expr expr-case.13 ((i1 = 7)) ((CASE (WHEN (< i1 5) THEN "low") (WHEN (< i1 10) THEN "medium") (WHEN (< i1 15) THEN "high") (ELSE "error"))) "medium") ; (create-expr-test-table2) (define-syntax run-sql-expr2 (syntax-rules () [(_ sql-str) (let ((result '())) (exec db (sql sql-str) (lambda (colnames rows) (set! result (append result (vector->list rows))) 0)) result)])) (define-syntax check-expr2 (syntax-rules () [(check-expr name expr expected-result) (check (begin 'name 'expr (run-sql-expr2 (SELECT (a) FROM test2 WHERE expr ORDER-BY (a)))) => expected-result)])) (check-expr2 'expr-7.2 (AND (< a 10) (> a 8)) '("9")) (check-expr2 'expr-7.3 (AND (<= a 10) (>= a 8)) '("8" "9" "10")) (check-expr2 'expr-7.4 (AND (>= a 8) (<= a 10)) '("8" "9" "10")) (check-expr2 'expr-7.5 (OR (>= a 20) (<= a 1)) '("1" "20")) (check-expr2 'expr-7.6 (AND (!= b 4) (<= a 3)) '("1" "3")) (check-expr2 'expr-7.7 (OR (== b 8) (OR (== b 16) (== b 32))) '("3" "4" "5")) (check-expr2 'expr-7.8 (OR (NOT (<> b 8)) (== b 1024)) '("3" "10")) (check-expr2 'expr-7.9 (LIKE b "10%") '("10" "20")) (check-expr2 'expr-7.10 (LIKE b "_4") '("6")) (check-expr2 'expr-7.11 (GLOB a "1?") '("10" "11" "12" "13" "14" "15" "16" "17" "18" "19" )) (check-expr2 'expr-7.12 (GLOB b "1*4") '("10" "14")) (check-expr2 'expr-7.13 (GLOB b "1[456]") '("4" )) (check-expr2 'expr-7.14 (ISNULL a) '()) (check-expr2 'expr-7.15 (AND (NOTNULL a) (< a 3)) '("1" "2")) (check-expr2 'expr-7.16 (AND a (< a 3)) '("1" "2")) (check-expr2 'expr-7.17 (NOT a) '()) (check-expr2 'expr-7.18 (OR (== a 11) (AND (> b 1000) (< b 2000))) '("10" "11")) (check-expr2 'expr-7.19 (OR (<= a 1) (>= a 20)) '("1" "20")) (check-expr2 'expr-7.20 (OR (< a 1) (> a 20)) '()) (check-expr2 'expr-7.21 (OR (> a 19) (< a 1)) '("20")) ;; Skipped the rest #| |# (check-report) (close db) )