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()