s4: 4769510d6169 s4-funcs.sh

s4

view s4-funcs.sh @ 447:4769510d6169

Multiple Page Listing allows user specification of starting number

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 14 Jul 2017 10:25:06 +0859
parents	12548e1a4ff8
children	9724472a3cd2

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`; offset=${offset%%[!0-9]*}

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

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

1937 offset=$((offset-1))

1938 fi

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

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

1941 limit=30

1942 dir=`getcachedir "$1"`

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

1944 hrb="$myname?home"

1945 deficon=person-default.png

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

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

1948 gcs=gecos

1949 else # if group

1950 hrb="$myname?grp"

1951 deficon=group-default.png

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

1953 gcs=name

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

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

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

1957 ## err tagconv=$tagconv

1958 fi

1959 fi

1960 if [ ! -d $dir ]; then

1961 mkdir -p $dir

1962 fi

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

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

1965 fi

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

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

1968 fi

1969

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

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

1972 ##qgrp=`sqlquote $grp`

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

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

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

1976 quote(a.$nm) as qname,

1977 (SELECT val FROM ${tbl}_s

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

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

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

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

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

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

1984 end

1985 as name,

1986 b.tag,

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

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

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

1990 then '(ADMIN)'

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

1992 then '(Member)'

1993 when '$iamowner' = '' then ''

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

1995 CASE '$entity'

1996 WHEN 'グループ'

1997 THEN coalesce(

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

1999 'open')

2000 ||

2001 CASE WHEN '$user'

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

2003 THEN ' ismember'

2004 ELSE ''

2005 END

2006 ELSE 'user'

2007 END regmode

2008 from $tbl a left join

2009 (select $nm as name,

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

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

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

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

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

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

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

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

2018 # Note that mtime is stored only in grp_s.

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

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

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

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

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

2024 cgi_form $stage <<EOF

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

2026 `cgi_text kwd $kwd`</label>

2027 EOF

2028 echo '</div>'

2029 fi

2030 hiddens="$hiddens

2031 `cgi_hidden kwd \"$kwd\"`

2032 `cgi_hidden stage \"$stage\"`"

2033 cat<<EOF

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

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

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

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

2038 </form>

2039 EOF

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

2041 nextbtn=$(

2042 cat<<EOF

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

2044 `cgi_submit 次の${limit}件`

2045 $hiddens

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

2047 EOF

2048 )

2049 fi

2050 if [ $offset -gt 0 ]; then

2051 prevbtn=$(

2052 cat<<EOF

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

2054 `cgi_submit 前の${limit}件`

2055 $hiddens

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

2057 EOF

2058 )

2059 fi

2060 pnbtn="$nextbtn$prevbtn"

2061 echo $pnbtn

2062

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

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

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

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

2067 err name=$name owner=$ownerp lnk=$lnk

2068 err newlnk=$lnk regmode=$regmode

2069 icondir=$dir/$id

2070 # Pick up only last icon

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

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

2073 | _m4 $tagconv

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

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

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

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

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

2079 else

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

2081 fi

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

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

2084 else

2085 cond="$nm=$qname"

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

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

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

2089 # err "img=[$img]"

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

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

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

2093 echo "</a>"

2094 else

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

2096 fi

2097 fi

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

2099 echo "</div>"

2100 done

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

2102 )

2103 listmember() {

2104 listentry user "$@"

2105 }

2106 listgroup() {

2107 listentry group "$@"

2108 }

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

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

2111 query "SELECT DISTINCT hex(val) FROM grp_mem_m

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

2113 }

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

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

2116 grid=`getpar grid`

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

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

2119 else

2120 grid=$1

2121 fi

2122 grp=`getgroupbyid $grid`

2123 qgrp=`sqlquote "$grp"`

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

2125 if isgroup "$grp"; then

2126 tf=$tmpd/title.$$

2127 sf=$tmpd/search.$$

2128 bodyclass=`query "SELECT val FROM grp_s

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

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

2131 ismember="ismember"

2132 qgrp=`sqlquote "$grp"`

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

2134 else

2135 ismember="" # bodyclass="group"

2136 fi

2137 bodyclass="$bodyclass grouphome"

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

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

2140

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

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

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

2144 -D_BODYCLASS_="$bodyclass" \

2145 -D_DUMPTABLE_="" \

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

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

2148 # $htmlheader is defined in grp()

2149 else # if $grp is removed at par2table

2150 listgroup

2151 fi

2152 }

2153 showgroupsub() {

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

2155 # Using $ismember

2156 rowid=$2

2157 grp=`getgroupbyid $2`

2158 qgrp=`sqlquote "$grp"`

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

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

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

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

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

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

2165 return

2166 fi

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

2168 enticond="gname=$qgrp"

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

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

2171 cat<<-EOF

2172 <p class="groupimg">

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

2174 EOF

2175 fi

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

2177 viewtable $1 grp $rowid

2178 echo "</div>"

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

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

2181 iamowner=$rowid

2182 colmd=" mode"

2183 fi

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

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

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

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

2188 cat<<EOF

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

2190 <div class="fold clear">

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

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

2193 <div>

2194 `cgi_textarea message "" "cols=60"`

2195 `cgi_submit 送信`

2196 `cgi_reset リセット`

2197 </div>

2198 `cgi_hidden grp $rowid`

2199 </div></form>

2200 EOF

2201 fi

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

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

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

2205 else

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

2207 fi

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

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

2210 and key='email';"`

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

2212 ## and key='email';"

2213 ##err email=$eml

2214 cat <<EOF

2215 <div class="fold clear">

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

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

2218 <div>

2219 EOF

2220 cgi_form grp <<EOF

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

2222 <table class="b">

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

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

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

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

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

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

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

2230 </table>

2231 `cgi_hidden grp $rowid`

2232 EOF

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

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

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

2236 fi

2237 echo '</div></div>'

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

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

2240 cat<<-EOF

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

2242 `cgi_hidden owner "$grp"`

2243 `cgi_hidden kwd "@week"`

2244 `cgi_hidden stage searchart`

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

2246 </form>

2247 EOF

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

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

2250 DT_CHLD=article:blogid \

2251 DT_VIEW=replyblog dumptable html blog \

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

2253

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

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

2256 cm="?commission+$rowid"

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

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

2259 # team list

2260 hexteams=`hexteams "$grp"`

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

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

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

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

2265 SELECT val TEAM,

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

2267 MEMBERS

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

2269 EOF

2270 echo '</table></div>'

2271 fi

2272 }

2273 grp_getbodyclass() {

2274 # Get css class name for document.

2275 # `moderated' for moderated groups

2276 # `ismember' for groups where user belongs

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

2278 # $user=userNameCurrentlyLogin

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

2280 qgrp=`sqlquote "$1"`

2281 query<<-EOF

2282 SELECT coalesce(

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

2284 'open')

2285 ||

2286 CASE WHEN '$user'

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

2288 THEN ' ismember'

2289 ELSE ''

2290 END;

2291 EOF

2292 }

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

2294 err GRP_ACTION:IN

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

2296 grp=`getgroupbyid "$grid"`

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

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

2299 fi

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

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

2302 fi

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

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

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

2306 $layout/html.m4.html

2307

2308 isowner=""

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

2310 usel=`getpar usel`

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

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

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

2314 text=`getpar text`

2315

2316 rm=`getpar rm` cfm=`getpar confirm`

2317 ## err rm=$rm cfm=$cfm

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

2319 if [ "$isowner" ]; then

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

2321 # Eliminate

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

2323 for tbl in grp_mem grp_mem_s grp_mem_m; do

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

2325 # echo "sql=$sql"

2326 query "$sql"

2327 err rmGRPuser "$sql"

2328 done

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

2330 #err num=$num

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

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

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

2334 # err `echo "$sql"`

2335 html pre<<EOF

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

2337 `query "$sql"`

2338 EOF

2339 fi

2340 else

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

2342 return

2343 fi

2344 else # not Group Owner

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

2346 return

2347 fi

2348 cat<<EOF

2349

2350 EOF

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

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

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

2354 return 0

2355 fi

2356 gecos=`gecos $user`

2357 mkfrom=`getpar mkfrom`

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

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

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

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

2362 mail_from="$safegc <$fromad>"

2363 else

2364 mail_from="$admin"

2365 fi

2366 MAIL_FROM=$mail_from \

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

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

2369 $url

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

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

2372 ----------------------------------------------------------

2373 $text

2374 EOF

2375 if [ $? = 0 ]; then

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

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

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

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

2380 html pre<<EOF

2381 `query "$sql"`

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

2383 EOF

2384 err SendDone: `echo $sql`

2385 fi

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

2387 grp_reg_adm $grid $usel

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

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

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

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

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

2393 team=newteam

2394 fi

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

2396 cat<<-EOF | html p

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

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

2399 EOF

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

2401 else

2402 grp_add_team $grid "$team" $usel

2403 fi

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

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

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

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

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

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

2410 grp_rm_team $grid "$rmteam" $usel

2411 else

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

2413 fi

2414 else

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

2416 fi

2417 fi

2418 fi

2419 # New entry

2420 sql="WITH mems AS (

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

2422 ON gm.user=g.name

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

2424 ), posts AS (

2425 SELECT author, count(author) post

2426 FROM article NATURAL JOIN article_s

2427 WHERE blogid IN (SELECT id FROM blog_s

2428 WHERE key='owner'

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

2430 AND key='text'

2431 GROUP BY author

2432 ), teams AS (

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

2434 FROM grp_mem_m

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

2436 AND key='team'

2437 GROUP BY user

2438 ), user_post AS (

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

2440 FROM mems m LEFT JOIN posts

2441 ON m.name=posts.author

2442 GROUP by m.rowid

2443 )

2444 SELECT

2445 CASE

2446 WHEN (SELECT user FROM grp_adm

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

2448 AND user=up.name) IS NOT NULL

2449 then 'k'

2450 ELSE ''

2451 END || rowid || ',' || gecos NAME,

2452 post POST, team TEAM

2453 FROM user_post up LEFT JOIN teams t

2454 ON up.name=t.user

2455 ORDER BY gecos;"

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

2457 tf=$tmpd/title.$$

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

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

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

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

2462 </p></div>"

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

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

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

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

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

2468 </div>"

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

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

2471 allhexteams=$(hexteams "$grp")

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

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

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

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

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

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

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

2479 </div>

2480 "

2481 fi

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

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

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

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

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

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

2488 cgi_form grpaction<<EOF \

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

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

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

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

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

2494 $layout/form+dump.m4.html

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

2496 <div class="foldtabs">

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

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

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

2500 `cgi_datalist_h teams $allhexteams`

2501 </div>

2502 ${rmteammsg}

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

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

2505 <div>

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

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

2508 `cgi_textarea text "" cols=40`

2509 </div>

2510 ${isowner:+$cmmsg$excmsg}

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

2512 </div>

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

2514 <table class="td2r">

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

2516 </table>

2517 `cgi_hidden grp $grid`

2518 EOF

2519 }

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

2521 # Create TEMPORARY VIEW

2522 query<<EOF

2523 CREATE TEMPORARY VIEW writeusers AS

2524 SELECT DISTINCT author FROM article

2525 WHERE id in (

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

2527 );

2528 CREATE TEMPORARY VIEW movablegroups AS

2529 SELECT g.rowid growid , g.gname

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

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

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

2533 WHERE (SELECT author FROM writeusers

2534 EXCEPT

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

2536 IS NULL;

2537 $2

2538 EOF

2539 }

2540 sql4readableblogs() {

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

2542 # Blog is readable when:

2543 # 1: blog owner is an user

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

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

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

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

2548 CREATE TEMPORARY VIEW readableblogs AS

2549 SELECT blog.rowid rid, id, author

2550 FROM blog

2551 NATURAL JOIN

2552 (SELECT id,

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

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

2555 FROM blog_s GROUP by id) bs

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

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

2558 WHEN (SELECT val FROM grp_s

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

2560 AND

2561 (SELECT user FROM grp_mem

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

2563 THEN 0

2564 WHEN mode = 'quiz'

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

2566 ELSE 1

2567 END;

2568 EOF

2569 }

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

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

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

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

2574 return

2575 fi

2576 owner=`getvalbyid blog owner $rowid`

2577 title=`getvalbyid blog title $rowid`

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

2579 | _m4 -D_TITLE_="修正" \

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

2581 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

2584 # Move to group

2585 if isuser "$owner"; then

2586 crview4article $rowid

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

2588 ## err N=$n

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

2590 ## err ROWID=$rowid

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

2592 cat<<-EOF

2593 <div class="fold">

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

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

2596 <div>

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

2598 移動先グループ:

2599 <select name="mv2grp">

2600 EOF

2601 query ".mode html"

2602 query<<-EOF |

2603 $sql

2604 .mode list

2605 EOF

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

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

2608 cat<<-EOF

2609 </select>

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

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

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

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

2614 `cgi_hidden blogrowid $rowid`

2615 `cgi_submit 移動`

2616 `cgi_reset Reset`

2617 </form>

2618 </div>

2619 </div>

2620 EOF

2621 fi

2622 # end of isuser "$owner"

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

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

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

2626 cat<<-EOF

2627 <div class="fold">

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

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

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

2631 `query "SELECT

2632 coalesce((SELECT val FROM blog_s

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

2634 AND key='team'),

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

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

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

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

2639 `cgi_hidden blogrowid $rowid`<br>

2640 `cgi_submit 移動`

2641 `cgi_reset Reset`

2642 </form></div></div>

2643 EOF

2644 fi

2645 }

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

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

2648 blogrowid=`getpar blogrowid`

2649 cfm=`getpar cfm`

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

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

2652 . ./s4-blog.sh

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

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

2655 return

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

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

2658 elif { mv2grp=`getpar mv2grp`

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

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

2661 crview4article $blogrowid

2662 ########## TRANSACTION BEGIN

2663 query "BEGIN;"

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

2665 ## err Nwriteuser=$n

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

2667 query<<-EOF

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

2669 WHERE key='owner'

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

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

2672 EOF

2673 fi

2674 query "END;"

2675 ########## TRANSACTION END

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

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

2678 # blog owner can move it to ANY team

2679 case "$mv2team" in

2680 'なし')

2681 cat<<-EOF

2682 DELETE FROM blog_s

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

2684 AND key='team';

2685 EOF

2686 ;;

2687 "") ;;

2688 *)cat<<-EOF

2689 BEGIN;

2690 REPLACE INTO blog_s(id, key, val)

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

2692 'team', '$mv2team');

2693 REPLACE INTO blog_s(id, key, val)

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

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

2696 END;

2697 EOF

2698 esac | query

2699 fi

2700 blog_reply $blogrowid

2701 echo yes | html p

2702 }

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

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

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

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

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

2708 return

2709 fi

2710 owner=`getvalbyid blog owner $blogrowid`

2711 title=`getvalbyid blog title $blogrowid`

2712 author=`getvalbyid article author $rowid`

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

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

2715 : EDIT OK

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

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

2718 return

2719 fi

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

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

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

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

2724 > $tmpout

2725 rm -f /tmp/editart.out

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

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

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

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

2730 -D_BLOGS_= -D_DUMPTABLE_= \

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

2732 }

2733 send2mem() {

2734 rowid=`getpar grp`

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

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

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

2738 return

2739 fi

2740 message=`getpar message`

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

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

2743 return

2744 fi

2745 grp=`getgroupbyid $rowid`

2746 members=`collectemail $grp`

2747 # smail rcpt subj (file)

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

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

2750 $urlbase?grp+$rowid

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

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

2753

2754 $message

2755 EOF

2756 cat<<EOF

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

2758 <pre>

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

2760 </pre>

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

2762 EOF

2763 }

2764 joingrpadmit() {

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

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

2767 echo "bye bye" | html p; return

2768 fi

2769 t_usr=`session=$2 getpar user`

2770 t_grp=`session=$2 getpar group`

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

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

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

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

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

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

2777 return

2778 fi

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

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

2781 fi

2782 case $1 in

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

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

2785 *)

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

2787 return ;;

2788 esac

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

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

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

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

2793 に

2794 $t_usr

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

2796 しました。"

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

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

2799 echo "$body" | html p

2800 }

2801

2802 joingrprequest() {

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

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

2805 addsession $jss +${memoplimitdays}days

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

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

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

2809 $url

2810 $user さんから

2811 グループ $1

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

2813

2814 承認する:

2815 $urlbase?joingrpadmit+yes+$jss

2816

2817 白紙に戻す:

2818 $urlbase?joingrpadmit+no+$jss

2819 EOF

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

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

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

2823 }

2824 joingrp() {

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

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

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

2828 isowner="yes"

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

2830 isowner="yes"

2831 else

2832 isowner=""

2833 fi

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

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

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

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

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

2839 return

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

2841 : # Do not pursue those who leave

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

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

2844 else # adding user is $user itself

2845 case `getgroupattr $1 regmode` in

2846 moderated)

2847 joingrprequest "$@" # Request only

2848 return

2849 ;;

2850 *)

2851 ;;

2852 esac

2853 fi

2854 qgname=`sqlquote "$1"`

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

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

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

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

2859 # Notify joingrp to admin

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

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

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

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

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

2865 else

2866 echo $msg

2867 fi

2868 else

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

2870 fi

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

2872 # Coming here means newly created group

2873 sql="select case\

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

2875 then (select user from grp_mem\

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

2877 else '' end; "

2878 err NewGrpChk: $sql

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

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

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

2882 fi

2883 fi

2884 else

2885 query "delete from grp_mem $cond;

2886 delete from grp_mem_s $cond;

2887 delete from grp_mem_m $cond;"

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

2889 fi

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

2891 $url?grp+$grid

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

2893 グループ $1

2894 $action

2895 EOF

2896 }

2897 grp_add_team() (

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

2899 grp=`getgroupbyid $1`

2900 team=$2; shift; shift

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

2902 { echo "BEGIN;"

2903 for user; do

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

2905 '$grp',\

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

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

2908 done

2909 echo "END;"

2910 } | query

2911 )

2912 grp_rm_team() (

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

2914 grid=$1

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

2916 team=$2; shift; shift

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

2918 { echo "BEGIN;"

2919 for user; do

2920 echo "DELETE FROM grp_mem_m\

2921 WHERE gname=$qgrp \

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

2923 AND key='team' AND val='$team';"

2924 done

2925 cat<<-EOF

2926 DELETE FROM blog_s

2927 WHERE rowid=(

2928 SELECT rowid

2929 FROM blog_s a

2930 WHERE key='team'

2931 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2932 AND NOT EXISTS (SELECT * FROM grp_mem_m

2933 WHERE key='team' AND val=a.val -- a.val=team

2934 AND gname = (SELECT val FROM blog_s b

2935 WHERE a.id=b.id AND key='owner')

2936 ));

2937 EOF

2938

2939 echo "END;"

2940 } | query

2941 )

2942 grp_reg_adm() {

2943 # $1=grp-rowid $2...=user-rowid

2944 grid=$1

2945 grp=`getgroupbyid "$1"`

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

2947 echo "無効なグループIDです" | html p; return

2948 fi

2949 if ! isgrpowner "$user" "$grp"; then

2950 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2951 fi

2952 shift

2953 for urid; do

2954 newadm=`query "select name from user where rowid=$urid;"`

2955 if [ -z "$newadm" ]; then

2956 echo "指定ユーザIDがおかしいようです。" | html p; return

2957 fi

2958 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

2959 err ismember $newadm $grp

2960 if ismember $newadm "$grp"; then

2961 # OK, go ahead

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

2963 query "replace into grp_adm values($getgname, '$newadm');"

2964 # confirm insertion

2965 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

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

2967 echo "追加完了: $newadm" | html p

2968 else

2969 echo "追加失敗($1 $urid)" | html p

2970 fi

2971 fi

2972 showgroup $grid

2973 done

2974 }

2975 dt_rowhack() {

2976 # From: <TR>

2977 # ....

2978 # <TD>rowclass=foo</TD>

2979 # </TR>

2980 # To: <TR class="foo">....<TD>foo</TD></TR>

2981 sed -e '

2982 /^<TR>/ {

2983 :loop

2984 s/\n//

2985 N

2986 /<\/TR>/ {

2987 s/\n//

2988 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

2989 n

2990 }

2991 $q

2992 b loop

2993 }'

2994 }

2995 dumptable() {

2996 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2997 # textのフィールドだけ全てダンプにしたほうがいいか

2998 # $DT_VIEW sets link

2999 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3000 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3001 VIEW=${DT_VIEW-replyblog}

3002 if [ -n "$VIEW" ]; then

3003 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3004 fi

3005 # $DT_CHLD=ChildTable:BindColumn

3006 if [ -n "$DT_CHLD" ]; then

3007 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3008 cntall="(select count($_i) from $_t where $_i=a.id)"

3009 # XXX: Dirty workaround for slow subquery of acclog

3010 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3011 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

3012 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3013 and id in (select id from $_t where $_i=a.id) \

3014 and val > coalesce((select time from myacclog where \

3015 tblrowid=a.rowid),\

3016 '1970-01-01'))"

3017 cnt="$cntnew as '新着', $cntall as '総数',"

3018 dt_class=" td2r td3r dumpblogs"

3019 fi

3020 # Construct join expression

3021 eav="" scols=""

3022 pk=`gettblpkey $2`

3023 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3024 substr=${substr:-%s}

3025 for col in ${3:-`gettbl_s_cols $2`}; do

3026 valvar=val

3027 case $col in

3028 gecos) scols="$scols${scols:+, }${col#}"

3029 continue ;; # built-in column name

3030 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3031 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3032 case "$as" in

3033 *:*=*) cnd=${as#*:}

3034 h=${cnd%%=*} v=${cnd#*=}

3035 h=`sqlquotestr "$h"`

3036 v=`sqlquotestr "$v"`

3037 valvar="CASE val WHEN $h THEN $v END"

3038 as=${as%%:*} ;;

3039 esac

3040 ;;

3041 *) as=${col} ;;

3042 esac

3043 ss=`printf "$substr" "$valvar"`

3044 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3045 scols="$scols${scols:+, }b.$as"

3046 done

3047 #case author when '$user' then a.rowid else '---' end as ID,

3048 sql=${DT_SQL:-"select \

3049 a.rowid as LINK,\

3050 $cnt\

3051 $scols from $2 a left join\

3052 (select $pk,$eav,

3053 max(case key when 'owner'

3054 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

3055 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

3056 ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"

3057 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3058 <div>

3059 <div class="dumptable">

3060 <table class="b$dt_class">

3061 `sq -header -cmd ".mode $1" $db "$presql$sql"`

3062 </table>

3063 </div>

3064 </div>

3065 EOF

3066 }

3067

3068 par2table() (

3069 # copy current parameters of par into destination table

3070 # $1=definition-file

3071 # Using $user and $session

3072 # Return value:

3073 # 0: Stored successfully

3074 # 1: Insufficient fillings

3075 # 2: No permission to modify the record

3076 # 3: Invalid rowid

3077 # 4: SUCCESS to delete

3078 # 5: Stop deletion for lack of confirm check

3079 # 6: Password length too short

3080 # 7: Password mismatch

3081 # 8: Old password incorrect

3082 rowid=`getpar rowid`

3083 if [ ! -e $1 ]; then

3084 echo "テーブル定義ファイルが見付かりません" | html p

3085 exit 1

3086 fi

3087 tbl=${1%.def}

3088 tbl=${tbl##*/}

3089 if [ -n "$rowid" ]; then # Modify existing entry

3090 if [ x"$tbl" = x"user" ]; then

3091 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3092 elif [ x"$tbl" = x"grp" ]; then

3093 sql="select gname from $tbl where rowid=$rowid;"

3094 ##err p2t:grp:q $sql

3095 isgrpowner "$user" "`query $sql`" && rowowner=$user

3096 else

3097 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3098 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3099 fi

3100 ### err rowowner=$rowowner

3101 if [ x"$user" != x"$rowowner" ]; then

3102 echo "他人のレコードはいじれないの" | html p

3103 return 2

3104 elif [ -z "$rowowner" ]; then

3105 echo "指定したレコードはないみたい" | html p

3106 return 3

3107 fi

3108 rm=`getpar rm` cfm=`getpar confirm`

3109 # Editing existent entry

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

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

3112 query "delete from $tbl where rowid=$rowid;"

3113 return 4

3114 else

3115 echo "消去確認のチェックがないので消さなかったの..." | html p

3116 return 5

3117 fi

3118 fi

3119 fi

3120

3121 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

3123 # Update of existing record

3124 for col in `gettblcols $tbl`; do

3125 val=`getparquote $col`

3126 [ -z "$val" ] && continue

3127 ## err query "update $tbl set $col=$val where rowid=$rowid"

3128 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3129 sql="update $tbl set $col=$val where rowid=$rowid;"

3130 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3131 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3132 ## User name cannot be changed with interface provided with this

3133 ## script. But we offer the trigger to change owner user

3134 ## of blog_s table.

3135 #err "select quote($col) from $tbl where rowid=$rowid;"

3136 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3137 cat<<-EOF | query

3138 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3139 -- should use EXCLUSIVE transaction outside of this.

3140 SAVEPOINT par2table;

3141 $sql

3142 update blog_s set val=$val

3143 where key='owner' and val=$old;

3144 RELEASE SAVEPOINT par2table;

3145 EOF

3146 ## XX: DIRTY Hack Ends here

3147 ## We should keep blog's owner as a single column which has

3148 ## foreign key constraint with primary key of grp/user.

3149 else

3150 query "$sql"

3151 fi

3152 done

3153 # Then, set up $pval for further insertion of tbl_s and tbl_m

3154 for col in `gettblpkey $tbl`; do

3155 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3156 pval="$pval${pval:+, }\"$val\""

3157 done

3158 else

3159 # New entry

3160 # Generate values() for primary keys

3161 for col in `gettblpkey $tbl`; do

3162 # Genuine primary keys for _m and _s

3163 val=`getvalquote $tbl $col`

3164 [ -z "$val" ] && continue

3165 pval="$pval${pval:+, }$val"

3166 done

3167 ##err pval=$pval

3168 for col in `gettblfkey $tbl`; do

3169 # args for values() to insertion into master table

3170 val=`getvalquote $tbl $col`

3171 [ -z "$val" ] && continue

3172 formaster=$formaster"${formaster:+, }$val"

3173 done

3174 formaster="$pval${formaster:+, }$formaster"

3175 ## err formaster=$formaster

3176 if [ -z "$formaster" ]; then

3177 echo "項目を全て埋めてください" | html pre

3178 return 1

3179 fi

3180 ## err "replace into $tbl values($formaster);"

3181 query "replace into $tbl values($formaster);"

3182 ## Insertion to master table, done

3183 fi

3184

3185 for kt in s m; do

3186 tb2=${tbl}_$kt

3187 for col in `gettbl_${kt}_cols $tbl`; do

3188 ptype=`getpartype $col "limit 1"`

3189

3190 # First, check update of existing entries in _m

3191 if [ $kt = m ]; then

3192 # sessID|address.1.22|string|Somewhere-x.y.z

3193 sql=""

3194 ##err dots from query "select var from par where var like '$col.%';"

3195 for v in `query "select var from par where var like '$col.%';"`; do

3196 # v=address.1.22

3197 st_rowid=${v##*.}

3198 origcol=${v%%.*} # original column derived from

3199 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3200 ##case `getpartype $v` in

3201 ## err CASE `gettbl_coltype $tbl/$origcol` in

3202 ## err edit flag = `getpar action.$v`

3203 case `getpar action.$v` in

3204 rm)

3205 if [ x`getpar confirm.$v` = x"yes" ]; then

3206 newsql="delete from $tb2"

3207 else

3208 echo "削除確認未チェック" | html p

3209 fi ;;

3210 edit)

3211 case `gettbl_coltype $tbl/$origcol` in

3212 image|document|binary)

3213 file=$tmpd/`getparfilename $v`

3214 ## err type=file=$file

3215 [ -z "$file" ] && continue

3216 bn=`sqlquotestr "${file##*/}"`

3217 bin="X'"$(hexize "$file")"'"

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

3219 type=\"file:$ct\"

3220 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3221 cachedir=`getcachedir "$tbl/$rowid"`

3222 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3223 rm -rf $cachedir

3224 ;;

3225 *)

3226 newsql="update $tb2 set val=(select val from par where var \

3227 like '$col.%.$st_rowid')"

3228 ;;

3229 esac

3230 ;;

3231 *) # maybe "keep", do not modify value

3232 continue

3233 ;;

3234 esac

3235 # err newsql=$newsql

3236 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3237 done

3238

3239 if [ x"$bin" = x"NULL" ]; then

3240 ## err repl:normal sql=`echo $sql`

3241 query "$sql

3242 delete from $tb2 where type='string' and val='';"

3243 ## err repl:normal done

3244 else

3245 sqlfile="$tmpd/sqlf.$$"

3246 echo "$sql" > $sqlfile

3247 ## err repl:sqlfile=`ls -lF $sqlfile`

3248 query ".read $sqlfile"

3249 ## err repl:done

3250 fi

3251 # Rest of kt==m: set multiple mode

3252 nr=`getparcount $col`

3253 else

3254 nr=1 # for kt==s, number of records is 1

3255 fi

3256

3257 i=0

3258 while [ $i -lt $nr ]; do

3259 limit="limit 1 offset $i"

3260 i=$((i+1)) # increase beforehand against continue

3261 val=`getvalquote $tbl $col "$limit"`

3262 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3263 ## err $col=$val

3264 bin=NULL

3265 ## err partype$col=`getpartype $col "$limit"`

3266 case $ptype in

3267 file) file=$tmpd/`getparfilename $col "$limit"`

3268 ## err parfile-$col=$file

3269 [ -z "$file" ] && continue

3270 bin="X'"$(hexize "$file")"'"

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

3272 type=\"file:$ct\" ;;

3273 "*"*) continue ;; # foreign table

3274 *) type=\"string\" ;;

3275 esac

3276 case `gettbl_coltype $tbl/$col` in

3277 password) # special care for password

3278 # name={password,pswd1,pswd2}

3279 p1=`getpar pswd1 "$limit"`

3280 if [ -z "$p1" ]; then

3281 continue # SKIP password setting, if p1 is empty

3282 else

3283 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3284 ## err pswd=$pswd

3285 if pwcheck "$pswd"; then

3286 if [ x"$p1" = x"$p2" ]; then

3287 case "$p1" in

3288 ??????????*) ;;

3289 *) echo "パスワードは10字以上にしてください。" | html p

3290 return 6;;

3291 esac

3292 val="\"`echo $p1|mypwhash`\""

3293 else

3294 echo "2つの新パスワード不一致" | html p

3295 return 7

3296 fi

3297 else

3298 echo "旧パスワード違います" | html p

3299 return 8

3300 fi

3301 fi

3302 ;;

3303 esac

3304 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3305 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3306 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3307 if [ x"$bin" = x"NULL" ]; then

3308 ## err Normal-query: `echo $sql`

3309 query "$sql"

3310 else

3311 sqlfile="$tmpd/query.$$"

3312 echo "$sql" > $sqlfile

3313 ## err sqlfile=`ls -lF $sqlfile`

3314 query ".read $sqlfile"

3315 fi

3316 ## err p2t done

3317 done

3318 done

3319 done

3320 return 0

3321 ##err donee

3322 )

3323 genform() {

3324 # $1 = form definition file

3325 # $2, $3 (optional)= table name and ROWID

3326 # If $GF_VIEWONLY set and nonNull, output values without form

3327 # If $GF_ARGS set, use it as content-strings in the form

3328 # If $GF_OWNER set, use it as value of name="owner"

3329 # If $GF_STAGE set, use it as value of name="stage"

3330 forms="" hiddens="" rowid=$3

3331 if [ ! -e "$1" ]; then

3332 echo "そのようなデータベースはないようです($2)。" | html p

3333 return

3334 elif [ -n "$2" ]; then

3335 rec=`query "select * from $2 where rowid='$rowid';"`

3336 if [ -z "$rec" ]; then

3337 pk=`gettblpkey $2`

3338 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3339 rec=`query "select rowid from $2 where $pk='$rowid';"`

3340 rowid=$rec

3341 rec=$3

3342 fi

3343 if [ -z "$rec" ]; then

3344 echo "そんなレコードはないみたいね..." | html p

3345 return

3346 fi

3347 fi

3348 if [ -z "$GF_VIEWONLY" ]; then

3349 rm='<input id="rm" name="rm" type="checkbox"

3350 value="yes"><label for="rm">このエントリの削除</label>

3351 <span>ほんとうに消しますよ(確認)!

3352 <input name="confirm" type=checkbox value="yes">はい</span>'

3353 fi

3354 # Image Cache dir

3355 ## err genform: getcache=$2/$rowid

3356 td=`getcachedir "$2/$rowid"`

3357 while IFS=: read prompt name keytype type args; do

3358 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3359 sp="${args:+ }"

3360 form="" val=""

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

3362 # err genform2a: Seeking for "$2.$name, type=$type"

3363 rawval=`getvalbyid $2 $name $rowid $td`

3364 val=`echo "$rawval"|htmlescape`

3365 ## err genform3a: getvalbyid $2 $name $rowid $td

3366 ## err genform3b: val="[$val]" type="$type"

3367 fi

3368 if [ -n "$GF_VIEWONLY" ]; then

3369 is_hidden "$2" "$name" && continue

3370 fi

3371 case "$type" in

3372 text*)

3373 cgiform=cgi_multi_$type

3374 if [ -s $td/$name.count -a -n "$val" ]; then

3375 form=`$cgiform $name $td`

3376 val=$(echo "$val"|

3377 while read fn; do

3378 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3379 </td></tr>$nl"

3380 done)

3381 val="<table>$nl$val$nl</table>"

3382 else

3383 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3384 form=`cgi_$type $name "$rawval" "$args"`

3385 fi

3386 ;;

3387 [Rr][Aa][Dd][Ii][Oo])

3388 fh="<label><input type=\"radio\" name=\"$name\""

3389 form="`echo $args|sed -e \

3390 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3391 ;;

3392 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3393 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3394 ;;

3395 [Ss][Ee][Ll][Ee][Cc][Tt])

3396 fh="<select name=\"$name\">$nl"

3397 form=$(for l in $args; do

3398 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3399 done)

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

3401 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3402 fi

3403 form="$fh$form</select>"

3404 ;;

3405 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3406 if [ -s $td/$name.count ]; then

3407 form=`cgi_multi_file $name $td "$args"`

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

3409 hrfb="$myname?showattc+$2_m"

3410 val=$(echo "$rawval" \

3411 | while read fn; do

3412 data=`percenthex "$td/$fn"`

3413 #ct=`cat $td/$fn.content-type`

3414 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3415 ri=`cat "$td/$fn.rowid"`

3416 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3417 #imgsrc="<img src=\"data:$ct,$data\">"

3418 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3419 iconhref "$td/$fn" "$hrfb+$ri" ""

3420 done)

3421 fi

3422 else

3423 form="<input type=\"file\" name=\"$name\" $args>"

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

3425 imgs=$(echo "$rawval"\

3426 |while read fn;do

3427 data=`percenthex "$td/$fn"`

3428 echo "<img src=\"data:image/png,$data\">$fn<br>"

3429 done)

3430 form=$form"<br>$imgs"

3431 val=$imgs # 2015-06-15

3432 else

3433 form="<input type=\"file\" name=\"$name\" $args>"

3434 fi

3435 fi

3436 ;;

3437 [Hh][Ii][Dd][Dd][Ee][Nn])

3438 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3439 args="value=\"$GF_STAGE\""

3440 fi

3441 form="<input type=\"hidden\" name=\"$name\" $args>"

3442 prompt='' # Remove prompt

3443 ;;

3444 [Aa][Uu][Tt][Hh][Oo][Rr])

3445 [ -n "$GF_VIEWONLY" ] && continue

3446 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3447 prompt="" ;;

3448 [Oo][Ww][Nn][Ee][Rr])

3449 [ -n "$GF_VIEWONLY" ] && continue

3450 val=${GF_OWNER:-$val}

3451 val=${val:-$user}

3452 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3453 prompt="" ;;

3454 [Uu][Ss][Ee][Rr])

3455 # XXX: is null $user ok?

3456 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3457 [ -n "$GF_VIEWONLY" ] && continue

3458 form="$user"

3459 ;;

3460 [Pp]assword)

3461 [ -n "$GF_VIEWONLY" ] && continue

3462 form="`cgi_passwd`"

3463 val=""

3464 ;;

3465 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3466 [ -n "$GF_VIEWONLY" ] && continue

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

3468 val=`genserial`

3469 fi

3470 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3471 prompt="" ;;

3472 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3473 prompt=""

3474 ;;

3475 parent|path|blog*)

3476 prompt=""

3477 ;;

3478 "*"*)

3479 tail=$tail"``"

3480 continue ;;

3481 esac

3482 if [ -n "$prompt" ]; then

3483 if [ -n "${GF_VIEWONLY}" ]; then

3484 form=$val

3485 else

3486 :

3487 fi

3488 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3489 else

3490 hiddens=$hiddens$nl"$form"

3491 fi

3492 done < $1

3493 # enctype="multipart/form-data"

3494 cat<<EOF

3495 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3496 ${rowid:+$rm}

3497 <table class="b $2">

3498 $forms

3499 </table>$hiddens

3500 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3501 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3502 EOF

3503 if [ -z $GF_VIEWONLY ]; then

3504 cat<<EOF

3505 <input type="submit" name="sub" value="OK">

3506 <input type="reset" name="res" value="Reset">

3507 EOF

3508 fi

3509 cat<<EOF

3510 $GF_ARGS</form>

3511 $tail

3512 EOF

3513 }

3514 edittable() {

3515 # $1=form-def $2=table $3 rowid

3516 genform "$@"

3517 }

3518 viewtable() {

3519 GF_VIEWONLY=1 genform "$@"

3520 }

3521 showattc() {

3522 # $1=table_m $2=rowid &optional $3=RawFlag

3523 ## err \$1=$1 \$2=$2 \$3=$3

3524 if ! isfilereadable $user $1 $2; then

3525 contenttype; echo

3526 echo "このファイルは管理者のみしか見られません" | html p

3527 putfooter; exit

3528 fi

3529 idir=`umask 002; mktempd` || exit 1

3530 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3531 bin=$idir/$myname-$$.bin

3532 sql="select quote(bin) from $1 where rowid='$2';"

3533 ## err showattc: sql: $sql

3534 sq $db "$sql" | unhexize > $bin

3535 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3536 type=${tv%//*} fn=${tv#*//}

3537 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3538 ct=${type#file:}

3539 case $ct in # all text/* changed to text/plain

3540 text/*)

3541 charset=`nkf -g $bin|cut -d' ' -f1`

3542 case $charset in

3543 ASCII*) charset="" ;;

3544 esac

3545 if [ -z "$3" ]; then

3546 ct="text/html${charset:+; charset=$charset}"

3547 link="?showattc+$1+$2+raw"

3548 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3549 | sed 's,^,<span></span>,' \

3550 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3551 -D_LINK_="$link" \

3552 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3553 exit $?

3554 fi

3555 ct="text/plain${charset:+; charset=$charset}"

3556 ;;

3557 esac

3558 contenttype "$ct"

3559 echo "Content-Disposition: filename=\"$fn\""

3560 echo "Content-Length: " `cat $bin | wc -c`; echo

3561 #echo "Content-Type: " ${type#file:}; echo

3562 cat $bin

3563 }

3564 #

3565 # Some default stupid handler on CGI values

3566 #

3567 default_storedb() {

3568 # ARG: $1=table-def-file

3569 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3570 tbl=`basename $1`

3571 tbl=${tbl%.def}

3572 cols="`grep :text $1|cut -d: -f2`"

3573 col=`echo "$cols"|head -1`

3574 vcol=`getpar $col`

3575 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3576 if [ -n "$vcol" ]; then

3577 par2table $1

3578 else

3579 return 2 # No insertion occurred

3580 fi

3581 }

3582

3583 default_view() { # $1=def-file

3584 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3585 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3586 default_storedb "$@"

3587 query "select rowid from $tbl order by rowid desc;" \

3588 | while read rowid; do

3589 viewtable $1 $tbl $rowid

3590 done | _m4 -D_TITLE_="$tbl" \

3591 -D_FORM_="`genform $1`" \

3592 -D_DUMPTABLE_="syscmd(cat)" \

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

3594 }

3595 default_viewtext() { # $1=def-file

3596 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3597 default_storedb "$@"

3598 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3599 | _m4 -D_TITLE_="$tbl" \

3600 -D_FORM_="`genform $1`" \

3601 -D_DUMPTABLE_="syscmd(cat)" \

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

3603 }

3604 default_smail() {

3605 default_storedb "$@"

3606 if [ $? -eq 2 ]; then

3607 _m4 -D_TITLE_="入力" \

3608 -D_FORM_="`genform $1`" \

3609 -D_DUMPTABLE_="" \

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

3611 return

3612 fi

3613 cond=""

3614 for pk in `gettblpkey $tbl`; do

3615 pv=$(sqlquote "$(getpar $pk)")

3616 cond="$cond${cond:+ and }$pk=$pv"

3617 done

3618 sql="select rowid from $tbl where $cond;"

3619 rowid=`query "$sql"`

3620 ## err smail1 - "$sql" "-> rowid=$rowid"

3621

3622 while IFS=: read prompt name keytype type args; do # Read from $1

3623 val=`getpar $name`

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

3625 text="$text

3626 $prompt

3627 $name=$val

3628 ---------------------------------------------------------"

3629 fi

3630 case "$type" in

3631 image|document|file)

3632 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3633 fns=$(echo "$fn"|while read fn; do

3634 err mv $tmpd/$fn.orig $tmpd/$fn

3635 mv $tmpd/$fn.orig $tmpd/$fn

3636 rm $tmpd/$fn.rowid # Remove cache flag

3637 ## err "`ls $tmpd/$fn`"

3638 echo $fn

3639 done)

3640 files="$files $fns"

3641 ;;

3642 esac

3643 done < $1

3644 ## err FILES=$files "`ls -lF $tmpd`"

3645 subj="from ${REMOTE_ADDR}"

3646 (echo "$url"

3647 echo "への書き込みがありました。"

3648 echo "------"

3649 echo "$text"

3650 ) | (cd $tmpd &&

3651 err LS="`ls -lF`" &&

3652 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3653 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3654 echo "以下の内容で送信しました。" | html p

3655 viewtable $1 $tbl \

3656 `query "select rowid from $tbl order by rowid desc limit 1;"`

3657 echo "戻る" | html a "href=\"?\""

3658 }