spikerj/spikersoft-issues
1
0
Fork 0
Code Issues 196 Pull Requests Actions Packages Projects Releases Wiki Activity

sql: sandbox starter schema — give the empty DuckDB connection something to query #139

New Issue
Closed
opened 2026-05-15 01:40:24 +00:00 by spikerj · 1 comment
spikerj commented 2026-05-15 01:40:24 +00:00
Owner
Copy Link
Copy Source

Context

Follow-up to #132. The new SQL playground (feature-dev-tools-sql-runner) opens the sandbox tab with an empty DuckDB connection — the student has to CREATE TABLE + INSERT before any SELECT will return rows. That's pedagogically backwards: the sandbox is supposed to be a low-friction place to try out the SELECT/WHERE/JOIN they just learned, not a forcing function for DDL.

Lessons mode is unaffected — each lesson's SqlLessonPlan ships schema+seed and the grader loads them automatically. This is sandbox-mode only.

What needs to happen

1. Embed a sandbox snapshot per theme

Mirror the three backend schema constants (TravelSchema, VideoGamesSchema, AnimalsSchema) as TypeScript string constants in the frontend feature library. Pre-load the active theme's snapshot into the DuckDB connection when the user lands on the sandbox tab.

File: libraries/features/dev-tools-sql-runner/src/lib/sandbox-snapshots.ts

export const TRAVEL_SANDBOX_SCHEMA = `...same DDL as TravelSchema.SchemaSql...`;
export const TRAVEL_SANDBOX_SEED   = `...same INSERTs as TravelSchema.SeedSql...`;
// ... same for video-games / animals

These must stay in sync with the backend C# files — add a smoke test in SqlCurriculumTests (or a frontend vitest) that diffs the two and fails on drift. Or extract the schemas into a shared .sql asset folder served from /assets/sql-themes/<theme>/{schema,seed}.sql that both sides read from — cleaner long-term but adds an HTTP fetch on first sandbox open.

2. Wire it into SqlRunnerService.executeCode

Replace the current await this.duckdb.reset(); at the top of executeCode with await this.duckdb.loadThemeSnapshot(snapshot.schema, snapshot.seed) based on the active theme. Reset still happens (loadThemeSnapshot calls reset internally) so accumulated state doesn't bleed across runs, but the user lands on tables they can actually query.

3. Reload on theme switch

Wire the theme picker's themeChangedSignal into SqlRunner (the shell component) so a theme switch immediately reloads the sandbox snapshot. Today the change only affects the NEXT submission — by the time the user types a query against tables they expect, the connection is still on the old theme.

4. Update the sandbox starter code

SQL_LANGUAGE_RUNNER_CONFIG.starterCode should reflect the active theme's table names. Either:

  • Make starterCode theme-aware (replace it at runtime when the picker changes), or
  • Keep a single generic starter and rely on the lesson sidebar to teach table names. The first option is more student-friendly.

Acceptance

  • Sandbox tab opens with the active theme's tables already populated — SELECT * FROM travelers LIMIT 5; returns 5 rows on first paint
  • Switching theme reloads the sandbox snapshot AND updates the editor's starter query
  • Sandbox snapshots stay in sync with backend <Theme>Schema.cs (smoke test or shared .sql assets)
  • Each Run still resets the connection so prior DDL/DML doesn't leak across runs

Labels

enhancement · frontend · ux · sql

## Context Follow-up to [#132](https://git.spikersoft.com/spikerj/spikersoft-issues/issues/132). The new SQL playground (`feature-dev-tools-sql-runner`) opens the sandbox tab with an empty DuckDB connection — the student has to `CREATE TABLE` + `INSERT` before any SELECT will return rows. That's pedagogically backwards: the sandbox is supposed to be a low-friction place to try out the SELECT/WHERE/JOIN they just learned, not a forcing function for DDL. Lessons mode is unaffected — each lesson's `SqlLessonPlan` ships schema+seed and the grader loads them automatically. This is sandbox-mode only. ## What needs to happen ### 1. Embed a sandbox snapshot per theme Mirror the three backend schema constants ([`TravelSchema`](spikersoft-backend/SpikerSoft.Business/Domain/Lessons/Curriculum/Sql/Themes/TravelSchema.cs), [`VideoGamesSchema`](spikersoft-backend/SpikerSoft.Business/Domain/Lessons/Curriculum/Sql/Themes/VideoGamesSchema.cs), [`AnimalsSchema`](spikersoft-backend/SpikerSoft.Business/Domain/Lessons/Curriculum/Sql/Themes/AnimalsSchema.cs)) as TypeScript string constants in the frontend feature library. Pre-load the active theme's snapshot into the DuckDB connection when the user lands on the sandbox tab. File: `libraries/features/dev-tools-sql-runner/src/lib/sandbox-snapshots.ts` ```ts export const TRAVEL_SANDBOX_SCHEMA = `...same DDL as TravelSchema.SchemaSql...`; export const TRAVEL_SANDBOX_SEED = `...same INSERTs as TravelSchema.SeedSql...`; // ... same for video-games / animals ``` These must stay in sync with the backend C# files — add a smoke test in `SqlCurriculumTests` (or a frontend vitest) that diffs the two and fails on drift. Or extract the schemas into a shared `.sql` asset folder served from `/assets/sql-themes/<theme>/{schema,seed}.sql` that both sides read from — cleaner long-term but adds an HTTP fetch on first sandbox open. ### 2. Wire it into `SqlRunnerService.executeCode` Replace the current `await this.duckdb.reset();` at the top of `executeCode` with `await this.duckdb.loadThemeSnapshot(snapshot.schema, snapshot.seed)` based on the active theme. Reset still happens (`loadThemeSnapshot` calls `reset` internally) so accumulated state doesn't bleed across runs, but the user lands on tables they can actually query. ### 3. Reload on theme switch Wire the theme picker's `themeChangedSignal` into `SqlRunner` (the shell component) so a theme switch immediately reloads the sandbox snapshot. Today the change only affects the NEXT submission — by the time the user types a query against tables they expect, the connection is still on the old theme. ### 4. Update the sandbox starter code `SQL_LANGUAGE_RUNNER_CONFIG.starterCode` should reflect the active theme's table names. Either: - Make `starterCode` theme-aware (replace it at runtime when the picker changes), or - Keep a single generic starter and rely on the lesson sidebar to teach table names. The first option is more student-friendly. ## Acceptance - [ ] Sandbox tab opens with the active theme's tables already populated — `SELECT * FROM travelers LIMIT 5;` returns 5 rows on first paint - [ ] Switching theme reloads the sandbox snapshot AND updates the editor's starter query - [ ] Sandbox snapshots stay in sync with backend `<Theme>Schema.cs` (smoke test or shared `.sql` assets) - [ ] Each Run still resets the connection so prior DDL/DML doesn't leak across runs ## Labels `enhancement` · `frontend` · `ux` · `sql`
spikerj commented 2026-05-15 02:11:50 +00:00
Author
Owner
Copy Link
Copy Source

Closed by implementation

Shipped:

  • sandbox-snapshots.ts embeds a trimmed schema + seed for each of the three themes (Travel / Video Games / Animals) plus a theme-appropriate starter query. The sync-with-backend contract is documented in the file header.
  • SqlRunnerService.executeCode now calls duckdb.loadThemeSnapshot(...) instead of a bare reset() so every sandbox Run lands on the active theme's tables. Reset still happens (loadThemeSnapshot calls it internally), so prior DDL/DML state still doesn't leak across runs.
  • New SqlRunnerService.prepareSandbox() method that the shell calls on init (so the very first SELECT works without a prior Run) and on theme change (so swapping themes immediately changes the available tables).
  • The SqlRunner shell wires an Angular effect() to the theme picker's themeChangedSignal() so a theme switch triggers prepareSandbox() automatically.

Verification: SELECT first_name FROM travelers LIMIT 5; returns rows on first paint of sandbox mode — no CREATE TABLE needed.

Note: the editor's starter text swap-on-theme-change is part of #140 (theme-switch UX polish). This ticket covers the data side; the editor display side closes over there.

Closing.

## Closed by implementation Shipped: - [`sandbox-snapshots.ts`](spikersoft-angular/libraries/features/dev-tools-sql-runner/src/lib/sandbox-snapshots.ts) embeds a trimmed schema + seed for each of the three themes (Travel / Video Games / Animals) plus a theme-appropriate starter query. The sync-with-backend contract is documented in the file header. - `SqlRunnerService.executeCode` now calls `duckdb.loadThemeSnapshot(...)` instead of a bare `reset()` so every sandbox Run lands on the active theme's tables. Reset still happens (loadThemeSnapshot calls it internally), so prior DDL/DML state still doesn't leak across runs. - New `SqlRunnerService.prepareSandbox()` method that the shell calls on init (so the very first SELECT works without a prior Run) and on theme change (so swapping themes immediately changes the available tables). - The `SqlRunner` shell wires an Angular `effect()` to the theme picker's `themeChangedSignal()` so a theme switch triggers `prepareSandbox()` automatically. **Verification**: `SELECT first_name FROM travelers LIMIT 5;` returns rows on first paint of sandbox mode — no `CREATE TABLE` needed. Note: the editor's *starter text* swap-on-theme-change is part of #140 (theme-switch UX polish). This ticket covers the data side; the editor display side closes over there. Closing.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
agentic
Actionable surfaced by the agentic architecture & security review
 bug
Something is not working
 duplicate
This issue or pull request already exists
 enhancement
New feature
 help wanted
Need some help
 invalid
Something is wrong
 question
More information is needed
 sonarqube
Code quality issue surfaced by SonarQube (bug / code smell)
 wontfix
This won't be fixed
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
spikerj (Joseph Spiker)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: spikerj/spikersoft-issues#139