s4: f095f639e5bf s4-funcs.sh

s4

view s4-funcs.sh @ 469:f095f639e5bf

Show lastlog in other user's home

author	HIROSE Yuuji <yuuji@gentei.org>
date	Tue, 22 Aug 2017 21:04:16 +0859
parents	c00857f0e9d2
children	1dacdf998892

line source

1 #!/bin/sh

2 # Here's global variable table. Do not use this names.

3 # $HGid$

5 [ -f s4-config.sh ] && . ./s4-config.sh

7 myname=`basename ${SCRIPT_NAME:-$0}`

8 mydir=`dirname ${SCRIPT_FILENAME:-$0}`

9 myargs="$@"

10 PATH=/usr/local/sqlite3/bin:/usr/local/vim7/bin:/usr/iekei/ImageMagick/bin:/usr/local/ImageMagick/bin:$PATH

11 tmpdir=${TMPDIR:-tmp}

12 dbdir=${DBDIR:-db}

13 tmpfiles=""

14 db=${DB:-$dbdir/cgi.sq3}

15 admin=${ADMIN:-hostmaster@example.org}

16 templ=${TEMPL:-templ}

17 layout=${LAYOUT:-$templ/default}

18 formdir=${FORMDIR:-$templ/form}

19 imgdir=${IMGDIR:-img}

20 url=${URL:-"${REQUEST_SCHEME:-http${HTTPS:+s}}://$HTTP_HOST$REQUEST_URI"}

21 urlbase=${url%%\?*}

22 msgdir=$templ/msg

23 timeout="+2 days"

24 memoplimitdays="7"

25 dumpcollen=22

26 #thumbxy=120x120

27 thumbxy=96x96

28 iconxy_S=80x80

29 iconxy_M=400x400

30 maximagexy=1600x1600

31 ### maximagexy=400x400

32 file_accept='accept="image/*,text/*,audio/*,application/vnd.oasis.*,application/pdf,application/x-*"'

33 blogreadflagrowid=0

34 querylog=$tmpdir/query.log

36 tconfs=""

37 imgcached=cache/img.`date +%Y/%m`

38 conftbl=_tblconf

39 nl="

40 "

41 iconcachekey="profimgcache_S"

42 case "$HTTP_USER_AGENT" in

43 *i[Pp]hone*|*[Aa]ndroid*) touchpanel=1 ;;

44 *) touchpanel="" ;;

45 esac

46 . ./s4-cgi.sh

48 : <<EOF

50 !! 検索等でblogテーブル参照時は sql4readableblogs() で定義される

51 !! readableblogs テーブルを使うこと

52 資料配布、グループ管理・ML、ファイル交換、クリッカー、アンケート

53 レポート提出管理

54 ひとつのarticleをheadingにして新規ツリーを作成、あるといいかも。

56 [2016]

57 7/12 根本への反省

58 * cgi自身の $1, $2 での切り替えでなく、CGI変数での受け渡しにすべき。

59 arg1/arg2/arg3 的に $1 に / 区切りでつけた方がよかったかな。

61 [以下2015]

62 8/4 ○グループに承認加入モードを追加

63 ○グループに参加していない場合は grpaction できない

64 Web

65 締切設定

67 8/2 ○s4.cgi生成系 → index.cgi生成

68 ○自分の提出物リスト

70 7/19 ○設置

71 ○一斉送信

72 ○getparfilename の tmpd の扱い

73 ○やっぱりs4にしようかな

74 7/18 ○書込著者からホームへのリンク

75 7/17 ○個人blogに「レポート提出用」がついたときの挙動

76 ○添付ファイル回収

77 ○imgcacheは別ディレクトリにしないと + .htaccess

78 7/15 ○レポート提出モードの表示を付ける

79 管理者権限での削除? → まだいいか

81 7/13 ○前回アクセス基準の新着数は欲しいなあ

82 ○レポート提出はどうしよう

83 → ○blogにモードを追加:

84 ○レポート提出モード

85 添付ファイル (誰が見たかログ)

86 クリッカーは別立てメニューにしないと(管理者がON/OFF)

87 ○添付ファイルの読み出し権(6/22から) ← モードで対処

90 7/9 ○管理者の追加

91 △グループメンバの操作 → 要不要を吟味

92 ○グループ情報編集の行先はそのグループがいい?

93 ○新規グループの作成はどこから入るか

94 △グループホームとユーザホームを揃える

96 7/8 ○グループ一覧をユーザ一覧と揃える。

98 7/6の次 ○グループのconf編集の入口

99 ○グループ検索

100

101 6/22の次 ○ホーム画面、○招待状、親記事追跡、○編集ボタン、削除ボタン、

102

103

104 6/7の次 ○blogを作ってみる || userconfig || _mのまとめ編集(削除)

105 6/7の次の次 ○userconfigの画面だけ作ってみる。

106

107 ○ 5/28の次 edittableに「削除」ボタンを足す

108 ○6/1 par2tableを triplex 対応に

109 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'));

110 →とすると一気に

111

112 ## form.def を考えなおそう:

113 ## userのように必須カラムを決まった位置に付ける?

114 ## 必須カラム、owner(foreign key passwd(name)), update datetime

115 ## ユーザ管理とグループ管理はデフォルトで持たせてしまえ

116

117 ## 縦持ちデータの入力/編集を供給する関数 single + multi

118 ## 持てるテーブル構造はシステム標準5種 + ユーザ定義2種類

119 ## 1. passwd

120 ## 2. grp

121 ## 3. grp_mem

122 ## 4. topic 記事のIDとなる

123 ## 5. topic_cont 特定IDの記事の内容物

124 ## 6. list 繰り返し登場あり

125 ## 7. hash 繰り返し登場なし

126

127 ## ● listの定義:

128 ## create table list(id unique, parentID, type, value);

129 ## ● hashの定義:

130 ## create table hash(parentID, type, value, primary key(parentID, type));

131

132 ## グループ属性: community, friend

133 ## ○ blob使えるのかな。streamで行けるのか? xxdで行けた。ありがたい。

134 ## form-defとtableは1対1対応でいいか

135 ## csv2sq3 で .csv.sq3 の Makefile

136

137 ## 書き込みオブジェクトとは何か?

138 ## topic : id, belongto, title, owner, mode

139 ## type := root | comment

140 ## topic_cont : id, topicid(F), ppath, contenttype, filename, content,

141 ## unique(id, filename)

142 ## type := body(single) | attachment(multi)

143

144 ## group := name(P), tag, gecos, owner(F), mode

145 ## tag := personal | friend | ... any string

146 ## group_member := gname(F), type, name(F), UNIQUE(gname, type, name)

147 ## type := "u" | "g"

148 ## できたー!

149 ## 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';

150 ↓

151 ↓以下に変更

152 with recursive allmem as

153 (select gname,val from grp_m where gname='foo'

154 union all select grp_m.gname,grp_m.val from

155 grp_m,allmem where allmem.val=grp_m.gname)

156 select val from allmem where val in (select name from user);

157

158

159 with recursive allmem as

160 (select gname,val from grp_m where gname='foo'

161 union all select grp_m.gname,grp_m.val from grp_m,allmem

162 where allmem.val=grp_m.gname)

163 select a.*, coalesce(b.val,a.val) from allmem a left join grp_mem_s b

164 on a.gname=b.gname and a.val=b.user and b.key='email'

165 where a.val in (select name from user);

166

167

168 ## triggerもできた。

169 ## 5/22から:グループ作成画面

170 ## 埋め込み画像 data:CONTENT-TYPE;base64,.....

171

172 ## 考え得るノードタイプ

173 ## 日報 - 個人所属かグループ所属か

174 ## 課題提出 - 個人所属かグループ所属か

175 ## グループ管理

176 ## 個人情報管理

177 ##

178

179 ## 例: group:sip - topic:1:sip:Aperture:yuuji:rw

180 ## - topic:2:sip:ISO:yuuji:rw

181 ## topic_cont 1:1:/:body:text...Aperture

182 ## 2:1:/1:body:text..Aperture

183 ## 3:1:/1:attachment:binary..Aperture

184 ## 4:1:/2:body:text..Aperture

185 ## 5:1:/2:attachment:binary..Aperture

186 ## 6:2:/:body:text..ISO

187 ## 7:2:/6:body:text..ISO

188 ## 8:2:/6:attachment:binary..

189

190 ## ログテーブル

191 ## time, who, action, tbl, id idなんか取れるかな

192

193

194

195 ■表設計

196 * 3つの表に分散管理

197 id格納表 + hash表 + list表

198 * *_s *_m

199

200

201

202 user, user_map, user_col

203

204 ■抽象エントリタイプ

205 * user

206 idとして機能 → table中の owner に自動挿入(?)

207 * group

208 権限判定に利用

209 * serial

210 自動idとして機能

211 * password

212 入力 type=passwordで入力

213 変更 oldpasswd, password×2 で確認後修正

214 * session

215 password認証後のセッションキーとして機能

216 * text

217 入力 type=text

218 * textarea

219 入力 textarea

220 * image|document

221 入力 type=fileで入力し、mime-typeを確認

222 * owner

223 入力時の $user で、外部キー制約が付く

224 * gowner

225 グループとしての所有者で、外部キー制約が付く

226 * timestamp

227 datetime()

228 * parent

229 木構造の場合の親の位置

230 * path

231 木構造の場合の自分の位置

232

233 格納タイプ

234 * list

235 表 parentID, key, val でUNIQUE(parentID, key, val)

236 * hash

237 表 parentID, key, val でUNIQUE(parentID, key)

238

239 オブジェクトタイプ

240 * entry

241 id, title, owner

242 * textpart

243 id, parentID, text

244 * binarypart

245 id, parentID, contenttype, filename, content

246 * content

247 hash(textpart), list(binarypart)

248 * topic

249 id, hash(content), list(reply)

250 * reply

251 id, parentID, content

252 * blog

253 list(entry)

254 blog = [topic, list(reply)]

255

256

257 blog = [ {"title" => "hoge", "owner" => "yuuji", "date" => "2015-04-27",

258 "text" => "hogehoge ..",

259 "reply" => [ {"serial" => 1,

260 "author" => "taro",

261 "date" => "2015-04-28",

262 "parent" => "/",

263 "path" => "/1",

264 "text" => "blah, blah, ....",

265 "image" => ["a.jpg", "b.jpg"] },

266 {"serial" => 2,

267 "author" => "hanako",

268 "date" => "2015-04-29",

269 "parent" => "/",

270 "path" => "/2",

271 "text" => "blah, blah, ....",

272 "image" => [] }]},

273 {"title" => "buha", ...} ]

274

275

276 user:=

277 ユーザ名(英数字):name:p:text:length="20" maxlength="40"

278 パスワード:pswd:s:password:length="20" maxlength="40"

279 説明(日本語OK):gecos:s:text:length="20" maxlength="40"

280 セッションキー:skey:s:session

281 メイルアドレス:email:m:text:length="20" maxlength="40"

282 住所:address:m:textarea:maxlength="400"

283 プロフィール画像:profimg:m:image:maxlength="400K"

284 履歴書:profpdf:m:document:maxlength="4M"

285

286 変換表

287 /user/email=m

288

289 blog:=

290 シリアル:id:p:serial

291 タイトル:title:s:text:

292 所有者:owner:s:owner:

293 時刻:ctime:s:stamp:

294 リード文:heading:s:textarea:

295 リプライ:reply:m:*article:

296

297 article:=

298 シリアル:id:p:serial

299 筆者:author:s:owner

300 時刻:ctime:s:stamp:

301 参照元:parent:s:parent:

302 パス:path:s:path:

303 本文:text:s:textarea:

304 画像:image:m:image:

305

306 履歴書:profpdf:m:document:maxlength="4M"

307

308

309 EOF

310

311 sq() {

312 # ./args.rb -cmd ".timeout 3000" "$@"

313 sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000" "$@"

314 }

315 dbsetup() {

316 [ -d $tmpdir ] || mkdir -m 1777 $tmpdir

317 [ -d $dbdir ] || mkdir -m 1775 $dbdir

318 sqi=$tmpdir/sqi.$$

319 sqo=$tmpdir/sqo.$$

320 mkfifo $sqi $sqo

321 #tail -f $sqi | sq $db & # "tail -f" is too heavy. DO NOT USE!!

322 sq $db < $sqi &

323 sq3pid="`jobs -p` $!"

324 exec 2>> $tmpdir/error.out

325 exec 3>> $tmpdir/debug.out

326 exec 5> $sqi # Turning $sqi access through fd5 for continuous open state

327 rm $sqi

328 }

329 cleanup2() { # Dirty workaround for produced zombie processes

330 pkill -9 -u `id -u` -P 1

331 }

332 cleanup() {

333 trap '' INT HUP EXIT TERM PIPE

334 echo .quit >&5

335 kill $sq3pid

336 kill $sq3pid

337 rm -f $sqo $sqi

338 rm -rf $tmpfiles

339 cleanup2

340 }

341 # We want to use piped function to put querylog, but we use

342 # simple redirection for the sake of speed.

343 query() {

344 echo ".once $sqo" >&5

345 echo "`date '+%F %T'`:[${user:-NULL}] <<<" >> $querylog

346 if [ -z "$1" ]; then

347 tee -a $querylog

348 else

349 echo "$@" >> $querylog

350 echo "$@"

351 fi >&5

352 cat $sqo

353 echo '>>>' >> $querylog

354 }

355 _m4() {

356 #_S4NAME_=f,f,f

357 m4 ${_S4NAME_:+"-D_S4NAME_=${_S4NAME_}"} "$@"

358 }

359 ismember() {

360 # $1=user, $2=group

361 err ismem: "select user from grp_mem where gname=$(sqlquote $2) and user='$1';"

362 test -n "`query \"select user from grp_mem where gname=$(sqlquote \"$2\") and user='$1';\"`"

363 }

364 isuser() { # Check if $1 is a valid user

365 test -n "`query \"select name from user where name='$1';\"`"

366 }

367 isgroup() { # Check if $1 is a valid group

368 err isgroup: "select gname from grp where gname=$(sqlquote $1);"

369 test -n "`query \"select gname from grp where gname=$(sqlquote \"$1\");\"`"

370 }

371 isgrpowner() (

372 # $1=user, $2=group

373 gn=`sqlquote "$2"`

374 sql="select user from grp_adm where gname=$gn and user='$1';"

375 err isgrpowner: $sql

376 test -n "`query $sql`"

377 )

378 getgroupadminmails() {

379 # $1=group

380 for i in $(getgroupadmins $1); do

381 email4group "$1" "$i" ;

382 done

383 }

384 getgroupadmins() { # $1=group

385 # This function is called in a backquote, so needn't to be subshellized

386 qgrp=`sqlquote "$1"`

387 query "select user from grp_adm where gname=$qgrp;"

388 }

389 getgroupattr() { # $1=group $2=attr

390 # This function is called in a backquote, so needn't to be subshellized

391 getvalbyid grp $2 \

392 $(query "select rowid from grp where gname=`sqlquote \"$1\"`;")

393 }

394 getgroupbyid() {

395 # $1=id|gname

396 sql="select coalesce((select gname from grp where gname=$(sqlquote \"$1\")),

397 (select gname from grp where rowid=$(sqlquote $1)));"

398 # err ggbyid: `echo $sql`

399 query $sql

400 }

401 isfilereadable() { # $1=user $2=tbl $3=rowid

402 # Return true if user($1) can read attachment files in tbl($2):rowid($3)

403 [ -z "$1" -o -z "$2" -o -z "$3" ] && return 1 # invalid argument

404

405 # Return true when anonymous mode

406 [ "$anonymousmode" ] && return 0

407 # case `getvalbyid blog mode $2` in

408 # normal|*open*|"") return 0 ;;

409 # *closed*)

410 # owner=`getvalbyid blog owner $2`

411 # if isgrp $owner; then

412 # isgrpowner $1 $owner && return 0 || return 1

413 # elif isuser $owner; then

414 # [ x"$1" = x"$owner" ] && return 0 || return 1

415 # fi

416 # esac

417 # ↑ 要はこういう処理を↓で一気にやっている

418 sql="with getblog as (\

419 select key,val from blog_s where id=(\

420 select blogid from article where id in\

421 (select id from $2 where rowid=$3))),\

422 getowner as (select val from getblog where key='owner'),\

423 getmode as (select val from getblog where key='mode')\

424 select case\

425 when (select author from article where\

426 id=(select id from $2 where rowid=$3))='$1' \

427 then 'author'\

428 when (select val from getmode) in ('report-open', 'normal')\

429 then 'open'\

430 when (select val from getmode) is null \

431 then 'open'

432 when (select val from getowner) in (select gname from grp)\

433 then (select user from grp_adm where \

434 gname=(select val from getowner) and \

435 user='$1')\

436 when (select author from article where\

437 id=(select id from $2 where rowid=$3))='$1'

438 then 'user+author'

439 else '' end;"

440 ## err isfilereadable: sql="`echo $sql`"

441 # caseのネストで内側のcaseがスカラーtrueを返しても外側はtrue扱いにならない

442 result=`query "$sql"`

443 [ -n "$result" ] && return 0

444 return 2

445 }

446 linkhome() {

447 # $1=UserOrGroup

448 echo -n '<a href="?'

449 if isuser $1; then

450 err "select 'home+'||rowid from user where name='$1';"

451 query "select 'home+'||rowid from user where name='$1';"

452 else

453 echo -n "grp+$1"

454 fi

455 echo "\">`gecos $1`</a>"

456 }

457 hreflink() {

458 # s4 specific notation:

459 # ^href=URL

460 # ^iframe=URL

461 # OSM umap Wikistyle Notation:

462 # [[URL]] - Simle Link

463 # [[URL|Word]] - Link with anchor word

464 # {{URL}} - <img src="URL">

465 # {{URL|width}} - <img src="URL" width="width">

466 # {{{URL}} } - <iframe src="URL"></iframe>

467 # {{{URL|height}} - <iframe src="URL" height="height"></iframe>

468 _hrefptn="[-A-Za-z0-9,.:;/~_%#&+?=@!]*"

469 sed -e "s|\[\[\#$[0-9][0-9]*$\]\]|<a href=\"?aid\1\">#\1</a>|g" \

470 -e "s|\[\[$$_hrefptn$\|$[^]]*$\]\]|<a href=\"\1\">\2</a>|g" \

471 -e "s|\[\[$$_hrefptn$\]\]|<a href=\"\1\">\1</a>|" \

472 -e "s|{{{$$_hrefptn$\|$.*$}}}|<iframe src=\"\1\" height=\"\2\"></iframe>|g" \

473 -e "s|{{{$$_hrefptn$}}}|<iframe src=\"\1\"></iframe>|g" \

474 -e "s|{{$$_hrefptn$\|$.*$}}|<img src=\"\1\" width=\"\2\">|g" \

475 -e "s|{{$$_hrefptn$}}|<img src=\"\1\">|g"\

476 -e "s|^href=$$_hrefptn$|<a &>\1</a>|" \

477 -e "s|^iframe=$$_hrefptn$|<iframe src=\"\1\"></iframe>|" \

478 -e "s,^#### *$.*$,<h4>\1</h4>," \

479 -e "s,^### *$.*$,<h3>\1</h3>," \

480 -e "s,^## *$.*$,<h2>\1</h2>,"

481 }

482 minitbl() {

483 sed -n '

484 /^|.*|/ {; # If the line begin with "|" and has 2 or more "|"

485 s,|$,,; # Remove trailing "|" first

486 s,|\* *$[^|]*$ *,<th>\1</th>,g; # "|*..." to "<th>...</th>"

487 s,| *$[^|]*$ *,<td>\1</td>,g; # "|..." to "<td>...</td>"

488 s,^,<tr>,; s,$,</tr>,; # Enclose with "<tr>" and "</tr>"

489 H; # Concat this line to HoldSpace

490 s/.*//; # Delete PatternSpace for finalization

491 $ b cont

492 d; # If in final line, output the rest, else jump to next turn

493 }

494 :cont

495 x; # For non-"|" lines, check HoldSpace

496 /^./ {; # If HoldSpace has "|" table elements

497 s|^.|<table class="mini">|; # Enclose whole elements like this:

498 # . of ^. is workaround for FreeBSD sed

499 # s|$|</table>|; # <table class="mini">..\n..</table>

500 p; # Print whole "table" element

501 s/.*//; # Erase all when done.

502 x; s|^|</table>|; x; # Preppend /table to the next line

503 }

504 x; # Back to the newest line

505 p; # Print rest'

506 }

507 acclog() (

508 # $1=table, $2=rowid

509 n=${2%%[!0-9]*} # Remove non-digit chars from $2(should be rowid)

510 if [ -n "$n" ]; then

511 now=`date +"%F %T"`

512 #query "replace into acclog values('$user', '$1', '$n', '$now');"

513 #query "replace into acclog values('$user', '$1', $n, '$now');"

514 query "replace into tblaccesses values('$user', '$1', $n, '$now');"

515 fi

516 )

517 gecos() (

518 u=`sqlquote "${1:-$user}"`

519 query "select gecos from gecoses where name=$u;"

520 )

521 setpar() {

522 query "replace into par values('$session', '$1', '$2', \"$3\");"

523 }

524 replpar() {

525 query "update par set val=\"$3\" where sessid='$session' and var='$1' and type='$2';"

526 }

527 getpar() {

528 val=`query "select val from par where var='$1' and sessid='$session' $2;"`

529 ## err getpar/val1: "val=[$val]"

530 if [ -z "$val" ]; then

531 val=`query "select val from cookie where var='$1' and sessid='$session' $2;"`

532 fi

533 ## err getpar/val2: "val=[$val]"

534 case "$var" in

535 owner)

536 if [ x"$user" = x"$val" ]; then

537 echo $user; return

538 elif ismember $user $val; then

539 echo $val; return

540 fi ;;

541 esac

542 ## err getpar/ret: "val=[$val]"

543 echo "$val"

544 }

545

546 getpartype() {

547 query "select type from par where var='$1' and sessid='$session' $2;"

548 }

549 getparcount() {

550 query "select count(*) from par where var='$1' and sessid='$session' $2;"

551 }

552 getparfilename() {

553 # null if type of $1 is not file

554 (f=`query "select val from par where var='$1' and sessid='$session' and type='file' $2;"`

555 [ -n "$f" ] && echo $f)

556 }

557 sqlquote() {

558 (v="$1"

559 case "$v" in

560 "") return ;; # null

561 "X'"*) # quoted hex string

562 echo $1 ;;

563 *\"*) # string including dbl-quote"

564 v=`echo "$v"|sed -e 's/\"/\"\"/g'`

565 echo "\"$v\""

566 return ;;

567 *.*.*|*-*-*|*[Ee]*[Ee]*|[Ee]*|*[\ -,:-df-~]*) # string

568 echo "\"$v\""

569 return ;;

570 *)

571 if expr "$v" : '[-0-9.Ee][-0-9.Ee]*$' >/dev/null 2>&1; then

572 echo $v # MAYBE numeric, maybe...

573 else

574 echo "\"$v\""

575 fi ;;

576 esac)

577 }

578 sqlquotestr() (

579 case "$1" in

580 *\'*) v=`echo "$1"| sed "s/'/''/g"`

581 echo "'$v'" ;;

582 *) echo "'$1'" ;;

583 esac

584 )

585 mktempd() {

586 TMPDIR=$tmpd mktemp -d -t $session

587 }

588 getcachedir() { # $1=maintable

589 if [ -n "$imgcached" ]; then

590 echo $imgcached/$(echo ${1:-hoge}|md5)/$thumbxy

591 else

592 echo $tmpd/$thumbxy

593 fi

594 }

595 getval() {

596 # $1=table $2=col $3(optional)=condition

597 case `gettbl_coltype "/$1/$2"` in

598 user|author) # author added 2015-06-18 for article(author)

599 echo "$user" ;;

600 stamp|datetime)

601 date "+%F %T" ;;

602 serial)

603 (s=`getpar $2`

604 if [ -n "$s" ]; then echo $s; else echo "`date +%s`x$$"; fi) ;;

605 *)

606 getpar "$2" "$3";;

607 esac

608 }

609

610 getvalquote() {

611 # $1=table $2=col $3(optional)=condition

612 (v=`getval "$@"`

613 case "$v" in

614 "") echo NULL ;;

615 *) sqlquote "$v" ;;

616 esac)

617 }

618 getparquote() {

619 sqlquote "`getpar $1`"

620 }

621 getbinbyid() {

622 # $1=tbl $2=col $3=rowid $4=tmpdirForBinary

623

624 }

625 getvalbyid() {

626 # $1=tbl $2=col $3=rowid $4=tmpdirForBinary

627 # If two or more values found, save them to $tmpd/${column}.$N and

628 # store the number of files into $tmpd/${column}.count and

629 # their each rowid stored into $tmpd/${column}.$N.rowid.

630 ## err gtb-$1=`gettblcols $1`, tbl=$1, col=$2, '$3'=$3

631

632 (for c in `gettblcols $1`; do

633 if [ x"$2" = x"$c" ]; then

634 ###sq $db "select $2 from $1 where rowid=$3"

635 query "select $2 from $1 where rowid=$3;"

636 return

637 fi

638 done

639 rowid=$3

640 pk=`gettblpkey $1`

641 key=`query "select $pk from $1 where rowid=$3;"`

642 getkey="(select $pk from $1 where rowid=$3)"

643 td=${4:-$tmpd}

644 [ -d $td ] || mkdir -p $td

645 ### err "select $pk from $1 where rowid=$3" - key=$key '$4(tmp)'=$4

646 for kt in s m; do

647 t=${1}_$kt

648 for c in `gettbl_${kt}_cols $1`; do

649 vcount=1 # count(val)

650 if [ x"$2" = x"$c" ]; then

651 #### cond="$t where $pk=\"$key\" and key=\"$c\"" #2015-07-22

652 cond="$t where $pk=$getkey and key=\"$c\""

653 val=`query "select val from $cond limit 1;"`

654 type=`query "select type from $cond limit 1;"`

655 if [ $kt = m ]; then

656 ###vcount=`sq $db "select count(val) from $cond"`

657 # Reset val to store filenames if type is string

658 val=`query "select val from $cond and type like 'file:%' order by rowid;"`

659 err gvb1-sql: "select count(val) from $cond;"

660 vcount=`query "select count(val) from $cond;"`

661 echo $vcount > $td/$c.count

662 i=0

663 ## err gvbid: i=$i vcount=$vcount

664 while [ $i -lt $vcount ]; do

665 slice="order by rowid limit 1 offset $i"

666 i=$((i+1))

667 fn=$c.$i

668 err td=$td, fn=$fn, type=$type, val="[$val]"

669 case $type in

670 file:*)

671 #file=$td/$val

672 r_f=`query "select rowid||'//'||val from $cond $slice;"`

673 f_rid=${r_f%%//*}

674 file=$td/${r_f##*//}

675 # FOR SPEED: Skip file generation if imgcache exists

676 [ -s "$file" -a -s "$td/$fn.rowid" -a -s "$file.rowid" ] \

677 && [ x"$f_rid" = x"`cat $td/$fn.rowid`" ] \

678 && continue

679 # err gvbid-get="select quote(bin) from $cond $slice;"

680 ## err output: "fn=[$fn] file=[$file]"

681 sq $db<<EOF | unhexize > "$file"

682 .output '$td/$fn.rowid'

683 select rowid from $cond $slice;

684 .output '$td/$fn'

685 select val from $cond $slice;

686 .output '$td/${fn}.content-type'

687 select substr(type, 6) from $cond $slice;

688 .output stdout

689 select quote(bin) from $cond $slice;

690 EOF

691 ## err gvbid-get2: "`ls -lF $file`"

692 ## err i=$i - file=$file rowid=`cat $td/$fn.rowid`

693 cp "$td/$fn.rowid" "$file.rowid" 2>&3 # for convenience

694 cp "$file" "$file.orig" 2>&3

695 ls -lh "$file" |

696 awk '{print $5"B"}'|sed 's/BB/B/' > "$file.size"

697 case "$type" in

698 *:[Ii]mage*) mogrify -geometry $thumbxy "$file" ;;

699 ### ここのアイコンを増やしたい

700 *|*:[Aa]pplication*)

701 convert -geometry $thumbxy $imgdir/file-icon.png \

702 png:- > "$file"

703 ;;

704 esac

705 ;;

706 *)

707 sq $db<<EOF

708 .output $td/$fn.rowid

709 select rowid from $cond $slice;

710 .output $td/$fn

711 select val from $cond $slice;

712 EOF

713 val=$val${val:+$nl}"`echo $fn`" # should be delimited by newline

714 ;;

715 esac

716 done

717 else

718 rm -f $td/$c.count

719 case $type in

720 file:*)

721 echo "$val" \

722 | while read fn; do

723 file=$td/$fn

724 if [ ! -s "$file" ]; then

725 ## sq $db "select quote(bin) from $cond and val=\"$fn\"" \

726 query "select quote(bin) from $cond and val=\"$fn\";" \

727 | unhexize > "$file"

728 ##@@## -- echo ${type#file:} > "$file.content-type"

729 case $type in

730 *:[Ii]mage*) mogrify -geometry $thumbxy "$file" ;;

731 *:[Aa]pplication*)

732 convert -geometry $thumbxy $imgdir/file-icon.png \

733 png:- > $file ;;

734 esac

735 fi

736 done

737 ;;

738 esac

739 fi

740 echo "$val" # Keep newlines by ""

741 return

742 fi

743 done

744 done)

745 }

746 getvalbypkey() (

747 # $1=tbl $2=col $3=pkey $4=tmpdirForBinary

748 pk=`gettblpkey $1`

749 rowid=`query "select rowid from $1 where $pk='$3';"`

750 getvalbyid "$1" "$2" $rowid $4

751 )

752 getvalbycond() {

753 # $1=tbl $2=col $3=SQL-Condition

754 ###rowid=`sq $db "select rowid from $1 where $3"`

755 rowid=`query "select rowid from $1 where $3;"`

756 if [ -n "$rowid" ]; then

757 getvalbyid "$1" "$2" $rowid "$4"

758 fi

759 }

760 getpwfield() {

761 # getpwfield user column

762 # val=`sqlite3 $db "select $2 from passwd where name='$1' $3"`

763 val=`getvalbycond user $2 "name='$1'"`

764 if [ -n "$val" ]; then

765 echo "$val"

766 return 0

767 else

768 return 1

769 fi

770 }

771 encode() {

772 if [ -z "$sha1" ]; then

773 if type sha1 >/dev/null 2>&1; then

774 sha1=sha1

775 elif type sha1sum >/dev/null 2>&1; then

776 sha1=sha1sum

777 elif type gsha1sum >/dev/null 2>&1; then

778 sha1=gsha1sum

779 fi

780 fi

781 $sha1 "$@" | cut -d' ' -f1

782 }

783 enjpeg() {

784 if [ -z "$cjpeg" ]; then

785 if type cjpeg >/dev/null 2>&1; then

786 cjpeg="cjpeg"

787 else

788 cjpeg="convert - jpeg:-"

789 fi

790 fi

791 $cjpeg "$@"

792 }

793 mycrypt() (

794 key=$1 salt=$2

795 err \$2=$2

796 case $2 in

797 '$'*'$'*) salt=${salt#\$4\$}

798 salt=${salt%\$*} ;;

799 esac

800 echo -n '$4$'"$salt"'$'

801 echo "$salt$key" | encode || exit 1 # Abort if fail to call encode

802 )

803 hexize() {

804 if [ -z "$hexize" ]; then

805 if type xxd >/dev/null 2>&1; then

806 hexize="xxd -p"

807 else

808 hexize_hd() {

809 hexdump -ve '1/1 "%.2x"'

810 }

811 hexize="hexize_hd"

812 fi

813 fi

814 cat "$@" | $hexize | tr -d '\n'

815 }

816 unhexize() {

817 if [ -z "$unhex" ]; then

818 if type xxd >/dev/null 2>&1; then

819 unhex="xxd -p -r"

820 elif type perl >/dev/null 2>&1; then

821 cat >$tmpd/unhex.pl<<EOF

822 s/([0-9a-f]{2})/print chr hex \$1/gie

823 EOF

824 # Perl refuses -e in setuid circumstances, which can be absurdly

825 # avoided by creating scripts in a file where its parent directory is

826 # world writable...:)

827 unhex="perl -n $tmpd/unhex.pl"

828 fi

829 fi

830 cat "$@" | $unhex

831 # cat $1 | tee /tmp/uh.in| $unhex | tee /tmp/uh.out

832 }

833 percenthex() {

834 hexize "$@" | sed 's/$..$/%\1/g'

835 }

836 htmlescape() {

837 sed -e 's/\&/\&/g' -e 's/"/\"/g' -e "s/'/\'/g" \

838 -e "s/</\</g; s/>/\>/g"

839 }

840 enascii() {

841 if [ -z "$enascii" ]; then

842 if type kakasi >/dev/null 2>&1; then

843 enascii="kakasi -Ha -Ka -Ja -Ea -ka"

844 else

845 enascii_now=`date +%FT%T`

846 enascii_sed() {

847 nkf -Z0Z1Z2 \

848 | sed -e "s/^/$enascii_now/" -e "s|[^-0-9.A-z/,()_=]|x|g"

849 }

850 enascii="enascii_sed"

851 fi

852 fi

853 cat "$@" | $enascii

854 }

855 size_h() {

856 i="$1" oi=$1

857 set -- B B KB MB GB TB

858 while [ $((i)) -gt 9 -a -n "$1" ]; do # -gt 9 means $oi > 1024

859 oi=$i

860 i=$((i/1024))

861 shift

862 done

863 echo ${oi}$1

864 }

865 gettblconf() {

866 if [ -z "$tconfs" ]; then

867 ## tconfs=`sq $db \

868 tconfs=`query \

869 "select tbl||'/'||col||'='||keytype||'/'||objtype from $conftbl;"`

870 fi

871 # /tb1/col1=p/text /tb1/col2=s/text /tb1/col3=m/image /tb2/col1=p/text ...

872 }

873 gettblkeys() {

874 # $1=tbl

875 gettblconf

876 echo "$tconfs" | fgrep "/$1/" | \

877 (type="" keys="" fks="" cols="" scols="" mcols="" hcols=""

878 while IFS='=' read tc conf; do # tc=/tb1/col1 conf=s/text

879 col=${tc##*/} type=${conf%%/*}

880 case $type in

881 *p*)

882 cols=$cols"${cols:+:}$col"

883 keys=$keys"${keys:+:}$col" ;;

884 *f*) cols=$cols"${cols:+:}$col"

885 fks=$fks"${fks:+:}$col" ;;

886 *m*) mcols=$mcols"${mcols:+:}$col" ;;

887 *s*) scols=$scols"${scols:+:}$col" ;;

888 esac

889 case $type in

890 *h*) hcols=$hcols"${hcols:+:}$col" ;;

891 esac

892 done

893 echo "_keys=$keys _fks=$fks _cols=$cols _scols=$scols _mcols=$mcols _hcols=$hcols")

894 }

895 gettblpkey() {

896 # $1=tbl

897 gettblkeys $1 | cut -d ' ' -f 1 | sed -e 's/.*=//' -e 's/:/ /g'

898 }

899 gettblfkey() {

900 (x=`gettblkeys $1`

901 x=${x#*_fks=} # cut before "_fks=" including

902 echo ${x%% *} | tr ':' ' ')

903 }

904 gettblcols() {

905 (x=`gettblkeys $1`

906 x=${x#*_cols=} # cut before "_cols=" including

907 echo ${x%% *} | tr ':' ' ')

908 }

909 gettbl_s_cols() {

910 (x=`gettblkeys $1`

911 x=${x#*_scols=} # cut before "_scols=" including

912 echo ${x%% *} | tr ':' ' ')

913 }

914 gettbl_m_cols() {

915 (x=`gettblkeys $1`

916 x=${x#*_mcols=} # cut before "_mcols=" including

917 echo ${x%% *} | tr ':' ' ')

918 }

919 gettbl_h_cols() {

920 (x=`gettblkeys $1`

921 x=${x#*_hcols=} # cut before "_hcols=" including

922 echo ${x%% *} | tr ':' ' ')

923 }

924 gettbl_coltype() (

925 gettblconf

926 x=`echo "$tconfs"|fgrep $1=`

927 x=${x#*=} # cut before =

928 echo ${x#*/} # cut before p/ including

929 )

930 is_hidden() {

931 # $1=Tbl $2=col

932 gettblconf

933 x=`echo "$tconfs"|fgrep /$1/$2=`

934 x=${x#*=} # cut before =

935 x=${x%%/*} # cut after /

936 case $x in

937 *h*) return 0 ;;

938 *) return 1 ;;

939 esac

940 }

941

942 dbsetbyid() {

943 # $1=tbl $2=id $3=col $4=val/filename - &optional - $5=content-type

944 (t0=$1 t=$1 p=$2 c=$3

945 tsc=$t/$c val=$4

946 quotedp=$(sqlquotestr "$p")

947 unset primary update

948 gettblconf

949 #err tsc=$tsc, tconfs="$tconfs"

950 conf=`echo "$tconfs"|fgrep "$tsc"=`

951 #err conf=$conf

952 case ${conf#*=} in

953 p*) primary=1 ;;

954 f*) update=1 ;;

955 u*) ;;

956 m*) t=${t}_m;;

957 s*) t=${t}_s;;

958 esac

959 #err t=$t

960 type=string fn=""

961 case $conf in

962 */password)

963 type=encoded ### val=`echo $val|encode`

964 ;;

965 */image*|*/document*)

966 type=`file --mime-type - < "$val" | cut -d' ' -f2`

967 bin="X'`hexize "$val"`'"

968 ;;

969 esac

970 pkey=`echo "$tconfs"|grep "${t0}/.*=p"|sed 1q`

971 pkey=${pkey#/*/} # cut $tbl/

972 pkey=${pkey%=p/*} # cut =p/... -> primary key

973 if [ "$primary" ]; then

974 nulls=`echo "$tconfs"|grep "$t/.*=[fu]/"|sed 's/^.*/, NULL/'|tr -d '\n'`

975 ###sq $db "replace into $t values(\"$val\"$nulls)"

976 query "replace into $t values(\"$val\"$nulls);"

977 elif [ "$update" ]; then

978 query "update $1 set $c=\"$val\" where $pkey=$quotedp;"

979 else

980 query "replace into $t values($quotedp, \"$c\", \"$type\", \"$val\", \"$bin\");"

981 fi

982 )

983 }

984 expire() (

985 at="${1:-$timeout}"

986 FMT="${2:-%F %T}"

987 TZ=GMT gdate -d "$at" +"$FMT"

988 )

989 addsession() {

990 # expireをセット

991 # loginの先にどの画面に行くかの状態遷移表書式を決める

992 expire=`expire ${2:-"+1min"}`

993 query "replace into session values('$1', '$expire');"

994 # Remove old session parameters

995 now=`expire now`

996 query "delete from session where expire < '$now';"

997 }

998 gencookie() (

999 for kv; do

1000 expire="`expire '' '%a, %d-%b-%Y %H:%M:%S GMT'`"

1001 echo "Set-Cookie: $kv; expires=$expire"

1002 done

1003 )

1004 contenttype() {

1005 echo "Content-type: ${1:-text/html; charset=utf-8}"

1006 contenttype() {} # Only need to work once

1007 }

1008 putheader() {

1009

1010 }

1011 putfooter() {

1012 _m4 -D_TITLE_="${TITLE:-$myname}" $layout/footer.m4.html

1013 }

1014 getcookie() (

1015 for kv in `echo $HTTP_COOKIE|sed 's/[;, ]/ /g'`; do

1016 k="${kv%%=*}"

1017 v="`echo ${kv#*=}|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"

1018 query "replace into cookie values('$session', '$k', 'string', \"$v\");"

1019 done

1020 )

1021 genrandom() {

1022 # $1=columns (default: 10)

1023 dd if=/dev/urandom count=1 2>/dev/null|nkf -MB \

1024 | tr -d '+='|fold -w${1:-10}|sed -n 10p

1025 }

1026 genserial() {

1027 echo $((($(date +%s)-1433084400)/10))c$$

1028 }

1029 smail() {

1030 # smail rcpts subj (file)

1031 # $SMAIL_TO <- Recipient value of To: header

1032 # $MAIL_FROM <- From: header value

1033 from=`echo "${MAIL_FROM:-$admin}"|nkf -jM|tr -d '\n'`

1034 rcpt=`echo $1|tr ' ' '\n'|sort -u|tr '\n' ' '` # uniq and strip newlines

1035 subj=`echo $2|nkf -jM|tr -d '\n'`

1036 (_m4 -D_RCPT_="${SMAIL_TO:-$rcpt}" -D_SUBJ_="\`$subj'" -D_FROM_="$from" $msgdir/mail-header.m4

1037 cat $3 | nkf -jd ) | sendmail -f $admin $rcpt

1038 }

1039 setviastring() {

1040 table=$1

1041 oifs="$IFS"

1042 IFS="&"

1043 for us in $2; do

1044 k=${us%%=*}

1045 v="`echo ${us#*=}|tr '%+' '= '|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"

1046 query "replace into $table values('$session', '$k', 'string', \"$v\");"

1047 #echo $k=$v

1048 done

1049 IFS="$oifs"

1050 }

1051 checkdomain() (

1052 # Check the validity of domain by referring DNS

1053 item=$1

1054 err checkdomain $1

1055 host ${item#*@} 1>&3 2>&3

1056 host ${item#*@} >/dev/null 2>&1

1057 )

1058 pwcheck() {

1059 # $1=passwd

1060 dbpswd=`getpwfield $user pswd`

1061 encpswd=`mycrypt "$1" "$dbpswd"`

1062 ## err user=$user, pswd=$1, db=$dbpswd, enc=$encpswd

1063 [ x"$dbpswd" = x"$encpswd" ]

1064 }

1065 mypwhash() {

1066 mycrypt `cat` `genrandom 5`

1067 }

1068 wasureta() {

1069 user=$1

1070 if ! checkdomain $user; then

1071 contenttype; echo

1072 _m4 -D_TITLE_='Invalid email' $layout/title-only.html

1073 echo "ユーザ名($user)には正しいメイルアドレスが必要です。" | html p

1074 putfooter

1075 exit 0

1076 fi

1077 newpswd=`genrandom` # newsalt=`genrandom 5`

1078 #encpswd=`mycrypt "$newpswd" "$newsalt"`

1079 encpswd=`echo $newpswd|mypwhash`

1080 dbsetbyid user $user pswd "$encpswd"

1081 # Avoid $user substitution with m4, because $url comes from user input.

1082 _m4 -D_PSWD_="$newpswd" -D_URL_="$url" -D_ADMIN_="$admin" \

1083 $msgdir/mail-newaccount.m4 \

1084 | sed "s/_USER_/$user/g" \

1085 | smail $user "New Account"

1086 }

1087 checkauth() {

1088 user=`getpar user`

1089 skc=`getpar skey` # from cookie

1090 [ -z "$user" ] && return 3

1091 skey="`getpwfield $user skey`"

1092 if [ -n "$skey" ]; then

1093 if [ x"$skey" = x"$skc" ]; then

1094 return 0

1095 fi

1096 fi

1097 pswd=`getpar pswd`

1098 quser=`sqlquotestr "$user"`

1099 dbuser=`query "SELECT name FROM user WHERE name=$quser;"`

1100 if [ -z "$dbuser" ]; then

1101 return 1

1102 elif [ x"$pswd" = x"wasureta" ]; then

1103 wasureta $user

1104 return 1 # wasureta error

1105 fi

1106 # dbpswd="`sq $db \"select pswd from passwd where name='$user'\"`"

1107 # putheader; echo; echo user=$user, db=$dbpswd, enc=$encpswd

1108 if pwcheck "$pswd"; then

1109 newsession=`genrandom 50`

1110 dbsetbyid user $user skey "$newsession"

1111 gencookie "user=$user" "skey=$newsession"

1112 return 0

1113 fi

1114 return 2 # Password mismatch

1115 }

1116 showlogin() {

1117 args=`echo $myargs|tr ' ' '+'`

1118 _m4 -D_SYSNAME_="Welcome" -D_MYNAME_="$myname${args+?}$args" \

1119 $layout/login.m4.html

1120 exit 0

1121 }

1122 dologin() {

1123 checkauth

1124 st=$?

1125 if [ $st != 0 ]; then

1126 contenttype; echo

1127 _m4 -D_USER_="$user" -D_URL_="$url" -D_ADMIN_="$admin" \

1128 $msgdir/login-fail-$st.m4.html

1129 showlogin # and EXIT

1130 fi

1131 }

1132

1133 # Do instant jobs here

1134 dbsetup

1135 trap cleanup INT HUP EXIT TERM PIPE

1136 # trap cleanup INT HUP

1137

1138 err() {

1139 echo "$@" 1>&3

1140 }

1141

1142 cgiinit() {

1143 session=`date +%F-$$`

1144 tmpf=tmp/stream

1145 tmpd=`tmpd=$tmpdir mktempd`

1146 tmpfiles=$tmpfiles" $tmpd"

1147 addsession $session

1148 getcookie

1149 case "$REQUEST_METHOD" in

1150 get|GET) s="$QUERY_STRING" ;;

1151 post|POST) ## dd count=$CONTENT_LENGTH bs=1 of=$tmpf 2>/dev/null #slow

1152 ## dd bs=$CONTENT_LENGTH count=1 of=$tmpf # NOT working

1153 # cat > $tmpf # too much?

1154 head -c $CONTENT_LENGTH > $tmpf # safe?

1155 (echo CL=$CONTENT_LENGTH; ls -lF $tmpf) 1>&3

1156 s="`cat tmp/stream`"

1157 tmpfiles=$tmpfiles"${tmpfiles+ }$tmpf"

1158 ;;

1159 esac

1160 case "$CONTENT_TYPE" in

1161 *boundary*)

1162 bndry=${CONTENT_TYPE#*boundary=}

1163 #for us in `LC_CTYPE=C ./mpsplit.rb "$bndry" $tmpd < $tmpf`

1164 for us in `LC_CTYPE=C ./mpsplit.pl "$bndry" $tmpd < $tmpf`

1165 do

1166 k=${us%%\=*}

1167 #echo u=$us

1168 #v="`echo ${us#*=}|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"

1169 v="`echo ${us#*=}|unhexize|sed -e 's/\"/\"\"/g'`"

1170 # err k=$k v=$v

1171 case "$k" in

1172 *:filename)

1173 type='file'; k=${k%:filename}

1174 # DO NOT ALLOW Space and '|' in file names

1175 newv=`echo "$v"|sed 's/[ \|]/X/g'`

1176 if [ x"$v" != x"$newv" ]; then

1177 :

1178 fi

1179 # (echo k=$k v="[$v]"; ls -lF "$tmpd/$v"; file --mime-type "$tmpd/$v") 1>&3

1180 case `file --mime-type - < "$tmpd/$v"|cut -d' ' -f2` in

1181 [Ii]mage/x-xcf)

1182 bzip2 "$tmpd/$v"

1183 v=${v}.bz2

1184 ;;

1185 [Ii]mage/x-*|*/vnd.*) ;;

1186 [Ii]mage/*)

1187 mogrify -resize $maximagexy'>' "$tmpd/$v"

1188 ;;

1189 esac

1190 ;;

1191 *)

1192 type='string'

1193 ;;

1194 esac

1195 #sq $db "replace into par values('$session', '$k', '$type', \"$v\")"

1196 setpar "$k" "$type" "$v"

1197 done

1198 ;;

1199 *)

1200 setviastring par "$s"

1201 ;;

1202 esac

1203 }

1204 email4group() {

1205 # Get for-$1=group email address(es) for $2...=users

1206 qgrp=`sqlquote "$1"`; shift

1207 users=`for i; do sqlquote "$i"; done`

1208 users=`echo $users|tr ' ' ','`

1209 sql="select coalesce(s.val, g.user) from grp_mem g

1210 left join grp_mem_s s on g.gname=s.gname and g.user=s.user

1211 and s.key='email'

1212 where g.gname=$qgrp and g.user in ($users);"

1213 query "$sql"

1214 }

1215 email4groupbyuid() {

1216 # Get for-$1=group email address(es) for $2...=user-ids

1217 qgrp=`sqlquote "$1"`; shift

1218 uids=`echo "$@"`

1219 uids=`echo $uids|tr ' ' ','`

1220 sql="WITH

1221 grpemails AS (

1222 SELECT gname, user, val email

1223 FROM grp_mem NATURAL JOIN grp_mem_s

1224 WHERE key='email' AND gname=$qgrp),

1225 useremails AS (

1226 SELECT user.rowid rid, user.name, val email

1227 FROM user

1228 LEFT JOIN user_s

1229 ON user.name=user_s.name AND user_s.key='email')

1230 SELECT DISTINCT coalesce(g.email, u.name)

1231 FROM useremails u LEFT JOIN grpemails g

1232 ON u.name=g.user

1233 WHERE u.rid in ($uids);"

1234 ## err email4gByid `echo $sql`

1235 query "$sql"

1236 }

1237 collectmembersbyid() {

1238 # Collect user names of group specified by grid

1239 rid=${1%%[!0-9]*} # Cleaning

1240 query "SELECT user FROM grp_mem \

1241 WHERE gname=(SELECT gname FROM grp WHERE rowid=$rid);"

1242 }

1243 collectmembersbyid() {

1244 # Collect user names of group name

1245 qgrp=`sqlquote "$1"`

1246 query "SELECT user FROM grp_mem WHERE gname=$qgrp;"

1247 }

1248 collectgecosesbyid() {

1249 # Collect user gecoses of group

1250 rid=${1%%[!0-9]*} # Cleaning

1251 query<<-EOF

1252 SELECT gecos

1253 FROM gecoses

1254 WHERE name IN (SELECT user FROM grp_mem

1255 WHERE gname=(SELECT gname FROM grp WHERE rowid=$rid));

1256 EOF

1257 }

1258 collectemail() (

1259 # Collect email addresses for group $1

1260 # If $TEAM is set, filter by team name

1261 # If $EXCEPT is set as username(s) delimited by comma,

1262 # remove $EXCEPT from list: ....NOT IN ($EXCEPT)

1263 for e; do

1264 if isuser "$e"; then

1265 em=`query "select val from user_m where name='$e' and key='email';"`

1266 [ -n "$em" ] && echo "$em" || echo "$e"

1267 else

1268 qgrp=`sqlquote "$e"`

1269 if [ -z "$TEAM" ]; then

1270 gmem="grp_mem"

1271 else

1272 tm=`sqlquote "$TEAM"`

1273 gmem="(SELECT gname, user FROM grp_mem_m WHERE gname='$e' AND key='team' AND val=$tm)"

1274 fi

1275 ex=${EXCEPT:+"AND g.user NOT IN ($EXCEPT)"}

1276 sql="select coalesce(s.val,um.val,g.user) from

1277 $gmem g left join grp_mem_s s

1278 on g.gname=s.gname and g.user=s.user and s.key='email'

1279 left join user_m um on g.user=um.name and um.key='email'

1280 where g.gname=$qgrp $ex;"

1281 ## err CollectEmail: `echo "$sql"`

1282 query "$sql"

1283 fi

1284 done

1285 )

1286 sendinvitation() (

1287 # $1=email

1288 iss="invite-`date +%s`-$user"

1289 addsession $iss +${memoplimitdays}days # 1 week due date

1290 query "replace into par values('$iss', 'invite', 'string', \"$1\");"

1291 gecos=`gecos`

1292 name=$user"${gecos:+($gecos)}"

1293 regist="$urlbase?reg+$iss"

1294 _m4 -D_URL_="$urlbase" \

1295 -D_USER_="$name" \

1296 -D_EMAIL_="$1" \

1297 -D_REGIST_="$regist" \

1298 -D_ADMIN_="$admin" \

1299 $msgdir/mail-invite.m4 \

1300 | smail $1 "BBSへの御招待"

1301 return 0

1302 )

1303 emaildomaincheck() {

1304 case "$1" in

1305 *@*@*) echo "無効なアドレスです"; return 1 ;;

1306 *@*)

1307 local=${1%@*} domain=${1#*@}

1308 if ! host $domain >/dev/null 2>&1; then

1309 echo "ドメイン($domain)が見付かりません。"

1310 return 2

1311 fi

1312 return 0

1313 ;;

1314 *) echo "正しいメイルアドレスをいれてください"; return 3 ;;

1315 esac

1316 }

1317 invite() {

1318 email=`getpar email`

1319 case $email in

1320 *@*@*) repo="無効なアドレスです" ;;

1321 *@*)

1322 local=${email%@*} domain=${email#*@}

1323 if ! repo=`emaildomaincheck $email`; then

1324 repo="招待アドレスのエラー: $repo"

1325 elif [ -n "`query \"select * from user where name='$email';\"`" ]; then

1326 repo="$email さんは既に加入しています。"

1327 elif sendinvitation $email; then

1328 repo="アドレス($email)宛に案内を送信しました。"

1329 fi ;;

1330 "") repo="招待したい人のメイルアドレスを入力してください。" ;;

1331 *) repo="無効なアドレスです" ;;

1332 esac

1333 addr=`query "select val from par where sessid like 'invite-%-$user';"`

1334 if [ -n "$addr" ]; then

1335 susp="<h2>招待済みで加入待ちのアドレス</h2><pre>$addr</pre>"

1336 fi

1337 _m4 -D_TITLE_="招待" -D_REPORT_="\`$repo'" -D_ACTION_="?invite" \

1338 -D_BODYCLASS_="default" -D_SUSPENDED_="$susp" \

1339 $layout/html.m4.html $layout/invite.m4.html

1340 }

1341 regist() {

1342 # $1=session-id-for-invitation

1343 _m4 -D_TITLE_="Invitation" $layout/html.m4.html

1344 if [ -z "$1" ]; then

1345 echo "bye bye" | html p

1346 reutrn

1347 fi

1348 email=`session=$1 getpar invite`

1349 if [ -z "$email" ];then

1350 cat<<EOF

1351 <p>無効な招待状チケットです。</p>

1352 <p>招待状の有効期限(1週間)が切れているか、チケット番号が異なっています。

1353 加入している人に、再度招待してもらいましょう。</p>

1354 EOF

1355 return

1356 fi

1357 echo "$email さんようこそ" | html h2

1358 query "replace into user values('$email');"

1359 # Fake login password to wasureta

1360 query "replace into par values('$session', 'pswd', 'string', 'wasureta'),

1361 ('$session', 'user', 'string', '$email');"

1362 wasureta $email

1363 echo "このアドレスに初期パスワードを送信しました。" |html p

1364 echo "新着メイルを確認してログインしてください。" |html p

1365 addsession $1 # for removal after 1 minute

1366 _m4 -D_SYSNAME_="Initial Login" -D_MYNAME_="$myname?userconf" \

1367 $layout/login.m4.html

1368 return

1369 }

1370 group_safename() {

1371 # Convert $1 to safe group name

1372 echo "$1" | tr -d '"'"'",

1373 }

1374 groupupdate() {

1375 gname=`getpar gname`

1376 qgname=`sqlquote "$gname"`

1377 if [ -n "$gname" ]; then

1378 # See ALSO same job in showgroup()

1379 newgname=`group_safename "$gname"`

1380 err newgname=$newgname

1381 if [ x"$newgname" != x"$gname" ]; then

1382 err NewGNAME: gname=$newgname

1383 gname=$newgname

1384 echo "使用禁止文字を除去し $gname としました。" | html p

1385 replpar gname string "$gname"

1386 fi

1387 # Name confliction check

1388 parow=`getpar rowid`

1389 ## err parow=$parow

1390 qgname=`sqlquote "$gname"` # Set again in case gname modified

1391 query "BEGIN EXCLUSIVE;"

1392 ## err "select count(gname) from grp where rowid != ${parow:-0} and gname = $qgname;"

1393 count=$(query "select count(gname) from grp where rowid != ${parow:-0} and gname = $qgname;")

1394 if [ $count -gt 0 ]; then

1395 echo "そのグループ名は既にあります。" | html p

1396 query "END;"

1397 return

1398 fi

1399 par2table $formdir/grp.def

1400 query "END TRANSACTION;"

1401 # Remove orphan

1402 : <<EOF

1403 select a.id,b.val from (select * from blog where id in

1404 (select id from blog_s where key='owner'

1405 and val not in (select name from user union select gname from grp)))

1406 a left join blog_s b on a.id=b.id and b.key='owner';

1407 EOF

1408 rm=`getpar rm` cfm=`getpar confirm`

1409 ## err groupupdate:::: after par2tbl rmcfm=$rm$cfm

1410 if [ x"$rm$cfm" = x"yesyes" ]; then

1411 if [ -z "`query \"select gname from grp where gname=$qgname;\"`" ]; then

1412 sql="delete from blog where id in

1413 (select id from blog_s where key='owner' and val=$qgname);"

1414 err rm-grp cleaning sql=`echo $sql`

1415 query "$sql";

1416 grps # When removing a group, switch to grp-list

1417 return # and return

1418 fi

1419 fi

1420 [ -z "$parow" ] && joingrp "$gname" "$user" yes "" as-admin

1421 fi

1422 sql="select rowid from grp where gname=$qgname;"

1423 grid=$(query $sql)

1424 ## err grpupdate:new-grid=$grid, sql=$sql

1425 grp $grid

1426 }

1427 groupclone() {

1428 # $1=grp-rowid of clone-base group

1429 qgrp=`query "SELECT quote(gname) FROM grp WHERE rowid=$1;"`

1430 if [ -z "$qgrp" ]; then

1431 echo "無効なグループIDです($1)" | html p

1432 return

1433 fi

1434 i=0

1435 while true; do

1436 copy="-copy$i"

1437 newqname=`query "SELECT quote($qgrp || '$copy');"`

1438 # err Trying new grp=$newqname with copy=$copy

1439 test=`query "SELECT gname FROM grp WHERE gname=$newqname;"`

1440 if [ -n "$test" ]; then

1441 i=$((i++))

1442 continue

1443 fi

1444 break

1445 done

1446 # Creating New group "$newqname" with members of old group

1447 # err Creating new grp=$newqname with copy=$copy

1448 query<<-EOF

1449 BEGIN;

1450 INSERT INTO grp VALUES($newqname); -- Create NEW one

1451 REPLACE INTO grp_s(gname, key, val) -- Copy tag

1452 SELECT $newqname, key, val

1453 FROM grp_s WHERE gname=$qgrp AND key IN ('tag', 'mode');

1454 REPLACE INTO grp_s(gname, key, type, val) -- Copy gecos with "copy$n"

1455 SELECT $newqname, key, type, val || '$copy'

1456 FROM grp_s WHERE gname=$qgrp AND key='gecos';

1457 -- Copy members and their configuration --

1458 REPLACE INTO grp_mem SELECT $newqname, user

1459 FROM grp_mem WHERE gname=$qgrp;

1460 REPLACE INTO grp_mem_s SELECT $newqname, user, key, type, val, bin

1461 FROM grp_mem_s WHERE gname=$qgrp;

1462 REPLACE INTO grp_mem_m SELECT $newqname, user, key, type, val, bin

1463 FROM grp_mem_m WHERE gname=$qgrp;

1464 -- Copy administrators --

1465 REPLACE INTO grp_adm SELECT $newqname, user

1466 FROM grp_adm WHERE gname=$qgrp;

1467 COMMIT;

1468 EOF

1469 newrowid=`query "SELECT rowid FROM grp WHERE gname=$newqname;"`

1470 STOPCLONEMSG=1 groupconf "$newrowid"

1471 }

1472 groupman() {

1473 note="<p>グループ名に使用できない文字は自動的に削除されます。</p>"

1474

1475 GF_STAGE="grpconf"

1476 GF_STAGE=groupupdate

1477 DT_VIEW=grp dumptable html grp 'gname gecos:DESC mtime:TIME' 'order by b.TIME desc' \

1478 |_m4 -D_TITLE_="グループ作成" \

1479 -D_FORM_="$note`genform $formdir/grp.def`" \

1480 -D_DUMPTABLE_="syscmd(cat)" \

1481 $layout/html.m4.html $layout/form+dump.m4.html

1482 }

1483 userconf() {

1484 [ -n "`getpar rowid`" ] && par2table $formdir/user.def

1485 _m4 -D_BODYCLASS_=userconf -D_TITLE_="ユーザ情報編集" $layout/html.m4.html

1486 GF_ACTION="?home" edittable "$formdir/user.def" "user" "$user"

1487 }

1488 groupconf() {

1489 # $1=rowid in grp (2015-07-21 changed from gname)

1490 [ -n "`getpar rowid`" ] && par2table $formdir/grp.def

1491 _m4 -D_BODYCLASS_=groupconf -D_TITLE_="グループ情報編集" $layout/html.m4.html

1492 #rowid=`query "select rowid from grp where gname='$1';"`

1493 rowid=${1%%[!A-Z0-9a-z_]*}

1494 # GF_ACTION="?grp+$1" edittable "$formdir/grp.def" "grp" "$rowid" #2015-0804

1495 GF_STAGE="groupupdate" edittable "$formdir/grp.def" "grp" "$rowid"

1496 if [ -z "$STOPCLONEMSG" ]; then

1497 echo "同じ構成員で新規グループ<a href=\"?groupclone+$rowid\">作成</a>" \

1498 | html p

1499 fi

1500 }

1501 mems() {

1502 _m4 -D_TITLE_="参加者一覧" -D_BODYCLASS_=listmember $layout/html.m4.html

1503 kwd=`getpar kwd`

1504 listmember $kwd

1505 }

1506 grps() {

1507 _m4 -D_TITLE_="グループ一覧" -D_BODYCLASS_=listgroup $layout/html.m4.html

1508 kwd=`getpar kwd`

1509 listgroup $kwd \

1510 | _m4 -D_DUMPTABLE_="syscmd(cat)" \

1511 -D_TITLE_="グループ関連操作" \

1512 -D_FORM_="<a href=\"?groupman\">新規グループ作成</a>" \

1513 $layout/form+dump.m4.html

1514 }

1515 grp() { # $1=group-rowid

1516 gpg=`getpar grp`

1517 grid=${1:-$gpg}

1518 grp=`getgroupbyid "$grid"`

1519 ## . ./s4-blog.sh

1520 jg=`getpar joingrp`

1521 if [ -n "$jg" ]; then

1522 [ -n "$jg" -a -n "$grp" ] &&

1523 joingrp "$grp" "$user" "$jg" "`getpar email`"

1524 fi

1525 htmlheader=$layout/html.m4.html

1526 showgroup "$grid"

1527 }

1528 sql4interestblogs() {

1529 cat<<EOF

1530 CREATE TEMPORARY VIEW interestblogs AS

1531 SELECT blog.rowid rid, id, author

1532 FROM blog

1533 NATURAL JOIN

1534 (SELECT id, val owner FROM blog_s WHERE key='owner') bs

1535 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

1536 THEN 1 -- blog owner is an user, READABLE

1537 WHEN (SELECT user FROM grp_mem

1538 WHERE gname=bs.owner AND user='$user') IS NULL

1539 THEN 0

1540 ELSE 1

1541 END;

1542 EOF

1543 }

1544 listnewblogsql() { # $1=user

1545 deftime=`query "SELECT coalesce((SELECT time FROM acclog

1546 WHERE user='$user'

1547 AND tblrowid=$blogreadflagrowid),

1548 "0");"`

1549 cat<<EOF

1550 `sql4interestblogs`

1551 WITH article_ctime as (

1552 SELECT id,blogid,author,val ctime

1553 FROM article join article_s s using(id)

1554 WHERE s.key='ctime'

1555 ), blog_title_owner as (

1556 SELECT blg.rid brid, id,

1557 max(case key when 'title' then val end) title,

1558 max(case key when 'owner' then val end) owner

1559 FROM interestblogs blg, blog_s using(id) group by id

1560 ), visited AS (

1561 SELECT b.id id, brid, b.owner owner, b.title title, ctime, ac.author author

1562 FROM blog_title_owner b, article_ctime ac

1563 JOIN acclog al

1564 ON b.id=ac.blogid

1565 AND al.tbl='blog'

1566 AND al.tblrowid=brid

1567 AND al.user='$user'

1568 AND al.time < ctime

1569 AND '$deftime' < ctime

1570 ), unvisited as (

1571 SELECT b.id id, brid, b.owner owner, b.title title, ctime, ac.author author

1572 FROM blog_title_owner b, article_ctime ac

1573 ON b.id=ac.blogid

1574 WHERE brid NOT IN (SELECT tblrowid FROM acclog

1575 WHERE tbl='blog' AND user='$user')

1576 AND ctime > '$deftime'

1577 ), blogall as (

1578 /* ---------------------------------------

1579 Collect new articles with dividing them into visited and unvisited

1580 separately, because constructing joined table of artice X acclog

1581 tends to become HUGE combinations.

1582 --------------------------------------- */

1583 SELECT * FROM visited UNION SELECT * FROM unvisited

1584 ), news as (

1585 select bl.brid brid, bl.title, bl.id blid, ctime,

1586 count(bl.id) "新着", bl.owner, bl.author

1587 from blogall bl /* left join a_u l

1588 on bl.brid=l.tblrowid */

1589 group by bl.id order by ctime desc,"新着" desc, bl.id

1590 LIMIT 10

1591 ) SELECT brid LINK, "新着",

1592 (SELECT count(*) FROM article WHERE blogid=blid) "総数",

1593 ctime, title,

1594 (SELECT gecos FROM gecoses WHERE name=author) gecos

1595 FROM news;

1596 EOF

1597 }

1598

1599 search_form() {

1600 help="(1)空白区切りの単語で本文検索

1601 (2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索

1602 @2016-0[1-6] → 2016年1月から6月

1603 @>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間

1604 @week → 最近一週間

1605 (3)#番号で記事ID検索

1606 (1)と(2)は組み合わせOK

1607 例: @2016-10-0[1-9] 芋煮

1608 → 2016年10月上旬でキーワード「芋煮」を含む記事検索

1609 ※クイズ板は検索対象から外されます。"

1610 auth=""

1611 placeholder="全記事からの検索"

1612 case "$1" in

1613 author=*)

1614 a=`echo "${1#author=}"|htmlescape`

1615 g=`gecos ${1#author=}`

1616 auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"

1617 placeholder="このユーザの書込検索"

1618 help="★★ $g さんの書き込みから検索します$nl$help"

1619 ;;

1620 grp=*)

1621 a=`echo "${1#grp=}"|htmlescape`

1622 g=`gecos ${1#grp=}`

1623 auth="<input type=\"hidden\" name=\"owner\" value=\"$a\">"

1624 placeholder="このグループからの検索"

1625 ;;

1626 esac

1627 cat<<-EOF

1628 <div class="right">

1629 <form action="$myname">$auth

1630 <input type="text" name="kwd" value="" title="$help"

1631 placeholder=" $placeholder " width="10" accesskey="k">

1632 ${touchpanel:+<p class="help">$help</p>}

1633 <input type="hidden" name="stage" value="searchart">

1634 </form>

1635 </div>

1636 EOF

1637 }

1638

1639 imgsrc_cache() (

1640 # $1 = directory for cache'ing

1641 # $2 = table (user_m or grp_m)

1642 # $3 = keycond (was: condition for choosingowner)

1643 # $4 = size : S = Small, M = Medium, O = Original

1644 dir="$1" tbl="$2"

1645 keycond="$3"

1646 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

1647 ORDER BY rowid DESC LIMIT 1"

1648 [ -d "$dir" ] || mkdir -p "$dir"

1649 tmpf=$tmpd/imgsrc_cache.$$

1650 case "$4" in

1651 [Ss]) size=S ;;

1652 [Oo]) size=O ;;

1653 *) size=M ;;

1654 esac

1655 # ImageCache filename storing schema:

1656 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

1657 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

1658 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

1659 valtype=`query "$sql0"`

1660 filename=${valtype%%//*}

1661 filetype=${valtype##*//file:}

1662 if [ x"$filename" = x"${filename%.*}" ]; then

1663 # If nor filename extension found, set it to image type

1664 case "$filetype" in

1665 image/*) filename=$filename.${filetype#image/} ;;

1666 esac

1667 fi

1668 cacheimg_S=$dir/S_$filename

1669 cacheimg_M=$dir/M_$filename

1670 cacheimg_O=$dir/$filename

1671 cacheimg=$dir/${size}_$filename

1672 sumfile="$dir/$filename.sum"

1673 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

1674 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

1675 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

1676 # if cache is fresh and has the same checksum,

1677 echo "<img src=\"$cacheimg\">"

1678 else

1679 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

1680 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

1681 fifo2=$fifo.2

1682 mkfifo $fifo $fifo2

1683 fmt=${filename##*.}

1684 ## [[ NOTE ]]

1685 ## a. convert oldimage newimage

1686 ## b. convert oldimage fmt:- | convert - newimage

1687 ## b is much smaller than a

1688 cat $tmpf | unhexize \

1689 | tee $fifo \

1690 | convert -define ${fmt}:size=${iconxy_M} \

1691 -resize ${iconxy_M}'>' - ${fmt}:- \

1692 | tee $fifo2 \

1693 | convert - "$cacheimg_M" &

1694 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

1695 -resize ${iconxy_S}'>' - ${fmt}:- \

1696 | convert - "$cacheimg_S" &

1697 printf '%s' "<img src=\"data:${filetype},"

1698 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

1699 echo '">'

1700 echo "$sum" > $sumfile

1701 fi

1702 ## Now preparing cache image, done.

1703 ## Store this information to DB

1704 stbl=${tbl%_m}_s # user_s or grp_s

1705 pkey=${keycond%%=*} # Primary Key name

1706 pval=${keycond#*=} # Primary Key value

1707 query <<-EOF

1708 REPLACE INTO $stbl($pkey, key, type, val)

1709 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

1710 EOF

1711 )

1712

1713 showhome() {

1714 # $1=userRowIdToShow

1715 err showhome \$1=$1

1716 case "$1" in

1717 *@*) uname=`getvalbypkey user name "$1"` ;;

1718 *) uname=`getvalbyid user name $1` ;;

1719 esac

1720 ## err ShowHome: uname=$uname

1721 td=`getcachedir home/"$1"`

1722 gecos=`gecos "$uname"`

1723 ## err SH:gecos=$gecos

1724 GF_VIEWONLY=1

1725 cond="gname in (select gname from grp_mem where user='$uname')"

1726 search_form_args=""

1727 if [ x"$user" = x"$uname" ]; then

1728 usermenu="<a href=\"?userconf\" accesskey=\"e\"

1729 title=\"E\">プロフィールの編集</a> /

1730 <a href=\"?blog\" accesskey=\"n\" title=\"N\">新規話題の作成</a>"

1731 # Display folders

1732 sql="select count(id) from article_m where id

1733 in (select id from article where author='$user')

1734 and type like 'file:%';"

1735 ## err nfile-sql=`echo "$sql"`

1736 nfile=`query "$sql"`

1737 # err nfile=$nfile

1738 if [ $nfile -gt 0 ]; then

1739 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

1740 title=\"L\">過去の提出ファイル</a>"

1741 fi

1742 else

1743 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

1744 GROUP BY user;"`

1745 usermenu="<p>Last seen on $latestlog</p>"

1746 search_form_args="author=$uname"

1747 fi

1748 . ./s4-blog.sh

1749

1750 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

1751 search_form $search_form_args > $sf

1752 echo "$gecos さん" > $tf

1753 { echo "<div class=\"noprofimg\">"

1754 viewtable $formdir/user.def user $1

1755 echo "</div>"

1756 } > $pf

1757

1758 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

1759 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

1760 imf=$tmpd/profimg.$$; touch $imf

1761 if [ -n "$img" ]; then

1762 if true; then

1763 tbl=user_m

1764 enticond="name='$uname'"

1765 imgsrc_cache "$td/main" user_m "$enticond" M

1766 else

1767 { printf '%s' "<IMG src=\"data:${img#file:},"

1768 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

1769 | sed 's/$..$/%\1/g'

1770 echo '">'

1771 }

1772 fi > $imf

1773 fi

1774 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

1775 val='$uname';"`

1776 listblog $uname > $bf

1777

1778 hometail=$tmpd/tail.$$

1779 mkfifo $hometail

1780

1781 #Calling listgroupbytable, originally here

1782

1783 (

1784 # Display Most Recent Entry

1785 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

1786 shortval=${shortval:-val}

1787

1788 # The m.aid in the next line is suspicious. But works fine in SQLite3...

1789 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

1790 ctime,

1791 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

1792 (SELECT gecos FROM gecoses

1793 WHERE name=(SELECT val FROM blog_s

1794 WHERE key='owner' AND id=b.id)) owner,

1795 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

1796 FROM blog b

1797 JOIN

1798 (SELECT distinct blogid, a.id aid, max(val) ctime

1799 FROM article a, article_s s

1800 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

1801 GROUP BY blogid ORDER BY val DESC LIMIT 50

1802 ) m

1803 ON b.id=m.blogid;"

1804 # This should be as follows

1805 : <<EOF

1806 WITH arts AS(

1807 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

1808 a.blogid, a.id id, s.val ctime

1809 FROM article a NATURAL JOIN article_s s

1810 WHERE s.key = 'ctime' AND a.author='$user'

1811 GROUP by s.id

1812 )

1813 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

1814 FROM arts a0

1815 JOIN

1816 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

1817 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

1818 ORDER BY ctime DESC LIMIT 50;

1819 EOF

1820

1821 cat<<-EOF

1822 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

1823 for="mre" title="D">最近の書き込み先</label>

1824 <div class="lcto">

1825 `DT_VIEW=replyblog dumptable html blog`

1826 </div>

1827 EOF

1828 unset DT_SQL

1829 if [ x"$user" = x"$uname" ]; then

1830 # Display NEWS

1831 # 2016-06-26

1832 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

1833 acclog blog $blogreadflagrowid

1834 # echo "全部既読にしました" | html p

1835 fi

1836 # 2016-02-19 Counting NEWS without using dumptable.

1837 sql=`listnewblogsql "$user"`

1838 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

1839 cont=`echo "$new10"|grep "^<TR>"|wc -l`

1840 cont=$((cont-1))

1841 err newcount=$cont

1842 if [ $cont -gt 0 ]; then

1843 #echo "全体の新着記事${cont}傑" | html h2

1844 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

1845 echo "<label for=\"new10\" title=\"F\">新着${cont}傑</label><div>"

1846 cat<<-EOF | html form 'action="?home"'

1847 `cgi_checkbox readchk yes 'id="read"'`<label

1848 for="read">新着ふくめて全部読んだことにする</label>

1849 `cgi_submit '確定'`

1850 `cgi_hidden read yes`

1851 EOF

1852 echo "$new10 "

1853 echo "</div>"

1854 fi

1855 else # Not My Home ($user != $uname)

1856 : # DT_SQL=

1857 fi

1858 ) > $hometail & # Is background call safe to m4??

1859 #

1860 listgroupbytable $formdir/grp.def $cond |

1861 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

1862 -D_PROFILE_="spaste(\`$pf')$usermenu" \

1863 -D_PROFIMG_="spaste(\`$imf')" \

1864 -D_BLOGS_="spaste(\`$bf')" \

1865 -D_SEARCH_="spaste(\`$sf')" \

1866 -D_NBLOG_="$nblog" \

1867 -D_GROUPS_="syscmd(\`cat')" \

1868 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

1869 $layout/html.m4.html $layout/home.m4.html

1870

1871 # Record access log

1872 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

1873 }

1874 commission() { # $1=grp-rowid $2=user-rowid

1875 contenttype; echo

1876 ## err commission: "$@"

1877 gname=`getgroupbyid $1`

1878 echo "グループ $gname 管理者委任" \

1879 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

1880 if [ -n "$2" ]; then

1881 grp_reg_adm "$@"

1882 else

1883 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

1884 fi

1885 }

1886 listgroupbytable() {

1887 # $1=deffile $2...=condition

1888 tagline=`grep :tag: $1`; shift

1889 and="${1:+and }" where=${1:+where }

1890 href="<a href=\"$myname?grp+"

1891 echo '<div class="listgroup">'

1892 NGsql="select distinct tag from\

1893 (select gname, max(case key when 'tag' then val end) as tag, \

1894 max(case key when 'ctime' then val end) as ctime\

1895 from grp_s group by gname order by ctime);"

1896 sql="select val from grp_s where key='tag' $and$* group by val;"

1897 ## err ListGRP: query sql="$sql"

1898 for tag in `query "$sql"`

1899 do

1900 ## err ListGrp: tag=$tag

1901 tn=${tagline%%=${tag}*}

1902 tn=${tn##*[ :]}

1903 sql="select rowid||':'||gname as 'グループ名',説明 from

1904 (select (select rowid from grp g where g.gname=grp_s.gname)

1905 as rowid,

1906 gname,

1907 max(case key when 'gecos' then val end) as '説明',

1908 max(case key when 'tag' then val end) as 'tag',

1909 max(case key when 'mtime' then val end) as mtime from grp_s

1910 $where$* group by gname having tag='$tag' order by mtime desc);"

1911 ## err PersonalGroupList= `echo $sql`

1912 echo "<h2>$tn</h2>"

1913 echo '<table class="b listgroup">'

1914 sq -header -html $db "$sql" \

1915 | sed "s,$<TR><TD>$$[0-9]*$:$[^ ]*$</TD>,\1$href\2\">\3</a>,"

1916 echo '</table>'

1917 done

1918 echo '</div>'

1919 }

1920 iconhref() (

1921 # $1=icon-file, $2=Href $3=title $4...=anchor

1922 data=`percenthex "$1"`

1923 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

1924 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

1925 href=$2; title=$3; shift 3

1926 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

1927 )

1928 iconhref2() (

1929 # $1=icon-file, $2=Href $3=title $4...=anchor

1930 src=$1

1931 href=$2; title=$3; shift 3

1932 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

1933 )

1934 listentry() (

1935 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

1936 # Referring variable $iamowner=$grp to attach owner-request links

1937 ## err listentry: \$1=$1 \$2=$2 \$3=$3

1938 cond='' hiddens=''

1939 offset=`getpar offset`; offset=${offset%%[!0-9]*}

1940 if [ -z "$offset" ]; then

1941 offset=`getpar start`; offset=${offset%%[!0-9]*}

1942 offset=$((offset-1))

1943 fi

1944 offset=$((offset + 0)) # change to numeric forcibly

1945 [ $offset -lt 0 ] && offset=0

1946 limit=30

1947 dir=`getcachedir "$1"`

1948 if [ x"$1" = x"user" ]; then

1949 hrb="$myname?home"

1950 deficon=person-default.png

1951 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

1952 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

1953 gcs=gecos

1954 else # if group

1955 hrb="$myname?grp"

1956 deficon=group-default.png

1957 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

1958 gcs=name

1959 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

1960 if [ -n "$tagline" ]; then

1961 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

1962 ## err tagconv=$tagconv

1963 fi

1964 fi

1965 if [ ! -d $dir ]; then

1966 mkdir -p $dir

1967 fi

1968 if [ ! -s $dir/$deficon ]; then

1969 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

1970 fi

1971 if [ -n "$2" ]; then

1972 cond="where nick like '%$2%' or b.name like '%$2%'"

1973 fi

1974

1975 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

1976 # grpは呼出し元の動的スコープ変数でよくないな...

1977 ##qgrp=`sqlquote $grp`

1978 getgrp="(select gname from grp where rowid=${rowid:--1})"

1979 sql="select a.rowid, a.$link,

1980 coalesce(b.$gcs, a.$nm) as nick,

1981 quote(a.$nm) as qname,

1982 (SELECT val FROM ${tbl}_s

1983 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

1984 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

1985 || case when a.$nm in (select gname from grp)

1986 then printf('(%d名)',

1987 (select count(user) from grp_mem where gname=a.$nm))

1988 else ' <'||a.$nm||'>'

1989 end

1990 as name,

1991 b.tag,

1992 case when a.$nm in (select user from grp_adm

1993 where gname=$getgrp) then '(管理者)'

1994 when '$user' in (select user from grp_adm where gname=a.$nm)

1995 then '(ADMIN)'

1996 when '$user' in (select user from grp_mem where gname=a.$nm)

1997 then '(Member)'

1998 when '$iamowner' = '' then ''

1999 else ',not='||a.rowid end as ownerlink,

2000 CASE '$entity'

2001 WHEN 'グループ'

2002 THEN coalesce(

2003 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2004 'open')

2005 ||

2006 CASE WHEN '$user'

2007 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2008 THEN ' ismember'

2009 ELSE ''

2010 END

2011 ELSE 'user'

2012 END regmode

2013 from $tbl a left join

2014 (select $nm as name,

2015 max(case key when 'gecos' then val end) as gecos,

2016 max(case key when 'tag' then val end) as tag,

2017 max(case key when 'mtime' then val end) as mtime,

2018 max(case key when 'wtime' then val end) as wtime

2019 from ${tbl}_s group by $nm)

2020 b on a.$nm=b.name $cond $3

2021 order by b.tag desc, b.wtime desc, b.mtime desc, a.rowid asc"

2022 # Give precedence to newer maintained groups (2016-09-24)

2023 # Note that mtime is stored only in grp_s.

2024 ## err LE:sql.1="$sql"

2025 total=`query "with x as ($sql) select count(*) from x;"`

2026 echo "${entity} 一覧" | html h2

2027 if [ $total -gt $limit ]; then

2028 echo '<div class="right">'

2029 cgi_form $stage <<EOF

2030 <label>次の語を含む${entity}で検索:

2031 `cgi_text kwd $kwd`</label>

2032 EOF

2033 echo '</div>'

2034 fi

2035 hiddens="$hiddens

2036 `cgi_hidden kwd \"$kwd\"`

2037 `cgi_hidden stage \"$stage\"`"

2038 cat<<EOF

2039 <form action="$myname" method="POST">

2040 <p>${total}件中の<input class="hidesub" type="text" name="start"

2041 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2042 <input type="submit" value="確定"></p>

2043 </form>

2044 EOF

2045 if [ $((offset+limit)) -lt $total ]; then

2046 nextbtn=$(

2047 cat<<EOF

2048 <div class="right clear"><form action="$myname" method="POST">

2049 `cgi_submit 次の${limit}件`

2050 $hiddens

2051 `cgi_hidden offset $((offset + limit))`</form></div>

2052 EOF

2053 )

2054 fi

2055 if [ $offset -gt 0 ]; then

2056 prevbtn=$(

2057 cat<<EOF

2058 <form action="$myname" method="POST">

2059 `cgi_submit 前の${limit}件`

2060 $hiddens

2061 `cgi_hidden offset $((offset - limit))`</form>

2062 EOF

2063 )

2064 fi

2065 pnbtn="$nextbtn$prevbtn"

2066 echo $pnbtn

2067

2068 ## err ListEntry: `echo "$sql"\;`

2069 # sq $db here??? 2016-11-28

2070 query "$sql limit $limit ${offset:+offset $offset};" \

2071 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2072 err name=$name owner=$ownerp lnk=$lnk

2073 err newlnk=$lnk regmode=$regmode

2074 icondir=$dir/$id

2075 # Pick up only last icon

2076 echo "<div class=\"iconlist xy$thumbxy $type\">

2077 <p class=\"tag _$tag\">$tag</p>" \

2078 | _m4 $tagconv

2079 if [ -n "$NOSPEEDUP" ]; then

2080 files=`getvalbyid $tbl profimg $id $icondir`

2081 if [ -n "$files" ]; then

2082 icon=`echo "$files"|tail -1`

2083 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2084 else

2085 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2086 fi

2087 elif [ -n "$icon" -a -s "$icon" ]; then

2088 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2089 else

2090 cond="$nm=$qname"

2091 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2092 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2093 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2094 # err "img=[$img]"

2095 if [ -n "$img" ]; then

2096 echo "<a href=\"$hrb+$lnk\">"

2097 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2098 echo "</a>"

2099 else

2100 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2101 fi

2102 fi

2103 echo "<br>$name${ownerp:+<br>$ownerp}"

2104 echo "</div>"

2105 done

2106 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2107 )

2108 listmember() {

2109 listentry user "$@"

2110 }

2111 listgroup() {

2112 listentry group "$@"

2113 }

2114 hexteams() { # $1=gname, $2(optional)=user

2115 cond=${2:+" AND user='$2'"}

2116 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2117 WHERE gname='$1' AND key='team'$cond;"

2118 }

2119 showgroup() { # $1=group-rowid

2120 if [ -z "$1" ]; then

2121 grid=`getpar grid`

2122 grid=${grid%%[!0-9]*}

2123 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2124 else

2125 grid=$1

2126 fi

2127 grp=`getgroupbyid $grid`

2128 qgrp=`sqlquote "$grp"`

2129 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2130 if isgroup "$grp"; then

2131 tf=$tmpd/title.$$

2132 sf=$tmpd/search.$$

2133 bodyclass=`query "SELECT val FROM grp_s

2134 WHERE gname=$qgrp AND key='regmode';"`

2135 if ismember "$user" "$grp"; then

2136 ismember="ismember"

2137 qgrp=`sqlquote "$grp"`

2138 bodyclass="$bodyclass${bodyclass:+ }ismember"

2139 else

2140 ismember="" # bodyclass="group"

2141 fi

2142 bodyclass="$bodyclass grouphome"

2143 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2144 echo "グループ $grp" > $tf

2145

2146 showgroupsub $formdir/grp.def "$grid" | \

2147 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2148 -D_FORM_="syscmd(\`cat')" \

2149 -D_BODYCLASS_="$bodyclass" \

2150 -D_DUMPTABLE_="" \

2151 $htmlheader $sf $layout/form+dump.m4.html

2152 # $htmlheader $layout/form+dump.m4.html

2153 # $htmlheader is defined in grp()

2154 else # if $grp is removed at par2table

2155 listgroup

2156 fi

2157 }

2158 showgroupsub() {

2159 # $1=def-file $2=group-rowid

2160 # Using $ismember

2161 rowid=$2

2162 grp=`getgroupbyid $2`

2163 qgrp=`sqlquote "$grp"`

2164 td=`getcachedir grp/"$2"`

2165 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2166 if [ -z "$rowid" ]; then

2167 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2168 #grp=`sq $db "select gname from grp where rowid=$grp"`

2169 echo "showgroupsub: invalid argument($1 $2)" | html p

2170 return

2171 fi

2172 val=`getvalbyid grp profimg $rowid $tmpd`

2173 enticond="gname=$qgrp"

2174 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2175 if [ -n "$img" ]; then

2176 cat<<-EOF

2177 <p class="groupimg">

2178 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2179 EOF

2180 fi

2181 echo "<div class=\"noprofimg\">"

2182 viewtable $1 grp $rowid

2183 echo "</div>"

2184 if isgrpowner "$user" "$grp"; then

2185 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2186 iamowner=$rowid

2187 colmd=" mode"

2188 fi

2189 if [ -n "$ismember" ]; then

2190 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2191 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2192 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2193 cat<<EOF

2194 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2195 <div class="fold clear">

2196 `cgi_checkbox send yes id="send"`<label

2197 for="send">グループ全員にメッセージ送信</label>

2198 <div>

2199 `cgi_textarea message "" "cols=60"`

2200 `cgi_submit 送信`

2201 `cgi_reset リセット`

2202 </div>

2203 `cgi_hidden grp $rowid`

2204 </div></form>

2205 EOF

2206 fi

2207 # 加入ボタン + 加入者リスト

2208 if [ -n "$ismember" ]; then

2209 ismem='checked' state="(参加中)"

2210 else

2211 nomem='checked' state="(現在非加入)"

2212 fi

2213 # このグループでの加入アドレス

2214 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2215 and key='email';"`

2216 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2217 ## and key='email';"

2218 ##err email=$eml

2219 cat <<EOF

2220 <div class="fold clear">

2221 `cgi_checkbox reg yes id="reg"`<label

2222 for="reg">自身の加入状態を操作する</label>$state

2223 <div>

2224 EOF

2225 cgi_form grp <<EOF

2226 <p>このグループに</p>

2227 <table class="b">

2228 <tr><th>メンバーとして</th><td>

2229 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2230 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2231 <tr><th>参加する場合のメイルアドレス<br>

2232 <small>(メインのアドレスとは違うものにする場合に記入<br>

2233 同じでよい場合は空欄に)</small></th>

2234 <td>`cgi_text email $eml`</td></tr>

2235 </table>

2236 `cgi_hidden grp $rowid`

2237 EOF

2238 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2239 echo "moderated (承認加入の)グループなので実際に参加できるのは

2240 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2241 fi

2242 echo '</div></div>'

2243 echo '<h2>話題一覧</h2>'

2244 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2245 cat<<-EOF

2246 <form class="summary" action="$myname" title="$thelp">

2247 `cgi_hidden owner "$grp"`

2248 `cgi_hidden kwd "@week"`

2249 `cgi_hidden stage searchart`

2250 `cgi_submit "一週間のまとめ"`

2251 </form>

2252 EOF

2253 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2254 colstate="state:稼動状態:frozen=rowclass=凍結"

2255 DT_CHLD=article:blogid \

2256 DT_VIEW=replyblog dumptable html blog \

2257 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2258

2259 getgname="(select gname from grp where rowid=$rowid)"

2260 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2261 cm="?commission+$rowid"

2262 thumbxy=50x50 listmember "" "$c" "$rowid" \

2263 |sed -e "s|$<br>$,not=$.*$|\1|" # 間違って押しやすい

2264 # team list

2265 hexteams=`hexteams "$grp"`

2266 if [ -n "$hexteams" ]; then

2267 echo "チーム一覧" | html h2

2268 echo '<div class="dumptable"><table class="b">'

2269 sq $db -html -header<<-EOF

2270 SELECT val TEAM,

2271 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2272 MEMBERS

2273 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2274 EOF

2275 echo '</table></div>'

2276 fi

2277 }

2278 grp_getbodyclass() {

2279 # Get css class name for document.

2280 # `moderated' for moderated groups

2281 # `ismember' for groups where user belongs

2282 # $1=GroupName (w/o quote)

2283 # $user=userNameCurrentlyLogin

2284 ## err grp_getbodyclass: 1="$1"

2285 qgrp=`sqlquote "$1"`

2286 query<<-EOF

2287 SELECT coalesce(

2288 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2289 'open')

2290 ||

2291 CASE WHEN '$user'

2292 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2293 THEN ' ismember'

2294 ELSE ''

2295 END;

2296 EOF

2297 }

2298 grpaction() { # $1=group-rowid

2299 err GRP_ACTION:IN

2300 grid=${1:-`getpar grp`}

2301 grp=`getgroupbyid "$grid"`

2302 if [ -z "$grp" ]; then

2303 echo "無効な指定です。" | html p; return

2304 fi

2305 if ! ismember $user "$grp"; then

2306 echo "加入者のみに許可された操作です。" | html p; return

2307 fi

2308 echo "グループ $grp 個別選択操作" \

2309 | _m4 -D_TITLE_="syscmd(\`cat')" \

2310 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2311 $layout/html.m4.html

2312

2313 isowner=""

2314 isgrpowner "$user" "$grp" && isowner="yes"

2315 usel=`getpar usel`

2316 if [ -n "$usel" ]; then

2317 uids=$(echo `echo $usel`|tr ' ' ',')

2318 ## err grpaction-1: grp=$grp, `echo $sql`

2319 text=`getpar text`

2320

2321 rm=`getpar rm` cfm=`getpar confirm`

2322 ## err rm=$rm cfm=$cfm

2323 if [ x"$rm" = x"yes" ]; then

2324 if [ "$isowner" ]; then

2325 if [ x"$rm$cfm" = x"yesyes" ]; then

2326 # Eliminate

2327 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2328 for tbl in grp_mem grp_mem_s grp_mem_m; do

2329 sql="delete from $tbl $cond;"

2330 # echo "sql=$sql"

2331 query "$sql"

2332 err rmGRPuser "$sql"

2333 done

2334 num=`query "select count(*) from user where rowid in ($uids);"`

2335 #err num=$num

2336 if [ 0$num -gt 0 ]; then

2337 sql="select coalesce(b.val,a.name) from user a left join \

2338 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2339 # err `echo "$sql"`

2340 html pre<<EOF

2341 以下の${num}名のグループ $grp 登録を解除しました。

2342 `query "$sql"`

2343 EOF

2344 fi

2345 else

2346 echo "確認のチェックがないのでやめておきます。" | html p

2347 return

2348 fi

2349 else # not Group Owner

2350 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2351 return

2352 fi

2353 cat<<EOF

2354

2355 EOF

2356 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2357 if [ -z "$text" ]; then # if msg is empty

2358 echo "なにかメッセージを..." | html p

2359 return 0

2360 fi

2361 gecos=`gecos $user`

2362 mkfrom=`getpar mkfrom`

2363 if [ x"$mkfrom" = x"yes" ]; then

2364 safegc=`echo "$gecos" | tr -d '<>@'`

2365 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2366 fromad=`email4groupbyuid "$grp" "$myuid"`

2367 mail_from="$safegc <$fromad>"

2368 else

2369 mail_from="$admin"

2370 fi

2371 MAIL_FROM=$mail_from \

2372 smail "`email4groupbyuid "$grp" $usel` $user" \

2373 "$gecos さんからのメッセージ" <<EOF

2374 $url

2375 のグループ「$grp」のメンバーである $gecos さんから、

2376 あなた宛へのメッセージです。

2377 ----------------------------------------------------------

2378 $text

2379 EOF

2380 if [ $? = 0 ]; then

2381 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2382 sql="select coalesce(b.val, a.name) from

2383 (select name from user where rowid in ($uids)) a

2384 left join user_s b on a.name=b.name and b.key='gecos';"

2385 html pre<<EOF

2386 `query "$sql"`

2387 (送信者である $gecos さんも含まれます)

2388 EOF

2389 err SendDone: `echo $sql`

2390 fi

2391 elif [ x"$rm" = x"commission" ]; then

2392 grp_reg_adm $grid $usel

2393 elif [ x"$rm" = x"addteam" ]; then

2394 team=`getpar team|sed "s/'/''/g"` # for single quotation

2395 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2396 if [ x"$team" != x"$newteam" ]; then

2397 echo "チーム名に使えない文字を除去しました" | html p

2398 team=newteam

2399 fi

2400 if [ -z "$team" -o x"$team" = x"なし" ]; then

2401 cat<<-EOF | html p

2402 有効なチーム名を入力してください。

2403 カンマだけ、「なし」という名前は使えません。

2404 EOF

2405 echo "有効なチーム名を入力してください。" | html p

2406 else

2407 grp_add_team $grid "$team" $usel

2408 fi

2409 elif [ x"$rm" = x"rmteam" ]; then

2410 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2411 rmteam=`getpar rmteam|sed "s/'/''/g"`

2412 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2413 gname='$grp' AND user='$user' AND key='team'\

2414 AND val='$rmteam';\"`" ]; then

2415 grp_rm_team $grid "$rmteam" $usel

2416 else

2417 echo "所属していないチームの除去操作はできません。"|html p

2418 fi

2419 else

2420 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2421 fi

2422 fi

2423 fi

2424 # New entry

2425 sql="WITH mems AS (

2426 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2427 ON gm.user=g.name

2428 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2429 ), posts AS (

2430 SELECT author, count(author) post

2431 FROM article NATURAL JOIN article_s

2432 WHERE blogid IN (SELECT id FROM blog_s

2433 WHERE key='owner'

2434 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2435 AND key='text'

2436 GROUP BY author

2437 ), teams AS (

2438 SELECT user, group_concat(val, ', ') team

2439 FROM grp_mem_m

2440 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2441 AND key='team'

2442 GROUP BY user

2443 ), user_post AS (

2444 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2445 FROM mems m LEFT JOIN posts

2446 ON m.name=posts.author

2447 GROUP by m.rowid

2448 )

2449 SELECT

2450 CASE

2451 WHEN (SELECT user FROM grp_adm

2452 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2453 AND user=up.name) IS NOT NULL

2454 then 'k'

2455 ELSE ''

2456 END || rowid || ',' || gecos NAME,

2457 post POST, team TEAM

2458 FROM user_post up LEFT JOIN teams t

2459 ON up.name=t.user

2460 ORDER BY gecos;"

2461 ## err grpaction: "`echo \"$sql\"`"

2462 tf=$tmpd/title.$$

2463 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2464 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2465 for=\"cmadmin\">グループ管理者委任</label>

2466 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2467 </p></div>"

2468 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2469 for=\"conf\">グループ登録解除</label>

2470 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2471 <p>この操作による通知は本人に行きません。

2472 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2473 </div>"

2474 # Get team list to which current user belongs into $hexteams

2475 myhexteams=$(hexteams "$grp" "$user")

2476 allhexteams=$(hexteams "$grp")

2477 if [ -n "$myhexteams" ]; then

2478 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2479 for=\"cmrmteam\">チーム属性除去</label>

2480 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2481 を除去します: `cgi_checkbox teamconfirm yes` 確認

2482 <p>この操作による通知は本人に行きません。

2483 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2484 </div>

2485 "

2486 fi

2487 b1='<label> <input type="checkbox" name="usel" value="'

2488 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2489 #b2='"> <span>' b3='</span></label>'

2490 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2491 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2492 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2493 cgi_form grpaction<<EOF \

2494 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2495 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2496 | _m4 -D_TITLE_="spaste(\`$tf')" \

2497 -D_SUBTITLE_="チェック後操作ボタン" \

2498 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2499 $layout/form+dump.m4.html

2500 <p>下でチェックした人を対象として:</p>

2501 <div class="foldtabs">

2502 `cgi_radio rm addteam 'id="cmteam"'`<label

2503 for="cmteam">同じチーム属性を付与</label>

2504 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2505 `cgi_datalist_h teams $allhexteams`

2506 </div>

2507 ${rmteammsg}

2508 `cgi_radio rm send id="sendmsg"`<label

2509 for="sendmsg">メッセージ送信</label>

2510 <div>

2511 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2512 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2513 `cgi_textarea text "" cols=40`

2514 </div>

2515 ${isowner:+$cmmsg$excmsg}

2516 `cgi_radio rm close id="x"`<label for="x">×</label>

2517 </div>

2518 <h4>$grp 参加者一覧</h4>

2519 <table class="td2r">

2520 `sq $db -header -html "$sql"`

2521 </table>

2522 `cgi_hidden grp $grid`

2523 EOF

2524 }

2525 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2526 # Create TEMPORARY VIEW

2527 query<<EOF

2528 CREATE TEMPORARY VIEW writeusers AS

2529 SELECT DISTINCT author FROM article

2530 WHERE id in (

2531 select id from article where blogid=(select id from blog where rowid=$1)

2532 );

2533 CREATE TEMPORARY VIEW movablegroups AS

2534 SELECT g.rowid growid , g.gname

2535 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2536 ON grp.gname=gm.gname -- そのユーザが属している

2537 AND user='$user') g -- グループに絞る

2538 WHERE (SELECT author FROM writeusers

2539 EXCEPT

2540 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2541 IS NULL;

2542 $2

2543 EOF

2544 }

2545 sql4readableblogs() {

2546 # Create view of blogs that can be readable to $user

2547 # Blog is readable when:

2548 # 1: blog owner is an user

2549 # 2: else, 2.1: owner-group where the $user belongs

2550 # 2.2: else, owner-group is not moderated

2551 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2552 cat<<EOF ## | tee tmp/sql.out

2553 CREATE TEMPORARY VIEW readableblogs AS

2554 SELECT blog.rowid rid, id, author

2555 FROM blog

2556 NATURAL JOIN

2557 (SELECT id,

2558 max(CASE key WHEN 'owner' THEN val END) owner,

2559 max(CASE key WHEN 'mode' THEN val END) mode

2560 FROM blog_s GROUP by id) bs

2561 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2562 THEN 1 -- blog owner is an user, READABLE

2563 WHEN (SELECT val FROM grp_s

2564 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2565 AND

2566 (SELECT user FROM grp_mem

2567 WHERE gname=bs.owner AND user='$user') IS NULL

2568 THEN 0

2569 WHEN mode = 'quiz'

2570 THEN 0 -- "quiz" mode blog cannot be searched

2571 ELSE 1

2572 END;

2573 EOF

2574 }

2575 editheading() { # $1=rowid-of-heading

2576 rowid=${1%%[!A-Z0-9a-z_]*}

2577 if [ -z "$rowid" ]; then

2578 echo "話題番号が未指定です。" | html p

2579 return

2580 fi

2581 owner=`getvalbyid blog owner $rowid`

2582 title=`getvalbyid blog title $rowid`

2583 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2584 | _m4 -D_TITLE_="修正" \

2585 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2586 -D_BLOGS_="" -D_DUMPTABLE_="" \

2587 -D_FORM_="syscmd(\`cat')" \

2588 $layout/html.m4.html $layout/form+dump.m4.html

2589 # Move to group

2590 if isuser "$owner"; then

2591 crview4article $rowid

2592 n=`query "SELECT count(*) FROM writeusers;"`

2593 ## err N=$n

2594 if [ $((n)) -gt 0 ]; then

2595 ## err ROWID=$rowid

2596 sql="SELECT growid || ':' || gname FROM movablegroups;"

2597 cat<<-EOF

2598 <div class="fold">

2599 `cgi_checkbox mv send id="mv"`<label

2600 for="mv">この話題をグループ所有に移動する</label>

2601 <div>

2602 <form action="?mvart" method="POST" enctype="multipart/form-data">

2603 移動先グループ:

2604 <select name="mv2grp">

2605 EOF

2606 query ".mode html"

2607 query<<-EOF |

2608 $sql

2609 .mode list

2610 EOF

2611 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2612 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2613 cat<<-EOF

2614 </select>

2615 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2616 そのグループに加入しているものに限られます)</p>

2617 <p>`cgi_checkbox cfm yes`<label>確認

2618 (この操作は元に戻すことができません)</label></p>

2619 `cgi_hidden blogrowid $rowid`

2620 `cgi_submit 移動`

2621 `cgi_reset Reset`

2622 </form>

2623 </div>

2624 </div>

2625 EOF

2626 fi

2627 # end of isuser "$owner"

2628 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2629 [ -n "$hexteams" ];}; then

2630 none="`echo なし|hexize`"

2631 cat<<-EOF

2632 <div class="fold">

2633 `cgi_checkbox mv2team send id="mv2team"`<label

2634 for="mv2team">この話題を以下のチームのものにする</label>

2635 <div><p>現在の所属チーム設定:

2636 `query "SELECT

2637 coalesce((SELECT val FROM blog_s

2638 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2639 AND key='team'),

2640 ':なし');"`</p>

2641 <form action="?mvart" method="POST" enctype="multipart/form-data">

2642 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2643 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2644 `cgi_hidden blogrowid $rowid`<br>

2645 `cgi_submit 移動`

2646 `cgi_reset Reset`

2647 </form></div></div>

2648 EOF

2649 fi

2650 }

2651 mvart() { # move diary to some group or team

2652 # or move blog of group to team which belong to the group

2653 blogrowid=`getpar blogrowid`

2654 cfm=`getpar cfm`

2655 ##### echo move blog:$blogrowid to $mv2grp | html p

2656 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2657 . ./s4-blog.sh

2658 if [ -z "$blogrowid" ]; then

2659 echo "無効な指定です(mvart)。" | html p

2660 return

2661 elif [ x"$cfm" != x"yes" ]; then

2662 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2663 elif { mv2grp=`getpar mv2grp`

2664 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2665 [ -n "$mv2grp" ]; }; then

2666 crview4article $blogrowid

2667 ########## TRANSACTION BEGIN

2668 query "BEGIN;"

2669 n=`query "SELECT count(*) FROM writeusers;"`

2670 ## err Nwriteuser=$n

2671 if [ $((n)) -gt 0 ]; then

2672 query<<-EOF

2673 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2674 WHERE key='owner'

2675 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2676 AND $mv2grp IN (SELECT growid FROM movablegroups);

2677 EOF

2678 fi

2679 query "END;"

2680 ########## TRANSACTION END

2681 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2682 [ -n "$mv2team" ];}; then

2683 # blog owner can move it to ANY team

2684 case "$mv2team" in

2685 'なし')

2686 cat<<-EOF

2687 DELETE FROM blog_s

2688 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2689 AND key='team';

2690 EOF

2691 ;;

2692 "") ;;

2693 *)cat<<-EOF

2694 BEGIN;

2695 REPLACE INTO blog_s(id, key, val)

2696 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2697 'team', '$mv2team');

2698 REPLACE INTO blog_s(id, key, val)

2699 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2700 'notify', 'all'); -- Change notify to all

2701 END;

2702 EOF

2703 esac | query

2704 fi

2705 blog_reply $blogrowid

2706 echo yes | html p

2707 }

2708 editart() { # $1=article-rowid $2=blogrowid

2709 rowid=${1%%[!A-Z0-9a-z_]*}

2710 blogrowid=${2%%[!A-Z0-9a-z_]*}

2711 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2712 echo "表示する記事番号が未指定です。" | html p

2713 return

2714 fi

2715 owner=`getvalbyid blog owner $blogrowid`

2716 title=`getvalbyid blog title $blogrowid`

2717 author=`getvalbyid article author $rowid`

2718 ## err EDITart: owner=$owner, author=$author

2719 if isgrpowner "$user" "$owner"; then

2720 : EDIT OK

2721 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2722 echo "本人か所有者しか編集できません." | html p

2723 return

2724 fi

2725 aid=`query "select id from article where rowid=$rowid;"`

2726 tmpout=$tmpd/editart.$$.out

2727 GF_ACTION="?replyblog+$blogrowid#$aid" \

2728 edittable $formdir/article.def article $rowid \

2729 > $tmpout

2730 rm -f /tmp/editart.out

2731 # Cannot use pipelining to m4 with genform() because of stdin stack

2732 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2733 -D_FORM_="syscmd(cat $tmpout)" \

2734 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2735 -D_BLOGS_= -D_DUMPTABLE_= \

2736 $layout/html.m4.html $layout/form+dump.m4.html

2737 }

2738 send2mem() {

2739 rowid=`getpar grp`

2740 rowid=${rowid%%[!0-9]*} # Cleaning

2741 if [ -z "$rowid" ]; then

2742 echo "グループが未指定です。" | html p

2743 return

2744 fi

2745 message=`getpar message`

2746 if [ -z "$message" ]; then

2747 echo "文章を入れてください。" | html p

2748 return

2749 fi

2750 grp=`getgroupbyid $rowid`

2751 members=`collectemail $grp`

2752 # smail rcpt subj (file)

2753 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$admin>" \

2754 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2755 $urlbase?grp+$rowid

2756 グループ $grp に所属する

2757 `gecos $user` さんよりメッセージ:

2758

2759 $message

2760 EOF

2761 cat<<EOF

2762 <p>以下のユーザに送信しました。</p>

2763 <pre>

2764 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

2765 </pre>

2766 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2767 EOF

2768 }

2769 joingrpadmit() {

2770 # $1=yes/no $2=session-key

2771 if [ -z "$2" ]; then

2772 echo "bye bye" | html p; return

2773 fi

2774 t_usr=`session=$2 getpar user`

2775 t_grp=`session=$2 getpar group`

2776 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2777 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2778 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2779 echo "無効な加入依頼です。" | html p

2780 echo "有効期限が切れたか、

2781 他の管理者がいる場合は処理済みの可能性があります。" | html p

2782 return

2783 fi

2784 if ! isgrpowner "$user" "$t_grp"; then

2785 echo "グループ管理者のみの機能です。" | html p; return

2786 fi

2787 case $1 in

2788 yes) joingrp "$t_grp" "$t_usr" yes ;;

2789 no) joingrp "$t_grp" "$t_usr" no ;;

2790 *)

2791 echo "無効な指定です($1)。" | html p

2792 return ;;

2793 esac

2794 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

2795 rcpts="`getgroupadminmails $t_grp` $user"

2796 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2797 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2798 に

2799 $t_usr

2800 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2801 しました。"

2802 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2803 query "delete from session where id='$2';"

2804 echo "$body" | html p

2805 }

2806

2807 joingrprequest() {

2808 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2809 jss="joingrp-`date +%s`-`genrandom 12`"

2810 addsession $jss +${memoplimitdays}days

2811 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

2812 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

2813 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2814 $url

2815 $user さんから

2816 グループ $1

2817 に加入依頼がありました。

2818

2819 承認する:

2820 $urlbase?joingrpadmit+yes+$jss

2821

2822 白紙に戻す:

2823 $urlbase?joingrpadmit+no+$jss

2824 EOF

2825 echo "管理者に加入依頼を出しました。

2826 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2827 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2828 }

2829 joingrp() {

2830 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2831 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2832 if isgrpowner "$user" "$1"; then

2833 isowner="yes"

2834 elif [ -n "$5" ]; then

2835 isowner="yes"

2836 else

2837 isowner=""

2838 fi

2839 ## err jg:isgrpowner: isowner="$isowner"

2840 if [ -n "$isowner" ]; then

2841 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2842 elif [ x"$2" != x"$user" ]; then # if user is not login user

2843 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2844 return

2845 elif [ x"$3" = x"no" ]; then

2846 : # Do not pursue those who leave

2847 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2848 : # Member can change own email address for the joining moderated group

2849 else # adding user is $user itself

2850 case `getgroupattr $1 regmode` in

2851 moderated)

2852 joingrprequest "$@" # Request only

2853 return

2854 ;;

2855 *)

2856 ;;

2857 esac

2858 fi

2859 qgname=`sqlquote "$1"`

2860 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

2861 cond="where gname=$qgname and user='$2'"

2862 if [ x"$3" = x"yes" ]; then

2863 query "replace into grp_mem values($qgname, '$2');"

2864 # Notify joingrp to admin

2865 action="に加入しました。"

2866 if [ -n "$4" ]; then

2867 if msg=`emaildomaincheck "$4"`; then

2868 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2869 'string', '$4', NULL);"

2870 else

2871 echo $msg

2872 fi

2873 else

2874 query "delete from grp_mem_s $cond and key='email';"

2875 fi

2876 if [ -n "$5" ]; then # as ADMIN

2877 # Coming here means newly created group

2878 sql="select case\

2879 when (select count(*) from grp_mem where gname=$qgname)=1\

2880 then (select user from grp_mem\

2881 where gname=$qgname and user='$user')\

2882 else '' end; "

2883 err NewGrpChk: $sql

2884 if [ -n "`query \"$sql\"`" ]; then

2885 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

2886 query "replace into grp_adm values($qgname, '$user');"

2887 fi

2888 fi

2889 else

2890 query "delete from grp_mem $cond;

2891 delete from grp_mem_s $cond;

2892 delete from grp_mem_m $cond;"

2893 action="から脱退しました。"

2894 fi

2895 smail "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

2896 $url?grp+$grid

2897 $user (`gecos $user`)さんが

2898 グループ $1

2899 $action

2900 EOF

2901 }

2902 grp_add_team() (

2903 # $1=grp-rowid $2=team $3...=user-rowid(s)

2904 grp=`getgroupbyid $1`

2905 team=$2; shift; shift

2906 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2907 { echo "BEGIN;"

2908 for user; do

2909 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2910 '$grp',\

2911 (SELECT name FROM user WHERE rowid=$user),\

2912 'team', 'string', '$team');"

2913 done

2914 echo "END;"

2915 } | query

2916 )

2917 grp_rm_team() (

2918 # $1=grp-rowid $2=team $3...=user-rowid(s)

2919 grid=$1

2920 qgrp=$(sqlquote "`getgroupbyid $grid`")

2921 team=$2; shift; shift

2922 [ -z "$grid" -o -z "$team" ] && return

2923 { echo "BEGIN;"

2924 for user; do

2925 echo "DELETE FROM grp_mem_m\

2926 WHERE gname=$qgrp \

2927 AND user=(SELECT name FROM user WHERE rowid=$user)\

2928 AND key='team' AND val='$team';"

2929 done

2930 cat<<-EOF

2931 DELETE FROM blog_s

2932 WHERE rowid=(

2933 SELECT rowid

2934 FROM blog_s a

2935 WHERE key='team'

2936 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2937 AND NOT EXISTS (SELECT * FROM grp_mem_m

2938 WHERE key='team' AND val=a.val -- a.val=team

2939 AND gname = (SELECT val FROM blog_s b

2940 WHERE a.id=b.id AND key='owner')

2941 ));

2942 EOF

2943

2944 echo "END;"

2945 } | query

2946 )

2947 grp_reg_adm() {

2948 # $1=grp-rowid $2...=user-rowid

2949 grid=$1

2950 grp=`getgroupbyid "$1"`

2951 if [ -z "$grp" ]; then

2952 echo "無効なグループIDです" | html p; return

2953 fi

2954 if ! isgrpowner "$user" "$grp"; then

2955 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2956 fi

2957 shift

2958 for urid; do

2959 newadm=`query "select name from user where rowid=$urid;"`

2960 if [ -z "$newadm" ]; then

2961 echo "指定ユーザIDがおかしいようです。" | html p; return

2962 fi

2963 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

2964 err ismember $newadm $grp

2965 if ismember $newadm "$grp"; then

2966 # OK, go ahead

2967 getgname="(select gname from grp where rowid=$grid)"

2968 query "replace into grp_adm values($getgname, '$newadm');"

2969 # confirm insertion

2970 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2971 if [ -n "`query \"$sql;\"`" ]; then

2972 echo "追加完了: $newadm" | html p

2973 else

2974 echo "追加失敗($1 $urid)" | html p

2975 fi

2976 fi

2977 showgroup $grid

2978 done

2979 }

2980 dt_rowhack() {

2981 # From: <TR>

2982 # ....

2983 # <TD>rowclass=foo</TD>

2984 # </TR>

2985 # To: <TR class="foo">....<TD>foo</TD></TR>

2986 sed -e '

2987 /^<TR>/ {

2988 :loop

2989 s/\n//

2990 N

2991 /<\/TR>/ {

2992 s/\n//

2993 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

2994 n

2995 }

2996 $q

2997 b loop

2998 }'

2999 }

3000 dumptable() {

3001 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3002 # textのフィールドだけ全てダンプにしたほうがいいか

3003 # $DT_VIEW sets link

3004 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3005 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3006 VIEW=${DT_VIEW-replyblog}

3007 if [ -n "$VIEW" ]; then

3008 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3009 fi

3010 # $DT_CHLD=ChildTable:BindColumn

3011 if [ -n "$DT_CHLD" ]; then

3012 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3013 cntall="(select count($_i) from $_t where $_i=a.id)"

3014 # XXX: Dirty workaround for slow subquery of acclog

3015 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3016 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

3017 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3018 and id in (select id from $_t where $_i=a.id) \

3019 and val > coalesce((select time from myacclog where \

3020 tblrowid=a.rowid),\

3021 '1970-01-01'))"

3022 cnt="$cntnew as '新着', $cntall as '総数',"

3023 dt_class=" td2r td3r dumpblogs"

3024 fi

3025 # Construct join expression

3026 eav="" scols=""

3027 pk=`gettblpkey $2`

3028 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3029 substr=${substr:-%s}

3030 for col in ${3:-`gettbl_s_cols $2`}; do

3031 valvar=val

3032 case $col in

3033 gecos) scols="$scols${scols:+, }${col#}"

3034 continue ;; # built-in column name

3035 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3036 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3037 case "$as" in

3038 *:*=*) cnd=${as#*:}

3039 h=${cnd%%=*} v=${cnd#*=}

3040 h=`sqlquotestr "$h"`

3041 v=`sqlquotestr "$v"`

3042 valvar="CASE val WHEN $h THEN $v END"

3043 as=${as%%:*} ;;

3044 esac

3045 ;;

3046 *) as=${col} ;;

3047 esac

3048 ss=`printf "$substr" "$valvar"`

3049 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3050 scols="$scols${scols:+, }b.$as"

3051 done

3052 #case author when '$user' then a.rowid else '---' end as ID,

3053 sql=${DT_SQL:-"select \

3054 a.rowid as LINK,\

3055 $cnt\

3056 $scols from $2 a left join\

3057 (select $pk,$eav,

3058 max(case key when 'owner'

3059 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

3060 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

3061 ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"

3062 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3063 <div>

3064 <div class="dumptable">

3065 <table class="b$dt_class">

3066 `sq -header -cmd ".mode $1" $db "$presql$sql"`

3067 </table>

3068 </div>

3069 </div>

3070 EOF

3071 }

3072

3073 par2table() (

3074 # copy current parameters of par into destination table

3075 # $1=definition-file

3076 # Using $user and $session

3077 # Return value:

3078 # 0: Stored successfully

3079 # 1: Insufficient fillings

3080 # 2: No permission to modify the record

3081 # 3: Invalid rowid

3082 # 4: SUCCESS to delete

3083 # 5: Stop deletion for lack of confirm check

3084 # 6: Password length too short

3085 # 7: Password mismatch

3086 # 8: Old password incorrect

3087 rowid=`getpar rowid`

3088 if [ ! -e $1 ]; then

3089 echo "テーブル定義ファイルが見付かりません" | html p

3090 exit 1

3091 fi

3092 tbl=${1%.def}

3093 tbl=${tbl##*/}

3094 if [ -n "$rowid" ]; then # Modify existing entry

3095 if [ x"$tbl" = x"user" ]; then

3096 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3097 elif [ x"$tbl" = x"grp" ]; then

3098 sql="select gname from $tbl where rowid=$rowid;"

3099 ##err p2t:grp:q $sql

3100 isgrpowner "$user" "`query $sql`" && rowowner=$user

3101 else

3102 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3103 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3104 fi

3105 ### err rowowner=$rowowner

3106 if [ x"$user" != x"$rowowner" ]; then

3107 echo "他人のレコードはいじれないの" | html p

3108 return 2

3109 elif [ -z "$rowowner" ]; then

3110 echo "指定したレコードはないみたい" | html p

3111 return 3

3112 fi

3113 rm=`getpar rm` cfm=`getpar confirm`

3114 # Editing existent entry

3115 if [ x"$rm" = x"yes" ]; then

3116 if [ x"$rm$cfm" = x"yesyes" ]; then

3117 query "delete from $tbl where rowid=$rowid;"

3118 return 4

3119 else

3120 echo "消去確認のチェックがないので消さなかったの..." | html p

3121 return 5

3122 fi

3123 fi

3124 fi

3125

3126 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3127 if [ -n "$rowid" ]; then

3128 # Update of existing record

3129 for col in `gettblcols $tbl`; do

3130 val=`getparquote $col`

3131 [ -z "$val" ] && continue

3132 ## err query "update $tbl set $col=$val where rowid=$rowid"

3133 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3134 sql="update $tbl set $col=$val where rowid=$rowid;"

3135 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3136 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3137 ## User name cannot be changed with interface provided with this

3138 ## script. But we offer the trigger to change owner user

3139 ## of blog_s table.

3140 #err "select quote($col) from $tbl where rowid=$rowid;"

3141 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3142 cat<<-EOF | query

3143 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3144 -- should use EXCLUSIVE transaction outside of this.

3145 SAVEPOINT par2table;

3146 $sql

3147 update blog_s set val=$val

3148 where key='owner' and val=$old;

3149 RELEASE SAVEPOINT par2table;

3150 EOF

3151 ## XX: DIRTY Hack Ends here

3152 ## We should keep blog's owner as a single column which has

3153 ## foreign key constraint with primary key of grp/user.

3154 else

3155 query "$sql"

3156 fi

3157 done

3158 # Then, set up $pval for further insertion of tbl_s and tbl_m

3159 for col in `gettblpkey $tbl`; do

3160 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3161 pval="$pval${pval:+, }\"$val\""

3162 done

3163 else

3164 # New entry

3165 # Generate values() for primary keys

3166 for col in `gettblpkey $tbl`; do

3167 # Genuine primary keys for _m and _s

3168 val=`getvalquote $tbl $col`

3169 [ -z "$val" ] && continue

3170 pval="$pval${pval:+, }$val"

3171 done

3172 ##err pval=$pval

3173 for col in `gettblfkey $tbl`; do

3174 # args for values() to insertion into master table

3175 val=`getvalquote $tbl $col`

3176 [ -z "$val" ] && continue

3177 formaster=$formaster"${formaster:+, }$val"

3178 done

3179 formaster="$pval${formaster:+, }$formaster"

3180 ## err formaster=$formaster

3181 if [ -z "$formaster" ]; then

3182 echo "項目を全て埋めてください" | html pre

3183 return 1

3184 fi

3185 ## err "replace into $tbl values($formaster);"

3186 query "replace into $tbl values($formaster);"

3187 ## Insertion to master table, done

3188 fi

3189

3190 for kt in s m; do

3191 tb2=${tbl}_$kt

3192 for col in `gettbl_${kt}_cols $tbl`; do

3193 ptype=`getpartype $col "limit 1"`

3194

3195 # First, check update of existing entries in _m

3196 if [ $kt = m ]; then

3197 # sessID|address.1.22|string|Somewhere-x.y.z

3198 sql=""

3199 ##err dots from query "select var from par where var like '$col.%';"

3200 for v in `query "select var from par where var like '$col.%';"`; do

3201 # v=address.1.22

3202 st_rowid=${v##*.}

3203 origcol=${v%%.*} # original column derived from

3204 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3205 ##case `getpartype $v` in

3206 ## err CASE `gettbl_coltype $tbl/$origcol` in

3207 ## err edit flag = `getpar action.$v`

3208 case `getpar action.$v` in

3209 rm)

3210 if [ x`getpar confirm.$v` = x"yes" ]; then

3211 newsql="delete from $tb2"

3212 else

3213 echo "削除確認未チェック" | html p

3214 fi ;;

3215 edit)

3216 case `gettbl_coltype $tbl/$origcol` in

3217 image|document|binary)

3218 file=$tmpd/`getparfilename $v`

3219 ## err type=file=$file

3220 [ -z "$file" ] && continue

3221 bn=`sqlquotestr "${file##*/}"`

3222 bin="X'"$(hexize "$file")"'"

3223 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3224 type=\"file:$ct\"

3225 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3226 cachedir=`getcachedir "$tbl/$rowid"`

3227 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3228 rm -rf $cachedir

3229 ;;

3230 *)

3231 newsql="update $tb2 set val=(select val from par where var \

3232 like '$col.%.$st_rowid')"

3233 ;;

3234 esac

3235 ;;

3236 *) # maybe "keep", do not modify value

3237 continue

3238 ;;

3239 esac

3240 # err newsql=$newsql

3241 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3242 done

3243

3244 if [ x"$bin" = x"NULL" ]; then

3245 ## err repl:normal sql=`echo $sql`

3246 query "$sql

3247 delete from $tb2 where type='string' and val='';"

3248 ## err repl:normal done

3249 else

3250 sqlfile="$tmpd/sqlf.$$"

3251 echo "$sql" > $sqlfile

3252 ## err repl:sqlfile=`ls -lF $sqlfile`

3253 query ".read $sqlfile"

3254 ## err repl:done

3255 fi

3256 # Rest of kt==m: set multiple mode

3257 nr=`getparcount $col`

3258 else

3259 nr=1 # for kt==s, number of records is 1

3260 fi

3261

3262 i=0

3263 while [ $i -lt $nr ]; do

3264 limit="limit 1 offset $i"

3265 i=$((i+1)) # increase beforehand against continue

3266 val=`getvalquote $tbl $col "$limit"`

3267 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3268 ## err $col=$val

3269 bin=NULL

3270 ## err partype$col=`getpartype $col "$limit"`

3271 case $ptype in

3272 file) file=$tmpd/`getparfilename $col "$limit"`

3273 ## err parfile-$col=$file

3274 [ -z "$file" ] && continue

3275 bin="X'"$(hexize "$file")"'"

3276 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3277 type=\"file:$ct\" ;;

3278 "*"*) continue ;; # foreign table

3279 *) type=\"string\" ;;

3280 esac

3281 case `gettbl_coltype $tbl/$col` in

3282 password) # special care for password

3283 # name={password,pswd1,pswd2}

3284 p1=`getpar pswd1 "$limit"`

3285 if [ -z "$p1" ]; then

3286 continue # SKIP password setting, if p1 is empty

3287 else

3288 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3289 ## err pswd=$pswd

3290 if pwcheck "$pswd"; then

3291 if [ x"$p1" = x"$p2" ]; then

3292 case "$p1" in

3293 ??????????*) ;;

3294 *) echo "パスワードは10字以上にしてください。" | html p

3295 return 6;;

3296 esac

3297 val="\"`echo $p1|mypwhash`\""

3298 else

3299 echo "2つの新パスワード不一致" | html p

3300 return 7

3301 fi

3302 else

3303 echo "旧パスワード違います" | html p

3304 return 8

3305 fi

3306 fi

3307 ;;

3308 esac

3309 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3310 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3311 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3312 if [ x"$bin" = x"NULL" ]; then

3313 ## err Normal-query: `echo $sql`

3314 query "$sql"

3315 else

3316 sqlfile="$tmpd/query.$$"

3317 echo "$sql" > $sqlfile

3318 ## err sqlfile=`ls -lF $sqlfile`

3319 query ".read $sqlfile"

3320 fi

3321 ## err p2t done

3322 done

3323 done

3324 done

3325 return 0

3326 ##err donee

3327 )

3328 genform() {

3329 # $1 = form definition file

3330 # $2, $3 (optional)= table name and ROWID

3331 # If $GF_VIEWONLY set and nonNull, output values without form

3332 # If $GF_ARGS set, use it as content-strings in the form

3333 # If $GF_OWNER set, use it as value of name="owner"

3334 # If $GF_STAGE set, use it as value of name="stage"

3335 forms="" hiddens="" rowid=$3

3336 if [ ! -e "$1" ]; then

3337 echo "そのようなデータベースはないようです($2)。" | html p

3338 return

3339 elif [ -n "$2" ]; then

3340 rec=`query "select * from $2 where rowid='$rowid';"`

3341 if [ -z "$rec" ]; then

3342 pk=`gettblpkey $2`

3343 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3344 rec=`query "select rowid from $2 where $pk='$rowid';"`

3345 rowid=$rec

3346 rec=$3

3347 fi

3348 if [ -z "$rec" ]; then

3349 echo "そんなレコードはないみたいね..." | html p

3350 return

3351 fi

3352 fi

3353 if [ -z "$GF_VIEWONLY" ]; then

3354 rm='<input id="rm" name="rm" type="checkbox"

3355 value="yes"><label for="rm">このエントリの削除</label>

3356 <span>ほんとうに消しますよ(確認)!

3357 <input name="confirm" type=checkbox value="yes">はい</span>'

3358 fi

3359 # Image Cache dir

3360 ## err genform: getcache=$2/$rowid

3361 td=`getcachedir "$2/$rowid"`

3362 while IFS=: read prompt name keytype type args; do

3363 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3364 sp="${args:+ }"

3365 form="" val=""

3366 if [ -n "$rowid" ]; then

3367 # err genform2a: Seeking for "$2.$name, type=$type"

3368 rawval=`getvalbyid $2 $name $rowid $td`

3369 val=`echo "$rawval"|htmlescape`

3370 ## err genform3a: getvalbyid $2 $name $rowid $td

3371 ## err genform3b: val="[$val]" type="$type"

3372 fi

3373 if [ -n "$GF_VIEWONLY" ]; then

3374 is_hidden "$2" "$name" && continue

3375 fi

3376 case "$type" in

3377 text*)

3378 cgiform=cgi_multi_$type

3379 if [ -s $td/$name.count -a -n "$val" ]; then

3380 form=`$cgiform $name $td`

3381 val=$(echo "$val"|

3382 while read fn; do

3383 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3384 </td></tr>$nl"

3385 done)

3386 val="<table>$nl$val$nl</table>"

3387 else

3388 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3389 form=`cgi_$type $name "$rawval" "$args"`

3390 fi

3391 ;;

3392 [Rr][Aa][Dd][Ii][Oo])

3393 fh="<label><input type=\"radio\" name=\"$name\""

3394 form="`echo $args|sed -e \

3395 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3396 ;;

3397 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3398 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3399 ;;

3400 [Ss][Ee][Ll][Ee][Cc][Tt])

3401 fh="<select name=\"$name\">$nl"

3402 form=$(for l in $args; do

3403 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3404 done)

3405 if [ -n "$val" ]; then

3406 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3407 fi

3408 form="$fh$form</select>"

3409 ;;

3410 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3411 if [ -s $td/$name.count ]; then

3412 form=`cgi_multi_file $name $td "$args"`

3413 if [ -n "$val" ]; then

3414 hrfb="$myname?showattc+$2_m"

3415 val=$(echo "$rawval" \

3416 | while read fn; do

3417 data=`percenthex "$td/$fn"`

3418 #ct=`cat $td/$fn.content-type`

3419 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3420 ri=`cat "$td/$fn.rowid"`

3421 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3422 #imgsrc="<img src=\"data:$ct,$data\">"

3423 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3424 iconhref "$td/$fn" "$hrfb+$ri" ""

3425 done)

3426 fi

3427 else

3428 form="<input type=\"file\" name=\"$name\" $args>"

3429 if [ -n "$val" ]; then

3430 imgs=$(echo "$rawval"\

3431 |while read fn;do

3432 data=`percenthex "$td/$fn"`

3433 echo "<img src=\"data:image/png,$data\">$fn<br>"

3434 done)

3435 form=$form"<br>$imgs"

3436 val=$imgs # 2015-06-15

3437 else

3438 form="<input type=\"file\" name=\"$name\" $args>"

3439 fi

3440 fi

3441 ;;

3442 [Hh][Ii][Dd][Dd][Ee][Nn])

3443 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3444 args="value=\"$GF_STAGE\""

3445 fi

3446 form="<input type=\"hidden\" name=\"$name\" $args>"

3447 prompt='' # Remove prompt

3448 ;;

3449 [Aa][Uu][Tt][Hh][Oo][Rr])

3450 [ -n "$GF_VIEWONLY" ] && continue

3451 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3452 prompt="" ;;

3453 [Oo][Ww][Nn][Ee][Rr])

3454 [ -n "$GF_VIEWONLY" ] && continue

3455 val=${GF_OWNER:-$val}

3456 val=${val:-$user}

3457 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3458 prompt="" ;;

3459 [Uu][Ss][Ee][Rr])

3460 # XXX: is null $user ok?

3461 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3462 [ -n "$GF_VIEWONLY" ] && continue

3463 form="$user"

3464 ;;

3465 [Pp]assword)

3466 [ -n "$GF_VIEWONLY" ] && continue

3467 form="`cgi_passwd`"

3468 val=""

3469 ;;

3470 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3471 [ -n "$GF_VIEWONLY" ] && continue

3472 if [ -z "$rowid" ]; then

3473 val=`genserial`

3474 fi

3475 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3476 prompt="" ;;

3477 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3478 prompt=""

3479 ;;

3480 parent|path|blog*)

3481 prompt=""

3482 ;;

3483 "*"*)

3484 tail=$tail"``"

3485 continue ;;

3486 esac

3487 if [ -n "$prompt" ]; then

3488 if [ -n "${GF_VIEWONLY}" ]; then

3489 form=$val

3490 else

3491 :

3492 fi

3493 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3494 else

3495 hiddens=$hiddens$nl"$form"

3496 fi

3497 done < $1

3498 # enctype="multipart/form-data"

3499 cat<<EOF

3500 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3501 ${rowid:+$rm}

3502 <table class="b $2">

3503 $forms

3504 </table>$hiddens

3505 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3506 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3507 EOF

3508 if [ -z $GF_VIEWONLY ]; then

3509 cat<<EOF

3510 <input type="submit" name="sub" value="OK">

3511 <input type="reset" name="res" value="Reset">

3512 EOF

3513 fi

3514 cat<<EOF

3515 $GF_ARGS</form>

3516 $tail

3517 EOF

3518 }

3519 edittable() {

3520 # $1=form-def $2=table $3 rowid

3521 genform "$@"

3522 }

3523 viewtable() {

3524 GF_VIEWONLY=1 genform "$@"

3525 }

3526 showattc() {

3527 # $1=table_m $2=rowid &optional $3=RawFlag

3528 ## err \$1=$1 \$2=$2 \$3=$3

3529 if ! isfilereadable $user $1 $2; then

3530 contenttype; echo

3531 echo "このファイルは管理者のみしか見られません" | html p

3532 putfooter; exit

3533 fi

3534 idir=`umask 002; mktempd` || exit 1

3535 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3536 bin=$idir/$myname-$$.bin

3537 sql="select quote(bin) from $1 where rowid='$2';"

3538 ## err showattc: sql: $sql

3539 sq $db "$sql" | unhexize > $bin

3540 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3541 type=${tv%//*} fn=${tv#*//}

3542 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3543 ct=${type#file:}

3544 case $ct in # all text/* changed to text/plain

3545 text/*)

3546 charset=`nkf -g $bin|cut -d' ' -f1`

3547 case $charset in

3548 ASCII*) charset="" ;;

3549 esac

3550 if [ -z "$3" ]; then

3551 ct="text/html${charset:+; charset=$charset}"

3552 link="?showattc+$1+$2+raw"

3553 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3554 | sed 's,^,<span></span>,' \

3555 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3556 -D_LINK_="$link" \

3557 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3558 exit $?

3559 fi

3560 ct="text/plain${charset:+; charset=$charset}"

3561 ;;

3562 esac

3563 contenttype "$ct"

3564 echo "Content-Disposition: filename=\"$fn\""

3565 echo "Content-Length: " `cat $bin | wc -c`; echo

3566 #echo "Content-Type: " ${type#file:}; echo

3567 cat $bin

3568 }

3569 #

3570 # Some default stupid handler on CGI values

3571 #

3572 default_storedb() {

3573 # ARG: $1=table-def-file

3574 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3575 tbl=`basename $1`

3576 tbl=${tbl%.def}

3577 cols="`grep :text $1|cut -d: -f2`"

3578 col=`echo "$cols"|head -1`

3579 vcol=`getpar $col`

3580 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3581 if [ -n "$vcol" ]; then

3582 par2table $1

3583 else

3584 return 2 # No insertion occurred

3585 fi

3586 }

3587

3588 default_view() { # $1=def-file

3589 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3590 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3591 default_storedb "$@"

3592 query "select rowid from $tbl order by rowid desc;" \

3593 | while read rowid; do

3594 viewtable $1 $tbl $rowid

3595 done | _m4 -D_TITLE_="$tbl" \

3596 -D_FORM_="`genform $1`" \

3597 -D_DUMPTABLE_="syscmd(cat)" \

3598 $layout/html.m4.html $layout/form+dump.m4.html

3599 }

3600 default_viewtext() { # $1=def-file

3601 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3602 default_storedb "$@"

3603 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3604 | _m4 -D_TITLE_="$tbl" \

3605 -D_FORM_="`genform $1`" \

3606 -D_DUMPTABLE_="syscmd(cat)" \

3607 $layout/html.m4.html $layout/form+dump.m4.html

3608 }

3609 default_smail() {

3610 default_storedb "$@"

3611 if [ $? -eq 2 ]; then

3612 _m4 -D_TITLE_="入力" \

3613 -D_FORM_="`genform $1`" \

3614 -D_DUMPTABLE_="" \

3615 $layout/html.m4.html $layout/form+dump.m4.html

3616 return

3617 fi

3618 cond=""

3619 for pk in `gettblpkey $tbl`; do

3620 pv=$(sqlquote "$(getpar $pk)")

3621 cond="$cond${cond:+ and }$pk=$pv"

3622 done

3623 sql="select rowid from $tbl where $cond;"

3624 rowid=`query "$sql"`

3625 ## err smail1 - "$sql" "-> rowid=$rowid"

3626

3627 while IFS=: read prompt name keytype type args; do # Read from $1

3628 val=`getpar $name`

3629 if [ -n "$val" ]; then

3630 text="$text

3631 $prompt

3632 $name=$val

3633 ---------------------------------------------------------"

3634 fi

3635 case "$type" in

3636 image|document|file)

3637 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3638 fns=$(echo "$fn"|while read fn; do

3639 err mv $tmpd/$fn.orig $tmpd/$fn

3640 mv $tmpd/$fn.orig $tmpd/$fn

3641 rm $tmpd/$fn.rowid # Remove cache flag

3642 ## err "`ls $tmpd/$fn`"

3643 echo $fn

3644 done)

3645 files="$files $fns"

3646 ;;

3647 esac

3648 done < $1

3649 ## err FILES=$files "`ls -lF $tmpd`"

3650 subj="from ${REMOTE_ADDR}"

3651 (echo "$url"

3652 echo "への書き込みがありました。"

3653 echo "------"

3654 echo "$text"

3655 ) | (cd $tmpd &&

3656 err LS="`ls -lF`" &&

3657 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3658 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3659 echo "以下の内容で送信しました。" | html p

3660 viewtable $1 $tbl \

3661 `query "select rowid from $tbl order by rowid desc limit 1;"`

3662 echo "戻る" | html a "href=\"?\""

3663 }