--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/s4-migrate.sh	Thu Jun 11 19:15:40 2020 +0900
@@ -0,0 +1,185 @@
+#!/bin/sh
+# s4 - migration module
+# (C)2020 by HIROSE, Yuuji
+
+srcdb=`unset DB; [ -f $1 ] && . ./$1 && echo ${DB:-db/cgi.sq3}`
+dst=`unset DB; [ -f $2 ] && . ./$2 && echo "${DB:-db/cgi.sq3}|$URL"`
+dstdb=${dst%\|*}
+dsturl=${dst##*\|}
+type htmlescape >/dev/null 2>&1 || . `dirname $1`/s4-funcs.sh
+world=${2##*-config-}; world=${world%.*}
+htmlworld=`echo "$world"|htmlescape`
+
+echo srcdb=$srcdb dstdb=$dstdb
+echo URL=$URL
+echo dstURL=$dsturl
+
+shift 2
+
+query "ATTACH DATABASE \"$dstdb\" AS dst;" || abort "Cannot attach db #{dstdb}"
+
+failure=0
+for grid; do
+  grid=$((0 + $grid))
+  gnamesql="(SELECT gname FROM main.grp WHERE rowid=$grid)"
+  grp=`query "SELECT gname FROM main.grp WHERE rowid=$grid;"`
+  htmlgrp=`echo "$grp"|htmlescape`
+  qgrp=`sqlquote "$grp"`
+  if [ -n "`query 'SELECT gname FROM dst.grp WHERE gname=$qgrp;'`" ]; then
+    echo "[$htmlgrp]グループがWorld[$htmlworld]にあるので中止します。"|html p
+    failure=$((failure+1))
+    continue
+  fi
+  echo "Copying $grid..."
+  query "BEGIN;"
+  query "REPLACE INTO dst.grp SELECT * FROM main.grp WHERE rowid=$grid;"
+  destrowid=`query "SELECT last_insert_rowid();"`
+  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 dst.$tbl SELECT * FROM main.$tbl
+	   WHERE gname=$gnamesql;"
+  done
+  blogs=`query "SELECT group_concat(\"'\"||id||\"'\", ',')
+  	        FROM main.blog_s WHERE key='owner' AND val=$gnamesql;"`
+  echo blogs=$blogs
+  for tbl in blog blog_s blog_m; do
+    query <<-EOF
+	REPLACE INTO dst.$tbl
+	  SELECT * FROM main.$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 dst.$tbl
+	  SELECT * FROM main.$tbl
+	  WHERE id IN (SELECT id FROM main.article WHERE blogid IN ($blogs));
+	EOF
+    done
+  done
+  ## Check the equality of two DBs
+  echo "grid=$grid grp=$grp qgrp=$qgrp" | htmlescape
+  # grp
+  d1=$(query <<-EOF
+	SELECT * FROM main.grp
+	       	 NATURAL LEFT JOIN main.grp_s
+		 NATURAL LEFT JOIN main.grp_m
+	WHERE gname=$qgrp
+	EXCEPT
+	SELECT * FROM dst.grp
+	       	 NATURAL LEFT JOIN dst.grp_s
+		 NATURAL LEFT JOIN dst.grp_m
+	WHERE gname=$qgrp;
+	EOF
+	)
+  err DONE 
+  err d1="$d1"
+  # blog
+  d2=$(query <<-EOF
+	SELECT * FROM main.blog
+	       	 NATURAL LEFT JOIN main.blog_s
+		 NATURAL LEFT JOIN main.blog_m
+	WHERE id IN (SELECT id FROM main.blog_s
+	      	     WHERE key='owner' AND val=$qgrp)
+	EXCEPT
+	SELECT * FROM dst.blog
+	       	 NATURAL LEFT JOIN dst.blog_s
+		 NATURAL LEFT JOIN dst.blog_m
+	WHERE id IN (SELECT id FROM dst.blog_s
+	      	     WHERE key='owner' AND val=$qgrp);
+	EOF
+    )
+  # article
+  d3=$(query <<-EOF
+	SELECT * FROM main.article
+	       	 NATURAL LEFT JOIN main.article_s
+	       	 NATURAL LEFT JOIN main.article_m
+	WHERE blogid IN ($blogs)
+	EXCEPT
+	SELECT * FROM dst.article
+	       	 NATURAL LEFT JOIN dst.article_s
+	       	 NATURAL LEFT JOIN dst.article_m
+	WHERE blogid IN ($blogs);
+	EOF
+       )
+  if [ -z "$d1$d2$d3" ]; then
+    echo "Copying done, rewriting article links..."
+    echo "Old URL: $UR"
+    echo "New URL: $dsturl"
+    query <<-EOF
+	UPDATE dst.article_s
+	SET val=replace(val, '$URL', '$dsturl')
+	WHERE key='text' AND val LIKE '|${URL}|' ESCAPE '|';
+	UPDATE dst.blog_s
+	SET val=replace(val, '$URL', '$dsturl')
+	WHERE key='heading' AND val LIKE '|${URL}|' ESCAPE '|';
+	EOF
+    # Create blog-rowid conversion table
+    sedfile=$tmpd/arttrans.sed
+    query <<-EOF > $sedfile
+	WITH arttrans AS (
+	  SELECT s.rowid srcrid, d.rowid dstrid
+	  FROM main.article s JOIN dst.article d ON s.id=d.id
+	  WHERE s.id in (SELECT id
+	  	     	 FROM article WHERE blogid IN ($blogs))
+	) SELECT printf("/^>/s/\#%s\([^0-9]*\)/\#%s\1/g", srcrid, dstrid)
+	  FROM arttrans;
+	EOF
+    query <<-EOF > $tmpd/repl.art.rowid
+	SELECT rowid FROM dst.article_s
+	WHERE key='text' AND val GLOB '>*#[1-9]*'
+	  AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
+	EOF
+    sql=$tmpd/update.sql
+    for arid in `cat $tmpd/repl.art.rowid`; do
+      newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \
+              | unhexize | sed -f "$sedfile" | hexize`
+      echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
+    done
+    # Rewrite blog-links in the group
+    #  Create sed script
+    sedfile2=${sedfile}2
+    query <<-EOF > $sedfile2
+	WITH blogtrans AS (
+	  SELECT s.rowid srcrid, d.rowid dstrid
+	  FROM main.blog s JOIN dst.blog d ON s.id=d.id
+	  WHERE s.id IN ($blogs)
+	) SELECT printf('s/\(\?replyblog\)\+%s\([^0-9]*\)/\1+%s\2/g',
+		         srcrid, dstrid)
+	  FROM blogtrans;
+	EOF
+    bloglinks=$tmpd/bloglinks.rowid
+    query <<-EOF > $bloglinks
+	SELECT rowid FROM dst.article_s
+	WHERE key='text' AND val LIKE '%?replyblog+%'
+	  AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
+	EOF
+    for arid in `cat $bloglinks`; do
+      newval=`query "SELECT hex(replace(val, '$URL', '$dsturl')) 
+      		     FROM dst.article_s WHERE rowid=$arid;" \
+              | unhexize | sed -f "$sedfile2" | hexize`
+      echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
+    done
+    if query ".read $sql"; then
+      echo "Success!!"
+      query <<-EOF
+	DELETE FROM main.article WHERE blogid IN ($blogs);
+	DELETE FROM main.blog WHERE id IN ($blogs);
+	DELETE FROM main.grp WHERE rowid=$grid;
+	EOF
+      query "END;"
+      echo "Done."
+    else
+      failure=-1
+      echo "Replacing failed."
+      query "ROLLBACK;"
+    fi
+  else
+    failure=$((failure + 1))
+    echo "Fail!"
+    query "ROLLBACK;"
+  fi
+done
+
+return $failure