yuuji@779: #!/bin/sh
yuuji@779: # s4 - migration module
yuuji@779: # (C)2020 by HIROSE, Yuuji
yuuji@779: 
yuuji@779: srcdb=`unset DB; [ -f $1 ] && . ./$1 && echo ${DB:-db/cgi.sq3}`
yuuji@779: dst=`unset DB; [ -f $2 ] && . ./$2 && echo "${DB:-db/cgi.sq3}|$URL"`
yuuji@779: dstdb=${dst%\|*}
yuuji@779: dsturl=${dst##*\|}
yuuji@869: type htmlescape >/dev/null 2>&1 || DB=$srcdb . `dirname $1`/s4-funcs.sh
yuuji@780: case "$2" in
yuuji@780:   s4-config.sh) world=Base ;;
yuuji@780:   *)		world=${2##*-config-}; world=${world%.*} ;;
yuuji@780: esac
yuuji@779: htmlworld=`echo "$world"|htmlescape`
yuuji@779: 
yuuji@786: err "--- Migration Started with \$1=$1 \$2=$2 at `date` ---"
yuuji@780: err srcdb=$srcdb dstdb=$dstdb
yuuji@780: err URL=$URL
yuuji@780: err dstURL=$dsturl
yuuji@779: 
yuuji@779: shift 2
yuuji@779: 
yuuji@779: query "ATTACH DATABASE \"$dstdb\" AS dst;" || abort "Cannot attach db #{dstdb}"
yuuji@779: 
yuuji@865: if [ -z "$tmpd" ]; then
yuuji@865:   tmpd=`tmpd=$tmpdir mktempd`	# If called from command line
yuuji@865:   if [ -z "$tmpd" -o ! -w "$tmpd" ]; then
yuuji@865:     err "-- Cannot detect temporary directory [$tmpd] --"
yuuji@865:     exit 2
yuuji@865:   fi
yuuji@865:   trap "rm -r $tmpd" EXIT INT HUP
yuuji@865: fi
yuuji@779: failure=0
yuuji@868: faillist=""
yuuji@779: for grid; do
yuuji@779:   grid=$((0 + $grid))
yuuji@779:   gnamesql="(SELECT gname FROM main.grp WHERE rowid=$grid)"
yuuji@779:   grp=`query "SELECT gname FROM main.grp WHERE rowid=$grid;"`
yuuji@868:   if [ -z "$grp" ]; then
yuuji@868:     echo "ID=$grid not found, skipped" | html p
yuuji@868:     continue
yuuji@868:   fi
yuuji@779:   htmlgrp=`echo "$grp"|htmlescape`
yuuji@779:   qgrp=`sqlquote "$grp"`
yuuji@794:   if [ -n "`query \"SELECT gname FROM dst.grp WHERE gname=$qgrp;\"`" ]; then
yuuji@779:     echo "[$htmlgrp]グループがWorld[$htmlworld]にあるので中止します。"|html p
yuuji@779:     failure=$((failure+1))
yuuji@779:     continue
yuuji@779:   fi
yuuji@779:   echo "Copying $grid..."
yuuji@779:   query "BEGIN;"
yuuji@779:   query "REPLACE INTO dst.grp SELECT * FROM main.grp WHERE rowid=$grid;"
yuuji@779:   destrowid=`query "SELECT last_insert_rowid();"`
yuuji@779:   for tbl in grp_s grp_m grp_mem grp_mem_s grp_mem_m \
yuuji@779: 		   grp_adm grp_adm_s grp_adm_m; do
yuuji@779:     query "REPLACE INTO dst.$tbl SELECT * FROM main.$tbl
yuuji@779: 	   WHERE gname=$gnamesql;"
yuuji@779:   done
yuuji@779:   blogs=`query "SELECT group_concat(\"'\"||id||\"'\", ',')
yuuji@779:   	        FROM main.blog_s WHERE key='owner' AND val=$gnamesql;"`
yuuji@779:   echo blogs=$blogs
yuuji@779:   for tbl in blog blog_s blog_m; do
yuuji@779:     query <<-EOF
yuuji@779: 	REPLACE INTO dst.$tbl
yuuji@779: 	  SELECT * FROM main.$tbl
yuuji@779: 	  WHERE id IN ($blogs);
yuuji@779: 	EOF
yuuji@779:   done
yuuji@784:   for tbl in article article_s article_m; do
yuuji@784:     query <<-EOF
yuuji@779: 	REPLACE INTO dst.$tbl
yuuji@779: 	  SELECT * FROM main.$tbl
yuuji@784: 	  WHERE id IN (SELECT id FROM main.article WHERE blogid IN ($blogs))
yuuji@784: 	  ORDER BY rowid;
yuuji@779: 	EOF
yuuji@779:   done
yuuji@779:   ## Check the equality of two DBs
yuuji@779:   echo "grid=$grid grp=$grp qgrp=$qgrp" | htmlescape
yuuji@779:   # grp
yuuji@779:   d1=$(query <<-EOF
yuuji@779: 	SELECT * FROM main.grp
yuuji@779: 	       	 NATURAL LEFT JOIN main.grp_s
yuuji@779: 		 NATURAL LEFT JOIN main.grp_m
yuuji@779: 	WHERE gname=$qgrp
yuuji@779: 	EXCEPT
yuuji@779: 	SELECT * FROM dst.grp
yuuji@779: 	       	 NATURAL LEFT JOIN dst.grp_s
yuuji@779: 		 NATURAL LEFT JOIN dst.grp_m
yuuji@779: 	WHERE gname=$qgrp;
yuuji@779: 	EOF
yuuji@779: 	)
yuuji@779:   err DONE 
yuuji@779:   err d1="$d1"
yuuji@779:   # blog
yuuji@779:   d2=$(query <<-EOF
yuuji@779: 	SELECT * FROM main.blog
yuuji@779: 	       	 NATURAL LEFT JOIN main.blog_s
yuuji@779: 		 NATURAL LEFT JOIN main.blog_m
yuuji@779: 	WHERE id IN (SELECT id FROM main.blog_s
yuuji@779: 	      	     WHERE key='owner' AND val=$qgrp)
yuuji@779: 	EXCEPT
yuuji@779: 	SELECT * FROM dst.blog
yuuji@779: 	       	 NATURAL LEFT JOIN dst.blog_s
yuuji@779: 		 NATURAL LEFT JOIN dst.blog_m
yuuji@779: 	WHERE id IN (SELECT id FROM dst.blog_s
yuuji@779: 	      	     WHERE key='owner' AND val=$qgrp);
yuuji@779: 	EOF
yuuji@779:     )
yuuji@786:   err d2="$d2"
yuuji@779:   # article
yuuji@779:   d3=$(query <<-EOF
yuuji@779: 	SELECT * FROM main.article
yuuji@779: 	       	 NATURAL LEFT JOIN main.article_s
yuuji@779: 	       	 NATURAL LEFT JOIN main.article_m
yuuji@779: 	WHERE blogid IN ($blogs)
yuuji@779: 	EXCEPT
yuuji@779: 	SELECT * FROM dst.article
yuuji@779: 	       	 NATURAL LEFT JOIN dst.article_s
yuuji@779: 	       	 NATURAL LEFT JOIN dst.article_m
yuuji@779: 	WHERE blogid IN ($blogs);
yuuji@779: 	EOF
yuuji@779:        )
yuuji@786:   err d3="$d3"
yuuji@779:   if [ -z "$d1$d2$d3" ]; then
yuuji@821:     echo "Copy and verification done, rewriting URL in articles..."
yuuji@780:     echo "Old URL: $URL"
yuuji@779:     echo "New URL: $dsturl"
yuuji@779:     query <<-EOF
yuuji@779: 	UPDATE dst.article_s
yuuji@790: 	SET val=replace(val,
yuuji@790: 			'${URL}?grp+$grid',
yuuji@790: 			'${dsturl}?grp+$destrowid')
yuuji@790: 	WHERE key='text' AND val LIKE '%${URL}%';
yuuji@792: 	EOF
yuuji@779:     # Create blog-rowid conversion table
yuuji@779:     sedfile=$tmpd/arttrans.sed
yuuji@786:     # sedfile=tmp/arttrans.sed
yuuji@779:     query <<-EOF > $sedfile
yuuji@779: 	WITH arttrans AS (
yuuji@779: 	  SELECT s.rowid srcrid, d.rowid dstrid
yuuji@779: 	  FROM main.article s JOIN dst.article d ON s.id=d.id
yuuji@779: 	  WHERE s.id in (SELECT id
yuuji@779: 	  	     	 FROM article WHERE blogid IN ($blogs))
yuuji@795: 	) SELECT printf("/^>/s/\#%s($|[^0-9])/\#%s\1/g", srcrid, dstrid)
yuuji@779: 	  FROM arttrans;
yuuji@779: 	EOF
yuuji@779:     query <<-EOF > $tmpd/repl.art.rowid
yuuji@779: 	SELECT rowid FROM dst.article_s
yuuji@779: 	WHERE key='text' AND val GLOB '>*#[1-9]*'
yuuji@779: 	  AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
yuuji@779: 	EOF
yuuji@779:     sql=$tmpd/update.sql
yuuji@779:     for arid in `cat $tmpd/repl.art.rowid`; do
yuuji@779:       newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \
yuuji@786:               | unhexize | sed -Ef "$sedfile" | hexize`
yuuji@813:       echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;"
yuuji@813:     done >$sql
yuuji@779:     # Rewrite blog-links in the group
yuuji@779:     #  Create sed script
yuuji@779:     sedfile2=${sedfile}2
yuuji@779:     query <<-EOF > $sedfile2
yuuji@779: 	WITH blogtrans AS (
yuuji@779: 	  SELECT s.rowid srcrid, d.rowid dstrid
yuuji@779: 	  FROM main.blog s JOIN dst.blog d ON s.id=d.id
yuuji@779: 	  WHERE s.id IN ($blogs)
yuuji@795: 	) SELECT printf('s/(\?replyblog)\+%s($|[^0-9])/\1+%s\2/g',
yuuji@779: 		         srcrid, dstrid)
yuuji@779: 	  FROM blogtrans;
yuuji@779: 	EOF
yuuji@779:     bloglinks=$tmpd/bloglinks.rowid
yuuji@779:     query <<-EOF > $bloglinks
yuuji@779: 	SELECT rowid FROM dst.article_s
yuuji@779: 	WHERE key='text' AND val LIKE '%?replyblog+%'
yuuji@779: 	  AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
yuuji@779: 	EOF
yuuji@779:     for arid in `cat $bloglinks`; do
yuuji@779:       newval=`query "SELECT hex(replace(val, '$URL', '$dsturl')) 
yuuji@779:       		     FROM dst.article_s WHERE rowid=$arid;" \
yuuji@795:               | unhexize | sed -Ef "$sedfile2" | hexize`
yuuji@779:       echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
yuuji@779:     done
yuuji@824:     # Rewrite showattc Direct-Links
yuuji@824:     attclinks=$(
yuuji@824:       query <<-EOF
yuuji@824: 	SELECT group_concat(rowid, ',') FROM dst.article_s
yuuji@824: 	WHERE key='text' AND val LIKE '%?showattc+article_m+%'
yuuji@824: 	  AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
yuuji@824: 	EOF
yuuji@824:       )
yuuji@824:     if [ -n "$attclinks" ]; then
yuuji@824:       err attclinks=$attclinks
yuuji@824:       sedfile3=${sedfile}3
yuuji@824:       query <<-EOF > $sedfile3
yuuji@824: 	WITH attctrans AS (
yuuji@824: 	  SELECT s.rowid srcarid, d.rowid dstarid
yuuji@824: 	  FROM main.article_m s NATURAL JOIN dst.article_m d
yuuji@824: 	  WHERE d.id IN (SELECT id FROM dst.article
yuuji@824: 	  	     	 WHERE blogid IN ($blogs))
yuuji@824: 	) SELECT printf('s/(\?showattc\+article_m)\+%s($|[^0-9])/\1+%s\2/g',
yuuji@824: 			 srcarid, dstarid)
yuuji@824: 	  FROM attctrans;
yuuji@824: 	EOF
yuuji@824:       echo $attclinks | tr , '\n' | while read arid; do
yuuji@824: 	newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \
yuuji@824:               | unhexize | sed -Ef "$sedfile3" | hexize`
yuuji@824: 	echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;"
yuuji@824:       done >> $sql
yuuji@824:     fi
yuuji@813:     if [ -z "`query \".read $sql\"`" ]; then
yuuji@824:       echo "URL rewriting done."
yuuji@824:       echo -n "Removing the group in the old world..."
yuuji@779:       query <<-EOF
yuuji@821: 	SAVEPOINT rmgroup;
yuuji@779: 	DELETE FROM main.article WHERE blogid IN ($blogs);
yuuji@779: 	DELETE FROM main.blog WHERE id IN ($blogs);
yuuji@779: 	DELETE FROM main.grp WHERE rowid=$grid;
yuuji@821: 	RELEASE SAVEPOINT rmgroup;
yuuji@779: 	EOF
yuuji@813:       s=`query "SELECT * FROM main.grp WHERE rowid=$grid;"`
yuuji@813:       if [ -z "$s" ]; then
yuuji@821: 	echo "Done."
yuuji@813: 	echo "Success!!"
yuuji@813: 	query "END;"
yuuji@813: 	clean-orphaned
yuuji@813: 	echo "Done."
yuuji@813:       else
yuuji@813: 	echo Removal failed
yuuji@813: 	echo "現行グループ消去ができませんでした。"
yuuji@813: 	echo "書き込みの多いグループの場合は空いている時間帯に試して下さい。"
yuuji@813: 	query "ROLLBACK;"
yuuji@868: 	failure=$((failure+1))
yuuji@868: 	faillist="$faillist${faillist:+ }$grid"
yuuji@813:       fi
yuuji@779:     else
yuuji@868:       failure==$((failure+1))
yuuji@779:       echo "Replacing failed."
yuuji@779:       query "ROLLBACK;"
yuuji@779:     fi
yuuji@779:   else
yuuji@779:     failure=$((failure + 1))
yuuji@779:     echo "Fail!"
yuuji@779:     query "ROLLBACK;"
yuuji@779:   fi
yuuji@779: done
yuuji@779: 
yuuji@780: err "Migration ended at `date` with failure=$failure"
yuuji@868: tty >/dev/null 2>&1 && test -n "$faillist" && echo "Skipped: $faillist"
yuuji@779: return $failure