• gmsv_mysqloo v8 - Object Orientated MySQL module (GMod 13)
    509 replies, posted
[h2]MySQLOO v8 - Object Orientated MySQL module - By AndyVincent[/h2] Link to previous thread: [url]http://facepunch.com/showthread.php?t=933647[/url] The original author of this module hasn't been on Facepunch in 2 years, when GMod 13 changed the way binary modules worked completely, I set about updating it and then kinda adopted it. I figured it'd be silly to have to look through 20 odd pages to find the download link for a working version so decided to make a new thread. [h2]Crashes/bugs[/h2] - Please provide code that reliably replicates the issue/crash. - Please tell me what OS you're running and whether it's on the client/listen server/dedicated server. - Double check you're using the latest version first. - You can provide crash dump files if you want -- personally I haven't got a clue what to do with them, but someone else might. - Please check the list of common problems first. [h2]Documentation[/h2] [lua]-- mysqloo table mysqloo.connect( host, username, password [, database, port, socket, flags] ) -- returns [Database] -- Initializes the database object, note that this does not actually connect to the database. mysqloo.VERSION -- [Number] Current MySQLOO version mysqloo.MYSQL_VERSION -- [Number] Current MySQL version mysqloo.MYSQL_INFO -- [String] Fancy version string mysqloo.DATABASE_CONNECTED -- [Number] 0 - Database is connected mysqloo.DATABASE_CONNECTING -- [Number] 1 - Datbase is connecting mysqloo.DATABASE_NOT_CONNECTED -- [Number] 2 - Database is not connected mysqloo.DATABASE_INTERNAL_ERROR -- [Number] 3 - Internal error mysqloo.QUERY_NOT_RUNNING -- [Number] 0 - Query not running mysqloo.QUERY_RUNNING -- [Number] 1 - Query running mysqloo.QUERY_READING_DATA -- [Number] 2 - Query is reading data mysqloo.QUERY_COMPLETE -- [Number] 3 - Query is complete mysqloo.QUERY_ABORTED -- [Number] 4 - Query was aborted mysqloo.OPTION_NUMERIC_FIELDS -- [Number] 1 - ? mysqloo.OPTION_NAMED_FIELDS -- [Number] 2 - ? mysqloo.OPTION_INTERPRET_DATA -- [Number] 4 - ? mysqloo.OPTION_CACHE -- [Number] 8 - ? -- Database object -- Functions Database:connect() -- Returns nothing -- Connects to the database Database:query( sql ) -- Returns [Query] -- Initializes a query to the database, [String] sql is the SQL query to run. Database:escape( str ) -- Returns [String] -- Escapes [String] str so that it is safe to use in a query. Database:abortAllQueries() -- Returns nothing -- Aborts all running queries to the database Database:status() -- Returns [Number] (mysqloo.DATABASE_* enums) -- Checks the connection to the database -- USE THIS FUNCTION CONSERVATIVELY - it forces the main thread to wait for all running queries to finish -- If you call this before each query it will cause lag on your server! -- Instead you should wait for a query to timeout, THEN check the connection. Database:wait() -- Returns nothing -- Forces the server to wait for the connection to finish. 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. -- 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 object -- Functions Query:start() -- Returns nothing -- Starts the query. Query:isRunning() -- Returns [Boolean] -- True if the query is running, 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 } Query:abort() -- Returns nothing -- Cancels the running query. Query:lastInsert() -- Returns [Number] -- Gets the autoincrement index of the last inserted row. 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. Query:setOption( option ) -- Returns nothing -- Changes how the query runs/returns data (mysqloo.OPTION_* enums). Query:wait() -- Returns nothing -- Forces the server to wait for the query to finish. Query:error() -- Returns [String] -- Gets the error caused by the query (if any). -- 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.[/lua] [h2]Example[/h2] [lua] require( "mysqloo" ) local db = mysqloo.connect( "123.456.789.0", "drake", "abc123", "database_name", 3306 ) function db:onConnected() print( "Database has connected!" ) local q = self:query( "SELECT 5+5;" ) function q:onSuccess( data ) print( "Query successful!" ) PrintTable( data ) end function q:onError( err, sql ) print( "Query errored!" ) print( "Query:", sql ) print( "Error:", err ) end q:start() end function db:onConnectionFailed( err ) print( "Connection to database failed!" ) print( "Error:", err ) end db:connect()[/lua] Here is a more complex example that includes a wrapper, the main point of this is to demonstrate how you should handle the database connection timing out, aka the "MySQL server has gone away" error. [lua] require( "mysqloo" ) local queue = {} local db = mysqloo.connect( "123.456.789.0", "drake", "abc123", "database_name", 3306 ) function db:onConnected() for k, v in pairs( queue ) do query( v[ 1 ], v[ 2 ] ) end queue = {} end function db:onConnectionFailed( err ) print( "Connection to database failed!" ) print( "Error:", err ) end db:connect() function query( sql, callback ) local q = db:query( sql ) function q:onSuccess( data ) callback( data ) end function q:onError( err ) if db:status() == mysqloo.DATABASE_NOT_CONNECTED then table.insert( queue, { sql, callback } ) db:connect() return end print( "Query Errored, error:", err, " sql: ", sql ) end q:start() end[/lua] [h2]Common Problems[/h2] Problem: [code]error loading module 'mysqloo' from file 'path\to\server\garrysmod\lua\bin\gmsv_mysqloo_win32.dll': %1 is not a valid Win32 application.[/code] Solution: You're missing (or have a wrong version of) libMySQL.dll, or it's in the wrong place (see Download & Installation). ----- Problem: [code] Couldn't include file 'includes/modules/mysqloo.lua' (File not found) (@path/to/file.lua (line x)) [ERROR] path\to\file.lua:x: Module not found! 1. require - [C]:-1 2. ... [/code] Solution: You're missing (or have the wrong file for your OS) the module, it should be in path/to/server/garrysmod/lua/bin. ----- Problem: Server lags considerably with a large number of players/queries ran. Solution: Make sure you're not using db:status() before each query you run - see documentation above for an explanation. ----- Problem: When you call db:connect() or query:start() nothing happens - neither the onConnected/onSuccess or onConnectionFailed/onError callbacks are ran at all. Solution: MySQLOO works using a Lua Think hook, Think isn't ran until after the first player has connected - simply join the server or run the "bot" console command. Note that all the queries will be ran in the correct order when the first player connects, so you don't have to move everything to a PlayerInitialSpawn hook. If you have a custom hook system, make sure the "MySqlOO::Poll" Think hook isn't being interfered with. Make sure you're not returning anything in any other Think hooks. [h2]Download & Installation[/h2] The binaries an
Query:lastInsert() wooo
[QUOTE=Wizard of Ass;38168874]Query:lastInsert() wooo[/QUOTE] Has always existed you know :v:
thank god, I was wondering when you'd make a new thread!
Oh.
Wich one is the best ? MySQLOO v8 or tmysql v4 ? Why ? I'm porting my gamemode to GMod 13. Should I port it to MySQLOO v8 as well ? I thank you in advance ! ;]
[QUOTE=MrManiak;38172877]Wich one is the best ? MySQLOO v8 or tmysql v4 ? Why ? I'm porting my gamemode to GMod 13. Should I port it to MySQLOO v8 as well ? I thank you in advance ! ;][/QUOTE] Purely personal preference
Just mentioning from the last thread that the latest version is working fine in the release of GM13.
So both have about the same performance ?
[QUOTE=MrManiak;38182788]So both have about the same performance ?[/QUOTE] [url]https://github.com/andyvincent/Garry-s-Mod-Extensions/blob/master/MySqlOO/source/CLASS_Query.cpp[/url] If creating OS threads is free, yes.
[QUOTE=AzuiSleet;38183174][url]https://github.com/andyvincent/Garry-s-Mod-Extensions/blob/master/MySqlOO/source/CLASS_Query.cpp[/url] If creating OS threads is free, yes.[/QUOTE] Sarcastic humor pointing out that tmysql is better than mysqloo? Serious question though, I'm not a wiz when it comes to this stuff but I do use it.
[QUOTE=AzuiSleet;38183174][url]https://github.com/andyvincent/Garry-s-Mod-Extensions/blob/master/MySqlOO/source/CLASS_Query.cpp[/url] If creating OS threads is free, yes.[/QUOTE] this one has a linux compile though! :v:
K, thanks ! :smile:
Awesome job Drakehawke!
I love you.
I have a query which runs in InitPostEntity (or at least did before testing), however it doesn't seem to be getting queued at all for once the first player joins, yet other queries are. Yet if I do things like have the OnData function, or use a simple timer with say, a 3 second delay just checking the status of the query it seems to run fine. The exact same query worked fine in GM12 and the only thing I can think of is the server is now loading in faster, but it is one of the queries I call last. Calling it manually after everything has loaded works fine. Any advice?
is this like evolve?
[QUOTE=fkim5778;38202517]is this like evolve?[/QUOTE] This is a mySQL module, not an administration mod.
[QUOTE=Teddi Orange;38202394]I have a query which runs in InitPostEntity (or at least did before testing), however it doesn't seem to be getting queued at all for once the first player joins, yet other queries are. Yet if I do things like have the OnData function, or use a simple timer with say, a 3 second delay just checking the status of the query it seems to run fine. The exact same query worked fine in GM12 and the only thing I can think of is the server is now loading in faster, but it is one of the queries I call last. Calling it manually after everything has loaded works fine. Any advice?[/QUOTE] I've always connected in InitPostEntity which might be why I haven't noticed this, out of interest where are you connecting? Initialize or directly when the file is included?
[QUOTE=Banana Lord.;38185230]this one has a linux compile though! :v:[/QUOTE] tmysql has a linux compile and is pretty much updated any time it's broken :frown: The reason I like tmysql is because this has waaaaay too much OO. But I'm glad someone has picked it up. [i]~[/i]good to have competition in the world[i]~[/i]
[QUOTE=Map in a box;38205031]tmysql has a linux compile and is pretty much updated any time it's broken :frown: The reason I like tmysql is because this has waaaaay too much OO. But I'm glad someone has picked it up. [i]~[/i]good to have competition in the world[i]~[/i][/QUOTE] Not to go a little off-topic but tmysql3_boost has no (working) Linux compile (and hasn't since been compiled July)
[QUOTE=Drakehawke;38204978]I've always connected in InitPostEntity which might be why I haven't noticed this, out of interest where are you connecting? Initialize or directly when the file is included?[/QUOTE] I usually connect in the gamemodes Initialize. I have a specific function for doing so that I call from my own base gamemode.
[QUOTE=Teddi Orange;38206528]I usually connect in the gamemodes Initialize. I have a specific function for doing so that I call from my own base gamemode.[/QUOTE] So if I connect in Initalize and then try and run a query in InitPostEntity it should replicate your problem?
[QUOTE=Drakehawke;38206988]So if I connect in Initalize and then try and run a query in InitPostEntity it should replicate your problem?[/QUOTE] This doesn't occur with all queries, just certain ones. But theoretically yes
[QUOTE=Teddi Orange;38207904]This doesn't occur with all queries, just certain ones. But theoretically yes[/QUOTE] Anything different about the ones that don't work?
The only thing that makes this query unique is it's a JOIN query (ie selects from two tables instead of one). The rest looking at them seem to all work as intended so far, but I'll get back to you if I find any others that don't.
[QUOTE=Banana Lord.;38206168]Not to go a little off-topic but tmysql3_boost has no (working) Linux compile (and hasn't since been compiled July)[/QUOTE] Don't use tmysql boost. [url]http://blackawps-glua-modules.googlecode.com/svn/trunk/gm_tmysql4/Release/[/url] [editline]27th October 2012[/editline] Sorry about going offtopic, banana pm me if you need any help
[QUOTE=Map in a box;38210219]Don't use tmysql boost. [url]http://blackawps-glua-modules.googlecode.com/svn/trunk/gm_tmysql4/Release/[/url] [editline]27th October 2012[/editline] Sorry about going offtopic, banana pm me if you need any help[/QUOTE] There's no non-boost version for Windows, that's why Azui uses boost, it makes cross-platform stuff easier.
That version there is for windows. [editline]27th October 2012[/editline] The linux one is [url]http://blackawps-glua-modules.googlecode.com/svn/trunk/gm_tmysql4_boost/Release/[/url]
MySQLOO has always been superior to tmysql, not the least of reasons is because of multiple connections and the fact that it only just broke recently (v7 was around a long time while tmysql kept breaking at engine updates). Boost doesn't make cross-platform easier because it's a pain in the arse to try to hunt down the exact version of boost azu happened to compile with to be able to compile the threading library that's required of it.
Sorry, you need to Log In to post a reply to this thread.