SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Tutorials

Reply
 
Thread Tools Display Modes
Old 03/09/2013, 12:03 PM   #1
AndreT
Gangsta
 
AndreT's Avatar
 
Join Date: Jul 2011
Posts: 966
Reputation: 414
Default MySQL plugin ORM usage tutorial

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.

Last edited by AndreT; 08/09/2013 at 11:33 AM.
AndreT is offline   Reply With Quote
Old 03/09/2013, 12:13 PM   #2
IstuntmanI
High-roller
 
IstuntmanI's Avatar
 
Join Date: Mar 2009
Location: Romania
Posts: 1,801
Reputation: 375
Default Re: MySQL plugin ORM usage tutorial

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).
__________________


IstuntmanI is offline   Reply With Quote
Old 03/09/2013, 12:29 PM   #3
maddinat0r
Banned
 
Join Date: Jun 2010
Location: Germany
Posts: 845
Reputation: 482
Default Re: MySQL plugin ORM usage tutorial

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.
maddinat0r is offline   Reply With Quote
Old 03/09/2013, 02:14 PM   #4
Misiur
High-roller
 
Misiur's Avatar
 
Join Date: Jul 2009
Location: Poland
Posts: 2,528
Reputation: 556
Default Re: MySQL plugin ORM usage tutorial

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.
Misiur is offline   Reply With Quote
Old 03/09/2013, 02:42 PM   #5
x96664
Gangsta
 
x96664's Avatar
 
Join Date: Jun 2009
Posts: 585
Reputation: 7
Default Re: MySQL plugin ORM usage tutorial

Nice tutorial, I thought that the tutorial would be released after releasing the plugin version.
x96664 is offline   Reply With Quote
Old 03/09/2013, 04:28 PM   #6
maddinat0r
Banned
 
Join Date: Jun 2010
Location: Germany
Posts: 845
Reputation: 482
Default Re: MySQL plugin ORM usage tutorial

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.
maddinat0r is offline   Reply With Quote
Old 03/09/2013, 05:22 PM   #7
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 10,068
Reputation: 2658
Default Re: MySQL plugin ORM usage tutorial

To me this all seems unnecessarily complicated. I'm sticking with standard queries. Everyone their preference, I guess.
__________________
Vince is offline   Reply With Quote
Old 03/09/2013, 05:26 PM   #8
iZN
High-roller
 
Join Date: Jun 2010
Posts: 2,369
Reputation: 569
Default Re: MySQL plugin ORM usage tutorial

Wow great tutorial AndreT, this will help me and others to use that ORM feature.
__________________
iZN is offline   Reply With Quote
Old 03/09/2013, 06:12 PM   #9
dusk
High-roller
 
dusk's Avatar
 
Join Date: Jul 2008
Posts: 1,113
Reputation: 46
Default Re: MySQL plugin ORM usage tutorial

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?
dusk is offline   Reply With Quote
Old 03/09/2013, 06:37 PM   #10
maddinat0r
Banned
 
Join Date: Jun 2010
Location: Germany
Posts: 845
Reputation: 482
Default Re: MySQL plugin ORM usage tutorial

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.
maddinat0r is offline   Reply With Quote
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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mysql usage problem XrespenserX Scripting Help 0 11/12/2011 06:44 PM
CPU Usage plugin? sciman001 Scripting Help 6 11/12/2011 03:02 AM
mysql plugin. loading plugin failed ikkentim Help Archive 2 10/02/2011 02:32 PM
[MYSQL-TUTORIAL FOR CITYWIDE]How to use G-stylez plugin for City WIDE! Julian2574 Help Archive 2 17/11/2010 05:01 PM
Is there a tutorial for the MYSQL plugin? dirkblok Help Archive 12 23/01/2010 10:17 PM


All times are GMT. The time now is 12:01 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.