s4: a868a34ca3bc s4-funcs.sh

s4

view s4-funcs.sh @ 422:a868a34ca3bc

Do not use article cache when user-icon is newer than cache.

author	HIROSE Yuuji <yuuji@gentei.org>
date	Thu, 16 Mar 2017 08:05:58 +0859
parents	e30fe590a53e
children	c250bc6da8ad

line source

1 #!/bin/sh

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

3 # $HGid$

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

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

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

9 myargs="$@"

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

11 tmpdir=${TMPDIR:-tmp}

12 dbdir=${DBDIR:-db}

13 tmpfiles=""

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

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

16 templ=${TEMPL:-templ}

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

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

19 imgdir=${IMGDIR:-img}

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

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

22 msgdir=$templ/msg

23 timeout="+2 days"

24 memoplimitdays="7"

25 dumpcollen=22

26 #thumbxy=120x120

27 thumbxy=96x96

28 iconxy_S=80x80

29 iconxy_M=400x400

30 maximagexy=1600x1600

31 ### maximagexy=400x400

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

33 blogreadflagrowid=0

34 querylog=$tmpdir/query.log

36 tconfs=""

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

38 conftbl=_tblconf

39 nl="

40 "

41 iconcachekey="profimgcache_S"

42 case "$HTTP_USER_AGENT" in

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

44 *) touchpanel="" ;;

45 esac

46 . ./s4-cgi.sh

48 : <<EOF

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

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

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

53 レポート提出管理

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

56 [2016]

57 7/12 根本への反省

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

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

61 [以下2015]

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

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

64 Web

65 締切設定

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

68 ○自分の提出物リスト

70 7/19 ○設置

71 ○一斉送信

72 ○getparfilename の tmpd の扱い

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

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

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

76 ○添付ファイル回収

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

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

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

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

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

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

84 ○レポート提出モード

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

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

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

90 7/9 ○管理者の追加

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

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

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

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

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

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

99 ○グループ検索

100

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

102

103

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

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

106

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

108 ○6/1 par2tableを triplex 対応に

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

110 →とすると一気に

111

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

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

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

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

116

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

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

119 ## 1. passwd

120 ## 2. grp

121 ## 3. grp_mem

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

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

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

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

126

127 ## ● listの定義:

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

129 ## ● hashの定義:

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

131

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

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

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

135 ## csv2sq3 で .csv.sq3 の Makefile

136

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

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

139 ## type := root | comment

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

141 ## unique(id, filename)

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

143

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

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

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

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

148 ## できたー!

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

150 ↓

151 ↓以下に変更

152 with recursive allmem as

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

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

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

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

157

158

159 with recursive allmem as

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

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

162 where allmem.val=grp_m.gname)

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

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

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

166

167

168 ## triggerもできた。

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

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

171

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

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

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

175 ## グループ管理

176 ## 個人情報管理

177 ##

178

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

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

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

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

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

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

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

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

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

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

189

190 ## ログテーブル

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

192

193

194

195 ■表設計

196 * 3つの表に分散管理

197 id格納表 + hash表 + list表

198 * *_s *_m

199

200

201

202 user, user_map, user_col

203

204 ■抽象エントリタイプ

205 * user

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

207 * group

208 権限判定に利用

209 * serial

210 自動idとして機能

211 * password

212 入力 type=passwordで入力

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

214 * session

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

216 * text

217 入力 type=text

218 * textarea

219 入力 textarea

220 * image|document

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

222 * owner

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

224 * gowner

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

226 * timestamp

227 datetime()

228 * parent

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

230 * path

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

232

233 格納タイプ

234 * list

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

236 * hash

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

238

239 オブジェクトタイプ

240 * entry

241 id, title, owner

242 * textpart

243 id, parentID, text

244 * binarypart

245 id, parentID, contenttype, filename, content

246 * content

247 hash(textpart), list(binarypart)

248 * topic

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

250 * reply

251 id, parentID, content

252 * blog

253 list(entry)

254 blog = [topic, list(reply)]

255

256

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

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

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

260 "author" => "taro",

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

262 "parent" => "/",

263 "path" => "/1",

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

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

266 {"serial" => 2,

267 "author" => "hanako",

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

269 "parent" => "/",

270 "path" => "/2",

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

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

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

274

275

276 user:=

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

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

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

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

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

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

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

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

285

286 変換表

287 /user/email=m

288

289 blog:=

290 シリアル:id:p:serial

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

292 所有者:owner:s:owner:

293 時刻:ctime:s:stamp:

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

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

296

297 article:=

298 シリアル:id:p:serial

299 筆者:author:s:owner

300 時刻:ctime:s:stamp:

301 参照元:parent:s:parent:

302 パス:path:s:path:

303 本文:text:s:textarea:

304 画像:image:m:image:

305

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

307

308

309 EOF

310

311 sq() {

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

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

314 }

315 dbsetup() {

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

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

318 sqi=$tmpdir/sqi.$$

319 sqo=$tmpdir/sqo.$$

320 mkfifo $sqi $sqo

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

322 sq $db < $sqi &

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

324 exec 2>> $tmpdir/error.out

325 exec 3>> $tmpdir/debug.out

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

327 rm $sqi

328 }

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

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

331 }

332 cleanup() {

333 trap '' INT HUP EXIT TERM PIPE

334 echo .quit >&5

335 kill $sq3pid

336 kill $sq3pid

337 rm -f $sqo $sqi

338 rm -rf $tmpfiles

339 cleanup2

340 }

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

342 # simple redirection for the sake of speed.

343 query() {

344 echo ".once $sqo" >&5

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

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

347 tee -a $querylog

348 else

349 echo "$@" >> $querylog

350 echo "$@"

351 fi >&5

352 cat $sqo

353 echo '>>>' >> $querylog

354 }

355 _m4() {

356 #_S4NAME_=f,f,f

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

358 }

359 ismember() {

360 # $1=user, $2=group

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

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

363 }

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

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

366 }

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

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

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

370 }

371 isgrpowner() (

372 # $1=user, $2=group

373 gn=`sqlquote "$2"`

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

375 err isgrpowner: $sql

376 test -n "`query $sql`"

377 )

378 getgroupadminmails() {

379 # $1=group

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

381 email4group "$1" "$i" ;

382 done

383 }

384 getgroupadmins() { # $1=group

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

386 qgrp=`sqlquote "$1"`

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

388 }

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

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

391 getvalbyid grp $2 \

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

393 }

394 getgroupbyid() {

395 # $1=id|gname

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

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

398 # err ggbyid: `echo $sql`

399 query $sql

400 }

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

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

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

404

405 # Return true when anonymous mode

406 [ "$anonymousmode" ] && return 0

407 # case `getvalbyid blog mode $2` in

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

409 # *closed*)

410 # owner=`getvalbyid blog owner $2`

411 # if isgrp $owner; then

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

413 # elif isuser $owner; then

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

415 # fi

416 # esac

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

418 sql="with getblog as (\

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

420 select blogid from article where id in\

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

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

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

424 select case\

425 when (select author from article where\

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

427 then 'author'\

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

429 then 'open'\

430 when (select val from getmode) is null \

431 then 'open'

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

433 then (select user from grp_adm where \

434 gname=(select val from getowner) and \

435 user='$1')\

436 when (select author from article where\

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

438 then 'user+author'

439 else '' end;"

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

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

442 result=`query "$sql"`

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

444 return 2

445 }

446 linkhome() {

447 # $1=UserOrGroup

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

449 if isuser $1; then

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

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

452 else

453 echo -n "grp+$1"

454 fi

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

456 }

457 hreflink() {

458 # s4 specific notation:

459 # ^href=URL

460 # ^iframe=URL

461 # OSM umap Wikistyle Notation:

462 # [[URL]] - Simle Link

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

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

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

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

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

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

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

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

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

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

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

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

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

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

477 }

478 minitbl() {

479 sed -n '

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

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

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

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

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

485 H; # Concat this line to HoldSpace

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

487 $ b cont

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

489 }

490 :cont

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

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

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

494 # . of ^. is workaround for FreeBSD sed

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

496 p; # Print whole "table" element

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

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

499 }

500 x; # Back to the newest line

501 p; # Print rest'

502 }

503 acclog() (

504 # $1=table, $2=rowid

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

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

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

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

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

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

511 fi

512 )

513 gecos() (

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

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

516 )

517 setpar() {

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

519 }

520 replpar() {

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

522 }

523 getpar() {

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

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

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

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

528 fi

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

530 case "$var" in

531 owner)

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

533 echo $user; return

534 elif ismember $user $val; then

535 echo $val; return

536 fi ;;

537 esac

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

539 echo "$val"

540 }

541

542 getpartype() {

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

544 }

545 getparcount() {

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

547 }

548 getparfilename() {

549 # null if type of $1 is not file

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

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

552 }

553 sqlquote() {

554 (v="$1"

555 case "$v" in

556 "") return ;; # null

557 "X'"*) # quoted hex string

558 echo $1 ;;

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

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

561 echo "\"$v\""

562 return ;;

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

564 echo "\"$v\""

565 return ;;

566 *)

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

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

569 else

570 echo "\"$v\""

571 fi ;;

572 esac)

573 }

574 sqlquotestr() (

575 case "$1" in

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

577 echo "'$v'" ;;

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

579 esac

580 )

581 mktempd() {

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

583 }

584 getcachedir() { # $1=maintable

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

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

587 else

588 echo $tmpd/$thumbxy

589 fi

590 }

591 getval() {

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

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

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

595 echo "$user" ;;

596 stamp|datetime)

597 date "+%F %T" ;;

598 serial)

599 (s=`getpar $2`

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

601 *)

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

603 esac

604 }

605

606 getvalquote() {

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

608 (v=`getval "$@"`

609 case "$v" in

610 "") echo NULL ;;

611 *) sqlquote "$v" ;;

612 esac)

613 }

614 getparquote() {

615 sqlquote `getpar $1`

616 }

617 getbinbyid() {

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

619

620 }

621 getvalbyid() {

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

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

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

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

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

627

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

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

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

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

632 return

633 fi

634 done

635 rowid=$3

636 pk=`gettblpkey $1`

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

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

639 td=${4:-$tmpd}

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

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

642 for kt in s m; do

643 t=${1}_$kt

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

645 vcount=1 # count(val)

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

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

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

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

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

651 if [ $kt = m ]; then

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

653 # Reset val to store filenames if type is string

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

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

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

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

658 i=0

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

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

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

662 i=$((i+1))

663 fn=$c.$i

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

665 case $type in

666 file:*)

667 #file=$td/$val

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

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

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

671 # FOR SPEED: Skip file generation if imgcache exists

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

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

674 && continue

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

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

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

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

679 select rowid from $cond $slice;

680 .output '$td/$fn'

681 select val from $cond $slice;

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

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

684 .output stdout

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

686 EOF

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

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

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

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

691 ls -lh "$file" |

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

693 case "$type" in

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

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

696 *|*:[Aa]pplication*)

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

698 png:- > "$file"

699 ;;

700 esac

701 ;;

702 *)

703 sq $db<<EOF

704 .output $td/$fn.rowid

705 select rowid from $cond $slice;

706 .output $td/$fn

707 select val from $cond $slice;

708 EOF

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

710 ;;

711 esac

712 done

713 else

714 rm -f $td/$c.count

715 case $type in

716 file:*)

717 echo "$val" \

718 | while read fn; do

719 file=$td/$fn

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

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

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

723 | unhexize > "$file"

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

725 case $type in

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

727 *:[Aa]pplication*)

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

729 png:- > $file ;;

730 esac

731 fi

732 done

733 ;;

734 esac

735 fi

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

737 return

738 fi

739 done

740 done)

741 }

742 getvalbypkey() (

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

744 pk=`gettblpkey $1`

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

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

747 )

748 getvalbycond() {

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

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

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

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

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

754 fi

755 }

756 getpwfield() {

757 # getpwfield user column

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

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

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

761 echo "$val"

762 return 0

763 else

764 return 1

765 fi

766 }

767 encode() {

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

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

770 sha1=sha1

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

772 sha1=sha1sum

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

774 sha1=gsha1sum

775 fi

776 fi

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

778 }

779 enjpeg() {

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

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

782 cjpeg="cjpeg"

783 else

784 cjpeg="convert - jpeg:-"

785 fi

786 fi

787 $cjpeg "$@"

788 }

789 mycrypt() (

790 key=$1 salt=$2

791 err \$2=$2

792 case $2 in

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

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

795 esac

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

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

798 )

799 hexize() {

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

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

802 hexize="xxd -p"

803 else

804 hexize_hd() {

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

806 }

807 hexize="hexize_hd"

808 fi

809 fi

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

811 }

812 unhexize() {

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

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

815 unhex="xxd -p -r"

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

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

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

819 EOF

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

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

822 # world writable...:)

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

824 fi

825 fi

826 cat "$@" | $unhex

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

828 }

829 percenthex() {

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

831 }

832 htmlescape() {

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

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

835 }

836 enascii() {

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

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

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

840 else

841 enascii_now=`date +%FT%T`

842 enascii_sed() {

843 nkf -Z0Z1Z2 \

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

845 }

846 enascii="enascii_sed"

847 fi

848 fi

849 cat "$@" | $enascii

850 }

851 size_h() {

852 i="$1" oi=$1

853 set -- B B KB MB GB TB

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

855 oi=$i

856 i=$((i/1024))

857 shift

858 done

859 echo ${oi}$1

860 }

861 gettblconf() {

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

863 ## tconfs=`sq $db \

864 tconfs=`query \

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

866 fi

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

868 }

869 gettblkeys() {

870 # $1=tbl

871 gettblconf

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

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

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

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

876 case $type in

877 *p*)

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

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

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

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

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

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

884 esac

885 case $type in

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

887 esac

888 done

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

890 }

891 gettblpkey() {

892 # $1=tbl

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

894 }

895 gettblfkey() {

896 (x=`gettblkeys $1`

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

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

899 }

900 gettblcols() {

901 (x=`gettblkeys $1`

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

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

904 }

905 gettbl_s_cols() {

906 (x=`gettblkeys $1`

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

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

909 }

910 gettbl_m_cols() {

911 (x=`gettblkeys $1`

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

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

914 }

915 gettbl_h_cols() {

916 (x=`gettblkeys $1`

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

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

919 }

920 gettbl_coltype() (

921 gettblconf

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

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

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

925 )

926 is_hidden() {

927 # $1=Tbl $2=col

928 gettblconf

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

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

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

932 case $x in

933 *h*) return 0 ;;

934 *) return 1 ;;

935 esac

936 }

937

938 dbsetbyid() {

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

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

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

942 quotedp=$(sqlquotestr "$p")

943 unset primary update

944 gettblconf

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

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

947 #err conf=$conf

948 case ${conf#*=} in

949 p*) primary=1 ;;

950 f*) update=1 ;;

951 u*) ;;

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

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

954 esac

955 #err t=$t

956 type=string fn=""

957 case $conf in

958 */password)

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

960 ;;

961 */image*|*/document*)

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

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

964 ;;

965 esac

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

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

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

969 if [ "$primary" ]; then

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

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

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

973 elif [ "$update" ]; then

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

975 else

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

977 fi

978 )

979 }

980 expire() (

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

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

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

984 )

985 addsession() {

986 # expireをセット

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

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

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

990 # Remove old session parameters

991 now=`expire now`

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

993 }

994 gencookie() (

995 for kv; do

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

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

998 done

999 )

1000 contenttype() {

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

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

1003 }

1004 putheader() {

1005

1006 }

1007 putfooter() {

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

1009 }

1010 getcookie() (

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

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

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

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

1015 done

1016 )

1017 genrandom() {

1018 # $1=columns (default: 10)

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

1020 }

1021 genserial() {

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

1023 }

1024 smail() {

1025 # smail rcpts subj (file)

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

1027 # $MAIL_FROM <- From: header value

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

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

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

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

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

1033 }

1034 setviastring() {

1035 table=$1

1036 oifs="$IFS"

1037 IFS="&"

1038 for us in $2; do

1039 k=${us%%=*}

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

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

1042 #echo $k=$v

1043 done

1044 IFS="$oifs"

1045 }

1046 checkdomain() (

1047 # Check the validity of domain by referring DNS

1048 item=$1

1049 err checkdomain $1

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

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

1052 )

1053 pwcheck() {

1054 # $1=passwd

1055 dbpswd=`getpwfield $user pswd`

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

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

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

1059 }

1060 mypwhash() {

1061 mycrypt `cat` `genrandom 5`

1062 }

1063 wasureta() {

1064 user=$1

1065 if ! checkdomain $user; then

1066 contenttype; echo

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

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

1069 putfooter

1070 exit 0

1071 fi

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

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

1074 encpswd=`echo $newpswd|mypwhash`

1075 dbsetbyid user $user pswd "$encpswd"

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

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

1078 $msgdir/mail-newaccount.m4 \

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

1080 | smail $user "New Account"

1081 }

1082 checkauth() {

1083 user=`getpar user`

1084 skc=`getpar skey` # from cookie

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

1086 skey="`getpwfield $user skey`"

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

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

1089 return 0

1090 fi

1091 fi

1092 pswd=`getpar pswd`

1093 quser=`sqlquotestr "$user"`

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

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

1096 return 1

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

1098 wasureta $user

1099 return 1 # wasureta error

1100 fi

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

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

1103 if pwcheck "$pswd"; then

1104 newsession=`genrandom 50`

1105 dbsetbyid user $user skey "$newsession"

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

1107 return 0

1108 fi

1109 return 2 # Password mismatch

1110 }

1111 showlogin() {

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

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

1114 $layout/login.m4.html

1115 exit 0

1116 }

1117 dologin() {

1118 checkauth

1119 st=$?

1120 if [ $st != 0 ]; then

1121 contenttype; echo

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

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

1124 showlogin # and EXIT

1125 fi

1126 }

1127

1128 # Do instant jobs here

1129 dbsetup

1130 trap cleanup INT HUP EXIT TERM PIPE

1131 # trap cleanup INT HUP

1132

1133 err() {

1134 echo "$@" 1>&3

1135 }

1136

1137 cgiinit() {

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

1139 tmpf=tmp/stream

1140 tmpd=`tmpd=$tmpdir mktempd`

1141 tmpfiles=$tmpfiles" $tmpd"

1142 addsession $session

1143 getcookie

1144 case "$REQUEST_METHOD" in

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

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

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

1148 # cat > $tmpf # too much?

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

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

1151 s="`cat tmp/stream`"

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

1153 ;;

1154 esac

1155 case "$CONTENT_TYPE" in

1156 *boundary*)

1157 bndry=${CONTENT_TYPE#*boundary=}

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

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

1160 do

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

1162 #echo u=$us

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

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

1165 # err k=$k v=$v

1166 case "$k" in

1167 *:filename)

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

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

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

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

1172 :

1173 fi

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

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

1176 [Ii]mage/x-xcf)

1177 bzip2 "$tmpd/$v"

1178 v=${v}.bz2

1179 ;;

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

1181 [Ii]mage/*)

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

1183 ;;

1184 esac

1185 ;;

1186 *)

1187 type='string'

1188 ;;

1189 esac

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

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

1192 done

1193 ;;

1194 *)

1195 setviastring par "$s"

1196 ;;

1197 esac

1198 }

1199 email4group() {

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

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

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

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

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

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

1206 and s.key='email'

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

1208 query "$sql"

1209 }

1210 email4groupbyuid() {

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

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

1213 uids=`echo "$@"`

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

1215 sql="WITH

1216 grpemails AS (

1217 SELECT gname, user, val email

1218 FROM grp_mem NATURAL JOIN grp_mem_s

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

1220 useremails AS (

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

1222 FROM user

1223 LEFT JOIN user_s

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

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

1226 FROM useremails u LEFT JOIN grpemails g

1227 ON u.name=g.user

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

1229 ## err email4gByid `echo $sql`

1230 query "$sql"

1231 }

1232 collectemail() (

1233 # Collect email addresses for group $1

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

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

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

1237 for e; do

1238 if isuser "$e"; then

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

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

1241 else

1242 qgrp=`sqlquote "$e"`

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

1244 gmem="grp_mem"

1245 else

1246 tm=`sqlquote "$TEAM"`

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

1248 fi

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

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

1251 $gmem g left join grp_mem_s s

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

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

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

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

1256 query "$sql"

1257 fi

1258 done

1259 )

1260 sendinvitation() (

1261 # $1=email

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

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

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

1265 gecos=`gecos`

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

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

1268 _m4 -D_URL_="$urlbase" \

1269 -D_USER_="$name" \

1270 -D_EMAIL_="$1" \

1271 -D_REGIST_="$regist" \

1272 -D_ADMIN_="$admin" \

1273 $msgdir/mail-invite.m4 \

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

1275 return 0

1276 )

1277 emaildomaincheck() {

1278 case "$1" in

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

1280 *@*)

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

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

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

1284 return 2

1285 fi

1286 return 0

1287 ;;

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

1289 esac

1290 }

1291 invite() {

1292 email=`getpar email`

1293 case $email in

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

1295 *@*)

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

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

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

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

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

1301 elif sendinvitation $email; then

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

1303 fi ;;

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

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

1306 esac

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

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

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

1310 fi

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

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

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

1314 }

1315 regist() {

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

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

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

1319 echo "bye bye" | html p

1320 reutrn

1321 fi

1322 email=`session=$1 getpar invite`

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

1324 cat<<EOF

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

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

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

1328 EOF

1329 return

1330 fi

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

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

1333 # Fake login password to wasureta

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

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

1336 wasureta $email

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

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

1339 addsession $1 # for removal after 1 minute

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

1341 $layout/login.m4.html

1342 return

1343 }

1344 group_safename() {

1345 # Convert $1 to safe group name

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

1347 }

1348 groupupdate() {

1349 gname=`getpar gname`

1350 qgname=`sqlquote $gname`

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

1352 # See ALSO same job in showgroup()

1353 newgname=`group_safename "$gname"`

1354 err newgname=$newgname

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

1356 err NewGNAME: gname=$newgname

1357 gname=$newgname

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

1359 replpar gname string "$gname"

1360 fi

1361 # Name confliction check

1362 parow=`getpar rowid`

1363 ## err parow=$parow

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

1365 query "BEGIN EXCLUSIVE;"

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

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

1368 if [ $count -gt 0 ]; then

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

1370 query "END;"

1371 return

1372 fi

1373 par2table $formdir/grp.def

1374 query "END TRANSACTION;"

1375 # Remove orphan

1376 : <<EOF

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

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

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

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

1381 EOF

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

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

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

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

1386 sql="delete from blog where id in

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

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

1389 query "$sql";

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

1391 return # and return

1392 fi

1393 fi

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

1395 fi

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

1397 grid=$(query $sql)

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

1399 grp $grid

1400 }

1401 groupclone() {

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

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

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

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

1406 return

1407 fi

1408 i=0

1409 while true; do

1410 copy="-copy$i"

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

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

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

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

1415 i=$((i++))

1416 continue

1417 fi

1418 break

1419 done

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

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

1422 query<<-EOF

1423 BEGIN;

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

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

1426 SELECT $newqname, key, val

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

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

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

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

1431 -- Copy members and their configuration --

1432 REPLACE INTO grp_mem SELECT $newqname, user

1433 FROM grp_mem WHERE gname=$qgrp;

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

1435 FROM grp_mem_s WHERE gname=$qgrp;

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

1437 FROM grp_mem_m WHERE gname=$qgrp;

1438 -- Copy administrators --

1439 REPLACE INTO grp_adm SELECT $newqname, user

1440 FROM grp_adm WHERE gname=$qgrp;

1441 COMMIT;

1442 EOF

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

1444 STOPCLONEMSG=1 groupconf "$newrowid"

1445 }

1446 groupman() {

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

1448

1449 GF_STAGE="grpconf"

1450 GF_STAGE=groupupdate

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

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

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

1454 -D_DUMPTABLE_="syscmd(cat)" \

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

1456 }

1457 userconf() {

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

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

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

1461 }

1462 groupconf() {

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

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

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

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

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

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

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

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

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

1472 | html p

1473 fi

1474 }

1475 mems() {

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

1477 kwd=`getpar kwd`

1478 listmember $kwd

1479 }

1480 grps() {

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

1482 kwd=`getpar kwd`

1483 listgroup $kwd \

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

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

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

1487 $layout/form+dump.m4.html

1488 }

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

1490 gpg=`getpar grp`

1491 grid=${1:-$gpg}

1492 grp=`getgroupbyid "$grid"`

1493 ## . ./s4-blog.sh

1494 jg=`getpar joingrp`

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

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

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

1498 fi

1499 htmlheader=$layout/html.m4.html

1500 showgroup "$grid"

1501 }

1502 sql4interestblogs() {

1503 cat<<EOF

1504 CREATE TEMPORARY VIEW interestblogs AS

1505 SELECT blog.rowid rid, id, author

1506 FROM blog

1507 NATURAL JOIN

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

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

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

1511 WHEN (SELECT user FROM grp_mem

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

1513 THEN 0

1514 ELSE 1

1515 END;

1516 EOF

1517 }

1518 listnewblogsql() { # $1=user

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

1520 WHERE user='$user'

1521 AND tblrowid=$blogreadflagrowid),

1522 "0");"`

1523 cat<<EOF

1524 `sql4interestblogs`

1525 WITH article_ctime as (

1526 SELECT id,blogid,author,val ctime

1527 FROM article join article_s s using(id)

1528 WHERE s.key='ctime'

1529 ), blog_title_owner as (

1530 SELECT blg.rid brid, id,

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

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

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

1534 ), visited AS (

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

1536 FROM blog_title_owner b, article_ctime ac

1537 JOIN acclog al

1538 ON b.id=ac.blogid

1539 AND al.tbl='blog'

1540 AND al.tblrowid=brid

1541 AND al.user='$user'

1542 AND al.time < ctime

1543 AND '$deftime' < ctime

1544 ), unvisited as (

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

1546 FROM blog_title_owner b, article_ctime ac

1547 ON b.id=ac.blogid

1548 WHERE brid NOT IN (SELECT tblrowid FROM acclog

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

1550 AND ctime > '$deftime'

1551 ), blogall as (

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

1553 Collect new articles with dividing them into visited and unvisited

1554 separately, because constructing joined table of artice X acclog

1555 tends to become HUGE combinations.

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

1557 SELECT * FROM visited UNION SELECT * FROM unvisited

1558 ), news as (

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

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

1561 from blogall bl /* left join a_u l

1562 on bl.brid=l.tblrowid */

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

1564 LIMIT 10

1565 ) SELECT brid LINK, "新着",

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

1567 ctime, title,

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

1569 FROM news;

1570 EOF

1571 }

1572

1573 search_form() {

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

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

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

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

1578 @week → 最近一週間

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

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

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

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

1583 auth=""

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

1585 case "$1" in

1586 author=*)

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

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

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

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

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

1592 ;;

1593 grp=*)

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

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

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

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

1598 ;;

1599 esac

1600 cat<<-EOF

1601 <div class="right">

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

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

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

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

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

1607 </form>

1608 </div>

1609 EOF

1610 }

1611

1612 imgsrc_cache() (

1613 # $1 = directory for cache'ing

1614 # $2 = table (user_m or grp_m)

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

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

1617 dir="$1" tbl="$2"

1618 keycond="$3"

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

1620 ORDER BY rowid DESC LIMIT 1"

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

1622 tmpf=$tmpd/imgsrc_cache.$$

1623 case "$4" in

1624 [Ss]) size=S ;;

1625 [Oo]) size=O ;;

1626 *) size=M ;;

1627 esac

1628 # ImageCache filename storing schema:

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

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

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

1632 valtype=`query "$sql0"`

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

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

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

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

1637 case "$filetype" in

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

1639 esac

1640 fi

1641 cacheimg_S=$dir/S_$filename

1642 cacheimg_M=$dir/M_$filename

1643 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1651 else

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

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

1654 fifo2=$fifo.2

1655 mkfifo $fifo $fifo2

1656 fmt=${filename##*.}

1657 ## [[ NOTE ]]

1658 ## a. convert oldimage newimage

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

1660 ## b is much smaller than a

1661 cat $tmpf | unhexize \

1662 | tee $fifo \

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

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

1665 | tee $fifo2 \

1666 | convert - "$cacheimg_M" &

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

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

1669 | convert - "$cacheimg_S" &

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

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

1672 echo '">'

1673 echo "$sum" > $sumfile

1674 fi

1675 ## Now preparing cache image, done.

1676 ## Store this information to DB

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

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

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

1680 query <<-EOF

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

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

1683 EOF

1684 )

1685

1686 showhome() {

1687 # $1=userRowIdToShow

1688 err showhome \$1=$1

1689 case "$1" in

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

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

1692 esac

1693 ## err ShowHome: uname=$uname

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

1695 gecos=`gecos "$uname"`

1696 ## err SH:gecos=$gecos

1697 GF_VIEWONLY=1

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

1699 search_form_args=""

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

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

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

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

1704 # Display folders

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

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

1707 and type like 'file:%';"

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

1709 nfile=`query "$sql"`

1710 # err nfile=$nfile

1711 if [ $nfile -gt 0 ]; then

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

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

1714 fi

1715 else

1716 search_form_args="author=$uname"

1717 fi

1718 . ./s4-blog.sh

1719

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

1721 search_form $search_form_args > $sf

1722 echo "$gecos さん" > $tf

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

1724 viewtable $formdir/user.def user $1

1725 echo "</div>"

1726 } > $pf

1727

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

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

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

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

1732 if true; then

1733 tbl=user_m

1734 enticond="name='$uname'"

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

1736 else

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

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

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

1740 echo '">'

1741 }

1742 fi > $imf

1743 fi

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

1745 val='$uname';"`

1746 listblog $uname > $bf

1747

1748 hometail=$tmpd/tail.$$

1749 mkfifo $hometail

1750

1751 #Calling listgroupbytable, originally here

1752

1753 (

1754 # Display Most Recent Entry

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

1756 shortval=${shortval:-val}

1757

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

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

1760 ctime,

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

1762 (SELECT gecos FROM gecoses

1763 WHERE name=(SELECT val FROM blog_s

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

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

1766 FROM blog b

1767 JOIN

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

1769 FROM article a, article_s s

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

1771 GROUP BY blogid ORDER BY val DESC LIMIT 50

1772 ) m

1773 ON b.id=m.blogid;"

1774 # This should be as follows

1775 : <<EOF

1776 WITH arts AS(

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

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

1779 FROM article a NATURAL JOIN article_s s

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

1781 GROUP by s.id

1782 )

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

1784 FROM arts a0

1785 JOIN

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

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

1788 ORDER BY ctime DESC LIMIT 50;

1789 EOF

1790

1791 cat<<-EOF

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

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

1794 <div class="lcto">

1795 `DT_VIEW=replyblog dumptable html blog`

1796 </div>

1797 EOF

1798 unset DT_SQL

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

1800 # Display NEWS

1801 # 2016-06-26

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

1803 acclog blog $blogreadflagrowid

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

1805 fi

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

1807 sql=`listnewblogsql "$user"`

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

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

1810 cont=$((cont-1))

1811 err newcount=$cont

1812 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

1819 `cgi_submit '確定'`

1820 `cgi_hidden read yes`

1821 EOF

1822 echo "$new10 "

1823 echo "</div>"

1824 fi

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

1826 : # DT_SQL=

1827 fi

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

1829 #

1830 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

1836 -D_NBLOG_="$nblog" \

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

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

1839 $layout/html.m4.html $layout/home.m4.html

1840

1841 # Record access log

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

1843 }

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

1845 contenttype; echo

1846 ## err commission: "$@"

1847 gname=`getgroupbyid $1`

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

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

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

1851 grp_reg_adm "$@"

1852 else

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

1854 fi

1855 }

1856 listgroupbytable() {

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

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

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

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

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

1862 NGsql="select distinct tag from\

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

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

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

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

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

1868 for tag in `query "$sql"`

1869 do

1870 ## err ListGrp: tag=$tag

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

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

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

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

1875 as rowid,

1876 gname,

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

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

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

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

1881 ## err PersonalGroupList= `echo $sql`

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

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

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

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

1886 echo '</table>'

1887 done

1888 echo '</div>'

1889 }

1890 iconhref() (

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

1892 data=`percenthex "$1"`

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

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

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

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

1897 )

1898 iconhref2() (

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

1900 src=$1

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

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

1903 )

1904 listentry() (

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

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

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

1908 cond='' hiddens=''

1909 offset=`getpar offset`

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

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

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

1913 limit=30

1914 dir=`getcachedir "$1"`

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

1916 hrb="$myname?home"

1917 deficon=person-default.png

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

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

1920 gcs=gecos

1921 else # if group

1922 hrb="$myname?grp"

1923 deficon=group-default.png

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

1925 gcs=name

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

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

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

1929 ## err tagconv=$tagconv

1930 fi

1931 fi

1932 if [ ! -d $dir ]; then

1933 mkdir -p $dir

1934 fi

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

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

1937 fi

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

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

1940 fi

1941

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

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

1944 ##qgrp=`sqlquote $grp`

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

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

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

1948 quote(a.$nm) as qname,

1949 (SELECT val FROM ${tbl}_s

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

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

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

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

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

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

1956 end

1957 as name,

1958 b.tag,

1959 case when a.$nm in (select user from grp_adm

1960 where gname=$getgrp) then '(管理者)'

1961 when '$user' in (select user from grp_adm where gname=a.$nm)

1962 then '(ADMIN)'

1963 when '$user' in (select user from grp_mem where gname=a.$nm)

1964 then '(Member)'

1965 when '$iamowner' = '' then ''

1966 else ',not='||a.rowid end as ownerlink,

1967 CASE '$entity'

1968 WHEN 'グループ'

1969 THEN coalesce(

1970 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

1971 'open')

1972 ||

1973 CASE WHEN '$user'

1974 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

1975 THEN ' ismember'

1976 ELSE ''

1977 END

1978 ELSE 'user'

1979 END regmode

1980 from $tbl a left join

1981 (select $nm as name,

1982 max(case key when 'gecos' then val end) as gecos,

1983 max(case key when 'tag' then val end) as tag,

1984 max(case key when 'mtime' then val end) as mtime,

1985 max(case key when 'wtime' then val end) as wtime

1986 from ${tbl}_s group by $nm)

1987 b on a.$nm=b.name $cond $3

1988 order by b.tag desc, b.wtime desc, b.mtime desc, a.rowid asc"

1989 # Give precedence to newer maintained groups (2016-09-24)

1990 # Note that mtime is stored only in grp_s.

1991 ## err LE:sql.1="$sql"

1992 total=`query "with x as ($sql) select count(*) from x;"`

1993 echo "${entity} 一覧" | html h2

1994 if [ $total -gt $limit ]; then

1995 echo '<div class="right">'

1996 cgi_form $stage <<EOF

1997 <label>次の語を含む${entity}で検索:

1998 `cgi_text kwd $kwd`</label>

1999 EOF

2000 echo '</div>'

2001 fi

2002 hiddens="$hiddens

2003 `cgi_hidden kwd \"$kwd\"`

2004 `cgi_hidden stage \"$stage\"`"

2005 cat<<EOF

2006 <p>${total}件中の$((offset+1))件めから${kwd:+" - 検索語: $kwd"}</p>

2007 EOF

2008 if [ $((offset+limit)) -lt $total ]; then

2009 nextbtn=$(

2010 cat<<EOF

2011 <div class="right clear"><form action="$myname" method="POST">

2012 `cgi_submit 次の${limit}件`

2013 $hiddens

2014 `cgi_hidden offset $((offset + limit))`</form></div>

2015 EOF

2016 )

2017 fi

2018 if [ $offset -gt 0 ]; then

2019 prevbtn=$(

2020 cat<<EOF

2021 <form action="$myname" method="POST">

2022 `cgi_submit 前の${limit}件`

2023 $hiddens

2024 `cgi_hidden offset $((offset - limit))`</form>

2025 EOF

2026 )

2027 fi

2028 pnbtn="$nextbtn$prevbtn"

2029 echo $pnbtn

2030

2031 ## err ListEntry: `echo "$sql"\;`

2032 # sq $db here??? 2016-11-28

2033 query "$sql limit $limit ${offset:+offset $offset};" \

2034 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2035 err name=$name owner=$ownerp lnk=$lnk

2036 err newlnk=$lnk regmode=$regmode

2037 icondir=$dir/$id

2038 # Pick up only last icon

2039 echo "<div class=\"iconlist xy$thumbxy $type\">

2040 <p class=\"tag _$tag\">$tag</p>" \

2041 | _m4 $tagconv

2042 if [ -n "$NOSPEEDUP" ]; then

2043 files=`getvalbyid $tbl profimg $id $icondir`

2044 if [ -n "$files" ]; then

2045 icon=`echo "$files"|tail -1`

2046 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2047 else

2048 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2049 fi

2050 elif [ -n "$icon" -a -s "$icon" ]; then

2051 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2052 else

2053 cond="$nm=$qname"

2054 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2055 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2056 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2057 # err "img=[$img]"

2058 if [ -n "$img" ]; then

2059 echo "<a href=\"$hrb+$lnk\">"

2060 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2061 echo "</a>"

2062 else

2063 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2064 fi

2065 fi

2066 echo "<br>$name${ownerp:+<br>$ownerp}"

2067 echo "</div>"

2068 done

2069 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2070 )

2071 listmember() {

2072 listentry user "$@"

2073 }

2074 listgroup() {

2075 listentry group "$@"

2076 }

2077 hexteams() { # $1=gname, $2(optional)=user

2078 cond=${2:+" AND user='$2'"}

2079 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2080 WHERE gname='$1' AND key='team'$cond;"

2081 }

2082 showgroup() { # $1=group-rowid

2083 if [ -z "$1" ]; then

2084 grid=`getpar grid`

2085 grid=${grid%%[!0-9]*}

2086 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2087 else

2088 grid=$1

2089 fi

2090 grp=`getgroupbyid $grid`

2091 qgrp=`sqlquote $grp`

2092 ## err showgroup2: grp=$grp qgrp="[$(sqlquote $grp)]"

2093 if isgroup "$grp"; then

2094 tf=$tmpd/title.$$

2095 sf=$tmpd/search.$$

2096 bodyclass=`query "SELECT val FROM grp_s

2097 WHERE gname=$qgrp AND key='regmode';"`

2098 if ismember "$user" "$grp"; then

2099 ismember="ismember"

2100 qgrp=`sqlquote $grp`

2101 bodyclass="$bodyclass${bodyclass:+ }ismember"

2102 else

2103 ismember="" # bodyclass="group"

2104 fi

2105 bodyclass="$bodyclass grouphome"

2106 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2107 echo "グループ $grp" > $tf

2108

2109 showgroupsub $formdir/grp.def "$grid" | \

2110 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2111 -D_FORM_="syscmd(\`cat')" \

2112 -D_BODYCLASS_="$bodyclass" \

2113 -D_DUMPTABLE_="" \

2114 $htmlheader $sf $layout/form+dump.m4.html

2115 # $htmlheader $layout/form+dump.m4.html

2116 # $htmlheader is defined in grp()

2117 else # if $grp is removed at par2table

2118 listgroup

2119 fi

2120 }

2121 showgroupsub() {

2122 # $1=def-file $2=group-rowid

2123 # Using $ismember

2124 rowid=$2

2125 grp=`getgroupbyid $2`

2126 qgrp=`sqlquote $grp`

2127 td=`getcachedir grp/"$2"`

2128 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2129 if [ -z "$rowid" ]; then

2130 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2131 #grp=`sq $db "select gname from grp where rowid=$grp"`

2132 echo "showgroupsub: invalid argument($1 $2)" | html p

2133 return

2134 fi

2135 val=`getvalbyid grp profimg $rowid $tmpd`

2136 enticond="gname=$qgrp"

2137 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2138 if [ -n "$img" ]; then

2139 cat<<-EOF

2140 <p class="groupimg">

2141 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2142 EOF

2143 fi

2144 echo "<div class=\"noprofimg\">"

2145 viewtable $1 grp $rowid

2146 echo "</div>"

2147 if isgrpowner "$user" "$grp"; then

2148 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2149 iamowner=$rowid

2150 colmd=" mode"

2151 fi

2152 if [ -n "$ismember" ]; then

2153 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2154 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2155 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2156 cat<<EOF

2157 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2158 <div class="fold clear">

2159 `cgi_checkbox send yes id="send"`<label

2160 for="send">グループ全員にメッセージ送信</label>

2161 <div>

2162 `cgi_textarea message "" "cols=60"`

2163 `cgi_submit 送信`

2164 `cgi_reset リセット`

2165 </div>

2166 `cgi_hidden grp $rowid`

2167 </div></form>

2168 EOF

2169 fi

2170 # 加入ボタン + 加入者リスト

2171 if [ -n "$ismember" ]; then

2172 ismem='checked' state="(参加中)"

2173 else

2174 nomem='checked' state="(現在非加入)"

2175 fi

2176 # このグループでの加入アドレス

2177 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2178 and key='email';"`

2179 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2180 ## and key='email';"

2181 ##err email=$eml

2182 cat <<EOF

2183 <div class="fold clear">

2184 `cgi_checkbox reg yes id="reg"`<label

2185 for="reg">自身の加入状態を操作する</label>$state

2186 <div>

2187 EOF

2188 cgi_form grp <<EOF

2189 <p>このグループに</p>

2190 <table class="b">

2191 <tr><th>メンバーとして</th><td>

2192 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2193 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2194 <tr><th>参加する場合のメイルアドレス<br>

2195 <small>(メインのアドレスとは違うものにする場合に記入<br>

2196 同じでよい場合は空欄に)</small></th>

2197 <td>`cgi_text email $eml`</td></tr>

2198 </table>

2199 `cgi_hidden grp $rowid`

2200 EOF

2201 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2202 echo "moderated (承認加入の)グループなので実際に参加できるのは

2203 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2204 fi

2205 echo '</div></div>'

2206 echo '<h2>話題一覧</h2>'

2207 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2208 cat<<-EOF

2209 <form class="summary" action="$myname" title="$thelp">

2210 `cgi_hidden owner "$grp"`

2211 `cgi_hidden kwd "@week"`

2212 `cgi_hidden stage searchart`

2213 `cgi_submit "一週間のまとめ"`

2214 </form>

2215 EOF

2216 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2217 colstate="state:稼動状態:frozen=rowclass=凍結"

2218 DT_CHLD=article:blogid \

2219 DT_VIEW=replyblog dumptable html blog \

2220 "ctime team title heading$colmd $colstate" "$cond"

2221

2222 getgname="(select gname from grp where rowid=$rowid)"

2223 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2224 cm="?commission+$rowid"

2225 thumbxy=50x50 listmember "" "$c" "$rowid" \

2226 |sed -e "s|$<br>$,not=$.*$|\1|" # 間違って押しやすい

2227 # team list

2228 hexteams=`hexteams "$grp"`

2229 if [ -n "$hexteams" ]; then

2230 echo "チーム一覧" | html h2

2231 echo '<div class="dumptable"><table class="b">'

2232 sq $db -html -header<<-EOF

2233 SELECT val TEAM,

2234 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2235 MEMBERS

2236 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2237 EOF

2238 echo '</table></div>'

2239 fi

2240 }

2241 grp_getbodyclass() {

2242 # Get css class name for document.

2243 # `moderated' for moderated groups

2244 # `ismember' for groups where user belongs

2245 # $1=GroupName (w/o quote)

2246 # $user=userNameCurrentlyLogin

2247 ## err grp_getbodyclass: 1="$1"

2248 qgrp=`sqlquote "$1"`

2249 query<<-EOF

2250 SELECT coalesce(

2251 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2252 'open')

2253 ||

2254 CASE WHEN '$user'

2255 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2256 THEN ' ismember'

2257 ELSE ''

2258 END;

2259 EOF

2260 }

2261 grpaction() { # $1=group-rowid

2262 err GRP_ACTION:IN

2263 grid=${1:-`getpar grp`}

2264 grp=`getgroupbyid "$grid"`

2265 if [ -z "$grp" ]; then

2266 echo "無効な指定です。" | html p; return

2267 fi

2268 if ! ismember $user $grp; then

2269 echo "加入者のみに許可された操作です。" | html p; return

2270 fi

2271 echo "グループ $grp 個別選択操作" \

2272 | _m4 -D_TITLE_="syscmd(\`cat')" \

2273 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2274 $layout/html.m4.html

2275

2276 isowner=""

2277 isgrpowner "$user" "$grp" && isowner="yes"

2278 usel=`getpar usel`

2279 if [ -n "$usel" ]; then

2280 uids=$(echo `echo $usel`|tr ' ' ',')

2281 ## err grpaction-1: grp=$grp, `echo $sql`

2282 text=`getpar text`

2283

2284 rm=`getpar rm` cfm=`getpar confirm`

2285 ## err rm=$rm cfm=$cfm

2286 if [ x"$rm" = x"yes" ]; then

2287 if [ "$isowner" ]; then

2288 if [ x"$rm$cfm" = x"yesyes" ]; then

2289 # Eliminate

2290 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2291 for tbl in grp_mem grp_mem_s grp_mem_m; do

2292 sql="delete from $tbl $cond;"

2293 # echo "sql=$sql"

2294 query "$sql"

2295 err rmGRPuser "$sql"

2296 done

2297 num=`query "select count(*) from user where rowid in ($uids);"`

2298 #err num=$num

2299 if [ 0$num -gt 0 ]; then

2300 sql="select coalesce(b.val,a.name) from user a left join \

2301 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2302 # err `echo "$sql"`

2303 html pre<<EOF

2304 以下の${num}名のグループ $grp 登録を解除しました。

2305 `query "$sql"`

2306 EOF

2307 fi

2308 else

2309 echo "確認のチェックがないのでやめておきます。" | html p

2310 return

2311 fi

2312 else # not Group Owner

2313 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2314 return

2315 fi

2316 cat<<EOF

2317

2318 EOF

2319 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2320 if [ -z "$text" ]; then # if msg is empty

2321 echo "なにかメッセージを..." | html p

2322 return 0

2323 fi

2324 gecos=`gecos $user`

2325 mkfrom=`getpar mkfrom`

2326 if [ x"$mkfrom" = x"yes" ]; then

2327 safegc=`echo "$gecos" | tr -d '<>@'`

2328 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2329 fromad=`email4groupbyuid "$grp" "$myuid"`

2330 mail_from="$safegc <$fromad>"

2331 else

2332 mail_from="$admin"

2333 fi

2334 MAIL_FROM=$mail_from \

2335 smail "`email4groupbyuid "$grp" $usel` $user" \

2336 "$gecos さんからのメッセージ" <<EOF

2337 $url

2338 のグループ「$grp」のメンバーである $gecos さんから、

2339 あなた宛へのメッセージです。

2340 ----------------------------------------------------------

2341 $text

2342 EOF

2343 if [ $? = 0 ]; then

2344 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2345 sql="select coalesce(b.val, a.name) from

2346 (select name from user where rowid in ($uids)) a

2347 left join user_s b on a.name=b.name and b.key='gecos';"

2348 html pre<<EOF

2349 `query "$sql"`

2350 (送信者である $gecos さんも含まれます)

2351 EOF

2352 err SendDone: `echo $sql`

2353 fi

2354 elif [ x"$rm" = x"commission" ]; then

2355 grp_reg_adm $grid $usel

2356 elif [ x"$rm" = x"addteam" ]; then

2357 team=`getpar team|sed "s/'/''/g"` # for single quotation

2358 newteam=`echo "$team"|tr -d ,`

2359 if [ x"$team" != x"$newteam" ]; then

2360 echo "チーム名に使えない文字を除去しました" | html p

2361 team=newteam

2362 fi

2363 if [ -z "$team" -o x"$team" = x"なし" ]; then

2364 cat<<-EOF | html p

2365 有効なチーム名を入力してください。

2366 カンマだけ、「なし」という名前は使えません。

2367 EOF

2368 echo "有効なチーム名を入力してください。" | html p

2369 else

2370 grp_add_team $grid "$team" $usel

2371 fi

2372 elif [ x"$rm" = x"rmteam" ]; then

2373 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2374 rmteam=`getpar rmteam|sed "s/'/''/g"`

2375 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2376 gname='$grp' AND user='$user' AND key='team'\

2377 AND val='$rmteam';\"`" ]; then

2378 grp_rm_team $grid "$rmteam" $usel

2379 else

2380 echo "所属していないチームの除去操作はできません。"|html p

2381 fi

2382 else

2383 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2384 fi

2385 fi

2386 fi

2387 # New entry

2388 sql="select /* Ahh, ugly SQL, I wanna fix... */

2389 case

2390 when (select user from grp_adm where

2391 gname=(select gname from grp where rowid=$grid)

2392 and user=a.name) is not null

2393 then 'k'

2394 else ''

2395 end || a.rowid||

2396 ','||a.gecos as NAME,

2397 (SELECT count(author) /* Put post count for scoring 2016-08-01 */

2398 FROM article NATURAL JOIN article_s

2399 WHERE blogid IN

2400 (SELECT id FROM blog_s

2401 WHERE key='owner'

2402 AND val=(SELECT gname FROM grp where rowid=$grid))

2403 AND author=a.name AND key='text')

2404 as POST,

2405 (SELECT group_concat(val, ',')

2406 FROM grp_mem_m

2407 WHERE gname='$grp' AND user=a.name AND key='team') as TEAM

2408 FROM

2409 gecoses a

2410 WHERE name in (select user from grp_mem where

2411 gname=(select gname from grp where rowid=$grid))

2412 ORDER by a.gecos;"

2413 ## err grpaction: "`echo \"$sql\"`"

2414 tf=$tmpd/title.$$

2415 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2416 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2417 for=\"cmadmin\">グループ管理者委任</label>

2418 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2419 </p></div>"

2420 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2421 for=\"conf\">グループ登録解除</label>

2422 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2423 <p>この操作による通知は本人に行きません。

2424 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2425 </div>"

2426 # Get team list to which current user belongs into $hexteams

2427 myhexteams=$(hexteams "$grp" "$user")

2428 allhexteams=$(hexteams "$grp")

2429 if [ -n "$myhexteams" ]; then

2430 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2431 for=\"cmrmteam\">チーム属性除去</label>

2432 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2433 を除去します: `cgi_checkbox teamconfirm yes` 確認

2434 <p>この操作による通知は本人に行きません。

2435 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2436 </div>

2437 "

2438 fi

2439 b1='<label> <input type="checkbox" name="usel" value="'

2440 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2441 #b2='"> <span>' b3='</span></label>'

2442 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2443 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2444 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2445 cgi_form grpaction<<EOF \

2446 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2447 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2448 | _m4 -D_TITLE_="spaste(\`$tf')" \

2449 -D_SUBTITLE_="チェック後操作ボタン" \

2450 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2451 $layout/form+dump.m4.html

2452 <p>下でチェックした人を対象として:</p>

2453 <div class="foldtabs">

2454 `cgi_radio rm addteam 'id="cmteam"'`<label

2455 for="cmteam">同じチーム属性を付与</label>

2456 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2457 `cgi_datalist_h teams $allhexteams`

2458 </div>

2459 ${rmteammsg}

2460 `cgi_radio rm send id="sendmsg"`<label

2461 for="sendmsg">メッセージ送信</label>

2462 <div>

2463 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2464 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2465 `cgi_textarea text "" cols=40`

2466 </div>

2467 ${isowner:+$cmmsg$excmsg}

2468 `cgi_radio rm close id="x"`<label for="x">×</label>

2469 </div>

2470 <h4>$grp 参加者一覧</h4>

2471 <table class="td2r">

2472 `sq $db -header -html "$sql"`

2473 </table>

2474 `cgi_hidden grp $grid`

2475 EOF

2476 }

2477 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2478 # Create TEMPORARY VIEW

2479 query<<EOF

2480 CREATE TEMPORARY VIEW writeusers AS

2481 SELECT DISTINCT author FROM article

2482 WHERE id in (

2483 select id from article where blogid=(select id from blog where rowid=$1)

2484 );

2485 CREATE TEMPORARY VIEW movablegroups AS

2486 SELECT g.rowid growid , g.gname

2487 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2488 ON grp.gname=gm.gname -- そのユーザが属している

2489 AND user='$user') g -- グループに絞る

2490 WHERE (SELECT author FROM writeusers

2491 EXCEPT

2492 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2493 IS NULL;

2494 $2

2495 EOF

2496 }

2497 sql4readableblogs() {

2498 # Create view of blogs that can be readable to $user

2499 # Blog is readable when:

2500 # 1: blog owner is an user

2501 # 2: else, 2.1: owner-group where the $user belongs

2502 # 2.2: else, owner-group is not moderated

2503 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2504 cat<<EOF ## | tee tmp/sql.out

2505 CREATE TEMPORARY VIEW readableblogs AS

2506 SELECT blog.rowid rid, id, author

2507 FROM blog

2508 NATURAL JOIN

2509 (SELECT id, val owner FROM blog_s WHERE key='owner') bs

2510 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2511 THEN 1 -- blog owner is an user, READABLE

2512 WHEN (SELECT val FROM grp_s

2513 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2514 AND

2515 (SELECT user FROM grp_mem

2516 WHERE gname=bs.owner AND user='$user') IS NULL

2517 THEN 0

2518 ELSE 1

2519 END;

2520 EOF

2521 }

2522 editheading() { # $1=rowid-of-heading

2523 rowid=${1%%[!A-Z0-9a-z_]*}

2524 if [ -z "$rowid" ]; then

2525 echo "話題番号が未指定です。" | html p

2526 return

2527 fi

2528 owner=`getvalbyid blog owner $rowid`

2529 title=`getvalbyid blog title $rowid`

2530 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2531 | _m4 -D_TITLE_="修正" \

2532 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2533 -D_BLOGS_="" -D_DUMPTABLE_="" \

2534 -D_FORM_="syscmd(\`cat')" \

2535 $layout/html.m4.html $layout/form+dump.m4.html

2536 # Move to group

2537 if isuser "$owner"; then

2538 crview4article $rowid

2539 n=`query "SELECT count(*) FROM writeusers;"`

2540 ## err N=$n

2541 if [ $((n)) -gt 0 ]; then

2542 ## err ROWID=$rowid

2543 sql="SELECT growid || ':' || gname FROM movablegroups;"

2544 cat<<-EOF

2545 <div class="fold">

2546 `cgi_checkbox mv send id="mv"`<label

2547 for="mv">この話題をグループ所有に移動する</label>

2548 <div>

2549 <form action="?mvart" method="POST" enctype="multipart/form-data">

2550 移動先グループ:

2551 <select name="mv2grp">

2552 EOF

2553 query ".mode html"

2554 query<<-EOF |

2555 $sql

2556 .mode list

2557 EOF

2558 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2559 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2560 cat<<-EOF

2561 </select>

2562 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2563 そのグループに加入しているものに限られます)</p>

2564 <p>`cgi_checkbox cfm yes`<label>確認

2565 (この操作は元に戻すことができません)</label></p>

2566 `cgi_hidden blogrowid $rowid`

2567 `cgi_submit 移動`

2568 `cgi_reset Reset`

2569 </form>

2570 </div>

2571 </div>

2572 EOF

2573 fi

2574 # end of isuser "$owner"

2575 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2576 [ -n "$hexteams" ];}; then

2577 none="`echo なし|hexize`"

2578 cat<<-EOF

2579 <div class="fold">

2580 `cgi_checkbox mv2team send id="mv2team"`<label

2581 for="mv2team">この話題を以下のチームのものにする</label>

2582 <div><p>現在の所属チーム設定:

2583 `query "SELECT

2584 coalesce((SELECT val FROM blog_s

2585 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2586 AND key='team'),

2587 ':なし');"`</p>

2588 <form action="?mvart" method="POST" enctype="multipart/form-data">

2589 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2590 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2591 `cgi_hidden blogrowid $rowid`<br>

2592 `cgi_submit 移動`

2593 `cgi_reset Reset`

2594 </form></div></div>

2595 EOF

2596 fi

2597 }

2598 mvart() { # move diary to some group or team

2599 # or move blog of group to team which belong to the group

2600 blogrowid=`getpar blogrowid`

2601 cfm=`getpar cfm`

2602 ##### echo move blog:$blogrowid to $mv2grp | html p

2603 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2604 . ./s4-blog.sh

2605 if [ -z "$blogrowid" ]; then

2606 echo "無効な指定です(mvart)。" | html p

2607 return

2608 elif [ x"$cfm" != x"yes" ]; then

2609 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2610 elif { mv2grp=`getpar mv2grp`

2611 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2612 [ -n "$mv2grp" ]; }; then

2613 crview4article $blogrowid

2614 ########## TRANSACTION BEGIN

2615 query "BEGIN;"

2616 n=`query "SELECT count(*) FROM writeusers;"`

2617 ## err Nwriteuser=$n

2618 if [ $((n)) -gt 0 ]; then

2619 query<<-EOF

2620 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2621 WHERE key='owner'

2622 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2623 AND $mv2grp IN (SELECT growid FROM movablegroups);

2624 EOF

2625 fi

2626 query "END;"

2627 ########## TRANSACTION END

2628 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2629 [ -n "$mv2team" ];}; then

2630 # blog owner can move it to ANY team

2631 case "$mv2team" in

2632 'なし')

2633 cat<<-EOF

2634 DELETE FROM blog_s

2635 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2636 AND key='team';

2637 EOF

2638 ;;

2639 "") ;;

2640 *)cat<<-EOF

2641 BEGIN;

2642 REPLACE INTO blog_s(id, key, val)

2643 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2644 'team', '$mv2team');

2645 REPLACE INTO blog_s(id, key, val)

2646 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2647 'notify', 'all'); -- Change notify to all

2648 END;

2649 EOF

2650 esac | query

2651 fi

2652 blog_reply $blogrowid

2653 echo yes | html p

2654 }

2655 editart() { # $1=article-rowid $2=blogrowid

2656 rowid=${1%%[!A-Z0-9a-z_]*}

2657 blogrowid=${2%%[!A-Z0-9a-z_]*}

2658 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2659 echo "表示する記事番号が未指定です。" | html p

2660 return

2661 fi

2662 owner=`getvalbyid blog owner $blogrowid`

2663 title=`getvalbyid blog title $blogrowid`

2664 author=`getvalbyid article author $rowid`

2665 ## err EDITart: owner=$owner, author=$author

2666 if isgrpowner $user $owner; then

2667 : EDIT OK

2668 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2669 echo "本人か所有者しか編集できません." | html p

2670 return

2671 fi

2672 aid=`query "select id from article where rowid=$rowid;"`

2673 tmpout=$tmpd/editart.$$.out

2674 GF_ACTION="?replyblog+$blogrowid#$aid" \

2675 edittable $formdir/article.def article $rowid \

2676 > $tmpout

2677 rm -f /tmp/editart.out

2678 # Cannot use pipelining to m4 with genform() because of stdin stack

2679 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2680 -D_FORM_="syscmd(cat $tmpout)" \

2681 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2682 -D_BLOGS_= -D_DUMPTABLE_= \

2683 $layout/html.m4.html $layout/form+dump.m4.html

2684 }

2685 send2mem() {

2686 rowid=`getpar grp`

2687 if [ -z "$rowid" ]; then

2688 echo "グループが未指定です。" | html p

2689 return

2690 fi

2691 message=`getpar message`

2692 if [ -z "$message" ]; then

2693 echo "文章を入れてください。" | html p

2694 return

2695 fi

2696 grp=`getgroupbyid $rowid`

2697 members=`collectemail $grp`

2698 # smail rcpt subj (file)

2699 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2700 $urlbase?grp+$rowid

2701 グループ $grp に所属する

2702 `gecos $user` さんよりメッセージ:

2703

2704 $message

2705 EOF

2706 cat<<EOF

2707 <p>以下の宛先に送信しました。</p>

2708 <pre>

2709 $members

2710 </pre>

2711 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2712 EOF

2713 }

2714 joingrpadmit() {

2715 # $1=yes/no $2=session-key

2716 if [ -z "$2" ]; then

2717 echo "bye bye" | html p; return

2718 fi

2719 t_usr=`session=$2 getpar user`

2720 t_grp=`session=$2 getpar group`

2721 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2722 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2723 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2724 echo "無効な加入依頼です。" | html p

2725 echo "有効期限が切れたか、

2726 他の管理者がいる場合は処理済みの可能性があります。" | html p

2727 return

2728 fi

2729 if ! isgrpowner "$user" $t_grp; then

2730 echo "グループ管理者のみの機能です。" | html p; return

2731 fi

2732 case $1 in

2733 yes) joingrp "$t_grp" "$t_usr" yes ;;

2734 no) joingrp "$t_grp" "$t_usr" no ;;

2735 *)

2736 echo "無効な指定です($1)。" | html p

2737 return ;;

2738 esac

2739 gid=$(query "select rowid from grp where gname=`sqlquote $t_grp`;")

2740 rcpts="`getgroupadminmails $t_grp` $user"

2741 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2742 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2743 に

2744 $t_usr

2745 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2746 しました。"

2747 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2748 query "delete from session where id='$2';"

2749 echo "$body" | html p

2750 }

2751

2752 joingrprequest() {

2753 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2754 jss="joingrp-`date +%s`-`genrandom 12`"

2755 addsession $jss +${memoplimitdays}days

2756 query "replace into par values('$jss', 'group', 'string', `sqlquote $1`),

2757 ('$jss', 'user', 'string', `sqlquote $user`);"

2758 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2759 $url

2760 $user さんから

2761 グループ $1

2762 に加入依頼がありました。

2763

2764 承認する:

2765 $urlbase?joingrpadmit+yes+$jss

2766

2767 白紙に戻す:

2768 $urlbase?joingrpadmit+no+$jss

2769 EOF

2770 echo "管理者に加入依頼を出しました。

2771 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2772 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2773 }

2774 joingrp() {

2775 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2776 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2777 if isgrpowner "$user" "$1"; then

2778 isowner="yes"

2779 elif [ -n "$5" ]; then

2780 isowner="yes"

2781 else

2782 isowner=""

2783 fi

2784 ## err jg:isgrpowner: isowner="$isowner"

2785 if [ -n "$isowner" ]; then

2786 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2787 elif [ x"$2" != x"$user" ]; then # if user is not login user

2788 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2789 return

2790 elif [ x"$3" = x"no" ]; then

2791 : # Do not pursue those who leave

2792 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2793 : # Member can change own email address for the joining moderated group

2794 else # adding user is $user itself

2795 case `getgroupattr $1 regmode` in

2796 moderated)

2797 joingrprequest "$@" # Request only

2798 return

2799 ;;

2800 *)

2801 ;;

2802 esac

2803 fi

2804 qgname=`sqlquote $1`

2805 cond="where gname=$qgname and user='$2'"

2806 if [ x"$3" = x"yes" ]; then

2807 query "replace into grp_mem values($qgname, '$2');"

2808 if [ -n "$4" ]; then

2809 if msg=`emaildomaincheck "$4"`; then

2810 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2811 'string', '$4', NULL);"

2812 else

2813 echo $msg

2814 fi

2815 else

2816 query "delete from grp_mem_s $cond and key='email';"

2817 fi

2818 if [ -n "$5" ]; then # as ADMIN

2819 # Coming here means newly created group

2820 sql="select case\

2821 when (select count(*) from grp_mem where gname=$qgname)=1\

2822 then (select user from grp_mem\

2823 where gname=$qgname and user='$user')\

2824 else '' end; "

2825 err NewGrpChk: $sql

2826 if [ -n "`query \"$sql\"`" ]; then

2827 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

2828 query "replace into grp_adm values($qgname, '$user');"

2829 fi

2830 fi

2831 else

2832 query "delete from grp_mem $cond;

2833 delete from grp_mem_s $cond;

2834 delete from grp_mem_m $cond;"

2835 fi

2836 }

2837 grp_add_team() (

2838 # $1=grp-rowid $2=team $3...=user-rowid(s)

2839 grp=`getgroupbyid $1`

2840 team=$2; shift; shift

2841 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2842 { echo "BEGIN;"

2843 for user; do

2844 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2845 '$grp',\

2846 (SELECT name FROM user WHERE rowid=$user),\

2847 'team', 'string', '$team');"

2848 done

2849 echo "END;"

2850 } | query

2851 )

2852 grp_rm_team() (

2853 # $1=grp-rowid $2=team $3...=user-rowid(s)

2854 grid=$1

2855 qgrp=$(sqlquote "`getgroupbyid $grid`")

2856 team=$2; shift; shift

2857 [ -z "$grid" -o -z "$team" ] && return

2858 { echo "BEGIN;"

2859 for user; do

2860 echo "DELETE FROM grp_mem_m\

2861 WHERE gname=$qgrp \

2862 AND user=(SELECT name FROM user WHERE rowid=$user)\

2863 AND key='team' AND val='$team';"

2864 done

2865 cat<<-EOF

2866 DELETE FROM blog_s

2867 WHERE rowid=(

2868 SELECT rowid

2869 FROM blog_s a

2870 WHERE key='team'

2871 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2872 AND NOT EXISTS (SELECT * FROM grp_mem_m

2873 WHERE key='team' AND val=a.val -- a.val=team

2874 AND gname = (SELECT val FROM blog_s b

2875 WHERE a.id=b.id AND key='owner')

2876 ));

2877 EOF

2878

2879 echo "END;"

2880 } | query

2881 )

2882 grp_reg_adm() {

2883 # $1=grp-rowid $2...=user-rowid

2884 grid=$1

2885 grp=`getgroupbyid "$1"`

2886 if [ -z "$grp" ]; then

2887 echo "無効なグループIDです" | html p; return

2888 fi

2889 if ! isgrpowner $user "$grp"; then

2890 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2891 fi

2892 shift

2893 for urid; do

2894 newadm=`query "select name from user where rowid=$urid;"`

2895 if [ -z "$newadm" ]; then

2896 echo "指定ユーザIDがおかしいようです。" | html p; return

2897 fi

2898 err GRP_reg_adm: "replace into grp_adm values(`sqlquote $grp`, '$newadm');"

2899 err ismember $newadm $grp

2900 if ismember $newadm $grp; then

2901 # OK, go ahead

2902 getgname="(select gname from grp where rowid=$grid)"

2903 query "replace into grp_adm values($getgname, '$newadm');"

2904 # confirm insertion

2905 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2906 if [ -n "`query \"$sql;\"`" ]; then

2907 echo "追加完了: $newadm" | html p

2908 else

2909 echo "追加失敗($1 $urid)" | html p

2910 fi

2911 fi

2912 showgroup $grid

2913 done

2914 }

2915 dt_rowhack() {

2916 # From: <TR>

2917 # ....

2918 # <TD>rowclass=foo</TD>

2919 # </TR>

2920 # To: <TR class="foo">....<TD>foo</TD></TR>

2921 sed -e '

2922 /^<TR>/ {

2923 :loop

2924 s/\n//

2925 N

2926 /<\/TR>/ {

2927 s/\n//

2928 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

2929 n

2930 }

2931 $q

2932 b loop

2933 }'

2934 }

2935 dumptable() {

2936 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2937 # textのフィールドだけ全てダンプにしたほうがいいか

2938 # $DT_VIEW sets link

2939 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2940 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2941 VIEW=${DT_VIEW-replyblog}

2942 if [ -n "$VIEW" ]; then

2943 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

2944 fi

2945 # $DT_CHLD=ChildTable:BindColumn

2946 if [ -n "$DT_CHLD" ]; then

2947 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

2948 cntall="(select count($_i) from $_t where $_i=a.id)"

2949 # XXX: Dirty workaround for slow subquery of acclog

2950 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

2951 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

2952 cntnew="(select count(val) from ${_t}_s where key='ctime' \

2953 and id in (select id from $_t where $_i=a.id) \

2954 and val > coalesce((select time from myacclog where \

2955 tblrowid=a.rowid),\

2956 '1970-01-01'))"

2957 cnt="$cntnew as '新着', $cntall as '総数',"

2958 dt_class=" td2r td3r dumpblogs"

2959 fi

2960 # Construct join expression

2961 eav="" scols=""

2962 pk=`gettblpkey $2`

2963 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

2964 substr=${substr:-%s}

2965 for col in ${3:-`gettbl_s_cols $2`}; do

2966 valvar=val

2967 case $col in

2968 gecos) scols="$scols${scols:+, }${col#}"

2969 continue ;; # built-in column name

2970 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

2971 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

2972 case "$as" in

2973 *:*=*) cnd=${as#*:}

2974 h=${cnd%%=*} v=${cnd#*=}

2975 h=`sqlquotestr "$h"`

2976 v=`sqlquotestr "$v"`

2977 valvar="CASE val WHEN $h THEN $v END"

2978 as=${as%%:*} ;;

2979 esac

2980 ;;

2981 *) as=${col} ;;

2982 esac

2983 ss=`printf "$substr" "$valvar"`

2984 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

2985 scols="$scols${scols:+, }b.$as"

2986 done

2987 #case author when '$user' then a.rowid else '---' end as ID,

2988 sql=${DT_SQL:-"select \

2989 a.rowid as LINK,\

2990 $cnt\

2991 $scols from $2 a left join\

2992 (select $pk,$eav,

2993 max(case key when 'owner'

2994 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

2995 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

2996 ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"

2997 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

2998 <div>

2999 <div class="dumptable">

3000 <table class="b$dt_class">

3001 `sq -header -cmd ".mode $1" $db "$presql$sql"`

3002 </table>

3003 </div>

3004 </div>

3005 EOF

3006 }

3007

3008 par2table() (

3009 # copy current parameters of par into destination table

3010 # $1=definition-file

3011 # Using $user and $session

3012 # Return value:

3013 # 0: Stored successfully

3014 # 1: Insufficient fillings

3015 # 2: No permission to modify the record

3016 # 3: Invalid rowid

3017 # 4: SUCCESS to delete

3018 # 5: Stop deletion for lack of confirm check

3019 # 6: Password length too short

3020 # 7: Password mismatch

3021 # 8: Old password incorrect

3022 rowid=`getpar rowid`

3023 if [ ! -e $1 ]; then

3024 echo "テーブル定義ファイルが見付かりません" | html p

3025 exit 1

3026 fi

3027 tbl=${1%.def}

3028 tbl=${tbl##*/}

3029 if [ -n "$rowid" ]; then # Modify existing entry

3030 if [ x"$tbl" = x"user" ]; then

3031 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3032 elif [ x"$tbl" = x"grp" ]; then

3033 sql="select gname from $tbl where rowid=$rowid;"

3034 ##err p2t:grp:q $sql

3035 isgrpowner $user "`query $sql`" && rowowner=$user

3036 else

3037 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3038 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3039 fi

3040 ### err rowowner=$rowowner

3041 if [ x"$user" != x"$rowowner" ]; then

3042 echo "他人のレコードはいじれないの" | html p

3043 return 2

3044 elif [ -z "$rowowner" ]; then

3045 echo "指定したレコードはないみたい" | html p

3046 return 3

3047 fi

3048 rm=`getpar rm` cfm=`getpar confirm`

3049 # Editing existent entry

3050 if [ x"$rm" = x"yes" ]; then

3051 if [ x"$rm$cfm" = x"yesyes" ]; then

3052 query "delete from $tbl where rowid=$rowid;"

3053 return 4

3054 else

3055 echo "消去確認のチェックがないので消さなかったの..." | html p

3056 return 5

3057 fi

3058 fi

3059 fi

3060

3061 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3062 if [ -n "$rowid" ]; then

3063 # Update of existing record

3064 for col in `gettblcols $tbl`; do

3065 val=`getparquote $col`

3066 [ -z "$val" ] && continue

3067 ## err query "update $tbl set $col=$val where rowid=$rowid"

3068 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3069 sql="update $tbl set $col=$val where rowid=$rowid;"

3070 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3071 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3072 ## User name cannot be changed with interface provided with this

3073 ## script. But we offer the trigger to change owner user

3074 ## of blog_s table.

3075 #err "select quote($col) from $tbl where rowid=$rowid;"

3076 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3077 cat<<-EOF | query

3078 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3079 -- should use EXCLUSIVE transaction outside of this.

3080 SAVEPOINT par2table;

3081 $sql

3082 update blog_s set val=$val

3083 where key='owner' and val=$old;

3084 RELEASE SAVEPOINT par2table;

3085 EOF

3086 ## XX: DIRTY Hack Ends here

3087 ## We should keep blog's owner as a single column which has

3088 ## foreign key constraint with primary key of grp/user.

3089 else

3090 query "$sql"

3091 fi

3092 done

3093 # Then, set up $pval for further insertion of tbl_s and tbl_m

3094 for col in `gettblpkey $tbl`; do

3095 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3096 pval="$pval${pval:+, }\"$val\""

3097 done

3098 else

3099 # New entry

3100 # Generate values() for primary keys

3101 for col in `gettblpkey $tbl`; do

3102 # Genuine primary keys for _m and _s

3103 val=`getvalquote $tbl $col`

3104 [ -z "$val" ] && continue

3105 pval="$pval${pval:+, }$val"

3106 done

3107 ##err pval=$pval

3108 for col in `gettblfkey $tbl`; do

3109 # args for values() to insertion into master table

3110 val=`getvalquote $tbl $col`

3111 [ -z "$val" ] && continue

3112 formaster=$formaster"${formaster:+, }$val"

3113 done

3114 formaster="$pval${formaster:+, }$formaster"

3115 ## err formaster=$formaster

3116 if [ -z "$formaster" ]; then

3117 echo "項目を全て埋めてください" | html pre

3118 return 1

3119 fi

3120 ## err "replace into $tbl values($formaster);"

3121 query "replace into $tbl values($formaster);"

3122 ## Insertion to master table, done

3123 fi

3124

3125 for kt in s m; do

3126 tb2=${tbl}_$kt

3127 for col in `gettbl_${kt}_cols $tbl`; do

3128 ptype=`getpartype $col "limit 1"`

3129

3130 # First, check update of existing entries in _m

3131 if [ $kt = m ]; then

3132 # sessID|address.1.22|string|Somewhere-x.y.z

3133 sql=""

3134 ##err dots from query "select var from par where var like '$col.%';"

3135 for v in `query "select var from par where var like '$col.%';"`; do

3136 # v=address.1.22

3137 st_rowid=${v##*.}

3138 origcol=${v%%.*} # original column derived from

3139 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3140 ##case `getpartype $v` in

3141 ## err CASE `gettbl_coltype $tbl/$origcol` in

3142 ## err edit flag = `getpar action.$v`

3143 case `getpar action.$v` in

3144 rm)

3145 if [ x`getpar confirm.$v` = x"yes" ]; then

3146 newsql="delete from $tb2"

3147 else

3148 echo "削除確認未チェック" | html p

3149 fi ;;

3150 edit)

3151 case `gettbl_coltype $tbl/$origcol` in

3152 image|document|binary)

3153 file=$tmpd/`getparfilename $v`

3154 ## err type=file=$file

3155 [ -z "$file" ] && continue

3156 bn=`sqlquotestr "${file##*/}"`

3157 bin="X'"$(hexize "$file")"'"

3158 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3159 type=\"file:$ct\"

3160 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3161 cachedir=`getcachedir "$tbl/$rowid"`

3162 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3163 rm -rf $cachedir

3164 ;;

3165 *)

3166 newsql="update $tb2 set val=(select val from par where var \

3167 like '$col.%.$st_rowid')"

3168 ;;

3169 esac

3170 ;;

3171 *) # maybe "keep", do not modify value

3172 continue

3173 ;;

3174 esac

3175 # err newsql=$newsql

3176 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3177 done

3178

3179 if [ x"$bin" = x"NULL" ]; then

3180 ## err repl:normal sql=`echo $sql`

3181 query "$sql

3182 delete from $tb2 where type='string' and val='';"

3183 ## err repl:normal done

3184 else

3185 sqlfile="$tmpd/sqlf.$$"

3186 echo "$sql" > $sqlfile

3187 ## err repl:sqlfile=`ls -lF $sqlfile`

3188 query ".read $sqlfile"

3189 ## err repl:done

3190 fi

3191 # Rest of kt==m: set multiple mode

3192 nr=`getparcount $col`

3193 else

3194 nr=1 # for kt==s, number of records is 1

3195 fi

3196

3197 i=0

3198 while [ $i -lt $nr ]; do

3199 limit="limit 1 offset $i"

3200 i=$((i+1)) # increase beforehand against continue

3201 val=`getvalquote $tbl $col "$limit"`

3202 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3203 ## err $col=$val

3204 bin=NULL

3205 ## err partype$col=`getpartype $col "$limit"`

3206 case $ptype in

3207 file) file=$tmpd/`getparfilename $col "$limit"`

3208 ## err parfile-$col=$file

3209 [ -z "$file" ] && continue

3210 bin="X'"$(hexize "$file")"'"

3211 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3212 type=\"file:$ct\" ;;

3213 "*"*) continue ;; # foreign table

3214 *) type=\"string\" ;;

3215 esac

3216 case `gettbl_coltype $tbl/$col` in

3217 password) # special care for password

3218 # name={password,pswd1,pswd2}

3219 p1=`getpar pswd1 "$limit"`

3220 if [ -z "$p1" ]; then

3221 continue # SKIP password setting, if p1 is empty

3222 else

3223 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3224 ## err pswd=$pswd

3225 if pwcheck "$pswd"; then

3226 if [ x"$p1" = x"$p2" ]; then

3227 case "$p1" in

3228 ??????????*) ;;

3229 *) echo "パスワードは10字以上にしてください。" | html p

3230 return 6;;

3231 esac

3232 val="\"`echo $p1|mypwhash`\""

3233 else

3234 echo "2つの新パスワード不一致" | html p

3235 return 7

3236 fi

3237 else

3238 echo "旧パスワード違います" | html p

3239 return 8

3240 fi

3241 fi

3242 ;;

3243 esac

3244 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3245 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3246 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3247 if [ x"$bin" = x"NULL" ]; then

3248 ## err Normal-query: `echo $sql`

3249 query "$sql"

3250 else

3251 sqlfile="$tmpd/query.$$"

3252 echo "$sql" > $sqlfile

3253 ## err sqlfile=`ls -lF $sqlfile`

3254 query ".read $sqlfile"

3255 fi

3256 ## err p2t done

3257 done

3258 done

3259 done

3260 return 0

3261 ##err donee

3262 )

3263 genform() {

3264 # $1 = form definition file

3265 # $2, $3 (optional)= table name and ROWID

3266 # If $GF_VIEWONLY set and nonNull, output values without form

3267 # If $GF_ARGS set, use it as content-strings in the form

3268 # If $GF_OWNER set, use it as value of name="owner"

3269 # If $GF_STAGE set, use it as value of name="stage"

3270 forms="" hiddens="" rowid=$3

3271 if [ ! -e "$1" ]; then

3272 echo "そのようなデータベースはないようです($2)。" | html p

3273 return

3274 elif [ -n "$2" ]; then

3275 rec=`query "select * from $2 where rowid='$rowid';"`

3276 if [ -z "$rec" ]; then

3277 pk=`gettblpkey $2`

3278 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3279 rec=`query "select rowid from $2 where $pk='$rowid';"`

3280 rowid=$rec

3281 rec=$3

3282 fi

3283 if [ -z "$rec" ]; then

3284 echo "そんなレコードはないみたいね..." | html p

3285 return

3286 fi

3287 fi

3288 if [ -z "$GF_VIEWONLY" ]; then

3289 rm='<input id="rm" name="rm" type="checkbox"

3290 value="yes"><label for="rm">このエントリの削除</label>

3291 <span>ほんとうに消しますよ(確認)!

3292 <input name="confirm" type=checkbox value="yes">はい</span>'

3293 fi

3294 # Image Cache dir

3295 ## err genform: getcache=$2/$rowid

3296 td=`getcachedir "$2/$rowid"`

3297 while IFS=: read prompt name keytype type args; do

3298 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3299 sp="${args:+ }"

3300 form="" val=""

3301 if [ -n "$rowid" ]; then

3302 # err genform2a: Seeking for "$2.$name, type=$type"

3303 rawval=`getvalbyid $2 $name $rowid $td`

3304 val=`echo "$rawval"|htmlescape`

3305 ## err genform3a: getvalbyid $2 $name $rowid $td

3306 ## err genform3b: val="[$val]" type="$type"

3307 fi

3308 if [ -n "$GF_VIEWONLY" ]; then

3309 is_hidden "$2" "$name" && continue

3310 fi

3311 case "$type" in

3312 text*)

3313 cgiform=cgi_multi_$type

3314 if [ -s $td/$name.count -a -n "$val" ]; then

3315 form=`$cgiform $name $td`

3316 val=$(echo "$val"|

3317 while read fn; do

3318 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3319 </td></tr>$nl"

3320 done)

3321 val="<table>$nl$val$nl</table>"

3322 else

3323 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3324 form=`cgi_$type $name "$rawval" "$args"`

3325 fi

3326 ;;

3327 [Rr][Aa][Dd][Ii][Oo])

3328 fh="<label><input type=\"radio\" name=\"$name\""

3329 form="`echo $args|sed -e \

3330 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3331 ;;

3332 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3333 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3334 ;;

3335 [Ss][Ee][Ll][Ee][Cc][Tt])

3336 fh="<select name=\"$name\">$nl"

3337 form=$(for l in $args; do

3338 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3339 done)

3340 if [ -n "$val" ]; then

3341 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3342 fi

3343 form="$fh$form</select>"

3344 ;;

3345 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3346 if [ -s $td/$name.count ]; then

3347 form=`cgi_multi_file $name $td "$args"`

3348 if [ -n "$val" ]; then

3349 hrfb="$myname?showattc+$2_m"

3350 val=$(echo "$rawval" \

3351 | while read fn; do

3352 data=`percenthex "$td/$fn"`

3353 #ct=`cat $td/$fn.content-type`

3354 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3355 ri=`cat "$td/$fn.rowid"`

3356 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3357 #imgsrc="<img src=\"data:$ct,$data\">"

3358 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3359 iconhref "$td/$fn" "$hrfb+$ri" ""

3360 done)

3361 fi

3362 else

3363 form="<input type=\"file\" name=\"$name\" $args>"

3364 if [ -n "$val" ]; then

3365 imgs=$(echo "$rawval"\

3366 |while read fn;do

3367 data=`percenthex "$td/$fn"`

3368 echo "<img src=\"data:image/png,$data\">$fn<br>"

3369 done)

3370 form=$form"<br>$imgs"

3371 val=$imgs # 2015-06-15

3372 else

3373 form="<input type=\"file\" name=\"$name\" $args>"

3374 fi

3375 fi

3376 ;;

3377 [Hh][Ii][Dd][Dd][Ee][Nn])

3378 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3379 args="value=\"$GF_STAGE\""

3380 fi

3381 form="<input type=\"hidden\" name=\"$name\" $args>"

3382 prompt='' # Remove prompt

3383 ;;

3384 [Aa][Uu][Tt][Hh][Oo][Rr])

3385 [ -n "$GF_VIEWONLY" ] && continue

3386 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3387 prompt="" ;;

3388 [Oo][Ww][Nn][Ee][Rr])

3389 [ -n "$GF_VIEWONLY" ] && continue

3390 val=${GF_OWNER:-$val}

3391 val=${val:-$user}

3392 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3393 prompt="" ;;

3394 [Uu][Ss][Ee][Rr])

3395 # XXX: is null $user ok?

3396 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3397 [ -n "$GF_VIEWONLY" ] && continue

3398 form="$user"

3399 ;;

3400 [Pp]assword)

3401 [ -n "$GF_VIEWONLY" ] && continue

3402 form="`cgi_passwd`"

3403 val=""

3404 ;;

3405 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3406 [ -n "$GF_VIEWONLY" ] && continue

3407 if [ -z "$rowid" ]; then

3408 val=`genserial`

3409 fi

3410 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3411 prompt="" ;;

3412 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3413 prompt=""

3414 ;;

3415 parent|path|blog*)

3416 prompt=""

3417 ;;

3418 "*"*)

3419 tail=$tail"``"

3420 continue ;;

3421 esac

3422 if [ -n "$prompt" ]; then

3423 if [ -n "${GF_VIEWONLY}" ]; then

3424 form=$val

3425 else

3426 :

3427 fi

3428 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3429 else

3430 hiddens=$hiddens$nl"$form"

3431 fi

3432 done < $1

3433 # enctype="multipart/form-data"

3434 cat<<EOF

3435 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3436 ${rowid:+$rm}

3437 <table class="b $2">

3438 $forms

3439 </table>$hiddens

3440 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3441 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3442 EOF

3443 if [ -z $GF_VIEWONLY ]; then

3444 cat<<EOF

3445 <input type="submit" name="sub" value="OK">

3446 <input type="reset" name="res" value="Reset">

3447 EOF

3448 fi

3449 cat<<EOF

3450 $GF_ARGS</form>

3451 $tail

3452 EOF

3453 }

3454 edittable() {

3455 # $1=form-def $2=table $3 rowid

3456 genform "$@"

3457 }

3458 viewtable() {

3459 GF_VIEWONLY=1 genform "$@"

3460 }

3461 showattc() {

3462 # $1=table_m $2=rowid &optional $3=RawFlag

3463 ## err \$1=$1 \$2=$2 \$3=$3

3464 if ! isfilereadable $user $1 $2; then

3465 contenttype; echo

3466 echo "このファイルは管理者のみしか見られません" | html p

3467 putfooter; exit

3468 fi

3469 idir=`umask 002; mktempd` || exit 1

3470 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3471 bin=$idir/$myname-$$.bin

3472 sql="select quote(bin) from $1 where rowid='$2';"

3473 ## err showattc: sql: $sql

3474 sq $db "$sql" | unhexize > $bin

3475 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3476 type=${tv%//*} fn=${tv#*//}

3477 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3478 ct=${type#file:}

3479 case $ct in # all text/* changed to text/plain

3480 text/*)

3481 charset=`nkf -g $bin|cut -d' ' -f1`

3482 case $charset in

3483 ASCII*) charset="" ;;

3484 esac

3485 if [ -z "$3" ]; then

3486 ct="text/html${charset:+; charset=$charset}"

3487 link="?showattc+$1+$2+raw"

3488 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3489 | sed 's,^,<span></span>,' \

3490 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3491 -D_LINK_="$link" \

3492 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3493 exit $?

3494 fi

3495 ct="text/plain${charset:+; charset=$charset}"

3496 ;;

3497 esac

3498 contenttype "$ct"

3499 echo "Content-Disposition: filename=\"$fn\""

3500 echo "Content-Length: " `cat $bin | wc -c`; echo

3501 #echo "Content-Type: " ${type#file:}; echo

3502 cat $bin

3503 }

3504 #

3505 # Some default stupid handler on CGI values

3506 #

3507 default_storedb() {

3508 # ARG: $1=table-def-file

3509 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3510 tbl=`basename $1`

3511 tbl=${tbl%.def}

3512 cols="`grep :text $1|cut -d: -f2`"

3513 col=`echo "$cols"|head -1`

3514 vcol=`getpar $col`

3515 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3516 if [ -n "$vcol" ]; then

3517 par2table $1

3518 else

3519 return 2 # No insertion occurred

3520 fi

3521 }

3522

3523 default_view() { # $1=def-file

3524 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3525 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3526 default_storedb "$@"

3527 query "select rowid from $tbl order by rowid desc;" \

3528 | while read rowid; do

3529 viewtable $1 $tbl $rowid

3530 done | _m4 -D_TITLE_="$tbl" \

3531 -D_FORM_="`genform $1`" \

3532 -D_DUMPTABLE_="syscmd(cat)" \

3533 $layout/html.m4.html $layout/form+dump.m4.html

3534 }

3535 default_viewtext() { # $1=def-file

3536 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3537 default_storedb "$@"

3538 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3539 | _m4 -D_TITLE_="$tbl" \

3540 -D_FORM_="`genform $1`" \

3541 -D_DUMPTABLE_="syscmd(cat)" \

3542 $layout/html.m4.html $layout/form+dump.m4.html

3543 }

3544 default_smail() {

3545 default_storedb "$@"

3546 if [ $? -eq 2 ]; then

3547 _m4 -D_TITLE_="入力" \

3548 -D_FORM_="`genform $1`" \

3549 -D_DUMPTABLE_="" \

3550 $layout/html.m4.html $layout/form+dump.m4.html

3551 return

3552 fi

3553 cond=""

3554 for pk in `gettblpkey $tbl`; do

3555 pv=$(sqlquote $(getpar $pk))

3556 cond="$cond${cond:+ and }$pk=$pv"

3557 done

3558 sql="select rowid from $tbl where $cond;"

3559 rowid=`query "$sql"`

3560 ## err smail1 - "$sql" "-> rowid=$rowid"

3561

3562 while IFS=: read prompt name keytype type args; do # Read from $1

3563 val=`getpar $name`

3564 if [ -n "$val" ]; then

3565 text="$text

3566 $prompt

3567 $name=$val

3568 ---------------------------------------------------------"

3569 fi

3570 case "$type" in

3571 image|document|file)

3572 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3573 fns=$(echo "$fn"|while read fn; do

3574 err mv $tmpd/$fn.orig $tmpd/$fn

3575 mv $tmpd/$fn.orig $tmpd/$fn

3576 rm $tmpd/$fn.rowid # Remove cache flag

3577 ## err "`ls $tmpd/$fn`"

3578 echo $fn

3579 done)

3580 files="$files $fns"

3581 ;;

3582 esac

3583 done < $1

3584 ## err FILES=$files "`ls -lF $tmpd`"

3585 subj="from ${REMOTE_ADDR}"

3586 (echo "$url"

3587 echo "への書き込みがありました。"

3588 echo "------"

3589 echo "$text"

3590 ) | (cd $tmpd &&

3591 err LS="`ls -lF`" &&

3592 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3593 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3594 echo "以下の内容で送信しました。" | html p

3595 viewtable $1 $tbl \

3596 `query "select rowid from $tbl order by rowid desc limit 1;"`

3597 echo "戻る" | html a "href=\"?\""

3598 }