Raised This Month: $238 Target: $400
 59% 

[TUT] SQLx - the newest AMXX SQL driver set (advantages and usage)


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Hawk552
AMX Mod X Moderator
Join Date: Aug 2005
Old 11-02-2006 , 17:46   [TUT] SQLx - the newest AMXX SQL driver set (advantages and usage)
Reply With Quote #1

Before we begin, it is important to understand that this tutorial assumes you know the basics about Pawn (syntactically and the implementation of it in AMXX). You should be at least an intermediate level scripter, and have knowledge of SQL (the language itself) and perhaps DBI (the now outdated and deprecated, although still functional SQL driver that was used in AMXX for a time). I will do small sections explaining the SQL code itself, but for the most part you should know the basics.

SQLx is one of the new SQL driver sets and APIs that were incorporated into AMXX 1.75 (?) and made publicly available shortly after the release of AMXX 1.70. The important new features include the ability to thread queries (to save gameplay interruption, the queries to the database are dispatched to a new thread and then a callback is made when the query is completed or fails), better API abstraction (easier to write plugins with and more understandable), connection "tuples" (a system used to store information about a database but not connect to it - very useful for writing APIs where a connection should be allowed to be independent across differen plugins), and the ability to load multiple modules (can load MySQL, SQLite and any other drivers).

The most important thing to understand about SQLx is that the entire system works with "Handles", which store all information such as a connection, tuple, result and prepared query. This makes it very simple to both screw up (trying to connect to a query's result, due to lack of tag differentiation, for example) but at the same time makes things a lot simpler - for example, a connection is dropped by running SQL_FreeHandle, the same native used to free a result, free a prepared query and free a database tuple.

Let's look at a comparison between DBI and SQLx:

DBI:
Code:
#include <amxmodx> #include <amxmisc> #include <dbi> new Sql:g_SqlConnection new g_Error[512] public plugin_init() {     register_plugin("DBI Demonstration","1.0","Hawk552")         new Host[64],User[64],Pass[64],Db[64]     // let's fetch the cvars we will use to connect     // no pcvars because we're only fetching them once     get_cvar_string("amx_sql_host",Host,63)     get_cvar_string("amx_sql_user",User,63)     get_cvar_string("amx_sql_pass",Pass,63)     get_cvar_string("amx_sql_db",Db,63)         // ok, we're ready to connect     g_SqlConnection = dbi_connect(Host,User,Pass,Db,g_Error,511)     // indicates that the connection failed (it's either SQL_OK or SQL_FAILED)     if(g_SqlConnection < SQL_OK)         // stop the plugin with an error message         set_fail_state(g_Error)             new Result:Results[3]         // run some queries     if((Results[0] = dbi_query(g_SqlConnection,"CREATE TABLE IF NOT EXISTS zomg (you INT(11),are INT(11),a INT(11),noob INT(11))")) < RESULT_OK || (Results[1] = dbi_query(g_SqlConnection,"INSERT INTO zomg VALUES('1','2','3','4')")) < RESULT_OK || (Results[2] = dbi_query(g_SqlConnection,"INSERT INTO zomg VALUES('4','3','2','1')")) < RESULT_OK)     {         // fetch any error if there were problems         dbi_error(g_SqlConnection,g_Error,511)         // stop the plugin         set_fail_state(g_Error)     }         // free the result handles     for(new Count;Count < 3;Count++)         dbi_free_result(Results[Count]) } public client_disconnect(id) {        // run a random query     new Result:QueryResult = dbi_query(g_SqlConnection,"SELECT * FROM zomg WHERE you='1' OR you='4'")         // query failed - database probably closed since the last time we used it     if(!QueryResult)     {         dbi_error(g_SqlConnection,g_Error,511)         set_fail_state(g_Error)     }         new Data     // notice here that we run the dbi_nextrow first - one of the main reasons I don't like DBI.     // you MUST do this, it doesn't start at the first row     while(dbi_nextrow(Result))     {         // get the row         // note that we could also use dbi_result(Result,"you")         Data = dbi_field(Result,1)         // tell the server console we have found data         server_print("zomg, we have data: %d",Data)     } } public plugin_end()     // close the connection     dbi_close(g_SqlConnection)

SQLx:
Code:
#include <amxmodx> #include <amxmisc> #include <sqlx> new Handle:g_SqlTuple new g_Error[512] public plugin_init() {     register_plugin("SQLx Demonstration","1.0","Hawk552")         new Host[64],User[64],Pass[64],Db[64]     // let's fetch the cvars we will use to connect     // no pcvars because we're only fetching them once     get_cvar_string("amx_sql_host",Host,63)     get_cvar_string("amx_sql_user",User,63)     get_cvar_string("amx_sql_pass",Pass,63)     get_cvar_string("amx_sql_db",Db,63)         // we tell the API that this is the information we want to connect to,     // just not yet. basically it's like storing it in global variables     g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)         // ok, we're ready to connect     new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511)     if(SqlConnection == Empty_Handle)         // stop the plugin with an error message         set_fail_state(g_Error)             new Handle:Queries[3]     // we must now prepare some random queries     Queries[0] = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS zomg (you INT(11),are INT(11),a INT(11),noob INT(11))")     Queries[1] = SQL_PrepareQuery(SqlConnection,"INSERT INTO zomg VALUES('1','2','3','4')")     Queries[2] = SQL_PrepareQuery(SqlConnection,"INSERT INTO zomg VALUES('4','3','2','1')")         for(new Count;Count < 3;Count++)     {         // run the queries, check if they were alright         // note that you can run the same query multiple times         // we are not doing this here, but it's nice to have         if(!SQL_Execute(Queries[Count]))         {             // if there were any problems             SQL_QueryError(Queries[Count],g_Error,511)             set_fail_state(g_Error)         }                 // close the handle         SQL_FreeHandle(Queries[Count])     }         // you free everything with SQL_FreeHandle     SQL_FreeHandle(SqlConnection)    } public client_disconnect(id) {        // ok, we're ready to connect     new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511)     if(SqlConnection == Empty_Handle)         // stop the plugin with an error message         set_fail_state(g_Error)         // run a random query     new Handle:Query = SQL_PrepareQuery(SqlConnection,"SELECT * FROM zomg WHERE you='1' OR you='4'")         // run the query     if(!SQL_Execute(Query))     {         // if there were any problems         SQL_QueryError(Query,g_Error,511)         set_fail_state(g_Error)     }         // checks to make sure there's more results     // notice that it starts at the first row, rather than null     new Data     while(SQL_MoreResults(Query))     {         // columns start at 0         Data = SQL_ReadResult(Query,0)                 server_print("Found data: %d",Data)         SQL_NextRow(Query)     }         // of course, free the handle     SQL_FreeHandle(Query)         // and of course, free the connection     SQL_FreeHandle(SqlConnection) } public plugin_end()     // free the tuple - note that this does not close the connection,     // since it wasn't connected in the first place     SQL_FreeHandle(g_SqlTuple)

As you can see, there aren't too many differences when not using threaded querying (which, in case you were wondering, I did not include in this example).

Unfortunately, none of this demonstrates the true power of SQLx. The only thing it showcases really is the Handle: and SQL_FreeHandle system.

The true power lies in threaded querying. Threaded querying means nothing short of a rewrite of an entire plugin that's written with DBI, but has huge advantages, especially on slow connections. The idea of a threaded query is essentially that a new thread (a sub-process) with which its only goal is to send, monitor, and inform of the things that happen to a query. Because it is on a seperate thread, a query that would normally take 1 second (and 1 second of total connection loss for all clients in the server) still takes the same amount of time, but does not interrupt gameplay.

What are the disadvantages? As I wrote, it means basically a total rewrite, and a hell of a lot more code. When a threaded query is completed, it calls a function (that is declared in the prototype of SQL_ThreadQuery) that handles the result of the query. It could be any amount of time after this query is sent, meaning time sensitive events would be nearly impossible to work with.

Here's an example in action:

Code:
#include <amxmodx> #include <amxmisc> #include <sqlx> new Handle:g_SqlTuple new g_Cache[512] public plugin_init() {     register_plugin("SQLx Demonstration","1.0","Hawk552")         new Host[64],User[64],Pass[64],Db[64]     // let's fetch the cvars we will use to connect     // no pcvars because we're only fetching them once     get_cvar_string("amx_sql_host",Host,63)     get_cvar_string("amx_sql_user",User,63)     get_cvar_string("amx_sql_pass",Pass,63)     get_cvar_string("amx_sql_db",Db,63)         // we tell the API that this is the information we want to connect to,     // just not yet. basically it's like storing it in global variables     g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)         copy(g_Cache,511,"CREATE TABLE IF NOT EXISTS zomg (you INT(11),are INT(11),a INT(11),noob INT(11))")     SQL_ThreadQuery(g_SqlTuple,"TableHandle",g_Cache) } public TableHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize) {     // lots of error checking     if(FailState == TQUERY_CONNECT_FAILED)         return set_fail_state("Could not connect to SQL database.")     else if(FailState == TQUERY_QUERY_FAILED)         return set_fail_state("Query failed.")         if(Errcode)         return log_amx("Error on query: %s",Error)             SQL_ThreadQuery(g_SqlTuple,"QueryHandle","INSERT INTO zomg VALUES('1','2','3','4')")     SQL_ThreadQuery(g_SqlTuple,"QueryHandle","INSERT INTO zomg VALUES('4','3','2','1')")         // notice that we didn't free the query - you don't have to         return PLUGIN_CONTINUE } public QueryHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize) {     // lots of error checking     if(FailState == TQUERY_CONNECT_FAILED)         return set_fail_state("Could not connect to SQL database.")     else if(FailState == TQUERY_QUERY_FAILED)         return set_fail_state("Query failed.")         if(Errcode)         return log_amx("Error on query: %s",Error)         return PLUGIN_CONTINUE } public client_disconnect(id)     SQL_ThreadQuery(g_SqlTuple,"SelectHandle","SELECT * FROM zomg WHERE you='1' OR you='4'") public SelectHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize) {     if(FailState == TQUERY_CONNECT_FAILED)         return set_fail_state("Could not connect to SQL database.")     else if(FailState == TQUERY_QUERY_FAILED)         return set_fail_state("Query failed.")         if(Errcode)         return log_amx("Error on query: %s",Error)         new DataNum     while(SQL_MoreResults(Query))     {         DataNum = SQL_ReadResult(Query,0)                 server_print("zomg, some data: %d",DataNum)             SQL_NextRow(Query)     }         return PLUGIN_CONTINUE } public plugin_end()     // free the tuple - note that this does not close the connection,     // since it wasn't connected in the first place     SQL_FreeHandle(g_SqlTuple)

Not so hard - especially if the result you get doesn't matter.

Finally, multiple modules running can be accomplished by running SQL_GetAffinity, comparing it to the database type you want to run, and setting it with SQL_SetAffinity. An example of this can be found in the sqlx.inc header:

Code:
new set_type[12] //... get_cvar_string("amx_sql_type", set_type, 11) //...     SQL_GetAffinity(get_type, 12)         if (!equali(get_type, set_type))     {         if (!SQL_SetAffinity(set_type))         {             log_amx("Failed to set affinity from %s to %s.", get_type, set_type)         }     }

SQLx is also useful for dealing with multiple plugins - the "tuple" and "handle" system makes for easy transfer of information across unknown territory (specifically addon plugins).

Here is an example:

Code:
#include <amxmodx> #include <amxmisc> #include <sqlx> new Handle:g_SqlTuple public plugin_init()     register_plugin("SQLx Demonstration - Core","1.0","Hawk552") public plugin_natives() {     new Host[64],User[64],Pass[64],Db[64]     // let's fetch the cvars we will use to connect     // no pcvars because we're only fetching them once     get_cvar_string("amx_sql_host",Host,63)     get_cvar_string("amx_sql_user",User,63)     get_cvar_string("amx_sql_pass",Pass,63)     get_cvar_string("amx_sql_db",Db,63)         // we tell the API that this is the information we want to connect to,     // just not yet. basically it's like storing it in global variables     g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)         register_native("sql_demo_get_handle","_sql_demo_get_handle") }     public _sql_demo_get_handle()     return _:g_SqlTuple public plugin_end()     // free the tuple - note that this does not close the connection,     // since it wasn't connected in the first place     SQL_FreeHandle(g_SqlTuple)

Code:
#include <amxmodx> #include <amxmisc> #include <sqlx> native Handle:sql_demo_get_handle()     public plugin_init() {     register_plugin("SQLx Demonstration - Attachment","1.0","Hawk552")         SQL_ThreadQuery(sql_demo_get_handle(),"QueryHandle","CREATE TABLE IF NOT EXISTS zomg (rofl INT(11))") } public QueryHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize) {     if(FailState == TQUERY_CONNECT_FAILED)         return set_fail_state("Could not connect to SQL database.")     else if(FailState == TQUERY_QUERY_FAILED)         return set_fail_state("Query failed.")         if(Errcode)         return log_amx("Error on query: %s",Error)             server_print("zomg, table made") }

Accomplishing this in DBI is not hard, but a connection would constantly need to be left open, something that SQLx doesn't demand.

Remember that all SQLx modules contain support for DBI as well, and many people are more comfortable with it. DBI has its weaknesses however, and SQLx was designed to overcome them.

Like always, if you have any questions or comments, feel free to post.
__________________

Last edited by Exolent[jNr]; 11-06-2010 at 20:14. Reason: Corrected error MaximusBrood noticed
Hawk552 is offline
Send a message via AIM to Hawk552
Zenith77
Veteran Member
Join Date: Aug 2005
Old 11-02-2006 , 18:03   Re: SQLx - the newest AMXX SQL driver set (advantages and usage)
Reply With Quote #2

Good job, I learned something new today (instead of dealing with countless idiots). I was also thinking while I was reading this that it would be cool to have a threaded sockets module ("SocketsX" lol?), and it would make a plugin I'm making alot more, umm flexible, to deal with.
__________________
Quote:
Originally Posted by phorelyph View Post
your retatred

Last edited by Zenith77; 11-02-2006 at 18:06.
Zenith77 is offline
Nostrodamous
BANNED
Join Date: Oct 2006
Old 11-02-2006 , 19:36   Re: SQLx - the newest AMXX SQL driver set (advantages and usage)
Reply With Quote #3

Nice tutorial Hawk , I was wondering when you would make another one . I like the new sql seems alot more capable.
Nostrodamous is offline
Old 11-02-2006, 19:37
SweatyBanana
This message has been deleted by Brad. Reason: off-topic
Old 11-02-2006, 20:10
Nostrodamous
This message has been deleted by Brad. Reason: off-topic
Old 11-02-2006, 20:11
SweatyBanana
This message has been deleted by Brad. Reason: off-topic
Old 11-02-2006, 20:15
Hawk552
This message has been deleted by Brad. Reason: off-topic
Old 11-02-2006, 20:24
Nostrodamous
This message has been deleted by Brad. Reason: off-topic
Old 11-02-2006, 20:28
Hawk552
This message has been deleted by Brad. Reason: off-topic
Rolnaaba
Veteran Member
Join Date: May 2006
Old 11-03-2006 , 11:15   Re: SQLx - the newest AMXX SQL driver set (advantages and usage)
Reply With Quote #4

thanks for making this for me hawk552 your awesome
__________________
DO NOT PM me about avp mod.
Rolnaaba is offline
BAILOPAN
Join Date: Jan 2004
Old 11-03-2006 , 14:20   Re: SQLx - the newest AMXX SQL driver set (advantages and usage)
Reply With Quote #5

Good article. The advantage of sharing handles in between plugins isn't something I thought of at first.
__________________
egg
BAILOPAN is offline
TheNewt
Donor
Join Date: Jun 2006
Location: Where I live.
Old 11-09-2006 , 15:36   Re: SQLx - the newest AMXX SQL driver set (advantages and usage)
Reply With Quote #6

I think I love you hawk...
J/k Kudos though!
__________________
Quote:
toe3_ left the chat room. (G-lined (AUTO Excessive connections from a single host.))
TheNewt is offline
MaximusBrood
Veteran Member
Join Date: Sep 2005
Location: The Netherlands
Old 11-10-2006 , 12:15   Re: SQLx - the newest AMXX SQL driver set (advantages and usage)
Reply With Quote #7

In your first SQLx example.

This,

Code:
        // we tell the API that this is the information we want to connect to,     // just not yet. basically it's like storing it in global variables     g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)         // ok, we're ready to connect     new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511)
    if(g_SqlTuple == Empty_Handle)
        // stop the plugin with an error message         set_fail_state(g_Error)

should be:

Code:
    // we tell the API that this is the information we want to connect to,     // just not yet. basically it's like storing it in global variables     g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)         // ok, we're ready to connect     new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511)
    if(SqlConnection == Empty_Handle)
        // stop the plugin with an error message         set_fail_state(g_Error)
__________________
Released six formerly private plugins. Not active here since ages.
MaximusBrood is offline
Hawk552
AMX Mod X Moderator
Join Date: Aug 2005
Old 11-10-2006 , 14:13   Re: SQLx - the newest AMXX SQL driver set (advantages and usage)
Reply With Quote #8

You're right, thanks.
__________________
Hawk552 is offline
Send a message via AIM to Hawk552
harbu
Senior Member
Join Date: Aug 2004
Location: Finland
Old 01-06-2007 , 12:52   Re: SQLx - the newest AMXX SQL driver set (advantages and usage)
Reply With Quote #9

Very nice tutorial, thanks Hawk
__________________
harbu is offline
Send a message via MSN to harbu
Rixorster
Senior Member
Join Date: Jul 2005
Old 01-06-2007 , 13:44   Re: SQLx - the newest AMXX SQL driver set (advantages and usage)
Reply With Quote #10

Yeh, thanks Hawk, now i actually know some SQL (I wouldn't even went to SQL side of AMXx stuff, but i got drawn to it by this )
__________________
You never know, what will happen the day after tomorrow...

+karma if i helped you!
Rixorster is offline
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

Forum Jump


All times are GMT -4. The time now is 08:39.


Powered by vBulletin®
Copyright ©2000 - 2020, vBulletin Solutions, Inc.
Theme made by Freecode