Re: SQLite and SwiftForth

Dear SFTalk,

thanks to everyone who responded with hints about using SQLite3 with
SF. I have now built a couple of simple SWOOP classes to encapsulate
the database and sql statement handling functions of the SQLite3
library. I copy it below in case anyone is interested. Obviously there
are other sqlite functions I could include, but this gets me going for
now.

Best,
Tony

{ ------------------------------------------------------------
    sqlite3
    SWOOP interface to the SQLite database library
  ------------------------------------------------------------ }

EMPTY
DECIMAL

\ SQLite return codes
0
ENUM SQLITE_OK \ Successful result
ENUM SQLITE_ERROR \ SQL error or missing database
ENUM SQLITE_INTERNAL \ An internal logic error in SQLite
ENUM SQLITE_PERM \ Access permission denied
ENUM SQLITE_ABORT \ Callback routine requested an abort
ENUM SQLITE_BUSY \ The database file is locked
ENUM SQLITE_LOCKED \ A table in the database is locked
ENUM SQLITE_NOMEM \ A malloc() failed
ENUM SQLITE_READONLY \ Attempt to write a readonly database
ENUM SQLITE_INTERRUPT \ Operation terminated by sqlite_interrupt()
ENUM SQLITE_IOERR \ Some kind of disk I/O error occurred
ENUM SQLITE_CORRUPT \ The database disk image is malformed
ENUM SQLITE_NOTFOUND \ (Internal Only) Table or record not found
ENUM SQLITE_FULL \ Insertion failed because database is full
ENUM SQLITE_CANTOPEN \ Unable to open the database file
ENUM SQLITE_PROTOCOL \ Database lock protocol error
ENUM SQLITE_EMPTY \ (Internal Only) Database table is empty
ENUM SQLITE_SCHEMA \ The database schema changed
ENUM SQLITE_TOOBIG \ Too much data for one row of a table
ENUM SQLITE_CONSTRAINT \ Abort due to contraint violation
ENUM SQLITE_MISMATCH \ Data type mismatch
ENUM SQLITE_MISUSE \ Library used incorrectly
ENUM SQLITE_NOLFS \ Uses OS features not supported on host
ENUM SQLITE_AUTH \ Authorization denied
DROP 100
ENUM SQLITE_ROW \ sqlite_step() has another row ready
ENUM SQLITE_DONE \ sqlite_step() has finished executing
DROP

\ SQLite3 data types
1
ENUM SQLITE_INTEGER
ENUM SQLITE_FLOAT
ENUM SQLITE_TEXT
ENUM SQLITE_BLOB
ENUM SQLITE_NULL
DROP

\ Import the SQLite C API functions
LIBRARY sqlite3.dll
1 CIMPORT: sqlite3_errmsg \ Return latest error message
1 CIMPORT: sqlite3_errcode \ Return the latest error code
2 CIMPORT: sqlite3_open \ Open a connection to a database
1 CIMPORT: sqlite3_close \ Close the database connection
5 CIMPORT: sqlite3_prepare \ Prepare an SQL statement
1 CIMPORT: sqlite3_step \ Execute a prepared SQL statement
1 CIMPORT: sqlite3_column_count \ No. of columns in a result set
2 CIMPORT: sqlite3_column_type \ Type of data in this column
2 CIMPORT: sqlite3_column_decltype \ Return text for declared type
2 CIMPORT: sqlite3_column_name \ Return the name of the column
2 CIMPORT: sqlite3_column_text \ Text in a column of a result set
1 CIMPORT: sqlite3_finalize \ Release a compiled SQL statement

{ Utility words }

\ Type out an ASCIIZ string
: ztype ( addr -- )
    DUP 0= IF ." NULL" ELSE ZCOUNT TYPE THEN ;

\ Create an ASCIIZ string from a counted string
: >ASCIIZ ( c-addr u -- addr )
    DUP 1+ ALLOCATE THROW \ Allocate space for the string
    DUP >R ZPLACE R> ; \ Copy the string into the buffer

\ The SQLDatabase class
\ Encapsulates behaviour of the database object in SQLite 3
CLASS SQLDatabase
    VARIABLE handle \ Handle to an open database
    MAX_PATH 1+ BUFFER: dbname \ Buffer for name of database file

    \ Display an error message if an error occurred
    : .message ( rc -- )
        SQLITE_OK <> IF
            handle @ :: sqlite3_errmsg
            :: ztype
        THEN ;

    \ Open the database whose name is passed
    : Open ( c-addr u -- rc )
        dbname ZPLACE \ Save the database name
        dbname handle :: sqlite3_open ; \ Open database/save handle

   \ Close the database
   : Close ( -- rc ) handle @ :: sqlite3_close ;

END-CLASS

\ The SQLStatement class
\ Encapsulates an SQL statement object in SQLite 3
CLASS SQLStatement
    VARIABLE dbh \ Database handle for this statement
    VARIABLE 'sql \ Pointer to statement
    VARIABLE 'remaining \ Text of uncompiled SQL
    VARIABLE sth \ Statement handle

    \ Prepare the statement
    : Prepare ( db c-addr u -- rc )
>ASCIIZ 'sql ! \ Save the SQL text
        ( db) -> handle @ dbh ! \ Save the database handle
        dbh @ 'sql @ -1 sth 'remaining :: sqlite3_prepare ;

    \ Execute the SQL statement once
    : Step ( -- rc ) sth @ :: sqlite3_step ;

    \ How many columns in a result set?
    : #cols ( -- n ) sth @ :: sqlite3_column_count ;

    \ Get the name of the nth column in a result set
    : col-name ( n -- addr ) sth @ SWAP :: sqlite3_column_name ;

    \ Get the type of the nth column in a result set
    : col-type ( n -- addr ) sth @ SWAP :: sqlite3_column_type ;

    \ Print the column type
    : .col-type ( col-type -- )
        CASE
            SQLITE_INTEGER OF ." INTEGER" ENDOF
            SQLITE_FLOAT OF ." FLOAT" ENDOF
            SQLITE_TEXT OF ." TEXT" ENDOF
            SQLITE_BLOB OF ." BLOB" ENDOF
            SQLITE_NULL OF ." NULL" ENDOF
            ( Default) ." UNKNOWN"
        ENDCASE ;

    \ Get the declared type of the nth column in a result set
    : col-declared-type ( n -- addr ) sth @ SWAP ::
            sqlite3_column_decltype ;

    \ Print a header row
    : .header ( -- )
        #cols 0 ?DO
            ." |" I col-name ztype SPACE
            ." [" I col-type .col-type SPACE
            ." (" I col-declared-type ztype ." )]"
        LOOP
        ." |" CR ;

    \ Get the text in the nth column of a result set
    : col-text ( n -- addr ) sth @ SWAP :: sqlite3_column_text ;

    \ Print a result row
    : .row ( -- )
        #cols 0 ?DO
            ." |" I col-text ztype SPACE
        LOOP
        ." |" CR ;

    \ Print all result sets
    : .rows ( -- )
        BEGIN
            Step SQLITE_ROW = WHILE
            .row
        REPEAT CR ;

    \ Finish with the statement
    : Finalize ( -- rc )
        sth @ :: sqlite3_finalize
        'sql @ FREE THROW ;

END-CLASS

{ ------------------------------------------------------------
    Test these classes
  ------------------------------------------------------------ }

SQLDatabase BUILDS db
SQLStatement BUILDS stmt

S" test.db" db Open db .message CR
db S" SELECT * FROM tbl1" stmt Prepare db .message CR
stmt .header
stmt .rows
stmt Finalize db .message CR
db Close DROP

----------------------------------------------------------------------
sftalk_at_forth.com The SwiftForth programming discussion email list
To unsubscribe, send subject "unsubscribe" to sftalk-request_at_forth.com
For list command help, send subject "help" to sftalk-request_at_forth.com
Message archives are located at http://www.forth.com/archive/sftalk
----------------------------------------------------------------------
This list is a forum for SwiftForth users. For product support and
bug reports, please send email to support_at_forth.com
----------------------------------------------------------------------
Received on Tue Oct 09 2007 - 12:25:17 PDT