s4: 93c38e425c85 s4-funcs.sh

s4

view s4-funcs.sh @ 953:93c38e425c85

Do not insert null new record.

author	HIROSE Yuuji <yuuji@gentei.org>
date	Mon, 08 Nov 2021 18:16:33 +0900
parents	d5e87825195f
children	946f2db59413

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=${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=$((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/csv"'

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

68 file_accept_help="

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

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

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

72 "

73 file_warn="$file_accept_help

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

75 blogreadflagrowid=0

76 blogcutoffflagrowid=-1

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

78 whatsnewdays=${WHATS_NEW_DAYS:-14}

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

80 session=$main_session

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

82 tex: {tags: "ams",

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

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

85 <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>'}

87 tconfs=""

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

89 conftbl=_tblconf

90 nl="

91 "

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

93 iconcachekey="profimgcache_S"

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

96 # Start debug logging

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

98 exec 3>> $logdir/debug.out

99 err() {

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

101 # Avoid backslash escape sequences

102 cat<<EOF 1>&3

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

104 EOF

105 }

106 case "$HTTP_USER_AGENT" in

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

108 *) touchpanel="" ;;

109 esac

110

111 # If S4MASTERDB is set, behave in another world

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

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

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

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

116 # Variables set in s4-world.sh

117 # $S4WORLDS, $S4WROLDNAME, $S4WORLDGRPS

118 # Files created in s4-world.sh

119 # $worldlistfile, $worldoptionfile, $worldnamefile, $worldgrpfile

120 fi

121

122

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

124

125 : <<EOF

126

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

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

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

130 レポート提出管理

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

132

133 [2016]

134 7/12 根本への反省

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

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

137

138 [以下2015]

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

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

141 Web

142 締切設定

143

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

145 ○自分の提出物リスト

146

147 7/19 ○設置

148 ○一斉送信

149 ○getparfilename の tmpd の扱い

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

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

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

153 ○添付ファイル回収

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

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

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

157

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

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

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

161 ○レポート提出モード

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

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

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

165

166

167 7/9 ○管理者の追加

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

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

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

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

172

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

174

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

176 ○グループ検索

177

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

179

180

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

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

183

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

185 ○6/1 par2tableを triplex 対応に

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

187 →とすると一気に

188

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

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

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

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

193

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

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

196 ## 1. passwd

197 ## 2. grp

198 ## 3. grp_mem

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

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

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

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

203

204 ## ● listの定義:

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

206 ## ● hashの定義:

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

208

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

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

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

212 ## csv2sq3 で .csv.sq3 の Makefile

213

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

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

216 ## type := root | comment

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

218 ## unique(id, filename)

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

220

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

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

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

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

225 ## できたー!

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

227 ↓

228 ↓以下に変更

229 with recursive allmem as

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

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

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

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

234

235

236 with recursive allmem as

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

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

239 where allmem.val=grp_m.gname)

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

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

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

243

244

245 ## triggerもできた。

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

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

248

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

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

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

252 ## グループ管理

253 ## 個人情報管理

254 ##

255

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

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

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

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

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

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

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

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

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

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

266

267 ## ログテーブル

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

269

270

271

272 ■表設計

273 * 3つの表に分散管理

274 id格納表 + hash表 + list表

275 * *_s *_m

276

277

278

279 user, user_map, user_col

280

281 ■抽象エントリタイプ

282 * user

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

284 * group

285 権限判定に利用

286 * serial

287 自動idとして機能

288 * password

289 入力 type=passwordで入力

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

291 * session

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

293 * text

294 入力 type=text

295 * textarea

296 入力 textarea

297 * image|document

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

299 * owner

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

301 * gowner

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

303 * timestamp

304 datetime()

305 * parent

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

307 * path

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

309

310 格納タイプ

311 * list

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

313 * hash

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

315

316 オブジェクトタイプ

317 * entry

318 id, title, owner

319 * textpart

320 id, parentID, text

321 * binarypart

322 id, parentID, contenttype, filename, content

323 * content

324 hash(textpart), list(binarypart)

325 * topic

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

327 * reply

328 id, parentID, content

329 * blog

330 list(entry)

331 blog = [topic, list(reply)]

332

333

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

335 "text" => "hogehoge ..",

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

337 "author" => "taro",

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

339 "parent" => "/",

340 "path" => "/1",

341 "text" => "blah, blah, ....",

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

343 {"serial" => 2,

344 "author" => "hanako",

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

346 "parent" => "/",

347 "path" => "/2",

348 "text" => "blah, blah, ....",

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

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

351

352

353 user:=

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

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

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

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

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

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

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

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

362

363 変換表

364 /user/email=m

365

366 blog:=

367 シリアル:id:p:serial

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

369 所有者:owner:s:owner:

370 時刻:ctime:s:stamp:

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

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

373

374 article:=

375 シリアル:id:p:serial

376 筆者:author:s:owner

377 時刻:ctime:s:stamp:

378 参照元:parent:s:parent:

379 パス:path:s:path:

380 本文:text:s:textarea:

381 画像:image:m:image:

382

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

384

385

386 EOF

387

388 logstart() {

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

390 }

391 logend() {

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

393 }

394 sqlog() {

395 logstart

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

397 cat >> $querylog

398 else

399 echo "$*" >> $querylog

400 fi

401 logend

402 }

403 sq() {

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

405 logstart

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

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

408 else

409 echo "$@" >> $querylog

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

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

412 fi

413 logend

414 }

415 dbsetup() {

416 pipedir=$tmpdir/pipedir

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

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

419 suf=`date +%s`

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

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

422 mkfifo $sqi $sqo

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

424 sq $db < $sqi &

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

426 if $isCGI; then

427 exec 2>> $logdir/error.out

428 fi

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

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

431 rm $sqi

432 # Attach supplemental DB

433 cat >&5 <<-EOF

434 .output /dev/null

435 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

439 .output stdout

440 EOF

441 }

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

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

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

445 fi

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

447 }

448 cleanup() {

449 trap '' INT HUP EXIT TERM PIPE

450 echo .quit >&5

451 kill $sq3pid

452 kill $sq3pid

453 rm -f $sqo $sqi

454 rm -rf $tmpfiles

455 cleanup2

456 }

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

458 # simple redirection for the sake of speed.

459 query() {

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

461 echo ".output $sqo" >&5

462 logstart

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

464 tee -a $querylog

465 else

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

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

468 fi >&5

469 echo ".output stdout" >&5

470 cat $sqo

471 rc=$?

472 logend

473 return $rc

474 }

475 _m4() {

476 #S4NAME=f,f,f

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

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

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

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

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

482 }

483 ismember() {

484 # $1=user, $2=group

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

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

487 }

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

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

490 }

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

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

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

494 }

495 isgrpowner() (

496 # $1=user, $2=group

497 gn=`sqlquote "$2"`

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

499 err isgrpowner: $sql

500 test -n "`query $sql`"

501 )

502 isgrpownerbygid() (

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

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

505 err isgrpownerbygid: $sql

506 test -n "`query $sql`"

507 )

508 getgroupadminmails() {

509 # $1=group

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

511 email4group "$1" "$i" ;

512 done

513 }

514 getgroupadmins() { # $1=group

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

516 qgrp=`sqlquote "$1"`

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

518 }

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

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

521 getvalbyid grp $2 \

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

523 }

524 getgroupbyid() {

525 # $1=id|gname

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

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

528 # err ggbyid: `echo $sql`

529 query $sql

530 }

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

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

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

534

535 # Return true when anonymous mode

536 [ "$anonymousmode" ] && return 0

537 # case `getvalbyid blog mode $2` in

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

539 # *closed*)

540 # owner=`getvalbyid blog owner $2`

541 # if isgrp $owner; then

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

543 # elif isuser $owner; then

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

545 # fi

546 # esac

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

548 case "$2" in

549 grp_*)

550 sql="SELECT 'owner'

551 FROM grp_adm

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

553 AND

554 user = '$user';"

555 ;;

556 *)

557 sql="with getblog as (

558 select key,val from blog_s where id=(

559 select blogid from article where id in

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

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

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

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

564 isgrpadm as (select user from grp_adm where

565 gname=(select val from getowner) and

566 user='$1'),

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

568 select case

569 when (select author from article where

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

571 then 'author'

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

573 then 'open'

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

575 then CASE

576 WHEN (SELECT val FROM isgrp) IS NULL

577 THEN

578 CASE WHEN (SELECT val from getowner)

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

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

581 ELSE ''

582 END

583 WHEN (select user from isgrpadm) IS NOT NULL

584 THEN 'i-am-admin'

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

586 END

587 when (select val from getmode) is null

588 then 'open'

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

590 then (SELECT user FROM isgrpadm)

591 when (select author from article where

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

593 then 'user+author'

594 else '' end;"

595 ;;

596 esac

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

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

599 # result=`query "$sql"`

600 # err FileAccessibility=$result

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

602 }

603 linkhome() {

604 # $1=UserOrGroupRowid

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

606 if isuser $1; then

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

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

609 name=`gecos $1|htmlescape`

610 else

611 _grid=`numericalize "$1"`

612 echo -n "grp+$1"

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

614 fi

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

616 }

617 hreflink() {

618 # s4 specific notation:

619 # ^href=URL

620 # ^iframe=URL

621 # ^video=URL

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

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

624 # OSM umap Wikistyle Notation:

625 # [[URL]] - Simle Link

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

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

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

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

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

631 # Other Style

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

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

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

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

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

637 # SPC+SPC+$ - <br>

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

639 checkboxON="${cb} checked>"

640 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

665

666 }

667 minitbl() {

668 sed -n '

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

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

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

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

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

674 H; # Concat this line to HoldSpace

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

676 $ b cont

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

678 }

679 :cont

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

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

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

683 # . of ^. is workaround for FreeBSD sed

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

685 p; # Print whole "table" element

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

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

688 }

689 x; # Back to the newest line

690 p; # Print rest' | miniul

691 }

692 miniul() {

693 sed -e '

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

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

696 :top

697 s/\n//;

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

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

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

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

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

703 $ b cont

704 N; # 次の行を読む

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

706 /^ /b top

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

708 p; s/.*//;

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

710 /^\* /b top

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

712 }

713

714 :cont

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

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

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

718 H; x

719 }

720 x' | miniol

721 }

722 miniol() {

723 sed -e '

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

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

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

727 x;

728 :top

729 s/\n//;

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

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

732 x;

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

734 x;

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

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

737 $ b cont

738 N; # 次の行を読む

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

740 /^ /b top

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

742 p; s/.*//;

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

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

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

746 }

747

748 :cont

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

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

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

752 H; x

753 }

754 x'

755 }

756 acclog() (

757 # $1=table, $2=rowid

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

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

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

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

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

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

764 fi

765 )

766 gecos() (

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

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

769 )

770 setpar() {

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

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

773 case "$1" in

774 user) _user="$v" ;;

775 skey) _skey="$v" ;;

776 esac

777 fi

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

779 }

780 unsetpar() {

781 for i; do

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

783 case "$i" in

784 user|skey) unset _$i ;;

785 esac

786 fi

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

788 done

789 }

790 replpar() {

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

792 }

793 getpar() {

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

795 val=""

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

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

798 user) val=$_user ;;

799 skey) val=$_skey ;;

800 esac

801 fi

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

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

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

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

806 fi

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

808 case "$var" in

809 owner)

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

811 echo $user; return

812 elif ismember $user $val; then

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

814 fi ;;

815 esac

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

817 printf '%s' "$val"

818 }

819 setskey() {

820 # For quick response...(?)

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

822 }

823 chkskey() {

824 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

830 return 0

831 fi

832 return 1

833 }

834 resetskey() {

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

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

837 fi

838 }

839 getpartype() {

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

841 }

842 getparcount() {

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

844 }

845 getparfilename() {

846 # null if type of $1 is not file

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

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

849 }

850 sqlquote() {

851 (v="$1"

852 case "$v" in

853 "") return ;; # null

854 "X'"*) # quoted hex string

855 echo $1 ;;

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

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

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

859 return ;;

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

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

862 return ;;

863 *)

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

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

866 else

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

868 fi ;;

869 esac)

870 }

871 sqlquotestr() (

872 case "$1" in

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

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

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

876 esac

877 )

878 mktempd() {

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

880 }

881 getcachedir() { # $1=maintable

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

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

884 else

885 echo $tmpd/$thumbxy

886 fi

887 }

888 getval() {

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

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

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

892 echo "$user" ;;

893 stamp|datetime)

894 date "+%F %T" ;;

895 serial)

896 (s=`getpar $2`

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

898 *)

899 getpar "$2" "$3";;

900 esac

901 }

902

903 getvalquote() {

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

905 (v=`getval "$@"`

906 case "$v" in

907 "") echo NULL ;;

908 *) sqlquote "$v" ;;

909 esac)

910 }

911 getparquote() {

912 sqlquote "`getpar $1`"

913 }

914 getbinbyid() {

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

916

917 }

918 getvalbyid() {

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

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

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

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

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

924

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

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

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

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

929 return

930 fi

931 done

932 rowid=$3

933 pk=`gettblpkey $1`

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

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

936 td=${4:-$tmpd}

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

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

939 for kt in s m; do

940 t=${1}_$kt

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

942 vcount=1 # count(val)

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

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

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

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

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

948 if [ $kt = m ]; then

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

950 # Reset val to store filenames if type is string

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

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

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

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

955 i=0

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

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

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

959 i=$((i+1))

960 fn=$c.$i

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

962 case $type in

963 file:*)

964 #file=$td/$val

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

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

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

968 # FOR SPEED: Skip file generation if imgcache exists

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

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

971 && continue

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

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

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

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

976 select rowid from $cond $slice;

977 .output '$td/$fn'

978 select val from $cond $slice;

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

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

981 .output stdout

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

983 EOF

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

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

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

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

988 ls -lh "$file" |

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

990 case "$type" in

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

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

993 *|*:[Aa]pplication*)

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

995 png:- > "$file"

996 ;;

997 esac

998 ;;

999 *)

1000 sq $db<<EOF

1001 .output $td/$fn.rowid

1002 select rowid from $cond $slice;

1003 .output $td/$fn

1004 select val from $cond $slice;

1005 EOF

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

1007 ;;

1008 esac

1009 done

1010 else

1011 rm -f $td/$c.count

1012 case $type in

1013 file:*)

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

1015 | while read fn; do

1016 file=$td/$fn

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

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

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

1020 | unhexize > "$file"

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

1022 case $type in

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

1024 *:[Aa]pplication*)

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

1026 png:- > $file ;;

1027 esac

1028 fi

1029 done

1030 ;;

1031 esac

1032 fi

1033 printf '%s' "$val"

1034 return

1035 fi

1036 done

1037 done)

1038 }

1039 getvalbypkey() (

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

1041 pk=`gettblpkey $1`

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

1043 getvalbyid "$1" "$2" $rowid $4

1044 )

1045 getvalbycond() {

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

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

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

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

1050 getvalbyid "$1" "$2" $rowid "$4"

1051 fi

1052 }

1053 getpwfield() {

1054 # getpwfield user column

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

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

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

1058 echo "$val"

1059 return 0

1060 else

1061 return 1

1062 fi

1063 }

1064 numericalize() {

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

1066 }

1067 encode() {

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

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

1070 sha1=sha1

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

1072 sha1=sha1sum

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

1074 sha1=gsha1sum

1075 fi

1076 fi

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

1078 }

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

1080 gs_pdfwrite() {

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

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

1083 }

1084 fi

1085 enjpeg() {

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

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

1088 cjpeg="cjpeg"

1089 else

1090 cjpeg="convert - jpeg:-"

1091 fi

1092 fi

1093 $cjpeg "$@"

1094 }

1095 mycrypt() (

1096 key=$1 salt=$2

1097 # err \$2=$2

1098 case $2 in

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

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

1101 esac

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

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

1104 )

1105 hexize() {

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

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

1108 hexize="xxd -p"

1109 else

1110 hexize_hd() {

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

1112 }

1113 hexize="hexize_hd"

1114 fi

1115 fi

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

1117 }

1118 unhexize() {

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

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

1121 unhex="xxd -p -r"

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

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

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

1125 EOF

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

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

1128 # world writable...:)

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

1130 fi

1131 fi

1132 cat "$@" | $unhex

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

1134 }

1135 percenthex() {

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

1137 }

1138 htmlescape() {

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

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

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

1142 }

1143 enascii() {

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

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

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

1147 else

1148 enascii_now=`date +%FT%T`

1149 enascii_sed() {

1150 nkf -Z0Z1Z2 \

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

1152 }

1153 enascii="enascii_sed"

1154 fi

1155 fi

1156 cat "$@" | $enascii

1157 }

1158 size_h() {

1159 i="$1" oi=$1

1160 set -- B B KB MB GB TB

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

1162 oi=$i

1163 i=$((i/1024))

1164 shift

1165 done

1166 echo ${oi}$1

1167 }

1168 gettblconf() {

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

1170 ## tconfs=`sq $db \

1171 tconfs=`query \

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

1173 fi

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

1175 }

1176 gettblkeys() {

1177 # $1=tbl

1178 gettblconf

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

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

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

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

1183 case $type in

1184 *p*)

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

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

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

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

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

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

1191 esac

1192 case $type in

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

1194 esac

1195 done

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

1197 }

1198 gettblpkey() {

1199 # $1=tbl

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

1201 }

1202 gettblfkey() {

1203 (x=`gettblkeys $1`

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

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

1206 }

1207 gettblcols() {

1208 (x=`gettblkeys $1`

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

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

1211 }

1212 gettbl_s_cols() {

1213 (x=`gettblkeys $1`

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

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

1216 }

1217 gettbl_m_cols() {

1218 (x=`gettblkeys $1`

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

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

1221 }

1222 gettbl_h_cols() {

1223 (x=`gettblkeys $1`

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

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

1226 }

1227 gettbl_coltype() (

1228 gettblconf

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

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

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

1232 )

1233 is_hidden() {

1234 # $1=Tbl $2=col

1235 gettblconf

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

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

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

1239 case $x in

1240 *h*) return 0 ;;

1241 *) return 1 ;;

1242 esac

1243 }

1244

1245 dbsetbyid() {

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

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

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

1249 quotedp=$(sqlquotestr "$p")

1250 unset primary update

1251 gettblconf

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

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

1254 #err conf=$conf

1255 case ${conf#*=} in

1256 p*) primary=1 ;;

1257 f*) update=1 ;;

1258 u*) ;;

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

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

1261 esac

1262 #err t=$t

1263 type=string fn=""

1264 case $conf in

1265 */password)

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

1267 ;;

1268 */image*|*/document*)

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

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

1271 ;;

1272 esac

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

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

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

1276 if [ "$primary" ]; then

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

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

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

1280 elif [ "$update" ]; then

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

1282 else

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

1284 fi

1285 )

1286 }

1287 expire() (

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

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

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

1291 )

1292 addsession() {

1293 # expireをセット

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

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

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

1297 # Remove old session parameters

1298 now=`expire now`

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

1300 }

1301 gencookie() (

1302 path=${URL#*:/}

1303 path=${URL%/*}

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

1305 for kv; do

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

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

1308 done

1309 )

1310 contenttype() {

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

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

1313 }

1314 putheader() {

1315

1316 }

1317 putfooter() {

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

1319 }

1320 getcookie() {

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

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

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

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

1325 case "$k" in

1326 user) _user="$v" ;;

1327 skey) _skey="$v" ;;

1328 esac

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

1330 done

1331 }

1332 genrandom() {

1333 # $1=columns (default: 10)

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

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

1336 }

1337 genserial() {

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

1339 }

1340 smail() {

1341 # smail rcpts subj (file)

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

1343 # $MAIL_FROM <- From: header value

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

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

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

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

1348 sender=${SENDER:-$admin}

1349 # Do not call m4 with directly passing text

1350 _r=$tmpd/rcpt

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

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

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

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

1355 }

1356 smail_queue_flush() {

1357 # $1=timelimit

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

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

1360 SELECT rowid FROM smailq

1361 GROUP BY rcpts, subj

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

1363 EOF

1364 )

1365 for rid in $rowids; do

1366 sq $workdb \

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

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

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

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

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

1372

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

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

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

1376 .separator "\n" "------------------\n\n"

1377 SELECT time, text FROM smailq

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

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

1380 ORDER by time;

1381 EOF

1382 then

1383 cat <<-EOF | sq $workdb

1384 DELETE FROM smailq

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

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

1387 EOF

1388 fi

1389 fi

1390 done

1391 }

1392 smail_queue() {

1393 # $1=Rcpts, $@=subj

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

1395 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1404 EOF

1405 )

1406 ## XXX: Adhoc load mitigation

1407 case "$now" in

1408 *[01])

1409 err flush_queue=$mintime

1410 smail_queue_flush ;;

1411 esac

1412 fi

1413 }

1414 setviastring() {

1415 table=par

1416 oifs="$IFS"

1417 IFS="&"

1418 for us in $1; do

1419 k=${us%%=*}

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

1421 setpar "$k" "string" "$v"

1422 done

1423 IFS="$oifs"

1424 }

1425 checkdomain() (

1426 # Check the validity of domain by referring DNS

1427 item=$1

1428 err checkdomain $1

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

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

1431 )

1432 pwcheck() {

1433 # $1=passwd

1434 dbpswd=`getpwfield $user pswd`

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

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

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

1438 }

1439 mypwhash() {

1440 mycrypt "`cat`" `genrandom 5`

1441 }

1442 flag_profupdate() {

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

1444 # This is useful to mitigate account sync load

1445 query <<-EOF

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

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

1448 EOF

1449 touch $userupdateflag

1450 }

1451 wasureta() {

1452 user=$1

1453 if ! checkdomain $user; then

1454 contenttype; echo

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

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

1457 putfooter

1458 exit 0

1459 fi

1460 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1462 encpswd=`echo $newpswd|mypwhash`

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

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

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

1466 $msgdir/mail-newaccount.m4 \

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

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

1469 }

1470 checkauth() {

1471 user=`getpar user`

1472 skc=`getpar skey` # from cookie

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

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

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

1476 if chkskey "$skc"; then

1477 return 0

1478 fi

1479 fi

1480 pswd=`getpar pswd`

1481 quser=`sqlquotestr "$user"`

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

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

1484 return 4 # 4=server too heavy

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

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

1487 return 2 # 2=login fail

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

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

1490 contenttype; echo

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

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

1493 cat <<-EOF | html p

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

1495 EOF

1496 exit 0 # XXXXX more smart...

1497 fi

1498 wasureta "$user"

1499 err wasureta issued for "$user"

1500 return 1 # wasureta error

1501 fi

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

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

1504 if pwcheck "$pswd"; then

1505 newsession=`genrandom 34`

1506 if setskey "$user" "$newsession" &&

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

1508 gencookie "user=$user" "skey=$newsession"

1509 return 0

1510 else

1511 return 4 # Heavy load??

1512 fi

1513 fi

1514 err "Login failed: [$user]"

1515 return 2 # Password mismatch

1516 }

1517 showlogin() {

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

1519 test -z "$args" && resetskey

1520 s4name=${S4NAME:-s4}

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

1522 cat $layout/login.m4.html

1523 echo '</body></html>'

1524 ) | _m4 \

1525 -D_BODYCLASS_="login" \

1526 -D_TITLE_="$s4name" \

1527 -D_SYSNAME_="Welcome to $s4name" \

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

1529 exit 0

1530 }

1531 dologin() {

1532 test -n "$S4WORLD" && syncaccount

1533 checkauth

1534 st=$?

1535 if [ $st != 0 ]; then

1536 contenttype; echo

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

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

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

1540 showlogin # and EXIT

1541 fi

1542 err "Auth OK: [$user]"

1543 }

1544

1545 # Do instant jobs here

1546 dbsetup

1547 trap cleanup INT HUP EXIT TERM PIPE

1548 # trap cleanup INT HUP

1549

1550

1551 cgiinit() {

1552 tmpd=`tmpd=$tmpdir mktempd`

1553 tmpf=$tmpd/stream.$$

1554 tmpfiles=$tmpfiles" $tmpd"

1555 addsession $session

1556 getcookie

1557 case "$REQUEST_METHOD" in

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

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

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

1561 # cat > $tmpf # too much?

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

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

1564 s="`cat $tmpf`"

1565 ;;

1566 esac

1567 case "$CONTENT_TYPE" in

1568 *boundary*)

1569 bndry=${CONTENT_TYPE#*boundary=}

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

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

1572 do

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

1574 #echo u=$us

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

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

1577 case "$k" in

1578 *:filename)

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

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

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

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

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

1584 :

1585 fi

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

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

1588 case "$mimetype" in

1589 [Ii]mage/x-xcf)

1590 bzip2 "$tmpd/$v"

1591 v=${v}.bz2

1592 ;;

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

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

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

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

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

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

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

1600 v=$vjpg

1601 else

1602 mimetype="Not supported"

1603 fi

1604 ;;

1605 [Ii]mage/*)

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

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

1608 ;;

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

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

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

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

1613 err Calling gs from $v to $nv

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

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

1616 v=$nv

1617 fi

1618 fi

1619 fi

1620 esac

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

1622 then

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

1624 continue

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

1626 replpar text string \

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

1628 continue

1629 fi

1630 ;;

1631 image|document|binary)

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

1633 continue ;;

1634 *)

1635 type='string'

1636 ;;

1637 esac

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

1639 setpar "$k" "$type" "$v"

1640 done

1641 ;;

1642 *)

1643 setviastring "$s"

1644 ;;

1645 esac

1646 }

1647 email4group() {

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

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

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

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

1652 sql="WITH

1653 grpemails AS (

1654 SELECT gname, user, val email

1655 FROM grp_mem NATURAL JOIN grp_mem_s

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

1657 useremails AS (

1658 SELECT user.name, val email

1659 FROM user

1660 LEFT JOIN user_m

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

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

1663 FROM useremails u LEFT JOIN grpemails g

1664 ON u.name=g.user

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

1666 query "$sql"

1667 }

1668 email4groupbyuid() {

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

1670 g=$1; shift

1671 uids=`echo "$@"`

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

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

1674 email4group "$g" `query "$sql"`

1675 }

1676 myemail4group() {

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

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

1679 }

1680 collectmembersbyid() {

1681 # Collect user names of group specified by grid

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

1683 query "SELECT user FROM grp_mem \

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

1685 }

1686 collectmembersbyid() {

1687 # Collect user names of group name

1688 qgrp=`sqlquote "$1"`

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

1690 }

1691 collectgecosesbyid() {

1692 # Collect user gecoses of group

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

1694 query<<-EOF

1695 SELECT gecos

1696 FROM gecoses

1697 WHERE name IN (SELECT user FROM grp_mem

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

1699 EOF

1700 }

1701 collectemail() (

1702 # Collect email addresses for group $1

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

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

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

1706 for e; do

1707 if isuser "$e"; then

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

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

1710 else

1711 qgrp=`sqlquote "$e"`

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

1713 gmem="grp_mem"

1714 else

1715 tm=`sqlquote "$TEAM"`

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

1717 fi

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

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

1720 $gmem g left join grp_mem_s s

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

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

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

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

1725 query "$sql"

1726 fi

1727 done

1728 )

1729 sendinvitation() (

1730 # $1=email

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

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

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

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

1735 gecos=`gecos`

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

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

1738 _m4 -D_URL_="$urlbase" \

1739 -D_USER_="$name" \

1740 -D_EMAIL_="$1" \

1741 -D_REGIST_="$regist" \

1742 -D_ADMIN_="$admin" \

1743 $msgdir/mail-invite.m4 \

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

1745 return 0

1746 )

1747 emaildomaincheck() {

1748 case "$1" in

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

1750 *@*)

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

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

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

1754 return 2

1755 fi

1756 return 0

1757 ;;

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

1759 esac

1760 }

1761 invite() {

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

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

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

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

1766 return

1767 fi

1768 fi

1769 case "$email" in

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

1771 *@*)

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

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

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

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

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

1777 elif sendinvitation $email; then

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

1779 else # Cannot be reached here

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

1781 fi ;;

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

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

1784 esac

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

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

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

1788 fi

1789 if [ -f $invite_policy ]; then

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

1791 else

1792 pol="$invite_policy"

1793 fi

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

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

1796 -D_INVITE_POLICY_="$pol" \

1797 $layout/html.m4.html $layout/invite.m4.html

1798 }

1799 regist() {

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

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

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

1803 echo "bye bye" | html p

1804 reutrn

1805 fi

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

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

1808 cat<<EOF

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

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

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

1812 EOF

1813 return

1814 fi

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

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

1817 # Fake login password to wasureta

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

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

1820 wasureta $email

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

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

1823 addsession $1 # for removal after 1 minute

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

1825 $layout/login.m4.html

1826 return

1827 }

1828 group_safename() {

1829 # Convert $1 to safe group name

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

1831 }

1832 groupupdate() {

1833 gname=`getpar gname`

1834 qgname=`sqlquote "$gname"`

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

1836 # See ALSO same job in showgroup()

1837 newgname=`group_safename "$gname"`

1838 err newgname=$newgname

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

1840 err NewGNAME: gname=$newgname

1841 gname=$newgname

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

1843 replpar gname string "$gname"

1844 fi

1845 # Name confliction check

1846 parow=`getpar rowid`

1847 ## err parow=$parow

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

1849 query "BEGIN EXCLUSIVE;"

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

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

1852 if [ $count -gt 0 ]; then

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

1854 query "END;"

1855 return

1856 fi

1857 par2table $formdir/grp.def

1858 query "END TRANSACTION;"

1859 # Remove orphan

1860 : <<EOF

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

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

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

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

1865 EOF

1866 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1870 sql="delete from blog where id in

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

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

1873 query "$sql";

1874 unsetpar tag kwd

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

1876 return # and return

1877 fi

1878 fi

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

1880 fi

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

1882 grid=$(query $sql)

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

1884 grp $grid

1885 }

1886 groupclone() {

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

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

1889 case "$1" in

1890 */noteam)

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

1892 esac

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

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

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

1896 return

1897 fi

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

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

1900 return

1901 fi

1902 i=0

1903 while true; do

1904 copy="-copy$i"

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

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

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

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

1909 i=$((i++))

1910 continue

1911 fi

1912 break

1913 done

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

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

1916 query<<-EOF

1917 BEGIN;

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

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

1920 SELECT $newqname, key, val

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

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

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

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

1925 -- Copy members and their configuration --

1926 REPLACE INTO grp_mem SELECT $newqname, user

1927 FROM grp_mem WHERE gname=$qgrp;

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

1929 FROM grp_mem_s WHERE gname=$qgrp;

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

1931 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1932 -- Copy administrators --

1933 REPLACE INTO grp_adm SELECT $newqname, user

1934 FROM grp_adm WHERE gname=$qgrp;

1935 COMMIT;

1936 EOF

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

1938 STOPCLONEMSG=1 groupconf "$newrowid"

1939 }

1940 groupman() {

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

1942

1943 GF_STAGE="grpconf"

1944 GF_STAGE=groupupdate

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

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

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

1948 -D_DUMPTABLE_="syscmd(cat)" \

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

1950 }

1951 userconf() {

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

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

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

1955 }

1956 groupconf() {

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

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

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

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

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

1962

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

1964 grp=`getgroupbyid $rowid`

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

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

1967 return

1968 fi

1969

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

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

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

1973 ## Setup migration menu

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

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

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

1977 err v=$v

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

1979 migrate=$(cat<<-EOF

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

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

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

1983 <form action="?migrategrp">

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

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

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

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

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

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

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

1991 `cgi_hidden stage migrategrp`

1992 `cgi_hidden rowid $rowid`

1993 `cgi_submit OK`

1994 `cgi_reset Reset`

1995 </form>

1996 </div>

1997 EOF

1998 )

1999 fi

2000 fi

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

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

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

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

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

2006 <table>

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

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

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

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

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

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

2013 </table>

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

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

2016 </div>

2017 $migrate

2018 `cgi_radio grpaction close id="x"`<label for="x" accesskey="x">×</label>

2019 <div style="height: $height; background: transparent;"></div>

2020 EOF

2021 fi

2022 }

2023 migrategrp() {

2024 rowid=`getpar rowid`

2025 rowid=${rowid%%[!0-9]*}

2026 grp=`getgroupbyid $rowid`

2027 if ! isgrpowner "$user" "$grp"; then

2028 echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"

2029 return

2030 fi

2031 if [ x`getpar emichk` != x"yes" ]; then

2032 echo "移住確認未チェックなので中止します。" | html p

2033 grp "$rowid"

2034 return

2035 fi

2036 destconf=`getpar migrateto`

2037 err destconf=$destconf

2038 if [ ! -e $destconf ]; then

2039 echo "移住先Worldが認識できないので中止します($destconf)。" | html p

2040 grp "$rowid"

2041 return

2042 fi

2043 if [ -n "$worldconf" ]; then

2044 srcconf=$worldconf

2045 else

2046 srcconf=s4-config.sh

2047 fi

2048 _m4 -D_TITLE_="移住操作" -D_BODYCLASS_="" $layout/html.m4.html

2049 echo "移住操作" | html h1

2050 echo '<pre>'

2051 set -- "$srcconf" "$destconf" "$rowid"

2052 err ./s4-migrate.sh "$srcconf" "$destconf" "$rowid"

2053 . ./s4-migrate.sh # Dot(.) sourcing might not pass arguments

2054 rc=$?

2055 echo "</pre>"

2056 if [ $rc -eq 0 ]; then

2057 echo "World [$world] への移住完了。" | html p

2058 echo "<p><a href=\"$dsturl?grp+$destrowid\">移住先</a></p>"

2059 elif [ -n "$faillist" ]; then

2060 echo "移住後削除失敗" | html p

2061 echo "空いている時間帯に再度試して下さい。" | html p

2062 else

2063 echo "移住失敗" | html p

2064 echo "移動先に重複がないか確認して下さい。" | html p

2065 fi

2066 return

2067 }

2068 mems() {

2069 _m4 -D_TITLE_="参加者一覧" -D_BODYCLASS_=listmember $layout/html.m4.html

2070 kwd=`getpar kwd`

2071 listmember $kwd

2072 }

2073 grps() {

2074 case "$S4WORLD" in

2075 $nonewgroupworld) ;;

2076 *) LINK_NEWGRP="<a href=\"?groupman\">新規グループ作成</a>" ;;

2077 esac

2078 _m4 -D_TITLE_="グループ一覧" -D_BODYCLASS_=listgroup $layout/html.m4.html

2079 kwd=`getpar kwd`

2080 listgroup "$kwd" \

2081 | _m4 -D_DUMPTABLE_="syscmd(cat)" \

2082 -D_TITLE_="グループ関連操作" \

2083 -D_FORM_="${LINK_NEWGRP}${NEWGRP_GUIDE}" \

2084 $layout/form+dump.m4.html

2085 }

2086 grp() { # $1=group-rowid

2087 gpg=`getpar grp`

2088 grid=${1:-$gpg}

2089 grp=`getgroupbyid "$grid"`

2090 ## . ./s4-blog.sh

2091 jg=`getpar joingrp`

2092 if [ -n "$jg" ]; then

2093 [ -n "$jg" -a -n "$grp" ] &&

2094 joingrp "$grp" "$user" "$jg" "`getpar email`"

2095 fi

2096 htmlheader=$layout/html.m4.html

2097 showgroup "$grid"

2098 }

2099 sql4interestblogs() {

2100 cat<<EOF

2101 CREATE TEMPORARY VIEW interestblogs AS

2102 SELECT blog.rowid rid, id, author

2103 FROM blog

2104 NATURAL JOIN

2105 (SELECT id, val owner FROM blog_s WHERE key='owner') bs

2106 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2107 THEN 1 -- blog owner is an user, READABLE

2108 WHEN (SELECT user FROM grp_mem

2109 WHERE gname=bs.owner AND user='$user') IS NULL

2110 THEN 0

2111 ELSE 1

2112 END;

2113 EOF

2114 }

2115 listnewblogsql() { # $1=user

2116 newdays=${WHATS_NEW_DAYS##*[!0-9]} # Shave non-digits

2117 newdays=${newdays%%[!0-9]*}

2118 newdays=${newdays:-14}

2119 basetime="datetime('now', 'localtime', '-${newdays} days')"

2120 deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog

2121 WHERE user='$user'

2122 AND tblrowid IN

2123 ($blogreadflagrowid,

2124 $blogcutoffflagrowid)),

2125 $basetime -- "0"

2126 );"`

2127 cat<<EOF

2128 `sql4interestblogs`

2129 WITH article_ctime as (

2130 SELECT id,blogid,author,max(val) ctime

2131 FROM article join article_s s using(id)

2132 WHERE s.key='ctime' AND s.val > '$deftime'

2133 GROUP BY id

2134 ), blog_title_owner as (

2135 SELECT blg.rid brid, id,

2136 max(case key when 'title' then val end) title,

2137 max(case key when 'owner' then val end) owner

2138 FROM interestblogs blg, blog_s using(id) group by id

2139 ), blogall as (

2140 SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid

2141 ), news as (

2142 SELECT brid, bl.id blid, bl.title, ctime,

2143 coalesce(al.time, '$deftime') atime,

2144 count(bl.id) "新着", bl.author

2145 FROM blogall bl

2146 LEFT JOIN

2147 (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al

2148 ON bl.brid=al.tblrowid

2149 WHERE atime < bl.ctime

2150 GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id

2151 LIMIT 10

2152 ) SELECT brid LINK, "新着",

2153 (SELECT count(*) FROM article WHERE blogid=blid) "総数",

2154 ctime, title,

2155 (SELECT gecos FROM gecoses WHERE name=author) gecos

2156 FROM news;

2157 EOF

2158 }

2159

2160 search_form() {

2161 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

2162 # $2(optional) = pre-input keywords

2163 help="(1)空白区切りの単語で本文検索

2164 (2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索

2165 @2016-0[1-6] → 2016年1月から6月

2166 @>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間

2167 @week → 最近一週間

2168 (3)#番号で記事ID検索

2169 (1)と(2)は組み合わせOK

2170 例: @2016-10-0[1-9] 芋煮

2171 → 2016年10月上旬でキーワード「芋煮」を含む記事検索

2172 ※クイズ板は検索対象から外されます。"

2173 auth=""

2174 placeholder="全記事からの検索"

2175 case "$1" in

2176 author=*)

2177 a=`echo "${1#author=}"|htmlescape`

2178 g=`gecos ${1#author=}`

2179 auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"

2180 placeholder="このユーザの書込検索"

2181 help="★★ $g さんの書き込みから検索します$nl$help"

2182 ;;

2183 grid=*)

2184 a=`echo "${1#grid=}"`; a=$((0 + $a))

2185 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

2186 placeholder="このグループからの検索"

2187 ;;

2188 esac

2189 inikwd="$2" # no need to htmlescape

2190 cat<<-EOF

2191 <div class="right">

2192 <form action="$myname">$auth

2193 <input type="text" name="kwd" value="$inikwd" title="$help"

2194 placeholder=" $placeholder " width="10" accesskey="k">

2195

2196 ${touchpanel:+<p class="help">$help</p>}

2197 <input type="hidden" name="stage" value="searchart">

2198

2199 </form>

2200 </div>

2201 EOF

2202 }

2203

2204 imgsrc_cache() (

2205 # $1 = directory for cache'ing

2206 # $2 = table (user_m or grp_m)

2207 # $3 = keycond (was: condition for choosingowner)

2208 # $4 = size : S = Small, M = Medium, O = Original

2209 dir="$1" tbl="$2"

2210 keycond="$3"

2211 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

2212 ORDER BY rowid DESC LIMIT 1"

2213 [ -d "$dir" ] || mkdir -p "$dir"

2214 tmpf=$tmpd/imgsrc_cache.$$

2215 case "$4" in

2216 [Ss]) size=S ;;

2217 [Oo]) size=O ;;

2218 *) size=M ;;

2219 esac

2220 # ImageCache filename storing schema:

2221 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

2222 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

2223 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

2224 valtype=`query "$sql0"`

2225 filename=${valtype%%//*}

2226 filetype=${valtype##*//file:}

2227 if [ x"$filename" = x"${filename%.*}" ]; then

2228 # If nor filename extension found, set it to image type

2229 case "$filetype" in

2230 image/*) filename=$filename.${filetype#image/} ;;

2231 esac

2232 fi

2233 cacheimg_S=$dir/S_$filename

2234 cacheimg_M=$dir/M_$filename

2235 cacheimg_O=$dir/$filename

2236 cacheimg=$dir/${size}_$filename

2237 sumfile="$dir/$filename.sum"

2238 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

2239 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2240 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2241 # if cache is fresh and has the same checksum,

2242 echo "<img src=\"$cacheimg\">"

2243 else

2244 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2245 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2246 fifo2=$fifo.2

2247 mkfifo $fifo $fifo2

2248 fmt=${filename##*.}

2249 ## [[ NOTE ]]

2250 ## a. convert oldimage newimage

2251 ## b. convert oldimage fmt:- | convert - newimage

2252 ## b is much smaller than a

2253 cat $tmpf | unhexize \

2254 | tee $fifo \

2255 | convert -define ${fmt}:size=${iconxy_M} \

2256 -resize ${iconxy_M}'>' - ${fmt}:- \

2257 | tee $fifo2 \

2258 | convert - "$cacheimg_M" &

2259 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2260 -resize ${iconxy_S}'>' - ${fmt}:- \

2261 | convert - "$cacheimg_S" &

2262 printf '%s' "<img src=\"data:${filetype},"

2263 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2264 echo '">'

2265 echo "$sum" > $sumfile

2266 fi

2267 ## Now preparing cache image, done.

2268 ## Store this information to DB

2269 stbl=${tbl%_m}_s # user_s or grp_s

2270 pkey=${keycond%%=*} # Primary Key name

2271 pval=${keycond#*=} # Primary Key value

2272 query <<-EOF

2273 REPLACE INTO $stbl($pkey, key, type, val)

2274 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2275 EOF

2276 )

2277

2278 showhome() {

2279 # $1=userRowIdToShow

2280 err showhome \$1=$1

2281 case "$1" in

2282 *@*) uname=`getvalbypkey user name "$1"` ;;

2283 *) uname=`getvalbyid user name $1` ;;

2284 esac

2285 ## err ShowHome: uname=$uname

2286 td=`getcachedir home/"$1"`

2287 gecos=`gecos "$uname"`

2288 ## err SH:gecos=$gecos

2289 GF_VIEWONLY=1

2290 cond="gname in (select gname from grp_mem where user='$uname')"

2291 search_form_args=""

2292 if [ x"$user" = x"$uname" ]; then

2293 if [ -z "$S4MASTERDB" ]; then

2294 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2295 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2296 elif [ -n "$S4MASTERURL" ]; then

2297 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2298 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2299 fi

2300 usermenu="$usermenu

2301 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2302 # Display folders

2303 sql="select count(id) from article_m where id

2304 in (select id from article where author='$user')

2305 and type like 'file:%';"

2306 ## err nfile-sql=`echo "$sql"`

2307 nfile=`query "$sql"`

2308 # err nfile=$nfile

2309 if [ $nfile -gt 0 ]; then

2310 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2311 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2312 fi

2313 else

2314 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2315 GROUP BY user;"`

2316 usermenu="<p>Last seen on $latestlog</p>"

2317 search_form_args="author=$uname"

2318 fi

2319 . ./s4-blog.sh

2320

2321 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2322 search_form "$search_form_args" > $sf

2323 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2324 | htmlescape > $tf

2325 { echo "<div class=\"noprofimg\">"

2326 viewtable $formdir/user.def user $1

2327 echo "</div>"

2328 } > $pf

2329

2330 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2331 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2332 imf=$tmpd/profimg.$$; touch $imf

2333 if [ -n "$img" ]; then

2334 if true; then

2335 tbl=user_m

2336 enticond="name='$uname'"

2337 imgsrc_cache "$td/main" user_m "$enticond" M

2338 else

2339 { printf '%s' "<IMG src=\"data:${img#file:},"

2340 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2341 | sed 's/$..$/%\1/g'

2342 echo '">'

2343 }

2344 fi > $imf

2345 fi

2346 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2347 val='$uname';"`

2348 listblog $uname > $bf

2349

2350 hometail=$tmpd/tail.$$

2351 mkfifo $hometail

2352

2353 #Calling listgroupbytable, originally here

2354

2355 (

2356 # Display Most Recent Entry

2357 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2358 shortval=${shortval:-val}

2359

2360 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2361 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2362 ctime,

2363 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2364 (SELECT gecos FROM gecoses

2365 WHERE name=(SELECT val FROM blog_s

2366 WHERE key='owner' AND id=b.id)) owner,

2367 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2368 FROM blog b

2369 JOIN

2370 (SELECT distinct blogid, a.id aid, max(val) ctime

2371 FROM article a, article_s s

2372 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2373 GROUP BY blogid ORDER BY val DESC LIMIT 50

2374 ) m

2375 ON b.id=m.blogid;"

2376 # This should be as follows

2377 : <<EOF

2378 WITH arts AS(

2379 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2380 a.blogid, a.id id, s.val ctime

2381 FROM article a NATURAL JOIN article_s s

2382 WHERE s.key = 'ctime' AND a.author='$user'

2383 GROUP by s.id

2384 )

2385 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2386 FROM arts a0

2387 JOIN

2388 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2389 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2390 ORDER BY ctime DESC LIMIT 50;

2391 EOF

2392

2393 cat<<-EOF

2394 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2395 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2396 <div class="lcto">

2397 `DT_VIEW=replyblog dumptable html blog`

2398 </div>

2399 EOF

2400 unset DT_SQL

2401 if [ x"$user" = x"$uname" ]; then

2402 # Display NEWS

2403 # 2016-06-26

2404 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2405 acclog blog $blogreadflagrowid

2406 # echo "全部既読にしました" | html p

2407 fi

2408 # 2016-02-19 Counting NEWS without using dumptable.

2409 sql=`listnewblogsql "$user"`

2410 # echo "$sql" > tmp/listnew

2411 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2412 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2413 cont=$((cont-1))

2414 ##err newcount=$cont

2415 if [ $cont -gt 0 ]; then

2416 #echo "全体の新着記事${cont}傑" | html h2

2417 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2418 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2419 cat<<-EOF | html form 'action="?home"'

2420 `cgi_checkbox readchk yes 'id="read"'`<label

2421 for="read">新着ふくめて全部読んだことにする</label>

2422 `cgi_submit '確定'`

2423 `cgi_hidden read yes`

2424 EOF

2425 echo "$new10 "

2426 echo "</div>"

2427 else # If news is 0, set log cut off flag

2428 acclog blog $blogcutoffflagrowid # for speed

2429 fi

2430 else # Not My Home ($user != $uname)

2431 : # DT_SQL=

2432 fi

2433 ) > $hometail & # Is background call safe to m4??

2434 #

2435 listgroupbytable $formdir/grp.def "$cond" $uname |

2436 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2437 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2438 -D_PROFIMG_="spaste(\`$imf')" \

2439 -D_BLOGS_="spaste(\`$bf')" \

2440 -D_SEARCH_="spaste(\`$sf')" \

2441 -D_NBLOG_="$nblog" \

2442 -D_GROUPS_="syscmd(\`cat')" \

2443 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2444 $layout/html.m4.html $layout/home.m4.html

2445

2446 # Record access log

2447 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2448 }

2449 commission() { # $1=grp-rowid $2=user-rowid

2450 contenttype; echo

2451 ## err commission: "$@"

2452 gname=`getgroupbyid $1`

2453 echo "グループ $gname 管理者委任" \

2454 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2455 if [ -n "$2" ]; then

2456 grp_reg_adm "$@"

2457 else

2458 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2459 fi

2460 }

2461 listgroupbytable() {

2462 # $1=deffile $2...=condition $3(optional)=uname

2463 tagline=`grep :tag: $1`;

2464 and="${2:+and }" where=${2:+where }

2465 href="<a href=\"$myname?grp+"

2466 echo '<div class="listgroup">'

2467 sql="select val from grp_s where key='tag' $and$2 group by val;"

2468 ## err ListGRP: query sql="$sql"

2469 for tag in `query "$sql"`

2470 do

2471 ## err ListGrp: tag=$tag

2472 tn=${tagline%%=${tag}*}

2473 tn=${tn##*[ :]}

2474 sql="select rowid||':'||gname as 'グループ名',説明 from

2475 (select (select rowid from grp g where g.gname=grp_s.gname)

2476 as rowid,

2477 gname,

2478 max(case key when 'gecos' then val end) as '説明',

2479 max(case key when 'tag' then val end) as 'tag',

2480 max(case key when 'mtime' then val end) as mtime from grp_s

2481 $where$2 group by gname having tag='$tag' order by mtime desc);"

2482 ## err PersonalGroupList= `echo $sql`

2483 echo "<h2>$tn</h2>"

2484 echo '<table class="b listgroup">'

2485 sq -header -html $db "$sql" \

2486 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2487 echo '</table>'

2488 done

2489 if [ -n "$S4WORLDLIST" -a -n "$3" ]; then

2490 err "peekgrpworlds($user) BEGIN: `gdate +%S.%03N`"

2491 peekgrpworlds mem:"$3"

2492 err "peekgrpworlds($user) END: `gdate +%S.%03N`"

2493 fi

2494 echo '</div>'

2495 }

2496 iconhref() (

2497 # $1=icon-file, $2=Href $3=title $4...=anchor

2498 data=`percenthex "$1"`

2499 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2500 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2501 href=$2; title=$3; shift 3

2502 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2503 )

2504 iconhref2() (

2505 # $1=icon-file, $2=Href $3=title $4...=anchor

2506 src=$1

2507 href=$2; title=$3; shift 3

2508 anchor=`echo $@|htmlescape`

2509 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2510 )

2511 listentry() (

2512 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2513 # Referring variable $iamowner=$grp to attach owner-request links

2514 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2515 cond='' hiddens=''

2516 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2517 if [ -z "$offset" ]; then

2518 offset=`getpar start`; offset=${offset%%[!0-9]*}

2519 offset=$((offset-1))

2520 fi

2521 offset=$((offset + 0)) # change to numeric forcibly

2522 [ $offset -lt 0 ] && offset=0

2523 limit=$listentlimit

2524 dir=`getcachedir "$1"`

2525 if [ x"$1" = x"user" ]; then

2526 hrb="$myname?home"

2527 deficon=person-default.png

2528 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2529 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2530 gcs=gecos

2531 else # if group

2532 hrb="$myname?grp"

2533 deficon=group-default.png

2534 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2535 gcs=name

2536 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2537 if [ -n "$tagline" ]; then

2538 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2539 ## err tagconv=$tagconv

2540 fi

2541 fi

2542 if [ ! -d $dir ]; then

2543 mkdir -p $dir

2544 fi

2545 if [ ! -s $dir/$deficon ]; then

2546 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2547 fi

2548 kwd=${2:-`getpar kwd`}

2549 if [ -n "$kwd" ]; then

2550 kwd=`echo $kwd | tr -d '";\n' | tr -d "'"`

2551 case "$kwd" in

2552 mem:*@*)

2553 byuser=${kwd#*mem:}

2554 qusr=`sqlquote "$byuser"`

2555 cond1="(a.gname IN (SELECT gname FROM grp_mem WHERE user=$qusr))"

2556 ;;

2557 esac

2558 if [ x"$1" = x"group" ]; then

2559 if [ -n "$cond1" ]; then

2560 enthead="`gecos "$byuser"|htmlescape` さんの所属"

2561 else

2562 cond1="(b.name like '%${kwd}%')"

2563 fi

2564 else

2565 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2566 fi

2567 fi

2568 tag=`getpar tag` tag2=`getpar tag2`

2569 if [ x"$tag" = x"NULL" ]; then

2570 tag="" tag2=""

2571 fi

2572 if [ -n "$tag$tag2" ]; then

2573 tag=${tag:-$tag2}

2574 qtag=`sqlquote "$tag"`

2575 cond2="tag=$qtag"

2576 fi

2577 if [ -n "$cond1$cond2" ]; then

2578 cond="$cond1${cond2:+ AND $cond2}"

2579 cond="WHERE ${cond# AND }"

2580 fi

2581

2582 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2583 # grpは呼出し元の動的スコープ変数でよくないな...

2584 ##qgrp=`sqlquote $grp`

2585 getgrp="(select gname from grp where rowid=${rowid:--1})"

2586 sql="select a.rowid, a.$link,

2587 coalesce(b.$gcs, a.$nm) as nick,

2588 quote(a.$nm) as qname,

2589 (SELECT val FROM ${tbl}_s

2590 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2591 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2592 || case when a.$nm in (select gname from grp)

2593 then printf('(%d名)',

2594 (select count(user) from grp_mem where gname=a.$nm))

2595 else ' <'||a.$nm||'>'

2596 end

2597 as name,

2598 b.tag,

2599 case when a.$nm in (select user from grp_adm

2600 where gname=$getgrp) then '管理者'

2601 when '$user' in (select user from grp_adm where gname=a.$nm)

2602 then 'ADMIN'

2603 when '$user' in (select user from grp_mem where gname=a.$nm)

2604 then 'Member'

2605 when '$iamowner' = '' then ''

2606 else ',not='||a.rowid end as ownerlink,

2607 CASE '$entity'

2608 WHEN 'グループ'

2609 THEN coalesce(

2610 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2611 'open')

2612 ||

2613 CASE WHEN '$user'

2614 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2615 THEN ' ismember'

2616 ELSE ''

2617 END

2618 ELSE 'user'

2619 END regmode

2620 from $tbl a left join

2621 (select $nm as name,

2622 max(case key when 'gecos' then val end) as gecos,

2623 max(case key when 'tag' then val end) as tag,

2624 max(case key when 'mtime' then val end) as mtime,

2625 max(case key when 'wtime' then val end) as wtime,

2626 max(case key when 'login' then val end) as login

2627 from ${tbl}_s group by $nm)

2628 b on a.$nm=b.name $cond $3

2629 order by b.wtime desc, b.login desc,

2630 b.mtime desc, b.tag desc, a.rowid asc"

2631 # Give precedence to newer maintained groups (2016-09-24)

2632 # Note that mtime is stored only in grp_s.

2633 ## err LE:sql.1="$sql"

2634 total=`query "with x as ($sql) select count(*) from x;"`

2635 echo "$enthead${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2636 echo '<div class="listentry">' # List-entry div

2637 # Show owner/member filter button

2638 METHOD=GET

2639 hiddens="$hiddens

2640 `cgi_hidden stage \"$stage\"`"

2641 if [ x"$tbl" = x"grp" ]; then

2642 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2643 fh="<select name=\"tag\">$nl"

2644 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2645 for l in $args; do

2646 val=${l#*=} tname=${l%=*}

2647 if [ x"$val" = x"$tag" ]; then

2648 s=" selected"

2649 selectedtags="(種別[${tname}]のみ)"

2650 else

2651 s=""

2652 fi

2653 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2654 done

2655 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2656 cat<<-EOF

2657 <form action="$myname" method="$METHOD">

2658 </form>

2659 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2660 for="ismembtn">参加中以外隠す</label>

2661 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2662 for="isadmbtn">管理者参加以外隠す</label>

2663 EOF

2664 # limit=3

2665 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2666 fi

2667 if [ $total -gt $limit -o $ -n "$S4WORLDLIST" -a x"$tbl" = x"grp" $ ]; then

2668 echo '<div>'

2669 METHOD=GET cgi_form $stage <<EOF

2670 $form

2671 <label>次の語を含む${entity}で検索:

2672 `cgi_text kwd "$kwd"`</label>

2673 $hiddens

2674 EOF

2675 echo '</div>'

2676 else

2677 echo $selectedtags | html p

2678 fi

2679 test -n "$kwd" && hiddens="$hiddens$nl`cgi_hidden kwd \"$kwd\"`"

2680 cat<<EOF

2681 <form action="$myname" method="$METHOD">

2682 <p>${total}件中の<input class="hidesub" type="text" name="start"

2683 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2684 <input type="submit" value="確定"></p>

2685 </form>

2686 EOF

2687 if [ $((offset+limit)) -lt $total ]; then

2688 nextbtn=$(

2689 cat<<EOF

2690 <div class="right clear"><form action="$myname" method="$METHOD">

2691 `cgi_submit 次の${limit}件`

2692 $hiddens

2693 `cgi_hidden offset $((offset + limit))`</form></div>

2694 EOF

2695 )

2696 fi

2697 if [ $offset -gt 0 ]; then

2698 prevbtn=$(

2699 cat<<EOF

2700 <form action="$myname" method="$METHOD">

2701 `cgi_submit 前の${limit}件`

2702 $hiddens

2703 `cgi_hidden offset $((offset - limit))`</form>

2704 EOF

2705 )

2706 fi

2707 pnbtn="$nextbtn$prevbtn"

2708 echo $pnbtn

2709

2710 ## err ListEntry: `echo "$sql"\;`

2711 # sq $db here??? 2016-11-28

2712 query "$sql limit $limit ${offset:+offset $offset};" \

2713 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2714 # err name=$name owner=$ownerp lnk=$lnk

2715 # err newlnk=$lnk regmode=$regmode

2716 icondir=$dir/$id

2717 # Pick up only last icon

2718 htmlname=`echo $name|htmlescape`

2719 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2720 <p class=\"tag _$tag\">$tag</p>" \

2721 | _m4 $tagconv

2722 if [ -n "$NOSPEEDUP" ]; then

2723 files=`getvalbyid $tbl profimg $id $icondir`

2724 if [ -n "$files" ]; then

2725 icon=`echo "$files"|tail -1`

2726 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2727 else

2728 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2729 fi

2730 elif [ -n "$icon" -a -s "$icon" ]; then

2731 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2732 else

2733 cond="$nm=$qname"

2734 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2735 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2736 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2737 # err "img=[$img]"

2738 if [ -n "$img" ]; then

2739 echo "<a href=\"$hrb+$lnk\">"

2740 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2741 echo "</a>"

2742 else

2743 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2744 fi

2745 fi

2746 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2747 echo "</div>"

2748 done

2749 echo "</div>" # End of List-entry div

2750 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2751 if [ -n "$kwd" -a x"$tbl" = x"grp" -a -n "$S4WORLDS" ]; then

2752 peekgrpworlds "$kwd"

2753 fi

2754 )

2755 listmember() {

2756 listentry user "$@"

2757 }

2758 listgroup() {

2759 listentry group "$@"

2760 }

2761 hexteams() { # $1=gname, $2(optional)=user

2762 cond=${2:+" AND user='$2'"}

2763 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2764 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2765 }

2766 showgroup() { # $1=group-rowid

2767 if [ -z "$1" ]; then

2768 grid=`getpar grid`

2769 grid=${grid%%[!0-9]*}

2770 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2771 else

2772 grid=$1

2773 fi

2774 grp=`getgroupbyid $grid`

2775 qgrp=`sqlquote "$grp"`

2776 htmlgrp=`echo "$grp"|htmlescape`

2777 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2778 if isgroup "$grp"; then

2779 tf=$tmpd/title.$$

2780 sf=$tmpd/search.$$

2781 bodyclass=`query "SELECT val FROM grp_s

2782 WHERE gname=$qgrp AND key='regmode';"`

2783 if ismember "$user" "$grp"; then

2784 ismember="ismember"

2785 bodyclass="$bodyclass${bodyclass:+ }ismember"

2786 else

2787 ismember="" # bodyclass="group"

2788 fi

2789 bodyclass="$bodyclass grouphome"

2790 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2791 echo "グループ $htmlgrp" > $tf

2792

2793 showgroupsub $formdir/grp.def "$grid" | \

2794 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2795 -D_FORM_="syscmd(\`cat')" \

2796 -D_BODYCLASS_="$bodyclass" \

2797 -D_DUMPTABLE_="" \

2798 $htmlheader $sf $layout/form+dump.m4.html

2799 # $htmlheader $layout/form+dump.m4.html

2800 # $htmlheader is defined in grp()

2801 else # if $grp is removed at par2table

2802 listgroup

2803 fi

2804 }

2805 showgroupsub() {

2806 # $1=def-file $2=group-rowid

2807 # Using $ismember

2808 rowid=$2

2809 grp=`getgroupbyid $2`

2810 qgrp=`sqlquote "$grp"`

2811 td=`getcachedir grp/"$2"`

2812 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2813 if [ -z "$rowid" ]; then

2814 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2815 #grp=`sq $db "select gname from grp where rowid=$grp"`

2816 echo "showgroupsub: invalid argument($1 $2)" | html p

2817 return

2818 fi

2819 val=`getvalbyid grp profimg $rowid $tmpd`

2820 enticond="gname=$qgrp"

2821 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2822 if [ -n "$img" ]; then

2823 cat<<-EOF

2824 <p class="groupimg">

2825 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2826 EOF

2827 fi

2828 echo "<div class=\"noprofimg\">"

2829 viewtable $1 grp $rowid

2830 echo "</div>"

2831 if isgrpowner "$user" "$grp"; then

2832 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2833 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2834 iamowner=$rowid

2835 colmd=" mode"

2836 fi

2837 if [ -n "$ismember" ]; then

2838 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2839 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2840 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2841 cat<<-EOF

2842 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2843 href="?blog+$rowid">グループの新規話題作成</a>

2844 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2845 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2846 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2847 <div class="fold clear">

2848 `cgi_checkbox send yes id="send"`<label

2849 for="send">グループ全員にメッセージ送信</label>

2850 <div>

2851 `cgi_textarea message "" "cols=60"`

2852 `cgi_submit 送信`

2853 `cgi_reset リセット`

2854 </div>

2855 `cgi_hidden grp $rowid`

2856 </div></form>

2857 EOF

2858 fi

2859 # 加入ボタン + 加入者リスト

2860 if [ -n "$ismember" ]; then

2861 ismem='checked' state="(参加中)"

2862 else

2863 nomem='checked' state="(現在非加入)"

2864 fi

2865 # このグループでの加入アドレス

2866 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2867 and key='email';"`

2868 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2869 ## and key='email';"

2870 ##err email=$eml

2871 cat <<EOF

2872 <div class="fold clear">

2873 `cgi_checkbox reg yes id="reg"`<label

2874 for="reg">自身の加入状態を操作する</label>$state

2875 <div>

2876 EOF

2877 cgi_form grp <<EOF

2878 <p>このグループに</p>

2879 <table class="b">

2880 <tr><th>メンバーとして</th><td>

2881 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2882 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2883 <tr><th>参加する場合のメイルアドレス<br>

2884 <small>(メインのアドレスとは違うものにする場合に記入<br>

2885 同じでよい場合は空欄に)</small></th>

2886 <td>`cgi_text email $eml`</td></tr>

2887 </table>

2888 `cgi_hidden grp $rowid`

2889 EOF

2890 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2891 echo "moderated (承認加入の)グループなので実際に参加できるのは

2892 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2893 fi

2894 echo '</div></div>'

2895 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2896 cat<<-EOF

2897 <div class="fold">

2898 <h2>話題一覧</h2>

2899 <form class="summary inline" action="$myname" title="$thelp">

2900 `cgi_hidden owner "$grp"`

2901 `cgi_hidden kwd "@week"`

2902 `cgi_hidden stage searchart`

2903 `cgi_submit "一週間のまとめ"`

2904 </form>

2905 <input type="checkbox" id="dtcheck" checked><label

2906 for="dtcheck">話題一覧表示</label>

2907 EOF

2908 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by 稼動状態, ctime desc"

2909 colstate="state:稼動状態:frozen=rowclass=凍結"

2910 frzbtn='<button class="toggle-frozen"></button>'

2911 DT_CHLD=article:blogid \

2912 DT_QOWNER="$qgrp" \

2913 DT_VIEW=replyblog dumptable html blog \

2914 "ctime title heading team notify:通知$colmd $colstate" "$cond" \

2915 | if [ -n "$iamowner" ]

2916 then

2917 sed -Ee "s,(<TD class=\"稼動状態\">).*(</TD>),\1$frzbtn\2,"

2918 else

2919 cat

2920 fi

2921 ## DO not convert to frzbtn when not admin

2922 echo "</div> "

2923

2924 getgname="(select gname from grp where rowid=$rowid)"

2925 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2926 cm="?commission+$rowid"

2927 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2928 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2929 # team list

2930 hexteams=`hexteams "$grp"`

2931 if [ -n "$hexteams" ]; then

2932 echo "チーム一覧" | html h2

2933 echo '<div class="dumptable"><table class="b">'

2934 sq $db -html -header<<-EOF

2935 SELECT val TEAM,

2936 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2937 MEMBERS

2938 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2939 EOF

2940 echo '</table></div>'

2941 fi

2942 }

2943 grp_getbodyclass() {

2944 # Get css class name for document.

2945 # `moderated' for moderated groups

2946 # `ismember' for groups where user belongs

2947 # $1=GroupName (w/o quote)

2948 # $user=userNameCurrentlyLogin

2949 ## err grp_getbodyclass: 1="$1"

2950 qgrp=`sqlquote "$1"`

2951 query<<-EOF

2952 SELECT coalesce(

2953 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2954 'open')

2955 ||

2956 CASE WHEN '$user'

2957 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2958 THEN ' ismember'

2959 ELSE ''

2960 END;

2961 EOF

2962 }

2963 grpaction() { # $1=group-rowid

2964 err GRP_ACTION:IN

2965 grid=${1:-`getpar grp`}

2966 grp=`getgroupbyid "$grid"`

2967 htmlgrp=`echo "$grp" | htmlescape`

2968 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2969 if [ -z "$grp" ]; then

2970 echo "無効な指定です。" | html p; return

2971 fi

2972 if ! ismember $user "$grp"; then

2973 echo "加入者のみに許可された操作です。" | html p; return

2974 fi

2975 isowner=""

2976 isgrpowner "$user" "$grp" && isowner="yes"

2977 err 2=$2 3=$3

2978 case "$2" in

2979 get:teamcsv)

2980 teamcsv "$grid"

2981 return

2982 ;;

2983 esac

2984 echo "グループ $grp 個別選択操作" \

2985 | _m4 -D_TITLE_="syscmd(\`cat')" \

2986 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2987 $layout/html.m4.html

2988

2989 usel=`getpar usel`

2990 if [ -n "$usel" ]; then

2991 uids=$(echo `echo $usel`|tr ' ' ',')

2992 ## err grpaction-1: grp=$grp, `echo $sql`

2993 text=`getpar text|tr -d '\r'`

2994

2995 rm=`getpar rm` cfm=`getpar confirm`

2996 ## err rm=$rm cfm=$cfm

2997 if [ x"$rm" = x"yes" ]; then

2998 if [ "$isowner" ]; then

2999 if [ x"$rm$cfm" = x"yesyes" ]; then

3000 # Eliminate

3001 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

3002 for tbl in grp_mem grp_mem_s grp_mem_m; do

3003 sql="delete from $tbl $cond;"

3004 # echo "sql=$sql"

3005 query "$sql"

3006 err rmGRPuser "$sql"

3007 done

3008 num=`query "select count(*) from user where rowid in ($uids);"`

3009 #err num=$num

3010 if [ 0$num -gt 0 ]; then

3011 sql="select coalesce(b.val,a.name) from user a left join \

3012 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

3013 # err `echo "$sql"`

3014 html pre<<EOF

3015 以下の${num}名のグループ $grp 登録を解除しました。

3016 `query "$sql"`

3017 EOF

3018 fi

3019 else

3020 echo "確認のチェックがないのでやめておきます。" | html p

3021 return

3022 fi

3023 else # not Group Owner

3024 echo "グループ管理者でないのでメンバー操作はできません。" | html p

3025 return

3026 fi

3027 cat<<EOF

3028

3029 EOF

3030 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

3031 if [ -z "$text" ]; then # if msg is empty

3032 echo "なにかメッセージを..." | html p

3033 return 0

3034 fi

3035 gecos=`gecos $user`

3036 safegc=`echo "$gecos" | tr -d '<>@,'`

3037 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3038 fromad=`myemail4group "$grp"`

3039 ###mail_from="$safegc <$fromad>"

3040 mail_from="$safegc <$user>" # TEST: 2020/5/13

3041 test -n `getpar sender` &&

3042 export SENDER=$user # TEST: 2020/5/15

3043 replyto=$fromad

3044

3045 ## Start parse of attachment files

3046 if [ -n "`getpar email`" ]; then

3047 ar=`getpar supprcpt`

3048 if [ -n "$ar" ]; then

3049 for a in $ar; do

3050 if checkdomain "$a"; then

3051 supprcpt="$supprcpt $a"

3052 else

3053 err "SupprcptErr=[$a] by $user"

3054 removercpt="$removercpt $a"

3055 fi

3056 done

3057 fi

3058 subj=`getpar subject`

3059 afiles=""

3060 for fn in `query "SELECT DISTINCT val FROM par WHERE var='image' AND sessid='$session';"`

3061 do

3062 f=$tmpd/$fn

3063 if [ -s $f ]; then

3064 afiles=$afiles"${afiles:+ }$f"

3065 fi

3066 done

3067 else

3068 preface=$(cat <<-EOF

3069 $url

3070 のグループ「$grp」のメンバーである $gecos さんから、

3071 あなた宛のメッセージです。

3072 ----------------------------------------------------------

3073 EOF

3074 )

3075 fi

3076 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

3077 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

3078 subj="${subj:-$gecos さんからのメッセージ}"

3079 REPLYTO=$replyto

3080 MAIL_FROM=$mail_from

3081 export REPLYTO SMAIL_TO MAIL_FROM

3082 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

3083 for r in $rcpts; do

3084 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

3085 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

3086 else

3087 # Show sender and rcpts address for guest

3088 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

3089 fi

3090 if [ -n "$afiles" ];then

3091 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

3092 else

3093 smail "$r" "$subj"

3094 fi <<EOF

3095 ${preface:+$preface$nl}$text

3096 EOF

3097 done

3098 if [ $? = 0 ]; then

3099 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

3100 sql="select coalesce(b.val, a.name) from

3101 (select name from user where rowid in ($uids)) a

3102 left join user_s b on a.name=b.name and b.key='gecos';"

3103 html pre<<EOF

3104 `query "$sql"`

3105 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

3106 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

3107 EOF

3108 err SendDone: `echo $sql`

3109 fi

3110 elif [ x"$rm" = x"commission" ]; then

3111 grp_reg_adm $grid $usel

3112 elif [ x"$rm" = x"addteam" ]; then

3113 team=`getpar team|sed "s/'/''/g"` # for single quotation

3114 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

3115 if [ x"$team" != x"$newteam" ]; then

3116 echo "チーム名に使えない文字を除去しました" | html p

3117 team=$newteam

3118 fi

3119 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

3120 cat<<-EOF | html p

3121 有効なチーム名を入力してください。

3122 カンマだけ、「なし」という名前は使えません。

3123 EOF

3124 echo "有効なチーム名を入力してください。" | html p

3125 else

3126 grp_add_team $grid "$team" $usel

3127 fi

3128 elif [ x"$rm" = x"rmteam" ]; then

3129 if [ x"yes" = x"`getpar teamconfirm`" ]; then

3130 rmteam=`getpar rmteam|sed "s/'/''/g"`

3131 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

3132 gname='$grp' AND user='$user' AND key='team'\

3133 AND val='$rmteam';\"`" ]; then

3134 grp_rm_team $grid "$rmteam" $usel

3135 else

3136 echo "所属していないチームの除去操作はできません。"|html p

3137 fi

3138 else

3139 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

3140 fi

3141 fi

3142 fi

3143 # POST count summary

3144 from=`getpar from`; to=`getpar to`

3145 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3146 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3147 fromtonote="<p title=\"Count the Number of Posts from-to\">POST/ACCESS集計: $from_input - $to_input</p>"

3148 # New entry

3149 sql="WITH mems AS (

3150 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3151 ON gm.user=g.name

3152 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3153 ), target_article AS (

3154 SELECT id FROM article_s

3155 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3156 ), posts AS (

3157 SELECT author, count(author) post

3158 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3159 WHERE blogid IN (SELECT id FROM blog_s

3160 WHERE key='owner'

3161 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3162 AND key='text'

3163 GROUP BY author

3164 ), teams AS (

3165 SELECT user, group_concat(val, ', ') team

3166 FROM mems m LEFT JOIN grp_mem_m gm ON m.name=gm.user

3167 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3168 AND key='team'

3169 GROUP BY user

3170 ), user_post AS (

3171 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3172 FROM mems m LEFT JOIN posts

3173 ON m.name=posts.author

3174 GROUP by m.rowid

3175 ), user_view AS (

3176 SELECT user vuser,count(user) cnt

3177 FROM tblaccesses

3178 WHERE user IN (

3179 SELECT user FROM grp_mem

3180 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid))

3181 AND tbl='blog'

3182 AND tblrowid IN (

3183 SELECT rowid FROM blog

3184 WHERE id IN (

3185 SELECT id FROM blog_s

3186 WHERE key='owner' AND val=(

3187 SELECT gname FROM grp WHERE rowid=$grid)))

3188 AND time BETWEEN '${from:-0000}' AND '${to:-9999}'

3189 GROUP BY user ORDER BY cnt

3190 )

3191 SELECT

3192 CASE

3193 WHEN (SELECT user FROM grp_adm

3194 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3195 AND user=up.name) IS NOT NULL

3196 then 'k'

3197 ELSE ''

3198 END || rowid || ','

3199 || rtrim(substr(name, 1, instr(name, '@')), '@') || ','

3200 || gecos NAME,

3201 post POST,

3202 (coalesce((SELECT cnt FROM user_view WHERE vuser=name), 0)) ACCESS,

3203 team _TEAM_

3204 FROM user_post up LEFT JOIN teams t

3205 ON up.name=t.user

3206 ORDER BY up.name;"

3207 ## Want to count ACCESS with JOIN, but failed to produce user-unique rows

3208 ## err grpaction: "`echo \"$sql\"`"

3209 tf=$tmpd/title.$$

3210 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3211 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3212 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3213 for=\"cmadmin\">管理者委任</label>

3214 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3215 </p></div>"

3216 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3217 title=\"Shortcut: g${nl}Remove from the Group\"

3218 for=\"conf\">GRP登録解除</label>

3219 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3220 <p>この操作による通知は本人に行きません。

3221 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3222 </div>"

3223 submitBTN=`cgi_submit 送信`

3224 # Get team list to which current user belongs into $hexteams

3225 allhexteams=$(hexteams "$grp")

3226 if [ -n "$isowner" ]; then

3227 myhexteams="$allhexteams" # admin can remove all teams' attr

3228 else

3229 myhexteams=$(hexteams "$grp" "$user")

3230 fi

3231 if [ -n "$isowner" -a -n "$allhexteams" ]; then

3232 gettingcsv="<p>Download: <a href=\"?getteamcsv+$grid\">Team.csv</a> (Zoom Breakout Room 事前割り当てに使えます), <a href=\"?getteamcsv+$grid+name\">Team-with-name.csv</a>(参照用名前付)</p>"

3233 fi

3234 if [ -n "$myhexteams" ]; then

3235 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3236 title=\"Shortcut: s${nl}Strip a team tag from\"

3237 for=\"cmrmteam\">チーム属性除去</label>

3238 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3239 を除去します: `cgi_checkbox teamconfirm yes` 確認 $submitBTN

3240 <p>この操作による通知は本人に行きません。

3241 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3242 </div>

3243 "

3244 fi

3245 stf=$tmpd/selteam.$$

3246 cgi_select_h selteam "5445414d" $allhexteams > $stf

3247 b1='<label> <input type="checkbox" name="usel" value="'

3248 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3249 br='<span id="reverse" title="Reverse Selection"></span>'

3250 #b2='"> <span>' b3='</span></label>'

3251 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

3252 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

3253 lnk='"> <span>\5</span></label> [<a href="?home+\3">HOME</a>]'

3254 cgi_form grpaction<<EOF \

3255 | sed -E \

3256 -e "s|^(<TR><TD)>(k?)([0-9]*),([^,]+),([^<]*)|\1 title=\"\4\">\2$b1\3$lnk|" \

3257 -e 's/^(<TR><TD[^>]*>)k(<label)/\1\2 class="admin"/' \

3258 -e "s|^(<TR><TH>)(NAME)|\1$br \2|" \

3259 | _m4 -D_TITLE_="spaste(\`$tf')" \

3260 -D_SUBTITLE_="チェック後操作ボタン" \

3261 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3262 $layout/form+dump.m4.html \

3263 | _m4 -D_TEAM_="spaste(\`$stf')"

3264 <p>下でチェックした人を対象として:</p>

3265 <div class="foldtabs">

3266 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3267 title="Shortcut: a${nl}Add a team tag to"

3268 for="cmteam">同じチーム属性を付与</label>

3269 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3270 `cgi_datalist_h teams $allhexteams`$submitBTN

3271 </div>

3272 ${rmteammsg}

3273 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3274 title="Shortcut: d${nl}DirectMail to"

3275 for="sendmsg" title="Direct Message">DM送信</label>

3276 <div>

3277 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3278 title="Using email format">email書式を使う</label>

3279 <div class="folded">

3280 <table>

3281 <tr><td>From: </td><td>$user</td></tr>

3282 <tr><td>このFrom:で送る</td>

3283 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3284 </td></tr>

3285 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3286 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3287 <tr><td>ファイル添付: </td>

3288 <td>`cgi_file image "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3289 </table>

3290 <p>(下記一覧から1人以上選択していない場合は送れません$submitBTN)</p>

3291 </div>

3292 <div>本文:`cgi_textarea text "" cols=72`

3293 </div>

3294 </div>

3295 ${isowner:+$cmmsg$excmsg}

3296 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3297 </div>

3298 <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote

3299 <table class="td2r td3r thl">

3300 `sq $db -header -html "$sql"`

3301 </table>

3302 `cgi_hidden grp $grid`

3303 `cgi_hidden myuid $myuid id="myuid"`

3304 EOF

3305 }

3306 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3307 # Create TEMPORARY VIEW

3308 query<<EOF

3309 CREATE TEMPORARY VIEW writeusers AS

3310 SELECT DISTINCT author FROM article

3311 WHERE id in (

3312 select id from article where blogid=(select id from blog where rowid=$1)

3313 );

3314 CREATE TEMPORARY VIEW movablegroups AS

3315 SELECT g.rowid growid , g.gname

3316 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3317 ON grp.gname=gm.gname -- そのユーザが属している

3318 AND user='$user') g -- グループに絞る

3319 WHERE (SELECT author FROM writeusers

3320 EXCEPT

3321 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3322 IS NULL;

3323 $2

3324 EOF

3325 }

3326 sql4readableblogs() {

3327 # Create view of blogs that can be readable to $user

3328 # Blog is readable when:

3329 # 1: blog owner is an user

3330 # 2: else, 2.1: owner-group where the $user belongs

3331 # 2.2: else, owner-group is not moderated

3332 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3333 cat<<EOF ## | tee tmp/sql.out

3334 CREATE TEMPORARY VIEW readableblogs AS

3335 SELECT blog.rowid rid, id, author

3336 FROM blog

3337 NATURAL JOIN

3338 (SELECT id,

3339 max(CASE key WHEN 'owner' THEN val END) owner,

3340 max(CASE key WHEN 'mode' THEN val END) mode

3341 FROM blog_s GROUP by id) bs

3342 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3343 THEN 1 -- blog owner is an user, READABLE

3344 WHEN (SELECT val FROM grp_s

3345 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3346 AND

3347 (SELECT user FROM grp_mem

3348 WHERE gname=bs.owner AND user='$user') IS NULL

3349 THEN 0

3350 WHEN mode IN ('quiz', 'enquete')

3351 THEN 0 -- "quiz" mode blog cannot be searched

3352 ELSE 1

3353 END;

3354 EOF

3355 }

3356 mvteamform() {

3357 owner=$1

3358 hexteams=$(hexteams "$owner" "$user")

3359 test -z "$hexteams" && return

3360 none="`echo なし|hexize`"

3361 cat<<-EOF

3362 <!-- <div class="fold">

3363 `cgi_checkbox mv2team send id="mv2team"`<label

3364 for="mv2team">この話題を以下のチームのものにする</label>

3365 <div> -->

3366 <p>この話題をチーム所有にする:

3367 チーム: `cgi_select_h mv2team $none $hexteams`</p>

3368 </form></div></div>

3369 EOF

3370 }

3371 editheading() { # $1=rowid-of-heading

3372 rowid=${1%%[!A-Z0-9a-z_]*}

3373 if [ -z "$rowid" ]; then

3374 echo "話題番号が未指定です。" | html p

3375 return

3376 fi

3377 owner=`getvalbyid blog owner $rowid`

3378 title=`getvalbyid blog title $rowid`

3379 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3380 | _m4 -D_TITLE_="修正" \

3381 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3382 -D_BLOGS_="" -D_DUMPTABLE_="" \

3383 -D_FORM_="syscmd(\`cat')" \

3384 $layout/html.m4.html $layout/form+dump.m4.html

3385 # Move to group

3386 if isuser "$owner"; then

3387 crview4article $rowid

3388 n=`query "SELECT count(*) FROM writeusers;"`

3389 ## err N=$n

3390 if [ $((n)) -gt 0 ]; then

3391 ## err ROWID=$rowid

3392 sql="SELECT growid || ':' || gname FROM movablegroups;"

3393 cat<<-EOF

3394 <div class="fold">

3395 `cgi_checkbox mv send id="mv"`<label

3396 for="mv">この話題をグループ所有に移動する</label>

3397 <div>

3398 <form action="?mvart" method="POST" enctype="multipart/form-data">

3399 移動先グループ:

3400 <select name="mv2grp">

3401 EOF

3402 query ".mode html"

3403 query<<-EOF |

3404 $sql

3405 .mode list

3406 EOF

3407 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3408 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3409 cat<<-EOF

3410 </select>

3411 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3412 そのグループに加入しているものに限られます)</p>

3413 <p>`cgi_checkbox cfm yes`<label>確認

3414 (この操作は元に戻すことができません)</label></p>

3415 `cgi_hidden blogrowid $rowid`

3416 `cgi_submit 移動`

3417 `cgi_reset Reset`

3418 </form>

3419 </div>

3420 </div>

3421 EOF

3422 fi

3423 # end of isuser "$owner"

3424 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3425 [ -n "$hexteams" ];}; then

3426 none="`echo なし|hexize`"

3427 cat<<-EOF

3428 <div class="fold">

3429 `cgi_checkbox mv2team send id="mv2team"`<label

3430 for="mv2team">この話題を以下のチームのものにする</label>

3431 <div><p>現在の所属チーム設定:

3432 `query "SELECT

3433 coalesce((SELECT val FROM blog_s

3434 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3435 AND key='team'),

3436 ':なし');"`</p>

3437 <form action="?mvart" method="POST" enctype="multipart/form-data">

3438 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3439 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3440 `cgi_hidden blogrowid $rowid`<br>

3441 `cgi_submit 移動`

3442 `cgi_reset Reset`

3443 </form></div></div>

3444 EOF

3445 fi

3446 }

3447 mvart() { # move diary to some group or team

3448 # or move blog of group to team which belong to the group

3449 blogrowid=`getpar blogrowid`

3450 cfm=`getpar cfm`

3451 ##### echo move blog:$blogrowid to $mv2grp | html p

3452 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3453 . ./s4-blog.sh

3454 if [ -z "$blogrowid" ]; then

3455 echo "無効な指定です(mvart)。" | html p

3456 return

3457 elif [ x"$cfm" != x"yes" ]; then

3458 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3459 elif { mv2grp=`getpar mv2grp`

3460 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3461 [ -n "$mv2grp" ]; }; then

3462 crview4article $blogrowid

3463 ########## TRANSACTION BEGIN

3464 query "BEGIN;"

3465 n=`query "SELECT count(*) FROM writeusers;"`

3466 ## err Nwriteuser=$n

3467 if [ $((n)) -gt 0 ]; then

3468 query<<-EOF

3469 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3470 WHERE key='owner'

3471 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3472 AND $mv2grp IN (SELECT growid FROM movablegroups);

3473 EOF

3474 fi

3475 query "END;"

3476 ########## TRANSACTION END

3477 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3478 [ -n "$mv2team" ];}; then

3479 # blog owner can move it to ANY team

3480 case "$mv2team" in

3481 'なし')

3482 cat<<-EOF

3483 DELETE FROM blog_s

3484 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3485 AND key='team';

3486 EOF

3487 ;;

3488 "") ;;

3489 *)cat<<-EOF

3490 BEGIN;

3491 REPLACE INTO blog_s(id, key, val)

3492 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3493 'team', '$mv2team');

3494 REPLACE INTO blog_s(id, key, val)

3495 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3496 'notify', 'all'); -- Change notify to all

3497 END;

3498 EOF

3499 esac | query

3500 fi

3501 blog_reply $blogrowid

3502 echo yes | html p

3503 }

3504 editart() { # $1=article-rowid $2=blogrowid

3505 rowid=${1%%[!A-Z0-9a-z_]*}

3506 blogrowid=${2%%[!A-Z0-9a-z_]*}

3507 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3508 echo "表示する記事番号が未指定です。" | html p

3509 return

3510 fi

3511 owner=`getvalbyid blog owner $blogrowid`

3512 title=`getvalbyid blog title $blogrowid`

3513 author=`getvalbyid article author $rowid`

3514 math=`getvalbyid blog mathjax $blogrowid`

3515 ## err EDITart: owner=$owner, author=$author

3516 if isgrpowner "$user" "$owner"; then

3517 : EDIT OK

3518 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3519 echo "本人か所有者しか編集できません." | html p

3520 return

3521 fi

3522 aid=`query "select id from article where rowid=$rowid;"`

3523 tmpout=$tmpd/editart.$$.out

3524 GF_ACTION="?replyblog+$blogrowid#$aid" \

3525 edittable $formdir/article.def article $rowid \

3526 > $tmpout

3527 printf '%s' "${math:+$mathjax}" >> $tmpout

3528 # Cannot use pipelining to m4 with genform() because of stdin stack

3529 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3530 -D_FORM_="syscmd(cat $tmpout)" \

3531 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3532 -D_BLOGS_= -D_DUMPTABLE_= \

3533 $layout/html.m4.html $layout/form+dump.m4.html

3534 }

3535 send2mem() {

3536 rowid=`getpar grp`

3537 rowid=${rowid%%[!0-9]*} # Cleaning

3538 if [ -z "$rowid" ]; then

3539 echo "グループが未指定です。" | html p

3540 return

3541 fi

3542 message=`getpar message`

3543 if [ -z "$message" ]; then

3544 echo "文章を入れてください。" | html p

3545 return

3546 fi

3547 grp=`getgroupbyid $rowid`

3548 members=`collectemail "$grp"`

3549 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3550 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3551 mailfrom="`gecos "$user"` <$mailfrom>"

3552 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3553 msg=$(cat<<-EOF

3554 $urlbase?grp+$rowid

3555 グループ $grp に所属する

3556 `gecos $user` さんよりメッセージ:

3557

3558 $message

3559 EOF

3560 )

3561 # smail rcpt subj (file)

3562 for m in $members; do

3563 echo "$msg" |

3564 MAIL_FROM=$mailfrom \

3565 SENDER=$noreply \

3566 REPLYTO=$mailfrom \

3567 smail "$m" "$sj"

3568 done

3569 cat<<EOF

3570 <p>以下のユーザに送信しました。</p>

3571 <pre>

3572 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3573 </pre>

3574 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3575 EOF

3576 }

3577 joingrpadmit() {

3578 # $1=yes/no $2=session-key

3579 if [ -z "$2" ]; then

3580 echo "bye bye" | html p; return

3581 fi

3582 t_usr=`session=$2 getpar adduser`

3583 t_grp=`session=$2 getpar group`

3584 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3585 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3586 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3587 echo "無効な加入依頼です。" | html p

3588 echo "有効期限が切れたか、

3589 他の管理者がいる場合は処理済みの可能性があります。" | html p

3590 return

3591 fi

3592 if ! isgrpowner "$user" "$t_grp"; then

3593 echo "グループ管理者のみの機能です。" | html p; return

3594 fi

3595 case $1 in

3596 yes) joingrp "$t_grp" "$t_usr" yes ;;

3597 no) joingrp "$t_grp" "$t_usr" no ;;

3598 *)

3599 echo "無効な指定です($1)。" | html p

3600 return ;;

3601 esac

3602 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3603 rcpts="`getgroupadminmails "$t_grp"` $user"

3604 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3605 body="に

3606 $t_usr

3607 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3608 しました。"

3609 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3610 query "delete from session where id='$2';"

3611 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3612 }

3613

3614 joingrprequest() {

3615 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3616 jss="joingrp-`date +%s`-`genrandom 12`"

3617 addsession $jss +${memoplimitdays}days

3618 grpadmins=`getgroupadmins "$1"`

3619 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3620 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3621 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3622 $url

3623 $user さんから

3624 グループ $1

3625 に加入依頼がありました。

3626

3627 承認する:

3628 $urlbase?joingrpadmit+yes+$jss

3629

3630 白紙に戻す:

3631 $urlbase?joingrpadmit+no+$jss

3632 EOF

3633 echo "管理者に加入依頼を出しました。

3634 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3635 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3636 }

3637 joingrp() {

3638 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3639 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3640 if isgrpowner "$user" "$1"; then

3641 isowner="yes"

3642 elif [ -n "$5" ]; then

3643 isowner="yes"

3644 else

3645 isowner=""

3646 fi

3647 ## err jg:isgrpowner: isowner="$isowner"

3648 if [ -n "$isowner" ]; then

3649 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3650 elif [ x"$2" != x"$user" ]; then # if user is not login user

3651 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3652 return

3653 elif [ x"$3" = x"no" ]; then

3654 : # Do not pursue those who leave

3655 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3656 : # Member can change own email address for the joining moderated group

3657 else # adding user is $user itself

3658 case `getgroupattr "$1" regmode` in

3659 moderated)

3660 joingrprequest "$@" # Request only

3661 return

3662 ;;

3663 *)

3664 ;;

3665 esac

3666 fi

3667 qgname=`sqlquote "$1"`

3668 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3669 cond="where gname=$qgname and user='$2'"

3670 if [ x"$3" = x"yes" ]; then

3671 query "replace into grp_mem values($qgname, '$2');"

3672 # Notify joingrp to admin

3673 action="に加入しました。"

3674 if [ -n "$4" ]; then

3675 if msg=`emaildomaincheck "$4"`; then

3676 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3677 'string', '$4', NULL);"

3678 else

3679 echo $msg

3680 fi

3681 else

3682 query "delete from grp_mem_s $cond and key='email';"

3683 fi

3684 if [ -n "$5" ]; then # as ADMIN

3685 # Coming here means newly created group

3686 sql="select case\

3687 when (select count(*) from grp_mem where gname=$qgname)=1\

3688 then (select user from grp_mem\

3689 where gname=$qgname and user='$user')\

3690 else '' end; "

3691 err NewGrpChk: $sql

3692 if [ -n "`query \"$sql\"`" ]; then

3693 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3694 query "replace into grp_adm values($qgname, '$user');"

3695 fi

3696 fi

3697 else

3698 query "begin;

3699 delete from grp_mem $cond;

3700 delete from grp_mem_s $cond;

3701 delete from grp_mem_m $cond;

3702 delete from grp_adm $cond;

3703 delete from grp_adm_s $cond;

3704 delete from grp_adm_m $cond;

3705 end;"

3706 action="から脱退しました。"

3707 fi

3708 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3709 $url?grp+$grid

3710 $user (`gecos $user`)さんが

3711 グループ $1

3712 $action

3713 EOF

3714 }

3715 grp_add_team() (

3716 # $1=grp-rowid $2=team $3...=user-rowid(s)

3717 grp=`getgroupbyid $1`

3718 team=$2; shift; shift

3719 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3720 { echo "BEGIN;"

3721 for user; do

3722 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3723 '$grp',\

3724 (SELECT name FROM user WHERE rowid=$user),\

3725 'team', 'string', '$team');"

3726 done

3727 echo "END;"

3728 } | query

3729 )

3730 grp_rm_team() (

3731 # $1=grp-rowid $2=team $3...=user-rowid(s)

3732 grid=$1

3733 qgrp=$(sqlquote "`getgroupbyid $grid`")

3734 team=$2; shift; shift

3735 [ -z "$grid" -o -z "$team" ] && return

3736 { echo "BEGIN;"

3737 for user; do

3738 echo "DELETE FROM grp_mem_m\

3739 WHERE gname=$qgrp \

3740 AND user=(SELECT name FROM user WHERE rowid=$user)\

3741 AND key='team' AND val='$team';"

3742 done

3743 cat<<-EOF

3744 DELETE FROM blog_s

3745 WHERE rowid=(

3746 SELECT rowid

3747 FROM blog_s a

3748 WHERE key='team'

3749 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3750 AND NOT EXISTS (SELECT * FROM grp_mem_m

3751 WHERE key='team' AND val=a.val -- a.val=team

3752 AND gname = (SELECT val FROM blog_s b

3753 WHERE a.id=b.id AND key='owner')

3754 ));

3755 EOF

3756

3757 echo "END;"

3758 } | query

3759 )

3760 grp_reg_adm() {

3761 # $1=grp-rowid $2...=user-rowid

3762 grid=$1

3763 grp=`getgroupbyid "$1"`

3764 if [ -z "$grp" ]; then

3765 echo "無効なグループIDです" | html p; return

3766 fi

3767 if ! isgrpowner "$user" "$grp"; then

3768 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3769 fi

3770 shift

3771 for urid; do

3772 newadm=`query "select name from user where rowid=$urid;"`

3773 if [ -z "$newadm" ]; then

3774 echo "指定ユーザIDがおかしいようです。" | html p; return

3775 fi

3776 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3777 err ismember $newadm $grp

3778 if ismember $newadm "$grp"; then

3779 # OK, go ahead

3780 getgname="(select gname from grp where rowid=$grid)"

3781 query "replace into grp_adm values($getgname, '$newadm');"

3782 # confirm insertion

3783 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3784 if [ -n "`query \"$sql;\"`" ]; then

3785 echo "追加完了: $newadm" | html p

3786 else

3787 echo "追加失敗($1 $urid)" | html p

3788 fi

3789 fi

3790 showgroup $grid

3791 done

3792 }

3793 dt_colhack() {

3794 # FROM: <TD>xxx:::yyy</TD>

3795 # TO: <TD class="xxx">yyy</TD>

3796 sed -Ee 's,<TD>([^:<"]+)'$asdelim'([^<]*)(</TD>|$),<TD class="\1">\2\3,g'

3797 }

3798 dt_rowhack() {

3799 # From: <TR>

3800 # ....

3801 # <TD>rowclass=foo</TD>

3802 # </TR>

3803 # To: <TR class="foo">....<TD>foo</TD></TR>

3804 sed -e '

3805 /^<TR>/ {

3806 :loop

3807 s/\n//

3808 N

3809 /<\/TR>/ {

3810 s/\n//

3811 s,^<TR>$.*$<TD$[^>]*$>rowclass=$.*$$</TD></TR>$,<TR class="\3">\1<TD\2>\3\4,

3812 n

3813 }

3814 $q

3815 b loop

3816 }'

3817 }

3818 dumptable() {

3819 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3820 # textのフィールドだけ全てダンプにしたほうがいいか

3821 # $DT_VIEW sets link

3822 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3823 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3824 VIEW=${DT_VIEW-replyblog}

3825 if [ -n "$VIEW" ]; then

3826 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3827 fi

3828 sqlfile=$tmpd/dump.sql

3829 : > $sqlfile # ensure to be empty

3830 printf '.mode html\n.header 1\n' > $sqlfile

3831 # $DT_CHLD=ChildTable:BindColumn

3832 if [ -n "$DT_CHLD" ]; then

3833 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3834 cat<<-EOF >> $sqlfile

3835 -- presql

3836 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3837 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3838 EOF

3839 # Speed up counting of new articles

3840 cat<<-EOF >> $sqlfile

3841 -- presql2

3842 DROP TABLE IF EXISTS _counts;

3843 CREATE TEMPORARY TABLE _counts AS

3844 SELECT $_i, count($_i) cnt

3845 FROM $_t GROUP BY $_i;

3846 /* Prepare NEW count table */

3847 CREATE TEMPORARY TABLE _target AS

3848 SELECT b.rowid trowid, b.id

3849 FROM "$2" b JOIN "$2_s" s

3850 ON b.id=s.id AND s.key='owner'

3851 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3852

3853 DROP TABLE IF EXISTS _children;

3854 CREATE TEMPORARY TABLE _children AS

3855 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3856 FROM (SELECT t.trowid, t.id $_i, a.id

3857 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3858 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3859

3860 DROP TABLE IF EXISTS _news;

3861 DROP VIEW IF EXISTS _news;

3862

3863 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3864 -- INSERT INTO _news

3865 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3866 CREATE TEMPORARY VIEW _news AS

3867 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3868 FROM (SELECT DISTINCT id FROM _target)

3869 a LEFT JOIN

3870 (SELECT $_i, count(ctime) newcnt

3871 FROM _children x

3872 WHERE ctime > coalesce((SELECT time from myacclog

3873 WHERE tblrowid=x.trowid),

3874 '1970-01-01')

3875 GROUP BY $_i) b

3876 ON a.id=b.$_i;

3877 EOF

3878 # REMOVE next line until 2019/5/1

3879 cntall="(select count($_i) from $_t where $_i=a.id)"

3880 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3881 # REMOVE next assignment until 2019/5/1

3882 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3883 and id in (select id from $_t where $_i=a.id) \

3884 and val > coalesce((select time from myacclog where \

3885 tblrowid=a.rowid),\

3886 '1970-01-01'))"

3887 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3888 cnt="$cntnew as '新着', $cntall as '総数',"

3889 dt_class=" td2r td3r dumpblogs"

3890 fi

3891 # Construct join expression

3892 eav="" scols=""

3893 pk=`gettblpkey $2`

3894 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3895 substr=${substr:-%s}

3896 for col in ${3:-`gettbl_s_cols $2`}; do

3897 valvar=val

3898 fromtbl=b

3899 if gettblcols "$2" | grep -w "$col" >/dev/null 2>&1; then

3900 # If $col belongs to master table

3901 fromtbl=a; col=${col#a.}

3902 fi

3903 case $col in

3904 gecos) scols="$scols${scols:+, }${col#}"

3905 continue ;; # built-in column name

3906 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3907 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3908 case "$as" in

3909 *:*=*) cnd=${as#*:}

3910 h=${cnd%%=*} v=${cnd#*=}

3911 h=`sqlquotestr "$h"`

3912 v=`sqlquotestr "$v"`

3913 if [ x"$fromtbl" = x"b" ]; then

3914 valvar="CASE val WHEN $h THEN $v END"

3915 else

3916 valvar="$h"

3917 fi

3918 as=${as%%:*} ;;

3919 esac

3920 ;;

3921 *) as=${col} ;;

3922 esac

3923 ss=`printf "$substr" "$valvar"`

3924 if [ x"$fromtbl" = x"b" ]; then

3925 eav=$eav${eav:+,}" \"$as$asdelim\"||coalesce(max(case key when '$col' then $ss end), '') as $as"

3926 else

3927 eav=$eav${eav:+,}" \"$as$asdelim\"||$ss as $as"

3928 fi

3929 scols="$scols${scols:+, }${fromtbl}.$as"

3930 done

3931 #case author when '$user' then a.rowid else '---' end as ID,

3932 if [ -n "$DT_SQL" ]; then

3933 echo "$DT_SQL"

3934 else

3935 cat<<-EOF

3936 SELECT a.rowid as LINK, $cnt $scols

3937 FROM $2 a LEFT JOIN

3938 (SELECT $pk,$eav,

3939 max(CASE key

3940 WHEN 'owner'

3941 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3942 as gecos

3943 FROM ${2}_s c GROUP BY $pk)

3944 b ON a.$pk=b.$pk $4;

3945 EOF

3946 fi >> $sqlfile

3947 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3948 sqlog<<-EOF

3949 *** SQL-file: $sqlfile ***

3950 `cat $sqlfile`

3951 EOF

3952 printf '.mode list\n.header 0\n' >> $sqlfile

3953 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_colhack | dt_rowhack

3954 <div>

3955 <div class="dumptable">

3956 <table class="b$dt_class">

3957 `query ".read $sqlfile"`

3958 </table>

3959 </div>

3960 </div>

3961 EOF

3962 }

3963

3964 clean-orphaned() {

3965 # This shoud be done by foreign_key rules, but some db lack them

3966 query<<-EOF

3967 -- Find blogs that have no parent

3968 WITH orphanedblog AS (

3969 SELECT blog.id,val FROM blog JOIN blog_s bs

3970 ON blog.id=bs.id AND key='owner'

3971 WHERE val NOT IN (SELECT gname FROM grp)

3972 AND val NOT IN (SELECT name FROM user)

3973 ) -- Remove them

3974 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

3975

3976 -- Find articles that have no parent blog

3977 WITH orphanedarticle AS (

3978 SELECT id FROM article

3979 WHERE blogid NOT IN (SELECT id FROM blog)

3980 ) -- Remove them

3981 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

3982 EOF

3983 }

3984

3985 clearcachedir() (

3986 td=`getcachedir "$1"`

3987 err td=$td: ls- `ls $td`

3988 if [ -w "$td/image.1" ]; then

3989 err Removing td=$td

3990 rm -fr $td/ # Clear icon-image cache!

3991 fi

3992 )

3993

3994 par2table() (

3995 # copy current parameters of par into destination table

3996 # $1=definition-file

3997 # Using $user and $session

3998 # Return value:

3999 # 0: Stored successfully

4000 # 1: Insufficient fillings

4001 # 2: No permission to modify the record

4002 # 3: Invalid rowid

4003 # 4: SUCCESS to delete

4004 # 5: Stop deletion for lack of confirm check

4005 # 6: Password length too short

4006 # 7: Password mismatch

4007 # 8: Old password incorrect

4008 # 9: Duplicated post

4009 rowid=`getpar rowid`

4010 if [ ! -e $1 ]; then

4011 echo "テーブル定義ファイルが見付かりません" | html p

4012 exit 1

4013 fi

4014 tbl=${1%.def}

4015 tbl=${tbl##*/}

4016 if [ -n "$rowid" ]; then # Modify existing entry

4017 if [ x"$tbl" = x"user" ]; then

4018 rowowner=`query "select name from $tbl where rowid=$rowid;"`

4019 elif [ x"$tbl" = x"grp" ]; then

4020 sql="select gname from $tbl where rowid=$rowid;"

4021 ##err p2t:grp:q $sql

4022 isgrpowner "$user" "`query $sql`" && rowowner=$user

4023 elif [ x"$tbl" = x"blog" ]; then

4024 # Check if owner in blog_s

4025 blogowner=`getvalbyid blog owner "$rowid"`

4026 if isgrpowner "$user" "$blogowner"; then

4027 rowowner=$user

4028 origauthor=`getvalbyid blog author $rowid`

4029 if [ -n "$origauthor" -a x"$user" != x"$origauthor" ];

4030 then # Keep original author

4031 setpar author string "$origauthor" # if differs from $user

4032 fi # 2021-11-06 suggd.by Ruri

4033 else

4034 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4035 fi

4036 else

4037 # 2016-12-05 There's no owner column in $tbl (need confirmation)

4038 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4039 fi

4040 ### err rowowner=$rowowner

4041 if [ x"$user" != x"$rowowner" ]; then

4042 echo "他人のレコードはいじれないの" | html p

4043 return 2

4044 elif [ -z "$rowowner" ]; then

4045 echo "指定したレコードはないみたい" | html p

4046 return 3

4047 fi

4048 rm=`getpar rm` cfm=`getpar confirm`

4049 # Editing existent entry

4050 if [ x"$rm" = x"yes" ]; then

4051 if [ x"$rm$cfm" = x"yesyes" ]; then

4052 query "delete from $tbl where rowid=$rowid;"

4053 clearcachedir "$tbl/$rowid"

4054 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

4055 clean-orphaned

4056 fi

4057 return 4

4058 else

4059 echo "消去確認のチェックがないので消さなかったの..." | html p

4060 return 5

4061 fi

4062 fi

4063 fi

4064

4065 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

4066 if [ -n "$rowid" ]; then

4067 # Update of existing record

4068 for col in `gettblcols $tbl`; do

4069 val=`getparquote $col`

4070 [ -z "$val" ] && continue

4071 ## err query "update $tbl set $col=$val where rowid=$rowid"

4072 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

4073 sql="update $tbl set $col=$val where rowid=$rowid;"

4074 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

4075 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

4076 ## User name cannot be changed with interface provided with this

4077 ## script. But we offer the trigger to change owner user

4078 ## of blog_s table.

4079 #err "select quote($col) from $tbl where rowid=$rowid;"

4080 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

4081 cat<<-EOF | query

4082 -- Here we cannot use BEGIN-COMMIT because groupupdate()

4083 -- should use EXCLUSIVE transaction outside of this.

4084 SAVEPOINT par2table;

4085 $sql

4086 update blog_s set val=$val

4087 where key='owner' and val=$old;

4088 RELEASE SAVEPOINT par2table;

4089 EOF

4090 ## XX: DIRTY Hack Ends here

4091 ## We should keep blog's owner as a single column which has

4092 ## foreign key constraint with primary key of grp/user.

4093 else

4094 query "$sql"

4095 fi

4096 done

4097 # Then, set up $pval for further insertion of tbl_s and tbl_m

4098 for col in `gettblpkey $tbl`; do

4099 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

4100 pval="$pval${pval:+, }\"$val\""

4101 done

4102 else

4103 # New entry

4104 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

4105 # Avoid empty repost of article.

4106 if [ x"$tbl" = x"article" ]; then

4107 # If rowid is empty and ID exists in article-table, that is REPOST!

4108 aid=`getpar id`

4109 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

4110 if [ -n "$xaid" ]; then

4111 # REPOST of article

4112 html p <<-EOF

4113 書き込み直後のリロードなので上書きを回避します。

4114 最新記事は末尾の「再読み込み」ボタンから見てください。

4115 EOF

4116 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

4117 return 9 # STOP Duplicated posting

4118 fi

4119 fi

4120 # Generate values() for primary keys

4121 for col in `gettblpkey $tbl`; do

4122 # Genuine primary keys for _m and _s

4123 val=`getvalquote $tbl $col`

4124 [ -z "$val" ] && continue

4125 pval="$pval${pval:+, }$val"

4126 done

4127 ##err pval=$pval

4128 for col in `gettblfkey $tbl`; do

4129 # args for values() to insertion into master table

4130 val=`getvalquote $tbl $col`

4131 [ -z "$val" ] && continue

4132 formaster=$formaster"${formaster:+, }$val"

4133 done

4134 formaster="$pval${formaster:+, }$formaster"

4135 ## err formaster=$formaster

4136 if [ -z "$formaster" ]; then

4137 echo "項目を全て埋めてください" | html pre

4138 return 1

4139 fi

4140 ## err "replace into $tbl values($formaster);"

4141 query "replace into $tbl values($formaster);"

4142 ## Insertion to master table, done

4143 fi

4144

4145 transaction=$tmpd/sqlfile.sql; touch $transaction

4146 for kt in s m; do

4147 tb2=${tbl}_$kt

4148 for col in `gettbl_${kt}_cols $tbl`; do

4149 ptype=`getpartype $col "limit 1"`

4150

4151 # First, check update of existing entries in _m

4152 if [ $kt = m ]; then

4153 # sessID|address.1.22|string|Somewhere-x.y.z

4154 sql=""

4155 ##err dots from query "select var from par where var like '$col.%';"

4156 for v in `query "select var from par where var like '$col.%' AND sessid='$session';"`; do

4157 # v=address.1.22

4158 st_rowid=${v##*.}

4159 origcol=${v%%.*} # original column derived from

4160 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

4161 ##case `getpartype $v` in

4162 ## err CASE `gettbl_coltype $tbl/$origcol` in

4163 ## err edit flag = `getpar action.$v`

4164 case `getpar action.$v` in

4165 rm)

4166 if [ x`getpar confirm.$v` = x"yes" ]; then

4167 newsql="delete from $tb2"

4168 else

4169 echo "削除確認未チェック" | html p

4170 fi ;;

4171 edit)

4172 case `gettbl_coltype $tbl/$origcol` in

4173 image|document|binary)

4174 file=$tmpd/`getparfilename $v`

4175 if [ ! -s "$file" ]; then # Maybe stupid REPOST

4176 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

4177 continue

4178 fi

4179 ## err type=file=$file

4180 [ -z "$file" ] && continue

4181 bn=`sqlquotestr "${file##*/}"`

4182 bin="X'"$(hexize "$file")"'"

4183 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

4184 type=\"file:$ct\"

4185 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

4186 cachedir=`getcachedir "$tbl/$rowid"`

4187 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

4188 rm -rf $cachedir

4189 ;;

4190 *)

4191 newsql="update $tb2 set val=(select val from par where var \

4192 like '$col.%.$st_rowid' AND sessid='$session')"

4193 ;;

4194 esac

4195 ;;

4196 mv)

4197 # regularize filename and strip directory part

4198 newname=`getpar mv.$v|tr -d '":;#<>?^%$!'|tr -d "'"|tr ' ' _`

4199 newname=`basename $newname`

4200 oldname=`query "SELECT val FROM $tb2 WHERE rowid=$st_rowid;"`

4201 oldext=`expr "$oldname" : '.*\.$.*$'`

4202 newext=`expr "$newname" : '.*\.$.*$'`

4203 err "p2t(mv): oldname=$oldname $oldext -> newname($v)=$newname $newext"

4204 if [ -n "$newname" -a x"$oldext" = x"$newext" ];

4205 then

4206 newsql="UPDATE $tb2 SET val='$newname'"

4207 else

4208 html p<<-EOF

4209 $newname は取り扱えないファイル名です。

4210 空白を含まない名前にして下さい。

4211 拡張子の変更もできません。

4212 EOF

4213 continue

4214 fi

4215 ;;

4216 *) # maybe "keep", do not modify value

4217 continue

4218 ;;

4219 esac

4220 # err newsql=$newsql

4221 sql=$sql$nl"$newsql where rowid=$st_rowid;"

4222 clearcachedir "$tbl/$rowid"

4223 done

4224

4225 if [ x"$bin" = x"NULL" ]; then

4226 ## err repl:normal sql=`echo $sql`

4227 if [ -n "$transaction" ]; then

4228 cat<<-EOF >> $transaction

4229 $sql

4230 DELETE FROM $tb2 WHERE type='string' AND val='';

4231 EOF

4232 else

4233 query "$sql

4234 delete from $tb2 where type='string' and val='';"

4235 ## err repl:normal done

4236 fi

4237 else

4238 # Binary update line is TOO LONG to pipelining

4239 sqlfile="$tmpd/sqlf.$$"

4240 if [ -n "$transaction" ]; then

4241 printf '%s' "$sql" >> $transaction

4242 else

4243 printf '%s' "$sql" > $sqlfile

4244 query ".read $sqlfile"

4245 fi

4246 fi

4247 # Rest of kt==m: set multiple mode

4248 nr=`getparcount $col`

4249 else

4250 nr=1 # for kt==s, number of records is 1

4251 fi

4252

4253 i=0

4254 while [ $i -lt $nr ]; do

4255 limit="limit 1 offset $i"

4256 i=$((i+1)) # increase beforehand against continue

4257 val=`getvalquote $tbl $col "$limit"`

4258 ##XXX [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4259 [ -z "$val" -o x"$val" = x'""' ] && continue

4260 ## err $col=$val

4261 bin=NULL

4262 ## err partype$col=`getpartype $col "$limit"`

4263 ptype=`getpartype $col "$limit"` # partype should be obtained each time

4264 case $ptype in

4265 file) file=$tmpd/`getparfilename $col "$limit"`

4266 ## err parfile-$col=$file

4267 [ -z "$file" ] && continue

4268 bin="X'"$(hexize "$file")"'"

4269 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4270 type=\"file:$ct\" ;;

4271 "*"*) continue ;; # foreign table

4272 *) type=\"string\" ;;

4273 esac

4274 case `gettbl_coltype $tbl/$col` in

4275 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx]|[Tt][Ee][Xx][Tt])

4276 test x"$val" = x"NULL" && val="''"

4277 ;;

4278 password) # special care for password

4279 # name={password,pswd1,pswd2}

4280 p1=`getpar pswd1 "$limit"`

4281 if [ -z "$p1" ]; then

4282 continue # SKIP password setting, if p1 is empty

4283 else

4284 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4285 ## err pswd=$pswd

4286 if pwcheck "$pswd"; then

4287 if [ x"$p1" = x"$p2" ]; then

4288 case "$p1" in

4289 ??????????*) ;;

4290 *) echo "パスワードは10字以上にしてください。" | html p

4291 return 6;;

4292 esac

4293 val="\"`echo $p1|mypwhash`\""

4294 else

4295 echo "2つの新パスワード不一致" | html p

4296 return 7

4297 fi

4298 else

4299 echo "旧パスワード違います" | html p

4300 return 8

4301 fi

4302 fi

4303 ;;

4304 esac

4305 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4306 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4307 if [ x"$val" = x"NULL" -a x"$bin" = x"NULL" ]; then

4308 continue # Do not insert completely NULL record 2021-11-08

4309 fi

4310 sql="REPLACE into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4311 if [ x"$bin" = x"NULL" ]; then

4312 ## err Normal-query: `echo $sql`

4313 if [ -n "$transaction" ]; then

4314 printf '%s' "$sql" >> $transaction

4315 else

4316 query "$sql"

4317 fi

4318 else

4319 sqlfile="$tmpd/query.$$"

4320 ## err sqlfile=`ls -lF $sqlfile`

4321 if [ -n "$transaction" ]; then

4322 printf '%s' "$sql" >> $transaction

4323 else

4324 printf '%s' "$sql" > $sqlfile

4325 query ".read $sqlfile"

4326 fi

4327 fi

4328 ## err p2t done

4329 done

4330 done

4331 done

4332 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4333 -- We cannot use transaction here, because groupupdate may use it.

4334 SAVEPOINT pa2table_insert;

4335 .read $transaction

4336 RELEASE SAVEPOINT pa2table_insert;

4337 EOF

4338 rc=$?

4339 [ $rc -eq 0 -a x"$tbl" = x"user" ] && flag_profupdate

4340 ## err "Table:$tbl update done "

4341 return $rc

4342 )

4343 genform() {

4344 # $1 = form definition file

4345 # $2, $3 (optional)= table name and ROWID

4346 # If $GF_VIEWONLY set and nonNull, output values without form

4347 # If $GF_ARGS set, use it as content-strings in the form

4348 # If $GF_OWNER set, use it as value of name="owner"

4349 # If $GF_STAGE set, use it as value of name="stage"

4350 forms="" hiddens="" rowid=$3

4351 if [ ! -e "$1" ]; then

4352 echo "そのようなデータベースはないようです($2)。" | html p

4353 return

4354 elif [ -n "$2" ]; then

4355 rec=`query "select * from $2 where rowid='$rowid';"`

4356 if [ -z "$rec" ]; then

4357 pk=`gettblpkey $2`

4358 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4359 rec=`query "select rowid from $2 where $pk='$rowid';"`

4360 rowid=$rec

4361 rec=$3

4362 fi

4363 if [ -z "$rec" ]; then

4364 echo "そんなレコードはないみたいね..." | html p

4365 return

4366 fi

4367 fi

4368 if [ -z "$GF_VIEWONLY" ]; then

4369 rm='<input id="rm" name="rm" type="checkbox"

4370 value="yes"><label for="rm">このエントリの削除</label>

4371 <span>ほんとうに消しますよ(確認)!

4372 <input name="confirm" type=checkbox value="yes">はい</span>'

4373 fi

4374 # Image Cache dir

4375 ## err genform: getcache=$2/$rowid

4376 td=`getcachedir "$2/$rowid"`

4377 while IFS=: read -r prompt name keytype type args; do

4378 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4379 sp="${args:+ }"

4380 form="" val=""

4381 if [ -n "$rowid" ]; then

4382 # err genform2a: Seeking for "$2.$name, type=$type"

4383 rawval=`getvalbyid $2 $name $rowid $td`

4384 val=`printf '%s\n' "$rawval"|htmlescape`

4385 ## err genform3a: getvalbyid $2 $name $rowid $td

4386 ## err genform3b: val="[$val]" type="$type"

4387 fi

4388 if [ -n "$GF_VIEWONLY" ]; then

4389 is_hidden "$2" "$name" && continue

4390 fi

4391 case "$type" in

4392 text*)

4393 cgiform=cgi_multi_$type

4394 if [ -s $td/$name.count -a -n "$val" ]; then

4395 form=`$cgiform $name $td`

4396 val=$(printf '%s\n' "$val"|

4397 while read fn; do

4398 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4399 </td></tr>$nl"

4400 done)

4401 val="<table>$nl$val$nl</table>"

4402 else

4403 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4404

4405 form=`cgi_$type $name "$rawval" "$args"`

4406 fi

4407 ;;

4408 [Rr][Aa][Dd][Ii][Oo])

4409 fh="<label><input type=\"radio\" name=\"$name\""

4410 form="`echo $args|sed -e \

4411 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4412 ;;

4413 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4414 checked=${val:+ checked}

4415 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\"$checked>${args%=*}</label>"

4416 ;;

4417 [Ss][Ee][Ll][Ee][Cc][Tt])

4418 fh="<select name=\"$name\">$nl"

4419 form=$(for l in $args; do

4420 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4421 done)

4422 if [ -n "$val" ]; then

4423 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4424 fi

4425 form="$fh$form</select>"

4426 ;;

4427 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4428 if [ -s $td/$name.count ]; then

4429 form=`cgi_multi_file $name $td "$args"`

4430 if [ -n "$val" ]; then

4431 hrfb="$myname?showattc+$2_m"

4432 val=$(echo "$rawval" \

4433 | while read fn; do

4434 data=`percenthex "$td/$fn"`

4435 #ct=`cat $td/$fn.content-type`

4436 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4437 ri=`cat "$td/$fn.rowid"`

4438 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4439 #imgsrc="<img src=\"data:$ct,$data\">"

4440 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4441 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4442 done)

4443 fi

4444 else

4445 form="<input type=\"file\" name=\"$name\" $args>"

4446 if [ -n "$val" ]; then

4447 imgs=$(echo "$rawval"\

4448 |while read fn;do

4449 data=`percenthex "$td/$fn"`

4450 echo "<img src=\"data:image/png,$data\">$fn<br>"

4451 done)

4452 form=$form"<br>$imgs"

4453 val=$imgs # 2015-06-15

4454 else

4455 form="<input type=\"file\" name=\"$name\" $args>"

4456 fi

4457 fi

4458 ;;

4459 [Hh][Ii][Dd][Dd][Ee][Nn])

4460 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4461 args="value=\"$GF_STAGE\""

4462 fi

4463 form="<input type=\"hidden\" name=\"$name\" $args>"

4464 prompt='' # Remove prompt

4465 ;;

4466 [Aa][Uu][Tt][Hh][Oo][Rr])

4467 [ -n "$GF_VIEWONLY" ] && continue

4468 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4469 prompt="" ;;

4470 [Oo][Ww][Nn][Ee][Rr])

4471 [ -n "$GF_VIEWONLY" ] && continue

4472 val=${GF_OWNER:-$val}

4473 val=${val:-$user}

4474 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4475 prompt="" ;;

4476 [Uu][Ss][Ee][Rr])

4477 # XXX: is null $user ok?

4478 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4479 [ -n "$GF_VIEWONLY" ] && continue

4480 form="$user"

4481 ;;

4482 [Pp]assword)

4483 [ -n "$GF_VIEWONLY" ] && continue

4484 form="`cgi_passwd`"

4485 val=""

4486 ;;

4487 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4488 [ -n "$GF_VIEWONLY" ] && continue

4489 if [ -z "$rowid" ]; then

4490 val=`genserial`

4491 fi

4492 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4493 prompt="" ;;

4494 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4495 prompt=""

4496 ;;

4497 parent|path|blog*)

4498 prompt=""

4499 ;;

4500 "*"*)

4501 tail=$tail"``"

4502 continue ;;

4503 esac

4504 if [ -n "$prompt" ]; then

4505 if [ -n "${GF_VIEWONLY}" ]; then

4506 form=$val

4507 else

4508 :

4509 fi

4510 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4511 else

4512 hiddens=$hiddens$nl"$form"

4513 fi

4514 done < $1

4515 # enctype="multipart/form-data"

4516 cat<<-EOF

4517 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4518 EOF

4519 test -n "$rowid" && printf '%s\n' "$rm" # Workaround for utf8 buggy NetBSD sh

4520 cat<<EOF

4521 <table class="b $2">

4522 $forms

4523 </table>$hiddens

4524 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4525 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4526 EOF

4527 if [ -z $GF_VIEWONLY ]; then

4528 cat<<EOF

4529 <input type="submit" name="sub" value="OK">

4530 <input type="reset" name="res" value="Reset">

4531 EOF

4532 fi

4533 cat<<EOF

4534 $GF_ARGS</form>

4535 $tail

4536 EOF

4537 }

4538 edittable() {

4539 # $1=form-def $2=table $3 rowid

4540 genform "$@"

4541 }

4542 viewtable() {

4543 GF_VIEWONLY=1 genform "$@"

4544 }

4545 showattc() {

4546 # $1=table_m $2=rowid &optional $3=RawFlag

4547 ## err \$1=$1 \$2=$2 \$3=$3

4548 if ! isfilereadable $user $1 $2; then

4549 contenttype; echo

4550 echo "このファイルは管理者のみしか見られません" | html p

4551 putfooter; exit

4552 fi

4553 idir=`umask 002; mktempd` || exit 1

4554 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4555 bin=$idir/$myname-$$.bin

4556 sql="select quote(bin) from $1 where rowid='$2';"

4557 ## err showattc: sql: $sql

4558 sq $db "$sql" | unhexize > $bin

4559 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4560 type=${tv%//*} fn=${tv#*//}

4561 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4562 ct=${type#file:}

4563 case $ct in # all text/* changed to text/plain

4564 text/*|application/csv|application/json)

4565 charset=`nkf -g $bin|cut -d' ' -f1`

4566 case $charset in

4567 ASCII*) charset="" ;;

4568 esac

4569 if [ -z "$3" ]; then

4570 ct="text/html${charset:+; charset=$charset}"

4571 link="?showattc+$1+$2+raw"

4572 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4573 | sed 's,^,<span></span>,' \

4574 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4575 -D_LINK_="$link" \

4576 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4577 exit $?

4578 fi

4579 ct="text/plain${charset:+; charset=$charset}"

4580 ;;

4581 esac

4582 contenttype "$ct"

4583 echo "Content-Disposition: filename=\"$fn\""

4584 echo "Content-Length: " `cat $bin | wc -c`; echo

4585 #echo "Content-Type: " ${type#file:}; echo

4586 cat $bin

4587 }

4588 #

4589 # Some default stupid handler on CGI values

4590 #

4591 default_storedb() {

4592 # ARG: $1=table-def-file

4593 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4594 tbl=`basename $1`

4595 tbl=${tbl%.def}

4596 cols="`grep :text $1|cut -d: -f2`"

4597 col=`echo "$cols"|head -1`

4598 vcol=`getpar $col`

4599 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4600 if [ -n "$vcol" ]; then

4601 par2table $1

4602 else

4603 return 2 # No insertion occurred

4604 fi

4605 }

4606

4607 default_view() { # $1=def-file

4608 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4609 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4610 default_storedb "$@"

4611 query "select rowid from $tbl order by rowid desc;" \

4612 | while read rowid; do

4613 viewtable $1 $tbl $rowid

4614 done | _m4 -D_TITLE_="$tbl" \

4615 -D_FORM_="`genform $1`" \

4616 -D_DUMPTABLE_="syscmd(cat)" \

4617 $layout/html.m4.html $layout/form+dump.m4.html

4618 }

4619 default_viewtext() { # $1=def-file

4620 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4621 default_storedb "$@"

4622 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4623 | _m4 -D_TITLE_="$tbl" \

4624 -D_FORM_="`genform $1`" \

4625 -D_DUMPTABLE_="syscmd(cat)" \

4626 $layout/html.m4.html $layout/form+dump.m4.html

4627 }

4628 default_smail() {

4629 default_storedb "$@"

4630 if [ $? -eq 2 ]; then

4631 _m4 -D_TITLE_="入力" \

4632 -D_FORM_="`genform $1`" \

4633 -D_DUMPTABLE_="" \

4634 $layout/html.m4.html $layout/form+dump.m4.html

4635 return

4636 fi

4637 cond=""

4638 for pk in `gettblpkey $tbl`; do

4639 pv=$(sqlquote "$(getpar $pk)")

4640 cond="$cond${cond:+ and }$pk=$pv"

4641 done

4642 sql="select rowid from $tbl where $cond;"

4643 rowid=`query "$sql"`

4644 ## err smail1 - "$sql" "-> rowid=$rowid"

4645

4646 while IFS=: read prompt name keytype type args; do # Read from $1

4647 val=`getpar $name`

4648 if [ -n "$val" ]; then

4649 text="$text

4650 $prompt

4651 $name=$val

4652 ---------------------------------------------------------"

4653 fi

4654 case "$type" in

4655 image|document|file)

4656 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4657 fns=$(echo "$fn"|while read fn; do

4658 err mv $tmpd/$fn.orig $tmpd/$fn

4659 mv $tmpd/$fn.orig $tmpd/$fn

4660 rm $tmpd/$fn.rowid # Remove cache flag

4661 ## err "`ls $tmpd/$fn`"

4662 echo $fn

4663 done)

4664 files="$files $fns"

4665 ;;

4666 esac

4667 done < $1

4668 ## err FILES=$files "`ls -lF $tmpd`"

4669 subj="from ${REMOTE_ADDR}"

4670 (echo "$url"

4671 echo "への書き込みがありました。"

4672 echo "------"

4673 echo "$text"

4674 ) | (cd $tmpd &&

4675 err LS="`ls -lF`" &&

4676 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4677 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4678 echo "以下の内容で送信しました。" | html p

4679 viewtable $1 $tbl \

4680 `query "select rowid from $tbl order by rowid desc limit 1;"`

4681 echo "戻る" | html a "href=\"?\""

4682 }