CLSQL Users' Guide

Kevin M. Rosenberg

Maintainer of CLSQL

Marcus T. Pearce

Contributor to CLSQL

Pierre R. Mai

Author of Original MaiSQL Code

onShore Development, Inc.

Author of UncommonSQL Package
  • CLSQL is Copyright © 2002-2010 by Kevin M. Rosenberg, Copyright © 1999-2001 by Pierre R. Mai, and Copyright © 1999-2003 onShore Development, Inc.

  • Allegro CL® is a registered trademark of Franz Inc.

  • Common SQL, LispWorks are trademarks or registered trademarks of LispWorks Ltd.

  • Oracle® is a registered trademark of Oracle Inc.

  • Microsoft Windows® is a registered trademark of Microsoft Inc.

  • Other brand or product names are the registered trademarks or trademarks of their respective holders.


Table of Contents

Preface
1. Introduction
Purpose
History
Prerequisites
ASDF
UFFI
MD5
Supported Common Lisp Implementation
Supported SQL Implementation
Installation
Ensure ASDF is loaded
Build C helper libraries
Add UFFI path
Add MD5 path
Add CLSQL path and load module
Run test suite (optional)
2. CommonSQL Tutorial
Introduction
Data Modeling with CLSQL
Class Relations
Object Creation
Finding Objects
Deleting Objects
Conclusion
I. Connection and Initialisation
DATABASE — The super-type of all CLSQL databases
*CONNECT-IF-EXISTS*Default value for the if-exists parameter of connect.
*DB-POOL-MAX-FREE-CONNECTIONS* — How many free connections should the connection pool try to keep.
*DEFAULT-DATABASE* — The default database object to use.
*DEFAULT-DATABASE-TYPE* — The default database type to use
*INITIALIZED-DATABASE-TYPES* — List of all initialized database types
CONNECT — create a connection to a database.
CONNECTED-DATABASES — Return the list of active database objects.
DATABASE-NAME — Get the name of a database object
DATABASE-NAME-FROM-SPEC — Return the database name string corresponding to the given connection specification.
DATABASE-TYPE — Get the type of a database object.
DISCONNECT — close a database connection
DISCONNECT-POOLED — closes all pooled database connections
FIND-DATABASE — >Locate a database object through it's name.
INITIALIZE-DATABASE-TYPE — Initializes a database type
RECONNECT — Re-establishes the connection between a database object and its RDBMS.
STATUS — Print information about connected databases.
CREATE-DATABASE — create a database
DESTROY-DATABASE — destroys a database
PROBE-DATABASE — tests for existence of a database
LIST-DATABASES — List databases matching the supplied connection spec and database type.
WITH-DATABASE — Execute a body of code with a variable bound to a specified database object.
WITH-DEFAULT-DATABASE — Execute a body of code with *default-database* bound to a specified database.
II. The Symbolic SQL Syntax
ENABLE-SQL-READER-SYNTAX — Globally enable square bracket reader syntax.
DISABLE-SQL-READER-SYNTAX — Globally disable square bracket reader syntax.
LOCALLY-ENABLE-SQL-READER-SYNTAX — Locally enable square bracket reader syntax.
LOCALLY-DISABLE-SQL-READER-SYNTAX — Locally disable square bracket reader syntax.
RESTORE-SQL-READER-SYNTAX-STATE — Restore square bracket reader syntax to its previous state.
FILE-ENABLE-SQL-READER-SYNTAX — Enable the square bracket reader syntax for the duration of the file.
SQL — Construct an SQL string from supplied expressions.
SQL-EXPRESSION — Constructs an SQL expression from supplied keyword arguments.
SQL-OPERATION — Constructs an SQL expression from a supplied operator and arguments.
SQL-OPERATOR — Returns the symbol for the supplied SQL operator.
III. Functional Data Definition Language (FDDL)
CREATE-TABLE — Create a database table.
DROP-TABLE — Drop a database table.
LIST-TABLES — Returns a list of database tables.
TABLE-EXISTS-P — Tests for the existence of a database table.
CREATE-VIEW — Create a database view.
DROP-VIEW — Drops a database view.
LIST-VIEWS — Returns a list of database views.
VIEW-EXISTS-P — Tests for the existence of a database view.
CREATE-INDEX — Create a database index.
DROP-INDEX — Drop a database index.
LIST-INDEXES — Returns a list of database indexes.
INDEX-EXISTS-P — Tests for the existence of a database index.
ATTRIBUTE-TYPE — Returns the type of the supplied attribute.
LIST-ATTRIBUTE-TYPES — Returns information about the attribute types of a table.
LIST-ATTRIBUTES — Returns the attributes of a table as a list.
CREATE-SEQUENCE — Create a database sequence.
DROP-SEQUENCE — Drop a database sequence.
LIST-SEQUENCES — Returns a list of database sequences.
SEQUENCE-EXISTS-P — Tests for the existence of a database sequence.
SEQUENCE-LAST — Return the last element in a database sequence.
SEQUENCE-NEXT — Increment the value of a database sequence.
SET-SEQUENCE-POSITION — Sets the position of a database sequence.
TRUNCATE-DATABASE — Drop all tables, views, indexes and sequences in a database.
IV. Functional Data Manipulation Language (FDML)
*CACHE-TABLE-QUERIES-DEFAULT* — Specifies the default behaviour for caching of attribute types.
CACHE-TABLE-QUERIES — Control the caching of table attribute types.
INSERT-RECORDS — Insert tuples of data into a database table.
UPDATE-RECORDS — Updates the values of existing records.
DELETE-RECORDS — Delete records from a database table.
EXECUTE-COMMAND — Execute an SQL command which returns no values.
QUERY — Execute an SQL query and return the tuples as a list.
PRINT-QUERY — Prints a tabular report of query results.
SELECT — Executes a query given the supplied constraints.
DO-QUERY — Iterate over all the tuples of a query.
LOOP — Extension to Common Lisp Loop to iterate over all the tuples of a query via a loop clause.
MAP-QUERY — Map a function over all the tuples from a query
V. Transaction Handling
START-TRANSACTION — Open a transaction block.
COMMIT — Commit modifications made in the current transaction.
ROLLBACK — Roll back modifications made in the current transaction.
IN-TRANSACTION-P — A predicate for testing whether a transaction is currently in progress.
ADD-TRANSACTION-COMMIT-HOOK — Specify hooks to be run when committing changes.
ADD-TRANSACTION-ROLLBACK-HOOK — Specify hooks to be run when rolling back changes.
SET-AUTOCOMMIT — Turn on or off autocommit for a database.
WITH-TRANSACTION — Execute a body of code within a transaction.
VI. Object Oriented Data Definition Language (OODDL)
STANDARD-DB-OBJECT — Superclass for all CLSQL View Classes.
*DEFAULT-STRING-LENGTH* — Default length of SQL strings.
CREATE-VIEW-FROM-CLASS — Create a SQL table from a View Class.
DEF-VIEW-CLASS — Defines CLOS classes with mapping to SQL database.
DROP-VIEW-FROM-CLASS — Delete table from SQL database.
LIST-CLASSES — List classes for tables in SQL database.
VII. Object Oriented Data Manipulation Language (OODML)
*DB-AUTO-SYNC* — Enables SQL storage during Lisp object creation.
*DEFAULT-CACHING* — Controls the default caching behavior.
*DEFAULT-UPDATE-OBJECTS-MAX-LEN* — The default maximum number of objects each query to perform a join
INSTANCE-REFRESHED — User hook to call on object refresh.
DELETE-INSTANCE-RECORDS — Delete SQL records represented by a View Class object.
UPDATE-RECORDS-FROM-INSTANCE — Update database from view class object.
UPDATE-RECORD-FROM-SLOT — Updates database from slot value.
UPDATE-RECORD-FROM-SLOTS — Update database from slots of view class object.
UPDATE-INSTANCE-FROM-RECORDS — Update slot values from database.
UPDATE-SLOT-FROM-RECORD — Update objects slot from database.
UPDATE-OBJECTS-JOINS — Updates joined slots of objects.
VIII. SQL I/O Recording
START-SQL-RECORDING — Start recording SQL commands or results.
STOP-SQL-RECORDING — Stop recording SQL commands or results.
SQL-RECORDING-P — Tests whether SQL commands or results are being recorded.
SQL-STREAM — Returns the broadcast stream used for recording SQL commands or results.
ADD-SQL-STREAM — Add a component to the broadcast streams used for recording SQL commands or results.
DELETE-SQL-STREAM — Remove a component from the broadcast streams used for recording SQL commands or results.
LIST-SQL-STREAMS — List the components of the broadcast streams used for recording SQL commands or results.
IX. CLSQL Condition System
*BACKEND-WARNING-BEHAVIOR* — Controls behaviour on warnings from underlying RDBMS.
SQL-CONDITION — the super-type of all CLSQL-specific conditions
SQL-ERROR — the super-type of all CLSQL-specific errors
SQL-WARNING — the super-type of all CLSQL-specific warnings
SQL-DATABASE-WARNING — Used to warn while accessing a CLSQL database.
SQL-USER-ERROR — condition representing errors because of invalid parameters from the library user.
SQL-DATABASE-ERROR — condition representing errors during query or command execution
SQL-CONNECTION-ERROR — condition representing errors during connection
SQL-DATABASE-DATA-ERROR — Used to signal an error with the SQL data passed to a database.
SQL-TEMPORARY-ERROR — Used to signal a temporary error in the database backend.
SQL-TIMEOUT-ERROR — condition representing errors when a connection times out.
SQL-FATAL-ERROR — condition representing a fatal error in a database connection
X. Index
Alphabetical Index for package CLSQL — Clickable index of all symbols
A. Database Back-ends
How CLSQL finds and loads foreign libraries
PostgreSQL
Libraries
Initialization
Connection Specification
Notes
PostgreSQL Socket
Libraries
Initialization
Connection Specification
Notes
MySQL
Libraries
Initialization
Connection Specification
Notes
ODBC
Libraries
Initialization
Connection Specification
Notes
Connect Examples
AODBC
Libraries
Initialization
Connection Specification
Notes
SQLite version 2
Libraries
Initialization
Connection Specification
Notes
SQLite version 3
Libraries
Initialization
Connection Specification
Notes
Oracle
Libraries
Library Versions
Initialization
Connection Specification
Notes
Glossary