Bigloo supports database programming. The current version proposes
a SQLite binding.
The Bigloo's C back-end supports SQL queries. It relies on the SQLite
library (
http://www.sqlite.org/). The SQLite binding is accessible
to Bigloo via the
sqlite library. Here is an example of module
that uses this library.
(module example1
(library sqlite))
(let ((db (instantiate::sqlite)))
...)
|
| sqlite | Bigloo Sqlite class |
(class sqlite
(path::bstring read-only (default ":memory:")))
|
The instances of the class sqlite hold SQLite databases. A database
may be permanently stored on a disk or loaded in memory. The class attribute
path is the location on the disk where the database is stored. The
special path :memory: denotes in-memory databases. When an instance
is created a SQLite database is opened.
Example:
(define db1 (instantiate::sqlite (path "/tmp/foo.db")))
(define db2 (instantiate::sqlite))
|
Binds the global variable db1 to a database that is stored on the file
system at location /tmp/foo.db. This example also binds the global
variable db2 to an in-memory SQLite database.
|
sqlite-close sqlite | Bigloo Sqlite function |
This function closes a database previously opened by creating an instance
of the class sqlite.
Example:
(let ((db (instantiate::sqlite)))
(sqlite-exec db "CREATE TABLE table1 (x INTEGER, y INTEGER)")
(sqlite-exec db "INSERT INTO table1 VALUES( ~a, ~a )" 1 4)
(sqlite-close db))
|
|
sqlite-format string arg ... | Bigloo Sqlite function |
Constructs a string of characters representing an SQLite
commands. This function acts as format (see Input and Output). It is augmented with three additional escape sequence:
~q, ~k, and ~l. The first one build a string of
characters where the characters denoting SQL strings (i.e., the
character ') is automatically escaped. The escape character
~k introduces a list of SQL strings. The escape character
~l introduces a SQL list.
Summary of all escape codes:
~a The corresponding value is inserted into the string
as if printed with display.
~s The corresponding value is inserted into the string
as if printed with write.
~% A newline is inserted.
~~ A tilde ~ is inserted.
~q An SQL escaped string.
~l Introduces a list (comma separated).
~k Introduces a list of SQL strings.
Examples:
(module example
(library sqlite))
(sqlite-format "~a" "foo'bar") => "foo'bar"
(sqlite-format "~q" "foo'bar") => "'foo''bar'"
(sqlite-format "~a" '("foo'bar" "foo")) => "(foo'bar foo)"
(sqlite-format "~k" '("foo'bar" "foo")) => "'foo''bar','foo'"
(sqlite-format "~l" '("foo'bar" "foo")) => "foo'bar,foo"
|
|
sqlite-exec sqlite string arg ... | Bigloo Sqlite function |
The function sqlite-exec executes an SQLite command. The command
is the built by implicitly invoking sqlite-format on string and
the optional arg arguments. This function returns a single element,
the first one returned by the SQL engine.
Example:
(module example
(library sqlite))
(define *db* (instantiate::sqlite))
(sqlite-exec *db* "CREATE TABLE foo (x INTEGER, y INTEGER)")
(for-each (lambda (x)
(sqlite-exec *db* "INSERT INTO foo VALUES(~A, ~A)" x (* x x)))
(iota 10))
(sqlite-exec *db* "SELECT * FROM foo")
=> 9
|
|
sqlite-eval sqlite procedure string arg ... | Bigloo Sqlite function |
The function sqlite-eval invokes a SQLite command built by
implicitly invoking sqlite-format on string and the optional
arg arguments. The result of the function is built by applying
procedure to the first value returned by the SQLite call.
Note: user callback (procedure) must not exit. That is they must
not invoke a function create by bind-exit. Exiting from a callback will
leave the database in a inconsistent state that prevent transactions to
be rolled back.
|
sqlite-map sqlite procedure string arg ... | Bigloo Sqlite function |
The function sqlite-map invokes a SQLite command built by
implicitly invoking sqlite-format on string and the optional
arg arguments. The result is a list whose elements are built by applying
procedure to all the values returned by the SQLite call.
Note: user callback (procedure) must not exit. That is they must
not invoke a function create by bind-exit. Exiting from a callback will
leave the database in a inconsistent state that prevent transactions to
be rolled back.
Example:
(module example
(library sqlite))
(define *db* (instantiate::sqlite))
(sqlite-exec *db* "CREATE TABLE foo (x INTEGER, y INTEGER)")
(for-each (lambda (x)
(sqlite-exec *db* "INSERT INTO foo VALUES(~A, ~A)" x (* x x)))
(iota 10))
(sqlite-map *db*
(lambda (s1 s2) (+ (string->integer s1) (string->integer s2)))
"SELECT * FROM foo")
=> (0 2 6 12 20 30 42 56 72 90)
|
|
Example2:
(module example
(library sqlite))
(define *db* (instantiate::sqlite))
(sqlite-exec *db* "CREATE TABLE foo (x INTEGER, y INTEGER)")
(for-each (lambda (x)
(sqlite-exec *db* "INSERT INTO foo VALUES(~A, ~A)" x (* x x)))
(iota 10))
(sqlite-map *db* vector "SELECT * FROM foo")
=> '(#("0" "0")
#("1" "1")
#("2" "4")
#("3" "9")
#("4" "16")
#("5" "25")
#("6" "36")
#("7" "49")
#("8" "64")
#("9" "81"))
|
sqlite-name-of-tables sqlite | Bigloo Sqlite function |
Returns the name of tables in the database. This list can also be
obtained with
(sqlite-map db
(lambda (x) x)
"SELECT name FROM sqlite_master WHERE type='table'")
|
|
sqlite-table-name-of-columns sqlite table | Bigloo Sqlite function |
Returns the name of columns in the table.
|
sqlite-last-insert-rowid sqlite | Bigloo Sqlite function |
Returns the SQLite rowid of the last inserted row.
|