[AndreT]

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.