def power_give(args): sql = "INSERT OR IGNORE INTO GroupCaps (GId, CId) VALUES ((Select GId FROM Groups WHERE GroupName='%s'), (Select CId FROM Caps WHERE CName='%s'));" % ( args[3], args[2]) es.sql('query', 'group_auth', sql) sql = "UPDATE Caps SET Reviewed=1 WHERE Cid =(Select CId FROM Caps WHERE CName='%s');" % args[ 2] es.sql('query', 'group_auth', sql)
def group_create(args): if isinstance(args[3], str): level = cap_level(args[3]) else: level = int(args[3]) sql = "INSERT OR IGNORE INTO Groups ('GroupName', 'GDefaultLevel') VALUES ('%s', %d);" %(args[2],level) es.sql('query', 'group_auth', sql)
def registerCapability(self, auth_capability, auth_recommendedlevel): if isinstance(auth_recommendedlevel, str): level = cap_level(auth_recommendedlevel) else: level = int(auth_recommendedlevel) sql = "INSERT OR IGNORE INTO Caps (CName, CDefaultLevel) VALUES ('%s', %d);" %(auth_capability, level) es.sql('query', 'group_auth', sql) return
def group_create(args): if isinstance(args[3], str): level = cap_level(args[3]) else: level = int(args[3]) sql = "INSERT OR IGNORE INTO Groups ('GroupName', 'GDefaultLevel') VALUES ('%s', %d);" % ( args[2], level) es.sql('query', 'group_auth', sql)
def load(): global gCommands services.register("auth", group_auth) gCommands = {'group_create':group_create, 'group_delete':group_delete, 'power_create':power_create, 'power_give':power_give, 'power_revoke':power_revoke, 'power_delete':power_delete, 'user_create':user_create, 'user_join':user_join, 'user_leave':user_leave, 'user_delete':user_delete} es.regcmd('gauth', 'examples/auth/group_auth/command_extra', 'Create groups, players, etc, via the group_auth script addon.') es.sql('open','group_auth','|examples/auth/group_auth') test = es.sql('queryvalue', 'group_auth', "SELECT COUNT(name) as num FROM sqlite_master WHERE type='table';") if not int(test): init_db()
def registerCapability(self, auth_capability, auth_recommendedlevel): if isinstance(auth_recommendedlevel, str): level = cap_level(auth_recommendedlevel) else: level = int(auth_recommendedlevel) sql = "INSERT OR IGNORE INTO Caps (CName, CDefaultLevel) VALUES ('%s', %d);" % ( auth_capability, level) es.sql('query', 'group_auth', sql) return
def check_nogroup(self, auth_capability): sql = "SELECT COUNT(vwCapsGroups.GId) as gc FROM vwCapsGroups WHERE (Reviewed AND CName='%s' AND GroupName='IdentifiedPlayers');" %(auth_capability) query = int(es.sql('queryvalue', 'group_auth', sql)) if query: return True else: sql = "SELECT COUNT(GId) as cg from Caps, Groups where Not Reviewed AND CName='%s' AND GroupName='IdentifiedPlayers' AND GDefaultLevel<=CDefaultLevel;" %(auth_capability) query = int(es.sql('queryvalue', 'group_auth', sql)) if query: return True else: return False
def check_id(self, auth_identifier, auth_capability): sql = "SELECT COUNT(vwCombined.UId) as cp FROM vwCombined WHERE (Reviewed AND CName='%s' AND SteamId='%s');" %(auth_capability, auth_identifier) query = int(es.sql('queryvalue', 'group_auth', sql)) if query: return True else: sql = "SELECT COUNT(vwPlayersGroups.GId) as pg FROM vwPlayersGroups WHERE SteamId='%s' AND GId in (SELECT GId from Caps, Groups where Not Reviewed AND CName='%s' AND GDefaultLevel<=CDefaultLevel);" %(auth_identifier, auth_capability) query = int(es.sql('queryvalue', 'group_auth', sql)) if query: return True else: return False
def check_id(self, auth_identifier, auth_capability): sql = "SELECT COUNT(vwCombined.UId) as cp FROM vwCombined WHERE (Reviewed AND CName='%s' AND SteamId='%s');" % ( auth_capability, auth_identifier) query = int(es.sql('queryvalue', 'group_auth', sql)) if query: return True else: sql = "SELECT COUNT(vwPlayersGroups.GId) as pg FROM vwPlayersGroups WHERE SteamId='%s' AND GId in (SELECT GId from Caps, Groups where Not Reviewed AND CName='%s' AND GDefaultLevel<=CDefaultLevel);" % ( auth_identifier, auth_capability) query = int(es.sql('queryvalue', 'group_auth', sql)) if query: return True else: return False
def check_nogroup(self, auth_capability): sql = "SELECT COUNT(vwCapsGroups.GId) as gc FROM vwCapsGroups WHERE (Reviewed AND CName='%s' AND GroupName='IdentifiedPlayers');" % ( auth_capability) query = int(es.sql('queryvalue', 'group_auth', sql)) if query: return True else: sql = "SELECT COUNT(GId) as cg from Caps, Groups where Not Reviewed AND CName='%s' AND GroupName='IdentifiedPlayers' AND GDefaultLevel<=CDefaultLevel;" % ( auth_capability) query = int(es.sql('queryvalue', 'group_auth', sql)) if query: return True else: return False
def check_exists(self, auth_identifier): sql = "SELECT COUNT(UId) as cp FROM Players WHERE SteamId='%s';" %(auth_identifier) query = int(es.sql('queryvalue', 'group_auth', sql)) if query: return True else: return False
def check_exists(self, auth_identifier): sql = "SELECT COUNT(UId) as cp FROM Players WHERE SteamId='%s';" % ( auth_identifier) query = int(es.sql('queryvalue', 'group_auth', sql)) if query: return True else: return False
def load(): global gCommands services.register("auth", group_auth) gCommands = { 'group_create': group_create, 'group_delete': group_delete, 'power_create': power_create, 'power_give': power_give, 'power_revoke': power_revoke, 'power_delete': power_delete, 'user_create': user_create, 'user_join': user_join, 'user_leave': user_leave, 'user_delete': user_delete } es.regcmd('gauth', 'examples/auth/group_auth/command_extra', 'Create groups, players, etc, via the group_auth script addon.') es.sql('open', 'group_auth', '|examples/auth/group_auth') test = es.sql( 'queryvalue', 'group_auth', "SELECT COUNT(name) as num FROM sqlite_master WHERE type='table';") if not int(test): init_db()
def user_leave(args): sql = "DELETE FROM PlayersGroups WHERE GId=(Select GId FROM Groups WHERE GroupName='%s') AND UId=(Select Uid FROM Players WHERE Name='%s');" % ( args[3], args[2]) es.sql('query', 'group_auth', sql)
def user_join(args): sql = "INSERT OR IGNORE INTO PlayersGroups (UId, GId) VALUES ((Select Uid FROM Players WHERE Name='%s'), (Select GId FROM Groups WHERE GroupName='%s'));" % ( args[2], args[3]) es.sql('query', 'group_auth', sql)
def user_create(args): sql = "INSERT OR IGNORE INTO Players (Name, SteamId) VALUES ('%s', '%s');" % ( args[2], args[3]) es.sql('query', 'group_auth', sql)
def power_delete(args): sql = "DELETE FROM GroupCaps WHERE CId in (SELECT CId FROM Caps WHERE CName='%s');DELETE FROM Caps WHERE CName='%s';" % ( args[2], args[2]) es.sql('query', 'group_auth', sql)
def power_revoke(args): sql = "DELETE FROM GroupCaps WHERE GId=(Select GId FROM Groups WHERE GroupName='%s') AND CId=(Select CId FROM Caps WHERE CName='%s');" % ( args[3], args[2]) es.sql('query', 'group_auth', sql)
def user_leave(args): sql = "DELETE FROM PlayersGroups WHERE GId=(Select GId FROM Groups WHERE GroupName='%s') AND UId=(Select Uid FROM Players WHERE Name='%s');" %(args[3],args[2]) es.sql('query', 'group_auth', sql)
def group_delete(args): sql = "DELETE FROM GroupCaps WHERE GId in (SELECT GId FROM Groups WHERE Groups.GroupName='%s');DELETE FROM PlayersGroups WHERE GId in (SELECT GId FROM Groups WHERE Groups.GroupName='%s');DELETE FROM Groups WHERE GroupName='%s';" %(args[2],args[2],args[2]) es.sql('query', 'group_auth', sql)
def power_delete(args): sql = "DELETE FROM GroupCaps WHERE CId in (SELECT CId FROM Caps WHERE CName='%s');DELETE FROM Caps WHERE CName='%s';" %(args[2],args[2]) es.sql('query', 'group_auth', sql)
def unload(): es.sql('close', 'group_auth', '|examples/auth/group_auth') services.unregister("auth")
def sql(argv): if argv[0].lower() == 'queryvalue' and len(argv) >= 4: sv[argv[2]] = es.sql(argv[0], argv[1], argv[3]) else: es.sql(*argv)
def user_create(args): sql = "INSERT OR IGNORE INTO Players (Name, SteamId) VALUES ('%s', '%s');" %(args[2],args[3]) es.sql('query', 'group_auth', sql)
def user_join(args): sql = "INSERT OR IGNORE INTO PlayersGroups (UId, GId) VALUES ((Select Uid FROM Players WHERE Name='%s'), (Select GId FROM Groups WHERE GroupName='%s'));" %(args[2],args[3]) es.sql('query', 'group_auth', sql)
def user_delete(args): sql = "DELETE FROM PlayersGroups WHERE UId in (SELECT Uid FROM Players WHERE Name='%s');DELETE FROM Players WHERE Name='%s';" % ( args[2], args[2]) es.sql('query', 'group_auth', sql)
def power_give(args): sql = "INSERT OR IGNORE INTO GroupCaps (GId, CId) VALUES ((Select GId FROM Groups WHERE GroupName='%s'), (Select CId FROM Caps WHERE CName='%s'));" %(args[3],args[2]) es.sql('query', 'group_auth', sql) sql = "UPDATE Caps SET Reviewed=1 WHERE Cid =(Select CId FROM Caps WHERE CName='%s');" % args[2] es.sql('query', 'group_auth', sql)
def group_delete(args): sql = "DELETE FROM GroupCaps WHERE GId in (SELECT GId FROM Groups WHERE Groups.GroupName='%s');DELETE FROM PlayersGroups WHERE GId in (SELECT GId FROM Groups WHERE Groups.GroupName='%s');DELETE FROM Groups WHERE GroupName='%s';" % ( args[2], args[2], args[2]) es.sql('query', 'group_auth', sql)
def power_revoke(args): sql = "DELETE FROM GroupCaps WHERE GId=(Select GId FROM Groups WHERE GroupName='%s') AND CId=(Select CId FROM Caps WHERE CName='%s');" %(args[3],args[2]) es.sql('query', 'group_auth', sql)
def user_delete(args): sql = "DELETE FROM PlayersGroups WHERE UId in (SELECT Uid FROM Players WHERE Name='%s');DELETE FROM Players WHERE Name='%s';" %(args[2],args[2]) es.sql('query', 'group_auth', sql)
def init_db(): #Used to create the defaults if you delete DB. es.sql('query', 'group_auth', "CREATE TABLE [Caps] ([CId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[CName] VARCHAR(30) UNIQUE NOT NULL,[CDefaultLevel] INTEGER NULL,[Reviewed] BOOLEAN DEFAULT '''0''' NOT NULL);") es.sql('query', 'group_auth', "CREATE TABLE [GroupCaps] ([GId] INTEGER NOT NULL,[CId] INTEGER NOT NULL,PRIMARY KEY(Cid, Gid));") es.sql('query', 'group_auth', "CREATE TABLE [Groups] ([GId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[GroupName] VARCHAR(30) UNIQUE NULL,[GDefaultLevel] INTEGER DEFAULT '''''''256''''''' NULL);") es.sql('query', 'group_auth', "CREATE TABLE [Players] ([Uid] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[Name] VARCHAR(100) UNIQUE NOT NULL,[SteamId] VARCHAR(30) NULL,[Comments] TEXT NULL);") es.sql('query', 'group_auth', "CREATE TABLE [PlayersGroups] ([UId] INTEGER NOT NULL,[GId] INTEGER NOT NULL,PRIMARY KEY(Uid, Gid));") es.sql('query', 'group_auth', "CREATE INDEX [IDX_PLAYERS_STEAMID] ON [Players]([SteamId] DESC,[Name] DESC);") es.sql('query', 'group_auth', "CREATE VIEW [vwCapsGroups] AS SELECT Caps.*, Groups.* FROM Caps, GroupCaps, Groups WHERE Groups.GId=GroupCaps.GId AND GroupCaps.CId=Caps.CId;") es.sql('query', 'group_auth', "CREATE VIEW [vwCombined] AS SELECT Caps.*, Players.*, Groups.* FROM Caps, GroupCaps, Groups, Players, PlayersGroups WHERE Players.Uid=PlayersGroups.Uid AND PlayersGroups.GId=Groups.GId AND Groups.GId=GroupCaps.GId AND GroupCaps.CId=Caps.CId;") es.sql('query', 'group_auth', "CREATE VIEW [vwPlayersGroups] AS SELECT Players.*, Groups.* FROM Groups, Players, PlayersGroups WHERE Players.Uid=PlayersGroups.Uid AND PlayersGroups.GId=Groups.GId;") user_create(['user', 'create', 'UNKNOWN','STEAM_ID_PENDING']) group_create(['group', 'create', 'UnidentifiedPlayers', '#UNRESTRICTED']) user_join(['user', 'join', 'UNKNOWN', 'UnidentifiedPlayers']) group_create(['group', 'create', 'IdentifiedPlayers', '#IDENTIFIED'])
def init_db(): #Used to create the defaults if you delete DB. es.sql( 'query', 'group_auth', "CREATE TABLE [Caps] ([CId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[CName] VARCHAR(30) UNIQUE NOT NULL,[CDefaultLevel] INTEGER NULL,[Reviewed] BOOLEAN DEFAULT '''0''' NOT NULL);" ) es.sql( 'query', 'group_auth', "CREATE TABLE [GroupCaps] ([GId] INTEGER NOT NULL,[CId] INTEGER NOT NULL,PRIMARY KEY(Cid, Gid));" ) es.sql( 'query', 'group_auth', "CREATE TABLE [Groups] ([GId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[GroupName] VARCHAR(30) UNIQUE NULL,[GDefaultLevel] INTEGER DEFAULT '''''''256''''''' NULL);" ) es.sql( 'query', 'group_auth', "CREATE TABLE [Players] ([Uid] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[Name] VARCHAR(100) UNIQUE NOT NULL,[SteamId] VARCHAR(30) NULL,[Comments] TEXT NULL);" ) es.sql( 'query', 'group_auth', "CREATE TABLE [PlayersGroups] ([UId] INTEGER NOT NULL,[GId] INTEGER NOT NULL,PRIMARY KEY(Uid, Gid));" ) es.sql( 'query', 'group_auth', "CREATE INDEX [IDX_PLAYERS_STEAMID] ON [Players]([SteamId] DESC,[Name] DESC);" ) es.sql( 'query', 'group_auth', "CREATE VIEW [vwCapsGroups] AS SELECT Caps.*, Groups.* FROM Caps, GroupCaps, Groups WHERE Groups.GId=GroupCaps.GId AND GroupCaps.CId=Caps.CId;" ) es.sql( 'query', 'group_auth', "CREATE VIEW [vwCombined] AS SELECT Caps.*, Players.*, Groups.* FROM Caps, GroupCaps, Groups, Players, PlayersGroups WHERE Players.Uid=PlayersGroups.Uid AND PlayersGroups.GId=Groups.GId AND Groups.GId=GroupCaps.GId AND GroupCaps.CId=Caps.CId;" ) es.sql( 'query', 'group_auth', "CREATE VIEW [vwPlayersGroups] AS SELECT Players.*, Groups.* FROM Groups, Players, PlayersGroups WHERE Players.Uid=PlayersGroups.Uid AND PlayersGroups.GId=Groups.GId;" ) user_create(['user', 'create', 'UNKNOWN', 'STEAM_ID_PENDING']) group_create(['group', 'create', 'UnidentifiedPlayers', '#UNRESTRICTED']) user_join(['user', 'join', 'UNKNOWN', 'UnidentifiedPlayers']) group_create(['group', 'create', 'IdentifiedPlayers', '#IDENTIFIED'])
def sql(argv): es.sql(*argv)