info bigloo

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

Bigloo Sqlite class: sqlite

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

Bigloo Sqlite function: sqlite-close sqlite

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

Bigloo Sqlite function: sqlite-format string arg …

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"

Bigloo Sqlite function: sqlite-exec sqlite string arg …

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

Bigloo Sqlite function: sqlite-eval sqlite procedure string arg …

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.

Bigloo Sqlite function: sqlite-map sqlite procedure string arg …

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.

(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"))

Bigloo Sqlite function: sqlite-name-of-tables sqlite

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'")

Bigloo Sqlite function: sqlite-name-of-columns sqlite table: Returns the name of columns in the table.

Bigloo Sqlite function: sqlite-last-insert-rowid sqlite: Returns the SQLite rowid of the last inserted row.

This document was generated on March 31, 2014 using texi2html 5.0.