Storing Data: Text Files? SQL?

I’m working on a gamemode that requires lots of data and a lot of this data are Lua tables.

How should I store this?

I’m leaning towards just keeping each player’s data in a text file since I think I read that it’s not a good idea to store serialized tables in SQL.

Is there any downfalls or benefits to these?

Keeping each players data in a text file would be worse than storign it in a SQL database.

Well, I’m not sure how exactly to store Lua tables in an SQL database or if it is even efficient.

I find that SQL is only better if your “gamemode” is spread across multiple servers.

If not, just use text files.

Alright. Right now I think it will probably be easier for me to start off with text files. Then later implement SQL storing.

SQL is a more elegant solution. In either case the easy way is JSON encoding.

[editline]28th August 2013[/editline]

util.TableToJSON util.JSONToTable

json is a string representing a table

it isn’t friendly with hugeee numbers in the source table, so store those as tostring(huge_number_variable) within the table

Do not store encoded information in an SQL database.

Well, at least explain why, otherwise the OP will never learn:

Storing a bunch of data, like what you’re storing in text files, in an SQL table is generally bad practice. Moving from text files and using the exact same approach to store data but in an SQL table (I.E storing a player ID and have another column full of encoded data) will not gain you much. You still have the relationship of “player”->“lots of player data”, but that is all - you can only access your data by grabbing ALL of it, then decoding it, then finally reading what you want. It is inefficient, and very limiting.

What you are trying to do is called “document-based storage”, where you store entire documents. SQL provides a relational database model, so by using the document-based model inside SQL you are throwing away the advantages of using SQL and you’re almost just as well off storing the data in a text file. I can almost guarantee users of your gamemode would love the option to have MySQL support, so I would recommend looking into it. There is a MySQL module available for GMod: http://forum.facepunch.com/showthread.php?t=1220537

The problem with mySQL is that GMod by default doesn’t allow to connect to external databases and server owners will have to install custom modules for that. For sake of simplier server setup I suggest saving data to text files unless there is absolute need for functions of mySQL.

If you’re only doing it on one server you can just use the inbuilt SQL library.

Interesting, thanks everyone.

So if I really wanted to use SQL i’d have to split data into separate parts and never store it encoded.

Currently I’m storing all the items a player owns in a table which is then JSON encoded and put into a text file, along with wins, losses, etc but is there a way to store something like that in SQL that is changed a lot and has no real limit to how many items the player can own?

So you’d have a structure like this:

players:
steamID|wins|losses|name|

playeritems:
itemID|playerSteamID|amount|

items:
id|name|blahblah|

So you’d have a structure that allows for any number of items per player

I see. So it’ll be it’s own table and all I specify is the id, player that owns it, and the amount of that item they have.

So simple yet I struggled trying tofigure out how I would do it yesterday, haha.

It’s easy once someone tells you, but coming up with the structure can be hard at first. Just practice a little and you’ll get the hang of it

Here’s a tip: File IO is one of the most expensive things in computing. I would recommend highly against it!

SQL, even just starting out using GMOD SQL is much better ( even though that’s flat file ) but you can export it and import it into a MYSQL database later on…

Learn SQL, learn how to link tables ( map ) together so you’re not storing the same data over and over.

So you can have a table for Players, items, etc as said above.

An account entry should have a UNIQUE PRIMARY AUTO_INCREMENT BIGINT 20 id starting at 1, SteamID, UniqueID, Account Name, other info.

That first auto_increment id should be used every where else when referencing that player. That way you’re not using steamid for every row for items ( Imagine the user has 1000 items, each 1 row, or however you choose to store it, and the steam id is 15 chars long which is 14.65KB of data, for JUST the steam id, where it can be reduced to the 1 to many numbers long for the account id, if it’s 1 long, it’s not even 1kb, if it’s 2 long first 100 people it’s 1.95KB, if it’s 3 long first 1000 people 2.92KB ) etc…

So, always look to optimize your tables where ever possible. You can save a TON of data just by mapping large date to smaller data.

Use ENUMs for user level, or other information that you store, which is hard coded. Gender, instead of MALE/FEMALE being stored, or M/F, store a tinyint, 0-1 using ENUMs. goes from 6 chars 48 bits, down to 1 bit.

SQL is definitely the way to go, but make sure you design your entire database on paper before you start implementing it, make sure you learn how to link tables together, etc.

And use foreign and composite primary keys.

So if I wanted to use MySQL, what module is best or preferred?

MySQLOO

This is a someone lengthy, detailed post. Read it all, I post some SQL examples, and how I do a few things such as a helper function to ensure that if ONE MODULE becomes outdated, and another works, I can return ANY type of data ( as the 2 recommended return different data by default; helper functions are nice to ensure backwards compatibility regardless of which DLL you put in place. ) Excuse the messy post, but it’s got a lot of info, and proprietary info to some of my SQL tests. All of it works.

I use TMySQL 4 BOOST.

The difference between TMySQL and MySQL OO is that by default, T returns as numbered indexes whereas OO returns as STRING indexes.

You can return STRING KEYS on T by adding this flag: QUERY_FLAG_ASSOC

But, what I do to make my entire system work with BOTH, TMySQL or MySQLOO just in case one breaks, and the other is up sooner:

Sorry, just cut and paste a little as an example… MY wrapper supports both TMySQL and MySQLOO without even doing anything. I do set the value to ROWTYPE ON CONNECT ( My connection sequence looks like TRY TO INCLUDE TMYSQL 4 BOOST AND CONNECT, ON FAIL TRY TO INCLUDE AND CONNECT MYSQL OO, ON FAIL, DISCONNECT SERVER, ON SUCCESS OF EITHER THROW OUT A DATABASECONNECTION SUCCEEDED HOOK ), but it’s not really necessary because you check if one or the other exists, and return the one that exists. It’s easy to use:

// This was used in a query where we limit the row to 1, basically you input what the STRING KEY value would be, and the NUMBER INDEX of where it would be. This ROW 1, it’s the ONLY variable so it’s NUMBER INDEX is 1, and the STRING KEY is user_connects. I return using the ASSOC, so it will actually return with user_connects, if I remove that flag it would return via number INDEX
[lua]local _cons = DB.GetValue( row, 1, 1, “user_connects” );[/lua]

Another example:
row is the returned var which stores it all, 1 because there’s a LIMIT 1 on the query, 1, 2, 3 because that’s the order of how they were selected in the query, and the names.

Here’s the query on this one ( Generate String processes data, and ESCAPES the value ):

// This is another HELPER FUNCTION, here’s the HEADER to get an idea of what it does: function DB.SelectQuery( _table, _fields, _where, _limit )
[lua]local _query = DB.SelectQuery( DATABASE_USER_ACCOUNTS_TABLE, { “character_firstname”, “character_lastname”, “userid” }, DB.GenerateString( true, “steamid”, Player:SteamID( ) ), 1 )

// That wrapper is a nice way to say:
local _query = “SELECT character_firstname, character_lastname, userid FROM gamemode_prefix_user_accounts WHERE steamid = ‘STEAM_0:1:4173055’ LIMIT 1”;[/lua]

[lua]local _fn = DB.GetValue( row, 1, 1, “character_firstname” ); // Notice I choose this first
local _ln = DB.GetValue( row, 1, 2, “character_lastname” ); // this second
local _id = DB.GetValue( row, 1, 3, “userid” );// this 3rd in the query? [/lua]

And one final one with multi rows, notice k replaces the 1, the numbers for the rest remains the same

I’ll show the query on this one: This is more advanced, but in reality it just links 2 tables together based on the steamid on 1 table to get, and character/user id
[lua]DATABASE:Query("SELECT " … DATABASE_USER_STORAGE_TABLE … ".itemid, " … DATABASE_USER_STORAGE_TABLE … ".slot, " … DATABASE_USER_STORAGE_TABLE … ".quantity, " … DATABASE_USER_STORAGE_TABLE … ".item_info, " … DATABASE_USER_STORAGE_TABLE … ".storage FROM " … DATABASE_USER_STORAGE_TABLE … " INNER JOIN " … DATABASE_USER_ACCOUNTS_TABLE … " ON " … DATABASE_USER_ACCOUNTS_TABLE … ".userid = " … DATABASE_USER_STORAGE_TABLE … ".userid WHERE " … DATABASE_USER_ACCOUNTS_TABLE … “.steamid=’” … DB.Escape( Player:SteamID( ), true ) … “’”, function(row)

// Which is a better way of saying this:
SELECT gamemode_prefix_user_storage.itemid, gamemode_prefix_user_storage.slot, gamemode_prefix_user_storage.quantity, gamemode_prefix_user_storage.item_info, gamemode_prefix_user_storage.storage FROM gamemode_prefix_user_storage INNER JOIN gamemode_prefix_user_acounts ON gamemode_prefix_user_acounts.userid = gamemode_prefix_user_storage.userid WHERE gamemode_prefix_user_acounts.steamid=‘STEAM_0:1:4173055’

// Notice how I link 2 tables together based on the account ID, but I use the STEAM ID to get the accounts table, which gives me the account id ( userid ) which then in term lets me grab storage information for that account ( this is my old SQL before I did the character system )[/lua]

[lua] local _inventory = { }
local _bank = { }
local _table = nil;
for k, v in pairs( row ) do
local _id = DB.GetValue( row, k, 1, “itemid” );
local _slot = DB.GetValue( row, k, 2, “slot” );
local _qty = DB.GetValue( row, k, 3, “quantity” );
local _info = DB.GetValue( row, k, 4, “item_info” );
local _stored = DB.GetValue( row, k, 5, “storage” );
end
[/lua]

[lua]//
// Simple DB Value TOOL ( All this does it go into the table and pull out the desired data, simple HELPER function which ensures backwards compatibility regardless of TMySQL or MySQL OO using numbered index return OR string index ) - Josh ‘Acecool’ Moser
//
if (!tmysql) then tmysql = {}; end
if (!DATABASE) then DATABASE = {}; end
if (!ERR) then ERR = {}; end
if (!DATABASE_ROWTYPE) then DATABASE_ROWTYPE = 1; end

DATABASE_ROWTYPE_NUMBER = 1;
DATABASE_ROWTYPE_NAME = 2;

function DB.GetValue(row, i, int, key)
if ( DATABASE_ROWTYPE == DATABASE_ROWTYPE_NUMBER && row[ i ][ int ] ) then
return row[ i ][ int ];
else
return row[ i ][ key ];
end
end[/lua]

A lot to take in, wow.