-
-
Save BlackCetha/f5eb75563ba0b7124b6bd9684f53a573 to your computer and use it in GitHub Desktop.
DBIntegration
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- /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() |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- /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