s4: 79aa2ae39103 s4-funcs.sh

s4

view s4-funcs.sh @ 1024:79aa2ae39103

glTF is not application/*, but model/*

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 13 Oct 2023 08:27:05 +0900
parents	49ba480fc955
children	cccd00682cc2

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,.m4a,.m4v,.mkv,.obj,.avi,.ogg,.mov,.webm,.gpx,.json,.geojson,.umap,.kml,.kmz,.html,.css,.js,.java,.el,.go,.cc,.rb,.rs,.py,.pl,lua,.awk,.sh,.c,.h,.log,.txt,.tex,.sty,.zip,.xcf,.bz2,.gz,.xz,.7z,.csv,.dat,.db,.sq3,.blend,.gltf,.glb"'

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,~~$[^~][^~]*$~~,<s>\1</s>,g' \

664 -e 's,$[^\\]$;;;,\1<br>,g;s,\\;;;,;;;,g' \

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

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

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

668

669 }

670 minitbl() {

671 sed -n '

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

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

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

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

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

677 H; # Concat this line to HoldSpace

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

679 $ b cont

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

681 }

682 :cont

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

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

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

686 # . of ^. is workaround for FreeBSD sed

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

688 p; # Print whole "table" element

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

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

691 }

692 x; # Back to the newest line

693 p; # Print rest' | miniul

694 }

695 miniul() {

696 sed -e '

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

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

699 :top

700 s/\n//;

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

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

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

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

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

706 $ b cont

707 N; # 次の行を読む

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

709 /^ /b top

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

711 p; s/.*//;

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

713 /^\* /b top

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

715 }

716

717 :cont

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

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

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

721 H; x

722 }

723 x' | miniol

724 }

725 miniol() {

726 sed -e '

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

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

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

730 x;

731 :top

732 s/\n//;

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

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

735 x;

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

737 x;

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

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

740 $ b cont

741 N; # 次の行を読む

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

743 /^ /b top

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

745 p; s/.*//;

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

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

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

749 }

750

751 :cont

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

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

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

755 H; x

756 }

757 x'

758 }

759 acclog() (

760 # $1=table, $2=rowid

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

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

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

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

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

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

767 fi

768 )

769 gecos() (

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

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

772 )

773 setpar() {

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

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

776 case "$1" in

777 user) _user="$v" ;;

778 skey) _skey="$v" ;;

779 esac

780 fi

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

782 }

783 unsetpar() {

784 for i; do

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

786 case "$i" in

787 user|skey) unset _$i ;;

788 esac

789 fi

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

791 done

792 }

793 replpar() {

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

795 }

796 getpar() {

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

798 val=""

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

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

801 user) val=$_user ;;

802 skey) val=$_skey ;;

803 esac

804 fi

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

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

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

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

809 fi

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

811 case "$var" in

812 owner)

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

814 echo $user; return

815 elif ismember $user $val; then

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

817 fi ;;

818 esac

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

820 printf '%s' "$val"

821 }

822 setskey() {

823 # For quick response...(?)

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

825 }

826 chkskey() {

827 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

833 return 0

834 fi

835 return 1

836 }

837 resetskey() {

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

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

840 fi

841 }

842 getpartype() {

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

844 }

845 getparcount() {

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

847 }

848 getparfilename() {

849 # null if type of $1 is not file

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

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

852 }

853 sqlquote() {

854 (v="$1"

855 case "$v" in

856 "") return ;; # null

857 "X'"*) # quoted hex string

858 echo $1 ;;

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

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

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

862 return ;;

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

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

865 return ;;

866 *)

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

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

869 else

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

871 fi ;;

872 esac)

873 }

874 sqlquotestr() (

875 case "$1" in

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

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

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

879 esac

880 )

881 mktempd() {

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

883 }

884 getcachedir() { # $1=maintable

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

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

887 else

888 echo $tmpd/$thumbxy

889 fi

890 }

891 getval() {

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

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

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

895 echo "$user" ;;

896 stamp|datetime)

897 date "+%F %T" ;;

898 serial)

899 (s=`getpar $2`

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

901 *)

902 getpar "$2" "$3";;

903 esac

904 }

905

906 getvalquote() {

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

908 (v=`getval "$@"`

909 case "$v" in

910 "") echo NULL ;;

911 *) sqlquotestr "$v" ;;

912 esac)

913 }

914 getparquote() {

915 sqlquote "`getpar $1`"

916 }

917 getbinbyid() {

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

919

920 }

921 getvalbyid() {

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

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

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

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

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

927

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

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

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

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

932 return

933 fi

934 done

935 rowid=$3

936 pk=`gettblpkey $1`

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

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

939 td=${4:-$tmpd}

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

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

942 for kt in s m; do

943 t=${1}_$kt

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

945 vcount=1 # count(val)

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

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

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

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

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

951 if [ $kt = m ]; then

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

953 # Reset val to store filenames if type is string

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

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

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

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

958 i=0

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

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

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

962 i=$((i+1))

963 fn=$c.$i

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

965 case $type in

966 file:*)

967 #file=$td/$val

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

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

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

971 # FOR SPEED: Skip file generation if imgcache exists

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

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

974 && continue

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

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

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

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

979 select rowid from $cond $slice;

980 .output '$td/$fn'

981 select val from $cond $slice;

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

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

984 .output stdout

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

986 EOF

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

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

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

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

991 ls -lh "$file" |

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

993 case "$type" in

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

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

996 *|*:[Aa]pplication*)

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

998 png:- > "$file"

999 ;;

1000 esac

1001 ;;

1002 *)

1003 sq $db<<EOF

1004 .output $td/$fn.rowid

1005 select rowid from $cond $slice;

1006 .output $td/$fn

1007 select val from $cond $slice;

1008 EOF

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

1010 ;;

1011 esac

1012 done

1013 else

1014 rm -f $td/$c.count

1015 case $type in

1016 file:*)

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

1018 | while read fn; do

1019 file=$td/$fn

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

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

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

1023 | unhexize > "$file"

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

1025 case $type in

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

1027 *:[Aa]pplication*)

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

1029 png:- > $file ;;

1030 esac

1031 fi

1032 done

1033 ;;

1034 esac

1035 fi

1036 printf '%s' "$val"

1037 return

1038 fi

1039 done

1040 done)

1041 }

1042 getvalbypkey() (

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

1044 pk=`gettblpkey $1`

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

1046 getvalbyid "$1" "$2" $rowid $4

1047 )

1048 getvalbycond() {

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

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

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

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

1053 getvalbyid "$1" "$2" $rowid "$4"

1054 fi

1055 }

1056 getpwfield() {

1057 # getpwfield user column

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

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

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

1061 echo "$val"

1062 return 0

1063 else

1064 return 1

1065 fi

1066 }

1067 numericalize() {

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

1069 }

1070 encode() {

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

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

1073 sha1=sha1

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

1075 sha1=sha1sum

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

1077 sha1=gsha1sum

1078 fi

1079 fi

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

1081 }

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

1083 gs_pdfwrite() {

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

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

1086 }

1087 fi

1088 enjpeg() {

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

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

1091 cjpeg="cjpeg"

1092 else

1093 cjpeg="convert - jpeg:-"

1094 fi

1095 fi

1096 $cjpeg "$@"

1097 }

1098 mycrypt() (

1099 key=$1 salt=$2

1100 # err \$2=$2

1101 case $2 in

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

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

1104 esac

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

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

1107 )

1108 hexize() {

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

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

1111 hexize="xxd -p"

1112 else

1113 hexize_hd() {

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

1115 }

1116 hexize="hexize_hd"

1117 fi

1118 fi

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

1120 }

1121 unhexize() {

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

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

1124 unhex="xxd -p -r"

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

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

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

1128 EOF

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

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

1131 # world writable...:)

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

1133 fi

1134 fi

1135 cat "$@" | $unhex

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

1137 }

1138 percenthex() {

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

1140 }

1141 htmlescape() {

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

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

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

1145 }

1146 enascii() {

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

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

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

1150 else

1151 enascii_now=`date +%FT%T`

1152 enascii_sed() {

1153 nkf -Z0Z1Z2 \

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

1155 }

1156 enascii="enascii_sed"

1157 fi

1158 fi

1159 cat "$@" | $enascii

1160 }

1161 size_h() {

1162 i="$1" oi=$1

1163 set -- B B KB MB GB TB

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

1165 oi=$i

1166 i=$((i/1024))

1167 shift

1168 done

1169 echo ${oi}$1

1170 }

1171 gettblconf() {

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

1173 ## tconfs=`sq $db \

1174 tconfs=`query \

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

1176 fi

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

1178 }

1179 gettblkeys() {

1180 # $1=tbl

1181 gettblconf

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

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

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

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

1186 case $type in

1187 *p*)

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

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

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

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

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

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

1194 esac

1195 case $type in

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

1197 esac

1198 done

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

1200 }

1201 gettblpkey() {

1202 # $1=tbl

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

1204 }

1205 gettblfkey() {

1206 (x=`gettblkeys $1`

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

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

1209 }

1210 gettblcols() {

1211 (x=`gettblkeys $1`

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

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

1214 }

1215 gettbl_s_cols() {

1216 (x=`gettblkeys $1`

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

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

1219 }

1220 gettbl_m_cols() {

1221 (x=`gettblkeys $1`

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

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

1224 }

1225 gettbl_h_cols() {

1226 (x=`gettblkeys $1`

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

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

1229 }

1230 gettbl_coltype() (

1231 gettblconf

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

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

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

1235 )

1236 is_hidden() {

1237 # $1=Tbl $2=col

1238 gettblconf

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

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

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

1242 case $x in

1243 *h*) return 0 ;;

1244 *) return 1 ;;

1245 esac

1246 }

1247

1248 dbsetbyid() {

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

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

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

1252 quotedp=$(sqlquotestr "$p")

1253 unset primary update

1254 gettblconf

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

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

1257 #err conf=$conf

1258 case ${conf#*=} in

1259 p*) primary=1 ;;

1260 f*) update=1 ;;

1261 u*) ;;

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

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

1264 esac

1265 #err t=$t

1266 type=string fn=""

1267 case $conf in

1268 */password)

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

1270 ;;

1271 */image*|*/document*)

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

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

1274 ;;

1275 esac

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

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

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

1279 if [ "$primary" ]; then

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

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

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

1283 elif [ "$update" ]; then

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

1285 else

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

1287 fi

1288 )

1289 }

1290 expire() (

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

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

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

1294 )

1295 addsession() {

1296 # expireをセット

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

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

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

1300 # Remove old session parameters

1301 now=`expire now`

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

1303 }

1304 gencookie() (

1305 path=${URL#*:/}

1306 path=${URL%/*}

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

1308 for kv; do

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

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

1311 done

1312 )

1313 contenttype() {

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

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

1316 }

1317 putheader() {

1318

1319 }

1320 putfooter() {

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

1322 }

1323 getcookie() {

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

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

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

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

1328 case "$k" in

1329 user) _user="$v" ;;

1330 skey) _skey="$v" ;;

1331 esac

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

1333 done

1334 }

1335 genrandom() {

1336 # $1=columns (default: 10)

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

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

1339 }

1340 genserial() {

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

1342 }

1343 smail() {

1344 # smail rcpts subj (file)

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

1346 # $MAIL_FROM <- From: header value

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

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

1349 ## Gmail rejects below(Duplicated headers)

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

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

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

1353 sender=${SENDER:-$admin}

1354 # Do not call m4 with directly passing text

1355 _r=$tmpd/rcpt

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

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

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

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

1360 }

1361 smail_queue_flush() {

1362 # $1=timelimit

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

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

1365 SELECT rowid FROM smailq

1366 GROUP BY rcpts, subj

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

1368 EOF

1369 )

1370 for rid in $rowids; do

1371 sq $workdb \

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

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

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

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

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

1377

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

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

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

1381 .separator "\n" "------------------\n\n"

1382 SELECT time, text FROM smailq

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

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

1385 ORDER by time;

1386 EOF

1387 then

1388 cat <<-EOF | sq $workdb

1389 DELETE FROM smailq

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

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

1392 EOF

1393 fi

1394 fi

1395 done

1396 }

1397 smail_queue() {

1398 # $1=Rcpts, $@=subj

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

1400 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1409 EOF

1410 )

1411 ## XXX: Adhoc load mitigation

1412 case "$now" in

1413 *[01])

1414 err flush_queue=$mintime

1415 smail_queue_flush ;;

1416 esac

1417 fi

1418 }

1419 setviastring() {

1420 table=par

1421 oifs="$IFS"

1422 IFS="&"

1423 for us in $1; do

1424 k=${us%%=*}

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

1426 setpar "$k" "string" "$v"

1427 done

1428 IFS="$oifs"

1429 }

1430 checkdomain() (

1431 # Check the validity of domain by referring DNS

1432 item=$1

1433 err checkdomain $1

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

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

1436 )

1437 pwcheck() {

1438 # $1=passwd

1439 dbpswd=`getpwfield $user pswd`

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

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

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

1443 }

1444 mypwhash() {

1445 mycrypt "`cat`" `genrandom 5`

1446 }

1447 flag_profupdate() {

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

1449 # This is useful to mitigate account sync load

1450 query <<-EOF

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

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

1453 EOF

1454 touch $userupdateflag

1455 }

1456 wasureta() {

1457 user=$1

1458 if ! checkdomain $user; then

1459 contenttype; echo

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

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

1462 putfooter

1463 exit 0

1464 fi

1465 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1467 encpswd=`echo $newpswd|mypwhash`

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

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

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

1471 $msgdir/mail-newaccount.m4 \

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

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

1474 }

1475 checkauth() {

1476 user=`getpar user`

1477 skc=`getpar skey` # from cookie

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

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

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

1481 if chkskey "$skc"; then

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

1483 return 0

1484 fi

1485 fi

1486 pswd=`getpar pswd`

1487 quser=`sqlquotestr "$user"`

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

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

1490 return 4 # 4=server too heavy

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

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

1493 return 2 # 2=login fail

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

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

1496 contenttype; echo

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

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

1499 cat <<-EOF | html p

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

1501 EOF

1502 exit 0 # XXXXX more smart...

1503 fi

1504 wasureta "$user"

1505 err wasureta issued for "$user"

1506 return 1 # wasureta error

1507 fi

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

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

1510 if pwcheck "$pswd"; then

1511 newsession=`genrandom 34`

1512 if setskey "$user" "$newsession" &&

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

1514 gencookie "user=$user" "skey=$newsession"

1515 return 0

1516 else

1517 return 4 # Heavy load??

1518 fi

1519 fi

1520 err "Login failed: [$user]"

1521 return 2 # Password mismatch

1522 }

1523 showlogin() {

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

1525 test -z "$args" && resetskey

1526 s4name=${S4NAME:-s4}

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

1528 cat $layout/login.m4.html

1529 echo '</body></html>'

1530 ) | _m4 \

1531 -D_BODYCLASS_="login" \

1532 -D_TITLE_="$s4name" \

1533 -D_SYSNAME_="Welcome to $s4name" \

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

1535 exit 0

1536 }

1537 dologin() {

1538 test -n "$S4WORLD" && syncaccount

1539 checkauth

1540 st=$?

1541 if [ $st != 0 ]; then

1542 contenttype; echo

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

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

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

1546 showlogin # and EXIT

1547 fi

1548 err "Auth OK: [$user]"

1549 }

1550

1551 # Do instant jobs here

1552 dbsetup

1553 trap cleanup INT HUP EXIT TERM PIPE

1554 # trap cleanup INT HUP

1555

1556

1557 cgiinit() {

1558 tmpd=`tmpd=$tmpdir mktempd`

1559 tmpf=$tmpd/stream.$$

1560 tmpfiles=$tmpfiles" $tmpd"

1561 addsession $session

1562 getcookie

1563 case "$REQUEST_METHOD" in

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

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

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

1567 # cat > $tmpf # too much?

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

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

1570 s="`cat $tmpf`"

1571 ;;

1572 esac

1573 case "$CONTENT_TYPE" in

1574 *boundary*)

1575 bndry=${CONTENT_TYPE#*boundary=}

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

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

1578 do

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

1580 #echo u=$us

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

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

1583 case "$k" in

1584 *:filename)

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

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

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

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

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

1590 :

1591 fi

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

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

1594 case "$mimetype" in

1595 [Ii]mage/x-xcf)

1596 bzip2 "$tmpd/$v"

1597 v=${v}.bz2

1598 ;;

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

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

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

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

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

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

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

1606 v=$vjpg

1607 else

1608 mimetype="Not supported"

1609 fi

1610 ;;

1611 [Ii]mage/*)

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

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

1614 ;;

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

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

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

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

1619 err Calling gs from $v to $nv

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

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

1622 v=$nv

1623 fi

1624 fi

1625 fi

1626 esac

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

1628 then

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

1630 continue

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

1632 replpar text string \

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

1634 continue

1635 fi

1636 ;;

1637 image|document|binary)

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

1639 continue ;;

1640 *)

1641 type='string'

1642 ;;

1643 esac

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

1645 setpar "$k" "$type" "$v"

1646 done

1647 ;;

1648 *)

1649 setviastring "$s"

1650 ;;

1651 esac

1652 }

1653 email4group() {

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

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

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

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

1658 sql="WITH

1659 grpemails AS (

1660 SELECT gname, user, val email

1661 FROM grp_mem NATURAL JOIN grp_mem_s

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

1663 useremails AS (

1664 SELECT user.name, val email

1665 FROM user

1666 LEFT JOIN user_m

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

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

1669 FROM useremails u LEFT JOIN grpemails g

1670 ON u.name=g.user

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

1672 query "$sql"

1673 }

1674 email4groupbyuid() {

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

1676 g=$1; shift

1677 uids=`echo "$@"`

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

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

1680 email4group "$g" `query "$sql"`

1681 }

1682 myemail4group() {

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

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

1685 }

1686 collectmembersbyid() {

1687 # Collect user names of group specified by grid

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

1689 query "SELECT user FROM grp_mem \

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

1691 }

1692 collectmembersbyid() {

1693 # Collect user names of group name

1694 qgrp=`sqlquote "$1"`

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

1696 }

1697 collectgecosesbyid() {

1698 # Collect user gecoses of group

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

1700 query<<-EOF

1701 SELECT gecos

1702 FROM gecoses

1703 WHERE name IN (SELECT user FROM grp_mem

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

1705 EOF

1706 }

1707 collectemail() (

1708 # Collect email addresses for group $1

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

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

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

1712 for e; do

1713 if isuser "$e"; then

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

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

1716 else

1717 qgrp=`sqlquote "$e"`

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

1719 gmem="grp_mem"

1720 else

1721 tm=`sqlquote "$TEAM"`

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

1723 fi

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

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

1726 $gmem g left join grp_mem_s s

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

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

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

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

1731 query "$sql"

1732 fi

1733 done

1734 )

1735 sendinvitation() (

1736 # $1=email

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

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

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

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

1741 gecos=`gecos`

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

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

1744 _m4 -D_URL_="$urlbase" \

1745 -D_USER_="$name" \

1746 -D_EMAIL_="$1" \

1747 -D_REGIST_="$regist" \

1748 -D_ADMIN_="$admin" \

1749 $msgdir/mail-invite.m4 \

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

1751 return 0

1752 )

1753 emaildomaincheck() {

1754 case "$1" in

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

1756 *@*)

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

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

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

1760 return 2

1761 fi

1762 return 0

1763 ;;

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

1765 esac

1766 }

1767 invite() {

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

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

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

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

1772 return

1773 fi

1774 fi

1775 case "$email" in

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

1777 *@*)

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

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

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

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

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

1783 elif sendinvitation $email; then

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

1785 else # Cannot be reached here

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

1787 fi ;;

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

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

1790 esac

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

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

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

1794 fi

1795 if [ -f $invite_policy ]; then

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

1797 else

1798 pol="$invite_policy"

1799 fi

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

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

1802 -D_INVITE_POLICY_="$pol" \

1803 $layout/html.m4.html $layout/invite.m4.html

1804 }

1805 regist() {

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

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

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

1809 echo "bye bye" | html p

1810 reutrn

1811 fi

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

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

1814 cat<<EOF

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

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

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

1818 EOF

1819 return

1820 fi

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

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

1823 # Fake login password to wasureta

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

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

1826 wasureta $email

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

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

1829 addsession $1 # for removal after 1 minute

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

1831 $layout/login.m4.html

1832 return

1833 }

1834 group_safename() {

1835 # Convert $1 to safe group name

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

1837 }

1838 groupupdate() {

1839 gname=`getpar gname`

1840 qgname=`sqlquote "$gname"`

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

1842 # See ALSO same job in showgroup()

1843 newgname=`group_safename "$gname"`

1844 err newgname=$newgname

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

1846 err NewGNAME: gname=$newgname

1847 gname=$newgname

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

1849 replpar gname string "$gname"

1850 fi

1851 # Name confliction check

1852 parow=`getpar rowid`

1853 ## err parow=$parow

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

1855 query "BEGIN EXCLUSIVE;"

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

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

1858 if [ $count -gt 0 ]; then

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

1860 query "END;"

1861 return

1862 fi

1863 par2table $formdir/grp.def

1864 query "END TRANSACTION;"

1865 # Remove orphan

1866 : <<EOF

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

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

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

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

1871 EOF

1872 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1876 sql="delete from blog where id in

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

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

1879 query "$sql";

1880 unsetpar tag kwd

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

1882 return # and return

1883 fi

1884 fi

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

1886 fi

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

1888 grid=$(query $sql)

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

1890 grp $grid

1891 }

1892 groupclone() {

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

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

1895 case "$1" in

1896 */noteam)

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

1898 esac

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

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

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

1902 return

1903 fi

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

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

1906 return

1907 fi

1908 i=0

1909 while true; do

1910 copy="-copy$i"

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

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

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

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

1915 i=$((i++))

1916 continue

1917 fi

1918 break

1919 done

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

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

1922 query<<-EOF

1923 BEGIN;

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

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

1926 SELECT $newqname, key, val

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

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

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

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

1931 -- Copy members and their configuration --

1932 REPLACE INTO grp_mem SELECT $newqname, user

1933 FROM grp_mem WHERE gname=$qgrp;

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

1935 FROM grp_mem_s WHERE gname=$qgrp;

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

1937 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1938 -- Copy administrators --

1939 REPLACE INTO grp_adm SELECT $newqname, user

1940 FROM grp_adm WHERE gname=$qgrp;

1941 COMMIT;

1942 EOF

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

1944 STOPCLONEMSG=1 groupconf "$newrowid"

1945 }

1946 groupman() {

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

1948

1949 GF_STAGE="grpconf"

1950 GF_STAGE=groupupdate

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

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

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

1954 -D_DUMPTABLE_="syscmd(cat)" \

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

1956 }

1957 userconf() {

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

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

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

1961 }

1962 groupconf() {

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

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

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

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

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

1968

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

1970 grp=`getgroupbyid $rowid`

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

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

1973 return

1974 fi

1975

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

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

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

1979 ## Setup migration menu

1980 height="10em" ## Ugly!!

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

1982 v=`fgrep -v "value=\"$worldconf\"" $worldoptionfile`

1983 err v=$v

1984 if [ -n "$v" ]; then

1985 migrate=$(cat<<-EOF

1986 `cgi_radio grpaction migrate id="migrate"`<label

1987 for="migrate">別Worldへ移住</label>

1988 <div style="height: $height;">

1989 <form action="?migrategrp">

1990 <p>移住先:<select name="migrateto">$nl$v$nl</select></p>

1991 <p>グループや掲示板のURLが変わります。

1992 外部からリンクしている場合は飛べなくなります。

1993 すでにリンクされた掲示板を多数含む場合は既存グループを温存し、

1994 「グループのクローン」で

1995 メンバーを引き継いだ上でそのクローンを移住するのがお勧めです。</p>

1996 <p><label>`cgi_checkbox emichk yes`確認</label></p>

1997 `cgi_hidden stage migrategrp`

1998 `cgi_hidden rowid $rowid`

1999 `cgi_submit OK`

2000 `cgi_reset Reset`

2001 </form>

2002 </div>

2003 EOF

2004 )

2005 fi

2006 fi

2007 html div 'class="foldtabs"' <<-EOF

2008 `cgi_radio grpaction clone id="clone"`<label

2009 for="clone">グループのクローン作成</label>

2010 <div style="height: $height;">

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

2012 <table>

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

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

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

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

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

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

2019 </table>

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

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

2022 </div>

2023 $migrate

2024 `cgi_radio grpaction close id="x"`<label for="x" accesskey="x">×</label>

2025 <div style="height: $height; background: transparent;"></div>

2026 EOF

2027 fi

2028 }

2029 migrategrp() {

2030 rowid=`getpar rowid`

2031 rowid=${rowid%%[!0-9]*}

2032 grp=`getgroupbyid $rowid`

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

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

2035 return

2036 fi

2037 if [ x`getpar emichk` != x"yes" ]; then

2038 echo "移住確認未チェックなので中止します。" | html p

2039 grp "$rowid"

2040 return

2041 fi

2042 destconf=`getpar migrateto`

2043 err destconf=$destconf

2044 if [ ! -e $destconf ]; then

2045 echo "移住先Worldが認識できないので中止します($destconf)。" | html p

2046 grp "$rowid"

2047 return

2048 fi

2049 if [ -n "$worldconf" ]; then

2050 srcconf=$worldconf

2051 else

2052 srcconf=s4-config.sh

2053 fi

2054 _m4 -D_TITLE_="移住操作" -D_BODYCLASS_="" $layout/html.m4.html

2055 echo "移住操作" | html h1

2056 echo '<pre>'

2057 set -- "$srcconf" "$destconf" "$rowid"

2058 err ./s4-migrate.sh "$srcconf" "$destconf" "$rowid"

2059 . ./s4-migrate.sh # Dot(.) sourcing might not pass arguments

2060 rc=$?

2061 echo "</pre>"

2062 if [ $rc -eq 0 ]; then

2063 echo "World [$world] への移住完了。" | html p

2064 echo "<p><a href=\"$dsturl?grp+$destrowid\">移住先</a></p>"

2065 elif [ -n "$faillist" ]; then

2066 echo "移住後削除失敗" | html p

2067 echo "空いている時間帯に再度試して下さい。" | html p

2068 else

2069 echo "移住失敗" | html p

2070 echo "移動先に重複がないか確認して下さい。" | html p

2071 fi

2072 return

2073 }

2074 mems() {

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

2076 kwd=`getpar kwd`

2077 listmember $kwd

2078 }

2079 grps() {

2080 case "$S4WORLD" in

2081 $nonewgroupworld) ;;

2082 *) LINK_NEWGRP="<a href=\"?groupman\">新規グループ作成</a>" ;;

2083 esac

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

2085 kwd=`getpar kwd`

2086 listgroup "$kwd" \

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

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

2089 -D_FORM_="${LINK_NEWGRP}${NEWGRP_GUIDE}" \

2090 $layout/form+dump.m4.html

2091 }

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

2093 gpg=`getpar grp`

2094 grid=${1:-$gpg}

2095 grp=`getgroupbyid "$grid"`

2096 ## . ./s4-blog.sh

2097 jg=`getpar joingrp`

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

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

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

2101 fi

2102 htmlheader=$layout/html.m4.html

2103 showgroup "$grid"

2104 }

2105 sql4interestblogs() {

2106 cat<<EOF

2107 CREATE TEMPORARY VIEW interestblogs AS

2108 SELECT blog.rowid rid, id, author

2109 FROM blog

2110 NATURAL JOIN

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

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

2113 THEN 1 -- blog owner is an user, READABLE when

2114 WHEN (SELECT user FROM grp_mem

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

2116 THEN 0

2117 ELSE 1

2118 END;

2119 EOF

2120 }

2121 listnewblogsql() { # $1=user

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

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

2124 newdays=${newdays:-14}

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

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

2127 WHERE user='$user'

2128 AND tblrowid IN

2129 ($blogreadflagrowid,

2130 $blogcutoffflagrowid)),

2131 $basetime -- "0"

2132 );"`

2133 cat<<EOF

2134 `sql4interestblogs`

2135 WITH article_ctime as (

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

2137 FROM article join article_s s using(id)

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

2139 GROUP BY id

2140 ), blog_title_owner as (

2141 SELECT blg.rid brid, id,

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

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

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

2145 ), blogall as (

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

2147 ), news as (

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

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

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

2151 FROM blogall bl

2152 LEFT JOIN

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

2154 ON bl.brid=al.tblrowid

2155 WHERE atime < bl.ctime

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

2157 LIMIT 10

2158 ) SELECT brid LINK, "新着",

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

2160 ctime, title,

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

2162 FROM news;

2163 EOF

2164 }

2165

2166 search_form() {

2167 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

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

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

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

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

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

2173 @week → 最近一週間

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

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

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

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

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

2179 auth=""

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

2181 case "$1" in

2182 author=*)

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

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

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

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

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

2188 ;;

2189 grid=*)

2190 a=`echo "${1#grid=}"`; a=$((0 + $a))

2191 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

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

2193 ;;

2194 esac

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

2196 cat<<-EOF

2197 <div class="right">

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

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

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

2201

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

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

2204

2205 </form>

2206 </div>

2207 EOF

2208 }

2209

2210 imgsrc_cache() (

2211 # $1 = directory for cache'ing

2212 # $2 = table (user_m or grp_m)

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

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

2215 dir="$1" tbl="$2"

2216 keycond="$3"

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

2218 ORDER BY rowid DESC LIMIT 1"

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

2220 tmpf=$tmpd/imgsrc_cache.$$

2221 case "$4" in

2222 [Ss]) size=S ;;

2223 [Oo]) size=O ;;

2224 *) size=M ;;

2225 esac

2226 # ImageCache filename storing schema:

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

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

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

2230 valtype=`query "$sql0"`

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

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

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

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

2235 case "$filetype" in

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

2237 esac

2238 fi

2239 cacheimg_S=$dir/S_$filename

2240 cacheimg_M=$dir/M_$filename

2241 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

2249 else

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

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

2252 fifo2=$fifo.2

2253 mkfifo $fifo $fifo2

2254 fmt=${filename##*.}

2255 ## [[ NOTE ]]

2256 ## a. convert oldimage newimage

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

2258 ## b is much smaller than a

2259 cat $tmpf | unhexize \

2260 | tee $fifo \

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

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

2263 | tee $fifo2 \

2264 | convert - "$cacheimg_M" &

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

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

2267 | convert - "$cacheimg_S" &

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

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

2270 echo '">'

2271 echo "$sum" > $sumfile

2272 fi

2273 ## Now preparing cache image, done.

2274 ## Store this information to DB

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

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

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

2278 query <<-EOF

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

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

2281 EOF

2282 )

2283

2284 showhome() {

2285 # $1=userRowIdToShow

2286 err showhome \$1=$1

2287 case "$1" in

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

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

2290 esac

2291 ## err ShowHome: uname=$uname

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

2293 gecos=`gecos "$uname"`

2294 ## err SH:gecos=$gecos

2295 GF_VIEWONLY=1

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

2297 search_form_args=""

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

2299 if [ -z "$S4MASTERDB" ]; then

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

2301 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2302 elif [ -n "$S4MASTERURL" ]; then

2303 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2304 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2305 fi

2306 usermenu="$usermenu

2307 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2308 # Display folders

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

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

2311 and type like 'file:%';"

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

2313 nfile=`query "$sql"`

2314 # err nfile=$nfile

2315 if [ $nfile -gt 0 ]; then

2316 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2317 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2318 fi

2319 else

2320 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2321 GROUP BY user;"`

2322 usermenu="<p>Last seen on $latestlog</p>"

2323 search_form_args="author=$uname"

2324 fi

2325 . ./s4-blog.sh

2326

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

2328 search_form "$search_form_args" > $sf

2329 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2330 | htmlescape > $tf

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

2332 viewtable $formdir/user.def user $1

2333 echo "</div>"

2334 } > $pf

2335

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

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

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

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

2340 if true; then

2341 tbl=user_m

2342 enticond="name='$uname'"

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

2344 else

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

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

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

2348 echo '">'

2349 }

2350 fi > $imf

2351 fi

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

2353 val='$uname';"`

2354 listblog $uname > $bf

2355

2356 hometail=$tmpd/tail.$$

2357 mkfifo $hometail

2358

2359 #Calling listgroupbytable, originally here

2360

2361 (

2362 # Display Most Recent Entry

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

2364 shortval=${shortval:-val}

2365

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

2367 # $hidden_mode is defined in global section

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

2369 ctime,

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

2371 (SELECT gecos FROM gecoses

2372 WHERE name=(SELECT val FROM blog_s

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

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

2375 FROM blog b

2376 JOIN

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

2378 FROM article a, article_s s

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

2380 GROUP BY blogid ORDER BY val DESC LIMIT 50

2381 ) m

2382 ON b.id=m.blogid

2383 AND

2384 ('$uname' = '$user' -- user can read all own posts

2385 OR

2386 -- exclude posts of others in quiz/enquete blogs

2387 NOT EXISTS (SELECT * FROM blog_s

2388 WHERE id=b.id AND key='mode' AND val IN $hidden_mode));"

2389 # This should be as follows

2390 : <<EOF

2391 WITH arts AS(

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

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

2394 FROM article a NATURAL JOIN article_s s

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

2396 GROUP by s.id

2397 )

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

2399 FROM arts a0

2400 JOIN

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

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

2403 ORDER BY ctime DESC LIMIT 50;

2404 EOF

2405

2406 cat<<-EOF

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

2408 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2409 <div class="lcto">

2410 `DT_VIEW=replyblog dumptable html blog`

2411 </div>

2412 EOF

2413 unset DT_SQL

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

2415 # Display NEWS

2416 # 2016-06-26

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

2418 acclog blog $blogreadflagrowid

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

2420 fi

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

2422 sql=`listnewblogsql "$user"`

2423 # echo "$sql" > tmp/listnew

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

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

2426 cont=$((cont-1))

2427 ##err newcount=$cont

2428 if [ $cont -gt 0 ]; then

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

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

2431 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

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

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

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

2435 `cgi_submit '確定'`

2436 `cgi_hidden read yes`

2437 EOF

2438 echo "$new10 "

2439 echo "</div>"

2440 else # If news is 0, set log cut off flag

2441 acclog blog $blogcutoffflagrowid # for speed

2442 fi

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

2444 : # DT_SQL=

2445 fi

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

2447 #

2448 listgroupbytable $formdir/grp.def "$cond" $uname |

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

2450 -D_PROFILE_="spaste(\`$pf')$usermenu" \

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

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

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

2454 -D_NBLOG_="$nblog" \

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

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

2457 $layout/html.m4.html $layout/home.m4.html

2458

2459 # Record access log

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

2461 }

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

2463 contenttype; echo

2464 ## err commission: "$@"

2465 gname=`getgroupbyid $1`

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

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

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

2469 grp_reg_adm "$@"

2470 else

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

2472 fi

2473 }

2474 listgroupbytable() {

2475 # $1=deffile $2...=condition $3(optional)=uname

2476 tagline=`grep :tag: $1`;

2477 and="${2:+and }" where=${2:+where }

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

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

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

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

2482 for tag in `query "$sql"`

2483 do

2484 ## err ListGrp: tag=$tag

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

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

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

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

2489 as rowid,

2490 gname,

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

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

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

2494 $where$2 group by gname having tag='$tag' order by mtime desc);"

2495 ## err PersonalGroupList= `echo $sql`

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

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

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

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

2500 echo '</table>'

2501 done

2502 if [ -n "$S4WORLDLIST" -a -n "$3" ]; then

2503 err "peekgrpworlds($user) BEGIN: `gdate +%S.%03N`"

2504 peekgrpworlds mem:"$3"

2505 err "peekgrpworlds($user) END: `gdate +%S.%03N`"

2506 fi

2507 echo '</div>'

2508 }

2509 iconhref() (

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

2511 data=`percenthex "$1"`

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

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

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

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

2516 )

2517 iconhref2() (

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

2519 src=$1

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

2521 anchor=`echo $@|htmlescape`

2522 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2523 )

2524 listentry() (

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

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

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

2528 cond='' hiddens=''

2529 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2530 if [ -z "$offset" ]; then

2531 offset=`getpar start`; offset=${offset%%[!0-9]*}

2532 offset=$((offset-1))

2533 fi

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

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

2536 limit=$listentlimit

2537 dir=`getcachedir "$1"`

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

2539 hrb="$myname?home"

2540 deficon=person-default.png

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

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

2543 gcs=gecos

2544 else # if group

2545 hrb="$myname?grp"

2546 deficon=group-default.png

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

2548 gcs=name

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

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

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

2552 ## err tagconv=$tagconv

2553 fi

2554 fi

2555 if [ ! -d $dir ]; then

2556 mkdir -p $dir

2557 fi

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

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

2560 fi

2561 kwd=${2:-`getpar kwd`}

2562 if [ -n "$kwd" ]; then

2563 kwd=`echo $kwd | tr -d '";\n' | tr -d "'"`

2564 case "$kwd" in

2565 mem:*@*)

2566 byuser=${kwd#*mem:}

2567 qusr=`sqlquote "$byuser"`

2568 cond1="(a.gname IN (SELECT gname FROM grp_mem WHERE user=$qusr))"

2569 ;;

2570 esac

2571 if [ x"$1" = x"group" ]; then

2572 if [ -n "$cond1" ]; then

2573 enthead="`gecos "$byuser"|htmlescape` さんの所属"

2574 else

2575 cond1="(b.name like '%${kwd}%')"

2576 fi

2577 else

2578 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2579 fi

2580 fi

2581 tag=`getpar tag` tag2=`getpar tag2`

2582 if [ x"$tag" = x"NULL" ]; then

2583 tag="" tag2=""

2584 fi

2585 if [ -n "$tag$tag2" ]; then

2586 tag=${tag:-$tag2}

2587 qtag=`sqlquote "$tag"`

2588 cond2="tag=$qtag"

2589 fi

2590 if [ -n "$cond1$cond2" ]; then

2591 cond="$cond1${cond2:+ AND $cond2}"

2592 cond="WHERE ${cond# AND }"

2593 fi

2594

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

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

2597 ##qgrp=`sqlquote $grp`

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

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

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

2601 quote(a.$nm) as qname,

2602 (SELECT val FROM ${tbl}_s

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

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

2605 || case when a.$nm in (select gname from grp)

2606 then printf('(%d名)',

2607 (select count(user) from grp_mem where gname=a.$nm))

2608 else ' <'||a.$nm||'>'

2609 end

2610 as name,

2611 b.tag,

2612 case when a.$nm in (select user from grp_adm

2613 where gname=$getgrp) then '管理者'

2614 when '$user' in (select user from grp_adm where gname=a.$nm)

2615 then 'ADMIN'

2616 when '$user' in (select user from grp_mem where gname=a.$nm)

2617 then 'Member'

2618 when '$iamowner' = '' then ''

2619 else ',not='||a.rowid end as ownerlink,

2620 CASE '$entity'

2621 WHEN 'グループ'

2622 THEN coalesce(

2623 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2624 'open')

2625 ||

2626 CASE WHEN '$user'

2627 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2628 THEN ' ismember'

2629 ELSE ''

2630 END

2631 ELSE 'user'

2632 END regmode

2633 from $tbl a left join

2634 (select $nm as name,

2635 max(case key when 'gecos' then val end) as gecos,

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

2637 max(case key when 'mtime' then val end) as mtime,

2638 max(case key when 'wtime' then val end) as wtime,

2639 max(case key when 'login' then val end) as login

2640 from ${tbl}_s group by $nm)

2641 b on a.$nm=b.name $cond $3

2642 order by b.wtime desc, b.login desc,

2643 b.mtime desc, b.tag desc, a.rowid asc"

2644 # Give precedence to newer maintained groups (2016-09-24)

2645 # Note that mtime is stored only in grp_s.

2646 ## err LE:sql.1="$sql"

2647 total=`query "with x as ($sql) select count(*) from x;"`

2648 echo "$enthead${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2649 echo '<div class="listentry">' # List-entry div

2650 # Show owner/member filter button

2651 METHOD=GET

2652 hiddens="$hiddens

2653 `cgi_hidden stage \"$stage\"`"

2654 if [ x"$tbl" = x"grp" ]; then

2655 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2656 fh="<select name=\"tag\">$nl"

2657 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2658 for l in $args; do

2659 val=${l#*=} tname=${l%=*}

2660 if [ x"$val" = x"$tag" ]; then

2661 s=" selected"

2662 selectedtags="(種別[${tname}]のみ)"

2663 else

2664 s=""

2665 fi

2666 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2667 done

2668 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2669 cat<<-EOF

2670 <form action="$myname" method="$METHOD">

2671 </form>

2672 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2673 for="ismembtn">参加中以外隠す</label>

2674 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2675 for="isadmbtn">管理者参加以外隠す</label>

2676 EOF

2677 # limit=3

2678 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2679 fi

2680 if [ $total -gt $limit -o $ -n "$S4WORLDLIST" -a x"$tbl" = x"grp" $ ]; then

2681 echo '<div>'

2682 METHOD=GET cgi_form $stage <<EOF

2683 $form

2684 <label>次の語を含む${entity}で検索:

2685 `cgi_text kwd "$kwd"`</label>

2686 $hiddens

2687 EOF

2688 echo '</div>'

2689 else

2690 echo $selectedtags | html p

2691 fi

2692 test -n "$kwd" && hiddens="$hiddens$nl`cgi_hidden kwd \"$kwd\"`"

2693 cat<<EOF

2694 <form action="$myname" method="$METHOD">

2695 <p>${total}件中の<input class="hidesub" type="text" name="start"

2696 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2697 <input type="submit" value="確定"></p>

2698 </form>

2699 EOF

2700 if [ $((offset+limit)) -lt $total ]; then

2701 nextbtn=$(

2702 cat<<EOF

2703 <div class="right clear"><form action="$myname" method="$METHOD">

2704 `cgi_submit 次の${limit}件`

2705 $hiddens

2706 `cgi_hidden offset $((offset + limit))`</form></div>

2707 EOF

2708 )

2709 fi

2710 if [ $offset -gt 0 ]; then

2711 prevbtn=$(

2712 cat<<EOF

2713 <form action="$myname" method="$METHOD">

2714 `cgi_submit 前の${limit}件`

2715 $hiddens

2716 `cgi_hidden offset $((offset - limit))`</form>

2717 EOF

2718 )

2719 fi

2720 pnbtn="$nextbtn$prevbtn"

2721 echo $pnbtn

2722

2723 ## err ListEntry: `echo "$sql"\;`

2724 # sq $db here??? 2016-11-28

2725 query "$sql limit $limit ${offset:+offset $offset};" \

2726 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2727 # err name=$name owner=$ownerp lnk=$lnk

2728 # err newlnk=$lnk regmode=$regmode

2729 icondir=$dir/$id

2730 # Pick up only last icon

2731 htmlname=`echo $name|htmlescape`

2732 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2733 <p class=\"tag _$tag\">$tag</p>" \

2734 | _m4 $tagconv

2735 if [ -n "$NOSPEEDUP" ]; then

2736 files=`getvalbyid $tbl profimg $id $icondir`

2737 if [ -n "$files" ]; then

2738 icon=`echo "$files"|tail -1`

2739 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2740 else

2741 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2742 fi

2743 elif [ -n "$icon" -a -s "$icon" ]; then

2744 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2745 else

2746 cond="$nm=$qname"

2747 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2748 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2749 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2750 # err "img=[$img]"

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

2752 echo "<a href=\"$hrb+$lnk\">"

2753 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2754 echo "</a>"

2755 else

2756 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2757 fi

2758 fi

2759 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2760 echo "</div>"

2761 done

2762 echo "</div>" # End of List-entry div

2763 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2764 if [ -n "$kwd" -a x"$tbl" = x"grp" -a -n "$S4WORLDS" ]; then

2765 peekgrpworlds "$kwd"

2766 fi

2767 )

2768 listmember() {

2769 listentry user "$@"

2770 }

2771 listgroup() {

2772 listentry group "$@"

2773 }

2774 hexteams() { # $1=gname, $2(optional)=user

2775 cond=${2:+" AND user='$2'"}

2776 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2777 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2778 }

2779 showgroup() { # $1=group-rowid

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

2781 grid=`getpar grid`

2782 grid=${grid%%[!0-9]*}

2783 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2784 else

2785 grid=$1

2786 fi

2787 grp=`getgroupbyid $grid`

2788 qgrp=`sqlquote "$grp"`

2789 htmlgrp=`echo "$grp"|htmlescape`

2790 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2791 if isgroup "$grp"; then

2792 tf=$tmpd/title.$$

2793 sf=$tmpd/search.$$

2794 bodyclass=`query "SELECT val FROM grp_s

2795 WHERE gname=$qgrp AND key='regmode';"`

2796 if ismember "$user" "$grp"; then

2797 ismember="ismember"

2798 bodyclass="$bodyclass${bodyclass:+ }ismember"

2799 else

2800 ismember="" # bodyclass="group"

2801 fi

2802 bodyclass="$bodyclass grouphome"

2803 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2804 echo "グループ $htmlgrp" > $tf

2805

2806 showgroupsub $formdir/grp.def "$grid" | \

2807 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2808 -D_FORM_="syscmd(\`cat')" \

2809 -D_BODYCLASS_="$bodyclass" \

2810 -D_DUMPTABLE_="" \

2811 $htmlheader $sf $layout/form+dump.m4.html

2812 # $htmlheader $layout/form+dump.m4.html

2813 # $htmlheader is defined in grp()

2814 else # if $grp is removed at par2table

2815 listgroup

2816 fi

2817 }

2818 showgroupsub() {

2819 # $1=def-file $2=group-rowid

2820 # Using $ismember

2821 rowid=$2

2822 grp=`getgroupbyid $2`

2823 qgrp=`sqlquote "$grp"`

2824 td=`getcachedir grp/"$2"`

2825 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2826 if [ -z "$rowid" ]; then

2827 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2828 #grp=`sq $db "select gname from grp where rowid=$grp"`

2829 echo "showgroupsub: invalid argument($1 $2)" | html p

2830 return

2831 fi

2832 val=`getvalbyid grp profimg $rowid $tmpd`

2833 enticond="gname=$qgrp"

2834 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

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

2836 cat<<-EOF

2837 <p class="groupimg">

2838 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2839 EOF

2840 fi

2841 echo "<div class=\"noprofimg\">"

2842 viewtable $1 grp $rowid

2843 echo "</div>"

2844 if isgrpowner "$user" "$grp"; then

2845 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2846 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2847 iamowner=$rowid

2848 colmd=" mode"

2849 # We have to specify report-type blog to lshandoutall

2850 repblog=`query "

2851 WITH grpblogs AS (

2852 SELECT id FROM blog_s

2853 WHERE key='owner' AND val=(SELECT gname FROM grp WHERE rowid=$rowid)

2854 ), ownerMode AS (

2855 SELECT id,

2856 max(CASE key WHEN 'owner' THEN val END) owner,

2857 max(CASE key WHEN 'mode' THEN val END) mode

2858 FROM blog_s

2859 GROUP BY id

2860 ), blogid AS (

2861 SELECT id

2862 FROM grpblogs NATURAL LEFT JOIN ownerMode

2863 WHERE mode LIKE 'report%' LIMIT 1

2864 ) SELECT blog.rowid FROM blogid NATURAL LEFT JOIN blog;"`

2865 if [ -n "$repblog" ]; then

2866 csvid="gethandoutcsv"

2867 replink="/ <a href=\"?lshandoutall+$repblog#$csvid\">レポート全集計</a>"

2868 fi

2869 fi

2870 if [ -n "$ismember" ]; then

2871 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2872 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2873 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2874 cat<<-EOF

2875 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2876 href="?blog+$rowid">グループの新規話題作成</a>

2877 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2878 href="?grpaction+$rowid">メンバーを個別選択しての操作</a>

2879 $replink</p>

2880 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2881 <div class="fold clear">

2882 `cgi_checkbox send yes id="send"`<label

2883 for="send">グループ全員にメッセージ送信</label>

2884 <div>

2885 `cgi_textarea message "" "cols=60"`

2886 `cgi_submit 送信`

2887 `cgi_reset リセット`

2888 </div>

2889 `cgi_hidden grp $rowid`

2890 </div></form>

2891 EOF

2892 fi

2893 # 加入ボタン + 加入者リスト

2894 if [ -n "$ismember" ]; then

2895 ismem='checked' state="(参加中)"

2896 else

2897 nomem='checked' state="(現在非加入)"

2898 fi

2899 # このグループでの加入アドレス

2900 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2901 and key='email';"`

2902 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2903 ## and key='email';"

2904 ##err email=$eml

2905 cat <<EOF

2906 <div class="fold clear">

2907 `cgi_checkbox reg yes id="reg"`<label

2908 for="reg">自身の加入状態を操作する</label>$state

2909 <div>

2910 EOF

2911 cgi_form grp <<EOF

2912 <p>このグループに</p>

2913 <table class="b">

2914 <tr><th>メンバーとして</th><td>

2915 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2916 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2917 <tr><th>参加する場合のメイルアドレス<br>

2918 <small>(メインのアドレスとは違うものにする場合に記入<br>

2919 同じでよい場合は空欄に)</small></th>

2920 <td>`cgi_text email $eml`</td></tr>

2921 </table>

2922 `cgi_hidden grp $rowid`

2923 EOF

2924 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2925 echo "moderated (承認加入の)グループなので実際に参加できるのは

2926 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2927 fi

2928 echo '</div></div>'

2929 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2930 cat<<-EOF

2931 <div class="fold">

2932 <h2>話題一覧</h2>

2933 <form class="summary inline" action="$myname" title="$thelp">

2934 `cgi_hidden owner "$grp"`

2935 `cgi_hidden kwd "@week"`

2936 `cgi_hidden stage searchart`

2937 `cgi_submit "一週間のまとめ"`

2938 </form>

2939 <input type="checkbox" id="dtcheck" checked><label

2940 for="dtcheck">話題一覧表示</label>

2941 EOF

2942 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by 稼動状態, ctime desc"

2943 colstate="state:稼動状態:frozen=rowclass=凍結"

2944 frzbtn='<button class="toggle-frozen"></button>'

2945 if [ -n "$ismember" ]; then

2946 DT_COUNT=author DT_COUNT_HEADER="書" DT_COUNT_BY=$user

2947 fi

2948 DT_CHLD=article:blogid \

2949 DT_QOWNER="$qgrp" \

2950 DT_VIEW=replyblog dumptable html blog \

2951 "ctime title heading team notify:通知$colmd $colstate" "$cond" \

2952 | if [ -n "$iamowner" ]

2953 then

2954 sed -Ee "s,(<TD class=\"稼動状態\">).*(</TD>),\1$frzbtn\2,"

2955 else

2956 cat

2957 fi

2958 ## DO not convert to frzbtn when not admin

2959 echo "</div> "

2960

2961 getgname="(select gname from grp where rowid=$rowid)"

2962 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2963 cm="?commission+$rowid"

2964 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2965 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2966 # team list

2967 hexteams=`hexteams "$grp"`

2968 if [ -n "$hexteams" ]; then

2969 echo "チーム一覧" | html h2

2970 echo '<div class="dumptable"><table class="b">'

2971 sq $db -html -header<<-EOF

2972 SELECT val TEAM,

2973 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2974 MEMBERS

2975 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2976 EOF

2977 echo '</table></div>'

2978 fi

2979 }

2980 grp_getbodyclass() {

2981 # Get css class name for document.

2982 # `moderated' for moderated groups

2983 # `ismember' for groups where user belongs

2984 # $1=GroupName (w/o quote)

2985 # $user=userNameCurrentlyLogin

2986 ## err grp_getbodyclass: 1="$1"

2987 qgrp=`sqlquote "$1"`

2988 query<<-EOF

2989 SELECT coalesce(

2990 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2991 'open')

2992 ||

2993 CASE WHEN '$user'

2994 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2995 THEN ' ismember'

2996 ELSE ''

2997 END;

2998 EOF

2999 }

3000 grpaction() { # $1=group-rowid

3001 err GRP_ACTION:IN

3002 grid=${1:-`getpar grp`}

3003 grp=`getgroupbyid "$grid"`

3004 htmlgrp=`echo "$grp" | htmlescape`

3005 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3006 if [ -z "$grp" ]; then

3007 echo "無効な指定です。" | html p; return

3008 fi

3009 if ! ismember $user "$grp"; then

3010 echo "加入者のみに許可された操作です。" | html p; return

3011 fi

3012 isowner=""

3013 isgrpowner "$user" "$grp" && isowner="yes"

3014 err 2=$2 3=$3

3015 case "$2" in

3016 get:teamcsv)

3017 teamcsv "$grid"

3018 return

3019 ;;

3020 esac

3021 echo "グループ $grp 個別選択操作" \

3022 | _m4 -D_TITLE_="syscmd(\`cat')" \

3023 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

3024 $layout/html.m4.html

3025

3026 usel=`getpar usel`

3027 if [ -n "$usel" ]; then

3028 uids=$(echo `echo $usel`|tr ' ' ',')

3029 ## err grpaction-1: grp=$grp, `echo $sql`

3030 text=`getpar text|tr -d '\r'`

3031

3032 rm=`getpar rm` cfm=`getpar confirm`

3033 ## err rm=$rm cfm=$cfm

3034 if [ x"$rm" = x"yes" ]; then

3035 if [ "$isowner" ]; then

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

3037 # Eliminate

3038 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

3039 for tbl in grp_mem grp_mem_s grp_mem_m grp_adm grp_adm_s grp_adm_m

3040 do

3041 sql="delete from $tbl $cond;"

3042 # echo "sql=$sql"

3043 query "$sql"

3044 err rmGRPuser "$sql"

3045 done

3046 num=`query "select count(*) from user where rowid in ($uids);"`

3047 #err num=$num

3048 if [ 0$num -gt 0 ]; then

3049 sql="select coalesce(b.val,a.name) from user a left join \

3050 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

3051 # err `echo "$sql"`

3052 html pre<<EOF

3053 以下の${num}名のグループ $grp 登録を解除しました。

3054 `query "$sql"`

3055 EOF

3056 fi

3057 else

3058 echo "確認のチェックがないのでやめておきます。" | html p

3059 return

3060 fi

3061 else # not Group Owner

3062 echo "グループ管理者でないのでメンバー操作はできません。" | html p

3063 return

3064 fi

3065 cat<<EOF

3066

3067 EOF

3068 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

3069 if [ -z "$text" ]; then # if msg is empty

3070 echo "なにかメッセージを..." | html p

3071 return 0

3072 fi

3073 gecos=`gecos $user`

3074 safegc=`echo "$gecos" | tr -d '<>@,'`

3075 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3076 fromad=`myemail4group "$grp"`

3077 ###mail_from="$safegc <$fromad>"

3078 mail_from="$safegc <$user>" # TEST: 2020/5/13

3079 test -n `getpar sender` &&

3080 export SENDER=$user # TEST: 2020/5/15

3081 replyto=$fromad

3082

3083 ## Start parse of attachment files

3084 if [ -n "`getpar email`" ]; then

3085 ar=`getpar supprcpt`

3086 if [ -n "$ar" ]; then

3087 for a in $ar; do

3088 if checkdomain "$a"; then

3089 supprcpt="$supprcpt $a"

3090 else

3091 err "SupprcptErr=[$a] by $user"

3092 removercpt="$removercpt $a"

3093 fi

3094 done

3095 fi

3096 subj=`getpar subject`

3097 afiles=""

3098 for fn in `query "SELECT DISTINCT val FROM par WHERE var='image' AND sessid='$session';"`

3099 do

3100 f=$tmpd/$fn

3101 if [ -s $f ]; then

3102 afiles=$afiles"${afiles:+ }$f"

3103 fi

3104 done

3105 else

3106 preface=$(cat <<-EOF

3107 $url

3108 のグループ「$grp」のメンバーである $gecos さんから、

3109 あなた宛のメッセージです。

3110 ----------------------------------------------------------

3111 EOF

3112 )

3113 fi

3114 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

3115 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

3116 subj="${subj:-$gecos さんからのメッセージ}"

3117 REPLYTO=$replyto

3118 MAIL_FROM=$mail_from

3119 export REPLYTO SMAIL_TO MAIL_FROM

3120 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

3121 for r in $rcpts; do

3122 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

3123 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

3124 else

3125 # Show sender and rcpts address for guest

3126 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

3127 fi

3128 if [ -n "$afiles" ];then

3129 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

3130 else

3131 smail "$r" "$subj"

3132 fi <<EOF

3133 ${preface:+$preface$nl}$text

3134 EOF

3135 done

3136 if [ $? = 0 ]; then

3137 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

3138 sql="select coalesce(b.val, a.name) from

3139 (select name from user where rowid in ($uids)) a

3140 left join user_s b on a.name=b.name and b.key='gecos';"

3141 html pre<<EOF

3142 `query "$sql"`

3143 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

3144 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

3145 EOF

3146 err SendDone: `echo $sql`

3147 fi

3148 elif [ x"$rm" = x"commission" ]; then

3149 grp_reg_adm $grid $usel

3150 elif [ x"$rm" = x"addteam" ]; then

3151 team=`getpar team|sed "s/'/''/g"` # for single quotation

3152 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

3153 if [ x"$team" != x"$newteam" ]; then

3154 echo "チーム名に使えない文字を除去しました" | html p

3155 team=$newteam

3156 fi

3157 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

3158 cat<<-EOF | html p

3159 有効なチーム名を入力してください。

3160 カンマだけ、「なし」という名前は使えません。

3161 EOF

3162 echo "有効なチーム名を入力してください。" | html p

3163 else

3164 grp_add_team $grid "$team" $usel

3165 fi

3166 elif [ x"$rm" = x"rmteam" ]; then

3167 if [ x"yes" = x"`getpar teamconfirm`" ]; then

3168 rmteam=`getpar rmteam|sed "s/'/''/g"`

3169 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

3170 gname='$grp' AND user='$user' AND key='team'\

3171 AND val='$rmteam';\"`" ]; then

3172 grp_rm_team $grid "$rmteam" $usel

3173 else

3174 echo "所属していないチームの除去操作はできません。"|html p

3175 fi

3176 else

3177 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

3178 fi

3179 fi

3180 fi

3181 # POST count summary

3182 from=`getpar from`; to=`getpar to`

3183 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3184 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3185 fromtonote="<p title=\"Count the Number of Posts from-to\">POST/ACCESS集計: $from_input - $to_input</p>"

3186 # New entry

3187 sql="WITH mems AS (

3188 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3189 ON gm.user=g.name

3190 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3191 ), target_article AS (

3192 SELECT id FROM article_s

3193 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3194 ), posts AS (

3195 SELECT author, count(author) post

3196 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3197 WHERE blogid IN (SELECT id FROM blog_s

3198 WHERE key='owner'

3199 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3200 AND key='text'

3201 GROUP BY author

3202 ), teams AS (

3203 SELECT user, group_concat(val, ', ') team

3204 FROM mems m LEFT JOIN grp_mem_m gm ON m.name=gm.user

3205 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3206 AND key='team'

3207 GROUP BY user

3208 ), user_post AS (

3209 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3210 FROM mems m LEFT JOIN posts

3211 ON m.name=posts.author

3212 GROUP by m.rowid

3213 ), user_view AS (

3214 SELECT user vuser,count(user) cnt

3215 FROM tblaccesses

3216 WHERE user IN (

3217 SELECT user FROM grp_mem

3218 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid))

3219 AND tbl='blog'

3220 AND tblrowid IN (

3221 SELECT rowid FROM blog

3222 WHERE id IN (

3223 SELECT id FROM blog_s

3224 WHERE key='owner' AND val=(

3225 SELECT gname FROM grp WHERE rowid=$grid)))

3226 AND time BETWEEN '${from:-0000}' AND '${to:-9999}'

3227 GROUP BY user ORDER BY cnt

3228 )

3229 SELECT

3230 CASE

3231 WHEN (SELECT user FROM grp_adm

3232 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3233 AND user=up.name) IS NOT NULL

3234 then 'k'

3235 ELSE ''

3236 END || rowid || ','

3237 || rtrim(substr(name, 1, instr(name, '@')), '@') UID,

3238 gecos NAME,

3239 post POST,

3240 (coalesce((SELECT cnt FROM user_view WHERE vuser=name), 0)) ACCESS,

3241 team _TEAM_

3242 FROM user_post up LEFT JOIN teams t

3243 ON up.name=t.user

3244 ORDER BY up.name;"

3245 ## Want to count ACCESS with JOIN, but failed to produce user-unique rows

3246 ## err grpaction: "`echo \"$sql\"`"

3247 tf=$tmpd/title.$$

3248 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3249 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3250 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3251 for=\"cmadmin\">管理者委任</label>

3252 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3253 </p></div>"

3254 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3255 title=\"Shortcut: g${nl}Remove from the Group\"

3256 for=\"conf\">GRP登録解除</label>

3257 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3258 <p>この操作による通知は本人に行きません。

3259 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3260 </div>"

3261 submitBTN=`cgi_submit 送信`

3262 # Get team list to which current user belongs into $hexteams

3263 allhexteams=$(hexteams "$grp")

3264 if [ -n "$isowner" ]; then

3265 myhexteams="$allhexteams" # admin can remove all teams' attr

3266 else

3267 myhexteams=$(hexteams "$grp" "$user")

3268 fi

3269 if [ -n "$isowner" -a -n "$allhexteams" ]; then

3270 gettingcsv="<p>Download: <a href=\"?getteamcsv+$grid\">Team.csv</a> (Zoom Breakout Room 事前割り当てに使えます), <a href=\"?getteamcsv+$grid+name\">Team-with-name.csv</a>(参照用名前付)</p>"

3271 fi

3272 if [ -n "$myhexteams" ]; then

3273 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3274 title=\"Shortcut: s${nl}Strip a team tag from\"

3275 for=\"cmrmteam\">チーム属性除去</label>

3276 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3277 を除去します: `cgi_checkbox teamconfirm yes` 確認 $submitBTN

3278 <p>この操作による通知は本人に行きません。

3279 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3280 </div>

3281 "

3282 fi

3283 stf=$tmpd/selteam.$$

3284 cgi_select_h selteam "5445414d" $allhexteams > $stf

3285 b1='<label> <input type="checkbox" name="usel" value="'

3286 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3287 br='<span id="reverse" title="Reverse Selection"></span>'

3288 # lnk='"> <span></span></label> [<a href="?home+\3">HOME</a>]'

3289 lnk='<a href="?home+\3">\5</a>'

3290 # (1) Join <TR> line and the next

3291 # (2) (<TR><TD)>(k?)(1234),(userid)</TD><TD>(GECOS)</TD>

3292 # ↓

3293 # <TR><TD>\2<label><input ...value="\3">\4</label></TD> \

3294 # <TD><a href="?home+\3">\5</a></TD>

3295 cgi_form grpaction<<EOF \

3296 | sed -e '/^<TR>/{; N; s/\n//; }' \

3297 | sed -E \

3298 -e "s|^(<TR><TD>)(k?)([0-9]*),(.*)</TD><TD>(.*)</TD>|\1\2$b1\3\">\4</label></td><td>$lnk</TD>|" \

3299 -e 's/^(<TR><TD[^>]*>)k(<label)/\1\2 class="admin"/' \

3300 -e "s|^(<TR><TH>)(UID)|\1$br \2|" \

3301 | _m4 -D_TITLE_="spaste(\`$tf')" \

3302 -D_SUBTITLE_="チェック後操作ボタン" \

3303 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3304 $layout/form+dump.m4.html \

3305 | _m4 -D_TEAM_="spaste(\`$stf')"

3306 <p>下でチェックした人を対象として:</p>

3307 <div class="foldtabs">

3308 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3309 title="Shortcut: a${nl}Add a team tag to"

3310 for="cmteam">同じチーム属性を付与</label>

3311 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3312 `cgi_datalist_h teams $allhexteams`$submitBTN

3313 </div>

3314 ${rmteammsg}

3315 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3316 title="Shortcut: d${nl}DirectMail to"

3317 for="sendmsg" title="Direct Message">DM送信</label>

3318 <div>

3319 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3320 title="Using email format">email書式を使う</label>

3321 <div class="folded">

3322 <table>

3323 <tr><td>From: </td><td>$user</td></tr>

3324 <tr><td>このFrom:で送る</td>

3325 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3326 </td></tr>

3327 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3328 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3329 <tr><td>ファイル添付: </td>

3330 <td>`cgi_file image "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3331 </table>

3332 <p>(下記一覧から1人以上選択していない場合は送れません$submitBTN)</p>

3333 </div>

3334 <div>本文:`cgi_textarea text "" cols=72`

3335 </div>

3336 </div>

3337 ${isowner:+$cmmsg$excmsg}

3338 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3339 </div>

3340 <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote

3341 <table class="td3r td4r thl">

3342 `sq $db -header -html "$sql"`

3343 </table>

3344 `cgi_hidden grp $grid`

3345 `cgi_hidden myuid $myuid id="myuid"`

3346 EOF

3347 }

3348 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3349 # Create TEMPORARY VIEW

3350 query<<EOF

3351 CREATE TEMPORARY VIEW writeusers AS

3352 SELECT DISTINCT author FROM article

3353 WHERE id in (

3354 select id from article where blogid=(select id from blog where rowid=$1)

3355 );

3356 CREATE TEMPORARY VIEW movablegroups AS

3357 SELECT g.rowid growid , g.gname

3358 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3359 ON grp.gname=gm.gname -- そのユーザが属している

3360 AND user='$user') g -- グループに絞る

3361 WHERE (SELECT author FROM writeusers

3362 EXCEPT

3363 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3364 IS NULL;

3365 $2

3366 EOF

3367 }

3368 sql4readableblogs() {

3369 # Create view of blogs that can be readable by $user

3370 # Blog is readable when:

3371 # 1: blog owner is an user

3372 # 2: else, 2.1: owner-group where the $user belongs

3373 # 2.2: else, owner-group is not moderated

3374 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3375

3376 # $hidden_mode is defined in global section at head

3377

3378 cat<<EOF ## | tee tmp/sql.out

3379 CREATE TEMPORARY VIEW readableblogs AS

3380 SELECT blog.rowid rid, id, author

3381 FROM blog

3382 NATURAL JOIN

3383 (SELECT id,

3384 max(CASE key WHEN 'owner' THEN val END) owner,

3385 max(CASE key WHEN 'mode' THEN val END) mode

3386 FROM blog_s GROUP by id) bs

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

3388 THEN -- blog owner is an user, READABLE

3389 NOT mode IN $hidden_mode

3390 WHEN (SELECT val FROM grp_s

3391 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3392 AND

3393 (SELECT user FROM grp_mem

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

3395 THEN 0

3396 WHEN mode IN $hidden_mode

3397 THEN 0 -- "quiz" mode blog cannot be searched

3398 ELSE 1

3399 END;

3400 EOF

3401 }

3402 mvteamform() {

3403 owner=$1

3404 hexteams=$(hexteams "$owner" "$user")

3405 test -z "$hexteams" && return

3406 none="`echo なし|hexize`"

3407 cat<<-EOF

3408 <!-- <div class="fold">

3409 `cgi_checkbox mv2team send id="mv2team"`<label

3410 for="mv2team">この話題を以下のチームのものにする</label>

3411 <div> -->

3412 <p>この話題をチーム所有にする:

3413 チーム: `cgi_select_h mv2team $none $hexteams`</p>

3414 </form></div></div>

3415 EOF

3416 }

3417 editheading() { # $1=rowid-of-heading

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

3419 if [ -z "$rowid" ]; then

3420 echo "話題番号が未指定です。" | html p

3421 return

3422 fi

3423 owner=`getvalbyid blog owner $rowid`

3424 title=`getvalbyid blog title $rowid`

3425 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3426 | _m4 -D_TITLE_="修正" \

3427 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3428 -D_BLOGS_="" -D_DUMPTABLE_="" \

3429 -D_FORM_="syscmd(\`cat')" \

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

3431 # Move to group

3432 if isuser "$owner"; then

3433 crview4article $rowid

3434 n=`query "SELECT count(*) FROM writeusers;"`

3435 ## err N=$n

3436 if [ $((n)) -gt 0 ]; then

3437 ## err ROWID=$rowid

3438 sql="SELECT growid || ':' || gname FROM movablegroups;"

3439 cat<<-EOF

3440 <div class="fold">

3441 `cgi_checkbox mv send id="mv"`<label

3442 for="mv">この話題をグループ所有に移動する</label>

3443 <div>

3444 <form action="?mvart" method="POST" enctype="multipart/form-data">

3445 移動先グループ:

3446 <select name="mv2grp">

3447 EOF

3448 query ".mode html"

3449 query<<-EOF |

3450 $sql

3451 .mode list

3452 EOF

3453 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3454 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3455 cat<<-EOF

3456 </select>

3457 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3458 そのグループに加入しているものに限られます)</p>

3459 <p>`cgi_checkbox cfm yes`<label>確認

3460 (この操作は元に戻すことができません)</label></p>

3461 `cgi_hidden blogrowid $rowid`

3462 `cgi_submit 移動`

3463 `cgi_reset Reset`

3464 </form>

3465 </div>

3466 </div>

3467 EOF

3468 fi

3469 # end of isuser "$owner"

3470 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3471 [ -n "$hexteams" ];}; then

3472 none="`echo なし|hexize`"

3473 cat<<-EOF

3474 <div class="fold">

3475 `cgi_checkbox mv2team send id="mv2team"`<label

3476 for="mv2team">この話題を以下のチームのものにする</label>

3477 <div><p>現在の所属チーム設定:

3478 `query "SELECT

3479 coalesce((SELECT val FROM blog_s

3480 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3481 AND key='team'),

3482 ':なし');"`</p>

3483 <form action="?mvart" method="POST" enctype="multipart/form-data">

3484 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3485 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3486 `cgi_hidden blogrowid $rowid`<br>

3487 `cgi_submit 移動`

3488 `cgi_reset Reset`

3489 </form></div></div>

3490 EOF

3491 fi

3492 }

3493 mvart() { # move diary to some group or team

3494 # or move blog of group to team which belong to the group

3495 blogrowid=`getpar blogrowid`

3496 cfm=`getpar cfm`

3497 ##### echo move blog:$blogrowid to $mv2grp | html p

3498 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3499 . ./s4-blog.sh

3500 if [ -z "$blogrowid" ]; then

3501 echo "無効な指定です(mvart)。" | html p

3502 return

3503 elif [ x"$cfm" != x"yes" ]; then

3504 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3505 elif { mv2grp=`getpar mv2grp`

3506 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3507 [ -n "$mv2grp" ]; }; then

3508 crview4article $blogrowid

3509 ########## TRANSACTION BEGIN

3510 query "BEGIN;"

3511 n=`query "SELECT count(*) FROM writeusers;"`

3512 ## err Nwriteuser=$n

3513 if [ $((n)) -gt 0 ]; then

3514 query<<-EOF

3515 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3516 WHERE key='owner'

3517 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3518 AND $mv2grp IN (SELECT growid FROM movablegroups);

3519 EOF

3520 fi

3521 query "END;"

3522 ########## TRANSACTION END

3523 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3524 [ -n "$mv2team" ];}; then

3525 # blog owner can move it to ANY team

3526 case "$mv2team" in

3527 'なし')

3528 cat<<-EOF

3529 DELETE FROM blog_s

3530 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3531 AND key='team';

3532 EOF

3533 ;;

3534 "") ;;

3535 *)cat<<-EOF

3536 BEGIN;

3537 REPLACE INTO blog_s(id, key, val)

3538 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3539 'team', '$mv2team');

3540 REPLACE INTO blog_s(id, key, val)

3541 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3542 'notify', 'all'); -- Change notify to all

3543 END;

3544 EOF

3545 esac | query

3546 fi

3547 blog_reply $blogrowid

3548 echo yes | html p

3549 }

3550 editart() { # $1=article-rowid $2=blogrowid

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

3552 blogrowid=${2%%[!A-Z0-9a-z_]*}

3553 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3554 echo "表示する記事番号が未指定です。" | html p

3555 return

3556 fi

3557 owner=`getvalbyid blog owner $blogrowid`

3558 title=`getvalbyid blog title $blogrowid`

3559 author=`getvalbyid article author $rowid`

3560 math=`getvalbyid blog mathjax $blogrowid`

3561 ## err EDITart: owner=$owner, author=$author

3562 if isgrpowner "$user" "$owner"; then

3563 : EDIT OK

3564 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3565 echo "本人か所有者しか編集できません." | html p

3566 return

3567 fi

3568 aid=`query "select id from article where rowid=$rowid;"`

3569 tmpout=$tmpd/editart.$$.out

3570 GF_ACTION="?replyblog+$blogrowid#$aid" \

3571 edittable $formdir/article.def article $rowid \

3572 > $tmpout

3573 printf '%s' "${math:+$mathjax}" >> $tmpout

3574 # Cannot use pipelining to m4 with genform() because of stdin stack

3575 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3576 -D_FORM_="syscmd(cat $tmpout)" \

3577 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3578 -D_BLOGS_= -D_DUMPTABLE_= \

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

3580 }

3581 send2mem() {

3582 rowid=`getpar grp`

3583 rowid=${rowid%%[!0-9]*} # Cleaning

3584 if [ -z "$rowid" ]; then

3585 echo "グループが未指定です。" | html p

3586 return

3587 fi

3588 message=`getpar message`

3589 if [ -z "$message" ]; then

3590 echo "文章を入れてください。" | html p

3591 return

3592 fi

3593 grp=`getgroupbyid $rowid`

3594 members=`collectemail "$grp"`

3595 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3596 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3597 mailfrom="`gecos "$user"` <$mailfrom>"

3598 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3599 msg=$(cat<<-EOF

3600 $urlbase?grp+$rowid

3601 グループ $grp に所属する

3602 `gecos $user` さんよりメッセージ:

3603

3604 $message

3605 EOF

3606 )

3607 # smail rcpt subj (file)

3608 for m in $members; do

3609 echo "$msg" |

3610 MAIL_FROM=$mailfrom \

3611 SENDER=$noreply \

3612 REPLYTO=$mailfrom \

3613 smail "$m" "$sj"

3614 done

3615 cat<<EOF

3616 <p>以下のユーザに送信しました。</p>

3617 <pre>

3618 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3619 </pre>

3620 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3621 EOF

3622 }

3623 joingrpadmit() {

3624 # $1=yes/no $2=session-key

3625 if [ -z "$2" ]; then

3626 echo "bye bye" | html p; return

3627 fi

3628 t_usr=`session=$2 getpar adduser`

3629 t_grp=`session=$2 getpar group`

3630 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3631 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3632 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3633 echo "無効な加入依頼です。" | html p

3634 echo "有効期限が切れたか、

3635 他の管理者がいる場合は処理済みの可能性があります。" | html p

3636 return

3637 fi

3638 if ! isgrpowner "$user" "$t_grp"; then

3639 echo "グループ管理者のみの機能です。" | html p; return

3640 fi

3641 case $1 in

3642 yes) joingrp "$t_grp" "$t_usr" yes ;;

3643 no) joingrp "$t_grp" "$t_usr" no ;;

3644 *)

3645 echo "無効な指定です($1)。" | html p

3646 return ;;

3647 esac

3648 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3649 rcpts="`getgroupadminmails "$t_grp"` $user"

3650 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3651 body="に

3652 $t_usr

3653 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3654 しました。"

3655 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3656 query "delete from session where id='$2';"

3657 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3658 }

3659

3660 joingrprequest() {

3661 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3662 jss="joingrp-`date +%s`-`genrandom 12`"

3663 gecos=`gecos "$user"`

3664 addsession $jss +${memoplimitdays}days

3665 grpadmins=`getgroupadmins "$1"`

3666 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3667 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3668 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3669 $url

3670 $user ${gecos:+($gecos)}さんから

3671 グループ $1

3672 に加入依頼がありました。

3673

3674 承認する:

3675 $urlbase?joingrpadmit+yes+$jss

3676

3677 白紙に戻す:

3678 $urlbase?joingrpadmit+no+$jss

3679 EOF

3680 echo "管理者に加入依頼を出しました。

3681 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3682 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3683 }

3684 joingrp() {

3685 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3686 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3687 if isgrpowner "$user" "$1"; then

3688 isowner="yes"

3689 elif [ -n "$5" ]; then

3690 isowner="yes"

3691 else

3692 isowner=""

3693 fi

3694 ## err jg:isgrpowner: isowner="$isowner"

3695 if [ -n "$isowner" ]; then

3696 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3697 elif [ x"$2" != x"$user" ]; then # if user is not login user

3698 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3699 return

3700 elif [ x"$3" = x"no" ]; then

3701 : # Do not pursue those who leave

3702 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3703 : # Member can change own email address for the joining moderated group

3704 else # adding user is $user itself

3705 case `getgroupattr "$1" regmode` in

3706 moderated)

3707 joingrprequest "$@" # Request only

3708 return

3709 ;;

3710 *)

3711 ;;

3712 esac

3713 fi

3714 qgname=`sqlquote "$1"`

3715 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3716 cond="where gname=$qgname and user='$2'"

3717 if [ x"$3" = x"yes" ]; then

3718 query "replace into grp_mem values($qgname, '$2');"

3719 # Notify joingrp to admin

3720 action="に加入しました。"

3721 if [ -n "$4" ]; then

3722 if msg=`emaildomaincheck "$4"`; then

3723 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3724 'string', '$4', NULL);"

3725 else

3726 echo $msg

3727 fi

3728 else

3729 query "delete from grp_mem_s $cond and key='email';"

3730 fi

3731 if [ -n "$5" ]; then # as ADMIN

3732 # Coming here means newly created group

3733 sql="select case\

3734 when (select count(*) from grp_mem where gname=$qgname)=1\

3735 then (select user from grp_mem\

3736 where gname=$qgname and user='$user')\

3737 else '' end; "

3738 err NewGrpChk: $sql

3739 if [ -n "`query \"$sql\"`" ]; then

3740 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3741 query "replace into grp_adm values($qgname, '$user');"

3742 fi

3743 fi

3744 else

3745 query "begin;

3746 delete from grp_mem $cond;

3747 delete from grp_mem_s $cond;

3748 delete from grp_mem_m $cond;

3749 delete from grp_adm $cond;

3750 delete from grp_adm_s $cond;

3751 delete from grp_adm_m $cond;

3752 end;"

3753 action="から脱退しました。"

3754 fi

3755 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3756 $url?grp+$grid

3757 $user (`gecos $user`)さんが

3758 グループ $1

3759 $action

3760 EOF

3761 }

3762 grp_add_team() (

3763 # $1=grp-rowid $2=team $3...=user-rowid(s)

3764 grp=`getgroupbyid $1`

3765 team=$2; shift; shift

3766 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3767 { echo "BEGIN;"

3768 for user; do

3769 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3770 '$grp',\

3771 (SELECT name FROM user WHERE rowid=$user),\

3772 'team', 'string', '$team');"

3773 done

3774 echo "END;"

3775 } | query

3776 )

3777 grp_rm_team() (

3778 # $1=grp-rowid $2=team $3...=user-rowid(s)

3779 grid=$1

3780 qgrp=$(sqlquote "`getgroupbyid $grid`")

3781 team=$2; shift; shift

3782 [ -z "$grid" -o -z "$team" ] && return

3783 { echo "BEGIN;"

3784 for user; do

3785 echo "DELETE FROM grp_mem_m\

3786 WHERE gname=$qgrp \

3787 AND user=(SELECT name FROM user WHERE rowid=$user)\

3788 AND key='team' AND val='$team';"

3789 done

3790 cat<<-EOF

3791 DELETE FROM blog_s

3792 WHERE rowid=(

3793 SELECT rowid

3794 FROM blog_s a

3795 WHERE key='team'

3796 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3797 AND NOT EXISTS (SELECT * FROM grp_mem_m

3798 WHERE key='team' AND val=a.val -- a.val=team

3799 AND gname = (SELECT val FROM blog_s b

3800 WHERE a.id=b.id AND key='owner')

3801 ));

3802 EOF

3803

3804 echo "END;"

3805 } | query

3806 )

3807 grp_reg_adm() {

3808 # $1=grp-rowid $2...=user-rowid

3809 grid=$1

3810 grp=`getgroupbyid "$1"`

3811 if [ -z "$grp" ]; then

3812 echo "無効なグループIDです" | html p; return

3813 fi

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

3815 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3816 fi

3817 shift

3818 for urid; do

3819 newadm=`query "select name from user where rowid=$urid;"`

3820 if [ -z "$newadm" ]; then

3821 echo "指定ユーザIDがおかしいようです。" | html p; return

3822 fi

3823 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3824 err ismember $newadm $grp

3825 if ismember $newadm "$grp"; then

3826 # OK, go ahead

3827 getgname="(select gname from grp where rowid=$grid)"

3828 query "replace into grp_adm values($getgname, '$newadm');"

3829 # confirm insertion

3830 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3831 if [ -n "`query \"$sql;\"`" ]; then

3832 echo "追加完了: $newadm" | html p

3833 else

3834 echo "追加失敗($1 $urid)" | html p

3835 fi

3836 fi

3837 showgroup $grid

3838 done

3839 }

3840 dt_colhack() {

3841 # FROM: <TD>xxx:::yyy</TD>

3842 # TO: <TD class="xxx">yyy</TD>

3843 sed -Ee 's,<TD>([^:<"]+)'$asdelim'([^<]*)(</TD>|$),<TD class="\1">\2\3,g'

3844 }

3845 dt_rowhack() {

3846 # From: <TR>

3847 # ....

3848 # <TD>rowclass=foo</TD>

3849 # </TR>

3850 # To: <TR class="foo">....<TD>foo</TD></TR>

3851 sed -e '

3852 /^<TR>/ {

3853 :loop

3854 s/\n//

3855 N

3856 /<\/TR>/ {

3857 s/\n//

3858 s,^<TR>$.*$<TD$[^>]*$>rowclass=$.*$$</TD></TR>$,<TR class="\3">\1<TD\2>\3\4,

3859 n

3860 }

3861 $q

3862 b loop

3863 }'

3864 }

3865 dumptable() {

3866 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3867 # textのフィールドだけ全てダンプにしたほうがいいか

3868 # $DT_VIEW sets link

3869 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3870 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3871 VIEW=${DT_VIEW-replyblog}

3872 if [ -n "$VIEW" ]; then

3873 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3874 fi

3875 sqlfile=$tmpd/dump.sql

3876 : > $sqlfile # ensure to be empty

3877 printf '.mode html\n.header 1\n' > $sqlfile

3878 # $DT_CHLD=ChildTable:BindColumn (eg. article:blogid)

3879 if [ -n "$DT_CHLD" ]; then

3880 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3881 cat<<-EOF >> $sqlfile

3882 -- presql

3883 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3884 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3885 EOF

3886 # Speed up counting of new articles

3887 dt_count=${DT_COUNT:+"CREATE TEMPORARY VIEW _dtcount AS

3888 SELECT a.id $_i, $DT_COUNT, coalesce(cnt2, 0) cnt2

3889 FROM (SELECT DISTINCT id FROM _target) a

3890 LEFT JOIN

3891 (SELECT blogid,$DT_COUNT,count($DT_COUNT) cnt2

3892 FROM $_t

3893 WHERE $DT_COUNT = '$DT_COUNT_BY'

3894 GROUP BY $_i,$DT_COUNT) b

3895 ON a.id=b.$_i

3896 ;"}

3897 cat<<-EOF >> $sqlfile

3898 -- presql2

3899 DROP TABLE IF EXISTS _counts;

3900 CREATE TEMPORARY TABLE _counts AS

3901 SELECT $_i, count($_i) cnt

3902 FROM $_t GROUP BY $_i;

3903 /* Prepare NEW count table */

3904 CREATE TEMPORARY TABLE _target AS

3905 SELECT b.rowid trowid, b.id

3906 FROM "$2" b JOIN "$2_s" s

3907 ON b.id=s.id AND s.key='owner'

3908 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3909

3910 DROP TABLE IF EXISTS _children;

3911 CREATE TEMPORARY TABLE _children AS

3912 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3913 FROM (SELECT t.trowid, t.id $_i, a.id

3914 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3915 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3916

3917 DROP TABLE IF EXISTS _news;

3918 DROP VIEW IF EXISTS _news;

3919

3920 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3921 -- INSERT INTO _news

3922 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3923 CREATE TEMPORARY VIEW _news AS

3924 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3925 FROM (SELECT DISTINCT id FROM _target)

3926 a LEFT JOIN

3927 (SELECT $_i, count(ctime) newcnt

3928 FROM _children x

3929 WHERE ctime > coalesce((SELECT time from myacclog

3930 WHERE tblrowid=x.trowid),

3931 '1970-01-01')

3932 GROUP BY $_i) b

3933 ON a.id=b.$_i;

3934 $dt_count

3935 EOF

3936 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3937 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3938 cntmine=${DT_COUNT:+"(SELECT cnt2 FROM _dtcount WHERE $_i=a.id)"}

3939 cnt="$cntnew as '新着', $cntall as '総数', "

3940 cnt=$cnt${DT_COUNT:+"$cntmine as '$DT_COUNT_HEADER',"}

3941 dt_class=" td2r td3r td4r dumpblogs"

3942 fi

3943 # Construct join expression

3944 eav="" scols=""

3945 pk=`gettblpkey $2`

3946 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3947 substr=${substr:-%s}

3948 for col in ${3:-`gettbl_s_cols $2`}; do

3949 valvar=val

3950 fromtbl=b

3951 if gettblcols "$2" | grep -w "$col" >/dev/null 2>&1; then

3952 # If $col belongs to master table

3953 fromtbl=a; col=${col#a.}

3954 fi

3955 case $col in

3956 gecos) scols="$scols${scols:+, }${col#}"

3957 continue ;; # built-in column name

3958 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3959 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3960 case "$as" in

3961 *:*=*) cnd=${as#*:}

3962 h=${cnd%%=*} v=${cnd#*=}

3963 h=`sqlquotestr "$h"`

3964 v=`sqlquotestr "$v"`

3965 if [ x"$fromtbl" = x"b" ]; then

3966 valvar="CASE val WHEN $h THEN $v END"

3967 else

3968 valvar="$h"

3969 fi

3970 as=${as%%:*} ;;

3971 esac

3972 ;;

3973 *) as=${col} ;;

3974 esac

3975 ss=`printf "$substr" "$valvar"`

3976 if [ x"$fromtbl" = x"b" ]; then

3977 eav=$eav${eav:+,}" \"$as$asdelim\"||coalesce(max(case key when '$col' then $ss end), '') as $as"

3978 else

3979 eav=$eav${eav:+,}" \"$as$asdelim\"||$ss as $as"

3980 fi

3981 scols="$scols${scols:+, }${fromtbl}.$as"

3982 done

3983 #case author when '$user' then a.rowid else '---' end as ID,

3984 if [ -n "$DT_SQL" ]; then

3985 echo "$DT_SQL"

3986 else

3987 cat<<-EOF

3988 SELECT a.rowid as LINK, $cnt $scols

3989 FROM $2 a LEFT JOIN

3990 (SELECT $pk,$eav,

3991 max(CASE key

3992 WHEN 'owner'

3993 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3994 as gecos

3995 FROM ${2}_s c GROUP BY $pk)

3996 b ON a.$pk=b.$pk $4;

3997 EOF

3998 fi >> $sqlfile

3999 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

4000 sqlog<<-EOF

4001 *** SQL-file: $sqlfile ***

4002 `cat $sqlfile`

4003 EOF

4004 printf '.mode list\n.header 0\n' >> $sqlfile

4005 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_colhack | dt_rowhack

4006 <div>

4007 <div class="dumptable">

4008 <table class="b$dt_class">

4009 `query ".read $sqlfile"`

4010 </table>

4011 </div>

4012 </div>

4013 EOF

4014 }

4015

4016 clean-orphaned() {

4017 # This shoud be done by foreign_key rules, but some db lack them

4018 query<<-EOF

4019 -- Find blogs that have no parent

4020 WITH orphanedblog AS (

4021 SELECT blog.id,val FROM blog JOIN blog_s bs

4022 ON blog.id=bs.id AND key='owner'

4023 WHERE val NOT IN (SELECT gname FROM grp)

4024 AND val NOT IN (SELECT name FROM user)

4025 ) -- Remove them

4026 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

4027

4028 -- Find articles that have no parent blog

4029 WITH orphanedarticle AS (

4030 SELECT id FROM article

4031 WHERE blogid NOT IN (SELECT id FROM blog)

4032 ) -- Remove them

4033 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

4034 EOF

4035 }

4036

4037 clearcachedir() (

4038 td=`getcachedir "$1"`

4039 err td=$td: ls- `ls $td`

4040 if [ -w "$td/image.1" ]; then

4041 err Removing td=$td

4042 rm -fr $td/ # Clear icon-image cache!

4043 fi

4044 )

4045

4046 par2table() (

4047 # copy current parameters of par into destination table

4048 # $1=definition-file

4049 # Using $user and $session

4050 # Return value:

4051 # 0: Stored successfully

4052 # 1: Insufficient fillings

4053 # 2: No permission to modify the record

4054 # 3: Invalid rowid

4055 # 4: SUCCESS to delete

4056 # 5: Stop deletion for lack of confirm check

4057 # 6: Password length too short

4058 # 7: Password mismatch

4059 # 8: Old password incorrect

4060 # 9: Duplicated post

4061 rowid=`getpar rowid`

4062 if [ ! -e $1 ]; then

4063 echo "テーブル定義ファイルが見付かりません" | html p

4064 exit 1

4065 fi

4066 tbl=${1%.def}

4067 tbl=${tbl##*/}

4068 if [ -n "$rowid" ]; then # Modify existing entry

4069 if [ x"$tbl" = x"user" ]; then

4070 rowowner=`query "select name from $tbl where rowid=$rowid;"`

4071 elif [ x"$tbl" = x"grp" ]; then

4072 sql="select gname from $tbl where rowid=$rowid;"

4073 ##err p2t:grp:q $sql

4074 isgrpowner "$user" "`query $sql`" && rowowner=$user

4075 elif [ x"$tbl" = x"blog" ]; then

4076 # Check if owner in blog_s

4077 blogowner=`getvalbyid blog owner "$rowid"`

4078 if isgrpowner "$user" "$blogowner"; then

4079 rowowner=$user

4080 origauthor=`getvalbyid blog author $rowid`

4081 if [ -n "$origauthor" -a x"$user" != x"$origauthor" ];

4082 then # Keep original author

4083 setpar author string "$origauthor" # if differs from $user

4084 fi # 2021-11-06 suggd.by Ruri

4085 else

4086 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4087 fi

4088 else

4089 # 2016-12-05 There's no owner column in $tbl (need confirmation)

4090 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4091 fi

4092 ### err rowowner=$rowowner

4093 if [ x"$user" != x"$rowowner" ]; then

4094 echo "他人のレコードはいじれないの" | html p

4095 return 2

4096 elif [ -z "$rowowner" ]; then

4097 echo "指定したレコードはないみたい" | html p

4098 return 3

4099 fi

4100 rm=`getpar rm` cfm=`getpar confirm`

4101 # Editing existent entry

4102 if [ x"$rm" = x"yes" ]; then

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

4104 query "delete from $tbl where rowid=$rowid;"

4105 clearcachedir "$tbl/$rowid"

4106 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

4107 clean-orphaned

4108 fi

4109 return 4

4110 else

4111 echo "消去確認のチェックがないので消さなかったの..." | html p

4112 return 5

4113 fi

4114 fi

4115 fi

4116

4117 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

4119 # Update of existing record

4120 for col in `gettblcols $tbl`; do

4121 val=`getparquote $col`

4122 [ -z "$val" ] && continue

4123 ## err query "update $tbl set $col=$val where rowid=$rowid"

4124 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

4125 sql="update $tbl set $col=$val where rowid=$rowid;"

4126 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

4127 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

4128 ## User name cannot be changed with interface provided with this

4129 ## script. But we offer the trigger to change owner user

4130 ## of blog_s table.

4131 #err "select quote($col) from $tbl where rowid=$rowid;"

4132 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

4133 cat<<-EOF | query

4134 -- Here we cannot use BEGIN-COMMIT because groupupdate()

4135 -- should use EXCLUSIVE transaction outside of this.

4136 SAVEPOINT par2table;

4137 $sql

4138 update blog_s set val=$val

4139 where key='owner' and val=$old;

4140 RELEASE SAVEPOINT par2table;

4141 EOF

4142 ## XX: DIRTY Hack Ends here

4143 ## We should keep blog's owner as a single column which has

4144 ## foreign key constraint with primary key of grp/user.

4145 else

4146 query "$sql"

4147 fi

4148 done

4149 # Then, set up $pval for further insertion of tbl_s and tbl_m

4150 for col in `gettblpkey $tbl`; do

4151 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

4152 pval="$pval${pval:+, }\"$val\""

4153 done

4154 else

4155 # New entry

4156 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

4157 # Avoid empty repost of article.

4158 if [ x"$tbl" = x"article" ]; then

4159 # If rowid is empty and ID exists in article-table, that is REPOST!

4160 aid=`getpar id`

4161 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

4162 if [ -n "$xaid" ]; then

4163 # REPOST of article

4164 html p <<-EOF

4165 書き込み直後のリロードなので上書きを回避します。

4166 最新記事は末尾の「再読み込み」ボタンから見てください。

4167 EOF

4168 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

4169 return 9 # STOP Duplicated posting

4170 fi

4171 fi

4172 # Generate values() for primary keys

4173 for col in `gettblpkey $tbl`; do

4174 # Genuine primary keys for _m and _s

4175 val=`getvalquote $tbl $col`

4176 [ -z "$val" ] && continue

4177 pval="$pval${pval:+, }$val"

4178 done

4179 ##err pval=$pval

4180 for col in `gettblfkey $tbl`; do

4181 # args for values() to insertion into master table

4182 val=`getvalquote $tbl $col`

4183 [ -z "$val" ] && continue

4184 formaster=$formaster"${formaster:+, }$val"

4185 done

4186 formaster="$pval${formaster:+, }$formaster"

4187 ## err formaster=$formaster

4188 if [ -z "$formaster" ]; then

4189 echo "項目を全て埋めてください" | html pre

4190 return 1

4191 fi

4192 ## err "replace into $tbl values($formaster);"

4193 query "replace into $tbl values($formaster);"

4194 ## Insertion to master table, done

4195 fi

4196

4197 transaction=$tmpd/sqlfile.sql; touch $transaction

4198 for kt in s m; do

4199 tb2=${tbl}_$kt

4200 for col in `gettbl_${kt}_cols $tbl`; do

4201 ptype=`getpartype $col "limit 1"`

4202

4203 # First, check update of existing entries in _m

4204 if [ $kt = m ]; then

4205 # sessID|address.1.22|string|Somewhere-x.y.z

4206 sql=""

4207 ##err dots from query "select var from par where var like '$col.%';"

4208 for v in `query "select var from par where var like '$col.%' AND sessid='$session';"`; do

4209 # v=address.1.22

4210 st_rowid=${v##*.}

4211 origcol=${v%%.*} # original column derived from

4212 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

4213 ##case `getpartype $v` in

4214 ## err CASE `gettbl_coltype $tbl/$origcol` in

4215 ## err edit flag = `getpar action.$v`

4216 case `getpar action.$v` in

4217 rm)

4218 if [ x`getpar confirm.$v` = x"yes" ]; then

4219 newsql="delete from $tb2"

4220 else

4221 echo "削除確認未チェック" | html p

4222 fi ;;

4223 edit)

4224 case `gettbl_coltype $tbl/$origcol` in

4225 image|document|binary)

4226 file=$tmpd/`getparfilename $v`

4227 if [ ! -s "$file" ]; then # Maybe stupid REPOST

4228 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

4229 continue

4230 fi

4231 ## err type=file=$file

4232 [ -z "$file" ] && continue

4233 bn=`sqlquotestr "${file##*/}"`

4234 bin="X'"$(hexize "$file")"'"

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

4236 type=\"file:$ct\"

4237 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

4238 cachedir=`getcachedir "$tbl/$rowid"`

4239 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

4240 rm -rf $cachedir

4241 ;;

4242 *)

4243 newsql="update $tb2 set val=(select val from par where var \

4244 like '$col.%.$st_rowid' AND sessid='$session')"

4245 ;;

4246 esac

4247 ;;

4248 mv)

4249 # regularize filename and strip directory part

4250 newname=`getpar mv.$v|tr -d '":;#<>?^%$!'|tr -d "'"|tr ' ' _`

4251 newname=`basename $newname`

4252 oldname=`query "SELECT val FROM $tb2 WHERE rowid=$st_rowid;"`

4253 oldext=`expr "$oldname" : '.*\.$.*$'`

4254 newext=`expr "$newname" : '.*\.$.*$'`

4255 err "p2t(mv): oldname=$oldname $oldext -> newname($v)=$newname $newext"

4256 if [ -n "$newname" -a x"$oldext" = x"$newext" ];

4257 then

4258 newsql="UPDATE $tb2 SET val='$newname'"

4259 else

4260 html p<<-EOF

4261 $newname は取り扱えないファイル名です。

4262 空白を含まない名前にして下さい。

4263 拡張子の変更もできません。

4264 EOF

4265 continue

4266 fi

4267 ;;

4268 *) # maybe "keep", do not modify value

4269 continue

4270 ;;

4271 esac

4272 # err newsql=$newsql

4273 sql=$sql$nl"$newsql where rowid=$st_rowid;"

4274 clearcachedir "$tbl/$rowid"

4275 done

4276

4277 if [ x"$bin" = x"NULL" ]; then

4278 ## err repl:normal sql=`echo $sql`

4279 if [ -n "$transaction" ]; then

4280 cat<<-EOF >> $transaction

4281 $sql

4282 DELETE FROM $tb2 WHERE type='string' AND val='';

4283 EOF

4284 else

4285 query "$sql

4286 delete from $tb2 where type='string' and val='';"

4287 ## err repl:normal done

4288 fi

4289 else

4290 # Binary update line is TOO LONG to pipelining

4291 sqlfile="$tmpd/sqlf.$$"

4292 if [ -n "$transaction" ]; then

4293 cat<<-EOF >> $transaction

4294 $sql

4295 EOF

4296 else

4297 cat<<-EOF > $sqlfile

4298 $sql

4299 EOF

4300 query ".read $sqlfile"

4301 fi

4302 fi

4303 # Rest of kt==m: set multiple mode

4304 nr=`getparcount $col`

4305 else

4306 nr=1 # for kt==s, number of records is 1

4307 fi

4308

4309 i=0

4310 while [ $i -lt $nr ]; do

4311 limit="limit 1 offset $i"

4312 i=$((i+1)) # increase beforehand against continue

4313 val=`getvalquote $tbl $col "$limit"`

4314 ##XXX [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4315 [ -z "$val" -o x"$val" = x'""' ] && continue

4316 ## err $col=$val

4317 bin=NULL

4318 ## err partype$col=`getpartype $col "$limit"`

4319 ptype=`getpartype $col "$limit"` # partype should be obtained each time

4320 case $ptype in

4321 file) file=$tmpd/`getparfilename $col "$limit"`

4322 ## err parfile-$col=$file

4323 [ -z "$file" ] && continue

4324 bin="X'"$(hexize "$file")"'"

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

4326 type=\"file:$ct\" ;;

4327 "*"*) continue ;; # foreign table

4328 *) type=\"string\" ;;

4329 esac

4330 case `gettbl_coltype $tbl/$col` in

4331 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx]|[Tt][Ee][Xx][Tt])

4332 test x"$val" = x"NULL" && val="''"

4333 ;;

4334 password) # special care for password

4335 # name={password,pswd1,pswd2}

4336 p1=`getpar pswd1 "$limit"`

4337 if [ -z "$p1" ]; then

4338 continue # SKIP password setting, if p1 is empty

4339 else

4340 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4341 ## err pswd=$pswd

4342 if pwcheck "$pswd"; then

4343 if [ x"$p1" = x"$p2" ]; then

4344 case "$p1" in

4345 ??????????*) ;;

4346 *) echo "パスワードは10字以上にしてください。" | html p

4347 return 6;;

4348 esac

4349 val="\"`echo $p1|mypwhash`\""

4350 else

4351 echo "2つの新パスワード不一致" | html p

4352 return 7

4353 fi

4354 else

4355 echo "旧パスワード違います" | html p

4356 return 8

4357 fi

4358 fi

4359 ;;

4360 esac

4361 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4362 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4363 if [ x"$val" = x"NULL" -a x"$bin" = x"NULL" ]; then

4364 continue # Do not insert completely NULL record 2021-11-08

4365 fi

4366 sql="REPLACE into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4367 if [ x"$bin" = x"NULL" ]; then

4368 ## err Normal-query: `echo $sql`

4369 if [ -n "$transaction" ]; then

4370 printf '%s' "$sql" >> $transaction

4371 else

4372 query "$sql"

4373 fi

4374 else

4375 sqlfile="$tmpd/query.$$"

4376 ## err sqlfile=`ls -lF $sqlfile`

4377 if [ -n "$transaction" ]; then

4378 cat<<-EOF >> $transaction

4379 $sql

4380 EOF

4381 else

4382 cat<<-EOF >> $sqlfile

4383 $sql

4384 EOF

4385 query ".read $sqlfile"

4386 fi

4387 fi

4388 ## err p2t done

4389 done

4390 done

4391 done

4392 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4393 -- We cannot use transaction here, because groupupdate may use it.

4394 SAVEPOINT pa2table_insert;

4395 .read $transaction

4396 RELEASE SAVEPOINT pa2table_insert;

4397 EOF

4398 rc=$?

4399 [ $rc -eq 0 -a x"$tbl" = x"user" ] && flag_profupdate

4400 ## err "Table:$tbl update done "

4401 return $rc

4402 )

4403 genform() {

4404 # $1 = form definition file

4405 # $2, $3 (optional)= table name and ROWID

4406 # If $GF_VIEWONLY set and nonNull, output values without form

4407 # If $GF_ARGS set, use it as content-strings in the form

4408 # If $GF_OWNER set, use it as value of name="owner"

4409 # If $GF_STAGE set, use it as value of name="stage"

4410 forms="" hiddens="" rowid=$3

4411 if [ ! -e "$1" ]; then

4412 echo "そのようなデータベースはないようです($2)。" | html p

4413 return

4414 elif [ -n "$2" ]; then

4415 rec=`query "select * from $2 where rowid='$rowid';"`

4416 if [ -z "$rec" ]; then

4417 pk=`gettblpkey $2`

4418 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4419 rec=`query "select rowid from $2 where $pk='$rowid';"`

4420 rowid=$rec

4421 rec=$3

4422 fi

4423 if [ -z "$rec" ]; then

4424 echo "そんなレコードはないみたいね..." | html p

4425 return

4426 fi

4427 fi

4428 if [ -z "$GF_VIEWONLY" ]; then

4429 rm='<input id="rm" name="rm" type="checkbox"

4430 value="yes"><label for="rm">このエントリの削除</label>

4431 <span>ほんとうに消しますよ(確認)!

4432 <input name="confirm" type=checkbox value="yes">はい</span>'

4433 fi

4434 # Image Cache dir

4435 ## err genform: getcache=$2/$rowid

4436 td=`getcachedir "$2/$rowid"`

4437 while IFS=: read -r prompt name keytype type args; do

4438 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4439 sp="${args:+ }"

4440 form="" val=""

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

4442 # err genform2a: Seeking for "$2.$name, type=$type"

4443 rawval=`getvalbyid $2 $name $rowid $td`

4444 val=`printf '%s\n' "$rawval"|htmlescape`

4445 ## err genform3a: getvalbyid $2 $name $rowid $td

4446 ## err genform3b: val="[$val]" type="$type"

4447 fi

4448 if [ -n "$GF_VIEWONLY" ]; then

4449 is_hidden "$2" "$name" && continue

4450 fi

4451 case "$type" in

4452 text*)

4453 cgiform=cgi_multi_$type

4454 if [ -s $td/$name.count -a -n "$val" ]; then

4455 form=`$cgiform $name $td`

4456 val=$(printf '%s\n' "$val"|

4457 while read fn; do

4458 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4459 </td></tr>$nl"

4460 done)

4461 val="<table>$nl$val$nl</table>"

4462 else

4463 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4464

4465 form=`cgi_$type $name "$rawval" "$args"`

4466 fi

4467 ;;

4468 [Rr][Aa][Dd][Ii][Oo])

4469 fh="<label><input type=\"radio\" name=\"$name\""

4470 form="`echo $args|sed -e \

4471 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4472 ;;

4473 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4474 checked=${val:+ checked}

4475 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\"$checked>${args%=*}</label>"

4476 ;;

4477 [Ss][Ee][Ll][Ee][Cc][Tt])

4478 fh="<select name=\"$name\">$nl"

4479 form=$(for l in $args; do

4480 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4481 done)

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

4483 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4484 fi

4485 form="$fh$form</select>"

4486 ;;

4487 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4488 if [ -s $td/$name.count ]; then

4489 form=`cgi_multi_file $name $td "$args"`

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

4491 hrfb="$myname?showattc+$2_m"

4492 val=$(echo "$rawval" \

4493 | while read fn; do

4494 data=`percenthex "$td/$fn"`

4495 #ct=`cat $td/$fn.content-type`

4496 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4497 ri=`cat "$td/$fn.rowid"`

4498 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4499 #imgsrc="<img src=\"data:$ct,$data\">"

4500 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4501 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4502 done)

4503 fi

4504 else

4505 form="<input type=\"file\" name=\"$name\" $args>"

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

4507 imgs=$(echo "$rawval"\

4508 |while read fn;do

4509 data=`percenthex "$td/$fn"`

4510 echo "<img src=\"data:image/png,$data\">$fn<br>"

4511 done)

4512 form=$form"<br>$imgs"

4513 val=$imgs # 2015-06-15

4514 else

4515 form="<input type=\"file\" name=\"$name\" $args>"

4516 fi

4517 fi

4518 ;;

4519 [Hh][Ii][Dd][Dd][Ee][Nn])

4520 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4521 args="value=\"$GF_STAGE\""

4522 fi

4523 form="<input type=\"hidden\" name=\"$name\" $args>"

4524 prompt='' # Remove prompt

4525 ;;

4526 [Aa][Uu][Tt][Hh][Oo][Rr])

4527 [ -n "$GF_VIEWONLY" ] && continue

4528 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4529 prompt="" ;;

4530 [Oo][Ww][Nn][Ee][Rr])

4531 [ -n "$GF_VIEWONLY" ] && continue

4532 val=${GF_OWNER:-$val}

4533 val=${val:-$user}

4534 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4535 prompt="" ;;

4536 [Uu][Ss][Ee][Rr])

4537 # XXX: is null $user ok?

4538 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4539 [ -n "$GF_VIEWONLY" ] && continue

4540 form="$user"

4541 ;;

4542 [Pp]assword)

4543 [ -n "$GF_VIEWONLY" ] && continue

4544 form="`cgi_passwd`"

4545 val=""

4546 ;;

4547 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4548 [ -n "$GF_VIEWONLY" ] && continue

4549 if [ -z "$rowid" ]; then

4550 val=`genserial`

4551 fi

4552 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4553 prompt="" ;;

4554 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4555 prompt=""

4556 ;;

4557 parent|path|blog*)

4558 prompt=""

4559 ;;

4560 "*"*)

4561 tail=$tail"``"

4562 continue ;;

4563 esac

4564 if [ -n "$prompt" ]; then

4565 if [ -n "${GF_VIEWONLY}" ]; then

4566 form=$val

4567 else

4568 :

4569 fi

4570 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4571 else

4572 hiddens=$hiddens$nl"$form"

4573 fi

4574 done < $1

4575 # enctype="multipart/form-data"

4576 cat<<-EOF

4577 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4578 EOF

4579 test -n "$rowid" && printf '%s\n' "$rm" # Workaround for utf8 buggy NetBSD sh

4580 cat<<EOF

4581 <table class="b $2">

4582 $forms

4583 </table>$hiddens

4584 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4585 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4586 EOF

4587 if [ -z $GF_VIEWONLY ]; then

4588 cat<<EOF

4589 <input type="submit" name="sub" value="OK">

4590 <input type="reset" name="res" value="Reset">

4591 EOF

4592 fi

4593 cat<<EOF

4594 $GF_ARGS</form>

4595 $tail

4596 EOF

4597 }

4598 edittable() {

4599 # $1=form-def $2=table $3 rowid

4600 genform "$@"

4601 }

4602 viewtable() {

4603 GF_VIEWONLY=1 genform "$@"

4604 }

4605 showattc() {

4606 # $1=table_m $2=rowid &optional $3=RawFlag

4607 ## err \$1=$1 \$2=$2 \$3=$3

4608 if ! isfilereadable $user $1 $2; then

4609 contenttype; echo

4610 echo "このファイルは管理者のみしか見られません" | html p

4611 putfooter; exit

4612 fi

4613 idir=`umask 002; mktempd` || exit 1

4614 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4615 bin=$idir/$myname-$$.bin

4616 sql="select quote(bin) from $1 where rowid='$2';"

4617 ## err showattc: sql: $sql

4618 sq $db "$sql" | unhexize > $bin

4619 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4620 type=${tv%//*} fn=${tv#*//}

4621 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4622 ct=${type#file:}

4623 case $ct in # all text/* changed to text/plain

4624 text/*|application/csv|application/json)

4625 charset=`nkf -g $bin|cut -d' ' -f1`

4626 case $charset in

4627 ASCII*) charset="" ;;

4628 esac

4629 if [ -z "$3" ]; then

4630 ct="text/html${charset:+; charset=$charset}"

4631 link="?showattc+$1+$2+raw"

4632 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4633 | sed 's,^,<span></span>,' \

4634 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4635 -D_LINK_="$link" \

4636 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4637 exit $?

4638 fi

4639 ct="text/plain${charset:+; charset=$charset}"

4640 ;;

4641 video/*)

4642 if [ -z "$3" ]; then

4643 _m4 -D_TITLE_="$fn" \

4644 -D_SRC_="?showattc+$1+$2+raw" $layout/videoplay.m4.html

4645 exit $?

4646 fi

4647 esac

4648 contenttype "$ct"

4649 echo "Content-Disposition: filename=\"$fn\""

4650 echo "Content-Length: " `cat $bin | wc -c`; echo

4651 #echo "Content-Type: " ${type#file:}; echo

4652 cat $bin

4653 }

4654 #

4655 # Some default stupid handler on CGI values

4656 #

4657 default_storedb() {

4658 # ARG: $1=table-def-file

4659 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4660 tbl=`basename $1`

4661 tbl=${tbl%.def}

4662 cols="`grep :text $1|cut -d: -f2`"

4663 col=`echo "$cols"|head -1`

4664 vcol=`getpar $col`

4665 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4666 if [ -n "$vcol" ]; then

4667 par2table $1

4668 else

4669 return 2 # No insertion occurred

4670 fi

4671 }

4672

4673 default_view() { # $1=def-file

4674 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4675 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4676 default_storedb "$@"

4677 query "select rowid from $tbl order by rowid desc;" \

4678 | while read rowid; do

4679 viewtable $1 $tbl $rowid

4680 done | _m4 -D_TITLE_="$tbl" \

4681 -D_FORM_="`genform $1`" \

4682 -D_DUMPTABLE_="syscmd(cat)" \

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

4684 }

4685 default_viewtext() { # $1=def-file

4686 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4687 default_storedb "$@"

4688 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4689 | _m4 -D_TITLE_="$tbl" \

4690 -D_FORM_="`genform $1`" \

4691 -D_DUMPTABLE_="syscmd(cat)" \

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

4693 }

4694 default_smail() {

4695 default_storedb "$@"

4696 if [ $? -eq 2 ]; then

4697 _m4 -D_TITLE_="入力" \

4698 -D_FORM_="`genform $1`" \

4699 -D_DUMPTABLE_="" \

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

4701 return

4702 fi

4703 cond=""

4704 for pk in `gettblpkey $tbl`; do

4705 pv=$(sqlquote "$(getpar $pk)")

4706 cond="$cond${cond:+ and }$pk=$pv"

4707 done

4708 sql="select rowid from $tbl where $cond;"

4709 rowid=`query "$sql"`

4710 ## err smail1 - "$sql" "-> rowid=$rowid"

4711

4712 while IFS=: read prompt name keytype type args; do # Read from $1

4713 val=`getpar $name`

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

4715 text="$text

4716 $prompt

4717 $name=$val

4718 ---------------------------------------------------------"

4719 fi

4720 case "$type" in

4721 image|document|file)

4722 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4723 fns=$(echo "$fn"|while read fn; do

4724 err mv $tmpd/$fn.orig $tmpd/$fn

4725 mv $tmpd/$fn.orig $tmpd/$fn

4726 rm $tmpd/$fn.rowid # Remove cache flag

4727 ## err "`ls $tmpd/$fn`"

4728 echo $fn

4729 done)

4730 files="$files $fns"

4731 ;;

4732 esac

4733 done < $1

4734 ## err FILES=$files "`ls -lF $tmpd`"

4735 subj="from ${REMOTE_ADDR}"

4736 (echo "$url"

4737 echo "への書き込みがありました。"

4738 echo "------"

4739 echo "$text"

4740 ) | (cd $tmpd &&

4741 err LS="`ls -lF`" &&

4742 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4743 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4744 echo "以下の内容で送信しました。" | html p

4745 viewtable $1 $tbl \

4746 `query "select rowid from $tbl order by rowid desc limit 1;"`

4747 echo "戻る" | html a "href=\"?\""

4748 }