s4

annotate scripts/migrate-grp.sh @ 1006:4bc9494c00ff

find changesets by author, revision, files, or words in the commit message
Add clean-orphaned.sql migrate-grp.sh
author HIROSE Yuuji <yuuji@gentei.org>
date Sun, 05 Mar 2023 15:12:17 +0859
parents
children
rev   line source
yuuji@1006 1 #!/bin/sh
yuuji@1006 2 # ./migrate-grp src-conf dst-conf GrpIDs
yuuji@1006 3 # eg) ./sitemoving.rb s4-config.sh s4-world-second.sh 8
yuuji@1006 4
yuuji@1006 5 [ -f $1 ] && . ./$1
yuuji@1006 6 [ -f $2 ] && . ./$2
yuuji@1006 7 . `dirname $1`/s4-funcs.sh
yuuji@1006 8
yuuji@1006 9 dstdb=$DB
yuuji@1006 10
yuuji@1006 11 echo db=$db dstdb=$dstdb masterdb=$S4MASTERDB URL=$URL M_URL=$S4MASTERURL
yuuji@1006 12
yuuji@1006 13 shift 2
yuuji@1006 14
yuuji@1006 15 query "ATTACH DATABASE \"$S4MASTERDB\" AS src;" || abort "Cannot attach db #{dstdb}"
yuuji@1006 16 for grid; do
yuuji@1006 17 grid=$((0 + $grid))
yuuji@1006 18 echo $grid
yuuji@1006 19 gnamesql="(SELECT gname FROM src.grp WHERE rowid=$grid)"
yuuji@1006 20 echo "Copying $grid..."
yuuji@1006 21 query "BEGIN;"
yuuji@1006 22 query "REPLACE INTO main.grp SELECT * FROM src.grp WHERE rowid=$grid;"
yuuji@1006 23 for tbl in grp_s grp_m grp_mem grp_mem_s grp_mem_m \
yuuji@1006 24 grp_adm grp_adm_s grp_adm_m; do
yuuji@1006 25 query "REPLACE INTO main.$tbl SELECT * FROM src.$tbl
yuuji@1006 26 WHERE gname=$gnamesql;"
yuuji@1006 27 done
yuuji@1006 28 blogs=`query "SELECT group_concat(\"'\"||id||\"'\", ',')
yuuji@1006 29 FROM src.blog_s WHERE key='owner' AND val=$gnamesql;"`
yuuji@1006 30 echo blogs=$blogs
yuuji@1006 31 for tbl in blog blog_s blog_m; do
yuuji@1006 32 query <<-EOF
yuuji@1006 33 REPLACE INTO main.$tbl
yuuji@1006 34 SELECT * FROM src.$tbl
yuuji@1006 35 WHERE id IN ($blogs);
yuuji@1006 36 EOF
yuuji@1006 37 done
yuuji@1006 38 for blogid in $blogs; do
yuuji@1006 39 for tbl in article article_s article_m; do
yuuji@1006 40 query <<-EOF
yuuji@1006 41 REPLACE INTO main.$tbl
yuuji@1006 42 SELECT * FROM src.$tbl
yuuji@1006 43 WHERE id IN (SELECT id FROM src.article WHERE blogid IN ($blogs));
yuuji@1006 44 EOF
yuuji@1006 45 done
yuuji@1006 46 done
yuuji@1006 47 query "END;"
yuuji@1006 48 done
yuuji@1006 49 exit
yuuji@1006 50 grid = g.to_i
yuuji@1006 51 # Copy group itself
yuuji@1006 52 gname = db.execute("SELECT gname FROM main.grp WHERE rowid=?", grid)[0][0]
yuuji@1006 53 r = db.execute("SELECT gname FROM dst.grp WHERE gname=?", gname)
yuuji@1006 54 if r[0] then
yuuji@1006 55 STDERR.printf("Skipping existing group %s\n", r[0][0])
yuuji@1006 56 ####### next
yuuji@1006 57 end
yuuji@1006 58 printf("Copying group %s...\n", gname)
yuuji@1006 59 db.execute("BEGIN;");
yuuji@1006 60 db.execute(
yuuji@1006 61 'REPLACE INTO dst.grp SELECT * FROM main.grp WHERE rowid=?', grid)
yuuji@1006 62 for tbl in %w(grp_s grp_m grp_mem grp_mem_s grp_mem_m
yuuji@1006 63 grp_adm grp_adm_s grp_adm_m)
yuuji@1006 64 db.execute(
yuuji@1006 65 "REPLACE INTO dst.#{tbl}
yuuji@1006 66 SELECT * FROM main.#{tbl} WHERE gname=?", gname)
yuuji@1006 67 end
yuuji@1006 68 ## Copy articles
yuuji@1006 69 db.execute("SELECT id FROM blog_s WHERE key='owner' AND val=?",
yuuji@1006 70 gname) do |row|
yuuji@1006 71 blogid = row[0]
yuuji@1006 72 for tbl in %w(blog blog_s blog_m)
yuuji@1006 73 db.execute(
yuuji@1006 74 "REPLACE INTO dst.#{tbl}
yuuji@1006 75 SELECT * FROM main.#{tbl} WHERE id=?", blogid)
yuuji@1006 76 db.execute(
yuuji@1006 77 "SELECT id FROM article WHERE blogid=?", blogid) do |article|
yuuji@1006 78 aid = article[0]
yuuji@1006 79 for atbl in %W(article article_m)
yuuji@1006 80 db.execute(
yuuji@1006 81 "REPLACE INTO dst.#{atbl}
yuuji@1006 82 SELECT * FROM main.#{atbl} WHERE id=?", aid)
yuuji@1006 83 end
yuuji@1006 84 db.execute(
yuuji@1006 85 "SELECT id, type, key, val, bin FROM article_s WHERE id=?",
yuuji@1006 86 aid) do |a|
yuuji@1006 87 db.execute("REPLACE INTO dst.article_s VALUES(?, ?, ?, ?, ?)",
yuuji@1006 88 a[0], a[1], a[2],
yuuji@1006 89 a[3].gsub(srcurl, dsturl),
yuuji@1006 90 a[4])
yuuji@1006 91 end
yuuji@1006 92 end
yuuji@1006 93 end
yuuji@1006 94 end
yuuji@1006 95 db.execute("END;");
yuuji@1006 96
yuuji@1006 97 end
yuuji@1006 98 puts("Done.")