s4: scripts/clean-orphaned.sql annotate

s4

Add clean-orphaned.sql migrate-grp.sh

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sun, 05 Mar 2023 15:12:17 +0859
parents
children

rev	line source
yuuji@1006	1 -- 親のないblogを探す
yuuji@1006	2
yuuji@1006	3 CREATE TEMPORARY VIEW IF NOT EXISTS orphanedblog AS
yuuji@1006	4 SELECT blog.id,val FROM blog JOIN blog_s bs
yuuji@1006	5 ON blog.id=bs.id AND key='owner'
yuuji@1006	6 WHERE val NOT IN (SELECT gname FROM grp)
yuuji@1006	7 AND val NOT IN (SELECT name FROM user);
yuuji@1006	8
yuuji@1006	9 SELECT * FROM orphanedblog;
yuuji@1006	10
yuuji@1006	11 SELECT id FROM orphanedblog;
yuuji@1006	12
yuuji@1006	13 -- 削除処理
yuuji@1006	14 SELECT count(*) FROM article;
yuuji@1006	15 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);
yuuji@1006	16 SELECT count(*) FROM article;
yuuji@1006	17
yuuji@1006	18 -- 親のないarticleを探すこれがFOREIGN_KEYしてなかった!!!
yuuji@1006	19
yuuji@1006	20 CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS
yuuji@1006	21 SELECT id FROM article
yuuji@1006	22 WHERE blogid NOT IN (SELECT id FROM blog);
yuuji@1006	23
yuuji@1006	24 SELECT count(*) FROM orphanedarticle;
yuuji@1006	25
yuuji@1006	26 SELECT id FROM orphanedarticle;
yuuji@1006	27
yuuji@1006	28 -- 削除処理
yuuji@1006	29 SELECT count(*) FROM article;
yuuji@1006	30 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);
yuuji@1006	31 SELECT count(*) FROM article;