tests/test-syntax.lisp

   1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
   2 ;;;; ======================================================================
   3 ;;;; File:    test-syntax.lisp
   4 ;;;; Author:  Marcus Pearce <m.t.pearce@city.ac.uk>
   5 ;;;; Created: 30/03/2004
   6 ;;;; Updated: $Id$
   7 ;;;; ======================================================================
   8 ;;;;
   9 ;;;; Description ==========================================================
  10 ;;;; ======================================================================
  11 ;;;;
  12 ;;;; Tests for the CLSQL Symbolic SQL syntax.
  13 ;;;;
  14 ;;;; ======================================================================
  15
  16 (in-package #:clsql-tests)
  17
  18 #.(clsql:locally-enable-sql-reader-syntax)
  19
  20
  21 (setq *rt-syntax*
  22       '(
  23
  24 (deftest :syntax/generic/1
  25     (clsql:sql "foo")
  26   "'foo'")
  27
  28 (deftest :syntax/generic/2
  29     (clsql:sql 23)
  30   "23")
  31
  32 (deftest :syntax/generic/3
  33     (clsql:sql 'bar)
  34   "BAR")
  35
  36 (deftest :syntax/generic/4
  37     (clsql:sql '("ten" 10 ten))
  38   "('ten',10,TEN)")
  39
  40 (deftest :syntax/generic/5
  41     (clsql:sql ["SELECT FOO,BAR FROM BAZ"])
  42   "SELECT FOO,BAR FROM BAZ")
  43
  44 (deftest :syntax/generic/6
  45     (clsql:sql "What's up Doc?")
  46   "'What''s up Doc?'")
  47
  48 (deftest :syntax/ident/1
  49     (clsql:sql [foo])
  50   "FOO")
  51
  52 (deftest :syntax/ident/2
  53     (clsql:sql [foo bar])
  54   "FOO.BAR")
  55
  56 (deftest :syntax/ident/3
  57     (clsql:sql [foo :integer])
  58   "FOO")
  59
  60 (deftest :syntax/ident/4
  61     (clsql:sql [foo bar :integer])
  62   "FOO.BAR")
  63
  64 (deftest :syntax/ident/5
  65     (clsql:sql [foo "bar"])
  66     "FOO \"bar\"")
  67
  68 (deftest :syntax/ident/6
  69     (clsql:sql ["foo" bar])
  70  "\"foo\".BAR")
  71
  72 (deftest :syntax/ident/7
  73     (clsql:sql ["foo" bar :integer])
  74  "\"foo\".BAR")
  75
  76
  77 (deftest :syntax/attribute/1
  78     (clsql:sql (clsql:sql-expression :table 'foo :attribute 'bar))
  79   "FOO.BAR")
  80
  81 (deftest :syntax/attribute/2
  82     (clsql:sql (clsql:sql-expression :table 'foo :attribute "bar"))
  83   "FOO.\"bar\"")
  84
  85 (deftest :syntax/attribute/3
  86     (clsql:sql (clsql:sql-expression :table "foo" :attribute 'bar))
  87   "\"foo\".BAR")
  88
  89 (deftest :syntax/attribute/4
  90     (clsql:sql (clsql:sql-expression :table "foo" :attribute "bar"))
  91   "\"foo\".\"bar\"")
  92
  93
  94 (deftest :syntax/subquery/1
  95     (clsql:sql [any '(3 4)])
  96  "ANY((3,4))")
  97
  98 (deftest :syntax/subquery/2
  99     (clsql:sql [in [foo] '(foo bar baz)])
 100   "(FOO IN (FOO,BAR,BAZ))")
 101
 102 (deftest :syntax/subquery/3
 103     (clsql:sql [all '(foo bar baz)])
 104   "ALL((FOO,BAR,BAZ))")
 105
 106 (deftest :syntax/subquery/4
 107     (clsql:sql [exists '(foo bar baz)])
 108   "EXISTS((FOO,BAR,BAZ))")
 109
 110 (deftest :syntax/subquery/5
 111     (clsql:sql [some '(foo bar baz)])
 112   "SOME((FOO,BAR,BAZ))")
 113
 114
 115 (deftest :syntax/aggregate/1
 116     (clsql:sql [max [+ [foo] [* 1000 [bar]]]])
 117  "MAX((FOO + (1000 * BAR)))")
 118
 119 (deftest :syntax/aggregate/2
 120     (clsql:sql [avg [+ [foo] [* 1000 [bar]]]])
 121  "AVG((FOO + (1000 * BAR)))")
 122
 123 (deftest :syntax/aggregate/3
 124     (clsql:sql [min [+ [foo] [* 1000 [bar]]]])
 125  "MIN((FOO + (1000 * BAR)))")
 126
 127 (deftest :syntax/aggregate/4
 128     (clsql:sql [sum [foo] [bar]])
 129  "SUM(FOO,BAR)")
 130
 131 (deftest :syntax/aggregate/5
 132     (clsql:sql [count [foo]])
 133  "COUNT(FOO)")
 134
 135
 136 (deftest :syntax/logical/1
 137     (values (clsql:sql [and [foo] [bar]])
 138             (clsql:sql [or [foo] [bar]]))
 139   "(FOO AND BAR)"
 140   "(FOO OR BAR)")
 141
 142 (deftest :syntax/logical/2
 143     (clsql:sql [not [foo]])
 144   "(NOT (FOO))")
 145
 146 ;;; Test how we apply logical operators when we have different numbers of children
 147 ;;; This is useful if we wish to (apply #'sql-and some-list) without having to do
 148 ;;; alot of length checking
 149 (deftest :syntax/logical/3
 150     (values (clsql:sql [and ])
 151             (clsql:sql [and [foo]])
 152             (clsql:sql [and [not [foo]]])
 153             (clsql:sql [and [foo] [bar] [baz]]))
 154   ""
 155   "FOO"
 156   "(NOT (FOO))"
 157   "(FOO AND BAR AND BAZ)")
 158
 159 (deftest :syntax/logical/4
 160     (clsql:sql [and [= [foo] [bar]]])
 161   "(FOO = BAR)")
 162
 163 (deftest :syntax/logical/5
 164   (clsql:sql [and [= [foo] [bar]]
 165                   [= [bar] [bast]]
 166                   [= [block] [blech]]])
 167   "((FOO = BAR) AND (BAR = BAST) AND (BLOCK = BLECH))")
 168
 169 (deftest :syntax/logical/6
 170     (clsql:sql
 171      (apply #'sql-and
 172             (list [= [foo] [bar]]
 173                   [and ]
 174                   [and [= [bar] [bast]]])))
 175   "((FOO = BAR) AND (BAR = BAST))")
 176
 177
 178 (deftest :syntax/null/1
 179     (clsql:sql [null [foo]])
 180   "(FOO IS NULL)")
 181
 182 (deftest :syntax/null/2
 183     (clsql:sql [not [null [foo]]])
 184   "(NOT ((FOO IS NULL)))")
 185
 186 (deftest :syntax/null/3
 187     (clsql:sql [null])
 188   "NULL")
 189
 190 (deftest :syntax/null/4
 191     (clsql:sql [not [null]])
 192   "(NOT (NULL))")
 193
 194
 195
 196 (deftest :syntax/relational/1
 197     (clsql:sql [> [baz] [beep]])
 198   "(BAZ > BEEP)")
 199
 200 (deftest :syntax/relational/2
 201     (let ((x 10))
 202       (clsql:sql [> [foo] x]))
 203   "(FOO > 10)")
 204
 205 (deftest :syntax/relational/3
 206     (clsql:sql [>= [baz] [beep]])
 207   "(BAZ >= BEEP)")
 208
 209 (deftest :syntax/relational/4
 210     (clsql:sql [< [baz] [beep]])
 211   "(BAZ < BEEP)")
 212
 213 (deftest :syntax/relational/5
 214     (clsql:sql [= [baz] [beep]])
 215   "(BAZ = BEEP)")
 216
 217 (deftest :syntax/relational/6
 218     (clsql:sql [<> [baz] [beep]])
 219   "(BAZ <> BEEP)")
 220
 221
 222 (deftest :syntax/between/1
 223     (clsql:sql [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]])
 224   "(FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)")
 225
 226 (deftest :syntax/between/2
 227     (clsql:sql [not [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]]])
 228   "(NOT ((FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)))")
 229
 230
 231 (deftest :syntax/arithmetic/1
 232     (clsql:sql [+ [foo bar] [baz]])
 233  "(FOO.BAR + BAZ)")
 234
 235 (deftest :syntax/arithmetic/2
 236     (clsql:sql [- [foo bar] [baz]])
 237  "(FOO.BAR - BAZ)")
 238
 239 (deftest :syntax/arithmetic/3
 240     (clsql:sql [/ [foo bar] [baz]])
 241  "(FOO.BAR / BAZ)")
 242
 243 (deftest :syntax/arithmetic/4
 244     (clsql:sql [* [foo bar] [baz]])
 245  "(FOO.BAR * BAZ)")
 246
 247 (deftest :syntax/arithmetic/5
 248     (clsql:sql [- [foo bar]])
 249  "(- (FOO.BAR))")
 250
 251 (deftest :syntax/arithmetic/6
 252     (clsql:sql [* 2 3])
 253   "(2 * 3)")
 254
 255
 256 (deftest :syntax/substr/1
 257     (clsql:sql [substr [hello] 1 4])
 258  "SUBSTR(HELLO,1,4)")
 259
 260 (deftest :syntax/substring/1
 261     (clsql:sql [substring [hello] 1 4])
 262  "SUBSTRING(HELLO,1,4)")
 263
 264
 265 (deftest :syntax/concat/1
 266     (clsql:sql [|| [foo] [bar] [baz]])
 267  "(FOO || BAR || BAZ)")
 268
 269 (deftest :syntax/concat/2
 270     (clsql:sql [concat [foo] [bar]])
 271  "CONCAT(FOO,BAR)")
 272
 273
 274 (deftest :syntax/pattern/1
 275     (clsql:sql [like [foo] "%v"])
 276   "(FOO LIKE '%v')")
 277
 278 (deftest :syntax/pattern/2
 279     (clsql:sql [not [like [foo] "%v"]])
 280   "(NOT ((FOO LIKE '%v')))")
 281
 282
 283 (deftest :syntax/distinct/1
 284     (clsql:sql [distinct [foo bar :string]])
 285  "DISTINCT FOO.BAR")
 286
 287 (deftest :syntax/distinct/2
 288     (clsql:sql [distinct [foo :string] [bar :integer]])
 289  "DISTINCT FOO, BAR")
 290
 291
 292 (deftest :syntax/order-by/1
 293     (clsql:sql [order-by [foo]])
 294  "ORDER BY FOO")
 295
 296 (deftest :syntax/group-by/1
 297     (clsql:sql [group-by [foo]])
 298  "GROUP BY FOO")
 299
 300 (deftest :syntax/group-by/2
 301     (clsql:sql
 302      (clsql-sys::make-query [foo] [bar] [count [foo]]
 303       :from [table]
 304       :group-by '([foo] [bar])
 305       :order-by '([foo] [bar])))
 306   "SELECT FOO,BAR,COUNT(FOO) FROM TABLE GROUP BY FOO,BAR ORDER BY FOO,BAR")
 307
 308
 309 (deftest :syntax/coalesce/1
 310     (clsql:sql [coalesce [foo] [bar] "not specified"])
 311  "COALESCE(FOO,BAR,'not specified')")
 312
 313 (deftest :syntax/coalesce/2
 314     (clsql:sql [nvl [foo] "not specified"])
 315  "COALESCE(FOO,'not specified')")
 316
 317 (deftest :syntax/nvl/1
 318     (clsql:sql [nvl [foo] "not specified"])
 319  "COALESCE(FOO,'not specified')")
 320
 321
 322
 323 (deftest :syntax/sets/1
 324     (clsql:sql [union [select [foo] :from [bar]] [select [baz] :from [bar]]])
 325  "SELECT FOO FROM BAR UNION SELECT BAZ FROM BAR")
 326
 327 (deftest :syntax/sets/2
 328     (clsql:sql [intersect [select [foo] :from [bar]] [select [baz] :from [bar]]])
 329  "SELECT FOO FROM BAR INTERSECT SELECT BAZ FROM BAR")
 330
 331 (deftest :syntax/sets/3
 332     (clsql:sql [except [select [foo] :from [bar]] [select [baz] :from [bar]]])
 333  "SELECT FOO FROM BAR EXCEPT SELECT BAZ FROM BAR")
 334
 335 (deftest :syntax/sets/4
 336     (clsql:sql [minus [select [foo] :from [bar]] [select [baz] :from [bar]]])
 337  "SELECT FOO FROM BAR MINUS SELECT BAZ FROM BAR")
 338
 339
 340 (deftest :syntax/function/1
 341     (clsql:sql [function "COS" [age]])
 342   "COS(AGE)")
 343
 344 (deftest :syntax/function/2
 345     (clsql:sql [function "TO_DATE" "02/06/99" "mm/DD/RR"])
 346   "TO_DATE('02/06/99','mm/DD/RR')")
 347
 348
 349 (deftest :syntax/query/1
 350     (clsql:sql [select [person_id] [surname] :from [person]])
 351   "SELECT PERSON_ID,SURNAME FROM PERSON")
 352
 353 (deftest :syntax/query/2
 354     (clsql:sql [select [foo] [bar *]
 355                       :from '([baz] [bar])
 356                       :where [or [= [foo] 3]
 357                                  [> [baz.quux] 10]]])
 358   "SELECT FOO,BAR.* FROM BAZ,BAR WHERE ((FOO = 3) OR (BAZ.QUUX > 10))")
 359
 360 (deftest :syntax/query/3
 361     (clsql:sql [select [foo bar] [baz]
 362                       :from '([foo] [quux])
 363                       :where [or [> [baz] 3]
 364                                  [like [foo bar] "SU%"]]])
 365   "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
 366
 367 (deftest :syntax/query/4
 368     (clsql:sql [select [count [*]] :from [emp]])
 369   "SELECT COUNT(*) FROM EMP")
 370
 371
 372 (deftest :syntax/expression/1
 373     (clsql:sql
 374      (clsql:sql-operation
 375       'select
 376       (clsql:sql-expression :table 'foo :attribute 'bar)
 377       (clsql:sql-expression :attribute 'baz)
 378       :from (list
 379              (clsql:sql-expression :table 'foo)
 380              (clsql:sql-expression :table 'quux))
 381       :where
 382       (clsql:sql-operation 'or
 383                           (clsql:sql-operation
 384                            '>
 385                            (clsql:sql-expression :attribute 'baz)
 386                            3)
 387                           (clsql:sql-operation
 388                            'like
 389                            (clsql:sql-expression :table 'foo
 390                                                 :attribute 'bar)
 391                            "SU%"))))
 392   "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
 393
 394 (deftest :syntax/expression/2
 395     (clsql:sql
 396      (apply (clsql:sql-operator 'and)
 397             (loop for table in '(thistime nexttime sometime never)
 398                   for count from 42
 399                   collect
 400                   [function "BETWEEN"
 401                             (clsql:sql-expression :table table
 402                                                  :attribute 'bar)
 403                             (clsql:sql-operation '* [hip] [hop])
 404                             count]
 405                   collect
 406                   [like (clsql:sql-expression :table table
 407                                              :attribute 'baz)
 408                         (clsql:sql table)])))
 409   "(BETWEEN(THISTIME.BAR,(HIP * HOP),42) AND (THISTIME.BAZ LIKE 'THISTIME') AND BETWEEN(NEXTTIME.BAR,(HIP * HOP),43) AND (NEXTTIME.BAZ LIKE 'NEXTTIME') AND BETWEEN(SOMETIME.BAR,(HIP * HOP),44) AND (SOMETIME.BAZ LIKE 'SOMETIME') AND BETWEEN(NEVER.BAR,(HIP * HOP),45) AND (NEVER.BAZ LIKE 'NEVER'))")
 410
 411 ))
 412
 413 #.(clsql:restore-sql-reader-syntax-state)