Skip to content

Instantly share code, notes, and snippets.

@Srlion

Srlion/async_sql_wrapper.lua

Last active January 12, 2019 17:20
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Srlion/11b5d48553fae9262cfc77b673e2d88e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Srlion/11b5d48553fae9262cfc77b673e2d88e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
async_sql_wrapper.lua
--[[
mysql - 1.0.2
A simple MySQL wrapper for Garry's Mod.
Alexander Grist-Hucker
http://www.alexgrist.com
https://github.com/alexgrist/GLua-MySQL-Wrapper/blob/master/mysql.lua
--]]
--[[
local async = include("async.lua")
local SQL = include("async_sql_wrapper.lua")
SQL:SetAddon("ADDON NAME")
SQL:SetAsync(async) -- https://github.com/Srlion/sr-stuff/blob/master/lua/async.lua
SQL:SetConfig({
Host = "localhost",
Username = "Username",
Password = "Password",
Database = "Database",
MySQL = false
})
SQL:Connect()
hook.Add(SQL:GetAddon() .. ".DatabaseConnected", "TEST", function()
print("CONNECTED!")
end)
hook.Add(SQL:GetAddon() .. ".DatabaseConnectionFailed", "TEST", function(err)
print(err)
end)
-- putting true inside Start() makes it use async promises, if you want to use callbacks then leave Start() empty and use QUERY:Callback(function(data) end)
async(function()
local status, data
SQL:Drop("people"):Start(true)
status, data = SQL:Create("people")
:Create("steamid64", "BIGINT UNSIGNED PRIMARY KEY NOT NULL")
:Create("name", "VARCHAR(255) NOT NULL")
:Create("rank", "VARCHAR(30) NOT NULL")
:Start(true)
if (status == false) then
print(data) -- data will be the error text here
return
end
-- OR
-- local query = SQL:Create("people")
-- query:Create("steamid64", "BIGINT UNSIGNED PRIMARY KEY NOT NULL")
-- query:Create("name", "VARCHAR(255) NOT NULL")
-- query:Create("rank", "VARCHAR(30) NOT NULL")
-- status, data = query:Start(true)
status, data = SQL:Insert("people")
:Insert("steamid64", "76561198261855442")
:Insert("name", "Srlion")
:Insert("rank", "superadmin")
:Start(true)
if (!status) then print(data) return end
status, data = SQL:Select("people")
:Where("steamid64", "76561198261855442")
:Cast("steamid64", "CHAR") -- we have to cast steamid64 as CHAR so it returns correctly
:Start(true)
if (!status) then print(data) return end
data = data[1]
if (!data) then return print("NO DATA") end
PrintTable(data)
end)()
]]
local SQL = SQL || {}
local config = {}
local isnumber = isnumber
local isstring = isstring
local istable = istable
local table_concat = table.concat
local function table_insert(t, v, i)
t[i || #t+1] = v
end
local QUERY_CLASS = {}
QUERY_CLASS.__index = QUERY_CLASS
function QUERY_CLASS:New(tableName, queryType)
local newObject = setmetatable({}, QUERY_CLASS)
newObject.queryType = queryType
newObject.tableName = tableName
newObject.selectList = {}
newObject.castList = {}
newObject.insertList = {}
newObject.insertvalues = {}
newObject.updateList = {}
newObject.createList = {}
newObject.whereList = {}
newObject.orderByList = {}
return newObject
end
function QUERY_CLASS:Escape(text)
return SQL:Escape(text)
end
function QUERY_CLASS:ForTable(tableName)
self.tableName = tableName
end
function QUERY_CLASS:Where(key, value)
self:WhereEqual(key, value)
end
function QUERY_CLASS:WhereEqual(key, value)
table_insert(self.whereList, "`" .. key .. "` = " .. self:Escape(value))
end
function QUERY_CLASS:WhereNotEqual(key, value)
table_insert(self.whereList, "`" .. key .. "` != " .. self:Escape(value))
end
function QUERY_CLASS:WhereLike(key, value)
table_insert(self.whereList, "`" .. key .. "` LIKE " .. self:Escape(value))
end
function QUERY_CLASS:WhereNotLike(key, value)
table_insert(self.whereList, "`" .. key .. "` NOT LIKE " .. self:Escape(value))
end
function QUERY_CLASS:WhereGT(key, value)
table_insert(self.whereList, "`" .. key .. "` > " .. self:Escape(value))
end
function QUERY_CLASS:WhereLT(key, value)
table_insert(self.whereList, "`" .. key .. "` < " .. self:Escape(value))
end
function QUERY_CLASS:WhereGTE(key, value)
table_insert(self.whereList, "`" .. key .. "` >= " .. self:Escape(value))
end
function QUERY_CLASS:WhereLTE(key, value)
table_insert(self.whereList, "`" .. key .. "` <= " .. self:Escape(value))
end
function QUERY_CLASS:OrderByDesc(key)
table_insert(self.orderByList, "`" .. key .. "` DESC")
end
function QUERY_CLASS:OrderByAsc(key)
table_insert(self.orderByList, "`" .. key .. "` ASC")
end
function QUERY_CLASS:Select(fieldName)
table_insert(self.selectList, "`" .. fieldName .. "`")
end
function QUERY_CLASS:Cast(key, value)
key = "`" .. key .. "`"
table_insert(self.castList, "CAST(" .. key .. " AS " .. (value || "CHAR") .. ") AS " .. key)
end
function QUERY_CLASS:Insert(key, value)
table_insert(self.insertList, "`" .. key .. "`")
table_insert(self.insertvalues, self:Escape(value))
end
function QUERY_CLASS:Update(key, value)
table_insert(self.updateList, "`" .. key .. "` = " .. self:Escape(value))
end
function QUERY_CLASS:Create(key, value)
if (!config.MySQL) then
value = value:gsub("AUTOINCREMENT", ""):gsub("AUTO_INCREMENT", "")
end
table_insert(self.createList, "`" .. key .. "` " .. value)
end
function QUERY_CLASS:PrimaryKey(...)
if (self.primaryKeys == nil) then self.primaryKeys = {} end
for _, v in ipairs({...}) do
table_insert(self.primaryKeys, "`" .. v .. "`")
end
end
function QUERY_CLASS:Limit(value)
self.limit = value
end
function QUERY_CLASS:Offset(value)
self.offset = value
end
function QUERY_CLASS:Callback(cb)
self.callback = cb
end
local function BuildTableName(queryObj, queryString)
if (isstring(queryObj.tableName)) then
return queryString .. " `" .. queryObj.tableName .. "`"
else
error(SQL:GetAddon(true) .. " No table name specified!")
end
end
local function BuildWhereList(queryObj, queryString)
local whereList = queryObj.whereList
if (#whereList > 0) then
return queryString .. " WHERE " .. table_concat(whereList, " AND ")
end
return queryString
end
local BUILDS = {}
function BUILDS.SELECT(queryObj)
local queryString = {"SELECT "}
local selectList = queryObj.selectList
if (#selectList == 0) then
table_insert(queryString, "*", 2)
else
table_insert(queryString, table_concat(selectList, ", "), 2)
end
local castList = queryObj.castList
if (#castList > 0) then
table_insert(queryString, ", " .. table_concat(castList, ", "), 3)
end
local tableName = queryObj.tableName
if (isstring(tableName)) then
table_insert(queryString, " FROM `" .. tableName .. "`")
else
error(SQL:GetAddon(true) .. " No table name specified!")
end
local whereList = queryObj.whereList
if (#whereList > 0) then
table_insert(queryString, " WHERE " .. table_concat(whereList, " AND "))
end
local orderByList = queryObj.orderByList
if (#orderByList > 0) then
table_insert(queryString, " ORDER BY " .. table_concat(orderByList, ", "))
end
local limit = queryObj.limit
if (isnumber(limit)) then
table_insert(queryString, " LIMIT " .. limit)
end
local offset = queryObj.offset
if (isnumber(offset)) then
table_insert(queryString, " OFFSET " .. offset)
end
return table_concat(queryString)
end
function BUILDS.INSERT(queryObj)
return BuildTableName(queryObj, "INSERT INTO") .. " (" .. table_concat(queryObj.insertList, ", ") .. ") VALUES (" .. table_concat(queryObj.insertvalues, ", ") .. ")"
end
function BUILDS.UPDATE(queryObj)
return BuildWhereList(queryObj, BuildTableName(queryObj, "UPDATE") .. " SET " .. table_concat(queryObj.updateList, ", "))
end
function BUILDS.DELETE(queryObj)
return BuildWhereList(queryObj, BuildTableName(queryObj, "DELETE FROM"))
end
function BUILDS.DROP(queryObj)
return BuildTableName(queryObj, "DROP TABLE")
end
function BUILDS.TRUNCATE(queryObj)
return BuildTableName(queryObj, "TRUNCATE TABLE")
end
function BUILDS.CREATE(queryObj)
local queryString = BuildTableName(queryObj, "CREATE TABLE IF NOT EXISTS")
queryString = queryString .. " (" .. table_concat(queryObj.createList, ", ")
local primaryKeys = queryObj.primaryKeys
if (primaryKeys != nil) then
queryString = queryString .. ", PRIMARY KEY (" .. table_concat(primaryKeys, ", ") .. ")"
end
return queryString .. ")"
end
function QUERY_CLASS:Start(_async)
if (_async) then
return SQL:AsyncQuery(BUILDS[self.queryType:upper()](self))
end
SQL:Query(BUILDS[self.queryType:upper()](self), self.callback)
end
for k, v in pairs(BUILDS) do
SQL[k:lower():gsub("^%l", string.upper)] = function(_, tableName)
return QUERY_CLASS:New(tableName, k)
end
end
local blackListed = {"New", "Escape", "Start"}
for k, v in pairs(QUERY_CLASS) do
local old = QUERY_CLASS[k]
if (isfunction(old) && !table.HasValue(blackListed, k)) then
QUERY_CLASS[k] = function(s, ...)
old(s, ...)
return s
end
end
end
local queued = {}
function SQL:Connect()
if (!config.MySQL) then
self:OnConnected()
return true
end
if (self.Database) then
local status = self.Database:status()
if (status == mysqloo.DATABASE_CONNECTING || status == DATABASE_CONNECTED) then
return true
end
end
SQL:SetConnected(false)
if (!istable(mysqloo)) then
require("mysqloo")
end
if (!mysqloo) then
error(("%s mysqloo module doesn't exist, get it from https://github.com/FredyH/MySQLOO"):fomat(SQL:GetAddon(true)))
return false
end
do
for _, v in ipairs({"Host", "Username", "Password", "Database"}) do
if (!isstring(config[v])) then
local msg = "%s SQL config value '%s' is invalid"
error(msg:format(SQL:GetAddon(true), v))
return false
end
end
end
self.Database = mysqloo.connect(config.Host, config.Username, config.Password, config.Database, tonumber(config.Port) || 3306)
self.Database.onConnected = function()
self:SetConnected(true)
for i = 1, #queued do
queued[i]()
queued[i] = nil
end
self:OnConnected()
end
self.Database.onConnectionFailed = function(_, err)
ErrorNoHalt(("%s failed to connect to the server: %s\n"):format(SQL:GetAddon(true), err))
self:OnConnectionFailed(err)
end
self.Database:setMultiStatements(false)
self.Database:connect()
return true
end
function SQL:Query(query, cb)
if (!config.MySQL) then
query = sql.Query(query)
if (query == false) then
ErrorNoHalt(("%s SQL query error: %s\n"):format(SQL:GetAddon(true), sql.LastError()))
elseif (cb) then
cb(query || {})
end
return
end
if (!self:IsConnected()) then
table_insert(queued, function()
self:Query(query, cb)
end)
self:Connect()
return
end
local queryTxt = query
query = self.Database:query(query)
if (cb) then
query.onSuccess = function(_, data)
cb(data)
end
end
query.onError = function(_, err) -- https://github.com/Kamshak/LibK/blob/master/lua/libk/server/sv_libk_database.lua#L129
local status = self.Database:status()
if (status == mysqloo.DATABASE_NOT_CONNECTED || status == mysqloo.DATABASE_CONNECTING || err:find("Lost connection to MySQL server during query")) then
table_insert(queued, function()
self:Query(queryTxt, cb)
end)
self:Connect()
return
end
ErrorNoHalt(("%s SQL query error: %s\n"):format(SQL:GetAddon(true), err))
end
query:start()
end
do
local async
function SQL:AsyncQuery(query)
return async.Promise(function(res)
self:Query(query, res)
end)
end
function SQL:SetAsync(_async)
async = _async
end
end
do
local connected = false
function SQL:IsConnected()
return connected
end
function SQL:SetConnected(v)
if (v == true) then
connected = true
else
connected = false
end
end
end
do
local escapeString = function(v, db)
if (config.MySQL) then
return "'" .. db:escape(v) .. "'"
else
return sql.SQLStr(v)
end
end
local type = type
function SQL:Escape(v)
if (v == nil) then return "NULL" end
local _type = type(v)
if (_type == "number") then return v end
if (_type == "boolean") then return v && "TRUE" || "false" end
if (_type == "string") then return escapeString(v, self.Database) end
error("Unknown type was passed to SQL:Escape: " .. _type)
end
end
function SQL:OnConnected()
hook.Run(SQL:GetAddon() .. ".DatabaseConnected")
end
function SQL:OnConnectionFailed(errorText)
hook.Run(SQL:GetAddon() .. ".DatabaseConnectionFailed", errorText)
end
function SQL:SetConfig(_config)
config = _config
if (isbool(config.MySQL) == false) then
config.MySQL = false
end
end
do
local addonName = "(SQL) NO NAME"
function SQL:SetAddon(name)
addonName = name
end
function SQL:GetAddon(inParen)
if (inParen == true) then
return "(" .. addonName .. ")"
end
return addonName
end
end
return SQL
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment