| 1 | import { describe, expect, it } from "vitest"; |
| 2 | // @ts-expect-error Node types are intentionally not part of the Worker build. |
| 3 | import { DatabaseSync } from "node:sqlite"; |
| 4 | import schema from "../schema.sql?raw"; |
| 5 | import migration from "../migrate-performance-indexes.sql?raw"; |
| 6 | |
| 7 | describe("forum performance indexes", () => { |
| 8 | it("keeps fresh installs and the additive migration aligned", () => { |
| 9 | for (const sql of [schema, migration]) { |
| 10 | expect(sql).toMatch(/CREATE INDEX IF NOT EXISTS posts_author_created_at\s+ON posts \(author, created_at\)/); |
| 11 | expect(sql).toMatch(/CREATE INDEX IF NOT EXISTS posts_visible_topic\s+ON posts \(topic_id, created_at\)\s+WHERE status = 'visible'/); |
| 12 | expect(sql).toMatch(/CREATE INDEX IF NOT EXISTS topics_visible_latest\s+ON topics \(pinned DESC, last_post_at DESC\)\s+WHERE status <> 'hidden'/); |
| 13 | expect(sql).toMatch(/CREATE INDEX IF NOT EXISTS topics_visible_top\s+ON topics \(reply_count DESC, last_post_at DESC\)\s+WHERE status <> 'hidden'/); |
| 14 | } |
| 15 | }); |
| 16 | |
| 17 | it("keeps the migration additive and idempotent", () => { |
| 18 | expect(migration).not.toMatch(/\b(?:DROP|ALTER)\b/); |
| 19 | expect(migration.match(/CREATE INDEX IF NOT EXISTS/g)).toHaveLength(4); |
| 20 | }); |
| 21 | |
| 22 | it("removes full scans from the hot topic and post-count queries", () => { |
| 23 | const db = new DatabaseSync(":memory:"); |
| 24 | try { |
| 25 | db.exec(schema); |
| 26 | db.exec(migration); |
| 27 | const postsPlan = db.prepare( |
| 28 | "EXPLAIN QUERY PLAN SELECT COUNT(*) FROM posts WHERE author = 'alice' AND created_at > '2026-01-01'", |
| 29 | ).all().map((row: Record<string, unknown>) => String(row.detail)).join(" "); |
| 30 | const topicsPlan = db.prepare( |
| 31 | "EXPLAIN QUERY PLAN SELECT id FROM topics WHERE status <> 'hidden' ORDER BY pinned DESC, last_post_at DESC LIMIT 50", |
| 32 | ).all().map((row: Record<string, unknown>) => String(row.detail)).join(" "); |
| 33 | expect(postsPlan).toContain("USING COVERING INDEX posts_author_created_at"); |
| 34 | expect(topicsPlan).toContain("USING INDEX topics_visible_latest"); |
| 35 | expect(topicsPlan).not.toContain("USE TEMP B-TREE FOR ORDER BY"); |
| 36 | } finally { |
| 37 | db.close(); |
| 38 | } |
| 39 | }); |
| 40 | }); |
| 41 |