Mercurial > hgrepos > hgweb.cgi > s4
annotate s4-migrate.sh @ 792:e086e07dfc9c feature-world
Too much deletion fixed
author | HIROSE Yuuji <yuuji@gentei.org> |
---|---|
date | Sat, 13 Jun 2020 18:19:13 +0900 |
parents | 567980314463 |
children | 8448724f69e3 |
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 | |
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 | |
784 | 56 for tbl in article article_s article_m; do |
57 query <<-EOF | |
779 | 58 REPLACE INTO dst.$tbl |
59 SELECT * FROM main.$tbl | |
784 | 60 WHERE id IN (SELECT id FROM main.article WHERE blogid IN ($blogs)) |
61 ORDER BY rowid; | |
779 | 62 EOF |
63 done | |
64 ## Check the equality of two DBs | |
65 echo "grid=$grid grp=$grp qgrp=$qgrp" | htmlescape | |
66 # grp | |
67 d1=$(query <<-EOF | |
68 SELECT * FROM main.grp | |
69 NATURAL LEFT JOIN main.grp_s | |
70 NATURAL LEFT JOIN main.grp_m | |
71 WHERE gname=$qgrp | |
72 EXCEPT | |
73 SELECT * FROM dst.grp | |
74 NATURAL LEFT JOIN dst.grp_s | |
75 NATURAL LEFT JOIN dst.grp_m | |
76 WHERE gname=$qgrp; | |
77 EOF | |
78 ) | |
79 err DONE | |
80 err d1="$d1" | |
81 # blog | |
82 d2=$(query <<-EOF | |
83 SELECT * FROM main.blog | |
84 NATURAL LEFT JOIN main.blog_s | |
85 NATURAL LEFT JOIN main.blog_m | |
86 WHERE id IN (SELECT id FROM main.blog_s | |
87 WHERE key='owner' AND val=$qgrp) | |
88 EXCEPT | |
89 SELECT * FROM dst.blog | |
90 NATURAL LEFT JOIN dst.blog_s | |
91 NATURAL LEFT JOIN dst.blog_m | |
92 WHERE id IN (SELECT id FROM dst.blog_s | |
93 WHERE key='owner' AND val=$qgrp); | |
94 EOF | |
95 ) | |
786
809caeb20758
Migration caller set arguments beforehand
HIROSE Yuuji <yuuji@gentei.org>
parents:
784
diff
changeset
|
96 err d2="$d2" |
779 | 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 ) | |
786
809caeb20758
Migration caller set arguments beforehand
HIROSE Yuuji <yuuji@gentei.org>
parents:
784
diff
changeset
|
110 err d3="$d3" |
779 | 111 if [ -z "$d1$d2$d3" ]; then |
112 echo "Copying done, rewriting article links..." | |
780
b23f26406690
Migration logging and messages fixed
HIROSE Yuuji <yuuji@gentei.org>
parents:
779
diff
changeset
|
113 echo "Old URL: $URL" |
779 | 114 echo "New URL: $dsturl" |
115 query <<-EOF | |
116 UPDATE dst.article_s | |
790 | 117 SET val=replace(val, |
118 '${URL}?grp+$grid', | |
119 '${dsturl}?grp+$destrowid') | |
120 WHERE key='text' AND val LIKE '%${URL}%'; | |
792 | 121 EOF |
779 | 122 # Create blog-rowid conversion table |
123 sedfile=$tmpd/arttrans.sed | |
786
809caeb20758
Migration caller set arguments beforehand
HIROSE Yuuji <yuuji@gentei.org>
parents:
784
diff
changeset
|
124 # sedfile=tmp/arttrans.sed |
779 | 125 query <<-EOF > $sedfile |
126 WITH arttrans AS ( | |
127 SELECT s.rowid srcrid, d.rowid dstrid | |
128 FROM main.article s JOIN dst.article d ON s.id=d.id | |
129 WHERE s.id in (SELECT id | |
130 FROM article WHERE blogid IN ($blogs)) | |
786
809caeb20758
Migration caller set arguments beforehand
HIROSE Yuuji <yuuji@gentei.org>
parents:
784
diff
changeset
|
131 ) SELECT printf("/^>/s/\#%s($|[ ,\#])/\#%s\1/g", srcrid, dstrid) |
779 | 132 FROM arttrans; |
133 EOF | |
134 query <<-EOF > $tmpd/repl.art.rowid | |
135 SELECT rowid FROM dst.article_s | |
136 WHERE key='text' AND val GLOB '>*#[1-9]*' | |
137 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs)); | |
138 EOF | |
139 sql=$tmpd/update.sql | |
140 for arid in `cat $tmpd/repl.art.rowid`; do | |
141 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
|
142 | unhexize | sed -Ef "$sedfile" | hexize` |
779 | 143 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql |
144 done | |
145 # Rewrite blog-links in the group | |
146 # Create sed script | |
147 sedfile2=${sedfile}2 | |
148 query <<-EOF > $sedfile2 | |
149 WITH blogtrans AS ( | |
150 SELECT s.rowid srcrid, d.rowid dstrid | |
151 FROM main.blog s JOIN dst.blog d ON s.id=d.id | |
152 WHERE s.id IN ($blogs) | |
153 ) SELECT printf('s/\(\?replyblog\)\+%s\([^0-9]*\)/\1+%s\2/g', | |
154 srcrid, dstrid) | |
155 FROM blogtrans; | |
156 EOF | |
157 bloglinks=$tmpd/bloglinks.rowid | |
158 query <<-EOF > $bloglinks | |
159 SELECT rowid FROM dst.article_s | |
160 WHERE key='text' AND val LIKE '%?replyblog+%' | |
161 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs)); | |
162 EOF | |
163 for arid in `cat $bloglinks`; do | |
164 newval=`query "SELECT hex(replace(val, '$URL', '$dsturl')) | |
165 FROM dst.article_s WHERE rowid=$arid;" \ | |
166 | unhexize | sed -f "$sedfile2" | hexize` | |
167 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql | |
168 done | |
169 if query ".read $sql"; then | |
170 echo "Success!!" | |
171 query <<-EOF | |
172 DELETE FROM main.article WHERE blogid IN ($blogs); | |
173 DELETE FROM main.blog WHERE id IN ($blogs); | |
174 DELETE FROM main.grp WHERE rowid=$grid; | |
175 EOF | |
176 query "END;" | |
791
567980314463
World and Migration structures refined
HIROSE Yuuji <yuuji@gentei.org>
parents:
790
diff
changeset
|
177 clean-orphaned |
779 | 178 echo "Done." |
179 else | |
180 failure=-1 | |
181 echo "Replacing failed." | |
182 query "ROLLBACK;" | |
183 fi | |
184 else | |
185 failure=$((failure + 1)) | |
186 echo "Fail!" | |
187 query "ROLLBACK;" | |
188 fi | |
189 done | |
190 | |
780
b23f26406690
Migration logging and messages fixed
HIROSE Yuuji <yuuji@gentei.org>
parents:
779
diff
changeset
|
191 err "Migration ended at `date` with failure=$failure" |
779 | 192 return $failure |