#!/bin/sh
type cgiinit >/dev/null 2>&1 || . `dirname $0`/s4-funcs.sh

create() {
  # $1=type $2=obj $3=defs...
  target=$1; shift
  name=$1; shift
  [ "$debug" ] && echo "create $target if not exists $name $@"
  sq $db "create $target if not exists $name $@"
}

create table $conftbl '(tbl,col,keytype,objtype, primary key(tbl, col))'

create_struct() {
  # $1=struct-def
  def=`basename $1`
  tmain=${def%.*}
  ts=${tmain}_s
  tm=${tmain}_m
  ct=$conftbl
  cat $1 | \
      (cols="" pkey="" uniq="" fkey="" fkey2=""
       while IFS=: read prompt col keytype objtype args; do
	 addcol=""
	 case $keytype in
	   *p*|*f*)
	     addcol=$col
	     case $keytype in
	       *pf*|*fp*)
		 pkey="$pkey${pkey:+, }$col"
		 fkey="${fkey}, foreign key($col) references $objtype on delete cascade on update cascade"
		 fkey2="${fkey2}, foreign key($col) references $objtype on delete cascade on update cascade" ;;
	       *p*)
		 pkey="$pkey${pkey:+, }$col"
		 fkey2="${fkey2}, foreign key($col) references $tmain($col) on delete cascade on update cascade" ;; 
	     esac
	     ;;
	   *u*)	uniq="$uniq${uniq:+, }$col"
		addcol=$col
		;;
	 esac
	 case $objtype in
	   author)
	     addcol=$col
	     fkey="${fkey}, foreign key($col) references user(name) on delete cascade on update cascade"
	     keytype=f   # blogの場合はuserのほうがいい??
	     ;;
	   owner)
	     ;;
	   serial)
	     addcol=$addcol" varchar(20)" ;;
	   hidden)
	     continue ;;
	   user)
	     
	     ;;
	   group)
	 esac
	 [ "$addcol" ] && cols="$cols${cols:+, }"$addcol
	 typevals="(\"/$tmain\", \"$col\", \"$keytype\", \"$objtype\")"
	 sq $db "replace into $ct values $typevals"
       done
       pk=${pkey:+", primary key($pkey)"}
       uq=${uniq:+", unique($uniq)"}
       create table $tmain "($cols$pk$uq$fkey)"
       create table $ts "($pkey, key, type, val, bin blob, primary key($pkey, key)$fkey2)"
       create table $tm "($pkey, key, type, val, bin blob, unique($pkey, key, val)$fkey2)"
      )
}

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);'
create table session '(id text primary key, expire text);'
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);'

# For compatibility
tblacc=`query "select name from sqlite_master where name='tblaccesses';"`
acc=`query "select name from sqlite_master where name='acclog';"`
create table tblaccesses "(user, tbl, tblrowid, time, \
	unique(user, tbl, tblrowid, time), \
	foreign key(user) references user(name) on delete cascade on update cascade)"
if [ -z "$tblacc" -a -n "$acc" ]; then
  echo Regenerating...
  query "INSERT INTO tblaccesses SELECT * FROM acclog;
DROP TABLE acclog;"
fi
# Ver.2 of acclog
create view acclog "AS
SELECT user, tbl, tblrowid, max(time) time FROM tblaccesses
GROUP by user, tbl, tblrowid;"

for f in $formdir/*def; do
  echo -n Creating form of $f...
  create_struct $f && echo Done || echo FAILED
done

create view gecoses "AS
SELECT rowid,
       name,
       coalesce(
         case when (SELECT name FROM user u WHERE u.name=name) IS NOT NULL
	      then (SELECT val FROM user_s u
	            WHERE u.name=a.name AND key='gecos')
	      else (SELECT val FROM grp_s g
	      	    WHERE g.gname=name AND key='gecos')
	 end,
	 name) gecos
FROM (SELECT rowid,'user' type,name FROM user
UNION ALL
SELECT rowid,'group' type, gname FROM grp) a;"