2017-08-27

(sph sql)

create sql-statements from scheme data

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

part of sph-lib

usage

procedures like "sql-select" usually return a string of sql

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")

import name

(sph sql)

exports

sph-sql-description

variable

sql-columns

procedure

signature

columns ->

pair/list/string/boolean -> string

description

construct the "column" part of an sql expression

sql-columns-list

procedure

signature

columns ->

(pair/list/string ...) -> list

sql-create-index

procedure

signature

name table-name columns [replace] ->

string string string ... -> string

sql-create-table

procedure

signature

table-name columns [replace add] ->

description

string ((name type options ...)/string ...) [boolean string:custom-suffix]

sql-delete

procedure

signature

table-name [where options] ->

string sql-where sql-options -> string

sql-delete-table

procedure

signature

table-name ->

string -> string

sql-insert

procedure

signature

table-name data [options] ->

string string/boolean/((column-name . data) ...) -> string

sql-insert-values

procedure

signature

a ->

alist -> string

description

convert to a sql values specification for the insert statement

((a . 1) (b . 2)) -> (a,b)values(1,2)

sql-select

procedure

signature

tables [columns where options] ->

string/(string ...) [string/(string ...)/boolean:all-columns string/list/pair string/list] -> string

sql-string

procedure

signature

a ->

string -> string

description

escapes string values for sql

sql-update

procedure

signature

table-name set [where options] ->

string pair/list/string sql-where list -> string

description

example: (sql-update "table-name" ((a . 1) (b . 2))) -> "update table-name set a=1 and b=2"

sql-value

procedure

signature

a ->

any -> string

description

convert types, escape and quote strings

sql-where

procedure

signature

conditions ->

string/pair/list -> string

sql-where-condition

procedure

signature

expr ->

description

for creating a "where" part sql separately.

"not" in row-expressions will always be in front of the column-condition in the resulting query. sqlite for example does not understand

"not c=NULL" as one might expect. use "ISNULL" instead of "NULL" in this case.


tags: programming guile documentation library scheme sph-lib sql sph-sql