Skip to content

Instantly share code, notes, and snippets.

@ThiefMaster
Last active September 24, 2015 12:51
Show Gist options
  • Save ThiefMaster/e5fb1e642aa149371688 to your computer and use it in GitHub Desktop.
Save ThiefMaster/e5fb1e642aa149371688 to your computer and use it in GitHub Desktop.
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment