Mercurial > hgrepos > hgweb.cgi > s4
annotate s4-migrate.sh @ 865:8c69e0c2e6af
Stand-alone invocation support
author | HIROSE Yuuji <yuuji@gentei.org> |
---|---|
date | Wed, 02 Sep 2020 17:09:24 +0900 |
parents | fcf485cbabc6 |
children | 2eaa037f35d0 |
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 | |
786
809caeb20758
Migration caller set arguments beforehand
HIROSE Yuuji <yuuji@gentei.org>
parents:
784
diff
changeset
|
16 err "--- Migration Started with \$1=$1 \$2=$2 at `date` ---" |
780
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 | |
865
8c69e0c2e6af
Stand-alone invocation support
HIROSE Yuuji <yuuji@gentei.org>
parents:
824
diff
changeset
|
25 if [ -z "$tmpd" ]; then |
8c69e0c2e6af
Stand-alone invocation support
HIROSE Yuuji <yuuji@gentei.org>
parents:
824
diff
changeset
|
26 tmpd=`tmpd=$tmpdir mktempd` # If called from command line |
8c69e0c2e6af
Stand-alone invocation support
HIROSE Yuuji <yuuji@gentei.org>
parents:
824
diff
changeset
|
27 if [ -z "$tmpd" -o ! -w "$tmpd" ]; then |
8c69e0c2e6af
Stand-alone invocation support
HIROSE Yuuji <yuuji@gentei.org>
parents:
824
diff
changeset
|
28 err "-- Cannot detect temporary directory [$tmpd] --" |
8c69e0c2e6af
Stand-alone invocation support
HIROSE Yuuji <yuuji@gentei.org>
parents:
824
diff
changeset
|
29 exit 2 |
8c69e0c2e6af
Stand-alone invocation support
HIROSE Yuuji <yuuji@gentei.org>
parents:
824
diff
changeset
|
30 fi |
8c69e0c2e6af
Stand-alone invocation support
HIROSE Yuuji <yuuji@gentei.org>
parents:
824
diff
changeset
|
31 trap "rm -r $tmpd" EXIT INT HUP |
8c69e0c2e6af
Stand-alone invocation support
HIROSE Yuuji <yuuji@gentei.org>
parents:
824
diff
changeset
|
32 fi |
779 | 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"` | |
794
8448724f69e3
Account sync-ing and migration fixed
HIROSE Yuuji <yuuji@gentei.org>
parents:
792
diff
changeset
|
40 if [ -n "`query \"SELECT gname FROM dst.grp WHERE gname=$qgrp;\"`" ]; then |
779 | 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 | |
784 | 64 for tbl in article article_s article_m; do |
65 query <<-EOF | |
779 | 66 REPLACE INTO dst.$tbl |
67 SELECT * FROM main.$tbl | |
784 | 68 WHERE id IN (SELECT id FROM main.article WHERE blogid IN ($blogs)) |
69 ORDER BY rowid; | |
779 | 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 ) | |
786
809caeb20758
Migration caller set arguments beforehand
HIROSE Yuuji <yuuji@gentei.org>
parents:
784
diff
changeset
|
104 err d2="$d2" |
779 | 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 ) | |
786
809caeb20758
Migration caller set arguments beforehand
HIROSE Yuuji <yuuji@gentei.org>
parents:
784
diff
changeset
|
118 err d3="$d3" |
779 | 119 if [ -z "$d1$d2$d3" ]; then |
821
2465fff63ad9
Old group deletion at migration done in a transaction
HIROSE Yuuji <yuuji@gentei.org>
parents:
813
diff
changeset
|
120 echo "Copy and verification done, rewriting URL in articles..." |
780
b23f26406690
Migration logging and messages fixed
HIROSE Yuuji <yuuji@gentei.org>
parents:
779
diff
changeset
|
121 echo "Old URL: $URL" |
779 | 122 echo "New URL: $dsturl" |
123 query <<-EOF | |
124 UPDATE dst.article_s | |
790 | 125 SET val=replace(val, |
126 '${URL}?grp+$grid', | |
127 '${dsturl}?grp+$destrowid') | |
128 WHERE key='text' AND val LIKE '%${URL}%'; | |
792 | 129 EOF |
779 | 130 # Create blog-rowid conversion table |
131 sedfile=$tmpd/arttrans.sed | |
786
809caeb20758
Migration caller set arguments beforehand
HIROSE Yuuji <yuuji@gentei.org>
parents:
784
diff
changeset
|
132 # sedfile=tmp/arttrans.sed |
779 | 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)) | |
795
6164d88fbc0e
Update replyblog URL with ERE
HIROSE Yuuji <yuuji@gentei.org>
parents:
794
diff
changeset
|
139 ) SELECT printf("/^>/s/\#%s($|[^0-9])/\#%s\1/g", srcrid, dstrid) |
779 | 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;" \ | |
786
809caeb20758
Migration caller set arguments beforehand
HIROSE Yuuji <yuuji@gentei.org>
parents:
784
diff
changeset
|
150 | unhexize | sed -Ef "$sedfile" | hexize` |
813
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
151 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" |
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
152 done >$sql |
779 | 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) | |
795
6164d88fbc0e
Update replyblog URL with ERE
HIROSE Yuuji <yuuji@gentei.org>
parents:
794
diff
changeset
|
161 ) SELECT printf('s/(\?replyblog)\+%s($|[^0-9])/\1+%s\2/g', |
779 | 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;" \ | |
795
6164d88fbc0e
Update replyblog URL with ERE
HIROSE Yuuji <yuuji@gentei.org>
parents:
794
diff
changeset
|
174 | unhexize | sed -Ef "$sedfile2" | hexize` |
779 | 175 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql |
176 done | |
824
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
177 # Rewrite showattc Direct-Links |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
178 attclinks=$( |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
179 query <<-EOF |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
180 SELECT group_concat(rowid, ',') FROM dst.article_s |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
181 WHERE key='text' AND val LIKE '%?showattc+article_m+%' |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
182 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs)); |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
183 EOF |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
184 ) |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
185 if [ -n "$attclinks" ]; then |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
186 err attclinks=$attclinks |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
187 sedfile3=${sedfile}3 |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
188 query <<-EOF > $sedfile3 |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
189 WITH attctrans AS ( |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
190 SELECT s.rowid srcarid, d.rowid dstarid |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
191 FROM main.article_m s NATURAL JOIN dst.article_m d |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
192 WHERE d.id IN (SELECT id FROM dst.article |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
193 WHERE blogid IN ($blogs)) |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
194 ) SELECT printf('s/(\?showattc\+article_m)\+%s($|[^0-9])/\1+%s\2/g', |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
195 srcarid, dstarid) |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
196 FROM attctrans; |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
197 EOF |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
198 echo $attclinks | tr , '\n' | while read arid; do |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
199 newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \ |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
200 | unhexize | sed -Ef "$sedfile3" | hexize` |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
201 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
202 done >> $sql |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
203 fi |
813
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
204 if [ -z "`query \".read $sql\"`" ]; then |
824
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
205 echo "URL rewriting done." |
fcf485cbabc6
At migration, translate direct-link to attachment files
HIROSE Yuuji <yuuji@gentei.org>
parents:
822
diff
changeset
|
206 echo -n "Removing the group in the old world..." |
779 | 207 query <<-EOF |
821
2465fff63ad9
Old group deletion at migration done in a transaction
HIROSE Yuuji <yuuji@gentei.org>
parents:
813
diff
changeset
|
208 SAVEPOINT rmgroup; |
779 | 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; | |
821
2465fff63ad9
Old group deletion at migration done in a transaction
HIROSE Yuuji <yuuji@gentei.org>
parents:
813
diff
changeset
|
212 RELEASE SAVEPOINT rmgroup; |
779 | 213 EOF |
813
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
214 s=`query "SELECT * FROM main.grp WHERE rowid=$grid;"` |
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
215 if [ -z "$s" ]; then |
821
2465fff63ad9
Old group deletion at migration done in a transaction
HIROSE Yuuji <yuuji@gentei.org>
parents:
813
diff
changeset
|
216 echo "Done." |
813
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
217 echo "Success!!" |
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
218 query "END;" |
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
219 clean-orphaned |
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
220 echo "Done." |
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
221 else |
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
222 echo Removal failed |
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
223 echo "現行グループ消去ができませんでした。" |
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
224 echo "書き込みの多いグループの場合は空いている時間帯に試して下さい。" |
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
225 query "ROLLBACK;" |
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
226 failure=-2 |
04034092338d
Removing group of previous world, confirmation added
HIROSE Yuuji <yuuji@gentei.org>
parents:
795
diff
changeset
|
227 fi |
779 | 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 | |
780
b23f26406690
Migration logging and messages fixed
HIROSE Yuuji <yuuji@gentei.org>
parents:
779
diff
changeset
|
240 err "Migration ended at `date` with failure=$failure" |
779 | 241 return $failure |