YstokSQL
YstokSQL is a Common Lisp library for interfacing relational databases via
ODBC. The implementation is much inspired by LispWorks® CommonSQL, provides
the functional interface almost in full and a limited support of the
object-oriented interface. It requires
Ystok-Library.
YstokSQL is based on the following open-source implementations:
YstokSQL was tested on LispWorks 4.2-4.4 and 5.0 for Windows against the following DBMSs and ODBC drivers:
On every Windows system after WinXP SP2 and Win2003 SP1, the Jet driver is
preinstalled. If Access were chosen, database administration required by an
YstokSQL application could be miserable (and MS Office is not needed).
For documentation, please refer to
Compatibility with Allegro CL, CLISP, Corman Lisp, CMUCL, MCL, and SBCL is targetable.
Bug reports and suggestions are welcome.
Differences from Other Implementations
Changes from SQL/ODBC version 0.9 (the last Paul Meurer's release)
- More CommonSQL compliant bracket syntax.
- Generated SQL code is entirely database-specific and depends on the actual
connected database we are running against, not on sql:*current-database*.
- ODBC 3.5 compliance:
- UNICODE UCS-2, including SQLConnectW and SQL_W... data types,
- SQL_NUMERIC and SQL_BIGINT.
- More prepared execution support; [?] is naturally used as a
parameter marker.
- More catalog functions.
- Prefetching more than one row, i.e. rowset size > 1.
- ODBC cursor operations including update via SQLSetPos and SQLBulkOperations.
- ODBC Installer support.
Differences from CommonSQL standard
- More powerful and consistent bracket syntax. Enhancements:
- more keywords inside brackets: :alias, :type, :catalog, :schema;
- added slq-parameter subclass of sql-ident and supported syntax
[? :type <type> :name <name> :key <key> :direction :input/:output/:io]
- implemented AS column alias keyword in queries, e.g.
[* [bar] 3] :as [triple-bar]
- implemented BETWEEN predicate, e.g.
[between [foo] [* [bar] 5] 9]
- concatenation operation [concat expr...] is represented by
a dedicated expression class instance, so may be easily
customized and expand into || or & depending on back-end;
- CASE-operator support.
- Due to the optimization and coallesced implementation of SQL operators,
sql-operator sometimes requires the operator symbol itself (not sql-symbol)
as the first argument, e.g. =, <, >, CASE, and some other. So the following
two forms are not always equivalent:
(sql-operation op args) vs. (apply (sql-operator op) args).
To be on the safe side, use the former one or
(apply #'sql-operation op args).
- Functional interface
- execute-command as well as functions implemented via it (update-records,
delete-records) returns two values:
(1) success code and (2) the number of records affected. - Signature of the function argument of map-query is twofold. It must accept:
either as many arguments as there are result columns if flatp is nil
(as the function is called via apply),
or one argument if flatp is true (as the function is called via funcall).
Flatp can only be specified via QUERY-EXPRESSION [select ...],
otherwise is assumed nil. - Loop macro extension allocates a single tuple object and destructively
modifies it on each iteration. To be on the safe side, a copy of it
should be made when collect results, e.g.
(loop for x being each record in [select [my-column] :from [my-table]]
collect (copy-list x))
(Actually, CommonSQL is vague about this.)
-
Object-oriented interface enhacements and limitations:
- ensure-db-object generic function to making or search for an instance
in cache in application-specific manner.
- additional db-info properties:
:auto-unique-valued - if true, on inserting try to refetch record and the
value of the slot corresponding to a COUNTER or SERIAL column.
:constraints - describes :unique, :not-null, or the like (akin to the slot
option :db-constraints in CLSQL).
If true, on inserting try to refetch record and set corresponding slots.
- update-instance-from-records can populate an instance either associated
or not associated with database.
- ensure-db-object generic function to
make or search for an instance in cache in an application-specific manner.
- update-objects-joins is not optimal;
- implemented only on LispWorks.
- locally-enable/disable/restore-sql-reader-syntax are macros,
not functions; additionally, *sql-readtable* is exhibited.
- Differently but more consistently named and better styled
- accessors, e.g. error-id (synonym of sql-state) instead of sql-error-error-id,
- key arguments, e.g. :force instead of :force-p.
Differences from CLSQL (and UncommonSQL)
- The value :auto of the argument :result-types is not supported as by
default data is transfered in binary format, which corresponds to a 'native'
DBMS type, e.g. float or timestamp data.
(CLSQL tends towards mapping too many Lisp types to VARCHAR and calling the
write-to-string and read-from-string functions too often.)
- The sql:universal-time type is normally mapped to the DBMS TIMESTAMP type
not to INTEGER.
- Set operations UNION, INTERSECT, and EXCEPT are implemented according to
CommonSQL via the :set-operation argument for select, e.g.
[select [foo] :from [bar] :set-operation [union :all [select [baz] :from
[bar]]]]
not
[union [select [foo] :from [bar]] [select [baz] :from [bar]]].
- Inner and outer joins supported as expression classes, e.g.
[select [foo x] [bar y] :from [inner-join [foo] [bar] :on [= [foo baz] [bar
baz]]]]
- LOB support.
- Aritmetic multiplication '*' is handled correctly, e.g.
[* [foo bar] 5]; unary minus is handled correctly, e.g.
[- [foo bar]].
Releases and New Features
Release 0.2
- More coherent error handling and exhaustive diagnostics via SQLGetDiagField and SQLGetDiagRecord.
- Converting to/from local-time instances.
- Tested operations on cursors.
- Loop macro extension for LispWorks.
- Various code improvements.
Release 0.3
- Object-oriented interface in its simplest.
- The desktop-mixin class covering several Jet database
subclasses: access-database, excel-database, etc.
- Coalescing SQL references to save space.
- sql-case-exp covering three operators CASE, COND and COALESCE
(analogous to ODBC IFNULL, Jet's Nz, Oracle's NV).
- SQL_NUMERIC and SQL_BIGINT types support.
Release 0.4
- Regression test suite.
- Set operations UNION, INTERSECT, EXCEPT.
- Database creation, destroying, and listing functions.
Release 1.1
- DDL functions for roles and privileges.
- Transaction support:
- macros define-commit-action and define-rollback-action,
- after methods on db-end-transaction executing action lists.
- Object-oriented interface improvements:
- added generics db-delete-instance-records, db-update-record-from-slot,
and db-update-records-from-instance,
- added generic db-class-to-allocate to determine dynamically what class
should be instance of. Useful for storing the extension of several subclasses
in a single database table (so called ORM "flat mapping").
See also
