CLSQL Users' Guide
|  | Next

# 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

|  | Next
|  | Preface