Artful Code

The newLISP SQL library is a set of classes and functions to ease generation of SQL code in newLISP. The module is not yet feature-complete but is in a usable state.

Much of the module uses the small convenience classes to “serialize” SQL expressions. Most of the module’s classes have :serialize methods that render the encapsulated data as a string. For example, the Field class:

(let ((f (Field "table" "fieldname")))
    (:serialize f))
=> "table.fieldname"

…or the Condition class:

(let ((c (Condition ">" "salary" 65000)))
    (:serialize c))
=> "salary > '65000'"

Note that values are enclosed in single quotes for ANSI compliance. The most interesting function in the module, however, is sql:expr, which uses the match-based primitives in the functional module to generate various types of expressions:

(sql:expr "employees" "first_name")
=> "employees.first_name"
 
(sql:expr "LIKE" "first_name" "Stev%")
=> "first_name LIKE 'Stev%'"
 
(sql:expr "OR" (sql:expr "LIKE" "first_name" "Stev%")
               (sql:expr "LIKE" "last_name" "John%"))
=> "((first_name LIKE 'Stev%') OR (last_name LIKE 'John%'))"
 
(sql:expr 6)
=>"6"
 
(sql:expr 'myapp:employees)
=> "employees"
 
(sql:expr "CONV" "AF" 16 10)
=> "CONV('AF',16,10)"
 
(sql:expr "LIKE" (sql:expr "employees" "first_name") "Stev%")
=> "employees.first_name LIKE 'Stev%'"

Additional functions directly express select, update, insert, and delete statements or handle data type conversions (such as parsing SQL datetimes).

Functional

Another addition to Artful Code’s module list is the functional module. This library provides some basic conditionals that make use of match to process data. These macros express program logic by associating blocks of code with the structure of data.

Here is an example using match-case. match-case accepts a single expression and a series of forms that describe what to do based on its structure. Each case form consists of a match expression, a list of variables to be locally bound to the result of the match, and an individual form to evaluate in a local scope with the matched variables bound:

(let ((x '(1 2 3 4 5)))
    (match-case x
        ((? ? ?) (a b c)
         (println "a b and c do not get bound here, because they do not match x"))
        ((? ? ? *) (a b c d)
         (println "a = 1, b = 2, c = 3, and d = '(4 5)"))))

Here, the second block would match because (? ? ? *) matches against (1 2 3 4 5) and creates the binding association, ((a 1) (b 2) (c 3) (d (4 5))).

Also included in the module is match-cond, which is more powerful than match-case. match-cond works like cond, except that instead of a user-defined conditional, the first form in each case is a list of pattern, symbol-list, and target. See the documentation for more details.

Edit (2009-02-16): the functional module in mentioned in this post has been replaced with the matching module.