[RIDE2DAY]

When to use the different MySQL queries

[*] Index

• Introduction
• MySQL queries
• Threaded and non-threaded queries
• What function should I use to send a query?
  • mysql_query
  • mysql_tquery
  • mysql_pquery
• MySQL transactions
• mysql_format or format?
• How to create a table correctly
• The end

[*] Introduction
Hello guys, this is the second part of my MySQL tutorial series I'm writing and at the same time it's the second one I write in English, so just like I say always: if you find any grammatical error let me know please. If you read the first part you should know that I'm using the R41-4 version, if you didn't I invite you to in order to know how to establish a connection correctly. In this tutorial we're going to see what type of queries we can use and in which cases we should use each one. Before anything I'd like to let you know that I'm not a MySQL expert, so if you know better code techniques you're welcome to share them with us.


[*] MySQL queries
So, what is a query? Well, a dictionary might help you with that! Alright, jokes apart, a query basically is a "question" we send to the database in order to receive an "answer", also known as "result". The result contains the answer to our query, so that's what we process when the MySQL server returns it. With the current MySQL plugin we can send different types of queries, by different types I mean how they will be executed/treated by the SA-MP server. Here's where threaded and non-threaded queries come in.


[*] Threaded and non-threaded queries
In SA-MP the script runs on a single thread, that means that if you execute two functions consecutively the second one will run after the first one finishes, but what would happen if the first function requires a lot of time? You can read more about it on this tutorial wrote by Slice.

In short, threaded queries have been created in order to avoid unnecessary waits. Multithreading is something very useful because we can't predict the time a query requires. If the server sleeps too much we can get into desynchronization troubles. Let's see a little graphic explanation, the black line represents the SA-MP script while the green one the MySQL query:

Non-threaded query: the SA-MP script sleeps while the MySQL query is being processed.	Threaded query: the SA-MP script runs while the MySQL query is being processed.

Personally, I love threaded queries. I didn't use non-threaded queries in my racing gamemode yet.


[*] What function should I use to send a query?
As I said before, with the current plugin we can send different types of queries. Let's see what we got:

• mysql_query: sends a non-threaded query to the MySQL server.
• mysql_tquery: sends a query which will be executed in another thread and calls a callback (if there was one specified) when the execution is successfully finished.
• mysql_pquery: sends a query which will be executed in another thread concurrently and calls a callback (if there was one specified) when the execution is successfully finished.

So, which one should we use? Well, it depends mainly on what your script does. Let's see some examples:

• mysql_query
  
  Code:

  Parameters:
  MySQL:handle          -> The connection handle this will be processed on.
  const query[]         -> The query you want to execute.
  bool:use_cache = true -> Set to true by default, if you don't want to use the cache set to false.
  
  Return values:
  A valid cache-id if use_cache was set to true, MYSQL_INVALID_CACHE otherwise.

  Probably this is the function everybody knows due its name, don't trust your old school programmer instincts in this case, nowadays it works very different! So, you better forget about mysql_store_result, mysql_free_result, mysql_num_rows, etc.
  
  In my opinion it's hard to tell when non-threaded queries are the way to go, but they might come in handy in some rare cases. Imagine a gmx case, if you use tquery/pquery in OnGameModeInit players might connect before your systems have loaded because the SA-MP script won't sleep while the queries are being processed. Neverthless, using non-threaded queries OnPlayerConnect won't be called until all queries have been processed and OnGameModeInit finished.
  
  PHP Code:

  public OnGameModeInit()
{
    new rows;
    new Cache:result;
    result = mysql_query(db_handle, "SELECT * FROM `houses`"); // Select all houses.
    if(cache_get_row_count(rows)) // Make sure the function succeeds.
    {
        for(new r = 0; r < rows; r++) // Process the rows (if there are any), if rows = 0 the code inside the for loop won't run.
        {
            // Cache functions here.
        }
    }
    cache_delete(result); // The previous result has been processed, delete it and free the memory.
    result = mysql_query(db_handle, "SELECT * FROM `vehicles`"); // Select all vehicles.
    // Repeat the process here.
    return 1;
} 

  That example is kinda ugly, we might use some functions like LoadHouses, LoadVehicles, etc. and write the code inside.
  
  PHP Code:

  public OnGameModeInit()
{
    LoadHouses();
    LoadVehicles();
    return 1;
}
LoadHouses()
{
    new rows;
    new Cache:result;
    result = mysql_query(db_handle, "SELECT * FROM `houses`");
    if(cache_get_row_count(rows))
    {
        for(new r = 0; r < rows; r++)
        {
  
        }
    }
    cache_delete(result);
    return 1;
}
LoadVehicles()
{
    new rows;
    new Cache:result;
    result = mysql_query(db_handle, "SELECT * FROM `vehicles`");
    if(cache_get_row_count(rows))
    {
        for(new r = 0; r < rows; r++)
        {
  
        }
    }
    cache_delete(result);
    return 1;
} 

  Remember it's very important to delete the cache, if you don't you might experience memory leaks. Watch out, running a non-threaded query without a SELECT statement doesn't mean it won't save the cache, pay attention:
  
  PHP Code:

  cache_delete(
    mysql_query(db_handle, "UPDATE `houses` SET `price` = %d WHERE `id` = 50")
);
/* Notice the cache_delete function, we're not using SELECT there but the
UPDATE statement will return the amount of rows that were actually changed.
If you want to ignore the result, set the use_cache parameter to false: */
mysql_query(db_handle, "UPDATE `houses` SET `price` = %d WHERE `id` = 50", false); 

• mysql_tquery
  
  Code:

  Parameters:
  MySQL:handle     -> The connection handle this will be processed on.
  const query[]    -> The query you want to execute.
  const callback[] -> The result callback to call after the query successfully executed (optional).
  const format[]   -> The format specifier string for the result callback (optional).
  {Float,_}:...    -> Indefinite number of arguments (optional).
  
  
  Return values:
  1 if the query was successfully queued for execution, 0 otherwise.

  Alright, after threaded queries execute, they call an independent callback so we can process the cache. Let's see a little example:
  
  PHP Code:

  public OnPlayerConnect(playerid)
{
    new query[73];
    new p_name[MAX_PLAYER_NAME];
    GetPlayerName(playerid, p_name, sizeof(p_name));
    // Later we'll see why we're using mysql_format instead of format.
    mysql_format(db_handle, query, sizeof(query), "SELECT COUNT(*) FROM `players` WHERE `name` = '%e'", p_name);
    
    mysql_tquery(db_handle, query, CheckPlayerAccount, "d", playerid);
    return 1;
}
forward CheckPlayerAccount(playerid);
public CheckPlayerAccount(playerid)
{
    new value;
    /* COUNT is a MySQL function, in this case it'll return a row even if the name
    doesn't exist in our table. If it doesn't exist the value will be 0, otherwise
    it'll be 1 or the amount of times the name is found.
    In the next function we're using 0 as row index and column index because we're
    working with a row which has one column only. */
    cache_get_value_index_int(0, 0, value);
    if(value > 0)
    {
        // That name is registered.
    }
    else
    {
        // That name isn't registered.
    }
    return 1; // This is very important, return 1 in order to delete the cache.
} 

  Notice how we used a new callback to process the cache, while the MySQL server responds the SA-MP script doesn't sleep. Basically we process the result as fast as we get it from the MySQL server but without "sleeping" while it arrives. This is really sweet because the SA-MP script is still running and players won't experience desynchronization if the MySQL server takes five minutes (hypothetically) to respond.
  
  It's good to know that these queries are queued, if you send two consecutive queries the second one won't be processed until the first one finishes. So yes, they're good for those cases where a query depends on a previous one, for example:
  
  PHP Code:

  public OnGameModeInit()
{
    mysql_tquery(db_handle, "CREATE TABLE IF NOT EXISTS `houses` (...)");
    mysql_tquery(db_handle, "SELECT * FROM `houses`", OnHousesLoad);
    /* You can't select the houses if their table hasn't been created,
    doing this the second query will be executed once the first one finishes.
    Notice how the first query doesn't use any callback, we don't need it in this case. */
    
    return 1;
}
forward OnHousesLoad();
public OnHousesLoad()
{
    // Process the houses selected here.
    return 1; // Delete the cache.
} 

• mysql_pquery
  
  Code:

  Parameters:
  MySQL:handle     -> The connection handle this will be processed on.
  const query[]    -> The query you want to execute.
  const callback[] -> The result callback to call after the query successfully executed (optional).
  const format[]   -> The format specifier string for the result callback (optional).
  {Float,_}:...    -> Indefinite number of arguments (optional).
  
  
  Return values:
  1 if the query was successfully queued for execution, 0 otherwise.

  This function works exactly as the previous one: mysql_tquery. Even so, there's a major difference when it comes to execution order. These are parallel queries, if we send two consecutive queries they might execute at once or just messily. Let's return to the previous example:
  
  PHP Code:

  public OnGameModeInit()
{
    mysql_pquery(db_handle, "CREATE TABLE IF NOT EXISTS `houses` (...)");
    mysql_pquery(db_handle, "SELECT * FROM `houses`", OnHousesLoad);
    /* This code isn't good, avoid it. The SELECT statement might execute before the
    CREATE TABLE one. That wouldn't make sense and it might generate errors. */
    
    return 1;
}
forward OnHousesLoad();
public OnHousesLoad()
{
    return 1;
} 

  So, when should we use these queries? When the execution order doesn't care at all or for huge SELECT statements. Suppose your server has 50 players connected and you send a gmx command, when they reconnect in OnPlayerConnect you'll be sending about 50 queries to your database for the login/register system, right? The order doesn't care in this case, you're going to select the corresponding data for each player in order to allow them login/register. So basically, it doesn't matter which player is processed first/last.
  
  PHP Code:

  public OnPlayerConnect(playerid)
{
    new query[73]; 
    new p_name[MAX_PLAYER_NAME]; 
    GetPlayerName(playerid, p_name, sizeof(p_name)); 
    mysql_format(db_handle, query, sizeof(query), "SELECT COUNT(*) FROM `players` WHERE `name` = '%e'", p_name); 
    
    // Notice we're using mysql_pquery.
    mysql_pquery(db_handle, query, CheckPlayerAccount, "d", playerid); 
    return 1;
}
forward CheckPlayerAccount(playerid);
public CheckPlayerAccount(playerid)
{
    new value;
    cache_get_value_index_int(0, 0, value)
    
    if(value > 0) 
    { 
        // That name is registered. 
    } 
    else 
    { 
        // That name isn't registered. 
    } 
    return 1; // Delete the cache.
} 

  How many queries can run at once?
  It depends on the POOL_SIZE option, by default it's set to two but you can change its value with mysql_set_option or set it inside the .ini file used by mysql_connect_file. If you want to know more about these functions take a look to the first tutorial.

[*] MySQL transactions
The current plugin supports MySQL transactions but they cannot be used with all the functions we've been talking about before, only mysql_tquery supports them. Let's analyze a hypothetical case: suppose you're leaving a mod garage with your tuned Elegy, how do you save its components? Well, before anything you must create a table to add them to. Table's structure should look something like this:

PHP Code:

CREATE TABLE IF NOT EXISTS `vehicle_mods` (
  `vehicleid` mediumint(11) NOT NULL,
  `slot` tinyint(4) NOT NULL,
  `componentid` smallint(6) NOT NULL,
  UNIQUE KEY `vehicleid` (`vehicleid`,`slot`),
  FOREIGN KEY (`vehicleid`) REFERENCES `vehicle_info`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/* vehicleid is the SQL ID of the vehicle which we've saved inside the vehicle_info table. */ 


Now we have to loop through vehicle's mod slots to check if they containt any component:

PHP Code:

public OnEnterExitModShop(playerid, enterexit, interiorid)
{
    if(!enterexit)
    {
        new componentid;
        for(new i = 0; i < 14; i++)
        {
            componentid = GetVehicleComponentInSlot(GetPlayerVehicleID(playerid), i);
            if(componentid != 0)
            {
                // Send save query here.
            }
        }
    }
    return 1;
} 


So how should the query look? Does the execution order matter? It doesn't. Well, then this should do the job:

PHP Code:

mysql_format(db_handle, query, sizeof(query),
    "INSERT INTO `vehicle_mods` VALUES(%d, %d, %d) ON DUPLICATE KEY UPDATE `componentid` = %d;",
    v_Info[idx][SQLID],
    i,
    componentid,
    componentid
);
mysql_pquery(db_handle, query); // Notice we're using mysql_pquery. 


That works fine. If the mods use all 14 slots then 14 queries are sent, we don't know which one finishes first but it doesn't care. Keep in mind that if the POOL_SIZE is set to two only two run at once, then another two and so on. You might set the POOL_SIZE to 14 to run all them at once.

That technique isn't bad at all, it works, but we can do it better: MySQL transactions. This would be even faster:

PHP Code:

mysql_tquery(db_handle, "START TRANSACTION;"); // Start transaction.
for(new i = 0; i < 14; i++)
{
    componentid = GetVehicleComponentInSlot(GetPlayerVehicleID(playerid), i);
    if(componentid != 0)
    {
        mysql_format(db_handle, query, sizeof(query),
            "INSERT INTO `vehicle_mods` VALUES(%d, %d, %d) ON DUPLICATE KEY UPDATE `componentid` = %d;",
            v_Info[idx][SQLID],
            i,
            componentid,
            componentid
        );
        mysql_tquery(db_handle, query); // Notice we're using mysql_tquery.
    }
}
mysql_tquery(db_handle, "COMMIT;"); // Commit transaction. 


A transaction supports different statements, you don't have to run only SELECT, INSERT, etc. statements inside but whatever you want. This isn't a SQL tutorial, I'm trying to document plugin's functions but here you have a little example just to make you understand what I mean:

PHP Code:

mysql_tquery(db_handle, "START TRANSACTION;");
mysql_tquery(db_handle, "SELECT * FROM ...", ...);
mysql_tquery(db_handle, "INSERT INTO ...", ...);
mysql_tquery(db_handle, "UPDATE ...", ...);
mysql_tquery(db_handle, "COMMIT;"); 


[*] mysql_format or format?
Many people asked me about this, it's really easy: mysql_format uses the charset you've set with mysql_set_charset, if I'm not wrong by default it's set to utf8. I'm not really sure about which charset uses SA-MP's format function but I think it's utf8 also.

So, supposing both use the same charset by default, why would I use mysql_format? It has special format specifiers, like %e, which escapes strings automatically instead of using mysql_escape_string. Check the documentation to see what other specifiers it supports.


[*] How to create a table correctly
As I told you before, we should make sure our tables have been created before using SELECT statements. Currently we can run SQL statements written in external files using:

• mysql_query_file: reads all queries from the specified file and executes them in a non-threaded manner.
• mysql_tquery_file: reads all queries from the specified file and executes them in a threaded manner.

It doesn't care if we send a non-threaded query in OnGameModeInit, it'll run only once, so doing something like this works fine:

PHP Code:

public OnGameModeInit()
{
    mysql_query_file(db_handle, "my_tables.sql");
    LoadGarages(); // SELECT statement inside.
    return 1;
} 


• my_tables.sql
  
  PHP Code:

  CREATE TABLE IF NOT EXISTS `garage_info` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `typeid` tinyint(4) NOT NULL,
  `price` mediumint(9) NOT NULL,
  `level` tinyint(4) NOT NULL,
  `x` float NOT NULL,
  `y` float NOT NULL,
  `z` float NOT NULL,
  `angle` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `garage_owners` (
  `garageid` mediumint(9) NOT NULL,
  `ownerid` mediumint(9) NOT NULL,
  UNIQUE KEY `garageid` (`garageid`,`ownerid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
// Notice we're using CREATE TABLE IF NOT EXISTS in both cases. 


That's just an example, but it would work.


[*] The end
This is where the second tutorial finishes, on the next one we're going to see how to make a login/register system using Whirlpool hashes. You might follow the development of the MySQL gamemode I'm writing for these tutorials on GitHub, that way you could see and understand better what I do.

Thank you for reading my thread, if you want to support what I do you might buy me a coffe! Subscribe to my ******* channel if you wish to follow the development of my gamemode.

Please, if you find any error let me know so I can correct it.

Arrivederci!

[Eoussama]

Splendid, learned a thing or two, thanks for your effort, I hope you cover some salting methods on the next tutorial too.

[Logic_]

I'm out of words to explain about the tutorial. Beautifully and amazingly done with usage of good vocabulary that I know about but never used xD.

One of the errors that I noticed are that you did not mention which image represents what (a threaded query or a non threaded) @ Threaded and non-threaded queries. I also at first sight got confused but since I know how MySQL works, I understood it. But to a completely new person who is trying to learn or improve their skills would get stuck!

[Virtual1ty]

Beautifully written tutorial. Great explaining. I'll for sure be checking it when porting my script(s) that use the old version(R7) of the MySQL plugin.

[Kaperstone]

Finally a tutorial I can link people to when they use mysql_query


I haven't read the tutorial but from the looks of it seems well detailed.

[Logic_]

Sorry for the bump but there are two reasons behind it:

1. It's a very good tutorial and almost everyone who uses MySQL should read this.
2. Secondly, can you make a tutorial based on the database relations? The one by Vince is good too but it's kind of outdated and your explanation is better (or that's what I've felt!)