s4
changeset 1006:4bc9494c00ff
Add clean-orphaned.sql migrate-grp.sh
author | HIROSE Yuuji <yuuji@gentei.org> |
---|---|
date | Sun, 05 Mar 2023 15:12:17 +0859 |
parents | c2fc7a148d2c |
children | 1586c9a93b5b |
files | scripts/clean-orphaned.sql scripts/migrate-grp.sh |
diffstat | 2 files changed, 129 insertions(+), 0 deletions(-) [+] |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/scripts/clean-orphaned.sql Sun Mar 05 15:12:17 2023 +0859 1.3 @@ -0,0 +1,31 @@ 1.4 +-- 親のないblogを探す 1.5 + 1.6 +CREATE TEMPORARY VIEW IF NOT EXISTS orphanedblog AS 1.7 + SELECT blog.id,val FROM blog JOIN blog_s bs 1.8 + ON blog.id=bs.id AND key='owner' 1.9 + WHERE val NOT IN (SELECT gname FROM grp) 1.10 + AND val NOT IN (SELECT name FROM user); 1.11 + 1.12 +SELECT * FROM orphanedblog; 1.13 + 1.14 +SELECT id FROM orphanedblog; 1.15 + 1.16 +-- 削除処理 1.17 +SELECT count(*) FROM article; 1.18 +DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog); 1.19 +SELECT count(*) FROM article; 1.20 + 1.21 +-- 親のないarticleを探す これがFOREIGN_KEYしてなかった!!! 1.22 + 1.23 +CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS 1.24 + SELECT id FROM article 1.25 + WHERE blogid NOT IN (SELECT id FROM blog); 1.26 + 1.27 +SELECT count(*) FROM orphanedarticle; 1.28 + 1.29 +SELECT id FROM orphanedarticle; 1.30 + 1.31 +-- 削除処理 1.32 +SELECT count(*) FROM article; 1.33 +DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle); 1.34 +SELECT count(*) FROM article;
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/scripts/migrate-grp.sh Sun Mar 05 15:12:17 2023 +0859 2.3 @@ -0,0 +1,98 @@ 2.4 +#!/bin/sh 2.5 +# ./migrate-grp src-conf dst-conf GrpIDs 2.6 +# eg) ./sitemoving.rb s4-config.sh s4-world-second.sh 8 2.7 + 2.8 +[ -f $1 ] && . ./$1 2.9 +[ -f $2 ] && . ./$2 2.10 +. `dirname $1`/s4-funcs.sh 2.11 + 2.12 +dstdb=$DB 2.13 + 2.14 +echo db=$db dstdb=$dstdb masterdb=$S4MASTERDB URL=$URL M_URL=$S4MASTERURL 2.15 + 2.16 +shift 2 2.17 + 2.18 +query "ATTACH DATABASE \"$S4MASTERDB\" AS src;" || abort "Cannot attach db #{dstdb}" 2.19 +for grid; do 2.20 + grid=$((0 + $grid)) 2.21 + echo $grid 2.22 + gnamesql="(SELECT gname FROM src.grp WHERE rowid=$grid)" 2.23 + echo "Copying $grid..." 2.24 + query "BEGIN;" 2.25 + query "REPLACE INTO main.grp SELECT * FROM src.grp WHERE rowid=$grid;" 2.26 + for tbl in grp_s grp_m grp_mem grp_mem_s grp_mem_m \ 2.27 + grp_adm grp_adm_s grp_adm_m; do 2.28 + query "REPLACE INTO main.$tbl SELECT * FROM src.$tbl 2.29 + WHERE gname=$gnamesql;" 2.30 + done 2.31 + blogs=`query "SELECT group_concat(\"'\"||id||\"'\", ',') 2.32 + FROM src.blog_s WHERE key='owner' AND val=$gnamesql;"` 2.33 + echo blogs=$blogs 2.34 + for tbl in blog blog_s blog_m; do 2.35 + query <<-EOF 2.36 + REPLACE INTO main.$tbl 2.37 + SELECT * FROM src.$tbl 2.38 + WHERE id IN ($blogs); 2.39 + EOF 2.40 + done 2.41 + for blogid in $blogs; do 2.42 + for tbl in article article_s article_m; do 2.43 + query <<-EOF 2.44 + REPLACE INTO main.$tbl 2.45 + SELECT * FROM src.$tbl 2.46 + WHERE id IN (SELECT id FROM src.article WHERE blogid IN ($blogs)); 2.47 + EOF 2.48 + done 2.49 + done 2.50 + query "END;" 2.51 +done 2.52 +exit 2.53 + grid = g.to_i 2.54 + # Copy group itself 2.55 + gname = db.execute("SELECT gname FROM main.grp WHERE rowid=?", grid)[0][0] 2.56 + r = db.execute("SELECT gname FROM dst.grp WHERE gname=?", gname) 2.57 + if r[0] then 2.58 + STDERR.printf("Skipping existing group %s\n", r[0][0]) 2.59 + ####### next 2.60 + end 2.61 + printf("Copying group %s...\n", gname) 2.62 + db.execute("BEGIN;"); 2.63 + db.execute( 2.64 + 'REPLACE INTO dst.grp SELECT * FROM main.grp WHERE rowid=?', grid) 2.65 + for tbl in %w(grp_s grp_m grp_mem grp_mem_s grp_mem_m 2.66 + grp_adm grp_adm_s grp_adm_m) 2.67 + db.execute( 2.68 + "REPLACE INTO dst.#{tbl} 2.69 + SELECT * FROM main.#{tbl} WHERE gname=?", gname) 2.70 + end 2.71 + ## Copy articles 2.72 + db.execute("SELECT id FROM blog_s WHERE key='owner' AND val=?", 2.73 + gname) do |row| 2.74 + blogid = row[0] 2.75 + for tbl in %w(blog blog_s blog_m) 2.76 + db.execute( 2.77 + "REPLACE INTO dst.#{tbl} 2.78 + SELECT * FROM main.#{tbl} WHERE id=?", blogid) 2.79 + db.execute( 2.80 + "SELECT id FROM article WHERE blogid=?", blogid) do |article| 2.81 + aid = article[0] 2.82 + for atbl in %W(article article_m) 2.83 + db.execute( 2.84 + "REPLACE INTO dst.#{atbl} 2.85 + SELECT * FROM main.#{atbl} WHERE id=?", aid) 2.86 + end 2.87 + db.execute( 2.88 + "SELECT id, type, key, val, bin FROM article_s WHERE id=?", 2.89 + aid) do |a| 2.90 + db.execute("REPLACE INTO dst.article_s VALUES(?, ?, ?, ?, ?)", 2.91 + a[0], a[1], a[2], 2.92 + a[3].gsub(srcurl, dsturl), 2.93 + a[4]) 2.94 + end 2.95 + end 2.96 + end 2.97 + end 2.98 + db.execute("END;"); 2.99 + 2.100 +end 2.101 +puts("Done.")