Mercurial > hgrepos > hgweb.cgi > s4
diff scripts/clean-orphaned.sql @ 1006:4bc9494c00ff draft
Add clean-orphaned.sql migrate-grp.sh
author | HIROSE Yuuji <yuuji@gentei.org> |
---|---|
date | Sun, 05 Mar 2023 15:12:17 +0859 |
parents | |
children |
line wrap: on
line diff
--- /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;