s4

annotate s4-migrate.sh @ 869:b2bb87a9b8a1

find changesets by author, revision, files, or words in the commit message
Supply main-db path on invocation from commandline
author HIROSE Yuuji <yuuji@gentei.org>
date Thu, 10 Sep 2020 12:11:37 +0859
parents 2eaa037f35d0
children f389a311a8d4
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@869 9 type htmlescape >/dev/null 2>&1 || DB=$srcdb . `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@865 25 if [ -z "$tmpd" ]; then
yuuji@865 26 tmpd=`tmpd=$tmpdir mktempd` # If called from command line
yuuji@865 27 if [ -z "$tmpd" -o ! -w "$tmpd" ]; then
yuuji@865 28 err "-- Cannot detect temporary directory [$tmpd] --"
yuuji@865 29 exit 2
yuuji@865 30 fi
yuuji@865 31 trap "rm -r $tmpd" EXIT INT HUP
yuuji@865 32 fi
yuuji@779 33 failure=0
yuuji@868 34 faillist=""
yuuji@779 35 for grid; do
yuuji@779 36 grid=$((0 + $grid))
yuuji@779 37 gnamesql="(SELECT gname FROM main.grp WHERE rowid=$grid)"
yuuji@779 38 grp=`query "SELECT gname FROM main.grp WHERE rowid=$grid;"`
yuuji@868 39 if [ -z "$grp" ]; then
yuuji@868 40 echo "ID=$grid not found, skipped" | html p
yuuji@868 41 continue
yuuji@868 42 fi
yuuji@779 43 htmlgrp=`echo "$grp"|htmlescape`
yuuji@779 44 qgrp=`sqlquote "$grp"`
yuuji@794 45 if [ -n "`query \"SELECT gname FROM dst.grp WHERE gname=$qgrp;\"`" ]; then
yuuji@779 46 echo "[$htmlgrp]グループがWorld[$htmlworld]にあるので中止します。"|html p
yuuji@779 47 failure=$((failure+1))
yuuji@779 48 continue
yuuji@779 49 fi
yuuji@779 50 echo "Copying $grid..."
yuuji@779 51 query "BEGIN;"
yuuji@779 52 query "REPLACE INTO dst.grp SELECT * FROM main.grp WHERE rowid=$grid;"
yuuji@779 53 destrowid=`query "SELECT last_insert_rowid();"`
yuuji@779 54 for tbl in grp_s grp_m grp_mem grp_mem_s grp_mem_m \
yuuji@779 55 grp_adm grp_adm_s grp_adm_m; do
yuuji@779 56 query "REPLACE INTO dst.$tbl SELECT * FROM main.$tbl
yuuji@779 57 WHERE gname=$gnamesql;"
yuuji@779 58 done
yuuji@779 59 blogs=`query "SELECT group_concat(\"'\"||id||\"'\", ',')
yuuji@779 60 FROM main.blog_s WHERE key='owner' AND val=$gnamesql;"`
yuuji@779 61 echo blogs=$blogs
yuuji@779 62 for tbl in blog blog_s blog_m; do
yuuji@779 63 query <<-EOF
yuuji@779 64 REPLACE INTO dst.$tbl
yuuji@779 65 SELECT * FROM main.$tbl
yuuji@779 66 WHERE id IN ($blogs);
yuuji@779 67 EOF
yuuji@779 68 done
yuuji@784 69 for tbl in article article_s article_m; do
yuuji@784 70 query <<-EOF
yuuji@779 71 REPLACE INTO dst.$tbl
yuuji@779 72 SELECT * FROM main.$tbl
yuuji@784 73 WHERE id IN (SELECT id FROM main.article WHERE blogid IN ($blogs))
yuuji@784 74 ORDER BY rowid;
yuuji@779 75 EOF
yuuji@779 76 done
yuuji@779 77 ## Check the equality of two DBs
yuuji@779 78 echo "grid=$grid grp=$grp qgrp=$qgrp" | htmlescape
yuuji@779 79 # grp
yuuji@779 80 d1=$(query <<-EOF
yuuji@779 81 SELECT * FROM main.grp
yuuji@779 82 NATURAL LEFT JOIN main.grp_s
yuuji@779 83 NATURAL LEFT JOIN main.grp_m
yuuji@779 84 WHERE gname=$qgrp
yuuji@779 85 EXCEPT
yuuji@779 86 SELECT * FROM dst.grp
yuuji@779 87 NATURAL LEFT JOIN dst.grp_s
yuuji@779 88 NATURAL LEFT JOIN dst.grp_m
yuuji@779 89 WHERE gname=$qgrp;
yuuji@779 90 EOF
yuuji@779 91 )
yuuji@779 92 err DONE
yuuji@779 93 err d1="$d1"
yuuji@779 94 # blog
yuuji@779 95 d2=$(query <<-EOF
yuuji@779 96 SELECT * FROM main.blog
yuuji@779 97 NATURAL LEFT JOIN main.blog_s
yuuji@779 98 NATURAL LEFT JOIN main.blog_m
yuuji@779 99 WHERE id IN (SELECT id FROM main.blog_s
yuuji@779 100 WHERE key='owner' AND val=$qgrp)
yuuji@779 101 EXCEPT
yuuji@779 102 SELECT * FROM dst.blog
yuuji@779 103 NATURAL LEFT JOIN dst.blog_s
yuuji@779 104 NATURAL LEFT JOIN dst.blog_m
yuuji@779 105 WHERE id IN (SELECT id FROM dst.blog_s
yuuji@779 106 WHERE key='owner' AND val=$qgrp);
yuuji@779 107 EOF
yuuji@779 108 )
yuuji@786 109 err d2="$d2"
yuuji@779 110 # article
yuuji@779 111 d3=$(query <<-EOF
yuuji@779 112 SELECT * FROM main.article
yuuji@779 113 NATURAL LEFT JOIN main.article_s
yuuji@779 114 NATURAL LEFT JOIN main.article_m
yuuji@779 115 WHERE blogid IN ($blogs)
yuuji@779 116 EXCEPT
yuuji@779 117 SELECT * FROM dst.article
yuuji@779 118 NATURAL LEFT JOIN dst.article_s
yuuji@779 119 NATURAL LEFT JOIN dst.article_m
yuuji@779 120 WHERE blogid IN ($blogs);
yuuji@779 121 EOF
yuuji@779 122 )
yuuji@786 123 err d3="$d3"
yuuji@779 124 if [ -z "$d1$d2$d3" ]; then
yuuji@821 125 echo "Copy and verification done, rewriting URL in articles..."
yuuji@780 126 echo "Old URL: $URL"
yuuji@779 127 echo "New URL: $dsturl"
yuuji@779 128 query <<-EOF
yuuji@779 129 UPDATE dst.article_s
yuuji@790 130 SET val=replace(val,
yuuji@790 131 '${URL}?grp+$grid',
yuuji@790 132 '${dsturl}?grp+$destrowid')
yuuji@790 133 WHERE key='text' AND val LIKE '%${URL}%';
yuuji@792 134 EOF
yuuji@779 135 # Create blog-rowid conversion table
yuuji@779 136 sedfile=$tmpd/arttrans.sed
yuuji@786 137 # sedfile=tmp/arttrans.sed
yuuji@779 138 query <<-EOF > $sedfile
yuuji@779 139 WITH arttrans AS (
yuuji@779 140 SELECT s.rowid srcrid, d.rowid dstrid
yuuji@779 141 FROM main.article s JOIN dst.article d ON s.id=d.id
yuuji@779 142 WHERE s.id in (SELECT id
yuuji@779 143 FROM article WHERE blogid IN ($blogs))
yuuji@795 144 ) SELECT printf("/^>/s/\#%s($|[^0-9])/\#%s\1/g", srcrid, dstrid)
yuuji@779 145 FROM arttrans;
yuuji@779 146 EOF
yuuji@779 147 query <<-EOF > $tmpd/repl.art.rowid
yuuji@779 148 SELECT rowid FROM dst.article_s
yuuji@779 149 WHERE key='text' AND val GLOB '>*#[1-9]*'
yuuji@779 150 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
yuuji@779 151 EOF
yuuji@779 152 sql=$tmpd/update.sql
yuuji@779 153 for arid in `cat $tmpd/repl.art.rowid`; do
yuuji@779 154 newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \
yuuji@786 155 | unhexize | sed -Ef "$sedfile" | hexize`
yuuji@813 156 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;"
yuuji@813 157 done >$sql
yuuji@779 158 # Rewrite blog-links in the group
yuuji@779 159 # Create sed script
yuuji@779 160 sedfile2=${sedfile}2
yuuji@779 161 query <<-EOF > $sedfile2
yuuji@779 162 WITH blogtrans AS (
yuuji@779 163 SELECT s.rowid srcrid, d.rowid dstrid
yuuji@779 164 FROM main.blog s JOIN dst.blog d ON s.id=d.id
yuuji@779 165 WHERE s.id IN ($blogs)
yuuji@795 166 ) SELECT printf('s/(\?replyblog)\+%s($|[^0-9])/\1+%s\2/g',
yuuji@779 167 srcrid, dstrid)
yuuji@779 168 FROM blogtrans;
yuuji@779 169 EOF
yuuji@779 170 bloglinks=$tmpd/bloglinks.rowid
yuuji@779 171 query <<-EOF > $bloglinks
yuuji@779 172 SELECT rowid FROM dst.article_s
yuuji@779 173 WHERE key='text' AND val LIKE '%?replyblog+%'
yuuji@779 174 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
yuuji@779 175 EOF
yuuji@779 176 for arid in `cat $bloglinks`; do
yuuji@779 177 newval=`query "SELECT hex(replace(val, '$URL', '$dsturl'))
yuuji@779 178 FROM dst.article_s WHERE rowid=$arid;" \
yuuji@795 179 | unhexize | sed -Ef "$sedfile2" | hexize`
yuuji@779 180 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
yuuji@779 181 done
yuuji@824 182 # Rewrite showattc Direct-Links
yuuji@824 183 attclinks=$(
yuuji@824 184 query <<-EOF
yuuji@824 185 SELECT group_concat(rowid, ',') FROM dst.article_s
yuuji@824 186 WHERE key='text' AND val LIKE '%?showattc+article_m+%'
yuuji@824 187 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
yuuji@824 188 EOF
yuuji@824 189 )
yuuji@824 190 if [ -n "$attclinks" ]; then
yuuji@824 191 err attclinks=$attclinks
yuuji@824 192 sedfile3=${sedfile}3
yuuji@824 193 query <<-EOF > $sedfile3
yuuji@824 194 WITH attctrans AS (
yuuji@824 195 SELECT s.rowid srcarid, d.rowid dstarid
yuuji@824 196 FROM main.article_m s NATURAL JOIN dst.article_m d
yuuji@824 197 WHERE d.id IN (SELECT id FROM dst.article
yuuji@824 198 WHERE blogid IN ($blogs))
yuuji@824 199 ) SELECT printf('s/(\?showattc\+article_m)\+%s($|[^0-9])/\1+%s\2/g',
yuuji@824 200 srcarid, dstarid)
yuuji@824 201 FROM attctrans;
yuuji@824 202 EOF
yuuji@824 203 echo $attclinks | tr , '\n' | while read arid; do
yuuji@824 204 newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \
yuuji@824 205 | unhexize | sed -Ef "$sedfile3" | hexize`
yuuji@824 206 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;"
yuuji@824 207 done >> $sql
yuuji@824 208 fi
yuuji@813 209 if [ -z "`query \".read $sql\"`" ]; then
yuuji@824 210 echo "URL rewriting done."
yuuji@824 211 echo -n "Removing the group in the old world..."
yuuji@779 212 query <<-EOF
yuuji@821 213 SAVEPOINT rmgroup;
yuuji@779 214 DELETE FROM main.article WHERE blogid IN ($blogs);
yuuji@779 215 DELETE FROM main.blog WHERE id IN ($blogs);
yuuji@779 216 DELETE FROM main.grp WHERE rowid=$grid;
yuuji@821 217 RELEASE SAVEPOINT rmgroup;
yuuji@779 218 EOF
yuuji@813 219 s=`query "SELECT * FROM main.grp WHERE rowid=$grid;"`
yuuji@813 220 if [ -z "$s" ]; then
yuuji@821 221 echo "Done."
yuuji@813 222 echo "Success!!"
yuuji@813 223 query "END;"
yuuji@813 224 clean-orphaned
yuuji@813 225 echo "Done."
yuuji@813 226 else
yuuji@813 227 echo Removal failed
yuuji@813 228 echo "現行グループ消去ができませんでした。"
yuuji@813 229 echo "書き込みの多いグループの場合は空いている時間帯に試して下さい。"
yuuji@813 230 query "ROLLBACK;"
yuuji@868 231 failure=$((failure+1))
yuuji@868 232 faillist="$faillist${faillist:+ }$grid"
yuuji@813 233 fi
yuuji@779 234 else
yuuji@868 235 failure==$((failure+1))
yuuji@779 236 echo "Replacing failed."
yuuji@779 237 query "ROLLBACK;"
yuuji@779 238 fi
yuuji@779 239 else
yuuji@779 240 failure=$((failure + 1))
yuuji@779 241 echo "Fail!"
yuuji@779 242 query "ROLLBACK;"
yuuji@779 243 fi
yuuji@779 244 done
yuuji@779 245
yuuji@780 246 err "Migration ended at `date` with failure=$failure"
yuuji@868 247 tty >/dev/null 2>&1 && test -n "$faillist" && echo "Skipped: $faillist"
yuuji@779 248 return $failure