[Tutorial] MySQL Threaded queries with y_inline

[Tutorial] MySQL Threaded queries with y_inline - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Tutorials (https://sampforum.blast.hk/forumdisplay.php?fid=70)
+---- Thread: [Tutorial] MySQL Threaded queries with y_inline (/showthread.php?tid=548986)

Pages: 1 2

MySQL Threaded queries with y_inline - Jay_ - 03.12.2014

Introduction

Multi-threaded programming is an extremely difficult pattern that a lot of developers in the Software Engineering world struggle with.

Whilst PAWN doesn't introduce a lot of the complexities present in most other languages out there, it's still difficult to keep your code neat and organised when threading your MySQL queries as you have to break your code up into separate functions and callbacks.

This is a basic tutorial on how you can improve your code structure by using y_inline, a third-party YSI library that's supported by the MySQL plugin.

I'm writing this because I haven't seen any scripts that use this so I'm assuming not many people know about it.

Prerequisites

MySQL plugin version R33 onwards and y_inline.

The Tutorial

Here's an example of how a threaded query might look using the standard syntax of the MySQL plugins API:

pawn Код:

// Initiaited when the player connects to the server. 
construct(playerid) {
    new query[256];
    mysql_format(getMySQLHandle(), query, sizeof(query), "SELECT * FROM %s WHERE PlayerName = '%e' LIMIT 1", 
                                            getMySQLTableName(), ReturnPlayerName(playerid));

    mysql_tquery(getMySQLHandle(), query, "OnAccountDataReceived", "i", playerid);
}

forward OnAccountDataReceived(playerid);
public OnAccountDataReceived(playerid) {
    new
        rows,
        fields,
        fieldString[128];
    
    cache_get_data(rows, fields, getMySQLHandle());
    if(rows > 0) {
        isThisPlayerRegistered[playerid] = true;

        // retrieve the primary key for the user id
        setUserID(playerid, cache_get_field_content_int(0, "ID"));

        //  Retrieve the encrypted password for validation later
        cache_get_field_content(0, "Password", fieldString);
        setEncryptedPassword(playerid, fieldString);
    } else {
        isThisPlayerRegistered[playerid] = false;
    }
}

This isn't ideal - we're sending the query request from one function and receiving the data from another!
This is messy and horrible to work with. Given, it doesn't look too bad in this example but when dealing with complex systems it can become really hacky and ugly.

In an ideal world, we want to be able to deal with the data returned from the threaded query in the same function - directly under the mysql_query call.

Unfortunately, that's not possible to do. But what we can do is embed our OnAccountDataReceived callback into the construct function using y_inline, so that our mysql_tquery function call and data received callback are all in the same function!

1. Tell the MySQL plugin that we're going to be using y_inline

First of all we need to tell the plugins include script that we are going to be using y_inline. This is really easy to do - simply add an include statement for YSI before you include your MySQL inc. You can do this like so:

pawn Код:

#include "YSI/y_inline.inc"

2. Implement the code!

The following piece of code is a threaded MySQL query which makes use of y_inline - the query is called within the same (parent) function in which the threaded callback returns the data.

pawn Код:

// Initiaited when the player connects to the server. 
construct(playerid)
{
    new query[256];
    mysql_format(getMySQLHandle(), query, sizeof(query), "SELECT * FROM %s WHERE PlayerName = '%e' LIMIT 1", 
                                            getMySQLTableName(), ReturnPlayerName(playerid));

    inline OnAccountDataReceived()
    {
        new
            rows,
            fields,
            fieldString[128],
            fieldInt;
        
        cache_get_data(rows, fields, getMySQLHandle());
        if(rows > 0) {
            isPlayerRegistered[playerid] = true;

            // Set the user ID, i.e. the primary key
            setUserID(playerid, cache_get_field_content_int(0, "ID"));

            // Set the encrypted password
            cache_get_field_content(0, "Password", fieldString);
            setEncryptedPassword(playerid, fieldString);
        }
        else {
            isPlayerRegistered[playerid] = false;
        }
    }
    mysql_tquery_inline(getMySQLHandle(), query, using inline OnAccountDataReceived);
}

And that's it! Simple eh, and it looks much better.

The call to mysql_tquery has changed. The function is called mysql_tquery_inline, and the third argument for the callback no longer accepts a string - the syntax is:

Код:

using inline [function name]

Note:
The inline child function must be defined before the call to mysql_tquery, as in the above code. In an ideal world it would be the other way round but this is just the way y_inline works unfortunately.

More information: https://sampforum.blast.hk/showthread.php?tid=295049

Re: MySQL Threaded queries with y_inline - PT - 04.12.2014

I was already using y_inline on Dialogs but not on MySQL.

What you mean with this two:

Quote:

* There is no support for strings or arrays
* There must always be at least one parameter inside in the inline function

Can you please provide an example?

Thanks

- Anyway nice job is an nice tutorial.

Re: MySQL Threaded queries with y_inline - Lordzy - 04.12.2014

Nice tutorial, calling out threaded queries just once outside a function was always a mess.

Quote:

Originally Posted by PT

I was already using y_inline on Dialogs but not on MySQL.

What you mean with this two:

Can you please provide an example?

Thanks

- Anyway nice job is an nice tutorial.

It means that you cannot use arrays (multi dimensional variables) within the inline function and also while passing an inline function, you must have ATLEAST 1 parameter.

pawn Код:

inline Query_OnFetchName(nick[])
//I suppose this isn't possible because 'nick' is an array.
//EDIT: YOU CAN ACTUALLY;
inline Query_OnFetchName(string:nick[])
//You must use the "string:" tag for strings.


inline Query_GetCounts() 
//Neither possible because it doesn't have any parameter.

I don't use YSI, but from what Jay_ has mentioned, I suppose that's it. Feel free to correct me if I'm wrong.

Re: MySQL Threaded queries with y_inline - Kaperstone - 04.12.2014

I don't understand why everyone use

Quote:

if(rows > 0)

And not simply if(rows), it do the same job, but without burning extra calories on writing " > 0"

Anyways, thanks for the tutorial !

Re: MySQL Threaded queries with y_inline - maddinat0r - 04.12.2014

Nice tutorial! But AFAIK strings are supported (you have to explicitly tag them though, like "inline Func(string:name)" (not sure if you have to add the brackets) and functions without parameters should work too (not quite sure about this though).
You also don't need to define "MYSQL_USE_YINLINE", just include "YSI/y_inline.inc" before including "a_mysql.inc", the MySQL include should automatically detect the y_inline include and enable support for inlined functions.

Re: MySQL Threaded queries with y_inline - iReacheR - 04.12.2014

Sweet! This just really helped with all the searching through the script for the thread function of a query and then to and fro to the query to edit stuff and manage it all properly.

Re: MySQL Threaded queries with y_inline - Lordzy - 04.12.2014

y_inline actually supports strings, maddinat0r is right. The topic mentioned was an old one compared to the documentation of y_inline which ****** did later.

https://sampforum.blast.hk/showthread.php?tid=295049

Re: MySQL Threaded queries with y_inline - Jay_ - 04.12.2014

Thanks for raising this. I'll give it a test and make some amendments when I get home tonight.

Re: MySQL Threaded queries with y_inline - Jay_ - 08.12.2014

Ah thanks for the feedback. Seems I was using this topic as a reference - I really should have checked the date and conducted a more recent search.

Updated

Re: MySQL Threaded queries with y_inline - rickisme - 08.12.2014

Hmm, i got this error when try remove "inline_playerid": error 017: undefined symbol "mysql_tquery_inline"

pawn Код:

stock LoadPlayerWeapon(playerid)
{
    new query[255];
    inline LoadWeaponData()
    {
        if(cache_num_rows() > 0)
        {
            for(new i = 0; i < 12; i++)
            {
                new gstr[32];
                format(gstr, sizeof gstr, "Weapon%d", i + 1);
                pInfo[playerid][pGun][i] = cache_get_field_content_int(0, gstr, MySQL);
                format(gstr, sizeof gstr, "Ammo%d", i + 1);
                pInfo[playerid][pGunAmmo][i] = cache_get_field_content_int(0, gstr, MySQL);
                if(pInfo[playerid][pGun][i] > 0 && pInfo[playerid][pGunAmmo][i] > 0)
                {
                    GivePlayerWeapon(playerid, pInfo[playerid][pGun][i], pInfo[playerid][pGunAmmo][i]);
                }
            }
        }
    }
    mysql_format(MySQL, query, sizeof query, "SELECT * FROM player_weapon WHERE UserId = %d LIMIT 1", pInfo[playerid][pID]);
    mysql_tquery_inline(MySQL, query, using inline LoadWeaponData);
}

But it's work fine when i use old way

pawn Код:

stock LoadPlayerWeapon(playerid)
{
    new query[255];
    inline LoadWeaponData(lwd_pid)
    {
        #pragma unused lwd_pid
        if(cache_num_rows() > 0)
        {
            for(new i = 0; i < 12; i++)
            {
                new gstr[32];
                format(gstr, sizeof gstr, "Weapon%d", i + 1);
                pInfo[playerid][pGun][i] = cache_get_field_content_int(0, gstr, MySQL);
                format(gstr, sizeof gstr, "Ammo%d", i + 1);
                pInfo[playerid][pGunAmmo][i] = cache_get_field_content_int(0, gstr, MySQL);
                if(pInfo[playerid][pGun][i] > 0 && pInfo[playerid][pGunAmmo][i] > 0)
                {
                    GivePlayerWeapon(playerid, pInfo[playerid][pGun][i], pInfo[playerid][pGunAmmo][i]);
                }
            }
        }
    }
    mysql_format(MySQL, query, sizeof query, "SELECT * FROM player_weapon WHERE UserId = %d LIMIT 1", pInfo[playerid][pID]);
    mysql_tquery_inline(MySQL, query, using inline LoadWeaponData, "i", playerid);
}

Already try to replace using inline LoadWeaponData to "LoadWeaponData" and got: warning 213: tag mismatch

P/S: MYSQL r39-2 and YSI 4.0 last version

Re: MySQL Threaded queries with y_inline - maddinat0r - 09.12.2014

You need to always specify the callback format string, even if you don't want to pass any parameters.

Code:

mysql_tquery_inline(MySQL, query, using inline LoadWeaponData, ""); //that "" is important

Re: MySQL Threaded queries with y_inline - rickisme - 10.12.2014

thanks maddinat0r, working now

Re: MySQL Threaded queries with y_inline - iReacheR - 14.01.2015

Lets keep this at the top for more users to see this. This is very good for those who get annoyed by the constant searching through the script for MySQL related callbacks.

Re: MySQL Threaded queries with y_inline - PaulDinam - 15.01.2015

Does this support ORM ?

Re: MySQL Threaded queries with y_inline - im - 15.01.2015

this

pawn Code:

mysql_tquery_inline(getMySQLHandle(), query, using inline OnAccountDataReceived);

will thrown the error:

Code:

error 017: undefined symbol "mysql_tquery_inline"

mysql_tquery_inline requires 4 params, so it should be:

pawn Code:

mysql_tquery_inline(getMySQLHandle(), query, using inline OnAccountDataReceived, "");

Someone should edit the tutorial.

Re: MySQL Threaded queries with y_inline - maddinat0r - 17.01.2015

Quote:

Originally Posted by PaulDinam

Does this support ORM ?

Partly yes; there is 'orm_select_inline' and 'orm_insert_inline', though no 'orm_load_inline' or 'orm_save_inline'.

Re: MySQL Threaded queries with y_inline - andyandyy8 - 14.02.2015

Code:

inline OnAccountCheckStatus(pid)
	{
		#pragma unused pid
		if(cache_num_rows())
		{
			banned = true; 
			cache_get_field_content(0, "playerBanReason", playerBanReason);
			cache_get_field_content(0, "playerBannedBy", playerBannedBy);
			playerBanDate = cache_get_field_content_int(0, "playerBanDate");
		}
		else format(playerBanReason, 40, "asdasda");
	}
	mysql_format(conn, string, sizeof(string), "SELECT playerBanReason, playerBanDate, playerBannedBy FROM %s WHERE playerNameBanned = \"%e\" LIMIT 1",Table_Bans,ReturnName(playerid));
	print(string);
	mysql_tquery_inline(conn, string, using inline OnAccountCheckStatus, "");

It doesn't work for me, it isn't called i think. The function has no effect, what is in inline function isn't executed.

Re: MySQL Threaded queries with y_inline - Cypress - 22.02.2015

pawn Code:

stock IsPlayerRegistered(playerid)
{
    printf("CALLED BY ID : %d", playerid);
    
    new query[128];
    mysql_format(mysql, query, sizeof(query), "SELECT * FROM `PLAYERS` WHERE `USERNAME` = '%e' LIMIT 1", PlayerName(playerid));
    mysql_pquery(mysql, query, "OnPlayerAccountCheck", "dd", playerid, pData[playerid][P_MYSQL_RACE_CHECK]);
    printf("%s", query);

    inline OnPlayerAccountCheck(check)
    {
        if (check != pData[playerid][P_MYSQL_RACE_CHECK])
            return KickPlayer(playerid, "MySQL race occured, please reconnect!");

        if (cache_num_rows() > 0)
        {
            cache_get_field_content(0, "PASSWORD", pData[playerid][P_PASSWORD], mysql, 129);

            ShowPlayerCustomDialog(playerid, 2);
            BitFlag_On(fData[playerid], P_REGISTERED);
        }
        else
        {
            ShowPlayerCustomDialog(playerid, 1);

            SendClientMessage(playerid, 0xA6D8A6FF, "New player? /help /rules /cmds");
            GivePlayerMoney(playerid, 3000);
            pData[playerid][P_MONEY] = 3000;

            BitFlag_Off(fData[playerid], P_REGISTERED);
        }
    }
    
    mysql_tquery_inline(mysql, query, using inline OnPlayerAccountCheck, "d", pData[playerid][P_MYSQL_RACE_CHECK]);
    return 1;
}

This code does not get executed after /rcon gmx. It works every time the server is online except when you use gmx.

I spent like 20 minutes to find the issue which pissed me off.

Re: MySQL Threaded queries with y_inline - kurta999 - 12.01.2017

It's still work with R40+?

Re: MySQL Threaded queries with y_inline - renatog - 12.01.2017

Quote:

Originally Posted by kurta999

It's still work with R40+?

Yes, but you have to use this too:
https://github.com/maddinat0r/samp-m...inline-include