#!/bin/sh
# Here's global variable table. Do not use this names.
# $HGid$
[ -f s4-config.sh ] && . ./s4-config.sh
myname=`basename ${SCRIPT_NAME:-$0}`
mydir=`dirname ${SCRIPT_FILENAME:-$0}`
myargs="$@"
PATH=/usr/local/sqlite3/bin:/usr/local/vim7/bin:/usr/iekei/ImageMagick/bin:/usr/local/ImageMagick/bin:$PATH
tmpdir=${TMPDIR:-tmp}
dbdir=${DBDIR:-db}
tmpfiles=""
db=${DB:-$dbdir/cgi.sq3}
admin=${ADMIN:-hostmaster@example.org}
templ=${TEMPL:-templ}
layout=${LAYOUT:-$templ/default}
formdir=${FORMDIR:-$templ/form}
imgdir=${IMGDIR:-img}
url=${URL:-"${REQUEST_SCHEME:-http${HTTPS:+s}}://$HTTP_HOST$REQUEST_URI"}
urlbase=${url%%\?*}
msgdir=$templ/msg
timeout="+2 days"
memoplimitdays="7"
dumpcollen=22
#thumbxy=120x120
thumbxy=96x96
iconxy_S=80x80
iconxy_M=400x400
maximagexy=1600x1600
### maximagexy=400x400
file_accept='accept="image/*,text/*,audio/*,application/vnd.oasis.*,application/pdf,application/x-*"'
blogreadflagrowid=0
tconfs=""
imgcached=cache/img.`date +%Y/%m`
conftbl=_tblconf
nl="
"
iconcachekey="profimgcache_S"
. ./s4-cgi.sh
: <<EOF
!! 検索等でblogテーブル参照時は sql4readableblogs() で定義される
!! readableblogs テーブルを使うこと
資料配布、グループ管理・ML、ファイル交換、クリッカー、アンケート
レポート提出管理
ひとつのarticleをheadingにして新規ツリーを作成、あるといいかも。
[2016]
7/12 根本への反省
* cgi自身の $1, $2 での切り替えでなく、CGI変数での受け渡しにすべき。
arg1/arg2/arg3 的に $1 に / 区切りでつけた方がよかったかな。
[以下2015]
8/4 ○グループに承認加入モードを追加
○グループに参加していない場合は grpaction できない
Web
締切設定
8/2 ○s4.cgi生成系 → index.cgi生成
○自分の提出物リスト
7/19 ○設置
○一斉送信
○getparfilename の tmpd の扱い
○やっぱりs4にしようかな
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" "$@"
}
dbsetup() {
[ -d $tmpdir ] || mkdir -m 1777 $tmpdir
[ -d $dbdir ] || mkdir -m 1775 $dbdir
sqi=$tmpdir/sqi.$$
sqo=$tmpdir/sqo.$$
mkfifo $sqi $sqo
#tail -f $sqi | sq $db & # "tail -f" is too heavy. DO NOT USE!!
sq $db < $sqi &
sq3pid="`jobs -p` $!"
exec 2>> $tmpdir/error.out
exec 3>> $tmpdir/debug.out
exec 5> $sqi # Turning $sqi access through fd5 for continuous open state
rm $sqi
}
cleanup() {
echo .quit >&5
kill $sq3pid
kill $sq3pid
rm -f $sqo $sqi
rm -rf $tmpfiles
}
query() {
echo ".once $sqo" >&5
if [ -z "$1" ]; then
cat
else
echo "$@"
fi >&5
cat $sqo
}
_m4() {
#_S4NAME_=f,f,f
m4 ${_S4NAME_:+"-D_S4NAME_=${_S4NAME_}"} "$@"
}
ismember() {
# $1=user, $2=group
err ismem: "select user from grp_mem where gname=$(sqlquote $2) and user='$1';"
test -n "`query \"select user from grp_mem where gname=$(sqlquote $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
err isgroup: "select gname from grp where gname=$(sqlquote $1);"
test -n "`query \"select gname from grp where gname=$(sqlquote $1);\"`"
}
isgrpowner() (
# $1=user, $2=group
gn=`sqlquote "$2"`
sql="select user from grp_adm where gname=$gn and user='$1';"
err isgrpowner: $sql
test -n "`query $sql`"
)
getgroupadminmails() {
# $1=group
for i in $(getgroupadmins $1); do
email4group "$1" "$i" ;
done
}
getgroupadmins() { # $1=group
# This function is called in a backquote, so needn't to be subshellized
qgrp=`sqlquote "$1"`
query "select user from grp_adm where gname=$qgrp;"
}
getgroupattr() { # $1=group $2=attr
# This function is called in a backquote, so needn't to be subshellized
getvalbyid grp $2 \
$(query "select rowid from grp where gname=`sqlquote $1`;")
}
getgroupbyid() {
# $1=id|gname
sql="select coalesce((select gname from grp where gname=$(sqlquote $1)),
(select gname from grp where rowid=$(sqlquote $1)));"
# err ggbyid: `echo $sql`
query $sql
}
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
# Return true when anonymous mode
[ "$anonymousmode" ] && return 0
# 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')\
when (select author from article where\
id=(select id from $2 where rowid=$3))='$1'
then 'user+author'
else '' end;"
err isfilereadable: sql="`echo $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>"
}
hreflink() {
# s4 specific notation:
# ^href=URL
# ^iframe=URL
# OSM umap Wikistyle Notation:
# [[URL]] - Simle Link
# [[URL|Word]] - Link with anchor word
# {{URL}} - <img src="URL">
# {{URL|width}} - <img src="URL" width="width">
# {{{URL}} } - <iframe src="URL"></iframe>
# {{{URL|height}} - <iframe src="URL" height="height"></iframe>
_hrefptn="[-A-Za-z0-9,.:;/~_%#&+?=@!]*"
sed -e "s|\[\[\($_hrefptn\)\|\(.*\)\]\]|<a href=\"\1\">\2</a>|g" \
-e "s|\[\[\($_hrefptn\)\]\]|<a href=\"\1\">\1</a>|" \
-e "s|{{{\($_hrefptn\)\|\(.*\)}}}|<iframe src=\"\1\" height=\"\2\"></iframe>|g" \
-e "s|{{{\($_hrefptn\)}}}|<iframe src=\"\1\"></iframe>|g" \
-e "s|{{\($_hrefptn\)\|\(.*\)}}|<img src=\"\1\" width=\"\2\">|g" \
-e "s|{{\($_hrefptn\)}}|<img src=\"\1\">|g"\
-e "s|^href=\($_hrefptn\)|<a &>\1</a>|" \
-e "s|^iframe=\($_hrefptn\)|<iframe src=\"\1\"></iframe>|"
}
minitbl() {
sed -n '
/^|.*|/ {; # If the line begin with "|" and has 2 or more "|"
s,|$,,; # Remove trailing "|" first
s,|\* *\([^|]*\) *,<th>\1</th>,g; # "|*..." to "<th>...</th>"
s,| *\([^|]*\) *,<td>\1</td>,g; # "|..." to "<td>...</td>"
s,^,<tr>,; s,$,</tr>,; # Enclose with "<tr>" and "</tr>"
H; # Concat this line to HoldSpace
s/.*//; # Delete PatternSpace for finalization
$ b cont
d; # If in final line, output the rest, else jump to next turn
}
:cont
x; # For non-"|" lines, check HoldSpace
/^./ {; # If HoldSpace has "|" table elements
s|^|<table class="mini">|; # Enclose whole elements like this:
# s|$|</table>|; # <table class="mini">..\n..</table>
p; # Print whole "table" element
s/.*//; # Erase all when done.
x; s|^|</table>|; x; # Preppend /table to the next line
}
x; # Back to the newest line
p; # Print rest'
}
acclog() (
# $1=table, $2=rowid
n=${2%%[!0-9]*} # Remove non-digit chars from $2(should be rowid)
if [ -n "$n" ]; then
now=`date +"%F %T"`
#query "replace into acclog values('$user', '$1', '$n', '$now');"
#query "replace into acclog values('$user', '$1', $n, '$now');"
query "replace into tblaccesses values('$user', '$1', $n, '$now');"
fi
)
gecos() (
u=`sqlquote ${1:-$user}`
query "select gecos from gecoses where name=$u;"
)
setpar() {
query "replace into par values('$session', '$1', '$2', \"$3\");"
}
replpar() {
query "update par set val=\"$3\" where sessid='$session' and var='$1' and type='$2';"
}
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;"`
err getpar/val1: "val=[$val]"
if [ -z "$val" ]; then
val=`query "select val from cookie where var='$1' and sessid='$session' $2;"`
fi
err getpar/val2: "val=[$val]"
case "$var" in
owner)
if [ x"$user" = x"$val" ]; then
echo $user; return
elif ismember $user $val; then
echo $val; return
fi ;;
esac
err getpar/ret: "val=[$val]"
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 $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)
}
sqlquotestr() (
case "$1" in
*\'*) v=`echo "$1"| sed "s/'/''/g"`
echo "'$v'" ;;
*) echo "'$1'" ;;
esac
)
mktempd() {
TMPDIR=$tmpd mktemp -d -t $session
}
getcachedir() { # $1=maintable
if [ -n "$imgcached" ]; then
echo $imgcached/$(echo ${1:-hoge}|md5)/$thumbxy
else
echo $tmpd/$thumbxy
fi
}
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 $2`
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`
}
getbinbyid() {
# $1=tbl $2=col $3=rowid $4=tmpdirForBinary
}
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
rowid=$3
pk=`gettblpkey $1`
key=`query "select $pk from $1 where rowid=$3;"`
getkey="(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:-$tmpd}
[ -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\"" #2015-07-22
cond="$t where $pk=$getkey and key=\"$c\""
val=`query "select val from $cond limit 1;"`
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;"`
err gvb1-sql: "select count(val) from $cond;"
vcount=`query "select count(val) from $cond;"`
echo $vcount > $td/$c.count
i=0
## err gvbid: i=$i vcount=$vcount
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
r_f=`query "select rowid||'//'||val from $cond $slice;"`
f_rid=${r_f%%//*}
file=$td/${r_f##*//}
# FOR SPEED: Skip file generation if imgcache exists
[ -s "$file" -a -s "$td/$fn.rowid" -a -s "$file.rowid" ] \
&& [ x"$f_rid" = x"`cat $td/$fn.rowid`" ] \
&& continue
# err gvbid-get="select quote(bin) from $cond $slice;"
## err output: "fn=[$fn] file=[$file]"
sq $db<<EOF | unhexize > "$file"
.output '$td/$fn.rowid'
select rowid from $cond $slice;
.output '$td/$fn'
select val from $cond $slice;
.output '$td/${fn}.content-type'
select substr(type, 6) from $cond $slice;
.output stdout
select quote(bin) from $cond $slice;
EOF
## err gvbid-get2: "`ls -lF $file`"
## err i=$i - file=$file rowid=`cat $td/$fn.rowid`
cp "$td/$fn.rowid" "$file.rowid" 2>&3 # for convenience
cp "$file" "$file.orig" 2>&3
ls -lh "$file" |
awk '{print $5"B"}'|sed 's/BB/B/' > "$file.size"
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${val:+$nl}"`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\";" \
| unhexize > "$file"
##@@## -- echo ${type#file:} > "$file.content-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
}
enjpeg() {
if [ -z "$cjpeg" ]; then
if type cjpeg >/dev/null 2>&1; then
cjpeg="cjpeg"
else
cjpeg="convert - jpeg:-"
fi
fi
$cjpeg "$@"
}
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 || exit 1 # Abort if fail to call encode
)
hexize() {
if [ -z "$hexize" ]; then
if type xxd >/dev/null 2>&1; then
hexize="xxd -p"
else
hexize_hd() {
hexdump -ve '1/1 "%.2x"'
}
hexize="hexize_hd"
fi
fi
cat "$@" | $hexize | tr -d '\n'
}
unhexize() {
if [ -z "$unhex" ]; then
if type xxd >/dev/null 2>&1; then
unhex="xxd -p -r"
elif type perl >/dev/null 2>&1; then
cat >$tmpd/unhex.pl<<EOF
s/([0-9a-f]{2})/print chr hex \$1/gie
EOF
# Perl refuses -e in setuid circumstances, which can be absurdly
# avoided by creating scripts in a file where its parent directory is
# world writable...:)
unhex="perl -n $tmpd/unhex.pl"
fi
fi
cat "$@" | $unhex
# cat $1 | tee /tmp/uh.in| $unhex | tee /tmp/uh.out
}
percenthex() {
hexize "$@" | sed 's/\(..\)/%\1/g'
}
htmlescape() {
sed -e 's/\&/\&/g' -e 's/"/\"/g' -e "s/'/\'/g" \
-e "s/</\</g; s/>/\>/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
}
size_h() {
i="$1" oi=$1
set -- B B KB MB GB TB
while [ $((i)) -gt 9 -a -n "$1" ]; do # -gt 9 means $oi > 1024
oi=$i
i=$((i/1024))
shift
done
echo ${oi}$1
}
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" | cut -d' ' -f2`
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 gdate -d "$at" +"$FMT"
)
addsession() {
# expireをセット
# loginの先にどの画面に行くかの状態遷移表書式を決める
expire=`expire ${2:-"+1min"}`
err addsession "$1" exp=$expire
query "replace into session values('$1', '$expire');"
# Remove old session parameters
now=`expire now`
query "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
}
genserial() {
echo $((($(date +%s)-1433084400)/10))c$$
}
smail() {
# smail rcpts subj (file)
# $SMAIL_TO <- Recipient value of To: header
# $MAIL_FROM <- From: header value
from=`echo "${MAIL_FROM:-$admin}"|nkf -jM|tr -d '\n'`
rcpt=`echo $1` # strip newlines
subj=`echo $2|nkf -jM|tr -d '\n'`
(_m4 -D_RCPT_="${SMAIL_TO:-$rcpt}" -D_SUBJ_="\`$subj'" -D_FROM_="$from" $msgdir/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'`"
query "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
err checkdomain $1
host ${item#*@} 1>&3 2>&3
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
contenttype; echo
_m4 -D_TITLE_='Invalid email' $layout/title-only.html
echo "ユーザ名($user)には正しいメイルアドレスが必要です。" | html p
putfooter
exit 0
fi
newpswd=`genrandom` # newsalt=`genrandom 5`
#encpswd=`mycrypt "$newpswd" "$newsalt"`
encpswd=`echo $newpswd|mypwhash`
dbsetbyid user $user pswd "$encpswd"
# Avoid $user substitution with m4, because $url comes from user input.
_m4 -D_PSWD_="$newpswd" -D_URL_="$url" -D_ADMIN_="$admin" \
$msgdir/mail-newaccount.m4 \
| sed "s/_USER_/$user/g" \
| 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`
quser=`sqlquotestr "$user"`
dbuser=`query "SELECT name FROM user WHERE name=$quser;"`
if [ -z "$dbuser" ]; then
return 1
elif [ 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" \
$msgdir/login-fail-$st.m4.html
showlogin # and EXIT
fi
}
# Do instant jobs here
dbsetup
trap cleanup INT HUP EXIT TERM PIPE
# trap cleanup INT HUP
err() {
echo "$@" 1>&3
}
cgiinit() {
session=`date +%F-$$`
tmpf=tmp/stream
tmpd=`tmpd=$tmpdir 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 #slow
## dd bs=$CONTENT_LENGTH count=1 of=$tmpf # NOT working
# cat > $tmpf # too much?
head -c $CONTENT_LENGTH > $tmpf # safe?
(echo CL=$CONTENT_LENGTH; ls -lF $tmpf) 1>&3
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" $tmpd < $tmpf`
for us in `LC_CTYPE=C ./mpsplit.pl "$bndry" $tmpd < $tmpf`
do
k=${us%%\=*}
#echo u=$us
#v="`echo ${us#*=}|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"
v="`echo ${us#*=}|unhexize|sed -e 's/\"/\"\"/g'`"
# err k=$k v=$v
case "$k" in
*:filename)
type='file'; k=${k%:filename}
# DO NOT ALLOW Space and '|' in file names
newv=`echo "$v"|sed 's/[ \|]/X/g'`
if [ x"$v" != x"$newv" ]; then
fi
# (echo k=$k v="[$v]"; ls -lF "$tmpd/$v"; file --mime-type "$tmpd/$v") 1>&3
case `file --mime-type - < "$tmpd/$v"|cut -d' ' -f2` in
[Ii]mage/x-xcf)
bzip2 "$tmpd/$v"
v=${v}.bz2
;;
[Ii]mage/x-*|*/vnd.*) ;;
[Ii]mage/*)
mogrify -resize $maximagexy'>' "$tmpd/$v"
;;
esac
;;
*)
type='string'
;;
esac
#sq $db "replace into par values('$session', '$k', '$type', \"$v\")"
setpar "$k" "$type" "$v"
done
;;
*)
setviastring par "$s"
;;
esac
}
email4group() {
# Get for-$1=group email address(es) for $2...=users
qgrp=`sqlquote "$1"`; shift
users=`for i; do sqlquote "$i"; done`
users=`echo $users|tr ' ' ','`
sql="select coalesce(s.val, g.user) from grp_mem g
left join grp_mem_s s on g.gname=s.gname and g.user=s.user
and s.key='email'
where g.gname=$qgrp and g.user in ($users);"
err `echo $sql`
query "$sql"
}
email4groupbyuid() {
# Get for-$1=group email address(es) for $2...=user-ids
qgrp=`sqlquote "$1"`; shift
err em4gbid-IN: "\$1=$1 qgrp=$qgrp"
uids=`echo "$@"`
uids=`echo $uids|tr ' ' ','`
sql="WITH
grpemails AS (
SELECT gname, user, val email
FROM grp_mem NATURAL JOIN grp_mem_s
WHERE key='email' AND gname=$qgrp),
useremails AS (
SELECT user.rowid rid, user.name, val email
FROM user
LEFT JOIN user_s
ON user.name=user_s.name AND user_s.key='email')
SELECT DISTINCT coalesce(g.email, u.name)
FROM useremails u LEFT JOIN grpemails g
ON u.name=g.user
WHERE u.rid in ($uids);"
err email4gByid `echo $sql`
query "$sql"
}
collectemail() (
# Collect email addresses for group $1
# If $TEAM is set, filter by team name
# If $EXCEPT is set as username(s) delimited by comma,
# remove $EXCEPT from list: ....NOT IN ($EXCEPT)
for e; do
if isuser "$e"; then
em=`query "select val from user_m where name='$e' and key='email';"`
[ -n "$em" ] && echo "$em" || echo "$e"
else
qgrp=`sqlquote "$e"`
if [ -z "$TEAM" ]; then
gmem="grp_mem"
else
tm=`sqlquote "$TEAM"`
gmem="(SELECT gname, user FROM grp_mem_m WHERE gname='$e' AND key='team' AND val=$tm)"
fi
ex=${EXCEPT:+"AND g.user NOT IN ($EXCEPT)"}
sql="select coalesce(s.val,um.val,g.user) from
$gmem g left join grp_mem_s s
on g.gname=s.gname and g.user=s.user and s.key='email'
left join user_m um on g.user=um.name and um.key='email'
where g.gname=$qgrp $ex;"
err CollectEmail: `echo "$sql"`
query "$sql"
fi
done
)
sendinvitation() (
# $1=email
iss="invite-`date +%s`-$user"
addsession $iss +${memoplimitdays}days # 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" \
$msgdir/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
addr=`query "select val from par where sessid like 'invite-%-$user';"`
err addr=$addr
if [ -n "$addr" ]; then
susp="<h2>招待済みで加入待ちのアドレス</h2><pre>$addr</pre>"
fi
_m4 -D_TITLE_="招待" -D_REPORT_="\`$repo'" -D_ACTION_="?invite" \
-D_BODYCLASS_="default" -D_SUSPENDED_="$susp" \
$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 "bye bye" | html p
reutrn
fi
email=`session=$1 getpar invite`
if [ -z "$email" ];then
cat<<EOF
<p>無効な招待状チケットです。</p>
<p>招待状の有効期限(1週間)が切れているか、チケット番号が異なっています。
加入している人に、再度招待してもらいましょう。</p>
EOF
return
fi
echo "$email さんようこそ" | html 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
echo "このアドレスに初期パスワードを送信しました。" |html p
echo "新着メイルを確認してログインしてください。" |html p
addsession $1 # for removal after 1 minute
_m4 -D_SYSNAME_="Initial Login" -D_MYNAME_="$myname?userconf" \
$layout/login.m4.html
return
}
group_safename() {
# Convert $1 to safe group name
echo "$1" | tr -d '"'"'",
}
groupupdate() {
gname=`getpar gname`
qgname=`sqlquote $gname`
err Enter:groupupdate
if [ -n "$gname" ]; then
# See ALSO same job in showgroup()
newgname=`group_safename "$gname"`
err newgname=$newgname
if [ x"$newgname" != x"$gname" ]; then
err NewGNAME: gname=$newgname
gname=$newgname
echo "使用禁止文字を除去し $gname としました。" | html p
replpar gname string "$gname"
fi
# Name confliction check
parow=`getpar rowid`
err parow=$parow
qgname=`sqlquote $gname` # Set again in case gname modified
query "BEGIN EXCLUSIVE;"
err "select count(gname) from grp where rowid != ${parow:-0} and gname = $qgname;"
count=$(query "select count(gname) from grp where rowid != ${parow:-0} and gname = $qgname;")
if [ $count -gt 0 ]; then
echo "そのグループ名は既にあります。" | html p
query "END;"
return
fi
par2table $formdir/grp.def
query "END TRANSACTION;"
# Remove orphan
: <<EOF
select a.id,b.val from (select * from blog where id in
(select id from blog_s where key='owner'
and val not in (select name from user union select gname from grp)))
a left join blog_s b on a.id=b.id and b.key='owner';
EOF
rm=`getpar rm` cfm=`getpar confirm`
err groupupdate:::: after par2tbl rmcfm=$rm$cfm
if [ x"$rm$cfm" = x"yesyes" ]; then
if [ -z "`query \"select gname from grp where gname=$qgname;\"`" ]; then
sql="delete from blog where id in
(select id from blog_s where key='owner' and val=$qgname);"
err rm-grp cleaning sql=`echo $sql`
query "$sql";
grps # When removing a group, switch to grp-list
return # and return
fi
fi
[ -z "$parow" ] && joingrp "$gname" "$user" yes "" as-admin
fi
sql="select rowid from grp where gname=$qgname;"
grid=$(query $sql)
err grpupdate:new-grid=$grid, sql=$sql
grp $grid
}
groupman() {
note="<p>グループ名に使用できない文字は自動的に削除されます。</p>"
GF_STAGE="grpconf"
GF_STAGE=groupupdate
DT_VIEW=grp dumptable html grp 'gname gecos:DESC mtime:TIME' 'order by b.TIME desc' \
|_m4 -D_TITLE_="グループ作成" \
-D_FORM_="$note`genform $formdir/grp.def`" \
-D_DUMPTABLE_="syscmd(cat)" \
$layout/html.m4.html $layout/form+dump.m4.html
}
userconf() {
[ -n "`getpar rowid`" ] && par2table $formdir/user.def
_m4 -D_BODYCLASS_=userconf -D_TITLE_="ユーザ情報編集" $layout/html.m4.html
GF_ACTION="?home" edittable "$formdir/user.def" "user" "$user"
}
groupconf() {
# $1=rowid in grp (2015-07-21 changed from gname)
[ -n "`getpar rowid`" ] && par2table $formdir/grp.def
_m4 -D_BODYCLASS_=groupconf -D_TITLE_="グループ情報編集" $layout/html.m4.html
#rowid=`query "select rowid from grp where gname='$1';"`
rowid=${1%%[!A-Z0-9a-z_]*}
err gcon \$1=$1 rowid=$rowid
# GF_ACTION="?grp+$1" edittable "$formdir/grp.def" "grp" "$rowid" #2015-0804
GF_STAGE="groupupdate" edittable "$formdir/grp.def" "grp" "$rowid"
}
mems() {
_m4 -D_TITLE_="参加者一覧" -D_BODYCLASS_=listmember $layout/html.m4.html
kwd=`getpar kwd`
listmember $kwd
}
grps() {
_m4 -D_TITLE_="グループ一覧" -D_BODYCLASS_=listgroup $layout/html.m4.html
kwd=`getpar kwd`
listgroup $kwd \
| _m4 -D_DUMPTABLE_="syscmd(cat)" \
-D_TITLE_="グループ関連操作" \
-D_FORM_="<a href=\"?groupman\">新規グループ作成</a>" \
$layout/form+dump.m4.html
}
grp() { # $1=group-rowid
gpg=`getpar grp`
grid=${1:-$gpg}
grp=`getgroupbyid "$grid"`
err grp: getpar-grp"(gpg)=[$grp]"
## . ./s4-blog.sh
jg=`getpar joingrp`
if [ -n "$jg" ]; then
err jg=$jg, grp=$grp
[ -n "$jg" -a -n "$grp" ] &&
joingrp "$grp" "$user" "$jg" "`getpar email`"
fi
htmlheader=$layout/html.m4.html
showgroup "$grid"
}
listnewblogsql() { # $1=user
cat<<EOF
`sql4readableblogs`
with article_ctime as (
select id,blogid,author,val ctime
from article join article_s s using(id)
where s.key='ctime'
), blog_title_owner as (
select blg.rid brid, id,
max(case key when 'title' then val end) title,
max(case key when 'owner' then val end) owner
from readableblogs blg, blog_s using(id) group by id
), acclog_user as (
select * from acclog where user='$user' and tbl='blog'
), blogall as (
select b.id, brid, b.owner, b.title, ctime, ac.author
from blog_title_owner b, article_ctime ac
on b.id=ac.blogid
)
select brid LINK,
sum(max(coalesce(l.time, "0"),
coalesce((SELECT time FROM acclog_user
WHERE tblrowid=$blogreadflagrowid), "0"))
< ctime
) "新着",
count(id) "総数", ctime, title,
/* owner gecos */
(select gecos from gecoses where name=bl.owner) gecos
from blogall bl left join acclog_user l
on bl.brid=round(l.tblrowid)
group by bl.id
having "新着" > 0 order by ctime desc, "新着" desc, bl.id
limit 10;
EOF
}
search_form() {
help="(1)空白区切りの単語で本文検索
(2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索
(3)#番号 で記事ID検索
(1)と(2)は組み合わせOK
例: @2016-10-0[1-9] 芋煮
→ 2016年10月上旬でキーワード「芋煮」を含む記事検索"
cat<<-EOF
<div class="fr">
<form action="$myname">
<input type="text" name="kwd" value="" title="$help"
placeholder=" 全記事からの検索 " width="10">
<input type="hidden" name="stage" value="searchart">
</form>
</div>
EOF
}
imgsrc_cache() (
# $1 = directory for cache'ing
# $2 = table (user_m or grp_m)
# $3 = keycond (was: condition for choosingowner)
# $4 = size : S = Small, M = Medium, O = Original
dir="$1" tbl="$2"
keycond="$3"
whos="$keycond AND key='profimg' AND type LIKE 'file:image%'
ORDER BY rowid LIMIT 1"
[ -d "$dir" ] || mkdir $dir
case "$4" in
[Ss]) size=S ;;
[Oo]) size=O ;;
*) size=M ;;
esac
# ImageCache filename storing schema:
# <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}
sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"
sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"
valtype=`query "$sql0"`
filename=${valtype%%//*}
filetype=${valtype##*//file:}
cacheimg_S=$dir/S_$filename
cacheimg_M=$dir/M_$filename
cacheimg_O=$dir/$filename
cacheimg=$dir/${size}_$filename
sumfile="$dir/$filename.sum"
tmpf=$tmpd/imgsrc_cache.$$
sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1
if test -s "$sumfile" && [ x"`cat $sumfile`" = x"$sum" ]; then
# if cache is fresh,
echo "<img src=\"$cacheimg\">"
else
fifo=$tmpd/fifo; mkfifo $fifo
fmt=${filename##*.}
## [[ NOTE ]]
## a. convert oldimage newimage
## b. convert oldimage fmt:- | convert - newimage
## b is much smaller than a
cat $tmpf | unhexize \
| tee $fifo \
| convert -define ${fmt}:size=${iconxy_M}x${iconxy_M} \
-resize ${iconxy_M}x${iconxy_M}'>' - pnm:- \
| convert - "$cacheimg_M" &
cat $fifo | convert -define ${fmt}:size=${iconxy_S}x${iconxy_S} \
-resize ${iconxy_S}x${iconxy_S}'>' - pnm:- \
| convert - "$cacheimg_S" &
printf '%s' "<img src=\"data:${filetype},"
cat $tmpf | sed 's/\(..\)/%\1/g'
echo '">'
echo "$sum" > $sumfile
fi
## Now preparing cache image, done.
## Store this information to DB
stbl=${tbl%_m}_s # user_s or grp_s
pkey=${keycond%%=*} # Primary Key name
pval=${keycond#*=} # Primary Key value
query <<-EOF
REPLACE INTO $stbl($pkey, key, type, val)
VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);
EOF
)
imgsrc_cache_clear() {
# $1=table
# $2=keycond
query "DELETE FROM $1 WHERE $2 AND key='$iconcachekey';"
}
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
td=`getcachedir home/"$1"`
gecos=`gecos "$uname"`
err SH:gecos=$gecos
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>"
# Display folders
sql="select count(id) from article_m where id
in (select id from article where author='$user')
and type like 'file:%';"
err nfile-sql=`echo "$sql"`
nfile=`query "$sql"`
# err nfile=$nfile
if [ $nfile -gt 0 ]; then
conflink="$conflink /
<a href=\"?lsmyfile\">過去の提出ファイル</a>"
fi
fi
. ./s4-blog.sh
tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$
search_form > $sf
echo "$gecos さん" > $tf
{ echo "<div class=\"noprofimg\">"
viewtable $formdir/user.def user $1
echo "</div>"
} > $pf
sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"
img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`
imf=$tmpd/profimg.$$; touch $imf
if [ -n "$img" ]; then
if true; then
tbl=user_m
enticond="name='$uname'"
imgsrc_cache "$td/main" user_m "$enticond" M
else
{ printf '%s' "<IMG src=\"data:${img#file:},"
query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \
| sed 's/\(..\)/%\1/g'
echo '">'
}
fi > $imf
fi
nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \
val='$uname';"`
listblog $uname > $bf
hometail=$tmpd/tail.$$
mkfifo $hometail
#Calling listgroupbytable, originally here
(
if [ x"$user" = x"$uname" ]; then
# Display Most Recent Entry
shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}
shortval=${shortval:-val}
# The m.aid in the next line is suspicious. But works fine in SQLite3...
DT_SQL="SELECT b.rowid || '#' || m.aid LINK,
ctime,
(SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,
(SELECT gecos FROM gecoses
WHERE name=(SELECT val FROM blog_s
WHERE key='owner' AND id=b.id)) owner,
(SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text
FROM blog b
JOIN
(SELECT distinct blogid, a.id aid, max(val) ctime
FROM article a, article_s s
ON a.id=s.id AND a.author='$user' AND s.key='ctime'
GROUP BY blogid ORDER BY val DESC LIMIT 50
) m
ON b.id=m.blogid;"
# This should be as follows
: <<EOF
WITH arts AS(
SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,
a.blogid, a.id id, s.val ctime
FROM article a NATURAL JOIN article_s s
WHERE s.key = 'ctime' AND a.author='$user'
GROUP by s.id
)
SELECT a0.brid,a0.blogid,a0.id,a0.ctime
FROM arts a0
JOIN
(SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1
ON a0.blogid=a1.blogid AND a0.ctime=a1.mct
ORDER BY ctime DESC LIMIT 50;
EOF
cat<<-EOF
`cgi_radio foldtabs yes id="mre"`<label
for="mre">最近の書き込み先</label>
<div class="lcto">
`DT_VIEW=replyblog dumptable html blog`
</div>
EOF
unset DT_SQL
# Display NEWS
if false; then
cond="where 新着 > 0 order by 新着 desc,ctime desc limit 10"
new10=`DT_CHLD=article:blogid \
DT_VIEW=replyblog dumptable html blog "ctime title gecos" "$cond"`
else
# 2016-06-26
if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then
acclog blog $blogreadflagrowid
# echo "全部既読にしました" | html p
fi
# 2016-02-19 Counting NEWS without using dumptable.
sql=`listnewblogsql "$user"`
new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`
fi
cont=`echo "$new10"|grep "^<TR>"|wc -l`
cont=$((cont-1))
err newcount=$cont
if [ $cont -gt 0 ]; then
#echo "全体の新着記事${cont}傑" | html h2
cgi_radio foldtabs yes 'id="new10"'
echo "<label for=\"new10\">新着${cont}傑</label><div>"
cat<<-EOF | html form 'action="?home"'
`cgi_checkbox readchk yes 'id="read"'`<label
for="read">新着ふくめて全部読んだことにする</label>
`cgi_submit '確定'`
`cgi_hidden read yes`
EOF
echo "$new10 <!-- new10 -->"
echo "</div>"
fi
fi
) > $hometail & # Is background call safe to m4??
#
listgroupbytable $formdir/grp.def $cond |
_m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \
-D_PROFILE_="spaste(\`$pf')$conflink" \
-D_PROFIMG_="spaste(\`$imf')" \
-D_BLOGS_="spaste(\`$bf')" \
-D_SEARCH_="spaste(\`$sf')" \
-D_NBLOG_="$nblog" \
-D_GROUPS_="syscmd(\`cat')" \
-D_HOMETAIL_="syscmd(\`cat $hometail')" \
$layout/html.m4.html $layout/home.m4.html
# Record access log
[ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1
}
commission() { # $1=grp-rowid $2=user-rowid
contenttype; echo
err commission: "$@"
gname=`getgroupbyid $1`
echo "グループ $gname 管理者委任" \
| _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html
if [ -n "$2" ]; then
grp_reg_adm "$@"
else
echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p
fi
}
listgroupbytable() {
# $1=deffile $2...=condition
tagline=`grep :tag: $1`; shift
and="${1:+and }" where=${1:+where }
href="<a href=\"$myname?grp+"
echo '<div class="listgroup">'
NGsql="select distinct tag from\
(select gname, max(case key when 'tag' then val end) as tag, \
max(case key when 'ctime' then val end) as ctime\
from grp_s group by gname order by ctime);"
sql="select val from grp_s where key='tag' $and$* group by val;"
err ListGRP: query sql="$sql"
for tag in `query "$sql"`
do
err ListGrp: tag=$tag
tn=${tagline%%=${tag}*}
tn=${tn##*[ :]}
sql="select rowid||':'||gname as 'グループ名',説明 from
(select (select rowid from grp g where g.gname=grp_s.gname)
as rowid,
gname,
max(case key when 'gecos' then val end) as '説明',
max(case key when 'tag' then val end) as 'tag',
max(case key when 'mtime' then val end) as mtime from grp_s
$where$* group by gname having tag='$tag' order by mtime desc);"
err PersonalGroupList= `echo $sql`
echo "<h2>$tn</h2>"
echo '<table class="b listgroup">'
sq -header -html $db "$sql" \
| sed "s,\(<TR><TD>\)\([0-9]*\):\([^ ]*\)</TD>,\1$href\2\">\3</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) $4=grprowid(if in grp)
# Referring variable $iamowner=$grp to attach owner-request links
err listentry: \$1=$1 \$2=$2 \$3=$3
cond='' hiddens=''
offset=`getpar offset`
offset=${offset%%[!0-9]*}
offset=$((offset + 0)) # change to numeric forcibly
[ $offset -lt 0 ] && offset=0
limit=30
dir=`getcachedir "$1"`
if [ x"$1" = x"user" ]; then
hrb="$myname?home"
deficon=person-default.png
entity="ユーザ" tbl=user link=rowid nm=name # stage=mems
[ -n "$4" ] && hiddens=`cgi_hidden grid $4`
gcs=gecos
else # if group
hrb="$myname?grp"
deficon=group-default.png
entity="グループ" tbl=grp link=rowid nm=gname stage=grps
gcs=name
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
fi
if [ ! -s $dir/$deficon ]; then
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
# grpは呼出し元の動的スコープ変数でよくないな...
##qgrp=`sqlquote $grp`
getgrp="(select gname from grp where rowid=${rowid:--1})"
sql="select a.rowid, a.$link,
coalesce(b.$gcs, a.$nm) as nick,
coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */
|| case when a.$nm in (select gname from grp)
then printf('(%d名)',
(select count(user) from grp_mem where gname=a.$nm))
else ' <'||a.$nm||'>'
end
as name,
b.tag,
case when a.$nm in (select user from grp_adm
where gname=$getgrp) then '(管理者)'
when '$user' in (select user from grp_adm where gname=a.$nm)
then '(ADMIN)'
when '$user' in (select user from grp_mem where gname=a.$nm)
then '(Member)'
when '$iamowner' = '' then ''
else ',not='||a.rowid end as ownerlink,
CASE '$entity'
WHEN 'グループ'
THEN coalesce(
(SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),
'open')
||
CASE WHEN '$user'
IN (SELECT user FROM grp_mem WHERE gname=a.$nm)
THEN ' ismember'
ELSE ''
END
ELSE 'user'
END regmode
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,
max(case key when 'mtime' then val end) as mtime
from ${tbl}_s group by $nm)
b on a.$nm=b.name $cond $3
order by b.tag desc, b.mtime desc, a.rowid asc"
# Give precedence to newer maintained groups (2016-09-24)
# Note that mtime is stored only in grp_s.
err LE:sql.1="$sql"
total=`query "with x as ($sql) select count(*) from x;"`
echo "${entity} 一覧" | html 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
hiddens="$hiddens
`cgi_hidden kwd \"$kwd\"`
`cgi_hidden stage \"$stage\"`"
cat<<EOF
<p>${total}件中の$((offset+1))件めから${kwd:+" - 検索語: $kwd"}</p>
EOF
if [ $((offset+limit)) -lt $total ]; then
nextbtn=$(
cat<<EOF
<div class="right clear"><form action="$myname" method="POST">
`cgi_submit 次の${limit}件`
$hiddens
`cgi_hidden offset $((offset + limit))`</form></div>
EOF
)
fi
if [ $offset -gt 0 ]; then
prevbtn=$(
cat<<EOF
<form action="$myname" method="POST">
`cgi_submit 前の${limit}件`
$hiddens
`cgi_hidden offset $((offset - limit))`</form>
EOF
)
fi
pnbtn="$nextbtn$prevbtn"
echo $pnbtn
err ListEntry: `echo "$sql"\;`
query "$sql limit $limit ${offset:+offset $offset};" \
| while IFS='|' read id lnk name gecos tag ownerp type; do
err name=$name owner=$ownerp lnk=$lnk
err newlnk=$lnk regmode=$regmode
icondir=$dir/$id
files=`getvalbyid $tbl profimg $id $icondir`
# Pick up only first icon
echo "<div class=\"iconlist xy$thumbxy $type\">
<p class=\"tag _$tag\">$tag</p>" \
| _m4 $tagconv
if [ -n "$files" ]; then
icon=`echo "$files"|head -1`
iconhref2 $icondir/$icon "$hrb+$lnk" "$gecos"
else
iconhref $dir/$deficon "$hrb+$lnk" "$gecos"
fi
echo "<br>$name${ownerp:+<br>$ownerp}"
echo "</div>"
done
echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}
)
listmember() {
listentry user "$@"
}
listgroup() {
listentry group "$@"
}
hexteams() { # $1=gname, $2(optional)=user
cond=${2:+" AND user='$2'"}
query "SELECT DISTINCT hex(val) FROM grp_mem_m
WHERE gname='$1' AND key='team'$cond;"
}
showgroup() { # $1=group-rowid
if [ -z "$1" ]; then
grid=`getpar grid`
grid=${grid%%[!0-9]*}
[ -n "$grid" ] && grp=`getgroupbyid $grid`
else
grid=$1
fi
grp=`getgroupbyid $grid`
qgrp=`sqlquote $grp`
err showgroup2: grp=$grp qgrp="[$(sqlquote $grp)]"
if isgroup "$grp"; then
tf=$tmpd/title.$$
bodyclass=`query "SELECT val FROM grp_s
WHERE gname=$qgrp AND key='regmode';"`
if ismember "$user" "$grp"; then
ismember="ismember"
qgrp=`sqlquote $grp`
bodyclass="$bodyclass${bodyclass:+ }ismember"
else
ismember="" # bodyclass="group"
fi
bodyclass="$bodyclass grouphome"
echo "グループ $grp" > $tf
showgroupsub $formdir/grp.def "$grid" | \
_m4 -D_TITLE_="spaste(\`$tf')" \
-D_FORM_="syscmd(\`cat')" \
-D_BODYCLASS_="$bodyclass" \
-D_DUMPTABLE_="" \
$htmlheader $layout/form+dump.m4.html
# $htmlheader is defined in grp()
else # if $grp is removed at par2table
listgroup
fi
}
showgroupsub() {
# $1=def-file $2=group-rowid
# Using $ismember
rowid=$2
grp=`getgroupbyid $2`
qgrp=`sqlquote $grp`
td=`getcachedir grp/"$2"`
#rowid=`sq $db "select rowid from grp where gname=$qgrp"`
if [ -z "$rowid" ]; then
#rowid=`sq $db "select rowid from grp where rowid=$grp"`
#grp=`sq $db "select gname from grp where rowid=$grp"`
echo "showgroupsub: invalid argument($1 $2)" | html p
return
fi
val=`getvalbyid grp profimg $rowid $tmpd`
enticond="gname=$qgrp"
img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`
if [ -n "$img" ]; then
cat<<-EOF
<p class="groupimg">
`imgsrc_cache $td/main grp_m "$enticond" M`</p>
EOF
fi
echo "<div class=\"noprofimg\">"
viewtable $1 grp $rowid
echo "</div>"
if isgrpowner "$user" "$grp"; then
echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"
iamowner=$rowid
fi
if [ -n "$ismember" ]; then
echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"
echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"
# div.fold input[type="checkbox"]:checked ~ div {display: block;}
cat<<EOF
<form action="?send2mem" method="POST" enctype="multipart/form-data">
<div class="fold clear">
`cgi_checkbox send yes id="send"`<label
for="send">グループ全員にメッセージ送信</label>
<div>
`cgi_textarea message "" "cols=60"`
`cgi_submit 送信`
`cgi_reset リセット`
</div>
`cgi_hidden grp $rowid`
</div></form>
EOF
fi
# 加入ボタン + 加入者リスト
err ismember $user $grp
if [ -n "$ismember" ]; then
ismem='checked' state="(参加中)"
else
nomem='checked' state="(現在非加入)"
fi
# このグループでの加入アドレス
eml=`query "select val from grp_mem_s where gname=$qgrp 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
cat <<EOF
<div class="fold clear">
`cgi_checkbox reg yes id="reg"`<label
for="reg">自身の加入状態を操作する</label>$state
<div>
EOF
cgi_form grp <<EOF
<p>このグループに</p>
<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 $rowid`
EOF
if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then
echo "moderated (承認加入の)グループなので実際に参加できるのは
グループ管理者が承認操作をした後になります。" | html p 'class="warn"'
fi
echo '</div></div>'
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=$qgrp) order by ctime desc"
DT_CHLD=article:blogid \
DT_VIEW=replyblog dumptable html blog 'ctime team title heading' "$cond"
getgname="(select gname from grp where rowid=$rowid)"
c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"
cm="?commission+$rowid"
thumbxy=50x50 listmember "" "$c" "$rowid" \
|sed -e "s|\(<br>\),not=\(.*\)|\1|" # 間違って押しやすい
# team list
hexteams=`hexteams "$grp"`
if [ -n "$hexteams" ]; then
echo "チーム一覧" | html h2
echo '<div class="dumptable"><table class="b">'
sq $db -html -header<<-EOF
SELECT val TEAM,
group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')
MEMBERS
FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;
EOF
echo '</table></div>'
fi
}
grp_getbodyclass() {
# Get css class name for document.
# `moderated' for moderated groups
# `ismember' for groups where user belongs
# $1=GroupName (w/o quote)
# $user=userNameCurrentlyLogin
err grp_getbodyclass: 1="$1"
qgrp=`sqlquote "$1"`
query<<-EOF
SELECT coalesce(
(SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),
'open')
||
CASE WHEN '$user'
IN (SELECT user FROM grp_mem WHERE gname=$qgrp)
THEN ' ismember'
ELSE ''
END;
EOF
}
grpaction() { # $1=group-rowid
err GRP_ACTION:IN
grid=${1:-`getpar grp`}
grp=`getgroupbyid "$grid"`
if [ -z "$grp" ]; then
echo "無効な指定です。" | html p; return
fi
if ! ismember $user $grp; then
echo "加入者のみに許可された操作です。" | html p; return
fi
echo "グループ $grp 個別選択操作" \
| _m4 -D_TITLE_="syscmd(\`cat')" \
-D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \
$layout/html.m4.html
isowner=""
isgrpowner "$user" "$grp" && isowner="yes"
usel=`getpar usel`
if [ -n "$usel" ]; then
uids=$(echo `echo $usel`|tr ' ' ',')
err grpaction-1: grp=$grp, `echo $sql`
text=`getpar text`
rm=`getpar rm` cfm=`getpar confirm`
err rm=$rm cfm=$cfm
if [ x"$rm" = x"yes" ]; then
if [ "$isowner" ]; then
if [ x"$rm$cfm" = x"yesyes" ]; then
# Eliminate
cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"
for tbl in grp_mem grp_mem_s grp_mem_m; do
sql="delete from $tbl $cond;"
# echo "sql=$sql"
query "$sql"
err rmGRPuser "$sql"
done
num=`query "select count(*) from user where rowid in ($uids);"`
#err num=$num
if [ 0$num -gt 0 ]; then
sql="select coalesce(b.val,a.name) from user a left join \
user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"
# err `echo "$sql"`
html pre<<EOF
以下の${num}名のグループ $grp 登録を解除しました。
`query "$sql"`
EOF
fi
else
echo "確認のチェックがないのでやめておきます。" | html p
return
fi
else # not Group Owner
echo "グループ管理者でないのでメンバー操作はできません。" | html p
return
fi
cat<<EOF
EOF
elif [ x"$rm" = x"send" ]; then # if sendmsg mode
if [ -z "$text" ]; then # if msg is empty
echo "なにかメッセージを..." | html p
return 0
fi
gecos=`gecos $user`
mkfrom=`getpar mkfrom`
if [ x"$mkfrom" = x"yes" ]; then
safegc=`echo "$gecos" | tr -d '<>@'`
myuid=`query "SELECT rowid FROM user WHERE name='$user';"`
fromad=`email4groupbyuid "$grp" "$myuid"`
mail_from="$safegc <$fromad>"
else
mail_from="$admin"
fi
MAIL_FROM=$mail_from \
smail "`email4groupbyuid "$grp" $usel` $user" \
"$gecos さんからのメッセージ" <<EOF
$url
のグループ「$grp」のメンバーである $gecos さんから、
あなた宛へのメッセージです。
----------------------------------------------------------
$text
EOF
if [ $? = 0 ]; then
echo "Note: 以下のメンバーにメッセージを送信しました。" | html p
sql="select coalesce(b.val, a.name) from
(select name from user where rowid in ($uids)) a
left join user_s b on a.name=b.name and b.key='gecos';"
html pre<<EOF
`query "$sql"`
(送信者である $gecos さんも含まれます)
EOF
err SendDone: `echo $sql`
fi
elif [ x"$rm" = x"commission" ]; then
grp_reg_adm $grid $usel
elif [ x"$rm" = x"addteam" ]; then
team=`getpar team|sed "s/'/''/g"` # for single quotation
newteam=`echo "$team"|tr -d ,`
if [ x"$team" != x"$newteam" ]; then
echo "チーム名に使えない文字を除去しました" | html p
team=newteam
fi
if [ -z "$team" -o x"$team" = x"なし" ]; then
cat<<-EOF | html p
有効なチーム名を入力してください。
カンマだけ、「なし」という名前は使えません。
EOF
echo "有効なチーム名を入力してください。" | html p
else
grp_add_team $grid "$team" $usel
fi
elif [ x"$rm" = x"rmteam" ]; then
if [ x"yes" = x"`getpar teamconfirm`" ]; then
rmteam=`getpar rmteam|sed "s/'/''/g"`
if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\
gname='$grp' AND user='$user' AND key='team'\
AND val='$rmteam';\"`" ]; then
grp_rm_team $grid "$rmteam" $usel
else
echo "所属していないチームの除去操作はできません。"|html p
fi
else
echo "確認チェックなしなのでチーム除去しませんでした。"|html p
fi
fi
fi
# New entry
sql="select /* Ahh, ugly SQL, I wanna fix... */
case
when (select user from grp_adm where
gname=(select gname from grp where rowid=$grid)
and user=a.name) is not null
then 'k'
else ''
end || a.rowid||
','||a.gecos as NAME,
(SELECT count(author) /* Put post count for scoring 2016-08-01 */
FROM article NATURAL JOIN article_s
WHERE blogid IN
(SELECT id FROM blog_s
WHERE key='owner'
AND val=(SELECT gname FROM grp where rowid=$grid))
AND author=a.name AND key='text')
as POST,
(SELECT group_concat(val, ',')
FROM grp_mem_m
WHERE gname='$grp' AND user=a.name AND key='team') as TEAM
FROM
gecoses a
WHERE name in (select user from grp_mem where
gname=(select gname from grp where rowid=$grid))
ORDER by a.gecos;"
err grpaction: "`echo \"$sql\"`"
tf=$tmpd/title.$$
echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf
cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label
for=\"cmadmin\">グループ管理者委任</label>
<div><p>このグループでの全権を付与します。信頼できる人に託してください。
</p></div>"
excmsg="`cgi_radio rm yes id=\"conf\"`<label
for=\"conf\">グループ登録解除</label>
<div>本当に消します! `cgi_checkbox confirm yes` 確認
<p>この操作による通知は本人に行きません。
あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>
</div>"
# Get team list to which current user belongs into $hexteams
myhexteams=$(hexteams "$grp" "$user")
allhexteams=$(hexteams "$grp")
if [ -n "$myhexteams" ]; then
rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label
for=\"cmrmteam\">チーム属性除去</label>
<div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`
を除去します: `cgi_checkbox teamconfirm yes` 確認
<p>この操作による通知は本人に行きません。
あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>
</div><!-- end of $rmteammsg -->
"
fi
b1='<label> <input type="checkbox" name="usel" value="'
ba='<label class="admin"><input type="checkbox" name="usel" value="'
#b2='"> <span>' b3='</span></label>'
# | sed -e "s|^\(<TR><TD>\)k\([0-9]*\),\([^<]*\)|\1$ba\2$b2\3$b3|" \
# -e "s|^\(<TR><TD>\)\([0-9]*\),\([^<]*\)|\1$b1\2$b2\3$b3|" \
lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'
cgi_form grpaction<<EOF \
| sed -e "s|^\(<TR><TD>\)k\([0-9]*\),\([^<]*\)|\1$ba\2$lnk|" \
-e "s|^\(<TR><TD>\)\([0-9]*\),\([^<]*\)|\1$b1\2$lnk|" \
| _m4 -D_TITLE_="spaste(\`$tf')" \
-D_SUBTITLE_="チェック後操作ボタン" \
-D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \
$layout/form+dump.m4.html
<p>下でチェックした人を対象として:</p>
<div class="foldtabs">
`cgi_radio rm addteam 'id="cmteam"'`<label
for="cmteam">同じチーム属性を付与</label>
<div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`
`cgi_datalist_h teams $allhexteams`
</div>
${rmteammsg}
`cgi_radio rm send id="sendmsg"`<label
for="sendmsg">メッセージ送信</label>
<div>
`cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"
>差出人を自分に(チェックを外すと相手が返事できない)</label><br>
`cgi_textarea text "" cols=40`
</div>
${isowner:+$cmmsg$excmsg}
`cgi_radio rm close id="x"`<label for="x">×</label>
</div>
<h4>$grp 参加者一覧</h4>
<table class="td2r">
`sq $db -header -html "$sql"`
</table>
`cgi_hidden grp $grid`
EOF
}
crview4article() { # $1=rowid of blog, $2(optional)=extra SQL
# Create TEMPORARY VIEW
query<<EOF
CREATE TEMPORARY VIEW writeusers AS
SELECT DISTINCT author FROM article
WHERE id in (
select id from article where blogid=(select id from blog where rowid=$1)
);
CREATE TEMPORARY VIEW movablegroups AS
SELECT g.rowid growid , g.gname
FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm
ON grp.gname=gm.gname -- そのユーザが属している
AND user='$user') g -- グループに絞る
WHERE (SELECT author FROM writeusers
EXCEPT
SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)
IS NULL;
$2
EOF
}
sql4readableblogs() {
# Create view of blogs that can be readable to $user
# Blog is readable when:
# 1: blog owner is an user
# 2: else, 2.1: owner-group where the $user belongs
# 2.2: else, owner-group is not moderated
# blog(id, author), blog_s(id, key='owner', val= ->owner)
cat<<EOF ## | tee tmp/sql.out
CREATE TEMPORARY VIEW readableblogs AS
SELECT blog.rowid rid, id, author
FROM blog
NATURAL JOIN
(SELECT id, val owner FROM blog_s WHERE key='owner') bs
WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL
THEN 1 -- blog owner is an user, READABLE
WHEN (SELECT val FROM grp_s
WHERE gname=bs.owner AND key='regmode') = 'moderated'
AND
(SELECT user FROM grp_mem
WHERE gname=bs.owner AND user='$user') IS NULL
THEN 0
ELSE 1
END;
EOF
}
editheading() { # $1=rowid-of-heading
rowid=${1%%[!A-Z0-9a-z_]*}
if [ -z "$rowid" ]; then
echo "話題番号が未指定です。" | html p
return
fi
owner=`getvalbyid blog owner $rowid`
title=`getvalbyid blog title $rowid`
GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \
| _m4 -D_TITLE_="修正" \
-D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \
-D_BLOGS_="" -D_DUMPTABLE_="" \
-D_FORM_="syscmd(\`cat')" \
$layout/html.m4.html $layout/form+dump.m4.html
# Move to group
if isuser "$owner"; then
crview4article $rowid
n=`query "SELECT count(*) FROM writeusers;"`
err N=$n
if [ $((n)) -gt 0 ]; then
err ROWID=$rowid
sql="SELECT growid || ':' || gname FROM movablegroups;"
cat<<-EOF
<div class="fold">
`cgi_checkbox mv send id="mv"`<label
for="mv">この話題をグループ所有に移動する</label>
<div>
<form action="?mvart" method="POST" enctype="multipart/form-data">
移動先グループ:
<select name="mv2grp">
EOF
query ".mode html"
query<<-EOF |
$sql
.mode list
EOF
sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \
-e 's,n>\([0-9]*\):\(.*\)<,n value="\1">\2<,'
cat<<-EOF
</select>
<p>(移動できるグループは、この「話題」に書き込んでいる人全てが
そのグループに加入しているものに限られます)</p>
<p>`cgi_checkbox cfm yes`<label>確認
(この操作は元に戻すことができません)</label></p>
`cgi_hidden blogrowid $rowid`
`cgi_submit 移動`
`cgi_reset Reset`
</form>
</div>
</div>
EOF
fi
# end of isuser "$owner"
elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP
[ -n "$hexteams" ];}; then
none="`echo なし|hexize`"
cat<<-EOF
<div class="fold">
`cgi_checkbox mv2team send id="mv2team"`<label
for="mv2team">この話題を以下のチームのものにする</label>
<div><p>現在の所属チーム設定:
`query "SELECT
coalesce((SELECT val FROM blog_s
WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)
AND key='team'),
':なし');"`</p>
<form action="?mvart" method="POST" enctype="multipart/form-data">
移動先チーム: `cgi_select_h mv2team $none $hexteams`
<p>`cgi_checkbox cfm yes`<label>確認</label></p>
`cgi_hidden blogrowid $rowid`<br>
`cgi_submit 移動`
`cgi_reset Reset`
</form></div></div>
EOF
fi
}
mvart() { # move diary to some group or team
# or move blog of group to team which belong to the group
blogrowid=`getpar blogrowid`
cfm=`getpar cfm`
##### echo move blog:$blogrowid to $mv2grp | html p
blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify
. ./s4-blog.sh
if [ -z "$blogrowid" ]; then
echo "無効な指定です(mvart)。" | html p
return
elif [ x"$cfm" != x"yes" ]; then
echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p
elif { mv2grp=`getpar mv2grp`
mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify
[ -n "$mv2grp" ]; }; then
crview4article $blogrowid
########## TRANSACTION BEGIN
query "BEGIN;"
n=`query "SELECT count(*) FROM writeusers;"`
err Nwriteuser=$n
if [ $((n)) -gt 0 ]; then
query<<-EOF
UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)
WHERE key='owner'
AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)
AND $mv2grp IN (SELECT growid FROM movablegroups);
EOF
fi
query "END;"
########## TRANSACTION END
elif { mv2team=`getpar mv2team|sed "s/'/''/g"`
[ -n "$mv2team" ];}; then
# blog owner can move it to ANY team
case "$mv2team" in
'なし')
cat<<-EOF
DELETE FROM blog_s
WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)
AND key='team';
EOF
;;
"") ;;
*)cat<<-EOF
BEGIN;
REPLACE INTO blog_s(id, key, val)
VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),
'team', '$mv2team');
REPLACE INTO blog_s(id, key, val)
VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),
'notify', 'all'); -- Change notify to all
END;
EOF
esac | query
fi
blog_reply $blogrowid
echo yes | html p
}
editart() { # $1=article-rowid $2=blogrowid
rowid=${1%%[!A-Z0-9a-z_]*}
blogrowid=${2%%[!A-Z0-9a-z_]*}
if [ -z "$rowid" -o -z "$blogrowid" ]; then
echo "表示する記事番号が未指定です。" | html p
return
fi
owner=`getvalbyid blog owner $blogrowid`
title=`getvalbyid blog title $blogrowid`
author=`getvalbyid article author $rowid`
err EDITart: owner=$owner, author=$author
if isgrpowner $user $owner; then
: EDIT OK
elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then
echo "本人か所有者しか編集できません." | html p
return
fi
aid=`query "select id from article where rowid=$rowid;"`
tmpout=$tmpd/editart.$$.out
GF_ACTION="?replyblog+$blogrowid#$aid" \
edittable $formdir/article.def article $rowid \
> $tmpout
rm -f /tmp/editart.out
# Cannot use pipelining to m4 with genform() because of stdin stack
_m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \
-D_FORM_="syscmd(cat $tmpout)" \
-D_SUBTITLE_="`gecos $owner`の「$title」" \
-D_BLOGS_= -D_DUMPTABLE_= \
$layout/html.m4.html $layout/form+dump.m4.html
}
send2mem() {
rowid=`getpar grp`
if [ -z "$rowid" ]; then
echo "グループが未指定です。" | html p
return
fi
message=`getpar message`
if [ -z "$message" ]; then
echo "文章を入れてください。" | html p
return
fi
grp=`getgroupbyid $rowid`
members=`collectemail $grp`
# smail rcpt subj (file)
smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF
$urlbase?grp+$rowid
グループ $grp に所属する
`gecos $user` さんよりメッセージ:
$message
EOF
cat<<EOF
<p>以下の宛先に送信しました。</p>
<pre>
$members
</pre>
<p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>
EOF
}
joingrpadmit() {
# $1=yes/no $2=session-key
if [ -z "$2" ]; then
echo "bye bye" | html p; return
fi
t_usr=`session=$2 getpar user`
t_grp=`session=$2 getpar group`
err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp
_m4 -D_TITLE_="joingrp" $layout/html.m4.html
if [ -z "$t_usr" -o -z "$t_grp" ]; then
echo "無効な加入依頼です。" | html p
echo "有効期限が切れたか、
他の管理者がいる場合は処理済みの可能性があります。" | html p
return
fi
if ! isgrpowner "$user" $t_grp; then
echo "グループ管理者のみの機能です。" | html p; return
fi
case $1 in
yes) joingrp "$t_grp" "$t_usr" yes ;;
no) joingrp "$t_grp" "$t_usr" no ;;
*)
echo "無効な指定です($1)。" | html p
return ;;
esac
gid=$(query "select rowid from grp where gname=`sqlquote $t_grp`;")
rcpts="`getgroupadminmails $t_grp` $user"
err admit: msgdir=$msgdir, rcpts="["$rcpts"]"
body="グループ <a href=\"?grp+$gid\">$t_grp</a>
に
$t_usr
`[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`
しました。"
(echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"
query "delete from session where id='$2';"
echo "$body" | html p
}
joingrprequest() {
# $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)
jss="joingrp-`date +%s`-`genrandom 12`"
addsession $jss +${memoplimitdays}days
query "replace into par values('$jss', 'group', 'string', `sqlquote $1`),
('$jss', 'user', 'string', `sqlquote $user`);"
smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF
$url
$user さんから
グループ $1
に加入依頼がありました。
承認する:
$urlbase?joingrpadmit+yes+$jss
白紙に戻す:
$urlbase?joingrpadmit+no+$jss
EOF
echo "管理者に加入依頼を出しました。
${memoplimitdays}日以内に加入承認操作がされれば加入できますが、
グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p
}
joingrp() {
# $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)
err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4
if isgrpowner "$user" "$1"; then
isowner="yes"
elif [ -n "$5" ]; then
isowner="yes"
else
isowner=""
fi
err jg:isgrpowner: isowner="$isowner"
if [ -n "$isowner" ]; then
: # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT
elif [ x"$2" != x"$user" ]; then # if user is not login user
echo "本人か、グループ管理者しか加入操作はできません。" | html p
return
elif [ x"$3" = x"no" ]; then
: # Do not pursue those who leave
elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then
: # Member can change own email address for the joining moderated group
else # adding user is $user itself
case `getgroupattr $1 regmode` in
moderated)
joingrprequest "$@" # Request only
return
;;
*)
;;
esac
fi
qgname=`sqlquote $1`
cond="where gname=$qgname and user='$2'"
if [ x"$3" = x"yes" ]; then
query "replace into grp_mem values($qgname, '$2');"
if [ -n "$4" ]; then
if msg=`emaildomaincheck "$4"`; then
err "replace into grp_mem_s values($qgname, '$user', 'email', \
'string', '$4', NULL);"
query "replace into grp_mem_s values($qgname, '$user', 'email', \
'string', '$4', NULL);"
else
echo $msg
fi
else
query "delete from grp_mem_s $cond and key='email';"
fi
if [ -n "$5" ]; then # as ADMIN
# Coming here means newly created group
sql="select case\
when (select count(*) from grp_mem where gname=$qgname)=1\
then (select user from grp_mem\
where gname=$qgname and user='$user')\
else '' end; "
err NewGrpChk: $sql
if [ -n "`query \"$sql\"`" ]; then
err ADMIN: "replace into grp_adm values($qgname, '$user');"
query "replace into grp_adm values($qgname, '$user');"
fi
fi
else
query "delete from grp_mem $cond;
delete from grp_mem_s $cond;
delete from grp_mem_m $cond;"
fi
}
grp_add_team() (
# $1=grp-rowid $2=team $3...=user-rowid(s)
grp=`getgroupbyid $1`
team=$2; shift; shift
[ -z "$grid" -o -z "$team" -o -z "$1" ] && return
{ echo "BEGIN;"
for user; do
echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\
'$grp',\
(SELECT name FROM user WHERE rowid=$user),\
'team', 'string', '$team');"
done
echo "END;"
} | query
)
grp_rm_team() (
# $1=grp-rowid $2=team $3...=user-rowid(s)
grid=$1
qgrp=$(sqlquote "`getgroupbyid $grid`")
team=$2; shift; shift
[ -z "$grid" -o -z "$team" ] && return
{ echo "BEGIN;"
for user; do
echo "DELETE FROM grp_mem_m\
WHERE gname=$qgrp \
AND user=(SELECT name FROM user WHERE rowid=$user)\
AND key='team' AND val='$team';"
done
cat<<-EOF
DELETE FROM blog_s
WHERE rowid=(
SELECT rowid
FROM blog_s a
WHERE key='team'
AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)
AND NOT EXISTS (SELECT * FROM grp_mem_m
WHERE key='team' AND val=a.val -- a.val=team
AND gname = (SELECT val FROM blog_s b
WHERE a.id=b.id AND key='owner')
));
EOF
echo "END;"
} | query
)
grp_reg_adm() {
# $1=grp-rowid $2...=user-rowid
grid=$1
grp=`getgroupbyid "$1"`
if [ -z "$grp" ]; then
echo "無効なグループIDです" | html p; return
fi
if ! isgrpowner $user "$grp"; then
echo "$grp グループの管理者しかこの操作はできません。" | html p; return
fi
shift
for urid; do
newadm=`query "select name from user where rowid=$urid;"`
if [ -z "$newadm" ]; then
echo "指定ユーザIDがおかしいようです。" | html p; return
fi
err GRP_reg_adm: "replace into grp_adm values(`sqlquote $grp`, '$newadm');"
err ismember $newadm $grp
if ismember $newadm $grp; then
# OK, go ahead
getgname="(select gname from grp where rowid=$grid)"
query "replace into grp_adm values($getgname, '$newadm');"
# confirm insertion
sql="select * from grp_adm where gname=$getgname and user='$newadm'"
if [ -n "`query \"$sql;\"`" ]; then
echo "追加完了: $newadm" | html p
else
echo "追加失敗($1 $urid)" | html p
fi
fi
showgroup $grid
done
}
dumptable() {
# $1=mode $2=Table $3=column-list-of-*_s(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\\3\">VIE</a><a href=\"$myname?$VIEW+\\2#bottom\">W</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)"
# XXX: Dirty workaround for slow subquery of acclog
presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS
SELECT * FROM acclog WHERE user='$user' and tbl='$2';"
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 myacclog where \
tblrowid=a.rowid),\
'1970-01-01'))"
cnt="$cntnew as '新着', $cntall as '総数',"
dt_class=" td2r td3r dumpblogs"
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
case $col in
gecos) scols="$scols${scols:+, }${col#}"
continue ;; # built-in column name
*:*) col=${col%:*} as=${col#*:} ;;
*) as=${col} ;;
esac
eav=$eav${eav:+,}" max(case key when '$col' then $substr end) as $as"
scols="$scols${scols:+, }b.$as"
done
#case author when '$user' then a.rowid else '---' end as ID,
sql=${DT_SQL:-"select \
a.rowid as LINK,\
$cnt\
$scols from $2 a left join\
(select $pk,$eav,
max(case key when 'owner'
then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos
from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}
err SQL="`echo \"$presql$sql\"|tr -d '\n'`"
cat<<EOF | sed "s,\(<TR><TD>\)\([1-9][0-9]*\)\(#[0-9a-fxs]*\)*</TD>,\1$elink$dvlink</TD>,"
<div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->
<div class="dumptable">
<table class="b$dt_class">
`sq -header -cmd ".mode $1" $db "$presql$sql"`
</table>
</div> <!-- dumptable -->
</div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->
EOF
}
par2table() (
# copy current parameters of par into destination table
# $1=definition-file
# Using $user and $session
# Return value:
# 0: Stored successfully
# 1: Insufficient fillings
# 2: No permission to modify the record
# 3: Invalid rowid
# 4: SUCCESS to delete
# 5: Stop deletion for lack of confirm check
# 6: Password length too short
# 7: Password mismatch
# 8: Old password incorrect
rowid=`getpar rowid`
err ...........rowid=$rowid
if [ ! -e $1 ]; then
echo "テーブル定義ファイルが見付かりません" | html 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
sql="select gname from $tbl where rowid=$rowid;"
##err p2t:grp:q $sql
isgrpowner $user "`query $sql`" && rowowner=$user
else
rowowner=`query "select owner from $tbl where rowid=$rowid;"`
rowowner=${rowowner:-`query "select author from $tbl
where rowid=$rowid;"`}
fi
### err rowowner=$rowowner
if [ x"$user" != x"$rowowner" ]; then
echo "他人のレコードはいじれないの" | html p
return 2
elif [ -z "$rowowner" ]; then
echo "指定したレコードはないみたい" | html p
return 3
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 4
else
echo "消去確認のチェックがないので消さなかったの..." | html p
return 5
fi
fi
fi
ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""
if [ -n "$rowid" ]; then
# Update of existing record
for col in `gettblcols $tbl`; do
val=`getparquote $col`
[ -z "$val" ] && continue
err query "update $tbl set $col=$val where rowid=$rowid"
## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s
sql="update $tbl set $col=$val where rowid=$rowid;"
if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \
-o x"tbl" = x"user" -a x"$col" = x"name" ]; then
## User name cannot be changed with interface provided with this
## script. But we offer the trigger to change owner user
## of blog_s table.
err "select quote($col) from $tbl where rowid=$rowid;"
old=`query "select quote($col) from $tbl where rowid=$rowid;"`
cat<<-EOF | query
BEGIN;
$sql
update blog_s set val=$val
where key='owner' and val=$old;
COMMIT;
EOF
## XX: DIRTY Hack Ends here
## We should keep blog's owner as a single column which has
## foreign key constraint with primary key of grp/user.
else
query "$sql"
fi
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 "項目を全て埋めてください" | html pre
return 1
fi
err "replace into $tbl values($formaster);"
query "replace into $tbl values($formaster);"
## 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
st_rowid=${v##*.}
origcol=${v%%.*} # original column derived from
err Updating for $v st_rowid=$st_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 "削除確認未チェック" | html p
fi ;;
edit)
case `gettbl_coltype $tbl/$origcol` in
image|document|binary)
file=$tmpd/`getparfilename $v`
err type=file=$file
[ -z "$file" ] && continue
bn=`sqlquotestr "${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"
cachedir=`getcachedir "$tbl/$rowid"`
err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir
rm -rf $cachedir
;;
*)
newsql="update $tb2 set val=(select val from par where var \
like '$col.%.$st_rowid')"
;;
esac
;;
*) # maybe "keep", do not modify value
continue
;;
esac
# err newsql=$newsql
sql=$sql$nl"$newsql where rowid=$st_rowid;"
done
if [ x"$bin" = x"NULL" ]; then
err repl:normal sql=`echo $sql`
query "$sql
delete from $tb2 where type='string' and val='';"
err repl:normal done
else
sqlfile="$tmpd/sqlf.$$"
echo "$sql" > $sqlfile
err repl:sqlfile=`ls -lF $sqlfile`
query ".read $sqlfile"
err repl:done
fi
# 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=$tmpd/`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 "パスワードは10字以上にしてください。" | html p
return 6;;
esac
val="\"`echo $p1|mypwhash`\""
else
echo "2つの新パスワード不一致" | html p
return 7
fi
else
echo "旧パスワード違います" | html p
return 8
fi
fi
;;
esac
err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"
#query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"
sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"
if [ x"$bin" = x"NULL" ]; then
err Normal-query: `echo $sql`
query "$sql"
else
sqlfile="$tmpd/query.$$"
echo "$sql" > $sqlfile
err sqlfile=`ls -lF $sqlfile`
query ".read $sqlfile"
fi
err p2t done
done
done
done
return 0
##err donee
)
genform() {
# $1 = form definition file
# $2, $3 (optional)= table name and ROWID
# If $GF_VIEWONLY set and nonNull, output values without form
# If $GF_ARGS set, use it as content-strings in the form
# 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 "そのようなデータベースはないようです($2)。" | html 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 "そんなレコードはないみたいね..." | html 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
# Image Cache dir
err genform: getcache=$2/$rowid
td=`getcachedir "$2/$rowid"`
while IFS=: read prompt name keytype type args; do
[ -z "${prompt%%\#*}" ] && continue # skip comment line(#)
sp="${args:+ }"
form="" val=""
if [ -n "$rowid" ]; then
# err genform2a: Seeking for "$2.$name, type=$type"
rawval=`getvalbyid $2 $name $rowid $td`
val=`echo "$rawval"|htmlescape`
err genform3a: getvalbyid $2 $name $rowid $td
err genform3b: val="[$val]" type="$type"
fi
if [ -n "$GF_VIEWONLY" ]; then
is_hidden "$2" "$name" && continue
fi
case "$type" in
text*)
cgiform=cgi_multi_$type
if [ -s $td/$name.count -a -n "$val" ]; then
form=`$cgiform $name $td`
val=$(echo "$val"|
while read fn; do
echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`
</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="<label><input type=\"radio\" name=\"$name\""
form="`echo $args|sed -e \
\"s,\([^ =][^=]*\)=\([^= ][^= ]*\),$fh value=\\"\2\\">\1</label>,g\"`"
;;
[Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])
form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"
;;
[Ss][Ee][Ll][Ee][Cc][Tt])
fh="<select name=\"$name\">$nl"
form=$(for l in $args; do
echo "<option value=\"${l#*=}\">${l%=*}</option>"
done)
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 $td/$name.count ]; then
err Calling cgi_multi_file: "nae=[$name] td=[$td] args=[$args]"
form=`cgi_multi_file $name $td "$args"`
if [ -n "$val" ]; then
hrfb="$myname?showattc+$2_m"
val=$(echo "$rawval" \
| while read fn; do
data=`percenthex "$td/$fn"`
#ct=`cat $td/$fn.content-type`
ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`
ri=`cat "$td/$fn.rowid"`
err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3
#imgsrc="<img src=\"data:$ct,$data\">"
#echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"
iconhref "$td/$fn" "$hrfb+$ri" ""
done)
fi
else
form="<input type=\"file\" name=\"$name\" $args>"
if [ -n "$val" ]; then
imgs=$(echo "$rawval"\
|while read fn;do
data=`percenthex "$td/$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])
if [ -z "$rowid" ]; then
val=`genserial`
fi
form="<input type=\"hidden\" name=\"$name\" 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
${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}
${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
$GF_ARGS</form>
$tail
EOF
}
edittable() {
# $1=form-def $2=table $3 rowid
genform "$@"
}
viewtable() {
GF_VIEWONLY=1 genform "$@"
}
showattc() {
# $1=table_m $2=rowid
err \$1=$1 \$2=$2
if ! isfilereadable $user $1 $2; then
contenttype; echo
echo "このファイルは管理者のみしか見られません" | html p
putfooter; exit
fi
idir=`umask 002; mktempd` || exit 1
# tmpfiles=$tmpfiles"${tmpfiles+ }$idir"
bin=$idir/$myname-$$.bin
sql="select quote(bin) from $1 where rowid='$2';"
err showattc: sql: $sql
sq $db "$sql" | unhexize > $bin
tv=`query "select type||'//'||val from $1 where rowid='$2';"`
type=${tv%//*} fn=${tv#*//}
err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}
ct=${type#file:}
case $ct in # all text/* changed to text/plain
text/*)
charset=`nkf -g $bin|cut -d' ' -f1`
case $charset in
ASCII*) charset="" ;;
esac
ct="text/plain${charset:+; charset=$charset}"
;;
esac
contenttype "$ct"
echo "Content-Disposition: filename=\"$fn\""
echo "Content-Length: " `cat $bin | wc -c`; echo
#echo "Content-Type: " ${type#file:}; echo
cat $bin
}
#
# Some default stupid handler on CGI values
#
default_storedb() {
# ARG: $1=table-def-file
# RET: $tbl=table-name, $col=mail-column, $cols=columns
tbl=`basename $1`
tbl=${tbl%.def}
cols="`grep :text $1|cut -d: -f2`"
col=`echo "$cols"|head -1`
vcol=`getpar $col`
err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol
if [ -n "$vcol" ]; then
par2table $1
else
return 2 # No insertion occurred
fi
}
default_view() { # $1=def-file
### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \
## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \
default_storedb "$@"
query "select rowid from $tbl order by rowid desc;" \
| while read rowid; do
viewtable $1 $tbl $rowid
done | _m4 -D_TITLE_="$tbl" \
-D_FORM_="`genform $1`" \
-D_DUMPTABLE_="syscmd(cat)" \
$layout/html.m4.html $layout/form+dump.m4.html
}
default_viewtext() { # $1=def-file
### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \
default_storedb "$@"
DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \
| _m4 -D_TITLE_="$tbl" \
-D_FORM_="`genform $1`" \
-D_DUMPTABLE_="syscmd(cat)" \
$layout/html.m4.html $layout/form+dump.m4.html
}
default_smail() {
default_storedb "$@"
if [ $? -eq 2 ]; then
_m4 -D_TITLE_="入力" \
-D_FORM_="`genform $1`" \
-D_DUMPTABLE_="" \
$layout/html.m4.html $layout/form+dump.m4.html
return
fi
cond=""
for pk in `gettblpkey $tbl`; do
pv=$(sqlquote $(getpar $pk))
cond="$cond${cond:+ and }$pk=$pv"
done
sql="select rowid from $tbl where $cond;"
rowid=`query "$sql"`
err smail1 - "$sql" "-> rowid=$rowid"
while IFS=: read prompt name keytype type args; do # Read from $1
val=`getpar $name`
if [ -n "$val" ]; then
text="$text
$prompt
$name=$val
---------------------------------------------------------"
fi
case "$type" in
image|document|file)
fn="`getvalbyid $tbl $name $rowid $tmpd`"
fns=$(echo "$fn"|while read fn; do
err mv $tmpd/$fn.orig $tmpd/$fn
mv $tmpd/$fn.orig $tmpd/$fn
rm $tmpd/$fn.rowid # Remove cache flag
err "`ls $tmpd/$fn`"
echo $fn
done)
files="$files $fns"
;;
esac
done < $1
err FILES=$files "`ls -lF $tmpd`"
subj="from ${REMOTE_ADDR}"
(echo "$url"
echo "への書き込みがありました。"
echo "------"
echo "$text"
) | (cd $tmpd &&
err LS="`ls -lF`" &&
$mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)
_m4 -D_TITLE_="入力完了" $layout/html.m4.html
echo "以下の内容で送信しました。" | html p
viewtable $1 $tbl \
`query "select rowid from $tbl order by rowid desc limit 1;"`
echo "戻る" | html a "href=\"?\""
}
HIROSE Yuuji