s4: c892a3633baa s4-funcs.sh

s4

view s4-funcs.sh @ 739:c892a3633baa

Annex-test: sessdb = main-sessdb

author	HIROSE Yuuji <yuuji@gentei.org>
date	Thu, 04 Jun 2020 20:31:25 +0900
parents	d78944aa1cd2
children	f2c7117f452e

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 myname=`basename ${SCRIPT_NAME:-$0}`

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

9 myargs="$@"

10 test -n "$HTTP_HOST" && isCGI=true

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

12 tmpdir=${TMPDIR:-tmp}

13 dbdir=${DBDIR:-db}

14 tmpfiles=""

15 querylog=${QUERYLOG:-$tmpdir/query.log}

16 searchlog=${SEARCHLOG:-$tmpdir/search.log}

17 db=${DB:-$dbdir/cgi.sq3}

18 sessdb=$dbdir/sess.sq3

19 userupdateflag=$dbdir/userupdate

20 sesstb=tmp.sess

21 workdb=$dbdir/tmpdata.sq3

22 listentlimit=${LISTENTLIMIT:-30}

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

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

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

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

27 templ=${TEMPL:-templ}

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

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

30 imgdir=${IMGDIR:-img}

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

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

33 msgdir=$templ/msg

34 timeout="+2 days"

35 memoplimitdays="7"

36 dumpcollen=22

37 #thumbxy=120x120

38 thumbxy=96x96

39 iconxy_S=80x80

40 iconxy_M=400x400

41 maximagexy=1600x1600

42 ### maximagexy=400x400

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

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

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

46 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,.txt,.zip,.xcf,.bz2,.gz,.xz,.7z,.csv,.dat"'

48 file_accept_help="

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

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

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

52 "

53 file_warn="$file_accept_help

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

55 blogreadflagrowid=0

56 blogcutoffflagrowid=-1

57 whatsnewdays=${WHATS_NEW_DAYS:-14}

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

59 session=$main_session

61 tconfs=""

62 imgcached=cache/img.`date +%Y/%m`

63 conftbl=_tblconf

64 nl="

65 "

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

67 iconcachekey="profimgcache_S"

69 # Start debug logging

70 exec 3>> $tmpdir/debug.out

71 err() {

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

73 }

74 case "$HTTP_USER_AGENT" in

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

76 *) touchpanel="" ;;

77 esac

79 # If S4MASTERDB is set, behave as ANNEX s4

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

81 # $S4ANNEX is set after s4-annex setup

82 . ./s4-annex.sh 2>> tmp/debug.out

83 err S4ANNEXES="$S4ANNEXES" S4ANNEXNAME=$S4ANNEXNAME

84 elif [ -n "$S4ANNEXES" ]; then

85 # Master Site mode, which has annexes

86 v=""

87 for i in $S4ANNEXES; do

88 _aname=${i%:*}; _apath=${i##*:}

89 v=${v}"<tr><td>⇒ <a href=\"$_apath\">$_aname</a></td></tr>$nl"

90 done

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

92 i="<tr><th title=\"Annex\">別館</a></th></tr>$nl"

93 S4ANNEXES="${touchpanel:+ : ▼}<div><table>$i$v</table></div>"

94 fi

95 fi

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

100 : <<EOF

101

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

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

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

105 レポート提出管理

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

107

108 [2016]

109 7/12 根本への反省

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

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

112

113 [以下2015]

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

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

116 Web

117 締切設定

118

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

120 ○自分の提出物リスト

121

122 7/19 ○設置

123 ○一斉送信

124 ○getparfilename の tmpd の扱い

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

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

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

128 ○添付ファイル回収

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

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

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

132

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

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

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

136 ○レポート提出モード

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

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

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

140

141

142 7/9 ○管理者の追加

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

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

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

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

147

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

149

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

151 ○グループ検索

152

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

154

155

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

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

158

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

160 ○6/1 par2tableを triplex 対応に

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

162 →とすると一気に

163

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

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

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

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

168

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

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

171 ## 1. passwd

172 ## 2. grp

173 ## 3. grp_mem

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

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

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

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

178

179 ## ● listの定義:

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

181 ## ● hashの定義:

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

183

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

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

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

187 ## csv2sq3 で .csv.sq3 の Makefile

188

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

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

191 ## type := root | comment

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

193 ## unique(id, filename)

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

195

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

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

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

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

200 ## できたー!

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

202 ↓

203 ↓以下に変更

204 with recursive allmem as

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

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

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

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

209

210

211 with recursive allmem as

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

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

214 where allmem.val=grp_m.gname)

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

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

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

218

219

220 ## triggerもできた。

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

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

223

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

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

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

227 ## グループ管理

228 ## 個人情報管理

229 ##

230

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

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

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

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

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

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

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

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

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

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

241

242 ## ログテーブル

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

244

245

246

247 ■表設計

248 * 3つの表に分散管理

249 id格納表 + hash表 + list表

250 * *_s *_m

251

252

253

254 user, user_map, user_col

255

256 ■抽象エントリタイプ

257 * user

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

259 * group

260 権限判定に利用

261 * serial

262 自動idとして機能

263 * password

264 入力 type=passwordで入力

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

266 * session

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

268 * text

269 入力 type=text

270 * textarea

271 入力 textarea

272 * image|document

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

274 * owner

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

276 * gowner

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

278 * timestamp

279 datetime()

280 * parent

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

282 * path

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

284

285 格納タイプ

286 * list

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

288 * hash

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

290

291 オブジェクトタイプ

292 * entry

293 id, title, owner

294 * textpart

295 id, parentID, text

296 * binarypart

297 id, parentID, contenttype, filename, content

298 * content

299 hash(textpart), list(binarypart)

300 * topic

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

302 * reply

303 id, parentID, content

304 * blog

305 list(entry)

306 blog = [topic, list(reply)]

307

308

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

310 "text" => "hogehoge ..",

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

312 "author" => "taro",

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

314 "parent" => "/",

315 "path" => "/1",

316 "text" => "blah, blah, ....",

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

318 {"serial" => 2,

319 "author" => "hanako",

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

321 "parent" => "/",

322 "path" => "/2",

323 "text" => "blah, blah, ....",

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

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

326

327

328 user:=

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

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

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

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

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

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

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

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

337

338 変換表

339 /user/email=m

340

341 blog:=

342 シリアル:id:p:serial

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

344 所有者:owner:s:owner:

345 時刻:ctime:s:stamp:

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

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

348

349 article:=

350 シリアル:id:p:serial

351 筆者:author:s:owner

352 時刻:ctime:s:stamp:

353 参照元:parent:s:parent:

354 パス:path:s:path:

355 本文:text:s:textarea:

356 画像:image:m:image:

357

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

359

360

361 EOF

362

363 logstart() {

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

365 }

366 logend() {

367 echo ">>>" >> ${1:-$querylog}

368 }

369 sqlog() {

370 logstart

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

372 cat >> $querylog

373 else

374 echo "$*" >> $querylog

375 fi

376 logend

377 }

378 sq() {

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

380 logstart

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

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

383 else

384 echo "$@" >> $querylog

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

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

387 fi

388 logend

389 }

390 dbsetup() {

391 pipedir=$tmpdir/pipedir

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

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

394 suf=`date +%s`

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

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

397 mkfifo $sqi $sqo

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

399 sq $db < $sqi &

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

401 if [ -n "$isCGI" ]; then

402 exec 2>> $tmpdir/error.out

403 fi

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

405 chmod o-r $tmpdir/error.out $tmpdir/debug.out

406 rm $sqi

407 # Attach supplemental DB

408 cat >&5 <<-EOF

409 .output /dev/null

410 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

414 .output stdout

415 EOF

416 }

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

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

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

420 fi

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

422 }

423 cleanup() {

424 trap '' INT HUP EXIT TERM PIPE

425 echo .quit >&5

426 kill $sq3pid

427 kill $sq3pid

428 rm -f $sqo $sqi

429 rm -rf $tmpfiles

430 cleanup2

431 }

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

433 # simple redirection for the sake of speed.

434 query() {

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

436 echo ".output $sqo" >&5

437 logstart

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

439 tee -a $querylog

440 else

441 echo "$@" >> $querylog

442 echo "$@"

443 fi >&5

444 echo ".output stdout" >&5

445 cat $sqo

446 rc=$?

447 logend

448 return $rc

449 }

450 _m4() {

451 #S4NAME=f,f,f

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

453 -D_S4ANNEXES_="${S4ANNEXES}" "$@"

454 }

455 ismember() {

456 # $1=user, $2=group

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

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

459 }

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

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

462 }

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

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

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

466 }

467 isgrpowner() (

468 # $1=user, $2=group

469 gn=`sqlquote "$2"`

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

471 err isgrpowner: $sql

472 test -n "`query $sql`"

473 )

474 isgrpownerbygid() (

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

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

477 err isgrpownerbygid: $sql

478 test -n "`query $sql`"

479 )

480 getgroupadminmails() {

481 # $1=group

482 for i in $(getgroupadmins $1); do

483 email4group "$1" "$i" ;

484 done

485 }

486 getgroupadmins() { # $1=group

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

488 qgrp=`sqlquote "$1"`

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

490 }

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

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

493 getvalbyid grp $2 \

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

495 }

496 getgroupbyid() {

497 # $1=id|gname

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

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

500 # err ggbyid: `echo $sql`

501 query $sql

502 }

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

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

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

506

507 # Return true when anonymous mode

508 [ "$anonymousmode" ] && return 0

509 # case `getvalbyid blog mode $2` in

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

511 # *closed*)

512 # owner=`getvalbyid blog owner $2`

513 # if isgrp $owner; then

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

515 # elif isuser $owner; then

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

517 # fi

518 # esac

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

520 sql="with getblog as (

521 select key,val from blog_s where id=(

522 select blogid from article where id in

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

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

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

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

527 isgrpadm as (select user from grp_adm where

528 gname=(select val from getowner) and

529 user='$1'),

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

531 select case

532 when (select author from article where

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

534 then 'author'

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

536 then 'open'

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

538 then CASE

539 WHEN (SELECT val FROM isgrp) IS NULL

540 THEN

541 CASE WHEN (SELECT val from getowner)

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

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

544 ELSE ''

545 END

546 WHEN (select user from isgrpadm) IS NOT NULL

547 THEN 'i-am-admin'

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

549 END

550 when (select val from getmode) is null

551 then 'open'

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

553 then (SELECT user FROM isgrpadm)

554 when (select author from article where

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

556 then 'user+author'

557 else '' end;"

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

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

560 # result=`query "$sql"`

561 # err FileAccessibility=$result

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

563 }

564 linkhome() {

565 # $1=UserOrGroup

566 echo -n '<a href="?'

567 if isuser $1; then

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

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

570 else

571 echo -n "grp+$1"

572 fi

573 echo "\">`gecos $1`</a>"

574 }

575 hreflink() {

576 # s4 specific notation:

577 # ^href=URL

578 # ^iframe=URL

579 # ^video=URL

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

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

582 # OSM umap Wikistyle Notation:

583 # [[URL]] - Simle Link

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

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

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

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

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

589 # Other Style

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

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

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

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

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

595 # SPC+SPC+$ - <br>

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

597 checkboxON="${cb} checked>"

598 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

623

624 }

625 minitbl() {

626 sed -n '

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

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

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

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

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

632 H; # Concat this line to HoldSpace

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

634 $ b cont

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

636 }

637 :cont

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

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

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

641 # . of ^. is workaround for FreeBSD sed

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

643 p; # Print whole "table" element

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

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

646 }

647 x; # Back to the newest line

648 p; # Print rest' | miniul

649 }

650 miniul() {

651 sed -e '

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

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

654 :top

655 s/\n//;

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

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

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

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

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

661 $ b cont

662 N; # 次の行を読む

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

664 /^ /b top

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

666 p; s/.*//;

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

668 /^\* /b top

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

670 }

671

672 :cont

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

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

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

676 H; x

677 }

678 x'

679 }

680 acclog() (

681 # $1=table, $2=rowid

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

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

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

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

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

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

688 fi

689 )

690 gecos() (

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

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

693 )

694 setpar() {

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

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

697 case "$1" in

698 user) _user="$v" ;;

699 skey) _skey="$v" ;;

700 esac

701 fi

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

703 }

704 replpar() {

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

706 }

707 getpar() {

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

709 val=""

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

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

712 user) val=$_user ;;

713 skey) val=$_skey ;;

714 esac

715 fi

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

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

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

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

720 fi

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

722 case "$var" in

723 owner)

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

725 echo $user; return

726 elif ismember $user $val; then

727 echo $val; return

728 fi ;;

729 esac

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

731 echo "$val"

732 }

733 setskey() {

734 # For quick response...(?)

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

736 }

737 chkskey() {

738 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

744 return 0

745 fi

746 return 1

747 }

748 resetskey() {

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

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

751 fi

752 }

753 getpartype() {

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

755 }

756 getparcount() {

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

758 }

759 getparfilename() {

760 # null if type of $1 is not file

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

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

763 }

764 sqlquote() {

765 (v="$1"

766 case "$v" in

767 "") return ;; # null

768 "X'"*) # quoted hex string

769 echo $1 ;;

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

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

772 echo "\"$v\""

773 return ;;

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

775 echo "\"$v\""

776 return ;;

777 *)

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

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

780 else

781 echo "\"$v\""

782 fi ;;

783 esac)

784 }

785 sqlquotestr() (

786 case "$1" in

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

788 echo "'$v'" ;;

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

790 esac

791 )

792 mktempd() {

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

794 }

795 getcachedir() { # $1=maintable

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

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

798 else

799 echo $tmpd/$thumbxy

800 fi

801 }

802 getval() {

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

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

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

806 echo "$user" ;;

807 stamp|datetime)

808 date "+%F %T" ;;

809 serial)

810 (s=`getpar $2`

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

812 *)

813 getpar "$2" "$3";;

814 esac

815 }

816

817 getvalquote() {

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

819 (v=`getval "$@"`

820 case "$v" in

821 "") echo NULL ;;

822 *) sqlquote "$v" ;;

823 esac)

824 }

825 getparquote() {

826 sqlquote "`getpar $1`"

827 }

828 getbinbyid() {

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

830

831 }

832 getvalbyid() {

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

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

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

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

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

838

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

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

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

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

843 return

844 fi

845 done

846 rowid=$3

847 pk=`gettblpkey $1`

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

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

850 td=${4:-$tmpd}

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

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

853 for kt in s m; do

854 t=${1}_$kt

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

856 vcount=1 # count(val)

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

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

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

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

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

862 if [ $kt = m ]; then

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

864 # Reset val to store filenames if type is string

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

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

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

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

869 i=0

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

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

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

873 i=$((i+1))

874 fn=$c.$i

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

876 case $type in

877 file:*)

878 #file=$td/$val

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

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

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

882 # FOR SPEED: Skip file generation if imgcache exists

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

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

885 && continue

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

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

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

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

890 select rowid from $cond $slice;

891 .output '$td/$fn'

892 select val from $cond $slice;

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

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

895 .output stdout

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

897 EOF

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

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

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

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

902 ls -lh "$file" |

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

904 case "$type" in

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

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

907 *|*:[Aa]pplication*)

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

909 png:- > "$file"

910 ;;

911 esac

912 ;;

913 *)

914 sq $db<<EOF

915 .output $td/$fn.rowid

916 select rowid from $cond $slice;

917 .output $td/$fn

918 select val from $cond $slice;

919 EOF

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

921 ;;

922 esac

923 done

924 else

925 rm -f $td/$c.count

926 case $type in

927 file:*)

928 echo "$val" \

929 | while read fn; do

930 file=$td/$fn

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

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

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

934 | unhexize > "$file"

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

936 case $type in

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

938 *:[Aa]pplication*)

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

940 png:- > $file ;;

941 esac

942 fi

943 done

944 ;;

945 esac

946 fi

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

948 return

949 fi

950 done

951 done)

952 }

953 getvalbypkey() (

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

955 pk=`gettblpkey $1`

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

957 getvalbyid "$1" "$2" $rowid $4

958 )

959 getvalbycond() {

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

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

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

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

964 getvalbyid "$1" "$2" $rowid "$4"

965 fi

966 }

967 getpwfield() {

968 # getpwfield user column

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

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

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

972 echo "$val"

973 return 0

974 else

975 return 1

976 fi

977 }

978 numericalize() {

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

980 }

981 encode() {

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

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

984 sha1=sha1

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

986 sha1=sha1sum

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

988 sha1=gsha1sum

989 fi

990 fi

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

992 }

993 enjpeg() {

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

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

996 cjpeg="cjpeg"

997 else

998 cjpeg="convert - jpeg:-"

999 fi

1000 fi

1001 $cjpeg "$@"

1002 }

1003 mycrypt() (

1004 key=$1 salt=$2

1005 # err \$2=$2

1006 case $2 in

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

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

1009 esac

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

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

1012 )

1013 hexize() {

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

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

1016 hexize="xxd -p"

1017 else

1018 hexize_hd() {

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

1020 }

1021 hexize="hexize_hd"

1022 fi

1023 fi

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

1025 }

1026 unhexize() {

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

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

1029 unhex="xxd -p -r"

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

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

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

1033 EOF

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

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

1036 # world writable...:)

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

1038 fi

1039 fi

1040 cat "$@" | $unhex

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

1042 }

1043 percenthex() {

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

1045 }

1046 htmlescape() {

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

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

1049 }

1050 enascii() {

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

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

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

1054 else

1055 enascii_now=`date +%FT%T`

1056 enascii_sed() {

1057 nkf -Z0Z1Z2 \

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

1059 }

1060 enascii="enascii_sed"

1061 fi

1062 fi

1063 cat "$@" | $enascii

1064 }

1065 size_h() {

1066 i="$1" oi=$1

1067 set -- B B KB MB GB TB

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

1069 oi=$i

1070 i=$((i/1024))

1071 shift

1072 done

1073 echo ${oi}$1

1074 }

1075 gettblconf() {

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

1077 ## tconfs=`sq $db \

1078 tconfs=`query \

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

1080 fi

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

1082 }

1083 gettblkeys() {

1084 # $1=tbl

1085 gettblconf

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

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

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

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

1090 case $type in

1091 *p*)

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

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

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

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

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

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

1098 esac

1099 case $type in

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

1101 esac

1102 done

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

1104 }

1105 gettblpkey() {

1106 # $1=tbl

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

1108 }

1109 gettblfkey() {

1110 (x=`gettblkeys $1`

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

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

1113 }

1114 gettblcols() {

1115 (x=`gettblkeys $1`

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

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

1118 }

1119 gettbl_s_cols() {

1120 (x=`gettblkeys $1`

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

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

1123 }

1124 gettbl_m_cols() {

1125 (x=`gettblkeys $1`

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

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

1128 }

1129 gettbl_h_cols() {

1130 (x=`gettblkeys $1`

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

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

1133 }

1134 gettbl_coltype() (

1135 gettblconf

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

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

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

1139 )

1140 is_hidden() {

1141 # $1=Tbl $2=col

1142 gettblconf

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

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

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

1146 case $x in

1147 *h*) return 0 ;;

1148 *) return 1 ;;

1149 esac

1150 }

1151

1152 dbsetbyid() {

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

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

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

1156 quotedp=$(sqlquotestr "$p")

1157 unset primary update

1158 gettblconf

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

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

1161 #err conf=$conf

1162 case ${conf#*=} in

1163 p*) primary=1 ;;

1164 f*) update=1 ;;

1165 u*) ;;

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

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

1168 esac

1169 #err t=$t

1170 type=string fn=""

1171 case $conf in

1172 */password)

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

1174 ;;

1175 */image*|*/document*)

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

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

1178 ;;

1179 esac

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

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

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

1183 if [ "$primary" ]; then

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

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

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

1187 elif [ "$update" ]; then

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

1189 else

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

1191 fi

1192 )

1193 }

1194 expire() (

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

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

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

1198 )

1199 addsession() {

1200 # expireをセット

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

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

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

1204 # Remove old session parameters

1205 now=`expire now`

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

1207 }

1208 gencookie() (

1209 path=${URL#*:/}

1210 path=${URL%/*}

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

1212 for kv; do

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

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

1215 done

1216 )

1217 contenttype() {

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

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

1220 }

1221 putheader() {

1222

1223 }

1224 putfooter() {

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

1226 }

1227 getcookie() {

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

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

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

1231 err "GetCookie: $k=[$v]"

1232 case "$k" in

1233 user) _user="$v" ;;

1234 skey) _skey="$v"; k=skey ;;

1235 esac

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

1237 done

1238 }

1239 genrandom() {

1240 # $1=columns (default: 10)

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

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

1243 }

1244 genserial() {

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

1246 }

1247 smail() {

1248 # smail rcpts subj (file)

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

1250 # $MAIL_FROM <- From: header value

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

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

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

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

1255 sender=${SENDER:-$admin}

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

1257 (_m4 -D_RCPT_="${SMAIL_TO:-$rcptheader}" -D_REPLYTO_="$replyto" -D_SUBJ_="\`$subj'" -D_FROM_="$from" $msgdir/mail-header.m4

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

1259 }

1260 smail_queue_flush() {

1261 # $1=timelimit

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

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

1264 SELECT rowid FROM smailq

1265 GROUP BY rcpts, subj

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

1267 EOF

1268 )

1269 for rid in $rowids; do

1270 sq $workdb \

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

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

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

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

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

1276

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

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

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

1280 .separator "\n" "------------------\n\n"

1281 SELECT time, text FROM smailq

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

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

1284 ORDER by time;

1285 EOF

1286 then

1287 echo rowid=$rid

1288 cat <<-EOF | sq $workdb

1289 DELETE FROM smailq

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

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

1292 EOF

1293 fi

1294 fi

1295 done

1296 }

1297 smail_queue() {

1298 # $1=Rcpts, $@=subj

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

1300 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1309 EOF

1310 )

1311 ## XXX: Adhoc load mitigation

1312 case "$now" in

1313 *[01])

1314 err flush_queue=$mintime

1315 smail_queue_flush ;;

1316 esac

1317 fi

1318 }

1319 setviastring() {

1320 table=par

1321 oifs="$IFS"

1322 IFS="&"

1323 for us in $1; do

1324 k=${us%%=*}

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

1326 setpar "$k" "string" "$v"

1327 done

1328 IFS="$oifs"

1329 }

1330 checkdomain() (

1331 # Check the validity of domain by referring DNS

1332 item=$1

1333 err checkdomain $1

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

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

1336 )

1337 pwcheck() {

1338 # $1=passwd

1339 dbpswd=`getpwfield $user pswd`

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

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

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

1343 }

1344 mypwhash() {

1345 mycrypt "`cat`" `genrandom 5`

1346 }

1347 wasureta() {

1348 user=$1

1349 if ! checkdomain $user; then

1350 contenttype; echo

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

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

1353 putfooter

1354 exit 0

1355 fi

1356 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1358 encpswd=`echo $newpswd|mypwhash`

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

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

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

1362 $msgdir/mail-newaccount.m4 \

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

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

1365 }

1366 checkauth() {

1367 user=`getpar user`

1368 skc=`getpar skey` # from cookie

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

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

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

1372 if chkskey "$skc"; then

1373 return 0

1374 fi

1375 fi

1376 pswd=`getpar pswd`

1377 quser=`sqlquotestr "$user"`

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

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

1380 return 4 # 4=server too heavy

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

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

1383 return 2 # 2=login fail

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

1385 wasureta "$user"

1386 return 1 # wasureta error

1387 fi

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

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

1390 if pwcheck "$pswd"; then

1391 newsession=`genrandom 34`

1392 if setskey "$user" "$newsession" &&

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

1394 # err gencookie "user=$user" "skey=$newsession"

1395 gencookie "user=$user" "skey=$newsession"

1396 return 0

1397 else

1398 return 4 # Heavy load??

1399 fi

1400 fi

1401 err "Login failed: [$user]"

1402 return 2 # Password mismatch

1403 }

1404 showlogin() {

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

1406 test -z "$args" && resetskey

1407 s4name=${S4NAME:-s4}

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

1409 cat $layout/login.m4.html

1410 echo '</body></html>'

1411 ) | _m4 \

1412 -D_BODYCLASS_="login" \

1413 -D_TITLE_="$s4name" \

1414 -D_SYSNAME_="Welcome to $s4name" \

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

1416 exit 0

1417 }

1418 dologin() {

1419 checkauth

1420 st=$?

1421 if [ $st != 0 ]; then

1422 contenttype; echo

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

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

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

1426 showlogin # and EXIT

1427 fi

1428 }

1429

1430 # Do instant jobs here

1431 dbsetup

1432 trap cleanup INT HUP EXIT TERM PIPE

1433 # trap cleanup INT HUP

1434

1435

1436 cgiinit() {

1437 tmpd=`tmpd=$tmpdir mktempd`

1438 tmpf=$tmpd/stream.$$

1439 tmpfiles=$tmpfiles" $tmpd"

1440 addsession $session

1441 getcookie

1442 case "$REQUEST_METHOD" in

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

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

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

1446 # cat > $tmpf # too much?

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

1448 (echo CL=$CONTENT_LENGTH; ls -lF $tmpf) 1>&3

1449 s="`cat $tmpf`"

1450 ;;

1451 esac

1452 case "$CONTENT_TYPE" in

1453 *boundary*)

1454 bndry=${CONTENT_TYPE#*boundary=}

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

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

1457 do

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

1459 #echo u=$us

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

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

1462 # err k=$k v=$v

1463 case "$k" in

1464 *:filename)

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

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

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

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

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

1470 :

1471 fi

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

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

1474 case "$mimetype" in

1475 [Ii]mage/x-xcf)

1476 bzip2 "$tmpd/$v"

1477 v=${v}.bz2

1478 ;;

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

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

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

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

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

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

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

1486 v=$vjpg

1487 else

1488 mimetype="Not supported"

1489 fi

1490 ;;

1491 [Ii]mage/*)

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

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

1494 ;;

1495 esac

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

1497 then

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

1499 continue

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

1501 replpar text string \

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

1503 continue

1504 fi

1505 ;;

1506 *)

1507 type='string'

1508 ;;

1509 esac

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

1511 setpar "$k" "$type" "$v"

1512 done

1513 ;;

1514 *)

1515 setviastring "$s"

1516 ;;

1517 esac

1518 }

1519 email4group() {

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

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

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

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

1524 sql="WITH

1525 grpemails AS (

1526 SELECT gname, user, val email

1527 FROM grp_mem NATURAL JOIN grp_mem_s

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

1529 useremails AS (

1530 SELECT user.name, val email

1531 FROM user

1532 LEFT JOIN user_m

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

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

1535 FROM useremails u LEFT JOIN grpemails g

1536 ON u.name=g.user

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

1538 query "$sql"

1539 }

1540 email4groupbyuid() {

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

1542 g=$1; shift

1543 uids=`echo "$@"`

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

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

1546 email4group "$g" `query "$sql"`

1547 }

1548 myemail4group() {

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

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

1551 }

1552 collectmembersbyid() {

1553 # Collect user names of group specified by grid

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

1555 query "SELECT user FROM grp_mem \

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

1557 }

1558 collectmembersbyid() {

1559 # Collect user names of group name

1560 qgrp=`sqlquote "$1"`

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

1562 }

1563 collectgecosesbyid() {

1564 # Collect user gecoses of group

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

1566 query<<-EOF

1567 SELECT gecos

1568 FROM gecoses

1569 WHERE name IN (SELECT user FROM grp_mem

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

1571 EOF

1572 }

1573 collectemail() (

1574 # Collect email addresses for group $1

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

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

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

1578 for e; do

1579 if isuser "$e"; then

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

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

1582 else

1583 qgrp=`sqlquote "$e"`

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

1585 gmem="grp_mem"

1586 else

1587 tm=`sqlquote "$TEAM"`

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

1589 fi

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

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

1592 $gmem g left join grp_mem_s s

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

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

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

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

1597 query "$sql"

1598 fi

1599 done

1600 )

1601 sendinvitation() (

1602 # $1=email

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

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

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

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

1607 gecos=`gecos`

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

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

1610 _m4 -D_URL_="$urlbase" \

1611 -D_USER_="$name" \

1612 -D_EMAIL_="$1" \

1613 -D_REGIST_="$regist" \

1614 -D_ADMIN_="$admin" \

1615 $msgdir/mail-invite.m4 \

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

1617 return 0

1618 )

1619 emaildomaincheck() {

1620 case "$1" in

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

1622 *@*)

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

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

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

1626 return 2

1627 fi

1628 return 0

1629 ;;

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

1631 esac

1632 }

1633 invite() {

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

1635 case "$email" in

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

1637 *@*)

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

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

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

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

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

1643 elif sendinvitation $email; then

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

1645 else # Cannot be reached here

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

1647 fi ;;

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

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

1650 esac

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

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

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

1654 fi

1655 if [ -f $invite_policy ]; then

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

1657 else

1658 pol="$invite_policy"

1659 fi

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

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

1662 -D_INVITE_POLICY_="$pol" \

1663 $layout/html.m4.html $layout/invite.m4.html

1664 }

1665 regist() {

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

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

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

1669 echo "bye bye" | html p

1670 reutrn

1671 fi

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

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

1674 cat<<EOF

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

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

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

1678 EOF

1679 return

1680 fi

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

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

1683 # Fake login password to wasureta

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

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

1686 wasureta $email

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

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

1689 addsession $1 # for removal after 1 minute

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

1691 $layout/login.m4.html

1692 return

1693 }

1694 group_safename() {

1695 # Convert $1 to safe group name

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

1697 }

1698 groupupdate() {

1699 gname=`getpar gname`

1700 qgname=`sqlquote "$gname"`

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

1702 # See ALSO same job in showgroup()

1703 newgname=`group_safename "$gname"`

1704 err newgname=$newgname

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

1706 err NewGNAME: gname=$newgname

1707 gname=$newgname

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

1709 replpar gname string "$gname"

1710 fi

1711 # Name confliction check

1712 parow=`getpar rowid`

1713 ## err parow=$parow

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

1715 query "BEGIN EXCLUSIVE;"

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

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

1718 if [ $count -gt 0 ]; then

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

1720 query "END;"

1721 return

1722 fi

1723 par2table $formdir/grp.def

1724 query "END TRANSACTION;"

1725 # Remove orphan

1726 : <<EOF

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

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

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

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

1731 EOF

1732 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1736 sql="delete from blog where id in

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

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

1739 query "$sql";

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

1741 return # and return

1742 fi

1743 fi

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

1745 fi

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

1747 grid=$(query $sql)

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

1749 grp $grid

1750 }

1751 groupclone() {

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

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

1754 case "$1" in

1755 */noteam)

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

1757 esac

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

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

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

1761 return

1762 fi

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

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

1765 return

1766 fi

1767 i=0

1768 while true; do

1769 copy="-copy$i"

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

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

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

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

1774 i=$((i++))

1775 continue

1776 fi

1777 break

1778 done

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

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

1781 query<<-EOF

1782 BEGIN;

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

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

1785 SELECT $newqname, key, val

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

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

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

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

1790 -- Copy members and their configuration --

1791 REPLACE INTO grp_mem SELECT $newqname, user

1792 FROM grp_mem WHERE gname=$qgrp;

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

1794 FROM grp_mem_s WHERE gname=$qgrp;

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

1796 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1797 -- Copy administrators --

1798 REPLACE INTO grp_adm SELECT $newqname, user

1799 FROM grp_adm WHERE gname=$qgrp;

1800 COMMIT;

1801 EOF

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

1803 STOPCLONEMSG=1 groupconf "$newrowid"

1804 }

1805 groupman() {

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

1807

1808 GF_STAGE="grpconf"

1809 GF_STAGE=groupupdate

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

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

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

1813 -D_DUMPTABLE_="syscmd(cat)" \

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

1815 }

1816 userconf() {

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

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

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

1820 }

1821 groupconf() {

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

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

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

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

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

1827

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

1829 grp=`getgroupbyid $rowid`

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

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

1832 return

1833 fi

1834

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

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

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

1838 html div 'class="fold"' <<-EOF

1839 `cgi_checkbox clone yes id="clone"`<label

1840 for="clone">同一メンバーで別グループを作成する</label>

1841 <div>

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

1843 <table>

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

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

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

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

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

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

1850 </table>

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

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

1853 </div>

1854 EOF

1855 fi

1856 }

1857 mems() {

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

1859 kwd=`getpar kwd`

1860 listmember $kwd

1861 }

1862 grps() {

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

1864 kwd=`getpar kwd`

1865 listgroup $kwd \

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

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

1868 -D_FORM_="<a href=\"?groupman\">新規グループ作成</a>" \

1869 $layout/form+dump.m4.html

1870 }

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

1872 gpg=`getpar grp`

1873 grid=${1:-$gpg}

1874 grp=`getgroupbyid "$grid"`

1875 ## . ./s4-blog.sh

1876 jg=`getpar joingrp`

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

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

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

1880 fi

1881 htmlheader=$layout/html.m4.html

1882 showgroup "$grid"

1883 }

1884 sql4interestblogs() {

1885 cat<<EOF

1886 CREATE TEMPORARY VIEW interestblogs AS

1887 SELECT blog.rowid rid, id, author

1888 FROM blog

1889 NATURAL JOIN

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

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

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

1893 WHEN (SELECT user FROM grp_mem

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

1895 THEN 0

1896 ELSE 1

1897 END;

1898 EOF

1899 }

1900 listnewblogsql() { # $1=user

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

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

1903 newdays=${newdays:-14}

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

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

1906 WHERE user='$user'

1907 AND tblrowid IN

1908 ($blogreadflagrowid,

1909 $blogcutoffflagrowid)),

1910 $basetime -- "0"

1911 );"`

1912 cat<<EOF

1913 `sql4interestblogs`

1914 WITH article_ctime as (

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

1916 FROM article join article_s s using(id)

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

1918 GROUP BY id

1919 ), blog_title_owner as (

1920 SELECT blg.rid brid, id,

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

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

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

1924 ), blogall as (

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

1926 ), news as (

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

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

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

1930 FROM blogall bl

1931 LEFT JOIN

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

1933 ON bl.brid=al.tblrowid

1934 WHERE atime < bl.ctime

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

1936 LIMIT 10

1937 ) SELECT brid LINK, "新着",

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

1939 ctime, title,

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

1941 FROM news;

1942 EOF

1943 }

1944

1945 search_form() {

1946 # $1 = { author=<AUTHOR> | grp=<GROUP> }

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

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

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

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

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

1952 @week → 最近一週間

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

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

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

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

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

1958 auth=""

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

1960 case "$1" in

1961 author=*)

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

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

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

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

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

1967 ;;

1968 grp=*)

1969 a=`echo "${1#grp=}"|htmlescape`

1970 g=`gecos ${1#grp=}`

1971 auth="<input type=\"hidden\" name=\"owner\" value=\"$a\">"

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

1973 ;;

1974 esac

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

1976 cat<<-EOF

1977 <div class="right">

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

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

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

1981

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

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

1984

1985 </form>

1986 </div>

1987 EOF

1988 }

1989

1990 imgsrc_cache() (

1991 # $1 = directory for cache'ing

1992 # $2 = table (user_m or grp_m)

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

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

1995 dir="$1" tbl="$2"

1996 keycond="$3"

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

1998 ORDER BY rowid DESC LIMIT 1"

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

2000 tmpf=$tmpd/imgsrc_cache.$$

2001 case "$4" in

2002 [Ss]) size=S ;;

2003 [Oo]) size=O ;;

2004 *) size=M ;;

2005 esac

2006 # ImageCache filename storing schema:

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

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

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

2010 valtype=`query "$sql0"`

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

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

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

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

2015 case "$filetype" in

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

2017 esac

2018 fi

2019 cacheimg_S=$dir/S_$filename

2020 cacheimg_M=$dir/M_$filename

2021 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

2029 else

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

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

2032 fifo2=$fifo.2

2033 mkfifo $fifo $fifo2

2034 fmt=${filename##*.}

2035 ## [[ NOTE ]]

2036 ## a. convert oldimage newimage

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

2038 ## b is much smaller than a

2039 cat $tmpf | unhexize \

2040 | tee $fifo \

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

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

2043 | tee $fifo2 \

2044 | convert - "$cacheimg_M" &

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

2046 -resize ${iconxy_S}'>' - ${fmt}:- \

2047 | convert - "$cacheimg_S" &

2048 printf '%s' "<img src=\"data:${filetype},"

2049 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2050 echo '">'

2051 echo "$sum" > $sumfile

2052 fi

2053 ## Now preparing cache image, done.

2054 ## Store this information to DB

2055 stbl=${tbl%_m}_s # user_s or grp_s

2056 pkey=${keycond%%=*} # Primary Key name

2057 pval=${keycond#*=} # Primary Key value

2058 query <<-EOF

2059 REPLACE INTO $stbl($pkey, key, type, val)

2060 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2061 EOF

2062 )

2063

2064 showhome() {

2065 # $1=userRowIdToShow

2066 err showhome \$1=$1

2067 case "$1" in

2068 *@*) uname=`getvalbypkey user name "$1"` ;;

2069 *) uname=`getvalbyid user name $1` ;;

2070 esac

2071 ## err ShowHome: uname=$uname

2072 td=`getcachedir home/"$1"`

2073 gecos=`gecos "$uname"`

2074 ## err SH:gecos=$gecos

2075 GF_VIEWONLY=1

2076 cond="gname in (select gname from grp_mem where user='$uname')"

2077 search_form_args=""

2078 if [ x"$user" = x"$uname" ]; then

2079 if [ -z "$S4ANNEXNAME" ]; then

2080 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2081 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2082 elif [ -n "$S4MASTERURL" ]; then

2083 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2084 title=\"Shortcut: E${nl}Main Site\">本館</a> / "

2085 fi

2086 usermenu="$usermenu

2087 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2088 # Display folders

2089 sql="select count(id) from article_m where id

2090 in (select id from article where author='$user')

2091 and type like 'file:%';"

2092 ## err nfile-sql=`echo "$sql"`

2093 nfile=`query "$sql"`

2094 # err nfile=$nfile

2095 if [ $nfile -gt 0 ]; then

2096 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2097 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2098 fi

2099 else

2100 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2101 GROUP BY user;"`

2102 usermenu="<p>Last seen on $latestlog</p>"

2103 search_form_args="author=$uname"

2104 fi

2105 . ./s4-blog.sh

2106

2107 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2108 search_form "$search_form_args" > $sf

2109 printf "%s さん" "$gecos" > $tf

2110 { echo "<div class=\"noprofimg\">"

2111 viewtable $formdir/user.def user $1

2112 echo "</div>"

2113 } > $pf

2114

2115 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2116 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2117 imf=$tmpd/profimg.$$; touch $imf

2118 if [ -n "$img" ]; then

2119 if true; then

2120 tbl=user_m

2121 enticond="name='$uname'"

2122 imgsrc_cache "$td/main" user_m "$enticond" M

2123 else

2124 { printf '%s' "<IMG src=\"data:${img#file:},"

2125 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2126 | sed 's/$..$/%\1/g'

2127 echo '">'

2128 }

2129 fi > $imf

2130 fi

2131 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2132 val='$uname';"`

2133 ## REMOVE This comment block until 2019/7/1

2134 ## err "----- `gdate +%FT%T.%3N` ------------C"

2135 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2136 listblog $uname > $bf

2137 ## unset ddd

2138 ## err "----- `gdate +%FT%T.%3N` ------------D"

2139

2140 hometail=$tmpd/tail.$$

2141 mkfifo $hometail

2142

2143 #Calling listgroupbytable, originally here

2144

2145 (

2146 # Display Most Recent Entry

2147 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2148 shortval=${shortval:-val}

2149

2150 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2151 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2152 ctime,

2153 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2154 (SELECT gecos FROM gecoses

2155 WHERE name=(SELECT val FROM blog_s

2156 WHERE key='owner' AND id=b.id)) owner,

2157 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2158 FROM blog b

2159 JOIN

2160 (SELECT distinct blogid, a.id aid, max(val) ctime

2161 FROM article a, article_s s

2162 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2163 GROUP BY blogid ORDER BY val DESC LIMIT 50

2164 ) m

2165 ON b.id=m.blogid;"

2166 # This should be as follows

2167 : <<EOF

2168 WITH arts AS(

2169 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2170 a.blogid, a.id id, s.val ctime

2171 FROM article a NATURAL JOIN article_s s

2172 WHERE s.key = 'ctime' AND a.author='$user'

2173 GROUP by s.id

2174 )

2175 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2176 FROM arts a0

2177 JOIN

2178 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2179 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2180 ORDER BY ctime DESC LIMIT 50;

2181 EOF

2182

2183 cat<<-EOF

2184 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2185 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2186 <div class="lcto">

2187 `DT_VIEW=replyblog dumptable html blog`

2188 </div>

2189 EOF

2190 unset DT_SQL

2191 if [ x"$user" = x"$uname" ]; then

2192 # Display NEWS

2193 # 2016-06-26

2194 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2195 acclog blog $blogreadflagrowid

2196 # echo "全部既読にしました" | html p

2197 fi

2198 # 2016-02-19 Counting NEWS without using dumptable.

2199 sql=`listnewblogsql "$user"`

2200 # echo "$sql" > tmp/listnew

2201 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2202 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2203 cont=$((cont-1))

2204 err newcount=$cont

2205 if [ $cont -gt 0 ]; then

2206 #echo "全体の新着記事${cont}傑" | html h2

2207 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2208 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2209 cat<<-EOF | html form 'action="?home"'

2210 `cgi_checkbox readchk yes 'id="read"'`<label

2211 for="read">新着ふくめて全部読んだことにする</label>

2212 `cgi_submit '確定'`

2213 `cgi_hidden read yes`

2214 EOF

2215 echo "$new10 "

2216 echo "</div>"

2217 else # If news is 0, set log cut off flag

2218 acclog blog $blogcutoffflagrowid # for speed

2219 fi

2220 else # Not My Home ($user != $uname)

2221 : # DT_SQL=

2222 fi

2223 ) > $hometail & # Is background call safe to m4??

2224 #

2225 listgroupbytable $formdir/grp.def $cond |

2226 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2227 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2228 -D_PROFIMG_="spaste(\`$imf')" \

2229 -D_BLOGS_="spaste(\`$bf')" \

2230 -D_SEARCH_="spaste(\`$sf')" \

2231 -D_NBLOG_="$nblog" \

2232 -D_GROUPS_="syscmd(\`cat')" \

2233 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2234 $layout/html.m4.html $layout/home.m4.html

2235

2236 # Record access log

2237 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2238 }

2239 commission() { # $1=grp-rowid $2=user-rowid

2240 contenttype; echo

2241 ## err commission: "$@"

2242 gname=`getgroupbyid $1`

2243 echo "グループ $gname 管理者委任" \

2244 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2245 if [ -n "$2" ]; then

2246 grp_reg_adm "$@"

2247 else

2248 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2249 fi

2250 }

2251 listgroupbytable() {

2252 # $1=deffile $2...=condition

2253 tagline=`grep :tag: $1`; shift

2254 and="${1:+and }" where=${1:+where }

2255 href="<a href=\"$myname?grp+"

2256 echo '<div class="listgroup">'

2257 NGsql="select distinct tag from\

2258 (select gname, max(case key when 'tag' then val end) as tag, \

2259 max(case key when 'ctime' then val end) as ctime\

2260 from grp_s group by gname order by ctime);"

2261 sql="select val from grp_s where key='tag' $and$* group by val;"

2262 ## err ListGRP: query sql="$sql"

2263 for tag in `query "$sql"`

2264 do

2265 ## err ListGrp: tag=$tag

2266 tn=${tagline%%=${tag}*}

2267 tn=${tn##*[ :]}

2268 sql="select rowid||':'||gname as 'グループ名',説明 from

2269 (select (select rowid from grp g where g.gname=grp_s.gname)

2270 as rowid,

2271 gname,

2272 max(case key when 'gecos' then val end) as '説明',

2273 max(case key when 'tag' then val end) as 'tag',

2274 max(case key when 'mtime' then val end) as mtime from grp_s

2275 $where$* group by gname having tag='$tag' order by mtime desc);"

2276 ## err PersonalGroupList= `echo $sql`

2277 echo "<h2>$tn</h2>"

2278 echo '<table class="b listgroup">'

2279 sq -header -html $db "$sql" \

2280 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2281 echo '</table>'

2282 done

2283 echo '</div>'

2284 }

2285 iconhref() (

2286 # $1=icon-file, $2=Href $3=title $4...=anchor

2287 data=`percenthex "$1"`

2288 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2289 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2290 href=$2; title=$3; shift 3

2291 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2292 )

2293 iconhref2() (

2294 # $1=icon-file, $2=Href $3=title $4...=anchor

2295 src=$1

2296 href=$2; title=$3; shift 3

2297 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2298 )

2299 listentry() (

2300 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2301 # Referring variable $iamowner=$grp to attach owner-request links

2302 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2303 cond='' hiddens=''

2304 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2305 if [ -z "$offset" ]; then

2306 offset=`getpar start`; offset=${offset%%[!0-9]*}

2307 offset=$((offset-1))

2308 fi

2309 offset=$((offset + 0)) # change to numeric forcibly

2310 [ $offset -lt 0 ] && offset=0

2311 limit=$listentlimit

2312 dir=`getcachedir "$1"`

2313 if [ x"$1" = x"user" ]; then

2314 hrb="$myname?home"

2315 deficon=person-default.png

2316 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2317 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2318 gcs=gecos

2319 else # if group

2320 hrb="$myname?grp"

2321 deficon=group-default.png

2322 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2323 gcs=name

2324 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2325 if [ -n "$tagline" ]; then

2326 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2327 ## err tagconv=$tagconv

2328 fi

2329 fi

2330 if [ ! -d $dir ]; then

2331 mkdir -p $dir

2332 fi

2333 if [ ! -s $dir/$deficon ]; then

2334 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2335 fi

2336 if [ -n "$2" ]; then

2337 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2338 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2339 fi

2340 tag=`getpar tag` tag2=`getpar tag2`

2341 if [ x"$tag" = x"NULL" ]; then

2342 tag="" tag2=""

2343 fi

2344 if [ -n "$tag$tag2" ]; then

2345 tag=${tag:-$tag2}

2346 qtag=`sqlquote "$tag"`

2347 cond2="tag=$qtag"

2348 fi

2349 if [ -n "$cond1$cond2" ]; then

2350 cond="$cond1${cond2:+ AND $cond2}"

2351 cond="WHERE ${cond# AND }"

2352 fi

2353

2354 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2355 # grpは呼出し元の動的スコープ変数でよくないな...

2356 ##qgrp=`sqlquote $grp`

2357 getgrp="(select gname from grp where rowid=${rowid:--1})"

2358 sql="select a.rowid, a.$link,

2359 coalesce(b.$gcs, a.$nm) as nick,

2360 quote(a.$nm) as qname,

2361 (SELECT val FROM ${tbl}_s

2362 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2363 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2364 || case when a.$nm in (select gname from grp)

2365 then printf('(%d名)',

2366 (select count(user) from grp_mem where gname=a.$nm))

2367 else ' <'||a.$nm||'>'

2368 end

2369 as name,

2370 b.tag,

2371 case when a.$nm in (select user from grp_adm

2372 where gname=$getgrp) then '管理者'

2373 when '$user' in (select user from grp_adm where gname=a.$nm)

2374 then 'ADMIN'

2375 when '$user' in (select user from grp_mem where gname=a.$nm)

2376 then 'Member'

2377 when '$iamowner' = '' then ''

2378 else ',not='||a.rowid end as ownerlink,

2379 CASE '$entity'

2380 WHEN 'グループ'

2381 THEN coalesce(

2382 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2383 'open')

2384 ||

2385 CASE WHEN '$user'

2386 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2387 THEN ' ismember'

2388 ELSE ''

2389 END

2390 ELSE 'user'

2391 END regmode

2392 from $tbl a left join

2393 (select $nm as name,

2394 max(case key when 'gecos' then val end) as gecos,

2395 max(case key when 'tag' then val end) as tag,

2396 max(case key when 'mtime' then val end) as mtime,

2397 max(case key when 'wtime' then val end) as wtime,

2398 max(case key when 'login' then val end) as login

2399 from ${tbl}_s group by $nm)

2400 b on a.$nm=b.name $cond $3

2401 order by b.wtime desc, b.login desc,

2402 b.mtime desc, b.tag desc, a.rowid asc"

2403 # Give precedence to newer maintained groups (2016-09-24)

2404 # Note that mtime is stored only in grp_s.

2405 ## err LE:sql.1="$sql"

2406 total=`query "with x as ($sql) select count(*) from x;"`

2407 echo "${entity} 一覧" | html h2

2408 echo '<div class="listentry">' # List-entry div

2409 # Show owner/member filter button

2410 METHOD=GET

2411 hiddens="$hiddens

2412 `cgi_hidden kwd \"$kwd\"`

2413 `cgi_hidden stage \"$stage\"`"

2414 if [ x"$tbl" = x"grp" ]; then

2415 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2416 fh="<select name=\"tag\">$nl"

2417 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2418 for l in $args; do

2419 val=${l#*=} tname=${l%=*}

2420 if [ x"$val" = x"$tag" ]; then

2421 s=" selected"

2422 selectedtags="(種別[${tname}]のみ)"

2423 else

2424 s=""

2425 fi

2426 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2427 done

2428 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2429 cat<<-EOF

2430 <form action="$myname" method="$METHOD">

2431 </form>

2432 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2433 for="ismembtn">参加中以外隠す</label>

2434 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2435 for="isadmbtn">管理者参加以外隠す</label>

2436 EOF

2437 # limit=3

2438 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2439 fi

2440 if [ $total -gt $limit ]; then

2441 echo '<div>'

2442 METHOD=GET cgi_form $stage <<EOF

2443 $form

2444 <label>次の語を含む${entity}で検索:

2445 `cgi_text kwd "$kwd"`</label>

2446 $hiddens

2447 EOF

2448 echo '</div>'

2449 else

2450 echo $selectedtags | html p

2451 fi

2452 cat<<EOF

2453 <form action="$myname" method="$METHOD">

2454 <p>${total}件中の<input class="hidesub" type="text" name="start"

2455 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2456 <input type="submit" value="確定"></p>

2457 </form>

2458 EOF

2459 if [ $((offset+limit)) -lt $total ]; then

2460 nextbtn=$(

2461 cat<<EOF

2462 <div class="right clear"><form action="$myname" method="$METHOD">

2463 `cgi_submit 次の${limit}件`

2464 $hiddens

2465 `cgi_hidden offset $((offset + limit))`</form></div>

2466 EOF

2467 )

2468 fi

2469 if [ $offset -gt 0 ]; then

2470 prevbtn=$(

2471 cat<<EOF

2472 <form action="$myname" method="$METHOD">

2473 `cgi_submit 前の${limit}件`

2474 $hiddens

2475 `cgi_hidden offset $((offset - limit))`</form>

2476 EOF

2477 )

2478 fi

2479 pnbtn="$nextbtn$prevbtn"

2480 echo $pnbtn

2481

2482 ## err ListEntry: `echo "$sql"\;`

2483 # sq $db here??? 2016-11-28

2484 query "$sql limit $limit ${offset:+offset $offset};" \

2485 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2486 # err name=$name owner=$ownerp lnk=$lnk

2487 # err newlnk=$lnk regmode=$regmode

2488 icondir=$dir/$id

2489 # Pick up only last icon

2490 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2491 <p class=\"tag _$tag\">$tag</p>" \

2492 | _m4 $tagconv

2493 if [ -n "$NOSPEEDUP" ]; then

2494 files=`getvalbyid $tbl profimg $id $icondir`

2495 if [ -n "$files" ]; then

2496 icon=`echo "$files"|tail -1`

2497 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2498 else

2499 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2500 fi

2501 elif [ -n "$icon" -a -s "$icon" ]; then

2502 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2503 else

2504 cond="$nm=$qname"

2505 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2506 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2507 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2508 # err "img=[$img]"

2509 if [ -n "$img" ]; then

2510 echo "<a href=\"$hrb+$lnk\">"

2511 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2512 echo "</a>"

2513 else

2514 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2515 fi

2516 fi

2517 echo "<br>$name${ownerp:+<br>($ownerp)}"

2518 echo "</div>"

2519 done

2520 echo "</div>" # End of List-entry div

2521 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2522 )

2523 listmember() {

2524 listentry user "$@"

2525 }

2526 listgroup() {

2527 listentry group "$@"

2528 }

2529 hexteams() { # $1=gname, $2(optional)=user

2530 cond=${2:+" AND user='$2'"}

2531 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2532 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2533 }

2534 showgroup() { # $1=group-rowid

2535 if [ -z "$1" ]; then

2536 grid=`getpar grid`

2537 grid=${grid%%[!0-9]*}

2538 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2539 else

2540 grid=$1

2541 fi

2542 grp=`getgroupbyid $grid`

2543 qgrp=`sqlquote "$grp"`

2544 htmlgrp=`echo "$grp"|htmlescape`

2545 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2546 if isgroup "$grp"; then

2547 tf=$tmpd/title.$$

2548 sf=$tmpd/search.$$

2549 bodyclass=`query "SELECT val FROM grp_s

2550 WHERE gname=$qgrp AND key='regmode';"`

2551 if ismember "$user" "$grp"; then

2552 ismember="ismember"

2553 bodyclass="$bodyclass${bodyclass:+ }ismember"

2554 else

2555 ismember="" # bodyclass="group"

2556 fi

2557 bodyclass="$bodyclass grouphome"

2558 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2559 echo "グループ $htmlgrp" > $tf

2560

2561 showgroupsub $formdir/grp.def "$grid" | \

2562 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2563 -D_FORM_="syscmd(\`cat')" \

2564 -D_BODYCLASS_="$bodyclass" \

2565 -D_DUMPTABLE_="" \

2566 $htmlheader $sf $layout/form+dump.m4.html

2567 # $htmlheader $layout/form+dump.m4.html

2568 # $htmlheader is defined in grp()

2569 else # if $grp is removed at par2table

2570 listgroup

2571 fi

2572 }

2573 showgroupsub() {

2574 # $1=def-file $2=group-rowid

2575 # Using $ismember

2576 rowid=$2

2577 grp=`getgroupbyid $2`

2578 qgrp=`sqlquote "$grp"`

2579 td=`getcachedir grp/"$2"`

2580 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2581 if [ -z "$rowid" ]; then

2582 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2583 #grp=`sq $db "select gname from grp where rowid=$grp"`

2584 echo "showgroupsub: invalid argument($1 $2)" | html p

2585 return

2586 fi

2587 val=`getvalbyid grp profimg $rowid $tmpd`

2588 enticond="gname=$qgrp"

2589 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2590 if [ -n "$img" ]; then

2591 cat<<-EOF

2592 <p class="groupimg">

2593 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2594 EOF

2595 fi

2596 echo "<div class=\"noprofimg\">"

2597 viewtable $1 grp $rowid

2598 echo "</div>"

2599 if isgrpowner "$user" "$grp"; then

2600 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2601 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2602 iamowner=$rowid

2603 colmd=" mode"

2604 fi

2605 if [ -n "$ismember" ]; then

2606 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2607 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2608 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2609 cat<<-EOF

2610 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2611 href="?blog+$rowid">グループの新規話題作成</a>

2612 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2613 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2614 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2615 <div class="fold clear">

2616 `cgi_checkbox send yes id="send"`<label

2617 for="send">グループ全員にメッセージ送信</label>

2618 <div>

2619 `cgi_textarea message "" "cols=60"`

2620 `cgi_submit 送信`

2621 `cgi_reset リセット`

2622 </div>

2623 `cgi_hidden grp $rowid`

2624 </div></form>

2625 EOF

2626 fi

2627 # 加入ボタン + 加入者リスト

2628 if [ -n "$ismember" ]; then

2629 ismem='checked' state="(参加中)"

2630 else

2631 nomem='checked' state="(現在非加入)"

2632 fi

2633 # このグループでの加入アドレス

2634 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2635 and key='email';"`

2636 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2637 ## and key='email';"

2638 ##err email=$eml

2639 cat <<EOF

2640 <div class="fold clear">

2641 `cgi_checkbox reg yes id="reg"`<label

2642 for="reg">自身の加入状態を操作する</label>$state

2643 <div>

2644 EOF

2645 cgi_form grp <<EOF

2646 <p>このグループに</p>

2647 <table class="b">

2648 <tr><th>メンバーとして</th><td>

2649 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2650 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2651 <tr><th>参加する場合のメイルアドレス<br>

2652 <small>(メインのアドレスとは違うものにする場合に記入<br>

2653 同じでよい場合は空欄に)</small></th>

2654 <td>`cgi_text email $eml`</td></tr>

2655 </table>

2656 `cgi_hidden grp $rowid`

2657 EOF

2658 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2659 echo "moderated (承認加入の)グループなので実際に参加できるのは

2660 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2661 fi

2662 echo '</div></div>'

2663 echo '<h2>話題一覧</h2>'

2664 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2665 cat<<-EOF

2666 <form class="summary" action="$myname" title="$thelp">

2667 `cgi_hidden owner "$grp"`

2668 `cgi_hidden kwd "@week"`

2669 `cgi_hidden stage searchart`

2670 `cgi_submit "一週間のまとめ"`

2671 </form>

2672 EOF

2673 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2674 colstate="state:稼動状態:frozen=rowclass=凍結"

2675 DT_CHLD=article:blogid \

2676 DT_QOWNER="$qgrp" \

2677 DT_VIEW=replyblog dumptable html blog \

2678 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2679

2680 getgname="(select gname from grp where rowid=$rowid)"

2681 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2682 cm="?commission+$rowid"

2683 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2684 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2685 # team list

2686 hexteams=`hexteams "$grp"`

2687 if [ -n "$hexteams" ]; then

2688 echo "チーム一覧" | html h2

2689 echo '<div class="dumptable"><table class="b">'

2690 sq $db -html -header<<-EOF

2691 SELECT val TEAM,

2692 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2693 MEMBERS

2694 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2695 EOF

2696 echo '</table></div>'

2697 fi

2698 }

2699 grp_getbodyclass() {

2700 # Get css class name for document.

2701 # `moderated' for moderated groups

2702 # `ismember' for groups where user belongs

2703 # $1=GroupName (w/o quote)

2704 # $user=userNameCurrentlyLogin

2705 ## err grp_getbodyclass: 1="$1"

2706 qgrp=`sqlquote "$1"`

2707 query<<-EOF

2708 SELECT coalesce(

2709 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2710 'open')

2711 ||

2712 CASE WHEN '$user'

2713 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2714 THEN ' ismember'

2715 ELSE ''

2716 END;

2717 EOF

2718 }

2719 grpaction() { # $1=group-rowid

2720 err GRP_ACTION:IN

2721 grid=${1:-`getpar grp`}

2722 grp=`getgroupbyid "$grid"`

2723 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2724 if [ -z "$grp" ]; then

2725 echo "無効な指定です。" | html p; return

2726 fi

2727 if ! ismember $user "$grp"; then

2728 echo "加入者のみに許可された操作です。" | html p; return

2729 fi

2730 echo "グループ $grp 個別選択操作" \

2731 | _m4 -D_TITLE_="syscmd(\`cat')" \

2732 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2733 $layout/html.m4.html

2734

2735 isowner=""

2736 isgrpowner "$user" "$grp" && isowner="yes"

2737 usel=`getpar usel`

2738 if [ -n "$usel" ]; then

2739 uids=$(echo `echo $usel`|tr ' ' ',')

2740 ## err grpaction-1: grp=$grp, `echo $sql`

2741 text=`getpar text|tr -d '\r'`

2742

2743 rm=`getpar rm` cfm=`getpar confirm`

2744 ## err rm=$rm cfm=$cfm

2745 if [ x"$rm" = x"yes" ]; then

2746 if [ "$isowner" ]; then

2747 if [ x"$rm$cfm" = x"yesyes" ]; then

2748 # Eliminate

2749 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2750 for tbl in grp_mem grp_mem_s grp_mem_m; do

2751 sql="delete from $tbl $cond;"

2752 # echo "sql=$sql"

2753 query "$sql"

2754 err rmGRPuser "$sql"

2755 done

2756 num=`query "select count(*) from user where rowid in ($uids);"`

2757 #err num=$num

2758 if [ 0$num -gt 0 ]; then

2759 sql="select coalesce(b.val,a.name) from user a left join \

2760 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2761 # err `echo "$sql"`

2762 html pre<<EOF

2763 以下の${num}名のグループ $grp 登録を解除しました。

2764 `query "$sql"`

2765 EOF

2766 fi

2767 else

2768 echo "確認のチェックがないのでやめておきます。" | html p

2769 return

2770 fi

2771 else # not Group Owner

2772 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2773 return

2774 fi

2775 cat<<EOF

2776

2777 EOF

2778 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2779 if [ -z "$text" ]; then # if msg is empty

2780 echo "なにかメッセージを..." | html p

2781 return 0

2782 fi

2783 gecos=`gecos $user`

2784 safegc=`echo "$gecos" | tr -d '<>@'`

2785 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2786 fromad=`myemail4group "$grp"`

2787 ###mail_from="$safegc <$fromad>"

2788 mail_from="$safegc <$user>" # TEST: 2020/5/13

2789 test -n `getpar sender` &&

2790 export SENDER=$user # TEST: 2020/5/15

2791 replyto=$fromad

2792

2793 ## Start parse of attachment files

2794 if [ -n "`getpar email`" ]; then

2795 ar=`getpar supprcpt`

2796 if [ -n "$ar" ]; then

2797 for a in $ar; do

2798 if checkdomain "$a"; then

2799 supprcpt="$supprcpt $a"

2800 else

2801 err "SupprcptErr=[$a] by $user"

2802 removercpt="$removercpt $a"

2803 fi

2804 done

2805 fi

2806 subj=`getpar subject`

2807 afiles=""

2808 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2809 do

2810 f=$tmpd/$fn

2811 if [ -s $f ]; then

2812 afiles=$afiles"${afiles:+ }$f"

2813 fi

2814 done

2815 else

2816 preface=$(cat <<-EOF

2817 $url

2818 のグループ「$grp」のメンバーである $gecos さんから、

2819 あなた宛のメッセージです。

2820 ----------------------------------------------------------

2821 EOF

2822 )

2823 fi

2824 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2825 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2826 subj="${subj:-$gecos さんからのメッセージ}"

2827 REPLYTO=$replyto

2828 MAIL_FROM=$mail_from

2829 export REPLYTO SMAIL_TO MAIL_FROM

2830 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2831 for r in $rcpts; do

2832 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2833 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2834 else

2835 # Show sender and rcpts address for guest

2836 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2837 fi

2838 if [ -n "$afiles" ];then

2839 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2840 else

2841 smail "$r" "$subj"

2842 fi <<EOF

2843 ${preface:+$preface$nl}$text

2844 EOF

2845 done

2846 if [ $? = 0 ]; then

2847 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2848 sql="select coalesce(b.val, a.name) from

2849 (select name from user where rowid in ($uids)) a

2850 left join user_s b on a.name=b.name and b.key='gecos';"

2851 html pre<<EOF

2852 `query "$sql"`

2853 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2854 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2855 EOF

2856 err SendDone: `echo $sql`

2857 fi

2858 elif [ x"$rm" = x"commission" ]; then

2859 grp_reg_adm $grid $usel

2860 elif [ x"$rm" = x"addteam" ]; then

2861 team=`getpar team|sed "s/'/''/g"` # for single quotation

2862 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2863 if [ x"$team" != x"$newteam" ]; then

2864 echo "チーム名に使えない文字を除去しました" | html p

2865 team=$newteam

2866 fi

2867 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2868 cat<<-EOF | html p

2869 有効なチーム名を入力してください。

2870 カンマだけ、「なし」という名前は使えません。

2871 EOF

2872 echo "有効なチーム名を入力してください。" | html p

2873 else

2874 grp_add_team $grid "$team" $usel

2875 fi

2876 elif [ x"$rm" = x"rmteam" ]; then

2877 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2878 rmteam=`getpar rmteam|sed "s/'/''/g"`

2879 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2880 gname='$grp' AND user='$user' AND key='team'\

2881 AND val='$rmteam';\"`" ]; then

2882 grp_rm_team $grid "$rmteam" $usel

2883 else

2884 echo "所属していないチームの除去操作はできません。"|html p

2885 fi

2886 else

2887 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2888 fi

2889 fi

2890 fi

2891 # POST count summary

2892 from=`getpar from`; to=`getpar to`

2893 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2894 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2895 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2896 # New entry

2897 sql="WITH mems AS (

2898 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2899 ON gm.user=g.name

2900 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2901 ), target_article AS (

2902 SELECT id FROM article_s

2903 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2904 ), posts AS (

2905 SELECT author, count(author) post

2906 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2907 WHERE blogid IN (SELECT id FROM blog_s

2908 WHERE key='owner'

2909 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2910 AND key='text'

2911 GROUP BY author

2912 ), teams AS (

2913 SELECT user, group_concat(val, ', ') team

2914 FROM grp_mem_m

2915 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2916 AND key='team'

2917 GROUP BY user

2918 ), user_post AS (

2919 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2920 FROM mems m LEFT JOIN posts

2921 ON m.name=posts.author

2922 GROUP by m.rowid

2923 )

2924 SELECT

2925 CASE

2926 WHEN (SELECT user FROM grp_adm

2927 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2928 AND user=up.name) IS NOT NULL

2929 then 'k'

2930 ELSE ''

2931 END || rowid || ',' || gecos NAME,

2932 post POST, team _TEAM_

2933 FROM user_post up LEFT JOIN teams t

2934 ON up.name=t.user

2935 ORDER BY gecos;"

2936 ## err grpaction: "`echo \"$sql\"`"

2937 tf=$tmpd/title.$$

2938 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2939 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2940 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2941 for=\"cmadmin\">管理者委任</label>

2942 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2943 </p></div>"

2944 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2945 title=\"Shortcut: g${nl}Remove from the Group\"

2946 for=\"conf\">GRP登録解除</label>

2947 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2948 <p>この操作による通知は本人に行きません。

2949 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2950 </div>"

2951 # Get team list to which current user belongs into $hexteams

2952 allhexteams=$(hexteams "$grp")

2953 if [ -n "$isowner" ]; then

2954 myhexteams="$allhexteams" # admin can remove all teams' attr

2955 else

2956 myhexteams=$(hexteams "$grp" "$user")

2957 fi

2958 if [ -n "$myhexteams" ]; then

2959 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2960 title=\"Shortcut: s${nl}Strip a team tag from\"

2961 for=\"cmrmteam\">チーム属性除去</label>

2962 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2963 を除去します: `cgi_checkbox teamconfirm yes` 確認

2964 <p>この操作による通知は本人に行きません。

2965 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2966 </div>

2967 "

2968 fi

2969 stf=$tmpd/selteam.$$

2970 cgi_select_h selteam "5445414d" $allhexteams > $stf

2971 b1='<label> <input type="checkbox" name="usel" value="'

2972 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2973 br='<span id="reverse" title="Reverse Selection"></span>'

2974 #b2='"> <span>' b3='</span></label>'

2975 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2976 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2977 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2978 cgi_form grpaction<<EOF \

2979 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2980 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2981 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2982 | _m4 -D_TITLE_="spaste(\`$tf')" \

2983 -D_SUBTITLE_="チェック後操作ボタン" \

2984 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2985 $layout/form+dump.m4.html \

2986 | _m4 -D_TEAM_="spaste(\`$stf')"

2987 <p>下でチェックした人を対象として:</p>

2988 <div class="foldtabs">

2989 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2990 title="Shortcut: a${nl}Add a team tag to"

2991 for="cmteam">同じチーム属性を付与</label>

2992 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2993 `cgi_datalist_h teams $allhexteams`

2994 </div>

2995 ${rmteammsg}

2996 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2997 title="Shortcut: d${nl}DirectMail to"

2998 for="sendmsg" title="Direct Message">DM送信</label>

2999 <div>

3000 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3001 title="Using email format">email書式を使う</label>

3002 <div class="folded">

3003 <table>

3004 <tr><td>From: </td><td>$user</td></tr>

3005 <tr><td>このFrom:で送る</td>

3006 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3007 </td></tr>

3008 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3009 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3010 <tr><td>ファイル添付: </td>

3011 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3012 </table>

3013 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

3014 </div>

3015 <div>本文:`cgi_textarea text "" cols=72`

3016 </div>

3017 </div>

3018 ${isowner:+$cmmsg$excmsg}

3019 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3020 </div>

3021 <h4>$grp 参加者一覧</h4>$fromtonote

3022 <table class="td2r thl">

3023 `sq $db -header -html "$sql"`

3024 </table>

3025 `cgi_hidden grp $grid`

3026 `cgi_hidden myuid $myuid id="myuid"`

3027 EOF

3028 }

3029 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3030 # Create TEMPORARY VIEW

3031 query<<EOF

3032 CREATE TEMPORARY VIEW writeusers AS

3033 SELECT DISTINCT author FROM article

3034 WHERE id in (

3035 select id from article where blogid=(select id from blog where rowid=$1)

3036 );

3037 CREATE TEMPORARY VIEW movablegroups AS

3038 SELECT g.rowid growid , g.gname

3039 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3040 ON grp.gname=gm.gname -- そのユーザが属している

3041 AND user='$user') g -- グループに絞る

3042 WHERE (SELECT author FROM writeusers

3043 EXCEPT

3044 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3045 IS NULL;

3046 $2

3047 EOF

3048 }

3049 sql4readableblogs() {

3050 # Create view of blogs that can be readable to $user

3051 # Blog is readable when:

3052 # 1: blog owner is an user

3053 # 2: else, 2.1: owner-group where the $user belongs

3054 # 2.2: else, owner-group is not moderated

3055 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3056 cat<<EOF ## | tee tmp/sql.out

3057 CREATE TEMPORARY VIEW readableblogs AS

3058 SELECT blog.rowid rid, id, author

3059 FROM blog

3060 NATURAL JOIN

3061 (SELECT id,

3062 max(CASE key WHEN 'owner' THEN val END) owner,

3063 max(CASE key WHEN 'mode' THEN val END) mode

3064 FROM blog_s GROUP by id) bs

3065 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3066 THEN 1 -- blog owner is an user, READABLE

3067 WHEN (SELECT val FROM grp_s

3068 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3069 AND

3070 (SELECT user FROM grp_mem

3071 WHERE gname=bs.owner AND user='$user') IS NULL

3072 THEN 0

3073 WHEN mode IN ('quiz', 'enquete')

3074 THEN 0 -- "quiz" mode blog cannot be searched

3075 ELSE 1

3076 END;

3077 EOF

3078 }

3079 editheading() { # $1=rowid-of-heading

3080 rowid=${1%%[!A-Z0-9a-z_]*}

3081 if [ -z "$rowid" ]; then

3082 echo "話題番号が未指定です。" | html p

3083 return

3084 fi

3085 owner=`getvalbyid blog owner $rowid`

3086 title=`getvalbyid blog title $rowid`

3087 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3088 | _m4 -D_TITLE_="修正" \

3089 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3090 -D_BLOGS_="" -D_DUMPTABLE_="" \

3091 -D_FORM_="syscmd(\`cat')" \

3092 $layout/html.m4.html $layout/form+dump.m4.html

3093 # Move to group

3094 if isuser "$owner"; then

3095 crview4article $rowid

3096 n=`query "SELECT count(*) FROM writeusers;"`

3097 ## err N=$n

3098 if [ $((n)) -gt 0 ]; then

3099 ## err ROWID=$rowid

3100 sql="SELECT growid || ':' || gname FROM movablegroups;"

3101 cat<<-EOF

3102 <div class="fold">

3103 `cgi_checkbox mv send id="mv"`<label

3104 for="mv">この話題をグループ所有に移動する</label>

3105 <div>

3106 <form action="?mvart" method="POST" enctype="multipart/form-data">

3107 移動先グループ:

3108 <select name="mv2grp">

3109 EOF

3110 query ".mode html"

3111 query<<-EOF |

3112 $sql

3113 .mode list

3114 EOF

3115 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3116 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3117 cat<<-EOF

3118 </select>

3119 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3120 そのグループに加入しているものに限られます)</p>

3121 <p>`cgi_checkbox cfm yes`<label>確認

3122 (この操作は元に戻すことができません)</label></p>

3123 `cgi_hidden blogrowid $rowid`

3124 `cgi_submit 移動`

3125 `cgi_reset Reset`

3126 </form>

3127 </div>

3128 </div>

3129 EOF

3130 fi

3131 # end of isuser "$owner"

3132 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3133 [ -n "$hexteams" ];}; then

3134 none="`echo なし|hexize`"

3135 cat<<-EOF

3136 <div class="fold">

3137 `cgi_checkbox mv2team send id="mv2team"`<label

3138 for="mv2team">この話題を以下のチームのものにする</label>

3139 <div><p>現在の所属チーム設定:

3140 `query "SELECT

3141 coalesce((SELECT val FROM blog_s

3142 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3143 AND key='team'),

3144 ':なし');"`</p>

3145 <form action="?mvart" method="POST" enctype="multipart/form-data">

3146 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3147 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3148 `cgi_hidden blogrowid $rowid`<br>

3149 `cgi_submit 移動`

3150 `cgi_reset Reset`

3151 </form></div></div>

3152 EOF

3153 fi

3154 }

3155 mvart() { # move diary to some group or team

3156 # or move blog of group to team which belong to the group

3157 blogrowid=`getpar blogrowid`

3158 cfm=`getpar cfm`

3159 ##### echo move blog:$blogrowid to $mv2grp | html p

3160 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3161 . ./s4-blog.sh

3162 if [ -z "$blogrowid" ]; then

3163 echo "無効な指定です(mvart)。" | html p

3164 return

3165 elif [ x"$cfm" != x"yes" ]; then

3166 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3167 elif { mv2grp=`getpar mv2grp`

3168 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3169 [ -n "$mv2grp" ]; }; then

3170 crview4article $blogrowid

3171 ########## TRANSACTION BEGIN

3172 query "BEGIN;"

3173 n=`query "SELECT count(*) FROM writeusers;"`

3174 ## err Nwriteuser=$n

3175 if [ $((n)) -gt 0 ]; then

3176 query<<-EOF

3177 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3178 WHERE key='owner'

3179 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3180 AND $mv2grp IN (SELECT growid FROM movablegroups);

3181 EOF

3182 fi

3183 query "END;"

3184 ########## TRANSACTION END

3185 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3186 [ -n "$mv2team" ];}; then

3187 # blog owner can move it to ANY team

3188 case "$mv2team" in

3189 'なし')

3190 cat<<-EOF

3191 DELETE FROM blog_s

3192 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3193 AND key='team';

3194 EOF

3195 ;;

3196 "") ;;

3197 *)cat<<-EOF

3198 BEGIN;

3199 REPLACE INTO blog_s(id, key, val)

3200 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3201 'team', '$mv2team');

3202 REPLACE INTO blog_s(id, key, val)

3203 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3204 'notify', 'all'); -- Change notify to all

3205 END;

3206 EOF

3207 esac | query

3208 fi

3209 blog_reply $blogrowid

3210 echo yes | html p

3211 }

3212 editart() { # $1=article-rowid $2=blogrowid

3213 rowid=${1%%[!A-Z0-9a-z_]*}

3214 blogrowid=${2%%[!A-Z0-9a-z_]*}

3215 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3216 echo "表示する記事番号が未指定です。" | html p

3217 return

3218 fi

3219 owner=`getvalbyid blog owner $blogrowid`

3220 title=`getvalbyid blog title $blogrowid`

3221 author=`getvalbyid article author $rowid`

3222 ## err EDITart: owner=$owner, author=$author

3223 if isgrpowner "$user" "$owner"; then

3224 : EDIT OK

3225 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3226 echo "本人か所有者しか編集できません." | html p

3227 return

3228 fi

3229 aid=`query "select id from article where rowid=$rowid;"`

3230 tmpout=$tmpd/editart.$$.out

3231 GF_ACTION="?replyblog+$blogrowid#$aid" \

3232 edittable $formdir/article.def article $rowid \

3233 > $tmpout

3234 rm -f /tmp/editart.out

3235 # Cannot use pipelining to m4 with genform() because of stdin stack

3236 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3237 -D_FORM_="syscmd(cat $tmpout)" \

3238 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3239 -D_BLOGS_= -D_DUMPTABLE_= \

3240 $layout/html.m4.html $layout/form+dump.m4.html

3241 }

3242 send2mem() {

3243 rowid=`getpar grp`

3244 rowid=${rowid%%[!0-9]*} # Cleaning

3245 if [ -z "$rowid" ]; then

3246 echo "グループが未指定です。" | html p

3247 return

3248 fi

3249 message=`getpar message`

3250 if [ -z "$message" ]; then

3251 echo "文章を入れてください。" | html p

3252 return

3253 fi

3254 grp=`getgroupbyid $rowid`

3255 members=`collectemail $grp`

3256 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3257 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3258 mailfrom="`gecos "$user"` <$mailfrom>"

3259 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3260 msg=$(cat<<-EOF

3261 $urlbase?grp+$rowid

3262 グループ $grp に所属する

3263 `gecos $user` さんよりメッセージ:

3264

3265 $message

3266 EOF

3267 )

3268 # smail rcpt subj (file)

3269 for m in $members; do

3270 echo "$msg" |

3271 MAIL_FROM=$mailfrom \

3272 SENDER=$noreply \

3273 REPLYTO=$mailfrom \

3274 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3275 smail "$m" "$sj"

3276 done

3277 cat<<EOF

3278 <p>以下のユーザに送信しました。</p>

3279 <pre>

3280 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3281 </pre>

3282 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3283 EOF

3284 }

3285 joingrpadmit() {

3286 # $1=yes/no $2=session-key

3287 if [ -z "$2" ]; then

3288 echo "bye bye" | html p; return

3289 fi

3290 t_usr=`session=$2 getpar adduser`

3291 t_grp=`session=$2 getpar group`

3292 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3293 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3294 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3295 echo "無効な加入依頼です。" | html p

3296 echo "有効期限が切れたか、

3297 他の管理者がいる場合は処理済みの可能性があります。" | html p

3298 return

3299 fi

3300 if ! isgrpowner "$user" "$t_grp"; then

3301 echo "グループ管理者のみの機能です。" | html p; return

3302 fi

3303 case $1 in

3304 yes) joingrp "$t_grp" "$t_usr" yes ;;

3305 no) joingrp "$t_grp" "$t_usr" no ;;

3306 *)

3307 echo "無効な指定です($1)。" | html p

3308 return ;;

3309 esac

3310 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3311 rcpts="`getgroupadminmails $t_grp` $user"

3312 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3313 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3314 に

3315 $t_usr

3316 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3317 しました。"

3318 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3319 query "delete from session where id='$2';"

3320 echo "$body" | html p

3321 }

3322

3323 joingrprequest() {

3324 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3325 jss="joingrp-`date +%s`-`genrandom 12`"

3326 addsession $jss +${memoplimitdays}days

3327 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3328 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3329 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3330 $url

3331 $user さんから

3332 グループ $1

3333 に加入依頼がありました。

3334

3335 承認する:

3336 $urlbase?joingrpadmit+yes+$jss

3337

3338 白紙に戻す:

3339 $urlbase?joingrpadmit+no+$jss

3340 EOF

3341 echo "管理者に加入依頼を出しました。

3342 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3343 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3344 }

3345 joingrp() {

3346 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3347 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3348 if isgrpowner "$user" "$1"; then

3349 isowner="yes"

3350 elif [ -n "$5" ]; then

3351 isowner="yes"

3352 else

3353 isowner=""

3354 fi

3355 ## err jg:isgrpowner: isowner="$isowner"

3356 if [ -n "$isowner" ]; then

3357 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3358 elif [ x"$2" != x"$user" ]; then # if user is not login user

3359 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3360 return

3361 elif [ x"$3" = x"no" ]; then

3362 : # Do not pursue those who leave

3363 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3364 : # Member can change own email address for the joining moderated group

3365 else # adding user is $user itself

3366 case `getgroupattr $1 regmode` in

3367 moderated)

3368 joingrprequest "$@" # Request only

3369 return

3370 ;;

3371 *)

3372 ;;

3373 esac

3374 fi

3375 qgname=`sqlquote "$1"`

3376 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3377 cond="where gname=$qgname and user='$2'"

3378 if [ x"$3" = x"yes" ]; then

3379 query "replace into grp_mem values($qgname, '$2');"

3380 # Notify joingrp to admin

3381 action="に加入しました。"

3382 if [ -n "$4" ]; then

3383 if msg=`emaildomaincheck "$4"`; then

3384 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3385 'string', '$4', NULL);"

3386 else

3387 echo $msg

3388 fi

3389 else

3390 query "delete from grp_mem_s $cond and key='email';"

3391 fi

3392 if [ -n "$5" ]; then # as ADMIN

3393 # Coming here means newly created group

3394 sql="select case\

3395 when (select count(*) from grp_mem where gname=$qgname)=1\

3396 then (select user from grp_mem\

3397 where gname=$qgname and user='$user')\

3398 else '' end; "

3399 err NewGrpChk: $sql

3400 if [ -n "`query \"$sql\"`" ]; then

3401 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3402 query "replace into grp_adm values($qgname, '$user');"

3403 fi

3404 fi

3405 else

3406 query "begin;

3407 delete from grp_mem $cond;

3408 delete from grp_mem_s $cond;

3409 delete from grp_mem_m $cond;

3410 delete from grp_adm $cond;

3411 delete from grp_adm_s $cond;

3412 delete from grp_adm_m $cond;

3413 end;"

3414 action="から脱退しました。"

3415 fi

3416 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3417 $url?grp+$grid

3418 $user (`gecos $user`)さんが

3419 グループ $1

3420 $action

3421 EOF

3422 }

3423 grp_add_team() (

3424 # $1=grp-rowid $2=team $3...=user-rowid(s)

3425 grp=`getgroupbyid $1`

3426 team=$2; shift; shift

3427 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3428 { echo "BEGIN;"

3429 for user; do

3430 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3431 '$grp',\

3432 (SELECT name FROM user WHERE rowid=$user),\

3433 'team', 'string', '$team');"

3434 done

3435 echo "END;"

3436 } | query

3437 )

3438 grp_rm_team() (

3439 # $1=grp-rowid $2=team $3...=user-rowid(s)

3440 grid=$1

3441 qgrp=$(sqlquote "`getgroupbyid $grid`")

3442 team=$2; shift; shift

3443 [ -z "$grid" -o -z "$team" ] && return

3444 { echo "BEGIN;"

3445 for user; do

3446 echo "DELETE FROM grp_mem_m\

3447 WHERE gname=$qgrp \

3448 AND user=(SELECT name FROM user WHERE rowid=$user)\

3449 AND key='team' AND val='$team';"

3450 done

3451 cat<<-EOF

3452 DELETE FROM blog_s

3453 WHERE rowid=(

3454 SELECT rowid

3455 FROM blog_s a

3456 WHERE key='team'

3457 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3458 AND NOT EXISTS (SELECT * FROM grp_mem_m

3459 WHERE key='team' AND val=a.val -- a.val=team

3460 AND gname = (SELECT val FROM blog_s b

3461 WHERE a.id=b.id AND key='owner')

3462 ));

3463 EOF

3464

3465 echo "END;"

3466 } | query

3467 )

3468 grp_reg_adm() {

3469 # $1=grp-rowid $2...=user-rowid

3470 grid=$1

3471 grp=`getgroupbyid "$1"`

3472 if [ -z "$grp" ]; then

3473 echo "無効なグループIDです" | html p; return

3474 fi

3475 if ! isgrpowner "$user" "$grp"; then

3476 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3477 fi

3478 shift

3479 for urid; do

3480 newadm=`query "select name from user where rowid=$urid;"`

3481 if [ -z "$newadm" ]; then

3482 echo "指定ユーザIDがおかしいようです。" | html p; return

3483 fi

3484 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3485 err ismember $newadm $grp

3486 if ismember $newadm "$grp"; then

3487 # OK, go ahead

3488 getgname="(select gname from grp where rowid=$grid)"

3489 query "replace into grp_adm values($getgname, '$newadm');"

3490 # confirm insertion

3491 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3492 if [ -n "`query \"$sql;\"`" ]; then

3493 echo "追加完了: $newadm" | html p

3494 else

3495 echo "追加失敗($1 $urid)" | html p

3496 fi

3497 fi

3498 showgroup $grid

3499 done

3500 }

3501 dt_rowhack() {

3502 # From: <TR>

3503 # ....

3504 # <TD>rowclass=foo</TD>

3505 # </TR>

3506 # To: <TR class="foo">....<TD>foo</TD></TR>

3507 sed -e '

3508 /^<TR>/ {

3509 :loop

3510 s/\n//

3511 N

3512 /<\/TR>/ {

3513 s/\n//

3514 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3515 n

3516 }

3517 $q

3518 b loop

3519 }'

3520 }

3521 dumptable() {

3522 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3523 # textのフィールドだけ全てダンプにしたほうがいいか

3524 # $DT_VIEW sets link

3525 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3526 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3527 VIEW=${DT_VIEW-replyblog}

3528 if [ -n "$VIEW" ]; then

3529 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3530 fi

3531 sqlfile=$tmpd/dump.sql

3532 : > $sqlfile # ensure to be empty

3533 printf '.mode html\n.header 1\n' > $sqlfile

3534 # $DT_CHLD=ChildTable:BindColumn

3535 if [ -n "$DT_CHLD" ]; then

3536 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3537 cat<<-EOF >> $sqlfile

3538 -- presql

3539 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3540 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3541 EOF

3542 # Speed up counting of new articles

3543 cat<<-EOF >> $sqlfile

3544 -- presql2

3545 DROP TABLE IF EXISTS _counts;

3546 CREATE TEMPORARY TABLE _counts AS

3547 SELECT $_i, count($_i) cnt

3548 FROM $_t GROUP BY $_i;

3549 /* Prepare NEW count table */

3550 CREATE TEMPORARY TABLE _target AS

3551 SELECT b.rowid trowid, b.id

3552 FROM "$2" b JOIN "$2_s" s

3553 ON b.id=s.id AND s.key='owner'

3554 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3555

3556 DROP TABLE IF EXISTS _children;

3557 CREATE TEMPORARY TABLE _children AS

3558 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3559 FROM (SELECT t.trowid, t.id $_i, a.id

3560 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3561 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3562

3563 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3564 DROP TABLE IF EXISTS _news;

3565 DROP VIEW IF EXISTS _news;

3566

3567 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3568 -- INSERT INTO _news

3569 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3570 CREATE TEMPORARY VIEW _news AS

3571 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3572 FROM (SELECT DISTINCT id FROM _target)

3573 a LEFT JOIN

3574 (SELECT $_i, count(ctime) newcnt

3575 FROM _children x

3576 WHERE ctime > coalesce((SELECT time from myacclog

3577 WHERE tblrowid=x.trowid),

3578 '1970-01-01')

3579 GROUP BY $_i) b

3580 ON a.id=b.$_i;

3581 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3582 EOF

3583 # REMOVE next line until 2019/5/1

3584 cntall="(select count($_i) from $_t where $_i=a.id)"

3585 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3586 # REMOVE next assignment until 2019/5/1

3587 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3588 and id in (select id from $_t where $_i=a.id) \

3589 and val > coalesce((select time from myacclog where \

3590 tblrowid=a.rowid),\

3591 '1970-01-01'))"

3592 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3593 cnt="$cntnew as '新着', $cntall as '総数',"

3594 dt_class=" td2r td3r dumpblogs"

3595 fi

3596 # Construct join expression

3597 eav="" scols=""

3598 pk=`gettblpkey $2`

3599 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3600 substr=${substr:-%s}

3601 for col in ${3:-`gettbl_s_cols $2`}; do

3602 valvar=val

3603 case $col in

3604 gecos) scols="$scols${scols:+, }${col#}"

3605 continue ;; # built-in column name

3606 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3607 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3608 case "$as" in

3609 *:*=*) cnd=${as#*:}

3610 h=${cnd%%=*} v=${cnd#*=}

3611 h=`sqlquotestr "$h"`

3612 v=`sqlquotestr "$v"`

3613 valvar="CASE val WHEN $h THEN $v END"

3614 as=${as%%:*} ;;

3615 esac

3616 ;;

3617 *) as=${col} ;;

3618 esac

3619 ss=`printf "$substr" "$valvar"`

3620 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3621 scols="$scols${scols:+, }b.$as"

3622 done

3623 #case author when '$user' then a.rowid else '---' end as ID,

3624 if [ -n "$DT_SQL" ]; then

3625 echo "$DT_SQL"

3626 else

3627 cat<<-EOF

3628 SELECT a.rowid as LINK, $cnt $scols

3629 FROM $2 a LEFT JOIN

3630 (SELECT $pk,$eav,

3631 max(CASE key

3632 WHEN 'owner'

3633 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3634 as gecos

3635 FROM ${2}_s c GROUP BY $pk)

3636 b ON a.$pk=b.$pk $4;

3637 EOF

3638 fi >> $sqlfile

3639 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3640 sqlog<<-EOF

3641 *** SQL-file: $sqlfile ***

3642 `cat $sqlfile`

3643 EOF

3644 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3645 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3646 cat $sqlfile >> tmp/sql

3647 # query ".read $sqlfile" > $tmpd/foo

3648 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3649 cp $tmpd/foo tmp/

3650 err "----- `gdate +%FT%T.%3N` ------------555555"

3651 ## $ddd LINE exists at the end of this function

3652 fi

3653 printf '.mode list\n.header 0\n' >> $sqlfile

3654 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3655 <div>

3656 <div class="dumptable">

3657 <table class="b$dt_class">

3658 `query ".read $sqlfile"`

3659 </table>

3660 </div>

3661 </div>

3662 EOF

3663 ### `query ".read $sqlfile"`

3664 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3665

3666 ## REMOVE THIS!

3667 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3668 }

3669

3670 par2table() (

3671 # copy current parameters of par into destination table

3672 # $1=definition-file

3673 # Using $user and $session

3674 # Return value:

3675 # 0: Stored successfully

3676 # 1: Insufficient fillings

3677 # 2: No permission to modify the record

3678 # 3: Invalid rowid

3679 # 4: SUCCESS to delete

3680 # 5: Stop deletion for lack of confirm check

3681 # 6: Password length too short

3682 # 7: Password mismatch

3683 # 8: Old password incorrect

3684 # 9: Duplicated post

3685 rowid=`getpar rowid`

3686 if [ ! -e $1 ]; then

3687 echo "テーブル定義ファイルが見付かりません" | html p

3688 exit 1

3689 fi

3690 tbl=${1%.def}

3691 tbl=${tbl##*/}

3692 if [ -n "$rowid" ]; then # Modify existing entry

3693 if [ x"$tbl" = x"user" ]; then

3694 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3695 elif [ x"$tbl" = x"grp" ]; then

3696 sql="select gname from $tbl where rowid=$rowid;"

3697 ##err p2t:grp:q $sql

3698 isgrpowner "$user" "`query $sql`" && rowowner=$user

3699 elif [ x"$tbl" = x"blog" ]; then

3700 # Check if owner in blog_s

3701 blogowner=`getvalbyid blog owner "$rowid"`

3702 if isgrpowner "$user" "$blogowner"; then

3703 rowowner=$user

3704 else

3705 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3706 fi

3707 else

3708 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3709 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3710 fi

3711 ### err rowowner=$rowowner

3712 if [ x"$user" != x"$rowowner" ]; then

3713 echo "他人のレコードはいじれないの" | html p

3714 return 2

3715 elif [ -z "$rowowner" ]; then

3716 echo "指定したレコードはないみたい" | html p

3717 return 3

3718 fi

3719 rm=`getpar rm` cfm=`getpar confirm`

3720 # Editing existent entry

3721 if [ x"$rm" = x"yes" ]; then

3722 if [ x"$rm$cfm" = x"yesyes" ]; then

3723 query "delete from $tbl where rowid=$rowid;"

3724 return 4

3725 else

3726 echo "消去確認のチェックがないので消さなかったの..." | html p

3727 return 5

3728 fi

3729 fi

3730 fi

3731

3732 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3733 if [ -n "$rowid" ]; then

3734 # Update of existing record

3735 for col in `gettblcols $tbl`; do

3736 val=`getparquote $col`

3737 [ -z "$val" ] && continue

3738 ## err query "update $tbl set $col=$val where rowid=$rowid"

3739 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3740 sql="update $tbl set $col=$val where rowid=$rowid;"

3741 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3742 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3743 ## User name cannot be changed with interface provided with this

3744 ## script. But we offer the trigger to change owner user

3745 ## of blog_s table.

3746 #err "select quote($col) from $tbl where rowid=$rowid;"

3747 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3748 cat<<-EOF | query

3749 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3750 -- should use EXCLUSIVE transaction outside of this.

3751 SAVEPOINT par2table;

3752 $sql

3753 update blog_s set val=$val

3754 where key='owner' and val=$old;

3755 RELEASE SAVEPOINT par2table;

3756 EOF

3757 ## XX: DIRTY Hack Ends here

3758 ## We should keep blog's owner as a single column which has

3759 ## foreign key constraint with primary key of grp/user.

3760 else

3761 query "$sql"

3762 fi

3763 done

3764 # Then, set up $pval for further insertion of tbl_s and tbl_m

3765 for col in `gettblpkey $tbl`; do

3766 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3767 pval="$pval${pval:+, }\"$val\""

3768 done

3769 else

3770 # New entry

3771 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3772 # Avoid empty repost of article.

3773 if [ x"$tbl" = x"article" ]; then

3774 # If rowid is empty and ID exists in article-table, that is REPOST!

3775 aid=`getpar id`

3776 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3777 if [ -n "$xaid" ]; then

3778 # REPOST of article

3779 html p <<-EOF

3780 書き込み直後のリロードなので上書きを回避します。

3781 最新記事は末尾の「再読み込み」ボタンから見てください。

3782 EOF

3783 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3784 return 9 # STOP Duplicated posting

3785 fi

3786 fi

3787 # Generate values() for primary keys

3788 for col in `gettblpkey $tbl`; do

3789 # Genuine primary keys for _m and _s

3790 val=`getvalquote $tbl $col`

3791 [ -z "$val" ] && continue

3792 pval="$pval${pval:+, }$val"

3793 done

3794 ##err pval=$pval

3795 for col in `gettblfkey $tbl`; do

3796 # args for values() to insertion into master table

3797 val=`getvalquote $tbl $col`

3798 [ -z "$val" ] && continue

3799 formaster=$formaster"${formaster:+, }$val"

3800 done

3801 formaster="$pval${formaster:+, }$formaster"

3802 ## err formaster=$formaster

3803 if [ -z "$formaster" ]; then

3804 echo "項目を全て埋めてください" | html pre

3805 return 1

3806 fi

3807 ## err "replace into $tbl values($formaster);"

3808 query "replace into $tbl values($formaster);"

3809 ## Insertion to master table, done

3810 fi

3811

3812 transaction=$tmpd/sqlfile.sql; touch $transaction

3813 for kt in s m; do

3814 tb2=${tbl}_$kt

3815 for col in `gettbl_${kt}_cols $tbl`; do

3816 ptype=`getpartype $col "limit 1"`

3817

3818 # First, check update of existing entries in _m

3819 if [ $kt = m ]; then

3820 # sessID|address.1.22|string|Somewhere-x.y.z

3821 sql=""

3822 ##err dots from query "select var from par where var like '$col.%';"

3823 for v in `query "select var from par where var like '$col.%';"`; do

3824 # v=address.1.22

3825 st_rowid=${v##*.}

3826 origcol=${v%%.*} # original column derived from

3827 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3828 ##case `getpartype $v` in

3829 ## err CASE `gettbl_coltype $tbl/$origcol` in

3830 ## err edit flag = `getpar action.$v`

3831 case `getpar action.$v` in

3832 rm)

3833 if [ x`getpar confirm.$v` = x"yes" ]; then

3834 newsql="delete from $tb2"

3835 else

3836 echo "削除確認未チェック" | html p

3837 fi ;;

3838 edit)

3839 case `gettbl_coltype $tbl/$origcol` in

3840 image|document|binary)

3841 file=$tmpd/`getparfilename $v`

3842 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3843 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3844 continue

3845 fi

3846 ## err type=file=$file

3847 [ -z "$file" ] && continue

3848 bn=`sqlquotestr "${file##*/}"`

3849 bin="X'"$(hexize "$file")"'"

3850 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3851 type=\"file:$ct\"

3852 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3853 cachedir=`getcachedir "$tbl/$rowid"`

3854 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3855 rm -rf $cachedir

3856 ;;

3857 *)

3858 newsql="update $tb2 set val=(select val from par where var \

3859 like '$col.%.$st_rowid')"

3860 ;;

3861 esac

3862 ;;

3863 *) # maybe "keep", do not modify value

3864 continue

3865 ;;

3866 esac

3867 # err newsql=$newsql

3868 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3869 done

3870

3871 if [ x"$bin" = x"NULL" ]; then

3872 ## err repl:normal sql=`echo $sql`

3873 if [ -n "$transaction" ]; then

3874 cat<<-EOF >> $transaction

3875 $sql

3876 DELETE FROM $tb2 WHERE type='string' AND val='';

3877 EOF

3878 else

3879 query "$sql

3880 delete from $tb2 where type='string' and val='';"

3881 ## err repl:normal done

3882 fi

3883 else

3884 # Binary update line is TOO LONG to pipelining

3885 sqlfile="$tmpd/sqlf.$$"

3886 if [ -n "$transaction" ]; then

3887 echo "$sql" >> $transaction

3888 else

3889 echo "$sql" > $sqlfile

3890 query ".read $sqlfile"

3891 fi

3892 fi

3893 # Rest of kt==m: set multiple mode

3894 nr=`getparcount $col`

3895 else

3896 nr=1 # for kt==s, number of records is 1

3897 fi

3898

3899 i=0

3900 while [ $i -lt $nr ]; do

3901 limit="limit 1 offset $i"

3902 i=$((i+1)) # increase beforehand against continue

3903 val=`getvalquote $tbl $col "$limit"`

3904 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3905 ## err $col=$val

3906 bin=NULL

3907 ## err partype$col=`getpartype $col "$limit"`

3908 case $ptype in

3909 file) file=$tmpd/`getparfilename $col "$limit"`

3910 ## err parfile-$col=$file

3911 [ -z "$file" ] && continue

3912 bin="X'"$(hexize "$file")"'"

3913 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3914 type=\"file:$ct\" ;;

3915 "*"*) continue ;; # foreign table

3916 *) type=\"string\" ;;

3917 esac

3918 case `gettbl_coltype $tbl/$col` in

3919 password) # special care for password

3920 # name={password,pswd1,pswd2}

3921 p1=`getpar pswd1 "$limit"`

3922 if [ -z "$p1" ]; then

3923 continue # SKIP password setting, if p1 is empty

3924 else

3925 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3926 ## err pswd=$pswd

3927 if pwcheck "$pswd"; then

3928 if [ x"$p1" = x"$p2" ]; then

3929 case "$p1" in

3930 ??????????*) ;;

3931 *) echo "パスワードは10字以上にしてください。" | html p

3932 return 6;;

3933 esac

3934 val="\"`echo $p1|mypwhash`\""

3935 else

3936 echo "2つの新パスワード不一致" | html p

3937 return 7

3938 fi

3939 else

3940 echo "旧パスワード違います" | html p

3941 return 8

3942 fi

3943 fi

3944 ;;

3945 esac

3946 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3947 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3948 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3949 if [ x"$bin" = x"NULL" ]; then

3950 ## err Normal-query: `echo $sql`

3951 if [ -n "$transaction" ]; then

3952 echo "$sql" >> $transaction

3953 else

3954 query "$sql"

3955 fi

3956 else

3957 sqlfile="$tmpd/query.$$"

3958 ## err sqlfile=`ls -lF $sqlfile`

3959 if [ -n "$transaction" ]; then

3960 echo "$sql" >> $transaction

3961 else

3962 echo "$sql" > $sqlfile

3963 query ".read $sqlfile"

3964 fi

3965 fi

3966 ## err p2t done

3967 done

3968 done

3969 done

3970 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3971 -- We cannot use transaction here, because groupupdate may use it.

3972 SAVEPOINT pa2table_insert;

3973 .read $transaction

3974 RELEASE SAVEPOINT pa2table_insert;

3975 EOF

3976 rc=$?

3977 [ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag

3978 ## err "Table:$tbl update done "

3979 return $rc

3980 )

3981 genform() {

3982 # $1 = form definition file

3983 # $2, $3 (optional)= table name and ROWID

3984 # If $GF_VIEWONLY set and nonNull, output values without form

3985 # If $GF_ARGS set, use it as content-strings in the form

3986 # If $GF_OWNER set, use it as value of name="owner"

3987 # If $GF_STAGE set, use it as value of name="stage"

3988 forms="" hiddens="" rowid=$3

3989 if [ ! -e "$1" ]; then

3990 echo "そのようなデータベースはないようです($2)。" | html p

3991 return

3992 elif [ -n "$2" ]; then

3993 rec=`query "select * from $2 where rowid='$rowid';"`

3994 if [ -z "$rec" ]; then

3995 pk=`gettblpkey $2`

3996 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3997 rec=`query "select rowid from $2 where $pk='$rowid';"`

3998 rowid=$rec

3999 rec=$3

4000 fi

4001 if [ -z "$rec" ]; then

4002 echo "そんなレコードはないみたいね..." | html p

4003 return

4004 fi

4005 fi

4006 if [ -z "$GF_VIEWONLY" ]; then

4007 rm='<input id="rm" name="rm" type="checkbox"

4008 value="yes"><label for="rm">このエントリの削除</label>

4009 <span>ほんとうに消しますよ(確認)!

4010 <input name="confirm" type=checkbox value="yes">はい</span>'

4011 fi

4012 # Image Cache dir

4013 ## err genform: getcache=$2/$rowid

4014 td=`getcachedir "$2/$rowid"`

4015 while IFS=: read prompt name keytype type args; do

4016 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4017 sp="${args:+ }"

4018 form="" val=""

4019 if [ -n "$rowid" ]; then

4020 # err genform2a: Seeking for "$2.$name, type=$type"

4021 rawval=`getvalbyid $2 $name $rowid $td`

4022 val=`echo "$rawval"|htmlescape`

4023 ## err genform3a: getvalbyid $2 $name $rowid $td

4024 ## err genform3b: val="[$val]" type="$type"

4025 fi

4026 if [ -n "$GF_VIEWONLY" ]; then

4027 is_hidden "$2" "$name" && continue

4028 fi

4029 case "$type" in

4030 text*)

4031 cgiform=cgi_multi_$type

4032 if [ -s $td/$name.count -a -n "$val" ]; then

4033 form=`$cgiform $name $td`

4034 val=$(echo "$val"|

4035 while read fn; do

4036 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4037 </td></tr>$nl"

4038 done)

4039 val="<table>$nl$val$nl</table>"

4040 else

4041 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4042 form=`cgi_$type $name "$rawval" "$args"`

4043 fi

4044 ;;

4045 [Rr][Aa][Dd][Ii][Oo])

4046 fh="<label><input type=\"radio\" name=\"$name\""

4047 form="`echo $args|sed -e \

4048 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4049 ;;

4050 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4051 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4052 ;;

4053 [Ss][Ee][Ll][Ee][Cc][Tt])

4054 fh="<select name=\"$name\">$nl"

4055 form=$(for l in $args; do

4056 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4057 done)

4058 if [ -n "$val" ]; then

4059 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4060 fi

4061 form="$fh$form</select>"

4062 ;;

4063 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4064 if [ -s $td/$name.count ]; then

4065 form=`cgi_multi_file $name $td "$args"`

4066 if [ -n "$val" ]; then

4067 hrfb="$myname?showattc+$2_m"

4068 val=$(echo "$rawval" \

4069 | while read fn; do

4070 data=`percenthex "$td/$fn"`

4071 #ct=`cat $td/$fn.content-type`

4072 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4073 ri=`cat "$td/$fn.rowid"`

4074 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4075 #imgsrc="<img src=\"data:$ct,$data\">"

4076 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4077 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4078 done)

4079 fi

4080 else

4081 form="<input type=\"file\" name=\"$name\" $args>"

4082 if [ -n "$val" ]; then

4083 imgs=$(echo "$rawval"\

4084 |while read fn;do

4085 data=`percenthex "$td/$fn"`

4086 echo "<img src=\"data:image/png,$data\">$fn<br>"

4087 done)

4088 form=$form"<br>$imgs"

4089 val=$imgs # 2015-06-15

4090 else

4091 form="<input type=\"file\" name=\"$name\" $args>"

4092 fi

4093 fi

4094 ;;

4095 [Hh][Ii][Dd][Dd][Ee][Nn])

4096 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4097 args="value=\"$GF_STAGE\""

4098 fi

4099 form="<input type=\"hidden\" name=\"$name\" $args>"

4100 prompt='' # Remove prompt

4101 ;;

4102 [Aa][Uu][Tt][Hh][Oo][Rr])

4103 [ -n "$GF_VIEWONLY" ] && continue

4104 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4105 prompt="" ;;

4106 [Oo][Ww][Nn][Ee][Rr])

4107 [ -n "$GF_VIEWONLY" ] && continue

4108 val=${GF_OWNER:-$val}

4109 val=${val:-$user}

4110 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4111 prompt="" ;;

4112 [Uu][Ss][Ee][Rr])

4113 # XXX: is null $user ok?

4114 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4115 [ -n "$GF_VIEWONLY" ] && continue

4116 form="$user"

4117 ;;

4118 [Pp]assword)

4119 [ -n "$GF_VIEWONLY" ] && continue

4120 form="`cgi_passwd`"

4121 val=""

4122 ;;

4123 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4124 [ -n "$GF_VIEWONLY" ] && continue

4125 if [ -z "$rowid" ]; then

4126 val=`genserial`

4127 fi

4128 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4129 prompt="" ;;

4130 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4131 prompt=""

4132 ;;

4133 parent|path|blog*)

4134 prompt=""

4135 ;;

4136 "*"*)

4137 tail=$tail"``"

4138 continue ;;

4139 esac

4140 if [ -n "$prompt" ]; then

4141 if [ -n "${GF_VIEWONLY}" ]; then

4142 form=$val

4143 else

4144 :

4145 fi

4146 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4147 else

4148 hiddens=$hiddens$nl"$form"

4149 fi

4150 done < $1

4151 # enctype="multipart/form-data"

4152 cat<<EOF

4153 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4154 ${rowid:+$rm}

4155 <table class="b $2">

4156 $forms

4157 </table>$hiddens

4158 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4159 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4160 EOF

4161 if [ -z $GF_VIEWONLY ]; then

4162 cat<<EOF

4163 <input type="submit" name="sub" value="OK">

4164 <input type="reset" name="res" value="Reset">

4165 EOF

4166 fi

4167 cat<<EOF

4168 $GF_ARGS</form>

4169 $tail

4170 EOF

4171 }

4172 edittable() {

4173 # $1=form-def $2=table $3 rowid

4174 genform "$@"

4175 }

4176 viewtable() {

4177 GF_VIEWONLY=1 genform "$@"

4178 }

4179 showattc() {

4180 # $1=table_m $2=rowid &optional $3=RawFlag

4181 ## err \$1=$1 \$2=$2 \$3=$3

4182 if ! isfilereadable $user $1 $2; then

4183 contenttype; echo

4184 echo "このファイルは管理者のみしか見られません" | html p

4185 putfooter; exit

4186 fi

4187 idir=`umask 002; mktempd` || exit 1

4188 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4189 bin=$idir/$myname-$$.bin

4190 sql="select quote(bin) from $1 where rowid='$2';"

4191 ## err showattc: sql: $sql

4192 sq $db "$sql" | unhexize > $bin

4193 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4194 type=${tv%//*} fn=${tv#*//}

4195 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4196 ct=${type#file:}

4197 case $ct in # all text/* changed to text/plain

4198 text/*)

4199 charset=`nkf -g $bin|cut -d' ' -f1`

4200 case $charset in

4201 ASCII*) charset="" ;;

4202 esac

4203 if [ -z "$3" ]; then

4204 ct="text/html${charset:+; charset=$charset}"

4205 link="?showattc+$1+$2+raw"

4206 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4207 | sed 's,^,<span></span>,' \

4208 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4209 -D_LINK_="$link" \

4210 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4211 exit $?

4212 fi

4213 ct="text/plain${charset:+; charset=$charset}"

4214 ;;

4215 esac

4216 contenttype "$ct"

4217 echo "Content-Disposition: filename=\"$fn\""

4218 echo "Content-Length: " `cat $bin | wc -c`; echo

4219 #echo "Content-Type: " ${type#file:}; echo

4220 cat $bin

4221 }

4222 #

4223 # Some default stupid handler on CGI values

4224 #

4225 default_storedb() {

4226 # ARG: $1=table-def-file

4227 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4228 tbl=`basename $1`

4229 tbl=${tbl%.def}

4230 cols="`grep :text $1|cut -d: -f2`"

4231 col=`echo "$cols"|head -1`

4232 vcol=`getpar $col`

4233 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4234 if [ -n "$vcol" ]; then

4235 par2table $1

4236 else

4237 return 2 # No insertion occurred

4238 fi

4239 }

4240

4241 default_view() { # $1=def-file

4242 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4243 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4244 default_storedb "$@"

4245 query "select rowid from $tbl order by rowid desc;" \

4246 | while read rowid; do

4247 viewtable $1 $tbl $rowid

4248 done | _m4 -D_TITLE_="$tbl" \

4249 -D_FORM_="`genform $1`" \

4250 -D_DUMPTABLE_="syscmd(cat)" \

4251 $layout/html.m4.html $layout/form+dump.m4.html

4252 }

4253 default_viewtext() { # $1=def-file

4254 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4255 default_storedb "$@"

4256 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4257 | _m4 -D_TITLE_="$tbl" \

4258 -D_FORM_="`genform $1`" \

4259 -D_DUMPTABLE_="syscmd(cat)" \

4260 $layout/html.m4.html $layout/form+dump.m4.html

4261 }

4262 default_smail() {

4263 default_storedb "$@"

4264 if [ $? -eq 2 ]; then

4265 _m4 -D_TITLE_="入力" \

4266 -D_FORM_="`genform $1`" \

4267 -D_DUMPTABLE_="" \

4268 $layout/html.m4.html $layout/form+dump.m4.html

4269 return

4270 fi

4271 cond=""

4272 for pk in `gettblpkey $tbl`; do

4273 pv=$(sqlquote "$(getpar $pk)")

4274 cond="$cond${cond:+ and }$pk=$pv"

4275 done

4276 sql="select rowid from $tbl where $cond;"

4277 rowid=`query "$sql"`

4278 ## err smail1 - "$sql" "-> rowid=$rowid"

4279

4280 while IFS=: read prompt name keytype type args; do # Read from $1

4281 val=`getpar $name`

4282 if [ -n "$val" ]; then

4283 text="$text

4284 $prompt

4285 $name=$val

4286 ---------------------------------------------------------"

4287 fi

4288 case "$type" in

4289 image|document|file)

4290 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4291 fns=$(echo "$fn"|while read fn; do

4292 err mv $tmpd/$fn.orig $tmpd/$fn

4293 mv $tmpd/$fn.orig $tmpd/$fn

4294 rm $tmpd/$fn.rowid # Remove cache flag

4295 ## err "`ls $tmpd/$fn`"

4296 echo $fn

4297 done)

4298 files="$files $fns"

4299 ;;

4300 esac

4301 done < $1

4302 ## err FILES=$files "`ls -lF $tmpd`"

4303 subj="from ${REMOTE_ADDR}"

4304 (echo "$url"

4305 echo "への書き込みがありました。"

4306 echo "------"

4307 echo "$text"

4308 ) | (cd $tmpd &&

4309 err LS="`ls -lF`" &&

4310 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4311 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4312 echo "以下の内容で送信しました。" | html p

4313 viewtable $1 $tbl \

4314 `query "select rowid from $tbl order by rowid desc limit 1;"`

4315 echo "戻る" | html a "href=\"?\""

4316 }