s4

annotate s4-migrate.sh @ 795:6164d88fbc0e

find changesets by author, revision, files, or words in the commit message
Update replyblog URL with ERE
author HIROSE Yuuji <yuuji@gentei.org>
date Sun, 14 Jun 2020 09:50:30 +0900
parents 8448724f69e3
children 04034092338d
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@794 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@792 121 EOF
yuuji@779 122 # Create blog-rowid conversion table
yuuji@779 123 sedfile=$tmpd/arttrans.sed
yuuji@786 124 # sedfile=tmp/arttrans.sed
yuuji@779 125 query <<-EOF > $sedfile
yuuji@779 126 WITH arttrans AS (
yuuji@779 127 SELECT s.rowid srcrid, d.rowid dstrid
yuuji@779 128 FROM main.article s JOIN dst.article d ON s.id=d.id
yuuji@779 129 WHERE s.id in (SELECT id
yuuji@779 130 FROM article WHERE blogid IN ($blogs))
yuuji@795 131 ) SELECT printf("/^>/s/\#%s($|[^0-9])/\#%s\1/g", srcrid, dstrid)
yuuji@779 132 FROM arttrans;
yuuji@779 133 EOF
yuuji@779 134 query <<-EOF > $tmpd/repl.art.rowid
yuuji@779 135 SELECT rowid FROM dst.article_s
yuuji@779 136 WHERE key='text' AND val GLOB '>*#[1-9]*'
yuuji@779 137 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
yuuji@779 138 EOF
yuuji@779 139 sql=$tmpd/update.sql
yuuji@779 140 for arid in `cat $tmpd/repl.art.rowid`; do
yuuji@779 141 newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \
yuuji@786 142 | unhexize | sed -Ef "$sedfile" | hexize`
yuuji@779 143 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
yuuji@779 144 done
yuuji@779 145 # Rewrite blog-links in the group
yuuji@779 146 # Create sed script
yuuji@779 147 sedfile2=${sedfile}2
yuuji@779 148 query <<-EOF > $sedfile2
yuuji@779 149 WITH blogtrans AS (
yuuji@779 150 SELECT s.rowid srcrid, d.rowid dstrid
yuuji@779 151 FROM main.blog s JOIN dst.blog d ON s.id=d.id
yuuji@779 152 WHERE s.id IN ($blogs)
yuuji@795 153 ) SELECT printf('s/(\?replyblog)\+%s($|[^0-9])/\1+%s\2/g',
yuuji@779 154 srcrid, dstrid)
yuuji@779 155 FROM blogtrans;
yuuji@779 156 EOF
yuuji@779 157 bloglinks=$tmpd/bloglinks.rowid
yuuji@779 158 query <<-EOF > $bloglinks
yuuji@779 159 SELECT rowid FROM dst.article_s
yuuji@779 160 WHERE key='text' AND val LIKE '%?replyblog+%'
yuuji@779 161 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
yuuji@779 162 EOF
yuuji@779 163 for arid in `cat $bloglinks`; do
yuuji@779 164 newval=`query "SELECT hex(replace(val, '$URL', '$dsturl'))
yuuji@779 165 FROM dst.article_s WHERE rowid=$arid;" \
yuuji@795 166 | unhexize | sed -Ef "$sedfile2" | hexize`
yuuji@779 167 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
yuuji@779 168 done
yuuji@779 169 if query ".read $sql"; then
yuuji@779 170 echo "Success!!"
yuuji@779 171 query <<-EOF
yuuji@779 172 DELETE FROM main.article WHERE blogid IN ($blogs);
yuuji@779 173 DELETE FROM main.blog WHERE id IN ($blogs);
yuuji@779 174 DELETE FROM main.grp WHERE rowid=$grid;
yuuji@779 175 EOF
yuuji@779 176 query "END;"
yuuji@791 177 clean-orphaned
yuuji@779 178 echo "Done."
yuuji@779 179 else
yuuji@779 180 failure=-1
yuuji@779 181 echo "Replacing failed."
yuuji@779 182 query "ROLLBACK;"
yuuji@779 183 fi
yuuji@779 184 else
yuuji@779 185 failure=$((failure + 1))
yuuji@779 186 echo "Fail!"
yuuji@779 187 query "ROLLBACK;"
yuuji@779 188 fi
yuuji@779 189 done
yuuji@779 190
yuuji@780 191 err "Migration ended at `date` with failure=$failure"
yuuji@779 192 return $failure