delete_orphaned.sql

CREATE TEMP TABLE orphaned_note_ids ON COMMIT DROP AS
    (SELECT id FROM events.notes x WHERE x.event_id IS NOT NULL and NOT EXISTS
        (SELECT 1 FROM events.events WHERE id = x.event_id));


UPDATE events.notes SET current_revision_id = NULL WHERE id IN (SELECT id FROM orphaned_note_ids);
DELETE FROM events.note_revisions WHERE note_id IN (SELECT id FROM orphaned_note_ids);
DELETE FROM events.notes WHERE id IN (SELECT id FROM orphaned_note_ids);



CREATE TEMP TABLE orphaned_folder_ids ON COMMIT DROP AS
    (SELECT id FROM attachments.folders x WHERE x.event_id IS NOT NULL and NOT EXISTS
        (SELECT 1 FROM events.events WHERE id = x.event_id));
CREATE TEMP TABLE orphaned_attachment_ids ON COMMIT DROP AS
    (SELECT id FROM attachments.attachments WHERE folder_id IN (SELECT id FROM orphaned_folder_ids));


UPDATE attachments.attachments SET file_id = NULL WHERE id IN (SELECT id FROM orphaned_attachment_ids);
DELETE FROM attachments.files WHERE attachment_id IN (SELECT id FROM orphaned_attachment_ids);
DELETE FROM attachments.legacy_attachment_id_map WHERE attachment_id IN (SELECT id FROM orphaned_attachment_ids);
DELETE FROM attachments.attachment_principals WHERE attachment_id IN (SELECT id FROM orphaned_attachment_ids);
DELETE FROM attachments.attachments WHERE id IN (SELECT id FROM orphaned_attachment_ids);
DELETE FROM attachments.legacy_folder_id_map WHERE folder_id IN (SELECT id FROM orphaned_folder_ids);
DELETE FROM attachments.folder_principals WHERE folder_id IN (SELECT id FROM orphaned_folder_ids);
DELETE FROM attachments.folders WHERE id IN (SELECT id FROM orphaned_folder_ids);