s4

annotate s4-migrate.sh @ 790:7ac852e8c216

find changesets by author, revision, files, or words in the commit message
Update URL only itself
author HIROSE Yuuji <yuuji@gentei.org>
date Sat, 13 Jun 2020 17:04:29 +0900
parents 809caeb20758
children 567980314463
rev   line source
yuuji@779 1 #!/bin/sh
yuuji@779 2 # s4 - migration module
yuuji@779 3 # (C)2020 by HIROSE, Yuuji
yuuji@779 4
yuuji@779 5 srcdb=`unset DB; [ -f $1 ] && . ./$1 && echo ${DB:-db/cgi.sq3}`
yuuji@779 6 dst=`unset DB; [ -f $2 ] && . ./$2 && echo "${DB:-db/cgi.sq3}|$URL"`
yuuji@779 7 dstdb=${dst%\|*}
yuuji@779 8 dsturl=${dst##*\|}
yuuji@779 9 type htmlescape >/dev/null 2>&1 || . `dirname $1`/s4-funcs.sh
yuuji@780 10 case "$2" in
yuuji@780 11 s4-config.sh) world=Base ;;
yuuji@780 12 *) world=${2##*-config-}; world=${world%.*} ;;
yuuji@780 13 esac
yuuji@779 14 htmlworld=`echo "$world"|htmlescape`
yuuji@779 15
yuuji@786 16 err "--- Migration Started with \$1=$1 \$2=$2 at `date` ---"
yuuji@780 17 err srcdb=$srcdb dstdb=$dstdb
yuuji@780 18 err URL=$URL
yuuji@780 19 err dstURL=$dsturl
yuuji@779 20
yuuji@779 21 shift 2
yuuji@779 22
yuuji@779 23 query "ATTACH DATABASE \"$dstdb\" AS dst;" || abort "Cannot attach db #{dstdb}"
yuuji@779 24
yuuji@779 25 failure=0
yuuji@779 26 for grid; do
yuuji@779 27 grid=$((0 + $grid))
yuuji@779 28 gnamesql="(SELECT gname FROM main.grp WHERE rowid=$grid)"
yuuji@779 29 grp=`query "SELECT gname FROM main.grp WHERE rowid=$grid;"`
yuuji@779 30 htmlgrp=`echo "$grp"|htmlescape`
yuuji@779 31 qgrp=`sqlquote "$grp"`
yuuji@779 32 if [ -n "`query 'SELECT gname FROM dst.grp WHERE gname=$qgrp;'`" ]; then
yuuji@779 33 echo "[$htmlgrp]グループがWorld[$htmlworld]にあるので中止します。"|html p
yuuji@779 34 failure=$((failure+1))
yuuji@779 35 continue
yuuji@779 36 fi
yuuji@779 37 echo "Copying $grid..."
yuuji@779 38 query "BEGIN;"
yuuji@779 39 query "REPLACE INTO dst.grp SELECT * FROM main.grp WHERE rowid=$grid;"
yuuji@779 40 destrowid=`query "SELECT last_insert_rowid();"`
yuuji@779 41 for tbl in grp_s grp_m grp_mem grp_mem_s grp_mem_m \
yuuji@779 42 grp_adm grp_adm_s grp_adm_m; do
yuuji@779 43 query "REPLACE INTO dst.$tbl SELECT * FROM main.$tbl
yuuji@779 44 WHERE gname=$gnamesql;"
yuuji@779 45 done
yuuji@779 46 blogs=`query "SELECT group_concat(\"'\"||id||\"'\", ',')
yuuji@779 47 FROM main.blog_s WHERE key='owner' AND val=$gnamesql;"`
yuuji@779 48 echo blogs=$blogs
yuuji@779 49 for tbl in blog blog_s blog_m; do
yuuji@779 50 query <<-EOF
yuuji@779 51 REPLACE INTO dst.$tbl
yuuji@779 52 SELECT * FROM main.$tbl
yuuji@779 53 WHERE id IN ($blogs);
yuuji@779 54 EOF
yuuji@779 55 done
yuuji@784 56 for tbl in article article_s article_m; do
yuuji@784 57 query <<-EOF
yuuji@779 58 REPLACE INTO dst.$tbl
yuuji@779 59 SELECT * FROM main.$tbl
yuuji@784 60 WHERE id IN (SELECT id FROM main.article WHERE blogid IN ($blogs))
yuuji@784 61 ORDER BY rowid;
yuuji@779 62 EOF
yuuji@779 63 done
yuuji@779 64 ## Check the equality of two DBs
yuuji@779 65 echo "grid=$grid grp=$grp qgrp=$qgrp" | htmlescape
yuuji@779 66 # grp
yuuji@779 67 d1=$(query <<-EOF
yuuji@779 68 SELECT * FROM main.grp
yuuji@779 69 NATURAL LEFT JOIN main.grp_s
yuuji@779 70 NATURAL LEFT JOIN main.grp_m
yuuji@779 71 WHERE gname=$qgrp
yuuji@779 72 EXCEPT
yuuji@779 73 SELECT * FROM dst.grp
yuuji@779 74 NATURAL LEFT JOIN dst.grp_s
yuuji@779 75 NATURAL LEFT JOIN dst.grp_m
yuuji@779 76 WHERE gname=$qgrp;
yuuji@779 77 EOF
yuuji@779 78 )
yuuji@779 79 err DONE
yuuji@779 80 err d1="$d1"
yuuji@779 81 # blog
yuuji@779 82 d2=$(query <<-EOF
yuuji@779 83 SELECT * FROM main.blog
yuuji@779 84 NATURAL LEFT JOIN main.blog_s
yuuji@779 85 NATURAL LEFT JOIN main.blog_m
yuuji@779 86 WHERE id IN (SELECT id FROM main.blog_s
yuuji@779 87 WHERE key='owner' AND val=$qgrp)
yuuji@779 88 EXCEPT
yuuji@779 89 SELECT * FROM dst.blog
yuuji@779 90 NATURAL LEFT JOIN dst.blog_s
yuuji@779 91 NATURAL LEFT JOIN dst.blog_m
yuuji@779 92 WHERE id IN (SELECT id FROM dst.blog_s
yuuji@779 93 WHERE key='owner' AND val=$qgrp);
yuuji@779 94 EOF
yuuji@779 95 )
yuuji@786 96 err d2="$d2"
yuuji@779 97 # article
yuuji@779 98 d3=$(query <<-EOF
yuuji@779 99 SELECT * FROM main.article
yuuji@779 100 NATURAL LEFT JOIN main.article_s
yuuji@779 101 NATURAL LEFT JOIN main.article_m
yuuji@779 102 WHERE blogid IN ($blogs)
yuuji@779 103 EXCEPT
yuuji@779 104 SELECT * FROM dst.article
yuuji@779 105 NATURAL LEFT JOIN dst.article_s
yuuji@779 106 NATURAL LEFT JOIN dst.article_m
yuuji@779 107 WHERE blogid IN ($blogs);
yuuji@779 108 EOF
yuuji@779 109 )
yuuji@786 110 err d3="$d3"
yuuji@779 111 if [ -z "$d1$d2$d3" ]; then
yuuji@779 112 echo "Copying done, rewriting article links..."
yuuji@780 113 echo "Old URL: $URL"
yuuji@779 114 echo "New URL: $dsturl"
yuuji@779 115 query <<-EOF
yuuji@779 116 UPDATE dst.article_s
yuuji@790 117 SET val=replace(val,
yuuji@790 118 '${URL}?grp+$grid',
yuuji@790 119 '${dsturl}?grp+$destrowid')
yuuji@790 120 WHERE key='text' AND val LIKE '%${URL}%';
yuuji@779 121 # Create blog-rowid conversion table
yuuji@779 122 sedfile=$tmpd/arttrans.sed
yuuji@786 123 # sedfile=tmp/arttrans.sed
yuuji@779 124 query <<-EOF > $sedfile
yuuji@779 125 WITH arttrans AS (
yuuji@779 126 SELECT s.rowid srcrid, d.rowid dstrid
yuuji@779 127 FROM main.article s JOIN dst.article d ON s.id=d.id
yuuji@779 128 WHERE s.id in (SELECT id
yuuji@779 129 FROM article WHERE blogid IN ($blogs))
yuuji@786 130 ) SELECT printf("/^>/s/\#%s($|[ ,\#])/\#%s\1/g", srcrid, dstrid)
yuuji@779 131 FROM arttrans;
yuuji@779 132 EOF
yuuji@779 133 query <<-EOF > $tmpd/repl.art.rowid
yuuji@779 134 SELECT rowid FROM dst.article_s
yuuji@779 135 WHERE key='text' AND val GLOB '>*#[1-9]*'
yuuji@779 136 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
yuuji@779 137 EOF
yuuji@779 138 sql=$tmpd/update.sql
yuuji@779 139 for arid in `cat $tmpd/repl.art.rowid`; do
yuuji@779 140 newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \
yuuji@786 141 | unhexize | sed -Ef "$sedfile" | hexize`
yuuji@779 142 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
yuuji@779 143 done
yuuji@779 144 # Rewrite blog-links in the group
yuuji@779 145 # Create sed script
yuuji@779 146 sedfile2=${sedfile}2
yuuji@779 147 query <<-EOF > $sedfile2
yuuji@779 148 WITH blogtrans AS (
yuuji@779 149 SELECT s.rowid srcrid, d.rowid dstrid
yuuji@779 150 FROM main.blog s JOIN dst.blog d ON s.id=d.id
yuuji@779 151 WHERE s.id IN ($blogs)
yuuji@779 152 ) SELECT printf('s/\(\?replyblog\)\+%s\([^0-9]*\)/\1+%s\2/g',
yuuji@779 153 srcrid, dstrid)
yuuji@779 154 FROM blogtrans;
yuuji@779 155 EOF
yuuji@779 156 bloglinks=$tmpd/bloglinks.rowid
yuuji@779 157 query <<-EOF > $bloglinks
yuuji@779 158 SELECT rowid FROM dst.article_s
yuuji@779 159 WHERE key='text' AND val LIKE '%?replyblog+%'
yuuji@779 160 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
yuuji@779 161 EOF
yuuji@779 162 for arid in `cat $bloglinks`; do
yuuji@779 163 newval=`query "SELECT hex(replace(val, '$URL', '$dsturl'))
yuuji@779 164 FROM dst.article_s WHERE rowid=$arid;" \
yuuji@779 165 | unhexize | sed -f "$sedfile2" | hexize`
yuuji@779 166 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
yuuji@779 167 done
yuuji@779 168 if query ".read $sql"; then
yuuji@779 169 echo "Success!!"
yuuji@779 170 query <<-EOF
yuuji@779 171 DELETE FROM main.article WHERE blogid IN ($blogs);
yuuji@779 172 DELETE FROM main.blog WHERE id IN ($blogs);
yuuji@779 173 DELETE FROM main.grp WHERE rowid=$grid;
yuuji@779 174 EOF
yuuji@779 175 query "END;"
yuuji@779 176 echo "Done."
yuuji@779 177 else
yuuji@779 178 failure=-1
yuuji@779 179 echo "Replacing failed."
yuuji@779 180 query "ROLLBACK;"
yuuji@779 181 fi
yuuji@779 182 else
yuuji@779 183 failure=$((failure + 1))
yuuji@779 184 echo "Fail!"
yuuji@779 185 query "ROLLBACK;"
yuuji@779 186 fi
yuuji@779 187 done
yuuji@779 188
yuuji@780 189 err "Migration ended at `date` with failure=$failure"
yuuji@779 190 return $failure