The SQLite structure

^________ Synopsis ^{____________________________________________________}

 signature SQLITE
 structure SQLite : SQLITE
 

The SQLite structure provides an interface to a database engine using a simple flat-file format. See the SQLite web page for further details. Alice uses version 3 of the library.

^________ Import ^{______________________________________________________}

 import structure SQLite from "x-alice:/lib/sqlite/SQLite"

^________ Interface ^{___________________________________________________}

 signature SQLITE =
 sig
 type db
 exception SQLError of string
 exception TypeError of string
 val opendb : string -> db
 val closedb : db -> unit
 val query : db * string -> 'a[...] list
 end
 

^________ Description ^{_________________________________________________}

opendb fileName

Opens the SQLite database stored in file fileName. If opening fails, this raises an SQLError exception with a string describing what went wrong.

closedb db

Closes the SQLite database referened through db. If closing fails, this raises an SQLError exception with a string describing what went wrong.

query (db, sql)

Runs the SQL query sql on database db. The resulting rows are returned as a lazy list. Each row is returned as a tuple or record of strings, integers, or reals, or the corresponding option types.

The return type must match the result of your query: It must be either a tuple of the right size or a record with the same labels as the column names. If the result does not match the type, a TypeError exception is raised with a string describing the error.

The fields of the record or tuple must be of type int,real, or string, or an option of these types. Whether a plain type or an option is given determines how the special database value null is treated: it is converted to the empty string or 0 if the field type is a plain type, or to NONE if an option type was given. If the type does not satisfy these rules, a TypeError exception is raised with a string describing the error.

Assuming a database of users with the following layout:

 table users: lastname (text), firstname (text), uid (int), password (text)
 

the following queries would be legal:

 val users : {lastname:string, firstname:string, uid:int option} list
 = query (db, "SELECT lastname,firstname,uid FROM users")
 val [pwd:string] = query (db, "SELECT password FROM users WHERE uid=42")
 val all : (string * string * int * string) list =
 = query (db, "SELECT * FROM users")
 

---