code/docs/superpowers/plans/2026-07-04-sql-crud-practice.md

SQL 增删改查练习功能 Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Let students pick one of three preset teaching tables, write SQL against it in the existing code editor, and see the table's resulting state after execution — with no automated grading, just observation for the teacher.

Architecture: SQL is wired in as a new language alongside python/c/cpp/turtle. Frontend composes <preset setup SQL> + <student SQL> + SELECT * FROM <table>; and submits it through the existing Judge0 pipeline (language_id=82, SQLite) — no backend changes for execution. A small backend addition (sqlparse) extends the existing generic /format endpoint to support SQL too.

Tech Stack: Vue 3 + TypeScript + CodeMirror 6 (@codemirror/lang-sql) on the frontend; Python sqlparse library on the backend (FastAPI).

Spec: codenext/docs/superpowers/specs/2026-07-04-sql-crud-practice-design.md

Global Constraints

• SQL execution requires zero codeapinew/ changes — it reuses the existing Judge0 direct-call path (languageToId["sql"] = 82).
• Every run always starts from the preset's initial data (setupSql) — no persistent/accumulating session across runs.
• After the student's SQL runs, always append SELECT * FROM <table>; to show the final table state, regardless of whether the student wrote SELECT/INSERT/UPDATE/DELETE.
• No automated grading, no answer comparison, no before/after diff highlighting — just show the raw post-execution output text (reuse existing OutputSection, plain text).
• Mobile excludes "sql" from the language selector, exactly like the existing "turtle" exclusion (mobile has no split-pane layout to host a table-picker + preview).
• codenext/ has no frontend test framework (no vitest/jest configured). Verify frontend tasks with npm run build (must succeed) plus manual browser verification via npm run start — do not introduce a new test runner.
• codeapinew/ already has pytest + test_formatter.py — use real TDD (red/green) for the backend task.
• Risk carried from the spec: the self-hosted Judge0 instance's support for language_id=82 (SQLite) has not been verified from code. This plan does not block on it, but Task 4's manual verification step is where it will surface if the image isn't enabled.

---

Task 1: Add sql language plumbing (types, Judge0 id, source, cache, share whitelist)

Files:

• Modify: codenext/src/types.ts:3
• Modify: codenext/src/templates.ts:1-33
• Modify: codenext/src/composables/code.ts:13-23,74-76

Interfaces:

• Produces: LANGUAGE type now includes "sql"; languageToId["sql"] === 82; sources.sql (default placeholder source, used to seed cache.code.sql and reset()).

• Consumes: nothing from other tasks (foundational).

• Step 1: Add "sql" to the LANGUAGE union

Edit codenext/src/types.ts:3:

export type LANGUAGE = "c" | "python" | "cpp" | "turtle" | "sql"

• Step 2: Add the Judge0 language id and default source for SQL

Edit codenext/src/templates.ts. Add a new source constant near the top (after turtleSource):

const sqlSource = "-- 在这里编写你的 SQL 语句\n"

Update languageToId and sources:

export const languageToId: { [key in string]: number } = {
  c: 50,
  cpp: 54,
  java: 62,
  python: 71,
  sql: 82,
}

export const sources = {
  c: cSource,
  cpp: cppSource,
  java: javaSource,
  python: pythonSource,
  turtle: turtleSource,
  sql: sqlSource,
}

• Step 3: Add sql to the code cache and the share-link language whitelist

Edit codenext/src/composables/code.ts. In the cache object (around line 17-22), add:

  code: {
    python: useStorage("code_python", sources["python"]),
    c: useStorage("code_c", sources["c"]),
    cpp: useStorage("code_cpp", sources["cpp"]),
    turtle: useStorage("code_turtle", sources["turtle"]),
    sql: useStorage("code_sql", sources["sql"]),
  },

In init() (around line 74), extend the whitelist:

      const lang = ["python", "c", "cpp", "turtle", "sql"].includes(data.lang)
        ? (data.lang as LANGUAGE)
        : defaultLanguage

• Step 4: Verify the build still succeeds

Run: cd codenext && npm run build Expected: build completes successfully (same output shape as before — no new UI is reachable yet since SelectLanguage.vue doesn't offer "sql" until Task 3).

• Step 5: Commit

cd codenext
git add src/types.ts src/templates.ts src/composables/code.ts
git commit -m "feat: add sql to LANGUAGE type, Judge0 mapping, and code cache"

---

Task 2: Candidate table data + sqlTable composable

Files:

• Create: codenext/src/data/sqlTables.ts
• Create: codenext/src/composables/sqlTable.ts
• Modify: codenext/src/composables/code.ts:40-48,106-126

Interfaces:

• Consumes: LANGUAGE type from Task 1 (already includes "sql").

• Produces:

  • SqlTablePreset interface: { id: string; label: string; description: string; tableName: string; setupSql: string }
  • sqlTables: SqlTablePreset[] (3 presets: students, employees, products)
  • defaultSqlTableId: string (equals sqlTables[0].id, i.e. "students")
  • selectedTableId: Ref<string> (current candidate table selection, used by SqlSection.vue in Task 4)
  • resetSqlTableSelection(): void
  • buildSqlScript(studentSql: string): string — used by run() in this task

• Step 1: Create the candidate table data file

Create codenext/src/data/sqlTables.ts:

export interface SqlTablePreset {
  id: string
  label: string
  description: string
  tableName: string
  setupSql: string
}

const studentsSetupSql = `CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  class TEXT NOT NULL,
  score INTEGER NOT NULL
);

INSERT INTO students (id, name, class, score) VALUES
  (1, '张伟', '一班', 92),
  (2, '王芳', '一班', 58),
  (3, '李娜', '二班', 76),
  (4, '刘洋', '二班', 45),
  (5, '陈静', '一班', 88),
  (6, '杨帆', '三班', 63),
  (7, '赵敏', '二班', 39),
  (8, '孙涛', '三班', 81);`

const employeesSetupSql = `CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT NOT NULL,
  salary INTEGER NOT NULL,
  hire_date TEXT NOT NULL
);

INSERT INTO employees (id, name, department, salary, hire_date) VALUES
  (1, '周明', '技术部', 12000, '2019-03-01'),
  (2, '吴倩', '市场部', 8000, '2021-07-15'),
  (3, '郑凯', '技术部', 15500, '2017-11-20'),
  (4, '钱多多', '财务部', 9200, '2020-01-10'),
  (5, '孙丽', '市场部', 7600, '2022-05-30'),
  (6, '李强', '技术部', 10800, '2023-09-01'),
  (7, '林小雨', '财务部', 8900, '2018-06-12'),
  (8, '黄河', '市场部', 6800, '2024-02-18');`

const productsSetupSql = `CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price REAL NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO products (id, name, category, price, stock) VALUES
  (1, '无线鼠标', '电子产品', 59.9, 120),
  (2, '机械键盘', '电子产品', 299.0, 45),
  (3, '保温杯', '生活用品', 39.5, 0),
  (4, '笔记本', '文具', 12.0, 300),
  (5, '蓝牙耳机', '电子产品', 199.0, 0),
  (6, '台灯', '生活用品', 89.0, 60),
  (7, '钢笔', '文具', 25.0, 150),
  (8, '充电宝', '电子产品', 129.0, 8);`

export const sqlTables: SqlTablePreset[] = [
  {
    id: "students",
    label: "学生成绩表",
    description: "适合练习按分数筛选、批量更新、删除不及格记录",
    tableName: "students",
    setupSql: studentsSetupSql,
  },
  {
    id: "employees",
    label: "员工工资表",
    description: "适合练习按部门分组调薪、按入职日期筛选",
    tableName: "employees",
    setupSql: employeesSetupSql,
  },
  {
    id: "products",
    label: "商品库存表",
    description: "适合练习新增商品、调整库存、下架缺货商品",
    tableName: "products",
    setupSql: productsSetupSql,
  },
]

export const defaultSqlTableId = sqlTables[0].id

• Step 2: Create the sqlTable composable

Create codenext/src/composables/sqlTable.ts:

import { ref } from "vue"
import { defaultSqlTableId, sqlTables } from "../data/sqlTables"

export const selectedTableId = ref(defaultSqlTableId)

export function resetSqlTableSelection() {
  selectedTableId.value = defaultSqlTableId
}

export function buildSqlScript(studentSql: string) {
  const table =
    sqlTables.find((item) => item.id === selectedTableId.value) ??
    sqlTables[0]
  return `${table.setupSql}\n\n${studentSql}\n\nSELECT * FROM ${table.tableName};`
}

• Step 3: Wire the composable into code.ts's language switch and run()

Edit codenext/src/composables/code.ts. Add the import at the top:

import { buildSqlScript, resetSqlTableSelection } from "./sqlTable"

Update the language watcher (around line 40-48) to reset the table selection when switching into SQL:

watch(
  () => code.language,
  (value: LANGUAGE) => {
    cache.language.value = value
    code.value = cache.code[value].value
    output.value = ""
    status.value = Status.NotStarted
    if (value === "sql") resetSqlTableSelection()
  },
)

Update run() (around line 106-126) to build the combined script for SQL:

export async function run() {
  loading.value = true
  const cleanCode = code.value.trim()
  if (!cleanCode) {
    loading.value = false
    return
  }
  if (code.language === "turtle") {
    turtleRunId.value++
  } else {
    output.value = ""
    status.value = Status.NotStarted
    const sourceCode =
      code.language === "sql" ? buildSqlScript(cleanCode) : cleanCode
    const result = await submit(
      { value: sourceCode, language: code.language },
      input.value.trim(),
    )
    output.value = result.output || ""
    status.value = result.status
  }
  loading.value = false
}

• Step 4: Verify the build still succeeds

Run: cd codenext && npm run build Expected: build completes successfully. (Behavioral verification of buildSqlScript/run() happens end-to-end in Task 4, once the UI can reach the SQL language.)

• Step 5: Commit

cd codenext
git add src/data/sqlTables.ts src/composables/sqlTable.ts src/composables/code.ts
git commit -m "feat: add candidate SQL table presets and script-building composable"

---

Task 3: Editor & language selector UI (make SQL selectable + syntax highlighted)

Files:

• Modify: codenext/package.json
• Modify: codenext/src/components/CodeEditor.vue:1-61
• Modify: codenext/src/components/SelectLanguage.vue:7-25
• Create: codenext/public/sql.svg

Interfaces:

• Consumes: LANGUAGE type from Task 1 ("sql" already valid).

• Produces: "sql" becomes selectable in SelectLanguage.vue's dropdown (desktop only); CodeEditor.vue renders SQL syntax highlighting when language === "sql".

• Step 1: Install the CodeMirror SQL language package

Run: cd codenext && npm install @codemirror/lang-sql Expected: package.json gains a new dependency entry under "@codemirror/lang-sql" (npm resolves the version; do not hand-pick a version number).

• Step 2: Add SQL syntax highlighting to CodeEditor.vue

Edit codenext/src/components/CodeEditor.vue. Add the import near the other @codemirror/lang-* imports (line 2-3):

import { sql } from "@codemirror/lang-sql"

Update langExtension (line 56-61):

const langExtension = computed(() => {
  if (props.language === "python" || props.language === "turtle") {
    return python()
  }
  if (props.language === "sql") {
    return sql()
  }
  return cpp()
})

• Step 3: Add the SQL option to the language selector, excluded on mobile like turtle

Edit codenext/src/components/SelectLanguage.vue. Update allLangs and the mobile filter (line 7-18):

const LANGS = computed(() => {
  const allLangs = [
    ["python", "Python"],
    ["turtle", "海龟绘图"],
    ["c", "C 语言"],
    ["cpp", "C++"],
    ["sql", "SQL"],
  ]
  if (isMobile.value) {
    return allLangs.filter(([lang]) => lang !== "turtle" && lang !== "sql")
  }
  return allLangs
})

Update the mobile auto-switch watcher (line 20-25) to also cover SQL:

// 如果当前在移动端且语言是海龟绘图或 SQL，自动切换到 Python
watch(isMobile, (mobile) => {
  if (mobile && (code.language === "turtle" || code.language === "sql")) {
    code.language = "python"
  }
})

• Step 4: Add the SQL language icon

Create codenext/public/sql.svg:

<svg xmlns="http://www.w3.org/2000/svg" width="1em" height="1em" viewBox="0 0 24 24"><path fill="#a9b9cb" d="M12 3c-4.97 0-9 1.343-9 3v12c0 1.657 4.03 3 9 3s9-1.343 9-3V6c0-1.657-4.03-3-9-3z"/><path fill="#7f8b99" d="M3 6v3c0 1.657 4.03 3 9 3s9-1.343 9-3V6c0 1.657-4.03 3-9 3S3 7.657 3 6z"/><path fill="#7f8b99" d="M3 11v3c0 1.657 4.03 3 9 3s9-1.343 9-3v-3c0 1.657-4.03 3-9 3s-9-1.343-9-3z"/></svg>

• Step 5: Verify the build succeeds

Run: cd codenext && npm run build Expected: build completes successfully.

• Step 6: Manually verify the language selector and syntax highlighting

Run: cd codenext && npm run start, open http://localhost:3000 in a browser.

• Open the language dropdown (desktop layout) — confirm "SQL" appears with the new icon and is selectable.

• Select SQL — confirm the editor now does SQL syntax highlighting (keywords colored) instead of C++ highlighting.

• Shrink the window to mobile width (or use device toolbar) — confirm "SQL" is absent from the dropdown, matching turtle's existing mobile behavior.

• Step 7: Commit

cd codenext
git add package.json package-lock.json src/components/CodeEditor.vue src/components/SelectLanguage.vue public/sql.svg
git commit -m "feat: make SQL selectable as a language with syntax highlighting"

---

Task 4: SqlSection UI + Content.vue wiring (end-to-end feature)

Files:

• Create: codenext/src/desktop/SqlSection.vue
• Modify: codenext/src/desktop/Content.vue

Interfaces:

• Consumes: selectedTableId, sqlTables (Task 2); code.language, run() (Task 1/2, via composables/code.ts); OutputSection.vue (existing, unmodified).

• Produces: fully working SQL practice flow reachable from the desktop UI.

• Step 1: Create SqlSection.vue

Create codenext/src/desktop/SqlSection.vue:

<script lang="ts" setup>
import { computed } from "vue"
import CodeEditor from "../components/CodeEditor.vue"
import { selectedTableId } from "../composables/sqlTable"
import { sqlTables } from "../data/sqlTables"
import OutputSection from "./OutputSection.vue"

const tableOptions = computed(() =>
  sqlTables.map((table) => ({ value: table.id, label: table.label })),
)

const selectedTable = computed(
  () =>
    sqlTables.find((table) => table.id === selectedTableId.value) ??
    sqlTables[0],
)
</script>

<template>
  <n-flex vertical class="sql-section">
    <n-select
      class="table-select"
      v-model:value="selectedTableId"
      :options="tableOptions"
    />
    <n-split
      direction="vertical"
      :default-size="1 / 3"
      :min="1 / 5"
      :max="3 / 5"
    >
      <template #1>
        <CodeEditor
          :model-value="selectedTable.setupSql"
          language="sql"
          readonly
          label="表结构与初始数据"
        />
      </template>
      <template #2>
        <OutputSection />
      </template>
    </n-split>
  </n-flex>
</template>

<style scoped>
.sql-section {
  height: 100%;
}
.table-select {
  margin: 12px 20px 0;
  width: 160px;
}
</style>

• Step 2: Wire SqlSection into Content.vue

Edit codenext/src/desktop/Content.vue:

<script lang="ts" setup>
import { code } from "../composables/code"
import CodeSection from "./CodeSection.vue"
import InputSection from "./InputSection.vue"
import OutputSection from "./OutputSection.vue"
import SqlSection from "./SqlSection.vue"
import TurtleSection from "./TurtleSection.vue"
</script>

<template>
  <n-layout-content class="container">
    <n-split direction="horizontal" :min="1 / 3" :max="4 / 5">
      <template #1>
        <CodeSection />
      </template>
      <template #2>
        <n-split
          v-if="code.language !== 'turtle' && code.language !== 'sql'"
          direction="vertical"
          :default-size="1 / 3"
          :min="1 / 5"
          :max="3 / 5"
        >
          <template #1>
            <InputSection />
          </template>
          <template #2>
            <OutputSection />
          </template>
        </n-split>
        <TurtleSection v-else-if="code.language === 'turtle'" />
        <SqlSection v-else />
      </template>
    </n-split>
  </n-layout-content>
</template>

<style scoped>
.container {
  height: calc(100vh - 60px);
}
</style>

• Step 3: Verify the build succeeds

Run: cd codenext && npm run build Expected: build completes successfully.

• Step 4: Manually verify the full end-to-end flow

Run: cd codenext && npm run start, open http://localhost:3000.

1. Switch language to SQL. Confirm the right pane now shows: a table dropdown (defaulting to "学生成绩表"), a read-only preview of the students setup SQL below it, and an (empty) output panel at the bottom.
2. In the left editor, type: UPDATE students SET score = 100 WHERE name = '王芳';
3. Click run (or press F5). Confirm the output panel shows the full students table with 王芳's score now 100 and all other rows unchanged.
4. Switch the table dropdown to "员工工资表". Confirm the preview panel updates to show the employees setup SQL.
5. Type DELETE FROM employees WHERE salary < 8000; and run. Confirm the output shows the employees table with the two lowest-salary rows (吴倩 8000 stays if not < 8000 — confirm rows below 8000, i.e. 孙丽 7600 and 黄河 6800, are gone) and the rest intact.
6. Re-run without changing anything. Confirm the result is identical each time (proves it always restarts from the preset data rather than accumulating state).
7. Type intentionally broken SQL (e.g. SELCT * FROM students;) and run. Confirm an error surfaces through the existing error/AI-analysis UI, the same way a C/C++ compile error would.
8. If this step fails with a Judge0 "language not supported" error: this surfaces the risk noted in the spec — the self-hosted Judge0 instance needs language_id=82 (SQLite) enabled. Stop and flag this to the user; it's an infra fix, not a code fix.

• Step 5: Commit

cd codenext
git add src/desktop/SqlSection.vue src/desktop/Content.vue
git commit -m "feat: add SqlSection UI wired into the desktop editor layout"

---

Task 5: Backend SQL formatting via sqlparse

Files:

• Modify: codeapinew/formatter.py
• Modify: codeapinew/pyproject.toml
• Modify: codeapinew/requirements.txt
• Modify: codeapinew/test_formatter.py

Interfaces:

• Consumes: nothing from other tasks (independent of the frontend tasks).

• Produces: format_code(code, "sql") returns sqlparse-formatted SQL instead of raising FormatError.

• Step 1: Write the failing tests

Add to codeapinew/test_formatter.py:

def test_format_sql_uppercases_keywords():
    result = format_code("select * from students where score>60", "sql")
    assert result == "SELECT * FROM students WHERE score>60"


def test_format_sql_splits_multiple_statements():
    result = format_code(
        "delete from students where score<60;insert into students (id) values (9);",
        "sql",
    )
    assert result == (
        "DELETE FROM students WHERE score<60;\n\n"
        "INSERT INTO students (id) VALUES (9);"
    )


def test_format_sql_tolerates_syntax_errors():
    result = format_code("select from where", "sql")
    assert result == "SELECT FROM WHERE"

• Step 2: Run the tests to verify they fail

Run: cd codeapinew && uv run pytest test_formatter.py -v -k sql Expected: 3 failures — format_code raises FormatError: 不支持的语言: sql (the sql branch doesn't exist yet).

• Step 3: Add the sqlparse dependency

Run: cd codeapinew && uv add sqlparse Expected: pyproject.toml gains sqlparse under dependencies, uv.lock updates.

Check the resolved version:

Run: grep sqlparse pyproject.toml uv.lock | head -5

Add the matching pinned entry to codeapinew/requirements.txt (insert alphabetically, matching the existing pin style, e.g. sqlparse==<resolved-version>).

• Step 4: Implement the sql branch in formatter.py

Edit codeapinew/formatter.py. Add the import at the top:

import sqlparse

Add the helper function (near _run, before format_code):

def _format_with_sql(code: str) -> str:
    # sqlparse 对语法错误宽容，不会抛异常，语法问题留给判题阶段反馈
    # strip_whitespace 会把多条语句压成一行，先按分号拆分再逐条格式化
    statements = sqlparse.split(code)
    return "\n\n".join(
        sqlparse.format(s, strip_whitespace=True, keyword_case="upper")
        for s in statements
    )

Add the branch inside format_code (before the final raise FormatError):

    if language == "sql":
        return _format_with_sql(code)

• Step 5: Run the tests to verify they pass

Run: cd codeapinew && uv run pytest test_formatter.py -v Expected: all tests pass, including the 3 new sql tests and the pre-existing python/c/cpp/unsupported-language tests (no regressions).

• Step 6: Commit

cd codeapinew
git add formatter.py pyproject.toml uv.lock requirements.txt test_formatter.py
git commit -m "feat: format SQL code via sqlparse in the /format endpoint"