s4: 32109e639070 s4-funcs.sh

s4

view s4-funcs.sh @ 1008:32109e639070

Add application/javascript

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sat, 13 May 2023 15:35:25 +0900
parents	1586c9a93b5b
children	40021967c23e

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

8 if $isCGI; then

9 case "$SCRIPT_NAME" in

10 *-world-*)

11 S4WORLD=${SCRIPT_NAME#*world-}

12 S4WORLD=${S4WORLD%.*}

13 echo S4WORLD=$S4WORLD >&2

14 worldconf=s4-config-${S4WORLD}.sh

15 ;;

16 *)

17 worldconf=s4-config.sh

18 ;;

19 esac

20 echo worldconf=$worldconf >&2

21 [ -n "$worldconf" -a -e "$worldconf" ] && . ./$worldconf

22 echo DB=$DB >&2

23 fi

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

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

26 cgiext=${CGIEXT:-.cgi}

27 myargs="$@"

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

29 tmpdir=${TMPDIR:-tmp}

30 dbdir=${DBDIR:-db}

31 logdir=${LOGDIR:-tmp}

32 tmpfiles=""

33 querylog=${QUERYLOG:-$logdir/query.log}

34 searchlog=${SEARCHLOG:-$logdir/search.log}

35 defaultdb=$dbdir/cgi.sq3

36 db=${S4INITDB:-${DB:-$defaultdb}}

37 sessdb=${SESSDB:-$dbdir/sess.sq3}

38 userupdateflag=$dbdir/userupdate

39 sesstb=tmp.sess

40 workdb=$dbdir/tmpdata.sq3

41 listentlimit=${LISTENTLIMIT:-30}

42 listartlimit=${LISTARTLIMIT:-50}

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

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

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

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

47 templ=${TEMPL:-templ}

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

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

50 imgdir=${IMGDIR:-img}

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

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

53 msgdir=$templ/msg

54 timeout="+2 days"

55 memoplimitdays="7"

56 dumpcollen=22

57 #thumbxy=120x120

58 thumbxy=96x96

59 iconxy_S=80x80

60 iconxy_M=400x400

61 maximagexy=1600x1600

62 ### maximagexy=400x400

63 filesize_max=${FILESIZE_MAX:-$((5*1024*1024))}

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

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

66 file_accept='accept=".jpg,.jpeg,.gif,.png,.tiff,.pdf,.odt,.ods,.odp,.odg,.mp3,.mp4,.m4v,.obj,.avi,.ogg,.mov,.webm,.gpx,.json,.geojson,.kml,.html,.css,.js,.java,.go,.cc,.rb,.py,.pl,lua,.awk,.sh,.c,.h,.txt,.tex,.sty,.zip,.xcf,.bz2,.gz,.xz,.7z,.csv,.dat,.db,.sq3,.blend,.gltf"'

68 file_accept_help="

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

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

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

72 "

73 file_warn="$file_accept_help

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

75 hidden_mode="('quiz', 'enquete')"

76 blogreadflagrowid=0

77 blogcutoffflagrowid=-1

78 nonewgroupworld=${NONEWGROUPWORLD:-*archive*}

79 whatsnewdays=${WHATS_NEW_DAYS:-14}

80 main_session=`date +%F-$$`

81 session=$main_session

82 mathjax=${MATHJAX:-'<script>MathJax = {

83 tex: {tags: "ams",

84 macros: {warn: ["\\fcolorbox{red}{white}{#1}", 1],

85 Warn: ["{\\Large \\warn{#1}}", 1]}}};</script>

86 <script src="https://polyfill.io/v3/polyfill.min.js?features=es6"></script><script id="MathJax-script" async src="https://cdn.jsdelivr.net/npm/mathjax@3/es5/tex-mml-chtml.js"></script>'}

88 tconfs=""

89 imgcached=cache/${S4WORLD:+$S4WORLD/}img.`date +%Y/%m`

90 conftbl=_tblconf

91 nl="

92 "

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

94 iconcachekey="profimgcache_S"

95 asdelim=":::" # delimiter of dumptable td-class specifier

97 # Start debug logging

98 logtag="($$)${S4WORLD:+{$S4WORLD\}}"

99 exec 3>> $logdir/debug.out

100 err() {

101 # echo "[`date +%F-%T%z`]$logtag $@" 1>&3

102 # Avoid backslash escape sequences

103 cat<<EOF 1>&3

104 [`date +%F-%T%z`]$logtag $@

105 EOF

106 }

107 case "$HTTP_USER_AGENT" in

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

109 *) touchpanel="" ;;

110 esac

111

112 # If S4MASTERDB is set, behave in another world

113 ### if [ -n "$S4MASTERDB" -a -s "$S4MASTERDB" ]; then

114 # If S4WORLDLIST is set, this s4 have world!

115 if [ -n "$S4WORLDLIST" ]; then

116 . ./s4-world.sh 2>> $logdir/debug.out

117 # Variables set in s4-world.sh

118 # $S4WORLDS, $S4WROLDNAME, $S4WORLDGRPS

119 # Files created in s4-world.sh

120 # $worldlistfile, $worldoptionfile, $worldnamefile, $worldgrpfile

121 fi

122

123

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

125

126 : <<EOF

127

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

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

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

131 レポート提出管理

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

133

134 [2016]

135 7/12 根本への反省

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

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

138

139 [以下2015]

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

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

142 Web

143 締切設定

144

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

146 ○自分の提出物リスト

147

148 7/19 ○設置

149 ○一斉送信

150 ○getparfilename の tmpd の扱い

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

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

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

154 ○添付ファイル回収

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

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

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

158

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

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

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

162 ○レポート提出モード

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

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

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

166

167

168 7/9 ○管理者の追加

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

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

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

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

173

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

175

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

177 ○グループ検索

178

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

180

181

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

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

184

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

186 ○6/1 par2tableを triplex 対応に

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

188 →とすると一気に

189

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

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

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

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

194

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

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

197 ## 1. passwd

198 ## 2. grp

199 ## 3. grp_mem

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

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

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

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

204

205 ## ● listの定義:

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

207 ## ● hashの定義:

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

209

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

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

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

213 ## csv2sq3 で .csv.sq3 の Makefile

214

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

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

217 ## type := root | comment

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

219 ## unique(id, filename)

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

221

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

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

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

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

226 ## できたー!

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

228 ↓

229 ↓以下に変更

230 with recursive allmem as

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

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

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

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

235

236

237 with recursive allmem as

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

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

240 where allmem.val=grp_m.gname)

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

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

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

244

245

246 ## triggerもできた。

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

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

249

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

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

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

253 ## グループ管理

254 ## 個人情報管理

255 ##

256

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

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

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

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

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

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

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

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

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

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

267

268 ## ログテーブル

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

270

271

272

273 ■表設計

274 * 3つの表に分散管理

275 id格納表 + hash表 + list表

276 * *_s *_m

277

278

279

280 user, user_map, user_col

281

282 ■抽象エントリタイプ

283 * user

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

285 * group

286 権限判定に利用

287 * serial

288 自動idとして機能

289 * password

290 入力 type=passwordで入力

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

292 * session

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

294 * text

295 入力 type=text

296 * textarea

297 入力 textarea

298 * image|document

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

300 * owner

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

302 * gowner

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

304 * timestamp

305 datetime()

306 * parent

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

308 * path

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

310

311 格納タイプ

312 * list

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

314 * hash

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

316

317 オブジェクトタイプ

318 * entry

319 id, title, owner

320 * textpart

321 id, parentID, text

322 * binarypart

323 id, parentID, contenttype, filename, content

324 * content

325 hash(textpart), list(binarypart)

326 * topic

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

328 * reply

329 id, parentID, content

330 * blog

331 list(entry)

332 blog = [topic, list(reply)]

333

334

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

336 "text" => "hogehoge ..",

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

338 "author" => "taro",

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

340 "parent" => "/",

341 "path" => "/1",

342 "text" => "blah, blah, ....",

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

344 {"serial" => 2,

345 "author" => "hanako",

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

347 "parent" => "/",

348 "path" => "/2",

349 "text" => "blah, blah, ....",

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

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

352

353

354 user:=

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

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

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

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

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

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

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

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

363

364 変換表

365 /user/email=m

366

367 blog:=

368 シリアル:id:p:serial

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

370 所有者:owner:s:owner:

371 時刻:ctime:s:stamp:

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

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

374

375 article:=

376 シリアル:id:p:serial

377 筆者:author:s:owner

378 時刻:ctime:s:stamp:

379 参照元:parent:s:parent:

380 パス:path:s:path:

381 本文:text:s:textarea:

382 画像:image:m:image:

383

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

385

386

387 EOF

388

389 logstart() {

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

391 }

392 logend() {

393 echo ">>>$logtag" >> ${1:-$querylog}

394 }

395 sqlog() {

396 logstart

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

398 cat >> $querylog

399 else

400 echo "$*" >> $querylog

401 fi

402 logend

403 }

404 sq() {

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

406 logstart

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

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

409 else

410 echo "$@" >> $querylog

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

412 ###sqlite3 -bail -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000" "$@"

413 fi

414 logend

415 }

416 dbsetup() {

417 pipedir=$tmpdir/pipedir

418 [ -d $pipedir ] || mkdir -p -m 1777 $pipedir

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

420 suf=`date +%s`

421 sqi=$pipedir/sqi-$suf.$$

422 sqo=$pipedir/sqo-$suf.$$

423 mkfifo $sqi $sqo

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

425 sq $db < $sqi &

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

427 if $isCGI; then

428 exec 2>> $logdir/error.out

429 fi

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

431 chmod o-r $logdir/error.out $logdir/debug.out

432 rm $sqi

433 # Attach supplemental DB

434 cat >&5 <<-EOF

435 .output /dev/null

436 ATTACH DATABASE "$sessdb" AS tmp;

437 CREATE TABLE IF NOT EXISTS $sesstb(user, skey, expire, UNIQUE(user, skey));

438 DELETE FROM $sesstb WHERE expire < datetime('now', 'localtime');

439 DELETE FROM $sesstb WHERE expire is NULL or expire = '';

440 .output stdout

441 EOF

442 }

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

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

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

446 fi

447 chmod o-r $querylog $db $sessdb # make sure

448 }

449 cleanup() {

450 trap '' INT HUP EXIT TERM PIPE

451 echo .quit >&5

452 kill $sq3pid

453 kill $sq3pid

454 rm -f $sqo $sqi

455 rm -rf $tmpfiles

456 cleanup2

457 }

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

459 # simple redirection for the sake of speed.

460 query() {

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

462 echo ".output $sqo" >&5

463 logstart

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

465 tee -a $querylog

466 else

467 printf '%s\n' "$@" >> $querylog

468 printf '%s\n' "$@"

469 fi >&5

470 echo ".output stdout" >&5

471 cat $sqo

472 rc=$?

473 logend

474 return $rc

475 }

476 _m4() {

477 #S4NAME=f,f,f

478 m4 ${S4NAME:+"-D_S4NAME_=${S4NAME}"} ${S4CSS:+-D_S4CSS_="$S4CSS"} \

479 ${S4WORLD:+-D_S4WORLD_="$S4WORLD"} \

480 ${S4WORLDNAME:+-D_S4WORLDNAME_="$S4WORLDNAME"} \

481 ${S4WORLDGRPS:+-D_S4WORLDGRPS_="$S4WORLDGRPS"} \

482 ${S4WORLDS:+-D_S4WORLDS_="$S4WORLDS"} "$@"

483 }

484 ismember() {

485 # $1=user, $2=group

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

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

488 }

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

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

491 }

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

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

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

495 }

496 isgrpowner() (

497 # $1=user, $2=group

498 gn=`sqlquote "$2"`

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

500 err isgrpowner: $sql

501 test -n "`query \"$sql\"`"

502 )

503 isgrpownerbygid() (

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

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

506 err isgrpownerbygid: $sql

507 test -n "`query $sql`"

508 )

509 getgroupadminmails() {

510 # $1=group

511 for i in $(getgroupadmins "$1"); do

512 email4group "$1" "$i" ;

513 done

514 }

515 getgroupadmins() { # $1=group

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

517 qgrp=`sqlquote "$1"`

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

519 }

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

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

522 getvalbyid grp $2 \

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

524 }

525 getgroupbyid() {

526 # $1=id|gname

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

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

529 # err ggbyid: `echo $sql`

530 query $sql

531 }

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

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

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

535

536 # Return true when anonymous mode

537 [ "$anonymousmode" ] && return 0

538 # case `getvalbyid blog mode $2` in

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

540 # *closed*)

541 # owner=`getvalbyid blog owner $2`

542 # if isgrp $owner; then

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

544 # elif isuser $owner; then

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

546 # fi

547 # esac

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

549 case "$2" in

550 grp_*)

551 sql="SELECT 'owner'

552 FROM grp_adm

553 WHERE gname=(SELECT gname FROM $2 WHERE rowid=$3)

554 AND

555 user = '$user';"

556 ;;

557 *)

558 sql="with getblog as (

559 select key,val from blog_s where id=(

560 select blogid from article where id in

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

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

563 getauthor as (select author from article where id=(select id from $2 where rowid=$3)),

564 isgrp as (SELECT val from getowner WHERE val IN (select gname from grp)),

565 isgrpadm as (select user from grp_adm where

566 gname=(select val from getowner) and

567 user='$1'),

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

569 select case

570 when (select author from article where

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

572 then 'author'

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

574 then 'open'

575 when (select val from getmode) in ('quiz', 'enquete')

576 then CASE

577 WHEN (SELECT val FROM isgrp) IS NULL

578 THEN

579 CASE WHEN (SELECT val from getowner)

580 IN ('$user', (SELECT author FROM getauthor))

581 THEN 'owner-or-user-article-is-readable'

582 ELSE ''

583 END

584 WHEN (select user from isgrpadm) IS NOT NULL

585 THEN 'i-am-admin'

586 ELSE (SELECT author from getauthor WHERE author IN (SELECT user FROM grp_adm WHERE gname=(SELECT val FROM getowner)))

587 END

588 when (select val from getmode) is null

589 then 'open'

590 when (select val from getowner) in (select gname from grp)

591 then (SELECT user FROM isgrpadm)

592 when (select author from article where

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

594 then 'user+author'

595 else '' end;"

596 ;;

597 esac

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

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

600 # result=`query "$sql"`

601 # err FileAccessibility=$result

602 [ -n "`query $sql`" ] || return 2

603 }

604 linkhome() {

605 # $1=UserOrGroupRowid

606 echo -n "<a href=\"$myname?"

607 if isuser $1; then

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

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

610 name=`gecos $1|htmlescape`

611 else

612 _grid=`numericalize "$1"`

613 echo -n "grp+$1"

614 name=`query "SELECT gname FROM grp WHERE rowid=$_grid;"|htmlescape`

615 fi

616 echo "\">$name</a>"

617 }

618 hreflink() {

619 # s4 specific notation:

620 # ^href=URL

621 # ^iframe=URL

622 # ^video=URL

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

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

625 # OSM umap Wikistyle Notation:

626 # [[URL]] - Simle Link

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

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

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

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

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

632 # Other Style

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

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

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

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

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

638 # SPC+SPC+$ - <br>

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

640 checkboxON="${cb} checked>"

641 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

666

667 }

668 minitbl() {

669 sed -n '

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

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

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

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

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

675 H; # Concat this line to HoldSpace

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

677 $ b cont

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

679 }

680 :cont

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

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

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

684 # . of ^. is workaround for FreeBSD sed

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

686 p; # Print whole "table" element

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

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

689 }

690 x; # Back to the newest line

691 p; # Print rest' | miniul

692 }

693 miniul() {

694 sed -e '

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

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

697 :top

698 s/\n//;

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

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

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

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

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

704 $ b cont

705 N; # 次の行を読む

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

707 /^ /b top

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

709 p; s/.*//;

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

711 /^\* /b top

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

713 }

714

715 :cont

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

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

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

719 H; x

720 }

721 x' | miniol

722 }

723 miniol() {

724 sed -e '

725 /^[1-9]\. / {; # 行頭 "N. "

726 h;x; # 1周目: ホールドスペース先頭に <ol> を

727 s,^$[1-9][0-9]*$\. .*,<ol start="\1">,; # 初期番号付きで追加

728 x;

729 :top

730 s/\n//;

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

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

733 x;

734 s,[1-9][0-9]*\. ,,; # まず行頭の "N. " を消しておく

735 x;

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

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

738 $ b cont

739 N; # 次の行を読む

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

741 /^ /b top

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

743 p; s/.*//;

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

745 /^[1-9][0-9]*\. /b top

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

747 }

748

749 :cont

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

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

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

753 H; x

754 }

755 x'

756 }

757 acclog() (

758 # $1=table, $2=rowid

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

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

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

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

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

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

765 fi

766 )

767 gecos() (

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

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

770 )

771 setpar() {

772 # 2020/5/14 Add dirty code to cache essential params

773 if [ x"$session" = x"$main_session" ]; then

774 case "$1" in

775 user) _user="$v" ;;

776 skey) _skey="$v" ;;

777 esac

778 fi

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

780 }

781 unsetpar() {

782 for i; do

783 if [ x"$session" = x"$main_session" ]; then

784 case "$i" in

785 user|skey) unset _$i ;;

786 esac

787 fi

788 query "DELETE FROM par WHERE var='$i' AND sessid='$session';"

789 done

790 }

791 replpar() {

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

793 }

794 getpar() {

795 # err GETPAR=$1, _user=$_user

796 val=""

797 if [ x"$session" = x"$main_session" ]; then

798 case "$1" in # Dirty cache mechanism for high-load average

799 user) val=$_user ;;

800 skey) val=$_skey ;;

801 esac

802 fi

803 val=${val:-`query "select val from par where var='$1' and sessid='$session' $2;"`}

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

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

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

807 fi

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

809 case "$var" in

810 owner)

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

812 echo $user; return

813 elif ismember $user $val; then

814 printf '%s' "$val"; return

815 fi ;;

816 esac

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

818 printf '%s' "$val"

819 }

820 setskey() {

821 # For quick response...(?)

822 query "REPLACE INTO $sesstb VALUES('$1', '$2', datetime('now', 'localtime', '$timeout'));"

823 }

824 chkskey() {

825 # $1=sesskey, $user=LoginUserName

826 test -z "$1" && return 1

827 repl=`query "SELECT rowid,user FROM $sesstb WHERE user='$user' AND skey = '$1';"` || return 2

828 rowid=${repl%%\|*}; repuser=${repl#*\|}

829 if [ -n "$rowid" -a x"$user" = x"$repuser" ]; then

830 query "UPDATE $sesstb SET expire=datetime('now', 'localtime', '$timeout') WHERE rowid=$rowid;" # Errors can be ignored

831 return 0

832 fi

833 return 1

834 }

835 resetskey() {

836 if [ -n "$_user" ]; then

837 query "DELETE FROM $sesstb WHERE user='$_user';"

838 fi

839 }

840 getpartype() {

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

842 }

843 getparcount() {

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

845 }

846 getparfilename() {

847 # null if type of $1 is not file

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

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

850 }

851 sqlquote() {

852 (v="$1"

853 case "$v" in

854 "") return ;; # null

855 "X'"*) # quoted hex string

856 echo $1 ;;

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

858 v=`printf '%s' "$v"|sed -e 's/\"/\"\"/g'`

859 printf '%s' "\"$v\""

860 return ;;

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

862 printf '%s' "\"$v\""

863 return ;;

864 *)

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

866 printf '%s' $v # MAYBE numeric, maybe...

867 else

868 printf '%s' "\"$v\""

869 fi ;;

870 esac)

871 }

872 sqlquotestr() (

873 case "$1" in

874 *\'*) v=`printf '%s' "$1"| sed "s/'/''/g"`

875 printf '%s' "'$v'" ;;

876 *) printf '%s' "'$1'" ;;

877 esac

878 )

879 mktempd() {

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

881 }

882 getcachedir() { # $1=maintable

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

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

885 else

886 echo $tmpd/$thumbxy

887 fi

888 }

889 getval() {

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

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

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

893 echo "$user" ;;

894 stamp|datetime)

895 date "+%F %T" ;;

896 serial)

897 (s=`getpar $2`

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

899 *)

900 getpar "$2" "$3";;

901 esac

902 }

903

904 getvalquote() {

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

906 (v=`getval "$@"`

907 case "$v" in

908 "") echo NULL ;;

909 *) sqlquote "$v" ;;

910 esac)

911 }

912 getparquote() {

913 sqlquote "`getpar $1`"

914 }

915 getbinbyid() {

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

917

918 }

919 getvalbyid() {

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

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

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

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

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

925

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

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

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

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

930 return

931 fi

932 done

933 rowid=$3

934 pk=`gettblpkey $1`

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

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

937 td=${4:-$tmpd}

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

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

940 for kt in s m; do

941 t=${1}_$kt

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

943 vcount=1 # count(val)

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

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

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

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

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

949 if [ $kt = m ]; then

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

951 # Reset val to store filenames if type is string

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

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

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

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

956 i=0

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

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

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

960 i=$((i+1))

961 fn=$c.$i

962 ## err td=$td, fn=$fn, type=$type, val="[$val]"

963 case $type in

964 file:*)

965 #file=$td/$val

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

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

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

969 # FOR SPEED: Skip file generation if imgcache exists

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

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

972 && continue

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

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

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

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

977 select rowid from $cond $slice;

978 .output '$td/$fn'

979 select val from $cond $slice;

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

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

982 .output stdout

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

984 EOF

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

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

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

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

989 ls -lh "$file" |

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

991 case "$type" in

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

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

994 *|*:[Aa]pplication*)

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

996 png:- > "$file"

997 ;;

998 esac

999 ;;

1000 *)

1001 sq $db<<EOF

1002 .output $td/$fn.rowid

1003 select rowid from $cond $slice;

1004 .output $td/$fn

1005 select val from $cond $slice;

1006 EOF

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

1008 ;;

1009 esac

1010 done

1011 else

1012 rm -f $td/$c.count

1013 case $type in

1014 file:*)

1015 printf '%s\n' "$val" \

1016 | while read fn; do

1017 file=$td/$fn

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

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

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

1021 | unhexize > "$file"

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

1023 case $type in

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

1025 *:[Aa]pplication*)

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

1027 png:- > $file ;;

1028 esac

1029 fi

1030 done

1031 ;;

1032 esac

1033 fi

1034 printf '%s' "$val"

1035 return

1036 fi

1037 done

1038 done)

1039 }

1040 getvalbypkey() (

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

1042 pk=`gettblpkey $1`

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

1044 getvalbyid "$1" "$2" $rowid $4

1045 )

1046 getvalbycond() {

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

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

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

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

1051 getvalbyid "$1" "$2" $rowid "$4"

1052 fi

1053 }

1054 getpwfield() {

1055 # getpwfield user column

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

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

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

1059 echo "$val"

1060 return 0

1061 else

1062 return 1

1063 fi

1064 }

1065 numericalize() {

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

1067 }

1068 encode() {

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

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

1071 sha1=sha1

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

1073 sha1=sha1sum

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

1075 sha1=gsha1sum

1076 fi

1077 fi

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

1079 }

1080 if type gs >/dev/null 2>&1; then

1081 gs_pdfwrite() {

1082 gs -sDEVICE=pdfwrite -dPDFSETTINGS=/default \

1083 -dNOPAUSE -dQUIET -dBATCH -o "$2" "$1" >/dev/null 2>&1

1084 }

1085 fi

1086 enjpeg() {

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

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

1089 cjpeg="cjpeg"

1090 else

1091 cjpeg="convert - jpeg:-"

1092 fi

1093 fi

1094 $cjpeg "$@"

1095 }

1096 mycrypt() (

1097 key=$1 salt=$2

1098 # err \$2=$2

1099 case $2 in

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

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

1102 esac

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

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

1105 )

1106 hexize() {

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

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

1109 hexize="xxd -p"

1110 else

1111 hexize_hd() {

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

1113 }

1114 hexize="hexize_hd"

1115 fi

1116 fi

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

1118 }

1119 unhexize() {

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

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

1122 unhex="xxd -p -r"

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

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

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

1126 EOF

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

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

1129 # world writable...:)

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

1131 fi

1132 fi

1133 cat "$@" | $unhex

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

1135 }

1136 percenthex() {

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

1138 }

1139 htmlescape() {

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

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

1142 -e 's/`/\&#96/'

1143 }

1144 enascii() {

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

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

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

1148 else

1149 enascii_now=`date +%FT%T`

1150 enascii_sed() {

1151 nkf -Z0Z1Z2 \

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

1153 }

1154 enascii="enascii_sed"

1155 fi

1156 fi

1157 cat "$@" | $enascii

1158 }

1159 size_h() {

1160 i="$1" oi=$1

1161 set -- B B KB MB GB TB

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

1163 oi=$i

1164 i=$((i/1024))

1165 shift

1166 done

1167 echo ${oi}$1

1168 }

1169 gettblconf() {

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

1171 ## tconfs=`sq $db \

1172 tconfs=`query \

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

1174 fi

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

1176 }

1177 gettblkeys() {

1178 # $1=tbl

1179 gettblconf

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

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

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

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

1184 case $type in

1185 *p*)

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

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

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

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

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

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

1192 esac

1193 case $type in

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

1195 esac

1196 done

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

1198 }

1199 gettblpkey() {

1200 # $1=tbl

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

1202 }

1203 gettblfkey() {

1204 (x=`gettblkeys $1`

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

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

1207 }

1208 gettblcols() {

1209 (x=`gettblkeys $1`

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

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

1212 }

1213 gettbl_s_cols() {

1214 (x=`gettblkeys $1`

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

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

1217 }

1218 gettbl_m_cols() {

1219 (x=`gettblkeys $1`

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

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

1222 }

1223 gettbl_h_cols() {

1224 (x=`gettblkeys $1`

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

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

1227 }

1228 gettbl_coltype() (

1229 gettblconf

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

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

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

1233 )

1234 is_hidden() {

1235 # $1=Tbl $2=col

1236 gettblconf

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

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

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

1240 case $x in

1241 *h*) return 0 ;;

1242 *) return 1 ;;

1243 esac

1244 }

1245

1246 dbsetbyid() {

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

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

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

1250 quotedp=$(sqlquotestr "$p")

1251 unset primary update

1252 gettblconf

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

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

1255 #err conf=$conf

1256 case ${conf#*=} in

1257 p*) primary=1 ;;

1258 f*) update=1 ;;

1259 u*) ;;

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

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

1262 esac

1263 #err t=$t

1264 type=string fn=""

1265 case $conf in

1266 */password)

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

1268 ;;

1269 */image*|*/document*)

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

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

1272 ;;

1273 esac

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

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

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

1277 if [ "$primary" ]; then

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

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

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

1281 elif [ "$update" ]; then

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

1283 else

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

1285 fi

1286 )

1287 }

1288 expire() (

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

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

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

1292 )

1293 addsession() {

1294 # expireをセット

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

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

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

1298 # Remove old session parameters

1299 now=`expire now`

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

1301 }

1302 gencookie() (

1303 path=${URL#*:/}

1304 path=${URL%/*}

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

1306 for kv; do

1307 # echo "Set-Cookie: $kv; expires=$expire; Path=$path"

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

1309 done

1310 )

1311 contenttype() {

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

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

1314 }

1315 putheader() {

1316

1317 }

1318 putfooter() {

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

1320 }

1321 getcookie() {

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

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

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

1325 ## err "GetCookie: $k=[$v]"

1326 case "$k" in

1327 user) _user="$v" ;;

1328 skey) _skey="$v" ;;

1329 esac

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

1331 done

1332 }

1333 genrandom() {

1334 # $1=columns (default: 10)

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

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

1337 }

1338 genserial() {

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

1340 }

1341 smail() {

1342 # smail rcpts subj (file)

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

1344 # $MAIL_FROM <- From: header value

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

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

1347 ## Gmail rejects below(Duplicated headers)

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

1349 rcptheader=`echo $1|tr ' ' '\n'|sort -u|tr '\n' ','|sed 's/,$//'`

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

1351 sender=${SENDER:-$admin}

1352 # Do not call m4 with directly passing text

1353 _r=$tmpd/rcpt

1354 echo -n "${SMAIL_TO:-$rcptheader}" > $_r

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

1356 (_m4 -D_RCPT_="spaste(\`$_r')" -D_REPLYTO_="$replyto" -D_SUBJ_="\`$subj'" -D_FROM_="$from" $msgdir/mail-header.m4

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

1358 }

1359 smail_queue_flush() {

1360 # $1=timelimit

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

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

1363 SELECT rowid FROM smailq

1364 GROUP BY rcpts, subj

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

1366 EOF

1367 )

1368 for rid in $rowids; do

1369 sq $workdb \

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

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

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

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

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

1375

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

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

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

1379 .separator "\n" "------------------\n\n"

1380 SELECT time, text FROM smailq

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

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

1383 ORDER by time;

1384 EOF

1385 then

1386 cat <<-EOF | sq $workdb

1387 DELETE FROM smailq

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

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

1390 EOF

1391 fi

1392 fi

1393 done

1394 }

1395 smail_queue() {

1396 # $1=Rcpts, $@=subj

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

1398 if [ $? -eq 0 ]; then

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

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

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

1402 err "smail_queue: rcpts=[$1] s=[$2] t:hex=[$text]"

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

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

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

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

1407 EOF

1408 )

1409 ## XXX: Adhoc load mitigation

1410 case "$now" in

1411 *[01])

1412 err flush_queue=$mintime

1413 smail_queue_flush ;;

1414 esac

1415 fi

1416 }

1417 setviastring() {

1418 table=par

1419 oifs="$IFS"

1420 IFS="&"

1421 for us in $1; do

1422 k=${us%%=*}

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

1424 setpar "$k" "string" "$v"

1425 done

1426 IFS="$oifs"

1427 }

1428 checkdomain() (

1429 # Check the validity of domain by referring DNS

1430 item=$1

1431 err checkdomain $1

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

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

1434 )

1435 pwcheck() {

1436 # $1=passwd

1437 dbpswd=`getpwfield $user pswd`

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

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

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

1441 }

1442 mypwhash() {

1443 mycrypt "`cat`" `genrandom 5`

1444 }

1445 flag_profupdate() {

1446 # XXX: Sorry to use undeclared column in user.def

1447 # This is useful to mitigate account sync load

1448 query <<-EOF

1449 REPLACE INTO user_s(name, key, type, val)

1450 VALUES('$user', 'profupdate', 'string', datetime('now', 'localtime'));

1451 EOF

1452 touch $userupdateflag

1453 }

1454 wasureta() {

1455 user=$1

1456 if ! checkdomain $user; then

1457 contenttype; echo

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

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

1460 putfooter

1461 exit 0

1462 fi

1463 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1465 encpswd=`echo $newpswd|mypwhash`

1466 dbsetbyid user $user pswd "$encpswd" && touch $userupdateflag

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

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

1469 $msgdir/mail-newaccount.m4 \

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

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

1472 }

1473 checkauth() {

1474 user=`getpar user`

1475 skc=`getpar skey` # from cookie

1476 [ -z "$user" ] && return 2

1477 ##skey="`getpwfield $user skey`"

1478 if [ -n "$skc" ]; then

1479 if chkskey "$skc"; then

1480 gencookie "user=$user" "skey=$skc" # 2021-12-24: Update expire

1481 return 0

1482 fi

1483 fi

1484 pswd=`getpar pswd`

1485 quser=`sqlquotestr "$user"`

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

1487 if [ $? != 0 ]; then # Maybe DB locked

1488 return 4 # 4=server too heavy

1489 elif [ -z "$dbuser" ]; then

1490 err "Login USER failed: [$user]"

1491 return 2 # 2=login fail

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

1493 if [ -n "$S4MASTERDB" ]; then

1494 contenttype; echo

1495 echo "Reset password is valid in BASE world." | html p

1496 echo "パスワードリセットはベースワールドでおこなってください。" | html p

1497 cat <<-EOF | html p

1498 <a href="$S4MASTERURL">BASE World</a>

1499 EOF

1500 exit 0 # XXXXX more smart...

1501 fi

1502 wasureta "$user"

1503 err wasureta issued for "$user"

1504 return 1 # wasureta error

1505 fi

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

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

1508 if pwcheck "$pswd"; then

1509 newsession=`genrandom 34`

1510 if setskey "$user" "$newsession" &&

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

1512 gencookie "user=$user" "skey=$newsession"

1513 return 0

1514 else

1515 return 4 # Heavy load??

1516 fi

1517 fi

1518 err "Login failed: [$user]"

1519 return 2 # Password mismatch

1520 }

1521 showlogin() {

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

1523 test -z "$args" && resetskey

1524 s4name=${S4NAME:-s4}

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

1526 cat $layout/login.m4.html

1527 echo '</body></html>'

1528 ) | _m4 \

1529 -D_BODYCLASS_="login" \

1530 -D_TITLE_="$s4name" \

1531 -D_SYSNAME_="Welcome to $s4name" \

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

1533 exit 0

1534 }

1535 dologin() {

1536 test -n "$S4WORLD" && syncaccount

1537 checkauth

1538 st=$?

1539 if [ $st != 0 ]; then

1540 contenttype; echo

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

1542 -D _LOADAVG_="`uptime|awk '{print $(NF-2)}'|tr -d ,`" \

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

1544 showlogin # and EXIT

1545 fi

1546 err "Auth OK: [$user]"

1547 }

1548

1549 # Do instant jobs here

1550 dbsetup

1551 trap cleanup INT HUP EXIT TERM PIPE

1552 # trap cleanup INT HUP

1553

1554

1555 cgiinit() {

1556 tmpd=`tmpd=$tmpdir mktempd`

1557 tmpf=$tmpd/stream.$$

1558 tmpfiles=$tmpfiles" $tmpd"

1559 addsession $session

1560 getcookie

1561 case "$REQUEST_METHOD" in

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

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

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

1565 # cat > $tmpf # too much?

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

1567 err "CONTENT_LENGTH=$CONTENT_LENGTH$nl`ls -lF $tmpf`"

1568 s="`cat $tmpf`"

1569 ;;

1570 esac

1571 case "$CONTENT_TYPE" in

1572 *boundary*)

1573 bndry=${CONTENT_TYPE#*boundary=}

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

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

1576 do

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

1578 #echo u=$us

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

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

1581 case "$k" in

1582 *:filename)

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

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

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

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

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

1588 :

1589 fi

1590 err "k=[$k] v=[$v]" # debug@2020-05-31

1591 err "`ls -lF $tmpd/$v` MimeType=$mimetype"

1592 case "$mimetype" in

1593 [Ii]mage/x-xcf)

1594 bzip2 "$tmpd/$v"

1595 v=${v}.bz2

1596 ;;

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

1598 [Ii]mage/[Hh][Ee][Ii][Ff])

1599 if type heif-convert >/dev/null 2>&1; then

1600 vjpg="${v%.heic}.jpg"

1601 err "Conv $v to $vjpg in $tmpd"

1602 convert -quality 75 -resize $maximagexy'>' \

1603 "$tmpd/$v" "$tmpd/$vjpg" >/dev/null 2>&1

1604 v=$vjpg

1605 else

1606 mimetype="Not supported"

1607 fi

1608 ;;

1609 [Ii]mage/*)

1610 mogrify -quality 75 -resize $maximagexy'>' "$tmpd/$v"

1611 err "Mogrified: `ls -lF $tmpd/$v`" # 2020-05-31

1612 ;;

1613 [Aa]pplication/[Pp][Dd][Ff])

1614 if [ x"`getpar comppdf`" = x"yes" ]; then

1615 if type gs_pdfwrite >/dev/null 2>&1; then

1616 nv=${v%.pdf}-compressed.pdf

1617 err Calling gs from $v to $nv

1618 if gs_pdfwrite "$tmpd/$v" "$tmpd/$nv"; then

1619 err "PDF compressed: `ls -lF $tmpd/*.pdf`"

1620 v=$nv

1621 fi

1622 fi

1623 fi

1624 esac

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

1626 then

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

1628 continue

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

1630 replpar text string \

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

1632 continue

1633 fi

1634 ;;

1635 image|document|binary)

1636 # When no file name supplied to input[type="file"]

1637 continue ;;

1638 *)

1639 type='string'

1640 ;;

1641 esac

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

1643 setpar "$k" "$type" "$v"

1644 done

1645 ;;

1646 *)

1647 setviastring "$s"

1648 ;;

1649 esac

1650 }

1651 email4group() {

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

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

1654 users=`for i; do sqlquote "$i"; echo; done`

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

1656 sql="WITH

1657 grpemails AS (

1658 SELECT gname, user, val email

1659 FROM grp_mem NATURAL JOIN grp_mem_s

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

1661 useremails AS (

1662 SELECT user.name, val email

1663 FROM user

1664 LEFT JOIN user_m

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

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

1667 FROM useremails u LEFT JOIN grpemails g

1668 ON u.name=g.user

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

1670 query "$sql"

1671 }

1672 email4groupbyuid() {

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

1674 g=$1; shift

1675 uids=`echo "$@"`

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

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

1678 email4group "$g" `query "$sql"`

1679 }

1680 myemail4group() {

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

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

1683 }

1684 collectmembersbyid() {

1685 # Collect user names of group specified by grid

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

1687 query "SELECT user FROM grp_mem \

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

1689 }

1690 collectmembersbyid() {

1691 # Collect user names of group name

1692 qgrp=`sqlquote "$1"`

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

1694 }

1695 collectgecosesbyid() {

1696 # Collect user gecoses of group

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

1698 query<<-EOF

1699 SELECT gecos

1700 FROM gecoses

1701 WHERE name IN (SELECT user FROM grp_mem

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

1703 EOF

1704 }

1705 collectemail() (

1706 # Collect email addresses for group $1

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

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

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

1710 for e; do

1711 if isuser "$e"; then

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

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

1714 else

1715 qgrp=`sqlquote "$e"`

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

1717 gmem="grp_mem"

1718 else

1719 tm=`sqlquote "$TEAM"`

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

1721 fi

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

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

1724 $gmem g left join grp_mem_s s

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

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

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

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

1729 query "$sql"

1730 fi

1731 done

1732 )

1733 sendinvitation() (

1734 # $1=email

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

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

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

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

1739 gecos=`gecos`

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

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

1742 _m4 -D_URL_="$urlbase" \

1743 -D_USER_="$name" \

1744 -D_EMAIL_="$1" \

1745 -D_REGIST_="$regist" \

1746 -D_ADMIN_="$admin" \

1747 $msgdir/mail-invite.m4 \

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

1749 return 0

1750 )

1751 emaildomaincheck() {

1752 case "$1" in

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

1754 *@*)

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

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

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

1758 return 2

1759 fi

1760 return 0

1761 ;;

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

1763 esac

1764 }

1765 invite() {

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

1767 if [ -n "$INVITE_ONLYFROM" ]; then

1768 if ! echo "$user" | grep -E -e "$INVITE_ONLYFROM" >/dev/null 2>&1; then

1769 echo "招待可能ユーザに登録されていません" | html p

1770 return

1771 fi

1772 fi

1773 case "$email" in

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

1775 *@*)

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

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

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

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

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

1781 elif sendinvitation $email; then

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

1783 else # Cannot be reached here

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

1785 fi ;;

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

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

1788 esac

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

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

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

1792 fi

1793 if [ -f $invite_policy ]; then

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

1795 else

1796 pol="$invite_policy"

1797 fi

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

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

1800 -D_INVITE_POLICY_="$pol" \

1801 $layout/html.m4.html $layout/invite.m4.html

1802 }

1803 regist() {

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

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

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

1807 echo "bye bye" | html p

1808 reutrn

1809 fi

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

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

1812 cat<<EOF

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

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

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

1816 EOF

1817 return

1818 fi

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

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

1821 # Fake login password to wasureta

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

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

1824 wasureta $email

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

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

1827 addsession $1 # for removal after 1 minute

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

1829 $layout/login.m4.html

1830 return

1831 }

1832 group_safename() {

1833 # Convert $1 to safe group name

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

1835 }

1836 groupupdate() {

1837 gname=`getpar gname`

1838 qgname=`sqlquote "$gname"`

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

1840 # See ALSO same job in showgroup()

1841 newgname=`group_safename "$gname"`

1842 err newgname=$newgname

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

1844 err NewGNAME: gname=$newgname

1845 gname=$newgname

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

1847 replpar gname string "$gname"

1848 fi

1849 # Name confliction check

1850 parow=`getpar rowid`

1851 ## err parow=$parow

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

1853 query "BEGIN EXCLUSIVE;"

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

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

1856 if [ $count -gt 0 ]; then

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

1858 query "END;"

1859 return

1860 fi

1861 par2table $formdir/grp.def

1862 query "END TRANSACTION;"

1863 # Remove orphan

1864 : <<EOF

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

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

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

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

1869 EOF

1870 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1874 sql="delete from blog where id in

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

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

1877 query "$sql";

1878 unsetpar tag kwd

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

1880 return # and return

1881 fi

1882 fi

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

1884 fi

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

1886 grid=$(query $sql)

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

1888 grp $grid

1889 }

1890 groupclone() {

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

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

1893 case "$1" in

1894 */noteam)

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

1896 esac

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

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

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

1900 return

1901 fi

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

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

1904 return

1905 fi

1906 i=0

1907 while true; do

1908 copy="-copy$i"

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

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

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

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

1913 i=$((i++))

1914 continue

1915 fi

1916 break

1917 done

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

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

1920 query<<-EOF

1921 BEGIN;

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

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

1924 SELECT $newqname, key, val

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

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

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

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

1929 -- Copy members and their configuration --

1930 REPLACE INTO grp_mem SELECT $newqname, user

1931 FROM grp_mem WHERE gname=$qgrp;

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

1933 FROM grp_mem_s WHERE gname=$qgrp;

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

1935 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1936 -- Copy administrators --

1937 REPLACE INTO grp_adm SELECT $newqname, user

1938 FROM grp_adm WHERE gname=$qgrp;

1939 COMMIT;

1940 EOF

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

1942 STOPCLONEMSG=1 groupconf "$newrowid"

1943 }

1944 groupman() {

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

1946

1947 GF_STAGE="grpconf"

1948 GF_STAGE=groupupdate

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

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

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

1952 -D_DUMPTABLE_="syscmd(cat)" \

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

1954 }

1955 userconf() {

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

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

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

1959 }

1960 groupconf() {

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

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

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

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

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

1966

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

1968 grp=`getgroupbyid $rowid`

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

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

1971 return

1972 fi

1973

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

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

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

1977 ## Setup migration menu

1978 height="10em" ## Ugly!!

1979 if [ -n "$S4WORLDLIST" ]; then

1980 v=`fgrep -v "value=\"$worldconf\"" $worldoptionfile`

1981 err v=$v

1982 if [ -n "$v" ]; then

1983 migrate=$(cat<<-EOF

1984 `cgi_radio grpaction migrate id="migrate"`<label

1985 for="migrate">別Worldへ移住</label>

1986 <div style="height: $height;">

1987 <form action="?migrategrp">

1988 <p>移住先:<select name="migrateto">$nl$v$nl</select></p>

1989 <p>グループや掲示板のURLが変わります。

1990 外部からリンクしている場合は飛べなくなります。

1991 すでにリンクされた掲示板を多数含む場合は既存グループを温存し、

1992 「グループのクローン」で

1993 メンバーを引き継いだ上でそのクローンを移住するのがお勧めです。</p>

1994 <p><label>`cgi_checkbox emichk yes`確認</label></p>

1995 `cgi_hidden stage migrategrp`

1996 `cgi_hidden rowid $rowid`

1997 `cgi_submit OK`

1998 `cgi_reset Reset`

1999 </form>

2000 </div>

2001 EOF

2002 )

2003 fi

2004 fi

2005 html div 'class="foldtabs"' <<-EOF

2006 `cgi_radio grpaction clone id="clone"`<label

2007 for="clone">グループのクローン作成</label>

2008 <div style="height: $height;">

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

2010 <table>

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

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

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

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

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

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

2017 </table>

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

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

2020 </div>

2021 $migrate

2022 `cgi_radio grpaction close id="x"`<label for="x" accesskey="x">×</label>

2023 <div style="height: $height; background: transparent;"></div>

2024 EOF

2025 fi

2026 }

2027 migrategrp() {

2028 rowid=`getpar rowid`

2029 rowid=${rowid%%[!0-9]*}

2030 grp=`getgroupbyid $rowid`

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

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

2033 return

2034 fi

2035 if [ x`getpar emichk` != x"yes" ]; then

2036 echo "移住確認未チェックなので中止します。" | html p

2037 grp "$rowid"

2038 return

2039 fi

2040 destconf=`getpar migrateto`

2041 err destconf=$destconf

2042 if [ ! -e $destconf ]; then

2043 echo "移住先Worldが認識できないので中止します($destconf)。" | html p

2044 grp "$rowid"

2045 return

2046 fi

2047 if [ -n "$worldconf" ]; then

2048 srcconf=$worldconf

2049 else

2050 srcconf=s4-config.sh

2051 fi

2052 _m4 -D_TITLE_="移住操作" -D_BODYCLASS_="" $layout/html.m4.html

2053 echo "移住操作" | html h1

2054 echo '<pre>'

2055 set -- "$srcconf" "$destconf" "$rowid"

2056 err ./s4-migrate.sh "$srcconf" "$destconf" "$rowid"

2057 . ./s4-migrate.sh # Dot(.) sourcing might not pass arguments

2058 rc=$?

2059 echo "</pre>"

2060 if [ $rc -eq 0 ]; then

2061 echo "World [$world] への移住完了。" | html p

2062 echo "<p><a href=\"$dsturl?grp+$destrowid\">移住先</a></p>"

2063 elif [ -n "$faillist" ]; then

2064 echo "移住後削除失敗" | html p

2065 echo "空いている時間帯に再度試して下さい。" | html p

2066 else

2067 echo "移住失敗" | html p

2068 echo "移動先に重複がないか確認して下さい。" | html p

2069 fi

2070 return

2071 }

2072 mems() {

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

2074 kwd=`getpar kwd`

2075 listmember $kwd

2076 }

2077 grps() {

2078 case "$S4WORLD" in

2079 $nonewgroupworld) ;;

2080 *) LINK_NEWGRP="<a href=\"?groupman\">新規グループ作成</a>" ;;

2081 esac

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

2083 kwd=`getpar kwd`

2084 listgroup "$kwd" \

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

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

2087 -D_FORM_="${LINK_NEWGRP}${NEWGRP_GUIDE}" \

2088 $layout/form+dump.m4.html

2089 }

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

2091 gpg=`getpar grp`

2092 grid=${1:-$gpg}

2093 grp=`getgroupbyid "$grid"`

2094 ## . ./s4-blog.sh

2095 jg=`getpar joingrp`

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

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

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

2099 fi

2100 htmlheader=$layout/html.m4.html

2101 showgroup "$grid"

2102 }

2103 sql4interestblogs() {

2104 cat<<EOF

2105 CREATE TEMPORARY VIEW interestblogs AS

2106 SELECT blog.rowid rid, id, author

2107 FROM blog

2108 NATURAL JOIN

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

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

2111 THEN 1 -- blog owner is an user, READABLE when

2112 WHEN (SELECT user FROM grp_mem

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

2114 THEN 0

2115 ELSE 1

2116 END;

2117 EOF

2118 }

2119 listnewblogsql() { # $1=user

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

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

2122 newdays=${newdays:-14}

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

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

2125 WHERE user='$user'

2126 AND tblrowid IN

2127 ($blogreadflagrowid,

2128 $blogcutoffflagrowid)),

2129 $basetime -- "0"

2130 );"`

2131 cat<<EOF

2132 `sql4interestblogs`

2133 WITH article_ctime as (

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

2135 FROM article join article_s s using(id)

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

2137 GROUP BY id

2138 ), blog_title_owner as (

2139 SELECT blg.rid brid, id,

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

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

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

2143 ), blogall as (

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

2145 ), news as (

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

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

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

2149 FROM blogall bl

2150 LEFT JOIN

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

2152 ON bl.brid=al.tblrowid

2153 WHERE atime < bl.ctime

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

2155 LIMIT 10

2156 ) SELECT brid LINK, "新着",

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

2158 ctime, title,

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

2160 FROM news;

2161 EOF

2162 }

2163

2164 search_form() {

2165 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

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

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

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

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

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

2171 @week → 最近一週間

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

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

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

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

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

2177 auth=""

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

2179 case "$1" in

2180 author=*)

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

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

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

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

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

2186 ;;

2187 grid=*)

2188 a=`echo "${1#grid=}"`; a=$((0 + $a))

2189 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

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

2191 ;;

2192 esac

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

2194 cat<<-EOF

2195 <div class="right">

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

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

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

2199

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

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

2202

2203 </form>

2204 </div>

2205 EOF

2206 }

2207

2208 imgsrc_cache() (

2209 # $1 = directory for cache'ing

2210 # $2 = table (user_m or grp_m)

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

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

2213 dir="$1" tbl="$2"

2214 keycond="$3"

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

2216 ORDER BY rowid DESC LIMIT 1"

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

2218 tmpf=$tmpd/imgsrc_cache.$$

2219 case "$4" in

2220 [Ss]) size=S ;;

2221 [Oo]) size=O ;;

2222 *) size=M ;;

2223 esac

2224 # ImageCache filename storing schema:

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

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

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

2228 valtype=`query "$sql0"`

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

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

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

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

2233 case "$filetype" in

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

2235 esac

2236 fi

2237 cacheimg_S=$dir/S_$filename

2238 cacheimg_M=$dir/M_$filename

2239 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

2247 else

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

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

2250 fifo2=$fifo.2

2251 mkfifo $fifo $fifo2

2252 fmt=${filename##*.}

2253 ## [[ NOTE ]]

2254 ## a. convert oldimage newimage

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

2256 ## b is much smaller than a

2257 cat $tmpf | unhexize \

2258 | tee $fifo \

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

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

2261 | tee $fifo2 \

2262 | convert - "$cacheimg_M" &

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

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

2265 | convert - "$cacheimg_S" &

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

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

2268 echo '">'

2269 echo "$sum" > $sumfile

2270 fi

2271 ## Now preparing cache image, done.

2272 ## Store this information to DB

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

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

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

2276 query <<-EOF

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

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

2279 EOF

2280 )

2281

2282 showhome() {

2283 # $1=userRowIdToShow

2284 err showhome \$1=$1

2285 case "$1" in

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

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

2288 esac

2289 ## err ShowHome: uname=$uname

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

2291 gecos=`gecos "$uname"`

2292 ## err SH:gecos=$gecos

2293 GF_VIEWONLY=1

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

2295 search_form_args=""

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

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

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

2299 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2300 elif [ -n "$S4MASTERURL" ]; then

2301 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2302 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2303 fi

2304 usermenu="$usermenu

2305 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2306 # Display folders

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

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

2309 and type like 'file:%';"

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

2311 nfile=`query "$sql"`

2312 # err nfile=$nfile

2313 if [ $nfile -gt 0 ]; then

2314 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2315 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2316 fi

2317 else

2318 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2319 GROUP BY user;"`

2320 usermenu="<p>Last seen on $latestlog</p>"

2321 search_form_args="author=$uname"

2322 fi

2323 . ./s4-blog.sh

2324

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

2326 search_form "$search_form_args" > $sf

2327 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2328 | htmlescape > $tf

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

2330 viewtable $formdir/user.def user $1

2331 echo "</div>"

2332 } > $pf

2333

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

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

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

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

2338 if true; then

2339 tbl=user_m

2340 enticond="name='$uname'"

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

2342 else

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

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

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

2346 echo '">'

2347 }

2348 fi > $imf

2349 fi

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

2351 val='$uname';"`

2352 listblog $uname > $bf

2353

2354 hometail=$tmpd/tail.$$

2355 mkfifo $hometail

2356

2357 #Calling listgroupbytable, originally here

2358

2359 (

2360 # Display Most Recent Entry

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

2362 shortval=${shortval:-val}

2363

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

2365 # $hidden_mode is defined in global section

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

2367 ctime,

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

2369 (SELECT gecos FROM gecoses

2370 WHERE name=(SELECT val FROM blog_s

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

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

2373 FROM blog b

2374 JOIN

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

2376 FROM article a, article_s s

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

2378 GROUP BY blogid ORDER BY val DESC LIMIT 50

2379 ) m

2380 ON b.id=m.blogid

2381 AND

2382 ('$uname' = '$user' -- user can read all own posts

2383 OR

2384 -- exclude posts of others in quiz/enquete blogs

2385 NOT EXISTS (SELECT * FROM blog_s

2386 WHERE id=b.id AND key='mode' AND val IN $hidden_mode));"

2387 # This should be as follows

2388 : <<EOF

2389 WITH arts AS(

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

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

2392 FROM article a NATURAL JOIN article_s s

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

2394 GROUP by s.id

2395 )

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

2397 FROM arts a0

2398 JOIN

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

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

2401 ORDER BY ctime DESC LIMIT 50;

2402 EOF

2403

2404 cat<<-EOF

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

2406 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2407 <div class="lcto">

2408 `DT_VIEW=replyblog dumptable html blog`

2409 </div>

2410 EOF

2411 unset DT_SQL

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

2413 # Display NEWS

2414 # 2016-06-26

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

2416 acclog blog $blogreadflagrowid

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

2418 fi

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

2420 sql=`listnewblogsql "$user"`

2421 # echo "$sql" > tmp/listnew

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

2423 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2424 cont=$((cont-1))

2425 ##err newcount=$cont

2426 if [ $cont -gt 0 ]; then

2427 #echo "全体の新着記事${cont}傑" | html h2

2428 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2429 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2430 cat<<-EOF | html form 'action="?home"'

2431 `cgi_checkbox readchk yes 'id="read"'`<label

2432 for="read">新着ふくめて全部読んだことにする</label>

2433 `cgi_submit '確定'`

2434 `cgi_hidden read yes`

2435 EOF

2436 echo "$new10 "

2437 echo "</div>"

2438 else # If news is 0, set log cut off flag

2439 acclog blog $blogcutoffflagrowid # for speed

2440 fi

2441 else # Not My Home ($user != $uname)

2442 : # DT_SQL=

2443 fi

2444 ) > $hometail & # Is background call safe to m4??

2445 #

2446 listgroupbytable $formdir/grp.def "$cond" $uname |

2447 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2448 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2449 -D_PROFIMG_="spaste(\`$imf')" \

2450 -D_BLOGS_="spaste(\`$bf')" \

2451 -D_SEARCH_="spaste(\`$sf')" \

2452 -D_NBLOG_="$nblog" \

2453 -D_GROUPS_="syscmd(\`cat')" \

2454 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2455 $layout/html.m4.html $layout/home.m4.html

2456

2457 # Record access log

2458 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2459 }

2460 commission() { # $1=grp-rowid $2=user-rowid

2461 contenttype; echo

2462 ## err commission: "$@"

2463 gname=`getgroupbyid $1`

2464 echo "グループ $gname 管理者委任" \

2465 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2466 if [ -n "$2" ]; then

2467 grp_reg_adm "$@"

2468 else

2469 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2470 fi

2471 }

2472 listgroupbytable() {

2473 # $1=deffile $2...=condition $3(optional)=uname

2474 tagline=`grep :tag: $1`;

2475 and="${2:+and }" where=${2:+where }

2476 href="<a href=\"$myname?grp+"

2477 echo '<div class="listgroup">'

2478 sql="select val from grp_s where key='tag' $and$2 group by val;"

2479 ## err ListGRP: query sql="$sql"

2480 for tag in `query "$sql"`

2481 do

2482 ## err ListGrp: tag=$tag

2483 tn=${tagline%%=${tag}*}

2484 tn=${tn##*[ :]}

2485 sql="select rowid||':'||gname as 'グループ名',説明 from

2486 (select (select rowid from grp g where g.gname=grp_s.gname)

2487 as rowid,

2488 gname,

2489 max(case key when 'gecos' then val end) as '説明',

2490 max(case key when 'tag' then val end) as 'tag',

2491 max(case key when 'mtime' then val end) as mtime from grp_s

2492 $where$2 group by gname having tag='$tag' order by mtime desc);"

2493 ## err PersonalGroupList= `echo $sql`

2494 echo "<h2>$tn</h2>"

2495 echo '<table class="b listgroup">'

2496 sq -header -html $db "$sql" \

2497 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2498 echo '</table>'

2499 done

2500 if [ -n "$S4WORLDLIST" -a -n "$3" ]; then

2501 err "peekgrpworlds($user) BEGIN: `gdate +%S.%03N`"

2502 peekgrpworlds mem:"$3"

2503 err "peekgrpworlds($user) END: `gdate +%S.%03N`"

2504 fi

2505 echo '</div>'

2506 }

2507 iconhref() (

2508 # $1=icon-file, $2=Href $3=title $4...=anchor

2509 data=`percenthex "$1"`

2510 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2511 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2512 href=$2; title=$3; shift 3

2513 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2514 )

2515 iconhref2() (

2516 # $1=icon-file, $2=Href $3=title $4...=anchor

2517 src=$1

2518 href=$2; title=$3; shift 3

2519 anchor=`echo $@|htmlescape`

2520 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2521 )

2522 listentry() (

2523 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2524 # Referring variable $iamowner=$grp to attach owner-request links

2525 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2526 cond='' hiddens=''

2527 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2528 if [ -z "$offset" ]; then

2529 offset=`getpar start`; offset=${offset%%[!0-9]*}

2530 offset=$((offset-1))

2531 fi

2532 offset=$((offset + 0)) # change to numeric forcibly

2533 [ $offset -lt 0 ] && offset=0

2534 limit=$listentlimit

2535 dir=`getcachedir "$1"`

2536 if [ x"$1" = x"user" ]; then

2537 hrb="$myname?home"

2538 deficon=person-default.png

2539 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2540 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2541 gcs=gecos

2542 else # if group

2543 hrb="$myname?grp"

2544 deficon=group-default.png

2545 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2546 gcs=name

2547 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2548 if [ -n "$tagline" ]; then

2549 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2550 ## err tagconv=$tagconv

2551 fi

2552 fi

2553 if [ ! -d $dir ]; then

2554 mkdir -p $dir

2555 fi

2556 if [ ! -s $dir/$deficon ]; then

2557 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2558 fi

2559 kwd=${2:-`getpar kwd`}

2560 if [ -n "$kwd" ]; then

2561 kwd=`echo $kwd | tr -d '";\n' | tr -d "'"`

2562 case "$kwd" in

2563 mem:*@*)

2564 byuser=${kwd#*mem:}

2565 qusr=`sqlquote "$byuser"`

2566 cond1="(a.gname IN (SELECT gname FROM grp_mem WHERE user=$qusr))"

2567 ;;

2568 esac

2569 if [ x"$1" = x"group" ]; then

2570 if [ -n "$cond1" ]; then

2571 enthead="`gecos "$byuser"|htmlescape` さんの所属"

2572 else

2573 cond1="(b.name like '%${kwd}%')"

2574 fi

2575 else

2576 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2577 fi

2578 fi

2579 tag=`getpar tag` tag2=`getpar tag2`

2580 if [ x"$tag" = x"NULL" ]; then

2581 tag="" tag2=""

2582 fi

2583 if [ -n "$tag$tag2" ]; then

2584 tag=${tag:-$tag2}

2585 qtag=`sqlquote "$tag"`

2586 cond2="tag=$qtag"

2587 fi

2588 if [ -n "$cond1$cond2" ]; then

2589 cond="$cond1${cond2:+ AND $cond2}"

2590 cond="WHERE ${cond# AND }"

2591 fi

2592

2593 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2594 # grpは呼出し元の動的スコープ変数でよくないな...

2595 ##qgrp=`sqlquote $grp`

2596 getgrp="(select gname from grp where rowid=${rowid:--1})"

2597 sql="select a.rowid, a.$link,

2598 coalesce(b.$gcs, a.$nm) as nick,

2599 quote(a.$nm) as qname,

2600 (SELECT val FROM ${tbl}_s

2601 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2602 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2603 || case when a.$nm in (select gname from grp)

2604 then printf('(%d名)',

2605 (select count(user) from grp_mem where gname=a.$nm))

2606 else ' <'||a.$nm||'>'

2607 end

2608 as name,

2609 b.tag,

2610 case when a.$nm in (select user from grp_adm

2611 where gname=$getgrp) then '管理者'

2612 when '$user' in (select user from grp_adm where gname=a.$nm)

2613 then 'ADMIN'

2614 when '$user' in (select user from grp_mem where gname=a.$nm)

2615 then 'Member'

2616 when '$iamowner' = '' then ''

2617 else ',not='||a.rowid end as ownerlink,

2618 CASE '$entity'

2619 WHEN 'グループ'

2620 THEN coalesce(

2621 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2622 'open')

2623 ||

2624 CASE WHEN '$user'

2625 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2626 THEN ' ismember'

2627 ELSE ''

2628 END

2629 ELSE 'user'

2630 END regmode

2631 from $tbl a left join

2632 (select $nm as name,

2633 max(case key when 'gecos' then val end) as gecos,

2634 max(case key when 'tag' then val end) as tag,

2635 max(case key when 'mtime' then val end) as mtime,

2636 max(case key when 'wtime' then val end) as wtime,

2637 max(case key when 'login' then val end) as login

2638 from ${tbl}_s group by $nm)

2639 b on a.$nm=b.name $cond $3

2640 order by b.wtime desc, b.login desc,

2641 b.mtime desc, b.tag desc, a.rowid asc"

2642 # Give precedence to newer maintained groups (2016-09-24)

2643 # Note that mtime is stored only in grp_s.

2644 ## err LE:sql.1="$sql"

2645 total=`query "with x as ($sql) select count(*) from x;"`

2646 echo "$enthead${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2647 echo '<div class="listentry">' # List-entry div

2648 # Show owner/member filter button

2649 METHOD=GET

2650 hiddens="$hiddens

2651 `cgi_hidden stage \"$stage\"`"

2652 if [ x"$tbl" = x"grp" ]; then

2653 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2654 fh="<select name=\"tag\">$nl"

2655 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2656 for l in $args; do

2657 val=${l#*=} tname=${l%=*}

2658 if [ x"$val" = x"$tag" ]; then

2659 s=" selected"

2660 selectedtags="(種別[${tname}]のみ)"

2661 else

2662 s=""

2663 fi

2664 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2665 done

2666 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2667 cat<<-EOF

2668 <form action="$myname" method="$METHOD">

2669 </form>

2670 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2671 for="ismembtn">参加中以外隠す</label>

2672 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2673 for="isadmbtn">管理者参加以外隠す</label>

2674 EOF

2675 # limit=3

2676 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2677 fi

2678 if [ $total -gt $limit -o $ -n "$S4WORLDLIST" -a x"$tbl" = x"grp" $ ]; then

2679 echo '<div>'

2680 METHOD=GET cgi_form $stage <<EOF

2681 $form

2682 <label>次の語を含む${entity}で検索:

2683 `cgi_text kwd "$kwd"`</label>

2684 $hiddens

2685 EOF

2686 echo '</div>'

2687 else

2688 echo $selectedtags | html p

2689 fi

2690 test -n "$kwd" && hiddens="$hiddens$nl`cgi_hidden kwd \"$kwd\"`"

2691 cat<<EOF

2692 <form action="$myname" method="$METHOD">

2693 <p>${total}件中の<input class="hidesub" type="text" name="start"

2694 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2695 <input type="submit" value="確定"></p>

2696 </form>

2697 EOF

2698 if [ $((offset+limit)) -lt $total ]; then

2699 nextbtn=$(

2700 cat<<EOF

2701 <div class="right clear"><form action="$myname" method="$METHOD">

2702 `cgi_submit 次の${limit}件`

2703 $hiddens

2704 `cgi_hidden offset $((offset + limit))`</form></div>

2705 EOF

2706 )

2707 fi

2708 if [ $offset -gt 0 ]; then

2709 prevbtn=$(

2710 cat<<EOF

2711 <form action="$myname" method="$METHOD">

2712 `cgi_submit 前の${limit}件`

2713 $hiddens

2714 `cgi_hidden offset $((offset - limit))`</form>

2715 EOF

2716 )

2717 fi

2718 pnbtn="$nextbtn$prevbtn"

2719 echo $pnbtn

2720

2721 ## err ListEntry: `echo "$sql"\;`

2722 # sq $db here??? 2016-11-28

2723 query "$sql limit $limit ${offset:+offset $offset};" \

2724 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2725 # err name=$name owner=$ownerp lnk=$lnk

2726 # err newlnk=$lnk regmode=$regmode

2727 icondir=$dir/$id

2728 # Pick up only last icon

2729 htmlname=`echo $name|htmlescape`

2730 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2731 <p class=\"tag _$tag\">$tag</p>" \

2732 | _m4 $tagconv

2733 if [ -n "$NOSPEEDUP" ]; then

2734 files=`getvalbyid $tbl profimg $id $icondir`

2735 if [ -n "$files" ]; then

2736 icon=`echo "$files"|tail -1`

2737 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2738 else

2739 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2740 fi

2741 elif [ -n "$icon" -a -s "$icon" ]; then

2742 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2743 else

2744 cond="$nm=$qname"

2745 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2746 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2747 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2748 # err "img=[$img]"

2749 if [ -n "$img" ]; then

2750 echo "<a href=\"$hrb+$lnk\">"

2751 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2752 echo "</a>"

2753 else

2754 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2755 fi

2756 fi

2757 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2758 echo "</div>"

2759 done

2760 echo "</div>" # End of List-entry div

2761 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2762 if [ -n "$kwd" -a x"$tbl" = x"grp" -a -n "$S4WORLDS" ]; then

2763 peekgrpworlds "$kwd"

2764 fi

2765 )

2766 listmember() {

2767 listentry user "$@"

2768 }

2769 listgroup() {

2770 listentry group "$@"

2771 }

2772 hexteams() { # $1=gname, $2(optional)=user

2773 cond=${2:+" AND user='$2'"}

2774 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2775 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2776 }

2777 showgroup() { # $1=group-rowid

2778 if [ -z "$1" ]; then

2779 grid=`getpar grid`

2780 grid=${grid%%[!0-9]*}

2781 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2782 else

2783 grid=$1

2784 fi

2785 grp=`getgroupbyid $grid`

2786 qgrp=`sqlquote "$grp"`

2787 htmlgrp=`echo "$grp"|htmlescape`

2788 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2789 if isgroup "$grp"; then

2790 tf=$tmpd/title.$$

2791 sf=$tmpd/search.$$

2792 bodyclass=`query "SELECT val FROM grp_s

2793 WHERE gname=$qgrp AND key='regmode';"`

2794 if ismember "$user" "$grp"; then

2795 ismember="ismember"

2796 bodyclass="$bodyclass${bodyclass:+ }ismember"

2797 else

2798 ismember="" # bodyclass="group"

2799 fi

2800 bodyclass="$bodyclass grouphome"

2801 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2802 echo "グループ $htmlgrp" > $tf

2803

2804 showgroupsub $formdir/grp.def "$grid" | \

2805 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2806 -D_FORM_="syscmd(\`cat')" \

2807 -D_BODYCLASS_="$bodyclass" \

2808 -D_DUMPTABLE_="" \

2809 $htmlheader $sf $layout/form+dump.m4.html

2810 # $htmlheader $layout/form+dump.m4.html

2811 # $htmlheader is defined in grp()

2812 else # if $grp is removed at par2table

2813 listgroup

2814 fi

2815 }

2816 showgroupsub() {

2817 # $1=def-file $2=group-rowid

2818 # Using $ismember

2819 rowid=$2

2820 grp=`getgroupbyid $2`

2821 qgrp=`sqlquote "$grp"`

2822 td=`getcachedir grp/"$2"`

2823 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2824 if [ -z "$rowid" ]; then

2825 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2826 #grp=`sq $db "select gname from grp where rowid=$grp"`

2827 echo "showgroupsub: invalid argument($1 $2)" | html p

2828 return

2829 fi

2830 val=`getvalbyid grp profimg $rowid $tmpd`

2831 enticond="gname=$qgrp"

2832 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2833 if [ -n "$img" ]; then

2834 cat<<-EOF

2835 <p class="groupimg">

2836 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2837 EOF

2838 fi

2839 echo "<div class=\"noprofimg\">"

2840 viewtable $1 grp $rowid

2841 echo "</div>"

2842 if isgrpowner "$user" "$grp"; then

2843 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2844 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2845 iamowner=$rowid

2846 colmd=" mode"

2847 # We have to specify report-type blog to lshandoutall

2848 repblog=`query "

2849 WITH grpblogs AS (

2850 SELECT id FROM blog_s

2851 WHERE key='owner' AND val=(SELECT gname FROM grp WHERE rowid=$rowid)

2852 ), ownerMode AS (

2853 SELECT id,

2854 max(CASE key WHEN 'owner' THEN val END) owner,

2855 max(CASE key WHEN 'mode' THEN val END) mode

2856 FROM blog_s

2857 GROUP BY id

2858 ), blogid AS (

2859 SELECT id

2860 FROM grpblogs NATURAL LEFT JOIN ownerMode

2861 WHERE mode LIKE 'report%' LIMIT 1

2862 ) SELECT blog.rowid FROM blogid NATURAL LEFT JOIN blog;"`

2863 if [ -n "$repblog" ]; then

2864 csvid="gethandoutcsv"

2865 replink="/ <a href=\"?lshandoutall+$repblog#$csvid\">レポート全集計</a>"

2866 fi

2867 fi

2868 if [ -n "$ismember" ]; then

2869 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2870 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2871 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2872 cat<<-EOF

2873 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2874 href="?blog+$rowid">グループの新規話題作成</a>

2875 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2876 href="?grpaction+$rowid">メンバーを個別選択しての操作</a>

2877 $replink</p>

2878 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2879 <div class="fold clear">

2880 `cgi_checkbox send yes id="send"`<label

2881 for="send">グループ全員にメッセージ送信</label>

2882 <div>

2883 `cgi_textarea message "" "cols=60"`

2884 `cgi_submit 送信`

2885 `cgi_reset リセット`

2886 </div>

2887 `cgi_hidden grp $rowid`

2888 </div></form>

2889 EOF

2890 fi

2891 # 加入ボタン + 加入者リスト

2892 if [ -n "$ismember" ]; then

2893 ismem='checked' state="(参加中)"

2894 else

2895 nomem='checked' state="(現在非加入)"

2896 fi

2897 # このグループでの加入アドレス

2898 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2899 and key='email';"`

2900 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2901 ## and key='email';"

2902 ##err email=$eml

2903 cat <<EOF

2904 <div class="fold clear">

2905 `cgi_checkbox reg yes id="reg"`<label

2906 for="reg">自身の加入状態を操作する</label>$state

2907 <div>

2908 EOF

2909 cgi_form grp <<EOF

2910 <p>このグループに</p>

2911 <table class="b">

2912 <tr><th>メンバーとして</th><td>

2913 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2914 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2915 <tr><th>参加する場合のメイルアドレス<br>

2916 <small>(メインのアドレスとは違うものにする場合に記入<br>

2917 同じでよい場合は空欄に)</small></th>

2918 <td>`cgi_text email $eml`</td></tr>

2919 </table>

2920 `cgi_hidden grp $rowid`

2921 EOF

2922 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2923 echo "moderated (承認加入の)グループなので実際に参加できるのは

2924 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2925 fi

2926 echo '</div></div>'

2927 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2928 cat<<-EOF

2929 <div class="fold">

2930 <h2>話題一覧</h2>

2931 <form class="summary inline" action="$myname" title="$thelp">

2932 `cgi_hidden owner "$grp"`

2933 `cgi_hidden kwd "@week"`

2934 `cgi_hidden stage searchart`

2935 `cgi_submit "一週間のまとめ"`

2936 </form>

2937 <input type="checkbox" id="dtcheck" checked><label

2938 for="dtcheck">話題一覧表示</label>

2939 EOF

2940 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by 稼動状態, ctime desc"

2941 colstate="state:稼動状態:frozen=rowclass=凍結"

2942 frzbtn='<button class="toggle-frozen"></button>'

2943 if [ -n "$ismember" ]; then

2944 DT_COUNT=author DT_COUNT_HEADER="書" DT_COUNT_BY=$user

2945 fi

2946 DT_CHLD=article:blogid \

2947 DT_QOWNER="$qgrp" \

2948 DT_VIEW=replyblog dumptable html blog \

2949 "ctime title heading team notify:通知$colmd $colstate" "$cond" \

2950 | if [ -n "$iamowner" ]

2951 then

2952 sed -Ee "s,(<TD class=\"稼動状態\">).*(</TD>),\1$frzbtn\2,"

2953 else

2954 cat

2955 fi

2956 ## DO not convert to frzbtn when not admin

2957 echo "</div> "

2958

2959 getgname="(select gname from grp where rowid=$rowid)"

2960 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2961 cm="?commission+$rowid"

2962 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2963 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2964 # team list

2965 hexteams=`hexteams "$grp"`

2966 if [ -n "$hexteams" ]; then

2967 echo "チーム一覧" | html h2

2968 echo '<div class="dumptable"><table class="b">'

2969 sq $db -html -header<<-EOF

2970 SELECT val TEAM,

2971 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2972 MEMBERS

2973 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2974 EOF

2975 echo '</table></div>'

2976 fi

2977 }

2978 grp_getbodyclass() {

2979 # Get css class name for document.

2980 # `moderated' for moderated groups

2981 # `ismember' for groups where user belongs

2982 # $1=GroupName (w/o quote)

2983 # $user=userNameCurrentlyLogin

2984 ## err grp_getbodyclass: 1="$1"

2985 qgrp=`sqlquote "$1"`

2986 query<<-EOF

2987 SELECT coalesce(

2988 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2989 'open')

2990 ||

2991 CASE WHEN '$user'

2992 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2993 THEN ' ismember'

2994 ELSE ''

2995 END;

2996 EOF

2997 }

2998 grpaction() { # $1=group-rowid

2999 err GRP_ACTION:IN

3000 grid=${1:-`getpar grp`}

3001 grp=`getgroupbyid "$grid"`

3002 htmlgrp=`echo "$grp" | htmlescape`

3003 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3004 if [ -z "$grp" ]; then

3005 echo "無効な指定です。" | html p; return

3006 fi

3007 if ! ismember $user "$grp"; then

3008 echo "加入者のみに許可された操作です。" | html p; return

3009 fi

3010 isowner=""

3011 isgrpowner "$user" "$grp" && isowner="yes"

3012 err 2=$2 3=$3

3013 case "$2" in

3014 get:teamcsv)

3015 teamcsv "$grid"

3016 return

3017 ;;

3018 esac

3019 echo "グループ $grp 個別選択操作" \

3020 | _m4 -D_TITLE_="syscmd(\`cat')" \

3021 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

3022 $layout/html.m4.html

3023

3024 usel=`getpar usel`

3025 if [ -n "$usel" ]; then

3026 uids=$(echo `echo $usel`|tr ' ' ',')

3027 ## err grpaction-1: grp=$grp, `echo $sql`

3028 text=`getpar text|tr -d '\r'`

3029

3030 rm=`getpar rm` cfm=`getpar confirm`

3031 ## err rm=$rm cfm=$cfm

3032 if [ x"$rm" = x"yes" ]; then

3033 if [ "$isowner" ]; then

3034 if [ x"$rm$cfm" = x"yesyes" ]; then

3035 # Eliminate

3036 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

3037 for tbl in grp_mem grp_mem_s grp_mem_m; do

3038 sql="delete from $tbl $cond;"

3039 # echo "sql=$sql"

3040 query "$sql"

3041 err rmGRPuser "$sql"

3042 done

3043 num=`query "select count(*) from user where rowid in ($uids);"`

3044 #err num=$num

3045 if [ 0$num -gt 0 ]; then

3046 sql="select coalesce(b.val,a.name) from user a left join \

3047 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

3048 # err `echo "$sql"`

3049 html pre<<EOF

3050 以下の${num}名のグループ $grp 登録を解除しました。

3051 `query "$sql"`

3052 EOF

3053 fi

3054 else

3055 echo "確認のチェックがないのでやめておきます。" | html p

3056 return

3057 fi

3058 else # not Group Owner

3059 echo "グループ管理者でないのでメンバー操作はできません。" | html p

3060 return

3061 fi

3062 cat<<EOF

3063

3064 EOF

3065 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

3066 if [ -z "$text" ]; then # if msg is empty

3067 echo "なにかメッセージを..." | html p

3068 return 0

3069 fi

3070 gecos=`gecos $user`

3071 safegc=`echo "$gecos" | tr -d '<>@,'`

3072 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3073 fromad=`myemail4group "$grp"`

3074 ###mail_from="$safegc <$fromad>"

3075 mail_from="$safegc <$user>" # TEST: 2020/5/13

3076 test -n `getpar sender` &&

3077 export SENDER=$user # TEST: 2020/5/15

3078 replyto=$fromad

3079

3080 ## Start parse of attachment files

3081 if [ -n "`getpar email`" ]; then

3082 ar=`getpar supprcpt`

3083 if [ -n "$ar" ]; then

3084 for a in $ar; do

3085 if checkdomain "$a"; then

3086 supprcpt="$supprcpt $a"

3087 else

3088 err "SupprcptErr=[$a] by $user"

3089 removercpt="$removercpt $a"

3090 fi

3091 done

3092 fi

3093 subj=`getpar subject`

3094 afiles=""

3095 for fn in `query "SELECT DISTINCT val FROM par WHERE var='image' AND sessid='$session';"`

3096 do

3097 f=$tmpd/$fn

3098 if [ -s $f ]; then

3099 afiles=$afiles"${afiles:+ }$f"

3100 fi

3101 done

3102 else

3103 preface=$(cat <<-EOF

3104 $url

3105 のグループ「$grp」のメンバーである $gecos さんから、

3106 あなた宛のメッセージです。

3107 ----------------------------------------------------------

3108 EOF

3109 )

3110 fi

3111 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

3112 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

3113 subj="${subj:-$gecos さんからのメッセージ}"

3114 REPLYTO=$replyto

3115 MAIL_FROM=$mail_from

3116 export REPLYTO SMAIL_TO MAIL_FROM

3117 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

3118 for r in $rcpts; do

3119 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

3120 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

3121 else

3122 # Show sender and rcpts address for guest

3123 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

3124 fi

3125 if [ -n "$afiles" ];then

3126 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

3127 else

3128 smail "$r" "$subj"

3129 fi <<EOF

3130 ${preface:+$preface$nl}$text

3131 EOF

3132 done

3133 if [ $? = 0 ]; then

3134 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

3135 sql="select coalesce(b.val, a.name) from

3136 (select name from user where rowid in ($uids)) a

3137 left join user_s b on a.name=b.name and b.key='gecos';"

3138 html pre<<EOF

3139 `query "$sql"`

3140 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

3141 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

3142 EOF

3143 err SendDone: `echo $sql`

3144 fi

3145 elif [ x"$rm" = x"commission" ]; then

3146 grp_reg_adm $grid $usel

3147 elif [ x"$rm" = x"addteam" ]; then

3148 team=`getpar team|sed "s/'/''/g"` # for single quotation

3149 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

3150 if [ x"$team" != x"$newteam" ]; then

3151 echo "チーム名に使えない文字を除去しました" | html p

3152 team=$newteam

3153 fi

3154 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

3155 cat<<-EOF | html p

3156 有効なチーム名を入力してください。

3157 カンマだけ、「なし」という名前は使えません。

3158 EOF

3159 echo "有効なチーム名を入力してください。" | html p

3160 else

3161 grp_add_team $grid "$team" $usel

3162 fi

3163 elif [ x"$rm" = x"rmteam" ]; then

3164 if [ x"yes" = x"`getpar teamconfirm`" ]; then

3165 rmteam=`getpar rmteam|sed "s/'/''/g"`

3166 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

3167 gname='$grp' AND user='$user' AND key='team'\

3168 AND val='$rmteam';\"`" ]; then

3169 grp_rm_team $grid "$rmteam" $usel

3170 else

3171 echo "所属していないチームの除去操作はできません。"|html p

3172 fi

3173 else

3174 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

3175 fi

3176 fi

3177 fi

3178 # POST count summary

3179 from=`getpar from`; to=`getpar to`

3180 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3181 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3182 fromtonote="<p title=\"Count the Number of Posts from-to\">POST/ACCESS集計: $from_input - $to_input</p>"

3183 # New entry

3184 sql="WITH mems AS (

3185 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3186 ON gm.user=g.name

3187 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3188 ), target_article AS (

3189 SELECT id FROM article_s

3190 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3191 ), posts AS (

3192 SELECT author, count(author) post

3193 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3194 WHERE blogid IN (SELECT id FROM blog_s

3195 WHERE key='owner'

3196 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3197 AND key='text'

3198 GROUP BY author

3199 ), teams AS (

3200 SELECT user, group_concat(val, ', ') team

3201 FROM mems m LEFT JOIN grp_mem_m gm ON m.name=gm.user

3202 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3203 AND key='team'

3204 GROUP BY user

3205 ), user_post AS (

3206 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3207 FROM mems m LEFT JOIN posts

3208 ON m.name=posts.author

3209 GROUP by m.rowid

3210 ), user_view AS (

3211 SELECT user vuser,count(user) cnt

3212 FROM tblaccesses

3213 WHERE user IN (

3214 SELECT user FROM grp_mem

3215 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid))

3216 AND tbl='blog'

3217 AND tblrowid IN (

3218 SELECT rowid FROM blog

3219 WHERE id IN (

3220 SELECT id FROM blog_s

3221 WHERE key='owner' AND val=(

3222 SELECT gname FROM grp WHERE rowid=$grid)))

3223 AND time BETWEEN '${from:-0000}' AND '${to:-9999}'

3224 GROUP BY user ORDER BY cnt

3225 )

3226 SELECT

3227 CASE

3228 WHEN (SELECT user FROM grp_adm

3229 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3230 AND user=up.name) IS NOT NULL

3231 then 'k'

3232 ELSE ''

3233 END || rowid || ','

3234 || rtrim(substr(name, 1, instr(name, '@')), '@') UID,

3235 gecos NAME,

3236 post POST,

3237 (coalesce((SELECT cnt FROM user_view WHERE vuser=name), 0)) ACCESS,

3238 team _TEAM_

3239 FROM user_post up LEFT JOIN teams t

3240 ON up.name=t.user

3241 ORDER BY up.name;"

3242 ## Want to count ACCESS with JOIN, but failed to produce user-unique rows

3243 ## err grpaction: "`echo \"$sql\"`"

3244 tf=$tmpd/title.$$

3245 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3246 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3247 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3248 for=\"cmadmin\">管理者委任</label>

3249 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3250 </p></div>"

3251 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3252 title=\"Shortcut: g${nl}Remove from the Group\"

3253 for=\"conf\">GRP登録解除</label>

3254 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3255 <p>この操作による通知は本人に行きません。

3256 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3257 </div>"

3258 submitBTN=`cgi_submit 送信`

3259 # Get team list to which current user belongs into $hexteams

3260 allhexteams=$(hexteams "$grp")

3261 if [ -n "$isowner" ]; then

3262 myhexteams="$allhexteams" # admin can remove all teams' attr

3263 else

3264 myhexteams=$(hexteams "$grp" "$user")

3265 fi

3266 if [ -n "$isowner" -a -n "$allhexteams" ]; then

3267 gettingcsv="<p>Download: <a href=\"?getteamcsv+$grid\">Team.csv</a> (Zoom Breakout Room 事前割り当てに使えます), <a href=\"?getteamcsv+$grid+name\">Team-with-name.csv</a>(参照用名前付)</p>"

3268 fi

3269 if [ -n "$myhexteams" ]; then

3270 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3271 title=\"Shortcut: s${nl}Strip a team tag from\"

3272 for=\"cmrmteam\">チーム属性除去</label>

3273 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3274 を除去します: `cgi_checkbox teamconfirm yes` 確認 $submitBTN

3275 <p>この操作による通知は本人に行きません。

3276 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3277 </div>

3278 "

3279 fi

3280 stf=$tmpd/selteam.$$

3281 cgi_select_h selteam "5445414d" $allhexteams > $stf

3282 b1='<label> <input type="checkbox" name="usel" value="'

3283 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3284 br='<span id="reverse" title="Reverse Selection"></span>'

3285 # lnk='"> <span></span></label> [<a href="?home+\3">HOME</a>]'

3286 lnk='<a href="?home+\3">\5</a>'

3287 # (1) Join <TR> line and the next

3288 # (2) (<TR><TD)>(k?)(1234),(userid)</TD><TD>(GECOS)</TD>

3289 # ↓

3290 # <TR><TD>\2<label><input ...value="\3">\4</label></TD> \

3291 # <TD><a href="?home+\3">\5</a></TD>

3292 cgi_form grpaction<<EOF \

3293 | sed -e '/^<TR>/{; N; s/\n//; }' \

3294 | sed -E \

3295 -e "s|^(<TR><TD>)(k?)([0-9]*),(.*)</TD><TD>(.*)</TD>|\1\2$b1\3\">\4</label></td><td>$lnk</TD>|" \

3296 -e 's/^(<TR><TD[^>]*>)k(<label)/\1\2 class="admin"/' \

3297 -e "s|^(<TR><TH>)(UID)|\1$br \2|" \

3298 | _m4 -D_TITLE_="spaste(\`$tf')" \

3299 -D_SUBTITLE_="チェック後操作ボタン" \

3300 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3301 $layout/form+dump.m4.html \

3302 | _m4 -D_TEAM_="spaste(\`$stf')"

3303 <p>下でチェックした人を対象として:</p>

3304 <div class="foldtabs">

3305 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3306 title="Shortcut: a${nl}Add a team tag to"

3307 for="cmteam">同じチーム属性を付与</label>

3308 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3309 `cgi_datalist_h teams $allhexteams`$submitBTN

3310 </div>

3311 ${rmteammsg}

3312 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3313 title="Shortcut: d${nl}DirectMail to"

3314 for="sendmsg" title="Direct Message">DM送信</label>

3315 <div>

3316 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3317 title="Using email format">email書式を使う</label>

3318 <div class="folded">

3319 <table>

3320 <tr><td>From: </td><td>$user</td></tr>

3321 <tr><td>このFrom:で送る</td>

3322 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3323 </td></tr>

3324 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3325 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3326 <tr><td>ファイル添付: </td>

3327 <td>`cgi_file image "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3328 </table>

3329 <p>(下記一覧から1人以上選択していない場合は送れません$submitBTN)</p>

3330 </div>

3331 <div>本文:`cgi_textarea text "" cols=72`

3332 </div>

3333 </div>

3334 ${isowner:+$cmmsg$excmsg}

3335 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3336 </div>

3337 <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote

3338 <table class="td3r td4r thl">

3339 `sq $db -header -html "$sql"`

3340 </table>

3341 `cgi_hidden grp $grid`

3342 `cgi_hidden myuid $myuid id="myuid"`

3343 EOF

3344 }

3345 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3346 # Create TEMPORARY VIEW

3347 query<<EOF

3348 CREATE TEMPORARY VIEW writeusers AS

3349 SELECT DISTINCT author FROM article

3350 WHERE id in (

3351 select id from article where blogid=(select id from blog where rowid=$1)

3352 );

3353 CREATE TEMPORARY VIEW movablegroups AS

3354 SELECT g.rowid growid , g.gname

3355 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3356 ON grp.gname=gm.gname -- そのユーザが属している

3357 AND user='$user') g -- グループに絞る

3358 WHERE (SELECT author FROM writeusers

3359 EXCEPT

3360 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3361 IS NULL;

3362 $2

3363 EOF

3364 }

3365 sql4readableblogs() {

3366 # Create view of blogs that can be readable by $user

3367 # Blog is readable when:

3368 # 1: blog owner is an user

3369 # 2: else, 2.1: owner-group where the $user belongs

3370 # 2.2: else, owner-group is not moderated

3371 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3372

3373 # $hidden_mode is defined in global section at head

3374

3375 cat<<EOF ## | tee tmp/sql.out

3376 CREATE TEMPORARY VIEW readableblogs AS

3377 SELECT blog.rowid rid, id, author

3378 FROM blog

3379 NATURAL JOIN

3380 (SELECT id,

3381 max(CASE key WHEN 'owner' THEN val END) owner,

3382 max(CASE key WHEN 'mode' THEN val END) mode

3383 FROM blog_s GROUP by id) bs

3384 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3385 THEN -- blog owner is an user, READABLE

3386 NOT mode IN $hidden_mode

3387 WHEN (SELECT val FROM grp_s

3388 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3389 AND

3390 (SELECT user FROM grp_mem

3391 WHERE gname=bs.owner AND user='$user') IS NULL

3392 THEN 0

3393 WHEN mode IN $hidden_mode

3394 THEN 0 -- "quiz" mode blog cannot be searched

3395 ELSE 1

3396 END;

3397 EOF

3398 }

3399 mvteamform() {

3400 owner=$1

3401 hexteams=$(hexteams "$owner" "$user")

3402 test -z "$hexteams" && return

3403 none="`echo なし|hexize`"

3404 cat<<-EOF

3405 <!-- <div class="fold">

3406 `cgi_checkbox mv2team send id="mv2team"`<label

3407 for="mv2team">この話題を以下のチームのものにする</label>

3408 <div> -->

3409 <p>この話題をチーム所有にする:

3410 チーム: `cgi_select_h mv2team $none $hexteams`</p>

3411 </form></div></div>

3412 EOF

3413 }

3414 editheading() { # $1=rowid-of-heading

3415 rowid=${1%%[!A-Z0-9a-z_]*}

3416 if [ -z "$rowid" ]; then

3417 echo "話題番号が未指定です。" | html p

3418 return

3419 fi

3420 owner=`getvalbyid blog owner $rowid`

3421 title=`getvalbyid blog title $rowid`

3422 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3423 | _m4 -D_TITLE_="修正" \

3424 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3425 -D_BLOGS_="" -D_DUMPTABLE_="" \

3426 -D_FORM_="syscmd(\`cat')" \

3427 $layout/html.m4.html $layout/form+dump.m4.html

3428 # Move to group

3429 if isuser "$owner"; then

3430 crview4article $rowid

3431 n=`query "SELECT count(*) FROM writeusers;"`

3432 ## err N=$n

3433 if [ $((n)) -gt 0 ]; then

3434 ## err ROWID=$rowid

3435 sql="SELECT growid || ':' || gname FROM movablegroups;"

3436 cat<<-EOF

3437 <div class="fold">

3438 `cgi_checkbox mv send id="mv"`<label

3439 for="mv">この話題をグループ所有に移動する</label>

3440 <div>

3441 <form action="?mvart" method="POST" enctype="multipart/form-data">

3442 移動先グループ:

3443 <select name="mv2grp">

3444 EOF

3445 query ".mode html"

3446 query<<-EOF |

3447 $sql

3448 .mode list

3449 EOF

3450 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3451 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3452 cat<<-EOF

3453 </select>

3454 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3455 そのグループに加入しているものに限られます)</p>

3456 <p>`cgi_checkbox cfm yes`<label>確認

3457 (この操作は元に戻すことができません)</label></p>

3458 `cgi_hidden blogrowid $rowid`

3459 `cgi_submit 移動`

3460 `cgi_reset Reset`

3461 </form>

3462 </div>

3463 </div>

3464 EOF

3465 fi

3466 # end of isuser "$owner"

3467 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3468 [ -n "$hexteams" ];}; then

3469 none="`echo なし|hexize`"

3470 cat<<-EOF

3471 <div class="fold">

3472 `cgi_checkbox mv2team send id="mv2team"`<label

3473 for="mv2team">この話題を以下のチームのものにする</label>

3474 <div><p>現在の所属チーム設定:

3475 `query "SELECT

3476 coalesce((SELECT val FROM blog_s

3477 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3478 AND key='team'),

3479 ':なし');"`</p>

3480 <form action="?mvart" method="POST" enctype="multipart/form-data">

3481 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3482 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3483 `cgi_hidden blogrowid $rowid`<br>

3484 `cgi_submit 移動`

3485 `cgi_reset Reset`

3486 </form></div></div>

3487 EOF

3488 fi

3489 }

3490 mvart() { # move diary to some group or team

3491 # or move blog of group to team which belong to the group

3492 blogrowid=`getpar blogrowid`

3493 cfm=`getpar cfm`

3494 ##### echo move blog:$blogrowid to $mv2grp | html p

3495 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3496 . ./s4-blog.sh

3497 if [ -z "$blogrowid" ]; then

3498 echo "無効な指定です(mvart)。" | html p

3499 return

3500 elif [ x"$cfm" != x"yes" ]; then

3501 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3502 elif { mv2grp=`getpar mv2grp`

3503 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3504 [ -n "$mv2grp" ]; }; then

3505 crview4article $blogrowid

3506 ########## TRANSACTION BEGIN

3507 query "BEGIN;"

3508 n=`query "SELECT count(*) FROM writeusers;"`

3509 ## err Nwriteuser=$n

3510 if [ $((n)) -gt 0 ]; then

3511 query<<-EOF

3512 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3513 WHERE key='owner'

3514 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3515 AND $mv2grp IN (SELECT growid FROM movablegroups);

3516 EOF

3517 fi

3518 query "END;"

3519 ########## TRANSACTION END

3520 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3521 [ -n "$mv2team" ];}; then

3522 # blog owner can move it to ANY team

3523 case "$mv2team" in

3524 'なし')

3525 cat<<-EOF

3526 DELETE FROM blog_s

3527 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3528 AND key='team';

3529 EOF

3530 ;;

3531 "") ;;

3532 *)cat<<-EOF

3533 BEGIN;

3534 REPLACE INTO blog_s(id, key, val)

3535 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3536 'team', '$mv2team');

3537 REPLACE INTO blog_s(id, key, val)

3538 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3539 'notify', 'all'); -- Change notify to all

3540 END;

3541 EOF

3542 esac | query

3543 fi

3544 blog_reply $blogrowid

3545 echo yes | html p

3546 }

3547 editart() { # $1=article-rowid $2=blogrowid

3548 rowid=${1%%[!A-Z0-9a-z_]*}

3549 blogrowid=${2%%[!A-Z0-9a-z_]*}

3550 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3551 echo "表示する記事番号が未指定です。" | html p

3552 return

3553 fi

3554 owner=`getvalbyid blog owner $blogrowid`

3555 title=`getvalbyid blog title $blogrowid`

3556 author=`getvalbyid article author $rowid`

3557 math=`getvalbyid blog mathjax $blogrowid`

3558 ## err EDITart: owner=$owner, author=$author

3559 if isgrpowner "$user" "$owner"; then

3560 : EDIT OK

3561 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3562 echo "本人か所有者しか編集できません." | html p

3563 return

3564 fi

3565 aid=`query "select id from article where rowid=$rowid;"`

3566 tmpout=$tmpd/editart.$$.out

3567 GF_ACTION="?replyblog+$blogrowid#$aid" \

3568 edittable $formdir/article.def article $rowid \

3569 > $tmpout

3570 printf '%s' "${math:+$mathjax}" >> $tmpout

3571 # Cannot use pipelining to m4 with genform() because of stdin stack

3572 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3573 -D_FORM_="syscmd(cat $tmpout)" \

3574 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3575 -D_BLOGS_= -D_DUMPTABLE_= \

3576 $layout/html.m4.html $layout/form+dump.m4.html

3577 }

3578 send2mem() {

3579 rowid=`getpar grp`

3580 rowid=${rowid%%[!0-9]*} # Cleaning

3581 if [ -z "$rowid" ]; then

3582 echo "グループが未指定です。" | html p

3583 return

3584 fi

3585 message=`getpar message`

3586 if [ -z "$message" ]; then

3587 echo "文章を入れてください。" | html p

3588 return

3589 fi

3590 grp=`getgroupbyid $rowid`

3591 members=`collectemail "$grp"`

3592 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3593 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3594 mailfrom="`gecos "$user"` <$mailfrom>"

3595 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3596 msg=$(cat<<-EOF

3597 $urlbase?grp+$rowid

3598 グループ $grp に所属する

3599 `gecos $user` さんよりメッセージ:

3600

3601 $message

3602 EOF

3603 )

3604 # smail rcpt subj (file)

3605 for m in $members; do

3606 echo "$msg" |

3607 MAIL_FROM=$mailfrom \

3608 SENDER=$noreply \

3609 REPLYTO=$mailfrom \

3610 smail "$m" "$sj"

3611 done

3612 cat<<EOF

3613 <p>以下のユーザに送信しました。</p>

3614 <pre>

3615 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3616 </pre>

3617 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3618 EOF

3619 }

3620 joingrpadmit() {

3621 # $1=yes/no $2=session-key

3622 if [ -z "$2" ]; then

3623 echo "bye bye" | html p; return

3624 fi

3625 t_usr=`session=$2 getpar adduser`

3626 t_grp=`session=$2 getpar group`

3627 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3628 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3629 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3630 echo "無効な加入依頼です。" | html p

3631 echo "有効期限が切れたか、

3632 他の管理者がいる場合は処理済みの可能性があります。" | html p

3633 return

3634 fi

3635 if ! isgrpowner "$user" "$t_grp"; then

3636 echo "グループ管理者のみの機能です。" | html p; return

3637 fi

3638 case $1 in

3639 yes) joingrp "$t_grp" "$t_usr" yes ;;

3640 no) joingrp "$t_grp" "$t_usr" no ;;

3641 *)

3642 echo "無効な指定です($1)。" | html p

3643 return ;;

3644 esac

3645 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3646 rcpts="`getgroupadminmails "$t_grp"` $user"

3647 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3648 body="に

3649 $t_usr

3650 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3651 しました。"

3652 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3653 query "delete from session where id='$2';"

3654 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3655 }

3656

3657 joingrprequest() {

3658 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3659 jss="joingrp-`date +%s`-`genrandom 12`"

3660 gecos=`gecos "$user"`

3661 addsession $jss +${memoplimitdays}days

3662 grpadmins=`getgroupadmins "$1"`

3663 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3664 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3665 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3666 $url

3667 $user ${gecos:+($gecos)}さんから

3668 グループ $1

3669 に加入依頼がありました。

3670

3671 承認する:

3672 $urlbase?joingrpadmit+yes+$jss

3673

3674 白紙に戻す:

3675 $urlbase?joingrpadmit+no+$jss

3676 EOF

3677 echo "管理者に加入依頼を出しました。

3678 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3679 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3680 }

3681 joingrp() {

3682 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3683 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3684 if isgrpowner "$user" "$1"; then

3685 isowner="yes"

3686 elif [ -n "$5" ]; then

3687 isowner="yes"

3688 else

3689 isowner=""

3690 fi

3691 ## err jg:isgrpowner: isowner="$isowner"

3692 if [ -n "$isowner" ]; then

3693 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3694 elif [ x"$2" != x"$user" ]; then # if user is not login user

3695 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3696 return

3697 elif [ x"$3" = x"no" ]; then

3698 : # Do not pursue those who leave

3699 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3700 : # Member can change own email address for the joining moderated group

3701 else # adding user is $user itself

3702 case `getgroupattr "$1" regmode` in

3703 moderated)

3704 joingrprequest "$@" # Request only

3705 return

3706 ;;

3707 *)

3708 ;;

3709 esac

3710 fi

3711 qgname=`sqlquote "$1"`

3712 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3713 cond="where gname=$qgname and user='$2'"

3714 if [ x"$3" = x"yes" ]; then

3715 query "replace into grp_mem values($qgname, '$2');"

3716 # Notify joingrp to admin

3717 action="に加入しました。"

3718 if [ -n "$4" ]; then

3719 if msg=`emaildomaincheck "$4"`; then

3720 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3721 'string', '$4', NULL);"

3722 else

3723 echo $msg

3724 fi

3725 else

3726 query "delete from grp_mem_s $cond and key='email';"

3727 fi

3728 if [ -n "$5" ]; then # as ADMIN

3729 # Coming here means newly created group

3730 sql="select case\

3731 when (select count(*) from grp_mem where gname=$qgname)=1\

3732 then (select user from grp_mem\

3733 where gname=$qgname and user='$user')\

3734 else '' end; "

3735 err NewGrpChk: $sql

3736 if [ -n "`query \"$sql\"`" ]; then

3737 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3738 query "replace into grp_adm values($qgname, '$user');"

3739 fi

3740 fi

3741 else

3742 query "begin;

3743 delete from grp_mem $cond;

3744 delete from grp_mem_s $cond;

3745 delete from grp_mem_m $cond;

3746 delete from grp_adm $cond;

3747 delete from grp_adm_s $cond;

3748 delete from grp_adm_m $cond;

3749 end;"

3750 action="から脱退しました。"

3751 fi

3752 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3753 $url?grp+$grid

3754 $user (`gecos $user`)さんが

3755 グループ $1

3756 $action

3757 EOF

3758 }

3759 grp_add_team() (

3760 # $1=grp-rowid $2=team $3...=user-rowid(s)

3761 grp=`getgroupbyid $1`

3762 team=$2; shift; shift

3763 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3764 { echo "BEGIN;"

3765 for user; do

3766 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3767 '$grp',\

3768 (SELECT name FROM user WHERE rowid=$user),\

3769 'team', 'string', '$team');"

3770 done

3771 echo "END;"

3772 } | query

3773 )

3774 grp_rm_team() (

3775 # $1=grp-rowid $2=team $3...=user-rowid(s)

3776 grid=$1

3777 qgrp=$(sqlquote "`getgroupbyid $grid`")

3778 team=$2; shift; shift

3779 [ -z "$grid" -o -z "$team" ] && return

3780 { echo "BEGIN;"

3781 for user; do

3782 echo "DELETE FROM grp_mem_m\

3783 WHERE gname=$qgrp \

3784 AND user=(SELECT name FROM user WHERE rowid=$user)\

3785 AND key='team' AND val='$team';"

3786 done

3787 cat<<-EOF

3788 DELETE FROM blog_s

3789 WHERE rowid=(

3790 SELECT rowid

3791 FROM blog_s a

3792 WHERE key='team'

3793 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3794 AND NOT EXISTS (SELECT * FROM grp_mem_m

3795 WHERE key='team' AND val=a.val -- a.val=team

3796 AND gname = (SELECT val FROM blog_s b

3797 WHERE a.id=b.id AND key='owner')

3798 ));

3799 EOF

3800

3801 echo "END;"

3802 } | query

3803 )

3804 grp_reg_adm() {

3805 # $1=grp-rowid $2...=user-rowid

3806 grid=$1

3807 grp=`getgroupbyid "$1"`

3808 if [ -z "$grp" ]; then

3809 echo "無効なグループIDです" | html p; return

3810 fi

3811 if ! isgrpowner "$user" "$grp"; then

3812 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3813 fi

3814 shift

3815 for urid; do

3816 newadm=`query "select name from user where rowid=$urid;"`

3817 if [ -z "$newadm" ]; then

3818 echo "指定ユーザIDがおかしいようです。" | html p; return

3819 fi

3820 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3821 err ismember $newadm $grp

3822 if ismember $newadm "$grp"; then

3823 # OK, go ahead

3824 getgname="(select gname from grp where rowid=$grid)"

3825 query "replace into grp_adm values($getgname, '$newadm');"

3826 # confirm insertion

3827 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3828 if [ -n "`query \"$sql;\"`" ]; then

3829 echo "追加完了: $newadm" | html p

3830 else

3831 echo "追加失敗($1 $urid)" | html p

3832 fi

3833 fi

3834 showgroup $grid

3835 done

3836 }

3837 dt_colhack() {

3838 # FROM: <TD>xxx:::yyy</TD>

3839 # TO: <TD class="xxx">yyy</TD>

3840 sed -Ee 's,<TD>([^:<"]+)'$asdelim'([^<]*)(</TD>|$),<TD class="\1">\2\3,g'

3841 }

3842 dt_rowhack() {

3843 # From: <TR>

3844 # ....

3845 # <TD>rowclass=foo</TD>

3846 # </TR>

3847 # To: <TR class="foo">....<TD>foo</TD></TR>

3848 sed -e '

3849 /^<TR>/ {

3850 :loop

3851 s/\n//

3852 N

3853 /<\/TR>/ {

3854 s/\n//

3855 s,^<TR>$.*$<TD$[^>]*$>rowclass=$.*$$</TD></TR>$,<TR class="\3">\1<TD\2>\3\4,

3856 n

3857 }

3858 $q

3859 b loop

3860 }'

3861 }

3862 dumptable() {

3863 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3864 # textのフィールドだけ全てダンプにしたほうがいいか

3865 # $DT_VIEW sets link

3866 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3867 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3868 VIEW=${DT_VIEW-replyblog}

3869 if [ -n "$VIEW" ]; then

3870 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3871 fi

3872 sqlfile=$tmpd/dump.sql

3873 : > $sqlfile # ensure to be empty

3874 printf '.mode html\n.header 1\n' > $sqlfile

3875 # $DT_CHLD=ChildTable:BindColumn (eg. article:blogid)

3876 if [ -n "$DT_CHLD" ]; then

3877 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3878 cat<<-EOF >> $sqlfile

3879 -- presql

3880 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3881 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3882 EOF

3883 # Speed up counting of new articles

3884 dt_count=${DT_COUNT:+"CREATE TEMPORARY VIEW _dtcount AS

3885 SELECT a.id $_i, $DT_COUNT, coalesce(cnt2, 0) cnt2

3886 FROM (SELECT DISTINCT id FROM _target) a

3887 LEFT JOIN

3888 (SELECT blogid,$DT_COUNT,count($DT_COUNT) cnt2

3889 FROM $_t

3890 WHERE $DT_COUNT = '$DT_COUNT_BY'

3891 GROUP BY $_i,$DT_COUNT) b

3892 ON a.id=b.$_i

3893 ;"}

3894 cat<<-EOF >> $sqlfile

3895 -- presql2

3896 DROP TABLE IF EXISTS _counts;

3897 CREATE TEMPORARY TABLE _counts AS

3898 SELECT $_i, count($_i) cnt

3899 FROM $_t GROUP BY $_i;

3900 /* Prepare NEW count table */

3901 CREATE TEMPORARY TABLE _target AS

3902 SELECT b.rowid trowid, b.id

3903 FROM "$2" b JOIN "$2_s" s

3904 ON b.id=s.id AND s.key='owner'

3905 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3906

3907 DROP TABLE IF EXISTS _children;

3908 CREATE TEMPORARY TABLE _children AS

3909 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3910 FROM (SELECT t.trowid, t.id $_i, a.id

3911 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3912 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3913

3914 DROP TABLE IF EXISTS _news;

3915 DROP VIEW IF EXISTS _news;

3916

3917 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3918 -- INSERT INTO _news

3919 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3920 CREATE TEMPORARY VIEW _news AS

3921 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3922 FROM (SELECT DISTINCT id FROM _target)

3923 a LEFT JOIN

3924 (SELECT $_i, count(ctime) newcnt

3925 FROM _children x

3926 WHERE ctime > coalesce((SELECT time from myacclog

3927 WHERE tblrowid=x.trowid),

3928 '1970-01-01')

3929 GROUP BY $_i) b

3930 ON a.id=b.$_i;

3931 $dt_count

3932 EOF

3933 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3934 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3935 cntmine=${DT_COUNT:+"(SELECT cnt2 FROM _dtcount WHERE $_i=a.id)"}

3936 cnt="$cntnew as '新着', $cntall as '総数', "

3937 cnt=$cnt${DT_COUNT:+"$cntmine as '$DT_COUNT_HEADER',"}

3938 dt_class=" td2r td3r td4r dumpblogs"

3939 fi

3940 # Construct join expression

3941 eav="" scols=""

3942 pk=`gettblpkey $2`

3943 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3944 substr=${substr:-%s}

3945 for col in ${3:-`gettbl_s_cols $2`}; do

3946 valvar=val

3947 fromtbl=b

3948 if gettblcols "$2" | grep -w "$col" >/dev/null 2>&1; then

3949 # If $col belongs to master table

3950 fromtbl=a; col=${col#a.}

3951 fi

3952 case $col in

3953 gecos) scols="$scols${scols:+, }${col#}"

3954 continue ;; # built-in column name

3955 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3956 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3957 case "$as" in

3958 *:*=*) cnd=${as#*:}

3959 h=${cnd%%=*} v=${cnd#*=}

3960 h=`sqlquotestr "$h"`

3961 v=`sqlquotestr "$v"`

3962 if [ x"$fromtbl" = x"b" ]; then

3963 valvar="CASE val WHEN $h THEN $v END"

3964 else

3965 valvar="$h"

3966 fi

3967 as=${as%%:*} ;;

3968 esac

3969 ;;

3970 *) as=${col} ;;

3971 esac

3972 ss=`printf "$substr" "$valvar"`

3973 if [ x"$fromtbl" = x"b" ]; then

3974 eav=$eav${eav:+,}" \"$as$asdelim\"||coalesce(max(case key when '$col' then $ss end), '') as $as"

3975 else

3976 eav=$eav${eav:+,}" \"$as$asdelim\"||$ss as $as"

3977 fi

3978 scols="$scols${scols:+, }${fromtbl}.$as"

3979 done

3980 #case author when '$user' then a.rowid else '---' end as ID,

3981 if [ -n "$DT_SQL" ]; then

3982 echo "$DT_SQL"

3983 else

3984 cat<<-EOF

3985 SELECT a.rowid as LINK, $cnt $scols

3986 FROM $2 a LEFT JOIN

3987 (SELECT $pk,$eav,

3988 max(CASE key

3989 WHEN 'owner'

3990 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3991 as gecos

3992 FROM ${2}_s c GROUP BY $pk)

3993 b ON a.$pk=b.$pk $4;

3994 EOF

3995 fi >> $sqlfile

3996 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3997 sqlog<<-EOF

3998 *** SQL-file: $sqlfile ***

3999 `cat $sqlfile`

4000 EOF

4001 printf '.mode list\n.header 0\n' >> $sqlfile

4002 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_colhack | dt_rowhack

4003 <div>

4004 <div class="dumptable">

4005 <table class="b$dt_class">

4006 `query ".read $sqlfile"`

4007 </table>

4008 </div>

4009 </div>

4010 EOF

4011 }

4012

4013 clean-orphaned() {

4014 # This shoud be done by foreign_key rules, but some db lack them

4015 query<<-EOF

4016 -- Find blogs that have no parent

4017 WITH orphanedblog AS (

4018 SELECT blog.id,val FROM blog JOIN blog_s bs

4019 ON blog.id=bs.id AND key='owner'

4020 WHERE val NOT IN (SELECT gname FROM grp)

4021 AND val NOT IN (SELECT name FROM user)

4022 ) -- Remove them

4023 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

4024

4025 -- Find articles that have no parent blog

4026 WITH orphanedarticle AS (

4027 SELECT id FROM article

4028 WHERE blogid NOT IN (SELECT id FROM blog)

4029 ) -- Remove them

4030 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

4031 EOF

4032 }

4033

4034 clearcachedir() (

4035 td=`getcachedir "$1"`

4036 err td=$td: ls- `ls $td`

4037 if [ -w "$td/image.1" ]; then

4038 err Removing td=$td

4039 rm -fr $td/ # Clear icon-image cache!

4040 fi

4041 )

4042

4043 par2table() (

4044 # copy current parameters of par into destination table

4045 # $1=definition-file

4046 # Using $user and $session

4047 # Return value:

4048 # 0: Stored successfully

4049 # 1: Insufficient fillings

4050 # 2: No permission to modify the record

4051 # 3: Invalid rowid

4052 # 4: SUCCESS to delete

4053 # 5: Stop deletion for lack of confirm check

4054 # 6: Password length too short

4055 # 7: Password mismatch

4056 # 8: Old password incorrect

4057 # 9: Duplicated post

4058 rowid=`getpar rowid`

4059 if [ ! -e $1 ]; then

4060 echo "テーブル定義ファイルが見付かりません" | html p

4061 exit 1

4062 fi

4063 tbl=${1%.def}

4064 tbl=${tbl##*/}

4065 if [ -n "$rowid" ]; then # Modify existing entry

4066 if [ x"$tbl" = x"user" ]; then

4067 rowowner=`query "select name from $tbl where rowid=$rowid;"`

4068 elif [ x"$tbl" = x"grp" ]; then

4069 sql="select gname from $tbl where rowid=$rowid;"

4070 ##err p2t:grp:q $sql

4071 isgrpowner "$user" "`query $sql`" && rowowner=$user

4072 elif [ x"$tbl" = x"blog" ]; then

4073 # Check if owner in blog_s

4074 blogowner=`getvalbyid blog owner "$rowid"`

4075 if isgrpowner "$user" "$blogowner"; then

4076 rowowner=$user

4077 origauthor=`getvalbyid blog author $rowid`

4078 if [ -n "$origauthor" -a x"$user" != x"$origauthor" ];

4079 then # Keep original author

4080 setpar author string "$origauthor" # if differs from $user

4081 fi # 2021-11-06 suggd.by Ruri

4082 else

4083 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4084 fi

4085 else

4086 # 2016-12-05 There's no owner column in $tbl (need confirmation)

4087 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4088 fi

4089 ### err rowowner=$rowowner

4090 if [ x"$user" != x"$rowowner" ]; then

4091 echo "他人のレコードはいじれないの" | html p

4092 return 2

4093 elif [ -z "$rowowner" ]; then

4094 echo "指定したレコードはないみたい" | html p

4095 return 3

4096 fi

4097 rm=`getpar rm` cfm=`getpar confirm`

4098 # Editing existent entry

4099 if [ x"$rm" = x"yes" ]; then

4100 if [ x"$rm$cfm" = x"yesyes" ]; then

4101 query "delete from $tbl where rowid=$rowid;"

4102 clearcachedir "$tbl/$rowid"

4103 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

4104 clean-orphaned

4105 fi

4106 return 4

4107 else

4108 echo "消去確認のチェックがないので消さなかったの..." | html p

4109 return 5

4110 fi

4111 fi

4112 fi

4113

4114 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

4115 if [ -n "$rowid" ]; then

4116 # Update of existing record

4117 for col in `gettblcols $tbl`; do

4118 val=`getparquote $col`

4119 [ -z "$val" ] && continue

4120 ## err query "update $tbl set $col=$val where rowid=$rowid"

4121 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

4122 sql="update $tbl set $col=$val where rowid=$rowid;"

4123 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

4124 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

4125 ## User name cannot be changed with interface provided with this

4126 ## script. But we offer the trigger to change owner user

4127 ## of blog_s table.

4128 #err "select quote($col) from $tbl where rowid=$rowid;"

4129 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

4130 cat<<-EOF | query

4131 -- Here we cannot use BEGIN-COMMIT because groupupdate()

4132 -- should use EXCLUSIVE transaction outside of this.

4133 SAVEPOINT par2table;

4134 $sql

4135 update blog_s set val=$val

4136 where key='owner' and val=$old;

4137 RELEASE SAVEPOINT par2table;

4138 EOF

4139 ## XX: DIRTY Hack Ends here

4140 ## We should keep blog's owner as a single column which has

4141 ## foreign key constraint with primary key of grp/user.

4142 else

4143 query "$sql"

4144 fi

4145 done

4146 # Then, set up $pval for further insertion of tbl_s and tbl_m

4147 for col in `gettblpkey $tbl`; do

4148 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

4149 pval="$pval${pval:+, }\"$val\""

4150 done

4151 else

4152 # New entry

4153 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

4154 # Avoid empty repost of article.

4155 if [ x"$tbl" = x"article" ]; then

4156 # If rowid is empty and ID exists in article-table, that is REPOST!

4157 aid=`getpar id`

4158 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

4159 if [ -n "$xaid" ]; then

4160 # REPOST of article

4161 html p <<-EOF

4162 書き込み直後のリロードなので上書きを回避します。

4163 最新記事は末尾の「再読み込み」ボタンから見てください。

4164 EOF

4165 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

4166 return 9 # STOP Duplicated posting

4167 fi

4168 fi

4169 # Generate values() for primary keys

4170 for col in `gettblpkey $tbl`; do

4171 # Genuine primary keys for _m and _s

4172 val=`getvalquote $tbl $col`

4173 [ -z "$val" ] && continue

4174 pval="$pval${pval:+, }$val"

4175 done

4176 ##err pval=$pval

4177 for col in `gettblfkey $tbl`; do

4178 # args for values() to insertion into master table

4179 val=`getvalquote $tbl $col`

4180 [ -z "$val" ] && continue

4181 formaster=$formaster"${formaster:+, }$val"

4182 done

4183 formaster="$pval${formaster:+, }$formaster"

4184 ## err formaster=$formaster

4185 if [ -z "$formaster" ]; then

4186 echo "項目を全て埋めてください" | html pre

4187 return 1

4188 fi

4189 ## err "replace into $tbl values($formaster);"

4190 query "replace into $tbl values($formaster);"

4191 ## Insertion to master table, done

4192 fi

4193

4194 transaction=$tmpd/sqlfile.sql; touch $transaction

4195 for kt in s m; do

4196 tb2=${tbl}_$kt

4197 for col in `gettbl_${kt}_cols $tbl`; do

4198 ptype=`getpartype $col "limit 1"`

4199

4200 # First, check update of existing entries in _m

4201 if [ $kt = m ]; then

4202 # sessID|address.1.22|string|Somewhere-x.y.z

4203 sql=""

4204 ##err dots from query "select var from par where var like '$col.%';"

4205 for v in `query "select var from par where var like '$col.%' AND sessid='$session';"`; do

4206 # v=address.1.22

4207 st_rowid=${v##*.}

4208 origcol=${v%%.*} # original column derived from

4209 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

4210 ##case `getpartype $v` in

4211 ## err CASE `gettbl_coltype $tbl/$origcol` in

4212 ## err edit flag = `getpar action.$v`

4213 case `getpar action.$v` in

4214 rm)

4215 if [ x`getpar confirm.$v` = x"yes" ]; then

4216 newsql="delete from $tb2"

4217 else

4218 echo "削除確認未チェック" | html p

4219 fi ;;

4220 edit)

4221 case `gettbl_coltype $tbl/$origcol` in

4222 image|document|binary)

4223 file=$tmpd/`getparfilename $v`

4224 if [ ! -s "$file" ]; then # Maybe stupid REPOST

4225 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

4226 continue

4227 fi

4228 ## err type=file=$file

4229 [ -z "$file" ] && continue

4230 bn=`sqlquotestr "${file##*/}"`

4231 bin="X'"$(hexize "$file")"'"

4232 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

4233 type=\"file:$ct\"

4234 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

4235 cachedir=`getcachedir "$tbl/$rowid"`

4236 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

4237 rm -rf $cachedir

4238 ;;

4239 *)

4240 newsql="update $tb2 set val=(select val from par where var \

4241 like '$col.%.$st_rowid' AND sessid='$session')"

4242 ;;

4243 esac

4244 ;;

4245 mv)

4246 # regularize filename and strip directory part

4247 newname=`getpar mv.$v|tr -d '":;#<>?^%$!'|tr -d "'"|tr ' ' _`

4248 newname=`basename $newname`

4249 oldname=`query "SELECT val FROM $tb2 WHERE rowid=$st_rowid;"`

4250 oldext=`expr "$oldname" : '.*\.$.*$'`

4251 newext=`expr "$newname" : '.*\.$.*$'`

4252 err "p2t(mv): oldname=$oldname $oldext -> newname($v)=$newname $newext"

4253 if [ -n "$newname" -a x"$oldext" = x"$newext" ];

4254 then

4255 newsql="UPDATE $tb2 SET val='$newname'"

4256 else

4257 html p<<-EOF

4258 $newname は取り扱えないファイル名です。

4259 空白を含まない名前にして下さい。

4260 拡張子の変更もできません。

4261 EOF

4262 continue

4263 fi

4264 ;;

4265 *) # maybe "keep", do not modify value

4266 continue

4267 ;;

4268 esac

4269 # err newsql=$newsql

4270 sql=$sql$nl"$newsql where rowid=$st_rowid;"

4271 clearcachedir "$tbl/$rowid"

4272 done

4273

4274 if [ x"$bin" = x"NULL" ]; then

4275 ## err repl:normal sql=`echo $sql`

4276 if [ -n "$transaction" ]; then

4277 cat<<-EOF >> $transaction

4278 $sql

4279 DELETE FROM $tb2 WHERE type='string' AND val='';

4280 EOF

4281 else

4282 query "$sql

4283 delete from $tb2 where type='string' and val='';"

4284 ## err repl:normal done

4285 fi

4286 else

4287 # Binary update line is TOO LONG to pipelining

4288 sqlfile="$tmpd/sqlf.$$"

4289 if [ -n "$transaction" ]; then

4290 cat<<-EOF >> $transaction

4291 $sql

4292 EOF

4293 else

4294 cat<<-EOF > $sqlfile

4295 $sql

4296 EOF

4297 query ".read $sqlfile"

4298 fi

4299 fi

4300 # Rest of kt==m: set multiple mode

4301 nr=`getparcount $col`

4302 else

4303 nr=1 # for kt==s, number of records is 1

4304 fi

4305

4306 i=0

4307 while [ $i -lt $nr ]; do

4308 limit="limit 1 offset $i"

4309 i=$((i+1)) # increase beforehand against continue

4310 val=`getvalquote $tbl $col "$limit"`

4311 ##XXX [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4312 [ -z "$val" -o x"$val" = x'""' ] && continue

4313 ## err $col=$val

4314 bin=NULL

4315 ## err partype$col=`getpartype $col "$limit"`

4316 ptype=`getpartype $col "$limit"` # partype should be obtained each time

4317 case $ptype in

4318 file) file=$tmpd/`getparfilename $col "$limit"`

4319 ## err parfile-$col=$file

4320 [ -z "$file" ] && continue

4321 bin="X'"$(hexize "$file")"'"

4322 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4323 type=\"file:$ct\" ;;

4324 "*"*) continue ;; # foreign table

4325 *) type=\"string\" ;;

4326 esac

4327 case `gettbl_coltype $tbl/$col` in

4328 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx]|[Tt][Ee][Xx][Tt])

4329 test x"$val" = x"NULL" && val="''"

4330 ;;

4331 password) # special care for password

4332 # name={password,pswd1,pswd2}

4333 p1=`getpar pswd1 "$limit"`

4334 if [ -z "$p1" ]; then

4335 continue # SKIP password setting, if p1 is empty

4336 else

4337 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4338 ## err pswd=$pswd

4339 if pwcheck "$pswd"; then

4340 if [ x"$p1" = x"$p2" ]; then

4341 case "$p1" in

4342 ??????????*) ;;

4343 *) echo "パスワードは10字以上にしてください。" | html p

4344 return 6;;

4345 esac

4346 val="\"`echo $p1|mypwhash`\""

4347 else

4348 echo "2つの新パスワード不一致" | html p

4349 return 7

4350 fi

4351 else

4352 echo "旧パスワード違います" | html p

4353 return 8

4354 fi

4355 fi

4356 ;;

4357 esac

4358 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4359 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4360 if [ x"$val" = x"NULL" -a x"$bin" = x"NULL" ]; then

4361 continue # Do not insert completely NULL record 2021-11-08

4362 fi

4363 sql="REPLACE into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4364 if [ x"$bin" = x"NULL" ]; then

4365 ## err Normal-query: `echo $sql`

4366 if [ -n "$transaction" ]; then

4367 printf '%s' "$sql" >> $transaction

4368 else

4369 query "$sql"

4370 fi

4371 else

4372 sqlfile="$tmpd/query.$$"

4373 ## err sqlfile=`ls -lF $sqlfile`

4374 if [ -n "$transaction" ]; then

4375 cat<<-EOF >> $transaction

4376 $sql

4377 EOF

4378 else

4379 cat<<-EOF >> $sqlfile

4380 $sql

4381 EOF

4382 query ".read $sqlfile"

4383 fi

4384 fi

4385 ## err p2t done

4386 done

4387 done

4388 done

4389 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4390 -- We cannot use transaction here, because groupupdate may use it.

4391 SAVEPOINT pa2table_insert;

4392 .read $transaction

4393 RELEASE SAVEPOINT pa2table_insert;

4394 EOF

4395 rc=$?

4396 [ $rc -eq 0 -a x"$tbl" = x"user" ] && flag_profupdate

4397 ## err "Table:$tbl update done "

4398 return $rc

4399 )

4400 genform() {

4401 # $1 = form definition file

4402 # $2, $3 (optional)= table name and ROWID

4403 # If $GF_VIEWONLY set and nonNull, output values without form

4404 # If $GF_ARGS set, use it as content-strings in the form

4405 # If $GF_OWNER set, use it as value of name="owner"

4406 # If $GF_STAGE set, use it as value of name="stage"

4407 forms="" hiddens="" rowid=$3

4408 if [ ! -e "$1" ]; then

4409 echo "そのようなデータベースはないようです($2)。" | html p

4410 return

4411 elif [ -n "$2" ]; then

4412 rec=`query "select * from $2 where rowid='$rowid';"`

4413 if [ -z "$rec" ]; then

4414 pk=`gettblpkey $2`

4415 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4416 rec=`query "select rowid from $2 where $pk='$rowid';"`

4417 rowid=$rec

4418 rec=$3

4419 fi

4420 if [ -z "$rec" ]; then

4421 echo "そんなレコードはないみたいね..." | html p

4422 return

4423 fi

4424 fi

4425 if [ -z "$GF_VIEWONLY" ]; then

4426 rm='<input id="rm" name="rm" type="checkbox"

4427 value="yes"><label for="rm">このエントリの削除</label>

4428 <span>ほんとうに消しますよ(確認)!

4429 <input name="confirm" type=checkbox value="yes">はい</span>'

4430 fi

4431 # Image Cache dir

4432 ## err genform: getcache=$2/$rowid

4433 td=`getcachedir "$2/$rowid"`

4434 while IFS=: read -r prompt name keytype type args; do

4435 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4436 sp="${args:+ }"

4437 form="" val=""

4438 if [ -n "$rowid" ]; then

4439 # err genform2a: Seeking for "$2.$name, type=$type"

4440 rawval=`getvalbyid $2 $name $rowid $td`

4441 val=`printf '%s\n' "$rawval"|htmlescape`

4442 ## err genform3a: getvalbyid $2 $name $rowid $td

4443 ## err genform3b: val="[$val]" type="$type"

4444 fi

4445 if [ -n "$GF_VIEWONLY" ]; then

4446 is_hidden "$2" "$name" && continue

4447 fi

4448 case "$type" in

4449 text*)

4450 cgiform=cgi_multi_$type

4451 if [ -s $td/$name.count -a -n "$val" ]; then

4452 form=`$cgiform $name $td`

4453 val=$(printf '%s\n' "$val"|

4454 while read fn; do

4455 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4456 </td></tr>$nl"

4457 done)

4458 val="<table>$nl$val$nl</table>"

4459 else

4460 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4461

4462 form=`cgi_$type $name "$rawval" "$args"`

4463 fi

4464 ;;

4465 [Rr][Aa][Dd][Ii][Oo])

4466 fh="<label><input type=\"radio\" name=\"$name\""

4467 form="`echo $args|sed -e \

4468 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4469 ;;

4470 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4471 checked=${val:+ checked}

4472 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\"$checked>${args%=*}</label>"

4473 ;;

4474 [Ss][Ee][Ll][Ee][Cc][Tt])

4475 fh="<select name=\"$name\">$nl"

4476 form=$(for l in $args; do

4477 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4478 done)

4479 if [ -n "$val" ]; then

4480 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4481 fi

4482 form="$fh$form</select>"

4483 ;;

4484 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4485 if [ -s $td/$name.count ]; then

4486 form=`cgi_multi_file $name $td "$args"`

4487 if [ -n "$val" ]; then

4488 hrfb="$myname?showattc+$2_m"

4489 val=$(echo "$rawval" \

4490 | while read fn; do

4491 data=`percenthex "$td/$fn"`

4492 #ct=`cat $td/$fn.content-type`

4493 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4494 ri=`cat "$td/$fn.rowid"`

4495 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4496 #imgsrc="<img src=\"data:$ct,$data\">"

4497 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4498 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4499 done)

4500 fi

4501 else

4502 form="<input type=\"file\" name=\"$name\" $args>"

4503 if [ -n "$val" ]; then

4504 imgs=$(echo "$rawval"\

4505 |while read fn;do

4506 data=`percenthex "$td/$fn"`

4507 echo "<img src=\"data:image/png,$data\">$fn<br>"

4508 done)

4509 form=$form"<br>$imgs"

4510 val=$imgs # 2015-06-15

4511 else

4512 form="<input type=\"file\" name=\"$name\" $args>"

4513 fi

4514 fi

4515 ;;

4516 [Hh][Ii][Dd][Dd][Ee][Nn])

4517 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4518 args="value=\"$GF_STAGE\""

4519 fi

4520 form="<input type=\"hidden\" name=\"$name\" $args>"

4521 prompt='' # Remove prompt

4522 ;;

4523 [Aa][Uu][Tt][Hh][Oo][Rr])

4524 [ -n "$GF_VIEWONLY" ] && continue

4525 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4526 prompt="" ;;

4527 [Oo][Ww][Nn][Ee][Rr])

4528 [ -n "$GF_VIEWONLY" ] && continue

4529 val=${GF_OWNER:-$val}

4530 val=${val:-$user}

4531 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4532 prompt="" ;;

4533 [Uu][Ss][Ee][Rr])

4534 # XXX: is null $user ok?

4535 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4536 [ -n "$GF_VIEWONLY" ] && continue

4537 form="$user"

4538 ;;

4539 [Pp]assword)

4540 [ -n "$GF_VIEWONLY" ] && continue

4541 form="`cgi_passwd`"

4542 val=""

4543 ;;

4544 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4545 [ -n "$GF_VIEWONLY" ] && continue

4546 if [ -z "$rowid" ]; then

4547 val=`genserial`

4548 fi

4549 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4550 prompt="" ;;

4551 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4552 prompt=""

4553 ;;

4554 parent|path|blog*)

4555 prompt=""

4556 ;;

4557 "*"*)

4558 tail=$tail"``"

4559 continue ;;

4560 esac

4561 if [ -n "$prompt" ]; then

4562 if [ -n "${GF_VIEWONLY}" ]; then

4563 form=$val

4564 else

4565 :

4566 fi

4567 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4568 else

4569 hiddens=$hiddens$nl"$form"

4570 fi

4571 done < $1

4572 # enctype="multipart/form-data"

4573 cat<<-EOF

4574 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4575 EOF

4576 test -n "$rowid" && printf '%s\n' "$rm" # Workaround for utf8 buggy NetBSD sh

4577 cat<<EOF

4578 <table class="b $2">

4579 $forms

4580 </table>$hiddens

4581 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4582 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4583 EOF

4584 if [ -z $GF_VIEWONLY ]; then

4585 cat<<EOF

4586 <input type="submit" name="sub" value="OK">

4587 <input type="reset" name="res" value="Reset">

4588 EOF

4589 fi

4590 cat<<EOF

4591 $GF_ARGS</form>

4592 $tail

4593 EOF

4594 }

4595 edittable() {

4596 # $1=form-def $2=table $3 rowid

4597 genform "$@"

4598 }

4599 viewtable() {

4600 GF_VIEWONLY=1 genform "$@"

4601 }

4602 showattc() {

4603 # $1=table_m $2=rowid &optional $3=RawFlag

4604 ## err \$1=$1 \$2=$2 \$3=$3

4605 if ! isfilereadable $user $1 $2; then

4606 contenttype; echo

4607 echo "このファイルは管理者のみしか見られません" | html p

4608 putfooter; exit

4609 fi

4610 idir=`umask 002; mktempd` || exit 1

4611 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4612 bin=$idir/$myname-$$.bin

4613 sql="select quote(bin) from $1 where rowid='$2';"

4614 ## err showattc: sql: $sql

4615 sq $db "$sql" | unhexize > $bin

4616 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4617 type=${tv%//*} fn=${tv#*//}

4618 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4619 ct=${type#file:}

4620 case $ct in # all text/* changed to text/plain

4621 text/*|application/csv|application/json)

4622 charset=`nkf -g $bin|cut -d' ' -f1`

4623 case $charset in

4624 ASCII*) charset="" ;;

4625 esac

4626 if [ -z "$3" ]; then

4627 ct="text/html${charset:+; charset=$charset}"

4628 link="?showattc+$1+$2+raw"

4629 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4630 | sed 's,^,<span></span>,' \

4631 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4632 -D_LINK_="$link" \

4633 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4634 exit $?

4635 fi

4636 ct="text/plain${charset:+; charset=$charset}"

4637 ;;

4638 video/*)

4639 if [ -z "$3" ]; then

4640 _m4 -D_TITLE_="$fn" \

4641 -D_SRC_="?showattc+$1+$2+raw" $layout/videoplay.m4.html

4642 exit $?

4643 fi

4644 esac

4645 contenttype "$ct"

4646 echo "Content-Disposition: filename=\"$fn\""

4647 echo "Content-Length: " `cat $bin | wc -c`; echo

4648 #echo "Content-Type: " ${type#file:}; echo

4649 cat $bin

4650 }

4651 #

4652 # Some default stupid handler on CGI values

4653 #

4654 default_storedb() {

4655 # ARG: $1=table-def-file

4656 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4657 tbl=`basename $1`

4658 tbl=${tbl%.def}

4659 cols="`grep :text $1|cut -d: -f2`"

4660 col=`echo "$cols"|head -1`

4661 vcol=`getpar $col`

4662 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4663 if [ -n "$vcol" ]; then

4664 par2table $1

4665 else

4666 return 2 # No insertion occurred

4667 fi

4668 }

4669

4670 default_view() { # $1=def-file

4671 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4672 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4673 default_storedb "$@"

4674 query "select rowid from $tbl order by rowid desc;" \

4675 | while read rowid; do

4676 viewtable $1 $tbl $rowid

4677 done | _m4 -D_TITLE_="$tbl" \

4678 -D_FORM_="`genform $1`" \

4679 -D_DUMPTABLE_="syscmd(cat)" \

4680 $layout/html.m4.html $layout/form+dump.m4.html

4681 }

4682 default_viewtext() { # $1=def-file

4683 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4684 default_storedb "$@"

4685 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4686 | _m4 -D_TITLE_="$tbl" \

4687 -D_FORM_="`genform $1`" \

4688 -D_DUMPTABLE_="syscmd(cat)" \

4689 $layout/html.m4.html $layout/form+dump.m4.html

4690 }

4691 default_smail() {

4692 default_storedb "$@"

4693 if [ $? -eq 2 ]; then

4694 _m4 -D_TITLE_="入力" \

4695 -D_FORM_="`genform $1`" \

4696 -D_DUMPTABLE_="" \

4697 $layout/html.m4.html $layout/form+dump.m4.html

4698 return

4699 fi

4700 cond=""

4701 for pk in `gettblpkey $tbl`; do

4702 pv=$(sqlquote "$(getpar $pk)")

4703 cond="$cond${cond:+ and }$pk=$pv"

4704 done

4705 sql="select rowid from $tbl where $cond;"

4706 rowid=`query "$sql"`

4707 ## err smail1 - "$sql" "-> rowid=$rowid"

4708

4709 while IFS=: read prompt name keytype type args; do # Read from $1

4710 val=`getpar $name`

4711 if [ -n "$val" ]; then

4712 text="$text

4713 $prompt

4714 $name=$val

4715 ---------------------------------------------------------"

4716 fi

4717 case "$type" in

4718 image|document|file)

4719 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4720 fns=$(echo "$fn"|while read fn; do

4721 err mv $tmpd/$fn.orig $tmpd/$fn

4722 mv $tmpd/$fn.orig $tmpd/$fn

4723 rm $tmpd/$fn.rowid # Remove cache flag

4724 ## err "`ls $tmpd/$fn`"

4725 echo $fn

4726 done)

4727 files="$files $fns"

4728 ;;

4729 esac

4730 done < $1

4731 ## err FILES=$files "`ls -lF $tmpd`"

4732 subj="from ${REMOTE_ADDR}"

4733 (echo "$url"

4734 echo "への書き込みがありました。"

4735 echo "------"

4736 echo "$text"

4737 ) | (cd $tmpd &&

4738 err LS="`ls -lF`" &&

4739 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4740 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4741 echo "以下の内容で送信しました。" | html p

4742 viewtable $1 $tbl \

4743 `query "select rowid from $tbl order by rowid desc limit 1;"`

4744 echo "戻る" | html a "href=\"?\""

4745 }