s4: 8593ff2579f4 s4-funcs.sh

s4

view s4-funcs.sh @ 883:8593ff2579f4

Ignore empty file-input form

author	HIROSE Yuuji <yuuji@gentei.org>
date	Wed, 16 Dec 2020 09:45:35 +0900
parents	a46e85d50954
children	4c02a9f862d7

line source

1 #!/bin/sh

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

3 # $HGid$

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

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

8 if $isCGI; then

9 case "$SCRIPT_NAME" in

10 *-world-*)

11 S4WORLD=${SCRIPT_NAME#*world-}

12 S4WORLD=${S4WORLD%.*}

13 echo S4WORLD=$S4WORLD >&2

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

15 ;;

16 *)

17 worldconf=s4-config.sh

18 ;;

19 esac

20 echo worldconf=$worldconf >&2

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

22 echo DB=$DB >&2

23 fi

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

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

26 cgiext=${CGIEXT:-.cgi}

27 myargs="$@"

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

29 tmpdir=${TMPDIR:-tmp}

30 dbdir=${DBDIR:-db}

31 logdir=${LOGDIR:-tmp}

32 tmpfiles=""

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

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

35 defaultdb=$dbdir/cgi.sq3

36 db=${DB:-$defaultdb}

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

38 userupdateflag=$dbdir/userupdate

39 sesstb=tmp.sess

40 workdb=$dbdir/tmpdata.sq3

41 listentlimit=${LISTENTLIMIT:-30}

42 listartlimit=${LISTARTLIMIT:-50}

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

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

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

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

47 templ=${TEMPL:-templ}

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

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

50 imgdir=${IMGDIR:-img}

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

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

53 msgdir=$templ/msg

54 timeout="+2 days"

55 memoplimitdays="7"

56 dumpcollen=22

57 #thumbxy=120x120

58 thumbxy=96x96

59 iconxy_S=80x80

60 iconxy_M=400x400

61 maximagexy=1600x1600

62 ### maximagexy=400x400

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

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

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

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

68 file_accept_help="

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

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

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

72 "

73 file_warn="$file_accept_help

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

75 blogreadflagrowid=0

76 blogcutoffflagrowid=-1

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

78 whatsnewdays=${WHATS_NEW_DAYS:-14}

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

80 session=$main_session

82 tconfs=""

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

84 conftbl=_tblconf

85 nl="

86 "

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

88 iconcachekey="profimgcache_S"

90 # Start debug logging

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

92 exec 3>> $logdir/debug.out

93 err() {

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

95 }

96 case "$HTTP_USER_AGENT" in

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

98 *) touchpanel="" ;;

99 esac

100

101 # If S4MASTERDB is set, behave in another world

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

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

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

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

106 # Variables set in s4-world.sh

107 # $S4WORLDS, $S4WROLDNAME, $S4WORLDGRPS

108 # Files created in s4-world.sh

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

110 fi

111

112

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

114

115 : <<EOF

116

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

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

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

120 レポート提出管理

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

122

123 [2016]

124 7/12 根本への反省

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

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

127

128 [以下2015]

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

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

131 Web

132 締切設定

133

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

135 ○自分の提出物リスト

136

137 7/19 ○設置

138 ○一斉送信

139 ○getparfilename の tmpd の扱い

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

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

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

143 ○添付ファイル回収

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

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

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

147

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

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

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

151 ○レポート提出モード

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

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

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

155

156

157 7/9 ○管理者の追加

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

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

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

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

162

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

164

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

166 ○グループ検索

167

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

169

170

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

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

173

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

175 ○6/1 par2tableを triplex 対応に

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

177 →とすると一気に

178

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

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

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

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

183

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

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

186 ## 1. passwd

187 ## 2. grp

188 ## 3. grp_mem

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

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

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

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

193

194 ## ● listの定義:

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

196 ## ● hashの定義:

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

198

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

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

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

202 ## csv2sq3 で .csv.sq3 の Makefile

203

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

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

206 ## type := root | comment

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

208 ## unique(id, filename)

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

210

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

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

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

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

215 ## できたー!

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

217 ↓

218 ↓以下に変更

219 with recursive allmem as

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

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

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

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

224

225

226 with recursive allmem as

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

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

229 where allmem.val=grp_m.gname)

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

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

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

233

234

235 ## triggerもできた。

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

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

238

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

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

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

242 ## グループ管理

243 ## 個人情報管理

244 ##

245

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

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

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

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

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

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

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

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

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

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

256

257 ## ログテーブル

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

259

260

261

262 ■表設計

263 * 3つの表に分散管理

264 id格納表 + hash表 + list表

265 * *_s *_m

266

267

268

269 user, user_map, user_col

270

271 ■抽象エントリタイプ

272 * user

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

274 * group

275 権限判定に利用

276 * serial

277 自動idとして機能

278 * password

279 入力 type=passwordで入力

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

281 * session

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

283 * text

284 入力 type=text

285 * textarea

286 入力 textarea

287 * image|document

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

289 * owner

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

291 * gowner

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

293 * timestamp

294 datetime()

295 * parent

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

297 * path

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

299

300 格納タイプ

301 * list

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

303 * hash

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

305

306 オブジェクトタイプ

307 * entry

308 id, title, owner

309 * textpart

310 id, parentID, text

311 * binarypart

312 id, parentID, contenttype, filename, content

313 * content

314 hash(textpart), list(binarypart)

315 * topic

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

317 * reply

318 id, parentID, content

319 * blog

320 list(entry)

321 blog = [topic, list(reply)]

322

323

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

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

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

327 "author" => "taro",

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

329 "parent" => "/",

330 "path" => "/1",

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

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

333 {"serial" => 2,

334 "author" => "hanako",

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

336 "parent" => "/",

337 "path" => "/2",

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

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

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

341

342

343 user:=

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

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

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

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

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

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

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

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

352

353 変換表

354 /user/email=m

355

356 blog:=

357 シリアル:id:p:serial

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

359 所有者:owner:s:owner:

360 時刻:ctime:s:stamp:

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

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

363

364 article:=

365 シリアル:id:p:serial

366 筆者:author:s:owner

367 時刻:ctime:s:stamp:

368 参照元:parent:s:parent:

369 パス:path:s:path:

370 本文:text:s:textarea:

371 画像:image:m:image:

372

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

374

375

376 EOF

377

378 logstart() {

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

380 }

381 logend() {

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

383 }

384 sqlog() {

385 logstart

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

387 cat >> $querylog

388 else

389 echo "$*" >> $querylog

390 fi

391 logend

392 }

393 sq() {

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

395 logstart

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

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

398 else

399 echo "$@" >> $querylog

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

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

402 fi

403 logend

404 }

405 dbsetup() {

406 pipedir=$tmpdir/pipedir

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

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

409 suf=`date +%s`

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

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

412 mkfifo $sqi $sqo

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

414 sq $db < $sqi &

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

416 if $isCGI; then

417 exec 2>> $logdir/error.out

418 fi

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

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

421 rm $sqi

422 # Attach supplemental DB

423 cat >&5 <<-EOF

424 .output /dev/null

425 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

429 .output stdout

430 EOF

431 }

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

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

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

435 fi

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

437 }

438 cleanup() {

439 trap '' INT HUP EXIT TERM PIPE

440 echo .quit >&5

441 kill $sq3pid

442 kill $sq3pid

443 rm -f $sqo $sqi

444 rm -rf $tmpfiles

445 cleanup2

446 }

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

448 # simple redirection for the sake of speed.

449 query() {

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

451 echo ".output $sqo" >&5

452 logstart

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

454 tee -a $querylog

455 else

456 echo "$@" >> $querylog

457 echo "$@"

458 fi >&5

459 echo ".output stdout" >&5

460 cat $sqo

461 rc=$?

462 logend

463 return $rc

464 }

465 _m4() {

466 #S4NAME=f,f,f

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

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

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

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

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

472 }

473 ismember() {

474 # $1=user, $2=group

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

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

477 }

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

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

480 }

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

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

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

484 }

485 isgrpowner() (

486 # $1=user, $2=group

487 gn=`sqlquote "$2"`

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

489 err isgrpowner: $sql

490 test -n "`query $sql`"

491 )

492 isgrpownerbygid() (

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

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

495 err isgrpownerbygid: $sql

496 test -n "`query $sql`"

497 )

498 getgroupadminmails() {

499 # $1=group

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

501 email4group "$1" "$i" ;

502 done

503 }

504 getgroupadmins() { # $1=group

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

506 qgrp=`sqlquote "$1"`

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

508 }

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

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

511 getvalbyid grp $2 \

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

513 }

514 getgroupbyid() {

515 # $1=id|gname

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

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

518 # err ggbyid: `echo $sql`

519 query $sql

520 }

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

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

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

524

525 # Return true when anonymous mode

526 [ "$anonymousmode" ] && return 0

527 # case `getvalbyid blog mode $2` in

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

529 # *closed*)

530 # owner=`getvalbyid blog owner $2`

531 # if isgrp $owner; then

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

533 # elif isuser $owner; then

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

535 # fi

536 # esac

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

538 sql="with getblog as (

539 select key,val from blog_s where id=(

540 select blogid from article where id in

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

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

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

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

545 isgrpadm as (select user from grp_adm where

546 gname=(select val from getowner) and

547 user='$1'),

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

549 select case

550 when (select author from article where

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

552 then 'author'

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

554 then 'open'

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

556 then CASE

557 WHEN (SELECT val FROM isgrp) IS NULL

558 THEN

559 CASE WHEN (SELECT val from getowner)

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

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

562 ELSE ''

563 END

564 WHEN (select user from isgrpadm) IS NOT NULL

565 THEN 'i-am-admin'

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

567 END

568 when (select val from getmode) is null

569 then 'open'

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

571 then (SELECT user FROM isgrpadm)

572 when (select author from article where

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

574 then 'user+author'

575 else '' end;"

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

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

578 # result=`query "$sql"`

579 # err FileAccessibility=$result

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

581 }

582 linkhome() {

583 # $1=UserOrGroupRowid

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

585 if isuser $1; then

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

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

588 name=`gecos $1|htmlescape`

589 else

590 _grid=`numericalize "$1"`

591 echo -n "grp+$1"

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

593 fi

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

595 }

596 hreflink() {

597 # s4 specific notation:

598 # ^href=URL

599 # ^iframe=URL

600 # ^video=URL

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

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

603 # OSM umap Wikistyle Notation:

604 # [[URL]] - Simle Link

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

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

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

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

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

610 # Other Style

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

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

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

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

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

616 # SPC+SPC+$ - <br>

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

618 checkboxON="${cb} checked>"

619 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

644

645 }

646 minitbl() {

647 sed -n '

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

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

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

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

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

653 H; # Concat this line to HoldSpace

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

655 $ b cont

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

657 }

658 :cont

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

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

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

662 # . of ^. is workaround for FreeBSD sed

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

664 p; # Print whole "table" element

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

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

667 }

668 x; # Back to the newest line

669 p; # Print rest' | miniul

670 }

671 miniul() {

672 sed -e '

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

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

675 :top

676 s/\n//;

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

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

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

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

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

682 $ b cont

683 N; # 次の行を読む

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

685 /^ /b top

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

687 p; s/.*//;

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

689 /^\* /b top

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

691 }

692

693 :cont

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

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

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

697 H; x

698 }

699 x' | miniol

700 }

701 miniol() {

702 sed -e '

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

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

705 :top

706 s/\n//;

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

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

709 x;

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

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

712 x;

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

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

715 $ b cont

716 N; # 次の行を読む

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

718 /^ /b top

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

720 p; s/.*//;

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

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

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

724 }

725

726 :cont

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

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

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

730 H; x

731 }

732 x'

733 }

734 acclog() (

735 # $1=table, $2=rowid

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

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

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

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

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

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

742 fi

743 )

744 gecos() (

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

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

747 )

748 setpar() {

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

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

751 case "$1" in

752 user) _user="$v" ;;

753 skey) _skey="$v" ;;

754 esac

755 fi

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

757 }

758 unsetpar() {

759 for i; do

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

761 case "$i" in

762 user|skey) unset _$i ;;

763 esac

764 fi

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

766 done

767 }

768 replpar() {

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

770 }

771 getpar() {

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

773 val=""

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

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

776 user) val=$_user ;;

777 skey) val=$_skey ;;

778 esac

779 fi

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

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

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

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

784 fi

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

786 case "$var" in

787 owner)

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

789 echo $user; return

790 elif ismember $user $val; then

791 echo $val; return

792 fi ;;

793 esac

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

795 echo "$val"

796 }

797 setskey() {

798 # For quick response...(?)

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

800 }

801 chkskey() {

802 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

808 return 0

809 fi

810 return 1

811 }

812 resetskey() {

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

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

815 fi

816 }

817 getpartype() {

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

819 }

820 getparcount() {

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

822 }

823 getparfilename() {

824 # null if type of $1 is not file

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

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

827 }

828 sqlquote() {

829 (v="$1"

830 case "$v" in

831 "") return ;; # null

832 "X'"*) # quoted hex string

833 echo $1 ;;

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

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

836 echo "\"$v\""

837 return ;;

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

839 echo "\"$v\""

840 return ;;

841 *)

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

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

844 else

845 echo "\"$v\""

846 fi ;;

847 esac)

848 }

849 sqlquotestr() (

850 case "$1" in

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

852 echo "'$v'" ;;

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

854 esac

855 )

856 mktempd() {

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

858 }

859 getcachedir() { # $1=maintable

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

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

862 else

863 echo $tmpd/$thumbxy

864 fi

865 }

866 getval() {

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

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

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

870 echo "$user" ;;

871 stamp|datetime)

872 date "+%F %T" ;;

873 serial)

874 (s=`getpar $2`

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

876 *)

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

878 esac

879 }

880

881 getvalquote() {

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

883 (v=`getval "$@"`

884 case "$v" in

885 "") echo NULL ;;

886 *) sqlquote "$v" ;;

887 esac)

888 }

889 getparquote() {

890 sqlquote "`getpar $1`"

891 }

892 getbinbyid() {

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

894

895 }

896 getvalbyid() {

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

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

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

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

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

902

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

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

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

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

907 return

908 fi

909 done

910 rowid=$3

911 pk=`gettblpkey $1`

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

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

914 td=${4:-$tmpd}

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

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

917 for kt in s m; do

918 t=${1}_$kt

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

920 vcount=1 # count(val)

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

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

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

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

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

926 if [ $kt = m ]; then

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

928 # Reset val to store filenames if type is string

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

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

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

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

933 i=0

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

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

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

937 i=$((i+1))

938 fn=$c.$i

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

940 case $type in

941 file:*)

942 #file=$td/$val

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

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

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

946 # FOR SPEED: Skip file generation if imgcache exists

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

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

949 && continue

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

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

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

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

954 select rowid from $cond $slice;

955 .output '$td/$fn'

956 select val from $cond $slice;

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

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

959 .output stdout

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

961 EOF

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

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

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

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

966 ls -lh "$file" |

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

968 case "$type" in

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

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

971 *|*:[Aa]pplication*)

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

973 png:- > "$file"

974 ;;

975 esac

976 ;;

977 *)

978 sq $db<<EOF

979 .output $td/$fn.rowid

980 select rowid from $cond $slice;

981 .output $td/$fn

982 select val from $cond $slice;

983 EOF

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

985 ;;

986 esac

987 done

988 else

989 rm -f $td/$c.count

990 case $type in

991 file:*)

992 echo "$val" \

993 | while read fn; do

994 file=$td/$fn

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

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

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

998 | unhexize > "$file"

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

1000 case $type in

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

1002 *:[Aa]pplication*)

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

1004 png:- > $file ;;

1005 esac

1006 fi

1007 done

1008 ;;

1009 esac

1010 fi

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

1012 return

1013 fi

1014 done

1015 done)

1016 }

1017 getvalbypkey() (

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

1019 pk=`gettblpkey $1`

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

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

1022 )

1023 getvalbycond() {

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

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

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

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

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

1029 fi

1030 }

1031 getpwfield() {

1032 # getpwfield user column

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

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

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

1036 echo "$val"

1037 return 0

1038 else

1039 return 1

1040 fi

1041 }

1042 numericalize() {

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

1044 }

1045 encode() {

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

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

1048 sha1=sha1

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

1050 sha1=sha1sum

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

1052 sha1=gsha1sum

1053 fi

1054 fi

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

1056 }

1057 enjpeg() {

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

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

1060 cjpeg="cjpeg"

1061 else

1062 cjpeg="convert - jpeg:-"

1063 fi

1064 fi

1065 $cjpeg "$@"

1066 }

1067 mycrypt() (

1068 key=$1 salt=$2

1069 # err \$2=$2

1070 case $2 in

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

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

1073 esac

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

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

1076 )

1077 hexize() {

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

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

1080 hexize="xxd -p"

1081 else

1082 hexize_hd() {

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

1084 }

1085 hexize="hexize_hd"

1086 fi

1087 fi

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

1089 }

1090 unhexize() {

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

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

1093 unhex="xxd -p -r"

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

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

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

1097 EOF

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

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

1100 # world writable...:)

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

1102 fi

1103 fi

1104 cat "$@" | $unhex

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

1106 }

1107 percenthex() {

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

1109 }

1110 htmlescape() {

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

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

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

1114 }

1115 enascii() {

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

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

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

1119 else

1120 enascii_now=`date +%FT%T`

1121 enascii_sed() {

1122 nkf -Z0Z1Z2 \

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

1124 }

1125 enascii="enascii_sed"

1126 fi

1127 fi

1128 cat "$@" | $enascii

1129 }

1130 size_h() {

1131 i="$1" oi=$1

1132 set -- B B KB MB GB TB

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

1134 oi=$i

1135 i=$((i/1024))

1136 shift

1137 done

1138 echo ${oi}$1

1139 }

1140 gettblconf() {

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

1142 ## tconfs=`sq $db \

1143 tconfs=`query \

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

1145 fi

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

1147 }

1148 gettblkeys() {

1149 # $1=tbl

1150 gettblconf

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

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

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

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

1155 case $type in

1156 *p*)

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

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

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

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

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

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

1163 esac

1164 case $type in

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

1166 esac

1167 done

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

1169 }

1170 gettblpkey() {

1171 # $1=tbl

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

1173 }

1174 gettblfkey() {

1175 (x=`gettblkeys $1`

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

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

1178 }

1179 gettblcols() {

1180 (x=`gettblkeys $1`

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

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

1183 }

1184 gettbl_s_cols() {

1185 (x=`gettblkeys $1`

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

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

1188 }

1189 gettbl_m_cols() {

1190 (x=`gettblkeys $1`

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

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

1193 }

1194 gettbl_h_cols() {

1195 (x=`gettblkeys $1`

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

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

1198 }

1199 gettbl_coltype() (

1200 gettblconf

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

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

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

1204 )

1205 is_hidden() {

1206 # $1=Tbl $2=col

1207 gettblconf

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

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

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

1211 case $x in

1212 *h*) return 0 ;;

1213 *) return 1 ;;

1214 esac

1215 }

1216

1217 dbsetbyid() {

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

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

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

1221 quotedp=$(sqlquotestr "$p")

1222 unset primary update

1223 gettblconf

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

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

1226 #err conf=$conf

1227 case ${conf#*=} in

1228 p*) primary=1 ;;

1229 f*) update=1 ;;

1230 u*) ;;

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

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

1233 esac

1234 #err t=$t

1235 type=string fn=""

1236 case $conf in

1237 */password)

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

1239 ;;

1240 */image*|*/document*)

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

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

1243 ;;

1244 esac

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

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

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

1248 if [ "$primary" ]; then

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

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

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

1252 elif [ "$update" ]; then

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

1254 else

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

1256 fi

1257 )

1258 }

1259 expire() (

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

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

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

1263 )

1264 addsession() {

1265 # expireをセット

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

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

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

1269 # Remove old session parameters

1270 now=`expire now`

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

1272 }

1273 gencookie() (

1274 path=${URL#*:/}

1275 path=${URL%/*}

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

1277 for kv; do

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

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

1280 done

1281 )

1282 contenttype() {

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

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

1285 }

1286 putheader() {

1287

1288 }

1289 putfooter() {

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

1291 }

1292 getcookie() {

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

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

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

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

1297 case "$k" in

1298 user) _user="$v" ;;

1299 skey) _skey="$v" ;;

1300 esac

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

1302 done

1303 }

1304 genrandom() {

1305 # $1=columns (default: 10)

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

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

1308 }

1309 genserial() {

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

1311 }

1312 smail() {

1313 # smail rcpts subj (file)

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

1315 # $MAIL_FROM <- From: header value

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

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

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

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

1320 sender=${SENDER:-$admin}

1321 # Do not call m4 with directly passing text

1322 _r=$tmpd/rcpt

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

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

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

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

1327 }

1328 smail_queue_flush() {

1329 # $1=timelimit

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

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

1332 SELECT rowid FROM smailq

1333 GROUP BY rcpts, subj

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

1335 EOF

1336 )

1337 for rid in $rowids; do

1338 sq $workdb \

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

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

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

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

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

1344

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

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

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

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

1349 SELECT time, text FROM smailq

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

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

1352 ORDER by time;

1353 EOF

1354 then

1355 cat <<-EOF | sq $workdb

1356 DELETE FROM smailq

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

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

1359 EOF

1360 fi

1361 fi

1362 done

1363 }

1364 smail_queue() {

1365 # $1=Rcpts, $@=subj

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

1367 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1376 EOF

1377 )

1378 ## XXX: Adhoc load mitigation

1379 case "$now" in

1380 *[01])

1381 err flush_queue=$mintime

1382 smail_queue_flush ;;

1383 esac

1384 fi

1385 }

1386 setviastring() {

1387 table=par

1388 oifs="$IFS"

1389 IFS="&"

1390 for us in $1; do

1391 k=${us%%=*}

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

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

1394 done

1395 IFS="$oifs"

1396 }

1397 checkdomain() (

1398 # Check the validity of domain by referring DNS

1399 item=$1

1400 err checkdomain $1

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

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

1403 )

1404 pwcheck() {

1405 # $1=passwd

1406 dbpswd=`getpwfield $user pswd`

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

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

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

1410 }

1411 mypwhash() {

1412 mycrypt "`cat`" `genrandom 5`

1413 }

1414 wasureta() {

1415 user=$1

1416 if ! checkdomain $user; then

1417 contenttype; echo

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

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

1420 putfooter

1421 exit 0

1422 fi

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

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

1425 encpswd=`echo $newpswd|mypwhash`

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

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

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

1429 $msgdir/mail-newaccount.m4 \

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

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

1432 }

1433 checkauth() {

1434 user=`getpar user`

1435 skc=`getpar skey` # from cookie

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

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

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

1439 if chkskey "$skc"; then

1440 return 0

1441 fi

1442 fi

1443 pswd=`getpar pswd`

1444 quser=`sqlquotestr "$user"`

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

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

1447 return 4 # 4=server too heavy

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

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

1450 return 2 # 2=login fail

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

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

1453 contenttype; echo

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

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

1456 cat <<-EOF | html p

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

1458 EOF

1459 exit 0 # XXXXX more smart...

1460 fi

1461 wasureta "$user"

1462 err wasureta issued for "$user"

1463 return 1 # wasureta error

1464 fi

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

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

1467 if pwcheck "$pswd"; then

1468 newsession=`genrandom 34`

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

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

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

1472 return 0

1473 else

1474 return 4 # Heavy load??

1475 fi

1476 fi

1477 err "Login failed: [$user]"

1478 return 2 # Password mismatch

1479 }

1480 showlogin() {

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

1482 test -z "$args" && resetskey

1483 s4name=${S4NAME:-s4}

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

1485 cat $layout/login.m4.html

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

1487 ) | _m4 \

1488 -D_BODYCLASS_="login" \

1489 -D_TITLE_="$s4name" \

1490 -D_SYSNAME_="Welcome to $s4name" \

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

1492 exit 0

1493 }

1494 dologin() {

1495 test -n "$S4WORLD" && syncaccount

1496 checkauth

1497 st=$?

1498 if [ $st != 0 ]; then

1499 contenttype; echo

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

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

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

1503 showlogin # and EXIT

1504 fi

1505 err "Auth OK: [$user]"

1506 }

1507

1508 # Do instant jobs here

1509 dbsetup

1510 trap cleanup INT HUP EXIT TERM PIPE

1511 # trap cleanup INT HUP

1512

1513

1514 cgiinit() {

1515 tmpd=`tmpd=$tmpdir mktempd`

1516 tmpf=$tmpd/stream.$$

1517 tmpfiles=$tmpfiles" $tmpd"

1518 addsession $session

1519 getcookie

1520 case "$REQUEST_METHOD" in

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

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

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

1524 # cat > $tmpf # too much?

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

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

1527 s="`cat $tmpf`"

1528 ;;

1529 esac

1530 case "$CONTENT_TYPE" in

1531 *boundary*)

1532 bndry=${CONTENT_TYPE#*boundary=}

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

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

1535 do

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

1537 #echo u=$us

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

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

1540 # err k=$k v=$v

1541 case "$k" in

1542 *:filename)

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

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

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

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

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

1548 :

1549 fi

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

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

1552 case "$mimetype" in

1553 [Ii]mage/x-xcf)

1554 bzip2 "$tmpd/$v"

1555 v=${v}.bz2

1556 ;;

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

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

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

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

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

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

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

1564 v=$vjpg

1565 else

1566 mimetype="Not supported"

1567 fi

1568 ;;

1569 [Ii]mage/*)

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

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

1572 ;;

1573 esac

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

1575 then

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

1577 continue

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

1579 replpar text string \

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

1581 continue

1582 fi

1583 ;;

1584 image|document|binary)

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

1586 continue ;;

1587 *)

1588 type='string'

1589 ;;

1590 esac

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

1592 setpar "$k" "$type" "$v"

1593 done

1594 ;;

1595 *)

1596 setviastring "$s"

1597 ;;

1598 esac

1599 }

1600 email4group() {

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

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

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

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

1605 sql="WITH

1606 grpemails AS (

1607 SELECT gname, user, val email

1608 FROM grp_mem NATURAL JOIN grp_mem_s

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

1610 useremails AS (

1611 SELECT user.name, val email

1612 FROM user

1613 LEFT JOIN user_m

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

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

1616 FROM useremails u LEFT JOIN grpemails g

1617 ON u.name=g.user

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

1619 query "$sql"

1620 }

1621 email4groupbyuid() {

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

1623 g=$1; shift

1624 uids=`echo "$@"`

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

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

1627 email4group "$g" `query "$sql"`

1628 }

1629 myemail4group() {

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

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

1632 }

1633 collectmembersbyid() {

1634 # Collect user names of group specified by grid

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

1636 query "SELECT user FROM grp_mem \

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

1638 }

1639 collectmembersbyid() {

1640 # Collect user names of group name

1641 qgrp=`sqlquote "$1"`

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

1643 }

1644 collectgecosesbyid() {

1645 # Collect user gecoses of group

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

1647 query<<-EOF

1648 SELECT gecos

1649 FROM gecoses

1650 WHERE name IN (SELECT user FROM grp_mem

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

1652 EOF

1653 }

1654 collectemail() (

1655 # Collect email addresses for group $1

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

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

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

1659 for e; do

1660 if isuser "$e"; then

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

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

1663 else

1664 qgrp=`sqlquote "$e"`

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

1666 gmem="grp_mem"

1667 else

1668 tm=`sqlquote "$TEAM"`

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

1670 fi

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

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

1673 $gmem g left join grp_mem_s s

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

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

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

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

1678 query "$sql"

1679 fi

1680 done

1681 )

1682 sendinvitation() (

1683 # $1=email

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

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

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

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

1688 gecos=`gecos`

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

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

1691 _m4 -D_URL_="$urlbase" \

1692 -D_USER_="$name" \

1693 -D_EMAIL_="$1" \

1694 -D_REGIST_="$regist" \

1695 -D_ADMIN_="$admin" \

1696 $msgdir/mail-invite.m4 \

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

1698 return 0

1699 )

1700 emaildomaincheck() {

1701 case "$1" in

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

1703 *@*)

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

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

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

1707 return 2

1708 fi

1709 return 0

1710 ;;

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

1712 esac

1713 }

1714 invite() {

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

1716 case "$email" in

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

1718 *@*)

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

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

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

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

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

1724 elif sendinvitation $email; then

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

1726 else # Cannot be reached here

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

1728 fi ;;

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

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

1731 esac

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

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

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

1735 fi

1736 if [ -f $invite_policy ]; then

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

1738 else

1739 pol="$invite_policy"

1740 fi

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

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

1743 -D_INVITE_POLICY_="$pol" \

1744 $layout/html.m4.html $layout/invite.m4.html

1745 }

1746 regist() {

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

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

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

1750 echo "bye bye" | html p

1751 reutrn

1752 fi

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

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

1755 cat<<EOF

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

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

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

1759 EOF

1760 return

1761 fi

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

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

1764 # Fake login password to wasureta

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

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

1767 wasureta $email

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

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

1770 addsession $1 # for removal after 1 minute

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

1772 $layout/login.m4.html

1773 return

1774 }

1775 group_safename() {

1776 # Convert $1 to safe group name

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

1778 }

1779 groupupdate() {

1780 gname=`getpar gname`

1781 qgname=`sqlquote "$gname"`

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

1783 # See ALSO same job in showgroup()

1784 newgname=`group_safename "$gname"`

1785 err newgname=$newgname

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

1787 err NewGNAME: gname=$newgname

1788 gname=$newgname

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

1790 replpar gname string "$gname"

1791 fi

1792 # Name confliction check

1793 parow=`getpar rowid`

1794 ## err parow=$parow

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

1796 query "BEGIN EXCLUSIVE;"

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

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

1799 if [ $count -gt 0 ]; then

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

1801 query "END;"

1802 return

1803 fi

1804 par2table $formdir/grp.def

1805 query "END TRANSACTION;"

1806 # Remove orphan

1807 : <<EOF

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

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

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

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

1812 EOF

1813 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1817 sql="delete from blog where id in

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

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

1820 query "$sql";

1821 unsetpar tag kwd

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

1823 return # and return

1824 fi

1825 fi

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

1827 fi

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

1829 grid=$(query $sql)

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

1831 grp $grid

1832 }

1833 groupclone() {

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

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

1836 case "$1" in

1837 */noteam)

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

1839 esac

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

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

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

1843 return

1844 fi

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

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

1847 return

1848 fi

1849 i=0

1850 while true; do

1851 copy="-copy$i"

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

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

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

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

1856 i=$((i++))

1857 continue

1858 fi

1859 break

1860 done

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

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

1863 query<<-EOF

1864 BEGIN;

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

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

1867 SELECT $newqname, key, val

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

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

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

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

1872 -- Copy members and their configuration --

1873 REPLACE INTO grp_mem SELECT $newqname, user

1874 FROM grp_mem WHERE gname=$qgrp;

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

1876 FROM grp_mem_s WHERE gname=$qgrp;

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

1878 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1879 -- Copy administrators --

1880 REPLACE INTO grp_adm SELECT $newqname, user

1881 FROM grp_adm WHERE gname=$qgrp;

1882 COMMIT;

1883 EOF

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

1885 STOPCLONEMSG=1 groupconf "$newrowid"

1886 }

1887 groupman() {

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

1889

1890 GF_STAGE="grpconf"

1891 GF_STAGE=groupupdate

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

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

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

1895 -D_DUMPTABLE_="syscmd(cat)" \

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

1897 }

1898 userconf() {

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

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

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

1902 }

1903 groupconf() {

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

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

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

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

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

1909

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

1911 grp=`getgroupbyid $rowid`

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

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

1914 return

1915 fi

1916

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

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

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

1920 ## Setup migration menu

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

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

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

1924 err v=$v

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

1926 migrate=$(cat<<-EOF

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

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

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

1930 <form action="?migrategrp">

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

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

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

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

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

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

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

1938 `cgi_hidden stage migrategrp`

1939 `cgi_hidden rowid $rowid`

1940 `cgi_submit OK`

1941 `cgi_reset Reset`

1942 </form>

1943 </div>

1944 EOF

1945 )

1946 fi

1947 fi

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

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

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

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

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

1953 <table>

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

1955 <button>クローン作成(チームも複製)</button></a></td>

1956 <td><p>(チームなどもそのままで掲示板なしの状態から)</p></td></tr>

1957 <tr><td><a href="?groupclone+$rowid/noteam">

1958 <button>作成(チームなし)</button></a></td>

1959 <td>(チームは引き継がずメンバーのみ同じグループを作成)</td></tr>

1960 </table>

1961 <p>ボタンを押すと即作成します。不要な場合はグループ編集で

1962 削除してください。</p>

1963 </div>

1964 $migrate

1965 `cgi_radio grpaction close id="x"`<label for="x" accesskey="x">×</label>

1966 <div style="height: $height; background: transparent;"></div>

1967 EOF

1968 fi

1969 }

1970 migrategrp() {

1971 rowid=`getpar rowid`

1972 rowid=${rowid%%[!0-9]*}

1973 grp=`getgroupbyid $rowid`

1974 if ! isgrpowner "$user" "$grp"; then

1975 echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"

1976 return

1977 fi

1978 if [ x`getpar emichk` != x"yes" ]; then

1979 echo "移住確認未チェックなので中止します。" | html p

1980 grp "$rowid"

1981 return

1982 fi

1983 destconf=`getpar migrateto`

1984 err destconf=$destconf

1985 if [ ! -e $destconf ]; then

1986 echo "移住先Worldが認識できないので中止します($destconf)。" | html p

1987 grp "$rowid"

1988 return

1989 fi

1990 if [ -n "$worldconf" ]; then

1991 srcconf=$worldconf

1992 else

1993 srcconf=s4-config.sh

1994 fi

1995 _m4 -D_TITLE_="移住操作" -D_BODYCLASS_="" $layout/html.m4.html

1996 echo "移住操作" | html h1

1997 echo '<pre>'

1998 set -- "$srcconf" "$destconf" "$rowid"

1999 err ./s4-migrate.sh "$srcconf" "$destconf" "$rowid"

2000 . ./s4-migrate.sh # Dot(.) sourcing might not pass arguments

2001 rc=$?

2002 echo "</pre>"

2003 if [ $rc -eq 0 ]; then

2004 echo "World [$world] への移住完了。" | html p

2005 echo "<p><a href=\"$dsturl?grp+$destrowid\">移住先</a></p>"

2006 elif [ -n "$faillist" ]; then

2007 echo "移住後削除失敗" | html p

2008 echo "空いている時間帯に再度試して下さい。" | html p

2009 else

2010 echo "移住失敗" | html p

2011 echo "移動先に重複がないか確認して下さい。" | html p

2012 fi

2013 return

2014 }

2015 mems() {

2016 _m4 -D_TITLE_="参加者一覧" -D_BODYCLASS_=listmember $layout/html.m4.html

2017 kwd=`getpar kwd`

2018 listmember $kwd

2019 }

2020 grps() {

2021 case "$S4WORLD" in

2022 $nonewgroupworld) ;;

2023 *) LINK_NEWGRP="<a href=\"?groupman\">新規グループ作成</a>" ;;

2024 esac

2025 _m4 -D_TITLE_="グループ一覧" -D_BODYCLASS_=listgroup $layout/html.m4.html

2026 kwd=`getpar kwd`

2027 listgroup "$kwd" \

2028 | _m4 -D_DUMPTABLE_="syscmd(cat)" \

2029 -D_TITLE_="グループ関連操作" \

2030 -D_FORM_="${LINK_NEWGRP}${NEWGRP_GUIDE}" \

2031 $layout/form+dump.m4.html

2032 }

2033 grp() { # $1=group-rowid

2034 gpg=`getpar grp`

2035 grid=${1:-$gpg}

2036 grp=`getgroupbyid "$grid"`

2037 ## . ./s4-blog.sh

2038 jg=`getpar joingrp`

2039 if [ -n "$jg" ]; then

2040 [ -n "$jg" -a -n "$grp" ] &&

2041 joingrp "$grp" "$user" "$jg" "`getpar email`"

2042 fi

2043 htmlheader=$layout/html.m4.html

2044 showgroup "$grid"

2045 }

2046 sql4interestblogs() {

2047 cat<<EOF

2048 CREATE TEMPORARY VIEW interestblogs AS

2049 SELECT blog.rowid rid, id, author

2050 FROM blog

2051 NATURAL JOIN

2052 (SELECT id, val owner FROM blog_s WHERE key='owner') bs

2053 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2054 THEN 1 -- blog owner is an user, READABLE

2055 WHEN (SELECT user FROM grp_mem

2056 WHERE gname=bs.owner AND user='$user') IS NULL

2057 THEN 0

2058 ELSE 1

2059 END;

2060 EOF

2061 }

2062 listnewblogsql() { # $1=user

2063 newdays=${WHATS_NEW_DAYS##*[!0-9]} # Shave non-digits

2064 newdays=${newdays%%[!0-9]*}

2065 newdays=${newdays:-14}

2066 basetime="datetime('now', 'localtime', '-${newdays} days')"

2067 deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog

2068 WHERE user='$user'

2069 AND tblrowid IN

2070 ($blogreadflagrowid,

2071 $blogcutoffflagrowid)),

2072 $basetime -- "0"

2073 );"`

2074 cat<<EOF

2075 `sql4interestblogs`

2076 WITH article_ctime as (

2077 SELECT id,blogid,author,max(val) ctime

2078 FROM article join article_s s using(id)

2079 WHERE s.key='ctime' AND s.val > '$deftime'

2080 GROUP BY id

2081 ), blog_title_owner as (

2082 SELECT blg.rid brid, id,

2083 max(case key when 'title' then val end) title,

2084 max(case key when 'owner' then val end) owner

2085 FROM interestblogs blg, blog_s using(id) group by id

2086 ), blogall as (

2087 SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid

2088 ), news as (

2089 SELECT brid, bl.id blid, bl.title, ctime,

2090 coalesce(al.time, '$deftime') atime,

2091 count(bl.id) "新着", bl.author

2092 FROM blogall bl

2093 LEFT JOIN

2094 (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al

2095 ON bl.brid=al.tblrowid

2096 WHERE atime < bl.ctime

2097 GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id

2098 LIMIT 10

2099 ) SELECT brid LINK, "新着",

2100 (SELECT count(*) FROM article WHERE blogid=blid) "総数",

2101 ctime, title,

2102 (SELECT gecos FROM gecoses WHERE name=author) gecos

2103 FROM news;

2104 EOF

2105 }

2106

2107 search_form() {

2108 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

2109 # $2(optional) = pre-input keywords

2110 help="(1)空白区切りの単語で本文検索

2111 (2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索

2112 @2016-0[1-6] → 2016年1月から6月

2113 @>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間

2114 @week → 最近一週間

2115 (3)#番号で記事ID検索

2116 (1)と(2)は組み合わせOK

2117 例: @2016-10-0[1-9] 芋煮

2118 → 2016年10月上旬でキーワード「芋煮」を含む記事検索

2119 ※クイズ板は検索対象から外されます。"

2120 auth=""

2121 placeholder="全記事からの検索"

2122 case "$1" in

2123 author=*)

2124 a=`echo "${1#author=}"|htmlescape`

2125 g=`gecos ${1#author=}`

2126 auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"

2127 placeholder="このユーザの書込検索"

2128 help="★★ $g さんの書き込みから検索します$nl$help"

2129 ;;

2130 grid=*)

2131 a=`echo "${1#grid=}"`; a=$((0 + $a))

2132 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

2133 placeholder="このグループからの検索"

2134 ;;

2135 esac

2136 inikwd="$2" # no need to htmlescape

2137 cat<<-EOF

2138 <div class="right">

2139 <form action="$myname">$auth

2140 <input type="text" name="kwd" value="$inikwd" title="$help"

2141 placeholder=" $placeholder " width="10" accesskey="k">

2142

2143 ${touchpanel:+<p class="help">$help</p>}

2144 <input type="hidden" name="stage" value="searchart">

2145

2146 </form>

2147 </div>

2148 EOF

2149 }

2150

2151 imgsrc_cache() (

2152 # $1 = directory for cache'ing

2153 # $2 = table (user_m or grp_m)

2154 # $3 = keycond (was: condition for choosingowner)

2155 # $4 = size : S = Small, M = Medium, O = Original

2156 dir="$1" tbl="$2"

2157 keycond="$3"

2158 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

2159 ORDER BY rowid DESC LIMIT 1"

2160 [ -d "$dir" ] || mkdir -p "$dir"

2161 tmpf=$tmpd/imgsrc_cache.$$

2162 case "$4" in

2163 [Ss]) size=S ;;

2164 [Oo]) size=O ;;

2165 *) size=M ;;

2166 esac

2167 # ImageCache filename storing schema:

2168 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

2169 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

2170 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

2171 valtype=`query "$sql0"`

2172 filename=${valtype%%//*}

2173 filetype=${valtype##*//file:}

2174 if [ x"$filename" = x"${filename%.*}" ]; then

2175 # If nor filename extension found, set it to image type

2176 case "$filetype" in

2177 image/*) filename=$filename.${filetype#image/} ;;

2178 esac

2179 fi

2180 cacheimg_S=$dir/S_$filename

2181 cacheimg_M=$dir/M_$filename

2182 cacheimg_O=$dir/$filename

2183 cacheimg=$dir/${size}_$filename

2184 sumfile="$dir/$filename.sum"

2185 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

2186 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2187 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2188 # if cache is fresh and has the same checksum,

2189 echo "<img src=\"$cacheimg\">"

2190 else

2191 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2192 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2193 fifo2=$fifo.2

2194 mkfifo $fifo $fifo2

2195 fmt=${filename##*.}

2196 ## [[ NOTE ]]

2197 ## a. convert oldimage newimage

2198 ## b. convert oldimage fmt:- | convert - newimage

2199 ## b is much smaller than a

2200 cat $tmpf | unhexize \

2201 | tee $fifo \

2202 | convert -define ${fmt}:size=${iconxy_M} \

2203 -resize ${iconxy_M}'>' - ${fmt}:- \

2204 | tee $fifo2 \

2205 | convert - "$cacheimg_M" &

2206 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2207 -resize ${iconxy_S}'>' - ${fmt}:- \

2208 | convert - "$cacheimg_S" &

2209 printf '%s' "<img src=\"data:${filetype},"

2210 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2211 echo '">'

2212 echo "$sum" > $sumfile

2213 fi

2214 ## Now preparing cache image, done.

2215 ## Store this information to DB

2216 stbl=${tbl%_m}_s # user_s or grp_s

2217 pkey=${keycond%%=*} # Primary Key name

2218 pval=${keycond#*=} # Primary Key value

2219 query <<-EOF

2220 REPLACE INTO $stbl($pkey, key, type, val)

2221 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2222 EOF

2223 )

2224

2225 showhome() {

2226 # $1=userRowIdToShow

2227 err showhome \$1=$1

2228 case "$1" in

2229 *@*) uname=`getvalbypkey user name "$1"` ;;

2230 *) uname=`getvalbyid user name $1` ;;

2231 esac

2232 ## err ShowHome: uname=$uname

2233 td=`getcachedir home/"$1"`

2234 gecos=`gecos "$uname"`

2235 ## err SH:gecos=$gecos

2236 GF_VIEWONLY=1

2237 cond="gname in (select gname from grp_mem where user='$uname')"

2238 search_form_args=""

2239 if [ x"$user" = x"$uname" ]; then

2240 if [ -z "$S4MASTERDB" ]; then

2241 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2242 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2243 elif [ -n "$S4MASTERURL" ]; then

2244 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2245 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2246 fi

2247 usermenu="$usermenu

2248 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2249 # Display folders

2250 sql="select count(id) from article_m where id

2251 in (select id from article where author='$user')

2252 and type like 'file:%';"

2253 ## err nfile-sql=`echo "$sql"`

2254 nfile=`query "$sql"`

2255 # err nfile=$nfile

2256 if [ $nfile -gt 0 ]; then

2257 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2258 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2259 fi

2260 else

2261 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2262 GROUP BY user;"`

2263 usermenu="<p>Last seen on $latestlog</p>"

2264 search_form_args="author=$uname"

2265 fi

2266 . ./s4-blog.sh

2267

2268 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2269 search_form "$search_form_args" > $sf

2270 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2271 | htmlescape > $tf

2272 { echo "<div class=\"noprofimg\">"

2273 viewtable $formdir/user.def user $1

2274 echo "</div>"

2275 } > $pf

2276

2277 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2278 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2279 imf=$tmpd/profimg.$$; touch $imf

2280 if [ -n "$img" ]; then

2281 if true; then

2282 tbl=user_m

2283 enticond="name='$uname'"

2284 imgsrc_cache "$td/main" user_m "$enticond" M

2285 else

2286 { printf '%s' "<IMG src=\"data:${img#file:},"

2287 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2288 | sed 's/$..$/%\1/g'

2289 echo '">'

2290 }

2291 fi > $imf

2292 fi

2293 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2294 val='$uname';"`

2295 ## REMOVE This comment block until 2019/7/1

2296 ## err "----- `gdate +%FT%T.%3N` ------------C"

2297 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2298 listblog $uname > $bf

2299 ## unset ddd

2300 ## err "----- `gdate +%FT%T.%3N` ------------D"

2301

2302 hometail=$tmpd/tail.$$

2303 mkfifo $hometail

2304

2305 #Calling listgroupbytable, originally here

2306

2307 (

2308 # Display Most Recent Entry

2309 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2310 shortval=${shortval:-val}

2311

2312 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2313 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2314 ctime,

2315 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2316 (SELECT gecos FROM gecoses

2317 WHERE name=(SELECT val FROM blog_s

2318 WHERE key='owner' AND id=b.id)) owner,

2319 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2320 FROM blog b

2321 JOIN

2322 (SELECT distinct blogid, a.id aid, max(val) ctime

2323 FROM article a, article_s s

2324 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2325 GROUP BY blogid ORDER BY val DESC LIMIT 50

2326 ) m

2327 ON b.id=m.blogid;"

2328 # This should be as follows

2329 : <<EOF

2330 WITH arts AS(

2331 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2332 a.blogid, a.id id, s.val ctime

2333 FROM article a NATURAL JOIN article_s s

2334 WHERE s.key = 'ctime' AND a.author='$user'

2335 GROUP by s.id

2336 )

2337 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2338 FROM arts a0

2339 JOIN

2340 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2341 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2342 ORDER BY ctime DESC LIMIT 50;

2343 EOF

2344

2345 cat<<-EOF

2346 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2347 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2348 <div class="lcto">

2349 `DT_VIEW=replyblog dumptable html blog`

2350 </div>

2351 EOF

2352 unset DT_SQL

2353 if [ x"$user" = x"$uname" ]; then

2354 # Display NEWS

2355 # 2016-06-26

2356 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2357 acclog blog $blogreadflagrowid

2358 # echo "全部既読にしました" | html p

2359 fi

2360 # 2016-02-19 Counting NEWS without using dumptable.

2361 sql=`listnewblogsql "$user"`

2362 # echo "$sql" > tmp/listnew

2363 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2364 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2365 cont=$((cont-1))

2366 ##err newcount=$cont

2367 if [ $cont -gt 0 ]; then

2368 #echo "全体の新着記事${cont}傑" | html h2

2369 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2370 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2371 cat<<-EOF | html form 'action="?home"'

2372 `cgi_checkbox readchk yes 'id="read"'`<label

2373 for="read">新着ふくめて全部読んだことにする</label>

2374 `cgi_submit '確定'`

2375 `cgi_hidden read yes`

2376 EOF

2377 echo "$new10 "

2378 echo "</div>"

2379 else # If news is 0, set log cut off flag

2380 acclog blog $blogcutoffflagrowid # for speed

2381 fi

2382 else # Not My Home ($user != $uname)

2383 : # DT_SQL=

2384 fi

2385 ) > $hometail & # Is background call safe to m4??

2386 #

2387 listgroupbytable $formdir/grp.def "$cond" $uname |

2388 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2389 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2390 -D_PROFIMG_="spaste(\`$imf')" \

2391 -D_BLOGS_="spaste(\`$bf')" \

2392 -D_SEARCH_="spaste(\`$sf')" \

2393 -D_NBLOG_="$nblog" \

2394 -D_GROUPS_="syscmd(\`cat')" \

2395 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2396 $layout/html.m4.html $layout/home.m4.html

2397

2398 # Record access log

2399 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2400 }

2401 commission() { # $1=grp-rowid $2=user-rowid

2402 contenttype; echo

2403 ## err commission: "$@"

2404 gname=`getgroupbyid $1`

2405 echo "グループ $gname 管理者委任" \

2406 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2407 if [ -n "$2" ]; then

2408 grp_reg_adm "$@"

2409 else

2410 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2411 fi

2412 }

2413 listgroupbytable() {

2414 # $1=deffile $2...=condition $3(optional)=uname

2415 tagline=`grep :tag: $1`;

2416 and="${2:+and }" where=${2:+where }

2417 href="<a href=\"$myname?grp+"

2418 echo '<div class="listgroup">'

2419 sql="select val from grp_s where key='tag' $and$2 group by val;"

2420 ## err ListGRP: query sql="$sql"

2421 for tag in `query "$sql"`

2422 do

2423 ## err ListGrp: tag=$tag

2424 tn=${tagline%%=${tag}*}

2425 tn=${tn##*[ :]}

2426 sql="select rowid||':'||gname as 'グループ名',説明 from

2427 (select (select rowid from grp g where g.gname=grp_s.gname)

2428 as rowid,

2429 gname,

2430 max(case key when 'gecos' then val end) as '説明',

2431 max(case key when 'tag' then val end) as 'tag',

2432 max(case key when 'mtime' then val end) as mtime from grp_s

2433 $where$2 group by gname having tag='$tag' order by mtime desc);"

2434 ## err PersonalGroupList= `echo $sql`

2435 echo "<h2>$tn</h2>"

2436 echo '<table class="b listgroup">'

2437 sq -header -html $db "$sql" \

2438 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2439 echo '</table>'

2440 done

2441 if [ -n "$S4WORLDLIST" -a -n "$3" ]; then

2442 err "peekgrpworlds($user) BEGIN: `gdate +%S.%03N`"

2443 peekgrpworlds mem:"$3"

2444 err "peekgrpworlds($user) END: `gdate +%S.%03N`"

2445 fi

2446 echo '</div>'

2447 }

2448 iconhref() (

2449 # $1=icon-file, $2=Href $3=title $4...=anchor

2450 data=`percenthex "$1"`

2451 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2452 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2453 href=$2; title=$3; shift 3

2454 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2455 )

2456 iconhref2() (

2457 # $1=icon-file, $2=Href $3=title $4...=anchor

2458 src=$1

2459 href=$2; title=$3; shift 3

2460 anchor=`echo $@|htmlescape`

2461 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2462 )

2463 listentry() (

2464 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2465 # Referring variable $iamowner=$grp to attach owner-request links

2466 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2467 cond='' hiddens=''

2468 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2469 if [ -z "$offset" ]; then

2470 offset=`getpar start`; offset=${offset%%[!0-9]*}

2471 offset=$((offset-1))

2472 fi

2473 offset=$((offset + 0)) # change to numeric forcibly

2474 [ $offset -lt 0 ] && offset=0

2475 limit=$listentlimit

2476 dir=`getcachedir "$1"`

2477 if [ x"$1" = x"user" ]; then

2478 hrb="$myname?home"

2479 deficon=person-default.png

2480 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2481 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2482 gcs=gecos

2483 else # if group

2484 hrb="$myname?grp"

2485 deficon=group-default.png

2486 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2487 gcs=name

2488 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2489 if [ -n "$tagline" ]; then

2490 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2491 ## err tagconv=$tagconv

2492 fi

2493 fi

2494 if [ ! -d $dir ]; then

2495 mkdir -p $dir

2496 fi

2497 if [ ! -s $dir/$deficon ]; then

2498 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2499 fi

2500 kwd=${2:-`getpar kwd`}

2501 if [ -n "$kwd" ]; then

2502 kwd=`echo $kwd | tr -d '";\n' | tr -d "'"`

2503 case "$kwd" in

2504 mem:*@*)

2505 byuser=${kwd#*mem:}

2506 qusr=`sqlquote "$byuser"`

2507 cond1="(a.gname IN (SELECT gname FROM grp_mem WHERE user=$qusr))"

2508 ;;

2509 esac

2510 if [ x"$1" = x"group" ]; then

2511 if [ -n "$cond1" ]; then

2512 enthead="`gecos "$byuser"|htmlescape` さんの所属"

2513 else

2514 cond1="(b.name like '%${kwd}%')"

2515 fi

2516 else

2517 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2518 fi

2519 fi

2520 tag=`getpar tag` tag2=`getpar tag2`

2521 if [ x"$tag" = x"NULL" ]; then

2522 tag="" tag2=""

2523 fi

2524 if [ -n "$tag$tag2" ]; then

2525 tag=${tag:-$tag2}

2526 qtag=`sqlquote "$tag"`

2527 cond2="tag=$qtag"

2528 fi

2529 if [ -n "$cond1$cond2" ]; then

2530 cond="$cond1${cond2:+ AND $cond2}"

2531 cond="WHERE ${cond# AND }"

2532 fi

2533

2534 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2535 # grpは呼出し元の動的スコープ変数でよくないな...

2536 ##qgrp=`sqlquote $grp`

2537 getgrp="(select gname from grp where rowid=${rowid:--1})"

2538 sql="select a.rowid, a.$link,

2539 coalesce(b.$gcs, a.$nm) as nick,

2540 quote(a.$nm) as qname,

2541 (SELECT val FROM ${tbl}_s

2542 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2543 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2544 || case when a.$nm in (select gname from grp)

2545 then printf('(%d名)',

2546 (select count(user) from grp_mem where gname=a.$nm))

2547 else ' <'||a.$nm||'>'

2548 end

2549 as name,

2550 b.tag,

2551 case when a.$nm in (select user from grp_adm

2552 where gname=$getgrp) then '管理者'

2553 when '$user' in (select user from grp_adm where gname=a.$nm)

2554 then 'ADMIN'

2555 when '$user' in (select user from grp_mem where gname=a.$nm)

2556 then 'Member'

2557 when '$iamowner' = '' then ''

2558 else ',not='||a.rowid end as ownerlink,

2559 CASE '$entity'

2560 WHEN 'グループ'

2561 THEN coalesce(

2562 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2563 'open')

2564 ||

2565 CASE WHEN '$user'

2566 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2567 THEN ' ismember'

2568 ELSE ''

2569 END

2570 ELSE 'user'

2571 END regmode

2572 from $tbl a left join

2573 (select $nm as name,

2574 max(case key when 'gecos' then val end) as gecos,

2575 max(case key when 'tag' then val end) as tag,

2576 max(case key when 'mtime' then val end) as mtime,

2577 max(case key when 'wtime' then val end) as wtime,

2578 max(case key when 'login' then val end) as login

2579 from ${tbl}_s group by $nm)

2580 b on a.$nm=b.name $cond $3

2581 order by b.wtime desc, b.login desc,

2582 b.mtime desc, b.tag desc, a.rowid asc"

2583 # Give precedence to newer maintained groups (2016-09-24)

2584 # Note that mtime is stored only in grp_s.

2585 ## err LE:sql.1="$sql"

2586 total=`query "with x as ($sql) select count(*) from x;"`

2587 echo "$enthead${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2588 echo '<div class="listentry">' # List-entry div

2589 # Show owner/member filter button

2590 METHOD=GET

2591 hiddens="$hiddens

2592 `cgi_hidden stage \"$stage\"`"

2593 if [ x"$tbl" = x"grp" ]; then

2594 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2595 fh="<select name=\"tag\">$nl"

2596 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2597 for l in $args; do

2598 val=${l#*=} tname=${l%=*}

2599 if [ x"$val" = x"$tag" ]; then

2600 s=" selected"

2601 selectedtags="(種別[${tname}]のみ)"

2602 else

2603 s=""

2604 fi

2605 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2606 done

2607 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2608 cat<<-EOF

2609 <form action="$myname" method="$METHOD">

2610 </form>

2611 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2612 for="ismembtn">参加中以外隠す</label>

2613 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2614 for="isadmbtn">管理者参加以外隠す</label>

2615 EOF

2616 # limit=3

2617 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2618 fi

2619 if [ $total -gt $limit -o $ -n "$S4WORLDLIST" -a x"$tbl" = x"grp" $ ]; then

2620 echo '<div>'

2621 METHOD=GET cgi_form $stage <<EOF

2622 $form

2623 <label>次の語を含む${entity}で検索:

2624 `cgi_text kwd "$kwd"`</label>

2625 $hiddens

2626 EOF

2627 echo '</div>'

2628 else

2629 echo $selectedtags | html p

2630 fi

2631 test -n "$kwd" && hiddens="$hiddens$nl`cgi_hidden kwd \"$kwd\"`"

2632 cat<<EOF

2633 <form action="$myname" method="$METHOD">

2634 <p>${total}件中の<input class="hidesub" type="text" name="start"

2635 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2636 <input type="submit" value="確定"></p>

2637 </form>

2638 EOF

2639 if [ $((offset+limit)) -lt $total ]; then

2640 nextbtn=$(

2641 cat<<EOF

2642 <div class="right clear"><form action="$myname" method="$METHOD">

2643 `cgi_submit 次の${limit}件`

2644 $hiddens

2645 `cgi_hidden offset $((offset + limit))`</form></div>

2646 EOF

2647 )

2648 fi

2649 if [ $offset -gt 0 ]; then

2650 prevbtn=$(

2651 cat<<EOF

2652 <form action="$myname" method="$METHOD">

2653 `cgi_submit 前の${limit}件`

2654 $hiddens

2655 `cgi_hidden offset $((offset - limit))`</form>

2656 EOF

2657 )

2658 fi

2659 pnbtn="$nextbtn$prevbtn"

2660 echo $pnbtn

2661

2662 ## err ListEntry: `echo "$sql"\;`

2663 # sq $db here??? 2016-11-28

2664 query "$sql limit $limit ${offset:+offset $offset};" \

2665 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2666 # err name=$name owner=$ownerp lnk=$lnk

2667 # err newlnk=$lnk regmode=$regmode

2668 icondir=$dir/$id

2669 # Pick up only last icon

2670 htmlname=`echo $name|htmlescape`

2671 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2672 <p class=\"tag _$tag\">$tag</p>" \

2673 | _m4 $tagconv

2674 if [ -n "$NOSPEEDUP" ]; then

2675 files=`getvalbyid $tbl profimg $id $icondir`

2676 if [ -n "$files" ]; then

2677 icon=`echo "$files"|tail -1`

2678 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2679 else

2680 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2681 fi

2682 elif [ -n "$icon" -a -s "$icon" ]; then

2683 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2684 else

2685 cond="$nm=$qname"

2686 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2687 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2688 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2689 # err "img=[$img]"

2690 if [ -n "$img" ]; then

2691 echo "<a href=\"$hrb+$lnk\">"

2692 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2693 echo "</a>"

2694 else

2695 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2696 fi

2697 fi

2698 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2699 echo "</div>"

2700 done

2701 echo "</div>" # End of List-entry div

2702 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2703 if [ -n "$kwd" -a x"$tbl" = x"grp" -a -n "$S4WORLDS" ]; then

2704 peekgrpworlds "$kwd"

2705 fi

2706 )

2707 listmember() {

2708 listentry user "$@"

2709 }

2710 listgroup() {

2711 listentry group "$@"

2712 }

2713 hexteams() { # $1=gname, $2(optional)=user

2714 cond=${2:+" AND user='$2'"}

2715 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2716 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2717 }

2718 showgroup() { # $1=group-rowid

2719 if [ -z "$1" ]; then

2720 grid=`getpar grid`

2721 grid=${grid%%[!0-9]*}

2722 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2723 else

2724 grid=$1

2725 fi

2726 grp=`getgroupbyid $grid`

2727 qgrp=`sqlquote "$grp"`

2728 htmlgrp=`echo "$grp"|htmlescape`

2729 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2730 if isgroup "$grp"; then

2731 tf=$tmpd/title.$$

2732 sf=$tmpd/search.$$

2733 bodyclass=`query "SELECT val FROM grp_s

2734 WHERE gname=$qgrp AND key='regmode';"`

2735 if ismember "$user" "$grp"; then

2736 ismember="ismember"

2737 bodyclass="$bodyclass${bodyclass:+ }ismember"

2738 else

2739 ismember="" # bodyclass="group"

2740 fi

2741 bodyclass="$bodyclass grouphome"

2742 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2743 echo "グループ $htmlgrp" > $tf

2744

2745 showgroupsub $formdir/grp.def "$grid" | \

2746 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2747 -D_FORM_="syscmd(\`cat')" \

2748 -D_BODYCLASS_="$bodyclass" \

2749 -D_DUMPTABLE_="" \

2750 $htmlheader $sf $layout/form+dump.m4.html

2751 # $htmlheader $layout/form+dump.m4.html

2752 # $htmlheader is defined in grp()

2753 else # if $grp is removed at par2table

2754 listgroup

2755 fi

2756 }

2757 showgroupsub() {

2758 # $1=def-file $2=group-rowid

2759 # Using $ismember

2760 rowid=$2

2761 grp=`getgroupbyid $2`

2762 qgrp=`sqlquote "$grp"`

2763 td=`getcachedir grp/"$2"`

2764 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2765 if [ -z "$rowid" ]; then

2766 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2767 #grp=`sq $db "select gname from grp where rowid=$grp"`

2768 echo "showgroupsub: invalid argument($1 $2)" | html p

2769 return

2770 fi

2771 val=`getvalbyid grp profimg $rowid $tmpd`

2772 enticond="gname=$qgrp"

2773 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2774 if [ -n "$img" ]; then

2775 cat<<-EOF

2776 <p class="groupimg">

2777 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2778 EOF

2779 fi

2780 echo "<div class=\"noprofimg\">"

2781 viewtable $1 grp $rowid

2782 echo "</div>"

2783 if isgrpowner "$user" "$grp"; then

2784 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2785 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2786 iamowner=$rowid

2787 colmd=" mode"

2788 fi

2789 if [ -n "$ismember" ]; then

2790 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2791 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2792 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2793 cat<<-EOF

2794 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2795 href="?blog+$rowid">グループの新規話題作成</a>

2796 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2797 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2798 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2799 <div class="fold clear">

2800 `cgi_checkbox send yes id="send"`<label

2801 for="send">グループ全員にメッセージ送信</label>

2802 <div>

2803 `cgi_textarea message "" "cols=60"`

2804 `cgi_submit 送信`

2805 `cgi_reset リセット`

2806 </div>

2807 `cgi_hidden grp $rowid`

2808 </div></form>

2809 EOF

2810 fi

2811 # 加入ボタン + 加入者リスト

2812 if [ -n "$ismember" ]; then

2813 ismem='checked' state="(参加中)"

2814 else

2815 nomem='checked' state="(現在非加入)"

2816 fi

2817 # このグループでの加入アドレス

2818 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2819 and key='email';"`

2820 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2821 ## and key='email';"

2822 ##err email=$eml

2823 cat <<EOF

2824 <div class="fold clear">

2825 `cgi_checkbox reg yes id="reg"`<label

2826 for="reg">自身の加入状態を操作する</label>$state

2827 <div>

2828 EOF

2829 cgi_form grp <<EOF

2830 <p>このグループに</p>

2831 <table class="b">

2832 <tr><th>メンバーとして</th><td>

2833 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2834 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2835 <tr><th>参加する場合のメイルアドレス<br>

2836 <small>(メインのアドレスとは違うものにする場合に記入<br>

2837 同じでよい場合は空欄に)</small></th>

2838 <td>`cgi_text email $eml`</td></tr>

2839 </table>

2840 `cgi_hidden grp $rowid`

2841 EOF

2842 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2843 echo "moderated (承認加入の)グループなので実際に参加できるのは

2844 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2845 fi

2846 echo '</div></div>'

2847 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2848 cat<<-EOF

2849 <div class="fold">

2850 <h2>話題一覧</h2>

2851 <form class="summary inline" action="$myname" title="$thelp">

2852 `cgi_hidden owner "$grp"`

2853 `cgi_hidden kwd "@week"`

2854 `cgi_hidden stage searchart`

2855 `cgi_submit "一週間のまとめ"`

2856 </form>

2857 <input type="checkbox" id="dtcheck" checked><label

2858 for="dtcheck">話題一覧表示</label>

2859 EOF

2860 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by 稼動状態, ctime desc"

2861 colstate="state:稼動状態:frozen=rowclass=凍結"

2862 DT_CHLD=article:blogid \

2863 DT_QOWNER="$qgrp" \

2864 DT_VIEW=replyblog dumptable html blog \

2865 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2866 echo "</div> "

2867

2868 getgname="(select gname from grp where rowid=$rowid)"

2869 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2870 cm="?commission+$rowid"

2871 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2872 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2873 # team list

2874 hexteams=`hexteams "$grp"`

2875 if [ -n "$hexteams" ]; then

2876 echo "チーム一覧" | html h2

2877 echo '<div class="dumptable"><table class="b">'

2878 sq $db -html -header<<-EOF

2879 SELECT val TEAM,

2880 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2881 MEMBERS

2882 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2883 EOF

2884 echo '</table></div>'

2885 fi

2886 }

2887 grp_getbodyclass() {

2888 # Get css class name for document.

2889 # `moderated' for moderated groups

2890 # `ismember' for groups where user belongs

2891 # $1=GroupName (w/o quote)

2892 # $user=userNameCurrentlyLogin

2893 ## err grp_getbodyclass: 1="$1"

2894 qgrp=`sqlquote "$1"`

2895 query<<-EOF

2896 SELECT coalesce(

2897 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2898 'open')

2899 ||

2900 CASE WHEN '$user'

2901 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2902 THEN ' ismember'

2903 ELSE ''

2904 END;

2905 EOF

2906 }

2907 grpaction() { # $1=group-rowid

2908 err GRP_ACTION:IN

2909 grid=${1:-`getpar grp`}

2910 grp=`getgroupbyid "$grid"`

2911 htmlgrp=`echo "$grp" | htmlescape`

2912 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2913 if [ -z "$grp" ]; then

2914 echo "無効な指定です。" | html p; return

2915 fi

2916 if ! ismember $user "$grp"; then

2917 echo "加入者のみに許可された操作です。" | html p; return

2918 fi

2919 isowner=""

2920 isgrpowner "$user" "$grp" && isowner="yes"

2921 err 2=$2 3=$3

2922 case "$2" in

2923 get:teamcsv)

2924 teamcsv "$grid"

2925 return

2926 ;;

2927 esac

2928 echo "グループ $grp 個別選択操作" \

2929 | _m4 -D_TITLE_="syscmd(\`cat')" \

2930 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2931 $layout/html.m4.html

2932

2933 usel=`getpar usel`

2934 if [ -n "$usel" ]; then

2935 uids=$(echo `echo $usel`|tr ' ' ',')

2936 ## err grpaction-1: grp=$grp, `echo $sql`

2937 text=`getpar text|tr -d '\r'`

2938

2939 rm=`getpar rm` cfm=`getpar confirm`

2940 ## err rm=$rm cfm=$cfm

2941 if [ x"$rm" = x"yes" ]; then

2942 if [ "$isowner" ]; then

2943 if [ x"$rm$cfm" = x"yesyes" ]; then

2944 # Eliminate

2945 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2946 for tbl in grp_mem grp_mem_s grp_mem_m; do

2947 sql="delete from $tbl $cond;"

2948 # echo "sql=$sql"

2949 query "$sql"

2950 err rmGRPuser "$sql"

2951 done

2952 num=`query "select count(*) from user where rowid in ($uids);"`

2953 #err num=$num

2954 if [ 0$num -gt 0 ]; then

2955 sql="select coalesce(b.val,a.name) from user a left join \

2956 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2957 # err `echo "$sql"`

2958 html pre<<EOF

2959 以下の${num}名のグループ $grp 登録を解除しました。

2960 `query "$sql"`

2961 EOF

2962 fi

2963 else

2964 echo "確認のチェックがないのでやめておきます。" | html p

2965 return

2966 fi

2967 else # not Group Owner

2968 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2969 return

2970 fi

2971 cat<<EOF

2972

2973 EOF

2974 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2975 if [ -z "$text" ]; then # if msg is empty

2976 echo "なにかメッセージを..." | html p

2977 return 0

2978 fi

2979 gecos=`gecos $user`

2980 safegc=`echo "$gecos" | tr -d '<>@'`

2981 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2982 fromad=`myemail4group "$grp"`

2983 ###mail_from="$safegc <$fromad>"

2984 mail_from="$safegc <$user>" # TEST: 2020/5/13

2985 test -n `getpar sender` &&

2986 export SENDER=$user # TEST: 2020/5/15

2987 replyto=$fromad

2988

2989 ## Start parse of attachment files

2990 if [ -n "`getpar email`" ]; then

2991 ar=`getpar supprcpt`

2992 if [ -n "$ar" ]; then

2993 for a in $ar; do

2994 if checkdomain "$a"; then

2995 supprcpt="$supprcpt $a"

2996 else

2997 err "SupprcptErr=[$a] by $user"

2998 removercpt="$removercpt $a"

2999 fi

3000 done

3001 fi

3002 subj=`getpar subject`

3003 afiles=""

3004 for fn in `query "SELECT DISTINCT val FROM par WHERE var='image' AND sessid='$session';"`

3005 do

3006 f=$tmpd/$fn

3007 if [ -s $f ]; then

3008 afiles=$afiles"${afiles:+ }$f"

3009 fi

3010 done

3011 else

3012 preface=$(cat <<-EOF

3013 $url

3014 のグループ「$grp」のメンバーである $gecos さんから、

3015 あなた宛のメッセージです。

3016 ----------------------------------------------------------

3017 EOF

3018 )

3019 fi

3020 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

3021 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

3022 subj="${subj:-$gecos さんからのメッセージ}"

3023 REPLYTO=$replyto

3024 MAIL_FROM=$mail_from

3025 export REPLYTO SMAIL_TO MAIL_FROM

3026 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

3027 for r in $rcpts; do

3028 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

3029 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

3030 else

3031 # Show sender and rcpts address for guest

3032 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

3033 fi

3034 if [ -n "$afiles" ];then

3035 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

3036 else

3037 smail "$r" "$subj"

3038 fi <<EOF

3039 ${preface:+$preface$nl}$text

3040 EOF

3041 done

3042 if [ $? = 0 ]; then

3043 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

3044 sql="select coalesce(b.val, a.name) from

3045 (select name from user where rowid in ($uids)) a

3046 left join user_s b on a.name=b.name and b.key='gecos';"

3047 html pre<<EOF

3048 `query "$sql"`

3049 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

3050 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

3051 EOF

3052 err SendDone: `echo $sql`

3053 fi

3054 elif [ x"$rm" = x"commission" ]; then

3055 grp_reg_adm $grid $usel

3056 elif [ x"$rm" = x"addteam" ]; then

3057 team=`getpar team|sed "s/'/''/g"` # for single quotation

3058 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

3059 if [ x"$team" != x"$newteam" ]; then

3060 echo "チーム名に使えない文字を除去しました" | html p

3061 team=$newteam

3062 fi

3063 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

3064 cat<<-EOF | html p

3065 有効なチーム名を入力してください。

3066 カンマだけ、「なし」という名前は使えません。

3067 EOF

3068 echo "有効なチーム名を入力してください。" | html p

3069 else

3070 grp_add_team $grid "$team" $usel

3071 fi

3072 elif [ x"$rm" = x"rmteam" ]; then

3073 if [ x"yes" = x"`getpar teamconfirm`" ]; then

3074 rmteam=`getpar rmteam|sed "s/'/''/g"`

3075 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

3076 gname='$grp' AND user='$user' AND key='team'\

3077 AND val='$rmteam';\"`" ]; then

3078 grp_rm_team $grid "$rmteam" $usel

3079 else

3080 echo "所属していないチームの除去操作はできません。"|html p

3081 fi

3082 else

3083 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

3084 fi

3085 fi

3086 fi

3087 # POST count summary

3088 from=`getpar from`; to=`getpar to`

3089 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3090 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3091 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

3092 # New entry

3093 sql="WITH mems AS (

3094 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3095 ON gm.user=g.name

3096 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3097 ), target_article AS (

3098 SELECT id FROM article_s

3099 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3100 ), posts AS (

3101 SELECT author, count(author) post

3102 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3103 WHERE blogid IN (SELECT id FROM blog_s

3104 WHERE key='owner'

3105 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3106 AND key='text'

3107 GROUP BY author

3108 ), teams AS (

3109 SELECT user, group_concat(val, ', ') team

3110 FROM mems m LEFT JOIN grp_mem_m gm ON m.name=gm.user

3111 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3112 AND key='team'

3113 GROUP BY user

3114 ), user_post AS (

3115 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3116 FROM mems m LEFT JOIN posts

3117 ON m.name=posts.author

3118 GROUP by m.rowid

3119 )

3120 SELECT

3121 CASE

3122 WHEN (SELECT user FROM grp_adm

3123 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3124 AND user=up.name) IS NOT NULL

3125 then 'k'

3126 ELSE ''

3127 END || rowid || ','

3128 || rtrim(substr(name, 1, instr(name, '@')), '@') || ','

3129 || gecos NAME,

3130 post POST, team _TEAM_

3131 FROM user_post up LEFT JOIN teams t

3132 ON up.name=t.user

3133 ORDER BY name;"

3134 ## err grpaction: "`echo \"$sql\"`"

3135 tf=$tmpd/title.$$

3136 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3137 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3138 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3139 for=\"cmadmin\">管理者委任</label>

3140 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3141 </p></div>"

3142 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3143 title=\"Shortcut: g${nl}Remove from the Group\"

3144 for=\"conf\">GRP登録解除</label>

3145 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3146 <p>この操作による通知は本人に行きません。

3147 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3148 </div>"

3149 # Get team list to which current user belongs into $hexteams

3150 allhexteams=$(hexteams "$grp")

3151 if [ -n "$isowner" ]; then

3152 myhexteams="$allhexteams" # admin can remove all teams' attr

3153 else

3154 myhexteams=$(hexteams "$grp" "$user")

3155 fi

3156 if [ -n "$isowner" -a -n "$allhexteams" ]; then

3157 gettingcsv="<p>Download: <a href=\"?getteamcsv+$grid\">Team.csv</a> (Zoom Breakout Room 事前割り当てに使えます)</p>"

3158 fi

3159 if [ -n "$myhexteams" ]; then

3160 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3161 title=\"Shortcut: s${nl}Strip a team tag from\"

3162 for=\"cmrmteam\">チーム属性除去</label>

3163 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3164 を除去します: `cgi_checkbox teamconfirm yes` 確認

3165 <p>この操作による通知は本人に行きません。

3166 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3167 </div>

3168 "

3169 fi

3170 stf=$tmpd/selteam.$$

3171 cgi_select_h selteam "5445414d" $allhexteams > $stf

3172 b1='<label> <input type="checkbox" name="usel" value="'

3173 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3174 br='<span id="reverse" title="Reverse Selection"></span>'

3175 #b2='"> <span>' b3='</span></label>'

3176 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

3177 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

3178 lnk='"> <span>\5</span></label> [<a href="?home+\3">HOME</a>]'

3179 #-e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

3180 cgi_form grpaction<<EOF \

3181 | sed -E \

3182 -e "s|^(<TR><TD)>(k?)([0-9]*),([^,]+),([^<]*)|\1 title=\"\4\">\2$b1\3$lnk|" \

3183 -e 's/^(<TR><TD[^>]*>)k(<label)/\1\2 class="admin"/' \

3184 -e "s|^(<TR><TH>)(NAME)|\1$br \2|" \

3185 | _m4 -D_TITLE_="spaste(\`$tf')" \

3186 -D_SUBTITLE_="チェック後操作ボタン" \

3187 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3188 $layout/form+dump.m4.html \

3189 | _m4 -D_TEAM_="spaste(\`$stf')"

3190 <p>下でチェックした人を対象として:</p>

3191 <div class="foldtabs">

3192 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3193 title="Shortcut: a${nl}Add a team tag to"

3194 for="cmteam">同じチーム属性を付与</label>

3195 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3196 `cgi_datalist_h teams $allhexteams`

3197 </div>

3198 ${rmteammsg}

3199 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3200 title="Shortcut: d${nl}DirectMail to"

3201 for="sendmsg" title="Direct Message">DM送信</label>

3202 <div>

3203 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3204 title="Using email format">email書式を使う</label>

3205 <div class="folded">

3206 <table>

3207 <tr><td>From: </td><td>$user</td></tr>

3208 <tr><td>このFrom:で送る</td>

3209 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3210 </td></tr>

3211 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3212 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3213 <tr><td>ファイル添付: </td>

3214 <td>`cgi_file image "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3215 </table>

3216 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 送信`)</p>

3217 </div>

3218 <div>本文:`cgi_textarea text "" cols=72`

3219 </div>

3220 </div>

3221 ${isowner:+$cmmsg$excmsg}

3222 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3223 </div>

3224 <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote

3225 <table class="td2r thl">

3226 `sq $db -header -html "$sql"`

3227 </table>

3228 `cgi_hidden grp $grid`

3229 `cgi_hidden myuid $myuid id="myuid"`

3230 EOF

3231 }

3232 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3233 # Create TEMPORARY VIEW

3234 query<<EOF

3235 CREATE TEMPORARY VIEW writeusers AS

3236 SELECT DISTINCT author FROM article

3237 WHERE id in (

3238 select id from article where blogid=(select id from blog where rowid=$1)

3239 );

3240 CREATE TEMPORARY VIEW movablegroups AS

3241 SELECT g.rowid growid , g.gname

3242 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3243 ON grp.gname=gm.gname -- そのユーザが属している

3244 AND user='$user') g -- グループに絞る

3245 WHERE (SELECT author FROM writeusers

3246 EXCEPT

3247 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3248 IS NULL;

3249 $2

3250 EOF

3251 }

3252 sql4readableblogs() {

3253 # Create view of blogs that can be readable to $user

3254 # Blog is readable when:

3255 # 1: blog owner is an user

3256 # 2: else, 2.1: owner-group where the $user belongs

3257 # 2.2: else, owner-group is not moderated

3258 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3259 cat<<EOF ## | tee tmp/sql.out

3260 CREATE TEMPORARY VIEW readableblogs AS

3261 SELECT blog.rowid rid, id, author

3262 FROM blog

3263 NATURAL JOIN

3264 (SELECT id,

3265 max(CASE key WHEN 'owner' THEN val END) owner,

3266 max(CASE key WHEN 'mode' THEN val END) mode

3267 FROM blog_s GROUP by id) bs

3268 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3269 THEN 1 -- blog owner is an user, READABLE

3270 WHEN (SELECT val FROM grp_s

3271 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3272 AND

3273 (SELECT user FROM grp_mem

3274 WHERE gname=bs.owner AND user='$user') IS NULL

3275 THEN 0

3276 WHEN mode IN ('quiz', 'enquete')

3277 THEN 0 -- "quiz" mode blog cannot be searched

3278 ELSE 1

3279 END;

3280 EOF

3281 }

3282 mvteamform() {

3283 owner=$1

3284 hexteams=$(hexteams "$owner" "$user")

3285 test -z "$hexteams" && return

3286 none="`echo なし|hexize`"

3287 cat<<-EOF

3288 <!-- <div class="fold">

3289 `cgi_checkbox mv2team send id="mv2team"`<label

3290 for="mv2team">この話題を以下のチームのものにする</label>

3291 <div> -->

3292 <p>この話題をチーム所有にする:

3293 チーム: `cgi_select_h mv2team $none $hexteams`</p>

3294 </form></div></div>

3295 EOF

3296 }

3297 editheading() { # $1=rowid-of-heading

3298 rowid=${1%%[!A-Z0-9a-z_]*}

3299 if [ -z "$rowid" ]; then

3300 echo "話題番号が未指定です。" | html p

3301 return

3302 fi

3303 owner=`getvalbyid blog owner $rowid`

3304 title=`getvalbyid blog title $rowid`

3305 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3306 | _m4 -D_TITLE_="修正" \

3307 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3308 -D_BLOGS_="" -D_DUMPTABLE_="" \

3309 -D_FORM_="syscmd(\`cat')" \

3310 $layout/html.m4.html $layout/form+dump.m4.html

3311 # Move to group

3312 if isuser "$owner"; then

3313 crview4article $rowid

3314 n=`query "SELECT count(*) FROM writeusers;"`

3315 ## err N=$n

3316 if [ $((n)) -gt 0 ]; then

3317 ## err ROWID=$rowid

3318 sql="SELECT growid || ':' || gname FROM movablegroups;"

3319 cat<<-EOF

3320 <div class="fold">

3321 `cgi_checkbox mv send id="mv"`<label

3322 for="mv">この話題をグループ所有に移動する</label>

3323 <div>

3324 <form action="?mvart" method="POST" enctype="multipart/form-data">

3325 移動先グループ:

3326 <select name="mv2grp">

3327 EOF

3328 query ".mode html"

3329 query<<-EOF |

3330 $sql

3331 .mode list

3332 EOF

3333 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3334 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3335 cat<<-EOF

3336 </select>

3337 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3338 そのグループに加入しているものに限られます)</p>

3339 <p>`cgi_checkbox cfm yes`<label>確認

3340 (この操作は元に戻すことができません)</label></p>

3341 `cgi_hidden blogrowid $rowid`

3342 `cgi_submit 移動`

3343 `cgi_reset Reset`

3344 </form>

3345 </div>

3346 </div>

3347 EOF

3348 fi

3349 # end of isuser "$owner"

3350 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3351 [ -n "$hexteams" ];}; then

3352 none="`echo なし|hexize`"

3353 cat<<-EOF

3354 <div class="fold">

3355 `cgi_checkbox mv2team send id="mv2team"`<label

3356 for="mv2team">この話題を以下のチームのものにする</label>

3357 <div><p>現在の所属チーム設定:

3358 `query "SELECT

3359 coalesce((SELECT val FROM blog_s

3360 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3361 AND key='team'),

3362 ':なし');"`</p>

3363 <form action="?mvart" method="POST" enctype="multipart/form-data">

3364 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3365 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3366 `cgi_hidden blogrowid $rowid`<br>

3367 `cgi_submit 移動`

3368 `cgi_reset Reset`

3369 </form></div></div>

3370 EOF

3371 fi

3372 }

3373 mvart() { # move diary to some group or team

3374 # or move blog of group to team which belong to the group

3375 blogrowid=`getpar blogrowid`

3376 cfm=`getpar cfm`

3377 ##### echo move blog:$blogrowid to $mv2grp | html p

3378 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3379 . ./s4-blog.sh

3380 if [ -z "$blogrowid" ]; then

3381 echo "無効な指定です(mvart)。" | html p

3382 return

3383 elif [ x"$cfm" != x"yes" ]; then

3384 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3385 elif { mv2grp=`getpar mv2grp`

3386 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3387 [ -n "$mv2grp" ]; }; then

3388 crview4article $blogrowid

3389 ########## TRANSACTION BEGIN

3390 query "BEGIN;"

3391 n=`query "SELECT count(*) FROM writeusers;"`

3392 ## err Nwriteuser=$n

3393 if [ $((n)) -gt 0 ]; then

3394 query<<-EOF

3395 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3396 WHERE key='owner'

3397 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3398 AND $mv2grp IN (SELECT growid FROM movablegroups);

3399 EOF

3400 fi

3401 query "END;"

3402 ########## TRANSACTION END

3403 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3404 [ -n "$mv2team" ];}; then

3405 # blog owner can move it to ANY team

3406 case "$mv2team" in

3407 'なし')

3408 cat<<-EOF

3409 DELETE FROM blog_s

3410 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3411 AND key='team';

3412 EOF

3413 ;;

3414 "") ;;

3415 *)cat<<-EOF

3416 BEGIN;

3417 REPLACE INTO blog_s(id, key, val)

3418 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3419 'team', '$mv2team');

3420 REPLACE INTO blog_s(id, key, val)

3421 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3422 'notify', 'all'); -- Change notify to all

3423 END;

3424 EOF

3425 esac | query

3426 fi

3427 blog_reply $blogrowid

3428 echo yes | html p

3429 }

3430 editart() { # $1=article-rowid $2=blogrowid

3431 rowid=${1%%[!A-Z0-9a-z_]*}

3432 blogrowid=${2%%[!A-Z0-9a-z_]*}

3433 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3434 echo "表示する記事番号が未指定です。" | html p

3435 return

3436 fi

3437 owner=`getvalbyid blog owner $blogrowid`

3438 title=`getvalbyid blog title $blogrowid`

3439 author=`getvalbyid article author $rowid`

3440 ## err EDITart: owner=$owner, author=$author

3441 if isgrpowner "$user" "$owner"; then

3442 : EDIT OK

3443 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3444 echo "本人か所有者しか編集できません." | html p

3445 return

3446 fi

3447 aid=`query "select id from article where rowid=$rowid;"`

3448 tmpout=$tmpd/editart.$$.out

3449 GF_ACTION="?replyblog+$blogrowid#$aid" \

3450 edittable $formdir/article.def article $rowid \

3451 > $tmpout

3452 rm -f /tmp/editart.out

3453 # Cannot use pipelining to m4 with genform() because of stdin stack

3454 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3455 -D_FORM_="syscmd(cat $tmpout)" \

3456 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3457 -D_BLOGS_= -D_DUMPTABLE_= \

3458 $layout/html.m4.html $layout/form+dump.m4.html

3459 }

3460 send2mem() {

3461 rowid=`getpar grp`

3462 rowid=${rowid%%[!0-9]*} # Cleaning

3463 if [ -z "$rowid" ]; then

3464 echo "グループが未指定です。" | html p

3465 return

3466 fi

3467 message=`getpar message`

3468 if [ -z "$message" ]; then

3469 echo "文章を入れてください。" | html p

3470 return

3471 fi

3472 grp=`getgroupbyid $rowid`

3473 members=`collectemail "$grp"`

3474 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3475 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3476 mailfrom="`gecos "$user"` <$mailfrom>"

3477 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3478 msg=$(cat<<-EOF

3479 $urlbase?grp+$rowid

3480 グループ $grp に所属する

3481 `gecos $user` さんよりメッセージ:

3482

3483 $message

3484 EOF

3485 )

3486 # smail rcpt subj (file)

3487 for m in $members; do

3488 echo "$msg" |

3489 MAIL_FROM=$mailfrom \

3490 SENDER=$noreply \

3491 REPLYTO=$mailfrom \

3492 smail "$m" "$sj"

3493 done

3494 cat<<EOF

3495 <p>以下のユーザに送信しました。</p>

3496 <pre>

3497 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3498 </pre>

3499 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3500 EOF

3501 }

3502 joingrpadmit() {

3503 # $1=yes/no $2=session-key

3504 if [ -z "$2" ]; then

3505 echo "bye bye" | html p; return

3506 fi

3507 t_usr=`session=$2 getpar adduser`

3508 t_grp=`session=$2 getpar group`

3509 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3510 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3511 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3512 echo "無効な加入依頼です。" | html p

3513 echo "有効期限が切れたか、

3514 他の管理者がいる場合は処理済みの可能性があります。" | html p

3515 return

3516 fi

3517 if ! isgrpowner "$user" "$t_grp"; then

3518 echo "グループ管理者のみの機能です。" | html p; return

3519 fi

3520 case $1 in

3521 yes) joingrp "$t_grp" "$t_usr" yes ;;

3522 no) joingrp "$t_grp" "$t_usr" no ;;

3523 *)

3524 echo "無効な指定です($1)。" | html p

3525 return ;;

3526 esac

3527 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3528 rcpts="`getgroupadminmails "$t_grp"` $user"

3529 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3530 body="に

3531 $t_usr

3532 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3533 しました。"

3534 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3535 query "delete from session where id='$2';"

3536 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3537 }

3538

3539 joingrprequest() {

3540 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3541 jss="joingrp-`date +%s`-`genrandom 12`"

3542 addsession $jss +${memoplimitdays}days

3543 grpadmins=`getgroupadmins "$1"`

3544 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3545 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3546 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3547 $url

3548 $user さんから

3549 グループ $1

3550 に加入依頼がありました。

3551

3552 承認する:

3553 $urlbase?joingrpadmit+yes+$jss

3554

3555 白紙に戻す:

3556 $urlbase?joingrpadmit+no+$jss

3557 EOF

3558 echo "管理者に加入依頼を出しました。

3559 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3560 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3561 }

3562 joingrp() {

3563 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3564 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3565 if isgrpowner "$user" "$1"; then

3566 isowner="yes"

3567 elif [ -n "$5" ]; then

3568 isowner="yes"

3569 else

3570 isowner=""

3571 fi

3572 ## err jg:isgrpowner: isowner="$isowner"

3573 if [ -n "$isowner" ]; then

3574 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3575 elif [ x"$2" != x"$user" ]; then # if user is not login user

3576 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3577 return

3578 elif [ x"$3" = x"no" ]; then

3579 : # Do not pursue those who leave

3580 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3581 : # Member can change own email address for the joining moderated group

3582 else # adding user is $user itself

3583 case `getgroupattr "$1" regmode` in

3584 moderated)

3585 joingrprequest "$@" # Request only

3586 return

3587 ;;

3588 *)

3589 ;;

3590 esac

3591 fi

3592 qgname=`sqlquote "$1"`

3593 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3594 cond="where gname=$qgname and user='$2'"

3595 if [ x"$3" = x"yes" ]; then

3596 query "replace into grp_mem values($qgname, '$2');"

3597 # Notify joingrp to admin

3598 action="に加入しました。"

3599 if [ -n "$4" ]; then

3600 if msg=`emaildomaincheck "$4"`; then

3601 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3602 'string', '$4', NULL);"

3603 else

3604 echo $msg

3605 fi

3606 else

3607 query "delete from grp_mem_s $cond and key='email';"

3608 fi

3609 if [ -n "$5" ]; then # as ADMIN

3610 # Coming here means newly created group

3611 sql="select case\

3612 when (select count(*) from grp_mem where gname=$qgname)=1\

3613 then (select user from grp_mem\

3614 where gname=$qgname and user='$user')\

3615 else '' end; "

3616 err NewGrpChk: $sql

3617 if [ -n "`query \"$sql\"`" ]; then

3618 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3619 query "replace into grp_adm values($qgname, '$user');"

3620 fi

3621 fi

3622 else

3623 query "begin;

3624 delete from grp_mem $cond;

3625 delete from grp_mem_s $cond;

3626 delete from grp_mem_m $cond;

3627 delete from grp_adm $cond;

3628 delete from grp_adm_s $cond;

3629 delete from grp_adm_m $cond;

3630 end;"

3631 action="から脱退しました。"

3632 fi

3633 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3634 $url?grp+$grid

3635 $user (`gecos $user`)さんが

3636 グループ $1

3637 $action

3638 EOF

3639 }

3640 grp_add_team() (

3641 # $1=grp-rowid $2=team $3...=user-rowid(s)

3642 grp=`getgroupbyid $1`

3643 team=$2; shift; shift

3644 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3645 { echo "BEGIN;"

3646 for user; do

3647 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3648 '$grp',\

3649 (SELECT name FROM user WHERE rowid=$user),\

3650 'team', 'string', '$team');"

3651 done

3652 echo "END;"

3653 } | query

3654 )

3655 grp_rm_team() (

3656 # $1=grp-rowid $2=team $3...=user-rowid(s)

3657 grid=$1

3658 qgrp=$(sqlquote "`getgroupbyid $grid`")

3659 team=$2; shift; shift

3660 [ -z "$grid" -o -z "$team" ] && return

3661 { echo "BEGIN;"

3662 for user; do

3663 echo "DELETE FROM grp_mem_m\

3664 WHERE gname=$qgrp \

3665 AND user=(SELECT name FROM user WHERE rowid=$user)\

3666 AND key='team' AND val='$team';"

3667 done

3668 cat<<-EOF

3669 DELETE FROM blog_s

3670 WHERE rowid=(

3671 SELECT rowid

3672 FROM blog_s a

3673 WHERE key='team'

3674 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3675 AND NOT EXISTS (SELECT * FROM grp_mem_m

3676 WHERE key='team' AND val=a.val -- a.val=team

3677 AND gname = (SELECT val FROM blog_s b

3678 WHERE a.id=b.id AND key='owner')

3679 ));

3680 EOF

3681

3682 echo "END;"

3683 } | query

3684 )

3685 grp_reg_adm() {

3686 # $1=grp-rowid $2...=user-rowid

3687 grid=$1

3688 grp=`getgroupbyid "$1"`

3689 if [ -z "$grp" ]; then

3690 echo "無効なグループIDです" | html p; return

3691 fi

3692 if ! isgrpowner "$user" "$grp"; then

3693 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3694 fi

3695 shift

3696 for urid; do

3697 newadm=`query "select name from user where rowid=$urid;"`

3698 if [ -z "$newadm" ]; then

3699 echo "指定ユーザIDがおかしいようです。" | html p; return

3700 fi

3701 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3702 err ismember $newadm $grp

3703 if ismember $newadm "$grp"; then

3704 # OK, go ahead

3705 getgname="(select gname from grp where rowid=$grid)"

3706 query "replace into grp_adm values($getgname, '$newadm');"

3707 # confirm insertion

3708 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3709 if [ -n "`query \"$sql;\"`" ]; then

3710 echo "追加完了: $newadm" | html p

3711 else

3712 echo "追加失敗($1 $urid)" | html p

3713 fi

3714 fi

3715 showgroup $grid

3716 done

3717 }

3718 dt_rowhack() {

3719 # From: <TR>

3720 # ....

3721 # <TD>rowclass=foo</TD>

3722 # </TR>

3723 # To: <TR class="foo">....<TD>foo</TD></TR>

3724 sed -e '

3725 /^<TR>/ {

3726 :loop

3727 s/\n//

3728 N

3729 /<\/TR>/ {

3730 s/\n//

3731 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3732 n

3733 }

3734 $q

3735 b loop

3736 }'

3737 }

3738 dumptable() {

3739 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3740 # textのフィールドだけ全てダンプにしたほうがいいか

3741 # $DT_VIEW sets link

3742 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3743 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3744 VIEW=${DT_VIEW-replyblog}

3745 if [ -n "$VIEW" ]; then

3746 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3747 fi

3748 sqlfile=$tmpd/dump.sql

3749 : > $sqlfile # ensure to be empty

3750 printf '.mode html\n.header 1\n' > $sqlfile

3751 # $DT_CHLD=ChildTable:BindColumn

3752 if [ -n "$DT_CHLD" ]; then

3753 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3754 cat<<-EOF >> $sqlfile

3755 -- presql

3756 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3757 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3758 EOF

3759 # Speed up counting of new articles

3760 cat<<-EOF >> $sqlfile

3761 -- presql2

3762 DROP TABLE IF EXISTS _counts;

3763 CREATE TEMPORARY TABLE _counts AS

3764 SELECT $_i, count($_i) cnt

3765 FROM $_t GROUP BY $_i;

3766 /* Prepare NEW count table */

3767 CREATE TEMPORARY TABLE _target AS

3768 SELECT b.rowid trowid, b.id

3769 FROM "$2" b JOIN "$2_s" s

3770 ON b.id=s.id AND s.key='owner'

3771 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3772

3773 DROP TABLE IF EXISTS _children;

3774 CREATE TEMPORARY TABLE _children AS

3775 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3776 FROM (SELECT t.trowid, t.id $_i, a.id

3777 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3778 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3779

3780 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3781 DROP TABLE IF EXISTS _news;

3782 DROP VIEW IF EXISTS _news;

3783

3784 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3785 -- INSERT INTO _news

3786 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3787 CREATE TEMPORARY VIEW _news AS

3788 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3789 FROM (SELECT DISTINCT id FROM _target)

3790 a LEFT JOIN

3791 (SELECT $_i, count(ctime) newcnt

3792 FROM _children x

3793 WHERE ctime > coalesce((SELECT time from myacclog

3794 WHERE tblrowid=x.trowid),

3795 '1970-01-01')

3796 GROUP BY $_i) b

3797 ON a.id=b.$_i;

3798 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3799 EOF

3800 # REMOVE next line until 2019/5/1

3801 cntall="(select count($_i) from $_t where $_i=a.id)"

3802 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3803 # REMOVE next assignment until 2019/5/1

3804 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3805 and id in (select id from $_t where $_i=a.id) \

3806 and val > coalesce((select time from myacclog where \

3807 tblrowid=a.rowid),\

3808 '1970-01-01'))"

3809 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3810 cnt="$cntnew as '新着', $cntall as '総数',"

3811 dt_class=" td2r td3r dumpblogs"

3812 fi

3813 # Construct join expression

3814 eav="" scols=""

3815 pk=`gettblpkey $2`

3816 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3817 substr=${substr:-%s}

3818 for col in ${3:-`gettbl_s_cols $2`}; do

3819 valvar=val

3820 case $col in

3821 gecos) scols="$scols${scols:+, }${col#}"

3822 continue ;; # built-in column name

3823 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3824 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3825 case "$as" in

3826 *:*=*) cnd=${as#*:}

3827 h=${cnd%%=*} v=${cnd#*=}

3828 h=`sqlquotestr "$h"`

3829 v=`sqlquotestr "$v"`

3830 valvar="CASE val WHEN $h THEN $v END"

3831 as=${as%%:*} ;;

3832 esac

3833 ;;

3834 *) as=${col} ;;

3835 esac

3836 ss=`printf "$substr" "$valvar"`

3837 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3838 scols="$scols${scols:+, }b.$as"

3839 done

3840 #case author when '$user' then a.rowid else '---' end as ID,

3841 if [ -n "$DT_SQL" ]; then

3842 echo "$DT_SQL"

3843 else

3844 cat<<-EOF

3845 SELECT a.rowid as LINK, $cnt $scols

3846 FROM $2 a LEFT JOIN

3847 (SELECT $pk,$eav,

3848 max(CASE key

3849 WHEN 'owner'

3850 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3851 as gecos

3852 FROM ${2}_s c GROUP BY $pk)

3853 b ON a.$pk=b.$pk $4;

3854 EOF

3855 fi >> $sqlfile

3856 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3857 sqlog<<-EOF

3858 *** SQL-file: $sqlfile ***

3859 `cat $sqlfile`

3860 EOF

3861 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3862 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3863 cat $sqlfile >> tmp/sql

3864 # query ".read $sqlfile" > $tmpd/foo

3865 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3866 cp $tmpd/foo tmp/

3867 err "----- `gdate +%FT%T.%3N` ------------555555"

3868 ## $ddd LINE exists at the end of this function

3869 fi

3870 printf '.mode list\n.header 0\n' >> $sqlfile

3871 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3872 <div>

3873 <div class="dumptable">

3874 <table class="b$dt_class">

3875 `query ".read $sqlfile"`

3876 </table>

3877 </div>

3878 </div>

3879 EOF

3880 ### `query ".read $sqlfile"`

3881 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3882

3883 ## REMOVE THIS!

3884 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3885 }

3886

3887 clean-orphaned() {

3888 # This shoud be done by foreign_key rules, but some db lack them

3889 query<<-EOF

3890 -- Find blogs that have no parent

3891 WITH orphanedblog AS (

3892 SELECT blog.id,val FROM blog JOIN blog_s bs

3893 ON blog.id=bs.id AND key='owner'

3894 WHERE val NOT IN (SELECT gname FROM grp)

3895 AND val NOT IN (SELECT name FROM user)

3896 ) -- Remove them

3897 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

3898

3899 -- Find articles that have no parent blog

3900 WITH orphanedarticle AS (

3901 SELECT id FROM article

3902 WHERE blogid NOT IN (SELECT id FROM blog)

3903 ) -- Remove them

3904 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

3905 EOF

3906 }

3907

3908 clearcachedir() (

3909 td=`getcachedir "$1"`

3910 err td=$td: ls- `ls $td`

3911 if [ -w "$td/image.1" ]; then

3912 err Removing td=$td

3913 rm -fr $td/ # Clear icon-image cache!

3914 fi

3915 )

3916

3917 par2table() (

3918 # copy current parameters of par into destination table

3919 # $1=definition-file

3920 # Using $user and $session

3921 # Return value:

3922 # 0: Stored successfully

3923 # 1: Insufficient fillings

3924 # 2: No permission to modify the record

3925 # 3: Invalid rowid

3926 # 4: SUCCESS to delete

3927 # 5: Stop deletion for lack of confirm check

3928 # 6: Password length too short

3929 # 7: Password mismatch

3930 # 8: Old password incorrect

3931 # 9: Duplicated post

3932 rowid=`getpar rowid`

3933 if [ ! -e $1 ]; then

3934 echo "テーブル定義ファイルが見付かりません" | html p

3935 exit 1

3936 fi

3937 tbl=${1%.def}

3938 tbl=${tbl##*/}

3939 if [ -n "$rowid" ]; then # Modify existing entry

3940 if [ x"$tbl" = x"user" ]; then

3941 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3942 elif [ x"$tbl" = x"grp" ]; then

3943 sql="select gname from $tbl where rowid=$rowid;"

3944 ##err p2t:grp:q $sql

3945 isgrpowner "$user" "`query $sql`" && rowowner=$user

3946 elif [ x"$tbl" = x"blog" ]; then

3947 # Check if owner in blog_s

3948 blogowner=`getvalbyid blog owner "$rowid"`

3949 if isgrpowner "$user" "$blogowner"; then

3950 rowowner=$user

3951 else

3952 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3953 fi

3954 else

3955 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3956 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3957 fi

3958 ### err rowowner=$rowowner

3959 if [ x"$user" != x"$rowowner" ]; then

3960 echo "他人のレコードはいじれないの" | html p

3961 return 2

3962 elif [ -z "$rowowner" ]; then

3963 echo "指定したレコードはないみたい" | html p

3964 return 3

3965 fi

3966 rm=`getpar rm` cfm=`getpar confirm`

3967 # Editing existent entry

3968 if [ x"$rm" = x"yes" ]; then

3969 if [ x"$rm$cfm" = x"yesyes" ]; then

3970 query "delete from $tbl where rowid=$rowid;"

3971 clearcachedir "$tbl/$rowid"

3972 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

3973 clean-orphaned

3974 fi

3975 return 4

3976 else

3977 echo "消去確認のチェックがないので消さなかったの..." | html p

3978 return 5

3979 fi

3980 fi

3981 fi

3982

3983 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3984 if [ -n "$rowid" ]; then

3985 # Update of existing record

3986 for col in `gettblcols $tbl`; do

3987 val=`getparquote $col`

3988 [ -z "$val" ] && continue

3989 ## err query "update $tbl set $col=$val where rowid=$rowid"

3990 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3991 sql="update $tbl set $col=$val where rowid=$rowid;"

3992 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3993 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3994 ## User name cannot be changed with interface provided with this

3995 ## script. But we offer the trigger to change owner user

3996 ## of blog_s table.

3997 #err "select quote($col) from $tbl where rowid=$rowid;"

3998 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3999 cat<<-EOF | query

4000 -- Here we cannot use BEGIN-COMMIT because groupupdate()

4001 -- should use EXCLUSIVE transaction outside of this.

4002 SAVEPOINT par2table;

4003 $sql

4004 update blog_s set val=$val

4005 where key='owner' and val=$old;

4006 RELEASE SAVEPOINT par2table;

4007 EOF

4008 ## XX: DIRTY Hack Ends here

4009 ## We should keep blog's owner as a single column which has

4010 ## foreign key constraint with primary key of grp/user.

4011 else

4012 query "$sql"

4013 fi

4014 done

4015 # Then, set up $pval for further insertion of tbl_s and tbl_m

4016 for col in `gettblpkey $tbl`; do

4017 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

4018 pval="$pval${pval:+, }\"$val\""

4019 done

4020 else

4021 # New entry

4022 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

4023 # Avoid empty repost of article.

4024 if [ x"$tbl" = x"article" ]; then

4025 # If rowid is empty and ID exists in article-table, that is REPOST!

4026 aid=`getpar id`

4027 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

4028 if [ -n "$xaid" ]; then

4029 # REPOST of article

4030 html p <<-EOF

4031 書き込み直後のリロードなので上書きを回避します。

4032 最新記事は末尾の「再読み込み」ボタンから見てください。

4033 EOF

4034 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

4035 return 9 # STOP Duplicated posting

4036 fi

4037 fi

4038 # Generate values() for primary keys

4039 for col in `gettblpkey $tbl`; do

4040 # Genuine primary keys for _m and _s

4041 val=`getvalquote $tbl $col`

4042 [ -z "$val" ] && continue

4043 pval="$pval${pval:+, }$val"

4044 done

4045 ##err pval=$pval

4046 for col in `gettblfkey $tbl`; do

4047 # args for values() to insertion into master table

4048 val=`getvalquote $tbl $col`

4049 [ -z "$val" ] && continue

4050 formaster=$formaster"${formaster:+, }$val"

4051 done

4052 formaster="$pval${formaster:+, }$formaster"

4053 ## err formaster=$formaster

4054 if [ -z "$formaster" ]; then

4055 echo "項目を全て埋めてください" | html pre

4056 return 1

4057 fi

4058 ## err "replace into $tbl values($formaster);"

4059 query "replace into $tbl values($formaster);"

4060 ## Insertion to master table, done

4061 fi

4062

4063 transaction=$tmpd/sqlfile.sql; touch $transaction

4064 for kt in s m; do

4065 tb2=${tbl}_$kt

4066 for col in `gettbl_${kt}_cols $tbl`; do

4067 ptype=`getpartype $col "limit 1"`

4068

4069 # First, check update of existing entries in _m

4070 if [ $kt = m ]; then

4071 # sessID|address.1.22|string|Somewhere-x.y.z

4072 sql=""

4073 ##err dots from query "select var from par where var like '$col.%';"

4074 for v in `query "select var from par where var like '$col.%' AND sessid='$session';"`; do

4075 # v=address.1.22

4076 st_rowid=${v##*.}

4077 origcol=${v%%.*} # original column derived from

4078 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

4079 ##case `getpartype $v` in

4080 ## err CASE `gettbl_coltype $tbl/$origcol` in

4081 ## err edit flag = `getpar action.$v`

4082 case `getpar action.$v` in

4083 rm)

4084 if [ x`getpar confirm.$v` = x"yes" ]; then

4085 newsql="delete from $tb2"

4086 else

4087 echo "削除確認未チェック" | html p

4088 fi ;;

4089 edit)

4090 case `gettbl_coltype $tbl/$origcol` in

4091 image|document|binary)

4092 file=$tmpd/`getparfilename $v`

4093 if [ ! -s "$file" ]; then # Maybe stupid REPOST

4094 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

4095 continue

4096 fi

4097 ## err type=file=$file

4098 [ -z "$file" ] && continue

4099 bn=`sqlquotestr "${file##*/}"`

4100 bin="X'"$(hexize "$file")"'"

4101 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

4102 type=\"file:$ct\"

4103 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

4104 cachedir=`getcachedir "$tbl/$rowid"`

4105 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

4106 rm -rf $cachedir

4107 ;;

4108 *)

4109 newsql="update $tb2 set val=(select val from par where var \

4110 like '$col.%.$st_rowid' AND sessid='$session')"

4111 ;;

4112 esac

4113 ;;

4114 mv)

4115 # regularize filename and strip directory part

4116 newname=`getpar mv.$v|tr -d '":;#<>?^%$!'|tr -d "'"|tr ' ' _`

4117 newname=`basename $newname`

4118 oldname=`query "SELECT val FROM $tb2 WHERE rowid=$st_rowid;"`

4119 oldext=`expr "$oldname" : '.*\.$.*$'`

4120 newext=`expr "$newname" : '.*\.$.*$'`

4121 err "p2t(mv): oldname=$oldname $oldext -> newname($v)=$newname $newext"

4122 if [ -n "$newname" -a x"$oldext" = x"$newext" ];

4123 then

4124 newsql="UPDATE $tb2 SET val='$newname'"

4125 else

4126 html p<<-EOF

4127 $newname は取り扱えないファイル名です。

4128 空白を含まない名前にして下さい。

4129 拡張子の変更もできません。

4130 EOF

4131 continue

4132 fi

4133 ;;

4134 *) # maybe "keep", do not modify value

4135 continue

4136 ;;

4137 esac

4138 # err newsql=$newsql

4139 sql=$sql$nl"$newsql where rowid=$st_rowid;"

4140 clearcachedir "$tbl/$rowid"

4141 done

4142

4143 if [ x"$bin" = x"NULL" ]; then

4144 ## err repl:normal sql=`echo $sql`

4145 if [ -n "$transaction" ]; then

4146 cat<<-EOF >> $transaction

4147 $sql

4148 DELETE FROM $tb2 WHERE type='string' AND val='';

4149 EOF

4150 else

4151 query "$sql

4152 delete from $tb2 where type='string' and val='';"

4153 ## err repl:normal done

4154 fi

4155 else

4156 # Binary update line is TOO LONG to pipelining

4157 sqlfile="$tmpd/sqlf.$$"

4158 if [ -n "$transaction" ]; then

4159 echo "$sql" >> $transaction

4160 else

4161 echo "$sql" > $sqlfile

4162 query ".read $sqlfile"

4163 fi

4164 fi

4165 # Rest of kt==m: set multiple mode

4166 nr=`getparcount $col`

4167 else

4168 nr=1 # for kt==s, number of records is 1

4169 fi

4170

4171 i=0

4172 while [ $i -lt $nr ]; do

4173 limit="limit 1 offset $i"

4174 i=$((i+1)) # increase beforehand against continue

4175 val=`getvalquote $tbl $col "$limit"`

4176 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4177 ## err $col=$val

4178 bin=NULL

4179 ## err partype$col=`getpartype $col "$limit"`

4180 ptype=`getpartype $col "$limit"` # partype should be obtained each time

4181 case $ptype in

4182 file) file=$tmpd/`getparfilename $col "$limit"`

4183 ## err parfile-$col=$file

4184 [ -z "$file" ] && continue

4185 bin="X'"$(hexize "$file")"'"

4186 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4187 type=\"file:$ct\" ;;

4188 "*"*) continue ;; # foreign table

4189 *) type=\"string\" ;;

4190 esac

4191 case `gettbl_coltype $tbl/$col` in

4192 password) # special care for password

4193 # name={password,pswd1,pswd2}

4194 p1=`getpar pswd1 "$limit"`

4195 if [ -z "$p1" ]; then

4196 continue # SKIP password setting, if p1 is empty

4197 else

4198 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4199 ## err pswd=$pswd

4200 if pwcheck "$pswd"; then

4201 if [ x"$p1" = x"$p2" ]; then

4202 case "$p1" in

4203 ??????????*) ;;

4204 *) echo "パスワードは10字以上にしてください。" | html p

4205 return 6;;

4206 esac

4207 val="\"`echo $p1|mypwhash`\""

4208 else

4209 echo "2つの新パスワード不一致" | html p

4210 return 7

4211 fi

4212 else

4213 echo "旧パスワード違います" | html p

4214 return 8

4215 fi

4216 fi

4217 ;;

4218 esac

4219 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4220 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4221 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4222 if [ x"$bin" = x"NULL" ]; then

4223 ## err Normal-query: `echo $sql`

4224 if [ -n "$transaction" ]; then

4225 echo "$sql" >> $transaction

4226 else

4227 query "$sql"

4228 fi

4229 else

4230 sqlfile="$tmpd/query.$$"

4231 ## err sqlfile=`ls -lF $sqlfile`

4232 if [ -n "$transaction" ]; then

4233 echo "$sql" >> $transaction

4234 else

4235 echo "$sql" > $sqlfile

4236 query ".read $sqlfile"

4237 fi

4238 fi

4239 ## err p2t done

4240 done

4241 done

4242 done

4243 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4244 -- We cannot use transaction here, because groupupdate may use it.

4245 SAVEPOINT pa2table_insert;

4246 .read $transaction

4247 RELEASE SAVEPOINT pa2table_insert;

4248 EOF

4249 rc=$?

4250 if [ $rc -eq 0 -a x"$tbl" = x"user" ]; then

4251 # XXX: Sorry to use undeclared column in user.def

4252 # This is useful to mitigate account sync load

4253 query <<-EOF

4254 REPLACE INTO user_s(name, key, type, val)

4255 VALUES('$user', 'profupdate', 'string', datetime('now', 'localtime'));

4256 EOF

4257 touch $userupdateflag

4258 fi

4259 ## err "Table:$tbl update done "

4260 return $rc

4261 )

4262 genform() {

4263 # $1 = form definition file

4264 # $2, $3 (optional)= table name and ROWID

4265 # If $GF_VIEWONLY set and nonNull, output values without form

4266 # If $GF_ARGS set, use it as content-strings in the form

4267 # If $GF_OWNER set, use it as value of name="owner"

4268 # If $GF_STAGE set, use it as value of name="stage"

4269 forms="" hiddens="" rowid=$3

4270 if [ ! -e "$1" ]; then

4271 echo "そのようなデータベースはないようです($2)。" | html p

4272 return

4273 elif [ -n "$2" ]; then

4274 rec=`query "select * from $2 where rowid='$rowid';"`

4275 if [ -z "$rec" ]; then

4276 pk=`gettblpkey $2`

4277 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4278 rec=`query "select rowid from $2 where $pk='$rowid';"`

4279 rowid=$rec

4280 rec=$3

4281 fi

4282 if [ -z "$rec" ]; then

4283 echo "そんなレコードはないみたいね..." | html p

4284 return

4285 fi

4286 fi

4287 if [ -z "$GF_VIEWONLY" ]; then

4288 rm='<input id="rm" name="rm" type="checkbox"

4289 value="yes"><label for="rm">このエントリの削除</label>

4290 <span>ほんとうに消しますよ(確認)!

4291 <input name="confirm" type=checkbox value="yes">はい</span>'

4292 fi

4293 # Image Cache dir

4294 ## err genform: getcache=$2/$rowid

4295 td=`getcachedir "$2/$rowid"`

4296 while IFS=: read prompt name keytype type args; do

4297 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4298 sp="${args:+ }"

4299 form="" val=""

4300 if [ -n "$rowid" ]; then

4301 # err genform2a: Seeking for "$2.$name, type=$type"

4302 rawval=`getvalbyid $2 $name $rowid $td`

4303 val=`echo "$rawval"|htmlescape`

4304 ## err genform3a: getvalbyid $2 $name $rowid $td

4305 ## err genform3b: val="[$val]" type="$type"

4306 fi

4307 if [ -n "$GF_VIEWONLY" ]; then

4308 is_hidden "$2" "$name" && continue

4309 fi

4310 case "$type" in

4311 text*)

4312 cgiform=cgi_multi_$type

4313 if [ -s $td/$name.count -a -n "$val" ]; then

4314 form=`$cgiform $name $td`

4315 val=$(echo "$val"|

4316 while read fn; do

4317 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4318 </td></tr>$nl"

4319 done)

4320 val="<table>$nl$val$nl</table>"

4321 else

4322 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4323 form=`cgi_$type $name "$rawval" "$args"`

4324 fi

4325 ;;

4326 [Rr][Aa][Dd][Ii][Oo])

4327 fh="<label><input type=\"radio\" name=\"$name\""

4328 form="`echo $args|sed -e \

4329 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4330 ;;

4331 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4332 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4333 ;;

4334 [Ss][Ee][Ll][Ee][Cc][Tt])

4335 fh="<select name=\"$name\">$nl"

4336 form=$(for l in $args; do

4337 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4338 done)

4339 if [ -n "$val" ]; then

4340 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4341 fi

4342 form="$fh$form</select>"

4343 ;;

4344 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4345 if [ -s $td/$name.count ]; then

4346 form=`cgi_multi_file $name $td "$args"`

4347 if [ -n "$val" ]; then

4348 hrfb="$myname?showattc+$2_m"

4349 val=$(echo "$rawval" \

4350 | while read fn; do

4351 data=`percenthex "$td/$fn"`

4352 #ct=`cat $td/$fn.content-type`

4353 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4354 ri=`cat "$td/$fn.rowid"`

4355 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4356 #imgsrc="<img src=\"data:$ct,$data\">"

4357 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4358 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4359 done)

4360 fi

4361 else

4362 form="<input type=\"file\" name=\"$name\" $args>"

4363 if [ -n "$val" ]; then

4364 imgs=$(echo "$rawval"\

4365 |while read fn;do

4366 data=`percenthex "$td/$fn"`

4367 echo "<img src=\"data:image/png,$data\">$fn<br>"

4368 done)

4369 form=$form"<br>$imgs"

4370 val=$imgs # 2015-06-15

4371 else

4372 form="<input type=\"file\" name=\"$name\" $args>"

4373 fi

4374 fi

4375 ;;

4376 [Hh][Ii][Dd][Dd][Ee][Nn])

4377 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4378 args="value=\"$GF_STAGE\""

4379 fi

4380 form="<input type=\"hidden\" name=\"$name\" $args>"

4381 prompt='' # Remove prompt

4382 ;;

4383 [Aa][Uu][Tt][Hh][Oo][Rr])

4384 [ -n "$GF_VIEWONLY" ] && continue

4385 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4386 prompt="" ;;

4387 [Oo][Ww][Nn][Ee][Rr])

4388 [ -n "$GF_VIEWONLY" ] && continue

4389 val=${GF_OWNER:-$val}

4390 val=${val:-$user}

4391 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4392 prompt="" ;;

4393 [Uu][Ss][Ee][Rr])

4394 # XXX: is null $user ok?

4395 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4396 [ -n "$GF_VIEWONLY" ] && continue

4397 form="$user"

4398 ;;

4399 [Pp]assword)

4400 [ -n "$GF_VIEWONLY" ] && continue

4401 form="`cgi_passwd`"

4402 val=""

4403 ;;

4404 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4405 [ -n "$GF_VIEWONLY" ] && continue

4406 if [ -z "$rowid" ]; then

4407 val=`genserial`

4408 fi

4409 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4410 prompt="" ;;

4411 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4412 prompt=""

4413 ;;

4414 parent|path|blog*)

4415 prompt=""

4416 ;;

4417 "*"*)

4418 tail=$tail"``"

4419 continue ;;

4420 esac

4421 if [ -n "$prompt" ]; then

4422 if [ -n "${GF_VIEWONLY}" ]; then

4423 form=$val

4424 else

4425 :

4426 fi

4427 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4428 else

4429 hiddens=$hiddens$nl"$form"

4430 fi

4431 done < $1

4432 # enctype="multipart/form-data"

4433 cat<<EOF

4434 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4435 ${rowid:+$rm}

4436 <table class="b $2">

4437 $forms

4438 </table>$hiddens

4439 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4440 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4441 EOF

4442 if [ -z $GF_VIEWONLY ]; then

4443 cat<<EOF

4444 <input type="submit" name="sub" value="OK">

4445 <input type="reset" name="res" value="Reset">

4446 EOF

4447 fi

4448 cat<<EOF

4449 $GF_ARGS</form>

4450 $tail

4451 EOF

4452 }

4453 edittable() {

4454 # $1=form-def $2=table $3 rowid

4455 genform "$@"

4456 }

4457 viewtable() {

4458 GF_VIEWONLY=1 genform "$@"

4459 }

4460 showattc() {

4461 # $1=table_m $2=rowid &optional $3=RawFlag

4462 ## err \$1=$1 \$2=$2 \$3=$3

4463 if ! isfilereadable $user $1 $2; then

4464 contenttype; echo

4465 echo "このファイルは管理者のみしか見られません" | html p

4466 putfooter; exit

4467 fi

4468 idir=`umask 002; mktempd` || exit 1

4469 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4470 bin=$idir/$myname-$$.bin

4471 sql="select quote(bin) from $1 where rowid='$2';"

4472 ## err showattc: sql: $sql

4473 sq $db "$sql" | unhexize > $bin

4474 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4475 type=${tv%//*} fn=${tv#*//}

4476 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4477 ct=${type#file:}

4478 case $ct in # all text/* changed to text/plain

4479 text/*)

4480 charset=`nkf -g $bin|cut -d' ' -f1`

4481 case $charset in

4482 ASCII*) charset="" ;;

4483 esac

4484 if [ -z "$3" ]; then

4485 ct="text/html${charset:+; charset=$charset}"

4486 link="?showattc+$1+$2+raw"

4487 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4488 | sed 's,^,<span></span>,' \

4489 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4490 -D_LINK_="$link" \

4491 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4492 exit $?

4493 fi

4494 ct="text/plain${charset:+; charset=$charset}"

4495 ;;

4496 esac

4497 contenttype "$ct"

4498 echo "Content-Disposition: filename=\"$fn\""

4499 echo "Content-Length: " `cat $bin | wc -c`; echo

4500 #echo "Content-Type: " ${type#file:}; echo

4501 cat $bin

4502 }

4503 #

4504 # Some default stupid handler on CGI values

4505 #

4506 default_storedb() {

4507 # ARG: $1=table-def-file

4508 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4509 tbl=`basename $1`

4510 tbl=${tbl%.def}

4511 cols="`grep :text $1|cut -d: -f2`"

4512 col=`echo "$cols"|head -1`

4513 vcol=`getpar $col`

4514 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4515 if [ -n "$vcol" ]; then

4516 par2table $1

4517 else

4518 return 2 # No insertion occurred

4519 fi

4520 }

4521

4522 default_view() { # $1=def-file

4523 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4524 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4525 default_storedb "$@"

4526 query "select rowid from $tbl order by rowid desc;" \

4527 | while read rowid; do

4528 viewtable $1 $tbl $rowid

4529 done | _m4 -D_TITLE_="$tbl" \

4530 -D_FORM_="`genform $1`" \

4531 -D_DUMPTABLE_="syscmd(cat)" \

4532 $layout/html.m4.html $layout/form+dump.m4.html

4533 }

4534 default_viewtext() { # $1=def-file

4535 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4536 default_storedb "$@"

4537 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4538 | _m4 -D_TITLE_="$tbl" \

4539 -D_FORM_="`genform $1`" \

4540 -D_DUMPTABLE_="syscmd(cat)" \

4541 $layout/html.m4.html $layout/form+dump.m4.html

4542 }

4543 default_smail() {

4544 default_storedb "$@"

4545 if [ $? -eq 2 ]; then

4546 _m4 -D_TITLE_="入力" \

4547 -D_FORM_="`genform $1`" \

4548 -D_DUMPTABLE_="" \

4549 $layout/html.m4.html $layout/form+dump.m4.html

4550 return

4551 fi

4552 cond=""

4553 for pk in `gettblpkey $tbl`; do

4554 pv=$(sqlquote "$(getpar $pk)")

4555 cond="$cond${cond:+ and }$pk=$pv"

4556 done

4557 sql="select rowid from $tbl where $cond;"

4558 rowid=`query "$sql"`

4559 ## err smail1 - "$sql" "-> rowid=$rowid"

4560

4561 while IFS=: read prompt name keytype type args; do # Read from $1

4562 val=`getpar $name`

4563 if [ -n "$val" ]; then

4564 text="$text

4565 $prompt

4566 $name=$val

4567 ---------------------------------------------------------"

4568 fi

4569 case "$type" in

4570 image|document|file)

4571 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4572 fns=$(echo "$fn"|while read fn; do

4573 err mv $tmpd/$fn.orig $tmpd/$fn

4574 mv $tmpd/$fn.orig $tmpd/$fn

4575 rm $tmpd/$fn.rowid # Remove cache flag

4576 ## err "`ls $tmpd/$fn`"

4577 echo $fn

4578 done)

4579 files="$files $fns"

4580 ;;

4581 esac

4582 done < $1

4583 ## err FILES=$files "`ls -lF $tmpd`"

4584 subj="from ${REMOTE_ADDR}"

4585 (echo "$url"

4586 echo "への書き込みがありました。"

4587 echo "------"

4588 echo "$text"

4589 ) | (cd $tmpd &&

4590 err LS="`ls -lF`" &&

4591 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4592 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4593 echo "以下の内容で送信しました。" | html p

4594 viewtable $1 $tbl \

4595 `query "select rowid from $tbl order by rowid desc limit 1;"`

4596 echo "戻る" | html a "href=\"?\""

4597 }