chickadee » dbi

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:

Documentation

Connection management procedures

open
(open dbtype dbinit) procedure

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 db) procedure

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 db) procedure

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 db) procedure

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:

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

exec
(exec db query . params) procedure

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 . params) procedure

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 . params) procedure

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 . params) procedure

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 . params) procedure

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 db) procedure

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.

Contents »