Patch SQL for #3562 "Add all missing foreign keys"

-- Patch SQL for migration by #3562 "Add all missing foreign keys" https://github.com/tootsuite/mastodon/commit/4c06d1cb241020f2407a6be01b584c508a06ffe9
-- You may need additional SQL for your environment. This SQL works for me.

-- statuses.in_reply_to の statuses が無い
START TRANSACTION;
-- 件数確認 / check count
SELECT COUNT(*)
FROM statuses s
LEFT JOIN statuses rs ON rs.id = s.in_reply_to_id
WHERE s.in_reply_to_id IS NOT NULL
AND   rs.id IS NULL;
-- on_delete: nullify
UPDATE statuses SET in_reply_to_id = NULL
FROM statuses s
LEFT JOIN statuses rs ON rs.id = s.in_reply_to_id
WHERE statuses.id = s.id
AND   s.in_reply_to_id IS NOT NULL
AND   rs.id IS NULL;
-- 更新後件数確認(0件のはず) / re-check count(must be zero.)
SELECT COUNT(*)
FROM statuses s
LEFT JOIN statuses rs ON rs.id = s.in_reply_to_id
WHERE s.in_reply_to_id IS NOT NULL
AND   rs.id IS NULL;

COMMIT;

-- follow_requests.target_account_id の accounts が無い
START TRANSACTION;
-- 件数確認 / check count
SELECT COUNT(*)
FROM follow_requests fr
LEFT JOIN accounts a ON a.id = fr.target_account_id
WHERE fr.target_account_id IS NOT NULL
AND   a.id IS NULL;
-- on_delete: cascade
DELETE FROM follow_requests WHERE id IN (
  SELECT fr.id
  FROM follow_requests fr
  LEFT JOIN accounts a ON a.id = fr.target_account_id
  WHERE fr.target_account_id IS NOT NULL
  AND   a.id IS NULL
);
-- 更新後件数確認(0件のはず) / re-check count(must be zero.)
SELECT COUNT(*)
FROM follow_requests fr
LEFT JOIN accounts a ON a.id = fr.target_account_id
WHERE fr.target_account_id IS NOT NULL
AND   a.id IS NULL;

COMMIT;

-- media_attachments.status_id の statuses が無い
START TRANSACTION;
-- 件数確認 / check count
SELECT COUNT(*)
FROM media_attachments ma
LEFT JOIN statuses s ON s.id = ma.status_id
WHERE ma.status_id IS NOT NULL
AND   s.id IS NULL;
-- on_delete: nullify
UPDATE media_attachments SET status_id = NULL
FROM media_attachments ma
LEFT JOIN statuses s ON s.id = ma.status_id
WHERE media_attachments.id = ma.id
AND   ma.status_id IS NOT NULL
AND   s.id IS NULL;
-- 更新後件数確認(0件のはず) / re-check count(must be zero.)
SELECT COUNT(*)
FROM media_attachments ma
LEFT JOIN statuses s ON s.id = ma.status_id
WHERE ma.status_id IS NOT NULL
AND   s.id IS NULL;

COMMIT;

-- mentions.status_id の statuses が無い
START TRANSACTION;
-- 件数確認 / check count
SELECT COUNT(*)
FROM mentions m
LEFT JOIN statuses s ON s.id = m.status_id
WHERE m.status_id IS NOT NULL
AND   s.id IS NULL;
-- on_delete: cascade
DELETE FROM mentions WHERE id IN (
  SELECT m.id
  FROM mentions m
  LEFT JOIN statuses s ON s.id = m.status_id
  WHERE m.status_id IS NOT NULL
  AND   s.id IS NULL
);
-- 更新後件数確認(0件のはず) / re-check count(must be zero.)
SELECT COUNT(*)
FROM mentions m
LEFT JOIN statuses s ON s.id = m.status_id
WHERE m.status_id IS NOT NULL
AND   s.id IS NULL;

COMMIT;

-- preview_cards.status_id の statuses が無い
START TRANSACTION;
-- 件数確認 / check count
SELECT COUNT(*)
FROM preview_cards pc
LEFT JOIN statuses s ON s.id = pc.status_id
WHERE pc.status_id IS NOT NULL
AND   s.id IS NULL;
-- on_delete: cascade
DELETE FROM preview_cards WHERE id IN (
  SELECT pc.id
  FROM preview_cards pc
  LEFT JOIN statuses s ON s.id = pc.status_id
  WHERE pc.status_id IS NOT NULL
  AND   s.id IS NULL
);
-- 更新後件数確認(0件のはず) / re-check count(must be zero.)
SELECT COUNT(*)
FROM preview_cards pc
LEFT JOIN statuses s ON s.id = pc.status_id
WHERE pc.status_id IS NOT NULL
AND   s.id IS NULL;

COMMIT;

-- web_settings.user_id の users が無い
START TRANSACTION;
-- 件数確認 / check count
SELECT COUNT(*)
FROM web_settings ws
LEFT JOIN users u ON u.id = ws.user_id
WHERE ws.user_id IS NOT NULL
AND   u.id IS NULL;
-- on_delete: cascade
DELETE FROM web_settings WHERE id IN (
  SELECT ws.id
  FROM web_settings ws
  LEFT JOIN users u ON u.id = ws.user_id
  WHERE ws.user_id IS NOT NULL
  AND   u.id IS NULL
);
-- 更新後件数確認(0件のはず) / re-check count(must be zero.)
SELECT COUNT(*)
FROM web_settings ws
LEFT JOIN users u ON u.id = ws.user_id
WHERE ws.user_id IS NOT NULL
AND   u.id IS NULL;

COMMIT;

-- oauth_access_tokens.resource_owner_id の users が無い
START TRANSACTION;
-- 件数確認 / check count
SELECT COUNT(*)
FROM oauth_access_tokens o
LEFT JOIN users u ON u.id = o.resource_owner_id
WHERE o.resource_owner_id IS NOT NULL
AND   u.id IS NULL;
-- on_delete: cascade
DELETE FROM oauth_access_tokens WHERE id IN (
  SELECT o.id
  FROM oauth_access_tokens o
  LEFT JOIN users u ON u.id = o.resource_owner_id
  WHERE o.resource_owner_id IS NOT NULL
  AND   u.id IS NULL
);
-- 更新後件数確認(0件のはず) / re-check count(must be zero.)
SELECT COUNT(*)
FROM oauth_access_tokens o
LEFT JOIN users u ON u.id = o.resource_owner_id
WHERE o.resource_owner_id IS NOT NULL
AND   u.id IS NULL;

COMMIT;