2018-10-29

(sph sql)

  • handles quoting and escaping of data
  • utilise lists instead of unstructured strings
  • strings are allowed as alternative arguments at any place
  • powerful where-expression creation

usage

  • procedures like "sql-select" usually return an sql string
  • other procedures are utilities for parts of sql statements

the where expression

example

(sql-select "mytable" ("col1" "col"2) (quote ((like col1 3) (col2 (1 2 3)))))
(sql-select "mytable" ("col1" "col"2)
  (quote
    ( (any (col1 2) (not (like col1 3) (col2 (1 2 3))))
      (every (col1 (4 5)) (any (col2 (6 7)))))))

a where expression is a list

()

the list is filled with sub-lists that may be prefixed with a symbol every\any\not and column-matching expressions

((every (any (not ("a" 1)) (not (greater "b" 2))) (any (not ("a" 1)) (not (less "b" 2)))))
  • if the prefix symbol is ommitted, then every/"and" is used
  • column-matching expressions are also lists.
  • column-matching expressions have a length of either 2 or 3.
  • 2 for using "=" as the operator per default
(column values)

or 3 for specifying the operator

(operator column value)
(symbol\string symbol\string\list list\list-tree\string)

you can specify your own operator as a symbol or string, or use any of the following partly synonymous symbols

equal = greater > greater-or-equal >= less < less-or-equal <= like isnot space
  • using a tree list as value treats the values alternating by nesting as "or" and "and" related. "or" on the first nesting level
  • using a flat list as value treats the values as "or" related
  • when using a list as column, the matching condition is created for each of the columns

examples

each of the following lists starts with a procedure name and alternates between input and corresponding output examples

(sql-value
  1 "1"
  "a" "'a'"
  "\"'mystring'\"\"" "'\"\"''mystring''\"\"\"\"'")
(sql-where-expr
  ((("a" 1)))
  "a=1"
  ;alternating operator
  ((("a" (1 2 (3 (4 5) 6)))))
  "(a=1 or a=2 or (a=3 and a in(4,5) and a=6))"
  ;not
  ((not ("a" 1)))
  "not a=1"
  ;nested row-expr
  ((every (any (not ("a" 1)) (not (greater "b" 2))) (any (not ("a" 1)) (not (less "b" 2)))))
  "(not a=1 or not b>2) and (not a=1 or not b<2)"
  ;null
  ((("a" null)))
  "a=NULL"
  (((("a" "b") null)))
  "(a=NULL or b=NULL)")
(sql-create-table
  ("t" "c")
  "create table t(c)")
(sql-select
  ("t" "c" (("aa" "bb")))
  "select c from t where aa='bb'"
  ("t" "c" (("a" 1) ("b" "2")))
  "select c from t where a=1 and b='2'"
  ("t" "c" (("a" 1) ("b" "2")) ((limit . 1)))
  "select c from t where a=1 and b='2' limit 1"
  ("t" "c" (("a" 1) ("b" "2")) ((limit 1 2)))
  "select c from t where a=1 and b='2' limit 1,2"
  ("t" "c" (("a" 1) ("b" "2")) ((limit 1 2) (order "ide")))
  "select c from t where a=1 and b='2' limit 1,2 order by ide")
(sql-insert
  ("t" (("a" . 1) ("b" . "2")))
  "insert into t(a,b)values(1,'2')")
(sql-update
  ("t" "c")
  "update t set c")

example with plain strings

(sql-select "mytable m" "col1, col2")