1 #!/bin/sh

2

3 if [ -z "$S4MASTERDB" -o ! -s "$S4MASTERDB" ]; then

4 return

5 fi

6 if [ "$db" -ef "$S4MASTERDB" ]; then

7 return # Points to the same file

8 fi

9

10 # Now Another world is ACTIVE

11 # sessdb=`dirname $S4MASTERDB`/sess.sq3

12 ## skey="skey-`basename $mydir`"

13 syncflag=${db%.*}.synctime

14 userupdateflag=`dirname $S4MASTERDB`/`basename $userupdateflag`

15 err SYNCFLAG=$syncflag

16 test ! -e "$userupdateflag" && return

17 test "$syncflag" -nt "$userupdateflag" && return

18

19 # for sub.sq3

20 #

21 # main: user: 'taro', 'hanako', 'shige'

22 # sub: user: 'taro', 'hanako', 'shige'

23 # sub2: user_s: ('taro', 't'), ('hanako', 'h'), ('shige', 's')

24 # then update

25 #

26

27 ## sqlite3 -cmd '.timer 1' -cmd '.echo 1' $db <<EOF

28 err "Starting account synchronization"

29 err "db=$db mas=$S4MASTERDB sessdb=$sessdb"

30 # num=$(sqlite3 -bail -cmd 'PRAGMA FOREIGN_KEYS=on' $db <<EOF

31 cat > tmp/sql <<EOF

32 ATTACH DATABASE "$S4MASTERDB" AS m;

33 CREATE TABLE IF NOT EXISTS user(name, primary key(name));

34 BEGIN;

35 DElETE FROM main.user WHERE rowid NOT IN (SELECT rowid FROM m.user);

36 INSERT INTO main.user(rowid, name)

37 SELECT rowid, name FROM m.user

38 WHERE m.user.rowid NOT IN (SELECT rowid FROM user);

39 UPDATE user SET name = (SELECT name FROM m.user WHERE main.user.rowid=m.user.rowid);

40 DELETE FROM main.user_s WHERE rowid NOT IN (SELECT rowid FROM m.user_s);

41 REPLACE INTO main.user_s(rowid, name, key, type, val, bin)

42 SELECT rowid,* FROM m.user_s;

43 DELETE FROM main.user_m WHERE rowid NOT IN (SELECT rowid FROM m.user_m);

44 REPLACE INTO main.user_m(rowid, name, key, type, val, bin)

45 SELECT rowid,* FROM m.user_m

46 WHERE key NOT LIKE '%cache%';

47 END;

48

49 /* Compare user tables */

50 WITH master AS (

51 SELECT p.rowid,* FROM m.user p

52 NATURAL LEFT JOIN m.user_s

53 NATURAL LEFT JOIN m.user_m

54 ), thisworld AS (

55 SELECT p.rowid,* FROM user p

56 NATURAL LEFT JOIN user_s

57 NATURAL LEFT JOIN user_m

58 ), m_a AS (

59 SELECT * FROM master EXCEPT SELECT * FROM thisworld

60 ), a_m AS (

61 SELECT * FROM thisworld EXCEPT SELECT * FROM master

62 ) SELECT (SELECT count(*) FROM m_a) + (SELECT count(*) FROM a_m);

63

64 -- SELECT * FROM thisworld;

65 -- SELECT rowid,* FROM m.user LIMIT 10;

66 -- SELECT rowid,* FROM user WHERE rowid NOT IN (SELECT rowid FROM m.user);

67 DETACH DATABASE m;

68 EOF

69 num=$(sqlite3 -bail -cmd 'PRAGMA FOREIGN_KEYS=on' $db < tmp/sql )

70 if [ $num -eq 0 ]; then

71 touch $syncflag

72 fi

73 return $num