s4: 4bc9494c00ff scripts/clean-orphaned.sql

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

line source

1 -- 親のないblogを探す

3 CREATE TEMPORARY VIEW IF NOT EXISTS orphanedblog AS

4 SELECT blog.id,val FROM blog JOIN blog_s bs

5 ON blog.id=bs.id AND key='owner'

6 WHERE val NOT IN (SELECT gname FROM grp)

7 AND val NOT IN (SELECT name FROM user);

9 SELECT * FROM orphanedblog;

11 SELECT id FROM orphanedblog;

13 -- 削除処理

14 SELECT count(*) FROM article;

15 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

16 SELECT count(*) FROM article;

18 -- 親のないarticleを探すこれがFOREIGN_KEYしてなかった!!!

20 CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS

21 SELECT id FROM article

22 WHERE blogid NOT IN (SELECT id FROM blog);

24 SELECT count(*) FROM orphanedarticle;

26 SELECT id FROM orphanedarticle;

28 -- 削除処理

29 SELECT count(*) FROM article;

30 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

31 SELECT count(*) FROM article;