s4: 9ee0e242c25a s4-funcs.sh

s4

view s4-funcs.sh @ 667:9ee0e242c25a

Grp-Message customized for Report-handout

author	HIROSE Yuuji <yuuji@gentei.org>
date	Tue, 12 May 2020 10:28:44 +0900
parents	453f8a114af9
children	84b26e6e4d79

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-*"'

43 file_accept='accept=".jpg,.jpeg,.gif,.png,.tiff,.pdf,.odt,.ods,.odp,.odg,.mp3,.mp4,.avi,.ogg,.mov,.webm,.gpx,.json,.geojson,.kml,.html,.css,.rb,.c,.txt,.zip,.xcf,.bz2,.gz,.xz,.7z,.csv,.dat"'

45 file_accept_help="

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

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

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

49 "

50 file_warn="$file_accept_help

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

52 blogreadflagrowid=0

53 blogcutoffflagrowid=-1

54 whatsnewdays=${WHATS_NEW_DAYS:-14}

56 tconfs=""

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

58 conftbl=_tblconf

59 nl="

60 "

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

62 iconcachekey="profimgcache_S"

63 case "$HTTP_USER_AGENT" in

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

65 *) touchpanel="" ;;

66 esac

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

69 : <<EOF

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

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

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

74 レポート提出管理

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

77 [2016]

78 7/12 根本への反省

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

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

82 [以下2015]

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

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

85 Web

86 締切設定

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

89 ○自分の提出物リスト

91 7/19 ○設置

92 ○一斉送信

93 ○getparfilename の tmpd の扱い

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

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

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

97 ○添付ファイル回収

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

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

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

101

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

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

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

105 ○レポート提出モード

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

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

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

109

110

111 7/9 ○管理者の追加

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

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

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

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

116

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

118

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

120 ○グループ検索

121

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

123

124

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

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

127

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

129 ○6/1 par2tableを triplex 対応に

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

131 →とすると一気に

132

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

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

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

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

137

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

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

140 ## 1. passwd

141 ## 2. grp

142 ## 3. grp_mem

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

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

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

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

147

148 ## ● listの定義:

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

150 ## ● hashの定義:

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

152

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

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

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

156 ## csv2sq3 で .csv.sq3 の Makefile

157

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

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

160 ## type := root | comment

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

162 ## unique(id, filename)

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

164

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

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

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

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

169 ## できたー!

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

171 ↓

172 ↓以下に変更

173 with recursive allmem as

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

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

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

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

178

179

180 with recursive allmem as

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

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

183 where allmem.val=grp_m.gname)

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

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

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

187

188

189 ## triggerもできた。

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

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

192

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

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

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

196 ## グループ管理

197 ## 個人情報管理

198 ##

199

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

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

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

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

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

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

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

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

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

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

210

211 ## ログテーブル

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

213

214

215

216 ■表設計

217 * 3つの表に分散管理

218 id格納表 + hash表 + list表

219 * *_s *_m

220

221

222

223 user, user_map, user_col

224

225 ■抽象エントリタイプ

226 * user

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

228 * group

229 権限判定に利用

230 * serial

231 自動idとして機能

232 * password

233 入力 type=passwordで入力

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

235 * session

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

237 * text

238 入力 type=text

239 * textarea

240 入力 textarea

241 * image|document

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

243 * owner

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

245 * gowner

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

247 * timestamp

248 datetime()

249 * parent

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

251 * path

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

253

254 格納タイプ

255 * list

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

257 * hash

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

259

260 オブジェクトタイプ

261 * entry

262 id, title, owner

263 * textpart

264 id, parentID, text

265 * binarypart

266 id, parentID, contenttype, filename, content

267 * content

268 hash(textpart), list(binarypart)

269 * topic

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

271 * reply

272 id, parentID, content

273 * blog

274 list(entry)

275 blog = [topic, list(reply)]

276

277

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

279 "text" => "hogehoge ..",

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

281 "author" => "taro",

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

283 "parent" => "/",

284 "path" => "/1",

285 "text" => "blah, blah, ....",

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

287 {"serial" => 2,

288 "author" => "hanako",

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

290 "parent" => "/",

291 "path" => "/2",

292 "text" => "blah, blah, ....",

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

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

295

296

297 user:=

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

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

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

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

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

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

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

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

306

307 変換表

308 /user/email=m

309

310 blog:=

311 シリアル:id:p:serial

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

313 所有者:owner:s:owner:

314 時刻:ctime:s:stamp:

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

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

317

318 article:=

319 シリアル:id:p:serial

320 筆者:author:s:owner

321 時刻:ctime:s:stamp:

322 参照元:parent:s:parent:

323 パス:path:s:path:

324 本文:text:s:textarea:

325 画像:image:m:image:

326

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

328

329

330 EOF

331

332 logstart() {

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

334 }

335 logend() {

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

337 }

338 sqlog() {

339 logstart

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

341 cat >> $querylog

342 else

343 echo "$*" >> $querylog

344 fi

345 logend

346 }

347 sq() {

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

349 logstart

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

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

352 else

353 echo "$@" >> $querylog

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

355 fi

356 logend

357 }

358 dbsetup() {

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

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

361 sqi=$tmpdir/sqi.$$

362 sqo=$tmpdir/sqo.$$

363 mkfifo $sqi $sqo

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

365 sq $db < $sqi &

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

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

368 exec 2>> $tmpdir/error.out

369 fi

370 exec 3>> $tmpdir/debug.out

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

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

373 rm $sqi

374 }

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

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

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

378 fi

379 }

380 cleanup() {

381 trap '' INT HUP EXIT TERM PIPE

382 echo .quit >&5

383 kill $sq3pid

384 kill $sq3pid

385 rm -f $sqo $sqi

386 rm -rf $tmpfiles

387 cleanup2

388 }

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

390 # simple redirection for the sake of speed.

391 query() {

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

393 echo ".output $sqo" >&5

394 logstart

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

396 tee -a $querylog

397 else

398 echo "$@" >> $querylog

399 echo "$@"

400 fi >&5

401 echo ".output stdout" >&5

402 cat $sqo

403 logend

404 }

405 _m4() {

406 #S4NAME=f,f,f

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

408 }

409 ismember() {

410 # $1=user, $2=group

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

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

413 }

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

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

416 }

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

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

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

420 }

421 isgrpowner() (

422 # $1=user, $2=group

423 gn=`sqlquote "$2"`

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

425 err isgrpowner: $sql

426 test -n "`query $sql`"

427 )

428 isgrpownerbygid() (

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

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

431 err isgrpownerbygid: $sql

432 test -n "`query $sql`"

433 )

434 getgroupadminmails() {

435 # $1=group

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

437 email4group "$1" "$i" ;

438 done

439 }

440 getgroupadmins() { # $1=group

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

442 qgrp=`sqlquote "$1"`

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

444 }

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

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

447 getvalbyid grp $2 \

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

449 }

450 getgroupbyid() {

451 # $1=id|gname

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

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

454 # err ggbyid: `echo $sql`

455 query $sql

456 }

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

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

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

460

461 # Return true when anonymous mode

462 [ "$anonymousmode" ] && return 0

463 # case `getvalbyid blog mode $2` in

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

465 # *closed*)

466 # owner=`getvalbyid blog owner $2`

467 # if isgrp $owner; then

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

469 # elif isuser $owner; then

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

471 # fi

472 # esac

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

474 sql="with getblog as (\

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

476 select blogid from article where id in\

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

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

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

480 select case\

481 when (select author from article where\

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

483 then 'author'\

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

485 then 'open'\

486 when (select val from getmode) is null \

487 then 'open'

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

489 then (select user from grp_adm where \

490 gname=(select val from getowner) and \

491 user='$1')\

492 when (select author from article where\

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

494 then 'user+author'

495 else '' end;"

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

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

498 result=`query "$sql"`

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

500 return 2

501 }

502 linkhome() {

503 # $1=UserOrGroup

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

505 if isuser $1; then

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

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

508 else

509 echo -n "grp+$1"

510 fi

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

512 }

513 hreflink() {

514 # s4 specific notation:

515 # ^href=URL

516 # ^iframe=URL

517 # ^video=URL

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

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

520 # OSM umap Wikistyle Notation:

521 # [[URL]] - Simle Link

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

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

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

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

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

527 # Other Style

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

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

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

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

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

533 # SPC+SPC+$ - <br>

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

535 checkboxON="${cb} checked>"

536 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

561

562 }

563 minitbl() {

564 sed -n '

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

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

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

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

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

570 H; # Concat this line to HoldSpace

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

572 $ b cont

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

574 }

575 :cont

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

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

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

579 # . of ^. is workaround for FreeBSD sed

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

581 p; # Print whole "table" element

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

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

584 }

585 x; # Back to the newest line

586 p; # Print rest' | miniul

587 }

588 miniul() {

589 sed -e '

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

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

592 :top

593 s/\n//;

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

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

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

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

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

599 $ b cont

600 N; # 次の行を読む

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

602 /^ /b top

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

604 p; s/.*//;

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

606 /^\* /b top

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

608 }

609

610 :cont

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

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

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

614 H; x

615 }

616 x'

617 }

618 acclog() (

619 # $1=table, $2=rowid

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

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

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

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

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

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

626 fi

627 )

628 gecos() (

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

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

631 )

632 setpar() {

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

634 }

635 replpar() {

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

637 }

638 getpar() {

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

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

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

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

643 fi

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

645 case "$var" in

646 owner)

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

648 echo $user; return

649 elif ismember $user $val; then

650 echo $val; return

651 fi ;;

652 esac

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

654 echo "$val"

655 }

656

657 getpartype() {

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

659 }

660 getparcount() {

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

662 }

663 getparfilename() {

664 # null if type of $1 is not file

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

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

667 }

668 sqlquote() {

669 (v="$1"

670 case "$v" in

671 "") return ;; # null

672 "X'"*) # quoted hex string

673 echo $1 ;;

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

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

676 echo "\"$v\""

677 return ;;

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

679 echo "\"$v\""

680 return ;;

681 *)

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

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

684 else

685 echo "\"$v\""

686 fi ;;

687 esac)

688 }

689 sqlquotestr() (

690 case "$1" in

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

692 echo "'$v'" ;;

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

694 esac

695 )

696 mktempd() {

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

698 }

699 getcachedir() { # $1=maintable

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

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

702 else

703 echo $tmpd/$thumbxy

704 fi

705 }

706 getval() {

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

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

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

710 echo "$user" ;;

711 stamp|datetime)

712 date "+%F %T" ;;

713 serial)

714 (s=`getpar $2`

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

716 *)

717 getpar "$2" "$3";;

718 esac

719 }

720

721 getvalquote() {

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

723 (v=`getval "$@"`

724 case "$v" in

725 "") echo NULL ;;

726 *) sqlquote "$v" ;;

727 esac)

728 }

729 getparquote() {

730 sqlquote "`getpar $1`"

731 }

732 getbinbyid() {

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

734

735 }

736 getvalbyid() {

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

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

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

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

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

742

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

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

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

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

747 return

748 fi

749 done

750 rowid=$3

751 pk=`gettblpkey $1`

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

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

754 td=${4:-$tmpd}

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

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

757 for kt in s m; do

758 t=${1}_$kt

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

760 vcount=1 # count(val)

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

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

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

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

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

766 if [ $kt = m ]; then

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

768 # Reset val to store filenames if type is string

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

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

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

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

773 i=0

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

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

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

777 i=$((i+1))

778 fn=$c.$i

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

780 case $type in

781 file:*)

782 #file=$td/$val

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

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

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

786 # FOR SPEED: Skip file generation if imgcache exists

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

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

789 && continue

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

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

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

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

794 select rowid from $cond $slice;

795 .output '$td/$fn'

796 select val from $cond $slice;

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

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

799 .output stdout

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

801 EOF

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

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

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

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

806 ls -lh "$file" |

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

808 case "$type" in

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

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

811 *|*:[Aa]pplication*)

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

813 png:- > "$file"

814 ;;

815 esac

816 ;;

817 *)

818 sq $db<<EOF

819 .output $td/$fn.rowid

820 select rowid from $cond $slice;

821 .output $td/$fn

822 select val from $cond $slice;

823 EOF

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

825 ;;

826 esac

827 done

828 else

829 rm -f $td/$c.count

830 case $type in

831 file:*)

832 echo "$val" \

833 | while read fn; do

834 file=$td/$fn

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

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

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

838 | unhexize > "$file"

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

840 case $type in

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

842 *:[Aa]pplication*)

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

844 png:- > $file ;;

845 esac

846 fi

847 done

848 ;;

849 esac

850 fi

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

852 return

853 fi

854 done

855 done)

856 }

857 getvalbypkey() (

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

859 pk=`gettblpkey $1`

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

861 getvalbyid "$1" "$2" $rowid $4

862 )

863 getvalbycond() {

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

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

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

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

868 getvalbyid "$1" "$2" $rowid "$4"

869 fi

870 }

871 getpwfield() {

872 # getpwfield user column

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

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

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

876 echo "$val"

877 return 0

878 else

879 return 1

880 fi

881 }

882 numericalize() {

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

884 }

885 encode() {

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

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

888 sha1=sha1

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

890 sha1=sha1sum

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

892 sha1=gsha1sum

893 fi

894 fi

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

896 }

897 enjpeg() {

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

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

900 cjpeg="cjpeg"

901 else

902 cjpeg="convert - jpeg:-"

903 fi

904 fi

905 $cjpeg "$@"

906 }

907 mycrypt() (

908 key=$1 salt=$2

909 # err \$2=$2

910 case $2 in

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

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

913 esac

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

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

916 )

917 hexize() {

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

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

920 hexize="xxd -p"

921 else

922 hexize_hd() {

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

924 }

925 hexize="hexize_hd"

926 fi

927 fi

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

929 }

930 unhexize() {

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

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

933 unhex="xxd -p -r"

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

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

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

937 EOF

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

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

940 # world writable...:)

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

942 fi

943 fi

944 cat "$@" | $unhex

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

946 }

947 percenthex() {

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

949 }

950 htmlescape() {

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

952 -e "s/</\</g; s/>/\>/g" -e 's/`/\`/g' -e 's/(/\(/g'

953 }

954 enascii() {

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

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

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

958 else

959 enascii_now=`date +%FT%T`

960 enascii_sed() {

961 nkf -Z0Z1Z2 \

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

963 }

964 enascii="enascii_sed"

965 fi

966 fi

967 cat "$@" | $enascii

968 }

969 size_h() {

970 i="$1" oi=$1

971 set -- B B KB MB GB TB

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

973 oi=$i

974 i=$((i/1024))

975 shift

976 done

977 echo ${oi}$1

978 }

979 gettblconf() {

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

981 ## tconfs=`sq $db \

982 tconfs=`query \

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

984 fi

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

986 }

987 gettblkeys() {

988 # $1=tbl

989 gettblconf

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

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

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

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

994 case $type in

995 *p*)

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

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

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

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

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

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

1002 esac

1003 case $type in

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

1005 esac

1006 done

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

1008 }

1009 gettblpkey() {

1010 # $1=tbl

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

1012 }

1013 gettblfkey() {

1014 (x=`gettblkeys $1`

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

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

1017 }

1018 gettblcols() {

1019 (x=`gettblkeys $1`

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

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

1022 }

1023 gettbl_s_cols() {

1024 (x=`gettblkeys $1`

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

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

1027 }

1028 gettbl_m_cols() {

1029 (x=`gettblkeys $1`

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

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

1032 }

1033 gettbl_h_cols() {

1034 (x=`gettblkeys $1`

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

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

1037 }

1038 gettbl_coltype() (

1039 gettblconf

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

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

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

1043 )

1044 is_hidden() {

1045 # $1=Tbl $2=col

1046 gettblconf

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

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

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

1050 case $x in

1051 *h*) return 0 ;;

1052 *) return 1 ;;

1053 esac

1054 }

1055

1056 dbsetbyid() {

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

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

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

1060 quotedp=$(sqlquotestr "$p")

1061 unset primary update

1062 gettblconf

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

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

1065 #err conf=$conf

1066 case ${conf#*=} in

1067 p*) primary=1 ;;

1068 f*) update=1 ;;

1069 u*) ;;

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

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

1072 esac

1073 #err t=$t

1074 type=string fn=""

1075 case $conf in

1076 */password)

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

1078 ;;

1079 */image*|*/document*)

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

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

1082 ;;

1083 esac

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

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

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

1087 if [ "$primary" ]; then

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

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

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

1091 elif [ "$update" ]; then

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

1093 else

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

1095 fi

1096 )

1097 }

1098 expire() (

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

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

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

1102 )

1103 addsession() {

1104 # expireをセット

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

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

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

1108 # Remove old session parameters

1109 now=`expire now`

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

1111 }

1112 gencookie() (

1113 for kv; do

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

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

1116 done

1117 )

1118 contenttype() {

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

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

1121 }

1122 putheader() {

1123

1124 }

1125 putfooter() {

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

1127 }

1128 getcookie() (

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

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

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

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

1133 done

1134 )

1135 genrandom() {

1136 # $1=columns (default: 10)

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

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

1139 }

1140 genserial() {

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

1142 }

1143 smail() {

1144 # smail rcpts subj (file)

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

1146 # $MAIL_FROM <- From: header value

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

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

1149 rcptheader=`echo $1|tr ' ' '\n'|sort -u|sed '2,$s/^/To: /g'`

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

1151 sender=${SENDER:-$admin}

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

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

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

1155 }

1156 smail_queue_flush() {

1157 # $1=timelimit

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

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

1160 SELECT rowid FROM smailq

1161 GROUP BY rcpts, subj

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

1163 EOF

1164 )

1165 for rid in $rowids; do

1166 sq $workdb \

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

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

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

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

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

1172

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

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

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

1176 .separator "\n" "------------------\n\n"

1177 SELECT time, text FROM smailq

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

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

1180 ORDER by time;

1181 EOF

1182 then

1183 echo rowid=$rid

1184 cat <<-EOF | sq $workdb

1185 DELETE FROM smailq

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

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

1188 EOF

1189 fi

1190 fi

1191 done

1192 }

1193 smail_queue() {

1194 # $1=Rcpts, $@=subj

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

1196 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1205 EOF

1206 )

1207 ## XXX: Adhoc load mitigation

1208 case "$now" in

1209 *[01])

1210 err flush_queue=$mintime

1211 smail_queue_flush ;;

1212 esac

1213 fi

1214 }

1215 setviastring() {

1216 table=$1

1217 oifs="$IFS"

1218 IFS="&"

1219 for us in $2; do

1220 k=${us%%=*}

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

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

1223 #echo $k=$v

1224 done

1225 IFS="$oifs"

1226 }

1227 checkdomain() (

1228 # Check the validity of domain by referring DNS

1229 item=$1

1230 err checkdomain $1

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

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

1233 )

1234 pwcheck() {

1235 # $1=passwd

1236 dbpswd=`getpwfield $user pswd`

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

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

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

1240 }

1241 mypwhash() {

1242 mycrypt "`cat`" `genrandom 5`

1243 }

1244 wasureta() {

1245 user=$1

1246 if ! checkdomain $user; then

1247 contenttype; echo

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

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

1250 putfooter

1251 exit 0

1252 fi

1253 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1255 encpswd=`echo $newpswd|mypwhash`

1256 dbsetbyid user $user pswd "$encpswd"

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

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

1259 $msgdir/mail-newaccount.m4 \

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

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

1262 }

1263 checkauth() {

1264 user=`getpar user`

1265 skc=`getpar skey` # from cookie

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

1267 skey="`getpwfield $user skey`"

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

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

1270 return 0

1271 fi

1272 fi

1273 pswd=`getpar pswd`

1274 quser=`sqlquotestr "$user"`

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

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

1277 return 1

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

1279 wasureta "$user"

1280 return 1 # wasureta error

1281 fi

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

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

1284 if pwcheck "$pswd"; then

1285 newsession=`genrandom 50`

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

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

1288 gencookie "user=$user" "skey=$newsession"

1289 return 0

1290 else

1291 return 4 # Heavy load??

1292 fi

1293 fi

1294 return 2 # Password mismatch

1295 }

1296 showlogin() {

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

1298 s4name=${S4NAME:-s4}

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

1300 cat $layout/login.m4.html

1301 echo '</body></html>'

1302 ) | _m4 \

1303 -D_BODYCLASS_="login" \

1304 -D_TITLE_="$s4name" \

1305 -D_SYSNAME_="Welcome to $s4name" \

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

1307 exit 0

1308 }

1309 dologin() {

1310 checkauth

1311 st=$?

1312 if [ $st != 0 ]; then

1313 contenttype; echo

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

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

1316 showlogin # and EXIT

1317 fi

1318 }

1319

1320 # Do instant jobs here

1321 dbsetup

1322 trap cleanup INT HUP EXIT TERM PIPE

1323 # trap cleanup INT HUP

1324

1325 err() {

1326 echo "$@" 1>&3

1327 }

1328

1329 cgiinit() {

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

1331 tmpf=tmp/stream

1332 tmpd=`tmpd=$tmpdir mktempd`

1333 tmpfiles=$tmpfiles" $tmpd"

1334 addsession $session

1335 getcookie

1336 case "$REQUEST_METHOD" in

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

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

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

1340 # cat > $tmpf # too much?

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

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

1343 s="`cat tmp/stream`"

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

1345 ;;

1346 esac

1347 case "$CONTENT_TYPE" in

1348 *boundary*)

1349 bndry=${CONTENT_TYPE#*boundary=}

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

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

1352 do

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

1354 #echo u=$us

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

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

1357 # err k=$k v=$v

1358 case "$k" in

1359 *:filename)

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

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

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

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

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

1365 :

1366 fi

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

1368 case "$mimetype" in

1369 [Ii]mage/x-xcf)

1370 bzip2 "$tmpd/$v"

1371 v=${v}.bz2

1372 ;;

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

1374 [Ii]mage/*)

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

1376 ;;

1377 esac

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

1379 then

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

1381 continue

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

1383 replpar text string \

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

1385 continue

1386 fi

1387 ;;

1388 *)

1389 type='string'

1390 ;;

1391 esac

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

1393 setpar "$k" "$type" "$v"

1394 done

1395 ;;

1396 *)

1397 setviastring par "$s"

1398 ;;

1399 esac

1400 }

1401 email4group() {

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

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

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

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

1406 sql="WITH

1407 grpemails AS (

1408 SELECT gname, user, val email

1409 FROM grp_mem NATURAL JOIN grp_mem_s

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

1411 useremails AS (

1412 SELECT user.name, val email

1413 FROM user

1414 LEFT JOIN user_m

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

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

1417 FROM useremails u LEFT JOIN grpemails g

1418 ON u.name=g.user

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

1420 query "$sql"

1421 }

1422 email4groupbyuid() {

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

1424 g=$1; shift

1425 uids=`echo "$@"`

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

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

1428 email4group "$g" `query "$sql"`

1429 }

1430 myemail4group() {

1431 # Get my email address for $1-specified group

1432 email4group "$1" "$user" | sed -e 1q -e 's/[ ,].*//'

1433 }

1434 collectmembersbyid() {

1435 # Collect user names of group specified by grid

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

1437 query "SELECT user FROM grp_mem \

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

1439 }

1440 collectmembersbyid() {

1441 # Collect user names of group name

1442 qgrp=`sqlquote "$1"`

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

1444 }

1445 collectgecosesbyid() {

1446 # Collect user gecoses of group

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

1448 query<<-EOF

1449 SELECT gecos

1450 FROM gecoses

1451 WHERE name IN (SELECT user FROM grp_mem

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

1453 EOF

1454 }

1455 collectemail() (

1456 # Collect email addresses for group $1

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

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

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

1460 for e; do

1461 if isuser "$e"; then

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

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

1464 else

1465 qgrp=`sqlquote "$e"`

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

1467 gmem="grp_mem"

1468 else

1469 tm=`sqlquote "$TEAM"`

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

1471 fi

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

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

1474 $gmem g left join grp_mem_s s

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

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

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

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

1479 query "$sql"

1480 fi

1481 done

1482 )

1483 sendinvitation() (

1484 # $1=email

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

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

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

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

1489 gecos=`gecos`

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

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

1492 _m4 -D_URL_="$urlbase" \

1493 -D_USER_="$name" \

1494 -D_EMAIL_="$1" \

1495 -D_REGIST_="$regist" \

1496 -D_ADMIN_="$admin" \

1497 $msgdir/mail-invite.m4 \

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

1499 return 0

1500 )

1501 emaildomaincheck() {

1502 case "$1" in

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

1504 *@*)

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

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

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

1508 return 2

1509 fi

1510 return 0

1511 ;;

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

1513 esac

1514 }

1515 invite() {

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

1517 case "$email" in

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

1519 *@*)

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

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

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

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

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

1525 elif sendinvitation $email; then

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

1527 else # Cannot be reached here

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

1529 fi ;;

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

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

1532 esac

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

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

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

1536 fi

1537 if [ -f $invite_policy ]; then

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

1539 else

1540 pol="$invite_policy"

1541 fi

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

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

1544 -D_INVITE_POLICY_="$pol" \

1545 $layout/html.m4.html $layout/invite.m4.html

1546 }

1547 regist() {

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

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

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

1551 echo "bye bye" | html p

1552 reutrn

1553 fi

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

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

1556 cat<<EOF

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

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

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

1560 EOF

1561 return

1562 fi

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

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

1565 # Fake login password to wasureta

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

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

1568 wasureta $email

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

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

1571 addsession $1 # for removal after 1 minute

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

1573 $layout/login.m4.html

1574 return

1575 }

1576 group_safename() {

1577 # Convert $1 to safe group name

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

1579 }

1580 groupupdate() {

1581 gname=`getpar gname`

1582 qgname=`sqlquote "$gname"`

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

1584 # See ALSO same job in showgroup()

1585 newgname=`group_safename "$gname"`

1586 err newgname=$newgname

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

1588 err NewGNAME: gname=$newgname

1589 gname=$newgname

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

1591 replpar gname string "$gname"

1592 fi

1593 # Name confliction check

1594 parow=`getpar rowid`

1595 ## err parow=$parow

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

1597 query "BEGIN EXCLUSIVE;"

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

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

1600 if [ $count -gt 0 ]; then

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

1602 query "END;"

1603 return

1604 fi

1605 par2table $formdir/grp.def

1606 query "END TRANSACTION;"

1607 # Remove orphan

1608 : <<EOF

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

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

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

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

1613 EOF

1614 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1618 sql="delete from blog where id in

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

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

1621 query "$sql";

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

1623 return # and return

1624 fi

1625 fi

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

1627 fi

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

1629 grid=$(query $sql)

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

1631 grp $grid

1632 }

1633 groupclone() {

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

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

1636 case "$1" in

1637 */noteam)

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

1639 esac

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

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

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

1643 return

1644 fi

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

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

1647 return

1648 fi

1649 i=0

1650 while true; do

1651 copy="-copy$i"

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

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

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

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

1656 i=$((i++))

1657 continue

1658 fi

1659 break

1660 done

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

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

1663 query<<-EOF

1664 BEGIN;

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

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

1667 SELECT $newqname, key, val

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

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

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

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

1672 -- Copy members and their configuration --

1673 REPLACE INTO grp_mem SELECT $newqname, user

1674 FROM grp_mem WHERE gname=$qgrp;

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

1676 FROM grp_mem_s WHERE gname=$qgrp;

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

1678 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1679 -- Copy administrators --

1680 REPLACE INTO grp_adm SELECT $newqname, user

1681 FROM grp_adm WHERE gname=$qgrp;

1682 COMMIT;

1683 EOF

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

1685 STOPCLONEMSG=1 groupconf "$newrowid"

1686 }

1687 groupman() {

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

1689

1690 GF_STAGE="grpconf"

1691 GF_STAGE=groupupdate

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

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

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

1695 -D_DUMPTABLE_="syscmd(cat)" \

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

1697 }

1698 userconf() {

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

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

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

1702 }

1703 groupconf() {

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

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

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

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

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

1709

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

1711 grp=`getgroupbyid $rowid`

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

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

1714 return

1715 fi

1716

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

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

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

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

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

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

1723 <div>

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

1725 <table>

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

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

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

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

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

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

1732 </table>

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

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

1735 </div>

1736 EOF

1737 fi

1738 }

1739 mems() {

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

1741 kwd=`getpar kwd`

1742 listmember $kwd

1743 }

1744 grps() {

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

1746 kwd=`getpar kwd`

1747 listgroup $kwd \

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

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

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

1751 $layout/form+dump.m4.html

1752 }

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

1754 gpg=`getpar grp`

1755 grid=${1:-$gpg}

1756 grp=`getgroupbyid "$grid"`

1757 ## . ./s4-blog.sh

1758 jg=`getpar joingrp`

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

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

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

1762 fi

1763 htmlheader=$layout/html.m4.html

1764 showgroup "$grid"

1765 }

1766 sql4interestblogs() {

1767 cat<<EOF

1768 CREATE TEMPORARY VIEW interestblogs AS

1769 SELECT blog.rowid rid, id, author

1770 FROM blog

1771 NATURAL JOIN

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

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

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

1775 WHEN (SELECT user FROM grp_mem

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

1777 THEN 0

1778 ELSE 1

1779 END;

1780 EOF

1781 }

1782 listnewblogsql() { # $1=user

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

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

1785 newdays=${newdays:-14}

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

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

1788 WHERE user='$user'

1789 AND tblrowid IN

1790 ($blogreadflagrowid,

1791 $blogcutoffflagrowid)),

1792 $basetime -- "0"

1793 );"`

1794 cat<<EOF

1795 `sql4interestblogs`

1796 WITH article_ctime as (

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

1798 FROM article join article_s s using(id)

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

1800 GROUP BY id

1801 ), blog_title_owner as (

1802 SELECT blg.rid brid, id,

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

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

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

1806 ), blogall as (

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

1808 ), news as (

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

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

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

1812 FROM blogall bl

1813 LEFT JOIN

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

1815 ON bl.brid=al.tblrowid

1816 WHERE atime < bl.ctime

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

1818 LIMIT 10

1819 ) SELECT brid LINK, "新着",

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

1821 ctime, title,

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

1823 FROM news;

1824 EOF

1825 }

1826

1827 search_form() {

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

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

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

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

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

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

1834 @week → 最近一週間

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

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

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

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

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

1840 auth=""

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

1842 case "$1" in

1843 author=*)

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

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

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

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

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

1849 ;;

1850 grp=*)

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

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

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

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

1855 ;;

1856 esac

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

1858 cat<<-EOF

1859 <div class="right">

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

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

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

1863

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

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

1866

1867 </form>

1868 </div>

1869 EOF

1870 }

1871

1872 imgsrc_cache() (

1873 # $1 = directory for cache'ing

1874 # $2 = table (user_m or grp_m)

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

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

1877 dir="$1" tbl="$2"

1878 keycond="$3"

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

1880 ORDER BY rowid DESC LIMIT 1"

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

1882 tmpf=$tmpd/imgsrc_cache.$$

1883 case "$4" in

1884 [Ss]) size=S ;;

1885 [Oo]) size=O ;;

1886 *) size=M ;;

1887 esac

1888 # ImageCache filename storing schema:

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

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

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

1892 valtype=`query "$sql0"`

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

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

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

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

1897 case "$filetype" in

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

1899 esac

1900 fi

1901 cacheimg_S=$dir/S_$filename

1902 cacheimg_M=$dir/M_$filename

1903 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1911 else

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

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

1914 fifo2=$fifo.2

1915 mkfifo $fifo $fifo2

1916 fmt=${filename##*.}

1917 ## [[ NOTE ]]

1918 ## a. convert oldimage newimage

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

1920 ## b is much smaller than a

1921 cat $tmpf | unhexize \

1922 | tee $fifo \

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

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

1925 | tee $fifo2 \

1926 | convert - "$cacheimg_M" &

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

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

1929 | convert - "$cacheimg_S" &

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

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

1932 echo '">'

1933 echo "$sum" > $sumfile

1934 fi

1935 ## Now preparing cache image, done.

1936 ## Store this information to DB

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

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

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

1940 query <<-EOF

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

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

1943 EOF

1944 )

1945

1946 showhome() {

1947 # $1=userRowIdToShow

1948 err showhome \$1=$1

1949 case "$1" in

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

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

1952 esac

1953 ## err ShowHome: uname=$uname

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

1955 gecos=`gecos "$uname"`

1956 ## err SH:gecos=$gecos

1957 GF_VIEWONLY=1

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

1959 search_form_args=""

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

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

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

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

1964 # Display folders

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

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

1967 and type like 'file:%';"

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

1969 nfile=`query "$sql"`

1970 # err nfile=$nfile

1971 if [ $nfile -gt 0 ]; then

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

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

1974 fi

1975 else

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

1977 GROUP BY user;"`

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

1979 search_form_args="author=$uname"

1980 fi

1981 . ./s4-blog.sh

1982

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

1984 search_form "$search_form_args" > $sf

1985 printf "%s さん" "$gecos" > $tf

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

1987 viewtable $formdir/user.def user $1

1988 echo "</div>"

1989 } > $pf

1990

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

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

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

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

1995 if true; then

1996 tbl=user_m

1997 enticond="name='$uname'"

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

1999 else

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

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

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

2003 echo '">'

2004 }

2005 fi > $imf

2006 fi

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

2008 val='$uname';"`

2009 ## REMOVE This comment block until 2019/7/1

2010 ## err "----- `gdate +%FT%T.%3N` ------------C"

2011 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2012 listblog $uname > $bf

2013 ## unset ddd

2014 ## err "----- `gdate +%FT%T.%3N` ------------D"

2015

2016 hometail=$tmpd/tail.$$

2017 mkfifo $hometail

2018

2019 #Calling listgroupbytable, originally here

2020

2021 (

2022 # Display Most Recent Entry

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

2024 shortval=${shortval:-val}

2025

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

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

2028 ctime,

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

2030 (SELECT gecos FROM gecoses

2031 WHERE name=(SELECT val FROM blog_s

2032 WHERE key='owner' AND id=b.id)) owner,

2033 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2034 FROM blog b

2035 JOIN

2036 (SELECT distinct blogid, a.id aid, max(val) ctime

2037 FROM article a, article_s s

2038 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2039 GROUP BY blogid ORDER BY val DESC LIMIT 50

2040 ) m

2041 ON b.id=m.blogid;"

2042 # This should be as follows

2043 : <<EOF

2044 WITH arts AS(

2045 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2046 a.blogid, a.id id, s.val ctime

2047 FROM article a NATURAL JOIN article_s s

2048 WHERE s.key = 'ctime' AND a.author='$user'

2049 GROUP by s.id

2050 )

2051 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2052 FROM arts a0

2053 JOIN

2054 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2055 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2056 ORDER BY ctime DESC LIMIT 50;

2057 EOF

2058

2059 cat<<-EOF

2060 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2061 for="mre" title="D">最近の書き込み先</label>

2062 <div class="lcto">

2063 `DT_VIEW=replyblog dumptable html blog`

2064 </div>

2065 EOF

2066 unset DT_SQL

2067 if [ x"$user" = x"$uname" ]; then

2068 # Display NEWS

2069 # 2016-06-26

2070 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2071 acclog blog $blogreadflagrowid

2072 # echo "全部既読にしました" | html p

2073 fi

2074 # 2016-02-19 Counting NEWS without using dumptable.

2075 sql=`listnewblogsql "$user"`

2076 # echo "$sql" > tmp/listnew

2077 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2078 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2079 cont=$((cont-1))

2080 err newcount=$cont

2081 if [ $cont -gt 0 ]; then

2082 #echo "全体の新着記事${cont}傑" | html h2

2083 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2084 echo "<label for=\"new10\" title=\"F\">新着${cont}傑</label><div>"

2085 cat<<-EOF | html form 'action="?home"'

2086 `cgi_checkbox readchk yes 'id="read"'`<label

2087 for="read">新着ふくめて全部読んだことにする</label>

2088 `cgi_submit '確定'`

2089 `cgi_hidden read yes`

2090 EOF

2091 echo "$new10 "

2092 echo "</div>"

2093 else # If news is 0, set log cut off flag

2094 acclog blog $blogcutoffflagrowid # for speed

2095 fi

2096 else # Not My Home ($user != $uname)

2097 : # DT_SQL=

2098 fi

2099 ) > $hometail & # Is background call safe to m4??

2100 #

2101 listgroupbytable $formdir/grp.def $cond |

2102 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2103 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2104 -D_PROFIMG_="spaste(\`$imf')" \

2105 -D_BLOGS_="spaste(\`$bf')" \

2106 -D_SEARCH_="spaste(\`$sf')" \

2107 -D_NBLOG_="$nblog" \

2108 -D_GROUPS_="syscmd(\`cat')" \

2109 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2110 $layout/html.m4.html $layout/home.m4.html

2111

2112 # Record access log

2113 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2114 }

2115 commission() { # $1=grp-rowid $2=user-rowid

2116 contenttype; echo

2117 ## err commission: "$@"

2118 gname=`getgroupbyid $1`

2119 echo "グループ $gname 管理者委任" \

2120 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2121 if [ -n "$2" ]; then

2122 grp_reg_adm "$@"

2123 else

2124 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2125 fi

2126 }

2127 listgroupbytable() {

2128 # $1=deffile $2...=condition

2129 tagline=`grep :tag: $1`; shift

2130 and="${1:+and }" where=${1:+where }

2131 href="<a href=\"$myname?grp+"

2132 echo '<div class="listgroup">'

2133 NGsql="select distinct tag from\

2134 (select gname, max(case key when 'tag' then val end) as tag, \

2135 max(case key when 'ctime' then val end) as ctime\

2136 from grp_s group by gname order by ctime);"

2137 sql="select val from grp_s where key='tag' $and$* group by val;"

2138 ## err ListGRP: query sql="$sql"

2139 for tag in `query "$sql"`

2140 do

2141 ## err ListGrp: tag=$tag

2142 tn=${tagline%%=${tag}*}

2143 tn=${tn##*[ :]}

2144 sql="select rowid||':'||gname as 'グループ名',説明 from

2145 (select (select rowid from grp g where g.gname=grp_s.gname)

2146 as rowid,

2147 gname,

2148 max(case key when 'gecos' then val end) as '説明',

2149 max(case key when 'tag' then val end) as 'tag',

2150 max(case key when 'mtime' then val end) as mtime from grp_s

2151 $where$* group by gname having tag='$tag' order by mtime desc);"

2152 ## err PersonalGroupList= `echo $sql`

2153 echo "<h2>$tn</h2>"

2154 echo '<table class="b listgroup">'

2155 sq -header -html $db "$sql" \

2156 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2157 echo '</table>'

2158 done

2159 echo '</div>'

2160 }

2161 iconhref() (

2162 # $1=icon-file, $2=Href $3=title $4...=anchor

2163 data=`percenthex "$1"`

2164 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2165 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2166 href=$2; title=$3; shift 3

2167 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2168 )

2169 iconhref2() (

2170 # $1=icon-file, $2=Href $3=title $4...=anchor

2171 src=$1

2172 href=$2; title=$3; shift 3

2173 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2174 )

2175 listentry() (

2176 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2177 # Referring variable $iamowner=$grp to attach owner-request links

2178 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2179 cond='' hiddens=''

2180 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2181 if [ -z "$offset" ]; then

2182 offset=`getpar start`; offset=${offset%%[!0-9]*}

2183 offset=$((offset-1))

2184 fi

2185 offset=$((offset + 0)) # change to numeric forcibly

2186 [ $offset -lt 0 ] && offset=0

2187 limit=$listentlimit

2188 dir=`getcachedir "$1"`

2189 if [ x"$1" = x"user" ]; then

2190 hrb="$myname?home"

2191 deficon=person-default.png

2192 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2193 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2194 gcs=gecos

2195 else # if group

2196 hrb="$myname?grp"

2197 deficon=group-default.png

2198 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2199 gcs=name

2200 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2201 if [ -n "$tagline" ]; then

2202 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2203 ## err tagconv=$tagconv

2204 fi

2205 fi

2206 if [ ! -d $dir ]; then

2207 mkdir -p $dir

2208 fi

2209 if [ ! -s $dir/$deficon ]; then

2210 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2211 fi

2212 if [ -n "$2" ]; then

2213 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2214 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2215 fi

2216 tag=`getpar tag` tag2=`getpar tag2`

2217 if [ x"$tag" = x"NULL" ]; then

2218 tag="" tag2=""

2219 fi

2220 if [ -n "$tag$tag2" ]; then

2221 tag=${tag:-$tag2}

2222 qtag=`sqlquote "$tag"`

2223 cond2="tag=$qtag"

2224 fi

2225 if [ -n "$cond1$cond2" ]; then

2226 cond="$cond1${cond2:+ AND $cond2}"

2227 cond="WHERE ${cond# AND }"

2228 fi

2229

2230 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2231 # grpは呼出し元の動的スコープ変数でよくないな...

2232 ##qgrp=`sqlquote $grp`

2233 getgrp="(select gname from grp where rowid=${rowid:--1})"

2234 sql="select a.rowid, a.$link,

2235 coalesce(b.$gcs, a.$nm) as nick,

2236 quote(a.$nm) as qname,

2237 (SELECT val FROM ${tbl}_s

2238 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2239 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2240 || case when a.$nm in (select gname from grp)

2241 then printf('(%d名)',

2242 (select count(user) from grp_mem where gname=a.$nm))

2243 else ' <'||a.$nm||'>'

2244 end

2245 as name,

2246 b.tag,

2247 case when a.$nm in (select user from grp_adm

2248 where gname=$getgrp) then '管理者'

2249 when '$user' in (select user from grp_adm where gname=a.$nm)

2250 then 'ADMIN'

2251 when '$user' in (select user from grp_mem where gname=a.$nm)

2252 then 'Member'

2253 when '$iamowner' = '' then ''

2254 else ',not='||a.rowid end as ownerlink,

2255 CASE '$entity'

2256 WHEN 'グループ'

2257 THEN coalesce(

2258 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2259 'open')

2260 ||

2261 CASE WHEN '$user'

2262 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2263 THEN ' ismember'

2264 ELSE ''

2265 END

2266 ELSE 'user'

2267 END regmode

2268 from $tbl a left join

2269 (select $nm as name,

2270 max(case key when 'gecos' then val end) as gecos,

2271 max(case key when 'tag' then val end) as tag,

2272 max(case key when 'mtime' then val end) as mtime,

2273 max(case key when 'wtime' then val end) as wtime,

2274 max(case key when 'login' then val end) as login

2275 from ${tbl}_s group by $nm)

2276 b on a.$nm=b.name $cond $3

2277 order by b.wtime desc, b.login desc,

2278 b.mtime desc, b.tag desc, a.rowid asc"

2279 # Give precedence to newer maintained groups (2016-09-24)

2280 # Note that mtime is stored only in grp_s.

2281 ## err LE:sql.1="$sql"

2282 total=`query "with x as ($sql) select count(*) from x;"`

2283 echo "${entity} 一覧" | html h2

2284 echo '<div class="listentry">' # List-entry div

2285 # Show owner/member filter button

2286 METHOD=GET

2287 hiddens="$hiddens

2288 `cgi_hidden kwd \"$kwd\"`

2289 `cgi_hidden stage \"$stage\"`"

2290 if [ x"$tbl" = x"grp" ]; then

2291 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2292 fh="<select name=\"tag\">$nl"

2293 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2294 for l in $args; do

2295 val=${l#*=} tname=${l%=*}

2296 if [ x"$val" = x"$tag" ]; then

2297 s=" selected"

2298 selectedtags="(種別[${tname}]のみ)"

2299 else

2300 s=""

2301 fi

2302 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2303 done

2304 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2305 cat<<-EOF

2306 <form action="$myname" method="$METHOD">

2307 </form>

2308 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2309 for="ismembtn">参加中以外隠す</label>

2310 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2311 for="isadmbtn">管理者参加以外隠す</label>

2312 EOF

2313 # limit=3

2314 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2315 fi

2316 if [ $total -gt $limit ]; then

2317 echo '<div>'

2318 METHOD=GET cgi_form $stage <<EOF

2319 $form

2320 <label>次の語を含む${entity}で検索:

2321 `cgi_text kwd "$kwd"`</label>

2322 $hiddens

2323 EOF

2324 echo '</div>'

2325 else

2326 echo $selectedtags | html p

2327 fi

2328 cat<<EOF

2329 <form action="$myname" method="$METHOD">

2330 <p>${total}件中の<input class="hidesub" type="text" name="start"

2331 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2332 <input type="submit" value="確定"></p>

2333 </form>

2334 EOF

2335 if [ $((offset+limit)) -lt $total ]; then

2336 nextbtn=$(

2337 cat<<EOF

2338 <div class="right clear"><form action="$myname" method="$METHOD">

2339 `cgi_submit 次の${limit}件`

2340 $hiddens

2341 `cgi_hidden offset $((offset + limit))`</form></div>

2342 EOF

2343 )

2344 fi

2345 if [ $offset -gt 0 ]; then

2346 prevbtn=$(

2347 cat<<EOF

2348 <form action="$myname" method="$METHOD">

2349 `cgi_submit 前の${limit}件`

2350 $hiddens

2351 `cgi_hidden offset $((offset - limit))`</form>

2352 EOF

2353 )

2354 fi

2355 pnbtn="$nextbtn$prevbtn"

2356 echo $pnbtn

2357

2358 ## err ListEntry: `echo "$sql"\;`

2359 # sq $db here??? 2016-11-28

2360 query "$sql limit $limit ${offset:+offset $offset};" \

2361 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2362 # err name=$name owner=$ownerp lnk=$lnk

2363 # err newlnk=$lnk regmode=$regmode

2364 icondir=$dir/$id

2365 # Pick up only last icon

2366 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2367 <p class=\"tag _$tag\">$tag</p>" \

2368 | _m4 $tagconv

2369 if [ -n "$NOSPEEDUP" ]; then

2370 files=`getvalbyid $tbl profimg $id $icondir`

2371 if [ -n "$files" ]; then

2372 icon=`echo "$files"|tail -1`

2373 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2374 else

2375 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2376 fi

2377 elif [ -n "$icon" -a -s "$icon" ]; then

2378 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2379 else

2380 cond="$nm=$qname"

2381 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2382 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2383 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2384 # err "img=[$img]"

2385 if [ -n "$img" ]; then

2386 echo "<a href=\"$hrb+$lnk\">"

2387 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2388 echo "</a>"

2389 else

2390 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2391 fi

2392 fi

2393 echo "<br>$name${ownerp:+<br>($ownerp)}"

2394 echo "</div>"

2395 done

2396 echo "</div>" # End of List-entry div

2397 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2398 )

2399 listmember() {

2400 listentry user "$@"

2401 }

2402 listgroup() {

2403 listentry group "$@"

2404 }

2405 hexteams() { # $1=gname, $2(optional)=user

2406 cond=${2:+" AND user='$2'"}

2407 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2408 WHERE gname='$1' AND key='team'$cond;"

2409 }

2410 showgroup() { # $1=group-rowid

2411 if [ -z "$1" ]; then

2412 grid=`getpar grid`

2413 grid=${grid%%[!0-9]*}

2414 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2415 else

2416 grid=$1

2417 fi

2418 grp=`getgroupbyid $grid`

2419 qgrp=`sqlquote "$grp"`

2420 htmlgrp=`echo "$grp"|htmlescape`

2421 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2422 if isgroup "$grp"; then

2423 tf=$tmpd/title.$$

2424 sf=$tmpd/search.$$

2425 bodyclass=`query "SELECT val FROM grp_s

2426 WHERE gname=$qgrp AND key='regmode';"`

2427 if ismember "$user" "$grp"; then

2428 ismember="ismember"

2429 bodyclass="$bodyclass${bodyclass:+ }ismember"

2430 else

2431 ismember="" # bodyclass="group"

2432 fi

2433 bodyclass="$bodyclass grouphome"

2434 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2435 echo "グループ $htmlgrp" > $tf

2436

2437 showgroupsub $formdir/grp.def "$grid" | \

2438 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2439 -D_FORM_="syscmd(\`cat')" \

2440 -D_BODYCLASS_="$bodyclass" \

2441 -D_DUMPTABLE_="" \

2442 $htmlheader $sf $layout/form+dump.m4.html

2443 # $htmlheader $layout/form+dump.m4.html

2444 # $htmlheader is defined in grp()

2445 else # if $grp is removed at par2table

2446 listgroup

2447 fi

2448 }

2449 showgroupsub() {

2450 # $1=def-file $2=group-rowid

2451 # Using $ismember

2452 rowid=$2

2453 grp=`getgroupbyid $2`

2454 qgrp=`sqlquote "$grp"`

2455 td=`getcachedir grp/"$2"`

2456 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2457 if [ -z "$rowid" ]; then

2458 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2459 #grp=`sq $db "select gname from grp where rowid=$grp"`

2460 echo "showgroupsub: invalid argument($1 $2)" | html p

2461 return

2462 fi

2463 val=`getvalbyid grp profimg $rowid $tmpd`

2464 enticond="gname=$qgrp"

2465 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2466 if [ -n "$img" ]; then

2467 cat<<-EOF

2468 <p class="groupimg">

2469 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2470 EOF

2471 fi

2472 echo "<div class=\"noprofimg\">"

2473 viewtable $1 grp $rowid

2474 echo "</div>"

2475 if isgrpowner "$user" "$grp"; then

2476 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2477 iamowner=$rowid

2478 colmd=" mode"

2479 fi

2480 if [ -n "$ismember" ]; then

2481 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2482 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2483 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2484 cat<<EOF

2485 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2486 <div class="fold clear">

2487 `cgi_checkbox send yes id="send"`<label

2488 for="send">グループ全員にメッセージ送信</label>

2489 <div>

2490 `cgi_textarea message "" "cols=60"`

2491 `cgi_submit 送信`

2492 `cgi_reset リセット`

2493 </div>

2494 `cgi_hidden grp $rowid`

2495 </div></form>

2496 EOF

2497 fi

2498 # 加入ボタン + 加入者リスト

2499 if [ -n "$ismember" ]; then

2500 ismem='checked' state="(参加中)"

2501 else

2502 nomem='checked' state="(現在非加入)"

2503 fi

2504 # このグループでの加入アドレス

2505 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2506 and key='email';"`

2507 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2508 ## and key='email';"

2509 ##err email=$eml

2510 cat <<EOF

2511 <div class="fold clear">

2512 `cgi_checkbox reg yes id="reg"`<label

2513 for="reg">自身の加入状態を操作する</label>$state

2514 <div>

2515 EOF

2516 cgi_form grp <<EOF

2517 <p>このグループに</p>

2518 <table class="b">

2519 <tr><th>メンバーとして</th><td>

2520 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2521 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2522 <tr><th>参加する場合のメイルアドレス<br>

2523 <small>(メインのアドレスとは違うものにする場合に記入<br>

2524 同じでよい場合は空欄に)</small></th>

2525 <td>`cgi_text email $eml`</td></tr>

2526 </table>

2527 `cgi_hidden grp $rowid`

2528 EOF

2529 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2530 echo "moderated (承認加入の)グループなので実際に参加できるのは

2531 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2532 fi

2533 echo '</div></div>'

2534 echo '<h2>話題一覧</h2>'

2535 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2536 cat<<-EOF

2537 <form class="summary" action="$myname" title="$thelp">

2538 `cgi_hidden owner "$grp"`

2539 `cgi_hidden kwd "@week"`

2540 `cgi_hidden stage searchart`

2541 `cgi_submit "一週間のまとめ"`

2542 </form>

2543 EOF

2544 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2545 colstate="state:稼動状態:frozen=rowclass=凍結"

2546 DT_CHLD=article:blogid \

2547 DT_QOWNER="$qgrp" \

2548 DT_VIEW=replyblog dumptable html blog \

2549 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2550

2551 getgname="(select gname from grp where rowid=$rowid)"

2552 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2553 cm="?commission+$rowid"

2554 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2555 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2556 # team list

2557 hexteams=`hexteams "$grp"`

2558 if [ -n "$hexteams" ]; then

2559 echo "チーム一覧" | html h2

2560 echo '<div class="dumptable"><table class="b">'

2561 sq $db -html -header<<-EOF

2562 SELECT val TEAM,

2563 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2564 MEMBERS

2565 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2566 EOF

2567 echo '</table></div>'

2568 fi

2569 }

2570 grp_getbodyclass() {

2571 # Get css class name for document.

2572 # `moderated' for moderated groups

2573 # `ismember' for groups where user belongs

2574 # $1=GroupName (w/o quote)

2575 # $user=userNameCurrentlyLogin

2576 ## err grp_getbodyclass: 1="$1"

2577 qgrp=`sqlquote "$1"`

2578 query<<-EOF

2579 SELECT coalesce(

2580 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2581 'open')

2582 ||

2583 CASE WHEN '$user'

2584 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2585 THEN ' ismember'

2586 ELSE ''

2587 END;

2588 EOF

2589 }

2590 grpaction() { # $1=group-rowid

2591 err GRP_ACTION:IN

2592 grid=${1:-`getpar grp`}

2593 grp=`getgroupbyid "$grid"`

2594 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2595 if [ -z "$grp" ]; then

2596 echo "無効な指定です。" | html p; return

2597 fi

2598 if ! ismember $user "$grp"; then

2599 echo "加入者のみに許可された操作です。" | html p; return

2600 fi

2601 echo "グループ $grp 個別選択操作" \

2602 | _m4 -D_TITLE_="syscmd(\`cat')" \

2603 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2604 $layout/html.m4.html

2605

2606 isowner=""

2607 isgrpowner "$user" "$grp" && isowner="yes"

2608 usel=`getpar usel`

2609 if [ -n "$usel" ]; then

2610 uids=$(echo `echo $usel`|tr ' ' ',')

2611 ## err grpaction-1: grp=$grp, `echo $sql`

2612 text=`getpar text`

2613

2614 rm=`getpar rm` cfm=`getpar confirm`

2615 ## err rm=$rm cfm=$cfm

2616 if [ x"$rm" = x"yes" ]; then

2617 if [ "$isowner" ]; then

2618 if [ x"$rm$cfm" = x"yesyes" ]; then

2619 # Eliminate

2620 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2621 for tbl in grp_mem grp_mem_s grp_mem_m; do

2622 sql="delete from $tbl $cond;"

2623 # echo "sql=$sql"

2624 query "$sql"

2625 err rmGRPuser "$sql"

2626 done

2627 num=`query "select count(*) from user where rowid in ($uids);"`

2628 #err num=$num

2629 if [ 0$num -gt 0 ]; then

2630 sql="select coalesce(b.val,a.name) from user a left join \

2631 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2632 # err `echo "$sql"`

2633 html pre<<EOF

2634 以下の${num}名のグループ $grp 登録を解除しました。

2635 `query "$sql"`

2636 EOF

2637 fi

2638 else

2639 echo "確認のチェックがないのでやめておきます。" | html p

2640 return

2641 fi

2642 else # not Group Owner

2643 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2644 return

2645 fi

2646 cat<<EOF

2647

2648 EOF

2649 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2650 if [ -z "$text" ]; then # if msg is empty

2651 echo "なにかメッセージを..." | html p

2652 return 0

2653 fi

2654 gecos=`gecos $user`

2655 safegc=`echo "$gecos" | tr -d '<>@'`

2656 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2657 fromad=`myemail4group "$grp"`

2658 mail_from="$safegc <$fromad>"

2659 replyto=$fromad

2660

2661 ## Start parse of attachment files

2662 if [ -n "`getpar email`" ]; then

2663 ar=`getpar supprcpt`

2664 if [ -n "$ar" -a -n "`getpar suppck`" ]; then

2665 for a in $ar; do

2666 checkdomain "$a" && supprcpt="$supprcpt $a"

2667 done

2668 fi

2669 subj=`getpar subject`

2670 afiles=""

2671 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2672 do

2673 f=$tmpd/$fn

2674 if [ -s $f ]; then

2675 afiles=$afiles"${afiles:+ }$f"

2676 fi

2677 done

2678 else

2679 preface=$(cat <<-EOF

2680 $url

2681 のグループ「$grp」のメンバーである $gecos さんから、

2682 あなた宛のメッセージです。

2683 ----------------------------------------------------------

2684 EOF

2685 )

2686 fi

2687 ## SENDER=$noreply \ # Should not SENDER be set to $noreply??

2688 rcpts="`email4groupbyuid "$grp" $usel` $user$supprcpt"

2689 subj="${subj:-$gecos さんからのメッセージ}"

2690 REPLYTO=$replyto

2691 MAIL_FROM=$mail_from

2692 if [ -n "$afiles" ];then

2693 ./sendmultipart.sh -t "$rcpts" -s "$subj" -f "$mail_from" $afiles

2694 else

2695 smail "$rcpts" "$subj"

2696 fi <<EOF

2697 ${preface:+$preface$nl}$text

2698 EOF

2699 if [ $? = 0 ]; then

2700 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2701 sql="select coalesce(b.val, a.name) from

2702 (select name from user where rowid in ($uids)) a

2703 left join user_s b on a.name=b.name and b.key='gecos';"

2704 html pre<<EOF

2705 `query "$sql"`

2706 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2707 EOF

2708 err SendDone: `echo $sql`

2709 fi

2710 elif [ x"$rm" = x"commission" ]; then

2711 grp_reg_adm $grid $usel

2712 elif [ x"$rm" = x"addteam" ]; then

2713 team=`getpar team|sed "s/'/''/g"` # for single quotation

2714 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2715 if [ x"$team" != x"$newteam" ]; then

2716 echo "チーム名に使えない文字を除去しました" | html p

2717 team=newteam

2718 fi

2719 if [ -z "$team" -o x"$team" = x"なし" ]; then

2720 cat<<-EOF | html p

2721 有効なチーム名を入力してください。

2722 カンマだけ、「なし」という名前は使えません。

2723 EOF

2724 echo "有効なチーム名を入力してください。" | html p

2725 else

2726 grp_add_team $grid "$team" $usel

2727 fi

2728 elif [ x"$rm" = x"rmteam" ]; then

2729 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2730 rmteam=`getpar rmteam|sed "s/'/''/g"`

2731 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2732 gname='$grp' AND user='$user' AND key='team'\

2733 AND val='$rmteam';\"`" ]; then

2734 grp_rm_team $grid "$rmteam" $usel

2735 else

2736 echo "所属していないチームの除去操作はできません。"|html p

2737 fi

2738 else

2739 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2740 fi

2741 fi

2742 fi

2743 # POST count summary

2744 from=`getpar from`; to=`getpar to`

2745 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2746 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2747 fromtonote="<p>POST集計: $from_input - $to_input</p>"

2748 # New entry

2749 sql="WITH mems AS (

2750 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2751 ON gm.user=g.name

2752 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2753 ), target_article AS (

2754 SELECT id FROM article_s

2755 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2756 ), posts AS (

2757 SELECT author, count(author) post

2758 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2759 WHERE blogid IN (SELECT id FROM blog_s

2760 WHERE key='owner'

2761 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2762 AND key='text'

2763 GROUP BY author

2764 ), teams AS (

2765 SELECT user, group_concat(val, ', ') team

2766 FROM grp_mem_m

2767 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2768 AND key='team'

2769 GROUP BY user

2770 ), user_post AS (

2771 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2772 FROM mems m LEFT JOIN posts

2773 ON m.name=posts.author

2774 GROUP by m.rowid

2775 )

2776 SELECT

2777 CASE

2778 WHEN (SELECT user FROM grp_adm

2779 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2780 AND user=up.name) IS NOT NULL

2781 then 'k'

2782 ELSE ''

2783 END || rowid || ',' || gecos NAME,

2784 post POST, team TEAM

2785 FROM user_post up LEFT JOIN teams t

2786 ON up.name=t.user

2787 ORDER BY gecos;"

2788 ## err grpaction: "`echo \"$sql\"`"

2789 tf=$tmpd/title.$$

2790 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2791 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2792 for=\"cmadmin\">管理者委任</label>

2793 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2794 </p></div>"

2795 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2796 for=\"conf\">GRP登録解除</label>

2797 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2798 <p>この操作による通知は本人に行きません。

2799 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2800 </div>"

2801 # Get team list to which current user belongs into $hexteams

2802 allhexteams=$(hexteams "$grp")

2803 if [ -n "$isowner" ]; then

2804 myhexteams="$allhexteams" # admin can remove all teams' attr

2805 else

2806 myhexteams=$(hexteams "$grp" "$user")

2807 fi

2808 if [ -n "$myhexteams" ]; then

2809 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2810 for=\"cmrmteam\">チーム属性除去</label>

2811 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2812 を除去します: `cgi_checkbox teamconfirm yes` 確認

2813 <p>この操作による通知は本人に行きません。

2814 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2815 </div>

2816 "

2817 fi

2818 b1='<label> <input type="checkbox" name="usel" value="'

2819 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2820 br='<span id="reverse" title="Reverse Selection"></span>'

2821 #b2='"> <span>' b3='</span></label>'

2822 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2823 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2824 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2825 cgi_form grpaction<<EOF \

2826 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2827 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2828 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2829 | _m4 -D_TITLE_="spaste(\`$tf')" \

2830 -D_SUBTITLE_="チェック後操作ボタン" \

2831 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2832 $layout/form+dump.m4.html

2833 <p>下でチェックした人を対象として:</p>

2834 <div class="foldtabs">

2835 `cgi_radio rm addteam 'id="cmteam"'`<label

2836 for="cmteam">同じチーム属性を付与</label>

2837 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2838 `cgi_datalist_h teams $allhexteams`

2839 </div>

2840 ${rmteammsg}

2841 `cgi_radio rm send id="sendmsg"`<label

2842 for="sendmsg" title="Direct Message">DM送信</label>

2843 <div>

2844 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2845 >email書式を使う</label>

2846 <div class="folded">

2847 <table>

2848 <tr><td>From: </td><td>`myemail4group "$grp"`</td></tr>

2849 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2850 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2851 <tr><td>ファイル添付: </td>

2852 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2853 </table>

2854 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2855 </div>

2856 <div>本文:`cgi_textarea text "" cols=72`

2857 </div>

2858 </div>

2859 ${isowner:+$cmmsg$excmsg}

2860 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2861 </div>

2862 <h4>$grp 参加者一覧</h4>$fromtonote

2863 <table class="td2r thl">

2864 `sq $db -header -html "$sql"`

2865 </table>

2866 `cgi_hidden grp $grid`

2867 `cgi_hidden myuid $myuid id="myuid"`

2868 EOF

2869 }

2870 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2871 # Create TEMPORARY VIEW

2872 query<<EOF

2873 CREATE TEMPORARY VIEW writeusers AS

2874 SELECT DISTINCT author FROM article

2875 WHERE id in (

2876 select id from article where blogid=(select id from blog where rowid=$1)

2877 );

2878 CREATE TEMPORARY VIEW movablegroups AS

2879 SELECT g.rowid growid , g.gname

2880 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2881 ON grp.gname=gm.gname -- そのユーザが属している

2882 AND user='$user') g -- グループに絞る

2883 WHERE (SELECT author FROM writeusers

2884 EXCEPT

2885 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2886 IS NULL;

2887 $2

2888 EOF

2889 }

2890 sql4readableblogs() {

2891 # Create view of blogs that can be readable to $user

2892 # Blog is readable when:

2893 # 1: blog owner is an user

2894 # 2: else, 2.1: owner-group where the $user belongs

2895 # 2.2: else, owner-group is not moderated

2896 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2897 cat<<EOF ## | tee tmp/sql.out

2898 CREATE TEMPORARY VIEW readableblogs AS

2899 SELECT blog.rowid rid, id, author

2900 FROM blog

2901 NATURAL JOIN

2902 (SELECT id,

2903 max(CASE key WHEN 'owner' THEN val END) owner,

2904 max(CASE key WHEN 'mode' THEN val END) mode

2905 FROM blog_s GROUP by id) bs

2906 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2907 THEN 1 -- blog owner is an user, READABLE

2908 WHEN (SELECT val FROM grp_s

2909 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2910 AND

2911 (SELECT user FROM grp_mem

2912 WHERE gname=bs.owner AND user='$user') IS NULL

2913 THEN 0

2914 WHEN mode IN ('quiz', 'enquete')

2915 THEN 0 -- "quiz" mode blog cannot be searched

2916 ELSE 1

2917 END;

2918 EOF

2919 }

2920 editheading() { # $1=rowid-of-heading

2921 rowid=${1%%[!A-Z0-9a-z_]*}

2922 if [ -z "$rowid" ]; then

2923 echo "話題番号が未指定です。" | html p

2924 return

2925 fi

2926 owner=`getvalbyid blog owner $rowid`

2927 title=`getvalbyid blog title $rowid`

2928 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2929 | _m4 -D_TITLE_="修正" \

2930 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2931 -D_BLOGS_="" -D_DUMPTABLE_="" \

2932 -D_FORM_="syscmd(\`cat')" \

2933 $layout/html.m4.html $layout/form+dump.m4.html

2934 # Move to group

2935 if isuser "$owner"; then

2936 crview4article $rowid

2937 n=`query "SELECT count(*) FROM writeusers;"`

2938 ## err N=$n

2939 if [ $((n)) -gt 0 ]; then

2940 ## err ROWID=$rowid

2941 sql="SELECT growid || ':' || gname FROM movablegroups;"

2942 cat<<-EOF

2943 <div class="fold">

2944 `cgi_checkbox mv send id="mv"`<label

2945 for="mv">この話題をグループ所有に移動する</label>

2946 <div>

2947 <form action="?mvart" method="POST" enctype="multipart/form-data">

2948 移動先グループ:

2949 <select name="mv2grp">

2950 EOF

2951 query ".mode html"

2952 query<<-EOF |

2953 $sql

2954 .mode list

2955 EOF

2956 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2957 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2958 cat<<-EOF

2959 </select>

2960 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2961 そのグループに加入しているものに限られます)</p>

2962 <p>`cgi_checkbox cfm yes`<label>確認

2963 (この操作は元に戻すことができません)</label></p>

2964 `cgi_hidden blogrowid $rowid`

2965 `cgi_submit 移動`

2966 `cgi_reset Reset`

2967 </form>

2968 </div>

2969 </div>

2970 EOF

2971 fi

2972 # end of isuser "$owner"

2973 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2974 [ -n "$hexteams" ];}; then

2975 none="`echo なし|hexize`"

2976 cat<<-EOF

2977 <div class="fold">

2978 `cgi_checkbox mv2team send id="mv2team"`<label

2979 for="mv2team">この話題を以下のチームのものにする</label>

2980 <div><p>現在の所属チーム設定:

2981 `query "SELECT

2982 coalesce((SELECT val FROM blog_s

2983 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2984 AND key='team'),

2985 ':なし');"`</p>

2986 <form action="?mvart" method="POST" enctype="multipart/form-data">

2987 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2988 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2989 `cgi_hidden blogrowid $rowid`<br>

2990 `cgi_submit 移動`

2991 `cgi_reset Reset`

2992 </form></div></div>

2993 EOF

2994 fi

2995 }

2996 mvart() { # move diary to some group or team

2997 # or move blog of group to team which belong to the group

2998 blogrowid=`getpar blogrowid`

2999 cfm=`getpar cfm`

3000 ##### echo move blog:$blogrowid to $mv2grp | html p

3001 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3002 . ./s4-blog.sh

3003 if [ -z "$blogrowid" ]; then

3004 echo "無効な指定です(mvart)。" | html p

3005 return

3006 elif [ x"$cfm" != x"yes" ]; then

3007 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3008 elif { mv2grp=`getpar mv2grp`

3009 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3010 [ -n "$mv2grp" ]; }; then

3011 crview4article $blogrowid

3012 ########## TRANSACTION BEGIN

3013 query "BEGIN;"

3014 n=`query "SELECT count(*) FROM writeusers;"`

3015 ## err Nwriteuser=$n

3016 if [ $((n)) -gt 0 ]; then

3017 query<<-EOF

3018 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3019 WHERE key='owner'

3020 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3021 AND $mv2grp IN (SELECT growid FROM movablegroups);

3022 EOF

3023 fi

3024 query "END;"

3025 ########## TRANSACTION END

3026 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3027 [ -n "$mv2team" ];}; then

3028 # blog owner can move it to ANY team

3029 case "$mv2team" in

3030 'なし')

3031 cat<<-EOF

3032 DELETE FROM blog_s

3033 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3034 AND key='team';

3035 EOF

3036 ;;

3037 "") ;;

3038 *)cat<<-EOF

3039 BEGIN;

3040 REPLACE INTO blog_s(id, key, val)

3041 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3042 'team', '$mv2team');

3043 REPLACE INTO blog_s(id, key, val)

3044 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3045 'notify', 'all'); -- Change notify to all

3046 END;

3047 EOF

3048 esac | query

3049 fi

3050 blog_reply $blogrowid

3051 echo yes | html p

3052 }

3053 editart() { # $1=article-rowid $2=blogrowid

3054 rowid=${1%%[!A-Z0-9a-z_]*}

3055 blogrowid=${2%%[!A-Z0-9a-z_]*}

3056 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3057 echo "表示する記事番号が未指定です。" | html p

3058 return

3059 fi

3060 owner=`getvalbyid blog owner $blogrowid`

3061 title=`getvalbyid blog title $blogrowid`

3062 author=`getvalbyid article author $rowid`

3063 ## err EDITart: owner=$owner, author=$author

3064 if isgrpowner "$user" "$owner"; then

3065 : EDIT OK

3066 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3067 echo "本人か所有者しか編集できません." | html p

3068 return

3069 fi

3070 aid=`query "select id from article where rowid=$rowid;"`

3071 tmpout=$tmpd/editart.$$.out

3072 GF_ACTION="?replyblog+$blogrowid#$aid" \

3073 edittable $formdir/article.def article $rowid \

3074 > $tmpout

3075 rm -f /tmp/editart.out

3076 # Cannot use pipelining to m4 with genform() because of stdin stack

3077 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3078 -D_FORM_="syscmd(cat $tmpout)" \

3079 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3080 -D_BLOGS_= -D_DUMPTABLE_= \

3081 $layout/html.m4.html $layout/form+dump.m4.html

3082 }

3083 send2mem() {

3084 rowid=`getpar grp`

3085 rowid=${rowid%%[!0-9]*} # Cleaning

3086 if [ -z "$rowid" ]; then

3087 echo "グループが未指定です。" | html p

3088 return

3089 fi

3090 message=`getpar message`

3091 if [ -z "$message" ]; then

3092 echo "文章を入れてください。" | html p

3093 return

3094 fi

3095 grp=`getgroupbyid $rowid`

3096 members=`collectemail $grp`

3097 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3098 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3099 mailfrom="`gecos "$user"` <$mailfrom>"

3100 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3101 msg=$(cat<<-EOF

3102 $urlbase?grp+$rowid

3103 グループ $grp に所属する

3104 `gecos $user` さんよりメッセージ:

3105

3106 $message

3107 EOF

3108 )

3109 # smail rcpt subj (file)

3110 for m in $members; do

3111 echo "$msg" |

3112 MAIL_FROM=$mailfrom \

3113 SENDER=$noreply \

3114 REPLYTO=$mailfrom \

3115 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3116 smail "$members" "$sj"

3117 done

3118 cat<<EOF

3119 <p>以下のユーザに送信しました。</p>

3120 <pre>

3121 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3122 </pre>

3123 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3124 EOF

3125 }

3126 joingrpadmit() {

3127 # $1=yes/no $2=session-key

3128 if [ -z "$2" ]; then

3129 echo "bye bye" | html p; return

3130 fi

3131 t_usr=`session=$2 getpar user`

3132 t_grp=`session=$2 getpar group`

3133 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3134 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3135 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3136 echo "無効な加入依頼です。" | html p

3137 echo "有効期限が切れたか、

3138 他の管理者がいる場合は処理済みの可能性があります。" | html p

3139 return

3140 fi

3141 if ! isgrpowner "$user" "$t_grp"; then

3142 echo "グループ管理者のみの機能です。" | html p; return

3143 fi

3144 case $1 in

3145 yes) joingrp "$t_grp" "$t_usr" yes ;;

3146 no) joingrp "$t_grp" "$t_usr" no ;;

3147 *)

3148 echo "無効な指定です($1)。" | html p

3149 return ;;

3150 esac

3151 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3152 rcpts="`getgroupadminmails $t_grp` $user"

3153 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3154 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3155 に

3156 $t_usr

3157 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3158 しました。"

3159 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3160 query "delete from session where id='$2';"

3161 echo "$body" | html p

3162 }

3163

3164 joingrprequest() {

3165 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3166 jss="joingrp-`date +%s`-`genrandom 12`"

3167 addsession $jss +${memoplimitdays}days

3168 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3169 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3170 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3171 $url

3172 $user さんから

3173 グループ $1

3174 に加入依頼がありました。

3175

3176 承認する:

3177 $urlbase?joingrpadmit+yes+$jss

3178

3179 白紙に戻す:

3180 $urlbase?joingrpadmit+no+$jss

3181 EOF

3182 echo "管理者に加入依頼を出しました。

3183 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3184 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3185 }

3186 joingrp() {

3187 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3188 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3189 if isgrpowner "$user" "$1"; then

3190 isowner="yes"

3191 elif [ -n "$5" ]; then

3192 isowner="yes"

3193 else

3194 isowner=""

3195 fi

3196 ## err jg:isgrpowner: isowner="$isowner"

3197 if [ -n "$isowner" ]; then

3198 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3199 elif [ x"$2" != x"$user" ]; then # if user is not login user

3200 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3201 return

3202 elif [ x"$3" = x"no" ]; then

3203 : # Do not pursue those who leave

3204 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3205 : # Member can change own email address for the joining moderated group

3206 else # adding user is $user itself

3207 case `getgroupattr $1 regmode` in

3208 moderated)

3209 joingrprequest "$@" # Request only

3210 return

3211 ;;

3212 *)

3213 ;;

3214 esac

3215 fi

3216 qgname=`sqlquote "$1"`

3217 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3218 cond="where gname=$qgname and user='$2'"

3219 if [ x"$3" = x"yes" ]; then

3220 query "replace into grp_mem values($qgname, '$2');"

3221 # Notify joingrp to admin

3222 action="に加入しました。"

3223 if [ -n "$4" ]; then

3224 if msg=`emaildomaincheck "$4"`; then

3225 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3226 'string', '$4', NULL);"

3227 else

3228 echo $msg

3229 fi

3230 else

3231 query "delete from grp_mem_s $cond and key='email';"

3232 fi

3233 if [ -n "$5" ]; then # as ADMIN

3234 # Coming here means newly created group

3235 sql="select case\

3236 when (select count(*) from grp_mem where gname=$qgname)=1\

3237 then (select user from grp_mem\

3238 where gname=$qgname and user='$user')\

3239 else '' end; "

3240 err NewGrpChk: $sql

3241 if [ -n "`query \"$sql\"`" ]; then

3242 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3243 query "replace into grp_adm values($qgname, '$user');"

3244 fi

3245 fi

3246 else

3247 query "begin;

3248 delete from grp_mem $cond;

3249 delete from grp_mem_s $cond;

3250 delete from grp_mem_m $cond;

3251 delete from grp_adm $cond;

3252 delete from grp_adm_s $cond;

3253 delete from grp_adm_m $cond;

3254 end;"

3255 action="から脱退しました。"

3256 fi

3257 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3258 $url?grp+$grid

3259 $user (`gecos $user`)さんが

3260 グループ $1

3261 $action

3262 EOF

3263 }

3264 grp_add_team() (

3265 # $1=grp-rowid $2=team $3...=user-rowid(s)

3266 grp=`getgroupbyid $1`

3267 team=$2; shift; shift

3268 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3269 { echo "BEGIN;"

3270 for user; do

3271 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3272 '$grp',\

3273 (SELECT name FROM user WHERE rowid=$user),\

3274 'team', 'string', '$team');"

3275 done

3276 echo "END;"

3277 } | query

3278 )

3279 grp_rm_team() (

3280 # $1=grp-rowid $2=team $3...=user-rowid(s)

3281 grid=$1

3282 qgrp=$(sqlquote "`getgroupbyid $grid`")

3283 team=$2; shift; shift

3284 [ -z "$grid" -o -z "$team" ] && return

3285 { echo "BEGIN;"

3286 for user; do

3287 echo "DELETE FROM grp_mem_m\

3288 WHERE gname=$qgrp \

3289 AND user=(SELECT name FROM user WHERE rowid=$user)\

3290 AND key='team' AND val='$team';"

3291 done

3292 cat<<-EOF

3293 DELETE FROM blog_s

3294 WHERE rowid=(

3295 SELECT rowid

3296 FROM blog_s a

3297 WHERE key='team'

3298 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3299 AND NOT EXISTS (SELECT * FROM grp_mem_m

3300 WHERE key='team' AND val=a.val -- a.val=team

3301 AND gname = (SELECT val FROM blog_s b

3302 WHERE a.id=b.id AND key='owner')

3303 ));

3304 EOF

3305

3306 echo "END;"

3307 } | query

3308 )

3309 grp_reg_adm() {

3310 # $1=grp-rowid $2...=user-rowid

3311 grid=$1

3312 grp=`getgroupbyid "$1"`

3313 if [ -z "$grp" ]; then

3314 echo "無効なグループIDです" | html p; return

3315 fi

3316 if ! isgrpowner "$user" "$grp"; then

3317 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3318 fi

3319 shift

3320 for urid; do

3321 newadm=`query "select name from user where rowid=$urid;"`

3322 if [ -z "$newadm" ]; then

3323 echo "指定ユーザIDがおかしいようです。" | html p; return

3324 fi

3325 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3326 err ismember $newadm $grp

3327 if ismember $newadm "$grp"; then

3328 # OK, go ahead

3329 getgname="(select gname from grp where rowid=$grid)"

3330 query "replace into grp_adm values($getgname, '$newadm');"

3331 # confirm insertion

3332 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3333 if [ -n "`query \"$sql;\"`" ]; then

3334 echo "追加完了: $newadm" | html p

3335 else

3336 echo "追加失敗($1 $urid)" | html p

3337 fi

3338 fi

3339 showgroup $grid

3340 done

3341 }

3342 dt_rowhack() {

3343 # From: <TR>

3344 # ....

3345 # <TD>rowclass=foo</TD>

3346 # </TR>

3347 # To: <TR class="foo">....<TD>foo</TD></TR>

3348 sed -e '

3349 /^<TR>/ {

3350 :loop

3351 s/\n//

3352 N

3353 /<\/TR>/ {

3354 s/\n//

3355 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3356 n

3357 }

3358 $q

3359 b loop

3360 }'

3361 }

3362 dumptable() {

3363 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3364 # textのフィールドだけ全てダンプにしたほうがいいか

3365 # $DT_VIEW sets link

3366 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3367 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3368 VIEW=${DT_VIEW-replyblog}

3369 if [ -n "$VIEW" ]; then

3370 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3371 fi

3372 sqlfile=$tmpd/dump.sql

3373 : > $sqlfile # ensure to be empty

3374 printf '.mode html\n.header 1\n' > $sqlfile

3375 # $DT_CHLD=ChildTable:BindColumn

3376 if [ -n "$DT_CHLD" ]; then

3377 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3378 cat<<-EOF >> $sqlfile

3379 -- presql

3380 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3381 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3382 EOF

3383 # Speed up counting of new articles

3384 cat<<-EOF >> $sqlfile

3385 -- presql2

3386 DROP TABLE IF EXISTS _counts;

3387 CREATE TEMPORARY TABLE _counts AS

3388 SELECT $_i, count($_i) cnt

3389 FROM $_t GROUP BY $_i;

3390 /* Prepare NEW count table */

3391 CREATE TEMPORARY TABLE _target AS

3392 SELECT b.rowid trowid, b.id

3393 FROM "$2" b JOIN "$2_s" s

3394 ON b.id=s.id AND s.key='owner'

3395 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3396

3397 DROP TABLE IF EXISTS _children;

3398 CREATE TEMPORARY TABLE _children AS

3399 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3400 FROM (SELECT t.trowid, t.id $_i, a.id

3401 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3402 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3403

3404 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3405 DROP TABLE IF EXISTS _news;

3406 DROP VIEW IF EXISTS _news;

3407

3408 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3409 -- INSERT INTO _news

3410 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3411 CREATE TEMPORARY VIEW _news AS

3412 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3413 FROM (SELECT DISTINCT id FROM _target)

3414 a LEFT JOIN

3415 (SELECT $_i, count(ctime) newcnt

3416 FROM _children x

3417 WHERE ctime > coalesce((SELECT time from myacclog

3418 WHERE tblrowid=x.trowid),

3419 '1970-01-01')

3420 GROUP BY $_i) b

3421 ON a.id=b.$_i;

3422 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3423 EOF

3424 # REMOVE next line until 2019/5/1

3425 cntall="(select count($_i) from $_t where $_i=a.id)"

3426 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3427 # REMOVE next assignment until 2019/5/1

3428 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3429 and id in (select id from $_t where $_i=a.id) \

3430 and val > coalesce((select time from myacclog where \

3431 tblrowid=a.rowid),\

3432 '1970-01-01'))"

3433 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3434 cnt="$cntnew as '新着', $cntall as '総数',"

3435 dt_class=" td2r td3r dumpblogs"

3436 fi

3437 # Construct join expression

3438 eav="" scols=""

3439 pk=`gettblpkey $2`

3440 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3441 substr=${substr:-%s}

3442 for col in ${3:-`gettbl_s_cols $2`}; do

3443 valvar=val

3444 case $col in

3445 gecos) scols="$scols${scols:+, }${col#}"

3446 continue ;; # built-in column name

3447 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3448 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3449 case "$as" in

3450 *:*=*) cnd=${as#*:}

3451 h=${cnd%%=*} v=${cnd#*=}

3452 h=`sqlquotestr "$h"`

3453 v=`sqlquotestr "$v"`

3454 valvar="CASE val WHEN $h THEN $v END"

3455 as=${as%%:*} ;;

3456 esac

3457 ;;

3458 *) as=${col} ;;

3459 esac

3460 ss=`printf "$substr" "$valvar"`

3461 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3462 scols="$scols${scols:+, }b.$as"

3463 done

3464 #case author when '$user' then a.rowid else '---' end as ID,

3465 if [ -n "$DT_SQL" ]; then

3466 echo "$DT_SQL"

3467 else

3468 cat<<-EOF

3469 SELECT a.rowid as LINK, $cnt $scols

3470 FROM $2 a LEFT JOIN

3471 (SELECT $pk,$eav,

3472 max(CASE key

3473 WHEN 'owner'

3474 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3475 as gecos

3476 FROM ${2}_s c GROUP BY $pk)

3477 b ON a.$pk=b.$pk $4;

3478 EOF

3479 fi >> $sqlfile

3480 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3481 sqlog<<-EOF

3482 *** SQL-file: $sqlfile ***

3483 `cat $sqlfile`

3484 EOF

3485 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3486 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3487 cat $sqlfile >> tmp/sql

3488 # query ".read $sqlfile" > $tmpd/foo

3489 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3490 cp $tmpd/foo tmp/

3491 err "----- `gdate +%FT%T.%3N` ------------555555"

3492 ## $ddd LINE exists at the end of this function

3493 fi

3494 printf '.mode list\n.header 0\n' >> $sqlfile

3495 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3496 <div>

3497 <div class="dumptable">

3498 <table class="b$dt_class">

3499 `query ".read $sqlfile"`

3500 </table>

3501 </div>

3502 </div>

3503 EOF

3504 ### `query ".read $sqlfile"`

3505 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3506

3507 ## REMOVE THIS!

3508 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3509 }

3510

3511 par2table() (

3512 # copy current parameters of par into destination table

3513 # $1=definition-file

3514 # Using $user and $session

3515 # Return value:

3516 # 0: Stored successfully

3517 # 1: Insufficient fillings

3518 # 2: No permission to modify the record

3519 # 3: Invalid rowid

3520 # 4: SUCCESS to delete

3521 # 5: Stop deletion for lack of confirm check

3522 # 6: Password length too short

3523 # 7: Password mismatch

3524 # 8: Old password incorrect

3525 rowid=`getpar rowid`

3526 if [ ! -e $1 ]; then

3527 echo "テーブル定義ファイルが見付かりません" | html p

3528 exit 1

3529 fi

3530 tbl=${1%.def}

3531 tbl=${tbl##*/}

3532 if [ -n "$rowid" ]; then # Modify existing entry

3533 if [ x"$tbl" = x"user" ]; then

3534 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3535 elif [ x"$tbl" = x"grp" ]; then

3536 sql="select gname from $tbl where rowid=$rowid;"

3537 ##err p2t:grp:q $sql

3538 isgrpowner "$user" "`query $sql`" && rowowner=$user

3539 elif [ x"$tbl" = x"blog" ]; then

3540 # Check if owner in blog_s

3541 blogowner=`getvalbyid blog owner "$rowid"`

3542 if isgrpowner "$user" "$blogowner"; then

3543 rowowner=$user

3544 else

3545 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3546 fi

3547 else

3548 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3549 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3550 fi

3551 ### err rowowner=$rowowner

3552 if [ x"$user" != x"$rowowner" ]; then

3553 echo "他人のレコードはいじれないの" | html p

3554 return 2

3555 elif [ -z "$rowowner" ]; then

3556 echo "指定したレコードはないみたい" | html p

3557 return 3

3558 fi

3559 rm=`getpar rm` cfm=`getpar confirm`

3560 # Editing existent entry

3561 if [ x"$rm" = x"yes" ]; then

3562 if [ x"$rm$cfm" = x"yesyes" ]; then

3563 query "delete from $tbl where rowid=$rowid;"

3564 return 4

3565 else

3566 echo "消去確認のチェックがないので消さなかったの..." | html p

3567 return 5

3568 fi

3569 fi

3570 fi

3571

3572 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3573 if [ -n "$rowid" ]; then

3574 # Update of existing record

3575 for col in `gettblcols $tbl`; do

3576 val=`getparquote $col`

3577 [ -z "$val" ] && continue

3578 ## err query "update $tbl set $col=$val where rowid=$rowid"

3579 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3580 sql="update $tbl set $col=$val where rowid=$rowid;"

3581 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3582 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3583 ## User name cannot be changed with interface provided with this

3584 ## script. But we offer the trigger to change owner user

3585 ## of blog_s table.

3586 #err "select quote($col) from $tbl where rowid=$rowid;"

3587 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3588 cat<<-EOF | query

3589 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3590 -- should use EXCLUSIVE transaction outside of this.

3591 SAVEPOINT par2table;

3592 $sql

3593 update blog_s set val=$val

3594 where key='owner' and val=$old;

3595 RELEASE SAVEPOINT par2table;

3596 EOF

3597 ## XX: DIRTY Hack Ends here

3598 ## We should keep blog's owner as a single column which has

3599 ## foreign key constraint with primary key of grp/user.

3600 else

3601 query "$sql"

3602 fi

3603 done

3604 # Then, set up $pval for further insertion of tbl_s and tbl_m

3605 for col in `gettblpkey $tbl`; do

3606 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3607 pval="$pval${pval:+, }\"$val\""

3608 done

3609 else

3610 # New entry

3611 # Generate values() for primary keys

3612 for col in `gettblpkey $tbl`; do

3613 # Genuine primary keys for _m and _s

3614 val=`getvalquote $tbl $col`

3615 [ -z "$val" ] && continue

3616 pval="$pval${pval:+, }$val"

3617 done

3618 ##err pval=$pval

3619 for col in `gettblfkey $tbl`; do

3620 # args for values() to insertion into master table

3621 val=`getvalquote $tbl $col`

3622 [ -z "$val" ] && continue

3623 formaster=$formaster"${formaster:+, }$val"

3624 done

3625 formaster="$pval${formaster:+, }$formaster"

3626 ## err formaster=$formaster

3627 if [ -z "$formaster" ]; then

3628 echo "項目を全て埋めてください" | html pre

3629 return 1

3630 fi

3631 ## err "replace into $tbl values($formaster);"

3632 query "replace into $tbl values($formaster);"

3633 ## Insertion to master table, done

3634 fi

3635

3636 transaction=$tmpd/sqlfile.sql; touch $transaction

3637 for kt in s m; do

3638 tb2=${tbl}_$kt

3639 for col in `gettbl_${kt}_cols $tbl`; do

3640 ptype=`getpartype $col "limit 1"`

3641

3642 # First, check update of existing entries in _m

3643 if [ $kt = m ]; then

3644 # sessID|address.1.22|string|Somewhere-x.y.z

3645 sql=""

3646 ##err dots from query "select var from par where var like '$col.%';"

3647 for v in `query "select var from par where var like '$col.%';"`; do

3648 # v=address.1.22

3649 st_rowid=${v##*.}

3650 origcol=${v%%.*} # original column derived from

3651 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3652 ##case `getpartype $v` in

3653 ## err CASE `gettbl_coltype $tbl/$origcol` in

3654 ## err edit flag = `getpar action.$v`

3655 case `getpar action.$v` in

3656 rm)

3657 if [ x`getpar confirm.$v` = x"yes" ]; then

3658 newsql="delete from $tb2"

3659 else

3660 echo "削除確認未チェック" | html p

3661 fi ;;

3662 edit)

3663 case `gettbl_coltype $tbl/$origcol` in

3664 image|document|binary)

3665 file=$tmpd/`getparfilename $v`

3666 ## err type=file=$file

3667 [ -z "$file" ] && continue

3668 bn=`sqlquotestr "${file##*/}"`

3669 bin="X'"$(hexize "$file")"'"

3670 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3671 type=\"file:$ct\"

3672 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3673 cachedir=`getcachedir "$tbl/$rowid"`

3674 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3675 rm -rf $cachedir

3676 ;;

3677 *)

3678 newsql="update $tb2 set val=(select val from par where var \

3679 like '$col.%.$st_rowid')"

3680 ;;

3681 esac

3682 ;;

3683 *) # maybe "keep", do not modify value

3684 continue

3685 ;;

3686 esac

3687 # err newsql=$newsql

3688 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3689 done

3690

3691 if [ x"$bin" = x"NULL" ]; then

3692 ## err repl:normal sql=`echo $sql`

3693 if [ -n "$transaction" ]; then

3694 cat<<-EOF >> $transaction

3695 $sql

3696 DELETE FROM $tb2 WHERE type='string' AND val='';

3697 EOF

3698 else

3699 query "$sql

3700 delete from $tb2 where type='string' and val='';"

3701 ## err repl:normal done

3702 fi

3703 else

3704 # Binary update line is TOO LONG to pipelining

3705 sqlfile="$tmpd/sqlf.$$"

3706 if [ -n "$transaction" ]; then

3707 echo "$sql" >> $transaction

3708 else

3709 echo "$sql" > $sqlfile

3710 query ".read $sqlfile"

3711 fi

3712 fi

3713 # Rest of kt==m: set multiple mode

3714 nr=`getparcount $col`

3715 else

3716 nr=1 # for kt==s, number of records is 1

3717 fi

3718

3719 i=0

3720 while [ $i -lt $nr ]; do

3721 limit="limit 1 offset $i"

3722 i=$((i+1)) # increase beforehand against continue

3723 val=`getvalquote $tbl $col "$limit"`

3724 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3725 ## err $col=$val

3726 bin=NULL

3727 ## err partype$col=`getpartype $col "$limit"`

3728 case $ptype in

3729 file) file=$tmpd/`getparfilename $col "$limit"`

3730 ## err parfile-$col=$file

3731 [ -z "$file" ] && continue

3732 bin="X'"$(hexize "$file")"'"

3733 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3734 type=\"file:$ct\" ;;

3735 "*"*) continue ;; # foreign table

3736 *) type=\"string\" ;;

3737 esac

3738 case `gettbl_coltype $tbl/$col` in

3739 password) # special care for password

3740 # name={password,pswd1,pswd2}

3741 p1=`getpar pswd1 "$limit"`

3742 if [ -z "$p1" ]; then

3743 continue # SKIP password setting, if p1 is empty

3744 else

3745 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3746 ## err pswd=$pswd

3747 if pwcheck "$pswd"; then

3748 if [ x"$p1" = x"$p2" ]; then

3749 case "$p1" in

3750 ??????????*) ;;

3751 *) echo "パスワードは10字以上にしてください。" | html p

3752 return 6;;

3753 esac

3754 val="\"`echo $p1|mypwhash`\""

3755 else

3756 echo "2つの新パスワード不一致" | html p

3757 return 7

3758 fi

3759 else

3760 echo "旧パスワード違います" | html p

3761 return 8

3762 fi

3763 fi

3764 ;;

3765 esac

3766 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3767 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3768 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3769 if [ x"$bin" = x"NULL" ]; then

3770 ## err Normal-query: `echo $sql`

3771 if [ -n "$transaction" ]; then

3772 echo "$sql" >> $transaction

3773 else

3774 query "$sql"

3775 fi

3776 else

3777 sqlfile="$tmpd/query.$$"

3778 ## err sqlfile=`ls -lF $sqlfile`

3779 if [ -n "$transaction" ]; then

3780 echo "$sql" >> $transaction

3781 else

3782 echo "$sql" > $sqlfile

3783 query ".read $sqlfile"

3784 fi

3785 fi

3786 ## err p2t done

3787 done

3788 done

3789 done

3790 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3791 -- We cannot use transaction here, because groupupdate may use it.

3792 SAVEPOINT pa2table_insert;

3793 .read $transaction

3794 RELEASE SAVEPOINT pa2table_insert;

3795 EOF

3796 return $?

3797 ##err donee

3798 )

3799 genform() {

3800 # $1 = form definition file

3801 # $2, $3 (optional)= table name and ROWID

3802 # If $GF_VIEWONLY set and nonNull, output values without form

3803 # If $GF_ARGS set, use it as content-strings in the form

3804 # If $GF_OWNER set, use it as value of name="owner"

3805 # If $GF_STAGE set, use it as value of name="stage"

3806 forms="" hiddens="" rowid=$3

3807 if [ ! -e "$1" ]; then

3808 echo "そのようなデータベースはないようです($2)。" | html p

3809 return

3810 elif [ -n "$2" ]; then

3811 rec=`query "select * from $2 where rowid='$rowid';"`

3812 if [ -z "$rec" ]; then

3813 pk=`gettblpkey $2`

3814 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3815 rec=`query "select rowid from $2 where $pk='$rowid';"`

3816 rowid=$rec

3817 rec=$3

3818 fi

3819 if [ -z "$rec" ]; then

3820 echo "そんなレコードはないみたいね..." | html p

3821 return

3822 fi

3823 fi

3824 if [ -z "$GF_VIEWONLY" ]; then

3825 rm='<input id="rm" name="rm" type="checkbox"

3826 value="yes"><label for="rm">このエントリの削除</label>

3827 <span>ほんとうに消しますよ(確認)!

3828 <input name="confirm" type=checkbox value="yes">はい</span>'

3829 fi

3830 # Image Cache dir

3831 ## err genform: getcache=$2/$rowid

3832 td=`getcachedir "$2/$rowid"`

3833 while IFS=: read prompt name keytype type args; do

3834 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3835 sp="${args:+ }"

3836 form="" val=""

3837 if [ -n "$rowid" ]; then

3838 # err genform2a: Seeking for "$2.$name, type=$type"

3839 rawval=`getvalbyid $2 $name $rowid $td`

3840 val=`echo "$rawval"|htmlescape`

3841 ## err genform3a: getvalbyid $2 $name $rowid $td

3842 ## err genform3b: val="[$val]" type="$type"

3843 fi

3844 if [ -n "$GF_VIEWONLY" ]; then

3845 is_hidden "$2" "$name" && continue

3846 fi

3847 case "$type" in

3848 text*)

3849 cgiform=cgi_multi_$type

3850 if [ -s $td/$name.count -a -n "$val" ]; then

3851 form=`$cgiform $name $td`

3852 val=$(echo "$val"|

3853 while read fn; do

3854 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3855 </td></tr>$nl"

3856 done)

3857 val="<table>$nl$val$nl</table>"

3858 else

3859 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3860 form=`cgi_$type $name "$rawval" "$args"`

3861 fi

3862 ;;

3863 [Rr][Aa][Dd][Ii][Oo])

3864 fh="<label><input type=\"radio\" name=\"$name\""

3865 form="`echo $args|sed -e \

3866 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3867 ;;

3868 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3869 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3870 ;;

3871 [Ss][Ee][Ll][Ee][Cc][Tt])

3872 fh="<select name=\"$name\">$nl"

3873 form=$(for l in $args; do

3874 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3875 done)

3876 if [ -n "$val" ]; then

3877 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3878 fi

3879 form="$fh$form</select>"

3880 ;;

3881 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3882 if [ -s $td/$name.count ]; then

3883 form=`cgi_multi_file $name $td "$args"`

3884 if [ -n "$val" ]; then

3885 hrfb="$myname?showattc+$2_m"

3886 val=$(echo "$rawval" \

3887 | while read fn; do

3888 data=`percenthex "$td/$fn"`

3889 #ct=`cat $td/$fn.content-type`

3890 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3891 ri=`cat "$td/$fn.rowid"`

3892 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3893 #imgsrc="<img src=\"data:$ct,$data\">"

3894 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3895 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3896 done)

3897 fi

3898 else

3899 form="<input type=\"file\" name=\"$name\" $args>"

3900 if [ -n "$val" ]; then

3901 imgs=$(echo "$rawval"\

3902 |while read fn;do

3903 data=`percenthex "$td/$fn"`

3904 echo "<img src=\"data:image/png,$data\">$fn<br>"

3905 done)

3906 form=$form"<br>$imgs"

3907 val=$imgs # 2015-06-15

3908 else

3909 form="<input type=\"file\" name=\"$name\" $args>"

3910 fi

3911 fi

3912 ;;

3913 [Hh][Ii][Dd][Dd][Ee][Nn])

3914 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3915 args="value=\"$GF_STAGE\""

3916 fi

3917 form="<input type=\"hidden\" name=\"$name\" $args>"

3918 prompt='' # Remove prompt

3919 ;;

3920 [Aa][Uu][Tt][Hh][Oo][Rr])

3921 [ -n "$GF_VIEWONLY" ] && continue

3922 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3923 prompt="" ;;

3924 [Oo][Ww][Nn][Ee][Rr])

3925 [ -n "$GF_VIEWONLY" ] && continue

3926 val=${GF_OWNER:-$val}

3927 val=${val:-$user}

3928 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3929 prompt="" ;;

3930 [Uu][Ss][Ee][Rr])

3931 # XXX: is null $user ok?

3932 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3933 [ -n "$GF_VIEWONLY" ] && continue

3934 form="$user"

3935 ;;

3936 [Pp]assword)

3937 [ -n "$GF_VIEWONLY" ] && continue

3938 form="`cgi_passwd`"

3939 val=""

3940 ;;

3941 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3942 [ -n "$GF_VIEWONLY" ] && continue

3943 if [ -z "$rowid" ]; then

3944 val=`genserial`

3945 fi

3946 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3947 prompt="" ;;

3948 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3949 prompt=""

3950 ;;

3951 parent|path|blog*)

3952 prompt=""

3953 ;;

3954 "*"*)

3955 tail=$tail"``"

3956 continue ;;

3957 esac

3958 if [ -n "$prompt" ]; then

3959 if [ -n "${GF_VIEWONLY}" ]; then

3960 form=$val

3961 else

3962 :

3963 fi

3964 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3965 else

3966 hiddens=$hiddens$nl"$form"

3967 fi

3968 done < $1

3969 # enctype="multipart/form-data"

3970 cat<<EOF

3971 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3972 ${rowid:+$rm}

3973 <table class="b $2">

3974 $forms

3975 </table>$hiddens

3976 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3977 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3978 EOF

3979 if [ -z $GF_VIEWONLY ]; then

3980 cat<<EOF

3981 <input type="submit" name="sub" value="OK">

3982 <input type="reset" name="res" value="Reset">

3983 EOF

3984 fi

3985 cat<<EOF

3986 $GF_ARGS</form>

3987 $tail

3988 EOF

3989 }

3990 edittable() {

3991 # $1=form-def $2=table $3 rowid

3992 genform "$@"

3993 }

3994 viewtable() {

3995 GF_VIEWONLY=1 genform "$@"

3996 }

3997 showattc() {

3998 # $1=table_m $2=rowid &optional $3=RawFlag

3999 ## err \$1=$1 \$2=$2 \$3=$3

4000 if ! isfilereadable $user $1 $2; then

4001 contenttype; echo

4002 echo "このファイルは管理者のみしか見られません" | html p

4003 putfooter; exit

4004 fi

4005 idir=`umask 002; mktempd` || exit 1

4006 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4007 bin=$idir/$myname-$$.bin

4008 sql="select quote(bin) from $1 where rowid='$2';"

4009 ## err showattc: sql: $sql

4010 sq $db "$sql" | unhexize > $bin

4011 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4012 type=${tv%//*} fn=${tv#*//}

4013 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4014 ct=${type#file:}

4015 case $ct in # all text/* changed to text/plain

4016 text/*)

4017 charset=`nkf -g $bin|cut -d' ' -f1`

4018 case $charset in

4019 ASCII*) charset="" ;;

4020 esac

4021 if [ -z "$3" ]; then

4022 ct="text/html${charset:+; charset=$charset}"

4023 link="?showattc+$1+$2+raw"

4024 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4025 | sed 's,^,<span></span>,' \

4026 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4027 -D_LINK_="$link" \

4028 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4029 exit $?

4030 fi

4031 ct="text/plain${charset:+; charset=$charset}"

4032 ;;

4033 esac

4034 contenttype "$ct"

4035 echo "Content-Disposition: filename=\"$fn\""

4036 echo "Content-Length: " `cat $bin | wc -c`; echo

4037 #echo "Content-Type: " ${type#file:}; echo

4038 cat $bin

4039 }

4040 #

4041 # Some default stupid handler on CGI values

4042 #

4043 default_storedb() {

4044 # ARG: $1=table-def-file

4045 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4046 tbl=`basename $1`

4047 tbl=${tbl%.def}

4048 cols="`grep :text $1|cut -d: -f2`"

4049 col=`echo "$cols"|head -1`

4050 vcol=`getpar $col`

4051 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4052 if [ -n "$vcol" ]; then

4053 par2table $1

4054 else

4055 return 2 # No insertion occurred

4056 fi

4057 }

4058

4059 default_view() { # $1=def-file

4060 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4061 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4062 default_storedb "$@"

4063 query "select rowid from $tbl order by rowid desc;" \

4064 | while read rowid; do

4065 viewtable $1 $tbl $rowid

4066 done | _m4 -D_TITLE_="$tbl" \

4067 -D_FORM_="`genform $1`" \

4068 -D_DUMPTABLE_="syscmd(cat)" \

4069 $layout/html.m4.html $layout/form+dump.m4.html

4070 }

4071 default_viewtext() { # $1=def-file

4072 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4073 default_storedb "$@"

4074 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4075 | _m4 -D_TITLE_="$tbl" \

4076 -D_FORM_="`genform $1`" \

4077 -D_DUMPTABLE_="syscmd(cat)" \

4078 $layout/html.m4.html $layout/form+dump.m4.html

4079 }

4080 default_smail() {

4081 default_storedb "$@"

4082 if [ $? -eq 2 ]; then

4083 _m4 -D_TITLE_="入力" \

4084 -D_FORM_="`genform $1`" \

4085 -D_DUMPTABLE_="" \

4086 $layout/html.m4.html $layout/form+dump.m4.html

4087 return

4088 fi

4089 cond=""

4090 for pk in `gettblpkey $tbl`; do

4091 pv=$(sqlquote "$(getpar $pk)")

4092 cond="$cond${cond:+ and }$pk=$pv"

4093 done

4094 sql="select rowid from $tbl where $cond;"

4095 rowid=`query "$sql"`

4096 ## err smail1 - "$sql" "-> rowid=$rowid"

4097

4098 while IFS=: read prompt name keytype type args; do # Read from $1

4099 val=`getpar $name`

4100 if [ -n "$val" ]; then

4101 text="$text

4102 $prompt

4103 $name=$val

4104 ---------------------------------------------------------"

4105 fi

4106 case "$type" in

4107 image|document|file)

4108 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4109 fns=$(echo "$fn"|while read fn; do

4110 err mv $tmpd/$fn.orig $tmpd/$fn

4111 mv $tmpd/$fn.orig $tmpd/$fn

4112 rm $tmpd/$fn.rowid # Remove cache flag

4113 ## err "`ls $tmpd/$fn`"

4114 echo $fn

4115 done)

4116 files="$files $fns"

4117 ;;

4118 esac

4119 done < $1

4120 ## err FILES=$files "`ls -lF $tmpd`"

4121 subj="from ${REMOTE_ADDR}"

4122 (echo "$url"

4123 echo "への書き込みがありました。"

4124 echo "------"

4125 echo "$text"

4126 ) | (cd $tmpd &&

4127 err LS="`ls -lF`" &&

4128 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4129 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4130 echo "以下の内容で送信しました。" | html p

4131 viewtable $1 $tbl \

4132 `query "select rowid from $tbl order by rowid desc limit 1;"`

4133 echo "戻る" | html a "href=\"?\""

4134 }