s4: 12548e1a4ff8 s4-funcs.sh

s4

view s4-funcs.sh @ 445:12548e1a4ff8

Add comment about quiz board exclusion from search

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sat, 01 Jul 2017 20:17:41 +0859
parents	f1744a954888
children	4769510d6169

line source

1 #!/bin/sh

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

3 # $HGid$

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

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

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

9 myargs="$@"

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

11 tmpdir=${TMPDIR:-tmp}

12 dbdir=${DBDIR:-db}

13 tmpfiles=""

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

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

16 templ=${TEMPL:-templ}

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

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

19 imgdir=${IMGDIR:-img}

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

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

22 msgdir=$templ/msg

23 timeout="+2 days"

24 memoplimitdays="7"

25 dumpcollen=22

26 #thumbxy=120x120

27 thumbxy=96x96

28 iconxy_S=80x80

29 iconxy_M=400x400

30 maximagexy=1600x1600

31 ### maximagexy=400x400

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

33 blogreadflagrowid=0

34 querylog=$tmpdir/query.log

36 tconfs=""

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

38 conftbl=_tblconf

39 nl="

40 "

41 iconcachekey="profimgcache_S"

42 case "$HTTP_USER_AGENT" in

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

44 *) touchpanel="" ;;

45 esac

46 . ./s4-cgi.sh

48 : <<EOF

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

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

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

53 レポート提出管理

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

56 [2016]

57 7/12 根本への反省

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

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

61 [以下2015]

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

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

64 Web

65 締切設定

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

68 ○自分の提出物リスト

70 7/19 ○設置

71 ○一斉送信

72 ○getparfilename の tmpd の扱い

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

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

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

76 ○添付ファイル回収

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

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

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

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

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

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

84 ○レポート提出モード

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

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

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

90 7/9 ○管理者の追加

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

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

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

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

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

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

99 ○グループ検索

100

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

102

103

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

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

106

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

108 ○6/1 par2tableを triplex 対応に

109 select "yuuji@gentei.org",var,"text",NULL,val from par where var in (select col from _tblconf where tbl="/user" and keytype in ('p', 's'));

110 →とすると一気に

111

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

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

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

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

116

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

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

119 ## 1. passwd

120 ## 2. grp

121 ## 3. grp_mem

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

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

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

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

126

127 ## ● listの定義:

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

129 ## ● hashの定義:

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

131

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

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

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

135 ## csv2sq3 で .csv.sq3 の Makefile

136

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

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

139 ## type := root | comment

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

141 ## unique(id, filename)

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

143

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

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

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

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

148 ## できたー!

149 ## with recursive allmem as (select * from grp_mem where gname='bar' union all select grp_mem.* from grp_mem,allmem where allmem.name=grp_mem.gname) select * from allmem where type='u';

150 ↓

151 ↓以下に変更

152 with recursive allmem as

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

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

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

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

157

158

159 with recursive allmem as

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

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

162 where allmem.val=grp_m.gname)

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

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

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

166

167

168 ## triggerもできた。

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

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

171

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

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

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

175 ## グループ管理

176 ## 個人情報管理

177 ##

178

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

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

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

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

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

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

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

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

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

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

189

190 ## ログテーブル

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

192

193

194

195 ■表設計

196 * 3つの表に分散管理

197 id格納表 + hash表 + list表

198 * *_s *_m

199

200

201

202 user, user_map, user_col

203

204 ■抽象エントリタイプ

205 * user

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

207 * group

208 権限判定に利用

209 * serial

210 自動idとして機能

211 * password

212 入力 type=passwordで入力

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

214 * session

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

216 * text

217 入力 type=text

218 * textarea

219 入力 textarea

220 * image|document

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

222 * owner

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

224 * gowner

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

226 * timestamp

227 datetime()

228 * parent

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

230 * path

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

232

233 格納タイプ

234 * list

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

236 * hash

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

238

239 オブジェクトタイプ

240 * entry

241 id, title, owner

242 * textpart

243 id, parentID, text

244 * binarypart

245 id, parentID, contenttype, filename, content

246 * content

247 hash(textpart), list(binarypart)

248 * topic

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

250 * reply

251 id, parentID, content

252 * blog

253 list(entry)

254 blog = [topic, list(reply)]

255

256

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

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

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

260 "author" => "taro",

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

262 "parent" => "/",

263 "path" => "/1",

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

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

266 {"serial" => 2,

267 "author" => "hanako",

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

269 "parent" => "/",

270 "path" => "/2",

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

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

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

274

275

276 user:=

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

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

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

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

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

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

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

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

285

286 変換表

287 /user/email=m

288

289 blog:=

290 シリアル:id:p:serial

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

292 所有者:owner:s:owner:

293 時刻:ctime:s:stamp:

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

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

296

297 article:=

298 シリアル:id:p:serial

299 筆者:author:s:owner

300 時刻:ctime:s:stamp:

301 参照元:parent:s:parent:

302 パス:path:s:path:

303 本文:text:s:textarea:

304 画像:image:m:image:

305

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

307

308

309 EOF

310

311 sq() {

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

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

314 }

315 dbsetup() {

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

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

318 sqi=$tmpdir/sqi.$$

319 sqo=$tmpdir/sqo.$$

320 mkfifo $sqi $sqo

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

322 sq $db < $sqi &

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

324 exec 2>> $tmpdir/error.out

325 exec 3>> $tmpdir/debug.out

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

327 rm $sqi

328 }

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

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

331 }

332 cleanup() {

333 trap '' INT HUP EXIT TERM PIPE

334 echo .quit >&5

335 kill $sq3pid

336 kill $sq3pid

337 rm -f $sqo $sqi

338 rm -rf $tmpfiles

339 cleanup2

340 }

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

342 # simple redirection for the sake of speed.

343 query() {

344 echo ".once $sqo" >&5

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

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

347 tee -a $querylog

348 else

349 echo "$@" >> $querylog

350 echo "$@"

351 fi >&5

352 cat $sqo

353 echo '>>>' >> $querylog

354 }

355 _m4() {

356 #_S4NAME_=f,f,f

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

358 }

359 ismember() {

360 # $1=user, $2=group

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

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

363 }

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

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

366 }

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

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

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

370 }

371 isgrpowner() (

372 # $1=user, $2=group

373 gn=`sqlquote "$2"`

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

375 err isgrpowner: $sql

376 test -n "`query $sql`"

377 )

378 getgroupadminmails() {

379 # $1=group

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

381 email4group "$1" "$i" ;

382 done

383 }

384 getgroupadmins() { # $1=group

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

386 qgrp=`sqlquote "$1"`

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

388 }

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

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

391 getvalbyid grp $2 \

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

393 }

394 getgroupbyid() {

395 # $1=id|gname

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

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

398 # err ggbyid: `echo $sql`

399 query $sql

400 }

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

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

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

404

405 # Return true when anonymous mode

406 [ "$anonymousmode" ] && return 0

407 # case `getvalbyid blog mode $2` in

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

409 # *closed*)

410 # owner=`getvalbyid blog owner $2`

411 # if isgrp $owner; then

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

413 # elif isuser $owner; then

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

415 # fi

416 # esac

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

418 sql="with getblog as (\

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

420 select blogid from article where id in\

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

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

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

424 select case\

425 when (select author from article where\

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

427 then 'author'\

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

429 then 'open'\

430 when (select val from getmode) is null \

431 then 'open'

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

433 then (select user from grp_adm where \

434 gname=(select val from getowner) and \

435 user='$1')\

436 when (select author from article where\

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

438 then 'user+author'

439 else '' end;"

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

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

442 result=`query "$sql"`

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

444 return 2

445 }

446 linkhome() {

447 # $1=UserOrGroup

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

449 if isuser $1; then

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

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

452 else

453 echo -n "grp+$1"

454 fi

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

456 }

457 hreflink() {

458 # s4 specific notation:

459 # ^href=URL

460 # ^iframe=URL

461 # OSM umap Wikistyle Notation:

462 # [[URL]] - Simle Link

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

480 }

481 minitbl() {

482 sed -n '

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

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

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

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

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

488 H; # Concat this line to HoldSpace

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

490 $ b cont

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

492 }

493 :cont

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

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

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

497 # . of ^. is workaround for FreeBSD sed

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

499 p; # Print whole "table" element

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

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

502 }

503 x; # Back to the newest line

504 p; # Print rest'

505 }

506 acclog() (

507 # $1=table, $2=rowid

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

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

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

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

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

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

514 fi

515 )

516 gecos() (

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

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

519 )

520 setpar() {

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

522 }

523 replpar() {

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

525 }

526 getpar() {

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

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

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

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

531 fi

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

533 case "$var" in

534 owner)

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

536 echo $user; return

537 elif ismember $user $val; then

538 echo $val; return

539 fi ;;

540 esac

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

542 echo "$val"

543 }

544

545 getpartype() {

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

547 }

548 getparcount() {

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

550 }

551 getparfilename() {

552 # null if type of $1 is not file

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

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

555 }

556 sqlquote() {

557 (v="$1"

558 case "$v" in

559 "") return ;; # null

560 "X'"*) # quoted hex string

561 echo $1 ;;

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

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

564 echo "\"$v\""

565 return ;;

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

567 echo "\"$v\""

568 return ;;

569 *)

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

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

572 else

573 echo "\"$v\""

574 fi ;;

575 esac)

576 }

577 sqlquotestr() (

578 case "$1" in

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

580 echo "'$v'" ;;

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

582 esac

583 )

584 mktempd() {

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

586 }

587 getcachedir() { # $1=maintable

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

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

590 else

591 echo $tmpd/$thumbxy

592 fi

593 }

594 getval() {

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

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

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

598 echo "$user" ;;

599 stamp|datetime)

600 date "+%F %T" ;;

601 serial)

602 (s=`getpar $2`

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

604 *)

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

606 esac

607 }

608

609 getvalquote() {

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

611 (v=`getval "$@"`

612 case "$v" in

613 "") echo NULL ;;

614 *) sqlquote "$v" ;;

615 esac)

616 }

617 getparquote() {

618 sqlquote "`getpar $1`"

619 }

620 getbinbyid() {

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

622

623 }

624 getvalbyid() {

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

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

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

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

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

630

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

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

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

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

635 return

636 fi

637 done

638 rowid=$3

639 pk=`gettblpkey $1`

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

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

642 td=${4:-$tmpd}

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

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

645 for kt in s m; do

646 t=${1}_$kt

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

648 vcount=1 # count(val)

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

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

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

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

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

654 if [ $kt = m ]; then

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

656 # Reset val to store filenames if type is string

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

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

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

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

661 i=0

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

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

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

665 i=$((i+1))

666 fn=$c.$i

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

668 case $type in

669 file:*)

670 #file=$td/$val

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

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

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

674 # FOR SPEED: Skip file generation if imgcache exists

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

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

677 && continue

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

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

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

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

682 select rowid from $cond $slice;

683 .output '$td/$fn'

684 select val from $cond $slice;

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

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

687 .output stdout

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

689 EOF

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

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

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

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

694 ls -lh "$file" |

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

696 case "$type" in

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

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

699 *|*:[Aa]pplication*)

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

701 png:- > "$file"

702 ;;

703 esac

704 ;;

705 *)

706 sq $db<<EOF

707 .output $td/$fn.rowid

708 select rowid from $cond $slice;

709 .output $td/$fn

710 select val from $cond $slice;

711 EOF

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

713 ;;

714 esac

715 done

716 else

717 rm -f $td/$c.count

718 case $type in

719 file:*)

720 echo "$val" \

721 | while read fn; do

722 file=$td/$fn

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

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

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

726 | unhexize > "$file"

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

728 case $type in

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

730 *:[Aa]pplication*)

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

732 png:- > $file ;;

733 esac

734 fi

735 done

736 ;;

737 esac

738 fi

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

740 return

741 fi

742 done

743 done)

744 }

745 getvalbypkey() (

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

747 pk=`gettblpkey $1`

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

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

750 )

751 getvalbycond() {

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

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

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

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

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

757 fi

758 }

759 getpwfield() {

760 # getpwfield user column

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

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

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

764 echo "$val"

765 return 0

766 else

767 return 1

768 fi

769 }

770 encode() {

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

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

773 sha1=sha1

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

775 sha1=sha1sum

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

777 sha1=gsha1sum

778 fi

779 fi

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

781 }

782 enjpeg() {

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

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

785 cjpeg="cjpeg"

786 else

787 cjpeg="convert - jpeg:-"

788 fi

789 fi

790 $cjpeg "$@"

791 }

792 mycrypt() (

793 key=$1 salt=$2

794 err \$2=$2

795 case $2 in

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

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

798 esac

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

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

801 )

802 hexize() {

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

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

805 hexize="xxd -p"

806 else

807 hexize_hd() {

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

809 }

810 hexize="hexize_hd"

811 fi

812 fi

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

814 }

815 unhexize() {

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

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

818 unhex="xxd -p -r"

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

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

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

822 EOF

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

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

825 # world writable...:)

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

827 fi

828 fi

829 cat "$@" | $unhex

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

831 }

832 percenthex() {

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

834 }

835 htmlescape() {

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

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

838 }

839 enascii() {

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

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

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

843 else

844 enascii_now=`date +%FT%T`

845 enascii_sed() {

846 nkf -Z0Z1Z2 \

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

848 }

849 enascii="enascii_sed"

850 fi

851 fi

852 cat "$@" | $enascii

853 }

854 size_h() {

855 i="$1" oi=$1

856 set -- B B KB MB GB TB

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

858 oi=$i

859 i=$((i/1024))

860 shift

861 done

862 echo ${oi}$1

863 }

864 gettblconf() {

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

866 ## tconfs=`sq $db \

867 tconfs=`query \

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

869 fi

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

871 }

872 gettblkeys() {

873 # $1=tbl

874 gettblconf

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

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

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

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

879 case $type in

880 *p*)

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

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

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

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

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

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

887 esac

888 case $type in

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

890 esac

891 done

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

893 }

894 gettblpkey() {

895 # $1=tbl

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

897 }

898 gettblfkey() {

899 (x=`gettblkeys $1`

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

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

902 }

903 gettblcols() {

904 (x=`gettblkeys $1`

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

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

907 }

908 gettbl_s_cols() {

909 (x=`gettblkeys $1`

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

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

912 }

913 gettbl_m_cols() {

914 (x=`gettblkeys $1`

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

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

917 }

918 gettbl_h_cols() {

919 (x=`gettblkeys $1`

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

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

922 }

923 gettbl_coltype() (

924 gettblconf

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

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

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

928 )

929 is_hidden() {

930 # $1=Tbl $2=col

931 gettblconf

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

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

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

935 case $x in

936 *h*) return 0 ;;

937 *) return 1 ;;

938 esac

939 }

940

941 dbsetbyid() {

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

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

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

945 quotedp=$(sqlquotestr "$p")

946 unset primary update

947 gettblconf

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

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

950 #err conf=$conf

951 case ${conf#*=} in

952 p*) primary=1 ;;

953 f*) update=1 ;;

954 u*) ;;

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

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

957 esac

958 #err t=$t

959 type=string fn=""

960 case $conf in

961 */password)

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

963 ;;

964 */image*|*/document*)

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

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

967 ;;

968 esac

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

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

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

972 if [ "$primary" ]; then

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

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

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

976 elif [ "$update" ]; then

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

978 else

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

980 fi

981 )

982 }

983 expire() (

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

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

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

987 )

988 addsession() {

989 # expireをセット

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

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

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

993 # Remove old session parameters

994 now=`expire now`

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

996 }

997 gencookie() (

998 for kv; do

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

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

1001 done

1002 )

1003 contenttype() {

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

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

1006 }

1007 putheader() {

1008

1009 }

1010 putfooter() {

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

1012 }

1013 getcookie() (

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

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

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

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

1018 done

1019 )

1020 genrandom() {

1021 # $1=columns (default: 10)

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

1023 }

1024 genserial() {

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

1026 }

1027 smail() {

1028 # smail rcpts subj (file)

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

1030 # $MAIL_FROM <- From: header value

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

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

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

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

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

1036 }

1037 setviastring() {

1038 table=$1

1039 oifs="$IFS"

1040 IFS="&"

1041 for us in $2; do

1042 k=${us%%=*}

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

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

1045 #echo $k=$v

1046 done

1047 IFS="$oifs"

1048 }

1049 checkdomain() (

1050 # Check the validity of domain by referring DNS

1051 item=$1

1052 err checkdomain $1

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

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

1055 )

1056 pwcheck() {

1057 # $1=passwd

1058 dbpswd=`getpwfield $user pswd`

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

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

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

1062 }

1063 mypwhash() {

1064 mycrypt `cat` `genrandom 5`

1065 }

1066 wasureta() {

1067 user=$1

1068 if ! checkdomain $user; then

1069 contenttype; echo

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

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

1072 putfooter

1073 exit 0

1074 fi

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

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

1077 encpswd=`echo $newpswd|mypwhash`

1078 dbsetbyid user $user pswd "$encpswd"

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

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

1081 $msgdir/mail-newaccount.m4 \

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

1083 | smail $user "New Account"

1084 }

1085 checkauth() {

1086 user=`getpar user`

1087 skc=`getpar skey` # from cookie

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

1089 skey="`getpwfield $user skey`"

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

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

1092 return 0

1093 fi

1094 fi

1095 pswd=`getpar pswd`

1096 quser=`sqlquotestr "$user"`

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

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

1099 return 1

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

1101 wasureta $user

1102 return 1 # wasureta error

1103 fi

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

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

1106 if pwcheck "$pswd"; then

1107 newsession=`genrandom 50`

1108 dbsetbyid user $user skey "$newsession"

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

1110 return 0

1111 fi

1112 return 2 # Password mismatch

1113 }

1114 showlogin() {

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

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

1117 $layout/login.m4.html

1118 exit 0

1119 }

1120 dologin() {

1121 checkauth

1122 st=$?

1123 if [ $st != 0 ]; then

1124 contenttype; echo

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

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

1127 showlogin # and EXIT

1128 fi

1129 }

1130

1131 # Do instant jobs here

1132 dbsetup

1133 trap cleanup INT HUP EXIT TERM PIPE

1134 # trap cleanup INT HUP

1135

1136 err() {

1137 echo "$@" 1>&3

1138 }

1139

1140 cgiinit() {

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

1142 tmpf=tmp/stream

1143 tmpd=`tmpd=$tmpdir mktempd`

1144 tmpfiles=$tmpfiles" $tmpd"

1145 addsession $session

1146 getcookie

1147 case "$REQUEST_METHOD" in

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

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

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

1151 # cat > $tmpf # too much?

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

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

1154 s="`cat tmp/stream`"

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

1156 ;;

1157 esac

1158 case "$CONTENT_TYPE" in

1159 *boundary*)

1160 bndry=${CONTENT_TYPE#*boundary=}

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

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

1163 do

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

1165 #echo u=$us

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

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

1168 # err k=$k v=$v

1169 case "$k" in

1170 *:filename)

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

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

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

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

1175 :

1176 fi

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

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

1179 [Ii]mage/x-xcf)

1180 bzip2 "$tmpd/$v"

1181 v=${v}.bz2

1182 ;;

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

1184 [Ii]mage/*)

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

1186 ;;

1187 esac

1188 ;;

1189 *)

1190 type='string'

1191 ;;

1192 esac

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

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

1195 done

1196 ;;

1197 *)

1198 setviastring par "$s"

1199 ;;

1200 esac

1201 }

1202 email4group() {

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

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

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

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

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

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

1209 and s.key='email'

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

1211 query "$sql"

1212 }

1213 email4groupbyuid() {

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

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

1216 uids=`echo "$@"`

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

1218 sql="WITH

1219 grpemails AS (

1220 SELECT gname, user, val email

1221 FROM grp_mem NATURAL JOIN grp_mem_s

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

1223 useremails AS (

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

1225 FROM user

1226 LEFT JOIN user_s

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

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

1229 FROM useremails u LEFT JOIN grpemails g

1230 ON u.name=g.user

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

1232 ## err email4gByid `echo $sql`

1233 query "$sql"

1234 }

1235 collectmembersbyid() {

1236 # Collect user names of group specified by grid

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

1238 query "SELECT user FROM grp_mem \

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

1240 }

1241 collectmembersbyid() {

1242 # Collect user names of group name

1243 qgrp=`sqlquote "$1"`

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

1245 }

1246 collectgecosesbyid() {

1247 # Collect user gecoses of group

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

1249 query<<-EOF

1250 SELECT gecos

1251 FROM gecoses

1252 WHERE name IN (SELECT user FROM grp_mem

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

1254 EOF

1255 }

1256 collectemail() (

1257 # Collect email addresses for group $1

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

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

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

1261 for e; do

1262 if isuser "$e"; then

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

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

1265 else

1266 qgrp=`sqlquote "$e"`

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

1268 gmem="grp_mem"

1269 else

1270 tm=`sqlquote "$TEAM"`

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

1272 fi

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

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

1275 $gmem g left join grp_mem_s s

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

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

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

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

1280 query "$sql"

1281 fi

1282 done

1283 )

1284 sendinvitation() (

1285 # $1=email

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

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

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

1289 gecos=`gecos`

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

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

1292 _m4 -D_URL_="$urlbase" \

1293 -D_USER_="$name" \

1294 -D_EMAIL_="$1" \

1295 -D_REGIST_="$regist" \

1296 -D_ADMIN_="$admin" \

1297 $msgdir/mail-invite.m4 \

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

1299 return 0

1300 )

1301 emaildomaincheck() {

1302 case "$1" in

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

1304 *@*)

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

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

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

1308 return 2

1309 fi

1310 return 0

1311 ;;

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

1313 esac

1314 }

1315 invite() {

1316 email=`getpar email`

1317 case $email in

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

1319 *@*)

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

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

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

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

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

1325 elif sendinvitation $email; then

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

1327 fi ;;

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

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

1330 esac

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

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

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

1334 fi

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

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

1337 $layout/html.m4.html $layout/invite.m4.html

1338 }

1339 regist() {

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

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

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

1343 echo "bye bye" | html p

1344 reutrn

1345 fi

1346 email=`session=$1 getpar invite`

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

1348 cat<<EOF

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

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

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

1352 EOF

1353 return

1354 fi

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

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

1357 # Fake login password to wasureta

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

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

1360 wasureta $email

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

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

1363 addsession $1 # for removal after 1 minute

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

1365 $layout/login.m4.html

1366 return

1367 }

1368 group_safename() {

1369 # Convert $1 to safe group name

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

1371 }

1372 groupupdate() {

1373 gname=`getpar gname`

1374 qgname=`sqlquote "$gname"`

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

1376 # See ALSO same job in showgroup()

1377 newgname=`group_safename "$gname"`

1378 err newgname=$newgname

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

1380 err NewGNAME: gname=$newgname

1381 gname=$newgname

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

1383 replpar gname string "$gname"

1384 fi

1385 # Name confliction check

1386 parow=`getpar rowid`

1387 ## err parow=$parow

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

1389 query "BEGIN EXCLUSIVE;"

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

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

1392 if [ $count -gt 0 ]; then

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

1394 query "END;"

1395 return

1396 fi

1397 par2table $formdir/grp.def

1398 query "END TRANSACTION;"

1399 # Remove orphan

1400 : <<EOF

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

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

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

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

1405 EOF

1406 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1410 sql="delete from blog where id in

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

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

1413 query "$sql";

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

1415 return # and return

1416 fi

1417 fi

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

1419 fi

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

1421 grid=$(query $sql)

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

1423 grp $grid

1424 }

1425 groupclone() {

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

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

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

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

1430 return

1431 fi

1432 i=0

1433 while true; do

1434 copy="-copy$i"

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

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

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

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

1439 i=$((i++))

1440 continue

1441 fi

1442 break

1443 done

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

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

1446 query<<-EOF

1447 BEGIN;

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

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

1450 SELECT $newqname, key, val

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

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

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

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

1455 -- Copy members and their configuration --

1456 REPLACE INTO grp_mem SELECT $newqname, user

1457 FROM grp_mem WHERE gname=$qgrp;

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

1459 FROM grp_mem_s WHERE gname=$qgrp;

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

1461 FROM grp_mem_m WHERE gname=$qgrp;

1462 -- Copy administrators --

1463 REPLACE INTO grp_adm SELECT $newqname, user

1464 FROM grp_adm WHERE gname=$qgrp;

1465 COMMIT;

1466 EOF

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

1468 STOPCLONEMSG=1 groupconf "$newrowid"

1469 }

1470 groupman() {

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

1472

1473 GF_STAGE="grpconf"

1474 GF_STAGE=groupupdate

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

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

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

1478 -D_DUMPTABLE_="syscmd(cat)" \

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

1480 }

1481 userconf() {

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

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

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

1485 }

1486 groupconf() {

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

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

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

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

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

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

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

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

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

1496 | html p

1497 fi

1498 }

1499 mems() {

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

1501 kwd=`getpar kwd`

1502 listmember $kwd

1503 }

1504 grps() {

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

1506 kwd=`getpar kwd`

1507 listgroup $kwd \

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

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

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

1511 $layout/form+dump.m4.html

1512 }

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

1514 gpg=`getpar grp`

1515 grid=${1:-$gpg}

1516 grp=`getgroupbyid "$grid"`

1517 ## . ./s4-blog.sh

1518 jg=`getpar joingrp`

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

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

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

1522 fi

1523 htmlheader=$layout/html.m4.html

1524 showgroup "$grid"

1525 }

1526 sql4interestblogs() {

1527 cat<<EOF

1528 CREATE TEMPORARY VIEW interestblogs AS

1529 SELECT blog.rowid rid, id, author

1530 FROM blog

1531 NATURAL JOIN

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

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

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

1535 WHEN (SELECT user FROM grp_mem

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

1537 THEN 0

1538 ELSE 1

1539 END;

1540 EOF

1541 }

1542 listnewblogsql() { # $1=user

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

1544 WHERE user='$user'

1545 AND tblrowid=$blogreadflagrowid),

1546 "0");"`

1547 cat<<EOF

1548 `sql4interestblogs`

1549 WITH article_ctime as (

1550 SELECT id,blogid,author,val ctime

1551 FROM article join article_s s using(id)

1552 WHERE s.key='ctime'

1553 ), blog_title_owner as (

1554 SELECT blg.rid brid, id,

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

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

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

1558 ), visited AS (

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

1560 FROM blog_title_owner b, article_ctime ac

1561 JOIN acclog al

1562 ON b.id=ac.blogid

1563 AND al.tbl='blog'

1564 AND al.tblrowid=brid

1565 AND al.user='$user'

1566 AND al.time < ctime

1567 AND '$deftime' < ctime

1568 ), unvisited as (

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

1570 FROM blog_title_owner b, article_ctime ac

1571 ON b.id=ac.blogid

1572 WHERE brid NOT IN (SELECT tblrowid FROM acclog

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

1574 AND ctime > '$deftime'

1575 ), blogall as (

1576 /* ---------------------------------------

1577 Collect new articles with dividing them into visited and unvisited

1578 separately, because constructing joined table of artice X acclog

1579 tends to become HUGE combinations.

1580 --------------------------------------- */

1581 SELECT * FROM visited UNION SELECT * FROM unvisited

1582 ), news as (

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

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

1585 from blogall bl /* left join a_u l

1586 on bl.brid=l.tblrowid */

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

1588 LIMIT 10

1589 ) SELECT brid LINK, "新着",

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

1591 ctime, title,

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

1593 FROM news;

1594 EOF

1595 }

1596

1597 search_form() {

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

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

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

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

1602 @week → 最近一週間

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

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

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

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

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

1608 auth=""

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

1610 case "$1" in

1611 author=*)

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

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

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

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

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

1617 ;;

1618 grp=*)

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

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

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

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

1623 ;;

1624 esac

1625 cat<<-EOF

1626 <div class="right">

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

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

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

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

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

1632 </form>

1633 </div>

1634 EOF

1635 }

1636

1637 imgsrc_cache() (

1638 # $1 = directory for cache'ing

1639 # $2 = table (user_m or grp_m)

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

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

1642 dir="$1" tbl="$2"

1643 keycond="$3"

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

1645 ORDER BY rowid DESC LIMIT 1"

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

1647 tmpf=$tmpd/imgsrc_cache.$$

1648 case "$4" in

1649 [Ss]) size=S ;;

1650 [Oo]) size=O ;;

1651 *) size=M ;;

1652 esac

1653 # ImageCache filename storing schema:

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

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

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

1657 valtype=`query "$sql0"`

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

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

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

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

1662 case "$filetype" in

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

1664 esac

1665 fi

1666 cacheimg_S=$dir/S_$filename

1667 cacheimg_M=$dir/M_$filename

1668 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1676 else

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

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

1679 fifo2=$fifo.2

1680 mkfifo $fifo $fifo2

1681 fmt=${filename##*.}

1682 ## [[ NOTE ]]

1683 ## a. convert oldimage newimage

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

1685 ## b is much smaller than a

1686 cat $tmpf | unhexize \

1687 | tee $fifo \

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

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

1690 | tee $fifo2 \

1691 | convert - "$cacheimg_M" &

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

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

1694 | convert - "$cacheimg_S" &

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

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

1697 echo '">'

1698 echo "$sum" > $sumfile

1699 fi

1700 ## Now preparing cache image, done.

1701 ## Store this information to DB

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

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

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

1705 query <<-EOF

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

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

1708 EOF

1709 )

1710

1711 showhome() {

1712 # $1=userRowIdToShow

1713 err showhome \$1=$1

1714 case "$1" in

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

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

1717 esac

1718 ## err ShowHome: uname=$uname

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

1720 gecos=`gecos "$uname"`

1721 ## err SH:gecos=$gecos

1722 GF_VIEWONLY=1

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

1724 search_form_args=""

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

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

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

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

1729 # Display folders

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

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

1732 and type like 'file:%';"

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

1734 nfile=`query "$sql"`

1735 # err nfile=$nfile

1736 if [ $nfile -gt 0 ]; then

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

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

1739 fi

1740 else

1741 search_form_args="author=$uname"

1742 fi

1743 . ./s4-blog.sh

1744

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

1746 search_form $search_form_args > $sf

1747 echo "$gecos さん" > $tf

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

1749 viewtable $formdir/user.def user $1

1750 echo "</div>"

1751 } > $pf

1752

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

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

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

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

1757 if true; then

1758 tbl=user_m

1759 enticond="name='$uname'"

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

1761 else

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

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

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

1765 echo '">'

1766 }

1767 fi > $imf

1768 fi

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

1770 val='$uname';"`

1771 listblog $uname > $bf

1772

1773 hometail=$tmpd/tail.$$

1774 mkfifo $hometail

1775

1776 #Calling listgroupbytable, originally here

1777

1778 (

1779 # Display Most Recent Entry

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

1781 shortval=${shortval:-val}

1782

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

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

1785 ctime,

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

1787 (SELECT gecos FROM gecoses

1788 WHERE name=(SELECT val FROM blog_s

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

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

1791 FROM blog b

1792 JOIN

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

1794 FROM article a, article_s s

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

1796 GROUP BY blogid ORDER BY val DESC LIMIT 50

1797 ) m

1798 ON b.id=m.blogid;"

1799 # This should be as follows

1800 : <<EOF

1801 WITH arts AS(

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

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

1804 FROM article a NATURAL JOIN article_s s

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

1806 GROUP by s.id

1807 )

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

1809 FROM arts a0

1810 JOIN

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

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

1813 ORDER BY ctime DESC LIMIT 50;

1814 EOF

1815

1816 cat<<-EOF

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

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

1819 <div class="lcto">

1820 `DT_VIEW=replyblog dumptable html blog`

1821 </div>

1822 EOF

1823 unset DT_SQL

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

1825 # Display NEWS

1826 # 2016-06-26

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

1828 acclog blog $blogreadflagrowid

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

1830 fi

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

1832 sql=`listnewblogsql "$user"`

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

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

1835 cont=$((cont-1))

1836 err newcount=$cont

1837 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

1844 `cgi_submit '確定'`

1845 `cgi_hidden read yes`

1846 EOF

1847 echo "$new10 "

1848 echo "</div>"

1849 fi

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

1851 : # DT_SQL=

1852 fi

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

1854 #

1855 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

1861 -D_NBLOG_="$nblog" \

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

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

1864 $layout/html.m4.html $layout/home.m4.html

1865

1866 # Record access log

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

1868 }

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

1870 contenttype; echo

1871 ## err commission: "$@"

1872 gname=`getgroupbyid $1`

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

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

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

1876 grp_reg_adm "$@"

1877 else

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

1879 fi

1880 }

1881 listgroupbytable() {

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

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

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

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

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

1887 NGsql="select distinct tag from\

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

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

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

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

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

1893 for tag in `query "$sql"`

1894 do

1895 ## err ListGrp: tag=$tag

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

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

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

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

1900 as rowid,

1901 gname,

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

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

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

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

1906 ## err PersonalGroupList= `echo $sql`

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

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

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

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

1911 echo '</table>'

1912 done

1913 echo '</div>'

1914 }

1915 iconhref() (

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

1917 data=`percenthex "$1"`

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

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

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

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

1922 )

1923 iconhref2() (

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

1925 src=$1

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

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

1928 )

1929 listentry() (

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

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

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

1933 cond='' hiddens=''

1934 offset=`getpar offset`

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

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

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

1938 limit=30

1939 dir=`getcachedir "$1"`

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

1941 hrb="$myname?home"

1942 deficon=person-default.png

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

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

1945 gcs=gecos

1946 else # if group

1947 hrb="$myname?grp"

1948 deficon=group-default.png

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

1950 gcs=name

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

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

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

1954 ## err tagconv=$tagconv

1955 fi

1956 fi

1957 if [ ! -d $dir ]; then

1958 mkdir -p $dir

1959 fi

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

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

1962 fi

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

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

1965 fi

1966

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

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

1969 ##qgrp=`sqlquote $grp`

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

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

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

1973 quote(a.$nm) as qname,

1974 (SELECT val FROM ${tbl}_s

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

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

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

1978 then printf('(%d名)',

1979 (select count(user) from grp_mem where gname=a.$nm))

1980 else ' <'||a.$nm||'>'

1981 end

1982 as name,

1983 b.tag,

1984 case when a.$nm in (select user from grp_adm

1985 where gname=$getgrp) then '(管理者)'

1986 when '$user' in (select user from grp_adm where gname=a.$nm)

1987 then '(ADMIN)'

1988 when '$user' in (select user from grp_mem where gname=a.$nm)

1989 then '(Member)'

1990 when '$iamowner' = '' then ''

1991 else ',not='||a.rowid end as ownerlink,

1992 CASE '$entity'

1993 WHEN 'グループ'

1994 THEN coalesce(

1995 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

1996 'open')

1997 ||

1998 CASE WHEN '$user'

1999 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2000 THEN ' ismember'

2001 ELSE ''

2002 END

2003 ELSE 'user'

2004 END regmode

2005 from $tbl a left join

2006 (select $nm as name,

2007 max(case key when 'gecos' then val end) as gecos,

2008 max(case key when 'tag' then val end) as tag,

2009 max(case key when 'mtime' then val end) as mtime,

2010 max(case key when 'wtime' then val end) as wtime

2011 from ${tbl}_s group by $nm)

2012 b on a.$nm=b.name $cond $3

2013 order by b.tag desc, b.wtime desc, b.mtime 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 <p>${total}件中の$((offset+1))件めから${kwd:+" - 検索語: $kwd"}</p>

2032 EOF

2033 if [ $((offset+limit)) -lt $total ]; then

2034 nextbtn=$(

2035 cat<<EOF

2036 <div class="right clear"><form action="$myname" method="POST">

2037 `cgi_submit 次の${limit}件`

2038 $hiddens

2039 `cgi_hidden offset $((offset + limit))`</form></div>

2040 EOF

2041 )

2042 fi

2043 if [ $offset -gt 0 ]; then

2044 prevbtn=$(

2045 cat<<EOF

2046 <form action="$myname" method="POST">

2047 `cgi_submit 前の${limit}件`

2048 $hiddens

2049 `cgi_hidden offset $((offset - limit))`</form>

2050 EOF

2051 )

2052 fi

2053 pnbtn="$nextbtn$prevbtn"

2054 echo $pnbtn

2055

2056 ## err ListEntry: `echo "$sql"\;`

2057 # sq $db here??? 2016-11-28

2058 query "$sql limit $limit ${offset:+offset $offset};" \

2059 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2060 err name=$name owner=$ownerp lnk=$lnk

2061 err newlnk=$lnk regmode=$regmode

2062 icondir=$dir/$id

2063 # Pick up only last icon

2064 echo "<div class=\"iconlist xy$thumbxy $type\">

2065 <p class=\"tag _$tag\">$tag</p>" \

2066 | _m4 $tagconv

2067 if [ -n "$NOSPEEDUP" ]; then

2068 files=`getvalbyid $tbl profimg $id $icondir`

2069 if [ -n "$files" ]; then

2070 icon=`echo "$files"|tail -1`

2071 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2072 else

2073 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2074 fi

2075 elif [ -n "$icon" -a -s "$icon" ]; then

2076 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2077 else

2078 cond="$nm=$qname"

2079 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2080 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2081 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2082 # err "img=[$img]"

2083 if [ -n "$img" ]; then

2084 echo "<a href=\"$hrb+$lnk\">"

2085 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2086 echo "</a>"

2087 else

2088 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2089 fi

2090 fi

2091 echo "<br>$name${ownerp:+<br>$ownerp}"

2092 echo "</div>"

2093 done

2094 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2095 )

2096 listmember() {

2097 listentry user "$@"

2098 }

2099 listgroup() {

2100 listentry group "$@"

2101 }

2102 hexteams() { # $1=gname, $2(optional)=user

2103 cond=${2:+" AND user='$2'"}

2104 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2105 WHERE gname='$1' AND key='team'$cond;"

2106 }

2107 showgroup() { # $1=group-rowid

2108 if [ -z "$1" ]; then

2109 grid=`getpar grid`

2110 grid=${grid%%[!0-9]*}

2111 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2112 else

2113 grid=$1

2114 fi

2115 grp=`getgroupbyid $grid`

2116 qgrp=`sqlquote "$grp"`

2117 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2118 if isgroup "$grp"; then

2119 tf=$tmpd/title.$$

2120 sf=$tmpd/search.$$

2121 bodyclass=`query "SELECT val FROM grp_s

2122 WHERE gname=$qgrp AND key='regmode';"`

2123 if ismember "$user" "$grp"; then

2124 ismember="ismember"

2125 qgrp=`sqlquote "$grp"`

2126 bodyclass="$bodyclass${bodyclass:+ }ismember"

2127 else

2128 ismember="" # bodyclass="group"

2129 fi

2130 bodyclass="$bodyclass grouphome"

2131 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2132 echo "グループ $grp" > $tf

2133

2134 showgroupsub $formdir/grp.def "$grid" | \

2135 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2136 -D_FORM_="syscmd(\`cat')" \

2137 -D_BODYCLASS_="$bodyclass" \

2138 -D_DUMPTABLE_="" \

2139 $htmlheader $sf $layout/form+dump.m4.html

2140 # $htmlheader $layout/form+dump.m4.html

2141 # $htmlheader is defined in grp()

2142 else # if $grp is removed at par2table

2143 listgroup

2144 fi

2145 }

2146 showgroupsub() {

2147 # $1=def-file $2=group-rowid

2148 # Using $ismember

2149 rowid=$2

2150 grp=`getgroupbyid $2`

2151 qgrp=`sqlquote "$grp"`

2152 td=`getcachedir grp/"$2"`

2153 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2154 if [ -z "$rowid" ]; then

2155 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2156 #grp=`sq $db "select gname from grp where rowid=$grp"`

2157 echo "showgroupsub: invalid argument($1 $2)" | html p

2158 return

2159 fi

2160 val=`getvalbyid grp profimg $rowid $tmpd`

2161 enticond="gname=$qgrp"

2162 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2163 if [ -n "$img" ]; then

2164 cat<<-EOF

2165 <p class="groupimg">

2166 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2167 EOF

2168 fi

2169 echo "<div class=\"noprofimg\">"

2170 viewtable $1 grp $rowid

2171 echo "</div>"

2172 if isgrpowner "$user" "$grp"; then

2173 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2174 iamowner=$rowid

2175 colmd=" mode"

2176 fi

2177 if [ -n "$ismember" ]; then

2178 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2179 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2180 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2181 cat<<EOF

2182 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2183 <div class="fold clear">

2184 `cgi_checkbox send yes id="send"`<label

2185 for="send">グループ全員にメッセージ送信</label>

2186 <div>

2187 `cgi_textarea message "" "cols=60"`

2188 `cgi_submit 送信`

2189 `cgi_reset リセット`

2190 </div>

2191 `cgi_hidden grp $rowid`

2192 </div></form>

2193 EOF

2194 fi

2195 # 加入ボタン + 加入者リスト

2196 if [ -n "$ismember" ]; then

2197 ismem='checked' state="(参加中)"

2198 else

2199 nomem='checked' state="(現在非加入)"

2200 fi

2201 # このグループでの加入アドレス

2202 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2203 and key='email';"`

2204 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2205 ## and key='email';"

2206 ##err email=$eml

2207 cat <<EOF

2208 <div class="fold clear">

2209 `cgi_checkbox reg yes id="reg"`<label

2210 for="reg">自身の加入状態を操作する</label>$state

2211 <div>

2212 EOF

2213 cgi_form grp <<EOF

2214 <p>このグループに</p>

2215 <table class="b">

2216 <tr><th>メンバーとして</th><td>

2217 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2218 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2219 <tr><th>参加する場合のメイルアドレス<br>

2220 <small>(メインのアドレスとは違うものにする場合に記入<br>

2221 同じでよい場合は空欄に)</small></th>

2222 <td>`cgi_text email $eml`</td></tr>

2223 </table>

2224 `cgi_hidden grp $rowid`

2225 EOF

2226 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2227 echo "moderated (承認加入の)グループなので実際に参加できるのは

2228 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2229 fi

2230 echo '</div></div>'

2231 echo '<h2>話題一覧</h2>'

2232 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2233 cat<<-EOF

2234 <form class="summary" action="$myname" title="$thelp">

2235 `cgi_hidden owner "$grp"`

2236 `cgi_hidden kwd "@week"`

2237 `cgi_hidden stage searchart`

2238 `cgi_submit "一週間のまとめ"`

2239 </form>

2240 EOF

2241 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2242 colstate="state:稼動状態:frozen=rowclass=凍結"

2243 DT_CHLD=article:blogid \

2244 DT_VIEW=replyblog dumptable html blog \

2245 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2246

2247 getgname="(select gname from grp where rowid=$rowid)"

2248 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2249 cm="?commission+$rowid"

2250 thumbxy=50x50 listmember "" "$c" "$rowid" \

2251 |sed -e "s|$<br>$,not=$.*$|\1|" # 間違って押しやすい

2252 # team list

2253 hexteams=`hexteams "$grp"`

2254 if [ -n "$hexteams" ]; then

2255 echo "チーム一覧" | html h2

2256 echo '<div class="dumptable"><table class="b">'

2257 sq $db -html -header<<-EOF

2258 SELECT val TEAM,

2259 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2260 MEMBERS

2261 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2262 EOF

2263 echo '</table></div>'

2264 fi

2265 }

2266 grp_getbodyclass() {

2267 # Get css class name for document.

2268 # `moderated' for moderated groups

2269 # `ismember' for groups where user belongs

2270 # $1=GroupName (w/o quote)

2271 # $user=userNameCurrentlyLogin

2272 ## err grp_getbodyclass: 1="$1"

2273 qgrp=`sqlquote "$1"`

2274 query<<-EOF

2275 SELECT coalesce(

2276 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2277 'open')

2278 ||

2279 CASE WHEN '$user'

2280 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2281 THEN ' ismember'

2282 ELSE ''

2283 END;

2284 EOF

2285 }

2286 grpaction() { # $1=group-rowid

2287 err GRP_ACTION:IN

2288 grid=${1:-`getpar grp`}

2289 grp=`getgroupbyid "$grid"`

2290 if [ -z "$grp" ]; then

2291 echo "無効な指定です。" | html p; return

2292 fi

2293 if ! ismember $user "$grp"; then

2294 echo "加入者のみに許可された操作です。" | html p; return

2295 fi

2296 echo "グループ $grp 個別選択操作" \

2297 | _m4 -D_TITLE_="syscmd(\`cat')" \

2298 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2299 $layout/html.m4.html

2300

2301 isowner=""

2302 isgrpowner "$user" "$grp" && isowner="yes"

2303 usel=`getpar usel`

2304 if [ -n "$usel" ]; then

2305 uids=$(echo `echo $usel`|tr ' ' ',')

2306 ## err grpaction-1: grp=$grp, `echo $sql`

2307 text=`getpar text`

2308

2309 rm=`getpar rm` cfm=`getpar confirm`

2310 ## err rm=$rm cfm=$cfm

2311 if [ x"$rm" = x"yes" ]; then

2312 if [ "$isowner" ]; then

2313 if [ x"$rm$cfm" = x"yesyes" ]; then

2314 # Eliminate

2315 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2316 for tbl in grp_mem grp_mem_s grp_mem_m; do

2317 sql="delete from $tbl $cond;"

2318 # echo "sql=$sql"

2319 query "$sql"

2320 err rmGRPuser "$sql"

2321 done

2322 num=`query "select count(*) from user where rowid in ($uids);"`

2323 #err num=$num

2324 if [ 0$num -gt 0 ]; then

2325 sql="select coalesce(b.val,a.name) from user a left join \

2326 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2327 # err `echo "$sql"`

2328 html pre<<EOF

2329 以下の${num}名のグループ $grp 登録を解除しました。

2330 `query "$sql"`

2331 EOF

2332 fi

2333 else

2334 echo "確認のチェックがないのでやめておきます。" | html p

2335 return

2336 fi

2337 else # not Group Owner

2338 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2339 return

2340 fi

2341 cat<<EOF

2342

2343 EOF

2344 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2345 if [ -z "$text" ]; then # if msg is empty

2346 echo "なにかメッセージを..." | html p

2347 return 0

2348 fi

2349 gecos=`gecos $user`

2350 mkfrom=`getpar mkfrom`

2351 if [ x"$mkfrom" = x"yes" ]; then

2352 safegc=`echo "$gecos" | tr -d '<>@'`

2353 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2354 fromad=`email4groupbyuid "$grp" "$myuid"`

2355 mail_from="$safegc <$fromad>"

2356 else

2357 mail_from="$admin"

2358 fi

2359 MAIL_FROM=$mail_from \

2360 smail "`email4groupbyuid "$grp" $usel` $user" \

2361 "$gecos さんからのメッセージ" <<EOF

2362 $url

2363 のグループ「$grp」のメンバーである $gecos さんから、

2364 あなた宛へのメッセージです。

2365 ----------------------------------------------------------

2366 $text

2367 EOF

2368 if [ $? = 0 ]; then

2369 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2370 sql="select coalesce(b.val, a.name) from

2371 (select name from user where rowid in ($uids)) a

2372 left join user_s b on a.name=b.name and b.key='gecos';"

2373 html pre<<EOF

2374 `query "$sql"`

2375 (送信者である $gecos さんも含まれます)

2376 EOF

2377 err SendDone: `echo $sql`

2378 fi

2379 elif [ x"$rm" = x"commission" ]; then

2380 grp_reg_adm $grid $usel

2381 elif [ x"$rm" = x"addteam" ]; then

2382 team=`getpar team|sed "s/'/''/g"` # for single quotation

2383 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2384 if [ x"$team" != x"$newteam" ]; then

2385 echo "チーム名に使えない文字を除去しました" | html p

2386 team=newteam

2387 fi

2388 if [ -z "$team" -o x"$team" = x"なし" ]; then

2389 cat<<-EOF | html p

2390 有効なチーム名を入力してください。

2391 カンマだけ、「なし」という名前は使えません。

2392 EOF

2393 echo "有効なチーム名を入力してください。" | html p

2394 else

2395 grp_add_team $grid "$team" $usel

2396 fi

2397 elif [ x"$rm" = x"rmteam" ]; then

2398 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2399 rmteam=`getpar rmteam|sed "s/'/''/g"`

2400 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2401 gname='$grp' AND user='$user' AND key='team'\

2402 AND val='$rmteam';\"`" ]; then

2403 grp_rm_team $grid "$rmteam" $usel

2404 else

2405 echo "所属していないチームの除去操作はできません。"|html p

2406 fi

2407 else

2408 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2409 fi

2410 fi

2411 fi

2412 # New entry

2413 sql="WITH mems AS (

2414 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2415 ON gm.user=g.name

2416 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2417 ), posts AS (

2418 SELECT author, count(author) post

2419 FROM article NATURAL JOIN article_s

2420 WHERE blogid IN (SELECT id FROM blog_s

2421 WHERE key='owner'

2422 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2423 AND key='text'

2424 GROUP BY author

2425 ), teams AS (

2426 SELECT user, group_concat(val, ', ') team

2427 FROM grp_mem_m

2428 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2429 AND key='team'

2430 GROUP BY user

2431 ), user_post AS (

2432 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2433 FROM mems m LEFT JOIN posts

2434 ON m.name=posts.author

2435 GROUP by m.rowid

2436 )

2437 SELECT

2438 CASE

2439 WHEN (SELECT user FROM grp_adm

2440 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2441 AND user=up.name) IS NOT NULL

2442 then 'k'

2443 ELSE ''

2444 END || rowid || ',' || gecos NAME,

2445 post POST, team TEAM

2446 FROM user_post up LEFT JOIN teams t

2447 ON up.name=t.user

2448 ORDER BY gecos;"

2449 ## err grpaction: "`echo \"$sql\"`"

2450 tf=$tmpd/title.$$

2451 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2452 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2453 for=\"cmadmin\">グループ管理者委任</label>

2454 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2455 </p></div>"

2456 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2457 for=\"conf\">グループ登録解除</label>

2458 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2459 <p>この操作による通知は本人に行きません。

2460 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2461 </div>"

2462 # Get team list to which current user belongs into $hexteams

2463 myhexteams=$(hexteams "$grp" "$user")

2464 allhexteams=$(hexteams "$grp")

2465 if [ -n "$myhexteams" ]; then

2466 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2467 for=\"cmrmteam\">チーム属性除去</label>

2468 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2469 を除去します: `cgi_checkbox teamconfirm yes` 確認

2470 <p>この操作による通知は本人に行きません。

2471 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2472 </div>

2473 "

2474 fi

2475 b1='<label> <input type="checkbox" name="usel" value="'

2476 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2477 #b2='"> <span>' b3='</span></label>'

2478 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2479 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2480 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2481 cgi_form grpaction<<EOF \

2482 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2483 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2484 | _m4 -D_TITLE_="spaste(\`$tf')" \

2485 -D_SUBTITLE_="チェック後操作ボタン" \

2486 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2487 $layout/form+dump.m4.html

2488 <p>下でチェックした人を対象として:</p>

2489 <div class="foldtabs">

2490 `cgi_radio rm addteam 'id="cmteam"'`<label

2491 for="cmteam">同じチーム属性を付与</label>

2492 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2493 `cgi_datalist_h teams $allhexteams`

2494 </div>

2495 ${rmteammsg}

2496 `cgi_radio rm send id="sendmsg"`<label

2497 for="sendmsg">メッセージ送信</label>

2498 <div>

2499 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2500 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2501 `cgi_textarea text "" cols=40`

2502 </div>

2503 ${isowner:+$cmmsg$excmsg}

2504 `cgi_radio rm close id="x"`<label for="x">×</label>

2505 </div>

2506 <h4>$grp 参加者一覧</h4>

2507 <table class="td2r">

2508 `sq $db -header -html "$sql"`

2509 </table>

2510 `cgi_hidden grp $grid`

2511 EOF

2512 }

2513 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2514 # Create TEMPORARY VIEW

2515 query<<EOF

2516 CREATE TEMPORARY VIEW writeusers AS

2517 SELECT DISTINCT author FROM article

2518 WHERE id in (

2519 select id from article where blogid=(select id from blog where rowid=$1)

2520 );

2521 CREATE TEMPORARY VIEW movablegroups AS

2522 SELECT g.rowid growid , g.gname

2523 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2524 ON grp.gname=gm.gname -- そのユーザが属している

2525 AND user='$user') g -- グループに絞る

2526 WHERE (SELECT author FROM writeusers

2527 EXCEPT

2528 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2529 IS NULL;

2530 $2

2531 EOF

2532 }

2533 sql4readableblogs() {

2534 # Create view of blogs that can be readable to $user

2535 # Blog is readable when:

2536 # 1: blog owner is an user

2537 # 2: else, 2.1: owner-group where the $user belongs

2538 # 2.2: else, owner-group is not moderated

2539 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2540 cat<<EOF ## | tee tmp/sql.out

2541 CREATE TEMPORARY VIEW readableblogs AS

2542 SELECT blog.rowid rid, id, author

2543 FROM blog

2544 NATURAL JOIN

2545 (SELECT id,

2546 max(CASE key WHEN 'owner' THEN val END) owner,

2547 max(CASE key WHEN 'mode' THEN val END) mode

2548 FROM blog_s GROUP by id) bs

2549 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2550 THEN 1 -- blog owner is an user, READABLE

2551 WHEN (SELECT val FROM grp_s

2552 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2553 AND

2554 (SELECT user FROM grp_mem

2555 WHERE gname=bs.owner AND user='$user') IS NULL

2556 THEN 0

2557 WHEN mode = 'quiz'

2558 THEN 0 -- "quiz" mode blog cannot be searched

2559 ELSE 1

2560 END;

2561 EOF

2562 }

2563 editheading() { # $1=rowid-of-heading

2564 rowid=${1%%[!A-Z0-9a-z_]*}

2565 if [ -z "$rowid" ]; then

2566 echo "話題番号が未指定です。" | html p

2567 return

2568 fi

2569 owner=`getvalbyid blog owner $rowid`

2570 title=`getvalbyid blog title $rowid`

2571 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2572 | _m4 -D_TITLE_="修正" \

2573 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2574 -D_BLOGS_="" -D_DUMPTABLE_="" \

2575 -D_FORM_="syscmd(\`cat')" \

2576 $layout/html.m4.html $layout/form+dump.m4.html

2577 # Move to group

2578 if isuser "$owner"; then

2579 crview4article $rowid

2580 n=`query "SELECT count(*) FROM writeusers;"`

2581 ## err N=$n

2582 if [ $((n)) -gt 0 ]; then

2583 ## err ROWID=$rowid

2584 sql="SELECT growid || ':' || gname FROM movablegroups;"

2585 cat<<-EOF

2586 <div class="fold">

2587 `cgi_checkbox mv send id="mv"`<label

2588 for="mv">この話題をグループ所有に移動する</label>

2589 <div>

2590 <form action="?mvart" method="POST" enctype="multipart/form-data">

2591 移動先グループ:

2592 <select name="mv2grp">

2593 EOF

2594 query ".mode html"

2595 query<<-EOF |

2596 $sql

2597 .mode list

2598 EOF

2599 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2600 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2601 cat<<-EOF

2602 </select>

2603 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2604 そのグループに加入しているものに限られます)</p>

2605 <p>`cgi_checkbox cfm yes`<label>確認

2606 (この操作は元に戻すことができません)</label></p>

2607 `cgi_hidden blogrowid $rowid`

2608 `cgi_submit 移動`

2609 `cgi_reset Reset`

2610 </form>

2611 </div>

2612 </div>

2613 EOF

2614 fi

2615 # end of isuser "$owner"

2616 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2617 [ -n "$hexteams" ];}; then

2618 none="`echo なし|hexize`"

2619 cat<<-EOF

2620 <div class="fold">

2621 `cgi_checkbox mv2team send id="mv2team"`<label

2622 for="mv2team">この話題を以下のチームのものにする</label>

2623 <div><p>現在の所属チーム設定:

2624 `query "SELECT

2625 coalesce((SELECT val FROM blog_s

2626 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2627 AND key='team'),

2628 ':なし');"`</p>

2629 <form action="?mvart" method="POST" enctype="multipart/form-data">

2630 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2631 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2632 `cgi_hidden blogrowid $rowid`<br>

2633 `cgi_submit 移動`

2634 `cgi_reset Reset`

2635 </form></div></div>

2636 EOF

2637 fi

2638 }

2639 mvart() { # move diary to some group or team

2640 # or move blog of group to team which belong to the group

2641 blogrowid=`getpar blogrowid`

2642 cfm=`getpar cfm`

2643 ##### echo move blog:$blogrowid to $mv2grp | html p

2644 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2645 . ./s4-blog.sh

2646 if [ -z "$blogrowid" ]; then

2647 echo "無効な指定です(mvart)。" | html p

2648 return

2649 elif [ x"$cfm" != x"yes" ]; then

2650 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2651 elif { mv2grp=`getpar mv2grp`

2652 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2653 [ -n "$mv2grp" ]; }; then

2654 crview4article $blogrowid

2655 ########## TRANSACTION BEGIN

2656 query "BEGIN;"

2657 n=`query "SELECT count(*) FROM writeusers;"`

2658 ## err Nwriteuser=$n

2659 if [ $((n)) -gt 0 ]; then

2660 query<<-EOF

2661 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2662 WHERE key='owner'

2663 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2664 AND $mv2grp IN (SELECT growid FROM movablegroups);

2665 EOF

2666 fi

2667 query "END;"

2668 ########## TRANSACTION END

2669 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2670 [ -n "$mv2team" ];}; then

2671 # blog owner can move it to ANY team

2672 case "$mv2team" in

2673 'なし')

2674 cat<<-EOF

2675 DELETE FROM blog_s

2676 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2677 AND key='team';

2678 EOF

2679 ;;

2680 "") ;;

2681 *)cat<<-EOF

2682 BEGIN;

2683 REPLACE INTO blog_s(id, key, val)

2684 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2685 'team', '$mv2team');

2686 REPLACE INTO blog_s(id, key, val)

2687 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2688 'notify', 'all'); -- Change notify to all

2689 END;

2690 EOF

2691 esac | query

2692 fi

2693 blog_reply $blogrowid

2694 echo yes | html p

2695 }

2696 editart() { # $1=article-rowid $2=blogrowid

2697 rowid=${1%%[!A-Z0-9a-z_]*}

2698 blogrowid=${2%%[!A-Z0-9a-z_]*}

2699 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2700 echo "表示する記事番号が未指定です。" | html p

2701 return

2702 fi

2703 owner=`getvalbyid blog owner $blogrowid`

2704 title=`getvalbyid blog title $blogrowid`

2705 author=`getvalbyid article author $rowid`

2706 ## err EDITart: owner=$owner, author=$author

2707 if isgrpowner "$user" "$owner"; then

2708 : EDIT OK

2709 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2710 echo "本人か所有者しか編集できません." | html p

2711 return

2712 fi

2713 aid=`query "select id from article where rowid=$rowid;"`

2714 tmpout=$tmpd/editart.$$.out

2715 GF_ACTION="?replyblog+$blogrowid#$aid" \

2716 edittable $formdir/article.def article $rowid \

2717 > $tmpout

2718 rm -f /tmp/editart.out

2719 # Cannot use pipelining to m4 with genform() because of stdin stack

2720 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2721 -D_FORM_="syscmd(cat $tmpout)" \

2722 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2723 -D_BLOGS_= -D_DUMPTABLE_= \

2724 $layout/html.m4.html $layout/form+dump.m4.html

2725 }

2726 send2mem() {

2727 rowid=`getpar grp`

2728 rowid=${rowid%%[!0-9]*} # Cleaning

2729 if [ -z "$rowid" ]; then

2730 echo "グループが未指定です。" | html p

2731 return

2732 fi

2733 message=`getpar message`

2734 if [ -z "$message" ]; then

2735 echo "文章を入れてください。" | html p

2736 return

2737 fi

2738 grp=`getgroupbyid $rowid`

2739 members=`collectemail $grp`

2740 # smail rcpt subj (file)

2741 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$admin>" \

2742 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2743 $urlbase?grp+$rowid

2744 グループ $grp に所属する

2745 `gecos $user` さんよりメッセージ:

2746

2747 $message

2748 EOF

2749 cat<<EOF

2750 <p>以下のユーザに送信しました。</p>

2751 <pre>

2752 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

2753 </pre>

2754 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2755 EOF

2756 }

2757 joingrpadmit() {

2758 # $1=yes/no $2=session-key

2759 if [ -z "$2" ]; then

2760 echo "bye bye" | html p; return

2761 fi

2762 t_usr=`session=$2 getpar user`

2763 t_grp=`session=$2 getpar group`

2764 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2765 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2766 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2767 echo "無効な加入依頼です。" | html p

2768 echo "有効期限が切れたか、

2769 他の管理者がいる場合は処理済みの可能性があります。" | html p

2770 return

2771 fi

2772 if ! isgrpowner "$user" "$t_grp"; then

2773 echo "グループ管理者のみの機能です。" | html p; return

2774 fi

2775 case $1 in

2776 yes) joingrp "$t_grp" "$t_usr" yes ;;

2777 no) joingrp "$t_grp" "$t_usr" no ;;

2778 *)

2779 echo "無効な指定です($1)。" | html p

2780 return ;;

2781 esac

2782 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

2783 rcpts="`getgroupadminmails $t_grp` $user"

2784 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2785 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2786 に

2787 $t_usr

2788 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2789 しました。"

2790 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2791 query "delete from session where id='$2';"

2792 echo "$body" | html p

2793 }

2794

2795 joingrprequest() {

2796 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2797 jss="joingrp-`date +%s`-`genrandom 12`"

2798 addsession $jss +${memoplimitdays}days

2799 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

2800 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

2801 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2802 $url

2803 $user さんから

2804 グループ $1

2805 に加入依頼がありました。

2806

2807 承認する:

2808 $urlbase?joingrpadmit+yes+$jss

2809

2810 白紙に戻す:

2811 $urlbase?joingrpadmit+no+$jss

2812 EOF

2813 echo "管理者に加入依頼を出しました。

2814 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2815 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2816 }

2817 joingrp() {

2818 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2819 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2820 if isgrpowner "$user" "$1"; then

2821 isowner="yes"

2822 elif [ -n "$5" ]; then

2823 isowner="yes"

2824 else

2825 isowner=""

2826 fi

2827 ## err jg:isgrpowner: isowner="$isowner"

2828 if [ -n "$isowner" ]; then

2829 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2830 elif [ x"$2" != x"$user" ]; then # if user is not login user

2831 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2832 return

2833 elif [ x"$3" = x"no" ]; then

2834 : # Do not pursue those who leave

2835 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2836 : # Member can change own email address for the joining moderated group

2837 else # adding user is $user itself

2838 case `getgroupattr $1 regmode` in

2839 moderated)

2840 joingrprequest "$@" # Request only

2841 return

2842 ;;

2843 *)

2844 ;;

2845 esac

2846 fi

2847 qgname=`sqlquote "$1"`

2848 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

2849 cond="where gname=$qgname and user='$2'"

2850 if [ x"$3" = x"yes" ]; then

2851 query "replace into grp_mem values($qgname, '$2');"

2852 # Notify joingrp to admin

2853 action="に加入しました。"

2854 if [ -n "$4" ]; then

2855 if msg=`emaildomaincheck "$4"`; then

2856 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2857 'string', '$4', NULL);"

2858 else

2859 echo $msg

2860 fi

2861 else

2862 query "delete from grp_mem_s $cond and key='email';"

2863 fi

2864 if [ -n "$5" ]; then # as ADMIN

2865 # Coming here means newly created group

2866 sql="select case\

2867 when (select count(*) from grp_mem where gname=$qgname)=1\

2868 then (select user from grp_mem\

2869 where gname=$qgname and user='$user')\

2870 else '' end; "

2871 err NewGrpChk: $sql

2872 if [ -n "`query \"$sql\"`" ]; then

2873 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

2874 query "replace into grp_adm values($qgname, '$user');"

2875 fi

2876 fi

2877 else

2878 query "delete from grp_mem $cond;

2879 delete from grp_mem_s $cond;

2880 delete from grp_mem_m $cond;"

2881 action="から脱退しました。"

2882 fi

2883 smail "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

2884 $url?grp+$grid

2885 $user (`gecos $user`)さんが

2886 グループ $1

2887 $action

2888 EOF

2889 }

2890 grp_add_team() (

2891 # $1=grp-rowid $2=team $3...=user-rowid(s)

2892 grp=`getgroupbyid $1`

2893 team=$2; shift; shift

2894 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2895 { echo "BEGIN;"

2896 for user; do

2897 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2898 '$grp',\

2899 (SELECT name FROM user WHERE rowid=$user),\

2900 'team', 'string', '$team');"

2901 done

2902 echo "END;"

2903 } | query

2904 )

2905 grp_rm_team() (

2906 # $1=grp-rowid $2=team $3...=user-rowid(s)

2907 grid=$1

2908 qgrp=$(sqlquote "`getgroupbyid $grid`")

2909 team=$2; shift; shift

2910 [ -z "$grid" -o -z "$team" ] && return

2911 { echo "BEGIN;"

2912 for user; do

2913 echo "DELETE FROM grp_mem_m\

2914 WHERE gname=$qgrp \

2915 AND user=(SELECT name FROM user WHERE rowid=$user)\

2916 AND key='team' AND val='$team';"

2917 done

2918 cat<<-EOF

2919 DELETE FROM blog_s

2920 WHERE rowid=(

2921 SELECT rowid

2922 FROM blog_s a

2923 WHERE key='team'

2924 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2925 AND NOT EXISTS (SELECT * FROM grp_mem_m

2926 WHERE key='team' AND val=a.val -- a.val=team

2927 AND gname = (SELECT val FROM blog_s b

2928 WHERE a.id=b.id AND key='owner')

2929 ));

2930 EOF

2931

2932 echo "END;"

2933 } | query

2934 )

2935 grp_reg_adm() {

2936 # $1=grp-rowid $2...=user-rowid

2937 grid=$1

2938 grp=`getgroupbyid "$1"`

2939 if [ -z "$grp" ]; then

2940 echo "無効なグループIDです" | html p; return

2941 fi

2942 if ! isgrpowner "$user" "$grp"; then

2943 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2944 fi

2945 shift

2946 for urid; do

2947 newadm=`query "select name from user where rowid=$urid;"`

2948 if [ -z "$newadm" ]; then

2949 echo "指定ユーザIDがおかしいようです。" | html p; return

2950 fi

2951 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

2952 err ismember $newadm $grp

2953 if ismember $newadm "$grp"; then

2954 # OK, go ahead

2955 getgname="(select gname from grp where rowid=$grid)"

2956 query "replace into grp_adm values($getgname, '$newadm');"

2957 # confirm insertion

2958 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2959 if [ -n "`query \"$sql;\"`" ]; then

2960 echo "追加完了: $newadm" | html p

2961 else

2962 echo "追加失敗($1 $urid)" | html p

2963 fi

2964 fi

2965 showgroup $grid

2966 done

2967 }

2968 dt_rowhack() {

2969 # From: <TR>

2970 # ....

2971 # <TD>rowclass=foo</TD>

2972 # </TR>

2973 # To: <TR class="foo">....<TD>foo</TD></TR>

2974 sed -e '

2975 /^<TR>/ {

2976 :loop

2977 s/\n//

2978 N

2979 /<\/TR>/ {

2980 s/\n//

2981 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

2982 n

2983 }

2984 $q

2985 b loop

2986 }'

2987 }

2988 dumptable() {

2989 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2990 # textのフィールドだけ全てダンプにしたほうがいいか

2991 # $DT_VIEW sets link

2992 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2993 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2994 VIEW=${DT_VIEW-replyblog}

2995 if [ -n "$VIEW" ]; then

2996 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

2997 fi

2998 # $DT_CHLD=ChildTable:BindColumn

2999 if [ -n "$DT_CHLD" ]; then

3000 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3001 cntall="(select count($_i) from $_t where $_i=a.id)"

3002 # XXX: Dirty workaround for slow subquery of acclog

3003 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3004 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

3005 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3006 and id in (select id from $_t where $_i=a.id) \

3007 and val > coalesce((select time from myacclog where \

3008 tblrowid=a.rowid),\

3009 '1970-01-01'))"

3010 cnt="$cntnew as '新着', $cntall as '総数',"

3011 dt_class=" td2r td3r dumpblogs"

3012 fi

3013 # Construct join expression

3014 eav="" scols=""

3015 pk=`gettblpkey $2`

3016 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3017 substr=${substr:-%s}

3018 for col in ${3:-`gettbl_s_cols $2`}; do

3019 valvar=val

3020 case $col in

3021 gecos) scols="$scols${scols:+, }${col#}"

3022 continue ;; # built-in column name

3023 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3024 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3025 case "$as" in

3026 *:*=*) cnd=${as#*:}

3027 h=${cnd%%=*} v=${cnd#*=}

3028 h=`sqlquotestr "$h"`

3029 v=`sqlquotestr "$v"`

3030 valvar="CASE val WHEN $h THEN $v END"

3031 as=${as%%:*} ;;

3032 esac

3033 ;;

3034 *) as=${col} ;;

3035 esac

3036 ss=`printf "$substr" "$valvar"`

3037 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3038 scols="$scols${scols:+, }b.$as"

3039 done

3040 #case author when '$user' then a.rowid else '---' end as ID,

3041 sql=${DT_SQL:-"select \

3042 a.rowid as LINK,\

3043 $cnt\

3044 $scols from $2 a left join\

3045 (select $pk,$eav,

3046 max(case key when 'owner'

3047 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

3048 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

3049 ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"

3050 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3051 <div>

3052 <div class="dumptable">

3053 <table class="b$dt_class">

3054 `sq -header -cmd ".mode $1" $db "$presql$sql"`

3055 </table>

3056 </div>

3057 </div>

3058 EOF

3059 }

3060

3061 par2table() (

3062 # copy current parameters of par into destination table

3063 # $1=definition-file

3064 # Using $user and $session

3065 # Return value:

3066 # 0: Stored successfully

3067 # 1: Insufficient fillings

3068 # 2: No permission to modify the record

3069 # 3: Invalid rowid

3070 # 4: SUCCESS to delete

3071 # 5: Stop deletion for lack of confirm check

3072 # 6: Password length too short

3073 # 7: Password mismatch

3074 # 8: Old password incorrect

3075 rowid=`getpar rowid`

3076 if [ ! -e $1 ]; then

3077 echo "テーブル定義ファイルが見付かりません" | html p

3078 exit 1

3079 fi

3080 tbl=${1%.def}

3081 tbl=${tbl##*/}

3082 if [ -n "$rowid" ]; then # Modify existing entry

3083 if [ x"$tbl" = x"user" ]; then

3084 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3085 elif [ x"$tbl" = x"grp" ]; then

3086 sql="select gname from $tbl where rowid=$rowid;"

3087 ##err p2t:grp:q $sql

3088 isgrpowner "$user" "`query $sql`" && rowowner=$user

3089 else

3090 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3091 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3092 fi

3093 ### err rowowner=$rowowner

3094 if [ x"$user" != x"$rowowner" ]; then

3095 echo "他人のレコードはいじれないの" | html p

3096 return 2

3097 elif [ -z "$rowowner" ]; then

3098 echo "指定したレコードはないみたい" | html p

3099 return 3

3100 fi

3101 rm=`getpar rm` cfm=`getpar confirm`

3102 # Editing existent entry

3103 if [ x"$rm" = x"yes" ]; then

3104 if [ x"$rm$cfm" = x"yesyes" ]; then

3105 query "delete from $tbl where rowid=$rowid;"

3106 return 4

3107 else

3108 echo "消去確認のチェックがないので消さなかったの..." | html p

3109 return 5

3110 fi

3111 fi

3112 fi

3113

3114 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3115 if [ -n "$rowid" ]; then

3116 # Update of existing record

3117 for col in `gettblcols $tbl`; do

3118 val=`getparquote $col`

3119 [ -z "$val" ] && continue

3120 ## err query "update $tbl set $col=$val where rowid=$rowid"

3121 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3122 sql="update $tbl set $col=$val where rowid=$rowid;"

3123 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3124 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3125 ## User name cannot be changed with interface provided with this

3126 ## script. But we offer the trigger to change owner user

3127 ## of blog_s table.

3128 #err "select quote($col) from $tbl where rowid=$rowid;"

3129 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3130 cat<<-EOF | query

3131 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3132 -- should use EXCLUSIVE transaction outside of this.

3133 SAVEPOINT par2table;

3134 $sql

3135 update blog_s set val=$val

3136 where key='owner' and val=$old;

3137 RELEASE SAVEPOINT par2table;

3138 EOF

3139 ## XX: DIRTY Hack Ends here

3140 ## We should keep blog's owner as a single column which has

3141 ## foreign key constraint with primary key of grp/user.

3142 else

3143 query "$sql"

3144 fi

3145 done

3146 # Then, set up $pval for further insertion of tbl_s and tbl_m

3147 for col in `gettblpkey $tbl`; do

3148 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3149 pval="$pval${pval:+, }\"$val\""

3150 done

3151 else

3152 # New entry

3153 # Generate values() for primary keys

3154 for col in `gettblpkey $tbl`; do

3155 # Genuine primary keys for _m and _s

3156 val=`getvalquote $tbl $col`

3157 [ -z "$val" ] && continue

3158 pval="$pval${pval:+, }$val"

3159 done

3160 ##err pval=$pval

3161 for col in `gettblfkey $tbl`; do

3162 # args for values() to insertion into master table

3163 val=`getvalquote $tbl $col`

3164 [ -z "$val" ] && continue

3165 formaster=$formaster"${formaster:+, }$val"

3166 done

3167 formaster="$pval${formaster:+, }$formaster"

3168 ## err formaster=$formaster

3169 if [ -z "$formaster" ]; then

3170 echo "項目を全て埋めてください" | html pre

3171 return 1

3172 fi

3173 ## err "replace into $tbl values($formaster);"

3174 query "replace into $tbl values($formaster);"

3175 ## Insertion to master table, done

3176 fi

3177

3178 for kt in s m; do

3179 tb2=${tbl}_$kt

3180 for col in `gettbl_${kt}_cols $tbl`; do

3181 ptype=`getpartype $col "limit 1"`

3182

3183 # First, check update of existing entries in _m

3184 if [ $kt = m ]; then

3185 # sessID|address.1.22|string|Somewhere-x.y.z

3186 sql=""

3187 ##err dots from query "select var from par where var like '$col.%';"

3188 for v in `query "select var from par where var like '$col.%';"`; do

3189 # v=address.1.22

3190 st_rowid=${v##*.}

3191 origcol=${v%%.*} # original column derived from

3192 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3193 ##case `getpartype $v` in

3194 ## err CASE `gettbl_coltype $tbl/$origcol` in

3195 ## err edit flag = `getpar action.$v`

3196 case `getpar action.$v` in

3197 rm)

3198 if [ x`getpar confirm.$v` = x"yes" ]; then

3199 newsql="delete from $tb2"

3200 else

3201 echo "削除確認未チェック" | html p

3202 fi ;;

3203 edit)

3204 case `gettbl_coltype $tbl/$origcol` in

3205 image|document|binary)

3206 file=$tmpd/`getparfilename $v`

3207 ## err type=file=$file

3208 [ -z "$file" ] && continue

3209 bn=`sqlquotestr "${file##*/}"`

3210 bin="X'"$(hexize "$file")"'"

3211 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3212 type=\"file:$ct\"

3213 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3214 cachedir=`getcachedir "$tbl/$rowid"`

3215 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3216 rm -rf $cachedir

3217 ;;

3218 *)

3219 newsql="update $tb2 set val=(select val from par where var \

3220 like '$col.%.$st_rowid')"

3221 ;;

3222 esac

3223 ;;

3224 *) # maybe "keep", do not modify value

3225 continue

3226 ;;

3227 esac

3228 # err newsql=$newsql

3229 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3230 done

3231

3232 if [ x"$bin" = x"NULL" ]; then

3233 ## err repl:normal sql=`echo $sql`

3234 query "$sql

3235 delete from $tb2 where type='string' and val='';"

3236 ## err repl:normal done

3237 else

3238 sqlfile="$tmpd/sqlf.$$"

3239 echo "$sql" > $sqlfile

3240 ## err repl:sqlfile=`ls -lF $sqlfile`

3241 query ".read $sqlfile"

3242 ## err repl:done

3243 fi

3244 # Rest of kt==m: set multiple mode

3245 nr=`getparcount $col`

3246 else

3247 nr=1 # for kt==s, number of records is 1

3248 fi

3249

3250 i=0

3251 while [ $i -lt $nr ]; do

3252 limit="limit 1 offset $i"

3253 i=$((i+1)) # increase beforehand against continue

3254 val=`getvalquote $tbl $col "$limit"`

3255 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3256 ## err $col=$val

3257 bin=NULL

3258 ## err partype$col=`getpartype $col "$limit"`

3259 case $ptype in

3260 file) file=$tmpd/`getparfilename $col "$limit"`

3261 ## err parfile-$col=$file

3262 [ -z "$file" ] && continue

3263 bin="X'"$(hexize "$file")"'"

3264 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3265 type=\"file:$ct\" ;;

3266 "*"*) continue ;; # foreign table

3267 *) type=\"string\" ;;

3268 esac

3269 case `gettbl_coltype $tbl/$col` in

3270 password) # special care for password

3271 # name={password,pswd1,pswd2}

3272 p1=`getpar pswd1 "$limit"`

3273 if [ -z "$p1" ]; then

3274 continue # SKIP password setting, if p1 is empty

3275 else

3276 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3277 ## err pswd=$pswd

3278 if pwcheck "$pswd"; then

3279 if [ x"$p1" = x"$p2" ]; then

3280 case "$p1" in

3281 ??????????*) ;;

3282 *) echo "パスワードは10字以上にしてください。" | html p

3283 return 6;;

3284 esac

3285 val="\"`echo $p1|mypwhash`\""

3286 else

3287 echo "2つの新パスワード不一致" | html p

3288 return 7

3289 fi

3290 else

3291 echo "旧パスワード違います" | html p

3292 return 8

3293 fi

3294 fi

3295 ;;

3296 esac

3297 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3298 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3299 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3300 if [ x"$bin" = x"NULL" ]; then

3301 ## err Normal-query: `echo $sql`

3302 query "$sql"

3303 else

3304 sqlfile="$tmpd/query.$$"

3305 echo "$sql" > $sqlfile

3306 ## err sqlfile=`ls -lF $sqlfile`

3307 query ".read $sqlfile"

3308 fi

3309 ## err p2t done

3310 done

3311 done

3312 done

3313 return 0

3314 ##err donee

3315 )

3316 genform() {

3317 # $1 = form definition file

3318 # $2, $3 (optional)= table name and ROWID

3319 # If $GF_VIEWONLY set and nonNull, output values without form

3320 # If $GF_ARGS set, use it as content-strings in the form

3321 # If $GF_OWNER set, use it as value of name="owner"

3322 # If $GF_STAGE set, use it as value of name="stage"

3323 forms="" hiddens="" rowid=$3

3324 if [ ! -e "$1" ]; then

3325 echo "そのようなデータベースはないようです($2)。" | html p

3326 return

3327 elif [ -n "$2" ]; then

3328 rec=`query "select * from $2 where rowid='$rowid';"`

3329 if [ -z "$rec" ]; then

3330 pk=`gettblpkey $2`

3331 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3332 rec=`query "select rowid from $2 where $pk='$rowid';"`

3333 rowid=$rec

3334 rec=$3

3335 fi

3336 if [ -z "$rec" ]; then

3337 echo "そんなレコードはないみたいね..." | html p

3338 return

3339 fi

3340 fi

3341 if [ -z "$GF_VIEWONLY" ]; then

3342 rm='<input id="rm" name="rm" type="checkbox"

3343 value="yes"><label for="rm">このエントリの削除</label>

3344 <span>ほんとうに消しますよ(確認)!

3345 <input name="confirm" type=checkbox value="yes">はい</span>'

3346 fi

3347 # Image Cache dir

3348 ## err genform: getcache=$2/$rowid

3349 td=`getcachedir "$2/$rowid"`

3350 while IFS=: read prompt name keytype type args; do

3351 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3352 sp="${args:+ }"

3353 form="" val=""

3354 if [ -n "$rowid" ]; then

3355 # err genform2a: Seeking for "$2.$name, type=$type"

3356 rawval=`getvalbyid $2 $name $rowid $td`

3357 val=`echo "$rawval"|htmlescape`

3358 ## err genform3a: getvalbyid $2 $name $rowid $td

3359 ## err genform3b: val="[$val]" type="$type"

3360 fi

3361 if [ -n "$GF_VIEWONLY" ]; then

3362 is_hidden "$2" "$name" && continue

3363 fi

3364 case "$type" in

3365 text*)

3366 cgiform=cgi_multi_$type

3367 if [ -s $td/$name.count -a -n "$val" ]; then

3368 form=`$cgiform $name $td`

3369 val=$(echo "$val"|

3370 while read fn; do

3371 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3372 </td></tr>$nl"

3373 done)

3374 val="<table>$nl$val$nl</table>"

3375 else

3376 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3377 form=`cgi_$type $name "$rawval" "$args"`

3378 fi

3379 ;;

3380 [Rr][Aa][Dd][Ii][Oo])

3381 fh="<label><input type=\"radio\" name=\"$name\""

3382 form="`echo $args|sed -e \

3383 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3384 ;;

3385 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3386 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3387 ;;

3388 [Ss][Ee][Ll][Ee][Cc][Tt])

3389 fh="<select name=\"$name\">$nl"

3390 form=$(for l in $args; do

3391 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3392 done)

3393 if [ -n "$val" ]; then

3394 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3395 fi

3396 form="$fh$form</select>"

3397 ;;

3398 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3399 if [ -s $td/$name.count ]; then

3400 form=`cgi_multi_file $name $td "$args"`

3401 if [ -n "$val" ]; then

3402 hrfb="$myname?showattc+$2_m"

3403 val=$(echo "$rawval" \

3404 | while read fn; do

3405 data=`percenthex "$td/$fn"`

3406 #ct=`cat $td/$fn.content-type`

3407 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3408 ri=`cat "$td/$fn.rowid"`

3409 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3410 #imgsrc="<img src=\"data:$ct,$data\">"

3411 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3412 iconhref "$td/$fn" "$hrfb+$ri" ""

3413 done)

3414 fi

3415 else

3416 form="<input type=\"file\" name=\"$name\" $args>"

3417 if [ -n "$val" ]; then

3418 imgs=$(echo "$rawval"\

3419 |while read fn;do

3420 data=`percenthex "$td/$fn"`

3421 echo "<img src=\"data:image/png,$data\">$fn<br>"

3422 done)

3423 form=$form"<br>$imgs"

3424 val=$imgs # 2015-06-15

3425 else

3426 form="<input type=\"file\" name=\"$name\" $args>"

3427 fi

3428 fi

3429 ;;

3430 [Hh][Ii][Dd][Dd][Ee][Nn])

3431 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3432 args="value=\"$GF_STAGE\""

3433 fi

3434 form="<input type=\"hidden\" name=\"$name\" $args>"

3435 prompt='' # Remove prompt

3436 ;;

3437 [Aa][Uu][Tt][Hh][Oo][Rr])

3438 [ -n "$GF_VIEWONLY" ] && continue

3439 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3440 prompt="" ;;

3441 [Oo][Ww][Nn][Ee][Rr])

3442 [ -n "$GF_VIEWONLY" ] && continue

3443 val=${GF_OWNER:-$val}

3444 val=${val:-$user}

3445 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3446 prompt="" ;;

3447 [Uu][Ss][Ee][Rr])

3448 # XXX: is null $user ok?

3449 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3450 [ -n "$GF_VIEWONLY" ] && continue

3451 form="$user"

3452 ;;

3453 [Pp]assword)

3454 [ -n "$GF_VIEWONLY" ] && continue

3455 form="`cgi_passwd`"

3456 val=""

3457 ;;

3458 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3459 [ -n "$GF_VIEWONLY" ] && continue

3460 if [ -z "$rowid" ]; then

3461 val=`genserial`

3462 fi

3463 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3464 prompt="" ;;

3465 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3466 prompt=""

3467 ;;

3468 parent|path|blog*)

3469 prompt=""

3470 ;;

3471 "*"*)

3472 tail=$tail"``"

3473 continue ;;

3474 esac

3475 if [ -n "$prompt" ]; then

3476 if [ -n "${GF_VIEWONLY}" ]; then

3477 form=$val

3478 else

3479 :

3480 fi

3481 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3482 else

3483 hiddens=$hiddens$nl"$form"

3484 fi

3485 done < $1

3486 # enctype="multipart/form-data"

3487 cat<<EOF

3488 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3489 ${rowid:+$rm}

3490 <table class="b $2">

3491 $forms

3492 </table>$hiddens

3493 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3494 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3495 EOF

3496 if [ -z $GF_VIEWONLY ]; then

3497 cat<<EOF

3498 <input type="submit" name="sub" value="OK">

3499 <input type="reset" name="res" value="Reset">

3500 EOF

3501 fi

3502 cat<<EOF

3503 $GF_ARGS</form>

3504 $tail

3505 EOF

3506 }

3507 edittable() {

3508 # $1=form-def $2=table $3 rowid

3509 genform "$@"

3510 }

3511 viewtable() {

3512 GF_VIEWONLY=1 genform "$@"

3513 }

3514 showattc() {

3515 # $1=table_m $2=rowid &optional $3=RawFlag

3516 ## err \$1=$1 \$2=$2 \$3=$3

3517 if ! isfilereadable $user $1 $2; then

3518 contenttype; echo

3519 echo "このファイルは管理者のみしか見られません" | html p

3520 putfooter; exit

3521 fi

3522 idir=`umask 002; mktempd` || exit 1

3523 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3524 bin=$idir/$myname-$$.bin

3525 sql="select quote(bin) from $1 where rowid='$2';"

3526 ## err showattc: sql: $sql

3527 sq $db "$sql" | unhexize > $bin

3528 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3529 type=${tv%//*} fn=${tv#*//}

3530 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3531 ct=${type#file:}

3532 case $ct in # all text/* changed to text/plain

3533 text/*)

3534 charset=`nkf -g $bin|cut -d' ' -f1`

3535 case $charset in

3536 ASCII*) charset="" ;;

3537 esac

3538 if [ -z "$3" ]; then

3539 ct="text/html${charset:+; charset=$charset}"

3540 link="?showattc+$1+$2+raw"

3541 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3542 | sed 's,^,<span></span>,' \

3543 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3544 -D_LINK_="$link" \

3545 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3546 exit $?

3547 fi

3548 ct="text/plain${charset:+; charset=$charset}"

3549 ;;

3550 esac

3551 contenttype "$ct"

3552 echo "Content-Disposition: filename=\"$fn\""

3553 echo "Content-Length: " `cat $bin | wc -c`; echo

3554 #echo "Content-Type: " ${type#file:}; echo

3555 cat $bin

3556 }

3557 #

3558 # Some default stupid handler on CGI values

3559 #

3560 default_storedb() {

3561 # ARG: $1=table-def-file

3562 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3563 tbl=`basename $1`

3564 tbl=${tbl%.def}

3565 cols="`grep :text $1|cut -d: -f2`"

3566 col=`echo "$cols"|head -1`

3567 vcol=`getpar $col`

3568 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3569 if [ -n "$vcol" ]; then

3570 par2table $1

3571 else

3572 return 2 # No insertion occurred

3573 fi

3574 }

3575

3576 default_view() { # $1=def-file

3577 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3578 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3579 default_storedb "$@"

3580 query "select rowid from $tbl order by rowid desc;" \

3581 | while read rowid; do

3582 viewtable $1 $tbl $rowid

3583 done | _m4 -D_TITLE_="$tbl" \

3584 -D_FORM_="`genform $1`" \

3585 -D_DUMPTABLE_="syscmd(cat)" \

3586 $layout/html.m4.html $layout/form+dump.m4.html

3587 }

3588 default_viewtext() { # $1=def-file

3589 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3590 default_storedb "$@"

3591 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3592 | _m4 -D_TITLE_="$tbl" \

3593 -D_FORM_="`genform $1`" \

3594 -D_DUMPTABLE_="syscmd(cat)" \

3595 $layout/html.m4.html $layout/form+dump.m4.html

3596 }

3597 default_smail() {

3598 default_storedb "$@"

3599 if [ $? -eq 2 ]; then

3600 _m4 -D_TITLE_="入力" \

3601 -D_FORM_="`genform $1`" \

3602 -D_DUMPTABLE_="" \

3603 $layout/html.m4.html $layout/form+dump.m4.html

3604 return

3605 fi

3606 cond=""

3607 for pk in `gettblpkey $tbl`; do

3608 pv=$(sqlquote "$(getpar $pk)")

3609 cond="$cond${cond:+ and }$pk=$pv"

3610 done

3611 sql="select rowid from $tbl where $cond;"

3612 rowid=`query "$sql"`

3613 ## err smail1 - "$sql" "-> rowid=$rowid"

3614

3615 while IFS=: read prompt name keytype type args; do # Read from $1

3616 val=`getpar $name`

3617 if [ -n "$val" ]; then

3618 text="$text

3619 $prompt

3620 $name=$val

3621 ---------------------------------------------------------"

3622 fi

3623 case "$type" in

3624 image|document|file)

3625 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3626 fns=$(echo "$fn"|while read fn; do

3627 err mv $tmpd/$fn.orig $tmpd/$fn

3628 mv $tmpd/$fn.orig $tmpd/$fn

3629 rm $tmpd/$fn.rowid # Remove cache flag

3630 ## err "`ls $tmpd/$fn`"

3631 echo $fn

3632 done)

3633 files="$files $fns"

3634 ;;

3635 esac

3636 done < $1

3637 ## err FILES=$files "`ls -lF $tmpd`"

3638 subj="from ${REMOTE_ADDR}"

3639 (echo "$url"

3640 echo "への書き込みがありました。"

3641 echo "------"

3642 echo "$text"

3643 ) | (cd $tmpd &&

3644 err LS="`ls -lF`" &&

3645 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3646 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3647 echo "以下の内容で送信しました。" | html p

3648 viewtable $1 $tbl \

3649 `query "select rowid from $tbl order by rowid desc limit 1;"`

3650 echo "戻る" | html a "href=\"?\""

3651 }