综合练习：tracks schema 与只读 API

这一课你将能做到

1. 在 PostgreSQL 中独立落地 app.cf_track / app.cf_lesson 子集（约束、timestamptz、JSONB、索引齐全）
2. 插入与 sql-essentials 概念一致的种子数据，并用 JOIN / JSONB / EXPLAIN 自检
3. （可选）用 Spring pg-dev 暴露与生产隔离的只读 GET /api/pg/tracks

为什么要学

前几课拆开练：安装、DDL、类型、JSONB、索引、事务、Spring、迁移、选型、Compose。本课收束成一条可演示的闭环——像本站课程模型，但方言是 PG。

验收标准写清楚，避免「自以为会」；可选 API 证明应用层也通。仍不修改生产 MySQL。

先建立图像

验收闭环：

  Compose/容器 PG
       │
       ▼
  Flyway 或手跑 V1 DDL  →  app.cf_track / cf_lesson
       │
       ▼
  种子数据（≥3 track，每门 ≥2 lesson）
       │
       ├─ SQL：JOIN 统计、JSONB @>、slug 的 EXPLAIN
       └─（可选）Spring GET /api/pg/tracks

验收项	标准
DDL	无 MySQL 专有类型；有 PK/UK/FK；布尔用 BOOLEAN
扩展	metadata JSONB + GIN
查询	一条 JOIN 聚合 + 一条 @>
计划	slug = ? 走索引类节点
API	可选；路径勿覆盖生产 /api/tracks

跟着做

步骤 1：环境拉起

用 pg-docker 的 Compose 启动，psql 连入 codeforge_pg。确认与 MySQL 3306 无冲突。

步骤 2：落地 Schema

参考本站 backend/src/main/resources/schema.sql 改写方言（不要原样粘贴）：

CREATE SCHEMA IF NOT EXISTS app;

CREATE TABLE app.cf_track (
    id          BIGSERIAL PRIMARY KEY,
    slug        VARCHAR(64) NOT NULL,
    title       VARCHAR(255) NOT NULL,
    description TEXT,
    category    VARCHAR(32) NOT NULL DEFAULT 'language',
    level       VARCHAR(16) NOT NULL DEFAULT 'beginner',
    sort_order  INT NOT NULL DEFAULT 0,
    is_member   BOOLEAN NOT NULL DEFAULT FALSE,
    metadata    JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_track_slug UNIQUE (slug),
    CONSTRAINT ck_track_level
      CHECK (level IN ('beginner', 'intermediate', 'advanced'))
);

CREATE TABLE app.cf_lesson (
    id          BIGSERIAL PRIMARY KEY,
    track_id    BIGINT NOT NULL REFERENCES app.cf_track (id) ON DELETE CASCADE,
    slug        VARCHAR(64) NOT NULL,
    title       VARCHAR(255) NOT NULL,
    order_index INT NOT NULL DEFAULT 0,
    content_md  TEXT,
    status      VARCHAR(16) NOT NULL DEFAULT 'draft',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_lesson_track_slug UNIQUE (track_id, slug)
);

CREATE INDEX idx_track_cat_sort ON app.cf_track (category, sort_order);
CREATE INDEX idx_track_metadata_gin ON app.cf_track USING GIN (metadata);

理想情况：把上述纳入 Flyway V1__init_app_schema.sql（pg-migrate）。

步骤 3：种子数据

至少 3 门 track（含 postgresql-essentials、sql-essentials），每门 ≥2 条 lesson。示例头几行：

INSERT INTO app.cf_track (slug, title, category, level, sort_order, metadata)
VALUES
  ('sql-essentials', 'SQL 与关系型数据库', 'language', 'beginner', 10,
   '{"tags":["sql"],"featured":true}'::jsonb),
  ('postgresql-essentials', 'PostgreSQL 入门精讲', 'language', 'intermediate', 20,
   '{"tags":["sql","pg"],"featured":true}'::jsonb),
  ('redis-essentials', 'Redis 入门', 'language', 'beginner', 30,
   '{"tags":["cache"],"featured":false}'::jsonb);

-- 再 INSERT cf_lesson … 绑定 track_id

步骤 4：验收 SQL

-- JOIN 统计
SELECT t.slug, COUNT(l.id) AS lessons
FROM app.cf_track t
LEFT JOIN app.cf_lesson l ON l.track_id = t.id
GROUP BY t.slug
ORDER BY MIN(t.sort_order);

-- JSONB 包含
SELECT slug FROM app.cf_track
WHERE metadata @> '{"tags":["pg"]}'::jsonb;

-- 计划
EXPLAIN
SELECT id, title FROM app.cf_track WHERE slug = 'sql-essentials';

把结果截进笔记（或自己打勾验收表）。

步骤 5（可选）：只读 API

1. profile pg-dev + 驱动（pg-spring）
2. Mapper listAll() 读 app.cf_track
3. 控制器映射 GET /api/pg/tracks（刻意不同路径）
4. curl 看 JSON 列表字段

禁止把默认生产控制器直接改连 PG。

步骤 6：复盘三句话

用自己的话写：

1. 本练习相对 MySQL schema 改了哪些类型？
2. JSONB 与 GIN 解决了什么查询？
3. 若要给队友复现，最小文件集是什么？（compose + V1 + 种子）

讲清楚要点

1. 综合练习考的是组装：单一课会写不等于能交验收。
2. 方言改写是强制步骤：AUTO_INCREMENT/TINYINT 禁止出现。
3. 因为 唯一约束在 slug，所以 种子可用 ON CONFLICT 做幂等（可选）。
4. API 路径隔离：避免误测打到生产实现。
5. EXPLAIN 是验收一部分：证明索引意识，不单「能查出数」。
6. 学完去向：继续 redis-essentials 或加深 spring-boot-essentials；生产仍 MySQL。

你可能会懵的地方

• 和外键顺序？
  先 track 后 lesson；删数据时注意 CASCADE。

• 种子 id 硬编码？
  可用子查询按 slug 找 track_id，少写死数字。

• API 与生产字段不一致？
  练习允许简化；文档说明差异即可。

• 没有 Java 环境？
  SQL 验收已够及格；API 标可选。

• 和 sql-practice 课关系？
  那边深挖 MySQL 本站库只读；这边是 PG 方言重建子集。

错法对照

错法	问题	正解
粘贴 MySQL schema.sql	语法失败	按本课 DDL 改写
只建表无约束	脏数据	UK/FK/CHECK
验收只跑 SELECT *	证明力弱	JOIN + @> + EXPLAIN
可选 API 占用 /api/tracks	与生产混淆	/api/pg/tracks
改默认 yml 指向 PG	主线风险	pg-dev profile

自测三问

1. 验收清单里为什么要求 GIN 与 @> 同时出现？
2. 为何综合练习仍强调「不改生产 MySQL」？
3. Flyway V1 相对「每次手册敲 DDL」的协作价值是什么？

小结一句话

在 PostgreSQL 中重建 tracks 子集并完成 SQL（与可选只读 API）验收，闭合从 DDL 到应用的学习环。

官方延伸

• PostgreSQL：Tutorial
• 返回路线图：全栈 · 或继续 Redis / Spring Boot essentials