# HG changeset patch
# User HIROSE Yuuji <yuuji@gentei.org>
# Date 1591428803 -32400
# Node ID 262983fbc2ac8e4e72c580ba17f673e01ca3cdd0
# Parent  52e74eb05b7eba39a3f1828637aba564531dee53
Clean-up code to remove orphaned blogs and articles

diff -r 52e74eb05b7e -r 262983fbc2ac s4-funcs.sh
--- a/s4-funcs.sh	Sat Jun 06 16:32:45 2020 +0900
+++ b/s4-funcs.sh	Sat Jun 06 16:33:23 2020 +0900
@@ -3678,6 +3678,27 @@
   [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"
 }
 
+clean-orphaned() {
+  # This shoud be done by foreign_key rules, but some db lack them
+  query<<-EOF
+	-- Find blogs that have no parent
+	CREATE TEMPORARY VIEW IF NOT EXISTS orphanedblog AS
+	SELECT blog.id,val FROM blog JOIN blog_s bs
+	       ON blog.id=bs.id AND key='owner'
+            WHERE val NOT IN (SELECT gname FROM grp)
+              AND val NOT IN (SELECT name FROM user);
+	-- Remove them
+	DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);
+
+	-- Find articles that have no parent blog
+	CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS
+	SELECT id FROM article
+	WHERE  blogid NOT IN (SELECT id FROM blog);
+
+	-- Remove them
+	DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);
+	EOF
+}
 par2table() (
   # copy current parameters of par into destination table
   # $1=definition-file
@@ -3732,6 +3753,9 @@
     if [ x"$rm" = x"yes" ]; then
       if [ x"$rm$cfm" = x"yesyes" ]; then
 	query "delete from $tbl where rowid=$rowid;"
+	if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then
+	  clean-orphaned
+	fi
 	return 4
       else
 	echo "消去確認のチェックがないので消さなかったの..." | html p