Mercurial > hgrepos > hgweb.cgi > s4
diff y4-funcs.sh @ 4:6822f4362bf9
New system name declared as yas4
author | HIROSE Yuuji <yuuji@gentei.org> |
---|---|
date | Sun, 19 Jul 2015 13:51:41 +0900 |
parents | se-funcs.sh@95cebd686c97 |
children | 7ccd9c2eb97e |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/y4-funcs.sh Sun Jul 19 13:51:41 2015 +0900 @@ -0,0 +1,1807 @@ +#!/bin/sh +# Here's global variable table. Do not use this names. +# $HGid$ +myname=`basename ${SCRIPT_NAME:-$0}` +mydir=`dirname ${SCRIPT_FILENAME:-$0}` +myargs="$@" +#bindir=`(cd $mydir/bin; pwd)` +# :$bindir +PATH=/usr/local/sqlite3/bin:/usr/local/vim7/bin:/usr/local/ImageMagick/bin:$PATH +tmpdir=${TMPDIR:-tmp} +dbdir=${DBDIR:-db} +tmpfiles="" +db=${DB:-$dbdir/cgi.sq3} +admin=wwwadmin@gentei.org +templ=${TEMPL:-templ} +layout=$templ/default +formdir=$templ/form +imgdir=img +url=${URL:-"${REQUEST_SCHEME}://$HTTP_HOST$REQUEST_URI"} +urlbase=${url%%\?*} +msg=$templ/msg +timeout="+2 days" +dumpcollen=20 +thumbxy=120x120 +thumbxy=96x96 +maximagexy=1600x1600 +### maximagexy=400x400 +tconfs="" +imgcached=imgcache.`date +%Y/%m` +conftbl=_tblconf +nl=" +" +. ./y4-cgi.sh + +: <<EOF + + 資料配布、グループ管理・ML、ファイル交換、クリッカー、アンケート + レポート提出管理 +グループへの一斉送信機能は付いたが表に出てない(7/6) +ひとつのarticleをheadingにして新規ツリーを作成、あるといいかも。 + +7/19 設置 + 締切設定 + 一斉送信 + 自分の提出物リスト +7/18 ○書込著者からホームへのリンク +7/17 ○個人blogに「レポート提出用」がついたときの挙動 + ○添付ファイル回収 + ○imgcacheは別ディレクトリにしないと + .htaccess +7/15 ○レポート提出モードの表示を付ける + 管理者権限での削除? → まだいいか + +7/13 ○前回アクセス基準の新着数は欲しいなあ + ○レポート提出はどうしよう + → ○blogにモードを追加: + ○レポート提出モード + 添付ファイル (誰が見たかログ) + クリッカーは別立てメニューにしないと(管理者がON/OFF) + ○添付ファイルの読み出し権(6/22から) ← モードで対処 + + +7/9 ○管理者の追加 + △グループメンバの操作 → 要不要を吟味 + ○グループ情報編集の行先はそのグループがいい? + ○新規グループの作成はどこから入るか + △グループホームとユーザホームを揃える + +7/8 ○グループ一覧をユーザ一覧と揃える。 + +7/6の次 ○グループのconf編集の入口 + ○グループ検索 + +6/22の次 ○ホーム画面、○招待状、親記事追跡、○編集ボタン、削除ボタン、 + + +6/7の次 ○blogを作ってみる || userconfig || _mのまとめ編集(削除) +6/7の次の次 ○userconfigの画面だけ作ってみる。 + +○ 5/28の次 edittableに「削除」ボタンを足す +○6/1 par2tableを triplex 対応に + select "yuuji@gentei.org",var,"text",NULL,val from par where var in (select col from _tblconf where tbl="/user" and keytype in ('p', 's')); +→とすると 一気に + +## form.def を考えなおそう: +## userのように必須カラムを決まった位置に付ける? +## 必須カラム、owner(foreign key passwd(name)), update datetime +## ユーザ管理とグループ管理はデフォルトで持たせてしまえ + +## 縦持ちデータの入力/編集を供給する関数 single + multi +## 持てるテーブル構造はシステム標準5種 + ユーザ定義2種類 +## 1. passwd +## 2. grp +## 3. grp_mem +## 4. topic 記事のIDとなる +## 5. topic_cont 特定IDの記事の内容物 +## 6. list 繰り返し登場あり +## 7. hash 繰り返し登場なし + +## ● listの定義: +## create table list(id unique, parentID, type, value); +## ● hashの定義: +## create table hash(parentID, type, value, primary key(parentID, type)); + +## グループ属性: community, friend +## ○ blob使えるのかな。streamで行けるのか? xxdで行けた。ありがたい。 +## form-defとtableは1対1対応でいいか +## csv2sq3 で .csv.sq3 の Makefile + +## 書き込みオブジェクトとは何か? +## topic : id, belongto, title, owner, mode +## type := root | comment +## topic_cont : id, topicid(F), ppath, contenttype, filename, content, +## unique(id, filename) +## type := body(single) | attachment(multi) + +## group := name(P), tag, gecos, owner(F), mode +## tag := personal | friend | ... any string +## group_member := gname(F), type, name(F), UNIQUE(gname, type, name) +## type := "u" | "g" +## できたー! +## with recursive allmem as (select * from grp_mem where gname='bar' union all select grp_mem.* from grp_mem,allmem where allmem.name=grp_mem.gname) select * from allmem where type='u'; + ↓ + ↓以下に変更 + with recursive allmem as + (select gname,val from grp_m where gname='foo' + union all select grp_m.gname,grp_m.val from + grp_m,allmem where allmem.val=grp_m.gname) + select val from allmem where val in (select name from user); + + + with recursive allmem as + (select gname,val from grp_m where gname='foo' + union all select grp_m.gname,grp_m.val from grp_m,allmem + where allmem.val=grp_m.gname) + select a.*, coalesce(b.val,a.val) from allmem a left join grp_mem_s b + on a.gname=b.gname and a.val=b.user and b.key='email' + where a.val in (select name from user); + + +## triggerもできた。 +## 5/22から:グループ作成画面 +## 埋め込み画像 data:CONTENT-TYPE;base64,..... + +## 考え得るノードタイプ +## 日報 - 個人所属かグループ所属か +## 課題提出 - 個人所属かグループ所属か +## グループ管理 +## 個人情報管理 +## + +## 例: group:sip - topic:1:sip:Aperture:yuuji:rw +## - topic:2:sip:ISO:yuuji:rw +## topic_cont 1:1:/:body:text...Aperture +## 2:1:/1:body:text..Aperture +## 3:1:/1:attachment:binary..Aperture +## 4:1:/2:body:text..Aperture +## 5:1:/2:attachment:binary..Aperture +## 6:2:/:body:text..ISO +## 7:2:/6:body:text..ISO +## 8:2:/6:attachment:binary.. + +## ログテーブル +## time, who, action, tbl, id idなんか取れるかな + + + +■表設計 +* 3つの表に分散管理 + id格納表 + hash表 + list表 + * *_s *_m + + + +user, user_map, user_col + +■抽象エントリタイプ + * user + idとして機能 → table中の owner に自動挿入(?) + * group + 権限判定に利用 + * serial + 自動idとして機能 + * password + 入力 type=passwordで入力 + 変更 oldpasswd, password×2 で確認後修正 + * session + password認証後のセッションキーとして機能 + * text + 入力 type=text + * textarea + 入力 textarea + * image|document + 入力 type=fileで入力し、mime-typeを確認 + * owner + 入力時の $user で、外部キー制約が付く + * gowner + グループとしての所有者で、外部キー制約が付く + * timestamp + datetime() + * parent + 木構造の場合の親の位置 + * path + 木構造の場合の自分の位置 + +格納タイプ + * list + 表 parentID, key, val でUNIQUE(parentID, key, val) + * hash + 表 parentID, key, val でUNIQUE(parentID, key) + +オブジェクトタイプ + * entry + id, title, owner + * textpart + id, parentID, text + * binarypart + id, parentID, contenttype, filename, content + * content + hash(textpart), list(binarypart) + * topic + id, hash(content), list(reply) + * reply + id, parentID, content + * blog + list(entry) +blog = [topic, list(reply)] + + +blog = [ {"title" => "hoge", "owner" => "yuuji", "date" => "2015-04-27", + "text" => "hogehoge ..", + "reply" => [ {"serial" => 1, + "author" => "taro", + "date" => "2015-04-28", + "parent" => "/", + "path" => "/1", + "text" => "blah, blah, ....", + "image" => ["a.jpg", "b.jpg"] }, + {"serial" => 2, + "author" => "hanako", + "date" => "2015-04-29", + "parent" => "/", + "path" => "/2", + "text" => "blah, blah, ....", + "image" => [] }]}, + {"title" => "buha", ...} ] + + +user:= + ユーザ名(英数字):name:p:text:length="20" maxlength="40" + パスワード:pswd:s:password:length="20" maxlength="40" + 説明(日本語OK):gecos:s:text:length="20" maxlength="40" + セッションキー:skey:s:session + メイルアドレス:email:m:text:length="20" maxlength="40" + 住所:address:m:textarea:maxlength="400" + プロフィール画像:profimg:m:image:maxlength="400K" + 履歴書:profpdf:m:document:maxlength="4M" + +変換表 +/user/email=m + +blog:= + シリアル:id:p:serial + タイトル:title:s:text: + 所有者:owner:s:owner: + 時刻:ctime:s:stamp: + リード文:heading:s:textarea: + リプライ:reply:m:*article: + +article:= + シリアル:id:p:serial + 筆者:author:s:owner + 時刻:ctime:s:stamp: + 参照元:parent:s:parent: + パス:path:s:path: + 本文:text:s:textarea: + 画像:image:m:image: + +履歴書:profpdf:m:document:maxlength="4M" + + +EOF + +sq() { + # ./args.rb -cmd ".timeout 3000" "$@" + sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000" "$@" +} +####XXXXXXXXXXXXXXXXXXXXXX 場所移動 +sqi=/tmp/sqi.$$ +sqo=/tmp/sqo.$$ +mkfifo $sqi $sqo +tail -f $sqi | sq $db & +sq3pid="`jobs -p` $!" + +query() { + cat<<EOF > $sqi +.once $sqo +$@ +EOF + cat $sqo +} +exec 3>> $tmpdir/debug.out +exec 2>> $tmpdir/error.out +err() { + echo "$@" 1>&3 +} + +_m4() { + +} +ismember() { + # $1=user, $2=group +err ismem: "select user from grp_mem where gname='$2' and user='$1';" + test -n "`query \"select user from grp_mem where gname='$2' and user='$1';\"`" +} +isuser() { # Check if $1 is a valid user + test -n "`query \"select name from user where name='$1';\"`" +} +isgroup() { # Check if $1 is a valid group + test -n "`query \"select gname from grp where gname='$1';\"`" +} +isgrpowner() { + # $1=user, $2=group + test -n "`query \"select user from grp_adm + where gname='$2' and user='$1';\"`" +} +isfilereadable() { # $1=user $2=tbl $3=rowid + # Return true if user($1) can read attachment files in tbl($2):rowid($3) + [ -z "$1" -o -z "$2" -o -z "$3" ] && return 1 # invalid argument + + # case `getvalbyid blog mode $2` in + # normal|*open*|"") return 0 ;; + # *closed*) + # owner=`getvalbyid blog owner $2` + # if isgrp $owner; then + # isgrpowner $1 $owner && return 0 || return 1 + # elif isuser $owner; then + # [ x"$1" = x"$owner" ] && return 0 || return 1 + # fi + # esac + # ↑ 要はこういう処理を↓で一気にやっている + sql="with getblog as (\ + select key,val from blog_s where id=(\ + select blogid from article where id in\ + (select id from $2 where rowid=$3))),\ + getowner as (select val from getblog where key='owner'),\ + getmode as (select val from getblog where key='mode')\ + select case\ + when (select author from article where\ + id=(select id from $2 where rowid=$3))='$1' \ + then 'author'\ + when (select val from getmode) in ('report-open', 'normal')\ + then 'open'\ + when (select val from getmode) is null \ + then 'open' + when (select val from getowner) in (select gname from grp)\ + then (select user from grp_adm where \ + gname=(select val from getowner) and \ + user='$1')\ + else 'user' end;" + err isfilereadable: sql="$sql" + # caseのネストで内側のcaseがスカラーtrueを返しても外側はtrue扱いにならない + result=`query "$sql"` + [ -n "$result" ] && return 0 + return 2 +} +linkhome() { + # $1=UserOrGroup + echo -n '<a href="?' + if isuser $1; then + err "select 'home+'||rowid from user where name='$1';" + query "select 'home+'||rowid from user where name='$1';" + else + echo -n "grp+$1" + fi + echo "\">`gecos $1`</a>" +} +acclog() { + # $1=table, $2=rowid + if [ -n "$2" ]; then + now=`date +"%F %T"` + query "replace into acclog values('$user', '$1', '$2', '$now');" + fi +} +gecos() ( + u=${1:-$user} + #gecos=`query "select val from user_s where name='$u' and key='gecos';"` + sql="select case when (select name from user where name='$u') is not null + then (select val from user_s where name='$u' and key='gecos') + when (select gname from grp where gname='$u') is not null + then (select val from grp_s where gname='$u' and key='gecos') + else '$u' + end;" + query "$sql" +) +getpar() { +err getpar: "select val from par where var='$1' and sessid='$session' $2;" + val=`query "select val from par where var='$1' and sessid='$session' $2;"` + if [ -z "$val" ]; then + val=`query "select val from cookie where var='$1' and sessid='$session' $2;"` + fi + case "$var" in + owner) + if [ x"$user" = x"$val" ]; then + echo $user; return + elif ismember $user $val; then + echo $val; return + fi ;; + esac + echo "$val" +} + +getpartype() { + query "select type from par where var='$1' and sessid='$session' $2;" +} +getparcount() { + query "select count(*) from par where var='$1' and sessid='$session' $2;" +} +getparfilename() { + # null if type of $1 is not file + (f=`query "select val from par where var='$1' and sessid='$session' and type='file' $2;"` + [ -n "$f" ] && echo $tmpdir/$f) +} +sqlquote() { + (v="$1" + case "$v" in + "") return ;; # null + "X'"*) # quoted hex string + echo $1 ;; + *\"*) # string including dbl-quote" + v=`echo "$v"|sed -e 's/\"/\"\"/g'` + echo "\"$v\"" + return ;; + *.*.*|*-*-*|*[Ee]*[Ee]*|[Ee]*|*[\ -,:-df-~]*) # string + echo "\"$v\"" + return ;; + *) + if expr "$v" : '[-0-9.Ee][-0-9.Ee]*$' >/dev/null 2>&1; then + echo $v # MAYBE numeric, maybe... + else + echo "\"$v\"" + fi ;; + esac) +} +mktempd() { + mktemp -d -p $tmpdir/ -t $session +} +getval() { + # $1=table $2=col $3(optional)=condition + case `gettbl_coltype "/$1/$2"` in + user|author) # author added 2015-06-18 for article(author) + echo "$user" ;; + stamp|datetime) + date "+%F %T" ;; + serial) + (s=`getpar serial` + if [ -n "$s" ]; then echo $s; else echo "`date +%s`x$$"; fi) ;; + *) + getpar "$2" "$3";; + esac +} + +getvalquote() { + # $1=table $2=col $3(optional)=condition + (v=`getval "$@"` + case "$v" in + "") echo NULL ;; + *) sqlquote "$v" ;; + esac) +} +getparquote() { + sqlquote `getpar $1` +} +getvalbyid() { + # $1=tbl $2=col $3=rowid $4=tmpdirForBinary + # If two or more values found, save them to $tmpd/${column}.$N and + # store the number of files into $tmpd/${column}.count and + # their each rowid stored into $tmpd/${column}.$N.rowid. + ### err gtb-$1=`gettblcols $1`, tbl=$1, col=$2, '$3'=$3 + (for c in `gettblcols $1`; do + if [ x"$2" = x"$c" ]; then + err "select $2 from $1 where rowid=$3" + ###sq $db "select $2 from $1 where rowid=$3" + query "select $2 from $1 where rowid=$3;" + return + fi + done + pk=`gettblpkey $1` +# key=`sq $db "select $pk from $1 where rowid=$3"` + key=`query "select $pk from $1 where rowid=$3;"` + ### err "select $pk from $1 where rowid=$3" - key=$key '$4(tmp)'=$4 + for kt in s m; do + td=${4:-$tmpdir} + [ -d $td ] || mkdir -p $td + t=${1}_$kt + for c in `gettbl_${kt}_cols $1`; do + vcount=1 # count(val) + if [ x"$2" = x"$c" ]; then + cond="$t where $pk=\"$key\" and key=\"$c\"" + ###val=`query "select val from $cond;"` + val=`query "select val from $cond limit 1;"` + ###type=`query "select type from $cond;"` + type=`query "select type from $cond limit 1;"` + if [ $kt = m ]; then + ###vcount=`sq $db "select count(val) from $cond"` + # Reset val to store filenames if type is string + val=`query "select val from $cond and type like 'file:%' order by rowid;"` + vcount=`query "select count(val) from $cond;"` + echo $vcount > $td/$c.count + i=0 + while [ $i -lt $vcount ]; do + slice="order by rowid limit 1 offset $i" + i=$((i+1)) + fn=$c.$i +##err td=$td, fn=$fn, type=$type, val="[$val]" + case $type in + file:*) + #file=$td/$val + file=$td/`query "select val from $cond $slice;"` +####tmp [ -s $file -a -s $td/$fn.rowid ] && continue + sq $db<<EOF | xxd -r -p > $file +.output $td/$fn.rowid +select rowid from $cond $slice; +.output $td/$fn +select val from $cond $slice; +.output $file.content-type +select substr(type, 6) from $cond $slice; +.output stdout +select quote(bin) from $cond $slice; +EOF + ## err i=$i - file=$file rowid=`cat $td/$fn.rowid` + ln $td/$fn.rowid $file.rowid 2>&3 # for convenience + case $type in + *:[Ii]mage*) mogrify -geometry $thumbxy $file ;; + ### ここのアイコンを増やしたい + *|*:[Aa]pplication*) + convert -geometry $thumbxy $imgdir/file-icon.png \ + png:- > $file + ;; + esac + ;; + *) + sq $db<<EOF +.output $td/$fn.rowid +select rowid from $cond $slice; +.output $td/$fn +select val from $cond $slice; +EOF + val=$val"`echo $fn`" # should be delimited by newline + ;; + esac + done + else + rm -f $td/$c.count + case $type in + file:*) + echo "$val" \ + | while read fn; do + file=$td/$fn + if [ ! -s $file ]; then + ## sq $db "select quote(bin) from $cond and val=\"$fn\"" \ + query "select quote(bin) from $cond and val=\"$fn\";" \ + | xxd -r -p > $file + echo ${type#file:} > $file.content-type +# err TTTTTTTTTTTTTTTT: $type + case $type in + *:[Ii]mage*) mogrify -geometry $thumbxy $file ;; + *:[Aa]pplication*) + convert -geometry $thumbxy $imgdir/file-icon.png \ + png:- > $file ;; + esac + fi + done + ;; + esac + fi + echo "$val" # Keep newlines by "" + return + fi + done + done) +} +getvalbypkey() ( + # $1=tbl $2=col $3=pkey $4=tmpdirForBinary + pk=`gettblpkey $1` + rowid=`query "select rowid from $1 where $pk='$3';"` + getvalbyid "$1" "$2" $rowid $4 +) +getvalbycond() { + # $1=tbl $2=col $3=SQL-Condition + ###rowid=`sq $db "select rowid from $1 where $3"` + rowid=`query "select rowid from $1 where $3;"` + if [ -n "$rowid" ]; then + getvalbyid "$1" "$2" $rowid "$4" + fi +} +getpwfield() { + # getpwfield user column +# val=`sqlite3 $db "select $2 from passwd where name='$1' $3"` + val=`getvalbycond user $2 "name='$1'"` + if [ -n "$val" ]; then + echo "$val" + return 0 + else + return 1 + fi +} +encode() { + if [ -z "$sha1" ]; then + if type sha1 >/dev/null 2>&1; then + sha1=sha1 + elif type sha1sum >/dev/null 2>&1; then + sha1=sha1sum + elif type gsha1sum >/dev/null 2>&1; then + sha1=gsha1sum + fi + fi + $sha1 "$@" | cut -d' ' -f1 +} +mycrypt() ( + key=$1 salt=$2 +err \$2=$2 + case $2 in + '$'*'$'*) salt=${salt#\$4\$} + salt=${salt%\$*} ;; + esac + echo -n '$4$'"$salt"'$' + echo "$salt$key" | encode +) +hexize() { + if [ -z "$hexize" ]; then + if type xxd >/dev/null 2>&1; then + hexize="xxd -p" + else + hexize="hexdump -ve '1/1 \"%.2x\"'" + fi + fi + cat $1 | $hexize | tr -d '\n' +} +percenthex() { + hexize $1 | sed 's/\(..\)/%\1/g' +} +enascii() { + if [ -z "$enascii" ]; then + if type kakasi >/dev/null 2>&1; then + enascii="kakasi -Ha -Ka -Ja -Ea -ka" + else + enascii_now=`date +%FT%T` + enascii_sed() { + nkf -Z0Z1Z2 \ + | sed -e "s/^/$enascii_now/" -e "s|[^-0-9.A-z/,()_=]|x|g" + } + enascii="enascii_sed" + fi + fi + cat "$@" | $enascii +} +gettblconf() { + if [ -z "$tconfs" ]; then +## tconfs=`sq $db \ + tconfs=`query \ + "select tbl||'/'||col||'='||keytype||'/'||objtype from $conftbl;"` + fi + # /tb1/col1=p/text /tb1/col2=s/text /tb1/col3=m/image /tb2/col1=p/text ... +} +gettblkeys() { + # $1=tbl + gettblconf + echo "$tconfs" | fgrep "/$1/" | \ + (type="" keys="" fks="" cols="" scols="" mcols="" hcols="" + while IFS='=' read tc conf; do # tc=/tb1/col1 conf=s/text + col=${tc##*/} type=${conf%%/*} + case $type in + *p*) + cols=$cols"${cols:+:}$col" + keys=$keys"${keys:+:}$col" ;; + *f*) cols=$cols"${cols:+:}$col" + fks=$fks"${fks:+:}$col" ;; + *m*) mcols=$mcols"${mcols:+:}$col" ;; + *s*) scols=$scols"${scols:+:}$col" ;; + esac + case $type in + *h*) hcols=$hcols"${hcols:+:}$col" ;; + esac + done + echo "_keys=$keys _fks=$fks _cols=$cols _scols=$scols _mcols=$mcols _hcols=$hcols") +} +gettblpkey() { + # $1=tbl + gettblkeys $1 | cut -d ' ' -f 1 | sed -e 's/.*=//' -e 's/:/ /g' +} +gettblfkey() { + (x=`gettblkeys $1` + x=${x#*_fks=} # cut before "_fks=" including + echo ${x%% *} | tr ':' ' ') +} +gettblcols() { + (x=`gettblkeys $1` + x=${x#*_cols=} # cut before "_cols=" including + echo ${x%% *} | tr ':' ' ') +} +gettbl_s_cols() { + (x=`gettblkeys $1` + x=${x#*_scols=} # cut before "_scols=" including + echo ${x%% *} | tr ':' ' ') +} +gettbl_m_cols() { + (x=`gettblkeys $1` + x=${x#*_mcols=} # cut before "_mcols=" including + echo ${x%% *} | tr ':' ' ') +} +gettbl_h_cols() { + (x=`gettblkeys $1` + x=${x#*_hcols=} # cut before "_hcols=" including + echo ${x%% *} | tr ':' ' ') +} +gettbl_coltype() ( + gettblconf + x=`echo "$tconfs"|fgrep $1=` + x=${x#*=} # cut before = + echo ${x#*/} # cut before p/ including +) +is_hidden() { + # $1=Tbl $2=col + gettblconf + x=`echo "$tconfs"|fgrep /$1/$2=` + x=${x#*=} # cut before = + x=${x%%/*} # cut after / + case $x in + *h*) return 0 ;; + *) return 1 ;; + esac +} + +dbsetbyid() { + # $1=tbl $2=id $3=col $4=val/filename - &optional - $5=content-type + (t0=$1 t=$1 p=$2 c=$3 + tsc=$t/$c val=$4 + unset primary update + gettblconf +#err tsc=$tsc, tconfs="$tconfs" + conf=`echo "$tconfs"|fgrep "$tsc"=` +#err conf=$conf + case ${conf#*=} in + p*) primary=1 ;; + f*) update=1 ;; + u*) ;; + m*) t=${t}_m;; + s*) t=${t}_s;; + esac +#err t=$t + type=text fn="" + case $conf in + */password) + type=encoded ### val=`echo $val|encode` + ;; + */image*|*/document*) + type=`file --mime-type $val` + bin="X'`hexize $val`'" + ;; + esac + pkey=`echo "$tconfs"|grep "${t0}/.*=p"|sed 1q` + pkey=${pkey#/*/} # cut $tbl/ + pkey=${pkey%=p/*} # cut =p/... -> primary key + if [ "$primary" ]; then + nulls=`echo "$tconfs"|grep "$t/.*=[fu]/"|sed 's/^.*/, NULL/'|tr -d '\n'` + ###sq $db "replace into $t values(\"$val\"$nulls)" + query "replace into $t values(\"$val\"$nulls);" + elif [ "$update" ]; then + ###sq $db "update $1 set $c=\"$val\" where $pkey=\"$p\"" + query "update $1 set $c=\"$val\" where $pkey=\"$p\";" + else +#err "replace into $t values(\"$p\", \"$c\", \"$type\", \"$val\", \"$bin\")" + ###sq $db "replace into $t values(\"$p\", \"$c\", \"$type\", \"$val\", \"$bin\")" + query "replace into $t values(\"$p\", \"$c\", \"$type\", \"$val\", \"$bin\");" + fi + ) +} +expire() ( + at="${1:-$timeout}" + FMT="${2:-%F %T}" + TZ=GMT date -d "$at" +"$FMT" +) +addsession() { + # expireをセット + # loginの先にどの画面に行くかの状態遷移表書式を決める + expire=`expire ${2:-"+1min"}` +err addsession "$1" exp=$expire + sq $db "replace into session values('$1', '$expire')" + # Remove old session parameters + now=`expire now` + sq $db "delete from session where expire < '$now'" +} +gencookie() ( + for kv; do + expire="`expire '' '%a, %d-%b-%Y %H:%M:%S GMT'`" + echo "Set-Cookie: $kv; expires=$expire" + done +) +contenttype() { + echo "Content-type: ${1:-text/html; charset=utf-8}" + contenttype() {} # Only need to work once +} +putheader() { + +} +putfooter() { + m4 -D_TITLE_="${TITLE:-$myname}" $layout/footer.m4.html +} +getcookie() ( + for kv in `echo $HTTP_COOKIE|sed 's/[;, ]/ /g'`; do + k="${kv%%=*}" + v="`echo ${kv#*=}|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`" + query "replace into cookie values('$session', '$k', 'string', \"$v\");" + done +) +genrandom() { + # $1=columns (default: 10) + dd if=/dev/urandom count=1 2>/dev/null|nkf -MB|fold -w${1:-10}|sed -n 10p +} +smail() { + # smail rcpt subj (file) + rcpt=`echo $1` # strip newlines + subj="`echo $2|nkf -jM`" + # echo rcpt=$rcpt + (m4 -D_RCPT_="$rcpt" -D_SUBJ_="$subj" -D_FROM_=$admin $msg/mail-header.m4 + cat $3 | nkf -jd ) | sendmail -f $admin $rcpt +} +setviastring() { + table=$1 + oifs="$IFS" + IFS="&" + for us in $2; do + k=${us%%=*} + v="`echo ${us#*=}|tr '%+' '= '|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`" + sq $db "replace into $table values('$session', '$k', 'string', \"$v\")"; + #echo $k=$v + done + IFS="$oifs" +} +checkdomain() ( + # Check the validity of domain by referring DNS + item=$1 + host ${item#@*} >/dev/null 2>&1 +) +pwcheck() { + # $1=passwd + dbpswd=`getpwfield $user pswd` + encpswd=`mycrypt "$1" "$dbpswd"` + err user=$user, pswd=$1, db=$dbpswd, enc=$encpswd + [ x"$dbpswd" = x"$encpswd" ] +} +mypwhash() { + mycrypt `cat` `genrandom 5` +} +wasureta() { + user=$1 + if checkdomain $user; then + echo "ユーザ名には正しいメイルアドレスが必要です。" + exit 0 + fi + newpswd=`genrandom` # newsalt=`genrandom 5` + #encpswd=`mycrypt "$newpswd" "$newsalt"` + encpswd=`echo $newpswd|mypwhash` + dbsetbyid user $user pswd "$encpswd" + m4 -D_USER_="$user" -D_PSWD_="$newpswd" -D_URL_="$url" \ + $msg/mail-newaccount.m4 \ + | smail $user "New Account" +} +checkauth() { + user=`getpar user` + skc=`getpar skey` # from cookie + [ -z "$user" ] && return 3 + skey="`getpwfield $user skey`" +err user=$user skey=$skey + if [ -n "$skey" ]; then + if [ x"$skey" = x"$skc" ]; then + return 0 + fi + fi + pswd=`getpar pswd` + if [ x"$pswd" = x"wasureta" ]; then + wasureta $user + return 1 # wasureta error + fi + # dbpswd="`sq $db \"select pswd from passwd where name='$user'\"`" + # putheader; echo; echo user=$user, db=$dbpswd, enc=$encpswd + if pwcheck "$pswd"; then + newsession=`genrandom 50` + dbsetbyid user $user skey "$newsession" + gencookie "user=$user" "skey=$newsession" + return 0 + fi + return 2 # Password mismatch +} +showlogin() { + args=`echo $myargs|tr ' ' '+'` + m4 -D_SYSNAME_="Welcome" -D_MYNAME_="$myname${args+?}$args" \ + $layout/login.m4.html + exit 0 +} +dologin() { + checkauth + st=$? + if [ $st != 0 ]; then + contenttype; echo + m4 -D_USER_="$user" -D_URL_="$url" -D_ADMIN_="$admin" \ + $msg/login-fail-$st.m4.html + showlogin # and EXIT + fi +} +cleanup() { + echo .quit > $sqi + kill $sq3pid + rm -f $sqo $sqi + rm -rf $tmpfiles +} +trap cleanup INT HUP EXIT +# trap cleanup INT HUP +cgiinit() { + session=`date +%F-$$` + tmpf=tmp/stream + tmpd=`mktempd` + tmpfiles=$tmpfiles" $tmpd" + addsession $session + getcookie + case "$REQUEST_METHOD" in + get|GET) s="$QUERY_STRING" ;; + post|POST) dd count=$CONTENT_LENGTH bs=1 of=$tmpf 2>/dev/null + # cat > $tmpf + s="`cat tmp/stream`" + tmpfiles=$tmpfiles"${tmpfiles+ }$tmpf" + ;; + esac + case "$CONTENT_TYPE" in + *boundary*) + bndry=${CONTENT_TYPE#*boundary=} + for us in `LC_CTYPE=C ./mpsplit.rb "$bndry" $tmpdir < $tmpf` + do + k=${us%%\=*} + #echo u=$us + # v="`echo ${us#*=}|nkf -Ww -mQ|sed -e 's/=/=3d/g' -e 's/\"/=22/g'`" + v="`echo ${us#*=}|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`" + # err k=$k v=$v + case "$k" in + *:filename) + type='file'; k=${k%:filename} + case `file --mime-type $tmpdir/$v|cut -d' ' -f2` in + [Ii]mage/*) + mogrify -resize $maximagexy'>' $tmpdir/$v + ;; + esac + ;; + *) + type='string' + ;; + esac + sq $db "replace into par values('$session', '$k', '$type', \"$v\")" + done + ;; + *) + setviastring par "$s" + ;; + esac +} +collectemail() { + # Collect email addresses for group $1 + if isuser "$1"; then + em=`query "select val from user_m where name='$1' and key='email';"` + [ -n "$em" ] && echo "$em" || echo "$1" + else + query "with recursive allmem as + (select gname,val from grp_m where gname='$1' + union all select grp_m.gname,grp_m.val from grp_m,allmem + where allmem.val=grp_m.gname) + select coalesce(b.val,a.val) + from allmem a left join grp_mem_s b + on a.gname=b.gname and a.val=b.user and b.key='email' + where a.val in (select name from user) limit 10000;" + fi +} +sendinvitation() ( + # $1=email + iss="invite-`date +%s`-$(genrandom 10)" + addsession $iss +7days # 1 week due date + query "replace into par values('$iss', 'invite', 'string', \"$1\");" + gecos=`gecos` + name=$user"${gecos:+($gecos)}" + regist="$urlbase?reg+$iss" + m4 -D_URL_="$urlbase" \ + -D_USER_="$name" \ + -D_EMAIL_="$1" \ + -D_REGIST_="$regist" \ + -D_ADMIN_="$admin" \ + $msg/mail-invite.m4 \ + | smail $1 "BBSへの御招待" + return 0 +) +emaildomaincheck() { + case "$1" in + *@*@*) echo "無効なアドレスです"; return 1 ;; + *@*) + local=${1%@*} domain=${1#*@} + if ! host $domain >/dev/null 2>&1; then + echo "ドメイン($domain)が見付かりません。" + return 2 + fi + return 0 + ;; + *) echo "正しいメイルアドレスをいれてください"; return 3 ;; + esac +} +invite() { + email=`getpar email` + case $email in + *@*@*) repo="無効なアドレスです" ;; + *@*) + local=${email%@*} domain=${email#*@} + if ! repo=`emaildomaincheck $email`; then + repo="招待アドレスのエラー: $repo" + elif [ -n "`query \"select * from user where name='$email';\"`" ]; then + repo="$email さんは既に加入しています。" + elif sendinvitation $email; then + repo="アドレス($email)宛に案内を送信しました。" + fi ;; + "") repo="招待したい人のメイルアドレスを入力してください。" ;; + *) repo="無効なアドレスです" ;; + esac + m4 -D_REPORT_="$repo" -D_ACTION_="$myname?invite" \ + $layout/html.m4.html $layout/invite.m4.html +} +regist() { + # $1=session-id-for-invitation + m4 -D_TITLE_="Invitation" $layout/html.m4.html + if [ -z "$1" ]; then + echo "<p>bye bye</p>" + reutrn + fi + email=`session=$1 getpar invite` + if [ -z "$email" ];then + cat<<EOF +<p>無効な招待状チケットです。</p> +<p>招待状の有効期限(1週間)が切れているか、チケット番号が異なっています。 +加入している人に、再度招待してもらいましょう。</p> +EOF + reutrn + fi + echo "<h2>$email さんようこそ</h2>" + query "replace into user values('$email');" + # Fake login password to wasureta + query "replace into par values('$session', 'pswd', 'string', 'wasureta'), +('$session', 'user', 'string', '$email');" + wasureta $email + m4 -D_SYSNAME_="Initial Login" -D_MYNAME_="$myname?userconf" \ + $layout/login.m4.html + return +} +userconf() { + m4 -D_BODYCLASS_=userconf -D_TITLE_="ユーザ情報編集" $layout/html.m4.html + GF_ACTION="?home" edittable "$formdir/user.def" "user" "$user" +} +groupconf() { + m4 -D_BODYCLASS_=groupconf -D_TITLE_="グループ情報編集" $layout/html.m4.html + rowid=`query "select rowid from grp where gname='$1';"` +err gcon \$1=$1 rowid=$rowid + GF_ACTION="?grp+$1" edittable "$formdir/grp.def" "grp" "$rowid" +} +showhome() { + # $1=userRowIdToShow +err showhome \$1=$1 + case "$1" in + *@*) uname=`getvalbypkey user name "$1"` ;; + *) uname=`getvalbyid user name $1` ;; + esac +err ShowHome: uname=$uname + gecos=`gecos "$uname"` + GF_VIEWONLY=1 + cond="gname in (select gname from grp_mem where user='$uname')" + if [ x"$user" = x"$uname" ]; then + conflink="<a href=\"?userconf\">プロフィールの編集</a> / + <a href=\"?blog\">新規話題の作成</a>" + fi + . ./y4-blog.sh + + m4 -D_BODYCLASS_=home -D_TITLE_="$gecos さん" \ + -D_PROFILE_="`viewtable $formdir/user.def user $1`$conflink" \ + -D_BLOGS_="`listblog $uname`" \ + -D_GROUPS_="`listgroupbytable $formdir/grp.def $cond`" \ + $layout/html.m4.html $layout/home.m4.html + + # + # Record access log + [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1 +} + +listgroupbytable() { + # $1=deffile + tagline=`grep :tag: $1`; shift + and="${1:+and }" + href="<a href=\"$myname?grp+" + echo '<div class="listgroup">' +err ListGRP: query "select val from grp_s where key='tag' $and$* group by val;" + for tag in `query "select val from grp_s where key='tag' $and$* group by val;"` + do +err ListGrp: tag=$tag + tn=${tagline%%=${tag}*} + tn=${tn##*[ :]} + sql="select gname as 'グループ名',説明 from (select gname,max(case key when 'gecos' then val end) as '説明',max(case key when 'tag' then val end) as 'tag' from grp_s group by gname having tag='$tag');" + err PersonalGroupList= `echo $sql` + echo "<h2>$tn</h2>" + echo '<table class="b listgroup">' + sq -header -html $db "$sql" \ + | sed "s,\(<TR><TD>\)\([-_A-z0-9]*\)</TD>,\1$href\2\">\2</a>," + echo '</table>' + done + echo '</div>' +} +iconhref() ( + # $1=icon-file, $2=Href $3=title $4...=anchor + data=`percenthex $1` + ct=`file --mime-type $1|cut -d' ' -f2` +err iconhref: \$1=$1 \$2=$2 \$3="$@" + href=$2; title=$3; shift 3 + echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>" +) +iconhref2() ( + # $1=icon-file, $2=Href $3=title $4...=anchor + src=$1 + href=$2; title=$3; shift 3 + echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>" +) +listentry() ( + # $1=user/group $2=SearchKeyword $3=condition(if any) + # Referring variable $iamowner=$grp to attach owner-request links +err listentry: \$1=$1 \$2=$2 \$3=$3 + cond= + offset=`getpar offset` + offset=${offset%%[!0-9]*} + offset=$((offset + 0)) # change to numeric forcibly + [ $offset -lt 0 ] && offset=0 + limit=30 + dir=$tmpdir/$imgcached/$thumbxy.`echo $1|md5` + if [ x"$1" = x"user" ]; then + hrb="$myname?home" + deficon=person-default.png + entity="ユーザ" tbl=user link=rowid nm=name stage=mems + else # if group + hrb="$myname?grp" + deficon=person-default.png + entity="グループ" tbl=grp link=gname nm=gname stage=grps + tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-` + if [ -n "$tagline" ]; then + tagconv=`echo $tagline|sed 's/\([^= :]*\)=\([^= :]*\)/-D\2=\1/g'` +err tagconv=$tagconv + fi + fi + if [ ! -d $dir ]; then + mkdir -p $dir + convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon + fi + if [ -n "$2" ]; then + cond="where nick like '%$2%' or b.name like '%$2%'" + fi + + # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08 + sql="select a.rowid, a.$link, coalesce(b.gecos, a.$nm) as nick, b.tag, + case when '$iamowner' = '' then '' + when a.$nm in (select user from grp_adm + where gname='$grp') then '(管理者)' + else ',not='||a.rowid end as ownerlink + from $tbl a left join + (select $nm as name, + max(case key when 'gecos' then val end) as gecos, + max(case key when 'tag' then val end) as tag + from ${tbl}_s group by $nm) + b on a.$nm=b.name $cond $3 + order by b.tag desc, a.rowid asc" + total=`query "with x as ($sql) select count(*) from x;"` + echo "<h2>${entity} 一覧</h2>" + if [ $total -gt $limit ]; then + echo '<div class="right">' + cgi_form $stage <<EOF +<label>次の語を含む${entity}で検索: +`cgi_text kwd $kwd`</label> +EOF + echo '</div>' + fi + cat<<EOF + <p>${total}件中の${offset}件めから${kwd:+" - 検索語: $kwd"}</p> +EOF + if [ $((offset+limit)) -lt $total ]; then + cat<<EOF +<div class="right"><form action="$myname" method="POST"> +`cgi_submit 次の${limit}件` +`cgi_hidden kwd "$kwd"` +`cgi_hidden stage "$stage"` +`cgi_hidden offset $((offset + limit))`</form></div> +EOF + fi + if [ $offset -gt 0 ]; then + cat<<EOF +<form action="$myname" method="POST"> +`cgi_submit 前の${limit}件` +`cgi_hidden stage "$stage"` +`cgi_hidden kwd "$kwd"` +`cgi_hidden offset $((offset - limit))`</form> +EOF + fi + + err `echo $sql\;` + + query "$sql limit $limit ${offset:+offset $offset};" \ + | while IFS='|' read id lnk name tag ownerp; do +err name=$name owner=$ownerp + files=`getvalbyid $tbl profimg $id $dir` + # Pick up only first icon + echo "<div class=\"iconlist xy$thumbxy\"><p class=\"tag _$tag\">$tag</p>" \ + | m4 $tagconv + if [ -n "$files" ]; then + icon=`echo "$files"|head -1` + iconhref $dir/$icon "$hrb+$lnk" "$name" + else + iconhref $dir/$deficon "$hrb+$lnk" "$name" + fi + echo "<br>$name${ownerp:+<br>$ownerp}" + echo "</div>" + done +) +listmember() { + listentry user "$@" +} +listgroup() { + listentry group "$@" +} +showgroup() { + grp=$1 + + showgroupsub $formdir/grp.def $grp | \ + m4 -D_TITLE_="グループ $grp" \ + -D_FORM_="syscmd(\`cat')" \ + -D_DUMPTABLE_="" \ + $layout/groupman.m4.html +} +showgroupsub() { + # $1=def-file $2=group + grp=$2 + rowid=`sq $db "select rowid from grp where gname='$grp'"` + if [ -z "$rowid" ]; then + rowid=`sq $db "select rowid from grp where rowid=$grp"` + grp=`sq $db "select gname from grp where rowid=$grp"` + fi + val=`getvalbyid grp profimg $rowid $tmpd` + # 6/14の次グループのHOMEで出す情報を作る Done + viewtable $1 grp $rowid + if isgrpowner $user $grp; then + echo "<p><a href=\"?groupconf+$grp\">グループ情報の編集</a>" + iamowner=$grp + fi + if ismember $user $grp; then + echo "${iamowner:+ / }<a href=\"?blog+$grp\">グループの新規話題作成</a></p>" + fi + # 加入ボタン + 加入者リスト +err ismember $user $grp + ismember $user $grp && ismem='checked' || nomem='checked' + # このグループでの加入アドレス + eml=`query "select val from grp_mem_s where gname='$2' and user='$user' \ + and key='email';"` +err EML: "select val from grp_mem_s where gname='$2' and user='$user' \ + and key='email';" +err email=$eml + echo '<h2>このグループに</h2>' + cgi_form grp <<EOF +<table class="b"> +<tr><th>メンバーとして</th><td> +<label>`cgi_radio joingrp "yes" $ismem`参加</label> / +<label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr> +<tr><th>参加する場合のメイルアドレス<br> +<small>(メインのアドレスとは違うものにする場合に記入<br> +同じでよい場合は空欄に)</small></th> +<td>`cgi_text email $eml`</td></tr> +</table> +`cgi_hidden grp $grp` +EOF + + echo '<h2>話題一覧</h2>' + cgi_form searchart<<EOF +<label>`cgi_text kwd`という語を含むコメントを検索</label> +`cgi_hidden owner $grp` +EOF + cond="where a.id in (select id from blog_s where key='owner' and val='$grp') order by ctime desc" + DT_CHLD=article:blogid \ + DT_VIEW=replyblog dumptable html blog 'ctime title heading' "$cond" + + c="group by b.name having b.name in (select user from grp_mem where gname='$grp')" + cm="?commission+$grp" + thumbxy=50x50 listmember "" "$c" \ + |sed -e "s|\(<br>\),not=\(.*\)|\1<a href=\"$cm+\2\">管理者委託</a>|" +} +joingrp() { + # $1=group $2=user $3=yes/no $4=email(if any) +err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4 + if [ x"$2" != x"$user" ]; then # if user is not login user + if ! isgrpowner $user $1; then + echo "<p>本人か、グループ管理者しか加入操作はできません。</p>" + return + fi + fi + cond="where gname='$1' and user='$2'" + if [ x"$3" = x"yes" ]; then + query "replace into grp_mem values('$1', '$2');" + if [ -n "$4" ]; then + if msg=`emaildomaincheck "$4"`; then +err "replace into grp_mem_s values('$1', '$user', 'email', \ + 'string', '$4', NULL);" + query "replace into grp_mem_s values('$1', '$user', 'email', \ + 'string', '$4', NULL);" + else + echo $msg + fi + else + query "delete from grp_mem_s $cond and key='email';" + fi + else + query "delete from grp_mem $cond; +delete from grp_mem_s $cond; +delete from grp_mem_m $cond;" + fi +} +grp_reg_adm() { + # $1=grp $2=user-rowid + if ! isgrpowner $user $1; then + echo "<p>$1 グループの管理者しかこの操作はできません。"; return + fi + newadm=`query "select name from user where rowid=$2;"` + if [ -z "$newadm" ]; then + echo "<p>指定ユーザIDがおかしいようです。</p>"; return + fi +err GRP_reg_adm: "replace into grp_adm values('$1', '$newadm');" +err ismember $newadm $1 + if ismember $newadm $1; then + # OK, go ahead + query "replace into grp_adm values('$1', '$newadm');" + # confirm insertion + sql="select * from grp_adm where gname='$1' and user='$newadm'" + if [ -n "`query \"$sql;\"`" ]; then + echo "<p>追加完了</p>" + else + echo "<p>追加失敗($1 $2)</p>" + fi + fi + showgroup $1 +} +dumptable() { + # $1=mode $2=Table $3=column-list(defaults to *) $4=conditions(if any) + # textのフィールドだけ全てダンプにしたほうがいいか + # $DT_VIEW sets link + # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう? + ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>" + VIEW=${DT_VIEW-replyblog} + if [ -n "$VIEW" ]; then + dvlink=" <a href=\"$myname?$VIEW+\\2\">VIEW</a>" + fi + # $DT_CHLD=ChildTable:BindColumn + if [ -n "$DT_CHLD" ]; then + _t=${DT_CHLD%:*} _i=${DT_CHLD#*:} + cntall="(select count($_i) from $_t where $_i=a.id)" + cntnew="(select count(val) from ${_t}_s where key='ctime' \ + and id in (select id from $_t where $_i=a.id) \ + and val > coalesce((select time from acclog where \ + user='$user' and tbl='$2' and rowid=a.rowid),\ + '1970-01-01'))" + cnt="$cntnew||'/'||$cntall as '新着/総数'," + dt_class=" td2r" + fi + # Construct join expression + eav="" scols="" + pk=`gettblpkey $2` + substr=${dumpcollen:+"substr(val, 0, $dumpcollen)"} + substr=${substr:-val} + for col in ${3:-`gettbl_s_cols $2`}; do + eav=$eav${eav:+,}" max(case key when '$col' then $substr end) as $col" + scols="$scols${scols:+, }b.$col" + done +#case author when '$user' then a.rowid else '---' end as ID, + sql="select \ +a.rowid as ID,\ +$cnt\ +$scols from $2 a left join (select $pk,$eav from ${2}_s group by $pk) b on a.$pk=b.$pk $4;" +err SQL="$sql" + cat<<EOF | sed "s,\(<TR><TD>\)\([1-9][0-9]*\)</TD>,\1$elink$dvlink</TD>," +<div class="dumptable"> +<table class="b$dt_class"> +`sq -header -cmd ".mode $1" $db "$sql"` +</table> +</div> <!-- dumptable --> +EOF +} + +par2table() ( + # copy current parameters of par into destination table + # $1=definition-file + # Using $user and $session + rowid=`getpar rowid` + if [ ! -e $1 ]; then + echo "<p>テーブル定義ファイルが見付かりません</p>" + exit 1 + fi + tbl=${1%.def} + tbl=${tbl##*/} + if [ -n "$rowid" ]; then # Modify existing entry + if [ x"$tbl" = x"user" ]; then + rowowner=`query "select name from $tbl where rowid=$rowid;"` + elif [ x"$tbl" = x"grp" ]; then + isgrpowner $user `query "select gname from $tbl where rowid=$rowid;"` \ + && rowowner=$user + else + rowowner=`query "select owner from $tbl where rowid=$rowid;"` + rowowner=${rowowner:-`query "select author from $tbl + where rowid=$rowid;"`} + fi + if [ x"$user" != x"$rowowner" ]; then + echo "<p>他人のレコードはいじれないの</p>" + return + elif [ -z "$rowowner" ]; then + echo "<p>指定したレコードはないみたい</p>" + return + fi + rm=`getpar rm` cfm=`getpar confirm` + # Editing existent entry + if [ x"$rm" = x"yes" ]; then + if [ x"$rm$cfm" = x"yesyes" ]; then + query "delete from $tbl where rowid=$rowid;" + return + else + echo "<p>消去確認のチェックがないので消さなかったの...</p>" + return + fi + fi + fi + # XX: Subshelling here is unnecessary 2015-07-05 + (ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster="" getrow="" + if [ -n "$rowid" ]; then + # Update of existing record + for col in `gettblcols $tbl`; do + val=`getparquote $col` + [ -z "$val" ] && continue + err sq $db "update $tbl set $col=$val where rowid=$rowid" + query "update $tbl set $col=$val where rowid=$rowid;" + done + # Then, set up $pval for further insertion of tbl_s and tbl_m + for col in `gettblpkey $tbl`; do + val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'` + pval="$pval${pval:+, }\"$val\"" + done + else + # New entry + # Generate values() for primary keys + for col in `gettblpkey $tbl`; do + # Genuine primary keys for _m and _s + val=`getvalquote $tbl $col` + [ -z "$val" ] && continue + pval="$pval${pval:+, }$val" + done +err pval=$pval + for col in `gettblfkey $tbl`; do + # args for values() to insertion into master table + val=`getvalquote $tbl $col` + [ -z "$val" ] && continue + formaster=$formaster"${formaster:+, }$val" + done + formaster="$pval${formaster:+, }$formaster" +err formaster=$formaster + if [ -z "$formaster" ]; then + echo "<pre>項目を全て埋めてください</pre>" + return 1 + fi + err "replace into $tbl values($formaster);" + echo "replace into $tbl values($formaster); $getrow" | sq $db + ## Insertion to master table, done + fi + + for kt in s m; do + tb2=${tbl}_$kt + for col in `gettbl_${kt}_cols $tbl`; do + ptype=`getpartype $col "limit 1"` + + # First, check update of existing entries in _m + if [ $kt = m ]; then + # sessID|address.1.22|string|Somewhere-x.y.z + sql="" +err dots from query "select var from par where var like '$col.%';" + for v in `query "select var from par where var like '$col.%';"`; do + # v=address.1.22 + rowid=${v##*.} + origcol=${v%%.*} # original column derived from +err Updating for $v rowid=$rowid, partype=`getpartype $v` + ##case `getpartype $v` in + err CASE `gettbl_coltype $tbl/$origcol` in + err edit flag = `getpar action.$v` + case `getpar action.$v` in + rm) + if [ x`getpar confirm.$v` = x"yes" ]; then + newsql="delete from $tb2" + else + echo "<p>削除確認未チェック</p>" + fi ;; + edit) + case `gettbl_coltype $tbl/$origcol` in + image|document|binary) + file=`getparfilename $v` + err type=file=$file + [ -z "$file" ] && continue + bn=${file##*/} + bin="X'"$(hexize $file)"'" + ct=`file --mime-type $file|cut -d' ' -f2` + type=\"file:$ct\" + newsql="update $tb2 set val='$bn', type=$type, bin=$bin" + ;; + *) + newsql="update $tb2 set val=(select val from par where var \ +like '$col.%.$rowid')" + ;; + esac + ;; + *) # maybe `keep', do not modify value + continue + ;; + esac +# err newsql=$newsql + sql=$sql$nl"$newsql where rowid=$rowid;" + done + echo "$sql +delete from $tb2 where type='string' and val='';" | sq $db + # Rest of kt==m: set multiple mode + nr=`getparcount $col` + else + nr=1 # for kt==s, number of records is 1 + fi + + i=0 + while [ $i -lt $nr ]; do + limit="limit 1 offset $i" + i=$((i+1)) # increase beforehand against continue + val=`getvalquote $tbl $col "$limit"` + [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue +err $col=$val + bin=NULL +err partype$col=`getpartype $col "$limit"` + case $ptype in + file) file=`getparfilename $col "$limit"` +err parfile-$col=$file + [ -z "$file" ] && continue + bin="X'"$(hexize $file)"'" + ct=`file --mime-type $file|cut -d' ' -f2` + type=\"file:$ct\" ;; + "*"*) continue ;; # foreign table + *) type=\"string\" ;; + esac + case `gettbl_coltype $tbl/$col` in + password) # special care for password + # name={password,pswd1,pswd2} + p1=`getpar pswd1 "$limit"` + if [ -z "$p1" ]; then + continue # SKIP password setting, if p1 is empty + else + pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"` +## err pswd=$pswd + if pwcheck "$pswd"; then + if [ x"$p1" = x"$p2" ]; then + case "$p1" in + ????????*) ;; + *) echo "<p>8字以上にしてください。</p>" + return ;; + esac + val="\"`echo $p1|mypwhash`\"" + else + echo "<p>2つの新パスワード不一致</p>" + return + fi + else + echo "<p>旧パスワード違います</p>" + return + fi + fi + ;; + esac + #err "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);" + echo "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);" \ + | sq $db + done + done + done + return 0 + err donee) +) +par2table_old() { + # copy current parameters of par into destination table + # $1=dst-table $2=definition-file + # Using $user and $session + rowid=`getpar rowid` + if [ -n "$rowid" ]; then + rm=`getpar rm` cfm=`getpar confirm` + if [ x"$rm$cfm" = x"yesyes" ]; then + sq $db "delete from $1 where rowid=$rowid and owner=\"$user\"" + return + fi + fi + cat $2 \ + | (cols="" + while IFS=: read prompt name type args; do + [ x"$name" = x"stage" ] && continue + if [ -n "$rowid" ]; then + val=`getpar $name|sed -e 's/\"/\"\"/g'` + sq $db "update $1 set $name=\"$val\" where rowid=$rowid and owner=\"$user\"" + else + eav=$eav${eav+,}" max(case var when '$name' then val end)" + fi + done + [ -n "$rowid" ] && return + cond="where sessid='$session' group by sessid" + sq $db "replace into $1 select $eav,\"$user\" from par $cond" + # Think over again about putting $user + ) +} +genform() { + # $1 = form definition file + # $2, $3 (optional)= table name and ROWID + # If $GF_VIEWONLY set and nonNull, output values without form + # If $GF_HIDDEN set, use it hidden values + # If $GF_OWNER set, use it as value of name="owner" + # If $GF_STAGE set, use it as value of name="stage" + forms="" hiddens="" rowid=$3 + if [ ! -e "$1" ]; then + echo "<p>そのようなデータベースはないようです($2)。</p>" + return + elif [ -n "$2" ]; then +err genform1: "select * from $2 where rowid='$rowid'" + rec=`query "select * from $2 where rowid='$rowid';"` + if [ -z "$rec" ]; then + pk=`gettblpkey $2` + ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"` +err "select rowid from $2 where $pk='$rowid';" + rec=`query "select rowid from $2 where $pk='$rowid';"` +err rec-rowid=$rec + rowid=$rec + rec=$3 + fi + if [ -z "$rec" ]; then + echo "<p>そんなレコードはないみたいね...</p>" + return + fi + fi + if [ -z "$GF_VIEWONLY" ]; then + rm='<input id="rm" name="rm" type="checkbox" + value="yes"><label for="rm">このエントリの削除</label> +<span>ほんとうに消しますよ(確認)! +<input name="confirm" type=checkbox value="yes">はい</span>' + fi + while IFS=: read prompt name keytype type args; do + [ -z "${prompt%%\#*}" ] && continue # skip comment line(#) + sp="${args:+ }" + form="" val="" + if [ -n "$rowid" ]; then + val=`getvalbyid $2 $name $rowid $tmpd` + fi + if [ -n "$GF_VIEWONLY" ]; then + is_hidden "$2" "$name" && continue + fi + case "$type" in + text*) + cgiform=cgi_multi_$type + if [ -s $tmpd/$name.count ]; then + form=`$cgiform $name $tmpd` + val=$(echo "$val"| + while read fn; do + echo "<tr><td>`cat $tmpd/$fn`</td></tr>$nl" + done) + val="<table>$nl$val$nl</table>" + else + #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl" +err genform: cgi_$type $name $val "$args" + form=`cgi_$type $name "$val" "$args"` + fi + ;; + [Rr][Aa][Dd][Ii][Oo]) + fh="<input type=\"radio\" name=\"$name\"" + form="`echo $args|sed -e \ +\"s,\([^=][^=]*\)=\([^= ][^= ]*\),$fh value=\\"\2\\">\1,g\"`" + ;; + [Ss][Ee][Ll][Ee][Cc][Tt]) + fh="<select name=\"$name\">$nl" + form="`echo $args|sed -e \ +'s,\([^=][^=]*\)=\([^= ][^= ]*\),<option value=\"\2\">\1</option>,g'`" + + if [ -n "$val" ]; then + form=`echo $form|sed -e "s,\(value=.$val.\),\\1 selected,"` + fi + form="$fh$form</select>" + ;; + [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary) + if [ -s $tmpd/$name.count ]; then + form=`cgi_multi_file $name $tmpd "$args"` + if [ -n "$val" ]; then + hrfb="$myname?showattc+$2_m" + val=$(echo "$val" \ + | while read fn; do + data=`percenthex $tmpd/$fn` + #ct=`cat $tmpd/$fn.content-type` + ct=`file --mime-type $tmpd/$fn|cut -d' ' -f2` + ri=`cat $tmpd/$fn.rowid` +## err fn=$fn, name=$name, ri=$ri; ls -lF $tmpd 1>&3 + imgsrc="<img src=\"data:$ct,$data\">" + echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>" + done) + fi + else + form="<input type=\"file\" name=\"$name\" $args>" + if [ -n "$val" ]; then + imgs=$(echo "$val"\ + |while read fn;do + data=`percenthex $tmpd/$fn` + echo "<img src=\"data:image/png,$data\">$fn<br>" + done) + form=$form"<br>$imgs" + val=$imgs # 2015-06-15 + else + form="<input type=\"file\" name=\"$name\" $args>" + fi + fi + ;; + [Hh][Ii][Dd][Dd][Ee][Nn]) + if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then + args="value=\"$GF_STAGE\"" + fi + form="<input type=\"hidden\" name=\"$name\" $args>" + prompt='' # Remove prompt + ;; + [Aa][Uu][Tt][Hh][Oo][Rr]) + form="<input type=\"hidden\" name=\"author\" value=\"$user\">" + prompt="" ;; + [Oo][Ww][Nn][Ee][Rr]) + val=${GF_OWNER:-$val} + val=${val:-$user} + form="<input type=\"hidden\" name=\"owner\" value=\"$val\">" + prompt="" ;; + [Uu][Ss][Ee][Rr]) + # XXX: is null $user ok? + #form="<input type=\"hidden\" name=\"user\" value=\"$user\">" + [ -n "$GF_VIEWONLY" ] && continue + form="$user" + ;; + [Pp]assword) + [ -n "$GF_VIEWONLY" ] && continue + form="`cgi_passwd`" + val="" + ;; + [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp]) + val=$((($(date +%s)-1433084400)/10))c$$ + form="<input type=\"hidden\" name=\"serial\" value=\"$val\">" + prompt="" ;; + [Ss][Ee][Ss][Ss][Ii][Oo][Nn]) + prompt="" + ;; + parent|path|blog*) + prompt="" + ;; + "*"*) + tail=$tail"``" + continue ;; + esac + if [ -n "$prompt" ]; then + if [ -n "${GF_VIEWONLY}" ]; then + form=$val + else + : + fi + forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl" + else + hiddens=$hiddens$nl"$form" + fi + done < $1 + # enctype="multipart/form-data" + cat<<EOF +<form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data"> +${rowid:+$rm} + <table class="b $2"> +$forms + </table>$hiddens + ${rowid:+<input type=\"hidden\" name=\"rowid\" value=\"$rowid\">} +EOF + if [ -z $GF_VIEWONLY ]; then + cat<<EOF + <input type="submit" name="sub" value="OK"> + <input type="reset" name="res" value="Reset"> +EOF + fi + cat<<EOF +</form> +$tail +EOF +} +edittable() { + # $1=form-def $2=table $3 rowid + genform "$@" +} +viewtable() { + GF_VIEWONLY=1 genform "$@" +}