Mercurial > hgrepos > hgweb.cgi > s4

annotate scripts/clean-orphaned.sql @ 1006:4bc9494c00ff draft

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add clean-orphaned.sql migrate-grp.sh
author HIROSE Yuuji <yuuji@gentei.org>
date Sun, 05 Mar 2023 15:12:17 +0859
parents
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1006
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
1 -- 親のないblogを探す
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
2
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
3 CREATE TEMPORARY VIEW IF NOT EXISTS orphanedblog AS
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
4 SELECT blog.id,val FROM blog JOIN blog_s bs
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
5 ON blog.id=bs.id AND key='owner'
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
6 WHERE val NOT IN (SELECT gname FROM grp)
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
7 AND val NOT IN (SELECT name FROM user);
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
8
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
9 SELECT * FROM orphanedblog;
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
10
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
11 SELECT id FROM orphanedblog;
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
12
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
13 -- 削除処理
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
14 SELECT count(*) FROM article;
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
15 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
16 SELECT count(*) FROM article;
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
17
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
18 -- 親のないarticleを探す これがFOREIGN_KEYしてなかった!!!
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
19
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
20 CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
21 SELECT id FROM article
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
22 WHERE blogid NOT IN (SELECT id FROM blog);
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
23
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
24 SELECT count(*) FROM orphanedarticle;
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
25
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
26 SELECT id FROM orphanedarticle;
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
27
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
28 -- 削除処理
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
29 SELECT count(*) FROM article;
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
30 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);
4bc9494c00ff Add clean-orphaned.sql migrate-grp.sh
HIROSE Yuuji <yuuji@gentei.org>
parents:
diff changeset
31 SELECT count(*) FROM article;

yatex.org