s4

view s4-migrate.sh @ 865:8c69e0c2e6af

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