""" Foreign keys are enforced (v2.11.0) SQLite leaves foreign keys off per connection, so ON DELETE CASCADE never ran and hard deletes left tag, media and variant rows behind. """ import io import sqlite3 from pathlib import Path from PIL import Image from starpunk.database import get_db from starpunk.migrations import execute_in_transaction def png(): buf = io.BytesIO() Image.new("RGB", (30, 20), "red").save(buf, format="PNG") return buf.getvalue() def count(app, sql): return sqlite3.connect(app.config["DATABASE_PATH"]).execute(sql).fetchone()[0] def test_pooled_connections_enforce_foreign_keys(app): with app.app_context(): assert get_db(app).execute("PRAGMA foreign_keys").fetchone()[0] == 1 def test_hard_delete_removes_tags_and_media_links(app): from starpunk.media import attach_media_to_note, save_media from starpunk.notes import create_note, delete_note with app.test_request_context(): note = create_note("Tagged with a photo", published=True, tags=["one", "two"], has_media=True) media = save_media(png(), "a.png") attach_media_to_note(note.id, [media["id"]], [""]) delete_note(id=note.id, soft=False) assert count(app, "SELECT COUNT(*) FROM note_tags") == 0 assert count(app, "SELECT COUNT(*) FROM note_media") == 0 def test_deleting_media_removes_variants(app): from starpunk.media import delete_media, save_media with app.test_request_context(): media = save_media(png(), "b.png") delete_media(media["id"]) assert count(app, "SELECT COUNT(*) FROM media_variants") == 0 def test_migration_018_removes_existing_orphans(app): conn = sqlite3.connect(app.config["DATABASE_PATH"]) # foreign keys off here conn.execute("INSERT INTO tags (name, display_name) VALUES ('t', 't')") conn.execute("INSERT INTO note_tags (note_id, tag_id) VALUES (999, 1)") conn.execute("INSERT INTO webmentions (source_url, target_url, target_note_id) " "VALUES ('https://s.example/', 'http://localhost:5000/note/x', 999)") conn.commit() sql = (Path(__file__).parent.parent / "migrations" / "018_foreign_key_cleanup.sql").read_text() conn.execute("BEGIN") execute_in_transaction(conn, sql) conn.commit() assert conn.execute("SELECT COUNT(*) FROM note_tags").fetchone()[0] == 0 assert conn.execute("SELECT target_note_id FROM webmentions").fetchone()[0] is None assert conn.execute("PRAGMA foreign_key_check").fetchall() == []