from pathlib import Path from PIL import Image from pptx import Presentation from pptx.dml.color import RGBColor from pptx.enum.shapes import MSO_SHAPE from pptx.enum.text import MSO_AUTO_SIZE, MSO_ANCHOR, PP_ALIGN from pptx.util import Inches, Pt ROOT = Path(__file__).resolve().parents[1] ASSETS = ROOT / "assets" OUTPUT = ROOT / "campus_hub_demo_slides.pptx" WIDE_W = 13.333 WIDE_H = 7.5 COLORS = { "navy": "1F4E79", "blue": "2F75B5", "ink": "1F2933", "muted": "5B677A", "line": "C9D4E2", "soft": "F3F6FA", "white": "FFFFFF", "green": "548235", "orange": "C55A11", "red": "C00000", "purple": "7030A0", } FONT = "PingFang SC" MONO = "Consolas" def rgb(name: str) -> RGBColor: value = COLORS.get(name, name).lstrip("#") return RGBColor(int(value[0:2], 16), int(value[2:4], 16), int(value[4:6], 16)) def add_shape(slide, x, y, w, h, fill="white", line="line", shape=MSO_SHAPE.RECTANGLE): item = slide.shapes.add_shape(shape, Inches(x), Inches(y), Inches(w), Inches(h)) item.fill.solid() item.fill.fore_color.rgb = rgb(fill) if line: item.line.color.rgb = rgb(line) item.line.width = Pt(1) else: item.line.fill.background() return item def add_text( slide, text, x, y, w, h, size=16, color="ink", bold=False, align=PP_ALIGN.LEFT, font=FONT, valign=MSO_ANCHOR.TOP, ): box = slide.shapes.add_textbox(Inches(x), Inches(y), Inches(w), Inches(h)) frame = box.text_frame frame.clear() frame.word_wrap = True frame.auto_size = MSO_AUTO_SIZE.TEXT_TO_FIT_SHAPE frame.margin_left = Inches(0.04) frame.margin_right = Inches(0.04) frame.margin_top = Inches(0.03) frame.margin_bottom = Inches(0.03) frame.vertical_anchor = valign p = frame.paragraphs[0] p.alignment = align run = p.add_run() run.text = text run.font.name = font run.font.size = Pt(size) run.font.bold = bold run.font.color.rgb = rgb(color) return box def add_bullets(slide, lines, x, y, w, h, size=14.2, color="ink"): box = slide.shapes.add_textbox(Inches(x), Inches(y), Inches(w), Inches(h)) frame = box.text_frame frame.clear() frame.word_wrap = True frame.auto_size = MSO_AUTO_SIZE.TEXT_TO_FIT_SHAPE frame.margin_left = Inches(0.12) frame.margin_right = Inches(0.08) frame.margin_top = Inches(0.06) frame.margin_bottom = Inches(0.06) for i, line in enumerate(lines): p = frame.paragraphs[0] if i == 0 else frame.add_paragraph() p.text = line p.level = 0 p.font.name = FONT p.font.size = Pt(size) p.font.color.rgb = rgb(color) p.space_after = Pt(5) return box def add_code(slide, text, x, y, w, h, size=9.8): add_shape(slide, x, y, w, h, "soft", "line") return add_text(slide, text, x + 0.12, y + 0.12, w - 0.24, h - 0.24, size, "ink", font=MONO) def add_image(slide, name, x, y, w, h, border=True): path = ASSETS / name with Image.open(path) as img: iw, ih = img.size scale = min(w / iw, h / ih) dw, dh = iw * scale, ih * scale ox, oy = x + (w - dw) / 2, y + (h - dh) / 2 if border: add_shape(slide, x, y, w, h, "white", "line") slide.shapes.add_picture(str(path), Inches(ox), Inches(oy), Inches(dw), Inches(dh)) def add_slide(prs, title, section): slide = prs.slides.add_slide(prs.slide_layouts[6]) slide.background.fill.solid() slide.background.fill.fore_color.rgb = rgb("white") add_shape(slide, 0, 0, WIDE_W, 0.62, "navy", None) add_text(slide, title, 0.48, 0.12, 9.4, 0.36, 20, "white", True, valign=MSO_ANCHOR.MIDDLE) add_text(slide, section, 10.05, 0.18, 2.8, 0.25, 10.5, "white", align=PP_ALIGN.RIGHT) add_shape(slide, 0.48, 6.88, 12.38, 0.01, "line", None) return slide def add_card(slide, title, body, x, y, w, h, accent="blue"): add_shape(slide, x, y, w, h, "white", "line") add_shape(slide, x, y, 0.12, h, accent, None) add_text(slide, title, x + 0.25, y + 0.14, w - 0.35, 0.25, 12.2, "navy", True) add_text(slide, body, x + 0.25, y + 0.45, w - 0.35, h - 0.5, 10.4, "muted") def add_note(slide, text, x, y, w, h=0.58, color="navy"): add_shape(slide, x, y, w, h, "soft", "line") add_text(slide, text, x + 0.18, y + 0.16, w - 0.36, h - 0.2, 12.3, color, True) def add_table(slide, rows, x, y, w, h, col_widths, body_size=10.8): table_shape = slide.shapes.add_table(len(rows), len(rows[0]), Inches(x), Inches(y), Inches(w), Inches(h)) table = table_shape.table for i, width in enumerate(col_widths): table.columns[i].width = Inches(width) for r, row in enumerate(rows): for c, value in enumerate(row): cell = table.cell(r, c) cell.text = value cell.margin_left = Inches(0.06) cell.margin_right = Inches(0.06) cell.margin_top = Inches(0.04) cell.margin_bottom = Inches(0.04) cell.fill.solid() cell.fill.fore_color.rgb = rgb("navy" if r == 0 else ("soft" if r % 2 == 0 else "white")) for p in cell.text_frame.paragraphs: p.font.name = FONT p.font.size = Pt(11.5 if r == 0 else body_size) p.font.bold = bool(r == 0) p.font.color.rgb = rgb("white" if r == 0 else "ink") return table_shape def add_flow(slide, items, x, y, w, h, color="blue"): gap = 0.16 box_w = (w - gap * (len(items) - 1)) / len(items) for i, item in enumerate(items): bx = x + i * (box_w + gap) add_shape(slide, bx, y, box_w, h, "soft", "line") add_text(slide, item, bx + 0.08, y + 0.18, box_w - 0.16, h - 0.2, 11.2, color, True, align=PP_ALIGN.CENTER) def build(): prs = Presentation() prs.slide_width = Inches(WIDE_W) prs.slide_height = Inches(WIDE_H) # 1 slide = add_slide(prs, "平台基础：整体数据库设计", "平台基础") add_image(slide, "er-platform-org.png", 0.72, 1.0, 4.15, 2.48) add_image(slide, "er-platform-rbac.png", 0.72, 3.86, 4.15, 2.22) add_table( slide, [ ["设计对象", "数据库作用"], ["profiles", "auth.users 的业务扩展，一对一保存学号、状态、认证快照"], ["departments + department_closure", "邻接表保存直接父子；闭包表保存祖先-后代可达关系"], ["roles / permissions / user_roles", "角色、权限、多对多授权和数据范围控制"], ["audit_logs", "记录操作者快照、目标对象和差异，作为审计事实表"], ], 5.28, 1.05, 7.08, 3.45, [2.6, 4.48], ) add_note(slide, "课程设计体现：一对一扩展、多对多桥接、层级数据建模、审计事实表、索引与触发器。", 5.28, 5.25, 7.08) # 2 slide = add_slide(prs, "平台基础：部门闭包表与层级查询", "平台基础") add_code( slide, "create table public.department_closure (\n" " ancestor_id uuid not null references departments(id),\n" " descendant_id uuid not null references departments(id),\n" " depth integer not null,\n" " primary key (ancestor_id, descendant_id),\n" " constraint department_closure_depth_chk check (depth >= 0)\n" ");", 0.72, 1.0, 5.88, 2.35, 10.2, ) add_code( slide, "-- 插入新部门时维护闭包路径\n" "insert into department_closure values (new.id, new.id, 0);\n\n" "insert into department_closure (ancestor_id, descendant_id, depth)\n" "select c.ancestor_id, new.id, c.depth + 1\n" "from department_closure c\n" "where c.descendant_id = new.parent_id;", 6.9, 1.0, 5.55, 2.35, 10.2, ) add_table( slide, [ ["亮点", "说明"], ["查询性能", "查询“本部门及所有子部门”时直接按 ancestor_id 命中索引"], ["完整性", "depth >= 0，祖先-后代唯一，避免重复路径"], ["防环", "移动部门前检查新父节点是否已经是自己的后代"], ["维护方式", "insert/update parent_id 由触发器补路径、删旧路径"], ], 0.78, 3.9, 11.65, 2.15, [2.0, 9.65], body_size=10.6, ) # 3 slide = add_slide(prs, "平台基础：RBAC、资料同步与审计约束", "平台基础") add_code( slide, "constraint profiles_student_id_format_chk\n" " check (student_id ~ '^[0-9]{16}$');\n\n" "create table public.user_roles (\n" " user_id uuid not null references auth.users(id),\n" " role_id uuid not null references roles(id),\n" " primary key (user_id, role_id)\n" ");", 0.72, 1.0, 5.88, 3.15, 10.2, ) add_code( slide, "create trigger audit_logs_block_update\n" "before update on public.audit_logs\n" "for each row execute function audit_logs_block_mutation();\n\n" "create trigger audit_logs_block_delete\n" "before delete on public.audit_logs\n" "for each row execute function audit_logs_block_mutation();", 6.9, 1.0, 5.55, 3.15, 10.2, ) add_card(slide, "资料同步", "认证表负责登录，profiles 保存业务查询字段；注册和认证状态变化由触发器同步。", 0.78, 4.55, 3.65, 1.18, "blue") add_card(slide, "多对多授权", "user_roles、role_permissions 用复合主键去重，符合关系模型中联系表设计。", 4.78, 4.55, 3.65, 1.18, "green") add_card(slide, "审计只追加", "audit_logs 禁止更新和删除，保留 actor_roles、diff 等操作快照。", 8.78, 4.55, 3.65, 1.18, "orange") # 4 slide = add_slide(prs, "平台基础：运行验证与实现对应", "平台基础") add_image(slide, "profile-sync.png", 0.72, 1.0, 3.85, 2.45) add_image(slide, "department-tree.png", 4.76, 1.0, 3.85, 2.45) add_image(slide, "role-scope.png", 8.8, 1.0, 3.85, 2.45) add_card(slide, "身份资料", "验证 profiles 与 auth.users 的共享主键、学号格式和用户状态同步。", 0.72, 4.0, 3.85, 1.12, "blue") add_card(slide, "组织树", "验证 departments + department_closure 支撑层级展示和范围查询。", 4.76, 4.0, 3.85, 1.12, "green") add_card(slide, "角色权限", "验证 user_roles / role_permissions / data scopes 支撑权限与数据范围。", 8.8, 4.0, 3.85, 1.12, "orange") add_note(slide, "实现对应：profiles、department_closure、user_roles、role_permissions、role_data_scopes、audit_logs。", 0.82, 5.92, 11.65) # 5 slide = add_slide(prs, "功能房预约：整体数据库设计", "功能房预约") add_image(slide, "er-facility.png", 0.72, 1.0, 5.2, 3.45) add_table( slide, [ ["设计对象", "数据库作用"], ["facility_rooms", "房间资源主体，保存楼房、容量、开放状态、软删除状态"], ["facility_reservations", "预约主表：房间、申请人、时间窗口、审核状态、取消字段"], ["facility_reservation_participants", "参与人联系表，保存参与用户和申请人标记"], ["facility_bans", "封禁表，限制异常用户继续提交预约"], ], 6.25, 1.05, 6.25, 3.5, [2.6, 3.65], ) add_note(slide, "课程设计体现：时间区间约束、多行集合校验、状态一致性约束、事务与并发控制。", 0.72, 5.25, 11.78) # 6 slide = add_slide(prs, "功能房预约：时间冲突与并发控制", "功能房预约") add_code( slide, "constraint facility_reservations_room_active_time_excl\n" " exclude using gist (\n" " room_id with =,\n" " tstzrange(start_at, end_at, '[)') with &&\n" " )\n" " where (status in ('pending', 'approved'))", 0.72, 1.0, 5.88, 2.45, 10.2, ) add_code( slide, "await db.transaction(async (tx) => {\n" " await lockRoomOrThrow(tx, roomId); // select ... for update\n" " await assertNoTimeOverlap({ tx, roomId, startAt, endAt });\n" " const reservationId = await insertReservation(tx);\n" " await insertParticipants(tx, reservationId, participants);\n" "});", 6.9, 1.0, 5.55, 2.45, 10.2, ) add_table( slide, [ ["机制", "作用"], ["服务层预查", "已有 start_at < 新 end_at 且已有 end_at > 新 start_at，即判定重叠"], ["FOR UPDATE", "同一房间预约创建时锁定房间行，降低并发插入竞争"], ["排斥约束", "数据库最终兜底；并发请求绕过预查时仍会被拒绝"], ["[start,end)", "结束点不包含，10:00-11:00 与 11:00-12:00 不冲突"], ], 0.78, 3.95, 11.65, 2.1, [2.1, 9.55], body_size=10.5, ) # 7 slide = add_slide(prs, "功能房预约：参与人集合检查", "功能房预约") add_code( slide, "create table facility_reservation_participants (\n" " reservation_id uuid references facility_reservations(id),\n" " user_id uuid references auth.users(id),\n" " is_applicant boolean not null default false,\n" " primary key (reservation_id, user_id)\n" ");", 0.72, 1.0, 5.88, 2.0, 10.6, ) add_code( slide, "create constraint trigger facility_reservation_participants_consistency_trg\n" "after insert or update or delete on facility_reservation_participants\n" "deferrable initially deferred\n" "for each row execute function facility_validate_reservation_participants();", 6.9, 1.0, 5.55, 2.0, 10.6, ) add_table( slide, [ ["校验规则", "数据库实现"], ["至少 3 人", "count(*) < 3 时抛出 23514"], ["申请人唯一", "count(*) filter (where is_applicant) 必须等于 1"], ["主从一致", "is_applicant=true 的 user_id 必须等于主表 applicant_id"], ["延迟检查", "事务提交阶段检查最终集合，允许先插主表再插参与人"], ], 0.78, 3.55, 11.65, 2.5, [2.15, 9.5], body_size=10.7, ) # 8 slide = add_slide(prs, "功能房预约：审核状态一致性", "功能房预约") add_flow(slide, ["pending\n待审核", "approved\n已通过", "rejected\n已驳回", "cancelled\n已取消"], 0.78, 1.0, 11.65, 0.82, "navy") add_code( slide, "constraint facility_reservations_status_consistency_chk check (\n" " pending -> reviewed_by/reviewed_at/reject/cancel 字段均为空\n" " approved -> reviewed_by、reviewed_at 非空，reject/cancel 字段为空\n" " rejected -> reviewed_by、reviewed_at、reject_reason 非空\n" " cancelled -> cancelled_by、cancelled_at 非空，reject_reason 为空\n" ")", 0.78, 2.25, 6.0, 2.1, 10.2, ) add_table( slide, [ ["状态", "字段一致性要求"], ["pending", "没有审核人、审核时间、驳回原因、取消人、取消时间"], ["approved", "必须有审核人和审核时间，不能有驳回/取消字段"], ["rejected", "必须有审核人、审核时间和驳回原因"], ["cancelled", "必须有取消人和取消时间，并保留已有审核历史"], ], 7.1, 2.25, 5.25, 2.1, [1.45, 3.8], body_size=9.6, ) add_note(slide, "课程设计体现：状态不是孤立枚举，必须用 CHECK 约束把状态和相关时间/人员字段绑定。", 0.78, 5.35, 11.65) # 9 slide = add_slide(prs, "功能房预约：运行验证与实现对应", "功能房预约") add_image(slide, "facility-create.png", 0.72, 1.0, 3.85, 2.45) add_image(slide, "facility-conflict.png", 4.76, 1.0, 3.85, 2.45) add_image(slide, "facility-review.png", 8.8, 1.0, 3.85, 2.45) add_card(slide, "合法预约", "时间轴出现占用条，验证预约主表、房间外键和时间窗口查询。", 0.72, 4.0, 3.85, 1.12, "blue") add_card(slide, "冲突拦截", "重叠时间段被拒绝，验证重叠预查和数据库排斥约束。", 4.76, 4.0, 3.85, 1.12, "red") add_card(slide, "审核流转", "审核操作同步写入状态、审核人、审核时间和驳回/取消字段。", 8.8, 4.0, 3.85, 1.12, "green") add_note(slide, "实现对应：facility_reservations、participants、GiST 排斥约束、延迟触发器、事务与审计记录。", 0.82, 5.92, 11.65) # 10 slide = add_slide(prs, "课程资源分享：整体数据库设计", "课程资源分享") add_image(slide, "er-resource.png", 0.72, 1.0, 5.25, 3.85) add_table( slide, [ ["设计对象", "数据库作用"], ["majors / courses / major_leads", "专业、课程、专业负责人范围"], ["course_resources", "资源主体表：课程、专业、类型、审核状态、发布字段、作者"], ["course_resource_download_events", "下载事实表，支撑下载次数和资源榜"], ["course_resource_score_events", "积分事件事实表，支撑用户积分榜和追溯"], ["course_resource_bests", "最佳资源表，记录推荐人和推荐时间"], ], 6.25, 1.05, 6.25, 3.85, [2.75, 3.5], body_size=9.7, ) add_note(slide, "课程设计体现：教学层级、受控冗余、字段互斥、去重索引、审核状态流转、事件事实表。", 0.72, 5.45, 11.78) # 11 slide = add_slide(prs, "课程资源分享：受控冗余、互斥字段与去重", "课程资源分享") add_code( slide, "constraint course_resources_course_major_fk\n" " foreign key (course_id, major_id)\n" " references public.courses(id, major_id);\n\n" "constraint course_resources_file_or_link_chk check (\n" " file 类型不能混入 link 字段；\n" " link 类型不能混入 file 字段；\n" " 非 draft 必须补全对应资源明细\n" ");", 0.72, 1.0, 5.88, 3.0, 9.4, ) add_code( slide, "create unique index course_resources_course_sha256_active_uq\n" " on course_resources(course_id, sha256)\n" " where deleted_at is null and resource_type = 'file';\n\n" "create unique index course_resources_course_link_active_uq\n" " on course_resources(course_id, link_url_normalized)\n" " where deleted_at is null and resource_type = 'link';", 6.9, 1.0, 5.55, 3.0, 9.4, ) add_table( slide, [ ["亮点", "说明"], ["受控冗余", "course_resources 保留 major_id 方便范围过滤和统计，但用复合外键锁住一致性"], ["字段互斥", "文件型和外链型资源共享一张表，用 CHECK 防止字段组合非法"], ["软删除去重", "唯一索引只约束 deleted_at is null 的有效资源"], ["规范化外链", "link_url_normalized 参与唯一索引，避免同一 URL 不同写法重复提交"], ], 0.78, 4.45, 11.65, 1.75, [2.1, 9.55], body_size=10.0, ) # 12 slide = add_slide(prs, "课程资源分享：审核流程、积分事件与统计事实", "课程资源分享") add_flow(slide, ["draft\n草稿", "pending\n待审核", "published\n已发布", "rejected\n已驳回", "unpublished\n已下架"], 0.78, 0.98, 11.65, 0.82, "navy") add_code( slide, "constraint course_resources_status_consistency_chk check (\n" " draft -> submitted/reviewed/published/unpublished 均为空\n" " pending -> submitted_at 非空，review 字段为空\n" " rejected -> reviewed_at、review_comment 非空\n" " published -> reviewed_at、published_at 非空\n" " unpublished -> published_at、unpublished_at 非空\n" ");", 0.72, 2.1, 5.88, 2.55, 9.6, ) add_code( slide, "await db.transaction(async (tx) => {\n" " await tx.update(courseResources)\n" " .set({ status: 'published', reviewedAt: sql`now()`,\n" " publishedAt: sql`now()` });\n\n" " await tx.insert(courseResourceScoreEvents)\n" " .values({ eventType: 'approve', delta: approveDelta })\n" " .onConflictDoNothing();\n" "});", 6.9, 2.1, 5.55, 2.55, 9.2, ) add_card(slide, "事件事实表", "下载和积分不只存在汇总字段中，保留 download_events / score_events 作为可追溯来源。", 0.78, 5.05, 3.65, 0.95, "blue") add_card(slide, "首次积分语义", "score_events 用 (user_id, resource_id, event_type) 唯一约束避免重复加分。", 4.78, 5.05, 3.65, 0.95, "green") add_card(slide, "最佳资源约束", "最佳推荐单独建表；状态变更触发器保证非 published 资源不能继续保持最佳。", 8.78, 5.05, 3.65, 0.95, "orange") # 13 slide = add_slide(prs, "课程资源分享：运行验证与实现对应", "课程资源分享") add_image(slide, "resource-publish.png", 0.78, 1.0, 5.55, 3.55) add_image(slide, "resource-scoreboard.png", 6.75, 1.0, 5.55, 3.55) add_card(slide, "资源发布详情", "课程、专业、类型、审核时间、发布时间、作者和下载次数可回显。", 0.78, 5.0, 5.55, 0.9, "blue") add_card(slide, "积分与下载榜", "下载事件支撑资源榜，积分事件聚合出用户榜，统计来源可追溯。", 6.75, 5.0, 5.55, 0.9, "green") add_note(slide, "实现对应：course_resources、download_events、score_events、去重索引、状态 CHECK、审核事务。", 0.82, 6.18, 11.65, h=0.45) prs.save(OUTPUT) if __name__ == "__main__": build() print(f"generated: {OUTPUT}")