s4: 7b3786b1eb4b s4-funcs.sh

s4

view s4-funcs.sh @ 937:7b3786b1eb4b

Summarize access count as well as post count

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sun, 08 Aug 2021 11:28:37 +0900
parents	c17e67348950
children	79c7e64884c4

line source

1 #!/bin/sh

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

3 # $HGid$

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

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

8 if $isCGI; then

9 case "$SCRIPT_NAME" in

10 *-world-*)

11 S4WORLD=${SCRIPT_NAME#*world-}

12 S4WORLD=${S4WORLD%.*}

13 echo S4WORLD=$S4WORLD >&2

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

15 ;;

16 *)

17 worldconf=s4-config.sh

18 ;;

19 esac

20 echo worldconf=$worldconf >&2

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

22 echo DB=$DB >&2

23 fi

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

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

26 cgiext=${CGIEXT:-.cgi}

27 myargs="$@"

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

29 tmpdir=${TMPDIR:-tmp}

30 dbdir=${DBDIR:-db}

31 logdir=${LOGDIR:-tmp}

32 tmpfiles=""

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

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

35 defaultdb=$dbdir/cgi.sq3

36 db=${DB:-$defaultdb}

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

38 userupdateflag=$dbdir/userupdate

39 sesstb=tmp.sess

40 workdb=$dbdir/tmpdata.sq3

41 listentlimit=${LISTENTLIMIT:-30}

42 listartlimit=${LISTARTLIMIT:-50}

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

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

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

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

47 templ=${TEMPL:-templ}

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

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

50 imgdir=${IMGDIR:-img}

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

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

53 msgdir=$templ/msg

54 timeout="+2 days"

55 memoplimitdays="7"

56 dumpcollen=22

57 #thumbxy=120x120

58 thumbxy=96x96

59 iconxy_S=80x80

60 iconxy_M=400x400

61 maximagexy=1600x1600

62 ### maximagexy=400x400

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

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

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

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

68 file_accept_help="

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

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

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

72 "

73 file_warn="$file_accept_help

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

75 blogreadflagrowid=0

76 blogcutoffflagrowid=-1

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

78 whatsnewdays=${WHATS_NEW_DAYS:-14}

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

80 session=$main_session

82 tconfs=""

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

84 conftbl=_tblconf

85 nl="

86 "

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

88 iconcachekey="profimgcache_S"

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

91 # Start debug logging

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

93 exec 3>> $logdir/debug.out

94 err() {

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

96 }

97 case "$HTTP_USER_AGENT" in

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

99 *) touchpanel="" ;;

100 esac

101

102 # If S4MASTERDB is set, behave in another world

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

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

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

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

107 # Variables set in s4-world.sh

108 # $S4WORLDS, $S4WROLDNAME, $S4WORLDGRPS

109 # Files created in s4-world.sh

110 # $worldlistfile, $worldoptionfile, $worldnamefile, $worldgrpfile

111 fi

112

113

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

115

116 : <<EOF

117

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

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

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

121 レポート提出管理

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

123

124 [2016]

125 7/12 根本への反省

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

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

128

129 [以下2015]

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

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

132 Web

133 締切設定

134

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

136 ○自分の提出物リスト

137

138 7/19 ○設置

139 ○一斉送信

140 ○getparfilename の tmpd の扱い

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

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

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

144 ○添付ファイル回収

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

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

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

148

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

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

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

152 ○レポート提出モード

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

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

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

156

157

158 7/9 ○管理者の追加

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

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

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

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

163

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

165

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

167 ○グループ検索

168

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

170

171

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

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

174

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

176 ○6/1 par2tableを triplex 対応に

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

178 →とすると一気に

179

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

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

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

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

184

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

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

187 ## 1. passwd

188 ## 2. grp

189 ## 3. grp_mem

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

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

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

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

194

195 ## ● listの定義:

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

197 ## ● hashの定義:

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

199

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

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

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

203 ## csv2sq3 で .csv.sq3 の Makefile

204

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

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

207 ## type := root | comment

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

209 ## unique(id, filename)

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

211

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

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

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

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

216 ## できたー!

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

218 ↓

219 ↓以下に変更

220 with recursive allmem as

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

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

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

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

225

226

227 with recursive allmem as

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

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

230 where allmem.val=grp_m.gname)

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

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

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

234

235

236 ## triggerもできた。

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

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

239

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

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

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

243 ## グループ管理

244 ## 個人情報管理

245 ##

246

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

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

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

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

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

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

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

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

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

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

257

258 ## ログテーブル

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

260

261

262

263 ■表設計

264 * 3つの表に分散管理

265 id格納表 + hash表 + list表

266 * *_s *_m

267

268

269

270 user, user_map, user_col

271

272 ■抽象エントリタイプ

273 * user

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

275 * group

276 権限判定に利用

277 * serial

278 自動idとして機能

279 * password

280 入力 type=passwordで入力

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

282 * session

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

284 * text

285 入力 type=text

286 * textarea

287 入力 textarea

288 * image|document

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

290 * owner

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

292 * gowner

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

294 * timestamp

295 datetime()

296 * parent

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

298 * path

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

300

301 格納タイプ

302 * list

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

304 * hash

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

306

307 オブジェクトタイプ

308 * entry

309 id, title, owner

310 * textpart

311 id, parentID, text

312 * binarypart

313 id, parentID, contenttype, filename, content

314 * content

315 hash(textpart), list(binarypart)

316 * topic

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

318 * reply

319 id, parentID, content

320 * blog

321 list(entry)

322 blog = [topic, list(reply)]

323

324

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

326 "text" => "hogehoge ..",

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

328 "author" => "taro",

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

330 "parent" => "/",

331 "path" => "/1",

332 "text" => "blah, blah, ....",

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

334 {"serial" => 2,

335 "author" => "hanako",

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

337 "parent" => "/",

338 "path" => "/2",

339 "text" => "blah, blah, ....",

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

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

342

343

344 user:=

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

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

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

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

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

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

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

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

353

354 変換表

355 /user/email=m

356

357 blog:=

358 シリアル:id:p:serial

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

360 所有者:owner:s:owner:

361 時刻:ctime:s:stamp:

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

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

364

365 article:=

366 シリアル:id:p:serial

367 筆者:author:s:owner

368 時刻:ctime:s:stamp:

369 参照元:parent:s:parent:

370 パス:path:s:path:

371 本文:text:s:textarea:

372 画像:image:m:image:

373

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

375

376

377 EOF

378

379 logstart() {

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

381 }

382 logend() {

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

384 }

385 sqlog() {

386 logstart

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

388 cat >> $querylog

389 else

390 echo "$*" >> $querylog

391 fi

392 logend

393 }

394 sq() {

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

396 logstart

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

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

399 else

400 echo "$@" >> $querylog

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

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

403 fi

404 logend

405 }

406 dbsetup() {

407 pipedir=$tmpdir/pipedir

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

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

410 suf=`date +%s`

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

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

413 mkfifo $sqi $sqo

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

415 sq $db < $sqi &

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

417 if $isCGI; then

418 exec 2>> $logdir/error.out

419 fi

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

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

422 rm $sqi

423 # Attach supplemental DB

424 cat >&5 <<-EOF

425 .output /dev/null

426 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

430 .output stdout

431 EOF

432 }

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

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

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

436 fi

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

438 }

439 cleanup() {

440 trap '' INT HUP EXIT TERM PIPE

441 echo .quit >&5

442 kill $sq3pid

443 kill $sq3pid

444 rm -f $sqo $sqi

445 rm -rf $tmpfiles

446 cleanup2

447 }

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

449 # simple redirection for the sake of speed.

450 query() {

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

452 echo ".output $sqo" >&5

453 logstart

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

455 tee -a $querylog

456 else

457 echo "$@" >> $querylog

458 echo "$@"

459 fi >&5

460 echo ".output stdout" >&5

461 cat $sqo

462 rc=$?

463 logend

464 return $rc

465 }

466 _m4() {

467 #S4NAME=f,f,f

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

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

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

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

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

473 }

474 ismember() {

475 # $1=user, $2=group

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

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

478 }

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

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

481 }

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

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

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

485 }

486 isgrpowner() (

487 # $1=user, $2=group

488 gn=`sqlquote "$2"`

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

490 err isgrpowner: $sql

491 test -n "`query $sql`"

492 )

493 isgrpownerbygid() (

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

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

496 err isgrpownerbygid: $sql

497 test -n "`query $sql`"

498 )

499 getgroupadminmails() {

500 # $1=group

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

502 email4group "$1" "$i" ;

503 done

504 }

505 getgroupadmins() { # $1=group

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

507 qgrp=`sqlquote "$1"`

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

509 }

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

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

512 getvalbyid grp $2 \

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

514 }

515 getgroupbyid() {

516 # $1=id|gname

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

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

519 # err ggbyid: `echo $sql`

520 query $sql

521 }

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

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

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

525

526 # Return true when anonymous mode

527 [ "$anonymousmode" ] && return 0

528 # case `getvalbyid blog mode $2` in

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

530 # *closed*)

531 # owner=`getvalbyid blog owner $2`

532 # if isgrp $owner; then

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

534 # elif isuser $owner; then

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

536 # fi

537 # esac

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

539 case "$2" in

540 grp_*)

541 sql="SELECT 'owner'

542 FROM grp_adm

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

544 AND

545 user = '$user';"

546 ;;

547 *)

548 sql="with getblog as (

549 select key,val from blog_s where id=(

550 select blogid from article where id in

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

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

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

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

555 isgrpadm as (select user from grp_adm where

556 gname=(select val from getowner) and

557 user='$1'),

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

559 select case

560 when (select author from article where

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

562 then 'author'

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

564 then 'open'

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

566 then CASE

567 WHEN (SELECT val FROM isgrp) IS NULL

568 THEN

569 CASE WHEN (SELECT val from getowner)

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

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

572 ELSE ''

573 END

574 WHEN (select user from isgrpadm) IS NOT NULL

575 THEN 'i-am-admin'

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

577 END

578 when (select val from getmode) is null

579 then 'open'

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

581 then (SELECT user FROM isgrpadm)

582 when (select author from article where

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

584 then 'user+author'

585 else '' end;"

586 ;;

587 esac

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

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

590 # result=`query "$sql"`

591 # err FileAccessibility=$result

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

593 }

594 linkhome() {

595 # $1=UserOrGroupRowid

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

597 if isuser $1; then

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

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

600 name=`gecos $1|htmlescape`

601 else

602 _grid=`numericalize "$1"`

603 echo -n "grp+$1"

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

605 fi

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

607 }

608 hreflink() {

609 # s4 specific notation:

610 # ^href=URL

611 # ^iframe=URL

612 # ^video=URL

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

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

615 # OSM umap Wikistyle Notation:

616 # [[URL]] - Simle Link

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

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

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

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

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

622 # Other Style

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

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

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

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

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

628 # SPC+SPC+$ - <br>

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

630 checkboxON="${cb} checked>"

631 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

656

657 }

658 minitbl() {

659 sed -n '

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

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

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

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

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

665 H; # Concat this line to HoldSpace

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

667 $ b cont

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

669 }

670 :cont

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

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

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

674 # . of ^. is workaround for FreeBSD sed

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

676 p; # Print whole "table" element

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

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

679 }

680 x; # Back to the newest line

681 p; # Print rest' | miniul

682 }

683 miniul() {

684 sed -e '

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

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

687 :top

688 s/\n//;

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

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

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

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

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

694 $ b cont

695 N; # 次の行を読む

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

697 /^ /b top

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

699 p; s/.*//;

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

701 /^\* /b top

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

703 }

704

705 :cont

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

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

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

709 H; x

710 }

711 x' | miniol

712 }

713 miniol() {

714 sed -e '

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

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

717 :top

718 s/\n//;

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

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

721 x;

722 s,^<ol>\n$[1-9][0-9]*$\. ,<ol start="\1"><li>,; # 1行目のみ

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

724 x;

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

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

727 $ b cont

728 N; # 次の行を読む

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

730 /^ /b top

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

732 p; s/.*//;

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

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

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

736 }

737

738 :cont

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

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

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

742 H; x

743 }

744 x'

745 }

746 acclog() (

747 # $1=table, $2=rowid

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

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

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

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

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

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

754 fi

755 )

756 gecos() (

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

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

759 )

760 setpar() {

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

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

763 case "$1" in

764 user) _user="$v" ;;

765 skey) _skey="$v" ;;

766 esac

767 fi

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

769 }

770 unsetpar() {

771 for i; do

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

773 case "$i" in

774 user|skey) unset _$i ;;

775 esac

776 fi

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

778 done

779 }

780 replpar() {

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

782 }

783 getpar() {

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

785 val=""

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

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

788 user) val=$_user ;;

789 skey) val=$_skey ;;

790 esac

791 fi

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

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

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

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

796 fi

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

798 case "$var" in

799 owner)

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

801 echo $user; return

802 elif ismember $user $val; then

803 echo $val; return

804 fi ;;

805 esac

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

807 echo "$val"

808 }

809 setskey() {

810 # For quick response...(?)

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

812 }

813 chkskey() {

814 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

820 return 0

821 fi

822 return 1

823 }

824 resetskey() {

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

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

827 fi

828 }

829 getpartype() {

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

831 }

832 getparcount() {

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

834 }

835 getparfilename() {

836 # null if type of $1 is not file

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

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

839 }

840 sqlquote() {

841 (v="$1"

842 case "$v" in

843 "") return ;; # null

844 "X'"*) # quoted hex string

845 echo $1 ;;

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

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

848 echo "\"$v\""

849 return ;;

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

851 echo "\"$v\""

852 return ;;

853 *)

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

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

856 else

857 echo "\"$v\""

858 fi ;;

859 esac)

860 }

861 sqlquotestr() (

862 case "$1" in

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

864 echo "'$v'" ;;

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

866 esac

867 )

868 mktempd() {

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

870 }

871 getcachedir() { # $1=maintable

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

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

874 else

875 echo $tmpd/$thumbxy

876 fi

877 }

878 getval() {

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

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

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

882 echo "$user" ;;

883 stamp|datetime)

884 date "+%F %T" ;;

885 serial)

886 (s=`getpar $2`

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

888 *)

889 getpar "$2" "$3";;

890 esac

891 }

892

893 getvalquote() {

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

895 (v=`getval "$@"`

896 case "$v" in

897 "") echo NULL ;;

898 *) sqlquote "$v" ;;

899 esac)

900 }

901 getparquote() {

902 sqlquote "`getpar $1`"

903 }

904 getbinbyid() {

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

906

907 }

908 getvalbyid() {

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

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

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

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

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

914

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

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

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

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

919 return

920 fi

921 done

922 rowid=$3

923 pk=`gettblpkey $1`

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

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

926 td=${4:-$tmpd}

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

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

929 for kt in s m; do

930 t=${1}_$kt

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

932 vcount=1 # count(val)

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

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

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

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

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

938 if [ $kt = m ]; then

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

940 # Reset val to store filenames if type is string

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

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

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

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

945 i=0

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

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

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

949 i=$((i+1))

950 fn=$c.$i

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

952 case $type in

953 file:*)

954 #file=$td/$val

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

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

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

958 # FOR SPEED: Skip file generation if imgcache exists

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

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

961 && continue

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

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

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

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

966 select rowid from $cond $slice;

967 .output '$td/$fn'

968 select val from $cond $slice;

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

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

971 .output stdout

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

973 EOF

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

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

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

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

978 ls -lh "$file" |

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

980 case "$type" in

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

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

983 *|*:[Aa]pplication*)

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

985 png:- > "$file"

986 ;;

987 esac

988 ;;

989 *)

990 sq $db<<EOF

991 .output $td/$fn.rowid

992 select rowid from $cond $slice;

993 .output $td/$fn

994 select val from $cond $slice;

995 EOF

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

997 ;;

998 esac

999 done

1000 else

1001 rm -f $td/$c.count

1002 case $type in

1003 file:*)

1004 echo "$val" \

1005 | while read fn; do

1006 file=$td/$fn

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

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

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

1010 | unhexize > "$file"

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

1012 case $type in

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

1014 *:[Aa]pplication*)

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

1016 png:- > $file ;;

1017 esac

1018 fi

1019 done

1020 ;;

1021 esac

1022 fi

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

1024 return

1025 fi

1026 done

1027 done)

1028 }

1029 getvalbypkey() (

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

1031 pk=`gettblpkey $1`

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

1033 getvalbyid "$1" "$2" $rowid $4

1034 )

1035 getvalbycond() {

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

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

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

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

1040 getvalbyid "$1" "$2" $rowid "$4"

1041 fi

1042 }

1043 getpwfield() {

1044 # getpwfield user column

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

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

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

1048 echo "$val"

1049 return 0

1050 else

1051 return 1

1052 fi

1053 }

1054 numericalize() {

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

1056 }

1057 encode() {

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

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

1060 sha1=sha1

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

1062 sha1=sha1sum

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

1064 sha1=gsha1sum

1065 fi

1066 fi

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

1068 }

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

1070 gs_pdfwrite() {

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

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

1073 }

1074 fi

1075 enjpeg() {

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

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

1078 cjpeg="cjpeg"

1079 else

1080 cjpeg="convert - jpeg:-"

1081 fi

1082 fi

1083 $cjpeg "$@"

1084 }

1085 mycrypt() (

1086 key=$1 salt=$2

1087 # err \$2=$2

1088 case $2 in

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

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

1091 esac

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

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

1094 )

1095 hexize() {

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

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

1098 hexize="xxd -p"

1099 else

1100 hexize_hd() {

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

1102 }

1103 hexize="hexize_hd"

1104 fi

1105 fi

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

1107 }

1108 unhexize() {

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

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

1111 unhex="xxd -p -r"

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

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

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

1115 EOF

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

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

1118 # world writable...:)

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

1120 fi

1121 fi

1122 cat "$@" | $unhex

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

1124 }

1125 percenthex() {

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

1127 }

1128 htmlescape() {

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

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

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

1132 }

1133 enascii() {

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

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

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

1137 else

1138 enascii_now=`date +%FT%T`

1139 enascii_sed() {

1140 nkf -Z0Z1Z2 \

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

1142 }

1143 enascii="enascii_sed"

1144 fi

1145 fi

1146 cat "$@" | $enascii

1147 }

1148 size_h() {

1149 i="$1" oi=$1

1150 set -- B B KB MB GB TB

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

1152 oi=$i

1153 i=$((i/1024))

1154 shift

1155 done

1156 echo ${oi}$1

1157 }

1158 gettblconf() {

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

1160 ## tconfs=`sq $db \

1161 tconfs=`query \

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

1163 fi

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

1165 }

1166 gettblkeys() {

1167 # $1=tbl

1168 gettblconf

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

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

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

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

1173 case $type in

1174 *p*)

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

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

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

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

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

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

1181 esac

1182 case $type in

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

1184 esac

1185 done

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

1187 }

1188 gettblpkey() {

1189 # $1=tbl

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

1191 }

1192 gettblfkey() {

1193 (x=`gettblkeys $1`

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

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

1196 }

1197 gettblcols() {

1198 (x=`gettblkeys $1`

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

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

1201 }

1202 gettbl_s_cols() {

1203 (x=`gettblkeys $1`

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

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

1206 }

1207 gettbl_m_cols() {

1208 (x=`gettblkeys $1`

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

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

1211 }

1212 gettbl_h_cols() {

1213 (x=`gettblkeys $1`

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

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

1216 }

1217 gettbl_coltype() (

1218 gettblconf

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

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

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

1222 )

1223 is_hidden() {

1224 # $1=Tbl $2=col

1225 gettblconf

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

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

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

1229 case $x in

1230 *h*) return 0 ;;

1231 *) return 1 ;;

1232 esac

1233 }

1234

1235 dbsetbyid() {

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

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

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

1239 quotedp=$(sqlquotestr "$p")

1240 unset primary update

1241 gettblconf

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

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

1244 #err conf=$conf

1245 case ${conf#*=} in

1246 p*) primary=1 ;;

1247 f*) update=1 ;;

1248 u*) ;;

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

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

1251 esac

1252 #err t=$t

1253 type=string fn=""

1254 case $conf in

1255 */password)

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

1257 ;;

1258 */image*|*/document*)

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

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

1261 ;;

1262 esac

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

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

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

1266 if [ "$primary" ]; then

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

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

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

1270 elif [ "$update" ]; then

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

1272 else

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

1274 fi

1275 )

1276 }

1277 expire() (

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

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

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

1281 )

1282 addsession() {

1283 # expireをセット

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

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

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

1287 # Remove old session parameters

1288 now=`expire now`

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

1290 }

1291 gencookie() (

1292 path=${URL#*:/}

1293 path=${URL%/*}

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

1295 for kv; do

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

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

1298 done

1299 )

1300 contenttype() {

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

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

1303 }

1304 putheader() {

1305

1306 }

1307 putfooter() {

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

1309 }

1310 getcookie() {

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

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

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

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

1315 case "$k" in

1316 user) _user="$v" ;;

1317 skey) _skey="$v" ;;

1318 esac

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

1320 done

1321 }

1322 genrandom() {

1323 # $1=columns (default: 10)

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

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

1326 }

1327 genserial() {

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

1329 }

1330 smail() {

1331 # smail rcpts subj (file)

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

1333 # $MAIL_FROM <- From: header value

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

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

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

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

1338 sender=${SENDER:-$admin}

1339 # Do not call m4 with directly passing text

1340 _r=$tmpd/rcpt

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

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

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

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

1345 }

1346 smail_queue_flush() {

1347 # $1=timelimit

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

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

1350 SELECT rowid FROM smailq

1351 GROUP BY rcpts, subj

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

1353 EOF

1354 )

1355 for rid in $rowids; do

1356 sq $workdb \

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

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

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

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

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

1362

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

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

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

1366 .separator "\n" "------------------\n\n"

1367 SELECT time, text FROM smailq

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

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

1370 ORDER by time;

1371 EOF

1372 then

1373 cat <<-EOF | sq $workdb

1374 DELETE FROM smailq

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

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

1377 EOF

1378 fi

1379 fi

1380 done

1381 }

1382 smail_queue() {

1383 # $1=Rcpts, $@=subj

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

1385 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1394 EOF

1395 )

1396 ## XXX: Adhoc load mitigation

1397 case "$now" in

1398 *[01])

1399 err flush_queue=$mintime

1400 smail_queue_flush ;;

1401 esac

1402 fi

1403 }

1404 setviastring() {

1405 table=par

1406 oifs="$IFS"

1407 IFS="&"

1408 for us in $1; do

1409 k=${us%%=*}

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

1411 setpar "$k" "string" "$v"

1412 done

1413 IFS="$oifs"

1414 }

1415 checkdomain() (

1416 # Check the validity of domain by referring DNS

1417 item=$1

1418 err checkdomain $1

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

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

1421 )

1422 pwcheck() {

1423 # $1=passwd

1424 dbpswd=`getpwfield $user pswd`

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

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

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

1428 }

1429 mypwhash() {

1430 mycrypt "`cat`" `genrandom 5`

1431 }

1432 flag_profupdate() {

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

1434 # This is useful to mitigate account sync load

1435 query <<-EOF

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

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

1438 EOF

1439 touch $userupdateflag

1440 }

1441 wasureta() {

1442 user=$1

1443 if ! checkdomain $user; then

1444 contenttype; echo

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

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

1447 putfooter

1448 exit 0

1449 fi

1450 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1452 encpswd=`echo $newpswd|mypwhash`

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

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

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

1456 $msgdir/mail-newaccount.m4 \

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

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

1459 }

1460 checkauth() {

1461 user=`getpar user`

1462 skc=`getpar skey` # from cookie

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

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

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

1466 if chkskey "$skc"; then

1467 return 0

1468 fi

1469 fi

1470 pswd=`getpar pswd`

1471 quser=`sqlquotestr "$user"`

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

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

1474 return 4 # 4=server too heavy

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

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

1477 return 2 # 2=login fail

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

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

1480 contenttype; echo

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

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

1483 cat <<-EOF | html p

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

1485 EOF

1486 exit 0 # XXXXX more smart...

1487 fi

1488 wasureta "$user"

1489 err wasureta issued for "$user"

1490 return 1 # wasureta error

1491 fi

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

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

1494 if pwcheck "$pswd"; then

1495 newsession=`genrandom 34`

1496 if setskey "$user" "$newsession" &&

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

1498 gencookie "user=$user" "skey=$newsession"

1499 return 0

1500 else

1501 return 4 # Heavy load??

1502 fi

1503 fi

1504 err "Login failed: [$user]"

1505 return 2 # Password mismatch

1506 }

1507 showlogin() {

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

1509 test -z "$args" && resetskey

1510 s4name=${S4NAME:-s4}

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

1512 cat $layout/login.m4.html

1513 echo '</body></html>'

1514 ) | _m4 \

1515 -D_BODYCLASS_="login" \

1516 -D_TITLE_="$s4name" \

1517 -D_SYSNAME_="Welcome to $s4name" \

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

1519 exit 0

1520 }

1521 dologin() {

1522 test -n "$S4WORLD" && syncaccount

1523 checkauth

1524 st=$?

1525 if [ $st != 0 ]; then

1526 contenttype; echo

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

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

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

1530 showlogin # and EXIT

1531 fi

1532 err "Auth OK: [$user]"

1533 }

1534

1535 # Do instant jobs here

1536 dbsetup

1537 trap cleanup INT HUP EXIT TERM PIPE

1538 # trap cleanup INT HUP

1539

1540

1541 cgiinit() {

1542 tmpd=`tmpd=$tmpdir mktempd`

1543 tmpf=$tmpd/stream.$$

1544 tmpfiles=$tmpfiles" $tmpd"

1545 addsession $session

1546 getcookie

1547 case "$REQUEST_METHOD" in

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

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

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

1551 # cat > $tmpf # too much?

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

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

1554 s="`cat $tmpf`"

1555 ;;

1556 esac

1557 case "$CONTENT_TYPE" in

1558 *boundary*)

1559 bndry=${CONTENT_TYPE#*boundary=}

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

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

1562 do

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

1564 #echo u=$us

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

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

1567 # err k=$k v=$v

1568 case "$k" in

1569 *:filename)

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

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

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

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

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

1575 :

1576 fi

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

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

1579 case "$mimetype" in

1580 [Ii]mage/x-xcf)

1581 bzip2 "$tmpd/$v"

1582 v=${v}.bz2

1583 ;;

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

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

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

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

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

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

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

1591 v=$vjpg

1592 else

1593 mimetype="Not supported"

1594 fi

1595 ;;

1596 [Ii]mage/*)

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

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

1599 ;;

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

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

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

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

1604 err Calling gs from $v to $nv

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

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

1607 v=$nv

1608 fi

1609 fi

1610 fi

1611 esac

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

1613 then

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

1615 continue

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

1617 replpar text string \

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

1619 continue

1620 fi

1621 ;;

1622 image|document|binary)

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

1624 continue ;;

1625 *)

1626 type='string'

1627 ;;

1628 esac

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

1630 setpar "$k" "$type" "$v"

1631 done

1632 ;;

1633 *)

1634 setviastring "$s"

1635 ;;

1636 esac

1637 }

1638 email4group() {

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

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

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

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

1643 sql="WITH

1644 grpemails AS (

1645 SELECT gname, user, val email

1646 FROM grp_mem NATURAL JOIN grp_mem_s

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

1648 useremails AS (

1649 SELECT user.name, val email

1650 FROM user

1651 LEFT JOIN user_m

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

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

1654 FROM useremails u LEFT JOIN grpemails g

1655 ON u.name=g.user

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

1657 query "$sql"

1658 }

1659 email4groupbyuid() {

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

1661 g=$1; shift

1662 uids=`echo "$@"`

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

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

1665 email4group "$g" `query "$sql"`

1666 }

1667 myemail4group() {

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

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

1670 }

1671 collectmembersbyid() {

1672 # Collect user names of group specified by grid

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

1674 query "SELECT user FROM grp_mem \

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

1676 }

1677 collectmembersbyid() {

1678 # Collect user names of group name

1679 qgrp=`sqlquote "$1"`

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

1681 }

1682 collectgecosesbyid() {

1683 # Collect user gecoses of group

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

1685 query<<-EOF

1686 SELECT gecos

1687 FROM gecoses

1688 WHERE name IN (SELECT user FROM grp_mem

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

1690 EOF

1691 }

1692 collectemail() (

1693 # Collect email addresses for group $1

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

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

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

1697 for e; do

1698 if isuser "$e"; then

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

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

1701 else

1702 qgrp=`sqlquote "$e"`

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

1704 gmem="grp_mem"

1705 else

1706 tm=`sqlquote "$TEAM"`

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

1708 fi

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

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

1711 $gmem g left join grp_mem_s s

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

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

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

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

1716 query "$sql"

1717 fi

1718 done

1719 )

1720 sendinvitation() (

1721 # $1=email

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

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

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

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

1726 gecos=`gecos`

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

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

1729 _m4 -D_URL_="$urlbase" \

1730 -D_USER_="$name" \

1731 -D_EMAIL_="$1" \

1732 -D_REGIST_="$regist" \

1733 -D_ADMIN_="$admin" \

1734 $msgdir/mail-invite.m4 \

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

1736 return 0

1737 )

1738 emaildomaincheck() {

1739 case "$1" in

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

1741 *@*)

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

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

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

1745 return 2

1746 fi

1747 return 0

1748 ;;

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

1750 esac

1751 }

1752 invite() {

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

1754 case "$email" in

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

1756 *@*)

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

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

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

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

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

1762 elif sendinvitation $email; then

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

1764 else # Cannot be reached here

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

1766 fi ;;

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

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

1769 esac

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

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

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

1773 fi

1774 if [ -f $invite_policy ]; then

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

1776 else

1777 pol="$invite_policy"

1778 fi

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

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

1781 -D_INVITE_POLICY_="$pol" \

1782 $layout/html.m4.html $layout/invite.m4.html

1783 }

1784 regist() {

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

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

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

1788 echo "bye bye" | html p

1789 reutrn

1790 fi

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

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

1793 cat<<EOF

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

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

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

1797 EOF

1798 return

1799 fi

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

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

1802 # Fake login password to wasureta

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

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

1805 wasureta $email

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

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

1808 addsession $1 # for removal after 1 minute

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

1810 $layout/login.m4.html

1811 return

1812 }

1813 group_safename() {

1814 # Convert $1 to safe group name

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

1816 }

1817 groupupdate() {

1818 gname=`getpar gname`

1819 qgname=`sqlquote "$gname"`

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

1821 # See ALSO same job in showgroup()

1822 newgname=`group_safename "$gname"`

1823 err newgname=$newgname

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

1825 err NewGNAME: gname=$newgname

1826 gname=$newgname

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

1828 replpar gname string "$gname"

1829 fi

1830 # Name confliction check

1831 parow=`getpar rowid`

1832 ## err parow=$parow

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

1834 query "BEGIN EXCLUSIVE;"

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

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

1837 if [ $count -gt 0 ]; then

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

1839 query "END;"

1840 return

1841 fi

1842 par2table $formdir/grp.def

1843 query "END TRANSACTION;"

1844 # Remove orphan

1845 : <<EOF

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

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

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

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

1850 EOF

1851 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1855 sql="delete from blog where id in

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

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

1858 query "$sql";

1859 unsetpar tag kwd

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

1861 return # and return

1862 fi

1863 fi

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

1865 fi

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

1867 grid=$(query $sql)

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

1869 grp $grid

1870 }

1871 groupclone() {

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

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

1874 case "$1" in

1875 */noteam)

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

1877 esac

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

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

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

1881 return

1882 fi

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

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

1885 return

1886 fi

1887 i=0

1888 while true; do

1889 copy="-copy$i"

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

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

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

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

1894 i=$((i++))

1895 continue

1896 fi

1897 break

1898 done

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

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

1901 query<<-EOF

1902 BEGIN;

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

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

1905 SELECT $newqname, key, val

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

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

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

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

1910 -- Copy members and their configuration --

1911 REPLACE INTO grp_mem SELECT $newqname, user

1912 FROM grp_mem WHERE gname=$qgrp;

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

1914 FROM grp_mem_s WHERE gname=$qgrp;

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

1916 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1917 -- Copy administrators --

1918 REPLACE INTO grp_adm SELECT $newqname, user

1919 FROM grp_adm WHERE gname=$qgrp;

1920 COMMIT;

1921 EOF

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

1923 STOPCLONEMSG=1 groupconf "$newrowid"

1924 }

1925 groupman() {

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

1927

1928 GF_STAGE="grpconf"

1929 GF_STAGE=groupupdate

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

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

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

1933 -D_DUMPTABLE_="syscmd(cat)" \

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

1935 }

1936 userconf() {

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

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

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

1940 }

1941 groupconf() {

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

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

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

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

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

1947

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

1949 grp=`getgroupbyid $rowid`

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

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

1952 return

1953 fi

1954

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

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

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

1958 ## Setup migration menu

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

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

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

1962 err v=$v

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

1964 migrate=$(cat<<-EOF

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

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

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

1968 <form action="?migrategrp">

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

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

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

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

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

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

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

1976 `cgi_hidden stage migrategrp`

1977 `cgi_hidden rowid $rowid`

1978 `cgi_submit OK`

1979 `cgi_reset Reset`

1980 </form>

1981 </div>

1982 EOF

1983 )

1984 fi

1985 fi

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

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

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

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

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

1991 <table>

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

1993 <button>クローン作成(チームも複製)</button></a></td>

1994 <td><p>(チームなどもそのままで掲示板なしの状態から)</p></td></tr>

1995 <tr><td><a href="?groupclone+$rowid/noteam">

1996 <button>作成(チームなし)</button></a></td>

1997 <td>(チームは引き継がずメンバーのみ同じグループを作成)</td></tr>

1998 </table>

1999 <p>ボタンを押すと即作成します。不要な場合はグループ編集で

2000 削除してください。</p>

2001 </div>

2002 $migrate

2003 `cgi_radio grpaction close id="x"`<label for="x" accesskey="x">×</label>

2004 <div style="height: $height; background: transparent;"></div>

2005 EOF

2006 fi

2007 }

2008 migrategrp() {

2009 rowid=`getpar rowid`

2010 rowid=${rowid%%[!0-9]*}

2011 grp=`getgroupbyid $rowid`

2012 if ! isgrpowner "$user" "$grp"; then

2013 echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"

2014 return

2015 fi

2016 if [ x`getpar emichk` != x"yes" ]; then

2017 echo "移住確認未チェックなので中止します。" | html p

2018 grp "$rowid"

2019 return

2020 fi

2021 destconf=`getpar migrateto`

2022 err destconf=$destconf

2023 if [ ! -e $destconf ]; then

2024 echo "移住先Worldが認識できないので中止します($destconf)。" | html p

2025 grp "$rowid"

2026 return

2027 fi

2028 if [ -n "$worldconf" ]; then

2029 srcconf=$worldconf

2030 else

2031 srcconf=s4-config.sh

2032 fi

2033 _m4 -D_TITLE_="移住操作" -D_BODYCLASS_="" $layout/html.m4.html

2034 echo "移住操作" | html h1

2035 echo '<pre>'

2036 set -- "$srcconf" "$destconf" "$rowid"

2037 err ./s4-migrate.sh "$srcconf" "$destconf" "$rowid"

2038 . ./s4-migrate.sh # Dot(.) sourcing might not pass arguments

2039 rc=$?

2040 echo "</pre>"

2041 if [ $rc -eq 0 ]; then

2042 echo "World [$world] への移住完了。" | html p

2043 echo "<p><a href=\"$dsturl?grp+$destrowid\">移住先</a></p>"

2044 elif [ -n "$faillist" ]; then

2045 echo "移住後削除失敗" | html p

2046 echo "空いている時間帯に再度試して下さい。" | html p

2047 else

2048 echo "移住失敗" | html p

2049 echo "移動先に重複がないか確認して下さい。" | html p

2050 fi

2051 return

2052 }

2053 mems() {

2054 _m4 -D_TITLE_="参加者一覧" -D_BODYCLASS_=listmember $layout/html.m4.html

2055 kwd=`getpar kwd`

2056 listmember $kwd

2057 }

2058 grps() {

2059 case "$S4WORLD" in

2060 $nonewgroupworld) ;;

2061 *) LINK_NEWGRP="<a href=\"?groupman\">新規グループ作成</a>" ;;

2062 esac

2063 _m4 -D_TITLE_="グループ一覧" -D_BODYCLASS_=listgroup $layout/html.m4.html

2064 kwd=`getpar kwd`

2065 listgroup "$kwd" \

2066 | _m4 -D_DUMPTABLE_="syscmd(cat)" \

2067 -D_TITLE_="グループ関連操作" \

2068 -D_FORM_="${LINK_NEWGRP}${NEWGRP_GUIDE}" \

2069 $layout/form+dump.m4.html

2070 }

2071 grp() { # $1=group-rowid

2072 gpg=`getpar grp`

2073 grid=${1:-$gpg}

2074 grp=`getgroupbyid "$grid"`

2075 ## . ./s4-blog.sh

2076 jg=`getpar joingrp`

2077 if [ -n "$jg" ]; then

2078 [ -n "$jg" -a -n "$grp" ] &&

2079 joingrp "$grp" "$user" "$jg" "`getpar email`"

2080 fi

2081 htmlheader=$layout/html.m4.html

2082 showgroup "$grid"

2083 }

2084 sql4interestblogs() {

2085 cat<<EOF

2086 CREATE TEMPORARY VIEW interestblogs AS

2087 SELECT blog.rowid rid, id, author

2088 FROM blog

2089 NATURAL JOIN

2090 (SELECT id, val owner FROM blog_s WHERE key='owner') bs

2091 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2092 THEN 1 -- blog owner is an user, READABLE

2093 WHEN (SELECT user FROM grp_mem

2094 WHERE gname=bs.owner AND user='$user') IS NULL

2095 THEN 0

2096 ELSE 1

2097 END;

2098 EOF

2099 }

2100 listnewblogsql() { # $1=user

2101 newdays=${WHATS_NEW_DAYS##*[!0-9]} # Shave non-digits

2102 newdays=${newdays%%[!0-9]*}

2103 newdays=${newdays:-14}

2104 basetime="datetime('now', 'localtime', '-${newdays} days')"

2105 deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog

2106 WHERE user='$user'

2107 AND tblrowid IN

2108 ($blogreadflagrowid,

2109 $blogcutoffflagrowid)),

2110 $basetime -- "0"

2111 );"`

2112 cat<<EOF

2113 `sql4interestblogs`

2114 WITH article_ctime as (

2115 SELECT id,blogid,author,max(val) ctime

2116 FROM article join article_s s using(id)

2117 WHERE s.key='ctime' AND s.val > '$deftime'

2118 GROUP BY id

2119 ), blog_title_owner as (

2120 SELECT blg.rid brid, id,

2121 max(case key when 'title' then val end) title,

2122 max(case key when 'owner' then val end) owner

2123 FROM interestblogs blg, blog_s using(id) group by id

2124 ), blogall as (

2125 SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid

2126 ), news as (

2127 SELECT brid, bl.id blid, bl.title, ctime,

2128 coalesce(al.time, '$deftime') atime,

2129 count(bl.id) "新着", bl.author

2130 FROM blogall bl

2131 LEFT JOIN

2132 (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al

2133 ON bl.brid=al.tblrowid

2134 WHERE atime < bl.ctime

2135 GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id

2136 LIMIT 10

2137 ) SELECT brid LINK, "新着",

2138 (SELECT count(*) FROM article WHERE blogid=blid) "総数",

2139 ctime, title,

2140 (SELECT gecos FROM gecoses WHERE name=author) gecos

2141 FROM news;

2142 EOF

2143 }

2144

2145 search_form() {

2146 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

2147 # $2(optional) = pre-input keywords

2148 help="(1)空白区切りの単語で本文検索

2149 (2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索

2150 @2016-0[1-6] → 2016年1月から6月

2151 @>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間

2152 @week → 最近一週間

2153 (3)#番号で記事ID検索

2154 (1)と(2)は組み合わせOK

2155 例: @2016-10-0[1-9] 芋煮

2156 → 2016年10月上旬でキーワード「芋煮」を含む記事検索

2157 ※クイズ板は検索対象から外されます。"

2158 auth=""

2159 placeholder="全記事からの検索"

2160 case "$1" in

2161 author=*)

2162 a=`echo "${1#author=}"|htmlescape`

2163 g=`gecos ${1#author=}`

2164 auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"

2165 placeholder="このユーザの書込検索"

2166 help="★★ $g さんの書き込みから検索します$nl$help"

2167 ;;

2168 grid=*)

2169 a=`echo "${1#grid=}"`; a=$((0 + $a))

2170 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

2171 placeholder="このグループからの検索"

2172 ;;

2173 esac

2174 inikwd="$2" # no need to htmlescape

2175 cat<<-EOF

2176 <div class="right">

2177 <form action="$myname">$auth

2178 <input type="text" name="kwd" value="$inikwd" title="$help"

2179 placeholder=" $placeholder " width="10" accesskey="k">

2180

2181 ${touchpanel:+<p class="help">$help</p>}

2182 <input type="hidden" name="stage" value="searchart">

2183

2184 </form>

2185 </div>

2186 EOF

2187 }

2188

2189 imgsrc_cache() (

2190 # $1 = directory for cache'ing

2191 # $2 = table (user_m or grp_m)

2192 # $3 = keycond (was: condition for choosingowner)

2193 # $4 = size : S = Small, M = Medium, O = Original

2194 dir="$1" tbl="$2"

2195 keycond="$3"

2196 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

2197 ORDER BY rowid DESC LIMIT 1"

2198 [ -d "$dir" ] || mkdir -p "$dir"

2199 tmpf=$tmpd/imgsrc_cache.$$

2200 case "$4" in

2201 [Ss]) size=S ;;

2202 [Oo]) size=O ;;

2203 *) size=M ;;

2204 esac

2205 # ImageCache filename storing schema:

2206 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

2207 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

2208 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

2209 valtype=`query "$sql0"`

2210 filename=${valtype%%//*}

2211 filetype=${valtype##*//file:}

2212 if [ x"$filename" = x"${filename%.*}" ]; then

2213 # If nor filename extension found, set it to image type

2214 case "$filetype" in

2215 image/*) filename=$filename.${filetype#image/} ;;

2216 esac

2217 fi

2218 cacheimg_S=$dir/S_$filename

2219 cacheimg_M=$dir/M_$filename

2220 cacheimg_O=$dir/$filename

2221 cacheimg=$dir/${size}_$filename

2222 sumfile="$dir/$filename.sum"

2223 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

2224 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2225 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2226 # if cache is fresh and has the same checksum,

2227 echo "<img src=\"$cacheimg\">"

2228 else

2229 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2230 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2231 fifo2=$fifo.2

2232 mkfifo $fifo $fifo2

2233 fmt=${filename##*.}

2234 ## [[ NOTE ]]

2235 ## a. convert oldimage newimage

2236 ## b. convert oldimage fmt:- | convert - newimage

2237 ## b is much smaller than a

2238 cat $tmpf | unhexize \

2239 | tee $fifo \

2240 | convert -define ${fmt}:size=${iconxy_M} \

2241 -resize ${iconxy_M}'>' - ${fmt}:- \

2242 | tee $fifo2 \

2243 | convert - "$cacheimg_M" &

2244 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2245 -resize ${iconxy_S}'>' - ${fmt}:- \

2246 | convert - "$cacheimg_S" &

2247 printf '%s' "<img src=\"data:${filetype},"

2248 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2249 echo '">'

2250 echo "$sum" > $sumfile

2251 fi

2252 ## Now preparing cache image, done.

2253 ## Store this information to DB

2254 stbl=${tbl%_m}_s # user_s or grp_s

2255 pkey=${keycond%%=*} # Primary Key name

2256 pval=${keycond#*=} # Primary Key value

2257 query <<-EOF

2258 REPLACE INTO $stbl($pkey, key, type, val)

2259 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2260 EOF

2261 )

2262

2263 showhome() {

2264 # $1=userRowIdToShow

2265 err showhome \$1=$1

2266 case "$1" in

2267 *@*) uname=`getvalbypkey user name "$1"` ;;

2268 *) uname=`getvalbyid user name $1` ;;

2269 esac

2270 ## err ShowHome: uname=$uname

2271 td=`getcachedir home/"$1"`

2272 gecos=`gecos "$uname"`

2273 ## err SH:gecos=$gecos

2274 GF_VIEWONLY=1

2275 cond="gname in (select gname from grp_mem where user='$uname')"

2276 search_form_args=""

2277 if [ x"$user" = x"$uname" ]; then

2278 if [ -z "$S4MASTERDB" ]; then

2279 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2280 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2281 elif [ -n "$S4MASTERURL" ]; then

2282 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2283 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2284 fi

2285 usermenu="$usermenu

2286 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2287 # Display folders

2288 sql="select count(id) from article_m where id

2289 in (select id from article where author='$user')

2290 and type like 'file:%';"

2291 ## err nfile-sql=`echo "$sql"`

2292 nfile=`query "$sql"`

2293 # err nfile=$nfile

2294 if [ $nfile -gt 0 ]; then

2295 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2296 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2297 fi

2298 else

2299 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2300 GROUP BY user;"`

2301 usermenu="<p>Last seen on $latestlog</p>"

2302 search_form_args="author=$uname"

2303 fi

2304 . ./s4-blog.sh

2305

2306 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2307 search_form "$search_form_args" > $sf

2308 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2309 | htmlescape > $tf

2310 { echo "<div class=\"noprofimg\">"

2311 viewtable $formdir/user.def user $1

2312 echo "</div>"

2313 } > $pf

2314

2315 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2316 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2317 imf=$tmpd/profimg.$$; touch $imf

2318 if [ -n "$img" ]; then

2319 if true; then

2320 tbl=user_m

2321 enticond="name='$uname'"

2322 imgsrc_cache "$td/main" user_m "$enticond" M

2323 else

2324 { printf '%s' "<IMG src=\"data:${img#file:},"

2325 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2326 | sed 's/$..$/%\1/g'

2327 echo '">'

2328 }

2329 fi > $imf

2330 fi

2331 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2332 val='$uname';"`

2333 listblog $uname > $bf

2334

2335 hometail=$tmpd/tail.$$

2336 mkfifo $hometail

2337

2338 #Calling listgroupbytable, originally here

2339

2340 (

2341 # Display Most Recent Entry

2342 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2343 shortval=${shortval:-val}

2344

2345 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2346 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2347 ctime,

2348 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2349 (SELECT gecos FROM gecoses

2350 WHERE name=(SELECT val FROM blog_s

2351 WHERE key='owner' AND id=b.id)) owner,

2352 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2353 FROM blog b

2354 JOIN

2355 (SELECT distinct blogid, a.id aid, max(val) ctime

2356 FROM article a, article_s s

2357 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2358 GROUP BY blogid ORDER BY val DESC LIMIT 50

2359 ) m

2360 ON b.id=m.blogid;"

2361 # This should be as follows

2362 : <<EOF

2363 WITH arts AS(

2364 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2365 a.blogid, a.id id, s.val ctime

2366 FROM article a NATURAL JOIN article_s s

2367 WHERE s.key = 'ctime' AND a.author='$user'

2368 GROUP by s.id

2369 )

2370 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2371 FROM arts a0

2372 JOIN

2373 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2374 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2375 ORDER BY ctime DESC LIMIT 50;

2376 EOF

2377

2378 cat<<-EOF

2379 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2380 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2381 <div class="lcto">

2382 `DT_VIEW=replyblog dumptable html blog`

2383 </div>

2384 EOF

2385 unset DT_SQL

2386 if [ x"$user" = x"$uname" ]; then

2387 # Display NEWS

2388 # 2016-06-26

2389 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2390 acclog blog $blogreadflagrowid

2391 # echo "全部既読にしました" | html p

2392 fi

2393 # 2016-02-19 Counting NEWS without using dumptable.

2394 sql=`listnewblogsql "$user"`

2395 # echo "$sql" > tmp/listnew

2396 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2397 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2398 cont=$((cont-1))

2399 ##err newcount=$cont

2400 if [ $cont -gt 0 ]; then

2401 #echo "全体の新着記事${cont}傑" | html h2

2402 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2403 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2404 cat<<-EOF | html form 'action="?home"'

2405 `cgi_checkbox readchk yes 'id="read"'`<label

2406 for="read">新着ふくめて全部読んだことにする</label>

2407 `cgi_submit '確定'`

2408 `cgi_hidden read yes`

2409 EOF

2410 echo "$new10 "

2411 echo "</div>"

2412 else # If news is 0, set log cut off flag

2413 acclog blog $blogcutoffflagrowid # for speed

2414 fi

2415 else # Not My Home ($user != $uname)

2416 : # DT_SQL=

2417 fi

2418 ) > $hometail & # Is background call safe to m4??

2419 #

2420 listgroupbytable $formdir/grp.def "$cond" $uname |

2421 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2422 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2423 -D_PROFIMG_="spaste(\`$imf')" \

2424 -D_BLOGS_="spaste(\`$bf')" \

2425 -D_SEARCH_="spaste(\`$sf')" \

2426 -D_NBLOG_="$nblog" \

2427 -D_GROUPS_="syscmd(\`cat')" \

2428 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2429 $layout/html.m4.html $layout/home.m4.html

2430

2431 # Record access log

2432 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2433 }

2434 commission() { # $1=grp-rowid $2=user-rowid

2435 contenttype; echo

2436 ## err commission: "$@"

2437 gname=`getgroupbyid $1`

2438 echo "グループ $gname 管理者委任" \

2439 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2440 if [ -n "$2" ]; then

2441 grp_reg_adm "$@"

2442 else

2443 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2444 fi

2445 }

2446 listgroupbytable() {

2447 # $1=deffile $2...=condition $3(optional)=uname

2448 tagline=`grep :tag: $1`;

2449 and="${2:+and }" where=${2:+where }

2450 href="<a href=\"$myname?grp+"

2451 echo '<div class="listgroup">'

2452 sql="select val from grp_s where key='tag' $and$2 group by val;"

2453 ## err ListGRP: query sql="$sql"

2454 for tag in `query "$sql"`

2455 do

2456 ## err ListGrp: tag=$tag

2457 tn=${tagline%%=${tag}*}

2458 tn=${tn##*[ :]}

2459 sql="select rowid||':'||gname as 'グループ名',説明 from

2460 (select (select rowid from grp g where g.gname=grp_s.gname)

2461 as rowid,

2462 gname,

2463 max(case key when 'gecos' then val end) as '説明',

2464 max(case key when 'tag' then val end) as 'tag',

2465 max(case key when 'mtime' then val end) as mtime from grp_s

2466 $where$2 group by gname having tag='$tag' order by mtime desc);"

2467 ## err PersonalGroupList= `echo $sql`

2468 echo "<h2>$tn</h2>"

2469 echo '<table class="b listgroup">'

2470 sq -header -html $db "$sql" \

2471 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2472 echo '</table>'

2473 done

2474 if [ -n "$S4WORLDLIST" -a -n "$3" ]; then

2475 err "peekgrpworlds($user) BEGIN: `gdate +%S.%03N`"

2476 peekgrpworlds mem:"$3"

2477 err "peekgrpworlds($user) END: `gdate +%S.%03N`"

2478 fi

2479 echo '</div>'

2480 }

2481 iconhref() (

2482 # $1=icon-file, $2=Href $3=title $4...=anchor

2483 data=`percenthex "$1"`

2484 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2485 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2486 href=$2; title=$3; shift 3

2487 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2488 )

2489 iconhref2() (

2490 # $1=icon-file, $2=Href $3=title $4...=anchor

2491 src=$1

2492 href=$2; title=$3; shift 3

2493 anchor=`echo $@|htmlescape`

2494 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2495 )

2496 listentry() (

2497 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2498 # Referring variable $iamowner=$grp to attach owner-request links

2499 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2500 cond='' hiddens=''

2501 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2502 if [ -z "$offset" ]; then

2503 offset=`getpar start`; offset=${offset%%[!0-9]*}

2504 offset=$((offset-1))

2505 fi

2506 offset=$((offset + 0)) # change to numeric forcibly

2507 [ $offset -lt 0 ] && offset=0

2508 limit=$listentlimit

2509 dir=`getcachedir "$1"`

2510 if [ x"$1" = x"user" ]; then

2511 hrb="$myname?home"

2512 deficon=person-default.png

2513 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2514 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2515 gcs=gecos

2516 else # if group

2517 hrb="$myname?grp"

2518 deficon=group-default.png

2519 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2520 gcs=name

2521 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2522 if [ -n "$tagline" ]; then

2523 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2524 ## err tagconv=$tagconv

2525 fi

2526 fi

2527 if [ ! -d $dir ]; then

2528 mkdir -p $dir

2529 fi

2530 if [ ! -s $dir/$deficon ]; then

2531 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2532 fi

2533 kwd=${2:-`getpar kwd`}

2534 if [ -n "$kwd" ]; then

2535 kwd=`echo $kwd | tr -d '";\n' | tr -d "'"`

2536 case "$kwd" in

2537 mem:*@*)

2538 byuser=${kwd#*mem:}

2539 qusr=`sqlquote "$byuser"`

2540 cond1="(a.gname IN (SELECT gname FROM grp_mem WHERE user=$qusr))"

2541 ;;

2542 esac

2543 if [ x"$1" = x"group" ]; then

2544 if [ -n "$cond1" ]; then

2545 enthead="`gecos "$byuser"|htmlescape` さんの所属"

2546 else

2547 cond1="(b.name like '%${kwd}%')"

2548 fi

2549 else

2550 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2551 fi

2552 fi

2553 tag=`getpar tag` tag2=`getpar tag2`

2554 if [ x"$tag" = x"NULL" ]; then

2555 tag="" tag2=""

2556 fi

2557 if [ -n "$tag$tag2" ]; then

2558 tag=${tag:-$tag2}

2559 qtag=`sqlquote "$tag"`

2560 cond2="tag=$qtag"

2561 fi

2562 if [ -n "$cond1$cond2" ]; then

2563 cond="$cond1${cond2:+ AND $cond2}"

2564 cond="WHERE ${cond# AND }"

2565 fi

2566

2567 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2568 # grpは呼出し元の動的スコープ変数でよくないな...

2569 ##qgrp=`sqlquote $grp`

2570 getgrp="(select gname from grp where rowid=${rowid:--1})"

2571 sql="select a.rowid, a.$link,

2572 coalesce(b.$gcs, a.$nm) as nick,

2573 quote(a.$nm) as qname,

2574 (SELECT val FROM ${tbl}_s

2575 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2576 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2577 || case when a.$nm in (select gname from grp)

2578 then printf('(%d名)',

2579 (select count(user) from grp_mem where gname=a.$nm))

2580 else ' <'||a.$nm||'>'

2581 end

2582 as name,

2583 b.tag,

2584 case when a.$nm in (select user from grp_adm

2585 where gname=$getgrp) then '管理者'

2586 when '$user' in (select user from grp_adm where gname=a.$nm)

2587 then 'ADMIN'

2588 when '$user' in (select user from grp_mem where gname=a.$nm)

2589 then 'Member'

2590 when '$iamowner' = '' then ''

2591 else ',not='||a.rowid end as ownerlink,

2592 CASE '$entity'

2593 WHEN 'グループ'

2594 THEN coalesce(

2595 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2596 'open')

2597 ||

2598 CASE WHEN '$user'

2599 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2600 THEN ' ismember'

2601 ELSE ''

2602 END

2603 ELSE 'user'

2604 END regmode

2605 from $tbl a left join

2606 (select $nm as name,

2607 max(case key when 'gecos' then val end) as gecos,

2608 max(case key when 'tag' then val end) as tag,

2609 max(case key when 'mtime' then val end) as mtime,

2610 max(case key when 'wtime' then val end) as wtime,

2611 max(case key when 'login' then val end) as login

2612 from ${tbl}_s group by $nm)

2613 b on a.$nm=b.name $cond $3

2614 order by b.wtime desc, b.login desc,

2615 b.mtime desc, b.tag desc, a.rowid asc"

2616 # Give precedence to newer maintained groups (2016-09-24)

2617 # Note that mtime is stored only in grp_s.

2618 ## err LE:sql.1="$sql"

2619 total=`query "with x as ($sql) select count(*) from x;"`

2620 echo "$enthead${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2621 echo '<div class="listentry">' # List-entry div

2622 # Show owner/member filter button

2623 METHOD=GET

2624 hiddens="$hiddens

2625 `cgi_hidden stage \"$stage\"`"

2626 if [ x"$tbl" = x"grp" ]; then

2627 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2628 fh="<select name=\"tag\">$nl"

2629 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2630 for l in $args; do

2631 val=${l#*=} tname=${l%=*}

2632 if [ x"$val" = x"$tag" ]; then

2633 s=" selected"

2634 selectedtags="(種別[${tname}]のみ)"

2635 else

2636 s=""

2637 fi

2638 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2639 done

2640 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2641 cat<<-EOF

2642 <form action="$myname" method="$METHOD">

2643 </form>

2644 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2645 for="ismembtn">参加中以外隠す</label>

2646 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2647 for="isadmbtn">管理者参加以外隠す</label>

2648 EOF

2649 # limit=3

2650 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2651 fi

2652 if [ $total -gt $limit -o $ -n "$S4WORLDLIST" -a x"$tbl" = x"grp" $ ]; then

2653 echo '<div>'

2654 METHOD=GET cgi_form $stage <<EOF

2655 $form

2656 <label>次の語を含む${entity}で検索:

2657 `cgi_text kwd "$kwd"`</label>

2658 $hiddens

2659 EOF

2660 echo '</div>'

2661 else

2662 echo $selectedtags | html p

2663 fi

2664 test -n "$kwd" && hiddens="$hiddens$nl`cgi_hidden kwd \"$kwd\"`"

2665 cat<<EOF

2666 <form action="$myname" method="$METHOD">

2667 <p>${total}件中の<input class="hidesub" type="text" name="start"

2668 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2669 <input type="submit" value="確定"></p>

2670 </form>

2671 EOF

2672 if [ $((offset+limit)) -lt $total ]; then

2673 nextbtn=$(

2674 cat<<EOF

2675 <div class="right clear"><form action="$myname" method="$METHOD">

2676 `cgi_submit 次の${limit}件`

2677 $hiddens

2678 `cgi_hidden offset $((offset + limit))`</form></div>

2679 EOF

2680 )

2681 fi

2682 if [ $offset -gt 0 ]; then

2683 prevbtn=$(

2684 cat<<EOF

2685 <form action="$myname" method="$METHOD">

2686 `cgi_submit 前の${limit}件`

2687 $hiddens

2688 `cgi_hidden offset $((offset - limit))`</form>

2689 EOF

2690 )

2691 fi

2692 pnbtn="$nextbtn$prevbtn"

2693 echo $pnbtn

2694

2695 ## err ListEntry: `echo "$sql"\;`

2696 # sq $db here??? 2016-11-28

2697 query "$sql limit $limit ${offset:+offset $offset};" \

2698 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2699 # err name=$name owner=$ownerp lnk=$lnk

2700 # err newlnk=$lnk regmode=$regmode

2701 icondir=$dir/$id

2702 # Pick up only last icon

2703 htmlname=`echo $name|htmlescape`

2704 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2705 <p class=\"tag _$tag\">$tag</p>" \

2706 | _m4 $tagconv

2707 if [ -n "$NOSPEEDUP" ]; then

2708 files=`getvalbyid $tbl profimg $id $icondir`

2709 if [ -n "$files" ]; then

2710 icon=`echo "$files"|tail -1`

2711 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2712 else

2713 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2714 fi

2715 elif [ -n "$icon" -a -s "$icon" ]; then

2716 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2717 else

2718 cond="$nm=$qname"

2719 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2720 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2721 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2722 # err "img=[$img]"

2723 if [ -n "$img" ]; then

2724 echo "<a href=\"$hrb+$lnk\">"

2725 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2726 echo "</a>"

2727 else

2728 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2729 fi

2730 fi

2731 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2732 echo "</div>"

2733 done

2734 echo "</div>" # End of List-entry div

2735 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2736 if [ -n "$kwd" -a x"$tbl" = x"grp" -a -n "$S4WORLDS" ]; then

2737 peekgrpworlds "$kwd"

2738 fi

2739 )

2740 listmember() {

2741 listentry user "$@"

2742 }

2743 listgroup() {

2744 listentry group "$@"

2745 }

2746 hexteams() { # $1=gname, $2(optional)=user

2747 cond=${2:+" AND user='$2'"}

2748 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2749 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2750 }

2751 showgroup() { # $1=group-rowid

2752 if [ -z "$1" ]; then

2753 grid=`getpar grid`

2754 grid=${grid%%[!0-9]*}

2755 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2756 else

2757 grid=$1

2758 fi

2759 grp=`getgroupbyid $grid`

2760 qgrp=`sqlquote "$grp"`

2761 htmlgrp=`echo "$grp"|htmlescape`

2762 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2763 if isgroup "$grp"; then

2764 tf=$tmpd/title.$$

2765 sf=$tmpd/search.$$

2766 bodyclass=`query "SELECT val FROM grp_s

2767 WHERE gname=$qgrp AND key='regmode';"`

2768 if ismember "$user" "$grp"; then

2769 ismember="ismember"

2770 bodyclass="$bodyclass${bodyclass:+ }ismember"

2771 else

2772 ismember="" # bodyclass="group"

2773 fi

2774 bodyclass="$bodyclass grouphome"

2775 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2776 echo "グループ $htmlgrp" > $tf

2777

2778 showgroupsub $formdir/grp.def "$grid" | \

2779 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2780 -D_FORM_="syscmd(\`cat')" \

2781 -D_BODYCLASS_="$bodyclass" \

2782 -D_DUMPTABLE_="" \

2783 $htmlheader $sf $layout/form+dump.m4.html

2784 # $htmlheader $layout/form+dump.m4.html

2785 # $htmlheader is defined in grp()

2786 else # if $grp is removed at par2table

2787 listgroup

2788 fi

2789 }

2790 showgroupsub() {

2791 # $1=def-file $2=group-rowid

2792 # Using $ismember

2793 rowid=$2

2794 grp=`getgroupbyid $2`

2795 qgrp=`sqlquote "$grp"`

2796 td=`getcachedir grp/"$2"`

2797 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2798 if [ -z "$rowid" ]; then

2799 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2800 #grp=`sq $db "select gname from grp where rowid=$grp"`

2801 echo "showgroupsub: invalid argument($1 $2)" | html p

2802 return

2803 fi

2804 val=`getvalbyid grp profimg $rowid $tmpd`

2805 enticond="gname=$qgrp"

2806 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2807 if [ -n "$img" ]; then

2808 cat<<-EOF

2809 <p class="groupimg">

2810 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2811 EOF

2812 fi

2813 echo "<div class=\"noprofimg\">"

2814 viewtable $1 grp $rowid

2815 echo "</div>"

2816 if isgrpowner "$user" "$grp"; then

2817 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2818 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2819 iamowner=$rowid

2820 colmd=" mode"

2821 fi

2822 if [ -n "$ismember" ]; then

2823 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2824 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2825 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2826 cat<<-EOF

2827 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2828 href="?blog+$rowid">グループの新規話題作成</a>

2829 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2830 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2831 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2832 <div class="fold clear">

2833 `cgi_checkbox send yes id="send"`<label

2834 for="send">グループ全員にメッセージ送信</label>

2835 <div>

2836 `cgi_textarea message "" "cols=60"`

2837 `cgi_submit 送信`

2838 `cgi_reset リセット`

2839 </div>

2840 `cgi_hidden grp $rowid`

2841 </div></form>

2842 EOF

2843 fi

2844 # 加入ボタン + 加入者リスト

2845 if [ -n "$ismember" ]; then

2846 ismem='checked' state="(参加中)"

2847 else

2848 nomem='checked' state="(現在非加入)"

2849 fi

2850 # このグループでの加入アドレス

2851 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2852 and key='email';"`

2853 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2854 ## and key='email';"

2855 ##err email=$eml

2856 cat <<EOF

2857 <div class="fold clear">

2858 `cgi_checkbox reg yes id="reg"`<label

2859 for="reg">自身の加入状態を操作する</label>$state

2860 <div>

2861 EOF

2862 cgi_form grp <<EOF

2863 <p>このグループに</p>

2864 <table class="b">

2865 <tr><th>メンバーとして</th><td>

2866 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2867 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2868 <tr><th>参加する場合のメイルアドレス<br>

2869 <small>(メインのアドレスとは違うものにする場合に記入<br>

2870 同じでよい場合は空欄に)</small></th>

2871 <td>`cgi_text email $eml`</td></tr>

2872 </table>

2873 `cgi_hidden grp $rowid`

2874 EOF

2875 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2876 echo "moderated (承認加入の)グループなので実際に参加できるのは

2877 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2878 fi

2879 echo '</div></div>'

2880 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2881 cat<<-EOF

2882 <div class="fold">

2883 <h2>話題一覧</h2>

2884 <form class="summary inline" action="$myname" title="$thelp">

2885 `cgi_hidden owner "$grp"`

2886 `cgi_hidden kwd "@week"`

2887 `cgi_hidden stage searchart`

2888 `cgi_submit "一週間のまとめ"`

2889 </form>

2890 <input type="checkbox" id="dtcheck" checked><label

2891 for="dtcheck">話題一覧表示</label>

2892 EOF

2893 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by 稼動状態, ctime desc"

2894 colstate="state:稼動状態:frozen=rowclass=凍結"

2895 frzbtn='<button class="toggle-frozen"></button>'

2896 DT_CHLD=article:blogid \

2897 DT_QOWNER="$qgrp" \

2898 DT_VIEW=replyblog dumptable html blog \

2899 "ctime title heading team notify:通知$colmd $colstate" "$cond" \

2900 | if [ -n "$iamowner" ]

2901 then

2902 sed -Ee "s,(<TD class=\"稼動状態\">).*(</TD>),\1$frzbtn\2,"

2903 else

2904 cat

2905 fi

2906 ## DO not convert to frzbtn when not admin

2907 echo "</div> "

2908

2909 getgname="(select gname from grp where rowid=$rowid)"

2910 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2911 cm="?commission+$rowid"

2912 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2913 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2914 # team list

2915 hexteams=`hexteams "$grp"`

2916 if [ -n "$hexteams" ]; then

2917 echo "チーム一覧" | html h2

2918 echo '<div class="dumptable"><table class="b">'

2919 sq $db -html -header<<-EOF

2920 SELECT val TEAM,

2921 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2922 MEMBERS

2923 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2924 EOF

2925 echo '</table></div>'

2926 fi

2927 }

2928 grp_getbodyclass() {

2929 # Get css class name for document.

2930 # `moderated' for moderated groups

2931 # `ismember' for groups where user belongs

2932 # $1=GroupName (w/o quote)

2933 # $user=userNameCurrentlyLogin

2934 ## err grp_getbodyclass: 1="$1"

2935 qgrp=`sqlquote "$1"`

2936 query<<-EOF

2937 SELECT coalesce(

2938 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2939 'open')

2940 ||

2941 CASE WHEN '$user'

2942 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2943 THEN ' ismember'

2944 ELSE ''

2945 END;

2946 EOF

2947 }

2948 grpaction() { # $1=group-rowid

2949 err GRP_ACTION:IN

2950 grid=${1:-`getpar grp`}

2951 grp=`getgroupbyid "$grid"`

2952 htmlgrp=`echo "$grp" | htmlescape`

2953 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2954 if [ -z "$grp" ]; then

2955 echo "無効な指定です。" | html p; return

2956 fi

2957 if ! ismember $user "$grp"; then

2958 echo "加入者のみに許可された操作です。" | html p; return

2959 fi

2960 isowner=""

2961 isgrpowner "$user" "$grp" && isowner="yes"

2962 err 2=$2 3=$3

2963 case "$2" in

2964 get:teamcsv)

2965 teamcsv "$grid"

2966 return

2967 ;;

2968 esac

2969 echo "グループ $grp 個別選択操作" \

2970 | _m4 -D_TITLE_="syscmd(\`cat')" \

2971 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2972 $layout/html.m4.html

2973

2974 usel=`getpar usel`

2975 if [ -n "$usel" ]; then

2976 uids=$(echo `echo $usel`|tr ' ' ',')

2977 ## err grpaction-1: grp=$grp, `echo $sql`

2978 text=`getpar text|tr -d '\r'`

2979

2980 rm=`getpar rm` cfm=`getpar confirm`

2981 ## err rm=$rm cfm=$cfm

2982 if [ x"$rm" = x"yes" ]; then

2983 if [ "$isowner" ]; then

2984 if [ x"$rm$cfm" = x"yesyes" ]; then

2985 # Eliminate

2986 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2987 for tbl in grp_mem grp_mem_s grp_mem_m; do

2988 sql="delete from $tbl $cond;"

2989 # echo "sql=$sql"

2990 query "$sql"

2991 err rmGRPuser "$sql"

2992 done

2993 num=`query "select count(*) from user where rowid in ($uids);"`

2994 #err num=$num

2995 if [ 0$num -gt 0 ]; then

2996 sql="select coalesce(b.val,a.name) from user a left join \

2997 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2998 # err `echo "$sql"`

2999 html pre<<EOF

3000 以下の${num}名のグループ $grp 登録を解除しました。

3001 `query "$sql"`

3002 EOF

3003 fi

3004 else

3005 echo "確認のチェックがないのでやめておきます。" | html p

3006 return

3007 fi

3008 else # not Group Owner

3009 echo "グループ管理者でないのでメンバー操作はできません。" | html p

3010 return

3011 fi

3012 cat<<EOF

3013

3014 EOF

3015 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

3016 if [ -z "$text" ]; then # if msg is empty

3017 echo "なにかメッセージを..." | html p

3018 return 0

3019 fi

3020 gecos=`gecos $user`

3021 safegc=`echo "$gecos" | tr -d '<>@,'`

3022 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3023 fromad=`myemail4group "$grp"`

3024 ###mail_from="$safegc <$fromad>"

3025 mail_from="$safegc <$user>" # TEST: 2020/5/13

3026 test -n `getpar sender` &&

3027 export SENDER=$user # TEST: 2020/5/15

3028 replyto=$fromad

3029

3030 ## Start parse of attachment files

3031 if [ -n "`getpar email`" ]; then

3032 ar=`getpar supprcpt`

3033 if [ -n "$ar" ]; then

3034 for a in $ar; do

3035 if checkdomain "$a"; then

3036 supprcpt="$supprcpt $a"

3037 else

3038 err "SupprcptErr=[$a] by $user"

3039 removercpt="$removercpt $a"

3040 fi

3041 done

3042 fi

3043 subj=`getpar subject`

3044 afiles=""

3045 for fn in `query "SELECT DISTINCT val FROM par WHERE var='image' AND sessid='$session';"`

3046 do

3047 f=$tmpd/$fn

3048 if [ -s $f ]; then

3049 afiles=$afiles"${afiles:+ }$f"

3050 fi

3051 done

3052 else

3053 preface=$(cat <<-EOF

3054 $url

3055 のグループ「$grp」のメンバーである $gecos さんから、

3056 あなた宛のメッセージです。

3057 ----------------------------------------------------------

3058 EOF

3059 )

3060 fi

3061 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

3062 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

3063 subj="${subj:-$gecos さんからのメッセージ}"

3064 REPLYTO=$replyto

3065 MAIL_FROM=$mail_from

3066 export REPLYTO SMAIL_TO MAIL_FROM

3067 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

3068 for r in $rcpts; do

3069 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

3070 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

3071 else

3072 # Show sender and rcpts address for guest

3073 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

3074 fi

3075 if [ -n "$afiles" ];then

3076 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

3077 else

3078 smail "$r" "$subj"

3079 fi <<EOF

3080 ${preface:+$preface$nl}$text

3081 EOF

3082 done

3083 if [ $? = 0 ]; then

3084 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

3085 sql="select coalesce(b.val, a.name) from

3086 (select name from user where rowid in ($uids)) a

3087 left join user_s b on a.name=b.name and b.key='gecos';"

3088 html pre<<EOF

3089 `query "$sql"`

3090 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

3091 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

3092 EOF

3093 err SendDone: `echo $sql`

3094 fi

3095 elif [ x"$rm" = x"commission" ]; then

3096 grp_reg_adm $grid $usel

3097 elif [ x"$rm" = x"addteam" ]; then

3098 team=`getpar team|sed "s/'/''/g"` # for single quotation

3099 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

3100 if [ x"$team" != x"$newteam" ]; then

3101 echo "チーム名に使えない文字を除去しました" | html p

3102 team=$newteam

3103 fi

3104 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

3105 cat<<-EOF | html p

3106 有効なチーム名を入力してください。

3107 カンマだけ、「なし」という名前は使えません。

3108 EOF

3109 echo "有効なチーム名を入力してください。" | html p

3110 else

3111 grp_add_team $grid "$team" $usel

3112 fi

3113 elif [ x"$rm" = x"rmteam" ]; then

3114 if [ x"yes" = x"`getpar teamconfirm`" ]; then

3115 rmteam=`getpar rmteam|sed "s/'/''/g"`

3116 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

3117 gname='$grp' AND user='$user' AND key='team'\

3118 AND val='$rmteam';\"`" ]; then

3119 grp_rm_team $grid "$rmteam" $usel

3120 else

3121 echo "所属していないチームの除去操作はできません。"|html p

3122 fi

3123 else

3124 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

3125 fi

3126 fi

3127 fi

3128 # POST count summary

3129 from=`getpar from`; to=`getpar to`

3130 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3131 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3132 fromtonote="<p title=\"Count the Number of Posts from-to\">POST/ACCESS集計: $from_input - $to_input</p>"

3133 # New entry

3134 sql="WITH mems AS (

3135 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3136 ON gm.user=g.name

3137 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3138 ), target_article AS (

3139 SELECT id FROM article_s

3140 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3141 ), posts AS (

3142 SELECT author, count(author) post

3143 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3144 WHERE blogid IN (SELECT id FROM blog_s

3145 WHERE key='owner'

3146 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3147 AND key='text'

3148 GROUP BY author

3149 ), teams AS (

3150 SELECT user, group_concat(val, ', ') team

3151 FROM mems m LEFT JOIN grp_mem_m gm ON m.name=gm.user

3152 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3153 AND key='team'

3154 GROUP BY user

3155 ), user_post AS (

3156 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3157 FROM mems m LEFT JOIN posts

3158 ON m.name=posts.author

3159 GROUP by m.rowid

3160 ), user_view AS (

3161 SELECT user vuser,count(user) cnt

3162 FROM tblaccesses

3163 WHERE user IN (

3164 SELECT user FROM grp_mem

3165 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid))

3166 AND tbl='blog'

3167 AND tblrowid IN (

3168 SELECT rowid FROM blog

3169 WHERE id IN (

3170 SELECT id FROM blog_s

3171 WHERE key='owner' AND val=(

3172 SELECT gname FROM grp WHERE rowid=$grid)))

3173 AND time BETWEEN '${from:-0000}' AND '${to:-9999}'

3174 GROUP BY user ORDER BY cnt

3175 )

3176 SELECT

3177 CASE

3178 WHEN (SELECT user FROM grp_adm

3179 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3180 AND user=up.name) IS NOT NULL

3181 then 'k'

3182 ELSE ''

3183 END || rowid || ','

3184 || rtrim(substr(name, 1, instr(name, '@')), '@') || ','

3185 || gecos NAME,

3186 post POST,

3187 (coalesce((SELECT cnt FROM user_view WHERE vuser=name), 0)) ACCESS,

3188 team _TEAM_

3189 FROM user_post up LEFT JOIN teams t

3190 ON up.name=t.user

3191 ORDER BY up.name;"

3192 ## Want to count ACCESS with JOIN, but failed to produce user-unique rows

3193 ## err grpaction: "`echo \"$sql\"`"

3194 tf=$tmpd/title.$$

3195 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3196 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3197 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3198 for=\"cmadmin\">管理者委任</label>

3199 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3200 </p></div>"

3201 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3202 title=\"Shortcut: g${nl}Remove from the Group\"

3203 for=\"conf\">GRP登録解除</label>

3204 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3205 <p>この操作による通知は本人に行きません。

3206 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3207 </div>"

3208 submitBTN=`cgi_submit 送信`

3209 # Get team list to which current user belongs into $hexteams

3210 allhexteams=$(hexteams "$grp")

3211 if [ -n "$isowner" ]; then

3212 myhexteams="$allhexteams" # admin can remove all teams' attr

3213 else

3214 myhexteams=$(hexteams "$grp" "$user")

3215 fi

3216 if [ -n "$isowner" -a -n "$allhexteams" ]; then

3217 gettingcsv="<p>Download: <a href=\"?getteamcsv+$grid\">Team.csv</a> (Zoom Breakout Room 事前割り当てに使えます), <a href=\"?getteamcsv+$grid+name\">Team-with-name.csv</a>(参照用名前付)</p>"

3218 fi

3219 if [ -n "$myhexteams" ]; then

3220 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3221 title=\"Shortcut: s${nl}Strip a team tag from\"

3222 for=\"cmrmteam\">チーム属性除去</label>

3223 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3224 を除去します: `cgi_checkbox teamconfirm yes` 確認 $submitBTN

3225 <p>この操作による通知は本人に行きません。

3226 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3227 </div>

3228 "

3229 fi

3230 stf=$tmpd/selteam.$$

3231 cgi_select_h selteam "5445414d" $allhexteams > $stf

3232 b1='<label> <input type="checkbox" name="usel" value="'

3233 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3234 br='<span id="reverse" title="Reverse Selection"></span>'

3235 #b2='"> <span>' b3='</span></label>'

3236 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

3237 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

3238 lnk='"> <span>\5</span></label> [<a href="?home+\3">HOME</a>]'

3239 cgi_form grpaction<<EOF \

3240 | sed -E \

3241 -e "s|^(<TR><TD)>(k?)([0-9]*),([^,]+),([^<]*)|\1 title=\"\4\">\2$b1\3$lnk|" \

3242 -e 's/^(<TR><TD[^>]*>)k(<label)/\1\2 class="admin"/' \

3243 -e "s|^(<TR><TH>)(NAME)|\1$br \2|" \

3244 | _m4 -D_TITLE_="spaste(\`$tf')" \

3245 -D_SUBTITLE_="チェック後操作ボタン" \

3246 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3247 $layout/form+dump.m4.html \

3248 | _m4 -D_TEAM_="spaste(\`$stf')"

3249 <p>下でチェックした人を対象として:</p>

3250 <div class="foldtabs">

3251 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3252 title="Shortcut: a${nl}Add a team tag to"

3253 for="cmteam">同じチーム属性を付与</label>

3254 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3255 `cgi_datalist_h teams $allhexteams`$submitBTN

3256 </div>

3257 ${rmteammsg}

3258 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3259 title="Shortcut: d${nl}DirectMail to"

3260 for="sendmsg" title="Direct Message">DM送信</label>

3261 <div>

3262 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3263 title="Using email format">email書式を使う</label>

3264 <div class="folded">

3265 <table>

3266 <tr><td>From: </td><td>$user</td></tr>

3267 <tr><td>このFrom:で送る</td>

3268 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3269 </td></tr>

3270 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3271 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3272 <tr><td>ファイル添付: </td>

3273 <td>`cgi_file image "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3274 </table>

3275 <p>(下記一覧から1人以上選択していない場合は送れません$submitBTN)</p>

3276 </div>

3277 <div>本文:`cgi_textarea text "" cols=72`

3278 </div>

3279 </div>

3280 ${isowner:+$cmmsg$excmsg}

3281 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3282 </div>

3283 <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote

3284 <table class="td2r td3r thl">

3285 `sq $db -header -html "$sql"`

3286 </table>

3287 `cgi_hidden grp $grid`

3288 `cgi_hidden myuid $myuid id="myuid"`

3289 EOF

3290 }

3291 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3292 # Create TEMPORARY VIEW

3293 query<<EOF

3294 CREATE TEMPORARY VIEW writeusers AS

3295 SELECT DISTINCT author FROM article

3296 WHERE id in (

3297 select id from article where blogid=(select id from blog where rowid=$1)

3298 );

3299 CREATE TEMPORARY VIEW movablegroups AS

3300 SELECT g.rowid growid , g.gname

3301 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3302 ON grp.gname=gm.gname -- そのユーザが属している

3303 AND user='$user') g -- グループに絞る

3304 WHERE (SELECT author FROM writeusers

3305 EXCEPT

3306 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3307 IS NULL;

3308 $2

3309 EOF

3310 }

3311 sql4readableblogs() {

3312 # Create view of blogs that can be readable to $user

3313 # Blog is readable when:

3314 # 1: blog owner is an user

3315 # 2: else, 2.1: owner-group where the $user belongs

3316 # 2.2: else, owner-group is not moderated

3317 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3318 cat<<EOF ## | tee tmp/sql.out

3319 CREATE TEMPORARY VIEW readableblogs AS

3320 SELECT blog.rowid rid, id, author

3321 FROM blog

3322 NATURAL JOIN

3323 (SELECT id,

3324 max(CASE key WHEN 'owner' THEN val END) owner,

3325 max(CASE key WHEN 'mode' THEN val END) mode

3326 FROM blog_s GROUP by id) bs

3327 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3328 THEN 1 -- blog owner is an user, READABLE

3329 WHEN (SELECT val FROM grp_s

3330 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3331 AND

3332 (SELECT user FROM grp_mem

3333 WHERE gname=bs.owner AND user='$user') IS NULL

3334 THEN 0

3335 WHEN mode IN ('quiz', 'enquete')

3336 THEN 0 -- "quiz" mode blog cannot be searched

3337 ELSE 1

3338 END;

3339 EOF

3340 }

3341 mvteamform() {

3342 owner=$1

3343 hexteams=$(hexteams "$owner" "$user")

3344 test -z "$hexteams" && return

3345 none="`echo なし|hexize`"

3346 cat<<-EOF

3347 <!-- <div class="fold">

3348 `cgi_checkbox mv2team send id="mv2team"`<label

3349 for="mv2team">この話題を以下のチームのものにする</label>

3350 <div> -->

3351 <p>この話題をチーム所有にする:

3352 チーム: `cgi_select_h mv2team $none $hexteams`</p>

3353 </form></div></div>

3354 EOF

3355 }

3356 editheading() { # $1=rowid-of-heading

3357 rowid=${1%%[!A-Z0-9a-z_]*}

3358 if [ -z "$rowid" ]; then

3359 echo "話題番号が未指定です。" | html p

3360 return

3361 fi

3362 owner=`getvalbyid blog owner $rowid`

3363 title=`getvalbyid blog title $rowid`

3364 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3365 | _m4 -D_TITLE_="修正" \

3366 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3367 -D_BLOGS_="" -D_DUMPTABLE_="" \

3368 -D_FORM_="syscmd(\`cat')" \

3369 $layout/html.m4.html $layout/form+dump.m4.html

3370 # Move to group

3371 if isuser "$owner"; then

3372 crview4article $rowid

3373 n=`query "SELECT count(*) FROM writeusers;"`

3374 ## err N=$n

3375 if [ $((n)) -gt 0 ]; then

3376 ## err ROWID=$rowid

3377 sql="SELECT growid || ':' || gname FROM movablegroups;"

3378 cat<<-EOF

3379 <div class="fold">

3380 `cgi_checkbox mv send id="mv"`<label

3381 for="mv">この話題をグループ所有に移動する</label>

3382 <div>

3383 <form action="?mvart" method="POST" enctype="multipart/form-data">

3384 移動先グループ:

3385 <select name="mv2grp">

3386 EOF

3387 query ".mode html"

3388 query<<-EOF |

3389 $sql

3390 .mode list

3391 EOF

3392 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3393 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3394 cat<<-EOF

3395 </select>

3396 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3397 そのグループに加入しているものに限られます)</p>

3398 <p>`cgi_checkbox cfm yes`<label>確認

3399 (この操作は元に戻すことができません)</label></p>

3400 `cgi_hidden blogrowid $rowid`

3401 `cgi_submit 移動`

3402 `cgi_reset Reset`

3403 </form>

3404 </div>

3405 </div>

3406 EOF

3407 fi

3408 # end of isuser "$owner"

3409 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3410 [ -n "$hexteams" ];}; then

3411 none="`echo なし|hexize`"

3412 cat<<-EOF

3413 <div class="fold">

3414 `cgi_checkbox mv2team send id="mv2team"`<label

3415 for="mv2team">この話題を以下のチームのものにする</label>

3416 <div><p>現在の所属チーム設定:

3417 `query "SELECT

3418 coalesce((SELECT val FROM blog_s

3419 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3420 AND key='team'),

3421 ':なし');"`</p>

3422 <form action="?mvart" method="POST" enctype="multipart/form-data">

3423 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3424 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3425 `cgi_hidden blogrowid $rowid`<br>

3426 `cgi_submit 移動`

3427 `cgi_reset Reset`

3428 </form></div></div>

3429 EOF

3430 fi

3431 }

3432 mvart() { # move diary to some group or team

3433 # or move blog of group to team which belong to the group

3434 blogrowid=`getpar blogrowid`

3435 cfm=`getpar cfm`

3436 ##### echo move blog:$blogrowid to $mv2grp | html p

3437 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3438 . ./s4-blog.sh

3439 if [ -z "$blogrowid" ]; then

3440 echo "無効な指定です(mvart)。" | html p

3441 return

3442 elif [ x"$cfm" != x"yes" ]; then

3443 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3444 elif { mv2grp=`getpar mv2grp`

3445 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3446 [ -n "$mv2grp" ]; }; then

3447 crview4article $blogrowid

3448 ########## TRANSACTION BEGIN

3449 query "BEGIN;"

3450 n=`query "SELECT count(*) FROM writeusers;"`

3451 ## err Nwriteuser=$n

3452 if [ $((n)) -gt 0 ]; then

3453 query<<-EOF

3454 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3455 WHERE key='owner'

3456 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3457 AND $mv2grp IN (SELECT growid FROM movablegroups);

3458 EOF

3459 fi

3460 query "END;"

3461 ########## TRANSACTION END

3462 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3463 [ -n "$mv2team" ];}; then

3464 # blog owner can move it to ANY team

3465 case "$mv2team" in

3466 'なし')

3467 cat<<-EOF

3468 DELETE FROM blog_s

3469 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3470 AND key='team';

3471 EOF

3472 ;;

3473 "") ;;

3474 *)cat<<-EOF

3475 BEGIN;

3476 REPLACE INTO blog_s(id, key, val)

3477 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3478 'team', '$mv2team');

3479 REPLACE INTO blog_s(id, key, val)

3480 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3481 'notify', 'all'); -- Change notify to all

3482 END;

3483 EOF

3484 esac | query

3485 fi

3486 blog_reply $blogrowid

3487 echo yes | html p

3488 }

3489 editart() { # $1=article-rowid $2=blogrowid

3490 rowid=${1%%[!A-Z0-9a-z_]*}

3491 blogrowid=${2%%[!A-Z0-9a-z_]*}

3492 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3493 echo "表示する記事番号が未指定です。" | html p

3494 return

3495 fi

3496 owner=`getvalbyid blog owner $blogrowid`

3497 title=`getvalbyid blog title $blogrowid`

3498 author=`getvalbyid article author $rowid`

3499 ## err EDITart: owner=$owner, author=$author

3500 if isgrpowner "$user" "$owner"; then

3501 : EDIT OK

3502 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3503 echo "本人か所有者しか編集できません." | html p

3504 return

3505 fi

3506 aid=`query "select id from article where rowid=$rowid;"`

3507 tmpout=$tmpd/editart.$$.out

3508 GF_ACTION="?replyblog+$blogrowid#$aid" \

3509 edittable $formdir/article.def article $rowid \

3510 > $tmpout

3511 rm -f /tmp/editart.out

3512 # Cannot use pipelining to m4 with genform() because of stdin stack

3513 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3514 -D_FORM_="syscmd(cat $tmpout)" \

3515 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3516 -D_BLOGS_= -D_DUMPTABLE_= \

3517 $layout/html.m4.html $layout/form+dump.m4.html

3518 }

3519 send2mem() {

3520 rowid=`getpar grp`

3521 rowid=${rowid%%[!0-9]*} # Cleaning

3522 if [ -z "$rowid" ]; then

3523 echo "グループが未指定です。" | html p

3524 return

3525 fi

3526 message=`getpar message`

3527 if [ -z "$message" ]; then

3528 echo "文章を入れてください。" | html p

3529 return

3530 fi

3531 grp=`getgroupbyid $rowid`

3532 members=`collectemail "$grp"`

3533 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3534 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3535 mailfrom="`gecos "$user"` <$mailfrom>"

3536 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3537 msg=$(cat<<-EOF

3538 $urlbase?grp+$rowid

3539 グループ $grp に所属する

3540 `gecos $user` さんよりメッセージ:

3541

3542 $message

3543 EOF

3544 )

3545 # smail rcpt subj (file)

3546 for m in $members; do

3547 echo "$msg" |

3548 MAIL_FROM=$mailfrom \

3549 SENDER=$noreply \

3550 REPLYTO=$mailfrom \

3551 smail "$m" "$sj"

3552 done

3553 cat<<EOF

3554 <p>以下のユーザに送信しました。</p>

3555 <pre>

3556 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3557 </pre>

3558 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3559 EOF

3560 }

3561 joingrpadmit() {

3562 # $1=yes/no $2=session-key

3563 if [ -z "$2" ]; then

3564 echo "bye bye" | html p; return

3565 fi

3566 t_usr=`session=$2 getpar adduser`

3567 t_grp=`session=$2 getpar group`

3568 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3569 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3570 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3571 echo "無効な加入依頼です。" | html p

3572 echo "有効期限が切れたか、

3573 他の管理者がいる場合は処理済みの可能性があります。" | html p

3574 return

3575 fi

3576 if ! isgrpowner "$user" "$t_grp"; then

3577 echo "グループ管理者のみの機能です。" | html p; return

3578 fi

3579 case $1 in

3580 yes) joingrp "$t_grp" "$t_usr" yes ;;

3581 no) joingrp "$t_grp" "$t_usr" no ;;

3582 *)

3583 echo "無効な指定です($1)。" | html p

3584 return ;;

3585 esac

3586 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3587 rcpts="`getgroupadminmails "$t_grp"` $user"

3588 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3589 body="に

3590 $t_usr

3591 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3592 しました。"

3593 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3594 query "delete from session where id='$2';"

3595 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3596 }

3597

3598 joingrprequest() {

3599 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3600 jss="joingrp-`date +%s`-`genrandom 12`"

3601 addsession $jss +${memoplimitdays}days

3602 grpadmins=`getgroupadmins "$1"`

3603 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3604 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3605 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3606 $url

3607 $user さんから

3608 グループ $1

3609 に加入依頼がありました。

3610

3611 承認する:

3612 $urlbase?joingrpadmit+yes+$jss

3613

3614 白紙に戻す:

3615 $urlbase?joingrpadmit+no+$jss

3616 EOF

3617 echo "管理者に加入依頼を出しました。

3618 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3619 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3620 }

3621 joingrp() {

3622 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3623 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3624 if isgrpowner "$user" "$1"; then

3625 isowner="yes"

3626 elif [ -n "$5" ]; then

3627 isowner="yes"

3628 else

3629 isowner=""

3630 fi

3631 ## err jg:isgrpowner: isowner="$isowner"

3632 if [ -n "$isowner" ]; then

3633 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3634 elif [ x"$2" != x"$user" ]; then # if user is not login user

3635 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3636 return

3637 elif [ x"$3" = x"no" ]; then

3638 : # Do not pursue those who leave

3639 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3640 : # Member can change own email address for the joining moderated group

3641 else # adding user is $user itself

3642 case `getgroupattr "$1" regmode` in

3643 moderated)

3644 joingrprequest "$@" # Request only

3645 return

3646 ;;

3647 *)

3648 ;;

3649 esac

3650 fi

3651 qgname=`sqlquote "$1"`

3652 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3653 cond="where gname=$qgname and user='$2'"

3654 if [ x"$3" = x"yes" ]; then

3655 query "replace into grp_mem values($qgname, '$2');"

3656 # Notify joingrp to admin

3657 action="に加入しました。"

3658 if [ -n "$4" ]; then

3659 if msg=`emaildomaincheck "$4"`; then

3660 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3661 'string', '$4', NULL);"

3662 else

3663 echo $msg

3664 fi

3665 else

3666 query "delete from grp_mem_s $cond and key='email';"

3667 fi

3668 if [ -n "$5" ]; then # as ADMIN

3669 # Coming here means newly created group

3670 sql="select case\

3671 when (select count(*) from grp_mem where gname=$qgname)=1\

3672 then (select user from grp_mem\

3673 where gname=$qgname and user='$user')\

3674 else '' end; "

3675 err NewGrpChk: $sql

3676 if [ -n "`query \"$sql\"`" ]; then

3677 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3678 query "replace into grp_adm values($qgname, '$user');"

3679 fi

3680 fi

3681 else

3682 query "begin;

3683 delete from grp_mem $cond;

3684 delete from grp_mem_s $cond;

3685 delete from grp_mem_m $cond;

3686 delete from grp_adm $cond;

3687 delete from grp_adm_s $cond;

3688 delete from grp_adm_m $cond;

3689 end;"

3690 action="から脱退しました。"

3691 fi

3692 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3693 $url?grp+$grid

3694 $user (`gecos $user`)さんが

3695 グループ $1

3696 $action

3697 EOF

3698 }

3699 grp_add_team() (

3700 # $1=grp-rowid $2=team $3...=user-rowid(s)

3701 grp=`getgroupbyid $1`

3702 team=$2; shift; shift

3703 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3704 { echo "BEGIN;"

3705 for user; do

3706 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3707 '$grp',\

3708 (SELECT name FROM user WHERE rowid=$user),\

3709 'team', 'string', '$team');"

3710 done

3711 echo "END;"

3712 } | query

3713 )

3714 grp_rm_team() (

3715 # $1=grp-rowid $2=team $3...=user-rowid(s)

3716 grid=$1

3717 qgrp=$(sqlquote "`getgroupbyid $grid`")

3718 team=$2; shift; shift

3719 [ -z "$grid" -o -z "$team" ] && return

3720 { echo "BEGIN;"

3721 for user; do

3722 echo "DELETE FROM grp_mem_m\

3723 WHERE gname=$qgrp \

3724 AND user=(SELECT name FROM user WHERE rowid=$user)\

3725 AND key='team' AND val='$team';"

3726 done

3727 cat<<-EOF

3728 DELETE FROM blog_s

3729 WHERE rowid=(

3730 SELECT rowid

3731 FROM blog_s a

3732 WHERE key='team'

3733 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3734 AND NOT EXISTS (SELECT * FROM grp_mem_m

3735 WHERE key='team' AND val=a.val -- a.val=team

3736 AND gname = (SELECT val FROM blog_s b

3737 WHERE a.id=b.id AND key='owner')

3738 ));

3739 EOF

3740

3741 echo "END;"

3742 } | query

3743 )

3744 grp_reg_adm() {

3745 # $1=grp-rowid $2...=user-rowid

3746 grid=$1

3747 grp=`getgroupbyid "$1"`

3748 if [ -z "$grp" ]; then

3749 echo "無効なグループIDです" | html p; return

3750 fi

3751 if ! isgrpowner "$user" "$grp"; then

3752 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3753 fi

3754 shift

3755 for urid; do

3756 newadm=`query "select name from user where rowid=$urid;"`

3757 if [ -z "$newadm" ]; then

3758 echo "指定ユーザIDがおかしいようです。" | html p; return

3759 fi

3760 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3761 err ismember $newadm $grp

3762 if ismember $newadm "$grp"; then

3763 # OK, go ahead

3764 getgname="(select gname from grp where rowid=$grid)"

3765 query "replace into grp_adm values($getgname, '$newadm');"

3766 # confirm insertion

3767 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3768 if [ -n "`query \"$sql;\"`" ]; then

3769 echo "追加完了: $newadm" | html p

3770 else

3771 echo "追加失敗($1 $urid)" | html p

3772 fi

3773 fi

3774 showgroup $grid

3775 done

3776 }

3777 dt_colhack() {

3778 # FROM: <TD>xxx:::yyy</TD>

3779 # TO: <TD class="xxx">yyy</TD>

3780 sed -Ee 's,<TD>([^:<"]+)'$asdelim'([^<]*)(</TD>|$),<TD class="\1">\2\3,g'

3781 }

3782 dt_rowhack() {

3783 # From: <TR>

3784 # ....

3785 # <TD>rowclass=foo</TD>

3786 # </TR>

3787 # To: <TR class="foo">....<TD>foo</TD></TR>

3788 sed -e '

3789 /^<TR>/ {

3790 :loop

3791 s/\n//

3792 N

3793 /<\/TR>/ {

3794 s/\n//

3795 s,^<TR>$.*$<TD$[^>]*$>rowclass=$.*$$</TD></TR>$,<TR class="\3">\1<TD\2>\3\4,

3796 n

3797 }

3798 $q

3799 b loop

3800 }'

3801 }

3802 dumptable() {

3803 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3804 # textのフィールドだけ全てダンプにしたほうがいいか

3805 # $DT_VIEW sets link

3806 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3807 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3808 VIEW=${DT_VIEW-replyblog}

3809 if [ -n "$VIEW" ]; then

3810 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3811 fi

3812 sqlfile=$tmpd/dump.sql

3813 : > $sqlfile # ensure to be empty

3814 printf '.mode html\n.header 1\n' > $sqlfile

3815 # $DT_CHLD=ChildTable:BindColumn

3816 if [ -n "$DT_CHLD" ]; then

3817 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3818 cat<<-EOF >> $sqlfile

3819 -- presql

3820 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3821 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3822 EOF

3823 # Speed up counting of new articles

3824 cat<<-EOF >> $sqlfile

3825 -- presql2

3826 DROP TABLE IF EXISTS _counts;

3827 CREATE TEMPORARY TABLE _counts AS

3828 SELECT $_i, count($_i) cnt

3829 FROM $_t GROUP BY $_i;

3830 /* Prepare NEW count table */

3831 CREATE TEMPORARY TABLE _target AS

3832 SELECT b.rowid trowid, b.id

3833 FROM "$2" b JOIN "$2_s" s

3834 ON b.id=s.id AND s.key='owner'

3835 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3836

3837 DROP TABLE IF EXISTS _children;

3838 CREATE TEMPORARY TABLE _children AS

3839 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3840 FROM (SELECT t.trowid, t.id $_i, a.id

3841 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3842 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3843

3844 DROP TABLE IF EXISTS _news;

3845 DROP VIEW IF EXISTS _news;

3846

3847 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3848 -- INSERT INTO _news

3849 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3850 CREATE TEMPORARY VIEW _news AS

3851 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3852 FROM (SELECT DISTINCT id FROM _target)

3853 a LEFT JOIN

3854 (SELECT $_i, count(ctime) newcnt

3855 FROM _children x

3856 WHERE ctime > coalesce((SELECT time from myacclog

3857 WHERE tblrowid=x.trowid),

3858 '1970-01-01')

3859 GROUP BY $_i) b

3860 ON a.id=b.$_i;

3861 EOF

3862 # REMOVE next line until 2019/5/1

3863 cntall="(select count($_i) from $_t where $_i=a.id)"

3864 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3865 # REMOVE next assignment until 2019/5/1

3866 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3867 and id in (select id from $_t where $_i=a.id) \

3868 and val > coalesce((select time from myacclog where \

3869 tblrowid=a.rowid),\

3870 '1970-01-01'))"

3871 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3872 cnt="$cntnew as '新着', $cntall as '総数',"

3873 dt_class=" td2r td3r dumpblogs"

3874 fi

3875 # Construct join expression

3876 eav="" scols=""

3877 pk=`gettblpkey $2`

3878 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3879 substr=${substr:-%s}

3880 for col in ${3:-`gettbl_s_cols $2`}; do

3881 valvar=val

3882 fromtbl=b

3883 if gettblcols "$2" | grep -w "$col" >/dev/null 2>&1; then

3884 # If $col belongs to master table

3885 fromtbl=a; col=${col#a.}

3886 fi

3887 case $col in

3888 gecos) scols="$scols${scols:+, }${col#}"

3889 continue ;; # built-in column name

3890 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3891 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3892 case "$as" in

3893 *:*=*) cnd=${as#*:}

3894 h=${cnd%%=*} v=${cnd#*=}

3895 h=`sqlquotestr "$h"`

3896 v=`sqlquotestr "$v"`

3897 if [ x"$fromtbl" = x"b" ]; then

3898 valvar="CASE val WHEN $h THEN $v END"

3899 else

3900 valvar="$h"

3901 fi

3902 as=${as%%:*} ;;

3903 esac

3904 ;;

3905 *) as=${col} ;;

3906 esac

3907 ss=`printf "$substr" "$valvar"`

3908 if [ x"$fromtbl" = x"b" ]; then

3909 eav=$eav${eav:+,}" \"$as$asdelim\"||coalesce(max(case key when '$col' then $ss end), '') as $as"

3910 else

3911 eav=$eav${eav:+,}" \"$as$asdelim\"||$ss as $as"

3912 fi

3913 scols="$scols${scols:+, }${fromtbl}.$as"

3914 done

3915 #case author when '$user' then a.rowid else '---' end as ID,

3916 if [ -n "$DT_SQL" ]; then

3917 echo "$DT_SQL"

3918 else

3919 cat<<-EOF

3920 SELECT a.rowid as LINK, $cnt $scols

3921 FROM $2 a LEFT JOIN

3922 (SELECT $pk,$eav,

3923 max(CASE key

3924 WHEN 'owner'

3925 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3926 as gecos

3927 FROM ${2}_s c GROUP BY $pk)

3928 b ON a.$pk=b.$pk $4;

3929 EOF

3930 fi >> $sqlfile

3931 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3932 sqlog<<-EOF

3933 *** SQL-file: $sqlfile ***

3934 `cat $sqlfile`

3935 EOF

3936 printf '.mode list\n.header 0\n' >> $sqlfile

3937 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_colhack | dt_rowhack

3938 <div>

3939 <div class="dumptable">

3940 <table class="b$dt_class">

3941 `query ".read $sqlfile"`

3942 </table>

3943 </div>

3944 </div>

3945 EOF

3946 }

3947

3948 clean-orphaned() {

3949 # This shoud be done by foreign_key rules, but some db lack them

3950 query<<-EOF

3951 -- Find blogs that have no parent

3952 WITH orphanedblog AS (

3953 SELECT blog.id,val FROM blog JOIN blog_s bs

3954 ON blog.id=bs.id AND key='owner'

3955 WHERE val NOT IN (SELECT gname FROM grp)

3956 AND val NOT IN (SELECT name FROM user)

3957 ) -- Remove them

3958 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

3959

3960 -- Find articles that have no parent blog

3961 WITH orphanedarticle AS (

3962 SELECT id FROM article

3963 WHERE blogid NOT IN (SELECT id FROM blog)

3964 ) -- Remove them

3965 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

3966 EOF

3967 }

3968

3969 clearcachedir() (

3970 td=`getcachedir "$1"`

3971 err td=$td: ls- `ls $td`

3972 if [ -w "$td/image.1" ]; then

3973 err Removing td=$td

3974 rm -fr $td/ # Clear icon-image cache!

3975 fi

3976 )

3977

3978 par2table() (

3979 # copy current parameters of par into destination table

3980 # $1=definition-file

3981 # Using $user and $session

3982 # Return value:

3983 # 0: Stored successfully

3984 # 1: Insufficient fillings

3985 # 2: No permission to modify the record

3986 # 3: Invalid rowid

3987 # 4: SUCCESS to delete

3988 # 5: Stop deletion for lack of confirm check

3989 # 6: Password length too short

3990 # 7: Password mismatch

3991 # 8: Old password incorrect

3992 # 9: Duplicated post

3993 rowid=`getpar rowid`

3994 if [ ! -e $1 ]; then

3995 echo "テーブル定義ファイルが見付かりません" | html p

3996 exit 1

3997 fi

3998 tbl=${1%.def}

3999 tbl=${tbl##*/}

4000 if [ -n "$rowid" ]; then # Modify existing entry

4001 if [ x"$tbl" = x"user" ]; then

4002 rowowner=`query "select name from $tbl where rowid=$rowid;"`

4003 elif [ x"$tbl" = x"grp" ]; then

4004 sql="select gname from $tbl where rowid=$rowid;"

4005 ##err p2t:grp:q $sql

4006 isgrpowner "$user" "`query $sql`" && rowowner=$user

4007 elif [ x"$tbl" = x"blog" ]; then

4008 # Check if owner in blog_s

4009 blogowner=`getvalbyid blog owner "$rowid"`

4010 if isgrpowner "$user" "$blogowner"; then

4011 rowowner=$user

4012 else

4013 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4014 fi

4015 else

4016 # 2016-12-05 There's no owner column in $tbl (need confirmation)

4017 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4018 fi

4019 ### err rowowner=$rowowner

4020 if [ x"$user" != x"$rowowner" ]; then

4021 echo "他人のレコードはいじれないの" | html p

4022 return 2

4023 elif [ -z "$rowowner" ]; then

4024 echo "指定したレコードはないみたい" | html p

4025 return 3

4026 fi

4027 rm=`getpar rm` cfm=`getpar confirm`

4028 # Editing existent entry

4029 if [ x"$rm" = x"yes" ]; then

4030 if [ x"$rm$cfm" = x"yesyes" ]; then

4031 query "delete from $tbl where rowid=$rowid;"

4032 clearcachedir "$tbl/$rowid"

4033 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

4034 clean-orphaned

4035 fi

4036 return 4

4037 else

4038 echo "消去確認のチェックがないので消さなかったの..." | html p

4039 return 5

4040 fi

4041 fi

4042 fi

4043

4044 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

4045 if [ -n "$rowid" ]; then

4046 # Update of existing record

4047 for col in `gettblcols $tbl`; do

4048 val=`getparquote $col`

4049 [ -z "$val" ] && continue

4050 ## err query "update $tbl set $col=$val where rowid=$rowid"

4051 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

4052 sql="update $tbl set $col=$val where rowid=$rowid;"

4053 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

4054 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

4055 ## User name cannot be changed with interface provided with this

4056 ## script. But we offer the trigger to change owner user

4057 ## of blog_s table.

4058 #err "select quote($col) from $tbl where rowid=$rowid;"

4059 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

4060 cat<<-EOF | query

4061 -- Here we cannot use BEGIN-COMMIT because groupupdate()

4062 -- should use EXCLUSIVE transaction outside of this.

4063 SAVEPOINT par2table;

4064 $sql

4065 update blog_s set val=$val

4066 where key='owner' and val=$old;

4067 RELEASE SAVEPOINT par2table;

4068 EOF

4069 ## XX: DIRTY Hack Ends here

4070 ## We should keep blog's owner as a single column which has

4071 ## foreign key constraint with primary key of grp/user.

4072 else

4073 query "$sql"

4074 fi

4075 done

4076 # Then, set up $pval for further insertion of tbl_s and tbl_m

4077 for col in `gettblpkey $tbl`; do

4078 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

4079 pval="$pval${pval:+, }\"$val\""

4080 done

4081 else

4082 # New entry

4083 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

4084 # Avoid empty repost of article.

4085 if [ x"$tbl" = x"article" ]; then

4086 # If rowid is empty and ID exists in article-table, that is REPOST!

4087 aid=`getpar id`

4088 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

4089 if [ -n "$xaid" ]; then

4090 # REPOST of article

4091 html p <<-EOF

4092 書き込み直後のリロードなので上書きを回避します。

4093 最新記事は末尾の「再読み込み」ボタンから見てください。

4094 EOF

4095 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

4096 return 9 # STOP Duplicated posting

4097 fi

4098 fi

4099 # Generate values() for primary keys

4100 for col in `gettblpkey $tbl`; do

4101 # Genuine primary keys for _m and _s

4102 val=`getvalquote $tbl $col`

4103 [ -z "$val" ] && continue

4104 pval="$pval${pval:+, }$val"

4105 done

4106 ##err pval=$pval

4107 for col in `gettblfkey $tbl`; do

4108 # args for values() to insertion into master table

4109 val=`getvalquote $tbl $col`

4110 [ -z "$val" ] && continue

4111 formaster=$formaster"${formaster:+, }$val"

4112 done

4113 formaster="$pval${formaster:+, }$formaster"

4114 ## err formaster=$formaster

4115 if [ -z "$formaster" ]; then

4116 echo "項目を全て埋めてください" | html pre

4117 return 1

4118 fi

4119 ## err "replace into $tbl values($formaster);"

4120 query "replace into $tbl values($formaster);"

4121 ## Insertion to master table, done

4122 fi

4123

4124 transaction=$tmpd/sqlfile.sql; touch $transaction

4125 for kt in s m; do

4126 tb2=${tbl}_$kt

4127 for col in `gettbl_${kt}_cols $tbl`; do

4128 ptype=`getpartype $col "limit 1"`

4129

4130 # First, check update of existing entries in _m

4131 if [ $kt = m ]; then

4132 # sessID|address.1.22|string|Somewhere-x.y.z

4133 sql=""

4134 ##err dots from query "select var from par where var like '$col.%';"

4135 for v in `query "select var from par where var like '$col.%' AND sessid='$session';"`; do

4136 # v=address.1.22

4137 st_rowid=${v##*.}

4138 origcol=${v%%.*} # original column derived from

4139 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

4140 ##case `getpartype $v` in

4141 ## err CASE `gettbl_coltype $tbl/$origcol` in

4142 ## err edit flag = `getpar action.$v`

4143 case `getpar action.$v` in

4144 rm)

4145 if [ x`getpar confirm.$v` = x"yes" ]; then

4146 newsql="delete from $tb2"

4147 else

4148 echo "削除確認未チェック" | html p

4149 fi ;;

4150 edit)

4151 case `gettbl_coltype $tbl/$origcol` in

4152 image|document|binary)

4153 file=$tmpd/`getparfilename $v`

4154 if [ ! -s "$file" ]; then # Maybe stupid REPOST

4155 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

4156 continue

4157 fi

4158 ## err type=file=$file

4159 [ -z "$file" ] && continue

4160 bn=`sqlquotestr "${file##*/}"`

4161 bin="X'"$(hexize "$file")"'"

4162 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

4163 type=\"file:$ct\"

4164 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

4165 cachedir=`getcachedir "$tbl/$rowid"`

4166 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

4167 rm -rf $cachedir

4168 ;;

4169 *)

4170 newsql="update $tb2 set val=(select val from par where var \

4171 like '$col.%.$st_rowid' AND sessid='$session')"

4172 ;;

4173 esac

4174 ;;

4175 mv)

4176 # regularize filename and strip directory part

4177 newname=`getpar mv.$v|tr -d '":;#<>?^%$!'|tr -d "'"|tr ' ' _`

4178 newname=`basename $newname`

4179 oldname=`query "SELECT val FROM $tb2 WHERE rowid=$st_rowid;"`

4180 oldext=`expr "$oldname" : '.*\.$.*$'`

4181 newext=`expr "$newname" : '.*\.$.*$'`

4182 err "p2t(mv): oldname=$oldname $oldext -> newname($v)=$newname $newext"

4183 if [ -n "$newname" -a x"$oldext" = x"$newext" ];

4184 then

4185 newsql="UPDATE $tb2 SET val='$newname'"

4186 else

4187 html p<<-EOF

4188 $newname は取り扱えないファイル名です。

4189 空白を含まない名前にして下さい。

4190 拡張子の変更もできません。

4191 EOF

4192 continue

4193 fi

4194 ;;

4195 *) # maybe "keep", do not modify value

4196 continue

4197 ;;

4198 esac

4199 # err newsql=$newsql

4200 sql=$sql$nl"$newsql where rowid=$st_rowid;"

4201 clearcachedir "$tbl/$rowid"

4202 done

4203

4204 if [ x"$bin" = x"NULL" ]; then

4205 ## err repl:normal sql=`echo $sql`

4206 if [ -n "$transaction" ]; then

4207 cat<<-EOF >> $transaction

4208 $sql

4209 DELETE FROM $tb2 WHERE type='string' AND val='';

4210 EOF

4211 else

4212 query "$sql

4213 delete from $tb2 where type='string' and val='';"

4214 ## err repl:normal done

4215 fi

4216 else

4217 # Binary update line is TOO LONG to pipelining

4218 sqlfile="$tmpd/sqlf.$$"

4219 if [ -n "$transaction" ]; then

4220 echo "$sql" >> $transaction

4221 else

4222 echo "$sql" > $sqlfile

4223 query ".read $sqlfile"

4224 fi

4225 fi

4226 # Rest of kt==m: set multiple mode

4227 nr=`getparcount $col`

4228 else

4229 nr=1 # for kt==s, number of records is 1

4230 fi

4231

4232 i=0

4233 while [ $i -lt $nr ]; do

4234 limit="limit 1 offset $i"

4235 i=$((i+1)) # increase beforehand against continue

4236 val=`getvalquote $tbl $col "$limit"`

4237 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4238 ## err $col=$val

4239 bin=NULL

4240 ## err partype$col=`getpartype $col "$limit"`

4241 ptype=`getpartype $col "$limit"` # partype should be obtained each time

4242 case $ptype in

4243 file) file=$tmpd/`getparfilename $col "$limit"`

4244 ## err parfile-$col=$file

4245 [ -z "$file" ] && continue

4246 bin="X'"$(hexize "$file")"'"

4247 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4248 type=\"file:$ct\" ;;

4249 "*"*) continue ;; # foreign table

4250 *) type=\"string\" ;;

4251 esac

4252 case `gettbl_coltype $tbl/$col` in

4253 password) # special care for password

4254 # name={password,pswd1,pswd2}

4255 p1=`getpar pswd1 "$limit"`

4256 if [ -z "$p1" ]; then

4257 continue # SKIP password setting, if p1 is empty

4258 else

4259 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4260 ## err pswd=$pswd

4261 if pwcheck "$pswd"; then

4262 if [ x"$p1" = x"$p2" ]; then

4263 case "$p1" in

4264 ??????????*) ;;

4265 *) echo "パスワードは10字以上にしてください。" | html p

4266 return 6;;

4267 esac

4268 val="\"`echo $p1|mypwhash`\""

4269 else

4270 echo "2つの新パスワード不一致" | html p

4271 return 7

4272 fi

4273 else

4274 echo "旧パスワード違います" | html p

4275 return 8

4276 fi

4277 fi

4278 ;;

4279 esac

4280 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4281 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4282 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4283 if [ x"$bin" = x"NULL" ]; then

4284 ## err Normal-query: `echo $sql`

4285 if [ -n "$transaction" ]; then

4286 echo "$sql" >> $transaction

4287 else

4288 query "$sql"

4289 fi

4290 else

4291 sqlfile="$tmpd/query.$$"

4292 ## err sqlfile=`ls -lF $sqlfile`

4293 if [ -n "$transaction" ]; then

4294 echo "$sql" >> $transaction

4295 else

4296 echo "$sql" > $sqlfile

4297 query ".read $sqlfile"

4298 fi

4299 fi

4300 ## err p2t done

4301 done

4302 done

4303 done

4304 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4305 -- We cannot use transaction here, because groupupdate may use it.

4306 SAVEPOINT pa2table_insert;

4307 .read $transaction

4308 RELEASE SAVEPOINT pa2table_insert;

4309 EOF

4310 rc=$?

4311 [ $rc -eq 0 -a x"$tbl" = x"user" ] && flag_profupdate

4312 ## err "Table:$tbl update done "

4313 return $rc

4314 )

4315 genform() {

4316 # $1 = form definition file

4317 # $2, $3 (optional)= table name and ROWID

4318 # If $GF_VIEWONLY set and nonNull, output values without form

4319 # If $GF_ARGS set, use it as content-strings in the form

4320 # If $GF_OWNER set, use it as value of name="owner"

4321 # If $GF_STAGE set, use it as value of name="stage"

4322 forms="" hiddens="" rowid=$3

4323 if [ ! -e "$1" ]; then

4324 echo "そのようなデータベースはないようです($2)。" | html p

4325 return

4326 elif [ -n "$2" ]; then

4327 rec=`query "select * from $2 where rowid='$rowid';"`

4328 if [ -z "$rec" ]; then

4329 pk=`gettblpkey $2`

4330 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4331 rec=`query "select rowid from $2 where $pk='$rowid';"`

4332 rowid=$rec

4333 rec=$3

4334 fi

4335 if [ -z "$rec" ]; then

4336 echo "そんなレコードはないみたいね..." | html p

4337 return

4338 fi

4339 fi

4340 if [ -z "$GF_VIEWONLY" ]; then

4341 rm='<input id="rm" name="rm" type="checkbox"

4342 value="yes"><label for="rm">このエントリの削除</label>

4343 <span>ほんとうに消しますよ(確認)!

4344 <input name="confirm" type=checkbox value="yes">はい</span>'

4345 fi

4346 # Image Cache dir

4347 ## err genform: getcache=$2/$rowid

4348 td=`getcachedir "$2/$rowid"`

4349 while IFS=: read prompt name keytype type args; do

4350 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4351 sp="${args:+ }"

4352 form="" val=""

4353 if [ -n "$rowid" ]; then

4354 # err genform2a: Seeking for "$2.$name, type=$type"

4355 rawval=`getvalbyid $2 $name $rowid $td`

4356 val=`echo "$rawval"|htmlescape`

4357 ## err genform3a: getvalbyid $2 $name $rowid $td

4358 ## err genform3b: val="[$val]" type="$type"

4359 fi

4360 if [ -n "$GF_VIEWONLY" ]; then

4361 is_hidden "$2" "$name" && continue

4362 fi

4363 case "$type" in

4364 text*)

4365 cgiform=cgi_multi_$type

4366 if [ -s $td/$name.count -a -n "$val" ]; then

4367 form=`$cgiform $name $td`

4368 val=$(echo "$val"|

4369 while read fn; do

4370 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4371 </td></tr>$nl"

4372 done)

4373 val="<table>$nl$val$nl</table>"

4374 else

4375 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4376 form=`cgi_$type $name "$rawval" "$args"`

4377 fi

4378 ;;

4379 [Rr][Aa][Dd][Ii][Oo])

4380 fh="<label><input type=\"radio\" name=\"$name\""

4381 form="`echo $args|sed -e \

4382 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4383 ;;

4384 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4385 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4386 ;;

4387 [Ss][Ee][Ll][Ee][Cc][Tt])

4388 fh="<select name=\"$name\">$nl"

4389 form=$(for l in $args; do

4390 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4391 done)

4392 if [ -n "$val" ]; then

4393 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4394 fi

4395 form="$fh$form</select>"

4396 ;;

4397 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4398 if [ -s $td/$name.count ]; then

4399 form=`cgi_multi_file $name $td "$args"`

4400 if [ -n "$val" ]; then

4401 hrfb="$myname?showattc+$2_m"

4402 val=$(echo "$rawval" \

4403 | while read fn; do

4404 data=`percenthex "$td/$fn"`

4405 #ct=`cat $td/$fn.content-type`

4406 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4407 ri=`cat "$td/$fn.rowid"`

4408 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4409 #imgsrc="<img src=\"data:$ct,$data\">"

4410 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4411 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4412 done)

4413 fi

4414 else

4415 form="<input type=\"file\" name=\"$name\" $args>"

4416 if [ -n "$val" ]; then

4417 imgs=$(echo "$rawval"\

4418 |while read fn;do

4419 data=`percenthex "$td/$fn"`

4420 echo "<img src=\"data:image/png,$data\">$fn<br>"

4421 done)

4422 form=$form"<br>$imgs"

4423 val=$imgs # 2015-06-15

4424 else

4425 form="<input type=\"file\" name=\"$name\" $args>"

4426 fi

4427 fi

4428 ;;

4429 [Hh][Ii][Dd][Dd][Ee][Nn])

4430 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4431 args="value=\"$GF_STAGE\""

4432 fi

4433 form="<input type=\"hidden\" name=\"$name\" $args>"

4434 prompt='' # Remove prompt

4435 ;;

4436 [Aa][Uu][Tt][Hh][Oo][Rr])

4437 [ -n "$GF_VIEWONLY" ] && continue

4438 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4439 prompt="" ;;

4440 [Oo][Ww][Nn][Ee][Rr])

4441 [ -n "$GF_VIEWONLY" ] && continue

4442 val=${GF_OWNER:-$val}

4443 val=${val:-$user}

4444 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4445 prompt="" ;;

4446 [Uu][Ss][Ee][Rr])

4447 # XXX: is null $user ok?

4448 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4449 [ -n "$GF_VIEWONLY" ] && continue

4450 form="$user"

4451 ;;

4452 [Pp]assword)

4453 [ -n "$GF_VIEWONLY" ] && continue

4454 form="`cgi_passwd`"

4455 val=""

4456 ;;

4457 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4458 [ -n "$GF_VIEWONLY" ] && continue

4459 if [ -z "$rowid" ]; then

4460 val=`genserial`

4461 fi

4462 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4463 prompt="" ;;

4464 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4465 prompt=""

4466 ;;

4467 parent|path|blog*)

4468 prompt=""

4469 ;;

4470 "*"*)

4471 tail=$tail"``"

4472 continue ;;

4473 esac

4474 if [ -n "$prompt" ]; then

4475 if [ -n "${GF_VIEWONLY}" ]; then

4476 form=$val

4477 else

4478 :

4479 fi

4480 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4481 else

4482 hiddens=$hiddens$nl"$form"

4483 fi

4484 done < $1

4485 # enctype="multipart/form-data"

4486 cat<<EOF

4487 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4488 ${rowid:+$rm}

4489 <table class="b $2">

4490 $forms

4491 </table>$hiddens

4492 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4493 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4494 EOF

4495 if [ -z $GF_VIEWONLY ]; then

4496 cat<<EOF

4497 <input type="submit" name="sub" value="OK">

4498 <input type="reset" name="res" value="Reset">

4499 EOF

4500 fi

4501 cat<<EOF

4502 $GF_ARGS</form>

4503 $tail

4504 EOF

4505 }

4506 edittable() {

4507 # $1=form-def $2=table $3 rowid

4508 genform "$@"

4509 }

4510 viewtable() {

4511 GF_VIEWONLY=1 genform "$@"

4512 }

4513 showattc() {

4514 # $1=table_m $2=rowid &optional $3=RawFlag

4515 ## err \$1=$1 \$2=$2 \$3=$3

4516 if ! isfilereadable $user $1 $2; then

4517 contenttype; echo

4518 echo "このファイルは管理者のみしか見られません" | html p

4519 putfooter; exit

4520 fi

4521 idir=`umask 002; mktempd` || exit 1

4522 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4523 bin=$idir/$myname-$$.bin

4524 sql="select quote(bin) from $1 where rowid='$2';"

4525 ## err showattc: sql: $sql

4526 sq $db "$sql" | unhexize > $bin

4527 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4528 type=${tv%//*} fn=${tv#*//}

4529 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4530 ct=${type#file:}

4531 case $ct in # all text/* changed to text/plain

4532 text/*|application/csv|application/json)

4533 charset=`nkf -g $bin|cut -d' ' -f1`

4534 case $charset in

4535 ASCII*) charset="" ;;

4536 esac

4537 if [ -z "$3" ]; then

4538 ct="text/html${charset:+; charset=$charset}"

4539 link="?showattc+$1+$2+raw"

4540 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4541 | sed 's,^,<span></span>,' \

4542 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4543 -D_LINK_="$link" \

4544 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4545 exit $?

4546 fi

4547 ct="text/plain${charset:+; charset=$charset}"

4548 ;;

4549 esac

4550 contenttype "$ct"

4551 echo "Content-Disposition: filename=\"$fn\""

4552 echo "Content-Length: " `cat $bin | wc -c`; echo

4553 #echo "Content-Type: " ${type#file:}; echo

4554 cat $bin

4555 }

4556 #

4557 # Some default stupid handler on CGI values

4558 #

4559 default_storedb() {

4560 # ARG: $1=table-def-file

4561 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4562 tbl=`basename $1`

4563 tbl=${tbl%.def}

4564 cols="`grep :text $1|cut -d: -f2`"

4565 col=`echo "$cols"|head -1`

4566 vcol=`getpar $col`

4567 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4568 if [ -n "$vcol" ]; then

4569 par2table $1

4570 else

4571 return 2 # No insertion occurred

4572 fi

4573 }

4574

4575 default_view() { # $1=def-file

4576 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4577 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4578 default_storedb "$@"

4579 query "select rowid from $tbl order by rowid desc;" \

4580 | while read rowid; do

4581 viewtable $1 $tbl $rowid

4582 done | _m4 -D_TITLE_="$tbl" \

4583 -D_FORM_="`genform $1`" \

4584 -D_DUMPTABLE_="syscmd(cat)" \

4585 $layout/html.m4.html $layout/form+dump.m4.html

4586 }

4587 default_viewtext() { # $1=def-file

4588 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4589 default_storedb "$@"

4590 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4591 | _m4 -D_TITLE_="$tbl" \

4592 -D_FORM_="`genform $1`" \

4593 -D_DUMPTABLE_="syscmd(cat)" \

4594 $layout/html.m4.html $layout/form+dump.m4.html

4595 }

4596 default_smail() {

4597 default_storedb "$@"

4598 if [ $? -eq 2 ]; then

4599 _m4 -D_TITLE_="入力" \

4600 -D_FORM_="`genform $1`" \

4601 -D_DUMPTABLE_="" \

4602 $layout/html.m4.html $layout/form+dump.m4.html

4603 return

4604 fi

4605 cond=""

4606 for pk in `gettblpkey $tbl`; do

4607 pv=$(sqlquote "$(getpar $pk)")

4608 cond="$cond${cond:+ and }$pk=$pv"

4609 done

4610 sql="select rowid from $tbl where $cond;"

4611 rowid=`query "$sql"`

4612 ## err smail1 - "$sql" "-> rowid=$rowid"

4613

4614 while IFS=: read prompt name keytype type args; do # Read from $1

4615 val=`getpar $name`

4616 if [ -n "$val" ]; then

4617 text="$text

4618 $prompt

4619 $name=$val

4620 ---------------------------------------------------------"

4621 fi

4622 case "$type" in

4623 image|document|file)

4624 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4625 fns=$(echo "$fn"|while read fn; do

4626 err mv $tmpd/$fn.orig $tmpd/$fn

4627 mv $tmpd/$fn.orig $tmpd/$fn

4628 rm $tmpd/$fn.rowid # Remove cache flag

4629 ## err "`ls $tmpd/$fn`"

4630 echo $fn

4631 done)

4632 files="$files $fns"

4633 ;;

4634 esac

4635 done < $1

4636 ## err FILES=$files "`ls -lF $tmpd`"

4637 subj="from ${REMOTE_ADDR}"

4638 (echo "$url"

4639 echo "への書き込みがありました。"

4640 echo "------"

4641 echo "$text"

4642 ) | (cd $tmpd &&

4643 err LS="`ls -lF`" &&

4644 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4645 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4646 echo "以下の内容で送信しました。" | html p

4647 viewtable $1 $tbl \

4648 `query "select rowid from $tbl order by rowid desc limit 1;"`

4649 echo "戻る" | html a "href=\"?\""

4650 }