s4: c3bf12c5dba6 s4-funcs.sh

s4

view s4-funcs.sh @ 429:c3bf12c5dba6

Check replymark even if the author is the owner

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sat, 06 May 2017 18:03:39 +0859
parents	b3b33923638c
children	703346e6e7de

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|\[\[$$_hrefptn$\|$.*$\]\]|<a href=\"\1\">\2</a>|g" \

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

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

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

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

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

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

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

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

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

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

480 }

481 minitbl() {

482 sed -n '

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

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

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

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

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

488 H; # Concat this line to HoldSpace

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

490 $ b cont

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

492 }

493 :cont

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

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

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

497 # . of ^. is workaround for FreeBSD sed

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

499 p; # Print whole "table" element

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

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

502 }

503 x; # Back to the newest line

504 p; # Print rest'

505 }

506 acclog() (

507 # $1=table, $2=rowid

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

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

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

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

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

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

514 fi

515 )

516 gecos() (

517 u=`sqlquote ${1:-$user}`

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

519 )

520 setpar() {

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

522 }

523 replpar() {

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

525 }

526 getpar() {

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

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

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

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

531 fi

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

533 case "$var" in

534 owner)

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

536 echo $user; return

537 elif ismember $user $val; then

538 echo $val; return

539 fi ;;

540 esac

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

542 echo "$val"

543 }

544

545 getpartype() {

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

547 }

548 getparcount() {

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

550 }

551 getparfilename() {

552 # null if type of $1 is not file

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

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

555 }

556 sqlquote() {

557 (v="$1"

558 case "$v" in

559 "") return ;; # null

560 "X'"*) # quoted hex string

561 echo $1 ;;

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

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

564 echo "\"$v\""

565 return ;;

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

567 echo "\"$v\""

568 return ;;

569 *)

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

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

572 else

573 echo "\"$v\""

574 fi ;;

575 esac)

576 }

577 sqlquotestr() (

578 case "$1" in

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

580 echo "'$v'" ;;

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

582 esac

583 )

584 mktempd() {

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

586 }

587 getcachedir() { # $1=maintable

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

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

590 else

591 echo $tmpd/$thumbxy

592 fi

593 }

594 getval() {

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

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

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

598 echo "$user" ;;

599 stamp|datetime)

600 date "+%F %T" ;;

601 serial)

602 (s=`getpar $2`

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

604 *)

605 getpar "$2" "$3";;

606 esac

607 }

608

609 getvalquote() {

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

611 (v=`getval "$@"`

612 case "$v" in

613 "") echo NULL ;;

614 *) sqlquote "$v" ;;

615 esac)

616 }

617 getparquote() {

618 sqlquote `getpar $1`

619 }

620 getbinbyid() {

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

622

623 }

624 getvalbyid() {

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

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

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

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

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

630

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

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

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

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

635 return

636 fi

637 done

638 rowid=$3

639 pk=`gettblpkey $1`

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

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

642 td=${4:-$tmpd}

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

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

645 for kt in s m; do

646 t=${1}_$kt

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

648 vcount=1 # count(val)

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

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

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

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

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

654 if [ $kt = m ]; then

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

656 # Reset val to store filenames if type is string

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

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

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

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

661 i=0

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

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

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

665 i=$((i+1))

666 fn=$c.$i

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

668 case $type in

669 file:*)

670 #file=$td/$val

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

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

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

674 # FOR SPEED: Skip file generation if imgcache exists

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

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

677 && continue

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

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

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

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

682 select rowid from $cond $slice;

683 .output '$td/$fn'

684 select val from $cond $slice;

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

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

687 .output stdout

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

689 EOF

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

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

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

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

694 ls -lh "$file" |

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

696 case "$type" in

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

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

699 *|*:[Aa]pplication*)

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

701 png:- > "$file"

702 ;;

703 esac

704 ;;

705 *)

706 sq $db<<EOF

707 .output $td/$fn.rowid

708 select rowid from $cond $slice;

709 .output $td/$fn

710 select val from $cond $slice;

711 EOF

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

713 ;;

714 esac

715 done

716 else

717 rm -f $td/$c.count

718 case $type in

719 file:*)

720 echo "$val" \

721 | while read fn; do

722 file=$td/$fn

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

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

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

726 | unhexize > "$file"

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

728 case $type in

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

730 *:[Aa]pplication*)

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

732 png:- > $file ;;

733 esac

734 fi

735 done

736 ;;

737 esac

738 fi

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

740 return

741 fi

742 done

743 done)

744 }

745 getvalbypkey() (

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

747 pk=`gettblpkey $1`

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

749 getvalbyid "$1" "$2" $rowid $4

750 )

751 getvalbycond() {

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

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

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

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

756 getvalbyid "$1" "$2" $rowid "$4"

757 fi

758 }

759 getpwfield() {

760 # getpwfield user column

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

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

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

764 echo "$val"

765 return 0

766 else

767 return 1

768 fi

769 }

770 encode() {

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

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

773 sha1=sha1

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

775 sha1=sha1sum

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

777 sha1=gsha1sum

778 fi

779 fi

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

781 }

782 enjpeg() {

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

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

785 cjpeg="cjpeg"

786 else

787 cjpeg="convert - jpeg:-"

788 fi

789 fi

790 $cjpeg "$@"

791 }

792 mycrypt() (

793 key=$1 salt=$2

794 err \$2=$2

795 case $2 in

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

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

798 esac

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

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

801 )

802 hexize() {

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

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

805 hexize="xxd -p"

806 else

807 hexize_hd() {

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

809 }

810 hexize="hexize_hd"

811 fi

812 fi

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

814 }

815 unhexize() {

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

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

818 unhex="xxd -p -r"

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

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

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

822 EOF

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

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

825 # world writable...:)

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

827 fi

828 fi

829 cat "$@" | $unhex

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

831 }

832 percenthex() {

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

834 }

835 htmlescape() {

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

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

838 }

839 enascii() {

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

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

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

843 else

844 enascii_now=`date +%FT%T`

845 enascii_sed() {

846 nkf -Z0Z1Z2 \

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

848 }

849 enascii="enascii_sed"

850 fi

851 fi

852 cat "$@" | $enascii

853 }

854 size_h() {

855 i="$1" oi=$1

856 set -- B B KB MB GB TB

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

858 oi=$i

859 i=$((i/1024))

860 shift

861 done

862 echo ${oi}$1

863 }

864 gettblconf() {

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

866 ## tconfs=`sq $db \

867 tconfs=`query \

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

869 fi

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

871 }

872 gettblkeys() {

873 # $1=tbl

874 gettblconf

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

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

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

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

879 case $type in

880 *p*)

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

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

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

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

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

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

887 esac

888 case $type in

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

890 esac

891 done

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

893 }

894 gettblpkey() {

895 # $1=tbl

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

897 }

898 gettblfkey() {

899 (x=`gettblkeys $1`

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

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

902 }

903 gettblcols() {

904 (x=`gettblkeys $1`

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

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

907 }

908 gettbl_s_cols() {

909 (x=`gettblkeys $1`

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

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

912 }

913 gettbl_m_cols() {

914 (x=`gettblkeys $1`

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

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

917 }

918 gettbl_h_cols() {

919 (x=`gettblkeys $1`

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

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

922 }

923 gettbl_coltype() (

924 gettblconf

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

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

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

928 )

929 is_hidden() {

930 # $1=Tbl $2=col

931 gettblconf

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

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

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

935 case $x in

936 *h*) return 0 ;;

937 *) return 1 ;;

938 esac

939 }

940

941 dbsetbyid() {

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

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

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

945 quotedp=$(sqlquotestr "$p")

946 unset primary update

947 gettblconf

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

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

950 #err conf=$conf

951 case ${conf#*=} in

952 p*) primary=1 ;;

953 f*) update=1 ;;

954 u*) ;;

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

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

957 esac

958 #err t=$t

959 type=string fn=""

960 case $conf in

961 */password)

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

963 ;;

964 */image*|*/document*)

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

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

967 ;;

968 esac

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

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

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

972 if [ "$primary" ]; then

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

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

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

976 elif [ "$update" ]; then

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

978 else

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

980 fi

981 )

982 }

983 expire() (

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

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

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

987 )

988 addsession() {

989 # expireをセット

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

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

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

993 # Remove old session parameters

994 now=`expire now`

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

996 }

997 gencookie() (

998 for kv; do

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

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

1001 done

1002 )

1003 contenttype() {

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

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

1006 }

1007 putheader() {

1008

1009 }

1010 putfooter() {

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

1012 }

1013 getcookie() (

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

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

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

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

1018 done

1019 )

1020 genrandom() {

1021 # $1=columns (default: 10)

1022 dd if=/dev/urandom count=1 2>/dev/null|nkf -MB|fold -w${1:-10}|sed -n 10p

1023 }

1024 genserial() {

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

1026 }

1027 smail() {

1028 # smail rcpts subj (file)

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

1030 # $MAIL_FROM <- From: header value

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

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

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

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

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

1036 }

1037 setviastring() {

1038 table=$1

1039 oifs="$IFS"

1040 IFS="&"

1041 for us in $2; do

1042 k=${us%%=*}

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

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

1045 #echo $k=$v

1046 done

1047 IFS="$oifs"

1048 }

1049 checkdomain() (

1050 # Check the validity of domain by referring DNS

1051 item=$1

1052 err checkdomain $1

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

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

1055 )

1056 pwcheck() {

1057 # $1=passwd

1058 dbpswd=`getpwfield $user pswd`

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

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

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

1062 }

1063 mypwhash() {

1064 mycrypt `cat` `genrandom 5`

1065 }

1066 wasureta() {

1067 user=$1

1068 if ! checkdomain $user; then

1069 contenttype; echo

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

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

1072 putfooter

1073 exit 0

1074 fi

1075 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1077 encpswd=`echo $newpswd|mypwhash`

1078 dbsetbyid user $user pswd "$encpswd"

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

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

1081 $msgdir/mail-newaccount.m4 \

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

1083 | smail $user "New Account"

1084 }

1085 checkauth() {

1086 user=`getpar user`

1087 skc=`getpar skey` # from cookie

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

1089 skey="`getpwfield $user skey`"

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

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

1092 return 0

1093 fi

1094 fi

1095 pswd=`getpar pswd`

1096 quser=`sqlquotestr "$user"`

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

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

1099 return 1

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

1101 wasureta $user

1102 return 1 # wasureta error

1103 fi

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

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

1106 if pwcheck "$pswd"; then

1107 newsession=`genrandom 50`

1108 dbsetbyid user $user skey "$newsession"

1109 gencookie "user=$user" "skey=$newsession"

1110 return 0

1111 fi

1112 return 2 # Password mismatch

1113 }

1114 showlogin() {

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

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

1117 $layout/login.m4.html

1118 exit 0

1119 }

1120 dologin() {

1121 checkauth

1122 st=$?

1123 if [ $st != 0 ]; then

1124 contenttype; echo

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

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

1127 showlogin # and EXIT

1128 fi

1129 }

1130

1131 # Do instant jobs here

1132 dbsetup

1133 trap cleanup INT HUP EXIT TERM PIPE

1134 # trap cleanup INT HUP

1135

1136 err() {

1137 echo "$@" 1>&3

1138 }

1139

1140 cgiinit() {

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

1142 tmpf=tmp/stream

1143 tmpd=`tmpd=$tmpdir mktempd`

1144 tmpfiles=$tmpfiles" $tmpd"

1145 addsession $session

1146 getcookie

1147 case "$REQUEST_METHOD" in

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

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

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

1151 # cat > $tmpf # too much?

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

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

1154 s="`cat tmp/stream`"

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

1156 ;;

1157 esac

1158 case "$CONTENT_TYPE" in

1159 *boundary*)

1160 bndry=${CONTENT_TYPE#*boundary=}

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

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

1163 do

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

1165 #echo u=$us

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

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

1168 # err k=$k v=$v

1169 case "$k" in

1170 *:filename)

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

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

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

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

1175 :

1176 fi

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

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

1179 [Ii]mage/x-xcf)

1180 bzip2 "$tmpd/$v"

1181 v=${v}.bz2

1182 ;;

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

1184 [Ii]mage/*)

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

1186 ;;

1187 esac

1188 ;;

1189 *)

1190 type='string'

1191 ;;

1192 esac

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

1194 setpar "$k" "$type" "$v"

1195 done

1196 ;;

1197 *)

1198 setviastring par "$s"

1199 ;;

1200 esac

1201 }

1202 email4group() {

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

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

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

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

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

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

1209 and s.key='email'

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

1211 query "$sql"

1212 }

1213 email4groupbyuid() {

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

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

1216 uids=`echo "$@"`

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

1218 sql="WITH

1219 grpemails AS (

1220 SELECT gname, user, val email

1221 FROM grp_mem NATURAL JOIN grp_mem_s

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

1223 useremails AS (

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

1225 FROM user

1226 LEFT JOIN user_s

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

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

1229 FROM useremails u LEFT JOIN grpemails g

1230 ON u.name=g.user

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

1232 ## err email4gByid `echo $sql`

1233 query "$sql"

1234 }

1235 collectemail() (

1236 # Collect email addresses for group $1

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

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

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

1240 for e; do

1241 if isuser "$e"; then

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

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

1244 else

1245 qgrp=`sqlquote "$e"`

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

1247 gmem="grp_mem"

1248 else

1249 tm=`sqlquote "$TEAM"`

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

1251 fi

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

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

1254 $gmem g left join grp_mem_s s

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

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

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

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

1259 query "$sql"

1260 fi

1261 done

1262 )

1263 sendinvitation() (

1264 # $1=email

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

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

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

1268 gecos=`gecos`

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

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

1271 _m4 -D_URL_="$urlbase" \

1272 -D_USER_="$name" \

1273 -D_EMAIL_="$1" \

1274 -D_REGIST_="$regist" \

1275 -D_ADMIN_="$admin" \

1276 $msgdir/mail-invite.m4 \

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

1278 return 0

1279 )

1280 emaildomaincheck() {

1281 case "$1" in

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

1283 *@*)

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

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

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

1287 return 2

1288 fi

1289 return 0

1290 ;;

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

1292 esac

1293 }

1294 invite() {

1295 email=`getpar email`

1296 case $email in

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

1298 *@*)

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

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

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

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

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

1304 elif sendinvitation $email; then

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

1306 fi ;;

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

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

1309 esac

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

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

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

1313 fi

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

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

1316 $layout/html.m4.html $layout/invite.m4.html

1317 }

1318 regist() {

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

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

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

1322 echo "bye bye" | html p

1323 reutrn

1324 fi

1325 email=`session=$1 getpar invite`

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

1327 cat<<EOF

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

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

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

1331 EOF

1332 return

1333 fi

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

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

1336 # Fake login password to wasureta

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

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

1339 wasureta $email

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

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

1342 addsession $1 # for removal after 1 minute

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

1344 $layout/login.m4.html

1345 return

1346 }

1347 group_safename() {

1348 # Convert $1 to safe group name

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

1350 }

1351 groupupdate() {

1352 gname=`getpar gname`

1353 qgname=`sqlquote $gname`

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

1355 # See ALSO same job in showgroup()

1356 newgname=`group_safename "$gname"`

1357 err newgname=$newgname

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

1359 err NewGNAME: gname=$newgname

1360 gname=$newgname

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

1362 replpar gname string "$gname"

1363 fi

1364 # Name confliction check

1365 parow=`getpar rowid`

1366 ## err parow=$parow

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

1368 query "BEGIN EXCLUSIVE;"

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

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

1371 if [ $count -gt 0 ]; then

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

1373 query "END;"

1374 return

1375 fi

1376 par2table $formdir/grp.def

1377 query "END TRANSACTION;"

1378 # Remove orphan

1379 : <<EOF

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

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

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

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

1384 EOF

1385 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1389 sql="delete from blog where id in

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

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

1392 query "$sql";

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

1394 return # and return

1395 fi

1396 fi

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

1398 fi

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

1400 grid=$(query $sql)

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

1402 grp $grid

1403 }

1404 groupclone() {

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

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

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

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

1409 return

1410 fi

1411 i=0

1412 while true; do

1413 copy="-copy$i"

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

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

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

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

1418 i=$((i++))

1419 continue

1420 fi

1421 break

1422 done

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

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

1425 query<<-EOF

1426 BEGIN;

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

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

1429 SELECT $newqname, key, val

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

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

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

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

1434 -- Copy members and their configuration --

1435 REPLACE INTO grp_mem SELECT $newqname, user

1436 FROM grp_mem WHERE gname=$qgrp;

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

1438 FROM grp_mem_s WHERE gname=$qgrp;

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

1440 FROM grp_mem_m WHERE gname=$qgrp;

1441 -- Copy administrators --

1442 REPLACE INTO grp_adm SELECT $newqname, user

1443 FROM grp_adm WHERE gname=$qgrp;

1444 COMMIT;

1445 EOF

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

1447 STOPCLONEMSG=1 groupconf "$newrowid"

1448 }

1449 groupman() {

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

1451

1452 GF_STAGE="grpconf"

1453 GF_STAGE=groupupdate

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

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

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

1457 -D_DUMPTABLE_="syscmd(cat)" \

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

1459 }

1460 userconf() {

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

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

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

1464 }

1465 groupconf() {

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

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

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

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

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

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

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

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

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

1475 | html p

1476 fi

1477 }

1478 mems() {

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

1480 kwd=`getpar kwd`

1481 listmember $kwd

1482 }

1483 grps() {

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

1485 kwd=`getpar kwd`

1486 listgroup $kwd \

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

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

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

1490 $layout/form+dump.m4.html

1491 }

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

1493 gpg=`getpar grp`

1494 grid=${1:-$gpg}

1495 grp=`getgroupbyid "$grid"`

1496 ## . ./s4-blog.sh

1497 jg=`getpar joingrp`

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

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

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

1501 fi

1502 htmlheader=$layout/html.m4.html

1503 showgroup "$grid"

1504 }

1505 sql4interestblogs() {

1506 cat<<EOF

1507 CREATE TEMPORARY VIEW interestblogs AS

1508 SELECT blog.rowid rid, id, author

1509 FROM blog

1510 NATURAL JOIN

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

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

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

1514 WHEN (SELECT user FROM grp_mem

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

1516 THEN 0

1517 ELSE 1

1518 END;

1519 EOF

1520 }

1521 listnewblogsql() { # $1=user

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

1523 WHERE user='$user'

1524 AND tblrowid=$blogreadflagrowid),

1525 "0");"`

1526 cat<<EOF

1527 `sql4interestblogs`

1528 WITH article_ctime as (

1529 SELECT id,blogid,author,val ctime

1530 FROM article join article_s s using(id)

1531 WHERE s.key='ctime'

1532 ), blog_title_owner as (

1533 SELECT blg.rid brid, id,

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

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

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

1537 ), visited AS (

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

1539 FROM blog_title_owner b, article_ctime ac

1540 JOIN acclog al

1541 ON b.id=ac.blogid

1542 AND al.tbl='blog'

1543 AND al.tblrowid=brid

1544 AND al.user='$user'

1545 AND al.time < ctime

1546 AND '$deftime' < ctime

1547 ), unvisited as (

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

1549 FROM blog_title_owner b, article_ctime ac

1550 ON b.id=ac.blogid

1551 WHERE brid NOT IN (SELECT tblrowid FROM acclog

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

1553 AND ctime > '$deftime'

1554 ), blogall as (

1555 /* ---------------------------------------

1556 Collect new articles with dividing them into visited and unvisited

1557 separately, because constructing joined table of artice X acclog

1558 tends to become HUGE combinations.

1559 --------------------------------------- */

1560 SELECT * FROM visited UNION SELECT * FROM unvisited

1561 ), news as (

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

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

1564 from blogall bl /* left join a_u l

1565 on bl.brid=l.tblrowid */

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

1567 LIMIT 10

1568 ) SELECT brid LINK, "新着",

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

1570 ctime, title,

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

1572 FROM news;

1573 EOF

1574 }

1575

1576 search_form() {

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

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

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

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

1581 @week → 最近一週間

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

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

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

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

1586 auth=""

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

1588 case "$1" in

1589 author=*)

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

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

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

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

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

1595 ;;

1596 grp=*)

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

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

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

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

1601 ;;

1602 esac

1603 cat<<-EOF

1604 <div class="right">

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

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

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

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

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

1610 </form>

1611 </div>

1612 EOF

1613 }

1614

1615 imgsrc_cache() (

1616 # $1 = directory for cache'ing

1617 # $2 = table (user_m or grp_m)

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

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

1620 dir="$1" tbl="$2"

1621 keycond="$3"

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

1623 ORDER BY rowid DESC LIMIT 1"

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

1625 tmpf=$tmpd/imgsrc_cache.$$

1626 case "$4" in

1627 [Ss]) size=S ;;

1628 [Oo]) size=O ;;

1629 *) size=M ;;

1630 esac

1631 # ImageCache filename storing schema:

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

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

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

1635 valtype=`query "$sql0"`

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

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

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

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

1640 case "$filetype" in

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

1642 esac

1643 fi

1644 cacheimg_S=$dir/S_$filename

1645 cacheimg_M=$dir/M_$filename

1646 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1654 else

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

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

1657 fifo2=$fifo.2

1658 mkfifo $fifo $fifo2

1659 fmt=${filename##*.}

1660 ## [[ NOTE ]]

1661 ## a. convert oldimage newimage

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

1663 ## b is much smaller than a

1664 cat $tmpf | unhexize \

1665 | tee $fifo \

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

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

1668 | tee $fifo2 \

1669 | convert - "$cacheimg_M" &

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

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

1672 | convert - "$cacheimg_S" &

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

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

1675 echo '">'

1676 echo "$sum" > $sumfile

1677 fi

1678 ## Now preparing cache image, done.

1679 ## Store this information to DB

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

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

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

1683 query <<-EOF

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

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

1686 EOF

1687 )

1688

1689 showhome() {

1690 # $1=userRowIdToShow

1691 err showhome \$1=$1

1692 case "$1" in

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

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

1695 esac

1696 ## err ShowHome: uname=$uname

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

1698 gecos=`gecos "$uname"`

1699 ## err SH:gecos=$gecos

1700 GF_VIEWONLY=1

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

1702 search_form_args=""

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

1704 conflink="<a href=\"?userconf\" accesskey=\"e\"

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

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

1707 # Display folders

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

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

1710 and type like 'file:%';"

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

1712 nfile=`query "$sql"`

1713 # err nfile=$nfile

1714 if [ $nfile -gt 0 ]; then

1715 conflink="$conflink / <a href=\"?lsmyfile\" accesskey=\"l\"

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

1717 fi

1718 else

1719 search_form_args="author=$uname"

1720 fi

1721 . ./s4-blog.sh

1722

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

1724 search_form $search_form_args > $sf

1725 echo "$gecos さん" > $tf

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

1727 viewtable $formdir/user.def user $1

1728 echo "</div>"

1729 } > $pf

1730

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

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

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

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

1735 if true; then

1736 tbl=user_m

1737 enticond="name='$uname'"

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

1739 else

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

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

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

1743 echo '">'

1744 }

1745 fi > $imf

1746 fi

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

1748 val='$uname';"`

1749 listblog $uname > $bf

1750

1751 hometail=$tmpd/tail.$$

1752 mkfifo $hometail

1753

1754 #Calling listgroupbytable, originally here

1755

1756 (

1757 # Display Most Recent Entry

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

1759 shortval=${shortval:-val}

1760

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

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

1763 ctime,

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

1765 (SELECT gecos FROM gecoses

1766 WHERE name=(SELECT val FROM blog_s

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

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

1769 FROM blog b

1770 JOIN

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

1772 FROM article a, article_s s

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

1774 GROUP BY blogid ORDER BY val DESC LIMIT 50

1775 ) m

1776 ON b.id=m.blogid;"

1777 # This should be as follows

1778 : <<EOF

1779 WITH arts AS(

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

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

1782 FROM article a NATURAL JOIN article_s s

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

1784 GROUP by s.id

1785 )

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

1787 FROM arts a0

1788 JOIN

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

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

1791 ORDER BY ctime DESC LIMIT 50;

1792 EOF

1793

1794 cat<<-EOF

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

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

1797 <div class="lcto">

1798 `DT_VIEW=replyblog dumptable html blog`

1799 </div>

1800 EOF

1801 unset DT_SQL

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

1803 # Display NEWS

1804 # 2016-06-26

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

1806 acclog blog $blogreadflagrowid

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

1808 fi

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

1810 sql=`listnewblogsql "$user"`

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

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

1813 cont=$((cont-1))

1814 err newcount=$cont

1815 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

1822 `cgi_submit '確定'`

1823 `cgi_hidden read yes`

1824 EOF

1825 echo "$new10 "

1826 echo "</div>"

1827 fi

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

1829 : # DT_SQL=

1830 fi

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

1832 #

1833 listgroupbytable $formdir/grp.def $cond |

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

1835 -D_PROFILE_="spaste(\`$pf')$conflink" \

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

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

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

1839 -D_NBLOG_="$nblog" \

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

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

1842 $layout/html.m4.html $layout/home.m4.html

1843

1844 # Record access log

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

1846 }

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

1848 contenttype; echo

1849 ## err commission: "$@"

1850 gname=`getgroupbyid $1`

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

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

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

1854 grp_reg_adm "$@"

1855 else

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

1857 fi

1858 }

1859 listgroupbytable() {

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

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

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

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

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

1865 NGsql="select distinct tag from\

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

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

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

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

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

1871 for tag in `query "$sql"`

1872 do

1873 ## err ListGrp: tag=$tag

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

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

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

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

1878 as rowid,

1879 gname,

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

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

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

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

1884 ## err PersonalGroupList= `echo $sql`

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

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

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

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

1889 echo '</table>'

1890 done

1891 echo '</div>'

1892 }

1893 iconhref() (

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

1895 data=`percenthex "$1"`

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

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

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

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

1900 )

1901 iconhref2() (

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

1903 src=$1

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

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

1906 )

1907 listentry() (

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

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

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

1911 cond='' hiddens=''

1912 offset=`getpar offset`

1913 offset=${offset%%[!0-9]*}

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

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

1916 limit=30

1917 dir=`getcachedir "$1"`

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

1919 hrb="$myname?home"

1920 deficon=person-default.png

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

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

1923 gcs=gecos

1924 else # if group

1925 hrb="$myname?grp"

1926 deficon=group-default.png

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

1928 gcs=name

1929 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

1930 if [ -n "$tagline" ]; then

1931 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

1932 ## err tagconv=$tagconv

1933 fi

1934 fi

1935 if [ ! -d $dir ]; then

1936 mkdir -p $dir

1937 fi

1938 if [ ! -s $dir/$deficon ]; then

1939 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

1940 fi

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

1942 cond="where nick like '%$2%' or b.name like '%$2%'"

1943 fi

1944

1945 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

1946 # grpは呼出し元の動的スコープ変数でよくないな...

1947 ##qgrp=`sqlquote $grp`

1948 getgrp="(select gname from grp where rowid=${rowid:--1})"

1949 sql="select a.rowid, a.$link,

1950 coalesce(b.$gcs, a.$nm) as nick,

1951 quote(a.$nm) as qname,

1952 (SELECT val FROM ${tbl}_s

1953 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

1954 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

1955 || case when a.$nm in (select gname from grp)

1956 then printf('(%d名)',

1957 (select count(user) from grp_mem where gname=a.$nm))

1958 else ' <'||a.$nm||'>'

1959 end

1960 as name,

1961 b.tag,

1962 case when a.$nm in (select user from grp_adm

1963 where gname=$getgrp) then '(管理者)'

1964 when '$user' in (select user from grp_adm where gname=a.$nm)

1965 then '(ADMIN)'

1966 when '$user' in (select user from grp_mem where gname=a.$nm)

1967 then '(Member)'

1968 when '$iamowner' = '' then ''

1969 else ',not='||a.rowid end as ownerlink,

1970 CASE '$entity'

1971 WHEN 'グループ'

1972 THEN coalesce(

1973 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

1974 'open')

1975 ||

1976 CASE WHEN '$user'

1977 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

1978 THEN ' ismember'

1979 ELSE ''

1980 END

1981 ELSE 'user'

1982 END regmode

1983 from $tbl a left join

1984 (select $nm as name,

1985 max(case key when 'gecos' then val end) as gecos,

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

1987 max(case key when 'mtime' then val end) as mtime,

1988 max(case key when 'wtime' then val end) as wtime

1989 from ${tbl}_s group by $nm)

1990 b on a.$nm=b.name $cond $3

1991 order by b.tag desc, b.wtime desc, b.mtime desc, a.rowid asc"

1992 # Give precedence to newer maintained groups (2016-09-24)

1993 # Note that mtime is stored only in grp_s.

1994 ## err LE:sql.1="$sql"

1995 total=`query "with x as ($sql) select count(*) from x;"`

1996 echo "${entity} 一覧" | html h2

1997 if [ $total -gt $limit ]; then

1998 echo '<div class="right">'

1999 cgi_form $stage <<EOF

2000 <label>次の語を含む${entity}で検索:

2001 `cgi_text kwd $kwd`</label>

2002 EOF

2003 echo '</div>'

2004 fi

2005 hiddens="$hiddens

2006 `cgi_hidden kwd \"$kwd\"`

2007 `cgi_hidden stage \"$stage\"`"

2008 cat<<EOF

2009 <p>${total}件中の$((offset+1))件めから${kwd:+" - 検索語: $kwd"}</p>

2010 EOF

2011 if [ $((offset+limit)) -lt $total ]; then

2012 nextbtn=$(

2013 cat<<EOF

2014 <div class="right clear"><form action="$myname" method="POST">

2015 `cgi_submit 次の${limit}件`

2016 $hiddens

2017 `cgi_hidden offset $((offset + limit))`</form></div>

2018 EOF

2019 )

2020 fi

2021 if [ $offset -gt 0 ]; then

2022 prevbtn=$(

2023 cat<<EOF

2024 <form action="$myname" method="POST">

2025 `cgi_submit 前の${limit}件`

2026 $hiddens

2027 `cgi_hidden offset $((offset - limit))`</form>

2028 EOF

2029 )

2030 fi

2031 pnbtn="$nextbtn$prevbtn"

2032 echo $pnbtn

2033

2034 ## err ListEntry: `echo "$sql"\;`

2035 # sq $db here??? 2016-11-28

2036 query "$sql limit $limit ${offset:+offset $offset};" \

2037 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2038 err name=$name owner=$ownerp lnk=$lnk

2039 err newlnk=$lnk regmode=$regmode

2040 icondir=$dir/$id

2041 # Pick up only last icon

2042 echo "<div class=\"iconlist xy$thumbxy $type\">

2043 <p class=\"tag _$tag\">$tag</p>" \

2044 | _m4 $tagconv

2045 if [ -n "$NOSPEEDUP" ]; then

2046 files=`getvalbyid $tbl profimg $id $icondir`

2047 if [ -n "$files" ]; then

2048 icon=`echo "$files"|tail -1`

2049 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2050 else

2051 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2052 fi

2053 elif [ -n "$icon" -a -s "$icon" ]; then

2054 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2055 else

2056 cond="$nm=$qname"

2057 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2058 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2059 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2060 # err "img=[$img]"

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

2062 echo "<a href=\"$hrb+$lnk\">"

2063 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2064 echo "</a>"

2065 else

2066 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2067 fi

2068 fi

2069 echo "<br>$name${ownerp:+<br>$ownerp}"

2070 echo "</div>"

2071 done

2072 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2073 )

2074 listmember() {

2075 listentry user "$@"

2076 }

2077 listgroup() {

2078 listentry group "$@"

2079 }

2080 hexteams() { # $1=gname, $2(optional)=user

2081 cond=${2:+" AND user='$2'"}

2082 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2083 WHERE gname='$1' AND key='team'$cond;"

2084 }

2085 showgroup() { # $1=group-rowid

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

2087 grid=`getpar grid`

2088 grid=${grid%%[!0-9]*}

2089 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2090 else

2091 grid=$1

2092 fi

2093 grp=`getgroupbyid $grid`

2094 qgrp=`sqlquote $grp`

2095 ## err showgroup2: grp=$grp qgrp="[$(sqlquote $grp)]"

2096 if isgroup "$grp"; then

2097 tf=$tmpd/title.$$

2098 sf=$tmpd/search.$$

2099 bodyclass=`query "SELECT val FROM grp_s

2100 WHERE gname=$qgrp AND key='regmode';"`

2101 if ismember "$user" "$grp"; then

2102 ismember="ismember"

2103 qgrp=`sqlquote $grp`

2104 bodyclass="$bodyclass${bodyclass:+ }ismember"

2105 else

2106 ismember="" # bodyclass="group"

2107 fi

2108 bodyclass="$bodyclass grouphome"

2109 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2110 echo "グループ $grp" > $tf

2111

2112 showgroupsub $formdir/grp.def "$grid" | \

2113 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2114 -D_FORM_="syscmd(\`cat')" \

2115 -D_BODYCLASS_="$bodyclass" \

2116 -D_DUMPTABLE_="" \

2117 $htmlheader $sf $layout/form+dump.m4.html

2118 # $htmlheader $layout/form+dump.m4.html

2119 # $htmlheader is defined in grp()

2120 else # if $grp is removed at par2table

2121 listgroup

2122 fi

2123 }

2124 showgroupsub() {

2125 # $1=def-file $2=group-rowid

2126 # Using $ismember

2127 rowid=$2

2128 grp=`getgroupbyid $2`

2129 qgrp=`sqlquote $grp`

2130 td=`getcachedir grp/"$2"`

2131 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2132 if [ -z "$rowid" ]; then

2133 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2134 #grp=`sq $db "select gname from grp where rowid=$grp"`

2135 echo "showgroupsub: invalid argument($1 $2)" | html p

2136 return

2137 fi

2138 val=`getvalbyid grp profimg $rowid $tmpd`

2139 enticond="gname=$qgrp"

2140 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

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

2142 cat<<-EOF

2143 <p class="groupimg">

2144 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2145 EOF

2146 fi

2147 echo "<div class=\"noprofimg\">"

2148 viewtable $1 grp $rowid

2149 echo "</div>"

2150 if isgrpowner "$user" "$grp"; then

2151 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2152 iamowner=$rowid

2153 colmd=" mode"

2154 fi

2155 if [ -n "$ismember" ]; then

2156 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2157 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2158 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2159 cat<<EOF

2160 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2161 <div class="fold clear">

2162 `cgi_checkbox send yes id="send"`<label

2163 for="send">グループ全員にメッセージ送信</label>

2164 <div>

2165 `cgi_textarea message "" "cols=60"`

2166 `cgi_submit 送信`

2167 `cgi_reset リセット`

2168 </div>

2169 `cgi_hidden grp $rowid`

2170 </div></form>

2171 EOF

2172 fi

2173 # 加入ボタン + 加入者リスト

2174 if [ -n "$ismember" ]; then

2175 ismem='checked' state="(参加中)"

2176 else

2177 nomem='checked' state="(現在非加入)"

2178 fi

2179 # このグループでの加入アドレス

2180 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2181 and key='email';"`

2182 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2183 ## and key='email';"

2184 ##err email=$eml

2185 cat <<EOF

2186 <div class="fold clear">

2187 `cgi_checkbox reg yes id="reg"`<label

2188 for="reg">自身の加入状態を操作する</label>$state

2189 <div>

2190 EOF

2191 cgi_form grp <<EOF

2192 <p>このグループに</p>

2193 <table class="b">

2194 <tr><th>メンバーとして</th><td>

2195 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2196 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2197 <tr><th>参加する場合のメイルアドレス<br>

2198 <small>(メインのアドレスとは違うものにする場合に記入<br>

2199 同じでよい場合は空欄に)</small></th>

2200 <td>`cgi_text email $eml`</td></tr>

2201 </table>

2202 `cgi_hidden grp $rowid`

2203 EOF

2204 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2205 echo "moderated (承認加入の)グループなので実際に参加できるのは

2206 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2207 fi

2208 echo '</div></div>'

2209 echo '<h2>話題一覧</h2>'

2210 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2211 cat<<-EOF

2212 <form class="summary" action="$myname" title="$thelp">

2213 `cgi_hidden owner "$grp"`

2214 `cgi_hidden kwd "@week"`

2215 `cgi_hidden stage searchart`

2216 `cgi_submit "一週間のまとめ"`

2217 </form>

2218 EOF

2219 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2220 colstate="state:稼動状態:frozen=rowclass=凍結"

2221 DT_CHLD=article:blogid \

2222 DT_VIEW=replyblog dumptable html blog \

2223 "ctime team title heading$colmd $colstate" "$cond"

2224

2225 getgname="(select gname from grp where rowid=$rowid)"

2226 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2227 cm="?commission+$rowid"

2228 thumbxy=50x50 listmember "" "$c" "$rowid" \

2229 |sed -e "s|$<br>$,not=$.*$|\1|" # 間違って押しやすい

2230 # team list

2231 hexteams=`hexteams "$grp"`

2232 if [ -n "$hexteams" ]; then

2233 echo "チーム一覧" | html h2

2234 echo '<div class="dumptable"><table class="b">'

2235 sq $db -html -header<<-EOF

2236 SELECT val TEAM,

2237 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2238 MEMBERS

2239 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2240 EOF

2241 echo '</table></div>'

2242 fi

2243 }

2244 grp_getbodyclass() {

2245 # Get css class name for document.

2246 # `moderated' for moderated groups

2247 # `ismember' for groups where user belongs

2248 # $1=GroupName (w/o quote)

2249 # $user=userNameCurrentlyLogin

2250 ## err grp_getbodyclass: 1="$1"

2251 qgrp=`sqlquote "$1"`

2252 query<<-EOF

2253 SELECT coalesce(

2254 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2255 'open')

2256 ||

2257 CASE WHEN '$user'

2258 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2259 THEN ' ismember'

2260 ELSE ''

2261 END;

2262 EOF

2263 }

2264 grpaction() { # $1=group-rowid

2265 err GRP_ACTION:IN

2266 grid=${1:-`getpar grp`}

2267 grp=`getgroupbyid "$grid"`

2268 if [ -z "$grp" ]; then

2269 echo "無効な指定です。" | html p; return

2270 fi

2271 if ! ismember $user $grp; then

2272 echo "加入者のみに許可された操作です。" | html p; return

2273 fi

2274 echo "グループ $grp 個別選択操作" \

2275 | _m4 -D_TITLE_="syscmd(\`cat')" \

2276 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2277 $layout/html.m4.html

2278

2279 isowner=""

2280 isgrpowner "$user" "$grp" && isowner="yes"

2281 usel=`getpar usel`

2282 if [ -n "$usel" ]; then

2283 uids=$(echo `echo $usel`|tr ' ' ',')

2284 ## err grpaction-1: grp=$grp, `echo $sql`

2285 text=`getpar text`

2286

2287 rm=`getpar rm` cfm=`getpar confirm`

2288 ## err rm=$rm cfm=$cfm

2289 if [ x"$rm" = x"yes" ]; then

2290 if [ "$isowner" ]; then

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

2292 # Eliminate

2293 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2294 for tbl in grp_mem grp_mem_s grp_mem_m; do

2295 sql="delete from $tbl $cond;"

2296 # echo "sql=$sql"

2297 query "$sql"

2298 err rmGRPuser "$sql"

2299 done

2300 num=`query "select count(*) from user where rowid in ($uids);"`

2301 #err num=$num

2302 if [ 0$num -gt 0 ]; then

2303 sql="select coalesce(b.val,a.name) from user a left join \

2304 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2305 # err `echo "$sql"`

2306 html pre<<EOF

2307 以下の${num}名のグループ $grp 登録を解除しました。

2308 `query "$sql"`

2309 EOF

2310 fi

2311 else

2312 echo "確認のチェックがないのでやめておきます。" | html p

2313 return

2314 fi

2315 else # not Group Owner

2316 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2317 return

2318 fi

2319 cat<<EOF

2320

2321 EOF

2322 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2323 if [ -z "$text" ]; then # if msg is empty

2324 echo "なにかメッセージを..." | html p

2325 return 0

2326 fi

2327 gecos=`gecos $user`

2328 mkfrom=`getpar mkfrom`

2329 if [ x"$mkfrom" = x"yes" ]; then

2330 safegc=`echo "$gecos" | tr -d '<>@'`

2331 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2332 fromad=`email4groupbyuid "$grp" "$myuid"`

2333 mail_from="$safegc <$fromad>"

2334 else

2335 mail_from="$admin"

2336 fi

2337 MAIL_FROM=$mail_from \

2338 smail "`email4groupbyuid "$grp" $usel` $user" \

2339 "$gecos さんからのメッセージ" <<EOF

2340 $url

2341 のグループ「$grp」のメンバーである $gecos さんから、

2342 あなた宛へのメッセージです。

2343 ----------------------------------------------------------

2344 $text

2345 EOF

2346 if [ $? = 0 ]; then

2347 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2348 sql="select coalesce(b.val, a.name) from

2349 (select name from user where rowid in ($uids)) a

2350 left join user_s b on a.name=b.name and b.key='gecos';"

2351 html pre<<EOF

2352 `query "$sql"`

2353 (送信者である $gecos さんも含まれます)

2354 EOF

2355 err SendDone: `echo $sql`

2356 fi

2357 elif [ x"$rm" = x"commission" ]; then

2358 grp_reg_adm $grid $usel

2359 elif [ x"$rm" = x"addteam" ]; then

2360 team=`getpar team|sed "s/'/''/g"` # for single quotation

2361 newteam=`echo "$team"|tr -d ,`

2362 if [ x"$team" != x"$newteam" ]; then

2363 echo "チーム名に使えない文字を除去しました" | html p

2364 team=newteam

2365 fi

2366 if [ -z "$team" -o x"$team" = x"なし" ]; then

2367 cat<<-EOF | html p

2368 有効なチーム名を入力してください。

2369 カンマだけ、「なし」という名前は使えません。

2370 EOF

2371 echo "有効なチーム名を入力してください。" | html p

2372 else

2373 grp_add_team $grid "$team" $usel

2374 fi

2375 elif [ x"$rm" = x"rmteam" ]; then

2376 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2377 rmteam=`getpar rmteam|sed "s/'/''/g"`

2378 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2379 gname='$grp' AND user='$user' AND key='team'\

2380 AND val='$rmteam';\"`" ]; then

2381 grp_rm_team $grid "$rmteam" $usel

2382 else

2383 echo "所属していないチームの除去操作はできません。"|html p

2384 fi

2385 else

2386 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2387 fi

2388 fi

2389 fi

2390 # New entry

2391 sql="select /* Ahh, ugly SQL, I wanna fix... */

2392 case

2393 when (select user from grp_adm where

2394 gname=(select gname from grp where rowid=$grid)

2395 and user=a.name) is not null

2396 then 'k'

2397 else ''

2398 end || a.rowid||

2399 ','||a.gecos as NAME,

2400 (SELECT count(author) /* Put post count for scoring 2016-08-01 */

2401 FROM article NATURAL JOIN article_s

2402 WHERE blogid IN

2403 (SELECT id FROM blog_s

2404 WHERE key='owner'

2405 AND val=(SELECT gname FROM grp where rowid=$grid))

2406 AND author=a.name AND key='text')

2407 as POST,

2408 (SELECT group_concat(val, ',')

2409 FROM grp_mem_m

2410 WHERE gname='$grp' AND user=a.name AND key='team') as TEAM

2411 FROM

2412 gecoses a

2413 WHERE name in (select user from grp_mem where

2414 gname=(select gname from grp where rowid=$grid))

2415 ORDER by a.gecos;"

2416 ## err grpaction: "`echo \"$sql\"`"

2417 tf=$tmpd/title.$$

2418 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2419 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2420 for=\"cmadmin\">グループ管理者委任</label>

2421 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2422 </p></div>"

2423 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2424 for=\"conf\">グループ登録解除</label>

2425 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2426 <p>この操作による通知は本人に行きません。

2427 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2428 </div>"

2429 # Get team list to which current user belongs into $hexteams

2430 myhexteams=$(hexteams "$grp" "$user")

2431 allhexteams=$(hexteams "$grp")

2432 if [ -n "$myhexteams" ]; then

2433 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2434 for=\"cmrmteam\">チーム属性除去</label>

2435 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2436 を除去します: `cgi_checkbox teamconfirm yes` 確認

2437 <p>この操作による通知は本人に行きません。

2438 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2439 </div>

2440 "

2441 fi

2442 b1='<label> <input type="checkbox" name="usel" value="'

2443 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2444 #b2='"> <span>' b3='</span></label>'

2445 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2446 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2447 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2448 cgi_form grpaction<<EOF \

2449 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2450 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2451 | _m4 -D_TITLE_="spaste(\`$tf')" \

2452 -D_SUBTITLE_="チェック後操作ボタン" \

2453 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2454 $layout/form+dump.m4.html

2455 <p>下でチェックした人を対象として:</p>

2456 <div class="foldtabs">

2457 `cgi_radio rm addteam 'id="cmteam"'`<label

2458 for="cmteam">同じチーム属性を付与</label>

2459 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2460 `cgi_datalist_h teams $allhexteams`

2461 </div>

2462 ${rmteammsg}

2463 `cgi_radio rm send id="sendmsg"`<label

2464 for="sendmsg">メッセージ送信</label>

2465 <div>

2466 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2467 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2468 `cgi_textarea text "" cols=40`

2469 </div>

2470 ${isowner:+$cmmsg$excmsg}

2471 `cgi_radio rm close id="x"`<label for="x">×</label>

2472 </div>

2473 <h4>$grp 参加者一覧</h4>

2474 <table class="td2r">

2475 `sq $db -header -html "$sql"`

2476 </table>

2477 `cgi_hidden grp $grid`

2478 EOF

2479 }

2480 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2481 # Create TEMPORARY VIEW

2482 query<<EOF

2483 CREATE TEMPORARY VIEW writeusers AS

2484 SELECT DISTINCT author FROM article

2485 WHERE id in (

2486 select id from article where blogid=(select id from blog where rowid=$1)

2487 );

2488 CREATE TEMPORARY VIEW movablegroups AS

2489 SELECT g.rowid growid , g.gname

2490 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2491 ON grp.gname=gm.gname -- そのユーザが属している

2492 AND user='$user') g -- グループに絞る

2493 WHERE (SELECT author FROM writeusers

2494 EXCEPT

2495 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2496 IS NULL;

2497 $2

2498 EOF

2499 }

2500 sql4readableblogs() {

2501 # Create view of blogs that can be readable to $user

2502 # Blog is readable when:

2503 # 1: blog owner is an user

2504 # 2: else, 2.1: owner-group where the $user belongs

2505 # 2.2: else, owner-group is not moderated

2506 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2507 cat<<EOF ## | tee tmp/sql.out

2508 CREATE TEMPORARY VIEW readableblogs AS

2509 SELECT blog.rowid rid, id, author

2510 FROM blog

2511 NATURAL JOIN

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

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

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

2515 WHEN (SELECT val FROM grp_s

2516 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2517 AND

2518 (SELECT user FROM grp_mem

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

2520 THEN 0

2521 ELSE 1

2522 END;

2523 EOF

2524 }

2525 editheading() { # $1=rowid-of-heading

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

2527 if [ -z "$rowid" ]; then

2528 echo "話題番号が未指定です。" | html p

2529 return

2530 fi

2531 owner=`getvalbyid blog owner $rowid`

2532 title=`getvalbyid blog title $rowid`

2533 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2534 | _m4 -D_TITLE_="修正" \

2535 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2536 -D_BLOGS_="" -D_DUMPTABLE_="" \

2537 -D_FORM_="syscmd(\`cat')" \

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

2539 # Move to group

2540 if isuser "$owner"; then

2541 crview4article $rowid

2542 n=`query "SELECT count(*) FROM writeusers;"`

2543 ## err N=$n

2544 if [ $((n)) -gt 0 ]; then

2545 ## err ROWID=$rowid

2546 sql="SELECT growid || ':' || gname FROM movablegroups;"

2547 cat<<-EOF

2548 <div class="fold">

2549 `cgi_checkbox mv send id="mv"`<label

2550 for="mv">この話題をグループ所有に移動する</label>

2551 <div>

2552 <form action="?mvart" method="POST" enctype="multipart/form-data">

2553 移動先グループ:

2554 <select name="mv2grp">

2555 EOF

2556 query ".mode html"

2557 query<<-EOF |

2558 $sql

2559 .mode list

2560 EOF

2561 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2562 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2563 cat<<-EOF

2564 </select>

2565 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2566 そのグループに加入しているものに限られます)</p>

2567 <p>`cgi_checkbox cfm yes`<label>確認

2568 (この操作は元に戻すことができません)</label></p>

2569 `cgi_hidden blogrowid $rowid`

2570 `cgi_submit 移動`

2571 `cgi_reset Reset`

2572 </form>

2573 </div>

2574 </div>

2575 EOF

2576 fi

2577 # end of isuser "$owner"

2578 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2579 [ -n "$hexteams" ];}; then

2580 none="`echo なし|hexize`"

2581 cat<<-EOF

2582 <div class="fold">

2583 `cgi_checkbox mv2team send id="mv2team"`<label

2584 for="mv2team">この話題を以下のチームのものにする</label>

2585 <div><p>現在の所属チーム設定:

2586 `query "SELECT

2587 coalesce((SELECT val FROM blog_s

2588 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2589 AND key='team'),

2590 ':なし');"`</p>

2591 <form action="?mvart" method="POST" enctype="multipart/form-data">

2592 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2593 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2594 `cgi_hidden blogrowid $rowid`<br>

2595 `cgi_submit 移動`

2596 `cgi_reset Reset`

2597 </form></div></div>

2598 EOF

2599 fi

2600 }

2601 mvart() { # move diary to some group or team

2602 # or move blog of group to team which belong to the group

2603 blogrowid=`getpar blogrowid`

2604 cfm=`getpar cfm`

2605 ##### echo move blog:$blogrowid to $mv2grp | html p

2606 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2607 . ./s4-blog.sh

2608 if [ -z "$blogrowid" ]; then

2609 echo "無効な指定です(mvart)。" | html p

2610 return

2611 elif [ x"$cfm" != x"yes" ]; then

2612 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2613 elif { mv2grp=`getpar mv2grp`

2614 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2615 [ -n "$mv2grp" ]; }; then

2616 crview4article $blogrowid

2617 ########## TRANSACTION BEGIN

2618 query "BEGIN;"

2619 n=`query "SELECT count(*) FROM writeusers;"`

2620 ## err Nwriteuser=$n

2621 if [ $((n)) -gt 0 ]; then

2622 query<<-EOF

2623 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2624 WHERE key='owner'

2625 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2626 AND $mv2grp IN (SELECT growid FROM movablegroups);

2627 EOF

2628 fi

2629 query "END;"

2630 ########## TRANSACTION END

2631 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2632 [ -n "$mv2team" ];}; then

2633 # blog owner can move it to ANY team

2634 case "$mv2team" in

2635 'なし')

2636 cat<<-EOF

2637 DELETE FROM blog_s

2638 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2639 AND key='team';

2640 EOF

2641 ;;

2642 "") ;;

2643 *)cat<<-EOF

2644 BEGIN;

2645 REPLACE INTO blog_s(id, key, val)

2646 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2647 'team', '$mv2team');

2648 REPLACE INTO blog_s(id, key, val)

2649 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2650 'notify', 'all'); -- Change notify to all

2651 END;

2652 EOF

2653 esac | query

2654 fi

2655 blog_reply $blogrowid

2656 echo yes | html p

2657 }

2658 editart() { # $1=article-rowid $2=blogrowid

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

2660 blogrowid=${2%%[!A-Z0-9a-z_]*}

2661 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2662 echo "表示する記事番号が未指定です。" | html p

2663 return

2664 fi

2665 owner=`getvalbyid blog owner $blogrowid`

2666 title=`getvalbyid blog title $blogrowid`

2667 author=`getvalbyid article author $rowid`

2668 ## err EDITart: owner=$owner, author=$author

2669 if isgrpowner $user $owner; then

2670 : EDIT OK

2671 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2672 echo "本人か所有者しか編集できません." | html p

2673 return

2674 fi

2675 aid=`query "select id from article where rowid=$rowid;"`

2676 tmpout=$tmpd/editart.$$.out

2677 GF_ACTION="?replyblog+$blogrowid#$aid" \

2678 edittable $formdir/article.def article $rowid \

2679 > $tmpout

2680 rm -f /tmp/editart.out

2681 # Cannot use pipelining to m4 with genform() because of stdin stack

2682 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2683 -D_FORM_="syscmd(cat $tmpout)" \

2684 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2685 -D_BLOGS_= -D_DUMPTABLE_= \

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

2687 }

2688 send2mem() {

2689 rowid=`getpar grp`

2690 if [ -z "$rowid" ]; then

2691 echo "グループが未指定です。" | html p

2692 return

2693 fi

2694 message=`getpar message`

2695 if [ -z "$message" ]; then

2696 echo "文章を入れてください。" | html p

2697 return

2698 fi

2699 grp=`getgroupbyid $rowid`

2700 members=`collectemail $grp`

2701 # smail rcpt subj (file)

2702 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2703 $urlbase?grp+$rowid

2704 グループ $grp に所属する

2705 `gecos $user` さんよりメッセージ:

2706

2707 $message

2708 EOF

2709 cat<<EOF

2710 <p>以下の宛先に送信しました。</p>

2711 <pre>

2712 $members

2713 </pre>

2714 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2715 EOF

2716 }

2717 joingrpadmit() {

2718 # $1=yes/no $2=session-key

2719 if [ -z "$2" ]; then

2720 echo "bye bye" | html p; return

2721 fi

2722 t_usr=`session=$2 getpar user`

2723 t_grp=`session=$2 getpar group`

2724 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2725 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2726 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2727 echo "無効な加入依頼です。" | html p

2728 echo "有効期限が切れたか、

2729 他の管理者がいる場合は処理済みの可能性があります。" | html p

2730 return

2731 fi

2732 if ! isgrpowner "$user" $t_grp; then

2733 echo "グループ管理者のみの機能です。" | html p; return

2734 fi

2735 case $1 in

2736 yes) joingrp "$t_grp" "$t_usr" yes ;;

2737 no) joingrp "$t_grp" "$t_usr" no ;;

2738 *)

2739 echo "無効な指定です($1)。" | html p

2740 return ;;

2741 esac

2742 gid=$(query "select rowid from grp where gname=`sqlquote $t_grp`;")

2743 rcpts="`getgroupadminmails $t_grp` $user"

2744 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2745 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2746 に

2747 $t_usr

2748 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2749 しました。"

2750 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2751 query "delete from session where id='$2';"

2752 echo "$body" | html p

2753 }

2754

2755 joingrprequest() {

2756 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2757 jss="joingrp-`date +%s`-`genrandom 12`"

2758 addsession $jss +${memoplimitdays}days

2759 query "replace into par values('$jss', 'group', 'string', `sqlquote $1`),

2760 ('$jss', 'user', 'string', `sqlquote $user`);"

2761 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2762 $url

2763 $user さんから

2764 グループ $1

2765 に加入依頼がありました。

2766

2767 承認する:

2768 $urlbase?joingrpadmit+yes+$jss

2769

2770 白紙に戻す:

2771 $urlbase?joingrpadmit+no+$jss

2772 EOF

2773 echo "管理者に加入依頼を出しました。

2774 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2775 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2776 }

2777 joingrp() {

2778 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2779 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2780 if isgrpowner "$user" "$1"; then

2781 isowner="yes"

2782 elif [ -n "$5" ]; then

2783 isowner="yes"

2784 else

2785 isowner=""

2786 fi

2787 ## err jg:isgrpowner: isowner="$isowner"

2788 if [ -n "$isowner" ]; then

2789 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2790 elif [ x"$2" != x"$user" ]; then # if user is not login user

2791 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2792 return

2793 elif [ x"$3" = x"no" ]; then

2794 : # Do not pursue those who leave

2795 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2796 : # Member can change own email address for the joining moderated group

2797 else # adding user is $user itself

2798 case `getgroupattr $1 regmode` in

2799 moderated)

2800 joingrprequest "$@" # Request only

2801 return

2802 ;;

2803 *)

2804 ;;

2805 esac

2806 fi

2807 qgname=`sqlquote $1`

2808 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

2809 cond="where gname=$qgname and user='$2'"

2810 if [ x"$3" = x"yes" ]; then

2811 query "replace into grp_mem values($qgname, '$2');"

2812 # Notify joingrp to admin

2813 action="に加入しました。"

2814 if [ -n "$4" ]; then

2815 if msg=`emaildomaincheck "$4"`; then

2816 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2817 'string', '$4', NULL);"

2818 else

2819 echo $msg

2820 fi

2821 else

2822 query "delete from grp_mem_s $cond and key='email';"

2823 fi

2824 if [ -n "$5" ]; then # as ADMIN

2825 # Coming here means newly created group

2826 sql="select case\

2827 when (select count(*) from grp_mem where gname=$qgname)=1\

2828 then (select user from grp_mem\

2829 where gname=$qgname and user='$user')\

2830 else '' end; "

2831 err NewGrpChk: $sql

2832 if [ -n "`query \"$sql\"`" ]; then

2833 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

2834 query "replace into grp_adm values($qgname, '$user');"

2835 fi

2836 fi

2837 else

2838 query "delete from grp_mem $cond;

2839 delete from grp_mem_s $cond;

2840 delete from grp_mem_m $cond;"

2841 action="から脱退しました。"

2842 fi

2843 smail "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

2844 $url?grp+$grid

2845 $user (`gecos $user`)さんが

2846 グループ $1

2847 $action

2848 EOF

2849 }

2850 grp_add_team() (

2851 # $1=grp-rowid $2=team $3...=user-rowid(s)

2852 grp=`getgroupbyid $1`

2853 team=$2; shift; shift

2854 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2855 { echo "BEGIN;"

2856 for user; do

2857 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2858 '$grp',\

2859 (SELECT name FROM user WHERE rowid=$user),\

2860 'team', 'string', '$team');"

2861 done

2862 echo "END;"

2863 } | query

2864 )

2865 grp_rm_team() (

2866 # $1=grp-rowid $2=team $3...=user-rowid(s)

2867 grid=$1

2868 qgrp=$(sqlquote "`getgroupbyid $grid`")

2869 team=$2; shift; shift

2870 [ -z "$grid" -o -z "$team" ] && return

2871 { echo "BEGIN;"

2872 for user; do

2873 echo "DELETE FROM grp_mem_m\

2874 WHERE gname=$qgrp \

2875 AND user=(SELECT name FROM user WHERE rowid=$user)\

2876 AND key='team' AND val='$team';"

2877 done

2878 cat<<-EOF

2879 DELETE FROM blog_s

2880 WHERE rowid=(

2881 SELECT rowid

2882 FROM blog_s a

2883 WHERE key='team'

2884 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2885 AND NOT EXISTS (SELECT * FROM grp_mem_m

2886 WHERE key='team' AND val=a.val -- a.val=team

2887 AND gname = (SELECT val FROM blog_s b

2888 WHERE a.id=b.id AND key='owner')

2889 ));

2890 EOF

2891

2892 echo "END;"

2893 } | query

2894 )

2895 grp_reg_adm() {

2896 # $1=grp-rowid $2...=user-rowid

2897 grid=$1

2898 grp=`getgroupbyid "$1"`

2899 if [ -z "$grp" ]; then

2900 echo "無効なグループIDです" | html p; return

2901 fi

2902 if ! isgrpowner $user "$grp"; then

2903 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2904 fi

2905 shift

2906 for urid; do

2907 newadm=`query "select name from user where rowid=$urid;"`

2908 if [ -z "$newadm" ]; then

2909 echo "指定ユーザIDがおかしいようです。" | html p; return

2910 fi

2911 err GRP_reg_adm: "replace into grp_adm values(`sqlquote $grp`, '$newadm');"

2912 err ismember $newadm $grp

2913 if ismember $newadm $grp; then

2914 # OK, go ahead

2915 getgname="(select gname from grp where rowid=$grid)"

2916 query "replace into grp_adm values($getgname, '$newadm');"

2917 # confirm insertion

2918 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2919 if [ -n "`query \"$sql;\"`" ]; then

2920 echo "追加完了: $newadm" | html p

2921 else

2922 echo "追加失敗($1 $urid)" | html p

2923 fi

2924 fi

2925 showgroup $grid

2926 done

2927 }

2928 dt_rowhack() {

2929 # From: <TR>

2930 # ....

2931 # <TD>rowclass=foo</TD>

2932 # </TR>

2933 # To: <TR class="foo">....<TD>foo</TD></TR>

2934 sed -e '

2935 /^<TR>/ {

2936 :loop

2937 s/\n//

2938 N

2939 /<\/TR>/ {

2940 s/\n//

2941 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

2942 n

2943 }

2944 $q

2945 b loop

2946 }'

2947 }

2948 dumptable() {

2949 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2950 # textのフィールドだけ全てダンプにしたほうがいいか

2951 # $DT_VIEW sets link

2952 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2953 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2954 VIEW=${DT_VIEW-replyblog}

2955 if [ -n "$VIEW" ]; then

2956 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

2957 fi

2958 # $DT_CHLD=ChildTable:BindColumn

2959 if [ -n "$DT_CHLD" ]; then

2960 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

2961 cntall="(select count($_i) from $_t where $_i=a.id)"

2962 # XXX: Dirty workaround for slow subquery of acclog

2963 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

2964 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

2965 cntnew="(select count(val) from ${_t}_s where key='ctime' \

2966 and id in (select id from $_t where $_i=a.id) \

2967 and val > coalesce((select time from myacclog where \

2968 tblrowid=a.rowid),\

2969 '1970-01-01'))"

2970 cnt="$cntnew as '新着', $cntall as '総数',"

2971 dt_class=" td2r td3r dumpblogs"

2972 fi

2973 # Construct join expression

2974 eav="" scols=""

2975 pk=`gettblpkey $2`

2976 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

2977 substr=${substr:-%s}

2978 for col in ${3:-`gettbl_s_cols $2`}; do

2979 valvar=val

2980 case $col in

2981 gecos) scols="$scols${scols:+, }${col#}"

2982 continue ;; # built-in column name

2983 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

2984 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

2985 case "$as" in

2986 *:*=*) cnd=${as#*:}

2987 h=${cnd%%=*} v=${cnd#*=}

2988 h=`sqlquotestr "$h"`

2989 v=`sqlquotestr "$v"`

2990 valvar="CASE val WHEN $h THEN $v END"

2991 as=${as%%:*} ;;

2992 esac

2993 ;;

2994 *) as=${col} ;;

2995 esac

2996 ss=`printf "$substr" "$valvar"`

2997 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

2998 scols="$scols${scols:+, }b.$as"

2999 done

3000 #case author when '$user' then a.rowid else '---' end as ID,

3001 sql=${DT_SQL:-"select \

3002 a.rowid as LINK,\

3003 $cnt\

3004 $scols from $2 a left join\

3005 (select $pk,$eav,

3006 max(case key when 'owner'

3007 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

3008 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

3009 ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"

3010 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3011 <div>

3012 <div class="dumptable">

3013 <table class="b$dt_class">

3014 `sq -header -cmd ".mode $1" $db "$presql$sql"`

3015 </table>

3016 </div>

3017 </div>

3018 EOF

3019 }

3020

3021 par2table() (

3022 # copy current parameters of par into destination table

3023 # $1=definition-file

3024 # Using $user and $session

3025 # Return value:

3026 # 0: Stored successfully

3027 # 1: Insufficient fillings

3028 # 2: No permission to modify the record

3029 # 3: Invalid rowid

3030 # 4: SUCCESS to delete

3031 # 5: Stop deletion for lack of confirm check

3032 # 6: Password length too short

3033 # 7: Password mismatch

3034 # 8: Old password incorrect

3035 rowid=`getpar rowid`

3036 if [ ! -e $1 ]; then

3037 echo "テーブル定義ファイルが見付かりません" | html p

3038 exit 1

3039 fi

3040 tbl=${1%.def}

3041 tbl=${tbl##*/}

3042 if [ -n "$rowid" ]; then # Modify existing entry

3043 if [ x"$tbl" = x"user" ]; then

3044 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3045 elif [ x"$tbl" = x"grp" ]; then

3046 sql="select gname from $tbl where rowid=$rowid;"

3047 ##err p2t:grp:q $sql

3048 isgrpowner $user "`query $sql`" && rowowner=$user

3049 else

3050 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3051 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3052 fi

3053 ### err rowowner=$rowowner

3054 if [ x"$user" != x"$rowowner" ]; then

3055 echo "他人のレコードはいじれないの" | html p

3056 return 2

3057 elif [ -z "$rowowner" ]; then

3058 echo "指定したレコードはないみたい" | html p

3059 return 3

3060 fi

3061 rm=`getpar rm` cfm=`getpar confirm`

3062 # Editing existent entry

3063 if [ x"$rm" = x"yes" ]; then

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

3065 query "delete from $tbl where rowid=$rowid;"

3066 return 4

3067 else

3068 echo "消去確認のチェックがないので消さなかったの..." | html p

3069 return 5

3070 fi

3071 fi

3072 fi

3073

3074 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

3076 # Update of existing record

3077 for col in `gettblcols $tbl`; do

3078 val=`getparquote $col`

3079 [ -z "$val" ] && continue

3080 ## err query "update $tbl set $col=$val where rowid=$rowid"

3081 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3082 sql="update $tbl set $col=$val where rowid=$rowid;"

3083 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3084 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3085 ## User name cannot be changed with interface provided with this

3086 ## script. But we offer the trigger to change owner user

3087 ## of blog_s table.

3088 #err "select quote($col) from $tbl where rowid=$rowid;"

3089 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3090 cat<<-EOF | query

3091 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3092 -- should use EXCLUSIVE transaction outside of this.

3093 SAVEPOINT par2table;

3094 $sql

3095 update blog_s set val=$val

3096 where key='owner' and val=$old;

3097 RELEASE SAVEPOINT par2table;

3098 EOF

3099 ## XX: DIRTY Hack Ends here

3100 ## We should keep blog's owner as a single column which has

3101 ## foreign key constraint with primary key of grp/user.

3102 else

3103 query "$sql"

3104 fi

3105 done

3106 # Then, set up $pval for further insertion of tbl_s and tbl_m

3107 for col in `gettblpkey $tbl`; do

3108 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3109 pval="$pval${pval:+, }\"$val\""

3110 done

3111 else

3112 # New entry

3113 # Generate values() for primary keys

3114 for col in `gettblpkey $tbl`; do

3115 # Genuine primary keys for _m and _s

3116 val=`getvalquote $tbl $col`

3117 [ -z "$val" ] && continue

3118 pval="$pval${pval:+, }$val"

3119 done

3120 ##err pval=$pval

3121 for col in `gettblfkey $tbl`; do

3122 # args for values() to insertion into master table

3123 val=`getvalquote $tbl $col`

3124 [ -z "$val" ] && continue

3125 formaster=$formaster"${formaster:+, }$val"

3126 done

3127 formaster="$pval${formaster:+, }$formaster"

3128 ## err formaster=$formaster

3129 if [ -z "$formaster" ]; then

3130 echo "項目を全て埋めてください" | html pre

3131 return 1

3132 fi

3133 ## err "replace into $tbl values($formaster);"

3134 query "replace into $tbl values($formaster);"

3135 ## Insertion to master table, done

3136 fi

3137

3138 for kt in s m; do

3139 tb2=${tbl}_$kt

3140 for col in `gettbl_${kt}_cols $tbl`; do

3141 ptype=`getpartype $col "limit 1"`

3142

3143 # First, check update of existing entries in _m

3144 if [ $kt = m ]; then

3145 # sessID|address.1.22|string|Somewhere-x.y.z

3146 sql=""

3147 ##err dots from query "select var from par where var like '$col.%';"

3148 for v in `query "select var from par where var like '$col.%';"`; do

3149 # v=address.1.22

3150 st_rowid=${v##*.}

3151 origcol=${v%%.*} # original column derived from

3152 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3153 ##case `getpartype $v` in

3154 ## err CASE `gettbl_coltype $tbl/$origcol` in

3155 ## err edit flag = `getpar action.$v`

3156 case `getpar action.$v` in

3157 rm)

3158 if [ x`getpar confirm.$v` = x"yes" ]; then

3159 newsql="delete from $tb2"

3160 else

3161 echo "削除確認未チェック" | html p

3162 fi ;;

3163 edit)

3164 case `gettbl_coltype $tbl/$origcol` in

3165 image|document|binary)

3166 file=$tmpd/`getparfilename $v`

3167 ## err type=file=$file

3168 [ -z "$file" ] && continue

3169 bn=`sqlquotestr "${file##*/}"`

3170 bin="X'"$(hexize "$file")"'"

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

3172 type=\"file:$ct\"

3173 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3174 cachedir=`getcachedir "$tbl/$rowid"`

3175 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3176 rm -rf $cachedir

3177 ;;

3178 *)

3179 newsql="update $tb2 set val=(select val from par where var \

3180 like '$col.%.$st_rowid')"

3181 ;;

3182 esac

3183 ;;

3184 *) # maybe "keep", do not modify value

3185 continue

3186 ;;

3187 esac

3188 # err newsql=$newsql

3189 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3190 done

3191

3192 if [ x"$bin" = x"NULL" ]; then

3193 ## err repl:normal sql=`echo $sql`

3194 query "$sql

3195 delete from $tb2 where type='string' and val='';"

3196 ## err repl:normal done

3197 else

3198 sqlfile="$tmpd/sqlf.$$"

3199 echo "$sql" > $sqlfile

3200 ## err repl:sqlfile=`ls -lF $sqlfile`

3201 query ".read $sqlfile"

3202 ## err repl:done

3203 fi

3204 # Rest of kt==m: set multiple mode

3205 nr=`getparcount $col`

3206 else

3207 nr=1 # for kt==s, number of records is 1

3208 fi

3209

3210 i=0

3211 while [ $i -lt $nr ]; do

3212 limit="limit 1 offset $i"

3213 i=$((i+1)) # increase beforehand against continue

3214 val=`getvalquote $tbl $col "$limit"`

3215 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3216 ## err $col=$val

3217 bin=NULL

3218 ## err partype$col=`getpartype $col "$limit"`

3219 case $ptype in

3220 file) file=$tmpd/`getparfilename $col "$limit"`

3221 ## err parfile-$col=$file

3222 [ -z "$file" ] && continue

3223 bin="X'"$(hexize "$file")"'"

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

3225 type=\"file:$ct\" ;;

3226 "*"*) continue ;; # foreign table

3227 *) type=\"string\" ;;

3228 esac

3229 case `gettbl_coltype $tbl/$col` in

3230 password) # special care for password

3231 # name={password,pswd1,pswd2}

3232 p1=`getpar pswd1 "$limit"`

3233 if [ -z "$p1" ]; then

3234 continue # SKIP password setting, if p1 is empty

3235 else

3236 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3237 ## err pswd=$pswd

3238 if pwcheck "$pswd"; then

3239 if [ x"$p1" = x"$p2" ]; then

3240 case "$p1" in

3241 ??????????*) ;;

3242 *) echo "パスワードは10字以上にしてください。" | html p

3243 return 6;;

3244 esac

3245 val="\"`echo $p1|mypwhash`\""

3246 else

3247 echo "2つの新パスワード不一致" | html p

3248 return 7

3249 fi

3250 else

3251 echo "旧パスワード違います" | html p

3252 return 8

3253 fi

3254 fi

3255 ;;

3256 esac

3257 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3258 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3259 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3260 if [ x"$bin" = x"NULL" ]; then

3261 ## err Normal-query: `echo $sql`

3262 query "$sql"

3263 else

3264 sqlfile="$tmpd/query.$$"

3265 echo "$sql" > $sqlfile

3266 ## err sqlfile=`ls -lF $sqlfile`

3267 query ".read $sqlfile"

3268 fi

3269 ## err p2t done

3270 done

3271 done

3272 done

3273 return 0

3274 ##err donee

3275 )

3276 genform() {

3277 # $1 = form definition file

3278 # $2, $3 (optional)= table name and ROWID

3279 # If $GF_VIEWONLY set and nonNull, output values without form

3280 # If $GF_ARGS set, use it as content-strings in the form

3281 # If $GF_OWNER set, use it as value of name="owner"

3282 # If $GF_STAGE set, use it as value of name="stage"

3283 forms="" hiddens="" rowid=$3

3284 if [ ! -e "$1" ]; then

3285 echo "そのようなデータベースはないようです($2)。" | html p

3286 return

3287 elif [ -n "$2" ]; then

3288 rec=`query "select * from $2 where rowid='$rowid';"`

3289 if [ -z "$rec" ]; then

3290 pk=`gettblpkey $2`

3291 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3292 rec=`query "select rowid from $2 where $pk='$rowid';"`

3293 rowid=$rec

3294 rec=$3

3295 fi

3296 if [ -z "$rec" ]; then

3297 echo "そんなレコードはないみたいね..." | html p

3298 return

3299 fi

3300 fi

3301 if [ -z "$GF_VIEWONLY" ]; then

3302 rm='<input id="rm" name="rm" type="checkbox"

3303 value="yes"><label for="rm">このエントリの削除</label>

3304 <span>ほんとうに消しますよ(確認)!

3305 <input name="confirm" type=checkbox value="yes">はい</span>'

3306 fi

3307 # Image Cache dir

3308 ## err genform: getcache=$2/$rowid

3309 td=`getcachedir "$2/$rowid"`

3310 while IFS=: read prompt name keytype type args; do

3311 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3312 sp="${args:+ }"

3313 form="" val=""

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

3315 # err genform2a: Seeking for "$2.$name, type=$type"

3316 rawval=`getvalbyid $2 $name $rowid $td`

3317 val=`echo "$rawval"|htmlescape`

3318 ## err genform3a: getvalbyid $2 $name $rowid $td

3319 ## err genform3b: val="[$val]" type="$type"

3320 fi

3321 if [ -n "$GF_VIEWONLY" ]; then

3322 is_hidden "$2" "$name" && continue

3323 fi

3324 case "$type" in

3325 text*)

3326 cgiform=cgi_multi_$type

3327 if [ -s $td/$name.count -a -n "$val" ]; then

3328 form=`$cgiform $name $td`

3329 val=$(echo "$val"|

3330 while read fn; do

3331 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3332 </td></tr>$nl"

3333 done)

3334 val="<table>$nl$val$nl</table>"

3335 else

3336 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3337 form=`cgi_$type $name "$rawval" "$args"`

3338 fi

3339 ;;

3340 [Rr][Aa][Dd][Ii][Oo])

3341 fh="<label><input type=\"radio\" name=\"$name\""

3342 form="`echo $args|sed -e \

3343 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3344 ;;

3345 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3346 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3347 ;;

3348 [Ss][Ee][Ll][Ee][Cc][Tt])

3349 fh="<select name=\"$name\">$nl"

3350 form=$(for l in $args; do

3351 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3352 done)

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

3354 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3355 fi

3356 form="$fh$form</select>"

3357 ;;

3358 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3359 if [ -s $td/$name.count ]; then

3360 form=`cgi_multi_file $name $td "$args"`

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

3362 hrfb="$myname?showattc+$2_m"

3363 val=$(echo "$rawval" \

3364 | while read fn; do

3365 data=`percenthex "$td/$fn"`

3366 #ct=`cat $td/$fn.content-type`

3367 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3368 ri=`cat "$td/$fn.rowid"`

3369 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3370 #imgsrc="<img src=\"data:$ct,$data\">"

3371 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3372 iconhref "$td/$fn" "$hrfb+$ri" ""

3373 done)

3374 fi

3375 else

3376 form="<input type=\"file\" name=\"$name\" $args>"

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

3378 imgs=$(echo "$rawval"\

3379 |while read fn;do

3380 data=`percenthex "$td/$fn"`

3381 echo "<img src=\"data:image/png,$data\">$fn<br>"

3382 done)

3383 form=$form"<br>$imgs"

3384 val=$imgs # 2015-06-15

3385 else

3386 form="<input type=\"file\" name=\"$name\" $args>"

3387 fi

3388 fi

3389 ;;

3390 [Hh][Ii][Dd][Dd][Ee][Nn])

3391 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3392 args="value=\"$GF_STAGE\""

3393 fi

3394 form="<input type=\"hidden\" name=\"$name\" $args>"

3395 prompt='' # Remove prompt

3396 ;;

3397 [Aa][Uu][Tt][Hh][Oo][Rr])

3398 [ -n "$GF_VIEWONLY" ] && continue

3399 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3400 prompt="" ;;

3401 [Oo][Ww][Nn][Ee][Rr])

3402 [ -n "$GF_VIEWONLY" ] && continue

3403 val=${GF_OWNER:-$val}

3404 val=${val:-$user}

3405 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3406 prompt="" ;;

3407 [Uu][Ss][Ee][Rr])

3408 # XXX: is null $user ok?

3409 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3410 [ -n "$GF_VIEWONLY" ] && continue

3411 form="$user"

3412 ;;

3413 [Pp]assword)

3414 [ -n "$GF_VIEWONLY" ] && continue

3415 form="`cgi_passwd`"

3416 val=""

3417 ;;

3418 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3419 [ -n "$GF_VIEWONLY" ] && continue

3420 if [ -z "$rowid" ]; then

3421 val=`genserial`

3422 fi

3423 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3424 prompt="" ;;

3425 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3426 prompt=""

3427 ;;

3428 parent|path|blog*)

3429 prompt=""

3430 ;;

3431 "*"*)

3432 tail=$tail"``"

3433 continue ;;

3434 esac

3435 if [ -n "$prompt" ]; then

3436 if [ -n "${GF_VIEWONLY}" ]; then

3437 form=$val

3438 else

3439 :

3440 fi

3441 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3442 else

3443 hiddens=$hiddens$nl"$form"

3444 fi

3445 done < $1

3446 # enctype="multipart/form-data"

3447 cat<<EOF

3448 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3449 ${rowid:+$rm}

3450 <table class="b $2">

3451 $forms

3452 </table>$hiddens

3453 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3454 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3455 EOF

3456 if [ -z $GF_VIEWONLY ]; then

3457 cat<<EOF

3458 <input type="submit" name="sub" value="OK">

3459 <input type="reset" name="res" value="Reset">

3460 EOF

3461 fi

3462 cat<<EOF

3463 $GF_ARGS</form>

3464 $tail

3465 EOF

3466 }

3467 edittable() {

3468 # $1=form-def $2=table $3 rowid

3469 genform "$@"

3470 }

3471 viewtable() {

3472 GF_VIEWONLY=1 genform "$@"

3473 }

3474 showattc() {

3475 # $1=table_m $2=rowid &optional $3=RawFlag

3476 ## err \$1=$1 \$2=$2 \$3=$3

3477 if ! isfilereadable $user $1 $2; then

3478 contenttype; echo

3479 echo "このファイルは管理者のみしか見られません" | html p

3480 putfooter; exit

3481 fi

3482 idir=`umask 002; mktempd` || exit 1

3483 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3484 bin=$idir/$myname-$$.bin

3485 sql="select quote(bin) from $1 where rowid='$2';"

3486 ## err showattc: sql: $sql

3487 sq $db "$sql" | unhexize > $bin

3488 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3489 type=${tv%//*} fn=${tv#*//}

3490 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3491 ct=${type#file:}

3492 case $ct in # all text/* changed to text/plain

3493 text/*)

3494 charset=`nkf -g $bin|cut -d' ' -f1`

3495 case $charset in

3496 ASCII*) charset="" ;;

3497 esac

3498 if [ -z "$3" ]; then

3499 ct="text/html${charset:+; charset=$charset}"

3500 link="?showattc+$1+$2+raw"

3501 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3502 | sed 's,^,<span></span>,' \

3503 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3504 -D_LINK_="$link" \

3505 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3506 exit $?

3507 fi

3508 ct="text/plain${charset:+; charset=$charset}"

3509 ;;

3510 esac

3511 contenttype "$ct"

3512 echo "Content-Disposition: filename=\"$fn\""

3513 echo "Content-Length: " `cat $bin | wc -c`; echo

3514 #echo "Content-Type: " ${type#file:}; echo

3515 cat $bin

3516 }

3517 #

3518 # Some default stupid handler on CGI values

3519 #

3520 default_storedb() {

3521 # ARG: $1=table-def-file

3522 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3523 tbl=`basename $1`

3524 tbl=${tbl%.def}

3525 cols="`grep :text $1|cut -d: -f2`"

3526 col=`echo "$cols"|head -1`

3527 vcol=`getpar $col`

3528 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3529 if [ -n "$vcol" ]; then

3530 par2table $1

3531 else

3532 return 2 # No insertion occurred

3533 fi

3534 }

3535

3536 default_view() { # $1=def-file

3537 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3538 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3539 default_storedb "$@"

3540 query "select rowid from $tbl order by rowid desc;" \

3541 | while read rowid; do

3542 viewtable $1 $tbl $rowid

3543 done | _m4 -D_TITLE_="$tbl" \

3544 -D_FORM_="`genform $1`" \

3545 -D_DUMPTABLE_="syscmd(cat)" \

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

3547 }

3548 default_viewtext() { # $1=def-file

3549 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3550 default_storedb "$@"

3551 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3552 | _m4 -D_TITLE_="$tbl" \

3553 -D_FORM_="`genform $1`" \

3554 -D_DUMPTABLE_="syscmd(cat)" \

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

3556 }

3557 default_smail() {

3558 default_storedb "$@"

3559 if [ $? -eq 2 ]; then

3560 _m4 -D_TITLE_="入力" \

3561 -D_FORM_="`genform $1`" \

3562 -D_DUMPTABLE_="" \

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

3564 return

3565 fi

3566 cond=""

3567 for pk in `gettblpkey $tbl`; do

3568 pv=$(sqlquote $(getpar $pk))

3569 cond="$cond${cond:+ and }$pk=$pv"

3570 done

3571 sql="select rowid from $tbl where $cond;"

3572 rowid=`query "$sql"`

3573 ## err smail1 - "$sql" "-> rowid=$rowid"

3574

3575 while IFS=: read prompt name keytype type args; do # Read from $1

3576 val=`getpar $name`

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

3578 text="$text

3579 $prompt

3580 $name=$val

3581 ---------------------------------------------------------"

3582 fi

3583 case "$type" in

3584 image|document|file)

3585 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3586 fns=$(echo "$fn"|while read fn; do

3587 err mv $tmpd/$fn.orig $tmpd/$fn

3588 mv $tmpd/$fn.orig $tmpd/$fn

3589 rm $tmpd/$fn.rowid # Remove cache flag

3590 ## err "`ls $tmpd/$fn`"

3591 echo $fn

3592 done)

3593 files="$files $fns"

3594 ;;

3595 esac

3596 done < $1

3597 ## err FILES=$files "`ls -lF $tmpd`"

3598 subj="from ${REMOTE_ADDR}"

3599 (echo "$url"

3600 echo "への書き込みがありました。"

3601 echo "------"

3602 echo "$text"

3603 ) | (cd $tmpd &&

3604 err LS="`ls -lF`" &&

3605 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3606 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3607 echo "以下の内容で送信しました。" | html p

3608 viewtable $1 $tbl \

3609 `query "select rowid from $tbl order by rowid desc limit 1;"`

3610 echo "戻る" | html a "href=\"?\""

3611 }