sql.lsp

Module index

source

Module: sql

Author: Jeff Ober
Version: 0.2
Location: http://static.artfulcode.net/newlisp/sql.lsp

Provides function to programmatically write SQL statements. Requires util.lsp, generics.lsp, and functional.lsp.



The sql module contains methods and FOOP classes to help other modules write SQL statements.

To do:
• rewrite parse-date to work on Windows platforms
• program in ability to recognize SQL functions
• support for aggregate functions
• better error messages for invalid parameter combinations



Version history

0.2 • update and insert were not escaping values
0.1 • initial release

- § -

FOOP classes

The SQL FOOP classes wrap various SQL elements to facilitate the creation of models and the use of generic functions (see generics). Note that these FOOP classes are not members of the sql context.



- § -

:serialize

syntax: (:serialize sql-class)
parameter: sql-class - an instance of one of a sql module class (below)


Serializes the class to a string element of a SQL statement.



- § -

Token

syntax: (Token value)
parameter: value - any expression


Tokens are a generic serialization class. If value is another FOOP class, the FOOP instance will be returned and the call is effectively ignored. Otherwise, the value is stored for later serialization as part of a SQL statement.



example:
 (:serialize (Token "foo")) => "foo"
 (:serialize (Token 'myapp:foo)) => "foo"
 (:serialize (Token 7)) => "7"


- § -

Condition

syntax: (Condition operator operand-1 operand-2)
parameter: operator - conditional operator, such as "=", ">", or "<"
parameter: operand-1 - first operand of operator
parameter: operand-2 - second operand of operator


Generates a serializable condition statement. operand-1 and operator should be strings or symbols; operand-2 may be anything that evaluates to storable value.



example:
 (:serialize (Condition "LIKE" "name" "Joe %"))
 => "name LIKE 'Joe%'"


- § -

ConditionGroup

syntax: (ConditionGroup connector condition-1 condition-2 ...)
parameter: connector - "AND" or "OR"
parameter: instances of Condition


A collection of Conditions grouped by connector.

example:

 (ConditionGroup "AND" (Condition "=" "first_name" "Joe")
                       (Condition "=" "last_name" "Johnson"))
 => "((first_name = 'Joe') AND (last_name = 'Johnson'))"


- § -

Function

syntax: (Function function list-params)
parameter: function - SQL function
parameter: list-params - list of paramter values


example:
 (:serialize (Function "MAX" 3 4 5))
 => MAX(3,4,5)
 
 (:serialize (Function "CONV" "AF" 16 10))
 => CONV('AF',16,10)


- § -

Field

syntax: (Field str-table str-field-name)
parameter: str-table - parent table
parameter: str-field-name - field name


example:
 (:serialize (Field "employees" "first_name"))
 => "employees.first_name"
 
 (:serialize-with-value (Field "employees" "first_name") "Joe")
 => "employees.first_name = 'Joe'"


- § -

Fieldset

syntax: (Fieldset field ...)
parameter: field - Field instances


example:
 (:serialize (Fieldset (Field "employees" "first_name")
                       (Field "employees" "last_name")))
 => "employees.first_name, employees.last_name"
 
 (:serialize-with-values (Fieldset (Field "employees" "first_name")
                                   (Field "employees" "last_name"))
                         ("Joe" "Johnson"))
 => "employees.first_name = 'Joe', employees.last_name = 'Johnson'"


- § -

Table

syntax: (Table str-table-name list-fields)
parameter: str-table-name - table name
parameter: list-fields - list of field names


Note that fields do not show up in the serialized table. However, they are available for methods defined that use them (see source for sql methods for examples).



example:
 (:serialize (Table "employees" (list "first_name" "last_name")))
 => "employees"


- § -

Join

syntax: (Join join-type local-field remote-field)
parameter: join-type - e.g. "LEFT OUTER", "INNER"
parameter: local-field - instance of Field denoting local join field
parameter: remote-field - instance of Field denoting remote join field


example:
 (:serialize (Join "INNER" (Field "employees" "manager_id")
                           (Field "managers" "id"))
 => "INNER JOIN managers ON employees.manager_id = managers.id"


- § -

JoinGroup

syntax: (JoinGroup join-1 join-2 ...)
parameter: instances of Join


JoinGroups allow multiple Joins in the same query.



example:
 (:serialize (JoinGroup (Join "INNER" (Field "employees" "manager_id")
                                      (Field "managers" "id"))
                        (Join "INNER" (Field "managers" "favorite_buzzword")
                                      (Field "buzzwords" "id"))))
 => "INNER JOIN managers ON employees.manager_id = managers.id 
     INNER JOIN buzzwords ON managers.favorite_buzzword = buzzwords.id"

- § -

SQL methods

Methods defined in the sql context accept various combinations of parameters to ease the use of SQL in newLISP. Where possible, all combinations of parameter types have been documented (by possible, I mean I didn't get bored and stop). See the source for more information.



- § -

sql:token

syntax: (sql:token expr)
parameter: expr - an expression


Stringifies a the evaluated expression. Quoted context symbols are stripped to the symbol name. Functional equivalent of (Token expr).



- § -

sql:expr

syntax: (sql:expr)


expr is able to match various combinations of parameters to generate valid SQL expressions for use in complete SQL statements. expr is the building block of many of the other functions in the sql module. Note that expr can only recognize function calls when there is more than one parameter; calls with two arguments are seen as table.value. The ability to distinguish is intended in future versions.



example:
 (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%'"


- § -

sql:from

syntax: (sql:from)


from may accept a manual string, an instance of Table, or a combination of a Table instance and either a Join or JoinGroup instance.



example:
 (sql:from "employees") => "FROM employees"
 (sql:from (Table "employees") => "FROM employees"
 (sql:from (Table "employees") (Join "INNER" (Field "employees" "manager_id")
                                             (Field "managers" "id")))
 => "FROM employees INNER JOIN managers ON employees.manager_id = managers.id


- § -

sql:where

syntax: (sql:where)


where generally accepts conditional statements. where specifically accepts a string expression (possibly the result of an expr form), a Condition, a ConditionGroup, or a series of three strings (operator, operand-1, and operand-2).



example:
 (sql:where "=" "foo" "bar")
 => "WHERE (foo = 'bar')"
 
 (sql:where "foo = 'bar'")
 => "WHERE (foo = 'bar')"
 
 (sql:where (Condition "=" "foo" "bar"))
 => "WHERE (foo = 'bar')"
 
 (sql:where (ConditionGroup "AND"
                            (Condition "=" "foo" "bar")
                            (Condition "=" "baz" "bat")))
 => "WHERE ((foo = 'bar') AND (baz = 'bat'))"
 
 (sql:where (sql:expr "=" (sql:expr "life" "meaning") 42))
 => "WHERE (life.meaning = '42')"


- § -

sql:having

syntax: (sql:having)


having has the same semantics as where.



- § -

sql:field

syntax: (sql:field)


field formats a field for use in a SELECT statement. It accepts a string field name, a Table, Field, or Fieldset instance, or a list of any of the above.



example:
 (sql:field "foo")
 => "foo"
 
 (sql:field (Table "managers" (list "id" "salary")))
 => "managers.id, managers.salary"
 
 (sql:field (Field "managers" "id"))
 => "managers.id"
 
 (sql:field (Fieldset (Field "managers" "id") (Field "managers" "salary")))
 => "managers.id, managers.salary"
 
 (sql:field (list "id" "salary"))
 => "id, salary"
 
 (sql:field (list (Table "managers" (list "id" "salary"))
                  (Table "employees" (list "first_name" "last_name"))))
 => "managers.id, managers.salary, employees.first_name, employees.last_name"


- § -

sql:select

syntax: (sql:select selection query-elements ...)
parameter: selection - a manual string, Fieldset, or Table instance describing a list of fields
parameter: any number of valid SQL statement fragments


select produces a SQL query statemet. Generally, the first parameter will be the selection portion of the query. A manual string or Table may be used. Additionally, a Fieldset may be used, but this will require the addition of a from result as well.



Additional query-elements may be the result of expr or any other method that produces a valid statement fragment. No checking is done for syntax validity.



example:
 (sql:select '("id" "salary") (sql:from "managers"))
 => "SELECT id, salary FROM managers"
 
 (sql:select "id, salary" (sql:from "managers"))
 => "SELECT id, salary FROM managers"
 
 (sql:select (Table "managers" (list "id" "salary")))
 => "SELECT managers.id, managers.salary FROM managers"
 
 (sql:select (Table "managers" (list "id" "salary")) (sql:where "=" "id" 7))
 => "SELECT managers.id, managers.salary FROM managers WHERE id = 7"
 
 (sql:select (list (Table "managers" (list "id" "salary"))
                   "COUNT(employee_id) AS employees")
             (sql:from (Table "managers")
                       (Join "LEFT OUTER" (Field "managers" "id")
                                          (Field "employees" "manager_id")))
             (sql:where ">" "employees" 25)
             (sql:having ">" (sql:expr "managers" "salary") 65000)
             "GROUP BY manager.id") ; grouping is not yet implemented
 
 => "SELECT managers.id, managers.salary, COUNT(employee_id) AS employees
     FROM managers
     LEFT OUTER JOIN employees ON managers.id = employees.manager_id
     WHERE (employees > '25')
     HAVING (managers.salary > '65000')
     GROUP BY manager.id


- § -

sql:insert

syntax: (sql:insert table assoc-values)
parameter: table - a string or a Table instance
parameter: assoc-values - association list of field/value pairs; field may be a string or a Field instance


example:
 (sql:insert (Table "managers" '("salary" "last_name" "first_name"))
             '(("salary" 65000) ("first_name" "Joe") ("last_name" "Johnson")))
 => "INSERT INTO managers (salary, first_name, last_name) VALUES ('65000', 'Joe', 'Johnson')"
 
 (sql:insert "managers" '(("salary" 65000) ("first_name" "Joe") ("last_name" "Johnson")))
 => "INSERT INTO managers (salary, first_name, last_name) VALUES ('65000', 'Joe', 'Johnson')"


- § -

sql:update

syntax: (sql:update table assoc-values condition(s))
parameter: table - string or Table instance
parameter: assoc-values - association list of field/value pairs; field may be a string of a Field instance
parameter: condition(s) - string, Condition, or ConditionSet


example:
 (sql:update "managers" '(("salary" 75000)) (sql:where "=" (sql:expr "managers" "last_name") "Johnson"))
 => "UPDATE managers SET salary = '75000' WHERE (managers.last_name = 'Johnson')"


- § -

sql:delete

syntax: (sql:delete table condition(s)
parameter: table - string or Table instance
parameter: condition(s) - string, Condition, or ConditionSet


example:
 (sql:delete (Table "managers") (sql:where "=" (sql:expr "managers" "id") 6))
 => "DELETE FROM managers WHERE (managers.id = '6')"

- § -

Utility methods

Currently, all utility methods revolve around date and time field conversions.



- § -

sql:datetime->sql

syntax: (sql:datetime->sql int-yr int-mo int-day int-hr int-min int-sec)
syntax: (sql:datetime->sql int-timestamp)


Produces a valid SQL date-time.



example:
 (sql:datetime->sql 2008 07 04 16 27 53) => "2008-07-04 16:27:53"
 (sql:datetime->sql (date-value)) => "2008-07-14 15:34:11"


- § -

sql:timestamp->sql

syntax: (sql:timestamp->sql int-hour int-minute int-second)
syntax: (sql:timestamp->sql int-timestamp)


Produces a valid SQL timestamp.



example:
 (sql:timestamp->sql 14 12 36) => "14:12:36")
 (sql:timestamp->sql (date-value)) => "15:35:47"


- § -

sql:sql->datetime

syntax: (sql:sql->datetime str-sql-datetime)
parameter: str-sql-datetime - a valid SQL datetime field value


Parses a SQL datetime value using parse-date (and is therefore unavailable on windows at this time).



- § -

sql:sql->timestamp

syntax: (sql:sql->timestamp str-sql-timestamp)
parameter: str-sql-timestamp - a valid SQL time field value


Parses a SQL timestamp value using parse-date (and is therefore unavailable on windows at this time).

- ∂ -

Artful Code

generated with newLISP  and newLISPdoc
eXTReMe Tracker