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

Last change on this file since 37084 was 37084, checked in by zbigniew, 6 months ago

Update Chicken 4 docs for zbigniew eggs

These eggs were ported to Chicken 5 and still support
Chicken 4; docs remain synchronized.

atom chickadee chicken-doc chicken-doc-admin doctype
feature-test socket sql-de-lite tcp6 udp6

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