s4: 920818b41cd2 s4-funcs.sh

s4

view s4-funcs.sh @ 896:920818b41cd2

Application/{csv,json} forcibly handled as text/plain

author	HIROSE Yuuji <yuuji@gentei.org>
date	Thu, 31 Dec 2020 10:18:08 +0900
parents	c01b13db3bd3
children	3d437261edca

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 flag_profupdate() {

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

1416 # This is useful to mitigate account sync load

1417 query <<-EOF

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

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

1420 EOF

1421 touch $userupdateflag

1422 }

1423 wasureta() {

1424 user=$1

1425 if ! checkdomain $user; then

1426 contenttype; echo

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

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

1429 putfooter

1430 exit 0

1431 fi

1432 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1434 encpswd=`echo $newpswd|mypwhash`

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

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

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

1438 $msgdir/mail-newaccount.m4 \

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

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

1441 }

1442 checkauth() {

1443 user=`getpar user`

1444 skc=`getpar skey` # from cookie

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

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

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

1448 if chkskey "$skc"; then

1449 return 0

1450 fi

1451 fi

1452 pswd=`getpar pswd`

1453 quser=`sqlquotestr "$user"`

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

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

1456 return 4 # 4=server too heavy

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

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

1459 return 2 # 2=login fail

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

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

1462 contenttype; echo

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

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

1465 cat <<-EOF | html p

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

1467 EOF

1468 exit 0 # XXXXX more smart...

1469 fi

1470 wasureta "$user"

1471 err wasureta issued for "$user"

1472 return 1 # wasureta error

1473 fi

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

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

1476 if pwcheck "$pswd"; then

1477 newsession=`genrandom 34`

1478 if setskey "$user" "$newsession" &&

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

1480 gencookie "user=$user" "skey=$newsession"

1481 return 0

1482 else

1483 return 4 # Heavy load??

1484 fi

1485 fi

1486 err "Login failed: [$user]"

1487 return 2 # Password mismatch

1488 }

1489 showlogin() {

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

1491 test -z "$args" && resetskey

1492 s4name=${S4NAME:-s4}

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

1494 cat $layout/login.m4.html

1495 echo '</body></html>'

1496 ) | _m4 \

1497 -D_BODYCLASS_="login" \

1498 -D_TITLE_="$s4name" \

1499 -D_SYSNAME_="Welcome to $s4name" \

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

1501 exit 0

1502 }

1503 dologin() {

1504 test -n "$S4WORLD" && syncaccount

1505 checkauth

1506 st=$?

1507 if [ $st != 0 ]; then

1508 contenttype; echo

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

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

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

1512 showlogin # and EXIT

1513 fi

1514 err "Auth OK: [$user]"

1515 }

1516

1517 # Do instant jobs here

1518 dbsetup

1519 trap cleanup INT HUP EXIT TERM PIPE

1520 # trap cleanup INT HUP

1521

1522

1523 cgiinit() {

1524 tmpd=`tmpd=$tmpdir mktempd`

1525 tmpf=$tmpd/stream.$$

1526 tmpfiles=$tmpfiles" $tmpd"

1527 addsession $session

1528 getcookie

1529 case "$REQUEST_METHOD" in

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

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

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

1533 # cat > $tmpf # too much?

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

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

1536 s="`cat $tmpf`"

1537 ;;

1538 esac

1539 case "$CONTENT_TYPE" in

1540 *boundary*)

1541 bndry=${CONTENT_TYPE#*boundary=}

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

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

1544 do

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

1546 #echo u=$us

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

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

1549 # err k=$k v=$v

1550 case "$k" in

1551 *:filename)

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

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

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

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

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

1557 :

1558 fi

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

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

1561 case "$mimetype" in

1562 [Ii]mage/x-xcf)

1563 bzip2 "$tmpd/$v"

1564 v=${v}.bz2

1565 ;;

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

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

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

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

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

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

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

1573 v=$vjpg

1574 else

1575 mimetype="Not supported"

1576 fi

1577 ;;

1578 [Ii]mage/*)

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

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

1581 ;;

1582 esac

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

1584 then

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

1586 continue

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

1588 replpar text string \

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

1590 continue

1591 fi

1592 ;;

1593 image|document|binary)

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

1595 continue ;;

1596 *)

1597 type='string'

1598 ;;

1599 esac

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

1601 setpar "$k" "$type" "$v"

1602 done

1603 ;;

1604 *)

1605 setviastring "$s"

1606 ;;

1607 esac

1608 }

1609 email4group() {

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

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

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

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

1614 sql="WITH

1615 grpemails AS (

1616 SELECT gname, user, val email

1617 FROM grp_mem NATURAL JOIN grp_mem_s

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

1619 useremails AS (

1620 SELECT user.name, val email

1621 FROM user

1622 LEFT JOIN user_m

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

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

1625 FROM useremails u LEFT JOIN grpemails g

1626 ON u.name=g.user

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

1628 query "$sql"

1629 }

1630 email4groupbyuid() {

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

1632 g=$1; shift

1633 uids=`echo "$@"`

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

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

1636 email4group "$g" `query "$sql"`

1637 }

1638 myemail4group() {

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

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

1641 }

1642 collectmembersbyid() {

1643 # Collect user names of group specified by grid

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

1645 query "SELECT user FROM grp_mem \

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

1647 }

1648 collectmembersbyid() {

1649 # Collect user names of group name

1650 qgrp=`sqlquote "$1"`

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

1652 }

1653 collectgecosesbyid() {

1654 # Collect user gecoses of group

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

1656 query<<-EOF

1657 SELECT gecos

1658 FROM gecoses

1659 WHERE name IN (SELECT user FROM grp_mem

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

1661 EOF

1662 }

1663 collectemail() (

1664 # Collect email addresses for group $1

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

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

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

1668 for e; do

1669 if isuser "$e"; then

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

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

1672 else

1673 qgrp=`sqlquote "$e"`

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

1675 gmem="grp_mem"

1676 else

1677 tm=`sqlquote "$TEAM"`

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

1679 fi

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

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

1682 $gmem g left join grp_mem_s s

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

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

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

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

1687 query "$sql"

1688 fi

1689 done

1690 )

1691 sendinvitation() (

1692 # $1=email

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

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

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

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

1697 gecos=`gecos`

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

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

1700 _m4 -D_URL_="$urlbase" \

1701 -D_USER_="$name" \

1702 -D_EMAIL_="$1" \

1703 -D_REGIST_="$regist" \

1704 -D_ADMIN_="$admin" \

1705 $msgdir/mail-invite.m4 \

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

1707 return 0

1708 )

1709 emaildomaincheck() {

1710 case "$1" in

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

1712 *@*)

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

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

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

1716 return 2

1717 fi

1718 return 0

1719 ;;

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

1721 esac

1722 }

1723 invite() {

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

1725 case "$email" in

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

1727 *@*)

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

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

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

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

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

1733 elif sendinvitation $email; then

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

1735 else # Cannot be reached here

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

1737 fi ;;

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

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

1740 esac

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

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

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

1744 fi

1745 if [ -f $invite_policy ]; then

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

1747 else

1748 pol="$invite_policy"

1749 fi

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

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

1752 -D_INVITE_POLICY_="$pol" \

1753 $layout/html.m4.html $layout/invite.m4.html

1754 }

1755 regist() {

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

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

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

1759 echo "bye bye" | html p

1760 reutrn

1761 fi

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

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

1764 cat<<EOF

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

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

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

1768 EOF

1769 return

1770 fi

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

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

1773 # Fake login password to wasureta

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

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

1776 wasureta $email

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

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

1779 addsession $1 # for removal after 1 minute

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

1781 $layout/login.m4.html

1782 return

1783 }

1784 group_safename() {

1785 # Convert $1 to safe group name

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

1787 }

1788 groupupdate() {

1789 gname=`getpar gname`

1790 qgname=`sqlquote "$gname"`

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

1792 # See ALSO same job in showgroup()

1793 newgname=`group_safename "$gname"`

1794 err newgname=$newgname

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

1796 err NewGNAME: gname=$newgname

1797 gname=$newgname

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

1799 replpar gname string "$gname"

1800 fi

1801 # Name confliction check

1802 parow=`getpar rowid`

1803 ## err parow=$parow

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

1805 query "BEGIN EXCLUSIVE;"

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

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

1808 if [ $count -gt 0 ]; then

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

1810 query "END;"

1811 return

1812 fi

1813 par2table $formdir/grp.def

1814 query "END TRANSACTION;"

1815 # Remove orphan

1816 : <<EOF

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

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

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

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

1821 EOF

1822 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1826 sql="delete from blog where id in

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

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

1829 query "$sql";

1830 unsetpar tag kwd

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

1832 return # and return

1833 fi

1834 fi

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

1836 fi

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

1838 grid=$(query $sql)

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

1840 grp $grid

1841 }

1842 groupclone() {

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

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

1845 case "$1" in

1846 */noteam)

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

1848 esac

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

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

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

1852 return

1853 fi

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

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

1856 return

1857 fi

1858 i=0

1859 while true; do

1860 copy="-copy$i"

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

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

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

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

1865 i=$((i++))

1866 continue

1867 fi

1868 break

1869 done

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

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

1872 query<<-EOF

1873 BEGIN;

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

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

1876 SELECT $newqname, key, val

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

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

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

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

1881 -- Copy members and their configuration --

1882 REPLACE INTO grp_mem SELECT $newqname, user

1883 FROM grp_mem WHERE gname=$qgrp;

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

1885 FROM grp_mem_s WHERE gname=$qgrp;

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

1887 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1888 -- Copy administrators --

1889 REPLACE INTO grp_adm SELECT $newqname, user

1890 FROM grp_adm WHERE gname=$qgrp;

1891 COMMIT;

1892 EOF

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

1894 STOPCLONEMSG=1 groupconf "$newrowid"

1895 }

1896 groupman() {

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

1898

1899 GF_STAGE="grpconf"

1900 GF_STAGE=groupupdate

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

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

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

1904 -D_DUMPTABLE_="syscmd(cat)" \

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

1906 }

1907 userconf() {

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

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

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

1911 }

1912 groupconf() {

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

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

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

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

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

1918

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

1920 grp=`getgroupbyid $rowid`

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

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

1923 return

1924 fi

1925

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

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

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

1929 ## Setup migration menu

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

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

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

1933 err v=$v

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

1935 migrate=$(cat<<-EOF

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

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

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

1939 <form action="?migrategrp">

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

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

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

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

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

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

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

1947 `cgi_hidden stage migrategrp`

1948 `cgi_hidden rowid $rowid`

1949 `cgi_submit OK`

1950 `cgi_reset Reset`

1951 </form>

1952 </div>

1953 EOF

1954 )

1955 fi

1956 fi

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

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

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

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

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

1962 <table>

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

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

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

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

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

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

1969 </table>

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

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

1972 </div>

1973 $migrate

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

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

1976 EOF

1977 fi

1978 }

1979 migrategrp() {

1980 rowid=`getpar rowid`

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

1982 grp=`getgroupbyid $rowid`

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

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

1985 return

1986 fi

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

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

1989 grp "$rowid"

1990 return

1991 fi

1992 destconf=`getpar migrateto`

1993 err destconf=$destconf

1994 if [ ! -e $destconf ]; then

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

1996 grp "$rowid"

1997 return

1998 fi

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

2000 srcconf=$worldconf

2001 else

2002 srcconf=s4-config.sh

2003 fi

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

2005 echo "移住操作" | html h1

2006 echo '<pre>'

2007 set -- "$srcconf" "$destconf" "$rowid"

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

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

2010 rc=$?

2011 echo "</pre>"

2012 if [ $rc -eq 0 ]; then

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

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

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

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

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

2018 else

2019 echo "移住失敗" | html p

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

2021 fi

2022 return

2023 }

2024 mems() {

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

2026 kwd=`getpar kwd`

2027 listmember $kwd

2028 }

2029 grps() {

2030 case "$S4WORLD" in

2031 $nonewgroupworld) ;;

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

2033 esac

2034 _m4 -D_TITLE_="グループ一覧" -D_BODYCLASS_=listgroup $layout/html.m4.html

2035 kwd=`getpar kwd`

2036 listgroup "$kwd" \

2037 | _m4 -D_DUMPTABLE_="syscmd(cat)" \

2038 -D_TITLE_="グループ関連操作" \

2039 -D_FORM_="${LINK_NEWGRP}${NEWGRP_GUIDE}" \

2040 $layout/form+dump.m4.html

2041 }

2042 grp() { # $1=group-rowid

2043 gpg=`getpar grp`

2044 grid=${1:-$gpg}

2045 grp=`getgroupbyid "$grid"`

2046 ## . ./s4-blog.sh

2047 jg=`getpar joingrp`

2048 if [ -n "$jg" ]; then

2049 [ -n "$jg" -a -n "$grp" ] &&

2050 joingrp "$grp" "$user" "$jg" "`getpar email`"

2051 fi

2052 htmlheader=$layout/html.m4.html

2053 showgroup "$grid"

2054 }

2055 sql4interestblogs() {

2056 cat<<EOF

2057 CREATE TEMPORARY VIEW interestblogs AS

2058 SELECT blog.rowid rid, id, author

2059 FROM blog

2060 NATURAL JOIN

2061 (SELECT id, val owner FROM blog_s WHERE key='owner') bs

2062 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2063 THEN 1 -- blog owner is an user, READABLE

2064 WHEN (SELECT user FROM grp_mem

2065 WHERE gname=bs.owner AND user='$user') IS NULL

2066 THEN 0

2067 ELSE 1

2068 END;

2069 EOF

2070 }

2071 listnewblogsql() { # $1=user

2072 newdays=${WHATS_NEW_DAYS##*[!0-9]} # Shave non-digits

2073 newdays=${newdays%%[!0-9]*}

2074 newdays=${newdays:-14}

2075 basetime="datetime('now', 'localtime', '-${newdays} days')"

2076 deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog

2077 WHERE user='$user'

2078 AND tblrowid IN

2079 ($blogreadflagrowid,

2080 $blogcutoffflagrowid)),

2081 $basetime -- "0"

2082 );"`

2083 cat<<EOF

2084 `sql4interestblogs`

2085 WITH article_ctime as (

2086 SELECT id,blogid,author,max(val) ctime

2087 FROM article join article_s s using(id)

2088 WHERE s.key='ctime' AND s.val > '$deftime'

2089 GROUP BY id

2090 ), blog_title_owner as (

2091 SELECT blg.rid brid, id,

2092 max(case key when 'title' then val end) title,

2093 max(case key when 'owner' then val end) owner

2094 FROM interestblogs blg, blog_s using(id) group by id

2095 ), blogall as (

2096 SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid

2097 ), news as (

2098 SELECT brid, bl.id blid, bl.title, ctime,

2099 coalesce(al.time, '$deftime') atime,

2100 count(bl.id) "新着", bl.author

2101 FROM blogall bl

2102 LEFT JOIN

2103 (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al

2104 ON bl.brid=al.tblrowid

2105 WHERE atime < bl.ctime

2106 GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id

2107 LIMIT 10

2108 ) SELECT brid LINK, "新着",

2109 (SELECT count(*) FROM article WHERE blogid=blid) "総数",

2110 ctime, title,

2111 (SELECT gecos FROM gecoses WHERE name=author) gecos

2112 FROM news;

2113 EOF

2114 }

2115

2116 search_form() {

2117 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

2118 # $2(optional) = pre-input keywords

2119 help="(1)空白区切りの単語で本文検索

2120 (2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索

2121 @2016-0[1-6] → 2016年1月から6月

2122 @>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間

2123 @week → 最近一週間

2124 (3)#番号で記事ID検索

2125 (1)と(2)は組み合わせOK

2126 例: @2016-10-0[1-9] 芋煮

2127 → 2016年10月上旬でキーワード「芋煮」を含む記事検索

2128 ※クイズ板は検索対象から外されます。"

2129 auth=""

2130 placeholder="全記事からの検索"

2131 case "$1" in

2132 author=*)

2133 a=`echo "${1#author=}"|htmlescape`

2134 g=`gecos ${1#author=}`

2135 auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"

2136 placeholder="このユーザの書込検索"

2137 help="★★ $g さんの書き込みから検索します$nl$help"

2138 ;;

2139 grid=*)

2140 a=`echo "${1#grid=}"`; a=$((0 + $a))

2141 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

2142 placeholder="このグループからの検索"

2143 ;;

2144 esac

2145 inikwd="$2" # no need to htmlescape

2146 cat<<-EOF

2147 <div class="right">

2148 <form action="$myname">$auth

2149 <input type="text" name="kwd" value="$inikwd" title="$help"

2150 placeholder=" $placeholder " width="10" accesskey="k">

2151

2152 ${touchpanel:+<p class="help">$help</p>}

2153 <input type="hidden" name="stage" value="searchart">

2154

2155 </form>

2156 </div>

2157 EOF

2158 }

2159

2160 imgsrc_cache() (

2161 # $1 = directory for cache'ing

2162 # $2 = table (user_m or grp_m)

2163 # $3 = keycond (was: condition for choosingowner)

2164 # $4 = size : S = Small, M = Medium, O = Original

2165 dir="$1" tbl="$2"

2166 keycond="$3"

2167 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

2168 ORDER BY rowid DESC LIMIT 1"

2169 [ -d "$dir" ] || mkdir -p "$dir"

2170 tmpf=$tmpd/imgsrc_cache.$$

2171 case "$4" in

2172 [Ss]) size=S ;;

2173 [Oo]) size=O ;;

2174 *) size=M ;;

2175 esac

2176 # ImageCache filename storing schema:

2177 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

2178 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

2179 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

2180 valtype=`query "$sql0"`

2181 filename=${valtype%%//*}

2182 filetype=${valtype##*//file:}

2183 if [ x"$filename" = x"${filename%.*}" ]; then

2184 # If nor filename extension found, set it to image type

2185 case "$filetype" in

2186 image/*) filename=$filename.${filetype#image/} ;;

2187 esac

2188 fi

2189 cacheimg_S=$dir/S_$filename

2190 cacheimg_M=$dir/M_$filename

2191 cacheimg_O=$dir/$filename

2192 cacheimg=$dir/${size}_$filename

2193 sumfile="$dir/$filename.sum"

2194 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

2195 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2196 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2197 # if cache is fresh and has the same checksum,

2198 echo "<img src=\"$cacheimg\">"

2199 else

2200 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2201 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2202 fifo2=$fifo.2

2203 mkfifo $fifo $fifo2

2204 fmt=${filename##*.}

2205 ## [[ NOTE ]]

2206 ## a. convert oldimage newimage

2207 ## b. convert oldimage fmt:- | convert - newimage

2208 ## b is much smaller than a

2209 cat $tmpf | unhexize \

2210 | tee $fifo \

2211 | convert -define ${fmt}:size=${iconxy_M} \

2212 -resize ${iconxy_M}'>' - ${fmt}:- \

2213 | tee $fifo2 \

2214 | convert - "$cacheimg_M" &

2215 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2216 -resize ${iconxy_S}'>' - ${fmt}:- \

2217 | convert - "$cacheimg_S" &

2218 printf '%s' "<img src=\"data:${filetype},"

2219 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2220 echo '">'

2221 echo "$sum" > $sumfile

2222 fi

2223 ## Now preparing cache image, done.

2224 ## Store this information to DB

2225 stbl=${tbl%_m}_s # user_s or grp_s

2226 pkey=${keycond%%=*} # Primary Key name

2227 pval=${keycond#*=} # Primary Key value

2228 query <<-EOF

2229 REPLACE INTO $stbl($pkey, key, type, val)

2230 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2231 EOF

2232 )

2233

2234 showhome() {

2235 # $1=userRowIdToShow

2236 err showhome \$1=$1

2237 case "$1" in

2238 *@*) uname=`getvalbypkey user name "$1"` ;;

2239 *) uname=`getvalbyid user name $1` ;;

2240 esac

2241 ## err ShowHome: uname=$uname

2242 td=`getcachedir home/"$1"`

2243 gecos=`gecos "$uname"`

2244 ## err SH:gecos=$gecos

2245 GF_VIEWONLY=1

2246 cond="gname in (select gname from grp_mem where user='$uname')"

2247 search_form_args=""

2248 if [ x"$user" = x"$uname" ]; then

2249 if [ -z "$S4MASTERDB" ]; then

2250 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2251 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2252 elif [ -n "$S4MASTERURL" ]; then

2253 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2254 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2255 fi

2256 usermenu="$usermenu

2257 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2258 # Display folders

2259 sql="select count(id) from article_m where id

2260 in (select id from article where author='$user')

2261 and type like 'file:%';"

2262 ## err nfile-sql=`echo "$sql"`

2263 nfile=`query "$sql"`

2264 # err nfile=$nfile

2265 if [ $nfile -gt 0 ]; then

2266 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2267 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2268 fi

2269 else

2270 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2271 GROUP BY user;"`

2272 usermenu="<p>Last seen on $latestlog</p>"

2273 search_form_args="author=$uname"

2274 fi

2275 . ./s4-blog.sh

2276

2277 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2278 search_form "$search_form_args" > $sf

2279 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2280 | htmlescape > $tf

2281 { echo "<div class=\"noprofimg\">"

2282 viewtable $formdir/user.def user $1

2283 echo "</div>"

2284 } > $pf

2285

2286 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2287 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2288 imf=$tmpd/profimg.$$; touch $imf

2289 if [ -n "$img" ]; then

2290 if true; then

2291 tbl=user_m

2292 enticond="name='$uname'"

2293 imgsrc_cache "$td/main" user_m "$enticond" M

2294 else

2295 { printf '%s' "<IMG src=\"data:${img#file:},"

2296 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2297 | sed 's/$..$/%\1/g'

2298 echo '">'

2299 }

2300 fi > $imf

2301 fi

2302 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2303 val='$uname';"`

2304 listblog $uname > $bf

2305

2306 hometail=$tmpd/tail.$$

2307 mkfifo $hometail

2308

2309 #Calling listgroupbytable, originally here

2310

2311 (

2312 # Display Most Recent Entry

2313 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2314 shortval=${shortval:-val}

2315

2316 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2317 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2318 ctime,

2319 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2320 (SELECT gecos FROM gecoses

2321 WHERE name=(SELECT val FROM blog_s

2322 WHERE key='owner' AND id=b.id)) owner,

2323 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2324 FROM blog b

2325 JOIN

2326 (SELECT distinct blogid, a.id aid, max(val) ctime

2327 FROM article a, article_s s

2328 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2329 GROUP BY blogid ORDER BY val DESC LIMIT 50

2330 ) m

2331 ON b.id=m.blogid;"

2332 # This should be as follows

2333 : <<EOF

2334 WITH arts AS(

2335 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2336 a.blogid, a.id id, s.val ctime

2337 FROM article a NATURAL JOIN article_s s

2338 WHERE s.key = 'ctime' AND a.author='$user'

2339 GROUP by s.id

2340 )

2341 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2342 FROM arts a0

2343 JOIN

2344 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2345 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2346 ORDER BY ctime DESC LIMIT 50;

2347 EOF

2348

2349 cat<<-EOF

2350 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2351 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2352 <div class="lcto">

2353 `DT_VIEW=replyblog dumptable html blog`

2354 </div>

2355 EOF

2356 unset DT_SQL

2357 if [ x"$user" = x"$uname" ]; then

2358 # Display NEWS

2359 # 2016-06-26

2360 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2361 acclog blog $blogreadflagrowid

2362 # echo "全部既読にしました" | html p

2363 fi

2364 # 2016-02-19 Counting NEWS without using dumptable.

2365 sql=`listnewblogsql "$user"`

2366 # echo "$sql" > tmp/listnew

2367 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2368 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2369 cont=$((cont-1))

2370 ##err newcount=$cont

2371 if [ $cont -gt 0 ]; then

2372 #echo "全体の新着記事${cont}傑" | html h2

2373 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2374 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2375 cat<<-EOF | html form 'action="?home"'

2376 `cgi_checkbox readchk yes 'id="read"'`<label

2377 for="read">新着ふくめて全部読んだことにする</label>

2378 `cgi_submit '確定'`

2379 `cgi_hidden read yes`

2380 EOF

2381 echo "$new10 "

2382 echo "</div>"

2383 else # If news is 0, set log cut off flag

2384 acclog blog $blogcutoffflagrowid # for speed

2385 fi

2386 else # Not My Home ($user != $uname)

2387 : # DT_SQL=

2388 fi

2389 ) > $hometail & # Is background call safe to m4??

2390 #

2391 listgroupbytable $formdir/grp.def "$cond" $uname |

2392 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2393 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2394 -D_PROFIMG_="spaste(\`$imf')" \

2395 -D_BLOGS_="spaste(\`$bf')" \

2396 -D_SEARCH_="spaste(\`$sf')" \

2397 -D_NBLOG_="$nblog" \

2398 -D_GROUPS_="syscmd(\`cat')" \

2399 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2400 $layout/html.m4.html $layout/home.m4.html

2401

2402 # Record access log

2403 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2404 }

2405 commission() { # $1=grp-rowid $2=user-rowid

2406 contenttype; echo

2407 ## err commission: "$@"

2408 gname=`getgroupbyid $1`

2409 echo "グループ $gname 管理者委任" \

2410 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2411 if [ -n "$2" ]; then

2412 grp_reg_adm "$@"

2413 else

2414 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2415 fi

2416 }

2417 listgroupbytable() {

2418 # $1=deffile $2...=condition $3(optional)=uname

2419 tagline=`grep :tag: $1`;

2420 and="${2:+and }" where=${2:+where }

2421 href="<a href=\"$myname?grp+"

2422 echo '<div class="listgroup">'

2423 sql="select val from grp_s where key='tag' $and$2 group by val;"

2424 ## err ListGRP: query sql="$sql"

2425 for tag in `query "$sql"`

2426 do

2427 ## err ListGrp: tag=$tag

2428 tn=${tagline%%=${tag}*}

2429 tn=${tn##*[ :]}

2430 sql="select rowid||':'||gname as 'グループ名',説明 from

2431 (select (select rowid from grp g where g.gname=grp_s.gname)

2432 as rowid,

2433 gname,

2434 max(case key when 'gecos' then val end) as '説明',

2435 max(case key when 'tag' then val end) as 'tag',

2436 max(case key when 'mtime' then val end) as mtime from grp_s

2437 $where$2 group by gname having tag='$tag' order by mtime desc);"

2438 ## err PersonalGroupList= `echo $sql`

2439 echo "<h2>$tn</h2>"

2440 echo '<table class="b listgroup">'

2441 sq -header -html $db "$sql" \

2442 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2443 echo '</table>'

2444 done

2445 if [ -n "$S4WORLDLIST" -a -n "$3" ]; then

2446 err "peekgrpworlds($user) BEGIN: `gdate +%S.%03N`"

2447 peekgrpworlds mem:"$3"

2448 err "peekgrpworlds($user) END: `gdate +%S.%03N`"

2449 fi

2450 echo '</div>'

2451 }

2452 iconhref() (

2453 # $1=icon-file, $2=Href $3=title $4...=anchor

2454 data=`percenthex "$1"`

2455 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2456 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2457 href=$2; title=$3; shift 3

2458 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2459 )

2460 iconhref2() (

2461 # $1=icon-file, $2=Href $3=title $4...=anchor

2462 src=$1

2463 href=$2; title=$3; shift 3

2464 anchor=`echo $@|htmlescape`

2465 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2466 )

2467 listentry() (

2468 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2469 # Referring variable $iamowner=$grp to attach owner-request links

2470 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2471 cond='' hiddens=''

2472 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2473 if [ -z "$offset" ]; then

2474 offset=`getpar start`; offset=${offset%%[!0-9]*}

2475 offset=$((offset-1))

2476 fi

2477 offset=$((offset + 0)) # change to numeric forcibly

2478 [ $offset -lt 0 ] && offset=0

2479 limit=$listentlimit

2480 dir=`getcachedir "$1"`

2481 if [ x"$1" = x"user" ]; then

2482 hrb="$myname?home"

2483 deficon=person-default.png

2484 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2485 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2486 gcs=gecos

2487 else # if group

2488 hrb="$myname?grp"

2489 deficon=group-default.png

2490 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2491 gcs=name

2492 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2493 if [ -n "$tagline" ]; then

2494 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2495 ## err tagconv=$tagconv

2496 fi

2497 fi

2498 if [ ! -d $dir ]; then

2499 mkdir -p $dir

2500 fi

2501 if [ ! -s $dir/$deficon ]; then

2502 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2503 fi

2504 kwd=${2:-`getpar kwd`}

2505 if [ -n "$kwd" ]; then

2506 kwd=`echo $kwd | tr -d '";\n' | tr -d "'"`

2507 case "$kwd" in

2508 mem:*@*)

2509 byuser=${kwd#*mem:}

2510 qusr=`sqlquote "$byuser"`

2511 cond1="(a.gname IN (SELECT gname FROM grp_mem WHERE user=$qusr))"

2512 ;;

2513 esac

2514 if [ x"$1" = x"group" ]; then

2515 if [ -n "$cond1" ]; then

2516 enthead="`gecos "$byuser"|htmlescape` さんの所属"

2517 else

2518 cond1="(b.name like '%${kwd}%')"

2519 fi

2520 else

2521 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2522 fi

2523 fi

2524 tag=`getpar tag` tag2=`getpar tag2`

2525 if [ x"$tag" = x"NULL" ]; then

2526 tag="" tag2=""

2527 fi

2528 if [ -n "$tag$tag2" ]; then

2529 tag=${tag:-$tag2}

2530 qtag=`sqlquote "$tag"`

2531 cond2="tag=$qtag"

2532 fi

2533 if [ -n "$cond1$cond2" ]; then

2534 cond="$cond1${cond2:+ AND $cond2}"

2535 cond="WHERE ${cond# AND }"

2536 fi

2537

2538 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2539 # grpは呼出し元の動的スコープ変数でよくないな...

2540 ##qgrp=`sqlquote $grp`

2541 getgrp="(select gname from grp where rowid=${rowid:--1})"

2542 sql="select a.rowid, a.$link,

2543 coalesce(b.$gcs, a.$nm) as nick,

2544 quote(a.$nm) as qname,

2545 (SELECT val FROM ${tbl}_s

2546 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2547 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2548 || case when a.$nm in (select gname from grp)

2549 then printf('(%d名)',

2550 (select count(user) from grp_mem where gname=a.$nm))

2551 else ' <'||a.$nm||'>'

2552 end

2553 as name,

2554 b.tag,

2555 case when a.$nm in (select user from grp_adm

2556 where gname=$getgrp) then '管理者'

2557 when '$user' in (select user from grp_adm where gname=a.$nm)

2558 then 'ADMIN'

2559 when '$user' in (select user from grp_mem where gname=a.$nm)

2560 then 'Member'

2561 when '$iamowner' = '' then ''

2562 else ',not='||a.rowid end as ownerlink,

2563 CASE '$entity'

2564 WHEN 'グループ'

2565 THEN coalesce(

2566 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2567 'open')

2568 ||

2569 CASE WHEN '$user'

2570 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2571 THEN ' ismember'

2572 ELSE ''

2573 END

2574 ELSE 'user'

2575 END regmode

2576 from $tbl a left join

2577 (select $nm as name,

2578 max(case key when 'gecos' then val end) as gecos,

2579 max(case key when 'tag' then val end) as tag,

2580 max(case key when 'mtime' then val end) as mtime,

2581 max(case key when 'wtime' then val end) as wtime,

2582 max(case key when 'login' then val end) as login

2583 from ${tbl}_s group by $nm)

2584 b on a.$nm=b.name $cond $3

2585 order by b.wtime desc, b.login desc,

2586 b.mtime desc, b.tag desc, a.rowid asc"

2587 # Give precedence to newer maintained groups (2016-09-24)

2588 # Note that mtime is stored only in grp_s.

2589 ## err LE:sql.1="$sql"

2590 total=`query "with x as ($sql) select count(*) from x;"`

2591 echo "$enthead${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2592 echo '<div class="listentry">' # List-entry div

2593 # Show owner/member filter button

2594 METHOD=GET

2595 hiddens="$hiddens

2596 `cgi_hidden stage \"$stage\"`"

2597 if [ x"$tbl" = x"grp" ]; then

2598 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2599 fh="<select name=\"tag\">$nl"

2600 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2601 for l in $args; do

2602 val=${l#*=} tname=${l%=*}

2603 if [ x"$val" = x"$tag" ]; then

2604 s=" selected"

2605 selectedtags="(種別[${tname}]のみ)"

2606 else

2607 s=""

2608 fi

2609 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2610 done

2611 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2612 cat<<-EOF

2613 <form action="$myname" method="$METHOD">

2614 </form>

2615 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2616 for="ismembtn">参加中以外隠す</label>

2617 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2618 for="isadmbtn">管理者参加以外隠す</label>

2619 EOF

2620 # limit=3

2621 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2622 fi

2623 if [ $total -gt $limit -o $ -n "$S4WORLDLIST" -a x"$tbl" = x"grp" $ ]; then

2624 echo '<div>'

2625 METHOD=GET cgi_form $stage <<EOF

2626 $form

2627 <label>次の語を含む${entity}で検索:

2628 `cgi_text kwd "$kwd"`</label>

2629 $hiddens

2630 EOF

2631 echo '</div>'

2632 else

2633 echo $selectedtags | html p

2634 fi

2635 test -n "$kwd" && hiddens="$hiddens$nl`cgi_hidden kwd \"$kwd\"`"

2636 cat<<EOF

2637 <form action="$myname" method="$METHOD">

2638 <p>${total}件中の<input class="hidesub" type="text" name="start"

2639 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2640 <input type="submit" value="確定"></p>

2641 </form>

2642 EOF

2643 if [ $((offset+limit)) -lt $total ]; then

2644 nextbtn=$(

2645 cat<<EOF

2646 <div class="right clear"><form action="$myname" method="$METHOD">

2647 `cgi_submit 次の${limit}件`

2648 $hiddens

2649 `cgi_hidden offset $((offset + limit))`</form></div>

2650 EOF

2651 )

2652 fi

2653 if [ $offset -gt 0 ]; then

2654 prevbtn=$(

2655 cat<<EOF

2656 <form action="$myname" method="$METHOD">

2657 `cgi_submit 前の${limit}件`

2658 $hiddens

2659 `cgi_hidden offset $((offset - limit))`</form>

2660 EOF

2661 )

2662 fi

2663 pnbtn="$nextbtn$prevbtn"

2664 echo $pnbtn

2665

2666 ## err ListEntry: `echo "$sql"\;`

2667 # sq $db here??? 2016-11-28

2668 query "$sql limit $limit ${offset:+offset $offset};" \

2669 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2670 # err name=$name owner=$ownerp lnk=$lnk

2671 # err newlnk=$lnk regmode=$regmode

2672 icondir=$dir/$id

2673 # Pick up only last icon

2674 htmlname=`echo $name|htmlescape`

2675 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2676 <p class=\"tag _$tag\">$tag</p>" \

2677 | _m4 $tagconv

2678 if [ -n "$NOSPEEDUP" ]; then

2679 files=`getvalbyid $tbl profimg $id $icondir`

2680 if [ -n "$files" ]; then

2681 icon=`echo "$files"|tail -1`

2682 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2683 else

2684 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2685 fi

2686 elif [ -n "$icon" -a -s "$icon" ]; then

2687 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2688 else

2689 cond="$nm=$qname"

2690 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2691 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2692 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2693 # err "img=[$img]"

2694 if [ -n "$img" ]; then

2695 echo "<a href=\"$hrb+$lnk\">"

2696 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2697 echo "</a>"

2698 else

2699 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2700 fi

2701 fi

2702 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2703 echo "</div>"

2704 done

2705 echo "</div>" # End of List-entry div

2706 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2707 if [ -n "$kwd" -a x"$tbl" = x"grp" -a -n "$S4WORLDS" ]; then

2708 peekgrpworlds "$kwd"

2709 fi

2710 )

2711 listmember() {

2712 listentry user "$@"

2713 }

2714 listgroup() {

2715 listentry group "$@"

2716 }

2717 hexteams() { # $1=gname, $2(optional)=user

2718 cond=${2:+" AND user='$2'"}

2719 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2720 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2721 }

2722 showgroup() { # $1=group-rowid

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

2724 grid=`getpar grid`

2725 grid=${grid%%[!0-9]*}

2726 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2727 else

2728 grid=$1

2729 fi

2730 grp=`getgroupbyid $grid`

2731 qgrp=`sqlquote "$grp"`

2732 htmlgrp=`echo "$grp"|htmlescape`

2733 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2734 if isgroup "$grp"; then

2735 tf=$tmpd/title.$$

2736 sf=$tmpd/search.$$

2737 bodyclass=`query "SELECT val FROM grp_s

2738 WHERE gname=$qgrp AND key='regmode';"`

2739 if ismember "$user" "$grp"; then

2740 ismember="ismember"

2741 bodyclass="$bodyclass${bodyclass:+ }ismember"

2742 else

2743 ismember="" # bodyclass="group"

2744 fi

2745 bodyclass="$bodyclass grouphome"

2746 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2747 echo "グループ $htmlgrp" > $tf

2748

2749 showgroupsub $formdir/grp.def "$grid" | \

2750 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2751 -D_FORM_="syscmd(\`cat')" \

2752 -D_BODYCLASS_="$bodyclass" \

2753 -D_DUMPTABLE_="" \

2754 $htmlheader $sf $layout/form+dump.m4.html

2755 # $htmlheader $layout/form+dump.m4.html

2756 # $htmlheader is defined in grp()

2757 else # if $grp is removed at par2table

2758 listgroup

2759 fi

2760 }

2761 showgroupsub() {

2762 # $1=def-file $2=group-rowid

2763 # Using $ismember

2764 rowid=$2

2765 grp=`getgroupbyid $2`

2766 qgrp=`sqlquote "$grp"`

2767 td=`getcachedir grp/"$2"`

2768 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2769 if [ -z "$rowid" ]; then

2770 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2771 #grp=`sq $db "select gname from grp where rowid=$grp"`

2772 echo "showgroupsub: invalid argument($1 $2)" | html p

2773 return

2774 fi

2775 val=`getvalbyid grp profimg $rowid $tmpd`

2776 enticond="gname=$qgrp"

2777 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2778 if [ -n "$img" ]; then

2779 cat<<-EOF

2780 <p class="groupimg">

2781 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2782 EOF

2783 fi

2784 echo "<div class=\"noprofimg\">"

2785 viewtable $1 grp $rowid

2786 echo "</div>"

2787 if isgrpowner "$user" "$grp"; then

2788 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2789 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2790 iamowner=$rowid

2791 colmd=" mode"

2792 fi

2793 if [ -n "$ismember" ]; then

2794 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2795 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2796 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2797 cat<<-EOF

2798 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2799 href="?blog+$rowid">グループの新規話題作成</a>

2800 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2801 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2802 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2803 <div class="fold clear">

2804 `cgi_checkbox send yes id="send"`<label

2805 for="send">グループ全員にメッセージ送信</label>

2806 <div>

2807 `cgi_textarea message "" "cols=60"`

2808 `cgi_submit 送信`

2809 `cgi_reset リセット`

2810 </div>

2811 `cgi_hidden grp $rowid`

2812 </div></form>

2813 EOF

2814 fi

2815 # 加入ボタン + 加入者リスト

2816 if [ -n "$ismember" ]; then

2817 ismem='checked' state="(参加中)"

2818 else

2819 nomem='checked' state="(現在非加入)"

2820 fi

2821 # このグループでの加入アドレス

2822 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2823 and key='email';"`

2824 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2825 ## and key='email';"

2826 ##err email=$eml

2827 cat <<EOF

2828 <div class="fold clear">

2829 `cgi_checkbox reg yes id="reg"`<label

2830 for="reg">自身の加入状態を操作する</label>$state

2831 <div>

2832 EOF

2833 cgi_form grp <<EOF

2834 <p>このグループに</p>

2835 <table class="b">

2836 <tr><th>メンバーとして</th><td>

2837 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2838 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2839 <tr><th>参加する場合のメイルアドレス<br>

2840 <small>(メインのアドレスとは違うものにする場合に記入<br>

2841 同じでよい場合は空欄に)</small></th>

2842 <td>`cgi_text email $eml`</td></tr>

2843 </table>

2844 `cgi_hidden grp $rowid`

2845 EOF

2846 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2847 echo "moderated (承認加入の)グループなので実際に参加できるのは

2848 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2849 fi

2850 echo '</div></div>'

2851 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2852 cat<<-EOF

2853 <div class="fold">

2854 <h2>話題一覧</h2>

2855 <form class="summary inline" action="$myname" title="$thelp">

2856 `cgi_hidden owner "$grp"`

2857 `cgi_hidden kwd "@week"`

2858 `cgi_hidden stage searchart`

2859 `cgi_submit "一週間のまとめ"`

2860 </form>

2861 <input type="checkbox" id="dtcheck" checked><label

2862 for="dtcheck">話題一覧表示</label>

2863 EOF

2864 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by 稼動状態, ctime desc"

2865 colstate="state:稼動状態:frozen=rowclass=凍結"

2866 frzbtn='<button class="toggle-frozen"></button>'

2867 DT_CHLD=article:blogid \

2868 DT_QOWNER="$qgrp" \

2869 DT_VIEW=replyblog dumptable html blog \

2870 "ctime title heading team notify:通知$colmd $colstate" "$cond" \

2871 | if [ -n "$iamowner" ]

2872 then

2873 sed -Ee "s,(<TD class=\"稼動状態\">).*(</TD>),\1$frzbtn\2,"

2874 else

2875 cat

2876 fi

2877 ## DO not convert to frzbtn when not admin

2878 echo "</div> "

2879

2880 getgname="(select gname from grp where rowid=$rowid)"

2881 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2882 cm="?commission+$rowid"

2883 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2884 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2885 # team list

2886 hexteams=`hexteams "$grp"`

2887 if [ -n "$hexteams" ]; then

2888 echo "チーム一覧" | html h2

2889 echo '<div class="dumptable"><table class="b">'

2890 sq $db -html -header<<-EOF

2891 SELECT val TEAM,

2892 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2893 MEMBERS

2894 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2895 EOF

2896 echo '</table></div>'

2897 fi

2898 }

2899 grp_getbodyclass() {

2900 # Get css class name for document.

2901 # `moderated' for moderated groups

2902 # `ismember' for groups where user belongs

2903 # $1=GroupName (w/o quote)

2904 # $user=userNameCurrentlyLogin

2905 ## err grp_getbodyclass: 1="$1"

2906 qgrp=`sqlquote "$1"`

2907 query<<-EOF

2908 SELECT coalesce(

2909 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2910 'open')

2911 ||

2912 CASE WHEN '$user'

2913 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2914 THEN ' ismember'

2915 ELSE ''

2916 END;

2917 EOF

2918 }

2919 grpaction() { # $1=group-rowid

2920 err GRP_ACTION:IN

2921 grid=${1:-`getpar grp`}

2922 grp=`getgroupbyid "$grid"`

2923 htmlgrp=`echo "$grp" | htmlescape`

2924 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2925 if [ -z "$grp" ]; then

2926 echo "無効な指定です。" | html p; return

2927 fi

2928 if ! ismember $user "$grp"; then

2929 echo "加入者のみに許可された操作です。" | html p; return

2930 fi

2931 isowner=""

2932 isgrpowner "$user" "$grp" && isowner="yes"

2933 err 2=$2 3=$3

2934 case "$2" in

2935 get:teamcsv)

2936 teamcsv "$grid"

2937 return

2938 ;;

2939 esac

2940 echo "グループ $grp 個別選択操作" \

2941 | _m4 -D_TITLE_="syscmd(\`cat')" \

2942 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2943 $layout/html.m4.html

2944

2945 usel=`getpar usel`

2946 if [ -n "$usel" ]; then

2947 uids=$(echo `echo $usel`|tr ' ' ',')

2948 ## err grpaction-1: grp=$grp, `echo $sql`

2949 text=`getpar text|tr -d '\r'`

2950

2951 rm=`getpar rm` cfm=`getpar confirm`

2952 ## err rm=$rm cfm=$cfm

2953 if [ x"$rm" = x"yes" ]; then

2954 if [ "$isowner" ]; then

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

2956 # Eliminate

2957 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2958 for tbl in grp_mem grp_mem_s grp_mem_m; do

2959 sql="delete from $tbl $cond;"

2960 # echo "sql=$sql"

2961 query "$sql"

2962 err rmGRPuser "$sql"

2963 done

2964 num=`query "select count(*) from user where rowid in ($uids);"`

2965 #err num=$num

2966 if [ 0$num -gt 0 ]; then

2967 sql="select coalesce(b.val,a.name) from user a left join \

2968 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2969 # err `echo "$sql"`

2970 html pre<<EOF

2971 以下の${num}名のグループ $grp 登録を解除しました。

2972 `query "$sql"`

2973 EOF

2974 fi

2975 else

2976 echo "確認のチェックがないのでやめておきます。" | html p

2977 return

2978 fi

2979 else # not Group Owner

2980 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2981 return

2982 fi

2983 cat<<EOF

2984

2985 EOF

2986 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2987 if [ -z "$text" ]; then # if msg is empty

2988 echo "なにかメッセージを..." | html p

2989 return 0

2990 fi

2991 gecos=`gecos $user`

2992 safegc=`echo "$gecos" | tr -d '<>@'`

2993 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2994 fromad=`myemail4group "$grp"`

2995 ###mail_from="$safegc <$fromad>"

2996 mail_from="$safegc <$user>" # TEST: 2020/5/13

2997 test -n `getpar sender` &&

2998 export SENDER=$user # TEST: 2020/5/15

2999 replyto=$fromad

3000

3001 ## Start parse of attachment files

3002 if [ -n "`getpar email`" ]; then

3003 ar=`getpar supprcpt`

3004 if [ -n "$ar" ]; then

3005 for a in $ar; do

3006 if checkdomain "$a"; then

3007 supprcpt="$supprcpt $a"

3008 else

3009 err "SupprcptErr=[$a] by $user"

3010 removercpt="$removercpt $a"

3011 fi

3012 done

3013 fi

3014 subj=`getpar subject`

3015 afiles=""

3016 for fn in `query "SELECT DISTINCT val FROM par WHERE var='image' AND sessid='$session';"`

3017 do

3018 f=$tmpd/$fn

3019 if [ -s $f ]; then

3020 afiles=$afiles"${afiles:+ }$f"

3021 fi

3022 done

3023 else

3024 preface=$(cat <<-EOF

3025 $url

3026 のグループ「$grp」のメンバーである $gecos さんから、

3027 あなた宛のメッセージです。

3028 ----------------------------------------------------------

3029 EOF

3030 )

3031 fi

3032 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

3033 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

3034 subj="${subj:-$gecos さんからのメッセージ}"

3035 REPLYTO=$replyto

3036 MAIL_FROM=$mail_from

3037 export REPLYTO SMAIL_TO MAIL_FROM

3038 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

3039 for r in $rcpts; do

3040 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

3041 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

3042 else

3043 # Show sender and rcpts address for guest

3044 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

3045 fi

3046 if [ -n "$afiles" ];then

3047 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

3048 else

3049 smail "$r" "$subj"

3050 fi <<EOF

3051 ${preface:+$preface$nl}$text

3052 EOF

3053 done

3054 if [ $? = 0 ]; then

3055 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

3056 sql="select coalesce(b.val, a.name) from

3057 (select name from user where rowid in ($uids)) a

3058 left join user_s b on a.name=b.name and b.key='gecos';"

3059 html pre<<EOF

3060 `query "$sql"`

3061 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

3062 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

3063 EOF

3064 err SendDone: `echo $sql`

3065 fi

3066 elif [ x"$rm" = x"commission" ]; then

3067 grp_reg_adm $grid $usel

3068 elif [ x"$rm" = x"addteam" ]; then

3069 team=`getpar team|sed "s/'/''/g"` # for single quotation

3070 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

3071 if [ x"$team" != x"$newteam" ]; then

3072 echo "チーム名に使えない文字を除去しました" | html p

3073 team=$newteam

3074 fi

3075 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

3076 cat<<-EOF | html p

3077 有効なチーム名を入力してください。

3078 カンマだけ、「なし」という名前は使えません。

3079 EOF

3080 echo "有効なチーム名を入力してください。" | html p

3081 else

3082 grp_add_team $grid "$team" $usel

3083 fi

3084 elif [ x"$rm" = x"rmteam" ]; then

3085 if [ x"yes" = x"`getpar teamconfirm`" ]; then

3086 rmteam=`getpar rmteam|sed "s/'/''/g"`

3087 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

3088 gname='$grp' AND user='$user' AND key='team'\

3089 AND val='$rmteam';\"`" ]; then

3090 grp_rm_team $grid "$rmteam" $usel

3091 else

3092 echo "所属していないチームの除去操作はできません。"|html p

3093 fi

3094 else

3095 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

3096 fi

3097 fi

3098 fi

3099 # POST count summary

3100 from=`getpar from`; to=`getpar to`

3101 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3102 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3103 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

3104 # New entry

3105 sql="WITH mems AS (

3106 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3107 ON gm.user=g.name

3108 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3109 ), target_article AS (

3110 SELECT id FROM article_s

3111 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3112 ), posts AS (

3113 SELECT author, count(author) post

3114 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3115 WHERE blogid IN (SELECT id FROM blog_s

3116 WHERE key='owner'

3117 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3118 AND key='text'

3119 GROUP BY author

3120 ), teams AS (

3121 SELECT user, group_concat(val, ', ') team

3122 FROM mems m LEFT JOIN grp_mem_m gm ON m.name=gm.user

3123 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3124 AND key='team'

3125 GROUP BY user

3126 ), user_post AS (

3127 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3128 FROM mems m LEFT JOIN posts

3129 ON m.name=posts.author

3130 GROUP by m.rowid

3131 )

3132 SELECT

3133 CASE

3134 WHEN (SELECT user FROM grp_adm

3135 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3136 AND user=up.name) IS NOT NULL

3137 then 'k'

3138 ELSE ''

3139 END || rowid || ','

3140 || rtrim(substr(name, 1, instr(name, '@')), '@') || ','

3141 || gecos NAME,

3142 post POST, team _TEAM_

3143 FROM user_post up LEFT JOIN teams t

3144 ON up.name=t.user

3145 ORDER BY up.name;"

3146 ## err grpaction: "`echo \"$sql\"`"

3147 tf=$tmpd/title.$$

3148 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3149 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3150 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3151 for=\"cmadmin\">管理者委任</label>

3152 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3153 </p></div>"

3154 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3155 title=\"Shortcut: g${nl}Remove from the Group\"

3156 for=\"conf\">GRP登録解除</label>

3157 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3158 <p>この操作による通知は本人に行きません。

3159 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3160 </div>"

3161 # Get team list to which current user belongs into $hexteams

3162 allhexteams=$(hexteams "$grp")

3163 if [ -n "$isowner" ]; then

3164 myhexteams="$allhexteams" # admin can remove all teams' attr

3165 else

3166 myhexteams=$(hexteams "$grp" "$user")

3167 fi

3168 if [ -n "$isowner" -a -n "$allhexteams" ]; then

3169 gettingcsv="<p>Download: <a href=\"?getteamcsv+$grid\">Team.csv</a> (Zoom Breakout Room 事前割り当てに使えます)</p>"

3170 fi

3171 if [ -n "$myhexteams" ]; then

3172 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3173 title=\"Shortcut: s${nl}Strip a team tag from\"

3174 for=\"cmrmteam\">チーム属性除去</label>

3175 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3176 を除去します: `cgi_checkbox teamconfirm yes` 確認

3177 <p>この操作による通知は本人に行きません。

3178 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3179 </div>

3180 "

3181 fi

3182 stf=$tmpd/selteam.$$

3183 cgi_select_h selteam "5445414d" $allhexteams > $stf

3184 b1='<label> <input type="checkbox" name="usel" value="'

3185 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3186 br='<span id="reverse" title="Reverse Selection"></span>'

3187 #b2='"> <span>' b3='</span></label>'

3188 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

3189 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

3190 lnk='"> <span>\5</span></label> [<a href="?home+\3">HOME</a>]'

3191 #-e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

3192 cgi_form grpaction<<EOF \

3193 | sed -E \

3194 -e "s|^(<TR><TD)>(k?)([0-9]*),([^,]+),([^<]*)|\1 title=\"\4\">\2$b1\3$lnk|" \

3195 -e 's/^(<TR><TD[^>]*>)k(<label)/\1\2 class="admin"/' \

3196 -e "s|^(<TR><TH>)(NAME)|\1$br \2|" \

3197 | _m4 -D_TITLE_="spaste(\`$tf')" \

3198 -D_SUBTITLE_="チェック後操作ボタン" \

3199 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3200 $layout/form+dump.m4.html \

3201 | _m4 -D_TEAM_="spaste(\`$stf')"

3202 <p>下でチェックした人を対象として:</p>

3203 <div class="foldtabs">

3204 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3205 title="Shortcut: a${nl}Add a team tag to"

3206 for="cmteam">同じチーム属性を付与</label>

3207 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3208 `cgi_datalist_h teams $allhexteams`

3209 </div>

3210 ${rmteammsg}

3211 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3212 title="Shortcut: d${nl}DirectMail to"

3213 for="sendmsg" title="Direct Message">DM送信</label>

3214 <div>

3215 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3216 title="Using email format">email書式を使う</label>

3217 <div class="folded">

3218 <table>

3219 <tr><td>From: </td><td>$user</td></tr>

3220 <tr><td>このFrom:で送る</td>

3221 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3222 </td></tr>

3223 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3224 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3225 <tr><td>ファイル添付: </td>

3226 <td>`cgi_file image "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3227 </table>

3228 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 送信`)</p>

3229 </div>

3230 <div>本文:`cgi_textarea text "" cols=72`

3231 </div>

3232 </div>

3233 ${isowner:+$cmmsg$excmsg}

3234 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3235 </div>

3236 <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote

3237 <table class="td2r thl">

3238 `sq $db -header -html "$sql"`

3239 </table>

3240 `cgi_hidden grp $grid`

3241 `cgi_hidden myuid $myuid id="myuid"`

3242 EOF

3243 }

3244 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3245 # Create TEMPORARY VIEW

3246 query<<EOF

3247 CREATE TEMPORARY VIEW writeusers AS

3248 SELECT DISTINCT author FROM article

3249 WHERE id in (

3250 select id from article where blogid=(select id from blog where rowid=$1)

3251 );

3252 CREATE TEMPORARY VIEW movablegroups AS

3253 SELECT g.rowid growid , g.gname

3254 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3255 ON grp.gname=gm.gname -- そのユーザが属している

3256 AND user='$user') g -- グループに絞る

3257 WHERE (SELECT author FROM writeusers

3258 EXCEPT

3259 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3260 IS NULL;

3261 $2

3262 EOF

3263 }

3264 sql4readableblogs() {

3265 # Create view of blogs that can be readable to $user

3266 # Blog is readable when:

3267 # 1: blog owner is an user

3268 # 2: else, 2.1: owner-group where the $user belongs

3269 # 2.2: else, owner-group is not moderated

3270 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3271 cat<<EOF ## | tee tmp/sql.out

3272 CREATE TEMPORARY VIEW readableblogs AS

3273 SELECT blog.rowid rid, id, author

3274 FROM blog

3275 NATURAL JOIN

3276 (SELECT id,

3277 max(CASE key WHEN 'owner' THEN val END) owner,

3278 max(CASE key WHEN 'mode' THEN val END) mode

3279 FROM blog_s GROUP by id) bs

3280 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3281 THEN 1 -- blog owner is an user, READABLE

3282 WHEN (SELECT val FROM grp_s

3283 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3284 AND

3285 (SELECT user FROM grp_mem

3286 WHERE gname=bs.owner AND user='$user') IS NULL

3287 THEN 0

3288 WHEN mode IN ('quiz', 'enquete')

3289 THEN 0 -- "quiz" mode blog cannot be searched

3290 ELSE 1

3291 END;

3292 EOF

3293 }

3294 mvteamform() {

3295 owner=$1

3296 hexteams=$(hexteams "$owner" "$user")

3297 test -z "$hexteams" && return

3298 none="`echo なし|hexize`"

3299 cat<<-EOF

3300 <!-- <div class="fold">

3301 `cgi_checkbox mv2team send id="mv2team"`<label

3302 for="mv2team">この話題を以下のチームのものにする</label>

3303 <div> -->

3304 <p>この話題をチーム所有にする:

3305 チーム: `cgi_select_h mv2team $none $hexteams`</p>

3306 </form></div></div>

3307 EOF

3308 }

3309 editheading() { # $1=rowid-of-heading

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

3311 if [ -z "$rowid" ]; then

3312 echo "話題番号が未指定です。" | html p

3313 return

3314 fi

3315 owner=`getvalbyid blog owner $rowid`

3316 title=`getvalbyid blog title $rowid`

3317 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3318 | _m4 -D_TITLE_="修正" \

3319 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3320 -D_BLOGS_="" -D_DUMPTABLE_="" \

3321 -D_FORM_="syscmd(\`cat')" \

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

3323 # Move to group

3324 if isuser "$owner"; then

3325 crview4article $rowid

3326 n=`query "SELECT count(*) FROM writeusers;"`

3327 ## err N=$n

3328 if [ $((n)) -gt 0 ]; then

3329 ## err ROWID=$rowid

3330 sql="SELECT growid || ':' || gname FROM movablegroups;"

3331 cat<<-EOF

3332 <div class="fold">

3333 `cgi_checkbox mv send id="mv"`<label

3334 for="mv">この話題をグループ所有に移動する</label>

3335 <div>

3336 <form action="?mvart" method="POST" enctype="multipart/form-data">

3337 移動先グループ:

3338 <select name="mv2grp">

3339 EOF

3340 query ".mode html"

3341 query<<-EOF |

3342 $sql

3343 .mode list

3344 EOF

3345 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3346 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3347 cat<<-EOF

3348 </select>

3349 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3350 そのグループに加入しているものに限られます)</p>

3351 <p>`cgi_checkbox cfm yes`<label>確認

3352 (この操作は元に戻すことができません)</label></p>

3353 `cgi_hidden blogrowid $rowid`

3354 `cgi_submit 移動`

3355 `cgi_reset Reset`

3356 </form>

3357 </div>

3358 </div>

3359 EOF

3360 fi

3361 # end of isuser "$owner"

3362 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3363 [ -n "$hexteams" ];}; then

3364 none="`echo なし|hexize`"

3365 cat<<-EOF

3366 <div class="fold">

3367 `cgi_checkbox mv2team send id="mv2team"`<label

3368 for="mv2team">この話題を以下のチームのものにする</label>

3369 <div><p>現在の所属チーム設定:

3370 `query "SELECT

3371 coalesce((SELECT val FROM blog_s

3372 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3373 AND key='team'),

3374 ':なし');"`</p>

3375 <form action="?mvart" method="POST" enctype="multipart/form-data">

3376 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3377 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3378 `cgi_hidden blogrowid $rowid`<br>

3379 `cgi_submit 移動`

3380 `cgi_reset Reset`

3381 </form></div></div>

3382 EOF

3383 fi

3384 }

3385 mvart() { # move diary to some group or team

3386 # or move blog of group to team which belong to the group

3387 blogrowid=`getpar blogrowid`

3388 cfm=`getpar cfm`

3389 ##### echo move blog:$blogrowid to $mv2grp | html p

3390 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3391 . ./s4-blog.sh

3392 if [ -z "$blogrowid" ]; then

3393 echo "無効な指定です(mvart)。" | html p

3394 return

3395 elif [ x"$cfm" != x"yes" ]; then

3396 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3397 elif { mv2grp=`getpar mv2grp`

3398 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3399 [ -n "$mv2grp" ]; }; then

3400 crview4article $blogrowid

3401 ########## TRANSACTION BEGIN

3402 query "BEGIN;"

3403 n=`query "SELECT count(*) FROM writeusers;"`

3404 ## err Nwriteuser=$n

3405 if [ $((n)) -gt 0 ]; then

3406 query<<-EOF

3407 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3408 WHERE key='owner'

3409 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3410 AND $mv2grp IN (SELECT growid FROM movablegroups);

3411 EOF

3412 fi

3413 query "END;"

3414 ########## TRANSACTION END

3415 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3416 [ -n "$mv2team" ];}; then

3417 # blog owner can move it to ANY team

3418 case "$mv2team" in

3419 'なし')

3420 cat<<-EOF

3421 DELETE FROM blog_s

3422 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3423 AND key='team';

3424 EOF

3425 ;;

3426 "") ;;

3427 *)cat<<-EOF

3428 BEGIN;

3429 REPLACE INTO blog_s(id, key, val)

3430 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3431 'team', '$mv2team');

3432 REPLACE INTO blog_s(id, key, val)

3433 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3434 'notify', 'all'); -- Change notify to all

3435 END;

3436 EOF

3437 esac | query

3438 fi

3439 blog_reply $blogrowid

3440 echo yes | html p

3441 }

3442 editart() { # $1=article-rowid $2=blogrowid

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

3444 blogrowid=${2%%[!A-Z0-9a-z_]*}

3445 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3446 echo "表示する記事番号が未指定です。" | html p

3447 return

3448 fi

3449 owner=`getvalbyid blog owner $blogrowid`

3450 title=`getvalbyid blog title $blogrowid`

3451 author=`getvalbyid article author $rowid`

3452 ## err EDITart: owner=$owner, author=$author

3453 if isgrpowner "$user" "$owner"; then

3454 : EDIT OK

3455 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3456 echo "本人か所有者しか編集できません." | html p

3457 return

3458 fi

3459 aid=`query "select id from article where rowid=$rowid;"`

3460 tmpout=$tmpd/editart.$$.out

3461 GF_ACTION="?replyblog+$blogrowid#$aid" \

3462 edittable $formdir/article.def article $rowid \

3463 > $tmpout

3464 rm -f /tmp/editart.out

3465 # Cannot use pipelining to m4 with genform() because of stdin stack

3466 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3467 -D_FORM_="syscmd(cat $tmpout)" \

3468 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3469 -D_BLOGS_= -D_DUMPTABLE_= \

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

3471 }

3472 send2mem() {

3473 rowid=`getpar grp`

3474 rowid=${rowid%%[!0-9]*} # Cleaning

3475 if [ -z "$rowid" ]; then

3476 echo "グループが未指定です。" | html p

3477 return

3478 fi

3479 message=`getpar message`

3480 if [ -z "$message" ]; then

3481 echo "文章を入れてください。" | html p

3482 return

3483 fi

3484 grp=`getgroupbyid $rowid`

3485 members=`collectemail "$grp"`

3486 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3487 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3488 mailfrom="`gecos "$user"` <$mailfrom>"

3489 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3490 msg=$(cat<<-EOF

3491 $urlbase?grp+$rowid

3492 グループ $grp に所属する

3493 `gecos $user` さんよりメッセージ:

3494

3495 $message

3496 EOF

3497 )

3498 # smail rcpt subj (file)

3499 for m in $members; do

3500 echo "$msg" |

3501 MAIL_FROM=$mailfrom \

3502 SENDER=$noreply \

3503 REPLYTO=$mailfrom \

3504 smail "$m" "$sj"

3505 done

3506 cat<<EOF

3507 <p>以下のユーザに送信しました。</p>

3508 <pre>

3509 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3510 </pre>

3511 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3512 EOF

3513 }

3514 joingrpadmit() {

3515 # $1=yes/no $2=session-key

3516 if [ -z "$2" ]; then

3517 echo "bye bye" | html p; return

3518 fi

3519 t_usr=`session=$2 getpar adduser`

3520 t_grp=`session=$2 getpar group`

3521 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3522 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3523 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3524 echo "無効な加入依頼です。" | html p

3525 echo "有効期限が切れたか、

3526 他の管理者がいる場合は処理済みの可能性があります。" | html p

3527 return

3528 fi

3529 if ! isgrpowner "$user" "$t_grp"; then

3530 echo "グループ管理者のみの機能です。" | html p; return

3531 fi

3532 case $1 in

3533 yes) joingrp "$t_grp" "$t_usr" yes ;;

3534 no) joingrp "$t_grp" "$t_usr" no ;;

3535 *)

3536 echo "無効な指定です($1)。" | html p

3537 return ;;

3538 esac

3539 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3540 rcpts="`getgroupadminmails "$t_grp"` $user"

3541 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3542 body="に

3543 $t_usr

3544 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3545 しました。"

3546 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3547 query "delete from session where id='$2';"

3548 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3549 }

3550

3551 joingrprequest() {

3552 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3553 jss="joingrp-`date +%s`-`genrandom 12`"

3554 addsession $jss +${memoplimitdays}days

3555 grpadmins=`getgroupadmins "$1"`

3556 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3557 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3558 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3559 $url

3560 $user さんから

3561 グループ $1

3562 に加入依頼がありました。

3563

3564 承認する:

3565 $urlbase?joingrpadmit+yes+$jss

3566

3567 白紙に戻す:

3568 $urlbase?joingrpadmit+no+$jss

3569 EOF

3570 echo "管理者に加入依頼を出しました。

3571 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3572 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3573 }

3574 joingrp() {

3575 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3576 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3577 if isgrpowner "$user" "$1"; then

3578 isowner="yes"

3579 elif [ -n "$5" ]; then

3580 isowner="yes"

3581 else

3582 isowner=""

3583 fi

3584 ## err jg:isgrpowner: isowner="$isowner"

3585 if [ -n "$isowner" ]; then

3586 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3587 elif [ x"$2" != x"$user" ]; then # if user is not login user

3588 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3589 return

3590 elif [ x"$3" = x"no" ]; then

3591 : # Do not pursue those who leave

3592 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3593 : # Member can change own email address for the joining moderated group

3594 else # adding user is $user itself

3595 case `getgroupattr "$1" regmode` in

3596 moderated)

3597 joingrprequest "$@" # Request only

3598 return

3599 ;;

3600 *)

3601 ;;

3602 esac

3603 fi

3604 qgname=`sqlquote "$1"`

3605 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3606 cond="where gname=$qgname and user='$2'"

3607 if [ x"$3" = x"yes" ]; then

3608 query "replace into grp_mem values($qgname, '$2');"

3609 # Notify joingrp to admin

3610 action="に加入しました。"

3611 if [ -n "$4" ]; then

3612 if msg=`emaildomaincheck "$4"`; then

3613 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3614 'string', '$4', NULL);"

3615 else

3616 echo $msg

3617 fi

3618 else

3619 query "delete from grp_mem_s $cond and key='email';"

3620 fi

3621 if [ -n "$5" ]; then # as ADMIN

3622 # Coming here means newly created group

3623 sql="select case\

3624 when (select count(*) from grp_mem where gname=$qgname)=1\

3625 then (select user from grp_mem\

3626 where gname=$qgname and user='$user')\

3627 else '' end; "

3628 err NewGrpChk: $sql

3629 if [ -n "`query \"$sql\"`" ]; then

3630 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3631 query "replace into grp_adm values($qgname, '$user');"

3632 fi

3633 fi

3634 else

3635 query "begin;

3636 delete from grp_mem $cond;

3637 delete from grp_mem_s $cond;

3638 delete from grp_mem_m $cond;

3639 delete from grp_adm $cond;

3640 delete from grp_adm_s $cond;

3641 delete from grp_adm_m $cond;

3642 end;"

3643 action="から脱退しました。"

3644 fi

3645 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3646 $url?grp+$grid

3647 $user (`gecos $user`)さんが

3648 グループ $1

3649 $action

3650 EOF

3651 }

3652 grp_add_team() (

3653 # $1=grp-rowid $2=team $3...=user-rowid(s)

3654 grp=`getgroupbyid $1`

3655 team=$2; shift; shift

3656 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3657 { echo "BEGIN;"

3658 for user; do

3659 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3660 '$grp',\

3661 (SELECT name FROM user WHERE rowid=$user),\

3662 'team', 'string', '$team');"

3663 done

3664 echo "END;"

3665 } | query

3666 )

3667 grp_rm_team() (

3668 # $1=grp-rowid $2=team $3...=user-rowid(s)

3669 grid=$1

3670 qgrp=$(sqlquote "`getgroupbyid $grid`")

3671 team=$2; shift; shift

3672 [ -z "$grid" -o -z "$team" ] && return

3673 { echo "BEGIN;"

3674 for user; do

3675 echo "DELETE FROM grp_mem_m\

3676 WHERE gname=$qgrp \

3677 AND user=(SELECT name FROM user WHERE rowid=$user)\

3678 AND key='team' AND val='$team';"

3679 done

3680 cat<<-EOF

3681 DELETE FROM blog_s

3682 WHERE rowid=(

3683 SELECT rowid

3684 FROM blog_s a

3685 WHERE key='team'

3686 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3687 AND NOT EXISTS (SELECT * FROM grp_mem_m

3688 WHERE key='team' AND val=a.val -- a.val=team

3689 AND gname = (SELECT val FROM blog_s b

3690 WHERE a.id=b.id AND key='owner')

3691 ));

3692 EOF

3693

3694 echo "END;"

3695 } | query

3696 )

3697 grp_reg_adm() {

3698 # $1=grp-rowid $2...=user-rowid

3699 grid=$1

3700 grp=`getgroupbyid "$1"`

3701 if [ -z "$grp" ]; then

3702 echo "無効なグループIDです" | html p; return

3703 fi

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

3705 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3706 fi

3707 shift

3708 for urid; do

3709 newadm=`query "select name from user where rowid=$urid;"`

3710 if [ -z "$newadm" ]; then

3711 echo "指定ユーザIDがおかしいようです。" | html p; return

3712 fi

3713 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3714 err ismember $newadm $grp

3715 if ismember $newadm "$grp"; then

3716 # OK, go ahead

3717 getgname="(select gname from grp where rowid=$grid)"

3718 query "replace into grp_adm values($getgname, '$newadm');"

3719 # confirm insertion

3720 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3721 if [ -n "`query \"$sql;\"`" ]; then

3722 echo "追加完了: $newadm" | html p

3723 else

3724 echo "追加失敗($1 $urid)" | html p

3725 fi

3726 fi

3727 showgroup $grid

3728 done

3729 }

3730 dt_colhack() {

3731 # FROM: <TD>xxx:yyy</TD>

3732 # TO: <TD class="xxx">yyy</TD>

3733 sed -Ee 's,<TD>([^:<"]+):([^<]*)(</TD>|$),<TD class="\1">\2\3,g'

3734 }

3735 dt_rowhack() {

3736 # From: <TR>

3737 # ....

3738 # <TD>rowclass=foo</TD>

3739 # </TR>

3740 # To: <TR class="foo">....<TD>foo</TD></TR>

3741 sed -e '

3742 /^<TR>/ {

3743 :loop

3744 s/\n//

3745 N

3746 /<\/TR>/ {

3747 s/\n//

3748 s,^<TR>$.*$<TD$[^>]*$>rowclass=$.*$$</TD></TR>$,<TR class="\3">\1<TD\2>\3\4,

3749 n

3750 }

3751 $q

3752 b loop

3753 }'

3754 }

3755 dumptable() {

3756 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3757 # textのフィールドだけ全てダンプにしたほうがいいか

3758 # $DT_VIEW sets link

3759 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3760 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3761 VIEW=${DT_VIEW-replyblog}

3762 if [ -n "$VIEW" ]; then

3763 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3764 fi

3765 sqlfile=$tmpd/dump.sql

3766 : > $sqlfile # ensure to be empty

3767 printf '.mode html\n.header 1\n' > $sqlfile

3768 # $DT_CHLD=ChildTable:BindColumn

3769 if [ -n "$DT_CHLD" ]; then

3770 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3771 cat<<-EOF >> $sqlfile

3772 -- presql

3773 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3774 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3775 EOF

3776 # Speed up counting of new articles

3777 cat<<-EOF >> $sqlfile

3778 -- presql2

3779 DROP TABLE IF EXISTS _counts;

3780 CREATE TEMPORARY TABLE _counts AS

3781 SELECT $_i, count($_i) cnt

3782 FROM $_t GROUP BY $_i;

3783 /* Prepare NEW count table */

3784 CREATE TEMPORARY TABLE _target AS

3785 SELECT b.rowid trowid, b.id

3786 FROM "$2" b JOIN "$2_s" s

3787 ON b.id=s.id AND s.key='owner'

3788 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3789

3790 DROP TABLE IF EXISTS _children;

3791 CREATE TEMPORARY TABLE _children AS

3792 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3793 FROM (SELECT t.trowid, t.id $_i, a.id

3794 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3795 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3796

3797 DROP TABLE IF EXISTS _news;

3798 DROP VIEW IF EXISTS _news;

3799

3800 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3801 -- INSERT INTO _news

3802 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3803 CREATE TEMPORARY VIEW _news AS

3804 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3805 FROM (SELECT DISTINCT id FROM _target)

3806 a LEFT JOIN

3807 (SELECT $_i, count(ctime) newcnt

3808 FROM _children x

3809 WHERE ctime > coalesce((SELECT time from myacclog

3810 WHERE tblrowid=x.trowid),

3811 '1970-01-01')

3812 GROUP BY $_i) b

3813 ON a.id=b.$_i;

3814 EOF

3815 # REMOVE next line until 2019/5/1

3816 cntall="(select count($_i) from $_t where $_i=a.id)"

3817 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3818 # REMOVE next assignment until 2019/5/1

3819 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3820 and id in (select id from $_t where $_i=a.id) \

3821 and val > coalesce((select time from myacclog where \

3822 tblrowid=a.rowid),\

3823 '1970-01-01'))"

3824 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3825 cnt="$cntnew as '新着', $cntall as '総数',"

3826 dt_class=" td2r td3r dumpblogs"

3827 fi

3828 # Construct join expression

3829 eav="" scols=""

3830 pk=`gettblpkey $2`

3831 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3832 substr=${substr:-%s}

3833 for col in ${3:-`gettbl_s_cols $2`}; do

3834 valvar=val

3835 fromtbl=b

3836 if gettblcols "$2" | grep -w "$col" >/dev/null 2>&1; then

3837 # If $col belongs to master table

3838 fromtbl=a; col=${col#a.}

3839 fi

3840 case $col in

3841 gecos) scols="$scols${scols:+, }${col#}"

3842 continue ;; # built-in column name

3843 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3844 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3845 case "$as" in

3846 *:*=*) cnd=${as#*:}

3847 h=${cnd%%=*} v=${cnd#*=}

3848 h=`sqlquotestr "$h"`

3849 v=`sqlquotestr "$v"`

3850 if [ x"$fromtbl" = x"b" ]; then

3851 valvar="CASE val WHEN $h THEN $v END"

3852 else

3853 valvar="$h"

3854 fi

3855 as=${as%%:*} ;;

3856 esac

3857 ;;

3858 *) as=${col} ;;

3859 esac

3860 ss=`printf "$substr" "$valvar"`

3861 if [ x"$fromtbl" = x"b" ]; then

3862 eav=$eav${eav:+,}" \"$as:\"||coalesce(max(case key when '$col' then $ss end), '') as $as"

3863 else

3864 eav=$eav${eav:+,}" \"$as:\"||$ss as $as"

3865 fi

3866 scols="$scols${scols:+, }${fromtbl}.$as"

3867 done

3868 #case author when '$user' then a.rowid else '---' end as ID,

3869 if [ -n "$DT_SQL" ]; then

3870 echo "$DT_SQL"

3871 else

3872 cat<<-EOF

3873 SELECT a.rowid as LINK, $cnt $scols

3874 FROM $2 a LEFT JOIN

3875 (SELECT $pk,$eav,

3876 max(CASE key

3877 WHEN 'owner'

3878 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3879 as gecos

3880 FROM ${2}_s c GROUP BY $pk)

3881 b ON a.$pk=b.$pk $4;

3882 EOF

3883 fi >> $sqlfile

3884 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3885 sqlog<<-EOF

3886 *** SQL-file: $sqlfile ***

3887 `cat $sqlfile`

3888 EOF

3889 printf '.mode list\n.header 0\n' >> $sqlfile

3890 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_colhack | dt_rowhack

3891 <div>

3892 <div class="dumptable">

3893 <table class="b$dt_class">

3894 `query ".read $sqlfile"`

3895 </table>

3896 </div>

3897 </div>

3898 EOF

3899 }

3900

3901 clean-orphaned() {

3902 # This shoud be done by foreign_key rules, but some db lack them

3903 query<<-EOF

3904 -- Find blogs that have no parent

3905 WITH orphanedblog AS (

3906 SELECT blog.id,val FROM blog JOIN blog_s bs

3907 ON blog.id=bs.id AND key='owner'

3908 WHERE val NOT IN (SELECT gname FROM grp)

3909 AND val NOT IN (SELECT name FROM user)

3910 ) -- Remove them

3911 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

3912

3913 -- Find articles that have no parent blog

3914 WITH orphanedarticle AS (

3915 SELECT id FROM article

3916 WHERE blogid NOT IN (SELECT id FROM blog)

3917 ) -- Remove them

3918 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

3919 EOF

3920 }

3921

3922 clearcachedir() (

3923 td=`getcachedir "$1"`

3924 err td=$td: ls- `ls $td`

3925 if [ -w "$td/image.1" ]; then

3926 err Removing td=$td

3927 rm -fr $td/ # Clear icon-image cache!

3928 fi

3929 )

3930

3931 par2table() (

3932 # copy current parameters of par into destination table

3933 # $1=definition-file

3934 # Using $user and $session

3935 # Return value:

3936 # 0: Stored successfully

3937 # 1: Insufficient fillings

3938 # 2: No permission to modify the record

3939 # 3: Invalid rowid

3940 # 4: SUCCESS to delete

3941 # 5: Stop deletion for lack of confirm check

3942 # 6: Password length too short

3943 # 7: Password mismatch

3944 # 8: Old password incorrect

3945 # 9: Duplicated post

3946 rowid=`getpar rowid`

3947 if [ ! -e $1 ]; then

3948 echo "テーブル定義ファイルが見付かりません" | html p

3949 exit 1

3950 fi

3951 tbl=${1%.def}

3952 tbl=${tbl##*/}

3953 if [ -n "$rowid" ]; then # Modify existing entry

3954 if [ x"$tbl" = x"user" ]; then

3955 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3956 elif [ x"$tbl" = x"grp" ]; then

3957 sql="select gname from $tbl where rowid=$rowid;"

3958 ##err p2t:grp:q $sql

3959 isgrpowner "$user" "`query $sql`" && rowowner=$user

3960 elif [ x"$tbl" = x"blog" ]; then

3961 # Check if owner in blog_s

3962 blogowner=`getvalbyid blog owner "$rowid"`

3963 if isgrpowner "$user" "$blogowner"; then

3964 rowowner=$user

3965 else

3966 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3967 fi

3968 else

3969 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3970 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3971 fi

3972 ### err rowowner=$rowowner

3973 if [ x"$user" != x"$rowowner" ]; then

3974 echo "他人のレコードはいじれないの" | html p

3975 return 2

3976 elif [ -z "$rowowner" ]; then

3977 echo "指定したレコードはないみたい" | html p

3978 return 3

3979 fi

3980 rm=`getpar rm` cfm=`getpar confirm`

3981 # Editing existent entry

3982 if [ x"$rm" = x"yes" ]; then

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

3984 query "delete from $tbl where rowid=$rowid;"

3985 clearcachedir "$tbl/$rowid"

3986 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

3987 clean-orphaned

3988 fi

3989 return 4

3990 else

3991 echo "消去確認のチェックがないので消さなかったの..." | html p

3992 return 5

3993 fi

3994 fi

3995 fi

3996

3997 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

3999 # Update of existing record

4000 for col in `gettblcols $tbl`; do

4001 val=`getparquote $col`

4002 [ -z "$val" ] && continue

4003 ## err query "update $tbl set $col=$val where rowid=$rowid"

4004 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

4005 sql="update $tbl set $col=$val where rowid=$rowid;"

4006 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

4007 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

4008 ## User name cannot be changed with interface provided with this

4009 ## script. But we offer the trigger to change owner user

4010 ## of blog_s table.

4011 #err "select quote($col) from $tbl where rowid=$rowid;"

4012 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

4013 cat<<-EOF | query

4014 -- Here we cannot use BEGIN-COMMIT because groupupdate()

4015 -- should use EXCLUSIVE transaction outside of this.

4016 SAVEPOINT par2table;

4017 $sql

4018 update blog_s set val=$val

4019 where key='owner' and val=$old;

4020 RELEASE SAVEPOINT par2table;

4021 EOF

4022 ## XX: DIRTY Hack Ends here

4023 ## We should keep blog's owner as a single column which has

4024 ## foreign key constraint with primary key of grp/user.

4025 else

4026 query "$sql"

4027 fi

4028 done

4029 # Then, set up $pval for further insertion of tbl_s and tbl_m

4030 for col in `gettblpkey $tbl`; do

4031 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

4032 pval="$pval${pval:+, }\"$val\""

4033 done

4034 else

4035 # New entry

4036 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

4037 # Avoid empty repost of article.

4038 if [ x"$tbl" = x"article" ]; then

4039 # If rowid is empty and ID exists in article-table, that is REPOST!

4040 aid=`getpar id`

4041 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

4042 if [ -n "$xaid" ]; then

4043 # REPOST of article

4044 html p <<-EOF

4045 書き込み直後のリロードなので上書きを回避します。

4046 最新記事は末尾の「再読み込み」ボタンから見てください。

4047 EOF

4048 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

4049 return 9 # STOP Duplicated posting

4050 fi

4051 fi

4052 # Generate values() for primary keys

4053 for col in `gettblpkey $tbl`; do

4054 # Genuine primary keys for _m and _s

4055 val=`getvalquote $tbl $col`

4056 [ -z "$val" ] && continue

4057 pval="$pval${pval:+, }$val"

4058 done

4059 ##err pval=$pval

4060 for col in `gettblfkey $tbl`; do

4061 # args for values() to insertion into master table

4062 val=`getvalquote $tbl $col`

4063 [ -z "$val" ] && continue

4064 formaster=$formaster"${formaster:+, }$val"

4065 done

4066 formaster="$pval${formaster:+, }$formaster"

4067 ## err formaster=$formaster

4068 if [ -z "$formaster" ]; then

4069 echo "項目を全て埋めてください" | html pre

4070 return 1

4071 fi

4072 ## err "replace into $tbl values($formaster);"

4073 query "replace into $tbl values($formaster);"

4074 ## Insertion to master table, done

4075 fi

4076

4077 transaction=$tmpd/sqlfile.sql; touch $transaction

4078 for kt in s m; do

4079 tb2=${tbl}_$kt

4080 for col in `gettbl_${kt}_cols $tbl`; do

4081 ptype=`getpartype $col "limit 1"`

4082

4083 # First, check update of existing entries in _m

4084 if [ $kt = m ]; then

4085 # sessID|address.1.22|string|Somewhere-x.y.z

4086 sql=""

4087 ##err dots from query "select var from par where var like '$col.%';"

4088 for v in `query "select var from par where var like '$col.%' AND sessid='$session';"`; do

4089 # v=address.1.22

4090 st_rowid=${v##*.}

4091 origcol=${v%%.*} # original column derived from

4092 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

4093 ##case `getpartype $v` in

4094 ## err CASE `gettbl_coltype $tbl/$origcol` in

4095 ## err edit flag = `getpar action.$v`

4096 case `getpar action.$v` in

4097 rm)

4098 if [ x`getpar confirm.$v` = x"yes" ]; then

4099 newsql="delete from $tb2"

4100 else

4101 echo "削除確認未チェック" | html p

4102 fi ;;

4103 edit)

4104 case `gettbl_coltype $tbl/$origcol` in

4105 image|document|binary)

4106 file=$tmpd/`getparfilename $v`

4107 if [ ! -s "$file" ]; then # Maybe stupid REPOST

4108 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

4109 continue

4110 fi

4111 ## err type=file=$file

4112 [ -z "$file" ] && continue

4113 bn=`sqlquotestr "${file##*/}"`

4114 bin="X'"$(hexize "$file")"'"

4115 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

4116 type=\"file:$ct\"

4117 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

4118 cachedir=`getcachedir "$tbl/$rowid"`

4119 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

4120 rm -rf $cachedir

4121 ;;

4122 *)

4123 newsql="update $tb2 set val=(select val from par where var \

4124 like '$col.%.$st_rowid' AND sessid='$session')"

4125 ;;

4126 esac

4127 ;;

4128 mv)

4129 # regularize filename and strip directory part

4130 newname=`getpar mv.$v|tr -d '":;#<>?^%$!'|tr -d "'"|tr ' ' _`

4131 newname=`basename $newname`

4132 oldname=`query "SELECT val FROM $tb2 WHERE rowid=$st_rowid;"`

4133 oldext=`expr "$oldname" : '.*\.$.*$'`

4134 newext=`expr "$newname" : '.*\.$.*$'`

4135 err "p2t(mv): oldname=$oldname $oldext -> newname($v)=$newname $newext"

4136 if [ -n "$newname" -a x"$oldext" = x"$newext" ];

4137 then

4138 newsql="UPDATE $tb2 SET val='$newname'"

4139 else

4140 html p<<-EOF

4141 $newname は取り扱えないファイル名です。

4142 空白を含まない名前にして下さい。

4143 拡張子の変更もできません。

4144 EOF

4145 continue

4146 fi

4147 ;;

4148 *) # maybe "keep", do not modify value

4149 continue

4150 ;;

4151 esac

4152 # err newsql=$newsql

4153 sql=$sql$nl"$newsql where rowid=$st_rowid;"

4154 clearcachedir "$tbl/$rowid"

4155 done

4156

4157 if [ x"$bin" = x"NULL" ]; then

4158 ## err repl:normal sql=`echo $sql`

4159 if [ -n "$transaction" ]; then

4160 cat<<-EOF >> $transaction

4161 $sql

4162 DELETE FROM $tb2 WHERE type='string' AND val='';

4163 EOF

4164 else

4165 query "$sql

4166 delete from $tb2 where type='string' and val='';"

4167 ## err repl:normal done

4168 fi

4169 else

4170 # Binary update line is TOO LONG to pipelining

4171 sqlfile="$tmpd/sqlf.$$"

4172 if [ -n "$transaction" ]; then

4173 echo "$sql" >> $transaction

4174 else

4175 echo "$sql" > $sqlfile

4176 query ".read $sqlfile"

4177 fi

4178 fi

4179 # Rest of kt==m: set multiple mode

4180 nr=`getparcount $col`

4181 else

4182 nr=1 # for kt==s, number of records is 1

4183 fi

4184

4185 i=0

4186 while [ $i -lt $nr ]; do

4187 limit="limit 1 offset $i"

4188 i=$((i+1)) # increase beforehand against continue

4189 val=`getvalquote $tbl $col "$limit"`

4190 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4191 ## err $col=$val

4192 bin=NULL

4193 ## err partype$col=`getpartype $col "$limit"`

4194 ptype=`getpartype $col "$limit"` # partype should be obtained each time

4195 case $ptype in

4196 file) file=$tmpd/`getparfilename $col "$limit"`

4197 ## err parfile-$col=$file

4198 [ -z "$file" ] && continue

4199 bin="X'"$(hexize "$file")"'"

4200 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4201 type=\"file:$ct\" ;;

4202 "*"*) continue ;; # foreign table

4203 *) type=\"string\" ;;

4204 esac

4205 case `gettbl_coltype $tbl/$col` in

4206 password) # special care for password

4207 # name={password,pswd1,pswd2}

4208 p1=`getpar pswd1 "$limit"`

4209 if [ -z "$p1" ]; then

4210 continue # SKIP password setting, if p1 is empty

4211 else

4212 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4213 ## err pswd=$pswd

4214 if pwcheck "$pswd"; then

4215 if [ x"$p1" = x"$p2" ]; then

4216 case "$p1" in

4217 ??????????*) ;;

4218 *) echo "パスワードは10字以上にしてください。" | html p

4219 return 6;;

4220 esac

4221 val="\"`echo $p1|mypwhash`\""

4222 else

4223 echo "2つの新パスワード不一致" | html p

4224 return 7

4225 fi

4226 else

4227 echo "旧パスワード違います" | html p

4228 return 8

4229 fi

4230 fi

4231 ;;

4232 esac

4233 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4234 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4235 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4236 if [ x"$bin" = x"NULL" ]; then

4237 ## err Normal-query: `echo $sql`

4238 if [ -n "$transaction" ]; then

4239 echo "$sql" >> $transaction

4240 else

4241 query "$sql"

4242 fi

4243 else

4244 sqlfile="$tmpd/query.$$"

4245 ## err sqlfile=`ls -lF $sqlfile`

4246 if [ -n "$transaction" ]; then

4247 echo "$sql" >> $transaction

4248 else

4249 echo "$sql" > $sqlfile

4250 query ".read $sqlfile"

4251 fi

4252 fi

4253 ## err p2t done

4254 done

4255 done

4256 done

4257 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4258 -- We cannot use transaction here, because groupupdate may use it.

4259 SAVEPOINT pa2table_insert;

4260 .read $transaction

4261 RELEASE SAVEPOINT pa2table_insert;

4262 EOF

4263 rc=$?

4264 [ $rc -eq 0 -a x"$tbl" = x"user" ] && flag_profupdate

4265 ## err "Table:$tbl update done "

4266 return $rc

4267 )

4268 genform() {

4269 # $1 = form definition file

4270 # $2, $3 (optional)= table name and ROWID

4271 # If $GF_VIEWONLY set and nonNull, output values without form

4272 # If $GF_ARGS set, use it as content-strings in the form

4273 # If $GF_OWNER set, use it as value of name="owner"

4274 # If $GF_STAGE set, use it as value of name="stage"

4275 forms="" hiddens="" rowid=$3

4276 if [ ! -e "$1" ]; then

4277 echo "そのようなデータベースはないようです($2)。" | html p

4278 return

4279 elif [ -n "$2" ]; then

4280 rec=`query "select * from $2 where rowid='$rowid';"`

4281 if [ -z "$rec" ]; then

4282 pk=`gettblpkey $2`

4283 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4284 rec=`query "select rowid from $2 where $pk='$rowid';"`

4285 rowid=$rec

4286 rec=$3

4287 fi

4288 if [ -z "$rec" ]; then

4289 echo "そんなレコードはないみたいね..." | html p

4290 return

4291 fi

4292 fi

4293 if [ -z "$GF_VIEWONLY" ]; then

4294 rm='<input id="rm" name="rm" type="checkbox"

4295 value="yes"><label for="rm">このエントリの削除</label>

4296 <span>ほんとうに消しますよ(確認)!

4297 <input name="confirm" type=checkbox value="yes">はい</span>'

4298 fi

4299 # Image Cache dir

4300 ## err genform: getcache=$2/$rowid

4301 td=`getcachedir "$2/$rowid"`

4302 while IFS=: read prompt name keytype type args; do

4303 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4304 sp="${args:+ }"

4305 form="" val=""

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

4307 # err genform2a: Seeking for "$2.$name, type=$type"

4308 rawval=`getvalbyid $2 $name $rowid $td`

4309 val=`echo "$rawval"|htmlescape`

4310 ## err genform3a: getvalbyid $2 $name $rowid $td

4311 ## err genform3b: val="[$val]" type="$type"

4312 fi

4313 if [ -n "$GF_VIEWONLY" ]; then

4314 is_hidden "$2" "$name" && continue

4315 fi

4316 case "$type" in

4317 text*)

4318 cgiform=cgi_multi_$type

4319 if [ -s $td/$name.count -a -n "$val" ]; then

4320 form=`$cgiform $name $td`

4321 val=$(echo "$val"|

4322 while read fn; do

4323 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4324 </td></tr>$nl"

4325 done)

4326 val="<table>$nl$val$nl</table>"

4327 else

4328 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4329 form=`cgi_$type $name "$rawval" "$args"`

4330 fi

4331 ;;

4332 [Rr][Aa][Dd][Ii][Oo])

4333 fh="<label><input type=\"radio\" name=\"$name\""

4334 form="`echo $args|sed -e \

4335 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4336 ;;

4337 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4338 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4339 ;;

4340 [Ss][Ee][Ll][Ee][Cc][Tt])

4341 fh="<select name=\"$name\">$nl"

4342 form=$(for l in $args; do

4343 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4344 done)

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

4346 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4347 fi

4348 form="$fh$form</select>"

4349 ;;

4350 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4351 if [ -s $td/$name.count ]; then

4352 form=`cgi_multi_file $name $td "$args"`

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

4354 hrfb="$myname?showattc+$2_m"

4355 val=$(echo "$rawval" \

4356 | while read fn; do

4357 data=`percenthex "$td/$fn"`

4358 #ct=`cat $td/$fn.content-type`

4359 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4360 ri=`cat "$td/$fn.rowid"`

4361 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4362 #imgsrc="<img src=\"data:$ct,$data\">"

4363 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4364 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4365 done)

4366 fi

4367 else

4368 form="<input type=\"file\" name=\"$name\" $args>"

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

4370 imgs=$(echo "$rawval"\

4371 |while read fn;do

4372 data=`percenthex "$td/$fn"`

4373 echo "<img src=\"data:image/png,$data\">$fn<br>"

4374 done)

4375 form=$form"<br>$imgs"

4376 val=$imgs # 2015-06-15

4377 else

4378 form="<input type=\"file\" name=\"$name\" $args>"

4379 fi

4380 fi

4381 ;;

4382 [Hh][Ii][Dd][Dd][Ee][Nn])

4383 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4384 args="value=\"$GF_STAGE\""

4385 fi

4386 form="<input type=\"hidden\" name=\"$name\" $args>"

4387 prompt='' # Remove prompt

4388 ;;

4389 [Aa][Uu][Tt][Hh][Oo][Rr])

4390 [ -n "$GF_VIEWONLY" ] && continue

4391 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4392 prompt="" ;;

4393 [Oo][Ww][Nn][Ee][Rr])

4394 [ -n "$GF_VIEWONLY" ] && continue

4395 val=${GF_OWNER:-$val}

4396 val=${val:-$user}

4397 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4398 prompt="" ;;

4399 [Uu][Ss][Ee][Rr])

4400 # XXX: is null $user ok?

4401 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4402 [ -n "$GF_VIEWONLY" ] && continue

4403 form="$user"

4404 ;;

4405 [Pp]assword)

4406 [ -n "$GF_VIEWONLY" ] && continue

4407 form="`cgi_passwd`"

4408 val=""

4409 ;;

4410 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4411 [ -n "$GF_VIEWONLY" ] && continue

4412 if [ -z "$rowid" ]; then

4413 val=`genserial`

4414 fi

4415 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4416 prompt="" ;;

4417 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4418 prompt=""

4419 ;;

4420 parent|path|blog*)

4421 prompt=""

4422 ;;

4423 "*"*)

4424 tail=$tail"``"

4425 continue ;;

4426 esac

4427 if [ -n "$prompt" ]; then

4428 if [ -n "${GF_VIEWONLY}" ]; then

4429 form=$val

4430 else

4431 :

4432 fi

4433 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4434 else

4435 hiddens=$hiddens$nl"$form"

4436 fi

4437 done < $1

4438 # enctype="multipart/form-data"

4439 cat<<EOF

4440 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4441 ${rowid:+$rm}

4442 <table class="b $2">

4443 $forms

4444 </table>$hiddens

4445 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4446 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4447 EOF

4448 if [ -z $GF_VIEWONLY ]; then

4449 cat<<EOF

4450 <input type="submit" name="sub" value="OK">

4451 <input type="reset" name="res" value="Reset">

4452 EOF

4453 fi

4454 cat<<EOF

4455 $GF_ARGS</form>

4456 $tail

4457 EOF

4458 }

4459 edittable() {

4460 # $1=form-def $2=table $3 rowid

4461 genform "$@"

4462 }

4463 viewtable() {

4464 GF_VIEWONLY=1 genform "$@"

4465 }

4466 showattc() {

4467 # $1=table_m $2=rowid &optional $3=RawFlag

4468 ## err \$1=$1 \$2=$2 \$3=$3

4469 if ! isfilereadable $user $1 $2; then

4470 contenttype; echo

4471 echo "このファイルは管理者のみしか見られません" | html p

4472 putfooter; exit

4473 fi

4474 idir=`umask 002; mktempd` || exit 1

4475 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4476 bin=$idir/$myname-$$.bin

4477 sql="select quote(bin) from $1 where rowid='$2';"

4478 ## err showattc: sql: $sql

4479 sq $db "$sql" | unhexize > $bin

4480 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4481 type=${tv%//*} fn=${tv#*//}

4482 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4483 ct=${type#file:}

4484 case $ct in # all text/* changed to text/plain

4485 text/*|application/csv|application/json)

4486 charset=`nkf -g $bin|cut -d' ' -f1`

4487 case $charset in

4488 ASCII*) charset="" ;;

4489 esac

4490 if [ -z "$3" ]; then

4491 ct="text/html${charset:+; charset=$charset}"

4492 link="?showattc+$1+$2+raw"

4493 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4494 | sed 's,^,<span></span>,' \

4495 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4496 -D_LINK_="$link" \

4497 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4498 exit $?

4499 fi

4500 ct="text/plain${charset:+; charset=$charset}"

4501 ;;

4502 esac

4503 contenttype "$ct"

4504 echo "Content-Disposition: filename=\"$fn\""

4505 echo "Content-Length: " `cat $bin | wc -c`; echo

4506 #echo "Content-Type: " ${type#file:}; echo

4507 cat $bin

4508 }

4509 #

4510 # Some default stupid handler on CGI values

4511 #

4512 default_storedb() {

4513 # ARG: $1=table-def-file

4514 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4515 tbl=`basename $1`

4516 tbl=${tbl%.def}

4517 cols="`grep :text $1|cut -d: -f2`"

4518 col=`echo "$cols"|head -1`

4519 vcol=`getpar $col`

4520 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4521 if [ -n "$vcol" ]; then

4522 par2table $1

4523 else

4524 return 2 # No insertion occurred

4525 fi

4526 }

4527

4528 default_view() { # $1=def-file

4529 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4530 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4531 default_storedb "$@"

4532 query "select rowid from $tbl order by rowid desc;" \

4533 | while read rowid; do

4534 viewtable $1 $tbl $rowid

4535 done | _m4 -D_TITLE_="$tbl" \

4536 -D_FORM_="`genform $1`" \

4537 -D_DUMPTABLE_="syscmd(cat)" \

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

4539 }

4540 default_viewtext() { # $1=def-file

4541 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4542 default_storedb "$@"

4543 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4544 | _m4 -D_TITLE_="$tbl" \

4545 -D_FORM_="`genform $1`" \

4546 -D_DUMPTABLE_="syscmd(cat)" \

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

4548 }

4549 default_smail() {

4550 default_storedb "$@"

4551 if [ $? -eq 2 ]; then

4552 _m4 -D_TITLE_="入力" \

4553 -D_FORM_="`genform $1`" \

4554 -D_DUMPTABLE_="" \

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

4556 return

4557 fi

4558 cond=""

4559 for pk in `gettblpkey $tbl`; do

4560 pv=$(sqlquote "$(getpar $pk)")

4561 cond="$cond${cond:+ and }$pk=$pv"

4562 done

4563 sql="select rowid from $tbl where $cond;"

4564 rowid=`query "$sql"`

4565 ## err smail1 - "$sql" "-> rowid=$rowid"

4566

4567 while IFS=: read prompt name keytype type args; do # Read from $1

4568 val=`getpar $name`

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

4570 text="$text

4571 $prompt

4572 $name=$val

4573 ---------------------------------------------------------"

4574 fi

4575 case "$type" in

4576 image|document|file)

4577 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4578 fns=$(echo "$fn"|while read fn; do

4579 err mv $tmpd/$fn.orig $tmpd/$fn

4580 mv $tmpd/$fn.orig $tmpd/$fn

4581 rm $tmpd/$fn.rowid # Remove cache flag

4582 ## err "`ls $tmpd/$fn`"

4583 echo $fn

4584 done)

4585 files="$files $fns"

4586 ;;

4587 esac

4588 done < $1

4589 ## err FILES=$files "`ls -lF $tmpd`"

4590 subj="from ${REMOTE_ADDR}"

4591 (echo "$url"

4592 echo "への書き込みがありました。"

4593 echo "------"

4594 echo "$text"

4595 ) | (cd $tmpd &&

4596 err LS="`ls -lF`" &&

4597 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4598 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4599 echo "以下の内容で送信しました。" | html p

4600 viewtable $1 $tbl \

4601 `query "select rowid from $tbl order by rowid desc limit 1;"`

4602 echo "戻る" | html a "href=\"?\""

4603 }