'
' Simple SQLite3 wrapper - PvE May 2010, GPL.
'
' SQL syntax here: http://www.sqlite.org/lang.html
'---------------------------------------------------------------------------------------------------------

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

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*

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

CONST SQLITE_OK = 0

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

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

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)

    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