((section 2 "Outdated egg!" (p "This is an egg for CHICKEN 4, the unsupported old release.  You're almost certainly looking for " (int-link "/eggref/5/pandora" "the CHICKEN 5 version of this egg") ", if it exists.") (p "If it does not exist, there may be equivalent functionality provided by another egg; have a look at the " (link "https://wiki.call-cc.org/chicken-projects/egg-index-5.html" "egg index") ". Otherwise, please consider porting this egg to the current version of CHICKEN.")) (section 2 "Pandora" (section 3 "Description" (p "An object-relation mapper using the prometheus object system.")) (section 3 "Author" (p (link "http://www.chust.org/" "Thomas Chust"))) (section 3 "Requires" (ul (li (int-link "check-errors")) (li (int-link "matchable")) (li (int-link "prometheus")) (li (int-link "uri-generic")) (li (int-link "lru-cache")) (li (int-link "sql-null"))) (p "And to use it, one of the supported database backend eggs.")) (section 3 "Documentation" (p "Pandora provides utilities to access SQL datastores by manipulating object graphs. No SQL statements have to be written by hand.") (p "If SQL source is passed to Pandora directly, it is in the form of fragment lists, that is lists containing strings or the symbol " (tt "?") " where a statement parameter placeholder should be inserted by the database driver.") (p "Pandora comes with backends for SQLite3 and PostgreSQL databases. If you want to use these backends, you also have to install the " (int-link "sqlite3") " and/or " (int-link "postgresql") " eggs respectively. Additional backends can be added easily.") (p "Pandora and its bundled backends all support static linking.") (section 4 "Prototypical objects" (p "(TODO: these are objects, not constants. But wiki syntax currently has no " (tt "<object>") " tag)") (def (sig (constant "*sql-datastore*" (id *sql-datastore*))) (p "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 " (tt "connect") " method.") (p "Database drivers will usually want to inherit from this object, override the methods to fetch data and register their child using " (tt "add-connection-prototype!"))) (def (sig (constant "*sql-dataset*" (id *sql-dataset*))) (p "A representation of a set of rows in an SQL database. Methods of datastores return children of this object for further manipulation.") (p "A dataset that is sufficiently restricted to contain only a single row can also be seen as a reference to that row.")))) (section 3 "Working with datastores" (def (sig (method "(DATASTORE 'connect URI) => DATASTORE" (id DATASTORE))) (p "Establish a connection to a real database.") (p "Depending on the scheme of the " (tt "URI") ", a registered prototype to clone is selected and the " (tt "URI") " is passed on to the clone call.  The new object is returned.") (p (tt "URI") " may be a string or a " (int-link "uri-generic") " record, but is always passed to the connection prototype's " (tt "clone") " method as a record.") (p "The drivers bundled with pandora will handle the URI schemes " (tt "sqlite3") " or " (tt "memory") " for access to SQLite3 databases (the latter always creates a database in memory) and " (tt "postgresql") " or " (tt "psql") " for access to PostgreSQL databases.") (p "The SQLite3 driver also supports a URI parameter " (tt "cache") " that determines the size of the prepared statement cache, which defaults to 64.") (p "Before using a backend, it must be loaded, but it doesn't have to be imported.") (highlight scheme ";; Example using SQLite3:\n(require-extension pandora)\n(require-library pandora-sqlite3)\n(define store (*sql-datastore* 'connect \"sqlite3:test.db?cache=16\"))\n\n;; Example using PostgreSQL:\n(require-extension pandora)\n(require-library pandora-postgresql)\n(define store (*sql-datastore* 'connect \"psql:\"))") (p "<method>(DATASTORE 'disconnect!) => VOID</enscript>") (p "Close the connection this datastore holds to a real database. The default implementation does nothing.")) (def (sig (method "(DATASTORE 'table NAME) => DATASET" (id DATASTORE))) (p "Create a dataset backed by the table called " (tt "NAME") " in the datastore. Tables may be named by symbols or strings.") (p "Actual database implementations should try to obtain meta information about the table and setup column accessors. See " (tt "add-column-slots!") " for information how to do that conveniently.") (highlight scheme ";; Example:\n(define-values (items tags item-tags)\n(values\n(store 'table 'items)\n(store 'table 'tags)\n(store 'table 'item-tags)))")) (def (sig (method "(DATASTORE 'execute SQL [PARAMETERS]) => VOID" (id DATASTORE))) (p "Executes the given " (tt "SQL") " statement and discards all data possibly returned by it.") (p "The method is responsible to transform the SQL fragment list passed to it into a suitable form for processing by the database.") (p "The default implementation does nothing.") (p "You should never have to call this method directly.")) (def (sig (method "(DATASTORE 'fold PROC INIT SQL [PARAMETERS]) => VOID" (id DATASTORE))) (p "Fetches data returned by the given " (tt "SQL") " statement from from the database and applies " (tt "PROC") " to " (tt "INIT") " or its last return value and to all columns of each row.") (p "The method is responsible to transform the SQL fragment list passed to it into a suitable form for processing by the database.") (p "The default implementation just returns " (tt "INIT") ".") (p "You should never have to call this method directly.")) (def (sig (method "(DATASTORE 'with-transaction THUNK) => OBJECT" (id DATASTORE))) (p "Executes " (tt "THUNK") " within a transaction of the datastore. The transaction is rolled back if the " (tt "THUNK") " returns " (tt "#f") " or raises an error, otherwise the transaction is committed.") (p "Returns the result of the callback.")) (def (sig (method "(DATASTORE 'escape-sql-identifier ID) => STRING" (id DATASTORE))) (p "Escapes an SQL identifier in a driver specific way, if necessary.") (p "The default implementation just returns the " (tt "ID") " unchanged or raises an error if it contains characters not valid in a standard SQL identifier.") (p "Usually it should be unnecessary to call this method directly.")) (def (sig (method "(DATASTORE 'name->table-clause NAME) => STRING" (id DATASTORE))) (p "Turns a name into an SQL table clause, using driver specific escaping.") (p "The default implementation converts hyphenated identifiers into camel case and delegates to " (tt "escape-sql-identifier") " for the escaping.") (p "Usually it should be unnecessary to call this method directly.")) (def (sig (method "(DATASTORE 'name->column-clause NAME) => STRING" (id DATASTORE))) (p "Turns a name into an SQL column clause, using driver specific escaping.") (p "The default implementation converts hyphenated identifiers into lower case, underscore separated identifiers and delegates to " (tt "escape-sql-identifier") " for the escaping.") (p "Usually it should be unnecessary to call this method directly.")) (def (sig (method "(DATASTORE 'add-connection-prototype! URI-SCHEME OBJECT) => VOID" (id DATASTORE))) (p "Add a prototype for connections to databases using the given " (tt "URI-SCHEME") ".") (p "Only database drivers should have to call this method.")) (def (sig (method "(DATASTORE 'delete-connection-prototype! URI-SCHEME) => VOID" (id DATASTORE))) (p "Remove the prototype for connections to databases using the given " (tt "URI-SCHEME") ".") (p "This method just exists for completeness and is probably rarely useful."))) (section 3 "Working with datasets" (def (sig (method "(DATASET 'filter {KEY: VALUE | KEY: VALUES | SQL PARAMETERS}*) => DATASET" (id DATASET))) (p "Obtain a dataset that is filtered selecting records with fields set to specific values or with arbitrary boolean SQL expressions.") (highlight scheme ";; Example:\n(define containers (items 'filter name: '(\"bag\" \"box\" \"trunk\")))")) (def (sig (method "(DATASET 'match DATASET (KEY {REFERENCE | SQL . PARAMETERS})*) => DATASET" (id #f))) (p "Obtain a dataset that contains only rows where the " (tt "KEY") "s in this dataset can be found as " (tt "REFERENCE") "s in the given auxiliary dataset.") (p "This method can be used to model complex relations between tables, but you may also want to look at " (tt "add-link-slots!") " for a more convenient interface to specify common types of inter-table relations.") (highlight scheme ";; Example:\n(define container-tags (tags 'match (item-tags 'match containers '(item id)) '(tag id)))")) (def (sig (method "(DATASET 'order {KEY: {'asc | 'ascending | '< | 'desc | 'descending | '>} | SQL PARAMETERS}*) => DATASET" (id #f))) (p "Obtain a dataset that is sorted by the values of fields or as specified by arbitrary SQL expressions.")) (def (sig (method "(DATASET 'count) => CARDINAL-INTEGER" (id DATASET))) (p "Count the number of rows in the dataset.")) (def (sig (method "(DATASET 'first [THUNK]) => DATASET" (id DATASET))) (p "Returns a dataset containing only the first row of this one or the result of " (tt "THUNK") " if this dataset is empty. The default thunk raises an access exception.")) (def (sig (method "(DATASET 'all [limit: LIMIT] [offset: OFFSET]) => LIST" (id DATASET))) (p "Returns a list of datasets each containing one row from this dataset.") (p "The range of returned rows may optionally be restricted using the " (tt "LIMIT") " and " (tt "OFFSET") " arguments.")) (def (sig (method "(DATASET 'fold  PROC INIT [limit: LIMIT] [offset: OFFSET]) => OBJECT" (id DATASET))) (p "Applies " (tt "PROC") " to successive rows from this dataset and either " (tt "INIT") " or its last return value. Returns either " (tt "INIT") " (if the dataset is empty) or the result of the last application of " (tt "PROC") ".") (p "The range of processed rows may optionally be restricted using the " (tt "LIMIT") " and " (tt "OFFSET") " arguments.")) (def (sig (method "(DATASET 'map PROC [limit: LIMIT] [offset: OFFSET]) => LIST" (id DATASET))) (p "Applies " (tt "PROC") " to successive rows from this dataset and collects the results in a list.") (p "The range of processed rows may optionally be restricted using the " (tt "LIMIT") " and " (tt "OFFSET") " arguments.")) (def (sig (method "(DATASET 'for-each PROC [limit: LIMIT] [offset: OFFSET]) => VOID" (id DATASET))) (p "Applies " (tt "PROC") " to successive rows from this dataset and discards the results.") (p "The range of processed rows may optionally be restricted using the " (tt "LIMIT") " and " (tt "OFFSET") " arguments.")) (def (sig (method "(DATASET 'column-ref {NAME | SQL} [THUNK]) => {VALUE | VALUES}" (id #f)) (method "(DATASET 'column-set! {NAME | SQL} VALUE)" (id #f))) (p "Access the value of a column in the dataset.") (p "If " (tt "THUNK") " is " (tt "#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 " (tt "THUNK") " to produce a default value. If " (tt "THUNK") " is not explicitly specified, it defaults to a procedure throwing an access exception.") (p "Mind that the setter also affects all rows in the dataset.") (p "Your database backend has probably added accessor methods for columns using " (tt "add-column-slots!") ", so you can use them instead.") (highlight scheme ";; Example:\n((items 'filter name: \"bag\") 'column-set! 'size 42.0)\n(print (items 'column-ref 'size #f))\n\n;; Using accessor methods:\n((items 'filter name: \"bag\") 'set-size! 42.0)\n(print (items 'size #f))")) (def (sig (method "(DATASET 'select {KEY | SQL PARAMETERS}* [limit: LIMIT] [offset: OFFSET] [distinct: DISTINCT?]) => LIST" (id #f))) (p "Select the columns specified by the given " (tt "KEY") "s from the dataset, possibly uniquing the tuples if " (tt "DISTINCT?") " is specified and not " (tt "#f") ".") (p "The result is a list of lists where each inner list represents a result tuple.")) (def (sig (method "(DATASET 'update! {KEY: VALUE | SQL VALUE}*)" (id #f))) (p "Update the rows of this dataset, setting the given column " (tt "KEY") "s to the given " (tt "VALUE") "s.")) (def (sig (method "(DATASET 'insert! {KEY: VALUE | SQL VALUE}*)" (id #f))) (p "Insert a row with the given column " (tt "KEY") " " (tt "VALUE") " pairs into this dataset.")) (def (sig (method "(DATASET 'transfer! DATASET (KEY {REFERENCE | SQL . PARAMETERS})*)" (id #f))) (p "Insert rows from a different dataset into this one. The values for each column " (tt "KEY") " are taken from the " (tt "REFERENCE") " in the other dataset.")) (def (sig (method "(DATASET 'delete!)" (id DATASET))) (p "Deletes the contents of this dataset.")) (def (sig (method "(DATASET 'add-column-slots! GETTER SETTER {NAME | SQL})" (id #f))) (p "Adds getter and/or setter methods for a specific column to the dataset.") (p "Database drivers will try to setup accessors for the columns of a table automatically.")) (def (sig (method "(DATASET 'add-link-slots! GETTER SETTER ((KEY REFERENCE)*) DATASET)" (id DATASET)) (method "(DATASET 'add-link-slots! GETTER SETTER ((KEY REFERENCE)*) AUXILIARY ((KEY REFERENCE)*) DATASET)" (id DATASET))) (p "Adds getter and/or setter methods to this dataset for a link to a different dataset.") (p "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.") (highlight scheme ";; Example:\n(items 'add-link-slots! 'tags 'set-tags! '((id item)) item-tags '((tag id)) tags)\n((items 'filter name: \"bag\") 'set-tags! (tags 'filter name: '(\"large\" \"brown\")))\n(print ((containers 'tags) 'select 'name distinct: #t))")) (def (sig (method "(DATASET 'datastore) => DATASTORE" (id DATASET)) (method "(DATASET 'set-datastore! DATASTORE)" (id DATASET))) (p "The datastore associated with the dataset.") (p "This slot will be set up by methods of the datastore that return datasets. Modifying it directly is not recommended.")) (def (sig (method "(DATASET 'row-prototype) => DATASET" (id DATASET)) (method "(DATASET 'set-row-prototype! DATASET) => VOID" (id DATASET))) (p "The prototype to use for row objects created when iterating over or fetching from the dataset using the " (tt "first") ", " (tt "all") ", " (tt "fold") " or " (tt "map") " methods.") (p "The " (tt "table") " method of a datastore sets this field to the table itself by default.")) (def (sig (method "(DATASET 'table-clauses) => LIST" (id DATASET)) (method "(DATASET 'set-table-clauses! LIST)" (id DATASET))) (p "The SQL clauses defining the sources of data for this dataset, potentially augmented by parameters.") (p "This slot will be set up by methods of datastores or datasets that return datasets. Modifying it directly is not recommended.")) (def (sig (method "(DATASET 'filter-clauses) => LIST" (id DATASET)) (method "(DATASET 'set-filter-clauses! LIST)" (id DATASET))) (p "The SQL clauses defining filter conditions for this dataset, potentially augmented by parameters.") (p "This slot will be set up by methods of datastores or datasets that return datasets. Modifying it directly is not recommended.")) (def (sig (method "(DATASET 'order-clauses) => LIST" (id DATASET)) (method "(DATASET 'set-order-clauses! LIST)" (id DATASET))) (p "The SQL clauses defining the ordering of rows in this dataset, potentially augmented by parameters.") (p "This slot will be set up by methods of datastores or datasets that return datasets. Modifying it directly is not recommended.")) (def (sig (method "(DATASET 'primary-key-clauses) => LIST" (id DATASET)) (method "(DATASET 'set-primary-key-clauses! LIST)" (id DATASET))) (p "The SQL clauses defining the primary key columns for this dataset.") (p "This slot will be set up by the database engine, if possible.")) (def (sig (method "(DATASET 'table-expression) => STRING, LIST" (id DATASET))) (p "Renders the table clauses of the dataset into an SQL fragment and a list of parameters binding placeholders.")) (def (sig (method "(DATASET 'filter-expression) => STRING, LIST" (id DATASET))) (p "Renders the filter clauses of the dataset into an SQL fragment and a list of parameters binding placeholders.")) (def (sig (method "(DATASET 'order-expression) => STRING, LIST" (id DATASET))) (p "Renders the ordering clauses of the dataset into an SQL fragment and a list of parameters binding placeholders."))) (section 3 "SQL manipulation utilities" (def (sig (constant "char-set:sql-identifier" (id char-set:sql-identifier))) (p "A SRFI-14 character set containing what's valid inside an unescaped SQL identifier.")) (def (sig (procedure "(sql/parentheses SQL) => SQL" (id sql/parentheses))) (p "Adds parentheses around an SQL statement unless they are already present."))) (section 3 "Example" (p "Here's a simple demonstration") (highlight scheme ";;;; demo.scm\n;;;; :tabSize=2:indentSize=2:noTabs=true:\n;;;; This example creates a database that can be used to store discussion\n;;;; threads and shows how to access it using Pandora.\n(require-extension extras sql-null pandora)\n(require-library pandora-sqlite3)\n\n;; The datastore and its schema\n(define store (*sql-datastore* 'connect \"sqlite3:demo.db\"))\n\n(for-each\n  (cut store 'execute <>)\n  '((\"CREATE TABLE IF NOT EXISTS Authors (\"\n     \"  id INTEGER, name TEXT,\"\n     \"  PRIMARY KEY (id),\"\n     \"  UNIQUE (name)\"\n     \")\")\n    (\"CREATE TABLE IF NOT EXISTS Tags (\"\n     \"  id INTEGER, name TEXT,\"\n     \"  PRIMARY KEY (id),\"\n     \"  UNIQUE (name)\"\n     \")\")\n    (\"CREATE TABLE IF NOT EXISTS Posts (\"\n     \"  id INTEGER, author INTEGER, in_reply_to INTEGER, content TEXT,\"\n     \"  PRIMARY KEY (id),\"\n     \"  FOREIGN KEY (author) REFERENCES Authors (id),\"\n     \"  FOREIGN KEY (in_reply_to) REFERENCES Posts (id)\"\n     \")\")\n    (\"CREATE TABLE IF NOT EXISTS PostTags (\"\n     \"  post INTEGER, tag INTEGER,\"\n     \"  PRIMARY KEY (post, tag),\"\n     \"  FOREIGN KEY (post) REFERENCES Posts (id),\"\n     \"  FOREIGN KEY (tag) REFERENCES Tags (id)\"\n     \")\")))\n\n;; Datasets representing the tables\n(define-values (authors posts tags post-tags)\n  (apply values\n    (map\n      (cut store 'table <>)\n      '(authors posts tags post-tags))))\n\n;; Establish link relations between the tables\n(authors 'add-link-slots! 'posts*\n  '((id author)) posts)\n(posts 'add-link-slots! 'author* 'set-author*!\n  '((author id)) authors)\n\n(tags 'add-link-slots! 'posts*\n  '((id tag)) post-tags '((post id)) posts)\n(posts 'add-link-slots! 'tags* 'set-tags*!\n  '((id post)) post-tags '((tag id)) tags)\n\n(posts 'add-link-slots! 'in-reply-to* 'set-in-reply-to*!\n  '((in-reply-to id)) posts)\n(posts 'add-link-slots! 'replies*\n  '((id in-reply-to)) posts)\n\n;; Add a few authors\n(define-values (alice bob eve)\n  (apply values\n    (map\n      (lambda (name)\n        (authors 'insert! name: name)\n        (authors 'filter name: name))\n      '(\"Alice\" \"Bob\" \"Eve\"))))\n\n;; Add some useless tags\n(for-each\n  (cut tags 'insert! name: <>)\n  '(\"red\" \"green\" \"blue\"))\n\n;; Start a discussion\n(define a-post\n  (begin\n    (posts 'insert! author: (alice 'id) content: \"Hello everybody!\")\n    ((posts 'filter author: (alice 'id)) 'first)))\n\n;; Verify that the first post is not a reply to anything\n(pretty-print (sql-null? (a-post 'in-reply-to)))\n(pretty-print ((a-post 'in-reply-to*) 'count))\n\n;; Reply\n(for-each\n  (lambda (author content)\n    (posts 'insert! author: (author 'id) in-reply-to: (a-post 'id) content: content))\n  (list bob eve)\n  '(\"Hi, nice to hear from you.\" \"Hi there.\"))\n\n;; Tag all replies both \"red\" and \"blue\"\n((a-post 'replies*) 'set-tags*! (tags 'filter name: '(\"red\" \"blue\")))\n\n;; List all posts tagged \"blue\"\n(pretty-print (((tags 'filter name: \"blue\") 'posts*) 'content #f))\n\n;; List all posts by Eve\n(pretty-print ((eve 'posts*) 'content #f))\n\n;; List the tags of all posts by Eve\n(pretty-print (((eve 'posts*) 'tags*) 'name #f))")) (section 3 "Changelog " (ul (li "1.0.0 Initial release"))) (section 3 "License" (p "While this egg falls under a BSD license, please be aware that a dependency of this egg, " (int-link "prometheus") ", falls under the GPL license.") (pre " Copyright (c) 2009, Thomas Chust <chust@web.de>.  All rights reserved.\n \n Redistribution and use in source and binary forms, with or without\n modification, are permitted provided that the following conditions are met:\n \n Redistributions of source code must retain the above copyright notice,\n this list of conditions and the following disclaimer. Redistributions in\n binary form must reproduce the above copyright notice, this list of\n conditions and the following disclaimer in the documentation and/or\n other materials provided with the distribution. Neither the name of the\n author nor the names of its contributors may be used to endorse or\n promote products derived from this software without specific prior\n written permission.\n \n THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS \"AS\n IS\" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,\n THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR\n PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR\n CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,\n EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,\n PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR\n PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF\n LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING\n NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS\n SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE."))))