async def get_material(*,
material_id: UUID) -> Optional[RowMapping]:
stmt = sa.select(models.Materials)\
.where(models.Materials.c.material_id == str(material_id))
async with database.session() as ses:
return (await ses.execute(stmt)).mappings().one_or_none()
async def complete_material(*,
material_id: UUID,
completion_date: Optional[datetime.date] = None) -> None:
completion_date = completion_date or database.today().date()
logger.debug("Completing material_id=%s", material_id)
get_status_stmt = sa.select(models.Statuses)\
.where(models.Statuses.c.material_id == str(material_id))
update_status_stmt = models.Statuses\
.update().values(completed_at=completion_date)\
.where(models.Statuses.c.material_id == str(material_id))
async with database.session() as ses:
status = (await ses.execute(get_status_stmt)).mappings().first()
if status is None:
raise ValueError("Material_id=%s not assigned", material_id)
if status.completed_at is not None:
raise ValueError("Material_id=%s even completed", material_id)
if status.started_at > completion_date:
raise ValueError
await ses.execute(update_status_stmt)
logger.debug("Material_id=%s completed at %s",
material_id, completion_date)
async def get_materials() -> list[RowMapping]:
logger.info("Getting all materials")
stmt = sa.select(models.Materials)
async with database.session() as ses:
return (await ses.execute(stmt)).all()
async def get_cards_list() -> list[dict[str, Any]]:
logger.info("Getting all cards")
stmt = sa.select([models.Cards,
models.Notes,
models.Materials.c.title]) \
.join(models.Notes,
models.Cards.c.note_id == models.Notes.c.note_id)\
.join(models.Materials,
models.Notes.c.material_id == models.Materials.c.material_id)
async with database.session() as ses:
return [{
"card": {
"card_id": row.card_id,
"question": row.question,
"answer": row.answer,
"added_at": row.added_at
},
"note": {
"note_id": row.note_id,
"material_title": row.title,
"content": row.content,
"page": row.page,
"chapter": row.chapter
}
} async for row in await ses.stream(stmt)]
async def get_statuses() -> list[models.Statuses]:
logger.debug("Getting statuses")
stmt = sa.select(models.Statuses)
async with database.session() as ses:
return (await ses.execute(stmt)).all()
async def get_cards_count() -> int:
logger.debug("Getting amount of cards")
stmt = sa.select(sa.func.count(1))\
.select_from(models.Cards)
async with database.session() as ses:
return await ses.scalar(stmt)
async def _was_material_being_reading(*,
material_id: UUID) -> bool:
stmt = sa.select(sa.func.count(1) >= 1)\
.select_from(models.ReadingLog)\
.where(models.ReadingLog.c.material_id == str(material_id))
async with database.session() as ses:
return await ses.scalar(stmt)
async def does_material_exist(*,
material_id: UUID) -> bool:
logger.debug("Whether material_id=%s exists", material_id)
stmt = sa.select(models.Materials.c.material_id)\
.where(models.Materials.c.material_id == str(material_id))
async with database.session() as ses:
return await ses.scalar(stmt) is not None
async def get_status(*,
material_id: UUID) -> Optional[RowMapping]:
logger.debug("Getting status for material_id=%s", material_id)
stmt = sa.select(models.Statuses) \
.where(models.Statuses.c.material_id == str(material_id))
async with database.session() as ses:
return (await ses.execute(stmt)).mappings().one_or_none()
async def notes_with_cards() -> list[UUID]:
logger.info("Getting notes with a card")
stmt = sa.select(models.Notes.c.note_id)\
.join(models.Cards,
models.Cards.c.note_id == models.Notes.c.note_id)
async with database.session() as ses:
return (await ses.execute(stmt)).all()
async def get_log_records() -> dict[datetime.date, RowMapping]:
logger.debug("Getting all log records")
stmt = sa.select([models.ReadingLog,
models.Materials.c.title.label('material_title')])\
.join(models.Materials,
models.Materials.c.material_id == models.ReadingLog.c.material_id)
async with database.session() as ses:
return {row.date: row async for row in await ses.stream(stmt)}
async def _get_db_snapshot() -> SNAPSHOT:
data = {}
async with database.session() as ses:
for table in TABLES:
stmt = sa.select(table)
data[table.name] = [{
str(key): _convert_date_to_str(value)
for key, value in row.items()
} for row in (await ses.execute(stmt)).mappings().all()]
return data
async def get_completed_materials() -> list[RowMapping]:
logger.debug("Getting completed materials")
stmt = sa.select([models.Materials,
models.Statuses]) \
.join(models.Statuses,
models.Materials.c.material_id == models.Statuses.c.material_id) \
.where(models.Statuses.c.completed_at != None)
async with database.session() as ses:
return (await ses.execute(stmt)).mappings().all()
async def get_material_titles() -> dict[UUID, str]:
logger.debug("Getting material titles")
stmt = sa.select(
[models.Materials.c.material_id, models.Materials.c.title])
async with database.session() as ses:
return {
row.material_id: row.title
async for row in await ses.stream(stmt)
}
async def get_free_materials() -> list[RowMapping]:
logger.debug("Getting free materials")
assigned_condition = sa.select(1) \
.select_from(models.Statuses) \
.where(models.Statuses.c.material_id == models.Materials.c.material_id)
stmt = sa.select(models.Materials)\
.where(~sa.exists(assigned_condition)) \
async with database.session() as ses:
return (await ses.execute(stmt)).all()
async def is_material_assigned(*,
material_id: UUID) -> bool:
logger.debug("Checking material_id=%s", material_id)
stmt = sa.select(models.Materials.c.material_id) \
.join(models.Statuses,
models.Materials.c.material_id == models.Statuses.c.material_id) \
.where(models.Statuses.c.started_at != None) \
.where(models.Materials.c.material_id == str(material_id))
async with database.session() as ses:
return await ses.scalar(stmt) is not None
async def set_log(*, material_id: UUID, count: int,
date: datetime.date) -> None:
logger.debug("Setting log for material_id=%s, count=%s, date=%s: ",
material_id, count, date)
values = {'material_id': str(material_id), 'count': count, 'date': date}
stmt = models.ReadingLog \
.insert().values(values)
async with database.session() as ses:
await ses.execute(stmt)
logger.debug("Log record added")
async def get_reading_material_titles() -> dict[UUID, str]:
logger.debug("Getting material titles")
stmt = sa.select([models.Materials.c.material_id,
models.Materials.c.title])\
.join(models.Statuses,
models.Statuses.c.material_id == models.Materials.c.material_id)\
.where(models.Statuses.c.completed_at == None)
async with database.session() as ses:
return {
row.material_id: row.title
async for row in await ses.stream(stmt)
}
async def get_completion_dates() -> dict[UUID, datetime.date]:
logger.debug("Getting completion dates")
stmt = sa.select([models.Materials.c.material_id,
models.Statuses.c.completed_at]) \
.join(models.Statuses,
models.Statuses.c.material_id == models.Materials.c.material_id) \
.where(models.Statuses.c.completed_at != None)
async with database.session() as ses:
return {
row.material_id: row.completed_at
async for row in await ses.stream(stmt)
}
async def add_material(*,
title: str,
authors: str,
pages: int,
tags: Optional[str]) -> None:
logger.debug("Adding material title=%s", title)
values = {
"title": title,
"authors": authors,
"pages": pages,
"tags": tags
}
stmt = models.Materials\
.insert().values(values)
async with database.session() as ses:
await ses.execute(stmt)
logger.debug("Material added")
async def add_card(*,
material_id: UUID,
note_id: UUID,
question: str,
answer: Optional[str] = None) -> None:
logger.debug("Adding new card")
values = {
"material_id": str(material_id),
"note_id": str(note_id),
"question": question,
"answer": answer,
}
stmt = models.Cards\
.insert().values(values)
async with database.session() as ses:
await ses.execute(stmt)
logger.debug("Card added")
async def start_material(*,
material_id: UUID,
start_date: Optional[datetime.date] = None) -> None:
start_date = start_date or database.today().date()
logger.debug("Starting material_id=%s", material_id)
if start_date > database.today().date():
raise ValueError("Start date must be less than today")
values = {
"material_id": str(material_id),
"started_at": start_date
}
stmt = models.Statuses\
.insert().values(values)
async with database.session() as ses:
await ses.execute(stmt)
logger.debug("Material material_id=%s started", material_id)
async def _restore_db(dump_path: Path) -> None:
if not dump_path.exists():
raise ValueError("Dump file not found")
with dump_path.open() as f:
data = ujson.load(f)
async with database.session() as ses:
# order of them matters
for table in TABLES:
values = [{
key: _convert_str_to_date(value)
for key, value in record.items()
} for record in data[table.name]]
logger.debug("Inserting %s values to %s", len(values), table.name)
stmt = table.insert().values(values)
await ses.execute(stmt)
logger.debug("Data into %s inserted", table.name)
async def add_note(*,
material_id: UUID,
content: str,
chapter: int,
page: int,
date: Optional[datetime.date] = None) -> None:
date = date or database.today()
logger.debug("Adding note for material_id=%s at %s", material_id, date)
values = {
'material_id': str(material_id),
'content': content,
'chapter': chapter,
'page': page,
'added_at': date
}
stmt = models.Notes.\
insert().values(values)\
.returning(models.Notes.c.note_id)
async with database.session() as ses:
note_id = (await ses.execute(stmt)).one()[0]
logger.debug("Note_id=%s added", note_id)
async def get_notes() -> list[RowMapping]:
logger.debug("Getting notes")
stmt = sa.select(models.Notes)
async with database.session() as ses:
return (await ses.execute(stmt)).mappings().all()