s4: 5c9b7add02e9 s4-funcs.sh

s4

view s4-funcs.sh @ 628:5c9b7add02e9

Username search in group-home should carry grid parameter

author	HIROSE Yuuji <yuuji@gentei.org>
date	Wed, 22 Apr 2020 12:23:33 +0900
parents	86058f1bf9fa
children	8874225971ff

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 workdb=$dbdir/tmpdata.sq3

17 listentlimit=${LISTENTLIMIT:-30}

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

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

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

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

22 templ=${TEMPL:-templ}

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

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

25 imgdir=${IMGDIR:-img}

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

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

28 msgdir=$templ/msg

29 timeout="+2 days"

30 memoplimitdays="7"

31 dumpcollen=22

32 #thumbxy=120x120

33 thumbxy=96x96

34 iconxy_S=80x80

35 iconxy_M=400x400

36 maximagexy=1600x1600

37 ### maximagexy=400x400

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

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

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

42 file_accept_help="

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

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

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

46 "

47 file_warn="$file_accept_help

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

49 blogreadflagrowid=0

50 blogcutoffflagrowid=-1

51 whatsnewdays=${WHATS_NEW_DAYS:-14}

52 querylog=$tmpdir/query.log

53 searchlog=$tmpdir/search.log

55 tconfs=""

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

57 conftbl=_tblconf

58 nl="

59 "

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

61 iconcachekey="profimgcache_S"

62 case "$HTTP_USER_AGENT" in

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

64 *) touchpanel="" ;;

65 esac

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

68 : <<EOF

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

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

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

73 レポート提出管理

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

76 [2016]

77 7/12 根本への反省

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

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

81 [以下2015]

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

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

84 Web

85 締切設定

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

88 ○自分の提出物リスト

90 7/19 ○設置

91 ○一斉送信

92 ○getparfilename の tmpd の扱い

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

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

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

96 ○添付ファイル回収

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

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

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

100

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

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

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

104 ○レポート提出モード

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

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

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

108

109

110 7/9 ○管理者の追加

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

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

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

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

115

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

117

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

119 ○グループ検索

120

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

122

123

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

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

126

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

128 ○6/1 par2tableを triplex 対応に

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

130 →とすると一気に

131

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

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

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

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

136

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

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

139 ## 1. passwd

140 ## 2. grp

141 ## 3. grp_mem

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

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

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

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

146

147 ## ● listの定義:

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

149 ## ● hashの定義:

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

151

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

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

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

155 ## csv2sq3 で .csv.sq3 の Makefile

156

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

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

159 ## type := root | comment

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

161 ## unique(id, filename)

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

163

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

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

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

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

168 ## できたー!

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

170 ↓

171 ↓以下に変更

172 with recursive allmem as

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

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

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

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

177

178

179 with recursive allmem as

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

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

182 where allmem.val=grp_m.gname)

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

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

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

186

187

188 ## triggerもできた。

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

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

191

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

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

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

195 ## グループ管理

196 ## 個人情報管理

197 ##

198

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

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

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

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

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

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

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

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

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

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

209

210 ## ログテーブル

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

212

213

214

215 ■表設計

216 * 3つの表に分散管理

217 id格納表 + hash表 + list表

218 * *_s *_m

219

220

221

222 user, user_map, user_col

223

224 ■抽象エントリタイプ

225 * user

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

227 * group

228 権限判定に利用

229 * serial

230 自動idとして機能

231 * password

232 入力 type=passwordで入力

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

234 * session

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

236 * text

237 入力 type=text

238 * textarea

239 入力 textarea

240 * image|document

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

242 * owner

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

244 * gowner

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

246 * timestamp

247 datetime()

248 * parent

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

250 * path

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

252

253 格納タイプ

254 * list

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

256 * hash

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

258

259 オブジェクトタイプ

260 * entry

261 id, title, owner

262 * textpart

263 id, parentID, text

264 * binarypart

265 id, parentID, contenttype, filename, content

266 * content

267 hash(textpart), list(binarypart)

268 * topic

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

270 * reply

271 id, parentID, content

272 * blog

273 list(entry)

274 blog = [topic, list(reply)]

275

276

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

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

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

280 "author" => "taro",

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

282 "parent" => "/",

283 "path" => "/1",

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

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

286 {"serial" => 2,

287 "author" => "hanako",

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

289 "parent" => "/",

290 "path" => "/2",

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

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

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

294

295

296 user:=

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

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

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

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

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

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

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

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

305

306 変換表

307 /user/email=m

308

309 blog:=

310 シリアル:id:p:serial

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

312 所有者:owner:s:owner:

313 時刻:ctime:s:stamp:

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

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

316

317 article:=

318 シリアル:id:p:serial

319 筆者:author:s:owner

320 時刻:ctime:s:stamp:

321 参照元:parent:s:parent:

322 パス:path:s:path:

323 本文:text:s:textarea:

324 画像:image:m:image:

325

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

327

328

329 EOF

330

331 logstart() {

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

333 }

334 logend() {

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

336 }

337 sqlog() {

338 logstart

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

340 cat >> $querylog

341 else

342 echo "$*" >> $querylog

343 fi

344 logend

345 }

346 sq() {

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

348 logstart

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

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

351 else

352 echo "$@" >> $querylog

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

354 fi

355 logend

356 }

357 dbsetup() {

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

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

360 sqi=$tmpdir/sqi.$$

361 sqo=$tmpdir/sqo.$$

362 mkfifo $sqi $sqo

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

364 sq $db < $sqi &

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

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

367 exec 2>> $tmpdir/error.out

368 fi

369 exec 3>> $tmpdir/debug.out

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

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

372 rm $sqi

373 }

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

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

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

377 fi

378 }

379 cleanup() {

380 trap '' INT HUP EXIT TERM PIPE

381 echo .quit >&5

382 kill $sq3pid

383 kill $sq3pid

384 rm -f $sqo $sqi

385 rm -rf $tmpfiles

386 cleanup2

387 }

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

389 # simple redirection for the sake of speed.

390 query() {

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

392 echo ".output $sqo" >&5

393 logstart

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

395 tee -a $querylog

396 else

397 echo "$@" >> $querylog

398 echo "$@"

399 fi >&5

400 echo ".output stdout" >&5

401 cat $sqo

402 logend

403 }

404 _m4() {

405 #S4NAME=f,f,f

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

407 }

408 ismember() {

409 # $1=user, $2=group

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

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

412 }

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

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

415 }

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

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

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

419 }

420 isgrpowner() (

421 # $1=user, $2=group

422 gn=`sqlquote "$2"`

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

424 err isgrpowner: $sql

425 test -n "`query $sql`"

426 )

427 isgrpownerbygid() (

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

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

430 err isgrpownerbygid: $sql

431 test -n "`query $sql`"

432 )

433 getgroupadminmails() {

434 # $1=group

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

436 email4group "$1" "$i" ;

437 done

438 }

439 getgroupadmins() { # $1=group

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

441 qgrp=`sqlquote "$1"`

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

443 }

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

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

446 getvalbyid grp $2 \

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

448 }

449 getgroupbyid() {

450 # $1=id|gname

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

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

453 # err ggbyid: `echo $sql`

454 query $sql

455 }

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

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

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

459

460 # Return true when anonymous mode

461 [ "$anonymousmode" ] && return 0

462 # case `getvalbyid blog mode $2` in

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

464 # *closed*)

465 # owner=`getvalbyid blog owner $2`

466 # if isgrp $owner; then

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

468 # elif isuser $owner; then

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

470 # fi

471 # esac

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

473 sql="with getblog as (\

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

475 select blogid from article where id in\

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

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

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

479 select case\

480 when (select author from article where\

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

482 then 'author'\

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

484 then 'open'\

485 when (select val from getmode) is null \

486 then 'open'

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

488 then (select user from grp_adm where \

489 gname=(select val from getowner) and \

490 user='$1')\

491 when (select author from article where\

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

493 then 'user+author'

494 else '' end;"

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

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

497 result=`query "$sql"`

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

499 return 2

500 }

501 linkhome() {

502 # $1=UserOrGroup

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

504 if isuser $1; then

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

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

507 else

508 echo -n "grp+$1"

509 fi

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

511 }

512 hreflink() {

513 # s4 specific notation:

514 # ^href=URL

515 # ^iframe=URL

516 # ^video=URL

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

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

519 # OSM umap Wikistyle Notation:

520 # [[URL]] - Simle Link

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

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

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

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

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

526 # Other Style

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

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

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

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

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

532 # SPC+SPC+$ - <br>

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

534 checkboxON="${cb} checked>"

535 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

560

561 }

562 minitbl() {

563 sed -n '

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

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

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

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

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

569 H; # Concat this line to HoldSpace

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

571 $ b cont

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

573 }

574 :cont

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

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

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

578 # . of ^. is workaround for FreeBSD sed

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

580 p; # Print whole "table" element

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

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

583 }

584 x; # Back to the newest line

585 p; # Print rest' | miniul

586 }

587 miniul() {

588 sed -e '

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

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

591 :top

592 s/\n//;

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

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

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

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

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

598 $ b cont

599 N; # 次の行を読む

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

601 /^ /b top

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

603 p; s/.*//;

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

605 /^\* /b top

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

607 }

608

609 :cont

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

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

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

613 H; x

614 }

615 x'

616 }

617 acclog() (

618 # $1=table, $2=rowid

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

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

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

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

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

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

625 fi

626 )

627 gecos() (

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

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

630 )

631 setpar() {

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

633 }

634 replpar() {

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

636 }

637 getpar() {

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

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

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

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

642 fi

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

644 case "$var" in

645 owner)

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

647 echo $user; return

648 elif ismember $user $val; then

649 echo $val; return

650 fi ;;

651 esac

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

653 echo "$val"

654 }

655

656 getpartype() {

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

658 }

659 getparcount() {

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

661 }

662 getparfilename() {

663 # null if type of $1 is not file

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

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

666 }

667 sqlquote() {

668 (v="$1"

669 case "$v" in

670 "") return ;; # null

671 "X'"*) # quoted hex string

672 echo $1 ;;

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

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

675 echo "\"$v\""

676 return ;;

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

678 echo "\"$v\""

679 return ;;

680 *)

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

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

683 else

684 echo "\"$v\""

685 fi ;;

686 esac)

687 }

688 sqlquotestr() (

689 case "$1" in

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

691 echo "'$v'" ;;

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

693 esac

694 )

695 mktempd() {

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

697 }

698 getcachedir() { # $1=maintable

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

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

701 else

702 echo $tmpd/$thumbxy

703 fi

704 }

705 getval() {

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

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

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

709 echo "$user" ;;

710 stamp|datetime)

711 date "+%F %T" ;;

712 serial)

713 (s=`getpar $2`

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

715 *)

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

717 esac

718 }

719

720 getvalquote() {

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

722 (v=`getval "$@"`

723 case "$v" in

724 "") echo NULL ;;

725 *) sqlquote "$v" ;;

726 esac)

727 }

728 getparquote() {

729 sqlquote "`getpar $1`"

730 }

731 getbinbyid() {

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

733

734 }

735 getvalbyid() {

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

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

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

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

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

741

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

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

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

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

746 return

747 fi

748 done

749 rowid=$3

750 pk=`gettblpkey $1`

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

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

753 td=${4:-$tmpd}

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

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

756 for kt in s m; do

757 t=${1}_$kt

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

759 vcount=1 # count(val)

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

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

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

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

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

765 if [ $kt = m ]; then

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

767 # Reset val to store filenames if type is string

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

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

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

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

772 i=0

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

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

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

776 i=$((i+1))

777 fn=$c.$i

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

779 case $type in

780 file:*)

781 #file=$td/$val

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

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

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

785 # FOR SPEED: Skip file generation if imgcache exists

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

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

788 && continue

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

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

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

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

793 select rowid from $cond $slice;

794 .output '$td/$fn'

795 select val from $cond $slice;

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

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

798 .output stdout

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

800 EOF

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

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

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

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

805 ls -lh "$file" |

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

807 case "$type" in

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

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

810 *|*:[Aa]pplication*)

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

812 png:- > "$file"

813 ;;

814 esac

815 ;;

816 *)

817 sq $db<<EOF

818 .output $td/$fn.rowid

819 select rowid from $cond $slice;

820 .output $td/$fn

821 select val from $cond $slice;

822 EOF

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

824 ;;

825 esac

826 done

827 else

828 rm -f $td/$c.count

829 case $type in

830 file:*)

831 echo "$val" \

832 | while read fn; do

833 file=$td/$fn

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

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

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

837 | unhexize > "$file"

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

839 case $type in

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

841 *:[Aa]pplication*)

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

843 png:- > $file ;;

844 esac

845 fi

846 done

847 ;;

848 esac

849 fi

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

851 return

852 fi

853 done

854 done)

855 }

856 getvalbypkey() (

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

858 pk=`gettblpkey $1`

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

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

861 )

862 getvalbycond() {

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

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

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

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

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

868 fi

869 }

870 getpwfield() {

871 # getpwfield user column

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

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

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

875 echo "$val"

876 return 0

877 else

878 return 1

879 fi

880 }

881 numericalize() {

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

883 }

884 encode() {

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

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

887 sha1=sha1

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

889 sha1=sha1sum

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

891 sha1=gsha1sum

892 fi

893 fi

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

895 }

896 enjpeg() {

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

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

899 cjpeg="cjpeg"

900 else

901 cjpeg="convert - jpeg:-"

902 fi

903 fi

904 $cjpeg "$@"

905 }

906 mycrypt() (

907 key=$1 salt=$2

908 # err \$2=$2

909 case $2 in

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

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

912 esac

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

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

915 )

916 hexize() {

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

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

919 hexize="xxd -p"

920 else

921 hexize_hd() {

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

923 }

924 hexize="hexize_hd"

925 fi

926 fi

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

928 }

929 unhexize() {

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

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

932 unhex="xxd -p -r"

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

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

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

936 EOF

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

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

939 # world writable...:)

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

941 fi

942 fi

943 cat "$@" | $unhex

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

945 }

946 percenthex() {

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

948 }

949 htmlescape() {

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

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

952 }

953 enascii() {

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

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

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

957 else

958 enascii_now=`date +%FT%T`

959 enascii_sed() {

960 nkf -Z0Z1Z2 \

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

962 }

963 enascii="enascii_sed"

964 fi

965 fi

966 cat "$@" | $enascii

967 }

968 size_h() {

969 i="$1" oi=$1

970 set -- B B KB MB GB TB

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

972 oi=$i

973 i=$((i/1024))

974 shift

975 done

976 echo ${oi}$1

977 }

978 gettblconf() {

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

980 ## tconfs=`sq $db \

981 tconfs=`query \

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

983 fi

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

985 }

986 gettblkeys() {

987 # $1=tbl

988 gettblconf

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

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

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

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

993 case $type in

994 *p*)

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

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

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

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

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

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

1001 esac

1002 case $type in

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

1004 esac

1005 done

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

1007 }

1008 gettblpkey() {

1009 # $1=tbl

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

1011 }

1012 gettblfkey() {

1013 (x=`gettblkeys $1`

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

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

1016 }

1017 gettblcols() {

1018 (x=`gettblkeys $1`

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

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

1021 }

1022 gettbl_s_cols() {

1023 (x=`gettblkeys $1`

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

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

1026 }

1027 gettbl_m_cols() {

1028 (x=`gettblkeys $1`

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

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

1031 }

1032 gettbl_h_cols() {

1033 (x=`gettblkeys $1`

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

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

1036 }

1037 gettbl_coltype() (

1038 gettblconf

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

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

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

1042 )

1043 is_hidden() {

1044 # $1=Tbl $2=col

1045 gettblconf

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

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

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

1049 case $x in

1050 *h*) return 0 ;;

1051 *) return 1 ;;

1052 esac

1053 }

1054

1055 dbsetbyid() {

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

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

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

1059 quotedp=$(sqlquotestr "$p")

1060 unset primary update

1061 gettblconf

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

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

1064 #err conf=$conf

1065 case ${conf#*=} in

1066 p*) primary=1 ;;

1067 f*) update=1 ;;

1068 u*) ;;

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

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

1071 esac

1072 #err t=$t

1073 type=string fn=""

1074 case $conf in

1075 */password)

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

1077 ;;

1078 */image*|*/document*)

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

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

1081 ;;

1082 esac

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

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

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

1086 if [ "$primary" ]; then

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

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

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

1090 elif [ "$update" ]; then

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

1092 else

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

1094 fi

1095 )

1096 }

1097 expire() (

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

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

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

1101 )

1102 addsession() {

1103 # expireをセット

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

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

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

1107 # Remove old session parameters

1108 now=`expire now`

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

1110 }

1111 gencookie() (

1112 for kv; do

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

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

1115 done

1116 )

1117 contenttype() {

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

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

1120 }

1121 putheader() {

1122

1123 }

1124 putfooter() {

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

1126 }

1127 getcookie() (

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

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

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

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

1132 done

1133 )

1134 genrandom() {

1135 # $1=columns (default: 10)

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

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

1138 }

1139 genserial() {

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

1141 }

1142 smail() {

1143 # smail rcpts subj (file)

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

1145 # $MAIL_FROM <- From: header value

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

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

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

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

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

1151 }

1152 smail_queue_flush() {

1153 # $1=timelimit

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

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

1156 SELECT rowid FROM smailq

1157 GROUP BY rcpts, subj

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

1159 EOF

1160 )

1161 for rid in $rowids; do

1162 sq $workdb \

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

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

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

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

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

1168

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

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

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

1172 .separator "\n" "------------------\n\n"

1173 SELECT time, text FROM smailq

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

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

1176 ORDER by time;

1177 EOF

1178 then

1179 echo rowid=$rid

1180 cat <<-EOF | sq $workdb

1181 DELETE FROM smailq

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

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

1184 EOF

1185 fi

1186 fi

1187 done

1188 }

1189 smail_queue() {

1190 # $1=Rcpts, $@=subj

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

1192 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1201 EOF

1202 )

1203 ## XXX: Adhoc load mitigation

1204 case "$now" in

1205 *[01])

1206 err flush_queue=$mintime

1207 smail_queue_flush ;;

1208 esac

1209 fi

1210 }

1211 setviastring() {

1212 table=$1

1213 oifs="$IFS"

1214 IFS="&"

1215 for us in $2; do

1216 k=${us%%=*}

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

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

1219 #echo $k=$v

1220 done

1221 IFS="$oifs"

1222 }

1223 checkdomain() (

1224 # Check the validity of domain by referring DNS

1225 item=$1

1226 err checkdomain $1

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

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

1229 )

1230 pwcheck() {

1231 # $1=passwd

1232 dbpswd=`getpwfield $user pswd`

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

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

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

1236 }

1237 mypwhash() {

1238 mycrypt "`cat`" `genrandom 5`

1239 }

1240 wasureta() {

1241 user=$1

1242 if ! checkdomain $user; then

1243 contenttype; echo

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

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

1246 putfooter

1247 exit 0

1248 fi

1249 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1251 encpswd=`echo $newpswd|mypwhash`

1252 dbsetbyid user $user pswd "$encpswd"

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

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

1255 $msgdir/mail-newaccount.m4 \

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

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

1258 }

1259 checkauth() {

1260 user=`getpar user`

1261 skc=`getpar skey` # from cookie

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

1263 skey="`getpwfield $user skey`"

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

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

1266 return 0

1267 fi

1268 fi

1269 pswd=`getpar pswd`

1270 quser=`sqlquotestr "$user"`

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

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

1273 return 1

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

1275 wasureta "$user"

1276 return 1 # wasureta error

1277 fi

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

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

1280 if pwcheck "$pswd"; then

1281 newsession=`genrandom 50`

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

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

1284 gencookie "user=$user" "skey=$newsession"

1285 return 0

1286 else

1287 return 4 # Heavy load??

1288 fi

1289 fi

1290 return 2 # Password mismatch

1291 }

1292 showlogin() {

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

1294 s4name=${S4NAME:-s4}

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

1296 cat $layout/login.m4.html

1297 echo '</body></html>'

1298 ) | _m4 \

1299 -D_BODYCLASS_="login" \

1300 -D_TITLE_="$s4name" \

1301 -D_SYSNAME_="Welcome to $s4name" \

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

1303 exit 0

1304 }

1305 dologin() {

1306 checkauth

1307 st=$?

1308 if [ $st != 0 ]; then

1309 contenttype; echo

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

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

1312 showlogin # and EXIT

1313 fi

1314 }

1315

1316 # Do instant jobs here

1317 dbsetup

1318 trap cleanup INT HUP EXIT TERM PIPE

1319 # trap cleanup INT HUP

1320

1321 err() {

1322 echo "$@" 1>&3

1323 }

1324

1325 cgiinit() {

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

1327 tmpf=tmp/stream

1328 tmpd=`tmpd=$tmpdir mktempd`

1329 tmpfiles=$tmpfiles" $tmpd"

1330 addsession $session

1331 getcookie

1332 case "$REQUEST_METHOD" in

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

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

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

1336 # cat > $tmpf # too much?

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

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

1339 s="`cat tmp/stream`"

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

1341 ;;

1342 esac

1343 case "$CONTENT_TYPE" in

1344 *boundary*)

1345 bndry=${CONTENT_TYPE#*boundary=}

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

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

1348 do

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

1350 #echo u=$us

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

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

1353 # err k=$k v=$v

1354 case "$k" in

1355 *:filename)

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

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

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

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

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

1361 :

1362 fi

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

1364 case "$mimetype" in

1365 [Ii]mage/x-xcf)

1366 bzip2 "$tmpd/$v"

1367 v=${v}.bz2

1368 ;;

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

1370 [Ii]mage/*)

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

1372 ;;

1373 esac

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

1375 then

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

1377 continue

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

1379 replpar text string \

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

1381 continue

1382 fi

1383 ;;

1384 *)

1385 type='string'

1386 ;;

1387 esac

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

1389 setpar "$k" "$type" "$v"

1390 done

1391 ;;

1392 *)

1393 setviastring par "$s"

1394 ;;

1395 esac

1396 }

1397 email4group() {

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

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

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

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

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

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

1404 and s.key='email'

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

1406 query "$sql"

1407 }

1408 email4groupbyuid() {

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

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

1411 uids=`echo "$@"`

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

1413 sql="WITH

1414 grpemails AS (

1415 SELECT gname, user, val email

1416 FROM grp_mem NATURAL JOIN grp_mem_s

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

1418 useremails AS (

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

1420 FROM user

1421 LEFT JOIN user_m

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

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

1424 FROM useremails u LEFT JOIN grpemails g

1425 ON u.name=g.user

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

1427 ## err email4gByid `echo $sql`

1428 query "$sql"

1429 }

1430 collectmembersbyid() {

1431 # Collect user names of group specified by grid

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

1433 query "SELECT user FROM grp_mem \

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

1435 }

1436 collectmembersbyid() {

1437 # Collect user names of group name

1438 qgrp=`sqlquote "$1"`

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

1440 }

1441 collectgecosesbyid() {

1442 # Collect user gecoses of group

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

1444 query<<-EOF

1445 SELECT gecos

1446 FROM gecoses

1447 WHERE name IN (SELECT user FROM grp_mem

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

1449 EOF

1450 }

1451 collectemail() (

1452 # Collect email addresses for group $1

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

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

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

1456 for e; do

1457 if isuser "$e"; then

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

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

1460 else

1461 qgrp=`sqlquote "$e"`

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

1463 gmem="grp_mem"

1464 else

1465 tm=`sqlquote "$TEAM"`

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

1467 fi

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

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

1470 $gmem g left join grp_mem_s s

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

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

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

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

1475 query "$sql"

1476 fi

1477 done

1478 )

1479 sendinvitation() (

1480 # $1=email

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

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

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

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

1485 gecos=`gecos`

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

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

1488 _m4 -D_URL_="$urlbase" \

1489 -D_USER_="$name" \

1490 -D_EMAIL_="$1" \

1491 -D_REGIST_="$regist" \

1492 -D_ADMIN_="$admin" \

1493 $msgdir/mail-invite.m4 \

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

1495 return 0

1496 )

1497 emaildomaincheck() {

1498 case "$1" in

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

1500 *@*)

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

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

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

1504 return 2

1505 fi

1506 return 0

1507 ;;

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

1509 esac

1510 }

1511 invite() {

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

1513 case "$email" in

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

1515 *@*)

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

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

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

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

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

1521 elif sendinvitation $email; then

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

1523 else # Cannot be reached here

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

1525 fi ;;

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

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

1528 esac

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

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

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

1532 fi

1533 if [ -f $invite_policy ]; then

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

1535 else

1536 pol="$invite_policy"

1537 fi

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

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

1540 -D_INVITE_POLICY_="$pol" \

1541 $layout/html.m4.html $layout/invite.m4.html

1542 }

1543 regist() {

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

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

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

1547 echo "bye bye" | html p

1548 reutrn

1549 fi

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

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

1552 cat<<EOF

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

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

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

1556 EOF

1557 return

1558 fi

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

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

1561 # Fake login password to wasureta

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

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

1564 wasureta $email

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

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

1567 addsession $1 # for removal after 1 minute

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

1569 $layout/login.m4.html

1570 return

1571 }

1572 group_safename() {

1573 # Convert $1 to safe group name

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

1575 }

1576 groupupdate() {

1577 gname=`getpar gname`

1578 qgname=`sqlquote "$gname"`

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

1580 # See ALSO same job in showgroup()

1581 newgname=`group_safename "$gname"`

1582 err newgname=$newgname

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

1584 err NewGNAME: gname=$newgname

1585 gname=$newgname

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

1587 replpar gname string "$gname"

1588 fi

1589 # Name confliction check

1590 parow=`getpar rowid`

1591 ## err parow=$parow

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

1593 query "BEGIN EXCLUSIVE;"

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

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

1596 if [ $count -gt 0 ]; then

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

1598 query "END;"

1599 return

1600 fi

1601 par2table $formdir/grp.def

1602 query "END TRANSACTION;"

1603 # Remove orphan

1604 : <<EOF

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

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

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

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

1609 EOF

1610 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1614 sql="delete from blog where id in

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

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

1617 query "$sql";

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

1619 return # and return

1620 fi

1621 fi

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

1623 fi

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

1625 grid=$(query $sql)

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

1627 grp $grid

1628 }

1629 groupclone() {

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

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

1632 case "$1" in

1633 */noteam)

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

1635 esac

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

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

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

1639 return

1640 fi

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

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

1643 return

1644 fi

1645 i=0

1646 while true; do

1647 copy="-copy$i"

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

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

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

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

1652 i=$((i++))

1653 continue

1654 fi

1655 break

1656 done

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

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

1659 query<<-EOF

1660 BEGIN;

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

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

1663 SELECT $newqname, key, val

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

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

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

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

1668 -- Copy members and their configuration --

1669 REPLACE INTO grp_mem SELECT $newqname, user

1670 FROM grp_mem WHERE gname=$qgrp;

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

1672 FROM grp_mem_s WHERE gname=$qgrp;

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

1674 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1675 -- Copy administrators --

1676 REPLACE INTO grp_adm SELECT $newqname, user

1677 FROM grp_adm WHERE gname=$qgrp;

1678 COMMIT;

1679 EOF

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

1681 STOPCLONEMSG=1 groupconf "$newrowid"

1682 }

1683 groupman() {

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

1685

1686 GF_STAGE="grpconf"

1687 GF_STAGE=groupupdate

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

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

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

1691 -D_DUMPTABLE_="syscmd(cat)" \

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

1693 }

1694 userconf() {

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

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

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

1698 }

1699 groupconf() {

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

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

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

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

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

1705

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

1707 grp=`getgroupbyid $rowid`

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

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

1710 return

1711 fi

1712

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

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

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

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

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

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

1719 <div>

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

1721 <table>

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

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

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

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

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

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

1728 </table>

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

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

1731 </div>

1732 EOF

1733 fi

1734 }

1735 mems() {

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

1737 kwd=`getpar kwd`

1738 listmember $kwd

1739 }

1740 grps() {

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

1742 kwd=`getpar kwd`

1743 listgroup $kwd \

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

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

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

1747 $layout/form+dump.m4.html

1748 }

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

1750 gpg=`getpar grp`

1751 grid=${1:-$gpg}

1752 grp=`getgroupbyid "$grid"`

1753 ## . ./s4-blog.sh

1754 jg=`getpar joingrp`

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

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

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

1758 fi

1759 htmlheader=$layout/html.m4.html

1760 showgroup "$grid"

1761 }

1762 sql4interestblogs() {

1763 cat<<EOF

1764 CREATE TEMPORARY VIEW interestblogs AS

1765 SELECT blog.rowid rid, id, author

1766 FROM blog

1767 NATURAL JOIN

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

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

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

1771 WHEN (SELECT user FROM grp_mem

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

1773 THEN 0

1774 ELSE 1

1775 END;

1776 EOF

1777 }

1778 listnewblogsql() { # $1=user

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

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

1781 newdays=${newdays:-14}

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

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

1784 WHERE user='$user'

1785 AND tblrowid IN

1786 ($blogreadflagrowid,

1787 $blogcutoffflagrowid)),

1788 $basetime -- "0"

1789 );"`

1790 cat<<EOF

1791 `sql4interestblogs`

1792 WITH article_ctime as (

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

1794 FROM article join article_s s using(id)

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

1796 GROUP BY id

1797 ), blog_title_owner as (

1798 SELECT blg.rid brid, id,

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

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

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

1802 ), blogall as (

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

1804 ), news as (

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

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

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

1808 FROM blogall bl

1809 LEFT JOIN

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

1811 ON bl.brid=al.tblrowid

1812 WHERE atime < bl.ctime

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

1814 LIMIT 10

1815 ) SELECT brid LINK, "新着",

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

1817 ctime, title,

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

1819 FROM news;

1820 EOF

1821 }

1822

1823 search_form() {

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

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

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

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

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

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

1830 @week → 最近一週間

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

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

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

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

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

1836 auth=""

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

1838 case "$1" in

1839 author=*)

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

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

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

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

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

1845 ;;

1846 grp=*)

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

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

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

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

1851 ;;

1852 esac

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

1854 cat<<-EOF

1855 <div class="right">

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

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

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

1859

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

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

1862

1863 </form>

1864 </div>

1865 EOF

1866 }

1867

1868 imgsrc_cache() (

1869 # $1 = directory for cache'ing

1870 # $2 = table (user_m or grp_m)

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

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

1873 dir="$1" tbl="$2"

1874 keycond="$3"

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

1876 ORDER BY rowid DESC LIMIT 1"

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

1878 tmpf=$tmpd/imgsrc_cache.$$

1879 case "$4" in

1880 [Ss]) size=S ;;

1881 [Oo]) size=O ;;

1882 *) size=M ;;

1883 esac

1884 # ImageCache filename storing schema:

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

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

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

1888 valtype=`query "$sql0"`

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

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

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

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

1893 case "$filetype" in

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

1895 esac

1896 fi

1897 cacheimg_S=$dir/S_$filename

1898 cacheimg_M=$dir/M_$filename

1899 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1907 else

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

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

1910 fifo2=$fifo.2

1911 mkfifo $fifo $fifo2

1912 fmt=${filename##*.}

1913 ## [[ NOTE ]]

1914 ## a. convert oldimage newimage

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

1916 ## b is much smaller than a

1917 cat $tmpf | unhexize \

1918 | tee $fifo \

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

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

1921 | tee $fifo2 \

1922 | convert - "$cacheimg_M" &

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

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

1925 | convert - "$cacheimg_S" &

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

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

1928 echo '">'

1929 echo "$sum" > $sumfile

1930 fi

1931 ## Now preparing cache image, done.

1932 ## Store this information to DB

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

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

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

1936 query <<-EOF

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

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

1939 EOF

1940 )

1941

1942 showhome() {

1943 # $1=userRowIdToShow

1944 err showhome \$1=$1

1945 case "$1" in

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

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

1948 esac

1949 ## err ShowHome: uname=$uname

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

1951 gecos=`gecos "$uname"`

1952 ## err SH:gecos=$gecos

1953 GF_VIEWONLY=1

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

1955 search_form_args=""

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

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

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

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

1960 # Display folders

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

1962 in (select id from article where author='$user')

1963 and type like 'file:%';"

1964 ## err nfile-sql=`echo "$sql"`

1965 nfile=`query "$sql"`

1966 # err nfile=$nfile

1967 if [ $nfile -gt 0 ]; then

1968 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

1969 title=\"L\">過去の提出ファイル</a>"

1970 fi

1971 else

1972 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

1973 GROUP BY user;"`

1974 usermenu="<p>Last seen on $latestlog</p>"

1975 search_form_args="author=$uname"

1976 fi

1977 . ./s4-blog.sh

1978

1979 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

1980 search_form "$search_form_args" > $sf

1981 printf "%s さん" "$gecos" > $tf

1982 { echo "<div class=\"noprofimg\">"

1983 viewtable $formdir/user.def user $1

1984 echo "</div>"

1985 } > $pf

1986

1987 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

1988 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

1989 imf=$tmpd/profimg.$$; touch $imf

1990 if [ -n "$img" ]; then

1991 if true; then

1992 tbl=user_m

1993 enticond="name='$uname'"

1994 imgsrc_cache "$td/main" user_m "$enticond" M

1995 else

1996 { printf '%s' "<IMG src=\"data:${img#file:},"

1997 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

1998 | sed 's/$..$/%\1/g'

1999 echo '">'

2000 }

2001 fi > $imf

2002 fi

2003 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2004 val='$uname';"`

2005 ## REMOVE This comment block until 2019/7/1

2006 ## err "----- `gdate +%FT%T.%3N` ------------C"

2007 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2008 listblog $uname > $bf

2009 ## unset ddd

2010 ## err "----- `gdate +%FT%T.%3N` ------------D"

2011

2012 hometail=$tmpd/tail.$$

2013 mkfifo $hometail

2014

2015 #Calling listgroupbytable, originally here

2016

2017 (

2018 # Display Most Recent Entry

2019 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2020 shortval=${shortval:-val}

2021

2022 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2023 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2024 ctime,

2025 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2026 (SELECT gecos FROM gecoses

2027 WHERE name=(SELECT val FROM blog_s

2028 WHERE key='owner' AND id=b.id)) owner,

2029 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2030 FROM blog b

2031 JOIN

2032 (SELECT distinct blogid, a.id aid, max(val) ctime

2033 FROM article a, article_s s

2034 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2035 GROUP BY blogid ORDER BY val DESC LIMIT 50

2036 ) m

2037 ON b.id=m.blogid;"

2038 # This should be as follows

2039 : <<EOF

2040 WITH arts AS(

2041 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2042 a.blogid, a.id id, s.val ctime

2043 FROM article a NATURAL JOIN article_s s

2044 WHERE s.key = 'ctime' AND a.author='$user'

2045 GROUP by s.id

2046 )

2047 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2048 FROM arts a0

2049 JOIN

2050 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2051 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2052 ORDER BY ctime DESC LIMIT 50;

2053 EOF

2054

2055 cat<<-EOF

2056 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2057 for="mre" title="D">最近の書き込み先</label>

2058 <div class="lcto">

2059 `DT_VIEW=replyblog dumptable html blog`

2060 </div>

2061 EOF

2062 unset DT_SQL

2063 if [ x"$user" = x"$uname" ]; then

2064 # Display NEWS

2065 # 2016-06-26

2066 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2067 acclog blog $blogreadflagrowid

2068 # echo "全部既読にしました" | html p

2069 fi

2070 # 2016-02-19 Counting NEWS without using dumptable.

2071 sql=`listnewblogsql "$user"`

2072 # echo "$sql" > tmp/listnew

2073 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2074 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2075 cont=$((cont-1))

2076 err newcount=$cont

2077 if [ $cont -gt 0 ]; then

2078 #echo "全体の新着記事${cont}傑" | html h2

2079 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2080 echo "<label for=\"new10\" title=\"F\">新着${cont}傑</label><div>"

2081 cat<<-EOF | html form 'action="?home"'

2082 `cgi_checkbox readchk yes 'id="read"'`<label

2083 for="read">新着ふくめて全部読んだことにする</label>

2084 `cgi_submit '確定'`

2085 `cgi_hidden read yes`

2086 EOF

2087 echo "$new10 "

2088 echo "</div>"

2089 else # If news is 0, set log cut off flag

2090 acclog blog $blogcutoffflagrowid # for speed

2091 fi

2092 else # Not My Home ($user != $uname)

2093 : # DT_SQL=

2094 fi

2095 ) > $hometail & # Is background call safe to m4??

2096 #

2097 listgroupbytable $formdir/grp.def $cond |

2098 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2099 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2100 -D_PROFIMG_="spaste(\`$imf')" \

2101 -D_BLOGS_="spaste(\`$bf')" \

2102 -D_SEARCH_="spaste(\`$sf')" \

2103 -D_NBLOG_="$nblog" \

2104 -D_GROUPS_="syscmd(\`cat')" \

2105 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2106 $layout/html.m4.html $layout/home.m4.html

2107

2108 # Record access log

2109 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2110 }

2111 commission() { # $1=grp-rowid $2=user-rowid

2112 contenttype; echo

2113 ## err commission: "$@"

2114 gname=`getgroupbyid $1`

2115 echo "グループ $gname 管理者委任" \

2116 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2117 if [ -n "$2" ]; then

2118 grp_reg_adm "$@"

2119 else

2120 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2121 fi

2122 }

2123 listgroupbytable() {

2124 # $1=deffile $2...=condition

2125 tagline=`grep :tag: $1`; shift

2126 and="${1:+and }" where=${1:+where }

2127 href="<a href=\"$myname?grp+"

2128 echo '<div class="listgroup">'

2129 NGsql="select distinct tag from\

2130 (select gname, max(case key when 'tag' then val end) as tag, \

2131 max(case key when 'ctime' then val end) as ctime\

2132 from grp_s group by gname order by ctime);"

2133 sql="select val from grp_s where key='tag' $and$* group by val;"

2134 ## err ListGRP: query sql="$sql"

2135 for tag in `query "$sql"`

2136 do

2137 ## err ListGrp: tag=$tag

2138 tn=${tagline%%=${tag}*}

2139 tn=${tn##*[ :]}

2140 sql="select rowid||':'||gname as 'グループ名',説明 from

2141 (select (select rowid from grp g where g.gname=grp_s.gname)

2142 as rowid,

2143 gname,

2144 max(case key when 'gecos' then val end) as '説明',

2145 max(case key when 'tag' then val end) as 'tag',

2146 max(case key when 'mtime' then val end) as mtime from grp_s

2147 $where$* group by gname having tag='$tag' order by mtime desc);"

2148 ## err PersonalGroupList= `echo $sql`

2149 echo "<h2>$tn</h2>"

2150 echo '<table class="b listgroup">'

2151 sq -header -html $db "$sql" \

2152 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2153 echo '</table>'

2154 done

2155 echo '</div>'

2156 }

2157 iconhref() (

2158 # $1=icon-file, $2=Href $3=title $4...=anchor

2159 data=`percenthex "$1"`

2160 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2161 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2162 href=$2; title=$3; shift 3

2163 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2164 )

2165 iconhref2() (

2166 # $1=icon-file, $2=Href $3=title $4...=anchor

2167 src=$1

2168 href=$2; title=$3; shift 3

2169 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2170 )

2171 listentry() (

2172 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2173 # Referring variable $iamowner=$grp to attach owner-request links

2174 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2175 cond='' hiddens=''

2176 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2177 if [ -z "$offset" ]; then

2178 offset=`getpar start`; offset=${offset%%[!0-9]*}

2179 offset=$((offset-1))

2180 fi

2181 offset=$((offset + 0)) # change to numeric forcibly

2182 [ $offset -lt 0 ] && offset=0

2183 limit=$listentlimit

2184 dir=`getcachedir "$1"`

2185 if [ x"$1" = x"user" ]; then

2186 hrb="$myname?home"

2187 deficon=person-default.png

2188 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2189 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2190 gcs=gecos

2191 else # if group

2192 hrb="$myname?grp"

2193 deficon=group-default.png

2194 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2195 gcs=name

2196 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2197 if [ -n "$tagline" ]; then

2198 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2199 ## err tagconv=$tagconv

2200 fi

2201 fi

2202 if [ ! -d $dir ]; then

2203 mkdir -p $dir

2204 fi

2205 if [ ! -s $dir/$deficon ]; then

2206 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2207 fi

2208 if [ -n "$2" ]; then

2209 cond="where nick like '%$2%' or b.name like '%$2%'"

2210 kwd=$2

2211 fi

2212

2213 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2214 # grpは呼出し元の動的スコープ変数でよくないな...

2215 ##qgrp=`sqlquote $grp`

2216 getgrp="(select gname from grp where rowid=${rowid:--1})"

2217 sql="select a.rowid, a.$link,

2218 coalesce(b.$gcs, a.$nm) as nick,

2219 quote(a.$nm) as qname,

2220 (SELECT val FROM ${tbl}_s

2221 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2222 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2223 || case when a.$nm in (select gname from grp)

2224 then printf('(%d名)',

2225 (select count(user) from grp_mem where gname=a.$nm))

2226 else ' <'||a.$nm||'>'

2227 end

2228 as name,

2229 b.tag,

2230 case when a.$nm in (select user from grp_adm

2231 where gname=$getgrp) then '管理者'

2232 when '$user' in (select user from grp_adm where gname=a.$nm)

2233 then 'ADMIN'

2234 when '$user' in (select user from grp_mem where gname=a.$nm)

2235 then 'Member'

2236 when '$iamowner' = '' then ''

2237 else ',not='||a.rowid end as ownerlink,

2238 CASE '$entity'

2239 WHEN 'グループ'

2240 THEN coalesce(

2241 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2242 'open')

2243 ||

2244 CASE WHEN '$user'

2245 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2246 THEN ' ismember'

2247 ELSE ''

2248 END

2249 ELSE 'user'

2250 END regmode

2251 from $tbl a left join

2252 (select $nm as name,

2253 max(case key when 'gecos' then val end) as gecos,

2254 max(case key when 'tag' then val end) as tag,

2255 max(case key when 'mtime' then val end) as mtime,

2256 max(case key when 'wtime' then val end) as wtime,

2257 max(case key when 'login' then val end) as login

2258 from ${tbl}_s group by $nm)

2259 b on a.$nm=b.name $cond $3

2260 order by b.wtime desc, b.login desc,

2261 b.mtime desc, b.tag desc, a.rowid asc"

2262 # Give precedence to newer maintained groups (2016-09-24)

2263 # Note that mtime is stored only in grp_s.

2264 ## err LE:sql.1="$sql"

2265 total=`query "with x as ($sql) select count(*) from x;"`

2266 echo "${entity} 一覧" | html h2

2267 echo '<div class="listentry">' # List-entry div

2268 # Show owner/member filter button

2269 [ x"$tbl" = x"grp" ] && cat<<-EOF

2270 `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2271 for="ismembtn">参加中以外隠す</label>

2272 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2273 for="isadmbtn">管理者参加以外隠す</label>

2274 EOF

2275 METHOD=GET

2276 if [ $total -gt $limit ]; then

2277 echo '<div class="right">'

2278 METHOD=GET cgi_form $stage <<EOF

2279 <label>次の語を含む${entity}で検索:

2280 `cgi_text kwd "$kwd"`</label>

2281 $hiddens

2282 EOF

2283 echo '</div>'

2284 fi

2285 hiddens="$hiddens

2286 `cgi_hidden kwd \"$kwd\"`

2287 `cgi_hidden stage \"$stage\"`"

2288 cat<<EOF

2289 <form action="$myname" method="$METHOD">

2290 <p>${total}件中の<input class="hidesub" type="text" name="start"

2291 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2292 <input type="submit" value="確定"></p>

2293 </form>

2294 EOF

2295 if [ $((offset+limit)) -lt $total ]; then

2296 nextbtn=$(

2297 cat<<EOF

2298 <div class="right clear"><form action="$myname" method="$METHOD">

2299 `cgi_submit 次の${limit}件`

2300 $hiddens

2301 `cgi_hidden offset $((offset + limit))`</form></div>

2302 EOF

2303 )

2304 fi

2305 if [ $offset -gt 0 ]; then

2306 prevbtn=$(

2307 cat<<EOF

2308 <form action="$myname" method="$METHOD">

2309 `cgi_submit 前の${limit}件`

2310 $hiddens

2311 `cgi_hidden offset $((offset - limit))`</form>

2312 EOF

2313 )

2314 fi

2315 pnbtn="$nextbtn$prevbtn"

2316 echo $pnbtn

2317

2318 ## err ListEntry: `echo "$sql"\;`

2319 # sq $db here??? 2016-11-28

2320 query "$sql limit $limit ${offset:+offset $offset};" \

2321 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2322 # err name=$name owner=$ownerp lnk=$lnk

2323 # err newlnk=$lnk regmode=$regmode

2324 icondir=$dir/$id

2325 # Pick up only last icon

2326 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2327 <p class=\"tag _$tag\">$tag</p>" \

2328 | _m4 $tagconv

2329 if [ -n "$NOSPEEDUP" ]; then

2330 files=`getvalbyid $tbl profimg $id $icondir`

2331 if [ -n "$files" ]; then

2332 icon=`echo "$files"|tail -1`

2333 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2334 else

2335 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2336 fi

2337 elif [ -n "$icon" -a -s "$icon" ]; then

2338 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2339 else

2340 cond="$nm=$qname"

2341 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2342 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2343 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2344 # err "img=[$img]"

2345 if [ -n "$img" ]; then

2346 echo "<a href=\"$hrb+$lnk\">"

2347 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2348 echo "</a>"

2349 else

2350 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2351 fi

2352 fi

2353 echo "<br>$name${ownerp:+<br>($ownerp)}"

2354 echo "</div>"

2355 done

2356 echo "</div>" # End of List-entry div

2357 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2358 )

2359 listmember() {

2360 listentry user "$@"

2361 }

2362 listgroup() {

2363 listentry group "$@"

2364 }

2365 hexteams() { # $1=gname, $2(optional)=user

2366 cond=${2:+" AND user='$2'"}

2367 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2368 WHERE gname='$1' AND key='team'$cond;"

2369 }

2370 showgroup() { # $1=group-rowid

2371 if [ -z "$1" ]; then

2372 grid=`getpar grid`

2373 grid=${grid%%[!0-9]*}

2374 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2375 else

2376 grid=$1

2377 fi

2378 grp=`getgroupbyid $grid`

2379 qgrp=`sqlquote "$grp"`

2380 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2381 if isgroup "$grp"; then

2382 tf=$tmpd/title.$$

2383 sf=$tmpd/search.$$

2384 bodyclass=`query "SELECT val FROM grp_s

2385 WHERE gname=$qgrp AND key='regmode';"`

2386 if ismember "$user" "$grp"; then

2387 ismember="ismember"

2388 qgrp=`sqlquote "$grp"`

2389 bodyclass="$bodyclass${bodyclass:+ }ismember"

2390 else

2391 ismember="" # bodyclass="group"

2392 fi

2393 bodyclass="$bodyclass grouphome"

2394 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2395 echo "グループ $grp" > $tf

2396

2397 showgroupsub $formdir/grp.def "$grid" | \

2398 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2399 -D_FORM_="syscmd(\`cat')" \

2400 -D_BODYCLASS_="$bodyclass" \

2401 -D_DUMPTABLE_="" \

2402 $htmlheader $sf $layout/form+dump.m4.html

2403 # $htmlheader $layout/form+dump.m4.html

2404 # $htmlheader is defined in grp()

2405 else # if $grp is removed at par2table

2406 listgroup

2407 fi

2408 }

2409 showgroupsub() {

2410 # $1=def-file $2=group-rowid

2411 # Using $ismember

2412 rowid=$2

2413 grp=`getgroupbyid $2`

2414 qgrp=`sqlquote "$grp"`

2415 td=`getcachedir grp/"$2"`

2416 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2417 if [ -z "$rowid" ]; then

2418 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2419 #grp=`sq $db "select gname from grp where rowid=$grp"`

2420 echo "showgroupsub: invalid argument($1 $2)" | html p

2421 return

2422 fi

2423 val=`getvalbyid grp profimg $rowid $tmpd`

2424 enticond="gname=$qgrp"

2425 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2426 if [ -n "$img" ]; then

2427 cat<<-EOF

2428 <p class="groupimg">

2429 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2430 EOF

2431 fi

2432 echo "<div class=\"noprofimg\">"

2433 viewtable $1 grp $rowid

2434 echo "</div>"

2435 if isgrpowner "$user" "$grp"; then

2436 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2437 iamowner=$rowid

2438 colmd=" mode"

2439 fi

2440 if [ -n "$ismember" ]; then

2441 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2442 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2443 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2444 cat<<EOF

2445 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2446 <div class="fold clear">

2447 `cgi_checkbox send yes id="send"`<label

2448 for="send">グループ全員にメッセージ送信</label>

2449 <div>

2450 `cgi_textarea message "" "cols=60"`

2451 `cgi_submit 送信`

2452 `cgi_reset リセット`

2453 </div>

2454 `cgi_hidden grp $rowid`

2455 </div></form>

2456 EOF

2457 fi

2458 # 加入ボタン + 加入者リスト

2459 if [ -n "$ismember" ]; then

2460 ismem='checked' state="(参加中)"

2461 else

2462 nomem='checked' state="(現在非加入)"

2463 fi

2464 # このグループでの加入アドレス

2465 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2466 and key='email';"`

2467 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2468 ## and key='email';"

2469 ##err email=$eml

2470 cat <<EOF

2471 <div class="fold clear">

2472 `cgi_checkbox reg yes id="reg"`<label

2473 for="reg">自身の加入状態を操作する</label>$state

2474 <div>

2475 EOF

2476 cgi_form grp <<EOF

2477 <p>このグループに</p>

2478 <table class="b">

2479 <tr><th>メンバーとして</th><td>

2480 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2481 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2482 <tr><th>参加する場合のメイルアドレス<br>

2483 <small>(メインのアドレスとは違うものにする場合に記入<br>

2484 同じでよい場合は空欄に)</small></th>

2485 <td>`cgi_text email $eml`</td></tr>

2486 </table>

2487 `cgi_hidden grp $rowid`

2488 EOF

2489 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2490 echo "moderated (承認加入の)グループなので実際に参加できるのは

2491 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2492 fi

2493 echo '</div></div>'

2494 echo '<h2>話題一覧</h2>'

2495 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2496 cat<<-EOF

2497 <form class="summary" action="$myname" title="$thelp">

2498 `cgi_hidden owner "$grp"`

2499 `cgi_hidden kwd "@week"`

2500 `cgi_hidden stage searchart`

2501 `cgi_submit "一週間のまとめ"`

2502 </form>

2503 EOF

2504 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2505 colstate="state:稼動状態:frozen=rowclass=凍結"

2506 DT_CHLD=article:blogid \

2507 DT_QOWNER="$qgrp" \

2508 DT_VIEW=replyblog dumptable html blog \

2509 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2510

2511 getgname="(select gname from grp where rowid=$rowid)"

2512 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2513 cm="?commission+$rowid"

2514 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2515 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2516 # team list

2517 hexteams=`hexteams "$grp"`

2518 if [ -n "$hexteams" ]; then

2519 echo "チーム一覧" | html h2

2520 echo '<div class="dumptable"><table class="b">'

2521 sq $db -html -header<<-EOF

2522 SELECT val TEAM,

2523 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2524 MEMBERS

2525 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2526 EOF

2527 echo '</table></div>'

2528 fi

2529 }

2530 grp_getbodyclass() {

2531 # Get css class name for document.

2532 # `moderated' for moderated groups

2533 # `ismember' for groups where user belongs

2534 # $1=GroupName (w/o quote)

2535 # $user=userNameCurrentlyLogin

2536 ## err grp_getbodyclass: 1="$1"

2537 qgrp=`sqlquote "$1"`

2538 query<<-EOF

2539 SELECT coalesce(

2540 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2541 'open')

2542 ||

2543 CASE WHEN '$user'

2544 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2545 THEN ' ismember'

2546 ELSE ''

2547 END;

2548 EOF

2549 }

2550 grpaction() { # $1=group-rowid

2551 err GRP_ACTION:IN

2552 grid=${1:-`getpar grp`}

2553 grp=`getgroupbyid "$grid"`

2554 if [ -z "$grp" ]; then

2555 echo "無効な指定です。" | html p; return

2556 fi

2557 if ! ismember $user "$grp"; then

2558 echo "加入者のみに許可された操作です。" | html p; return

2559 fi

2560 echo "グループ $grp 個別選択操作" \

2561 | _m4 -D_TITLE_="syscmd(\`cat')" \

2562 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2563 $layout/html.m4.html

2564

2565 isowner=""

2566 isgrpowner "$user" "$grp" && isowner="yes"

2567 usel=`getpar usel`

2568 if [ -n "$usel" ]; then

2569 uids=$(echo `echo $usel`|tr ' ' ',')

2570 ## err grpaction-1: grp=$grp, `echo $sql`

2571 text=`getpar text`

2572

2573 rm=`getpar rm` cfm=`getpar confirm`

2574 ## err rm=$rm cfm=$cfm

2575 if [ x"$rm" = x"yes" ]; then

2576 if [ "$isowner" ]; then

2577 if [ x"$rm$cfm" = x"yesyes" ]; then

2578 # Eliminate

2579 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2580 for tbl in grp_mem grp_mem_s grp_mem_m; do

2581 sql="delete from $tbl $cond;"

2582 # echo "sql=$sql"

2583 query "$sql"

2584 err rmGRPuser "$sql"

2585 done

2586 num=`query "select count(*) from user where rowid in ($uids);"`

2587 #err num=$num

2588 if [ 0$num -gt 0 ]; then

2589 sql="select coalesce(b.val,a.name) from user a left join \

2590 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2591 # err `echo "$sql"`

2592 html pre<<EOF

2593 以下の${num}名のグループ $grp 登録を解除しました。

2594 `query "$sql"`

2595 EOF

2596 fi

2597 else

2598 echo "確認のチェックがないのでやめておきます。" | html p

2599 return

2600 fi

2601 else # not Group Owner

2602 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2603 return

2604 fi

2605 cat<<EOF

2606

2607 EOF

2608 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2609 if [ -z "$text" ]; then # if msg is empty

2610 echo "なにかメッセージを..." | html p

2611 return 0

2612 fi

2613 gecos=`gecos $user`

2614 mkfrom=`getpar mkfrom`

2615 if [ x"$mkfrom" = x"yes" ]; then

2616 safegc=`echo "$gecos" | tr -d '<>@'`

2617 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2618 fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2619 mail_from="$safegc <$fromad>"

2620 else

2621 mail_from="$noreply_from"

2622 fi

2623 MAIL_FROM=$mail_from \

2624 smail "`email4groupbyuid "$grp" $usel` $user" \

2625 "$gecos さんからのメッセージ" <<EOF

2626 $url

2627 のグループ「$grp」のメンバーである $gecos さんから、

2628 あなた宛へのメッセージです。

2629 ----------------------------------------------------------

2630 $text

2631 EOF

2632 if [ $? = 0 ]; then

2633 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2634 sql="select coalesce(b.val, a.name) from

2635 (select name from user where rowid in ($uids)) a

2636 left join user_s b on a.name=b.name and b.key='gecos';"

2637 html pre<<EOF

2638 `query "$sql"`

2639 (送信者である $gecos さんも含まれます)

2640 EOF

2641 err SendDone: `echo $sql`

2642 fi

2643 elif [ x"$rm" = x"commission" ]; then

2644 grp_reg_adm $grid $usel

2645 elif [ x"$rm" = x"addteam" ]; then

2646 team=`getpar team|sed "s/'/''/g"` # for single quotation

2647 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2648 if [ x"$team" != x"$newteam" ]; then

2649 echo "チーム名に使えない文字を除去しました" | html p

2650 team=newteam

2651 fi

2652 if [ -z "$team" -o x"$team" = x"なし" ]; then

2653 cat<<-EOF | html p

2654 有効なチーム名を入力してください。

2655 カンマだけ、「なし」という名前は使えません。

2656 EOF

2657 echo "有効なチーム名を入力してください。" | html p

2658 else

2659 grp_add_team $grid "$team" $usel

2660 fi

2661 elif [ x"$rm" = x"rmteam" ]; then

2662 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2663 rmteam=`getpar rmteam|sed "s/'/''/g"`

2664 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2665 gname='$grp' AND user='$user' AND key='team'\

2666 AND val='$rmteam';\"`" ]; then

2667 grp_rm_team $grid "$rmteam" $usel

2668 else

2669 echo "所属していないチームの除去操作はできません。"|html p

2670 fi

2671 else

2672 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2673 fi

2674 fi

2675 fi

2676 # POST count summary

2677 from=`getpar from`; to=`getpar to`

2678 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2679 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2680 fromtonote="<p>POST集計: $from_input - $to_input</p>"

2681 # New entry

2682 sql="WITH mems AS (

2683 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2684 ON gm.user=g.name

2685 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2686 ), target_article AS (

2687 SELECT id FROM article_s

2688 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2689 ), posts AS (

2690 SELECT author, count(author) post

2691 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2692 WHERE blogid IN (SELECT id FROM blog_s

2693 WHERE key='owner'

2694 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2695 AND key='text'

2696 GROUP BY author

2697 ), teams AS (

2698 SELECT user, group_concat(val, ', ') team

2699 FROM grp_mem_m

2700 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2701 AND key='team'

2702 GROUP BY user

2703 ), user_post AS (

2704 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2705 FROM mems m LEFT JOIN posts

2706 ON m.name=posts.author

2707 GROUP by m.rowid

2708 )

2709 SELECT

2710 CASE

2711 WHEN (SELECT user FROM grp_adm

2712 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2713 AND user=up.name) IS NOT NULL

2714 then 'k'

2715 ELSE ''

2716 END || rowid || ',' || gecos NAME,

2717 post POST, team TEAM

2718 FROM user_post up LEFT JOIN teams t

2719 ON up.name=t.user

2720 ORDER BY gecos;"

2721 ## err grpaction: "`echo \"$sql\"`"

2722 tf=$tmpd/title.$$

2723 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2724 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2725 for=\"cmadmin\">グループ管理者委任</label>

2726 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2727 </p></div>"

2728 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2729 for=\"conf\">グループ登録解除</label>

2730 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2731 <p>この操作による通知は本人に行きません。

2732 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2733 </div>"

2734 # Get team list to which current user belongs into $hexteams

2735 allhexteams=$(hexteams "$grp")

2736 if [ -n "$isowner" ]; then

2737 myhexteams="$allhexteams" # admin can remove all teams' attr

2738 else

2739 myhexteams=$(hexteams "$grp" "$user")

2740 fi

2741 if [ -n "$myhexteams" ]; then

2742 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2743 for=\"cmrmteam\">チーム属性除去</label>

2744 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2745 を除去します: `cgi_checkbox teamconfirm yes` 確認

2746 <p>この操作による通知は本人に行きません。

2747 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2748 </div>

2749 "

2750 fi

2751 b1='<label> <input type="checkbox" name="usel" value="'

2752 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2753 #b2='"> <span>' b3='</span></label>'

2754 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2755 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2756 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2757 cgi_form grpaction<<EOF \

2758 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2759 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2760 | _m4 -D_TITLE_="spaste(\`$tf')" \

2761 -D_SUBTITLE_="チェック後操作ボタン" \

2762 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2763 $layout/form+dump.m4.html

2764 <p>下でチェックした人を対象として:</p>

2765 <div class="foldtabs">

2766 `cgi_radio rm addteam 'id="cmteam"'`<label

2767 for="cmteam">同じチーム属性を付与</label>

2768 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2769 `cgi_datalist_h teams $allhexteams`

2770 </div>

2771 ${rmteammsg}

2772 `cgi_radio rm send id="sendmsg"`<label

2773 for="sendmsg">メッセージ送信</label>

2774 <div>

2775 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2776 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2777 `cgi_textarea text "" cols=40`

2778 </div>

2779 ${isowner:+$cmmsg$excmsg}

2780 `cgi_radio rm close id="x"`<label for="x">×</label>

2781 </div>

2782 <h4>$grp 参加者一覧</h4>$fromtonote

2783 <table class="td2r">

2784 `sq $db -header -html "$sql"`

2785 </table>

2786 `cgi_hidden grp $grid`

2787 EOF

2788 }

2789 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2790 # Create TEMPORARY VIEW

2791 query<<EOF

2792 CREATE TEMPORARY VIEW writeusers AS

2793 SELECT DISTINCT author FROM article

2794 WHERE id in (

2795 select id from article where blogid=(select id from blog where rowid=$1)

2796 );

2797 CREATE TEMPORARY VIEW movablegroups AS

2798 SELECT g.rowid growid , g.gname

2799 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2800 ON grp.gname=gm.gname -- そのユーザが属している

2801 AND user='$user') g -- グループに絞る

2802 WHERE (SELECT author FROM writeusers

2803 EXCEPT

2804 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2805 IS NULL;

2806 $2

2807 EOF

2808 }

2809 sql4readableblogs() {

2810 # Create view of blogs that can be readable to $user

2811 # Blog is readable when:

2812 # 1: blog owner is an user

2813 # 2: else, 2.1: owner-group where the $user belongs

2814 # 2.2: else, owner-group is not moderated

2815 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2816 cat<<EOF ## | tee tmp/sql.out

2817 CREATE TEMPORARY VIEW readableblogs AS

2818 SELECT blog.rowid rid, id, author

2819 FROM blog

2820 NATURAL JOIN

2821 (SELECT id,

2822 max(CASE key WHEN 'owner' THEN val END) owner,

2823 max(CASE key WHEN 'mode' THEN val END) mode

2824 FROM blog_s GROUP by id) bs

2825 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2826 THEN 1 -- blog owner is an user, READABLE

2827 WHEN (SELECT val FROM grp_s

2828 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2829 AND

2830 (SELECT user FROM grp_mem

2831 WHERE gname=bs.owner AND user='$user') IS NULL

2832 THEN 0

2833 WHEN mode IN ('quiz', 'enquete')

2834 THEN 0 -- "quiz" mode blog cannot be searched

2835 ELSE 1

2836 END;

2837 EOF

2838 }

2839 editheading() { # $1=rowid-of-heading

2840 rowid=${1%%[!A-Z0-9a-z_]*}

2841 if [ -z "$rowid" ]; then

2842 echo "話題番号が未指定です。" | html p

2843 return

2844 fi

2845 owner=`getvalbyid blog owner $rowid`

2846 title=`getvalbyid blog title $rowid`

2847 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2848 | _m4 -D_TITLE_="修正" \

2849 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2850 -D_BLOGS_="" -D_DUMPTABLE_="" \

2851 -D_FORM_="syscmd(\`cat')" \

2852 $layout/html.m4.html $layout/form+dump.m4.html

2853 # Move to group

2854 if isuser "$owner"; then

2855 crview4article $rowid

2856 n=`query "SELECT count(*) FROM writeusers;"`

2857 ## err N=$n

2858 if [ $((n)) -gt 0 ]; then

2859 ## err ROWID=$rowid

2860 sql="SELECT growid || ':' || gname FROM movablegroups;"

2861 cat<<-EOF

2862 <div class="fold">

2863 `cgi_checkbox mv send id="mv"`<label

2864 for="mv">この話題をグループ所有に移動する</label>

2865 <div>

2866 <form action="?mvart" method="POST" enctype="multipart/form-data">

2867 移動先グループ:

2868 <select name="mv2grp">

2869 EOF

2870 query ".mode html"

2871 query<<-EOF |

2872 $sql

2873 .mode list

2874 EOF

2875 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2876 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2877 cat<<-EOF

2878 </select>

2879 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2880 そのグループに加入しているものに限られます)</p>

2881 <p>`cgi_checkbox cfm yes`<label>確認

2882 (この操作は元に戻すことができません)</label></p>

2883 `cgi_hidden blogrowid $rowid`

2884 `cgi_submit 移動`

2885 `cgi_reset Reset`

2886 </form>

2887 </div>

2888 </div>

2889 EOF

2890 fi

2891 # end of isuser "$owner"

2892 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2893 [ -n "$hexteams" ];}; then

2894 none="`echo なし|hexize`"

2895 cat<<-EOF

2896 <div class="fold">

2897 `cgi_checkbox mv2team send id="mv2team"`<label

2898 for="mv2team">この話題を以下のチームのものにする</label>

2899 <div><p>現在の所属チーム設定:

2900 `query "SELECT

2901 coalesce((SELECT val FROM blog_s

2902 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2903 AND key='team'),

2904 ':なし');"`</p>

2905 <form action="?mvart" method="POST" enctype="multipart/form-data">

2906 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2907 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2908 `cgi_hidden blogrowid $rowid`<br>

2909 `cgi_submit 移動`

2910 `cgi_reset Reset`

2911 </form></div></div>

2912 EOF

2913 fi

2914 }

2915 mvart() { # move diary to some group or team

2916 # or move blog of group to team which belong to the group

2917 blogrowid=`getpar blogrowid`

2918 cfm=`getpar cfm`

2919 ##### echo move blog:$blogrowid to $mv2grp | html p

2920 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2921 . ./s4-blog.sh

2922 if [ -z "$blogrowid" ]; then

2923 echo "無効な指定です(mvart)。" | html p

2924 return

2925 elif [ x"$cfm" != x"yes" ]; then

2926 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2927 elif { mv2grp=`getpar mv2grp`

2928 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2929 [ -n "$mv2grp" ]; }; then

2930 crview4article $blogrowid

2931 ########## TRANSACTION BEGIN

2932 query "BEGIN;"

2933 n=`query "SELECT count(*) FROM writeusers;"`

2934 ## err Nwriteuser=$n

2935 if [ $((n)) -gt 0 ]; then

2936 query<<-EOF

2937 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2938 WHERE key='owner'

2939 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2940 AND $mv2grp IN (SELECT growid FROM movablegroups);

2941 EOF

2942 fi

2943 query "END;"

2944 ########## TRANSACTION END

2945 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2946 [ -n "$mv2team" ];}; then

2947 # blog owner can move it to ANY team

2948 case "$mv2team" in

2949 'なし')

2950 cat<<-EOF

2951 DELETE FROM blog_s

2952 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2953 AND key='team';

2954 EOF

2955 ;;

2956 "") ;;

2957 *)cat<<-EOF

2958 BEGIN;

2959 REPLACE INTO blog_s(id, key, val)

2960 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2961 'team', '$mv2team');

2962 REPLACE INTO blog_s(id, key, val)

2963 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2964 'notify', 'all'); -- Change notify to all

2965 END;

2966 EOF

2967 esac | query

2968 fi

2969 blog_reply $blogrowid

2970 echo yes | html p

2971 }

2972 editart() { # $1=article-rowid $2=blogrowid

2973 rowid=${1%%[!A-Z0-9a-z_]*}

2974 blogrowid=${2%%[!A-Z0-9a-z_]*}

2975 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2976 echo "表示する記事番号が未指定です。" | html p

2977 return

2978 fi

2979 owner=`getvalbyid blog owner $blogrowid`

2980 title=`getvalbyid blog title $blogrowid`

2981 author=`getvalbyid article author $rowid`

2982 ## err EDITart: owner=$owner, author=$author

2983 if isgrpowner "$user" "$owner"; then

2984 : EDIT OK

2985 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2986 echo "本人か所有者しか編集できません." | html p

2987 return

2988 fi

2989 aid=`query "select id from article where rowid=$rowid;"`

2990 tmpout=$tmpd/editart.$$.out

2991 GF_ACTION="?replyblog+$blogrowid#$aid" \

2992 edittable $formdir/article.def article $rowid \

2993 > $tmpout

2994 rm -f /tmp/editart.out

2995 # Cannot use pipelining to m4 with genform() because of stdin stack

2996 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2997 -D_FORM_="syscmd(cat $tmpout)" \

2998 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2999 -D_BLOGS_= -D_DUMPTABLE_= \

3000 $layout/html.m4.html $layout/form+dump.m4.html

3001 }

3002 send2mem() {

3003 rowid=`getpar grp`

3004 rowid=${rowid%%[!0-9]*} # Cleaning

3005 if [ -z "$rowid" ]; then

3006 echo "グループが未指定です。" | html p

3007 return

3008 fi

3009 message=`getpar message`

3010 if [ -z "$message" ]; then

3011 echo "文章を入れてください。" | html p

3012 return

3013 fi

3014 grp=`getgroupbyid $rowid`

3015 members=`collectemail $grp`

3016 # smail rcpt subj (file)

3017 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$admin>" \

3018 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

3019 $urlbase?grp+$rowid

3020 グループ $grp に所属する

3021 `gecos $user` さんよりメッセージ:

3022

3023 $message

3024 EOF

3025 cat<<EOF

3026 <p>以下のユーザに送信しました。</p>

3027 <pre>

3028 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3029 </pre>

3030 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3031 EOF

3032 }

3033 joingrpadmit() {

3034 # $1=yes/no $2=session-key

3035 if [ -z "$2" ]; then

3036 echo "bye bye" | html p; return

3037 fi

3038 t_usr=`session=$2 getpar user`

3039 t_grp=`session=$2 getpar group`

3040 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3041 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3042 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3043 echo "無効な加入依頼です。" | html p

3044 echo "有効期限が切れたか、

3045 他の管理者がいる場合は処理済みの可能性があります。" | html p

3046 return

3047 fi

3048 if ! isgrpowner "$user" "$t_grp"; then

3049 echo "グループ管理者のみの機能です。" | html p; return

3050 fi

3051 case $1 in

3052 yes) joingrp "$t_grp" "$t_usr" yes ;;

3053 no) joingrp "$t_grp" "$t_usr" no ;;

3054 *)

3055 echo "無効な指定です($1)。" | html p

3056 return ;;

3057 esac

3058 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3059 rcpts="`getgroupadminmails $t_grp` $user"

3060 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3061 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3062 に

3063 $t_usr

3064 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3065 しました。"

3066 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3067 query "delete from session where id='$2';"

3068 echo "$body" | html p

3069 }

3070

3071 joingrprequest() {

3072 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3073 jss="joingrp-`date +%s`-`genrandom 12`"

3074 addsession $jss +${memoplimitdays}days

3075 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3076 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3077 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3078 $url

3079 $user さんから

3080 グループ $1

3081 に加入依頼がありました。

3082

3083 承認する:

3084 $urlbase?joingrpadmit+yes+$jss

3085

3086 白紙に戻す:

3087 $urlbase?joingrpadmit+no+$jss

3088 EOF

3089 echo "管理者に加入依頼を出しました。

3090 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3091 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3092 }

3093 joingrp() {

3094 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3095 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3096 if isgrpowner "$user" "$1"; then

3097 isowner="yes"

3098 elif [ -n "$5" ]; then

3099 isowner="yes"

3100 else

3101 isowner=""

3102 fi

3103 ## err jg:isgrpowner: isowner="$isowner"

3104 if [ -n "$isowner" ]; then

3105 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3106 elif [ x"$2" != x"$user" ]; then # if user is not login user

3107 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3108 return

3109 elif [ x"$3" = x"no" ]; then

3110 : # Do not pursue those who leave

3111 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3112 : # Member can change own email address for the joining moderated group

3113 else # adding user is $user itself

3114 case `getgroupattr $1 regmode` in

3115 moderated)

3116 joingrprequest "$@" # Request only

3117 return

3118 ;;

3119 *)

3120 ;;

3121 esac

3122 fi

3123 qgname=`sqlquote "$1"`

3124 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3125 cond="where gname=$qgname and user='$2'"

3126 if [ x"$3" = x"yes" ]; then

3127 query "replace into grp_mem values($qgname, '$2');"

3128 # Notify joingrp to admin

3129 action="に加入しました。"

3130 if [ -n "$4" ]; then

3131 if msg=`emaildomaincheck "$4"`; then

3132 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3133 'string', '$4', NULL);"

3134 else

3135 echo $msg

3136 fi

3137 else

3138 query "delete from grp_mem_s $cond and key='email';"

3139 fi

3140 if [ -n "$5" ]; then # as ADMIN

3141 # Coming here means newly created group

3142 sql="select case\

3143 when (select count(*) from grp_mem where gname=$qgname)=1\

3144 then (select user from grp_mem\

3145 where gname=$qgname and user='$user')\

3146 else '' end; "

3147 err NewGrpChk: $sql

3148 if [ -n "`query \"$sql\"`" ]; then

3149 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3150 query "replace into grp_adm values($qgname, '$user');"

3151 fi

3152 fi

3153 else

3154 query "begin;

3155 delete from grp_mem $cond;

3156 delete from grp_mem_s $cond;

3157 delete from grp_mem_m $cond;

3158 delete from grp_adm $cond;

3159 delete from grp_adm_s $cond;

3160 delete from grp_adm_m $cond;

3161 end;"

3162 action="から脱退しました。"

3163 fi

3164 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3165 $url?grp+$grid

3166 $user (`gecos $user`)さんが

3167 グループ $1

3168 $action

3169 EOF

3170 }

3171 grp_add_team() (

3172 # $1=grp-rowid $2=team $3...=user-rowid(s)

3173 grp=`getgroupbyid $1`

3174 team=$2; shift; shift

3175 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3176 { echo "BEGIN;"

3177 for user; do

3178 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3179 '$grp',\

3180 (SELECT name FROM user WHERE rowid=$user),\

3181 'team', 'string', '$team');"

3182 done

3183 echo "END;"

3184 } | query

3185 )

3186 grp_rm_team() (

3187 # $1=grp-rowid $2=team $3...=user-rowid(s)

3188 grid=$1

3189 qgrp=$(sqlquote "`getgroupbyid $grid`")

3190 team=$2; shift; shift

3191 [ -z "$grid" -o -z "$team" ] && return

3192 { echo "BEGIN;"

3193 for user; do

3194 echo "DELETE FROM grp_mem_m\

3195 WHERE gname=$qgrp \

3196 AND user=(SELECT name FROM user WHERE rowid=$user)\

3197 AND key='team' AND val='$team';"

3198 done

3199 cat<<-EOF

3200 DELETE FROM blog_s

3201 WHERE rowid=(

3202 SELECT rowid

3203 FROM blog_s a

3204 WHERE key='team'

3205 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3206 AND NOT EXISTS (SELECT * FROM grp_mem_m

3207 WHERE key='team' AND val=a.val -- a.val=team

3208 AND gname = (SELECT val FROM blog_s b

3209 WHERE a.id=b.id AND key='owner')

3210 ));

3211 EOF

3212

3213 echo "END;"

3214 } | query

3215 )

3216 grp_reg_adm() {

3217 # $1=grp-rowid $2...=user-rowid

3218 grid=$1

3219 grp=`getgroupbyid "$1"`

3220 if [ -z "$grp" ]; then

3221 echo "無効なグループIDです" | html p; return

3222 fi

3223 if ! isgrpowner "$user" "$grp"; then

3224 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3225 fi

3226 shift

3227 for urid; do

3228 newadm=`query "select name from user where rowid=$urid;"`

3229 if [ -z "$newadm" ]; then

3230 echo "指定ユーザIDがおかしいようです。" | html p; return

3231 fi

3232 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3233 err ismember $newadm $grp

3234 if ismember $newadm "$grp"; then

3235 # OK, go ahead

3236 getgname="(select gname from grp where rowid=$grid)"

3237 query "replace into grp_adm values($getgname, '$newadm');"

3238 # confirm insertion

3239 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3240 if [ -n "`query \"$sql;\"`" ]; then

3241 echo "追加完了: $newadm" | html p

3242 else

3243 echo "追加失敗($1 $urid)" | html p

3244 fi

3245 fi

3246 showgroup $grid

3247 done

3248 }

3249 dt_rowhack() {

3250 # From: <TR>

3251 # ....

3252 # <TD>rowclass=foo</TD>

3253 # </TR>

3254 # To: <TR class="foo">....<TD>foo</TD></TR>

3255 sed -e '

3256 /^<TR>/ {

3257 :loop

3258 s/\n//

3259 N

3260 /<\/TR>/ {

3261 s/\n//

3262 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3263 n

3264 }

3265 $q

3266 b loop

3267 }'

3268 }

3269 dumptable() {

3270 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3271 # textのフィールドだけ全てダンプにしたほうがいいか

3272 # $DT_VIEW sets link

3273 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3274 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3275 VIEW=${DT_VIEW-replyblog}

3276 if [ -n "$VIEW" ]; then

3277 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3278 fi

3279 sqlfile=$tmpd/dump.sql

3280 : > $sqlfile # ensure to be empty

3281 printf '.mode html\n.header 1\n' > $sqlfile

3282 # $DT_CHLD=ChildTable:BindColumn

3283 if [ -n "$DT_CHLD" ]; then

3284 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3285 cat<<-EOF >> $sqlfile

3286 -- presql

3287 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3288 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3289 EOF

3290 # Speed up counting of new articles

3291 cat<<-EOF >> $sqlfile

3292 -- presql2

3293 DROP TABLE IF EXISTS _counts;

3294 CREATE TEMPORARY TABLE _counts AS

3295 SELECT $_i, count($_i) cnt

3296 FROM $_t GROUP BY $_i;

3297 /* Prepare NEW count table */

3298 CREATE TEMPORARY TABLE _target AS

3299 SELECT b.rowid trowid, b.id

3300 FROM "$2" b JOIN "$2_s" s

3301 ON b.id=s.id AND s.key='owner'

3302 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3303

3304 DROP TABLE IF EXISTS _children;

3305 CREATE TEMPORARY TABLE _children AS

3306 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3307 FROM (SELECT t.trowid, t.id $_i, a.id

3308 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3309 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3310

3311 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3312 DROP TABLE IF EXISTS _news;

3313 DROP VIEW IF EXISTS _news;

3314

3315 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3316 -- INSERT INTO _news

3317 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3318 CREATE TEMPORARY VIEW _news AS

3319 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3320 FROM (SELECT DISTINCT id FROM _target)

3321 a LEFT JOIN

3322 (SELECT $_i, count(ctime) newcnt

3323 FROM _children x

3324 WHERE ctime > coalesce((SELECT time from myacclog

3325 WHERE tblrowid=x.trowid),

3326 '1970-01-01')

3327 GROUP BY $_i) b

3328 ON a.id=b.$_i;

3329 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3330 EOF

3331 # REMOVE next line until 2019/5/1

3332 cntall="(select count($_i) from $_t where $_i=a.id)"

3333 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3334 # REMOVE next assignment until 2019/5/1

3335 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3336 and id in (select id from $_t where $_i=a.id) \

3337 and val > coalesce((select time from myacclog where \

3338 tblrowid=a.rowid),\

3339 '1970-01-01'))"

3340 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3341 cnt="$cntnew as '新着', $cntall as '総数',"

3342 dt_class=" td2r td3r dumpblogs"

3343 fi

3344 # Construct join expression

3345 eav="" scols=""

3346 pk=`gettblpkey $2`

3347 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3348 substr=${substr:-%s}

3349 for col in ${3:-`gettbl_s_cols $2`}; do

3350 valvar=val

3351 case $col in

3352 gecos) scols="$scols${scols:+, }${col#}"

3353 continue ;; # built-in column name

3354 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3355 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3356 case "$as" in

3357 *:*=*) cnd=${as#*:}

3358 h=${cnd%%=*} v=${cnd#*=}

3359 h=`sqlquotestr "$h"`

3360 v=`sqlquotestr "$v"`

3361 valvar="CASE val WHEN $h THEN $v END"

3362 as=${as%%:*} ;;

3363 esac

3364 ;;

3365 *) as=${col} ;;

3366 esac

3367 ss=`printf "$substr" "$valvar"`

3368 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3369 scols="$scols${scols:+, }b.$as"

3370 done

3371 #case author when '$user' then a.rowid else '---' end as ID,

3372 if [ -n "$DT_SQL" ]; then

3373 echo "$DT_SQL"

3374 else

3375 cat<<-EOF

3376 SELECT a.rowid as LINK, $cnt $scols

3377 FROM $2 a LEFT JOIN

3378 (SELECT $pk,$eav,

3379 max(CASE key

3380 WHEN 'owner'

3381 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3382 as gecos

3383 FROM ${2}_s c GROUP BY $pk)

3384 b ON a.$pk=b.$pk $4;

3385 EOF

3386 fi >> $sqlfile

3387 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3388 sqlog<<-EOF

3389 *** SQL-file: $sqlfile ***

3390 `cat $sqlfile`

3391 EOF

3392 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3393 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3394 cat $sqlfile >> tmp/sql

3395 # query ".read $sqlfile" > $tmpd/foo

3396 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3397 cp $tmpd/foo tmp/

3398 err "----- `gdate +%FT%T.%3N` ------------555555"

3399 ## $ddd LINE exists at the end of this function

3400 fi

3401 printf '.mode list\n.header 0\n' >> $sqlfile

3402 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3403 <div>

3404 <div class="dumptable">

3405 <table class="b$dt_class">

3406 `query ".read $sqlfile"`

3407 </table>

3408 </div>

3409 </div>

3410 EOF

3411 ### `query ".read $sqlfile"`

3412 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3413

3414 ## REMOVE THIS!

3415 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3416 }

3417

3418 par2table() (

3419 # copy current parameters of par into destination table

3420 # $1=definition-file

3421 # Using $user and $session

3422 # Return value:

3423 # 0: Stored successfully

3424 # 1: Insufficient fillings

3425 # 2: No permission to modify the record

3426 # 3: Invalid rowid

3427 # 4: SUCCESS to delete

3428 # 5: Stop deletion for lack of confirm check

3429 # 6: Password length too short

3430 # 7: Password mismatch

3431 # 8: Old password incorrect

3432 rowid=`getpar rowid`

3433 if [ ! -e $1 ]; then

3434 echo "テーブル定義ファイルが見付かりません" | html p

3435 exit 1

3436 fi

3437 tbl=${1%.def}

3438 tbl=${tbl##*/}

3439 if [ -n "$rowid" ]; then # Modify existing entry

3440 if [ x"$tbl" = x"user" ]; then

3441 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3442 elif [ x"$tbl" = x"grp" ]; then

3443 sql="select gname from $tbl where rowid=$rowid;"

3444 ##err p2t:grp:q $sql

3445 isgrpowner "$user" "`query $sql`" && rowowner=$user

3446 elif [ x"$tbl" = x"blog" ]; then

3447 # Check if owner in blog_s

3448 blogowner=`getvalbyid blog owner "$rowid"`

3449 if isgrpowner "$user" "$blogowner"; then

3450 rowowner=$user

3451 else

3452 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3453 fi

3454 else

3455 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3456 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3457 fi

3458 ### err rowowner=$rowowner

3459 if [ x"$user" != x"$rowowner" ]; then

3460 echo "他人のレコードはいじれないの" | html p

3461 return 2

3462 elif [ -z "$rowowner" ]; then

3463 echo "指定したレコードはないみたい" | html p

3464 return 3

3465 fi

3466 rm=`getpar rm` cfm=`getpar confirm`

3467 # Editing existent entry

3468 if [ x"$rm" = x"yes" ]; then

3469 if [ x"$rm$cfm" = x"yesyes" ]; then

3470 query "delete from $tbl where rowid=$rowid;"

3471 return 4

3472 else

3473 echo "消去確認のチェックがないので消さなかったの..." | html p

3474 return 5

3475 fi

3476 fi

3477 fi

3478

3479 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3480 if [ -n "$rowid" ]; then

3481 # Update of existing record

3482 for col in `gettblcols $tbl`; do

3483 val=`getparquote $col`

3484 [ -z "$val" ] && continue

3485 ## err query "update $tbl set $col=$val where rowid=$rowid"

3486 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3487 sql="update $tbl set $col=$val where rowid=$rowid;"

3488 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3489 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3490 ## User name cannot be changed with interface provided with this

3491 ## script. But we offer the trigger to change owner user

3492 ## of blog_s table.

3493 #err "select quote($col) from $tbl where rowid=$rowid;"

3494 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3495 cat<<-EOF | query

3496 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3497 -- should use EXCLUSIVE transaction outside of this.

3498 SAVEPOINT par2table;

3499 $sql

3500 update blog_s set val=$val

3501 where key='owner' and val=$old;

3502 RELEASE SAVEPOINT par2table;

3503 EOF

3504 ## XX: DIRTY Hack Ends here

3505 ## We should keep blog's owner as a single column which has

3506 ## foreign key constraint with primary key of grp/user.

3507 else

3508 query "$sql"

3509 fi

3510 done

3511 # Then, set up $pval for further insertion of tbl_s and tbl_m

3512 for col in `gettblpkey $tbl`; do

3513 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3514 pval="$pval${pval:+, }\"$val\""

3515 done

3516 else

3517 # New entry

3518 # Generate values() for primary keys

3519 for col in `gettblpkey $tbl`; do

3520 # Genuine primary keys for _m and _s

3521 val=`getvalquote $tbl $col`

3522 [ -z "$val" ] && continue

3523 pval="$pval${pval:+, }$val"

3524 done

3525 ##err pval=$pval

3526 for col in `gettblfkey $tbl`; do

3527 # args for values() to insertion into master table

3528 val=`getvalquote $tbl $col`

3529 [ -z "$val" ] && continue

3530 formaster=$formaster"${formaster:+, }$val"

3531 done

3532 formaster="$pval${formaster:+, }$formaster"

3533 ## err formaster=$formaster

3534 if [ -z "$formaster" ]; then

3535 echo "項目を全て埋めてください" | html pre

3536 return 1

3537 fi

3538 ## err "replace into $tbl values($formaster);"

3539 query "replace into $tbl values($formaster);"

3540 ## Insertion to master table, done

3541 fi

3542

3543 for kt in s m; do

3544 tb2=${tbl}_$kt

3545 for col in `gettbl_${kt}_cols $tbl`; do

3546 ptype=`getpartype $col "limit 1"`

3547

3548 # First, check update of existing entries in _m

3549 if [ $kt = m ]; then

3550 # sessID|address.1.22|string|Somewhere-x.y.z

3551 sql=""

3552 ##err dots from query "select var from par where var like '$col.%';"

3553 for v in `query "select var from par where var like '$col.%';"`; do

3554 # v=address.1.22

3555 st_rowid=${v##*.}

3556 origcol=${v%%.*} # original column derived from

3557 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3558 ##case `getpartype $v` in

3559 ## err CASE `gettbl_coltype $tbl/$origcol` in

3560 ## err edit flag = `getpar action.$v`

3561 case `getpar action.$v` in

3562 rm)

3563 if [ x`getpar confirm.$v` = x"yes" ]; then

3564 newsql="delete from $tb2"

3565 else

3566 echo "削除確認未チェック" | html p

3567 fi ;;

3568 edit)

3569 case `gettbl_coltype $tbl/$origcol` in

3570 image|document|binary)

3571 file=$tmpd/`getparfilename $v`

3572 ## err type=file=$file

3573 [ -z "$file" ] && continue

3574 bn=`sqlquotestr "${file##*/}"`

3575 bin="X'"$(hexize "$file")"'"

3576 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3577 type=\"file:$ct\"

3578 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3579 cachedir=`getcachedir "$tbl/$rowid"`

3580 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3581 rm -rf $cachedir

3582 ;;

3583 *)

3584 newsql="update $tb2 set val=(select val from par where var \

3585 like '$col.%.$st_rowid')"

3586 ;;

3587 esac

3588 ;;

3589 *) # maybe "keep", do not modify value

3590 continue

3591 ;;

3592 esac

3593 # err newsql=$newsql

3594 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3595 done

3596

3597 if [ x"$bin" = x"NULL" ]; then

3598 ## err repl:normal sql=`echo $sql`

3599 query "$sql

3600 delete from $tb2 where type='string' and val='';"

3601 ## err repl:normal done

3602 else

3603 sqlfile="$tmpd/sqlf.$$"

3604 echo "$sql" > $sqlfile

3605 ## err repl:sqlfile=`ls -lF $sqlfile`

3606 query ".read $sqlfile"

3607 ## err repl:done

3608 fi

3609 # Rest of kt==m: set multiple mode

3610 nr=`getparcount $col`

3611 else

3612 nr=1 # for kt==s, number of records is 1

3613 fi

3614

3615 i=0

3616 while [ $i -lt $nr ]; do

3617 limit="limit 1 offset $i"

3618 i=$((i+1)) # increase beforehand against continue

3619 val=`getvalquote $tbl $col "$limit"`

3620 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3621 ## err $col=$val

3622 bin=NULL

3623 ## err partype$col=`getpartype $col "$limit"`

3624 case $ptype in

3625 file) file=$tmpd/`getparfilename $col "$limit"`

3626 ## err parfile-$col=$file

3627 [ -z "$file" ] && continue

3628 bin="X'"$(hexize "$file")"'"

3629 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3630 type=\"file:$ct\" ;;

3631 "*"*) continue ;; # foreign table

3632 *) type=\"string\" ;;

3633 esac

3634 case `gettbl_coltype $tbl/$col` in

3635 password) # special care for password

3636 # name={password,pswd1,pswd2}

3637 p1=`getpar pswd1 "$limit"`

3638 if [ -z "$p1" ]; then

3639 continue # SKIP password setting, if p1 is empty

3640 else

3641 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3642 ## err pswd=$pswd

3643 if pwcheck "$pswd"; then

3644 if [ x"$p1" = x"$p2" ]; then

3645 case "$p1" in

3646 ??????????*) ;;

3647 *) echo "パスワードは10字以上にしてください。" | html p

3648 return 6;;

3649 esac

3650 val="\"`echo $p1|mypwhash`\""

3651 else

3652 echo "2つの新パスワード不一致" | html p

3653 return 7

3654 fi

3655 else

3656 echo "旧パスワード違います" | html p

3657 return 8

3658 fi

3659 fi

3660 ;;

3661 esac

3662 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3663 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3664 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3665 if [ x"$bin" = x"NULL" ]; then

3666 ## err Normal-query: `echo $sql`

3667 query "$sql"

3668 else

3669 sqlfile="$tmpd/query.$$"

3670 echo "$sql" > $sqlfile

3671 ## err sqlfile=`ls -lF $sqlfile`

3672 query ".read $sqlfile"

3673 fi

3674 ## err p2t done

3675 done

3676 done

3677 done

3678 return 0

3679 ##err donee

3680 )

3681 genform() {

3682 # $1 = form definition file

3683 # $2, $3 (optional)= table name and ROWID

3684 # If $GF_VIEWONLY set and nonNull, output values without form

3685 # If $GF_ARGS set, use it as content-strings in the form

3686 # If $GF_OWNER set, use it as value of name="owner"

3687 # If $GF_STAGE set, use it as value of name="stage"

3688 forms="" hiddens="" rowid=$3

3689 if [ ! -e "$1" ]; then

3690 echo "そのようなデータベースはないようです($2)。" | html p

3691 return

3692 elif [ -n "$2" ]; then

3693 rec=`query "select * from $2 where rowid='$rowid';"`

3694 if [ -z "$rec" ]; then

3695 pk=`gettblpkey $2`

3696 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3697 rec=`query "select rowid from $2 where $pk='$rowid';"`

3698 rowid=$rec

3699 rec=$3

3700 fi

3701 if [ -z "$rec" ]; then

3702 echo "そんなレコードはないみたいね..." | html p

3703 return

3704 fi

3705 fi

3706 if [ -z "$GF_VIEWONLY" ]; then

3707 rm='<input id="rm" name="rm" type="checkbox"

3708 value="yes"><label for="rm">このエントリの削除</label>

3709 <span>ほんとうに消しますよ(確認)!

3710 <input name="confirm" type=checkbox value="yes">はい</span>'

3711 fi

3712 # Image Cache dir

3713 ## err genform: getcache=$2/$rowid

3714 td=`getcachedir "$2/$rowid"`

3715 while IFS=: read prompt name keytype type args; do

3716 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3717 sp="${args:+ }"

3718 form="" val=""

3719 if [ -n "$rowid" ]; then

3720 # err genform2a: Seeking for "$2.$name, type=$type"

3721 rawval=`getvalbyid $2 $name $rowid $td`

3722 val=`echo "$rawval"|htmlescape`

3723 ## err genform3a: getvalbyid $2 $name $rowid $td

3724 ## err genform3b: val="[$val]" type="$type"

3725 fi

3726 if [ -n "$GF_VIEWONLY" ]; then

3727 is_hidden "$2" "$name" && continue

3728 fi

3729 case "$type" in

3730 text*)

3731 cgiform=cgi_multi_$type

3732 if [ -s $td/$name.count -a -n "$val" ]; then

3733 form=`$cgiform $name $td`

3734 val=$(echo "$val"|

3735 while read fn; do

3736 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3737 </td></tr>$nl"

3738 done)

3739 val="<table>$nl$val$nl</table>"

3740 else

3741 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3742 form=`cgi_$type $name "$rawval" "$args"`

3743 fi

3744 ;;

3745 [Rr][Aa][Dd][Ii][Oo])

3746 fh="<label><input type=\"radio\" name=\"$name\""

3747 form="`echo $args|sed -e \

3748 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3749 ;;

3750 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3751 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3752 ;;

3753 [Ss][Ee][Ll][Ee][Cc][Tt])

3754 fh="<select name=\"$name\">$nl"

3755 form=$(for l in $args; do

3756 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3757 done)

3758 if [ -n "$val" ]; then

3759 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3760 fi

3761 form="$fh$form</select>"

3762 ;;

3763 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3764 if [ -s $td/$name.count ]; then

3765 form=`cgi_multi_file $name $td "$args"`

3766 if [ -n "$val" ]; then

3767 hrfb="$myname?showattc+$2_m"

3768 val=$(echo "$rawval" \

3769 | while read fn; do

3770 data=`percenthex "$td/$fn"`

3771 #ct=`cat $td/$fn.content-type`

3772 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3773 ri=`cat "$td/$fn.rowid"`

3774 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3775 #imgsrc="<img src=\"data:$ct,$data\">"

3776 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3777 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3778 done)

3779 fi

3780 else

3781 form="<input type=\"file\" name=\"$name\" $args>"

3782 if [ -n "$val" ]; then

3783 imgs=$(echo "$rawval"\

3784 |while read fn;do

3785 data=`percenthex "$td/$fn"`

3786 echo "<img src=\"data:image/png,$data\">$fn<br>"

3787 done)

3788 form=$form"<br>$imgs"

3789 val=$imgs # 2015-06-15

3790 else

3791 form="<input type=\"file\" name=\"$name\" $args>"

3792 fi

3793 fi

3794 ;;

3795 [Hh][Ii][Dd][Dd][Ee][Nn])

3796 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3797 args="value=\"$GF_STAGE\""

3798 fi

3799 form="<input type=\"hidden\" name=\"$name\" $args>"

3800 prompt='' # Remove prompt

3801 ;;

3802 [Aa][Uu][Tt][Hh][Oo][Rr])

3803 [ -n "$GF_VIEWONLY" ] && continue

3804 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3805 prompt="" ;;

3806 [Oo][Ww][Nn][Ee][Rr])

3807 [ -n "$GF_VIEWONLY" ] && continue

3808 val=${GF_OWNER:-$val}

3809 val=${val:-$user}

3810 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3811 prompt="" ;;

3812 [Uu][Ss][Ee][Rr])

3813 # XXX: is null $user ok?

3814 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3815 [ -n "$GF_VIEWONLY" ] && continue

3816 form="$user"

3817 ;;

3818 [Pp]assword)

3819 [ -n "$GF_VIEWONLY" ] && continue

3820 form="`cgi_passwd`"

3821 val=""

3822 ;;

3823 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3824 [ -n "$GF_VIEWONLY" ] && continue

3825 if [ -z "$rowid" ]; then

3826 val=`genserial`

3827 fi

3828 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3829 prompt="" ;;

3830 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3831 prompt=""

3832 ;;

3833 parent|path|blog*)

3834 prompt=""

3835 ;;

3836 "*"*)

3837 tail=$tail"``"

3838 continue ;;

3839 esac

3840 if [ -n "$prompt" ]; then

3841 if [ -n "${GF_VIEWONLY}" ]; then

3842 form=$val

3843 else

3844 :

3845 fi

3846 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3847 else

3848 hiddens=$hiddens$nl"$form"

3849 fi

3850 done < $1

3851 # enctype="multipart/form-data"

3852 cat<<EOF

3853 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3854 ${rowid:+$rm}

3855 <table class="b $2">

3856 $forms

3857 </table>$hiddens

3858 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3859 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3860 EOF

3861 if [ -z $GF_VIEWONLY ]; then

3862 cat<<EOF

3863 <input type="submit" name="sub" value="OK">

3864 <input type="reset" name="res" value="Reset">

3865 EOF

3866 fi

3867 cat<<EOF

3868 $GF_ARGS</form>

3869 $tail

3870 EOF

3871 }

3872 edittable() {

3873 # $1=form-def $2=table $3 rowid

3874 genform "$@"

3875 }

3876 viewtable() {

3877 GF_VIEWONLY=1 genform "$@"

3878 }

3879 showattc() {

3880 # $1=table_m $2=rowid &optional $3=RawFlag

3881 ## err \$1=$1 \$2=$2 \$3=$3

3882 if ! isfilereadable $user $1 $2; then

3883 contenttype; echo

3884 echo "このファイルは管理者のみしか見られません" | html p

3885 putfooter; exit

3886 fi

3887 idir=`umask 002; mktempd` || exit 1

3888 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3889 bin=$idir/$myname-$$.bin

3890 sql="select quote(bin) from $1 where rowid='$2';"

3891 ## err showattc: sql: $sql

3892 sq $db "$sql" | unhexize > $bin

3893 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3894 type=${tv%//*} fn=${tv#*//}

3895 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3896 ct=${type#file:}

3897 case $ct in # all text/* changed to text/plain

3898 text/*)

3899 charset=`nkf -g $bin|cut -d' ' -f1`

3900 case $charset in

3901 ASCII*) charset="" ;;

3902 esac

3903 if [ -z "$3" ]; then

3904 ct="text/html${charset:+; charset=$charset}"

3905 link="?showattc+$1+$2+raw"

3906 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3907 | sed 's,^,<span></span>,' \

3908 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3909 -D_LINK_="$link" \

3910 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3911 exit $?

3912 fi

3913 ct="text/plain${charset:+; charset=$charset}"

3914 ;;

3915 esac

3916 contenttype "$ct"

3917 echo "Content-Disposition: filename=\"$fn\""

3918 echo "Content-Length: " `cat $bin | wc -c`; echo

3919 #echo "Content-Type: " ${type#file:}; echo

3920 cat $bin

3921 }

3922 #

3923 # Some default stupid handler on CGI values

3924 #

3925 default_storedb() {

3926 # ARG: $1=table-def-file

3927 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3928 tbl=`basename $1`

3929 tbl=${tbl%.def}

3930 cols="`grep :text $1|cut -d: -f2`"

3931 col=`echo "$cols"|head -1`

3932 vcol=`getpar $col`

3933 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3934 if [ -n "$vcol" ]; then

3935 par2table $1

3936 else

3937 return 2 # No insertion occurred

3938 fi

3939 }

3940

3941 default_view() { # $1=def-file

3942 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3943 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3944 default_storedb "$@"

3945 query "select rowid from $tbl order by rowid desc;" \

3946 | while read rowid; do

3947 viewtable $1 $tbl $rowid

3948 done | _m4 -D_TITLE_="$tbl" \

3949 -D_FORM_="`genform $1`" \

3950 -D_DUMPTABLE_="syscmd(cat)" \

3951 $layout/html.m4.html $layout/form+dump.m4.html

3952 }

3953 default_viewtext() { # $1=def-file

3954 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3955 default_storedb "$@"

3956 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3957 | _m4 -D_TITLE_="$tbl" \

3958 -D_FORM_="`genform $1`" \

3959 -D_DUMPTABLE_="syscmd(cat)" \

3960 $layout/html.m4.html $layout/form+dump.m4.html

3961 }

3962 default_smail() {

3963 default_storedb "$@"

3964 if [ $? -eq 2 ]; then

3965 _m4 -D_TITLE_="入力" \

3966 -D_FORM_="`genform $1`" \

3967 -D_DUMPTABLE_="" \

3968 $layout/html.m4.html $layout/form+dump.m4.html

3969 return

3970 fi

3971 cond=""

3972 for pk in `gettblpkey $tbl`; do

3973 pv=$(sqlquote "$(getpar $pk)")

3974 cond="$cond${cond:+ and }$pk=$pv"

3975 done

3976 sql="select rowid from $tbl where $cond;"

3977 rowid=`query "$sql"`

3978 ## err smail1 - "$sql" "-> rowid=$rowid"

3979

3980 while IFS=: read prompt name keytype type args; do # Read from $1

3981 val=`getpar $name`

3982 if [ -n "$val" ]; then

3983 text="$text

3984 $prompt

3985 $name=$val

3986 ---------------------------------------------------------"

3987 fi

3988 case "$type" in

3989 image|document|file)

3990 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3991 fns=$(echo "$fn"|while read fn; do

3992 err mv $tmpd/$fn.orig $tmpd/$fn

3993 mv $tmpd/$fn.orig $tmpd/$fn

3994 rm $tmpd/$fn.rowid # Remove cache flag

3995 ## err "`ls $tmpd/$fn`"

3996 echo $fn

3997 done)

3998 files="$files $fns"

3999 ;;

4000 esac

4001 done < $1

4002 ## err FILES=$files "`ls -lF $tmpd`"

4003 subj="from ${REMOTE_ADDR}"

4004 (echo "$url"

4005 echo "への書き込みがありました。"

4006 echo "------"

4007 echo "$text"

4008 ) | (cd $tmpd &&

4009 err LS="`ls -lF`" &&

4010 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4011 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4012 echo "以下の内容で送信しました。" | html p

4013 viewtable $1 $tbl \

4014 `query "select rowid from $tbl order by rowid desc limit 1;"`

4015 echo "戻る" | html a "href=\"?\""

4016 }