chickadee » pandora

Outdated egg!

This is an egg for CHICKEN 4, the unsupported old release. You're almost certainly looking for the CHICKEN 5 version of this egg, if it exists.

If it does not exist, there may be equivalent functionality provided by another egg; have a look at the egg index. Otherwise, please consider porting this egg to the current version of CHICKEN.

Pandora

Description

An object-relation mapper using the prometheus object system.

Author

Thomas Chust

Requires

And to use it, one of the supported database backend eggs.

Documentation

Pandora provides utilities to access SQL datastores by manipulating object graphs. No SQL statements have to be written by hand.

If SQL source is passed to Pandora directly, it is in the form of fragment lists, that is lists containing strings or the symbol ? where a statement parameter placeholder should be inserted by the database driver.

Pandora comes with backends for SQLite3 and PostgreSQL databases. If you want to use these backends, you also have to install the sqlite3 and/or postgresql eggs respectively. Additional backends can be added easily.

Pandora and its bundled backends all support static linking.

Prototypical objects

(TODO: these are objects, not constants. But wiki syntax currently has no <object> tag)

*sql-datastore*constant

A generic container that can be queried using SQL. It is possible to use this object directly to obtain dummy datasets that are potentially useful to generate SQL statements easily. To connect to an actual database, use the connect method.

Database drivers will usually want to inherit from this object, override the methods to fetch data and register their child using add-connection-prototype!

*sql-dataset*constant

A representation of a set of rows in an SQL database. Methods of datastores return children of this object for further manipulation.

A dataset that is sufficiently restricted to contain only a single row can also be seen as a reference to that row.

Working with datastores

(DATASTORE 'connect URI) => DATASTOREmethod

Establish a connection to a real database.

Depending on the scheme of the URI, a registered prototype to clone is selected and the URI is passed on to the clone call. The new object is returned.

URI may be a string or a uri-generic record, but is always passed to the connection prototype's clone method as a record.

The drivers bundled with pandora will handle the URI schemes sqlite3 or memory for access to SQLite3 databases (the latter always creates a database in memory) and postgresql or psql for access to PostgreSQL databases.

The SQLite3 driver also supports a URI parameter cache that determines the size of the prepared statement cache, which defaults to 64.

Before using a backend, it must be loaded, but it doesn't have to be imported.

;; Example using SQLite3:
(require-extension pandora)
(require-library pandora-sqlite3)
(define store (*sql-datastore* 'connect "sqlite3:test.db?cache=16"))

;; Example using PostgreSQL:
(require-extension pandora)
(require-library pandora-postgresql)
(define store (*sql-datastore* 'connect "psql:"))

<method>(DATASTORE 'disconnect!) => VOID</enscript>

Close the connection this datastore holds to a real database. The default implementation does nothing.

(DATASTORE 'table NAME) => DATASETmethod

Create a dataset backed by the table called NAME in the datastore. Tables may be named by symbols or strings.

Actual database implementations should try to obtain meta information about the table and setup column accessors. See add-column-slots! for information how to do that conveniently.

;; Example:
(define-values (items tags item-tags)
(values
(store 'table 'items)
(store 'table 'tags)
(store 'table 'item-tags)))
(DATASTORE 'execute SQL [PARAMETERS]) => VOIDmethod

Executes the given SQL statement and discards all data possibly returned by it.

The method is responsible to transform the SQL fragment list passed to it into a suitable form for processing by the database.

The default implementation does nothing.

You should never have to call this method directly.

(DATASTORE 'fold PROC INIT SQL [PARAMETERS]) => VOIDmethod

Fetches data returned by the given SQL statement from from the database and applies PROC to INIT or its last return value and to all columns of each row.

The method is responsible to transform the SQL fragment list passed to it into a suitable form for processing by the database.

The default implementation just returns INIT.

You should never have to call this method directly.

(DATASTORE 'with-transaction THUNK) => OBJECTmethod

Executes THUNK within a transaction of the datastore. The transaction is rolled back if the THUNK returns #f or raises an error, otherwise the transaction is committed.

Returns the result of the callback.

(DATASTORE 'escape-sql-identifier ID) => STRINGmethod

Escapes an SQL identifier in a driver specific way, if necessary.

The default implementation just returns the ID unchanged or raises an error if it contains characters not valid in a standard SQL identifier.

Usually it should be unnecessary to call this method directly.

(DATASTORE 'name->table-clause NAME) => STRINGmethod

Turns a name into an SQL table clause, using driver specific escaping.

The default implementation converts hyphenated identifiers into camel case and delegates to escape-sql-identifier for the escaping.

Usually it should be unnecessary to call this method directly.

(DATASTORE 'name->column-clause NAME) => STRINGmethod

Turns a name into an SQL column clause, using driver specific escaping.

The default implementation converts hyphenated identifiers into lower case, underscore separated identifiers and delegates to escape-sql-identifier for the escaping.

Usually it should be unnecessary to call this method directly.

(DATASTORE 'add-connection-prototype! URI-SCHEME OBJECT) => VOIDmethod

Add a prototype for connections to databases using the given URI-SCHEME.

Only database drivers should have to call this method.

(DATASTORE 'delete-connection-prototype! URI-SCHEME) => VOIDmethod

Remove the prototype for connections to databases using the given URI-SCHEME.

This method just exists for completeness and is probably rarely useful.

Working with datasets

(DATASET 'filter {KEY: VALUE | KEY: VALUES | SQL PARAMETERS}*) => DATASETmethod

Obtain a dataset that is filtered selecting records with fields set to specific values or with arbitrary boolean SQL expressions.

;; Example:
(define containers (items 'filter name: '("bag" "box" "trunk")))
(DATASET 'match DATASET (KEY {REFERENCE | SQL . PARAMETERS})*) => DATASETmethod

Obtain a dataset that contains only rows where the KEYs in this dataset can be found as REFERENCEs in the given auxiliary dataset.

This method can be used to model complex relations between tables, but you may also want to look at add-link-slots! for a more convenient interface to specify common types of inter-table relations.

;; Example:
(define container-tags (tags 'match (item-tags 'match containers '(item id)) '(tag id)))
(DATASET 'order {KEY: {'asc | 'ascending | '< | 'desc | 'descending | '>} | SQL PARAMETERS}*) => DATASETmethod

Obtain a dataset that is sorted by the values of fields or as specified by arbitrary SQL expressions.

(DATASET 'count) => CARDINAL-INTEGERmethod

Count the number of rows in the dataset.

(DATASET 'first [THUNK]) => DATASETmethod

Returns a dataset containing only the first row of this one or the result of THUNK if this dataset is empty. The default thunk raises an access exception.

(DATASET 'all [limit: LIMIT] [offset: OFFSET]) => LISTmethod

Returns a list of datasets each containing one row from this dataset.

The range of returned rows may optionally be restricted using the LIMIT and OFFSET arguments.

(DATASET 'fold PROC INIT [limit: LIMIT] [offset: OFFSET]) => OBJECTmethod

Applies PROC to successive rows from this dataset and either INIT or its last return value. Returns either INIT (if the dataset is empty) or the result of the last application of PROC.

The range of processed rows may optionally be restricted using the LIMIT and OFFSET arguments.

(DATASET 'map PROC [limit: LIMIT] [offset: OFFSET]) => LISTmethod

Applies PROC to successive rows from this dataset and collects the results in a list.

The range of processed rows may optionally be restricted using the LIMIT and OFFSET arguments.

(DATASET 'for-each PROC [limit: LIMIT] [offset: OFFSET]) => VOIDmethod

Applies PROC to successive rows from this dataset and discards the results.

The range of processed rows may optionally be restricted using the LIMIT and OFFSET arguments.

(DATASET 'column-ref {NAME | SQL} [THUNK]) => {VALUE | VALUES}method
(DATASET 'column-set! {NAME | SQL} VALUE)method

Access the value of a column in the dataset.

If THUNK is #f, the getter always returns all values of the column in a list. Otherwise the getter limits the query to a single row and returns the value found there or calls THUNK to produce a default value. If THUNK is not explicitly specified, it defaults to a procedure throwing an access exception.

Mind that the setter also affects all rows in the dataset.

Your database backend has probably added accessor methods for columns using add-column-slots!, so you can use them instead.

;; Example:
((items 'filter name: "bag") 'column-set! 'size 42.0)
(print (items 'column-ref 'size #f))

;; Using accessor methods:
((items 'filter name: "bag") 'set-size! 42.0)
(print (items 'size #f))
(DATASET 'select {KEY | SQL PARAMETERS}* [limit: LIMIT] [offset: OFFSET] [distinct: DISTINCT?]) => LISTmethod

Select the columns specified by the given KEYs from the dataset, possibly uniquing the tuples if DISTINCT? is specified and not #f.

The result is a list of lists where each inner list represents a result tuple.

(DATASET 'update! {KEY: VALUE | SQL VALUE}*)method

Update the rows of this dataset, setting the given column KEYs to the given VALUEs.

(DATASET 'insert! {KEY: VALUE | SQL VALUE}*)method

Insert a row with the given column KEY VALUE pairs into this dataset.

(DATASET 'transfer! DATASET (KEY {REFERENCE | SQL . PARAMETERS})*)method

Insert rows from a different dataset into this one. The values for each column KEY are taken from the REFERENCE in the other dataset.

(DATASET 'delete!)method

Deletes the contents of this dataset.

(DATASET 'add-column-slots! GETTER SETTER {NAME | SQL})method

Adds getter and/or setter methods for a specific column to the dataset.

Database drivers will try to setup accessors for the columns of a table automatically.

(DATASET 'add-link-slots! GETTER SETTER ((KEY REFERENCE)*) DATASET)method
(DATASET 'add-link-slots! GETTER SETTER ((KEY REFERENCE)*) AUXILIARY ((KEY REFERENCE)*) DATASET)method

Adds getter and/or setter methods to this dataset for a link to a different dataset.

The first form of this method can be used to model one-to-many and many-to-one relations. The second form using an auxiliary translation dataset can be used to model many to many relations.

;; Example:
(items 'add-link-slots! 'tags 'set-tags! '((id item)) item-tags '((tag id)) tags)
((items 'filter name: "bag") 'set-tags! (tags 'filter name: '("large" "brown")))
(print ((containers 'tags) 'select 'name distinct: #t))
(DATASET 'datastore) => DATASTOREmethod
(DATASET 'set-datastore! DATASTORE)method

The datastore associated with the dataset.

This slot will be set up by methods of the datastore that return datasets. Modifying it directly is not recommended.

(DATASET 'row-prototype) => DATASETmethod
(DATASET 'set-row-prototype! DATASET) => VOIDmethod

The prototype to use for row objects created when iterating over or fetching from the dataset using the first, all, fold or map methods.

The table method of a datastore sets this field to the table itself by default.

(DATASET 'table-clauses) => LISTmethod
(DATASET 'set-table-clauses! LIST)method

The SQL clauses defining the sources of data for this dataset, potentially augmented by parameters.

This slot will be set up by methods of datastores or datasets that return datasets. Modifying it directly is not recommended.

(DATASET 'filter-clauses) => LISTmethod
(DATASET 'set-filter-clauses! LIST)method

The SQL clauses defining filter conditions for this dataset, potentially augmented by parameters.

This slot will be set up by methods of datastores or datasets that return datasets. Modifying it directly is not recommended.

(DATASET 'order-clauses) => LISTmethod
(DATASET 'set-order-clauses! LIST)method

The SQL clauses defining the ordering of rows in this dataset, potentially augmented by parameters.

This slot will be set up by methods of datastores or datasets that return datasets. Modifying it directly is not recommended.

(DATASET 'primary-key-clauses) => LISTmethod
(DATASET 'set-primary-key-clauses! LIST)method

The SQL clauses defining the primary key columns for this dataset.

This slot will be set up by the database engine, if possible.

(DATASET 'table-expression) => STRING, LISTmethod

Renders the table clauses of the dataset into an SQL fragment and a list of parameters binding placeholders.

(DATASET 'filter-expression) => STRING, LISTmethod

Renders the filter clauses of the dataset into an SQL fragment and a list of parameters binding placeholders.

(DATASET 'order-expression) => STRING, LISTmethod

Renders the ordering clauses of the dataset into an SQL fragment and a list of parameters binding placeholders.

SQL manipulation utilities

char-set:sql-identifierconstant

A SRFI-14 character set containing what's valid inside an unescaped SQL identifier.

sql/parentheses SQLprocedure

Adds parentheses around an SQL statement unless they are already present.

Example

Here's a simple demonstration

;;;; demo.scm
;;;; :tabSize=2:indentSize=2:noTabs=true:
;;;; This example creates a database that can be used to store discussion
;;;; threads and shows how to access it using Pandora.
(require-extension extras sql-null pandora)
(require-library pandora-sqlite3)

;; The datastore and its schema
(define store (*sql-datastore* 'connect "sqlite3:demo.db"))

(for-each
  (cut store 'execute <>)
  '(("CREATE TABLE IF NOT EXISTS Authors ("
     "  id INTEGER, name TEXT,"
     "  PRIMARY KEY (id),"
     "  UNIQUE (name)"
     ")")
    ("CREATE TABLE IF NOT EXISTS Tags ("
     "  id INTEGER, name TEXT,"
     "  PRIMARY KEY (id),"
     "  UNIQUE (name)"
     ")")
    ("CREATE TABLE IF NOT EXISTS Posts ("
     "  id INTEGER, author INTEGER, in_reply_to INTEGER, content TEXT,"
     "  PRIMARY KEY (id),"
     "  FOREIGN KEY (author) REFERENCES Authors (id),"
     "  FOREIGN KEY (in_reply_to) REFERENCES Posts (id)"
     ")")
    ("CREATE TABLE IF NOT EXISTS PostTags ("
     "  post INTEGER, tag INTEGER,"
     "  PRIMARY KEY (post, tag),"
     "  FOREIGN KEY (post) REFERENCES Posts (id),"
     "  FOREIGN KEY (tag) REFERENCES Tags (id)"
     ")")))

;; Datasets representing the tables
(define-values (authors posts tags post-tags)
  (apply values
    (map
      (cut store 'table <>)
      '(authors posts tags post-tags))))

;; Establish link relations between the tables
(authors 'add-link-slots! 'posts*
  '((id author)) posts)
(posts 'add-link-slots! 'author* 'set-author*!
  '((author id)) authors)

(tags 'add-link-slots! 'posts*
  '((id tag)) post-tags '((post id)) posts)
(posts 'add-link-slots! 'tags* 'set-tags*!
  '((id post)) post-tags '((tag id)) tags)

(posts 'add-link-slots! 'in-reply-to* 'set-in-reply-to*!
  '((in-reply-to id)) posts)
(posts 'add-link-slots! 'replies*
  '((id in-reply-to)) posts)

;; Add a few authors
(define-values (alice bob eve)
  (apply values
    (map
      (lambda (name)
        (authors 'insert! name: name)
        (authors 'filter name: name))
      '("Alice" "Bob" "Eve"))))

;; Add some useless tags
(for-each
  (cut tags 'insert! name: <>)
  '("red" "green" "blue"))

;; Start a discussion
(define a-post
  (begin
    (posts 'insert! author: (alice 'id) content: "Hello everybody!")
    ((posts 'filter author: (alice 'id)) 'first)))

;; Verify that the first post is not a reply to anything
(pretty-print (sql-null? (a-post 'in-reply-to)))
(pretty-print ((a-post 'in-reply-to*) 'count))

;; Reply
(for-each
  (lambda (author content)
    (posts 'insert! author: (author 'id) in-reply-to: (a-post 'id) content: content))
  (list bob eve)
  '("Hi, nice to hear from you." "Hi there."))

;; Tag all replies both "red" and "blue"
((a-post 'replies*) 'set-tags*! (tags 'filter name: '("red" "blue")))

;; List all posts tagged "blue"
(pretty-print (((tags 'filter name: "blue") 'posts*) 'content #f))

;; List all posts by Eve
(pretty-print ((eve 'posts*) 'content #f))

;; List the tags of all posts by Eve
(pretty-print (((eve 'posts*) 'tags*) 'name #f))

Changelog

License

While this egg falls under a BSD license, please be aware that a dependency of this egg, prometheus, falls under the GPL license.

 Copyright (c) 2009, 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.

Contents »