async def __write_permission_into_dataBase(
db: aiosqlite.Connection,
id: str,
description: str = "",
parent: str = ROOT_PERMISSION_ID) -> NoReturn:
try:
async with db.cursor() as cursor:
await cursor.execute(
'''
SELECT * FROM Permissions
WHERE PermissionId=?
''', (id, ))
if await cursor.fetchone():
await cursor.execute(
'''
UPDATE Permissions SET
PermissionId=?,Description=?,PermissionParent=?
WHERE PermissionId=?
''', (id, description, parent, id))
else:
await cursor.execute(
'''
INSERT INTO Permissions (PermissionId,Description,PermissionParent)
VALUES (?,?,?)
''', (id, description, parent))
except:
await db.rollback()
raise
finally:
await db.commit()
async def create_database_schema(conn: aiosqlite.Connection) -> None:
"""Generate tables in database"""
schema = "db/database.sql"
with open(schema, encoding="UTF-8") as file:
sql_script = file.read()
async with conn.cursor() as cursor:
await cursor.executescript(sql_script)
await conn.commit()
async def __get_permission_from_database(
db: aiosqlite.Connection,
permittee: PermitteeId) -> Iterable[PermissionId]:
async with db.cursor() as cursor:
await cursor.execute(
'''
SELECT * FROM CommandPermission
WHERE PermissionId=?
''', (permittee.id, ))
async for row in cursor:
yield await load_permission_from_dataBase(db, row[0])
async def __get_permittee_from_database(
db: aiosqlite.Connection,
permission: PermissionId) -> Iterable[PermitteeId]:
async with db.cursor() as cursor:
await cursor.execute(
'''
SELECT * FROM CommandPermission
WHERE PermissionId=?
''', (permission.id, ))
async for row in cursor:
yield PermitteeId(id=row[1], directParents=tuple())
async def __set_permission_with_permittee(db: aiosqlite.Connection,
permission: PermissionId,
permittee: PermitteeId) -> NoReturn:
try:
if await __check_permittee_permission(db, permission, permittee):
return
async with db.cursor() as cursor:
await cursor.execute(
'''
INSERT INTO CommandPermission (PermissionId,PermitteeId)
VALUES (?,?)
''', (permission.id, permittee.id))
except:
await db.rollback()
raise
finally:
await db.commit()
async def __load_permission_from_dataBase(db: aiosqlite.Connection,
id: str) -> Dict[str, str]:
try:
async with db.cursor() as cursor:
await cursor.execute(
'''
SELECT * FROM Permissions
WHERE PermissionId=?
''', (id, ))
row = await cursor.fetchone()
if row:
return {"id": row[0], "description": row[1], "parent": row[2]}
await __write_permission_into_dataBase(db, id)
return await __load_permission_from_dataBase(db, id)
except:
await db.rollback()
raise
finally:
await db.commit()