alice
library
manual.

Alice Project

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


last modified 2007/Mar/30 17:10