SQLITE library Sqlite == Description == This document describes the HOP supports for SQL queries using the [[http://www.sqlite.org/|sqlite]] database. == Synopsis == === Server === (class sqlite ...) (class sqltiny ...) (sqlite-close db) (sqlite-format string arg ...) (sqlite-exec db string arg ...) (sqlite-eval db procedure string arg ...) (sqlite-map db procedure string arg ...) (sqlite-name-of-tables db) (sqlite-last-insert-rowid db) === Requirements === The server functions and class described in this page are defined in the ++sqlite++ library. That is, server codes using them must include a statement such as: (module a-module-name ... (library sqlite) ...) == Examples == sqlite == Server Definitions == === ++(class sqlite ...)++=== sqlite ^ fields ^ access ^ type ^ default value ^ short description ^ | path | r | string | ++":memory:"++ | the path of the dababase. | 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)) === ++(class sqltiny ...)++=== sqltiny ^ fields ^ access ^ type ^ default value ^ short description ^ | path | r | string | ++":memory:"++ | the path of the dababase. | A lightweight and portable simplified version of SQLite. === ++(sqlite-close db)++ === sqlite-close ^ arguments ^ type ^ short description ^ | db | ::sqlite | a database. | 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 fmt arg₀ ...) === sqlite-format ^ arguments ^ type ^ short description ^ | fmt | string | the description of the format. | | arg₀ | obj | the first argument. | | ... | ... | ... | The function ++sqlite-format++ constructs a string of characters representing an SQLite commands. This function acts as ++format++. 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. 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 db fmt arg₀ ...) === sqlite-exec ^ arguments ^ type ^ short description ^ | db | ::sqlite | the database. | | fmt | string | the description of the format. | | arg₀ | obj | the first argument. | | ... | ... | ... | The function ++sqlite-exec++ //executes// an SQLite command. The command is the built by implicitly invoking ++sqlite-format++ on ++fmt++ and the optional ++arg₀++ arguments. This function returns a possibly empty list. This function returns a single element, the first one returned by the SQL engine. (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 db proc fmt arg₀ ...) === sqlite-eval ^ arguments ^ type ^ short description ^ | db | ::sqlite | the database. | | proc | procedure | the result constructor. | | fmt | string | the description of the format. | | arg₀ | obj | the first argument. | | ... | ... | ... | The function ++sqlite-eval++ invokes a SQLite command built by implicitly invoking ++sqlite-format++ on ++fmt++ on and the optional ++arg₀++, ... arguments. The result of the function is built by applying ++proc++ 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 @code{bind-exit}. Exiting from a callback will leave the database in a inconsistent state that prevent transactions to be rolled back. === ++(sqlite-map db proc fmt arg₀ ...) === sqlite-map ^ arguments ^ type ^ short description ^ | db | ::sqlite | the database. | | proc | procedure | the results constructor. | | fmt | string | the description of the format. | | arg₀ | obj | the first argument. | | ... | ... | ... | 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 ++proc++ 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 @code{bind-exit}. Exiting from a callback will leave the database in a inconsistent state that prevent transactions to be rolled back. Examples: (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) === ++(sqlite-name-of-tables db) === sqlite-name-of-tables db ^ arguments ^ type ^ short description ^ | db | ::sqlite | the database. | Returns the list name of the tables of the database. This is equivalent to: (sqlite-map db (lambda (x) x) "SELECT name FROM sqlite_master WHERE type='table'") === ++(sqlite-last-insert-rowid db) === sqlite-last-insert-rowid ^ arguments ^ type ^ short description ^ | db | ::sqlite | the database. | Returns the SQLite //rowid// of the last inserted row.