# HG changeset patch
# User HIROSE Yuuji <yuuji@gentei.org>
# Date 1677996764 -32373
# Node ID 4bc9494c00ff34e367d186ff4c18c3a164b500cd
# Parent  c2fc7a148d2c7aeafdc6c49d487042589c4b0eae
Add clean-orphaned.sql migrate-grp.sh

diff -r c2fc7a148d2c -r 4bc9494c00ff scripts/clean-orphaned.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/scripts/clean-orphaned.sql	Sun Mar 05 15:12:17 2023 +0859
@@ -0,0 +1,31 @@
+-- 親のないblogを探す
+
+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);
+
+SELECT * FROM orphanedblog;
+
+SELECT id FROM orphanedblog;
+
+-- 削除処理
+SELECT count(*) FROM article;
+DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);
+SELECT count(*) FROM article;
+
+-- 親のないarticleを探す これがFOREIGN_KEYしてなかった!!!
+
+CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS
+  SELECT id FROM article
+  WHERE  blogid NOT IN (SELECT id FROM blog);
+
+SELECT count(*) FROM orphanedarticle;
+
+SELECT id FROM orphanedarticle;
+
+-- 削除処理
+SELECT count(*) FROM article;
+DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);
+SELECT count(*) FROM article;
diff -r c2fc7a148d2c -r 4bc9494c00ff scripts/migrate-grp.sh
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/scripts/migrate-grp.sh	Sun Mar 05 15:12:17 2023 +0859
@@ -0,0 +1,98 @@
+#!/bin/sh
+# ./migrate-grp src-conf dst-conf GrpIDs
+# eg) ./sitemoving.rb s4-config.sh s4-world-second.sh 8
+
+[ -f $1 ] && . ./$1
+[ -f $2 ] && . ./$2
+. `dirname $1`/s4-funcs.sh
+
+dstdb=$DB
+
+echo db=$db dstdb=$dstdb masterdb=$S4MASTERDB URL=$URL M_URL=$S4MASTERURL
+
+shift 2
+
+query "ATTACH DATABASE \"$S4MASTERDB\" AS src;" || abort "Cannot attach db #{dstdb}"
+for grid; do
+  grid=$((0 + $grid))
+  echo $grid
+  gnamesql="(SELECT gname FROM src.grp WHERE rowid=$grid)"
+  echo "Copying $grid..."
+  query "BEGIN;"
+  query "REPLACE INTO main.grp SELECT * FROM src.grp WHERE rowid=$grid;"
+  for tbl in grp_s grp_m grp_mem grp_mem_s grp_mem_m \
+		   grp_adm grp_adm_s grp_adm_m; do
+    query "REPLACE INTO main.$tbl SELECT * FROM src.$tbl
+	   WHERE gname=$gnamesql;"
+  done
+  blogs=`query "SELECT group_concat(\"'\"||id||\"'\", ',')
+  	        FROM src.blog_s WHERE key='owner' AND val=$gnamesql;"`
+  echo blogs=$blogs
+  for tbl in blog blog_s blog_m; do
+    query <<-EOF
+	REPLACE INTO main.$tbl
+	  SELECT * FROM src.$tbl
+	  WHERE id IN ($blogs);
+	EOF
+  done
+  for blogid in $blogs; do
+    for tbl in article article_s article_m; do
+      query <<-EOF
+	REPLACE INTO main.$tbl
+	  SELECT * FROM src.$tbl
+	  WHERE id IN (SELECT id FROM src.article WHERE blogid IN ($blogs));
+	EOF
+    done
+  done
+  query "END;"
+done
+exit
+  grid = g.to_i
+  # Copy group itself
+  gname = db.execute("SELECT gname FROM main.grp WHERE rowid=?", grid)[0][0]
+  r = db.execute("SELECT gname FROM dst.grp WHERE gname=?", gname)
+  if r[0] then
+    STDERR.printf("Skipping existing group %s\n", r[0][0])
+  #######  next
+  end
+  printf("Copying group %s...\n", gname)
+  db.execute("BEGIN;");
+  db.execute(
+    'REPLACE INTO dst.grp SELECT * FROM main.grp WHERE rowid=?', grid)
+  for tbl in %w(grp_s grp_m grp_mem grp_mem_s grp_mem_m
+		grp_adm grp_adm_s grp_adm_m)
+    db.execute(
+      "REPLACE INTO dst.#{tbl}
+       SELECT * FROM main.#{tbl} WHERE gname=?", gname)
+  end
+  ## Copy articles
+  db.execute("SELECT id FROM blog_s WHERE key='owner' AND val=?",
+             gname) do |row|
+    blogid = row[0]
+    for tbl in %w(blog blog_s blog_m)
+      db.execute(
+        "REPLACE INTO dst.#{tbl}
+	 SELECT * FROM main.#{tbl} WHERE id=?", blogid)
+      db.execute(
+        "SELECT id FROM article WHERE blogid=?", blogid) do |article|
+        aid = article[0]
+        for atbl in %W(article article_m)
+          db.execute(
+            "REPLACE INTO dst.#{atbl}
+             SELECT * FROM main.#{atbl} WHERE id=?", aid)
+        end
+        db.execute(
+          "SELECT id, type, key, val, bin FROM article_s WHERE id=?",
+          aid) do |a|
+          db.execute("REPLACE INTO dst.article_s VALUES(?, ?, ?, ?, ?)",
+                     a[0], a[1], a[2],
+                     a[3].gsub(srcurl, dsturl),
+                     a[4])
+        end
+      end
+    end
+  end
+  db.execute("END;");
+  
+end
+puts("Done.")