s4: 0cc58da8f7af s4-funcs.sh

s4

view s4-funcs.sh @ 650:0cc58da8f7af

Add supplemental message

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sat, 09 May 2020 12:58:05 +0900
parents	a8a5e6437f1a
children	22f4f91656a8

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 test -n "$HTTP_HOST" && isCGI=true

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

12 tmpdir=${TMPDIR:-tmp}

13 dbdir=${DBDIR:-db}

14 tmpfiles=""

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

16 querylog=${QUERYLOG:-$tmpdir/query.log}

17 searchlog=${SEARCHLOG:-$tmpdir/search.log}

18 workdb=$dbdir/tmpdata.sq3

19 listentlimit=${LISTENTLIMIT:-30}

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

21 noreply=${NOREPLY:-noreply@example.org}

22 noreply_from="${S4NAME:-s4} message notification <$noreply>"

23 invite_policy=${INVITE_POLICY:-"このコミュニティに関りのあるあなたの信頼できる人を招きます。"}

24 templ=${TEMPL:-templ}

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

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

27 imgdir=${IMGDIR:-img}

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

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

30 msgdir=$templ/msg

31 timeout="+2 days"

32 memoplimitdays="7"

33 dumpcollen=22

34 #thumbxy=120x120

35 thumbxy=96x96

36 iconxy_S=80x80

37 iconxy_M=400x400

38 maximagexy=1600x1600

39 ### maximagexy=400x400

40 filesize_max=$((5*1024*1024))

41 filesize_max_MB="$((filesize_max/1024/1024))MB"

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

44 file_accept_help="

45 添付可能ファイル: テキスト、画像、音声、動画、ODF、PDF、

46 圧縮ファイル、データベースファイル

47 (いずれも ${filesize_max_MB} 以内)

48 "

49 file_warn="$file_accept_help

50 [編集]リンクから修正してください。"

51 blogreadflagrowid=0

52 blogcutoffflagrowid=-1

53 whatsnewdays=${WHATS_NEW_DAYS:-14}

55 tconfs=""

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

57 conftbl=_tblconf

58 nl="

59 "

60 likeesc=`printf '\037'` # ESCAPE char of LIKE operator

61 iconcachekey="profimgcache_S"

62 case "$HTTP_USER_AGENT" in

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

64 *) touchpanel="" ;;

65 esac

66 [ -f ./s4-cgi.sh ] && . ./s4-cgi.sh

68 : <<EOF

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

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

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

73 レポート提出管理

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

76 [2016]

77 7/12 根本への反省

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

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

81 [以下2015]

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

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

84 Web

85 締切設定

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

88 ○自分の提出物リスト

90 7/19 ○設置

91 ○一斉送信

92 ○getparfilename の tmpd の扱い

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

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

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

96 ○添付ファイル回収

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

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

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

100

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

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

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

104 ○レポート提出モード

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

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

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

108

109

110 7/9 ○管理者の追加

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

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

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

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

115

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

117

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

119 ○グループ検索

120

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

122

123

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

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

126

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

128 ○6/1 par2tableを triplex 対応に

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

130 →とすると一気に

131

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

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

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

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

136

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

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

139 ## 1. passwd

140 ## 2. grp

141 ## 3. grp_mem

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

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

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

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

146

147 ## ● listの定義:

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

149 ## ● hashの定義:

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

151

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

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

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

155 ## csv2sq3 で .csv.sq3 の Makefile

156

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

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

159 ## type := root | comment

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

161 ## unique(id, filename)

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

163

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

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

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

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

168 ## できたー!

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

170 ↓

171 ↓以下に変更

172 with recursive allmem as

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

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

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

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

177

178

179 with recursive allmem as

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

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

182 where allmem.val=grp_m.gname)

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

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

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

186

187

188 ## triggerもできた。

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

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

191

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

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

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

195 ## グループ管理

196 ## 個人情報管理

197 ##

198

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

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

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

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

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

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

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

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

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

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

209

210 ## ログテーブル

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

212

213

214

215 ■表設計

216 * 3つの表に分散管理

217 id格納表 + hash表 + list表

218 * *_s *_m

219

220

221

222 user, user_map, user_col

223

224 ■抽象エントリタイプ

225 * user

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

227 * group

228 権限判定に利用

229 * serial

230 自動idとして機能

231 * password

232 入力 type=passwordで入力

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

234 * session

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

236 * text

237 入力 type=text

238 * textarea

239 入力 textarea

240 * image|document

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

242 * owner

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

244 * gowner

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

246 * timestamp

247 datetime()

248 * parent

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

250 * path

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

252

253 格納タイプ

254 * list

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

256 * hash

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

258

259 オブジェクトタイプ

260 * entry

261 id, title, owner

262 * textpart

263 id, parentID, text

264 * binarypart

265 id, parentID, contenttype, filename, content

266 * content

267 hash(textpart), list(binarypart)

268 * topic

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

270 * reply

271 id, parentID, content

272 * blog

273 list(entry)

274 blog = [topic, list(reply)]

275

276

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

278 "text" => "hogehoge ..",

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

280 "author" => "taro",

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

282 "parent" => "/",

283 "path" => "/1",

284 "text" => "blah, blah, ....",

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

286 {"serial" => 2,

287 "author" => "hanako",

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

289 "parent" => "/",

290 "path" => "/2",

291 "text" => "blah, blah, ....",

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

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

294

295

296 user:=

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

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

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

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

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

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

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

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

305

306 変換表

307 /user/email=m

308

309 blog:=

310 シリアル:id:p:serial

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

312 所有者:owner:s:owner:

313 時刻:ctime:s:stamp:

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

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

316

317 article:=

318 シリアル:id:p:serial

319 筆者:author:s:owner

320 時刻:ctime:s:stamp:

321 参照元:parent:s:parent:

322 パス:path:s:path:

323 本文:text:s:textarea:

324 画像:image:m:image:

325

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

327

328

329 EOF

330

331 logstart() {

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

333 }

334 logend() {

335 echo ">>>" >> ${1:-$querylog}

336 }

337 sqlog() {

338 logstart

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

340 cat >> $querylog

341 else

342 echo "$*" >> $querylog

343 fi

344 logend

345 }

346 sq() {

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

348 logstart

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

350 tee -a $querylog|sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000"

351 else

352 echo "$@" >> $querylog

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

354 fi

355 logend

356 }

357 dbsetup() {

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

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

360 sqi=$tmpdir/sqi.$$

361 sqo=$tmpdir/sqo.$$

362 mkfifo $sqi $sqo

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

364 sq $db < $sqi &

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

366 if [ -n "$isCGI" ]; then

367 exec 2>> $tmpdir/error.out

368 fi

369 exec 3>> $tmpdir/debug.out

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

371 chmod o-r $tmpdir/error.out $tmpdir/debug.out

372 rm $sqi

373 }

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

375 if [ -n "$HTTP_USER_AGENT" ]; then # When called from httpd

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

377 fi

378 }

379 cleanup() {

380 trap '' INT HUP EXIT TERM PIPE

381 echo .quit >&5

382 kill $sq3pid

383 kill $sq3pid

384 rm -f $sqo $sqi

385 rm -rf $tmpfiles

386 cleanup2

387 }

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

389 # simple redirection for the sake of speed.

390 query() {

391 # echo ".once $sqo" >&5

392 echo ".output $sqo" >&5

393 logstart

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

395 tee -a $querylog

396 else

397 echo "$@" >> $querylog

398 echo "$@"

399 fi >&5

400 echo ".output stdout" >&5

401 cat $sqo

402 logend

403 }

404 _m4() {

405 #S4NAME=f,f,f

406 m4 ${S4NAME:+"-D_S4NAME_=${S4NAME}"} ${S4CSS:+-D_S4CSS_="$S4CSS"} "$@"

407 }

408 ismember() {

409 # $1=user, $2=group

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

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

412 }

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

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

415 }

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

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

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

419 }

420 isgrpowner() (

421 # $1=user, $2=group

422 gn=`sqlquote "$2"`

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

424 err isgrpowner: $sql

425 test -n "`query $sql`"

426 )

427 isgrpownerbygid() (

428 # $1=user, $2=group-rowid

429 sql="select user from grp_adm where gname=(select gname from grp where rowid=$2) and user='$1';"

430 err isgrpownerbygid: $sql

431 test -n "`query $sql`"

432 )

433 getgroupadminmails() {

434 # $1=group

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

436 email4group "$1" "$i" ;

437 done

438 }

439 getgroupadmins() { # $1=group

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

441 qgrp=`sqlquote "$1"`

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

443 }

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

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

446 getvalbyid grp $2 \

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

448 }

449 getgroupbyid() {

450 # $1=id|gname

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

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

453 # err ggbyid: `echo $sql`

454 query $sql

455 }

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

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

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

459

460 # Return true when anonymous mode

461 [ "$anonymousmode" ] && return 0

462 # case `getvalbyid blog mode $2` in

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

464 # *closed*)

465 # owner=`getvalbyid blog owner $2`

466 # if isgrp $owner; then

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

468 # elif isuser $owner; then

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

470 # fi

471 # esac

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

473 sql="with getblog as (\

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

475 select blogid from article where id in\

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

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

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

479 select case\

480 when (select author from article where\

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

482 then 'author'\

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

484 then 'open'\

485 when (select val from getmode) is null \

486 then 'open'

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

488 then (select user from grp_adm where \

489 gname=(select val from getowner) and \

490 user='$1')\

491 when (select author from article where\

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

493 then 'user+author'

494 else '' end;"

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

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

497 result=`query "$sql"`

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

499 return 2

500 }

501 linkhome() {

502 # $1=UserOrGroup

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

504 if isuser $1; then

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

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

507 else

508 echo -n "grp+$1"

509 fi

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

511 }

512 hreflink() {

513 # s4 specific notation:

514 # ^href=URL

515 # ^iframe=URL

516 # ^video=URL

517 # [[#NUM]] - Jump to article ID NUM

518 # [[#Keyword] - Jump to keywrod search for "Keyword"

519 # OSM umap Wikistyle Notation:

520 # [[URL]] - Simle Link

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

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

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

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

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

526 # Other Style

527 # ---- - <hr> (In the beginning of line)

528 # *Word* - <em>Word</em>

529 # _Word_ - <em>Word</em>

530 # **Word** - <strong>Word</strong>

531 # __Word__ - <strong>Word</strong>

532 # SPC+SPC+$ - <br>

533 cb='<input type="checkbox" class="s4-checkbox" disabled'

534 checkboxON="${cb} checked>"

535 checkboxOFF="${cb}>"

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

537 _hrefptn="[A-Za-z0-9/~%+?=@!.][^][()<> ]*" # URL should start with ASCII

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

539 -e "s|\[\[#$[^]&]*$\]\]|<a href=\"?kwd=\1\&stage=searchart\">\#\1</a>|g" \

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

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

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

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

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

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

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

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

548 -e "s|^video=$$_hrefptn$|<video controls><source height=\"320\" src=\"\1\"></video>|" \

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

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

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

552 -e 's,^----*$,<hr>,' \

553 -e 's, \*\*$[^* |][^*|]*[^ |]$\*\* ,<strong>\1</strong>,g' \

554 -e 's, __$[^_ |][^_]*[^ ]$__ ,<strong>\1</strong>,g' \

555 -e 's, \*$[^* |][^*|]*[^ |]$\* ,<em>\1</em>,g' \

556 -e 's, _$[^_ ][^_]*[^ ]$_ ,<em>\1</em>,g' \

557 -e 's, $,<br>,' \

558 -e "s,- \[ *\]$[^|-]*$,${checkboxOFF}<label>\\1</label>,g" \

559 -e "s,- \[[^ ]\]$[^|-]*$,${checkboxON}<label>\\1</label>,g" \

560

561 }

562 minitbl() {

563 sed -n '

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

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

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

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

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

569 H; # Concat this line to HoldSpace

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

571 $ b cont

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

573 }

574 :cont

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

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

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

578 # . of ^. is workaround for FreeBSD sed

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

580 p; # Print whole "table" element

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

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

583 }

584 x; # Back to the newest line

585 p; # Print rest' | miniul

586 }

587 miniul() {

588 sed -e '

589 /^\* / {; # 行頭 "* "

590 x; s,^,<ul>,; x; # 1週目: ホールドスペース先頭に <ul> を

591 :top

592 s/\n//;

593 s/^ *//; # 2周目以降: 行頭空白削除

594 s,\* ,,; # まず行頭の "* " を消しておく

595 H; # 置き換え結果をホールドスペースに追加

596 s/.*//; # パターンスペースは消しておく

597 # ↓最終行なら残ったホールドスペース処理のため :cont へ

598 $ b cont

599 N; # 次の行を読む

600 s/\n//; # 空白始まりは継続行

601 /^ /b top

602 x; s/\n/<li>/; s,$,</li>,; # 継続行でなければ <li></li> で囲む

603 p; s/.*//;

604 x; # 次も "* " ならループを抜けない

605 /^\* /b top

606 s,^,</ul>,; # 次が一般行なら箇条書終わり

607 }

608

609 :cont

610 x; # 行頭| 以外の行:

611 /./ {; # ホールドスペースに文字列があれば

612 s/^\n/<li>/; s,$,</li></ul>,; # 箇条書を書き切って終わり

613 H; x

614 }

615 x'

616 }

617 acclog() (

618 # $1=table, $2=rowid

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

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

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

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

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

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

625 fi

626 )

627 gecos() (

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

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

630 )

631 setpar() {

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

633 }

634 replpar() {

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

636 }

637 getpar() {

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

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

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

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

642 fi

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

644 case "$var" in

645 owner)

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

647 echo $user; return

648 elif ismember $user $val; then

649 echo $val; return

650 fi ;;

651 esac

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

653 echo "$val"

654 }

655

656 getpartype() {

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

658 }

659 getparcount() {

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

661 }

662 getparfilename() {

663 # null if type of $1 is not file

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

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

666 }

667 sqlquote() {

668 (v="$1"

669 case "$v" in

670 "") return ;; # null

671 "X'"*) # quoted hex string

672 echo $1 ;;

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

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

675 echo "\"$v\""

676 return ;;

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

678 echo "\"$v\""

679 return ;;

680 *)

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

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

683 else

684 echo "\"$v\""

685 fi ;;

686 esac)

687 }

688 sqlquotestr() (

689 case "$1" in

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

691 echo "'$v'" ;;

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

693 esac

694 )

695 mktempd() {

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

697 }

698 getcachedir() { # $1=maintable

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

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

701 else

702 echo $tmpd/$thumbxy

703 fi

704 }

705 getval() {

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

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

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

709 echo "$user" ;;

710 stamp|datetime)

711 date "+%F %T" ;;

712 serial)

713 (s=`getpar $2`

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

715 *)

716 getpar "$2" "$3";;

717 esac

718 }

719

720 getvalquote() {

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

722 (v=`getval "$@"`

723 case "$v" in

724 "") echo NULL ;;

725 *) sqlquote "$v" ;;

726 esac)

727 }

728 getparquote() {

729 sqlquote "`getpar $1`"

730 }

731 getbinbyid() {

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

733

734 }

735 getvalbyid() {

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

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

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

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

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

741

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

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

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

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

746 return

747 fi

748 done

749 rowid=$3

750 pk=`gettblpkey $1`

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

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

753 td=${4:-$tmpd}

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

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

756 for kt in s m; do

757 t=${1}_$kt

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

759 vcount=1 # count(val)

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

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

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

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

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

765 if [ $kt = m ]; then

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

767 # Reset val to store filenames if type is string

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

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

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

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

772 i=0

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

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

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

776 i=$((i+1))

777 fn=$c.$i

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

779 case $type in

780 file:*)

781 #file=$td/$val

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

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

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

785 # FOR SPEED: Skip file generation if imgcache exists

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

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

788 && continue

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

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

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

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

793 select rowid from $cond $slice;

794 .output '$td/$fn'

795 select val from $cond $slice;

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

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

798 .output stdout

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

800 EOF

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

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

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

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

805 ls -lh "$file" |

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

807 case "$type" in

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

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

810 *|*:[Aa]pplication*)

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

812 png:- > "$file"

813 ;;

814 esac

815 ;;

816 *)

817 sq $db<<EOF

818 .output $td/$fn.rowid

819 select rowid from $cond $slice;

820 .output $td/$fn

821 select val from $cond $slice;

822 EOF

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

824 ;;

825 esac

826 done

827 else

828 rm -f $td/$c.count

829 case $type in

830 file:*)

831 echo "$val" \

832 | while read fn; do

833 file=$td/$fn

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

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

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

837 | unhexize > "$file"

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

839 case $type in

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

841 *:[Aa]pplication*)

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

843 png:- > $file ;;

844 esac

845 fi

846 done

847 ;;

848 esac

849 fi

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

851 return

852 fi

853 done

854 done)

855 }

856 getvalbypkey() (

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

858 pk=`gettblpkey $1`

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

860 getvalbyid "$1" "$2" $rowid $4

861 )

862 getvalbycond() {

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

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

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

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

867 getvalbyid "$1" "$2" $rowid "$4"

868 fi

869 }

870 getpwfield() {

871 # getpwfield user column

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

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

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

875 echo "$val"

876 return 0

877 else

878 return 1

879 fi

880 }

881 numericalize() {

882 echo "${1%%[!0-9]*}"

883 }

884 encode() {

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

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

887 sha1=sha1

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

889 sha1=sha1sum

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

891 sha1=gsha1sum

892 fi

893 fi

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

895 }

896 enjpeg() {

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

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

899 cjpeg="cjpeg"

900 else

901 cjpeg="convert - jpeg:-"

902 fi

903 fi

904 $cjpeg "$@"

905 }

906 mycrypt() (

907 key=$1 salt=$2

908 # err \$2=$2

909 case $2 in

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

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

912 esac

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

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

915 )

916 hexize() {

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

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

919 hexize="xxd -p"

920 else

921 hexize_hd() {

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

923 }

924 hexize="hexize_hd"

925 fi

926 fi

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

928 }

929 unhexize() {

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

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

932 unhex="xxd -p -r"

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

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

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

936 EOF

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

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

939 # world writable...:)

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

941 fi

942 fi

943 cat "$@" | $unhex

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

945 }

946 percenthex() {

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

948 }

949 htmlescape() {

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

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

952 }

953 enascii() {

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

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

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

957 else

958 enascii_now=`date +%FT%T`

959 enascii_sed() {

960 nkf -Z0Z1Z2 \

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

962 }

963 enascii="enascii_sed"

964 fi

965 fi

966 cat "$@" | $enascii

967 }

968 size_h() {

969 i="$1" oi=$1

970 set -- B B KB MB GB TB

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

972 oi=$i

973 i=$((i/1024))

974 shift

975 done

976 echo ${oi}$1

977 }

978 gettblconf() {

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

980 ## tconfs=`sq $db \

981 tconfs=`query \

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

983 fi

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

985 }

986 gettblkeys() {

987 # $1=tbl

988 gettblconf

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

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

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

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

993 case $type in

994 *p*)

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

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

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

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

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

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

1001 esac

1002 case $type in

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

1004 esac

1005 done

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

1007 }

1008 gettblpkey() {

1009 # $1=tbl

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

1011 }

1012 gettblfkey() {

1013 (x=`gettblkeys $1`

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

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

1016 }

1017 gettblcols() {

1018 (x=`gettblkeys $1`

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

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

1021 }

1022 gettbl_s_cols() {

1023 (x=`gettblkeys $1`

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

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

1026 }

1027 gettbl_m_cols() {

1028 (x=`gettblkeys $1`

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

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

1031 }

1032 gettbl_h_cols() {

1033 (x=`gettblkeys $1`

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

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

1036 }

1037 gettbl_coltype() (

1038 gettblconf

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

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

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

1042 )

1043 is_hidden() {

1044 # $1=Tbl $2=col

1045 gettblconf

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

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

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

1049 case $x in

1050 *h*) return 0 ;;

1051 *) return 1 ;;

1052 esac

1053 }

1054

1055 dbsetbyid() {

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

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

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

1059 quotedp=$(sqlquotestr "$p")

1060 unset primary update

1061 gettblconf

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

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

1064 #err conf=$conf

1065 case ${conf#*=} in

1066 p*) primary=1 ;;

1067 f*) update=1 ;;

1068 u*) ;;

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

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

1071 esac

1072 #err t=$t

1073 type=string fn=""

1074 case $conf in

1075 */password)

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

1077 ;;

1078 */image*|*/document*)

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

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

1081 ;;

1082 esac

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

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

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

1086 if [ "$primary" ]; then

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

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

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

1090 elif [ "$update" ]; then

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

1092 else

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

1094 fi

1095 )

1096 }

1097 expire() (

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

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

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

1101 )

1102 addsession() {

1103 # expireをセット

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

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

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

1107 # Remove old session parameters

1108 now=`expire now`

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

1110 }

1111 gencookie() (

1112 for kv; do

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

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

1115 done

1116 )

1117 contenttype() {

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

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

1120 }

1121 putheader() {

1122

1123 }

1124 putfooter() {

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

1126 }

1127 getcookie() (

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

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

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

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

1132 done

1133 )

1134 genrandom() {

1135 # $1=columns (default: 10)

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

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

1138 }

1139 genserial() {

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

1141 }

1142 smail() {

1143 # smail rcpts subj (file)

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

1145 # $MAIL_FROM <- From: header value

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

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

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

1149 sender=${SENDER:-$admin}

1150 replyto=${REPLYTO:+"Reply-to: $REPLYTO$LF"}

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

1152 cat $3 | nkf -jd ) | sendmail -f $sender $rcpt

1153 }

1154 smail_queue_flush() {

1155 # $1=timelimit

1156 timelimit=`query "SELECT datetime('now', 'localtime', '-6 hours');"`

1157 rowids=$(sq $workdb <<-EOF

1158 SELECT rowid FROM smailq

1159 GROUP BY rcpts, subj

1160 HAVING min(time) < '$timelimit';

1161 EOF

1162 )

1163 for rid in $rowids; do

1164 sq $workdb \

1165 "SELECT hex(rcpts),hex(subj) FROM smailq WHERE rowid in ($rid)" \

1166 | if IFS='|' read hexr hexs; then

1167 # err "hexrcpt=[$hexr] hexsubj=[$hexs]"

1168 rcpt=`echo "$hexr"|unhexize`

1169 subj=`echo "$hexs"|unhexize`

1170

1171 # err ROWID==$rowid "sql=<<SELECT hex(rcpts),hex(subj) FROM smailq WHERE rowid=$rowid;>>"

1172 # err "rcpt=[$rcpt] subj=[$subj]"

1173 if sq $workdb <<-EOF | smail "$rcpt" "$subj"

1174 .separator "\n" "------------------\n\n"

1175 SELECT time, text FROM smailq

1176 WHERE rcpts=(SELECT rcpts FROM smailq WHERE rowid=$rid)

1177 AND subj=(SELECT subj FROM smailq WHERE rowid=$rid)

1178 ORDER by time;

1179 EOF

1180 then

1181 echo rowid=$rid

1182 cat <<-EOF | sq $workdb

1183 DELETE FROM smailq

1184 WHERE rcpts=(SELECT rcpts FROM smailq WHERE rowid=$rid)

1185 AND subj=(SELECT subj FROM smailq WHERE rowid=$rid);

1186 EOF

1187 fi

1188 fi

1189 done

1190 }

1191 smail_queue() {

1192 # $1=Rcpts, $@=subj

1193 now=`query "SELECT datetime('now', 'localtime');"`

1194 if [ $? -eq 0 ]; then

1195 rcpts="X'"`echo "$1"|hexize`"'"

1196 subj="X'"`echo "$2"|hexize`"'"

1197 text="X'"`cat | hexize`"'"

1198 err "rcpts=[$rcpts] s=[$subj] t=[$text]"

1199 mintime=$(cat <<-EOF | sq $workdb

1200 CREATE TABLE IF NOT EXISTS smailq(rcpts, subj, text, time);

1201 INSERT INTO smailq VALUES($rcpts, $subj, $text, '$now');

1202 SELECT min(time) FROM smailq WHERE rcpts=$rcpts AND subj=$subj;

1203 EOF

1204 )

1205 ## XXX: Adhoc load mitigation

1206 case "$now" in

1207 *[01])

1208 err flush_queue=$mintime

1209 smail_queue_flush ;;

1210 esac

1211 fi

1212 }

1213 setviastring() {

1214 table=$1

1215 oifs="$IFS"

1216 IFS="&"

1217 for us in $2; do

1218 k=${us%%=*}

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

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

1221 #echo $k=$v

1222 done

1223 IFS="$oifs"

1224 }

1225 checkdomain() (

1226 # Check the validity of domain by referring DNS

1227 item=$1

1228 err checkdomain $1

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

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

1231 )

1232 pwcheck() {

1233 # $1=passwd

1234 dbpswd=`getpwfield $user pswd`

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

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

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

1238 }

1239 mypwhash() {

1240 mycrypt "`cat`" `genrandom 5`

1241 }

1242 wasureta() {

1243 user=$1

1244 if ! checkdomain $user; then

1245 contenttype; echo

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

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

1248 putfooter

1249 exit 0

1250 fi

1251 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1253 encpswd=`echo $newpswd|mypwhash`

1254 dbsetbyid user $user pswd "$encpswd"

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

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

1257 $msgdir/mail-newaccount.m4 \

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

1259 | smail "`collectemail $user`" "New Account"

1260 }

1261 checkauth() {

1262 user=`getpar user`

1263 skc=`getpar skey` # from cookie

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

1265 skey="`getpwfield $user skey`"

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

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

1268 return 0

1269 fi

1270 fi

1271 pswd=`getpar pswd`

1272 quser=`sqlquotestr "$user"`

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

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

1275 return 1

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

1277 wasureta "$user"

1278 return 1 # wasureta error

1279 fi

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

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

1282 if pwcheck "$pswd"; then

1283 newsession=`genrandom 50`

1284 if dbsetbyid user "$user" skey "$newsession" &&

1285 dbsetbyid user "$user" login "`date '+%F %T'`"; then

1286 gencookie "user=$user" "skey=$newsession"

1287 return 0

1288 else

1289 return 4 # Heavy load??

1290 fi

1291 fi

1292 return 2 # Password mismatch

1293 }

1294 showlogin() {

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

1296 s4name=${S4NAME:-s4}

1297 ( sed '/^<body/q' $layout/html.m4.html

1298 cat $layout/login.m4.html

1299 echo '</body></html>'

1300 ) | _m4 \

1301 -D_BODYCLASS_="login" \

1302 -D_TITLE_="$s4name" \

1303 -D_SYSNAME_="Welcome to $s4name" \

1304 -D_MYNAME_="$myname${args+?}$args" ${S4CSS:+-D_S4CSS_="$S4CSS"}

1305 exit 0

1306 }

1307 dologin() {

1308 checkauth

1309 st=$?

1310 if [ $st != 0 ]; then

1311 contenttype; echo

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

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

1314 showlogin # and EXIT

1315 fi

1316 }

1317

1318 # Do instant jobs here

1319 dbsetup

1320 trap cleanup INT HUP EXIT TERM PIPE

1321 # trap cleanup INT HUP

1322

1323 err() {

1324 echo "$@" 1>&3

1325 }

1326

1327 cgiinit() {

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

1329 tmpf=tmp/stream

1330 tmpd=`tmpd=$tmpdir mktempd`

1331 tmpfiles=$tmpfiles" $tmpd"

1332 addsession $session

1333 getcookie

1334 case "$REQUEST_METHOD" in

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

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

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

1338 # cat > $tmpf # too much?

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

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

1341 s="`cat tmp/stream`"

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

1343 ;;

1344 esac

1345 case "$CONTENT_TYPE" in

1346 *boundary*)

1347 bndry=${CONTENT_TYPE#*boundary=}

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

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

1350 do

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

1352 #echo u=$us

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

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

1355 # err k=$k v=$v

1356 case "$k" in

1357 *:filename)

1358 mimetype=`file --mime-type - < "$tmpd/$v"|cut -d' ' -f2`

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

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

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

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

1363 :

1364 fi

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

1366 case "$mimetype" in

1367 [Ii]mage/x-xcf)

1368 bzip2 "$tmpd/$v"

1369 v=${v}.bz2

1370 ;;

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

1372 [Ii]mage/*)

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

1374 ;;

1375 esac

1376 if ! echo "$mimetype" | egrep "$file_accept_egrep" >/dev/null 2>&1

1377 then

1378 replpar text string " *添付できない形式です($v)* $file_warn"

1379 continue

1380 elif [ `wc -c < "$tmpd/$v"` -gt "$filesize_max" ]; then

1381 replpar text string \

1382 " *添付ファイル($v)は${filesize_max}バイト以下にしてください* $file_warn"

1383 continue

1384 fi

1385 ;;

1386 *)

1387 type='string'

1388 ;;

1389 esac

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

1391 setpar "$k" "$type" "$v"

1392 done

1393 ;;

1394 *)

1395 setviastring par "$s"

1396 ;;

1397 esac

1398 }

1399 email4group() {

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

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

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

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

1404 sql="WITH

1405 grpemails AS (

1406 SELECT gname, user, val email

1407 FROM grp_mem NATURAL JOIN grp_mem_s

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

1409 useremails AS (

1410 SELECT user.name, val email

1411 FROM user

1412 LEFT JOIN user_m

1413 ON user.name=user_m.name AND user_m.key='email')

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

1415 FROM useremails u LEFT JOIN grpemails g

1416 ON u.name=g.user

1417 WHERE u.name in ($users);"

1418 query "$sql"

1419 }

1420 email4groupbyuid() {

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

1422 g=$1; shift

1423 uids=`echo "$@"`

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

1425 sql="SELECT DISTINCT name FROM user WHERE rowid IN ($uids);"

1426 email4group "$1" `query "$sql"`

1427 }

1428 collectmembersbyid() {

1429 # Collect user names of group specified by grid

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

1431 query "SELECT user FROM grp_mem \

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

1433 }

1434 collectmembersbyid() {

1435 # Collect user names of group name

1436 qgrp=`sqlquote "$1"`

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

1438 }

1439 collectgecosesbyid() {

1440 # Collect user gecoses of group

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

1442 query<<-EOF

1443 SELECT gecos

1444 FROM gecoses

1445 WHERE name IN (SELECT user FROM grp_mem

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

1447 EOF

1448 }

1449 collectemail() (

1450 # Collect email addresses for group $1

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

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

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

1454 for e; do

1455 if isuser "$e"; then

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

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

1458 else

1459 qgrp=`sqlquote "$e"`

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

1461 gmem="grp_mem"

1462 else

1463 tm=`sqlquote "$TEAM"`

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

1465 fi

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

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

1468 $gmem g left join grp_mem_s s

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

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

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

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

1473 query "$sql"

1474 fi

1475 done

1476 )

1477 sendinvitation() (

1478 # $1=email

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

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

1481 query "DELETE FROM par WHERE var='invite' AND val='$1';"

1482 query "REPLACE INTO par VALUES('$iss', 'invite', 'string', '$1');"

1483 gecos=`gecos`

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

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

1486 _m4 -D_URL_="$urlbase" \

1487 -D_USER_="$name" \

1488 -D_EMAIL_="$1" \

1489 -D_REGIST_="$regist" \

1490 -D_ADMIN_="$admin" \

1491 $msgdir/mail-invite.m4 \

1492 | smail $1 "SNSへの御招待"

1493 return 0

1494 )

1495 emaildomaincheck() {

1496 case "$1" in

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

1498 *@*)

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

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

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

1502 return 2

1503 fi

1504 return 0

1505 ;;

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

1507 esac

1508 }

1509 invite() {

1510 email=`getpar email | tr '[A-Z]' '[a-z]'`

1511 case "$email" in

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

1513 *@*)

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

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

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

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

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

1519 elif sendinvitation $email; then

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

1521 else # Cannot be reached here

1522 repo="自動登録できない状況です。管理者に依頼してください。"

1523 fi ;;

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

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

1526 esac

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

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

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

1530 fi

1531 if [ -f $invite_policy ]; then

1532 pol="spaste(\`$invite_policy')"

1533 else

1534 pol="$invite_policy"

1535 fi

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

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

1538 -D_INVITE_POLICY_="$pol" \

1539 $layout/html.m4.html $layout/invite.m4.html

1540 }

1541 regist() {

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

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

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

1545 echo "bye bye" | html p

1546 reutrn

1547 fi

1548 email=`session=$1 getpar invite | tr '[A-Z]' '[a-z]'` # Ensure lower case

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

1550 cat<<EOF

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

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

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

1554 EOF

1555 return

1556 fi

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

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

1559 # Fake login password to wasureta

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

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

1562 wasureta $email

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

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

1565 addsession $1 # for removal after 1 minute

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

1567 $layout/login.m4.html

1568 return

1569 }

1570 group_safename() {

1571 # Convert $1 to safe group name

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

1573 }

1574 groupupdate() {

1575 gname=`getpar gname`

1576 qgname=`sqlquote "$gname"`

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

1578 # See ALSO same job in showgroup()

1579 newgname=`group_safename "$gname"`

1580 err newgname=$newgname

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

1582 err NewGNAME: gname=$newgname

1583 gname=$newgname

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

1585 replpar gname string "$gname"

1586 fi

1587 # Name confliction check

1588 parow=`getpar rowid`

1589 ## err parow=$parow

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

1591 query "BEGIN EXCLUSIVE;"

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

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

1594 if [ $count -gt 0 ]; then

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

1596 query "END;"

1597 return

1598 fi

1599 par2table $formdir/grp.def

1600 query "END TRANSACTION;"

1601 # Remove orphan

1602 : <<EOF

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

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

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

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

1607 EOF

1608 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1612 sql="delete from blog where id in

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

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

1615 query "$sql";

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

1617 return # and return

1618 fi

1619 fi

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

1621 fi

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

1623 grid=$(query $sql)

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

1625 grp $grid

1626 }

1627 groupclone() {

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

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

1630 case "$1" in

1631 */noteam)

1632 noteam="AND key != 'team'" ;;

1633 esac

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

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

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

1637 return

1638 fi

1639 if ! isgrpownerbygid "$user" "$rid"; then

1640 echo "グループ管理者のみがクローン可能です" | html p

1641 return

1642 fi

1643 i=0

1644 while true; do

1645 copy="-copy$i"

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

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

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

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

1650 i=$((i++))

1651 continue

1652 fi

1653 break

1654 done

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

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

1657 query<<-EOF

1658 BEGIN;

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

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

1661 SELECT $newqname, key, val

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

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

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

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

1666 -- Copy members and their configuration --

1667 REPLACE INTO grp_mem SELECT $newqname, user

1668 FROM grp_mem WHERE gname=$qgrp;

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

1670 FROM grp_mem_s WHERE gname=$qgrp;

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

1672 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1673 -- Copy administrators --

1674 REPLACE INTO grp_adm SELECT $newqname, user

1675 FROM grp_adm WHERE gname=$qgrp;

1676 COMMIT;

1677 EOF

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

1679 STOPCLONEMSG=1 groupconf "$newrowid"

1680 }

1681 groupman() {

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

1683

1684 GF_STAGE="grpconf"

1685 GF_STAGE=groupupdate

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

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

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

1689 -D_DUMPTABLE_="syscmd(cat)" \

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

1691 }

1692 userconf() {

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

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

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

1696 }

1697 groupconf() {

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

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

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

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

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

1703

1704 ### If user is not admin, lead to group home

1705 grp=`getgroupbyid $rowid`

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

1707 echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"

1708 return

1709 fi

1710

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

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

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

1714 html div 'class="fold"' <<-EOF

1715 `cgi_checkbox clone yes id="clone"`<label

1716 for="clone">同一メンバーで別グループを作成する</label>

1717 <div>

1718 <p>構成メンバーが同じ新規グループを作成します。</p>

1719 <table>

1720 <tr><td><a href="?groupclone+$rowid">

1721 <button>クローン作成(チームも複製)</button></a></td>

1722 <td><p>(チームなどもそのままで掲示板なしの状態から)</p></td></tr>

1723 <tr><td><a href="?groupclone+$rowid/noteam">

1724 <button>作成(チームなし)</button></a></td>

1725 <td>(チームは引き継がずメンバーのみ同じグループを作成)</td></tr>

1726 </table>

1727 <p>ボタンを押すと即作成します。不要な場合はグループ編集で

1728 削除してください。</p>

1729 </div>

1730 EOF

1731 fi

1732 }

1733 mems() {

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

1735 kwd=`getpar kwd`

1736 listmember $kwd

1737 }

1738 grps() {

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

1740 kwd=`getpar kwd`

1741 listgroup $kwd \

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

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

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

1745 $layout/form+dump.m4.html

1746 }

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

1748 gpg=`getpar grp`

1749 grid=${1:-$gpg}

1750 grp=`getgroupbyid "$grid"`

1751 ## . ./s4-blog.sh

1752 jg=`getpar joingrp`

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

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

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

1756 fi

1757 htmlheader=$layout/html.m4.html

1758 showgroup "$grid"

1759 }

1760 sql4interestblogs() {

1761 cat<<EOF

1762 CREATE TEMPORARY VIEW interestblogs AS

1763 SELECT blog.rowid rid, id, author

1764 FROM blog

1765 NATURAL JOIN

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

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

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

1769 WHEN (SELECT user FROM grp_mem

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

1771 THEN 0

1772 ELSE 1

1773 END;

1774 EOF

1775 }

1776 listnewblogsql() { # $1=user

1777 newdays=${WHATS_NEW_DAYS##*[!0-9]} # Shave non-digits

1778 newdays=${newdays%%[!0-9]*}

1779 newdays=${newdays:-14}

1780 basetime="datetime('now', 'localtime', '-${newdays} days')"

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

1782 WHERE user='$user'

1783 AND tblrowid IN

1784 ($blogreadflagrowid,

1785 $blogcutoffflagrowid)),

1786 $basetime -- "0"

1787 );"`

1788 cat<<EOF

1789 `sql4interestblogs`

1790 WITH article_ctime as (

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

1792 FROM article join article_s s using(id)

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

1794 GROUP BY id

1795 ), blog_title_owner as (

1796 SELECT blg.rid brid, id,

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

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

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

1800 ), blogall as (

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

1802 ), news as (

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

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

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

1806 FROM blogall bl

1807 LEFT JOIN

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

1809 ON bl.brid=al.tblrowid

1810 WHERE atime < bl.ctime

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

1812 LIMIT 10

1813 ) SELECT brid LINK, "新着",

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

1815 ctime, title,

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

1817 FROM news;

1818 EOF

1819 }

1820

1821 search_form() {

1822 # $1 = { author=<AUTHOR> | grp=<GROUP> }

1823 # $2(optional) = pre-input keywords

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

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

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

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

1828 @week → 最近一週間

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

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

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

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

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

1834 auth=""

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

1836 case "$1" in

1837 author=*)

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

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

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

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

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

1843 ;;

1844 grp=*)

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

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

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

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

1849 ;;

1850 esac

1851 inikwd="$2" # no need to htmlescape

1852 cat<<-EOF

1853 <div class="right">

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

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

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

1857

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

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

1860

1861 </form>

1862 </div>

1863 EOF

1864 }

1865

1866 imgsrc_cache() (

1867 # $1 = directory for cache'ing

1868 # $2 = table (user_m or grp_m)

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

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

1871 dir="$1" tbl="$2"

1872 keycond="$3"

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

1874 ORDER BY rowid DESC LIMIT 1"

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

1876 tmpf=$tmpd/imgsrc_cache.$$

1877 case "$4" in

1878 [Ss]) size=S ;;

1879 [Oo]) size=O ;;

1880 *) size=M ;;

1881 esac

1882 # ImageCache filename storing schema:

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

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

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

1886 valtype=`query "$sql0"`

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

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

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

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

1891 case "$filetype" in

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

1893 esac

1894 fi

1895 cacheimg_S=$dir/S_$filename

1896 cacheimg_M=$dir/M_$filename

1897 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1905 else

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

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

1908 fifo2=$fifo.2

1909 mkfifo $fifo $fifo2

1910 fmt=${filename##*.}

1911 ## [[ NOTE ]]

1912 ## a. convert oldimage newimage

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

1914 ## b is much smaller than a

1915 cat $tmpf | unhexize \

1916 | tee $fifo \

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

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

1919 | tee $fifo2 \

1920 | convert - "$cacheimg_M" &

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

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

1923 | convert - "$cacheimg_S" &

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

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

1926 echo '">'

1927 echo "$sum" > $sumfile

1928 fi

1929 ## Now preparing cache image, done.

1930 ## Store this information to DB

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

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

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

1934 query <<-EOF

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

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

1937 EOF

1938 )

1939

1940 showhome() {

1941 # $1=userRowIdToShow

1942 err showhome \$1=$1

1943 case "$1" in

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

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

1946 esac

1947 ## err ShowHome: uname=$uname

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

1949 gecos=`gecos "$uname"`

1950 ## err SH:gecos=$gecos

1951 GF_VIEWONLY=1

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

1953 search_form_args=""

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

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

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

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

1958 # Display folders

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

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

1961 and type like 'file:%';"

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

1963 nfile=`query "$sql"`

1964 # err nfile=$nfile

1965 if [ $nfile -gt 0 ]; then

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

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

1968 fi

1969 else

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

1971 GROUP BY user;"`

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

1973 search_form_args="author=$uname"

1974 fi

1975 . ./s4-blog.sh

1976

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

1978 search_form "$search_form_args" > $sf

1979 printf "%s さん" "$gecos" > $tf

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

1981 viewtable $formdir/user.def user $1

1982 echo "</div>"

1983 } > $pf

1984

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

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

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

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

1989 if true; then

1990 tbl=user_m

1991 enticond="name='$uname'"

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

1993 else

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

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

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

1997 echo '">'

1998 }

1999 fi > $imf

2000 fi

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

2002 val='$uname';"`

2003 ## REMOVE This comment block until 2019/7/1

2004 ## err "----- `gdate +%FT%T.%3N` ------------C"

2005 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2006 listblog $uname > $bf

2007 ## unset ddd

2008 ## err "----- `gdate +%FT%T.%3N` ------------D"

2009

2010 hometail=$tmpd/tail.$$

2011 mkfifo $hometail

2012

2013 #Calling listgroupbytable, originally here

2014

2015 (

2016 # Display Most Recent Entry

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

2018 shortval=${shortval:-val}

2019

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

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

2022 ctime,

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

2024 (SELECT gecos FROM gecoses

2025 WHERE name=(SELECT val FROM blog_s

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

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

2028 FROM blog b

2029 JOIN

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

2031 FROM article a, article_s s

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

2033 GROUP BY blogid ORDER BY val DESC LIMIT 50

2034 ) m

2035 ON b.id=m.blogid;"

2036 # This should be as follows

2037 : <<EOF

2038 WITH arts AS(

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

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

2041 FROM article a NATURAL JOIN article_s s

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

2043 GROUP by s.id

2044 )

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

2046 FROM arts a0

2047 JOIN

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

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

2050 ORDER BY ctime DESC LIMIT 50;

2051 EOF

2052

2053 cat<<-EOF

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

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

2056 <div class="lcto">

2057 `DT_VIEW=replyblog dumptable html blog`

2058 </div>

2059 EOF

2060 unset DT_SQL

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

2062 # Display NEWS

2063 # 2016-06-26

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

2065 acclog blog $blogreadflagrowid

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

2067 fi

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

2069 sql=`listnewblogsql "$user"`

2070 # echo "$sql" > tmp/listnew

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

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

2073 cont=$((cont-1))

2074 err newcount=$cont

2075 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

2082 `cgi_submit '確定'`

2083 `cgi_hidden read yes`

2084 EOF

2085 echo "$new10 "

2086 echo "</div>"

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

2088 acclog blog $blogcutoffflagrowid # for speed

2089 fi

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

2091 : # DT_SQL=

2092 fi

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

2094 #

2095 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

2101 -D_NBLOG_="$nblog" \

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

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

2104 $layout/html.m4.html $layout/home.m4.html

2105

2106 # Record access log

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

2108 }

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

2110 contenttype; echo

2111 ## err commission: "$@"

2112 gname=`getgroupbyid $1`

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

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

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

2116 grp_reg_adm "$@"

2117 else

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

2119 fi

2120 }

2121 listgroupbytable() {

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

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

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

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

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

2127 NGsql="select distinct tag from\

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

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

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

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

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

2133 for tag in `query "$sql"`

2134 do

2135 ## err ListGrp: tag=$tag

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

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

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

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

2140 as rowid,

2141 gname,

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

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

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

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

2146 ## err PersonalGroupList= `echo $sql`

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

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

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

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

2151 echo '</table>'

2152 done

2153 echo '</div>'

2154 }

2155 iconhref() (

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

2157 data=`percenthex "$1"`

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

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

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

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

2162 )

2163 iconhref2() (

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

2165 src=$1

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

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

2168 )

2169 listentry() (

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

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

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

2173 cond='' hiddens=''

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

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

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

2177 offset=$((offset-1))

2178 fi

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

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

2181 limit=$listentlimit

2182 dir=`getcachedir "$1"`

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

2184 hrb="$myname?home"

2185 deficon=person-default.png

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

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

2188 gcs=gecos

2189 else # if group

2190 hrb="$myname?grp"

2191 deficon=group-default.png

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

2193 gcs=name

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

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

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

2197 ## err tagconv=$tagconv

2198 fi

2199 fi

2200 if [ ! -d $dir ]; then

2201 mkdir -p $dir

2202 fi

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

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

2205 fi

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

2207 cond1="(nick like '%$2%' or b.name like '%$2%')"

2208 kwd=$2

2209 fi

2210 tag=`getpar tag` tag2=`getpar tag2`

2211 if [ x"$tag" = x"NULL" ]; then

2212 tag="" tag2=""

2213 fi

2214 if [ -n "$tag$tag2" ]; then

2215 tag=${tag:-$tag2}

2216 qtag=`sqlquote "$tag"`

2217 cond2="tag=$qtag"

2218 fi

2219 if [ -n "$cond1$cond2" ]; then

2220 cond="$cond1${cond2:+ AND $cond2}"

2221 cond="WHERE ${cond# AND }"

2222 fi

2223

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

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

2226 ##qgrp=`sqlquote $grp`

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

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

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

2230 quote(a.$nm) as qname,

2231 (SELECT val FROM ${tbl}_s

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

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

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

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

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

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

2238 end

2239 as name,

2240 b.tag,

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

2242 where gname=$getgrp) then '管理者'

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

2244 then 'ADMIN'

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

2246 then 'Member'

2247 when '$iamowner' = '' then ''

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

2249 CASE '$entity'

2250 WHEN 'グループ'

2251 THEN coalesce(

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

2253 'open')

2254 ||

2255 CASE WHEN '$user'

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

2257 THEN ' ismember'

2258 ELSE ''

2259 END

2260 ELSE 'user'

2261 END regmode

2262 from $tbl a left join

2263 (select $nm as name,

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

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

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

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

2268 max(case key when 'login' then val end) as login

2269 from ${tbl}_s group by $nm)

2270 b on a.$nm=b.name $cond $3

2271 order by b.wtime desc, b.login desc,

2272 b.mtime desc, b.tag desc, a.rowid asc"

2273 # Give precedence to newer maintained groups (2016-09-24)

2274 # Note that mtime is stored only in grp_s.

2275 ## err LE:sql.1="$sql"

2276 total=`query "with x as ($sql) select count(*) from x;"`

2277 echo "${entity} 一覧" | html h2

2278 echo '<div class="listentry">' # List-entry div

2279 # Show owner/member filter button

2280 METHOD=GET

2281 hiddens="$hiddens

2282 `cgi_hidden kwd \"$kwd\"`

2283 `cgi_hidden stage \"$stage\"`"

2284 if [ x"$tbl" = x"grp" ]; then

2285 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2286 fh="<select name=\"tag\">$nl"

2287 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2288 for l in $args; do

2289 val=${l#*=} tname=${l%=*}

2290 if [ x"$val" = x"$tag" ]; then

2291 s=" selected"

2292 selectedtags="(種別[${tname}]のみ)"

2293 else

2294 s=""

2295 fi

2296 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2297 done

2298 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2299 cat<<-EOF

2300 <form action="$myname" method="$METHOD">

2301 </form>

2302 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2303 for="ismembtn">参加中以外隠す</label>

2304 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2305 for="isadmbtn">管理者参加以外隠す</label>

2306 EOF

2307 # limit=3

2308 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2309 fi

2310 if [ $total -gt $limit ]; then

2311 echo '<div>'

2312 METHOD=GET cgi_form $stage <<EOF

2313 $form

2314 <label>次の語を含む${entity}で検索:

2315 `cgi_text kwd "$kwd"`</label>

2316 $hiddens

2317 EOF

2318 echo '</div>'

2319 else

2320 echo $selectedtags | html p

2321 fi

2322 cat<<EOF

2323 <form action="$myname" method="$METHOD">

2324 <p>${total}件中の<input class="hidesub" type="text" name="start"

2325 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2326 <input type="submit" value="確定"></p>

2327 </form>

2328 EOF

2329 if [ $((offset+limit)) -lt $total ]; then

2330 nextbtn=$(

2331 cat<<EOF

2332 <div class="right clear"><form action="$myname" method="$METHOD">

2333 `cgi_submit 次の${limit}件`

2334 $hiddens

2335 `cgi_hidden offset $((offset + limit))`</form></div>

2336 EOF

2337 )

2338 fi

2339 if [ $offset -gt 0 ]; then

2340 prevbtn=$(

2341 cat<<EOF

2342 <form action="$myname" method="$METHOD">

2343 `cgi_submit 前の${limit}件`

2344 $hiddens

2345 `cgi_hidden offset $((offset - limit))`</form>

2346 EOF

2347 )

2348 fi

2349 pnbtn="$nextbtn$prevbtn"

2350 echo $pnbtn

2351

2352 ## err ListEntry: `echo "$sql"\;`

2353 # sq $db here??? 2016-11-28

2354 query "$sql limit $limit ${offset:+offset $offset};" \

2355 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2356 # err name=$name owner=$ownerp lnk=$lnk

2357 # err newlnk=$lnk regmode=$regmode

2358 icondir=$dir/$id

2359 # Pick up only last icon

2360 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2361 <p class=\"tag _$tag\">$tag</p>" \

2362 | _m4 $tagconv

2363 if [ -n "$NOSPEEDUP" ]; then

2364 files=`getvalbyid $tbl profimg $id $icondir`

2365 if [ -n "$files" ]; then

2366 icon=`echo "$files"|tail -1`

2367 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2368 else

2369 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2370 fi

2371 elif [ -n "$icon" -a -s "$icon" ]; then

2372 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2373 else

2374 cond="$nm=$qname"

2375 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2376 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2377 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2378 # err "img=[$img]"

2379 if [ -n "$img" ]; then

2380 echo "<a href=\"$hrb+$lnk\">"

2381 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2382 echo "</a>"

2383 else

2384 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2385 fi

2386 fi

2387 echo "<br>$name${ownerp:+<br>($ownerp)}"

2388 echo "</div>"

2389 done

2390 echo "</div>" # End of List-entry div

2391 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2392 )

2393 listmember() {

2394 listentry user "$@"

2395 }

2396 listgroup() {

2397 listentry group "$@"

2398 }

2399 hexteams() { # $1=gname, $2(optional)=user

2400 cond=${2:+" AND user='$2'"}

2401 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2402 WHERE gname='$1' AND key='team'$cond;"

2403 }

2404 showgroup() { # $1=group-rowid

2405 if [ -z "$1" ]; then

2406 grid=`getpar grid`

2407 grid=${grid%%[!0-9]*}

2408 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2409 else

2410 grid=$1

2411 fi

2412 grp=`getgroupbyid $grid`

2413 qgrp=`sqlquote "$grp"`

2414 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2415 if isgroup "$grp"; then

2416 tf=$tmpd/title.$$

2417 sf=$tmpd/search.$$

2418 bodyclass=`query "SELECT val FROM grp_s

2419 WHERE gname=$qgrp AND key='regmode';"`

2420 if ismember "$user" "$grp"; then

2421 ismember="ismember"

2422 qgrp=`sqlquote "$grp"`

2423 bodyclass="$bodyclass${bodyclass:+ }ismember"

2424 else

2425 ismember="" # bodyclass="group"

2426 fi

2427 bodyclass="$bodyclass grouphome"

2428 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2429 echo "グループ $grp" > $tf

2430

2431 showgroupsub $formdir/grp.def "$grid" | \

2432 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2433 -D_FORM_="syscmd(\`cat')" \

2434 -D_BODYCLASS_="$bodyclass" \

2435 -D_DUMPTABLE_="" \

2436 $htmlheader $sf $layout/form+dump.m4.html

2437 # $htmlheader $layout/form+dump.m4.html

2438 # $htmlheader is defined in grp()

2439 else # if $grp is removed at par2table

2440 listgroup

2441 fi

2442 }

2443 showgroupsub() {

2444 # $1=def-file $2=group-rowid

2445 # Using $ismember

2446 rowid=$2

2447 grp=`getgroupbyid $2`

2448 qgrp=`sqlquote "$grp"`

2449 td=`getcachedir grp/"$2"`

2450 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2451 if [ -z "$rowid" ]; then

2452 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2453 #grp=`sq $db "select gname from grp where rowid=$grp"`

2454 echo "showgroupsub: invalid argument($1 $2)" | html p

2455 return

2456 fi

2457 val=`getvalbyid grp profimg $rowid $tmpd`

2458 enticond="gname=$qgrp"

2459 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2460 if [ -n "$img" ]; then

2461 cat<<-EOF

2462 <p class="groupimg">

2463 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2464 EOF

2465 fi

2466 echo "<div class=\"noprofimg\">"

2467 viewtable $1 grp $rowid

2468 echo "</div>"

2469 if isgrpowner "$user" "$grp"; then

2470 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2471 iamowner=$rowid

2472 colmd=" mode"

2473 fi

2474 if [ -n "$ismember" ]; then

2475 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2476 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2477 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2478 cat<<EOF

2479 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2480 <div class="fold clear">

2481 `cgi_checkbox send yes id="send"`<label

2482 for="send">グループ全員にメッセージ送信</label>

2483 <div>

2484 `cgi_textarea message "" "cols=60"`

2485 `cgi_submit 送信`

2486 `cgi_reset リセット`

2487 </div>

2488 `cgi_hidden grp $rowid`

2489 </div></form>

2490 EOF

2491 fi

2492 # 加入ボタン + 加入者リスト

2493 if [ -n "$ismember" ]; then

2494 ismem='checked' state="(参加中)"

2495 else

2496 nomem='checked' state="(現在非加入)"

2497 fi

2498 # このグループでの加入アドレス

2499 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2500 and key='email';"`

2501 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2502 ## and key='email';"

2503 ##err email=$eml

2504 cat <<EOF

2505 <div class="fold clear">

2506 `cgi_checkbox reg yes id="reg"`<label

2507 for="reg">自身の加入状態を操作する</label>$state

2508 <div>

2509 EOF

2510 cgi_form grp <<EOF

2511 <p>このグループに</p>

2512 <table class="b">

2513 <tr><th>メンバーとして</th><td>

2514 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2515 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2516 <tr><th>参加する場合のメイルアドレス<br>

2517 <small>(メインのアドレスとは違うものにする場合に記入<br>

2518 同じでよい場合は空欄に)</small></th>

2519 <td>`cgi_text email $eml`</td></tr>

2520 </table>

2521 `cgi_hidden grp $rowid`

2522 EOF

2523 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2524 echo "moderated (承認加入の)グループなので実際に参加できるのは

2525 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2526 fi

2527 echo '</div></div>'

2528 echo '<h2>話題一覧</h2>'

2529 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2530 cat<<-EOF

2531 <form class="summary" action="$myname" title="$thelp">

2532 `cgi_hidden owner "$grp"`

2533 `cgi_hidden kwd "@week"`

2534 `cgi_hidden stage searchart`

2535 `cgi_submit "一週間のまとめ"`

2536 </form>

2537 EOF

2538 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2539 colstate="state:稼動状態:frozen=rowclass=凍結"

2540 DT_CHLD=article:blogid \

2541 DT_QOWNER="$qgrp" \

2542 DT_VIEW=replyblog dumptable html blog \

2543 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2544

2545 getgname="(select gname from grp where rowid=$rowid)"

2546 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2547 cm="?commission+$rowid"

2548 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2549 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2550 # team list

2551 hexteams=`hexteams "$grp"`

2552 if [ -n "$hexteams" ]; then

2553 echo "チーム一覧" | html h2

2554 echo '<div class="dumptable"><table class="b">'

2555 sq $db -html -header<<-EOF

2556 SELECT val TEAM,

2557 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2558 MEMBERS

2559 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2560 EOF

2561 echo '</table></div>'

2562 fi

2563 }

2564 grp_getbodyclass() {

2565 # Get css class name for document.

2566 # `moderated' for moderated groups

2567 # `ismember' for groups where user belongs

2568 # $1=GroupName (w/o quote)

2569 # $user=userNameCurrentlyLogin

2570 ## err grp_getbodyclass: 1="$1"

2571 qgrp=`sqlquote "$1"`

2572 query<<-EOF

2573 SELECT coalesce(

2574 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2575 'open')

2576 ||

2577 CASE WHEN '$user'

2578 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2579 THEN ' ismember'

2580 ELSE ''

2581 END;

2582 EOF

2583 }

2584 grpaction() { # $1=group-rowid

2585 err GRP_ACTION:IN

2586 grid=${1:-`getpar grp`}

2587 grp=`getgroupbyid "$grid"`

2588 if [ -z "$grp" ]; then

2589 echo "無効な指定です。" | html p; return

2590 fi

2591 if ! ismember $user "$grp"; then

2592 echo "加入者のみに許可された操作です。" | html p; return

2593 fi

2594 echo "グループ $grp 個別選択操作" \

2595 | _m4 -D_TITLE_="syscmd(\`cat')" \

2596 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2597 $layout/html.m4.html

2598

2599 isowner=""

2600 isgrpowner "$user" "$grp" && isowner="yes"

2601 usel=`getpar usel`

2602 if [ -n "$usel" ]; then

2603 uids=$(echo `echo $usel`|tr ' ' ',')

2604 ## err grpaction-1: grp=$grp, `echo $sql`

2605 text=`getpar text`

2606

2607 rm=`getpar rm` cfm=`getpar confirm`

2608 ## err rm=$rm cfm=$cfm

2609 if [ x"$rm" = x"yes" ]; then

2610 if [ "$isowner" ]; then

2611 if [ x"$rm$cfm" = x"yesyes" ]; then

2612 # Eliminate

2613 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2614 for tbl in grp_mem grp_mem_s grp_mem_m; do

2615 sql="delete from $tbl $cond;"

2616 # echo "sql=$sql"

2617 query "$sql"

2618 err rmGRPuser "$sql"

2619 done

2620 num=`query "select count(*) from user where rowid in ($uids);"`

2621 #err num=$num

2622 if [ 0$num -gt 0 ]; then

2623 sql="select coalesce(b.val,a.name) from user a left join \

2624 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2625 # err `echo "$sql"`

2626 html pre<<EOF

2627 以下の${num}名のグループ $grp 登録を解除しました。

2628 `query "$sql"`

2629 EOF

2630 fi

2631 else

2632 echo "確認のチェックがないのでやめておきます。" | html p

2633 return

2634 fi

2635 else # not Group Owner

2636 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2637 return

2638 fi

2639 cat<<EOF

2640

2641 EOF

2642 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2643 if [ -z "$text" ]; then # if msg is empty

2644 echo "なにかメッセージを..." | html p

2645 return 0

2646 fi

2647 gecos=`gecos $user`

2648 mkfrom=`getpar mkfrom`

2649 if [ x"$mkfrom" = x"yes" ]; then

2650 safegc=`echo "$gecos" | tr -d '<>@'`

2651 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2652 fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2653 mail_from="$safegc <$fromad>"

2654 replyto=$fromad

2655 else

2656 mail_from="$noreply_from"

2657 fi

2658 ## SENDER=$noreply \ # Should not SENDER be set to $noreply??

2659 REPLYTO=$replyto \

2660 MAIL_FROM=$mail_from \

2661 smail "`email4groupbyuid "$grp" $usel` $user" \

2662 "$gecos さんからのメッセージ" <<EOF

2663 $url

2664 のグループ「$grp」のメンバーである $gecos さんから、

2665 あなた宛へのメッセージです。

2666 ----------------------------------------------------------

2667 $text

2668 EOF

2669 if [ $? = 0 ]; then

2670 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2671 sql="select coalesce(b.val, a.name) from

2672 (select name from user where rowid in ($uids)) a

2673 left join user_s b on a.name=b.name and b.key='gecos';"

2674 html pre<<EOF

2675 `query "$sql"`

2676 (送信者である $gecos さんも含まれます)

2677 EOF

2678 err SendDone: `echo $sql`

2679 fi

2680 elif [ x"$rm" = x"commission" ]; then

2681 grp_reg_adm $grid $usel

2682 elif [ x"$rm" = x"addteam" ]; then

2683 team=`getpar team|sed "s/'/''/g"` # for single quotation

2684 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2685 if [ x"$team" != x"$newteam" ]; then

2686 echo "チーム名に使えない文字を除去しました" | html p

2687 team=newteam

2688 fi

2689 if [ -z "$team" -o x"$team" = x"なし" ]; then

2690 cat<<-EOF | html p

2691 有効なチーム名を入力してください。

2692 カンマだけ、「なし」という名前は使えません。

2693 EOF

2694 echo "有効なチーム名を入力してください。" | html p

2695 else

2696 grp_add_team $grid "$team" $usel

2697 fi

2698 elif [ x"$rm" = x"rmteam" ]; then

2699 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2700 rmteam=`getpar rmteam|sed "s/'/''/g"`

2701 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2702 gname='$grp' AND user='$user' AND key='team'\

2703 AND val='$rmteam';\"`" ]; then

2704 grp_rm_team $grid "$rmteam" $usel

2705 else

2706 echo "所属していないチームの除去操作はできません。"|html p

2707 fi

2708 else

2709 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2710 fi

2711 fi

2712 fi

2713 # POST count summary

2714 from=`getpar from`; to=`getpar to`

2715 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2716 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2717 fromtonote="<p>POST集計: $from_input - $to_input</p>"

2718 # New entry

2719 sql="WITH mems AS (

2720 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2721 ON gm.user=g.name

2722 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2723 ), target_article AS (

2724 SELECT id FROM article_s

2725 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2726 ), posts AS (

2727 SELECT author, count(author) post

2728 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2729 WHERE blogid IN (SELECT id FROM blog_s

2730 WHERE key='owner'

2731 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2732 AND key='text'

2733 GROUP BY author

2734 ), teams AS (

2735 SELECT user, group_concat(val, ', ') team

2736 FROM grp_mem_m

2737 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2738 AND key='team'

2739 GROUP BY user

2740 ), user_post AS (

2741 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2742 FROM mems m LEFT JOIN posts

2743 ON m.name=posts.author

2744 GROUP by m.rowid

2745 )

2746 SELECT

2747 CASE

2748 WHEN (SELECT user FROM grp_adm

2749 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2750 AND user=up.name) IS NOT NULL

2751 then 'k'

2752 ELSE ''

2753 END || rowid || ',' || gecos NAME,

2754 post POST, team TEAM

2755 FROM user_post up LEFT JOIN teams t

2756 ON up.name=t.user

2757 ORDER BY gecos;"

2758 ## err grpaction: "`echo \"$sql\"`"

2759 tf=$tmpd/title.$$

2760 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2761 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2762 for=\"cmadmin\">グループ管理者委任</label>

2763 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2764 </p></div>"

2765 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2766 for=\"conf\">グループ登録解除</label>

2767 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2768 <p>この操作による通知は本人に行きません。

2769 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2770 </div>"

2771 # Get team list to which current user belongs into $hexteams

2772 allhexteams=$(hexteams "$grp")

2773 if [ -n "$isowner" ]; then

2774 myhexteams="$allhexteams" # admin can remove all teams' attr

2775 else

2776 myhexteams=$(hexteams "$grp" "$user")

2777 fi

2778 if [ -n "$myhexteams" ]; then

2779 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2780 for=\"cmrmteam\">チーム属性除去</label>

2781 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2782 を除去します: `cgi_checkbox teamconfirm yes` 確認

2783 <p>この操作による通知は本人に行きません。

2784 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2785 </div>

2786 "

2787 fi

2788 b1='<label> <input type="checkbox" name="usel" value="'

2789 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2790 #b2='"> <span>' b3='</span></label>'

2791 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2792 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2793 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2794 cgi_form grpaction<<EOF \

2795 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2796 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2797 | _m4 -D_TITLE_="spaste(\`$tf')" \

2798 -D_SUBTITLE_="チェック後操作ボタン" \

2799 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2800 $layout/form+dump.m4.html

2801 <p>下でチェックした人を対象として:</p>

2802 <div class="foldtabs">

2803 `cgi_radio rm addteam 'id="cmteam"'`<label

2804 for="cmteam">同じチーム属性を付与</label>

2805 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2806 `cgi_datalist_h teams $allhexteams`

2807 </div>

2808 ${rmteammsg}

2809 `cgi_radio rm send id="sendmsg"`<label

2810 for="sendmsg">メッセージ送信</label>

2811 <div>

2812 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2813 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2814 `cgi_textarea text "" cols=40`

2815 </div>

2816 ${isowner:+$cmmsg$excmsg}

2817 `cgi_radio rm close id="x"`<label for="x">×</label>

2818 </div>

2819 <h4>$grp 参加者一覧</h4>$fromtonote

2820 <table class="td2r">

2821 `sq $db -header -html "$sql"`

2822 </table>

2823 `cgi_hidden grp $grid`

2824 EOF

2825 }

2826 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2827 # Create TEMPORARY VIEW

2828 query<<EOF

2829 CREATE TEMPORARY VIEW writeusers AS

2830 SELECT DISTINCT author FROM article

2831 WHERE id in (

2832 select id from article where blogid=(select id from blog where rowid=$1)

2833 );

2834 CREATE TEMPORARY VIEW movablegroups AS

2835 SELECT g.rowid growid , g.gname

2836 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2837 ON grp.gname=gm.gname -- そのユーザが属している

2838 AND user='$user') g -- グループに絞る

2839 WHERE (SELECT author FROM writeusers

2840 EXCEPT

2841 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2842 IS NULL;

2843 $2

2844 EOF

2845 }

2846 sql4readableblogs() {

2847 # Create view of blogs that can be readable to $user

2848 # Blog is readable when:

2849 # 1: blog owner is an user

2850 # 2: else, 2.1: owner-group where the $user belongs

2851 # 2.2: else, owner-group is not moderated

2852 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2853 cat<<EOF ## | tee tmp/sql.out

2854 CREATE TEMPORARY VIEW readableblogs AS

2855 SELECT blog.rowid rid, id, author

2856 FROM blog

2857 NATURAL JOIN

2858 (SELECT id,

2859 max(CASE key WHEN 'owner' THEN val END) owner,

2860 max(CASE key WHEN 'mode' THEN val END) mode

2861 FROM blog_s GROUP by id) bs

2862 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2863 THEN 1 -- blog owner is an user, READABLE

2864 WHEN (SELECT val FROM grp_s

2865 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2866 AND

2867 (SELECT user FROM grp_mem

2868 WHERE gname=bs.owner AND user='$user') IS NULL

2869 THEN 0

2870 WHEN mode IN ('quiz', 'enquete')

2871 THEN 0 -- "quiz" mode blog cannot be searched

2872 ELSE 1

2873 END;

2874 EOF

2875 }

2876 editheading() { # $1=rowid-of-heading

2877 rowid=${1%%[!A-Z0-9a-z_]*}

2878 if [ -z "$rowid" ]; then

2879 echo "話題番号が未指定です。" | html p

2880 return

2881 fi

2882 owner=`getvalbyid blog owner $rowid`

2883 title=`getvalbyid blog title $rowid`

2884 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2885 | _m4 -D_TITLE_="修正" \

2886 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2887 -D_BLOGS_="" -D_DUMPTABLE_="" \

2888 -D_FORM_="syscmd(\`cat')" \

2889 $layout/html.m4.html $layout/form+dump.m4.html

2890 # Move to group

2891 if isuser "$owner"; then

2892 crview4article $rowid

2893 n=`query "SELECT count(*) FROM writeusers;"`

2894 ## err N=$n

2895 if [ $((n)) -gt 0 ]; then

2896 ## err ROWID=$rowid

2897 sql="SELECT growid || ':' || gname FROM movablegroups;"

2898 cat<<-EOF

2899 <div class="fold">

2900 `cgi_checkbox mv send id="mv"`<label

2901 for="mv">この話題をグループ所有に移動する</label>

2902 <div>

2903 <form action="?mvart" method="POST" enctype="multipart/form-data">

2904 移動先グループ:

2905 <select name="mv2grp">

2906 EOF

2907 query ".mode html"

2908 query<<-EOF |

2909 $sql

2910 .mode list

2911 EOF

2912 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2913 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2914 cat<<-EOF

2915 </select>

2916 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2917 そのグループに加入しているものに限られます)</p>

2918 <p>`cgi_checkbox cfm yes`<label>確認

2919 (この操作は元に戻すことができません)</label></p>

2920 `cgi_hidden blogrowid $rowid`

2921 `cgi_submit 移動`

2922 `cgi_reset Reset`

2923 </form>

2924 </div>

2925 </div>

2926 EOF

2927 fi

2928 # end of isuser "$owner"

2929 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2930 [ -n "$hexteams" ];}; then

2931 none="`echo なし|hexize`"

2932 cat<<-EOF

2933 <div class="fold">

2934 `cgi_checkbox mv2team send id="mv2team"`<label

2935 for="mv2team">この話題を以下のチームのものにする</label>

2936 <div><p>現在の所属チーム設定:

2937 `query "SELECT

2938 coalesce((SELECT val FROM blog_s

2939 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2940 AND key='team'),

2941 ':なし');"`</p>

2942 <form action="?mvart" method="POST" enctype="multipart/form-data">

2943 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2944 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2945 `cgi_hidden blogrowid $rowid`<br>

2946 `cgi_submit 移動`

2947 `cgi_reset Reset`

2948 </form></div></div>

2949 EOF

2950 fi

2951 }

2952 mvart() { # move diary to some group or team

2953 # or move blog of group to team which belong to the group

2954 blogrowid=`getpar blogrowid`

2955 cfm=`getpar cfm`

2956 ##### echo move blog:$blogrowid to $mv2grp | html p

2957 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2958 . ./s4-blog.sh

2959 if [ -z "$blogrowid" ]; then

2960 echo "無効な指定です(mvart)。" | html p

2961 return

2962 elif [ x"$cfm" != x"yes" ]; then

2963 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2964 elif { mv2grp=`getpar mv2grp`

2965 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2966 [ -n "$mv2grp" ]; }; then

2967 crview4article $blogrowid

2968 ########## TRANSACTION BEGIN

2969 query "BEGIN;"

2970 n=`query "SELECT count(*) FROM writeusers;"`

2971 ## err Nwriteuser=$n

2972 if [ $((n)) -gt 0 ]; then

2973 query<<-EOF

2974 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2975 WHERE key='owner'

2976 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2977 AND $mv2grp IN (SELECT growid FROM movablegroups);

2978 EOF

2979 fi

2980 query "END;"

2981 ########## TRANSACTION END

2982 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2983 [ -n "$mv2team" ];}; then

2984 # blog owner can move it to ANY team

2985 case "$mv2team" in

2986 'なし')

2987 cat<<-EOF

2988 DELETE FROM blog_s

2989 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2990 AND key='team';

2991 EOF

2992 ;;

2993 "") ;;

2994 *)cat<<-EOF

2995 BEGIN;

2996 REPLACE INTO blog_s(id, key, val)

2997 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2998 'team', '$mv2team');

2999 REPLACE INTO blog_s(id, key, val)

3000 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3001 'notify', 'all'); -- Change notify to all

3002 END;

3003 EOF

3004 esac | query

3005 fi

3006 blog_reply $blogrowid

3007 echo yes | html p

3008 }

3009 editart() { # $1=article-rowid $2=blogrowid

3010 rowid=${1%%[!A-Z0-9a-z_]*}

3011 blogrowid=${2%%[!A-Z0-9a-z_]*}

3012 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3013 echo "表示する記事番号が未指定です。" | html p

3014 return

3015 fi

3016 owner=`getvalbyid blog owner $blogrowid`

3017 title=`getvalbyid blog title $blogrowid`

3018 author=`getvalbyid article author $rowid`

3019 ## err EDITart: owner=$owner, author=$author

3020 if isgrpowner "$user" "$owner"; then

3021 : EDIT OK

3022 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3023 echo "本人か所有者しか編集できません." | html p

3024 return

3025 fi

3026 aid=`query "select id from article where rowid=$rowid;"`

3027 tmpout=$tmpd/editart.$$.out

3028 GF_ACTION="?replyblog+$blogrowid#$aid" \

3029 edittable $formdir/article.def article $rowid \

3030 > $tmpout

3031 rm -f /tmp/editart.out

3032 # Cannot use pipelining to m4 with genform() because of stdin stack

3033 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3034 -D_FORM_="syscmd(cat $tmpout)" \

3035 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3036 -D_BLOGS_= -D_DUMPTABLE_= \

3037 $layout/html.m4.html $layout/form+dump.m4.html

3038 }

3039 send2mem() {

3040 rowid=`getpar grp`

3041 rowid=${rowid%%[!0-9]*} # Cleaning

3042 if [ -z "$rowid" ]; then

3043 echo "グループが未指定です。" | html p

3044 return

3045 fi

3046 message=`getpar message`

3047 if [ -z "$message" ]; then

3048 echo "文章を入れてください。" | html p

3049 return

3050 fi

3051 grp=`getgroupbyid $rowid`

3052 members=`collectemail $grp`

3053 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3054 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3055 # smail rcpt subj (file)

3056 MAIL_FROM=$mailfrom \

3057 SENDER=$noreply \

3058 REPLYTO=$mailfrom \

3059 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$noreply>" \

3060 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

3061 $urlbase?grp+$rowid

3062 グループ $grp に所属する

3063 `gecos $user` さんよりメッセージ:

3064

3065 $message

3066 EOF

3067 cat<<EOF

3068 <p>以下のユーザに送信しました。</p>

3069 <pre>

3070 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3071 </pre>

3072 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3073 EOF

3074 }

3075 joingrpadmit() {

3076 # $1=yes/no $2=session-key

3077 if [ -z "$2" ]; then

3078 echo "bye bye" | html p; return

3079 fi

3080 t_usr=`session=$2 getpar user`

3081 t_grp=`session=$2 getpar group`

3082 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3083 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3084 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3085 echo "無効な加入依頼です。" | html p

3086 echo "有効期限が切れたか、

3087 他の管理者がいる場合は処理済みの可能性があります。" | html p

3088 return

3089 fi

3090 if ! isgrpowner "$user" "$t_grp"; then

3091 echo "グループ管理者のみの機能です。" | html p; return

3092 fi

3093 case $1 in

3094 yes) joingrp "$t_grp" "$t_usr" yes ;;

3095 no) joingrp "$t_grp" "$t_usr" no ;;

3096 *)

3097 echo "無効な指定です($1)。" | html p

3098 return ;;

3099 esac

3100 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3101 rcpts="`getgroupadminmails $t_grp` $user"

3102 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3103 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3104 に

3105 $t_usr

3106 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3107 しました。"

3108 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3109 query "delete from session where id='$2';"

3110 echo "$body" | html p

3111 }

3112

3113 joingrprequest() {

3114 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3115 jss="joingrp-`date +%s`-`genrandom 12`"

3116 addsession $jss +${memoplimitdays}days

3117 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3118 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3119 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3120 $url

3121 $user さんから

3122 グループ $1

3123 に加入依頼がありました。

3124

3125 承認する:

3126 $urlbase?joingrpadmit+yes+$jss

3127

3128 白紙に戻す:

3129 $urlbase?joingrpadmit+no+$jss

3130 EOF

3131 echo "管理者に加入依頼を出しました。

3132 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3133 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3134 }

3135 joingrp() {

3136 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3137 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3138 if isgrpowner "$user" "$1"; then

3139 isowner="yes"

3140 elif [ -n "$5" ]; then

3141 isowner="yes"

3142 else

3143 isowner=""

3144 fi

3145 ## err jg:isgrpowner: isowner="$isowner"

3146 if [ -n "$isowner" ]; then

3147 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3148 elif [ x"$2" != x"$user" ]; then # if user is not login user

3149 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3150 return

3151 elif [ x"$3" = x"no" ]; then

3152 : # Do not pursue those who leave

3153 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3154 : # Member can change own email address for the joining moderated group

3155 else # adding user is $user itself

3156 case `getgroupattr $1 regmode` in

3157 moderated)

3158 joingrprequest "$@" # Request only

3159 return

3160 ;;

3161 *)

3162 ;;

3163 esac

3164 fi

3165 qgname=`sqlquote "$1"`

3166 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3167 cond="where gname=$qgname and user='$2'"

3168 if [ x"$3" = x"yes" ]; then

3169 query "replace into grp_mem values($qgname, '$2');"

3170 # Notify joingrp to admin

3171 action="に加入しました。"

3172 if [ -n "$4" ]; then

3173 if msg=`emaildomaincheck "$4"`; then

3174 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3175 'string', '$4', NULL);"

3176 else

3177 echo $msg

3178 fi

3179 else

3180 query "delete from grp_mem_s $cond and key='email';"

3181 fi

3182 if [ -n "$5" ]; then # as ADMIN

3183 # Coming here means newly created group

3184 sql="select case\

3185 when (select count(*) from grp_mem where gname=$qgname)=1\

3186 then (select user from grp_mem\

3187 where gname=$qgname and user='$user')\

3188 else '' end; "

3189 err NewGrpChk: $sql

3190 if [ -n "`query \"$sql\"`" ]; then

3191 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3192 query "replace into grp_adm values($qgname, '$user');"

3193 fi

3194 fi

3195 else

3196 query "begin;

3197 delete from grp_mem $cond;

3198 delete from grp_mem_s $cond;

3199 delete from grp_mem_m $cond;

3200 delete from grp_adm $cond;

3201 delete from grp_adm_s $cond;

3202 delete from grp_adm_m $cond;

3203 end;"

3204 action="から脱退しました。"

3205 fi

3206 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3207 $url?grp+$grid

3208 $user (`gecos $user`)さんが

3209 グループ $1

3210 $action

3211 EOF

3212 }

3213 grp_add_team() (

3214 # $1=grp-rowid $2=team $3...=user-rowid(s)

3215 grp=`getgroupbyid $1`

3216 team=$2; shift; shift

3217 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3218 { echo "BEGIN;"

3219 for user; do

3220 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3221 '$grp',\

3222 (SELECT name FROM user WHERE rowid=$user),\

3223 'team', 'string', '$team');"

3224 done

3225 echo "END;"

3226 } | query

3227 )

3228 grp_rm_team() (

3229 # $1=grp-rowid $2=team $3...=user-rowid(s)

3230 grid=$1

3231 qgrp=$(sqlquote "`getgroupbyid $grid`")

3232 team=$2; shift; shift

3233 [ -z "$grid" -o -z "$team" ] && return

3234 { echo "BEGIN;"

3235 for user; do

3236 echo "DELETE FROM grp_mem_m\

3237 WHERE gname=$qgrp \

3238 AND user=(SELECT name FROM user WHERE rowid=$user)\

3239 AND key='team' AND val='$team';"

3240 done

3241 cat<<-EOF

3242 DELETE FROM blog_s

3243 WHERE rowid=(

3244 SELECT rowid

3245 FROM blog_s a

3246 WHERE key='team'

3247 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3248 AND NOT EXISTS (SELECT * FROM grp_mem_m

3249 WHERE key='team' AND val=a.val -- a.val=team

3250 AND gname = (SELECT val FROM blog_s b

3251 WHERE a.id=b.id AND key='owner')

3252 ));

3253 EOF

3254

3255 echo "END;"

3256 } | query

3257 )

3258 grp_reg_adm() {

3259 # $1=grp-rowid $2...=user-rowid

3260 grid=$1

3261 grp=`getgroupbyid "$1"`

3262 if [ -z "$grp" ]; then

3263 echo "無効なグループIDです" | html p; return

3264 fi

3265 if ! isgrpowner "$user" "$grp"; then

3266 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3267 fi

3268 shift

3269 for urid; do

3270 newadm=`query "select name from user where rowid=$urid;"`

3271 if [ -z "$newadm" ]; then

3272 echo "指定ユーザIDがおかしいようです。" | html p; return

3273 fi

3274 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3275 err ismember $newadm $grp

3276 if ismember $newadm "$grp"; then

3277 # OK, go ahead

3278 getgname="(select gname from grp where rowid=$grid)"

3279 query "replace into grp_adm values($getgname, '$newadm');"

3280 # confirm insertion

3281 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3282 if [ -n "`query \"$sql;\"`" ]; then

3283 echo "追加完了: $newadm" | html p

3284 else

3285 echo "追加失敗($1 $urid)" | html p

3286 fi

3287 fi

3288 showgroup $grid

3289 done

3290 }

3291 dt_rowhack() {

3292 # From: <TR>

3293 # ....

3294 # <TD>rowclass=foo</TD>

3295 # </TR>

3296 # To: <TR class="foo">....<TD>foo</TD></TR>

3297 sed -e '

3298 /^<TR>/ {

3299 :loop

3300 s/\n//

3301 N

3302 /<\/TR>/ {

3303 s/\n//

3304 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3305 n

3306 }

3307 $q

3308 b loop

3309 }'

3310 }

3311 dumptable() {

3312 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3313 # textのフィールドだけ全てダンプにしたほうがいいか

3314 # $DT_VIEW sets link

3315 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3316 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3317 VIEW=${DT_VIEW-replyblog}

3318 if [ -n "$VIEW" ]; then

3319 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3320 fi

3321 sqlfile=$tmpd/dump.sql

3322 : > $sqlfile # ensure to be empty

3323 printf '.mode html\n.header 1\n' > $sqlfile

3324 # $DT_CHLD=ChildTable:BindColumn

3325 if [ -n "$DT_CHLD" ]; then

3326 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3327 cat<<-EOF >> $sqlfile

3328 -- presql

3329 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3330 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3331 EOF

3332 # Speed up counting of new articles

3333 cat<<-EOF >> $sqlfile

3334 -- presql2

3335 DROP TABLE IF EXISTS _counts;

3336 CREATE TEMPORARY TABLE _counts AS

3337 SELECT $_i, count($_i) cnt

3338 FROM $_t GROUP BY $_i;

3339 /* Prepare NEW count table */

3340 CREATE TEMPORARY TABLE _target AS

3341 SELECT b.rowid trowid, b.id

3342 FROM "$2" b JOIN "$2_s" s

3343 ON b.id=s.id AND s.key='owner'

3344 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3345

3346 DROP TABLE IF EXISTS _children;

3347 CREATE TEMPORARY TABLE _children AS

3348 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3349 FROM (SELECT t.trowid, t.id $_i, a.id

3350 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3351 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3352

3353 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3354 DROP TABLE IF EXISTS _news;

3355 DROP VIEW IF EXISTS _news;

3356

3357 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3358 -- INSERT INTO _news

3359 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3360 CREATE TEMPORARY VIEW _news AS

3361 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3362 FROM (SELECT DISTINCT id FROM _target)

3363 a LEFT JOIN

3364 (SELECT $_i, count(ctime) newcnt

3365 FROM _children x

3366 WHERE ctime > coalesce((SELECT time from myacclog

3367 WHERE tblrowid=x.trowid),

3368 '1970-01-01')

3369 GROUP BY $_i) b

3370 ON a.id=b.$_i;

3371 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3372 EOF

3373 # REMOVE next line until 2019/5/1

3374 cntall="(select count($_i) from $_t where $_i=a.id)"

3375 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3376 # REMOVE next assignment until 2019/5/1

3377 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3378 and id in (select id from $_t where $_i=a.id) \

3379 and val > coalesce((select time from myacclog where \

3380 tblrowid=a.rowid),\

3381 '1970-01-01'))"

3382 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3383 cnt="$cntnew as '新着', $cntall as '総数',"

3384 dt_class=" td2r td3r dumpblogs"

3385 fi

3386 # Construct join expression

3387 eav="" scols=""

3388 pk=`gettblpkey $2`

3389 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3390 substr=${substr:-%s}

3391 for col in ${3:-`gettbl_s_cols $2`}; do

3392 valvar=val

3393 case $col in

3394 gecos) scols="$scols${scols:+, }${col#}"

3395 continue ;; # built-in column name

3396 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3397 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3398 case "$as" in

3399 *:*=*) cnd=${as#*:}

3400 h=${cnd%%=*} v=${cnd#*=}

3401 h=`sqlquotestr "$h"`

3402 v=`sqlquotestr "$v"`

3403 valvar="CASE val WHEN $h THEN $v END"

3404 as=${as%%:*} ;;

3405 esac

3406 ;;

3407 *) as=${col} ;;

3408 esac

3409 ss=`printf "$substr" "$valvar"`

3410 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3411 scols="$scols${scols:+, }b.$as"

3412 done

3413 #case author when '$user' then a.rowid else '---' end as ID,

3414 if [ -n "$DT_SQL" ]; then

3415 echo "$DT_SQL"

3416 else

3417 cat<<-EOF

3418 SELECT a.rowid as LINK, $cnt $scols

3419 FROM $2 a LEFT JOIN

3420 (SELECT $pk,$eav,

3421 max(CASE key

3422 WHEN 'owner'

3423 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3424 as gecos

3425 FROM ${2}_s c GROUP BY $pk)

3426 b ON a.$pk=b.$pk $4;

3427 EOF

3428 fi >> $sqlfile

3429 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3430 sqlog<<-EOF

3431 *** SQL-file: $sqlfile ***

3432 `cat $sqlfile`

3433 EOF

3434 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3435 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3436 cat $sqlfile >> tmp/sql

3437 # query ".read $sqlfile" > $tmpd/foo

3438 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3439 cp $tmpd/foo tmp/

3440 err "----- `gdate +%FT%T.%3N` ------------555555"

3441 ## $ddd LINE exists at the end of this function

3442 fi

3443 printf '.mode list\n.header 0\n' >> $sqlfile

3444 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3445 <div>

3446 <div class="dumptable">

3447 <table class="b$dt_class">

3448 `query ".read $sqlfile"`

3449 </table>

3450 </div>

3451 </div>

3452 EOF

3453 ### `query ".read $sqlfile"`

3454 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3455

3456 ## REMOVE THIS!

3457 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3458 }

3459

3460 par2table() (

3461 # copy current parameters of par into destination table

3462 # $1=definition-file

3463 # Using $user and $session

3464 # Return value:

3465 # 0: Stored successfully

3466 # 1: Insufficient fillings

3467 # 2: No permission to modify the record

3468 # 3: Invalid rowid

3469 # 4: SUCCESS to delete

3470 # 5: Stop deletion for lack of confirm check

3471 # 6: Password length too short

3472 # 7: Password mismatch

3473 # 8: Old password incorrect

3474 rowid=`getpar rowid`

3475 if [ ! -e $1 ]; then

3476 echo "テーブル定義ファイルが見付かりません" | html p

3477 exit 1

3478 fi

3479 tbl=${1%.def}

3480 tbl=${tbl##*/}

3481 if [ -n "$rowid" ]; then # Modify existing entry

3482 if [ x"$tbl" = x"user" ]; then

3483 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3484 elif [ x"$tbl" = x"grp" ]; then

3485 sql="select gname from $tbl where rowid=$rowid;"

3486 ##err p2t:grp:q $sql

3487 isgrpowner "$user" "`query $sql`" && rowowner=$user

3488 elif [ x"$tbl" = x"blog" ]; then

3489 # Check if owner in blog_s

3490 blogowner=`getvalbyid blog owner "$rowid"`

3491 if isgrpowner "$user" "$blogowner"; then

3492 rowowner=$user

3493 else

3494 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3495 fi

3496 else

3497 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3498 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3499 fi

3500 ### err rowowner=$rowowner

3501 if [ x"$user" != x"$rowowner" ]; then

3502 echo "他人のレコードはいじれないの" | html p

3503 return 2

3504 elif [ -z "$rowowner" ]; then

3505 echo "指定したレコードはないみたい" | html p

3506 return 3

3507 fi

3508 rm=`getpar rm` cfm=`getpar confirm`

3509 # Editing existent entry

3510 if [ x"$rm" = x"yes" ]; then

3511 if [ x"$rm$cfm" = x"yesyes" ]; then

3512 query "delete from $tbl where rowid=$rowid;"

3513 return 4

3514 else

3515 echo "消去確認のチェックがないので消さなかったの..." | html p

3516 return 5

3517 fi

3518 fi

3519 fi

3520

3521 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3522 if [ -n "$rowid" ]; then

3523 # Update of existing record

3524 for col in `gettblcols $tbl`; do

3525 val=`getparquote $col`

3526 [ -z "$val" ] && continue

3527 ## err query "update $tbl set $col=$val where rowid=$rowid"

3528 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3529 sql="update $tbl set $col=$val where rowid=$rowid;"

3530 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3531 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3532 ## User name cannot be changed with interface provided with this

3533 ## script. But we offer the trigger to change owner user

3534 ## of blog_s table.

3535 #err "select quote($col) from $tbl where rowid=$rowid;"

3536 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3537 cat<<-EOF | query

3538 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3539 -- should use EXCLUSIVE transaction outside of this.

3540 SAVEPOINT par2table;

3541 $sql

3542 update blog_s set val=$val

3543 where key='owner' and val=$old;

3544 RELEASE SAVEPOINT par2table;

3545 EOF

3546 ## XX: DIRTY Hack Ends here

3547 ## We should keep blog's owner as a single column which has

3548 ## foreign key constraint with primary key of grp/user.

3549 else

3550 query "$sql"

3551 fi

3552 done

3553 # Then, set up $pval for further insertion of tbl_s and tbl_m

3554 for col in `gettblpkey $tbl`; do

3555 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3556 pval="$pval${pval:+, }\"$val\""

3557 done

3558 else

3559 # New entry

3560 # Generate values() for primary keys

3561 for col in `gettblpkey $tbl`; do

3562 # Genuine primary keys for _m and _s

3563 val=`getvalquote $tbl $col`

3564 [ -z "$val" ] && continue

3565 pval="$pval${pval:+, }$val"

3566 done

3567 ##err pval=$pval

3568 for col in `gettblfkey $tbl`; do

3569 # args for values() to insertion into master table

3570 val=`getvalquote $tbl $col`

3571 [ -z "$val" ] && continue

3572 formaster=$formaster"${formaster:+, }$val"

3573 done

3574 formaster="$pval${formaster:+, }$formaster"

3575 ## err formaster=$formaster

3576 if [ -z "$formaster" ]; then

3577 echo "項目を全て埋めてください" | html pre

3578 return 1

3579 fi

3580 ## err "replace into $tbl values($formaster);"

3581 query "replace into $tbl values($formaster);"

3582 ## Insertion to master table, done

3583 fi

3584

3585 transaction=$tmpd/sqlfile.sql; touch $transaction

3586 for kt in s m; do

3587 tb2=${tbl}_$kt

3588 for col in `gettbl_${kt}_cols $tbl`; do

3589 ptype=`getpartype $col "limit 1"`

3590

3591 # First, check update of existing entries in _m

3592 if [ $kt = m ]; then

3593 # sessID|address.1.22|string|Somewhere-x.y.z

3594 sql=""

3595 ##err dots from query "select var from par where var like '$col.%';"

3596 for v in `query "select var from par where var like '$col.%';"`; do

3597 # v=address.1.22

3598 st_rowid=${v##*.}

3599 origcol=${v%%.*} # original column derived from

3600 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3601 ##case `getpartype $v` in

3602 ## err CASE `gettbl_coltype $tbl/$origcol` in

3603 ## err edit flag = `getpar action.$v`

3604 case `getpar action.$v` in

3605 rm)

3606 if [ x`getpar confirm.$v` = x"yes" ]; then

3607 newsql="delete from $tb2"

3608 else

3609 echo "削除確認未チェック" | html p

3610 fi ;;

3611 edit)

3612 case `gettbl_coltype $tbl/$origcol` in

3613 image|document|binary)

3614 file=$tmpd/`getparfilename $v`

3615 ## err type=file=$file

3616 [ -z "$file" ] && continue

3617 bn=`sqlquotestr "${file##*/}"`

3618 bin="X'"$(hexize "$file")"'"

3619 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3620 type=\"file:$ct\"

3621 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3622 cachedir=`getcachedir "$tbl/$rowid"`

3623 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3624 rm -rf $cachedir

3625 ;;

3626 *)

3627 newsql="update $tb2 set val=(select val from par where var \

3628 like '$col.%.$st_rowid')"

3629 ;;

3630 esac

3631 ;;

3632 *) # maybe "keep", do not modify value

3633 continue

3634 ;;

3635 esac

3636 # err newsql=$newsql

3637 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3638 done

3639

3640 if [ x"$bin" = x"NULL" ]; then

3641 ## err repl:normal sql=`echo $sql`

3642 if [ -n "$transaction" ]; then

3643 cat<<-EOF >> $transaction

3644 $sql

3645 DELETE FROM $tb2 WHERE type='string' AND val='';

3646 EOF

3647 else

3648 query "$sql

3649 delete from $tb2 where type='string' and val='';"

3650 ## err repl:normal done

3651 fi

3652 else

3653 # Binary update line is TOO LONG to pipelining

3654 sqlfile="$tmpd/sqlf.$$"

3655 if [ -n "$transaction" ]; then

3656 echo "$sql" >> $transaction

3657 else

3658 echo "$sql" > $sqlfile

3659 query ".read $sqlfile"

3660 fi

3661 fi

3662 # Rest of kt==m: set multiple mode

3663 nr=`getparcount $col`

3664 else

3665 nr=1 # for kt==s, number of records is 1

3666 fi

3667

3668 i=0

3669 while [ $i -lt $nr ]; do

3670 limit="limit 1 offset $i"

3671 i=$((i+1)) # increase beforehand against continue

3672 val=`getvalquote $tbl $col "$limit"`

3673 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3674 ## err $col=$val

3675 bin=NULL

3676 ## err partype$col=`getpartype $col "$limit"`

3677 case $ptype in

3678 file) file=$tmpd/`getparfilename $col "$limit"`

3679 ## err parfile-$col=$file

3680 [ -z "$file" ] && continue

3681 bin="X'"$(hexize "$file")"'"

3682 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3683 type=\"file:$ct\" ;;

3684 "*"*) continue ;; # foreign table

3685 *) type=\"string\" ;;

3686 esac

3687 case `gettbl_coltype $tbl/$col` in

3688 password) # special care for password

3689 # name={password,pswd1,pswd2}

3690 p1=`getpar pswd1 "$limit"`

3691 if [ -z "$p1" ]; then

3692 continue # SKIP password setting, if p1 is empty

3693 else

3694 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3695 ## err pswd=$pswd

3696 if pwcheck "$pswd"; then

3697 if [ x"$p1" = x"$p2" ]; then

3698 case "$p1" in

3699 ??????????*) ;;

3700 *) echo "パスワードは10字以上にしてください。" | html p

3701 return 6;;

3702 esac

3703 val="\"`echo $p1|mypwhash`\""

3704 else

3705 echo "2つの新パスワード不一致" | html p

3706 return 7

3707 fi

3708 else

3709 echo "旧パスワード違います" | html p

3710 return 8

3711 fi

3712 fi

3713 ;;

3714 esac

3715 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3716 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3717 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3718 if [ x"$bin" = x"NULL" ]; then

3719 ## err Normal-query: `echo $sql`

3720 if [ -n "$transaction" ]; then

3721 echo "$sql" >> $transaction

3722 else

3723 query "$sql"

3724 fi

3725 else

3726 sqlfile="$tmpd/query.$$"

3727 ## err sqlfile=`ls -lF $sqlfile`

3728 if [ -n "$transaction" ]; then

3729 echo "$sql" >> $transaction

3730 else

3731 echo "$sql" > $sqlfile

3732 query ".read $sqlfile"

3733 fi

3734 fi

3735 ## err p2t done

3736 done

3737 done

3738 done

3739 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3740 -- We cannot use transaction here, because groupupdate may use it.

3741 SAVEPOINT pa2table_insert;

3742 .read $transaction

3743 RELEASE SAVEPOINT pa2table_insert;

3744 EOF

3745 return $?

3746 ##err donee

3747 )

3748 genform() {

3749 # $1 = form definition file

3750 # $2, $3 (optional)= table name and ROWID

3751 # If $GF_VIEWONLY set and nonNull, output values without form

3752 # If $GF_ARGS set, use it as content-strings in the form

3753 # If $GF_OWNER set, use it as value of name="owner"

3754 # If $GF_STAGE set, use it as value of name="stage"

3755 forms="" hiddens="" rowid=$3

3756 if [ ! -e "$1" ]; then

3757 echo "そのようなデータベースはないようです($2)。" | html p

3758 return

3759 elif [ -n "$2" ]; then

3760 rec=`query "select * from $2 where rowid='$rowid';"`

3761 if [ -z "$rec" ]; then

3762 pk=`gettblpkey $2`

3763 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3764 rec=`query "select rowid from $2 where $pk='$rowid';"`

3765 rowid=$rec

3766 rec=$3

3767 fi

3768 if [ -z "$rec" ]; then

3769 echo "そんなレコードはないみたいね..." | html p

3770 return

3771 fi

3772 fi

3773 if [ -z "$GF_VIEWONLY" ]; then

3774 rm='<input id="rm" name="rm" type="checkbox"

3775 value="yes"><label for="rm">このエントリの削除</label>

3776 <span>ほんとうに消しますよ(確認)!

3777 <input name="confirm" type=checkbox value="yes">はい</span>'

3778 fi

3779 # Image Cache dir

3780 ## err genform: getcache=$2/$rowid

3781 td=`getcachedir "$2/$rowid"`

3782 while IFS=: read prompt name keytype type args; do

3783 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3784 sp="${args:+ }"

3785 form="" val=""

3786 if [ -n "$rowid" ]; then

3787 # err genform2a: Seeking for "$2.$name, type=$type"

3788 rawval=`getvalbyid $2 $name $rowid $td`

3789 val=`echo "$rawval"|htmlescape`

3790 ## err genform3a: getvalbyid $2 $name $rowid $td

3791 ## err genform3b: val="[$val]" type="$type"

3792 fi

3793 if [ -n "$GF_VIEWONLY" ]; then

3794 is_hidden "$2" "$name" && continue

3795 fi

3796 case "$type" in

3797 text*)

3798 cgiform=cgi_multi_$type

3799 if [ -s $td/$name.count -a -n "$val" ]; then

3800 form=`$cgiform $name $td`

3801 val=$(echo "$val"|

3802 while read fn; do

3803 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3804 </td></tr>$nl"

3805 done)

3806 val="<table>$nl$val$nl</table>"

3807 else

3808 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3809 form=`cgi_$type $name "$rawval" "$args"`

3810 fi

3811 ;;

3812 [Rr][Aa][Dd][Ii][Oo])

3813 fh="<label><input type=\"radio\" name=\"$name\""

3814 form="`echo $args|sed -e \

3815 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3816 ;;

3817 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3818 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3819 ;;

3820 [Ss][Ee][Ll][Ee][Cc][Tt])

3821 fh="<select name=\"$name\">$nl"

3822 form=$(for l in $args; do

3823 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3824 done)

3825 if [ -n "$val" ]; then

3826 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3827 fi

3828 form="$fh$form</select>"

3829 ;;

3830 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3831 if [ -s $td/$name.count ]; then

3832 form=`cgi_multi_file $name $td "$args"`

3833 if [ -n "$val" ]; then

3834 hrfb="$myname?showattc+$2_m"

3835 val=$(echo "$rawval" \

3836 | while read fn; do

3837 data=`percenthex "$td/$fn"`

3838 #ct=`cat $td/$fn.content-type`

3839 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3840 ri=`cat "$td/$fn.rowid"`

3841 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3842 #imgsrc="<img src=\"data:$ct,$data\">"

3843 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3844 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3845 done)

3846 fi

3847 else

3848 form="<input type=\"file\" name=\"$name\" $args>"

3849 if [ -n "$val" ]; then

3850 imgs=$(echo "$rawval"\

3851 |while read fn;do

3852 data=`percenthex "$td/$fn"`

3853 echo "<img src=\"data:image/png,$data\">$fn<br>"

3854 done)

3855 form=$form"<br>$imgs"

3856 val=$imgs # 2015-06-15

3857 else

3858 form="<input type=\"file\" name=\"$name\" $args>"

3859 fi

3860 fi

3861 ;;

3862 [Hh][Ii][Dd][Dd][Ee][Nn])

3863 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3864 args="value=\"$GF_STAGE\""

3865 fi

3866 form="<input type=\"hidden\" name=\"$name\" $args>"

3867 prompt='' # Remove prompt

3868 ;;

3869 [Aa][Uu][Tt][Hh][Oo][Rr])

3870 [ -n "$GF_VIEWONLY" ] && continue

3871 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3872 prompt="" ;;

3873 [Oo][Ww][Nn][Ee][Rr])

3874 [ -n "$GF_VIEWONLY" ] && continue

3875 val=${GF_OWNER:-$val}

3876 val=${val:-$user}

3877 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3878 prompt="" ;;

3879 [Uu][Ss][Ee][Rr])

3880 # XXX: is null $user ok?

3881 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3882 [ -n "$GF_VIEWONLY" ] && continue

3883 form="$user"

3884 ;;

3885 [Pp]assword)

3886 [ -n "$GF_VIEWONLY" ] && continue

3887 form="`cgi_passwd`"

3888 val=""

3889 ;;

3890 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3891 [ -n "$GF_VIEWONLY" ] && continue

3892 if [ -z "$rowid" ]; then

3893 val=`genserial`

3894 fi

3895 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3896 prompt="" ;;

3897 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3898 prompt=""

3899 ;;

3900 parent|path|blog*)

3901 prompt=""

3902 ;;

3903 "*"*)

3904 tail=$tail"``"

3905 continue ;;

3906 esac

3907 if [ -n "$prompt" ]; then

3908 if [ -n "${GF_VIEWONLY}" ]; then

3909 form=$val

3910 else

3911 :

3912 fi

3913 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3914 else

3915 hiddens=$hiddens$nl"$form"

3916 fi

3917 done < $1

3918 # enctype="multipart/form-data"

3919 cat<<EOF

3920 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3921 ${rowid:+$rm}

3922 <table class="b $2">

3923 $forms

3924 </table>$hiddens

3925 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3926 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3927 EOF

3928 if [ -z $GF_VIEWONLY ]; then

3929 cat<<EOF

3930 <input type="submit" name="sub" value="OK">

3931 <input type="reset" name="res" value="Reset">

3932 EOF

3933 fi

3934 cat<<EOF

3935 $GF_ARGS</form>

3936 $tail

3937 EOF

3938 }

3939 edittable() {

3940 # $1=form-def $2=table $3 rowid

3941 genform "$@"

3942 }

3943 viewtable() {

3944 GF_VIEWONLY=1 genform "$@"

3945 }

3946 showattc() {

3947 # $1=table_m $2=rowid &optional $3=RawFlag

3948 ## err \$1=$1 \$2=$2 \$3=$3

3949 if ! isfilereadable $user $1 $2; then

3950 contenttype; echo

3951 echo "このファイルは管理者のみしか見られません" | html p

3952 putfooter; exit

3953 fi

3954 idir=`umask 002; mktempd` || exit 1

3955 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3956 bin=$idir/$myname-$$.bin

3957 sql="select quote(bin) from $1 where rowid='$2';"

3958 ## err showattc: sql: $sql

3959 sq $db "$sql" | unhexize > $bin

3960 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3961 type=${tv%//*} fn=${tv#*//}

3962 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3963 ct=${type#file:}

3964 case $ct in # all text/* changed to text/plain

3965 text/*)

3966 charset=`nkf -g $bin|cut -d' ' -f1`

3967 case $charset in

3968 ASCII*) charset="" ;;

3969 esac

3970 if [ -z "$3" ]; then

3971 ct="text/html${charset:+; charset=$charset}"

3972 link="?showattc+$1+$2+raw"

3973 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3974 | sed 's,^,<span></span>,' \

3975 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3976 -D_LINK_="$link" \

3977 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3978 exit $?

3979 fi

3980 ct="text/plain${charset:+; charset=$charset}"

3981 ;;

3982 esac

3983 contenttype "$ct"

3984 echo "Content-Disposition: filename=\"$fn\""

3985 echo "Content-Length: " `cat $bin | wc -c`; echo

3986 #echo "Content-Type: " ${type#file:}; echo

3987 cat $bin

3988 }

3989 #

3990 # Some default stupid handler on CGI values

3991 #

3992 default_storedb() {

3993 # ARG: $1=table-def-file

3994 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3995 tbl=`basename $1`

3996 tbl=${tbl%.def}

3997 cols="`grep :text $1|cut -d: -f2`"

3998 col=`echo "$cols"|head -1`

3999 vcol=`getpar $col`

4000 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4001 if [ -n "$vcol" ]; then

4002 par2table $1

4003 else

4004 return 2 # No insertion occurred

4005 fi

4006 }

4007

4008 default_view() { # $1=def-file

4009 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4010 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4011 default_storedb "$@"

4012 query "select rowid from $tbl order by rowid desc;" \

4013 | while read rowid; do

4014 viewtable $1 $tbl $rowid

4015 done | _m4 -D_TITLE_="$tbl" \

4016 -D_FORM_="`genform $1`" \

4017 -D_DUMPTABLE_="syscmd(cat)" \

4018 $layout/html.m4.html $layout/form+dump.m4.html

4019 }

4020 default_viewtext() { # $1=def-file

4021 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4022 default_storedb "$@"

4023 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4024 | _m4 -D_TITLE_="$tbl" \

4025 -D_FORM_="`genform $1`" \

4026 -D_DUMPTABLE_="syscmd(cat)" \

4027 $layout/html.m4.html $layout/form+dump.m4.html

4028 }

4029 default_smail() {

4030 default_storedb "$@"

4031 if [ $? -eq 2 ]; then

4032 _m4 -D_TITLE_="入力" \

4033 -D_FORM_="`genform $1`" \

4034 -D_DUMPTABLE_="" \

4035 $layout/html.m4.html $layout/form+dump.m4.html

4036 return

4037 fi

4038 cond=""

4039 for pk in `gettblpkey $tbl`; do

4040 pv=$(sqlquote "$(getpar $pk)")

4041 cond="$cond${cond:+ and }$pk=$pv"

4042 done

4043 sql="select rowid from $tbl where $cond;"

4044 rowid=`query "$sql"`

4045 ## err smail1 - "$sql" "-> rowid=$rowid"

4046

4047 while IFS=: read prompt name keytype type args; do # Read from $1

4048 val=`getpar $name`

4049 if [ -n "$val" ]; then

4050 text="$text

4051 $prompt

4052 $name=$val

4053 ---------------------------------------------------------"

4054 fi

4055 case "$type" in

4056 image|document|file)

4057 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4058 fns=$(echo "$fn"|while read fn; do

4059 err mv $tmpd/$fn.orig $tmpd/$fn

4060 mv $tmpd/$fn.orig $tmpd/$fn

4061 rm $tmpd/$fn.rowid # Remove cache flag

4062 ## err "`ls $tmpd/$fn`"

4063 echo $fn

4064 done)

4065 files="$files $fns"

4066 ;;

4067 esac

4068 done < $1

4069 ## err FILES=$files "`ls -lF $tmpd`"

4070 subj="from ${REMOTE_ADDR}"

4071 (echo "$url"

4072 echo "への書き込みがありました。"

4073 echo "------"

4074 echo "$text"

4075 ) | (cd $tmpd &&

4076 err LS="`ls -lF`" &&

4077 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4078 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4079 echo "以下の内容で送信しました。" | html p

4080 viewtable $1 $tbl \

4081 `query "select rowid from $tbl order by rowid desc limit 1;"`

4082 echo "戻る" | html a "href=\"?\""

4083 }