MySQLOO v9.5
A mostly rewritten version of MySQLOO. Made by andyvincent, update by Drakehawke and KingofBeast.
Link to previous thread: http://forum.facepunch.com/showthread.php?t=1357773
Changes:
05.08.2017:
Fixed queries run in coroutines.
31.05.2017:
Added db:setCharacterSet()
Added db:disconnect()
Fixed a crash when starting and waiting for a query in the callback of the database
07.03.2017:
fixed query:error() not working in transactions
06.03.2017:
Fixed query:error() not returning any results/crashing
28.01.2017:
Fixed a memory leak as well as a rare crash that could occur involving prepared statements.
12.01.2017:
Queries can now be started multiple times
PreparedQueries now cache and reuse the prepared statements allocated on the server
PreparedQueries can now return multiple result sets
Database:ping() now always attempts to reconnect to the database regardless of autoReconnect status
Added automatic update check that checks if an update is available
Added mysqloo.MINOR_VERSION
Added database:setCachePreparedStatements
slightly changed behavior of query:hasMoreResults()
Added lua library for ease of use
Added tmysql4 wrapper
Added connection pool lua library
30.12.2016:
If a callback errors the error is now printed to the console instead of being silently ignored.
15.06.2016:
Fixed constants to match older versions of mysqloo
13.05.2016:
Fixed a crash caused by transactions on shutdown
30.04.2016:
Added database:ping()
Changed database:wait() to allow swapping the query to the front
Fixed setMultiStatements and setAutoReconnect
Initial release:
Added prepared queries (should be used instead of escaping)
Added transactions (Still kind of experimental)
Database instances now have autoreconnect enabled by default
Queries can now have multiple result sets
Added Database:queueSize()
Fixed memory leak issues with previous versions
Fixed threading issues with previous versions
Documentation: (mostly copied from Drakehawke)
[LUA]
– mysqloo table
mysqloo.connect( host, username, password [, database, port, socket] )
– returns [Database]
– Initializes the database object, note that this does not actually connect to the database.
mysqloo.VERSION – [String] Current MySQLOO version (currently “9”)
mysqloo.MINOR_VERSION – [String] minor version of this library
mysqloo.DATABASE_CONNECTED – [Number] - Database is connected
mysqloo.DATABASE_CONNECTING – [Number] - Datbase is connecting
mysqloo.DATABASE_NOT_CONNECTED – [Number] - Database is not connected
mysqloo.DATABASE_INTERNAL_ERROR – [Number] - Internal error
mysqloo.QUERY_NOT_RUNNING – [Number] - Query not running
mysqloo.QUERY_WAITING – [Number] - Query is queued/started
mysqloo.QUERY_RUNNING – [Number] - Query is being processed (on the database server)
mysqloo.QUERY_COMPLETE – [Number] - Query is complete
mysqloo.QUERY_ABORTED – [Number] - Query was aborted
mysqloo.OPTION_NUMERIC_FIELDS – [Number] - Instead of rows being key value pairs, this makes them just be an array of values
mysqloo.OPTION_NAMED_FIELDS – [Number] - Not used anymore
mysqloo.OPTION_INTERPRET_DATA – [Number] - Not used anymore
mysqloo.OPTION_CACHE – [Number] - Not used anymore
– Database object
– Functions
Database:connect()
– Returns nothing
– Connects to the database (non blocking)
– This function calls the onConnected or onConnectionFailed callback
Database:disconnect(shouldWait)
– Returns nothing
– disconnects from the database and waits for all queries to finish if shouldWait is true
Database:query( sql )
– Returns [Query]
– Initializes a query to the database, [String] sql is the SQL query to run.
Database:prepare()
– Returns [PreparedQuery]
– Creates a prepared query associated with the database
Database:createTransaction()
– Returns [Transaction]
– Creates a transaction that executes multiple statements atomically
– Check https://en.wikipedia.org/wiki/ACID for more information
Database:escape( str )
– Returns [String]
– Escapes [String] str so that it is safe to use in a query.
– If the characterset of the database is changed after connecting, this might not work properly anymore
Database:abortAllQueries()
– Returns the amount of queries aborted successfully
– Aborts all waiting (QUERY_WAITING) queries
Database:status()
– Returns [Number] (mysqloo.DATABASE_* enums)
– Checks the connection to the database
– This shouldn’t be used to detect timeouts to the server anymore (it’s not possible anymore)
Database:setAutoReconnect(shouldReconnect)
– Returns nothing
– The autoreconnect feature of mysqloo can be disabled if this function is called with shouldReconnect = false
– This has to be called before Database:connect() to work
Database:setMultiStatements(useMultiStatemets)
– Returns nothing
– Multi statements (“SELECT 1; SELECT 2;”) can be disabled if this function is called with useMultiStatemets = false
– This has to be called before Database:connect() to work
Database:setCachePreparedStatements(cachePreparedStatements)
– Returns nothing
– This will disable all caching of prepared query handles
– which will reduce the performance of prepared queries that are being reused
– Set this to true if you run into the prepared query limit imposed by the server
Database:wait()
– Returns nothing
– Forces the server to wait for the connection to finish. (might cause deadlocks)
– This has to be called after Database:connect()
Database:serverVersion()
– Returns [Number]
– Gets the MySQL servers version
Database:serverInfo()
– Returns [String]
– Fancy string of the MySQL servers version
Database:hostInfo()
– Returns [String]
– Gets information about the connection.
Database:queueSize()
– Returns [Number]
– Gets the amount of queries waiting to be processed
Database:ping()
– Returns [Boolean]
– Actively checks if the database connection is still up and attempts to reconnect if it is down
– This will freeze your server for at least 2 times the pingtime to
– the database server if the connection is down
– returns true if the connection is still up, false otherwise
Database:setCharacterSet(charSetName)
– Returns [Boolean, String]
– Attempts to set the connection’s character set to the one specified.
– Please note that this does block the main server thread if there is a query currently being ran
– Returns true on success, false and an error message on failure
– Callbacks
Database.onConnected( db )
– Called when the connection to the MySQL server is successful
Database.onConnectionFailed( db, err )
– Called when the connection to the MySQL server fails, [String] err is why.
– Query/PreparedQuery object (transactions also inherit all functions, some have no effect though)
– Functions
Query:start()
– Returns nothing
– Starts the query.
Query:isRunning()
– Returns [Boolean]
– True if the query is running or waiting, false if it isn’t.
Query:getData()
– Returns [Table]
– Gets the data the query returned from the server
– Format: { row1, row2, row3, … }
– Row format: { field_name = field_value } or {first_field, second_field, …} if OPTION_NUMERIC_FIELDS is enabled
Query:abort()
– Returns [Boolean]
– Attempts to abort the query if it is still in the state QUERY_WAITING
– Returns true if aborting was successful, false otherwise
Query:lastInsert()
– Returns [Number]
– Gets the autoincrement index of the last inserted row of the current result set
Query:status()
– Returns [Number] (mysqloo.QUERY_* enums)
– Gets the status of the query.
Query:affectedRows()
– Returns [Number]
– Gets the number of rows the query has affected (of the current result set)
Query:setOption( option )
– Returns nothing
– Changes how the query returns data (mysqloo.OPTION_* enums).
Query:wait(shouldSwap)
– Returns nothing
– Forces the server to wait for the query to finish.
– This should only ever be used if it is really necessary, since it can cause lag and
– If shouldSwap is true, the query is being swapped to the front of the queue
– making it the next query to be executed
Query:error()
– Returns [String]
– Gets the error caused by the query (if any).
Query:hasMoreResults()
– Returns [Boolean]
– Returns true if the query still has more data associated with it (which means getNextResults() can be called)
Query:getNextResults()
– Returns [Table]
– Pops the current result set, chaning the results of lastInsert() and affectedRows() and getData()
– to those of the next result set. Returns the rows of the next result set in the same format as getData()
– Throws an error if attempted to be called when there is no result set left to be popped
– Callbacks
– ALWAYS set these callbacks before you start the query or you might run into issues
Query.onAborted( q )
– Called when the query is aborted.
Query.onError( q, err, sql )
– Called when the query errors, [String] err is the error and [String] sql is the SQL query that caused it.
Query.onSuccess( q, data )
– Called when the query is successful, [Table] data is the data the query returned.
Query.onData( q, data )
– Called when the query retrieves a row of data, [Table] data is the row.
– PreparedQuery object
– A prepared query uses a prepared statement that, instead of having the actual values of
– new rows, etc. in the query itself, it uses parameters, that can be set using the appropriate methods
– PreparedStatements make sql injections pretty much impossible since the data is sent seperately from the parameterized query
– Prepared queries also make it easy to reuse one query several times to insert/update/delete multiple rows
– An example of a parameterized query would be “INSERT INTO users (steamid, nick) VALUES(?,?)”
– A few types of queries might not work with prepared statements
– For more information about prepared statements you can read here: https://en.wikipedia.org/wiki/Prepared_statement
PreparedQuery:setNumber(index, number)
– Returns nothing
– Sets the parameter at index (1-based) to be of type double with value number
PreparedQuery:setString(index, str)
– Returns nothing
– Sets the parameter at index (1-based) to be of type string with value str
– Note: str should not!! be escaped
PreparedQuery:setBoolean(index, bool)
– Returns nothing
– Sets the parameter at index (1-based) to be of type boolean with value bool
PreparedQuery:setNull(index)
– Returns nothing
– Sets the parameter at index (1-based) to be NULL
PreparedQuery:putNewParameters()
– Returns nothing
– This shouldn’t be used anymore, just start the same prepared multiple times with different parameters
– Transaction object
– Transactions are used to group several seperate queries or prepared queries together.
– Either all queries in the transaction are executed successfully or none of them are.
– Please note that single queries are already transactions by themselves so it usually only makes sense to have transactions
– with at least two queries.
– Since mysqloo works async, much of the power of transactions (such as manually rolling back a transaction) cannot be used properly, but
– there’s still many areas they can be useful.
Transaction:addQuery(query)
– Adds a query to the transaction. The callbacks of the added queries will not be called.
– query can be either a PreparedQuery or a regular Query
Transaction:getQueries()
– Returns all queries that have been added to this transaction.
– Callbacks
Transaction.onError(tr, err)
– Called when any of the queries caused an error, no queries will have had any effect
Transaction.onSuccess()
– Called when all queries in the transaction have been executed successfully
[/LUA]
tmysql4 Wrapper
Since tmysql4 is not actively maintained anymore I have included a tmysql4 wrapper which can be found here
I also wrote a library that makes using mysqloo easier and less verbose.
Some important changes between mysqloo9 and mysqloo8 that might cause incompatibilities:
Bigint fields now return as numbers. This might be problematic if you want to use a bigint as the primary key and select it from the server (such as steamid64).
In these cases make sure to cast the bigint to a string in your SQL query like this: SELECT (CAST steamid64 as ‘CHAR’) as steamid64 FROM …
Bit fields now return as numbers (0 or 1)
OPTION_NAMED_FIELDS and OPTION_CACHE don’t have any effect anymore since named fields is the default setting and all data is cached now.
OPTION_INTERPRET_DATA is unused as I fail to see any use (if anyone needs it I can easily readd it)
Examples on how to use old and new features can be found in the github repository.
Download:
There are prebuilt binaries available for Linux and Windows.
They have to be placed within your garrysmod/lua/bin/ folder (create the bin folder if it does not exist)
You are also going to need either libmysql on Windows or libmysqlclient on Linux.
They have to be placed within the root folder of your server (the one that contains srcds.exe or srcds_run)
Bugs:
As this release contains some experimental features there might be some bugs.
If you find any bugs or you think your server crashes because of this module, please let me know in this thread.
Additionally, feel free to contribute on GitHub.
