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 skey="skey-`basename $mydir`"

11 syncflag=$dbdir/usersynced

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

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

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

15

16 # for sub.sq3

17 #

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

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

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

21 # then update

22 #

23

24 judgeequal() {

25 read num

26 test $num -eq 0 && touch $syncflag

27 return $num

28 }

29

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

31 err "Starting account synchronization"

32 err "db=$db mas=$S4MASTERDB sess=$sessdb"

33 sqlite3 -bail -cmd 'PRAGMA FOREIGN_KEYS=on' $db <<EOF | judgeequal

34 ATTACH DATABASE "$S4MASTERDB" AS m;

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

36 BEGIN;

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

38 INSERT INTO main.user(rowid, name)

39 SELECT rowid, name FROM m.user

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

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

42 DELETE FROM main.user_s;

43 INSERT INTO main.user_s(rowid, name, key, type, val, bin)

44 SELECT rowid,* FROM m.user_s;

45 DELETE FROM main.user_m;

46 INSERT INTO main.user_m(rowid, name, key, type, val, bin)

47 SELECT rowid,* FROM m.user_m

48 WHERE key NOT LIKE '%cache%';

49 END;

50

51 /* Compare user tables */

52 WITH master AS (

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

54 NATURAL LEFT JOIN m.user_s

55 NATURAL LEFT JOIN m.user_m

56 ), annex AS (

57 SELECT p.rowid,* FROM user p

58 NATURAL LEFT JOIN user_s

59 NATURAL LEFT JOIN user_m

60 ), m_a AS (

61 SELECT * FROM master EXCEPT SELECT * FROM annex

62 ), a_m AS (

63 SELECT * FROM annex EXCEPT SELECT * FROM master

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

65

66 -- SELECT * FROM annex;

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

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

69 DETACH DATABASE m;

70 EOF

71 err Done