CLSQL Users' Guide

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