Scheme-PG

9  SQL Statement Creation

In order to create SQL statements, programmers frequently utilize error prone and often syntactically unappealing string formatting operations. Taking a cue from Scheme-QL, Scheme-PG provides a set of procedures and macros that allow programmers to create SQL statements using a little language implemented in Scheme. While this little language is still under development, it implements enough of SQL that it should prove useful for many applications.

Scheme-PG introduces a set of new forms that mirror a subset of the SQL language functions such as SELECT, INSERT, UPDATE and DELETE. These new forms return valid SQL statements as Scheme strings, are not dependent on other Scheme-PG functionality, and therefore can be used by themselves to support SQL statement creation for use with other Scheme database packages.¹

In the new forms introduced by Scheme-PG

• SQL keywords such as SELECT, WHERE, UPDATE and DELETE are macro literals

• SQL objects such as columns, tables, views, etc. are represented as Scheme symbols

• SQL values such as the value to be inserted into a database or the value used in a condition in a SQL WHERE clause can be Scheme strings or Scheme numbers in Scheme-PG

Scheme-PG requires that Scheme symbols appear in positions where SQL objects should appear (e.g. a column list in a SELECT statement) and it requires that Scheme strings or numbers appear in positions where SQL values should appear (e.g. a value in an INSERT statement). Scheme-PG handles proper formatting of values. Specifically, the escape-string procedure, (see §8) is called on strings and the result is surrounded by single quotes. Scheme-PG also formats SQL objects by surrounding them with double quotes so that table or column names can for example contain spaces or dashes. Positions in the forms where Scheme-PG expects an SQL object or SQL value are are quasiquoted, so unquoting (e.g. ,first-name ,(get-first-name)) can be utilized.

(where condition)      SYNTAX

where: condition --> string

where: and (listof conditions) --> string

where: or (listof conditions) --> string

where: not (listof conditions) --> string

The where macro supports the creation of WHERE clauses for use in SQL statements such as SELECT, UPDATE and DELETE. Fundamental to the where clause is a condition which is a length three proper list (operator column value). In a condition, a valid operator is a symbol, a valid column is either a symbol representing a column name or a length two proper list of symbols representing a table name and a column name, and a valid value is either a valid column or a string or number. The case of value being a valid column occurs in a condition in which the values of two database columns are compared. For example,

• SELECT * FROM pers WHERE first-name = last-name

• SELECT * FROM pers,addr WHERE pers.id = addr.id.

The SQL AS syntax is supported as shown in the following examples,

• SELECT ((as first-name fn) FROM pers WHERE first-name = last-name

• SELECT * FROM pers,addr WHERE pers.id = addr.id.

The case of value being a string or number occurs in a condition in which the value of a database column is compared to a constant. For example,

• SELECT * FROM pers WHERE last-name LIKE 'Do%'

• SELECT * FROM pers WHERE age 22.

If value is a valid column it is formatted as described above. If value is not a valid column then, if it is a string it is formatted as described above, if it is a number it is returned unformatted and in all other cases an exception is raised. An example of an additional Scheme data type that could be supported as a value in future releases is a list, which could be used in an SQL IN, e.g. SELECT * FROM addr WHERE state IN ('NH', 'NJ', 'NY').

Scheme-PG tranforms a condition (operator column value) into the string ``column operator value'' formatting its components as described above. The following examples correspond to the four SELECT statements listed above

• (= first-name last-name) first-name = last-name

• (= (pers id) (addr id)) pers.id = addr.id

• (like last-name ``Do%'') last-name LIKE 'Do%'

• ( age 22) age 22

The where macro accepts either a single condition or the literals and, or and not and a list of conditions. Programmers can build where clauses that contain arbitrarily complex Boolean expression through use of and, or and not as shown by the following examples

• (where (and (like last-name ``Do%'') ( age 22)))
  WHERE last-name LIKE 'Do%' AND age 22

• (where (or (and ( age 18) ( age 22)) (like last-name ``Do%'')))
  WHERE (age 18 AND age 22) OR last-name LIKE 'Do%'

(select acolumns atables aclause ...)      SYNTAX

select: (listof symbol/(table column)) (listof symbol/(schema table)) clause ... --> string

select: all (listof symbol/(table column)) clause ... --> string

The select macro supports the creation of SQL SELECT statements. It accepts a list of columns either as symbols or as length two proper lists consisting of a table name and a column name, a list of table names as either symbols or as length proper lists consisting of a schema name and a table name, and a arbitrary number of clauses as strings. In lieu of the list of column names the single literal all can be provided to generate the * in the common SELECT * statement. The select-distinct macro is the same as the select macro except that it creates a SELECT DISTINCT rather statement than a SELECT statement. The SQL AS syntax is also supported. The following examples demonstrate the use of the select marco.

• (select (first-name last-name) (pers))
  SELECT "first-name","last-name" FROM "pers"

• (select ((pers first-name) (pers last-name)) (pers))
  SELECT "pers"."first-name","pers"."last-name" FROM "pers"

• (select ((as first-name fn) (as last-name ln)) (pers))
  SELECT "first-name" AS "fn","last-name" AS "ln" FROM "pers"

• (select ((as (pers first-name) fn) (as last-name ln) age) (pers))
  SELECT "pers"."first-name" AS "fn","last-name" AS "ln","age" FROM "pers"
  

(insert atable avalues)      SYNTAX
insert: symbol (listof strings and/or numbers) --> string

(insert atable acolumns avalues)      SYNTAX
insert: symbol (listof symbols) (listof strings and/or numbers) --> string

(insert atable acolumns-values)      SYNTAX
insert: symbol (listof (symbols . string and/or number)) --> string

(insert atable acolumns-values)      SYNTAX
insert: symbol (listof (symbols string and/or number)) --> string

The insert macro has four rules that support the creation of INSERT statements as shown in the examples below. The following form implements the values-only format of an INSERT statement.

• (insert pers (1 ``John'' ``Doe'' 20))
  INSERT INTO ``pers'' (1,'John','Doe',20)

The following forms all generate the same INSERT statement.

• (insert pers (id first-name last-name age) (1 ``John'' ``Doe'' 20))

• (insert pers ((id . 1) (first-name . ``John'') (last-name . ``Doe'') (age . 20)))

• (insert pers ((id 1) (first-name ``John'') (last-name ``Doe'') (age 20)))
  INSERT INTO ``pers'' (``id'',``first-name'',``last-name'',``age'')
  VALUES (1,'John', 'Doe',20)

(delete atable)      SYNTAX
delete: symbol --> string

(delete atable awhere)      SYNTAX
delete: symbol where --> string

The delete macro has two rules that support the creation of DELETE statements as shown in the examples below. The where clause, in the second instance is a string.

• (delete pers) DELETE FROM ``pers''

• (delete pers (where (and ( age 45) (= state "NJ"))))
  DELETE FROM "pers" WHERE "age" 45 AND "state" = 'NJ'

(update atable acolumns-values)      SYNTAX
update: symbol (listof (symbols . string and/or number)) --> string

(update atable acolumns-values)      SYNTAX
update: symbol (listof (symbols string and/or number)) --> string

(update atable acolumns-values where)      SYNTAX
update: symbol (listof (symbols . string and/or number)) where --> string

(update atable acolumns-values where)      SYNTAX
update: symbol (listof (symbols string and/or number)) where --> string

The update macro has four rules that support the creation of UPDATE statements as shown in the examples below. The following two forms both construct the same UPDATE statement:

• (update pers ((first-name "John") (age 20)))

• (update pers ((first-name . "John") (age . 20))) UPDATE pers SET first-name='John', age=20

The following two forms both construct the same UPDATE statement. The where clause in these instances are strings.

• (update pers ((first-name "John") (age 20)) (where (= last-name "Doe")))

• (update pers ((first-name . "John") (age . 20)) (where (= last-name "Doe"))) UPDATE pers SET first-name='John', age=20 WHERE age 20

(limit offset number)      SYNTAX
limit: non-negative-integer non-negative-integer --> string
The limit macro creates a LIMIT clause for use in a SELECT statement. The offset argument is the number of rows to be skipped, so an offset of 0 means that the first row returned will be the first row of the result. The number argument is an upper bound on the number of rows to return. Less than number rows can be returned depending on the number of rows in the result and the value of offset.

(order-by column asc/desc)      SYNTAX
order-by: (listof (symbol 'asc or 'desc))) --> string
The order-by macro creates an ORDER BY clause for use in a SELECT. It accepts a proper list of length two lists that consists of a symbol representing a column name and either the symbol asc (to indicate the result should be put in ascending order) or the symbol desc (to indicate the result should be put in descending order).

References