Database ORM (MySQL Data Wrapper) v1.1

Backstabnoob · **Plugin Info:**

Database ORM v1.1
Version 1.1 released on 23rd of July, 2014

Table of Contents

Description
Possibilities
Requirements
Installation, configuration
API (Natives and forwards)
Credits
Changelog
Questions and Answers
Examples
Plugins using this
Support me

Description top

Database ORM is a complex API designed to give 3rd party plugins easy access to the database, while making sure nothing goes wrong by using wrong and exhausting queries. This is typically good for servers with very high load on the MySQL database and for people who don't know much about MySQL. The intelligent query divider system inserts/updates as many rows as possible while minimizing the amount of queries.

The usage, however, can be challenging in some cases and requires intermediate knowledge in Pawn. Please refer to the examples in the topic to make sure you really know what you're doing, as while being very powerful, this can also be a devastating tool if used incorrectly.

WARNING: Please note that this is a beta release and it probably contains some bugs. I tested it as much as I could by myself, but that's a little hard without real-time simulation. With this, I'd also like to ask you guys if you could try testing this as much as you can and give me any feedback.

Possibilities top

This system can be useful in a lot of ways, but there are some things it really isn't designed to be used for.

This should be used for:
Inventories

Player XP

Player stats

Banks

Server variables (zones, global bank account,...)

Etc.

This shouldn't be used for:
Things that can be replaced by a simple config or cvars

Other things I can't think of right now

Simply put: Use common sense please. Don't use this for things that would be overkill.

Requirements top

There are only two requirements in particular:
Latest 1.8.3 snapshot from here: http://www.amxmodx.org/snapshots.php. I used git3921.
This might not be needed, but is HIGHLY recommended, especially for Linux as 1.8.2 was giving me segmentation faults. It seems that cellvectors had problems in 1.8.2 which they don't in 1.8.3 PLEASE USE THIS VERSION.

MySQL server reachable with AMXX.

STEAM! This only works with STEAM_0:0:XXX and STEAM_0:1:XXX Steam IDs.

Installation, configuration top

To install:
Simply download the .sma file, compile it and put it into your plugins folder on the server. Add database_orm.amxx at the end of plugins.ini. Then download the orm_config.ini file and put into the configs folder.

To configure:
Open orm_config.ini. You will see the following values:
orm_hostname - the hostname to connect to. This is usually 'localhost' or '127.0.0.1'.

orm_username - the username for the MySQL server. This is usually 'root'.

orm_password - the password for the MySQL server. This is usually blank for local servers.

orm_database - the database of the MySQL server. There is no default one, you need to create it unless you already have it.

orm_saveperiod - this is how often you want to save the data in seconds. Notice: Data is also being saved automatically on player's disconnect and on plugin change. This is a very intensive process with lots of plugins using the core, so I highly recommend setting this to 0.0 to completely disable it if your server has short map times (20-30 minutes). This feature should be used only on servers which are either highly unstable or are running the same map without mapchange 24/7. In that case, I recommend setting this to 20 minutes (1200.0 seconds).

orm_preparedquery - this is a complex variable which decides what "tasks" or "events" should be saved with prepared queries rather than threaded queries. The difference between those two is that threaded queries take much longer to process, but don't lag the server at all, while prepared queries will be quickly done but they always wait for response, so there is a noticeable lag (with huge amount of data this can be in seconds). This setting only works for saving, not for loading. It's a flag value and the possible flags are:
a - Single player save (such as on their disconnect)

b - All players save (happens only during the save cycle, if it's 0 this is never called)

c - On static save (static classes have nothing to do with players and they reside in the memory since the server load, more on that later in the API section), this is called only during the save cycle, same as 'b'

d - On item delete - whenever an item is deleted with the CSRP_Delete{Player/Static}ClassItem( ) native

e - On plugin end - when the map is changing (saves all players and all static classes), recommended, although the difference is noticeable only on high load because plugin_end dispatches all threaded queries instantly as far as I know

f - On class nuke - When a class is nuked with the CSRP_Nuke{Player/Static}Class( ) native

orm_showsavemsg - shows a progress via print_center during saving cycles. The value doesn't matter if orm_saveperiod is 0, as the save never happens, only on the plugin end where this is unnecessary.

API (Natives and forwards) top

I'll put the .inc file here, the natives and forwards should be explained in it. It won't tell you anything when it comes to writing down the actual plugins, you should look at the examples for that kind of stuff.

There's one BIG thing to know before making a plugin, though, and that is the difference between Player and Static classes.

What are the player classes?
Player classes hold data for players.They are "three-dimensional" classes, with the first dimension being the player index, second dimension being the item itself and third dimension being the item values. The data is being automatically loaded on player connect and unloaded on player disconnect.

The structure for a class with the columns item_index(VARCHAR 32) and item_amount(INT 11) looks something like this:
Spoiler
Code:
Array(  // the class
    [0] => Array( // player with ID 1
        [0] => Array(  // item 0
            [0] => "item_hamburger" // item_index
            [1] => 14   // item_amount
        )
        [1] => Array(  // item 1 
            [0] => "item_m4a1"  // item_index
            [1] => 1  // item_amount
        )
    )
    [1] => Array( // player with ID 2
        [0] => Array(  // item 0
            [0] => "item_chickensteak" // item_index
            [1] => 6   // item_amount
        )
        [1] => Array(  // item 1 
            [0] => "item_foo"  // item_index
            [1] => 32  // item_amount
        )
    )
    // etc.
)
What are static classes?
Static classes hold the data for the server itself and are loaded into the memory at the plugin start and unloaded on mapchange/exit/restart (plugin end). They are "two-dimensional" classes with the first dimension being the item itself and the second dimension being the item values.

The structure for a class with the columns property_owner(INT 11) and property_name(VARCHAR 32) looks something like this:
Spoiler
Code:
Array( // the class
    [0] => Array( // item 0
        [0] => 1415551   // property_owner
        [1] => "Post office"   // property_name
    )
    [1] => Array( // item 1
        [0] => 2515222   // property_owner
        [1] => "Supermarket" // property_name
    )
)
It is very simple once you get it down. Please ask if you're unsure about something.

The .INC file itself:

PHP Code:

// I don't really know what this does. #pragma library "database_orm" /************************************************** ************ API ENUMS AND STRUCTURES ************ *************************************************/ enum ClassColumnType { Column_Varchar, Column_Int }; // Usage: new Array:Columns = ArrayCreate(ClassColumnStruct, Number_Of_Columns) enum _: ClassColumnStruct { _ColumnName[16], // name of the column ClassColumnType: _Column, // either Column_Varchar or Column_Int _MaxLen // maxlen of the field. Use 11 for ints. }; enum Class { Invalid_Class = 0 }; /**************************************** ************ PLAYER CLASSES ************ ***************************************/ /* CSRP_CreatePlayerClass(): Creates a Player class. * @param (String) ClassName: name of the class (= table name in the database), must be unique * @param (Array) Columns: must contain the columns of the database. The array cannot ever be deleted! See examples. * @param (Int) CompositeKey: if -1, the player index will be unique (meaning only one row per player in the table), otherwise it's a combination of two columns (such as player index and item index). * @param (String) ClassCallback: function to call on class initialization. * @return: (Class) handle of the class. */ native Class:CSRP_CreatePlayerClass(const ClassName[], Array:Columns, CompositeKey = -1, const ClassCallback[]); /* CSRP_GetPlayerClassData(): Retrieves all of the data for player object in the class. * @param (Class) hClass: The class handler * @param (Int) Player: index of the player * @return: (Array) Dynamic array with all of the data ready to be looped. Use ArraySize(Array) for the amount of items. */ native Array:CSRP_GetPlayerClassData(Class:hClass, Player); /* CSRP_FindInPlayerClassString(): Finds an entry in the class for player by a string of the composite key. Composite key required. * @param (Class) hClass: The class handler * @param (Int) Player: index of the player * @param (String) Find: the string to look for * @param-byref (Int) ItemIndex: Optionally returns the index of the element in the array * @return: (Array) item handler if found, Invalid_Array if nothing exists or an error occured */ native Array:CSRP_FindInPlayerClassString(Class:hClass, Player, Find[], &ItemIndex=-1); /* CSRP_FindInPlayerClassCell(): Finds an entry in the class for player by a cell of the composite key. Composite key required. * @param (Class) hClass: The class handler * @param (Int) Player: index of the player * @param (Any) Find: the cell to look for * @param-byref (Int) ItemIndex: Optionally returns the index of the element in the array * @return: (Array) item handler if found, Invalid_Array if nothing exists or an error occured */ native Array:CSRP_FindInPlayerClassCell(Class:hClass, Player, any:Find, &ItemIndex=-1); /* CSRP_FindInPlayerClassUnique(): Finds an entry in the class for a player. The class must not have a composite key. * @param (Class) hClass: The class handler * @param (Int) Player: index of the player * @return: (Array) item handler if found, Invalid_Array if nothing exists or an error occured */ native Array:CSRP_FindInPlayerClassUnique(Class:hClass, Player); /* CSRP_PushPlayerClassItem(): Pushes an item to the class for player. The array must have the exact same amount of elements as there are columns in the class. * @param (Class) hClass: The class handler * @param (Int) Player: index of the player * @param (Array) Item: handler of the array with the item. Must not be destroyed! * @return: (Int) index of the item usable within CSRP_{Get/Delete}PlayerClassItem */ native CSRP_PushPlayerClassItem(Class:hClass, Player, Array:Item); /* CSRP_GetPlayerClassItem(): Retrieves an item handle for player by its index. * @param (Class) hClass: The class handler * @param (Int) Player: index of the player * @param (Int) ItemIndex: The array index to find. * @return: (Array) item handler if found, Invalid_Array if nothing exists or an error occured */ native Array:CSRP_GetPlayerClassItem(Class:hClass, Player, ItemIndex); /* CSRP_DeletePlayerClassItem(): Deletes an item from the player object in the player class by index. * @param (Class) hClass: The class handler * @param (Int) Player: index of the player * @param (Int) ItemIndex: the item to delete * @return: (Int) 1 on success, 0 on failure */ native CSRP_DeletePlayerClassItem(Class:hClass, Player, ItemIndex); /* CSRP_SavePlayerClass(): Saves the class, either for one player or for all players. This should be used in the CSRP_OnSave() forward. * @param (Class) hClass: The class handler * @param (Int) Player: index of the player. If 0, all players are saved. * @return: (Int) 1 on success, 0 on failure */ native CSRP_SavePlayerClass(Class:hClass, Player = 0); /* CSRP_ClearPlayerClass(): Clears the content of a player's object in the player class. * @param (Class) hClass: The class handler * @param (Int) Player: index of the player * @return: (Int) 1 on success, 0 on failure */ native CSRP_ClearPlayerClass(Class:hClass, Player); /* CSRP_DestroyPlayerClass(): Completely empties the class and destroys its handle, rendering it unusable until mapchange. * @param-byref (Class) hClass: The class handler. Changed to Invalid_Class after usage. * @return: (Int) 1 on success, 0 on failure */ native CSRP_DestroyPlayerClass(&Class:hClass); /* CSRP_NukePlayerClass(): The same as CSRP_DestroyPlayerClass, except this also removes the table from the database. The plugin needs to be paused/disabled after using this native, or the class will be re-created on next mapchange. * @param-byref (Class) hClass: The class handler. Changed to Invalid_Class after usage. * @return: (Int) 1 on success, 0 on failure */ native CSRP_NukePlayerClass(&Class:hClass); /**************************************** ************ STATIC CLASSES ************ ***************************************/ /* CSRP_CreateStaticClass(): Creates a Static class. * @param (String) ClassName: name of the class (= table name in the database), must be unique * @param (Array) aColumns: must contain the columns of the database. The array cannot ever be deleted! See examples. * @param (Int) UniqueKey: Similar to player classes, except static classes don't support composite keys. Only one column can be unique and it is required. * @param (String) ClassCallback: function to call on class initialization. * @return: (Class) handle of the class. */ native Class:CSRP_CreateStaticClass(const ClassName[], Array:aColumns, UniqueKey, const ClassCallback[]); /* CSRP_GetStaticClassData(): Retrieves all of the data in the class. * @param (Class) hClass: The class handler * @return: (Array) Dynamic array with all of the data ready to be looped. Use ArraySize(Array) for the amount of items. */ native Array:CSRP_GetStaticClassData(Class:hClass); /* CSRP_FindInStaticClassString(): Finds an entry in the class by a string of the unique key. * @param (Class) hClass: The class handler * @param (String) Find: the string to look for * @param-byref (Int) ItemIndex: Optionally returns the index of the element in the array * @return: (Array) item handler if found, Invalid_Array if nothing exists or an error occured */ native Array:CSRP_FindInStaticClassString(Class:hClass, Find[], &ItemIndex=-1); /* CSRP_FindInStaticClassCell(): Finds an entry in the class by a cell of the unique key. * @param (Class) hClass: The class handler * @param (Any) Find: the cell to look for * @param-byref (Int) ItemIndex: Optionally returns the index of the element in the array * @return: (Array) item handler if found, Invalid_Array if nothing exists or an error occured */ native Array:CSRP_FindInStaticClassCell(Class:hClass, any:Find, &ItemIndex=-1); /* CSRP_PushStaticClassItem(): Pushes an item to the class. The array must have the exact same amount of elements as there are columns in the class. * @param (Class) hClass: The class handler * @param (Array) Item: handler of the array with the item. Must not be destroyed! * @return: (Int) index of the item usable within CSRP_{Get/Delete}StaticClassItem */ native CSRP_PushStaticClassItem(Class:hClass, Array:Item); /* CSRP_GetStaticClassItem(): Retrieves an item handle by its index. * @param (Class) hClass: The class handler * @param (Int) ItemIndex: The array index to find. * @return: (Array) item handler if found, Invalid_Array if nothing exists or an error occured */ native Array:CSRP_GetStaticClassItem(Class:hClass, ItemIndex); /* CSRP_DeleteStaticClassItem(): Deletes an item from the class by index. * @param (Class) hClass: The class handler * @param (Int) ItemIndex: the item to delete * @return: (Int) 1 on success, 0 on failure */ native CSRP_DeleteStaticClassItem(Class:hClass, ItemIndex); /* CSRP_SaveStaticClass(): Saves the class. This should be used in the CSRP_OnSave() forward. * @param (Class) hClass: The class handler * @return: (Int) 1 on success, 0 on failure */ native CSRP_SaveStaticClass(Class:hClass); /* CSRP_ClearStaticClass(): Clears the content of the class. * @param (Class) hClass: The class handler * @return: (Int) 1 on success, 0 on failure */ native CSRP_ClearStaticClass(Class:hClass); /* CSRP_DestroyPlayerClass(): Completely empties the class and destroys its handle, rendering it unusable until mapchange. * @param-byref (Class) hClass: The class handler. Changed to Invalid_Class after usage. * @return: (Int) 1 on success, 0 on failure */ native CSRP_DestroyStaticClass(&Class:hClass); /* CSRP_NukeStaticClass(): The same as CSRP_DestroyStaticClass, except this also removes the table from the database. The plugin needs to be paused/disabled after using this native, or the class will be re-created on next mapchange. * @param-byref (Class) hClass: The class handler. Changed to Invalid_Class after usage. * @return: (Int) 1 on success, 0 on failure */ native CSRP_NukeStaticClass(&Class:hClass); /***************************************** ************ MISC, UTILITIES ************ ****************************************/ /* CSRP_ShareSQLHandle(): Retrieves the SQLx tuple handler of the core plugin. This should be used only in extreme situations where you need to use complex queries to achieve your goal. This is identical to SQL_MakeDbTuple(), except ours uses the same handle as the core. * @return: (Handle) SQLx MySQL tuple handler. */ native Handle: CSRP_ShareSQLHandle(); /********************************** ************ FORWARDS ************ *********************************/ /* CSRP_OnSave(): Called whenever a save cycle is started. The period is defined in the orm_config.ini file. Use CSRP_Save{Player/Static}Class() natives here, so everything is saved at once. */ forward CSRP_OnSave(); /* CSRP_OnPlayerLoad(): Called when a player is completely loaded. Make sure the player whenever you use any of the player class natives! @param (Int) Player: index of the loaded player. */ forward CSRP_OnPlayerLoad(Player); /* CSRP_OnServerLoad(): Called when all static and player classes are initialized and they're ready to work with. */ forward CSRP_OnServerLoad();

Credits top

Hawk552 - Original idea, help with a lot of stuff, the comment block layout, encouragement.
Black Rose - Discussion, ideas, overall help when necessary, encouragement.
Kia - for taking the time to actually test this in the real world.