s4: 5acef432b1de s4-funcs.sh

s4

view s4-funcs.sh @ 905:5acef432b1de

TD-class delimiter changed to :::

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sun, 03 Jan 2021 14:44:56 +0900
parents	3d437261edca
children	b47f64276427

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"

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

91 # Start debug logging

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

93 exec 3>> $logdir/debug.out

94 err() {

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

96 }

97 case "$HTTP_USER_AGENT" in

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

99 *) touchpanel="" ;;

100 esac

101

102 # If S4MASTERDB is set, behave in another world

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

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

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

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

107 # Variables set in s4-world.sh

108 # $S4WORLDS, $S4WROLDNAME, $S4WORLDGRPS

109 # Files created in s4-world.sh

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

111 fi

112

113

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

115

116 : <<EOF

117

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

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

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

121 レポート提出管理

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

123

124 [2016]

125 7/12 根本への反省

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

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

128

129 [以下2015]

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

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

132 Web

133 締切設定

134

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

136 ○自分の提出物リスト

137

138 7/19 ○設置

139 ○一斉送信

140 ○getparfilename の tmpd の扱い

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

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

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

144 ○添付ファイル回収

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

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

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

148

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

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

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

152 ○レポート提出モード

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

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

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

156

157

158 7/9 ○管理者の追加

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

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

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

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

163

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

165

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

167 ○グループ検索

168

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

170

171

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

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

174

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

176 ○6/1 par2tableを triplex 対応に

177 select "yuuji@gentei.org",var,"text",NULL,val from par where var in (select col from _tblconf where tbl="/user" and keytype in ('p', 's'));

178 →とすると一気に

179

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

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

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

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

184

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

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

187 ## 1. passwd

188 ## 2. grp

189 ## 3. grp_mem

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

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

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

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

194

195 ## ● listの定義:

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

197 ## ● hashの定義:

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

199

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

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

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

203 ## csv2sq3 で .csv.sq3 の Makefile

204

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

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

207 ## type := root | comment

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

209 ## unique(id, filename)

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

211

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

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

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

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

216 ## できたー!

217 ## with recursive allmem as (select * from grp_mem where gname='bar' union all select grp_mem.* from grp_mem,allmem where allmem.name=grp_mem.gname) select * from allmem where type='u';

218 ↓

219 ↓以下に変更

220 with recursive allmem as

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

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

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

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

225

226

227 with recursive allmem as

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

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

230 where allmem.val=grp_m.gname)

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

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

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

234

235

236 ## triggerもできた。

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

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

239

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

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

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

243 ## グループ管理

244 ## 個人情報管理

245 ##

246

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

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

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

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

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

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

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

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

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

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

257

258 ## ログテーブル

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

260

261

262

263 ■表設計

264 * 3つの表に分散管理

265 id格納表 + hash表 + list表

266 * *_s *_m

267

268

269

270 user, user_map, user_col

271

272 ■抽象エントリタイプ

273 * user

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

275 * group

276 権限判定に利用

277 * serial

278 自動idとして機能

279 * password

280 入力 type=passwordで入力

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

282 * session

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

284 * text

285 入力 type=text

286 * textarea

287 入力 textarea

288 * image|document

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

290 * owner

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

292 * gowner

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

294 * timestamp

295 datetime()

296 * parent

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

298 * path

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

300

301 格納タイプ

302 * list

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

304 * hash

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

306

307 オブジェクトタイプ

308 * entry

309 id, title, owner

310 * textpart

311 id, parentID, text

312 * binarypart

313 id, parentID, contenttype, filename, content

314 * content

315 hash(textpart), list(binarypart)

316 * topic

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

318 * reply

319 id, parentID, content

320 * blog

321 list(entry)

322 blog = [topic, list(reply)]

323

324

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

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

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

328 "author" => "taro",

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

330 "parent" => "/",

331 "path" => "/1",

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

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

334 {"serial" => 2,

335 "author" => "hanako",

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

337 "parent" => "/",

338 "path" => "/2",

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

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

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

342

343

344 user:=

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

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

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

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

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

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

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

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

353

354 変換表

355 /user/email=m

356

357 blog:=

358 シリアル:id:p:serial

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

360 所有者:owner:s:owner:

361 時刻:ctime:s:stamp:

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

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

364

365 article:=

366 シリアル:id:p:serial

367 筆者:author:s:owner

368 時刻:ctime:s:stamp:

369 参照元:parent:s:parent:

370 パス:path:s:path:

371 本文:text:s:textarea:

372 画像:image:m:image:

373

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

375

376

377 EOF

378

379 logstart() {

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

381 }

382 logend() {

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

384 }

385 sqlog() {

386 logstart

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

388 cat >> $querylog

389 else

390 echo "$*" >> $querylog

391 fi

392 logend

393 }

394 sq() {

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

396 logstart

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

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

399 else

400 echo "$@" >> $querylog

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

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

403 fi

404 logend

405 }

406 dbsetup() {

407 pipedir=$tmpdir/pipedir

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

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

410 suf=`date +%s`

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

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

413 mkfifo $sqi $sqo

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

415 sq $db < $sqi &

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

417 if $isCGI; then

418 exec 2>> $logdir/error.out

419 fi

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

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

422 rm $sqi

423 # Attach supplemental DB

424 cat >&5 <<-EOF

425 .output /dev/null

426 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

430 .output stdout

431 EOF

432 }

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

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

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

436 fi

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

438 }

439 cleanup() {

440 trap '' INT HUP EXIT TERM PIPE

441 echo .quit >&5

442 kill $sq3pid

443 kill $sq3pid

444 rm -f $sqo $sqi

445 rm -rf $tmpfiles

446 cleanup2

447 }

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

449 # simple redirection for the sake of speed.

450 query() {

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

452 echo ".output $sqo" >&5

453 logstart

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

455 tee -a $querylog

456 else

457 echo "$@" >> $querylog

458 echo "$@"

459 fi >&5

460 echo ".output stdout" >&5

461 cat $sqo

462 rc=$?

463 logend

464 return $rc

465 }

466 _m4() {

467 #S4NAME=f,f,f

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

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

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

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

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

473 }

474 ismember() {

475 # $1=user, $2=group

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

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

478 }

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

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

481 }

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

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

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

485 }

486 isgrpowner() (

487 # $1=user, $2=group

488 gn=`sqlquote "$2"`

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

490 err isgrpowner: $sql

491 test -n "`query $sql`"

492 )

493 isgrpownerbygid() (

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

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

496 err isgrpownerbygid: $sql

497 test -n "`query $sql`"

498 )

499 getgroupadminmails() {

500 # $1=group

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

502 email4group "$1" "$i" ;

503 done

504 }

505 getgroupadmins() { # $1=group

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

507 qgrp=`sqlquote "$1"`

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

509 }

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

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

512 getvalbyid grp $2 \

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

514 }

515 getgroupbyid() {

516 # $1=id|gname

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

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

519 # err ggbyid: `echo $sql`

520 query $sql

521 }

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

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

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

525

526 # Return true when anonymous mode

527 [ "$anonymousmode" ] && return 0

528 # case `getvalbyid blog mode $2` in

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

530 # *closed*)

531 # owner=`getvalbyid blog owner $2`

532 # if isgrp $owner; then

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

534 # elif isuser $owner; then

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

536 # fi

537 # esac

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

539 sql="with getblog as (

540 select key,val from blog_s where id=(

541 select blogid from article where id in

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

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

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

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

546 isgrpadm as (select user from grp_adm where

547 gname=(select val from getowner) and

548 user='$1'),

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

550 select case

551 when (select author from article where

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

553 then 'author'

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

555 then 'open'

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

557 then CASE

558 WHEN (SELECT val FROM isgrp) IS NULL

559 THEN

560 CASE WHEN (SELECT val from getowner)

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

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

563 ELSE ''

564 END

565 WHEN (select user from isgrpadm) IS NOT NULL

566 THEN 'i-am-admin'

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

568 END

569 when (select val from getmode) is null

570 then 'open'

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

572 then (SELECT user FROM isgrpadm)

573 when (select author from article where

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

575 then 'user+author'

576 else '' end;"

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

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

579 # result=`query "$sql"`

580 # err FileAccessibility=$result

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

582 }

583 linkhome() {

584 # $1=UserOrGroupRowid

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

586 if isuser $1; then

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

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

589 name=`gecos $1|htmlescape`

590 else

591 _grid=`numericalize "$1"`

592 echo -n "grp+$1"

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

594 fi

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

596 }

597 hreflink() {

598 # s4 specific notation:

599 # ^href=URL

600 # ^iframe=URL

601 # ^video=URL

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

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

604 # OSM umap Wikistyle Notation:

605 # [[URL]] - Simle Link

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

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

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

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

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

611 # Other Style

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

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

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

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

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

617 # SPC+SPC+$ - <br>

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

619 checkboxON="${cb} checked>"

620 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

645

646 }

647 minitbl() {

648 sed -n '

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

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

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

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

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

654 H; # Concat this line to HoldSpace

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

656 $ b cont

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

658 }

659 :cont

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

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

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

663 # . of ^. is workaround for FreeBSD sed

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

665 p; # Print whole "table" element

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

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

668 }

669 x; # Back to the newest line

670 p; # Print rest' | miniul

671 }

672 miniul() {

673 sed -e '

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

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

676 :top

677 s/\n//;

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

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

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

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

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

683 $ b cont

684 N; # 次の行を読む

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

686 /^ /b top

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

688 p; s/.*//;

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

690 /^\* /b top

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

692 }

693

694 :cont

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

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

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

698 H; x

699 }

700 x' | miniol

701 }

702 miniol() {

703 sed -e '

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

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

706 :top

707 s/\n//;

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

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

710 x;

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

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

713 x;

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

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

716 $ b cont

717 N; # 次の行を読む

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

719 /^ /b top

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

721 p; s/.*//;

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

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

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

725 }

726

727 :cont

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

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

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

731 H; x

732 }

733 x'

734 }

735 acclog() (

736 # $1=table, $2=rowid

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

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

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

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

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

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

743 fi

744 )

745 gecos() (

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

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

748 )

749 setpar() {

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

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

752 case "$1" in

753 user) _user="$v" ;;

754 skey) _skey="$v" ;;

755 esac

756 fi

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

758 }

759 unsetpar() {

760 for i; do

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

762 case "$i" in

763 user|skey) unset _$i ;;

764 esac

765 fi

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

767 done

768 }

769 replpar() {

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

771 }

772 getpar() {

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

774 val=""

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

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

777 user) val=$_user ;;

778 skey) val=$_skey ;;

779 esac

780 fi

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

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

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

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

785 fi

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

787 case "$var" in

788 owner)

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

790 echo $user; return

791 elif ismember $user $val; then

792 echo $val; return

793 fi ;;

794 esac

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

796 echo "$val"

797 }

798 setskey() {

799 # For quick response...(?)

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

801 }

802 chkskey() {

803 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

809 return 0

810 fi

811 return 1

812 }

813 resetskey() {

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

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

816 fi

817 }

818 getpartype() {

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

820 }

821 getparcount() {

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

823 }

824 getparfilename() {

825 # null if type of $1 is not file

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

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

828 }

829 sqlquote() {

830 (v="$1"

831 case "$v" in

832 "") return ;; # null

833 "X'"*) # quoted hex string

834 echo $1 ;;

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

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

837 echo "\"$v\""

838 return ;;

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

840 echo "\"$v\""

841 return ;;

842 *)

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

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

845 else

846 echo "\"$v\""

847 fi ;;

848 esac)

849 }

850 sqlquotestr() (

851 case "$1" in

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

853 echo "'$v'" ;;

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

855 esac

856 )

857 mktempd() {

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

859 }

860 getcachedir() { # $1=maintable

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

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

863 else

864 echo $tmpd/$thumbxy

865 fi

866 }

867 getval() {

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

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

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

871 echo "$user" ;;

872 stamp|datetime)

873 date "+%F %T" ;;

874 serial)

875 (s=`getpar $2`

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

877 *)

878 getpar "$2" "$3";;

879 esac

880 }

881

882 getvalquote() {

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

884 (v=`getval "$@"`

885 case "$v" in

886 "") echo NULL ;;

887 *) sqlquote "$v" ;;

888 esac)

889 }

890 getparquote() {

891 sqlquote "`getpar $1`"

892 }

893 getbinbyid() {

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

895

896 }

897 getvalbyid() {

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

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

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

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

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

903

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

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

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

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

908 return

909 fi

910 done

911 rowid=$3

912 pk=`gettblpkey $1`

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

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

915 td=${4:-$tmpd}

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

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

918 for kt in s m; do

919 t=${1}_$kt

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

921 vcount=1 # count(val)

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

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

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

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

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

927 if [ $kt = m ]; then

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

929 # Reset val to store filenames if type is string

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

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

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

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

934 i=0

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

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

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

938 i=$((i+1))

939 fn=$c.$i

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

941 case $type in

942 file:*)

943 #file=$td/$val

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

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

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

947 # FOR SPEED: Skip file generation if imgcache exists

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

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

950 && continue

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

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

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

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

955 select rowid from $cond $slice;

956 .output '$td/$fn'

957 select val from $cond $slice;

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

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

960 .output stdout

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

962 EOF

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

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

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

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

967 ls -lh "$file" |

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

969 case "$type" in

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

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

972 *|*:[Aa]pplication*)

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

974 png:- > "$file"

975 ;;

976 esac

977 ;;

978 *)

979 sq $db<<EOF

980 .output $td/$fn.rowid

981 select rowid from $cond $slice;

982 .output $td/$fn

983 select val from $cond $slice;

984 EOF

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

986 ;;

987 esac

988 done

989 else

990 rm -f $td/$c.count

991 case $type in

992 file:*)

993 echo "$val" \

994 | while read fn; do

995 file=$td/$fn

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

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

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

999 | unhexize > "$file"

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

1001 case $type in

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

1003 *:[Aa]pplication*)

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

1005 png:- > $file ;;

1006 esac

1007 fi

1008 done

1009 ;;

1010 esac

1011 fi

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

1013 return

1014 fi

1015 done

1016 done)

1017 }

1018 getvalbypkey() (

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

1020 pk=`gettblpkey $1`

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

1022 getvalbyid "$1" "$2" $rowid $4

1023 )

1024 getvalbycond() {

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

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

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

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

1029 getvalbyid "$1" "$2" $rowid "$4"

1030 fi

1031 }

1032 getpwfield() {

1033 # getpwfield user column

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

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

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

1037 echo "$val"

1038 return 0

1039 else

1040 return 1

1041 fi

1042 }

1043 numericalize() {

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

1045 }

1046 encode() {

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

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

1049 sha1=sha1

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

1051 sha1=sha1sum

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

1053 sha1=gsha1sum

1054 fi

1055 fi

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

1057 }

1058 enjpeg() {

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

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

1061 cjpeg="cjpeg"

1062 else

1063 cjpeg="convert - jpeg:-"

1064 fi

1065 fi

1066 $cjpeg "$@"

1067 }

1068 mycrypt() (

1069 key=$1 salt=$2

1070 # err \$2=$2

1071 case $2 in

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

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

1074 esac

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

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

1077 )

1078 hexize() {

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

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

1081 hexize="xxd -p"

1082 else

1083 hexize_hd() {

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

1085 }

1086 hexize="hexize_hd"

1087 fi

1088 fi

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

1090 }

1091 unhexize() {

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

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

1094 unhex="xxd -p -r"

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

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

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

1098 EOF

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

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

1101 # world writable...:)

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

1103 fi

1104 fi

1105 cat "$@" | $unhex

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

1107 }

1108 percenthex() {

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

1110 }

1111 htmlescape() {

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

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

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

1115 }

1116 enascii() {

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

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

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

1120 else

1121 enascii_now=`date +%FT%T`

1122 enascii_sed() {

1123 nkf -Z0Z1Z2 \

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

1125 }

1126 enascii="enascii_sed"

1127 fi

1128 fi

1129 cat "$@" | $enascii

1130 }

1131 size_h() {

1132 i="$1" oi=$1

1133 set -- B B KB MB GB TB

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

1135 oi=$i

1136 i=$((i/1024))

1137 shift

1138 done

1139 echo ${oi}$1

1140 }

1141 gettblconf() {

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

1143 ## tconfs=`sq $db \

1144 tconfs=`query \

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

1146 fi

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

1148 }

1149 gettblkeys() {

1150 # $1=tbl

1151 gettblconf

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

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

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

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

1156 case $type in

1157 *p*)

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

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

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

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

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

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

1164 esac

1165 case $type in

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

1167 esac

1168 done

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

1170 }

1171 gettblpkey() {

1172 # $1=tbl

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

1174 }

1175 gettblfkey() {

1176 (x=`gettblkeys $1`

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

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

1179 }

1180 gettblcols() {

1181 (x=`gettblkeys $1`

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

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

1184 }

1185 gettbl_s_cols() {

1186 (x=`gettblkeys $1`

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

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

1189 }

1190 gettbl_m_cols() {

1191 (x=`gettblkeys $1`

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

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

1194 }

1195 gettbl_h_cols() {

1196 (x=`gettblkeys $1`

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

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

1199 }

1200 gettbl_coltype() (

1201 gettblconf

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

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

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

1205 )

1206 is_hidden() {

1207 # $1=Tbl $2=col

1208 gettblconf

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

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

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

1212 case $x in

1213 *h*) return 0 ;;

1214 *) return 1 ;;

1215 esac

1216 }

1217

1218 dbsetbyid() {

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

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

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

1222 quotedp=$(sqlquotestr "$p")

1223 unset primary update

1224 gettblconf

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

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

1227 #err conf=$conf

1228 case ${conf#*=} in

1229 p*) primary=1 ;;

1230 f*) update=1 ;;

1231 u*) ;;

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

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

1234 esac

1235 #err t=$t

1236 type=string fn=""

1237 case $conf in

1238 */password)

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

1240 ;;

1241 */image*|*/document*)

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

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

1244 ;;

1245 esac

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

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

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

1249 if [ "$primary" ]; then

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

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

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

1253 elif [ "$update" ]; then

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

1255 else

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

1257 fi

1258 )

1259 }

1260 expire() (

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

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

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

1264 )

1265 addsession() {

1266 # expireをセット

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

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

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

1270 # Remove old session parameters

1271 now=`expire now`

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

1273 }

1274 gencookie() (

1275 path=${URL#*:/}

1276 path=${URL%/*}

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

1278 for kv; do

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

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

1281 done

1282 )

1283 contenttype() {

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

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

1286 }

1287 putheader() {

1288

1289 }

1290 putfooter() {

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

1292 }

1293 getcookie() {

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

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

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

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

1298 case "$k" in

1299 user) _user="$v" ;;

1300 skey) _skey="$v" ;;

1301 esac

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

1303 done

1304 }

1305 genrandom() {

1306 # $1=columns (default: 10)

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

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

1309 }

1310 genserial() {

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

1312 }

1313 smail() {

1314 # smail rcpts subj (file)

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

1316 # $MAIL_FROM <- From: header value

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

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

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

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

1321 sender=${SENDER:-$admin}

1322 # Do not call m4 with directly passing text

1323 _r=$tmpd/rcpt

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

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

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

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

1328 }

1329 smail_queue_flush() {

1330 # $1=timelimit

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

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

1333 SELECT rowid FROM smailq

1334 GROUP BY rcpts, subj

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

1336 EOF

1337 )

1338 for rid in $rowids; do

1339 sq $workdb \

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

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

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

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

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

1345

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

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

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

1349 .separator "\n" "------------------\n\n"

1350 SELECT time, text FROM smailq

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

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

1353 ORDER by time;

1354 EOF

1355 then

1356 cat <<-EOF | sq $workdb

1357 DELETE FROM smailq

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

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

1360 EOF

1361 fi

1362 fi

1363 done

1364 }

1365 smail_queue() {

1366 # $1=Rcpts, $@=subj

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

1368 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1377 EOF

1378 )

1379 ## XXX: Adhoc load mitigation

1380 case "$now" in

1381 *[01])

1382 err flush_queue=$mintime

1383 smail_queue_flush ;;

1384 esac

1385 fi

1386 }

1387 setviastring() {

1388 table=par

1389 oifs="$IFS"

1390 IFS="&"

1391 for us in $1; do

1392 k=${us%%=*}

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

1394 setpar "$k" "string" "$v"

1395 done

1396 IFS="$oifs"

1397 }

1398 checkdomain() (

1399 # Check the validity of domain by referring DNS

1400 item=$1

1401 err checkdomain $1

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

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

1404 )

1405 pwcheck() {

1406 # $1=passwd

1407 dbpswd=`getpwfield $user pswd`

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

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

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

1411 }

1412 mypwhash() {

1413 mycrypt "`cat`" `genrandom 5`

1414 }

1415 flag_profupdate() {

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

1417 # This is useful to mitigate account sync load

1418 query <<-EOF

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

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

1421 EOF

1422 touch $userupdateflag

1423 }

1424 wasureta() {

1425 user=$1

1426 if ! checkdomain $user; then

1427 contenttype; echo

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

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

1430 putfooter

1431 exit 0

1432 fi

1433 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1435 encpswd=`echo $newpswd|mypwhash`

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

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

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

1439 $msgdir/mail-newaccount.m4 \

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

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

1442 }

1443 checkauth() {

1444 user=`getpar user`

1445 skc=`getpar skey` # from cookie

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

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

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

1449 if chkskey "$skc"; then

1450 return 0

1451 fi

1452 fi

1453 pswd=`getpar pswd`

1454 quser=`sqlquotestr "$user"`

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

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

1457 return 4 # 4=server too heavy

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

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

1460 return 2 # 2=login fail

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

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

1463 contenttype; echo

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

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

1466 cat <<-EOF | html p

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

1468 EOF

1469 exit 0 # XXXXX more smart...

1470 fi

1471 wasureta "$user"

1472 err wasureta issued for "$user"

1473 return 1 # wasureta error

1474 fi

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

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

1477 if pwcheck "$pswd"; then

1478 newsession=`genrandom 34`

1479 if setskey "$user" "$newsession" &&

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

1481 gencookie "user=$user" "skey=$newsession"

1482 return 0

1483 else

1484 return 4 # Heavy load??

1485 fi

1486 fi

1487 err "Login failed: [$user]"

1488 return 2 # Password mismatch

1489 }

1490 showlogin() {

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

1492 test -z "$args" && resetskey

1493 s4name=${S4NAME:-s4}

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

1495 cat $layout/login.m4.html

1496 echo '</body></html>'

1497 ) | _m4 \

1498 -D_BODYCLASS_="login" \

1499 -D_TITLE_="$s4name" \

1500 -D_SYSNAME_="Welcome to $s4name" \

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

1502 exit 0

1503 }

1504 dologin() {

1505 test -n "$S4WORLD" && syncaccount

1506 checkauth

1507 st=$?

1508 if [ $st != 0 ]; then

1509 contenttype; echo

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

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

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

1513 showlogin # and EXIT

1514 fi

1515 err "Auth OK: [$user]"

1516 }

1517

1518 # Do instant jobs here

1519 dbsetup

1520 trap cleanup INT HUP EXIT TERM PIPE

1521 # trap cleanup INT HUP

1522

1523

1524 cgiinit() {

1525 tmpd=`tmpd=$tmpdir mktempd`

1526 tmpf=$tmpd/stream.$$

1527 tmpfiles=$tmpfiles" $tmpd"

1528 addsession $session

1529 getcookie

1530 case "$REQUEST_METHOD" in

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

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

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

1534 # cat > $tmpf # too much?

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

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

1537 s="`cat $tmpf`"

1538 ;;

1539 esac

1540 case "$CONTENT_TYPE" in

1541 *boundary*)

1542 bndry=${CONTENT_TYPE#*boundary=}

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

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

1545 do

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

1547 #echo u=$us

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

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

1550 # err k=$k v=$v

1551 case "$k" in

1552 *:filename)

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

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

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

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

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

1558 :

1559 fi

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

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

1562 case "$mimetype" in

1563 [Ii]mage/x-xcf)

1564 bzip2 "$tmpd/$v"

1565 v=${v}.bz2

1566 ;;

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

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

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

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

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

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

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

1574 v=$vjpg

1575 else

1576 mimetype="Not supported"

1577 fi

1578 ;;

1579 [Ii]mage/*)

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

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

1582 ;;

1583 esac

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

1585 then

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

1587 continue

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

1589 replpar text string \

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

1591 continue

1592 fi

1593 ;;

1594 image|document|binary)

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

1596 continue ;;

1597 *)

1598 type='string'

1599 ;;

1600 esac

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

1602 setpar "$k" "$type" "$v"

1603 done

1604 ;;

1605 *)

1606 setviastring "$s"

1607 ;;

1608 esac

1609 }

1610 email4group() {

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

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

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

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

1615 sql="WITH

1616 grpemails AS (

1617 SELECT gname, user, val email

1618 FROM grp_mem NATURAL JOIN grp_mem_s

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

1620 useremails AS (

1621 SELECT user.name, val email

1622 FROM user

1623 LEFT JOIN user_m

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

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

1626 FROM useremails u LEFT JOIN grpemails g

1627 ON u.name=g.user

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

1629 query "$sql"

1630 }

1631 email4groupbyuid() {

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

1633 g=$1; shift

1634 uids=`echo "$@"`

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

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

1637 email4group "$g" `query "$sql"`

1638 }

1639 myemail4group() {

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

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

1642 }

1643 collectmembersbyid() {

1644 # Collect user names of group specified by grid

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

1646 query "SELECT user FROM grp_mem \

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

1648 }

1649 collectmembersbyid() {

1650 # Collect user names of group name

1651 qgrp=`sqlquote "$1"`

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

1653 }

1654 collectgecosesbyid() {

1655 # Collect user gecoses of group

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

1657 query<<-EOF

1658 SELECT gecos

1659 FROM gecoses

1660 WHERE name IN (SELECT user FROM grp_mem

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

1662 EOF

1663 }

1664 collectemail() (

1665 # Collect email addresses for group $1

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

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

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

1669 for e; do

1670 if isuser "$e"; then

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

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

1673 else

1674 qgrp=`sqlquote "$e"`

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

1676 gmem="grp_mem"

1677 else

1678 tm=`sqlquote "$TEAM"`

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

1680 fi

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

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

1683 $gmem g left join grp_mem_s s

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

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

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

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

1688 query "$sql"

1689 fi

1690 done

1691 )

1692 sendinvitation() (

1693 # $1=email

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

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

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

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

1698 gecos=`gecos`

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

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

1701 _m4 -D_URL_="$urlbase" \

1702 -D_USER_="$name" \

1703 -D_EMAIL_="$1" \

1704 -D_REGIST_="$regist" \

1705 -D_ADMIN_="$admin" \

1706 $msgdir/mail-invite.m4 \

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

1708 return 0

1709 )

1710 emaildomaincheck() {

1711 case "$1" in

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

1713 *@*)

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

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

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

1717 return 2

1718 fi

1719 return 0

1720 ;;

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

1722 esac

1723 }

1724 invite() {

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

1726 case "$email" in

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

1728 *@*)

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

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

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

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

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

1734 elif sendinvitation $email; then

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

1736 else # Cannot be reached here

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

1738 fi ;;

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

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

1741 esac

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

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

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

1745 fi

1746 if [ -f $invite_policy ]; then

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

1748 else

1749 pol="$invite_policy"

1750 fi

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

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

1753 -D_INVITE_POLICY_="$pol" \

1754 $layout/html.m4.html $layout/invite.m4.html

1755 }

1756 regist() {

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

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

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

1760 echo "bye bye" | html p

1761 reutrn

1762 fi

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

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

1765 cat<<EOF

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

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

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

1769 EOF

1770 return

1771 fi

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

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

1774 # Fake login password to wasureta

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

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

1777 wasureta $email

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

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

1780 addsession $1 # for removal after 1 minute

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

1782 $layout/login.m4.html

1783 return

1784 }

1785 group_safename() {

1786 # Convert $1 to safe group name

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

1788 }

1789 groupupdate() {

1790 gname=`getpar gname`

1791 qgname=`sqlquote "$gname"`

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

1793 # See ALSO same job in showgroup()

1794 newgname=`group_safename "$gname"`

1795 err newgname=$newgname

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

1797 err NewGNAME: gname=$newgname

1798 gname=$newgname

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

1800 replpar gname string "$gname"

1801 fi

1802 # Name confliction check

1803 parow=`getpar rowid`

1804 ## err parow=$parow

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

1806 query "BEGIN EXCLUSIVE;"

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

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

1809 if [ $count -gt 0 ]; then

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

1811 query "END;"

1812 return

1813 fi

1814 par2table $formdir/grp.def

1815 query "END TRANSACTION;"

1816 # Remove orphan

1817 : <<EOF

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

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

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

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

1822 EOF

1823 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1827 sql="delete from blog where id in

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

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

1830 query "$sql";

1831 unsetpar tag kwd

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

1833 return # and return

1834 fi

1835 fi

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

1837 fi

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

1839 grid=$(query $sql)

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

1841 grp $grid

1842 }

1843 groupclone() {

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

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

1846 case "$1" in

1847 */noteam)

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

1849 esac

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

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

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

1853 return

1854 fi

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

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

1857 return

1858 fi

1859 i=0

1860 while true; do

1861 copy="-copy$i"

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

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

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

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

1866 i=$((i++))

1867 continue

1868 fi

1869 break

1870 done

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

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

1873 query<<-EOF

1874 BEGIN;

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

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

1877 SELECT $newqname, key, val

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

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

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

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

1882 -- Copy members and their configuration --

1883 REPLACE INTO grp_mem SELECT $newqname, user

1884 FROM grp_mem WHERE gname=$qgrp;

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

1886 FROM grp_mem_s WHERE gname=$qgrp;

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

1888 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1889 -- Copy administrators --

1890 REPLACE INTO grp_adm SELECT $newqname, user

1891 FROM grp_adm WHERE gname=$qgrp;

1892 COMMIT;

1893 EOF

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

1895 STOPCLONEMSG=1 groupconf "$newrowid"

1896 }

1897 groupman() {

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

1899

1900 GF_STAGE="grpconf"

1901 GF_STAGE=groupupdate

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

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

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

1905 -D_DUMPTABLE_="syscmd(cat)" \

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

1907 }

1908 userconf() {

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

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

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

1912 }

1913 groupconf() {

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

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

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

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

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

1919

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

1921 grp=`getgroupbyid $rowid`

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

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

1924 return

1925 fi

1926

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

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

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

1930 ## Setup migration menu

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

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

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

1934 err v=$v

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

1936 migrate=$(cat<<-EOF

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

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

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

1940 <form action="?migrategrp">

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

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

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

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

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

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

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

1948 `cgi_hidden stage migrategrp`

1949 `cgi_hidden rowid $rowid`

1950 `cgi_submit OK`

1951 `cgi_reset Reset`

1952 </form>

1953 </div>

1954 EOF

1955 )

1956 fi

1957 fi

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

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

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

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

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

1963 <table>

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

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

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

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

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

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

1970 </table>

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

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

1973 </div>

1974 $migrate

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

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

1977 EOF

1978 fi

1979 }

1980 migrategrp() {

1981 rowid=`getpar rowid`

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

1983 grp=`getgroupbyid $rowid`

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

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

1986 return

1987 fi

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

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

1990 grp "$rowid"

1991 return

1992 fi

1993 destconf=`getpar migrateto`

1994 err destconf=$destconf

1995 if [ ! -e $destconf ]; then

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

1997 grp "$rowid"

1998 return

1999 fi

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

2001 srcconf=$worldconf

2002 else

2003 srcconf=s4-config.sh

2004 fi

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

2006 echo "移住操作" | html h1

2007 echo '<pre>'

2008 set -- "$srcconf" "$destconf" "$rowid"

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

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

2011 rc=$?

2012 echo "</pre>"

2013 if [ $rc -eq 0 ]; then

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

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

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

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

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

2019 else

2020 echo "移住失敗" | html p

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

2022 fi

2023 return

2024 }

2025 mems() {

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

2027 kwd=`getpar kwd`

2028 listmember $kwd

2029 }

2030 grps() {

2031 case "$S4WORLD" in

2032 $nonewgroupworld) ;;

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

2034 esac

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

2036 kwd=`getpar kwd`

2037 listgroup "$kwd" \

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

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

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

2041 $layout/form+dump.m4.html

2042 }

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

2044 gpg=`getpar grp`

2045 grid=${1:-$gpg}

2046 grp=`getgroupbyid "$grid"`

2047 ## . ./s4-blog.sh

2048 jg=`getpar joingrp`

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

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

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

2052 fi

2053 htmlheader=$layout/html.m4.html

2054 showgroup "$grid"

2055 }

2056 sql4interestblogs() {

2057 cat<<EOF

2058 CREATE TEMPORARY VIEW interestblogs AS

2059 SELECT blog.rowid rid, id, author

2060 FROM blog

2061 NATURAL JOIN

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

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

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

2065 WHEN (SELECT user FROM grp_mem

2066 WHERE gname=bs.owner AND user='$user') IS NULL

2067 THEN 0

2068 ELSE 1

2069 END;

2070 EOF

2071 }

2072 listnewblogsql() { # $1=user

2073 newdays=${WHATS_NEW_DAYS##*[!0-9]} # Shave non-digits

2074 newdays=${newdays%%[!0-9]*}

2075 newdays=${newdays:-14}

2076 basetime="datetime('now', 'localtime', '-${newdays} days')"

2077 deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog

2078 WHERE user='$user'

2079 AND tblrowid IN

2080 ($blogreadflagrowid,

2081 $blogcutoffflagrowid)),

2082 $basetime -- "0"

2083 );"`

2084 cat<<EOF

2085 `sql4interestblogs`

2086 WITH article_ctime as (

2087 SELECT id,blogid,author,max(val) ctime

2088 FROM article join article_s s using(id)

2089 WHERE s.key='ctime' AND s.val > '$deftime'

2090 GROUP BY id

2091 ), blog_title_owner as (

2092 SELECT blg.rid brid, id,

2093 max(case key when 'title' then val end) title,

2094 max(case key when 'owner' then val end) owner

2095 FROM interestblogs blg, blog_s using(id) group by id

2096 ), blogall as (

2097 SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid

2098 ), news as (

2099 SELECT brid, bl.id blid, bl.title, ctime,

2100 coalesce(al.time, '$deftime') atime,

2101 count(bl.id) "新着", bl.author

2102 FROM blogall bl

2103 LEFT JOIN

2104 (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al

2105 ON bl.brid=al.tblrowid

2106 WHERE atime < bl.ctime

2107 GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id

2108 LIMIT 10

2109 ) SELECT brid LINK, "新着",

2110 (SELECT count(*) FROM article WHERE blogid=blid) "総数",

2111 ctime, title,

2112 (SELECT gecos FROM gecoses WHERE name=author) gecos

2113 FROM news;

2114 EOF

2115 }

2116

2117 search_form() {

2118 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

2119 # $2(optional) = pre-input keywords

2120 help="(1)空白区切りの単語で本文検索

2121 (2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索

2122 @2016-0[1-6] → 2016年1月から6月

2123 @>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間

2124 @week → 最近一週間

2125 (3)#番号で記事ID検索

2126 (1)と(2)は組み合わせOK

2127 例: @2016-10-0[1-9] 芋煮

2128 → 2016年10月上旬でキーワード「芋煮」を含む記事検索

2129 ※クイズ板は検索対象から外されます。"

2130 auth=""

2131 placeholder="全記事からの検索"

2132 case "$1" in

2133 author=*)

2134 a=`echo "${1#author=}"|htmlescape`

2135 g=`gecos ${1#author=}`

2136 auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"

2137 placeholder="このユーザの書込検索"

2138 help="★★ $g さんの書き込みから検索します$nl$help"

2139 ;;

2140 grid=*)

2141 a=`echo "${1#grid=}"`; a=$((0 + $a))

2142 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

2143 placeholder="このグループからの検索"

2144 ;;

2145 esac

2146 inikwd="$2" # no need to htmlescape

2147 cat<<-EOF

2148 <div class="right">

2149 <form action="$myname">$auth

2150 <input type="text" name="kwd" value="$inikwd" title="$help"

2151 placeholder=" $placeholder " width="10" accesskey="k">

2152

2153 ${touchpanel:+<p class="help">$help</p>}

2154 <input type="hidden" name="stage" value="searchart">

2155

2156 </form>

2157 </div>

2158 EOF

2159 }

2160

2161 imgsrc_cache() (

2162 # $1 = directory for cache'ing

2163 # $2 = table (user_m or grp_m)

2164 # $3 = keycond (was: condition for choosingowner)

2165 # $4 = size : S = Small, M = Medium, O = Original

2166 dir="$1" tbl="$2"

2167 keycond="$3"

2168 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

2169 ORDER BY rowid DESC LIMIT 1"

2170 [ -d "$dir" ] || mkdir -p "$dir"

2171 tmpf=$tmpd/imgsrc_cache.$$

2172 case "$4" in

2173 [Ss]) size=S ;;

2174 [Oo]) size=O ;;

2175 *) size=M ;;

2176 esac

2177 # ImageCache filename storing schema:

2178 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

2179 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

2180 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

2181 valtype=`query "$sql0"`

2182 filename=${valtype%%//*}

2183 filetype=${valtype##*//file:}

2184 if [ x"$filename" = x"${filename%.*}" ]; then

2185 # If nor filename extension found, set it to image type

2186 case "$filetype" in

2187 image/*) filename=$filename.${filetype#image/} ;;

2188 esac

2189 fi

2190 cacheimg_S=$dir/S_$filename

2191 cacheimg_M=$dir/M_$filename

2192 cacheimg_O=$dir/$filename

2193 cacheimg=$dir/${size}_$filename

2194 sumfile="$dir/$filename.sum"

2195 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

2196 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2197 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2198 # if cache is fresh and has the same checksum,

2199 echo "<img src=\"$cacheimg\">"

2200 else

2201 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2202 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2203 fifo2=$fifo.2

2204 mkfifo $fifo $fifo2

2205 fmt=${filename##*.}

2206 ## [[ NOTE ]]

2207 ## a. convert oldimage newimage

2208 ## b. convert oldimage fmt:- | convert - newimage

2209 ## b is much smaller than a

2210 cat $tmpf | unhexize \

2211 | tee $fifo \

2212 | convert -define ${fmt}:size=${iconxy_M} \

2213 -resize ${iconxy_M}'>' - ${fmt}:- \

2214 | tee $fifo2 \

2215 | convert - "$cacheimg_M" &

2216 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2217 -resize ${iconxy_S}'>' - ${fmt}:- \

2218 | convert - "$cacheimg_S" &

2219 printf '%s' "<img src=\"data:${filetype},"

2220 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2221 echo '">'

2222 echo "$sum" > $sumfile

2223 fi

2224 ## Now preparing cache image, done.

2225 ## Store this information to DB

2226 stbl=${tbl%_m}_s # user_s or grp_s

2227 pkey=${keycond%%=*} # Primary Key name

2228 pval=${keycond#*=} # Primary Key value

2229 query <<-EOF

2230 REPLACE INTO $stbl($pkey, key, type, val)

2231 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2232 EOF

2233 )

2234

2235 showhome() {

2236 # $1=userRowIdToShow

2237 err showhome \$1=$1

2238 case "$1" in

2239 *@*) uname=`getvalbypkey user name "$1"` ;;

2240 *) uname=`getvalbyid user name $1` ;;

2241 esac

2242 ## err ShowHome: uname=$uname

2243 td=`getcachedir home/"$1"`

2244 gecos=`gecos "$uname"`

2245 ## err SH:gecos=$gecos

2246 GF_VIEWONLY=1

2247 cond="gname in (select gname from grp_mem where user='$uname')"

2248 search_form_args=""

2249 if [ x"$user" = x"$uname" ]; then

2250 if [ -z "$S4MASTERDB" ]; then

2251 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2252 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2253 elif [ -n "$S4MASTERURL" ]; then

2254 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2255 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2256 fi

2257 usermenu="$usermenu

2258 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2259 # Display folders

2260 sql="select count(id) from article_m where id

2261 in (select id from article where author='$user')

2262 and type like 'file:%';"

2263 ## err nfile-sql=`echo "$sql"`

2264 nfile=`query "$sql"`

2265 # err nfile=$nfile

2266 if [ $nfile -gt 0 ]; then

2267 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2268 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2269 fi

2270 else

2271 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2272 GROUP BY user;"`

2273 usermenu="<p>Last seen on $latestlog</p>"

2274 search_form_args="author=$uname"

2275 fi

2276 . ./s4-blog.sh

2277

2278 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2279 search_form "$search_form_args" > $sf

2280 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2281 | htmlescape > $tf

2282 { echo "<div class=\"noprofimg\">"

2283 viewtable $formdir/user.def user $1

2284 echo "</div>"

2285 } > $pf

2286

2287 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2288 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2289 imf=$tmpd/profimg.$$; touch $imf

2290 if [ -n "$img" ]; then

2291 if true; then

2292 tbl=user_m

2293 enticond="name='$uname'"

2294 imgsrc_cache "$td/main" user_m "$enticond" M

2295 else

2296 { printf '%s' "<IMG src=\"data:${img#file:},"

2297 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2298 | sed 's/$..$/%\1/g'

2299 echo '">'

2300 }

2301 fi > $imf

2302 fi

2303 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2304 val='$uname';"`

2305 listblog $uname > $bf

2306

2307 hometail=$tmpd/tail.$$

2308 mkfifo $hometail

2309

2310 #Calling listgroupbytable, originally here

2311

2312 (

2313 # Display Most Recent Entry

2314 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2315 shortval=${shortval:-val}

2316

2317 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2318 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2319 ctime,

2320 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2321 (SELECT gecos FROM gecoses

2322 WHERE name=(SELECT val FROM blog_s

2323 WHERE key='owner' AND id=b.id)) owner,

2324 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2325 FROM blog b

2326 JOIN

2327 (SELECT distinct blogid, a.id aid, max(val) ctime

2328 FROM article a, article_s s

2329 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2330 GROUP BY blogid ORDER BY val DESC LIMIT 50

2331 ) m

2332 ON b.id=m.blogid;"

2333 # This should be as follows

2334 : <<EOF

2335 WITH arts AS(

2336 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2337 a.blogid, a.id id, s.val ctime

2338 FROM article a NATURAL JOIN article_s s

2339 WHERE s.key = 'ctime' AND a.author='$user'

2340 GROUP by s.id

2341 )

2342 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2343 FROM arts a0

2344 JOIN

2345 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2346 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2347 ORDER BY ctime DESC LIMIT 50;

2348 EOF

2349

2350 cat<<-EOF

2351 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2352 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2353 <div class="lcto">

2354 `DT_VIEW=replyblog dumptable html blog`

2355 </div>

2356 EOF

2357 unset DT_SQL

2358 if [ x"$user" = x"$uname" ]; then

2359 # Display NEWS

2360 # 2016-06-26

2361 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2362 acclog blog $blogreadflagrowid

2363 # echo "全部既読にしました" | html p

2364 fi

2365 # 2016-02-19 Counting NEWS without using dumptable.

2366 sql=`listnewblogsql "$user"`

2367 # echo "$sql" > tmp/listnew

2368 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2369 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2370 cont=$((cont-1))

2371 ##err newcount=$cont

2372 if [ $cont -gt 0 ]; then

2373 #echo "全体の新着記事${cont}傑" | html h2

2374 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2375 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2376 cat<<-EOF | html form 'action="?home"'

2377 `cgi_checkbox readchk yes 'id="read"'`<label

2378 for="read">新着ふくめて全部読んだことにする</label>

2379 `cgi_submit '確定'`

2380 `cgi_hidden read yes`

2381 EOF

2382 echo "$new10 "

2383 echo "</div>"

2384 else # If news is 0, set log cut off flag

2385 acclog blog $blogcutoffflagrowid # for speed

2386 fi

2387 else # Not My Home ($user != $uname)

2388 : # DT_SQL=

2389 fi

2390 ) > $hometail & # Is background call safe to m4??

2391 #

2392 listgroupbytable $formdir/grp.def "$cond" $uname |

2393 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2394 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2395 -D_PROFIMG_="spaste(\`$imf')" \

2396 -D_BLOGS_="spaste(\`$bf')" \

2397 -D_SEARCH_="spaste(\`$sf')" \

2398 -D_NBLOG_="$nblog" \

2399 -D_GROUPS_="syscmd(\`cat')" \

2400 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2401 $layout/html.m4.html $layout/home.m4.html

2402

2403 # Record access log

2404 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2405 }

2406 commission() { # $1=grp-rowid $2=user-rowid

2407 contenttype; echo

2408 ## err commission: "$@"

2409 gname=`getgroupbyid $1`

2410 echo "グループ $gname 管理者委任" \

2411 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2412 if [ -n "$2" ]; then

2413 grp_reg_adm "$@"

2414 else

2415 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2416 fi

2417 }

2418 listgroupbytable() {

2419 # $1=deffile $2...=condition $3(optional)=uname

2420 tagline=`grep :tag: $1`;

2421 and="${2:+and }" where=${2:+where }

2422 href="<a href=\"$myname?grp+"

2423 echo '<div class="listgroup">'

2424 sql="select val from grp_s where key='tag' $and$2 group by val;"

2425 ## err ListGRP: query sql="$sql"

2426 for tag in `query "$sql"`

2427 do

2428 ## err ListGrp: tag=$tag

2429 tn=${tagline%%=${tag}*}

2430 tn=${tn##*[ :]}

2431 sql="select rowid||':'||gname as 'グループ名',説明 from

2432 (select (select rowid from grp g where g.gname=grp_s.gname)

2433 as rowid,

2434 gname,

2435 max(case key when 'gecos' then val end) as '説明',

2436 max(case key when 'tag' then val end) as 'tag',

2437 max(case key when 'mtime' then val end) as mtime from grp_s

2438 $where$2 group by gname having tag='$tag' order by mtime desc);"

2439 ## err PersonalGroupList= `echo $sql`

2440 echo "<h2>$tn</h2>"

2441 echo '<table class="b listgroup">'

2442 sq -header -html $db "$sql" \

2443 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2444 echo '</table>'

2445 done

2446 if [ -n "$S4WORLDLIST" -a -n "$3" ]; then

2447 err "peekgrpworlds($user) BEGIN: `gdate +%S.%03N`"

2448 peekgrpworlds mem:"$3"

2449 err "peekgrpworlds($user) END: `gdate +%S.%03N`"

2450 fi

2451 echo '</div>'

2452 }

2453 iconhref() (

2454 # $1=icon-file, $2=Href $3=title $4...=anchor

2455 data=`percenthex "$1"`

2456 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2457 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2458 href=$2; title=$3; shift 3

2459 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2460 )

2461 iconhref2() (

2462 # $1=icon-file, $2=Href $3=title $4...=anchor

2463 src=$1

2464 href=$2; title=$3; shift 3

2465 anchor=`echo $@|htmlescape`

2466 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2467 )

2468 listentry() (

2469 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2470 # Referring variable $iamowner=$grp to attach owner-request links

2471 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2472 cond='' hiddens=''

2473 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2474 if [ -z "$offset" ]; then

2475 offset=`getpar start`; offset=${offset%%[!0-9]*}

2476 offset=$((offset-1))

2477 fi

2478 offset=$((offset + 0)) # change to numeric forcibly

2479 [ $offset -lt 0 ] && offset=0

2480 limit=$listentlimit

2481 dir=`getcachedir "$1"`

2482 if [ x"$1" = x"user" ]; then

2483 hrb="$myname?home"

2484 deficon=person-default.png

2485 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2486 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2487 gcs=gecos

2488 else # if group

2489 hrb="$myname?grp"

2490 deficon=group-default.png

2491 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2492 gcs=name

2493 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2494 if [ -n "$tagline" ]; then

2495 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2496 ## err tagconv=$tagconv

2497 fi

2498 fi

2499 if [ ! -d $dir ]; then

2500 mkdir -p $dir

2501 fi

2502 if [ ! -s $dir/$deficon ]; then

2503 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2504 fi

2505 kwd=${2:-`getpar kwd`}

2506 if [ -n "$kwd" ]; then

2507 kwd=`echo $kwd | tr -d '";\n' | tr -d "'"`

2508 case "$kwd" in

2509 mem:*@*)

2510 byuser=${kwd#*mem:}

2511 qusr=`sqlquote "$byuser"`

2512 cond1="(a.gname IN (SELECT gname FROM grp_mem WHERE user=$qusr))"

2513 ;;

2514 esac

2515 if [ x"$1" = x"group" ]; then

2516 if [ -n "$cond1" ]; then

2517 enthead="`gecos "$byuser"|htmlescape` さんの所属"

2518 else

2519 cond1="(b.name like '%${kwd}%')"

2520 fi

2521 else

2522 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2523 fi

2524 fi

2525 tag=`getpar tag` tag2=`getpar tag2`

2526 if [ x"$tag" = x"NULL" ]; then

2527 tag="" tag2=""

2528 fi

2529 if [ -n "$tag$tag2" ]; then

2530 tag=${tag:-$tag2}

2531 qtag=`sqlquote "$tag"`

2532 cond2="tag=$qtag"

2533 fi

2534 if [ -n "$cond1$cond2" ]; then

2535 cond="$cond1${cond2:+ AND $cond2}"

2536 cond="WHERE ${cond# AND }"

2537 fi

2538

2539 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2540 # grpは呼出し元の動的スコープ変数でよくないな...

2541 ##qgrp=`sqlquote $grp`

2542 getgrp="(select gname from grp where rowid=${rowid:--1})"

2543 sql="select a.rowid, a.$link,

2544 coalesce(b.$gcs, a.$nm) as nick,

2545 quote(a.$nm) as qname,

2546 (SELECT val FROM ${tbl}_s

2547 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2548 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2549 || case when a.$nm in (select gname from grp)

2550 then printf('(%d名)',

2551 (select count(user) from grp_mem where gname=a.$nm))

2552 else ' <'||a.$nm||'>'

2553 end

2554 as name,

2555 b.tag,

2556 case when a.$nm in (select user from grp_adm

2557 where gname=$getgrp) then '管理者'

2558 when '$user' in (select user from grp_adm where gname=a.$nm)

2559 then 'ADMIN'

2560 when '$user' in (select user from grp_mem where gname=a.$nm)

2561 then 'Member'

2562 when '$iamowner' = '' then ''

2563 else ',not='||a.rowid end as ownerlink,

2564 CASE '$entity'

2565 WHEN 'グループ'

2566 THEN coalesce(

2567 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2568 'open')

2569 ||

2570 CASE WHEN '$user'

2571 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2572 THEN ' ismember'

2573 ELSE ''

2574 END

2575 ELSE 'user'

2576 END regmode

2577 from $tbl a left join

2578 (select $nm as name,

2579 max(case key when 'gecos' then val end) as gecos,

2580 max(case key when 'tag' then val end) as tag,

2581 max(case key when 'mtime' then val end) as mtime,

2582 max(case key when 'wtime' then val end) as wtime,

2583 max(case key when 'login' then val end) as login

2584 from ${tbl}_s group by $nm)

2585 b on a.$nm=b.name $cond $3

2586 order by b.wtime desc, b.login desc,

2587 b.mtime desc, b.tag desc, a.rowid asc"

2588 # Give precedence to newer maintained groups (2016-09-24)

2589 # Note that mtime is stored only in grp_s.

2590 ## err LE:sql.1="$sql"

2591 total=`query "with x as ($sql) select count(*) from x;"`

2592 echo "$enthead${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2593 echo '<div class="listentry">' # List-entry div

2594 # Show owner/member filter button

2595 METHOD=GET

2596 hiddens="$hiddens

2597 `cgi_hidden stage \"$stage\"`"

2598 if [ x"$tbl" = x"grp" ]; then

2599 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2600 fh="<select name=\"tag\">$nl"

2601 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2602 for l in $args; do

2603 val=${l#*=} tname=${l%=*}

2604 if [ x"$val" = x"$tag" ]; then

2605 s=" selected"

2606 selectedtags="(種別[${tname}]のみ)"

2607 else

2608 s=""

2609 fi

2610 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2611 done

2612 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2613 cat<<-EOF

2614 <form action="$myname" method="$METHOD">

2615 </form>

2616 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2617 for="ismembtn">参加中以外隠す</label>

2618 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2619 for="isadmbtn">管理者参加以外隠す</label>

2620 EOF

2621 # limit=3

2622 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2623 fi

2624 if [ $total -gt $limit -o $ -n "$S4WORLDLIST" -a x"$tbl" = x"grp" $ ]; then

2625 echo '<div>'

2626 METHOD=GET cgi_form $stage <<EOF

2627 $form

2628 <label>次の語を含む${entity}で検索:

2629 `cgi_text kwd "$kwd"`</label>

2630 $hiddens

2631 EOF

2632 echo '</div>'

2633 else

2634 echo $selectedtags | html p

2635 fi

2636 test -n "$kwd" && hiddens="$hiddens$nl`cgi_hidden kwd \"$kwd\"`"

2637 cat<<EOF

2638 <form action="$myname" method="$METHOD">

2639 <p>${total}件中の<input class="hidesub" type="text" name="start"

2640 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2641 <input type="submit" value="確定"></p>

2642 </form>

2643 EOF

2644 if [ $((offset+limit)) -lt $total ]; then

2645 nextbtn=$(

2646 cat<<EOF

2647 <div class="right clear"><form action="$myname" method="$METHOD">

2648 `cgi_submit 次の${limit}件`

2649 $hiddens

2650 `cgi_hidden offset $((offset + limit))`</form></div>

2651 EOF

2652 )

2653 fi

2654 if [ $offset -gt 0 ]; then

2655 prevbtn=$(

2656 cat<<EOF

2657 <form action="$myname" method="$METHOD">

2658 `cgi_submit 前の${limit}件`

2659 $hiddens

2660 `cgi_hidden offset $((offset - limit))`</form>

2661 EOF

2662 )

2663 fi

2664 pnbtn="$nextbtn$prevbtn"

2665 echo $pnbtn

2666

2667 ## err ListEntry: `echo "$sql"\;`

2668 # sq $db here??? 2016-11-28

2669 query "$sql limit $limit ${offset:+offset $offset};" \

2670 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2671 # err name=$name owner=$ownerp lnk=$lnk

2672 # err newlnk=$lnk regmode=$regmode

2673 icondir=$dir/$id

2674 # Pick up only last icon

2675 htmlname=`echo $name|htmlescape`

2676 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2677 <p class=\"tag _$tag\">$tag</p>" \

2678 | _m4 $tagconv

2679 if [ -n "$NOSPEEDUP" ]; then

2680 files=`getvalbyid $tbl profimg $id $icondir`

2681 if [ -n "$files" ]; then

2682 icon=`echo "$files"|tail -1`

2683 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2684 else

2685 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2686 fi

2687 elif [ -n "$icon" -a -s "$icon" ]; then

2688 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2689 else

2690 cond="$nm=$qname"

2691 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2692 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2693 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2694 # err "img=[$img]"

2695 if [ -n "$img" ]; then

2696 echo "<a href=\"$hrb+$lnk\">"

2697 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2698 echo "</a>"

2699 else

2700 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2701 fi

2702 fi

2703 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2704 echo "</div>"

2705 done

2706 echo "</div>" # End of List-entry div

2707 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2708 if [ -n "$kwd" -a x"$tbl" = x"grp" -a -n "$S4WORLDS" ]; then

2709 peekgrpworlds "$kwd"

2710 fi

2711 )

2712 listmember() {

2713 listentry user "$@"

2714 }

2715 listgroup() {

2716 listentry group "$@"

2717 }

2718 hexteams() { # $1=gname, $2(optional)=user

2719 cond=${2:+" AND user='$2'"}

2720 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2721 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2722 }

2723 showgroup() { # $1=group-rowid

2724 if [ -z "$1" ]; then

2725 grid=`getpar grid`

2726 grid=${grid%%[!0-9]*}

2727 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2728 else

2729 grid=$1

2730 fi

2731 grp=`getgroupbyid $grid`

2732 qgrp=`sqlquote "$grp"`

2733 htmlgrp=`echo "$grp"|htmlescape`

2734 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2735 if isgroup "$grp"; then

2736 tf=$tmpd/title.$$

2737 sf=$tmpd/search.$$

2738 bodyclass=`query "SELECT val FROM grp_s

2739 WHERE gname=$qgrp AND key='regmode';"`

2740 if ismember "$user" "$grp"; then

2741 ismember="ismember"

2742 bodyclass="$bodyclass${bodyclass:+ }ismember"

2743 else

2744 ismember="" # bodyclass="group"

2745 fi

2746 bodyclass="$bodyclass grouphome"

2747 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2748 echo "グループ $htmlgrp" > $tf

2749

2750 showgroupsub $formdir/grp.def "$grid" | \

2751 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2752 -D_FORM_="syscmd(\`cat')" \

2753 -D_BODYCLASS_="$bodyclass" \

2754 -D_DUMPTABLE_="" \

2755 $htmlheader $sf $layout/form+dump.m4.html

2756 # $htmlheader $layout/form+dump.m4.html

2757 # $htmlheader is defined in grp()

2758 else # if $grp is removed at par2table

2759 listgroup

2760 fi

2761 }

2762 showgroupsub() {

2763 # $1=def-file $2=group-rowid

2764 # Using $ismember

2765 rowid=$2

2766 grp=`getgroupbyid $2`

2767 qgrp=`sqlquote "$grp"`

2768 td=`getcachedir grp/"$2"`

2769 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2770 if [ -z "$rowid" ]; then

2771 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2772 #grp=`sq $db "select gname from grp where rowid=$grp"`

2773 echo "showgroupsub: invalid argument($1 $2)" | html p

2774 return

2775 fi

2776 val=`getvalbyid grp profimg $rowid $tmpd`

2777 enticond="gname=$qgrp"

2778 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2779 if [ -n "$img" ]; then

2780 cat<<-EOF

2781 <p class="groupimg">

2782 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2783 EOF

2784 fi

2785 echo "<div class=\"noprofimg\">"

2786 viewtable $1 grp $rowid

2787 echo "</div>"

2788 if isgrpowner "$user" "$grp"; then

2789 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2790 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2791 iamowner=$rowid

2792 colmd=" mode"

2793 fi

2794 if [ -n "$ismember" ]; then

2795 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2796 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2797 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2798 cat<<-EOF

2799 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2800 href="?blog+$rowid">グループの新規話題作成</a>

2801 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2802 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2803 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2804 <div class="fold clear">

2805 `cgi_checkbox send yes id="send"`<label

2806 for="send">グループ全員にメッセージ送信</label>

2807 <div>

2808 `cgi_textarea message "" "cols=60"`

2809 `cgi_submit 送信`

2810 `cgi_reset リセット`

2811 </div>

2812 `cgi_hidden grp $rowid`

2813 </div></form>

2814 EOF

2815 fi

2816 # 加入ボタン + 加入者リスト

2817 if [ -n "$ismember" ]; then

2818 ismem='checked' state="(参加中)"

2819 else

2820 nomem='checked' state="(現在非加入)"

2821 fi

2822 # このグループでの加入アドレス

2823 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2824 and key='email';"`

2825 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2826 ## and key='email';"

2827 ##err email=$eml

2828 cat <<EOF

2829 <div class="fold clear">

2830 `cgi_checkbox reg yes id="reg"`<label

2831 for="reg">自身の加入状態を操作する</label>$state

2832 <div>

2833 EOF

2834 cgi_form grp <<EOF

2835 <p>このグループに</p>

2836 <table class="b">

2837 <tr><th>メンバーとして</th><td>

2838 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2839 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2840 <tr><th>参加する場合のメイルアドレス<br>

2841 <small>(メインのアドレスとは違うものにする場合に記入<br>

2842 同じでよい場合は空欄に)</small></th>

2843 <td>`cgi_text email $eml`</td></tr>

2844 </table>

2845 `cgi_hidden grp $rowid`

2846 EOF

2847 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2848 echo "moderated (承認加入の)グループなので実際に参加できるのは

2849 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2850 fi

2851 echo '</div></div>'

2852 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2853 cat<<-EOF

2854 <div class="fold">

2855 <h2>話題一覧</h2>

2856 <form class="summary inline" action="$myname" title="$thelp">

2857 `cgi_hidden owner "$grp"`

2858 `cgi_hidden kwd "@week"`

2859 `cgi_hidden stage searchart`

2860 `cgi_submit "一週間のまとめ"`

2861 </form>

2862 <input type="checkbox" id="dtcheck" checked><label

2863 for="dtcheck">話題一覧表示</label>

2864 EOF

2865 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by 稼動状態, ctime desc"

2866 colstate="state:稼動状態:frozen=rowclass=凍結"

2867 frzbtn='<button class="toggle-frozen"></button>'

2868 DT_CHLD=article:blogid \

2869 DT_QOWNER="$qgrp" \

2870 DT_VIEW=replyblog dumptable html blog \

2871 "ctime title heading team notify:通知$colmd $colstate" "$cond" \

2872 | if [ -n "$iamowner" ]

2873 then

2874 sed -Ee "s,(<TD class=\"稼動状態\">).*(</TD>),\1$frzbtn\2,"

2875 else

2876 cat

2877 fi

2878 ## DO not convert to frzbtn when not admin

2879 echo "</div> "

2880

2881 getgname="(select gname from grp where rowid=$rowid)"

2882 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2883 cm="?commission+$rowid"

2884 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2885 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2886 # team list

2887 hexteams=`hexteams "$grp"`

2888 if [ -n "$hexteams" ]; then

2889 echo "チーム一覧" | html h2

2890 echo '<div class="dumptable"><table class="b">'

2891 sq $db -html -header<<-EOF

2892 SELECT val TEAM,

2893 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2894 MEMBERS

2895 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2896 EOF

2897 echo '</table></div>'

2898 fi

2899 }

2900 grp_getbodyclass() {

2901 # Get css class name for document.

2902 # `moderated' for moderated groups

2903 # `ismember' for groups where user belongs

2904 # $1=GroupName (w/o quote)

2905 # $user=userNameCurrentlyLogin

2906 ## err grp_getbodyclass: 1="$1"

2907 qgrp=`sqlquote "$1"`

2908 query<<-EOF

2909 SELECT coalesce(

2910 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2911 'open')

2912 ||

2913 CASE WHEN '$user'

2914 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2915 THEN ' ismember'

2916 ELSE ''

2917 END;

2918 EOF

2919 }

2920 grpaction() { # $1=group-rowid

2921 err GRP_ACTION:IN

2922 grid=${1:-`getpar grp`}

2923 grp=`getgroupbyid "$grid"`

2924 htmlgrp=`echo "$grp" | htmlescape`

2925 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2926 if [ -z "$grp" ]; then

2927 echo "無効な指定です。" | html p; return

2928 fi

2929 if ! ismember $user "$grp"; then

2930 echo "加入者のみに許可された操作です。" | html p; return

2931 fi

2932 isowner=""

2933 isgrpowner "$user" "$grp" && isowner="yes"

2934 err 2=$2 3=$3

2935 case "$2" in

2936 get:teamcsv)

2937 teamcsv "$grid"

2938 return

2939 ;;

2940 esac

2941 echo "グループ $grp 個別選択操作" \

2942 | _m4 -D_TITLE_="syscmd(\`cat')" \

2943 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2944 $layout/html.m4.html

2945

2946 usel=`getpar usel`

2947 if [ -n "$usel" ]; then

2948 uids=$(echo `echo $usel`|tr ' ' ',')

2949 ## err grpaction-1: grp=$grp, `echo $sql`

2950 text=`getpar text|tr -d '\r'`

2951

2952 rm=`getpar rm` cfm=`getpar confirm`

2953 ## err rm=$rm cfm=$cfm

2954 if [ x"$rm" = x"yes" ]; then

2955 if [ "$isowner" ]; then

2956 if [ x"$rm$cfm" = x"yesyes" ]; then

2957 # Eliminate

2958 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2959 for tbl in grp_mem grp_mem_s grp_mem_m; do

2960 sql="delete from $tbl $cond;"

2961 # echo "sql=$sql"

2962 query "$sql"

2963 err rmGRPuser "$sql"

2964 done

2965 num=`query "select count(*) from user where rowid in ($uids);"`

2966 #err num=$num

2967 if [ 0$num -gt 0 ]; then

2968 sql="select coalesce(b.val,a.name) from user a left join \

2969 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2970 # err `echo "$sql"`

2971 html pre<<EOF

2972 以下の${num}名のグループ $grp 登録を解除しました。

2973 `query "$sql"`

2974 EOF

2975 fi

2976 else

2977 echo "確認のチェックがないのでやめておきます。" | html p

2978 return

2979 fi

2980 else # not Group Owner

2981 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2982 return

2983 fi

2984 cat<<EOF

2985

2986 EOF

2987 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2988 if [ -z "$text" ]; then # if msg is empty

2989 echo "なにかメッセージを..." | html p

2990 return 0

2991 fi

2992 gecos=`gecos $user`

2993 safegc=`echo "$gecos" | tr -d '<>@,'`

2994 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2995 fromad=`myemail4group "$grp"`

2996 ###mail_from="$safegc <$fromad>"

2997 mail_from="$safegc <$user>" # TEST: 2020/5/13

2998 test -n `getpar sender` &&

2999 export SENDER=$user # TEST: 2020/5/15

3000 replyto=$fromad

3001

3002 ## Start parse of attachment files

3003 if [ -n "`getpar email`" ]; then

3004 ar=`getpar supprcpt`

3005 if [ -n "$ar" ]; then

3006 for a in $ar; do

3007 if checkdomain "$a"; then

3008 supprcpt="$supprcpt $a"

3009 else

3010 err "SupprcptErr=[$a] by $user"

3011 removercpt="$removercpt $a"

3012 fi

3013 done

3014 fi

3015 subj=`getpar subject`

3016 afiles=""

3017 for fn in `query "SELECT DISTINCT val FROM par WHERE var='image' AND sessid='$session';"`

3018 do

3019 f=$tmpd/$fn

3020 if [ -s $f ]; then

3021 afiles=$afiles"${afiles:+ }$f"

3022 fi

3023 done

3024 else

3025 preface=$(cat <<-EOF

3026 $url

3027 のグループ「$grp」のメンバーである $gecos さんから、

3028 あなた宛のメッセージです。

3029 ----------------------------------------------------------

3030 EOF

3031 )

3032 fi

3033 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

3034 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

3035 subj="${subj:-$gecos さんからのメッセージ}"

3036 REPLYTO=$replyto

3037 MAIL_FROM=$mail_from

3038 export REPLYTO SMAIL_TO MAIL_FROM

3039 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

3040 for r in $rcpts; do

3041 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

3042 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

3043 else

3044 # Show sender and rcpts address for guest

3045 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

3046 fi

3047 if [ -n "$afiles" ];then

3048 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

3049 else

3050 smail "$r" "$subj"

3051 fi <<EOF

3052 ${preface:+$preface$nl}$text

3053 EOF

3054 done

3055 if [ $? = 0 ]; then

3056 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

3057 sql="select coalesce(b.val, a.name) from

3058 (select name from user where rowid in ($uids)) a

3059 left join user_s b on a.name=b.name and b.key='gecos';"

3060 html pre<<EOF

3061 `query "$sql"`

3062 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

3063 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

3064 EOF

3065 err SendDone: `echo $sql`

3066 fi

3067 elif [ x"$rm" = x"commission" ]; then

3068 grp_reg_adm $grid $usel

3069 elif [ x"$rm" = x"addteam" ]; then

3070 team=`getpar team|sed "s/'/''/g"` # for single quotation

3071 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

3072 if [ x"$team" != x"$newteam" ]; then

3073 echo "チーム名に使えない文字を除去しました" | html p

3074 team=$newteam

3075 fi

3076 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

3077 cat<<-EOF | html p

3078 有効なチーム名を入力してください。

3079 カンマだけ、「なし」という名前は使えません。

3080 EOF

3081 echo "有効なチーム名を入力してください。" | html p

3082 else

3083 grp_add_team $grid "$team" $usel

3084 fi

3085 elif [ x"$rm" = x"rmteam" ]; then

3086 if [ x"yes" = x"`getpar teamconfirm`" ]; then

3087 rmteam=`getpar rmteam|sed "s/'/''/g"`

3088 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

3089 gname='$grp' AND user='$user' AND key='team'\

3090 AND val='$rmteam';\"`" ]; then

3091 grp_rm_team $grid "$rmteam" $usel

3092 else

3093 echo "所属していないチームの除去操作はできません。"|html p

3094 fi

3095 else

3096 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

3097 fi

3098 fi

3099 fi

3100 # POST count summary

3101 from=`getpar from`; to=`getpar to`

3102 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3103 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3104 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

3105 # New entry

3106 sql="WITH mems AS (

3107 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3108 ON gm.user=g.name

3109 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3110 ), target_article AS (

3111 SELECT id FROM article_s

3112 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3113 ), posts AS (

3114 SELECT author, count(author) post

3115 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3116 WHERE blogid IN (SELECT id FROM blog_s

3117 WHERE key='owner'

3118 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3119 AND key='text'

3120 GROUP BY author

3121 ), teams AS (

3122 SELECT user, group_concat(val, ', ') team

3123 FROM mems m LEFT JOIN grp_mem_m gm ON m.name=gm.user

3124 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3125 AND key='team'

3126 GROUP BY user

3127 ), user_post AS (

3128 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3129 FROM mems m LEFT JOIN posts

3130 ON m.name=posts.author

3131 GROUP by m.rowid

3132 )

3133 SELECT

3134 CASE

3135 WHEN (SELECT user FROM grp_adm

3136 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3137 AND user=up.name) IS NOT NULL

3138 then 'k'

3139 ELSE ''

3140 END || rowid || ','

3141 || rtrim(substr(name, 1, instr(name, '@')), '@') || ','

3142 || gecos NAME,

3143 post POST, team _TEAM_

3144 FROM user_post up LEFT JOIN teams t

3145 ON up.name=t.user

3146 ORDER BY up.name;"

3147 ## err grpaction: "`echo \"$sql\"`"

3148 tf=$tmpd/title.$$

3149 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3150 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3151 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3152 for=\"cmadmin\">管理者委任</label>

3153 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3154 </p></div>"

3155 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3156 title=\"Shortcut: g${nl}Remove from the Group\"

3157 for=\"conf\">GRP登録解除</label>

3158 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3159 <p>この操作による通知は本人に行きません。

3160 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3161 </div>"

3162 # Get team list to which current user belongs into $hexteams

3163 allhexteams=$(hexteams "$grp")

3164 if [ -n "$isowner" ]; then

3165 myhexteams="$allhexteams" # admin can remove all teams' attr

3166 else

3167 myhexteams=$(hexteams "$grp" "$user")

3168 fi

3169 if [ -n "$isowner" -a -n "$allhexteams" ]; then

3170 gettingcsv="<p>Download: <a href=\"?getteamcsv+$grid\">Team.csv</a> (Zoom Breakout Room 事前割り当てに使えます)</p>"

3171 fi

3172 if [ -n "$myhexteams" ]; then

3173 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3174 title=\"Shortcut: s${nl}Strip a team tag from\"

3175 for=\"cmrmteam\">チーム属性除去</label>

3176 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3177 を除去します: `cgi_checkbox teamconfirm yes` 確認

3178 <p>この操作による通知は本人に行きません。

3179 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3180 </div>

3181 "

3182 fi

3183 stf=$tmpd/selteam.$$

3184 cgi_select_h selteam "5445414d" $allhexteams > $stf

3185 b1='<label> <input type="checkbox" name="usel" value="'

3186 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3187 br='<span id="reverse" title="Reverse Selection"></span>'

3188 #b2='"> <span>' b3='</span></label>'

3189 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

3190 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

3191 lnk='"> <span>\5</span></label> [<a href="?home+\3">HOME</a>]'

3192 #-e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

3193 cgi_form grpaction<<EOF \

3194 | sed -E \

3195 -e "s|^(<TR><TD)>(k?)([0-9]*),([^,]+),([^<]*)|\1 title=\"\4\">\2$b1\3$lnk|" \

3196 -e 's/^(<TR><TD[^>]*>)k(<label)/\1\2 class="admin"/' \

3197 -e "s|^(<TR><TH>)(NAME)|\1$br \2|" \

3198 | _m4 -D_TITLE_="spaste(\`$tf')" \

3199 -D_SUBTITLE_="チェック後操作ボタン" \

3200 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3201 $layout/form+dump.m4.html \

3202 | _m4 -D_TEAM_="spaste(\`$stf')"

3203 <p>下でチェックした人を対象として:</p>

3204 <div class="foldtabs">

3205 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3206 title="Shortcut: a${nl}Add a team tag to"

3207 for="cmteam">同じチーム属性を付与</label>

3208 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3209 `cgi_datalist_h teams $allhexteams`

3210 </div>

3211 ${rmteammsg}

3212 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3213 title="Shortcut: d${nl}DirectMail to"

3214 for="sendmsg" title="Direct Message">DM送信</label>

3215 <div>

3216 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3217 title="Using email format">email書式を使う</label>

3218 <div class="folded">

3219 <table>

3220 <tr><td>From: </td><td>$user</td></tr>

3221 <tr><td>このFrom:で送る</td>

3222 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3223 </td></tr>

3224 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3225 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3226 <tr><td>ファイル添付: </td>

3227 <td>`cgi_file image "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3228 </table>

3229 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 送信`)</p>

3230 </div>

3231 <div>本文:`cgi_textarea text "" cols=72`

3232 </div>

3233 </div>

3234 ${isowner:+$cmmsg$excmsg}

3235 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3236 </div>

3237 <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote

3238 <table class="td2r thl">

3239 `sq $db -header -html "$sql"`

3240 </table>

3241 `cgi_hidden grp $grid`

3242 `cgi_hidden myuid $myuid id="myuid"`

3243 EOF

3244 }

3245 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3246 # Create TEMPORARY VIEW

3247 query<<EOF

3248 CREATE TEMPORARY VIEW writeusers AS

3249 SELECT DISTINCT author FROM article

3250 WHERE id in (

3251 select id from article where blogid=(select id from blog where rowid=$1)

3252 );

3253 CREATE TEMPORARY VIEW movablegroups AS

3254 SELECT g.rowid growid , g.gname

3255 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3256 ON grp.gname=gm.gname -- そのユーザが属している

3257 AND user='$user') g -- グループに絞る

3258 WHERE (SELECT author FROM writeusers

3259 EXCEPT

3260 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3261 IS NULL;

3262 $2

3263 EOF

3264 }

3265 sql4readableblogs() {

3266 # Create view of blogs that can be readable to $user

3267 # Blog is readable when:

3268 # 1: blog owner is an user

3269 # 2: else, 2.1: owner-group where the $user belongs

3270 # 2.2: else, owner-group is not moderated

3271 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3272 cat<<EOF ## | tee tmp/sql.out

3273 CREATE TEMPORARY VIEW readableblogs AS

3274 SELECT blog.rowid rid, id, author

3275 FROM blog

3276 NATURAL JOIN

3277 (SELECT id,

3278 max(CASE key WHEN 'owner' THEN val END) owner,

3279 max(CASE key WHEN 'mode' THEN val END) mode

3280 FROM blog_s GROUP by id) bs

3281 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3282 THEN 1 -- blog owner is an user, READABLE

3283 WHEN (SELECT val FROM grp_s

3284 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3285 AND

3286 (SELECT user FROM grp_mem

3287 WHERE gname=bs.owner AND user='$user') IS NULL

3288 THEN 0

3289 WHEN mode IN ('quiz', 'enquete')

3290 THEN 0 -- "quiz" mode blog cannot be searched

3291 ELSE 1

3292 END;

3293 EOF

3294 }

3295 mvteamform() {

3296 owner=$1

3297 hexteams=$(hexteams "$owner" "$user")

3298 test -z "$hexteams" && return

3299 none="`echo なし|hexize`"

3300 cat<<-EOF

3301 <!-- <div class="fold">

3302 `cgi_checkbox mv2team send id="mv2team"`<label

3303 for="mv2team">この話題を以下のチームのものにする</label>

3304 <div> -->

3305 <p>この話題をチーム所有にする:

3306 チーム: `cgi_select_h mv2team $none $hexteams`</p>

3307 </form></div></div>

3308 EOF

3309 }

3310 editheading() { # $1=rowid-of-heading

3311 rowid=${1%%[!A-Z0-9a-z_]*}

3312 if [ -z "$rowid" ]; then

3313 echo "話題番号が未指定です。" | html p

3314 return

3315 fi

3316 owner=`getvalbyid blog owner $rowid`

3317 title=`getvalbyid blog title $rowid`

3318 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3319 | _m4 -D_TITLE_="修正" \

3320 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3321 -D_BLOGS_="" -D_DUMPTABLE_="" \

3322 -D_FORM_="syscmd(\`cat')" \

3323 $layout/html.m4.html $layout/form+dump.m4.html

3324 # Move to group

3325 if isuser "$owner"; then

3326 crview4article $rowid

3327 n=`query "SELECT count(*) FROM writeusers;"`

3328 ## err N=$n

3329 if [ $((n)) -gt 0 ]; then

3330 ## err ROWID=$rowid

3331 sql="SELECT growid || ':' || gname FROM movablegroups;"

3332 cat<<-EOF

3333 <div class="fold">

3334 `cgi_checkbox mv send id="mv"`<label

3335 for="mv">この話題をグループ所有に移動する</label>

3336 <div>

3337 <form action="?mvart" method="POST" enctype="multipart/form-data">

3338 移動先グループ:

3339 <select name="mv2grp">

3340 EOF

3341 query ".mode html"

3342 query<<-EOF |

3343 $sql

3344 .mode list

3345 EOF

3346 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3347 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3348 cat<<-EOF

3349 </select>

3350 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3351 そのグループに加入しているものに限られます)</p>

3352 <p>`cgi_checkbox cfm yes`<label>確認

3353 (この操作は元に戻すことができません)</label></p>

3354 `cgi_hidden blogrowid $rowid`

3355 `cgi_submit 移動`

3356 `cgi_reset Reset`

3357 </form>

3358 </div>

3359 </div>

3360 EOF

3361 fi

3362 # end of isuser "$owner"

3363 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3364 [ -n "$hexteams" ];}; then

3365 none="`echo なし|hexize`"

3366 cat<<-EOF

3367 <div class="fold">

3368 `cgi_checkbox mv2team send id="mv2team"`<label

3369 for="mv2team">この話題を以下のチームのものにする</label>

3370 <div><p>現在の所属チーム設定:

3371 `query "SELECT

3372 coalesce((SELECT val FROM blog_s

3373 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3374 AND key='team'),

3375 ':なし');"`</p>

3376 <form action="?mvart" method="POST" enctype="multipart/form-data">

3377 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3378 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3379 `cgi_hidden blogrowid $rowid`<br>

3380 `cgi_submit 移動`

3381 `cgi_reset Reset`

3382 </form></div></div>

3383 EOF

3384 fi

3385 }

3386 mvart() { # move diary to some group or team

3387 # or move blog of group to team which belong to the group

3388 blogrowid=`getpar blogrowid`

3389 cfm=`getpar cfm`

3390 ##### echo move blog:$blogrowid to $mv2grp | html p

3391 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3392 . ./s4-blog.sh

3393 if [ -z "$blogrowid" ]; then

3394 echo "無効な指定です(mvart)。" | html p

3395 return

3396 elif [ x"$cfm" != x"yes" ]; then

3397 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3398 elif { mv2grp=`getpar mv2grp`

3399 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3400 [ -n "$mv2grp" ]; }; then

3401 crview4article $blogrowid

3402 ########## TRANSACTION BEGIN

3403 query "BEGIN;"

3404 n=`query "SELECT count(*) FROM writeusers;"`

3405 ## err Nwriteuser=$n

3406 if [ $((n)) -gt 0 ]; then

3407 query<<-EOF

3408 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3409 WHERE key='owner'

3410 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3411 AND $mv2grp IN (SELECT growid FROM movablegroups);

3412 EOF

3413 fi

3414 query "END;"

3415 ########## TRANSACTION END

3416 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3417 [ -n "$mv2team" ];}; then

3418 # blog owner can move it to ANY team

3419 case "$mv2team" in

3420 'なし')

3421 cat<<-EOF

3422 DELETE FROM blog_s

3423 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3424 AND key='team';

3425 EOF

3426 ;;

3427 "") ;;

3428 *)cat<<-EOF

3429 BEGIN;

3430 REPLACE INTO blog_s(id, key, val)

3431 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3432 'team', '$mv2team');

3433 REPLACE INTO blog_s(id, key, val)

3434 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3435 'notify', 'all'); -- Change notify to all

3436 END;

3437 EOF

3438 esac | query

3439 fi

3440 blog_reply $blogrowid

3441 echo yes | html p

3442 }

3443 editart() { # $1=article-rowid $2=blogrowid

3444 rowid=${1%%[!A-Z0-9a-z_]*}

3445 blogrowid=${2%%[!A-Z0-9a-z_]*}

3446 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3447 echo "表示する記事番号が未指定です。" | html p

3448 return

3449 fi

3450 owner=`getvalbyid blog owner $blogrowid`

3451 title=`getvalbyid blog title $blogrowid`

3452 author=`getvalbyid article author $rowid`

3453 ## err EDITart: owner=$owner, author=$author

3454 if isgrpowner "$user" "$owner"; then

3455 : EDIT OK

3456 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3457 echo "本人か所有者しか編集できません." | html p

3458 return

3459 fi

3460 aid=`query "select id from article where rowid=$rowid;"`

3461 tmpout=$tmpd/editart.$$.out

3462 GF_ACTION="?replyblog+$blogrowid#$aid" \

3463 edittable $formdir/article.def article $rowid \

3464 > $tmpout

3465 rm -f /tmp/editart.out

3466 # Cannot use pipelining to m4 with genform() because of stdin stack

3467 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3468 -D_FORM_="syscmd(cat $tmpout)" \

3469 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3470 -D_BLOGS_= -D_DUMPTABLE_= \

3471 $layout/html.m4.html $layout/form+dump.m4.html

3472 }

3473 send2mem() {

3474 rowid=`getpar grp`

3475 rowid=${rowid%%[!0-9]*} # Cleaning

3476 if [ -z "$rowid" ]; then

3477 echo "グループが未指定です。" | html p

3478 return

3479 fi

3480 message=`getpar message`

3481 if [ -z "$message" ]; then

3482 echo "文章を入れてください。" | html p

3483 return

3484 fi

3485 grp=`getgroupbyid $rowid`

3486 members=`collectemail "$grp"`

3487 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3488 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3489 mailfrom="`gecos "$user"` <$mailfrom>"

3490 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3491 msg=$(cat<<-EOF

3492 $urlbase?grp+$rowid

3493 グループ $grp に所属する

3494 `gecos $user` さんよりメッセージ:

3495

3496 $message

3497 EOF

3498 )

3499 # smail rcpt subj (file)

3500 for m in $members; do

3501 echo "$msg" |

3502 MAIL_FROM=$mailfrom \

3503 SENDER=$noreply \

3504 REPLYTO=$mailfrom \

3505 smail "$m" "$sj"

3506 done

3507 cat<<EOF

3508 <p>以下のユーザに送信しました。</p>

3509 <pre>

3510 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3511 </pre>

3512 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3513 EOF

3514 }

3515 joingrpadmit() {

3516 # $1=yes/no $2=session-key

3517 if [ -z "$2" ]; then

3518 echo "bye bye" | html p; return

3519 fi

3520 t_usr=`session=$2 getpar adduser`

3521 t_grp=`session=$2 getpar group`

3522 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3523 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3524 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3525 echo "無効な加入依頼です。" | html p

3526 echo "有効期限が切れたか、

3527 他の管理者がいる場合は処理済みの可能性があります。" | html p

3528 return

3529 fi

3530 if ! isgrpowner "$user" "$t_grp"; then

3531 echo "グループ管理者のみの機能です。" | html p; return

3532 fi

3533 case $1 in

3534 yes) joingrp "$t_grp" "$t_usr" yes ;;

3535 no) joingrp "$t_grp" "$t_usr" no ;;

3536 *)

3537 echo "無効な指定です($1)。" | html p

3538 return ;;

3539 esac

3540 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3541 rcpts="`getgroupadminmails "$t_grp"` $user"

3542 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3543 body="に

3544 $t_usr

3545 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3546 しました。"

3547 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3548 query "delete from session where id='$2';"

3549 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3550 }

3551

3552 joingrprequest() {

3553 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3554 jss="joingrp-`date +%s`-`genrandom 12`"

3555 addsession $jss +${memoplimitdays}days

3556 grpadmins=`getgroupadmins "$1"`

3557 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3558 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3559 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3560 $url

3561 $user さんから

3562 グループ $1

3563 に加入依頼がありました。

3564

3565 承認する:

3566 $urlbase?joingrpadmit+yes+$jss

3567

3568 白紙に戻す:

3569 $urlbase?joingrpadmit+no+$jss

3570 EOF

3571 echo "管理者に加入依頼を出しました。

3572 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3573 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3574 }

3575 joingrp() {

3576 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3577 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3578 if isgrpowner "$user" "$1"; then

3579 isowner="yes"

3580 elif [ -n "$5" ]; then

3581 isowner="yes"

3582 else

3583 isowner=""

3584 fi

3585 ## err jg:isgrpowner: isowner="$isowner"

3586 if [ -n "$isowner" ]; then

3587 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3588 elif [ x"$2" != x"$user" ]; then # if user is not login user

3589 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3590 return

3591 elif [ x"$3" = x"no" ]; then

3592 : # Do not pursue those who leave

3593 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3594 : # Member can change own email address for the joining moderated group

3595 else # adding user is $user itself

3596 case `getgroupattr "$1" regmode` in

3597 moderated)

3598 joingrprequest "$@" # Request only

3599 return

3600 ;;

3601 *)

3602 ;;

3603 esac

3604 fi

3605 qgname=`sqlquote "$1"`

3606 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3607 cond="where gname=$qgname and user='$2'"

3608 if [ x"$3" = x"yes" ]; then

3609 query "replace into grp_mem values($qgname, '$2');"

3610 # Notify joingrp to admin

3611 action="に加入しました。"

3612 if [ -n "$4" ]; then

3613 if msg=`emaildomaincheck "$4"`; then

3614 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3615 'string', '$4', NULL);"

3616 else

3617 echo $msg

3618 fi

3619 else

3620 query "delete from grp_mem_s $cond and key='email';"

3621 fi

3622 if [ -n "$5" ]; then # as ADMIN

3623 # Coming here means newly created group

3624 sql="select case\

3625 when (select count(*) from grp_mem where gname=$qgname)=1\

3626 then (select user from grp_mem\

3627 where gname=$qgname and user='$user')\

3628 else '' end; "

3629 err NewGrpChk: $sql

3630 if [ -n "`query \"$sql\"`" ]; then

3631 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3632 query "replace into grp_adm values($qgname, '$user');"

3633 fi

3634 fi

3635 else

3636 query "begin;

3637 delete from grp_mem $cond;

3638 delete from grp_mem_s $cond;

3639 delete from grp_mem_m $cond;

3640 delete from grp_adm $cond;

3641 delete from grp_adm_s $cond;

3642 delete from grp_adm_m $cond;

3643 end;"

3644 action="から脱退しました。"

3645 fi

3646 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3647 $url?grp+$grid

3648 $user (`gecos $user`)さんが

3649 グループ $1

3650 $action

3651 EOF

3652 }

3653 grp_add_team() (

3654 # $1=grp-rowid $2=team $3...=user-rowid(s)

3655 grp=`getgroupbyid $1`

3656 team=$2; shift; shift

3657 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3658 { echo "BEGIN;"

3659 for user; do

3660 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3661 '$grp',\

3662 (SELECT name FROM user WHERE rowid=$user),\

3663 'team', 'string', '$team');"

3664 done

3665 echo "END;"

3666 } | query

3667 )

3668 grp_rm_team() (

3669 # $1=grp-rowid $2=team $3...=user-rowid(s)

3670 grid=$1

3671 qgrp=$(sqlquote "`getgroupbyid $grid`")

3672 team=$2; shift; shift

3673 [ -z "$grid" -o -z "$team" ] && return

3674 { echo "BEGIN;"

3675 for user; do

3676 echo "DELETE FROM grp_mem_m\

3677 WHERE gname=$qgrp \

3678 AND user=(SELECT name FROM user WHERE rowid=$user)\

3679 AND key='team' AND val='$team';"

3680 done

3681 cat<<-EOF

3682 DELETE FROM blog_s

3683 WHERE rowid=(

3684 SELECT rowid

3685 FROM blog_s a

3686 WHERE key='team'

3687 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3688 AND NOT EXISTS (SELECT * FROM grp_mem_m

3689 WHERE key='team' AND val=a.val -- a.val=team

3690 AND gname = (SELECT val FROM blog_s b

3691 WHERE a.id=b.id AND key='owner')

3692 ));

3693 EOF

3694

3695 echo "END;"

3696 } | query

3697 )

3698 grp_reg_adm() {

3699 # $1=grp-rowid $2...=user-rowid

3700 grid=$1

3701 grp=`getgroupbyid "$1"`

3702 if [ -z "$grp" ]; then

3703 echo "無効なグループIDです" | html p; return

3704 fi

3705 if ! isgrpowner "$user" "$grp"; then

3706 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3707 fi

3708 shift

3709 for urid; do

3710 newadm=`query "select name from user where rowid=$urid;"`

3711 if [ -z "$newadm" ]; then

3712 echo "指定ユーザIDがおかしいようです。" | html p; return

3713 fi

3714 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3715 err ismember $newadm $grp

3716 if ismember $newadm "$grp"; then

3717 # OK, go ahead

3718 getgname="(select gname from grp where rowid=$grid)"

3719 query "replace into grp_adm values($getgname, '$newadm');"

3720 # confirm insertion

3721 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3722 if [ -n "`query \"$sql;\"`" ]; then

3723 echo "追加完了: $newadm" | html p

3724 else

3725 echo "追加失敗($1 $urid)" | html p

3726 fi

3727 fi

3728 showgroup $grid

3729 done

3730 }

3731 dt_colhack() {

3732 # FROM: <TD>xxx:::yyy</TD>

3733 # TO: <TD class="xxx">yyy</TD>

3734 sed -Ee 's,<TD>([^:<"]+)'$asdelim'([^<]*)(</TD>|$),<TD class="\1">\2\3,g'

3735 }

3736 dt_rowhack() {

3737 # From: <TR>

3738 # ....

3739 # <TD>rowclass=foo</TD>

3740 # </TR>

3741 # To: <TR class="foo">....<TD>foo</TD></TR>

3742 sed -e '

3743 /^<TR>/ {

3744 :loop

3745 s/\n//

3746 N

3747 /<\/TR>/ {

3748 s/\n//

3749 s,^<TR>$.*$<TD$[^>]*$>rowclass=$.*$$</TD></TR>$,<TR class="\3">\1<TD\2>\3\4,

3750 n

3751 }

3752 $q

3753 b loop

3754 }'

3755 }

3756 dumptable() {

3757 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3758 # textのフィールドだけ全てダンプにしたほうがいいか

3759 # $DT_VIEW sets link

3760 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3761 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3762 VIEW=${DT_VIEW-replyblog}

3763 if [ -n "$VIEW" ]; then

3764 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3765 fi

3766 sqlfile=$tmpd/dump.sql

3767 : > $sqlfile # ensure to be empty

3768 printf '.mode html\n.header 1\n' > $sqlfile

3769 # $DT_CHLD=ChildTable:BindColumn

3770 if [ -n "$DT_CHLD" ]; then

3771 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3772 cat<<-EOF >> $sqlfile

3773 -- presql

3774 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3775 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3776 EOF

3777 # Speed up counting of new articles

3778 cat<<-EOF >> $sqlfile

3779 -- presql2

3780 DROP TABLE IF EXISTS _counts;

3781 CREATE TEMPORARY TABLE _counts AS

3782 SELECT $_i, count($_i) cnt

3783 FROM $_t GROUP BY $_i;

3784 /* Prepare NEW count table */

3785 CREATE TEMPORARY TABLE _target AS

3786 SELECT b.rowid trowid, b.id

3787 FROM "$2" b JOIN "$2_s" s

3788 ON b.id=s.id AND s.key='owner'

3789 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3790

3791 DROP TABLE IF EXISTS _children;

3792 CREATE TEMPORARY TABLE _children AS

3793 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3794 FROM (SELECT t.trowid, t.id $_i, a.id

3795 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3796 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3797

3798 DROP TABLE IF EXISTS _news;

3799 DROP VIEW IF EXISTS _news;

3800

3801 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3802 -- INSERT INTO _news

3803 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3804 CREATE TEMPORARY VIEW _news AS

3805 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3806 FROM (SELECT DISTINCT id FROM _target)

3807 a LEFT JOIN

3808 (SELECT $_i, count(ctime) newcnt

3809 FROM _children x

3810 WHERE ctime > coalesce((SELECT time from myacclog

3811 WHERE tblrowid=x.trowid),

3812 '1970-01-01')

3813 GROUP BY $_i) b

3814 ON a.id=b.$_i;

3815 EOF

3816 # REMOVE next line until 2019/5/1

3817 cntall="(select count($_i) from $_t where $_i=a.id)"

3818 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3819 # REMOVE next assignment until 2019/5/1

3820 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3821 and id in (select id from $_t where $_i=a.id) \

3822 and val > coalesce((select time from myacclog where \

3823 tblrowid=a.rowid),\

3824 '1970-01-01'))"

3825 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3826 cnt="$cntnew as '新着', $cntall as '総数',"

3827 dt_class=" td2r td3r dumpblogs"

3828 fi

3829 # Construct join expression

3830 eav="" scols=""

3831 pk=`gettblpkey $2`

3832 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3833 substr=${substr:-%s}

3834 for col in ${3:-`gettbl_s_cols $2`}; do

3835 valvar=val

3836 fromtbl=b

3837 if gettblcols "$2" | grep -w "$col" >/dev/null 2>&1; then

3838 # If $col belongs to master table

3839 fromtbl=a; col=${col#a.}

3840 fi

3841 case $col in

3842 gecos) scols="$scols${scols:+, }${col#}"

3843 continue ;; # built-in column name

3844 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3845 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3846 case "$as" in

3847 *:*=*) cnd=${as#*:}

3848 h=${cnd%%=*} v=${cnd#*=}

3849 h=`sqlquotestr "$h"`

3850 v=`sqlquotestr "$v"`

3851 if [ x"$fromtbl" = x"b" ]; then

3852 valvar="CASE val WHEN $h THEN $v END"

3853 else

3854 valvar="$h"

3855 fi

3856 as=${as%%:*} ;;

3857 esac

3858 ;;

3859 *) as=${col} ;;

3860 esac

3861 ss=`printf "$substr" "$valvar"`

3862 if [ x"$fromtbl" = x"b" ]; then

3863 eav=$eav${eav:+,}" \"$as$asdelim\"||coalesce(max(case key when '$col' then $ss end), '') as $as"

3864 else

3865 eav=$eav${eav:+,}" \"$as$asdelim\"||$ss as $as"

3866 fi

3867 scols="$scols${scols:+, }${fromtbl}.$as"

3868 done

3869 #case author when '$user' then a.rowid else '---' end as ID,

3870 if [ -n "$DT_SQL" ]; then

3871 echo "$DT_SQL"

3872 else

3873 cat<<-EOF

3874 SELECT a.rowid as LINK, $cnt $scols

3875 FROM $2 a LEFT JOIN

3876 (SELECT $pk,$eav,

3877 max(CASE key

3878 WHEN 'owner'

3879 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3880 as gecos

3881 FROM ${2}_s c GROUP BY $pk)

3882 b ON a.$pk=b.$pk $4;

3883 EOF

3884 fi >> $sqlfile

3885 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3886 sqlog<<-EOF

3887 *** SQL-file: $sqlfile ***

3888 `cat $sqlfile`

3889 EOF

3890 printf '.mode list\n.header 0\n' >> $sqlfile

3891 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_colhack | dt_rowhack

3892 <div>

3893 <div class="dumptable">

3894 <table class="b$dt_class">

3895 `query ".read $sqlfile"`

3896 </table>

3897 </div>

3898 </div>

3899 EOF

3900 }

3901

3902 clean-orphaned() {

3903 # This shoud be done by foreign_key rules, but some db lack them

3904 query<<-EOF

3905 -- Find blogs that have no parent

3906 WITH orphanedblog AS (

3907 SELECT blog.id,val FROM blog JOIN blog_s bs

3908 ON blog.id=bs.id AND key='owner'

3909 WHERE val NOT IN (SELECT gname FROM grp)

3910 AND val NOT IN (SELECT name FROM user)

3911 ) -- Remove them

3912 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

3913

3914 -- Find articles that have no parent blog

3915 WITH orphanedarticle AS (

3916 SELECT id FROM article

3917 WHERE blogid NOT IN (SELECT id FROM blog)

3918 ) -- Remove them

3919 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

3920 EOF

3921 }

3922

3923 clearcachedir() (

3924 td=`getcachedir "$1"`

3925 err td=$td: ls- `ls $td`

3926 if [ -w "$td/image.1" ]; then

3927 err Removing td=$td

3928 rm -fr $td/ # Clear icon-image cache!

3929 fi

3930 )

3931

3932 par2table() (

3933 # copy current parameters of par into destination table

3934 # $1=definition-file

3935 # Using $user and $session

3936 # Return value:

3937 # 0: Stored successfully

3938 # 1: Insufficient fillings

3939 # 2: No permission to modify the record

3940 # 3: Invalid rowid

3941 # 4: SUCCESS to delete

3942 # 5: Stop deletion for lack of confirm check

3943 # 6: Password length too short

3944 # 7: Password mismatch

3945 # 8: Old password incorrect

3946 # 9: Duplicated post

3947 rowid=`getpar rowid`

3948 if [ ! -e $1 ]; then

3949 echo "テーブル定義ファイルが見付かりません" | html p

3950 exit 1

3951 fi

3952 tbl=${1%.def}

3953 tbl=${tbl##*/}

3954 if [ -n "$rowid" ]; then # Modify existing entry

3955 if [ x"$tbl" = x"user" ]; then

3956 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3957 elif [ x"$tbl" = x"grp" ]; then

3958 sql="select gname from $tbl where rowid=$rowid;"

3959 ##err p2t:grp:q $sql

3960 isgrpowner "$user" "`query $sql`" && rowowner=$user

3961 elif [ x"$tbl" = x"blog" ]; then

3962 # Check if owner in blog_s

3963 blogowner=`getvalbyid blog owner "$rowid"`

3964 if isgrpowner "$user" "$blogowner"; then

3965 rowowner=$user

3966 else

3967 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3968 fi

3969 else

3970 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3971 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3972 fi

3973 ### err rowowner=$rowowner

3974 if [ x"$user" != x"$rowowner" ]; then

3975 echo "他人のレコードはいじれないの" | html p

3976 return 2

3977 elif [ -z "$rowowner" ]; then

3978 echo "指定したレコードはないみたい" | html p

3979 return 3

3980 fi

3981 rm=`getpar rm` cfm=`getpar confirm`

3982 # Editing existent entry

3983 if [ x"$rm" = x"yes" ]; then

3984 if [ x"$rm$cfm" = x"yesyes" ]; then

3985 query "delete from $tbl where rowid=$rowid;"

3986 clearcachedir "$tbl/$rowid"

3987 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

3988 clean-orphaned

3989 fi

3990 return 4

3991 else

3992 echo "消去確認のチェックがないので消さなかったの..." | html p

3993 return 5

3994 fi

3995 fi

3996 fi

3997

3998 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3999 if [ -n "$rowid" ]; then

4000 # Update of existing record

4001 for col in `gettblcols $tbl`; do

4002 val=`getparquote $col`

4003 [ -z "$val" ] && continue

4004 ## err query "update $tbl set $col=$val where rowid=$rowid"

4005 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

4006 sql="update $tbl set $col=$val where rowid=$rowid;"

4007 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

4008 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

4009 ## User name cannot be changed with interface provided with this

4010 ## script. But we offer the trigger to change owner user

4011 ## of blog_s table.

4012 #err "select quote($col) from $tbl where rowid=$rowid;"

4013 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

4014 cat<<-EOF | query

4015 -- Here we cannot use BEGIN-COMMIT because groupupdate()

4016 -- should use EXCLUSIVE transaction outside of this.

4017 SAVEPOINT par2table;

4018 $sql

4019 update blog_s set val=$val

4020 where key='owner' and val=$old;

4021 RELEASE SAVEPOINT par2table;

4022 EOF

4023 ## XX: DIRTY Hack Ends here

4024 ## We should keep blog's owner as a single column which has

4025 ## foreign key constraint with primary key of grp/user.

4026 else

4027 query "$sql"

4028 fi

4029 done

4030 # Then, set up $pval for further insertion of tbl_s and tbl_m

4031 for col in `gettblpkey $tbl`; do

4032 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

4033 pval="$pval${pval:+, }\"$val\""

4034 done

4035 else

4036 # New entry

4037 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

4038 # Avoid empty repost of article.

4039 if [ x"$tbl" = x"article" ]; then

4040 # If rowid is empty and ID exists in article-table, that is REPOST!

4041 aid=`getpar id`

4042 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

4043 if [ -n "$xaid" ]; then

4044 # REPOST of article

4045 html p <<-EOF

4046 書き込み直後のリロードなので上書きを回避します。

4047 最新記事は末尾の「再読み込み」ボタンから見てください。

4048 EOF

4049 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

4050 return 9 # STOP Duplicated posting

4051 fi

4052 fi

4053 # Generate values() for primary keys

4054 for col in `gettblpkey $tbl`; do

4055 # Genuine primary keys for _m and _s

4056 val=`getvalquote $tbl $col`

4057 [ -z "$val" ] && continue

4058 pval="$pval${pval:+, }$val"

4059 done

4060 ##err pval=$pval

4061 for col in `gettblfkey $tbl`; do

4062 # args for values() to insertion into master table

4063 val=`getvalquote $tbl $col`

4064 [ -z "$val" ] && continue

4065 formaster=$formaster"${formaster:+, }$val"

4066 done

4067 formaster="$pval${formaster:+, }$formaster"

4068 ## err formaster=$formaster

4069 if [ -z "$formaster" ]; then

4070 echo "項目を全て埋めてください" | html pre

4071 return 1

4072 fi

4073 ## err "replace into $tbl values($formaster);"

4074 query "replace into $tbl values($formaster);"

4075 ## Insertion to master table, done

4076 fi

4077

4078 transaction=$tmpd/sqlfile.sql; touch $transaction

4079 for kt in s m; do

4080 tb2=${tbl}_$kt

4081 for col in `gettbl_${kt}_cols $tbl`; do

4082 ptype=`getpartype $col "limit 1"`

4083

4084 # First, check update of existing entries in _m

4085 if [ $kt = m ]; then

4086 # sessID|address.1.22|string|Somewhere-x.y.z

4087 sql=""

4088 ##err dots from query "select var from par where var like '$col.%';"

4089 for v in `query "select var from par where var like '$col.%' AND sessid='$session';"`; do

4090 # v=address.1.22

4091 st_rowid=${v##*.}

4092 origcol=${v%%.*} # original column derived from

4093 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

4094 ##case `getpartype $v` in

4095 ## err CASE `gettbl_coltype $tbl/$origcol` in

4096 ## err edit flag = `getpar action.$v`

4097 case `getpar action.$v` in

4098 rm)

4099 if [ x`getpar confirm.$v` = x"yes" ]; then

4100 newsql="delete from $tb2"

4101 else

4102 echo "削除確認未チェック" | html p

4103 fi ;;

4104 edit)

4105 case `gettbl_coltype $tbl/$origcol` in

4106 image|document|binary)

4107 file=$tmpd/`getparfilename $v`

4108 if [ ! -s "$file" ]; then # Maybe stupid REPOST

4109 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

4110 continue

4111 fi

4112 ## err type=file=$file

4113 [ -z "$file" ] && continue

4114 bn=`sqlquotestr "${file##*/}"`

4115 bin="X'"$(hexize "$file")"'"

4116 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

4117 type=\"file:$ct\"

4118 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

4119 cachedir=`getcachedir "$tbl/$rowid"`

4120 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

4121 rm -rf $cachedir

4122 ;;

4123 *)

4124 newsql="update $tb2 set val=(select val from par where var \

4125 like '$col.%.$st_rowid' AND sessid='$session')"

4126 ;;

4127 esac

4128 ;;

4129 mv)

4130 # regularize filename and strip directory part

4131 newname=`getpar mv.$v|tr -d '":;#<>?^%$!'|tr -d "'"|tr ' ' _`

4132 newname=`basename $newname`

4133 oldname=`query "SELECT val FROM $tb2 WHERE rowid=$st_rowid;"`

4134 oldext=`expr "$oldname" : '.*\.$.*$'`

4135 newext=`expr "$newname" : '.*\.$.*$'`

4136 err "p2t(mv): oldname=$oldname $oldext -> newname($v)=$newname $newext"

4137 if [ -n "$newname" -a x"$oldext" = x"$newext" ];

4138 then

4139 newsql="UPDATE $tb2 SET val='$newname'"

4140 else

4141 html p<<-EOF

4142 $newname は取り扱えないファイル名です。

4143 空白を含まない名前にして下さい。

4144 拡張子の変更もできません。

4145 EOF

4146 continue

4147 fi

4148 ;;

4149 *) # maybe "keep", do not modify value

4150 continue

4151 ;;

4152 esac

4153 # err newsql=$newsql

4154 sql=$sql$nl"$newsql where rowid=$st_rowid;"

4155 clearcachedir "$tbl/$rowid"

4156 done

4157

4158 if [ x"$bin" = x"NULL" ]; then

4159 ## err repl:normal sql=`echo $sql`

4160 if [ -n "$transaction" ]; then

4161 cat<<-EOF >> $transaction

4162 $sql

4163 DELETE FROM $tb2 WHERE type='string' AND val='';

4164 EOF

4165 else

4166 query "$sql

4167 delete from $tb2 where type='string' and val='';"

4168 ## err repl:normal done

4169 fi

4170 else

4171 # Binary update line is TOO LONG to pipelining

4172 sqlfile="$tmpd/sqlf.$$"

4173 if [ -n "$transaction" ]; then

4174 echo "$sql" >> $transaction

4175 else

4176 echo "$sql" > $sqlfile

4177 query ".read $sqlfile"

4178 fi

4179 fi

4180 # Rest of kt==m: set multiple mode

4181 nr=`getparcount $col`

4182 else

4183 nr=1 # for kt==s, number of records is 1

4184 fi

4185

4186 i=0

4187 while [ $i -lt $nr ]; do

4188 limit="limit 1 offset $i"

4189 i=$((i+1)) # increase beforehand against continue

4190 val=`getvalquote $tbl $col "$limit"`

4191 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4192 ## err $col=$val

4193 bin=NULL

4194 ## err partype$col=`getpartype $col "$limit"`

4195 ptype=`getpartype $col "$limit"` # partype should be obtained each time

4196 case $ptype in

4197 file) file=$tmpd/`getparfilename $col "$limit"`

4198 ## err parfile-$col=$file

4199 [ -z "$file" ] && continue

4200 bin="X'"$(hexize "$file")"'"

4201 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4202 type=\"file:$ct\" ;;

4203 "*"*) continue ;; # foreign table

4204 *) type=\"string\" ;;

4205 esac

4206 case `gettbl_coltype $tbl/$col` in

4207 password) # special care for password

4208 # name={password,pswd1,pswd2}

4209 p1=`getpar pswd1 "$limit"`

4210 if [ -z "$p1" ]; then

4211 continue # SKIP password setting, if p1 is empty

4212 else

4213 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4214 ## err pswd=$pswd

4215 if pwcheck "$pswd"; then

4216 if [ x"$p1" = x"$p2" ]; then

4217 case "$p1" in

4218 ??????????*) ;;

4219 *) echo "パスワードは10字以上にしてください。" | html p

4220 return 6;;

4221 esac

4222 val="\"`echo $p1|mypwhash`\""

4223 else

4224 echo "2つの新パスワード不一致" | html p

4225 return 7

4226 fi

4227 else

4228 echo "旧パスワード違います" | html p

4229 return 8

4230 fi

4231 fi

4232 ;;

4233 esac

4234 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4235 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4236 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4237 if [ x"$bin" = x"NULL" ]; then

4238 ## err Normal-query: `echo $sql`

4239 if [ -n "$transaction" ]; then

4240 echo "$sql" >> $transaction

4241 else

4242 query "$sql"

4243 fi

4244 else

4245 sqlfile="$tmpd/query.$$"

4246 ## err sqlfile=`ls -lF $sqlfile`

4247 if [ -n "$transaction" ]; then

4248 echo "$sql" >> $transaction

4249 else

4250 echo "$sql" > $sqlfile

4251 query ".read $sqlfile"

4252 fi

4253 fi

4254 ## err p2t done

4255 done

4256 done

4257 done

4258 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4259 -- We cannot use transaction here, because groupupdate may use it.

4260 SAVEPOINT pa2table_insert;

4261 .read $transaction

4262 RELEASE SAVEPOINT pa2table_insert;

4263 EOF

4264 rc=$?

4265 [ $rc -eq 0 -a x"$tbl" = x"user" ] && flag_profupdate

4266 ## err "Table:$tbl update done "

4267 return $rc

4268 )

4269 genform() {

4270 # $1 = form definition file

4271 # $2, $3 (optional)= table name and ROWID

4272 # If $GF_VIEWONLY set and nonNull, output values without form

4273 # If $GF_ARGS set, use it as content-strings in the form

4274 # If $GF_OWNER set, use it as value of name="owner"

4275 # If $GF_STAGE set, use it as value of name="stage"

4276 forms="" hiddens="" rowid=$3

4277 if [ ! -e "$1" ]; then

4278 echo "そのようなデータベースはないようです($2)。" | html p

4279 return

4280 elif [ -n "$2" ]; then

4281 rec=`query "select * from $2 where rowid='$rowid';"`

4282 if [ -z "$rec" ]; then

4283 pk=`gettblpkey $2`

4284 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4285 rec=`query "select rowid from $2 where $pk='$rowid';"`

4286 rowid=$rec

4287 rec=$3

4288 fi

4289 if [ -z "$rec" ]; then

4290 echo "そんなレコードはないみたいね..." | html p

4291 return

4292 fi

4293 fi

4294 if [ -z "$GF_VIEWONLY" ]; then

4295 rm='<input id="rm" name="rm" type="checkbox"

4296 value="yes"><label for="rm">このエントリの削除</label>

4297 <span>ほんとうに消しますよ(確認)!

4298 <input name="confirm" type=checkbox value="yes">はい</span>'

4299 fi

4300 # Image Cache dir

4301 ## err genform: getcache=$2/$rowid

4302 td=`getcachedir "$2/$rowid"`

4303 while IFS=: read prompt name keytype type args; do

4304 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4305 sp="${args:+ }"

4306 form="" val=""

4307 if [ -n "$rowid" ]; then

4308 # err genform2a: Seeking for "$2.$name, type=$type"

4309 rawval=`getvalbyid $2 $name $rowid $td`

4310 val=`echo "$rawval"|htmlescape`

4311 ## err genform3a: getvalbyid $2 $name $rowid $td

4312 ## err genform3b: val="[$val]" type="$type"

4313 fi

4314 if [ -n "$GF_VIEWONLY" ]; then

4315 is_hidden "$2" "$name" && continue

4316 fi

4317 case "$type" in

4318 text*)

4319 cgiform=cgi_multi_$type

4320 if [ -s $td/$name.count -a -n "$val" ]; then

4321 form=`$cgiform $name $td`

4322 val=$(echo "$val"|

4323 while read fn; do

4324 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4325 </td></tr>$nl"

4326 done)

4327 val="<table>$nl$val$nl</table>"

4328 else

4329 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4330 form=`cgi_$type $name "$rawval" "$args"`

4331 fi

4332 ;;

4333 [Rr][Aa][Dd][Ii][Oo])

4334 fh="<label><input type=\"radio\" name=\"$name\""

4335 form="`echo $args|sed -e \

4336 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4337 ;;

4338 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4339 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4340 ;;

4341 [Ss][Ee][Ll][Ee][Cc][Tt])

4342 fh="<select name=\"$name\">$nl"

4343 form=$(for l in $args; do

4344 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4345 done)

4346 if [ -n "$val" ]; then

4347 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4348 fi

4349 form="$fh$form</select>"

4350 ;;

4351 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4352 if [ -s $td/$name.count ]; then

4353 form=`cgi_multi_file $name $td "$args"`

4354 if [ -n "$val" ]; then

4355 hrfb="$myname?showattc+$2_m"

4356 val=$(echo "$rawval" \

4357 | while read fn; do

4358 data=`percenthex "$td/$fn"`

4359 #ct=`cat $td/$fn.content-type`

4360 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4361 ri=`cat "$td/$fn.rowid"`

4362 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4363 #imgsrc="<img src=\"data:$ct,$data\">"

4364 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4365 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4366 done)

4367 fi

4368 else

4369 form="<input type=\"file\" name=\"$name\" $args>"

4370 if [ -n "$val" ]; then

4371 imgs=$(echo "$rawval"\

4372 |while read fn;do

4373 data=`percenthex "$td/$fn"`

4374 echo "<img src=\"data:image/png,$data\">$fn<br>"

4375 done)

4376 form=$form"<br>$imgs"

4377 val=$imgs # 2015-06-15

4378 else

4379 form="<input type=\"file\" name=\"$name\" $args>"

4380 fi

4381 fi

4382 ;;

4383 [Hh][Ii][Dd][Dd][Ee][Nn])

4384 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4385 args="value=\"$GF_STAGE\""

4386 fi

4387 form="<input type=\"hidden\" name=\"$name\" $args>"

4388 prompt='' # Remove prompt

4389 ;;

4390 [Aa][Uu][Tt][Hh][Oo][Rr])

4391 [ -n "$GF_VIEWONLY" ] && continue

4392 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4393 prompt="" ;;

4394 [Oo][Ww][Nn][Ee][Rr])

4395 [ -n "$GF_VIEWONLY" ] && continue

4396 val=${GF_OWNER:-$val}

4397 val=${val:-$user}

4398 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4399 prompt="" ;;

4400 [Uu][Ss][Ee][Rr])

4401 # XXX: is null $user ok?

4402 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4403 [ -n "$GF_VIEWONLY" ] && continue

4404 form="$user"

4405 ;;

4406 [Pp]assword)

4407 [ -n "$GF_VIEWONLY" ] && continue

4408 form="`cgi_passwd`"

4409 val=""

4410 ;;

4411 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4412 [ -n "$GF_VIEWONLY" ] && continue

4413 if [ -z "$rowid" ]; then

4414 val=`genserial`

4415 fi

4416 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4417 prompt="" ;;

4418 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4419 prompt=""

4420 ;;

4421 parent|path|blog*)

4422 prompt=""

4423 ;;

4424 "*"*)

4425 tail=$tail"``"

4426 continue ;;

4427 esac

4428 if [ -n "$prompt" ]; then

4429 if [ -n "${GF_VIEWONLY}" ]; then

4430 form=$val

4431 else

4432 :

4433 fi

4434 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4435 else

4436 hiddens=$hiddens$nl"$form"

4437 fi

4438 done < $1

4439 # enctype="multipart/form-data"

4440 cat<<EOF

4441 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4442 ${rowid:+$rm}

4443 <table class="b $2">

4444 $forms

4445 </table>$hiddens

4446 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4447 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4448 EOF

4449 if [ -z $GF_VIEWONLY ]; then

4450 cat<<EOF

4451 <input type="submit" name="sub" value="OK">

4452 <input type="reset" name="res" value="Reset">

4453 EOF

4454 fi

4455 cat<<EOF

4456 $GF_ARGS</form>

4457 $tail

4458 EOF

4459 }

4460 edittable() {

4461 # $1=form-def $2=table $3 rowid

4462 genform "$@"

4463 }

4464 viewtable() {

4465 GF_VIEWONLY=1 genform "$@"

4466 }

4467 showattc() {

4468 # $1=table_m $2=rowid &optional $3=RawFlag

4469 ## err \$1=$1 \$2=$2 \$3=$3

4470 if ! isfilereadable $user $1 $2; then

4471 contenttype; echo

4472 echo "このファイルは管理者のみしか見られません" | html p

4473 putfooter; exit

4474 fi

4475 idir=`umask 002; mktempd` || exit 1

4476 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4477 bin=$idir/$myname-$$.bin

4478 sql="select quote(bin) from $1 where rowid='$2';"

4479 ## err showattc: sql: $sql

4480 sq $db "$sql" | unhexize > $bin

4481 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4482 type=${tv%//*} fn=${tv#*//}

4483 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4484 ct=${type#file:}

4485 case $ct in # all text/* changed to text/plain

4486 text/*|application/csv|application/json)

4487 charset=`nkf -g $bin|cut -d' ' -f1`

4488 case $charset in

4489 ASCII*) charset="" ;;

4490 esac

4491 if [ -z "$3" ]; then

4492 ct="text/html${charset:+; charset=$charset}"

4493 link="?showattc+$1+$2+raw"

4494 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4495 | sed 's,^,<span></span>,' \

4496 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4497 -D_LINK_="$link" \

4498 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4499 exit $?

4500 fi

4501 ct="text/plain${charset:+; charset=$charset}"

4502 ;;

4503 esac

4504 contenttype "$ct"

4505 echo "Content-Disposition: filename=\"$fn\""

4506 echo "Content-Length: " `cat $bin | wc -c`; echo

4507 #echo "Content-Type: " ${type#file:}; echo

4508 cat $bin

4509 }

4510 #

4511 # Some default stupid handler on CGI values

4512 #

4513 default_storedb() {

4514 # ARG: $1=table-def-file

4515 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4516 tbl=`basename $1`

4517 tbl=${tbl%.def}

4518 cols="`grep :text $1|cut -d: -f2`"

4519 col=`echo "$cols"|head -1`

4520 vcol=`getpar $col`

4521 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4522 if [ -n "$vcol" ]; then

4523 par2table $1

4524 else

4525 return 2 # No insertion occurred

4526 fi

4527 }

4528

4529 default_view() { # $1=def-file

4530 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4531 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4532 default_storedb "$@"

4533 query "select rowid from $tbl order by rowid desc;" \

4534 | while read rowid; do

4535 viewtable $1 $tbl $rowid

4536 done | _m4 -D_TITLE_="$tbl" \

4537 -D_FORM_="`genform $1`" \

4538 -D_DUMPTABLE_="syscmd(cat)" \

4539 $layout/html.m4.html $layout/form+dump.m4.html

4540 }

4541 default_viewtext() { # $1=def-file

4542 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4543 default_storedb "$@"

4544 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4545 | _m4 -D_TITLE_="$tbl" \

4546 -D_FORM_="`genform $1`" \

4547 -D_DUMPTABLE_="syscmd(cat)" \

4548 $layout/html.m4.html $layout/form+dump.m4.html

4549 }

4550 default_smail() {

4551 default_storedb "$@"

4552 if [ $? -eq 2 ]; then

4553 _m4 -D_TITLE_="入力" \

4554 -D_FORM_="`genform $1`" \

4555 -D_DUMPTABLE_="" \

4556 $layout/html.m4.html $layout/form+dump.m4.html

4557 return

4558 fi

4559 cond=""

4560 for pk in `gettblpkey $tbl`; do

4561 pv=$(sqlquote "$(getpar $pk)")

4562 cond="$cond${cond:+ and }$pk=$pv"

4563 done

4564 sql="select rowid from $tbl where $cond;"

4565 rowid=`query "$sql"`

4566 ## err smail1 - "$sql" "-> rowid=$rowid"

4567

4568 while IFS=: read prompt name keytype type args; do # Read from $1

4569 val=`getpar $name`

4570 if [ -n "$val" ]; then

4571 text="$text

4572 $prompt

4573 $name=$val

4574 ---------------------------------------------------------"

4575 fi

4576 case "$type" in

4577 image|document|file)

4578 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4579 fns=$(echo "$fn"|while read fn; do

4580 err mv $tmpd/$fn.orig $tmpd/$fn

4581 mv $tmpd/$fn.orig $tmpd/$fn

4582 rm $tmpd/$fn.rowid # Remove cache flag

4583 ## err "`ls $tmpd/$fn`"

4584 echo $fn

4585 done)

4586 files="$files $fns"

4587 ;;

4588 esac

4589 done < $1

4590 ## err FILES=$files "`ls -lF $tmpd`"

4591 subj="from ${REMOTE_ADDR}"

4592 (echo "$url"

4593 echo "への書き込みがありました。"

4594 echo "------"

4595 echo "$text"

4596 ) | (cd $tmpd &&

4597 err LS="`ls -lF`" &&

4598 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4599 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4600 echo "以下の内容で送信しました。" | html p

4601 viewtable $1 $tbl \

4602 `query "select rowid from $tbl order by rowid desc limit 1;"`

4603 echo "戻る" | html a "href=\"?\""

4604 }