CLSQL Users' Guide

Kevin M. Rosenberg

Maintainer of CLSQL

Pierre R. Mai

Author of Original MaiSQL Code

onShore Development, Inc.

Author of UncommonSQL Package
  • CLSQL is Copyright © 2002-2004 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 and Xanalys are trademarks or registered trademarks of Xanalys Inc.

  • 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.
*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 -
CONNECTED-DATABASES -
CREATE-DATABASE -
DATABASE-NAME -
DATABASE-TYPE -
DESTROY-DATABASE -
DISCONNECT -
DISCONNECT-POOLED -
FIND-DATABASE -
INITIALIZE-DATABASE-TYPE -
LIST-DATABASES -
PROBE-DATABASE -
RECONNECT -
STATUS -
TRUNCATE-DATABASE -
WITH-DATABASE -
WITH-DEFAULT-DATABASE -
II. The Symbolic SQL Syntax
DISABLE-SQL-READER-SYNTAX -
ENABLE-SQL-READER-SYNTAX -
LOCALLY-DISABLE-SQL-READER-SYNTAX -
LOCALLY-ENABLE-SQL-READER-SYNTAX -
RESTORE-SQL-READER-SYNTAX-STATE -
SQL -
SQL-EXPRESSION -
SQL-OPERATION -
SQL-OPERATOR -
III. Functional Data Definition Language (FDDL)
CREATE-TABLE -
DESCRIBE-TABLE -
DROP-TABLE -
LIST-TABLES -
TABLE-EXISTS-P -
CREATE-VIEW -
DROP-VIEW -
LIST-VIEWS -
VIEW-EXISTS-P -
CREATE-INDEX -
DROP-INDEX -
INDEX-EXISTS-P -
LIST-INDEXES -
LIST-TABLE-INDEXES -
ATTRIBUTE-TYPE -
LIST-ATTRIBUTE-TYPES -
LIST-ATTRIBUTES -
CREATE-SEQUENCE -
DROP-SEQUENCE -
LIST-SEQUENCES -
SEQUENCE-EXISTS-P -
SEQUENCE-LAST -
SEQUENCE-NEXT -
SET-SEQUENCE-POSITION -
IV. Functional Data Manipulation Language (FDML)
*CACHE-TABLE-QUERIES-DEFAULT* -
BIND-PARAMETER -
CACHE-TABLE-QUERIES -
DELETE-RECORDS -
DO-QUERY -
EXECUTE-COMMAND -
FOR-EACH-ROW -
FREE-PREPARED-SQL -
INSERT-RECORDS -
LOOP-FOR-AS-TUPLES - Iterate over all the tuples of a query via a loop clause
MAP-QUERY - Map a function over all the tuples from a query
PREPARE-SQL -
PRINT-QUERY -
QUERY -
RUN-PREPARED-SQL -
SELECT -
UPDATE-RECORDS -
V. Transaction Handling
ADD-TRANSACTION-COMMIT-HOOK -
ADD-TRANSACTION-ROLLBACK-HOOK -
COMMIT -
IN-TRANSACTION-P -
ROLLBACK -
SET-AUTOCOMMIT -
START-TRANSACTION -
WITH-TRANSACTION -
VI. Object Oriented Data Definition Language (OODDL)
STANDARD-DB-OBJECT - Superclass for all CLSQL View Classes.
*DEFAULT-VARCHAR-LENGTH* -
CREATE-VIEW-FROM-CLASS -
DEF-VIEW-CLASS -
DROP-VIEW-FROM-CLASS -
LIST-CLASSES -
VII. Object Oriented Data Manipulation Language (OODML)
*DB-AUTO-SYNC* -
*DEFAULT-UPDATE-OBJECTS-MAX-LEN* -
DELETE-INSTANCE-RECORDS -
INSTANCE-REFRESHED -
UPDATE-INSTANCE-FROM-RECORDS -
UPDATE-OBJECTS-JOINS -
UPDATE-RECORD-FROM-SLOT -
UPDATE-RECORD-FROM-SLOTS -
UPDATE-RECORDS-FROM-INSTANCE -
UPDATE-SLOT-FROM-RECORD -
VIII. SQL I/O Recording
ADD-SQL-STREAM -
DELETE-SQL-STREAM -
LIST-SQL-STREAMS -
SQL-RECORDING-P -
SQL-STREAM -
START-SQL-RECORDING -
STOP-SQL-RECORDING -
IX. CLSQL Condition System
*BACKEND-WARNING-BEHAVIOR* -
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. Large Object Support
CREATE-LARGE-OBJECT -
DELETE-LARGE-OBJECT -
READ-LARGE-OBJECT -
WRITE-LARGE-OBJECT -
XI. CLSQL-SYS
DATABASE-INITIALIZE-DATABASE-TYPE - Back-end part of initialize-database-type.
XII. Index
Alphabetical Index for package CLSQL - Clickable index of all symbols
A. Database Back-ends
PostgreSQL
Libraries
Initialization
Connection Specification
PostgreSQL Socket
Libraries
Initialization
Connection Specification
MySQL
Libraries
Initialization
Connection Specification
ODBC
Libraries
Initialization
Connection Specification
AODBC
Libraries
Initialization
Connection Specification
SQLite
Libraries
Initialization
Connection Specification
Oracle
Libraries
Initialization
Connection Specification
Glossary