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-get sqlite procedure string arg ... | bigloo sqlite function |
Similar to sqlite-eval but the callback is invoked with two
arguments: an array of column names and an array of values.
|
sqlite-for-each sqlite procedure string arg ... | bigloo sqlite function |
The function sqlite-for-each invokes a SQLite command built by
implicitly invoking sqlite-format on string and the optional
arg arguments. The function procedure is applied to all
the elements statisfying the request. It accepts two vectors. The
first one is the name of the table column, the second the values.
The function sqlite-for-each does not return any value.
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 (keys vals) (print keys vals))
"SELECT * FROM foo")
-| #("x" "y") #(0 0)
#("x" "y") #(1 1)
...
|
|
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.
|