s4

view s4-migrate.sh @ 784:f9702e2c6591

find changesets by author, revision, files, or words in the commit message
Ensure to roder by rowid
author HIROSE Yuuji <yuuji@gentei.org>
date Sat, 13 Jun 2020 07:49:52 +0900
parents b23f26406690
children 809caeb20758
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 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 # article
97 d3=$(query <<-EOF
98 SELECT * FROM main.article
99 NATURAL LEFT JOIN main.article_s
100 NATURAL LEFT JOIN main.article_m
101 WHERE blogid IN ($blogs)
102 EXCEPT
103 SELECT * FROM dst.article
104 NATURAL LEFT JOIN dst.article_s
105 NATURAL LEFT JOIN dst.article_m
106 WHERE blogid IN ($blogs);
107 EOF
108 )
109 if [ -z "$d1$d2$d3" ]; then
110 echo "Copying done, rewriting article links..."
111 echo "Old URL: $URL"
112 echo "New URL: $dsturl"
113 query <<-EOF
114 UPDATE dst.article_s
115 SET val=replace(val, '$URL', '$dsturl')
116 WHERE key='text' AND val LIKE '|${URL}|' ESCAPE '|';
117 UPDATE dst.blog_s
118 SET val=replace(val, '$URL', '$dsturl')
119 WHERE key='heading' AND val LIKE '|${URL}|' ESCAPE '|';
120 EOF
121 # Create blog-rowid conversion table
122 sedfile=$tmpd/arttrans.sed
123 sedfile=tmp/arttrans.sed
124 query <<-EOF > $sedfile
125 WITH arttrans AS (
126 SELECT s.rowid srcrid, d.rowid dstrid
127 FROM main.article s JOIN dst.article d ON s.id=d.id
128 WHERE s.id in (SELECT id
129 FROM article WHERE blogid IN ($blogs))
130 ) SELECT printf("/^>/s/\#%s\([^0-9]*\)/\#%s\1/g", srcrid, dstrid)
131 FROM arttrans;
132 EOF
133 query <<-EOF > $tmpd/repl.art.rowid
134 SELECT rowid FROM dst.article_s
135 WHERE key='text' AND val GLOB '>*#[1-9]*'
136 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
137 EOF
138 sql=$tmpd/update.sql
139 for arid in `cat $tmpd/repl.art.rowid`; do
140 newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \
141 | unhexize | sed -f "$sedfile" | hexize`
142 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
143 done
144 # Rewrite blog-links in the group
145 # Create sed script
146 sedfile2=${sedfile}2
147 query <<-EOF > $sedfile2
148 WITH blogtrans AS (
149 SELECT s.rowid srcrid, d.rowid dstrid
150 FROM main.blog s JOIN dst.blog d ON s.id=d.id
151 WHERE s.id IN ($blogs)
152 ) SELECT printf('s/\(\?replyblog\)\+%s\([^0-9]*\)/\1+%s\2/g',
153 srcrid, dstrid)
154 FROM blogtrans;
155 EOF
156 bloglinks=$tmpd/bloglinks.rowid
157 query <<-EOF > $bloglinks
158 SELECT rowid FROM dst.article_s
159 WHERE key='text' AND val LIKE '%?replyblog+%'
160 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
161 EOF
162 for arid in `cat $bloglinks`; do
163 newval=`query "SELECT hex(replace(val, '$URL', '$dsturl'))
164 FROM dst.article_s WHERE rowid=$arid;" \
165 | unhexize | sed -f "$sedfile2" | hexize`
166 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
167 done
168 if query ".read $sql"; then
169 echo "Success!!"
170 query <<-EOF
171 DELETE FROM main.article WHERE blogid IN ($blogs);
172 DELETE FROM main.blog WHERE id IN ($blogs);
173 DELETE FROM main.grp WHERE rowid=$grid;
174 EOF
175 query "END;"
176 echo "Done."
177 else
178 failure=-1
179 echo "Replacing failed."
180 query "ROLLBACK;"
181 fi
182 else
183 failure=$((failure + 1))
184 echo "Fail!"
185 query "ROLLBACK;"
186 fi
187 done
188
189 err "Migration ended at `date` with failure=$failure"
190 return $failure