s4

annotate s4-world.sh @ 771:b97f1c84bdb1

find changesets by author, revision, files, or words in the commit message
New script s4-newworld supports NewWorld creation
author HIROSE Yuuji <yuuji@gentei.org>
date Tue, 09 Jun 2020 12:26:15 +0900
parents af0cb6f59c8e
children 567980314463
rev   line source
yuuji@734 1 #!/bin/sh
yuuji@734 2
yuuji@734 3 if [ -z "$S4MASTERDB" -o ! -s "$S4MASTERDB" ]; then
yuuji@734 4 return
yuuji@734 5 fi
yuuji@734 6 if [ "$db" -ef "$S4MASTERDB" ]; then
yuuji@734 7 return # Points to the same file
yuuji@734 8 fi
yuuji@734 9
yuuji@743 10 # Now Another world is ACTIVE
yuuji@742 11 # sessdb=`dirname $S4MASTERDB`/sess.sq3
yuuji@739 12 ## skey="skey-`basename $mydir`"
yuuji@742 13 syncflag=${db%.*}.synctime
yuuji@734 14 userupdateflag=`dirname $S4MASTERDB`/`basename $userupdateflag`
yuuji@771 15 err SYNCFLAG=$syncflag
yuuji@734 16 test ! -e "$userupdateflag" && return
yuuji@734 17 test "$syncflag" -nt "$userupdateflag" && return
yuuji@742 18
yuuji@734 19 # for sub.sq3
yuuji@734 20 #
yuuji@734 21 # main: user: 'taro', 'hanako', 'shige'
yuuji@734 22 # sub: user: 'taro', 'hanako', 'shige'
yuuji@734 23 # sub2: user_s: ('taro', 't'), ('hanako', 'h'), ('shige', 's')
yuuji@734 24 # then update
yuuji@734 25 #
yuuji@734 26
yuuji@734 27 ## sqlite3 -cmd '.timer 1' -cmd '.echo 1' $db <<EOF
yuuji@734 28 err "Starting account synchronization"
yuuji@742 29 err "db=$db mas=$S4MASTERDB sessdb=$sessdb"
yuuji@771 30 # num=$(sqlite3 -bail -cmd 'PRAGMA FOREIGN_KEYS=on' $db <<EOF
yuuji@771 31 cat > tmp/sql <<EOF
yuuji@734 32 ATTACH DATABASE "$S4MASTERDB" AS m;
yuuji@734 33 CREATE TABLE IF NOT EXISTS user(name, primary key(name));
yuuji@734 34 BEGIN;
yuuji@734 35 DElETE FROM main.user WHERE rowid NOT IN (SELECT rowid FROM m.user);
yuuji@734 36 INSERT INTO main.user(rowid, name)
yuuji@734 37 SELECT rowid, name FROM m.user
yuuji@734 38 WHERE m.user.rowid NOT IN (SELECT rowid FROM user);
yuuji@734 39 UPDATE user SET name = (SELECT name FROM m.user WHERE main.user.rowid=m.user.rowid);
yuuji@742 40 DELETE FROM main.user_s WHERE rowid NOT IN (SELECT rowid FROM m.user_s);
yuuji@742 41 REPLACE INTO main.user_s(rowid, name, key, type, val, bin)
yuuji@734 42 SELECT rowid,* FROM m.user_s;
yuuji@742 43 DELETE FROM main.user_m WHERE rowid NOT IN (SELECT rowid FROM m.user_m);
yuuji@742 44 REPLACE INTO main.user_m(rowid, name, key, type, val, bin)
yuuji@734 45 SELECT rowid,* FROM m.user_m
yuuji@734 46 WHERE key NOT LIKE '%cache%';
yuuji@734 47 END;
yuuji@734 48
yuuji@734 49 /* Compare user tables */
yuuji@734 50 WITH master AS (
yuuji@734 51 SELECT p.rowid,* FROM m.user p
yuuji@734 52 NATURAL LEFT JOIN m.user_s
yuuji@734 53 NATURAL LEFT JOIN m.user_m
yuuji@743 54 ), thisworld AS (
yuuji@734 55 SELECT p.rowid,* FROM user p
yuuji@734 56 NATURAL LEFT JOIN user_s
yuuji@734 57 NATURAL LEFT JOIN user_m
yuuji@734 58 ), m_a AS (
yuuji@743 59 SELECT * FROM master EXCEPT SELECT * FROM thisworld
yuuji@734 60 ), a_m AS (
yuuji@743 61 SELECT * FROM thisworld EXCEPT SELECT * FROM master
yuuji@734 62 ) SELECT (SELECT count(*) FROM m_a) + (SELECT count(*) FROM a_m);
yuuji@734 63
yuuji@743 64 -- SELECT * FROM thisworld;
yuuji@734 65 -- SELECT rowid,* FROM m.user LIMIT 10;
yuuji@734 66 -- SELECT rowid,* FROM user WHERE rowid NOT IN (SELECT rowid FROM m.user);
yuuji@734 67 DETACH DATABASE m;
yuuji@734 68 EOF
yuuji@771 69 num=$(sqlite3 -bail -cmd 'PRAGMA FOREIGN_KEYS=on' $db < tmp/sql )
yuuji@739 70 if [ $num -eq 0 ]; then
yuuji@739 71 touch $syncflag
yuuji@739 72 fi
yuuji@739 73 return $num