SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Filterscripts > Includes

Reply
 
Thread Tools Display Modes
Old 14/12/2011, 08:28 PM   #1
Slice
High-roller
 
Join Date: Mar 2008
Location: Sweden
Posts: 1,899
Reputation: 1590
Spray SQLite Improved



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:
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

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.
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.

Last edited by Slice; 07/10/2013 at 11:00 AM.
Slice is offline   Reply With Quote
Old 14/12/2011, 08:32 PM   #2
Scenario
Godfather
 
Scenario's Avatar
 
Join Date: Feb 2010
Location: Heaven
Posts: 8,132
Reputation: 701
Default Re: SQLite Improved

Awesome release!
Scenario is offline   Reply With Quote
Old 14/12/2011, 08:34 PM   #3
TheArcher
High-roller
 
TheArcher's Avatar
 
Join Date: Dec 2009
Location: Home
Posts: 2,423
Reputation: 251
Default Re: SQLite Improved

Yes, it's a cool Include. Why don't you try to release an improved for MySQL?
__________________
TheArcher is offline   Reply With Quote
Old 14/12/2011, 08:35 PM   #4
Gforcez1337
High-roller
 
Gforcez1337's Avatar
 
Join Date: Jul 2010
Location: The Netherlands.
Posts: 1,102
Reputation: 367
Default Re: SQLite Improved

again a cool release
__________________

Gforcez1337 is offline   Reply With Quote
Old 14/12/2011, 08:37 PM   #5
Kar
High-roller
 
Kar's Avatar
 
Join Date: May 2010
Location: Black Asylum
Posts: 3,211
Reputation: 498
Default Re: SQLite Improved

Great, little typo here. "new float_value, int_value, string_value[128];" shouldn't it be new Float:float_value, ...
Kar is offline   Reply With Quote
Old 14/12/2011, 08:41 PM   #6
FireCat
High-roller
 
FireCat's Avatar
 
Join Date: Jul 2010
Posts: 2,404
Reputation: 596
Default Re: SQLite Improved

Looks nice (:
FireCat is offline   Reply With Quote
Old 14/12/2011, 08:55 PM   #7
agent5
Big Clucker
 
agent5's Avatar
 
Join Date: Aug 2011
Posts: 55
Reputation: 4
Default Re: SQLite Improved

Awsome man !
Rep +
agent5 is offline   Reply With Quote
Old 14/12/2011, 09:11 PM   #8
[HiC]TheKiller
High-roller
 
Join Date: Mar 2008
Location: New Zealand
Posts: 3,022
Reputation: 409
Default Re: SQLite Improved

Nice one slice . Your releases with SQLite are pretty awesome .
__________________
[HiC]TheKiller is offline   Reply With Quote
Old 15/12/2011, 04:32 AM   #9
Lorenc_
High-roller
 
Lorenc_'s Avatar
 
Join Date: Jan 2010
Location: Australia
Posts: 4,273
Reputation: 1137
Default Re: SQLite Improved

Nice include!
__________________
Join the best Cops And Robbers in SA-MP, today. svr.irresistiblegaming.com:7777

Lorenc_ is offline   Reply With Quote
Old 15/12/2011, 06:25 AM   #10
System64
Gangsta
 
System64's Avatar
 
Join Date: May 2011
Location: Zadar, Croatia
Posts: 940
Reputation: 143
Default Re: SQLite Improved

fucking awesome
__________________
All my works
I left SA:MP! Maybe once I start scripting again but now I'm based on web programming
Soon, I'll buy domain system32.com.hr but for now you can contact me on LogoOff or on my ******* channel!
System64 is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
[FilterScript] [FS] Improved aiming luby Filterscripts 70 19/09/2014 09:00 AM
[GameMode] SECOND RELEASE TDM: IMPROVED grantism Gamemode Scripts 5 15/10/2011 03:59 PM
Have I improved? BigAl Everything and Nothing 1 10/09/2011 12:17 AM
[Map] An improved LSPD Blacknight Maps 5 01/01/2011 04:21 PM
[Map] [MAP]Improved District im2fast4u Maps 2 06/10/2009 03:44 PM


All times are GMT. The time now is 01:36 PM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.