sqlite3-tinyclos
![[Picture of an egg]](egg.jpg)
A bridge between persistent storage in SQLite3 tables and TinyCLOS objects.
This egg is intended to quickly establish an object oriented interface to data in an SQLite3 database. The interface you are provided with is rather basic, but easily extensible.
In the simplest case you just create the database with its schema of tables and then call sqlite3:define-stored-object-class once for each table you want to access through this interface. See the example below for a first impression.
This procedure creates a TinyCLOS class of metaclass <sqlite3:stored-object-class> and superclasses supers, defaulting to a list containing only <sqlite3:stored-object>. The new class can be instantiated to access data stored in the table of the given SQLite3 db.
Additional value slots for the instances can be obtained by specifying a list as keyword parameter slots.
The list specified for supers can be chosen arbitrarily but it should probably contain either <sqlite3:stored-object> itself or a subclass of it. To just add superclasses in addition to the default one you may want to use add-super keyword parameters, which are prepended in sequence to the list specified by supers or the default list.
The prefix referred to in the following paragraphs is either taken from the corresponding keyword argument or, if no such argument is given, is assumed to be the empty string.
The generated class has the name given as a keyword argument or, if no such argument is present, a name composed of the prefix and the table name stripped of any trailing 's' characters.
The generated class is assigned to the global variable specified by symbol or, if no such argument is given, the symbol composed of an opening angle bracket, the name of the class and a closing angle bracket.
For all columns in the table that are not part of the primary key, two accessor methods for retrieving and setting them are defined, with names computed by sqlite3:field-name->getter-symbol and sqlite3:field-name->setter-symbol respectively. If a list is passed in the keyword argument no-getter-or-setter and it contains the name of a database field, no getter or setter is created for this field. Likewise no getter is generated for columns mentioned in no-getter and no setter is generated for those mentioned in no-setter.
These methods defined by sqlite3:define-stored-object-class retrieve the value of a regular or boolean field in the database respectively. For boolean fields, whose names start with is_ in the database, an automatic conversion from NULL or 0 to #f and anything else to #t is performed.
These methods defined by sqlite3:define-stored-object-class set the value of a field in the database. For boolean fields, whose names start with is_ in the database, an automatic conversion from #f to 0 and anything else to 1 is performed.
The metaclass for classes generated by sqlite3:define-stored-object-class. It provides its class instances with facilities to store the database handle and analyze the schema.
In particular, instances of this class contain db, table, pk and fields slots. Of these db and table must be set when instantiating (which is automatically done by sqlite3:define-stored-object-class), while pk and fields are computed by the initialize method. All these fields should be read with the accessors described below.
The common base class of classes generated by sqlite3:define-stored-object-class. It provides general facilities for row level data access.
In particular, instances of this class contain a pk slot storing the current values of the primary key columns for this object. The contents of this slot should be retrieved and set using the accessors described below. When creating an object of class <sqlite3:stored-object>, the pk slot can either be set with the usual TinyCLOS initialization argument syntax or by specifying exactly all the primary key values as initialization arguments.
The initialize method for this class also checks whether a row with the specified primary key already exists in the database using sqlite3:in-store? and inserts such a row using sqlite3:create-in-store! if this is not the case.
This subclass of <sqlite3:stored-object> has a modified sqlite3:in-store? method that automatically allocates a new primary key one larger than the largest one in use if the object was created with an empty primary key. This means that objects created by a simple (make <class>) call, where <class> is a subclass of <sqlite3:stored-object/automatic-integer-pk>, will immediately be entered into the database with a new unique id.
Note that this class can only be used sensibly with tables that have a single integer primary key. Also note that instances of this class should only be created with proper exclusive locks on the database in place.
The following methods are common to the standard metaclass and class:
Given a stored object class or a stored object instance this method returns the database connection that object belongs to.
Given a stored object class or a stored object instance this method returns the name of the database table that holds the instances of the class or the object respectively.
Given a stored object class this method returns a list of column names that hold the primary key for objects of this class.
Given a stored object instance this method returns the list of primary key values for this object corresponding in sequence to the list of primary key columns.
Given a stored object class or a stored object instance this method returns a comma separated list of primary key column names.
Given a stored object class or a stored object instance this method returns a comma separated list of tokens in the form primary_key_column_name = ?.
Given a stored object class or a stored object instance this method returns a list of tokens in the form primary_key_column_name = ?, separated by the word AND.
Given a stored object class or a stored object instance this method returns a list of column names which are not part of the primary key.
The following methods are available for objects of class <sqlite3:stored-object>:
Changes the value of the primary key columns for the given object to the new given values.
Checks whether a row with the currently set primary key column values (still) exists in the database.
Creates a row with the currently set primary key column values and default values for all other columns if no such row already exists. Returns #t or #f if a row was inserted or not.
This method may return #f if a row with the given primary key already exists, but it may also return #f if the database imposes some constraints on the values of further rows that is not fulfilled by the default values. You should generally either not create tables with such constraints for use with this egg or you should override sqlite3:create-in-store! for your stored object classes because it is called from initialize if an object of class <sqlite3:stored-object> is created with a not yet existing primary key.
Removes the row with the currently stored primary key column values from the database and returns #t or returns #f and does nothing if no such row exists.
The following two methods should seldomly be needed, as specific field accessors are generated automatically by sqlite3:define-stored-object-class:
Retrieves the value stored in column name from the table apropriate for this object where the primary key columns have the values currently stored in this object.
Sets the column name to the given value in the table apropriate for this object where the primary key columns have the values stored in this object.
Given the name of a database column this procedure returns a mangled version of the name with underscores replaced by dashes and the prefix is- replaced by a trailing question mark. Thus the return value is a scheme symbol in the form <prefix><name>[?].
This procedure is internally used by sqlite3:define-stored-object-class to compute the names of getter methods.
Given the name of a database column this procedure returns a mangled version of the name with underscores replaced by dashes and the prefix is- stripped. In addition to the given prefix, set- is prepended to the result and an exclamation mark is appended. Thus the return value is a scheme symbol in the form <prefix>set-<name>!.
This procedure is internally used by sqlite3:define-stored-object-class to compute the names of setter methods.
;;;; sqlite3-tinyclos-demo.scm ;;;; Small demonstration of the capabilities of sqlite3-tinyclos ;;; load extensions (require-extension sqlite3 tinyclos sqlite3-tinyclos) ;;; create a database (define db (sqlite3:open "test.db")) (sqlite3:exec db "CREATE TABLE eggs( name TEXT NOT NULL, license TEXT, is_module INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(name), CHECK(is_module IN (0, 1)));") ;;; define the object class (sqlite3:define-stored-object-class db "eggs") ;;; add some data (let ((egg (make <egg> "sqlite3"))) ;; now the entry should already exist (print (sqlite3:in-store? egg)) ;; let's add some data (set-license! egg "BSD") ;; and read it back (print (license egg)) ;; other fields are filled with default data (print (module? egg)) ;; changing the primary key is possible as well (sqlite3:set-pk! egg "foo") (print (sqlite3:pk egg)) ;; which does not change data, of course (print (license egg)) ;; and we can also delete the entry (print (sqlite3:remove-from-store! egg)) ;; then it is gone (print (sqlite3:in-store? egg))) ;;;; vim:set shiftwidth=2 softtabstop=2: ;;;;
Copyright (c) 2006, Thomas Chust <chust@web.de>. All rights reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of the author nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission. THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.