--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/scripts/clean-orphaned.sql	Sun Mar 05 15:12:17 2023 +0859
@@ -0,0 +1,31 @@
+-- 親のないblogを探す
+
+CREATE TEMPORARY VIEW IF NOT EXISTS orphanedblog AS
+  SELECT blog.id,val FROM blog JOIN blog_s bs
+            ON blog.id=bs.id AND key='owner'
+            WHERE val NOT IN (SELECT gname FROM grp)
+              AND val NOT IN (SELECT name FROM user);
+
+SELECT * FROM orphanedblog;
+
+SELECT id FROM orphanedblog;
+
+-- 削除処理
+SELECT count(*) FROM article;
+DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);
+SELECT count(*) FROM article;
+
+-- 親のないarticleを探す これがFOREIGN_KEYしてなかった!!!
+
+CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS
+  SELECT id FROM article
+  WHERE  blogid NOT IN (SELECT id FROM blog);
+
+SELECT count(*) FROM orphanedarticle;
+
+SELECT id FROM orphanedarticle;
+
+-- 削除処理
+SELECT count(*) FROM article;
+DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);
+SELECT count(*) FROM article;