[Include] SQLite Improved
#1



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.
Reply
#2

Awesome release!
Reply
#3

Yes, it's a cool Include. Why don't you try to release an improved for MySQL?
Reply
#4

again a cool release
Reply
#5

Great, little typo here. "new float_value, int_value, string_value[128];" shouldn't it be new Float:float_value, ...
Reply
#6

Looks nice (:
Reply
#7

Awsome man !
Rep +
Reply
#8

Nice one slice . Your releases with SQLite are pretty awesome .
Reply
#9

Nice include!
Reply
#10

fucking awesome
Reply
#11

AWSUM THX!
Good to see this released (already discussed on IRC) :P
Reply
#12

well scripted, well documented so well done!
Reply
#13

Thanks for the feedback.

@Kar: Yeah, slipped my mind somehow!

Update
  • Added stmt_autoclose.
  • Memory usage decreased significantly.
  • All functions now accept both packed and unpacked strings.
  • Minor bug fixes and optimizations.
Reply
#14

Fuck you I am not going to rewrite my db functions once again
Reply
#15

Quote:
Originally Posted by Finn
Посмотреть сообщение
Fuck you I am not going to rewrite my db functions once again
Was that an insult?
Reply
#16

Quote:
Originally Posted by TheArcher
Посмотреть сообщение
Was that an insult?
No, it was sarcasm.
Reply
#17

Pretty damn awesome. Very useful for host that don't have MySQL.
Reply
#18

Update!
  • Added db_open_persistent, db_is_persistent, db_is_valid_persistent, and db_free_persistent.
    Persistent databases work exactly the same as ones you open with db_open, except for one thing: you only have to open it once. No, the database won't be constantly open.
    Example:
    pawn Код:
    new
        DB:g_db
    ;

    public OnGameModeInit() {
        g_db = db_open_persistent("mydb.db");
       
        db_query(g_db, "...");
       
        // The db will now close
    }

    public OnSomethingSomething() {
        // SQLitei will detect that the db is closed and open it
        db_query(g_db, "...");
       
        // The db is still open
        db_query(g_db, "...");
       
        // The db will now close
    }
  • Added db_query_autofree.
    This is just like db_query, except it will free the result for you when the current callback finishes executing. No need to use db_free_result.

    Note: Do not use db_free_result on results from this function.
  • Added db_get_field_int and db_get_field_float.
    By default, it will fetch field 0. You can add an additional argument to specify another field.

    Quickly fetch an integer or float from a result. Example:
    pawn Код:
    new user_count;

    user_count = db_fetch_int(db_query_autofree(db, "SELECT COUNT(*) FROM users"));

    printf("There are %d users registered.", user_count);
  • Corrected a few SQLite natives.
Reply
#19

surely gonna use it

Awesome work...
Reply
#20

Update!

Highlights:
  • Added two new types for stmt_bind_value:
    • DB::TYPE_WP_HASH: Puts a BLOB value of a whirlpool hash from the given string into the query (ex. x'FFAA4411...').
    • DB::TYPE_PLAYER_NAME: Puts a player name from the ID passed.
    Note that DB_USE_WHIRLPOOL must be defined as true in order for DB::TYPE_WP_HASH to work.
  • Added the preprocessor options DE_DEBUG (logs debug info) and DB_LOG_TO_CHAT (prints log messages to chat).
  • Improved the way results are dealt with internally to avoid crashes at all costs.
  • Added debug messages pretty much everywhere (shown if DB_DEBUG is true).
I'm also releasing an example of a gamemode with login and register commands using ZCMD and Whirlpool!
Download demo!
Reply


Forum Jump:


Users browsing this thread: 10 Guest(s)