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