From 595658021ebaf450894bb143a346e409b55e02af Mon Sep 17 00:00:00 2001 From: "Kevin M. Rosenberg" Date: Wed, 7 Apr 2004 16:59:18 +0000 Subject: [PATCH] r8851: more integration --- ChangeLog | 5 +- NEWS | 7 +- doc/bookinfo.xml | 1 + ...usql-tutorial.lisp => clsql-tutorial.lisp} | 0 doc/clsql.xml | 2 +- doc/{usql.xml => csql.xml} | 150 +++++++++--------- doc/entities.inc | 1 + doc/intro.xml | 44 ++--- sql/README | 64 -------- tests/README | 28 +++- 10 files changed, 136 insertions(+), 166 deletions(-) rename doc/{usql-tutorial.lisp => clsql-tutorial.lisp} (100%) rename doc/{usql.xml => csql.xml} (83%) delete mode 100644 sql/README 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/usql-tutorial.lisp b/doc/clsql-tutorial.lisp similarity index 100% rename from doc/usql-tutorial.lisp rename to doc/clsql-tutorial.lisp 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/usql.xml b/doc/csql.xml similarity index 83% rename from doc/usql.xml rename to doc/csql.xml index 0ee305d..d0d09fa 100644 --- a/doc/usql.xml +++ b/doc/csql.xml @@ -5,15 +5,16 @@ %myents; ]> - - UncommonSQL + + &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 &usql; classes providing a + 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 @@ -22,7 +23,7 @@ - &usql; provides two different interfaces to &sql; databases, a + &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; @@ -35,11 +36,11 @@ - &usql; is based on the CommonSQL package from Xanalys, so the + &clsql; 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 + &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: @@ -47,8 +48,9 @@ - Xanalys LispWorks User Guide - The CommonSQL Package - + Xanalys &lw; User Guide - The &commonsql; + Package + @@ -56,7 +58,8 @@ - Xanalys LispWorks Reference Manual - The SQL Package + Xanalys &lw; Reference Manual - The SQL + Package @@ -64,7 +67,7 @@ - CommonSQL Tutorial by Nick Levine + &commonsql; Tutorial by Nick Levine @@ -72,10 +75,10 @@ - Data Modeling with UncommonSQL + Data Modeling with &clsql; - Before we can create, query and manipulate &usql; objects, we + Before we can create, query and manipulate &clsql; objects, we need to define our data model as noted by Philip Greenspun @@ -89,27 +92,28 @@ - When data modeling, you are telling the RDBMS the following: + When data modeling, you are telling the relational database + management system (RDBMS) the following: - What elements of the data you will store + What elements of the data you will store. - How large each element can be + How large each element can be. - What kind of information each element can contain + What kind of information each element can contain. - What elements may be left blank + What elements may be left blank. - Which elements are constrained to a fixed range + Which elements are constrained to a fixed range. - Whether and how various tables are to be linked + Whether and how various tables are to be linked. @@ -117,7 +121,7 @@ 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 + 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 @@ -152,12 +156,12 @@ 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 +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-usql:def-view-class employee () +(clsql:def-view-class employee () ((emplid :db-kind :key :db-constraints :not-null @@ -183,7 +187,7 @@ mapped into a database). They would be defined as follows: :nulls-ok t)) (:base-table employee)) -(clsql-usql:def-view-class company () +(clsql:def-view-class company () ((companyid :db-type :key :db-constraints :not-null @@ -267,7 +271,7 @@ mapped into a database). They would be defined as follows: 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 + primary key (&clsql; supports multi-field primary keys) by specifying the :db-kind key slot option. @@ -275,7 +279,7 @@ mapped into a database). They would be defined as follows: 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 + 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 @@ -293,7 +297,7 @@ mapped into a database). They would be defined as follows: - + Class Relations @@ -336,7 +340,7 @@ SELECT company_name FROM company, employee -With &usql; however we do not need to write out such queries because +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 @@ -404,7 +408,7 @@ manager: -&usql; join slots can represent one-to-one, one-to-many, and +&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. @@ -414,7 +418,7 @@ 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 +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: @@ -449,13 +453,13 @@ is a list of keywords and values. The available keywords are: -There are other :join-info options available in &usql;, but we will +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 @@ -465,14 +469,14 @@ first need to create our tables in the database: -Note: the file doc/usql-tutorial.lisp contains +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-usql:create-view-from-class 'employee) -(clsql-usql:create-view-from-class 'company) +(clsql:create-view-from-class 'employee) +(clsql:create-view-from-class 'company) @@ -505,9 +509,9 @@ In order to insert an objects into the database we use the -(clsql-usql:update-records-from-instance employee1) -(clsql-usql:update-records-from-instance employee2) -(clsql-usql:update-records-from-instance company1) +(clsql:update-records-from-instance employee1) +(clsql:update-records-from-instance employee2) +(clsql:update-records-from-instance company1) @@ -520,25 +524,25 @@ 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) +(clsql: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) +(clsql:add-to-relation company1 'employees employee1) +(clsql:add-to-relation company1 'employees employee2) ;; Lenin is president of Widgets Inc. -(clsql-usql:add-to-relation company1 'president employee1) +(clsql: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 + 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 &usql; objects re just normal CLOS objects, we can manipulate + 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. @@ -549,8 +553,8 @@ the home-key and foreign-key slots in both objects in the relation. ;; 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) +;; 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) @@ -560,9 +564,9 @@ the home-key and foreign-key slots in both objects in the relation. (setf (employee-email employee1) "lenin-nospam@soviets.org") ;; Update the database -(clsql-usql:update-records-from-instance employee1) +(clsql:update-records-from-instance employee1) -(let ((new-lenin (car (clsql-usql:select 'employee +(let ((new-lenin (car (clsql:select 'employee :where [= [slot-value 'employee 'emplid] 1])))) (format t "His new email is ~A" (employee-email new-lenin))) @@ -571,19 +575,19 @@ the home-key and foreign-key slots in both objects in the relation. 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 + CLSQL:SELECT we need to discuss the Functional &sql; interface and it's integration with the Object - Oriented interface of &usql;. + 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? &usql; provides a functional + 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. @@ -594,7 +598,7 @@ the home-key and foreign-key slots in both objects in the relation. -(clsql-usql:locally-enable-sql-reader-syntax) +(clsql:locally-enable-sql-reader-syntax) @@ -608,31 +612,31 @@ the home-key and foreign-key slots in both objects in the relation. ;; an attribute or table name -[foo] => #<CLSQL-USQL-SYS::SQL-IDENT-ATTRIBUTE FOO> +[foo] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO> ;; a attribute identifier with table qualifier -[foo bar] => #<CLSQL-USQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR> +[foo bar] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR> ;; a attribute identifier with table qualifier [= "Lenin" [first_name]] => - #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)> + #<CLSQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)> [< [emplid] 3] => - #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)> + #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)> [and [< [emplid] 2] [= [first_name] "Lenin"]] => - #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND + #<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-USQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)> + #<CLSQL-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)> + #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)> @@ -647,7 +651,7 @@ the home-key and foreign-key slots in both objects in the relation. 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 + &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. @@ -665,19 +669,19 @@ the home-key and foreign-key slots in both objects in the relation. ;; all employees -(clsql-usql:select 'employee) +(clsql:select 'employee) ;; all companies -(clsql-usql:select 'company) +(clsql:select 'company) ;; employees named Lenin -(clsql-usql:select 'employee :where [= [slot-value 'employee 'last-name] +(clsql:select 'employee :where [= [slot-value 'employee 'last-name] "Lenin"]) -(clsql-usql:select 'company :where [= [slot-value 'company 'name] +(clsql:select 'company :where [= [slot-value 'company 'name] "Widgets Inc."]) ;; Employees of Widget's Inc. -(clsql-usql:select 'employee +(clsql:select 'employee :where [and [= [slot-value 'employee 'companyid] [slot-value 'company 'companyid]] [= [slot-value 'company 'name] @@ -697,7 +701,7 @@ the home-key and foreign-key slots in both objects in the relation. - + Deleting Objects @@ -720,15 +724,15 @@ the home-key and foreign-key slots in both objects in the relation. - + Conclusion - There are alot more nooks and crannies to &usql;, some of which are + 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 &usql; itself and the inline documentation for it's various - function. + 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/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 -- 2.34.1