Skip to content

Create polished demo videos without editing skills. Mac/Windows/Linux

6.3 KB
Raw Blame
// Regression tests for ensureSchema against databases that predate schema
// versioning. The v4.1.0 bug: a production DB with tables but no _meta row
// was treated as "fresh", so CREATE TABLE IF NOT EXISTS no-opped, the v2
// ALTER never ran, and every INSERT touching password_salt threw a 500.
// These tests rebuild the database into each historical shape and verify
// uploads work afterward.
import { describe, it, expect, beforeEach, afterAll } from "vitest";
import { env, SELF } from "cloudflare:test";
import { __resetSchemaCacheForTests } from "../src/index.js";

const AUTH = { Authorization: "Bearer test-secret" };
const BASE = "https://share.test";

// The videos table exactly as production had it before migration 0006
// (base schema + migrations 0002–0005): no password_salt column.
const V1_STATEMENTS = [
	`CREATE TABLE videos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    share_code TEXT UNIQUE NOT NULL,
    title TEXT NOT NULL,
    duration REAL NOT NULL DEFAULT 0,
    width INTEGER NOT NULL DEFAULT 0,
    height INTEGER NOT NULL DEFAULT 0,
    has_webcam INTEGER NOT NULL DEFAULT 0,
    file_size INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    expires_at TEXT NOT NULL,
    upload_completed INTEGER NOT NULL DEFAULT 0,
    view_count INTEGER NOT NULL DEFAULT 0,
    password_hash TEXT,
    cta_url TEXT,
    cta_text TEXT,
    last_notified_view_count INTEGER NOT NULL DEFAULT 0,
    is_meeting INTEGER NOT NULL DEFAULT 0,
    summary TEXT
  )`,
	`CREATE TABLE transcript_segments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    video_id INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
    start_time REAL NOT NULL,
    end_time REAL NOT NULL,
    text TEXT NOT NULL,
    speaker TEXT
  )`,
	`CREATE TABLE reactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    video_id INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
    timestamp REAL NOT NULL,
    emoji TEXT NOT NULL,
    client_ip TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  )`,
	`CREATE TABLE comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    video_id INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
    timestamp REAL NOT NULL,
    author_name TEXT NOT NULL DEFAULT 'Anonymous',
    text TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    client_ip TEXT NOT NULL DEFAULT ''
  )`,
	`CREATE TABLE chapters (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    video_id INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
    timestamp REAL NOT NULL,
    title TEXT NOT NULL
  )`,
];

async function dropAllTables() {
	const { results } = await env.DB.prepare(
		`SELECT name FROM sqlite_master WHERE type = 'table'
     AND name NOT LIKE 'sqlite\\_%' ESCAPE '\\' AND name NOT LIKE '\\_cf\\_%' ESCAPE '\\'`,
	).all();
	for (const { name } of results) {
		await env.DB.prepare(`DROP TABLE IF EXISTS "${name}"`).run();
	}
}

async function createV1Schema() {
	for (const sql of V1_STATEMENTS) {
		await env.DB.prepare(sql).run();
	}
}

async function videoColumns() {
	const { results } = await env.DB.prepare(`PRAGMA table_info(videos)`).all();
	return results.map((c) => c.name);
}

async function upload() {
	return SELF.fetch(`${BASE}/api/upload`, {
		method: "POST",
		headers: { ...AUTH, "Content-Type": "application/json" },
		body: JSON.stringify({ title: "Migration Test", duration: 3 }),
	});
}

describe("schema migration paths", () => {
	beforeEach(async () => {
		await dropAllTables();
		__resetSchemaCacheForTests();
	});

	// Leave a clean, fully-migrated database for any test file that runs after.
	afterAll(async () => {
		await dropAllTables();
		__resetSchemaCacheForTests();
	});

	it("migrates a pre-versioning database (tables exist, no _meta)", async () => {
		await createV1Schema();

		const res = await upload();
		expect(res.status).toBe(200);
		const body = await res.json();
		expect(body.shareCode).toBeTruthy();

		expect(await videoColumns()).toContain("password_salt");
		const meta = await env.DB.prepare(
			`SELECT value FROM _meta WHERE key = 'schema_version'`,
		).first();
		expect(meta.value).toBe("5");
		expect(await videoColumns()).toContain("owner_id");
	});

	it("repairs a database mis-stamped as current without the v2 columns", async () => {
		// The exact state v4.1.0 left production in: v1 tables, _meta says 2.
		await createV1Schema();
		await env.DB.prepare(`CREATE TABLE _meta (key TEXT PRIMARY KEY, value TEXT)`).run();
		await env.DB.prepare(`INSERT INTO _meta (key, value) VALUES ('schema_version', '2')`).run();

		const res = await upload();
		expect(res.status).toBe(200);

		expect(await videoColumns()).toContain("password_salt");
	});

	it("re-running migrations on an already-migrated database is a no-op", async () => {
		await createV1Schema();
		expect((await upload()).status).toBe(200); // migrates

		__resetSchemaCacheForTests(); // simulate a new isolate cold start
		expect((await upload()).status).toBe(200); // must not throw duplicate-column
	});

	it("bootstraps a truly fresh database with the consolidated schema", async () => {
		const res = await upload();
		expect(res.status).toBe(200);

		const cols = await videoColumns();
		expect(cols).toContain("password_salt");
		const tables = await env.DB.prepare(
			`SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'password_attempts'`,
		).first();
		expect(tables).toBeTruthy();
		expect(
			await env.DB.prepare(
				`SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'comment_users'`,
			).first(),
		).toBeTruthy();
	});

	it("password flow works end-to-end on a migrated pre-versioning database", async () => {
		await createV1Schema();

		const hash = "2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824"; // sha256("hello")
		const res = await SELF.fetch(`${BASE}/api/upload`, {
			method: "POST",
			headers: { ...AUTH, "Content-Type": "application/json" },
			body: JSON.stringify({ title: "Protected", duration: 3, password_hash: hash }),
		});
		expect(res.status).toBe(200);
		const { shareCode } = await res.json();
		await env.DB.prepare(`UPDATE videos SET upload_completed = 1 WHERE share_code = ?`)
			.bind(shareCode)
			.run();

		const verify = await SELF.fetch(`${BASE}/s/${shareCode}/verify-password`, {
			method: "POST",
			headers: { "Content-Type": "application/json" },
			body: JSON.stringify({ password: "hello" }),
		});
		expect(verify.status).toBe(200);
	});
});