gmsv_mysqloo - Updated OO MySQL Module (multiple statements/stored procs!)

[c👌de_g🎷]

[QUOTE=Unknown Gamer;48900952]I think people still use MySQLOO is because MySQLOO is really the only compatible module with most MySQL addons.

I'm no expert at modules or MySQL so correct me if im wrong :P[/QUOTE]

Then convert them; almost all of the functions have similar usage.

[MyDimeIsUp]

[QUOTE=code_gs;48901176]Then convert them; almost all of the functions have similar usage.[/QUOTE]

Would you give a list of what to change from. It'd be helpful for the people who want to convert.

Like this. Ex:
MySQLOO
function DB:Connect

tmysqloo
function DB:Query

(Know that im horrible at MySQL)

[BlackAwps]

Fuck it, I'm making a wrapper for tmysql so you could just drop it in as a replacement for mysqloo.

[Blasteh]

[QUOTE=BlackAwps;48901946]Fuck it, I'm making a wrapper for tmysql so you could just drop it in as a replacement for mysqloo.[/QUOTE]

How about adding a callback for when it is connected as well? That's the only thing missing that's stopped me from switching pointshop over, I've got everything else on tmysql4 already.

[BlackAwps]

Don't worry, I have that covered.

[lua]-- TO INSTALL: SAVE AS lua/includes/modules/mysqloo.lua and have tmysql4 install in lua/bin

require("tmysql4")

mysqloo = {
VERSION = "8.2 tmysql4",
MYSQL_VERSION = MYSQL_VERSION,
MYSQL_INFO = MYSQL_INFO,

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,

OPTION_NUMERIC_FIELDS = 1,
OPTION_NAMED_FIELDS = 2,
OPTION_INTERPRET_DATA = 4,
OPTION_CACHE = 8,

CLIENT_MULTI_STATEMENTS = CLIENT_MULTI_STATEMENTS,
CLIENT_MULTI_RESULTS = CLIENT_MULTI_RESULTS,
CLIENT_INTERACTIVE = CLIENT_INTERACTIVE,
}

local gofuckurself = {}
gofuckurself.__index = gofuckurself

local seriouslyeatadick = {}
seriouslyeatadick.__index = seriouslyeatadick

function mysqloo.connect(host, username, password, database, port, sockt, flags)
return setmetatable({
host = host,
username = username,
password = password,
database = database,
port = port,
socket = socket,
flags = flags,
connected = false,
},gofuckurself)
end

function gofuckurself:connect()
self.db, err = tmysql.initialize(self.host, self.username, self.password, self.database, self.port, self.socket, self.flags)

if not self.db then
self:onConnectionFailed(err)
return
end

self.connected = true
self:onConnected()
end

function gofuckurself:onConnected()
-- Blank function that gets overwritten
end

function gofuckurself:onConnectionFailed(err)
-- Blank function that gets overwritten
end

function gofuckurself:query(query)
return setmetatable({
db = self.db,
query = query,
running = false,
complete = false,
error = "",
lastid = 0,
affected = 0,
data = {},
abort = false,
option = mysqloo.OPTION_NAMED_FIELDS,
},seriouslyeatadick)
end

function gofuckurself:setCharset(str)
self.db:SetCharacterSet(str) -- Look at me, adding fuctionality to mysqloo WHAT A NICE GUY I AM
end

function gofuckurself:escape(str)
return self.db:Escape(str)
end

function gofuckurself:abortAllQueries()
-- rofl go fuck urself
end

function gofuckurself:status()
if self.connected then
return mysqloo.DATABASE_CONNECTED
else
return mysqloo.DATABASE_NOT_CONNECTED
end
-- ya fuk u im not supporting the other bullshit
end

function gofuckurself:wait()
-- ya seriously, if you think im going to support this you can FUCK RIGHT OFF
end

function gofuckurself:serverVersion()
return 1234 -- Who cares
end

function gofuckurself:serverInfo()
return MYSQL_VERSION -- Who cares
end

function gofuckurself:hostInfo()
return MYSQL_INFO -- Who cares
end

function seriouslyeatadick:start()
self.running = true
self.db:Query(self.query, function(results)
if self.abort then return end -- yeah, deal with it

self.doingdata = true

results = results[1] -- xd skip all other data sets BECAUSE WHY NOT
self.error = results.error
self.lastid = results.lastid
self.affected = results.affected
self.data = results.data
if results.status then
self:onSuccess(self.data)
for k,v in pairs(self.data) do
self:onData({k=v}) -- ???
end
else
self:onError(self.error, self.query)
end

self.doingdata = false
self.complete = true
end, nil, (self.option == mysqloo.OPTION_NUMERIC_FIELDS))
end

function seriouslyeatadick:onSuccess(data)
-- Blank function that gets overwritten
end

function seriouslyeatadick:onData(data)
-- Blank function that gets overwritten
end

function seriouslyeatadick:onError(err)
-- Blank function that gets overwritten
end

function seriouslyeatadick:isRunning()
return self.running
end

function seriouslyeatadick:getData()
return self.data
end

function seriouslyeatadick:abort()
self.abort = true
end

function seriouslyeatadick:lastInsert()
return self.lastid
end

function seriouslyeatadick:affectedRows()
return self.affected
end

function seriouslyeatadick:status() -- OH BOY
if self.doingdata then
return mysqloo.QUERY_READING_DATA
elseif self.abort then
return mysqloo.QUERY_ABORTED
elseif self.complete then
return mysqloo.QUERY_COMPLETE
elseif self.running then
return mysqloo.QUERY_RUNNING
else
return mysqloo.QUERY_NOT_RUNNING
end
end

function seriouslyeatadick:setOption(option)
self.option = option
end

function seriouslyeatadick:wait()
-- Yeah no
end

function seriouslyeatadick:error()
return self.error
end[/lua]

[del]It's going to need an updated tmysql4, so until I make a new build the mysqloo.OPTION_NUMERIC_FIELDS won't work just yet.[/del]

Updated tmysql4 to support results with numbers as keys instead of field-named strings.

[Jocken]

Would you guys say that tmysql4 is vastly superior to mysqloo?

I have yet to really see any speed tests to determine which module is faster.

[c👌de_g🎷]

[QUOTE=Jocken300;48903328]Would you guys say that tmysql4 is vastly superior to mysqloo?

I have yet to really see any speed tests to determine which module is faster.[/QUOTE]

Absolutely. tmysql has much better garbage collection and isn't plagued with memory leaks from simply sending in queries or aborting a connection. It also, as recently mentioned, allows setting column/database charset.

[Map in a box]

i am reentering the gmod scene.
MYSQLOO SUCKS OH GOD WHY DO PEOPLE USE IT *distant wailing*
i am now leaving the gmod scene again.

[MyDimeIsUp]

[QUOTE=BlackAwps;48902069]Don't worry, I have that covered.

[lua]function gofuckurself:connect()
self.db, err = tmysql.initialize(self.host, self.username, self.password, self.database, self.port, self.socket, self.flags)

if not self.db then
self.onConnectionFailed(self,err)
return
end

self.connected = true
self.onConnected(self)
end[/lua]

[editline]14th October 2015[/editline]

Anyone mind testing this for me?

[lua]require("tmysql4")

mysqloo = {
VERSION = "8.2 tmysql4",
MYSQL_VERSION = MYSQL_VERSION,
MYSQL_INFO = MYSQL_INFO,

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,

OPTION_NUMERIC_FIELDS = 1,
OPTION_NAMED_FIELDS = 2,
OPTION_INTERPRET_DATA = 4,
OPTION_CACHE = 8,

CLIENT_MULTI_STATEMENTS = CLIENT_MULTI_STATEMENTS,
CLIENT_MULTI_RESULTS = CLIENT_MULTI_RESULTS,
CLIENT_INTERACTIVE = CLIENT_INTERACTIVE,
}

local gofuckurself = {}
gofuckurself.__index = gofuckurself

local seriouslyeatadick = {}
seriouslyeatadick.__index = seriouslyeatadick

function mysqloo.connect(host, username, password, database, port, sockt, flags)
return setmetatable({
host = host,
username = username,
password = password,
database = database,
port = port,
socket = socket,
flags = flags,
connected = false,
},gofuckurself)
end

function gofuckurself:connect()
self.db, err = tmysql.initialize(self.host, self.username, self.password, self.database, self.port, self.socket, self.flags)

if not self.db then
self:onConnectionFailed(err)
return
end

self.connected = true
self:onConnected()
end

function gofuckurself:onConnected()
-- Blank function that gets overwritten
end

function gofuckurself:onConnectionFailed(err)
-- Blank function that gets overwritten
end

function gofuckurself:query(query)
return setmetatable({
db = self.db,
query = query,
running = false,
complete = false,
error = "",
lastid = 0,
affected = 0,
data = {},
option = mysqloo.OPTION_NUMERIC_FIELDS,
},seriouslyeatadick)
end

function gofuckurself:escape(str)
return self.db:Escape(str)
end

function gofuckurself:abortAllQueries()
-- rofl go fuck urself
end

function gofuckurself:status()
if self.connected then
return mysqloo.DATABASE_CONNECTED
else
return mysqloo.DATABASE_NOT_CONNECTED
end
-- ya fuk u im not supporting the other bullshit
end

function gofuckurself:wait()
-- ya seriously, if you think im going to support this you can FUCK RIGHT OFF
end

function gofuckurself:serverVersion()
return 1234 -- Who cares
end

function gofuckurself:serverInfo()
return MYSQL_VERSION -- Who cares
end

function gofuckurself:hostInfo()
return MYSQL_INFO -- Who cares
end

function seriouslyeatadick:start()
self.running = true
self.db:Query(self.query, function(results)
if self.abort then return end -- yeah, deal with it

self.doingdata = true

results = results[1] -- xd skip all other data sets BECAUSE WHY NOT
self.error = results.error
self.lastid = results.lastid
self.affected = results.affected
self.data = results.data
if results.status then
self:onSuccess(self.data)
for k,v in pairs(self.data) do
self:onData({k=v}) -- ???
end
else
self:onError(self.error, self.query)
end

self.doingdata = false
self.complete = true
end, nil, (self.option == mysqloo.OPTION_NUMERIC_FIELDS))
end

function seriouslyeatadick:onSuceess(data)
-- Blank function that gets overwritten
end

function seriouslyeatadick:onData(data)
-- Blank function that gets overwritten
end

function seriouslyeatadick:onError(err)
-- Blank function that gets overwritten
end

function seriouslyeatadick:isRunning()
return self.running
end

function seriouslyeatadick:getData()
return self.data
end

function seriouslyeatadick:abort()
self.abort = true
end

function seriouslyeatadick:lastInsert()
return self.lastid
end

function seriouslyeatadick:affectedRows()
return self.affected
end

function seriouslyeatadick:status() -- OH BOY
if self.doingdata then
return mysqloo.QUERY_READING_DATA
elseif self.abort then
return mysqloo.QUERY_ABORTED
elseif self.complete then
return mysqloo.QUERY_COMPLETE
elseif self.running then
return mysqloo.QUERY_RUNNING
else
return mysqloo.QUERY_NOT_RUNNING
end
end

function seriouslyeatadick:setOption(option)
self.option = option
end

function seriouslyeatadick:wait()
-- Yeah no
end

function seriouslyeatadick:error()
return self.error
end[/lua]

It's going to need an updated tmysql4, so until I make a new build the mysqloo.OPTION_NUMERIC_FIELDS won't work just yet.[/QUOTE]

I don't mind testing this. Where do I put this? lua/autorun?

[c👌de_g🎷]

[QUOTE=Unknown Gamer;48907956]I don't mind testing this. Where do I put this? lua/autorun?[/QUOTE]

lua/autorun/server; serverside module.

[Map in a box]

well for one you're not escaping your strings, which is a massive security issue, but mysql also doesn't properly store unicode because mysql is terrible. Use a blob or something similar.

[Willox]

[QUOTE=Map in a box;48910349]well for one you're not escaping your strings, which is a massive security issue, but mysql also doesn't properly store unicode because mysql is terrible. Use a blob or something similar.[/QUOTE]

Use the utf8mb4 'char set'

[BlackAwps]

[QUOTE=code_gs;48907983]lua/autorun/server; serverside module.[/QUOTE]

You would actually want to put this in lua/includes/modules so require("mysqloo") would work as normal and you wouldn't have to touch a single line of any addon that uses it.

[darkjacky]

On popular request I have made a benchmark for both modules.

Here are the results. times are in seconds.

mysqloo:

Querys  25000
Average 0.00087432357579928
Minimum 0.00033150375963942
Maximum 0.0095045841553656

tmysql4:

Querys  25000
Average 0.00022150328762435
Minimum 0.00004837978
Maximum 0.018824848131715

Conclusion:
tmysql4 average time is 0.000652820288 lower than that of mysqloo.
tmysql4 minimum time is 0.00028312397 lower than that of mysqloo.
tmysql4 maximum time is 0.00932026397 higher than that of mysqloo.

The lua I used:
tmysql4:

require("tmysql4")
local db = tmysql.initialize( "127.0.0.1", "root", "root", "test", 3306, nil, CLIENT_MULTI_RESULTS )

local tab = {}
timer.Create( "AverageSqlQuery", 10, 0, function()
	local min, avg, max = 1, 0, 0
	for i = 1, #tab do
		local num = tab[ i ]
		avg = avg + num
		if min > num then
			min = num
		elseif max < num then
			max = num
		end
	end
	print("\n")
	print("Querys", #tab)
	print( "Average", avg / #tab )
	print( "Minimum", min )
	print( "Maximum", max )
end )

hook.Add( "Think", "mysqlBenchmark", function()
	local docheck = true
	local t = SysTime()
	db:Query( "select * from table", function( result )
		if result[ 1 ].status ~= true then
			docheck = false
			table.insert( tab, SysTime() - t )
			return
		end
	end )
	while docheck do
		db:Poll()
	end
end )

mysqloo:

require("mysqloo")
local db = mysqloo.connect( "127.0.0.1", "root", "root", "test", 3306 )
function db:onConnected()
	print( "Database has connected!" )
end
db:connect()

local tab = {}
timer.Create( "AverageSqlQuery", 10, 0, function()
	local min, avg, max = 1, 0, 0
	for i = 1, #tab do
		local num = tab[ i ]
		avg = avg + num
		if min > num then
			min = num
		elseif max < num then
			max = num
		end
	end
	print("\n")
	print("Querys", #tab)
	print( "Average", avg / #tab )
	print( "Minimum", min )
	print( "Maximum", max )
end )

hook.Add( "Think", "mysqlBenchmark", function()
	local t = SysTime()
	local q = db:query( "select * from `table`;" )
	function q:onSuccess( data )
		table.insert( tab, SysTime() - t )
		return
	end
	q:start()
	q:wait()
end )

The table table had 1 row.

Results may vary between connection speeds, cpu speeds etc.

And yes. If I had optimized my lua a bit more tmysql4 results would have been even lower.

Another note: mysqloo is anoying and did not accept the same query as tmysql4 did.

tmysql4 allowed "select * from table"
mysqloo did not. I had to change it to "select * from `table`;"

[BlackAwps]

[QUOTE=darkjacky;48922624]

while docheck do
	db:Poll()
end

[/QUOTE]

You're a genius. I can use that to implement wait() into my mysqloo wrapper!

[darkjacky]

[QUOTE=BlackAwps;48923380]You're a genius. I can use that to implement wait() into my mysqloo wrapper![/QUOTE]

Please don't. If you can't connect to the server this will hang forever. +its horrible.

[Koji6ac9H]

Can you make it support non-ascii chars?

[Pwned231]

What is the alternative for sql.Begin() and sql.Commit() in here?

[andreblue]

[QUOTE=Pwned231;50763918]What is the alternative for sql.Begin() and sql.Commit() in here?[/QUOTE]

Read the wiki page for the functions and they give you a hint:
[url]https://wiki.garrysmod.com/page/sql/Commit[/url]
[url]https://wiki.garrysmod.com/page/sql/Begin[/url]

If you are still not sure:
They are a quick way to run BEGIN and COMMIT SQL Key works
[url]http://dev.mysql.com/doc/refman/5.7/en/begin-end.html[/url]

[Pwned231]

[QUOTE=andreblue;50764990]Read the wiki page for the functions and they give you a hint:
[url]https://wiki.garrysmod.com/page/sql/Commit[/url]
[url]https://wiki.garrysmod.com/page/sql/Begin[/url]

If you are still not sure:
They are a quick way to run BEGIN and COMMIT SQL Key works
[url]http://dev.mysql.com/doc/refman/5.7/en/begin-end.html[/url][/QUOTE]

1st guy that actually helped, thanks alot! :)