1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/y4-init.sh	Sun Jul 19 13:51:41 2015 +0900
     1.3 @@ -0,0 +1,101 @@
     1.4 +#!/bin/sh
     1.5 +. `dirname $0`/y4-funcs.sh
     1.6 +
     1.7 +create() {
     1.8 +  # $1=type $2=obj $3=defs...
     1.9 +  target=$1; shift
    1.10 +  name=$1; shift
    1.11 +  [ "$debug" ] && echo "create $target if not exists $name $@"
    1.12 +  sq $db "create $target if not exists $name $@"
    1.13 +}
    1.14 +create table passwd '(name primary key, pswd, gecos, skey);'
    1.15 +: <<EOF
    1.16 +
    1.17 +create table grp '(name primary key, tag text, gecos, owner, foreign key(owner) references passwd(name) on delete cascade on update cascade);'
    1.18 +# create table grp_mem '(gname, type, name, foreign key(gname) references grp(name) on delete cascade on update cascade, check (type in ("u", "g")), foreign key(name) references ug(name) on delete cascade on update cascade, unique(gname, type, name));'
    1.19 +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);
    1.20 +
    1.21 +
    1.22 +create table ug '(name primay key);'
    1.23 +create trigger p2ugin 'after insert on passwd begin insert into ug values(new.name); end;'
    1.24 +create trigger g2ugin 'after insert on grp begin insert into ug values(new.name); end;'
    1.25 +create trigger p2ugup 'after update of name on passwd begin update ug set name = new.name where name=old.name; end;'
    1.26 +create trigger g2ugup 'after update of name on grp begin update ug set name = new.name where name=old.name; end;'
    1.27 +create trigger p2ugrm 'after delete on passwd begin delete from ug where name = old.name; end;'
    1.28 +create trigger g2ugrm 'after delete on grp begin delete from ug where name = old.name; end;'
    1.29 +
    1.30 +EOF
    1.31 +
    1.32 +create table $conftbl '(tbl,col,keytype,objtype, primary key(tbl, col))'
    1.33 +
    1.34 +create_struct() {
    1.35 +  # $1=struct-def
    1.36 +  def=`basename $1`
    1.37 +  tmain=${def%.*}
    1.38 +  ts=${tmain}_s
    1.39 +  tm=${tmain}_m
    1.40 +  ct=$conftbl
    1.41 +  cat $1 | \
    1.42 +      (cols="" pkey="" uniq="" fkey="" fkey2=""
    1.43 +       while IFS=: read prompt col keytype objtype args; do
    1.44 +	 addcol=""
    1.45 +	 case $keytype in
    1.46 +	   *p*|*f*)
    1.47 +	     addcol=$col
    1.48 +	     pkey="$pkey${pkey:+, }$col"
    1.49 +	     case $keytype in
    1.50 +	       *pf*|*fp*)
    1.51 +		 fkey="${fkey}, foreign key($col) references $objtype on delete cascade on update cascade"
    1.52 +		 fkey2="${fkey2}, foreign key($col) references $objtype on delete cascade on update cascade" ;;
    1.53 +	       *p*)
    1.54 +		 fkey2="${fkey2}, foreign key($col) references $tmain($col) on delete cascade on update cascade" ;; 
    1.55 +	     esac
    1.56 +	     ;;
    1.57 +	   *u*)	uniq="$uniq${uniq:+, }$col"
    1.58 +		addcol=$col
    1.59 +		;;
    1.60 +	 esac
    1.61 +	 case $objtype in
    1.62 +	   author)
    1.63 +	     addcol=$col
    1.64 +	     fkey="${fkey}, foreign key($col) references user(name) on delete cascade on update cascade"
    1.65 +	     keytype=f   # blogの場合はuserのほうがいい??
    1.66 +	     ;;
    1.67 +	   owner)
    1.68 +	     ;;
    1.69 +	   serial)
    1.70 +	     addcol=$addcol" varchar(20)" ;;
    1.71 +	   hidden)
    1.72 +	     continue ;;
    1.73 +	   user)
    1.74 +	     
    1.75 +	     ;;
    1.76 +	   group)
    1.77 +	 esac
    1.78 +	 [ "$addcol" ] && cols="$cols${cols:+, }"$addcol
    1.79 +	 typevals="(\"/$tmain\", \"$col\", \"$keytype\", \"$objtype\")"
    1.80 +	 sq $db "replace into $ct values $typevals"
    1.81 +       done
    1.82 +       pk=${pkey:+", primary key($pkey)"}
    1.83 +       uq=${uniq:+", unique($uniq)"}
    1.84 +       create table $tmain "($cols$pk$uq$fkey)"
    1.85 +       create table $ts "($pkey, key, type, val, bin blob, primary key($pkey, key)$fkey2)"
    1.86 +       create table $tm "($pkey, key, type, val, bin blob, unique($pkey, key, val)$fkey2)"
    1.87 +      )
    1.88 +}
    1.89 +
    1.90 +create_struct $formdir/user.def
    1.91 +create_struct $formdir/grp.def
    1.92 +create_struct $formdir/grp_mem.def
    1.93 +create_struct $formdir/grp_adm.def
    1.94 +create_struct $formdir/blog.def
    1.95 +create_struct $formdir/article.def
    1.96 +create_struct $formdir/diary.def
    1.97 +
    1.98 +create table acclog "(user, tbl, rowid, time, \
    1.99 +	primary key(user, tbl, rowid), \
   1.100 +	foreign key(user) references user(name))"
   1.101 +
   1.102 +gettblcols ${1:-grp}
   1.103 +echo -n s:; gettbl_s_cols ${1:-grp}
   1.104 +echo -n m:; gettbl_m_cols ${1:-grp}