From: Kevin M. Rosenberg Date: Wed, 7 Apr 2004 16:59:18 +0000 (+0000) Subject: r8851: more integration X-Git-Tag: v3.8.6~724 X-Git-Url: http://git.kpe.io/?p=clsql.git;a=commitdiff_plain;h=595658021ebaf450894bb143a346e409b55e02af r8851: more integration --- diff --git a/ChangeLog b/ChangeLog index 080c257..2642628 100644 --- a/ChangeLog +++ b/ChangeLog @@ -1,9 +1,6 @@ 06 Apr 2004 Kevin Rosenberg (kevin@rosenberg.net) * With for Marcus Pearce's excellent work, I've merged - his clsql-usql port into the code base. Added the USQL - documentation to the XML documentation. - * The CLSQL and CLSQL-USQL high-level interfaces can now - both be loaded and imported into the same package. + his clsql-usql port into clsql. 02 Apr 2004 Kevin Rosenberg (kevin@rosenberg.net) * Integrate patch from Marcus Pearce diff --git a/NEWS b/NEWS index ce47188..3d158e3 100644 --- a/NEWS +++ b/NEWS @@ -1,5 +1,2 @@ -CLSQL now supports SBCL, OpenMCL, and SCL. - -CLSQL now supports locking on connection pools. - - +CLSQL now supports the CommonSQL-API with the merge of the orphaned +UncommonSQL package. diff --git a/doc/bookinfo.xml b/doc/bookinfo.xml index 12d19e9..c301d41 100644 --- a/doc/bookinfo.xml +++ b/doc/bookinfo.xml @@ -22,6 +22,7 @@ onShore Development, Inc. + Author of UncommonSQL Package diff --git a/doc/clsql-tutorial.lisp b/doc/clsql-tutorial.lisp new file mode 100644 index 0000000..9c9b93f --- /dev/null +++ b/doc/clsql-tutorial.lisp @@ -0,0 +1,190 @@ + +(in-package :cl-user) + +;; You must set these variables to appropriate values. +(defvar *tutorial-database-type* nil + "Possible values are :postgresql,:postgresql-socket :mysql or :sqlite") +(defvar *tutorial-database-name* "" + "The name of the database we will work in.") +(defvar *tutorial-database-user* "" + "The name of the database user we will work as.") +(defvar *tutorial-database-server* "" + "The name of the database server if required") +(defvar *tutorial-database-password* "" + "The password if required") + +(sql:def-view-class employee () + ((emplid + :db-kind :key + :db-constraints :not-null + :nulls-ok nil + :type integer + :initarg :emplid) + (first-name + :accessor first-name + :type (string 30) + :initarg :first-name) + (last-name + :accessor last-name + :type (string 30) + :initarg :last-name) + (email + :accessor employee-email + :type (string 100) + :nulls-ok t + :initarg :email) + (companyid + :type integer) + (company + :accessor employee-company + :db-kind :join + :db-info (:join-class company + :home-key companyid + :foreign-key companyid + :set nil)) + (managerid + :type integer + :nulls-ok t) + (manager + :accessor employee-manager + :db-kind :join + :db-info (:join-class employee + :home-key managerid + :foreign-key emplid + :set nil))) + (:base-table employee)) + +(sql:def-view-class company () + ((companyid + :db-type :key + :db-constraints :not-null + :type integer + :initarg :companyid) + (name + :type (string 100) + :initarg :name) + (presidentid + :type integer) + (president + :reader president + :db-kind :join + :db-info (:join-class employee + :home-key presidentid + :foreign-key emplid + :set nil)) + (employees + :reader company-employees + :db-kind :join + :db-info (:join-class employee + :home-key companyid + :foreign-key companyid + :set t))) + (:base-table company)) + +;; Connect to the database (see the CLSQL documentation for vendor +;; specific connection specs). +(sql:connect `(,*tutorial-database-server* + ,*tutorial-database-name* + ,*tutorial-database-user* + ,*tutorial-database-password*) + :database-type *tutorial-database-type*) + +;; Record the sql going out, helps us learn what is going +;; on behind the scenes +(sql:start-sql-recording) + +;; Create the tables for our view classes +;; First we drop them, ignoring any errors +(ignore-errors + (sql:drop-view-from-class 'employee) + (sql:drop-view-from-class 'company)) + +(sql:create-view-from-class 'employee) +(sql:create-view-from-class 'company) + + +;; Create some instances of our view classes +(defvar employee1 (make-instance 'employee + :emplid 1 + :first-name "Vladamir" + :last-name "Lenin" + :email "lenin@soviet.org")) + +(defvar company1 (make-instance 'company + :companyid 1 + :name "Widgets Inc.")) + + +(defvar employee2 (make-instance 'employee + :emplid 2 + :first-name "Josef" + :last-name "Stalin" + :email "stalin@soviet.org")) + +;; Lenin manages Stalin (for now) +(sql:add-to-relation employee2 'manager employee1) + +;; Lenin and Stalin both work for Widgets Inc. +(sql:add-to-relation company1 'employees employee1) +(sql:add-to-relation company1 'employees employee2) + +;; Lenin is president of Widgets Inc. +(sql:add-to-relation company1 'president employee1) + +(sql:update-records-from-instance employee1) +(sql:update-records-from-instance employee2) +(sql:update-records-from-instance company1) + +;; lets us use the functional +;; sql interface +(sql:locally-enable-sql-reader-syntax) + + +(format t "The email address of ~A ~A is ~A" + (first-name employee1) + (last-name employee1) + (employee-email employee1)) + +(setf (employee-email employee1) "lenin-nospam@soviets.org") + +;; Update the database +(sql:update-records-from-instance employee1) + +(let ((new-lenin (car + (sql:select 'employee + :where [= [slot-value 'employee 'emplid] 1])))) + (format t "His new email is ~A" + (employee-email new-lenin))) + + +;; Some queries + +;; all employees +(sql:select 'employee) +;; all companies +(sql:select 'company) + +;; employees named Lenin +(sql:select 'employee :where [= [slot-value 'employee 'last-name] + "Lenin"]) + +(sql:select 'company :where [= [slot-value 'company 'name] + "Widgets Inc."]) + +;; Employees of Widget's Inc. +(sql:select 'employee + :where [and [= [slot-value 'employee 'companyid] + [slot-value 'company 'companyid]] + [= [slot-value 'company 'name] + "Widgets Inc."]]) + +;; Same thing, except that we are using the employee +;; relation in the company view class to do the join for us, +;; saving us the work of writing out the SQL! +(company-employees company1) + +;; President of Widgets Inc. +(president company1) + +;; Manager of Josef Stalin +(employee-manager employee2) diff --git a/doc/clsql.xml b/doc/clsql.xml index 06ca474..41159df 100644 --- a/doc/clsql.xml +++ b/doc/clsql.xml @@ -11,7 +11,7 @@ - + diff --git a/doc/csql.xml b/doc/csql.xml new file mode 100644 index 0000000..d0d09fa --- /dev/null +++ b/doc/csql.xml @@ -0,0 +1,740 @@ + + +%myents; +]> + + + &commonsql; Tutorial + Based on the &usql; Tutorial + + + Introduction + + + The goal of this tutorial is to guide a new developer thru the + process of creating a set of &clsql; classes providing a + Object-Oriented interface to persistent data stored in an &sql; + database. We will assume that the reader is familiar with how + &sql; works, how relations (tables) should be structured, and + has created at least one &sql; application previously. We will + also assume a minor level of experience with Common Lisp. + + + + &clsql; provides two different interfaces to &sql; databases, a + Functional interface, and an Object-Oriented interface. The + Functional interface consists of a special syntax for embedded + &sql; expressions in Lisp, and provides lisp functions for &sql; + operations like SELECT and + UPDATE. The object-oriented interface provides + a way for mapping Common Lisp Objects System (CLOS) objects into + databases and includes functions for inserting new objects, + querying objects, and removing objects. Most applications will + use a combination of the two. + + + + &clsql; is based on the CommonSQL package from Xanalys, so the + documentation that Xanalys makes available online is useful for + &clsql; as well. It is suggested that developers new to &clsql; read + their documentation as well, as any differences between CommonSQL + and &clsql; are minor. Xanalys makes the following documents + available: + + + + + + + Xanalys &lw; User Guide - The &commonsql; + Package + + + + + + + + + Xanalys &lw; Reference Manual - The SQL + Package + + + + + + + + &commonsql; Tutorial by Nick Levine + + + + + + + + Data Modeling with &clsql; + + + Before we can create, query and manipulate &clsql; objects, we + need to define our data model as noted by Philip Greenspun + + + + Philip Greenspun's "SQL For Web Nerds" - Data + Modeling + + + + + + + When data modeling, you are telling the relational database + management system (RDBMS) the following: + + + + + What elements of the data you will store. + + + How large each element can be. + + + What kind of information each element can contain. + + + What elements may be left blank. + + + Which elements are constrained to a fixed range. + + + Whether and how various tables are to be linked. + + + + + With &sql; database one would do this by defining a set of + relations, or tables, followed by a set of queries for joining + the tables together in order to construct complex records. + However, with &clsql; we do this by defining a set of CLOS + classes, specifying how they will be turned into tables, and how + they can be joined to one another via relations between their + attributes. The &sql; tables, as well as the queries for + joining them together are created for us automatically, saving + us from dealing with some of the tedium of &sql;. + + + + Let us start with a simple example of two &sql; tables, and the + relations between them. + + + +CREATE TABLE EMPLOYEE ( + emplid NOT NULL number(38), + first_name NOT NULL varchar2(30), + last_name NOT NULL varchar2(30), + emall varchar2(100), + companyid NOT NULL number(38), + managerid number(38) +) + +CREATE TABLE COMPANY ( + companyid NOT NULL number(38), + name NOT NULL varchar2(100), + presidentid NOT NULL number(38) +) + + + +This is of course the canonical &sql; tutorial example, "The Org Chart". + + + +In &clsql;, we would have two "view classes" (a fancy word for a class +mapped into a database). They would be defined as follows: + + + +(clsql:def-view-class employee () + ((emplid + :db-kind :key + :db-constraints :not-null + :type integer + :initarg :emplid) + (first-name + :accessor first-name + :type (string 30) + :initarg :first-name) + (last-name + :accessor last-name + :type (string 30) + :initarg :last-name) + (email + :accessor employee-email + :type (string 100) + :nulls-ok t + :initarg :email) + (companyid + :type integer) + (managerid + :type integer + :nulls-ok t)) + (:base-table employee)) + +(clsql:def-view-class company () + ((companyid + :db-type :key + :db-constraints :not-null + :type integer + :initarg :companyid) + (name + :type (string 100) + :initarg :name) + (presidentid + :type integer)) + (:base-table company)) + + + + The DEF-VIEW-CLASS macro is just like the + normal CLOS DEFCLASS macro, except that it + handles several slot options that DEFCLASS + doesn't. These slot options have to do with the mapping of the slot + into the database. We only use a few of the slot options in the + above example, but there are several others. + + + + + + :column - The name of the &sql; column this slot is stored in. + Defaults to the slot name. If the slot name is not a valid &sql; + identifier, it is escaped, so foo-bar becomes foo_bar. + + + + + :db-kind - The kind of database mapping which + is performed for this slot. :base indicates + the slot maps to an ordinary column of the database view. + :key indicates that this slot corresponds to + part of the unique keys for this view, :join + indicates a join slot representing a relation to another view + and :virtual indicates that this slot is an ordinary CLOS slot. + Defaults to :base. + + + + :db-reader - If a string, then when reading + values from the database, the string will be used for a format + string, with the only value being the value from the database. + The resulting string will be used as the slot value. If a + function then it will take one argument, the value from the + database, and return the value that should be put into the slot. + + + + + :db-writer - If a string, then when reading + values from the slot for the database, the string will be used + for a format string, with the only value being the value of the + slot. The resulting string will be used as the column value in + the database. If a function then it will take one argument, the + value of the slot, and return the value that should be put into + the database. + + + + :db-type - A string which will be used as the + type specifier for this slots column definition in the database. + + + + + :nulls-ok - If &t;, all &sql; &null; values + retrieved from the database become nil; if &nil;, all &null; + values retrieved are converted by + DATABASE-NULL-VALUE. + + + + :db-info - A join specification. + + + + + In our example each table as a primary key attribute, which is + required to be unique. We indicate that a slot is part of the + primary key (&clsql; supports multi-field primary keys) by specifying + the :db-kind key slot option. + + + + The &sql; type of a slot when it is mapped into the database is + determined by the :type slot option. The argument + for the :type option is a Common Lisp datatype. + The &clsql; framework will determine the appropriate mapping + depending on the database system the table is being created in. If + we really wanted to determine what &sql; type was used for a slot, + we could specify a :db-type option like + "NUMBER(38)" and we would be guaranteed that the slot would be + stored in the database as a NUMBER(38). This is not recomended + because it could makes your view class unportable across database + systems. + + + + DEF-VIEW-CLASS also supports some class + options, like :base-table. The + :base-table option specifies what the table name + for the view class will be when it is mapped into the database. + + + + +Class Relations + + +In an &sql; only application, the EMPLOYEE and +COMPANY tables can be queried to determine things +like, "Who is Vladamir's manager?", What company does Josef work +for?", and "What employees work for Widgets Inc.". This is done by +joining tables with an &sql; query. + + + +Who works for Widgets Inc.? + + + +SELECT first_name, last_name FROM employee, company + WHERE employee.companyid = company.companyid + AND company.company_name = "Widgets Inc." + + + +Who is Vladamir's manager? + + + +SELECT managerid FROM employee + WHERE employee.first_name = "Vladamir" + AND employee.last_name = "Lenin" + + + +What company does Josef work for? + + + +SELECT company_name FROM company, employee + WHERE employee.first_name = "Josef" + AND employee.last-name = "Stalin" + AND employee.companyid = company.companyid + + + +With &clsql; however we do not need to write out such queries because +our view classes can maintain the relations between employees and +companies, and employees to their managers for us. We can then access +these relations like we would any other attribute of an employee or +company object. In order to do this we define some join slots for our +view classes. + + + +What company does an employee work for? If we add the following slot +definition to the employee class we can then ask for it's +COMPANY slot and get the appropriate result. + + + + ;; In the employee slot list + (company + :accessor employee-company + :db-kind :join + :db-info (:join-class company + :home-key companyid + :foreign-key companyid + :set nil)) + + + +Who are the employees of a given company? And who is the president of +it? We add the following slot definition to the company view class and +we can then ask for it's EMPLOYEES slot and get the +right result. + + + + ;; In the company slot list + (employees + :reader company-employees + :db-kind :join + :db-info (:join-class employee + :home-key companyid + :foreign-key companyid + :set t)) + + (president + :reader president + :db-kind :join + :db-info (:join-class employee + :home-key presidentid + :foreign-key emplid + :set nil)) + + + +And lastly, to define the relation between an employee and their +manager: + + + + ;; In the employee slot list + (manager + :accessor employee-manager + :db-kind :join + :db-info (:join-class employee + :home-key managerid + :foreign-key emplid + :set nil)) + + + +&clsql; join slots can represent one-to-one, one-to-many, and +many-to-many relations. Above we only have one-to-one and one-to-many +relations, later we will explain how to model many-to-many relations. +First, let's go over the slot definitions and the available options. + + + +In order for a slot to be a join, we must specify that it's +:db-kind :join, as opposed to +:base or :key. Once we do that, we +still need to tell &clsql; how to create the join statements for the +relation. This is what the :db-info option does. It +is a list of keywords and values. The available keywords are: + + + + + + :join-class - The view class to which we want + to join. It can be another view class, or the same view class + as our object. + + + + :home-key - The slot(s) in the immediate object + whose value will be compared to the foreign-key slot(s) in the + join-class in order to join the two tables. It can be a single + slot-name, or it can be a list of slot names. + + + + :foreign-key - The slot(s) in the join-class + which will be compared to the value(s) of the home-key. + + + + + :set - A boolean which if false, indicates that + this is a one-to-one relation, only one object will be returned. + If true, than this is a one-to-many relation, a list of objects + will be returned when we ask for this slots value. + + + + +There are other :join-info options available in &clsql;, but we will +save those till we get to the many-to-many relation examples. + + + + + +Object Creation + + +Now that we have our model laid out, we should create some object. +Let us assume that we have a database connect set up already. We +first need to create our tables in the database: + + + +Note: the file doc/clsql-tutorial.lisp contains +view class definitions which you can load into your list at this point +in order to play along at home. + + + +(clsql:create-view-from-class 'employee) +(clsql:create-view-from-class 'company) + + + +Then we will create our objects. We create them just like you would +any other CLOS object: + + + +(defvar employee1 (make-instance 'employee + :emplid 1 + :first-name "Vladamir" + :last-name "Lenin" + :email "lenin@soviet.org")) + +(defvar company1 (make-instance 'company + :companyid 1 + :name "Widgets Inc.")) + + +(defvar employee2 (make-instance 'employee + :emplid 2 + :first-name "Josef" + :last-name "Stalin" + :email "stalin@soviet.org")) + + + +In order to insert an objects into the database we use the +UPDATE-RECORDS-FROM-INSTANCE function as follows: + + + +(clsql:update-records-from-instance employee1) +(clsql:update-records-from-instance employee2) +(clsql:update-records-from-instance company1) + + + +Now we can set up some of the relations between employees and +companies, and their managers. The +ADD-TO-RELATION method provides us with an easy +way of doing that. It will update both the relation slot, as well as +the home-key and foreign-key slots in both objects in the relation. + + + +;; Lenin manages Stalin (for now) +(clsql:add-to-relation employee2 'manager employee1) + +;; Lenin and Stalin both work for Widgets Inc. +(clsql:add-to-relation company1 'employees employee1) +(clsql:add-to-relation company1 'employees employee2) + +;; Lenin is president of Widgets Inc. +(clsql:add-to-relation company1 'president employee1) + + + + After you make any changes to an object, you have to specifically + tell &clsql; to update the &sql; database. The + UPDATE-RECORDS-FROM-INSTANCE method will write + all of the changes you have made to the object into the database. + + + + Since &clsql; objects are just normal CLOS objects, we can manipulate + their slots just like any other object. For instance, let's say + that Lenin changes his email because he was getting too much spam + from the German Socialists. + + + +;; Print Lenin's current email address, change it and save it to the +;; database. Get a new object representing Lenin from the database +;; and print the email + +;; This lets us use the functional &clsql; interface with [] syntax +(clsql:locally-enable-sql-reader-syntax) + +(format t "The email address of ~A ~A is ~A" + (first-name employee1) + (last-name employee1) + (employee-email employee1)) + +(setf (employee-email employee1) "lenin-nospam@soviets.org") + +;; Update the database +(clsql:update-records-from-instance employee1) + +(let ((new-lenin (car (clsql:select 'employee + :where [= [slot-value 'employee 'emplid] 1])))) + (format t "His new email is ~A" + (employee-email new-lenin))) + + + + Everything except for the last LET expression + is already familiar to us by now. To understand the call to + CLSQL:SELECT we need to discuss the + Functional &sql; interface and it's integration with the Object + Oriented interface of &clsql;. + + + + + +Finding Objects + + + Now that we have our objects in the database, how do we get them out + when we need to work with them? &clsql; provides a functional + interface to &sql;, which consists of a special Lisp reader macro + and some functions. The special syntax allows us to embed &sql; in + lisp expressions, and lisp expressions in &sql;, with ease. + + + + Once we have turned on the syntax with the expression: + + + +(clsql:locally-enable-sql-reader-syntax) + + + + We can start entering fragments of &sql; into our lisp reader. We + will get back objects which represent the lisp expressions. These + objects will later be compiled into &sql; expressions that are + optimized for the database backed we are connected to. This means + that we have a database independent &sql; syntax. Here are some + examples: + + + +;; an attribute or table name +[foo] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO> + +;; a attribute identifier with table qualifier +[foo bar] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR> + +;; a attribute identifier with table qualifier +[= "Lenin" [first_name]] => + #<CLSQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)> + +[< [emplid] 3] => + #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)> + +[and [< [emplid] 2] [= [first_name] "Lenin"]] => + #<CLSQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND + (FIRST_NAME = 'Lenin'))> + + +;; If we want to reference a slot in an object we can us the +;; SLOT-VALUE sql extension +[= [slot-value 'employee 'emplid] 1] => + #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)> + +[= [slot-value 'employee 'emplid] + [slot-value 'company 'presidentid]] => + #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)> + + + + The SLOT-VALUE operator is important because it + let's us query objects in a way that is robust to any changes in the + object->table mapping, like column name changes, or table name + changes. So when you are querying objects, be sure to use the + SLOT-VALUE &sql; extension. + + + + Since we can now formulate &sql; relational expression which can be + used as qualifiers, like we put after the WHERE + keyword in &sql; statements, we can start querying our objects. + &clsql; provides a function SELECT which can return + use complete objects from the database which conform to a qualifier, + can be sorted, and various other &sql; operations. + + + + The first argument to SELECT is a class name. it + also has a set of keyword arguments which are covered in the + documentation. For now we will concern ourselves only with the + :where keyword. Select returns a list of objects, or nil if it + can't find any. It's important to remember that it always returns a + list, so even if you are expecting only one result, you should + remember to extract it from the list you get from + SELECT. + + + +;; all employees +(clsql:select 'employee) +;; all companies +(clsql:select 'company) + +;; employees named Lenin +(clsql:select 'employee :where [= [slot-value 'employee 'last-name] + "Lenin"]) + +(clsql:select 'company :where [= [slot-value 'company 'name] + "Widgets Inc."]) + +;; Employees of Widget's Inc. +(clsql:select 'employee + :where [and [= [slot-value 'employee 'companyid] + [slot-value 'company 'companyid]] + [= [slot-value 'company 'name] + "Widgets Inc."]]) + +;; Same thing, except that we are using the employee +;; relation in the company view class to do the join for us, +;; saving us the work of writing out the &sql;! +(company-employees company1) + +;; President of Widgets Inc. +(president company1) + +;; Manager of Josef Stalin +(employee-manager employee2) + + + + + +Deleting Objects + + + Now that we know how to create objects in our database, manipulate + them and query them (including using our predefined relations to + save us the trouble writing alot of &sql;) we should learn how to + clean up after ourself. It's quite simple really. The function + DELETE-INSTANCE-RECORDS will remove an object + from the database. However, when we remove an object we are + responsible for making sure that the database is left in a correct + state. + + + + For example, if we remove a company record, we need to either remove + all of it's employees or we need to move them to another company. + Likewise if we remove an employee, we should make sure to update any + other employees who had them as a manager. + + + + + +Conclusion + + + There are many nooks and crannies to &clsql;, some of which are + covered in the Xanalys documents we refered to earlier, some are + not. The best documentation at this time is still the source code + for &clsql; itself and the inline documentation for its various + functions. + + + + + diff --git a/doc/entities.inc b/doc/entities.inc index 575fe92..fafa3dd 100644 --- a/doc/entities.inc +++ b/doc/entities.inc @@ -7,6 +7,7 @@ MaiSQL"> SQL"> UncommonSQL"> +CommonSQL"> MySQL"> PostgreSQL"> SQLite"> diff --git a/doc/intro.xml b/doc/intro.xml index de09cf4..52f8bc7 100644 --- a/doc/intro.xml +++ b/doc/intro.xml @@ -11,50 +11,58 @@ Purpose &clsql; is a Common Lisp interface to SQL databases. A number of Common - Lisp implementations and SQL databases are supported. The general - structure of &clsql; is based on the - CommonSQL - package by Xanalys. + linkend="gloss-sql">SQL databases. A number of Common + Lisp implementations and SQL databases are supported. The general + structure of &clsql; is based on the &commonsql; package by + Xanalys. History - &clsql; is written by Kevin M. Rosenberg in 2001 and was based - substantially on Pierre R. Mai's excellent &maisql; package. In - April 2004, Marcus Pearce ported the UncommonSQL to &clsql; - which provides a CommonSQL-compatible API for &clsql;. The main - changes from &maisql; are: + The &clsql; project was started by Kevin M. Rosenberg in 2001 to + support SQL access on multiple Common Lisp implementations using + the &uffi; library. The initial code was based substantially on + Pierre R. Mai's excellent &maisql; package. In late 2003, the + &usql; library was orphaned by its author, onShore Development, + Inc with the cessation of further development. In April 2004, + Marcus Pearce ported the &usql; library to &clsql;. The &usql; + library provides a &commonsql;-compatible API for &clsql;. + + + The main changes from &maisql; and &usql; are: - port from the &cmucl; FFI to &uffi;. + Port from the &cmucl; FFI to &uffi; which provide + compatibility with the major Common Lisp + implementations. Optimized loading of integer and floating-point fields. - new &acl; ODBC interface back-end. + Additional database backends: &acl; ODBC and &sqlite;. - compatibility layer for &cmucl; specific code. + A compatibility layer for &cmucl; specific code. - much improved robustness for the &mysql; back-end + Much improved robustness for the &mysql; back-end along with version 4 client library support. - improved system loading. + Improved library loading and installation documentation. - improved packages and symbol export. + Improved packages and symbol export. - transaction support. + Pooled connections. - UncommonSQL support. + Integrated transaction support for the classic + &maisql; iteration macros. diff --git a/doc/usql-tutorial.lisp b/doc/usql-tutorial.lisp deleted file mode 100644 index 9c9b93f..0000000 --- a/doc/usql-tutorial.lisp +++ /dev/null @@ -1,190 +0,0 @@ - -(in-package :cl-user) - -;; You must set these variables to appropriate values. -(defvar *tutorial-database-type* nil - "Possible values are :postgresql,:postgresql-socket :mysql or :sqlite") -(defvar *tutorial-database-name* "" - "The name of the database we will work in.") -(defvar *tutorial-database-user* "" - "The name of the database user we will work as.") -(defvar *tutorial-database-server* "" - "The name of the database server if required") -(defvar *tutorial-database-password* "" - "The password if required") - -(sql:def-view-class employee () - ((emplid - :db-kind :key - :db-constraints :not-null - :nulls-ok nil - :type integer - :initarg :emplid) - (first-name - :accessor first-name - :type (string 30) - :initarg :first-name) - (last-name - :accessor last-name - :type (string 30) - :initarg :last-name) - (email - :accessor employee-email - :type (string 100) - :nulls-ok t - :initarg :email) - (companyid - :type integer) - (company - :accessor employee-company - :db-kind :join - :db-info (:join-class company - :home-key companyid - :foreign-key companyid - :set nil)) - (managerid - :type integer - :nulls-ok t) - (manager - :accessor employee-manager - :db-kind :join - :db-info (:join-class employee - :home-key managerid - :foreign-key emplid - :set nil))) - (:base-table employee)) - -(sql:def-view-class company () - ((companyid - :db-type :key - :db-constraints :not-null - :type integer - :initarg :companyid) - (name - :type (string 100) - :initarg :name) - (presidentid - :type integer) - (president - :reader president - :db-kind :join - :db-info (:join-class employee - :home-key presidentid - :foreign-key emplid - :set nil)) - (employees - :reader company-employees - :db-kind :join - :db-info (:join-class employee - :home-key companyid - :foreign-key companyid - :set t))) - (:base-table company)) - -;; Connect to the database (see the CLSQL documentation for vendor -;; specific connection specs). -(sql:connect `(,*tutorial-database-server* - ,*tutorial-database-name* - ,*tutorial-database-user* - ,*tutorial-database-password*) - :database-type *tutorial-database-type*) - -;; Record the sql going out, helps us learn what is going -;; on behind the scenes -(sql:start-sql-recording) - -;; Create the tables for our view classes -;; First we drop them, ignoring any errors -(ignore-errors - (sql:drop-view-from-class 'employee) - (sql:drop-view-from-class 'company)) - -(sql:create-view-from-class 'employee) -(sql:create-view-from-class 'company) - - -;; Create some instances of our view classes -(defvar employee1 (make-instance 'employee - :emplid 1 - :first-name "Vladamir" - :last-name "Lenin" - :email "lenin@soviet.org")) - -(defvar company1 (make-instance 'company - :companyid 1 - :name "Widgets Inc.")) - - -(defvar employee2 (make-instance 'employee - :emplid 2 - :first-name "Josef" - :last-name "Stalin" - :email "stalin@soviet.org")) - -;; Lenin manages Stalin (for now) -(sql:add-to-relation employee2 'manager employee1) - -;; Lenin and Stalin both work for Widgets Inc. -(sql:add-to-relation company1 'employees employee1) -(sql:add-to-relation company1 'employees employee2) - -;; Lenin is president of Widgets Inc. -(sql:add-to-relation company1 'president employee1) - -(sql:update-records-from-instance employee1) -(sql:update-records-from-instance employee2) -(sql:update-records-from-instance company1) - -;; lets us use the functional -;; sql interface -(sql:locally-enable-sql-reader-syntax) - - -(format t "The email address of ~A ~A is ~A" - (first-name employee1) - (last-name employee1) - (employee-email employee1)) - -(setf (employee-email employee1) "lenin-nospam@soviets.org") - -;; Update the database -(sql:update-records-from-instance employee1) - -(let ((new-lenin (car - (sql:select 'employee - :where [= [slot-value 'employee 'emplid] 1])))) - (format t "His new email is ~A" - (employee-email new-lenin))) - - -;; Some queries - -;; all employees -(sql:select 'employee) -;; all companies -(sql:select 'company) - -;; employees named Lenin -(sql:select 'employee :where [= [slot-value 'employee 'last-name] - "Lenin"]) - -(sql:select 'company :where [= [slot-value 'company 'name] - "Widgets Inc."]) - -;; Employees of Widget's Inc. -(sql:select 'employee - :where [and [= [slot-value 'employee 'companyid] - [slot-value 'company 'companyid]] - [= [slot-value 'company 'name] - "Widgets Inc."]]) - -;; Same thing, except that we are using the employee -;; relation in the company view class to do the join for us, -;; saving us the work of writing out the SQL! -(company-employees company1) - -;; President of Widgets Inc. -(president company1) - -;; Manager of Josef Stalin -(employee-manager employee2) diff --git a/doc/usql.xml b/doc/usql.xml deleted file mode 100644 index 0ee305d..0000000 --- a/doc/usql.xml +++ /dev/null @@ -1,736 +0,0 @@ - - -%myents; -]> - - - UncommonSQL - - - Introduction - - - The goal of this tutorial is to guide a new developer thru the - process of creating a set of &usql; classes providing a - Object-Oriented interface to persistent data stored in an &sql; - database. We will assume that the reader is familiar with how - &sql; works, how relations (tables) should be structured, and - has created at least one &sql; application previously. We will - also assume a minor level of experience with Common Lisp. - - - - &usql; provides two different interfaces to &sql; databases, a - Functional interface, and an Object-Oriented interface. The - Functional interface consists of a special syntax for embedded - &sql; expressions in Lisp, and provides lisp functions for &sql; - operations like SELECT and - UPDATE. The object-oriented interface provides - a way for mapping Common Lisp Objects System (CLOS) objects into - databases and includes functions for inserting new objects, - querying objects, and removing objects. Most applications will - use a combination of the two. - - - - &usql; is based on the CommonSQL package from Xanalys, so the - documentation that Xanalys makes available online is useful for - &usql; as well. It is suggested that developers new to &usql; check - their documentation out, as any differences between CommonSQL - and &usql; are minor. Xanalys makes the following documents - available: - - - - - - - Xanalys LispWorks User Guide - The CommonSQL Package - - - - - - - - - Xanalys LispWorks Reference Manual - The SQL Package - - - - - - - - CommonSQL Tutorial by Nick Levine - - - - - - - - Data Modeling with UncommonSQL - - - Before we can create, query and manipulate &usql; objects, we - need to define our data model as noted by Philip Greenspun - - - - Philip Greenspun's "SQL For Web Nerds" - Data - Modeling - - - - - - - When data modeling, you are telling the RDBMS the following: - - - - - What elements of the data you will store - - - How large each element can be - - - What kind of information each element can contain - - - What elements may be left blank - - - Which elements are constrained to a fixed range - - - Whether and how various tables are to be linked - - - - - With &sql; database one would do this by defining a set of - relations, or tables, followed by a set of queries for joining - the tables together in order to construct complex records. - However, with &usql; we do this by defining a set of CLOS - classes, specifying how they will be turned into tables, and how - they can be joined to one another via relations between their - attributes. The &sql; tables, as well as the queries for - joining them together are created for us automatically, saving - us from dealing with some of the tedium of &sql;. - - - - Let us start with a simple example of two &sql; tables, and the - relations between them. - - - -CREATE TABLE EMPLOYEE ( - emplid NOT NULL number(38), - first_name NOT NULL varchar2(30), - last_name NOT NULL varchar2(30), - emall varchar2(100), - companyid NOT NULL number(38), - managerid number(38) -) - -CREATE TABLE COMPANY ( - companyid NOT NULL number(38), - name NOT NULL varchar2(100), - presidentid NOT NULL number(38) -) - - - -This is of course the canonical &sql; tutorial example, "The Org Chart". - - - -In &usql;, we would have two "view classes" (a fancy word for a class -mapped into a database). They would be defined as follows: - - - -(clsql-usql:def-view-class employee () - ((emplid - :db-kind :key - :db-constraints :not-null - :type integer - :initarg :emplid) - (first-name - :accessor first-name - :type (string 30) - :initarg :first-name) - (last-name - :accessor last-name - :type (string 30) - :initarg :last-name) - (email - :accessor employee-email - :type (string 100) - :nulls-ok t - :initarg :email) - (companyid - :type integer) - (managerid - :type integer - :nulls-ok t)) - (:base-table employee)) - -(clsql-usql:def-view-class company () - ((companyid - :db-type :key - :db-constraints :not-null - :type integer - :initarg :companyid) - (name - :type (string 100) - :initarg :name) - (presidentid - :type integer)) - (:base-table company)) - - - - The DEF-VIEW-CLASS macro is just like the - normal CLOS DEFCLASS macro, except that it - handles several slot options that DEFCLASS - doesn't. These slot options have to do with the mapping of the slot - into the database. We only use a few of the slot options in the - above example, but there are several others. - - - - - - :column - The name of the &sql; column this slot is stored in. - Defaults to the slot name. If the slot name is not a valid &sql; - identifier, it is escaped, so foo-bar becomes foo_bar. - - - - - :db-kind - The kind of database mapping which - is performed for this slot. :base indicates - the slot maps to an ordinary column of the database view. - :key indicates that this slot corresponds to - part of the unique keys for this view, :join - indicates a join slot representing a relation to another view - and :virtual indicates that this slot is an ordinary CLOS slot. - Defaults to :base. - - - - :db-reader - If a string, then when reading - values from the database, the string will be used for a format - string, with the only value being the value from the database. - The resulting string will be used as the slot value. If a - function then it will take one argument, the value from the - database, and return the value that should be put into the slot. - - - - - :db-writer - If a string, then when reading - values from the slot for the database, the string will be used - for a format string, with the only value being the value of the - slot. The resulting string will be used as the column value in - the database. If a function then it will take one argument, the - value of the slot, and return the value that should be put into - the database. - - - - :db-type - A string which will be used as the - type specifier for this slots column definition in the database. - - - - - :nulls-ok - If &t;, all &sql; &null; values - retrieved from the database become nil; if &nil;, all &null; - values retrieved are converted by - DATABASE-NULL-VALUE. - - - - :db-info - A join specification. - - - - - In our example each table as a primary key attribute, which is - required to be unique. We indicate that a slot is part of the - primary key (&usql; supports multi-field primary keys) by specifying - the :db-kind key slot option. - - - - The &sql; type of a slot when it is mapped into the database is - determined by the :type slot option. The argument - for the :type option is a Common Lisp datatype. - The &usql; framework will determine the appropriate mapping - depending on the database system the table is being created in. If - we really wanted to determine what &sql; type was used for a slot, - we could specify a :db-type option like - "NUMBER(38)" and we would be guaranteed that the slot would be - stored in the database as a NUMBER(38). This is not recomended - because it could makes your view class unportable across database - systems. - - - - DEF-VIEW-CLASS also supports some class - options, like :base-table. The - :base-table option specifies what the table name - for the view class will be when it is mapped into the database. - - - - -Class Relations - - -In an &sql; only application, the EMPLOYEE and -COMPANY tables can be queried to determine things -like, "Who is Vladamir's manager?", What company does Josef work -for?", and "What employees work for Widgets Inc.". This is done by -joining tables with an &sql; query. - - - -Who works for Widgets Inc.? - - - -SELECT first_name, last_name FROM employee, company - WHERE employee.companyid = company.companyid - AND company.company_name = "Widgets Inc." - - - -Who is Vladamir's manager? - - - -SELECT managerid FROM employee - WHERE employee.first_name = "Vladamir" - AND employee.last_name = "Lenin" - - - -What company does Josef work for? - - - -SELECT company_name FROM company, employee - WHERE employee.first_name = "Josef" - AND employee.last-name = "Stalin" - AND employee.companyid = company.companyid - - - -With &usql; however we do not need to write out such queries because -our view classes can maintain the relations between employees and -companies, and employees to their managers for us. We can then access -these relations like we would any other attribute of an employee or -company object. In order to do this we define some join slots for our -view classes. - - - -What company does an employee work for? If we add the following slot -definition to the employee class we can then ask for it's -COMPANY slot and get the appropriate result. - - - - ;; In the employee slot list - (company - :accessor employee-company - :db-kind :join - :db-info (:join-class company - :home-key companyid - :foreign-key companyid - :set nil)) - - - -Who are the employees of a given company? And who is the president of -it? We add the following slot definition to the company view class and -we can then ask for it's EMPLOYEES slot and get the -right result. - - - - ;; In the company slot list - (employees - :reader company-employees - :db-kind :join - :db-info (:join-class employee - :home-key companyid - :foreign-key companyid - :set t)) - - (president - :reader president - :db-kind :join - :db-info (:join-class employee - :home-key presidentid - :foreign-key emplid - :set nil)) - - - -And lastly, to define the relation between an employee and their -manager: - - - - ;; In the employee slot list - (manager - :accessor employee-manager - :db-kind :join - :db-info (:join-class employee - :home-key managerid - :foreign-key emplid - :set nil)) - - - -&usql; join slots can represent one-to-one, one-to-many, and -many-to-many relations. Above we only have one-to-one and one-to-many -relations, later we will explain how to model many-to-many relations. -First, let's go over the slot definitions and the available options. - - - -In order for a slot to be a join, we must specify that it's -:db-kind :join, as opposed to -:base or :key. Once we do that, we -still need to tell &usql; how to create the join statements for the -relation. This is what the :db-info option does. It -is a list of keywords and values. The available keywords are: - - - - - - :join-class - The view class to which we want - to join. It can be another view class, or the same view class - as our object. - - - - :home-key - The slot(s) in the immediate object - whose value will be compared to the foreign-key slot(s) in the - join-class in order to join the two tables. It can be a single - slot-name, or it can be a list of slot names. - - - - :foreign-key - The slot(s) in the join-class - which will be compared to the value(s) of the home-key. - - - - - :set - A boolean which if false, indicates that - this is a one-to-one relation, only one object will be returned. - If true, than this is a one-to-many relation, a list of objects - will be returned when we ask for this slots value. - - - - -There are other :join-info options available in &usql;, but we will -save those till we get to the many-to-many relation examples. - - - - - -Object Creation - - -Now that we have our model laid out, we should create some object. -Let us assume that we have a database connect set up already. We -first need to create our tables in the database: - - - -Note: the file doc/usql-tutorial.lisp contains -view class definitions which you can load into your list at this point -in order to play along at home. - - - -(clsql-usql:create-view-from-class 'employee) -(clsql-usql:create-view-from-class 'company) - - - -Then we will create our objects. We create them just like you would -any other CLOS object: - - - -(defvar employee1 (make-instance 'employee - :emplid 1 - :first-name "Vladamir" - :last-name "Lenin" - :email "lenin@soviet.org")) - -(defvar company1 (make-instance 'company - :companyid 1 - :name "Widgets Inc.")) - - -(defvar employee2 (make-instance 'employee - :emplid 2 - :first-name "Josef" - :last-name "Stalin" - :email "stalin@soviet.org")) - - - -In order to insert an objects into the database we use the -UPDATE-RECORDS-FROM-INSTANCE function as follows: - - - -(clsql-usql:update-records-from-instance employee1) -(clsql-usql:update-records-from-instance employee2) -(clsql-usql:update-records-from-instance company1) - - - -Now we can set up some of the relations between employees and -companies, and their managers. The -ADD-TO-RELATION method provides us with an easy -way of doing that. It will update both the relation slot, as well as -the home-key and foreign-key slots in both objects in the relation. - - - -;; Lenin manages Stalin (for now) -(clsql-usql:add-to-relation employee2 'manager employee1) - -;; Lenin and Stalin both work for Widgets Inc. -(clsql-usql:add-to-relation company1 'employees employee1) -(clsql-usql:add-to-relation company1 'employees employee2) - -;; Lenin is president of Widgets Inc. -(clsql-usql:add-to-relation company1 'president employee1) - - - - After you make any changes to an object, you have to specifically - tell &usql; to update the &sql; database. The - UPDATE-RECORDS-FROM-INSTANCE method will write - all of the changes you have made to the object into the database. - - - - Since &usql; objects re just normal CLOS objects, we can manipulate - their slots just like any other object. For instance, let's say - that Lenin changes his email because he was getting too much spam - from the German Socialists. - - - -;; Print Lenin's current email address, change it and save it to the -;; database. Get a new object representing Lenin from the database -;; and print the email - -;; This lets us use the functional &usql; interface with [] syntax -(clsql-usql:locally-enable-sql-reader-syntax) - -(format t "The email address of ~A ~A is ~A" - (first-name employee1) - (last-name employee1) - (employee-email employee1)) - -(setf (employee-email employee1) "lenin-nospam@soviets.org") - -;; Update the database -(clsql-usql:update-records-from-instance employee1) - -(let ((new-lenin (car (clsql-usql:select 'employee - :where [= [slot-value 'employee 'emplid] 1])))) - (format t "His new email is ~A" - (employee-email new-lenin))) - - - - Everything except for the last LET expression - is already familiar to us by now. To understand the call to - CLSQL-USQL:SELECT we need to discuss the - Functional &sql; interface and it's integration with the Object - Oriented interface of &usql;. - - - - - -Finding Objects - - - Now that we have our objects in the database, how do we get them out - when we need to work with them? &usql; provides a functional - interface to &sql;, which consists of a special Lisp reader macro - and some functions. The special syntax allows us to embed &sql; in - lisp expressions, and lisp expressions in &sql;, with ease. - - - - Once we have turned on the syntax with the expression: - - - -(clsql-usql:locally-enable-sql-reader-syntax) - - - - We can start entering fragments of &sql; into our lisp reader. We - will get back objects which represent the lisp expressions. These - objects will later be compiled into &sql; expressions that are - optimized for the database backed we are connected to. This means - that we have a database independent &sql; syntax. Here are some - examples: - - - -;; an attribute or table name -[foo] => #<CLSQL-USQL-SYS::SQL-IDENT-ATTRIBUTE FOO> - -;; a attribute identifier with table qualifier -[foo bar] => #<CLSQL-USQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR> - -;; a attribute identifier with table qualifier -[= "Lenin" [first_name]] => - #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)> - -[< [emplid] 3] => - #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)> - -[and [< [emplid] 2] [= [first_name] "Lenin"]] => - #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND - (FIRST_NAME = 'Lenin'))> - - -;; If we want to reference a slot in an object we can us the -;; SLOT-VALUE sql extension -[= [slot-value 'employee 'emplid] 1] => - #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)> - -[= [slot-value 'employee 'emplid] - [slot-value 'company 'presidentid]] => - #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)> - - - - The SLOT-VALUE operator is important because it - let's us query objects in a way that is robust to any changes in the - object->table mapping, like column name changes, or table name - changes. So when you are querying objects, be sure to use the - SLOT-VALUE &sql; extension. - - - - Since we can now formulate &sql; relational expression which can be - used as qualifiers, like we put after the WHERE - keyword in &sql; statements, we can start querying our objects. - &usql; provides a function SELECT which can return - use complete objects from the database which conform to a qualifier, - can be sorted, and various other &sql; operations. - - - - The first argument to SELECT is a class name. it - also has a set of keyword arguments which are covered in the - documentation. For now we will concern ourselves only with the - :where keyword. Select returns a list of objects, or nil if it - can't find any. It's important to remember that it always returns a - list, so even if you are expecting only one result, you should - remember to extract it from the list you get from - SELECT. - - - -;; all employees -(clsql-usql:select 'employee) -;; all companies -(clsql-usql:select 'company) - -;; employees named Lenin -(clsql-usql:select 'employee :where [= [slot-value 'employee 'last-name] - "Lenin"]) - -(clsql-usql:select 'company :where [= [slot-value 'company 'name] - "Widgets Inc."]) - -;; Employees of Widget's Inc. -(clsql-usql:select 'employee - :where [and [= [slot-value 'employee 'companyid] - [slot-value 'company 'companyid]] - [= [slot-value 'company 'name] - "Widgets Inc."]]) - -;; Same thing, except that we are using the employee -;; relation in the company view class to do the join for us, -;; saving us the work of writing out the &sql;! -(company-employees company1) - -;; President of Widgets Inc. -(president company1) - -;; Manager of Josef Stalin -(employee-manager employee2) - - - - - -Deleting Objects - - - Now that we know how to create objects in our database, manipulate - them and query them (including using our predefined relations to - save us the trouble writing alot of &sql;) we should learn how to - clean up after ourself. It's quite simple really. The function - DELETE-INSTANCE-RECORDS will remove an object - from the database. However, when we remove an object we are - responsible for making sure that the database is left in a correct - state. - - - - For example, if we remove a company record, we need to either remove - all of it's employees or we need to move them to another company. - Likewise if we remove an employee, we should make sure to update any - other employees who had them as a manager. - - - - - -Conclusion - - - There are alot more nooks and crannies to &usql;, some of which are - covered in the Xanalys documents we refered to earlier, some are - not. The best documentation at this time is still the source code - for &usql; itself and the inline documentation for it's various - function. - - - - - diff --git a/sql/README b/sql/README deleted file mode 100644 index c0ea747..0000000 --- a/sql/README +++ /dev/null @@ -1,64 +0,0 @@ -INTRODUCTIION - -CLSQL-USQL is a high level SQL interface for Common Lisp which is -based on the CommonSQL package from Xanalys. It was originally -developed at Onshore Development, Inc. based on Pierre Mai's MaiSQL -package. It now incorporates some of the code developed for CLSQL. See -the files CONTRIBUTORS and COPYING for more details. - -CLSQL-USQL depends on the low-level database interfaces provided by -CLSQL and includes both a functional and an object oriented -interface to SQL RDBMS. - -DOCUMENTATION - -A CLSQL-USQL tutorial can be found in the directory doc/ - -Also see the CommonSQL documentation avaialble on the Lispworks website: - -Xanalys LispWorks User Guide - The CommonSQL Package -http://www.lispworks.com/reference/lw43/LWUG/html/lwuser-167.htm - -Xanalys LispWorks Reference Manual -- The SQL Package -http://www.lispworks.com/reference/lw43/LWRM/html/lwref-383.htm - -CommonSQL Tutorial by Nick Levine -http://www.ravenbrook.com/doc/2002/09/13/common-sql/ - - -PREREQUISITES - - o COMMON LISP: currently CMUCL, SBCL, Lispworks - o RDBMS: currently Postgresql, Mysql, Sqlite - o ASDF (from http://cvs.sourceforge.net/viewcvs.py/cclan/asdf/) - o CLSQL-2.0.0 or later (from http://clsql.b9.com) - o RT for running the test suite (from http://files.b9.com/rt/rt.tar.gz) - - -INSTALLATION - -Just load clsql-usql.asd or put it somewhere where ASDF can find it -and call: - -(asdf:oos 'asdf:load-op :clsql-usql) - -You'll then need to load a CLSQL backend before you can do anything. - -To run the regression tests load clsql-usql-tests.asd or put it -somewhere where ASDF can find it, edit the file tests/test-init.lisp -and set the following variables to appropriate values: - - *test-database-server* - *test-database-name* - *test-database-user* - *test-database-password* - -And then call: - -(asdf:oos 'asdf:load-op :clsql-usql-tests) -(usql-tests:test-usql BACKEND) - -where BACKEND is the CLSQL database interface to use (currently one of -:postgresql, :postgresql-socket, :sqlite or :mysql). - - diff --git a/tests/README b/tests/README index c20387a..d7418a3 100644 --- a/tests/README +++ b/tests/README @@ -1,3 +1,29 @@ +* RUNNING THE REGRESSION SUITE + +Just load clsql.asd or put it somewhere where ASDF can find it +and call: + +(asdf:oos 'asdf:load-op 'clsql) + +You'll then need to load a CLSQL backend before you can do anything. + +To run the regression tests load clsql-tests.asd or put it +somewhere where ASDF can find it, edit the file tests/test-init.lisp +and set the following variables to appropriate values: + + *test-database-server* + *test-database-name* + *test-database-user* + *test-database-password* + +And then call: + +(asdf:oos 'asdf:load-op 'clsql-tests) +(clsql-tests:test-usql BACKEND) + +where BACKEND is the CLSQL database interface to use (currently one of +:postgresql, :postgresql-socket, :sqlite or :mysql). + * REGRESSION TEST SUITE GOALS The intent of this test suite is to provide sufficient coverage for @@ -107,4 +133,4 @@ will be run. *** Object Create/Modification/Deletion in a context -- partly covered already *** Interaction of multiple contexts *** Schema manipulation within a context - *** Rollback and error handling within a context \ No newline at end of file + *** Rollback and error handling within a context