//[sqlite3.flx]
//$ Core Sqlite3 functions and extensions to provide row iterator, simple statement execution,
//$ statement preperation and access to sqlite_step statement execution.
//$
//$ Iterator example:
//$
//$
//$@felix
//$ var db : sqlite3_db_t;
//$ var stmt:sqlite3_stmt_t;
//$ var err = sqlite3_open("multiple_sa.db", &db);
//$ if err != 0 do
//$ print "open DB error[abort] ";
//$ println $ sqlite3_errmsg db;
//$ goto finish;
//$ done;
//$ err = sqlite3_prepare_v2(db, "select * from contact", 21, stmt, "");
//$ if not err == (caseno SQLITE_OK) then
//$ { println ("sql error "+str(err)+":"+sqlite3_errmsg(db));goto finish; }
//$ else {
//$ var it = sqlite3_row_iterator (stmt);
//$ var row:ret_val[darray[column_value]];
//$ while (fun ():bool = { row = it();
//$ return (match row with |end_of_results[darray[column_value]] =>false |_ => true
//$ endmatch); }) () do
//$ var t = match row with
//$ | row a => ((get_text_val(get(a,0))),(get_text_val(get(a,1))))
//$ | _ => ("","")
//$ endmatch;
//$ print t; endl;
//$ done
//$ }
//$ finish:>
//$ err = sqlite3_finalize(stmt);
//$ println(str(err));
//$ sqlite3_close(db);
//$@
class Sqlite3 {
requires package "flx_sqlite3";
//$ Type of a database handle.
type sqlite3_db_t = "sqlite3*";
//$ Database open.
gen sqlite3_open : string * &sqlite3_db_t -> int =
"sqlite3_open($1.c_str(), $2)"
;
//$ Database close.
proc sqlite3_close : sqlite3_db_t = "sqlite3_close($1);";
//$ Type of an exec callback.
typedef sqlite3_exec_callback_t =
address // client data pointer established by call to sqlite3_exec
* int // number of result columns
* +(+char) // column value as text
* +(+char) // column name
--> int
;
//$ Quick sql execution using callback.
//$ arg1: db_handle
//$ arg2: sql statement.
//$ arg3: callback function.
//$ arg4: client data pointer.
//$ arg5: pointer to error message array.
//$ result: error code.
gen sqlite3_exec : sqlite3_db_t * string * sqlite3_exec_callback_t * address * &(+char) -> int =
"sqlite3_exec($1,$2.c_str(),$3,$4,$5)"
;
//$ quick sql execution without data handler callback.
//$ arg1: db_handle
//$ arg2: sql statement.
//$ arg3: pointer to error message array.
gen sqlite3_exec : sqlite3_db_t * string * &(+char) -> int =
"sqlite3_exec($1,$2.c_str(),0,0,$3)"
;
//$ Error message extractor.
gen sqlite3_errmsg : sqlite3_db_t -> +char=
"(char*)sqlite3_errmsg($1)"
;
//$ Type of sql statement handle.
type sqlite3_stmt_t = "sqlite3_stmt*";
//$ Sqlite3 return codes.
enum sqlite3_result_codes {
SQLITE_OK = 0, /* Successful result */
SQLITE_ERROR = 1, /* SQL error or missing database */
SQLITE_INTERNAL = 2, /* Internal logic error in SQLite */
SQLITE_PERM = 3, /* Access permission denied */
SQLITE_ABORT = 4, /* Callback routine requested an abort */
SQLITE_BUSY = 5, /* The database file is locked */
SQLITE_LOCKED = 6, /* A table in the database is locked */
SQLITE_NOMEM = 7, /* A malloc() failed */
SQLITE_READONLY = 8, /* Attempt to write a readonly database */
SQLITE_INTERRUPT = 9, /* Operation terminated by sqlite3_interrupt()*/
SQLITE_IOERR = 10, /* Some kind of disk I/O error occurred */
SQLITE_CORRUPT = 11, /* The database disk image is malformed */
SQLITE_NOTFOUND = 12, /* Unknown opcode in sqlite3_file_control() */
SQLITE_FULL = 13, /* Insertion failed because database is full */
SQLITE_CANTOPEN = 14, /* Unable to open the database file */
SQLITE_PROTOCOL = 15, /* Database lock protocol error */
SQLITE_EMPTY = 16, /* Database is empty */
SQLITE_SCHEMA = 17, /* The database schema changed */
SQLITE_TOOBIG = 18, /* String or BLOB exceeds size limit */
SQLITE_CONSTRAINT = 19, /* Abort due to constraint violation */
SQLITE_MISMATCH = 20, /* Data type mismatch */
SQLITE_MISUSE = 21, /* Library used incorrectly */
SQLITE_NOLFS = 22, /* Uses OS features not supported on host */
SQLITE_AUTH = 23, /* Authorization denied */
SQLITE_FORMAT = 24, /* Auxiliary database format error */
SQLITE_RANGE = 25, /* 2nd parameter to sqlite3_bind out of range */
SQLITE_NOTADB = 26, /* File opened that is not a database file */
SQLITE_ROW = 100, /* sqlite3_step() has another row ready */
SQLITE_DONE = 101, /* sqlite3_step() has finished executing */
SQLITE_UNK_RESULT = 999
}
//$ Conversion from int result to named return codes.
fun to_sqlite3_result_code: int -> sqlite3_result_codes =
|0 => SQLITE_OK
|1 => SQLITE_ERROR
|2 => SQLITE_INTERNAL
|3 => SQLITE_PERM
|4 => SQLITE_ABORT
|5 => SQLITE_BUSY
|6 => SQLITE_LOCKED
|7 => SQLITE_NOMEM
|8 => SQLITE_READONLY
|9 => SQLITE_INTERRUPT
|10 => SQLITE_IOERR
|11 => SQLITE_CORRUPT
|12 => SQLITE_NOTFOUND
|13 => SQLITE_FULL
|14 => SQLITE_CANTOPEN
|15 => SQLITE_PROTOCOL
|16 => SQLITE_EMPTY
|17 => SQLITE_SCHEMA
|18 => SQLITE_TOOBIG
|19 => SQLITE_CONSTRAINT
|20 => SQLITE_MISMATCH
|21 => SQLITE_MISUSE
|22 => SQLITE_NOLFS
|23 => SQLITE_AUTH
|24 => SQLITE_FORMAT
|25 => SQLITE_RANGE
|26 => SQLITE_NOTADB
|100 => SQLITE_ROW
|101 => SQLITE_DONE
| _ => SQLITE_UNK_RESULT;
//$ Tag names for Sqlite3 data types.
enum sqlite3_types {
SQLITE_INTEGER = 1,
SQLITE_FLOAT = 2,
SQLITE_TEXT = 3,
SQLITE_BLOB = 4,
SQLITE_NULL = 5,
SQLITE_UNK_TYPE = 999
}
instance Eq[sqlite3_result_codes] {
//$ Allow checking for specific return codes.
fun ==: sqlite3_result_codes * sqlite3_result_codes -> bool = "$1==$2";
}
open Eq[sqlite3_result_codes];
//$ Conversion from int type to named Sqlite3 data type.
fun to_sqlite3_type: int -> sqlite3_types =
|1 => SQLITE_INTEGER
|2 => SQLITE_FLOAT
|4 => SQLITE_BLOB
|5 => SQLITE_NULL
|3 => SQLITE_TEXT
| _ => SQLITE_UNK_TYPE;
//$ Prepare an sqlite3 statement for execution.
gen sqlite3_prepare_v2: sqlite3_db_t * string * int * sqlite3_stmt_t *string -> int =
"sqlite3_prepare_v2($1,$2.c_str(),$3,&$4,NULL)";
//$ Execute one step of the prepared statement.
noinline gen sqlite3_step: sqlite3_stmt_t -> int = "sqlite3_step($1)";
//$ Determine the number of columns (field) a statement will process.
gen sqlite3_column_count: sqlite3_stmt_t -> int = "sqlite3_column_count($1)";
//$ Determine the name of the n'th column to be processed.
gen sqlite3_column_name: sqlite3_stmt_t*int -> string = "sqlite3_column_name($1,$2)";
//$ Determine the type of the n'th column to be processed.
gen sqlite3_column_type: sqlite3_stmt_t*int->int = "sqlite3_column_type($1,$2)";
//$ Fetch the value of a text field.
gen sqlite3_column_text: sqlite3_stmt_t*int->string = "(char *)(sqlite3_column_text($1,$2))";
//$ Fetch the value of a double field.
gen sqlite3_column_double: sqlite3_stmt_t*int->double = "sqlite3_column_double($1,$2)";
//$ Fetch the value of a int field.
gen sqlite3_column_int: sqlite3_stmt_t*int->int = "sqlite3_column_int($1,$2)";
//$ Fetch the value of a blob field.
gen sqlite3_column_blob: sqlite3_stmt_t*int->&byte = "(unsigned char *)sqlite3_column_blob($1,$2)";
//$ Fetch the number of bytes of a field.
gen sqlite3_column_bytes: sqlite3_stmt_t*int -> int = "sqlite3_column_bytes($1,$2)";
//$ Finish up with stepping a statement.
//$ Releases associated resources.
//$ The statement handle becomes invalid afterwards.
gen sqlite3_finalize: sqlite3_stmt_t -> int = "sqlite3_finalize($1)";
//$ A unified type to fetch a field value.
variant column_value =
|int_val of int
|double_val of double
|text_val of string
|byte_val of int*&byte
|null_val;
//$ A unified result of a statement.
variant ret_val[t] =
|row of t
|row_fail of sqlite3_result_codes*string
|end_of_results;
//$ A unified result code.
variant result_code[t] =
| qry_ok of t
| qry_fail of sqlite3_result_codes*string;
//$ Unified preparation of a query.
fun sqlite3_prepare_stmt (db:sqlite3_db_t,query:string):result_code[sqlite3_stmt_t] = {
var stmt:sqlite3_stmt_t;
return match to_sqlite3_result_code ( sqlite3_prepare_v2(db, query, int(len query), stmt, "")) with
| #SQLITE_OK => qry_ok stmt
| c => qry_fail[sqlite3_stmt_t] (c,str(sqlite3_errmsg(db)))
endmatch;
}
//$ Fetch all the columns of a query at once.
//$ Return them in a darray.
fun sqlite3_get_columns (stmt:sqlite3_stmt_t):darray[column_value] = {
val n = sqlite3_column_count(stmt);
val results = darray[column_value]( size n,null_val);
for var i:int in 0 upto n - 1 do
var v = match to_sqlite3_type( sqlite3_column_type(stmt, i) ) with
| #SQLITE_TEXT => text_val (sqlite3_column_text(stmt, i))
| #SQLITE_INTEGER => int_val (sqlite3_column_int(stmt, i))
| #SQLITE_FLOAT => double_val (sqlite3_column_double(stmt, i))
| #SQLITE_BLOB => byte_val (sqlite3_column_bytes(stmt,i),
sqlite3_column_blob(stmt, i))
| #SQLITE_NULL => null_val
endmatch;
set(results,i,v );
done;
return results;
}
//$ A stream iterator which returns successive rows of a table.
gen sqlite3_row_iterator (stmt:sqlite3_stmt_t) () :ret_val[darray[column_value]] = {
again:>
var result_code = to_sqlite3_result_code$ sqlite3_step(stmt);
if result_code == SQLITE_BUSY do goto again; done;
match result_code with
| #SQLITE_DONE => {val p=sqlite3_finalize(stmt);}(); yield end_of_results[darray[column_value]];
| #SQLITE_ROW => yield ( row ( sqlite3_get_columns stmt) );
//| #SQLITE_BUSY => { Faio::sleep (Faio::sys_clock,0.05); goto again; end_of_results[darray[column_value]];}
| v => {val p=sqlite3_finalize stmt;}(); yield end_of_results[darray[column_value]];
endmatch;
goto again;
yield end_of_results[darray[column_value]];
}
//$ Get the int value out of a int typed field.
//$ Throws match failure if the field isn't an int type.
fun get_int_val: column_value->int = | int_val v => v;
//$ Get the double value out of a double typed field.
//$ Throws match failure if the field isn't a double type.
fun get_double_val: column_value->double = | double_val v => v;
//$ Get the text value out of a text typed field.
//$ Throws match failure if the field isn't a text type.
fun get_text_val: column_value->string = | text_val v => v;
//$ Get the statement handle out of a return code.
fun get_stmt: result_code[sqlite3_stmt_t]-> sqlite3_stmt_t = | qry_ok v => v;
//$ Get the next row from an row iterator.
gen get_next ( iter:()->ret_val[darray[column_value]],row:&ret_val[darray[column_value]]):bool = {
row <- iter();
return (match *row with
| #end_of_results =>false
| #row_fail =>false
| _ => true
endmatch);
}
//$ Execute an prepared statement.
gen sqlite3_execute (stmt:sqlite3_stmt_t) :bool = {
val v= match to_sqlite3_result_code$ sqlite3_step(stmt) with
| #SQLITE_BUSY => sqlite3_execute(stmt)
| #SQLITE_DONE => true
| _ => false
endmatch;
val n = sqlite3_finalize stmt;
return v;
}
header """
std::string sqlite3_quote_helper(const char *str) {
const char * val = sqlite3_mprintf("%q",str);
std::string ret = std::string(val);
sqlite3_free((char *)val);
return ret;
}
""";
//$ Quote a string for use in a query.
gen sqlite3_quote: string->string = "sqlite3_quote_helper($1.c_str())";
}