s4

view s4-migrate.sh @ 792:e086e07dfc9c

find changesets by author, revision, files, or words in the commit message
Too much deletion fixed
author HIROSE Yuuji <yuuji@gentei.org>
date Sat, 13 Jun 2020 18:19:13 +0900
parents 567980314463
children 8448724f69e3
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 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 tbl in article article_s article_m; do
57 query <<-EOF
58 REPLACE INTO dst.$tbl
59 SELECT * FROM main.$tbl
60 WHERE id IN (SELECT id FROM main.article WHERE blogid IN ($blogs))
61 ORDER BY rowid;
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 )
96 err d2="$d2"
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 err d3="$d3"
111 if [ -z "$d1$d2$d3" ]; then
112 echo "Copying done, rewriting article links..."
113 echo "Old URL: $URL"
114 echo "New URL: $dsturl"
115 query <<-EOF
116 UPDATE dst.article_s
117 SET val=replace(val,
118 '${URL}?grp+$grid',
119 '${dsturl}?grp+$destrowid')
120 WHERE key='text' AND val LIKE '%${URL}%';
121 EOF
122 # Create blog-rowid conversion table
123 sedfile=$tmpd/arttrans.sed
124 # sedfile=tmp/arttrans.sed
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))
131 ) SELECT printf("/^>/s/\#%s($|[ ,\#])/\#%s\1/g", srcrid, dstrid)
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;" \
142 | unhexize | sed -Ef "$sedfile" | hexize`
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;"
177 clean-orphaned
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
191 err "Migration ended at `date` with failure=$failure"
192 return $failure