Update to MySQLOO v9

My Dime Is Up · Jun 15, 2016

MySQLOO v9
A mostly rewritten version of MySQLOO. Made by andyvincent, update by Drakehawke and KingofBeast.

Link to previous thread: https://facepunch.com/showthread.php?t=1357773

Changes:
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 databaseing()
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)
-- 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

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

mysqloPTION_NUMERIC_FIELDS -- [Number] - Instead of rows being key value pairs, this makes them just be an array of values
mysqloPTION_NAMED_FIELDS -- [Number] - Not used anymore
mysqloPTION_INTERPRET_DATA -- [Number] - Not used anymore
mysqloPTION_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:query( sql )
-- Returns [Query]
-- Initializes a query to the database, [String] sql is the SQL query to run.

Databaserepare()
-- 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: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

Databaseing()
-- Returns [Boolean]
-- Actively checks if the database connection is still up
-- 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

-- 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 (mysqloPTION_* 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 has any more result sets associated with it
-- Note: A prepared query can only return one result set

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
-- Note: A prepared query can only return one result set

-- Callbacks
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

PreparedQueryutNewParameters()
-- Returns nothing
-- Adds an additional row of parameters to the prepared query, this allows for
-- multiple statements only using one query (doesn't work with SELECT statements)
-- Note: if the query errors at parameter row n, all rows (1..n-1) have already being executed and will not be reverted
-- You can change this by placing the preparedQuery inside of a transaction.

-- 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.

-- Calbacks

Transaction.onError(tr, err)
-- Called when any of the queries caused an erro, no queries will have had any effect

Transaction.onSuccess()
-- Called when all queries in the transaction have been executed successfully
Click to expand...

My Dime Is Up · Jun 15, 2016

https://facepunch.com/showthread.php?t=1515853

Reached the 10K post limit xD

This would also not break anything since the MySQL module has been made backwards compatible with older MySQL addons.

Chai · Jun 15, 2016

Why?

My Dime Is Up · Jun 15, 2016

Chai said: ↑

Why?
Click to expand...

syl0r said:

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
Click to expand...

My Dime Is Up · Jun 15, 2016

@Highwon forgoto to tag you since I reached the 10K limit

Chai · Jun 15, 2016

But is it better than https://facepunch.com/showthread.php?t=1442438 ?

My Dime Is Up · Jun 15, 2016

Chai said: ↑

But is it better than https://facepunch.com/showthread.php?t=1442438 ?
Click to expand...

WAS better than v8.1 but v9 is out and people are saying its not MUCH better, but certainly better than tmysql4

Revenge282 said:

As someone still using tmysql3 for everything, I have to ask the golden question:

MySQLOO v9 vs. TMySQL4

Why should I use one or the other, advantages, disadvantages, performance, etc.? (Prepared statements aside)
Click to expand...

syl0r said:

The major reason to use MySQLOO are prepared statements and transactions. Both of these do not exist in the current version of tmysql.

Some of you might know prepared statements from PHP or JDBC. They allow you to reuse one query (so the database server only has to parse and optimize the query once) but more importantly allow you to easily bind arguments to parameters (without having to painfully escape them all).
I recommend checking out this example.

On the other hand, transactions are mostly used to preserve logical consistency in your database model.
An example. A player puts a donut from his inventory into his storage.

These two statements only make sense to have effect if they both completed successfully, and if one of them fails (for example the database connection goes down), the player might have lost some donuts.
Transactions fix this problem by grouping both queries together into one atomic action.
To see it in action you can just look at this example.

Another advantage of this module is that all queries are executed strictly in order.
If you use this module and execute those two queries, the user will definitely have $10000 in his bank when they are done.
With tmysql4, you can't tell what's going to happen. Tmysql uses 2 threads per database instance, which introduces non-determinism and the result could be either that the user has $100000, or $10000 in their bank.

While performance wise tmysql is slightly faster (even though almost equal), performance of the mysql module really isn't a factor. Most of the time of each query is spent communicating with the db server as well as actually executing the query. The time taken to create query objects compared to networking is negligible.
Click to expand...

Paradox · Jun 16, 2016

-1 I see no use of this, atleast imo I don't, also the fact it works perfectly fine as it stands right now from what i've seen.

Log in or Sign up

Update to MySQLOO v9

Update MySQL module with faster and improved performance

Yes

No

I don't even know what this is, but yes

I don't even know what this is, but no

My Dime Is Up Its my dime to code VIP

My Dime Is Up Its my dime to code VIP

Chai returned; VIP

My Dime Is Up Its my dime to code VIP

My Dime Is Up Its my dime to code VIP

Chai returned; VIP

My Dime Is Up Its my dime to code VIP

Paradox The One Eyed Ghoul Banned Elite

Log in or Sign up

Useful Searches

Update to MySQLOO v9

Update MySQL module with faster and improved performance

Yes

No

I don't even know what this is, but yes

I don't even know what this is, but no

My Dime Is Up Its my dime to code VIP

My Dime Is Up Its my dime to code VIP

Chai returned; VIP

My Dime Is Up Its my dime to code VIP

My Dime Is Up Its my dime to code VIP

Chai returned; VIP

My Dime Is Up Its my dime to code VIP

Paradox The One Eyed Ghoul Banned Elite