Mercurial > hgrepos > hgweb.cgi > s4
view s4-migrate.sh @ 1058:c8a01955404f draft default tip
Add sup/sub replace
author | HIROSE Yuuji <yuuji@gentei.org> |
---|---|
date | Tue, 18 Jun 2024 19:34:39 +0900 |
parents | f389a311a8d4 |
children |
line wrap: on
line source
#!/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 || DB=$srcdb . `dirname $1`/s4-funcs.sh case "$2" in s4-config.sh) world=Base ;; *) world=${2##*-config-}; world=${world%.*} ;; esac htmlworld=`echo "$world"|htmlescape` err "--- Migration Started with \$1=$1 \$2=$2 at `date` ---" err srcdb=$srcdb dstdb=$dstdb err URL=$URL err dstURL=$dsturl shift 2 query "ATTACH DATABASE \"$dstdb\" AS dst;" || abort "Cannot attach db #{dstdb}" if [ -z "$tmpd" ]; then tmpd=`tmpd=$tmpdir mktempd` # If called from command line if [ -z "$tmpd" -o ! -w "$tmpd" ]; then err "-- Cannot detect temporary directory [$tmpd] --" exit 2 fi trap "rm -r $tmpd" EXIT INT HUP fi failure=0 faillist="" 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;"` if [ -z "$grp" ]; then echo "ID=$grid not found, skipped" | html p continue fi 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 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)) ORDER BY rowid; EOF 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 ) err d2="$d2" # 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 ) err d3="$d3" if [ -z "$d1$d2$d3" ]; then echo "Copy and verification done, rewriting URL in articles..." echo "Old URL: $URL" echo "New URL: $dsturl" query <<-EOF UPDATE dst.article_s SET val=replace(val, '${URL}?grp+$grid', '${dsturl}?grp+$destrowid') WHERE key='text' AND val LIKE '%${URL}%'; EOF # Create blog-rowid conversion table sedfile=$tmpd/arttrans.sed # sedfile=tmp/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 -Ef "$sedfile" | hexize` echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" done >$sql # 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 -Ef "$sedfile2" | hexize` echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql done # Rewrite showattc Direct-Links attclinks=$( query <<-EOF SELECT group_concat(rowid, ',') FROM dst.article_s WHERE key='text' AND val LIKE '%?showattc+article_m+%' AND id IN (SELECT id FROM article WHERE blogid IN ($blogs)); EOF ) if [ -n "$attclinks" ]; then err attclinks=$attclinks sedfile3=${sedfile}3 query <<-EOF > $sedfile3 WITH attctrans AS ( SELECT s.rowid srcarid, d.rowid dstarid FROM main.article_m s NATURAL JOIN dst.article_m d WHERE d.id IN (SELECT id FROM dst.article WHERE blogid IN ($blogs)) ) SELECT printf('s/(\?showattc\+article_m)\+%s($|[^0-9])/\1+%s\2/g', srcarid, dstarid) FROM attctrans; EOF echo $attclinks | tr , '\n' | while read arid; do newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \ | unhexize | sed -Ef "$sedfile3" | hexize` echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" done >> $sql fi if [ -z "`query \".read $sql\"`" ]; then echo "URL rewriting done." echo -n "Removing the group in the old world..." query <<-EOF SAVEPOINT rmgroup; DELETE FROM main.article WHERE blogid IN ($blogs); DELETE FROM main.blog WHERE id IN ($blogs); DELETE FROM main.grp WHERE rowid=$grid; RELEASE SAVEPOINT rmgroup; EOF s=`query "SELECT * FROM main.grp WHERE rowid=$grid;"` if [ -z "$s" ]; then echo "Done." echo "Success!!" query "END;" clean_orphaned echo "Done." else echo Removal failed echo "現行グループ消去ができませんでした。" echo "書き込みの多いグループの場合は空いている時間帯に試して下さい。" query "ROLLBACK;" failure=$((failure+1)) faillist="$faillist${faillist:+ }$grid" fi else failure==$((failure+1)) echo "Replacing failed." query "ROLLBACK;" fi else failure=$((failure + 1)) echo "Fail!" query "ROLLBACK;" fi done err "Migration ended at `date` with failure=$failure" tty >/dev/null 2>&1 && test -n "$faillist" && echo "Skipped: $faillist" return $failure