s4: 38bf8d300b12 s4-funcs.sh

s4

view s4-funcs.sh @ 472:38bf8d300b12

For slower connection, it's better to use img-link over data:

author	HIROSE Yuuji <yuuji@gentei.org>
date	Wed, 30 Aug 2017 08:47:01 +0859
parents	1dacdf998892
children	4f9036d35b57

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 blogcutoffflagrowid=-1

35 querylog=$tmpdir/query.log

37 tconfs=""

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

39 conftbl=_tblconf

40 nl="

41 "

42 iconcachekey="profimgcache_S"

43 case "$HTTP_USER_AGENT" in

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

45 *) touchpanel="" ;;

46 esac

47 . ./s4-cgi.sh

49 : <<EOF

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

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

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

54 レポート提出管理

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

57 [2016]

58 7/12 根本への反省

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

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

62 [以下2015]

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

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

65 Web

66 締切設定

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

69 ○自分の提出物リスト

71 7/19 ○設置

72 ○一斉送信

73 ○getparfilename の tmpd の扱い

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

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

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

77 ○添付ファイル回収

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

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

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

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

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

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

85 ○レポート提出モード

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

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

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

91 7/9 ○管理者の追加

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

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

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

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

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

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

100 ○グループ検索

101

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

103

104

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

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

107

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

109 ○6/1 par2tableを triplex 対応に

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

111 →とすると一気に

112

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

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

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

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

117

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

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

120 ## 1. passwd

121 ## 2. grp

122 ## 3. grp_mem

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

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

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

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

127

128 ## ● listの定義:

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

130 ## ● hashの定義:

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

132

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

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

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

136 ## csv2sq3 で .csv.sq3 の Makefile

137

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

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

140 ## type := root | comment

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

142 ## unique(id, filename)

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

144

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

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

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

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

149 ## できたー!

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

151 ↓

152 ↓以下に変更

153 with recursive allmem as

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

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

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

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

158

159

160 with recursive allmem as

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

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

163 where allmem.val=grp_m.gname)

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

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

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

167

168

169 ## triggerもできた。

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

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

172

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

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

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

176 ## グループ管理

177 ## 個人情報管理

178 ##

179

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

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

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

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

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

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

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

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

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

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

190

191 ## ログテーブル

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

193

194

195

196 ■表設計

197 * 3つの表に分散管理

198 id格納表 + hash表 + list表

199 * *_s *_m

200

201

202

203 user, user_map, user_col

204

205 ■抽象エントリタイプ

206 * user

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

208 * group

209 権限判定に利用

210 * serial

211 自動idとして機能

212 * password

213 入力 type=passwordで入力

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

215 * session

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

217 * text

218 入力 type=text

219 * textarea

220 入力 textarea

221 * image|document

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

223 * owner

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

225 * gowner

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

227 * timestamp

228 datetime()

229 * parent

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

231 * path

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

233

234 格納タイプ

235 * list

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

237 * hash

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

239

240 オブジェクトタイプ

241 * entry

242 id, title, owner

243 * textpart

244 id, parentID, text

245 * binarypart

246 id, parentID, contenttype, filename, content

247 * content

248 hash(textpart), list(binarypart)

249 * topic

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

251 * reply

252 id, parentID, content

253 * blog

254 list(entry)

255 blog = [topic, list(reply)]

256

257

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

259 "text" => "hogehoge ..",

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

261 "author" => "taro",

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

263 "parent" => "/",

264 "path" => "/1",

265 "text" => "blah, blah, ....",

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

267 {"serial" => 2,

268 "author" => "hanako",

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

270 "parent" => "/",

271 "path" => "/2",

272 "text" => "blah, blah, ....",

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

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

275

276

277 user:=

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

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

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

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

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

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

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

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

286

287 変換表

288 /user/email=m

289

290 blog:=

291 シリアル:id:p:serial

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

293 所有者:owner:s:owner:

294 時刻:ctime:s:stamp:

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

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

297

298 article:=

299 シリアル:id:p:serial

300 筆者:author:s:owner

301 時刻:ctime:s:stamp:

302 参照元:parent:s:parent:

303 パス:path:s:path:

304 本文:text:s:textarea:

305 画像:image:m:image:

306

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

308

309

310 EOF

311

312 sq() {

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

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

315 }

316 dbsetup() {

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

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

319 sqi=$tmpdir/sqi.$$

320 sqo=$tmpdir/sqo.$$

321 mkfifo $sqi $sqo

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

323 sq $db < $sqi &

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

325 exec 2>> $tmpdir/error.out

326 exec 3>> $tmpdir/debug.out

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

328 rm $sqi

329 }

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

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

332 }

333 cleanup() {

334 trap '' INT HUP EXIT TERM PIPE

335 echo .quit >&5

336 kill $sq3pid

337 kill $sq3pid

338 rm -f $sqo $sqi

339 rm -rf $tmpfiles

340 cleanup2

341 }

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

343 # simple redirection for the sake of speed.

344 query() {

345 echo ".once $sqo" >&5

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

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

348 tee -a $querylog

349 else

350 echo "$@" >> $querylog

351 echo "$@"

352 fi >&5

353 cat $sqo

354 echo '>>>' >> $querylog

355 }

356 _m4() {

357 #_S4NAME_=f,f,f

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

359 }

360 ismember() {

361 # $1=user, $2=group

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

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

364 }

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

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

367 }

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

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

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

371 }

372 isgrpowner() (

373 # $1=user, $2=group

374 gn=`sqlquote "$2"`

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

376 err isgrpowner: $sql

377 test -n "`query $sql`"

378 )

379 getgroupadminmails() {

380 # $1=group

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

382 email4group "$1" "$i" ;

383 done

384 }

385 getgroupadmins() { # $1=group

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

387 qgrp=`sqlquote "$1"`

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

389 }

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

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

392 getvalbyid grp $2 \

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

394 }

395 getgroupbyid() {

396 # $1=id|gname

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

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

399 # err ggbyid: `echo $sql`

400 query $sql

401 }

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

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

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

405

406 # Return true when anonymous mode

407 [ "$anonymousmode" ] && return 0

408 # case `getvalbyid blog mode $2` in

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

410 # *closed*)

411 # owner=`getvalbyid blog owner $2`

412 # if isgrp $owner; then

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

414 # elif isuser $owner; then

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

416 # fi

417 # esac

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

419 sql="with getblog as (\

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

421 select blogid from article where id in\

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

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

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

425 select case\

426 when (select author from article where\

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

428 then 'author'\

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

430 then 'open'\

431 when (select val from getmode) is null \

432 then 'open'

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

434 then (select user from grp_adm where \

435 gname=(select val from getowner) and \

436 user='$1')\

437 when (select author from article where\

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

439 then 'user+author'

440 else '' end;"

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

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

443 result=`query "$sql"`

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

445 return 2

446 }

447 linkhome() {

448 # $1=UserOrGroup

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

450 if isuser $1; then

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

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

453 else

454 echo -n "grp+$1"

455 fi

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

457 }

458 hreflink() {

459 # s4 specific notation:

460 # ^href=URL

461 # ^iframe=URL

462 # OSM umap Wikistyle Notation:

463 # [[URL]] - Simle Link

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

482 }

483 minitbl() {

484 sed -n '

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

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

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

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

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

490 H; # Concat this line to HoldSpace

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

492 $ b cont

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

494 }

495 :cont

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

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

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

499 # . of ^. is workaround for FreeBSD sed

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

501 p; # Print whole "table" element

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

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

504 }

505 x; # Back to the newest line

506 p; # Print rest'

507 }

508 acclog() (

509 # $1=table, $2=rowid

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

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

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

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

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

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

516 fi

517 )

518 gecos() (

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

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

521 )

522 setpar() {

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

524 }

525 replpar() {

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

527 }

528 getpar() {

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

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

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

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

533 fi

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

535 case "$var" in

536 owner)

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

538 echo $user; return

539 elif ismember $user $val; then

540 echo $val; return

541 fi ;;

542 esac

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

544 echo "$val"

545 }

546

547 getpartype() {

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

549 }

550 getparcount() {

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

552 }

553 getparfilename() {

554 # null if type of $1 is not file

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

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

557 }

558 sqlquote() {

559 (v="$1"

560 case "$v" in

561 "") return ;; # null

562 "X'"*) # quoted hex string

563 echo $1 ;;

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

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

566 echo "\"$v\""

567 return ;;

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

569 echo "\"$v\""

570 return ;;

571 *)

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

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

574 else

575 echo "\"$v\""

576 fi ;;

577 esac)

578 }

579 sqlquotestr() (

580 case "$1" in

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

582 echo "'$v'" ;;

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

584 esac

585 )

586 mktempd() {

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

588 }

589 getcachedir() { # $1=maintable

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

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

592 else

593 echo $tmpd/$thumbxy

594 fi

595 }

596 getval() {

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

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

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

600 echo "$user" ;;

601 stamp|datetime)

602 date "+%F %T" ;;

603 serial)

604 (s=`getpar $2`

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

606 *)

607 getpar "$2" "$3";;

608 esac

609 }

610

611 getvalquote() {

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

613 (v=`getval "$@"`

614 case "$v" in

615 "") echo NULL ;;

616 *) sqlquote "$v" ;;

617 esac)

618 }

619 getparquote() {

620 sqlquote "`getpar $1`"

621 }

622 getbinbyid() {

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

624

625 }

626 getvalbyid() {

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

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

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

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

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

632

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

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

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

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

637 return

638 fi

639 done

640 rowid=$3

641 pk=`gettblpkey $1`

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

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

644 td=${4:-$tmpd}

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

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

647 for kt in s m; do

648 t=${1}_$kt

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

650 vcount=1 # count(val)

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

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

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

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

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

656 if [ $kt = m ]; then

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

658 # Reset val to store filenames if type is string

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

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

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

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

663 i=0

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

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

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

667 i=$((i+1))

668 fn=$c.$i

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

670 case $type in

671 file:*)

672 #file=$td/$val

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

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

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

676 # FOR SPEED: Skip file generation if imgcache exists

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

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

679 && continue

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

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

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

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

684 select rowid from $cond $slice;

685 .output '$td/$fn'

686 select val from $cond $slice;

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

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

689 .output stdout

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

691 EOF

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

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

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

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

696 ls -lh "$file" |

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

698 case "$type" in

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

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

701 *|*:[Aa]pplication*)

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

703 png:- > "$file"

704 ;;

705 esac

706 ;;

707 *)

708 sq $db<<EOF

709 .output $td/$fn.rowid

710 select rowid from $cond $slice;

711 .output $td/$fn

712 select val from $cond $slice;

713 EOF

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

715 ;;

716 esac

717 done

718 else

719 rm -f $td/$c.count

720 case $type in

721 file:*)

722 echo "$val" \

723 | while read fn; do

724 file=$td/$fn

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

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

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

728 | unhexize > "$file"

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

730 case $type in

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

732 *:[Aa]pplication*)

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

734 png:- > $file ;;

735 esac

736 fi

737 done

738 ;;

739 esac

740 fi

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

742 return

743 fi

744 done

745 done)

746 }

747 getvalbypkey() (

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

749 pk=`gettblpkey $1`

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

751 getvalbyid "$1" "$2" $rowid $4

752 )

753 getvalbycond() {

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

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

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

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

758 getvalbyid "$1" "$2" $rowid "$4"

759 fi

760 }

761 getpwfield() {

762 # getpwfield user column

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

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

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

766 echo "$val"

767 return 0

768 else

769 return 1

770 fi

771 }

772 encode() {

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

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

775 sha1=sha1

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

777 sha1=sha1sum

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

779 sha1=gsha1sum

780 fi

781 fi

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

783 }

784 enjpeg() {

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

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

787 cjpeg="cjpeg"

788 else

789 cjpeg="convert - jpeg:-"

790 fi

791 fi

792 $cjpeg "$@"

793 }

794 mycrypt() (

795 key=$1 salt=$2

796 err \$2=$2

797 case $2 in

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

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

800 esac

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

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

803 )

804 hexize() {

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

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

807 hexize="xxd -p"

808 else

809 hexize_hd() {

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

811 }

812 hexize="hexize_hd"

813 fi

814 fi

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

816 }

817 unhexize() {

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

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

820 unhex="xxd -p -r"

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

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

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

824 EOF

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

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

827 # world writable...:)

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

829 fi

830 fi

831 cat "$@" | $unhex

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

833 }

834 percenthex() {

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

836 }

837 htmlescape() {

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

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

840 }

841 enascii() {

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

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

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

845 else

846 enascii_now=`date +%FT%T`

847 enascii_sed() {

848 nkf -Z0Z1Z2 \

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

850 }

851 enascii="enascii_sed"

852 fi

853 fi

854 cat "$@" | $enascii

855 }

856 size_h() {

857 i="$1" oi=$1

858 set -- B B KB MB GB TB

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

860 oi=$i

861 i=$((i/1024))

862 shift

863 done

864 echo ${oi}$1

865 }

866 gettblconf() {

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

868 ## tconfs=`sq $db \

869 tconfs=`query \

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

871 fi

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

873 }

874 gettblkeys() {

875 # $1=tbl

876 gettblconf

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

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

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

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

881 case $type in

882 *p*)

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

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

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

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

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

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

889 esac

890 case $type in

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

892 esac

893 done

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

895 }

896 gettblpkey() {

897 # $1=tbl

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

899 }

900 gettblfkey() {

901 (x=`gettblkeys $1`

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

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

904 }

905 gettblcols() {

906 (x=`gettblkeys $1`

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

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

909 }

910 gettbl_s_cols() {

911 (x=`gettblkeys $1`

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

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

914 }

915 gettbl_m_cols() {

916 (x=`gettblkeys $1`

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

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

919 }

920 gettbl_h_cols() {

921 (x=`gettblkeys $1`

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

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

924 }

925 gettbl_coltype() (

926 gettblconf

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

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

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

930 )

931 is_hidden() {

932 # $1=Tbl $2=col

933 gettblconf

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

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

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

937 case $x in

938 *h*) return 0 ;;

939 *) return 1 ;;

940 esac

941 }

942

943 dbsetbyid() {

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

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

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

947 quotedp=$(sqlquotestr "$p")

948 unset primary update

949 gettblconf

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

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

952 #err conf=$conf

953 case ${conf#*=} in

954 p*) primary=1 ;;

955 f*) update=1 ;;

956 u*) ;;

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

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

959 esac

960 #err t=$t

961 type=string fn=""

962 case $conf in

963 */password)

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

965 ;;

966 */image*|*/document*)

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

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

969 ;;

970 esac

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

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

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

974 if [ "$primary" ]; then

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

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

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

978 elif [ "$update" ]; then

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

980 else

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

982 fi

983 )

984 }

985 expire() (

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

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

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

989 )

990 addsession() {

991 # expireをセット

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

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

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

995 # Remove old session parameters

996 now=`expire now`

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

998 }

999 gencookie() (

1000 for kv; do

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

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

1003 done

1004 )

1005 contenttype() {

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

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

1008 }

1009 putheader() {

1010

1011 }

1012 putfooter() {

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

1014 }

1015 getcookie() (

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

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

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

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

1020 done

1021 )

1022 genrandom() {

1023 # $1=columns (default: 10)

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

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

1026 }

1027 genserial() {

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

1029 }

1030 smail() {

1031 # smail rcpts subj (file)

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

1033 # $MAIL_FROM <- From: header value

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

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

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

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

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

1039 }

1040 setviastring() {

1041 table=$1

1042 oifs="$IFS"

1043 IFS="&"

1044 for us in $2; do

1045 k=${us%%=*}

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

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

1048 #echo $k=$v

1049 done

1050 IFS="$oifs"

1051 }

1052 checkdomain() (

1053 # Check the validity of domain by referring DNS

1054 item=$1

1055 err checkdomain $1

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

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

1058 )

1059 pwcheck() {

1060 # $1=passwd

1061 dbpswd=`getpwfield $user pswd`

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

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

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

1065 }

1066 mypwhash() {

1067 mycrypt `cat` `genrandom 5`

1068 }

1069 wasureta() {

1070 user=$1

1071 if ! checkdomain $user; then

1072 contenttype; echo

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

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

1075 putfooter

1076 exit 0

1077 fi

1078 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1080 encpswd=`echo $newpswd|mypwhash`

1081 dbsetbyid user $user pswd "$encpswd"

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

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

1084 $msgdir/mail-newaccount.m4 \

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

1086 | smail $user "New Account"

1087 }

1088 checkauth() {

1089 user=`getpar user`

1090 skc=`getpar skey` # from cookie

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

1092 skey="`getpwfield $user skey`"

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

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

1095 return 0

1096 fi

1097 fi

1098 pswd=`getpar pswd`

1099 quser=`sqlquotestr "$user"`

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

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

1102 return 1

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

1104 wasureta $user

1105 return 1 # wasureta error

1106 fi

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

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

1109 if pwcheck "$pswd"; then

1110 newsession=`genrandom 50`

1111 dbsetbyid user $user skey "$newsession"

1112 gencookie "user=$user" "skey=$newsession"

1113 return 0

1114 fi

1115 return 2 # Password mismatch

1116 }

1117 showlogin() {

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

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

1120 $layout/login.m4.html

1121 exit 0

1122 }

1123 dologin() {

1124 checkauth

1125 st=$?

1126 if [ $st != 0 ]; then

1127 contenttype; echo

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

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

1130 showlogin # and EXIT

1131 fi

1132 }

1133

1134 # Do instant jobs here

1135 dbsetup

1136 trap cleanup INT HUP EXIT TERM PIPE

1137 # trap cleanup INT HUP

1138

1139 err() {

1140 echo "$@" 1>&3

1141 }

1142

1143 cgiinit() {

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

1145 tmpf=tmp/stream

1146 tmpd=`tmpd=$tmpdir mktempd`

1147 tmpfiles=$tmpfiles" $tmpd"

1148 addsession $session

1149 getcookie

1150 case "$REQUEST_METHOD" in

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

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

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

1154 # cat > $tmpf # too much?

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

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

1157 s="`cat tmp/stream`"

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

1159 ;;

1160 esac

1161 case "$CONTENT_TYPE" in

1162 *boundary*)

1163 bndry=${CONTENT_TYPE#*boundary=}

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

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

1166 do

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

1168 #echo u=$us

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

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

1171 # err k=$k v=$v

1172 case "$k" in

1173 *:filename)

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

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

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

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

1178 :

1179 fi

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

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

1182 [Ii]mage/x-xcf)

1183 bzip2 "$tmpd/$v"

1184 v=${v}.bz2

1185 ;;

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

1187 [Ii]mage/*)

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

1189 ;;

1190 esac

1191 ;;

1192 *)

1193 type='string'

1194 ;;

1195 esac

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

1197 setpar "$k" "$type" "$v"

1198 done

1199 ;;

1200 *)

1201 setviastring par "$s"

1202 ;;

1203 esac

1204 }

1205 email4group() {

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

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

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

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

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

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

1212 and s.key='email'

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

1214 query "$sql"

1215 }

1216 email4groupbyuid() {

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

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

1219 uids=`echo "$@"`

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

1221 sql="WITH

1222 grpemails AS (

1223 SELECT gname, user, val email

1224 FROM grp_mem NATURAL JOIN grp_mem_s

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

1226 useremails AS (

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

1228 FROM user

1229 LEFT JOIN user_s

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

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

1232 FROM useremails u LEFT JOIN grpemails g

1233 ON u.name=g.user

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

1235 ## err email4gByid `echo $sql`

1236 query "$sql"

1237 }

1238 collectmembersbyid() {

1239 # Collect user names of group specified by grid

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

1241 query "SELECT user FROM grp_mem \

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

1243 }

1244 collectmembersbyid() {

1245 # Collect user names of group name

1246 qgrp=`sqlquote "$1"`

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

1248 }

1249 collectgecosesbyid() {

1250 # Collect user gecoses of group

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

1252 query<<-EOF

1253 SELECT gecos

1254 FROM gecoses

1255 WHERE name IN (SELECT user FROM grp_mem

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

1257 EOF

1258 }

1259 collectemail() (

1260 # Collect email addresses for group $1

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

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

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

1264 for e; do

1265 if isuser "$e"; then

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

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

1268 else

1269 qgrp=`sqlquote "$e"`

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

1271 gmem="grp_mem"

1272 else

1273 tm=`sqlquote "$TEAM"`

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

1275 fi

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

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

1278 $gmem g left join grp_mem_s s

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

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

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

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

1283 query "$sql"

1284 fi

1285 done

1286 )

1287 sendinvitation() (

1288 # $1=email

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

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

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

1292 gecos=`gecos`

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

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

1295 _m4 -D_URL_="$urlbase" \

1296 -D_USER_="$name" \

1297 -D_EMAIL_="$1" \

1298 -D_REGIST_="$regist" \

1299 -D_ADMIN_="$admin" \

1300 $msgdir/mail-invite.m4 \

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

1302 return 0

1303 )

1304 emaildomaincheck() {

1305 case "$1" in

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

1307 *@*)

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

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

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

1311 return 2

1312 fi

1313 return 0

1314 ;;

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

1316 esac

1317 }

1318 invite() {

1319 email=`getpar email`

1320 case $email in

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

1322 *@*)

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

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

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

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

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

1328 elif sendinvitation $email; then

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

1330 fi ;;

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

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

1333 esac

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

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

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

1337 fi

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

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

1340 $layout/html.m4.html $layout/invite.m4.html

1341 }

1342 regist() {

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

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

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

1346 echo "bye bye" | html p

1347 reutrn

1348 fi

1349 email=`session=$1 getpar invite`

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

1351 cat<<EOF

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

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

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

1355 EOF

1356 return

1357 fi

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

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

1360 # Fake login password to wasureta

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

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

1363 wasureta $email

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

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

1366 addsession $1 # for removal after 1 minute

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

1368 $layout/login.m4.html

1369 return

1370 }

1371 group_safename() {

1372 # Convert $1 to safe group name

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

1374 }

1375 groupupdate() {

1376 gname=`getpar gname`

1377 qgname=`sqlquote "$gname"`

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

1379 # See ALSO same job in showgroup()

1380 newgname=`group_safename "$gname"`

1381 err newgname=$newgname

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

1383 err NewGNAME: gname=$newgname

1384 gname=$newgname

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

1386 replpar gname string "$gname"

1387 fi

1388 # Name confliction check

1389 parow=`getpar rowid`

1390 ## err parow=$parow

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

1392 query "BEGIN EXCLUSIVE;"

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

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

1395 if [ $count -gt 0 ]; then

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

1397 query "END;"

1398 return

1399 fi

1400 par2table $formdir/grp.def

1401 query "END TRANSACTION;"

1402 # Remove orphan

1403 : <<EOF

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

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

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

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

1408 EOF

1409 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1413 sql="delete from blog where id in

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

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

1416 query "$sql";

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

1418 return # and return

1419 fi

1420 fi

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

1422 fi

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

1424 grid=$(query $sql)

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

1426 grp $grid

1427 }

1428 groupclone() {

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

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

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

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

1433 return

1434 fi

1435 i=0

1436 while true; do

1437 copy="-copy$i"

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

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

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

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

1442 i=$((i++))

1443 continue

1444 fi

1445 break

1446 done

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

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

1449 query<<-EOF

1450 BEGIN;

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

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

1453 SELECT $newqname, key, val

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

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

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

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

1458 -- Copy members and their configuration --

1459 REPLACE INTO grp_mem SELECT $newqname, user

1460 FROM grp_mem WHERE gname=$qgrp;

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

1462 FROM grp_mem_s WHERE gname=$qgrp;

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

1464 FROM grp_mem_m WHERE gname=$qgrp;

1465 -- Copy administrators --

1466 REPLACE INTO grp_adm SELECT $newqname, user

1467 FROM grp_adm WHERE gname=$qgrp;

1468 COMMIT;

1469 EOF

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

1471 STOPCLONEMSG=1 groupconf "$newrowid"

1472 }

1473 groupman() {

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

1475

1476 GF_STAGE="grpconf"

1477 GF_STAGE=groupupdate

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

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

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

1481 -D_DUMPTABLE_="syscmd(cat)" \

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

1483 }

1484 userconf() {

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

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

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

1488 }

1489 groupconf() {

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

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

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

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

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

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

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

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

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

1499 | html p

1500 fi

1501 }

1502 mems() {

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

1504 kwd=`getpar kwd`

1505 listmember $kwd

1506 }

1507 grps() {

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

1509 kwd=`getpar kwd`

1510 listgroup $kwd \

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

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

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

1514 $layout/form+dump.m4.html

1515 }

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

1517 gpg=`getpar grp`

1518 grid=${1:-$gpg}

1519 grp=`getgroupbyid "$grid"`

1520 ## . ./s4-blog.sh

1521 jg=`getpar joingrp`

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

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

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

1525 fi

1526 htmlheader=$layout/html.m4.html

1527 showgroup "$grid"

1528 }

1529 sql4interestblogs() {

1530 cat<<EOF

1531 CREATE TEMPORARY VIEW interestblogs AS

1532 SELECT blog.rowid rid, id, author

1533 FROM blog

1534 NATURAL JOIN

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

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

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

1538 WHEN (SELECT user FROM grp_mem

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

1540 THEN 0

1541 ELSE 1

1542 END;

1543 EOF

1544 }

1545 listnewblogsql() { # $1=user

1546 deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog

1547 WHERE user='$user'

1548 AND tblrowid IN

1549 ($blogreadflagrowid,

1550 $blogcutoffflagrowid)),

1551 "0");"`

1552 cat<<EOF

1553 `sql4interestblogs`

1554 WITH article_ctime as (

1555 SELECT id,blogid,author,max(val) ctime

1556 FROM article join article_s s using(id)

1557 WHERE s.key='ctime' AND s.val > '$deftime'

1558 GROUP BY id

1559 ), blog_title_owner as (

1560 SELECT blg.rid brid, id,

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

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

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

1564 ), blogall as (

1565 SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid

1566 ), news as (

1567 SELECT brid, bl.id blid, bl.title, ctime,

1568 coalesce(al.time, '$deftime') atime,

1569 count(bl.id) "新着", bl.author

1570 FROM blogall bl

1571 LEFT JOIN

1572 (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al

1573 ON bl.brid=al.tblrowid

1574 WHERE atime < bl.ctime

1575 GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id

1576 LIMIT 10

1577 ) SELECT brid LINK, "新着",

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

1579 ctime, title,

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

1581 FROM news;

1582 EOF

1583 }

1584

1585 search_form() {

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

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

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

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

1590 @week → 最近一週間

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

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

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

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

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

1596 auth=""

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

1598 case "$1" in

1599 author=*)

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

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

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

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

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

1605 ;;

1606 grp=*)

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

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

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

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

1611 ;;

1612 esac

1613 cat<<-EOF

1614 <div class="right">

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

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

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

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

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

1620 </form>

1621 </div>

1622 EOF

1623 }

1624

1625 imgsrc_cache() (

1626 # $1 = directory for cache'ing

1627 # $2 = table (user_m or grp_m)

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

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

1630 dir="$1" tbl="$2"

1631 keycond="$3"

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

1633 ORDER BY rowid DESC LIMIT 1"

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

1635 tmpf=$tmpd/imgsrc_cache.$$

1636 case "$4" in

1637 [Ss]) size=S ;;

1638 [Oo]) size=O ;;

1639 *) size=M ;;

1640 esac

1641 # ImageCache filename storing schema:

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

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

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

1645 valtype=`query "$sql0"`

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

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

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

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

1650 case "$filetype" in

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

1652 esac

1653 fi

1654 cacheimg_S=$dir/S_$filename

1655 cacheimg_M=$dir/M_$filename

1656 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1664 else

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

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

1667 fifo2=$fifo.2

1668 mkfifo $fifo $fifo2

1669 fmt=${filename##*.}

1670 ## [[ NOTE ]]

1671 ## a. convert oldimage newimage

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

1673 ## b is much smaller than a

1674 cat $tmpf | unhexize \

1675 | tee $fifo \

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

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

1678 | tee $fifo2 \

1679 | convert - "$cacheimg_M" &

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

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

1682 | convert - "$cacheimg_S" &

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

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

1685 echo '">'

1686 echo "$sum" > $sumfile

1687 fi

1688 ## Now preparing cache image, done.

1689 ## Store this information to DB

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

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

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

1693 query <<-EOF

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

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

1696 EOF

1697 )

1698

1699 showhome() {

1700 # $1=userRowIdToShow

1701 err showhome \$1=$1

1702 case "$1" in

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

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

1705 esac

1706 ## err ShowHome: uname=$uname

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

1708 gecos=`gecos "$uname"`

1709 ## err SH:gecos=$gecos

1710 GF_VIEWONLY=1

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

1712 search_form_args=""

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

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

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

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

1717 # Display folders

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

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

1720 and type like 'file:%';"

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

1722 nfile=`query "$sql"`

1723 # err nfile=$nfile

1724 if [ $nfile -gt 0 ]; then

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

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

1727 fi

1728 else

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

1730 GROUP BY user;"`

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

1732 search_form_args="author=$uname"

1733 fi

1734 . ./s4-blog.sh

1735

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

1737 search_form $search_form_args > $sf

1738 echo "$gecos さん" > $tf

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

1740 viewtable $formdir/user.def user $1

1741 echo "</div>"

1742 } > $pf

1743

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

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

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

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

1748 if true; then

1749 tbl=user_m

1750 enticond="name='$uname'"

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

1752 else

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

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

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

1756 echo '">'

1757 }

1758 fi > $imf

1759 fi

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

1761 val='$uname';"`

1762 listblog $uname > $bf

1763

1764 hometail=$tmpd/tail.$$

1765 mkfifo $hometail

1766

1767 #Calling listgroupbytable, originally here

1768

1769 (

1770 # Display Most Recent Entry

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

1772 shortval=${shortval:-val}

1773

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

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

1776 ctime,

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

1778 (SELECT gecos FROM gecoses

1779 WHERE name=(SELECT val FROM blog_s

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

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

1782 FROM blog b

1783 JOIN

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

1785 FROM article a, article_s s

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

1787 GROUP BY blogid ORDER BY val DESC LIMIT 50

1788 ) m

1789 ON b.id=m.blogid;"

1790 # This should be as follows

1791 : <<EOF

1792 WITH arts AS(

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

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

1795 FROM article a NATURAL JOIN article_s s

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

1797 GROUP by s.id

1798 )

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

1800 FROM arts a0

1801 JOIN

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

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

1804 ORDER BY ctime DESC LIMIT 50;

1805 EOF

1806

1807 cat<<-EOF

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

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

1810 <div class="lcto">

1811 `DT_VIEW=replyblog dumptable html blog`

1812 </div>

1813 EOF

1814 unset DT_SQL

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

1816 # Display NEWS

1817 # 2016-06-26

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

1819 acclog blog $blogreadflagrowid

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

1821 fi

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

1823 sql=`listnewblogsql "$user"`

1824 # echo "$sql" > tmp/listnew

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

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

1827 cont=$((cont-1))

1828 err newcount=$cont

1829 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

1836 `cgi_submit '確定'`

1837 `cgi_hidden read yes`

1838 EOF

1839 echo "$new10 "

1840 echo "</div>"

1841 else # If news is 0, set log cut off flag

1842 acclog blog $blogcutoffflagrowid # for speed

1843 fi

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

1845 : # DT_SQL=

1846 fi

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

1848 #

1849 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

1855 -D_NBLOG_="$nblog" \

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

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

1858 $layout/html.m4.html $layout/home.m4.html

1859

1860 # Record access log

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

1862 }

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

1864 contenttype; echo

1865 ## err commission: "$@"

1866 gname=`getgroupbyid $1`

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

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

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

1870 grp_reg_adm "$@"

1871 else

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

1873 fi

1874 }

1875 listgroupbytable() {

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

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

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

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

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

1881 NGsql="select distinct tag from\

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

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

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

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

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

1887 for tag in `query "$sql"`

1888 do

1889 ## err ListGrp: tag=$tag

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

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

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

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

1894 as rowid,

1895 gname,

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

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

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

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

1900 ## err PersonalGroupList= `echo $sql`

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

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

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

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

1905 echo '</table>'

1906 done

1907 echo '</div>'

1908 }

1909 iconhref() (

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

1911 data=`percenthex "$1"`

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

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

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

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

1916 )

1917 iconhref2() (

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

1919 src=$1

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

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

1922 )

1923 listentry() (

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

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

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

1927 cond='' hiddens=''

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

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

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

1931 offset=$((offset-1))

1932 fi

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

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

1935 limit=30

1936 dir=`getcachedir "$1"`

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

1938 hrb="$myname?home"

1939 deficon=person-default.png

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

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

1942 gcs=gecos

1943 else # if group

1944 hrb="$myname?grp"

1945 deficon=group-default.png

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

1947 gcs=name

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

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

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

1951 ## err tagconv=$tagconv

1952 fi

1953 fi

1954 if [ ! -d $dir ]; then

1955 mkdir -p $dir

1956 fi

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

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

1959 fi

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

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

1962 fi

1963

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

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

1966 ##qgrp=`sqlquote $grp`

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

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

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

1970 quote(a.$nm) as qname,

1971 (SELECT val FROM ${tbl}_s

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

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

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

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

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

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

1978 end

1979 as name,

1980 b.tag,

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

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

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

1984 then '(ADMIN)'

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

1986 then '(Member)'

1987 when '$iamowner' = '' then ''

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

1989 CASE '$entity'

1990 WHEN 'グループ'

1991 THEN coalesce(

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

1993 'open')

1994 ||

1995 CASE WHEN '$user'

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

1997 THEN ' ismember'

1998 ELSE ''

1999 END

2000 ELSE 'user'

2001 END regmode

2002 from $tbl a left join

2003 (select $nm as name,

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

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

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

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

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

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

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

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

2012 # Note that mtime is stored only in grp_s.

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

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

2015 echo "${entity} 一覧" | html h2

2016 if [ $total -gt $limit ]; then

2017 echo '<div class="right">'

2018 cgi_form $stage <<EOF

2019 <label>次の語を含む${entity}で検索:

2020 `cgi_text kwd $kwd`</label>

2021 EOF

2022 echo '</div>'

2023 fi

2024 hiddens="$hiddens

2025 `cgi_hidden kwd \"$kwd\"`

2026 `cgi_hidden stage \"$stage\"`"

2027 cat<<EOF

2028 <form action="$myname" method="POST">

2029 <p>${total}件中の<input class="hidesub" type="text" name="start"

2030 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2031 <input type="submit" value="確定"></p>

2032 </form>

2033 EOF

2034 if [ $((offset+limit)) -lt $total ]; then

2035 nextbtn=$(

2036 cat<<EOF

2037 <div class="right clear"><form action="$myname" method="POST">

2038 `cgi_submit 次の${limit}件`

2039 $hiddens

2040 `cgi_hidden offset $((offset + limit))`</form></div>

2041 EOF

2042 )

2043 fi

2044 if [ $offset -gt 0 ]; then

2045 prevbtn=$(

2046 cat<<EOF

2047 <form action="$myname" method="POST">

2048 `cgi_submit 前の${limit}件`

2049 $hiddens

2050 `cgi_hidden offset $((offset - limit))`</form>

2051 EOF

2052 )

2053 fi

2054 pnbtn="$nextbtn$prevbtn"

2055 echo $pnbtn

2056

2057 ## err ListEntry: `echo "$sql"\;`

2058 # sq $db here??? 2016-11-28

2059 query "$sql limit $limit ${offset:+offset $offset};" \

2060 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2061 err name=$name owner=$ownerp lnk=$lnk

2062 err newlnk=$lnk regmode=$regmode

2063 icondir=$dir/$id

2064 # Pick up only last icon

2065 echo "<div class=\"iconlist xy$thumbxy $type\">

2066 <p class=\"tag _$tag\">$tag</p>" \

2067 | _m4 $tagconv

2068 if [ -n "$NOSPEEDUP" ]; then

2069 files=`getvalbyid $tbl profimg $id $icondir`

2070 if [ -n "$files" ]; then

2071 icon=`echo "$files"|tail -1`

2072 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2073 else

2074 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2075 fi

2076 elif [ -n "$icon" -a -s "$icon" ]; then

2077 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2078 else

2079 cond="$nm=$qname"

2080 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2081 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2082 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2083 # err "img=[$img]"

2084 if [ -n "$img" ]; then

2085 echo "<a href=\"$hrb+$lnk\">"

2086 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2087 echo "</a>"

2088 else

2089 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2090 fi

2091 fi

2092 echo "<br>$name${ownerp:+<br>$ownerp}"

2093 echo "</div>"

2094 done

2095 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2096 )

2097 listmember() {

2098 listentry user "$@"

2099 }

2100 listgroup() {

2101 listentry group "$@"

2102 }

2103 hexteams() { # $1=gname, $2(optional)=user

2104 cond=${2:+" AND user='$2'"}

2105 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2106 WHERE gname='$1' AND key='team'$cond;"

2107 }

2108 showgroup() { # $1=group-rowid

2109 if [ -z "$1" ]; then

2110 grid=`getpar grid`

2111 grid=${grid%%[!0-9]*}

2112 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2113 else

2114 grid=$1

2115 fi

2116 grp=`getgroupbyid $grid`

2117 qgrp=`sqlquote "$grp"`

2118 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2119 if isgroup "$grp"; then

2120 tf=$tmpd/title.$$

2121 sf=$tmpd/search.$$

2122 bodyclass=`query "SELECT val FROM grp_s

2123 WHERE gname=$qgrp AND key='regmode';"`

2124 if ismember "$user" "$grp"; then

2125 ismember="ismember"

2126 qgrp=`sqlquote "$grp"`

2127 bodyclass="$bodyclass${bodyclass:+ }ismember"

2128 else

2129 ismember="" # bodyclass="group"

2130 fi

2131 bodyclass="$bodyclass grouphome"

2132 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2133 echo "グループ $grp" > $tf

2134

2135 showgroupsub $formdir/grp.def "$grid" | \

2136 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2137 -D_FORM_="syscmd(\`cat')" \

2138 -D_BODYCLASS_="$bodyclass" \

2139 -D_DUMPTABLE_="" \

2140 $htmlheader $sf $layout/form+dump.m4.html

2141 # $htmlheader $layout/form+dump.m4.html

2142 # $htmlheader is defined in grp()

2143 else # if $grp is removed at par2table

2144 listgroup

2145 fi

2146 }

2147 showgroupsub() {

2148 # $1=def-file $2=group-rowid

2149 # Using $ismember

2150 rowid=$2

2151 grp=`getgroupbyid $2`

2152 qgrp=`sqlquote "$grp"`

2153 td=`getcachedir grp/"$2"`

2154 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2155 if [ -z "$rowid" ]; then

2156 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2157 #grp=`sq $db "select gname from grp where rowid=$grp"`

2158 echo "showgroupsub: invalid argument($1 $2)" | html p

2159 return

2160 fi

2161 val=`getvalbyid grp profimg $rowid $tmpd`

2162 enticond="gname=$qgrp"

2163 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2164 if [ -n "$img" ]; then

2165 cat<<-EOF

2166 <p class="groupimg">

2167 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2168 EOF

2169 fi

2170 echo "<div class=\"noprofimg\">"

2171 viewtable $1 grp $rowid

2172 echo "</div>"

2173 if isgrpowner "$user" "$grp"; then

2174 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2175 iamowner=$rowid

2176 colmd=" mode"

2177 fi

2178 if [ -n "$ismember" ]; then

2179 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2180 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2181 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2182 cat<<EOF

2183 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2184 <div class="fold clear">

2185 `cgi_checkbox send yes id="send"`<label

2186 for="send">グループ全員にメッセージ送信</label>

2187 <div>

2188 `cgi_textarea message "" "cols=60"`

2189 `cgi_submit 送信`

2190 `cgi_reset リセット`

2191 </div>

2192 `cgi_hidden grp $rowid`

2193 </div></form>

2194 EOF

2195 fi

2196 # 加入ボタン + 加入者リスト

2197 if [ -n "$ismember" ]; then

2198 ismem='checked' state="(参加中)"

2199 else

2200 nomem='checked' state="(現在非加入)"

2201 fi

2202 # このグループでの加入アドレス

2203 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2204 and key='email';"`

2205 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2206 ## and key='email';"

2207 ##err email=$eml

2208 cat <<EOF

2209 <div class="fold clear">

2210 `cgi_checkbox reg yes id="reg"`<label

2211 for="reg">自身の加入状態を操作する</label>$state

2212 <div>

2213 EOF

2214 cgi_form grp <<EOF

2215 <p>このグループに</p>

2216 <table class="b">

2217 <tr><th>メンバーとして</th><td>

2218 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2219 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2220 <tr><th>参加する場合のメイルアドレス<br>

2221 <small>(メインのアドレスとは違うものにする場合に記入<br>

2222 同じでよい場合は空欄に)</small></th>

2223 <td>`cgi_text email $eml`</td></tr>

2224 </table>

2225 `cgi_hidden grp $rowid`

2226 EOF

2227 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2228 echo "moderated (承認加入の)グループなので実際に参加できるのは

2229 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2230 fi

2231 echo '</div></div>'

2232 echo '<h2>話題一覧</h2>'

2233 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2234 cat<<-EOF

2235 <form class="summary" action="$myname" title="$thelp">

2236 `cgi_hidden owner "$grp"`

2237 `cgi_hidden kwd "@week"`

2238 `cgi_hidden stage searchart`

2239 `cgi_submit "一週間のまとめ"`

2240 </form>

2241 EOF

2242 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2243 colstate="state:稼動状態:frozen=rowclass=凍結"

2244 DT_CHLD=article:blogid \

2245 DT_VIEW=replyblog dumptable html blog \

2246 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2247

2248 getgname="(select gname from grp where rowid=$rowid)"

2249 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2250 cm="?commission+$rowid"

2251 thumbxy=50x50 listmember "" "$c" "$rowid" \

2252 |sed -e "s|$<br>$,not=$.*$|\1|" # 間違って押しやすい

2253 # team list

2254 hexteams=`hexteams "$grp"`

2255 if [ -n "$hexteams" ]; then

2256 echo "チーム一覧" | html h2

2257 echo '<div class="dumptable"><table class="b">'

2258 sq $db -html -header<<-EOF

2259 SELECT val TEAM,

2260 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2261 MEMBERS

2262 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2263 EOF

2264 echo '</table></div>'

2265 fi

2266 }

2267 grp_getbodyclass() {

2268 # Get css class name for document.

2269 # `moderated' for moderated groups

2270 # `ismember' for groups where user belongs

2271 # $1=GroupName (w/o quote)

2272 # $user=userNameCurrentlyLogin

2273 ## err grp_getbodyclass: 1="$1"

2274 qgrp=`sqlquote "$1"`

2275 query<<-EOF

2276 SELECT coalesce(

2277 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2278 'open')

2279 ||

2280 CASE WHEN '$user'

2281 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2282 THEN ' ismember'

2283 ELSE ''

2284 END;

2285 EOF

2286 }

2287 grpaction() { # $1=group-rowid

2288 err GRP_ACTION:IN

2289 grid=${1:-`getpar grp`}

2290 grp=`getgroupbyid "$grid"`

2291 if [ -z "$grp" ]; then

2292 echo "無効な指定です。" | html p; return

2293 fi

2294 if ! ismember $user "$grp"; then

2295 echo "加入者のみに許可された操作です。" | html p; return

2296 fi

2297 echo "グループ $grp 個別選択操作" \

2298 | _m4 -D_TITLE_="syscmd(\`cat')" \

2299 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2300 $layout/html.m4.html

2301

2302 isowner=""

2303 isgrpowner "$user" "$grp" && isowner="yes"

2304 usel=`getpar usel`

2305 if [ -n "$usel" ]; then

2306 uids=$(echo `echo $usel`|tr ' ' ',')

2307 ## err grpaction-1: grp=$grp, `echo $sql`

2308 text=`getpar text`

2309

2310 rm=`getpar rm` cfm=`getpar confirm`

2311 ## err rm=$rm cfm=$cfm

2312 if [ x"$rm" = x"yes" ]; then

2313 if [ "$isowner" ]; then

2314 if [ x"$rm$cfm" = x"yesyes" ]; then

2315 # Eliminate

2316 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2317 for tbl in grp_mem grp_mem_s grp_mem_m; do

2318 sql="delete from $tbl $cond;"

2319 # echo "sql=$sql"

2320 query "$sql"

2321 err rmGRPuser "$sql"

2322 done

2323 num=`query "select count(*) from user where rowid in ($uids);"`

2324 #err num=$num

2325 if [ 0$num -gt 0 ]; then

2326 sql="select coalesce(b.val,a.name) from user a left join \

2327 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2328 # err `echo "$sql"`

2329 html pre<<EOF

2330 以下の${num}名のグループ $grp 登録を解除しました。

2331 `query "$sql"`

2332 EOF

2333 fi

2334 else

2335 echo "確認のチェックがないのでやめておきます。" | html p

2336 return

2337 fi

2338 else # not Group Owner

2339 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2340 return

2341 fi

2342 cat<<EOF

2343

2344 EOF

2345 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2346 if [ -z "$text" ]; then # if msg is empty

2347 echo "なにかメッセージを..." | html p

2348 return 0

2349 fi

2350 gecos=`gecos $user`

2351 mkfrom=`getpar mkfrom`

2352 if [ x"$mkfrom" = x"yes" ]; then

2353 safegc=`echo "$gecos" | tr -d '<>@'`

2354 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2355 fromad=`email4groupbyuid "$grp" "$myuid"`

2356 mail_from="$safegc <$fromad>"

2357 else

2358 mail_from="$admin"

2359 fi

2360 MAIL_FROM=$mail_from \

2361 smail "`email4groupbyuid "$grp" $usel` $user" \

2362 "$gecos さんからのメッセージ" <<EOF

2363 $url

2364 のグループ「$grp」のメンバーである $gecos さんから、

2365 あなた宛へのメッセージです。

2366 ----------------------------------------------------------

2367 $text

2368 EOF

2369 if [ $? = 0 ]; then

2370 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2371 sql="select coalesce(b.val, a.name) from

2372 (select name from user where rowid in ($uids)) a

2373 left join user_s b on a.name=b.name and b.key='gecos';"

2374 html pre<<EOF

2375 `query "$sql"`

2376 (送信者である $gecos さんも含まれます)

2377 EOF

2378 err SendDone: `echo $sql`

2379 fi

2380 elif [ x"$rm" = x"commission" ]; then

2381 grp_reg_adm $grid $usel

2382 elif [ x"$rm" = x"addteam" ]; then

2383 team=`getpar team|sed "s/'/''/g"` # for single quotation

2384 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2385 if [ x"$team" != x"$newteam" ]; then

2386 echo "チーム名に使えない文字を除去しました" | html p

2387 team=newteam

2388 fi

2389 if [ -z "$team" -o x"$team" = x"なし" ]; then

2390 cat<<-EOF | html p

2391 有効なチーム名を入力してください。

2392 カンマだけ、「なし」という名前は使えません。

2393 EOF

2394 echo "有効なチーム名を入力してください。" | html p

2395 else

2396 grp_add_team $grid "$team" $usel

2397 fi

2398 elif [ x"$rm" = x"rmteam" ]; then

2399 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2400 rmteam=`getpar rmteam|sed "s/'/''/g"`

2401 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2402 gname='$grp' AND user='$user' AND key='team'\

2403 AND val='$rmteam';\"`" ]; then

2404 grp_rm_team $grid "$rmteam" $usel

2405 else

2406 echo "所属していないチームの除去操作はできません。"|html p

2407 fi

2408 else

2409 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2410 fi

2411 fi

2412 fi

2413 # New entry

2414 sql="WITH mems AS (

2415 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2416 ON gm.user=g.name

2417 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2418 ), posts AS (

2419 SELECT author, count(author) post

2420 FROM article NATURAL JOIN article_s

2421 WHERE blogid IN (SELECT id FROM blog_s

2422 WHERE key='owner'

2423 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2424 AND key='text'

2425 GROUP BY author

2426 ), teams AS (

2427 SELECT user, group_concat(val, ', ') team

2428 FROM grp_mem_m

2429 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2430 AND key='team'

2431 GROUP BY user

2432 ), user_post AS (

2433 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2434 FROM mems m LEFT JOIN posts

2435 ON m.name=posts.author

2436 GROUP by m.rowid

2437 )

2438 SELECT

2439 CASE

2440 WHEN (SELECT user FROM grp_adm

2441 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2442 AND user=up.name) IS NOT NULL

2443 then 'k'

2444 ELSE ''

2445 END || rowid || ',' || gecos NAME,

2446 post POST, team TEAM

2447 FROM user_post up LEFT JOIN teams t

2448 ON up.name=t.user

2449 ORDER BY gecos;"

2450 ## err grpaction: "`echo \"$sql\"`"

2451 tf=$tmpd/title.$$

2452 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2453 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2454 for=\"cmadmin\">グループ管理者委任</label>

2455 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2456 </p></div>"

2457 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2458 for=\"conf\">グループ登録解除</label>

2459 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2460 <p>この操作による通知は本人に行きません。

2461 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2462 </div>"

2463 # Get team list to which current user belongs into $hexteams

2464 myhexteams=$(hexteams "$grp" "$user")

2465 allhexteams=$(hexteams "$grp")

2466 if [ -n "$myhexteams" ]; then

2467 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2468 for=\"cmrmteam\">チーム属性除去</label>

2469 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2470 を除去します: `cgi_checkbox teamconfirm yes` 確認

2471 <p>この操作による通知は本人に行きません。

2472 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2473 </div>

2474 "

2475 fi

2476 b1='<label> <input type="checkbox" name="usel" value="'

2477 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2478 #b2='"> <span>' b3='</span></label>'

2479 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2480 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2481 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2482 cgi_form grpaction<<EOF \

2483 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2484 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2485 | _m4 -D_TITLE_="spaste(\`$tf')" \

2486 -D_SUBTITLE_="チェック後操作ボタン" \

2487 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2488 $layout/form+dump.m4.html

2489 <p>下でチェックした人を対象として:</p>

2490 <div class="foldtabs">

2491 `cgi_radio rm addteam 'id="cmteam"'`<label

2492 for="cmteam">同じチーム属性を付与</label>

2493 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2494 `cgi_datalist_h teams $allhexteams`

2495 </div>

2496 ${rmteammsg}

2497 `cgi_radio rm send id="sendmsg"`<label

2498 for="sendmsg">メッセージ送信</label>

2499 <div>

2500 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2501 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2502 `cgi_textarea text "" cols=40`

2503 </div>

2504 ${isowner:+$cmmsg$excmsg}

2505 `cgi_radio rm close id="x"`<label for="x">×</label>

2506 </div>

2507 <h4>$grp 参加者一覧</h4>

2508 <table class="td2r">

2509 `sq $db -header -html "$sql"`

2510 </table>

2511 `cgi_hidden grp $grid`

2512 EOF

2513 }

2514 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2515 # Create TEMPORARY VIEW

2516 query<<EOF

2517 CREATE TEMPORARY VIEW writeusers AS

2518 SELECT DISTINCT author FROM article

2519 WHERE id in (

2520 select id from article where blogid=(select id from blog where rowid=$1)

2521 );

2522 CREATE TEMPORARY VIEW movablegroups AS

2523 SELECT g.rowid growid , g.gname

2524 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2525 ON grp.gname=gm.gname -- そのユーザが属している

2526 AND user='$user') g -- グループに絞る

2527 WHERE (SELECT author FROM writeusers

2528 EXCEPT

2529 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2530 IS NULL;

2531 $2

2532 EOF

2533 }

2534 sql4readableblogs() {

2535 # Create view of blogs that can be readable to $user

2536 # Blog is readable when:

2537 # 1: blog owner is an user

2538 # 2: else, 2.1: owner-group where the $user belongs

2539 # 2.2: else, owner-group is not moderated

2540 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2541 cat<<EOF ## | tee tmp/sql.out

2542 CREATE TEMPORARY VIEW readableblogs AS

2543 SELECT blog.rowid rid, id, author

2544 FROM blog

2545 NATURAL JOIN

2546 (SELECT id,

2547 max(CASE key WHEN 'owner' THEN val END) owner,

2548 max(CASE key WHEN 'mode' THEN val END) mode

2549 FROM blog_s GROUP by id) bs

2550 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2551 THEN 1 -- blog owner is an user, READABLE

2552 WHEN (SELECT val FROM grp_s

2553 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2554 AND

2555 (SELECT user FROM grp_mem

2556 WHERE gname=bs.owner AND user='$user') IS NULL

2557 THEN 0

2558 WHEN mode = 'quiz'

2559 THEN 0 -- "quiz" mode blog cannot be searched

2560 ELSE 1

2561 END;

2562 EOF

2563 }

2564 editheading() { # $1=rowid-of-heading

2565 rowid=${1%%[!A-Z0-9a-z_]*}

2566 if [ -z "$rowid" ]; then

2567 echo "話題番号が未指定です。" | html p

2568 return

2569 fi

2570 owner=`getvalbyid blog owner $rowid`

2571 title=`getvalbyid blog title $rowid`

2572 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2573 | _m4 -D_TITLE_="修正" \

2574 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2575 -D_BLOGS_="" -D_DUMPTABLE_="" \

2576 -D_FORM_="syscmd(\`cat')" \

2577 $layout/html.m4.html $layout/form+dump.m4.html

2578 # Move to group

2579 if isuser "$owner"; then

2580 crview4article $rowid

2581 n=`query "SELECT count(*) FROM writeusers;"`

2582 ## err N=$n

2583 if [ $((n)) -gt 0 ]; then

2584 ## err ROWID=$rowid

2585 sql="SELECT growid || ':' || gname FROM movablegroups;"

2586 cat<<-EOF

2587 <div class="fold">

2588 `cgi_checkbox mv send id="mv"`<label

2589 for="mv">この話題をグループ所有に移動する</label>

2590 <div>

2591 <form action="?mvart" method="POST" enctype="multipart/form-data">

2592 移動先グループ:

2593 <select name="mv2grp">

2594 EOF

2595 query ".mode html"

2596 query<<-EOF |

2597 $sql

2598 .mode list

2599 EOF

2600 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2601 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2602 cat<<-EOF

2603 </select>

2604 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2605 そのグループに加入しているものに限られます)</p>

2606 <p>`cgi_checkbox cfm yes`<label>確認

2607 (この操作は元に戻すことができません)</label></p>

2608 `cgi_hidden blogrowid $rowid`

2609 `cgi_submit 移動`

2610 `cgi_reset Reset`

2611 </form>

2612 </div>

2613 </div>

2614 EOF

2615 fi

2616 # end of isuser "$owner"

2617 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2618 [ -n "$hexteams" ];}; then

2619 none="`echo なし|hexize`"

2620 cat<<-EOF

2621 <div class="fold">

2622 `cgi_checkbox mv2team send id="mv2team"`<label

2623 for="mv2team">この話題を以下のチームのものにする</label>

2624 <div><p>現在の所属チーム設定:

2625 `query "SELECT

2626 coalesce((SELECT val FROM blog_s

2627 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2628 AND key='team'),

2629 ':なし');"`</p>

2630 <form action="?mvart" method="POST" enctype="multipart/form-data">

2631 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2632 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2633 `cgi_hidden blogrowid $rowid`<br>

2634 `cgi_submit 移動`

2635 `cgi_reset Reset`

2636 </form></div></div>

2637 EOF

2638 fi

2639 }

2640 mvart() { # move diary to some group or team

2641 # or move blog of group to team which belong to the group

2642 blogrowid=`getpar blogrowid`

2643 cfm=`getpar cfm`

2644 ##### echo move blog:$blogrowid to $mv2grp | html p

2645 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2646 . ./s4-blog.sh

2647 if [ -z "$blogrowid" ]; then

2648 echo "無効な指定です(mvart)。" | html p

2649 return

2650 elif [ x"$cfm" != x"yes" ]; then

2651 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2652 elif { mv2grp=`getpar mv2grp`

2653 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2654 [ -n "$mv2grp" ]; }; then

2655 crview4article $blogrowid

2656 ########## TRANSACTION BEGIN

2657 query "BEGIN;"

2658 n=`query "SELECT count(*) FROM writeusers;"`

2659 ## err Nwriteuser=$n

2660 if [ $((n)) -gt 0 ]; then

2661 query<<-EOF

2662 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2663 WHERE key='owner'

2664 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2665 AND $mv2grp IN (SELECT growid FROM movablegroups);

2666 EOF

2667 fi

2668 query "END;"

2669 ########## TRANSACTION END

2670 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2671 [ -n "$mv2team" ];}; then

2672 # blog owner can move it to ANY team

2673 case "$mv2team" in

2674 'なし')

2675 cat<<-EOF

2676 DELETE FROM blog_s

2677 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2678 AND key='team';

2679 EOF

2680 ;;

2681 "") ;;

2682 *)cat<<-EOF

2683 BEGIN;

2684 REPLACE INTO blog_s(id, key, val)

2685 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2686 'team', '$mv2team');

2687 REPLACE INTO blog_s(id, key, val)

2688 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2689 'notify', 'all'); -- Change notify to all

2690 END;

2691 EOF

2692 esac | query

2693 fi

2694 blog_reply $blogrowid

2695 echo yes | html p

2696 }

2697 editart() { # $1=article-rowid $2=blogrowid

2698 rowid=${1%%[!A-Z0-9a-z_]*}

2699 blogrowid=${2%%[!A-Z0-9a-z_]*}

2700 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2701 echo "表示する記事番号が未指定です。" | html p

2702 return

2703 fi

2704 owner=`getvalbyid blog owner $blogrowid`

2705 title=`getvalbyid blog title $blogrowid`

2706 author=`getvalbyid article author $rowid`

2707 ## err EDITart: owner=$owner, author=$author

2708 if isgrpowner "$user" "$owner"; then

2709 : EDIT OK

2710 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2711 echo "本人か所有者しか編集できません." | html p

2712 return

2713 fi

2714 aid=`query "select id from article where rowid=$rowid;"`

2715 tmpout=$tmpd/editart.$$.out

2716 GF_ACTION="?replyblog+$blogrowid#$aid" \

2717 edittable $formdir/article.def article $rowid \

2718 > $tmpout

2719 rm -f /tmp/editart.out

2720 # Cannot use pipelining to m4 with genform() because of stdin stack

2721 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2722 -D_FORM_="syscmd(cat $tmpout)" \

2723 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2724 -D_BLOGS_= -D_DUMPTABLE_= \

2725 $layout/html.m4.html $layout/form+dump.m4.html

2726 }

2727 send2mem() {

2728 rowid=`getpar grp`

2729 rowid=${rowid%%[!0-9]*} # Cleaning

2730 if [ -z "$rowid" ]; then

2731 echo "グループが未指定です。" | html p

2732 return

2733 fi

2734 message=`getpar message`

2735 if [ -z "$message" ]; then

2736 echo "文章を入れてください。" | html p

2737 return

2738 fi

2739 grp=`getgroupbyid $rowid`

2740 members=`collectemail $grp`

2741 # smail rcpt subj (file)

2742 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$admin>" \

2743 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2744 $urlbase?grp+$rowid

2745 グループ $grp に所属する

2746 `gecos $user` さんよりメッセージ:

2747

2748 $message

2749 EOF

2750 cat<<EOF

2751 <p>以下のユーザに送信しました。</p>

2752 <pre>

2753 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

2754 </pre>

2755 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2756 EOF

2757 }

2758 joingrpadmit() {

2759 # $1=yes/no $2=session-key

2760 if [ -z "$2" ]; then

2761 echo "bye bye" | html p; return

2762 fi

2763 t_usr=`session=$2 getpar user`

2764 t_grp=`session=$2 getpar group`

2765 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2766 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2767 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2768 echo "無効な加入依頼です。" | html p

2769 echo "有効期限が切れたか、

2770 他の管理者がいる場合は処理済みの可能性があります。" | html p

2771 return

2772 fi

2773 if ! isgrpowner "$user" "$t_grp"; then

2774 echo "グループ管理者のみの機能です。" | html p; return

2775 fi

2776 case $1 in

2777 yes) joingrp "$t_grp" "$t_usr" yes ;;

2778 no) joingrp "$t_grp" "$t_usr" no ;;

2779 *)

2780 echo "無効な指定です($1)。" | html p

2781 return ;;

2782 esac

2783 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

2784 rcpts="`getgroupadminmails $t_grp` $user"

2785 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2786 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2787 に

2788 $t_usr

2789 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2790 しました。"

2791 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2792 query "delete from session where id='$2';"

2793 echo "$body" | html p

2794 }

2795

2796 joingrprequest() {

2797 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2798 jss="joingrp-`date +%s`-`genrandom 12`"

2799 addsession $jss +${memoplimitdays}days

2800 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

2801 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

2802 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2803 $url

2804 $user さんから

2805 グループ $1

2806 に加入依頼がありました。

2807

2808 承認する:

2809 $urlbase?joingrpadmit+yes+$jss

2810

2811 白紙に戻す:

2812 $urlbase?joingrpadmit+no+$jss

2813 EOF

2814 echo "管理者に加入依頼を出しました。

2815 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2816 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2817 }

2818 joingrp() {

2819 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2820 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2821 if isgrpowner "$user" "$1"; then

2822 isowner="yes"

2823 elif [ -n "$5" ]; then

2824 isowner="yes"

2825 else

2826 isowner=""

2827 fi

2828 ## err jg:isgrpowner: isowner="$isowner"

2829 if [ -n "$isowner" ]; then

2830 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2831 elif [ x"$2" != x"$user" ]; then # if user is not login user

2832 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2833 return

2834 elif [ x"$3" = x"no" ]; then

2835 : # Do not pursue those who leave

2836 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2837 : # Member can change own email address for the joining moderated group

2838 else # adding user is $user itself

2839 case `getgroupattr $1 regmode` in

2840 moderated)

2841 joingrprequest "$@" # Request only

2842 return

2843 ;;

2844 *)

2845 ;;

2846 esac

2847 fi

2848 qgname=`sqlquote "$1"`

2849 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

2850 cond="where gname=$qgname and user='$2'"

2851 if [ x"$3" = x"yes" ]; then

2852 query "replace into grp_mem values($qgname, '$2');"

2853 # Notify joingrp to admin

2854 action="に加入しました。"

2855 if [ -n "$4" ]; then

2856 if msg=`emaildomaincheck "$4"`; then

2857 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2858 'string', '$4', NULL);"

2859 else

2860 echo $msg

2861 fi

2862 else

2863 query "delete from grp_mem_s $cond and key='email';"

2864 fi

2865 if [ -n "$5" ]; then # as ADMIN

2866 # Coming here means newly created group

2867 sql="select case\

2868 when (select count(*) from grp_mem where gname=$qgname)=1\

2869 then (select user from grp_mem\

2870 where gname=$qgname and user='$user')\

2871 else '' end; "

2872 err NewGrpChk: $sql

2873 if [ -n "`query \"$sql\"`" ]; then

2874 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

2875 query "replace into grp_adm values($qgname, '$user');"

2876 fi

2877 fi

2878 else

2879 query "delete from grp_mem $cond;

2880 delete from grp_mem_s $cond;

2881 delete from grp_mem_m $cond;"

2882 action="から脱退しました。"

2883 fi

2884 smail "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

2885 $url?grp+$grid

2886 $user (`gecos $user`)さんが

2887 グループ $1

2888 $action

2889 EOF

2890 }

2891 grp_add_team() (

2892 # $1=grp-rowid $2=team $3...=user-rowid(s)

2893 grp=`getgroupbyid $1`

2894 team=$2; shift; shift

2895 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2896 { echo "BEGIN;"

2897 for user; do

2898 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2899 '$grp',\

2900 (SELECT name FROM user WHERE rowid=$user),\

2901 'team', 'string', '$team');"

2902 done

2903 echo "END;"

2904 } | query

2905 )

2906 grp_rm_team() (

2907 # $1=grp-rowid $2=team $3...=user-rowid(s)

2908 grid=$1

2909 qgrp=$(sqlquote "`getgroupbyid $grid`")

2910 team=$2; shift; shift

2911 [ -z "$grid" -o -z "$team" ] && return

2912 { echo "BEGIN;"

2913 for user; do

2914 echo "DELETE FROM grp_mem_m\

2915 WHERE gname=$qgrp \

2916 AND user=(SELECT name FROM user WHERE rowid=$user)\

2917 AND key='team' AND val='$team';"

2918 done

2919 cat<<-EOF

2920 DELETE FROM blog_s

2921 WHERE rowid=(

2922 SELECT rowid

2923 FROM blog_s a

2924 WHERE key='team'

2925 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2926 AND NOT EXISTS (SELECT * FROM grp_mem_m

2927 WHERE key='team' AND val=a.val -- a.val=team

2928 AND gname = (SELECT val FROM blog_s b

2929 WHERE a.id=b.id AND key='owner')

2930 ));

2931 EOF

2932

2933 echo "END;"

2934 } | query

2935 )

2936 grp_reg_adm() {

2937 # $1=grp-rowid $2...=user-rowid

2938 grid=$1

2939 grp=`getgroupbyid "$1"`

2940 if [ -z "$grp" ]; then

2941 echo "無効なグループIDです" | html p; return

2942 fi

2943 if ! isgrpowner "$user" "$grp"; then

2944 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2945 fi

2946 shift

2947 for urid; do

2948 newadm=`query "select name from user where rowid=$urid;"`

2949 if [ -z "$newadm" ]; then

2950 echo "指定ユーザIDがおかしいようです。" | html p; return

2951 fi

2952 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

2953 err ismember $newadm $grp

2954 if ismember $newadm "$grp"; then

2955 # OK, go ahead

2956 getgname="(select gname from grp where rowid=$grid)"

2957 query "replace into grp_adm values($getgname, '$newadm');"

2958 # confirm insertion

2959 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2960 if [ -n "`query \"$sql;\"`" ]; then

2961 echo "追加完了: $newadm" | html p

2962 else

2963 echo "追加失敗($1 $urid)" | html p

2964 fi

2965 fi

2966 showgroup $grid

2967 done

2968 }

2969 dt_rowhack() {

2970 # From: <TR>

2971 # ....

2972 # <TD>rowclass=foo</TD>

2973 # </TR>

2974 # To: <TR class="foo">....<TD>foo</TD></TR>

2975 sed -e '

2976 /^<TR>/ {

2977 :loop

2978 s/\n//

2979 N

2980 /<\/TR>/ {

2981 s/\n//

2982 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

2983 n

2984 }

2985 $q

2986 b loop

2987 }'

2988 }

2989 dumptable() {

2990 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2991 # textのフィールドだけ全てダンプにしたほうがいいか

2992 # $DT_VIEW sets link

2993 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2994 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2995 VIEW=${DT_VIEW-replyblog}

2996 if [ -n "$VIEW" ]; then

2997 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

2998 fi

2999 # $DT_CHLD=ChildTable:BindColumn

3000 if [ -n "$DT_CHLD" ]; then

3001 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3002 cntall="(select count($_i) from $_t where $_i=a.id)"

3003 # XXX: Dirty workaround for slow subquery of acclog

3004 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3005 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

3006 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3007 and id in (select id from $_t where $_i=a.id) \

3008 and val > coalesce((select time from myacclog where \

3009 tblrowid=a.rowid),\

3010 '1970-01-01'))"

3011 cnt="$cntnew as '新着', $cntall as '総数',"

3012 dt_class=" td2r td3r dumpblogs"

3013 fi

3014 # Construct join expression

3015 eav="" scols=""

3016 pk=`gettblpkey $2`

3017 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3018 substr=${substr:-%s}

3019 for col in ${3:-`gettbl_s_cols $2`}; do

3020 valvar=val

3021 case $col in

3022 gecos) scols="$scols${scols:+, }${col#}"

3023 continue ;; # built-in column name

3024 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3025 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3026 case "$as" in

3027 *:*=*) cnd=${as#*:}

3028 h=${cnd%%=*} v=${cnd#*=}

3029 h=`sqlquotestr "$h"`

3030 v=`sqlquotestr "$v"`

3031 valvar="CASE val WHEN $h THEN $v END"

3032 as=${as%%:*} ;;

3033 esac

3034 ;;

3035 *) as=${col} ;;

3036 esac

3037 ss=`printf "$substr" "$valvar"`

3038 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3039 scols="$scols${scols:+, }b.$as"

3040 done

3041 #case author when '$user' then a.rowid else '---' end as ID,

3042 sql=${DT_SQL:-"select \

3043 a.rowid as LINK,\

3044 $cnt\

3045 $scols from $2 a left join\

3046 (select $pk,$eav,

3047 max(case key when 'owner'

3048 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

3049 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

3050 ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"

3051 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3052 <div>

3053 <div class="dumptable">

3054 <table class="b$dt_class">

3055 `sq -header -cmd ".mode $1" $db "$presql$sql"`

3056 </table>

3057 </div>

3058 </div>

3059 EOF

3060 }

3061

3062 par2table() (

3063 # copy current parameters of par into destination table

3064 # $1=definition-file

3065 # Using $user and $session

3066 # Return value:

3067 # 0: Stored successfully

3068 # 1: Insufficient fillings

3069 # 2: No permission to modify the record

3070 # 3: Invalid rowid

3071 # 4: SUCCESS to delete

3072 # 5: Stop deletion for lack of confirm check

3073 # 6: Password length too short

3074 # 7: Password mismatch

3075 # 8: Old password incorrect

3076 rowid=`getpar rowid`

3077 if [ ! -e $1 ]; then

3078 echo "テーブル定義ファイルが見付かりません" | html p

3079 exit 1

3080 fi

3081 tbl=${1%.def}

3082 tbl=${tbl##*/}

3083 if [ -n "$rowid" ]; then # Modify existing entry

3084 if [ x"$tbl" = x"user" ]; then

3085 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3086 elif [ x"$tbl" = x"grp" ]; then

3087 sql="select gname from $tbl where rowid=$rowid;"

3088 ##err p2t:grp:q $sql

3089 isgrpowner "$user" "`query $sql`" && rowowner=$user

3090 else

3091 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3092 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3093 fi

3094 ### err rowowner=$rowowner

3095 if [ x"$user" != x"$rowowner" ]; then

3096 echo "他人のレコードはいじれないの" | html p

3097 return 2

3098 elif [ -z "$rowowner" ]; then

3099 echo "指定したレコードはないみたい" | html p

3100 return 3

3101 fi

3102 rm=`getpar rm` cfm=`getpar confirm`

3103 # Editing existent entry

3104 if [ x"$rm" = x"yes" ]; then

3105 if [ x"$rm$cfm" = x"yesyes" ]; then

3106 query "delete from $tbl where rowid=$rowid;"

3107 return 4

3108 else

3109 echo "消去確認のチェックがないので消さなかったの..." | html p

3110 return 5

3111 fi

3112 fi

3113 fi

3114

3115 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3116 if [ -n "$rowid" ]; then

3117 # Update of existing record

3118 for col in `gettblcols $tbl`; do

3119 val=`getparquote $col`

3120 [ -z "$val" ] && continue

3121 ## err query "update $tbl set $col=$val where rowid=$rowid"

3122 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3123 sql="update $tbl set $col=$val where rowid=$rowid;"

3124 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3125 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3126 ## User name cannot be changed with interface provided with this

3127 ## script. But we offer the trigger to change owner user

3128 ## of blog_s table.

3129 #err "select quote($col) from $tbl where rowid=$rowid;"

3130 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3131 cat<<-EOF | query

3132 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3133 -- should use EXCLUSIVE transaction outside of this.

3134 SAVEPOINT par2table;

3135 $sql

3136 update blog_s set val=$val

3137 where key='owner' and val=$old;

3138 RELEASE SAVEPOINT par2table;

3139 EOF

3140 ## XX: DIRTY Hack Ends here

3141 ## We should keep blog's owner as a single column which has

3142 ## foreign key constraint with primary key of grp/user.

3143 else

3144 query "$sql"

3145 fi

3146 done

3147 # Then, set up $pval for further insertion of tbl_s and tbl_m

3148 for col in `gettblpkey $tbl`; do

3149 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3150 pval="$pval${pval:+, }\"$val\""

3151 done

3152 else

3153 # New entry

3154 # Generate values() for primary keys

3155 for col in `gettblpkey $tbl`; do

3156 # Genuine primary keys for _m and _s

3157 val=`getvalquote $tbl $col`

3158 [ -z "$val" ] && continue

3159 pval="$pval${pval:+, }$val"

3160 done

3161 ##err pval=$pval

3162 for col in `gettblfkey $tbl`; do

3163 # args for values() to insertion into master table

3164 val=`getvalquote $tbl $col`

3165 [ -z "$val" ] && continue

3166 formaster=$formaster"${formaster:+, }$val"

3167 done

3168 formaster="$pval${formaster:+, }$formaster"

3169 ## err formaster=$formaster

3170 if [ -z "$formaster" ]; then

3171 echo "項目を全て埋めてください" | html pre

3172 return 1

3173 fi

3174 ## err "replace into $tbl values($formaster);"

3175 query "replace into $tbl values($formaster);"

3176 ## Insertion to master table, done

3177 fi

3178

3179 for kt in s m; do

3180 tb2=${tbl}_$kt

3181 for col in `gettbl_${kt}_cols $tbl`; do

3182 ptype=`getpartype $col "limit 1"`

3183

3184 # First, check update of existing entries in _m

3185 if [ $kt = m ]; then

3186 # sessID|address.1.22|string|Somewhere-x.y.z

3187 sql=""

3188 ##err dots from query "select var from par where var like '$col.%';"

3189 for v in `query "select var from par where var like '$col.%';"`; do

3190 # v=address.1.22

3191 st_rowid=${v##*.}

3192 origcol=${v%%.*} # original column derived from

3193 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3194 ##case `getpartype $v` in

3195 ## err CASE `gettbl_coltype $tbl/$origcol` in

3196 ## err edit flag = `getpar action.$v`

3197 case `getpar action.$v` in

3198 rm)

3199 if [ x`getpar confirm.$v` = x"yes" ]; then

3200 newsql="delete from $tb2"

3201 else

3202 echo "削除確認未チェック" | html p

3203 fi ;;

3204 edit)

3205 case `gettbl_coltype $tbl/$origcol` in

3206 image|document|binary)

3207 file=$tmpd/`getparfilename $v`

3208 ## err type=file=$file

3209 [ -z "$file" ] && continue

3210 bn=`sqlquotestr "${file##*/}"`

3211 bin="X'"$(hexize "$file")"'"

3212 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3213 type=\"file:$ct\"

3214 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3215 cachedir=`getcachedir "$tbl/$rowid"`

3216 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3217 rm -rf $cachedir

3218 ;;

3219 *)

3220 newsql="update $tb2 set val=(select val from par where var \

3221 like '$col.%.$st_rowid')"

3222 ;;

3223 esac

3224 ;;

3225 *) # maybe "keep", do not modify value

3226 continue

3227 ;;

3228 esac

3229 # err newsql=$newsql

3230 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3231 done

3232

3233 if [ x"$bin" = x"NULL" ]; then

3234 ## err repl:normal sql=`echo $sql`

3235 query "$sql

3236 delete from $tb2 where type='string' and val='';"

3237 ## err repl:normal done

3238 else

3239 sqlfile="$tmpd/sqlf.$$"

3240 echo "$sql" > $sqlfile

3241 ## err repl:sqlfile=`ls -lF $sqlfile`

3242 query ".read $sqlfile"

3243 ## err repl:done

3244 fi

3245 # Rest of kt==m: set multiple mode

3246 nr=`getparcount $col`

3247 else

3248 nr=1 # for kt==s, number of records is 1

3249 fi

3250

3251 i=0

3252 while [ $i -lt $nr ]; do

3253 limit="limit 1 offset $i"

3254 i=$((i+1)) # increase beforehand against continue

3255 val=`getvalquote $tbl $col "$limit"`

3256 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3257 ## err $col=$val

3258 bin=NULL

3259 ## err partype$col=`getpartype $col "$limit"`

3260 case $ptype in

3261 file) file=$tmpd/`getparfilename $col "$limit"`

3262 ## err parfile-$col=$file

3263 [ -z "$file" ] && continue

3264 bin="X'"$(hexize "$file")"'"

3265 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3266 type=\"file:$ct\" ;;

3267 "*"*) continue ;; # foreign table

3268 *) type=\"string\" ;;

3269 esac

3270 case `gettbl_coltype $tbl/$col` in

3271 password) # special care for password

3272 # name={password,pswd1,pswd2}

3273 p1=`getpar pswd1 "$limit"`

3274 if [ -z "$p1" ]; then

3275 continue # SKIP password setting, if p1 is empty

3276 else

3277 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3278 ## err pswd=$pswd

3279 if pwcheck "$pswd"; then

3280 if [ x"$p1" = x"$p2" ]; then

3281 case "$p1" in

3282 ??????????*) ;;

3283 *) echo "パスワードは10字以上にしてください。" | html p

3284 return 6;;

3285 esac

3286 val="\"`echo $p1|mypwhash`\""

3287 else

3288 echo "2つの新パスワード不一致" | html p

3289 return 7

3290 fi

3291 else

3292 echo "旧パスワード違います" | html p

3293 return 8

3294 fi

3295 fi

3296 ;;

3297 esac

3298 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3299 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3300 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3301 if [ x"$bin" = x"NULL" ]; then

3302 ## err Normal-query: `echo $sql`

3303 query "$sql"

3304 else

3305 sqlfile="$tmpd/query.$$"

3306 echo "$sql" > $sqlfile

3307 ## err sqlfile=`ls -lF $sqlfile`

3308 query ".read $sqlfile"

3309 fi

3310 ## err p2t done

3311 done

3312 done

3313 done

3314 return 0

3315 ##err donee

3316 )

3317 genform() {

3318 # $1 = form definition file

3319 # $2, $3 (optional)= table name and ROWID

3320 # If $GF_VIEWONLY set and nonNull, output values without form

3321 # If $GF_ARGS set, use it as content-strings in the form

3322 # If $GF_OWNER set, use it as value of name="owner"

3323 # If $GF_STAGE set, use it as value of name="stage"

3324 forms="" hiddens="" rowid=$3

3325 if [ ! -e "$1" ]; then

3326 echo "そのようなデータベースはないようです($2)。" | html p

3327 return

3328 elif [ -n "$2" ]; then

3329 rec=`query "select * from $2 where rowid='$rowid';"`

3330 if [ -z "$rec" ]; then

3331 pk=`gettblpkey $2`

3332 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3333 rec=`query "select rowid from $2 where $pk='$rowid';"`

3334 rowid=$rec

3335 rec=$3

3336 fi

3337 if [ -z "$rec" ]; then

3338 echo "そんなレコードはないみたいね..." | html p

3339 return

3340 fi

3341 fi

3342 if [ -z "$GF_VIEWONLY" ]; then

3343 rm='<input id="rm" name="rm" type="checkbox"

3344 value="yes"><label for="rm">このエントリの削除</label>

3345 <span>ほんとうに消しますよ(確認)!

3346 <input name="confirm" type=checkbox value="yes">はい</span>'

3347 fi

3348 # Image Cache dir

3349 ## err genform: getcache=$2/$rowid

3350 td=`getcachedir "$2/$rowid"`

3351 while IFS=: read prompt name keytype type args; do

3352 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3353 sp="${args:+ }"

3354 form="" val=""

3355 if [ -n "$rowid" ]; then

3356 # err genform2a: Seeking for "$2.$name, type=$type"

3357 rawval=`getvalbyid $2 $name $rowid $td`

3358 val=`echo "$rawval"|htmlescape`

3359 ## err genform3a: getvalbyid $2 $name $rowid $td

3360 ## err genform3b: val="[$val]" type="$type"

3361 fi

3362 if [ -n "$GF_VIEWONLY" ]; then

3363 is_hidden "$2" "$name" && continue

3364 fi

3365 case "$type" in

3366 text*)

3367 cgiform=cgi_multi_$type

3368 if [ -s $td/$name.count -a -n "$val" ]; then

3369 form=`$cgiform $name $td`

3370 val=$(echo "$val"|

3371 while read fn; do

3372 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3373 </td></tr>$nl"

3374 done)

3375 val="<table>$nl$val$nl</table>"

3376 else

3377 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3378 form=`cgi_$type $name "$rawval" "$args"`

3379 fi

3380 ;;

3381 [Rr][Aa][Dd][Ii][Oo])

3382 fh="<label><input type=\"radio\" name=\"$name\""

3383 form="`echo $args|sed -e \

3384 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3385 ;;

3386 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3387 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3388 ;;

3389 [Ss][Ee][Ll][Ee][Cc][Tt])

3390 fh="<select name=\"$name\">$nl"

3391 form=$(for l in $args; do

3392 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3393 done)

3394 if [ -n "$val" ]; then

3395 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3396 fi

3397 form="$fh$form</select>"

3398 ;;

3399 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3400 if [ -s $td/$name.count ]; then

3401 form=`cgi_multi_file $name $td "$args"`

3402 if [ -n "$val" ]; then

3403 hrfb="$myname?showattc+$2_m"

3404 val=$(echo "$rawval" \

3405 | while read fn; do

3406 data=`percenthex "$td/$fn"`

3407 #ct=`cat $td/$fn.content-type`

3408 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3409 ri=`cat "$td/$fn.rowid"`

3410 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3411 #imgsrc="<img src=\"data:$ct,$data\">"

3412 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3413 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3414 done)

3415 fi

3416 else

3417 form="<input type=\"file\" name=\"$name\" $args>"

3418 if [ -n "$val" ]; then

3419 imgs=$(echo "$rawval"\

3420 |while read fn;do

3421 data=`percenthex "$td/$fn"`

3422 echo "<img src=\"data:image/png,$data\">$fn<br>"

3423 done)

3424 form=$form"<br>$imgs"

3425 val=$imgs # 2015-06-15

3426 else

3427 form="<input type=\"file\" name=\"$name\" $args>"

3428 fi

3429 fi

3430 ;;

3431 [Hh][Ii][Dd][Dd][Ee][Nn])

3432 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3433 args="value=\"$GF_STAGE\""

3434 fi

3435 form="<input type=\"hidden\" name=\"$name\" $args>"

3436 prompt='' # Remove prompt

3437 ;;

3438 [Aa][Uu][Tt][Hh][Oo][Rr])

3439 [ -n "$GF_VIEWONLY" ] && continue

3440 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3441 prompt="" ;;

3442 [Oo][Ww][Nn][Ee][Rr])

3443 [ -n "$GF_VIEWONLY" ] && continue

3444 val=${GF_OWNER:-$val}

3445 val=${val:-$user}

3446 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3447 prompt="" ;;

3448 [Uu][Ss][Ee][Rr])

3449 # XXX: is null $user ok?

3450 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3451 [ -n "$GF_VIEWONLY" ] && continue

3452 form="$user"

3453 ;;

3454 [Pp]assword)

3455 [ -n "$GF_VIEWONLY" ] && continue

3456 form="`cgi_passwd`"

3457 val=""

3458 ;;

3459 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3460 [ -n "$GF_VIEWONLY" ] && continue

3461 if [ -z "$rowid" ]; then

3462 val=`genserial`

3463 fi

3464 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3465 prompt="" ;;

3466 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3467 prompt=""

3468 ;;

3469 parent|path|blog*)

3470 prompt=""

3471 ;;

3472 "*"*)

3473 tail=$tail"``"

3474 continue ;;

3475 esac

3476 if [ -n "$prompt" ]; then

3477 if [ -n "${GF_VIEWONLY}" ]; then

3478 form=$val

3479 else

3480 :

3481 fi

3482 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3483 else

3484 hiddens=$hiddens$nl"$form"

3485 fi

3486 done < $1

3487 # enctype="multipart/form-data"

3488 cat<<EOF

3489 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3490 ${rowid:+$rm}

3491 <table class="b $2">

3492 $forms

3493 </table>$hiddens

3494 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3495 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3496 EOF

3497 if [ -z $GF_VIEWONLY ]; then

3498 cat<<EOF

3499 <input type="submit" name="sub" value="OK">

3500 <input type="reset" name="res" value="Reset">

3501 EOF

3502 fi

3503 cat<<EOF

3504 $GF_ARGS</form>

3505 $tail

3506 EOF

3507 }

3508 edittable() {

3509 # $1=form-def $2=table $3 rowid

3510 genform "$@"

3511 }

3512 viewtable() {

3513 GF_VIEWONLY=1 genform "$@"

3514 }

3515 showattc() {

3516 # $1=table_m $2=rowid &optional $3=RawFlag

3517 ## err \$1=$1 \$2=$2 \$3=$3

3518 if ! isfilereadable $user $1 $2; then

3519 contenttype; echo

3520 echo "このファイルは管理者のみしか見られません" | html p

3521 putfooter; exit

3522 fi

3523 idir=`umask 002; mktempd` || exit 1

3524 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3525 bin=$idir/$myname-$$.bin

3526 sql="select quote(bin) from $1 where rowid='$2';"

3527 ## err showattc: sql: $sql

3528 sq $db "$sql" | unhexize > $bin

3529 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3530 type=${tv%//*} fn=${tv#*//}

3531 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3532 ct=${type#file:}

3533 case $ct in # all text/* changed to text/plain

3534 text/*)

3535 charset=`nkf -g $bin|cut -d' ' -f1`

3536 case $charset in

3537 ASCII*) charset="" ;;

3538 esac

3539 if [ -z "$3" ]; then

3540 ct="text/html${charset:+; charset=$charset}"

3541 link="?showattc+$1+$2+raw"

3542 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3543 | sed 's,^,<span></span>,' \

3544 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3545 -D_LINK_="$link" \

3546 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3547 exit $?

3548 fi

3549 ct="text/plain${charset:+; charset=$charset}"

3550 ;;

3551 esac

3552 contenttype "$ct"

3553 echo "Content-Disposition: filename=\"$fn\""

3554 echo "Content-Length: " `cat $bin | wc -c`; echo

3555 #echo "Content-Type: " ${type#file:}; echo

3556 cat $bin

3557 }

3558 #

3559 # Some default stupid handler on CGI values

3560 #

3561 default_storedb() {

3562 # ARG: $1=table-def-file

3563 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3564 tbl=`basename $1`

3565 tbl=${tbl%.def}

3566 cols="`grep :text $1|cut -d: -f2`"

3567 col=`echo "$cols"|head -1`

3568 vcol=`getpar $col`

3569 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3570 if [ -n "$vcol" ]; then

3571 par2table $1

3572 else

3573 return 2 # No insertion occurred

3574 fi

3575 }

3576

3577 default_view() { # $1=def-file

3578 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3579 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3580 default_storedb "$@"

3581 query "select rowid from $tbl order by rowid desc;" \

3582 | while read rowid; do

3583 viewtable $1 $tbl $rowid

3584 done | _m4 -D_TITLE_="$tbl" \

3585 -D_FORM_="`genform $1`" \

3586 -D_DUMPTABLE_="syscmd(cat)" \

3587 $layout/html.m4.html $layout/form+dump.m4.html

3588 }

3589 default_viewtext() { # $1=def-file

3590 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3591 default_storedb "$@"

3592 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3593 | _m4 -D_TITLE_="$tbl" \

3594 -D_FORM_="`genform $1`" \

3595 -D_DUMPTABLE_="syscmd(cat)" \

3596 $layout/html.m4.html $layout/form+dump.m4.html

3597 }

3598 default_smail() {

3599 default_storedb "$@"

3600 if [ $? -eq 2 ]; then

3601 _m4 -D_TITLE_="入力" \

3602 -D_FORM_="`genform $1`" \

3603 -D_DUMPTABLE_="" \

3604 $layout/html.m4.html $layout/form+dump.m4.html

3605 return

3606 fi

3607 cond=""

3608 for pk in `gettblpkey $tbl`; do

3609 pv=$(sqlquote "$(getpar $pk)")

3610 cond="$cond${cond:+ and }$pk=$pv"

3611 done

3612 sql="select rowid from $tbl where $cond;"

3613 rowid=`query "$sql"`

3614 ## err smail1 - "$sql" "-> rowid=$rowid"

3615

3616 while IFS=: read prompt name keytype type args; do # Read from $1

3617 val=`getpar $name`

3618 if [ -n "$val" ]; then

3619 text="$text

3620 $prompt

3621 $name=$val

3622 ---------------------------------------------------------"

3623 fi

3624 case "$type" in

3625 image|document|file)

3626 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3627 fns=$(echo "$fn"|while read fn; do

3628 err mv $tmpd/$fn.orig $tmpd/$fn

3629 mv $tmpd/$fn.orig $tmpd/$fn

3630 rm $tmpd/$fn.rowid # Remove cache flag

3631 ## err "`ls $tmpd/$fn`"

3632 echo $fn

3633 done)

3634 files="$files $fns"

3635 ;;

3636 esac

3637 done < $1

3638 ## err FILES=$files "`ls -lF $tmpd`"

3639 subj="from ${REMOTE_ADDR}"

3640 (echo "$url"

3641 echo "への書き込みがありました。"

3642 echo "------"

3643 echo "$text"

3644 ) | (cd $tmpd &&

3645 err LS="`ls -lF`" &&

3646 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3647 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3648 echo "以下の内容で送信しました。" | html p

3649 viewtable $1 $tbl \

3650 `query "select rowid from $tbl order by rowid desc limit 1;"`

3651 echo "戻る" | html a "href=\"?\""

3652 }