s4: d78944aa1cd2 s4-funcs.sh

s4

view s4-funcs.sh @ 738:d78944aa1cd2

Merged with default

author	HIROSE Yuuji <yuuji@gentei.org>
date	Thu, 04 Jun 2020 07:38:18 +0900
parents	23f17b1aebc3 441768a11b84
children	c892a3633baa

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 skey='skey' # Changed in s4-annex, if ANNEX mode

20 userupdateflag=$dbdir/userupdate

21 sesstb=tmp.sess

22 workdb=$dbdir/tmpdata.sq3

23 listentlimit=${LISTENTLIMIT:-30}

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

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

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

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

28 templ=${TEMPL:-templ}

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

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

31 imgdir=${IMGDIR:-img}

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

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

34 msgdir=$templ/msg

35 timeout="+2 days"

36 memoplimitdays="7"

37 dumpcollen=22

38 #thumbxy=120x120

39 thumbxy=96x96

40 iconxy_S=80x80

41 iconxy_M=400x400

42 maximagexy=1600x1600

43 ### maximagexy=400x400

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

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

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

47 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"'

49 file_accept_help="

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

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

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

53 "

54 file_warn="$file_accept_help

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

56 blogreadflagrowid=0

57 blogcutoffflagrowid=-1

58 whatsnewdays=${WHATS_NEW_DAYS:-14}

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

60 session=$main_session

62 tconfs=""

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

64 conftbl=_tblconf

65 nl="

66 "

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

68 iconcachekey="profimgcache_S"

70 # Start debug logging

71 exec 3>> $tmpdir/debug.out

72 err() {

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

74 }

75 # If S4MASTERDB is set, behave as ANNEX s4

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

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

78 fi

81 case "$HTTP_USER_AGENT" in

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

83 *) touchpanel="" ;;

84 esac

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

87 : <<EOF

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

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

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

92 レポート提出管理

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

95 [2016]

96 7/12 根本への反省

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

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

100 [以下2015]

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

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

103 Web

104 締切設定

105

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

107 ○自分の提出物リスト

108

109 7/19 ○設置

110 ○一斉送信

111 ○getparfilename の tmpd の扱い

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

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

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

115 ○添付ファイル回収

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

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

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

119

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

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

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

123 ○レポート提出モード

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

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

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

127

128

129 7/9 ○管理者の追加

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

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

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

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

134

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

136

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

138 ○グループ検索

139

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

141

142

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

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

145

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

147 ○6/1 par2tableを triplex 対応に

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

149 →とすると一気に

150

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

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

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

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

155

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

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

158 ## 1. passwd

159 ## 2. grp

160 ## 3. grp_mem

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

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

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

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

165

166 ## ● listの定義:

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

168 ## ● hashの定義:

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

170

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

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

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

174 ## csv2sq3 で .csv.sq3 の Makefile

175

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

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

178 ## type := root | comment

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

180 ## unique(id, filename)

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

182

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

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

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

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

187 ## できたー!

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

189 ↓

190 ↓以下に変更

191 with recursive allmem as

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

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

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

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

196

197

198 with recursive allmem as

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

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

201 where allmem.val=grp_m.gname)

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

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

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

205

206

207 ## triggerもできた。

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

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

210

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

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

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

214 ## グループ管理

215 ## 個人情報管理

216 ##

217

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

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

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

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

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

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

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

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

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

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

228

229 ## ログテーブル

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

231

232

233

234 ■表設計

235 * 3つの表に分散管理

236 id格納表 + hash表 + list表

237 * *_s *_m

238

239

240

241 user, user_map, user_col

242

243 ■抽象エントリタイプ

244 * user

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

246 * group

247 権限判定に利用

248 * serial

249 自動idとして機能

250 * password

251 入力 type=passwordで入力

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

253 * session

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

255 * text

256 入力 type=text

257 * textarea

258 入力 textarea

259 * image|document

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

261 * owner

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

263 * gowner

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

265 * timestamp

266 datetime()

267 * parent

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

269 * path

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

271

272 格納タイプ

273 * list

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

275 * hash

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

277

278 オブジェクトタイプ

279 * entry

280 id, title, owner

281 * textpart

282 id, parentID, text

283 * binarypart

284 id, parentID, contenttype, filename, content

285 * content

286 hash(textpart), list(binarypart)

287 * topic

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

289 * reply

290 id, parentID, content

291 * blog

292 list(entry)

293 blog = [topic, list(reply)]

294

295

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

297 "text" => "hogehoge ..",

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

299 "author" => "taro",

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

301 "parent" => "/",

302 "path" => "/1",

303 "text" => "blah, blah, ....",

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

305 {"serial" => 2,

306 "author" => "hanako",

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

308 "parent" => "/",

309 "path" => "/2",

310 "text" => "blah, blah, ....",

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

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

313

314

315 user:=

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

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

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

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

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

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

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

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

324

325 変換表

326 /user/email=m

327

328 blog:=

329 シリアル:id:p:serial

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

331 所有者:owner:s:owner:

332 時刻:ctime:s:stamp:

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

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

335

336 article:=

337 シリアル:id:p:serial

338 筆者:author:s:owner

339 時刻:ctime:s:stamp:

340 参照元:parent:s:parent:

341 パス:path:s:path:

342 本文:text:s:textarea:

343 画像:image:m:image:

344

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

346

347

348 EOF

349

350 logstart() {

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

352 }

353 logend() {

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

355 }

356 sqlog() {

357 logstart

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

359 cat >> $querylog

360 else

361 echo "$*" >> $querylog

362 fi

363 logend

364 }

365 sq() {

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

367 logstart

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

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

370 else

371 echo "$@" >> $querylog

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

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

374 fi

375 logend

376 }

377 dbsetup() {

378 pipedir=$tmpdir/pipedir

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

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

381 suf=`date +%s`

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

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

384 mkfifo $sqi $sqo

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

386 sq $db < $sqi &

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

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

389 exec 2>> $tmpdir/error.out

390 fi

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

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

393 rm $sqi

394 # Attach supplemental DB

395 cat >&5 <<-EOF

396 .output /dev/null

397 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

401 .output stdout

402 EOF

403 }

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

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

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

407 fi

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

409 }

410 cleanup() {

411 trap '' INT HUP EXIT TERM PIPE

412 echo .quit >&5

413 kill $sq3pid

414 kill $sq3pid

415 rm -f $sqo $sqi

416 rm -rf $tmpfiles

417 cleanup2

418 }

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

420 # simple redirection for the sake of speed.

421 query() {

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

423 echo ".output $sqo" >&5

424 logstart

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

426 tee -a $querylog

427 else

428 echo "$@" >> $querylog

429 echo "$@"

430 fi >&5

431 echo ".output stdout" >&5

432 cat $sqo

433 rc=$?

434 logend

435 return $rc

436 }

437 _m4() {

438 #S4NAME=f,f,f

439 m4 ${S4NAME:+"-D_S4NAME_=${S4NAME}"} ${S4CSS:+-D_S4CSS_="$S4CSS"} "$@"

440 }

441 ismember() {

442 # $1=user, $2=group

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

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

445 }

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

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

448 }

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

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

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

452 }

453 isgrpowner() (

454 # $1=user, $2=group

455 gn=`sqlquote "$2"`

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

457 err isgrpowner: $sql

458 test -n "`query $sql`"

459 )

460 isgrpownerbygid() (

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

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

463 err isgrpownerbygid: $sql

464 test -n "`query $sql`"

465 )

466 getgroupadminmails() {

467 # $1=group

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

469 email4group "$1" "$i" ;

470 done

471 }

472 getgroupadmins() { # $1=group

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

474 qgrp=`sqlquote "$1"`

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

476 }

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

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

479 getvalbyid grp $2 \

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

481 }

482 getgroupbyid() {

483 # $1=id|gname

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

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

486 # err ggbyid: `echo $sql`

487 query $sql

488 }

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

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

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

492

493 # Return true when anonymous mode

494 [ "$anonymousmode" ] && return 0

495 # case `getvalbyid blog mode $2` in

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

497 # *closed*)

498 # owner=`getvalbyid blog owner $2`

499 # if isgrp $owner; then

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

501 # elif isuser $owner; then

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

503 # fi

504 # esac

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

506 sql="with getblog as (

507 select key,val from blog_s where id=(

508 select blogid from article where id in

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

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

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

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

513 isgrpadm as (select user from grp_adm where

514 gname=(select val from getowner) and

515 user='$1'),

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

517 select case

518 when (select author from article where

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

520 then 'author'

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

522 then 'open'

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

524 then CASE

525 WHEN (SELECT val FROM isgrp) IS NULL

526 THEN

527 CASE WHEN (SELECT val from getowner)

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

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

530 ELSE ''

531 END

532 WHEN (select user from isgrpadm) IS NOT NULL

533 THEN 'i-am-admin'

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

535 END

536 when (select val from getmode) is null

537 then 'open'

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

539 then (SELECT user FROM isgrpadm)

540 when (select author from article where

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

542 then 'user+author'

543 else '' end;"

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

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

546 # result=`query "$sql"`

547 # err FileAccessibility=$result

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

549 }

550 linkhome() {

551 # $1=UserOrGroup

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

553 if isuser $1; then

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

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

556 else

557 echo -n "grp+$1"

558 fi

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

560 }

561 hreflink() {

562 # s4 specific notation:

563 # ^href=URL

564 # ^iframe=URL

565 # ^video=URL

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

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

568 # OSM umap Wikistyle Notation:

569 # [[URL]] - Simle Link

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

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

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

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

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

575 # Other Style

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

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

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

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

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

581 # SPC+SPC+$ - <br>

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

583 checkboxON="${cb} checked>"

584 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

609

610 }

611 minitbl() {

612 sed -n '

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

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

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

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

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

618 H; # Concat this line to HoldSpace

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

620 $ b cont

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

622 }

623 :cont

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

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

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

627 # . of ^. is workaround for FreeBSD sed

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

629 p; # Print whole "table" element

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

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

632 }

633 x; # Back to the newest line

634 p; # Print rest' | miniul

635 }

636 miniul() {

637 sed -e '

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

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

640 :top

641 s/\n//;

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

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

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

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

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

647 $ b cont

648 N; # 次の行を読む

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

650 /^ /b top

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

652 p; s/.*//;

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

654 /^\* /b top

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

656 }

657

658 :cont

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

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

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

662 H; x

663 }

664 x'

665 }

666 acclog() (

667 # $1=table, $2=rowid

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

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

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

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

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

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

674 fi

675 )

676 gecos() (

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

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

679 )

680 setpar() {

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

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

683 case "$1" in

684 user) _user="$v" ;;

685 skey) _skey="$v" ;;

686 esac

687 fi

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

689 }

690 replpar() {

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

692 }

693 getpar() {

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

695 val=""

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

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

698 user) val=$_user ;;

699 skey) val=$_skey ;;

700 esac

701 fi

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

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

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

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

706 fi

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

708 case "$var" in

709 owner)

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

711 echo $user; return

712 elif ismember $user $val; then

713 echo $val; return

714 fi ;;

715 esac

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

717 echo "$val"

718 }

719 setskey() {

720 # For quick response...(?)

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

722 }

723 chkskey() {

724 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

730 return 0

731 fi

732 return 1

733 }

734 resetskey() {

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

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

737 fi

738 }

739 getpartype() {

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

741 }

742 getparcount() {

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

744 }

745 getparfilename() {

746 # null if type of $1 is not file

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

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

749 }

750 sqlquote() {

751 (v="$1"

752 case "$v" in

753 "") return ;; # null

754 "X'"*) # quoted hex string

755 echo $1 ;;

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

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

758 echo "\"$v\""

759 return ;;

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

761 echo "\"$v\""

762 return ;;

763 *)

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

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

766 else

767 echo "\"$v\""

768 fi ;;

769 esac)

770 }

771 sqlquotestr() (

772 case "$1" in

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

774 echo "'$v'" ;;

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

776 esac

777 )

778 mktempd() {

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

780 }

781 getcachedir() { # $1=maintable

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

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

784 else

785 echo $tmpd/$thumbxy

786 fi

787 }

788 getval() {

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

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

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

792 echo "$user" ;;

793 stamp|datetime)

794 date "+%F %T" ;;

795 serial)

796 (s=`getpar $2`

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

798 *)

799 getpar "$2" "$3";;

800 esac

801 }

802

803 getvalquote() {

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

805 (v=`getval "$@"`

806 case "$v" in

807 "") echo NULL ;;

808 *) sqlquote "$v" ;;

809 esac)

810 }

811 getparquote() {

812 sqlquote "`getpar $1`"

813 }

814 getbinbyid() {

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

816

817 }

818 getvalbyid() {

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

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

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

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

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

824

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

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

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

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

829 return

830 fi

831 done

832 rowid=$3

833 pk=`gettblpkey $1`

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

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

836 td=${4:-$tmpd}

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

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

839 for kt in s m; do

840 t=${1}_$kt

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

842 vcount=1 # count(val)

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

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

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

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

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

848 if [ $kt = m ]; then

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

850 # Reset val to store filenames if type is string

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

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

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

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

855 i=0

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

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

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

859 i=$((i+1))

860 fn=$c.$i

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

862 case $type in

863 file:*)

864 #file=$td/$val

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

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

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

868 # FOR SPEED: Skip file generation if imgcache exists

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

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

871 && continue

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

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

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

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

876 select rowid from $cond $slice;

877 .output '$td/$fn'

878 select val from $cond $slice;

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

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

881 .output stdout

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

883 EOF

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

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

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

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

888 ls -lh "$file" |

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

890 case "$type" in

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

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

893 *|*:[Aa]pplication*)

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

895 png:- > "$file"

896 ;;

897 esac

898 ;;

899 *)

900 sq $db<<EOF

901 .output $td/$fn.rowid

902 select rowid from $cond $slice;

903 .output $td/$fn

904 select val from $cond $slice;

905 EOF

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

907 ;;

908 esac

909 done

910 else

911 rm -f $td/$c.count

912 case $type in

913 file:*)

914 echo "$val" \

915 | while read fn; do

916 file=$td/$fn

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

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

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

920 | unhexize > "$file"

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

922 case $type in

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

924 *:[Aa]pplication*)

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

926 png:- > $file ;;

927 esac

928 fi

929 done

930 ;;

931 esac

932 fi

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

934 return

935 fi

936 done

937 done)

938 }

939 getvalbypkey() (

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

941 pk=`gettblpkey $1`

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

943 getvalbyid "$1" "$2" $rowid $4

944 )

945 getvalbycond() {

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

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

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

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

950 getvalbyid "$1" "$2" $rowid "$4"

951 fi

952 }

953 getpwfield() {

954 # getpwfield user column

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

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

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

958 echo "$val"

959 return 0

960 else

961 return 1

962 fi

963 }

964 numericalize() {

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

966 }

967 encode() {

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

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

970 sha1=sha1

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

972 sha1=sha1sum

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

974 sha1=gsha1sum

975 fi

976 fi

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

978 }

979 enjpeg() {

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

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

982 cjpeg="cjpeg"

983 else

984 cjpeg="convert - jpeg:-"

985 fi

986 fi

987 $cjpeg "$@"

988 }

989 mycrypt() (

990 key=$1 salt=$2

991 # err \$2=$2

992 case $2 in

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

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

995 esac

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

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

998 )

999 hexize() {

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

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

1002 hexize="xxd -p"

1003 else

1004 hexize_hd() {

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

1006 }

1007 hexize="hexize_hd"

1008 fi

1009 fi

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

1011 }

1012 unhexize() {

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

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

1015 unhex="xxd -p -r"

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

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

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

1019 EOF

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

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

1022 # world writable...:)

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

1024 fi

1025 fi

1026 cat "$@" | $unhex

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

1028 }

1029 percenthex() {

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

1031 }

1032 htmlescape() {

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

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

1035 }

1036 enascii() {

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

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

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

1040 else

1041 enascii_now=`date +%FT%T`

1042 enascii_sed() {

1043 nkf -Z0Z1Z2 \

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

1045 }

1046 enascii="enascii_sed"

1047 fi

1048 fi

1049 cat "$@" | $enascii

1050 }

1051 size_h() {

1052 i="$1" oi=$1

1053 set -- B B KB MB GB TB

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

1055 oi=$i

1056 i=$((i/1024))

1057 shift

1058 done

1059 echo ${oi}$1

1060 }

1061 gettblconf() {

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

1063 ## tconfs=`sq $db \

1064 tconfs=`query \

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

1066 fi

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

1068 }

1069 gettblkeys() {

1070 # $1=tbl

1071 gettblconf

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

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

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

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

1076 case $type in

1077 *p*)

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

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

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

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

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

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

1084 esac

1085 case $type in

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

1087 esac

1088 done

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

1090 }

1091 gettblpkey() {

1092 # $1=tbl

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

1094 }

1095 gettblfkey() {

1096 (x=`gettblkeys $1`

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

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

1099 }

1100 gettblcols() {

1101 (x=`gettblkeys $1`

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

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

1104 }

1105 gettbl_s_cols() {

1106 (x=`gettblkeys $1`

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

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

1109 }

1110 gettbl_m_cols() {

1111 (x=`gettblkeys $1`

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

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

1114 }

1115 gettbl_h_cols() {

1116 (x=`gettblkeys $1`

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

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

1119 }

1120 gettbl_coltype() (

1121 gettblconf

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

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

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

1125 )

1126 is_hidden() {

1127 # $1=Tbl $2=col

1128 gettblconf

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

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

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

1132 case $x in

1133 *h*) return 0 ;;

1134 *) return 1 ;;

1135 esac

1136 }

1137

1138 dbsetbyid() {

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

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

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

1142 quotedp=$(sqlquotestr "$p")

1143 unset primary update

1144 gettblconf

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

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

1147 #err conf=$conf

1148 case ${conf#*=} in

1149 p*) primary=1 ;;

1150 f*) update=1 ;;

1151 u*) ;;

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

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

1154 esac

1155 #err t=$t

1156 type=string fn=""

1157 case $conf in

1158 */password)

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

1160 ;;

1161 */image*|*/document*)

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

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

1164 ;;

1165 esac

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

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

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

1169 if [ "$primary" ]; then

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

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

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

1173 elif [ "$update" ]; then

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

1175 else

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

1177 fi

1178 )

1179 }

1180 expire() (

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

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

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

1184 )

1185 addsession() {

1186 # expireをセット

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

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

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

1190 # Remove old session parameters

1191 now=`expire now`

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

1193 }

1194 gencookie() (

1195 path=${URL#*:/}

1196 path=${URL%/*}

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

1198 for kv; do

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

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

1201 done

1202 )

1203 contenttype() {

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

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

1206 }

1207 putheader() {

1208

1209 }

1210 putfooter() {

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

1212 }

1213 getcookie() {

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

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

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

1217 case "$k" in

1218 user) _user="$v" ;;

1219 $skey) _skey="$v" ;;

1220 esac

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

1222 done

1223 }

1224 genrandom() {

1225 # $1=columns (default: 10)

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

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

1228 }

1229 genserial() {

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

1231 }

1232 smail() {

1233 # smail rcpts subj (file)

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

1235 # $MAIL_FROM <- From: header value

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

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

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

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

1240 sender=${SENDER:-$admin}

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

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

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

1244 }

1245 smail_queue_flush() {

1246 # $1=timelimit

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

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

1249 SELECT rowid FROM smailq

1250 GROUP BY rcpts, subj

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

1252 EOF

1253 )

1254 for rid in $rowids; do

1255 sq $workdb \

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

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

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

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

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

1261

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

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

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

1265 .separator "\n" "------------------\n\n"

1266 SELECT time, text FROM smailq

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

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

1269 ORDER by time;

1270 EOF

1271 then

1272 echo rowid=$rid

1273 cat <<-EOF | sq $workdb

1274 DELETE FROM smailq

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

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

1277 EOF

1278 fi

1279 fi

1280 done

1281 }

1282 smail_queue() {

1283 # $1=Rcpts, $@=subj

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

1285 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1294 EOF

1295 )

1296 ## XXX: Adhoc load mitigation

1297 case "$now" in

1298 *[01])

1299 err flush_queue=$mintime

1300 smail_queue_flush ;;

1301 esac

1302 fi

1303 }

1304 setviastring() {

1305 table=par

1306 oifs="$IFS"

1307 IFS="&"

1308 for us in $1; do

1309 k=${us%%=*}

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

1311 setpar "$k" "string" "$v"

1312 done

1313 IFS="$oifs"

1314 }

1315 checkdomain() (

1316 # Check the validity of domain by referring DNS

1317 item=$1

1318 err checkdomain $1

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

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

1321 )

1322 pwcheck() {

1323 # $1=passwd

1324 dbpswd=`getpwfield $user pswd`

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

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

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

1328 }

1329 mypwhash() {

1330 mycrypt "`cat`" `genrandom 5`

1331 }

1332 wasureta() {

1333 user=$1

1334 if ! checkdomain $user; then

1335 contenttype; echo

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

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

1338 putfooter

1339 exit 0

1340 fi

1341 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1343 encpswd=`echo $newpswd|mypwhash`

1344 dbsetbyid user $user pswd "$encpswd"

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

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

1347 $msgdir/mail-newaccount.m4 \

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

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

1350 }

1351 checkauth() {

1352 user=`getpar user`

1353 skc=`getpar skey` # from cookie

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

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

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

1357 if chkskey "$skc"; then

1358 return 0

1359 fi

1360 fi

1361 pswd=`getpar pswd`

1362 quser=`sqlquotestr "$user"`

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

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

1365 return 4 # 4=server too heavy

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

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

1368 return 2 # 2=login fail

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

1370 wasureta "$user"

1371 return 1 # wasureta error

1372 fi

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

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

1375 if pwcheck "$pswd"; then

1376 newsession=`genrandom 34`

1377 if setskey "$user" "$newsession" &&

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

1379 err gencookie "user=$user" "$skey=$newsession"

1380 gencookie "user=$user" "$skey=$newsession"

1381 return 0

1382 else

1383 return 4 # Heavy load??

1384 fi

1385 fi

1386 err "Login failed: [$user]"

1387 return 2 # Password mismatch

1388 }

1389 showlogin() {

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

1391 test -z "$args" && resetskey

1392 s4name=${S4NAME:-s4}

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

1394 cat $layout/login.m4.html

1395 echo '</body></html>'

1396 ) | _m4 \

1397 -D_BODYCLASS_="login" \

1398 -D_TITLE_="$s4name" \

1399 -D_SYSNAME_="Welcome to $s4name" \

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

1401 exit 0

1402 }

1403 dologin() {

1404 checkauth

1405 st=$?

1406 if [ $st != 0 ]; then

1407 contenttype; echo

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

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

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

1411 showlogin # and EXIT

1412 fi

1413 }

1414

1415 # Do instant jobs here

1416 dbsetup

1417 trap cleanup INT HUP EXIT TERM PIPE

1418 # trap cleanup INT HUP

1419

1420

1421 cgiinit() {

1422 tmpd=`tmpd=$tmpdir mktempd`

1423 tmpf=$tmpd/stream.$$

1424 tmpfiles=$tmpfiles" $tmpd"

1425 addsession $session

1426 getcookie

1427 case "$REQUEST_METHOD" in

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

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

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

1431 # cat > $tmpf # too much?

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

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

1434 s="`cat $tmpf`"

1435 ;;

1436 esac

1437 case "$CONTENT_TYPE" in

1438 *boundary*)

1439 bndry=${CONTENT_TYPE#*boundary=}

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

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

1442 do

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

1444 #echo u=$us

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

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

1447 # err k=$k v=$v

1448 case "$k" in

1449 *:filename)

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

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

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

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

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

1455 :

1456 fi

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

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

1459 case "$mimetype" in

1460 [Ii]mage/x-xcf)

1461 bzip2 "$tmpd/$v"

1462 v=${v}.bz2

1463 ;;

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

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

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

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

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

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

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

1471 v=$vjpg

1472 else

1473 mimetype="Not supported"

1474 fi

1475 ;;

1476 [Ii]mage/*)

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

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

1479 ;;

1480 esac

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

1482 then

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

1484 continue

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

1486 replpar text string \

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

1488 continue

1489 fi

1490 ;;

1491 *)

1492 type='string'

1493 ;;

1494 esac

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

1496 setpar "$k" "$type" "$v"

1497 done

1498 ;;

1499 *)

1500 setviastring "$s"

1501 ;;

1502 esac

1503 }

1504 email4group() {

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

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

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

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

1509 sql="WITH

1510 grpemails AS (

1511 SELECT gname, user, val email

1512 FROM grp_mem NATURAL JOIN grp_mem_s

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

1514 useremails AS (

1515 SELECT user.name, val email

1516 FROM user

1517 LEFT JOIN user_m

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

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

1520 FROM useremails u LEFT JOIN grpemails g

1521 ON u.name=g.user

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

1523 query "$sql"

1524 }

1525 email4groupbyuid() {

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

1527 g=$1; shift

1528 uids=`echo "$@"`

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

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

1531 email4group "$g" `query "$sql"`

1532 }

1533 myemail4group() {

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

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

1536 }

1537 collectmembersbyid() {

1538 # Collect user names of group specified by grid

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

1540 query "SELECT user FROM grp_mem \

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

1542 }

1543 collectmembersbyid() {

1544 # Collect user names of group name

1545 qgrp=`sqlquote "$1"`

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

1547 }

1548 collectgecosesbyid() {

1549 # Collect user gecoses of group

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

1551 query<<-EOF

1552 SELECT gecos

1553 FROM gecoses

1554 WHERE name IN (SELECT user FROM grp_mem

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

1556 EOF

1557 }

1558 collectemail() (

1559 # Collect email addresses for group $1

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

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

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

1563 for e; do

1564 if isuser "$e"; then

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

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

1567 else

1568 qgrp=`sqlquote "$e"`

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

1570 gmem="grp_mem"

1571 else

1572 tm=`sqlquote "$TEAM"`

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

1574 fi

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

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

1577 $gmem g left join grp_mem_s s

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

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

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

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

1582 query "$sql"

1583 fi

1584 done

1585 )

1586 sendinvitation() (

1587 # $1=email

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

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

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

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

1592 gecos=`gecos`

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

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

1595 _m4 -D_URL_="$urlbase" \

1596 -D_USER_="$name" \

1597 -D_EMAIL_="$1" \

1598 -D_REGIST_="$regist" \

1599 -D_ADMIN_="$admin" \

1600 $msgdir/mail-invite.m4 \

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

1602 return 0

1603 )

1604 emaildomaincheck() {

1605 case "$1" in

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

1607 *@*)

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

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

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

1611 return 2

1612 fi

1613 return 0

1614 ;;

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

1616 esac

1617 }

1618 invite() {

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

1620 case "$email" in

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

1622 *@*)

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

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

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

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

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

1628 elif sendinvitation $email; then

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

1630 else # Cannot be reached here

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

1632 fi ;;

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

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

1635 esac

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

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

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

1639 fi

1640 if [ -f $invite_policy ]; then

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

1642 else

1643 pol="$invite_policy"

1644 fi

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

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

1647 -D_INVITE_POLICY_="$pol" \

1648 $layout/html.m4.html $layout/invite.m4.html

1649 }

1650 regist() {

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

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

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

1654 echo "bye bye" | html p

1655 reutrn

1656 fi

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

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

1659 cat<<EOF

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

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

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

1663 EOF

1664 return

1665 fi

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

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

1668 # Fake login password to wasureta

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

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

1671 wasureta $email

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

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

1674 addsession $1 # for removal after 1 minute

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

1676 $layout/login.m4.html

1677 return

1678 }

1679 group_safename() {

1680 # Convert $1 to safe group name

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

1682 }

1683 groupupdate() {

1684 gname=`getpar gname`

1685 qgname=`sqlquote "$gname"`

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

1687 # See ALSO same job in showgroup()

1688 newgname=`group_safename "$gname"`

1689 err newgname=$newgname

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

1691 err NewGNAME: gname=$newgname

1692 gname=$newgname

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

1694 replpar gname string "$gname"

1695 fi

1696 # Name confliction check

1697 parow=`getpar rowid`

1698 ## err parow=$parow

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

1700 query "BEGIN EXCLUSIVE;"

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

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

1703 if [ $count -gt 0 ]; then

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

1705 query "END;"

1706 return

1707 fi

1708 par2table $formdir/grp.def

1709 query "END TRANSACTION;"

1710 # Remove orphan

1711 : <<EOF

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

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

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

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

1716 EOF

1717 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1721 sql="delete from blog where id in

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

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

1724 query "$sql";

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

1726 return # and return

1727 fi

1728 fi

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

1730 fi

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

1732 grid=$(query $sql)

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

1734 grp $grid

1735 }

1736 groupclone() {

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

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

1739 case "$1" in

1740 */noteam)

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

1742 esac

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

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

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

1746 return

1747 fi

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

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

1750 return

1751 fi

1752 i=0

1753 while true; do

1754 copy="-copy$i"

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

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

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

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

1759 i=$((i++))

1760 continue

1761 fi

1762 break

1763 done

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

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

1766 query<<-EOF

1767 BEGIN;

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

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

1770 SELECT $newqname, key, val

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

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

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

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

1775 -- Copy members and their configuration --

1776 REPLACE INTO grp_mem SELECT $newqname, user

1777 FROM grp_mem WHERE gname=$qgrp;

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

1779 FROM grp_mem_s WHERE gname=$qgrp;

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

1781 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1782 -- Copy administrators --

1783 REPLACE INTO grp_adm SELECT $newqname, user

1784 FROM grp_adm WHERE gname=$qgrp;

1785 COMMIT;

1786 EOF

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

1788 STOPCLONEMSG=1 groupconf "$newrowid"

1789 }

1790 groupman() {

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

1792

1793 GF_STAGE="grpconf"

1794 GF_STAGE=groupupdate

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

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

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

1798 -D_DUMPTABLE_="syscmd(cat)" \

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

1800 }

1801 userconf() {

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

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

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

1805 }

1806 groupconf() {

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

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

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

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

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

1812

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

1814 grp=`getgroupbyid $rowid`

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

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

1817 return

1818 fi

1819

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

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

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

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

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

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

1826 <div>

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

1828 <table>

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

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

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

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

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

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

1835 </table>

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

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

1838 </div>

1839 EOF

1840 fi

1841 }

1842 mems() {

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

1844 kwd=`getpar kwd`

1845 listmember $kwd

1846 }

1847 grps() {

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

1849 kwd=`getpar kwd`

1850 listgroup $kwd \

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

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

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

1854 $layout/form+dump.m4.html

1855 }

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

1857 gpg=`getpar grp`

1858 grid=${1:-$gpg}

1859 grp=`getgroupbyid "$grid"`

1860 ## . ./s4-blog.sh

1861 jg=`getpar joingrp`

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

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

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

1865 fi

1866 htmlheader=$layout/html.m4.html

1867 showgroup "$grid"

1868 }

1869 sql4interestblogs() {

1870 cat<<EOF

1871 CREATE TEMPORARY VIEW interestblogs AS

1872 SELECT blog.rowid rid, id, author

1873 FROM blog

1874 NATURAL JOIN

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

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

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

1878 WHEN (SELECT user FROM grp_mem

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

1880 THEN 0

1881 ELSE 1

1882 END;

1883 EOF

1884 }

1885 listnewblogsql() { # $1=user

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

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

1888 newdays=${newdays:-14}

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

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

1891 WHERE user='$user'

1892 AND tblrowid IN

1893 ($blogreadflagrowid,

1894 $blogcutoffflagrowid)),

1895 $basetime -- "0"

1896 );"`

1897 cat<<EOF

1898 `sql4interestblogs`

1899 WITH article_ctime as (

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

1901 FROM article join article_s s using(id)

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

1903 GROUP BY id

1904 ), blog_title_owner as (

1905 SELECT blg.rid brid, id,

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

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

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

1909 ), blogall as (

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

1911 ), news as (

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

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

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

1915 FROM blogall bl

1916 LEFT JOIN

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

1918 ON bl.brid=al.tblrowid

1919 WHERE atime < bl.ctime

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

1921 LIMIT 10

1922 ) SELECT brid LINK, "新着",

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

1924 ctime, title,

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

1926 FROM news;

1927 EOF

1928 }

1929

1930 search_form() {

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

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

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

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

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

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

1937 @week → 最近一週間

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

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

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

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

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

1943 auth=""

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

1945 case "$1" in

1946 author=*)

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

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

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

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

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

1952 ;;

1953 grp=*)

1954 a=`echo "${1#grp=}"|htmlescape`

1955 g=`gecos ${1#grp=}`

1956 auth="<input type=\"hidden\" name=\"owner\" value=\"$a\">"

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

1958 ;;

1959 esac

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

1961 cat<<-EOF

1962 <div class="right">

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

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

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

1966

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

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

1969

1970 </form>

1971 </div>

1972 EOF

1973 }

1974

1975 imgsrc_cache() (

1976 # $1 = directory for cache'ing

1977 # $2 = table (user_m or grp_m)

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

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

1980 dir="$1" tbl="$2"

1981 keycond="$3"

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

1983 ORDER BY rowid DESC LIMIT 1"

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

1985 tmpf=$tmpd/imgsrc_cache.$$

1986 case "$4" in

1987 [Ss]) size=S ;;

1988 [Oo]) size=O ;;

1989 *) size=M ;;

1990 esac

1991 # ImageCache filename storing schema:

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

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

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

1995 valtype=`query "$sql0"`

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

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

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

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

2000 case "$filetype" in

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

2002 esac

2003 fi

2004 cacheimg_S=$dir/S_$filename

2005 cacheimg_M=$dir/M_$filename

2006 cacheimg_O=$dir/$filename

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

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

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

2010 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2011 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2012 # if cache is fresh and has the same checksum,

2013 echo "<img src=\"$cacheimg\">"

2014 else

2015 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2016 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2017 fifo2=$fifo.2

2018 mkfifo $fifo $fifo2

2019 fmt=${filename##*.}

2020 ## [[ NOTE ]]

2021 ## a. convert oldimage newimage

2022 ## b. convert oldimage fmt:- | convert - newimage

2023 ## b is much smaller than a

2024 cat $tmpf | unhexize \

2025 | tee $fifo \

2026 | convert -define ${fmt}:size=${iconxy_M} \

2027 -resize ${iconxy_M}'>' - ${fmt}:- \

2028 | tee $fifo2 \

2029 | convert - "$cacheimg_M" &

2030 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2031 -resize ${iconxy_S}'>' - ${fmt}:- \

2032 | convert - "$cacheimg_S" &

2033 printf '%s' "<img src=\"data:${filetype},"

2034 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2035 echo '">'

2036 echo "$sum" > $sumfile

2037 fi

2038 ## Now preparing cache image, done.

2039 ## Store this information to DB

2040 stbl=${tbl%_m}_s # user_s or grp_s

2041 pkey=${keycond%%=*} # Primary Key name

2042 pval=${keycond#*=} # Primary Key value

2043 query <<-EOF

2044 REPLACE INTO $stbl($pkey, key, type, val)

2045 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2046 EOF

2047 )

2048

2049 showhome() {

2050 # $1=userRowIdToShow

2051 err showhome \$1=$1

2052 case "$1" in

2053 *@*) uname=`getvalbypkey user name "$1"` ;;

2054 *) uname=`getvalbyid user name $1` ;;

2055 esac

2056 ## err ShowHome: uname=$uname

2057 td=`getcachedir home/"$1"`

2058 gecos=`gecos "$uname"`

2059 ## err SH:gecos=$gecos

2060 GF_VIEWONLY=1

2061 cond="gname in (select gname from grp_mem where user='$uname')"

2062 search_form_args=""

2063 if [ x"$user" = x"$uname" ]; then

2064 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2065 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> /

2066 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2067 # Display folders

2068 sql="select count(id) from article_m where id

2069 in (select id from article where author='$user')

2070 and type like 'file:%';"

2071 ## err nfile-sql=`echo "$sql"`

2072 nfile=`query "$sql"`

2073 # err nfile=$nfile

2074 if [ $nfile -gt 0 ]; then

2075 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2076 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2077 fi

2078 else

2079 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2080 GROUP BY user;"`

2081 usermenu="<p>Last seen on $latestlog</p>"

2082 search_form_args="author=$uname"

2083 fi

2084 . ./s4-blog.sh

2085

2086 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2087 search_form "$search_form_args" > $sf

2088 printf "%s さん" "$gecos" > $tf

2089 { echo "<div class=\"noprofimg\">"

2090 viewtable $formdir/user.def user $1

2091 echo "</div>"

2092 } > $pf

2093

2094 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2095 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2096 imf=$tmpd/profimg.$$; touch $imf

2097 if [ -n "$img" ]; then

2098 if true; then

2099 tbl=user_m

2100 enticond="name='$uname'"

2101 imgsrc_cache "$td/main" user_m "$enticond" M

2102 else

2103 { printf '%s' "<IMG src=\"data:${img#file:},"

2104 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2105 | sed 's/$..$/%\1/g'

2106 echo '">'

2107 }

2108 fi > $imf

2109 fi

2110 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2111 val='$uname';"`

2112 ## REMOVE This comment block until 2019/7/1

2113 ## err "----- `gdate +%FT%T.%3N` ------------C"

2114 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2115 listblog $uname > $bf

2116 ## unset ddd

2117 ## err "----- `gdate +%FT%T.%3N` ------------D"

2118

2119 hometail=$tmpd/tail.$$

2120 mkfifo $hometail

2121

2122 #Calling listgroupbytable, originally here

2123

2124 (

2125 # Display Most Recent Entry

2126 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2127 shortval=${shortval:-val}

2128

2129 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2130 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2131 ctime,

2132 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2133 (SELECT gecos FROM gecoses

2134 WHERE name=(SELECT val FROM blog_s

2135 WHERE key='owner' AND id=b.id)) owner,

2136 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2137 FROM blog b

2138 JOIN

2139 (SELECT distinct blogid, a.id aid, max(val) ctime

2140 FROM article a, article_s s

2141 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2142 GROUP BY blogid ORDER BY val DESC LIMIT 50

2143 ) m

2144 ON b.id=m.blogid;"

2145 # This should be as follows

2146 : <<EOF

2147 WITH arts AS(

2148 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2149 a.blogid, a.id id, s.val ctime

2150 FROM article a NATURAL JOIN article_s s

2151 WHERE s.key = 'ctime' AND a.author='$user'

2152 GROUP by s.id

2153 )

2154 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2155 FROM arts a0

2156 JOIN

2157 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2158 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2159 ORDER BY ctime DESC LIMIT 50;

2160 EOF

2161

2162 cat<<-EOF

2163 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2164 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2165 <div class="lcto">

2166 `DT_VIEW=replyblog dumptable html blog`

2167 </div>

2168 EOF

2169 unset DT_SQL

2170 if [ x"$user" = x"$uname" ]; then

2171 # Display NEWS

2172 # 2016-06-26

2173 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2174 acclog blog $blogreadflagrowid

2175 # echo "全部既読にしました" | html p

2176 fi

2177 # 2016-02-19 Counting NEWS without using dumptable.

2178 sql=`listnewblogsql "$user"`

2179 # echo "$sql" > tmp/listnew

2180 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2181 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2182 cont=$((cont-1))

2183 err newcount=$cont

2184 if [ $cont -gt 0 ]; then

2185 #echo "全体の新着記事${cont}傑" | html h2

2186 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2187 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2188 cat<<-EOF | html form 'action="?home"'

2189 `cgi_checkbox readchk yes 'id="read"'`<label

2190 for="read">新着ふくめて全部読んだことにする</label>

2191 `cgi_submit '確定'`

2192 `cgi_hidden read yes`

2193 EOF

2194 echo "$new10 "

2195 echo "</div>"

2196 else # If news is 0, set log cut off flag

2197 acclog blog $blogcutoffflagrowid # for speed

2198 fi

2199 else # Not My Home ($user != $uname)

2200 : # DT_SQL=

2201 fi

2202 ) > $hometail & # Is background call safe to m4??

2203 #

2204 listgroupbytable $formdir/grp.def $cond |

2205 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2206 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2207 -D_PROFIMG_="spaste(\`$imf')" \

2208 -D_BLOGS_="spaste(\`$bf')" \

2209 -D_SEARCH_="spaste(\`$sf')" \

2210 -D_NBLOG_="$nblog" \

2211 -D_GROUPS_="syscmd(\`cat')" \

2212 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2213 $layout/html.m4.html $layout/home.m4.html

2214

2215 # Record access log

2216 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2217 }

2218 commission() { # $1=grp-rowid $2=user-rowid

2219 contenttype; echo

2220 ## err commission: "$@"

2221 gname=`getgroupbyid $1`

2222 echo "グループ $gname 管理者委任" \

2223 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2224 if [ -n "$2" ]; then

2225 grp_reg_adm "$@"

2226 else

2227 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2228 fi

2229 }

2230 listgroupbytable() {

2231 # $1=deffile $2...=condition

2232 tagline=`grep :tag: $1`; shift

2233 and="${1:+and }" where=${1:+where }

2234 href="<a href=\"$myname?grp+"

2235 echo '<div class="listgroup">'

2236 NGsql="select distinct tag from\

2237 (select gname, max(case key when 'tag' then val end) as tag, \

2238 max(case key when 'ctime' then val end) as ctime\

2239 from grp_s group by gname order by ctime);"

2240 sql="select val from grp_s where key='tag' $and$* group by val;"

2241 ## err ListGRP: query sql="$sql"

2242 for tag in `query "$sql"`

2243 do

2244 ## err ListGrp: tag=$tag

2245 tn=${tagline%%=${tag}*}

2246 tn=${tn##*[ :]}

2247 sql="select rowid||':'||gname as 'グループ名',説明 from

2248 (select (select rowid from grp g where g.gname=grp_s.gname)

2249 as rowid,

2250 gname,

2251 max(case key when 'gecos' then val end) as '説明',

2252 max(case key when 'tag' then val end) as 'tag',

2253 max(case key when 'mtime' then val end) as mtime from grp_s

2254 $where$* group by gname having tag='$tag' order by mtime desc);"

2255 ## err PersonalGroupList= `echo $sql`

2256 echo "<h2>$tn</h2>"

2257 echo '<table class="b listgroup">'

2258 sq -header -html $db "$sql" \

2259 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2260 echo '</table>'

2261 done

2262 echo '</div>'

2263 }

2264 iconhref() (

2265 # $1=icon-file, $2=Href $3=title $4...=anchor

2266 data=`percenthex "$1"`

2267 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2268 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2269 href=$2; title=$3; shift 3

2270 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2271 )

2272 iconhref2() (

2273 # $1=icon-file, $2=Href $3=title $4...=anchor

2274 src=$1

2275 href=$2; title=$3; shift 3

2276 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2277 )

2278 listentry() (

2279 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2280 # Referring variable $iamowner=$grp to attach owner-request links

2281 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2282 cond='' hiddens=''

2283 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2284 if [ -z "$offset" ]; then

2285 offset=`getpar start`; offset=${offset%%[!0-9]*}

2286 offset=$((offset-1))

2287 fi

2288 offset=$((offset + 0)) # change to numeric forcibly

2289 [ $offset -lt 0 ] && offset=0

2290 limit=$listentlimit

2291 dir=`getcachedir "$1"`

2292 if [ x"$1" = x"user" ]; then

2293 hrb="$myname?home"

2294 deficon=person-default.png

2295 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2296 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2297 gcs=gecos

2298 else # if group

2299 hrb="$myname?grp"

2300 deficon=group-default.png

2301 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2302 gcs=name

2303 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2304 if [ -n "$tagline" ]; then

2305 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2306 ## err tagconv=$tagconv

2307 fi

2308 fi

2309 if [ ! -d $dir ]; then

2310 mkdir -p $dir

2311 fi

2312 if [ ! -s $dir/$deficon ]; then

2313 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2314 fi

2315 if [ -n "$2" ]; then

2316 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2317 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2318 fi

2319 tag=`getpar tag` tag2=`getpar tag2`

2320 if [ x"$tag" = x"NULL" ]; then

2321 tag="" tag2=""

2322 fi

2323 if [ -n "$tag$tag2" ]; then

2324 tag=${tag:-$tag2}

2325 qtag=`sqlquote "$tag"`

2326 cond2="tag=$qtag"

2327 fi

2328 if [ -n "$cond1$cond2" ]; then

2329 cond="$cond1${cond2:+ AND $cond2}"

2330 cond="WHERE ${cond# AND }"

2331 fi

2332

2333 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2334 # grpは呼出し元の動的スコープ変数でよくないな...

2335 ##qgrp=`sqlquote $grp`

2336 getgrp="(select gname from grp where rowid=${rowid:--1})"

2337 sql="select a.rowid, a.$link,

2338 coalesce(b.$gcs, a.$nm) as nick,

2339 quote(a.$nm) as qname,

2340 (SELECT val FROM ${tbl}_s

2341 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2342 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2343 || case when a.$nm in (select gname from grp)

2344 then printf('(%d名)',

2345 (select count(user) from grp_mem where gname=a.$nm))

2346 else ' <'||a.$nm||'>'

2347 end

2348 as name,

2349 b.tag,

2350 case when a.$nm in (select user from grp_adm

2351 where gname=$getgrp) then '管理者'

2352 when '$user' in (select user from grp_adm where gname=a.$nm)

2353 then 'ADMIN'

2354 when '$user' in (select user from grp_mem where gname=a.$nm)

2355 then 'Member'

2356 when '$iamowner' = '' then ''

2357 else ',not='||a.rowid end as ownerlink,

2358 CASE '$entity'

2359 WHEN 'グループ'

2360 THEN coalesce(

2361 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2362 'open')

2363 ||

2364 CASE WHEN '$user'

2365 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2366 THEN ' ismember'

2367 ELSE ''

2368 END

2369 ELSE 'user'

2370 END regmode

2371 from $tbl a left join

2372 (select $nm as name,

2373 max(case key when 'gecos' then val end) as gecos,

2374 max(case key when 'tag' then val end) as tag,

2375 max(case key when 'mtime' then val end) as mtime,

2376 max(case key when 'wtime' then val end) as wtime,

2377 max(case key when 'login' then val end) as login

2378 from ${tbl}_s group by $nm)

2379 b on a.$nm=b.name $cond $3

2380 order by b.wtime desc, b.login desc,

2381 b.mtime desc, b.tag desc, a.rowid asc"

2382 # Give precedence to newer maintained groups (2016-09-24)

2383 # Note that mtime is stored only in grp_s.

2384 ## err LE:sql.1="$sql"

2385 total=`query "with x as ($sql) select count(*) from x;"`

2386 echo "${entity} 一覧" | html h2

2387 echo '<div class="listentry">' # List-entry div

2388 # Show owner/member filter button

2389 METHOD=GET

2390 hiddens="$hiddens

2391 `cgi_hidden kwd \"$kwd\"`

2392 `cgi_hidden stage \"$stage\"`"

2393 if [ x"$tbl" = x"grp" ]; then

2394 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2395 fh="<select name=\"tag\">$nl"

2396 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2397 for l in $args; do

2398 val=${l#*=} tname=${l%=*}

2399 if [ x"$val" = x"$tag" ]; then

2400 s=" selected"

2401 selectedtags="(種別[${tname}]のみ)"

2402 else

2403 s=""

2404 fi

2405 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2406 done

2407 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2408 cat<<-EOF

2409 <form action="$myname" method="$METHOD">

2410 </form>

2411 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2412 for="ismembtn">参加中以外隠す</label>

2413 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2414 for="isadmbtn">管理者参加以外隠す</label>

2415 EOF

2416 # limit=3

2417 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2418 fi

2419 if [ $total -gt $limit ]; then

2420 echo '<div>'

2421 METHOD=GET cgi_form $stage <<EOF

2422 $form

2423 <label>次の語を含む${entity}で検索:

2424 `cgi_text kwd "$kwd"`</label>

2425 $hiddens

2426 EOF

2427 echo '</div>'

2428 else

2429 echo $selectedtags | html p

2430 fi

2431 cat<<EOF

2432 <form action="$myname" method="$METHOD">

2433 <p>${total}件中の<input class="hidesub" type="text" name="start"

2434 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2435 <input type="submit" value="確定"></p>

2436 </form>

2437 EOF

2438 if [ $((offset+limit)) -lt $total ]; then

2439 nextbtn=$(

2440 cat<<EOF

2441 <div class="right clear"><form action="$myname" method="$METHOD">

2442 `cgi_submit 次の${limit}件`

2443 $hiddens

2444 `cgi_hidden offset $((offset + limit))`</form></div>

2445 EOF

2446 )

2447 fi

2448 if [ $offset -gt 0 ]; then

2449 prevbtn=$(

2450 cat<<EOF

2451 <form action="$myname" method="$METHOD">

2452 `cgi_submit 前の${limit}件`

2453 $hiddens

2454 `cgi_hidden offset $((offset - limit))`</form>

2455 EOF

2456 )

2457 fi

2458 pnbtn="$nextbtn$prevbtn"

2459 echo $pnbtn

2460

2461 ## err ListEntry: `echo "$sql"\;`

2462 # sq $db here??? 2016-11-28

2463 query "$sql limit $limit ${offset:+offset $offset};" \

2464 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2465 # err name=$name owner=$ownerp lnk=$lnk

2466 # err newlnk=$lnk regmode=$regmode

2467 icondir=$dir/$id

2468 # Pick up only last icon

2469 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2470 <p class=\"tag _$tag\">$tag</p>" \

2471 | _m4 $tagconv

2472 if [ -n "$NOSPEEDUP" ]; then

2473 files=`getvalbyid $tbl profimg $id $icondir`

2474 if [ -n "$files" ]; then

2475 icon=`echo "$files"|tail -1`

2476 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2477 else

2478 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2479 fi

2480 elif [ -n "$icon" -a -s "$icon" ]; then

2481 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2482 else

2483 cond="$nm=$qname"

2484 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2485 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2486 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2487 # err "img=[$img]"

2488 if [ -n "$img" ]; then

2489 echo "<a href=\"$hrb+$lnk\">"

2490 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2491 echo "</a>"

2492 else

2493 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2494 fi

2495 fi

2496 echo "<br>$name${ownerp:+<br>($ownerp)}"

2497 echo "</div>"

2498 done

2499 echo "</div>" # End of List-entry div

2500 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2501 )

2502 listmember() {

2503 listentry user "$@"

2504 }

2505 listgroup() {

2506 listentry group "$@"

2507 }

2508 hexteams() { # $1=gname, $2(optional)=user

2509 cond=${2:+" AND user='$2'"}

2510 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2511 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2512 }

2513 showgroup() { # $1=group-rowid

2514 if [ -z "$1" ]; then

2515 grid=`getpar grid`

2516 grid=${grid%%[!0-9]*}

2517 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2518 else

2519 grid=$1

2520 fi

2521 grp=`getgroupbyid $grid`

2522 qgrp=`sqlquote "$grp"`

2523 htmlgrp=`echo "$grp"|htmlescape`

2524 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2525 if isgroup "$grp"; then

2526 tf=$tmpd/title.$$

2527 sf=$tmpd/search.$$

2528 bodyclass=`query "SELECT val FROM grp_s

2529 WHERE gname=$qgrp AND key='regmode';"`

2530 if ismember "$user" "$grp"; then

2531 ismember="ismember"

2532 bodyclass="$bodyclass${bodyclass:+ }ismember"

2533 else

2534 ismember="" # bodyclass="group"

2535 fi

2536 bodyclass="$bodyclass grouphome"

2537 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2538 echo "グループ $htmlgrp" > $tf

2539

2540 showgroupsub $formdir/grp.def "$grid" | \

2541 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2542 -D_FORM_="syscmd(\`cat')" \

2543 -D_BODYCLASS_="$bodyclass" \

2544 -D_DUMPTABLE_="" \

2545 $htmlheader $sf $layout/form+dump.m4.html

2546 # $htmlheader $layout/form+dump.m4.html

2547 # $htmlheader is defined in grp()

2548 else # if $grp is removed at par2table

2549 listgroup

2550 fi

2551 }

2552 showgroupsub() {

2553 # $1=def-file $2=group-rowid

2554 # Using $ismember

2555 rowid=$2

2556 grp=`getgroupbyid $2`

2557 qgrp=`sqlquote "$grp"`

2558 td=`getcachedir grp/"$2"`

2559 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2560 if [ -z "$rowid" ]; then

2561 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2562 #grp=`sq $db "select gname from grp where rowid=$grp"`

2563 echo "showgroupsub: invalid argument($1 $2)" | html p

2564 return

2565 fi

2566 val=`getvalbyid grp profimg $rowid $tmpd`

2567 enticond="gname=$qgrp"

2568 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2569 if [ -n "$img" ]; then

2570 cat<<-EOF

2571 <p class="groupimg">

2572 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2573 EOF

2574 fi

2575 echo "<div class=\"noprofimg\">"

2576 viewtable $1 grp $rowid

2577 echo "</div>"

2578 if isgrpowner "$user" "$grp"; then

2579 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2580 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2581 iamowner=$rowid

2582 colmd=" mode"

2583 fi

2584 if [ -n "$ismember" ]; then

2585 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2586 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2587 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2588 cat<<-EOF

2589 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2590 href="?blog+$rowid">グループの新規話題作成</a>

2591 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2592 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2593 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2594 <div class="fold clear">

2595 `cgi_checkbox send yes id="send"`<label

2596 for="send">グループ全員にメッセージ送信</label>

2597 <div>

2598 `cgi_textarea message "" "cols=60"`

2599 `cgi_submit 送信`

2600 `cgi_reset リセット`

2601 </div>

2602 `cgi_hidden grp $rowid`

2603 </div></form>

2604 EOF

2605 fi

2606 # 加入ボタン + 加入者リスト

2607 if [ -n "$ismember" ]; then

2608 ismem='checked' state="(参加中)"

2609 else

2610 nomem='checked' state="(現在非加入)"

2611 fi

2612 # このグループでの加入アドレス

2613 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2614 and key='email';"`

2615 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2616 ## and key='email';"

2617 ##err email=$eml

2618 cat <<EOF

2619 <div class="fold clear">

2620 `cgi_checkbox reg yes id="reg"`<label

2621 for="reg">自身の加入状態を操作する</label>$state

2622 <div>

2623 EOF

2624 cgi_form grp <<EOF

2625 <p>このグループに</p>

2626 <table class="b">

2627 <tr><th>メンバーとして</th><td>

2628 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2629 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2630 <tr><th>参加する場合のメイルアドレス<br>

2631 <small>(メインのアドレスとは違うものにする場合に記入<br>

2632 同じでよい場合は空欄に)</small></th>

2633 <td>`cgi_text email $eml`</td></tr>

2634 </table>

2635 `cgi_hidden grp $rowid`

2636 EOF

2637 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2638 echo "moderated (承認加入の)グループなので実際に参加できるのは

2639 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2640 fi

2641 echo '</div></div>'

2642 echo '<h2>話題一覧</h2>'

2643 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2644 cat<<-EOF

2645 <form class="summary" action="$myname" title="$thelp">

2646 `cgi_hidden owner "$grp"`

2647 `cgi_hidden kwd "@week"`

2648 `cgi_hidden stage searchart`

2649 `cgi_submit "一週間のまとめ"`

2650 </form>

2651 EOF

2652 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2653 colstate="state:稼動状態:frozen=rowclass=凍結"

2654 DT_CHLD=article:blogid \

2655 DT_QOWNER="$qgrp" \

2656 DT_VIEW=replyblog dumptable html blog \

2657 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2658

2659 getgname="(select gname from grp where rowid=$rowid)"

2660 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2661 cm="?commission+$rowid"

2662 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2663 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2664 # team list

2665 hexteams=`hexteams "$grp"`

2666 if [ -n "$hexteams" ]; then

2667 echo "チーム一覧" | html h2

2668 echo '<div class="dumptable"><table class="b">'

2669 sq $db -html -header<<-EOF

2670 SELECT val TEAM,

2671 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2672 MEMBERS

2673 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2674 EOF

2675 echo '</table></div>'

2676 fi

2677 }

2678 grp_getbodyclass() {

2679 # Get css class name for document.

2680 # `moderated' for moderated groups

2681 # `ismember' for groups where user belongs

2682 # $1=GroupName (w/o quote)

2683 # $user=userNameCurrentlyLogin

2684 ## err grp_getbodyclass: 1="$1"

2685 qgrp=`sqlquote "$1"`

2686 query<<-EOF

2687 SELECT coalesce(

2688 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2689 'open')

2690 ||

2691 CASE WHEN '$user'

2692 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2693 THEN ' ismember'

2694 ELSE ''

2695 END;

2696 EOF

2697 }

2698 grpaction() { # $1=group-rowid

2699 err GRP_ACTION:IN

2700 grid=${1:-`getpar grp`}

2701 grp=`getgroupbyid "$grid"`

2702 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2703 if [ -z "$grp" ]; then

2704 echo "無効な指定です。" | html p; return

2705 fi

2706 if ! ismember $user "$grp"; then

2707 echo "加入者のみに許可された操作です。" | html p; return

2708 fi

2709 echo "グループ $grp 個別選択操作" \

2710 | _m4 -D_TITLE_="syscmd(\`cat')" \

2711 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2712 $layout/html.m4.html

2713

2714 isowner=""

2715 isgrpowner "$user" "$grp" && isowner="yes"

2716 usel=`getpar usel`

2717 if [ -n "$usel" ]; then

2718 uids=$(echo `echo $usel`|tr ' ' ',')

2719 ## err grpaction-1: grp=$grp, `echo $sql`

2720 text=`getpar text|tr -d '\r'`

2721

2722 rm=`getpar rm` cfm=`getpar confirm`

2723 ## err rm=$rm cfm=$cfm

2724 if [ x"$rm" = x"yes" ]; then

2725 if [ "$isowner" ]; then

2726 if [ x"$rm$cfm" = x"yesyes" ]; then

2727 # Eliminate

2728 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2729 for tbl in grp_mem grp_mem_s grp_mem_m; do

2730 sql="delete from $tbl $cond;"

2731 # echo "sql=$sql"

2732 query "$sql"

2733 err rmGRPuser "$sql"

2734 done

2735 num=`query "select count(*) from user where rowid in ($uids);"`

2736 #err num=$num

2737 if [ 0$num -gt 0 ]; then

2738 sql="select coalesce(b.val,a.name) from user a left join \

2739 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2740 # err `echo "$sql"`

2741 html pre<<EOF

2742 以下の${num}名のグループ $grp 登録を解除しました。

2743 `query "$sql"`

2744 EOF

2745 fi

2746 else

2747 echo "確認のチェックがないのでやめておきます。" | html p

2748 return

2749 fi

2750 else # not Group Owner

2751 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2752 return

2753 fi

2754 cat<<EOF

2755

2756 EOF

2757 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2758 if [ -z "$text" ]; then # if msg is empty

2759 echo "なにかメッセージを..." | html p

2760 return 0

2761 fi

2762 gecos=`gecos $user`

2763 safegc=`echo "$gecos" | tr -d '<>@'`

2764 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2765 fromad=`myemail4group "$grp"`

2766 ###mail_from="$safegc <$fromad>"

2767 mail_from="$safegc <$user>" # TEST: 2020/5/13

2768 test -n `getpar sender` &&

2769 export SENDER=$user # TEST: 2020/5/15

2770 replyto=$fromad

2771

2772 ## Start parse of attachment files

2773 if [ -n "`getpar email`" ]; then

2774 ar=`getpar supprcpt`

2775 if [ -n "$ar" ]; then

2776 for a in $ar; do

2777 if checkdomain "$a"; then

2778 supprcpt="$supprcpt $a"

2779 else

2780 err "SupprcptErr=[$a] by $user"

2781 removercpt="$removercpt $a"

2782 fi

2783 done

2784 fi

2785 subj=`getpar subject`

2786 afiles=""

2787 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2788 do

2789 f=$tmpd/$fn

2790 if [ -s $f ]; then

2791 afiles=$afiles"${afiles:+ }$f"

2792 fi

2793 done

2794 else

2795 preface=$(cat <<-EOF

2796 $url

2797 のグループ「$grp」のメンバーである $gecos さんから、

2798 あなた宛のメッセージです。

2799 ----------------------------------------------------------

2800 EOF

2801 )

2802 fi

2803 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2804 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2805 subj="${subj:-$gecos さんからのメッセージ}"

2806 REPLYTO=$replyto

2807 MAIL_FROM=$mail_from

2808 export REPLYTO SMAIL_TO MAIL_FROM

2809 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2810 for r in $rcpts; do

2811 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2812 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2813 else

2814 # Show sender and rcpts address for guest

2815 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2816 fi

2817 if [ -n "$afiles" ];then

2818 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2819 else

2820 smail "$r" "$subj"

2821 fi <<EOF

2822 ${preface:+$preface$nl}$text

2823 EOF

2824 done

2825 if [ $? = 0 ]; then

2826 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2827 sql="select coalesce(b.val, a.name) from

2828 (select name from user where rowid in ($uids)) a

2829 left join user_s b on a.name=b.name and b.key='gecos';"

2830 html pre<<EOF

2831 `query "$sql"`

2832 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2833 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2834 EOF

2835 err SendDone: `echo $sql`

2836 fi

2837 elif [ x"$rm" = x"commission" ]; then

2838 grp_reg_adm $grid $usel

2839 elif [ x"$rm" = x"addteam" ]; then

2840 team=`getpar team|sed "s/'/''/g"` # for single quotation

2841 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2842 if [ x"$team" != x"$newteam" ]; then

2843 echo "チーム名に使えない文字を除去しました" | html p

2844 team=$newteam

2845 fi

2846 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2847 cat<<-EOF | html p

2848 有効なチーム名を入力してください。

2849 カンマだけ、「なし」という名前は使えません。

2850 EOF

2851 echo "有効なチーム名を入力してください。" | html p

2852 else

2853 grp_add_team $grid "$team" $usel

2854 fi

2855 elif [ x"$rm" = x"rmteam" ]; then

2856 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2857 rmteam=`getpar rmteam|sed "s/'/''/g"`

2858 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2859 gname='$grp' AND user='$user' AND key='team'\

2860 AND val='$rmteam';\"`" ]; then

2861 grp_rm_team $grid "$rmteam" $usel

2862 else

2863 echo "所属していないチームの除去操作はできません。"|html p

2864 fi

2865 else

2866 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2867 fi

2868 fi

2869 fi

2870 # POST count summary

2871 from=`getpar from`; to=`getpar to`

2872 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2873 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2874 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2875 # New entry

2876 sql="WITH mems AS (

2877 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2878 ON gm.user=g.name

2879 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2880 ), target_article AS (

2881 SELECT id FROM article_s

2882 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2883 ), posts AS (

2884 SELECT author, count(author) post

2885 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2886 WHERE blogid IN (SELECT id FROM blog_s

2887 WHERE key='owner'

2888 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2889 AND key='text'

2890 GROUP BY author

2891 ), teams AS (

2892 SELECT user, group_concat(val, ', ') team

2893 FROM grp_mem_m

2894 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2895 AND key='team'

2896 GROUP BY user

2897 ), user_post AS (

2898 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2899 FROM mems m LEFT JOIN posts

2900 ON m.name=posts.author

2901 GROUP by m.rowid

2902 )

2903 SELECT

2904 CASE

2905 WHEN (SELECT user FROM grp_adm

2906 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2907 AND user=up.name) IS NOT NULL

2908 then 'k'

2909 ELSE ''

2910 END || rowid || ',' || gecos NAME,

2911 post POST, team _TEAM_

2912 FROM user_post up LEFT JOIN teams t

2913 ON up.name=t.user

2914 ORDER BY gecos;"

2915 ## err grpaction: "`echo \"$sql\"`"

2916 tf=$tmpd/title.$$

2917 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2918 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2919 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2920 for=\"cmadmin\">管理者委任</label>

2921 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2922 </p></div>"

2923 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2924 title=\"Shortcut: g${nl}Remove from the Group\"

2925 for=\"conf\">GRP登録解除</label>

2926 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2927 <p>この操作による通知は本人に行きません。

2928 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2929 </div>"

2930 # Get team list to which current user belongs into $hexteams

2931 allhexteams=$(hexteams "$grp")

2932 if [ -n "$isowner" ]; then

2933 myhexteams="$allhexteams" # admin can remove all teams' attr

2934 else

2935 myhexteams=$(hexteams "$grp" "$user")

2936 fi

2937 if [ -n "$myhexteams" ]; then

2938 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2939 title=\"Shortcut: s${nl}Strip a team tag from\"

2940 for=\"cmrmteam\">チーム属性除去</label>

2941 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2942 を除去します: `cgi_checkbox teamconfirm yes` 確認

2943 <p>この操作による通知は本人に行きません。

2944 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2945 </div>

2946 "

2947 fi

2948 stf=$tmpd/selteam.$$

2949 cgi_select_h selteam "5445414d" $allhexteams > $stf

2950 b1='<label> <input type="checkbox" name="usel" value="'

2951 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2952 br='<span id="reverse" title="Reverse Selection"></span>'

2953 #b2='"> <span>' b3='</span></label>'

2954 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2955 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2956 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2957 cgi_form grpaction<<EOF \

2958 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2959 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2960 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2961 | _m4 -D_TITLE_="spaste(\`$tf')" \

2962 -D_SUBTITLE_="チェック後操作ボタン" \

2963 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2964 $layout/form+dump.m4.html \

2965 | _m4 -D_TEAM_="spaste(\`$stf')"

2966 <p>下でチェックした人を対象として:</p>

2967 <div class="foldtabs">

2968 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2969 title="Shortcut: a${nl}Add a team tag to"

2970 for="cmteam">同じチーム属性を付与</label>

2971 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2972 `cgi_datalist_h teams $allhexteams`

2973 </div>

2974 ${rmteammsg}

2975 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2976 title="Shortcut: d${nl}DirectMail to"

2977 for="sendmsg" title="Direct Message">DM送信</label>

2978 <div>

2979 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2980 title="Using email format">email書式を使う</label>

2981 <div class="folded">

2982 <table>

2983 <tr><td>From: </td><td>$user</td></tr>

2984 <tr><td>このFrom:で送る</td>

2985 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2986 </td></tr>

2987 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2988 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2989 <tr><td>ファイル添付: </td>

2990 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2991 </table>

2992 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2993 </div>

2994 <div>本文:`cgi_textarea text "" cols=72`

2995 </div>

2996 </div>

2997 ${isowner:+$cmmsg$excmsg}

2998 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2999 </div>

3000 <h4>$grp 参加者一覧</h4>$fromtonote

3001 <table class="td2r thl">

3002 `sq $db -header -html "$sql"`

3003 </table>

3004 `cgi_hidden grp $grid`

3005 `cgi_hidden myuid $myuid id="myuid"`

3006 EOF

3007 }

3008 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3009 # Create TEMPORARY VIEW

3010 query<<EOF

3011 CREATE TEMPORARY VIEW writeusers AS

3012 SELECT DISTINCT author FROM article

3013 WHERE id in (

3014 select id from article where blogid=(select id from blog where rowid=$1)

3015 );

3016 CREATE TEMPORARY VIEW movablegroups AS

3017 SELECT g.rowid growid , g.gname

3018 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3019 ON grp.gname=gm.gname -- そのユーザが属している

3020 AND user='$user') g -- グループに絞る

3021 WHERE (SELECT author FROM writeusers

3022 EXCEPT

3023 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3024 IS NULL;

3025 $2

3026 EOF

3027 }

3028 sql4readableblogs() {

3029 # Create view of blogs that can be readable to $user

3030 # Blog is readable when:

3031 # 1: blog owner is an user

3032 # 2: else, 2.1: owner-group where the $user belongs

3033 # 2.2: else, owner-group is not moderated

3034 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3035 cat<<EOF ## | tee tmp/sql.out

3036 CREATE TEMPORARY VIEW readableblogs AS

3037 SELECT blog.rowid rid, id, author

3038 FROM blog

3039 NATURAL JOIN

3040 (SELECT id,

3041 max(CASE key WHEN 'owner' THEN val END) owner,

3042 max(CASE key WHEN 'mode' THEN val END) mode

3043 FROM blog_s GROUP by id) bs

3044 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3045 THEN 1 -- blog owner is an user, READABLE

3046 WHEN (SELECT val FROM grp_s

3047 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3048 AND

3049 (SELECT user FROM grp_mem

3050 WHERE gname=bs.owner AND user='$user') IS NULL

3051 THEN 0

3052 WHEN mode IN ('quiz', 'enquete')

3053 THEN 0 -- "quiz" mode blog cannot be searched

3054 ELSE 1

3055 END;

3056 EOF

3057 }

3058 editheading() { # $1=rowid-of-heading

3059 rowid=${1%%[!A-Z0-9a-z_]*}

3060 if [ -z "$rowid" ]; then

3061 echo "話題番号が未指定です。" | html p

3062 return

3063 fi

3064 owner=`getvalbyid blog owner $rowid`

3065 title=`getvalbyid blog title $rowid`

3066 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3067 | _m4 -D_TITLE_="修正" \

3068 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3069 -D_BLOGS_="" -D_DUMPTABLE_="" \

3070 -D_FORM_="syscmd(\`cat')" \

3071 $layout/html.m4.html $layout/form+dump.m4.html

3072 # Move to group

3073 if isuser "$owner"; then

3074 crview4article $rowid

3075 n=`query "SELECT count(*) FROM writeusers;"`

3076 ## err N=$n

3077 if [ $((n)) -gt 0 ]; then

3078 ## err ROWID=$rowid

3079 sql="SELECT growid || ':' || gname FROM movablegroups;"

3080 cat<<-EOF

3081 <div class="fold">

3082 `cgi_checkbox mv send id="mv"`<label

3083 for="mv">この話題をグループ所有に移動する</label>

3084 <div>

3085 <form action="?mvart" method="POST" enctype="multipart/form-data">

3086 移動先グループ:

3087 <select name="mv2grp">

3088 EOF

3089 query ".mode html"

3090 query<<-EOF |

3091 $sql

3092 .mode list

3093 EOF

3094 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3095 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3096 cat<<-EOF

3097 </select>

3098 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3099 そのグループに加入しているものに限られます)</p>

3100 <p>`cgi_checkbox cfm yes`<label>確認

3101 (この操作は元に戻すことができません)</label></p>

3102 `cgi_hidden blogrowid $rowid`

3103 `cgi_submit 移動`

3104 `cgi_reset Reset`

3105 </form>

3106 </div>

3107 </div>

3108 EOF

3109 fi

3110 # end of isuser "$owner"

3111 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3112 [ -n "$hexteams" ];}; then

3113 none="`echo なし|hexize`"

3114 cat<<-EOF

3115 <div class="fold">

3116 `cgi_checkbox mv2team send id="mv2team"`<label

3117 for="mv2team">この話題を以下のチームのものにする</label>

3118 <div><p>現在の所属チーム設定:

3119 `query "SELECT

3120 coalesce((SELECT val FROM blog_s

3121 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3122 AND key='team'),

3123 ':なし');"`</p>

3124 <form action="?mvart" method="POST" enctype="multipart/form-data">

3125 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3126 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3127 `cgi_hidden blogrowid $rowid`<br>

3128 `cgi_submit 移動`

3129 `cgi_reset Reset`

3130 </form></div></div>

3131 EOF

3132 fi

3133 }

3134 mvart() { # move diary to some group or team

3135 # or move blog of group to team which belong to the group

3136 blogrowid=`getpar blogrowid`

3137 cfm=`getpar cfm`

3138 ##### echo move blog:$blogrowid to $mv2grp | html p

3139 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3140 . ./s4-blog.sh

3141 if [ -z "$blogrowid" ]; then

3142 echo "無効な指定です(mvart)。" | html p

3143 return

3144 elif [ x"$cfm" != x"yes" ]; then

3145 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3146 elif { mv2grp=`getpar mv2grp`

3147 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3148 [ -n "$mv2grp" ]; }; then

3149 crview4article $blogrowid

3150 ########## TRANSACTION BEGIN

3151 query "BEGIN;"

3152 n=`query "SELECT count(*) FROM writeusers;"`

3153 ## err Nwriteuser=$n

3154 if [ $((n)) -gt 0 ]; then

3155 query<<-EOF

3156 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3157 WHERE key='owner'

3158 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3159 AND $mv2grp IN (SELECT growid FROM movablegroups);

3160 EOF

3161 fi

3162 query "END;"

3163 ########## TRANSACTION END

3164 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3165 [ -n "$mv2team" ];}; then

3166 # blog owner can move it to ANY team

3167 case "$mv2team" in

3168 'なし')

3169 cat<<-EOF

3170 DELETE FROM blog_s

3171 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3172 AND key='team';

3173 EOF

3174 ;;

3175 "") ;;

3176 *)cat<<-EOF

3177 BEGIN;

3178 REPLACE INTO blog_s(id, key, val)

3179 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3180 'team', '$mv2team');

3181 REPLACE INTO blog_s(id, key, val)

3182 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3183 'notify', 'all'); -- Change notify to all

3184 END;

3185 EOF

3186 esac | query

3187 fi

3188 blog_reply $blogrowid

3189 echo yes | html p

3190 }

3191 editart() { # $1=article-rowid $2=blogrowid

3192 rowid=${1%%[!A-Z0-9a-z_]*}

3193 blogrowid=${2%%[!A-Z0-9a-z_]*}

3194 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3195 echo "表示する記事番号が未指定です。" | html p

3196 return

3197 fi

3198 owner=`getvalbyid blog owner $blogrowid`

3199 title=`getvalbyid blog title $blogrowid`

3200 author=`getvalbyid article author $rowid`

3201 ## err EDITart: owner=$owner, author=$author

3202 if isgrpowner "$user" "$owner"; then

3203 : EDIT OK

3204 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3205 echo "本人か所有者しか編集できません." | html p

3206 return

3207 fi

3208 aid=`query "select id from article where rowid=$rowid;"`

3209 tmpout=$tmpd/editart.$$.out

3210 GF_ACTION="?replyblog+$blogrowid#$aid" \

3211 edittable $formdir/article.def article $rowid \

3212 > $tmpout

3213 rm -f /tmp/editart.out

3214 # Cannot use pipelining to m4 with genform() because of stdin stack

3215 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3216 -D_FORM_="syscmd(cat $tmpout)" \

3217 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3218 -D_BLOGS_= -D_DUMPTABLE_= \

3219 $layout/html.m4.html $layout/form+dump.m4.html

3220 }

3221 send2mem() {

3222 rowid=`getpar grp`

3223 rowid=${rowid%%[!0-9]*} # Cleaning

3224 if [ -z "$rowid" ]; then

3225 echo "グループが未指定です。" | html p

3226 return

3227 fi

3228 message=`getpar message`

3229 if [ -z "$message" ]; then

3230 echo "文章を入れてください。" | html p

3231 return

3232 fi

3233 grp=`getgroupbyid $rowid`

3234 members=`collectemail $grp`

3235 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3236 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3237 mailfrom="`gecos "$user"` <$mailfrom>"

3238 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3239 msg=$(cat<<-EOF

3240 $urlbase?grp+$rowid

3241 グループ $grp に所属する

3242 `gecos $user` さんよりメッセージ:

3243

3244 $message

3245 EOF

3246 )

3247 # smail rcpt subj (file)

3248 for m in $members; do

3249 echo "$msg" |

3250 MAIL_FROM=$mailfrom \

3251 SENDER=$noreply \

3252 REPLYTO=$mailfrom \

3253 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3254 smail "$m" "$sj"

3255 done

3256 cat<<EOF

3257 <p>以下のユーザに送信しました。</p>

3258 <pre>

3259 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3260 </pre>

3261 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3262 EOF

3263 }

3264 joingrpadmit() {

3265 # $1=yes/no $2=session-key

3266 if [ -z "$2" ]; then

3267 echo "bye bye" | html p; return

3268 fi

3269 t_usr=`session=$2 getpar adduser`

3270 t_grp=`session=$2 getpar group`

3271 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3272 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3273 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3274 echo "無効な加入依頼です。" | html p

3275 echo "有効期限が切れたか、

3276 他の管理者がいる場合は処理済みの可能性があります。" | html p

3277 return

3278 fi

3279 if ! isgrpowner "$user" "$t_grp"; then

3280 echo "グループ管理者のみの機能です。" | html p; return

3281 fi

3282 case $1 in

3283 yes) joingrp "$t_grp" "$t_usr" yes ;;

3284 no) joingrp "$t_grp" "$t_usr" no ;;

3285 *)

3286 echo "無効な指定です($1)。" | html p

3287 return ;;

3288 esac

3289 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3290 rcpts="`getgroupadminmails $t_grp` $user"

3291 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3292 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3293 に

3294 $t_usr

3295 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3296 しました。"

3297 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3298 query "delete from session where id='$2';"

3299 echo "$body" | html p

3300 }

3301

3302 joingrprequest() {

3303 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3304 jss="joingrp-`date +%s`-`genrandom 12`"

3305 addsession $jss +${memoplimitdays}days

3306 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3307 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3308 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3309 $url

3310 $user さんから

3311 グループ $1

3312 に加入依頼がありました。

3313

3314 承認する:

3315 $urlbase?joingrpadmit+yes+$jss

3316

3317 白紙に戻す:

3318 $urlbase?joingrpadmit+no+$jss

3319 EOF

3320 echo "管理者に加入依頼を出しました。

3321 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3322 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3323 }

3324 joingrp() {

3325 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3326 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3327 if isgrpowner "$user" "$1"; then

3328 isowner="yes"

3329 elif [ -n "$5" ]; then

3330 isowner="yes"

3331 else

3332 isowner=""

3333 fi

3334 ## err jg:isgrpowner: isowner="$isowner"

3335 if [ -n "$isowner" ]; then

3336 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3337 elif [ x"$2" != x"$user" ]; then # if user is not login user

3338 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3339 return

3340 elif [ x"$3" = x"no" ]; then

3341 : # Do not pursue those who leave

3342 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3343 : # Member can change own email address for the joining moderated group

3344 else # adding user is $user itself

3345 case `getgroupattr $1 regmode` in

3346 moderated)

3347 joingrprequest "$@" # Request only

3348 return

3349 ;;

3350 *)

3351 ;;

3352 esac

3353 fi

3354 qgname=`sqlquote "$1"`

3355 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3356 cond="where gname=$qgname and user='$2'"

3357 if [ x"$3" = x"yes" ]; then

3358 query "replace into grp_mem values($qgname, '$2');"

3359 # Notify joingrp to admin

3360 action="に加入しました。"

3361 if [ -n "$4" ]; then

3362 if msg=`emaildomaincheck "$4"`; then

3363 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3364 'string', '$4', NULL);"

3365 else

3366 echo $msg

3367 fi

3368 else

3369 query "delete from grp_mem_s $cond and key='email';"

3370 fi

3371 if [ -n "$5" ]; then # as ADMIN

3372 # Coming here means newly created group

3373 sql="select case\

3374 when (select count(*) from grp_mem where gname=$qgname)=1\

3375 then (select user from grp_mem\

3376 where gname=$qgname and user='$user')\

3377 else '' end; "

3378 err NewGrpChk: $sql

3379 if [ -n "`query \"$sql\"`" ]; then

3380 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3381 query "replace into grp_adm values($qgname, '$user');"

3382 fi

3383 fi

3384 else

3385 query "begin;

3386 delete from grp_mem $cond;

3387 delete from grp_mem_s $cond;

3388 delete from grp_mem_m $cond;

3389 delete from grp_adm $cond;

3390 delete from grp_adm_s $cond;

3391 delete from grp_adm_m $cond;

3392 end;"

3393 action="から脱退しました。"

3394 fi

3395 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3396 $url?grp+$grid

3397 $user (`gecos $user`)さんが

3398 グループ $1

3399 $action

3400 EOF

3401 }

3402 grp_add_team() (

3403 # $1=grp-rowid $2=team $3...=user-rowid(s)

3404 grp=`getgroupbyid $1`

3405 team=$2; shift; shift

3406 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3407 { echo "BEGIN;"

3408 for user; do

3409 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3410 '$grp',\

3411 (SELECT name FROM user WHERE rowid=$user),\

3412 'team', 'string', '$team');"

3413 done

3414 echo "END;"

3415 } | query

3416 )

3417 grp_rm_team() (

3418 # $1=grp-rowid $2=team $3...=user-rowid(s)

3419 grid=$1

3420 qgrp=$(sqlquote "`getgroupbyid $grid`")

3421 team=$2; shift; shift

3422 [ -z "$grid" -o -z "$team" ] && return

3423 { echo "BEGIN;"

3424 for user; do

3425 echo "DELETE FROM grp_mem_m\

3426 WHERE gname=$qgrp \

3427 AND user=(SELECT name FROM user WHERE rowid=$user)\

3428 AND key='team' AND val='$team';"

3429 done

3430 cat<<-EOF

3431 DELETE FROM blog_s

3432 WHERE rowid=(

3433 SELECT rowid

3434 FROM blog_s a

3435 WHERE key='team'

3436 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3437 AND NOT EXISTS (SELECT * FROM grp_mem_m

3438 WHERE key='team' AND val=a.val -- a.val=team

3439 AND gname = (SELECT val FROM blog_s b

3440 WHERE a.id=b.id AND key='owner')

3441 ));

3442 EOF

3443

3444 echo "END;"

3445 } | query

3446 )

3447 grp_reg_adm() {

3448 # $1=grp-rowid $2...=user-rowid

3449 grid=$1

3450 grp=`getgroupbyid "$1"`

3451 if [ -z "$grp" ]; then

3452 echo "無効なグループIDです" | html p; return

3453 fi

3454 if ! isgrpowner "$user" "$grp"; then

3455 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3456 fi

3457 shift

3458 for urid; do

3459 newadm=`query "select name from user where rowid=$urid;"`

3460 if [ -z "$newadm" ]; then

3461 echo "指定ユーザIDがおかしいようです。" | html p; return

3462 fi

3463 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3464 err ismember $newadm $grp

3465 if ismember $newadm "$grp"; then

3466 # OK, go ahead

3467 getgname="(select gname from grp where rowid=$grid)"

3468 query "replace into grp_adm values($getgname, '$newadm');"

3469 # confirm insertion

3470 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3471 if [ -n "`query \"$sql;\"`" ]; then

3472 echo "追加完了: $newadm" | html p

3473 else

3474 echo "追加失敗($1 $urid)" | html p

3475 fi

3476 fi

3477 showgroup $grid

3478 done

3479 }

3480 dt_rowhack() {

3481 # From: <TR>

3482 # ....

3483 # <TD>rowclass=foo</TD>

3484 # </TR>

3485 # To: <TR class="foo">....<TD>foo</TD></TR>

3486 sed -e '

3487 /^<TR>/ {

3488 :loop

3489 s/\n//

3490 N

3491 /<\/TR>/ {

3492 s/\n//

3493 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3494 n

3495 }

3496 $q

3497 b loop

3498 }'

3499 }

3500 dumptable() {

3501 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3502 # textのフィールドだけ全てダンプにしたほうがいいか

3503 # $DT_VIEW sets link

3504 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3505 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3506 VIEW=${DT_VIEW-replyblog}

3507 if [ -n "$VIEW" ]; then

3508 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3509 fi

3510 sqlfile=$tmpd/dump.sql

3511 : > $sqlfile # ensure to be empty

3512 printf '.mode html\n.header 1\n' > $sqlfile

3513 # $DT_CHLD=ChildTable:BindColumn

3514 if [ -n "$DT_CHLD" ]; then

3515 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3516 cat<<-EOF >> $sqlfile

3517 -- presql

3518 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3519 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3520 EOF

3521 # Speed up counting of new articles

3522 cat<<-EOF >> $sqlfile

3523 -- presql2

3524 DROP TABLE IF EXISTS _counts;

3525 CREATE TEMPORARY TABLE _counts AS

3526 SELECT $_i, count($_i) cnt

3527 FROM $_t GROUP BY $_i;

3528 /* Prepare NEW count table */

3529 CREATE TEMPORARY TABLE _target AS

3530 SELECT b.rowid trowid, b.id

3531 FROM "$2" b JOIN "$2_s" s

3532 ON b.id=s.id AND s.key='owner'

3533 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3534

3535 DROP TABLE IF EXISTS _children;

3536 CREATE TEMPORARY TABLE _children AS

3537 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3538 FROM (SELECT t.trowid, t.id $_i, a.id

3539 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3540 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3541

3542 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3543 DROP TABLE IF EXISTS _news;

3544 DROP VIEW IF EXISTS _news;

3545

3546 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3547 -- INSERT INTO _news

3548 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3549 CREATE TEMPORARY VIEW _news AS

3550 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3551 FROM (SELECT DISTINCT id FROM _target)

3552 a LEFT JOIN

3553 (SELECT $_i, count(ctime) newcnt

3554 FROM _children x

3555 WHERE ctime > coalesce((SELECT time from myacclog

3556 WHERE tblrowid=x.trowid),

3557 '1970-01-01')

3558 GROUP BY $_i) b

3559 ON a.id=b.$_i;

3560 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3561 EOF

3562 # REMOVE next line until 2019/5/1

3563 cntall="(select count($_i) from $_t where $_i=a.id)"

3564 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3565 # REMOVE next assignment until 2019/5/1

3566 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3567 and id in (select id from $_t where $_i=a.id) \

3568 and val > coalesce((select time from myacclog where \

3569 tblrowid=a.rowid),\

3570 '1970-01-01'))"

3571 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3572 cnt="$cntnew as '新着', $cntall as '総数',"

3573 dt_class=" td2r td3r dumpblogs"

3574 fi

3575 # Construct join expression

3576 eav="" scols=""

3577 pk=`gettblpkey $2`

3578 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3579 substr=${substr:-%s}

3580 for col in ${3:-`gettbl_s_cols $2`}; do

3581 valvar=val

3582 case $col in

3583 gecos) scols="$scols${scols:+, }${col#}"

3584 continue ;; # built-in column name

3585 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3586 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3587 case "$as" in

3588 *:*=*) cnd=${as#*:}

3589 h=${cnd%%=*} v=${cnd#*=}

3590 h=`sqlquotestr "$h"`

3591 v=`sqlquotestr "$v"`

3592 valvar="CASE val WHEN $h THEN $v END"

3593 as=${as%%:*} ;;

3594 esac

3595 ;;

3596 *) as=${col} ;;

3597 esac

3598 ss=`printf "$substr" "$valvar"`

3599 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3600 scols="$scols${scols:+, }b.$as"

3601 done

3602 #case author when '$user' then a.rowid else '---' end as ID,

3603 if [ -n "$DT_SQL" ]; then

3604 echo "$DT_SQL"

3605 else

3606 cat<<-EOF

3607 SELECT a.rowid as LINK, $cnt $scols

3608 FROM $2 a LEFT JOIN

3609 (SELECT $pk,$eav,

3610 max(CASE key

3611 WHEN 'owner'

3612 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3613 as gecos

3614 FROM ${2}_s c GROUP BY $pk)

3615 b ON a.$pk=b.$pk $4;

3616 EOF

3617 fi >> $sqlfile

3618 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3619 sqlog<<-EOF

3620 *** SQL-file: $sqlfile ***

3621 `cat $sqlfile`

3622 EOF

3623 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3624 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3625 cat $sqlfile >> tmp/sql

3626 # query ".read $sqlfile" > $tmpd/foo

3627 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3628 cp $tmpd/foo tmp/

3629 err "----- `gdate +%FT%T.%3N` ------------555555"

3630 ## $ddd LINE exists at the end of this function

3631 fi

3632 printf '.mode list\n.header 0\n' >> $sqlfile

3633 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3634 <div>

3635 <div class="dumptable">

3636 <table class="b$dt_class">

3637 `query ".read $sqlfile"`

3638 </table>

3639 </div>

3640 </div>

3641 EOF

3642 ### `query ".read $sqlfile"`

3643 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3644

3645 ## REMOVE THIS!

3646 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3647 }

3648

3649 par2table() (

3650 # copy current parameters of par into destination table

3651 # $1=definition-file

3652 # Using $user and $session

3653 # Return value:

3654 # 0: Stored successfully

3655 # 1: Insufficient fillings

3656 # 2: No permission to modify the record

3657 # 3: Invalid rowid

3658 # 4: SUCCESS to delete

3659 # 5: Stop deletion for lack of confirm check

3660 # 6: Password length too short

3661 # 7: Password mismatch

3662 # 8: Old password incorrect

3663 # 9: Duplicated post

3664 rowid=`getpar rowid`

3665 if [ ! -e $1 ]; then

3666 echo "テーブル定義ファイルが見付かりません" | html p

3667 exit 1

3668 fi

3669 tbl=${1%.def}

3670 tbl=${tbl##*/}

3671 if [ -n "$rowid" ]; then # Modify existing entry

3672 if [ x"$tbl" = x"user" ]; then

3673 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3674 elif [ x"$tbl" = x"grp" ]; then

3675 sql="select gname from $tbl where rowid=$rowid;"

3676 ##err p2t:grp:q $sql

3677 isgrpowner "$user" "`query $sql`" && rowowner=$user

3678 elif [ x"$tbl" = x"blog" ]; then

3679 # Check if owner in blog_s

3680 blogowner=`getvalbyid blog owner "$rowid"`

3681 if isgrpowner "$user" "$blogowner"; then

3682 rowowner=$user

3683 else

3684 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3685 fi

3686 else

3687 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3688 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3689 fi

3690 ### err rowowner=$rowowner

3691 if [ x"$user" != x"$rowowner" ]; then

3692 echo "他人のレコードはいじれないの" | html p

3693 return 2

3694 elif [ -z "$rowowner" ]; then

3695 echo "指定したレコードはないみたい" | html p

3696 return 3

3697 fi

3698 rm=`getpar rm` cfm=`getpar confirm`

3699 # Editing existent entry

3700 if [ x"$rm" = x"yes" ]; then

3701 if [ x"$rm$cfm" = x"yesyes" ]; then

3702 query "delete from $tbl where rowid=$rowid;"

3703 return 4

3704 else

3705 echo "消去確認のチェックがないので消さなかったの..." | html p

3706 return 5

3707 fi

3708 fi

3709 fi

3710

3711 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3712 if [ -n "$rowid" ]; then

3713 # Update of existing record

3714 for col in `gettblcols $tbl`; do

3715 val=`getparquote $col`

3716 [ -z "$val" ] && continue

3717 ## err query "update $tbl set $col=$val where rowid=$rowid"

3718 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3719 sql="update $tbl set $col=$val where rowid=$rowid;"

3720 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3721 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3722 ## User name cannot be changed with interface provided with this

3723 ## script. But we offer the trigger to change owner user

3724 ## of blog_s table.

3725 #err "select quote($col) from $tbl where rowid=$rowid;"

3726 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3727 cat<<-EOF | query

3728 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3729 -- should use EXCLUSIVE transaction outside of this.

3730 SAVEPOINT par2table;

3731 $sql

3732 update blog_s set val=$val

3733 where key='owner' and val=$old;

3734 RELEASE SAVEPOINT par2table;

3735 EOF

3736 ## XX: DIRTY Hack Ends here

3737 ## We should keep blog's owner as a single column which has

3738 ## foreign key constraint with primary key of grp/user.

3739 else

3740 query "$sql"

3741 fi

3742 done

3743 # Then, set up $pval for further insertion of tbl_s and tbl_m

3744 for col in `gettblpkey $tbl`; do

3745 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3746 pval="$pval${pval:+, }\"$val\""

3747 done

3748 else

3749 # New entry

3750 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3751 # Avoid empty repost of article.

3752 if [ x"$tbl" = x"article" ]; then

3753 # If rowid is empty and ID exists in article-table, that is REPOST!

3754 aid=`getpar id`

3755 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3756 if [ -n "$xaid" ]; then

3757 # REPOST of article

3758 html p <<-EOF

3759 書き込み直後のリロードなので上書きを回避します。

3760 最新記事は末尾の「再読み込み」ボタンから見てください。

3761 EOF

3762 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3763 return 9 # STOP Duplicated posting

3764 fi

3765 fi

3766 # Generate values() for primary keys

3767 for col in `gettblpkey $tbl`; do

3768 # Genuine primary keys for _m and _s

3769 val=`getvalquote $tbl $col`

3770 [ -z "$val" ] && continue

3771 pval="$pval${pval:+, }$val"

3772 done

3773 ##err pval=$pval

3774 for col in `gettblfkey $tbl`; do

3775 # args for values() to insertion into master table

3776 val=`getvalquote $tbl $col`

3777 [ -z "$val" ] && continue

3778 formaster=$formaster"${formaster:+, }$val"

3779 done

3780 formaster="$pval${formaster:+, }$formaster"

3781 ## err formaster=$formaster

3782 if [ -z "$formaster" ]; then

3783 echo "項目を全て埋めてください" | html pre

3784 return 1

3785 fi

3786 ## err "replace into $tbl values($formaster);"

3787 query "replace into $tbl values($formaster);"

3788 ## Insertion to master table, done

3789 fi

3790

3791 transaction=$tmpd/sqlfile.sql; touch $transaction

3792 for kt in s m; do

3793 tb2=${tbl}_$kt

3794 for col in `gettbl_${kt}_cols $tbl`; do

3795 ptype=`getpartype $col "limit 1"`

3796

3797 # First, check update of existing entries in _m

3798 if [ $kt = m ]; then

3799 # sessID|address.1.22|string|Somewhere-x.y.z

3800 sql=""

3801 ##err dots from query "select var from par where var like '$col.%';"

3802 for v in `query "select var from par where var like '$col.%';"`; do

3803 # v=address.1.22

3804 st_rowid=${v##*.}

3805 origcol=${v%%.*} # original column derived from

3806 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3807 ##case `getpartype $v` in

3808 ## err CASE `gettbl_coltype $tbl/$origcol` in

3809 ## err edit flag = `getpar action.$v`

3810 case `getpar action.$v` in

3811 rm)

3812 if [ x`getpar confirm.$v` = x"yes" ]; then

3813 newsql="delete from $tb2"

3814 else

3815 echo "削除確認未チェック" | html p

3816 fi ;;

3817 edit)

3818 case `gettbl_coltype $tbl/$origcol` in

3819 image|document|binary)

3820 file=$tmpd/`getparfilename $v`

3821 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3822 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3823 continue

3824 fi

3825 ## err type=file=$file

3826 [ -z "$file" ] && continue

3827 bn=`sqlquotestr "${file##*/}"`

3828 bin="X'"$(hexize "$file")"'"

3829 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3830 type=\"file:$ct\"

3831 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3832 cachedir=`getcachedir "$tbl/$rowid"`

3833 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3834 rm -rf $cachedir

3835 ;;

3836 *)

3837 newsql="update $tb2 set val=(select val from par where var \

3838 like '$col.%.$st_rowid')"

3839 ;;

3840 esac

3841 ;;

3842 *) # maybe "keep", do not modify value

3843 continue

3844 ;;

3845 esac

3846 # err newsql=$newsql

3847 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3848 done

3849

3850 if [ x"$bin" = x"NULL" ]; then

3851 ## err repl:normal sql=`echo $sql`

3852 if [ -n "$transaction" ]; then

3853 cat<<-EOF >> $transaction

3854 $sql

3855 DELETE FROM $tb2 WHERE type='string' AND val='';

3856 EOF

3857 else

3858 query "$sql

3859 delete from $tb2 where type='string' and val='';"

3860 ## err repl:normal done

3861 fi

3862 else

3863 # Binary update line is TOO LONG to pipelining

3864 sqlfile="$tmpd/sqlf.$$"

3865 if [ -n "$transaction" ]; then

3866 echo "$sql" >> $transaction

3867 else

3868 echo "$sql" > $sqlfile

3869 query ".read $sqlfile"

3870 fi

3871 fi

3872 # Rest of kt==m: set multiple mode

3873 nr=`getparcount $col`

3874 else

3875 nr=1 # for kt==s, number of records is 1

3876 fi

3877

3878 i=0

3879 while [ $i -lt $nr ]; do

3880 limit="limit 1 offset $i"

3881 i=$((i+1)) # increase beforehand against continue

3882 val=`getvalquote $tbl $col "$limit"`

3883 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3884 ## err $col=$val

3885 bin=NULL

3886 ## err partype$col=`getpartype $col "$limit"`

3887 case $ptype in

3888 file) file=$tmpd/`getparfilename $col "$limit"`

3889 ## err parfile-$col=$file

3890 [ -z "$file" ] && continue

3891 bin="X'"$(hexize "$file")"'"

3892 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3893 type=\"file:$ct\" ;;

3894 "*"*) continue ;; # foreign table

3895 *) type=\"string\" ;;

3896 esac

3897 case `gettbl_coltype $tbl/$col` in

3898 password) # special care for password

3899 # name={password,pswd1,pswd2}

3900 p1=`getpar pswd1 "$limit"`

3901 if [ -z "$p1" ]; then

3902 continue # SKIP password setting, if p1 is empty

3903 else

3904 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3905 ## err pswd=$pswd

3906 if pwcheck "$pswd"; then

3907 if [ x"$p1" = x"$p2" ]; then

3908 case "$p1" in

3909 ??????????*) ;;

3910 *) echo "パスワードは10字以上にしてください。" | html p

3911 return 6;;

3912 esac

3913 val="\"`echo $p1|mypwhash`\""

3914 else

3915 echo "2つの新パスワード不一致" | html p

3916 return 7

3917 fi

3918 else

3919 echo "旧パスワード違います" | html p

3920 return 8

3921 fi

3922 fi

3923 ;;

3924 esac

3925 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3926 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3927 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3928 if [ x"$bin" = x"NULL" ]; then

3929 ## err Normal-query: `echo $sql`

3930 if [ -n "$transaction" ]; then

3931 echo "$sql" >> $transaction

3932 else

3933 query "$sql"

3934 fi

3935 else

3936 sqlfile="$tmpd/query.$$"

3937 ## err sqlfile=`ls -lF $sqlfile`

3938 if [ -n "$transaction" ]; then

3939 echo "$sql" >> $transaction

3940 else

3941 echo "$sql" > $sqlfile

3942 query ".read $sqlfile"

3943 fi

3944 fi

3945 ## err p2t done

3946 done

3947 done

3948 done

3949 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3950 -- We cannot use transaction here, because groupupdate may use it.

3951 SAVEPOINT pa2table_insert;

3952 .read $transaction

3953 RELEASE SAVEPOINT pa2table_insert;

3954 EOF

3955 rc=$?

3956 [ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag

3957 ## err "Table:$tbl update done "

3958 return $rc

3959 )

3960 genform() {

3961 # $1 = form definition file

3962 # $2, $3 (optional)= table name and ROWID

3963 # If $GF_VIEWONLY set and nonNull, output values without form

3964 # If $GF_ARGS set, use it as content-strings in the form

3965 # If $GF_OWNER set, use it as value of name="owner"

3966 # If $GF_STAGE set, use it as value of name="stage"

3967 forms="" hiddens="" rowid=$3

3968 if [ ! -e "$1" ]; then

3969 echo "そのようなデータベースはないようです($2)。" | html p

3970 return

3971 elif [ -n "$2" ]; then

3972 rec=`query "select * from $2 where rowid='$rowid';"`

3973 if [ -z "$rec" ]; then

3974 pk=`gettblpkey $2`

3975 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3976 rec=`query "select rowid from $2 where $pk='$rowid';"`

3977 rowid=$rec

3978 rec=$3

3979 fi

3980 if [ -z "$rec" ]; then

3981 echo "そんなレコードはないみたいね..." | html p

3982 return

3983 fi

3984 fi

3985 if [ -z "$GF_VIEWONLY" ]; then

3986 rm='<input id="rm" name="rm" type="checkbox"

3987 value="yes"><label for="rm">このエントリの削除</label>

3988 <span>ほんとうに消しますよ(確認)!

3989 <input name="confirm" type=checkbox value="yes">はい</span>'

3990 fi

3991 # Image Cache dir

3992 ## err genform: getcache=$2/$rowid

3993 td=`getcachedir "$2/$rowid"`

3994 while IFS=: read prompt name keytype type args; do

3995 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3996 sp="${args:+ }"

3997 form="" val=""

3998 if [ -n "$rowid" ]; then

3999 # err genform2a: Seeking for "$2.$name, type=$type"

4000 rawval=`getvalbyid $2 $name $rowid $td`

4001 val=`echo "$rawval"|htmlescape`

4002 ## err genform3a: getvalbyid $2 $name $rowid $td

4003 ## err genform3b: val="[$val]" type="$type"

4004 fi

4005 if [ -n "$GF_VIEWONLY" ]; then

4006 is_hidden "$2" "$name" && continue

4007 fi

4008 case "$type" in

4009 text*)

4010 cgiform=cgi_multi_$type

4011 if [ -s $td/$name.count -a -n "$val" ]; then

4012 form=`$cgiform $name $td`

4013 val=$(echo "$val"|

4014 while read fn; do

4015 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4016 </td></tr>$nl"

4017 done)

4018 val="<table>$nl$val$nl</table>"

4019 else

4020 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4021 form=`cgi_$type $name "$rawval" "$args"`

4022 fi

4023 ;;

4024 [Rr][Aa][Dd][Ii][Oo])

4025 fh="<label><input type=\"radio\" name=\"$name\""

4026 form="`echo $args|sed -e \

4027 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4028 ;;

4029 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4030 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4031 ;;

4032 [Ss][Ee][Ll][Ee][Cc][Tt])

4033 fh="<select name=\"$name\">$nl"

4034 form=$(for l in $args; do

4035 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4036 done)

4037 if [ -n "$val" ]; then

4038 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4039 fi

4040 form="$fh$form</select>"

4041 ;;

4042 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4043 if [ -s $td/$name.count ]; then

4044 form=`cgi_multi_file $name $td "$args"`

4045 if [ -n "$val" ]; then

4046 hrfb="$myname?showattc+$2_m"

4047 val=$(echo "$rawval" \

4048 | while read fn; do

4049 data=`percenthex "$td/$fn"`

4050 #ct=`cat $td/$fn.content-type`

4051 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4052 ri=`cat "$td/$fn.rowid"`

4053 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4054 #imgsrc="<img src=\"data:$ct,$data\">"

4055 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4056 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4057 done)

4058 fi

4059 else

4060 form="<input type=\"file\" name=\"$name\" $args>"

4061 if [ -n "$val" ]; then

4062 imgs=$(echo "$rawval"\

4063 |while read fn;do

4064 data=`percenthex "$td/$fn"`

4065 echo "<img src=\"data:image/png,$data\">$fn<br>"

4066 done)

4067 form=$form"<br>$imgs"

4068 val=$imgs # 2015-06-15

4069 else

4070 form="<input type=\"file\" name=\"$name\" $args>"

4071 fi

4072 fi

4073 ;;

4074 [Hh][Ii][Dd][Dd][Ee][Nn])

4075 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4076 args="value=\"$GF_STAGE\""

4077 fi

4078 form="<input type=\"hidden\" name=\"$name\" $args>"

4079 prompt='' # Remove prompt

4080 ;;

4081 [Aa][Uu][Tt][Hh][Oo][Rr])

4082 [ -n "$GF_VIEWONLY" ] && continue

4083 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4084 prompt="" ;;

4085 [Oo][Ww][Nn][Ee][Rr])

4086 [ -n "$GF_VIEWONLY" ] && continue

4087 val=${GF_OWNER:-$val}

4088 val=${val:-$user}

4089 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4090 prompt="" ;;

4091 [Uu][Ss][Ee][Rr])

4092 # XXX: is null $user ok?

4093 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4094 [ -n "$GF_VIEWONLY" ] && continue

4095 form="$user"

4096 ;;

4097 [Pp]assword)

4098 [ -n "$GF_VIEWONLY" ] && continue

4099 form="`cgi_passwd`"

4100 val=""

4101 ;;

4102 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4103 [ -n "$GF_VIEWONLY" ] && continue

4104 if [ -z "$rowid" ]; then

4105 val=`genserial`

4106 fi

4107 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4108 prompt="" ;;

4109 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4110 prompt=""

4111 ;;

4112 parent|path|blog*)

4113 prompt=""

4114 ;;

4115 "*"*)

4116 tail=$tail"``"

4117 continue ;;

4118 esac

4119 if [ -n "$prompt" ]; then

4120 if [ -n "${GF_VIEWONLY}" ]; then

4121 form=$val

4122 else

4123 :

4124 fi

4125 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4126 else

4127 hiddens=$hiddens$nl"$form"

4128 fi

4129 done < $1

4130 # enctype="multipart/form-data"

4131 cat<<EOF

4132 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4133 ${rowid:+$rm}

4134 <table class="b $2">

4135 $forms

4136 </table>$hiddens

4137 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4138 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4139 EOF

4140 if [ -z $GF_VIEWONLY ]; then

4141 cat<<EOF

4142 <input type="submit" name="sub" value="OK">

4143 <input type="reset" name="res" value="Reset">

4144 EOF

4145 fi

4146 cat<<EOF

4147 $GF_ARGS</form>

4148 $tail

4149 EOF

4150 }

4151 edittable() {

4152 # $1=form-def $2=table $3 rowid

4153 genform "$@"

4154 }

4155 viewtable() {

4156 GF_VIEWONLY=1 genform "$@"

4157 }

4158 showattc() {

4159 # $1=table_m $2=rowid &optional $3=RawFlag

4160 ## err \$1=$1 \$2=$2 \$3=$3

4161 if ! isfilereadable $user $1 $2; then

4162 contenttype; echo

4163 echo "このファイルは管理者のみしか見られません" | html p

4164 putfooter; exit

4165 fi

4166 idir=`umask 002; mktempd` || exit 1

4167 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4168 bin=$idir/$myname-$$.bin

4169 sql="select quote(bin) from $1 where rowid='$2';"

4170 ## err showattc: sql: $sql

4171 sq $db "$sql" | unhexize > $bin

4172 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4173 type=${tv%//*} fn=${tv#*//}

4174 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4175 ct=${type#file:}

4176 case $ct in # all text/* changed to text/plain

4177 text/*)

4178 charset=`nkf -g $bin|cut -d' ' -f1`

4179 case $charset in

4180 ASCII*) charset="" ;;

4181 esac

4182 if [ -z "$3" ]; then

4183 ct="text/html${charset:+; charset=$charset}"

4184 link="?showattc+$1+$2+raw"

4185 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4186 | sed 's,^,<span></span>,' \

4187 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4188 -D_LINK_="$link" \

4189 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4190 exit $?

4191 fi

4192 ct="text/plain${charset:+; charset=$charset}"

4193 ;;

4194 esac

4195 contenttype "$ct"

4196 echo "Content-Disposition: filename=\"$fn\""

4197 echo "Content-Length: " `cat $bin | wc -c`; echo

4198 #echo "Content-Type: " ${type#file:}; echo

4199 cat $bin

4200 }

4201 #

4202 # Some default stupid handler on CGI values

4203 #

4204 default_storedb() {

4205 # ARG: $1=table-def-file

4206 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4207 tbl=`basename $1`

4208 tbl=${tbl%.def}

4209 cols="`grep :text $1|cut -d: -f2`"

4210 col=`echo "$cols"|head -1`

4211 vcol=`getpar $col`

4212 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4213 if [ -n "$vcol" ]; then

4214 par2table $1

4215 else

4216 return 2 # No insertion occurred

4217 fi

4218 }

4219

4220 default_view() { # $1=def-file

4221 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4222 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4223 default_storedb "$@"

4224 query "select rowid from $tbl order by rowid desc;" \

4225 | while read rowid; do

4226 viewtable $1 $tbl $rowid

4227 done | _m4 -D_TITLE_="$tbl" \

4228 -D_FORM_="`genform $1`" \

4229 -D_DUMPTABLE_="syscmd(cat)" \

4230 $layout/html.m4.html $layout/form+dump.m4.html

4231 }

4232 default_viewtext() { # $1=def-file

4233 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4234 default_storedb "$@"

4235 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4236 | _m4 -D_TITLE_="$tbl" \

4237 -D_FORM_="`genform $1`" \

4238 -D_DUMPTABLE_="syscmd(cat)" \

4239 $layout/html.m4.html $layout/form+dump.m4.html

4240 }

4241 default_smail() {

4242 default_storedb "$@"

4243 if [ $? -eq 2 ]; then

4244 _m4 -D_TITLE_="入力" \

4245 -D_FORM_="`genform $1`" \

4246 -D_DUMPTABLE_="" \

4247 $layout/html.m4.html $layout/form+dump.m4.html

4248 return

4249 fi

4250 cond=""

4251 for pk in `gettblpkey $tbl`; do

4252 pv=$(sqlquote "$(getpar $pk)")

4253 cond="$cond${cond:+ and }$pk=$pv"

4254 done

4255 sql="select rowid from $tbl where $cond;"

4256 rowid=`query "$sql"`

4257 ## err smail1 - "$sql" "-> rowid=$rowid"

4258

4259 while IFS=: read prompt name keytype type args; do # Read from $1

4260 val=`getpar $name`

4261 if [ -n "$val" ]; then

4262 text="$text

4263 $prompt

4264 $name=$val

4265 ---------------------------------------------------------"

4266 fi

4267 case "$type" in

4268 image|document|file)

4269 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4270 fns=$(echo "$fn"|while read fn; do

4271 err mv $tmpd/$fn.orig $tmpd/$fn

4272 mv $tmpd/$fn.orig $tmpd/$fn

4273 rm $tmpd/$fn.rowid # Remove cache flag

4274 ## err "`ls $tmpd/$fn`"

4275 echo $fn

4276 done)

4277 files="$files $fns"

4278 ;;

4279 esac

4280 done < $1

4281 ## err FILES=$files "`ls -lF $tmpd`"

4282 subj="from ${REMOTE_ADDR}"

4283 (echo "$url"

4284 echo "への書き込みがありました。"

4285 echo "------"

4286 echo "$text"

4287 ) | (cd $tmpd &&

4288 err LS="`ls -lF`" &&

4289 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4290 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4291 echo "以下の内容で送信しました。" | html p

4292 viewtable $1 $tbl \

4293 `query "select rowid from $tbl order by rowid desc limit 1;"`

4294 echo "戻る" | html a "href=\"?\""

4295 }