source: project/wiki/eggref/4/sql-de-lite @ 26392

Last change on this file since 26392 was 26392, checked in by Jim Ursetto, 8 years ago

wiki/sql-de-lite: Clean up egg description for a new, brighter day

  • Property svnwiki:tags set to egg eggs
File size: 33.7 KB
Line 
1== sql-de-lite
2
3'''sql-de-lite''' is an interface to SQLite 3 for Chicken 4.
4
5'''sql-de-lite''' has the following features:
6
7* Integrated SQLite3 library
8* Prepared statement cache
9* High-level and low-level API
10* User-defined scalar and aggregate functions
11* Focus on ensuring database is never accidentally left locked
12* Rollbacks and database close permitted even if statements are open
13
14[[toc:]]
15
16=== Installation
17
18Starting with '''sql-de-lite''' 0.4.0, the SQLite 3 library is
19included with the egg.  It will be automatically built if the system
20library is missing or too old; otherwise, the system library is used.
21You can also override the egg's choice of internal or external library.
22
23In most cases, the following will do the right thing:
24
25 chicken-install sql-de-lite
26
27The goal of this automatic detection is ensure that you can just
28depend on this extension without requiring a separate library install
29step from your users.
30
31==== Built-in library
32
33SQLite 3.7.11 is included in the egg and will be linked in statically
34if the system library is missing or older than 3.7.11.  The built-in
35library is compiled with FTS3 and FTS4 (full text search) enabled.
36
37Additionally, a copy of the SQLite3 command shell is installed
38with your Chicken binaries as {{chicken-sqlite3}}.
39
40To force the use of the built-in library, pass the {{sql-de-lite-internal-lib}}
41feature to {{chicken-install}}.  This requires Chicken 4.5.1 or later.
42
43 chicken-install -D sql-de-lite-internal-lib sql-de-lite
44
45==== External library
46
47A SQLite library installed in the default system library location will
48be used instead of the built-in library, if present and recent enough.
49
50To force the use of an external library, pass the {{sql-de-lite-external-lib}}
51feature to {{chicken-install}}.  This requires Chicken 4.5.1 or later.
52
53 chicken-install -D sql-de-lite-external-lib sql-de-lite
54
55If you force an external library to be used, the build will fail
56unless the library is present and recent enough not to cause link
57errors.  The extension assumes you know what you doing, and does
58not enforce a minimum library version.  It is probably necessary to
59use 3.6.x or later, and the latest stable is always recommended.
60
61==== Non-standard library location
62
63You can point the build process at a library in a non-standard
64location--for example, under your home directory--by setting
65{{CSC_OPTIONS}} appropriately.
66
67 export CSC_OPTIONS="-I$HOME/local/include -L$HOME/local/lib"
68 chicken-install sql-de-lite
69
70==== Your own built-in library, static
71
72If you want to use your own built-in library, retrieve the egg source,
73[[http://www.sqlite.org/download.html|download the SQLite
74amalgamation]] zip file or tarball, then extract it into
75{{sqlite3/}} inside the egg directory.
76
77 chicken-install -r sql-de-lite
78 cd sql-de-lite
79 curl -O http://www.sqlite.org/sqlite-amalgamation-3_7_0_1.zip
80 unzip sqlite-amalgamation-3_7_0_1.zip -d sqlite3
81 chicken-install          # build and install the egg
82
83All we really need is {{shell.c}}, {{sqlite3.c}}, and {{sqlite3.h}}
84from the amalgamation.  Autoconf is not used.
85
86Currently, there is no way to override the compilation options to
87SQLite3 from {{chicken-install}}.  If you need to do so, edit the
88{{sql-de-lite.setup}} file.
89
90=== Low-level interface
91
92The low-level interface maps closely to the underlying library API.  It may be used in tandem with or instead of the high-level API as desired.
93
94==== Opening the database
95
96<procedure>(open-database filename)</procedure>
97
98Opens {{filename}}, a sqlite3 database.  If no database exists, one is created transparently.  {{filename}} may also be one of the following symbols:
99
100* {{memory}}: a new database in memory unique to this connection
101* {{temp}} or {{temporary}}: a new temporary database on disk, visible only to this connection
102
103Returns a {{#<sqlite-database>}} object.
104
105==== Closing the database
106
107<procedure>(close-database db)</procedure>
108
109Closes the database connection {{db}}.
110
111The prepared statement cache is flushed prior to closing, finalizing
112all cached statements.  Transient statements must be finalized
113manually, or the close will fail.  (The high-level query/exec
114interface does this for you.)
115
116'''Note.'''  Prior to 0.5.0, we did finalize transient statements as
117well, by walking the library's list of open statements before closing.
118Unfortunately, this included any statements prepared and owned by
119SQLite itself--for example, when using FTS--resulting in a double finalize
120and crash.
121
122<procedure>(database-closed? db)</procedure>
123
124Predicate that checks if database {{db}} is closed.
125
126==== Preparing a SQL statement
127
128<procedure>(prepare db sql)</procedure>
129
130Looks up a prepared statement in the statement cache.  If not found,
131it prepares a new statement and caches it, returning the statement.  If
132found, the statement is reset if need be, and returned.  An exception
133is thrown if a statement we pulled from cache is currently running --- in
134other words, has been stepped at least once and has not yet returned
135{{SQLITE_DONE}}. (NB: warn and reset would also be acceptable
136behavior.)
137
138Preparing a SQL statement consisting entirely of whitespace or comment
139is an error as of 0.5.0.
140
141Returns a {{#<sqlite-statement>}} object.
142
143<procedure>(prepare-transient db sql)</procedure>
144
145Same as {{prepare}}, but bypasses the cache completely.  This
146procedure is subject to removal.
147
148You ''must'' be sure to finalize all transient statements prior to
149closing the database.  For a safer option, use {{sql/transient}}
150with {{query}} or {{exec}}.
151
152<parameter>(prepared-cache-size n) [default: 100]</parameter>
153
154Sets the capacity of the prepared statement cache, in statements.
155
156When the cache reaches capacity and a new statement is prepared, the
157least recently used statement is finalized and drops off the cache.
158
159This setting takes effect only upon initiating a new connection, and
160the statement cache is unique per connection.
161
162Set capacity to 0 to disable the cache; this is not recommended.
163All statements will become effectively transient, and must be
164finalized manually.  Using {{sql}} with {{query}} and {{exec}}
165will take care of this for you.
166
167==== Stepping the statement
168
169<procedure>(step statement)</procedure>
170
171Steps {{statement}} and returns one of the following:
172
173* {{'row}}: a row was returned ({{SQLITE_ROW}})
174* {{'done}}: the statement is done executing ({{SQLITE_DONE}})
175* {{#f}}: step failed due to error
176
177{{#f}} is only ever returned if raising exceptions is disabled.
178Completion of execution is still considered a "success" and so the
179true value {{'done}} is returned, rather than {{#f}}.
180
181Upon database error, the statement is reset.
182
183==== Resetting a statement
184
185<procedure>(reset statement)</procedure>
186
187Resets {{statement}} to the beginning of its program, returning the
188statement.
189
190==== Finalizing a statement
191
192<procedure>(finalize statement)</procedure>
193
194Finalize {{statement}}.  Finalizing a finalized statement or a cached
195statement is a no-op.  Finalizing a statement on a closed database is
196also a no-op.  Cached statements are finalized as they expire, and all
197other statements known to the SQLite library are automatically
198finalized when the database is closed, so it is rarely necessary to
199call this directly.
200
201Transient statements (statements deliberately prepared to bypass the
202cache, including those that are prepared when cache size is zero) must
203be finalized either by using this procedure or the {{query}} /
204{{exec}} interface.
205
206<procedure>(resurrect statement)</procedure>
207
208Resurrects a previously finalized statement {{s}} or, if still alive, just
209resets it.  Returns {{s}}, which is also modified in place.
210
211Resurrection is accomplished either by pulling a previously prepared
212SQLite statement from the cache, or if it is not cached, by
213re-preparing the original SQL associated with the statement.
214Afterwards, the statement may be used normally.
215
216==== Binding parameters
217
218<procedure>(bind statement index value)</procedure>
219
220Bind parameter at {{index}} of {{statement}} to {{value}}, and returns
221{{statement}}.  The variable {{index}} may be an integer (the first
222parameter is 1, not 0) or a string for a named parameter --- for
223example, "$key", ":key" or "@key".  For named parameters, the {{$}},
224{{:}} or {{@}} must be included in the string.  A reference to an
225invalid index will throw an exception.
226
227<procedure>(bind-parameters statement . parameters)</procedure>
228
229Convenience function which binds {{parameters}} to indices 1 .. n, in
230order.  Keyword arguments are permitted; {{foo: 3}} will bind 3
231to parameter {{:foo}}.
232
233The number of parameters must match the statement's
234{{bind-parameter-count}}, or an error will be signaled.  Also, all
235keywords used must be valid parameter names.
236
237Mixing named and anonymous arguments in the same statement is not
238recommended.
239
240<procedure>(bind-parameter-count statement)</procedure>
241
242Returns the number of bound parameter slots in this prepared statement.  If numbered parameters are present, gaps may be left in the sequence.  Named parameters count in the slot total as well.
243
244<procedure>(bind-parameter-name statement i)</procedure>
245
246Returns a string representing the name of the bound parameter at index
247{{i}}, or {{#f}} if the parameter is anonymous or is out of range.
248
249The string includes the parameter name prefix; for example {{":foo"}},
250{{"$foo"}}, {{"@foo"}} or {{"?nnn"}}.
251
252==== Obtaining result data
253
254In this section's examples, we assume a simple database has been
255created with the following commands:
256
257 CREATE TABLE cache(key text, val text);
258 INSERT INTO cache(key,val) VALUES('foo', 'bar');
259 INSERT INTO cache(key,val) VALUES('baz', 'quux');
260
261and a SELECT statement has been prepared and stepped once:
262
263 (define s (prepare db "SELECT rowid, * from cache;"))
264 (step s)  ; => row
265
266and {{s}} is implicitly reset between examples.
267
268<procedure>(column-name statement index)</procedure>
269
270Return the name of the specified result set column as a symbol.  The statement need not have been stepped to retrieve column names or column count.
271
272 (column-name s 1)
273 ; => key
274
275<procedure>(column-names statement)</procedure>
276
277Convenience function which returns a list of all column names for the
278result set, in order.
279
280 (column-names s)
281 ; => (rowid key val)
282
283<procedure>(column-count statement)</procedure>
284
285Return the number of columns in the result set returned by the
286prepared statement.
287
288 (column-count s)
289 ; => 3
290
291<procedure>(column-type statement index)</procedure>
292
293Returns the type of the indexed column in the current row.  SQLite is dynamically typed and the column types are unique to each row.
294
295<table>
296<tr><th>Symbol</th><th>Database type</th></tr>
297<tr><td>integer</td><td>SQLITE_INTEGER</td></tr>
298<tr><td>float</td><td>SQLITE_FLOAT</td></tr>
299<tr><td>text</td><td>SQLITE_TEXT</td></tr>
300<tr><td>blob</td><td>SQLITE_BLOB</td></tr>
301<tr><td>null</td><td>SQLITE_NULL</td></tr>
302</table>
303
304 (map (lambda (i) (column-type s i))
305      (list 0 1 2))
306 ; => (integer text text)
307
308<procedure>(column-data statement index)</procedure>
309
310Returns the data from the indexed column in the current row.
311
312<table>
313<tr><th>Column type</th><th>Scheme type</th></tr>
314<tr><td>integer</td><td>Exact or inexact number</td></tr>
315<tr><td>float</td><td>Inexact number</td></tr>
316<tr><td>text</td><td>String</td></tr>
317<tr><td>blob</td><td>Blob</td></tr>
318<tr><td>null</td><td>'()</td></tr>
319</table>
320
321 (map (lambda (i) (column-data s i))
322      (list 0 1 2))
323 ; => (1 "foo" "bar")
324
325{{integer}} values are retrieved with {{sqlite3_column_int64}}.  On a
32632-bit machine, values outside the signed 31-bit fixnum range are
327returned as inexact numbers.  On a 64-bit machine, values outside the
328signed 63-bit fixnum range are returned as inexact numbers.  Note that
329inexact numbers are 64-bit floating point values, and can only accurately represent 53 bits of an integer.
330
331<procedure>(row-data statement)</procedure>
332
333Retrieve a list of column data from the current row.  If the last
334execution of {{step}} returned {{done}}, a NULL value will be returned
335for every column.
336
337 (row-data s)
338 ; => (1 "foo" "bar")
339
340<procedure>(row-alist statement)</procedure>
341
342Retrieve an alist mapping column names to column data for the current row.
343
344 (row-alist s)
345 ; => ((rowid . 1) (key . "foo") (val . "bar"))
346
347<procedure>(change-count db)</procedure>
348
349Returns the number of database rows that were changed or inserted or
350deleted by the most recently completed SQL statement, not including
351triggers, as in [[http://www.sqlite.org/capi3ref.html#sqlite3_changes|sqlite3_changes]].
352
353<procedure>(total-change-count db)</procedure>
354
355Returns the number of row changes caused by INSERT, UPDATE or DELETE
356statements since the database connection was opened, including
357triggers, as in [[http://www.sqlite.org/capi3ref.html#sqlite3_total_changes|sqlite3_total_changes]].
358
359<procedure>(last-insert-rowid db)</procedure>
360
361Get the ROWID of the last successful INSERT, as in [[http://www.sqlite.org/capi3ref.html#sqlite3_last_insert_rowid|sqlite3_last_insert_rowid]].
362
363=== High-level interface
364
365==== call-with-database
366
367<procedure>(call-with-database filename proc)</procedure>
368
369Opens a database, calls {{proc}} with the database object and then closes the database on return.  If an error occurs in proc, the database is closed immediately.
370
371==== Statements
372
373<procedure>(sql db sql-str)</procedure>
374
375Creates a statement object associated with the database connection
376{{db}} and the SQL {{sql-str}}.  Preparation of the statement is
377deferred until needed.  This is a normal statement in every respect
378except that it must be {{resurrect}}ed before it can be used.
379
380{{sql}} is recommended over {{prepare}} when using the {{query}} /
381{{exec}} interface so that you can declare your statements without
382compiling them until and unless they are actually used.
383
384Additionally, {{sql}} is safer.  For example, when the statement cache
385is disabled, an error that occurs between {{prepare}} and {{query}}
386may leak an unfinalized statement.  An error occurring between {{sql}}
387and {{query}} is no problem, because preparation is deferred until
388safely inside {{query}}.  The same goes for {{sql/transient}},
389which bypasses the cache completely.
390
391<procedure>(sql/transient db sql-str)</procedure>
392
393Equivalent to {{(sql db sql-str)}}, but the statement will not be
394cached; it is prepared anew every time it is resurrected.
395
396{{sql/transient}} is recommended over {{prepare-transient}} for
397the same reasons mentioned in {{sql}}.
398
399==== Query
400
401<procedure>(query proc s . args)</procedure>
402
403Resurrects statement {{s}}, binds {{args}} to {{s}} using
404{{bind-parameters}}, and performs a {{query*}}.  If the statement is
405transient, it is finalized immediately afterward, even if an exception
406occurs.
407
408{{query}} is the usual way to perform a query unless you need to bind
409arguments manually, need more control or are using the low-level
410interface, in which case you can use {{query*}} if desired.
411
412You typically call {{query}} or {{query*}} with one of the provided
413result fetching procedures; you can also pass your own
414procedure to perform whichever operations you would like.
415
416<procedure>(query* proc s)</procedure>
417
418Calls {{(proc s)}} and resets the statement {{s}} immediately afterward, to
419avoid locking the database.  If an exception occurs during proc,
420the statement will still be reset.  The statement is not reset before
421execution.
422
423The entire purpose of {{query*}} is to ensure a statement is reset
424after it is executed.  If a statement were left in a running
425state --- for example, if an uncaught exception occurs during proc, or
426you simply do not exhaust its result set --- then the database will
427be locked for writing until the statement is finalized.
428
429===== Query procedures
430
431<procedure>(fetch s)</procedure>
432<procedure>(fetch-row s)</procedure>
433
434Fetch the next row of the result set.  This is the equivalent to
435performing a {{step}} followed by a {{row-data}} call, and works with
436both the high- and low-level interfaces.  If the statement has
437finished executing, fetch returns '().  These query procedures do not
438reset the statement before or afterward; one may do so using {{reset}}
439or {{query}}.
440
441{{fetch}} and {{fetch-row}} are aliases.
442
443 (fetch s)
444 ; => (1 "foo" "bar")
445 (fetch s)
446 ; => (2 "baz" "quux")
447 (fetch s)
448 ; => ()
449 (fetch s)
450 ; => error
451 (query fetch s)
452 ; => (1 "foo" "bar")
453 (query fetch s)
454 ; => (1 "foo" "bar")
455 (fetch s)
456 ; => (1 "foo" "bar")
457
458<procedure>(fetch-all s)</procedure>
459<procedure>(fetch-rows s)</procedure>
460
461Calls {{fetch}} until it returns {{'()}}, and collects the
462result into a list.  {{fetch-all}} and {{fetch-rows}} are
463aliases.
464
465 (query fetch-all s)
466 ; => ((1 "foo" "bar")
467       (2 "baz" "quux"))
468
469<procedure>(fetch-alist s)</procedure>
470
471Fetch the next row of the result set and return an alist
472mapping symbolic row names to values.  Equivalent to {{step}}
473followed by {{row-alist}}.
474
475 (query fetch-alist s)
476 ; ((rowid . 1) (key . "foo") (val . "bar"))
477
478<procedure>(fetch-alists s)</procedure>
479
480Fetches all rows and returns a list of alists, one per row.
481
482 (query fetch-alists s)
483 ; (((rowid . 1) (key . "foo") (val . "bar"))
484 ;  ((rowid . 2) (key . "baz") (val . "quux")))
485
486<procedure>(fetch-value s)</procedure>
487
488Fetches and returns only the first value (first column) of the next
489row, or {{#f}} if the row contained no column data.  Equivalent to
490using {{first-column}} on the result of a {{fetch}}, but does not
491materialize the entire row.
492
493 (query fetch-value
494        (sql db "select key from cache"))
495 ; => "foo"
496
497 (query fetch-value
498        (sql db "select key from cache where key=?") "nosuchkey")
499 ; => #f
500
501<procedure>(fetch-column s)</procedure>
502
503Fetches all rows and returns a list containing the first column
504of each, or {{'()}} if there was no column data.
505
506 (query fetch-column
507        (sql db "select key from cache"))
508 ; => ("foo" "bar")
509
510<procedure>(for-each-row proc)</procedure><br>
511<procedure>(for-each-row* proc)</procedure>
512
513Returns a procedure suitable for passing to {{query}}, taking one argument, a statement object.
514
515The procedure will call fetch once for each row and call your callback as {{(proc row)}}, discarding the results.
516
517 (query (for-each-row
518          (lambda (x) (print "row: " x)))
519        s)
520 ; row: (1 foo bar)
521 ; row: (2 baz quux)
522 ; => undefined
523
524{{for-each-row*}} behaves like {{for-each-row}}, but your callback is
525invoked with one argument for each column value.  For example, these
526produce equivalent results:
527
528 (query (for-each-row (match-lambda ((name sql)
529                         (print "table: " name " sql: " sql ";"))))
530        (sql db "select name, sql from sqlite_master;")) 
531 (query (for-each-row* (lambda (name sql)
532                         (print "table: " name " sql: " sql ";")))
533        (sql db "select name, sql from sqlite_master;"))
534
535<procedure>(map-rows proc)</procedure><br>
536<procedure>(map-rows* proc)</procedure>
537
538Return a procedure suitable for passing to {{query}}, taking one argument, a statement object.
539
540The procedure will call fetch once for each row and call {{(proc row)}}, collecting the results into a list, in order.
541
542 (query (map-rows car) s)
543 ; => (1 2)
544
545Another example; these two produce equivalent results:
546
547 (query (map-rows car) (sql db "select name, sql from sqlite_master;"))
548 (map car (query fetch-all (sql db "select name, sql from sqlite_master;")))
549
550{{map-rows*}} behaves like {{map-rows}}, but your callback
551is invoked with one argument for each column value.
552
553<procedure>(fold-rows kons knil)</procedure><br>
554<procedure>(fold-rows* kons knil)</procedure><br>
555
556Calls {{(kons x xs)}} once for each row, where {{x}} is the current row data and {{xs}} is the seed (previous return value from {{kons}}).  The initial seed is {{knil}}.
557
558 (query (fold-rows cons '()) s)
559 ; => ((2 "baz" "quux") (1 "foo" "bar"))
560
561 ;; sum the returned rowids
562 (query (fold-rows (lambda (x xs) (+ (car x) xs))
563                   0)
564        s)
565 ; => 3
566
567 ;; that was contrived, you should actually do the sum in the database
568 (car (query fetch (sql db "select sum(rowid) from mytable;")))
569 ; => 3
570
571{{fold-rows*}} behaves like {{fold-rows}}, but the {{kons}} callback is invoked
572with one column for each argument value, plus the seed as the last argument --
573for example, as {{(kons x y z seed)}}.  This turns out to be quite inefficient
574and makes little sense, so {{fold-rows*}} is deprecated as of 0.4.2.
575
576<procedure>(first-column row)</procedure>
577
578Returns the first column of {{row}}, or {{#f}} if the row is '().
579
580 (first-column (query fetch (sql db "select sum(rowid) from mytable;")))
581 ; => 3
582
583You can also use {{fetch-value}} here instead:
584
585 (query fetch-value (sql db "select sum(rowid) from mytable;"))
586 ; => 3
587
588==== Execute
589
590<procedure>(exec s . args)</procedure>
591
592Resurrects statement {{s}}, binds {{args}} to {{s}} using
593{{bind-parameters}}, and performs an {{exec*}}.  If {{s}} is
594transient, it is finalized immediately afterward, even if an exception
595occurs.
596
597<procedure>(exec* s)</procedure>
598
599Executes statement {{sql}}, returning the number of changes (if the
600result set has no columns as in {{INSERT}}, {{DELETE}}, {{UPDATE}}) or the first
601row (if column data is returned as in {{SELECT}}).  In the latter
602case, it is like performing a (query* fetch s), but is more efficient.
603
604Resurrection is omitted, as it would wipe out any bindings.  Reset is
605NOT done beforehand; it is cheap, but the user must reset before a
606bind anyway.
607
608The statement is always reset afterward, even if an exception occurs,
609to avoid locking the database.  Note however that an internal error
610when retrieving column data (such as a string > 16MB) will leave the
611statement open -- this is a flaw in the current implementation.
612
613 (exec (sql db "INSERT INTO cache(key, val) values(?, ?);")
614       "chicken" 4)
615 ; => 1
616 (exec (sql db "SELECT * FROM cache WHERE key = ?;")
617       "chicken")
618 ; => ("chicken" "4")
619 (first-column (exec (sql db "SELECT val FROM cache;")))
620 ; => "bar"
621 (first-column (exec (sql db "SELECT val FROM cache;")))
622 ; => "bar"
623
624=== Transactions
625
626==== with-transaction
627
628<procedure>(with-transaction db thunk #!optional (type deferred))</procedure><br>
629<procedure>(with-deferred-transaction db thunk)</procedure><br>
630<procedure>(with-immediate-transaction db thunk)</procedure><br>
631<procedure>(with-exclusive-transaction db thunk)</procedure>
632
633Executes {{thunk}} within a {{BEGIN TRANSACTION}} block, and returns
634the value of {{thunk}}.  The optional {{type}} may be one of the symbols
635{{deferred}}, {{immediate}}, or {{exclusive}}.  You may also use the
636named convenience functions instead of the optional parameter.
637
638The transaction is committed with {{(commit db)}} if {{thunk}} returns
639a true value.  Escaping or re-entering the dynamic extent of {{thunk}}
640will not commit or rollback the in-progress transaction.  However, if
641an exception occurs during {{thunk}}, or {{thunk}} returns {{#f}}, or
642the commit fails, the transaction will be rolled back with {{(rollback db)}}.  If this rollback fails, that is a critical error and you should likely abort.
643
644==== rollback
645
646<procedure>(rollback db)</procedure>
647
648Rollback current transaction.  Unconditionally resets running queries
649before doing so, as rollback would fail if read or read/write queries
650are running.  Successful rollback returns a true value.  Rolling back
651in autocommit mode also returns a true value.
652
653==== commit
654
655<procedure>(commit db)</procedure>
656
657Commit current transaction.  This does not rollback running queries,
658because running read queries are acceptable, and the behavior in the
659presence of pending write statements is unclear.  If the commit
660fails, you can always rollback, which will reset the pending queries.
661
662Successful commit, or commit in autocommit mode, returns a true value.
663
664==== autocommit?
665
666<procedure>(autocommit? db)</procedure>
667
668Returns {{#t}} if the database is in autocommit mode, or {{#f}} if within a transaction.
669
670=== Error handling
671
672When a database error occurs, an exception of type {{(exn sqlite)}} is raised, containing
673the database error code and message.  This information is also available from the
674database using the {{error-code}} and {{error-message}} interface.
675
676<procedure>(sqlite-exception? e)</procedure>
677
678Is {{e}} an exception raised by the database?
679
680<procedure>(sqlite-exception-status e)</procedure>
681
682Get the database error code as a symbol.  See {{error-code}} for details.
683
684<procedure>(sqlite-exception-message e)</procedure>
685
686Get the database error message as a string.
687
688<procedure>(error-code db)</procedure>
689
690Returns the last database error code as a symbol.
691
692<table>
693<tr><th>Symbol            </th><th>C error code</th></tr>
694<tr><td>ok                </td><td>SQLITE_OK</td></tr>
695<tr><td>error          </td><td>SQLITE_ERROR</td></tr>
696<tr><td>internal    </td><td>SQLITE_INTERNAL</td></tr>
697<tr><td>permission          </td><td>SQLITE_PERM</td></tr>
698<tr><td>abort          </td><td>SQLITE_ABORT</td></tr>
699<tr><td>busy            </td><td>SQLITE_BUSY</td></tr>
700<tr><td>locked        </td><td>SQLITE_LOCKED</td></tr>
701<tr><td>no-memory  </td><td>SQLITE_NOMEM</td></tr>
702<tr><td>read-only  </td><td>SQLITE_READONLY</td></tr>
703<tr><td>interrupt  </td><td>SQLITE_INTERRUPT</td></tr>
704<tr><td>io-error    </td><td>SQLITE_IOERR</td></tr>
705<tr><td>corrupt      </td><td>SQLITE_CORRUPT</td></tr>
706<tr><td>not-found  </td><td>SQLITE_NOTFOUND</td></tr>
707<tr><td>full            </td><td>SQLITE_FULL</td></tr>
708<tr><td>cant-open  </td><td>SQLITE_CANTOPEN</td></tr>
709<tr><td>protocol    </td><td>SQLITE_PROTOCOL</td></tr>
710<tr><td>empty          </td><td>SQLITE_EMPTY</td></tr>
711<tr><td>schema        </td><td>SQLITE_SCHEMA</td></tr>
712<tr><td>too-big      </td><td>SQLITE_TOOBIG</td></tr>
713<tr><td>constraint   </td><td>SQLITE_CONSTRAINT</td></tr>
714<tr><td>mismatch    </td><td>SQLITE_MISMATCH</td></tr>
715<tr><td>misuse        </td><td>SQLITE_MISUSE</td></tr>
716<tr><td>no-lfs        </td><td>SQLITE_NOLFS</td></tr>
717<tr><td>authorization    </td><td>SQLITE_AUTH</td></tr>
718<tr><td>format        </td><td>SQLITE_FORMAT</td></tr>
719<tr><td>range          </td><td>SQLITE_RANGE</td></tr>
720<tr><td>not-a-database  </td><td>SQLITE_NOTADB</td></tr>
721<tr><td>row              </td><td>SQLITE_ROW</td></tr>
722<tr><td>done            </td><td>SQLITE_DONE</td></tr>
723</table>
724
725<procedure>(error-message db)</procedure>
726
727Returns the last database error message as a string.
728
729<parameter>(raise-database-errors BOOLEAN) [default: #t]</parameter>
730
731Set to {{#t}} to raise an exception on database error, {{#f}} to return a false value.  Note that certain critical errors, such as "misuse of interface" and arity mismatches of bound parameters will raise exceptions regardless.  Procedures in this extension that utilize the low-level interface are written to work correctly with both {{#f}} return values and errors.
732
733Disabling raising of database errors is intended for experts and this option may be removed.
734
735=== Busy handling
736
737Busy handling is done outside of the library, instead of inside the
738library busy handler, because with SRFI-18 threads it is not legal to
739yield within a callback.  The backoff algorithm of
740sqlite3_busy_timeout is reimplemented.
741
742SQLite can deadlock in certain situations and to avoid this will
743return SQLITE_BUSY immediately rather than invoking the busy handler.
744However if there is no busy handler, we cannot tell a retryable
745SQLITE_BUSY from a deadlock one.  To gain deadlock protection we
746register a simple busy handler which sets a flag indicating this BUSY
747is retryable.  This is done without invoking a callback into Scheme.
748
749==== set-busy-handler!
750
751<procedure>(set-busy-handler! db proc)</procedure>
752
753Register the busy handler {{proc}} on the open connection {{db}};
754the handler will be called repeatedly when a {{prepare}} or {{step}}
755operation returns SQLITE_BUSY.  It is passed the two arguments {{(db
756count)}}, which are the associated database connection and the number
757of times this busy handler has been invoked so far for this operation.
758The procedure should return {{#f}} to stop retrying and have the
759operation return a BUSY error to the caller, or {{#t}} if the busy
760operation should be retried.
761
762By default, no busy handler is registered.  Busy handlers are unique
763to each connection and must be registered after the connection is
764open.
765
766 (call-with-database
767  (lambda (db)
768   (set-busy-handler! db (busy-timeout 10000)) ; 10 second timeout
769   ...))
770
771==== busy-timeout
772
773<procedure>(busy-timeout ms)</procedure>
774
775Return a procedure suitable for use in set-busy-handler!, implementing
776a spinning busy timeout using the SQLite3 busy wait algorithm.  This
777handler will wait up to {{ms}} milliseconds total before giving up.
778Other threads may be scheduled while this one is busy-waiting.
779
780=== User-defined functions
781
782You may define your own scalar and aggregate functions in Scheme.
783
784Currently, a significant performance penalty is imposed on all calls
785to {{step}} once the first user-defined function is registered, due to
786a limitation on callbacks in Chicken.  This penalty is on the order of
78710 times, and is imposed on all statements regardless of whether a
788user function is actually invoked.  However, if no functions are
789registered, there is no penalty.
790
791<procedure>(register-scalar-function! db name nargs proc)</procedure>
792
793Register a user-defined scalar function {{name}} of arity {{nargs}}.
794{{nargs}} may range from 0 to 127, or -1 to define a function taking any
795number of arguments.  You may define multiple functions with differing
796numbers of arguments.  Defining a function with the same {{nargs}} as
797an existing function will redefine it, even built-in functions.
798
799{{proc}} should be a function taking {{nargs}} arguments; to delete an
800existing function, set proc to {{#f}}.  The return value is used as
801the value of the scalar function.  If an error occurs during the
802function, it is signaled as a database error.
803
804Functions must be defined anew for every database connection.
805
806Be very careful when combining [[#Callbacks and SRFI-18|user-defined functions and SRFI-18 threads]].
807
808<procedure>(register-aggregate-function! db name nargs pstep #!optional (seed 0) (pfinal identity))</procedure>
809
810Register a user-defined aggregate function {{name}} of arity {{nargs}}.
811{{nargs}} may range from 0 to 127, or -1 to define a function taking any
812number of arguments.  You may define multiple functions with differing
813numbers of arguments.  Defining a function with the same {{nargs}} as
814an existing function will redefine it, even built-in functions.
815
816{{seed}} is the initial seed passed to this particular invocation of
817the aggregate function.  At every step, {{pstep}} is invoked as
818{{(pstep seed arg1 ... argn)}} and its return value becomes the next
819seed.  Finally, {{(pfinal seed)}} is invoked to do any final
820transformation necessary on the seed.  (For example, if {{seed}} is a record,
821you may need to pull out and return the relevant data.)  The return
822value of {{pfinal}} is used as the value of the aggregate function.
823If an error occurs during {{pstep}} or {{pfinal}}, it is signaled as
824a database error.
825
826{{pstep}} should be a function taking {{nargs}} arguments.  To delete
827an existing aggregate function, set pstep to {{#f}}.  In this case the
828values of {{seed}} and {{pfinal}} are ignored.
829
830Functions must be defined anew for every database connection.
831
832Be very careful when combining [[#Callbacks and SRFI-18|user-defined functions and SRFI-18 threads]].
833
834==== Callbacks and SRFI-18
835
836'''Warning.''' Callbacks are inherently unsafe
837in combination with SRFI-18 threads; callbacks must always be exited
838in the order they were entered, which can be violated with
839thread-switching or {{call/cc}}.  {{sql-de-lite}} takes care to
840disable thread scheduling inside user-defined function callbacks.
841It also traps and safely signals errors via the library.
842
843However, you ''must not'':
844
845* invoke {{call/cc}} to escape the user-defined function
846* invoke {{thread-yield!}} or {{thread-sleep!}}
847* perform blocking I/O (for example, writing to a file or network port)
848
849unless you are a wizard, or can guarantee no other thread can ever invoke
850a callback (even one created with another egg).
851
852User-defined functions have not been heavily stress-tested in the presence
853of multiple threads, so caution is advised.
854
855=== Miscellaneous
856
857<procedure>(schema db)</procedure>
858
859Returns a list of SQL statements making up the database schema.
860
861<procedure>(print-schema db)</procedure>
862
863Displays the database schema to the current output port; the result is similar to using {{.schema}} at the {{sqlite3}} command prompt.
864
865<procedure>(flush-cache! db)</procedure>
866
867Flush the prepared statement cache, 
868
869<procedure>(finalized? statement)</procedure>
870
871Returns {{#t}} if the statement is finalized or has never been prepared.
872
873 (finalized? (sql db "select 1;"))     ; => #t
874 (finalized? (prepare db "select 1;")) ; => #f
875
876<string>library-version</string>
877
878A string representing the SQLite3 library version (e.g. "3.6.11").
879
880=== Notes
881
882==== SRFI-18 threads
883
884If you are operating on the same database in multiple threads, you
885must open a new connection per thread.  Reusing the same connection
886will result in corruption.
887
888Be very careful when combining [[#Callbacks and SRFI-18|user-defined functions and SRFI-18 threads]].
889
890=== About this egg
891
892==== Version history
893
894; 0.6.1 : Reverse seed and pstep args to {{register-aggregate-function!}}, make seed optional
895; 0.6.0 : Scalar and aggregate user-defined functions.  Upgrade internal library to 3.7.11.
896; 0.5.1 : Support named parameters.
897; 0.5.0 : Add database-closed?, sql/transient.  Ensure transient statements are finalized in QUERY and EXEC. Disable last-resort finalization of open statements in close-database.  Warn if database is not closed after call-with-database.  Ensure statement column count and column names are correct if the database schema changes.  Make preparing whitespace/comment SQL illegal (for now).  Ensure resurrected transient statements are still transient.
898; 0.4.5 : Add {{fetch-value, -column, -alists, -row, and -rows}}.  Reset {{exec}}ed stmts that return 0 rows, preventing spurious reserved lock after ROLLBACK.  Bind exact numbers to int64, not int.
899; 0.4.4 : Remove deprecated {{milliseconds->time}} call
900; 0.4.3 : byte-vector and pointer types upgraded for compat with Chicken 4.7
901; 0.4.2 : Upgrade to SQLite 3.7.3; fix {{fold-rows*}} (and deprecate it)
902; 0.4.1 : Drop dependency on [[easyffi]]
903; 0.4.0 : Add integrated SQLite3 library
904; 0.3.0 : Initial release
905
906==== Author
907
908[[http://3e8.org|Jim Ursetto]]
909
910==== License
911
912The egg is BSD-licensed.  The SQLite 3 library is public domain.
Note: See TracBrowser for help on using the repository browser.