Skip to content

Instantly share code, notes, and snippets.

@BlackCetha
Created April 1, 2017 13:15
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save BlackCetha/f5eb75563ba0b7124b6bd9684f53a573 to your computer and use it in GitHub Desktop.
Save BlackCetha/f5eb75563ba0b7124b6bd9684f53a573 to your computer and use it in GitHub Desktop.
DBIntegration
-- /lua/autorun/server/dbbootstrap.lua
require "mysqloo"
local connectionData = {
host = "",
user = "",
password = "",
database = ""
}
db = mysqloo.connect(
connectionData.host,
connectionData.user,
connectionData.password,
connectionData.database
)
db.APIKey = ""
db.LogError = function ( ... )
local args = { ... }
local msg = ""
for _, arg in pairs( args ) do
msg = msg .. tostring( arg ) .. " "
end
msg = string.TrimRight( msg )
print( "[DB Error] (" .. os.date( "%H:%M:%S" ) .. ") " .. msg )
file.Append( "dberrors.log", "[" .. os.date( "%d.%m.%Y-%H:%M:%S" ) .. "] " .. msg .. "\n" )
if not db or not db:ping() or not db.ServerID then
print "Could not write error to DB: Not connected"
return
end
local logquery = db:prepare( [[
INSERT INTO errors (server_id, text) VALUES (?, ?)
]] )
logquery:setNumber( 1, db.ServerID )
logquery:setString( 2, msg )
function logquery:onError ( errstring )
print("Could not write error to DB: DB error\n" .. errstring)
end
logquery:start()
end
local function loadDBModules ()
local svIntegrations = file.Find( "dbintegration/server/*.lua", "LUA" )
for _, integration in pairs( svIntegrations ) do
include( "dbintegration/server/" .. integration )
print( "[DB] Loaded serverside integration " .. integration )
end
local clIntegrations = file.Find( "dbintegration/client/*.lua", "LUA" )
for _, integration in pairs( clIntegrations ) do
AddCSLuaFile( "dbintegration/client/" .. integration )
print( "[DB] Loaded clientsde integration " .. integration )
end
end
local function getServerID ( callback )
local query = db:prepare( [[
SELECT
server_id,
alias
FROM servers
WHERE
address = ? AND
port = ?
]] )
query:setNumber( 1, GetConVar( "hostip" ):GetInt() )
query:setNumber( 2, GetConVar( "hostport" ):GetInt() )
function query:onSuccess ( dbdata )
if #dbdata == 1 then
callback( dbdata[1].server_id, dbdata[1].alias or "" )
elseif #dbdata == 0 then
callback()
else
db.LogError( "getServerID", "Received ", #dbdata, "rows for server, aborting" )
end
end
function query:onError ( errstring )
db.LogError( "getServerID", errstring )
end
query:start()
end
function createServerEntry ()
local query = db:prepare( [[
INSERT INTO servers (
address,
port
) VALUES (?, ?)
]] )
query:setNumber( 1, GetConVar( "hostip" ):GetInt() )
query:setNumber( 2, GetConVar( "hostport" ):GetInt() )
function query:onSuccess ( data )
db.ServerID = self:lastInsert()
db.ServerAlias = "<No Alias>"
print( "Registered with database as server #" .. db.ServerID )
loadDBModules()
end
function createQuery:onError ( errstring )
db.LogError( "createServer", errstring )
end
createQuery:start()
createQuery:wait( true )
end
function db:onConnected ()
getServerID( function ( id, alias )
if not id then
return createServerEntry()
end
db.ServerID = id
db.ServerAlias = alias or "<No Alias>"
print( "Connected to database as server #" .. db.ServerID .. " '" .. db.ServerAlias .. "'" )
loadDBModules()
end )
end
function db:onConnectionFailed ( errstring )
db.LogError( "Connection failed", errstring )
end
print "[DB] Connecting"
db:connect()
db:wait()
-- /lua/dbintegration/server/ulib_ucl.lua
ULib.ucl = {} -- REWRITE EVERYTHING!
local ucl = ULib.ucl
ucl.defaultColor = Color( 255, 255, 255 )
--[[
ucl.groups = {
group_id = {
name = "group_name"
color = Color( X, X, X ),
displayname = "GroupName :)"
}
}
ucl.users = {
steamid64 = {
group = group_id
}
}
]]--
local queries = {
fetchGroups = [[
SELECT
name,
displayname,
color
FROM
groups
]],
groupExists = [[
SELECT
group_id
FROM
groups
WHERE
name = ?
]],
createGroup = [[
INSERT INTO groups (
name,
displayname,
color
) VALUES (
?,
?,
?
)
]],
groupAllow = [[
INSERT INTO group_permission (
group_id,
permission_id
) VALUES (
(SELECT group_id FROM groups WHERE name = ?),
(SELECT permission_id FROM permissions WHERE identifier = ?)
)
]],
groupDeny = [[
DELETE FROM
group_permission
WHERE
group_id = (SELECT group_id FROM groups WHERE name = ?),
permission_id = (SELECT permission_id FROM permission WHERE identifier = ?)
]],
renameGroup = [[
UPDATE
groups
SET
name = ?,
WHERE
name = ?
]],
groupInherits = [[
SELECT
t.name,
t.color,
t.displayname
FROM
groups g
JOIN
group_inheritance i
ON
i.query_group = g.group_id
JOIN
groups t
ON
t.group_id = i.inherited_group
WHERE
g.name = ?
]],
setGroupInheritance = [[
INSERT INTO group_inheritance (
query_group,
inherited_group
)
SELECT
(SELECT group_id FROM groups WHERE name = ?),
inherited_group
FROM
group_inheritance
WHERE
query_group = (SELECT group_id FROM groups WHERE name = ?)
]],
setGroupSelfInheritance = [[
INSERT INTO group_inheritance (
query_group,
inherited_group
) VALUES (
(SELECT group_id FROM groups WHERE name = ?),
(SELECT group_id FROM groups WHERE name = ?)
)
]],
removeGroupInheritance = [[
DELETE FROM
group_inheritance
WHERE
query_group = (SELECT group_id FROM groups WHERE name = ?)
]]
}
local function ColorToInt ( colortable )
if not colortable then return nil end
local cint = colortable.r
cint = bit.lshift( cint, 8 ) + colortable.g
cint = bit.lshift( cint, 8 ) + colortable.b
return cint
end
local function IntToColor ( cint )
if not cint then return nil end
local r = bit.band( bit.rshift( cint, 16 ), 255 )
local g = bit.band( bit.rshift( cint, 8 ), 255 )
local b = bit.band( cint, 255 )
return Color( r, g, b )
end
ucl.fetchGroups = function ( callback ) -- This is only for displaying purposes. Actual data is never cached.
local fetchGroups = db:prepare( queries.fetchGroups )
function fetchGroups:onSuccess ( dbdata )
ucl.groups = {}
for _, row in pairs( dbdata ) do
ucl.groups[ #ucl.groups + 1 ] = {
name = row.name,
color = IntToColor( row.color ) or ucl.defaultColor,
displayname = row.displayname or row.name
}
end
if callback then callback() end
end
function fetchGroups:onError ( errstring )
db.LogError( "ULib.ucl.fetchGroups", errstring )
end
fetchGroups:start()
end
ucl.fetchGroups()
ucl.groupExists = function ( name, callback )
ULib.checkArg( 1, "ULib.ucl.groupExists", "string", name )
local groupExists = db:prepare( queries.groupExists )
groupExists:setString( 1, name )
function groupExists:onSuccess ( dbdata )
callback( #dbdata ~= 0 )
end
function groupExists:onError ( errstring )
db.LogError( "ULib.ucl.groupExists", name, errstring )
end
groupExists:start()
end
ucl.addGroup = function ( name, allows, inherit_from, _, displayname, color )
ULib.checkArg( 1, "ULib.ucl.addGroup", "string", name )
ULib.checkArg( 2, "ULib.ucl.addGroup", { "nil", "table" }, allows )
ULib.checkArg( 3, "ULib.ucl.addGroup", { "nil", "string" }, inherit_from )
allows = allows or {}
inherit_from = inherit_from or "user"
if inherit_from == name then return error( "Group cannot inherit from itself", 2 ) end
ucl.groupExists( name, function ( exists )
if exists == true then
return print( "Group already exists, cannot add again (" .. name .. ")" )
end
ucl.groupExists( inherit_from, function ( exists )
if exists == false then
return print( "Invalid group for inheritance (" .. tostring( inherit_from ) .. ")" )
end
local createGroup = db:prepare( queries.createGroup )
createGroup:setString( 1, name )
if displayname then
createGroup:setString( 2, displayname )
else
createGroup:setNull( 2 )
end
if color then
createGroup:setNumber( 3, ColorToInt( color ) )
else
createGroup:setNull( 3 )
end
function createGroup:onSuccess ()
ucl.groupAllow( name, allows )
ucl.setGroupInheritance( name, inherit_from )
ucl.fetchGroups( function ()
hook.Call( ULib.HOOK_UCLCHANGED )
end )
end
function createGroup:onError ( errstring )
db.LogError( "ULib.ucl.addGroup", name, inherit_from, displayname, tostring( color ), errstring )
end
createGroup:start()
end )
end )
end
ucl.groupAllow = function ( name, access, revoke, multicall )
ULib.checkArg( 1, "ULib.ucl.groupAllow", "string", name )
ULib.checkArg( 2, "ULib.ucl.groupAllow", { "string", "table" }, access )
ULib.checkArg( 3, "ULib.ucl.groupAllow", { "nil", "boolean" }, revoke )
ucl.groupExists( name, function ( exists )
if exists == false then
return print( "Group does not exist for changing access (" .. name .. ")" )
end
if type( access ) == "table" then
for _, entry in pairs( access ) do
ucl.groupAllow( name, entry, false, true )
end
hook.Call( ULib.HOOK_UCLCHANGED )
end
local accesstag = access:lower()
if not revoke or revoke == false then
local groupAllow = db:prepare( queries.groupAllow )
groupAllow:setString( 1, name )
groupAllow:setString( 2, accesstag )
function groupAllow:onSuccess ()
if not multicall then
hook.Call( ULib.HOOK_UCLCHANGED )
end
end
function groupAllow:onError ( errstring )
db.LogError( "ULib.ucl.groupAllow", name, accesstag, errstring )
end
groupAllow:start()
else
local groupDeny = db:prepare( queries.groupDeny )
groupDeny:setString( 1, name )
groupDeny:setString( 2, accesstag )
function groupDeny:onSuccess ()
if not multicall then
hook.Call( ULib.HOOK_UCLCHANGED )
end
end
function groupDeny:onError ( errstring )
db.LogError( "ULib.ucl.groupDeny", name, accesstag, errstring )
end
groupDeny:start()
end
end )
end
ucl.renameGroup = function ( old, new )
ULib.checkArg( 1, "ULib.ucl.renameGroup", "string", old )
ULib.checkArg( 2, "ULib.ucl.renameGroup", "string", new )
local renameGroup = db:prepare( queries.renameGroup )
renameGroup:setString( 1, new )
renameGroup:setString( 2, old )
function renameGroup:onSuccess ()
ucl.fetchGroups( function ()
hook.Call( ULib.HOOK_UCLCHANGED )
end )
end
function renameGroup:onError ( errstring )
db.LogError( "ULib.ucl.renameGroup", old, new, errstring )
end
end
local function removeGroupInheritance ( group, callback )
local removeGroupInheritance = db:prepare( queries.removeGroupInheritance )
removeGroupInheritance:setString( 1, group )
function removeGroupInheritance:onSuccess ()
callback()
end
function removeGroupInheritance:onError ( errstring )
db.LogError( "removeGroupInheritance", group, errstring )
end
removeGroupInheritance:start()
end
local function writeGroupInheritance ( group, inherit, callback )
local transaction = db:createTransaction()
local setGroupInheritance = db:prepare( queries.setGroupInheritance )
setGroupInheritance:setString( 1, group )
setGroupInheritance:setString( 2, inherit )
setGroupSelfInheritance:setString( 1, group )
setGroupSelfInheritance:setString( 2, group )
transaction:addQuery( setGroupInheritance )
transaction:addQuery( setGroupSelfInheritance )
function transaction:onSuccess ()
callback()
end
function transaction:onError ( errstring )
db.LogError( "writeGroupInheritance", group, inherit, errstring )
end
transaction:start()
end
ucl.setGroupInheritance = function ( group, inherit_from )
ULib.checkArg( 1, "ULib.ucl.renameGroup", "string", group )
ULib.checkArg( 2, "ULib.ucl.renameGroup", { "nil", "string" }, inherit_from )
inherit_from = inherit_from or "user"
ucl.groupExists( group, function ( exists )
if exists == false then
return print( "Group does not exist (" .. group .. ")" )
end
ucl.groupExists( inherit_from, function ( exists )
if exists == false then
return print( "Group for inheritance does not exist: " .. tostring( inherit_from ) )
end
removeGroupInheritance( group, function ()
writeGroupInheritance( group, inherit_from, function ()
hook.Call( ULib.HOOK_UCLCHANGED )
end )
end )
end )
end )
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment