This module is a Forth DBMS driver to SQLite3. It implements a minimal interface for use with ForthQL and an extended interface for SQL3Lite specific operations.
This module is written in VFX Forth for Linux but it can probably be ported easily to other Forth environments as well.
The following aspects of VFX has been used:
0 Constant sql-multithread
Compilation option when including this module.
All DBMS drivers should test & define this value.
MODULE SQLITE3
0 Constant SQLITE_OK \ Successful result 1 Constant SQLITE_ERROR \ SQL error or missing database 2 Constant SQLITE_INTERNAL \ An internal logic error in SQLite 3 Constant SQLITE_PERM \ Access permission denied 4 Constant SQLITE_ABORT \ Callback routine requested an abort 5 Constant SQLITE_BUSY \ The database file is locked 6 Constant SQLITE_LOCKED \ A table in the database is locked 7 Constant SQLITE_NOMEM \ A malloc() failed 8 Constant SQLITE_READONLY \ Attempt to write a readonly database 9 Constant SQLITE_INTERRUPT \ Operation terminated by sqlite_interrupt() #10 Constant SQLITE_IOERR \ Some kind of disk I/O error occurred #11 Constant SQLITE_CORRUPT \ The database disk image is malformed #12 Constant SQLITE_NOTFOUND \ (Internal Only) Table or record not found #13 Constant SQLITE_FULL \ Insertion failed because database is full #14 Constant SQLITE_CANTOPEN \ Unable to open the database file #15 Constant SQLITE_PROTOCOL \ Database lock protocol error #16 Constant SQLITE_EMPTY \ (Internal Only) Database table is empty #17 Constant SQLITE_SCHEMA \ The database schema changed #18 Constant SQLITE_TOOBIG \ Too much data for one row of a table #19 Constant SQLITE_CONSTRAINT \ Abort due to contraint violation #20 Constant SQLITE_MISMATCH \ Data type mismatch #21 Constant SQLITE_MISUSE \ Library used incorrectly #22 Constant SQLITE_NOLFS \ Uses OS features not supported on host #23 Constant SQLITE_AUTH \ Authorization denied #100 Constant SQLITE_ROW \ sqlite_step() has another row ready #101 Constant SQLITE_DONE \ sqlite_step() has finished executing
1 Constant SQLITE_INTEGER 2 Constant SQLITE_FLOAT 3 Constant SQLITE_TEXT 4 Constant SQLITE_BLOB 5 Constant SQLITE_NULL
: .sql3-version
Print the SQLite3 version being loaded
The SQLite3 version number is an integer
with the value (X*1000000 + Y*1000 + Z).
ErrDef SQL3ColumnIndex "Index out of bounds in column widths array"
ErrDef SQL3ColumnWidthError "Column specifier not within range"
The ones below comes from the SQlite3 DBMS itself.
ErrDef SQL3_ERROR "SQLite3: SQL error or missing database" ErrDef SQL3_INTERNAL "SQLite3: An internal logic error in SQLite" ErrDef SQL3_PERM "SQLite3: Access permission denied" ErrDef SQL3_ABORT "SQLite3: Callback routine requested an abort" ErrDef SQL3_BUSY "SQLite3: The database file is locked" ErrDef SQL3_LOCKED "SQLite3: A table in the database is locked" ErrDef SQL3_NOMEM "SQLite3: A malloc() failed" ErrDef SQL3_READONLY "SQLite3: Attempt to write a readonly database" ErrDef SQL3_INTERRUPT "SQLite3: Operation terminated by sqlite_interrupt()" ErrDef SQL3_IOERR "SQLite3: Some kind of disk I/O error occurred" ErrDef SQL3_CORRUPT "SQLite3: The database disk image is malformed" ErrDef SQL3_NOTFOUND "SQLite3: (Internal Only) Table or record not found" ErrDef SQL3_FULL "SQLite3: Insertion failed because database is full" ErrDef SQL3_CANTOPEN "SQLite3: Unable to open the database file" ErrDef SQL3_PROTOCOL "SQLite3: Database lock protocol error" ErrDef SQL3_EMPTY "SQLite3: (Internal Only) Database table is empty" ErrDef SQL3_SCHEMA "SQLite3: The database schema changed" ErrDef SQL3_TOOBIG "SQLite3: Too much data for one row of a table" ErrDef SQL3_CONSTRAINT "SQLite3: Abort due to contraint violation" ErrDef SQL3_MISMATCH "SQLite3: Data type mismatch" ErrDef SQL3_MISUSE "SQLite3: Library used incorrectly" ErrDef SQL3_AUTH "SQLite3: Authorization denied"
: db-open \ ca1 u1 -- ior
Open a connection to a SQLite3 database file given by path ca1 u1.
Returned handle is stored in SQL3-HANDLE USER variable.
Does nothing if already open (SQL3-HANDLE is non-zero).
: db-close \ -- ior
Close a connection to a SQLite3 database. Handle is taken from
SQL3-HANDLE. Clears SQL3-HANDLE as well.
Does nothing if already closed (SQL3-HANDLE is zero).
: db-errmsg \ ior -- ca1 u1
Get an error description message ca1 u1 from an ior.
Warning: In SQLite3, ior is not looked up,
the most recent error produced is retrieved.
: db-execute \ z-addr -- ior
Execute SQL statement z-addr not returning any output.
: db-process \ z-addr xt -- ior
Execute SQL statement z-addr.
When complete, word given by xt is repetidely called,
row by row, to process output.
: db-process-with \ z-addr xt1 xt2 -- ior
Execute SQL statement z-addr.
When complete, word given by xt2 is repetidely called,
row by row, to process output.
ior code signals operation result.
xt1 is the execution token for a word that - when executed -
return a cell with client data.
Client data can be anything, but mostly will be an object handle
for OOP support (the this keyword).
<clientData> VALUE clientDataPtr \ xt1 is ' clientDataPtr.
: this ( -- handle) ... ; \ xt1 is ' this
: db-throw \ ior --
Map SQLite3 ior into an appropiate VFX Forth exception and throw it.
: db-print \ z-addr --
Print the SQL statement z-addr being executed and its result set
nicely formatted.
: SQLite3Callback: \ xt --
Declare a SQLite3 callback that, when called by the Linux C interface
it will execute in turn the action whose execution token is xt
The action Forth word must have a signature like its C counterpart:
int action(void* cliData, int nCols, char* colValue[], char* colName[]);
: sql3-$@ \ a-addr i -- ca u
Retrieve a string ca u given the base array of pointers
a-addr to Z-strings and the index into the array i.
Intended to retrieve contents from the callback Forth word being used
to process rows in the ForthQL. See SQLite3Callback:.
This API export function for a non callback driven interface. Iteration on the result set is done by the application itself.
: sql3-prepare \ z-addr1 -- z-addr2 ior
Precompiles a SQL statement z-addr1 for later execution through one
or more calls to SQL3-STEP.
Only a single SQL statement is compiled, remaining
is left as z-addr2 for subsequent calls to SQL3-PREPARE.
An empty statement set SQL3-STH to zero.
: sql3-step \ -- ior
Get next row from the result set.
ior is SQLITE_ROW if result set not complete or
SQLITE_DONE otherwise. A different value signals an error.
: sql3-reset \ -- ior
Reset the execution of a SQL statement.
: sql3-#cols \ -- n
Return the number of columns in the result set. Only works after
calling SQL3-PREPARE.
: sql3-finalize \ -- ior
Clean up the result set after being iterated.
Las thing to do after a SQL3-PREPARE and
zero or more SQL3-STEPs.
ior signals an error condition.
: sql3-NULL \ -- ca1 u1
The verbatim NULL string with its four characters.
: sql3-col-name \ n -- ca u
Return the column name for column n.
NULL datatype is retrieved as the NULL string.
: sql3-col-text \ n -- ca u
Return the column value as ASCII text for column n.
NULL datatype is retrieved as the NULL string.
: sql3-col-type \ n1 -- n2
Return the column type for column n1 as defined in one of
the SQLITE constants SQLITE_INTEGER through SQLITE_NULL.
: sql3-numeric? \ n1 -- flag
True if column type n1 is either SQLITE_INTEGER
or SQLITE_FLOAT.
40 Constant MAXWIDTH
Maximun character width for a given column.
32 Constant MAXCOLS
Maximun number of columns to pretty-print.
: th-colwidth \ n1 i --
Set the ith column width to n1.
Throw SQL3ColumnIndex if i out of bounds or
SQL3ColumnIndex if n1 is too wide.
: colwidth \ n1 --
Set all column widths to n1.
Throw SQL3ColumnIndex if n1 is too wide.
: +colwidth \ n1 --
Increment all column widths to n1.
Throw SQL3ColumnIndex if the resulting width is too wide.
SQLite3Callback: SQL-RESULT \ client* nCols colValues* colNames* -- ior
Print a query result set, like |SQL. but done as
|SQL> SQL-RESULT. Printed formatting has deficiencies.
Use |SQL. instead.