s4: c01081811298 s4-funcs.sh

s4

view s4-funcs.sh @ 475:c01081811298

typo of SQL statement

author	HIROSE Yuuji <yuuji@gentei.org>
date	Thu, 07 Sep 2017 12:14:32 +0859
parents	4f9036d35b57
children	926f61977175

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 dbsetbyid user $user login "`date '+%F %T'`"

1113 gencookie "user=$user" "skey=$newsession"

1114 return 0

1115 fi

1116 return 2 # Password mismatch

1117 }

1118 showlogin() {

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

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

1121 $layout/login.m4.html

1122 exit 0

1123 }

1124 dologin() {

1125 checkauth

1126 st=$?

1127 if [ $st != 0 ]; then

1128 contenttype; echo

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

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

1131 showlogin # and EXIT

1132 fi

1133 }

1134

1135 # Do instant jobs here

1136 dbsetup

1137 trap cleanup INT HUP EXIT TERM PIPE

1138 # trap cleanup INT HUP

1139

1140 err() {

1141 echo "$@" 1>&3

1142 }

1143

1144 cgiinit() {

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

1146 tmpf=tmp/stream

1147 tmpd=`tmpd=$tmpdir mktempd`

1148 tmpfiles=$tmpfiles" $tmpd"

1149 addsession $session

1150 getcookie

1151 case "$REQUEST_METHOD" in

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

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

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

1155 # cat > $tmpf # too much?

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

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

1158 s="`cat tmp/stream`"

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

1160 ;;

1161 esac

1162 case "$CONTENT_TYPE" in

1163 *boundary*)

1164 bndry=${CONTENT_TYPE#*boundary=}

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

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

1167 do

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

1169 #echo u=$us

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

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

1172 # err k=$k v=$v

1173 case "$k" in

1174 *:filename)

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

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

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

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

1179 :

1180 fi

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

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

1183 [Ii]mage/x-xcf)

1184 bzip2 "$tmpd/$v"

1185 v=${v}.bz2

1186 ;;

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

1188 [Ii]mage/*)

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

1190 ;;

1191 esac

1192 ;;

1193 *)

1194 type='string'

1195 ;;

1196 esac

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

1198 setpar "$k" "$type" "$v"

1199 done

1200 ;;

1201 *)

1202 setviastring par "$s"

1203 ;;

1204 esac

1205 }

1206 email4group() {

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

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

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

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

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

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

1213 and s.key='email'

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

1215 query "$sql"

1216 }

1217 email4groupbyuid() {

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

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

1220 uids=`echo "$@"`

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

1222 sql="WITH

1223 grpemails AS (

1224 SELECT gname, user, val email

1225 FROM grp_mem NATURAL JOIN grp_mem_s

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

1227 useremails AS (

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

1229 FROM user

1230 LEFT JOIN user_s

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

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

1233 FROM useremails u LEFT JOIN grpemails g

1234 ON u.name=g.user

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

1236 ## err email4gByid `echo $sql`

1237 query "$sql"

1238 }

1239 collectmembersbyid() {

1240 # Collect user names of group specified by grid

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

1242 query "SELECT user FROM grp_mem \

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

1244 }

1245 collectmembersbyid() {

1246 # Collect user names of group name

1247 qgrp=`sqlquote "$1"`

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

1249 }

1250 collectgecosesbyid() {

1251 # Collect user gecoses of group

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

1253 query<<-EOF

1254 SELECT gecos

1255 FROM gecoses

1256 WHERE name IN (SELECT user FROM grp_mem

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

1258 EOF

1259 }

1260 collectemail() (

1261 # Collect email addresses for group $1

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

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

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

1265 for e; do

1266 if isuser "$e"; then

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

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

1269 else

1270 qgrp=`sqlquote "$e"`

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

1272 gmem="grp_mem"

1273 else

1274 tm=`sqlquote "$TEAM"`

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

1276 fi

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

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

1279 $gmem g left join grp_mem_s s

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

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

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

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

1284 query "$sql"

1285 fi

1286 done

1287 )

1288 sendinvitation() (

1289 # $1=email

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

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

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

1293 gecos=`gecos`

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

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

1296 _m4 -D_URL_="$urlbase" \

1297 -D_USER_="$name" \

1298 -D_EMAIL_="$1" \

1299 -D_REGIST_="$regist" \

1300 -D_ADMIN_="$admin" \

1301 $msgdir/mail-invite.m4 \

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

1303 return 0

1304 )

1305 emaildomaincheck() {

1306 case "$1" in

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

1308 *@*)

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

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

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

1312 return 2

1313 fi

1314 return 0

1315 ;;

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

1317 esac

1318 }

1319 invite() {

1320 email=`getpar email`

1321 case $email in

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

1323 *@*)

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

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

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

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

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

1329 elif sendinvitation $email; then

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

1331 fi ;;

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

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

1334 esac

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

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

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

1338 fi

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

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

1341 $layout/html.m4.html $layout/invite.m4.html

1342 }

1343 regist() {

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

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

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

1347 echo "bye bye" | html p

1348 reutrn

1349 fi

1350 email=`session=$1 getpar invite`

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

1352 cat<<EOF

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

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

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

1356 EOF

1357 return

1358 fi

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

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

1361 # Fake login password to wasureta

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

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

1364 wasureta $email

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

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

1367 addsession $1 # for removal after 1 minute

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

1369 $layout/login.m4.html

1370 return

1371 }

1372 group_safename() {

1373 # Convert $1 to safe group name

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

1375 }

1376 groupupdate() {

1377 gname=`getpar gname`

1378 qgname=`sqlquote "$gname"`

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

1380 # See ALSO same job in showgroup()

1381 newgname=`group_safename "$gname"`

1382 err newgname=$newgname

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

1384 err NewGNAME: gname=$newgname

1385 gname=$newgname

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

1387 replpar gname string "$gname"

1388 fi

1389 # Name confliction check

1390 parow=`getpar rowid`

1391 ## err parow=$parow

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

1393 query "BEGIN EXCLUSIVE;"

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

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

1396 if [ $count -gt 0 ]; then

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

1398 query "END;"

1399 return

1400 fi

1401 par2table $formdir/grp.def

1402 query "END TRANSACTION;"

1403 # Remove orphan

1404 : <<EOF

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

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

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

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

1409 EOF

1410 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1414 sql="delete from blog where id in

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

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

1417 query "$sql";

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

1419 return # and return

1420 fi

1421 fi

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

1423 fi

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

1425 grid=$(query $sql)

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

1427 grp $grid

1428 }

1429 groupclone() {

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

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

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

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

1434 return

1435 fi

1436 i=0

1437 while true; do

1438 copy="-copy$i"

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

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

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

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

1443 i=$((i++))

1444 continue

1445 fi

1446 break

1447 done

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

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

1450 query<<-EOF

1451 BEGIN;

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

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

1454 SELECT $newqname, key, val

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

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

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

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

1459 -- Copy members and their configuration --

1460 REPLACE INTO grp_mem SELECT $newqname, user

1461 FROM grp_mem WHERE gname=$qgrp;

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

1463 FROM grp_mem_s WHERE gname=$qgrp;

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

1465 FROM grp_mem_m WHERE gname=$qgrp;

1466 -- Copy administrators --

1467 REPLACE INTO grp_adm SELECT $newqname, user

1468 FROM grp_adm WHERE gname=$qgrp;

1469 COMMIT;

1470 EOF

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

1472 STOPCLONEMSG=1 groupconf "$newrowid"

1473 }

1474 groupman() {

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

1476

1477 GF_STAGE="grpconf"

1478 GF_STAGE=groupupdate

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

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

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

1482 -D_DUMPTABLE_="syscmd(cat)" \

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

1484 }

1485 userconf() {

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

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

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

1489 }

1490 groupconf() {

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

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

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

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

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

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

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

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

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

1500 | html p

1501 fi

1502 }

1503 mems() {

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

1505 kwd=`getpar kwd`

1506 listmember $kwd

1507 }

1508 grps() {

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

1510 kwd=`getpar kwd`

1511 listgroup $kwd \

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

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

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

1515 $layout/form+dump.m4.html

1516 }

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

1518 gpg=`getpar grp`

1519 grid=${1:-$gpg}

1520 grp=`getgroupbyid "$grid"`

1521 ## . ./s4-blog.sh

1522 jg=`getpar joingrp`

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

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

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

1526 fi

1527 htmlheader=$layout/html.m4.html

1528 showgroup "$grid"

1529 }

1530 sql4interestblogs() {

1531 cat<<EOF

1532 CREATE TEMPORARY VIEW interestblogs AS

1533 SELECT blog.rowid rid, id, author

1534 FROM blog

1535 NATURAL JOIN

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

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

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

1539 WHEN (SELECT user FROM grp_mem

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

1541 THEN 0

1542 ELSE 1

1543 END;

1544 EOF

1545 }

1546 listnewblogsql() { # $1=user

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

1548 WHERE user='$user'

1549 AND tblrowid IN

1550 ($blogreadflagrowid,

1551 $blogcutoffflagrowid)),

1552 "0");"`

1553 cat<<EOF

1554 `sql4interestblogs`

1555 WITH article_ctime as (

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

1557 FROM article join article_s s using(id)

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

1559 GROUP BY id

1560 ), blog_title_owner as (

1561 SELECT blg.rid brid, id,

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

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

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

1565 ), blogall as (

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

1567 ), news as (

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

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

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

1571 FROM blogall bl

1572 LEFT JOIN

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

1574 ON bl.brid=al.tblrowid

1575 WHERE atime < bl.ctime

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

1577 LIMIT 10

1578 ) SELECT brid LINK, "新着",

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

1580 ctime, title,

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

1582 FROM news;

1583 EOF

1584 }

1585

1586 search_form() {

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

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

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

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

1591 @week → 最近一週間

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

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

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

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

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

1597 auth=""

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

1599 case "$1" in

1600 author=*)

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

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

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

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

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

1606 ;;

1607 grp=*)

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

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

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

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

1612 ;;

1613 esac

1614 cat<<-EOF

1615 <div class="right">

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

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

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

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

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

1621 </form>

1622 </div>

1623 EOF

1624 }

1625

1626 imgsrc_cache() (

1627 # $1 = directory for cache'ing

1628 # $2 = table (user_m or grp_m)

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

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

1631 dir="$1" tbl="$2"

1632 keycond="$3"

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

1634 ORDER BY rowid DESC LIMIT 1"

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

1636 tmpf=$tmpd/imgsrc_cache.$$

1637 case "$4" in

1638 [Ss]) size=S ;;

1639 [Oo]) size=O ;;

1640 *) size=M ;;

1641 esac

1642 # ImageCache filename storing schema:

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

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

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

1646 valtype=`query "$sql0"`

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

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

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

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

1651 case "$filetype" in

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

1653 esac

1654 fi

1655 cacheimg_S=$dir/S_$filename

1656 cacheimg_M=$dir/M_$filename

1657 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1665 else

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

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

1668 fifo2=$fifo.2

1669 mkfifo $fifo $fifo2

1670 fmt=${filename##*.}

1671 ## [[ NOTE ]]

1672 ## a. convert oldimage newimage

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

1674 ## b is much smaller than a

1675 cat $tmpf | unhexize \

1676 | tee $fifo \

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

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

1679 | tee $fifo2 \

1680 | convert - "$cacheimg_M" &

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

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

1683 | convert - "$cacheimg_S" &

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

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

1686 echo '">'

1687 echo "$sum" > $sumfile

1688 fi

1689 ## Now preparing cache image, done.

1690 ## Store this information to DB

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

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

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

1694 query <<-EOF

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

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

1697 EOF

1698 )

1699

1700 showhome() {

1701 # $1=userRowIdToShow

1702 err showhome \$1=$1

1703 case "$1" in

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

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

1706 esac

1707 ## err ShowHome: uname=$uname

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

1709 gecos=`gecos "$uname"`

1710 ## err SH:gecos=$gecos

1711 GF_VIEWONLY=1

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

1713 search_form_args=""

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

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

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

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

1718 # Display folders

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

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

1721 and type like 'file:%';"

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

1723 nfile=`query "$sql"`

1724 # err nfile=$nfile

1725 if [ $nfile -gt 0 ]; then

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

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

1728 fi

1729 else

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

1731 GROUP BY user;"`

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

1733 search_form_args="author=$uname"

1734 fi

1735 . ./s4-blog.sh

1736

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

1738 search_form $search_form_args > $sf

1739 echo "$gecos さん" > $tf

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

1741 viewtable $formdir/user.def user $1

1742 echo "</div>"

1743 } > $pf

1744

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

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

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

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

1749 if true; then

1750 tbl=user_m

1751 enticond="name='$uname'"

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

1753 else

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

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

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

1757 echo '">'

1758 }

1759 fi > $imf

1760 fi

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

1762 val='$uname';"`

1763 listblog $uname > $bf

1764

1765 hometail=$tmpd/tail.$$

1766 mkfifo $hometail

1767

1768 #Calling listgroupbytable, originally here

1769

1770 (

1771 # Display Most Recent Entry

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

1773 shortval=${shortval:-val}

1774

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

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

1777 ctime,

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

1779 (SELECT gecos FROM gecoses

1780 WHERE name=(SELECT val FROM blog_s

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

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

1783 FROM blog b

1784 JOIN

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

1786 FROM article a, article_s s

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

1788 GROUP BY blogid ORDER BY val DESC LIMIT 50

1789 ) m

1790 ON b.id=m.blogid;"

1791 # This should be as follows

1792 : <<EOF

1793 WITH arts AS(

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

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

1796 FROM article a NATURAL JOIN article_s s

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

1798 GROUP by s.id

1799 )

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

1801 FROM arts a0

1802 JOIN

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

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

1805 ORDER BY ctime DESC LIMIT 50;

1806 EOF

1807

1808 cat<<-EOF

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

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

1811 <div class="lcto">

1812 `DT_VIEW=replyblog dumptable html blog`

1813 </div>

1814 EOF

1815 unset DT_SQL

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

1817 # Display NEWS

1818 # 2016-06-26

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

1820 acclog blog $blogreadflagrowid

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

1822 fi

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

1824 sql=`listnewblogsql "$user"`

1825 # echo "$sql" > tmp/listnew

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

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

1828 cont=$((cont-1))

1829 err newcount=$cont

1830 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

1837 `cgi_submit '確定'`

1838 `cgi_hidden read yes`

1839 EOF

1840 echo "$new10 "

1841 echo "</div>"

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

1843 acclog blog $blogcutoffflagrowid # for speed

1844 fi

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

1846 : # DT_SQL=

1847 fi

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

1849 #

1850 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

1856 -D_NBLOG_="$nblog" \

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

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

1859 $layout/html.m4.html $layout/home.m4.html

1860

1861 # Record access log

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

1863 }

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

1865 contenttype; echo

1866 ## err commission: "$@"

1867 gname=`getgroupbyid $1`

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

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

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

1871 grp_reg_adm "$@"

1872 else

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

1874 fi

1875 }

1876 listgroupbytable() {

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

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

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

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

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

1882 NGsql="select distinct tag from\

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

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

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

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

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

1888 for tag in `query "$sql"`

1889 do

1890 ## err ListGrp: tag=$tag

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

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

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

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

1895 as rowid,

1896 gname,

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

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

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

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

1901 ## err PersonalGroupList= `echo $sql`

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

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

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

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

1906 echo '</table>'

1907 done

1908 echo '</div>'

1909 }

1910 iconhref() (

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

1912 data=`percenthex "$1"`

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

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

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

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

1917 )

1918 iconhref2() (

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

1920 src=$1

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

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

1923 )

1924 listentry() (

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

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

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

1928 cond='' hiddens=''

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

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

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

1932 offset=$((offset-1))

1933 fi

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

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

1936 limit=30

1937 dir=`getcachedir "$1"`

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

1939 hrb="$myname?home"

1940 deficon=person-default.png

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

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

1943 gcs=gecos

1944 else # if group

1945 hrb="$myname?grp"

1946 deficon=group-default.png

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

1948 gcs=name

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

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

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

1952 ## err tagconv=$tagconv

1953 fi

1954 fi

1955 if [ ! -d $dir ]; then

1956 mkdir -p $dir

1957 fi

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

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

1960 fi

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

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

1963 fi

1964

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

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

1967 ##qgrp=`sqlquote $grp`

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

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

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

1971 quote(a.$nm) as qname,

1972 (SELECT val FROM ${tbl}_s

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

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

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

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

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

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

1979 end

1980 as name,

1981 b.tag,

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

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

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

1985 then '(ADMIN)'

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

1987 then '(Member)'

1988 when '$iamowner' = '' then ''

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

1990 CASE '$entity'

1991 WHEN 'グループ'

1992 THEN coalesce(

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

1994 'open')

1995 ||

1996 CASE WHEN '$user'

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

1998 THEN ' ismember'

1999 ELSE ''

2000 END

2001 ELSE 'user'

2002 END regmode

2003 from $tbl a left join

2004 (select $nm as name,

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

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

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

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

2009 max(case key when 'login' then val end) as login

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

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

2012 order by b.wtime desc, b.login desc,

2013 b.mtime desc, b.tag desc, a.rowid asc"

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

2015 # Note that mtime is stored only in grp_s.

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

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

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

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

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

2021 cgi_form $stage <<EOF

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

2023 `cgi_text kwd $kwd`</label>

2024 EOF

2025 echo '</div>'

2026 fi

2027 hiddens="$hiddens

2028 `cgi_hidden kwd \"$kwd\"`

2029 `cgi_hidden stage \"$stage\"`"

2030 cat<<EOF

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

2032 <p>${total}件中の<input class="hidesub" type="text" name="start"

2033 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2034 <input type="submit" value="確定"></p>

2035 </form>

2036 EOF

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

2038 nextbtn=$(

2039 cat<<EOF

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

2041 `cgi_submit 次の${limit}件`

2042 $hiddens

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

2044 EOF

2045 )

2046 fi

2047 if [ $offset -gt 0 ]; then

2048 prevbtn=$(

2049 cat<<EOF

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

2051 `cgi_submit 前の${limit}件`

2052 $hiddens

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

2054 EOF

2055 )

2056 fi

2057 pnbtn="$nextbtn$prevbtn"

2058 echo $pnbtn

2059

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

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

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

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

2064 err name=$name owner=$ownerp lnk=$lnk

2065 err newlnk=$lnk regmode=$regmode

2066 icondir=$dir/$id

2067 # Pick up only last icon

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

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

2070 | _m4 $tagconv

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

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

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

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

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

2076 else

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

2078 fi

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

2080 iconhref2 "$icon" "$hrb+$lnk" "$gecos<br>$mt"

2081 else

2082 cond="$nm=$qname"

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

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

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

2086 # err "img=[$img]"

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

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

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

2090 echo "</a>"

2091 else

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

2093 fi

2094 fi

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

2096 echo "</div>"

2097 done

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

2099 )

2100 listmember() {

2101 listentry user "$@"

2102 }

2103 listgroup() {

2104 listentry group "$@"

2105 }

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

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

2108 query "SELECT DISTINCT hex(val) FROM grp_mem_m

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

2110 }

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

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

2113 grid=`getpar grid`

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

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

2116 else

2117 grid=$1

2118 fi

2119 grp=`getgroupbyid $grid`

2120 qgrp=`sqlquote "$grp"`

2121 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2122 if isgroup "$grp"; then

2123 tf=$tmpd/title.$$

2124 sf=$tmpd/search.$$

2125 bodyclass=`query "SELECT val FROM grp_s

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

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

2128 ismember="ismember"

2129 qgrp=`sqlquote "$grp"`

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

2131 else

2132 ismember="" # bodyclass="group"

2133 fi

2134 bodyclass="$bodyclass grouphome"

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

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

2137

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

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

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

2141 -D_BODYCLASS_="$bodyclass" \

2142 -D_DUMPTABLE_="" \

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

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

2145 # $htmlheader is defined in grp()

2146 else # if $grp is removed at par2table

2147 listgroup

2148 fi

2149 }

2150 showgroupsub() {

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

2152 # Using $ismember

2153 rowid=$2

2154 grp=`getgroupbyid $2`

2155 qgrp=`sqlquote "$grp"`

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

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

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

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

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

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

2162 return

2163 fi

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

2165 enticond="gname=$qgrp"

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

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

2168 cat<<-EOF

2169 <p class="groupimg">

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

2171 EOF

2172 fi

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

2174 viewtable $1 grp $rowid

2175 echo "</div>"

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

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

2178 iamowner=$rowid

2179 colmd=" mode"

2180 fi

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

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

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

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

2185 cat<<EOF

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

2187 <div class="fold clear">

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

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

2190 <div>

2191 `cgi_textarea message "" "cols=60"`

2192 `cgi_submit 送信`

2193 `cgi_reset リセット`

2194 </div>

2195 `cgi_hidden grp $rowid`

2196 </div></form>

2197 EOF

2198 fi

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

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

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

2202 else

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

2204 fi

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

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

2207 and key='email';"`

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

2209 ## and key='email';"

2210 ##err email=$eml

2211 cat <<EOF

2212 <div class="fold clear">

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

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

2215 <div>

2216 EOF

2217 cgi_form grp <<EOF

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

2219 <table class="b">

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

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

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

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

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

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

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

2227 </table>

2228 `cgi_hidden grp $rowid`

2229 EOF

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

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

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

2233 fi

2234 echo '</div></div>'

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

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

2237 cat<<-EOF

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

2239 `cgi_hidden owner "$grp"`

2240 `cgi_hidden kwd "@week"`

2241 `cgi_hidden stage searchart`

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

2243 </form>

2244 EOF

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

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

2247 DT_CHLD=article:blogid \

2248 DT_VIEW=replyblog dumptable html blog \

2249 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2250

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

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

2253 cm="?commission+$rowid"

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

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

2256 # team list

2257 hexteams=`hexteams "$grp"`

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

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

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

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

2262 SELECT val TEAM,

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

2264 MEMBERS

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

2266 EOF

2267 echo '</table></div>'

2268 fi

2269 }

2270 grp_getbodyclass() {

2271 # Get css class name for document.

2272 # `moderated' for moderated groups

2273 # `ismember' for groups where user belongs

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

2275 # $user=userNameCurrentlyLogin

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

2277 qgrp=`sqlquote "$1"`

2278 query<<-EOF

2279 SELECT coalesce(

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

2281 'open')

2282 ||

2283 CASE WHEN '$user'

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

2285 THEN ' ismember'

2286 ELSE ''

2287 END;

2288 EOF

2289 }

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

2291 err GRP_ACTION:IN

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

2293 grp=`getgroupbyid "$grid"`

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

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

2296 fi

2297 if ! ismember $user "$grp"; then

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

2299 fi

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

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

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

2303 $layout/html.m4.html

2304

2305 isowner=""

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

2307 usel=`getpar usel`

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

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

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

2311 text=`getpar text`

2312

2313 rm=`getpar rm` cfm=`getpar confirm`

2314 ## err rm=$rm cfm=$cfm

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

2316 if [ "$isowner" ]; then

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

2318 # Eliminate

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

2320 for tbl in grp_mem grp_mem_s grp_mem_m; do

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

2322 # echo "sql=$sql"

2323 query "$sql"

2324 err rmGRPuser "$sql"

2325 done

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

2327 #err num=$num

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

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

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

2331 # err `echo "$sql"`

2332 html pre<<EOF

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

2334 `query "$sql"`

2335 EOF

2336 fi

2337 else

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

2339 return

2340 fi

2341 else # not Group Owner

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

2343 return

2344 fi

2345 cat<<EOF

2346

2347 EOF

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

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

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

2351 return 0

2352 fi

2353 gecos=`gecos $user`

2354 mkfrom=`getpar mkfrom`

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

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

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

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

2359 mail_from="$safegc <$fromad>"

2360 else

2361 mail_from="$admin"

2362 fi

2363 MAIL_FROM=$mail_from \

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

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

2366 $url

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

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

2369 ----------------------------------------------------------

2370 $text

2371 EOF

2372 if [ $? = 0 ]; then

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

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

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

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

2377 html pre<<EOF

2378 `query "$sql"`

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

2380 EOF

2381 err SendDone: `echo $sql`

2382 fi

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

2384 grp_reg_adm $grid $usel

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

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

2387 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

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

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

2390 team=newteam

2391 fi

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

2393 cat<<-EOF | html p

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

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

2396 EOF

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

2398 else

2399 grp_add_team $grid "$team" $usel

2400 fi

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

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

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

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

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

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

2407 grp_rm_team $grid "$rmteam" $usel

2408 else

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

2410 fi

2411 else

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

2413 fi

2414 fi

2415 fi

2416 # New entry

2417 sql="WITH mems AS (

2418 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2419 ON gm.user=g.name

2420 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2421 ), posts AS (

2422 SELECT author, count(author) post

2423 FROM article NATURAL JOIN article_s

2424 WHERE blogid IN (SELECT id FROM blog_s

2425 WHERE key='owner'

2426 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2427 AND key='text'

2428 GROUP BY author

2429 ), teams AS (

2430 SELECT user, group_concat(val, ', ') team

2431 FROM grp_mem_m

2432 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2433 AND key='team'

2434 GROUP BY user

2435 ), user_post AS (

2436 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2437 FROM mems m LEFT JOIN posts

2438 ON m.name=posts.author

2439 GROUP by m.rowid

2440 )

2441 SELECT

2442 CASE

2443 WHEN (SELECT user FROM grp_adm

2444 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2445 AND user=up.name) IS NOT NULL

2446 then 'k'

2447 ELSE ''

2448 END || rowid || ',' || gecos NAME,

2449 post POST, team TEAM

2450 FROM user_post up LEFT JOIN teams t

2451 ON up.name=t.user

2452 ORDER BY gecos;"

2453 ## err grpaction: "`echo \"$sql\"`"

2454 tf=$tmpd/title.$$

2455 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2456 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2457 for=\"cmadmin\">グループ管理者委任</label>

2458 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2459 </p></div>"

2460 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2461 for=\"conf\">グループ登録解除</label>

2462 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2463 <p>この操作による通知は本人に行きません。

2464 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2465 </div>"

2466 # Get team list to which current user belongs into $hexteams

2467 myhexteams=$(hexteams "$grp" "$user")

2468 allhexteams=$(hexteams "$grp")

2469 if [ -n "$myhexteams" ]; then

2470 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2471 for=\"cmrmteam\">チーム属性除去</label>

2472 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2473 を除去します: `cgi_checkbox teamconfirm yes` 確認

2474 <p>この操作による通知は本人に行きません。

2475 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2476 </div>

2477 "

2478 fi

2479 b1='<label> <input type="checkbox" name="usel" value="'

2480 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2481 #b2='"> <span>' b3='</span></label>'

2482 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2483 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2484 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2485 cgi_form grpaction<<EOF \

2486 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2487 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

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

2489 -D_SUBTITLE_="チェック後操作ボタン" \

2490 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2491 $layout/form+dump.m4.html

2492 <p>下でチェックした人を対象として:</p>

2493 <div class="foldtabs">

2494 `cgi_radio rm addteam 'id="cmteam"'`<label

2495 for="cmteam">同じチーム属性を付与</label>

2496 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2497 `cgi_datalist_h teams $allhexteams`

2498 </div>

2499 ${rmteammsg}

2500 `cgi_radio rm send id="sendmsg"`<label

2501 for="sendmsg">メッセージ送信</label>

2502 <div>

2503 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2504 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2505 `cgi_textarea text "" cols=40`

2506 </div>

2507 ${isowner:+$cmmsg$excmsg}

2508 `cgi_radio rm close id="x"`<label for="x">×</label>

2509 </div>

2510 <h4>$grp 参加者一覧</h4>

2511 <table class="td2r">

2512 `sq $db -header -html "$sql"`

2513 </table>

2514 `cgi_hidden grp $grid`

2515 EOF

2516 }

2517 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2518 # Create TEMPORARY VIEW

2519 query<<EOF

2520 CREATE TEMPORARY VIEW writeusers AS

2521 SELECT DISTINCT author FROM article

2522 WHERE id in (

2523 select id from article where blogid=(select id from blog where rowid=$1)

2524 );

2525 CREATE TEMPORARY VIEW movablegroups AS

2526 SELECT g.rowid growid , g.gname

2527 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2528 ON grp.gname=gm.gname -- そのユーザが属している

2529 AND user='$user') g -- グループに絞る

2530 WHERE (SELECT author FROM writeusers

2531 EXCEPT

2532 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2533 IS NULL;

2534 $2

2535 EOF

2536 }

2537 sql4readableblogs() {

2538 # Create view of blogs that can be readable to $user

2539 # Blog is readable when:

2540 # 1: blog owner is an user

2541 # 2: else, 2.1: owner-group where the $user belongs

2542 # 2.2: else, owner-group is not moderated

2543 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2544 cat<<EOF ## | tee tmp/sql.out

2545 CREATE TEMPORARY VIEW readableblogs AS

2546 SELECT blog.rowid rid, id, author

2547 FROM blog

2548 NATURAL JOIN

2549 (SELECT id,

2550 max(CASE key WHEN 'owner' THEN val END) owner,

2551 max(CASE key WHEN 'mode' THEN val END) mode

2552 FROM blog_s GROUP by id) bs

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

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

2555 WHEN (SELECT val FROM grp_s

2556 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2557 AND

2558 (SELECT user FROM grp_mem

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

2560 THEN 0

2561 WHEN mode = 'quiz'

2562 THEN 0 -- "quiz" mode blog cannot be searched

2563 ELSE 1

2564 END;

2565 EOF

2566 }

2567 editheading() { # $1=rowid-of-heading

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

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

2570 echo "話題番号が未指定です。" | html p

2571 return

2572 fi

2573 owner=`getvalbyid blog owner $rowid`

2574 title=`getvalbyid blog title $rowid`

2575 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2576 | _m4 -D_TITLE_="修正" \

2577 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2578 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

2581 # Move to group

2582 if isuser "$owner"; then

2583 crview4article $rowid

2584 n=`query "SELECT count(*) FROM writeusers;"`

2585 ## err N=$n

2586 if [ $((n)) -gt 0 ]; then

2587 ## err ROWID=$rowid

2588 sql="SELECT growid || ':' || gname FROM movablegroups;"

2589 cat<<-EOF

2590 <div class="fold">

2591 `cgi_checkbox mv send id="mv"`<label

2592 for="mv">この話題をグループ所有に移動する</label>

2593 <div>

2594 <form action="?mvart" method="POST" enctype="multipart/form-data">

2595 移動先グループ:

2596 <select name="mv2grp">

2597 EOF

2598 query ".mode html"

2599 query<<-EOF |

2600 $sql

2601 .mode list

2602 EOF

2603 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2604 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2605 cat<<-EOF

2606 </select>

2607 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2608 そのグループに加入しているものに限られます)</p>

2609 <p>`cgi_checkbox cfm yes`<label>確認

2610 (この操作は元に戻すことができません)</label></p>

2611 `cgi_hidden blogrowid $rowid`

2612 `cgi_submit 移動`

2613 `cgi_reset Reset`

2614 </form>

2615 </div>

2616 </div>

2617 EOF

2618 fi

2619 # end of isuser "$owner"

2620 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2621 [ -n "$hexteams" ];}; then

2622 none="`echo なし|hexize`"

2623 cat<<-EOF

2624 <div class="fold">

2625 `cgi_checkbox mv2team send id="mv2team"`<label

2626 for="mv2team">この話題を以下のチームのものにする</label>

2627 <div><p>現在の所属チーム設定:

2628 `query "SELECT

2629 coalesce((SELECT val FROM blog_s

2630 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2631 AND key='team'),

2632 ':なし');"`</p>

2633 <form action="?mvart" method="POST" enctype="multipart/form-data">

2634 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2635 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2636 `cgi_hidden blogrowid $rowid`<br>

2637 `cgi_submit 移動`

2638 `cgi_reset Reset`

2639 </form></div></div>

2640 EOF

2641 fi

2642 }

2643 mvart() { # move diary to some group or team

2644 # or move blog of group to team which belong to the group

2645 blogrowid=`getpar blogrowid`

2646 cfm=`getpar cfm`

2647 ##### echo move blog:$blogrowid to $mv2grp | html p

2648 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2649 . ./s4-blog.sh

2650 if [ -z "$blogrowid" ]; then

2651 echo "無効な指定です(mvart)。" | html p

2652 return

2653 elif [ x"$cfm" != x"yes" ]; then

2654 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2655 elif { mv2grp=`getpar mv2grp`

2656 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2657 [ -n "$mv2grp" ]; }; then

2658 crview4article $blogrowid

2659 ########## TRANSACTION BEGIN

2660 query "BEGIN;"

2661 n=`query "SELECT count(*) FROM writeusers;"`

2662 ## err Nwriteuser=$n

2663 if [ $((n)) -gt 0 ]; then

2664 query<<-EOF

2665 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2666 WHERE key='owner'

2667 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2668 AND $mv2grp IN (SELECT growid FROM movablegroups);

2669 EOF

2670 fi

2671 query "END;"

2672 ########## TRANSACTION END

2673 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2674 [ -n "$mv2team" ];}; then

2675 # blog owner can move it to ANY team

2676 case "$mv2team" in

2677 'なし')

2678 cat<<-EOF

2679 DELETE FROM blog_s

2680 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2681 AND key='team';

2682 EOF

2683 ;;

2684 "") ;;

2685 *)cat<<-EOF

2686 BEGIN;

2687 REPLACE INTO blog_s(id, key, val)

2688 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2689 'team', '$mv2team');

2690 REPLACE INTO blog_s(id, key, val)

2691 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2692 'notify', 'all'); -- Change notify to all

2693 END;

2694 EOF

2695 esac | query

2696 fi

2697 blog_reply $blogrowid

2698 echo yes | html p

2699 }

2700 editart() { # $1=article-rowid $2=blogrowid

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

2702 blogrowid=${2%%[!A-Z0-9a-z_]*}

2703 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2704 echo "表示する記事番号が未指定です。" | html p

2705 return

2706 fi

2707 owner=`getvalbyid blog owner $blogrowid`

2708 title=`getvalbyid blog title $blogrowid`

2709 author=`getvalbyid article author $rowid`

2710 ## err EDITart: owner=$owner, author=$author

2711 if isgrpowner "$user" "$owner"; then

2712 : EDIT OK

2713 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2714 echo "本人か所有者しか編集できません." | html p

2715 return

2716 fi

2717 aid=`query "select id from article where rowid=$rowid;"`

2718 tmpout=$tmpd/editart.$$.out

2719 GF_ACTION="?replyblog+$blogrowid#$aid" \

2720 edittable $formdir/article.def article $rowid \

2721 > $tmpout

2722 rm -f /tmp/editart.out

2723 # Cannot use pipelining to m4 with genform() because of stdin stack

2724 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2725 -D_FORM_="syscmd(cat $tmpout)" \

2726 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2727 -D_BLOGS_= -D_DUMPTABLE_= \

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

2729 }

2730 send2mem() {

2731 rowid=`getpar grp`

2732 rowid=${rowid%%[!0-9]*} # Cleaning

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

2734 echo "グループが未指定です。" | html p

2735 return

2736 fi

2737 message=`getpar message`

2738 if [ -z "$message" ]; then

2739 echo "文章を入れてください。" | html p

2740 return

2741 fi

2742 grp=`getgroupbyid $rowid`

2743 members=`collectemail $grp`

2744 # smail rcpt subj (file)

2745 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$admin>" \

2746 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2747 $urlbase?grp+$rowid

2748 グループ $grp に所属する

2749 `gecos $user` さんよりメッセージ:

2750

2751 $message

2752 EOF

2753 cat<<EOF

2754 <p>以下のユーザに送信しました。</p>

2755 <pre>

2756 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

2757 </pre>

2758 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2759 EOF

2760 }

2761 joingrpadmit() {

2762 # $1=yes/no $2=session-key

2763 if [ -z "$2" ]; then

2764 echo "bye bye" | html p; return

2765 fi

2766 t_usr=`session=$2 getpar user`

2767 t_grp=`session=$2 getpar group`

2768 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2769 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2770 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2771 echo "無効な加入依頼です。" | html p

2772 echo "有効期限が切れたか、

2773 他の管理者がいる場合は処理済みの可能性があります。" | html p

2774 return

2775 fi

2776 if ! isgrpowner "$user" "$t_grp"; then

2777 echo "グループ管理者のみの機能です。" | html p; return

2778 fi

2779 case $1 in

2780 yes) joingrp "$t_grp" "$t_usr" yes ;;

2781 no) joingrp "$t_grp" "$t_usr" no ;;

2782 *)

2783 echo "無効な指定です($1)。" | html p

2784 return ;;

2785 esac

2786 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

2787 rcpts="`getgroupadminmails $t_grp` $user"

2788 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2789 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2790 に

2791 $t_usr

2792 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2793 しました。"

2794 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2795 query "delete from session where id='$2';"

2796 echo "$body" | html p

2797 }

2798

2799 joingrprequest() {

2800 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2801 jss="joingrp-`date +%s`-`genrandom 12`"

2802 addsession $jss +${memoplimitdays}days

2803 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

2804 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

2805 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2806 $url

2807 $user さんから

2808 グループ $1

2809 に加入依頼がありました。

2810

2811 承認する:

2812 $urlbase?joingrpadmit+yes+$jss

2813

2814 白紙に戻す:

2815 $urlbase?joingrpadmit+no+$jss

2816 EOF

2817 echo "管理者に加入依頼を出しました。

2818 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2819 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2820 }

2821 joingrp() {

2822 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2823 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2824 if isgrpowner "$user" "$1"; then

2825 isowner="yes"

2826 elif [ -n "$5" ]; then

2827 isowner="yes"

2828 else

2829 isowner=""

2830 fi

2831 ## err jg:isgrpowner: isowner="$isowner"

2832 if [ -n "$isowner" ]; then

2833 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2834 elif [ x"$2" != x"$user" ]; then # if user is not login user

2835 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2836 return

2837 elif [ x"$3" = x"no" ]; then

2838 : # Do not pursue those who leave

2839 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2840 : # Member can change own email address for the joining moderated group

2841 else # adding user is $user itself

2842 case `getgroupattr $1 regmode` in

2843 moderated)

2844 joingrprequest "$@" # Request only

2845 return

2846 ;;

2847 *)

2848 ;;

2849 esac

2850 fi

2851 qgname=`sqlquote "$1"`

2852 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

2853 cond="where gname=$qgname and user='$2'"

2854 if [ x"$3" = x"yes" ]; then

2855 query "replace into grp_mem values($qgname, '$2');"

2856 # Notify joingrp to admin

2857 action="に加入しました。"

2858 if [ -n "$4" ]; then

2859 if msg=`emaildomaincheck "$4"`; then

2860 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2861 'string', '$4', NULL);"

2862 else

2863 echo $msg

2864 fi

2865 else

2866 query "delete from grp_mem_s $cond and key='email';"

2867 fi

2868 if [ -n "$5" ]; then # as ADMIN

2869 # Coming here means newly created group

2870 sql="select case\

2871 when (select count(*) from grp_mem where gname=$qgname)=1\

2872 then (select user from grp_mem\

2873 where gname=$qgname and user='$user')\

2874 else '' end; "

2875 err NewGrpChk: $sql

2876 if [ -n "`query \"$sql\"`" ]; then

2877 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

2878 query "replace into grp_adm values($qgname, '$user');"

2879 fi

2880 fi

2881 else

2882 query "delete from grp_mem $cond;

2883 delete from grp_mem_s $cond;

2884 delete from grp_mem_m $cond;"

2885 action="から脱退しました。"

2886 fi

2887 smail "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

2888 $url?grp+$grid

2889 $user (`gecos $user`)さんが

2890 グループ $1

2891 $action

2892 EOF

2893 }

2894 grp_add_team() (

2895 # $1=grp-rowid $2=team $3...=user-rowid(s)

2896 grp=`getgroupbyid $1`

2897 team=$2; shift; shift

2898 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2899 { echo "BEGIN;"

2900 for user; do

2901 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2902 '$grp',\

2903 (SELECT name FROM user WHERE rowid=$user),\

2904 'team', 'string', '$team');"

2905 done

2906 echo "END;"

2907 } | query

2908 )

2909 grp_rm_team() (

2910 # $1=grp-rowid $2=team $3...=user-rowid(s)

2911 grid=$1

2912 qgrp=$(sqlquote "`getgroupbyid $grid`")

2913 team=$2; shift; shift

2914 [ -z "$grid" -o -z "$team" ] && return

2915 { echo "BEGIN;"

2916 for user; do

2917 echo "DELETE FROM grp_mem_m\

2918 WHERE gname=$qgrp \

2919 AND user=(SELECT name FROM user WHERE rowid=$user)\

2920 AND key='team' AND val='$team';"

2921 done

2922 cat<<-EOF

2923 DELETE FROM blog_s

2924 WHERE rowid=(

2925 SELECT rowid

2926 FROM blog_s a

2927 WHERE key='team'

2928 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2929 AND NOT EXISTS (SELECT * FROM grp_mem_m

2930 WHERE key='team' AND val=a.val -- a.val=team

2931 AND gname = (SELECT val FROM blog_s b

2932 WHERE a.id=b.id AND key='owner')

2933 ));

2934 EOF

2935

2936 echo "END;"

2937 } | query

2938 )

2939 grp_reg_adm() {

2940 # $1=grp-rowid $2...=user-rowid

2941 grid=$1

2942 grp=`getgroupbyid "$1"`

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

2944 echo "無効なグループIDです" | html p; return

2945 fi

2946 if ! isgrpowner "$user" "$grp"; then

2947 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2948 fi

2949 shift

2950 for urid; do

2951 newadm=`query "select name from user where rowid=$urid;"`

2952 if [ -z "$newadm" ]; then

2953 echo "指定ユーザIDがおかしいようです。" | html p; return

2954 fi

2955 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

2956 err ismember $newadm $grp

2957 if ismember $newadm "$grp"; then

2958 # OK, go ahead

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

2960 query "replace into grp_adm values($getgname, '$newadm');"

2961 # confirm insertion

2962 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2963 if [ -n "`query \"$sql;\"`" ]; then

2964 echo "追加完了: $newadm" | html p

2965 else

2966 echo "追加失敗($1 $urid)" | html p

2967 fi

2968 fi

2969 showgroup $grid

2970 done

2971 }

2972 dt_rowhack() {

2973 # From: <TR>

2974 # ....

2975 # <TD>rowclass=foo</TD>

2976 # </TR>

2977 # To: <TR class="foo">....<TD>foo</TD></TR>

2978 sed -e '

2979 /^<TR>/ {

2980 :loop

2981 s/\n//

2982 N

2983 /<\/TR>/ {

2984 s/\n//

2985 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

2986 n

2987 }

2988 $q

2989 b loop

2990 }'

2991 }

2992 dumptable() {

2993 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2994 # textのフィールドだけ全てダンプにしたほうがいいか

2995 # $DT_VIEW sets link

2996 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2997 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2998 VIEW=${DT_VIEW-replyblog}

2999 if [ -n "$VIEW" ]; then

3000 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3001 fi

3002 # $DT_CHLD=ChildTable:BindColumn

3003 if [ -n "$DT_CHLD" ]; then

3004 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3005 cntall="(select count($_i) from $_t where $_i=a.id)"

3006 # XXX: Dirty workaround for slow subquery of acclog

3007 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3008 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

3009 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3010 and id in (select id from $_t where $_i=a.id) \

3011 and val > coalesce((select time from myacclog where \

3012 tblrowid=a.rowid),\

3013 '1970-01-01'))"

3014 cnt="$cntnew as '新着', $cntall as '総数',"

3015 dt_class=" td2r td3r dumpblogs"

3016 fi

3017 # Construct join expression

3018 eav="" scols=""

3019 pk=`gettblpkey $2`

3020 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3021 substr=${substr:-%s}

3022 for col in ${3:-`gettbl_s_cols $2`}; do

3023 valvar=val

3024 case $col in

3025 gecos) scols="$scols${scols:+, }${col#}"

3026 continue ;; # built-in column name

3027 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3028 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3029 case "$as" in

3030 *:*=*) cnd=${as#*:}

3031 h=${cnd%%=*} v=${cnd#*=}

3032 h=`sqlquotestr "$h"`

3033 v=`sqlquotestr "$v"`

3034 valvar="CASE val WHEN $h THEN $v END"

3035 as=${as%%:*} ;;

3036 esac

3037 ;;

3038 *) as=${col} ;;

3039 esac

3040 ss=`printf "$substr" "$valvar"`

3041 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3042 scols="$scols${scols:+, }b.$as"

3043 done

3044 #case author when '$user' then a.rowid else '---' end as ID,

3045 sql=${DT_SQL:-"select \

3046 a.rowid as LINK,\

3047 $cnt\

3048 $scols from $2 a left join\

3049 (select $pk,$eav,

3050 max(case key when 'owner'

3051 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

3052 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

3053 ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"

3054 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3055 <div>

3056 <div class="dumptable">

3057 <table class="b$dt_class">

3058 `sq -header -cmd ".mode $1" $db "$presql$sql"`

3059 </table>

3060 </div>

3061 </div>

3062 EOF

3063 }

3064

3065 par2table() (

3066 # copy current parameters of par into destination table

3067 # $1=definition-file

3068 # Using $user and $session

3069 # Return value:

3070 # 0: Stored successfully

3071 # 1: Insufficient fillings

3072 # 2: No permission to modify the record

3073 # 3: Invalid rowid

3074 # 4: SUCCESS to delete

3075 # 5: Stop deletion for lack of confirm check

3076 # 6: Password length too short

3077 # 7: Password mismatch

3078 # 8: Old password incorrect

3079 rowid=`getpar rowid`

3080 if [ ! -e $1 ]; then

3081 echo "テーブル定義ファイルが見付かりません" | html p

3082 exit 1

3083 fi

3084 tbl=${1%.def}

3085 tbl=${tbl##*/}

3086 if [ -n "$rowid" ]; then # Modify existing entry

3087 if [ x"$tbl" = x"user" ]; then

3088 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3089 elif [ x"$tbl" = x"grp" ]; then

3090 sql="select gname from $tbl where rowid=$rowid;"

3091 ##err p2t:grp:q $sql

3092 isgrpowner "$user" "`query $sql`" && rowowner=$user

3093 else

3094 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3095 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3096 fi

3097 ### err rowowner=$rowowner

3098 if [ x"$user" != x"$rowowner" ]; then

3099 echo "他人のレコードはいじれないの" | html p

3100 return 2

3101 elif [ -z "$rowowner" ]; then

3102 echo "指定したレコードはないみたい" | html p

3103 return 3

3104 fi

3105 rm=`getpar rm` cfm=`getpar confirm`

3106 # Editing existent entry

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

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

3109 query "delete from $tbl where rowid=$rowid;"

3110 return 4

3111 else

3112 echo "消去確認のチェックがないので消さなかったの..." | html p

3113 return 5

3114 fi

3115 fi

3116 fi

3117

3118 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

3120 # Update of existing record

3121 for col in `gettblcols $tbl`; do

3122 val=`getparquote $col`

3123 [ -z "$val" ] && continue

3124 ## err query "update $tbl set $col=$val where rowid=$rowid"

3125 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3126 sql="update $tbl set $col=$val where rowid=$rowid;"

3127 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3128 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3129 ## User name cannot be changed with interface provided with this

3130 ## script. But we offer the trigger to change owner user

3131 ## of blog_s table.

3132 #err "select quote($col) from $tbl where rowid=$rowid;"

3133 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3134 cat<<-EOF | query

3135 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3136 -- should use EXCLUSIVE transaction outside of this.

3137 SAVEPOINT par2table;

3138 $sql

3139 update blog_s set val=$val

3140 where key='owner' and val=$old;

3141 RELEASE SAVEPOINT par2table;

3142 EOF

3143 ## XX: DIRTY Hack Ends here

3144 ## We should keep blog's owner as a single column which has

3145 ## foreign key constraint with primary key of grp/user.

3146 else

3147 query "$sql"

3148 fi

3149 done

3150 # Then, set up $pval for further insertion of tbl_s and tbl_m

3151 for col in `gettblpkey $tbl`; do

3152 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3153 pval="$pval${pval:+, }\"$val\""

3154 done

3155 else

3156 # New entry

3157 # Generate values() for primary keys

3158 for col in `gettblpkey $tbl`; do

3159 # Genuine primary keys for _m and _s

3160 val=`getvalquote $tbl $col`

3161 [ -z "$val" ] && continue

3162 pval="$pval${pval:+, }$val"

3163 done

3164 ##err pval=$pval

3165 for col in `gettblfkey $tbl`; do

3166 # args for values() to insertion into master table

3167 val=`getvalquote $tbl $col`

3168 [ -z "$val" ] && continue

3169 formaster=$formaster"${formaster:+, }$val"

3170 done

3171 formaster="$pval${formaster:+, }$formaster"

3172 ## err formaster=$formaster

3173 if [ -z "$formaster" ]; then

3174 echo "項目を全て埋めてください" | html pre

3175 return 1

3176 fi

3177 ## err "replace into $tbl values($formaster);"

3178 query "replace into $tbl values($formaster);"

3179 ## Insertion to master table, done

3180 fi

3181

3182 for kt in s m; do

3183 tb2=${tbl}_$kt

3184 for col in `gettbl_${kt}_cols $tbl`; do

3185 ptype=`getpartype $col "limit 1"`

3186

3187 # First, check update of existing entries in _m

3188 if [ $kt = m ]; then

3189 # sessID|address.1.22|string|Somewhere-x.y.z

3190 sql=""

3191 ##err dots from query "select var from par where var like '$col.%';"

3192 for v in `query "select var from par where var like '$col.%';"`; do

3193 # v=address.1.22

3194 st_rowid=${v##*.}

3195 origcol=${v%%.*} # original column derived from

3196 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3197 ##case `getpartype $v` in

3198 ## err CASE `gettbl_coltype $tbl/$origcol` in

3199 ## err edit flag = `getpar action.$v`

3200 case `getpar action.$v` in

3201 rm)

3202 if [ x`getpar confirm.$v` = x"yes" ]; then

3203 newsql="delete from $tb2"

3204 else

3205 echo "削除確認未チェック" | html p

3206 fi ;;

3207 edit)

3208 case `gettbl_coltype $tbl/$origcol` in

3209 image|document|binary)

3210 file=$tmpd/`getparfilename $v`

3211 ## err type=file=$file

3212 [ -z "$file" ] && continue

3213 bn=`sqlquotestr "${file##*/}"`

3214 bin="X'"$(hexize "$file")"'"

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

3216 type=\"file:$ct\"

3217 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3218 cachedir=`getcachedir "$tbl/$rowid"`

3219 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3220 rm -rf $cachedir

3221 ;;

3222 *)

3223 newsql="update $tb2 set val=(select val from par where var \

3224 like '$col.%.$st_rowid')"

3225 ;;

3226 esac

3227 ;;

3228 *) # maybe "keep", do not modify value

3229 continue

3230 ;;

3231 esac

3232 # err newsql=$newsql

3233 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3234 done

3235

3236 if [ x"$bin" = x"NULL" ]; then

3237 ## err repl:normal sql=`echo $sql`

3238 query "$sql

3239 delete from $tb2 where type='string' and val='';"

3240 ## err repl:normal done

3241 else

3242 sqlfile="$tmpd/sqlf.$$"

3243 echo "$sql" > $sqlfile

3244 ## err repl:sqlfile=`ls -lF $sqlfile`

3245 query ".read $sqlfile"

3246 ## err repl:done

3247 fi

3248 # Rest of kt==m: set multiple mode

3249 nr=`getparcount $col`

3250 else

3251 nr=1 # for kt==s, number of records is 1

3252 fi

3253

3254 i=0

3255 while [ $i -lt $nr ]; do

3256 limit="limit 1 offset $i"

3257 i=$((i+1)) # increase beforehand against continue

3258 val=`getvalquote $tbl $col "$limit"`

3259 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3260 ## err $col=$val

3261 bin=NULL

3262 ## err partype$col=`getpartype $col "$limit"`

3263 case $ptype in

3264 file) file=$tmpd/`getparfilename $col "$limit"`

3265 ## err parfile-$col=$file

3266 [ -z "$file" ] && continue

3267 bin="X'"$(hexize "$file")"'"

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

3269 type=\"file:$ct\" ;;

3270 "*"*) continue ;; # foreign table

3271 *) type=\"string\" ;;

3272 esac

3273 case `gettbl_coltype $tbl/$col` in

3274 password) # special care for password

3275 # name={password,pswd1,pswd2}

3276 p1=`getpar pswd1 "$limit"`

3277 if [ -z "$p1" ]; then

3278 continue # SKIP password setting, if p1 is empty

3279 else

3280 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3281 ## err pswd=$pswd

3282 if pwcheck "$pswd"; then

3283 if [ x"$p1" = x"$p2" ]; then

3284 case "$p1" in

3285 ??????????*) ;;

3286 *) echo "パスワードは10字以上にしてください。" | html p

3287 return 6;;

3288 esac

3289 val="\"`echo $p1|mypwhash`\""

3290 else

3291 echo "2つの新パスワード不一致" | html p

3292 return 7

3293 fi

3294 else

3295 echo "旧パスワード違います" | html p

3296 return 8

3297 fi

3298 fi

3299 ;;

3300 esac

3301 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3302 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3303 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3304 if [ x"$bin" = x"NULL" ]; then

3305 ## err Normal-query: `echo $sql`

3306 query "$sql"

3307 else

3308 sqlfile="$tmpd/query.$$"

3309 echo "$sql" > $sqlfile

3310 ## err sqlfile=`ls -lF $sqlfile`

3311 query ".read $sqlfile"

3312 fi

3313 ## err p2t done

3314 done

3315 done

3316 done

3317 return 0

3318 ##err donee

3319 )

3320 genform() {

3321 # $1 = form definition file

3322 # $2, $3 (optional)= table name and ROWID

3323 # If $GF_VIEWONLY set and nonNull, output values without form

3324 # If $GF_ARGS set, use it as content-strings in the form

3325 # If $GF_OWNER set, use it as value of name="owner"

3326 # If $GF_STAGE set, use it as value of name="stage"

3327 forms="" hiddens="" rowid=$3

3328 if [ ! -e "$1" ]; then

3329 echo "そのようなデータベースはないようです($2)。" | html p

3330 return

3331 elif [ -n "$2" ]; then

3332 rec=`query "select * from $2 where rowid='$rowid';"`

3333 if [ -z "$rec" ]; then

3334 pk=`gettblpkey $2`

3335 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3336 rec=`query "select rowid from $2 where $pk='$rowid';"`

3337 rowid=$rec

3338 rec=$3

3339 fi

3340 if [ -z "$rec" ]; then

3341 echo "そんなレコードはないみたいね..." | html p

3342 return

3343 fi

3344 fi

3345 if [ -z "$GF_VIEWONLY" ]; then

3346 rm='<input id="rm" name="rm" type="checkbox"

3347 value="yes"><label for="rm">このエントリの削除</label>

3348 <span>ほんとうに消しますよ(確認)!

3349 <input name="confirm" type=checkbox value="yes">はい</span>'

3350 fi

3351 # Image Cache dir

3352 ## err genform: getcache=$2/$rowid

3353 td=`getcachedir "$2/$rowid"`

3354 while IFS=: read prompt name keytype type args; do

3355 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3356 sp="${args:+ }"

3357 form="" val=""

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

3359 # err genform2a: Seeking for "$2.$name, type=$type"

3360 rawval=`getvalbyid $2 $name $rowid $td`

3361 val=`echo "$rawval"|htmlescape`

3362 ## err genform3a: getvalbyid $2 $name $rowid $td

3363 ## err genform3b: val="[$val]" type="$type"

3364 fi

3365 if [ -n "$GF_VIEWONLY" ]; then

3366 is_hidden "$2" "$name" && continue

3367 fi

3368 case "$type" in

3369 text*)

3370 cgiform=cgi_multi_$type

3371 if [ -s $td/$name.count -a -n "$val" ]; then

3372 form=`$cgiform $name $td`

3373 val=$(echo "$val"|

3374 while read fn; do

3375 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3376 </td></tr>$nl"

3377 done)

3378 val="<table>$nl$val$nl</table>"

3379 else

3380 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3381 form=`cgi_$type $name "$rawval" "$args"`

3382 fi

3383 ;;

3384 [Rr][Aa][Dd][Ii][Oo])

3385 fh="<label><input type=\"radio\" name=\"$name\""

3386 form="`echo $args|sed -e \

3387 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3388 ;;

3389 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3390 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3391 ;;

3392 [Ss][Ee][Ll][Ee][Cc][Tt])

3393 fh="<select name=\"$name\">$nl"

3394 form=$(for l in $args; do

3395 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3396 done)

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

3398 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3399 fi

3400 form="$fh$form</select>"

3401 ;;

3402 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3403 if [ -s $td/$name.count ]; then

3404 form=`cgi_multi_file $name $td "$args"`

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

3406 hrfb="$myname?showattc+$2_m"

3407 val=$(echo "$rawval" \

3408 | while read fn; do

3409 data=`percenthex "$td/$fn"`

3410 #ct=`cat $td/$fn.content-type`

3411 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3412 ri=`cat "$td/$fn.rowid"`

3413 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3414 #imgsrc="<img src=\"data:$ct,$data\">"

3415 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3416 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3417 done)

3418 fi

3419 else

3420 form="<input type=\"file\" name=\"$name\" $args>"

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

3422 imgs=$(echo "$rawval"\

3423 |while read fn;do

3424 data=`percenthex "$td/$fn"`

3425 echo "<img src=\"data:image/png,$data\">$fn<br>"

3426 done)

3427 form=$form"<br>$imgs"

3428 val=$imgs # 2015-06-15

3429 else

3430 form="<input type=\"file\" name=\"$name\" $args>"

3431 fi

3432 fi

3433 ;;

3434 [Hh][Ii][Dd][Dd][Ee][Nn])

3435 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3436 args="value=\"$GF_STAGE\""

3437 fi

3438 form="<input type=\"hidden\" name=\"$name\" $args>"

3439 prompt='' # Remove prompt

3440 ;;

3441 [Aa][Uu][Tt][Hh][Oo][Rr])

3442 [ -n "$GF_VIEWONLY" ] && continue

3443 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3444 prompt="" ;;

3445 [Oo][Ww][Nn][Ee][Rr])

3446 [ -n "$GF_VIEWONLY" ] && continue

3447 val=${GF_OWNER:-$val}

3448 val=${val:-$user}

3449 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3450 prompt="" ;;

3451 [Uu][Ss][Ee][Rr])

3452 # XXX: is null $user ok?

3453 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3454 [ -n "$GF_VIEWONLY" ] && continue

3455 form="$user"

3456 ;;

3457 [Pp]assword)

3458 [ -n "$GF_VIEWONLY" ] && continue

3459 form="`cgi_passwd`"

3460 val=""

3461 ;;

3462 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3463 [ -n "$GF_VIEWONLY" ] && continue

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

3465 val=`genserial`

3466 fi

3467 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3468 prompt="" ;;

3469 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3470 prompt=""

3471 ;;

3472 parent|path|blog*)

3473 prompt=""

3474 ;;

3475 "*"*)

3476 tail=$tail"``"

3477 continue ;;

3478 esac

3479 if [ -n "$prompt" ]; then

3480 if [ -n "${GF_VIEWONLY}" ]; then

3481 form=$val

3482 else

3483 :

3484 fi

3485 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3486 else

3487 hiddens=$hiddens$nl"$form"

3488 fi

3489 done < $1

3490 # enctype="multipart/form-data"

3491 cat<<EOF

3492 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3493 ${rowid:+$rm}

3494 <table class="b $2">

3495 $forms

3496 </table>$hiddens

3497 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3498 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3499 EOF

3500 if [ -z $GF_VIEWONLY ]; then

3501 cat<<EOF

3502 <input type="submit" name="sub" value="OK">

3503 <input type="reset" name="res" value="Reset">

3504 EOF

3505 fi

3506 cat<<EOF

3507 $GF_ARGS</form>

3508 $tail

3509 EOF

3510 }

3511 edittable() {

3512 # $1=form-def $2=table $3 rowid

3513 genform "$@"

3514 }

3515 viewtable() {

3516 GF_VIEWONLY=1 genform "$@"

3517 }

3518 showattc() {

3519 # $1=table_m $2=rowid &optional $3=RawFlag

3520 ## err \$1=$1 \$2=$2 \$3=$3

3521 if ! isfilereadable $user $1 $2; then

3522 contenttype; echo

3523 echo "このファイルは管理者のみしか見られません" | html p

3524 putfooter; exit

3525 fi

3526 idir=`umask 002; mktempd` || exit 1

3527 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3528 bin=$idir/$myname-$$.bin

3529 sql="select quote(bin) from $1 where rowid='$2';"

3530 ## err showattc: sql: $sql

3531 sq $db "$sql" | unhexize > $bin

3532 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3533 type=${tv%//*} fn=${tv#*//}

3534 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3535 ct=${type#file:}

3536 case $ct in # all text/* changed to text/plain

3537 text/*)

3538 charset=`nkf -g $bin|cut -d' ' -f1`

3539 case $charset in

3540 ASCII*) charset="" ;;

3541 esac

3542 if [ -z "$3" ]; then

3543 ct="text/html${charset:+; charset=$charset}"

3544 link="?showattc+$1+$2+raw"

3545 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3546 | sed 's,^,<span></span>,' \

3547 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3548 -D_LINK_="$link" \

3549 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3550 exit $?

3551 fi

3552 ct="text/plain${charset:+; charset=$charset}"

3553 ;;

3554 esac

3555 contenttype "$ct"

3556 echo "Content-Disposition: filename=\"$fn\""

3557 echo "Content-Length: " `cat $bin | wc -c`; echo

3558 #echo "Content-Type: " ${type#file:}; echo

3559 cat $bin

3560 }

3561 #

3562 # Some default stupid handler on CGI values

3563 #

3564 default_storedb() {

3565 # ARG: $1=table-def-file

3566 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3567 tbl=`basename $1`

3568 tbl=${tbl%.def}

3569 cols="`grep :text $1|cut -d: -f2`"

3570 col=`echo "$cols"|head -1`

3571 vcol=`getpar $col`

3572 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3573 if [ -n "$vcol" ]; then

3574 par2table $1

3575 else

3576 return 2 # No insertion occurred

3577 fi

3578 }

3579

3580 default_view() { # $1=def-file

3581 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3582 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3583 default_storedb "$@"

3584 query "select rowid from $tbl order by rowid desc;" \

3585 | while read rowid; do

3586 viewtable $1 $tbl $rowid

3587 done | _m4 -D_TITLE_="$tbl" \

3588 -D_FORM_="`genform $1`" \

3589 -D_DUMPTABLE_="syscmd(cat)" \

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

3591 }

3592 default_viewtext() { # $1=def-file

3593 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3594 default_storedb "$@"

3595 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3596 | _m4 -D_TITLE_="$tbl" \

3597 -D_FORM_="`genform $1`" \

3598 -D_DUMPTABLE_="syscmd(cat)" \

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

3600 }

3601 default_smail() {

3602 default_storedb "$@"

3603 if [ $? -eq 2 ]; then

3604 _m4 -D_TITLE_="入力" \

3605 -D_FORM_="`genform $1`" \

3606 -D_DUMPTABLE_="" \

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

3608 return

3609 fi

3610 cond=""

3611 for pk in `gettblpkey $tbl`; do

3612 pv=$(sqlquote "$(getpar $pk)")

3613 cond="$cond${cond:+ and }$pk=$pv"

3614 done

3615 sql="select rowid from $tbl where $cond;"

3616 rowid=`query "$sql"`

3617 ## err smail1 - "$sql" "-> rowid=$rowid"

3618

3619 while IFS=: read prompt name keytype type args; do # Read from $1

3620 val=`getpar $name`

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

3622 text="$text

3623 $prompt

3624 $name=$val

3625 ---------------------------------------------------------"

3626 fi

3627 case "$type" in

3628 image|document|file)

3629 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3630 fns=$(echo "$fn"|while read fn; do

3631 err mv $tmpd/$fn.orig $tmpd/$fn

3632 mv $tmpd/$fn.orig $tmpd/$fn

3633 rm $tmpd/$fn.rowid # Remove cache flag

3634 ## err "`ls $tmpd/$fn`"

3635 echo $fn

3636 done)

3637 files="$files $fns"

3638 ;;

3639 esac

3640 done < $1

3641 ## err FILES=$files "`ls -lF $tmpd`"

3642 subj="from ${REMOTE_ADDR}"

3643 (echo "$url"

3644 echo "への書き込みがありました。"

3645 echo "------"

3646 echo "$text"

3647 ) | (cd $tmpd &&

3648 err LS="`ls -lF`" &&

3649 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3650 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3651 echo "以下の内容で送信しました。" | html p

3652 viewtable $1 $tbl \

3653 `query "select rowid from $tbl order by rowid desc limit 1;"`

3654 echo "戻る" | html a "href=\"?\""

3655 }