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.")