s4

annotate s4-init.sh @ 278:2f1607d8b56b

find changesets by author, revision, files, or words in the commit message
New access log schema started
author HIROSE Yuuji <yuuji@gentei.org>
date Tue, 02 Aug 2016 13:25:47 +0859
parents cfbedd0686dd
children 7d7b81cfd3a0
rev   line source
yuuji@0 1 #!/bin/sh
yuuji@17 2 . `dirname $0`/s4-funcs.sh
yuuji@0 3
yuuji@0 4 create() {
yuuji@0 5 # $1=type $2=obj $3=defs...
yuuji@0 6 target=$1; shift
yuuji@0 7 name=$1; shift
yuuji@0 8 [ "$debug" ] && echo "create $target if not exists $name $@"
yuuji@0 9 sq $db "create $target if not exists $name $@"
yuuji@0 10 }
yuuji@0 11
yuuji@0 12 create table $conftbl '(tbl,col,keytype,objtype, primary key(tbl, col))'
yuuji@0 13
yuuji@0 14 create_struct() {
yuuji@0 15 # $1=struct-def
yuuji@0 16 def=`basename $1`
yuuji@0 17 tmain=${def%.*}
yuuji@0 18 ts=${tmain}_s
yuuji@0 19 tm=${tmain}_m
yuuji@0 20 ct=$conftbl
yuuji@0 21 cat $1 | \
yuuji@0 22 (cols="" pkey="" uniq="" fkey="" fkey2=""
yuuji@0 23 while IFS=: read prompt col keytype objtype args; do
yuuji@0 24 addcol=""
yuuji@0 25 case $keytype in
yuuji@0 26 *p*|*f*)
yuuji@0 27 addcol=$col
yuuji@0 28 case $keytype in
yuuji@0 29 *pf*|*fp*)
yuuji@13 30 pkey="$pkey${pkey:+, }$col"
yuuji@0 31 fkey="${fkey}, foreign key($col) references $objtype on delete cascade on update cascade"
yuuji@0 32 fkey2="${fkey2}, foreign key($col) references $objtype on delete cascade on update cascade" ;;
yuuji@0 33 *p*)
yuuji@8 34 pkey="$pkey${pkey:+, }$col"
yuuji@0 35 fkey2="${fkey2}, foreign key($col) references $tmain($col) on delete cascade on update cascade" ;;
yuuji@0 36 esac
yuuji@0 37 ;;
yuuji@0 38 *u*) uniq="$uniq${uniq:+, }$col"
yuuji@0 39 addcol=$col
yuuji@0 40 ;;
yuuji@0 41 esac
yuuji@0 42 case $objtype in
yuuji@0 43 author)
yuuji@0 44 addcol=$col
yuuji@0 45 fkey="${fkey}, foreign key($col) references user(name) on delete cascade on update cascade"
yuuji@0 46 keytype=f # blogの場合はuserのほうがいい??
yuuji@0 47 ;;
yuuji@0 48 owner)
yuuji@0 49 ;;
yuuji@0 50 serial)
yuuji@0 51 addcol=$addcol" varchar(20)" ;;
yuuji@0 52 hidden)
yuuji@0 53 continue ;;
yuuji@0 54 user)
yuuji@0 55
yuuji@0 56 ;;
yuuji@0 57 group)
yuuji@0 58 esac
yuuji@0 59 [ "$addcol" ] && cols="$cols${cols:+, }"$addcol
yuuji@0 60 typevals="(\"/$tmain\", \"$col\", \"$keytype\", \"$objtype\")"
yuuji@0 61 sq $db "replace into $ct values $typevals"
yuuji@0 62 done
yuuji@0 63 pk=${pkey:+", primary key($pkey)"}
yuuji@0 64 uq=${uniq:+", unique($uniq)"}
yuuji@0 65 create table $tmain "($cols$pk$uq$fkey)"
yuuji@0 66 create table $ts "($pkey, key, type, val, bin blob, primary key($pkey, key)$fkey2)"
yuuji@0 67 create table $tm "($pkey, key, type, val, bin blob, unique($pkey, key, val)$fkey2)"
yuuji@0 68 )
yuuji@0 69 }
yuuji@0 70
yuuji@5 71 create table par '(sessid text, var text, type text, val text, primary key(sessid, var, val) foreign key(sessid) references session(id) on update cascade on delete cascade);'
yuuji@5 72 create table session '(id text primary key, expire text);'
yuuji@5 73 create table cookie '(sessid text, var text, type text, val text, primary key(sessid, var) foreign key(sessid) references session(id) on update cascade on delete cascade);'
yuuji@278 74 tblacc=`query "select name from sqlite_master where name='tblaccesses';"`
yuuji@278 75 acc=`query "select name from sqlite_master where name='acclog';"`
yuuji@278 76 echo tblacc="[$tblacc]" acc="[$acc]"
yuuji@278 77 create table tblaccesses "(user, tbl, tblrowid, time, \
yuuji@278 78 unique(user, tbl, tblrowid, time), \
yuuji@0 79 foreign key(user) references user(name))"
yuuji@278 80 if [ -z "$tblacc" -a -n "$acc" ]; then
yuuji@278 81 echo Regenerating...
yuuji@278 82 query "INSERT INTO tblaccesses SELECT * FROM acclog;
yuuji@278 83 DROP TABLE acclog;"
yuuji@278 84 fi
yuuji@278 85 create view acclog "AS
yuuji@278 86 SELECT user, tbl, tblrowid, max(time) time FROM tblaccesses
yuuji@278 87 GROUP by user, tbl, tblrowid;"
yuuji@0 88
yuuji@172 89 create view gecoses "AS
yuuji@276 90 SELECT rowid,
yuuji@276 91 name,
yuuji@172 92 coalesce(
yuuji@172 93 case when (SELECT name FROM user u WHERE u.name=name) IS NOT NULL
yuuji@172 94 then (SELECT val FROM user_s u
yuuji@172 95 WHERE u.name=a.name AND key='gecos')
yuuji@172 96 else (SELECT val FROM grp_s g
yuuji@172 97 WHERE g.gname=name AND key='gecos')
yuuji@172 98 end,
yuuji@172 99 name) gecos
yuuji@277 100 FROM (SELECT rowid,'user' type,name FROM user
yuuji@277 101 UNION ALL
yuuji@277 102 SELECT rowid,'group' type, gname FROM grp) a;"
yuuji@172 103
yuuji@93 104
yuuji@93 105 for f in $formdir/*def; do
yuuji@93 106 echo -n Creating form of $f...
yuuji@93 107 create_struct $f && echo Done || echo FAILED
yuuji@93 108 done