async def read_wing(
wing_id: int, db_pool: Pool = Depends(get_db_pool)) -> Record:
wing: Record
connection: Connection
async with db_pool.acquire() as connection:
wing = await wings_repo.find_by_id(connection, wing_id)
return wing
async def update_rank(rank_id: int, rank_update: RankUpdate, db_pool: Pool = Depends(get_db_pool)) -> Record:
rank: Record
connection: Connection
async with db_pool.acquire() as connection:
await ranks_repo.update(connection, rank_id, rank_update)
rank = await ranks_repo.find_by_id(connection, rank_id)
return rank
async def get_records(pool: asyncpg.Pool, url: str):
async with pool.acquire() as conn:
records_success = await conn.fetch(
'SELECT * FROM success JOIN sites s on s.id = success.site_id WHERE url = $1', url)
records_fail = await conn.fetch(
'SELECT * FROM errors JOIN sites s on s.id = errors.site_id WHERE url = $1', url)
return records_success, records_fail
async def create_tables(pool: Pool) -> None:
"""Execute all sql files inside /tables folder."""
tables_path = Path("bot", "postgres", "tables")
async with pool.acquire() as connection:
async with connection.transaction():
for table_file in tables_path.iterdir():
await connection.execute(table_file.read_text())
async def create_wing(
wing_create: WingCreate, db_pool: Pool = Depends(get_db_pool)) -> Record:
wing: Record
connection: Connection
async with db_pool.acquire() as connection:
wing_id: int = await wings_repo.save(connection, wing_create)
wing = await wings_repo.find_by_id(connection, wing_id)
return wing
async def db_execute(pool: Pool, sql_statement: str, *args) -> str:
"""Execute SQL statement."""
async with pool.acquire() as connection:
logger.info(f"Executing SQL: {sql_statement}")
logger.info(f"with args: {args}")
status = await connection.execute(sql_statement, *args)
logger.info(f"DB execute status: {status}")
return status
async def create_rank_category(rank_category_create: RankCategoryCreate,
db_pool: Pool = Depends(get_db_pool)) -> Record:
rank_category: Record
connection: Connection
async with db_pool.acquire() as connection:
rank_category_id: int = await rank_categories_repo.save(connection, rank_category_create)
rank_category = await rank_categories_repo.find_by_id(connection, rank_category_id)
return rank_category
async def db_fetch(pool: Pool, sql_statement: str, *args) -> List[Record]:
"""Execute SQL statement."""
async with pool.acquire() as connection:
result = await connection.fetch(
sql_statement,
*args,
)
return result
async def read_rank_categories(enabled: Optional[bool] = None, db_pool: Pool = Depends(get_db_pool)) -> List[Record]:
rank_categories: List[Record]
connection: Connection
async with db_pool.acquire() as connection:
if enabled is None:
rank_categories = await rank_categories_repo.find_all(connection)
elif enabled:
rank_categories = await rank_categories_repo.find_enabled(connection)
else:
rank_categories = await rank_categories_repo.find_disabled(connection)
return rank_categories
async def read_ranks_for_rank_category(rank_category_id: int, enabled: Optional[bool] = None,
db_pool: Pool = Depends(get_db_pool)) -> List[Record]:
ranks: List[Record]
connection: Connection
async with db_pool.acquire() as connection:
if enabled is None:
ranks = await ranks_repo.find_all_by_rank_category_id(connection, rank_category_id)
elif enabled:
ranks = await ranks_repo.find_enabled_by_rank_category_id(connection, rank_category_id)
else:
ranks = await ranks_repo.find_disabled_by_rank_category_id(connection, rank_category_id)
return ranks
async def read_wings(
enabled: Optional[bool] = None,
db_pool: Pool = Depends(get_db_pool)
) -> List[Record]:
wings: List[Record]
connection: Connection
async with db_pool.acquire() as connection:
if enabled is None:
wings = await wings_repo.find_all(connection)
elif enabled:
wings = await wings_repo.find_enabled(connection)
else:
wings = await wings_repo.find_disabled(connection)
return wings
async def create(
cls,
pool: asyncpg.Pool,
discord_id: int,
nick: str,
race_id: int,
class_id: int,
location_id: Optional[int] = None,
) -> Player:
if location_id is None:
location_id = race_id
async with pool.acquire() as conn:
async with conn.transaction():
try:
player_data = await conn.fetchrow(
(
"INSERT INTO players (discord_id, nick, race, class, location)"
"VALUES ($1, $2, $3, $4, $5) RETURNING *"
),
discord_id,
nick,
race_id,
class_id,
location_id,
)
except asyncpg.UniqueViolationError:
# TODO: parse e.detail to get problematic key or check beforehand
raise NickOrIDUsed
equipment_data = await conn.fetchrow(
"INSERT INTO equipment (discord_id) VALUES ($1) RETURNING *",
discord_id,
)
return cls.from_data(player_data, equipment_data)
async def read_rank_category(rank_category_id: int, db_pool: Pool = Depends(get_db_pool)) -> Record:
rank_category: Record
connection: Connection
async with db_pool.acquire() as connection:
rank_category = await rank_categories_repo.find_by_id(connection, rank_category_id)
return rank_category