'
' Simple SQLite3 wrapper - PvE May 2010, GPL.
'
' SQL syntax here: http://www.sqlite.org/lang.html
'
' Version 1.0: initial release
' Version 1.1: fixed compile warning in DB_ERR$
' Version 1.2: adjustments for including separate functions
'---------------------------------------------------------------------------------------------------------

CALL INIT

REM --------------------------------------------------------------------------------------------------

SUB INIT

    TRAP LOCAL
    CATCH GOTO sqlite_handle_error

    sqlite_LIB$ = "libsqlite3.so"
    sqlite_seq = -1

    LABEL sqlite_import_retry
        INCR sqlite_seq
        sqlite_retry = FALSE
        IMPORT "sqlite3_open(char*,long)" FROM sqlite_LIB$ TYPE int
        IF sqlite_retry THEN GOTO sqlite_import_retry

    GOTO sqlite_continue

    LABEL sqlite_handle_error
        IF sqlite_seq IS 0 THEN sqlite_LIB$ = "libsqlite3.so.0"
        ELSE sqlite_LIB$ = CONCAT$(LEFT$(sqlite_LIB$, INSTRREV(sqlite_LIB$, ".")), STR$(sqlite_seq))
        IF sqlite_seq < 10 THEN sqlite_retry = TRUE
        ELSE
            PRINT "No SQLite library found!"
            END
        END IF
        RESUME

    LABEL sqlite_continue

    CATCH GOTO sqlite_lookup_error

    IMPORT "sqlite3_close(long)" FROM sqlite_LIB$ TYPE int
    IMPORT "sqlite3_errmsg(long)" FROM sqlite_LIB$ TYPE char*
    IMPORT "sqlite3_exec(long,char*,void*,void*,long)" FROM sqlite_LIB$ TYPE int
    IMPORT "sqlite3_free(void*)" FROM sqlite_LIB$ TYPE void
    IMPORT "sqlite3_libversion(void)" FROM sqlite_LIB$ TYPE char*

    GOTO Continue_Sub

    LABEL sqlite_lookup_error
        PRINT ERR$(ERROR)
        END

    LABEL Continue_Sub
        CATCH RESET
        TRAP SYSTEM

    DECLARE DB_FS$, DB_RS$, DB_RESULT$, DB_ERROR$
    DECLARE sqlite_internal_flag

    CONST SQLITE_OK = 0

    DB_FS$ = TAB$(1)
    DB_RS$ = NL$

END SUB

'---------------------------------------------------------------------------------------------------------

FUNCTION DB_OPEN(STRING name)

    LOCAL db, result

    result = sqlite3_open(name, ADDRESS(db))

    IF result THEN RETURN result

    RETURN db

END FUNCTION

'---------------------------------------------------------------------------------------------------------

FUNCTION sqlite_results(void *NotUsed, int amount, char **value, char **column_name) : REM INCLUDE

    LOCAL i

    IF sqlite_internal_flag AND amount > 0 THEN
        DB_RESULT$ = ""
        FOR i = 0 TO amount - 1
            DB_RESULT$ = CONCAT$(DB_RESULT$, column_name[i], DB_FS$)
        NEXT
        DB_RESULT$ = CONCAT$(DB_RESULT$, DB_RS$)
        sqlite_internal_flag = FALSE
    ENDIF

    IF amount > 0 THEN
        FOR i = 0 TO amount - 1
            DB_RESULT$ = CONCAT$(DB_RESULT$, value[i], DB_FS$)
        NEXT
        DB_RESULT$ = CONCAT$(DB_RESULT$, DB_RS$)
    END IF

    RETURN 0

END FUNCTION

'---------------------------------------------------------------------------------------------------------

FUNCTION DB_SQL(NUMBER db, STRING sql$)

    LOCAL result TYPE int
    LOCAL err_msg TYPE char*

    DB_ERROR$ = ""
    sqlite_internal_flag = TRUE
    result = sqlite3_exec(db, sql$, sqlite_results, 0, ADDRESS(err_msg))

    IF result NE SQLITE_OK THEN
        DB_ERROR$ = err_msg
        sqlite3_free(err_msg)
    END IF

    RETURN result

END FUNCTION

'---------------------------------------------------------------------------------------------------------

FUNCTION DB_CLOSE(NUMBER db)

    LOCAL result TYPE int

    result = sqlite3_close(db)

    RETURN result

END FUNCTION

'---------------------------------------------------------------------------------------------------------

FUNCTION DB_STATUS$(NUMBER db)

    LOCAL status$

    status$ = sqlite3_errmsg(db)

    RETURN status$

END FUNCTION

'---------------------------------------------------------------------------------------------------------

FUNCTION DB_VERSION$()

    LOCAL version$

    version$ = sqlite3_libversion()

    RETURN version$

END FUNCTION

'---------------------------------------------------------------------------------------------------------

TRAP SYSTEM
CATCH RESET