1import { describe, expect, it } from 'vitest';2import { SWEEP_SQL } from '../src/sweep';34/**5 * The sweep runs raw SQL in a D1 batch; these tests pin the semantics of each6 * statement without needing a database.7 */8describe('sweep SQL', () => {9 it('deletes only expired AND unstarred scripts', () => {10 const sql = SWEEP_SQL.expiredScripts;11 expect(sql).toContain('DELETE FROM scripts');12 expect(sql).toContain('expires_at IS NOT NULL AND expires_at < ?1');13 expect(sql).toContain("NOT EXISTS (SELECT 1 FROM stars WHERE stars.kind = 'script'");14 });1516 it('purges trashed scripts by deleted_at cutoff, ignoring stars', () => {17 const sql = SWEEP_SQL.trashPurge;18 expect(sql).toContain('DELETE FROM scripts');19 expect(sql).toContain('deleted_at IS NOT NULL AND deleted_at < ?1');20 expect(sql).not.toContain('stars'); // owner asked: stars do not protect trash21 });2223 it('deletes only expired AND unstarred sims', () => {24 const sql = SWEEP_SQL.expiredSims;25 expect(sql).toContain('DELETE FROM sims');26 expect(sql).toContain('expires_at IS NOT NULL AND expires_at < ?1');27 expect(sql).toContain("NOT EXISTS (SELECT 1 FROM stars WHERE stars.kind = 'sim'");28 });2930 it('retains expired public sims while any participant is still LISTED', () => {31 const sql = SWEEP_SQL.expiredSims;32 expect(sql).toContain("NOT (sims.visibility = 'public' AND EXISTS");33 expect(sql).toContain('sim_scripts ss JOIN scripts sc ON sc.id = ss.script_id');34 // The full LISTED predicate must gate the retention.35 expect(sql).toContain("sc.status = 'validated'");36 expect(sql).toContain('sc.deleted_at IS NULL');37 expect(sql).toContain('sc.linked_script_id IS NULL');38 expect(sql).toContain('lu.public_sharing = 1');39 expect(sql).toContain("ls.kind = 'script' AND ls.target_id = sc.id AND ls.user_id = sc.owner_id");40 });4142 it('removes stars whose target no longer exists, for both kinds', () => {43 const sql = SWEEP_SQL.orphanStars;44 expect(sql).toContain('DELETE FROM stars');45 expect(sql).toContain("kind = 'script' AND NOT EXISTS (SELECT 1 FROM scripts");46 expect(sql).toContain("kind = 'sim' AND NOT EXISTS (SELECT 1 FROM sims");47 });4849 it('keeps blobs referenced by scripts, script_versions, or sim_scripts', () => {50 const sql = SWEEP_SQL.orphanBlobs;51 expect(sql).toContain('DELETE FROM blobs');52 for (const table of ['scripts', 'script_versions', 'sim_scripts']) {53 expect(sql).toContain(`NOT EXISTS (SELECT 1 FROM ${table} WHERE ${table}.hash = blobs.hash)`);54 }55 });5657 it('statements pin exact shape (regression snapshot)', () => {58 expect(SWEEP_SQL).toMatchInlineSnapshot(`59 {60 "expiredScripts": "DELETE FROM scripts61 WHERE expires_at IS NOT NULL AND expires_at < ?162 AND NOT EXISTS (SELECT 1 FROM stars WHERE stars.kind = 'script' AND stars.target_id = scripts.id)",63 "expiredSims": "DELETE FROM sims64 WHERE expires_at IS NOT NULL AND expires_at < ?165 AND NOT EXISTS (SELECT 1 FROM stars WHERE stars.kind = 'sim' AND stars.target_id = sims.id)66 AND NOT (sims.visibility = 'public' AND EXISTS (67 SELECT 1 FROM sim_scripts ss JOIN scripts sc ON sc.id = ss.script_id68 WHERE ss.sim_id = sims.id AND (sc.status = 'validated'69 AND sc.deleted_at IS NULL70 AND sc.linked_script_id IS NULL71 AND EXISTS (SELECT 1 FROM users lu WHERE lu.id = sc.owner_id AND lu.public_sharing = 1)72 AND EXISTS (SELECT 1 FROM stars ls WHERE ls.kind = 'script' AND ls.target_id = sc.id AND ls.user_id = sc.owner_id))))",73 "orphanBlobs": "DELETE FROM blobs74 WHERE NOT EXISTS (SELECT 1 FROM scripts WHERE scripts.hash = blobs.hash)75 AND NOT EXISTS (SELECT 1 FROM script_versions WHERE script_versions.hash = blobs.hash)76 AND NOT EXISTS (SELECT 1 FROM sim_scripts WHERE sim_scripts.hash = blobs.hash)",77 "orphanStars": "DELETE FROM stars78 WHERE (kind = 'script' AND NOT EXISTS (SELECT 1 FROM scripts WHERE scripts.id = stars.target_id))79 OR (kind = 'sim' AND NOT EXISTS (SELECT 1 FROM sims WHERE sims.id = stars.target_id))",80 "trashPurge": "DELETE FROM scripts81 WHERE deleted_at IS NOT NULL AND deleted_at < ?1",82 }83 `);84 });85});