1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/scripts/clean-orphaned.sql	Sun Mar 05 15:12:17 2023 +0859
     1.3 @@ -0,0 +1,31 @@
     1.4 +-- 親のないblogを探す
     1.5 +
     1.6 +CREATE TEMPORARY VIEW IF NOT EXISTS orphanedblog AS
     1.7 +  SELECT blog.id,val FROM blog JOIN blog_s bs
     1.8 +            ON blog.id=bs.id AND key='owner'
     1.9 +            WHERE val NOT IN (SELECT gname FROM grp)
    1.10 +              AND val NOT IN (SELECT name FROM user);
    1.11 +
    1.12 +SELECT * FROM orphanedblog;
    1.13 +
    1.14 +SELECT id FROM orphanedblog;
    1.15 +
    1.16 +-- 削除処理
    1.17 +SELECT count(*) FROM article;
    1.18 +DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);
    1.19 +SELECT count(*) FROM article;
    1.20 +
    1.21 +-- 親のないarticleを探す これがFOREIGN_KEYしてなかった!!!
    1.22 +
    1.23 +CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS
    1.24 +  SELECT id FROM article
    1.25 +  WHERE  blogid NOT IN (SELECT id FROM blog);
    1.26 +
    1.27 +SELECT count(*) FROM orphanedarticle;
    1.28 +
    1.29 +SELECT id FROM orphanedarticle;
    1.30 +
    1.31 +-- 削除処理
    1.32 +SELECT count(*) FROM article;
    1.33 +DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);
    1.34 +SELECT count(*) FROM article;