s4: 0905a624c720 s4-funcs.sh

s4

view s4-funcs.sh @ 877:0905a624c720

Checkbox should have value of rowid of user

author	HIROSE Yuuji <yuuji@gentei.org>
date	Tue, 08 Dec 2020 12:24:19 +0900
parents	610c3fb6d52a
children	a240c8988c65

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,.avi,.ogg,.mov,.webm,.gpx,.json,.geojson,.kml,.html,.css,.rb,.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"

90 # Start debug logging

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

92 exec 3>> $logdir/debug.out

93 err() {

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

95 }

96 case "$HTTP_USER_AGENT" in

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

98 *) touchpanel="" ;;

99 esac

100

101 # If S4MASTERDB is set, behave in another world

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

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

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

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

106 # Variables set in s4-world.sh

107 # $S4WORLDS, $S4WROLDNAME, $S4WORLDGRPS

108 # Files created in s4-world.sh

109 # $worldlistfile, $worldoptionfile, $worldnamefile, $worldgrpfile

110 fi

111

112

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

114

115 : <<EOF

116

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

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

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

120 レポート提出管理

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

122

123 [2016]

124 7/12 根本への反省

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

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

127

128 [以下2015]

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

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

131 Web

132 締切設定

133

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

135 ○自分の提出物リスト

136

137 7/19 ○設置

138 ○一斉送信

139 ○getparfilename の tmpd の扱い

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

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

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

143 ○添付ファイル回収

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

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

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

147

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

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

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

151 ○レポート提出モード

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

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

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

155

156

157 7/9 ○管理者の追加

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

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

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

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

162

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

164

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

166 ○グループ検索

167

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

169

170

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

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

173

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

175 ○6/1 par2tableを triplex 対応に

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

177 →とすると一気に

178

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

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

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

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

183

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

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

186 ## 1. passwd

187 ## 2. grp

188 ## 3. grp_mem

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

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

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

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

193

194 ## ● listの定義:

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

196 ## ● hashの定義:

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

198

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

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

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

202 ## csv2sq3 で .csv.sq3 の Makefile

203

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

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

206 ## type := root | comment

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

208 ## unique(id, filename)

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

210

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

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

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

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

215 ## できたー!

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

217 ↓

218 ↓以下に変更

219 with recursive allmem as

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

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

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

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

224

225

226 with recursive allmem as

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

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

229 where allmem.val=grp_m.gname)

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

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

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

233

234

235 ## triggerもできた。

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

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

238

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

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

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

242 ## グループ管理

243 ## 個人情報管理

244 ##

245

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

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

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

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

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

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

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

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

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

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

256

257 ## ログテーブル

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

259

260

261

262 ■表設計

263 * 3つの表に分散管理

264 id格納表 + hash表 + list表

265 * *_s *_m

266

267

268

269 user, user_map, user_col

270

271 ■抽象エントリタイプ

272 * user

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

274 * group

275 権限判定に利用

276 * serial

277 自動idとして機能

278 * password

279 入力 type=passwordで入力

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

281 * session

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

283 * text

284 入力 type=text

285 * textarea

286 入力 textarea

287 * image|document

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

289 * owner

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

291 * gowner

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

293 * timestamp

294 datetime()

295 * parent

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

297 * path

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

299

300 格納タイプ

301 * list

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

303 * hash

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

305

306 オブジェクトタイプ

307 * entry

308 id, title, owner

309 * textpart

310 id, parentID, text

311 * binarypart

312 id, parentID, contenttype, filename, content

313 * content

314 hash(textpart), list(binarypart)

315 * topic

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

317 * reply

318 id, parentID, content

319 * blog

320 list(entry)

321 blog = [topic, list(reply)]

322

323

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

325 "text" => "hogehoge ..",

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

327 "author" => "taro",

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

329 "parent" => "/",

330 "path" => "/1",

331 "text" => "blah, blah, ....",

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

333 {"serial" => 2,

334 "author" => "hanako",

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

336 "parent" => "/",

337 "path" => "/2",

338 "text" => "blah, blah, ....",

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

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

341

342

343 user:=

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

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

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

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

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

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

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

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

352

353 変換表

354 /user/email=m

355

356 blog:=

357 シリアル:id:p:serial

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

359 所有者:owner:s:owner:

360 時刻:ctime:s:stamp:

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

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

363

364 article:=

365 シリアル:id:p:serial

366 筆者:author:s:owner

367 時刻:ctime:s:stamp:

368 参照元:parent:s:parent:

369 パス:path:s:path:

370 本文:text:s:textarea:

371 画像:image:m:image:

372

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

374

375

376 EOF

377

378 logstart() {

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

380 }

381 logend() {

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

383 }

384 sqlog() {

385 logstart

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

387 cat >> $querylog

388 else

389 echo "$*" >> $querylog

390 fi

391 logend

392 }

393 sq() {

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

395 logstart

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

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

398 else

399 echo "$@" >> $querylog

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

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

402 fi

403 logend

404 }

405 dbsetup() {

406 pipedir=$tmpdir/pipedir

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

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

409 suf=`date +%s`

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

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

412 mkfifo $sqi $sqo

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

414 sq $db < $sqi &

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

416 if $isCGI; then

417 exec 2>> $logdir/error.out

418 fi

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

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

421 rm $sqi

422 # Attach supplemental DB

423 cat >&5 <<-EOF

424 .output /dev/null

425 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

429 .output stdout

430 EOF

431 }

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

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

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

435 fi

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

437 }

438 cleanup() {

439 trap '' INT HUP EXIT TERM PIPE

440 echo .quit >&5

441 kill $sq3pid

442 kill $sq3pid

443 rm -f $sqo $sqi

444 rm -rf $tmpfiles

445 cleanup2

446 }

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

448 # simple redirection for the sake of speed.

449 query() {

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

451 echo ".output $sqo" >&5

452 logstart

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

454 tee -a $querylog

455 else

456 echo "$@" >> $querylog

457 echo "$@"

458 fi >&5

459 echo ".output stdout" >&5

460 cat $sqo

461 rc=$?

462 logend

463 return $rc

464 }

465 _m4() {

466 #S4NAME=f,f,f

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

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

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

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

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

472 }

473 ismember() {

474 # $1=user, $2=group

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

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

477 }

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

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

480 }

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

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

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

484 }

485 isgrpowner() (

486 # $1=user, $2=group

487 gn=`sqlquote "$2"`

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

489 err isgrpowner: $sql

490 test -n "`query $sql`"

491 )

492 isgrpownerbygid() (

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

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

495 err isgrpownerbygid: $sql

496 test -n "`query $sql`"

497 )

498 getgroupadminmails() {

499 # $1=group

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

501 email4group "$1" "$i" ;

502 done

503 }

504 getgroupadmins() { # $1=group

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

506 qgrp=`sqlquote "$1"`

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

508 }

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

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

511 getvalbyid grp $2 \

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

513 }

514 getgroupbyid() {

515 # $1=id|gname

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

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

518 # err ggbyid: `echo $sql`

519 query $sql

520 }

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

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

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

524

525 # Return true when anonymous mode

526 [ "$anonymousmode" ] && return 0

527 # case `getvalbyid blog mode $2` in

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

529 # *closed*)

530 # owner=`getvalbyid blog owner $2`

531 # if isgrp $owner; then

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

533 # elif isuser $owner; then

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

535 # fi

536 # esac

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

538 sql="with getblog as (

539 select key,val from blog_s where id=(

540 select blogid from article where id in

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

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

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

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

545 isgrpadm as (select user from grp_adm where

546 gname=(select val from getowner) and

547 user='$1'),

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

549 select case

550 when (select author from article where

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

552 then 'author'

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

554 then 'open'

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

556 then CASE

557 WHEN (SELECT val FROM isgrp) IS NULL

558 THEN

559 CASE WHEN (SELECT val from getowner)

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

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

562 ELSE ''

563 END

564 WHEN (select user from isgrpadm) IS NOT NULL

565 THEN 'i-am-admin'

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

567 END

568 when (select val from getmode) is null

569 then 'open'

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

571 then (SELECT user FROM isgrpadm)

572 when (select author from article where

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

574 then 'user+author'

575 else '' end;"

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

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

578 # result=`query "$sql"`

579 # err FileAccessibility=$result

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

581 }

582 linkhome() {

583 # $1=UserOrGroupRowid

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

585 if isuser $1; then

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

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

588 name=`gecos $1|htmlescape`

589 else

590 _grid=`numericalize "$1"`

591 echo -n "grp+$1"

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

593 fi

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

595 }

596 hreflink() {

597 # s4 specific notation:

598 # ^href=URL

599 # ^iframe=URL

600 # ^video=URL

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

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

603 # OSM umap Wikistyle Notation:

604 # [[URL]] - Simle Link

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

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

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

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

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

610 # Other Style

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

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

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

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

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

616 # SPC+SPC+$ - <br>

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

618 checkboxON="${cb} checked>"

619 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

644

645 }

646 minitbl() {

647 sed -n '

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

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

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

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

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

653 H; # Concat this line to HoldSpace

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

655 $ b cont

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

657 }

658 :cont

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

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

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

662 # . of ^. is workaround for FreeBSD sed

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

664 p; # Print whole "table" element

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

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

667 }

668 x; # Back to the newest line

669 p; # Print rest' | miniul

670 }

671 miniul() {

672 sed -e '

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

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

675 :top

676 s/\n//;

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

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

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

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

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

682 $ b cont

683 N; # 次の行を読む

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

685 /^ /b top

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

687 p; s/.*//;

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

689 /^\* /b top

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

691 }

692

693 :cont

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

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

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

697 H; x

698 }

699 x' | miniol

700 }

701 miniol() {

702 sed -e '

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

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

705 :top

706 s/\n//;

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

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

709 x;

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

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

712 x;

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

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

715 $ b cont

716 N; # 次の行を読む

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

718 /^ /b top

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

720 p; s/.*//;

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

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

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

724 }

725

726 :cont

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

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

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

730 H; x

731 }

732 x'

733 }

734 acclog() (

735 # $1=table, $2=rowid

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

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

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

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

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

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

742 fi

743 )

744 gecos() (

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

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

747 )

748 setpar() {

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

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

751 case "$1" in

752 user) _user="$v" ;;

753 skey) _skey="$v" ;;

754 esac

755 fi

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

757 }

758 unsetpar() {

759 for i; do

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

761 case "$i" in

762 user|skey) unset _$i ;;

763 esac

764 fi

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

766 done

767 }

768 replpar() {

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

770 }

771 getpar() {

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

773 val=""

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

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

776 user) val=$_user ;;

777 skey) val=$_skey ;;

778 esac

779 fi

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

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

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

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

784 fi

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

786 case "$var" in

787 owner)

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

789 echo $user; return

790 elif ismember $user $val; then

791 echo $val; return

792 fi ;;

793 esac

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

795 echo "$val"

796 }

797 setskey() {

798 # For quick response...(?)

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

800 }

801 chkskey() {

802 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

808 return 0

809 fi

810 return 1

811 }

812 resetskey() {

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

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

815 fi

816 }

817 getpartype() {

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

819 }

820 getparcount() {

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

822 }

823 getparfilename() {

824 # null if type of $1 is not file

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

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

827 }

828 sqlquote() {

829 (v="$1"

830 case "$v" in

831 "") return ;; # null

832 "X'"*) # quoted hex string

833 echo $1 ;;

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

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

836 echo "\"$v\""

837 return ;;

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

839 echo "\"$v\""

840 return ;;

841 *)

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

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

844 else

845 echo "\"$v\""

846 fi ;;

847 esac)

848 }

849 sqlquotestr() (

850 case "$1" in

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

852 echo "'$v'" ;;

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

854 esac

855 )

856 mktempd() {

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

858 }

859 getcachedir() { # $1=maintable

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

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

862 else

863 echo $tmpd/$thumbxy

864 fi

865 }

866 getval() {

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

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

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

870 echo "$user" ;;

871 stamp|datetime)

872 date "+%F %T" ;;

873 serial)

874 (s=`getpar $2`

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

876 *)

877 getpar "$2" "$3";;

878 esac

879 }

880

881 getvalquote() {

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

883 (v=`getval "$@"`

884 case "$v" in

885 "") echo NULL ;;

886 *) sqlquote "$v" ;;

887 esac)

888 }

889 getparquote() {

890 sqlquote "`getpar $1`"

891 }

892 getbinbyid() {

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

894

895 }

896 getvalbyid() {

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

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

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

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

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

902

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

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

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

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

907 return

908 fi

909 done

910 rowid=$3

911 pk=`gettblpkey $1`

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

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

914 td=${4:-$tmpd}

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

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

917 for kt in s m; do

918 t=${1}_$kt

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

920 vcount=1 # count(val)

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

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

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

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

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

926 if [ $kt = m ]; then

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

928 # Reset val to store filenames if type is string

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

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

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

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

933 i=0

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

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

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

937 i=$((i+1))

938 fn=$c.$i

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

940 case $type in

941 file:*)

942 #file=$td/$val

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

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

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

946 # FOR SPEED: Skip file generation if imgcache exists

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

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

949 && continue

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

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

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

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

954 select rowid from $cond $slice;

955 .output '$td/$fn'

956 select val from $cond $slice;

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

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

959 .output stdout

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

961 EOF

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

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

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

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

966 ls -lh "$file" |

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

968 case "$type" in

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

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

971 *|*:[Aa]pplication*)

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

973 png:- > "$file"

974 ;;

975 esac

976 ;;

977 *)

978 sq $db<<EOF

979 .output $td/$fn.rowid

980 select rowid from $cond $slice;

981 .output $td/$fn

982 select val from $cond $slice;

983 EOF

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

985 ;;

986 esac

987 done

988 else

989 rm -f $td/$c.count

990 case $type in

991 file:*)

992 echo "$val" \

993 | while read fn; do

994 file=$td/$fn

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

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

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

998 | unhexize > "$file"

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

1000 case $type in

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

1002 *:[Aa]pplication*)

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

1004 png:- > $file ;;

1005 esac

1006 fi

1007 done

1008 ;;

1009 esac

1010 fi

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

1012 return

1013 fi

1014 done

1015 done)

1016 }

1017 getvalbypkey() (

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

1019 pk=`gettblpkey $1`

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

1021 getvalbyid "$1" "$2" $rowid $4

1022 )

1023 getvalbycond() {

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

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

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

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

1028 getvalbyid "$1" "$2" $rowid "$4"

1029 fi

1030 }

1031 getpwfield() {

1032 # getpwfield user column

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

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

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

1036 echo "$val"

1037 return 0

1038 else

1039 return 1

1040 fi

1041 }

1042 numericalize() {

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

1044 }

1045 encode() {

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

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

1048 sha1=sha1

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

1050 sha1=sha1sum

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

1052 sha1=gsha1sum

1053 fi

1054 fi

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

1056 }

1057 enjpeg() {

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

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

1060 cjpeg="cjpeg"

1061 else

1062 cjpeg="convert - jpeg:-"

1063 fi

1064 fi

1065 $cjpeg "$@"

1066 }

1067 mycrypt() (

1068 key=$1 salt=$2

1069 # err \$2=$2

1070 case $2 in

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

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

1073 esac

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

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

1076 )

1077 hexize() {

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

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

1080 hexize="xxd -p"

1081 else

1082 hexize_hd() {

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

1084 }

1085 hexize="hexize_hd"

1086 fi

1087 fi

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

1089 }

1090 unhexize() {

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

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

1093 unhex="xxd -p -r"

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

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

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

1097 EOF

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

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

1100 # world writable...:)

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

1102 fi

1103 fi

1104 cat "$@" | $unhex

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

1106 }

1107 percenthex() {

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

1109 }

1110 htmlescape() {

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

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

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

1114 }

1115 enascii() {

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

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

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

1119 else

1120 enascii_now=`date +%FT%T`

1121 enascii_sed() {

1122 nkf -Z0Z1Z2 \

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

1124 }

1125 enascii="enascii_sed"

1126 fi

1127 fi

1128 cat "$@" | $enascii

1129 }

1130 size_h() {

1131 i="$1" oi=$1

1132 set -- B B KB MB GB TB

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

1134 oi=$i

1135 i=$((i/1024))

1136 shift

1137 done

1138 echo ${oi}$1

1139 }

1140 gettblconf() {

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

1142 ## tconfs=`sq $db \

1143 tconfs=`query \

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

1145 fi

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

1147 }

1148 gettblkeys() {

1149 # $1=tbl

1150 gettblconf

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

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

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

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

1155 case $type in

1156 *p*)

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

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

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

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

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

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

1163 esac

1164 case $type in

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

1166 esac

1167 done

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

1169 }

1170 gettblpkey() {

1171 # $1=tbl

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

1173 }

1174 gettblfkey() {

1175 (x=`gettblkeys $1`

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

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

1178 }

1179 gettblcols() {

1180 (x=`gettblkeys $1`

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

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

1183 }

1184 gettbl_s_cols() {

1185 (x=`gettblkeys $1`

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

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

1188 }

1189 gettbl_m_cols() {

1190 (x=`gettblkeys $1`

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

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

1193 }

1194 gettbl_h_cols() {

1195 (x=`gettblkeys $1`

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

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

1198 }

1199 gettbl_coltype() (

1200 gettblconf

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

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

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

1204 )

1205 is_hidden() {

1206 # $1=Tbl $2=col

1207 gettblconf

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

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

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

1211 case $x in

1212 *h*) return 0 ;;

1213 *) return 1 ;;

1214 esac

1215 }

1216

1217 dbsetbyid() {

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

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

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

1221 quotedp=$(sqlquotestr "$p")

1222 unset primary update

1223 gettblconf

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

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

1226 #err conf=$conf

1227 case ${conf#*=} in

1228 p*) primary=1 ;;

1229 f*) update=1 ;;

1230 u*) ;;

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

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

1233 esac

1234 #err t=$t

1235 type=string fn=""

1236 case $conf in

1237 */password)

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

1239 ;;

1240 */image*|*/document*)

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

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

1243 ;;

1244 esac

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

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

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

1248 if [ "$primary" ]; then

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

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

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

1252 elif [ "$update" ]; then

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

1254 else

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

1256 fi

1257 )

1258 }

1259 expire() (

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

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

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

1263 )

1264 addsession() {

1265 # expireをセット

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

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

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

1269 # Remove old session parameters

1270 now=`expire now`

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

1272 }

1273 gencookie() (

1274 path=${URL#*:/}

1275 path=${URL%/*}

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

1277 for kv; do

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

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

1280 done

1281 )

1282 contenttype() {

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

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

1285 }

1286 putheader() {

1287

1288 }

1289 putfooter() {

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

1291 }

1292 getcookie() {

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

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

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

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

1297 case "$k" in

1298 user) _user="$v" ;;

1299 skey) _skey="$v" ;;

1300 esac

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

1302 done

1303 }

1304 genrandom() {

1305 # $1=columns (default: 10)

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

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

1308 }

1309 genserial() {

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

1311 }

1312 smail() {

1313 # smail rcpts subj (file)

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

1315 # $MAIL_FROM <- From: header value

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

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

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

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

1320 sender=${SENDER:-$admin}

1321 # Do not call m4 with directly passing text

1322 _r=$tmpd/rcpt

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

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

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

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

1327 }

1328 smail_queue_flush() {

1329 # $1=timelimit

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

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

1332 SELECT rowid FROM smailq

1333 GROUP BY rcpts, subj

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

1335 EOF

1336 )

1337 for rid in $rowids; do

1338 sq $workdb \

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

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

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

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

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

1344

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

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

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

1348 .separator "\n" "------------------\n\n"

1349 SELECT time, text FROM smailq

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

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

1352 ORDER by time;

1353 EOF

1354 then

1355 cat <<-EOF | sq $workdb

1356 DELETE FROM smailq

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

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

1359 EOF

1360 fi

1361 fi

1362 done

1363 }

1364 smail_queue() {

1365 # $1=Rcpts, $@=subj

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

1367 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1376 EOF

1377 )

1378 ## XXX: Adhoc load mitigation

1379 case "$now" in

1380 *[01])

1381 err flush_queue=$mintime

1382 smail_queue_flush ;;

1383 esac

1384 fi

1385 }

1386 setviastring() {

1387 table=par

1388 oifs="$IFS"

1389 IFS="&"

1390 for us in $1; do

1391 k=${us%%=*}

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

1393 setpar "$k" "string" "$v"

1394 done

1395 IFS="$oifs"

1396 }

1397 checkdomain() (

1398 # Check the validity of domain by referring DNS

1399 item=$1

1400 err checkdomain $1

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

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

1403 )

1404 pwcheck() {

1405 # $1=passwd

1406 dbpswd=`getpwfield $user pswd`

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

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

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

1410 }

1411 mypwhash() {

1412 mycrypt "`cat`" `genrandom 5`

1413 }

1414 wasureta() {

1415 user=$1

1416 if ! checkdomain $user; then

1417 contenttype; echo

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

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

1420 putfooter

1421 exit 0

1422 fi

1423 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1425 encpswd=`echo $newpswd|mypwhash`

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

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

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

1429 $msgdir/mail-newaccount.m4 \

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

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

1432 }

1433 checkauth() {

1434 user=`getpar user`

1435 skc=`getpar skey` # from cookie

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

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

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

1439 if chkskey "$skc"; then

1440 return 0

1441 fi

1442 fi

1443 pswd=`getpar pswd`

1444 quser=`sqlquotestr "$user"`

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

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

1447 return 4 # 4=server too heavy

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

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

1450 return 2 # 2=login fail

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

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

1453 contenttype; echo

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

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

1456 cat <<-EOF | html p

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

1458 EOF

1459 exit 0 # XXXXX more smart...

1460 fi

1461 wasureta "$user"

1462 err wasureta issued for "$user"

1463 return 1 # wasureta error

1464 fi

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

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

1467 if pwcheck "$pswd"; then

1468 newsession=`genrandom 34`

1469 if setskey "$user" "$newsession" &&

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

1471 gencookie "user=$user" "skey=$newsession"

1472 return 0

1473 else

1474 return 4 # Heavy load??

1475 fi

1476 fi

1477 err "Login failed: [$user]"

1478 return 2 # Password mismatch

1479 }

1480 showlogin() {

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

1482 test -z "$args" && resetskey

1483 s4name=${S4NAME:-s4}

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

1485 cat $layout/login.m4.html

1486 echo '</body></html>'

1487 ) | _m4 \

1488 -D_BODYCLASS_="login" \

1489 -D_TITLE_="$s4name" \

1490 -D_SYSNAME_="Welcome to $s4name" \

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

1492 exit 0

1493 }

1494 dologin() {

1495 test -n "$S4WORLD" && syncaccount

1496 checkauth

1497 st=$?

1498 if [ $st != 0 ]; then

1499 contenttype; echo

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

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

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

1503 showlogin # and EXIT

1504 fi

1505 err "Auth OK: [$user]"

1506 }

1507

1508 # Do instant jobs here

1509 dbsetup

1510 trap cleanup INT HUP EXIT TERM PIPE

1511 # trap cleanup INT HUP

1512

1513

1514 cgiinit() {

1515 tmpd=`tmpd=$tmpdir mktempd`

1516 tmpf=$tmpd/stream.$$

1517 tmpfiles=$tmpfiles" $tmpd"

1518 addsession $session

1519 getcookie

1520 case "$REQUEST_METHOD" in

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

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

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

1524 # cat > $tmpf # too much?

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

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

1527 s="`cat $tmpf`"

1528 ;;

1529 esac

1530 case "$CONTENT_TYPE" in

1531 *boundary*)

1532 bndry=${CONTENT_TYPE#*boundary=}

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

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

1535 do

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

1537 #echo u=$us

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

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

1540 # err k=$k v=$v

1541 case "$k" in

1542 *:filename)

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

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

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

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

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

1548 :

1549 fi

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

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

1552 case "$mimetype" in

1553 [Ii]mage/x-xcf)

1554 bzip2 "$tmpd/$v"

1555 v=${v}.bz2

1556 ;;

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

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

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

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

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

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

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

1564 v=$vjpg

1565 else

1566 mimetype="Not supported"

1567 fi

1568 ;;

1569 [Ii]mage/*)

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

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

1572 ;;

1573 esac

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

1575 then

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

1577 continue

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

1579 replpar text string \

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

1581 continue

1582 fi

1583 ;;

1584 *)

1585 type='string'

1586 ;;

1587 esac

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

1589 setpar "$k" "$type" "$v"

1590 done

1591 ;;

1592 *)

1593 setviastring "$s"

1594 ;;

1595 esac

1596 }

1597 email4group() {

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

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

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

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

1602 sql="WITH

1603 grpemails AS (

1604 SELECT gname, user, val email

1605 FROM grp_mem NATURAL JOIN grp_mem_s

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

1607 useremails AS (

1608 SELECT user.name, val email

1609 FROM user

1610 LEFT JOIN user_m

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

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

1613 FROM useremails u LEFT JOIN grpemails g

1614 ON u.name=g.user

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

1616 query "$sql"

1617 }

1618 email4groupbyuid() {

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

1620 g=$1; shift

1621 uids=`echo "$@"`

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

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

1624 email4group "$g" `query "$sql"`

1625 }

1626 myemail4group() {

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

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

1629 }

1630 collectmembersbyid() {

1631 # Collect user names of group specified by grid

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

1633 query "SELECT user FROM grp_mem \

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

1635 }

1636 collectmembersbyid() {

1637 # Collect user names of group name

1638 qgrp=`sqlquote "$1"`

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

1640 }

1641 collectgecosesbyid() {

1642 # Collect user gecoses of group

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

1644 query<<-EOF

1645 SELECT gecos

1646 FROM gecoses

1647 WHERE name IN (SELECT user FROM grp_mem

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

1649 EOF

1650 }

1651 collectemail() (

1652 # Collect email addresses for group $1

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

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

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

1656 for e; do

1657 if isuser "$e"; then

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

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

1660 else

1661 qgrp=`sqlquote "$e"`

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

1663 gmem="grp_mem"

1664 else

1665 tm=`sqlquote "$TEAM"`

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

1667 fi

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

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

1670 $gmem g left join grp_mem_s s

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

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

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

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

1675 query "$sql"

1676 fi

1677 done

1678 )

1679 sendinvitation() (

1680 # $1=email

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

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

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

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

1685 gecos=`gecos`

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

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

1688 _m4 -D_URL_="$urlbase" \

1689 -D_USER_="$name" \

1690 -D_EMAIL_="$1" \

1691 -D_REGIST_="$regist" \

1692 -D_ADMIN_="$admin" \

1693 $msgdir/mail-invite.m4 \

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

1695 return 0

1696 )

1697 emaildomaincheck() {

1698 case "$1" in

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

1700 *@*)

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

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

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

1704 return 2

1705 fi

1706 return 0

1707 ;;

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

1709 esac

1710 }

1711 invite() {

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

1713 case "$email" in

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

1715 *@*)

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

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

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

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

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

1721 elif sendinvitation $email; then

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

1723 else # Cannot be reached here

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

1725 fi ;;

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

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

1728 esac

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

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

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

1732 fi

1733 if [ -f $invite_policy ]; then

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

1735 else

1736 pol="$invite_policy"

1737 fi

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

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

1740 -D_INVITE_POLICY_="$pol" \

1741 $layout/html.m4.html $layout/invite.m4.html

1742 }

1743 regist() {

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

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

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

1747 echo "bye bye" | html p

1748 reutrn

1749 fi

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

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

1752 cat<<EOF

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

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

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

1756 EOF

1757 return

1758 fi

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

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

1761 # Fake login password to wasureta

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

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

1764 wasureta $email

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

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

1767 addsession $1 # for removal after 1 minute

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

1769 $layout/login.m4.html

1770 return

1771 }

1772 group_safename() {

1773 # Convert $1 to safe group name

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

1775 }

1776 groupupdate() {

1777 gname=`getpar gname`

1778 qgname=`sqlquote "$gname"`

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

1780 # See ALSO same job in showgroup()

1781 newgname=`group_safename "$gname"`

1782 err newgname=$newgname

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

1784 err NewGNAME: gname=$newgname

1785 gname=$newgname

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

1787 replpar gname string "$gname"

1788 fi

1789 # Name confliction check

1790 parow=`getpar rowid`

1791 ## err parow=$parow

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

1793 query "BEGIN EXCLUSIVE;"

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

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

1796 if [ $count -gt 0 ]; then

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

1798 query "END;"

1799 return

1800 fi

1801 par2table $formdir/grp.def

1802 query "END TRANSACTION;"

1803 # Remove orphan

1804 : <<EOF

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

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

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

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

1809 EOF

1810 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1814 sql="delete from blog where id in

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

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

1817 query "$sql";

1818 unsetpar tag kwd

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

1820 return # and return

1821 fi

1822 fi

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

1824 fi

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

1826 grid=$(query $sql)

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

1828 grp $grid

1829 }

1830 groupclone() {

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

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

1833 case "$1" in

1834 */noteam)

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

1836 esac

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

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

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

1840 return

1841 fi

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

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

1844 return

1845 fi

1846 i=0

1847 while true; do

1848 copy="-copy$i"

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

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

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

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

1853 i=$((i++))

1854 continue

1855 fi

1856 break

1857 done

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

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

1860 query<<-EOF

1861 BEGIN;

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

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

1864 SELECT $newqname, key, val

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

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

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

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

1869 -- Copy members and their configuration --

1870 REPLACE INTO grp_mem SELECT $newqname, user

1871 FROM grp_mem WHERE gname=$qgrp;

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

1873 FROM grp_mem_s WHERE gname=$qgrp;

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

1875 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1876 -- Copy administrators --

1877 REPLACE INTO grp_adm SELECT $newqname, user

1878 FROM grp_adm WHERE gname=$qgrp;

1879 COMMIT;

1880 EOF

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

1882 STOPCLONEMSG=1 groupconf "$newrowid"

1883 }

1884 groupman() {

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

1886

1887 GF_STAGE="grpconf"

1888 GF_STAGE=groupupdate

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

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

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

1892 -D_DUMPTABLE_="syscmd(cat)" \

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

1894 }

1895 userconf() {

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

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

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

1899 }

1900 groupconf() {

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

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

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

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

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

1906

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

1908 grp=`getgroupbyid $rowid`

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

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

1911 return

1912 fi

1913

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

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

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

1917 ## Setup migration menu

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

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

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

1921 err v=$v

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

1923 migrate=$(cat<<-EOF

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

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

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

1927 <form action="?migrategrp">

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

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

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

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

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

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

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

1935 `cgi_hidden stage migrategrp`

1936 `cgi_hidden rowid $rowid`

1937 `cgi_submit OK`

1938 `cgi_reset Reset`

1939 </form>

1940 </div>

1941 EOF

1942 )

1943 fi

1944 fi

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

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

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

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

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

1950 <table>

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

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

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

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

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

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

1957 </table>

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

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

1960 </div>

1961 $migrate

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

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

1964 EOF

1965 fi

1966 }

1967 migrategrp() {

1968 rowid=`getpar rowid`

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

1970 grp=`getgroupbyid $rowid`

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

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

1973 return

1974 fi

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

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

1977 grp "$rowid"

1978 return

1979 fi

1980 destconf=`getpar migrateto`

1981 err destconf=$destconf

1982 if [ ! -e $destconf ]; then

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

1984 grp "$rowid"

1985 return

1986 fi

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

1988 srcconf=$worldconf

1989 else

1990 srcconf=s4-config.sh

1991 fi

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

1993 echo "移住操作" | html h1

1994 echo '<pre>'

1995 set -- "$srcconf" "$destconf" "$rowid"

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

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

1998 rc=$?

1999 echo "</pre>"

2000 if [ $rc -eq 0 ]; then

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

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

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

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

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

2006 else

2007 echo "移住失敗" | html p

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

2009 fi

2010 return

2011 }

2012 mems() {

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

2014 kwd=`getpar kwd`

2015 listmember $kwd

2016 }

2017 grps() {

2018 case "$S4WORLD" in

2019 $nonewgroupworld) ;;

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

2021 esac

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

2023 kwd=`getpar kwd`

2024 listgroup "$kwd" \

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

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

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

2028 $layout/form+dump.m4.html

2029 }

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

2031 gpg=`getpar grp`

2032 grid=${1:-$gpg}

2033 grp=`getgroupbyid "$grid"`

2034 ## . ./s4-blog.sh

2035 jg=`getpar joingrp`

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

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

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

2039 fi

2040 htmlheader=$layout/html.m4.html

2041 showgroup "$grid"

2042 }

2043 sql4interestblogs() {

2044 cat<<EOF

2045 CREATE TEMPORARY VIEW interestblogs AS

2046 SELECT blog.rowid rid, id, author

2047 FROM blog

2048 NATURAL JOIN

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

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

2051 THEN 1 -- blog owner is an user, READABLE

2052 WHEN (SELECT user FROM grp_mem

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

2054 THEN 0

2055 ELSE 1

2056 END;

2057 EOF

2058 }

2059 listnewblogsql() { # $1=user

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

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

2062 newdays=${newdays:-14}

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

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

2065 WHERE user='$user'

2066 AND tblrowid IN

2067 ($blogreadflagrowid,

2068 $blogcutoffflagrowid)),

2069 $basetime -- "0"

2070 );"`

2071 cat<<EOF

2072 `sql4interestblogs`

2073 WITH article_ctime as (

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

2075 FROM article join article_s s using(id)

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

2077 GROUP BY id

2078 ), blog_title_owner as (

2079 SELECT blg.rid brid, id,

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

2081 max(case key when 'owner' then val end) owner

2082 FROM interestblogs blg, blog_s using(id) group by id

2083 ), blogall as (

2084 SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid

2085 ), news as (

2086 SELECT brid, bl.id blid, bl.title, ctime,

2087 coalesce(al.time, '$deftime') atime,

2088 count(bl.id) "新着", bl.author

2089 FROM blogall bl

2090 LEFT JOIN

2091 (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al

2092 ON bl.brid=al.tblrowid

2093 WHERE atime < bl.ctime

2094 GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id

2095 LIMIT 10

2096 ) SELECT brid LINK, "新着",

2097 (SELECT count(*) FROM article WHERE blogid=blid) "総数",

2098 ctime, title,

2099 (SELECT gecos FROM gecoses WHERE name=author) gecos

2100 FROM news;

2101 EOF

2102 }

2103

2104 search_form() {

2105 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

2106 # $2(optional) = pre-input keywords

2107 help="(1)空白区切りの単語で本文検索

2108 (2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索

2109 @2016-0[1-6] → 2016年1月から6月

2110 @>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間

2111 @week → 最近一週間

2112 (3)#番号で記事ID検索

2113 (1)と(2)は組み合わせOK

2114 例: @2016-10-0[1-9] 芋煮

2115 → 2016年10月上旬でキーワード「芋煮」を含む記事検索

2116 ※クイズ板は検索対象から外されます。"

2117 auth=""

2118 placeholder="全記事からの検索"

2119 case "$1" in

2120 author=*)

2121 a=`echo "${1#author=}"|htmlescape`

2122 g=`gecos ${1#author=}`

2123 auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"

2124 placeholder="このユーザの書込検索"

2125 help="★★ $g さんの書き込みから検索します$nl$help"

2126 ;;

2127 grid=*)

2128 a=`echo "${1#grid=}"`; a=$((0 + $a))

2129 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

2130 placeholder="このグループからの検索"

2131 ;;

2132 esac

2133 inikwd="$2" # no need to htmlescape

2134 cat<<-EOF

2135 <div class="right">

2136 <form action="$myname">$auth

2137 <input type="text" name="kwd" value="$inikwd" title="$help"

2138 placeholder=" $placeholder " width="10" accesskey="k">

2139

2140 ${touchpanel:+<p class="help">$help</p>}

2141 <input type="hidden" name="stage" value="searchart">

2142

2143 </form>

2144 </div>

2145 EOF

2146 }

2147

2148 imgsrc_cache() (

2149 # $1 = directory for cache'ing

2150 # $2 = table (user_m or grp_m)

2151 # $3 = keycond (was: condition for choosingowner)

2152 # $4 = size : S = Small, M = Medium, O = Original

2153 dir="$1" tbl="$2"

2154 keycond="$3"

2155 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

2156 ORDER BY rowid DESC LIMIT 1"

2157 [ -d "$dir" ] || mkdir -p "$dir"

2158 tmpf=$tmpd/imgsrc_cache.$$

2159 case "$4" in

2160 [Ss]) size=S ;;

2161 [Oo]) size=O ;;

2162 *) size=M ;;

2163 esac

2164 # ImageCache filename storing schema:

2165 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

2166 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

2167 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

2168 valtype=`query "$sql0"`

2169 filename=${valtype%%//*}

2170 filetype=${valtype##*//file:}

2171 if [ x"$filename" = x"${filename%.*}" ]; then

2172 # If nor filename extension found, set it to image type

2173 case "$filetype" in

2174 image/*) filename=$filename.${filetype#image/} ;;

2175 esac

2176 fi

2177 cacheimg_S=$dir/S_$filename

2178 cacheimg_M=$dir/M_$filename

2179 cacheimg_O=$dir/$filename

2180 cacheimg=$dir/${size}_$filename

2181 sumfile="$dir/$filename.sum"

2182 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

2183 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2184 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2185 # if cache is fresh and has the same checksum,

2186 echo "<img src=\"$cacheimg\">"

2187 else

2188 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2189 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2190 fifo2=$fifo.2

2191 mkfifo $fifo $fifo2

2192 fmt=${filename##*.}

2193 ## [[ NOTE ]]

2194 ## a. convert oldimage newimage

2195 ## b. convert oldimage fmt:- | convert - newimage

2196 ## b is much smaller than a

2197 cat $tmpf | unhexize \

2198 | tee $fifo \

2199 | convert -define ${fmt}:size=${iconxy_M} \

2200 -resize ${iconxy_M}'>' - ${fmt}:- \

2201 | tee $fifo2 \

2202 | convert - "$cacheimg_M" &

2203 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2204 -resize ${iconxy_S}'>' - ${fmt}:- \

2205 | convert - "$cacheimg_S" &

2206 printf '%s' "<img src=\"data:${filetype},"

2207 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2208 echo '">'

2209 echo "$sum" > $sumfile

2210 fi

2211 ## Now preparing cache image, done.

2212 ## Store this information to DB

2213 stbl=${tbl%_m}_s # user_s or grp_s

2214 pkey=${keycond%%=*} # Primary Key name

2215 pval=${keycond#*=} # Primary Key value

2216 query <<-EOF

2217 REPLACE INTO $stbl($pkey, key, type, val)

2218 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2219 EOF

2220 )

2221

2222 showhome() {

2223 # $1=userRowIdToShow

2224 err showhome \$1=$1

2225 case "$1" in

2226 *@*) uname=`getvalbypkey user name "$1"` ;;

2227 *) uname=`getvalbyid user name $1` ;;

2228 esac

2229 ## err ShowHome: uname=$uname

2230 td=`getcachedir home/"$1"`

2231 gecos=`gecos "$uname"`

2232 ## err SH:gecos=$gecos

2233 GF_VIEWONLY=1

2234 cond="gname in (select gname from grp_mem where user='$uname')"

2235 search_form_args=""

2236 if [ x"$user" = x"$uname" ]; then

2237 if [ -z "$S4MASTERDB" ]; then

2238 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2239 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2240 elif [ -n "$S4MASTERURL" ]; then

2241 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2242 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2243 fi

2244 usermenu="$usermenu

2245 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2246 # Display folders

2247 sql="select count(id) from article_m where id

2248 in (select id from article where author='$user')

2249 and type like 'file:%';"

2250 ## err nfile-sql=`echo "$sql"`

2251 nfile=`query "$sql"`

2252 # err nfile=$nfile

2253 if [ $nfile -gt 0 ]; then

2254 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2255 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2256 fi

2257 else

2258 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2259 GROUP BY user;"`

2260 usermenu="<p>Last seen on $latestlog</p>"

2261 search_form_args="author=$uname"

2262 fi

2263 . ./s4-blog.sh

2264

2265 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2266 search_form "$search_form_args" > $sf

2267 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2268 | htmlescape > $tf

2269 { echo "<div class=\"noprofimg\">"

2270 viewtable $formdir/user.def user $1

2271 echo "</div>"

2272 } > $pf

2273

2274 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2275 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2276 imf=$tmpd/profimg.$$; touch $imf

2277 if [ -n "$img" ]; then

2278 if true; then

2279 tbl=user_m

2280 enticond="name='$uname'"

2281 imgsrc_cache "$td/main" user_m "$enticond" M

2282 else

2283 { printf '%s' "<IMG src=\"data:${img#file:},"

2284 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2285 | sed 's/$..$/%\1/g'

2286 echo '">'

2287 }

2288 fi > $imf

2289 fi

2290 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2291 val='$uname';"`

2292 ## REMOVE This comment block until 2019/7/1

2293 ## err "----- `gdate +%FT%T.%3N` ------------C"

2294 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2295 listblog $uname > $bf

2296 ## unset ddd

2297 ## err "----- `gdate +%FT%T.%3N` ------------D"

2298

2299 hometail=$tmpd/tail.$$

2300 mkfifo $hometail

2301

2302 #Calling listgroupbytable, originally here

2303

2304 (

2305 # Display Most Recent Entry

2306 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2307 shortval=${shortval:-val}

2308

2309 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2310 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2311 ctime,

2312 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2313 (SELECT gecos FROM gecoses

2314 WHERE name=(SELECT val FROM blog_s

2315 WHERE key='owner' AND id=b.id)) owner,

2316 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2317 FROM blog b

2318 JOIN

2319 (SELECT distinct blogid, a.id aid, max(val) ctime

2320 FROM article a, article_s s

2321 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2322 GROUP BY blogid ORDER BY val DESC LIMIT 50

2323 ) m

2324 ON b.id=m.blogid;"

2325 # This should be as follows

2326 : <<EOF

2327 WITH arts AS(

2328 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2329 a.blogid, a.id id, s.val ctime

2330 FROM article a NATURAL JOIN article_s s

2331 WHERE s.key = 'ctime' AND a.author='$user'

2332 GROUP by s.id

2333 )

2334 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2335 FROM arts a0

2336 JOIN

2337 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2338 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2339 ORDER BY ctime DESC LIMIT 50;

2340 EOF

2341

2342 cat<<-EOF

2343 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2344 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2345 <div class="lcto">

2346 `DT_VIEW=replyblog dumptable html blog`

2347 </div>

2348 EOF

2349 unset DT_SQL

2350 if [ x"$user" = x"$uname" ]; then

2351 # Display NEWS

2352 # 2016-06-26

2353 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2354 acclog blog $blogreadflagrowid

2355 # echo "全部既読にしました" | html p

2356 fi

2357 # 2016-02-19 Counting NEWS without using dumptable.

2358 sql=`listnewblogsql "$user"`

2359 # echo "$sql" > tmp/listnew

2360 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2361 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2362 cont=$((cont-1))

2363 ##err newcount=$cont

2364 if [ $cont -gt 0 ]; then

2365 #echo "全体の新着記事${cont}傑" | html h2

2366 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2367 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2368 cat<<-EOF | html form 'action="?home"'

2369 `cgi_checkbox readchk yes 'id="read"'`<label

2370 for="read">新着ふくめて全部読んだことにする</label>

2371 `cgi_submit '確定'`

2372 `cgi_hidden read yes`

2373 EOF

2374 echo "$new10 "

2375 echo "</div>"

2376 else # If news is 0, set log cut off flag

2377 acclog blog $blogcutoffflagrowid # for speed

2378 fi

2379 else # Not My Home ($user != $uname)

2380 : # DT_SQL=

2381 fi

2382 ) > $hometail & # Is background call safe to m4??

2383 #

2384 listgroupbytable $formdir/grp.def "$cond" $uname |

2385 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2386 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2387 -D_PROFIMG_="spaste(\`$imf')" \

2388 -D_BLOGS_="spaste(\`$bf')" \

2389 -D_SEARCH_="spaste(\`$sf')" \

2390 -D_NBLOG_="$nblog" \

2391 -D_GROUPS_="syscmd(\`cat')" \

2392 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2393 $layout/html.m4.html $layout/home.m4.html

2394

2395 # Record access log

2396 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2397 }

2398 commission() { # $1=grp-rowid $2=user-rowid

2399 contenttype; echo

2400 ## err commission: "$@"

2401 gname=`getgroupbyid $1`

2402 echo "グループ $gname 管理者委任" \

2403 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2404 if [ -n "$2" ]; then

2405 grp_reg_adm "$@"

2406 else

2407 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2408 fi

2409 }

2410 listgroupbytable() {

2411 # $1=deffile $2...=condition $3(optional)=uname

2412 tagline=`grep :tag: $1`;

2413 and="${2:+and }" where=${2:+where }

2414 href="<a href=\"$myname?grp+"

2415 echo '<div class="listgroup">'

2416 sql="select val from grp_s where key='tag' $and$2 group by val;"

2417 ## err ListGRP: query sql="$sql"

2418 for tag in `query "$sql"`

2419 do

2420 ## err ListGrp: tag=$tag

2421 tn=${tagline%%=${tag}*}

2422 tn=${tn##*[ :]}

2423 sql="select rowid||':'||gname as 'グループ名',説明 from

2424 (select (select rowid from grp g where g.gname=grp_s.gname)

2425 as rowid,

2426 gname,

2427 max(case key when 'gecos' then val end) as '説明',

2428 max(case key when 'tag' then val end) as 'tag',

2429 max(case key when 'mtime' then val end) as mtime from grp_s

2430 $where$2 group by gname having tag='$tag' order by mtime desc);"

2431 ## err PersonalGroupList= `echo $sql`

2432 echo "<h2>$tn</h2>"

2433 echo '<table class="b listgroup">'

2434 sq -header -html $db "$sql" \

2435 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2436 echo '</table>'

2437 done

2438 if [ -n "$S4WORLDLIST" -a -n "$3" ]; then

2439 err "peekgrpworlds($user) BEGIN: `gdate +%S.%03N`"

2440 peekgrpworlds mem:"$3"

2441 err "peekgrpworlds($user) END: `gdate +%S.%03N`"

2442 fi

2443 echo '</div>'

2444 }

2445 iconhref() (

2446 # $1=icon-file, $2=Href $3=title $4...=anchor

2447 data=`percenthex "$1"`

2448 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2449 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2450 href=$2; title=$3; shift 3

2451 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2452 )

2453 iconhref2() (

2454 # $1=icon-file, $2=Href $3=title $4...=anchor

2455 src=$1

2456 href=$2; title=$3; shift 3

2457 anchor=`echo $@|htmlescape`

2458 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2459 )

2460 listentry() (

2461 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2462 # Referring variable $iamowner=$grp to attach owner-request links

2463 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2464 cond='' hiddens=''

2465 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2466 if [ -z "$offset" ]; then

2467 offset=`getpar start`; offset=${offset%%[!0-9]*}

2468 offset=$((offset-1))

2469 fi

2470 offset=$((offset + 0)) # change to numeric forcibly

2471 [ $offset -lt 0 ] && offset=0

2472 limit=$listentlimit

2473 dir=`getcachedir "$1"`

2474 if [ x"$1" = x"user" ]; then

2475 hrb="$myname?home"

2476 deficon=person-default.png

2477 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2478 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2479 gcs=gecos

2480 else # if group

2481 hrb="$myname?grp"

2482 deficon=group-default.png

2483 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2484 gcs=name

2485 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2486 if [ -n "$tagline" ]; then

2487 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2488 ## err tagconv=$tagconv

2489 fi

2490 fi

2491 if [ ! -d $dir ]; then

2492 mkdir -p $dir

2493 fi

2494 if [ ! -s $dir/$deficon ]; then

2495 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2496 fi

2497 kwd=${2:-`getpar kwd`}

2498 if [ -n "$kwd" ]; then

2499 kwd=`echo $kwd | tr -d '";\n' | tr -d "'"`

2500 case "$kwd" in

2501 mem:*@*)

2502 byuser=${kwd#*mem:}

2503 qusr=`sqlquote "$byuser"`

2504 cond1="(a.gname IN (SELECT gname FROM grp_mem WHERE user=$qusr))"

2505 ;;

2506 esac

2507 if [ x"$1" = x"group" ]; then

2508 if [ -n "$cond1" ]; then

2509 enthead="`gecos "$byuser"|htmlescape` さんの所属"

2510 else

2511 cond1="(b.name like '%${kwd}%')"

2512 fi

2513 else

2514 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2515 fi

2516 fi

2517 tag=`getpar tag` tag2=`getpar tag2`

2518 if [ x"$tag" = x"NULL" ]; then

2519 tag="" tag2=""

2520 fi

2521 if [ -n "$tag$tag2" ]; then

2522 tag=${tag:-$tag2}

2523 qtag=`sqlquote "$tag"`

2524 cond2="tag=$qtag"

2525 fi

2526 if [ -n "$cond1$cond2" ]; then

2527 cond="$cond1${cond2:+ AND $cond2}"

2528 cond="WHERE ${cond# AND }"

2529 fi

2530

2531 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2532 # grpは呼出し元の動的スコープ変数でよくないな...

2533 ##qgrp=`sqlquote $grp`

2534 getgrp="(select gname from grp where rowid=${rowid:--1})"

2535 sql="select a.rowid, a.$link,

2536 coalesce(b.$gcs, a.$nm) as nick,

2537 quote(a.$nm) as qname,

2538 (SELECT val FROM ${tbl}_s

2539 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2540 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2541 || case when a.$nm in (select gname from grp)

2542 then printf('(%d名)',

2543 (select count(user) from grp_mem where gname=a.$nm))

2544 else ' <'||a.$nm||'>'

2545 end

2546 as name,

2547 b.tag,

2548 case when a.$nm in (select user from grp_adm

2549 where gname=$getgrp) then '管理者'

2550 when '$user' in (select user from grp_adm where gname=a.$nm)

2551 then 'ADMIN'

2552 when '$user' in (select user from grp_mem where gname=a.$nm)

2553 then 'Member'

2554 when '$iamowner' = '' then ''

2555 else ',not='||a.rowid end as ownerlink,

2556 CASE '$entity'

2557 WHEN 'グループ'

2558 THEN coalesce(

2559 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2560 'open')

2561 ||

2562 CASE WHEN '$user'

2563 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2564 THEN ' ismember'

2565 ELSE ''

2566 END

2567 ELSE 'user'

2568 END regmode

2569 from $tbl a left join

2570 (select $nm as name,

2571 max(case key when 'gecos' then val end) as gecos,

2572 max(case key when 'tag' then val end) as tag,

2573 max(case key when 'mtime' then val end) as mtime,

2574 max(case key when 'wtime' then val end) as wtime,

2575 max(case key when 'login' then val end) as login

2576 from ${tbl}_s group by $nm)

2577 b on a.$nm=b.name $cond $3

2578 order by b.wtime desc, b.login desc,

2579 b.mtime desc, b.tag desc, a.rowid asc"

2580 # Give precedence to newer maintained groups (2016-09-24)

2581 # Note that mtime is stored only in grp_s.

2582 ## err LE:sql.1="$sql"

2583 total=`query "with x as ($sql) select count(*) from x;"`

2584 echo "$enthead${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2585 echo '<div class="listentry">' # List-entry div

2586 # Show owner/member filter button

2587 METHOD=GET

2588 hiddens="$hiddens

2589 `cgi_hidden stage \"$stage\"`"

2590 if [ x"$tbl" = x"grp" ]; then

2591 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2592 fh="<select name=\"tag\">$nl"

2593 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2594 for l in $args; do

2595 val=${l#*=} tname=${l%=*}

2596 if [ x"$val" = x"$tag" ]; then

2597 s=" selected"

2598 selectedtags="(種別[${tname}]のみ)"

2599 else

2600 s=""

2601 fi

2602 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2603 done

2604 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2605 cat<<-EOF

2606 <form action="$myname" method="$METHOD">

2607 </form>

2608 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2609 for="ismembtn">参加中以外隠す</label>

2610 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2611 for="isadmbtn">管理者参加以外隠す</label>

2612 EOF

2613 # limit=3

2614 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2615 fi

2616 if [ $total -gt $limit -o $ -n "$S4WORLDLIST" -a x"$tbl" = x"grp" $ ]; then

2617 echo '<div>'

2618 METHOD=GET cgi_form $stage <<EOF

2619 $form

2620 <label>次の語を含む${entity}で検索:

2621 `cgi_text kwd "$kwd"`</label>

2622 $hiddens

2623 EOF

2624 echo '</div>'

2625 else

2626 echo $selectedtags | html p

2627 fi

2628 test -n "$kwd" && hiddens="$hiddens$nl`cgi_hidden kwd \"$kwd\"`"

2629 cat<<EOF

2630 <form action="$myname" method="$METHOD">

2631 <p>${total}件中の<input class="hidesub" type="text" name="start"

2632 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2633 <input type="submit" value="確定"></p>

2634 </form>

2635 EOF

2636 if [ $((offset+limit)) -lt $total ]; then

2637 nextbtn=$(

2638 cat<<EOF

2639 <div class="right clear"><form action="$myname" method="$METHOD">

2640 `cgi_submit 次の${limit}件`

2641 $hiddens

2642 `cgi_hidden offset $((offset + limit))`</form></div>

2643 EOF

2644 )

2645 fi

2646 if [ $offset -gt 0 ]; then

2647 prevbtn=$(

2648 cat<<EOF

2649 <form action="$myname" method="$METHOD">

2650 `cgi_submit 前の${limit}件`

2651 $hiddens

2652 `cgi_hidden offset $((offset - limit))`</form>

2653 EOF

2654 )

2655 fi

2656 pnbtn="$nextbtn$prevbtn"

2657 echo $pnbtn

2658

2659 ## err ListEntry: `echo "$sql"\;`

2660 # sq $db here??? 2016-11-28

2661 query "$sql limit $limit ${offset:+offset $offset};" \

2662 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2663 # err name=$name owner=$ownerp lnk=$lnk

2664 # err newlnk=$lnk regmode=$regmode

2665 icondir=$dir/$id

2666 # Pick up only last icon

2667 htmlname=`echo $name|htmlescape`

2668 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2669 <p class=\"tag _$tag\">$tag</p>" \

2670 | _m4 $tagconv

2671 if [ -n "$NOSPEEDUP" ]; then

2672 files=`getvalbyid $tbl profimg $id $icondir`

2673 if [ -n "$files" ]; then

2674 icon=`echo "$files"|tail -1`

2675 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2676 else

2677 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2678 fi

2679 elif [ -n "$icon" -a -s "$icon" ]; then

2680 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2681 else

2682 cond="$nm=$qname"

2683 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2684 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2685 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2686 # err "img=[$img]"

2687 if [ -n "$img" ]; then

2688 echo "<a href=\"$hrb+$lnk\">"

2689 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2690 echo "</a>"

2691 else

2692 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2693 fi

2694 fi

2695 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2696 echo "</div>"

2697 done

2698 echo "</div>" # End of List-entry div

2699 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2700 if [ -n "$kwd" -a x"$tbl" = x"grp" -a -n "$S4WORLDS" ]; then

2701 peekgrpworlds "$kwd"

2702 fi

2703 )

2704 listmember() {

2705 listentry user "$@"

2706 }

2707 listgroup() {

2708 listentry group "$@"

2709 }

2710 hexteams() { # $1=gname, $2(optional)=user

2711 cond=${2:+" AND user='$2'"}

2712 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2713 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2714 }

2715 showgroup() { # $1=group-rowid

2716 if [ -z "$1" ]; then

2717 grid=`getpar grid`

2718 grid=${grid%%[!0-9]*}

2719 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2720 else

2721 grid=$1

2722 fi

2723 grp=`getgroupbyid $grid`

2724 qgrp=`sqlquote "$grp"`

2725 htmlgrp=`echo "$grp"|htmlescape`

2726 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2727 if isgroup "$grp"; then

2728 tf=$tmpd/title.$$

2729 sf=$tmpd/search.$$

2730 bodyclass=`query "SELECT val FROM grp_s

2731 WHERE gname=$qgrp AND key='regmode';"`

2732 if ismember "$user" "$grp"; then

2733 ismember="ismember"

2734 bodyclass="$bodyclass${bodyclass:+ }ismember"

2735 else

2736 ismember="" # bodyclass="group"

2737 fi

2738 bodyclass="$bodyclass grouphome"

2739 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2740 echo "グループ $htmlgrp" > $tf

2741

2742 showgroupsub $formdir/grp.def "$grid" | \

2743 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2744 -D_FORM_="syscmd(\`cat')" \

2745 -D_BODYCLASS_="$bodyclass" \

2746 -D_DUMPTABLE_="" \

2747 $htmlheader $sf $layout/form+dump.m4.html

2748 # $htmlheader $layout/form+dump.m4.html

2749 # $htmlheader is defined in grp()

2750 else # if $grp is removed at par2table

2751 listgroup

2752 fi

2753 }

2754 showgroupsub() {

2755 # $1=def-file $2=group-rowid

2756 # Using $ismember

2757 rowid=$2

2758 grp=`getgroupbyid $2`

2759 qgrp=`sqlquote "$grp"`

2760 td=`getcachedir grp/"$2"`

2761 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2762 if [ -z "$rowid" ]; then

2763 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2764 #grp=`sq $db "select gname from grp where rowid=$grp"`

2765 echo "showgroupsub: invalid argument($1 $2)" | html p

2766 return

2767 fi

2768 val=`getvalbyid grp profimg $rowid $tmpd`

2769 enticond="gname=$qgrp"

2770 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2771 if [ -n "$img" ]; then

2772 cat<<-EOF

2773 <p class="groupimg">

2774 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2775 EOF

2776 fi

2777 echo "<div class=\"noprofimg\">"

2778 viewtable $1 grp $rowid

2779 echo "</div>"

2780 if isgrpowner "$user" "$grp"; then

2781 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2782 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2783 iamowner=$rowid

2784 colmd=" mode"

2785 fi

2786 if [ -n "$ismember" ]; then

2787 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2788 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2789 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2790 cat<<-EOF

2791 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2792 href="?blog+$rowid">グループの新規話題作成</a>

2793 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2794 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2795 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2796 <div class="fold clear">

2797 `cgi_checkbox send yes id="send"`<label

2798 for="send">グループ全員にメッセージ送信</label>

2799 <div>

2800 `cgi_textarea message "" "cols=60"`

2801 `cgi_submit 送信`

2802 `cgi_reset リセット`

2803 </div>

2804 `cgi_hidden grp $rowid`

2805 </div></form>

2806 EOF

2807 fi

2808 # 加入ボタン + 加入者リスト

2809 if [ -n "$ismember" ]; then

2810 ismem='checked' state="(参加中)"

2811 else

2812 nomem='checked' state="(現在非加入)"

2813 fi

2814 # このグループでの加入アドレス

2815 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2816 and key='email';"`

2817 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2818 ## and key='email';"

2819 ##err email=$eml

2820 cat <<EOF

2821 <div class="fold clear">

2822 `cgi_checkbox reg yes id="reg"`<label

2823 for="reg">自身の加入状態を操作する</label>$state

2824 <div>

2825 EOF

2826 cgi_form grp <<EOF

2827 <p>このグループに</p>

2828 <table class="b">

2829 <tr><th>メンバーとして</th><td>

2830 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2831 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2832 <tr><th>参加する場合のメイルアドレス<br>

2833 <small>(メインのアドレスとは違うものにする場合に記入<br>

2834 同じでよい場合は空欄に)</small></th>

2835 <td>`cgi_text email $eml`</td></tr>

2836 </table>

2837 `cgi_hidden grp $rowid`

2838 EOF

2839 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2840 echo "moderated (承認加入の)グループなので実際に参加できるのは

2841 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2842 fi

2843 echo '</div></div>'

2844 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2845 cat<<-EOF

2846 <div class="fold">

2847 <h2>話題一覧</h2>

2848 <form class="summary inline" action="$myname" title="$thelp">

2849 `cgi_hidden owner "$grp"`

2850 `cgi_hidden kwd "@week"`

2851 `cgi_hidden stage searchart`

2852 `cgi_submit "一週間のまとめ"`

2853 </form>

2854 <input type="checkbox" id="dtcheck" checked><label

2855 for="dtcheck">話題一覧表示</label>

2856 EOF

2857 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2858 colstate="state:稼動状態:frozen=rowclass=凍結"

2859 DT_CHLD=article:blogid \

2860 DT_QOWNER="$qgrp" \

2861 DT_VIEW=replyblog dumptable html blog \

2862 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2863 echo "</div> "

2864

2865 getgname="(select gname from grp where rowid=$rowid)"

2866 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2867 cm="?commission+$rowid"

2868 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2869 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2870 # team list

2871 hexteams=`hexteams "$grp"`

2872 if [ -n "$hexteams" ]; then

2873 echo "チーム一覧" | html h2

2874 echo '<div class="dumptable"><table class="b">'

2875 sq $db -html -header<<-EOF

2876 SELECT val TEAM,

2877 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2878 MEMBERS

2879 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2880 EOF

2881 echo '</table></div>'

2882 fi

2883 }

2884 grp_getbodyclass() {

2885 # Get css class name for document.

2886 # `moderated' for moderated groups

2887 # `ismember' for groups where user belongs

2888 # $1=GroupName (w/o quote)

2889 # $user=userNameCurrentlyLogin

2890 ## err grp_getbodyclass: 1="$1"

2891 qgrp=`sqlquote "$1"`

2892 query<<-EOF

2893 SELECT coalesce(

2894 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2895 'open')

2896 ||

2897 CASE WHEN '$user'

2898 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2899 THEN ' ismember'

2900 ELSE ''

2901 END;

2902 EOF

2903 }

2904 grpaction() { # $1=group-rowid

2905 err GRP_ACTION:IN

2906 grid=${1:-`getpar grp`}

2907 grp=`getgroupbyid "$grid"`

2908 htmlgrp=`echo "$grp" | htmlescape`

2909 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2910 if [ -z "$grp" ]; then

2911 echo "無効な指定です。" | html p; return

2912 fi

2913 if ! ismember $user "$grp"; then

2914 echo "加入者のみに許可された操作です。" | html p; return

2915 fi

2916 isowner=""

2917 isgrpowner "$user" "$grp" && isowner="yes"

2918 err 2=$2 3=$3

2919 case "$2" in

2920 get:teamcsv)

2921 teamcsv "$grid"

2922 return

2923 ;;

2924 esac

2925 echo "グループ $grp 個別選択操作" \

2926 | _m4 -D_TITLE_="syscmd(\`cat')" \

2927 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2928 $layout/html.m4.html

2929

2930 usel=`getpar usel`

2931 if [ -n "$usel" ]; then

2932 uids=$(echo `echo $usel`|tr ' ' ',')

2933 ## err grpaction-1: grp=$grp, `echo $sql`

2934 text=`getpar text|tr -d '\r'`

2935

2936 rm=`getpar rm` cfm=`getpar confirm`

2937 ## err rm=$rm cfm=$cfm

2938 if [ x"$rm" = x"yes" ]; then

2939 if [ "$isowner" ]; then

2940 if [ x"$rm$cfm" = x"yesyes" ]; then

2941 # Eliminate

2942 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2943 for tbl in grp_mem grp_mem_s grp_mem_m; do

2944 sql="delete from $tbl $cond;"

2945 # echo "sql=$sql"

2946 query "$sql"

2947 err rmGRPuser "$sql"

2948 done

2949 num=`query "select count(*) from user where rowid in ($uids);"`

2950 #err num=$num

2951 if [ 0$num -gt 0 ]; then

2952 sql="select coalesce(b.val,a.name) from user a left join \

2953 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2954 # err `echo "$sql"`

2955 html pre<<EOF

2956 以下の${num}名のグループ $grp 登録を解除しました。

2957 `query "$sql"`

2958 EOF

2959 fi

2960 else

2961 echo "確認のチェックがないのでやめておきます。" | html p

2962 return

2963 fi

2964 else # not Group Owner

2965 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2966 return

2967 fi

2968 cat<<EOF

2969

2970 EOF

2971 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2972 if [ -z "$text" ]; then # if msg is empty

2973 echo "なにかメッセージを..." | html p

2974 return 0

2975 fi

2976 gecos=`gecos $user`

2977 safegc=`echo "$gecos" | tr -d '<>@'`

2978 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2979 fromad=`myemail4group "$grp"`

2980 ###mail_from="$safegc <$fromad>"

2981 mail_from="$safegc <$user>" # TEST: 2020/5/13

2982 test -n `getpar sender` &&

2983 export SENDER=$user # TEST: 2020/5/15

2984 replyto=$fromad

2985

2986 ## Start parse of attachment files

2987 if [ -n "`getpar email`" ]; then

2988 ar=`getpar supprcpt`

2989 if [ -n "$ar" ]; then

2990 for a in $ar; do

2991 if checkdomain "$a"; then

2992 supprcpt="$supprcpt $a"

2993 else

2994 err "SupprcptErr=[$a] by $user"

2995 removercpt="$removercpt $a"

2996 fi

2997 done

2998 fi

2999 subj=`getpar subject`

3000 afiles=""

3001 for fn in `query "SELECT DISTINCT val FROM par WHERE var='image' AND sessid='$session';"`

3002 do

3003 f=$tmpd/$fn

3004 if [ -s $f ]; then

3005 afiles=$afiles"${afiles:+ }$f"

3006 fi

3007 done

3008 else

3009 preface=$(cat <<-EOF

3010 $url

3011 のグループ「$grp」のメンバーである $gecos さんから、

3012 あなた宛のメッセージです。

3013 ----------------------------------------------------------

3014 EOF

3015 )

3016 fi

3017 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

3018 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

3019 subj="${subj:-$gecos さんからのメッセージ}"

3020 REPLYTO=$replyto

3021 MAIL_FROM=$mail_from

3022 export REPLYTO SMAIL_TO MAIL_FROM

3023 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

3024 for r in $rcpts; do

3025 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

3026 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

3027 else

3028 # Show sender and rcpts address for guest

3029 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

3030 fi

3031 if [ -n "$afiles" ];then

3032 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

3033 else

3034 smail "$r" "$subj"

3035 fi <<EOF

3036 ${preface:+$preface$nl}$text

3037 EOF

3038 done

3039 if [ $? = 0 ]; then

3040 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

3041 sql="select coalesce(b.val, a.name) from

3042 (select name from user where rowid in ($uids)) a

3043 left join user_s b on a.name=b.name and b.key='gecos';"

3044 html pre<<EOF

3045 `query "$sql"`

3046 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

3047 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

3048 EOF

3049 err SendDone: `echo $sql`

3050 fi

3051 elif [ x"$rm" = x"commission" ]; then

3052 grp_reg_adm $grid $usel

3053 elif [ x"$rm" = x"addteam" ]; then

3054 team=`getpar team|sed "s/'/''/g"` # for single quotation

3055 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

3056 if [ x"$team" != x"$newteam" ]; then

3057 echo "チーム名に使えない文字を除去しました" | html p

3058 team=$newteam

3059 fi

3060 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

3061 cat<<-EOF | html p

3062 有効なチーム名を入力してください。

3063 カンマだけ、「なし」という名前は使えません。

3064 EOF

3065 echo "有効なチーム名を入力してください。" | html p

3066 else

3067 grp_add_team $grid "$team" $usel

3068 fi

3069 elif [ x"$rm" = x"rmteam" ]; then

3070 if [ x"yes" = x"`getpar teamconfirm`" ]; then

3071 rmteam=`getpar rmteam|sed "s/'/''/g"`

3072 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

3073 gname='$grp' AND user='$user' AND key='team'\

3074 AND val='$rmteam';\"`" ]; then

3075 grp_rm_team $grid "$rmteam" $usel

3076 else

3077 echo "所属していないチームの除去操作はできません。"|html p

3078 fi

3079 else

3080 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

3081 fi

3082 fi

3083 fi

3084 # POST count summary

3085 from=`getpar from`; to=`getpar to`

3086 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3087 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3088 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

3089 # New entry

3090 sql="WITH mems AS (

3091 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3092 ON gm.user=g.name

3093 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3094 ), target_article AS (

3095 SELECT id FROM article_s

3096 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3097 ), posts AS (

3098 SELECT author, count(author) post

3099 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3100 WHERE blogid IN (SELECT id FROM blog_s

3101 WHERE key='owner'

3102 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3103 AND key='text'

3104 GROUP BY author

3105 ), teams AS (

3106 SELECT user, group_concat(val, ', ') team

3107 FROM mems m LEFT JOIN grp_mem_m gm ON m.name=gm.user

3108 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3109 AND key='team'

3110 GROUP BY user

3111 ), user_post AS (

3112 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3113 FROM mems m LEFT JOIN posts

3114 ON m.name=posts.author

3115 GROUP by m.rowid

3116 )

3117 SELECT

3118 CASE

3119 WHEN (SELECT user FROM grp_adm

3120 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3121 AND user=up.name) IS NOT NULL

3122 then 'k'

3123 ELSE ''

3124 END || rowid || ','

3125 || rtrim(substr(name, 1, instr(name, '@')), '@') || ','

3126 || gecos NAME,

3127 post POST, team _TEAM_

3128 FROM user_post up LEFT JOIN teams t

3129 ON up.name=t.user

3130 ORDER BY name;"

3131 ## err grpaction: "`echo \"$sql\"`"

3132 tf=$tmpd/title.$$

3133 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3134 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3135 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3136 for=\"cmadmin\">管理者委任</label>

3137 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3138 </p></div>"

3139 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3140 title=\"Shortcut: g${nl}Remove from the Group\"

3141 for=\"conf\">GRP登録解除</label>

3142 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3143 <p>この操作による通知は本人に行きません。

3144 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3145 </div>"

3146 # Get team list to which current user belongs into $hexteams

3147 allhexteams=$(hexteams "$grp")

3148 if [ -n "$isowner" ]; then

3149 myhexteams="$allhexteams" # admin can remove all teams' attr

3150 else

3151 myhexteams=$(hexteams "$grp" "$user")

3152 fi

3153 if [ -n "$isowner" -a -n "$allhexteams" ]; then

3154 gettingcsv="<p>Download: <a href=\"?getteamcsv+$grid\">Team.csv</a> (Zoom Breakout Room 事前割り当てに使えます)</p>"

3155 fi

3156 if [ -n "$myhexteams" ]; then

3157 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3158 title=\"Shortcut: s${nl}Strip a team tag from\"

3159 for=\"cmrmteam\">チーム属性除去</label>

3160 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3161 を除去します: `cgi_checkbox teamconfirm yes` 確認

3162 <p>この操作による通知は本人に行きません。

3163 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3164 </div>

3165 "

3166 fi

3167 stf=$tmpd/selteam.$$

3168 cgi_select_h selteam "5445414d" $allhexteams > $stf

3169 b1='<label> <input type="checkbox" name="usel" value="'

3170 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3171 br='<span id="reverse" title="Reverse Selection"></span>'

3172 #b2='"> <span>' b3='</span></label>'

3173 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

3174 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

3175 lnk='"> <span>\5</span></label> [<a href="?home+\3">HOME</a>]'

3176 #-e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

3177 cgi_form grpaction<<EOF \

3178 | sed -E \

3179 -e "s|^(<TR><TD)>(k?)([0-9]*),([^,]+),([^<]*)|\1 title=\"\4\">\2$b1\3$lnk|" \

3180 -e 's/^(<TR><TD[^>]*>)k(<label)/\1\2 class="admin"/' \

3181 -e "s|^(<TR><TH>)(NAME)|\1$br \2|" \

3182 | _m4 -D_TITLE_="spaste(\`$tf')" \

3183 -D_SUBTITLE_="チェック後操作ボタン" \

3184 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3185 $layout/form+dump.m4.html \

3186 | _m4 -D_TEAM_="spaste(\`$stf')"

3187 <p>下でチェックした人を対象として:</p>

3188 <div class="foldtabs">

3189 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3190 title="Shortcut: a${nl}Add a team tag to"

3191 for="cmteam">同じチーム属性を付与</label>

3192 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3193 `cgi_datalist_h teams $allhexteams`

3194 </div>

3195 ${rmteammsg}

3196 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3197 title="Shortcut: d${nl}DirectMail to"

3198 for="sendmsg" title="Direct Message">DM送信</label>

3199 <div>

3200 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3201 title="Using email format">email書式を使う</label>

3202 <div class="folded">

3203 <table>

3204 <tr><td>From: </td><td>$user</td></tr>

3205 <tr><td>このFrom:で送る</td>

3206 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3207 </td></tr>

3208 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3209 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3210 <tr><td>ファイル添付: </td>

3211 <td>`cgi_file image "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3212 </table>

3213 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 送信`)</p>

3214 </div>

3215 <div>本文:`cgi_textarea text "" cols=72`

3216 </div>

3217 </div>

3218 ${isowner:+$cmmsg$excmsg}

3219 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3220 </div>

3221 <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote

3222 <table class="td2r thl">

3223 `sq $db -header -html "$sql"`

3224 </table>

3225 `cgi_hidden grp $grid`

3226 `cgi_hidden myuid $myuid id="myuid"`

3227 EOF

3228 }

3229 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3230 # Create TEMPORARY VIEW

3231 query<<EOF

3232 CREATE TEMPORARY VIEW writeusers AS

3233 SELECT DISTINCT author FROM article

3234 WHERE id in (

3235 select id from article where blogid=(select id from blog where rowid=$1)

3236 );

3237 CREATE TEMPORARY VIEW movablegroups AS

3238 SELECT g.rowid growid , g.gname

3239 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3240 ON grp.gname=gm.gname -- そのユーザが属している

3241 AND user='$user') g -- グループに絞る

3242 WHERE (SELECT author FROM writeusers

3243 EXCEPT

3244 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3245 IS NULL;

3246 $2

3247 EOF

3248 }

3249 sql4readableblogs() {

3250 # Create view of blogs that can be readable to $user

3251 # Blog is readable when:

3252 # 1: blog owner is an user

3253 # 2: else, 2.1: owner-group where the $user belongs

3254 # 2.2: else, owner-group is not moderated

3255 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3256 cat<<EOF ## | tee tmp/sql.out

3257 CREATE TEMPORARY VIEW readableblogs AS

3258 SELECT blog.rowid rid, id, author

3259 FROM blog

3260 NATURAL JOIN

3261 (SELECT id,

3262 max(CASE key WHEN 'owner' THEN val END) owner,

3263 max(CASE key WHEN 'mode' THEN val END) mode

3264 FROM blog_s GROUP by id) bs

3265 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3266 THEN 1 -- blog owner is an user, READABLE

3267 WHEN (SELECT val FROM grp_s

3268 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3269 AND

3270 (SELECT user FROM grp_mem

3271 WHERE gname=bs.owner AND user='$user') IS NULL

3272 THEN 0

3273 WHEN mode IN ('quiz', 'enquete')

3274 THEN 0 -- "quiz" mode blog cannot be searched

3275 ELSE 1

3276 END;

3277 EOF

3278 }

3279 mvteamform() {

3280 owner=$1

3281 hexteams=$(hexteams "$owner" "$user")

3282 test -z "$hexteams" && return

3283 none="`echo なし|hexize`"

3284 cat<<-EOF

3285 <!-- <div class="fold">

3286 `cgi_checkbox mv2team send id="mv2team"`<label

3287 for="mv2team">この話題を以下のチームのものにする</label>

3288 <div> -->

3289 <p>この話題をチーム所有にする:

3290 チーム: `cgi_select_h mv2team $none $hexteams`</p>

3291 </form></div></div>

3292 EOF

3293 }

3294 editheading() { # $1=rowid-of-heading

3295 rowid=${1%%[!A-Z0-9a-z_]*}

3296 if [ -z "$rowid" ]; then

3297 echo "話題番号が未指定です。" | html p

3298 return

3299 fi

3300 owner=`getvalbyid blog owner $rowid`

3301 title=`getvalbyid blog title $rowid`

3302 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3303 | _m4 -D_TITLE_="修正" \

3304 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3305 -D_BLOGS_="" -D_DUMPTABLE_="" \

3306 -D_FORM_="syscmd(\`cat')" \

3307 $layout/html.m4.html $layout/form+dump.m4.html

3308 # Move to group

3309 if isuser "$owner"; then

3310 crview4article $rowid

3311 n=`query "SELECT count(*) FROM writeusers;"`

3312 ## err N=$n

3313 if [ $((n)) -gt 0 ]; then

3314 ## err ROWID=$rowid

3315 sql="SELECT growid || ':' || gname FROM movablegroups;"

3316 cat<<-EOF

3317 <div class="fold">

3318 `cgi_checkbox mv send id="mv"`<label

3319 for="mv">この話題をグループ所有に移動する</label>

3320 <div>

3321 <form action="?mvart" method="POST" enctype="multipart/form-data">

3322 移動先グループ:

3323 <select name="mv2grp">

3324 EOF

3325 query ".mode html"

3326 query<<-EOF |

3327 $sql

3328 .mode list

3329 EOF

3330 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3331 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3332 cat<<-EOF

3333 </select>

3334 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3335 そのグループに加入しているものに限られます)</p>

3336 <p>`cgi_checkbox cfm yes`<label>確認

3337 (この操作は元に戻すことができません)</label></p>

3338 `cgi_hidden blogrowid $rowid`

3339 `cgi_submit 移動`

3340 `cgi_reset Reset`

3341 </form>

3342 </div>

3343 </div>

3344 EOF

3345 fi

3346 # end of isuser "$owner"

3347 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3348 [ -n "$hexteams" ];}; then

3349 none="`echo なし|hexize`"

3350 cat<<-EOF

3351 <div class="fold">

3352 `cgi_checkbox mv2team send id="mv2team"`<label

3353 for="mv2team">この話題を以下のチームのものにする</label>

3354 <div><p>現在の所属チーム設定:

3355 `query "SELECT

3356 coalesce((SELECT val FROM blog_s

3357 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3358 AND key='team'),

3359 ':なし');"`</p>

3360 <form action="?mvart" method="POST" enctype="multipart/form-data">

3361 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3362 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3363 `cgi_hidden blogrowid $rowid`<br>

3364 `cgi_submit 移動`

3365 `cgi_reset Reset`

3366 </form></div></div>

3367 EOF

3368 fi

3369 }

3370 mvart() { # move diary to some group or team

3371 # or move blog of group to team which belong to the group

3372 blogrowid=`getpar blogrowid`

3373 cfm=`getpar cfm`

3374 ##### echo move blog:$blogrowid to $mv2grp | html p

3375 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3376 . ./s4-blog.sh

3377 if [ -z "$blogrowid" ]; then

3378 echo "無効な指定です(mvart)。" | html p

3379 return

3380 elif [ x"$cfm" != x"yes" ]; then

3381 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3382 elif { mv2grp=`getpar mv2grp`

3383 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3384 [ -n "$mv2grp" ]; }; then

3385 crview4article $blogrowid

3386 ########## TRANSACTION BEGIN

3387 query "BEGIN;"

3388 n=`query "SELECT count(*) FROM writeusers;"`

3389 ## err Nwriteuser=$n

3390 if [ $((n)) -gt 0 ]; then

3391 query<<-EOF

3392 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3393 WHERE key='owner'

3394 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3395 AND $mv2grp IN (SELECT growid FROM movablegroups);

3396 EOF

3397 fi

3398 query "END;"

3399 ########## TRANSACTION END

3400 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3401 [ -n "$mv2team" ];}; then

3402 # blog owner can move it to ANY team

3403 case "$mv2team" in

3404 'なし')

3405 cat<<-EOF

3406 DELETE FROM blog_s

3407 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3408 AND key='team';

3409 EOF

3410 ;;

3411 "") ;;

3412 *)cat<<-EOF

3413 BEGIN;

3414 REPLACE INTO blog_s(id, key, val)

3415 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3416 'team', '$mv2team');

3417 REPLACE INTO blog_s(id, key, val)

3418 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3419 'notify', 'all'); -- Change notify to all

3420 END;

3421 EOF

3422 esac | query

3423 fi

3424 blog_reply $blogrowid

3425 echo yes | html p

3426 }

3427 editart() { # $1=article-rowid $2=blogrowid

3428 rowid=${1%%[!A-Z0-9a-z_]*}

3429 blogrowid=${2%%[!A-Z0-9a-z_]*}

3430 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3431 echo "表示する記事番号が未指定です。" | html p

3432 return

3433 fi

3434 owner=`getvalbyid blog owner $blogrowid`

3435 title=`getvalbyid blog title $blogrowid`

3436 author=`getvalbyid article author $rowid`

3437 ## err EDITart: owner=$owner, author=$author

3438 if isgrpowner "$user" "$owner"; then

3439 : EDIT OK

3440 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3441 echo "本人か所有者しか編集できません." | html p

3442 return

3443 fi

3444 aid=`query "select id from article where rowid=$rowid;"`

3445 tmpout=$tmpd/editart.$$.out

3446 GF_ACTION="?replyblog+$blogrowid#$aid" \

3447 edittable $formdir/article.def article $rowid \

3448 > $tmpout

3449 rm -f /tmp/editart.out

3450 # Cannot use pipelining to m4 with genform() because of stdin stack

3451 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3452 -D_FORM_="syscmd(cat $tmpout)" \

3453 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3454 -D_BLOGS_= -D_DUMPTABLE_= \

3455 $layout/html.m4.html $layout/form+dump.m4.html

3456 }

3457 send2mem() {

3458 rowid=`getpar grp`

3459 rowid=${rowid%%[!0-9]*} # Cleaning

3460 if [ -z "$rowid" ]; then

3461 echo "グループが未指定です。" | html p

3462 return

3463 fi

3464 message=`getpar message`

3465 if [ -z "$message" ]; then

3466 echo "文章を入れてください。" | html p

3467 return

3468 fi

3469 grp=`getgroupbyid $rowid`

3470 members=`collectemail "$grp"`

3471 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3472 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3473 mailfrom="`gecos "$user"` <$mailfrom>"

3474 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3475 msg=$(cat<<-EOF

3476 $urlbase?grp+$rowid

3477 グループ $grp に所属する

3478 `gecos $user` さんよりメッセージ:

3479

3480 $message

3481 EOF

3482 )

3483 # smail rcpt subj (file)

3484 for m in $members; do

3485 echo "$msg" |

3486 MAIL_FROM=$mailfrom \

3487 SENDER=$noreply \

3488 REPLYTO=$mailfrom \

3489 smail "$m" "$sj"

3490 done

3491 cat<<EOF

3492 <p>以下のユーザに送信しました。</p>

3493 <pre>

3494 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3495 </pre>

3496 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3497 EOF

3498 }

3499 joingrpadmit() {

3500 # $1=yes/no $2=session-key

3501 if [ -z "$2" ]; then

3502 echo "bye bye" | html p; return

3503 fi

3504 t_usr=`session=$2 getpar adduser`

3505 t_grp=`session=$2 getpar group`

3506 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3507 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3508 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3509 echo "無効な加入依頼です。" | html p

3510 echo "有効期限が切れたか、

3511 他の管理者がいる場合は処理済みの可能性があります。" | html p

3512 return

3513 fi

3514 if ! isgrpowner "$user" "$t_grp"; then

3515 echo "グループ管理者のみの機能です。" | html p; return

3516 fi

3517 case $1 in

3518 yes) joingrp "$t_grp" "$t_usr" yes ;;

3519 no) joingrp "$t_grp" "$t_usr" no ;;

3520 *)

3521 echo "無効な指定です($1)。" | html p

3522 return ;;

3523 esac

3524 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3525 rcpts="`getgroupadminmails "$t_grp"` $user"

3526 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3527 body="に

3528 $t_usr

3529 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3530 しました。"

3531 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3532 query "delete from session where id='$2';"

3533 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3534 }

3535

3536 joingrprequest() {

3537 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3538 jss="joingrp-`date +%s`-`genrandom 12`"

3539 addsession $jss +${memoplimitdays}days

3540 grpadmins=`getgroupadmins "$1"`

3541 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3542 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3543 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3544 $url

3545 $user さんから

3546 グループ $1

3547 に加入依頼がありました。

3548

3549 承認する:

3550 $urlbase?joingrpadmit+yes+$jss

3551

3552 白紙に戻す:

3553 $urlbase?joingrpadmit+no+$jss

3554 EOF

3555 echo "管理者に加入依頼を出しました。

3556 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3557 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3558 }

3559 joingrp() {

3560 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3561 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3562 if isgrpowner "$user" "$1"; then

3563 isowner="yes"

3564 elif [ -n "$5" ]; then

3565 isowner="yes"

3566 else

3567 isowner=""

3568 fi

3569 ## err jg:isgrpowner: isowner="$isowner"

3570 if [ -n "$isowner" ]; then

3571 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3572 elif [ x"$2" != x"$user" ]; then # if user is not login user

3573 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3574 return

3575 elif [ x"$3" = x"no" ]; then

3576 : # Do not pursue those who leave

3577 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3578 : # Member can change own email address for the joining moderated group

3579 else # adding user is $user itself

3580 case `getgroupattr "$1" regmode` in

3581 moderated)

3582 joingrprequest "$@" # Request only

3583 return

3584 ;;

3585 *)

3586 ;;

3587 esac

3588 fi

3589 qgname=`sqlquote "$1"`

3590 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3591 cond="where gname=$qgname and user='$2'"

3592 if [ x"$3" = x"yes" ]; then

3593 query "replace into grp_mem values($qgname, '$2');"

3594 # Notify joingrp to admin

3595 action="に加入しました。"

3596 if [ -n "$4" ]; then

3597 if msg=`emaildomaincheck "$4"`; then

3598 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3599 'string', '$4', NULL);"

3600 else

3601 echo $msg

3602 fi

3603 else

3604 query "delete from grp_mem_s $cond and key='email';"

3605 fi

3606 if [ -n "$5" ]; then # as ADMIN

3607 # Coming here means newly created group

3608 sql="select case\

3609 when (select count(*) from grp_mem where gname=$qgname)=1\

3610 then (select user from grp_mem\

3611 where gname=$qgname and user='$user')\

3612 else '' end; "

3613 err NewGrpChk: $sql

3614 if [ -n "`query \"$sql\"`" ]; then

3615 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3616 query "replace into grp_adm values($qgname, '$user');"

3617 fi

3618 fi

3619 else

3620 query "begin;

3621 delete from grp_mem $cond;

3622 delete from grp_mem_s $cond;

3623 delete from grp_mem_m $cond;

3624 delete from grp_adm $cond;

3625 delete from grp_adm_s $cond;

3626 delete from grp_adm_m $cond;

3627 end;"

3628 action="から脱退しました。"

3629 fi

3630 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3631 $url?grp+$grid

3632 $user (`gecos $user`)さんが

3633 グループ $1

3634 $action

3635 EOF

3636 }

3637 grp_add_team() (

3638 # $1=grp-rowid $2=team $3...=user-rowid(s)

3639 grp=`getgroupbyid $1`

3640 team=$2; shift; shift

3641 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3642 { echo "BEGIN;"

3643 for user; do

3644 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3645 '$grp',\

3646 (SELECT name FROM user WHERE rowid=$user),\

3647 'team', 'string', '$team');"

3648 done

3649 echo "END;"

3650 } | query

3651 )

3652 grp_rm_team() (

3653 # $1=grp-rowid $2=team $3...=user-rowid(s)

3654 grid=$1

3655 qgrp=$(sqlquote "`getgroupbyid $grid`")

3656 team=$2; shift; shift

3657 [ -z "$grid" -o -z "$team" ] && return

3658 { echo "BEGIN;"

3659 for user; do

3660 echo "DELETE FROM grp_mem_m\

3661 WHERE gname=$qgrp \

3662 AND user=(SELECT name FROM user WHERE rowid=$user)\

3663 AND key='team' AND val='$team';"

3664 done

3665 cat<<-EOF

3666 DELETE FROM blog_s

3667 WHERE rowid=(

3668 SELECT rowid

3669 FROM blog_s a

3670 WHERE key='team'

3671 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3672 AND NOT EXISTS (SELECT * FROM grp_mem_m

3673 WHERE key='team' AND val=a.val -- a.val=team

3674 AND gname = (SELECT val FROM blog_s b

3675 WHERE a.id=b.id AND key='owner')

3676 ));

3677 EOF

3678

3679 echo "END;"

3680 } | query

3681 )

3682 grp_reg_adm() {

3683 # $1=grp-rowid $2...=user-rowid

3684 grid=$1

3685 grp=`getgroupbyid "$1"`

3686 if [ -z "$grp" ]; then

3687 echo "無効なグループIDです" | html p; return

3688 fi

3689 if ! isgrpowner "$user" "$grp"; then

3690 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3691 fi

3692 shift

3693 for urid; do

3694 newadm=`query "select name from user where rowid=$urid;"`

3695 if [ -z "$newadm" ]; then

3696 echo "指定ユーザIDがおかしいようです。" | html p; return

3697 fi

3698 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3699 err ismember $newadm $grp

3700 if ismember $newadm "$grp"; then

3701 # OK, go ahead

3702 getgname="(select gname from grp where rowid=$grid)"

3703 query "replace into grp_adm values($getgname, '$newadm');"

3704 # confirm insertion

3705 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3706 if [ -n "`query \"$sql;\"`" ]; then

3707 echo "追加完了: $newadm" | html p

3708 else

3709 echo "追加失敗($1 $urid)" | html p

3710 fi

3711 fi

3712 showgroup $grid

3713 done

3714 }

3715 dt_rowhack() {

3716 # From: <TR>

3717 # ....

3718 # <TD>rowclass=foo</TD>

3719 # </TR>

3720 # To: <TR class="foo">....<TD>foo</TD></TR>

3721 sed -e '

3722 /^<TR>/ {

3723 :loop

3724 s/\n//

3725 N

3726 /<\/TR>/ {

3727 s/\n//

3728 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3729 n

3730 }

3731 $q

3732 b loop

3733 }'

3734 }

3735 dumptable() {

3736 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3737 # textのフィールドだけ全てダンプにしたほうがいいか

3738 # $DT_VIEW sets link

3739 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3740 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3741 VIEW=${DT_VIEW-replyblog}

3742 if [ -n "$VIEW" ]; then

3743 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3744 fi

3745 sqlfile=$tmpd/dump.sql

3746 : > $sqlfile # ensure to be empty

3747 printf '.mode html\n.header 1\n' > $sqlfile

3748 # $DT_CHLD=ChildTable:BindColumn

3749 if [ -n "$DT_CHLD" ]; then

3750 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3751 cat<<-EOF >> $sqlfile

3752 -- presql

3753 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3754 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3755 EOF

3756 # Speed up counting of new articles

3757 cat<<-EOF >> $sqlfile

3758 -- presql2

3759 DROP TABLE IF EXISTS _counts;

3760 CREATE TEMPORARY TABLE _counts AS

3761 SELECT $_i, count($_i) cnt

3762 FROM $_t GROUP BY $_i;

3763 /* Prepare NEW count table */

3764 CREATE TEMPORARY TABLE _target AS

3765 SELECT b.rowid trowid, b.id

3766 FROM "$2" b JOIN "$2_s" s

3767 ON b.id=s.id AND s.key='owner'

3768 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3769

3770 DROP TABLE IF EXISTS _children;

3771 CREATE TEMPORARY TABLE _children AS

3772 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3773 FROM (SELECT t.trowid, t.id $_i, a.id

3774 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3775 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3776

3777 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3778 DROP TABLE IF EXISTS _news;

3779 DROP VIEW IF EXISTS _news;

3780

3781 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3782 -- INSERT INTO _news

3783 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3784 CREATE TEMPORARY VIEW _news AS

3785 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3786 FROM (SELECT DISTINCT id FROM _target)

3787 a LEFT JOIN

3788 (SELECT $_i, count(ctime) newcnt

3789 FROM _children x

3790 WHERE ctime > coalesce((SELECT time from myacclog

3791 WHERE tblrowid=x.trowid),

3792 '1970-01-01')

3793 GROUP BY $_i) b

3794 ON a.id=b.$_i;

3795 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3796 EOF

3797 # REMOVE next line until 2019/5/1

3798 cntall="(select count($_i) from $_t where $_i=a.id)"

3799 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3800 # REMOVE next assignment until 2019/5/1

3801 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3802 and id in (select id from $_t where $_i=a.id) \

3803 and val > coalesce((select time from myacclog where \

3804 tblrowid=a.rowid),\

3805 '1970-01-01'))"

3806 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3807 cnt="$cntnew as '新着', $cntall as '総数',"

3808 dt_class=" td2r td3r dumpblogs"

3809 fi

3810 # Construct join expression

3811 eav="" scols=""

3812 pk=`gettblpkey $2`

3813 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3814 substr=${substr:-%s}

3815 for col in ${3:-`gettbl_s_cols $2`}; do

3816 valvar=val

3817 case $col in

3818 gecos) scols="$scols${scols:+, }${col#}"

3819 continue ;; # built-in column name

3820 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3821 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3822 case "$as" in

3823 *:*=*) cnd=${as#*:}

3824 h=${cnd%%=*} v=${cnd#*=}

3825 h=`sqlquotestr "$h"`

3826 v=`sqlquotestr "$v"`

3827 valvar="CASE val WHEN $h THEN $v END"

3828 as=${as%%:*} ;;

3829 esac

3830 ;;

3831 *) as=${col} ;;

3832 esac

3833 ss=`printf "$substr" "$valvar"`

3834 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3835 scols="$scols${scols:+, }b.$as"

3836 done

3837 #case author when '$user' then a.rowid else '---' end as ID,

3838 if [ -n "$DT_SQL" ]; then

3839 echo "$DT_SQL"

3840 else

3841 cat<<-EOF

3842 SELECT a.rowid as LINK, $cnt $scols

3843 FROM $2 a LEFT JOIN

3844 (SELECT $pk,$eav,

3845 max(CASE key

3846 WHEN 'owner'

3847 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3848 as gecos

3849 FROM ${2}_s c GROUP BY $pk)

3850 b ON a.$pk=b.$pk $4;

3851 EOF

3852 fi >> $sqlfile

3853 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3854 sqlog<<-EOF

3855 *** SQL-file: $sqlfile ***

3856 `cat $sqlfile`

3857 EOF

3858 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3859 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3860 cat $sqlfile >> tmp/sql

3861 # query ".read $sqlfile" > $tmpd/foo

3862 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3863 cp $tmpd/foo tmp/

3864 err "----- `gdate +%FT%T.%3N` ------------555555"

3865 ## $ddd LINE exists at the end of this function

3866 fi

3867 printf '.mode list\n.header 0\n' >> $sqlfile

3868 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3869 <div>

3870 <div class="dumptable">

3871 <table class="b$dt_class">

3872 `query ".read $sqlfile"`

3873 </table>

3874 </div>

3875 </div>

3876 EOF

3877 ### `query ".read $sqlfile"`

3878 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3879

3880 ## REMOVE THIS!

3881 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3882 }

3883

3884 clean-orphaned() {

3885 # This shoud be done by foreign_key rules, but some db lack them

3886 query<<-EOF

3887 -- Find blogs that have no parent

3888 WITH orphanedblog AS (

3889 SELECT blog.id,val FROM blog JOIN blog_s bs

3890 ON blog.id=bs.id AND key='owner'

3891 WHERE val NOT IN (SELECT gname FROM grp)

3892 AND val NOT IN (SELECT name FROM user)

3893 ) -- Remove them

3894 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

3895

3896 -- Find articles that have no parent blog

3897 WITH orphanedarticle AS (

3898 SELECT id FROM article

3899 WHERE blogid NOT IN (SELECT id FROM blog)

3900 ) -- Remove them

3901 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

3902 EOF

3903 }

3904

3905 clearcachedir() (

3906 td=`getcachedir "$1"`

3907 err td=$td: ls- `ls $td`

3908 if [ -w "$td/image.1" ]; then

3909 err Removing td=$td

3910 rm -fr $td/ # Clear icon-image cache!

3911 fi

3912 )

3913

3914 par2table() (

3915 # copy current parameters of par into destination table

3916 # $1=definition-file

3917 # Using $user and $session

3918 # Return value:

3919 # 0: Stored successfully

3920 # 1: Insufficient fillings

3921 # 2: No permission to modify the record

3922 # 3: Invalid rowid

3923 # 4: SUCCESS to delete

3924 # 5: Stop deletion for lack of confirm check

3925 # 6: Password length too short

3926 # 7: Password mismatch

3927 # 8: Old password incorrect

3928 # 9: Duplicated post

3929 rowid=`getpar rowid`

3930 if [ ! -e $1 ]; then

3931 echo "テーブル定義ファイルが見付かりません" | html p

3932 exit 1

3933 fi

3934 tbl=${1%.def}

3935 tbl=${tbl##*/}

3936 if [ -n "$rowid" ]; then # Modify existing entry

3937 if [ x"$tbl" = x"user" ]; then

3938 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3939 elif [ x"$tbl" = x"grp" ]; then

3940 sql="select gname from $tbl where rowid=$rowid;"

3941 ##err p2t:grp:q $sql

3942 isgrpowner "$user" "`query $sql`" && rowowner=$user

3943 elif [ x"$tbl" = x"blog" ]; then

3944 # Check if owner in blog_s

3945 blogowner=`getvalbyid blog owner "$rowid"`

3946 if isgrpowner "$user" "$blogowner"; then

3947 rowowner=$user

3948 else

3949 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3950 fi

3951 else

3952 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3953 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3954 fi

3955 ### err rowowner=$rowowner

3956 if [ x"$user" != x"$rowowner" ]; then

3957 echo "他人のレコードはいじれないの" | html p

3958 return 2

3959 elif [ -z "$rowowner" ]; then

3960 echo "指定したレコードはないみたい" | html p

3961 return 3

3962 fi

3963 rm=`getpar rm` cfm=`getpar confirm`

3964 # Editing existent entry

3965 if [ x"$rm" = x"yes" ]; then

3966 if [ x"$rm$cfm" = x"yesyes" ]; then

3967 query "delete from $tbl where rowid=$rowid;"

3968 clearcachedir "$tbl/$rowid"

3969 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

3970 clean-orphaned

3971 fi

3972 return 4

3973 else

3974 echo "消去確認のチェックがないので消さなかったの..." | html p

3975 return 5

3976 fi

3977 fi

3978 fi

3979

3980 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3981 if [ -n "$rowid" ]; then

3982 # Update of existing record

3983 for col in `gettblcols $tbl`; do

3984 val=`getparquote $col`

3985 [ -z "$val" ] && continue

3986 ## err query "update $tbl set $col=$val where rowid=$rowid"

3987 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3988 sql="update $tbl set $col=$val where rowid=$rowid;"

3989 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3990 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3991 ## User name cannot be changed with interface provided with this

3992 ## script. But we offer the trigger to change owner user

3993 ## of blog_s table.

3994 #err "select quote($col) from $tbl where rowid=$rowid;"

3995 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3996 cat<<-EOF | query

3997 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3998 -- should use EXCLUSIVE transaction outside of this.

3999 SAVEPOINT par2table;

4000 $sql

4001 update blog_s set val=$val

4002 where key='owner' and val=$old;

4003 RELEASE SAVEPOINT par2table;

4004 EOF

4005 ## XX: DIRTY Hack Ends here

4006 ## We should keep blog's owner as a single column which has

4007 ## foreign key constraint with primary key of grp/user.

4008 else

4009 query "$sql"

4010 fi

4011 done

4012 # Then, set up $pval for further insertion of tbl_s and tbl_m

4013 for col in `gettblpkey $tbl`; do

4014 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

4015 pval="$pval${pval:+, }\"$val\""

4016 done

4017 else

4018 # New entry

4019 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

4020 # Avoid empty repost of article.

4021 if [ x"$tbl" = x"article" ]; then

4022 # If rowid is empty and ID exists in article-table, that is REPOST!

4023 aid=`getpar id`

4024 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

4025 if [ -n "$xaid" ]; then

4026 # REPOST of article

4027 html p <<-EOF

4028 書き込み直後のリロードなので上書きを回避します。

4029 最新記事は末尾の「再読み込み」ボタンから見てください。

4030 EOF

4031 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

4032 return 9 # STOP Duplicated posting

4033 fi

4034 fi

4035 # Generate values() for primary keys

4036 for col in `gettblpkey $tbl`; do

4037 # Genuine primary keys for _m and _s

4038 val=`getvalquote $tbl $col`

4039 [ -z "$val" ] && continue

4040 pval="$pval${pval:+, }$val"

4041 done

4042 ##err pval=$pval

4043 for col in `gettblfkey $tbl`; do

4044 # args for values() to insertion into master table

4045 val=`getvalquote $tbl $col`

4046 [ -z "$val" ] && continue

4047 formaster=$formaster"${formaster:+, }$val"

4048 done

4049 formaster="$pval${formaster:+, }$formaster"

4050 ## err formaster=$formaster

4051 if [ -z "$formaster" ]; then

4052 echo "項目を全て埋めてください" | html pre

4053 return 1

4054 fi

4055 ## err "replace into $tbl values($formaster);"

4056 query "replace into $tbl values($formaster);"

4057 ## Insertion to master table, done

4058 fi

4059

4060 transaction=$tmpd/sqlfile.sql; touch $transaction

4061 for kt in s m; do

4062 tb2=${tbl}_$kt

4063 for col in `gettbl_${kt}_cols $tbl`; do

4064 ptype=`getpartype $col "limit 1"`

4065

4066 # First, check update of existing entries in _m

4067 if [ $kt = m ]; then

4068 # sessID|address.1.22|string|Somewhere-x.y.z

4069 sql=""

4070 ##err dots from query "select var from par where var like '$col.%';"

4071 for v in `query "select var from par where var like '$col.%' AND sessid='$session';"`; do

4072 # v=address.1.22

4073 st_rowid=${v##*.}

4074 origcol=${v%%.*} # original column derived from

4075 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

4076 ##case `getpartype $v` in

4077 ## err CASE `gettbl_coltype $tbl/$origcol` in

4078 ## err edit flag = `getpar action.$v`

4079 case `getpar action.$v` in

4080 rm)

4081 if [ x`getpar confirm.$v` = x"yes" ]; then

4082 newsql="delete from $tb2"

4083 else

4084 echo "削除確認未チェック" | html p

4085 fi ;;

4086 edit)

4087 case `gettbl_coltype $tbl/$origcol` in

4088 image|document|binary)

4089 file=$tmpd/`getparfilename $v`

4090 if [ ! -s "$file" ]; then # Maybe stupid REPOST

4091 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

4092 continue

4093 fi

4094 ## err type=file=$file

4095 [ -z "$file" ] && continue

4096 bn=`sqlquotestr "${file##*/}"`

4097 bin="X'"$(hexize "$file")"'"

4098 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

4099 type=\"file:$ct\"

4100 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

4101 cachedir=`getcachedir "$tbl/$rowid"`

4102 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

4103 rm -rf $cachedir

4104 ;;

4105 *)

4106 newsql="update $tb2 set val=(select val from par where var \

4107 like '$col.%.$st_rowid' AND sessid='$session')"

4108 ;;

4109 esac

4110 ;;

4111 mv)

4112 # regularize filename and strip directory part

4113 newname=`getpar mv.$v|tr -d '":;#<>?^%$!'|tr -d "'"|tr ' ' _`

4114 newname=`basename $newname`

4115 oldname=`query "SELECT val FROM $tb2 WHERE rowid=$st_rowid;"`

4116 oldext=`expr "$oldname" : '.*\.$.*$'`

4117 newext=`expr "$newname" : '.*\.$.*$'`

4118 err "p2t(mv): oldname=$oldname $oldext -> newname($v)=$newname $newext"

4119 if [ -n "$newname" -a x"$oldext" = x"$newext" ];

4120 then

4121 newsql="UPDATE $tb2 SET val='$newname'"

4122 else

4123 html p<<-EOF

4124 $newname は取り扱えないファイル名です。

4125 空白を含まない名前にして下さい。

4126 拡張子の変更もできません。

4127 EOF

4128 continue

4129 fi

4130 ;;

4131 *) # maybe "keep", do not modify value

4132 continue

4133 ;;

4134 esac

4135 # err newsql=$newsql

4136 sql=$sql$nl"$newsql where rowid=$st_rowid;"

4137 clearcachedir "$tbl/$rowid"

4138 done

4139

4140 if [ x"$bin" = x"NULL" ]; then

4141 ## err repl:normal sql=`echo $sql`

4142 if [ -n "$transaction" ]; then

4143 cat<<-EOF >> $transaction

4144 $sql

4145 DELETE FROM $tb2 WHERE type='string' AND val='';

4146 EOF

4147 else

4148 query "$sql

4149 delete from $tb2 where type='string' and val='';"

4150 ## err repl:normal done

4151 fi

4152 else

4153 # Binary update line is TOO LONG to pipelining

4154 sqlfile="$tmpd/sqlf.$$"

4155 if [ -n "$transaction" ]; then

4156 echo "$sql" >> $transaction

4157 else

4158 echo "$sql" > $sqlfile

4159 query ".read $sqlfile"

4160 fi

4161 fi

4162 # Rest of kt==m: set multiple mode

4163 nr=`getparcount $col`

4164 else

4165 nr=1 # for kt==s, number of records is 1

4166 fi

4167

4168 i=0

4169 while [ $i -lt $nr ]; do

4170 limit="limit 1 offset $i"

4171 i=$((i+1)) # increase beforehand against continue

4172 val=`getvalquote $tbl $col "$limit"`

4173 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4174 ## err $col=$val

4175 bin=NULL

4176 ## err partype$col=`getpartype $col "$limit"`

4177 ptype=`getpartype $col "$limit"` # partype should be obtained each time

4178 case $ptype in

4179 file) file=$tmpd/`getparfilename $col "$limit"`

4180 ## err parfile-$col=$file

4181 [ -z "$file" ] && continue

4182 bin="X'"$(hexize "$file")"'"

4183 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4184 type=\"file:$ct\" ;;

4185 "*"*) continue ;; # foreign table

4186 *) type=\"string\" ;;

4187 esac

4188 case `gettbl_coltype $tbl/$col` in

4189 password) # special care for password

4190 # name={password,pswd1,pswd2}

4191 p1=`getpar pswd1 "$limit"`

4192 if [ -z "$p1" ]; then

4193 continue # SKIP password setting, if p1 is empty

4194 else

4195 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4196 ## err pswd=$pswd

4197 if pwcheck "$pswd"; then

4198 if [ x"$p1" = x"$p2" ]; then

4199 case "$p1" in

4200 ??????????*) ;;

4201 *) echo "パスワードは10字以上にしてください。" | html p

4202 return 6;;

4203 esac

4204 val="\"`echo $p1|mypwhash`\""

4205 else

4206 echo "2つの新パスワード不一致" | html p

4207 return 7

4208 fi

4209 else

4210 echo "旧パスワード違います" | html p

4211 return 8

4212 fi

4213 fi

4214 ;;

4215 esac

4216 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4217 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4218 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4219 if [ x"$bin" = x"NULL" ]; then

4220 ## err Normal-query: `echo $sql`

4221 if [ -n "$transaction" ]; then

4222 echo "$sql" >> $transaction

4223 else

4224 query "$sql"

4225 fi

4226 else

4227 sqlfile="$tmpd/query.$$"

4228 ## err sqlfile=`ls -lF $sqlfile`

4229 if [ -n "$transaction" ]; then

4230 echo "$sql" >> $transaction

4231 else

4232 echo "$sql" > $sqlfile

4233 query ".read $sqlfile"

4234 fi

4235 fi

4236 ## err p2t done

4237 done

4238 done

4239 done

4240 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4241 -- We cannot use transaction here, because groupupdate may use it.

4242 SAVEPOINT pa2table_insert;

4243 .read $transaction

4244 RELEASE SAVEPOINT pa2table_insert;

4245 EOF

4246 rc=$?

4247 [ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag

4248 ## err "Table:$tbl update done "

4249 return $rc

4250 )

4251 genform() {

4252 # $1 = form definition file

4253 # $2, $3 (optional)= table name and ROWID

4254 # If $GF_VIEWONLY set and nonNull, output values without form

4255 # If $GF_ARGS set, use it as content-strings in the form

4256 # If $GF_OWNER set, use it as value of name="owner"

4257 # If $GF_STAGE set, use it as value of name="stage"

4258 forms="" hiddens="" rowid=$3

4259 if [ ! -e "$1" ]; then

4260 echo "そのようなデータベースはないようです($2)。" | html p

4261 return

4262 elif [ -n "$2" ]; then

4263 rec=`query "select * from $2 where rowid='$rowid';"`

4264 if [ -z "$rec" ]; then

4265 pk=`gettblpkey $2`

4266 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4267 rec=`query "select rowid from $2 where $pk='$rowid';"`

4268 rowid=$rec

4269 rec=$3

4270 fi

4271 if [ -z "$rec" ]; then

4272 echo "そんなレコードはないみたいね..." | html p

4273 return

4274 fi

4275 fi

4276 if [ -z "$GF_VIEWONLY" ]; then

4277 rm='<input id="rm" name="rm" type="checkbox"

4278 value="yes"><label for="rm">このエントリの削除</label>

4279 <span>ほんとうに消しますよ(確認)!

4280 <input name="confirm" type=checkbox value="yes">はい</span>'

4281 fi

4282 # Image Cache dir

4283 ## err genform: getcache=$2/$rowid

4284 td=`getcachedir "$2/$rowid"`

4285 while IFS=: read prompt name keytype type args; do

4286 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4287 sp="${args:+ }"

4288 form="" val=""

4289 if [ -n "$rowid" ]; then

4290 # err genform2a: Seeking for "$2.$name, type=$type"

4291 rawval=`getvalbyid $2 $name $rowid $td`

4292 val=`echo "$rawval"|htmlescape`

4293 ## err genform3a: getvalbyid $2 $name $rowid $td

4294 ## err genform3b: val="[$val]" type="$type"

4295 fi

4296 if [ -n "$GF_VIEWONLY" ]; then

4297 is_hidden "$2" "$name" && continue

4298 fi

4299 case "$type" in

4300 text*)

4301 cgiform=cgi_multi_$type

4302 if [ -s $td/$name.count -a -n "$val" ]; then

4303 form=`$cgiform $name $td`

4304 val=$(echo "$val"|

4305 while read fn; do

4306 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4307 </td></tr>$nl"

4308 done)

4309 val="<table>$nl$val$nl</table>"

4310 else

4311 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4312 form=`cgi_$type $name "$rawval" "$args"`

4313 fi

4314 ;;

4315 [Rr][Aa][Dd][Ii][Oo])

4316 fh="<label><input type=\"radio\" name=\"$name\""

4317 form="`echo $args|sed -e \

4318 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4319 ;;

4320 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4321 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4322 ;;

4323 [Ss][Ee][Ll][Ee][Cc][Tt])

4324 fh="<select name=\"$name\">$nl"

4325 form=$(for l in $args; do

4326 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4327 done)

4328 if [ -n "$val" ]; then

4329 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4330 fi

4331 form="$fh$form</select>"

4332 ;;

4333 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4334 if [ -s $td/$name.count ]; then

4335 form=`cgi_multi_file $name $td "$args"`

4336 if [ -n "$val" ]; then

4337 hrfb="$myname?showattc+$2_m"

4338 val=$(echo "$rawval" \

4339 | while read fn; do

4340 data=`percenthex "$td/$fn"`

4341 #ct=`cat $td/$fn.content-type`

4342 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4343 ri=`cat "$td/$fn.rowid"`

4344 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4345 #imgsrc="<img src=\"data:$ct,$data\">"

4346 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4347 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4348 done)

4349 fi

4350 else

4351 form="<input type=\"file\" name=\"$name\" $args>"

4352 if [ -n "$val" ]; then

4353 imgs=$(echo "$rawval"\

4354 |while read fn;do

4355 data=`percenthex "$td/$fn"`

4356 echo "<img src=\"data:image/png,$data\">$fn<br>"

4357 done)

4358 form=$form"<br>$imgs"

4359 val=$imgs # 2015-06-15

4360 else

4361 form="<input type=\"file\" name=\"$name\" $args>"

4362 fi

4363 fi

4364 ;;

4365 [Hh][Ii][Dd][Dd][Ee][Nn])

4366 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4367 args="value=\"$GF_STAGE\""

4368 fi

4369 form="<input type=\"hidden\" name=\"$name\" $args>"

4370 prompt='' # Remove prompt

4371 ;;

4372 [Aa][Uu][Tt][Hh][Oo][Rr])

4373 [ -n "$GF_VIEWONLY" ] && continue

4374 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4375 prompt="" ;;

4376 [Oo][Ww][Nn][Ee][Rr])

4377 [ -n "$GF_VIEWONLY" ] && continue

4378 val=${GF_OWNER:-$val}

4379 val=${val:-$user}

4380 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4381 prompt="" ;;

4382 [Uu][Ss][Ee][Rr])

4383 # XXX: is null $user ok?

4384 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4385 [ -n "$GF_VIEWONLY" ] && continue

4386 form="$user"

4387 ;;

4388 [Pp]assword)

4389 [ -n "$GF_VIEWONLY" ] && continue

4390 form="`cgi_passwd`"

4391 val=""

4392 ;;

4393 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4394 [ -n "$GF_VIEWONLY" ] && continue

4395 if [ -z "$rowid" ]; then

4396 val=`genserial`

4397 fi

4398 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4399 prompt="" ;;

4400 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4401 prompt=""

4402 ;;

4403 parent|path|blog*)

4404 prompt=""

4405 ;;

4406 "*"*)

4407 tail=$tail"``"

4408 continue ;;

4409 esac

4410 if [ -n "$prompt" ]; then

4411 if [ -n "${GF_VIEWONLY}" ]; then

4412 form=$val

4413 else

4414 :

4415 fi

4416 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4417 else

4418 hiddens=$hiddens$nl"$form"

4419 fi

4420 done < $1

4421 # enctype="multipart/form-data"

4422 cat<<EOF

4423 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4424 ${rowid:+$rm}

4425 <table class="b $2">

4426 $forms

4427 </table>$hiddens

4428 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4429 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4430 EOF

4431 if [ -z $GF_VIEWONLY ]; then

4432 cat<<EOF

4433 <input type="submit" name="sub" value="OK">

4434 <input type="reset" name="res" value="Reset">

4435 EOF

4436 fi

4437 cat<<EOF

4438 $GF_ARGS</form>

4439 $tail

4440 EOF

4441 }

4442 edittable() {

4443 # $1=form-def $2=table $3 rowid

4444 genform "$@"

4445 }

4446 viewtable() {

4447 GF_VIEWONLY=1 genform "$@"

4448 }

4449 showattc() {

4450 # $1=table_m $2=rowid &optional $3=RawFlag

4451 ## err \$1=$1 \$2=$2 \$3=$3

4452 if ! isfilereadable $user $1 $2; then

4453 contenttype; echo

4454 echo "このファイルは管理者のみしか見られません" | html p

4455 putfooter; exit

4456 fi

4457 idir=`umask 002; mktempd` || exit 1

4458 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4459 bin=$idir/$myname-$$.bin

4460 sql="select quote(bin) from $1 where rowid='$2';"

4461 ## err showattc: sql: $sql

4462 sq $db "$sql" | unhexize > $bin

4463 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4464 type=${tv%//*} fn=${tv#*//}

4465 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4466 ct=${type#file:}

4467 case $ct in # all text/* changed to text/plain

4468 text/*)

4469 charset=`nkf -g $bin|cut -d' ' -f1`

4470 case $charset in

4471 ASCII*) charset="" ;;

4472 esac

4473 if [ -z "$3" ]; then

4474 ct="text/html${charset:+; charset=$charset}"

4475 link="?showattc+$1+$2+raw"

4476 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4477 | sed 's,^,<span></span>,' \

4478 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4479 -D_LINK_="$link" \

4480 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4481 exit $?

4482 fi

4483 ct="text/plain${charset:+; charset=$charset}"

4484 ;;

4485 esac

4486 contenttype "$ct"

4487 echo "Content-Disposition: filename=\"$fn\""

4488 echo "Content-Length: " `cat $bin | wc -c`; echo

4489 #echo "Content-Type: " ${type#file:}; echo

4490 cat $bin

4491 }

4492 #

4493 # Some default stupid handler on CGI values

4494 #

4495 default_storedb() {

4496 # ARG: $1=table-def-file

4497 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4498 tbl=`basename $1`

4499 tbl=${tbl%.def}

4500 cols="`grep :text $1|cut -d: -f2`"

4501 col=`echo "$cols"|head -1`

4502 vcol=`getpar $col`

4503 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4504 if [ -n "$vcol" ]; then

4505 par2table $1

4506 else

4507 return 2 # No insertion occurred

4508 fi

4509 }

4510

4511 default_view() { # $1=def-file

4512 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4513 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4514 default_storedb "$@"

4515 query "select rowid from $tbl order by rowid desc;" \

4516 | while read rowid; do

4517 viewtable $1 $tbl $rowid

4518 done | _m4 -D_TITLE_="$tbl" \

4519 -D_FORM_="`genform $1`" \

4520 -D_DUMPTABLE_="syscmd(cat)" \

4521 $layout/html.m4.html $layout/form+dump.m4.html

4522 }

4523 default_viewtext() { # $1=def-file

4524 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4525 default_storedb "$@"

4526 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4527 | _m4 -D_TITLE_="$tbl" \

4528 -D_FORM_="`genform $1`" \

4529 -D_DUMPTABLE_="syscmd(cat)" \

4530 $layout/html.m4.html $layout/form+dump.m4.html

4531 }

4532 default_smail() {

4533 default_storedb "$@"

4534 if [ $? -eq 2 ]; then

4535 _m4 -D_TITLE_="入力" \

4536 -D_FORM_="`genform $1`" \

4537 -D_DUMPTABLE_="" \

4538 $layout/html.m4.html $layout/form+dump.m4.html

4539 return

4540 fi

4541 cond=""

4542 for pk in `gettblpkey $tbl`; do

4543 pv=$(sqlquote "$(getpar $pk)")

4544 cond="$cond${cond:+ and }$pk=$pv"

4545 done

4546 sql="select rowid from $tbl where $cond;"

4547 rowid=`query "$sql"`

4548 ## err smail1 - "$sql" "-> rowid=$rowid"

4549

4550 while IFS=: read prompt name keytype type args; do # Read from $1

4551 val=`getpar $name`

4552 if [ -n "$val" ]; then

4553 text="$text

4554 $prompt

4555 $name=$val

4556 ---------------------------------------------------------"

4557 fi

4558 case "$type" in

4559 image|document|file)

4560 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4561 fns=$(echo "$fn"|while read fn; do

4562 err mv $tmpd/$fn.orig $tmpd/$fn

4563 mv $tmpd/$fn.orig $tmpd/$fn

4564 rm $tmpd/$fn.rowid # Remove cache flag

4565 ## err "`ls $tmpd/$fn`"

4566 echo $fn

4567 done)

4568 files="$files $fns"

4569 ;;

4570 esac

4571 done < $1

4572 ## err FILES=$files "`ls -lF $tmpd`"

4573 subj="from ${REMOTE_ADDR}"

4574 (echo "$url"

4575 echo "への書き込みがありました。"

4576 echo "------"

4577 echo "$text"

4578 ) | (cd $tmpd &&

4579 err LS="`ls -lF`" &&

4580 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4581 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4582 echo "以下の内容で送信しました。" | html p

4583 viewtable $1 $tbl \

4584 `query "select rowid from $tbl order by rowid desc limit 1;"`

4585 echo "戻る" | html a "href=\"?\""

4586 }