s4
changeset 779:23be002abbaa feature-world
Add s4-migrate.sh
| author | HIROSE Yuuji <yuuji@gentei.org> |
|---|---|
| date | Thu, 11 Jun 2020 19:15:40 +0900 |
| parents | 3e6d36fa579d |
| children | b23f26406690 |
| files | s4-migrate.sh |
| diffstat | 1 files changed, 185 insertions(+), 0 deletions(-) [+] |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/s4-migrate.sh Thu Jun 11 19:15:40 2020 +0900 1.3 @@ -0,0 +1,185 @@ 1.4 +#!/bin/sh 1.5 +# s4 - migration module 1.6 +# (C)2020 by HIROSE, Yuuji 1.7 + 1.8 +srcdb=`unset DB; [ -f $1 ] && . ./$1 && echo ${DB:-db/cgi.sq3}` 1.9 +dst=`unset DB; [ -f $2 ] && . ./$2 && echo "${DB:-db/cgi.sq3}|$URL"` 1.10 +dstdb=${dst%\|*} 1.11 +dsturl=${dst##*\|} 1.12 +type htmlescape >/dev/null 2>&1 || . `dirname $1`/s4-funcs.sh 1.13 +world=${2##*-config-}; world=${world%.*} 1.14 +htmlworld=`echo "$world"|htmlescape` 1.15 + 1.16 +echo srcdb=$srcdb dstdb=$dstdb 1.17 +echo URL=$URL 1.18 +echo dstURL=$dsturl 1.19 + 1.20 +shift 2 1.21 + 1.22 +query "ATTACH DATABASE \"$dstdb\" AS dst;" || abort "Cannot attach db #{dstdb}" 1.23 + 1.24 +failure=0 1.25 +for grid; do 1.26 + grid=$((0 + $grid)) 1.27 + gnamesql="(SELECT gname FROM main.grp WHERE rowid=$grid)" 1.28 + grp=`query "SELECT gname FROM main.grp WHERE rowid=$grid;"` 1.29 + htmlgrp=`echo "$grp"|htmlescape` 1.30 + qgrp=`sqlquote "$grp"` 1.31 + if [ -n "`query 'SELECT gname FROM dst.grp WHERE gname=$qgrp;'`" ]; then 1.32 + echo "[$htmlgrp]グループがWorld[$htmlworld]にあるので中止します。"|html p 1.33 + failure=$((failure+1)) 1.34 + continue 1.35 + fi 1.36 + echo "Copying $grid..." 1.37 + query "BEGIN;" 1.38 + query "REPLACE INTO dst.grp SELECT * FROM main.grp WHERE rowid=$grid;" 1.39 + destrowid=`query "SELECT last_insert_rowid();"` 1.40 + for tbl in grp_s grp_m grp_mem grp_mem_s grp_mem_m \ 1.41 + grp_adm grp_adm_s grp_adm_m; do 1.42 + query "REPLACE INTO dst.$tbl SELECT * FROM main.$tbl 1.43 + WHERE gname=$gnamesql;" 1.44 + done 1.45 + blogs=`query "SELECT group_concat(\"'\"||id||\"'\", ',') 1.46 + FROM main.blog_s WHERE key='owner' AND val=$gnamesql;"` 1.47 + echo blogs=$blogs 1.48 + for tbl in blog blog_s blog_m; do 1.49 + query <<-EOF 1.50 + REPLACE INTO dst.$tbl 1.51 + SELECT * FROM main.$tbl 1.52 + WHERE id IN ($blogs); 1.53 + EOF 1.54 + done 1.55 + for blogid in $blogs; do 1.56 + for tbl in article article_s article_m; do 1.57 + query <<-EOF 1.58 + REPLACE INTO dst.$tbl 1.59 + SELECT * FROM main.$tbl 1.60 + WHERE id IN (SELECT id FROM main.article WHERE blogid IN ($blogs)); 1.61 + EOF 1.62 + done 1.63 + done 1.64 + ## Check the equality of two DBs 1.65 + echo "grid=$grid grp=$grp qgrp=$qgrp" | htmlescape 1.66 + # grp 1.67 + d1=$(query <<-EOF 1.68 + SELECT * FROM main.grp 1.69 + NATURAL LEFT JOIN main.grp_s 1.70 + NATURAL LEFT JOIN main.grp_m 1.71 + WHERE gname=$qgrp 1.72 + EXCEPT 1.73 + SELECT * FROM dst.grp 1.74 + NATURAL LEFT JOIN dst.grp_s 1.75 + NATURAL LEFT JOIN dst.grp_m 1.76 + WHERE gname=$qgrp; 1.77 + EOF 1.78 + ) 1.79 + err DONE 1.80 + err d1="$d1" 1.81 + # blog 1.82 + d2=$(query <<-EOF 1.83 + SELECT * FROM main.blog 1.84 + NATURAL LEFT JOIN main.blog_s 1.85 + NATURAL LEFT JOIN main.blog_m 1.86 + WHERE id IN (SELECT id FROM main.blog_s 1.87 + WHERE key='owner' AND val=$qgrp) 1.88 + EXCEPT 1.89 + SELECT * FROM dst.blog 1.90 + NATURAL LEFT JOIN dst.blog_s 1.91 + NATURAL LEFT JOIN dst.blog_m 1.92 + WHERE id IN (SELECT id FROM dst.blog_s 1.93 + WHERE key='owner' AND val=$qgrp); 1.94 + EOF 1.95 + ) 1.96 + # article 1.97 + d3=$(query <<-EOF 1.98 + SELECT * FROM main.article 1.99 + NATURAL LEFT JOIN main.article_s 1.100 + NATURAL LEFT JOIN main.article_m 1.101 + WHERE blogid IN ($blogs) 1.102 + EXCEPT 1.103 + SELECT * FROM dst.article 1.104 + NATURAL LEFT JOIN dst.article_s 1.105 + NATURAL LEFT JOIN dst.article_m 1.106 + WHERE blogid IN ($blogs); 1.107 + EOF 1.108 + ) 1.109 + if [ -z "$d1$d2$d3" ]; then 1.110 + echo "Copying done, rewriting article links..." 1.111 + echo "Old URL: $UR" 1.112 + echo "New URL: $dsturl" 1.113 + query <<-EOF 1.114 + UPDATE dst.article_s 1.115 + SET val=replace(val, '$URL', '$dsturl') 1.116 + WHERE key='text' AND val LIKE '|${URL}|' ESCAPE '|'; 1.117 + UPDATE dst.blog_s 1.118 + SET val=replace(val, '$URL', '$dsturl') 1.119 + WHERE key='heading' AND val LIKE '|${URL}|' ESCAPE '|'; 1.120 + EOF 1.121 + # Create blog-rowid conversion table 1.122 + sedfile=$tmpd/arttrans.sed 1.123 + query <<-EOF > $sedfile 1.124 + WITH arttrans AS ( 1.125 + SELECT s.rowid srcrid, d.rowid dstrid 1.126 + FROM main.article s JOIN dst.article d ON s.id=d.id 1.127 + WHERE s.id in (SELECT id 1.128 + FROM article WHERE blogid IN ($blogs)) 1.129 + ) SELECT printf("/^>/s/\#%s\([^0-9]*\)/\#%s\1/g", srcrid, dstrid) 1.130 + FROM arttrans; 1.131 + EOF 1.132 + query <<-EOF > $tmpd/repl.art.rowid 1.133 + SELECT rowid FROM dst.article_s 1.134 + WHERE key='text' AND val GLOB '>*#[1-9]*' 1.135 + AND id IN (SELECT id FROM article WHERE blogid IN ($blogs)); 1.136 + EOF 1.137 + sql=$tmpd/update.sql 1.138 + for arid in `cat $tmpd/repl.art.rowid`; do 1.139 + newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \ 1.140 + | unhexize | sed -f "$sedfile" | hexize` 1.141 + echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql 1.142 + done 1.143 + # Rewrite blog-links in the group 1.144 + # Create sed script 1.145 + sedfile2=${sedfile}2 1.146 + query <<-EOF > $sedfile2 1.147 + WITH blogtrans AS ( 1.148 + SELECT s.rowid srcrid, d.rowid dstrid 1.149 + FROM main.blog s JOIN dst.blog d ON s.id=d.id 1.150 + WHERE s.id IN ($blogs) 1.151 + ) SELECT printf('s/\(\?replyblog\)\+%s\([^0-9]*\)/\1+%s\2/g', 1.152 + srcrid, dstrid) 1.153 + FROM blogtrans; 1.154 + EOF 1.155 + bloglinks=$tmpd/bloglinks.rowid 1.156 + query <<-EOF > $bloglinks 1.157 + SELECT rowid FROM dst.article_s 1.158 + WHERE key='text' AND val LIKE '%?replyblog+%' 1.159 + AND id IN (SELECT id FROM article WHERE blogid IN ($blogs)); 1.160 + EOF 1.161 + for arid in `cat $bloglinks`; do 1.162 + newval=`query "SELECT hex(replace(val, '$URL', '$dsturl')) 1.163 + FROM dst.article_s WHERE rowid=$arid;" \ 1.164 + | unhexize | sed -f "$sedfile2" | hexize` 1.165 + echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql 1.166 + done 1.167 + if query ".read $sql"; then 1.168 + echo "Success!!" 1.169 + query <<-EOF 1.170 + DELETE FROM main.article WHERE blogid IN ($blogs); 1.171 + DELETE FROM main.blog WHERE id IN ($blogs); 1.172 + DELETE FROM main.grp WHERE rowid=$grid; 1.173 + EOF 1.174 + query "END;" 1.175 + echo "Done." 1.176 + else 1.177 + failure=-1 1.178 + echo "Replacing failed." 1.179 + query "ROLLBACK;" 1.180 + fi 1.181 + else 1.182 + failure=$((failure + 1)) 1.183 + echo "Fail!" 1.184 + query "ROLLBACK;" 1.185 + fi 1.186 +done 1.187 + 1.188 +return $failure