18. Bigloo
A practical Scheme compiler
User manual for version 4.3a
April 2017 -- Database
Bigloo supports database programming. The current version proposes a SQLite binding.

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










This Html page has been produced by Skribe.
Last update Tue Apr 18 19:20:20 2017.