You can edit this page using wiki syntax for markup.

Article contents:== Outdated egg! This is an egg for CHICKEN 4, the unsupported old release. You're almost certainly looking for [[/eggref/5/dbi|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 [[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. [[tags: egg]] == dbi [[toc:]] === 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 <procedure>(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 <procedure>(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 <procedure>(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 <procedure>(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: * 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)</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: <enscript highlight="scheme"> (define mydb (open 'sqlite3 '((dbname . "/tmp/db")))) (exec mydb "INSERT INTO films (name, year) VALUES (?, ?)" "The Godfather" 1972) </enscript> ===== for-each-row <procedure>(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: <enscript highlight="scheme"> (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 </enscript> ===== get-rows <procedure>(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: <enscript highlight="scheme"> (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)) </enscript> ==== get-one-row <procedure>(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: <enscript highlight="scheme"> (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 </enscript> ==== get-one <procedure>(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: <enscript highlight="scheme"> (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 </enscript> ==== Support procedures ===== now <procedure>(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 [[http://www.kiatoa.com/cgi-bin/fossils/dbi|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. Description of your changes:

I would like to authenticate

Spam control

What do you get when you subtract 1 from 11?