#!/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="$@"
test -n "$HTTP_HOST" && isCGI=true
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=""
querylog=${QUERYLOG:-$tmpdir/query.log}
searchlog=${SEARCHLOG:-$tmpdir/search.log}
db=${DB:-$dbdir/cgi.sq3}
sessdb=$dbdir/sess.sq3
skey='skey' # Changed in s4-annex, if ANNEX mode
userupdateflag=$dbdir/userupdate
sesstb=tmp.sess
workdb=$dbdir/tmpdata.sq3
listentlimit=${LISTENTLIMIT:-30}
admin=${ADMIN:-hostmaster@example.org}
noreply=${NOREPLY:-noreply@example.org}
noreply_from="${S4NAME:-s4} message notification <$noreply>"
invite_policy=${INVITE_POLICY:-"このコミュニティに関りのあるあなたの信頼できる人を招きます。"}
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
filesize_max=$((5*1024*1024))
filesize_max_MB="$((filesize_max/1024/1024))MB"
file_accept='accept="image/*,text/*,audio/*,application/vnd.oasis.*,application/pdf,application/x-*"'
file_accept='accept=".jpg,.jpeg,.gif,.png,.tiff,.pdf,.odt,.ods,.odp,.odg,.mp3,.mp4,.avi,.ogg,.mov,.webm,.gpx,.json,.geojson,.kml,.html,.css,.rb,.c,.txt,.zip,.xcf,.bz2,.gz,.xz,.7z,.csv,.dat"'
file_accept_egrep='^(text/|message/|image/|audio/|video/|application/(vnd.oasis|pdf|epub|xml|zip|[xz]-))'
file_accept_help="
添付可能ファイル: テキスト、画像、音声、動画、ODF、PDF、
圧縮ファイル、データベースファイル
(いずれも ${filesize_max_MB} 以内)
"
file_warn="$file_accept_help
[編集]リンクから修正してください。"
blogreadflagrowid=0
blogcutoffflagrowid=-1
whatsnewdays=${WHATS_NEW_DAYS:-14}
main_session=`date +%F-$$`
session=$main_session
tconfs=""
imgcached=cache/img.`date +%Y/%m`
conftbl=_tblconf
nl="
"
likeesc=`printf '\037'` # ESCAPE char of LIKE operator
iconcachekey="profimgcache_S"
# Start debug logging
exec 3>> $tmpdir/debug.out
err() {
echo "[`date +%F-%T%z`] $@" 1>&3
}
# If S4MASTERDB is set, behave as ANNEX s4
if [ -n "$S4MASTERDB" -a -s "$S4MASTERDB" ]; then
. ./s4-annex.sh 2>> tmp/debug.out
fi
case "$HTTP_USER_AGENT" in
*i[Pp]hone*|*[Aa]ndroid*) touchpanel=1 ;;
*) touchpanel="" ;;
esac
[ -f ./s4-cgi.sh ] && . ./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
logstart() {
echo "`date '+%F %T'`:[${user:-NULL}] <<<" >> ${1:-$querylog}
}
logend() {
echo ">>>" >> ${1:-$querylog}
}
sqlog() {
logstart
if [ -z "$1" ]; then
cat >> $querylog
else
echo "$*" >> $querylog
fi
logend
}
sq() {
# ./args.rb -cmd ".timeout 3000" "$@"
logstart
if [ -z "$1" ]; then
tee -a $querylog|sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000"
else
echo "$@" >> $querylog
sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000" "$@"
###sqlite3 -bail -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000" "$@"
fi
logend
}
dbsetup() {
pipedir=$tmpdir/pipedir
[ -d $pipedir ] || mkdir -p -m 1777 $pipedir
[ -d $dbdir ] || mkdir -m 1775 $dbdir
suf=`date +%s`
sqi=$pipedir/sqi-$suf.$$
sqo=$pipedir/sqo-$suf.$$
mkfifo $sqi $sqo
#tail -f $sqi | sq $db & # "tail -f" is too heavy. DO NOT USE!!
sq $db < $sqi &
sq3pid="`jobs -p` $!"
if [ -n "$isCGI" ]; then
exec 2>> $tmpdir/error.out
fi
exec 5> $sqi # Turning $sqi access through fd5 for continuous open state
chmod o-r $tmpdir/error.out $tmpdir/debug.out
rm $sqi
# Attach supplemental DB
cat >&5 <<-EOF
.output /dev/null
ATTACH DATABASE "$sessdb" AS tmp;
CREATE TABLE IF NOT EXISTS $sesstb(user, skey, expire, UNIQUE(user, skey));
DELETE FROM $sesstb WHERE expire < datetime('now', 'localtime');
DELETE FROM $sesstb WHERE expire is NULL or expire = '';
.output stdout
EOF
}
cleanup2() { # Dirty workaround for produced zombie processes
if [ -n "$HTTP_USER_AGENT" ]; then # When called from httpd
pkill -9 -u `id -u` -P 1
fi
chmod o-r $querylog $db $sessdb # make sure
}
cleanup() {
trap '' INT HUP EXIT TERM PIPE
echo .quit >&5
kill $sq3pid
kill $sq3pid
rm -f $sqo $sqi
rm -rf $tmpfiles
cleanup2
}
# We want to use piped function to put querylog, but we use
# simple redirection for the sake of speed.
query() {
# echo ".once $sqo" >&5
echo ".output $sqo" >&5
logstart
if [ -z "$1" ]; then
tee -a $querylog
else
echo "$@" >> $querylog
echo "$@"
fi >&5
echo ".output stdout" >&5
cat $sqo
rc=$?
logend
return $rc
}
_m4() {
#S4NAME=f,f,f
m4 ${S4NAME:+"-D_S4NAME_=${S4NAME}"} ${S4CSS:+-D_S4CSS_="$S4CSS"} "$@"
}
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`"
)
isgrpownerbygid() (
# $1=user, $2=group-rowid
sql="select user from grp_adm where gname=(select gname from grp where rowid=$2) and user='$1';"
err isgrpownerbygid: $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'),
getauthor as (select author from article where id=(select id from $2 where rowid=$3)),
isgrp as (SELECT val from getowner WHERE val IN (select gname from grp)),
isgrpadm as (select user from grp_adm where
gname=(select val from getowner) and
user='$1'),
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) in ('quiz', 'enquete')
then CASE
WHEN (SELECT val FROM isgrp) IS NULL
THEN
CASE WHEN (SELECT val from getowner)
IN ('$user', (SELECT author FROM getauthor))
THEN 'owner-or-user-article-is-readable'
ELSE ''
END
WHEN (select user from isgrpadm) IS NOT NULL
THEN 'i-am-admin'
ELSE (SELECT author from getauthor WHERE author IN (SELECT user FROM grp_adm WHERE gname=(SELECT val FROM getowner)))
END
when (select val from getmode) is null
then 'open'
when (select val from getowner) in (select gname from grp)
then (SELECT user FROM isgrpadm)
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"`
# err FileAccessibility=$result
[ -n "`query $sql`" ] || 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
# ^video=URL
# [[#NUM]] - Jump to article ID NUM
# [[#Keyword] - Jump to keywrod search for "Keyword"
# 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>
# Other Style
# ---- - <hr> (In the beginning of line)
# *Word* - <em>Word</em>
# _Word_ - <em>Word</em>
# **Word** - <strong>Word</strong>
# __Word__ - <strong>Word</strong>
# SPC+SPC+$ - <br>
cb='<input type="checkbox" class="s4-checkbox" disabled'
checkboxON="${cb} checked>"
checkboxOFF="${cb}>"
_hrefptn="[-A-Za-z0-9,.:;/~_%#&+?=@!]*"
_hrefptn="[A-Za-z0-9/~%+?=@!.][^][()<> ]*" # URL should start with ASCII
sed -e "s|\[\[\#\([0-9][0-9]*\)\]\]|<a href=\"?aid\1\">#\1</a>|g" \
-e "s|\[\[#\([^]&]*\)\]\]|<a href=\"?kwd=\1\&stage=searchart\">\#\1</a>|g" \
-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>|" \
-e "s|^video=\($_hrefptn\)|<video controls><source height=\"320\" src=\"\1\"></video>|" \
-e "s,^#### *\(.*\),<h4>\1</h4>," \
-e "s,^### *\(.*\),<h3>\1</h3>," \
-e "s,^## *\(.*\),<h2>\1</h2>," \
-e 's,^----*$,<hr>,' \
-e 's, \*\*\([^* |][^*|]*[^ |]\)\*\* ,<strong>\1</strong>,g' \
-e 's, __\([^_ |][^_]*[^ ]\)__ ,<strong>\1</strong>,g' \
-e 's, \*\([^* |][^*|]*[^ |]\)\* ,<em>\1</em>,g' \
-e 's, _\([^_ ][^_]*[^ ]\)_ ,<em>\1</em>,g' \
-e 's, $,<br>,' \
-e "s,- \[ *\]\([^|-]*\),${checkboxOFF}<label>\\1</label>,g" \
-e "s,- \[[^ ]\]\([^|-]*\),${checkboxON}<label>\\1</label>,g" \
}
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:
# . of ^. is workaround for FreeBSD sed
# 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' | miniul
}
miniul() {
sed -e '
/^\* / {; # 行頭 "* "
x; s,^,<ul>,; x; # 1週目: ホールドスペース先頭に <ul> を
:top
s/\n//;
s/^ *//; # 2周目以降: 行頭空白削除
s,\* ,,; # まず行頭の "* " を消しておく
H; # 置き換え結果をホールドスペースに追加
s/.*//; # パターンスペースは消しておく
# ↓最終行なら残ったホールドスペース処理のため :cont へ
$ b cont
N; # 次の行を読む
s/\n//; # 空白始まりは継続行
/^ /b top
x; s/\n/<li>/; s,$,</li>,; # 継続行でなければ <li></li> で囲む
p; s/.*//;
x; # 次も "* " ならループを抜けない
/^\* /b top
s,^,</ul>,; # 次が一般行なら箇条書終わり
}
:cont
x; # 行頭| 以外の行:
/./ {; # ホールドスペースに文字列があれば
s/^\n/<li>/; s,$,</li></ul>,; # 箇条書を書き切って終わり
H; x
}
x'
}
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() {
# 2020/5/14 Add dirty code to cache essential params
if [ x"$session" = x"$main_session" ]; then
case "$1" in
user) _user="$v" ;;
skey) _skey="$v" ;;
esac
fi
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=$1, _user=$_user
val=""
if [ x"$session" = x"$main_session" ]; then
case "$1" in # Dirty cache mechanism for high-load average
user) val=$_user ;;
skey) val=$_skey ;;
esac
fi
val=${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"
}
setskey() {
# For quick response...(?)
query "REPLACE INTO $sesstb VALUES('$1', '$2', datetime('now', 'localtime', '$timeout'));"
}
chkskey() {
# $1=sesskey, $user=LoginUserName
test -z "$1" && return 1
repl=`query "SELECT rowid,user FROM $sesstb WHERE user='$user' AND skey = '$1';"` || return 2
rowid=${repl%%\|*}; repuser=${repl#*\|}
if [ -n "$rowid" -a x"$user" = x"$repuser" ]; then
query "UPDATE $sesstb SET expire=datetime('now', 'localtime', '$timeout') WHERE rowid=$rowid;" # Errors can be ignored
return 0
fi
return 1
}
resetskey() {
if [ -n "$_user" ]; then
query "DELETE FROM $sesstb WHERE user='$_user';"
fi
}
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
###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)"
td=${4:-$tmpd}
[ -d $td ] || mkdir -p $td
### err "select $pk from $1 where rowid=$3" - key=$key '$4(tmp)'=$4
for kt in s m; do
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
}
numericalize() {
echo "${1%%[!0-9]*}"
}
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" -e 's/`/\`/g' -e '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
quotedp=$(sqlquotestr "$p")
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=string 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
query "update $1 set $c=\"$val\" where $pkey=$quotedp;"
else
query "replace into $t values($quotedp, \"$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"}`
query "replace into session values('$1', '$expire');"
# Remove old session parameters
now=`expire now`
query "delete from session where expire < '$now';"
}
gencookie() (
path=${URL#*:/}
path=${URL%/*}
expire="`expire '' '%a, %d-%b-%Y %H:%M:%S GMT'`"
for kv; do
# echo "Set-Cookie: $kv; expires=$expire; Path=$path"
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'`"
case "$k" in
user) _user="$v" ;;
$skey) _skey="$v" ;;
esac
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 \
| tr -d '+=\n'|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 : /|tr -d '\n'`
rcpt=`echo $1|tr ' ' '\n'|sort -u|tr '\n' ' '` # uniq and strip newlines
rcptheader=`echo $1|tr ' ' '\n'|sort -u|sed '2,$s/^/To: /g'`
subj=`echo $2|nkf -jM|tr -d '\n'`
sender=${SENDER:-$admin}
replyto=${REPLYTO:+"Reply-to: $REPLYTO$LF"}
(_m4 -D_RCPT_="${SMAIL_TO:-$rcptheader}" -D_REPLYTO_="$replyto" -D_SUBJ_="\`$subj'" -D_FROM_="$from" $msgdir/mail-header.m4
cat $3 | nkf -jd ) | sendmail -f $sender $rcpt
}
smail_queue_flush() {
# $1=timelimit
timelimit=`query "SELECT datetime('now', 'localtime', '-6 hours');"`
rowids=$(sq $workdb <<-EOF
SELECT rowid FROM smailq
GROUP BY rcpts, subj
HAVING min(time) < '$timelimit';
EOF
)
for rid in $rowids; do
sq $workdb \
"SELECT hex(rcpts),hex(subj) FROM smailq WHERE rowid in ($rid)" \
| if IFS='|' read hexr hexs; then
# err "hexrcpt=[$hexr] hexsubj=[$hexs]"
rcpt=`echo "$hexr"|unhexize`
subj=`echo "$hexs"|unhexize`
# err ROWID==$rowid "sql=<<SELECT hex(rcpts),hex(subj) FROM smailq WHERE rowid=$rowid;>>"
# err "rcpt=[$rcpt] subj=[$subj]"
if sq $workdb <<-EOF | smail "$rcpt" "$subj"
.separator "\n" "------------------\n\n"
SELECT time, text FROM smailq
WHERE rcpts=(SELECT rcpts FROM smailq WHERE rowid=$rid)
AND subj=(SELECT subj FROM smailq WHERE rowid=$rid)
ORDER by time;
EOF
then
echo rowid=$rid
cat <<-EOF | sq $workdb
DELETE FROM smailq
WHERE rcpts=(SELECT rcpts FROM smailq WHERE rowid=$rid)
AND subj=(SELECT subj FROM smailq WHERE rowid=$rid);
EOF
fi
fi
done
}
smail_queue() {
# $1=Rcpts, $@=subj
now=`query "SELECT datetime('now', 'localtime');"`
if [ $? -eq 0 ]; then
rcpts="X'"`echo "$1"|hexize`"'"
subj="X'"`echo "$2"|hexize`"'"
text="X'"`cat | hexize`"'"
err "smail_queue: rcpts=[$1] s=[$2] t:hex=[$text]"
mintime=$(cat <<-EOF | sq $workdb
CREATE TABLE IF NOT EXISTS smailq(rcpts, subj, text, time);
INSERT INTO smailq VALUES($rcpts, $subj, $text, '$now');
SELECT min(time) FROM smailq WHERE rcpts=$rcpts AND subj=$subj;
EOF
)
## XXX: Adhoc load mitigation
case "$now" in
*[01])
err flush_queue=$mintime
smail_queue_flush ;;
esac
fi
}
setviastring() {
table=par
oifs="$IFS"
IFS="&"
for us in $1; do
k=${us%%=*}
v="`echo ${us#*=}|tr '%+' '= '|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"
setpar "$k" "string" "$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 "`collectemail $user`" "New Account"
}
checkauth() {
user=`getpar user`
skc=`getpar skey` # from cookie
[ -z "$user" ] && return 2
##skey="`getpwfield $user skey`"
if [ -n "$skc" ]; then
if chkskey "$skc"; then
return 0
fi
fi
pswd=`getpar pswd`
quser=`sqlquotestr "$user"`
dbuser=`query "SELECT name FROM user WHERE name=$quser;"`
if [ $? != 0 ]; then # Maybe DB locked
return 4 # 4=server too heavy
elif [ -z "$dbuser" ]; then
err "Login USER failed: [$user]"
return 2 # 2=login fail
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 34`
if setskey "$user" "$newsession" &&
dbsetbyid user "$user" login "`date '+%F %T'`"; then
err gencookie "user=$user" "$skey=$newsession"
gencookie "user=$user" "$skey=$newsession"
return 0
else
return 4 # Heavy load??
fi
fi
err "Login failed: [$user]"
return 2 # Password mismatch
}
showlogin() {
args=`echo $myargs|tr ' ' '+'`
test -z "$args" && resetskey
s4name=${S4NAME:-s4}
( sed '/^<body/q' $layout/html.m4.html
cat $layout/login.m4.html
echo '</body></html>'
) | _m4 \
-D_BODYCLASS_="login" \
-D_TITLE_="$s4name" \
-D_SYSNAME_="Welcome to $s4name" \
-D_MYNAME_="$myname${args+?}$args" ${S4CSS:+-D_S4CSS_="$S4CSS"}
exit 0
}
dologin() {
checkauth
st=$?
if [ $st != 0 ]; then
contenttype; echo
_m4 -D_USER_="$user" -D_URL_="$url" -D_ADMIN_="$admin" \
-D _LOADAVG_="`uptime|awk '{print $(NF-2)}'|tr -d ,`" \
$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
cgiinit() {
tmpd=`tmpd=$tmpdir mktempd`
tmpf=$tmpd/stream.$$
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 $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)
mimetype=`file --mime-type - < "$tmpd/$v"|cut -d' ' -f2`
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
err "k=[$k] v=[$v]" # debug@2020-05-31
err "`ls -lF $tmpd/$v` MimeType=$mimetype"
case "$mimetype" in
[Ii]mage/x-xcf)
bzip2 "$tmpd/$v"
v=${v}.bz2
;;
[Ii]mage/x-*|*/vnd.*) ;;
[Ii]mage/[Hh][Ee][Ii][Ff])
if type heif-convert >/dev/null 2>&1; then
vjpg="${v%.heic}.jpg"
err "Conv $v to $vjpg in $tmpd"
convert -quality 75 -resize $maximagexy'>' \
"$tmpd/$v" "$tmpd/$vjpg" >/dev/null 2>&1
v=$vjpg
else
mimetype="Not supported"
fi
;;
[Ii]mage/*)
mogrify -quality 75 -resize $maximagexy'>' "$tmpd/$v"
err "Mogrified: `ls -lF $tmpd/$v`" # 2020-05-31
;;
esac
if ! echo "$mimetype" | egrep "$file_accept_egrep" >/dev/null 2>&1
then
replpar text string " *添付できない形式です($v)* $file_warn"
continue
elif [ `wc -c < "$tmpd/$v"` -gt "$filesize_max" ]; then
replpar text string \
" *添付ファイル($v)は${filesize_max}バイト以下にしてください* $file_warn"
continue
fi
;;
*)
type='string'
;;
esac
#sq $db "replace into par values('$session', '$k', '$type', \"$v\")"
setpar "$k" "$type" "$v"
done
;;
*)
setviastring "$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="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.name, val email
FROM user
LEFT JOIN user_m
ON user.name=user_m.name AND user_m.key='email')
SELECT DISTINCT coalesce(g.email, u.email, u.name)
FROM useremails u LEFT JOIN grpemails g
ON u.name=g.user
WHERE u.name in ($users);"
query "$sql"
}
email4groupbyuid() {
# Get for-$1=group email address(es) for $2...=user-ids
g=$1; shift
uids=`echo "$@"`
uids=`echo $uids|tr ' ' ','`
sql="SELECT DISTINCT name FROM user WHERE rowid IN ($uids);"
email4group "$g" `query "$sql"`
}
myemail4group() {
# Get my email address for $1-specified group
email4group "$1" "$user" | sed -e 1q -e 's/[ ,].*//'
}
collectmembersbyid() {
# Collect user names of group specified by grid
rid=${1%%[!0-9]*} # Cleaning
query "SELECT user FROM grp_mem \
WHERE gname=(SELECT gname FROM grp WHERE rowid=$rid);"
}
collectmembersbyid() {
# Collect user names of group name
qgrp=`sqlquote "$1"`
query "SELECT user FROM grp_mem WHERE gname=$qgrp;"
}
collectgecosesbyid() {
# Collect user gecoses of group
rid=${1%%[!0-9]*} # Cleaning
query<<-EOF
SELECT gecos
FROM gecoses
WHERE name IN (SELECT user FROM grp_mem
WHERE gname=(SELECT gname FROM grp WHERE rowid=$rid));
EOF
}
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 "DELETE FROM par WHERE var='invite' AND val='$1';"
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 "SNSへの御招待"
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 | tr '[A-Z]' '[a-z]'`
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)宛に案内を送信しました。"
else # Cannot be reached here
repo="自動登録できない状況です。管理者に依頼してください。"
fi ;;
"") repo="招待したい人のメイルアドレスを入力してください。" ;;
*) repo="無効なアドレスです" ;;
esac
addr=`query "select val from par where sessid like 'invite-%-$user';"`
if [ -n "$addr" ]; then
susp="<h2>招待済みで加入待ちのアドレス</h2><pre>$addr</pre>"
fi
if [ -f $invite_policy ]; then
pol="spaste(\`$invite_policy')"
else
pol="$invite_policy"
fi
_m4 -D_TITLE_="招待" -D_REPORT_="\`$repo'" -D_ACTION_="?invite" \
-D_BODYCLASS_="default" -D_SUSPENDED_="$susp" \
-D_INVITE_POLICY_="$pol" \
$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 | tr '[A-Z]' '[a-z]'` # Ensure lower case
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"`
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
}
groupclone() {
# $1=grp-rowid of clone-base group
rid=${1%%[!0-9]*} # Cleaning
case "$1" in
*/noteam)
noteam="AND key != 'team'" ;;
esac
qgrp=`query "SELECT quote(gname) FROM grp WHERE rowid=$rid;"`
if [ -z "$qgrp" ]; then
echo "無効なグループIDです($1)" | html p
return
fi
if ! isgrpownerbygid "$user" "$rid"; then
echo "グループ管理者のみがクローン可能です" | html p
return
fi
i=0
while true; do
copy="-copy$i"
newqname=`query "SELECT quote($qgrp || '$copy');"`
# err Trying new grp=$newqname with copy=$copy
test=`query "SELECT gname FROM grp WHERE gname=$newqname;"`
if [ -n "$test" ]; then
i=$((i++))
continue
fi
break
done
# Creating New group "$newqname" with members of old group
# err Creating new grp=$newqname with copy=$copy
query<<-EOF
BEGIN;
INSERT INTO grp VALUES($newqname); -- Create NEW one
REPLACE INTO grp_s(gname, key, val) -- Copy tag
SELECT $newqname, key, val
FROM grp_s WHERE gname=$qgrp AND key IN ('tag', 'mode');
REPLACE INTO grp_s(gname, key, type, val) -- Copy gecos with "copy$n"
SELECT $newqname, key, type, val || '$copy'
FROM grp_s WHERE gname=$qgrp AND key='gecos';
-- Copy members and their configuration --
REPLACE INTO grp_mem SELECT $newqname, user
FROM grp_mem WHERE gname=$qgrp;
REPLACE INTO grp_mem_s SELECT $newqname, user, key, type, val, bin
FROM grp_mem_s WHERE gname=$qgrp;
REPLACE INTO grp_mem_m SELECT $newqname, user, key, type, val, bin
FROM grp_mem_m WHERE gname=$qgrp $noteam;
-- Copy administrators --
REPLACE INTO grp_adm SELECT $newqname, user
FROM grp_adm WHERE gname=$qgrp;
COMMIT;
EOF
newrowid=`query "SELECT rowid FROM grp WHERE gname=$newqname;"`
STOPCLONEMSG=1 groupconf "$newrowid"
}
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_]*}
### If user is not admin, lead to group home
grp=`getgroupbyid $rowid`
if ! isgrpowner "$user" "$grp"; then
echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"
return
fi
# GF_ACTION="?grp+$1" edittable "$formdir/grp.def" "grp" "$rowid" #2015-0804
GF_STAGE="groupupdate" edittable "$formdir/grp.def" "grp" "$rowid"
if [ -z "$STOPCLONEMSG" ]; then
html div 'class="fold"' <<-EOF
`cgi_checkbox clone yes id="clone"`<label
for="clone">同一メンバーで別グループを作成する</label>
<div>
<p>構成メンバーが同じ新規グループを作成します。</p>
<table>
<tr><td><a href="?groupclone+$rowid">
<button>クローン作成(チームも複製)</button></a></td>
<td><p>(チームなどもそのままで掲示板なしの状態から)</p></td></tr>
<tr><td><a href="?groupclone+$rowid/noteam">
<button>作成(チームなし)</button></a></td>
<td>(チームは引き継がずメンバーのみ同じグループを作成)</td></tr>
</table>
<p>ボタンを押すと即作成します。不要な場合はグループ編集で
削除してください。</p>
</div>
EOF
fi
}
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"`
## . ./s4-blog.sh
jg=`getpar joingrp`
if [ -n "$jg" ]; then
[ -n "$jg" -a -n "$grp" ] &&
joingrp "$grp" "$user" "$jg" "`getpar email`"
fi
htmlheader=$layout/html.m4.html
showgroup "$grid"
}
sql4interestblogs() {
cat<<EOF
CREATE TEMPORARY VIEW interestblogs 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 user FROM grp_mem
WHERE gname=bs.owner AND user='$user') IS NULL
THEN 0
ELSE 1
END;
EOF
}
listnewblogsql() { # $1=user
newdays=${WHATS_NEW_DAYS##*[!0-9]} # Shave non-digits
newdays=${newdays%%[!0-9]*}
newdays=${newdays:-14}
basetime="datetime('now', 'localtime', '-${newdays} days')"
deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog
WHERE user='$user'
AND tblrowid IN
($blogreadflagrowid,
$blogcutoffflagrowid)),
$basetime -- "0"
);"`
cat<<EOF
`sql4interestblogs`
WITH article_ctime as (
SELECT id,blogid,author,max(val) ctime
FROM article join article_s s using(id)
WHERE s.key='ctime' AND s.val > '$deftime'
GROUP BY id
), 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 interestblogs blg, blog_s using(id) group by id
), blogall as (
SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid
), news as (
SELECT brid, bl.id blid, bl.title, ctime,
coalesce(al.time, '$deftime') atime,
count(bl.id) "新着", bl.author
FROM blogall bl
LEFT JOIN
(SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al
ON bl.brid=al.tblrowid
WHERE atime < bl.ctime
GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id
LIMIT 10
) SELECT brid LINK, "新着",
(SELECT count(*) FROM article WHERE blogid=blid) "総数",
ctime, title,
(SELECT gecos FROM gecoses WHERE name=author) gecos
FROM news;
EOF
}
search_form() {
# $1 = { author=<AUTHOR> | grp=<GROUP> }
# $2(optional) = pre-input keywords
help="(1)空白区切りの単語で本文検索
(2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索
@2016-0[1-6] → 2016年1月から6月
@>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間
@week → 最近一週間
(3)#番号 で記事ID検索
(1)と(2)は組み合わせOK
例: @2016-10-0[1-9] 芋煮
→ 2016年10月上旬でキーワード「芋煮」を含む記事検索
※クイズ板は検索対象から外されます。"
auth=""
placeholder="全記事からの検索"
case "$1" in
author=*)
a=`echo "${1#author=}"|htmlescape`
g=`gecos ${1#author=}`
auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"
placeholder="このユーザの書込検索"
help="★★ $g さんの書き込みから検索します$nl$help"
;;
grp=*)
a=`echo "${1#grp=}"|htmlescape`
g=`gecos ${1#grp=}`
auth="<input type=\"hidden\" name=\"owner\" value=\"$a\">"
placeholder="このグループからの検索"
;;
esac
inikwd="$2" # no need to htmlescape
cat<<-EOF
<div class="right">
<form action="$myname">$auth
<input type="text" name="kwd" value="$inikwd" title="$help"
placeholder=" $placeholder " width="10" accesskey="k">
<!-- POST SENTENCE -->
${touchpanel:+<p class="help">$help</p>}
<input type="hidden" name="stage" value="searchart">
<!-- EOF -->
</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 DESC LIMIT 1"
[ -d "$dir" ] || mkdir -p "$dir"
tmpf=$tmpd/imgsrc_cache.$$
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:}
if [ x"$filename" = x"${filename%.*}" ]; then
# If nor filename extension found, set it to image type
case "$filetype" in
image/*) filename=$filename.${filetype#image/} ;;
esac
fi
cacheimg_S=$dir/S_$filename
cacheimg_M=$dir/M_$filename
cacheimg_O=$dir/$filename
cacheimg=$dir/${size}_$filename
sumfile="$dir/$filename.sum"
sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1
if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \
&& test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then
# if cache is fresh and has the same checksum,
echo "<img src=\"$cacheimg\">"
else
fifo=`mktemp "$tmpf.fifo.XXXXXXX"`
rm -f $fifo # Safe, because $tmpf is in mktemp dir.
fifo2=$fifo.2
mkfifo $fifo $fifo2
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} \
-resize ${iconxy_M}'>' - ${fmt}:- \
| tee $fifo2 \
| convert - "$cacheimg_M" &
cat $fifo | convert -define ${fmt}:size=${iconxy_S} \
-resize ${iconxy_S}'>' - ${fmt}:- \
| convert - "$cacheimg_S" &
printf '%s' "<img src=\"data:${filetype},"
hexize "$fifo2" |sed 's/\(..\)/%\1/g' # Use medium as pre-cached image
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
)
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')"
search_form_args=""
if [ x"$user" = x"$uname" ]; then
usermenu="<a href=\"?userconf\" accesskey=\"e\"
title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> /
<a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New 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
usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"
title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"
fi
else
latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \
GROUP BY user;"`
usermenu="<p>Last seen on $latestlog</p>"
search_form_args="author=$uname"
fi
. ./s4-blog.sh
tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$
search_form "$search_form_args" > $sf
printf "%s さん" "$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';"`
## REMOVE This comment block until 2019/7/1
## err "----- `gdate +%FT%T.%3N` ------------C"
## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1
listblog $uname > $bf
## unset ddd
## err "----- `gdate +%FT%T.%3N` ------------D"
hometail=$tmpd/tail.$$
mkfifo $hometail
#Calling listgroupbytable, originally here
(
# 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='$uname' 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" accesskey="d"'`<label
for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>
<div class="lcto">
`DT_VIEW=replyblog dumptable html blog`
</div>
EOF
unset DT_SQL
if [ x"$user" = x"$uname" ]; then
# Display NEWS
# 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"`
# echo "$sql" > tmp/listnew
new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`
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" accesskey="f"'
echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${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>"
else # If news is 0, set log cut off flag
acclog blog $blogcutoffflagrowid # for speed
fi
else # Not My Home ($user != $uname)
: # DT_SQL=
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')$usermenu" \
-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]*}
if [ -z "$offset" ]; then
offset=`getpar start`; offset=${offset%%[!0-9]*}
offset=$((offset-1))
fi
offset=$((offset + 0)) # change to numeric forcibly
[ $offset -lt 0 ] && offset=0
limit=$listentlimit
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
kwd=`echo $2 | tr -d '";\n' | tr -d "'"`
cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"
fi
tag=`getpar tag` tag2=`getpar tag2`
if [ x"$tag" = x"NULL" ]; then
tag="" tag2=""
fi
if [ -n "$tag$tag2" ]; then
tag=${tag:-$tag2}
qtag=`sqlquote "$tag"`
cond2="tag=$qtag"
fi
if [ -n "$cond1$cond2" ]; then
cond="$cond1${cond2:+ AND $cond2}"
cond="WHERE ${cond# AND }"
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,
quote(a.$nm) as qname,
(SELECT val FROM ${tbl}_s
WHERE $nm=a.$nm AND key='$iconcachekey') icon,
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,
max(case key when 'wtime' then val end) as wtime,
max(case key when 'login' then val end) as login
from ${tbl}_s group by $nm)
b on a.$nm=b.name $cond $3
order by b.wtime desc, b.login desc,
b.mtime desc, b.tag 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
echo '<div class="listentry">' # List-entry div
# Show owner/member filter button
METHOD=GET
hiddens="$hiddens
`cgi_hidden kwd \"$kwd\"`
`cgi_hidden stage \"$stage\"`"
if [ x"$tbl" = x"grp" ]; then
args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`
fh="<select name=\"tag\">$nl"
fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"
for l in $args; do
val=${l#*=} tname=${l%=*}
if [ x"$val" = x"$tag" ]; then
s=" selected"
selectedtags="(種別[${tname}]のみ)"
else
s=""
fi
form=$nl$form"<option value=\"$val\"$s>$tname</option>"
done
form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"
cat<<-EOF
<form action="$myname" method="$METHOD">
</form>
以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label
for="ismembtn">参加中以外隠す</label>
`cgi_checkbox onlyadm no 'id="isadmbtn"'`<label
for="isadmbtn">管理者参加以外隠す</label>
EOF
# limit=3
hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`
fi
if [ $total -gt $limit ]; then
echo '<div>'
METHOD=GET cgi_form $stage <<EOF
$form
<label>次の語を含む${entity}で検索:
`cgi_text kwd "$kwd"`</label>
$hiddens
EOF
echo '</div>'
else
echo $selectedtags | html p
fi
cat<<EOF
<form action="$myname" method="$METHOD">
<p>${total}件中の<input class="hidesub" type="text" name="start"
value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens
<input type="submit" value="確定"></p>
</form>
EOF
if [ $((offset+limit)) -lt $total ]; then
nextbtn=$(
cat<<EOF
<div class="right clear"><form action="$myname" method="$METHOD">
`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="$METHOD">
`cgi_submit 前の${limit}件`
$hiddens
`cgi_hidden offset $((offset - limit))`</form>
EOF
)
fi
pnbtn="$nextbtn$prevbtn"
echo $pnbtn
## err ListEntry: `echo "$sql"\;`
# sq $db here??? 2016-11-28
query "$sql limit $limit ${offset:+offset $offset};" \
| while IFS='|' read id lnk name qname icon gecos tag ownerp type; do
# err name=$name owner=$ownerp lnk=$lnk
# err newlnk=$lnk regmode=$regmode
icondir=$dir/$id
# Pick up only last icon
echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">
<p class=\"tag _$tag\">$tag</p>" \
| _m4 $tagconv
if [ -n "$NOSPEEDUP" ]; then
files=`getvalbyid $tbl profimg $id $icondir`
if [ -n "$files" ]; then
icon=`echo "$files"|tail -1`
iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"
else
iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"
fi
elif [ -n "$icon" -a -s "$icon" ]; then
iconhref2 "$icon" "$hrb+$lnk" "$gecos"
else
cond="$nm=$qname"
# err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S
# err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"
img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`
# err "img=[$img]"
if [ -n "$img" ]; then
echo "<a href=\"$hrb+$lnk\">"
imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S
echo "</a>"
else
iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"
fi
fi
echo "<br>$name${ownerp:+<br>($ownerp)}"
echo "</div>"
done
echo "</div>" # End of List-entry div
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 ORDER by val;"
}
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"`
htmlgrp=`echo "$grp"|htmlescape`
## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"
if isgroup "$grp"; then
tf=$tmpd/title.$$
sf=$tmpd/search.$$
bodyclass=`query "SELECT val FROM grp_s
WHERE gname=$qgrp AND key='regmode';"`
if ismember "$user" "$grp"; then
ismember="ismember"
bodyclass="$bodyclass${bodyclass:+ }ismember"
else
ismember="" # bodyclass="group"
fi
bodyclass="$bodyclass grouphome"
echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf
echo "グループ $htmlgrp" > $tf
showgroupsub $formdir/grp.def "$grid" | \
_m4 -D_TITLE_="syscmd(\`cat $tf')" \
-D_FORM_="syscmd(\`cat')" \
-D_BODYCLASS_="$bodyclass" \
-D_DUMPTABLE_="" \
$htmlheader $sf $layout/form+dump.m4.html
# $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\" accesskey=\"e\"
title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"
iamowner=$rowid
colmd=" mode"
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
${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"
href="?blog+$rowid">グループの新規話題作成</a>
/ <a accesskey="m" title="Shortcut: m${nl}Operations on Members"
href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>
<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
# 加入ボタン + 加入者リスト
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>'
thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"
cat<<-EOF
<form class="summary" action="$myname" title="$thelp">
`cgi_hidden owner "$grp"`
`cgi_hidden kwd "@week"`
`cgi_hidden stage searchart`
`cgi_submit "一週間のまとめ"`
</form>
EOF
cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"
colstate="state:稼動状態:frozen=rowclass=凍結"
DT_CHLD=article:blogid \
DT_QOWNER="$qgrp" \
DT_VIEW=replyblog dumptable html blog \
"ctime title heading team notify:通知$colmd $colstate" "$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 "`getpar kwd`" "$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"`
myuid=`query "SELECT rowid FROM user WHERE name='$user';"`
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|tr -d '\r'`
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`
safegc=`echo "$gecos" | tr -d '<>@'`
#fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`
fromad=`myemail4group "$grp"`
###mail_from="$safegc <$fromad>"
mail_from="$safegc <$user>" # TEST: 2020/5/13
test -n `getpar sender` &&
export SENDER=$user # TEST: 2020/5/15
replyto=$fromad
## Start parse of attachment files
if [ -n "`getpar email`" ]; then
ar=`getpar supprcpt`
if [ -n "$ar" ]; then
for a in $ar; do
if checkdomain "$a"; then
supprcpt="$supprcpt $a"
else
err "SupprcptErr=[$a] by $user"
removercpt="$removercpt $a"
fi
done
fi
subj=`getpar subject`
afiles=""
for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`
do
f=$tmpd/$fn
if [ -s $f ]; then
afiles=$afiles"${afiles:+ }$f"
fi
done
else
preface=$(cat <<-EOF
$url
のグループ「$grp」のメンバーである $gecos さんから、
あなた宛のメッセージです。
----------------------------------------------------------
EOF
)
fi
rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"
rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`
subj="${subj:-$gecos さんからのメッセージ}"
REPLYTO=$replyto
MAIL_FROM=$mail_from
export REPLYTO SMAIL_TO MAIL_FROM
err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"
for r in $rcpts; do
if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then
SMAIL_TO=$rcpts # Show all rcpts to sender oneself
else
# Show sender and rcpts address for guest
SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`
fi
if [ -n "$afiles" ];then
./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles
else
smail "$r" "$subj"
fi <<EOF
${preface:+$preface$nl}$text
EOF
done
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"`
${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)
${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}
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 ,` # ..and strip spaces of both ends
if [ x"$team" != x"$newteam" ]; then
echo "チーム名に使えない文字を除去しました" | html p
team=$newteam
fi
if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; 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
# POST count summary
from=`getpar from`; to=`getpar to`
from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"
to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"
fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p><!-- $from - $to -->"
# New entry
sql="WITH mems AS (
SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g
ON gm.user=g.name
WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)
), target_article AS (
SELECT id FROM article_s
WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'
), posts AS (
SELECT author, count(author) post
FROM article NATURAL JOIN article_s NATURAL JOIN target_article
WHERE blogid IN (SELECT id FROM blog_s
WHERE key='owner'
AND val=(SELECT gname FROM grp WHERE rowid=$grid))
AND key='text'
GROUP BY author
), teams AS (
SELECT user, group_concat(val, ', ') team
FROM grp_mem_m
WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)
AND key='team'
GROUP BY user
), user_post AS (
SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST
FROM mems m LEFT JOIN posts
ON m.name=posts.author
GROUP by m.rowid
)
SELECT
CASE
WHEN (SELECT user FROM grp_adm
WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)
AND user=up.name) IS NOT NULL
then 'k'
ELSE ''
END || rowid || ',' || gecos NAME,
post POST, team _TEAM_
FROM user_post up LEFT JOIN teams t
ON up.name=t.user
ORDER BY gecos;"
## err grpaction: "`echo \"$sql\"`"
tf=$tmpd/title.$$
echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf
cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"
title=\"Shortcut: f${nl}Add to Administrator of the Group\"
for=\"cmadmin\">管理者委任</label>
<div><p>このグループでの全権を付与します。信頼できる人に託してください。
</p></div>"
excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"
title=\"Shortcut: g${nl}Remove from the Group\"
for=\"conf\">GRP登録解除</label>
<div>本当に消します! `cgi_checkbox confirm yes` 確認
<p>この操作による通知は本人に行きません。
あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>
</div>"
# Get team list to which current user belongs into $hexteams
allhexteams=$(hexteams "$grp")
if [ -n "$isowner" ]; then
myhexteams="$allhexteams" # admin can remove all teams' attr
else
myhexteams=$(hexteams "$grp" "$user")
fi
if [ -n "$myhexteams" ]; then
rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"
title=\"Shortcut: s${nl}Strip a team tag from\"
for=\"cmrmteam\">チーム属性除去</label>
<div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`
を除去します: `cgi_checkbox teamconfirm yes` 確認
<p>この操作による通知は本人に行きません。
あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>
</div><!-- end of $rmteammsg -->
"
fi
stf=$tmpd/selteam.$$
cgi_select_h selteam "5445414d" $allhexteams > $stf
b1='<label> <input type="checkbox" name="usel" value="'
ba='<label class="admin"><input type="checkbox" name="usel" value="'
br='<span id="reverse" title="Reverse Selection"></span>'
#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|" \
-e "s|^\(<TR><TH>\)\(NAME\)|\1$br \2|" \
| _m4 -D_TITLE_="spaste(\`$tf')" \
-D_SUBTITLE_="チェック後操作ボタン" \
-D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \
$layout/form+dump.m4.html \
| _m4 -D_TEAM_="spaste(\`$stf')"
<p>下でチェックした人を対象として:</p>
<div class="foldtabs">
`cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"
title="Shortcut: a${nl}Add a team tag to"
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 accesskey="d"
title="Shortcut: d${nl}DirectMail to"
for="sendmsg" title="Direct Message">DM送信</label>
<div>
`cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"
title="Using email format">email書式を使う</label>
<div class="folded">
<table>
<tr><td>From: </td><td>$user</td></tr>
<tr><td>このFrom:で送る</td>
<td>`cgi_checkbox sender yes 'checked'`<small></small>
</td></tr>
<tr><td>Subject: </td><td>`cgi_text subject`</td></tr>
<tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>
<tr><td>ファイル添付: </td>
<td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>
</table>
<p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>
</div>
<div>本文:`cgi_textarea text "" cols=72`
</div>
</div>
${isowner:+$cmmsg$excmsg}
`cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>
</div>
<h4>$grp 参加者一覧</h4>$fromtonote
<table class="td2r thl">
`sq $db -header -html "$sql"`
</table>
`cgi_hidden grp $grid`
`cgi_hidden myuid $myuid id="myuid"`
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,
max(CASE key WHEN 'owner' THEN val END) owner,
max(CASE key WHEN 'mode' THEN val END) mode
FROM blog_s GROUP by id) 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
WHEN mode IN ('quiz', 'enquete')
THEN 0 -- "quiz" mode blog cannot be searched
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`
rowid=${rowid%%[!0-9]*} # Cleaning
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`
myuid=`query "SELECT rowid FROM user WHERE name='$user';"`
mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`
mailfrom="`gecos "$user"` <$mailfrom>"
sj="グループ $grp 宛メッセージ(from `gecos $user`)"
msg=$(cat<<-EOF
$urlbase?grp+$rowid
グループ $grp に所属する
`gecos $user` さんよりメッセージ:
$message
EOF
)
# smail rcpt subj (file)
for m in $members; do
echo "$msg" |
MAIL_FROM=$mailfrom \
SENDER=$noreply \
REPLYTO=$mailfrom \
SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \
smail "$m" "$sj"
done
cat<<EOF
<p>以下のユーザに送信しました。</p>
<pre>
`collectgecosesbyid "$rowid" | sed 's/$/ さん/'`
</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 adduser`
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', 'adduser', '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"`
grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`
cond="where gname=$qgname and user='$2'"
if [ x"$3" = x"yes" ]; then
query "replace into grp_mem values($qgname, '$2');"
# Notify joingrp to admin
action="に加入しました。"
if [ -n "$4" ]; then
if msg=`emaildomaincheck "$4"`; then
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 "begin;
delete from grp_mem $cond;
delete from grp_mem_s $cond;
delete from grp_mem_m $cond;
delete from grp_adm $cond;
delete from grp_adm_s $cond;
delete from grp_adm_m $cond;
end;"
action="から脱退しました。"
fi
smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF
$url?grp+$grid
$user (`gecos $user`)さんが
グループ $1
$action
EOF
}
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
}
dt_rowhack() {
# From: <TR>
# ....
# <TD>rowclass=foo</TD>
# </TR>
# To: <TR class="foo">....<TD>foo</TD></TR>
sed -e '
/^<TR>/ {
:loop
s/\n//
N
/<\/TR>/ {
s/\n//
s,^<TR>\(.*\)<TD>rowclass=\(.*\)\(</TD></TR>\),<TR class="\2">\1<TD>\2\3,
n
}
$q
b loop
}'
}
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\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"
fi
sqlfile=$tmpd/dump.sql
: > $sqlfile # ensure to be empty
printf '.mode html\n.header 1\n' > $sqlfile
# $DT_CHLD=ChildTable:BindColumn
if [ -n "$DT_CHLD" ]; then
_t=${DT_CHLD%:*} _i=${DT_CHLD#*:}
cat<<-EOF >> $sqlfile
-- presql
CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS
SELECT * FROM acclog WHERE user='$user' and tbl='$2';
EOF
# Speed up counting of new articles
cat<<-EOF >> $sqlfile
-- presql2
DROP TABLE IF EXISTS _counts;
CREATE TEMPORARY TABLE _counts AS
SELECT $_i, count($_i) cnt
FROM $_t GROUP BY $_i;
/* Prepare NEW count table */
CREATE TEMPORARY TABLE _target AS
SELECT b.rowid trowid, b.id
FROM "$2" b JOIN "$2_s" s
ON b.id=s.id AND s.key='owner'
${DT_QOWNER:+ AND s.val=$DT_QOWNER};
DROP TABLE IF EXISTS _children;
CREATE TEMPORARY TABLE _children AS
SELECT a.trowid trowid, $_i, a.id, s.val ctime
FROM (SELECT t.trowid, t.id $_i, a.id
FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a
LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';
-- ${ddd:+.system gdate +%T.%3N >> tmp/d1}
DROP TABLE IF EXISTS _news;
DROP VIEW IF EXISTS _news;
-- CREATE TEMPORARY TABLE _news($_i, newcnt);
-- INSERT INTO _news
/* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */
CREATE TEMPORARY VIEW _news AS
SELECT a.id $_i, coalesce(newcnt, 0) newcnt
FROM (SELECT DISTINCT id FROM _target)
a LEFT JOIN
(SELECT $_i, count(ctime) newcnt
FROM _children x
WHERE ctime > coalesce((SELECT time from myacclog
WHERE tblrowid=x.trowid),
'1970-01-01')
GROUP BY $_i) b
ON a.id=b.$_i;
-- ${ddd:+.system gdate +%T.%3N >> tmp/d1}
EOF
# REMOVE next line until 2019/5/1
cntall="(select count($_i) from $_t where $_i=a.id)"
cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"
# REMOVE next assignment until 2019/5/1
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'))"
cntnew="(SELECT newcnt FROM _news where $_i=a.id)"
cnt="$cntnew as '新着', $cntall as '総数',"
dt_class=" td2r td3r dumpblogs"
fi
# Construct join expression
eav="" scols=""
pk=`gettblpkey $2`
substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}
substr=${substr:-%s}
for col in ${3:-`gettbl_s_cols $2`}; do
valvar=val
case $col in
gecos) scols="$scols${scols:+, }${col#}"
continue ;; # built-in column name
*:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中
col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage
case "$as" in
*:*=*) cnd=${as#*:}
h=${cnd%%=*} v=${cnd#*=}
h=`sqlquotestr "$h"`
v=`sqlquotestr "$v"`
valvar="CASE val WHEN $h THEN $v END"
as=${as%%:*} ;;
esac
;;
*) as=${col} ;;
esac
ss=`printf "$substr" "$valvar"`
eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"
scols="$scols${scols:+, }b.$as"
done
#case author when '$user' then a.rowid else '---' end as ID,
if [ -n "$DT_SQL" ]; then
echo "$DT_SQL"
else
cat<<-EOF
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;
EOF
fi >> $sqlfile
## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"
sqlog<<-EOF
*** SQL-file: $sqlfile ***
`cat $sqlfile`
EOF
if [ "$ddd" ]; then # REMOVE this block until 2019/7/1
err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"
cat $sqlfile >> tmp/sql
# query ".read $sqlfile" > $tmpd/foo
sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo
cp $tmpd/foo tmp/
err "----- `gdate +%FT%T.%3N` ------------555555"
## $ddd LINE exists at the end of this function
fi
printf '.mode list\n.header 0\n' >> $sqlfile
cat<<EOF | sed "s,\(<TR><TD>\)\([1-9][0-9]*\)\(#[0-9a-fxs]*\)*</TD>,\1$elink$dvlink</TD>," | dt_rowhack
<div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->
<div class="dumptable">
<table class="b$dt_class">
`query ".read $sqlfile"`
</table>
</div> <!-- dumptable -->
</div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->
EOF
### `query ".read $sqlfile"`
### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`
## REMOVE THIS!
[ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"
}
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
# 9: Duplicated post
rowid=`getpar 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
elif [ x"$tbl" = x"blog" ]; then
# Check if owner in blog_s
blogowner=`getvalbyid blog owner "$rowid"`
if isgrpowner "$user" "$blogowner"; then
rowowner=$user
else
rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`
fi
else
# 2016-12-05 There's no owner column in $tbl (need confirmation)
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
-- Here we cannot use BEGIN-COMMIT because groupupdate()
-- should use EXCLUSIVE transaction outside of this.
SAVEPOINT par2table;
$sql
update blog_s set val=$val
where key='owner' and val=$old;
RELEASE SAVEPOINT par2table;
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
# XXX: WORK-AROUND FOR SOME STUPID BROWSER
# Avoid empty repost of article.
if [ x"$tbl" = x"article" ]; then
# If rowid is empty and ID exists in article-table, that is REPOST!
aid=`getpar id`
xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`
if [ -n "$xaid" ]; then
# REPOST of article
html p <<-EOF
書き込み直後のリロードなので上書きを回避します。
最新記事は末尾の「再読み込み」ボタンから見てください。
EOF
err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"
return 9 # STOP Duplicated posting
fi
fi
# 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
transaction=$tmpd/sqlfile.sql; touch $transaction
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`
if [ ! -s "$file" ]; then # Maybe stupid REPOST
err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"
continue
fi
## 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`
if [ -n "$transaction" ]; then
cat<<-EOF >> $transaction
$sql
DELETE FROM $tb2 WHERE type='string' AND val='';
EOF
else
query "$sql
delete from $tb2 where type='string' and val='';"
## err repl:normal done
fi
else
# Binary update line is TOO LONG to pipelining
sqlfile="$tmpd/sqlf.$$"
if [ -n "$transaction" ]; then
echo "$sql" >> $transaction
else
echo "$sql" > $sqlfile
query ".read $sqlfile"
fi
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`
if [ -n "$transaction" ]; then
echo "$sql" >> $transaction
else
query "$sql"
fi
else
sqlfile="$tmpd/query.$$"
## err sqlfile=`ls -lF $sqlfile`
if [ -n "$transaction" ]; then
echo "$sql" >> $transaction
else
echo "$sql" > $sqlfile
query ".read $sqlfile"
fi
fi
## err p2t done
done
done
done
[ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query
-- We cannot use transaction here, because groupupdate may use it.
SAVEPOINT pa2table_insert;
.read $transaction
RELEASE SAVEPOINT pa2table_insert;
EOF
rc=$?
[ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag
## err "Table:$tbl update done "
return $rc
)
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
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'"`
rec=`query "select rowid from $2 where $pk='$rowid';"`
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"
form=`cgi_$type $name "$rawval" "$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
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>"
iconhref2 "$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])
[ -n "$GF_VIEWONLY" ] && continue
form="<input type=\"hidden\" name=\"author\" value=\"$user\">"
prompt="" ;;
[Oo][Ww][Nn][Ee][Rr])
[ -n "$GF_VIEWONLY" ] && continue
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])
[ -n "$GF_VIEWONLY" ] && continue
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 &optional $3=RawFlag
## err \$1=$1 \$2=$2 \$3=$3
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
if [ -z "$3" ]; then
ct="text/html${charset:+; charset=$charset}"
link="?showattc+$1+$2+raw"
nkf -e $bin | htmlescape | nkf --oc="$charset" \
| sed 's,^,<span></span>,' \
| _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \
-D_LINK_="$link" \
-D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt
exit $?
fi
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