Databases: File or SQL?

[MediCat]

Hi everyone. I have some sort of loadout system, where player decides himself what weapons should be given him on spawn. I have a weapon table like

soc.weps = {
	[101] = {
		class = "weapon_fists",
		name = "Hands",
		price = 0,
		model = "models/weapons/w_hands.mdl",
	},
	[102] = {
		class = "weapon_physgun",
		name = "Physgun",
		price = 0,
		model = "models/weapons/w_superphyscannon.mdl",
	},
	[103] = {
		class = "gmod_camera",
		name = "Camera",
		price = 0,
		model = "models/maxofs2d/camera.mdl",
...

so each player has a table representing his loadout

ply.soc_loadout = {101, 102, 103}

So here is the question - is it okay to use file based database in that case? Is it good to system to store about 8,000 (approximated amount of unique players joined to server over last 6 months) files 1Kb each? Or probably I should create a SQL table with 2 columns (SteamID, JSON with loadout)?

[MysteryPancake]

What seems simpler to you? 8,000 text files or 2 columns?

[URL="https://wiki.garrysmod.com/page/Category:sql"]Also, from the wiki page[/URL]:
[QUOTE]SQL is the preferred and fastest method of storing large amounts of data.[/QUOTE]

[editline]13th August 2017[/editline]

I voted 'files are best for that purpose' by accident sorry

[PerkyMcRibs]

[QUOTE=MPan1;52568051]What seems simpler to you? 8,000 text files or 2 columns?

[URL="https://wiki.garrysmod.com/page/Category:sql"]Also, from the wiki page[/URL]:

[editline]13th August 2017[/editline]

I voted 'files are best for that purpose' by accident sorry[/QUOTE]

Or you could...you know... make it under one table in one text file if you don't know SQL...

Wouldn't even be in the data files so you wouldn't even have to network it from there. From what I see, it would just be a static table. A configuration file of sorts.

Wouldn't have to rely on SQL.

[MediCat]

[QUOTE=PerkyMcRibs;52568097]Or you could...you know... make it under one table in one text file if you don't know SQL...

Wouldn't even be in the data files so you wouldn't even have to network it from there. From what I see, it would just be a static table. A configuration file of sorts.

Wouldn't have to rely on SQL.[/QUOTE]

I actually already use SQL in other cases so it's not a problem at all. But I dislike the idea to load 8k entries at once, especially when only 10-20 will be used on runtime. Waste of RAM, even if there will be no impact on performance.

[god o warzen]

me personally I know only a little on SQL but I do prefer to use it before anything else. any addon I have for my server I always make sure to use the SQL option if available.

[PerkyMcRibs]

[QUOTE=MediCat;52568138]I actually already use SQL in other cases so it's not a problem at all. But I dislike the idea to load 8k entries at once, especially when only 10-20 will be used on runtime. Waste of RAM, even if there will be no impact on performance.[/QUOTE]

The only reason I would is so you don't have to rely on the SQL server. Less factors.

[MediCat]

[QUOTE=PerkyMcRibs;52568296]The only reason I would is so you don't have to rely on the SQL server. Less factors.[/QUOTE]

What do you mean by SQL server? All the data is stored in local sv.db

[Sugar Tits]

It's up to you. SQL will be better in most (if not all) cases. Saving data in text files should only really be used if the data inside of the file isn't too large and the process relating to the text files is simple and doesn't happen too often (although an actual DB will prove to be better in every aspect basically).

Text files are good if you just want to make something fast so you don't have to setup an actual db + write the queries but just make sure you're not trying to read a crazy amount of data often.

If you have ever touched SQL I would definitely check that out, it's a very useful when it comes to storing and retrieving data. Gmod uses SQLite to store data locally in sv.db but you can use modules that utilize SQL such as mysqloo and tmysql

[PerkyMcRibs]

[QUOTE=MediCat;52568317]What do you mean by SQL server? All the data is stored in local sv.db[/QUOTE]

:/

I wouldn't know. I never bothered learning SQL. But aren't SQL databases stored in MySql databases online? Hence like the donation systems and such?

[MrRalgoman]

sv.db and cl.db are gmods SQLite built in functionality, you can have you server connect to a sql server online if you choose but by default they use the local SQLite dbs.. also, someone just told me a bit ago the sql data tends to corrupt a lot, is that not the case?

[MediCat]

[QUOTE=PerkyMcRibs;52568405]:/

I wouldn't know. I never bothered learning SQL. But aren't SQL databases stored in MySql databases online? Hence like the donation systems and such?[/QUOTE]

SQL database is just a file type, like "example.db", you can access it anytime, it is located in garrysmod folder and named cl.db or sv.db. I'm sure there is a way to connect it with other databases, but I have no idea how :D

And I thought that all the donation info synchs between gmod server and website host using http protocols, and both sides do have SQL databases (files). At least that's how I see it :p

[Kevlon]

Using files for anything except loading them one time or something is bad due to gmod stopping everything while reading or writing files. If you are making a file for every player then you are murdering the server.

[PerkyMcRibs]

[QUOTE=Kevlon;52569133]Using files for anything except loading them one time or something is bad due to gmod stopping everything while reading or writing files. If you are making a file for every player then you are murdering the server.[/QUOTE]

Can anyone confirm gmod actually stops everything while reading and writing files?

[Revenge282]

[QUOTE=PerkyMcRibs;52569209]Can anyone confirm gmod actually stops everything while reading and writing files?[/QUOTE]

Why are we even debating this in the first place? Databases trump files for this kind of thing in any way you try to spin it. Databases were specifically designed to prevent this from happening, and to enable people to do more with the data than a file could.

You coming up with reasons why using a file I/O system is better than a database is just plain uninformed and silly.

If you want a solid answer, here it is:

If you have just one server, and you have no intentions on adding another, or having a website interact with anything on your game server, then you can use the SQLite implementation in GMod.
[img]http://wiki.garrysmod.com/favicon.ico[/img] [url=http://wiki.garrysmod.com/page/Player/SetPData]Player:SetPData[/url]
[img]http://wiki.garrysmod.com/favicon.ico[/img] [url=http://wiki.garrysmod.com/page/Player/GetPData]Player:GetPData[/url]
etc.

If you want to integrate another server in the future, or a website, then you will need to use MySQL. To do that, you will need a MySQL module, and for that, I recommend MySQLOO: [url]https://facepunch.com/showthread.php?t=1515853[/url]

Files are not an option for 99.99% of anything you would want to do in this scenario.

[PerkyMcRibs]

[QUOTE=Revenge282;52569216]Why are we even debating this in the first place? Databases trump files for this kind of thing in any way you try to spin it. Databases were specifically designed to prevent this from happening, and to enable people to do more with the data than a file could.

You coming up with reasons why using a file I/O system is better than a database is just plain uninformed and silly.[/QUOTE]

I'm not debating, I'm asking this because I need to know. Stop mumbling and jumbling my words. If this is actually true, then I have a whole lot of work to do.

[editline]13th August 2017[/editline]

[QUOTE=Revenge282;52569216]Why are we even debating this in the first place? Databases trump files for this kind of thing in any way you try to spin it. Databases were specifically designed to prevent this from happening, and to enable people to do more with the data than a file could.

You coming up with reasons why using a file I/O system is better than a database is just plain uninformed and silly.

If you want a solid answer, here it is:

If you have just one server, and you have no intentions on adding another, or having a website interact with anything on your game server, then you can use the SQLite implementation in GMod.
[img]http://wiki.garrysmod.com/favicon.ico[/img] [url=http://wiki.garrysmod.com/page/Player/SetPData]Player:SetPData[/url]
[img]http://wiki.garrysmod.com/favicon.ico[/img] [url=http://wiki.garrysmod.com/page/Player/GetPData]Player:GetPData[/url]
etc.

If you want to integrate another server in the future, or a website, then you will need to use MySQL. To do that, you will need a MySQL module, and for that, I recommend MySQLOO: [url]https://facepunch.com/showthread.php?t=1515853[/url]

Files are not a good option for 99.99% of anything you would want to do in this scenario.[/QUOTE]

What about in the scenario of me writing and getting data files based on the Steam64ID of a player for my own custom addon? As in, I would be writing and reading the files about 10-20 times per minute (maybe that's a low, if it's a 100 player server, then a lot more.)

[Revenge282]

[QUOTE=PerkyMcRibs;52569219]I'm not debating, I'm asking this because I need to know. Stop mumbling and jumbling my words. If this is actually true, then I have a whole lot of work to do.

[editline]13th August 2017[/editline]

What about in the scenario of me writing and getting data files based on the Steam64ID of a player for my own custom addon? As in, I would be writing and reading the files about 10-20 times per minute (maybe that's a low, if it's a 100 player server, then a lot more.)[/QUOTE]

Dude. I literally do not know how else to explain this to you. There is no situation that you could imagine that files would outperform a database. At all, ever, period.

No one sat down at the MySQL conference table and said, "Hey, let's design a system worse than storing data in a file!".

[PerkyMcRibs]

[QUOTE=Revenge282;52569238]Dude. I literally do not know how else to explain this to you. There is no situation that you could imagine that files would outperform a database. At all, ever, period.

No one sat down at the MySQL conference table and said, "Hey, let's design a system worse than storing data in a file!".[/QUOTE]

I'm not asking you what "outpreforms". I'm asking if the server actually stops while doing data gets and writes.

Also, what is it with you and MySql, are you two like dating?

[god o warzen]

to put it simple, think of it as 2 separate computers. 1 runs your server, the other one runs database related things. would you rather have 1 CPU for databases+server or 2 CPUs split to where DATABASE and SERVER have their own individual CPUs? there is also to mention that sql looks a lot nicer and is much easier to manage since you kinda have a spreadsheet look to it. faster, cleaner, better for the players

[PerkyMcRibs]

*Snip, not even worth the comment.

[god o warzen]

side note, other then SQL or File is there even a third option that exists? kinda curious at this point if there is

[PerkyMcRibs]

[QUOTE=god o warzen;52569321]side note, other then SQL or File is there even a third option that exists? kinda curious at this point if there is[/QUOTE]

[url]http://gmod.org/wiki/Databases_and_GMOD[/url]

[MediCat]

Wow, what a discussion. Revenge282 - thank you for info. Also, is it possible to create 2 different .bat files to start one srcds.exe? Then all files and database will be shared for them and I don't need to bother with SQL synching. Please correct me of I'm wrong.

[Tyler B.]

[QUOTE=PerkyMcRibs;52569336][url]http://gmod.org/wiki/Databases_and_GMOD[/url][/QUOTE]

holy shit that's not even a garry's mod wiki did you even read it??

[URL="http://gmod.org/wiki/Main_Page"]http://gmod.org/wiki/Main_Page[/URL]

[Revenge282]

[QUOTE=MediCat;52572524]Wow, what a discussion. Revenge282 - thank you for info. Also, is it possible to create 2 different .bat files to start one srcds.exe? Then all files and database will be shared for them and I don't need to bother with SQL synching. Please correct me of I'm wrong.[/QUOTE]

If, when you say "SQL synching", you mean so they can use the same SQLite database with the function I linked above, then this is a very bad idea. This is where MySQL shines, and is intended for use. Luckily it's not much more complicated than SQLite, and their syntax is quite similar.

[editline]14th August 2017[/editline]

[QUOTE=TylerB;52572646]holy shit that's not even a garry's mod wiki did you even read it??

[URL="http://gmod.org/wiki/Main_Page"]http://gmod.org/wiki/Main_Page[/URL][/QUOTE]

He is gone. Looooong gone.

[Galaxy]

Pretty much anything that involves tables or player data (like pdata does) or anything similar you will want to use SQL for. In case you have to store like a single entity location then text file could be a better solution. Generally SQL is better though.

[MediCat]

[QUOTE=Revenge282;52572689]If, when you say "SQL synching", you mean so they can use the same SQLite database with the function I linked above, then this is a very bad idea. This is where MySQL shines, and is intended for use. Luckily it's not much more complicated than SQLite, and their syntax is quite similar.[/QUOTE]
I just have no idea how does that SQL system work. I know that server have its own database and that website hosting offer remote databases, so I thought that server and website databases are some sort of synching using HTTP requests. Can you provide me a link to any kind of tutorial for this thing? Or, if you have spare time, you can explain it by yourself :p

[Revenge282]

1. Get a MySQL server from a host, there are plenty of free ones, and like you said, web hosts often give you some as well.

2. Download MySQLOO v9 from the link I posted above somewhere, and install it on your Garry's Mod server. Be sure to read the thread for the addon as well so you know how to use it in code.

3. Create a database for your gamemode, and connect to it with MySQLOO. You can also now connect your website, or other game servers to share the same data.

4. You can start using the functions MySQLOO has to run [URL="https://www.tutorialspoint.com/mysql/mysql-select-database.htm"]SQL queries[/URL] and read/write your data to the database.

That's the best I can explain it without holding your hand through it, which I don't really want to do, and shouldn't. It's a pretty easy setup, learning the queries is a little different, but it's not hard either.