Writing an Addon: Best choice of MySQL library?

I need my addon (Intended for sale) to be able to run on anybodys server (Including people who use a Hosting Service), but gmsv_mysqloo v8 requires them to place a file in the SRCDS folder (http://forum.facepunch.com/showthread.php?t=1220537). I know some Hosts dont let you do this, is there a better MySQL library to use? What are peoples preferences?

There is no way to use MySQL without the libmysql library (at least I don’t know any). You will probably have to use the built in SQLite database if you want to save some data.

If such thing existed I bet mysqloo wouldn’t be a thing.

You could set it up to use a wrapper. Write several to go with the addon; one that uses flatfile or that uses sqlite which is included with GMod, and another for MySQLOO / TMySQL

Thanks Acecool. Could I presume most hosts would have the libmysql by default?

With MySQLOO can i put it in the same directory as my Addon and use require to call it or does it have to go in /lua/bin

I know some hosts have no problem adding libmysql, but I’m sure there are some that may not allow it. libmysql needs to be in the same directory as srcds.exe / hl.exe; so adding it to the addon wouldn’t work. Although, if you’re allowed to package .dll inside of gma files, you could add one to lua/bin, but I believe .dll is blacklisted.

If you write it up like a wrapper, you would just call the same functions throughout your code, but depending on the config option, you’d code it to redirect those calls through your wrapper to something else such as File I/O, SQLite or MySQL.

It is quite common to write things that way.

You could try static linking to libmysql (a bit more complicated though)

Thanks guys, any other tips? Advice is wanted regarding MySQL libraries that are avalaible, TMySQL vs SQLOO

TMySQL by default uses numerical keys when returning a table from a query; OO uses column-names. With T you can still return string keys by adding a flag after the callback function in the query function arguments.

OO does things slightly different but they aren’t ( they’re’nt O_o ) much different…

Connecting with OO
https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_mysqloo.lua.html

with T
https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_tmysql.lua.html

Both / fallback in place
https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_fallbacks_in_place.lua.html

I don’t have “Query” tutorials up for both yet, but they aren’t too different.

Thanks. MySQLOO seems better suited for what im doing. It also just looks nicer! All hail Acecool, please link to your classes thing, I can never find it no matter where i look?

It’ll be on my site once I revive it; class isn’t 100% ready yet.

Right now most of the stuff in “one place” is in the skeletonized dev-base I released. A bunch of stuff is still being added to it too.

It is recommended that you use a form of GIT/SVN software to allow for easy updating.
Src: https://bitbucket.org/Acecool/acecooldev_base/src/
Issue-Tracker: https://bitbucket.org/Acecool/acecooldev_base/issues/
Pull-Requests: https://bitbucket.org/Acecool/acecooldev_base/pull-requests/
Wiki: https://bitbucket.org/Acecool/acecooldev_base/wiki/Home/

-snip-

Both tmysql and mysqloo have huge downsides.

  1. Neither of them enforce the order of execution.
    Let’s look at this scenario:
    At the start the player has $500
    The player buys something for $50 from a shop and the next tick he gets fined $450.
    In your queries you are setting the money (e.g. “UPDATE player_tbl SET money = %s WHERE …”)
    Possible results for player’s money:
    $450, $50, $0 (not equally likely but all still possible)

  2. Tmysql can only establish a connection to one database, so if you need to access multiple you are screwed.

  3. Mysqloo creates one thread per query, even though only one thread can run at the same time.
    So if you create 5000 queries you end up having 1 thread that is working and 4999 threads that are sleeping. (This can also result in lock starvation)
    Also the reason mysqloo doesn’t enforce the order of execution is because the next thread that is allowed
    to run is random and not ordered.

  4. Mysqloo currently has a huge memory leak. It tries to use reference counting as garbage collection.
    The problem is that reference counting fails if you have circular referencing. And exactly that happens
    when it runs a callback.

  5. Mysqloo’s escape function is potentially blocking which can result in massive stutters.

So as you can see Tmysql doesn’t have quite as many downsides as mysqloo.
If you rely on multiple connections you have to use mysqloo though (even though it sucks ass)

I currently use T, but it uses a queue and a Lua hook to process requests. It is bad practice to rely on MySQL / Database in “real-time”… What I mean is: Grab the money the player has, keep it in memory while the player is on the server and use the data in memory for deductions, etc… Don’t update the MySQL for EVERY thing the player does that may change their data.

Never rely on something that uses a callback for real-time information, or for checking of data. Always pull data into memory that will be used and manipulate the data in memory. Schedule updates ( instead of updating after every change ), but don’t rely on information coming from the database except the initial sync.