gmsv_mysql - The newest MySQL module out there!

gmsv_mysql 1.0
gmsv_mysql is a module for Garry’s Mod servers that enables communication with a MySQL server. The other widely available MySQL modules are gmsv_tmysql and gmsv_mysqloo. Both modules do their job but lack features that the other has. Thus, gmsv_mysql aims to provide the best of both worlds.

This is the first public version of gmsv_mysql, and it needs testing. In-house, everything seems to work but more bugs are sure to be uncovered with wider usage. If you come across anything that breaks, doesn’t work, or just have suggestions, don’t hesitate to post here!

Features



Fully object-oriented design
Support for multiple resultsets
Executes queries in the order they're created
Fully asynchronous (except if you want instant results!)
Queries can hold the main thread on request
Prepared Statements!


Building
Linux and Windows builds should be straightforward. Premake5 is required, and BuildProjects.bat will plop solution files into a solutions folder. Visual Studio should compile without complaints. Linux build requires C++11 and the makefiles may need to have -m32 added to CXXFLAGS.

Installation
Place the built DLL into garrysmod/lua/bin. The MySQL library dll is not required for gmsv_mysql as it is built directly into the finished DLL!
GitLab: Click
Windows: gmsv_mysql_win32.dll
Linux: gmsv_mysql_linux.dll

Disclaimers
The MySQL C Connector APIs for both Windows and Linux are included in this repository. This is only a convenience to those wanting to build the module. We don’t claim any credit for it and if Oracle wishes, we will remove it from the repository. Additionally, when updates to the module are made, we will ensure that the packaged connector API is up-to-date.

Prepared Statements functionality is emulated. True prepared statements using the C Connector API typically expects the compiler to know beforehand how much data will go to and be received from the MySQL server. Since that simply won’t be the case, our prepared statements work as you would expect one to, though they’re fundamentally different. The module escapes the input and formulates a finished query which runs normally. This means that typical benefits won’t be observed (except faster escaping and convenience). It is possible to implement true prepared statements, but it involves a fair amount of memory management. Maybe we’ll have them in the future.

gmsv_mysql uses code from both gmsv_mysqloo and gmsv_tmysql. For that code, credit goes to its respective authors. The goal with gmsv_mysql is only to bring every feature possible to one module.

Documentation

Global



DATABASE_CONNECTED = 0
DATABASE_CONNECTING = 1
DATABASE_NOT_CONNECTED = 2
DATABASE_INTERNAL_ERROR = 3

QUERY_NOT_RUNNING = 0
QUERY_RUNNING = 1
QUERY_READING_DATA = 2
QUERY_COMPLETE = 3
QUERY_ABORTED = 4
QUERY_WAITING = 5

CLIENT_INTERACTIVE = 1024
CLIENT_MULTI_STATEMENTS = 65536
CLIENT_MULTI_RESULTS = 131072


Module Table



mysql.MODULE_VERSION -- Returns an integer that represents the module version
mysql.MODULE_INFO -- Returns a string that represents the module version
mysql.MYSQL_VERSION -- Returns an integer that represents the MySQL client library version
mysql.MYSQL_INFO -- Returns a string that represents the MySQL client library version

mysql.Connect(<string host>, <string user>, <string pass>, <string database>, [number port = 3306], [string unixSocket = 0], [number clientFlags = interactive,multistatements,multiresults]) -- Returns a database object configured with the given parameters


Database Object



dbase:Connect() -- Initiates a connection to the MySQL server
dbase:Wait()
dbase:Disconnect() -- Closes the connection to the MySQL server
dbase:Query(<string query>) -- Returns a query object configured with the given query
dbase:Prepare(<string query>) -- Returns a statement object to be used as a prepared statement
dbase:Status() -- Returns the current connection status to the MySQL server
dbase:Escape(<string>) -- Returns the escaped version of the input
dbase:GetCharSet() -- Returns the current character set
dbase:SetCharSet(<string charSet>) -- Sets the current character set
dbase:HostInfo() -- Returns the current host info (e.g. 'thed3vine.net via TCP/IP')
dbase:ServerVersion() -- Returns an integer that represents the MySQL server's version
dbase:ServerInfo() -- Returns a string that represents the MySQL server's version
dbase:GetAllQueries() -- Returns a table of all processing and waiting query objects

function dbase.OnConnected(self) -- Called by the module when the database successfully connects
function dbase.OnConnectionFailed(self, err) -- Called by the module when the database connection fails


Query Object



query:Start() -- Adds the query to the end of the database object's processing stack
query:Wait() -- Processes the query while holding the main thread
query:Abort() -- Cancels the query if it isn't already processing
query:IsRunning() -- Returns true if the query is being processed
query:Status() -- Returns the current status of the query
query:GetData() -- If the query has completed, returns a reference to the table passed to query.OnCompleted

function query.OnCompleted(self, results) -- Called by the module when the query is completed


Statement Object
Prepared statements are a convenient way to run the same query multiple times with different inputs. Supply a query to dbase:Prepare() using question marks to symbolize the input you’ll give to it later, then call statement:Run() with the variables you’d like to use. The module will take care of plopping your input into the full query and running it, returning to a callback. statement:Run will take any amount of arguments (including 0) and the callback is optional.



statement:Run([arg1], [arg2], [arg3], [function callBack(table results)])
statement:Wait(<bool shouldWait = false>) -- If true, future statement:Run calls will process synchronously


Results Structure
Overwrite your query object’s OnCompleted function to run when it’s finished.



{
  1 = { -- First resultset
    Success = true,
    Affected = 1, -- Number of affected rows. Represents number of received rows in select queries
    LastID = 0, -- Number representing the last inserted ID. 0 if the table does not auto_increment
    Data = {
      -- Table containing all received rows. Each row uses named fields
    }
  },

  2 = { -- Second resultset
    Success = false,
    ErrorID = 1146,
    Error = "Table 'database.table' doesn't exist"
  },

  3 = { -- Third resultset
    Success = false,
    Aborted = true
  }
}


tmysql4 does all of this and without the terrible syntax mysqloo has, so what benefits does this have over tmysql4?

[editline]17th October 2015[/editline]

you also seem to have used tmysql code without crediting the author, but still say tmysql lacks?
uhm?
also why are you making a new thread for each query, like what

[editline]17th October 2015[/editline]

Especially if you’re waiting on the main thread why does it create a new thread for that instead of doing the query on the main thread itself

One of the bigger things here is support for prepared statements. As the OP states at the moment they’re emulated but work as expected, so that you get the convenience of it without needing a wrapper, and the module handles all of the escaping in those which is easy plus eliminates the time going back in forth from Lua->C->Lua to escape data. Eventually the module will have true prepared statements as well.

I made the module primarily to use on my own servers because I prefer the OO approach that gmsv_mysqloo had but it was plagued with a lack of features. I made it public because why not.

Also, each query does not get a new thread. There is 1 worker thread that the database object uses to run through queries that are ready to be processed. A query gets its own thread only if wait() is called on it.
Under disclaimers, I note that code will be reminiscent of both modules.

Like you literally ripped shit from tmysql4

https://bitbucket.org/breakpointservers/bkacjios-glua-modules/src/c2d54eb24ba98c90911c88e01a9a4f6da3660526/gm_tmysql4/src/database.cpp?at=default&fileviewer=file-view-default#database.cpp-141

And then claim you added functionality that it didn’t have. Alrighty then. Can we please let mysqloo die?

Your prepared statements aren’t true prepared statements and you can -very- easily make prepared statements of sorts in lua. crossing the lua->c barrier isn’t a bad thing because as far as i can see it’d do it with your prepared statements too.
This OO is nearly worse than what NodeJS has people using and thats pretty unfortunate.
Why would you create a new thread on wait when you have the main thread there for you?
Code being reminiscent is different from being an exact copy.

Look, I’ve said under disclaimers that the module uses code from both of the others. I’ll change it to a word more assertive than reminiscent, and note that I’m not taking credit for it.

There’s a simple fact that some people still use gmsv_mysqloo and obviously something is keeping them from switching to gmsv_tmysql. And, just like the last sentence at the top of the post says, this module will give a middle ground.

The thread is created on q:Wait() only if the worker is not already on it. You’re right, I can have it process on the main thread but it’s a definite gain from gmsv_mysqloo creating a thread for every query. I’ll make it process waited queries on the main thread.
As for the Lua->C barrier, all of the escaping is done by the module thereby eliminating (x) Lua->C calls for DB:Escape. Plus, the fact that prepared statements can essentially be done with Lua doesn’t make it a bad thing that the module brings it out-of-the-box.

I apologize that I didn’t state plainly that there’s code used from both modules, but if that’s your only point from here on I’d appreciate that the flaming stop.

Then say that outright, you just don’t take code from someone and leave their name out of it completely.

Literally the only reason mysqloo is still used is because of legacy addons. Pointshop mainly.

It’s not flaming, you’re literally stealing code.

Here have some more.

https://bitbucket.org/breakpointservers/bkacjios-glua-modules/src/c2d54eb24ba98c90911c88e01a9a4f6da3660526/gm_tmysql4/src/gm_tmysql.cpp?at=default&fileviewer=file-view-default#gm_tmysql.cpp-274

Adding to the prepared statement would cause more overhead than having a lua 1:1 clone use the mysql escape function instead(which can also be emulated in lua)
You can also use string.format with %q, eg this wrapper

Updated OP

  • Fixed source to compile cleanly under Linux
  • query:Wait() now runs the query on the main thread instead of creating a new worker
  • Added Linux download link

Can you allow escaping of strings including null bytes? If you really wanted to be able to claim something as your own you can legally rip code from https://github.com/willox/gmsv_mysql/tree/master/src/MySQL

@Willox, the default behavior from mysqloo’s escape function allocates stringlen*2+4 for the buffer. Correct me if I’m wrong but from what I understand that’s what lets it handle strings with null bytes and dual-byte characters, right?

Updated builds:

  • Fixed scenarios where queries wouldn’t run their callbacks and would mess up the Lua stack instead
  • Statements no longer throw soft errors if there’s no callback to run
  • Minor memory management work, trying to work out where mysqloo leaked memory

strlen will get the length of a null-terminated string. I don’t know why strlen * 2 + 4 would be allocated. MySQL’s documentation itself states that only “length*2+1” would be necessary in the worst case scenario of every char in the string needing escaping.

https://gitlab.com/D3vine/gmsv_mysql/blob/master/include/gmodlua/include/GarrysMod/Lua/LuaBase.h#L46 (the 2nd parameter)

My request is that escape( “\0” ) == “\0”

maybe check out this: https://github.com/keplerproject/luasql

they have support for prepared statements

(for those of you who frequent this part of the forums, yes, I do have an agenda)

Doesn’t appear to be asynchronous whatsoever, a big no-no for a real-time video game.

Still, while not something to directly port, the code’s not entirely useless for a developer wanting to figure out how to implement prepared statements

Updated builds:

  • Queries spawned by statements are now deleted as soon as they finish
  • Cleaned up some code that sometimes caused Lua stack corruption
  • DB:Escape now escapes null-terminators within strings ("\0" -> “\0”)