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 the sqlite 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.

sqlite-close sqlitebigloo 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 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'")

sqlite-table-name-of-columns sqlite tablebigloo sqlite function

Returns the name of columns in the table.

sqlite-last-insert-rowid sqlitebigloo sqlite function

Returns the SQLite rowid of the last inserted row.