Which better sv.db or mysqloo?

[Koji6ac9H]

Hello everyone.
On my server I need frequent synchronization with the database. Before I had server and site on different hosts, but now I've moved to the DS, What will advise?
Easies way how i think it's sv.db, cuz i can using data at site and server w/o sync., it will not hinder the work cuz db working with server and site аt the same time?

P.S. Sorry for my English

[crazyscouter]

Use MySQLOO if you want to save the data remotely, for instance, using it between servers... not sure what you're really asking though, tbh.

[G4MB!T]

It SOUNDS like you are asking how you can synchronize multiple servers using the sv.db on each one. The short answer is: [b]Dont[/b]. The long answer is the same but I'll explain a bit.

[t]https://photos-4.dropbox.com/t/0/AABg81CVRtFgudC03V4zlhfXNV-YizN2Ln5VprKWaHmPAQ/12/209507621/png/1024x768/3/1408971600/0/2/databasing101.png/MRkzesGDQg2wohoIZ2qbVk-AgkL-fqOZ8BVjDPywa5A[/t]

The top one is what I gathered you are doing. Something (like an admin mod) uses SQLITE (sv.db) to store rank data about each player. This setup is fine for a single server configuration but since you cant sync it across multiple servers, its a pretty dodgy setup. The bottom one is the best option for having multiple servers accessing a database. You store your rank data on the database, and have each server query the database for the information. You do not need to sync anything and the database can even be accessed outside of Garry's Mod for all sorts of reasons like displaying admin ranks on your community website or whatever.

[Cyberuben]

[QUOTE=G4MB!T;45790129]It SOUNDS like you are asking how you can synchronize multiple servers using the sv.db on each one. The short answer is: [b]Dont[/b]. The long answer is the same but I'll explain a bit.

[t]https://photos-6.dropbox.com/t/0/AABYOECNBdUUABSjkmBXZRzofJubQXS99vd32bT4ZdWkIw/12/209507621/png/1024x768/3/1408935600/0/2/databasing101.png/MRkzesGDQg2wohoIZ2qbVk-AgkL-fqOZ8BVjDPywa5A[/t]

The top one is what I gathered you are doing. Something (like an admin mod) uses SQLITE (sv.db) to store rank data about each player. This setup is fine for a single server configuration but since you cant sync it across multiple servers, its a pretty dodgy setup. The bottom one is the best option for having multiple servers accessing a database. You store your rank data on the database, and have each server query the database for the information. You do not need to sync anything and the database can even be accessed outside of Garry's Mod for all sorts of reasons like displaying admin ranks on your community website or whatever.[/QUOTE]

Make sure your database is in the same datacenter and (if possible) on the same machine as your game server. As soon as the servers need to go through a WAN IP to get to the MySQL server, queries will take longer and in the worst case freeze up your server until the query is completed. I advice to not ever use a database that is hosted in another city than your game server.

[G4MB!T]

[QUOTE=Cyberuben;45790208]Make sure your database is in the same datacenter and (if possible) on the same machine as your game server. As soon as the servers need to go through a WAN IP to get to the MySQL server, queries will take longer and in the worst case freeze up your server until the query is completed. I advice to not ever use a database that is hosted in another city than your game server.[/QUOTE]

I'm pretty sure most datacenters have a fiber connection right now and MySQL does not block either. Unless you actually own a slot at a datacenter, the chance that you will get a game server and an SQL database in same datacenter is fairly small.

[microkiller]

just use mysqloo, saves problems with ongoing problems and stuff going missing.

[G4MB!T]

[QUOTE=microkiller;45790428]just use mysqloo, saves problems with ongoing problems and stuff going missing.[/QUOTE]

Its the only SQL wrapper for Garry's Mod ...

[Хум]

[QUOTE=G4MB!T;45790444]Its the only SQL wrapper for Garry's Mod ...[/QUOTE]

What about tmysql?

[G4MB!T]

[QUOTE=HumbleTH;45790476]What about tmysql?[/QUOTE]

I thought that died with GM13.

[ph:lxyz]

Use a real database like PostgreSQL if you can... MySQL is the 'PHP' of the database world. Sure, it works, but it's inconsistent and (depending on how you set it up) unsafe by default. I guess it doesn't matter too much for a gmod server.

[G4MB!T]

[QUOTE=ph:lxyz;45792061]Use a real database like PostgreSQL if you can... MySQL is the 'PHP' of the database world. Sure, it works, but it's inconsistent and (depending on how you set it up) unsafe by default. I guess it doesn't matter too much for a gmod server.[/QUOTE]

Actually, MySQL is fine, even if its just for general databasing. Enterprises use it and stuff.

[ph:lxyz]

[QUOTE=G4MB!T;45792080]Enterprises use it and stuff.[/QUOTE]

I work in an company that uses it. We're replacing it right now.

One word: Oracle.

Also: [url]https://www.youtube.com/watch?v=1PoFIohBSM4[/url]

[G4MB!T]

[QUOTE=ph:lxyz;45792216]I work in an company that uses it. We're replacing it right now.

One word: Oracle.

Also: [url]https://www.youtube.com/watch?v=1PoFIohBSM4[/url][/QUOTE]

Oracle basically super charged MySQL and turned it into this powerful beast that you can control for $30/month (made up pricing :l). On the other hand, its a Garry's Mod server, and honestly if you check your types, you wont run into silly errors. That being said, MySQL does a pretty bang-up job, but it does the job (sort of).

[Koji6ac9H]

Damn. Need to learn English, ppl can't understand me :tinfoil:

[G4MB!T]

Considering this is an English forum, thats probably a good idea.

[StarFox]

I had a really bad experience with mysql in general with Garry's Mod, I used it with my nutscript schema and I had tons of data that wouldn't save like deleting a character had no effect.

[ph:lxyz]

I will still enable server admins to choose the database backend they use in my "SeriousRP" gamemode because - while I dislike MySQL, it is still a viable option and I recognise that people will want to use it.

[Teddi]

To be fair, mySQL isn't so much the PHP of the database world, as it's more the abused child when it was taken in by another family. It's perfectly viable and has some odd pitfalls (design is more lets push the data no matter what) but for small situations, eg a game server there's nothing wrong with it.

That and I think there's no current Postgre binary module for GMod at the moment.

[ph:lxyz]

[QUOTE=Teddi Orange;45793939]To be fair, mySQL isn't so much the PHP of the database world, as it's more the abused child when it was taken in by another family. It's perfectly viable and has some odd pitfalls (design is more lets push the data no matter what) but for small situations, eg a game server there's nothing wrong with it.

That and I think there's no current Postgre binary module for GMod at the moment.[/QUOTE]

I'll make one at some point but it might be a way off yet since sqlite will do for development.

[Acecool]

TMySQL is actually a great system. The only thing I don't like is by default it returns numbers as keys instead of the column names. It can easily be changed though by adding a flag. I made my own wrapper that will grab the value regardless ( so it could use MySQLOO or TMySQL without needing any logic checks ).

Also, I wrote a Query generator with ability to join tables together. I wrote a basic one that creates insert/update/select queries in one function; then as a test I made one that joins two tables, and another that will do unlimited tables. Once I combine them all together into one query generator, it'll be released with my dev-base.

Here are some basic initialization hooks for whichever MySQL db you use:

Basic: [url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_mysqloo.lua.html[/url]

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_tmysql.lua.html[/url]

With fallbacks to use either or: [url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_fallbacks_in_place.lua.html[/url]

Those 3 will call a hook depending on whether or not the connection was successful; from in those hooks you can force the server to quit / shutdown, restart the map, or whatever else you want.

Simple queries / structures ( I typically prefer the SET method for updates / inserts, the generator when done will allow both ): [url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/basic_queries_and_query_formats_used_to_check_for_existing_row.lua.html[/url]

If you ever decide to convert SV.DB to MySQL, there is a lot of work involved to do it right. For example, the player p data is stored like userid[column_name].. Essentially for a proper conversion, you'd grab all the relevant data and format it into a table with the userid being the primary key for each user, then add information to each table you want to insert / create based on information read from the db.

Here is an example of converting PlayerPData to MySQL DB Insert queries after formatting and adding extra fields to populate as users join ( such as SteamID since we are only using userid.. )

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/converting_sv_db_to_mysql.lua.html[/url]

Hopefully these tutorials and information helps. As always, to view the Lua from any of my tutorials to enable copy/pasting ( html doesn't copy/paste well in terms of Lua ), remove .html from the url.