s4

view s4-migrate.sh @ 1037:634fee6a6bd2

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