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.

YstokSQL is based on the following open-source implementations:

• SQL/ODBC module by Paul Meurer,
• MaiSQL package by Pierre R. Mai,
• UncommonSQL by OnShore Development,
• CLSQL maintained by Kevin M. Rosenberg.

YstokSQL was tested on LispWorks 4.2-4.4 and 5.0 for Windows against the following DBMSs and ODBC drivers:

• MS Access (Jet 3.5 and 4.0),
• Oracle 8.1.7 and 10.1 (InstantClient 10.1, 10.2),
• PostgreSQL 8.1 driver,
• SQLite3 driver 0.71 by Christian Werner,
• MS SQL Server.

Dependencies

YstokSQL requires Ystok-Library.

Platforms

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

Compatibility with Allegro CL, CLISP, Corman Lisp, CMUCL, MCL, and SBCL is targetable.

Download and installation

ysql-1-1-012.zip
(364KB)

YstokSQL is available from http://lisp.ystok.ru/ysql/ysql-1-1-012.zip. The distribution package contains both the ASDF file ysql.asd and the LispWorks system definition file defsys.lisp.

Here is change.log.

Documentation

For documentation, please refer to

• Common SQL documentation in LispWorks User Guide and the SQL Package in LispWorks Reference Manual,
• CLSQL documentation.

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

• YstokSQL forum (both Russian and English).