[Tutorial] MySQL plugin ORM usage tutorial
#1

This is a tutorial for the ORM feature of the MySQL plugin versions r33 and above. ORM stands for object-relational mapping and what it means in the MySQL plugin and PAWN scripting context, I'll try to explain in this tutorial.

This feature written by Pain123 is intended to make front-end scripting a lot easier for people who just want to create awesome content instead of having to worry too much about the backend behavior and/or how to operate the SQL language. So all the querying is done for you by the plugin.

First thing that needs to be done is creating an ORM instance by calling id = orm_create(table_name[]) where the table_name array consists of your MySQL table name that you wish the ORM instance to control. Now some PAWN global (or static) variables must be "tied" to the ORM system by calling functions like (id is the return value of orm_create):
pawn Code:
//global variables
new kills, Float:kd_ratio, name[MAX_PLAYER_NAME+1];

// ... in a function somewhere
orm_addvar_int(id, kills, "kills"); // orm_addvar_int(ORM:id, variable, field_name[])
orm_addvar_float(id, kd_ratio, "ratio"); // orm_addvar_float(ORM:id, Float:variable, field_name[])
orm_addvar_string(id, name, sizeof(name), "name"); // orm_addvar_string(ORM:id, array[], max_len, field_name[])
To walk you through this a bit: these lines of code add 3 entries to "keep track of" for the ORM feature of the MySQL plugin. These three are actually macros that all call orm_addvar, but that is irrelevant at the moment. Your database must contain the field name you pass to these functions. And the variable must be either global or static - in other words it cannot be on the stack.

Now that you grasp the idea of what this plugin does, or if not, I'll have a go at saying this again - keep track of your variables and call all the queries for you, we should get to a more realistic example, a scenario we might actually find in a script. The idea of this script is to create an user system.
pawn Code:
#include <a_mysql>

enum E_PLAYER {
    ORM:ORM_ID,
    ID,
    Name[MAX_PLAYER_NAME+1],
    Money,
    Level,
    Float:PosX,
    Float:PosY,
    Float:PosZ,
};
new Player[MAX_PLAYERS][E_PLAYER];

public OnPlayerConnect(playerid)
{
   
    GetPlayerName(playerid, Player[playerid][Name], MAX_PLAYER_NAME);
    new ORM:ormid = Player[playerid][ORM_ID] = orm_create("players");

    orm_addvar_int(ormid, Player[playerid][ID], "ID");
    orm_addvar_string(ormid, Player[playerid][Name], MAX_PLAYER_NAME+1, "Name");
    orm_addvar_int(ormid, Player[playerid][Money], "Money");
    orm_addvar_int(ormid, Player[playerid][Level], "Level");
    orm_addvar_float(ormid, Player[playerid][PosX], "PosX");
    orm_addvar_float(ormid, Player[playerid][PosY], "PosY");
    orm_addvar_float(ormid, Player[playerid][PosZ], "PosZ");
   
    orm_setkey(ormid, "Name");
    orm_select(ormid, "OnPlayerDataLoad", "d", playerid);
    return 1;
}

forward OnPlayerDataLoad(playerid);
public OnPlayerDataLoad(playerid)
{
    switch(orm_errno(Player[playerid][ORM_ID]))
    {
        case ERROR_OK: {
            ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "Login", "Please insert your password.", "Login", "Abort");
        }
        case ERROR_NO_DATA: {
            ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_PASSWORD, "Register", "Please insert your password.", "Register", "Abort");
        }
    }
    orm_setkey(Player[playerid][ORM_ID], "ID"); // Set a new key to use WHERE `ID` = ... in future queries!
    return 1;
}

public OnPlayerDisconnect(playerid, reason)
{
    if(Player[playerid][ID] != 0) {
        orm_update(Player[playerid][ORM_ID]);  
    }
    orm_destroy(Player[playerid][ORM_ID]);

    for(new E_PLAYER:e; e < E_PLAYER; ++e)
        Player[playerid][e] = 0;
    return 1;
}
Creating an ORM instance and adding variables/arrays to it should be straightforward in this example. But after that comes orm_setkey, which is required to generate a WHERE-clause for your queries.
This part of OnPlayerConnect:
pawn Code:
orm_setkey(ormid, "Name");
orm_select(ormid, "OnPlayerDataLoad", "d", playerid);
will do the following:
1) Set the internal field to query by to `Name` (the WHERE part of the query)
2) Generate this query: "SELECT `ID`,`Name`,`Money`,`Level`,`PosX`,`PosY`,`PosZ` FROM `players` WHERE `Name`='%s' LIMIT 1" (%s would be the player's name)
3) Execute the query (this is not done in the main thread)
4) Update the data with the queried information.
5) Call OnPlayerDataLoad with playerid as the only parameter once the query finishes

Depending on what the query finds, the plugin sets a value for the error variable, and in your returning query, you must check for errors (unless you're absolutely sure about what you're doing) by using the orm_errno native which can have the following return values:
* ERROR_OK (0)
* ERROR_NO_DATA (1)

So ERROR_OK, for us in this case, denotes that the player's account exists and we can continue with our user authentication process - ask them to login. The values of the returned fields have been set to their respective (see orm_addvar_*) variables, in our case Player[playerid].

But another thing here is that for future better queries, we set a new key field to query by:
pawn Code:
orm_setkey(Player[playerid][ORM_ID], "ID");
This means that any future queries we call with orm_update (as shown in OnPlayerDisconnect), orm_select or orm_delete will look like "... WHERE `ID`='%d'" where %d is Player[playerid][ID]. Once the player disconnects, we delete the ORM instance with orm_destroy(ORM:id). Prior to this, you can call all orm-natives except of orm_select and orm_update.

Should the player decide to register on the server, you will meet the orm_insert function, which inserts the player entry for you. The syntax is similar to that of orm_select:
pawn Code:
orm_insert(Player[playerid][ORM_ID], "OnPlayerRegister", "d", playerid);
// ...
forward OnPlayerRegister(playerid);
public OnPlayerRegister(playerid)
{
    printf("Player %d signed up and their ID value is %d.", playerid, Player[playerid][ID]);
}
You notice that the Player[playerid][ID] value gets updated. This is what is done once an INSERT query finishes, and you must use orm_setkey to the ID variable before calling orm_insert. If you don't do so, the new ID will be saved into the name variable, and that's the last thing we want.

Okay, you should now know how to retrieve (orm_select) and insert (orm_insert) data. But how do we update existing data in the database? That's where we'll need to use orm_update. This native generates an UPDATE-query with all of the current values the registered variables have.
For example, the user "User1" has ID 65 (not playerid, the ID in the player-table), is level 4, has 54634$ and is somewhere in Los Santos. In that case, orm_update would generate this query:
Code:
UPDATE `players` SET `Name`='User1', `Money`='54634', `Level`='3', `PosX`='745.231', `PosY`='-967.1425', `PosZ`='14.2543' WHERE `ID`='65'
We now can generate almost all important types of queries, only the DELETE-query is missing. But don't worry, there is also a native for that: orm_delete. As you can think of, this native generates a DELETE-query. In our example it would generate and send that query:
Code:
DELETE FROM `players` WHERE `ID`='65'
Unlike orm_insert, orm_delete has an extra (optional) parameter named "clearvars". If you set this to true, orm_delete will not only erase the correct record in the corresponding table, it will also reset the registered variables by setting their values to 0 (all registered variables, even the key).


Through this tutorial we used as example player data, but you shouldn't restrict yourself to this! You can manage any data you want, for example vehicle data or house data. Here is one example on how you could load vehicles with the ORM system:
pawn Code:
new SQL = -1;

enum e_Vehicle
{
    ORM:ORM_ID,
    VID,
    ID,
    ModelID,
    Color1,
    Plate[32],
    Float:Pos[4],
};
new Vehicle[MAX_VEHICLES][e_Vehicle];


public OnGameModeInit()
{
    mysql_log();
   
    SQL = mysql_connect("127.0.0.1", "root", "test", "pass");
   
    //load vehicles
    mysql_tquery(SQL, "SELECT * FROM `vehicles`", "OnVehiclesLoad", "");
    return 1;
}


forward OnVehiclesLoad();
public OnVehiclesLoad()
{
    for(new r=0; r < cache_num_rows(); ++r) {
        new ORM:ormid = Vehicle[r][ORM_ID] = orm_create("vehicles");

        orm_addvar_int(ormid, Vehicle[r][ID], "ID"); //this is the key
        orm_setkey(ormid, "ID"); //here we declare it as the key
        orm_addvar_int(ormid, Vehicle[r][ModelID], "ModelID");
        orm_addvar_int(ormid, Vehicle[r][Color1], "Color1");
        orm_addvar_string(ormid, Vehicle[r][Plate], 32, "Plate");
        orm_addvar_float(ormid, Vehicle[r][Pos][0], "PosX");
        orm_addvar_float(ormid, Vehicle[r][Pos][1], "PosY");
        orm_addvar_float(ormid, Vehicle[r][Pos][2], "PosZ");
        orm_addvar_float(ormid, Vehicle[r][Pos][3], "PosA");

        orm_apply_cache(ormid, r);

        Vehicle[r][VID] = CreateVehicle(Vehicle[r][ModelID], Vehicle[r][Pos][0], Vehicle[r][Pos][1], Vehicle[r][Pos][2], Vehicle[r][Pos][3], Vehicle[r][Color1], -1, -1);
    }

    return 1;
}
What does this code do?
It sends a query to fetch all the vehicle data (the query in OnGameModeInit) from the "vehicles" table. Then it iterates through the rows of the cache in the called public. For every row, one ORM instance is created and variables are assigned (you should now be familiar with it). Then there is a new native called we didn't covered before, orm_apply_cache. What does it do exactly? It takes the current, active cache and looks, if there are fields which were specified previously with orm_addvar in it. In the example above, that means that it will look for fields with the name "ID", "ModelID", "Color1" and so on. orm_apply_cache takes one additional parameter though: a row number, where the system will search in that row for the fields. If a field is found, its value is taken and assigned to the corresponding variable.

I hope users will find this new feature useful and this tutorial will help them along the road.

Thanks to Pain123 for collaborating on the tutorial as well as for the idea.
Reply
#2

Great tutorial, this one will make working with ORM much simpler. But I don't understand (you explained, but still...) what it is with orm_apply_cache (Pain123 didn't explained me before, and I didn't seen this function before, I guess it's from a newer r33 build).
Reply
#3

orm_apply_cache is the same as orm_select, but without sending a query. One could say orm_select is calling orm_apply_cache automatically before calling the public.
Reply
#4

Just a little tip: orm is not some magical one-fits-all tool, and still it's really good to know how things work underneath - SQL is really, really powerful. But, for most of your cases, using orm will speed up development a lot so read this tutorial carefully.
Reply
#5

Nice tutorial, I thought that the tutorial would be released after releasing the plugin version.
Reply
#6

I asked AndreT to pre-release it, so people have time to read and understand it a bit. But don't worry, R33 is coming soon.
Reply
#7

To me this all seems unnecessarily complicated. I'm sticking with standard queries. Everyone their preference, I guess.
Reply
#8

Wow great tutorial AndreT, this will help me and others to use that ORM feature.
Reply
#9

This might be a stupid question but: is the plugin R33 released, or am i blind? Because I can't see it in it's project home.

Regarding this feature: you said it's for "people who just want to create awesome content instead of having to worry too much about the backend behavior and/or how to operate the SQL language", but if I know a thing or two about the basic queries, can i get any advantages out of it?
Reply
#10

Don't worry, you aren't blind. I'm currently working on R33 and I'm trying hard to release it as soon as possible.
Reply
#11

Thanks for the answer, but i have another question: where is R32? The last available download at projects home is R31.. And now you say R33 is coming up... Isn't there something missing?

What about my last post's second part?
Reply
#12

Quote:
Originally Posted by dusk
View Post
Thanks for the answer, but i have another question: where is R32? The last available download at projects home is R31.. And now you say R33 is coming up... Isn't there something missing?

What about my last post's second part?
On the top of the list
Reply
#13

Quote:
Originally Posted by x96664
View Post
Oh, I was being myself and looking only at the "download" section, thanks for that
Reply
#14

First of all, thanks for the feedback, everybody.

Quote:
Originally Posted by Vince
View Post
To me this all seems unnecessarily complicated. I'm sticking with standard queries. Everyone their preference, I guess.
I agree with you a bit, I think there could be a way to make the usage a bit less complicated, for example setting up the variables up when the gamemode starts up to provide a better overview and perhaps even better speed.
Reply
#15

Quote:
Originally Posted by AndreT
View Post
First of all, thanks for the feedback, everybody.


I agree with you a bit, I think there could be a way to make the usage a bit less complicated, for example setting up the variables up when the gamemode starts up to provide a better overview and perhaps even better speed.
Speaking about speed, could we see some speed tests? Or is there no need for them?
Reply
#16

Quote:
Originally Posted by dusk
View Post
Speaking about speed, could we see some speed tests? Or is there no need for them?
http://forum.sa-mp.com/showpost.php?...postcount=4171

Quote:
Originally Posted by orm benchmarks
10 runs with 5000 iterations

Windows (4x3.4GHz, logging disabled)
-------
orm_select:
324.8999ms
0.0649ms

orm_update:
26.0000ms
0.0052ms

orm_insert:
349.3999ms
0.0698ms

-----------------------------------------------


Linux (4x3.4GHz, logging disabled)
-----
orm_select:
183.1999ms
0.0366ms

orm_update:
21.7999ms
0.0043ms

orm_insert:
247.0000ms
0.0494ms
The first time is the overall time for 5000 iterations, the second time is the time per iteration.
Reply
#17

Quote:
Originally Posted by Pain123
View Post
http://forum.sa-mp.com/showpost.php?...postcount=4171


The first time is the overall time for 5000 iterations, the second time is the time per iteration.
Aha, but it is faster! Not like i run queries 10000 times... but still, it's a step forward! That means i should(and will) use this

Thanks for the results
Reply
#18

which one is better? cache or ORM for save player info's?
Reply
#19

Quote:
Originally Posted by Aliassassin123456
View Post
which one is better? cache or ORM for save player info's?
They're both the same, the ORM-way is just easier (at least I hope it is).
Reply
#20

Thanks for answer so there isn't any reason to replace cache's with ORM
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)