rev |
line source |
yuuji@536
|
1 #!/bin/sh
|
yuuji@537
|
2 type cgiinit >/dev/null 2>&1 || . `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@652
|
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@279
|
74
|
yuuji@279
|
75 # For compatibility
|
yuuji@278
|
76 tblacc=`query "select name from sqlite_master where name='tblaccesses';"`
|
yuuji@278
|
77 acc=`query "select name from sqlite_master where name='acclog';"`
|
yuuji@278
|
78 create table tblaccesses "(user, tbl, tblrowid, time, \
|
yuuji@278
|
79 unique(user, tbl, tblrowid, time), \
|
yuuji@582
|
80 foreign key(user) references user(name) on delete cascade on update cascade)"
|
yuuji@278
|
81 if [ -z "$tblacc" -a -n "$acc" ]; then
|
yuuji@278
|
82 echo Regenerating...
|
yuuji@278
|
83 query "INSERT INTO tblaccesses SELECT * FROM acclog;
|
yuuji@278
|
84 DROP TABLE acclog;"
|
yuuji@278
|
85 fi
|
yuuji@279
|
86 # Ver.2 of acclog
|
yuuji@278
|
87 create view acclog "AS
|
yuuji@278
|
88 SELECT user, tbl, tblrowid, max(time) time FROM tblaccesses
|
yuuji@278
|
89 GROUP by user, tbl, tblrowid;"
|
yuuji@0
|
90
|
yuuji@535
|
91 for f in $formdir/*def; do
|
yuuji@535
|
92 echo -n Creating form of $f...
|
yuuji@535
|
93 create_struct $f && echo Done || echo FAILED
|
yuuji@535
|
94 done
|
yuuji@535
|
95
|
yuuji@172
|
96 create view gecoses "AS
|
yuuji@276
|
97 SELECT rowid,
|
yuuji@276
|
98 name,
|
yuuji@172
|
99 coalesce(
|
yuuji@172
|
100 case when (SELECT name FROM user u WHERE u.name=name) IS NOT NULL
|
yuuji@172
|
101 then (SELECT val FROM user_s u
|
yuuji@172
|
102 WHERE u.name=a.name AND key='gecos')
|
yuuji@172
|
103 else (SELECT val FROM grp_s g
|
yuuji@172
|
104 WHERE g.gname=name AND key='gecos')
|
yuuji@172
|
105 end,
|
yuuji@172
|
106 name) gecos
|
yuuji@277
|
107 FROM (SELECT rowid,'user' type,name FROM user
|
yuuji@277
|
108 UNION ALL
|
yuuji@277
|
109 SELECT rowid,'group' type, gname FROM grp) a;"
|