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