SQLite lesson data store with schema and C# access layer #4

New issue

Closed

opened 2026-05-16 04:11:26 +00:00 by agent-engineering · 0 comments

agent-engineering commented

2026-05-16 04:11:26 +00:00

Contributor

What

Create the lesson data store at plugin/Data/LessonStore.cs:

SQLite schema:

CREATE TABLE lessons (
  id TEXT PRIMARY KEY,           -- UUID
  title TEXT NOT NULL,
  grade INTEGER NOT NULL,
  subject TEXT NOT NULL,
  lesson_date TEXT NOT NULL,     -- ISO date
  description TEXT,
  source_path TEXT NOT NULL,     -- path to .typ file
  compiled_html TEXT,            -- cached Typst → HTML output
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE lesson_media (
  lesson_id TEXT REFERENCES lessons(id),
  jellyfin_item_id TEXT NOT NULL,  -- Jellyfin UUID
  sort_order INTEGER NOT NULL,
  PRIMARY KEY (lesson_id, jellyfin_item_id)
);

C# access layer:

Uses Microsoft.Data.Sqlite (already available in Jellyfin's runtime)
LessonStore(string dbPath) constructor
Methods: GetAll(), GetById(string id), GetByGrade(int grade), Upsert(Lesson lesson), Delete(string id)
Read operations should work for the Jellyfin plugin (read-only access)

Acceptance

Integration test creates a DB, inserts a lesson, reads it back
Lesson model class with all fields mapped
LessonMedia model for the join table
DB path: ~/.local/share/jellyfin-lessons/lessons.db (configurable)

Notes

Use Microsoft.Data.Sqlite NuGet package (add to csproj). Jellyfin already ships this so ExcludeAssets=runtime is fine.

The store is shared between the Jellyfin plugin (reads) and the admin UI (writes). Both will use the same DB path.

## What Create the lesson data store at `plugin/Data/LessonStore.cs`: **SQLite schema:** ```sql CREATE TABLE lessons ( id TEXT PRIMARY KEY, -- UUID title TEXT NOT NULL, grade INTEGER NOT NULL, subject TEXT NOT NULL, lesson_date TEXT NOT NULL, -- ISO date description TEXT, source_path TEXT NOT NULL, -- path to .typ file compiled_html TEXT, -- cached Typst → HTML output created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE lesson_media ( lesson_id TEXT REFERENCES lessons(id), jellyfin_item_id TEXT NOT NULL, -- Jellyfin UUID sort_order INTEGER NOT NULL, PRIMARY KEY (lesson_id, jellyfin_item_id) ); ``` **C# access layer:** - Uses `Microsoft.Data.Sqlite` (already available in Jellyfin's runtime) - `LessonStore(string dbPath)` constructor - Methods: `GetAll()`, `GetById(string id)`, `GetByGrade(int grade)`, `Upsert(Lesson lesson)`, `Delete(string id)` - Read operations should work for the Jellyfin plugin (read-only access) ## Acceptance - Integration test creates a DB, inserts a lesson, reads it back - `Lesson` model class with all fields mapped - `LessonMedia` model for the join table - DB path: `~/.local/share/jellyfin-lessons/lessons.db` (configurable) ## Notes Use `Microsoft.Data.Sqlite` NuGet package (add to csproj). Jellyfin already ships this so ExcludeAssets=runtime is fine. The store is shared between the Jellyfin plugin (reads) and the admin UI (writes). Both will use the same DB path.