source: project/wiki/eggref/4/dbi @ 33760

Last change on this file since 33760 was 33760, checked in by svnwiki, 3 years ago

Anonymous wiki edit for IP [192.55.55.41]: Corrected parameter ordering in for-each-row example.

File size: 8.2 KB
Line 
1[[tags: egg]]
2
3== dbi
4
5[[toc:]]
6
7=== Description
8
9A database abstraction layer to provide a common interface across
10multiple databases.
11
12=== Author
13
14Matthew Welland
15
16=== Requirements
17
18Requires the [[autoload]] extension, to provide "soft" dependencies on
19actual database egg implementations.  Support for the following
20database eggs is available:
21
22* [[sqlite3]]
23* [[postgresql]]
24* [[mysql-client]]
25
26=== Documentation
27
28==== Connection management procedures
29
30===== open
31
32<procedure>(open dbtype dbinit)</procedure>
33
34Opens a connection to the database of type {{dbtype}} with connection
35information in the {{dbinit}} alist.  An opaque {{db}} handle is
36returned.
37
38The following symbols are accepted as {{dbtype}}:
39* {{sqlite3}}
40* {{pg}}
41* {{mysql}}
42
43Depending on the backend, the {{dbinit}} alist supports the
44following keys:
45* {{dbname}}: The database name (pg), schema name (mysql) or filename (sqlite3).
46* {{host}}: The host to connect to (pg, mysql).
47* {{user}}: The user to connect as (pg, mysql).
48* {{password}}: The {{user}}'s password (pg, mysql).
49
50If any are omitted, the database driver's defaults are used.  In the
51case of MySQL, this means {{.my.cnf}} is consulted, and in the case of
52Postgres, {{.pgpass}} and various {{PG}} environment variables are
53consulted.  Check the manual of your database system for more info.
54
55===== db-dbtype
56
57<procedure>(db-dbtype db)</procedure>
58
59Returns the symbol of {{db}}'s backend driver, as it was supplied to
60the {{open}} call which returned the {{db}} object.
61
62===== db-conn
63
64<procedure>(db-conn db)</procedure>
65
66Returns the underlying backend-specific raw connection object of
67{{db}}'s backend driver, as created by the {{open}} call which
68returned the {{db}} object.
69
70This can be used whenever some database-specific feature is needed for
71which this egg does not (yet) provide an abstraction.
72
73===== close
74
75<procedure>(close db)</procedure>
76
77Close the connection to {{db}}.
78
79NOTE: In the case of MySQL, this is a no-op, because the underlying
80driver egg doesn't support closing connections explicitly.  It does
81register a finalizer, so you should be able to force it by losing all
82references to the {{db}} object and forcing a garbage collection.
83
84
85==== Querying procedures
86
87These procedures perform queries ''and'' immediately operate on the
88result set.  There is no way to directly retrieve a result set object,
89so if you need to refer to the result set later, you'll need to store
90the tuples in an object yourself.
91
92These procedures all accept query parameters in a generic syntax.
93Each placeholder "{{?}}" is replaced in query strings by their escaped
94parameter values, regardless of the underlying database egg (so in
95case of Postgres this means {{$1}} etc are '''not''' supported).
96
97There's some support for mapping Scheme objects to SQL values in queries:
98* Lists are comma-separated as {{x, y, z}}, so you can use a list with one placeholder in an {{IN}} or {{VALUES}} statement.
99* Strings are kept as-is (but, of course, quoted and escaped to protect against injection).
100* Symbols are converted to strings, so they can be used interchangeably.
101* 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.
102* Booleans will be converted to {{TRUE}} or {{FALSE}} on input.
103* Vectors are assumed to be dates, and converted to a timestamp string (CURRENTLY BROKEN).
104
105For conversion of SQL values in result sets to Scheme objects, dbi
106defers to whatever the relevant driver egg does.
107
108===== exec
109
110<procedure>(exec db query . params)</procedure>
111
112Execute the {{query}} for its side-effects on the database connection
113{{db}}.  {{params}} should be rest arguments which replace the
114corresponding "{{?}}" placeholders in {{query}}.
115
116Example:
117
118<enscript highlight="scheme">
119(define mydb (open 'sqlite3 '((dbname . "/tmp/db"))))
120(exec mydb
121      "INSERT INTO films (name, year) VALUES (?, ?)"
122      "The Godfather" 1972)
123</enscript>
124
125===== for-each-row
126
127<procedure>(for-each-row proc db query . params)</procedure>
128
129Execute the {{query}} on the database connection {{db}} and invoke the
130procedure {{proc}} for every row.  {{params}} should be rest arguments
131which replace the corresponding "{{?}}"  placeholders in {{query}}.
132
133The procedure should accept one argument, which will be a vector
134containing the tuple's fields.
135
136Example:
137
138<enscript highlight="scheme">
139(define mydb (open 'sqlite3 '((dbname . "/tmp/db"))))
140(for-each-row (lambda (tuple)
141                (print (vector-ref tuple 0) " -- " (vector-ref tuple 1)))
142              mydb
143              "SELECT name, year FROM films WHERE name = ? OR name = ?"
144              "The Godfather" "Alien")
145
146;; This will print something like:
147;; The Godfather -- 1972
148;; Alien -- 1979
149</enscript>
150
151===== get-rows
152
153<procedure>(get-rows db query . params)</procedure>
154
155Execute the {{query}} on the database connection {{db}} and return the
156entire set, represented as a list of tuple vectors.  The {{params}}
157should be rest arguments which replace the corresponding "{{?}}"
158placeholders in {{query}}.
159
160Example:
161
162<enscript highlight="scheme">
163(define mydb (open 'sqlite3 '((dbname . "/tmp/db"))))
164(let ((tuples (get-rows
165                mydb
166                "SELECT name, year FROM films WHERE name = ? OR name = ?"
167                "The Godfather" "Alien")))
168  (pp tuples))
169;; This will print something like:
170;; (#("The Godfather" 1972)
171;;   ("Alien" 1979))
172</enscript>
173
174==== get-one-row
175
176<procedure>(get-one-row db query . params)</procedure>
177
178Execute the {{query}} on the database connection {{db}} and return the
179first row in the set.  The {{params}} should be rest arguments which
180replace the corresponding "{{?}}"  placeholders in {{query}}.
181
182The returned row is represented by a vector with the row's fields or
183{{#f}} if the query returns an empty set.
184
185NOTE: This will still retrieve the entire result set, despite only
186returning the one row.  So it's still up to you to add {{LIMIT 1}} or
187{{FETCH FIRST ROW ONLY}} to your query!
188
189Example:
190
191<enscript highlight="scheme">
192(define mydb (open 'sqlite3 '((dbname . "/tmp/db"))))
193(let ((tuple (get-one-row
194               mydb
195               "SELECT name, year FROM films WHERE name = ?"
196               "The Godfather")))
197  (print (vector-ref tuple 0) " -- " (vector-ref tuple 1)))
198;; This will print something like:
199;; The Godfather -- 1972
200</enscript>
201
202==== get-one
203
204<procedure>(get-one db query . params)</procedure>
205
206Like {{get-one-row}}, except it returns only the first ''field'' of
207the first row in the set (or {{#f}} if the set is empty).
208
209NOTE: This will still retrieve the entire result set, despite only
210returning the one row.  So it's still up to you to add {{LIMIT 1}} or
211{{FETCH FIRST ROW ONLY}} to your query!
212
213Example:
214
215<enscript highlight="scheme">
216(define mydb (open 'sqlite3 '((dbname . "/tmp/db"))))
217(let ((tuple (get-one-row
218               mydb
219               "SELECT name, year FROM films WHERE name = ?"
220               "The Godfather")))
221  (print (vector-ref tuple 0) " -- " (vector-ref tuple 1)))
222;; This will print something like:
223;; The Godfather -- 1972
224</enscript>
225
226==== Support procedures
227
228===== now
229
230<procedure>(now db)</procedure>
231
232Returns a string representing the current date/time, using the syntax
233required by the database to which {{db}} is a connection.
234
235=== Repository
236
237You can find the source code to this egg in
238[[http://www.kiatoa.com/cgi-bin/fossils/dbi|the dbi fossil repository]].
239
240=== License
241
242  Copyright (C) 2007-2016 Matt Welland
243  Copyright (C) 2016 Peter Bex
244  Redistribution and use in source and binary forms, with or without
245  modification, is permitted.
246 
247  THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS
248  OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
249  WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
250  ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE
251  LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
252  CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
253  OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
254  BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
255  LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
256  (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
257  USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
258  DAMAGE.
Note: See TracBrowser for help on using the repository browser.