rev |
line source |
yuuji@779
|
1 #!/bin/sh
|
yuuji@779
|
2 # s4 - migration module
|
yuuji@779
|
3 # (C)2020 by HIROSE, Yuuji
|
yuuji@779
|
4
|
yuuji@779
|
5 srcdb=`unset DB; [ -f $1 ] && . ./$1 && echo ${DB:-db/cgi.sq3}`
|
yuuji@779
|
6 dst=`unset DB; [ -f $2 ] && . ./$2 && echo "${DB:-db/cgi.sq3}|$URL"`
|
yuuji@779
|
7 dstdb=${dst%\|*}
|
yuuji@779
|
8 dsturl=${dst##*\|}
|
yuuji@779
|
9 type htmlescape >/dev/null 2>&1 || . `dirname $1`/s4-funcs.sh
|
yuuji@780
|
10 case "$2" in
|
yuuji@780
|
11 s4-config.sh) world=Base ;;
|
yuuji@780
|
12 *) world=${2##*-config-}; world=${world%.*} ;;
|
yuuji@780
|
13 esac
|
yuuji@779
|
14 htmlworld=`echo "$world"|htmlescape`
|
yuuji@779
|
15
|
yuuji@786
|
16 err "--- Migration Started with \$1=$1 \$2=$2 at `date` ---"
|
yuuji@780
|
17 err srcdb=$srcdb dstdb=$dstdb
|
yuuji@780
|
18 err URL=$URL
|
yuuji@780
|
19 err dstURL=$dsturl
|
yuuji@779
|
20
|
yuuji@779
|
21 shift 2
|
yuuji@779
|
22
|
yuuji@779
|
23 query "ATTACH DATABASE \"$dstdb\" AS dst;" || abort "Cannot attach db #{dstdb}"
|
yuuji@779
|
24
|
yuuji@779
|
25 failure=0
|
yuuji@779
|
26 for grid; do
|
yuuji@779
|
27 grid=$((0 + $grid))
|
yuuji@779
|
28 gnamesql="(SELECT gname FROM main.grp WHERE rowid=$grid)"
|
yuuji@779
|
29 grp=`query "SELECT gname FROM main.grp WHERE rowid=$grid;"`
|
yuuji@779
|
30 htmlgrp=`echo "$grp"|htmlescape`
|
yuuji@779
|
31 qgrp=`sqlquote "$grp"`
|
yuuji@794
|
32 if [ -n "`query \"SELECT gname FROM dst.grp WHERE gname=$qgrp;\"`" ]; then
|
yuuji@779
|
33 echo "[$htmlgrp]グループがWorld[$htmlworld]にあるので中止します。"|html p
|
yuuji@779
|
34 failure=$((failure+1))
|
yuuji@779
|
35 continue
|
yuuji@779
|
36 fi
|
yuuji@779
|
37 echo "Copying $grid..."
|
yuuji@779
|
38 query "BEGIN;"
|
yuuji@779
|
39 query "REPLACE INTO dst.grp SELECT * FROM main.grp WHERE rowid=$grid;"
|
yuuji@779
|
40 destrowid=`query "SELECT last_insert_rowid();"`
|
yuuji@779
|
41 for tbl in grp_s grp_m grp_mem grp_mem_s grp_mem_m \
|
yuuji@779
|
42 grp_adm grp_adm_s grp_adm_m; do
|
yuuji@779
|
43 query "REPLACE INTO dst.$tbl SELECT * FROM main.$tbl
|
yuuji@779
|
44 WHERE gname=$gnamesql;"
|
yuuji@779
|
45 done
|
yuuji@779
|
46 blogs=`query "SELECT group_concat(\"'\"||id||\"'\", ',')
|
yuuji@779
|
47 FROM main.blog_s WHERE key='owner' AND val=$gnamesql;"`
|
yuuji@779
|
48 echo blogs=$blogs
|
yuuji@779
|
49 for tbl in blog blog_s blog_m; do
|
yuuji@779
|
50 query <<-EOF
|
yuuji@779
|
51 REPLACE INTO dst.$tbl
|
yuuji@779
|
52 SELECT * FROM main.$tbl
|
yuuji@779
|
53 WHERE id IN ($blogs);
|
yuuji@779
|
54 EOF
|
yuuji@779
|
55 done
|
yuuji@784
|
56 for tbl in article article_s article_m; do
|
yuuji@784
|
57 query <<-EOF
|
yuuji@779
|
58 REPLACE INTO dst.$tbl
|
yuuji@779
|
59 SELECT * FROM main.$tbl
|
yuuji@784
|
60 WHERE id IN (SELECT id FROM main.article WHERE blogid IN ($blogs))
|
yuuji@784
|
61 ORDER BY rowid;
|
yuuji@779
|
62 EOF
|
yuuji@779
|
63 done
|
yuuji@779
|
64 ## Check the equality of two DBs
|
yuuji@779
|
65 echo "grid=$grid grp=$grp qgrp=$qgrp" | htmlescape
|
yuuji@779
|
66 # grp
|
yuuji@779
|
67 d1=$(query <<-EOF
|
yuuji@779
|
68 SELECT * FROM main.grp
|
yuuji@779
|
69 NATURAL LEFT JOIN main.grp_s
|
yuuji@779
|
70 NATURAL LEFT JOIN main.grp_m
|
yuuji@779
|
71 WHERE gname=$qgrp
|
yuuji@779
|
72 EXCEPT
|
yuuji@779
|
73 SELECT * FROM dst.grp
|
yuuji@779
|
74 NATURAL LEFT JOIN dst.grp_s
|
yuuji@779
|
75 NATURAL LEFT JOIN dst.grp_m
|
yuuji@779
|
76 WHERE gname=$qgrp;
|
yuuji@779
|
77 EOF
|
yuuji@779
|
78 )
|
yuuji@779
|
79 err DONE
|
yuuji@779
|
80 err d1="$d1"
|
yuuji@779
|
81 # blog
|
yuuji@779
|
82 d2=$(query <<-EOF
|
yuuji@779
|
83 SELECT * FROM main.blog
|
yuuji@779
|
84 NATURAL LEFT JOIN main.blog_s
|
yuuji@779
|
85 NATURAL LEFT JOIN main.blog_m
|
yuuji@779
|
86 WHERE id IN (SELECT id FROM main.blog_s
|
yuuji@779
|
87 WHERE key='owner' AND val=$qgrp)
|
yuuji@779
|
88 EXCEPT
|
yuuji@779
|
89 SELECT * FROM dst.blog
|
yuuji@779
|
90 NATURAL LEFT JOIN dst.blog_s
|
yuuji@779
|
91 NATURAL LEFT JOIN dst.blog_m
|
yuuji@779
|
92 WHERE id IN (SELECT id FROM dst.blog_s
|
yuuji@779
|
93 WHERE key='owner' AND val=$qgrp);
|
yuuji@779
|
94 EOF
|
yuuji@779
|
95 )
|
yuuji@786
|
96 err d2="$d2"
|
yuuji@779
|
97 # article
|
yuuji@779
|
98 d3=$(query <<-EOF
|
yuuji@779
|
99 SELECT * FROM main.article
|
yuuji@779
|
100 NATURAL LEFT JOIN main.article_s
|
yuuji@779
|
101 NATURAL LEFT JOIN main.article_m
|
yuuji@779
|
102 WHERE blogid IN ($blogs)
|
yuuji@779
|
103 EXCEPT
|
yuuji@779
|
104 SELECT * FROM dst.article
|
yuuji@779
|
105 NATURAL LEFT JOIN dst.article_s
|
yuuji@779
|
106 NATURAL LEFT JOIN dst.article_m
|
yuuji@779
|
107 WHERE blogid IN ($blogs);
|
yuuji@779
|
108 EOF
|
yuuji@779
|
109 )
|
yuuji@786
|
110 err d3="$d3"
|
yuuji@779
|
111 if [ -z "$d1$d2$d3" ]; then
|
yuuji@779
|
112 echo "Copying done, rewriting article links..."
|
yuuji@780
|
113 echo "Old URL: $URL"
|
yuuji@779
|
114 echo "New URL: $dsturl"
|
yuuji@779
|
115 query <<-EOF
|
yuuji@779
|
116 UPDATE dst.article_s
|
yuuji@790
|
117 SET val=replace(val,
|
yuuji@790
|
118 '${URL}?grp+$grid',
|
yuuji@790
|
119 '${dsturl}?grp+$destrowid')
|
yuuji@790
|
120 WHERE key='text' AND val LIKE '%${URL}%';
|
yuuji@792
|
121 EOF
|
yuuji@779
|
122 # Create blog-rowid conversion table
|
yuuji@779
|
123 sedfile=$tmpd/arttrans.sed
|
yuuji@786
|
124 # sedfile=tmp/arttrans.sed
|
yuuji@779
|
125 query <<-EOF > $sedfile
|
yuuji@779
|
126 WITH arttrans AS (
|
yuuji@779
|
127 SELECT s.rowid srcrid, d.rowid dstrid
|
yuuji@779
|
128 FROM main.article s JOIN dst.article d ON s.id=d.id
|
yuuji@779
|
129 WHERE s.id in (SELECT id
|
yuuji@779
|
130 FROM article WHERE blogid IN ($blogs))
|
yuuji@786
|
131 ) SELECT printf("/^>/s/\#%s($|[ ,\#])/\#%s\1/g", srcrid, dstrid)
|
yuuji@779
|
132 FROM arttrans;
|
yuuji@779
|
133 EOF
|
yuuji@779
|
134 query <<-EOF > $tmpd/repl.art.rowid
|
yuuji@779
|
135 SELECT rowid FROM dst.article_s
|
yuuji@779
|
136 WHERE key='text' AND val GLOB '>*#[1-9]*'
|
yuuji@779
|
137 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
|
yuuji@779
|
138 EOF
|
yuuji@779
|
139 sql=$tmpd/update.sql
|
yuuji@779
|
140 for arid in `cat $tmpd/repl.art.rowid`; do
|
yuuji@779
|
141 newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \
|
yuuji@786
|
142 | unhexize | sed -Ef "$sedfile" | hexize`
|
yuuji@779
|
143 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
|
yuuji@779
|
144 done
|
yuuji@779
|
145 # Rewrite blog-links in the group
|
yuuji@779
|
146 # Create sed script
|
yuuji@779
|
147 sedfile2=${sedfile}2
|
yuuji@779
|
148 query <<-EOF > $sedfile2
|
yuuji@779
|
149 WITH blogtrans AS (
|
yuuji@779
|
150 SELECT s.rowid srcrid, d.rowid dstrid
|
yuuji@779
|
151 FROM main.blog s JOIN dst.blog d ON s.id=d.id
|
yuuji@779
|
152 WHERE s.id IN ($blogs)
|
yuuji@779
|
153 ) SELECT printf('s/\(\?replyblog\)\+%s\([^0-9]*\)/\1+%s\2/g',
|
yuuji@779
|
154 srcrid, dstrid)
|
yuuji@779
|
155 FROM blogtrans;
|
yuuji@779
|
156 EOF
|
yuuji@779
|
157 bloglinks=$tmpd/bloglinks.rowid
|
yuuji@779
|
158 query <<-EOF > $bloglinks
|
yuuji@779
|
159 SELECT rowid FROM dst.article_s
|
yuuji@779
|
160 WHERE key='text' AND val LIKE '%?replyblog+%'
|
yuuji@779
|
161 AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
|
yuuji@779
|
162 EOF
|
yuuji@779
|
163 for arid in `cat $bloglinks`; do
|
yuuji@779
|
164 newval=`query "SELECT hex(replace(val, '$URL', '$dsturl'))
|
yuuji@779
|
165 FROM dst.article_s WHERE rowid=$arid;" \
|
yuuji@779
|
166 | unhexize | sed -f "$sedfile2" | hexize`
|
yuuji@779
|
167 echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
|
yuuji@779
|
168 done
|
yuuji@779
|
169 if query ".read $sql"; then
|
yuuji@779
|
170 echo "Success!!"
|
yuuji@779
|
171 query <<-EOF
|
yuuji@779
|
172 DELETE FROM main.article WHERE blogid IN ($blogs);
|
yuuji@779
|
173 DELETE FROM main.blog WHERE id IN ($blogs);
|
yuuji@779
|
174 DELETE FROM main.grp WHERE rowid=$grid;
|
yuuji@779
|
175 EOF
|
yuuji@779
|
176 query "END;"
|
yuuji@791
|
177 clean-orphaned
|
yuuji@779
|
178 echo "Done."
|
yuuji@779
|
179 else
|
yuuji@779
|
180 failure=-1
|
yuuji@779
|
181 echo "Replacing failed."
|
yuuji@779
|
182 query "ROLLBACK;"
|
yuuji@779
|
183 fi
|
yuuji@779
|
184 else
|
yuuji@779
|
185 failure=$((failure + 1))
|
yuuji@779
|
186 echo "Fail!"
|
yuuji@779
|
187 query "ROLLBACK;"
|
yuuji@779
|
188 fi
|
yuuji@779
|
189 done
|
yuuji@779
|
190
|
yuuji@780
|
191 err "Migration ended at `date` with failure=$failure"
|
yuuji@779
|
192 return $failure
|