s4: 8682149ed229 s4-funcs.sh

s4

view s4-funcs.sh @ 1041:8682149ed229

Encolose column name of widechar with double-quotes.

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sun, 21 Apr 2024 12:16:44 +0900
parents	78e904f9be34
children	99ba706d5fdb

line source

1 #!/bin/sh

2 # Here's global variable table. Do not use this names.

3 # $HGid$

5 [ -f s4-config.sh ] && . ./s4-config.sh

7 test -n "$HTTP_HOST" && isCGI=true || isCGI=false

8 if $isCGI; then

9 case "$SCRIPT_NAME" in

10 *-world-*)

11 S4WORLD=${SCRIPT_NAME#*world-}

12 S4WORLD=${S4WORLD%.*}

13 echo S4WORLD=$S4WORLD >&2

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

15 ;;

16 *)

17 worldconf=s4-config.sh

18 ;;

19 esac

20 echo worldconf=$worldconf >&2

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

22 echo DB=$DB >&2

23 fi

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

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

26 cgiext=${CGIEXT:-.cgi}

27 myargs="$@"

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

29 tmpdir=${TMPDIR:-tmp}

30 dbdir=${DBDIR:-db}

31 logdir=${LOGDIR:-tmp}

32 tmpfiles=""

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

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

35 defaultdb=$dbdir/cgi.sq3

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

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

38 userupdateflag=$dbdir/userupdate

39 sesstb=tmp.sess

40 workdb=$dbdir/tmpdata.sq3

41 listentlimit=${LISTENTLIMIT:-30}

42 listartlimit=${LISTARTLIMIT:-50}

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

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

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

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

47 templ=${TEMPL:-templ}

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

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

50 imgdir=${IMGDIR:-img}

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

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

53 msgdir=$templ/msg

54 timeout="+2 days"

55 memoplimitdays="7"

56 dumpcollen=22

57 #thumbxy=120x120

58 thumbxy=96x96

59 iconxy_S=80x80

60 iconxy_M=400x400

61 maximagexy=1600x1600

62 ### maximagexy=400x400

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

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

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

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

68 file_accept_help="

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

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

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

72 "

73 file_warn="$file_accept_help

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

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

76 blogreadflagrowid=0

77 blogcutoffflagrowid=-1

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

79 whatsnewdays=${WHATS_NEW_DAYS:-14}

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

81 session=$main_session

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

83 tex: {tags: "ams",

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

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

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

88 tconfs=""

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

90 conftbl=_tblconf

91 nl="

92 "

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

94 iconcachekey="profimgcache_S"

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

96 sqlite3 --help 2>&1 | grep -q -- '-json' && usejson=true || usejson=false

98 # Start debug logging

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

100 exec 3>> $logdir/debug.out

101 err() {

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

103 # Avoid backslash escape sequences

104 cat<<EOF 1>&3

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

106 EOF

107 }

108 case "$HTTP_USER_AGENT" in

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

110 *) touchpanel="" ;;

111 esac

112

113 # If S4MASTERDB is set, behave in another world

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

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

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

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

118 # Variables set in s4-world.sh

119 # $S4WORLDS, $S4WROLDNAME, $S4WORLDGRPS

120 # Files created in s4-world.sh

121 # $worldlistfile, $worldoptionfile, $worldnamefile, $worldgrpfile

122 fi

123

124

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

126

127 : <<EOF

128

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

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

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

132 レポート提出管理

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

134

135 [2016]

136 7/12 根本への反省

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

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

139

140 [以下2015]

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

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

143 Web

144 締切設定

145

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

147 ○自分の提出物リスト

148

149 7/19 ○設置

150 ○一斉送信

151 ○getparfilename の tmpd の扱い

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

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

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

155 ○添付ファイル回収

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

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

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

159

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

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

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

163 ○レポート提出モード

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

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

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

167

168

169 7/9 ○管理者の追加

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

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

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

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

174

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

176

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

178 ○グループ検索

179

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

181

182

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

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

185

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

187 ○6/1 par2tableを triplex 対応に

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

189 →とすると一気に

190

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

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

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

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

195

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

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

198 ## 1. passwd

199 ## 2. grp

200 ## 3. grp_mem

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

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

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

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

205

206 ## ● listの定義:

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

208 ## ● hashの定義:

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

210

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

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

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

214 ## csv2sq3 で .csv.sq3 の Makefile

215

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

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

218 ## type := root | comment

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

220 ## unique(id, filename)

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

222

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

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

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

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

227 ## できたー!

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

229 ↓

230 ↓以下に変更

231 with recursive allmem as

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

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

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

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

236

237

238 with recursive allmem as

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

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

241 where allmem.val=grp_m.gname)

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

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

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

245

246

247 ## triggerもできた。

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

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

250

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

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

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

254 ## グループ管理

255 ## 個人情報管理

256 ##

257

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

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

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

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

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

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

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

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

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

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

268

269 ## ログテーブル

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

271

272

273

274 ■表設計

275 * 3つの表に分散管理

276 id格納表 + hash表 + list表

277 * *_s *_m

278

279

280

281 user, user_map, user_col

282

283 ■抽象エントリタイプ

284 * user

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

286 * group

287 権限判定に利用

288 * serial

289 自動idとして機能

290 * password

291 入力 type=passwordで入力

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

293 * session

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

295 * text

296 入力 type=text

297 * textarea

298 入力 textarea

299 * image|document

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

301 * owner

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

303 * gowner

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

305 * timestamp

306 datetime()

307 * parent

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

309 * path

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

311

312 格納タイプ

313 * list

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

315 * hash

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

317

318 オブジェクトタイプ

319 * entry

320 id, title, owner

321 * textpart

322 id, parentID, text

323 * binarypart

324 id, parentID, contenttype, filename, content

325 * content

326 hash(textpart), list(binarypart)

327 * topic

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

329 * reply

330 id, parentID, content

331 * blog

332 list(entry)

333 blog = [topic, list(reply)]

334

335

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

337 "text" => "hogehoge ..",

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

339 "author" => "taro",

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

341 "parent" => "/",

342 "path" => "/1",

343 "text" => "blah, blah, ....",

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

345 {"serial" => 2,

346 "author" => "hanako",

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

348 "parent" => "/",

349 "path" => "/2",

350 "text" => "blah, blah, ....",

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

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

353

354

355 user:=

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

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

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

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

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

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

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

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

364

365 変換表

366 /user/email=m

367

368 blog:=

369 シリアル:id:p:serial

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

371 所有者:owner:s:owner:

372 時刻:ctime:s:stamp:

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

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

375

376 article:=

377 シリアル:id:p:serial

378 筆者:author:s:owner

379 時刻:ctime:s:stamp:

380 参照元:parent:s:parent:

381 パス:path:s:path:

382 本文:text:s:textarea:

383 画像:image:m:image:

384

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

386

387

388 EOF

389

390 logstart() {

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

392 }

393 logend() {

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

395 }

396 sqlog() {

397 logstart

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

399 cat >> $querylog

400 else

401 echo "$*" >> $querylog

402 fi

403 logend

404 }

405 sq() {

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

407 logstart

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

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

410 else

411 echo "$@" >> $querylog

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

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

414 fi

415 logend

416 }

417 dbsetup() {

418 pipedir=$tmpdir/pipedir

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

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

421 suf=`date +%s`

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

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

424 mkfifo $sqi $sqo

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

426 sq $db < $sqi &

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

428 if $isCGI; then

429 exec 2>> $logdir/error.out

430 chmod o-r $logdir/error.out

431 fi

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

433 chmod o-r $logdir/debug.out

434 rm $sqi

435 # Attach supplemental DB

436 cat >&5 <<-EOF

437 .output /dev/null

438 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

442 .output stdout

443 EOF

444 }

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

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

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

448 fi

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

450 }

451 cleanup() {

452 trap '' INT HUP EXIT TERM PIPE

453 echo .quit >&5

454 kill $sq3pid

455 kill $sq3pid

456 rm -f $sqo $sqi

457 rm -rf $tmpfiles

458 cleanup2

459 }

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

461 # simple redirection for the sake of speed.

462 query() {

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

464 echo ".output $sqo" >&5

465 logstart

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

467 tee -a $querylog

468 else

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

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

471 fi >&5

472 echo ".output stdout" >&5

473 cat $sqo

474 rc=$?

475 logend

476 return $rc

477 }

478 _m4() {

479 #S4NAME=f,f,f

480 if m4 --version | grep -q GNU; then

481 if type om4; then # https://github.com/ibara/m4

482 m4() {

483 om4 "$@"

484 }

485 fi

486 fi >/dev/null 2>&1

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

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

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

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

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

492 }

493 if ! type gdate && date --version | grep -q GNU; then

494 gdate() date "$@"

495 fi >/dev/null 2>&1

496 if ! type md5 && type md5sum && md5sum --version | grep -q GNU; then

497 md5() {

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

499 md5sum | cut -d' ' -f 1

500 else

501 for f; do

502 printf "MD5 (%s) = %s\n" "$f" "$(md5 < $f)"

503 done

504 fi

505 }

506 fi >/dev/null 2>&1

507 ismember() {

508 # $1=user, $2=group

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

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

511 }

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

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

514 }

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

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

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

518 }

519 isgrpowner() (

520 # $1=user, $2=group

521 gn=`sqlquote "$2"`

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

523 err isgrpowner: $sql

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

525 )

526 isgrpownerbygid() (

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

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

529 err isgrpownerbygid: $sql

530 test -n "`query $sql`"

531 )

532 getgroupadminmails() {

533 # $1=group

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

535 email4group "$1" "$i" ;

536 done

537 }

538 getgroupadmins() { # $1=group

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

540 qgrp=`sqlquote "$1"`

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

542 }

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

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

545 getvalbyid grp $2 \

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

547 }

548 getgroupbyid() {

549 # $1=id|gname

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

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

552 # err ggbyid: `echo $sql`

553 query $sql

554 }

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

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

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

558

559 # Return true when anonymous mode

560 [ "$anonymousmode" ] && return 0

561 # case `getvalbyid blog mode $2` in

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

563 # *closed*)

564 # owner=`getvalbyid blog owner $2`

565 # if isgrp $owner; then

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

567 # elif isuser $owner; then

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

569 # fi

570 # esac

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

572 case "$2" in

573 grp_*)

574 sql="SELECT 'owner'

575 FROM grp_adm

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

577 AND

578 user = '$user';"

579 ;;

580 *)

581 sql="with getblog as (

582 select key,val from blog_s where id=(

583 select blogid from article where id in

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

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

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

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

588 isgrpadm as (select user from grp_adm where

589 gname=(select val from getowner) and

590 user='$1'),

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

592 select case

593 when (select author from article where

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

595 then 'author'

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

597 then 'open'

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

599 then CASE

600 WHEN (SELECT val FROM isgrp) IS NULL

601 THEN

602 CASE WHEN (SELECT val from getowner)

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

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

605 ELSE ''

606 END

607 WHEN (select user from isgrpadm) IS NOT NULL

608 THEN 'i-am-admin'

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

610 END

611 when (select val from getmode) is null

612 then 'open'

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

614 then (SELECT user FROM isgrpadm)

615 when (select author from article where

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

617 then 'user+author'

618 else '' end;"

619 ;;

620 esac

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

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

623 # result=`query "$sql"`

624 # err FileAccessibility=$result

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

626 }

627 linkhome() {

628 # $1=UserOrGroupRowid

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

630 if isuser $1; then

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

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

633 name=`gecos $1|htmlescape`

634 else

635 _grid=`numericalize "$1"`

636 echo -n "grp+$1"

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

638 fi

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

640 }

641 hreflink() {

642 # s4 specific notation:

643 # ^href=URL

644 # ^iframe=URL

645 # ^video=URL

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

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

648 # OSM umap Wikistyle Notation:

649 # [[URL]] - Simle Link

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

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

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

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

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

655 # Other Style

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

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

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

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

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

661 # SPC+SPC+$ - <br>

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

663 checkboxON="${cb} checked>"

664 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

686 -e 's,~~$[^~][^~]*$~~,<s>\1</s>,g' \

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

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

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

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

691

692 }

693 minitbl() {

694 sed -n '

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

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

697 s,|\* *>>>>>>|$[^|]*$ *,<th colspan="7">\1</th>,g;

698 s,|\* *>>>>>|$[^|]*$ *,<th colspan="6">\1</th>,g;

699 s,|\* *>>>>|$[^|]*$ *,<th colspan="5">\1</th>,g;

700 s,|\* *>>>|$[^|]*$ *,<th colspan="4">\1</th>,g;

701 s,|\* *>>|$[^|]*$ *,<th colspan="3">\1</th>,g;

702 s,|\* *>|$[^|]*$ *,<th colspan="2">\1</th>,g;

703 s,|\* *^^^^^^$[^|]*$ *,<th rowspan="7">\1</th>,g;

704 s,|\* *^^^^^$[^|]*$ *,<th rowspan="6">\1</th>,g;

705 s,|\* *^^^^$[^|]*$ *,<th rowspan="5">\1</th>,g;

706 s,|\* *^^^$[^|]*$ *,<th rowspan="4">\1</th>,g;

707 s,|\* *^^$[^|]*$ *,<th rowspan="3">\1</th>,g;

708 s,|\* *^$[^|]*$ *,<th rowspan="2">\1</th>,g;

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

710 s,| *>>>>>>|$[^|]*$ *,<td colspan="7">\1</td>,g;

711 s,| *>>>>>|$[^|]*$ *,<td colspan="6">\1</td>,g;

712 s,| *>>>>|$[^|]*$ *,<td colspan="5">\1</td>,g;

713 s,| *>>>|$[^|]*$ *,<td colspan="4">\1</td>,g;

714 s,| *>>|$[^|]*$ *,<td colspan="3">\1</td>,g;

715 s,| *>|$[^|]*$ *,<td colspan="2">\1</td>,g;

716 s,| *^^^^^^$[^|]*$ *,<td rowspan="7">\1</td>,g;

717 s,| *^^^^^$[^|]*$ *,<td rowspan="6">\1</td>,g;

718 s,| *^^^^$[^|]*$ *,<td rowspan="5">\1</td>,g;

719 s,| *^^^$[^|]*$ *,<td rowspan="4">\1</td>,g;

720 s,| *^^$[^|]*$ *,<td rowspan="3">\1</td>,g;

721 s,| *^$[^|]*$ *,<td rowspan="2">\1</td>,g;

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

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

724 H; # Concat this line to HoldSpace

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

726 $ b cont

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

728 }

729 :cont

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

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

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

733 # . of ^. is workaround for FreeBSD sed

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

735 p; # Print whole "table" element

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

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

738 }

739 x; # Back to the newest line

740 p; # Print rest' | miniul

741 }

742 miniul() {

743 sed -e '

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

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

746 :top

747 s/\n//;

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

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

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

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

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

753 $ b cont

754 N; # 次の行を読む

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

756 /^ /b top

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

758 p; s/.*//;

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

760 /^\* /b top

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

762 }

763

764 :cont

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

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

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

768 H; x

769 }

770 x' | miniol

771 }

772 miniol() {

773 sed -e '

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

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

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

777 x;

778 :top

779 s/\n//;

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

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

782 x;

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

784 x;

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

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

787 $ b cont

788 N; # 次の行を読む

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

790 /^ /b top

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

792 p; s/.*//;

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

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

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

796 }

797

798 :cont

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

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

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

802 H; x

803 }

804 x'

805 }

806 acclog() (

807 # $1=table, $2=rowid

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

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

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

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

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

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

814 fi

815 )

816 gecos() (

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

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

819 )

820 setpar() {

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

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

823 case "$1" in

824 user) _user="$v" ;;

825 skey) _skey="$v" ;;

826 esac

827 fi

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

829 }

830 unsetpar() {

831 for i; do

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

833 case "$i" in

834 user|skey) unset _$i ;;

835 esac

836 fi

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

838 done

839 }

840 replpar() {

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

842 }

843 getpar() {

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

845 val=""

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

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

848 user) val=$_user ;;

849 skey) val=$_skey ;;

850 esac

851 fi

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

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

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

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

856 fi

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

858 case "$var" in

859 owner)

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

861 echo $user; return

862 elif ismember $user $val; then

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

864 fi ;;

865 esac

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

867 printf '%s' "$val"

868 }

869 setskey() {

870 # For quick response...(?)

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

872 }

873 chkskey() {

874 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

880 return 0

881 fi

882 return 1

883 }

884 resetskey() {

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

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

887 fi

888 }

889 getpartype() {

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

891 }

892 getparcount() {

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

894 }

895 getparfilename() {

896 # null if type of $1 is not file

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

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

899 }

900 sqlquote() {

901 (v="$1"

902 case "$v" in

903 "") return ;; # null

904 "X'"*) # quoted hex string

905 echo $1 ;;

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

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

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

909 return ;;

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

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

912 return ;;

913 *)

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

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

916 else

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

918 fi ;;

919 esac)

920 }

921 sqlquotestr() (

922 case "$1" in

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

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

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

926 esac

927 )

928 mktempd() {

929 TMPDIR=$tmpd mktemp -d -t $session.XXXX

930 }

931 getcachedir() { # $1=maintable

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

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

934 else

935 echo $tmpd/$thumbxy

936 fi

937 }

938 getval() {

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

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

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

942 echo "$user" ;;

943 stamp|datetime)

944 date "+%F %T" ;;

945 serial)

946 (s=`getpar $2`

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

948 *)

949 getpar "$2" "$3";;

950 esac

951 }

952

953 getvalquote() {

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

955 (v=`getval "$@"`

956 case "$v" in

957 "") echo NULL ;;

958 *) sqlquotestr "$v" ;;

959 esac)

960 }

961 getparquote() {

962 sqlquote "`getpar $1`"

963 }

964 getvalbyid() {

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

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

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

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

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

970

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

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

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

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

975 return

976 fi

977 done

978 rowid=$3

979 pk=`gettblpkey $1`

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

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

982 td=${4:-$tmpd}

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

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

985 for kt in s m; do

986 t=${1}_$kt

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

988 vcount=1 # count(val)

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

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

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

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

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

994 if [ $kt = m ]; then

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

996 # Reset val to store filenames if type is string

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

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

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

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

1001 i=0

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

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

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

1005 i=$((i+1))

1006 fn=$c.$i

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

1008 case $type in

1009 file:*)

1010 #file=$td/$val

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

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

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

1014 # FOR SPEED: Skip file generation if imgcache exists

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

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

1017 && continue

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

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

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

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

1022 select rowid from $cond $slice;

1023 .output '$td/$fn'

1024 select val from $cond $slice;

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

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

1027 .output stdout

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

1029 EOF

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

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

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

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

1034 ls -lh "$file" |

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

1036 case "$type" in

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

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

1039 *|*:[Aa]pplication*)

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

1041 png:- > "$file"

1042 ;;

1043 esac

1044 ;;

1045 *)

1046 sq $db<<EOF

1047 .output $td/$fn.rowid

1048 select rowid from $cond $slice;

1049 .output $td/$fn

1050 select val from $cond $slice;

1051 EOF

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

1053 ;;

1054 esac

1055 done

1056 else

1057 rm -f $td/$c.count

1058 case $type in

1059 file:*)

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

1061 | while read fn; do

1062 file=$td/$fn

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

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

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

1066 | unhexize > "$file"

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

1068 case $type in

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

1070 *:[Aa]pplication*)

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

1072 png:- > $file ;;

1073 esac

1074 fi

1075 done

1076 ;;

1077 esac

1078 fi

1079 printf '%s' "$val"

1080 return

1081 fi

1082 done

1083 done)

1084 }

1085 getvalbypkey() (

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

1087 pk=`gettblpkey $1`

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

1089 getvalbyid "$1" "$2" $rowid $4

1090 )

1091 getvalbycond() {

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

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

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

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

1096 getvalbyid "$1" "$2" $rowid "$4"

1097 fi

1098 }

1099 getpwfield() {

1100 # getpwfield user column

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

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

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

1104 echo "$val"

1105 return 0

1106 else

1107 return 1

1108 fi

1109 }

1110 numericalize() {

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

1112 }

1113 encode() {

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

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

1116 sha1=sha1

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

1118 sha1=sha1sum

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

1120 sha1=gsha1sum

1121 fi

1122 fi

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

1124 }

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

1126 gs_pdfwrite() {

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

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

1129 }

1130 fi

1131 enjpeg() {

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

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

1134 cjpeg="cjpeg"

1135 else

1136 cjpeg="convert - jpeg:-"

1137 fi

1138 fi

1139 $cjpeg "$@"

1140 }

1141 mycrypt() (

1142 key=$1 salt=$2

1143 # err \$2=$2

1144 case $2 in

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

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

1147 esac

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

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

1150 )

1151 hexize() {

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

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

1154 hexize="xxd -p"

1155 else

1156 hexize_hd() {

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

1158 }

1159 hexize="hexize_hd"

1160 fi

1161 fi

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

1163 }

1164 unhexize() {

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

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

1167 unhex="xxd -p -r"

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

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

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

1171 EOF

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

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

1174 # world writable...:)

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

1176 fi

1177 fi

1178 cat "$@" | $unhex

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

1180 }

1181 percenthex() {

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

1183 }

1184 htmlescape() {

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

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

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

1188 }

1189 enascii() {

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

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

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

1193 else

1194 enascii_now=`date +%FT%T`

1195 enascii_sed() {

1196 nkf -Z0Z1Z2 \

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

1198 }

1199 enascii="enascii_sed"

1200 fi

1201 fi

1202 cat "$@" | $enascii

1203 }

1204 size_h() {

1205 i="$1" oi=$1

1206 set -- B B KB MB GB TB

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

1208 oi=$i

1209 i=$((i/1024))

1210 shift

1211 done

1212 echo ${oi}$1

1213 }

1214 gettblconf() {

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

1216 ## tconfs=`sq $db \

1217 tconfs=`query \

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

1219 fi

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

1221 }

1222 gettblkeys() {

1223 # $1=tbl

1224 gettblconf

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

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

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

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

1229 case $type in

1230 *p*)

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

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

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

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

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

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

1237 esac

1238 case $type in

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

1240 esac

1241 done

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

1243 }

1244 gettblpkey() {

1245 # $1=tbl

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

1247 }

1248 gettblfkey() {

1249 (x=`gettblkeys $1`

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

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

1252 }

1253 gettblcols() {

1254 (x=`gettblkeys $1`

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

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

1257 }

1258 gettbl_s_cols() {

1259 (x=`gettblkeys $1`

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

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

1262 }

1263 gettbl_m_cols() {

1264 (x=`gettblkeys $1`

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

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

1267 }

1268 gettbl_h_cols() {

1269 (x=`gettblkeys $1`

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

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

1272 }

1273 gettbl_coltype() (

1274 gettblconf

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

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

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

1278 )

1279 is_hidden() {

1280 # $1=Tbl $2=col

1281 gettblconf

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

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

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

1285 case $x in

1286 *h*) return 0 ;;

1287 *) return 1 ;;

1288 esac

1289 }

1290

1291 dbsetbyid() {

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

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

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

1295 quotedp=$(sqlquotestr "$p")

1296 unset primary update

1297 gettblconf

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

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

1300 #err conf=$conf

1301 case ${conf#*=} in

1302 p*) primary=1 ;;

1303 f*) update=1 ;;

1304 u*) ;;

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

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

1307 esac

1308 #err t=$t

1309 type=string fn=""

1310 case $conf in

1311 */password)

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

1313 ;;

1314 */image*|*/document*)

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

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

1317 ;;

1318 esac

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

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

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

1322 if [ "$primary" ]; then

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

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

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

1326 elif [ "$update" ]; then

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

1328 else

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

1330 fi

1331 )

1332 }

1333 expire() (

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

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

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

1337 )

1338 addsession() {

1339 # expireをセット

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

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

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

1343 # Remove old session parameters

1344 now=`expire now`

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

1346 }

1347 gencookie() (

1348 path=${URL#*:/}

1349 path=${URL%/*}

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

1351 for kv; do

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

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

1354 done

1355 )

1356 contenttype() {

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

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

1359 }

1360 putheader() {

1361 :

1362 }

1363 putfooter() {

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

1365 }

1366 getcookie() {

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

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

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

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

1371 case "$k" in

1372 user) _user="$v" ;;

1373 skey) _skey="$v" ;;

1374 esac

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

1376 done

1377 }

1378 genrandom() {

1379 # $1=columns (default: 10)

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

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

1382 }

1383 genserial() {

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

1385 }

1386 smail() {

1387 # smail rcpts subj (file)

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

1389 # $MAIL_FROM <- From: header value

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

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

1392 ## Gmail rejects below(Duplicated headers)

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

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

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

1396 sender=${SENDER:-$admin}

1397 # Do not call m4 with directly passing text

1398 _r=$tmpd/rcpt

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

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

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

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

1403 }

1404 smail_queue_flush() {

1405 # $1=timelimit

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

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

1408 SELECT rowid FROM smailq

1409 GROUP BY rcpts, subj

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

1411 EOF

1412 )

1413 for rid in $rowids; do

1414 sq $workdb \

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

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

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

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

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

1420

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

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

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

1424 .separator "\n" "------------------\n\n"

1425 SELECT time, text FROM smailq

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

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

1428 ORDER by time;

1429 EOF

1430 then

1431 cat <<-EOF | sq $workdb

1432 DELETE FROM smailq

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

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

1435 EOF

1436 fi

1437 fi

1438 done

1439 }

1440 smail_queue() {

1441 # $1=Rcpts, $@=subj

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

1443 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1452 EOF

1453 )

1454 ## XXX: Adhoc load mitigation

1455 case "$now" in

1456 *[01])

1457 err flush_queue=$mintime

1458 smail_queue_flush ;;

1459 esac

1460 fi

1461 }

1462 setviastring() {

1463 table=par

1464 oifs="$IFS"

1465 IFS="&"

1466 for us in $1; do

1467 k=${us%%=*}

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

1469 setpar "$k" "string" "$v"

1470 done

1471 IFS="$oifs"

1472 }

1473 checkdomain() (

1474 # Check the validity of domain by referring DNS

1475 item=$1

1476 err checkdomain $1

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

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

1479 )

1480 pwcheck() {

1481 # $1=passwd

1482 dbpswd=`getpwfield $user pswd`

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

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

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

1486 }

1487 mypwhash() {

1488 mycrypt "`cat`" `genrandom 5`

1489 }

1490 flag_profupdate() {

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

1492 # This is useful to mitigate account sync load

1493 query <<-EOF

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

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

1496 EOF

1497 touch $userupdateflag

1498 }

1499 wasureta() {

1500 user=$1

1501 if ! checkdomain $user; then

1502 contenttype; echo

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

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

1505 putfooter

1506 exit 0

1507 fi

1508 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1510 encpswd=`echo $newpswd|mypwhash`

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

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

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

1514 $msgdir/mail-newaccount.m4 \

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

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

1517 }

1518 checkauth() {

1519 user=`getpar user`

1520 skc=`getpar skey` # from cookie

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

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

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

1524 if chkskey "$skc"; then

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

1526 return 0

1527 fi

1528 fi

1529 pswd=`getpar pswd`

1530 quser=`sqlquotestr "$user"`

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

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

1533 return 4 # 4=server too heavy

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

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

1536 return 2 # 2=login fail

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

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

1539 contenttype; echo

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

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

1542 cat <<-EOF | html p

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

1544 EOF

1545 exit 0 # XXXXX more smart...

1546 fi

1547 wasureta "$user"

1548 err wasureta issued for "$user"

1549 return 1 # wasureta error

1550 fi

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

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

1553 if pwcheck "$pswd"; then

1554 newsession=`genrandom 34`

1555 if setskey "$user" "$newsession" &&

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

1557 gencookie "user=$user" "skey=$newsession"

1558 return 0

1559 else

1560 return 4 # Heavy load??

1561 fi

1562 fi

1563 err "Login failed: [$user]"

1564 return 2 # Password mismatch

1565 }

1566 showlogin() {

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

1568 test -z "$args" && resetskey

1569 s4name=${S4NAME:-s4}

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

1571 cat $layout/login.m4.html

1572 echo '</body></html>'

1573 ) | _m4 \

1574 -D_BODYCLASS_="login" \

1575 -D_TITLE_="$s4name" \

1576 -D_SYSNAME_="Welcome to $s4name" \

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

1578 exit 0

1579 }

1580 dologin() {

1581 test -n "$S4WORLD" && syncaccount

1582 checkauth

1583 st=$?

1584 if [ $st != 0 ]; then

1585 contenttype; echo

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

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

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

1589 showlogin # and EXIT

1590 fi

1591 err "Auth OK: [$user]"

1592 }

1593

1594 # Do instant jobs here

1595 dbsetup

1596 trap cleanup INT HUP EXIT TERM PIPE

1597 # trap cleanup INT HUP

1598

1599

1600 cgiinit() {

1601 tmpd=`tmpd=$tmpdir mktempd`

1602 tmpf=$tmpd/stream.$$

1603 tmpfiles=$tmpfiles" $tmpd"

1604 addsession $session

1605 getcookie

1606 case "$REQUEST_METHOD" in

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

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

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

1610 # cat > $tmpf # too much?

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

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

1613 s="`cat $tmpf`"

1614 ;;

1615 esac

1616 case "$CONTENT_TYPE" in

1617 *boundary*)

1618 bndry=${CONTENT_TYPE#*boundary=}

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

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

1621 do

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

1623 #echo u=$us

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

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

1626 case "$k" in

1627 *:filename)

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

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

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

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

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

1633 :

1634 fi

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

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

1637 case "$mimetype" in

1638 [Ii]mage/x-xcf)

1639 bzip2 "$tmpd/$v"

1640 v=${v}.bz2

1641 ;;

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

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

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

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

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

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

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

1649 v=$vjpg

1650 else

1651 mimetype="Not supported"

1652 fi

1653 ;;

1654 [Ii]mage/*)

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

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

1657 ;;

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

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

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

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

1662 err Calling gs from $v to $nv

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

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

1665 v=$nv

1666 fi

1667 fi

1668 fi

1669 esac

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

1671 then

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

1673 continue

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

1675 replpar text string \

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

1677 continue

1678 fi

1679 ;;

1680 image|document|binary)

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

1682 continue ;;

1683 *)

1684 type='string'

1685 ;;

1686 esac

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

1688 setpar "$k" "$type" "$v"

1689 done

1690 ;;

1691 *)

1692 setviastring "$s"

1693 ;;

1694 esac

1695 }

1696 email4group() {

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

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

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

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

1701 sql="WITH

1702 grpemails AS (

1703 SELECT gname, user, val email

1704 FROM grp_mem NATURAL JOIN grp_mem_s

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

1706 useremails AS (

1707 SELECT user.name, val email

1708 FROM user

1709 LEFT JOIN user_m

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

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

1712 FROM useremails u LEFT JOIN grpemails g

1713 ON u.name=g.user

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

1715 query "$sql"

1716 }

1717 email4groupbyuid() {

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

1719 g=$1; shift

1720 uids=`echo "$@"`

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

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

1723 email4group "$g" `query "$sql"`

1724 }

1725 myemail4group() {

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

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

1728 }

1729 collectmembersbyid() {

1730 # Collect user names of group specified by grid

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

1732 query "SELECT user FROM grp_mem \

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

1734 }

1735 collectmembersbyid() {

1736 # Collect user names of group name

1737 qgrp=`sqlquote "$1"`

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

1739 }

1740 collectgecosesbyid() {

1741 # Collect user gecoses of group

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

1743 query<<-EOF

1744 SELECT gecos

1745 FROM gecoses

1746 WHERE name IN (SELECT user FROM grp_mem

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

1748 EOF

1749 }

1750 collectemail() (

1751 # Collect email addresses for group $1

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

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

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

1755 for e; do

1756 if isuser "$e"; then

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

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

1759 else

1760 qgrp=`sqlquote "$e"`

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

1762 gmem="grp_mem"

1763 else

1764 tm=`sqlquote "$TEAM"`

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

1766 fi

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

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

1769 $gmem g left join grp_mem_s s

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

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

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

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

1774 query "$sql"

1775 fi

1776 done

1777 )

1778 sendinvitation() (

1779 # $1=email

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

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

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

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

1784 gecos=`gecos`

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

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

1787 _m4 -D_URL_="$urlbase" \

1788 -D_USER_="$name" \

1789 -D_EMAIL_="$1" \

1790 -D_REGIST_="$regist" \

1791 -D_ADMIN_="$admin" \

1792 $msgdir/mail-invite.m4 \

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

1794 return 0

1795 )

1796 emaildomaincheck() {

1797 case "$1" in

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

1799 *@*)

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

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

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

1803 return 2

1804 fi

1805 return 0

1806 ;;

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

1808 esac

1809 }

1810 invite() {

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

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

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

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

1815 return

1816 fi

1817 fi

1818 case "$email" in

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

1820 *@*)

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

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

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

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

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

1826 elif sendinvitation $email; then

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

1828 else # Cannot be reached here

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

1830 fi ;;

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

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

1833 esac

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

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

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

1837 fi

1838 if [ -f $invite_policy ]; then

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

1840 else

1841 pol="$invite_policy"

1842 fi

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

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

1845 -D_INVITE_POLICY_="$pol" \

1846 $layout/html.m4.html $layout/invite.m4.html

1847 }

1848 regist() {

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

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

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

1852 echo "bye bye" | html p

1853 reutrn

1854 fi

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

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

1857 cat<<EOF

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

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

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

1861 EOF

1862 return

1863 fi

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

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

1866 # Fake login password to wasureta

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

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

1869 wasureta $email

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

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

1872 addsession $1 # for removal after 1 minute

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

1874 $layout/login.m4.html

1875 return

1876 }

1877 group_safename() {

1878 # Convert $1 to safe group name

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

1880 }

1881 groupupdate() {

1882 gname=`getpar gname`

1883 qgname=`sqlquote "$gname"`

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

1885 # See ALSO same job in showgroup()

1886 newgname=`group_safename "$gname"`

1887 err newgname=$newgname

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

1889 err NewGNAME: gname=$newgname

1890 gname=$newgname

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

1892 replpar gname string "$gname"

1893 fi

1894 # Name confliction check

1895 parow=`getpar rowid`

1896 ## err parow=$parow

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

1898 query "BEGIN EXCLUSIVE;"

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

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

1901 if [ $count -gt 0 ]; then

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

1903 query "END;"

1904 return

1905 fi

1906 par2table $formdir/grp.def

1907 query "END TRANSACTION;"

1908 # Remove orphan

1909 : <<EOF

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

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

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

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

1914 EOF

1915 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1919 sql="delete from blog where id in

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

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

1922 query "$sql";

1923 unsetpar tag kwd

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

1925 return # and return

1926 fi

1927 fi

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

1929 fi

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

1931 grid=$(query $sql)

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

1933 grp $grid

1934 }

1935 groupclone() {

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

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

1938 case "$1" in

1939 */noteam)

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

1941 esac

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

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

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

1945 return

1946 fi

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

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

1949 return

1950 fi

1951 i=0

1952 while true; do

1953 copy="-copy$i"

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

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

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

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

1958 i=$((i++))

1959 continue

1960 fi

1961 break

1962 done

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

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

1965 query<<-EOF

1966 BEGIN;

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

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

1969 SELECT $newqname, key, val

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

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

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

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

1974 -- Copy members and their configuration --

1975 REPLACE INTO grp_mem SELECT $newqname, user

1976 FROM grp_mem WHERE gname=$qgrp;

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

1978 FROM grp_mem_s WHERE gname=$qgrp;

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

1980 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1981 -- Copy administrators --

1982 REPLACE INTO grp_adm SELECT $newqname, user

1983 FROM grp_adm WHERE gname=$qgrp;

1984 COMMIT;

1985 EOF

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

1987 STOPCLONEMSG=1 groupconf "$newrowid"

1988 }

1989 groupman() {

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

1991

1992 GF_STAGE="grpconf"

1993 GF_STAGE=groupupdate

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

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

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

1997 -D_DUMPTABLE_="syscmd(cat)" \

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

1999 }

2000 userconf() {

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

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

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

2004 }

2005 groupconf() {

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

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

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

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

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

2011

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

2013 grp=`getgroupbyid $rowid`

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

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

2016 return

2017 fi

2018

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

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

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

2022 ## Setup migration menu

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

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

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

2026 err v=$v

2027 if [ -n "$v" ]; then

2028 migrate=$(cat<<-EOF

2029 `cgi_radio grpaction migrate id="migrate"`<label

2030 for="migrate">別Worldへ移住</label>

2031 <div style="height: $height;">

2032 <form action="?migrategrp">

2033 <p>移住先:<select name="migrateto">$nl$v$nl</select></p>

2034 <p>グループや掲示板のURLが変わります。

2035 外部からリンクしている場合は飛べなくなります。

2036 すでにリンクされた掲示板を多数含む場合は既存グループを温存し、

2037 「グループのクローン」で

2038 メンバーを引き継いだ上でそのクローンを移住するのがお勧めです。</p>

2039 <p><label>`cgi_checkbox emichk yes`確認</label></p>

2040 `cgi_hidden stage migrategrp`

2041 `cgi_hidden rowid $rowid`

2042 `cgi_submit OK`

2043 `cgi_reset Reset`

2044 </form>

2045 </div>

2046 EOF

2047 )

2048 fi

2049 fi

2050 html div 'class="foldtabs"' <<-EOF

2051 `cgi_radio grpaction clone id="clone"`<label

2052 for="clone">グループのクローン作成</label>

2053 <div style="height: $height;">

2054 <p>構成メンバーが同じ新規グループを作成します。</p>

2055 <table>

2056 <tr><td><a href="?groupclone+$rowid">

2057 <button>クローン作成(チームも複製)</button></a></td>

2058 <td><p>(チームなどもそのままで掲示板なしの状態から)</p></td></tr>

2059 <tr><td><a href="?groupclone+$rowid/noteam">

2060 <button>作成(チームなし)</button></a></td>

2061 <td>(チームは引き継がずメンバーのみ同じグループを作成)</td></tr>

2062 </table>

2063 <p>ボタンを押すと即作成します。不要な場合はグループ編集で

2064 削除してください。</p>

2065 </div>

2066 $migrate

2067 `cgi_radio grpaction close id="x"`<label for="x" accesskey="x">×</label>

2068 <div style="height: $height; background: transparent;"></div>

2069 EOF

2070 fi

2071 }

2072 migrategrp() {

2073 rowid=`getpar rowid`

2074 rowid=${rowid%%[!0-9]*}

2075 grp=`getgroupbyid $rowid`

2076 if ! isgrpowner "$user" "$grp"; then

2077 echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"

2078 return

2079 fi

2080 if [ x`getpar emichk` != x"yes" ]; then

2081 echo "移住確認未チェックなので中止します。" | html p

2082 grp "$rowid"

2083 return

2084 fi

2085 destconf=`getpar migrateto`

2086 err destconf=$destconf

2087 if [ ! -e $destconf ]; then

2088 echo "移住先Worldが認識できないので中止します($destconf)。" | html p

2089 grp "$rowid"

2090 return

2091 fi

2092 if [ -n "$worldconf" ]; then

2093 srcconf=$worldconf

2094 else

2095 srcconf=s4-config.sh

2096 fi

2097 _m4 -D_TITLE_="移住操作" -D_BODYCLASS_="" $layout/html.m4.html

2098 echo "移住操作" | html h1

2099 echo '<pre>'

2100 set -- "$srcconf" "$destconf" "$rowid"

2101 err ./s4-migrate.sh "$srcconf" "$destconf" "$rowid"

2102 . ./s4-migrate.sh # Dot(.) sourcing might not pass arguments

2103 rc=$?

2104 echo "</pre>"

2105 if [ $rc -eq 0 ]; then

2106 echo "World [$world] への移住完了。" | html p

2107 echo "<p><a href=\"$dsturl?grp+$destrowid\">移住先</a></p>"

2108 elif [ -n "$faillist" ]; then

2109 echo "移住後削除失敗" | html p

2110 echo "空いている時間帯に再度試して下さい。" | html p

2111 else

2112 echo "移住失敗" | html p

2113 echo "移動先に重複がないか確認して下さい。" | html p

2114 fi

2115 return

2116 }

2117 mems() {

2118 _m4 -D_TITLE_="参加者一覧" -D_BODYCLASS_=listmember $layout/html.m4.html

2119 kwd=`getpar kwd`

2120 listmember $kwd

2121 }

2122 grps() {

2123 case "$S4WORLD" in

2124 $nonewgroupworld) ;;

2125 *) LINK_NEWGRP="<a href=\"?groupman\">新規グループ作成</a>" ;;

2126 esac

2127 _m4 -D_TITLE_="グループ一覧" -D_BODYCLASS_=listgroup $layout/html.m4.html

2128 kwd=`getpar kwd`

2129 listgroup "$kwd" \

2130 | _m4 -D_DUMPTABLE_="syscmd(cat)" \

2131 -D_TITLE_="グループ関連操作" \

2132 -D_FORM_="${LINK_NEWGRP}${NEWGRP_GUIDE}" \

2133 $layout/form+dump.m4.html

2134 }

2135 grp() { # $1=group-rowid

2136 gpg=`getpar grp`

2137 grid=${1:-$gpg}

2138 grp=`getgroupbyid "$grid"`

2139 ## . ./s4-blog.sh

2140 jg=`getpar joingrp`

2141 if [ -n "$jg" ]; then

2142 [ -n "$jg" -a -n "$grp" ] &&

2143 joingrp "$grp" "$user" "$jg" "`getpar email`"

2144 fi

2145 htmlheader=$layout/html.m4.html

2146 showgroup "$grid"

2147 }

2148 sql4interestblogs() {

2149 cat<<EOF

2150 CREATE TEMPORARY VIEW interestblogs AS

2151 SELECT blog.rowid rid, id, author

2152 FROM blog

2153 NATURAL JOIN

2154 (SELECT id, val owner FROM blog_s WHERE key='owner') bs

2155 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2156 THEN 1 -- blog owner is an user, READABLE when

2157 WHEN (SELECT user FROM grp_mem

2158 WHERE gname=bs.owner AND user='$user') IS NULL

2159 THEN 0

2160 ELSE 1

2161 END;

2162 EOF

2163 }

2164 listnewblogsql() { # $1=user

2165 newdays=${WHATS_NEW_DAYS##*[!0-9]} # Shave non-digits

2166 newdays=${newdays%%[!0-9]*}

2167 newdays=${newdays:-14}

2168 basetime="datetime('now', 'localtime', '-${newdays} days')"

2169 deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog

2170 WHERE user='$user'

2171 AND tblrowid IN

2172 ($blogreadflagrowid,

2173 $blogcutoffflagrowid)),

2174 $basetime -- "0"

2175 );"`

2176 cat<<EOF

2177 `sql4interestblogs`

2178 WITH article_ctime as (

2179 SELECT id,blogid,author,max(val) ctime

2180 FROM article join article_s s using(id)

2181 WHERE s.key='ctime' AND s.val > '$deftime'

2182 GROUP BY id

2183 ), blog_title_owner as (

2184 SELECT blg.rid brid, id,

2185 max(case key when 'title' then val end) title,

2186 max(case key when 'owner' then val end) owner

2187 FROM interestblogs blg, blog_s using(id) group by id

2188 ), blogall as (

2189 SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid

2190 ), news as (

2191 SELECT brid, bl.id blid, bl.title, ctime,

2192 coalesce(al.time, '$deftime') atime,

2193 count(bl.id) "新着", bl.author

2194 FROM blogall bl

2195 LEFT JOIN

2196 (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al

2197 ON bl.brid=al.tblrowid

2198 WHERE atime < bl.ctime

2199 GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id

2200 LIMIT 10

2201 ) SELECT brid LINK, "新着",

2202 (SELECT count(*) FROM article WHERE blogid=blid) "総数",

2203 ctime, title,

2204 (SELECT gecos FROM gecoses WHERE name=author) gecos

2205 FROM news;

2206 EOF

2207 }

2208

2209 search_form() {

2210 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

2211 # $2(optional) = pre-input keywords

2212 help="(1)空白区切りの単語で本文検索

2213 (2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索

2214 @2016-0[1-6] → 2016年1月から6月

2215 @>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間

2216 @week → 最近一週間

2217 (3)#番号で記事ID検索

2218 (1)と(2)は組み合わせOK

2219 例: @2016-10-0[1-9] 芋煮

2220 → 2016年10月上旬でキーワード「芋煮」を含む記事検索

2221 ※クイズ板は検索対象から外されます。"

2222 auth=""

2223 placeholder="全記事からの検索"

2224 case "$1" in

2225 author=*)

2226 a=`echo "${1#author=}"|htmlescape`

2227 g=`gecos ${1#author=}`

2228 auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"

2229 placeholder="このユーザの書込検索"

2230 help="★★ $g さんの書き込みから検索します$nl$help"

2231 ;;

2232 grid=*)

2233 a=`echo "${1#grid=}"`; a=$((0 + $a))

2234 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

2235 placeholder="このグループからの検索"

2236 ;;

2237 esac

2238 inikwd="$2" # no need to htmlescape

2239 cat<<-EOF

2240 <div class="right">

2241 <form action="$myname">$auth

2242 <input type="text" name="kwd" value="$inikwd" title="$help"

2243 placeholder=" $placeholder " width="10" accesskey="k">

2244

2245 ${touchpanel:+<p class="help">$help</p>}

2246 <input type="hidden" name="stage" value="searchart">

2247

2248 </form>

2249 </div>

2250 EOF

2251 }

2252

2253 imgsrc_cache() (

2254 # $1 = directory for cache'ing

2255 # $2 = table (user_m or grp_m)

2256 # $3 = keycond (was: condition for choosingowner)

2257 # $4 = size : S = Small, M = Medium, O = Original

2258 dir="$1" tbl="$2"

2259 keycond="$3"

2260 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

2261 ORDER BY rowid DESC LIMIT 1"

2262 [ -d "$dir" ] || mkdir -p "$dir"

2263 tmpf=$tmpd/imgsrc_cache.$$

2264 case "$4" in

2265 [Ss]) size=S ;;

2266 [Oo]) size=O ;;

2267 *) size=M ;;

2268 esac

2269 # ImageCache filename storing schema:

2270 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

2271 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

2272 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

2273 valtype=`query "$sql0"`

2274 filename=${valtype%%//*}

2275 filetype=${valtype##*//file:}

2276 if [ x"$filename" = x"${filename%.*}" ]; then

2277 # If nor filename extension found, set it to image type

2278 case "$filetype" in

2279 image/*) filename=$filename.${filetype#image/} ;;

2280 esac

2281 fi

2282 cacheimg_S=$dir/S_$filename

2283 cacheimg_M=$dir/M_$filename

2284 cacheimg_O=$dir/$filename

2285 cacheimg=$dir/${size}_$filename

2286 sumfile="$dir/$filename.sum"

2287 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

2288 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2289 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2290 # if cache is fresh and has the same checksum,

2291 echo "<img src=\"$cacheimg\">"

2292 else

2293 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2294 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2295 fifo2=$fifo.2

2296 mkfifo $fifo $fifo2

2297 fmt=${filename##*.}

2298 ## [[ NOTE ]]

2299 ## a. convert oldimage newimage

2300 ## b. convert oldimage fmt:- | convert - newimage

2301 ## b is much smaller than a

2302 cat $tmpf | unhexize \

2303 | tee $fifo \

2304 | convert -define ${fmt}:size=${iconxy_M} \

2305 -resize ${iconxy_M}'>' - ${fmt}:- \

2306 | tee $fifo2 \

2307 | convert - "$cacheimg_M" &

2308 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2309 -resize ${iconxy_S}'>' - ${fmt}:- \

2310 | convert - "$cacheimg_S" &

2311 printf '%s' "<img src=\"data:${filetype},"

2312 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2313 echo '">'

2314 echo "$sum" > $sumfile

2315 fi

2316 ## Now preparing cache image, done.

2317 ## Store this information to DB

2318 stbl=${tbl%_m}_s # user_s or grp_s

2319 pkey=${keycond%%=*} # Primary Key name

2320 pval=${keycond#*=} # Primary Key value

2321 query <<-EOF

2322 REPLACE INTO $stbl($pkey, key, type, val)

2323 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2324 EOF

2325 )

2326

2327 showhome() {

2328 # $1=userRowIdToShow

2329 err showhome \$1=$1

2330 case "$1" in

2331 *@*) uname=`getvalbypkey user name "$1"` ;;

2332 *) uname=`getvalbyid user name $1` ;;

2333 esac

2334 ## err ShowHome: uname=$uname

2335 td=`getcachedir home/"$1"`

2336 gecos=`gecos "$uname"`

2337 ## err SH:gecos=$gecos

2338 GF_VIEWONLY=1

2339 cond="gname in (select gname from grp_mem where user='$uname')"

2340 search_form_args=""

2341 if [ x"$user" = x"$uname" ]; then

2342 if [ -z "$S4MASTERDB" ]; then

2343 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2344 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2345 elif [ -n "$S4MASTERURL" ]; then

2346 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2347 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2348 fi

2349 usermenu="$usermenu

2350 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2351 # Display folders

2352 sql="select count(id) from article_m where id

2353 in (select id from article where author='$user')

2354 and type like 'file:%';"

2355 ## err nfile-sql=`echo "$sql"`

2356 nfile=`query "$sql"`

2357 # err nfile=$nfile

2358 if [ $nfile -gt 0 ]; then

2359 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2360 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2361 fi

2362 else

2363 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2364 GROUP BY user;"`

2365 usermenu="<p>Last seen on $latestlog</p>"

2366 search_form_args="author=$uname"

2367 fi

2368 . ./s4-blog.sh

2369

2370 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2371 search_form "$search_form_args" > $sf

2372 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2373 | htmlescape > $tf

2374 { echo "<div class=\"noprofimg\">"

2375 viewtable $formdir/user.def user $1

2376 echo "</div>"

2377 } > $pf

2378

2379 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2380 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2381 imf=$tmpd/profimg.$$; touch $imf

2382 if [ -n "$img" ]; then

2383 if true; then

2384 tbl=user_m

2385 enticond="name='$uname'"

2386 imgsrc_cache "$td/main" user_m "$enticond" M

2387 else

2388 { printf '%s' "<IMG src=\"data:${img#file:},"

2389 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2390 | sed 's/$..$/%\1/g'

2391 echo '">'

2392 }

2393 fi > $imf

2394 fi

2395 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2396 val='$uname';"`

2397 listblog $uname > $bf

2398

2399 hometail=$tmpd/tail.$$

2400 mkfifo $hometail

2401

2402 #Calling listgroupbytable, originally here

2403

2404 (

2405 # Display Most Recent Entry

2406 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2407 shortval=${shortval:-val}

2408

2409 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2410 # $hidden_mode is defined in global section

2411 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2412 ctime,

2413 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2414 (SELECT gecos FROM gecoses

2415 WHERE name=(SELECT val FROM blog_s

2416 WHERE key='owner' AND id=b.id)) owner,

2417 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2418 FROM blog b

2419 JOIN

2420 (SELECT distinct blogid, a.id aid, max(val) ctime

2421 FROM article a, article_s s

2422 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2423 GROUP BY blogid ORDER BY val DESC LIMIT 50

2424 ) m

2425 ON b.id=m.blogid

2426 AND

2427 ('$uname' = '$user' -- user can read all own posts

2428 OR

2429 -- exclude posts of others in quiz/enquete blogs

2430 NOT EXISTS (SELECT * FROM blog_s

2431 WHERE id=b.id AND key='mode' AND val IN $hidden_mode));"

2432 # This should be as follows

2433 : <<EOF

2434 WITH arts AS(

2435 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2436 a.blogid, a.id id, s.val ctime

2437 FROM article a NATURAL JOIN article_s s

2438 WHERE s.key = 'ctime' AND a.author='$user'

2439 GROUP by s.id

2440 )

2441 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2442 FROM arts a0

2443 JOIN

2444 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2445 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2446 ORDER BY ctime DESC LIMIT 50;

2447 EOF

2448

2449 cat<<-EOF

2450 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2451 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2452 <div class="lcto">

2453 `DT_VIEW=replyblog dumptable html blog`

2454 </div>

2455 EOF

2456 unset DT_SQL

2457 if [ x"$user" = x"$uname" ]; then

2458 # Display NEWS

2459 # 2016-06-26

2460 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2461 acclog blog $blogreadflagrowid

2462 # echo "全部既読にしました" | html p

2463 fi

2464 # 2016-02-19 Counting NEWS without using dumptable.

2465 sql=`listnewblogsql "$user"`

2466 # echo "$sql" > tmp/listnew

2467 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2468 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2469 cont=$((cont-1))

2470 ##err newcount=$cont

2471 if [ $cont -gt 0 ]; then

2472 #echo "全体の新着記事${cont}傑" | html h2

2473 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2474 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2475 cat<<-EOF | html form 'action="?home"'

2476 `cgi_checkbox readchk yes 'id="read"'`<label

2477 for="read">新着ふくめて全部読んだことにする</label>

2478 `cgi_submit '確定'`

2479 `cgi_hidden read yes`

2480 EOF

2481 echo "$new10 "

2482 echo "</div>"

2483 else # If news is 0, set log cut off flag

2484 acclog blog $blogcutoffflagrowid # for speed

2485 fi

2486 else # Not My Home ($user != $uname)

2487 : # DT_SQL=

2488 fi

2489 ) > $hometail & # Is background call safe to m4??

2490 #

2491 listgroupbytable $formdir/grp.def "$cond" $uname |

2492 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2493 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2494 -D_PROFIMG_="spaste(\`$imf')" \

2495 -D_BLOGS_="spaste(\`$bf')" \

2496 -D_SEARCH_="spaste(\`$sf')" \

2497 -D_NBLOG_="$nblog" \

2498 -D_GROUPS_="syscmd(\`cat')" \

2499 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2500 $layout/html.m4.html $layout/home.m4.html

2501

2502 # Record access log

2503 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2504 }

2505 commission() { # $1=grp-rowid $2=user-rowid

2506 contenttype; echo

2507 ## err commission: "$@"

2508 gname=`getgroupbyid $1`

2509 echo "グループ $gname 管理者委任" \

2510 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2511 if [ -n "$2" ]; then

2512 grp_reg_adm "$@"

2513 else

2514 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2515 fi

2516 }

2517 listgroupbytable() {

2518 # $1=deffile $2...=condition $3(optional)=uname

2519 tagline=`grep :tag: $1`;

2520 and="${2:+and }" where=${2:+where }

2521 href="<a href=\"$myname?grp+"

2522 echo '<div class="listgroup">'

2523 sql="select val from grp_s where key='tag' $and$2 group by val;"

2524 ## err ListGRP: query sql="$sql"

2525 for tag in `query "$sql"`

2526 do

2527 ## err ListGrp: tag=$tag

2528 tn=${tagline%%=${tag}*}

2529 tn=${tn##*[ :]}

2530 sql="select rowid||':'||gname as 'グループ名',説明 from

2531 (select (select rowid from grp g where g.gname=grp_s.gname)

2532 as rowid,

2533 gname,

2534 max(case key when 'gecos' then val end) as '説明',

2535 max(case key when 'tag' then val end) as 'tag',

2536 max(case key when 'mtime' then val end) as mtime from grp_s

2537 $where$2 group by gname having tag='$tag' order by mtime desc);"

2538 ## err PersonalGroupList= `echo $sql`

2539 echo "<h2>$tn</h2>"

2540 echo '<table class="b listgroup">'

2541 sq -header -html $db "$sql" \

2542 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2543 echo '</table>'

2544 done

2545 if [ -n "$S4WORLDLIST" -a -n "$3" ]; then

2546 err "peekgrpworlds($user) BEGIN: `gdate +%S.%03N`"

2547 peekgrpworlds mem:"$3"

2548 err "peekgrpworlds($user) END: `gdate +%S.%03N`"

2549 fi

2550 echo '</div>'

2551 }

2552 iconhref() (

2553 # $1=icon-file, $2=Href $3=title $4...=anchor

2554 data=`percenthex "$1"`

2555 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2556 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2557 href=$2; title=$3; shift 3

2558 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2559 )

2560 iconhref2() (

2561 # $1=icon-file, $2=Href $3=title $4...=anchor

2562 src=$1

2563 href=$2; title=$3; shift 3

2564 anchor=`echo $@|htmlescape`

2565 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2566 )

2567 listentry() (

2568 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2569 # Referring variable $iamowner=$grp to attach owner-request links

2570 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2571 cond='' hiddens=''

2572 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2573 if [ -z "$offset" ]; then

2574 offset=`getpar start`; offset=${offset%%[!0-9]*}

2575 offset=$((offset-1))

2576 fi

2577 offset=$((offset + 0)) # change to numeric forcibly

2578 [ $offset -lt 0 ] && offset=0

2579 limit=$listentlimit

2580 dir=`getcachedir "$1"`

2581 if [ x"$1" = x"user" ]; then

2582 hrb="$myname?home"

2583 deficon=person-default.png

2584 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2585 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2586 gcs=gecos

2587 else # if group

2588 hrb="$myname?grp"

2589 deficon=group-default.png

2590 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2591 gcs=name

2592 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2593 if [ -n "$tagline" ]; then

2594 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2595 ## err tagconv=$tagconv

2596 fi

2597 fi

2598 if [ ! -d $dir ]; then

2599 mkdir -p $dir

2600 fi

2601 if [ ! -s $dir/$deficon ]; then

2602 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2603 fi

2604 kwd=${2:-`getpar kwd`}

2605 if [ -n "$kwd" ]; then

2606 kwd=`echo $kwd | tr -d '";\n' | tr -d "'"`

2607 case "$kwd" in

2608 mem:*@*)

2609 byuser=${kwd#*mem:}

2610 qusr=`sqlquote "$byuser"`

2611 cond1="(a.gname IN (SELECT gname FROM grp_mem WHERE user=$qusr))"

2612 ;;

2613 esac

2614 if [ x"$1" = x"group" ]; then

2615 if [ -n "$cond1" ]; then

2616 enthead="`gecos "$byuser"|htmlescape` さんの所属"

2617 else

2618 cond1="(b.name like '%${kwd}%')"

2619 fi

2620 else

2621 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2622 fi

2623 fi

2624 tag=`getpar tag` tag2=`getpar tag2`

2625 if [ x"$tag" = x"NULL" ]; then

2626 tag="" tag2=""

2627 fi

2628 if [ -n "$tag$tag2" ]; then

2629 tag=${tag:-$tag2}

2630 qtag=`sqlquote "$tag"`

2631 cond2="tag=$qtag"

2632 fi

2633 if [ -n "$cond1$cond2" ]; then

2634 cond="$cond1${cond2:+ AND $cond2}"

2635 cond="WHERE ${cond# AND }"

2636 fi

2637

2638 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2639 # grpは呼出し元の動的スコープ変数でよくないな...

2640 ##qgrp=`sqlquote $grp`

2641 getgrp="(select gname from grp where rowid=${rowid:--1})"

2642 sql="select a.rowid, a.$link,

2643 coalesce(b.$gcs, a.$nm) as nick,

2644 quote(a.$nm) as qname,

2645 (SELECT val FROM ${tbl}_s

2646 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2647 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2648 || case when a.$nm in (select gname from grp)

2649 then printf('(%d名)',

2650 (select count(user) from grp_mem where gname=a.$nm))

2651 else ' <'||a.$nm||'>'

2652 end

2653 as name,

2654 b.tag,

2655 case when a.$nm in (select user from grp_adm

2656 where gname=$getgrp) then '管理者'

2657 when '$user' in (select user from grp_adm where gname=a.$nm)

2658 then 'ADMIN'

2659 when '$user' in (select user from grp_mem where gname=a.$nm)

2660 then 'Member'

2661 when '$iamowner' = '' then ''

2662 else ',not='||a.rowid end as ownerlink,

2663 CASE '$entity'

2664 WHEN 'グループ'

2665 THEN coalesce(

2666 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2667 'open')

2668 ||

2669 CASE WHEN '$user'

2670 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2671 THEN ' ismember'

2672 ELSE ''

2673 END

2674 ELSE 'user'

2675 END regmode

2676 from $tbl a left join

2677 (select $nm as name,

2678 max(case key when 'gecos' then val end) as gecos,

2679 max(case key when 'tag' then val end) as tag,

2680 max(case key when 'mtime' then val end) as mtime,

2681 max(case key when 'wtime' then val end) as wtime,

2682 max(case key when 'login' then val end) as login

2683 from ${tbl}_s group by $nm)

2684 b on a.$nm=b.name $cond $3

2685 order by b.wtime desc, b.login desc,

2686 b.mtime desc, b.tag desc, a.rowid asc"

2687 # Give precedence to newer maintained groups (2016-09-24)

2688 # Note that mtime is stored only in grp_s.

2689 ## err LE:sql.1="$sql"

2690 total=`query "with x as ($sql) select count(*) from x;"`

2691 echo "$enthead${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2692 echo '<div class="listentry">' # List-entry div

2693 # Show owner/member filter button

2694 METHOD=GET

2695 hiddens="$hiddens

2696 `cgi_hidden stage \"$stage\"`"

2697 if [ x"$tbl" = x"grp" ]; then

2698 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2699 fh="<select name=\"tag\">$nl"

2700 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2701 for l in $args; do

2702 val=${l#*=} tname=${l%=*}

2703 if [ x"$val" = x"$tag" ]; then

2704 s=" selected"

2705 selectedtags="(種別[${tname}]のみ)"

2706 else

2707 s=""

2708 fi

2709 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2710 done

2711 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2712 cat<<-EOF

2713 <form action="$myname" method="$METHOD">

2714 </form>

2715 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2716 for="ismembtn">参加中以外隠す</label>

2717 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2718 for="isadmbtn">管理者参加以外隠す</label>

2719 EOF

2720 # limit=3

2721 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2722 fi

2723 if [ $total -gt $limit -o $ -n "$S4WORLDLIST" -a x"$tbl" = x"grp" $ ]; then

2724 echo '<div>'

2725 METHOD=GET cgi_form $stage <<EOF

2726 $form

2727 <label>次の語を含む${entity}で検索:

2728 `cgi_text kwd "$kwd"`</label>

2729 $hiddens

2730 EOF

2731 echo '</div>'

2732 else

2733 echo $selectedtags | html p

2734 fi

2735 test -n "$kwd" && hiddens="$hiddens$nl`cgi_hidden kwd \"$kwd\"`"

2736 cat<<EOF

2737 <form action="$myname" method="$METHOD">

2738 <p>${total}件中の<input class="hidesub" type="text" name="start"

2739 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2740 <input type="submit" value="確定"></p>

2741 </form>

2742 EOF

2743 if [ $((offset+limit)) -lt $total ]; then

2744 nextbtn=$(

2745 cat<<EOF

2746 <div class="right clear"><form action="$myname" method="$METHOD">

2747 `cgi_submit 次の${limit}件`

2748 $hiddens

2749 `cgi_hidden offset $((offset + limit))`</form></div>

2750 EOF

2751 )

2752 fi

2753 if [ $offset -gt 0 ]; then

2754 prevbtn=$(

2755 cat<<EOF

2756 <form action="$myname" method="$METHOD">

2757 `cgi_submit 前の${limit}件`

2758 $hiddens

2759 `cgi_hidden offset $((offset - limit))`</form>

2760 EOF

2761 )

2762 fi

2763 pnbtn="$nextbtn$prevbtn"

2764 echo $pnbtn

2765

2766 ## err ListEntry: `echo "$sql"\;`

2767 # sq $db here??? 2016-11-28

2768 query "$sql limit $limit ${offset:+offset $offset};" \

2769 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2770 # err name=$name owner=$ownerp lnk=$lnk

2771 # err newlnk=$lnk regmode=$regmode

2772 icondir=$dir/$id

2773 # Pick up only last icon

2774 htmlname=`echo $name|htmlescape`

2775 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2776 <p class=\"tag _$tag\">$tag</p>" \

2777 | _m4 $tagconv

2778 if [ -n "$NOSPEEDUP" ]; then

2779 files=`getvalbyid $tbl profimg $id $icondir`

2780 if [ -n "$files" ]; then

2781 icon=`echo "$files"|tail -1`

2782 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2783 else

2784 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2785 fi

2786 elif [ -n "$icon" -a -s "$icon" ]; then

2787 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2788 else

2789 cond="$nm=$qname"

2790 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2791 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2792 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2793 # err "img=[$img]"

2794 if [ -n "$img" ]; then

2795 echo "<a href=\"$hrb+$lnk\">"

2796 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2797 echo "</a>"

2798 else

2799 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2800 fi

2801 fi

2802 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2803 echo "</div>"

2804 done

2805 echo "</div>" # End of List-entry div

2806 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2807 if [ -n "$kwd" -a x"$tbl" = x"grp" -a -n "$S4WORLDS" ]; then

2808 peekgrpworlds "$kwd"

2809 fi

2810 )

2811 listmember() {

2812 listentry user "$@"

2813 }

2814 listgroup() {

2815 listentry group "$@"

2816 }

2817 hexteams() { # $1=gname, $2(optional)=user

2818 cond=${2:+" AND user='$2'"}

2819 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2820 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2821 }

2822 showgroup() { # $1=group-rowid

2823 if [ -z "$1" ]; then

2824 grid=`getpar grid`

2825 grid=${grid%%[!0-9]*}

2826 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2827 else

2828 grid=$1

2829 fi

2830 grp=`getgroupbyid $grid`

2831 qgrp=`sqlquote "$grp"`

2832 htmlgrp=`echo "$grp"|htmlescape`

2833 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2834 if isgroup "$grp"; then

2835 tf=$tmpd/title.$$

2836 sf=$tmpd/search.$$

2837 bodyclass=`query "SELECT val FROM grp_s

2838 WHERE gname=$qgrp AND key='regmode';"`

2839 if ismember "$user" "$grp"; then

2840 ismember="ismember"

2841 bodyclass="$bodyclass${bodyclass:+ }ismember"

2842 else

2843 ismember="" # bodyclass="group"

2844 fi

2845 bodyclass="$bodyclass grouphome"

2846 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2847 echo "グループ $htmlgrp" > $tf

2848

2849 showgroupsub $formdir/grp.def "$grid" | \

2850 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2851 -D_FORM_="syscmd(\`cat')" \

2852 -D_BODYCLASS_="$bodyclass" \

2853 -D_DUMPTABLE_="" \

2854 $htmlheader $sf $layout/form+dump.m4.html

2855 # $htmlheader $layout/form+dump.m4.html

2856 # $htmlheader is defined in grp()

2857 else # if $grp is removed at par2table

2858 listgroup

2859 fi

2860 }

2861 showgroupsub() {

2862 # $1=def-file $2=group-rowid

2863 # Using $ismember

2864 rowid=$2

2865 grp=`getgroupbyid $2`

2866 qgrp=`sqlquote "$grp"`

2867 td=`getcachedir grp/"$2"`

2868 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2869 if [ -z "$rowid" ]; then

2870 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2871 #grp=`sq $db "select gname from grp where rowid=$grp"`

2872 echo "showgroupsub: invalid argument($1 $2)" | html p

2873 return

2874 fi

2875 val=`getvalbyid grp profimg $rowid $tmpd`

2876 enticond="gname=$qgrp"

2877 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2878 if [ -n "$img" ]; then

2879 cat<<-EOF

2880 <p class="groupimg">

2881 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2882 EOF

2883 fi

2884 echo "<div class=\"noprofimg\">"

2885 viewtable $1 grp $rowid

2886 echo "</div>"

2887 if isgrpowner "$user" "$grp"; then

2888 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2889 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2890 iamowner=$rowid

2891 colmd=" mode"

2892 # We have to specify report-type blog to lshandoutall

2893 repblog=`query "

2894 WITH grpblogs AS (

2895 SELECT id FROM blog_s

2896 WHERE key='owner' AND val=(SELECT gname FROM grp WHERE rowid=$rowid)

2897 ), ownerMode AS (

2898 SELECT id,

2899 max(CASE key WHEN 'owner' THEN val END) owner,

2900 max(CASE key WHEN 'mode' THEN val END) mode

2901 FROM blog_s

2902 GROUP BY id

2903 ), blogid AS (

2904 SELECT id

2905 FROM grpblogs NATURAL LEFT JOIN ownerMode

2906 WHERE mode LIKE 'report%' LIMIT 1

2907 ) SELECT blog.rowid FROM blogid NATURAL LEFT JOIN blog;"`

2908 if [ -n "$repblog" ]; then

2909 csvid="gethandoutcsv"

2910 replink="/ <a href=\"?lshandoutall+$repblog#$csvid\">レポート全集計</a>"

2911 fi

2912 fi

2913 if [ -n "$ismember" ]; then

2914 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2915 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2916 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2917 cat<<-EOF

2918 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2919 href="?blog+$rowid">グループの新規話題作成</a>

2920 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2921 href="?grpaction+$rowid">メンバーを個別選択しての操作</a>

2922 $replink</p>

2923 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2924 <div class="fold clear">

2925 `cgi_checkbox send yes id="send"`<label

2926 for="send">グループ全員にメッセージ送信</label>

2927 <div>

2928 `cgi_textarea message "" "cols=60"`

2929 `cgi_submit 送信`

2930 `cgi_reset リセット`

2931 </div>

2932 `cgi_hidden grp $rowid`

2933 </div></form>

2934 EOF

2935 fi

2936 # 加入ボタン + 加入者リスト

2937 if [ -n "$ismember" ]; then

2938 ismem='checked' state="(参加中)"

2939 else

2940 nomem='checked' state="(現在非加入)"

2941 fi

2942 # このグループでの加入アドレス

2943 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2944 and key='email';"`

2945 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2946 ## and key='email';"

2947 ##err email=$eml

2948 cat <<EOF

2949 <div class="fold clear">

2950 `cgi_checkbox reg yes id="reg"`<label

2951 for="reg">自身の加入状態を操作する</label>$state

2952 <div>

2953 EOF

2954 cgi_form grp <<EOF

2955 <p>このグループに</p>

2956 <table class="b">

2957 <tr><th>メンバーとして</th><td>

2958 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2959 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2960 <tr><th>参加する場合のメイルアドレス<br>

2961 <small>(メインのアドレスとは違うものにする場合に記入<br>

2962 同じでよい場合は空欄に)</small></th>

2963 <td>`cgi_text email $eml`</td></tr>

2964 </table>

2965 `cgi_hidden grp $rowid`

2966 EOF

2967 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2968 echo "moderated (承認加入の)グループなので実際に参加できるのは

2969 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2970 fi

2971 echo '</div></div>'

2972 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2973 cat<<-EOF

2974 <div class="fold">

2975 <h2>話題一覧</h2>

2976 <form class="summary inline" action="$myname" title="$thelp">

2977 `cgi_hidden owner "$grp"`

2978 `cgi_hidden kwd "@week"`

2979 `cgi_hidden stage searchart`

2980 `cgi_submit "一週間のまとめ"`

2981 </form>

2982 <input type="checkbox" id="dtcheck" checked><label

2983 for="dtcheck">話題一覧表示</label>

2984 EOF

2985 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by \"稼動状態\", ctime desc"

2986 colstate="state:稼動状態:frozen=rowclass=凍結"

2987 frzbtn='<button class="toggle-frozen"></button>'

2988 if [ -n "$ismember" ]; then

2989 DT_COUNT=author DT_COUNT_HEADER="書" DT_COUNT_BY=$user

2990 fi

2991 DT_CHLD=article:blogid \

2992 DT_QOWNER="$qgrp" \

2993 DT_VIEW=replyblog dumptable html blog \

2994 "ctime title heading team notify:通知$colmd $colstate" "$cond" \

2995 | if [ -n "$iamowner" ]

2996 then

2997 sed -Ee "s,(<TD class=\"稼動状態\">).*(</TD>),\1$frzbtn\2,"

2998 else

2999 cat

3000 fi

3001 ## DO not convert to frzbtn when not admin

3002 echo "</div> "

3003

3004 getgname="(select gname from grp where rowid=$rowid)"

3005 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

3006 cm="?commission+$rowid"

3007 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

3008 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

3009 # team list

3010 hexteams=`hexteams "$grp"`

3011 if [ -n "$hexteams" ]; then

3012 echo "チーム一覧" | html h2

3013 echo '<div class="dumptable"><table class="b">'

3014 sq $db -html -header<<-EOF

3015 SELECT val TEAM,

3016 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

3017 MEMBERS

3018 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

3019 EOF

3020 echo '</table></div>'

3021 fi

3022 }

3023 grp_getbodyclass() {

3024 # Get css class name for document.

3025 # `moderated' for moderated groups

3026 # `ismember' for groups where user belongs

3027 # $1=GroupName (w/o quote)

3028 # $user=userNameCurrentlyLogin

3029 ## err grp_getbodyclass: 1="$1"

3030 qgrp=`sqlquote "$1"`

3031 query<<-EOF

3032 SELECT coalesce(

3033 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

3034 'open')

3035 ||

3036 CASE WHEN '$user'

3037 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

3038 THEN ' ismember'

3039 ELSE ''

3040 END;

3041 EOF

3042 }

3043 grpaction() { # $1=group-rowid

3044 err GRP_ACTION:IN

3045 grid=${1:-`getpar grp`}

3046 grp=`getgroupbyid "$grid"`

3047 htmlgrp=`echo "$grp" | htmlescape`

3048 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3049 if [ -z "$grp" ]; then

3050 echo "無効な指定です。" | html p; return

3051 fi

3052 if ! ismember $user "$grp"; then

3053 echo "加入者のみに許可された操作です。" | html p; return

3054 fi

3055 isowner=""

3056 isgrpowner "$user" "$grp" && isowner="yes"

3057 err 2=$2 3=$3

3058 case "$2" in

3059 get:teamcsv)

3060 teamcsv "$grid"

3061 return

3062 ;;

3063 esac

3064 echo "グループ $grp 個別選択操作" \

3065 | _m4 -D_TITLE_="syscmd(\`cat')" \

3066 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

3067 $layout/html.m4.html

3068

3069 usel=`getpar usel`

3070 if [ -n "$usel" ]; then

3071 uids=$(echo `echo $usel`|tr ' ' ',')

3072 ## err grpaction-1: grp=$grp, `echo $sql`

3073 text=`getpar text|tr -d '\r'`

3074

3075 rm=`getpar rm` cfm=`getpar confirm`

3076 ## err rm=$rm cfm=$cfm

3077 if [ x"$rm" = x"yes" ]; then

3078 if [ "$isowner" ]; then

3079 if [ x"$rm$cfm" = x"yesyes" ]; then

3080 # Eliminate

3081 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

3082 for tbl in grp_mem grp_mem_s grp_mem_m grp_adm grp_adm_s grp_adm_m

3083 do

3084 sql="delete from $tbl $cond;"

3085 # echo "sql=$sql"

3086 query "$sql"

3087 err rmGRPuser "$sql"

3088 done

3089 num=`query "select count(*) from user where rowid in ($uids);"`

3090 #err num=$num

3091 if [ 0$num -gt 0 ]; then

3092 sql="select coalesce(b.val,a.name) from user a left join \

3093 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

3094 # err `echo "$sql"`

3095 html pre<<EOF

3096 以下の${num}名のグループ $grp 登録を解除しました。

3097 `query "$sql"`

3098 EOF

3099 fi

3100 else

3101 echo "確認のチェックがないのでやめておきます。" | html p

3102 return

3103 fi

3104 else # not Group Owner

3105 echo "グループ管理者でないのでメンバー操作はできません。" | html p

3106 return

3107 fi

3108 cat<<EOF

3109

3110 EOF

3111 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

3112 if [ -z "$text" ]; then # if msg is empty

3113 echo "なにかメッセージを..." | html p

3114 return 0

3115 fi

3116 gecos=`gecos $user`

3117 safegc=`echo "$gecos" | tr -d '<>@,'`

3118 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3119 fromad=`myemail4group "$grp"`

3120 ###mail_from="$safegc <$fromad>"

3121 mail_from="$safegc <$user>" # TEST: 2020/5/13

3122 test -n `getpar sender` &&

3123 export SENDER=$user # TEST: 2020/5/15

3124 replyto=$fromad

3125

3126 ## Start parse of attachment files

3127 if [ -n "`getpar email`" ]; then

3128 ar=`getpar supprcpt`

3129 if [ -n "$ar" ]; then

3130 for a in $ar; do

3131 if checkdomain "$a"; then

3132 supprcpt="$supprcpt $a"

3133 else

3134 err "SupprcptErr=[$a] by $user"

3135 removercpt="$removercpt $a"

3136 fi

3137 done

3138 fi

3139 subj=`getpar subject`

3140 afiles=""

3141 for fn in `query "SELECT DISTINCT val FROM par WHERE var='image' AND sessid='$session';"`

3142 do

3143 f=$tmpd/$fn

3144 if [ -s $f ]; then

3145 afiles=$afiles"${afiles:+ }$f"

3146 fi

3147 done

3148 else

3149 preface=$(cat <<-EOF

3150 $url

3151 のグループ「$grp」のメンバーである $gecos さんから、

3152 あなた宛のメッセージです。

3153 ----------------------------------------------------------

3154 EOF

3155 )

3156 fi

3157 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

3158 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

3159 subj="${subj:-$gecos さんからのメッセージ}"

3160 REPLYTO=$replyto

3161 MAIL_FROM=$mail_from

3162 export REPLYTO SMAIL_TO MAIL_FROM

3163 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

3164 for r in $rcpts; do

3165 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

3166 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

3167 else

3168 # Show sender and rcpts address for guest

3169 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

3170 fi

3171 if [ -n "$afiles" ];then

3172 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

3173 else

3174 smail "$r" "$subj"

3175 fi <<EOF

3176 ${preface:+$preface$nl}$text

3177 EOF

3178 done

3179 if [ $? = 0 ]; then

3180 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

3181 sql="select coalesce(b.val, a.name) from

3182 (select name from user where rowid in ($uids)) a

3183 left join user_s b on a.name=b.name and b.key='gecos';"

3184 html pre<<EOF

3185 `query "$sql"`

3186 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

3187 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

3188 EOF

3189 err SendDone: `echo $sql`

3190 fi

3191 elif [ x"$rm" = x"commission" ]; then

3192 grp_reg_adm $grid $usel

3193 elif [ x"$rm" = x"addteam" ]; then

3194 team=`getpar team|sed "s/'/''/g"` # for single quotation

3195 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

3196 if [ x"$team" != x"$newteam" ]; then

3197 echo "チーム名に使えない文字を除去しました" | html p

3198 team=$newteam

3199 fi

3200 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

3201 cat<<-EOF | html p

3202 有効なチーム名を入力してください。

3203 カンマだけ、「なし」という名前は使えません。

3204 EOF

3205 echo "有効なチーム名を入力してください。" | html p

3206 else

3207 grp_add_team $grid "$team" $usel

3208 fi

3209 elif [ x"$rm" = x"rmteam" ]; then

3210 if [ x"yes" = x"`getpar teamconfirm`" ]; then

3211 rmteam=`getpar rmteam|sed "s/'/''/g"`

3212 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

3213 gname='$grp' AND user='$user' AND key='team'\

3214 AND val='$rmteam';\"`" ]; then

3215 grp_rm_team $grid "$rmteam" $usel

3216 else

3217 echo "所属していないチームの除去操作はできません。"|html p

3218 fi

3219 else

3220 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

3221 fi

3222 fi

3223 fi

3224 # POST count summary

3225 from=`getpar from`; to=`getpar to`

3226 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3227 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3228 fromtonote="<p title=\"Count the Number of Posts from-to\">POST/ACCESS集計: $from_input - $to_input</p>"

3229 # New entry

3230 sql="WITH mems AS (

3231 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3232 ON gm.user=g.name

3233 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3234 ), target_article AS (

3235 SELECT id FROM article_s

3236 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3237 ), posts AS (

3238 SELECT author, count(author) post

3239 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3240 WHERE blogid IN (SELECT id FROM blog_s

3241 WHERE key='owner'

3242 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3243 AND key='text'

3244 GROUP BY author

3245 ), teams AS (

3246 SELECT user, group_concat(val, ', ') team

3247 FROM mems m LEFT JOIN grp_mem_m gm ON m.name=gm.user

3248 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3249 AND key='team'

3250 GROUP BY user

3251 ), user_post AS (

3252 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3253 FROM mems m LEFT JOIN posts

3254 ON m.name=posts.author

3255 GROUP by m.rowid

3256 ), user_view AS (

3257 SELECT user vuser,count(user) cnt

3258 FROM tblaccesses

3259 WHERE user IN (

3260 SELECT user FROM grp_mem

3261 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid))

3262 AND tbl='blog'

3263 AND tblrowid IN (

3264 SELECT rowid FROM blog

3265 WHERE id IN (

3266 SELECT id FROM blog_s

3267 WHERE key='owner' AND val=(

3268 SELECT gname FROM grp WHERE rowid=$grid)))

3269 AND time BETWEEN '${from:-0000}' AND '${to:-9999}'

3270 GROUP BY user ORDER BY cnt

3271 )

3272 SELECT

3273 CASE

3274 WHEN (SELECT user FROM grp_adm

3275 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3276 AND user=up.name) IS NOT NULL

3277 then 'k'

3278 ELSE ''

3279 END || rowid || ','

3280 || rtrim(substr(name, 1, instr(name, '@')), '@') UID,

3281 gecos NAME,

3282 post POST,

3283 (coalesce((SELECT cnt FROM user_view WHERE vuser=name), 0)) ACCESS,

3284 team _TEAM_

3285 FROM user_post up LEFT JOIN teams t

3286 ON up.name=t.user

3287 ORDER BY up.name;"

3288 ## Want to count ACCESS with JOIN, but failed to produce user-unique rows

3289 ## err grpaction: "`echo \"$sql\"`"

3290 tf=$tmpd/title.$$

3291 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3292 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3293 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3294 for=\"cmadmin\">管理者委任</label>

3295 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3296 </p></div>"

3297 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3298 title=\"Shortcut: g${nl}Remove from the Group\"

3299 for=\"conf\">GRP登録解除</label>

3300 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3301 <p>この操作による通知は本人に行きません。

3302 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3303 </div>"

3304 submitBTN=`cgi_submit 送信`

3305 # Get team list to which current user belongs into $hexteams

3306 allhexteams=$(hexteams "$grp")

3307 if [ -n "$isowner" ]; then

3308 myhexteams="$allhexteams" # admin can remove all teams' attr

3309 else

3310 myhexteams=$(hexteams "$grp" "$user")

3311 fi

3312 if [ -n "$isowner" -a -n "$allhexteams" ]; then

3313 gettingcsv="<p>Download: <a href=\"?getteamcsv+$grid\">Team.csv</a> (Zoom Breakout Room 事前割り当てに使えます), <a href=\"?getteamcsv+$grid+name\">Team-with-name.csv</a>(参照用名前付)</p>"

3314 fi

3315 if [ -n "$myhexteams" ]; then

3316 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3317 title=\"Shortcut: s${nl}Strip a team tag from\"

3318 for=\"cmrmteam\">チーム属性除去</label>

3319 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3320 を除去します: `cgi_checkbox teamconfirm yes` 確認 $submitBTN

3321 <p>この操作による通知は本人に行きません。

3322 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3323 </div>

3324 "

3325 fi

3326 stf=$tmpd/selteam.$$

3327 cgi_select_h selteam "5445414d" $allhexteams > $stf

3328 b1='<label> <input type="checkbox" name="usel" value="'

3329 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3330 br='<span id="reverse" title="Reverse Selection"></span>'

3331 # lnk='"> <span></span></label> [<a href="?home+\3">HOME</a>]'

3332 lnk='<a href="?home+\3">\5</a>'

3333 # (1) Join <TR> line and the next

3334 # (2) (<TR><TD)>(k?)(1234),(userid)</TD><TD>(GECOS)</TD>

3335 # ↓

3336 # <TR><TD>\2<label><input ...value="\3">\4</label></TD> \

3337 # <TD><a href="?home+\3">\5</a></TD>

3338 cgi_form grpaction<<EOF \

3339 | sed -e '/^<TR>/{; N; s/\n//; }' \

3340 | sed -E \

3341 -e "s|^(<TR><TD>)(k?)([0-9]*),(.*)</TD><TD>(.*)</TD>|\1\2$b1\3\">\4</label></td><td>$lnk</TD>|" \

3342 -e 's/^(<TR><TD[^>]*>)k(<label)/\1\2 class="admin"/' \

3343 -e "s|^(<TR><TH>)(UID)|\1$br \2|" \

3344 | _m4 -D_TITLE_="spaste(\`$tf')" \

3345 -D_SUBTITLE_="チェック後操作ボタン" \

3346 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3347 $layout/form+dump.m4.html \

3348 | _m4 -D_TEAM_="spaste(\`$stf')"

3349 <p>下でチェックした人を対象として:</p>

3350 <div class="foldtabs">

3351 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3352 title="Shortcut: a${nl}Add a team tag to"

3353 for="cmteam">同じチーム属性を付与</label>

3354 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3355 `cgi_datalist_h teams $allhexteams`$submitBTN

3356 </div>

3357 ${rmteammsg}

3358 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3359 title="Shortcut: d${nl}DirectMail to"

3360 for="sendmsg" title="Direct Message">DM送信</label>

3361 <div>

3362 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3363 title="Using email format">email書式を使う</label>

3364 <div class="folded">

3365 <table>

3366 <tr><td>From: </td><td>$user</td></tr>

3367 <tr><td>このFrom:で送る</td>

3368 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3369 </td></tr>

3370 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3371 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3372 <tr><td>ファイル添付: </td>

3373 <td>`cgi_file image "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3374 </table>

3375 <p>(下記一覧から1人以上選択していない場合は送れません$submitBTN)</p>

3376 </div>

3377 <div>本文:`cgi_textarea text "" cols=72`

3378 </div>

3379 </div>

3380 ${isowner:+$cmmsg$excmsg}

3381 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3382 </div>

3383 <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote

3384 <table class="td3r td4r thl">

3385 `sq $db -header -html "$sql"`

3386 </table>

3387 `cgi_hidden grp $grid`

3388 `cgi_hidden myuid $myuid id="myuid"`

3389 EOF

3390 }

3391 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3392 # Create TEMPORARY VIEW

3393 query<<EOF

3394 CREATE TEMPORARY VIEW writeusers AS

3395 SELECT DISTINCT author FROM article

3396 WHERE id in (

3397 select id from article where blogid=(select id from blog where rowid=$1)

3398 );

3399 CREATE TEMPORARY VIEW movablegroups AS

3400 SELECT g.rowid growid , g.gname

3401 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3402 ON grp.gname=gm.gname -- そのユーザが属している

3403 AND user='$user') g -- グループに絞る

3404 WHERE (SELECT author FROM writeusers

3405 EXCEPT

3406 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3407 IS NULL;

3408 $2

3409 EOF

3410 }

3411 sql4readableblogs() {

3412 # Create view of blogs that can be readable by $user

3413 # Blog is readable when:

3414 # 1: blog owner is an user

3415 # 2: else, 2.1: owner-group where the $user belongs

3416 # 2.2: else, owner-group is not moderated

3417 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3418

3419 # $hidden_mode is defined in global section at head

3420

3421 cat<<EOF ## | tee tmp/sql.out

3422 CREATE TEMPORARY VIEW readableblogs AS

3423 SELECT blog.rowid rid, id, author

3424 FROM blog

3425 NATURAL JOIN

3426 (SELECT id,

3427 max(CASE key WHEN 'owner' THEN val END) owner,

3428 max(CASE key WHEN 'mode' THEN val END) mode

3429 FROM blog_s GROUP by id) bs

3430 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3431 THEN -- blog owner is an user, READABLE

3432 NOT mode IN $hidden_mode

3433 WHEN (SELECT val FROM grp_s

3434 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3435 AND

3436 (SELECT user FROM grp_mem

3437 WHERE gname=bs.owner AND user='$user') IS NULL

3438 THEN 0

3439 WHEN mode IN $hidden_mode

3440 THEN 0 -- "quiz" mode blog cannot be searched

3441 ELSE 1

3442 END;

3443 EOF

3444 }

3445 mvteamform() {

3446 owner=$1

3447 hexteams=$(hexteams "$owner" "$user")

3448 test -z "$hexteams" && return

3449 none="`echo なし|hexize`"

3450 cat<<-EOF

3451 <!-- <div class="fold">

3452 `cgi_checkbox mv2team send id="mv2team"`<label

3453 for="mv2team">この話題を以下のチームのものにする</label>

3454 <div> -->

3455 <p>この話題をチーム所有にする:

3456 チーム: `cgi_select_h mv2team $none $hexteams`</p>

3457 </form></div></div>

3458 EOF

3459 }

3460 editheading() { # $1=rowid-of-heading

3461 rowid=${1%%[!A-Z0-9a-z_]*}

3462 if [ -z "$rowid" ]; then

3463 echo "話題番号が未指定です。" | html p

3464 return

3465 fi

3466 owner=`getvalbyid blog owner $rowid`

3467 title=`getvalbyid blog title $rowid`

3468 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3469 | _m4 -D_TITLE_="修正" \

3470 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3471 -D_BLOGS_="" -D_DUMPTABLE_="" \

3472 -D_FORM_="syscmd(\`cat')" \

3473 $layout/html.m4.html $layout/form+dump.m4.html

3474 # Move to group

3475 if isuser "$owner"; then

3476 crview4article $rowid

3477 n=`query "SELECT count(*) FROM writeusers;"`

3478 ## err N=$n

3479 if [ $((n)) -gt 0 ]; then

3480 ## err ROWID=$rowid

3481 sql="SELECT growid || ':' || gname FROM movablegroups;"

3482 cat<<-EOF

3483 <div class="fold">

3484 `cgi_checkbox mv send id="mv"`<label

3485 for="mv">この話題をグループ所有に移動する</label>

3486 <div>

3487 <form action="?mvart" method="POST" enctype="multipart/form-data">

3488 移動先グループ:

3489 <select name="mv2grp">

3490 EOF

3491 query ".mode html"

3492 query<<-EOF |

3493 $sql

3494 .mode list

3495 EOF

3496 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3497 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3498 cat<<-EOF

3499 </select>

3500 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3501 そのグループに加入しているものに限られます)</p>

3502 <p>`cgi_checkbox cfm yes`<label>確認

3503 (この操作は元に戻すことができません)</label></p>

3504 `cgi_hidden blogrowid $rowid`

3505 `cgi_submit 移動`

3506 `cgi_reset Reset`

3507 </form>

3508 </div>

3509 </div>

3510 EOF

3511 fi

3512 # end of isuser "$owner"

3513 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3514 [ -n "$hexteams" ];}; then

3515 none="`echo なし|hexize`"

3516 cat<<-EOF

3517 <div class="fold">

3518 `cgi_checkbox mv2team send id="mv2team"`<label

3519 for="mv2team">この話題を以下のチームのものにする</label>

3520 <div><p>現在の所属チーム設定:

3521 `query "SELECT

3522 coalesce((SELECT val FROM blog_s

3523 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3524 AND key='team'),

3525 ':なし');"`</p>

3526 <form action="?mvart" method="POST" enctype="multipart/form-data">

3527 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3528 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3529 `cgi_hidden blogrowid $rowid`<br>

3530 `cgi_submit 移動`

3531 `cgi_reset Reset`

3532 </form></div></div>

3533 EOF

3534 fi

3535 }

3536 mvart() { # move diary to some group or team

3537 # or move blog of group to team which belong to the group

3538 blogrowid=`getpar blogrowid`

3539 cfm=`getpar cfm`

3540 ##### echo move blog:$blogrowid to $mv2grp | html p

3541 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3542 . ./s4-blog.sh

3543 if [ -z "$blogrowid" ]; then

3544 echo "無効な指定です(mvart)。" | html p

3545 return

3546 elif [ x"$cfm" != x"yes" ]; then

3547 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3548 elif { mv2grp=`getpar mv2grp`

3549 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3550 [ -n "$mv2grp" ]; }; then

3551 crview4article $blogrowid

3552 ########## TRANSACTION BEGIN

3553 query "BEGIN;"

3554 n=`query "SELECT count(*) FROM writeusers;"`

3555 ## err Nwriteuser=$n

3556 if [ $((n)) -gt 0 ]; then

3557 query<<-EOF

3558 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3559 WHERE key='owner'

3560 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3561 AND $mv2grp IN (SELECT growid FROM movablegroups);

3562 EOF

3563 fi

3564 query "END;"

3565 ########## TRANSACTION END

3566 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3567 [ -n "$mv2team" ];}; then

3568 # blog owner can move it to ANY team

3569 case "$mv2team" in

3570 'なし')

3571 cat<<-EOF

3572 DELETE FROM blog_s

3573 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3574 AND key='team';

3575 EOF

3576 ;;

3577 "") ;;

3578 *)cat<<-EOF

3579 BEGIN;

3580 REPLACE INTO blog_s(id, key, val)

3581 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3582 'team', '$mv2team');

3583 REPLACE INTO blog_s(id, key, val)

3584 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3585 'notify', 'all'); -- Change notify to all

3586 END;

3587 EOF

3588 esac | query

3589 fi

3590 blog_reply $blogrowid

3591 echo yes | html p

3592 }

3593 editart() { # $1=article-rowid $2=blogrowid

3594 rowid=${1%%[!A-Z0-9a-z_]*}

3595 blogrowid=${2%%[!A-Z0-9a-z_]*}

3596 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3597 echo "表示する記事番号が未指定です。" | html p

3598 return

3599 fi

3600 owner=`getvalbyid blog owner $blogrowid`

3601 title=`getvalbyid blog title $blogrowid`

3602 author=`getvalbyid article author $rowid`

3603 math=`getvalbyid blog mathjax $blogrowid`

3604 ## err EDITart: owner=$owner, author=$author

3605 if isgrpowner "$user" "$owner"; then

3606 : EDIT OK

3607 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3608 echo "本人か所有者しか編集できません." | html p

3609 return

3610 fi

3611 aid=`query "select id from article where rowid=$rowid;"`

3612 tmpout=$tmpd/editart.$$.out

3613 GF_ACTION="?replyblog+$blogrowid#$aid" \

3614 edittable $formdir/article.def article $rowid \

3615 > $tmpout

3616 printf '%s' "${math:+$mathjax}" >> $tmpout

3617 # Cannot use pipelining to m4 with genform() because of stdin stack

3618 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3619 -D_FORM_="syscmd(cat $tmpout)" \

3620 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3621 -D_BLOGS_= -D_DUMPTABLE_= \

3622 $layout/html.m4.html $layout/form+dump.m4.html

3623 }

3624 send2mem() {

3625 rowid=`getpar grp`

3626 rowid=${rowid%%[!0-9]*} # Cleaning

3627 if [ -z "$rowid" ]; then

3628 echo "グループが未指定です。" | html p

3629 return

3630 fi

3631 message=`getpar message`

3632 if [ -z "$message" ]; then

3633 echo "文章を入れてください。" | html p

3634 return

3635 fi

3636 grp=`getgroupbyid $rowid`

3637 members=`collectemail "$grp"`

3638 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3639 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3640 mailfrom="`gecos "$user"` <$mailfrom>"

3641 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3642 msg=$(cat<<-EOF

3643 $urlbase?grp+$rowid

3644 グループ $grp に所属する

3645 `gecos $user` さんよりメッセージ:

3646

3647 $message

3648 EOF

3649 )

3650 # smail rcpt subj (file)

3651 for m in $members; do

3652 echo "$msg" |

3653 MAIL_FROM=$mailfrom \

3654 SENDER=$noreply \

3655 REPLYTO=$mailfrom \

3656 smail "$m" "$sj"

3657 done

3658 cat<<EOF

3659 <p>以下のユーザに送信しました。</p>

3660 <pre>

3661 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3662 </pre>

3663 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3664 EOF

3665 }

3666 joingrpadmit() {

3667 # $1=yes/no $2=session-key

3668 if [ -z "$2" ]; then

3669 echo "bye bye" | html p; return

3670 fi

3671 t_usr=`session=$2 getpar adduser`

3672 t_grp=`session=$2 getpar group`

3673 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3674 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3675 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3676 echo "無効な加入依頼です。" | html p

3677 echo "有効期限が切れたか、

3678 他の管理者がいる場合は処理済みの可能性があります。" | html p

3679 return

3680 fi

3681 if ! isgrpowner "$user" "$t_grp"; then

3682 echo "グループ管理者のみの機能です。" | html p; return

3683 fi

3684 case $1 in

3685 yes) joingrp "$t_grp" "$t_usr" yes ;;

3686 no) joingrp "$t_grp" "$t_usr" no ;;

3687 *)

3688 echo "無効な指定です($1)。" | html p

3689 return ;;

3690 esac

3691 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3692 rcpts="`getgroupadminmails "$t_grp"` $user"

3693 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3694 body="に

3695 $t_usr

3696 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3697 しました。"

3698 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3699 query "delete from session where id='$2';"

3700 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3701 }

3702

3703 joingrprequest() {

3704 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3705 jss="joingrp-`date +%s`-`genrandom 12`"

3706 gecos=`gecos "$user"`

3707 addsession $jss +${memoplimitdays}days

3708 grpadmins=`getgroupadmins "$1"`

3709 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3710 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3711 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3712 $url

3713 $user ${gecos:+($gecos)}さんから

3714 グループ $1

3715 に加入依頼がありました。

3716

3717 承認する:

3718 $urlbase?joingrpadmit+yes+$jss

3719

3720 白紙に戻す:

3721 $urlbase?joingrpadmit+no+$jss

3722 EOF

3723 echo "管理者に加入依頼を出しました。

3724 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3725 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3726 }

3727 joingrp() {

3728 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3729 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3730 if isgrpowner "$user" "$1"; then

3731 isowner="yes"

3732 elif [ -n "$5" ]; then

3733 isowner="yes"

3734 else

3735 isowner=""

3736 fi

3737 ## err jg:isgrpowner: isowner="$isowner"

3738 if [ -n "$isowner" ]; then

3739 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3740 elif [ x"$2" != x"$user" ]; then # if user is not login user

3741 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3742 return

3743 elif [ x"$3" = x"no" ]; then

3744 : # Do not pursue those who leave

3745 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3746 : # Member can change own email address for the joining moderated group

3747 else # adding user is $user itself

3748 case `getgroupattr "$1" regmode` in

3749 moderated)

3750 joingrprequest "$@" # Request only

3751 return

3752 ;;

3753 *)

3754 ;;

3755 esac

3756 fi

3757 qgname=`sqlquote "$1"`

3758 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3759 cond="where gname=$qgname and user='$2'"

3760 if [ x"$3" = x"yes" ]; then

3761 query "replace into grp_mem values($qgname, '$2');"

3762 # Notify joingrp to admin

3763 action="に加入しました。"

3764 if [ -n "$4" ]; then

3765 if msg=`emaildomaincheck "$4"`; then

3766 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3767 'string', '$4', NULL);"

3768 else

3769 echo $msg

3770 fi

3771 else

3772 query "delete from grp_mem_s $cond and key='email';"

3773 fi

3774 if [ -n "$5" ]; then # as ADMIN

3775 # Coming here means newly created group

3776 sql="select case\

3777 when (select count(*) from grp_mem where gname=$qgname)=1\

3778 then (select user from grp_mem\

3779 where gname=$qgname and user='$user')\

3780 else '' end; "

3781 err NewGrpChk: $sql

3782 if [ -n "`query \"$sql\"`" ]; then

3783 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3784 query "replace into grp_adm values($qgname, '$user');"

3785 fi

3786 fi

3787 else

3788 query "begin;

3789 delete from grp_mem $cond;

3790 delete from grp_mem_s $cond;

3791 delete from grp_mem_m $cond;

3792 delete from grp_adm $cond;

3793 delete from grp_adm_s $cond;

3794 delete from grp_adm_m $cond;

3795 end;"

3796 action="から脱退しました。"

3797 fi

3798 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3799 $url?grp+$grid

3800 $user (`gecos $user`)さんが

3801 グループ $1

3802 $action

3803 EOF

3804 }

3805 grp_add_team() (

3806 # $1=grp-rowid $2=team $3...=user-rowid(s)

3807 grp=`getgroupbyid $1`

3808 team=$2; shift; shift

3809 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3810 { echo "BEGIN;"

3811 for user; do

3812 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3813 '$grp',\

3814 (SELECT name FROM user WHERE rowid=$user),\

3815 'team', 'string', '$team');"

3816 done

3817 echo "END;"

3818 } | query

3819 )

3820 grp_rm_team() (

3821 # $1=grp-rowid $2=team $3...=user-rowid(s)

3822 grid=$1

3823 qgrp=$(sqlquote "`getgroupbyid $grid`")

3824 team=$2; shift; shift

3825 [ -z "$grid" -o -z "$team" ] && return

3826 { echo "BEGIN;"

3827 for user; do

3828 echo "DELETE FROM grp_mem_m\

3829 WHERE gname=$qgrp \

3830 AND user=(SELECT name FROM user WHERE rowid=$user)\

3831 AND key='team' AND val='$team';"

3832 done

3833 cat<<-EOF

3834 DELETE FROM blog_s

3835 WHERE rowid=(

3836 SELECT rowid

3837 FROM blog_s a

3838 WHERE key='team'

3839 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3840 AND NOT EXISTS (SELECT * FROM grp_mem_m

3841 WHERE key='team' AND val=a.val -- a.val=team

3842 AND gname = (SELECT val FROM blog_s b

3843 WHERE a.id=b.id AND key='owner')

3844 ));

3845 EOF

3846

3847 echo "END;"

3848 } | query

3849 )

3850 grp_reg_adm() {

3851 # $1=grp-rowid $2...=user-rowid

3852 grid=$1

3853 grp=`getgroupbyid "$1"`

3854 if [ -z "$grp" ]; then

3855 echo "無効なグループIDです" | html p; return

3856 fi

3857 if ! isgrpowner "$user" "$grp"; then

3858 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3859 fi

3860 shift

3861 for urid; do

3862 newadm=`query "select name from user where rowid=$urid;"`

3863 if [ -z "$newadm" ]; then

3864 echo "指定ユーザIDがおかしいようです。" | html p; return

3865 fi

3866 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3867 err ismember $newadm $grp

3868 if ismember $newadm "$grp"; then

3869 # OK, go ahead

3870 getgname="(select gname from grp where rowid=$grid)"

3871 query "replace into grp_adm values($getgname, '$newadm');"

3872 # confirm insertion

3873 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3874 if [ -n "`query \"$sql;\"`" ]; then

3875 echo "追加完了: $newadm" | html p

3876 else

3877 echo "追加失敗($1 $urid)" | html p

3878 fi

3879 fi

3880 showgroup $grid

3881 done

3882 }

3883 dt_colhack() {

3884 # FROM: <TD>xxx:::yyy</TD>

3885 # TO: <TD class="xxx">yyy</TD>

3886 sed -Ee 's,<TD>([^:<"]+)'$asdelim'([^<]*)(</TD>|$),<TD class="\1">\2\3,g'

3887 }

3888 dt_rowhack() {

3889 # From: <TR>

3890 # ....

3891 # <TD>rowclass=foo</TD>

3892 # </TR>

3893 # To: <TR class="foo">....<TD>foo</TD></TR>

3894 sed -e '

3895 /^<TR>/ {

3896 :loop

3897 s/\n//

3898 N

3899 /<\/TR>/ {

3900 s/\n//

3901 s,^<TR>$.*$<TD$[^>]*$>rowclass=$.*$$</TD></TR>$,<TR class="\3">\1<TD\2>\3\4,

3902 n

3903 }

3904 $q

3905 b loop

3906 }'

3907 }

3908 dumptable() {

3909 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3910 # textのフィールドだけ全てダンプにしたほうがいいか

3911 # $DT_VIEW sets link

3912 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3913 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3914 VIEW=${DT_VIEW-replyblog}

3915 if [ -n "$VIEW" ]; then

3916 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3917 fi

3918 sqlfile=$tmpd/dump.sql

3919 : > $sqlfile # ensure to be empty

3920 printf '.mode html\n.header 1\n' > $sqlfile

3921 # $DT_CHLD=ChildTable:BindColumn (eg. article:blogid)

3922 if [ -n "$DT_CHLD" ]; then

3923 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3924 cat<<-EOF >> $sqlfile

3925 -- presql

3926 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3927 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3928 EOF

3929 # Speed up counting of new articles

3930 dt_count=${DT_COUNT:+"CREATE TEMPORARY VIEW _dtcount AS

3931 SELECT a.id $_i, $DT_COUNT, coalesce(cnt2, 0) cnt2

3932 FROM (SELECT DISTINCT id FROM _target) a

3933 LEFT JOIN

3934 (SELECT blogid,$DT_COUNT,count($DT_COUNT) cnt2

3935 FROM $_t

3936 WHERE $DT_COUNT = '$DT_COUNT_BY'

3937 GROUP BY $_i,$DT_COUNT) b

3938 ON a.id=b.$_i

3939 ;"}

3940 cat<<-EOF >> $sqlfile

3941 -- presql2

3942 DROP TABLE IF EXISTS _counts;

3943 CREATE TEMPORARY TABLE _counts AS

3944 SELECT $_i, count($_i) cnt

3945 FROM $_t GROUP BY $_i;

3946 /* Prepare NEW count table */

3947 CREATE TEMPORARY TABLE _target AS

3948 SELECT b.rowid trowid, b.id

3949 FROM "$2" b JOIN "$2_s" s

3950 ON b.id=s.id AND s.key='owner'

3951 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3952

3953 DROP TABLE IF EXISTS _children;

3954 CREATE TEMPORARY TABLE _children AS

3955 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3956 FROM (SELECT t.trowid, t.id $_i, a.id

3957 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3958 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3959

3960 DROP TABLE IF EXISTS _news;

3961 DROP VIEW IF EXISTS _news;

3962

3963 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3964 -- INSERT INTO _news

3965 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3966 CREATE TEMPORARY VIEW _news AS

3967 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3968 FROM (SELECT DISTINCT id FROM _target)

3969 a LEFT JOIN

3970 (SELECT $_i, count(ctime) newcnt

3971 FROM _children x

3972 WHERE ctime > coalesce((SELECT time from myacclog

3973 WHERE tblrowid=x.trowid),

3974 '1970-01-01')

3975 GROUP BY $_i) b

3976 ON a.id=b.$_i;

3977 $dt_count

3978 EOF

3979 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3980 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3981 cntmine=${DT_COUNT:+"(SELECT cnt2 FROM _dtcount WHERE $_i=a.id)"}

3982 cnt="$cntnew as '新着', $cntall as '総数', "

3983 cnt=$cnt${DT_COUNT:+"$cntmine as '$DT_COUNT_HEADER',"}

3984 dt_class=" td2r td3r td4r dumpblogs"

3985 fi

3986 # Construct join expression

3987 eav="" scols=""

3988 pk=`gettblpkey $2`

3989 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3990 substr=${substr:-%s}

3991 for col in ${3:-`gettbl_s_cols $2`}; do

3992 valvar=val

3993 fromtbl=b

3994 if gettblcols "$2" | grep -w "$col" >/dev/null 2>&1; then

3995 # If $col belongs to master table

3996 fromtbl=a; col=${col#a.}

3997 fi

3998 case $col in

3999 gecos) scols="$scols${scols:+, }${col#}"

4000 continue ;; # built-in column name

4001 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

4002 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

4003 case "$as" in

4004 *:*=*) cnd=${as#*:}

4005 h=${cnd%%=*} v=${cnd#*=}

4006 h=`sqlquotestr "$h"`

4007 v=`sqlquotestr "$v"`

4008 if [ x"$fromtbl" = x"b" ]; then

4009 valvar="CASE val WHEN $h THEN $v END"

4010 else

4011 valvar="$h"

4012 fi

4013 as=${as%%:*} ;;

4014 esac

4015 ;;

4016 *) as=${col} ;;

4017 esac

4018 ss=`printf "$substr" "$valvar"`

4019 if [ x"$fromtbl" = x"b" ]; then

4020 eav=$eav${eav:+,}" \"$as$asdelim\"||coalesce(max(case key when '$col' then $ss end), '') as $as"

4021 else

4022 eav=$eav${eav:+,}" \"$as$asdelim\"||$ss as $as"

4023 fi

4024 scols="$scols${scols:+, }${fromtbl}.$as"

4025 done

4026 #case author when '$user' then a.rowid else '---' end as ID,

4027 if [ -n "$DT_SQL" ]; then

4028 echo "$DT_SQL"

4029 else

4030 cat<<-EOF

4031 SELECT a.rowid as LINK, $cnt $scols

4032 FROM $2 a LEFT JOIN

4033 (SELECT $pk,$eav,

4034 max(CASE key

4035 WHEN 'owner'

4036 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

4037 as gecos

4038 FROM ${2}_s c GROUP BY $pk)

4039 b ON a.$pk=b.$pk $4;

4040 EOF

4041 fi >> $sqlfile

4042 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

4043 sqlog<<-EOF

4044 *** SQL-file: $sqlfile ***

4045 `cat $sqlfile`

4046 EOF

4047 printf '.mode list\n.header 0\n' >> $sqlfile

4048 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_colhack | dt_rowhack

4049 <div>

4050 <div class="dumptable">

4051 <table class="b$dt_class">

4052 `query ".read $sqlfile"`

4053 </table>

4054 </div>

4055 </div>

4056 EOF

4057 }

4058

4059 clean_orphaned() {

4060 # This shoud be done by foreign_key rules, but some db lack them

4061 query<<-EOF

4062 -- Find blogs that have no parent

4063 WITH orphanedblog AS (

4064 SELECT blog.id,val FROM blog JOIN blog_s bs

4065 ON blog.id=bs.id AND key='owner'

4066 WHERE val NOT IN (SELECT gname FROM grp)

4067 AND val NOT IN (SELECT name FROM user)

4068 ) -- Remove them

4069 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

4070

4071 -- Find articles that have no parent blog

4072 WITH orphanedarticle AS (

4073 SELECT id FROM article

4074 WHERE blogid NOT IN (SELECT id FROM blog)

4075 ) -- Remove them

4076 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

4077 EOF

4078 }

4079

4080 clearcachedir() (

4081 td=`getcachedir "$1"`

4082 err td=$td: ls- `ls $td`

4083 if [ -w "$td/image.1" ]; then

4084 err Removing td=$td

4085 rm -fr $td/ # Clear icon-image cache!

4086 fi

4087 )

4088

4089 par2table() (

4090 # copy current parameters of par into destination table

4091 # $1=definition-file

4092 # Using $user and $session

4093 # Return value:

4094 # 0: Stored successfully

4095 # 1: Insufficient fillings

4096 # 2: No permission to modify the record

4097 # 3: Invalid rowid

4098 # 4: SUCCESS to delete

4099 # 5: Stop deletion for lack of confirm check

4100 # 6: Password length too short

4101 # 7: Password mismatch

4102 # 8: Old password incorrect

4103 # 9: Duplicated post

4104 rowid=`getpar rowid`

4105 if [ ! -e $1 ]; then

4106 echo "テーブル定義ファイルが見付かりません" | html p

4107 exit 1

4108 fi

4109 tbl=${1%.def}

4110 tbl=${tbl##*/}

4111 if [ -n "$rowid" ]; then # Modify existing entry

4112 if [ x"$tbl" = x"user" ]; then

4113 rowowner=`query "select name from $tbl where rowid=$rowid;"`

4114 elif [ x"$tbl" = x"grp" ]; then

4115 sql="select gname from $tbl where rowid=$rowid;"

4116 ##err p2t:grp:q $sql

4117 isgrpowner "$user" "`query $sql`" && rowowner=$user

4118 elif [ x"$tbl" = x"blog" ]; then

4119 # Check if owner in blog_s

4120 blogowner=`getvalbyid blog owner "$rowid"`

4121 if isgrpowner "$user" "$blogowner"; then

4122 rowowner=$user

4123 origauthor=`getvalbyid blog author $rowid`

4124 if [ -n "$origauthor" -a x"$user" != x"$origauthor" ];

4125 then # Keep original author

4126 setpar author string "$origauthor" # if differs from $user

4127 fi # 2021-11-06 suggd.by Ruri

4128 else

4129 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4130 fi

4131 else

4132 # 2016-12-05 There's no owner column in $tbl (need confirmation)

4133 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4134 fi

4135 ### err rowowner=$rowowner

4136 if [ x"$user" != x"$rowowner" ]; then

4137 echo "他人のレコードはいじれないの" | html p

4138 return 2

4139 elif [ -z "$rowowner" ]; then

4140 echo "指定したレコードはないみたい" | html p

4141 return 3

4142 fi

4143 rm=`getpar rm` cfm=`getpar confirm`

4144 # Editing existent entry

4145 if [ x"$rm" = x"yes" ]; then

4146 if [ x"$rm$cfm" = x"yesyes" ]; then

4147 query "delete from $tbl where rowid=$rowid;"

4148 clearcachedir "$tbl/$rowid"

4149 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

4150 clean_orphaned

4151 fi

4152 return 4

4153 else

4154 echo "消去確認のチェックがないので消さなかったの..." | html p

4155 return 5

4156 fi

4157 fi

4158 fi

4159

4160 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

4161 if [ -n "$rowid" ]; then

4162 # Update of existing record

4163 for col in `gettblcols $tbl`; do

4164 val=`getparquote $col`

4165 [ -z "$val" ] && continue

4166 ## err query "update $tbl set $col=$val where rowid=$rowid"

4167 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

4168 sql="update $tbl set $col=$val where rowid=$rowid;"

4169 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

4170 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

4171 ## User name cannot be changed with interface provided with this

4172 ## script. But we offer the trigger to change owner user

4173 ## of blog_s table.

4174 #err "select quote($col) from $tbl where rowid=$rowid;"

4175 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

4176 cat<<-EOF | query

4177 -- Here we cannot use BEGIN-COMMIT because groupupdate()

4178 -- should use EXCLUSIVE transaction outside of this.

4179 SAVEPOINT par2table;

4180 $sql

4181 update blog_s set val=$val

4182 where key='owner' and val=$old;

4183 RELEASE SAVEPOINT par2table;

4184 EOF

4185 ## XX: DIRTY Hack Ends here

4186 ## We should keep blog's owner as a single column which has

4187 ## foreign key constraint with primary key of grp/user.

4188 else

4189 query "$sql"

4190 fi

4191 done

4192 # Then, set up $pval for further insertion of tbl_s and tbl_m

4193 for col in `gettblpkey $tbl`; do

4194 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

4195 pval="$pval${pval:+, }\"$val\""

4196 done

4197 else

4198 # New entry

4199 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

4200 # Avoid empty repost of article.

4201 if [ x"$tbl" = x"article" ]; then

4202 # If rowid is empty and ID exists in article-table, that is REPOST!

4203 aid=`getpar id`

4204 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

4205 if [ -n "$xaid" ]; then

4206 # REPOST of article

4207 html p <<-EOF

4208 書き込み直後のリロードなので上書きを回避します。

4209 最新記事は末尾の「再読み込み」ボタンから見てください。

4210 EOF

4211 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

4212 return 9 # STOP Duplicated posting

4213 fi

4214 fi

4215 # Generate values() for primary keys

4216 for col in `gettblpkey $tbl`; do

4217 # Genuine primary keys for _m and _s

4218 val=`getvalquote $tbl $col`

4219 [ -z "$val" ] && continue

4220 pval="$pval${pval:+, }$val"

4221 done

4222 ##err pval=$pval

4223 for col in `gettblfkey $tbl`; do

4224 # args for values() to insertion into master table

4225 val=`getvalquote $tbl $col`

4226 [ -z "$val" ] && continue

4227 formaster=$formaster"${formaster:+, }$val"

4228 done

4229 formaster="$pval${formaster:+, }$formaster"

4230 ## err formaster=$formaster

4231 if [ -z "$formaster" ]; then

4232 echo "項目を全て埋めてください" | html pre

4233 return 1

4234 fi

4235 ## err "replace into $tbl values($formaster);"

4236 query "replace into $tbl values($formaster);"

4237 ## Insertion to master table, done

4238 fi

4239

4240 transaction=$tmpd/sqlfile.sql; touch $transaction

4241 for kt in s m; do

4242 tb2=${tbl}_$kt

4243 for col in `gettbl_${kt}_cols $tbl`; do

4244 ptype=`getpartype $col "limit 1"`

4245

4246 # First, check update of existing entries in _m

4247 if [ $kt = m ]; then

4248 # sessID|address.1.22|string|Somewhere-x.y.z

4249 sql=""

4250 ##err dots from query "select var from par where var like '$col.%';"

4251 for v in `query "select var from par where var like '$col.%' AND sessid='$session';"`; do

4252 # v=address.1.22

4253 st_rowid=${v##*.}

4254 origcol=${v%%.*} # original column derived from

4255 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

4256 ##case `getpartype $v` in

4257 ## err CASE `gettbl_coltype $tbl/$origcol` in

4258 ## err edit flag = `getpar action.$v`

4259 case `getpar action.$v` in

4260 rm)

4261 if [ x`getpar confirm.$v` = x"yes" ]; then

4262 newsql="delete from $tb2"

4263 else

4264 echo "削除確認未チェック" | html p

4265 fi ;;

4266 edit)

4267 case `gettbl_coltype $tbl/$origcol` in

4268 image|document|binary)

4269 file=$tmpd/`getparfilename $v`

4270 if [ ! -s "$file" ]; then # Maybe stupid REPOST

4271 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

4272 continue

4273 fi

4274 ## err type=file=$file

4275 [ -z "$file" ] && continue

4276 bn=`sqlquotestr "${file##*/}"`

4277 bin="X'"$(hexize "$file")"'"

4278 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

4279 type=\"file:$ct\"

4280 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

4281 cachedir=`getcachedir "$tbl/$rowid"`

4282 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

4283 rm -rf $cachedir

4284 ;;

4285 *)

4286 newsql="update $tb2 set val=(select val from par where var \

4287 like '$col.%.$st_rowid' AND sessid='$session')"

4288 ;;

4289 esac

4290 ;;

4291 mv)

4292 # regularize filename and strip directory part

4293 newname=`getpar mv.$v|tr -d '":;#<>?^%$!'|tr -d "'"|tr ' ' _`

4294 newname=`basename $newname`

4295 oldname=`query "SELECT val FROM $tb2 WHERE rowid=$st_rowid;"`

4296 oldext=`expr "$oldname" : '.*\.$.*$'`

4297 newext=`expr "$newname" : '.*\.$.*$'`

4298 err "p2t(mv): oldname=$oldname $oldext -> newname($v)=$newname $newext"

4299 if [ -n "$newname" -a x"$oldext" = x"$newext" ];

4300 then

4301 newsql="UPDATE $tb2 SET val='$newname'"

4302 else

4303 html p<<-EOF

4304 $newname は取り扱えないファイル名です。

4305 空白を含まない名前にして下さい。

4306 拡張子の変更もできません。

4307 EOF

4308 continue

4309 fi

4310 ;;

4311 *) # maybe "keep", do not modify value

4312 continue

4313 ;;

4314 esac

4315 # err newsql=$newsql

4316 sql=$sql$nl"$newsql where rowid=$st_rowid;"

4317 clearcachedir "$tbl/$rowid"

4318 done

4319

4320 if [ x"$bin" = x"NULL" ]; then

4321 ## err repl:normal sql=`echo $sql`

4322 if [ -n "$transaction" ]; then

4323 cat<<-EOF >> $transaction

4324 $sql

4325 DELETE FROM $tb2 WHERE type='string' AND val='';

4326 EOF

4327 else

4328 query "$sql

4329 delete from $tb2 where type='string' and val='';"

4330 ## err repl:normal done

4331 fi

4332 else

4333 # Binary update line is TOO LONG to pipelining

4334 sqlfile="$tmpd/sqlf.$$"

4335 if [ -n "$transaction" ]; then

4336 cat<<-EOF >> $transaction

4337 $sql

4338 EOF

4339 else

4340 cat<<-EOF > $sqlfile

4341 $sql

4342 EOF

4343 query ".read $sqlfile"

4344 fi

4345 fi

4346 # Rest of kt==m: set multiple mode

4347 nr=`getparcount $col`

4348 else

4349 nr=1 # for kt==s, number of records is 1

4350 fi

4351

4352 i=0

4353 while [ $i -lt $nr ]; do

4354 limit="limit 1 offset $i"

4355 i=$((i+1)) # increase beforehand against continue

4356 val=`getvalquote $tbl $col "$limit"`

4357 ##XXX [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4358 [ -z "$val" -o x"$val" = x'""' ] && continue

4359 ## err $col=$val

4360 bin=NULL

4361 ## err partype$col=`getpartype $col "$limit"`

4362 ptype=`getpartype $col "$limit"` # partype should be obtained each time

4363 case $ptype in

4364 file) file=$tmpd/`getparfilename $col "$limit"`

4365 ## err parfile-$col=$file

4366 [ -z "$file" ] && continue

4367 bin="X'"$(hexize "$file")"'"

4368 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4369 type=\"file:$ct\" ;;

4370 "*"*) continue ;; # foreign table

4371 *) type=\"string\" ;;

4372 esac

4373 case `gettbl_coltype $tbl/$col` in

4374 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx]|[Tt][Ee][Xx][Tt])

4375 test x"$val" = x"NULL" && val="''"

4376 ;;

4377 password) # special care for password

4378 # name={password,pswd1,pswd2}

4379 p1=`getpar pswd1 "$limit"`

4380 if [ -z "$p1" ]; then

4381 continue # SKIP password setting, if p1 is empty

4382 else

4383 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4384 ## err pswd=$pswd

4385 if pwcheck "$pswd"; then

4386 if [ x"$p1" = x"$p2" ]; then

4387 case "$p1" in

4388 ??????????*) ;;

4389 *) echo "パスワードは10字以上にしてください。" | html p

4390 return 6;;

4391 esac

4392 val="\"`echo $p1|mypwhash`\""

4393 else

4394 echo "2つの新パスワード不一致" | html p

4395 return 7

4396 fi

4397 else

4398 echo "旧パスワード違います" | html p

4399 return 8

4400 fi

4401 fi

4402 ;;

4403 esac

4404 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4405 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4406 if [ x"$val" = x"NULL" -a x"$bin" = x"NULL" ]; then

4407 continue # Do not insert completely NULL record 2021-11-08

4408 fi

4409 sql="REPLACE into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4410 if [ x"$bin" = x"NULL" ]; then

4411 ## err Normal-query: `echo $sql`

4412 if [ -n "$transaction" ]; then

4413 printf '%s' "$sql" >> $transaction

4414 else

4415 query "$sql"

4416 fi

4417 else

4418 sqlfile="$tmpd/query.$$"

4419 ## err sqlfile=`ls -lF $sqlfile`

4420 if [ -n "$transaction" ]; then

4421 cat<<-EOF >> $transaction

4422 $sql

4423 EOF

4424 else

4425 cat<<-EOF >> $sqlfile

4426 $sql

4427 EOF

4428 query ".read $sqlfile"

4429 fi

4430 fi

4431 ## err p2t done

4432 done

4433 done

4434 done

4435 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4436 -- We cannot use transaction here, because groupupdate may use it.

4437 SAVEPOINT pa2table_insert;

4438 .read $transaction

4439 RELEASE SAVEPOINT pa2table_insert;

4440 EOF

4441 rc=$?

4442 [ $rc -eq 0 -a x"$tbl" = x"user" ] && flag_profupdate

4443 ## err "Table:$tbl update done "

4444 return $rc

4445 )

4446 genform() {

4447 # $1 = form definition file

4448 # $2, $3 (optional)= table name and ROWID

4449 # If $GF_VIEWONLY set and nonNull, output values without form

4450 # If $GF_ARGS set, use it as content-strings in the form

4451 # If $GF_OWNER set, use it as value of name="owner"

4452 # If $GF_STAGE set, use it as value of name="stage"

4453 forms="" hiddens="" rowid=$3

4454 if [ ! -e "$1" ]; then

4455 echo "そのようなデータベースはないようです($2)。" | html p

4456 return

4457 elif [ -n "$2" ]; then

4458 rec=`query "select * from $2 where rowid='$rowid';"`

4459 if [ -z "$rec" ]; then

4460 pk=`gettblpkey $2`

4461 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4462 rec=`query "select rowid from $2 where $pk='$rowid';"`

4463 rowid=$rec

4464 rec=$3

4465 fi

4466 if [ -z "$rec" ]; then

4467 echo "そんなレコードはないみたいね..." | html p

4468 return

4469 fi

4470 fi

4471 if [ -z "$GF_VIEWONLY" ]; then

4472 rm='<input id="rm" name="rm" type="checkbox"

4473 value="yes"><label for="rm">このエントリの削除</label>

4474 <span>ほんとうに消しますよ(確認)!

4475 <input name="confirm" type=checkbox value="yes">はい</span>'

4476 fi

4477 # Image Cache dir

4478 ## err genform: getcache=$2/$rowid

4479 td=`getcachedir "$2/$rowid"`

4480 while IFS=: read -r prompt name keytype type args; do

4481 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4482 sp="${args:+ }"

4483 form="" val=""

4484 if [ -n "$rowid" ]; then

4485 # err genform2a: Seeking for "$2.$name, type=$type"

4486 rawval=`getvalbyid $2 $name $rowid $td`

4487 val=`printf '%s\n' "$rawval"|htmlescape`

4488 ## err genform3a: getvalbyid $2 $name $rowid $td

4489 ## err genform3b: val="[$val]" type="$type"

4490 fi

4491 if [ -n "$GF_VIEWONLY" ]; then

4492 is_hidden "$2" "$name" && continue

4493 fi

4494 case "$type" in

4495 text*)

4496 cgiform=cgi_multi_$type

4497 if [ -s $td/$name.count -a -n "$val" ]; then

4498 form=`$cgiform $name $td`

4499 val=$(printf '%s\n' "$val"|

4500 while read fn; do

4501 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4502 </td></tr>$nl"

4503 done)

4504 val="<table>$nl$val$nl</table>"

4505 else

4506 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4507

4508 form=`cgi_$type $name "$rawval" "$args"`

4509 fi

4510 ;;

4511 [Rr][Aa][Dd][Ii][Oo])

4512 fh="<label><input type=\"radio\" name=\"$name\""

4513 form="`echo $args|sed -e \

4514 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4515 ;;

4516 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4517 checked=${val:+ checked}

4518 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\"$checked>${args%=*}</label>"

4519 ;;

4520 [Ss][Ee][Ll][Ee][Cc][Tt])

4521 fh="<select name=\"$name\">$nl"

4522 form=$(for l in $args; do

4523 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4524 done)

4525 if [ -n "$val" ]; then

4526 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4527 fi

4528 form="$fh$form</select>"

4529 ;;

4530 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4531 if [ -s $td/$name.count ]; then

4532 form=`cgi_multi_file $name $td "$args"`

4533 if [ -n "$val" ]; then

4534 hrfb="$myname?showattc+$2_m"

4535 val=$(echo "$rawval" \

4536 | while read fn; do

4537 data=`percenthex "$td/$fn"`

4538 #ct=`cat $td/$fn.content-type`

4539 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4540 ri=`cat "$td/$fn.rowid"`

4541 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4542 #imgsrc="<img src=\"data:$ct,$data\">"

4543 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4544 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4545 done)

4546 fi

4547 else

4548 form="<input type=\"file\" name=\"$name\" $args>"

4549 if [ -n "$val" ]; then

4550 imgs=$(echo "$rawval"\

4551 |while read fn;do

4552 data=`percenthex "$td/$fn"`

4553 echo "<img src=\"data:image/png,$data\">$fn<br>"

4554 done)

4555 form=$form"<br>$imgs"

4556 val=$imgs # 2015-06-15

4557 else

4558 form="<input type=\"file\" name=\"$name\" $args>"

4559 fi

4560 fi

4561 ;;

4562 [Hh][Ii][Dd][Dd][Ee][Nn])

4563 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4564 args="value=\"$GF_STAGE\""

4565 fi

4566 form="<input type=\"hidden\" name=\"$name\" $args>"

4567 prompt='' # Remove prompt

4568 ;;

4569 [Aa][Uu][Tt][Hh][Oo][Rr])

4570 [ -n "$GF_VIEWONLY" ] && continue

4571 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4572 prompt="" ;;

4573 [Oo][Ww][Nn][Ee][Rr])

4574 [ -n "$GF_VIEWONLY" ] && continue

4575 val=${GF_OWNER:-$val}

4576 val=${val:-$user}

4577 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4578 prompt="" ;;

4579 [Uu][Ss][Ee][Rr])

4580 # XXX: is null $user ok?

4581 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4582 [ -n "$GF_VIEWONLY" ] && continue

4583 form="$user"

4584 ;;

4585 [Pp]assword)

4586 [ -n "$GF_VIEWONLY" ] && continue

4587 form="`cgi_passwd`"

4588 val=""

4589 ;;

4590 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4591 [ -n "$GF_VIEWONLY" ] && continue

4592 if [ -z "$rowid" ]; then

4593 val=`genserial`

4594 fi

4595 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4596 prompt="" ;;

4597 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4598 prompt=""

4599 ;;

4600 parent|path|blog*)

4601 prompt=""

4602 ;;

4603 "*"*)

4604 tail=$tail"``"

4605 continue ;;

4606 esac

4607 if [ -n "$prompt" ]; then

4608 if [ -n "${GF_VIEWONLY}" ]; then

4609 form=$val

4610 else

4611 :

4612 fi

4613 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4614 else

4615 hiddens=$hiddens$nl"$form"

4616 fi

4617 done < $1

4618 # enctype="multipart/form-data"

4619 cat<<-EOF

4620 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4621 EOF

4622 test -n "$rowid" && printf '%s\n' "$rm" # Workaround for utf8 buggy NetBSD sh

4623 cat<<EOF

4624 <table class="b $2">

4625 $forms

4626 </table>$hiddens

4627 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4628 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4629 EOF

4630 if [ -z $GF_VIEWONLY ]; then

4631 cat<<EOF

4632 <input type="submit" name="sub" value="OK">

4633 <input type="reset" name="res" value="Reset">

4634 EOF

4635 fi

4636 cat<<EOF

4637 $GF_ARGS</form>

4638 $tail

4639 EOF

4640 }

4641 edittable() {

4642 # $1=form-def $2=table $3 rowid

4643 genform "$@"

4644 }

4645 viewtable() {

4646 GF_VIEWONLY=1 genform "$@"

4647 }

4648 showattc() {

4649 # $1=table_m $2=rowid &optional $3=RawFlag

4650 ## err \$1=$1 \$2=$2 \$3=$3

4651 if ! isfilereadable $user $1 $2; then

4652 contenttype; echo

4653 echo "このファイルは管理者のみしか見られません" | html p

4654 putfooter; exit

4655 fi

4656 idir=`umask 002; mktempd` || exit 1

4657 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4658 bin=$idir/$myname-$$.bin

4659 sql="select quote(bin) from $1 where rowid='$2';"

4660 ## err showattc: sql: $sql

4661 sq $db "$sql" | unhexize > $bin

4662 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4663 type=${tv%//*} fn=${tv#*//}

4664 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4665 ct=${type#file:}

4666 case $ct in # all text/* changed to text/plain

4667 text/*|application/csv|application/json)

4668 charset=`nkf -g $bin|cut -d' ' -f1`

4669 case $charset in

4670 ASCII*) charset="" ;;

4671 esac

4672 if [ -z "$3" ]; then

4673 ct="text/html${charset:+; charset=$charset}"

4674 link="?showattc+$1+$2+raw"

4675 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4676 | sed 's,^,<span></span>,' \

4677 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4678 -D_LINK_="$link" \

4679 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4680 exit $?

4681 fi

4682 ct="text/plain${charset:+; charset=$charset}"

4683 ;;

4684 video/*)

4685 if [ -z "$3" ]; then

4686 _m4 -D_TITLE_="$fn" \

4687 -D_SRC_="?showattc+$1+$2+raw" $layout/videoplay.m4.html

4688 exit $?

4689 fi

4690 esac

4691 contenttype "$ct"

4692 echo "Content-Disposition: filename=\"$fn\""

4693 echo "Content-Length: " `cat $bin | wc -c`; echo

4694 #echo "Content-Type: " ${type#file:}; echo

4695 cat $bin

4696 }

4697 #

4698 # Some default stupid handler on CGI values

4699 #

4700 default_storedb() {

4701 # ARG: $1=table-def-file

4702 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4703 tbl=`basename $1`

4704 tbl=${tbl%.def}

4705 cols="`grep :text $1|cut -d: -f2`"

4706 col=`echo "$cols"|head -1`

4707 vcol=`getpar $col`

4708 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4709 if [ -n "$vcol" ]; then

4710 par2table $1

4711 else

4712 return 2 # No insertion occurred

4713 fi

4714 }

4715

4716 default_view() { # $1=def-file

4717 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4718 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4719 default_storedb "$@"

4720 query "select rowid from $tbl order by rowid desc;" \

4721 | while read rowid; do

4722 viewtable $1 $tbl $rowid

4723 done | _m4 -D_TITLE_="$tbl" \

4724 -D_FORM_="`genform $1`" \

4725 -D_DUMPTABLE_="syscmd(cat)" \

4726 $layout/html.m4.html $layout/form+dump.m4.html

4727 }

4728 default_viewtext() { # $1=def-file

4729 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4730 default_storedb "$@"

4731 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4732 | _m4 -D_TITLE_="$tbl" \

4733 -D_FORM_="`genform $1`" \

4734 -D_DUMPTABLE_="syscmd(cat)" \

4735 $layout/html.m4.html $layout/form+dump.m4.html

4736 }

4737 default_smail() {

4738 default_storedb "$@"

4739 if [ $? -eq 2 ]; then

4740 _m4 -D_TITLE_="入力" \

4741 -D_FORM_="`genform $1`" \

4742 -D_DUMPTABLE_="" \

4743 $layout/html.m4.html $layout/form+dump.m4.html

4744 return

4745 fi

4746 cond=""

4747 for pk in `gettblpkey $tbl`; do

4748 pv=$(sqlquote "$(getpar $pk)")

4749 cond="$cond${cond:+ and }$pk=$pv"

4750 done

4751 sql="select rowid from $tbl where $cond;"

4752 rowid=`query "$sql"`

4753 ## err smail1 - "$sql" "-> rowid=$rowid"

4754

4755 while IFS=: read prompt name keytype type args; do # Read from $1

4756 val=`getpar $name`

4757 if [ -n "$val" ]; then

4758 text="$text

4759 $prompt

4760 $name=$val

4761 ---------------------------------------------------------"

4762 fi

4763 case "$type" in

4764 image|document|file)

4765 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4766 fns=$(echo "$fn"|while read fn; do

4767 err mv $tmpd/$fn.orig $tmpd/$fn

4768 mv $tmpd/$fn.orig $tmpd/$fn

4769 rm $tmpd/$fn.rowid # Remove cache flag

4770 ## err "`ls $tmpd/$fn`"

4771 echo $fn

4772 done)

4773 files="$files $fns"

4774 ;;

4775 esac

4776 done < $1

4777 ## err FILES=$files "`ls -lF $tmpd`"

4778 subj="from ${REMOTE_ADDR}"

4779 (echo "$url"

4780 echo "への書き込みがありました。"

4781 echo "------"

4782 echo "$text"

4783 ) | (cd $tmpd &&

4784 err LS="`ls -lF`" &&

4785 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4786 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4787 echo "以下の内容で送信しました。" | html p

4788 viewtable $1 $tbl \

4789 `query "select rowid from $tbl order by rowid desc limit 1;"`

4790 echo "戻る" | html a "href=\"?\""

4791 }