[Slice]

The built-in SQLite is more powerful than most people realize. One issue, however, is that the native functions given are very few and low-level. This library is doing something about that!

While this library mainly has functions that facilitate development, it also brings new functionality and solves crucial bugs.

A few issues taken care of:

• You can see error messages from db_query (for example, no such column name: test).
• NULL values in results caused the server to crash when using db_get_field.
• Freeing results twice caused a crash.
• Not freeing results at all caused memory leaks. SQLitei will take care of this, though you should still free results (especially if you're running many queries in a loop for example).
• You couldn't browse through results back and forth - db_next_row was the only function available.

One of the main features of this library is the prepared statements. I'll try explaining it with a couple examples below.

Prepared statements

Inserting data

Example of how it could look to insert a row into a database:

pawn Code:

// Prepare a statement
new DBStatement:stmt = db_prepare(db, "INSERT INTO mytable VALUES(?, ?, ?)");

// Set the values for the question-marks (first one is 0, second one is 1, etc.)
stmt_bind_value(stmt, 0, DB::TYPE_FLOAT , 123.456);
stmt_bind_value(stmt, 1, DB::TYPE_INT   , 1234567);
stmt_bind_value(stmt, 2, DB::TYPE_STRING, "there's no need to \"escape\" anything!");

new array[] = {1, 2, 3, 4, 5};
stmt_bind_value(stmt, 2, DB::TYPE_ARRAY, array, sizeof(array));

// Run the statement
stmt_execute(stmt);

// Then close it
stmt_close(stmt);

Reading data

pawn Code:

// These variables will be used to store the data
new Float:float_value, int_value, string_value[128];

// Prepare a new statement
new DBStatement:stmt = db_prepare(db, "SELECT a, b, c FROM mytable");

// Bind the results. Whenever a row is fetched, the variables passed to stmt_bind_result_field
// will have their values set to the respective field (by its index).
stmt_bind_result_field(stmt, 0, DB::TYPE_FLOAT , float_value);
stmt_bind_result_field(stmt, 1, DB::TYPE_INT   , int_value);
stmt_bind_result_field(stmt, 2, DB::TYPE_STRING, string_value, sizeof(string_value));

// Execute the statement!
if (stmt_execute(stmt)) {
    // When the last row is fetched, the result will be freed.
    while (stmt_fetch_row(stmt)) {
        printf("float:   %f", float_value);
        printf("integer: %d", int_value);
        printf("string:  %s", string_value);
    }
}

// Close the statement
stmt_close(stmt);

More examples

You can easily, for example, change single params or binds and execute the statement again. Examples on this later.
For now, you can have a look through the following ready-to-run gamemode for more examples:
http://pastebin.com/z4KcbdJC

Download

Download: sqlitei.inc

Functions
Each function is described below.

pawn Code:

// DB connections
         DB:db_open(name[])
            db_close(DB:db)

         DB:db_open_persistent(name[])
       bool:db_is_persistent(DB:db)
            db_free_persistent(DB:db)

            db_changes(DB:db)
            db_total_changes(DB:db)
            db_last_insert_rowid(DB:db)

            db_get_struct_info(DB:db, offset)
            db_set_struct_info(DB:db, offset, value)

// Queries and results
   DBResult:db_query(DB:db, query[], autorelease = true)
       bool:db_exec(DB:db, query[])
            db_insert(DB:db, query[])
            db_query_int(DB:db, query[], field = 0)
      Float:db_query_float(DB:db, query[], field = 0)

            db_print_result(DBResult:dbresult, max_field_length = 40)

            db_num_rows(DBResult:dbresult)
            db_next_row(DBResult:dbresult)
            db_set_row_index(DBResult:dbresult, row)
            db_get_row_index(DBResult:dbresult)

            db_num_fields(DBResult:dbresult)
            db_field_name(DBResult:dbresult, field, result[], max_length)
            db_get_field(DBResult:dbresult, field, result[], max_length)
            db_get_field_assoc(DBResult:dbresult, field[], result[], max_length)
            db_get_field_int(DBResult:dbresult, field = 0)
      Float:db_get_field_float(DBResult:dbresult, field = 0)

            db_autofree_result(DBResult:dbresult)
            db_free_result(DBResult:dbresult)
            db_is_result_freed(DBResult:dbresult)

            db_escape_string(string[], enclosing[] = "'", size = sizeof(string))

// Prepared statements
DBStatement:db_prepare(DB:db, query[])

       bool:stmt_execute(&DBStatement:statement, store_result = true, auto_free_result = true)

            stmt_bind_value(&DBStatement:statement, param, DBDataType:type, ...)

            stmt_bind_result_field(&DBStatement:statement, field, DBDataType:type, ...)
            stmt_skip_row(&DBStatement:statement)
       bool:stmt_fetch_row(&DBStatement:statement)
            stmt_rows_left(&DBStatement:statement)
   DBResult:stmt_free_result(&DBStatement:statement)

            stmt_close(&DBStatement:statement)
            stmt_autoclose(&DBStatement:statement)

Database connections

DB:db_open(name[])

Open a database.

db_close(DB:db)

Close a database. If the database is persistent, it will be closed but re-opened if you do something with it.

DB:db_open_persistent(name[])

Open a persistent database. A persistent database only has to be opened once. When it's not being used, it will be closed then re-opened when used again.

bool:db_is_persistent(DB:db)

Check if a DB handle is persistent.

db_free_persistent(DB:db)

Free a persistent database; as opposed to db_close, this will actually free the database completely.

db_changes(DB:db)

Return the number of changes made by the previous query.

db_total_changes(DB:db)

Return the total number of changes made since the DB was opened.

db_last_insert_rowid(DB:db)

Return the rowid from the last row you inserted. See this if you don't know what "rowid" is.

db_get_struct_info(DB:db, offset)

Get information from the SQLite C-struct. Mainly used internally.

db_set_struct_info(DB:db, offset, value)

Same as above, but sets it.

Queries and results

DBResult:db_query(DB:db, query[], autorelease = true)

Query the database and return the result. All results will be autoreleased by default, unless the 3rd argument is "false".

bool:db_exec(DB:db, query[])

Run a query and return true if it succeeded.

db_insert(DB:db, query[])

Run a query and return the rowid of the inserted row. Again, see this if you don't know what "rowid" is.

db_query_int(DB:db, query[], field = 0)

Run a query and return an integer from the first row at field 0 (or another field if specified in the 3rd argument).

Float:db_query_float(DB:db, query[], field = 0)

Same as above, but return a float value.

db_print_result(DBResult:dbresult, max_field_length = 40)

Print out the full result from the query. This doesn't change the result's current row index, so you can use the result as usual.

db_num_rows(DBResult:dbresult)

Return the number of rows in the result.

db_next_row(DBResult:dbresult)

Go to the next row. Returns true if there was another row to go to, otherwise false.

db_get_row_index(DBResult:dbresult)

Get the current row index (i.e. if you use db_next_row twice the row index 2).

db_set_row_index(DBResult:dbresult, row)

Set the current row index.

db_num_fields(DBResult:dbresult)

Return the number of fields in the result.

db_field_name(DBResult:dbresult, field, result[], max_length)

Get a field name.

db_get_field(DBResult:dbresult, field, result[], max_length)

Get a field's value as a string.

db_get_field_assoc(DBResult:dbresult, field[], result[], max_length)

Get a field's value as a string. The field is specified by its name instead of an index.

db_get_field_int(DBResult:dbresult, field = 0)

Get a field as an integer.

Float:db_get_field_float(DBResult:dbresult, field = 0)

Get a field as a float.

db_autofree_result(DBResult:dbresult)

Make the result automatically be freed after the current callback finishes.

db_free_result(DBResult:dbresult)

Free the result.

db_is_result_freed(DBResult:dbresult)

Returns whether or not the result was freed. Note that this only works until you open another DB or perform another query. It should be avoided.

db_escape_string(string[], enclosing[] = "'", size = sizeof(string))

Escape a string for use within a query.

Prepared statements

DBStatement:db_prepare(DB:db, query[])

Prepare a statement. Put question marks as placeholders for values to be inserted. Don't put quotes around the question marks.

bool:stmt_execute(&DBStatement:statement, store_result = true, auto_free_result = true)

Execute a statement. Returns true on success.

stmt_bind_value(&DBStatement:statement, param, DBDataType:type, ...)

Bind a value to a question mark (first question mark is param 0, second one is 1, etc.).

stmt_bind_result_field(&DBStatement:statement, field, DBDataType:type, ...)

Bind a field in the result to a variable.

stmt_skip_row(&DBStatement:statement)

Skip the current row.

bool:stmt_fetch_row(&DBStatement:statement)

Fetch a row and go to the next one. When there are no rows left, the result will be freed and the function returns false.

stmt_rows_left(&DBStatement:statement)

Return the number of rows left.

DBResult:stmt_free_result(&DBStatement:statement)

Free the result from the previous statement. Not necessary as the statements take care of that themselves anyway.

stmt_close(&DBStatement:statement)

Close a statement.

stmt_autoclose(&DBStatement:statement)

Make a statement automatically close after the current callback.