Mercurial > hgrepos > hgweb.cgi > s4
comparison 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 |
comparison
equal
deleted
inserted
replaced
1005:c2fc7a148d2c | 1006:4bc9494c00ff |
---|---|
1 -- 親のないblogを探す | |
2 | |
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); | |
8 | |
9 SELECT * FROM orphanedblog; | |
10 | |
11 SELECT id FROM orphanedblog; | |
12 | |
13 -- 削除処理 | |
14 SELECT count(*) FROM article; | |
15 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog); | |
16 SELECT count(*) FROM article; | |
17 | |
18 -- 親のないarticleを探す これがFOREIGN_KEYしてなかった!!! | |
19 | |
20 CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS | |
21 SELECT id FROM article | |
22 WHERE blogid NOT IN (SELECT id FROM blog); | |
23 | |
24 SELECT count(*) FROM orphanedarticle; | |
25 | |
26 SELECT id FROM orphanedarticle; | |
27 | |
28 -- 削除処理 | |
29 SELECT count(*) FROM article; | |
30 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle); | |
31 SELECT count(*) FROM article; |