Database
Bigloo supports database programming. The current version proposes a SQLite binding.SQLite
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 thesqlite
library. Here is an example of module
that uses this library.
(module example1 (library sqlite)) (let ((db (instantiate::sqlite))) ...)
sqlitebigloo 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.
.keep
sqlite-close sqlitebigloo sqlite function
This function closes a database previously opened by creating an instance of the classsqlite
.
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))
.keep
sqlite-format string arg ...bigloo sqlite function
Constructs a string of characters representing an SQLite commands. This function acts asformat
(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.
(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"
.keep
sqlite-exec sqlite string arg ...bigloo sqlite function
The functionsqlite-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
.keep
sqlite-eval sqlite procedure string arg ...bigloo sqlite function
The functionsqlite-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.
.keep
sqlite-map sqlite procedure string arg ...bigloo sqlite function
The functionsqlite-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)
.keep
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 sqlitebigloo 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'")
.keep
sqlite-table-name-of-columns sqlite tablebigloo sqlite function
Returns the name of columns in the table..keep
sqlite-last-insert-rowid sqlitebigloo sqlite function
Returns the SQLite rowid of the last inserted row..keep