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; |
