SQLite module for Squirrel.

Started by ., Mar 28, 2015, 04:26 AM

Previous topic - Next topic

.

Well, as promised I looked into the current SQLite module to see if I can add proper error reporting. Because a silent error doesn't really help anyone. Neither the script-writer or the people trying to help here on the forum. Try to make a mistake in the query syntax or try to break it somehow so that I know it's working properly. Please expect some bugs because I did this tonight as a fun project and I haven't done much testing with it.

These are the available functions:
function [userpointer database_handle]
    SQLite_Open(string *database_path, integer flags, string vfs);
function [bool status]
    SQLite_Exec(userpointer *database_handle, string *query_string);
function [userpointer statement_handle]
    SQLite_Query(userpointer *database_handle, string *query_string);
function [bool status]
    SQLite_Close(userpointer *database_handle);
function [integer error_code]
    SQLite_ErrCode(userpointer *database_handle);
function [integer error_code]
    SQLite_ExtendedErrCode(userpointer *database_handle);
function [string error_message]
    SQLite_ErrMsg(userpointer *database_handle);
function [string error_message]
    SQLite_ErrStr(integer *error_code);
function [bool status]
    SQLite_ToggleExtendedErrCode(*database_handle, bool *toggle);
function [bool status]
    SQLite_NextRow(userpointer *statement_handle);
function [mixed column_data]
    SQLite_ColumnData(userpointer *statement_handle, integer *column_index);
function [integer column_count]
    SQLite_ColumnCount(userpointer *statement_handle);
function [string column_name]
    SQLite_ColumnName(userpointer *statement_handle, integer *column_index);
function [string column_decltype]
    SQLite_ColumnDecltype(userpointer *statement_handle, integer *column_index);
function [integer column_data_size]
    SQLite_ColumnBytes(userpointer *statement_handle, integer *column_index);
function [bool status]
    SQLite_Reset(userpointer *statement_handle);
function [null]
    SQLite_Release(userpointer *statement_handle);

Note: Arguments marked with * the beginning are required while the rest are optional. On the left is their data type. At the beginning of the function name between [...] is the return type and it's value.

An example of the usage in script can be found here:
// NOTE: Most of these functions throw an exception in case of failure
//    so should use a try/catch block to catch them and respond

// Open a temporary database in memory.
local db = SQLite_Open(":memory:");

// Enable extended error codes
SQLite_ToggleExtendedErrCode(db, true);

// Execute a set of queries without expecting any results.
SQLite_Exec(db, @"CREATE TABLE IF NOT EXISTS [players] (
    [id] INTEGER  PRIMARY KEY NOT NULL,
    [name] VARCHAR(64)  UNIQUE NOT NULL,
    [kills] INTEGER DEFAULT '0' NULL,
    [deaths] INTEGER DEFAULT '0' NULL,
    [class] INTEGER DEFAULT '0' NULL
);");

// The exec function doesn't return anything. Which makes it perfect
//    for quick INSERT, UPDATE, DELETE operations
// NOTE: All of the following queries could be executed in one call
//    But for the sake of this example they aren't
SQLite_Exec(db, @"INSERT INTO
[players] (id, name, kills, deaths, class)
VALUES (0, 'player0', 213, 33, 3);");

SQLite_Exec(db, @"INSERT INTO
[players] (id, name, kills, deaths, class)
VALUES (1, 'player1', 42, 2, 2);");

SQLite_Exec(db, @"INSERT INTO
[players] (id, name, kills, deaths, class)
VALUES (2, 'player2', 432, 435, 2);");

SQLite_Exec(db, @"INSERT INTO
[players] (id, name, kills, deaths, class)
VALUES (3, 'player3', 54, 43, 1);");

// The query function returns a statement. This is perfect for SELECT
//    operations which involve returning data from the database
local result = SQLite_Query(db, "SELECT * FROM players");

print("---- First Iteration ----");

// NOTE: The result is already initialized to the first row,
//    so it's best used with a do/while loop
do {
    // Get the number of columns
    local column_count = SQLite_ColumnCount(result);
   
    print("column count " + column_count + "\n");
   
    // Print information about each column
    for (local i = 0; i < column_count; i++)
    {
        print("column " + i);
   
        // Print the name of the column
        print("column name " + SQLite_ColumnName(result, i));
        // Print the value inside the column
        print("column data " + SQLite_ColumnData(result, i));
        // Print the declaration type of the column
        print("column decltype " + SQLite_ColumnDecltype(result, i));
        // Print the size of the data inside the column
        print("column bytes " + SQLite_ColumnBytes(result, i));
   
        print("\n");
    }

// The nextrow function returns false if reaches the end,
//   which automatically breaks the loop
} while (SQLite_NextRow(result))

print("---- Second Iteration ----");

// Reset the previous result so we can print it again
SQLite_Reset(result);

// NOTE: The result is already initialized to the first row,
//    so it's best used with a do/while loop
do {
    // Get the number of columns
    local column_count = SQLite_ColumnCount(result);

    // Print value inside each column
    for (local i = 0; i < column_count; i++)
    {
        // Print the value inside the column
        print("column data " + SQLite_ColumnData(result, i));

    }
    print("\n");

// The nextrow function returns false if reaches the end,
//   which automatically breaks the loop
} while (SQLite_NextRow(result))

// Let's clean the result
SQLite_Release(result);

// Close the database handle
SQLite_Close(db);

Note: Most of these functions throw exceptions in case of errors. That's why you should use them in try/catch blocks if you wish to know when they fail. Otherwise you'll get a nice error message detailing the cause of the failure along with the function and line where that happened.

You can also use the provided functions to manually extract the error codes and get the message manually.

This is a list of available constants (all integers):
SQLITE_ABORT
SQLITE_ABORT_ROLLBACK
SQLITE_ACCESS_EXISTS
SQLITE_ACCESS_READ
SQLITE_ACCESS_READWRITE
SQLITE_ALTER_TABLE
SQLITE_ANALYZE
SQLITE_ANY
SQLITE_ATTACH
SQLITE_AUTH
SQLITE_AUTH_USER
SQLITE_BLOB
SQLITE_BUSY
SQLITE_BUSY_RECOVERY
SQLITE_BUSY_SNAPSHOT
SQLITE_CANTOPEN
SQLITE_CANTOPEN_CONVPATH
SQLITE_CANTOPEN_FULLPATH
SQLITE_CANTOPEN_ISDIR
SQLITE_CANTOPEN_NOTEMPDIR
SQLITE_CHECKPOINT_FULL
SQLITE_CHECKPOINT_PASSIVE
SQLITE_CHECKPOINT_RESTART
SQLITE_CHECKPOINT_TRUNCATE
SQLITE_CONFIG_COVERING_INDEX_SCAN
SQLITE_CONFIG_GETMALLOC
SQLITE_CONFIG_GETMUTEX
SQLITE_CONFIG_GETPCACHE
SQLITE_CONFIG_GETPCACHE2
SQLITE_CONFIG_HEAP
SQLITE_CONFIG_LOG
SQLITE_CONFIG_LOOKASIDE
SQLITE_CONFIG_MALLOC
SQLITE_CONFIG_MEMSTATUS
SQLITE_CONFIG_MMAP_SIZE
SQLITE_CONFIG_MULTITHREAD
SQLITE_CONFIG_MUTEX
SQLITE_CONFIG_PAGECACHE
SQLITE_CONFIG_PCACHE
SQLITE_CONFIG_PCACHE2
SQLITE_CONFIG_PCACHE_HDRSZ
SQLITE_CONFIG_PMASZ
SQLITE_CONFIG_SCRATCH
SQLITE_CONFIG_SERIALIZED
SQLITE_CONFIG_SINGLETHREAD
SQLITE_CONFIG_SQLLOG
SQLITE_CONFIG_URI
SQLITE_CONFIG_WIN32_HEAPSIZE
SQLITE_CONSTRAINT
SQLITE_CONSTRAINT_CHECK
SQLITE_CONSTRAINT_COMMITHOOK
SQLITE_CONSTRAINT_FOREIGNKEY
SQLITE_CONSTRAINT_FUNCTION
SQLITE_CONSTRAINT_NOTNULL
SQLITE_CONSTRAINT_PRIMARYKEY
SQLITE_CONSTRAINT_ROWID
SQLITE_CONSTRAINT_TRIGGER
SQLITE_CONSTRAINT_UNIQUE
SQLITE_CONSTRAINT_VTAB
SQLITE_COPY
SQLITE_CORRUPT
SQLITE_CORRUPT_VTAB
SQLITE_CREATE_INDEX
SQLITE_CREATE_TABLE
SQLITE_CREATE_TEMP_INDEX
SQLITE_CREATE_TEMP_TABLE
SQLITE_CREATE_TEMP_TRIGGER
SQLITE_CREATE_TEMP_VIEW
SQLITE_CREATE_TRIGGER
SQLITE_CREATE_VIEW
SQLITE_CREATE_VTABLE
SQLITE_DBCONFIG_ENABLE_FKEY
SQLITE_DBCONFIG_ENABLE_TRIGGER
SQLITE_DBCONFIG_LOOKASIDE
SQLITE_DBSTATUS_CACHE_HIT
SQLITE_DBSTATUS_CACHE_MISS
SQLITE_DBSTATUS_CACHE_USED
SQLITE_DBSTATUS_CACHE_WRITE
SQLITE_DBSTATUS_DEFERRED_FKS
SQLITE_DBSTATUS_LOOKASIDE_HIT
SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL
SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE
SQLITE_DBSTATUS_LOOKASIDE_USED
SQLITE_DBSTATUS_MAX
SQLITE_DBSTATUS_SCHEMA_USED
SQLITE_DBSTATUS_STMT_USED
SQLITE_DELETE
SQLITE_DENY
SQLITE_DETACH
SQLITE_DETERMINISTIC
SQLITE_DONE
SQLITE_DROP_INDEX
SQLITE_DROP_TABLE
SQLITE_DROP_TEMP_INDEX
SQLITE_DROP_TEMP_TABLE
SQLITE_DROP_TEMP_TRIGGER
SQLITE_DROP_TEMP_VIEW
SQLITE_DROP_TRIGGER
SQLITE_DROP_VIEW
SQLITE_DROP_VTABLE
SQLITE_EMPTY
SQLITE_ERROR
SQLITE_FAIL
SQLITE_FCNTL_BUSYHANDLER
SQLITE_FCNTL_CHUNK_SIZE
SQLITE_FCNTL_COMMIT_PHASETWO
SQLITE_FCNTL_FILE_POINTER
SQLITE_FCNTL_HAS_MOVED
SQLITE_FCNTL_LOCKSTATE
SQLITE_FCNTL_MMAP_SIZE
SQLITE_FCNTL_OVERWRITE
SQLITE_FCNTL_PERSIST_WAL
SQLITE_FCNTL_POWERSAFE_OVERWRITE
SQLITE_FCNTL_PRAGMA
SQLITE_FCNTL_SIZE_HINT
SQLITE_FCNTL_SYNC
SQLITE_FCNTL_SYNC_OMITTED
SQLITE_FCNTL_TEMPFILENAME
SQLITE_FCNTL_TRACE
SQLITE_FCNTL_VFSNAME
SQLITE_FCNTL_WIN32_AV_RETRY
SQLITE_FCNTL_WIN32_SET_HANDLE
SQLITE_FLOAT
SQLITE_FORMAT
SQLITE_FULL
SQLITE_FUNCTION
SQLITE_GET_LOCKPROXYFILE
SQLITE_IGNORE
SQLITE_INDEX_CONSTRAINT_EQ
SQLITE_INDEX_CONSTRAINT_GE
SQLITE_INDEX_CONSTRAINT_GT
SQLITE_INDEX_CONSTRAINT_LE
SQLITE_INDEX_CONSTRAINT_LT
SQLITE_INDEX_CONSTRAINT_MATCH
SQLITE_INSERT
SQLITE_INTEGER
SQLITE_INTERNAL
SQLITE_INTERRUPT
SQLITE_IOCAP_ATOMIC
SQLITE_IOCAP_ATOMIC16K
SQLITE_IOCAP_ATOMIC1K
SQLITE_IOCAP_ATOMIC2K
SQLITE_IOCAP_ATOMIC32K
SQLITE_IOCAP_ATOMIC4K
SQLITE_IOCAP_ATOMIC512
SQLITE_IOCAP_ATOMIC64K
SQLITE_IOCAP_ATOMIC8K
SQLITE_IOCAP_IMMUTABLE
SQLITE_IOCAP_POWERSAFE_OVERWRITE
SQLITE_IOCAP_SAFE_APPEND
SQLITE_IOCAP_SEQUENTIAL
SQLITE_IOCAP_UNDELETABLE_WHEN_OPEN
SQLITE_IOERR
SQLITE_IOERR_ACCESS
SQLITE_IOERR_BLOCKED
SQLITE_IOERR_CHECKRESERVEDLOCK
SQLITE_IOERR_CLOSE
SQLITE_IOERR_CONVPATH
SQLITE_IOERR_DELETE
SQLITE_IOERR_DELETE_NOENT
SQLITE_IOERR_DIR_CLOSE
SQLITE_IOERR_DIR_FSYNC
SQLITE_IOERR_FSTAT
SQLITE_IOERR_FSYNC
SQLITE_IOERR_GETTEMPPATH
SQLITE_IOERR_LOCK
SQLITE_IOERR_MMAP
SQLITE_IOERR_NOMEM
SQLITE_IOERR_RDLOCK
SQLITE_IOERR_READ
SQLITE_IOERR_SEEK
SQLITE_IOERR_SHMLOCK
SQLITE_IOERR_SHMMAP
SQLITE_IOERR_SHMOPEN
SQLITE_IOERR_SHMSIZE
SQLITE_IOERR_SHORT_READ
SQLITE_IOERR_TRUNCATE
SQLITE_IOERR_UNLOCK
SQLITE_IOERR_WRITE
SQLITE_LAST_ERRNO
SQLITE_LIMIT_ATTACHED
SQLITE_LIMIT_COLUMN
SQLITE_LIMIT_COMPOUND_SELECT
SQLITE_LIMIT_EXPR_DEPTH
SQLITE_LIMIT_FUNCTION_ARG
SQLITE_LIMIT_LENGTH
SQLITE_LIMIT_LIKE_PATTERN_LENGTH
SQLITE_LIMIT_SQL_LENGTH
SQLITE_LIMIT_TRIGGER_DEPTH
SQLITE_LIMIT_VARIABLE_NUMBER
SQLITE_LIMIT_VDBE_OP
SQLITE_LIMIT_WORKER_THREADS
SQLITE_LOCKED
SQLITE_LOCKED_SHAREDCACHE
SQLITE_LOCK_EXCLUSIVE
SQLITE_LOCK_NONE
SQLITE_LOCK_PENDING
SQLITE_LOCK_RESERVED
SQLITE_LOCK_SHARED
SQLITE_MISMATCH
SQLITE_MISUSE
SQLITE_MUTEX_FAST
SQLITE_MUTEX_RECURSIVE
SQLITE_MUTEX_STATIC_APP1
SQLITE_MUTEX_STATIC_APP2
SQLITE_MUTEX_STATIC_APP3
SQLITE_MUTEX_STATIC_LRU
SQLITE_MUTEX_STATIC_LRU2
SQLITE_MUTEX_STATIC_MASTER
SQLITE_MUTEX_STATIC_MEM
SQLITE_MUTEX_STATIC_MEM2
SQLITE_MUTEX_STATIC_OPEN
SQLITE_MUTEX_STATIC_PMEM
SQLITE_MUTEX_STATIC_PRNG
SQLITE_NOLFS
SQLITE_NOMEM
SQLITE_NOTADB
SQLITE_NOTFOUND
SQLITE_NOTICE
SQLITE_NOTICE_RECOVER_ROLLBACK
SQLITE_NOTICE_RECOVER_WAL
SQLITE_NULL
SQLITE_OK
SQLITE_OPEN_AUTOPROXY
SQLITE_OPEN_CREATE
SQLITE_OPEN_DELETEONCLOSE
SQLITE_OPEN_EXCLUSIVE
SQLITE_OPEN_FULLMUTEX
SQLITE_OPEN_MAIN_DB
SQLITE_OPEN_MAIN_JOURNAL
SQLITE_OPEN_MASTER_JOURNAL
SQLITE_OPEN_MEMORY
SQLITE_OPEN_NOMUTEX
SQLITE_OPEN_PRIVATECACHE
SQLITE_OPEN_READONLY
SQLITE_OPEN_READWRITE
SQLITE_OPEN_SHAREDCACHE
SQLITE_OPEN_SUBJOURNAL
SQLITE_OPEN_TEMP_DB
SQLITE_OPEN_TEMP_JOURNAL
SQLITE_OPEN_TRANSIENT_DB
SQLITE_OPEN_URI
SQLITE_OPEN_WAL
SQLITE_PERM
SQLITE_PRAGMA
SQLITE_PROTOCOL
SQLITE_RANGE
SQLITE_READ
SQLITE_READONLY
SQLITE_READONLY_CANTLOCK
SQLITE_READONLY_DBMOVED
SQLITE_READONLY_RECOVERY
SQLITE_READONLY_ROLLBACK
SQLITE_RECURSIVE
SQLITE_REINDEX
SQLITE_REPLACE
SQLITE_ROLLBACK
SQLITE_ROW
SQLITE_SAVEPOINT
SQLITE_SCANSTAT_EST
SQLITE_SCANSTAT_EXPLAIN
SQLITE_SCANSTAT_NAME
SQLITE_SCANSTAT_NLOOP
SQLITE_SCANSTAT_NVISIT
SQLITE_SCANSTAT_SELECTID
SQLITE_SCHEMA
SQLITE_SELECT
SQLITE_SET_LOCKPROXYFILE
SQLITE_SHM_EXCLUSIVE
SQLITE_SHM_LOCK
SQLITE_SHM_NLOCK
SQLITE_SHM_SHARED
SQLITE_SHM_UNLOCK
SQLITE_STATUS_MALLOC_COUNT
SQLITE_STATUS_MALLOC_SIZE
SQLITE_STATUS_MEMORY_USED
SQLITE_STATUS_PAGECACHE_OVERFLOW
SQLITE_STATUS_PAGECACHE_SIZE
SQLITE_STATUS_PAGECACHE_USED
SQLITE_STATUS_PARSER_STACK
SQLITE_STATUS_SCRATCH_OVERFLOW
SQLITE_STATUS_SCRATCH_SIZE
SQLITE_STATUS_SCRATCH_USED
SQLITE_STMTSTATUS_AUTOINDEX
SQLITE_STMTSTATUS_FULLSCAN_STEP
SQLITE_STMTSTATUS_SORT
SQLITE_STMTSTATUS_VM_STEP
SQLITE_SYNC_DATAONLY
SQLITE_SYNC_FULL
SQLITE_SYNC_NORMAL
SQLITE_TESTCTRL_ALWAYS
SQLITE_TESTCTRL_ASSERT
SQLITE_TESTCTRL_BENIGN_MALLOC_HOOKS
SQLITE_TESTCTRL_BITVEC_TEST
SQLITE_TESTCTRL_BYTEORDER
SQLITE_TESTCTRL_EXPLAIN_STMT
SQLITE_TESTCTRL_FAULT_INSTALL
SQLITE_TESTCTRL_FIRST
SQLITE_TESTCTRL_ISINIT
SQLITE_TESTCTRL_ISKEYWORD
SQLITE_TESTCTRL_LAST
SQLITE_TESTCTRL_LOCALTIME_FAULT
SQLITE_TESTCTRL_NEVER_CORRUPT
SQLITE_TESTCTRL_OPTIMIZATIONS
SQLITE_TESTCTRL_PENDING_BYTE
SQLITE_TESTCTRL_PRNG_RESET
SQLITE_TESTCTRL_PRNG_RESTORE
SQLITE_TESTCTRL_PRNG_SAVE
SQLITE_TESTCTRL_RESERVE
SQLITE_TESTCTRL_SCRATCHMALLOC
SQLITE_TESTCTRL_SORTER_MMAP
SQLITE_TESTCTRL_VDBE_COVERAGE
SQLITE_TEXT
SQLITE_TOOBIG
SQLITE_TRANSACTION
SQLITE_UPDATE
SQLITE_UTF16
SQLITE_UTF16BE
SQLITE_UTF16LE
SQLITE_UTF16_ALIGNED
SQLITE_UTF8
SQLITE_VERSION_NUMBER
SQLITE_VTAB_CONSTRAINT_SUPPORT
SQLITE_WARNING
SQLITE_WARNING_AUTOINDEX

The source can be downloaded here. In order to use it you'll need the original module and all you need to do is overwrite the files in the original module with the ones provided in this source and then compile. For any issues please post here and I'll gladly look into it.

This isn't meant to replace the original. This is just a small upgrade to add some features that would help the script-writer find bugs in their code much more easily.



The source along with binaries are now available here. The binaries can be found in the sub-folder /bin/ for all supported platforms (Windows x32/x64 and Linux x32/x64). The binaries were compiled using GCC 4.9.2 on all platforms and architectures. The source is also available in the same archive.

SIDE-NOTE: In case your scripts end up being executed multiple times then add the sqlite module before the squirrel module in server.cfg.
.

DizzasTeR

So you finally finished it SLC, it seems to be making database working abit more friendly but it will take some time to get used to it.

.

Quote from: Doom_Killer on Mar 28, 2015, 05:03 AMit will take some time to get used to it.

It's the same thing. Only the function names are different to avoid conflicts. And whenever there's an error in the query or passing the wrong parameters, the functions will throw an exception to. If you don't catch that exception in a try catch block then you'll get a nice message explaining why that error was thrown and the line number and function that caused that.
.

DizzasTeR

Quote from: S.L.C on Mar 28, 2015, 03:38 PM
Quote from: Doom_Killer on Mar 28, 2015, 05:03 AMit will take some time to get used to it.

It's the same thing. Only the function names are different to avoid conflicts. And whenever there's an error in the query or passing the wrong parameters, the functions will throw an exception to. If you don't catch that exception in a try catch block then you'll get a nice message explaining why that error was thrown and the line number and function that caused that.

Awesome ;)

.

UPDATE: Binaries and source are now available in the first post (at the bottom).
.

.

#5
UPDATE: Source is now available on Github along with the binaries.

What's changed:
  • Fixed a minor issue in SQLite_Exec(...) that could result in a thrown error, regardless of the result.
  • Updated to the latest server SDK header.

Only Windows binaries at the moment. I'm currently setting a VM to build Linux binaries as well. Should be available by tonight if everything goes smoothly.
.

.

#6
As a bonus to anyone trying to use the module and likes how PHP allows you to easily iterate results. I decided to share a small wrapper for the module functionality. I had it sitting around and I know how much it sucks to have to work with indexes whenever you want your values.

The wrapper from file "Database.nut" depends on another file called "Message.nut". Both files can be found in separate posts after this because I'm limited to the number of characters I can use in a single post.

Here's a simple script to demonstrate the use of the wrapper:
// Database needs Message and we need Database
dofile("Message.nut", true);
dofile("Database.nut", true);

_Log.Msg(@"--------------------------------");

// Only one database can be used at a time.
if (!_DB.Open(":memory:"))
{
    throw "Cannot start without the database handle";
}

// Add some dummy data
{
    // Feed the random generator
    srand((GetTickCount() % time()) / 3);

    // Temporary function to generate some random passwords for the sake of this example
    local GenPassword = function()
    {
        return ::format(@"%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c",
            ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48,
            ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48,
            ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48,
            ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48,
            ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48,
            ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48,
            ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48,
            ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48, ::rand() % 10 + 48
        );
    }

    // Since we're not expecting any values to return. We use the exec method.
    if (!_DB.Exec(@"CREATE TABLE IF NOT EXISTS [Account] (
        [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        [User] VARCHAR(64)  UNIQUE NOT NULL,
        [Pass] VARCHAR(32)  NOT NULL,
        [Level] NUMERIC DEFAULT '0' NULL,
        [Points] NUMERIC DEFAULT '0' NULL,
        [Wealth] NUMERIC DEFAULT '0' NULL
    )
    ")) throw "Unable to initialize the table structure";

    // Dummy list of user names
    local usernames = ["olBoy", "HYper", "cr@ckHead", "yoMAMA", "FUNOOB", "LAsTOne"];

    // Temporary variable in which all queries are bulked together
    local queries = "";

    // Generate the random data
    foreach (name in usernames)
    {
        queries += ::format(@"INSERT INTO [Account] ([User], [Pass], [Level], [Points], [Wealth])
                VALUES ('%s', '%s', %d, %d, %d);",
                _DB.Escape(name), _DB.Escape(GenPassword()), rand(), rand(), rand());
    }

    // Execute all queries at once using the exec method.
    if (!_DB.Exec(queries)) throw "Unable to insert the dummy data";
}

{
    // Retrieve some data from the database. Since we're expecting data, we use the query method.
    // And to make things easier, we use pass the result to an new instance of the Statment class.
    local result =  _DB.Statement(_DB.Query("SELECT * FROM [Account]"));

    // Print the available columns
    _Log.Inf(@"Successfully selected %d columns", result.Count());

    _Log.Msg(@"--------------------------------");

    // Let's use the awesome Statement instance to iterate our data one row at a time
    foreach (row in result)
    {
        // "row" is now a table with the values from the current row.
        //  the column name is the key and the value is the data in that cell
        _Log.Msg(@"ID:      %d", row["ID"]);
        _Log.Msg(@"User:    %s", row["User"]);
        _Log.Msg(@"Pass:    %s", row["Pass"]);
        _Log.Msg(@"Level:   %d", row["Level"]);
        _Log.Msg(@"Points:  %d", row["Points"]);
        _Log.Msg(@"Wealth:  %d", row["Wealth"]);

        _Log.Msg(@"--------------------------------");
    }

    // Tell the statement instance to release the statement resources
    result.Release();

    // Everything else is cleaned when we leave this context
}

Save it as some_script.nut and load it in your server.cfg to test it. Please note that not all functionality is covered. This is just to confirm that the module and wrapper are working.
.

.

#7
Save this code in a script named "Database.nut" :
// ------------------------------------------------------------------------------------------------
_DB <- {
    // --------------------------------------------------------------------------------------------
    m_Con = null
    m_Path = ""

    // --------------------------------------------------------------------------------------------
    m_Query = ""

    // --------------------------------------------------------------------------------------------
    function Path()
    {
        return m_path;
    }

    // --------------------------------------------------------------------------------------------
    function Handle()
    {
        return m_Con;
    }

    // --------------------------------------------------------------------------------------------
    function Open(filename)
    {
        if (m_Con != null) {
            ::_Log.Wrn(@"_DB.Open: Another database connection is already opened");
            return true;
        }

        if (typeof filename != "string") {
            ::_Log.Ftl(@"_DB.Open: Cannot open database without a valid path");
            ::_Log.Inf(@"=> Expected: (string). Got: (%s)", typeof filename);

            return false;
        } else if (filename.len() <= 0) {
            ::_Log.Ftl(@"_DB.Open: Cannot open database without a valid path");
            ::_Log.Inf(@"=> Specified string size was: %d", filename.len());

            return false;
        } else m_Path = filename;

        try {
            m_Con = ::SQLite_Open(m_Path);
        } catch (e) {
            ::_Log.Ftl(@"_DB.Open: Unable to open or create database: %s", m_Path);
            ::_Log.Inf(@"=> Reason: %s", e);

            return false;
        }

        if (m_Con != null) {
            ::_Log.Scs(@"Succesfully opened connection to database: %s", m_Path);

            return true;
        } else {
            ::_Log.Scs(@"Unable to open connection to database: %s", m_Path);

            return true;
        }
    }

    // --------------------------------------------------------------------------------------------
    function Close()
    {
        if (m_Con == null) {
            ::_Log.Err(@"_DB.Close: Unable to locate any existing database connection");

            return false;
        } else if (typeof m_Con != "userdata") {
            ::_Log.Err(@"_DB.Close: Unable to locate any valid database connection");

            return false;
        }

        try {
            ::SQLite_Close(m_Con);
        } catch (e) {
            ::_Log.Ftl(@"_DB.Close: Unable to close database: %s", m_Path);
            ::_Log.Inf(@"=> Reason: %s", e);

            return false;
        }

        ::_Log.Scs(@"Succesfully closed connection to database: %s", m_Path);

        m_Con = null;
        m_Path = "";

        return true;
    }

    // --------------------------------------------------------------------------------------------
    function LastQuery()
    {
        return m_Query;
    }

    // --------------------------------------------------------------------------------------------
    function Escape(str)
    {
        try {
            return ::SQLite_Escape(str);
        } catch(e) {
            ::_Log.Err(@"_DB.Escape: Unable to escape: %s", str);
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    // --------------------------------------------------------------------------------------------
    function Exec(str)
    {
        m_Query = str;
        try {
            return ::SQLite_Exec(m_Con, m_Query);
        } catch (e) {
            ::_Log.Err(@"_DB.Exec: Unable to execute: %s", m_Query);
            ::_Log.Inf(@"=> Reason: %s", e);

            return false;
        }
    }

    function ExecF(...)
    {
        vargv.insert(0, this);

        try {
            m_Query = ::format.acall(vargv);
        } catch (e)  {
            ::_Log.Err(@"_DB.ExecF: Unable to format query: %s", vargv[1]);
            ::_Log.Inf(@"=> Reason: %s", e);

            return false;
        }

        try {
            return ::SQLite_Exec(m_Con, m_Query);
        } catch (e) {
            ::_Log.Err(@"_DB.ExecF: Unable to execute: %s", m_Query);
            ::_Log.Inf(@"=> Reason: %s", e);

            return false;
        }
    }
   
    // --------------------------------------------------------------------------------------------
    function Query(str)
    {
        m_Query = str;
        try {
            return ::SQLite_Query(m_Con, m_Query);
        } catch (e) {
            ::_Log.Err(@"_DB.Query: Unable to query: %s", m_Query);
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    function QueryF(...)
    {
        vargv.insert(0, this);

        try {
            m_Query = ::format.acall(vargv);
        } catch (e)  {
            ::_Log.Err(@"_DB.QueryF: Unable to format query: %s", vargv[1]);
            ::_Log.Inf(@"=> Reason: %s", e);

            return false;
        }

        try {
            return ::SQLite_Query(m_Con, m_Query);
        } catch (e) {
            ::_Log.Err(@"_DB.QueryF: Unable to query: %s", m_Query);
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    // --------------------------------------------------------------------------------------------
    function ErrCode()
    {
        try {
            return ::SQLite_ErrCode(m_Con);
        } catch (e) {
            ::_Log.Err(@"_DB.ErrCode: Unable to retrieve the error code");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    function ExtErrCode()
    {
        try {
            return ::SQLite_ExtendedErrCode(m_Con);
        } catch (e) {
            ::_Log.Err(@"_DB.ExtErrCode: Unable to retrieve the extended error code");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    // --------------------------------------------------------------------------------------------
    function ErrMsg()
    {
        try {
            return ::SQLite_ErrMsg(m_Con);
        } catch (e) {
            ::_Log.Err(@"_DB.ErrMsg: Unable to retrieve the error message");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    function ErrStr(code)
    {
        try {
            return ::SQLite_ErrStr(code);
        } catch (e) {
            ::_Log.Err(@"_DB.ErrStr: Unable to retrieve the error code string");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    // --------------------------------------------------------------------------------------------
    function SetExtErrCode(toggle)
    {
        try {
            return ::SQLite_ToggleExtendedErrCode(m_Con, toggle);
        } catch (e) {
            ::_Log.Err(@"_DB.SetExtErrCode: Unable to set the extended error code to %s",
                        toggle ? "true" : "false");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    // --------------------------------------------------------------------------------------------
    function Fetch(statement)
    {
        local col = 0;

        try {
            col = ::SQLite_ColumnCount(statement);
        } catch (e) {
            ::_Log.Err(@"_DB.Fetch: Unable to retrieve column count");
            ::_Log.Inf(@"=> Reason: %s", e);
        }

        try {
            local data = {};
            for (; col >= 0; --col) {
                data.rawset(::SQLite_ColumnName(statement, col), ::SQLite_ColumnData(statement, col));
            }
            return data;
        } catch (e) {
            ::_Log.Err(@"_DB.Fetch: Unable to process current row");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    // --------------------------------------------------------------------------------------------
    function Release(statement)
    {
        if (typeof statement == "userpointer") {
            try {
                ::SQLite_Release(statement);
            } catch (e) {
                ::_Log.Err(@"_DB.Release: Unable to release statement");
                ::_Log.Inf(@"=> Reason: %s", e);
            }
        }
    }
}

// ------------------------------------------------------------------------------------------------
class _DB.Statement
{
    // --------------------------------------------------------------------------------------------
    m_Stmt = null

    // --------------------------------------------------------------------------------------------
    function constructor(statement)
    {
        if (typeof statement != "userdata") {
            ::_Log.Err(@"_DB.Statement.constructor: Wrong argument types");
            ::_Log.Inf(@"=> Expected: (userdata) Got: (%s)", typeof statement);
        } else m_Stmt = statement;
    }

    // --------------------------------------------------------------------------------------------
    function _cmp(other)
    {
        if (typeof other == "dbstatement") {
            return other.m_Stmt == this.m_Stmt;
        } else return false;
    }

    // --------------------------------------------------------------------------------------------
    function _typeof()
    {
        return "dbstatement";
    }

    // --------------------------------------------------------------------------------------------
    function _tostring()
    {
        return "";
    }

    // --------------------------------------------------------------------------------------------
    function _get(idx)
    {
        return idx == true ? this.Fetch() : {};
    }

    // --------------------------------------------------------------------------------------------
    function _nexti(prev)
    {
        if (prev == null) return this.Reset();
        return this.Next();
    }

    // --------------------------------------------------------------------------------------------
    function Handle()
    {
        return m_Stmt;
    }

    // --------------------------------------------------------------------------------------------
    function Release()
    {
        if (typeof m_Stmt == "userpointer") {
            try {
                ::SQLite_Release(m_Stmt);
            } catch (e) {
                ::_Log.Err(@"_DB.Statement.Release: Unable to release statement");
                ::_Log.Inf(@"=> Reason: %s", e);
            }
        }
    }

    // --------------------------------------------------------------------------------------------
    function Reset()
    {
        try {
            if (::SQLite_Reset(m_Stmt)) return true;
        } catch (e) {
            ::_Log.Err(@"_DB.Statement.Reset: Unable to reset statement");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    // --------------------------------------------------------------------------------------------
    function Next()
    {
        try {
            if (::SQLite_NextRow(m_Stmt)) return true;
        } catch (e) {
            ::_Log.Err(@"_DB.Statement.Next: Unable to advance statement");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    // --------------------------------------------------------------------------------------------
    function Count()
    {
        try {
            return ::SQLite_ColumnCount(m_Stmt);
        } catch (e) {
            ::_Log.Err(@"_DB.Statement.Count: Unable to retrieve column count");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
        return 0;
    }

    // --------------------------------------------------------------------------------------------
    function Data(idx)
    {
        try {
            return ::SQLite_ColumnData(m_Stmt, idx);
        } catch (e) {
            ::_Log.Err(@"_DB.Statement.Data: Unable to retrieve column data");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    // --------------------------------------------------------------------------------------------
    function Name(idx)
    {
        try {
            return ::SQLite_ColumnName(m_Stmt, idx);
        } catch (e) {
            ::_Log.Err(@"_DB.Statement.Name: Unable to retrieve column name");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    // --------------------------------------------------------------------------------------------
    function Decltype(idx)
    {
        try {
            return ::SQLite_ColumnDecltype(m_Stmt, idx);
        } catch (e) {
            ::_Log.Err(@"_DB.Statement.Decltype: Unable to retrieve column declaration type");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    // --------------------------------------------------------------------------------------------
    function Size(idx)
    {
        try {
            return ::SQLite_ColumnBytes(m_Stmt, idx);
        } catch (e) {
            ::_Log.Err(@"_DB.Statement.Size: Unable to retrieve column bytes size");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }

    // --------------------------------------------------------------------------------------------
    function Fetch()
    {
        local col = this.Count()-1;

        try {
            local data = {};
            for (; col >= 0; --col) {
                data.rawset(::SQLite_ColumnName(m_Stmt, col), ::SQLite_ColumnData(m_Stmt, col));
            }
            return data;
        } catch (e) {
            ::_Log.Err(@"_DB.Statement.Fetch: Unable to process current row");
            ::_Log.Inf(@"=> Reason: %s", e);
        }
    }
}
.

.

#8
Save this code in a script "Message.nut" :
// ------------------------------------------------------------------------------------------------
_Log <- {
    // --------------------------------------------------------------------------------------------
    function Dbg(...)
    {
        try {
            vargv.insert(0, this);
            local str = ::format.acall(vargv);
            ::print(::format(@"%s %s", @"[DBG]", str));
        } catch (e) {
            ::print("[ERR] Unable to format log message: " + e);
        }
    }
    // --------------------------------------------------------------------------------------------
    function Msg(...)
    {
        try {
            vargv.insert(0, this);
            local str = ::format.acall(vargv);
            ::print(::format(@"%s %s", @"[MSG]", str));
        } catch (e) {
            ::print("[ERR] Unable to format log message: " + e);
        }
    }
    // --------------------------------------------------------------------------------------------
    function Scs(...)
    {
        try {
            vargv.insert(0, this);
            local str = ::format.acall(vargv);
            ::print(::format(@"%s %s", @"[SCS]", str));
        } catch (e) {
            ::print("[ERR] Unable to format log message: " + e);
        }
    }
    // --------------------------------------------------------------------------------------------
    function Inf(...)
    {
        try {
            vargv.insert(0, this);
            local str = ::format.acall(vargv);
            ::print(::format(@"%s %s", @"[INF]", str));
        } catch (e) {
            ::print("[ERR] Unable to format log message: " + e);
        }
    }
    // --------------------------------------------------------------------------------------------
    function Wrn(...)
    {
        try {
            vargv.insert(0, this);
            local str = ::format.acall(vargv);
            ::print(::format(@"%s %s", @"[WRN]", str));
        } catch (e) {
            ::print("[ERR] Unable to format log message: " + e);
        }
    }
    // --------------------------------------------------------------------------------------------
    function Err(...)
    {
        try {
            vargv.insert(0, this);
            local str = ::format.acall(vargv);
            ::print(::format(@"%s %s", @"[ERR]", str));
        } catch (e) {
            ::print("[ERR] Unable to format log message: " + e);
        }
    }
    // --------------------------------------------------------------------------------------------
    function Ftl(...)
    {
        try {
            vargv.insert(0, this);
            local str = ::format.acall(vargv);
            ::print(::format(@"%s %s", @"[FTL]", str));
        } catch (e) {
            ::print("[ERR] Unable to format log message: " + e);
        }
    }
}

// Stripped code....
.

.

#9
Linux binaries are also available now. They were compiled using fairly new compilers so let me know if you have any issues on older systems.
.

Anik

Please Update it for 04rel004

Beary


Kewun

yep, update pls cuz map editor doesnt work :c

KAKAN

Quote from: Kewun on May 05, 2016, 06:29 PMyep, update pls cuz map editor doesnt work :c
Map editor won't work in the new version because it still uses server side textdraws. So, that's not a reason to update :D
Though, I would request @SLC to keep both the versions, updated one and outdated one :D
oh no

.

#14
Binaries are now available on the repository download page For any issues feel free to contact me.
.