Raised This Month: $ Target: $400
 0% 

MySQL Data Layer


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Backstabnoob
BANNED
Join Date: Feb 2009
Location: Iwotadai Dorm
Old 06-05-2014 , 07:19   MySQL Data Layer
Reply With Quote #1

I'm not really sure if Scripting Help is the best place for this, but here goes.
This thread will be more like a discussion than actual scripting help.

As the mod I'm working on (the one in my sig, CSRP) will have MANY sub-modules and will communicate with the database A LOT, I decided it's stupid to let each subplugin/submodule handle the database in their own way. I'm trying to create a complex class system to handle the data of the database easily, while the core plugin takes care of all the hard work. An example of some natives it should have:

PHP Code:

enum _
:ClassColumnStruct
{
    
__szColumnName16 ],
    
ClassColumnType__iColumn,
    
__iMaxLen
}

enum Class
{
    
Class_Invalid = -1
}

enum ClassColumnType
{
    
Column_Varchar,
    
Column_Int
}

enum ClassReturn
{
    
Class_InvalidHandle = -1,
    
Class_DatabaseFailure,
    
Class_Success
}

// these are for player data
Class: CSRP_CreatePlayerClass( const szTable[ ], Array: aColumns, const szCallback[ ] )
ClassReturnCSRP_GetPlayerClassDataid, Class: hClass, &Array: aResults )
ClassReturnCSRP_PushPlayerClassDataid, Class: hClassTrietData )

// these are for other data by key instead of player index
Class: CSRP_CreateKeyClass( const szTable[ ], Array: aColumns, const szCallback[ ] )
ClassReturnCSRP_GetKeyClassData( const szKey[ ], Class: hClass, &Array: aResults )
ClassReturnCSRP_PushKeyClassData( const szKey[ ], Class: hClassTrietData )

// general stuff
ClassReturnCSRP_DestroyClassHandle( &Class: hClass )  // destroys the class handle and releases everything from the memory
ClassReturnCSRP_NukeClass( &Class: hClass // completely removes the class from the database and destroys the class handle
ClassReturnCSRP_ClassForceSave( Class: hClass // forces the class to save itself 
Example of usage:
PHP Code:
enum _TestColumns
{
    
Column_PlayerName,
    
Column_PlayerAuthId
}

new Class: 
g_hClassHandle

new Array: aColumns ArrayCreateClassColumnStruct )
    
new 
aColumnValuesTestColumns ][ ClassColumnStruct ] = 
{
    { 
"jail_lifttime"Column_Int11 },
    { 
"jail_name"Column_Varchar32 }
}
    
for( new 
iTestColumns++ )
{
    
ArrayPushArrayaColumnsaColumnValues] )
}

g_hClassHandle CSRP_CreatePlayerClass"csrp_jailtimer"aColumns"_OnClassLoad" 
What should happen in the above: CSRP_CreatePlayerClass should create a table in the database called csrp_jailtimer with these columns:
Code:
key SMALLINT(5) UNSIGNED - since it's a player class, this will hold the unique player index
jail_lifttime INT(11)
jail_name VARCHAR(32)
The core should call _OnClassLoad once the class is properly initialized.

So far I know how to do that, no worries. However the problem happens when we talk about loading the actual data.
How would I want to do that technically? First, I thought about loading the entire table into memory. Trie for each row. Then I realized that's a terribad idea.

I'm now deciding on this: If the class is a player class (i.e. it holds data for each player), the player's data should be loaded on connect and saved and destroyed on disconnect. That seems reasonable and I think I'll stick with that, but what about non-player classes? For example a class that holds the list of all the items available for players to buy. These data need to be accessible all the time; should I just load them all on the class initialization and save/destroy them on mapchange, like I first wanted to? Or do you have a better idea?

Also, HOW would you store data from a table? The player index doesn't necessarily have to be unique. For example, if the class would be a player inventory class with the following columns:
Code:
key SMALLINT(5) UNSIGNED
item VARCHAR(32)
amount INT(11)
The data in the table can be as follows:
Code:
+-----+------------------+--------+
| key | item             | amount |
+-----+------------------+--------+
| 1   | item_m4a1        | 3      |
| 1   | item_hamburger   | 1      |
| 1   | item_flyingwings | 8      |
| 1   | item_flashlight  | 7      |
| 15  | item_flashlight  | 3      |
| 16  | item_awp         | 16     |
| 4   | item_primaryammo | 8      |
+-----+------------------+--------+
Which means that a CSRP_GetPlayerClassData( 1, Some_Class_Handle, Array: aOutput ) native call will have to save something like this into the aOutput array in the parameters:
Code:
Array
{
     Trie: { "item" : "item_m4a1", "amount" : 3 }
     Trie: { "item" : "item_hamburger", "amount" : 1 }
     Trie: { "item" : "item_flyingwings", "amount" : 8 }
     Trie: { "item" : "item_flashlight", "amount" : 7 }
}
Which kinda blows. This wouldn't be a problem with PHP's array design, but I have ABSOLUTELY NO IDEA how to do this reasonably well in Pawn.

Let me know what you think about my whole design, I need to get this done ASAP.

Last edited by Backstabnoob; 06-05-2014 at 08:15.
Backstabnoob is offline
aron9forever
Veteran Member
Join Date: Feb 2013
Location: Rromania
Old 06-05-2014 , 10:19   Re: MySQL Data Layer
Reply With Quote #2

well here's the deal, I have far from enough knowledge to actually talk about subjects like these, but you could "inspire" yourself from sa-mp rpg gamemodes. They are also written in PAWN and there's basically no difference except event handling and functions, and those scripts are designed to handle big amounts of player info, most of the newer ones using sql.
aron9forever is offline
Backstabnoob
BANNED
Join Date: Feb 2009
Location: Iwotadai Dorm
Old 06-05-2014 , 14:00   Re: MySQL Data Layer
Reply With Quote #3

Eh. The ones I looked at had around 50 thousand lines of terrible code. I don't think I can find anything useful there anyway.


An example using my method:
Players - 30
Opened classes - for example 10
Each class has 4 columns
Each column has a name consisting of 16 characters
Each player has let's say 30 rows in each of the classes (probably won't ever happen, but still)
Maxlen of each string from the trie hashtable would be 128 characters

All in all, that's 30 * 10 * 4 * 16 * 30 * 128 = 73,728,000 cells

A single cell is 4 or 8 bytes, let's go with 4, that's 294,912,000 bytes = 295MB of memory. There must be a better way

Last edited by Backstabnoob; 06-05-2014 at 14:14.
Backstabnoob is offline
aron9forever
Veteran Member
Join Date: Feb 2013
Location: Rromania
Old 06-05-2014 , 14:12   Re: MySQL Data Layer
Reply With Quote #4

Quote:
Originally Posted by Backstabnoob View Post
Eh. The ones I looked at had around 50 thousand lines of terrible code. I don't think I can find anything useful there anyway.
that's not hard to explain, since the open source ones are pretty terrible and deprecated
but you can't call it terrible if it can handle 1000 players at once
aron9forever is offline
Backstabnoob
BANNED
Join Date: Feb 2009
Location: Iwotadai Dorm
Old 06-06-2014 , 06:29   Re: MySQL Data Layer
Reply With Quote #5

I'm so stupid. Why do I event want to use Tries? I have direct access to the column index, so I can simply use a sub-array.
Code:
Array
{
     Array: { 0: "item_m4a1", 1: 3 }
     Array: { 0: "item_hamburger", 1: 1 }
}
etc. This seems like a much better option.

Any better ideas? Does anyone know if it matters that I'm going to create up to a thousand or more dynamic array handles?
Backstabnoob is offline
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

Forum Jump


All times are GMT -4. The time now is 09:48.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode