s4: 4d912c2d48df s4-funcs.sh

s4

view s4-funcs.sh @ 407:4d912c2d48df

Little bit spped up the judgement on necessity of cache re-creation

author	HIROSE Yuuji <yuuji@gentei.org>
date	Thu, 09 Feb 2017 11:20:10 +0900
parents	f50d4df067b5
children	e30fe590a53e

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 }

478 minitbl() {

479 sed -n '

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

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

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

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

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

485 H; # Concat this line to HoldSpace

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

487 $ b cont

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

489 }

490 :cont

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

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

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

494 # . of ^. is workaround for FreeBSD sed

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

496 p; # Print whole "table" element

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

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

499 }

500 x; # Back to the newest line

501 p; # Print rest'

502 }

503 acclog() (

504 # $1=table, $2=rowid

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

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

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

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

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

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

511 fi

512 )

513 gecos() (

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

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

516 )

517 setpar() {

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

519 }

520 replpar() {

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

522 }

523 getpar() {

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

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

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

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

528 fi

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

530 case "$var" in

531 owner)

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

533 echo $user; return

534 elif ismember $user $val; then

535 echo $val; return

536 fi ;;

537 esac

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

539 echo "$val"

540 }

541

542 getpartype() {

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

544 }

545 getparcount() {

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

547 }

548 getparfilename() {

549 # null if type of $1 is not file

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

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

552 }

553 sqlquote() {

554 (v="$1"

555 case "$v" in

556 "") return ;; # null

557 "X'"*) # quoted hex string

558 echo $1 ;;

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

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

561 echo "\"$v\""

562 return ;;

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

564 echo "\"$v\""

565 return ;;

566 *)

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

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

569 else

570 echo "\"$v\""

571 fi ;;

572 esac)

573 }

574 sqlquotestr() (

575 case "$1" in

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

577 echo "'$v'" ;;

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

579 esac

580 )

581 mktempd() {

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

583 }

584 getcachedir() { # $1=maintable

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

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

587 else

588 echo $tmpd/$thumbxy

589 fi

590 }

591 getval() {

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

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

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

595 echo "$user" ;;

596 stamp|datetime)

597 date "+%F %T" ;;

598 serial)

599 (s=`getpar $2`

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

601 *)

602 getpar "$2" "$3";;

603 esac

604 }

605

606 getvalquote() {

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

608 (v=`getval "$@"`

609 case "$v" in

610 "") echo NULL ;;

611 *) sqlquote "$v" ;;

612 esac)

613 }

614 getparquote() {

615 sqlquote `getpar $1`

616 }

617 getbinbyid() {

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

619

620 }

621 getvalbyid() {

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

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

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

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

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

627

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

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

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

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

632 return

633 fi

634 done

635 rowid=$3

636 pk=`gettblpkey $1`

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

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

639 td=${4:-$tmpd}

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

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

642 for kt in s m; do

643 t=${1}_$kt

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

645 vcount=1 # count(val)

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

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

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

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

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

651 if [ $kt = m ]; then

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

653 # Reset val to store filenames if type is string

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

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

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

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

658 i=0

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

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

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

662 i=$((i+1))

663 fn=$c.$i

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

665 case $type in

666 file:*)

667 #file=$td/$val

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

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

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

671 # FOR SPEED: Skip file generation if imgcache exists

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

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

674 && continue

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

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

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

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

679 select rowid from $cond $slice;

680 .output '$td/$fn'

681 select val from $cond $slice;

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

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

684 .output stdout

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

686 EOF

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

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

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

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

691 ls -lh "$file" |

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

693 case "$type" in

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

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

696 *|*:[Aa]pplication*)

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

698 png:- > "$file"

699 ;;

700 esac

701 ;;

702 *)

703 sq $db<<EOF

704 .output $td/$fn.rowid

705 select rowid from $cond $slice;

706 .output $td/$fn

707 select val from $cond $slice;

708 EOF

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

710 ;;

711 esac

712 done

713 else

714 rm -f $td/$c.count

715 case $type in

716 file:*)

717 echo "$val" \

718 | while read fn; do

719 file=$td/$fn

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

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

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

723 | unhexize > "$file"

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

725 case $type in

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

727 *:[Aa]pplication*)

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

729 png:- > $file ;;

730 esac

731 fi

732 done

733 ;;

734 esac

735 fi

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

737 return

738 fi

739 done

740 done)

741 }

742 getvalbypkey() (

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

744 pk=`gettblpkey $1`

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

746 getvalbyid "$1" "$2" $rowid $4

747 )

748 getvalbycond() {

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

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

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

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

753 getvalbyid "$1" "$2" $rowid "$4"

754 fi

755 }

756 getpwfield() {

757 # getpwfield user column

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

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

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

761 echo "$val"

762 return 0

763 else

764 return 1

765 fi

766 }

767 encode() {

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

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

770 sha1=sha1

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

772 sha1=sha1sum

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

774 sha1=gsha1sum

775 fi

776 fi

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

778 }

779 enjpeg() {

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

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

782 cjpeg="cjpeg"

783 else

784 cjpeg="convert - jpeg:-"

785 fi

786 fi

787 $cjpeg "$@"

788 }

789 mycrypt() (

790 key=$1 salt=$2

791 err \$2=$2

792 case $2 in

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

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

795 esac

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

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

798 )

799 hexize() {

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

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

802 hexize="xxd -p"

803 else

804 hexize_hd() {

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

806 }

807 hexize="hexize_hd"

808 fi

809 fi

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

811 }

812 unhexize() {

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

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

815 unhex="xxd -p -r"

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

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

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

819 EOF

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

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

822 # world writable...:)

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

824 fi

825 fi

826 cat "$@" | $unhex

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

828 }

829 percenthex() {

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

831 }

832 htmlescape() {

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

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

835 }

836 enascii() {

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

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

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

840 else

841 enascii_now=`date +%FT%T`

842 enascii_sed() {

843 nkf -Z0Z1Z2 \

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

845 }

846 enascii="enascii_sed"

847 fi

848 fi

849 cat "$@" | $enascii

850 }

851 size_h() {

852 i="$1" oi=$1

853 set -- B B KB MB GB TB

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

855 oi=$i

856 i=$((i/1024))

857 shift

858 done

859 echo ${oi}$1

860 }

861 gettblconf() {

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

863 ## tconfs=`sq $db \

864 tconfs=`query \

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

866 fi

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

868 }

869 gettblkeys() {

870 # $1=tbl

871 gettblconf

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

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

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

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

876 case $type in

877 *p*)

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

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

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

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

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

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

884 esac

885 case $type in

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

887 esac

888 done

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

890 }

891 gettblpkey() {

892 # $1=tbl

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

894 }

895 gettblfkey() {

896 (x=`gettblkeys $1`

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

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

899 }

900 gettblcols() {

901 (x=`gettblkeys $1`

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

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

904 }

905 gettbl_s_cols() {

906 (x=`gettblkeys $1`

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

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

909 }

910 gettbl_m_cols() {

911 (x=`gettblkeys $1`

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

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

914 }

915 gettbl_h_cols() {

916 (x=`gettblkeys $1`

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

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

919 }

920 gettbl_coltype() (

921 gettblconf

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

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

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

925 )

926 is_hidden() {

927 # $1=Tbl $2=col

928 gettblconf

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

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

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

932 case $x in

933 *h*) return 0 ;;

934 *) return 1 ;;

935 esac

936 }

937

938 dbsetbyid() {

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

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

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

942 quotedp=$(sqlquotestr "$p")

943 unset primary update

944 gettblconf

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

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

947 #err conf=$conf

948 case ${conf#*=} in

949 p*) primary=1 ;;

950 f*) update=1 ;;

951 u*) ;;

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

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

954 esac

955 #err t=$t

956 type=string fn=""

957 case $conf in

958 */password)

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

960 ;;

961 */image*|*/document*)

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

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

964 ;;

965 esac

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

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

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

969 if [ "$primary" ]; then

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

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

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

973 elif [ "$update" ]; then

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

975 else

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

977 fi

978 )

979 }

980 expire() (

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

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

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

984 )

985 addsession() {

986 # expireをセット

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

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

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

990 # Remove old session parameters

991 now=`expire now`

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

993 }

994 gencookie() (

995 for kv; do

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

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

998 done

999 )

1000 contenttype() {

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

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

1003 }

1004 putheader() {

1005

1006 }

1007 putfooter() {

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

1009 }

1010 getcookie() (

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

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

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

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

1015 done

1016 )

1017 genrandom() {

1018 # $1=columns (default: 10)

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

1020 }

1021 genserial() {

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

1023 }

1024 smail() {

1025 # smail rcpts subj (file)

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

1027 # $MAIL_FROM <- From: header value

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

1029 rcpt=`echo $1` # strip newlines

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

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

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

1033 }

1034 setviastring() {

1035 table=$1

1036 oifs="$IFS"

1037 IFS="&"

1038 for us in $2; do

1039 k=${us%%=*}

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

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

1042 #echo $k=$v

1043 done

1044 IFS="$oifs"

1045 }

1046 checkdomain() (

1047 # Check the validity of domain by referring DNS

1048 item=$1

1049 err checkdomain $1

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

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

1052 )

1053 pwcheck() {

1054 # $1=passwd

1055 dbpswd=`getpwfield $user pswd`

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

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

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

1059 }

1060 mypwhash() {

1061 mycrypt `cat` `genrandom 5`

1062 }

1063 wasureta() {

1064 user=$1

1065 if ! checkdomain $user; then

1066 contenttype; echo

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

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

1069 putfooter

1070 exit 0

1071 fi

1072 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1074 encpswd=`echo $newpswd|mypwhash`

1075 dbsetbyid user $user pswd "$encpswd"

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

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

1078 $msgdir/mail-newaccount.m4 \

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

1080 | smail $user "New Account"

1081 }

1082 checkauth() {

1083 user=`getpar user`

1084 skc=`getpar skey` # from cookie

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

1086 skey="`getpwfield $user skey`"

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

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

1089 return 0

1090 fi

1091 fi

1092 pswd=`getpar pswd`

1093 quser=`sqlquotestr "$user"`

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

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

1096 return 1

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

1098 wasureta $user

1099 return 1 # wasureta error

1100 fi

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

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

1103 if pwcheck "$pswd"; then

1104 newsession=`genrandom 50`

1105 dbsetbyid user $user skey "$newsession"

1106 gencookie "user=$user" "skey=$newsession"

1107 return 0

1108 fi

1109 return 2 # Password mismatch

1110 }

1111 showlogin() {

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

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

1114 $layout/login.m4.html

1115 exit 0

1116 }

1117 dologin() {

1118 checkauth

1119 st=$?

1120 if [ $st != 0 ]; then

1121 contenttype; echo

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

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

1124 showlogin # and EXIT

1125 fi

1126 }

1127

1128 # Do instant jobs here

1129 dbsetup

1130 trap cleanup INT HUP EXIT TERM PIPE

1131 # trap cleanup INT HUP

1132

1133 err() {

1134 echo "$@" 1>&3

1135 }

1136

1137 cgiinit() {

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

1139 tmpf=tmp/stream

1140 tmpd=`tmpd=$tmpdir mktempd`

1141 tmpfiles=$tmpfiles" $tmpd"

1142 addsession $session

1143 getcookie

1144 case "$REQUEST_METHOD" in

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

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

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

1148 # cat > $tmpf # too much?

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

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

1151 s="`cat tmp/stream`"

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

1153 ;;

1154 esac

1155 case "$CONTENT_TYPE" in

1156 *boundary*)

1157 bndry=${CONTENT_TYPE#*boundary=}

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

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

1160 do

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

1162 #echo u=$us

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

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

1165 # err k=$k v=$v

1166 case "$k" in

1167 *:filename)

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

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

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

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

1172 :

1173 fi

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

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

1176 [Ii]mage/x-xcf)

1177 bzip2 "$tmpd/$v"

1178 v=${v}.bz2

1179 ;;

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

1181 [Ii]mage/*)

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

1183 ;;

1184 esac

1185 ;;

1186 *)

1187 type='string'

1188 ;;

1189 esac

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

1191 setpar "$k" "$type" "$v"

1192 done

1193 ;;

1194 *)

1195 setviastring par "$s"

1196 ;;

1197 esac

1198 }

1199 email4group() {

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

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

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

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

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

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

1206 and s.key='email'

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

1208 query "$sql"

1209 }

1210 email4groupbyuid() {

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

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

1213 uids=`echo "$@"`

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

1215 sql="WITH

1216 grpemails AS (

1217 SELECT gname, user, val email

1218 FROM grp_mem NATURAL JOIN grp_mem_s

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

1220 useremails AS (

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

1222 FROM user

1223 LEFT JOIN user_s

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

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

1226 FROM useremails u LEFT JOIN grpemails g

1227 ON u.name=g.user

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

1229 ## err email4gByid `echo $sql`

1230 query "$sql"

1231 }

1232 collectemail() (

1233 # Collect email addresses for group $1

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

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

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

1237 for e; do

1238 if isuser "$e"; then

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

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

1241 else

1242 qgrp=`sqlquote "$e"`

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

1244 gmem="grp_mem"

1245 else

1246 tm=`sqlquote "$TEAM"`

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

1248 fi

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

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

1251 $gmem g left join grp_mem_s s

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

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

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

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

1256 query "$sql"

1257 fi

1258 done

1259 )

1260 sendinvitation() (

1261 # $1=email

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

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

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

1265 gecos=`gecos`

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

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

1268 _m4 -D_URL_="$urlbase" \

1269 -D_USER_="$name" \

1270 -D_EMAIL_="$1" \

1271 -D_REGIST_="$regist" \

1272 -D_ADMIN_="$admin" \

1273 $msgdir/mail-invite.m4 \

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

1275 return 0

1276 )

1277 emaildomaincheck() {

1278 case "$1" in

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

1280 *@*)

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

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

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

1284 return 2

1285 fi

1286 return 0

1287 ;;

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

1289 esac

1290 }

1291 invite() {

1292 email=`getpar email`

1293 case $email in

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

1295 *@*)

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

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

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

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

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

1301 elif sendinvitation $email; then

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

1303 fi ;;

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

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

1306 esac

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

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

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

1310 fi

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

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

1313 $layout/html.m4.html $layout/invite.m4.html

1314 }

1315 regist() {

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

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

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

1319 echo "bye bye" | html p

1320 reutrn

1321 fi

1322 email=`session=$1 getpar invite`

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

1324 cat<<EOF

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

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

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

1328 EOF

1329 return

1330 fi

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

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

1333 # Fake login password to wasureta

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

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

1336 wasureta $email

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

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

1339 addsession $1 # for removal after 1 minute

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

1341 $layout/login.m4.html

1342 return

1343 }

1344 group_safename() {

1345 # Convert $1 to safe group name

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

1347 }

1348 groupupdate() {

1349 gname=`getpar gname`

1350 qgname=`sqlquote $gname`

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

1352 # See ALSO same job in showgroup()

1353 newgname=`group_safename "$gname"`

1354 err newgname=$newgname

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

1356 err NewGNAME: gname=$newgname

1357 gname=$newgname

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

1359 replpar gname string "$gname"

1360 fi

1361 # Name confliction check

1362 parow=`getpar rowid`

1363 ## err parow=$parow

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

1365 query "BEGIN EXCLUSIVE;"

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

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

1368 if [ $count -gt 0 ]; then

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

1370 query "END;"

1371 return

1372 fi

1373 par2table $formdir/grp.def

1374 query "END TRANSACTION;"

1375 # Remove orphan

1376 : <<EOF

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

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

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

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

1381 EOF

1382 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1386 sql="delete from blog where id in

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

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

1389 query "$sql";

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

1391 return # and return

1392 fi

1393 fi

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

1395 fi

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

1397 grid=$(query $sql)

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

1399 grp $grid

1400 }

1401 groupclone() {

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

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

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

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

1406 return

1407 fi

1408 i=0

1409 while true; do

1410 copy="-copy$i"

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

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

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

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

1415 i=$((i++))

1416 continue

1417 fi

1418 break

1419 done

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

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

1422 query<<-EOF

1423 BEGIN;

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

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

1426 SELECT $newqname, key, val

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

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

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

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

1431 -- Copy members and their configuration --

1432 REPLACE INTO grp_mem SELECT $newqname, user

1433 FROM grp_mem WHERE gname=$qgrp;

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

1435 FROM grp_mem_s WHERE gname=$qgrp;

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

1437 FROM grp_mem_m WHERE gname=$qgrp;

1438 -- Copy administrators --

1439 REPLACE INTO grp_adm SELECT $newqname, user

1440 FROM grp_adm WHERE gname=$qgrp;

1441 COMMIT;

1442 EOF

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

1444 STOPCLONEMSG=1 groupconf "$newrowid"

1445 }

1446 groupman() {

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

1448

1449 GF_STAGE="grpconf"

1450 GF_STAGE=groupupdate

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

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

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

1454 -D_DUMPTABLE_="syscmd(cat)" \

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

1456 }

1457 userconf() {

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

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

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

1461 }

1462 groupconf() {

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

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

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

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

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

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

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

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

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

1472 | html p

1473 fi

1474 }

1475 mems() {

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

1477 kwd=`getpar kwd`

1478 listmember $kwd

1479 }

1480 grps() {

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

1482 kwd=`getpar kwd`

1483 listgroup $kwd \

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

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

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

1487 $layout/form+dump.m4.html

1488 }

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

1490 gpg=`getpar grp`

1491 grid=${1:-$gpg}

1492 grp=`getgroupbyid "$grid"`

1493 ## . ./s4-blog.sh

1494 jg=`getpar joingrp`

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

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

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

1498 fi

1499 htmlheader=$layout/html.m4.html

1500 showgroup "$grid"

1501 }

1502 sql4interestblogs() {

1503 cat<<EOF

1504 CREATE TEMPORARY VIEW interestblogs AS

1505 SELECT blog.rowid rid, id, author

1506 FROM blog

1507 NATURAL JOIN

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

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

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

1511 WHEN (SELECT user FROM grp_mem

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

1513 THEN 0

1514 ELSE 1

1515 END;

1516 EOF

1517 }

1518 listnewblogsql() { # $1=user

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

1520 WHERE user='$user'

1521 AND tblrowid=$blogreadflagrowid),

1522 "0");"`

1523 cat<<EOF

1524 `sql4interestblogs`

1525 WITH article_ctime as (

1526 SELECT id,blogid,author,val ctime

1527 FROM article join article_s s using(id)

1528 WHERE s.key='ctime'

1529 ), blog_title_owner as (

1530 SELECT blg.rid brid, id,

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

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

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

1534 ), visited AS (

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

1536 FROM blog_title_owner b, article_ctime ac

1537 JOIN acclog al

1538 ON b.id=ac.blogid

1539 AND al.tbl='blog'

1540 AND al.tblrowid=brid

1541 AND al.user='$user'

1542 AND al.time < ctime

1543 AND '$deftime' < ctime

1544 ), unvisited as (

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

1546 FROM blog_title_owner b, article_ctime ac

1547 ON b.id=ac.blogid

1548 WHERE brid NOT IN (SELECT tblrowid FROM acclog

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

1550 AND ctime > '$deftime'

1551 ), blogall as (

1552 /* ---------------------------------------

1553 Collect new articles with dividing them into visited and unvisited

1554 separately, because constructing joined table of artice X acclog

1555 tends to become HUGE combinations.

1556 --------------------------------------- */

1557 SELECT * FROM visited UNION SELECT * FROM unvisited

1558 ), news as (

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

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

1561 from blogall bl /* left join a_u l

1562 on bl.brid=l.tblrowid */

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

1564 LIMIT 10

1565 ) SELECT brid LINK, "新着",

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

1567 ctime, title,

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

1569 FROM news;

1570 EOF

1571 }

1572

1573 search_form() {

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

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

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

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

1578 @week → 最近一週間

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

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

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

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

1583 auth=""

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

1585 case "$1" in

1586 author=*)

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

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

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

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

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

1592 ;;

1593 esac

1594 cat<<-EOF

1595 <div class="fr">

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

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

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

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

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

1601 </form>

1602 </div>

1603 EOF

1604 }

1605

1606 imgsrc_cache() (

1607 # $1 = directory for cache'ing

1608 # $2 = table (user_m or grp_m)

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

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

1611 dir="$1" tbl="$2"

1612 keycond="$3"

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

1614 ORDER BY rowid DESC LIMIT 1"

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

1616 tmpf=$tmpd/imgsrc_cache.$$

1617 case "$4" in

1618 [Ss]) size=S ;;

1619 [Oo]) size=O ;;

1620 *) size=M ;;

1621 esac

1622 # ImageCache filename storing schema:

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

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

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

1626 valtype=`query "$sql0"`

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

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

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

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

1631 case "$filetype" in

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

1633 esac

1634 fi

1635 cacheimg_S=$dir/S_$filename

1636 cacheimg_M=$dir/M_$filename

1637 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1645 else

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

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

1648 fifo2=$fifo.2

1649 mkfifo $fifo $fifo2

1650 fmt=${filename##*.}

1651 ## [[ NOTE ]]

1652 ## a. convert oldimage newimage

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

1654 ## b is much smaller than a

1655 cat $tmpf | unhexize \

1656 | tee $fifo \

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

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

1659 | tee $fifo2 \

1660 | convert - "$cacheimg_M" &

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

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

1663 | convert - "$cacheimg_S" &

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

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

1666 echo '">'

1667 echo "$sum" > $sumfile

1668 fi

1669 ## Now preparing cache image, done.

1670 ## Store this information to DB

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

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

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

1674 query <<-EOF

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

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

1677 EOF

1678 )

1679

1680 showhome() {

1681 # $1=userRowIdToShow

1682 err showhome \$1=$1

1683 case "$1" in

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

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

1686 esac

1687 ## err ShowHome: uname=$uname

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

1689 gecos=`gecos "$uname"`

1690 ## err SH:gecos=$gecos

1691 GF_VIEWONLY=1

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

1693 search_form_args=""

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

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

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

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

1698 # Display folders

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

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

1701 and type like 'file:%';"

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

1703 nfile=`query "$sql"`

1704 # err nfile=$nfile

1705 if [ $nfile -gt 0 ]; then

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

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

1708 fi

1709 else

1710 search_form_args="author=$uname"

1711 fi

1712 . ./s4-blog.sh

1713

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

1715 search_form $search_form_args > $sf

1716 echo "$gecos さん" > $tf

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

1718 viewtable $formdir/user.def user $1

1719 echo "</div>"

1720 } > $pf

1721

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

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

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

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

1726 if true; then

1727 tbl=user_m

1728 enticond="name='$uname'"

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

1730 else

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

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

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

1734 echo '">'

1735 }

1736 fi > $imf

1737 fi

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

1739 val='$uname';"`

1740 listblog $uname > $bf

1741

1742 hometail=$tmpd/tail.$$

1743 mkfifo $hometail

1744

1745 #Calling listgroupbytable, originally here

1746

1747 (

1748 # Display Most Recent Entry

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

1750 shortval=${shortval:-val}

1751

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

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

1754 ctime,

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

1756 (SELECT gecos FROM gecoses

1757 WHERE name=(SELECT val FROM blog_s

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

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

1760 FROM blog b

1761 JOIN

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

1763 FROM article a, article_s s

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

1765 GROUP BY blogid ORDER BY val DESC LIMIT 50

1766 ) m

1767 ON b.id=m.blogid;"

1768 # This should be as follows

1769 : <<EOF

1770 WITH arts AS(

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

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

1773 FROM article a NATURAL JOIN article_s s

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

1775 GROUP by s.id

1776 )

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

1778 FROM arts a0

1779 JOIN

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

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

1782 ORDER BY ctime DESC LIMIT 50;

1783 EOF

1784

1785 cat<<-EOF

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

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

1788 <div class="lcto">

1789 `DT_VIEW=replyblog dumptable html blog`

1790 </div>

1791 EOF

1792 unset DT_SQL

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

1794 # Display NEWS

1795 # 2016-06-26

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

1797 acclog blog $blogreadflagrowid

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

1799 fi

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

1801 sql=`listnewblogsql "$user"`

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

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

1804 cont=$((cont-1))

1805 err newcount=$cont

1806 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

1813 `cgi_submit '確定'`

1814 `cgi_hidden read yes`

1815 EOF

1816 echo "$new10 "

1817 echo "</div>"

1818 fi

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

1820 : # DT_SQL=

1821 fi

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

1823 #

1824 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

1830 -D_NBLOG_="$nblog" \

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

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

1833 $layout/html.m4.html $layout/home.m4.html

1834

1835 # Record access log

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

1837 }

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

1839 contenttype; echo

1840 ## err commission: "$@"

1841 gname=`getgroupbyid $1`

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

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

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

1845 grp_reg_adm "$@"

1846 else

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

1848 fi

1849 }

1850 listgroupbytable() {

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

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

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

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

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

1856 NGsql="select distinct tag from\

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

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

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

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

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

1862 for tag in `query "$sql"`

1863 do

1864 ## err ListGrp: tag=$tag

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

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

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

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

1869 as rowid,

1870 gname,

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

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

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

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

1875 ## err PersonalGroupList= `echo $sql`

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

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

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

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

1880 echo '</table>'

1881 done

1882 echo '</div>'

1883 }

1884 iconhref() (

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

1886 data=`percenthex "$1"`

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

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

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

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

1891 )

1892 iconhref2() (

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

1894 src=$1

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

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

1897 )

1898 listentry() (

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

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

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

1902 cond='' hiddens=''

1903 offset=`getpar offset`

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

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

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

1907 limit=30

1908 dir=`getcachedir "$1"`

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

1910 hrb="$myname?home"

1911 deficon=person-default.png

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

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

1914 gcs=gecos

1915 else # if group

1916 hrb="$myname?grp"

1917 deficon=group-default.png

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

1919 gcs=name

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

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

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

1923 ## err tagconv=$tagconv

1924 fi

1925 fi

1926 if [ ! -d $dir ]; then

1927 mkdir -p $dir

1928 fi

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

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

1931 fi

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

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

1934 fi

1935

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

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

1938 ##qgrp=`sqlquote $grp`

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

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

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

1942 quote(a.$nm) as qname,

1943 (SELECT val FROM ${tbl}_s

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

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

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

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

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

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

1950 end

1951 as name,

1952 b.tag,

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

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

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

1956 then '(ADMIN)'

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

1958 then '(Member)'

1959 when '$iamowner' = '' then ''

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

1961 CASE '$entity'

1962 WHEN 'グループ'

1963 THEN coalesce(

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

1965 'open')

1966 ||

1967 CASE WHEN '$user'

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

1969 THEN ' ismember'

1970 ELSE ''

1971 END

1972 ELSE 'user'

1973 END regmode

1974 from $tbl a left join

1975 (select $nm as name,

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

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

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

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

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

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

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

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

1984 # Note that mtime is stored only in grp_s.

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

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

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

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

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

1990 cgi_form $stage <<EOF

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

1992 `cgi_text kwd $kwd`</label>

1993 EOF

1994 echo '</div>'

1995 fi

1996 hiddens="$hiddens

1997 `cgi_hidden kwd \"$kwd\"`

1998 `cgi_hidden stage \"$stage\"`"

1999 cat<<EOF

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

2001 EOF

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

2003 nextbtn=$(

2004 cat<<EOF

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

2006 `cgi_submit 次の${limit}件`

2007 $hiddens

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

2009 EOF

2010 )

2011 fi

2012 if [ $offset -gt 0 ]; then

2013 prevbtn=$(

2014 cat<<EOF

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

2016 `cgi_submit 前の${limit}件`

2017 $hiddens

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

2019 EOF

2020 )

2021 fi

2022 pnbtn="$nextbtn$prevbtn"

2023 echo $pnbtn

2024

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

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

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

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

2029 err name=$name owner=$ownerp lnk=$lnk

2030 err newlnk=$lnk regmode=$regmode

2031 icondir=$dir/$id

2032 # Pick up only last icon

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

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

2035 | _m4 $tagconv

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

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

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

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

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

2041 else

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

2043 fi

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

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

2046 else

2047 cond="$nm=$qname"

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

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

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

2051 # err "img=[$img]"

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

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

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

2055 echo "</a>"

2056 else

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

2058 fi

2059 fi

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

2061 echo "</div>"

2062 done

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

2064 )

2065 listmember() {

2066 listentry user "$@"

2067 }

2068 listgroup() {

2069 listentry group "$@"

2070 }

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

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

2073 query "SELECT DISTINCT hex(val) FROM grp_mem_m

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

2075 }

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

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

2078 grid=`getpar grid`

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

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

2081 else

2082 grid=$1

2083 fi

2084 grp=`getgroupbyid $grid`

2085 qgrp=`sqlquote $grp`

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

2087 if isgroup "$grp"; then

2088 tf=$tmpd/title.$$

2089 bodyclass=`query "SELECT val FROM grp_s

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

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

2092 ismember="ismember"

2093 qgrp=`sqlquote $grp`

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

2095 else

2096 ismember="" # bodyclass="group"

2097 fi

2098 bodyclass="$bodyclass grouphome"

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

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

2101 _m4 -D_TITLE_="spaste(\`$tf')" \

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

2103 -D_BODYCLASS_="$bodyclass" \

2104 -D_DUMPTABLE_="" \

2105 $htmlheader $layout/form+dump.m4.html

2106 # $htmlheader is defined in grp()

2107 else # if $grp is removed at par2table

2108 listgroup

2109 fi

2110 }

2111 showgroupsub() {

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

2113 # Using $ismember

2114 rowid=$2

2115 grp=`getgroupbyid $2`

2116 qgrp=`sqlquote $grp`

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

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

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

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

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

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

2123 return

2124 fi

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

2126 enticond="gname=$qgrp"

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

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

2129 cat<<-EOF

2130 <p class="groupimg">

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

2132 EOF

2133 fi

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

2135 viewtable $1 grp $rowid

2136 echo "</div>"

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

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

2139 iamowner=$rowid

2140 colmd=" mode"

2141 fi

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

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

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

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

2146 cat<<EOF

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

2148 <div class="fold clear">

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

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

2151 <div>

2152 `cgi_textarea message "" "cols=60"`

2153 `cgi_submit 送信`

2154 `cgi_reset リセット`

2155 </div>

2156 `cgi_hidden grp $rowid`

2157 </div></form>

2158 EOF

2159 fi

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

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

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

2163 else

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

2165 fi

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

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

2168 and key='email';"`

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

2170 ## and key='email';"

2171 ##err email=$eml

2172 cat <<EOF

2173 <div class="fold clear">

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

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

2176 <div>

2177 EOF

2178 cgi_form grp <<EOF

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

2180 <table class="b">

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

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

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

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

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

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

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

2188 </table>

2189 `cgi_hidden grp $rowid`

2190 EOF

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

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

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

2194 fi

2195 echo '</div></div>'

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

2197 cgi_form searchart<<EOF

2198 <label>`cgi_text kwd`という語を含むコメントを検索</label>

2199 `cgi_hidden owner $grp`

2200 EOF

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

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

2203 DT_CHLD=article:blogid \

2204 DT_VIEW=replyblog dumptable html blog \

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

2206

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

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

2209 cm="?commission+$rowid"

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

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

2212 # team list

2213 hexteams=`hexteams "$grp"`

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

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

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

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

2218 SELECT val TEAM,

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

2220 MEMBERS

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

2222 EOF

2223 echo '</table></div>'

2224 fi

2225 }

2226 grp_getbodyclass() {

2227 # Get css class name for document.

2228 # `moderated' for moderated groups

2229 # `ismember' for groups where user belongs

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

2231 # $user=userNameCurrentlyLogin

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

2233 qgrp=`sqlquote "$1"`

2234 query<<-EOF

2235 SELECT coalesce(

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

2237 'open')

2238 ||

2239 CASE WHEN '$user'

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

2241 THEN ' ismember'

2242 ELSE ''

2243 END;

2244 EOF

2245 }

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

2247 err GRP_ACTION:IN

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

2249 grp=`getgroupbyid "$grid"`

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

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

2252 fi

2253 if ! ismember $user $grp; then

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

2255 fi

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

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

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

2259 $layout/html.m4.html

2260

2261 isowner=""

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

2263 usel=`getpar usel`

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

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

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

2267 text=`getpar text`

2268

2269 rm=`getpar rm` cfm=`getpar confirm`

2270 ## err rm=$rm cfm=$cfm

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

2272 if [ "$isowner" ]; then

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

2274 # Eliminate

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

2276 for tbl in grp_mem grp_mem_s grp_mem_m; do

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

2278 # echo "sql=$sql"

2279 query "$sql"

2280 err rmGRPuser "$sql"

2281 done

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

2283 #err num=$num

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

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

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

2287 # err `echo "$sql"`

2288 html pre<<EOF

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

2290 `query "$sql"`

2291 EOF

2292 fi

2293 else

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

2295 return

2296 fi

2297 else # not Group Owner

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

2299 return

2300 fi

2301 cat<<EOF

2302

2303 EOF

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

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

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

2307 return 0

2308 fi

2309 gecos=`gecos $user`

2310 mkfrom=`getpar mkfrom`

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

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

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

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

2315 mail_from="$safegc <$fromad>"

2316 else

2317 mail_from="$admin"

2318 fi

2319 MAIL_FROM=$mail_from \

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

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

2322 $url

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

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

2325 ----------------------------------------------------------

2326 $text

2327 EOF

2328 if [ $? = 0 ]; then

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

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

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

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

2333 html pre<<EOF

2334 `query "$sql"`

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

2336 EOF

2337 err SendDone: `echo $sql`

2338 fi

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

2340 grp_reg_adm $grid $usel

2341 elif [ x"$rm" = x"addteam" ]; then

2342 team=`getpar team|sed "s/'/''/g"` # for single quotation

2343 newteam=`echo "$team"|tr -d ,`

2344 if [ x"$team" != x"$newteam" ]; then

2345 echo "チーム名に使えない文字を除去しました" | html p

2346 team=newteam

2347 fi

2348 if [ -z "$team" -o x"$team" = x"なし" ]; then

2349 cat<<-EOF | html p

2350 有効なチーム名を入力してください。

2351 カンマだけ、「なし」という名前は使えません。

2352 EOF

2353 echo "有効なチーム名を入力してください。" | html p

2354 else

2355 grp_add_team $grid "$team" $usel

2356 fi

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

2358 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2359 rmteam=`getpar rmteam|sed "s/'/''/g"`

2360 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2361 gname='$grp' AND user='$user' AND key='team'\

2362 AND val='$rmteam';\"`" ]; then

2363 grp_rm_team $grid "$rmteam" $usel

2364 else

2365 echo "所属していないチームの除去操作はできません。"|html p

2366 fi

2367 else

2368 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2369 fi

2370 fi

2371 fi

2372 # New entry

2373 sql="select /* Ahh, ugly SQL, I wanna fix... */

2374 case

2375 when (select user from grp_adm where

2376 gname=(select gname from grp where rowid=$grid)

2377 and user=a.name) is not null

2378 then 'k'

2379 else ''

2380 end || a.rowid||

2381 ','||a.gecos as NAME,

2382 (SELECT count(author) /* Put post count for scoring 2016-08-01 */

2383 FROM article NATURAL JOIN article_s

2384 WHERE blogid IN

2385 (SELECT id FROM blog_s

2386 WHERE key='owner'

2387 AND val=(SELECT gname FROM grp where rowid=$grid))

2388 AND author=a.name AND key='text')

2389 as POST,

2390 (SELECT group_concat(val, ',')

2391 FROM grp_mem_m

2392 WHERE gname='$grp' AND user=a.name AND key='team') as TEAM

2393 FROM

2394 gecoses a

2395 WHERE name in (select user from grp_mem where

2396 gname=(select gname from grp where rowid=$grid))

2397 ORDER by a.gecos;"

2398 ## err grpaction: "`echo \"$sql\"`"

2399 tf=$tmpd/title.$$

2400 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2401 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2402 for=\"cmadmin\">グループ管理者委任</label>

2403 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2404 </p></div>"

2405 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2406 for=\"conf\">グループ登録解除</label>

2407 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2408 <p>この操作による通知は本人に行きません。

2409 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2410 </div>"

2411 # Get team list to which current user belongs into $hexteams

2412 myhexteams=$(hexteams "$grp" "$user")

2413 allhexteams=$(hexteams "$grp")

2414 if [ -n "$myhexteams" ]; then

2415 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2416 for=\"cmrmteam\">チーム属性除去</label>

2417 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2418 を除去します: `cgi_checkbox teamconfirm yes` 確認

2419 <p>この操作による通知は本人に行きません。

2420 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2421 </div>

2422 "

2423 fi

2424 b1='<label> <input type="checkbox" name="usel" value="'

2425 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2426 #b2='"> <span>' b3='</span></label>'

2427 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2428 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2429 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2430 cgi_form grpaction<<EOF \

2431 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2432 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2433 | _m4 -D_TITLE_="spaste(\`$tf')" \

2434 -D_SUBTITLE_="チェック後操作ボタン" \

2435 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2436 $layout/form+dump.m4.html

2437 <p>下でチェックした人を対象として:</p>

2438 <div class="foldtabs">

2439 `cgi_radio rm addteam 'id="cmteam"'`<label

2440 for="cmteam">同じチーム属性を付与</label>

2441 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2442 `cgi_datalist_h teams $allhexteams`

2443 </div>

2444 ${rmteammsg}

2445 `cgi_radio rm send id="sendmsg"`<label

2446 for="sendmsg">メッセージ送信</label>

2447 <div>

2448 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2449 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2450 `cgi_textarea text "" cols=40`

2451 </div>

2452 ${isowner:+$cmmsg$excmsg}

2453 `cgi_radio rm close id="x"`<label for="x">×</label>

2454 </div>

2455 <h4>$grp 参加者一覧</h4>

2456 <table class="td2r">

2457 `sq $db -header -html "$sql"`

2458 </table>

2459 `cgi_hidden grp $grid`

2460 EOF

2461 }

2462 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2463 # Create TEMPORARY VIEW

2464 query<<EOF

2465 CREATE TEMPORARY VIEW writeusers AS

2466 SELECT DISTINCT author FROM article

2467 WHERE id in (

2468 select id from article where blogid=(select id from blog where rowid=$1)

2469 );

2470 CREATE TEMPORARY VIEW movablegroups AS

2471 SELECT g.rowid growid , g.gname

2472 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2473 ON grp.gname=gm.gname -- そのユーザが属している

2474 AND user='$user') g -- グループに絞る

2475 WHERE (SELECT author FROM writeusers

2476 EXCEPT

2477 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2478 IS NULL;

2479 $2

2480 EOF

2481 }

2482 sql4readableblogs() {

2483 # Create view of blogs that can be readable to $user

2484 # Blog is readable when:

2485 # 1: blog owner is an user

2486 # 2: else, 2.1: owner-group where the $user belongs

2487 # 2.2: else, owner-group is not moderated

2488 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2489 cat<<EOF ## | tee tmp/sql.out

2490 CREATE TEMPORARY VIEW readableblogs AS

2491 SELECT blog.rowid rid, id, author

2492 FROM blog

2493 NATURAL JOIN

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

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

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

2497 WHEN (SELECT val FROM grp_s

2498 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2499 AND

2500 (SELECT user FROM grp_mem

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

2502 THEN 0

2503 ELSE 1

2504 END;

2505 EOF

2506 }

2507 editheading() { # $1=rowid-of-heading

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

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

2510 echo "話題番号が未指定です。" | html p

2511 return

2512 fi

2513 owner=`getvalbyid blog owner $rowid`

2514 title=`getvalbyid blog title $rowid`

2515 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2516 | _m4 -D_TITLE_="修正" \

2517 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2518 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

2521 # Move to group

2522 if isuser "$owner"; then

2523 crview4article $rowid

2524 n=`query "SELECT count(*) FROM writeusers;"`

2525 ## err N=$n

2526 if [ $((n)) -gt 0 ]; then

2527 ## err ROWID=$rowid

2528 sql="SELECT growid || ':' || gname FROM movablegroups;"

2529 cat<<-EOF

2530 <div class="fold">

2531 `cgi_checkbox mv send id="mv"`<label

2532 for="mv">この話題をグループ所有に移動する</label>

2533 <div>

2534 <form action="?mvart" method="POST" enctype="multipart/form-data">

2535 移動先グループ:

2536 <select name="mv2grp">

2537 EOF

2538 query ".mode html"

2539 query<<-EOF |

2540 $sql

2541 .mode list

2542 EOF

2543 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2544 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2545 cat<<-EOF

2546 </select>

2547 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2548 そのグループに加入しているものに限られます)</p>

2549 <p>`cgi_checkbox cfm yes`<label>確認

2550 (この操作は元に戻すことができません)</label></p>

2551 `cgi_hidden blogrowid $rowid`

2552 `cgi_submit 移動`

2553 `cgi_reset Reset`

2554 </form>

2555 </div>

2556 </div>

2557 EOF

2558 fi

2559 # end of isuser "$owner"

2560 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2561 [ -n "$hexteams" ];}; then

2562 none="`echo なし|hexize`"

2563 cat<<-EOF

2564 <div class="fold">

2565 `cgi_checkbox mv2team send id="mv2team"`<label

2566 for="mv2team">この話題を以下のチームのものにする</label>

2567 <div><p>現在の所属チーム設定:

2568 `query "SELECT

2569 coalesce((SELECT val FROM blog_s

2570 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2571 AND key='team'),

2572 ':なし');"`</p>

2573 <form action="?mvart" method="POST" enctype="multipart/form-data">

2574 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2575 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2576 `cgi_hidden blogrowid $rowid`<br>

2577 `cgi_submit 移動`

2578 `cgi_reset Reset`

2579 </form></div></div>

2580 EOF

2581 fi

2582 }

2583 mvart() { # move diary to some group or team

2584 # or move blog of group to team which belong to the group

2585 blogrowid=`getpar blogrowid`

2586 cfm=`getpar cfm`

2587 ##### echo move blog:$blogrowid to $mv2grp | html p

2588 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2589 . ./s4-blog.sh

2590 if [ -z "$blogrowid" ]; then

2591 echo "無効な指定です(mvart)。" | html p

2592 return

2593 elif [ x"$cfm" != x"yes" ]; then

2594 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2595 elif { mv2grp=`getpar mv2grp`

2596 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2597 [ -n "$mv2grp" ]; }; then

2598 crview4article $blogrowid

2599 ########## TRANSACTION BEGIN

2600 query "BEGIN;"

2601 n=`query "SELECT count(*) FROM writeusers;"`

2602 ## err Nwriteuser=$n

2603 if [ $((n)) -gt 0 ]; then

2604 query<<-EOF

2605 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2606 WHERE key='owner'

2607 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2608 AND $mv2grp IN (SELECT growid FROM movablegroups);

2609 EOF

2610 fi

2611 query "END;"

2612 ########## TRANSACTION END

2613 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2614 [ -n "$mv2team" ];}; then

2615 # blog owner can move it to ANY team

2616 case "$mv2team" in

2617 'なし')

2618 cat<<-EOF

2619 DELETE FROM blog_s

2620 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2621 AND key='team';

2622 EOF

2623 ;;

2624 "") ;;

2625 *)cat<<-EOF

2626 BEGIN;

2627 REPLACE INTO blog_s(id, key, val)

2628 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2629 'team', '$mv2team');

2630 REPLACE INTO blog_s(id, key, val)

2631 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2632 'notify', 'all'); -- Change notify to all

2633 END;

2634 EOF

2635 esac | query

2636 fi

2637 blog_reply $blogrowid

2638 echo yes | html p

2639 }

2640 editart() { # $1=article-rowid $2=blogrowid

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

2642 blogrowid=${2%%[!A-Z0-9a-z_]*}

2643 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2644 echo "表示する記事番号が未指定です。" | html p

2645 return

2646 fi

2647 owner=`getvalbyid blog owner $blogrowid`

2648 title=`getvalbyid blog title $blogrowid`

2649 author=`getvalbyid article author $rowid`

2650 ## err EDITart: owner=$owner, author=$author

2651 if isgrpowner $user $owner; then

2652 : EDIT OK

2653 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2654 echo "本人か所有者しか編集できません." | html p

2655 return

2656 fi

2657 aid=`query "select id from article where rowid=$rowid;"`

2658 tmpout=$tmpd/editart.$$.out

2659 GF_ACTION="?replyblog+$blogrowid#$aid" \

2660 edittable $formdir/article.def article $rowid \

2661 > $tmpout

2662 rm -f /tmp/editart.out

2663 # Cannot use pipelining to m4 with genform() because of stdin stack

2664 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2665 -D_FORM_="syscmd(cat $tmpout)" \

2666 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2667 -D_BLOGS_= -D_DUMPTABLE_= \

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

2669 }

2670 send2mem() {

2671 rowid=`getpar grp`

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

2673 echo "グループが未指定です。" | html p

2674 return

2675 fi

2676 message=`getpar message`

2677 if [ -z "$message" ]; then

2678 echo "文章を入れてください。" | html p

2679 return

2680 fi

2681 grp=`getgroupbyid $rowid`

2682 members=`collectemail $grp`

2683 # smail rcpt subj (file)

2684 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2685 $urlbase?grp+$rowid

2686 グループ $grp に所属する

2687 `gecos $user` さんよりメッセージ:

2688

2689 $message

2690 EOF

2691 cat<<EOF

2692 <p>以下の宛先に送信しました。</p>

2693 <pre>

2694 $members

2695 </pre>

2696 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2697 EOF

2698 }

2699 joingrpadmit() {

2700 # $1=yes/no $2=session-key

2701 if [ -z "$2" ]; then

2702 echo "bye bye" | html p; return

2703 fi

2704 t_usr=`session=$2 getpar user`

2705 t_grp=`session=$2 getpar group`

2706 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2707 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2708 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2709 echo "無効な加入依頼です。" | html p

2710 echo "有効期限が切れたか、

2711 他の管理者がいる場合は処理済みの可能性があります。" | html p

2712 return

2713 fi

2714 if ! isgrpowner "$user" $t_grp; then

2715 echo "グループ管理者のみの機能です。" | html p; return

2716 fi

2717 case $1 in

2718 yes) joingrp "$t_grp" "$t_usr" yes ;;

2719 no) joingrp "$t_grp" "$t_usr" no ;;

2720 *)

2721 echo "無効な指定です($1)。" | html p

2722 return ;;

2723 esac

2724 gid=$(query "select rowid from grp where gname=`sqlquote $t_grp`;")

2725 rcpts="`getgroupadminmails $t_grp` $user"

2726 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2727 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2728 に

2729 $t_usr

2730 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2731 しました。"

2732 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2733 query "delete from session where id='$2';"

2734 echo "$body" | html p

2735 }

2736

2737 joingrprequest() {

2738 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2739 jss="joingrp-`date +%s`-`genrandom 12`"

2740 addsession $jss +${memoplimitdays}days

2741 query "replace into par values('$jss', 'group', 'string', `sqlquote $1`),

2742 ('$jss', 'user', 'string', `sqlquote $user`);"

2743 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2744 $url

2745 $user さんから

2746 グループ $1

2747 に加入依頼がありました。

2748

2749 承認する:

2750 $urlbase?joingrpadmit+yes+$jss

2751

2752 白紙に戻す:

2753 $urlbase?joingrpadmit+no+$jss

2754 EOF

2755 echo "管理者に加入依頼を出しました。

2756 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2757 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2758 }

2759 joingrp() {

2760 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2761 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2762 if isgrpowner "$user" "$1"; then

2763 isowner="yes"

2764 elif [ -n "$5" ]; then

2765 isowner="yes"

2766 else

2767 isowner=""

2768 fi

2769 ## err jg:isgrpowner: isowner="$isowner"

2770 if [ -n "$isowner" ]; then

2771 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2772 elif [ x"$2" != x"$user" ]; then # if user is not login user

2773 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2774 return

2775 elif [ x"$3" = x"no" ]; then

2776 : # Do not pursue those who leave

2777 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2778 : # Member can change own email address for the joining moderated group

2779 else # adding user is $user itself

2780 case `getgroupattr $1 regmode` in

2781 moderated)

2782 joingrprequest "$@" # Request only

2783 return

2784 ;;

2785 *)

2786 ;;

2787 esac

2788 fi

2789 qgname=`sqlquote $1`

2790 cond="where gname=$qgname and user='$2'"

2791 if [ x"$3" = x"yes" ]; then

2792 query "replace into grp_mem values($qgname, '$2');"

2793 if [ -n "$4" ]; then

2794 if msg=`emaildomaincheck "$4"`; then

2795 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2796 'string', '$4', NULL);"

2797 else

2798 echo $msg

2799 fi

2800 else

2801 query "delete from grp_mem_s $cond and key='email';"

2802 fi

2803 if [ -n "$5" ]; then # as ADMIN

2804 # Coming here means newly created group

2805 sql="select case\

2806 when (select count(*) from grp_mem where gname=$qgname)=1\

2807 then (select user from grp_mem\

2808 where gname=$qgname and user='$user')\

2809 else '' end; "

2810 err NewGrpChk: $sql

2811 if [ -n "`query \"$sql\"`" ]; then

2812 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

2813 query "replace into grp_adm values($qgname, '$user');"

2814 fi

2815 fi

2816 else

2817 query "delete from grp_mem $cond;

2818 delete from grp_mem_s $cond;

2819 delete from grp_mem_m $cond;"

2820 fi

2821 }

2822 grp_add_team() (

2823 # $1=grp-rowid $2=team $3...=user-rowid(s)

2824 grp=`getgroupbyid $1`

2825 team=$2; shift; shift

2826 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2827 { echo "BEGIN;"

2828 for user; do

2829 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2830 '$grp',\

2831 (SELECT name FROM user WHERE rowid=$user),\

2832 'team', 'string', '$team');"

2833 done

2834 echo "END;"

2835 } | query

2836 )

2837 grp_rm_team() (

2838 # $1=grp-rowid $2=team $3...=user-rowid(s)

2839 grid=$1

2840 qgrp=$(sqlquote "`getgroupbyid $grid`")

2841 team=$2; shift; shift

2842 [ -z "$grid" -o -z "$team" ] && return

2843 { echo "BEGIN;"

2844 for user; do

2845 echo "DELETE FROM grp_mem_m\

2846 WHERE gname=$qgrp \

2847 AND user=(SELECT name FROM user WHERE rowid=$user)\

2848 AND key='team' AND val='$team';"

2849 done

2850 cat<<-EOF

2851 DELETE FROM blog_s

2852 WHERE rowid=(

2853 SELECT rowid

2854 FROM blog_s a

2855 WHERE key='team'

2856 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2857 AND NOT EXISTS (SELECT * FROM grp_mem_m

2858 WHERE key='team' AND val=a.val -- a.val=team

2859 AND gname = (SELECT val FROM blog_s b

2860 WHERE a.id=b.id AND key='owner')

2861 ));

2862 EOF

2863

2864 echo "END;"

2865 } | query

2866 )

2867 grp_reg_adm() {

2868 # $1=grp-rowid $2...=user-rowid

2869 grid=$1

2870 grp=`getgroupbyid "$1"`

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

2872 echo "無効なグループIDです" | html p; return

2873 fi

2874 if ! isgrpowner $user "$grp"; then

2875 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2876 fi

2877 shift

2878 for urid; do

2879 newadm=`query "select name from user where rowid=$urid;"`

2880 if [ -z "$newadm" ]; then

2881 echo "指定ユーザIDがおかしいようです。" | html p; return

2882 fi

2883 err GRP_reg_adm: "replace into grp_adm values(`sqlquote $grp`, '$newadm');"

2884 err ismember $newadm $grp

2885 if ismember $newadm $grp; then

2886 # OK, go ahead

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

2888 query "replace into grp_adm values($getgname, '$newadm');"

2889 # confirm insertion

2890 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2891 if [ -n "`query \"$sql;\"`" ]; then

2892 echo "追加完了: $newadm" | html p

2893 else

2894 echo "追加失敗($1 $urid)" | html p

2895 fi

2896 fi

2897 showgroup $grid

2898 done

2899 }

2900 dt_rowhack() {

2901 # From: <TR>

2902 # ....

2903 # <TD>rowclass=foo</TD>

2904 # </TR>

2905 # To: <TR class="foo">....<TD>foo</TD></TR>

2906 sed -e '

2907 /^<TR>/ {

2908 :loop

2909 s/\n//

2910 N

2911 /<\/TR>/ {

2912 s/\n//

2913 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

2914 n

2915 }

2916 $q

2917 b loop

2918 }'

2919 }

2920 dumptable() {

2921 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2922 # textのフィールドだけ全てダンプにしたほうがいいか

2923 # $DT_VIEW sets link

2924 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2925 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2926 VIEW=${DT_VIEW-replyblog}

2927 if [ -n "$VIEW" ]; then

2928 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

2929 fi

2930 # $DT_CHLD=ChildTable:BindColumn

2931 if [ -n "$DT_CHLD" ]; then

2932 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

2933 cntall="(select count($_i) from $_t where $_i=a.id)"

2934 # XXX: Dirty workaround for slow subquery of acclog

2935 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

2936 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

2937 cntnew="(select count(val) from ${_t}_s where key='ctime' \

2938 and id in (select id from $_t where $_i=a.id) \

2939 and val > coalesce((select time from myacclog where \

2940 tblrowid=a.rowid),\

2941 '1970-01-01'))"

2942 cnt="$cntnew as '新着', $cntall as '総数',"

2943 dt_class=" td2r td3r dumpblogs"

2944 fi

2945 # Construct join expression

2946 eav="" scols=""

2947 pk=`gettblpkey $2`

2948 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

2949 substr=${substr:-%s}

2950 for col in ${3:-`gettbl_s_cols $2`}; do

2951 valvar=val

2952 case $col in

2953 gecos) scols="$scols${scols:+, }${col#}"

2954 continue ;; # built-in column name

2955 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

2956 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

2957 case "$as" in

2958 *:*=*) cnd=${as#*:}

2959 h=${cnd%%=*} v=${cnd#*=}

2960 h=`sqlquotestr "$h"`

2961 v=`sqlquotestr "$v"`

2962 valvar="CASE val WHEN $h THEN $v END"

2963 as=${as%%:*} ;;

2964 esac

2965 ;;

2966 *) as=${col} ;;

2967 esac

2968 ss=`printf "$substr" "$valvar"`

2969 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

2970 scols="$scols${scols:+, }b.$as"

2971 done

2972 #case author when '$user' then a.rowid else '---' end as ID,

2973 sql=${DT_SQL:-"select \

2974 a.rowid as LINK,\

2975 $cnt\

2976 $scols from $2 a left join\

2977 (select $pk,$eav,

2978 max(case key when 'owner'

2979 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

2980 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

2981 ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"

2982 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

2983 <div>

2984 <div class="dumptable">

2985 <table class="b$dt_class">

2986 `sq -header -cmd ".mode $1" $db "$presql$sql"`

2987 </table>

2988 </div>

2989 </div>

2990 EOF

2991 }

2992

2993 par2table() (

2994 # copy current parameters of par into destination table

2995 # $1=definition-file

2996 # Using $user and $session

2997 # Return value:

2998 # 0: Stored successfully

2999 # 1: Insufficient fillings

3000 # 2: No permission to modify the record

3001 # 3: Invalid rowid

3002 # 4: SUCCESS to delete

3003 # 5: Stop deletion for lack of confirm check

3004 # 6: Password length too short

3005 # 7: Password mismatch

3006 # 8: Old password incorrect

3007 rowid=`getpar rowid`

3008 if [ ! -e $1 ]; then

3009 echo "テーブル定義ファイルが見付かりません" | html p

3010 exit 1

3011 fi

3012 tbl=${1%.def}

3013 tbl=${tbl##*/}

3014 if [ -n "$rowid" ]; then # Modify existing entry

3015 if [ x"$tbl" = x"user" ]; then

3016 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3017 elif [ x"$tbl" = x"grp" ]; then

3018 sql="select gname from $tbl where rowid=$rowid;"

3019 ##err p2t:grp:q $sql

3020 isgrpowner $user "`query $sql`" && rowowner=$user

3021 else

3022 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3023 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3024 fi

3025 ### err rowowner=$rowowner

3026 if [ x"$user" != x"$rowowner" ]; then

3027 echo "他人のレコードはいじれないの" | html p

3028 return 2

3029 elif [ -z "$rowowner" ]; then

3030 echo "指定したレコードはないみたい" | html p

3031 return 3

3032 fi

3033 rm=`getpar rm` cfm=`getpar confirm`

3034 # Editing existent entry

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

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

3037 query "delete from $tbl where rowid=$rowid;"

3038 return 4

3039 else

3040 echo "消去確認のチェックがないので消さなかったの..." | html p

3041 return 5

3042 fi

3043 fi

3044 fi

3045

3046 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

3048 # Update of existing record

3049 for col in `gettblcols $tbl`; do

3050 val=`getparquote $col`

3051 [ -z "$val" ] && continue

3052 ## err query "update $tbl set $col=$val where rowid=$rowid"

3053 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3054 sql="update $tbl set $col=$val where rowid=$rowid;"

3055 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3056 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3057 ## User name cannot be changed with interface provided with this

3058 ## script. But we offer the trigger to change owner user

3059 ## of blog_s table.

3060 #err "select quote($col) from $tbl where rowid=$rowid;"

3061 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3062 cat<<-EOF | query

3063 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3064 -- should use EXCLUSIVE transaction outside of this.

3065 SAVEPOINT par2table;

3066 $sql

3067 update blog_s set val=$val

3068 where key='owner' and val=$old;

3069 RELEASE SAVEPOINT par2table;

3070 EOF

3071 ## XX: DIRTY Hack Ends here

3072 ## We should keep blog's owner as a single column which has

3073 ## foreign key constraint with primary key of grp/user.

3074 else

3075 query "$sql"

3076 fi

3077 done

3078 # Then, set up $pval for further insertion of tbl_s and tbl_m

3079 for col in `gettblpkey $tbl`; do

3080 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3081 pval="$pval${pval:+, }\"$val\""

3082 done

3083 else

3084 # New entry

3085 # Generate values() for primary keys

3086 for col in `gettblpkey $tbl`; do

3087 # Genuine primary keys for _m and _s

3088 val=`getvalquote $tbl $col`

3089 [ -z "$val" ] && continue

3090 pval="$pval${pval:+, }$val"

3091 done

3092 ##err pval=$pval

3093 for col in `gettblfkey $tbl`; do

3094 # args for values() to insertion into master table

3095 val=`getvalquote $tbl $col`

3096 [ -z "$val" ] && continue

3097 formaster=$formaster"${formaster:+, }$val"

3098 done

3099 formaster="$pval${formaster:+, }$formaster"

3100 ## err formaster=$formaster

3101 if [ -z "$formaster" ]; then

3102 echo "項目を全て埋めてください" | html pre

3103 return 1

3104 fi

3105 ## err "replace into $tbl values($formaster);"

3106 query "replace into $tbl values($formaster);"

3107 ## Insertion to master table, done

3108 fi

3109

3110 for kt in s m; do

3111 tb2=${tbl}_$kt

3112 for col in `gettbl_${kt}_cols $tbl`; do

3113 ptype=`getpartype $col "limit 1"`

3114

3115 # First, check update of existing entries in _m

3116 if [ $kt = m ]; then

3117 # sessID|address.1.22|string|Somewhere-x.y.z

3118 sql=""

3119 ##err dots from query "select var from par where var like '$col.%';"

3120 for v in `query "select var from par where var like '$col.%';"`; do

3121 # v=address.1.22

3122 st_rowid=${v##*.}

3123 origcol=${v%%.*} # original column derived from

3124 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3125 ##case `getpartype $v` in

3126 ## err CASE `gettbl_coltype $tbl/$origcol` in

3127 ## err edit flag = `getpar action.$v`

3128 case `getpar action.$v` in

3129 rm)

3130 if [ x`getpar confirm.$v` = x"yes" ]; then

3131 newsql="delete from $tb2"

3132 else

3133 echo "削除確認未チェック" | html p

3134 fi ;;

3135 edit)

3136 case `gettbl_coltype $tbl/$origcol` in

3137 image|document|binary)

3138 file=$tmpd/`getparfilename $v`

3139 ## err type=file=$file

3140 [ -z "$file" ] && continue

3141 bn=`sqlquotestr "${file##*/}"`

3142 bin="X'"$(hexize "$file")"'"

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

3144 type=\"file:$ct\"

3145 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3146 cachedir=`getcachedir "$tbl/$rowid"`

3147 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3148 rm -rf $cachedir

3149 ;;

3150 *)

3151 newsql="update $tb2 set val=(select val from par where var \

3152 like '$col.%.$st_rowid')"

3153 ;;

3154 esac

3155 ;;

3156 *) # maybe "keep", do not modify value

3157 continue

3158 ;;

3159 esac

3160 # err newsql=$newsql

3161 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3162 done

3163

3164 if [ x"$bin" = x"NULL" ]; then

3165 ## err repl:normal sql=`echo $sql`

3166 query "$sql

3167 delete from $tb2 where type='string' and val='';"

3168 ## err repl:normal done

3169 else

3170 sqlfile="$tmpd/sqlf.$$"

3171 echo "$sql" > $sqlfile

3172 ## err repl:sqlfile=`ls -lF $sqlfile`

3173 query ".read $sqlfile"

3174 ## err repl:done

3175 fi

3176 # Rest of kt==m: set multiple mode

3177 nr=`getparcount $col`

3178 else

3179 nr=1 # for kt==s, number of records is 1

3180 fi

3181

3182 i=0

3183 while [ $i -lt $nr ]; do

3184 limit="limit 1 offset $i"

3185 i=$((i+1)) # increase beforehand against continue

3186 val=`getvalquote $tbl $col "$limit"`

3187 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3188 ## err $col=$val

3189 bin=NULL

3190 ## err partype$col=`getpartype $col "$limit"`

3191 case $ptype in

3192 file) file=$tmpd/`getparfilename $col "$limit"`

3193 ## err parfile-$col=$file

3194 [ -z "$file" ] && continue

3195 bin="X'"$(hexize "$file")"'"

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

3197 type=\"file:$ct\" ;;

3198 "*"*) continue ;; # foreign table

3199 *) type=\"string\" ;;

3200 esac

3201 case `gettbl_coltype $tbl/$col` in

3202 password) # special care for password

3203 # name={password,pswd1,pswd2}

3204 p1=`getpar pswd1 "$limit"`

3205 if [ -z "$p1" ]; then

3206 continue # SKIP password setting, if p1 is empty

3207 else

3208 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3209 ## err pswd=$pswd

3210 if pwcheck "$pswd"; then

3211 if [ x"$p1" = x"$p2" ]; then

3212 case "$p1" in

3213 ??????????*) ;;

3214 *) echo "パスワードは10字以上にしてください。" | html p

3215 return 6;;

3216 esac

3217 val="\"`echo $p1|mypwhash`\""

3218 else

3219 echo "2つの新パスワード不一致" | html p

3220 return 7

3221 fi

3222 else

3223 echo "旧パスワード違います" | html p

3224 return 8

3225 fi

3226 fi

3227 ;;

3228 esac

3229 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3230 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3231 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3232 if [ x"$bin" = x"NULL" ]; then

3233 ## err Normal-query: `echo $sql`

3234 query "$sql"

3235 else

3236 sqlfile="$tmpd/query.$$"

3237 echo "$sql" > $sqlfile

3238 ## err sqlfile=`ls -lF $sqlfile`

3239 query ".read $sqlfile"

3240 fi

3241 ## err p2t done

3242 done

3243 done

3244 done

3245 return 0

3246 ##err donee

3247 )

3248 genform() {

3249 # $1 = form definition file

3250 # $2, $3 (optional)= table name and ROWID

3251 # If $GF_VIEWONLY set and nonNull, output values without form

3252 # If $GF_ARGS set, use it as content-strings in the form

3253 # If $GF_OWNER set, use it as value of name="owner"

3254 # If $GF_STAGE set, use it as value of name="stage"

3255 forms="" hiddens="" rowid=$3

3256 if [ ! -e "$1" ]; then

3257 echo "そのようなデータベースはないようです($2)。" | html p

3258 return

3259 elif [ -n "$2" ]; then

3260 rec=`query "select * from $2 where rowid='$rowid';"`

3261 if [ -z "$rec" ]; then

3262 pk=`gettblpkey $2`

3263 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3264 rec=`query "select rowid from $2 where $pk='$rowid';"`

3265 rowid=$rec

3266 rec=$3

3267 fi

3268 if [ -z "$rec" ]; then

3269 echo "そんなレコードはないみたいね..." | html p

3270 return

3271 fi

3272 fi

3273 if [ -z "$GF_VIEWONLY" ]; then

3274 rm='<input id="rm" name="rm" type="checkbox"

3275 value="yes"><label for="rm">このエントリの削除</label>

3276 <span>ほんとうに消しますよ(確認)!

3277 <input name="confirm" type=checkbox value="yes">はい</span>'

3278 fi

3279 # Image Cache dir

3280 ## err genform: getcache=$2/$rowid

3281 td=`getcachedir "$2/$rowid"`

3282 while IFS=: read prompt name keytype type args; do

3283 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3284 sp="${args:+ }"

3285 form="" val=""

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

3287 # err genform2a: Seeking for "$2.$name, type=$type"

3288 rawval=`getvalbyid $2 $name $rowid $td`

3289 val=`echo "$rawval"|htmlescape`

3290 ## err genform3a: getvalbyid $2 $name $rowid $td

3291 ## err genform3b: val="[$val]" type="$type"

3292 fi

3293 if [ -n "$GF_VIEWONLY" ]; then

3294 is_hidden "$2" "$name" && continue

3295 fi

3296 case "$type" in

3297 text*)

3298 cgiform=cgi_multi_$type

3299 if [ -s $td/$name.count -a -n "$val" ]; then

3300 form=`$cgiform $name $td`

3301 val=$(echo "$val"|

3302 while read fn; do

3303 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3304 </td></tr>$nl"

3305 done)

3306 val="<table>$nl$val$nl</table>"

3307 else

3308 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3309 form=`cgi_$type $name "$rawval" "$args"`

3310 fi

3311 ;;

3312 [Rr][Aa][Dd][Ii][Oo])

3313 fh="<label><input type=\"radio\" name=\"$name\""

3314 form="`echo $args|sed -e \

3315 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3316 ;;

3317 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3318 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3319 ;;

3320 [Ss][Ee][Ll][Ee][Cc][Tt])

3321 fh="<select name=\"$name\">$nl"

3322 form=$(for l in $args; do

3323 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3324 done)

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

3326 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3327 fi

3328 form="$fh$form</select>"

3329 ;;

3330 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3331 if [ -s $td/$name.count ]; then

3332 form=`cgi_multi_file $name $td "$args"`

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

3334 hrfb="$myname?showattc+$2_m"

3335 val=$(echo "$rawval" \

3336 | while read fn; do

3337 data=`percenthex "$td/$fn"`

3338 #ct=`cat $td/$fn.content-type`

3339 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3340 ri=`cat "$td/$fn.rowid"`

3341 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3342 #imgsrc="<img src=\"data:$ct,$data\">"

3343 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3344 iconhref "$td/$fn" "$hrfb+$ri" ""

3345 done)

3346 fi

3347 else

3348 form="<input type=\"file\" name=\"$name\" $args>"

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

3350 imgs=$(echo "$rawval"\

3351 |while read fn;do

3352 data=`percenthex "$td/$fn"`

3353 echo "<img src=\"data:image/png,$data\">$fn<br>"

3354 done)

3355 form=$form"<br>$imgs"

3356 val=$imgs # 2015-06-15

3357 else

3358 form="<input type=\"file\" name=\"$name\" $args>"

3359 fi

3360 fi

3361 ;;

3362 [Hh][Ii][Dd][Dd][Ee][Nn])

3363 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3364 args="value=\"$GF_STAGE\""

3365 fi

3366 form="<input type=\"hidden\" name=\"$name\" $args>"

3367 prompt='' # Remove prompt

3368 ;;

3369 [Aa][Uu][Tt][Hh][Oo][Rr])

3370 [ -n "$GF_VIEWONLY" ] && continue

3371 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3372 prompt="" ;;

3373 [Oo][Ww][Nn][Ee][Rr])

3374 [ -n "$GF_VIEWONLY" ] && continue

3375 val=${GF_OWNER:-$val}

3376 val=${val:-$user}

3377 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3378 prompt="" ;;

3379 [Uu][Ss][Ee][Rr])

3380 # XXX: is null $user ok?

3381 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3382 [ -n "$GF_VIEWONLY" ] && continue

3383 form="$user"

3384 ;;

3385 [Pp]assword)

3386 [ -n "$GF_VIEWONLY" ] && continue

3387 form="`cgi_passwd`"

3388 val=""

3389 ;;

3390 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3391 [ -n "$GF_VIEWONLY" ] && continue

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

3393 val=`genserial`

3394 fi

3395 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3396 prompt="" ;;

3397 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3398 prompt=""

3399 ;;

3400 parent|path|blog*)

3401 prompt=""

3402 ;;

3403 "*"*)

3404 tail=$tail"``"

3405 continue ;;

3406 esac

3407 if [ -n "$prompt" ]; then

3408 if [ -n "${GF_VIEWONLY}" ]; then

3409 form=$val

3410 else

3411 :

3412 fi

3413 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3414 else

3415 hiddens=$hiddens$nl"$form"

3416 fi

3417 done < $1

3418 # enctype="multipart/form-data"

3419 cat<<EOF

3420 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3421 ${rowid:+$rm}

3422 <table class="b $2">

3423 $forms

3424 </table>$hiddens

3425 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3426 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3427 EOF

3428 if [ -z $GF_VIEWONLY ]; then

3429 cat<<EOF

3430 <input type="submit" name="sub" value="OK">

3431 <input type="reset" name="res" value="Reset">

3432 EOF

3433 fi

3434 cat<<EOF

3435 $GF_ARGS</form>

3436 $tail

3437 EOF

3438 }

3439 edittable() {

3440 # $1=form-def $2=table $3 rowid

3441 genform "$@"

3442 }

3443 viewtable() {

3444 GF_VIEWONLY=1 genform "$@"

3445 }

3446 showattc() {

3447 # $1=table_m $2=rowid &optional $3=RawFlag

3448 ## err \$1=$1 \$2=$2 \$3=$3

3449 if ! isfilereadable $user $1 $2; then

3450 contenttype; echo

3451 echo "このファイルは管理者のみしか見られません" | html p

3452 putfooter; exit

3453 fi

3454 idir=`umask 002; mktempd` || exit 1

3455 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3456 bin=$idir/$myname-$$.bin

3457 sql="select quote(bin) from $1 where rowid='$2';"

3458 ## err showattc: sql: $sql

3459 sq $db "$sql" | unhexize > $bin

3460 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3461 type=${tv%//*} fn=${tv#*//}

3462 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3463 ct=${type#file:}

3464 case $ct in # all text/* changed to text/plain

3465 text/*)

3466 charset=`nkf -g $bin|cut -d' ' -f1`

3467 case $charset in

3468 ASCII*) charset="" ;;

3469 esac

3470 if [ -z "$3" ]; then

3471 ct="text/html${charset:+; charset=$charset}"

3472 link="?showattc+$1+$2+raw"

3473 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3474 | sed 's,^,<span></span>,' \

3475 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3476 -D_LINK_="$link" \

3477 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3478 exit $?

3479 fi

3480 ct="text/plain${charset:+; charset=$charset}"

3481 ;;

3482 esac

3483 contenttype "$ct"

3484 echo "Content-Disposition: filename=\"$fn\""

3485 echo "Content-Length: " `cat $bin | wc -c`; echo

3486 #echo "Content-Type: " ${type#file:}; echo

3487 cat $bin

3488 }

3489 #

3490 # Some default stupid handler on CGI values

3491 #

3492 default_storedb() {

3493 # ARG: $1=table-def-file

3494 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3495 tbl=`basename $1`

3496 tbl=${tbl%.def}

3497 cols="`grep :text $1|cut -d: -f2`"

3498 col=`echo "$cols"|head -1`

3499 vcol=`getpar $col`

3500 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3501 if [ -n "$vcol" ]; then

3502 par2table $1

3503 else

3504 return 2 # No insertion occurred

3505 fi

3506 }

3507

3508 default_view() { # $1=def-file

3509 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3510 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3511 default_storedb "$@"

3512 query "select rowid from $tbl order by rowid desc;" \

3513 | while read rowid; do

3514 viewtable $1 $tbl $rowid

3515 done | _m4 -D_TITLE_="$tbl" \

3516 -D_FORM_="`genform $1`" \

3517 -D_DUMPTABLE_="syscmd(cat)" \

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

3519 }

3520 default_viewtext() { # $1=def-file

3521 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3522 default_storedb "$@"

3523 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3524 | _m4 -D_TITLE_="$tbl" \

3525 -D_FORM_="`genform $1`" \

3526 -D_DUMPTABLE_="syscmd(cat)" \

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

3528 }

3529 default_smail() {

3530 default_storedb "$@"

3531 if [ $? -eq 2 ]; then

3532 _m4 -D_TITLE_="入力" \

3533 -D_FORM_="`genform $1`" \

3534 -D_DUMPTABLE_="" \

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

3536 return

3537 fi

3538 cond=""

3539 for pk in `gettblpkey $tbl`; do

3540 pv=$(sqlquote $(getpar $pk))

3541 cond="$cond${cond:+ and }$pk=$pv"

3542 done

3543 sql="select rowid from $tbl where $cond;"

3544 rowid=`query "$sql"`

3545 ## err smail1 - "$sql" "-> rowid=$rowid"

3546

3547 while IFS=: read prompt name keytype type args; do # Read from $1

3548 val=`getpar $name`

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

3550 text="$text

3551 $prompt

3552 $name=$val

3553 ---------------------------------------------------------"

3554 fi

3555 case "$type" in

3556 image|document|file)

3557 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3558 fns=$(echo "$fn"|while read fn; do

3559 err mv $tmpd/$fn.orig $tmpd/$fn

3560 mv $tmpd/$fn.orig $tmpd/$fn

3561 rm $tmpd/$fn.rowid # Remove cache flag

3562 ## err "`ls $tmpd/$fn`"

3563 echo $fn

3564 done)

3565 files="$files $fns"

3566 ;;

3567 esac

3568 done < $1

3569 ## err FILES=$files "`ls -lF $tmpd`"

3570 subj="from ${REMOTE_ADDR}"

3571 (echo "$url"

3572 echo "への書き込みがありました。"

3573 echo "------"

3574 echo "$text"

3575 ) | (cd $tmpd &&

3576 err LS="`ls -lF`" &&

3577 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3578 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3579 echo "以下の内容で送信しました。" | html p

3580 viewtable $1 $tbl \

3581 `query "select rowid from $tbl order by rowid desc limit 1;"`

3582 echo "戻る" | html a "href=\"?\""

3583 }