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