[AndreT]

Hey!

Since BlueG's MySQL plugin was updated to version R7 in the beginning of February (2012), there have been a lot of people who haven't been able to understand how to fully thread their queries or how to use the new cache functions. I have received 2 requests for a tutorial on this subject, so I decided to come up with one to (hopefully) help everyone understand how to thread queries and use the cache functionality.

Plugin version R7
* Dropped support for unthreaded queries.
* Added cache functionality with functions cache_get_row, cache_get_field, cache_get_data, cache_get_field_content.
* Switching over to using mysql_function_query, which unlike mysql_query_callback from R6-2, allows scripters to pass custom variables as parameters to the callback.
* Bugfixes, improvements, etc. that this tutorial does not cover.

Plugin version R8 and newer
BlueG has decided to use a public repository for the development of this plugin. This means that others can commit code as well, and some already have. Here are some of the changes:
* Added cache_get_row_int and cache_get_row_float (supported briefly in this tutorial, but I'll continue it some time).
* More security added to internal string handling to prevent buffer overflows.
* Fixed crash issue on gamemode restart (gmx).
* A few more internal issues cleared up.

Benefits of using the cache
* It is faster! Using cache functions instead of mysql_fetch_row is notably faster. For JernejL, loading 1300 vehicles from the database went from 6 seconds to 60 milliseconds (source) and from my personal experience, I can tell that loading 500 houses from a database is now at least 10-20 times faster (source). Here are some more results.
* It does not require the coder to store/free the query result! There's no need to call mysql_store_result() to store the result or mysql_free_result() to free the result after done processing it. Specifically the result freeing has proven itself to be a source of problems, specially for newer scripters.
* It is easier to read/write/modify! (depending on the programmer's own preference)
To continue with, parsing the query result has become simpler compared to several older methods like sscanf.

pawn Code:

sscanf(data, "p<|>s[20]s[20]s[20]", string1, string2, string3);
// Becomes
cache_get_row(0, 0, string1);
cache_get_row(0, 1, string2);
cache_get_row(0, 2, string3);
// or
cache_get_field_content(0, "fieldname1", string1);
cache_get_field_content(0, "fieldname2", string2);
cache_get_field_content(0, "fieldname3", string3);

Functions
Before continuing with tips and examples for converting from older plugin versions to the newer format, lets go over the new functions that version R7 of the plugin introduced.

mysql_function_query(connectionHandle, query[], bool:cache, callback[], format[], {Float,_}:... )
* Replaces mysql_query and mysql_query_callback from older versions.
* Allows scripter to pass extra parameters to the callback with the format[] and following parameters (behavior similar to CallRemoteFunction)

pawn Code:

mysql_function_query(dbHandle, "SELECT ... FROM ...", true, "OnQueryFinished", "siii", "Andre", 1, 2, 3);
forward OnQueryFinished(name[], number_1, number_2, number_3);
public OnQueryFinished(name[], number_1, number_2, number_3)
{
    printf("Data: %s %i %i %i", name, number_1, number_2, number_3);
    // will print: Andre 1 2 3
}

As you can see in the mysql_function_query usage, "siii" stands for "string integer integer integer", which is respective to the data of "Andre 1 2 3", which is originally passed as separate parameters. This very effectively removes the need to use extra variables, PVars or GVars for this purpose. All parameters can be passed to the query function itself (keep it below 20 parameters though).

* Allows scripter to decide whether to use caching or not. The third parameter of the function decides whether the query should take advantage of the new cache functions. If this is set to true, the plugin will populate a vector with the raw data received as the result of the query. Whenever you run a SELECT query, this should be set to true (read above for the benefits of using cache functions).

pawn Code:

// SELECT queries (that return a result set)
mysql_function_query(dbHandle, "SELECT * FROM players", true, "OnPlayerListLoad", "");
// We set caching to true and set the callback to "OnPlayerListLoad" - this callback will be called without parameters (the empty quotes specify that) when the query finishes.

// Other (UPDATE, etc) queries
mysql_function_query(dbHandle, "UPDATE players SET kills = 10 WHERE id = 1", false, "", "");
// We leave the callback and parameters parameters empty and run a simple UPDATE query.

cache_get_data(&num_rows, &num_fields, connectionHandle = 1)
This function is the first that should be called in most cases after running a query with caching set to true. This, of course, unless you're 100% sure that you already know the amount of rows/fields that your query returns.
A very simple example of this:

pawn Code:

mysql_function_query(dbHandle, "SELECT * FROM players WHERE name = 'Andre' LIMIT 0,1", true, "OnPlayerDataLoad", "s", "Andre");
forward OnPlayerDataLoad(name[]);
public OnPlayerDataLoad(name[])
{
    new rows, fields;
    cache_get_data(rows, fields);
    if(!rows)
    {
        print(!"Andre is not a registered account on this server!");
    }
    else
    {
        printf("Andre is registered (1 row with %d fields)", fields);
    }
}

As you can see, it returns the number of rows and fields that the query returned.

cache_get_row(row, idx, dest[], connectionHandle = 1)
This is the most basic function for retrieving all sorts of data. We will be using this the most. The syntax is really simple and as I mentioned before, probably easier to understand in a large block of code (specially when the query returns a lot of fields) than sscanf parsing perhaps. What this function does is take the row index and the field index and fetches its data to the destination string (dest[]) that the scripter specified.
This example is a continuation of the example for cache_get_data. We assume that our table has the following syntax:
1. ID (unique ID, AUTO_INCREMENT) - field index 0
2. name - field index 1
3. level - field index 2

pawn Code:

printf("Andre is registerd (1 row with %d fields)", fields);
new temp[12];
cache_get_row(0, 0, temp);
pritnf("Andre's ID is %d", strval(temp));
cache_get_row(0, 2, temp);
printf("Andre's level is %d", strval(temp));

Since cache_get_row takes a string as the third parameter (destination), we will have to use a temporary placeholder for all sorts of numeric values that we want to store as integers later. An example of this would be:

pawn Code:

cache_get_row(0, 1, temp);
PlayerInfo[playerid][pLevel] = strval(temp);

It should be understood for scripters that indexes in programming start from 0. This means that the first database field would actually be considered as field with index 0. The second field is a field with index 1 and so on...

cache_get_row_int(row, idx, connectionHandle = 1)
Not supported in R7. See this repository.
New updates by cool guys like udan have brought the function I introduced here back in 2012 to the main plugin code. This is extremely useful as it requires the scripter to write less code and is faster as well.

pawn Code:

// Old code
new temp[12];
cache_get_row(0, 1, temp);
PlayerInfo[playerid][pMoney] = strval(temp);

pawn Code:

// New code
PlayerInfo[playerid][pMoney] = cache_get_row_int(0, 1);

Large tests showed that the new function executes 1.5 times faster than using the combination of cache_get_row() and strval().

Float:cache_get_row_float(row, idx, connectionHandle = 1)
Not supported in R7. See this repository.
See this post for the code I wrote back in 2012 and some speed tests.

This function should make you happy if you work with floats a lot. And handling of float values is somewhat slow in PAWN, but a lot faster in C++, so if some of it can be done internally, it is a win-win situation. Similarly to the cache_get_row_int implementation, this function helps you write shorter code that executes faster.

pawn Code:

// Old code
new temp[12];
cache_get_row(0, 10, temp);
PlayerInfo[playerid][pSpeedMultiplier] = floatstr(temp);

pawn Code:

// New code
PlayerInfo[playerid][pSpeedMultiplier] = cache_get_row_float(0, 10);

The new code is ~2 times faster than using cache_get_row() with floatstr(). See the link above for speed tests.

cache_get_field(field_index, dest[], connectionHandle = 1)
The plugin also stores the names of the fields that a query retrieves. I cannot think of an example from my own code where this could become useful, but when you run a query like SELECT * FROM ... with an asterisk that selects all fields, there can be some cases where you need to retrieve the field's name. This function will help you in this case:

pawn Code:

public OnQueryExecute() // whatever "SELECT * FROM ..." query
{
    new fieldname[32];
    cache_get_field(0, fieldname);
    printf("The name of field 0 is '%s'", fieldname);
    return 1;
}

A little bit more complicated example of combining cache_get_data and cache_get_field:

pawn Code:

public OnQueryExecute()
{
    new fieldname[32], fields, rows;
    // retrieve the amount of rows and fields (we'll take advantage of the field count)
    cache_get_data(rows, fields);
    // loop through all fields
    for(new i = 0; i != fields; i++)
    {
        cache_get_field(i, fieldname);
        printf("The name of field %d is '%s'", i, fieldname);
    }
    return 1;
}

cache_get_field_content(row, const field_name[], dest[], connectionHandle = 1)
This is a function that I don't suggest people to use unless you're handling a case where the field index is unknown. But in most cases, the scripters already know the field index! An example of this would be:

pawn Code:

mysql_function_query(dbHandle, "SELECT name,level,kills,deaths FROM players WHERE id = 1", true, "OnPlayerDataLoad", "");
// indexes: name = 0, level = 1, kills = 1, deaths = 2

In this example, you already know the indexes, so there's nothing wrong with using cache_get_row!
But I'm lazy and I want to do this? Sure, why not, the speed difference isn't notable at all. I don't know if I'm a freak if it comes to cases like this, but when I know that the plugin has no need to loop through the names of all fields, it will be at least a little bit faster.

An example of this function, anyways: (based on the example I gave for cache_get_row)

pawn Code:

printf("Andre is registerd (1 row with %d fields)", fields);
new temp[12];
cache_get_field_content(0, "level", temp);
printf("Andre's level is %d", strval(temp));

Converting
Since unthreaded queries are totally unsupported in the new version of the plugin, we should first cover how to convert unthreaded queries into threaded ones. And this time using the new querying function and cache compatibility. This is a most basic example of an old unthreaded query:

pawn Code:

format(query, sizeof(query), "SELECT name,level,kills,deaths FROM players WHERE name = %s LIMIT 0,1", PlayerName);
mysql_query(query);
mysql_store_result();
if(mysql_num_rows())
{
    mysql_fetch_row(data);
    sscanf(data, "p<|>s[24]iii", name, level[playerid], kills[playerid], deaths[playerid]);
    SendClientMessage(playerid, 0xFFFFFFFF, "I have received your data!");
}
mysql_free_result();

Queries like the one above were not recommended before (mixing threaded and unthreaded queries also sometimes had undefined results) and they are totally unsupported now. The new behavior is that when a query finishes, the callback specified in the mysql_function_query line is called. This also means that if we are retrieving data about a player, we will need to pass (send) the player ID as a parameter to the callback. When the query finishes, we use the cache functions described above to store the information we're querying for. This is easy, just watch this example with some handy comments included.

pawn Code:

format(query, sizeof(query), "SELECT name,level,kills,deaths FROM players WHERE name = '%s' LIMIT 0,1", PlayerName);
mysql_function_query(dbHandle, query, true, "OnPlayerDataLoad", "i", playerid);

// somewhere else in your script. In the main scope...
forward OnPlayerDataLoad(playerid);
public OnPlayerDataLoad(playerid)
{
    // Get the amount of rows (0 or 1 due to the LIMIT clause) and fields returned by the query.
    new rows, fields;
    cache_get_data(rows, fields);
    if(rows)
    {
        cache_get_row(0, 0, name); // Store the data from the name field into the name string.
        new temp[12]; // Create a temporary string to store the numeric values we will not retrieve.
        cache_get_row(0, 1, temp); // Store the string representation of the player's level in the temporary string.
        level[playerid] = strval(temp); // Store the numeric representation of the player's level in the right array.
        // The same comments go for the next 2 fields (kills and deaths).
        cache_get_row(0, 2, temp), kills[playerid] = strval(temp);
        cache_get_row(0, 3, temp), deaths[playerid] = strval(temp);
    }
    return 1; 
    // Returning 1 to whatever callback that uses the cache functionality will clear out the cache in the plugin.
    // Note that no usage of mysql_free_result is required.
}

A huge benefit from threading is that your server no longer hangs during the execution of the query that is ran through mysql_function_query. This means that while the query is running (and larger queries can take up to even a few seconds to execute), the server will be able to do other things in the background (synchronize player data, call other callbacks, functions, etc). Due to a feature of R7, you no longer need to worry about freeing data and memory leaks: whenever you return 1 to the callback that is ran by the plugin, the vectors containing the cached field data will be cleared automatically.


Useful: protecting your server against race condition attack (thanks to JernejL)
Useful: Multiple approaches to selecting data from MySQL and then parsing it (with and without field names)
Useful: A basic gamemode using MySQL R7 plugin and caching

Thank you for reading. This tutorial was mostly written very late in the evening and every suggestion is welcome. If you have any requests, questions or would like to know more about a certain aspect, don't hesitate to post.

16/03/2013: I added some information about the newer plugin versions and will continue updating this tutorial.