Storing Data: Text Files? SQL?

SQL is fun, it’s FAST ( Google uses a modified version of MySQL with the SQL language to power it’s search engine ), it’s customizable, you can link tables, etc. It’s an incredibly efficient way to store data!

Remember, FILE IO ( Input/Output) is one of the most expensive things you can do, so I’d really recommend learning SQL before setting up a flatfile system and having to redo it down the road. We’ll be more than happy to assist you as long as you’re willing to learn.

The examples I gave were simple selects, the last one linked 2 tables together to avoid doing a query to get data, and then another query to finish getting data from another table. You can chain as many as you want together essentially. That’s why I said you want to get away from STEAMID, has a primary account table hold the steamid and unique id, and other information which is then bound to a unique, primary, auto_increment id which can then be mapped across ALL other tables. You join on other tables from the account_table. userid ON other_table.userid, etc etc, and you querty the WHERE account_table.steamid = “blah” that way you have 1 way in to the entire structure, and a nice way to link across. You could use STEAMID for everything, but the issue as I said just 1 user with 1000 entries is 15kb. You have thousands of users and just steamid for EVERYTHING really adds up.

Design everything on paper first, and design a database wrapper / helper functions to assist with backwards compatibility as said, because it is a 3rd party module. If Garry updates the game, they may go down, if one is up before the other you should have some solution created that you just drop the DLLs in, and it works. If you follow that logic I told you for on connect, if fail include and try connecting using other module. I think there is more than 2 out there, chain even more together…

It’s just so robust, if you’re willing to learn… I provided you with 1 simple helper function which provides the cross-compatibility ( SUPER HELPFUL / USEFUL ), so when you design your queries, use that so that if one goes down, that part is set, then you just have to handle connecting.

BASIC FORMAT IS:

Option A) SELECT column1, column2 FROM table
Option B) SELECT * FROM table // Selects ALL, so when you output remember it will be in order of how the structure is set up, if you alter that then you have to change your code; which is why I recommend always using option A instead of *

Using Option A with conditions: SELECT column1, column2 FROM table WHERE steamid = ‘BLAH’ LIMIT 1 // limits to 1 result, on that steam id.

Now, when you INSERT INTO, or UPDATE your tables, make sure you ESCAPE your strings. use http://wiki.garrysmod.com/page/Global/SQLStr to escape them, otherwise people can INJECT data by changing their name, spoofing steam, or doing other things which can WIPE your entire database.

I just found this which you can learn with, it’s interactive: http://www.sqlcourse.com/index.html

You shouldn’t have to do this, because the column ‘steamid’ should be a primary key in a case like this.

Yeah SQL is a bit more efficient noticeably on performance. Due to some to personal experience of using flatfile storage for a large number of players for a tiny bit of data, still had huge repercussions for me. You can experience net and fps drops from it(or at least the way I was doing it :P). Anywho you can easily allow the server to network the data quickly with PDATA and just use the net library to send it to the client for viewing purposes.

ADDED INFO: In a sense, yes, but no. I don’t use STEAMID as PRIMARY KEY, I use the account ID, or userid, as the PRIMARY KEY, as that is the auto_increment, internal method of referencing that user. It’s shorter, less data across tables to store, etc. While the STEAMID is in a sense a primary, it’s not listed as such in the database because two primaries doesn’t work. I’m experienced enough in database design and management that I can get away with not setting such keys as primary because I know when and how to manage it so that duplicates never arise, I still use LIMIT 1 out of habit as just so never more than 1 will return in case someone inserts one manually which should never happen as I’m the only one with db access and I don’t modify the db outside of redesign, etc…

As long as you properly manage your data, everything is golden. Below is a surefire way to ensure that your data is properly managed, by inserting / creating an account at the first available instant ( MUCH sooner than the player would be able to fully connect to do anything else with the db / account, the account will be waiting for them )

ORIGINAL POST:
True, but I LIMIT 1 on all where it should be limited. I do a check before I insert based on STEAMID, but I use a different number as my primary key. While true, I do manage my database differently so that a STEAMID will NEVER be inserted twice, I still LIMIT 1 for good practice just in case someone enters a second one in the DB manually, then you have to iterate through and update both, I’d rather limit 1 and be done…

Here’s an example of what I do. AS soon as the server sees the player / steam id / name of the client ( On game event listener, on connect which is as SOON as the player clicks connect on a server before anything starts downloading or whatever ) I insert that user into the DB if the steam id doesn’t exist, or I update first login if it hasn’t been set yet ( for prior accounts that joined when it was just a dev server; using same db so I need to update that info ). This way the account is created instantly, then when they join it’s just a matter of changing a few other things… I don’t do anything with user_connects, so I could technically remove it, but I haven’t going through all the SQL to finalize it all, so I’m not terribly worried…

ESCAPING data is important!!! So you’ll see a lot of my custom escape which references the escape above, anything where it says GenerateString builds those key=’" … value … "’ automatically, and escapes within that function… But this gives you another piece of the puzzle…

Notice the default data: Username is the name they join with, levelid I set to 1, which is MEMBER/GUEST ( I should use the ENUM for guest here actually and set it to MEMBER on first full join to simplify things ), I set the first login time to that moment when they first connected, and their connects to 0 as they haven’t fully connected ( a different function, once they fully connect, will update their connects and fill in more info ), I set the steam id which is important. In another later function I submit their uniqueid as a secondary form of authentication when they fully connect ( as it’s only available on full connect )
[lua]function DB.InitialAccountCheckOrCreate( steamid, name )
– To see the difference This query is what the SelectQuery generates exactly…
– local _query = “SELECT user_connects, firstlogin FROM " … DATABASE_USER_ACCOUNTS_TABLE … " WHERE steamid=’” … DB.Escape( steamid, true ) … “’ LIMIT 1”;
local _query = DB.SelectQuery( DATABASE_USER_ACCOUNTS_TABLE, { “user_connects”, “firstlogin” }, DB.GenerateString( true, “steamid”, steamid ), 1 )
DATABASE:Query( _query, function( row )
if ( #row > 0 ) then
local _first = DB.GetValue( row, 1, 2, “firstlogin” );
if ( tonumber( _first ) == 0 ) then
DATABASE:Query( “UPDATE " … DATABASE_USER_ACCOUNTS_TABLE … " SET firstlogin=’” … DB.Escape( os.time( ), true ) … “’ WHERE steamid=’” … DB.Escape( steamid, true ) … “’ LIMIT 1” )
end
else
MsgC(COLOR_ORANGE, “[” … GAMEMODE.Name … "] User “” … DB.Escape( name, true ) … “” [ " … DB.Escape( steamid, true ) … " ] doesn’t exist - INSERTING.
")
DATABASE:Query( “INSERT INTO " … DATABASE_USER_ACCOUNTS_TABLE … " SET steamid=’” … DB.Escape( steamid, true ) … “’, username=’” … DB.Escape( name, true ) … “’, levelid=‘1’, firstlogin=’” … DB.Escape( os.time( ), true ) … “’, user_connects=‘0’”)
end
end, QUERY_FLAG_ASSOC )
end[/lua]

So… use a unique constraint? My point still stands, if you do it correctly (steamid as primary key if you use that for identification, or steamid as a unique constraint if you have another arbitrary integer value to identify users) then you won’t need to use “LIMIT 1” in a case like this. The database is smart enough to not search for more occurences than the one found when it’s specified as being unique in one way or the other.

You are right, I did set SteamID as a unique as well… so yes, LIMIT isn’t needed, But I still do it :slight_smile:

This isnt’ the entire table, a lot of columns have been omitted.
CREATE TABLE IF NOT EXISTS user_accounts (
userid bigint(20) NOT NULL AUTO_INCREMENT,
steamid varchar(50) NOT NULL,
uniqueid bigint(20) NOT NULL,
username varchar(50) NOT NULL,
levelid int(4) NOT NULL,
PRIMARY KEY (userid),
UNIQUE KEY steamid (steamid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=116 ;

Can you give link to TMySQL 4 BOOST?

http://blackawps-glua-modules.googlecode.com/svn/trunk/gm_tmysql4_boost/Release/

gm_tmysql4_win32.dll needs to be renamed gmsv_tmysql4_win32.dll and placed in your SERVER garry’s mod / lua / bin directory, for me that’s:
F:\dedicated_servers\server_1_acecooldev\garrysmod\lua\bin\gmsv_tmysql4_win32.dll
F:\dedicated_servers\server_1_acecooldev\garrysmod\lua\bin\gmsv_mysqloo_win32.dll – Told you I had both as backup plan

lua isn’t needed but gives you an idea of what it does, and may help you get started.

http://blackawps-glua-modules.googlecode.com/svn/trunk/gm_tmysql4_boost/gm_tmysql4/lib/

libmysql.dll needs to be placed in your SERVER directory, where SRCDS.EXE and HL2.EXE are located, for me, that’s:
F:\dedicated_servers\server_1_acecooldev\hl2.exe
F:\dedicated_servers\server_1_acecooldev\srcds.exe
F:\dedicated_servers\server_1_acecooldev\libmysql.dll – here

EDIT: Link for OO http://forum.facepunch.com/showthread.php?t=1220537

You should use MySQLOO in my opinion.