s4: ab6bb3efd40e s4-funcs.sh

s4

view s4-funcs.sh @ 823:ab6bb3efd40e

Showall link at the bottom points to also bottom

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 19 Jun 2020 15:32:57 +0900
parents	ed2c8aab742d
children	e9817a71f332

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 db=${DB:-$dbdir/cgi.sq3}

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

37 userupdateflag=$dbdir/userupdate

38 sesstb=tmp.sess

39 workdb=$dbdir/tmpdata.sq3

40 listentlimit=${LISTENTLIMIT:-30}

41 listartlimit=${LISTARTLIMIT:-50}

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

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

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

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

46 templ=${TEMPL:-templ}

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

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

49 imgdir=${IMGDIR:-img}

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

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

52 msgdir=$templ/msg

53 timeout="+2 days"

54 memoplimitdays="7"

55 dumpcollen=22

56 #thumbxy=120x120

57 thumbxy=96x96

58 iconxy_S=80x80

59 iconxy_M=400x400

60 maximagexy=1600x1600

61 ### maximagexy=400x400

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

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

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

65 file_accept='accept=".jpg,.jpeg,.gif,.png,.tiff,.pdf,.odt,.ods,.odp,.odg,.mp3,.mp4,.avi,.ogg,.mov,.webm,.gpx,.json,.geojson,.kml,.html,.css,.rb,.c,.txt,.zip,.xcf,.bz2,.gz,.xz,.7z,.csv,.dat"'

67 file_accept_help="

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

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

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

71 "

72 file_warn="$file_accept_help

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

74 blogreadflagrowid=0

75 blogcutoffflagrowid=-1

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

77 whatsnewdays=${WHATS_NEW_DAYS:-14}

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

79 session=$main_session

81 tconfs=""

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

83 conftbl=_tblconf

84 nl="

85 "

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

87 iconcachekey="profimgcache_S"

89 # Start debug logging

90 logtag="${S4WORLD:+{$S4WORLD\}}"

91 exec 3>> $logdir/debug.out

92 err() {

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

94 }

95 case "$HTTP_USER_AGENT" in

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

97 *) touchpanel="" ;;

98 esac

100 # If S4MASTERDB is set, behave in another world

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

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

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

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

105 # Variables set in s4-world.sh

106 # $S4WORLDS, $S4WROLDNAME, $S4WORLDGRPS

107 # Files created in s4-world.sh

108 # $worldlistfile, $worldoptionfile, $worldnamefile, $worldgrpfile

109 fi

110

111

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

113

114 : <<EOF

115

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

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

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

119 レポート提出管理

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

121

122 [2016]

123 7/12 根本への反省

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

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

126

127 [以下2015]

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

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

130 Web

131 締切設定

132

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

134 ○自分の提出物リスト

135

136 7/19 ○設置

137 ○一斉送信

138 ○getparfilename の tmpd の扱い

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

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

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

142 ○添付ファイル回収

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

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

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

146

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

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

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

150 ○レポート提出モード

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

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

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

154

155

156 7/9 ○管理者の追加

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

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

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

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

161

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

163

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

165 ○グループ検索

166

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

168

169

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

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

172

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

174 ○6/1 par2tableを triplex 対応に

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

176 →とすると一気に

177

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

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

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

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

182

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

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

185 ## 1. passwd

186 ## 2. grp

187 ## 3. grp_mem

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

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

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

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

192

193 ## ● listの定義:

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

195 ## ● hashの定義:

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

197

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

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

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

201 ## csv2sq3 で .csv.sq3 の Makefile

202

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

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

205 ## type := root | comment

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

207 ## unique(id, filename)

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

209

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

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

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

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

214 ## できたー!

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

216 ↓

217 ↓以下に変更

218 with recursive allmem as

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

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

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

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

223

224

225 with recursive allmem as

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

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

228 where allmem.val=grp_m.gname)

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

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

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

232

233

234 ## triggerもできた。

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

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

237

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

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

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

241 ## グループ管理

242 ## 個人情報管理

243 ##

244

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

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

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

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

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

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

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

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

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

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

255

256 ## ログテーブル

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

258

259

260

261 ■表設計

262 * 3つの表に分散管理

263 id格納表 + hash表 + list表

264 * *_s *_m

265

266

267

268 user, user_map, user_col

269

270 ■抽象エントリタイプ

271 * user

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

273 * group

274 権限判定に利用

275 * serial

276 自動idとして機能

277 * password

278 入力 type=passwordで入力

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

280 * session

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

282 * text

283 入力 type=text

284 * textarea

285 入力 textarea

286 * image|document

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

288 * owner

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

290 * gowner

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

292 * timestamp

293 datetime()

294 * parent

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

296 * path

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

298

299 格納タイプ

300 * list

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

302 * hash

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

304

305 オブジェクトタイプ

306 * entry

307 id, title, owner

308 * textpart

309 id, parentID, text

310 * binarypart

311 id, parentID, contenttype, filename, content

312 * content

313 hash(textpart), list(binarypart)

314 * topic

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

316 * reply

317 id, parentID, content

318 * blog

319 list(entry)

320 blog = [topic, list(reply)]

321

322

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

324 "text" => "hogehoge ..",

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

326 "author" => "taro",

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

328 "parent" => "/",

329 "path" => "/1",

330 "text" => "blah, blah, ....",

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

332 {"serial" => 2,

333 "author" => "hanako",

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

335 "parent" => "/",

336 "path" => "/2",

337 "text" => "blah, blah, ....",

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

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

340

341

342 user:=

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

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

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

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

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

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

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

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

351

352 変換表

353 /user/email=m

354

355 blog:=

356 シリアル:id:p:serial

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

358 所有者:owner:s:owner:

359 時刻:ctime:s:stamp:

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

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

362

363 article:=

364 シリアル:id:p:serial

365 筆者:author:s:owner

366 時刻:ctime:s:stamp:

367 参照元:parent:s:parent:

368 パス:path:s:path:

369 本文:text:s:textarea:

370 画像:image:m:image:

371

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

373

374

375 EOF

376

377 logstart() {

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

379 }

380 logend() {

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

382 }

383 sqlog() {

384 logstart

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

386 cat >> $querylog

387 else

388 echo "$*" >> $querylog

389 fi

390 logend

391 }

392 sq() {

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

394 logstart

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

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

397 else

398 echo "$@" >> $querylog

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

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

401 fi

402 logend

403 }

404 dbsetup() {

405 pipedir=$tmpdir/pipedir

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

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

408 suf=`date +%s`

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

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

411 mkfifo $sqi $sqo

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

413 sq $db < $sqi &

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

415 if $isCGI; then

416 exec 2>> $logdir/error.out

417 fi

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

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

420 rm $sqi

421 # Attach supplemental DB

422 cat >&5 <<-EOF

423 .output /dev/null

424 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

428 .output stdout

429 EOF

430 }

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

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

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

434 fi

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

436 }

437 cleanup() {

438 trap '' INT HUP EXIT TERM PIPE

439 echo .quit >&5

440 kill $sq3pid

441 kill $sq3pid

442 rm -f $sqo $sqi

443 rm -rf $tmpfiles

444 cleanup2

445 }

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

447 # simple redirection for the sake of speed.

448 query() {

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

450 echo ".output $sqo" >&5

451 logstart

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

453 tee -a $querylog

454 else

455 echo "$@" >> $querylog

456 echo "$@"

457 fi >&5

458 echo ".output stdout" >&5

459 cat $sqo

460 rc=$?

461 logend

462 return $rc

463 }

464 _m4() {

465 #S4NAME=f,f,f

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

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

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

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

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

471 }

472 ismember() {

473 # $1=user, $2=group

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

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

476 }

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

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

479 }

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

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

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

483 }

484 isgrpowner() (

485 # $1=user, $2=group

486 gn=`sqlquote "$2"`

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

488 err isgrpowner: $sql

489 test -n "`query $sql`"

490 )

491 isgrpownerbygid() (

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

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

494 err isgrpownerbygid: $sql

495 test -n "`query $sql`"

496 )

497 getgroupadminmails() {

498 # $1=group

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

500 email4group "$1" "$i" ;

501 done

502 }

503 getgroupadmins() { # $1=group

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

505 qgrp=`sqlquote "$1"`

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

507 }

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

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

510 getvalbyid grp $2 \

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

512 }

513 getgroupbyid() {

514 # $1=id|gname

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

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

517 # err ggbyid: `echo $sql`

518 query $sql

519 }

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

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

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

523

524 # Return true when anonymous mode

525 [ "$anonymousmode" ] && return 0

526 # case `getvalbyid blog mode $2` in

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

528 # *closed*)

529 # owner=`getvalbyid blog owner $2`

530 # if isgrp $owner; then

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

532 # elif isuser $owner; then

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

534 # fi

535 # esac

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

537 sql="with getblog as (

538 select key,val from blog_s where id=(

539 select blogid from article where id in

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

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

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

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

544 isgrpadm as (select user from grp_adm where

545 gname=(select val from getowner) and

546 user='$1'),

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

548 select case

549 when (select author from article where

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

551 then 'author'

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

553 then 'open'

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

555 then CASE

556 WHEN (SELECT val FROM isgrp) IS NULL

557 THEN

558 CASE WHEN (SELECT val from getowner)

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

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

561 ELSE ''

562 END

563 WHEN (select user from isgrpadm) IS NOT NULL

564 THEN 'i-am-admin'

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

566 END

567 when (select val from getmode) is null

568 then 'open'

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

570 then (SELECT user FROM isgrpadm)

571 when (select author from article where

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

573 then 'user+author'

574 else '' end;"

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

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

577 # result=`query "$sql"`

578 # err FileAccessibility=$result

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

580 }

581 linkhome() {

582 # $1=UserOrGroupRowid

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

584 if isuser $1; then

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

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

587 name=`gecos $1|htmlescape`

588 else

589 _grid=`numericalize "$1"`

590 echo -n "grp+$1"

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

592 fi

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

594 }

595 hreflink() {

596 # s4 specific notation:

597 # ^href=URL

598 # ^iframe=URL

599 # ^video=URL

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

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

602 # OSM umap Wikistyle Notation:

603 # [[URL]] - Simle Link

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

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

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

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

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

609 # Other Style

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

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

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

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

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

615 # SPC+SPC+$ - <br>

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

617 checkboxON="${cb} checked>"

618 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

643

644 }

645 minitbl() {

646 sed -n '

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

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

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

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

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

652 H; # Concat this line to HoldSpace

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

654 $ b cont

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

656 }

657 :cont

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

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

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

661 # . of ^. is workaround for FreeBSD sed

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

663 p; # Print whole "table" element

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

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

666 }

667 x; # Back to the newest line

668 p; # Print rest' | miniul

669 }

670 miniul() {

671 sed -e '

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

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

674 :top

675 s/\n//;

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

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

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

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

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

681 $ b cont

682 N; # 次の行を読む

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

684 /^ /b top

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

686 p; s/.*//;

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

688 /^\* /b top

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

690 }

691

692 :cont

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

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

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

696 H; x

697 }

698 x'

699 }

700 acclog() (

701 # $1=table, $2=rowid

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

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

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

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

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

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

708 fi

709 )

710 gecos() (

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

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

713 )

714 setpar() {

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

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

717 case "$1" in

718 user) _user="$v" ;;

719 skey) _skey="$v" ;;

720 esac

721 fi

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

723 }

724 unsetpar() {

725 for i; do

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

727 case "$i" in

728 user|skey) unset _$i ;;

729 esac

730 fi

731 query "DELETE FROM par WHERE var='$i';"

732 done

733 }

734 replpar() {

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

736 }

737 getpar() {

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

739 val=""

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

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

742 user) val=$_user ;;

743 skey) val=$_skey ;;

744 esac

745 fi

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

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

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

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

750 fi

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

752 case "$var" in

753 owner)

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

755 echo $user; return

756 elif ismember $user $val; then

757 echo $val; return

758 fi ;;

759 esac

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

761 echo "$val"

762 }

763 setskey() {

764 # For quick response...(?)

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

766 }

767 chkskey() {

768 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

774 return 0

775 fi

776 return 1

777 }

778 resetskey() {

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

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

781 fi

782 }

783 getpartype() {

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

785 }

786 getparcount() {

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

788 }

789 getparfilename() {

790 # null if type of $1 is not file

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

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

793 }

794 sqlquote() {

795 (v="$1"

796 case "$v" in

797 "") return ;; # null

798 "X'"*) # quoted hex string

799 echo $1 ;;

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

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

802 echo "\"$v\""

803 return ;;

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

805 echo "\"$v\""

806 return ;;

807 *)

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

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

810 else

811 echo "\"$v\""

812 fi ;;

813 esac)

814 }

815 sqlquotestr() (

816 case "$1" in

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

818 echo "'$v'" ;;

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

820 esac

821 )

822 mktempd() {

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

824 }

825 getcachedir() { # $1=maintable

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

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

828 else

829 echo $tmpd/$thumbxy

830 fi

831 }

832 getval() {

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

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

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

836 echo "$user" ;;

837 stamp|datetime)

838 date "+%F %T" ;;

839 serial)

840 (s=`getpar $2`

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

842 *)

843 getpar "$2" "$3";;

844 esac

845 }

846

847 getvalquote() {

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

849 (v=`getval "$@"`

850 case "$v" in

851 "") echo NULL ;;

852 *) sqlquote "$v" ;;

853 esac)

854 }

855 getparquote() {

856 sqlquote "`getpar $1`"

857 }

858 getbinbyid() {

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

860

861 }

862 getvalbyid() {

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

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

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

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

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

868

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

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

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

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

873 return

874 fi

875 done

876 rowid=$3

877 pk=`gettblpkey $1`

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

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

880 td=${4:-$tmpd}

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

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

883 for kt in s m; do

884 t=${1}_$kt

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

886 vcount=1 # count(val)

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

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

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

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

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

892 if [ $kt = m ]; then

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

894 # Reset val to store filenames if type is string

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

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

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

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

899 i=0

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

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

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

903 i=$((i+1))

904 fn=$c.$i

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

906 case $type in

907 file:*)

908 #file=$td/$val

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

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

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

912 # FOR SPEED: Skip file generation if imgcache exists

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

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

915 && continue

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

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

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

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

920 select rowid from $cond $slice;

921 .output '$td/$fn'

922 select val from $cond $slice;

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

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

925 .output stdout

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

927 EOF

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

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

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

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

932 ls -lh "$file" |

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

934 case "$type" in

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

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

937 *|*:[Aa]pplication*)

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

939 png:- > "$file"

940 ;;

941 esac

942 ;;

943 *)

944 sq $db<<EOF

945 .output $td/$fn.rowid

946 select rowid from $cond $slice;

947 .output $td/$fn

948 select val from $cond $slice;

949 EOF

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

951 ;;

952 esac

953 done

954 else

955 rm -f $td/$c.count

956 case $type in

957 file:*)

958 echo "$val" \

959 | while read fn; do

960 file=$td/$fn

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

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

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

964 | unhexize > "$file"

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

966 case $type in

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

968 *:[Aa]pplication*)

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

970 png:- > $file ;;

971 esac

972 fi

973 done

974 ;;

975 esac

976 fi

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

978 return

979 fi

980 done

981 done)

982 }

983 getvalbypkey() (

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

985 pk=`gettblpkey $1`

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

987 getvalbyid "$1" "$2" $rowid $4

988 )

989 getvalbycond() {

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

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

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

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

994 getvalbyid "$1" "$2" $rowid "$4"

995 fi

996 }

997 getpwfield() {

998 # getpwfield user column

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

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

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

1002 echo "$val"

1003 return 0

1004 else

1005 return 1

1006 fi

1007 }

1008 numericalize() {

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

1010 }

1011 encode() {

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

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

1014 sha1=sha1

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

1016 sha1=sha1sum

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

1018 sha1=gsha1sum

1019 fi

1020 fi

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

1022 }

1023 enjpeg() {

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

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

1026 cjpeg="cjpeg"

1027 else

1028 cjpeg="convert - jpeg:-"

1029 fi

1030 fi

1031 $cjpeg "$@"

1032 }

1033 mycrypt() (

1034 key=$1 salt=$2

1035 # err \$2=$2

1036 case $2 in

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

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

1039 esac

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

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

1042 )

1043 hexize() {

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

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

1046 hexize="xxd -p"

1047 else

1048 hexize_hd() {

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

1050 }

1051 hexize="hexize_hd"

1052 fi

1053 fi

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

1055 }

1056 unhexize() {

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

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

1059 unhex="xxd -p -r"

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

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

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

1063 EOF

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

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

1066 # world writable...:)

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

1068 fi

1069 fi

1070 cat "$@" | $unhex

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

1072 }

1073 percenthex() {

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

1075 }

1076 htmlescape() {

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

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

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

1080 }

1081 enascii() {

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

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

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

1085 else

1086 enascii_now=`date +%FT%T`

1087 enascii_sed() {

1088 nkf -Z0Z1Z2 \

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

1090 }

1091 enascii="enascii_sed"

1092 fi

1093 fi

1094 cat "$@" | $enascii

1095 }

1096 size_h() {

1097 i="$1" oi=$1

1098 set -- B B KB MB GB TB

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

1100 oi=$i

1101 i=$((i/1024))

1102 shift

1103 done

1104 echo ${oi}$1

1105 }

1106 gettblconf() {

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

1108 ## tconfs=`sq $db \

1109 tconfs=`query \

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

1111 fi

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

1113 }

1114 gettblkeys() {

1115 # $1=tbl

1116 gettblconf

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

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

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

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

1121 case $type in

1122 *p*)

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

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

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

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

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

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

1129 esac

1130 case $type in

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

1132 esac

1133 done

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

1135 }

1136 gettblpkey() {

1137 # $1=tbl

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

1139 }

1140 gettblfkey() {

1141 (x=`gettblkeys $1`

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

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

1144 }

1145 gettblcols() {

1146 (x=`gettblkeys $1`

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

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

1149 }

1150 gettbl_s_cols() {

1151 (x=`gettblkeys $1`

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

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

1154 }

1155 gettbl_m_cols() {

1156 (x=`gettblkeys $1`

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

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

1159 }

1160 gettbl_h_cols() {

1161 (x=`gettblkeys $1`

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

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

1164 }

1165 gettbl_coltype() (

1166 gettblconf

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

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

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

1170 )

1171 is_hidden() {

1172 # $1=Tbl $2=col

1173 gettblconf

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

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

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

1177 case $x in

1178 *h*) return 0 ;;

1179 *) return 1 ;;

1180 esac

1181 }

1182

1183 dbsetbyid() {

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

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

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

1187 quotedp=$(sqlquotestr "$p")

1188 unset primary update

1189 gettblconf

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

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

1192 #err conf=$conf

1193 case ${conf#*=} in

1194 p*) primary=1 ;;

1195 f*) update=1 ;;

1196 u*) ;;

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

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

1199 esac

1200 #err t=$t

1201 type=string fn=""

1202 case $conf in

1203 */password)

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

1205 ;;

1206 */image*|*/document*)

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

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

1209 ;;

1210 esac

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

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

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

1214 if [ "$primary" ]; then

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

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

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

1218 elif [ "$update" ]; then

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

1220 else

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

1222 fi

1223 )

1224 }

1225 expire() (

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

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

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

1229 )

1230 addsession() {

1231 # expireをセット

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

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

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

1235 # Remove old session parameters

1236 now=`expire now`

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

1238 }

1239 gencookie() (

1240 path=${URL#*:/}

1241 path=${URL%/*}

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

1243 for kv; do

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

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

1246 done

1247 )

1248 contenttype() {

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

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

1251 }

1252 putheader() {

1253

1254 }

1255 putfooter() {

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

1257 }

1258 getcookie() {

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

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

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

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

1263 case "$k" in

1264 user) _user="$v" ;;

1265 skey) _skey="$v" ;;

1266 esac

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

1268 done

1269 }

1270 genrandom() {

1271 # $1=columns (default: 10)

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

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

1274 }

1275 genserial() {

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

1277 }

1278 smail() {

1279 # smail rcpts subj (file)

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

1281 # $MAIL_FROM <- From: header value

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

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

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

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

1286 sender=${SENDER:-$admin}

1287 # Do not call m4 with directly passing text

1288 _r=$tmpd/rcpt

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

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

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

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

1293 }

1294 smail_queue_flush() {

1295 # $1=timelimit

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

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

1298 SELECT rowid FROM smailq

1299 GROUP BY rcpts, subj

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

1301 EOF

1302 )

1303 for rid in $rowids; do

1304 sq $workdb \

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

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

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

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

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

1310

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

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

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

1314 .separator "\n" "------------------\n\n"

1315 SELECT time, text FROM smailq

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

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

1318 ORDER by time;

1319 EOF

1320 then

1321 cat <<-EOF | sq $workdb

1322 DELETE FROM smailq

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

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

1325 EOF

1326 fi

1327 fi

1328 done

1329 }

1330 smail_queue() {

1331 # $1=Rcpts, $@=subj

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

1333 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1342 EOF

1343 )

1344 ## XXX: Adhoc load mitigation

1345 case "$now" in

1346 *[01])

1347 err flush_queue=$mintime

1348 smail_queue_flush ;;

1349 esac

1350 fi

1351 }

1352 setviastring() {

1353 table=par

1354 oifs="$IFS"

1355 IFS="&"

1356 for us in $1; do

1357 k=${us%%=*}

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

1359 setpar "$k" "string" "$v"

1360 done

1361 IFS="$oifs"

1362 }

1363 checkdomain() (

1364 # Check the validity of domain by referring DNS

1365 item=$1

1366 err checkdomain $1

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

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

1369 )

1370 pwcheck() {

1371 # $1=passwd

1372 dbpswd=`getpwfield $user pswd`

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

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

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

1376 }

1377 mypwhash() {

1378 mycrypt "`cat`" `genrandom 5`

1379 }

1380 wasureta() {

1381 user=$1

1382 if ! checkdomain $user; then

1383 contenttype; echo

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

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

1386 putfooter

1387 exit 0

1388 fi

1389 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1391 encpswd=`echo $newpswd|mypwhash`

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

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

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

1395 $msgdir/mail-newaccount.m4 \

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

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

1398 }

1399 checkauth() {

1400 user=`getpar user`

1401 skc=`getpar skey` # from cookie

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

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

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

1405 if chkskey "$skc"; then

1406 return 0

1407 fi

1408 fi

1409 pswd=`getpar pswd`

1410 quser=`sqlquotestr "$user"`

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

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

1413 return 4 # 4=server too heavy

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

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

1416 return 2 # 2=login fail

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

1418 wasureta "$user"

1419 return 1 # wasureta error

1420 fi

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

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

1423 if pwcheck "$pswd"; then

1424 newsession=`genrandom 34`

1425 if setskey "$user" "$newsession" &&

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

1427 gencookie "user=$user" "skey=$newsession"

1428 return 0

1429 else

1430 return 4 # Heavy load??

1431 fi

1432 fi

1433 err "Login failed: [$user]"

1434 return 2 # Password mismatch

1435 }

1436 showlogin() {

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

1438 test -z "$args" && resetskey

1439 s4name=${S4NAME:-s4}

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

1441 cat $layout/login.m4.html

1442 echo '</body></html>'

1443 ) | _m4 \

1444 -D_BODYCLASS_="login" \

1445 -D_TITLE_="$s4name" \

1446 -D_SYSNAME_="Welcome to $s4name" \

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

1448 exit 0

1449 }

1450 dologin() {

1451 test -n "$S4WORLD" && syncaccount

1452 checkauth

1453 st=$?

1454 if [ $st != 0 ]; then

1455 contenttype; echo

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

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

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

1459 showlogin # and EXIT

1460 fi

1461 }

1462

1463 # Do instant jobs here

1464 dbsetup

1465 trap cleanup INT HUP EXIT TERM PIPE

1466 # trap cleanup INT HUP

1467

1468

1469 cgiinit() {

1470 tmpd=`tmpd=$tmpdir mktempd`

1471 tmpf=$tmpd/stream.$$

1472 tmpfiles=$tmpfiles" $tmpd"

1473 addsession $session

1474 getcookie

1475 case "$REQUEST_METHOD" in

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

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

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

1479 # cat > $tmpf # too much?

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

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

1482 s="`cat $tmpf`"

1483 ;;

1484 esac

1485 case "$CONTENT_TYPE" in

1486 *boundary*)

1487 bndry=${CONTENT_TYPE#*boundary=}

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

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

1490 do

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

1492 #echo u=$us

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

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

1495 # err k=$k v=$v

1496 case "$k" in

1497 *:filename)

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

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

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

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

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

1503 :

1504 fi

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

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

1507 case "$mimetype" in

1508 [Ii]mage/x-xcf)

1509 bzip2 "$tmpd/$v"

1510 v=${v}.bz2

1511 ;;

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

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

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

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

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

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

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

1519 v=$vjpg

1520 else

1521 mimetype="Not supported"

1522 fi

1523 ;;

1524 [Ii]mage/*)

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

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

1527 ;;

1528 esac

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

1530 then

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

1532 continue

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

1534 replpar text string \

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

1536 continue

1537 fi

1538 ;;

1539 *)

1540 type='string'

1541 ;;

1542 esac

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

1544 setpar "$k" "$type" "$v"

1545 done

1546 ;;

1547 *)

1548 setviastring "$s"

1549 ;;

1550 esac

1551 }

1552 email4group() {

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

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

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

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

1557 sql="WITH

1558 grpemails AS (

1559 SELECT gname, user, val email

1560 FROM grp_mem NATURAL JOIN grp_mem_s

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

1562 useremails AS (

1563 SELECT user.name, val email

1564 FROM user

1565 LEFT JOIN user_m

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

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

1568 FROM useremails u LEFT JOIN grpemails g

1569 ON u.name=g.user

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

1571 query "$sql"

1572 }

1573 email4groupbyuid() {

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

1575 g=$1; shift

1576 uids=`echo "$@"`

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

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

1579 email4group "$g" `query "$sql"`

1580 }

1581 myemail4group() {

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

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

1584 }

1585 collectmembersbyid() {

1586 # Collect user names of group specified by grid

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

1588 query "SELECT user FROM grp_mem \

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

1590 }

1591 collectmembersbyid() {

1592 # Collect user names of group name

1593 qgrp=`sqlquote "$1"`

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

1595 }

1596 collectgecosesbyid() {

1597 # Collect user gecoses of group

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

1599 query<<-EOF

1600 SELECT gecos

1601 FROM gecoses

1602 WHERE name IN (SELECT user FROM grp_mem

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

1604 EOF

1605 }

1606 collectemail() (

1607 # Collect email addresses for group $1

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

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

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

1611 for e; do

1612 if isuser "$e"; then

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

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

1615 else

1616 qgrp=`sqlquote "$e"`

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

1618 gmem="grp_mem"

1619 else

1620 tm=`sqlquote "$TEAM"`

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

1622 fi

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

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

1625 $gmem g left join grp_mem_s s

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

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

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

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

1630 query "$sql"

1631 fi

1632 done

1633 )

1634 sendinvitation() (

1635 # $1=email

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

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

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

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

1640 gecos=`gecos`

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

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

1643 _m4 -D_URL_="$urlbase" \

1644 -D_USER_="$name" \

1645 -D_EMAIL_="$1" \

1646 -D_REGIST_="$regist" \

1647 -D_ADMIN_="$admin" \

1648 $msgdir/mail-invite.m4 \

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

1650 return 0

1651 )

1652 emaildomaincheck() {

1653 case "$1" in

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

1655 *@*)

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

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

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

1659 return 2

1660 fi

1661 return 0

1662 ;;

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

1664 esac

1665 }

1666 invite() {

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

1668 case "$email" in

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

1670 *@*)

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

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

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

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

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

1676 elif sendinvitation $email; then

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

1678 else # Cannot be reached here

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

1680 fi ;;

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

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

1683 esac

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

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

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

1687 fi

1688 if [ -f $invite_policy ]; then

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

1690 else

1691 pol="$invite_policy"

1692 fi

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

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

1695 -D_INVITE_POLICY_="$pol" \

1696 $layout/html.m4.html $layout/invite.m4.html

1697 }

1698 regist() {

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

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

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

1702 echo "bye bye" | html p

1703 reutrn

1704 fi

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

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

1707 cat<<EOF

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

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

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

1711 EOF

1712 return

1713 fi

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

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

1716 # Fake login password to wasureta

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

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

1719 wasureta $email

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

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

1722 addsession $1 # for removal after 1 minute

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

1724 $layout/login.m4.html

1725 return

1726 }

1727 group_safename() {

1728 # Convert $1 to safe group name

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

1730 }

1731 groupupdate() {

1732 gname=`getpar gname`

1733 qgname=`sqlquote "$gname"`

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

1735 # See ALSO same job in showgroup()

1736 newgname=`group_safename "$gname"`

1737 err newgname=$newgname

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

1739 err NewGNAME: gname=$newgname

1740 gname=$newgname

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

1742 replpar gname string "$gname"

1743 fi

1744 # Name confliction check

1745 parow=`getpar rowid`

1746 ## err parow=$parow

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

1748 query "BEGIN EXCLUSIVE;"

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

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

1751 if [ $count -gt 0 ]; then

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

1753 query "END;"

1754 return

1755 fi

1756 par2table $formdir/grp.def

1757 query "END TRANSACTION;"

1758 # Remove orphan

1759 : <<EOF

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

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

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

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

1764 EOF

1765 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1769 sql="delete from blog where id in

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

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

1772 query "$sql";

1773 unsetpar tag kwd

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

1775 return # and return

1776 fi

1777 fi

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

1779 fi

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

1781 grid=$(query $sql)

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

1783 grp $grid

1784 }

1785 groupclone() {

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

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

1788 case "$1" in

1789 */noteam)

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

1791 esac

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

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

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

1795 return

1796 fi

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

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

1799 return

1800 fi

1801 i=0

1802 while true; do

1803 copy="-copy$i"

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

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

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

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

1808 i=$((i++))

1809 continue

1810 fi

1811 break

1812 done

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

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

1815 query<<-EOF

1816 BEGIN;

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

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

1819 SELECT $newqname, key, val

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

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

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

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

1824 -- Copy members and their configuration --

1825 REPLACE INTO grp_mem SELECT $newqname, user

1826 FROM grp_mem WHERE gname=$qgrp;

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

1828 FROM grp_mem_s WHERE gname=$qgrp;

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

1830 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1831 -- Copy administrators --

1832 REPLACE INTO grp_adm SELECT $newqname, user

1833 FROM grp_adm WHERE gname=$qgrp;

1834 COMMIT;

1835 EOF

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

1837 STOPCLONEMSG=1 groupconf "$newrowid"

1838 }

1839 groupman() {

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

1841

1842 GF_STAGE="grpconf"

1843 GF_STAGE=groupupdate

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

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

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

1847 -D_DUMPTABLE_="syscmd(cat)" \

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

1849 }

1850 userconf() {

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

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

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

1854 }

1855 groupconf() {

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

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

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

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

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

1861

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

1863 grp=`getgroupbyid $rowid`

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

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

1866 return

1867 fi

1868

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

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

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

1872 ## Setup migration menu

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

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

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

1876 err v=$v

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

1878 migrate=$(cat<<-EOF

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

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

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

1882 <form action="?migrategrp">

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

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

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

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

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

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

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

1890 `cgi_hidden stage migrategrp`

1891 `cgi_hidden rowid $rowid`

1892 `cgi_submit OK`

1893 `cgi_reset Reset`

1894 </form>

1895 </div>

1896 EOF

1897 )

1898 fi

1899 fi

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

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

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

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

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

1905 <table>

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

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

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

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

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

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

1912 </table>

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

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

1915 </div>

1916 $migrate

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

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

1919 EOF

1920 fi

1921 }

1922 migrategrp() {

1923 rowid=`getpar rowid`

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

1925 grp=`getgroupbyid $rowid`

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

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

1928 return

1929 fi

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

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

1932 grp "$rowid"

1933 return

1934 fi

1935 destconf=`getpar migrateto`

1936 err destconf=$destconf

1937 if [ ! -e $destconf ]; then

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

1939 grp "$rowid"

1940 return

1941 fi

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

1943 srcconf=$worldconf

1944 else

1945 srcconf=s4-config.sh

1946 fi

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

1948 echo "移住操作" | html h1

1949 echo '<pre>'

1950 set -- "$srcconf" "$destconf" "$rowid"

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

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

1953 rc=$?

1954 echo "</pre>"

1955 if [ $rc -eq 0 ]; then

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

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

1958 clean-orphaned

1959 else

1960 echo "移住失敗" | html p

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

1962 fi

1963 return

1964 }

1965 mems() {

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

1967 kwd=`getpar kwd`

1968 listmember $kwd

1969 }

1970 grps() {

1971 case "$S4WORLD" in

1972 $nonewgroupworld) ;;

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

1974 esac

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

1976 kwd=`getpar kwd`

1977 listgroup $kwd \

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

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

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

1981 $layout/form+dump.m4.html

1982 }

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

1984 gpg=`getpar grp`

1985 grid=${1:-$gpg}

1986 grp=`getgroupbyid "$grid"`

1987 ## . ./s4-blog.sh

1988 jg=`getpar joingrp`

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

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

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

1992 fi

1993 htmlheader=$layout/html.m4.html

1994 showgroup "$grid"

1995 }

1996 sql4interestblogs() {

1997 cat<<EOF

1998 CREATE TEMPORARY VIEW interestblogs AS

1999 SELECT blog.rowid rid, id, author

2000 FROM blog

2001 NATURAL JOIN

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

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

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

2005 WHEN (SELECT user FROM grp_mem

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

2007 THEN 0

2008 ELSE 1

2009 END;

2010 EOF

2011 }

2012 listnewblogsql() { # $1=user

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

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

2015 newdays=${newdays:-14}

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

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

2018 WHERE user='$user'

2019 AND tblrowid IN

2020 ($blogreadflagrowid,

2021 $blogcutoffflagrowid)),

2022 $basetime -- "0"

2023 );"`

2024 cat<<EOF

2025 `sql4interestblogs`

2026 WITH article_ctime as (

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

2028 FROM article join article_s s using(id)

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

2030 GROUP BY id

2031 ), blog_title_owner as (

2032 SELECT blg.rid brid, id,

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

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

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

2036 ), blogall as (

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

2038 ), news as (

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

2040 coalesce(al.time, '$deftime') atime,

2041 count(bl.id) "新着", bl.author

2042 FROM blogall bl

2043 LEFT JOIN

2044 (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al

2045 ON bl.brid=al.tblrowid

2046 WHERE atime < bl.ctime

2047 GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id

2048 LIMIT 10

2049 ) SELECT brid LINK, "新着",

2050 (SELECT count(*) FROM article WHERE blogid=blid) "総数",

2051 ctime, title,

2052 (SELECT gecos FROM gecoses WHERE name=author) gecos

2053 FROM news;

2054 EOF

2055 }

2056

2057 search_form() {

2058 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

2059 # $2(optional) = pre-input keywords

2060 help="(1)空白区切りの単語で本文検索

2061 (2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索

2062 @2016-0[1-6] → 2016年1月から6月

2063 @>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間

2064 @week → 最近一週間

2065 (3)#番号で記事ID検索

2066 (1)と(2)は組み合わせOK

2067 例: @2016-10-0[1-9] 芋煮

2068 → 2016年10月上旬でキーワード「芋煮」を含む記事検索

2069 ※クイズ板は検索対象から外されます。"

2070 auth=""

2071 placeholder="全記事からの検索"

2072 case "$1" in

2073 author=*)

2074 a=`echo "${1#author=}"|htmlescape`

2075 g=`gecos ${1#author=}`

2076 auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"

2077 placeholder="このユーザの書込検索"

2078 help="★★ $g さんの書き込みから検索します$nl$help"

2079 ;;

2080 grid=*)

2081 a=`echo "${1#grid=}"`; a=$((0 + $a))

2082 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

2083 placeholder="このグループからの検索"

2084 ;;

2085 esac

2086 inikwd="$2" # no need to htmlescape

2087 cat<<-EOF

2088 <div class="right">

2089 <form action="$myname">$auth

2090 <input type="text" name="kwd" value="$inikwd" title="$help"

2091 placeholder=" $placeholder " width="10" accesskey="k">

2092

2093 ${touchpanel:+<p class="help">$help</p>}

2094 <input type="hidden" name="stage" value="searchart">

2095

2096 </form>

2097 </div>

2098 EOF

2099 }

2100

2101 imgsrc_cache() (

2102 # $1 = directory for cache'ing

2103 # $2 = table (user_m or grp_m)

2104 # $3 = keycond (was: condition for choosingowner)

2105 # $4 = size : S = Small, M = Medium, O = Original

2106 dir="$1" tbl="$2"

2107 keycond="$3"

2108 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

2109 ORDER BY rowid DESC LIMIT 1"

2110 [ -d "$dir" ] || mkdir -p "$dir"

2111 tmpf=$tmpd/imgsrc_cache.$$

2112 case "$4" in

2113 [Ss]) size=S ;;

2114 [Oo]) size=O ;;

2115 *) size=M ;;

2116 esac

2117 # ImageCache filename storing schema:

2118 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

2119 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

2120 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

2121 valtype=`query "$sql0"`

2122 filename=${valtype%%//*}

2123 filetype=${valtype##*//file:}

2124 if [ x"$filename" = x"${filename%.*}" ]; then

2125 # If nor filename extension found, set it to image type

2126 case "$filetype" in

2127 image/*) filename=$filename.${filetype#image/} ;;

2128 esac

2129 fi

2130 cacheimg_S=$dir/S_$filename

2131 cacheimg_M=$dir/M_$filename

2132 cacheimg_O=$dir/$filename

2133 cacheimg=$dir/${size}_$filename

2134 sumfile="$dir/$filename.sum"

2135 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

2136 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2137 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2138 # if cache is fresh and has the same checksum,

2139 echo "<img src=\"$cacheimg\">"

2140 else

2141 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2142 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2143 fifo2=$fifo.2

2144 mkfifo $fifo $fifo2

2145 fmt=${filename##*.}

2146 ## [[ NOTE ]]

2147 ## a. convert oldimage newimage

2148 ## b. convert oldimage fmt:- | convert - newimage

2149 ## b is much smaller than a

2150 cat $tmpf | unhexize \

2151 | tee $fifo \

2152 | convert -define ${fmt}:size=${iconxy_M} \

2153 -resize ${iconxy_M}'>' - ${fmt}:- \

2154 | tee $fifo2 \

2155 | convert - "$cacheimg_M" &

2156 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2157 -resize ${iconxy_S}'>' - ${fmt}:- \

2158 | convert - "$cacheimg_S" &

2159 printf '%s' "<img src=\"data:${filetype},"

2160 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2161 echo '">'

2162 echo "$sum" > $sumfile

2163 fi

2164 ## Now preparing cache image, done.

2165 ## Store this information to DB

2166 stbl=${tbl%_m}_s # user_s or grp_s

2167 pkey=${keycond%%=*} # Primary Key name

2168 pval=${keycond#*=} # Primary Key value

2169 query <<-EOF

2170 REPLACE INTO $stbl($pkey, key, type, val)

2171 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2172 EOF

2173 )

2174

2175 showhome() {

2176 # $1=userRowIdToShow

2177 err showhome \$1=$1

2178 case "$1" in

2179 *@*) uname=`getvalbypkey user name "$1"` ;;

2180 *) uname=`getvalbyid user name $1` ;;

2181 esac

2182 ## err ShowHome: uname=$uname

2183 td=`getcachedir home/"$1"`

2184 gecos=`gecos "$uname"`

2185 ## err SH:gecos=$gecos

2186 GF_VIEWONLY=1

2187 cond="gname in (select gname from grp_mem where user='$uname')"

2188 search_form_args=""

2189 if [ x"$user" = x"$uname" ]; then

2190 if [ -z "$S4MASTERDB" ]; then

2191 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2192 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2193 elif [ -n "$S4MASTERURL" ]; then

2194 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2195 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2196 fi

2197 usermenu="$usermenu

2198 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2199 # Display folders

2200 sql="select count(id) from article_m where id

2201 in (select id from article where author='$user')

2202 and type like 'file:%';"

2203 ## err nfile-sql=`echo "$sql"`

2204 nfile=`query "$sql"`

2205 # err nfile=$nfile

2206 if [ $nfile -gt 0 ]; then

2207 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2208 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2209 fi

2210 else

2211 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2212 GROUP BY user;"`

2213 usermenu="<p>Last seen on $latestlog</p>"

2214 search_form_args="author=$uname"

2215 fi

2216 . ./s4-blog.sh

2217

2218 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2219 search_form "$search_form_args" > $sf

2220 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2221 | htmlescape > $tf

2222 { echo "<div class=\"noprofimg\">"

2223 viewtable $formdir/user.def user $1

2224 echo "</div>"

2225 } > $pf

2226

2227 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2228 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2229 imf=$tmpd/profimg.$$; touch $imf

2230 if [ -n "$img" ]; then

2231 if true; then

2232 tbl=user_m

2233 enticond="name='$uname'"

2234 imgsrc_cache "$td/main" user_m "$enticond" M

2235 else

2236 { printf '%s' "<IMG src=\"data:${img#file:},"

2237 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2238 | sed 's/$..$/%\1/g'

2239 echo '">'

2240 }

2241 fi > $imf

2242 fi

2243 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2244 val='$uname';"`

2245 ## REMOVE This comment block until 2019/7/1

2246 ## err "----- `gdate +%FT%T.%3N` ------------C"

2247 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2248 listblog $uname > $bf

2249 ## unset ddd

2250 ## err "----- `gdate +%FT%T.%3N` ------------D"

2251

2252 hometail=$tmpd/tail.$$

2253 mkfifo $hometail

2254

2255 #Calling listgroupbytable, originally here

2256

2257 (

2258 # Display Most Recent Entry

2259 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2260 shortval=${shortval:-val}

2261

2262 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2263 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2264 ctime,

2265 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2266 (SELECT gecos FROM gecoses

2267 WHERE name=(SELECT val FROM blog_s

2268 WHERE key='owner' AND id=b.id)) owner,

2269 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2270 FROM blog b

2271 JOIN

2272 (SELECT distinct blogid, a.id aid, max(val) ctime

2273 FROM article a, article_s s

2274 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2275 GROUP BY blogid ORDER BY val DESC LIMIT 50

2276 ) m

2277 ON b.id=m.blogid;"

2278 # This should be as follows

2279 : <<EOF

2280 WITH arts AS(

2281 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2282 a.blogid, a.id id, s.val ctime

2283 FROM article a NATURAL JOIN article_s s

2284 WHERE s.key = 'ctime' AND a.author='$user'

2285 GROUP by s.id

2286 )

2287 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2288 FROM arts a0

2289 JOIN

2290 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2291 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2292 ORDER BY ctime DESC LIMIT 50;

2293 EOF

2294

2295 cat<<-EOF

2296 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2297 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2298 <div class="lcto">

2299 `DT_VIEW=replyblog dumptable html blog`

2300 </div>

2301 EOF

2302 unset DT_SQL

2303 if [ x"$user" = x"$uname" ]; then

2304 # Display NEWS

2305 # 2016-06-26

2306 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2307 acclog blog $blogreadflagrowid

2308 # echo "全部既読にしました" | html p

2309 fi

2310 # 2016-02-19 Counting NEWS without using dumptable.

2311 sql=`listnewblogsql "$user"`

2312 # echo "$sql" > tmp/listnew

2313 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2314 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2315 cont=$((cont-1))

2316 err newcount=$cont

2317 if [ $cont -gt 0 ]; then

2318 #echo "全体の新着記事${cont}傑" | html h2

2319 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2320 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2321 cat<<-EOF | html form 'action="?home"'

2322 `cgi_checkbox readchk yes 'id="read"'`<label

2323 for="read">新着ふくめて全部読んだことにする</label>

2324 `cgi_submit '確定'`

2325 `cgi_hidden read yes`

2326 EOF

2327 echo "$new10 "

2328 echo "</div>"

2329 else # If news is 0, set log cut off flag

2330 acclog blog $blogcutoffflagrowid # for speed

2331 fi

2332 else # Not My Home ($user != $uname)

2333 : # DT_SQL=

2334 fi

2335 ) > $hometail & # Is background call safe to m4??

2336 #

2337 listgroupbytable $formdir/grp.def $cond |

2338 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2339 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2340 -D_PROFIMG_="spaste(\`$imf')" \

2341 -D_BLOGS_="spaste(\`$bf')" \

2342 -D_SEARCH_="spaste(\`$sf')" \

2343 -D_NBLOG_="$nblog" \

2344 -D_GROUPS_="syscmd(\`cat')" \

2345 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2346 $layout/html.m4.html $layout/home.m4.html

2347

2348 # Record access log

2349 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2350 }

2351 commission() { # $1=grp-rowid $2=user-rowid

2352 contenttype; echo

2353 ## err commission: "$@"

2354 gname=`getgroupbyid $1`

2355 echo "グループ $gname 管理者委任" \

2356 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2357 if [ -n "$2" ]; then

2358 grp_reg_adm "$@"

2359 else

2360 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2361 fi

2362 }

2363 listgroupbytable() {

2364 # $1=deffile $2...=condition

2365 tagline=`grep :tag: $1`; shift

2366 and="${1:+and }" where=${1:+where }

2367 href="<a href=\"$myname?grp+"

2368 echo '<div class="listgroup">'

2369 NGsql="select distinct tag from\

2370 (select gname, max(case key when 'tag' then val end) as tag, \

2371 max(case key when 'ctime' then val end) as ctime\

2372 from grp_s group by gname order by ctime);"

2373 sql="select val from grp_s where key='tag' $and$* group by val;"

2374 ## err ListGRP: query sql="$sql"

2375 for tag in `query "$sql"`

2376 do

2377 ## err ListGrp: tag=$tag

2378 tn=${tagline%%=${tag}*}

2379 tn=${tn##*[ :]}

2380 sql="select rowid||':'||gname as 'グループ名',説明 from

2381 (select (select rowid from grp g where g.gname=grp_s.gname)

2382 as rowid,

2383 gname,

2384 max(case key when 'gecos' then val end) as '説明',

2385 max(case key when 'tag' then val end) as 'tag',

2386 max(case key when 'mtime' then val end) as mtime from grp_s

2387 $where$* group by gname having tag='$tag' order by mtime desc);"

2388 ## err PersonalGroupList= `echo $sql`

2389 echo "<h2>$tn</h2>"

2390 echo '<table class="b listgroup">'

2391 sq -header -html $db "$sql" \

2392 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2393 echo '</table>'

2394 done

2395 echo '</div>'

2396 }

2397 iconhref() (

2398 # $1=icon-file, $2=Href $3=title $4...=anchor

2399 data=`percenthex "$1"`

2400 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2401 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2402 href=$2; title=$3; shift 3

2403 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2404 )

2405 iconhref2() (

2406 # $1=icon-file, $2=Href $3=title $4...=anchor

2407 src=$1

2408 href=$2; title=$3; shift 3

2409 anchor=`echo $@|htmlescape`

2410 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2411 )

2412 listentry() (

2413 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2414 # Referring variable $iamowner=$grp to attach owner-request links

2415 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2416 cond='' hiddens=''

2417 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2418 if [ -z "$offset" ]; then

2419 offset=`getpar start`; offset=${offset%%[!0-9]*}

2420 offset=$((offset-1))

2421 fi

2422 offset=$((offset + 0)) # change to numeric forcibly

2423 [ $offset -lt 0 ] && offset=0

2424 limit=$listentlimit

2425 dir=`getcachedir "$1"`

2426 if [ x"$1" = x"user" ]; then

2427 hrb="$myname?home"

2428 deficon=person-default.png

2429 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2430 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2431 gcs=gecos

2432 else # if group

2433 hrb="$myname?grp"

2434 deficon=group-default.png

2435 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2436 gcs=name

2437 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2438 if [ -n "$tagline" ]; then

2439 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2440 ## err tagconv=$tagconv

2441 fi

2442 fi

2443 if [ ! -d $dir ]; then

2444 mkdir -p $dir

2445 fi

2446 if [ ! -s $dir/$deficon ]; then

2447 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2448 fi

2449 if [ -n "$2" ]; then

2450 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2451 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2452 fi

2453 tag=`getpar tag` tag2=`getpar tag2`

2454 if [ x"$tag" = x"NULL" ]; then

2455 tag="" tag2=""

2456 fi

2457 if [ -n "$tag$tag2" ]; then

2458 tag=${tag:-$tag2}

2459 qtag=`sqlquote "$tag"`

2460 cond2="tag=$qtag"

2461 fi

2462 if [ -n "$cond1$cond2" ]; then

2463 cond="$cond1${cond2:+ AND $cond2}"

2464 cond="WHERE ${cond# AND }"

2465 fi

2466

2467 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2468 # grpは呼出し元の動的スコープ変数でよくないな...

2469 ##qgrp=`sqlquote $grp`

2470 getgrp="(select gname from grp where rowid=${rowid:--1})"

2471 sql="select a.rowid, a.$link,

2472 coalesce(b.$gcs, a.$nm) as nick,

2473 quote(a.$nm) as qname,

2474 (SELECT val FROM ${tbl}_s

2475 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2476 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2477 || case when a.$nm in (select gname from grp)

2478 then printf('(%d名)',

2479 (select count(user) from grp_mem where gname=a.$nm))

2480 else ' <'||a.$nm||'>'

2481 end

2482 as name,

2483 b.tag,

2484 case when a.$nm in (select user from grp_adm

2485 where gname=$getgrp) then '管理者'

2486 when '$user' in (select user from grp_adm where gname=a.$nm)

2487 then 'ADMIN'

2488 when '$user' in (select user from grp_mem where gname=a.$nm)

2489 then 'Member'

2490 when '$iamowner' = '' then ''

2491 else ',not='||a.rowid end as ownerlink,

2492 CASE '$entity'

2493 WHEN 'グループ'

2494 THEN coalesce(

2495 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2496 'open')

2497 ||

2498 CASE WHEN '$user'

2499 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2500 THEN ' ismember'

2501 ELSE ''

2502 END

2503 ELSE 'user'

2504 END regmode

2505 from $tbl a left join

2506 (select $nm as name,

2507 max(case key when 'gecos' then val end) as gecos,

2508 max(case key when 'tag' then val end) as tag,

2509 max(case key when 'mtime' then val end) as mtime,

2510 max(case key when 'wtime' then val end) as wtime,

2511 max(case key when 'login' then val end) as login

2512 from ${tbl}_s group by $nm)

2513 b on a.$nm=b.name $cond $3

2514 order by b.wtime desc, b.login desc,

2515 b.mtime desc, b.tag desc, a.rowid asc"

2516 # Give precedence to newer maintained groups (2016-09-24)

2517 # Note that mtime is stored only in grp_s.

2518 ## err LE:sql.1="$sql"

2519 total=`query "with x as ($sql) select count(*) from x;"`

2520 echo "${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2521 echo '<div class="listentry">' # List-entry div

2522 # Show owner/member filter button

2523 METHOD=GET

2524 hiddens="$hiddens

2525 `cgi_hidden kwd \"$kwd\"`

2526 `cgi_hidden stage \"$stage\"`"

2527 if [ x"$tbl" = x"grp" ]; then

2528 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2529 fh="<select name=\"tag\">$nl"

2530 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2531 for l in $args; do

2532 val=${l#*=} tname=${l%=*}

2533 if [ x"$val" = x"$tag" ]; then

2534 s=" selected"

2535 selectedtags="(種別[${tname}]のみ)"

2536 else

2537 s=""

2538 fi

2539 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2540 done

2541 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2542 cat<<-EOF

2543 <form action="$myname" method="$METHOD">

2544 </form>

2545 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2546 for="ismembtn">参加中以外隠す</label>

2547 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2548 for="isadmbtn">管理者参加以外隠す</label>

2549 EOF

2550 # limit=3

2551 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2552 fi

2553 if [ $total -gt $limit ]; then

2554 echo '<div>'

2555 METHOD=GET cgi_form $stage <<EOF

2556 $form

2557 <label>次の語を含む${entity}で検索:

2558 `cgi_text kwd "$kwd"`</label>

2559 $hiddens

2560 EOF

2561 echo '</div>'

2562 else

2563 echo $selectedtags | html p

2564 fi

2565 cat<<EOF

2566 <form action="$myname" method="$METHOD">

2567 <p>${total}件中の<input class="hidesub" type="text" name="start"

2568 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2569 <input type="submit" value="確定"></p>

2570 </form>

2571 EOF

2572 if [ $((offset+limit)) -lt $total ]; then

2573 nextbtn=$(

2574 cat<<EOF

2575 <div class="right clear"><form action="$myname" method="$METHOD">

2576 `cgi_submit 次の${limit}件`

2577 $hiddens

2578 `cgi_hidden offset $((offset + limit))`</form></div>

2579 EOF

2580 )

2581 fi

2582 if [ $offset -gt 0 ]; then

2583 prevbtn=$(

2584 cat<<EOF

2585 <form action="$myname" method="$METHOD">

2586 `cgi_submit 前の${limit}件`

2587 $hiddens

2588 `cgi_hidden offset $((offset - limit))`</form>

2589 EOF

2590 )

2591 fi

2592 pnbtn="$nextbtn$prevbtn"

2593 echo $pnbtn

2594

2595 ## err ListEntry: `echo "$sql"\;`

2596 # sq $db here??? 2016-11-28

2597 query "$sql limit $limit ${offset:+offset $offset};" \

2598 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2599 # err name=$name owner=$ownerp lnk=$lnk

2600 # err newlnk=$lnk regmode=$regmode

2601 icondir=$dir/$id

2602 # Pick up only last icon

2603 htmlname=`echo $name|htmlescape`

2604 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2605 <p class=\"tag _$tag\">$tag</p>" \

2606 | _m4 $tagconv

2607 if [ -n "$NOSPEEDUP" ]; then

2608 files=`getvalbyid $tbl profimg $id $icondir`

2609 if [ -n "$files" ]; then

2610 icon=`echo "$files"|tail -1`

2611 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2612 else

2613 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2614 fi

2615 elif [ -n "$icon" -a -s "$icon" ]; then

2616 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2617 else

2618 cond="$nm=$qname"

2619 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2620 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2621 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2622 # err "img=[$img]"

2623 if [ -n "$img" ]; then

2624 echo "<a href=\"$hrb+$lnk\">"

2625 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2626 echo "</a>"

2627 else

2628 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2629 fi

2630 fi

2631 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2632 echo "</div>"

2633 done

2634 echo "</div>" # End of List-entry div

2635 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2636 )

2637 listmember() {

2638 listentry user "$@"

2639 }

2640 listgroup() {

2641 listentry group "$@"

2642 }

2643 hexteams() { # $1=gname, $2(optional)=user

2644 cond=${2:+" AND user='$2'"}

2645 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2646 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2647 }

2648 showgroup() { # $1=group-rowid

2649 if [ -z "$1" ]; then

2650 grid=`getpar grid`

2651 grid=${grid%%[!0-9]*}

2652 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2653 else

2654 grid=$1

2655 fi

2656 grp=`getgroupbyid $grid`

2657 qgrp=`sqlquote "$grp"`

2658 htmlgrp=`echo "$grp"|htmlescape`

2659 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2660 if isgroup "$grp"; then

2661 tf=$tmpd/title.$$

2662 sf=$tmpd/search.$$

2663 bodyclass=`query "SELECT val FROM grp_s

2664 WHERE gname=$qgrp AND key='regmode';"`

2665 if ismember "$user" "$grp"; then

2666 ismember="ismember"

2667 bodyclass="$bodyclass${bodyclass:+ }ismember"

2668 else

2669 ismember="" # bodyclass="group"

2670 fi

2671 bodyclass="$bodyclass grouphome"

2672 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2673 echo "グループ $htmlgrp" > $tf

2674

2675 showgroupsub $formdir/grp.def "$grid" | \

2676 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2677 -D_FORM_="syscmd(\`cat')" \

2678 -D_BODYCLASS_="$bodyclass" \

2679 -D_DUMPTABLE_="" \

2680 $htmlheader $sf $layout/form+dump.m4.html

2681 # $htmlheader $layout/form+dump.m4.html

2682 # $htmlheader is defined in grp()

2683 else # if $grp is removed at par2table

2684 listgroup

2685 fi

2686 }

2687 showgroupsub() {

2688 # $1=def-file $2=group-rowid

2689 # Using $ismember

2690 rowid=$2

2691 grp=`getgroupbyid $2`

2692 qgrp=`sqlquote "$grp"`

2693 td=`getcachedir grp/"$2"`

2694 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2695 if [ -z "$rowid" ]; then

2696 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2697 #grp=`sq $db "select gname from grp where rowid=$grp"`

2698 echo "showgroupsub: invalid argument($1 $2)" | html p

2699 return

2700 fi

2701 val=`getvalbyid grp profimg $rowid $tmpd`

2702 enticond="gname=$qgrp"

2703 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2704 if [ -n "$img" ]; then

2705 cat<<-EOF

2706 <p class="groupimg">

2707 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2708 EOF

2709 fi

2710 echo "<div class=\"noprofimg\">"

2711 viewtable $1 grp $rowid

2712 echo "</div>"

2713 if isgrpowner "$user" "$grp"; then

2714 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2715 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2716 iamowner=$rowid

2717 colmd=" mode"

2718 fi

2719 if [ -n "$ismember" ]; then

2720 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2721 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2722 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2723 cat<<-EOF

2724 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2725 href="?blog+$rowid">グループの新規話題作成</a>

2726 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2727 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2728 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2729 <div class="fold clear">

2730 `cgi_checkbox send yes id="send"`<label

2731 for="send">グループ全員にメッセージ送信</label>

2732 <div>

2733 `cgi_textarea message "" "cols=60"`

2734 `cgi_submit 送信`

2735 `cgi_reset リセット`

2736 </div>

2737 `cgi_hidden grp $rowid`

2738 </div></form>

2739 EOF

2740 fi

2741 # 加入ボタン + 加入者リスト

2742 if [ -n "$ismember" ]; then

2743 ismem='checked' state="(参加中)"

2744 else

2745 nomem='checked' state="(現在非加入)"

2746 fi

2747 # このグループでの加入アドレス

2748 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2749 and key='email';"`

2750 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2751 ## and key='email';"

2752 ##err email=$eml

2753 cat <<EOF

2754 <div class="fold clear">

2755 `cgi_checkbox reg yes id="reg"`<label

2756 for="reg">自身の加入状態を操作する</label>$state

2757 <div>

2758 EOF

2759 cgi_form grp <<EOF

2760 <p>このグループに</p>

2761 <table class="b">

2762 <tr><th>メンバーとして</th><td>

2763 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2764 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2765 <tr><th>参加する場合のメイルアドレス<br>

2766 <small>(メインのアドレスとは違うものにする場合に記入<br>

2767 同じでよい場合は空欄に)</small></th>

2768 <td>`cgi_text email $eml`</td></tr>

2769 </table>

2770 `cgi_hidden grp $rowid`

2771 EOF

2772 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2773 echo "moderated (承認加入の)グループなので実際に参加できるのは

2774 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2775 fi

2776 echo '</div></div>'

2777 echo '<h2>話題一覧</h2>'

2778 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2779 cat<<-EOF

2780 <form class="summary" action="$myname" title="$thelp">

2781 `cgi_hidden owner "$grp"`

2782 `cgi_hidden kwd "@week"`

2783 `cgi_hidden stage searchart`

2784 `cgi_submit "一週間のまとめ"`

2785 </form>

2786 EOF

2787 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2788 colstate="state:稼動状態:frozen=rowclass=凍結"

2789 DT_CHLD=article:blogid \

2790 DT_QOWNER="$qgrp" \

2791 DT_VIEW=replyblog dumptable html blog \

2792 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2793

2794 getgname="(select gname from grp where rowid=$rowid)"

2795 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2796 cm="?commission+$rowid"

2797 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2798 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2799 # team list

2800 hexteams=`hexteams "$grp"`

2801 if [ -n "$hexteams" ]; then

2802 echo "チーム一覧" | html h2

2803 echo '<div class="dumptable"><table class="b">'

2804 sq $db -html -header<<-EOF

2805 SELECT val TEAM,

2806 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2807 MEMBERS

2808 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2809 EOF

2810 echo '</table></div>'

2811 fi

2812 }

2813 grp_getbodyclass() {

2814 # Get css class name for document.

2815 # `moderated' for moderated groups

2816 # `ismember' for groups where user belongs

2817 # $1=GroupName (w/o quote)

2818 # $user=userNameCurrentlyLogin

2819 ## err grp_getbodyclass: 1="$1"

2820 qgrp=`sqlquote "$1"`

2821 query<<-EOF

2822 SELECT coalesce(

2823 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2824 'open')

2825 ||

2826 CASE WHEN '$user'

2827 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2828 THEN ' ismember'

2829 ELSE ''

2830 END;

2831 EOF

2832 }

2833 grpaction() { # $1=group-rowid

2834 err GRP_ACTION:IN

2835 grid=${1:-`getpar grp`}

2836 grp=`getgroupbyid "$grid"`

2837 htmlgrp=`echo "$grp" | htmlescape`

2838 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2839 if [ -z "$grp" ]; then

2840 echo "無効な指定です。" | html p; return

2841 fi

2842 if ! ismember $user "$grp"; then

2843 echo "加入者のみに許可された操作です。" | html p; return

2844 fi

2845 echo "グループ $grp 個別選択操作" \

2846 | _m4 -D_TITLE_="syscmd(\`cat')" \

2847 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2848 $layout/html.m4.html

2849

2850 isowner=""

2851 isgrpowner "$user" "$grp" && isowner="yes"

2852 usel=`getpar usel`

2853 if [ -n "$usel" ]; then

2854 uids=$(echo `echo $usel`|tr ' ' ',')

2855 ## err grpaction-1: grp=$grp, `echo $sql`

2856 text=`getpar text|tr -d '\r'`

2857

2858 rm=`getpar rm` cfm=`getpar confirm`

2859 ## err rm=$rm cfm=$cfm

2860 if [ x"$rm" = x"yes" ]; then

2861 if [ "$isowner" ]; then

2862 if [ x"$rm$cfm" = x"yesyes" ]; then

2863 # Eliminate

2864 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2865 for tbl in grp_mem grp_mem_s grp_mem_m; do

2866 sql="delete from $tbl $cond;"

2867 # echo "sql=$sql"

2868 query "$sql"

2869 err rmGRPuser "$sql"

2870 done

2871 num=`query "select count(*) from user where rowid in ($uids);"`

2872 #err num=$num

2873 if [ 0$num -gt 0 ]; then

2874 sql="select coalesce(b.val,a.name) from user a left join \

2875 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2876 # err `echo "$sql"`

2877 html pre<<EOF

2878 以下の${num}名のグループ $grp 登録を解除しました。

2879 `query "$sql"`

2880 EOF

2881 fi

2882 else

2883 echo "確認のチェックがないのでやめておきます。" | html p

2884 return

2885 fi

2886 else # not Group Owner

2887 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2888 return

2889 fi

2890 cat<<EOF

2891

2892 EOF

2893 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2894 if [ -z "$text" ]; then # if msg is empty

2895 echo "なにかメッセージを..." | html p

2896 return 0

2897 fi

2898 gecos=`gecos $user`

2899 safegc=`echo "$gecos" | tr -d '<>@'`

2900 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2901 fromad=`myemail4group "$grp"`

2902 ###mail_from="$safegc <$fromad>"

2903 mail_from="$safegc <$user>" # TEST: 2020/5/13

2904 test -n `getpar sender` &&

2905 export SENDER=$user # TEST: 2020/5/15

2906 replyto=$fromad

2907

2908 ## Start parse of attachment files

2909 if [ -n "`getpar email`" ]; then

2910 ar=`getpar supprcpt`

2911 if [ -n "$ar" ]; then

2912 for a in $ar; do

2913 if checkdomain "$a"; then

2914 supprcpt="$supprcpt $a"

2915 else

2916 err "SupprcptErr=[$a] by $user"

2917 removercpt="$removercpt $a"

2918 fi

2919 done

2920 fi

2921 subj=`getpar subject`

2922 afiles=""

2923 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2924 do

2925 f=$tmpd/$fn

2926 if [ -s $f ]; then

2927 afiles=$afiles"${afiles:+ }$f"

2928 fi

2929 done

2930 else

2931 preface=$(cat <<-EOF

2932 $url

2933 のグループ「$grp」のメンバーである $gecos さんから、

2934 あなた宛のメッセージです。

2935 ----------------------------------------------------------

2936 EOF

2937 )

2938 fi

2939 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2940 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2941 subj="${subj:-$gecos さんからのメッセージ}"

2942 REPLYTO=$replyto

2943 MAIL_FROM=$mail_from

2944 export REPLYTO SMAIL_TO MAIL_FROM

2945 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2946 for r in $rcpts; do

2947 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2948 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2949 else

2950 # Show sender and rcpts address for guest

2951 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2952 fi

2953 if [ -n "$afiles" ];then

2954 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2955 else

2956 smail "$r" "$subj"

2957 fi <<EOF

2958 ${preface:+$preface$nl}$text

2959 EOF

2960 done

2961 if [ $? = 0 ]; then

2962 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2963 sql="select coalesce(b.val, a.name) from

2964 (select name from user where rowid in ($uids)) a

2965 left join user_s b on a.name=b.name and b.key='gecos';"

2966 html pre<<EOF

2967 `query "$sql"`

2968 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2969 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2970 EOF

2971 err SendDone: `echo $sql`

2972 fi

2973 elif [ x"$rm" = x"commission" ]; then

2974 grp_reg_adm $grid $usel

2975 elif [ x"$rm" = x"addteam" ]; then

2976 team=`getpar team|sed "s/'/''/g"` # for single quotation

2977 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2978 if [ x"$team" != x"$newteam" ]; then

2979 echo "チーム名に使えない文字を除去しました" | html p

2980 team=$newteam

2981 fi

2982 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2983 cat<<-EOF | html p

2984 有効なチーム名を入力してください。

2985 カンマだけ、「なし」という名前は使えません。

2986 EOF

2987 echo "有効なチーム名を入力してください。" | html p

2988 else

2989 grp_add_team $grid "$team" $usel

2990 fi

2991 elif [ x"$rm" = x"rmteam" ]; then

2992 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2993 rmteam=`getpar rmteam|sed "s/'/''/g"`

2994 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2995 gname='$grp' AND user='$user' AND key='team'\

2996 AND val='$rmteam';\"`" ]; then

2997 grp_rm_team $grid "$rmteam" $usel

2998 else

2999 echo "所属していないチームの除去操作はできません。"|html p

3000 fi

3001 else

3002 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

3003 fi

3004 fi

3005 fi

3006 # POST count summary

3007 from=`getpar from`; to=`getpar to`

3008 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3009 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3010 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

3011 # New entry

3012 sql="WITH mems AS (

3013 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3014 ON gm.user=g.name

3015 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3016 ), target_article AS (

3017 SELECT id FROM article_s

3018 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3019 ), posts AS (

3020 SELECT author, count(author) post

3021 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3022 WHERE blogid IN (SELECT id FROM blog_s

3023 WHERE key='owner'

3024 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3025 AND key='text'

3026 GROUP BY author

3027 ), teams AS (

3028 SELECT user, group_concat(val, ', ') team

3029 FROM grp_mem_m

3030 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3031 AND key='team'

3032 GROUP BY user

3033 ), user_post AS (

3034 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3035 FROM mems m LEFT JOIN posts

3036 ON m.name=posts.author

3037 GROUP by m.rowid

3038 )

3039 SELECT

3040 CASE

3041 WHEN (SELECT user FROM grp_adm

3042 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3043 AND user=up.name) IS NOT NULL

3044 then 'k'

3045 ELSE ''

3046 END || rowid || ',' || gecos NAME,

3047 post POST, team _TEAM_

3048 FROM user_post up LEFT JOIN teams t

3049 ON up.name=t.user

3050 ORDER BY gecos;"

3051 ## err grpaction: "`echo \"$sql\"`"

3052 tf=$tmpd/title.$$

3053 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3054 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3055 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3056 for=\"cmadmin\">管理者委任</label>

3057 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3058 </p></div>"

3059 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3060 title=\"Shortcut: g${nl}Remove from the Group\"

3061 for=\"conf\">GRP登録解除</label>

3062 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3063 <p>この操作による通知は本人に行きません。

3064 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3065 </div>"

3066 # Get team list to which current user belongs into $hexteams

3067 allhexteams=$(hexteams "$grp")

3068 if [ -n "$isowner" ]; then

3069 myhexteams="$allhexteams" # admin can remove all teams' attr

3070 else

3071 myhexteams=$(hexteams "$grp" "$user")

3072 fi

3073 if [ -n "$myhexteams" ]; then

3074 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3075 title=\"Shortcut: s${nl}Strip a team tag from\"

3076 for=\"cmrmteam\">チーム属性除去</label>

3077 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3078 を除去します: `cgi_checkbox teamconfirm yes` 確認

3079 <p>この操作による通知は本人に行きません。

3080 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3081 </div>

3082 "

3083 fi

3084 stf=$tmpd/selteam.$$

3085 cgi_select_h selteam "5445414d" $allhexteams > $stf

3086 b1='<label> <input type="checkbox" name="usel" value="'

3087 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3088 br='<span id="reverse" title="Reverse Selection"></span>'

3089 #b2='"> <span>' b3='</span></label>'

3090 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

3091 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

3092 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

3093 cgi_form grpaction<<EOF \

3094 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

3095 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

3096 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

3097 | _m4 -D_TITLE_="spaste(\`$tf')" \

3098 -D_SUBTITLE_="チェック後操作ボタン" \

3099 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3100 $layout/form+dump.m4.html \

3101 | _m4 -D_TEAM_="spaste(\`$stf')"

3102 <p>下でチェックした人を対象として:</p>

3103 <div class="foldtabs">

3104 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3105 title="Shortcut: a${nl}Add a team tag to"

3106 for="cmteam">同じチーム属性を付与</label>

3107 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3108 `cgi_datalist_h teams $allhexteams`

3109 </div>

3110 ${rmteammsg}

3111 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3112 title="Shortcut: d${nl}DirectMail to"

3113 for="sendmsg" title="Direct Message">DM送信</label>

3114 <div>

3115 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3116 title="Using email format">email書式を使う</label>

3117 <div class="folded">

3118 <table>

3119 <tr><td>From: </td><td>$user</td></tr>

3120 <tr><td>このFrom:で送る</td>

3121 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3122 </td></tr>

3123 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3124 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3125 <tr><td>ファイル添付: </td>

3126 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3127 </table>

3128 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

3129 </div>

3130 <div>本文:`cgi_textarea text "" cols=72`

3131 </div>

3132 </div>

3133 ${isowner:+$cmmsg$excmsg}

3134 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3135 </div>

3136 <h4>$htmlgrp 参加者一覧</h4>$fromtonote

3137 <table class="td2r thl">

3138 `sq $db -header -html "$sql"`

3139 </table>

3140 `cgi_hidden grp $grid`

3141 `cgi_hidden myuid $myuid id="myuid"`

3142 EOF

3143 }

3144 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3145 # Create TEMPORARY VIEW

3146 query<<EOF

3147 CREATE TEMPORARY VIEW writeusers AS

3148 SELECT DISTINCT author FROM article

3149 WHERE id in (

3150 select id from article where blogid=(select id from blog where rowid=$1)

3151 );

3152 CREATE TEMPORARY VIEW movablegroups AS

3153 SELECT g.rowid growid , g.gname

3154 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3155 ON grp.gname=gm.gname -- そのユーザが属している

3156 AND user='$user') g -- グループに絞る

3157 WHERE (SELECT author FROM writeusers

3158 EXCEPT

3159 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3160 IS NULL;

3161 $2

3162 EOF

3163 }

3164 sql4readableblogs() {

3165 # Create view of blogs that can be readable to $user

3166 # Blog is readable when:

3167 # 1: blog owner is an user

3168 # 2: else, 2.1: owner-group where the $user belongs

3169 # 2.2: else, owner-group is not moderated

3170 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3171 cat<<EOF ## | tee tmp/sql.out

3172 CREATE TEMPORARY VIEW readableblogs AS

3173 SELECT blog.rowid rid, id, author

3174 FROM blog

3175 NATURAL JOIN

3176 (SELECT id,

3177 max(CASE key WHEN 'owner' THEN val END) owner,

3178 max(CASE key WHEN 'mode' THEN val END) mode

3179 FROM blog_s GROUP by id) bs

3180 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3181 THEN 1 -- blog owner is an user, READABLE

3182 WHEN (SELECT val FROM grp_s

3183 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3184 AND

3185 (SELECT user FROM grp_mem

3186 WHERE gname=bs.owner AND user='$user') IS NULL

3187 THEN 0

3188 WHEN mode IN ('quiz', 'enquete')

3189 THEN 0 -- "quiz" mode blog cannot be searched

3190 ELSE 1

3191 END;

3192 EOF

3193 }

3194 editheading() { # $1=rowid-of-heading

3195 rowid=${1%%[!A-Z0-9a-z_]*}

3196 if [ -z "$rowid" ]; then

3197 echo "話題番号が未指定です。" | html p

3198 return

3199 fi

3200 owner=`getvalbyid blog owner $rowid`

3201 title=`getvalbyid blog title $rowid`

3202 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3203 | _m4 -D_TITLE_="修正" \

3204 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3205 -D_BLOGS_="" -D_DUMPTABLE_="" \

3206 -D_FORM_="syscmd(\`cat')" \

3207 $layout/html.m4.html $layout/form+dump.m4.html

3208 # Move to group

3209 if isuser "$owner"; then

3210 crview4article $rowid

3211 n=`query "SELECT count(*) FROM writeusers;"`

3212 ## err N=$n

3213 if [ $((n)) -gt 0 ]; then

3214 ## err ROWID=$rowid

3215 sql="SELECT growid || ':' || gname FROM movablegroups;"

3216 cat<<-EOF

3217 <div class="fold">

3218 `cgi_checkbox mv send id="mv"`<label

3219 for="mv">この話題をグループ所有に移動する</label>

3220 <div>

3221 <form action="?mvart" method="POST" enctype="multipart/form-data">

3222 移動先グループ:

3223 <select name="mv2grp">

3224 EOF

3225 query ".mode html"

3226 query<<-EOF |

3227 $sql

3228 .mode list

3229 EOF

3230 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3231 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3232 cat<<-EOF

3233 </select>

3234 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3235 そのグループに加入しているものに限られます)</p>

3236 <p>`cgi_checkbox cfm yes`<label>確認

3237 (この操作は元に戻すことができません)</label></p>

3238 `cgi_hidden blogrowid $rowid`

3239 `cgi_submit 移動`

3240 `cgi_reset Reset`

3241 </form>

3242 </div>

3243 </div>

3244 EOF

3245 fi

3246 # end of isuser "$owner"

3247 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3248 [ -n "$hexteams" ];}; then

3249 none="`echo なし|hexize`"

3250 cat<<-EOF

3251 <div class="fold">

3252 `cgi_checkbox mv2team send id="mv2team"`<label

3253 for="mv2team">この話題を以下のチームのものにする</label>

3254 <div><p>現在の所属チーム設定:

3255 `query "SELECT

3256 coalesce((SELECT val FROM blog_s

3257 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3258 AND key='team'),

3259 ':なし');"`</p>

3260 <form action="?mvart" method="POST" enctype="multipart/form-data">

3261 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3262 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3263 `cgi_hidden blogrowid $rowid`<br>

3264 `cgi_submit 移動`

3265 `cgi_reset Reset`

3266 </form></div></div>

3267 EOF

3268 fi

3269 }

3270 mvart() { # move diary to some group or team

3271 # or move blog of group to team which belong to the group

3272 blogrowid=`getpar blogrowid`

3273 cfm=`getpar cfm`

3274 ##### echo move blog:$blogrowid to $mv2grp | html p

3275 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3276 . ./s4-blog.sh

3277 if [ -z "$blogrowid" ]; then

3278 echo "無効な指定です(mvart)。" | html p

3279 return

3280 elif [ x"$cfm" != x"yes" ]; then

3281 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3282 elif { mv2grp=`getpar mv2grp`

3283 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3284 [ -n "$mv2grp" ]; }; then

3285 crview4article $blogrowid

3286 ########## TRANSACTION BEGIN

3287 query "BEGIN;"

3288 n=`query "SELECT count(*) FROM writeusers;"`

3289 ## err Nwriteuser=$n

3290 if [ $((n)) -gt 0 ]; then

3291 query<<-EOF

3292 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3293 WHERE key='owner'

3294 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3295 AND $mv2grp IN (SELECT growid FROM movablegroups);

3296 EOF

3297 fi

3298 query "END;"

3299 ########## TRANSACTION END

3300 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3301 [ -n "$mv2team" ];}; then

3302 # blog owner can move it to ANY team

3303 case "$mv2team" in

3304 'なし')

3305 cat<<-EOF

3306 DELETE FROM blog_s

3307 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3308 AND key='team';

3309 EOF

3310 ;;

3311 "") ;;

3312 *)cat<<-EOF

3313 BEGIN;

3314 REPLACE INTO blog_s(id, key, val)

3315 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3316 'team', '$mv2team');

3317 REPLACE INTO blog_s(id, key, val)

3318 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3319 'notify', 'all'); -- Change notify to all

3320 END;

3321 EOF

3322 esac | query

3323 fi

3324 blog_reply $blogrowid

3325 echo yes | html p

3326 }

3327 editart() { # $1=article-rowid $2=blogrowid

3328 rowid=${1%%[!A-Z0-9a-z_]*}

3329 blogrowid=${2%%[!A-Z0-9a-z_]*}

3330 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3331 echo "表示する記事番号が未指定です。" | html p

3332 return

3333 fi

3334 owner=`getvalbyid blog owner $blogrowid`

3335 title=`getvalbyid blog title $blogrowid`

3336 author=`getvalbyid article author $rowid`

3337 ## err EDITart: owner=$owner, author=$author

3338 if isgrpowner "$user" "$owner"; then

3339 : EDIT OK

3340 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3341 echo "本人か所有者しか編集できません." | html p

3342 return

3343 fi

3344 aid=`query "select id from article where rowid=$rowid;"`

3345 tmpout=$tmpd/editart.$$.out

3346 GF_ACTION="?replyblog+$blogrowid#$aid" \

3347 edittable $formdir/article.def article $rowid \

3348 > $tmpout

3349 rm -f /tmp/editart.out

3350 # Cannot use pipelining to m4 with genform() because of stdin stack

3351 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3352 -D_FORM_="syscmd(cat $tmpout)" \

3353 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3354 -D_BLOGS_= -D_DUMPTABLE_= \

3355 $layout/html.m4.html $layout/form+dump.m4.html

3356 }

3357 send2mem() {

3358 rowid=`getpar grp`

3359 rowid=${rowid%%[!0-9]*} # Cleaning

3360 if [ -z "$rowid" ]; then

3361 echo "グループが未指定です。" | html p

3362 return

3363 fi

3364 message=`getpar message`

3365 if [ -z "$message" ]; then

3366 echo "文章を入れてください。" | html p

3367 return

3368 fi

3369 grp=`getgroupbyid $rowid`

3370 members=`collectemail "$grp"`

3371 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3372 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3373 mailfrom="`gecos "$user"` <$mailfrom>"

3374 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3375 msg=$(cat<<-EOF

3376 $urlbase?grp+$rowid

3377 グループ $grp に所属する

3378 `gecos $user` さんよりメッセージ:

3379

3380 $message

3381 EOF

3382 )

3383 # smail rcpt subj (file)

3384 for m in $members; do

3385 echo "$msg" |

3386 MAIL_FROM=$mailfrom \

3387 SENDER=$noreply \

3388 REPLYTO=$mailfrom \

3389 smail "$m" "$sj"

3390 done

3391 cat<<EOF

3392 <p>以下のユーザに送信しました。</p>

3393 <pre>

3394 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3395 </pre>

3396 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3397 EOF

3398 }

3399 joingrpadmit() {

3400 # $1=yes/no $2=session-key

3401 if [ -z "$2" ]; then

3402 echo "bye bye" | html p; return

3403 fi

3404 t_usr=`session=$2 getpar adduser`

3405 t_grp=`session=$2 getpar group`

3406 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3407 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3408 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3409 echo "無効な加入依頼です。" | html p

3410 echo "有効期限が切れたか、

3411 他の管理者がいる場合は処理済みの可能性があります。" | html p

3412 return

3413 fi

3414 if ! isgrpowner "$user" "$t_grp"; then

3415 echo "グループ管理者のみの機能です。" | html p; return

3416 fi

3417 case $1 in

3418 yes) joingrp "$t_grp" "$t_usr" yes ;;

3419 no) joingrp "$t_grp" "$t_usr" no ;;

3420 *)

3421 echo "無効な指定です($1)。" | html p

3422 return ;;

3423 esac

3424 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3425 rcpts="`getgroupadminmails "$t_grp"` $user"

3426 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3427 body="に

3428 $t_usr

3429 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3430 しました。"

3431 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3432 query "delete from session where id='$2';"

3433 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3434 }

3435

3436 joingrprequest() {

3437 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3438 jss="joingrp-`date +%s`-`genrandom 12`"

3439 addsession $jss +${memoplimitdays}days

3440 grpadmins=`getgroupadmins "$1"`

3441 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3442 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3443 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3444 $url

3445 $user さんから

3446 グループ $1

3447 に加入依頼がありました。

3448

3449 承認する:

3450 $urlbase?joingrpadmit+yes+$jss

3451

3452 白紙に戻す:

3453 $urlbase?joingrpadmit+no+$jss

3454 EOF

3455 echo "管理者に加入依頼を出しました。

3456 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3457 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3458 }

3459 joingrp() {

3460 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3461 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3462 if isgrpowner "$user" "$1"; then

3463 isowner="yes"

3464 elif [ -n "$5" ]; then

3465 isowner="yes"

3466 else

3467 isowner=""

3468 fi

3469 ## err jg:isgrpowner: isowner="$isowner"

3470 if [ -n "$isowner" ]; then

3471 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3472 elif [ x"$2" != x"$user" ]; then # if user is not login user

3473 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3474 return

3475 elif [ x"$3" = x"no" ]; then

3476 : # Do not pursue those who leave

3477 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3478 : # Member can change own email address for the joining moderated group

3479 else # adding user is $user itself

3480 case `getgroupattr "$1" regmode` in

3481 moderated)

3482 joingrprequest "$@" # Request only

3483 return

3484 ;;

3485 *)

3486 ;;

3487 esac

3488 fi

3489 qgname=`sqlquote "$1"`

3490 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3491 cond="where gname=$qgname and user='$2'"

3492 if [ x"$3" = x"yes" ]; then

3493 query "replace into grp_mem values($qgname, '$2');"

3494 # Notify joingrp to admin

3495 action="に加入しました。"

3496 if [ -n "$4" ]; then

3497 if msg=`emaildomaincheck "$4"`; then

3498 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3499 'string', '$4', NULL);"

3500 else

3501 echo $msg

3502 fi

3503 else

3504 query "delete from grp_mem_s $cond and key='email';"

3505 fi

3506 if [ -n "$5" ]; then # as ADMIN

3507 # Coming here means newly created group

3508 sql="select case\

3509 when (select count(*) from grp_mem where gname=$qgname)=1\

3510 then (select user from grp_mem\

3511 where gname=$qgname and user='$user')\

3512 else '' end; "

3513 err NewGrpChk: $sql

3514 if [ -n "`query \"$sql\"`" ]; then

3515 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3516 query "replace into grp_adm values($qgname, '$user');"

3517 fi

3518 fi

3519 else

3520 query "begin;

3521 delete from grp_mem $cond;

3522 delete from grp_mem_s $cond;

3523 delete from grp_mem_m $cond;

3524 delete from grp_adm $cond;

3525 delete from grp_adm_s $cond;

3526 delete from grp_adm_m $cond;

3527 end;"

3528 action="から脱退しました。"

3529 fi

3530 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3531 $url?grp+$grid

3532 $user (`gecos $user`)さんが

3533 グループ $1

3534 $action

3535 EOF

3536 }

3537 grp_add_team() (

3538 # $1=grp-rowid $2=team $3...=user-rowid(s)

3539 grp=`getgroupbyid $1`

3540 team=$2; shift; shift

3541 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3542 { echo "BEGIN;"

3543 for user; do

3544 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3545 '$grp',\

3546 (SELECT name FROM user WHERE rowid=$user),\

3547 'team', 'string', '$team');"

3548 done

3549 echo "END;"

3550 } | query

3551 )

3552 grp_rm_team() (

3553 # $1=grp-rowid $2=team $3...=user-rowid(s)

3554 grid=$1

3555 qgrp=$(sqlquote "`getgroupbyid $grid`")

3556 team=$2; shift; shift

3557 [ -z "$grid" -o -z "$team" ] && return

3558 { echo "BEGIN;"

3559 for user; do

3560 echo "DELETE FROM grp_mem_m\

3561 WHERE gname=$qgrp \

3562 AND user=(SELECT name FROM user WHERE rowid=$user)\

3563 AND key='team' AND val='$team';"

3564 done

3565 cat<<-EOF

3566 DELETE FROM blog_s

3567 WHERE rowid=(

3568 SELECT rowid

3569 FROM blog_s a

3570 WHERE key='team'

3571 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3572 AND NOT EXISTS (SELECT * FROM grp_mem_m

3573 WHERE key='team' AND val=a.val -- a.val=team

3574 AND gname = (SELECT val FROM blog_s b

3575 WHERE a.id=b.id AND key='owner')

3576 ));

3577 EOF

3578

3579 echo "END;"

3580 } | query

3581 )

3582 grp_reg_adm() {

3583 # $1=grp-rowid $2...=user-rowid

3584 grid=$1

3585 grp=`getgroupbyid "$1"`

3586 if [ -z "$grp" ]; then

3587 echo "無効なグループIDです" | html p; return

3588 fi

3589 if ! isgrpowner "$user" "$grp"; then

3590 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3591 fi

3592 shift

3593 for urid; do

3594 newadm=`query "select name from user where rowid=$urid;"`

3595 if [ -z "$newadm" ]; then

3596 echo "指定ユーザIDがおかしいようです。" | html p; return

3597 fi

3598 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3599 err ismember $newadm $grp

3600 if ismember $newadm "$grp"; then

3601 # OK, go ahead

3602 getgname="(select gname from grp where rowid=$grid)"

3603 query "replace into grp_adm values($getgname, '$newadm');"

3604 # confirm insertion

3605 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3606 if [ -n "`query \"$sql;\"`" ]; then

3607 echo "追加完了: $newadm" | html p

3608 else

3609 echo "追加失敗($1 $urid)" | html p

3610 fi

3611 fi

3612 showgroup $grid

3613 done

3614 }

3615 dt_rowhack() {

3616 # From: <TR>

3617 # ....

3618 # <TD>rowclass=foo</TD>

3619 # </TR>

3620 # To: <TR class="foo">....<TD>foo</TD></TR>

3621 sed -e '

3622 /^<TR>/ {

3623 :loop

3624 s/\n//

3625 N

3626 /<\/TR>/ {

3627 s/\n//

3628 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3629 n

3630 }

3631 $q

3632 b loop

3633 }'

3634 }

3635 dumptable() {

3636 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3637 # textのフィールドだけ全てダンプにしたほうがいいか

3638 # $DT_VIEW sets link

3639 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3640 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3641 VIEW=${DT_VIEW-replyblog}

3642 if [ -n "$VIEW" ]; then

3643 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3644 fi

3645 sqlfile=$tmpd/dump.sql

3646 : > $sqlfile # ensure to be empty

3647 printf '.mode html\n.header 1\n' > $sqlfile

3648 # $DT_CHLD=ChildTable:BindColumn

3649 if [ -n "$DT_CHLD" ]; then

3650 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3651 cat<<-EOF >> $sqlfile

3652 -- presql

3653 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3654 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3655 EOF

3656 # Speed up counting of new articles

3657 cat<<-EOF >> $sqlfile

3658 -- presql2

3659 DROP TABLE IF EXISTS _counts;

3660 CREATE TEMPORARY TABLE _counts AS

3661 SELECT $_i, count($_i) cnt

3662 FROM $_t GROUP BY $_i;

3663 /* Prepare NEW count table */

3664 CREATE TEMPORARY TABLE _target AS

3665 SELECT b.rowid trowid, b.id

3666 FROM "$2" b JOIN "$2_s" s

3667 ON b.id=s.id AND s.key='owner'

3668 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3669

3670 DROP TABLE IF EXISTS _children;

3671 CREATE TEMPORARY TABLE _children AS

3672 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3673 FROM (SELECT t.trowid, t.id $_i, a.id

3674 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3675 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3676

3677 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3678 DROP TABLE IF EXISTS _news;

3679 DROP VIEW IF EXISTS _news;

3680

3681 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3682 -- INSERT INTO _news

3683 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3684 CREATE TEMPORARY VIEW _news AS

3685 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3686 FROM (SELECT DISTINCT id FROM _target)

3687 a LEFT JOIN

3688 (SELECT $_i, count(ctime) newcnt

3689 FROM _children x

3690 WHERE ctime > coalesce((SELECT time from myacclog

3691 WHERE tblrowid=x.trowid),

3692 '1970-01-01')

3693 GROUP BY $_i) b

3694 ON a.id=b.$_i;

3695 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3696 EOF

3697 # REMOVE next line until 2019/5/1

3698 cntall="(select count($_i) from $_t where $_i=a.id)"

3699 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3700 # REMOVE next assignment until 2019/5/1

3701 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3702 and id in (select id from $_t where $_i=a.id) \

3703 and val > coalesce((select time from myacclog where \

3704 tblrowid=a.rowid),\

3705 '1970-01-01'))"

3706 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3707 cnt="$cntnew as '新着', $cntall as '総数',"

3708 dt_class=" td2r td3r dumpblogs"

3709 fi

3710 # Construct join expression

3711 eav="" scols=""

3712 pk=`gettblpkey $2`

3713 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3714 substr=${substr:-%s}

3715 for col in ${3:-`gettbl_s_cols $2`}; do

3716 valvar=val

3717 case $col in

3718 gecos) scols="$scols${scols:+, }${col#}"

3719 continue ;; # built-in column name

3720 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3721 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3722 case "$as" in

3723 *:*=*) cnd=${as#*:}

3724 h=${cnd%%=*} v=${cnd#*=}

3725 h=`sqlquotestr "$h"`

3726 v=`sqlquotestr "$v"`

3727 valvar="CASE val WHEN $h THEN $v END"

3728 as=${as%%:*} ;;

3729 esac

3730 ;;

3731 *) as=${col} ;;

3732 esac

3733 ss=`printf "$substr" "$valvar"`

3734 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3735 scols="$scols${scols:+, }b.$as"

3736 done

3737 #case author when '$user' then a.rowid else '---' end as ID,

3738 if [ -n "$DT_SQL" ]; then

3739 echo "$DT_SQL"

3740 else

3741 cat<<-EOF

3742 SELECT a.rowid as LINK, $cnt $scols

3743 FROM $2 a LEFT JOIN

3744 (SELECT $pk,$eav,

3745 max(CASE key

3746 WHEN 'owner'

3747 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3748 as gecos

3749 FROM ${2}_s c GROUP BY $pk)

3750 b ON a.$pk=b.$pk $4;

3751 EOF

3752 fi >> $sqlfile

3753 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3754 sqlog<<-EOF

3755 *** SQL-file: $sqlfile ***

3756 `cat $sqlfile`

3757 EOF

3758 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3759 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3760 cat $sqlfile >> tmp/sql

3761 # query ".read $sqlfile" > $tmpd/foo

3762 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3763 cp $tmpd/foo tmp/

3764 err "----- `gdate +%FT%T.%3N` ------------555555"

3765 ## $ddd LINE exists at the end of this function

3766 fi

3767 printf '.mode list\n.header 0\n' >> $sqlfile

3768 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3769 <div>

3770 <div class="dumptable">

3771 <table class="b$dt_class">

3772 `query ".read $sqlfile"`

3773 </table>

3774 </div>

3775 </div>

3776 EOF

3777 ### `query ".read $sqlfile"`

3778 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3779

3780 ## REMOVE THIS!

3781 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3782 }

3783

3784 clean-orphaned() {

3785 # This shoud be done by foreign_key rules, but some db lack them

3786 query<<-EOF

3787 -- Find blogs that have no parent

3788 WITH orphanedblog AS (

3789 SELECT blog.id,val FROM blog JOIN blog_s bs

3790 ON blog.id=bs.id AND key='owner'

3791 WHERE val NOT IN (SELECT gname FROM grp)

3792 AND val NOT IN (SELECT name FROM user)

3793 ) -- Remove them

3794 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

3795

3796 -- Find articles that have no parent blog

3797 WITH orphanedarticle AS (

3798 SELECT id FROM article

3799 WHERE blogid NOT IN (SELECT id FROM blog)

3800 ) -- Remove them

3801 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

3802 EOF

3803 }

3804 par2table() (

3805 # copy current parameters of par into destination table

3806 # $1=definition-file

3807 # Using $user and $session

3808 # Return value:

3809 # 0: Stored successfully

3810 # 1: Insufficient fillings

3811 # 2: No permission to modify the record

3812 # 3: Invalid rowid

3813 # 4: SUCCESS to delete

3814 # 5: Stop deletion for lack of confirm check

3815 # 6: Password length too short

3816 # 7: Password mismatch

3817 # 8: Old password incorrect

3818 # 9: Duplicated post

3819 rowid=`getpar rowid`

3820 if [ ! -e $1 ]; then

3821 echo "テーブル定義ファイルが見付かりません" | html p

3822 exit 1

3823 fi

3824 tbl=${1%.def}

3825 tbl=${tbl##*/}

3826 if [ -n "$rowid" ]; then # Modify existing entry

3827 if [ x"$tbl" = x"user" ]; then

3828 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3829 elif [ x"$tbl" = x"grp" ]; then

3830 sql="select gname from $tbl where rowid=$rowid;"

3831 ##err p2t:grp:q $sql

3832 isgrpowner "$user" "`query $sql`" && rowowner=$user

3833 elif [ x"$tbl" = x"blog" ]; then

3834 # Check if owner in blog_s

3835 blogowner=`getvalbyid blog owner "$rowid"`

3836 if isgrpowner "$user" "$blogowner"; then

3837 rowowner=$user

3838 else

3839 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3840 fi

3841 else

3842 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3843 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3844 fi

3845 ### err rowowner=$rowowner

3846 if [ x"$user" != x"$rowowner" ]; then

3847 echo "他人のレコードはいじれないの" | html p

3848 return 2

3849 elif [ -z "$rowowner" ]; then

3850 echo "指定したレコードはないみたい" | html p

3851 return 3

3852 fi

3853 rm=`getpar rm` cfm=`getpar confirm`

3854 # Editing existent entry

3855 if [ x"$rm" = x"yes" ]; then

3856 if [ x"$rm$cfm" = x"yesyes" ]; then

3857 query "delete from $tbl where rowid=$rowid;"

3858 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

3859 clean-orphaned

3860 fi

3861 return 4

3862 else

3863 echo "消去確認のチェックがないので消さなかったの..." | html p

3864 return 5

3865 fi

3866 fi

3867 fi

3868

3869 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3870 if [ -n "$rowid" ]; then

3871 # Update of existing record

3872 for col in `gettblcols $tbl`; do

3873 val=`getparquote $col`

3874 [ -z "$val" ] && continue

3875 ## err query "update $tbl set $col=$val where rowid=$rowid"

3876 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3877 sql="update $tbl set $col=$val where rowid=$rowid;"

3878 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3879 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3880 ## User name cannot be changed with interface provided with this

3881 ## script. But we offer the trigger to change owner user

3882 ## of blog_s table.

3883 #err "select quote($col) from $tbl where rowid=$rowid;"

3884 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3885 cat<<-EOF | query

3886 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3887 -- should use EXCLUSIVE transaction outside of this.

3888 SAVEPOINT par2table;

3889 $sql

3890 update blog_s set val=$val

3891 where key='owner' and val=$old;

3892 RELEASE SAVEPOINT par2table;

3893 EOF

3894 ## XX: DIRTY Hack Ends here

3895 ## We should keep blog's owner as a single column which has

3896 ## foreign key constraint with primary key of grp/user.

3897 else

3898 query "$sql"

3899 fi

3900 done

3901 # Then, set up $pval for further insertion of tbl_s and tbl_m

3902 for col in `gettblpkey $tbl`; do

3903 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3904 pval="$pval${pval:+, }\"$val\""

3905 done

3906 else

3907 # New entry

3908 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3909 # Avoid empty repost of article.

3910 if [ x"$tbl" = x"article" ]; then

3911 # If rowid is empty and ID exists in article-table, that is REPOST!

3912 aid=`getpar id`

3913 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3914 if [ -n "$xaid" ]; then

3915 # REPOST of article

3916 html p <<-EOF

3917 書き込み直後のリロードなので上書きを回避します。

3918 最新記事は末尾の「再読み込み」ボタンから見てください。

3919 EOF

3920 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3921 return 9 # STOP Duplicated posting

3922 fi

3923 fi

3924 # Generate values() for primary keys

3925 for col in `gettblpkey $tbl`; do

3926 # Genuine primary keys for _m and _s

3927 val=`getvalquote $tbl $col`

3928 [ -z "$val" ] && continue

3929 pval="$pval${pval:+, }$val"

3930 done

3931 ##err pval=$pval

3932 for col in `gettblfkey $tbl`; do

3933 # args for values() to insertion into master table

3934 val=`getvalquote $tbl $col`

3935 [ -z "$val" ] && continue

3936 formaster=$formaster"${formaster:+, }$val"

3937 done

3938 formaster="$pval${formaster:+, }$formaster"

3939 ## err formaster=$formaster

3940 if [ -z "$formaster" ]; then

3941 echo "項目を全て埋めてください" | html pre

3942 return 1

3943 fi

3944 ## err "replace into $tbl values($formaster);"

3945 query "replace into $tbl values($formaster);"

3946 ## Insertion to master table, done

3947 fi

3948

3949 transaction=$tmpd/sqlfile.sql; touch $transaction

3950 for kt in s m; do

3951 tb2=${tbl}_$kt

3952 for col in `gettbl_${kt}_cols $tbl`; do

3953 ptype=`getpartype $col "limit 1"`

3954

3955 # First, check update of existing entries in _m

3956 if [ $kt = m ]; then

3957 # sessID|address.1.22|string|Somewhere-x.y.z

3958 sql=""

3959 ##err dots from query "select var from par where var like '$col.%';"

3960 for v in `query "select var from par where var like '$col.%';"`; do

3961 # v=address.1.22

3962 st_rowid=${v##*.}

3963 origcol=${v%%.*} # original column derived from

3964 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3965 ##case `getpartype $v` in

3966 ## err CASE `gettbl_coltype $tbl/$origcol` in

3967 ## err edit flag = `getpar action.$v`

3968 case `getpar action.$v` in

3969 rm)

3970 if [ x`getpar confirm.$v` = x"yes" ]; then

3971 newsql="delete from $tb2"

3972 else

3973 echo "削除確認未チェック" | html p

3974 fi ;;

3975 edit)

3976 case `gettbl_coltype $tbl/$origcol` in

3977 image|document|binary)

3978 file=$tmpd/`getparfilename $v`

3979 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3980 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3981 continue

3982 fi

3983 ## err type=file=$file

3984 [ -z "$file" ] && continue

3985 bn=`sqlquotestr "${file##*/}"`

3986 bin="X'"$(hexize "$file")"'"

3987 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3988 type=\"file:$ct\"

3989 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3990 cachedir=`getcachedir "$tbl/$rowid"`

3991 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3992 rm -rf $cachedir

3993 ;;

3994 *)

3995 newsql="update $tb2 set val=(select val from par where var \

3996 like '$col.%.$st_rowid')"

3997 ;;

3998 esac

3999 ;;

4000 *) # maybe "keep", do not modify value

4001 continue

4002 ;;

4003 esac

4004 # err newsql=$newsql

4005 sql=$sql$nl"$newsql where rowid=$st_rowid;"

4006 done

4007

4008 if [ x"$bin" = x"NULL" ]; then

4009 ## err repl:normal sql=`echo $sql`

4010 if [ -n "$transaction" ]; then

4011 cat<<-EOF >> $transaction

4012 $sql

4013 DELETE FROM $tb2 WHERE type='string' AND val='';

4014 EOF

4015 else

4016 query "$sql

4017 delete from $tb2 where type='string' and val='';"

4018 ## err repl:normal done

4019 fi

4020 else

4021 # Binary update line is TOO LONG to pipelining

4022 sqlfile="$tmpd/sqlf.$$"

4023 if [ -n "$transaction" ]; then

4024 echo "$sql" >> $transaction

4025 else

4026 echo "$sql" > $sqlfile

4027 query ".read $sqlfile"

4028 fi

4029 fi

4030 # Rest of kt==m: set multiple mode

4031 nr=`getparcount $col`

4032 else

4033 nr=1 # for kt==s, number of records is 1

4034 fi

4035

4036 i=0

4037 while [ $i -lt $nr ]; do

4038 limit="limit 1 offset $i"

4039 i=$((i+1)) # increase beforehand against continue

4040 val=`getvalquote $tbl $col "$limit"`

4041 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4042 ## err $col=$val

4043 bin=NULL

4044 ## err partype$col=`getpartype $col "$limit"`

4045 case $ptype in

4046 file) file=$tmpd/`getparfilename $col "$limit"`

4047 ## err parfile-$col=$file

4048 [ -z "$file" ] && continue

4049 bin="X'"$(hexize "$file")"'"

4050 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4051 type=\"file:$ct\" ;;

4052 "*"*) continue ;; # foreign table

4053 *) type=\"string\" ;;

4054 esac

4055 case `gettbl_coltype $tbl/$col` in

4056 password) # special care for password

4057 # name={password,pswd1,pswd2}

4058 p1=`getpar pswd1 "$limit"`

4059 if [ -z "$p1" ]; then

4060 continue # SKIP password setting, if p1 is empty

4061 else

4062 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4063 ## err pswd=$pswd

4064 if pwcheck "$pswd"; then

4065 if [ x"$p1" = x"$p2" ]; then

4066 case "$p1" in

4067 ??????????*) ;;

4068 *) echo "パスワードは10字以上にしてください。" | html p

4069 return 6;;

4070 esac

4071 val="\"`echo $p1|mypwhash`\""

4072 else

4073 echo "2つの新パスワード不一致" | html p

4074 return 7

4075 fi

4076 else

4077 echo "旧パスワード違います" | html p

4078 return 8

4079 fi

4080 fi

4081 ;;

4082 esac

4083 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4084 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4085 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4086 if [ x"$bin" = x"NULL" ]; then

4087 ## err Normal-query: `echo $sql`

4088 if [ -n "$transaction" ]; then

4089 echo "$sql" >> $transaction

4090 else

4091 query "$sql"

4092 fi

4093 else

4094 sqlfile="$tmpd/query.$$"

4095 ## err sqlfile=`ls -lF $sqlfile`

4096 if [ -n "$transaction" ]; then

4097 echo "$sql" >> $transaction

4098 else

4099 echo "$sql" > $sqlfile

4100 query ".read $sqlfile"

4101 fi

4102 fi

4103 ## err p2t done

4104 done

4105 done

4106 done

4107 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4108 -- We cannot use transaction here, because groupupdate may use it.

4109 SAVEPOINT pa2table_insert;

4110 .read $transaction

4111 RELEASE SAVEPOINT pa2table_insert;

4112 EOF

4113 rc=$?

4114 [ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag

4115 ## err "Table:$tbl update done "

4116 return $rc

4117 )

4118 genform() {

4119 # $1 = form definition file

4120 # $2, $3 (optional)= table name and ROWID

4121 # If $GF_VIEWONLY set and nonNull, output values without form

4122 # If $GF_ARGS set, use it as content-strings in the form

4123 # If $GF_OWNER set, use it as value of name="owner"

4124 # If $GF_STAGE set, use it as value of name="stage"

4125 forms="" hiddens="" rowid=$3

4126 if [ ! -e "$1" ]; then

4127 echo "そのようなデータベースはないようです($2)。" | html p

4128 return

4129 elif [ -n "$2" ]; then

4130 rec=`query "select * from $2 where rowid='$rowid';"`

4131 if [ -z "$rec" ]; then

4132 pk=`gettblpkey $2`

4133 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4134 rec=`query "select rowid from $2 where $pk='$rowid';"`

4135 rowid=$rec

4136 rec=$3

4137 fi

4138 if [ -z "$rec" ]; then

4139 echo "そんなレコードはないみたいね..." | html p

4140 return

4141 fi

4142 fi

4143 if [ -z "$GF_VIEWONLY" ]; then

4144 rm='<input id="rm" name="rm" type="checkbox"

4145 value="yes"><label for="rm">このエントリの削除</label>

4146 <span>ほんとうに消しますよ(確認)!

4147 <input name="confirm" type=checkbox value="yes">はい</span>'

4148 fi

4149 # Image Cache dir

4150 ## err genform: getcache=$2/$rowid

4151 td=`getcachedir "$2/$rowid"`

4152 while IFS=: read prompt name keytype type args; do

4153 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4154 sp="${args:+ }"

4155 form="" val=""

4156 if [ -n "$rowid" ]; then

4157 # err genform2a: Seeking for "$2.$name, type=$type"

4158 rawval=`getvalbyid $2 $name $rowid $td`

4159 val=`echo "$rawval"|htmlescape`

4160 ## err genform3a: getvalbyid $2 $name $rowid $td

4161 ## err genform3b: val="[$val]" type="$type"

4162 fi

4163 if [ -n "$GF_VIEWONLY" ]; then

4164 is_hidden "$2" "$name" && continue

4165 fi

4166 case "$type" in

4167 text*)

4168 cgiform=cgi_multi_$type

4169 if [ -s $td/$name.count -a -n "$val" ]; then

4170 form=`$cgiform $name $td`

4171 val=$(echo "$val"|

4172 while read fn; do

4173 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4174 </td></tr>$nl"

4175 done)

4176 val="<table>$nl$val$nl</table>"

4177 else

4178 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4179 form=`cgi_$type $name "$rawval" "$args"`

4180 fi

4181 ;;

4182 [Rr][Aa][Dd][Ii][Oo])

4183 fh="<label><input type=\"radio\" name=\"$name\""

4184 form="`echo $args|sed -e \

4185 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4186 ;;

4187 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4188 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4189 ;;

4190 [Ss][Ee][Ll][Ee][Cc][Tt])

4191 fh="<select name=\"$name\">$nl"

4192 form=$(for l in $args; do

4193 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4194 done)

4195 if [ -n "$val" ]; then

4196 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4197 fi

4198 form="$fh$form</select>"

4199 ;;

4200 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4201 if [ -s $td/$name.count ]; then

4202 form=`cgi_multi_file $name $td "$args"`

4203 if [ -n "$val" ]; then

4204 hrfb="$myname?showattc+$2_m"

4205 val=$(echo "$rawval" \

4206 | while read fn; do

4207 data=`percenthex "$td/$fn"`

4208 #ct=`cat $td/$fn.content-type`

4209 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4210 ri=`cat "$td/$fn.rowid"`

4211 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4212 #imgsrc="<img src=\"data:$ct,$data\">"

4213 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4214 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4215 done)

4216 fi

4217 else

4218 form="<input type=\"file\" name=\"$name\" $args>"

4219 if [ -n "$val" ]; then

4220 imgs=$(echo "$rawval"\

4221 |while read fn;do

4222 data=`percenthex "$td/$fn"`

4223 echo "<img src=\"data:image/png,$data\">$fn<br>"

4224 done)

4225 form=$form"<br>$imgs"

4226 val=$imgs # 2015-06-15

4227 else

4228 form="<input type=\"file\" name=\"$name\" $args>"

4229 fi

4230 fi

4231 ;;

4232 [Hh][Ii][Dd][Dd][Ee][Nn])

4233 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4234 args="value=\"$GF_STAGE\""

4235 fi

4236 form="<input type=\"hidden\" name=\"$name\" $args>"

4237 prompt='' # Remove prompt

4238 ;;

4239 [Aa][Uu][Tt][Hh][Oo][Rr])

4240 [ -n "$GF_VIEWONLY" ] && continue

4241 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4242 prompt="" ;;

4243 [Oo][Ww][Nn][Ee][Rr])

4244 [ -n "$GF_VIEWONLY" ] && continue

4245 val=${GF_OWNER:-$val}

4246 val=${val:-$user}

4247 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4248 prompt="" ;;

4249 [Uu][Ss][Ee][Rr])

4250 # XXX: is null $user ok?

4251 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4252 [ -n "$GF_VIEWONLY" ] && continue

4253 form="$user"

4254 ;;

4255 [Pp]assword)

4256 [ -n "$GF_VIEWONLY" ] && continue

4257 form="`cgi_passwd`"

4258 val=""

4259 ;;

4260 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4261 [ -n "$GF_VIEWONLY" ] && continue

4262 if [ -z "$rowid" ]; then

4263 val=`genserial`

4264 fi

4265 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4266 prompt="" ;;

4267 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4268 prompt=""

4269 ;;

4270 parent|path|blog*)

4271 prompt=""

4272 ;;

4273 "*"*)

4274 tail=$tail"``"

4275 continue ;;

4276 esac

4277 if [ -n "$prompt" ]; then

4278 if [ -n "${GF_VIEWONLY}" ]; then

4279 form=$val

4280 else

4281 :

4282 fi

4283 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4284 else

4285 hiddens=$hiddens$nl"$form"

4286 fi

4287 done < $1

4288 # enctype="multipart/form-data"

4289 cat<<EOF

4290 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4291 ${rowid:+$rm}

4292 <table class="b $2">

4293 $forms

4294 </table>$hiddens

4295 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4296 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4297 EOF

4298 if [ -z $GF_VIEWONLY ]; then

4299 cat<<EOF

4300 <input type="submit" name="sub" value="OK">

4301 <input type="reset" name="res" value="Reset">

4302 EOF

4303 fi

4304 cat<<EOF

4305 $GF_ARGS</form>

4306 $tail

4307 EOF

4308 }

4309 edittable() {

4310 # $1=form-def $2=table $3 rowid

4311 genform "$@"

4312 }

4313 viewtable() {

4314 GF_VIEWONLY=1 genform "$@"

4315 }

4316 showattc() {

4317 # $1=table_m $2=rowid &optional $3=RawFlag

4318 ## err \$1=$1 \$2=$2 \$3=$3

4319 if ! isfilereadable $user $1 $2; then

4320 contenttype; echo

4321 echo "このファイルは管理者のみしか見られません" | html p

4322 putfooter; exit

4323 fi

4324 idir=`umask 002; mktempd` || exit 1

4325 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4326 bin=$idir/$myname-$$.bin

4327 sql="select quote(bin) from $1 where rowid='$2';"

4328 ## err showattc: sql: $sql

4329 sq $db "$sql" | unhexize > $bin

4330 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4331 type=${tv%//*} fn=${tv#*//}

4332 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4333 ct=${type#file:}

4334 case $ct in # all text/* changed to text/plain

4335 text/*)

4336 charset=`nkf -g $bin|cut -d' ' -f1`

4337 case $charset in

4338 ASCII*) charset="" ;;

4339 esac

4340 if [ -z "$3" ]; then

4341 ct="text/html${charset:+; charset=$charset}"

4342 link="?showattc+$1+$2+raw"

4343 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4344 | sed 's,^,<span></span>,' \

4345 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4346 -D_LINK_="$link" \

4347 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4348 exit $?

4349 fi

4350 ct="text/plain${charset:+; charset=$charset}"

4351 ;;

4352 esac

4353 contenttype "$ct"

4354 echo "Content-Disposition: filename=\"$fn\""

4355 echo "Content-Length: " `cat $bin | wc -c`; echo

4356 #echo "Content-Type: " ${type#file:}; echo

4357 cat $bin

4358 }

4359 #

4360 # Some default stupid handler on CGI values

4361 #

4362 default_storedb() {

4363 # ARG: $1=table-def-file

4364 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4365 tbl=`basename $1`

4366 tbl=${tbl%.def}

4367 cols="`grep :text $1|cut -d: -f2`"

4368 col=`echo "$cols"|head -1`

4369 vcol=`getpar $col`

4370 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4371 if [ -n "$vcol" ]; then

4372 par2table $1

4373 else

4374 return 2 # No insertion occurred

4375 fi

4376 }

4377

4378 default_view() { # $1=def-file

4379 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4380 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4381 default_storedb "$@"

4382 query "select rowid from $tbl order by rowid desc;" \

4383 | while read rowid; do

4384 viewtable $1 $tbl $rowid

4385 done | _m4 -D_TITLE_="$tbl" \

4386 -D_FORM_="`genform $1`" \

4387 -D_DUMPTABLE_="syscmd(cat)" \

4388 $layout/html.m4.html $layout/form+dump.m4.html

4389 }

4390 default_viewtext() { # $1=def-file

4391 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4392 default_storedb "$@"

4393 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4394 | _m4 -D_TITLE_="$tbl" \

4395 -D_FORM_="`genform $1`" \

4396 -D_DUMPTABLE_="syscmd(cat)" \

4397 $layout/html.m4.html $layout/form+dump.m4.html

4398 }

4399 default_smail() {

4400 default_storedb "$@"

4401 if [ $? -eq 2 ]; then

4402 _m4 -D_TITLE_="入力" \

4403 -D_FORM_="`genform $1`" \

4404 -D_DUMPTABLE_="" \

4405 $layout/html.m4.html $layout/form+dump.m4.html

4406 return

4407 fi

4408 cond=""

4409 for pk in `gettblpkey $tbl`; do

4410 pv=$(sqlquote "$(getpar $pk)")

4411 cond="$cond${cond:+ and }$pk=$pv"

4412 done

4413 sql="select rowid from $tbl where $cond;"

4414 rowid=`query "$sql"`

4415 ## err smail1 - "$sql" "-> rowid=$rowid"

4416

4417 while IFS=: read prompt name keytype type args; do # Read from $1

4418 val=`getpar $name`

4419 if [ -n "$val" ]; then

4420 text="$text

4421 $prompt

4422 $name=$val

4423 ---------------------------------------------------------"

4424 fi

4425 case "$type" in

4426 image|document|file)

4427 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4428 fns=$(echo "$fn"|while read fn; do

4429 err mv $tmpd/$fn.orig $tmpd/$fn

4430 mv $tmpd/$fn.orig $tmpd/$fn

4431 rm $tmpd/$fn.rowid # Remove cache flag

4432 ## err "`ls $tmpd/$fn`"

4433 echo $fn

4434 done)

4435 files="$files $fns"

4436 ;;

4437 esac

4438 done < $1

4439 ## err FILES=$files "`ls -lF $tmpd`"

4440 subj="from ${REMOTE_ADDR}"

4441 (echo "$url"

4442 echo "への書き込みがありました。"

4443 echo "------"

4444 echo "$text"

4445 ) | (cd $tmpd &&

4446 err LS="`ls -lF`" &&

4447 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4448 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4449 echo "以下の内容で送信しました。" | html p

4450 viewtable $1 $tbl \

4451 `query "select rowid from $tbl order by rowid desc limit 1;"`

4452 echo "戻る" | html a "href=\"?\""

4453 }