You are looking at historical revision 33557 of this page. It may differ significantly from its current revision.
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
[procedure] (open dbtype dbinit)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
[procedure] (db-dbtype db)Returns the symbol of db's backend driver, as it was supplied to the open call which returned the db object.
db-conn
[procedure] (db-conn db)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
[procedure] (close db)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
[procedure] (exec db query . params)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
[procedure] (for-each-row proc db query . params)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")))) (row-for-each mydb (lambda (tuple) (print (vector-ref tuple 0) " -- " (vector-ref tuple 1))) "SELECT name, year FROM films WHERE name = ? OR name = ?" "The Godfather" "Alien") ;; This will print something like: ;; The Godfather -- 1972 ;; Alien -- 1979
get-rows
[procedure] (get-rows db query . params)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
[procedure] (get-one-row db query . params)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
[procedure] (get-one db query . params)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
Changelog
- 0.1: Initial release