chickadee » dbi

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.

dbi

Description

A database abstraction layer to provide a common interface across multiple databases.

Author

Matthew Welland

Requirements

Requires the autoload extension, to provide "soft" dependencies on actual database egg implementations. Support for the following database eggs is available:

• sqlite3
• sql-de-lite
• postgresql
• mysql-client

Documentation

Connection management procedures

open

open dbtype dbinitprocedure

Opens a connection to the database of type dbtype with connection information in the dbinit alist. An opaque db handle is returned.

The following symbols are accepted as dbtype:

• sqlite3
• pg
• mysql

Depending on the backend, the dbinit alist supports the following keys:

• dbname: The database name (pg), schema name (mysql) or filename (sqlite3).
• host: The host to connect to (pg, mysql).
• user: The user to connect as (pg, mysql).
• password: The user's password (pg, mysql).

If any are omitted, the database driver's defaults are used. In the case of MySQL, this means .my.cnf is consulted, and in the case of Postgres, .pgpass and various PG environment variables are consulted. Check the manual of your database system for more info.

db-dbtype

db-dbtype dbprocedure

Returns the symbol of db's backend driver, as it was supplied to the open call which returned the db object.

db-conn

db-conn dbprocedure

Returns the underlying backend-specific raw connection object of db's backend driver, as created by the open call which returned the db object.

This can be used whenever some database-specific feature is needed for which this egg does not (yet) provide an abstraction.

close

close dbprocedure

Close the connection to db.

NOTE: In the case of MySQL, this is a no-op, because the underlying driver egg doesn't support closing connections explicitly. It does register a finalizer, so you should be able to force it by losing all references to the db object and forcing a garbage collection.

Querying procedures

These procedures perform queries and immediately operate on the result set. There is no way to directly retrieve a result set object, so if you need to refer to the result set later, you'll need to store the tuples in an object yourself.

These procedures all accept query parameters in a generic syntax. Each placeholder "?" is replaced in query strings by their escaped parameter values, regardless of the underlying database egg (so in case of Postgres this means $1 etc are not supported).

There's some support for mapping Scheme objects to SQL values in queries:

• Lists are comma-separated as x, y, z, so you can use a list with one placeholder in an IN or VALUES statement.
• Strings are kept as-is (but, of course, quoted and escaped to protect against injection).
• Symbols are converted to strings, so they can be used interchangeably.
• Numbers of any type will be converted to a string in Scheme and then put into the query (unquoted). In other words, they're basically used as-is.
• Booleans will be converted to TRUE or FALSE on input.
• Vectors are assumed to be dates, and converted to a timestamp string (CURRENTLY BROKEN).

For conversion of SQL values in result sets to Scheme objects, dbi defers to whatever the relevant driver egg does.

exec

exec db query #!rest paramsprocedure

Execute the query for its side-effects on the database connection db. params should be rest arguments which replace the corresponding "?" placeholders in query.

Example:

(define mydb (open 'sqlite3 '((dbname . "/tmp/db"))))
(exec mydb
      "INSERT INTO films (name, year) VALUES (?, ?)"
      "The Godfather" 1972)

for-each-row

for-each-row proc db query #!rest paramsprocedure

Execute the query on the database connection db and invoke the procedure proc for every row. params should be rest arguments which replace the corresponding "?" placeholders in query.

The procedure should accept one argument, which will be a vector containing the tuple's fields.

Example:

(define mydb (open 'sqlite3 '((dbname . "/tmp/db"))))
(for-each-row (lambda (tuple)
	        (print (vector-ref tuple 0) " -- " (vector-ref tuple 1)))
              mydb
              "SELECT name, year FROM films WHERE name = ? OR name = ?"
              "The Godfather" "Alien")

;; This will print something like:
;; The Godfather -- 1972
;; Alien -- 1979

get-rows

get-rows db query #!rest paramsprocedure

Execute the query on the database connection db and return the entire set, represented as a list of tuple vectors. The params should be rest arguments which replace the corresponding "?" placeholders in query.

Example:

(define mydb (open 'sqlite3 '((dbname . "/tmp/db"))))
(let ((tuples (get-rows
                mydb
                "SELECT name, year FROM films WHERE name = ? OR name = ?"
                "The Godfather" "Alien")))
  (pp tuples))
;; This will print something like:
;; (#("The Godfather" 1972)
;;   ("Alien" 1979))

get-one-row

get-one-row db query #!rest paramsprocedure

Execute the query on the database connection db and return the first row in the set. The params should be rest arguments which replace the corresponding "?" placeholders in query.

The returned row is represented by a vector with the row's fields or #f if the query returns an empty set.

NOTE: This will still retrieve the entire result set, despite only returning the one row. So it's still up to you to add LIMIT 1 or FETCH FIRST ROW ONLY to your query!

Example:

(define mydb (open 'sqlite3 '((dbname . "/tmp/db"))))
(let ((tuple (get-one-row
               mydb
               "SELECT name, year FROM films WHERE name = ?"
               "The Godfather")))
  (print (vector-ref tuple 0) " -- " (vector-ref tuple 1)))
;; This will print something like:
;; The Godfather -- 1972

get-one

get-one db query #!rest paramsprocedure

Like get-one-row, except it returns only the first field of the first row in the set (or #f if the set is empty).

NOTE: This will still retrieve the entire result set, despite only returning the one row. So it's still up to you to add LIMIT 1 or FETCH FIRST ROW ONLY to your query!

Example:

(define mydb (open 'sqlite3 '((dbname . "/tmp/db"))))
(let ((tuple (get-one-row
               mydb
               "SELECT name, year FROM films WHERE name = ?"
               "The Godfather")))
  (print (vector-ref tuple 0) " -- " (vector-ref tuple 1)))
;; This will print something like:
;; The Godfather -- 1972

Support procedures

now

now dbprocedure

Returns a string representing the current date/time, using the syntax required by the database to which db is a connection.

Repository

You can find the source code to this egg in the dbi fossil repository.

License

 Copyright (C) 2007-2016 Matt Welland
 Copyright (C) 2016 Peter Bex
 Redistribution and use in source and binary forms, with or without
 modification, is permitted.

 THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``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 AUTHOR 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.