s4: c22e71dc8d9b s4-funcs.sh

s4

view s4-funcs.sh @ 752:c22e71dc8d9b

Base of annex feature added

author	HIROSE Yuuji <yuuji@gentei.org>
date	Wed, 03 Jun 2020 21:32:10 +0900
parents	98702a1251cd
children	23f17b1aebc3

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}

60 tconfs=""

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

62 conftbl=_tblconf

63 nl="

64 "

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

66 iconcachekey="profimgcache_S"

68 # Start debug logging

69 exec 3>> $tmpdir/debug.out

70 err() {

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

72 }

73 # If S4MASTERDB is set, behave as ANNEX s4

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

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

76 fi

79 case "$HTTP_USER_AGENT" in

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

81 *) touchpanel="" ;;

82 esac

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

85 : <<EOF

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

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

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

90 レポート提出管理

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

93 [2016]

94 7/12 根本への反省

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

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

98 [以下2015]

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

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

101 Web

102 締切設定

103

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

105 ○自分の提出物リスト

106

107 7/19 ○設置

108 ○一斉送信

109 ○getparfilename の tmpd の扱い

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

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

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

113 ○添付ファイル回収

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

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

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

117

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

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

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

121 ○レポート提出モード

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

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

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

125

126

127 7/9 ○管理者の追加

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

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

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

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

132

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

134

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

136 ○グループ検索

137

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

139

140

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

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

143

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

145 ○6/1 par2tableを triplex 対応に

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

147 →とすると一気に

148

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

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

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

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

153

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

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

156 ## 1. passwd

157 ## 2. grp

158 ## 3. grp_mem

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

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

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

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

163

164 ## ● listの定義:

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

166 ## ● hashの定義:

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

168

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

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

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

172 ## csv2sq3 で .csv.sq3 の Makefile

173

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

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

176 ## type := root | comment

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

178 ## unique(id, filename)

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

180

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

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

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

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

185 ## できたー!

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

187 ↓

188 ↓以下に変更

189 with recursive allmem as

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

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

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

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

194

195

196 with recursive allmem as

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

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

199 where allmem.val=grp_m.gname)

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

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

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

203

204

205 ## triggerもできた。

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

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

208

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

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

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

212 ## グループ管理

213 ## 個人情報管理

214 ##

215

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

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

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

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

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

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

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

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

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

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

226

227 ## ログテーブル

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

229

230

231

232 ■表設計

233 * 3つの表に分散管理

234 id格納表 + hash表 + list表

235 * *_s *_m

236

237

238

239 user, user_map, user_col

240

241 ■抽象エントリタイプ

242 * user

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

244 * group

245 権限判定に利用

246 * serial

247 自動idとして機能

248 * password

249 入力 type=passwordで入力

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

251 * session

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

253 * text

254 入力 type=text

255 * textarea

256 入力 textarea

257 * image|document

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

259 * owner

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

261 * gowner

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

263 * timestamp

264 datetime()

265 * parent

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

267 * path

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

269

270 格納タイプ

271 * list

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

273 * hash

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

275

276 オブジェクトタイプ

277 * entry

278 id, title, owner

279 * textpart

280 id, parentID, text

281 * binarypart

282 id, parentID, contenttype, filename, content

283 * content

284 hash(textpart), list(binarypart)

285 * topic

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

287 * reply

288 id, parentID, content

289 * blog

290 list(entry)

291 blog = [topic, list(reply)]

292

293

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

295 "text" => "hogehoge ..",

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

297 "author" => "taro",

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

299 "parent" => "/",

300 "path" => "/1",

301 "text" => "blah, blah, ....",

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

303 {"serial" => 2,

304 "author" => "hanako",

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

306 "parent" => "/",

307 "path" => "/2",

308 "text" => "blah, blah, ....",

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

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

311

312

313 user:=

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

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

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

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

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

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

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

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

322

323 変換表

324 /user/email=m

325

326 blog:=

327 シリアル:id:p:serial

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

329 所有者:owner:s:owner:

330 時刻:ctime:s:stamp:

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

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

333

334 article:=

335 シリアル:id:p:serial

336 筆者:author:s:owner

337 時刻:ctime:s:stamp:

338 参照元:parent:s:parent:

339 パス:path:s:path:

340 本文:text:s:textarea:

341 画像:image:m:image:

342

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

344

345

346 EOF

347

348 logstart() {

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

350 }

351 logend() {

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

353 }

354 sqlog() {

355 logstart

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

357 cat >> $querylog

358 else

359 echo "$*" >> $querylog

360 fi

361 logend

362 }

363 sq() {

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

365 logstart

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

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

368 else

369 echo "$@" >> $querylog

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

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

372 fi

373 logend

374 }

375 dbsetup() {

376 pipedir=$tmpdir/pipedir

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

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

379 suf=`date +%s`

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

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

382 mkfifo $sqi $sqo

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

384 sq $db < $sqi &

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

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

387 exec 2>> $tmpdir/error.out

388 fi

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

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

391 rm $sqi

392 # Attach supplemental DB

393 cat >&5 <<-EOF

394 .output /dev/null

395 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

399 .output stdout

400 EOF

401 }

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

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

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

405 fi

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

407 }

408 cleanup() {

409 trap '' INT HUP EXIT TERM PIPE

410 echo .quit >&5

411 kill $sq3pid

412 kill $sq3pid

413 rm -f $sqo $sqi

414 rm -rf $tmpfiles

415 cleanup2

416 }

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

418 # simple redirection for the sake of speed.

419 query() {

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

421 echo ".output $sqo" >&5

422 logstart

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

424 tee -a $querylog

425 else

426 echo "$@" >> $querylog

427 echo "$@"

428 fi >&5

429 echo ".output stdout" >&5

430 cat $sqo

431 rc=$?

432 logend

433 return $rc

434 }

435 _m4() {

436 #S4NAME=f,f,f

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

438 }

439 ismember() {

440 # $1=user, $2=group

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

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

443 }

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

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

446 }

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

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

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

450 }

451 isgrpowner() (

452 # $1=user, $2=group

453 gn=`sqlquote "$2"`

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

455 err isgrpowner: $sql

456 test -n "`query $sql`"

457 )

458 isgrpownerbygid() (

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

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

461 err isgrpownerbygid: $sql

462 test -n "`query $sql`"

463 )

464 getgroupadminmails() {

465 # $1=group

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

467 email4group "$1" "$i" ;

468 done

469 }

470 getgroupadmins() { # $1=group

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

472 qgrp=`sqlquote "$1"`

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

474 }

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

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

477 getvalbyid grp $2 \

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

479 }

480 getgroupbyid() {

481 # $1=id|gname

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

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

484 # err ggbyid: `echo $sql`

485 query $sql

486 }

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

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

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

490

491 # Return true when anonymous mode

492 [ "$anonymousmode" ] && return 0

493 # case `getvalbyid blog mode $2` in

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

495 # *closed*)

496 # owner=`getvalbyid blog owner $2`

497 # if isgrp $owner; then

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

499 # elif isuser $owner; then

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

501 # fi

502 # esac

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

504 sql="with getblog as (

505 select key,val from blog_s where id=(

506 select blogid from article where id in

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

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

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

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

511 isgrpadm as (select user from grp_adm where

512 gname=(select val from getowner) and

513 user='$1'),

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

515 select case

516 when (select author from article where

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

518 then 'author'

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

520 then 'open'

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

522 then CASE

523 WHEN (SELECT val FROM isgrp) IS NULL

524 THEN

525 CASE WHEN (SELECT val from getowner)

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

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

528 ELSE ''

529 END

530 WHEN (select user from isgrpadm) IS NOT NULL

531 THEN 'i-am-admin'

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

533 END

534 when (select val from getmode) is null

535 then 'open'

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

537 then (SELECT user FROM isgrpadm)

538 when (select author from article where

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

540 then 'user+author'

541 else '' end;"

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

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

544 # result=`query "$sql"`

545 # err FileAccessibility=$result

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

547 }

548 linkhome() {

549 # $1=UserOrGroup

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

551 if isuser $1; then

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

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

554 else

555 echo -n "grp+$1"

556 fi

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

558 }

559 hreflink() {

560 # s4 specific notation:

561 # ^href=URL

562 # ^iframe=URL

563 # ^video=URL

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

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

566 # OSM umap Wikistyle Notation:

567 # [[URL]] - Simle Link

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

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

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

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

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

573 # Other Style

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

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

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

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

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

579 # SPC+SPC+$ - <br>

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

581 checkboxON="${cb} checked>"

582 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

607

608 }

609 minitbl() {

610 sed -n '

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

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

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

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

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

616 H; # Concat this line to HoldSpace

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

618 $ b cont

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

620 }

621 :cont

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

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

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

625 # . of ^. is workaround for FreeBSD sed

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

627 p; # Print whole "table" element

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

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

630 }

631 x; # Back to the newest line

632 p; # Print rest' | miniul

633 }

634 miniul() {

635 sed -e '

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

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

638 :top

639 s/\n//;

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

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

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

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

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

645 $ b cont

646 N; # 次の行を読む

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

648 /^ /b top

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

650 p; s/.*//;

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

652 /^\* /b top

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

654 }

655

656 :cont

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

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

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

660 H; x

661 }

662 x'

663 }

664 acclog() (

665 # $1=table, $2=rowid

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

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

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

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

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

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

672 fi

673 )

674 gecos() (

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

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

677 )

678 setpar() {

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

680 case "$1" in

681 user) _user="$v" ;;

682 skey) _skey="$v" ;;

683 esac

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

685 }

686 replpar() {

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

688 }

689 getpar() {

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

691 val=""

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

693 user) val=$_user ;;

694 skey) val=$_skey ;;

695 esac

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

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

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

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

700 fi

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

702 case "$var" in

703 owner)

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

705 echo $user; return

706 elif ismember $user $val; then

707 echo $val; return

708 fi ;;

709 esac

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

711 echo "$val"

712 }

713 setskey() {

714 # For quick response...(?)

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

716 }

717 chkskey() {

718 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

724 return 0

725 fi

726 return 1

727 }

728 resetskey() {

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

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

731 fi

732 }

733 getpartype() {

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

735 }

736 getparcount() {

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

738 }

739 getparfilename() {

740 # null if type of $1 is not file

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

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

743 }

744 sqlquote() {

745 (v="$1"

746 case "$v" in

747 "") return ;; # null

748 "X'"*) # quoted hex string

749 echo $1 ;;

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

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

752 echo "\"$v\""

753 return ;;

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

755 echo "\"$v\""

756 return ;;

757 *)

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

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

760 else

761 echo "\"$v\""

762 fi ;;

763 esac)

764 }

765 sqlquotestr() (

766 case "$1" in

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

768 echo "'$v'" ;;

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

770 esac

771 )

772 mktempd() {

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

774 }

775 getcachedir() { # $1=maintable

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

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

778 else

779 echo $tmpd/$thumbxy

780 fi

781 }

782 getval() {

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

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

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

786 echo "$user" ;;

787 stamp|datetime)

788 date "+%F %T" ;;

789 serial)

790 (s=`getpar $2`

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

792 *)

793 getpar "$2" "$3";;

794 esac

795 }

796

797 getvalquote() {

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

799 (v=`getval "$@"`

800 case "$v" in

801 "") echo NULL ;;

802 *) sqlquote "$v" ;;

803 esac)

804 }

805 getparquote() {

806 sqlquote "`getpar $1`"

807 }

808 getbinbyid() {

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

810

811 }

812 getvalbyid() {

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

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

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

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

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

818

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

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

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

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

823 return

824 fi

825 done

826 rowid=$3

827 pk=`gettblpkey $1`

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

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

830 td=${4:-$tmpd}

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

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

833 for kt in s m; do

834 t=${1}_$kt

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

836 vcount=1 # count(val)

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

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

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

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

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

842 if [ $kt = m ]; then

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

844 # Reset val to store filenames if type is string

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

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

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

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

849 i=0

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

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

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

853 i=$((i+1))

854 fn=$c.$i

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

856 case $type in

857 file:*)

858 #file=$td/$val

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

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

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

862 # FOR SPEED: Skip file generation if imgcache exists

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

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

865 && continue

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

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

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

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

870 select rowid from $cond $slice;

871 .output '$td/$fn'

872 select val from $cond $slice;

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

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

875 .output stdout

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

877 EOF

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

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

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

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

882 ls -lh "$file" |

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

884 case "$type" in

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

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

887 *|*:[Aa]pplication*)

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

889 png:- > "$file"

890 ;;

891 esac

892 ;;

893 *)

894 sq $db<<EOF

895 .output $td/$fn.rowid

896 select rowid from $cond $slice;

897 .output $td/$fn

898 select val from $cond $slice;

899 EOF

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

901 ;;

902 esac

903 done

904 else

905 rm -f $td/$c.count

906 case $type in

907 file:*)

908 echo "$val" \

909 | while read fn; do

910 file=$td/$fn

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

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

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

914 | unhexize > "$file"

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

916 case $type in

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

918 *:[Aa]pplication*)

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

920 png:- > $file ;;

921 esac

922 fi

923 done

924 ;;

925 esac

926 fi

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

928 return

929 fi

930 done

931 done)

932 }

933 getvalbypkey() (

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

935 pk=`gettblpkey $1`

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

937 getvalbyid "$1" "$2" $rowid $4

938 )

939 getvalbycond() {

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

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

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

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

944 getvalbyid "$1" "$2" $rowid "$4"

945 fi

946 }

947 getpwfield() {

948 # getpwfield user column

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

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

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

952 echo "$val"

953 return 0

954 else

955 return 1

956 fi

957 }

958 numericalize() {

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

960 }

961 encode() {

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

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

964 sha1=sha1

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

966 sha1=sha1sum

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

968 sha1=gsha1sum

969 fi

970 fi

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

972 }

973 enjpeg() {

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

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

976 cjpeg="cjpeg"

977 else

978 cjpeg="convert - jpeg:-"

979 fi

980 fi

981 $cjpeg "$@"

982 }

983 mycrypt() (

984 key=$1 salt=$2

985 # err \$2=$2

986 case $2 in

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

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

989 esac

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

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

992 )

993 hexize() {

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

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

996 hexize="xxd -p"

997 else

998 hexize_hd() {

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

1000 }

1001 hexize="hexize_hd"

1002 fi

1003 fi

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

1005 }

1006 unhexize() {

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

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

1009 unhex="xxd -p -r"

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

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

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

1013 EOF

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

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

1016 # world writable...:)

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

1018 fi

1019 fi

1020 cat "$@" | $unhex

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

1022 }

1023 percenthex() {

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

1025 }

1026 htmlescape() {

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

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

1029 }

1030 enascii() {

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

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

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

1034 else

1035 enascii_now=`date +%FT%T`

1036 enascii_sed() {

1037 nkf -Z0Z1Z2 \

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

1039 }

1040 enascii="enascii_sed"

1041 fi

1042 fi

1043 cat "$@" | $enascii

1044 }

1045 size_h() {

1046 i="$1" oi=$1

1047 set -- B B KB MB GB TB

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

1049 oi=$i

1050 i=$((i/1024))

1051 shift

1052 done

1053 echo ${oi}$1

1054 }

1055 gettblconf() {

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

1057 ## tconfs=`sq $db \

1058 tconfs=`query \

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

1060 fi

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

1062 }

1063 gettblkeys() {

1064 # $1=tbl

1065 gettblconf

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

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

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

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

1070 case $type in

1071 *p*)

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

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

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

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

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

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

1078 esac

1079 case $type in

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

1081 esac

1082 done

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

1084 }

1085 gettblpkey() {

1086 # $1=tbl

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

1088 }

1089 gettblfkey() {

1090 (x=`gettblkeys $1`

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

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

1093 }

1094 gettblcols() {

1095 (x=`gettblkeys $1`

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

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

1098 }

1099 gettbl_s_cols() {

1100 (x=`gettblkeys $1`

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

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

1103 }

1104 gettbl_m_cols() {

1105 (x=`gettblkeys $1`

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

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

1108 }

1109 gettbl_h_cols() {

1110 (x=`gettblkeys $1`

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

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

1113 }

1114 gettbl_coltype() (

1115 gettblconf

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

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

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

1119 )

1120 is_hidden() {

1121 # $1=Tbl $2=col

1122 gettblconf

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

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

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

1126 case $x in

1127 *h*) return 0 ;;

1128 *) return 1 ;;

1129 esac

1130 }

1131

1132 dbsetbyid() {

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

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

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

1136 quotedp=$(sqlquotestr "$p")

1137 unset primary update

1138 gettblconf

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

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

1141 #err conf=$conf

1142 case ${conf#*=} in

1143 p*) primary=1 ;;

1144 f*) update=1 ;;

1145 u*) ;;

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

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

1148 esac

1149 #err t=$t

1150 type=string fn=""

1151 case $conf in

1152 */password)

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

1154 ;;

1155 */image*|*/document*)

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

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

1158 ;;

1159 esac

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

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

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

1163 if [ "$primary" ]; then

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

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

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

1167 elif [ "$update" ]; then

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

1169 else

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

1171 fi

1172 )

1173 }

1174 expire() (

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

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

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

1178 )

1179 addsession() {

1180 # expireをセット

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

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

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

1184 # Remove old session parameters

1185 now=`expire now`

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

1187 }

1188 gencookie() (

1189 path=${URL#*:/}

1190 path=${URL%/*}

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

1192 for kv; do

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

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

1195 done

1196 )

1197 contenttype() {

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

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

1200 }

1201 putheader() {

1202

1203 }

1204 putfooter() {

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

1206 }

1207 getcookie() {

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

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

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

1211 case "$k" in

1212 user) _user="$v" ;;

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

1214 esac

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

1216 done

1217 }

1218 genrandom() {

1219 # $1=columns (default: 10)

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

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

1222 }

1223 genserial() {

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

1225 }

1226 smail() {

1227 # smail rcpts subj (file)

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

1229 # $MAIL_FROM <- From: header value

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

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

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

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

1234 sender=${SENDER:-$admin}

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

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

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

1238 }

1239 smail_queue_flush() {

1240 # $1=timelimit

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

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

1243 SELECT rowid FROM smailq

1244 GROUP BY rcpts, subj

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

1246 EOF

1247 )

1248 for rid in $rowids; do

1249 sq $workdb \

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

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

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

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

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

1255

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

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

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

1259 .separator "\n" "------------------\n\n"

1260 SELECT time, text FROM smailq

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

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

1263 ORDER by time;

1264 EOF

1265 then

1266 echo rowid=$rid

1267 cat <<-EOF | sq $workdb

1268 DELETE FROM smailq

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

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

1271 EOF

1272 fi

1273 fi

1274 done

1275 }

1276 smail_queue() {

1277 # $1=Rcpts, $@=subj

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

1279 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1288 EOF

1289 )

1290 ## XXX: Adhoc load mitigation

1291 case "$now" in

1292 *[01])

1293 err flush_queue=$mintime

1294 smail_queue_flush ;;

1295 esac

1296 fi

1297 }

1298 setviastring() {

1299 table=par

1300 oifs="$IFS"

1301 IFS="&"

1302 for us in $1; do

1303 k=${us%%=*}

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

1305 setpar "$k" "string" "$v"

1306 done

1307 IFS="$oifs"

1308 }

1309 checkdomain() (

1310 # Check the validity of domain by referring DNS

1311 item=$1

1312 err checkdomain $1

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

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

1315 )

1316 pwcheck() {

1317 # $1=passwd

1318 dbpswd=`getpwfield $user pswd`

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

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

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

1322 }

1323 mypwhash() {

1324 mycrypt "`cat`" `genrandom 5`

1325 }

1326 wasureta() {

1327 user=$1

1328 if ! checkdomain $user; then

1329 contenttype; echo

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

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

1332 putfooter

1333 exit 0

1334 fi

1335 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1337 encpswd=`echo $newpswd|mypwhash`

1338 dbsetbyid user $user pswd "$encpswd"

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

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

1341 $msgdir/mail-newaccount.m4 \

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

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

1344 }

1345 checkauth() {

1346 user=`getpar user`

1347 skc=`getpar skey` # from cookie

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

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

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

1351 if chkskey "$skc"; then

1352 return 0

1353 fi

1354 fi

1355 pswd=`getpar pswd`

1356 quser=`sqlquotestr "$user"`

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

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

1359 return 4 # 4=server too heavy

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

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

1362 return 2 # 2=login fail

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

1364 wasureta "$user"

1365 return 1 # wasureta error

1366 fi

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

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

1369 if pwcheck "$pswd"; then

1370 newsession=`genrandom 34`

1371 if setskey "$user" "$newsession" &&

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

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

1374 gencookie "user=$user" "$skey=$newsession"

1375 return 0

1376 else

1377 return 4 # Heavy load??

1378 fi

1379 fi

1380 err "Login failed: [$user]"

1381 return 2 # Password mismatch

1382 }

1383 showlogin() {

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

1385 test -z "$args" && resetskey

1386 s4name=${S4NAME:-s4}

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

1388 cat $layout/login.m4.html

1389 echo '</body></html>'

1390 ) | _m4 \

1391 -D_BODYCLASS_="login" \

1392 -D_TITLE_="$s4name" \

1393 -D_SYSNAME_="Welcome to $s4name" \

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

1395 exit 0

1396 }

1397 dologin() {

1398 checkauth

1399 st=$?

1400 if [ $st != 0 ]; then

1401 contenttype; echo

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

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

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

1405 showlogin # and EXIT

1406 fi

1407 }

1408

1409 # Do instant jobs here

1410 dbsetup

1411 trap cleanup INT HUP EXIT TERM PIPE

1412 # trap cleanup INT HUP

1413

1414

1415 cgiinit() {

1416 session=`date +%F-$$`

1417 tmpd=`tmpd=$tmpdir mktempd`

1418 tmpf=$tmpd/stream.$$

1419 tmpfiles=$tmpfiles" $tmpd"

1420 addsession $session

1421 getcookie

1422 case "$REQUEST_METHOD" in

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

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

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

1426 # cat > $tmpf # too much?

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

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

1429 s="`cat $tmpf`"

1430 ;;

1431 esac

1432 case "$CONTENT_TYPE" in

1433 *boundary*)

1434 bndry=${CONTENT_TYPE#*boundary=}

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

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

1437 do

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

1439 #echo u=$us

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

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

1442 # err k=$k v=$v

1443 case "$k" in

1444 *:filename)

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

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

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

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

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

1450 :

1451 fi

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

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

1454 case "$mimetype" in

1455 [Ii]mage/x-xcf)

1456 bzip2 "$tmpd/$v"

1457 v=${v}.bz2

1458 ;;

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

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

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

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

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

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

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

1466 v=$vjpg

1467 else

1468 mimetype="Not supported"

1469 fi

1470 ;;

1471 [Ii]mage/*)

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

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

1474 ;;

1475 esac

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

1477 then

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

1479 continue

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

1481 replpar text string \

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

1483 continue

1484 fi

1485 ;;

1486 *)

1487 type='string'

1488 ;;

1489 esac

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

1491 setpar "$k" "$type" "$v"

1492 done

1493 ;;

1494 *)

1495 setviastring "$s"

1496 ;;

1497 esac

1498 }

1499 email4group() {

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

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

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

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

1504 sql="WITH

1505 grpemails AS (

1506 SELECT gname, user, val email

1507 FROM grp_mem NATURAL JOIN grp_mem_s

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

1509 useremails AS (

1510 SELECT user.name, val email

1511 FROM user

1512 LEFT JOIN user_m

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

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

1515 FROM useremails u LEFT JOIN grpemails g

1516 ON u.name=g.user

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

1518 query "$sql"

1519 }

1520 email4groupbyuid() {

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

1522 g=$1; shift

1523 uids=`echo "$@"`

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

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

1526 email4group "$g" `query "$sql"`

1527 }

1528 myemail4group() {

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

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

1531 }

1532 collectmembersbyid() {

1533 # Collect user names of group specified by grid

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

1535 query "SELECT user FROM grp_mem \

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

1537 }

1538 collectmembersbyid() {

1539 # Collect user names of group name

1540 qgrp=`sqlquote "$1"`

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

1542 }

1543 collectgecosesbyid() {

1544 # Collect user gecoses of group

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

1546 query<<-EOF

1547 SELECT gecos

1548 FROM gecoses

1549 WHERE name IN (SELECT user FROM grp_mem

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

1551 EOF

1552 }

1553 collectemail() (

1554 # Collect email addresses for group $1

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

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

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

1558 for e; do

1559 if isuser "$e"; then

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

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

1562 else

1563 qgrp=`sqlquote "$e"`

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

1565 gmem="grp_mem"

1566 else

1567 tm=`sqlquote "$TEAM"`

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

1569 fi

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

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

1572 $gmem g left join grp_mem_s s

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

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

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

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

1577 query "$sql"

1578 fi

1579 done

1580 )

1581 sendinvitation() (

1582 # $1=email

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

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

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

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

1587 gecos=`gecos`

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

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

1590 _m4 -D_URL_="$urlbase" \

1591 -D_USER_="$name" \

1592 -D_EMAIL_="$1" \

1593 -D_REGIST_="$regist" \

1594 -D_ADMIN_="$admin" \

1595 $msgdir/mail-invite.m4 \

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

1597 return 0

1598 )

1599 emaildomaincheck() {

1600 case "$1" in

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

1602 *@*)

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

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

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

1606 return 2

1607 fi

1608 return 0

1609 ;;

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

1611 esac

1612 }

1613 invite() {

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

1615 case "$email" in

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

1617 *@*)

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

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

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

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

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

1623 elif sendinvitation $email; then

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

1625 else # Cannot be reached here

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

1627 fi ;;

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

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

1630 esac

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

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

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

1634 fi

1635 if [ -f $invite_policy ]; then

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

1637 else

1638 pol="$invite_policy"

1639 fi

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

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

1642 -D_INVITE_POLICY_="$pol" \

1643 $layout/html.m4.html $layout/invite.m4.html

1644 }

1645 regist() {

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

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

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

1649 echo "bye bye" | html p

1650 reutrn

1651 fi

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

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

1654 cat<<EOF

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

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

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

1658 EOF

1659 return

1660 fi

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

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

1663 # Fake login password to wasureta

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

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

1666 wasureta $email

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

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

1669 addsession $1 # for removal after 1 minute

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

1671 $layout/login.m4.html

1672 return

1673 }

1674 group_safename() {

1675 # Convert $1 to safe group name

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

1677 }

1678 groupupdate() {

1679 gname=`getpar gname`

1680 qgname=`sqlquote "$gname"`

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

1682 # See ALSO same job in showgroup()

1683 newgname=`group_safename "$gname"`

1684 err newgname=$newgname

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

1686 err NewGNAME: gname=$newgname

1687 gname=$newgname

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

1689 replpar gname string "$gname"

1690 fi

1691 # Name confliction check

1692 parow=`getpar rowid`

1693 ## err parow=$parow

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

1695 query "BEGIN EXCLUSIVE;"

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

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

1698 if [ $count -gt 0 ]; then

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

1700 query "END;"

1701 return

1702 fi

1703 par2table $formdir/grp.def

1704 query "END TRANSACTION;"

1705 # Remove orphan

1706 : <<EOF

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

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

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

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

1711 EOF

1712 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1716 sql="delete from blog where id in

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

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

1719 query "$sql";

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

1721 return # and return

1722 fi

1723 fi

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

1725 fi

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

1727 grid=$(query $sql)

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

1729 grp $grid

1730 }

1731 groupclone() {

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

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

1734 case "$1" in

1735 */noteam)

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

1737 esac

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

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

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

1741 return

1742 fi

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

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

1745 return

1746 fi

1747 i=0

1748 while true; do

1749 copy="-copy$i"

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

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

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

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

1754 i=$((i++))

1755 continue

1756 fi

1757 break

1758 done

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

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

1761 query<<-EOF

1762 BEGIN;

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

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

1765 SELECT $newqname, key, val

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

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

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

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

1770 -- Copy members and their configuration --

1771 REPLACE INTO grp_mem SELECT $newqname, user

1772 FROM grp_mem WHERE gname=$qgrp;

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

1774 FROM grp_mem_s WHERE gname=$qgrp;

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

1776 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1777 -- Copy administrators --

1778 REPLACE INTO grp_adm SELECT $newqname, user

1779 FROM grp_adm WHERE gname=$qgrp;

1780 COMMIT;

1781 EOF

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

1783 STOPCLONEMSG=1 groupconf "$newrowid"

1784 }

1785 groupman() {

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

1787

1788 GF_STAGE="grpconf"

1789 GF_STAGE=groupupdate

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

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

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

1793 -D_DUMPTABLE_="syscmd(cat)" \

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

1795 }

1796 userconf() {

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

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

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

1800 }

1801 groupconf() {

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

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

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

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

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

1807

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

1809 grp=`getgroupbyid $rowid`

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

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

1812 return

1813 fi

1814

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

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

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

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

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

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

1821 <div>

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

1823 <table>

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

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

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

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

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

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

1830 </table>

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

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

1833 </div>

1834 EOF

1835 fi

1836 }

1837 mems() {

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

1839 kwd=`getpar kwd`

1840 listmember $kwd

1841 }

1842 grps() {

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

1844 kwd=`getpar kwd`

1845 listgroup $kwd \

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

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

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

1849 $layout/form+dump.m4.html

1850 }

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

1852 gpg=`getpar grp`

1853 grid=${1:-$gpg}

1854 grp=`getgroupbyid "$grid"`

1855 ## . ./s4-blog.sh

1856 jg=`getpar joingrp`

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

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

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

1860 fi

1861 htmlheader=$layout/html.m4.html

1862 showgroup "$grid"

1863 }

1864 sql4interestblogs() {

1865 cat<<EOF

1866 CREATE TEMPORARY VIEW interestblogs AS

1867 SELECT blog.rowid rid, id, author

1868 FROM blog

1869 NATURAL JOIN

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

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

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

1873 WHEN (SELECT user FROM grp_mem

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

1875 THEN 0

1876 ELSE 1

1877 END;

1878 EOF

1879 }

1880 listnewblogsql() { # $1=user

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

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

1883 newdays=${newdays:-14}

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

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

1886 WHERE user='$user'

1887 AND tblrowid IN

1888 ($blogreadflagrowid,

1889 $blogcutoffflagrowid)),

1890 $basetime -- "0"

1891 );"`

1892 cat<<EOF

1893 `sql4interestblogs`

1894 WITH article_ctime as (

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

1896 FROM article join article_s s using(id)

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

1898 GROUP BY id

1899 ), blog_title_owner as (

1900 SELECT blg.rid brid, id,

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

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

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

1904 ), blogall as (

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

1906 ), news as (

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

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

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

1910 FROM blogall bl

1911 LEFT JOIN

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

1913 ON bl.brid=al.tblrowid

1914 WHERE atime < bl.ctime

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

1916 LIMIT 10

1917 ) SELECT brid LINK, "新着",

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

1919 ctime, title,

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

1921 FROM news;

1922 EOF

1923 }

1924

1925 search_form() {

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

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

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

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

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

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

1932 @week → 最近一週間

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

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

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

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

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

1938 auth=""

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

1940 case "$1" in

1941 author=*)

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

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

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

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

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

1947 ;;

1948 grp=*)

1949 a=`echo "${1#grp=}"|htmlescape`

1950 g=`gecos ${1#grp=}`

1951 auth="<input type=\"hidden\" name=\"owner\" value=\"$a\">"

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

1953 ;;

1954 esac

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

1956 cat<<-EOF

1957 <div class="right">

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

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

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

1961

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

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

1964

1965 </form>

1966 </div>

1967 EOF

1968 }

1969

1970 imgsrc_cache() (

1971 # $1 = directory for cache'ing

1972 # $2 = table (user_m or grp_m)

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

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

1975 dir="$1" tbl="$2"

1976 keycond="$3"

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

1978 ORDER BY rowid DESC LIMIT 1"

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

1980 tmpf=$tmpd/imgsrc_cache.$$

1981 case "$4" in

1982 [Ss]) size=S ;;

1983 [Oo]) size=O ;;

1984 *) size=M ;;

1985 esac

1986 # ImageCache filename storing schema:

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

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

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

1990 valtype=`query "$sql0"`

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

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

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

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

1995 case "$filetype" in

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

1997 esac

1998 fi

1999 cacheimg_S=$dir/S_$filename

2000 cacheimg_M=$dir/M_$filename

2001 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

2009 else

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

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

2012 fifo2=$fifo.2

2013 mkfifo $fifo $fifo2

2014 fmt=${filename##*.}

2015 ## [[ NOTE ]]

2016 ## a. convert oldimage newimage

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

2018 ## b is much smaller than a

2019 cat $tmpf | unhexize \

2020 | tee $fifo \

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

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

2023 | tee $fifo2 \

2024 | convert - "$cacheimg_M" &

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

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

2027 | convert - "$cacheimg_S" &

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

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

2030 echo '">'

2031 echo "$sum" > $sumfile

2032 fi

2033 ## Now preparing cache image, done.

2034 ## Store this information to DB

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

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

2037 pval=${keycond#*=} # Primary Key value

2038 query <<-EOF

2039 REPLACE INTO $stbl($pkey, key, type, val)

2040 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2041 EOF

2042 )

2043

2044 showhome() {

2045 # $1=userRowIdToShow

2046 err showhome \$1=$1

2047 case "$1" in

2048 *@*) uname=`getvalbypkey user name "$1"` ;;

2049 *) uname=`getvalbyid user name $1` ;;

2050 esac

2051 ## err ShowHome: uname=$uname

2052 td=`getcachedir home/"$1"`

2053 gecos=`gecos "$uname"`

2054 ## err SH:gecos=$gecos

2055 GF_VIEWONLY=1

2056 cond="gname in (select gname from grp_mem where user='$uname')"

2057 search_form_args=""

2058 if [ x"$user" = x"$uname" ]; then

2059 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2060 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> /

2061 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2062 # Display folders

2063 sql="select count(id) from article_m where id

2064 in (select id from article where author='$user')

2065 and type like 'file:%';"

2066 ## err nfile-sql=`echo "$sql"`

2067 nfile=`query "$sql"`

2068 # err nfile=$nfile

2069 if [ $nfile -gt 0 ]; then

2070 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2071 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2072 fi

2073 else

2074 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2075 GROUP BY user;"`

2076 usermenu="<p>Last seen on $latestlog</p>"

2077 search_form_args="author=$uname"

2078 fi

2079 . ./s4-blog.sh

2080

2081 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2082 search_form "$search_form_args" > $sf

2083 printf "%s さん" "$gecos" > $tf

2084 { echo "<div class=\"noprofimg\">"

2085 viewtable $formdir/user.def user $1

2086 echo "</div>"

2087 } > $pf

2088

2089 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2090 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2091 imf=$tmpd/profimg.$$; touch $imf

2092 if [ -n "$img" ]; then

2093 if true; then

2094 tbl=user_m

2095 enticond="name='$uname'"

2096 imgsrc_cache "$td/main" user_m "$enticond" M

2097 else

2098 { printf '%s' "<IMG src=\"data:${img#file:},"

2099 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2100 | sed 's/$..$/%\1/g'

2101 echo '">'

2102 }

2103 fi > $imf

2104 fi

2105 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2106 val='$uname';"`

2107 ## REMOVE This comment block until 2019/7/1

2108 ## err "----- `gdate +%FT%T.%3N` ------------C"

2109 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2110 listblog $uname > $bf

2111 ## unset ddd

2112 ## err "----- `gdate +%FT%T.%3N` ------------D"

2113

2114 hometail=$tmpd/tail.$$

2115 mkfifo $hometail

2116

2117 #Calling listgroupbytable, originally here

2118

2119 (

2120 # Display Most Recent Entry

2121 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2122 shortval=${shortval:-val}

2123

2124 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2125 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2126 ctime,

2127 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2128 (SELECT gecos FROM gecoses

2129 WHERE name=(SELECT val FROM blog_s

2130 WHERE key='owner' AND id=b.id)) owner,

2131 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2132 FROM blog b

2133 JOIN

2134 (SELECT distinct blogid, a.id aid, max(val) ctime

2135 FROM article a, article_s s

2136 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2137 GROUP BY blogid ORDER BY val DESC LIMIT 50

2138 ) m

2139 ON b.id=m.blogid;"

2140 # This should be as follows

2141 : <<EOF

2142 WITH arts AS(

2143 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2144 a.blogid, a.id id, s.val ctime

2145 FROM article a NATURAL JOIN article_s s

2146 WHERE s.key = 'ctime' AND a.author='$user'

2147 GROUP by s.id

2148 )

2149 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2150 FROM arts a0

2151 JOIN

2152 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2153 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2154 ORDER BY ctime DESC LIMIT 50;

2155 EOF

2156

2157 cat<<-EOF

2158 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2159 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2160 <div class="lcto">

2161 `DT_VIEW=replyblog dumptable html blog`

2162 </div>

2163 EOF

2164 unset DT_SQL

2165 if [ x"$user" = x"$uname" ]; then

2166 # Display NEWS

2167 # 2016-06-26

2168 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2169 acclog blog $blogreadflagrowid

2170 # echo "全部既読にしました" | html p

2171 fi

2172 # 2016-02-19 Counting NEWS without using dumptable.

2173 sql=`listnewblogsql "$user"`

2174 # echo "$sql" > tmp/listnew

2175 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2176 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2177 cont=$((cont-1))

2178 err newcount=$cont

2179 if [ $cont -gt 0 ]; then

2180 #echo "全体の新着記事${cont}傑" | html h2

2181 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2182 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2183 cat<<-EOF | html form 'action="?home"'

2184 `cgi_checkbox readchk yes 'id="read"'`<label

2185 for="read">新着ふくめて全部読んだことにする</label>

2186 `cgi_submit '確定'`

2187 `cgi_hidden read yes`

2188 EOF

2189 echo "$new10 "

2190 echo "</div>"

2191 else # If news is 0, set log cut off flag

2192 acclog blog $blogcutoffflagrowid # for speed

2193 fi

2194 else # Not My Home ($user != $uname)

2195 : # DT_SQL=

2196 fi

2197 ) > $hometail & # Is background call safe to m4??

2198 #

2199 listgroupbytable $formdir/grp.def $cond |

2200 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2201 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2202 -D_PROFIMG_="spaste(\`$imf')" \

2203 -D_BLOGS_="spaste(\`$bf')" \

2204 -D_SEARCH_="spaste(\`$sf')" \

2205 -D_NBLOG_="$nblog" \

2206 -D_GROUPS_="syscmd(\`cat')" \

2207 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2208 $layout/html.m4.html $layout/home.m4.html

2209

2210 # Record access log

2211 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2212 }

2213 commission() { # $1=grp-rowid $2=user-rowid

2214 contenttype; echo

2215 ## err commission: "$@"

2216 gname=`getgroupbyid $1`

2217 echo "グループ $gname 管理者委任" \

2218 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2219 if [ -n "$2" ]; then

2220 grp_reg_adm "$@"

2221 else

2222 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2223 fi

2224 }

2225 listgroupbytable() {

2226 # $1=deffile $2...=condition

2227 tagline=`grep :tag: $1`; shift

2228 and="${1:+and }" where=${1:+where }

2229 href="<a href=\"$myname?grp+"

2230 echo '<div class="listgroup">'

2231 NGsql="select distinct tag from\

2232 (select gname, max(case key when 'tag' then val end) as tag, \

2233 max(case key when 'ctime' then val end) as ctime\

2234 from grp_s group by gname order by ctime);"

2235 sql="select val from grp_s where key='tag' $and$* group by val;"

2236 ## err ListGRP: query sql="$sql"

2237 for tag in `query "$sql"`

2238 do

2239 ## err ListGrp: tag=$tag

2240 tn=${tagline%%=${tag}*}

2241 tn=${tn##*[ :]}

2242 sql="select rowid||':'||gname as 'グループ名',説明 from

2243 (select (select rowid from grp g where g.gname=grp_s.gname)

2244 as rowid,

2245 gname,

2246 max(case key when 'gecos' then val end) as '説明',

2247 max(case key when 'tag' then val end) as 'tag',

2248 max(case key when 'mtime' then val end) as mtime from grp_s

2249 $where$* group by gname having tag='$tag' order by mtime desc);"

2250 ## err PersonalGroupList= `echo $sql`

2251 echo "<h2>$tn</h2>"

2252 echo '<table class="b listgroup">'

2253 sq -header -html $db "$sql" \

2254 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2255 echo '</table>'

2256 done

2257 echo '</div>'

2258 }

2259 iconhref() (

2260 # $1=icon-file, $2=Href $3=title $4...=anchor

2261 data=`percenthex "$1"`

2262 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2263 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2264 href=$2; title=$3; shift 3

2265 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2266 )

2267 iconhref2() (

2268 # $1=icon-file, $2=Href $3=title $4...=anchor

2269 src=$1

2270 href=$2; title=$3; shift 3

2271 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2272 )

2273 listentry() (

2274 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2275 # Referring variable $iamowner=$grp to attach owner-request links

2276 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2277 cond='' hiddens=''

2278 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2279 if [ -z "$offset" ]; then

2280 offset=`getpar start`; offset=${offset%%[!0-9]*}

2281 offset=$((offset-1))

2282 fi

2283 offset=$((offset + 0)) # change to numeric forcibly

2284 [ $offset -lt 0 ] && offset=0

2285 limit=$listentlimit

2286 dir=`getcachedir "$1"`

2287 if [ x"$1" = x"user" ]; then

2288 hrb="$myname?home"

2289 deficon=person-default.png

2290 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2291 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2292 gcs=gecos

2293 else # if group

2294 hrb="$myname?grp"

2295 deficon=group-default.png

2296 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2297 gcs=name

2298 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2299 if [ -n "$tagline" ]; then

2300 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2301 ## err tagconv=$tagconv

2302 fi

2303 fi

2304 if [ ! -d $dir ]; then

2305 mkdir -p $dir

2306 fi

2307 if [ ! -s $dir/$deficon ]; then

2308 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2309 fi

2310 if [ -n "$2" ]; then

2311 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2312 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2313 fi

2314 tag=`getpar tag` tag2=`getpar tag2`

2315 if [ x"$tag" = x"NULL" ]; then

2316 tag="" tag2=""

2317 fi

2318 if [ -n "$tag$tag2" ]; then

2319 tag=${tag:-$tag2}

2320 qtag=`sqlquote "$tag"`

2321 cond2="tag=$qtag"

2322 fi

2323 if [ -n "$cond1$cond2" ]; then

2324 cond="$cond1${cond2:+ AND $cond2}"

2325 cond="WHERE ${cond# AND }"

2326 fi

2327

2328 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2329 # grpは呼出し元の動的スコープ変数でよくないな...

2330 ##qgrp=`sqlquote $grp`

2331 getgrp="(select gname from grp where rowid=${rowid:--1})"

2332 sql="select a.rowid, a.$link,

2333 coalesce(b.$gcs, a.$nm) as nick,

2334 quote(a.$nm) as qname,

2335 (SELECT val FROM ${tbl}_s

2336 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2337 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2338 || case when a.$nm in (select gname from grp)

2339 then printf('(%d名)',

2340 (select count(user) from grp_mem where gname=a.$nm))

2341 else ' <'||a.$nm||'>'

2342 end

2343 as name,

2344 b.tag,

2345 case when a.$nm in (select user from grp_adm

2346 where gname=$getgrp) then '管理者'

2347 when '$user' in (select user from grp_adm where gname=a.$nm)

2348 then 'ADMIN'

2349 when '$user' in (select user from grp_mem where gname=a.$nm)

2350 then 'Member'

2351 when '$iamowner' = '' then ''

2352 else ',not='||a.rowid end as ownerlink,

2353 CASE '$entity'

2354 WHEN 'グループ'

2355 THEN coalesce(

2356 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2357 'open')

2358 ||

2359 CASE WHEN '$user'

2360 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2361 THEN ' ismember'

2362 ELSE ''

2363 END

2364 ELSE 'user'

2365 END regmode

2366 from $tbl a left join

2367 (select $nm as name,

2368 max(case key when 'gecos' then val end) as gecos,

2369 max(case key when 'tag' then val end) as tag,

2370 max(case key when 'mtime' then val end) as mtime,

2371 max(case key when 'wtime' then val end) as wtime,

2372 max(case key when 'login' then val end) as login

2373 from ${tbl}_s group by $nm)

2374 b on a.$nm=b.name $cond $3

2375 order by b.wtime desc, b.login desc,

2376 b.mtime desc, b.tag desc, a.rowid asc"

2377 # Give precedence to newer maintained groups (2016-09-24)

2378 # Note that mtime is stored only in grp_s.

2379 ## err LE:sql.1="$sql"

2380 total=`query "with x as ($sql) select count(*) from x;"`

2381 echo "${entity} 一覧" | html h2

2382 echo '<div class="listentry">' # List-entry div

2383 # Show owner/member filter button

2384 METHOD=GET

2385 hiddens="$hiddens

2386 `cgi_hidden kwd \"$kwd\"`

2387 `cgi_hidden stage \"$stage\"`"

2388 if [ x"$tbl" = x"grp" ]; then

2389 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2390 fh="<select name=\"tag\">$nl"

2391 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2392 for l in $args; do

2393 val=${l#*=} tname=${l%=*}

2394 if [ x"$val" = x"$tag" ]; then

2395 s=" selected"

2396 selectedtags="(種別[${tname}]のみ)"

2397 else

2398 s=""

2399 fi

2400 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2401 done

2402 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2403 cat<<-EOF

2404 <form action="$myname" method="$METHOD">

2405 </form>

2406 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2407 for="ismembtn">参加中以外隠す</label>

2408 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2409 for="isadmbtn">管理者参加以外隠す</label>

2410 EOF

2411 # limit=3

2412 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2413 fi

2414 if [ $total -gt $limit ]; then

2415 echo '<div>'

2416 METHOD=GET cgi_form $stage <<EOF

2417 $form

2418 <label>次の語を含む${entity}で検索:

2419 `cgi_text kwd "$kwd"`</label>

2420 $hiddens

2421 EOF

2422 echo '</div>'

2423 else

2424 echo $selectedtags | html p

2425 fi

2426 cat<<EOF

2427 <form action="$myname" method="$METHOD">

2428 <p>${total}件中の<input class="hidesub" type="text" name="start"

2429 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2430 <input type="submit" value="確定"></p>

2431 </form>

2432 EOF

2433 if [ $((offset+limit)) -lt $total ]; then

2434 nextbtn=$(

2435 cat<<EOF

2436 <div class="right clear"><form action="$myname" method="$METHOD">

2437 `cgi_submit 次の${limit}件`

2438 $hiddens

2439 `cgi_hidden offset $((offset + limit))`</form></div>

2440 EOF

2441 )

2442 fi

2443 if [ $offset -gt 0 ]; then

2444 prevbtn=$(

2445 cat<<EOF

2446 <form action="$myname" method="$METHOD">

2447 `cgi_submit 前の${limit}件`

2448 $hiddens

2449 `cgi_hidden offset $((offset - limit))`</form>

2450 EOF

2451 )

2452 fi

2453 pnbtn="$nextbtn$prevbtn"

2454 echo $pnbtn

2455

2456 ## err ListEntry: `echo "$sql"\;`

2457 # sq $db here??? 2016-11-28

2458 query "$sql limit $limit ${offset:+offset $offset};" \

2459 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2460 # err name=$name owner=$ownerp lnk=$lnk

2461 # err newlnk=$lnk regmode=$regmode

2462 icondir=$dir/$id

2463 # Pick up only last icon

2464 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2465 <p class=\"tag _$tag\">$tag</p>" \

2466 | _m4 $tagconv

2467 if [ -n "$NOSPEEDUP" ]; then

2468 files=`getvalbyid $tbl profimg $id $icondir`

2469 if [ -n "$files" ]; then

2470 icon=`echo "$files"|tail -1`

2471 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2472 else

2473 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2474 fi

2475 elif [ -n "$icon" -a -s "$icon" ]; then

2476 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2477 else

2478 cond="$nm=$qname"

2479 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2480 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2481 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2482 # err "img=[$img]"

2483 if [ -n "$img" ]; then

2484 echo "<a href=\"$hrb+$lnk\">"

2485 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2486 echo "</a>"

2487 else

2488 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2489 fi

2490 fi

2491 echo "<br>$name${ownerp:+<br>($ownerp)}"

2492 echo "</div>"

2493 done

2494 echo "</div>" # End of List-entry div

2495 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2496 )

2497 listmember() {

2498 listentry user "$@"

2499 }

2500 listgroup() {

2501 listentry group "$@"

2502 }

2503 hexteams() { # $1=gname, $2(optional)=user

2504 cond=${2:+" AND user='$2'"}

2505 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2506 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2507 }

2508 showgroup() { # $1=group-rowid

2509 if [ -z "$1" ]; then

2510 grid=`getpar grid`

2511 grid=${grid%%[!0-9]*}

2512 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2513 else

2514 grid=$1

2515 fi

2516 grp=`getgroupbyid $grid`

2517 qgrp=`sqlquote "$grp"`

2518 htmlgrp=`echo "$grp"|htmlescape`

2519 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2520 if isgroup "$grp"; then

2521 tf=$tmpd/title.$$

2522 sf=$tmpd/search.$$

2523 bodyclass=`query "SELECT val FROM grp_s

2524 WHERE gname=$qgrp AND key='regmode';"`

2525 if ismember "$user" "$grp"; then

2526 ismember="ismember"

2527 bodyclass="$bodyclass${bodyclass:+ }ismember"

2528 else

2529 ismember="" # bodyclass="group"

2530 fi

2531 bodyclass="$bodyclass grouphome"

2532 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2533 echo "グループ $htmlgrp" > $tf

2534

2535 showgroupsub $formdir/grp.def "$grid" | \

2536 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2537 -D_FORM_="syscmd(\`cat')" \

2538 -D_BODYCLASS_="$bodyclass" \

2539 -D_DUMPTABLE_="" \

2540 $htmlheader $sf $layout/form+dump.m4.html

2541 # $htmlheader $layout/form+dump.m4.html

2542 # $htmlheader is defined in grp()

2543 else # if $grp is removed at par2table

2544 listgroup

2545 fi

2546 }

2547 showgroupsub() {

2548 # $1=def-file $2=group-rowid

2549 # Using $ismember

2550 rowid=$2

2551 grp=`getgroupbyid $2`

2552 qgrp=`sqlquote "$grp"`

2553 td=`getcachedir grp/"$2"`

2554 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2555 if [ -z "$rowid" ]; then

2556 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2557 #grp=`sq $db "select gname from grp where rowid=$grp"`

2558 echo "showgroupsub: invalid argument($1 $2)" | html p

2559 return

2560 fi

2561 val=`getvalbyid grp profimg $rowid $tmpd`

2562 enticond="gname=$qgrp"

2563 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2564 if [ -n "$img" ]; then

2565 cat<<-EOF

2566 <p class="groupimg">

2567 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2568 EOF

2569 fi

2570 echo "<div class=\"noprofimg\">"

2571 viewtable $1 grp $rowid

2572 echo "</div>"

2573 if isgrpowner "$user" "$grp"; then

2574 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2575 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2576 iamowner=$rowid

2577 colmd=" mode"

2578 fi

2579 if [ -n "$ismember" ]; then

2580 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2581 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2582 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2583 cat<<-EOF

2584 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2585 href="?blog+$rowid">グループの新規話題作成</a>

2586 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2587 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2588 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2589 <div class="fold clear">

2590 `cgi_checkbox send yes id="send"`<label

2591 for="send">グループ全員にメッセージ送信</label>

2592 <div>

2593 `cgi_textarea message "" "cols=60"`

2594 `cgi_submit 送信`

2595 `cgi_reset リセット`

2596 </div>

2597 `cgi_hidden grp $rowid`

2598 </div></form>

2599 EOF

2600 fi

2601 # 加入ボタン + 加入者リスト

2602 if [ -n "$ismember" ]; then

2603 ismem='checked' state="(参加中)"

2604 else

2605 nomem='checked' state="(現在非加入)"

2606 fi

2607 # このグループでの加入アドレス

2608 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2609 and key='email';"`

2610 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2611 ## and key='email';"

2612 ##err email=$eml

2613 cat <<EOF

2614 <div class="fold clear">

2615 `cgi_checkbox reg yes id="reg"`<label

2616 for="reg">自身の加入状態を操作する</label>$state

2617 <div>

2618 EOF

2619 cgi_form grp <<EOF

2620 <p>このグループに</p>

2621 <table class="b">

2622 <tr><th>メンバーとして</th><td>

2623 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2624 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2625 <tr><th>参加する場合のメイルアドレス<br>

2626 <small>(メインのアドレスとは違うものにする場合に記入<br>

2627 同じでよい場合は空欄に)</small></th>

2628 <td>`cgi_text email $eml`</td></tr>

2629 </table>

2630 `cgi_hidden grp $rowid`

2631 EOF

2632 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2633 echo "moderated (承認加入の)グループなので実際に参加できるのは

2634 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2635 fi

2636 echo '</div></div>'

2637 echo '<h2>話題一覧</h2>'

2638 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2639 cat<<-EOF

2640 <form class="summary" action="$myname" title="$thelp">

2641 `cgi_hidden owner "$grp"`

2642 `cgi_hidden kwd "@week"`

2643 `cgi_hidden stage searchart`

2644 `cgi_submit "一週間のまとめ"`

2645 </form>

2646 EOF

2647 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2648 colstate="state:稼動状態:frozen=rowclass=凍結"

2649 DT_CHLD=article:blogid \

2650 DT_QOWNER="$qgrp" \

2651 DT_VIEW=replyblog dumptable html blog \

2652 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2653

2654 getgname="(select gname from grp where rowid=$rowid)"

2655 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2656 cm="?commission+$rowid"

2657 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2658 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2659 # team list

2660 hexteams=`hexteams "$grp"`

2661 if [ -n "$hexteams" ]; then

2662 echo "チーム一覧" | html h2

2663 echo '<div class="dumptable"><table class="b">'

2664 sq $db -html -header<<-EOF

2665 SELECT val TEAM,

2666 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2667 MEMBERS

2668 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2669 EOF

2670 echo '</table></div>'

2671 fi

2672 }

2673 grp_getbodyclass() {

2674 # Get css class name for document.

2675 # `moderated' for moderated groups

2676 # `ismember' for groups where user belongs

2677 # $1=GroupName (w/o quote)

2678 # $user=userNameCurrentlyLogin

2679 ## err grp_getbodyclass: 1="$1"

2680 qgrp=`sqlquote "$1"`

2681 query<<-EOF

2682 SELECT coalesce(

2683 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2684 'open')

2685 ||

2686 CASE WHEN '$user'

2687 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2688 THEN ' ismember'

2689 ELSE ''

2690 END;

2691 EOF

2692 }

2693 grpaction() { # $1=group-rowid

2694 err GRP_ACTION:IN

2695 grid=${1:-`getpar grp`}

2696 grp=`getgroupbyid "$grid"`

2697 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2698 if [ -z "$grp" ]; then

2699 echo "無効な指定です。" | html p; return

2700 fi

2701 if ! ismember $user "$grp"; then

2702 echo "加入者のみに許可された操作です。" | html p; return

2703 fi

2704 echo "グループ $grp 個別選択操作" \

2705 | _m4 -D_TITLE_="syscmd(\`cat')" \

2706 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2707 $layout/html.m4.html

2708

2709 isowner=""

2710 isgrpowner "$user" "$grp" && isowner="yes"

2711 usel=`getpar usel`

2712 if [ -n "$usel" ]; then

2713 uids=$(echo `echo $usel`|tr ' ' ',')

2714 ## err grpaction-1: grp=$grp, `echo $sql`

2715 text=`getpar text|tr -d '\r'`

2716

2717 rm=`getpar rm` cfm=`getpar confirm`

2718 ## err rm=$rm cfm=$cfm

2719 if [ x"$rm" = x"yes" ]; then

2720 if [ "$isowner" ]; then

2721 if [ x"$rm$cfm" = x"yesyes" ]; then

2722 # Eliminate

2723 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2724 for tbl in grp_mem grp_mem_s grp_mem_m; do

2725 sql="delete from $tbl $cond;"

2726 # echo "sql=$sql"

2727 query "$sql"

2728 err rmGRPuser "$sql"

2729 done

2730 num=`query "select count(*) from user where rowid in ($uids);"`

2731 #err num=$num

2732 if [ 0$num -gt 0 ]; then

2733 sql="select coalesce(b.val,a.name) from user a left join \

2734 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2735 # err `echo "$sql"`

2736 html pre<<EOF

2737 以下の${num}名のグループ $grp 登録を解除しました。

2738 `query "$sql"`

2739 EOF

2740 fi

2741 else

2742 echo "確認のチェックがないのでやめておきます。" | html p

2743 return

2744 fi

2745 else # not Group Owner

2746 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2747 return

2748 fi

2749 cat<<EOF

2750

2751 EOF

2752 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2753 if [ -z "$text" ]; then # if msg is empty

2754 echo "なにかメッセージを..." | html p

2755 return 0

2756 fi

2757 gecos=`gecos $user`

2758 safegc=`echo "$gecos" | tr -d '<>@'`

2759 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2760 fromad=`myemail4group "$grp"`

2761 ###mail_from="$safegc <$fromad>"

2762 mail_from="$safegc <$user>" # TEST: 2020/5/13

2763 test -n `getpar sender` &&

2764 export SENDER=$user # TEST: 2020/5/15

2765 replyto=$fromad

2766

2767 ## Start parse of attachment files

2768 if [ -n "`getpar email`" ]; then

2769 ar=`getpar supprcpt`

2770 if [ -n "$ar" ]; then

2771 for a in $ar; do

2772 if checkdomain "$a"; then

2773 supprcpt="$supprcpt $a"

2774 else

2775 err "SupprcptErr=[$a] by $user"

2776 removercpt="$removercpt $a"

2777 fi

2778 done

2779 fi

2780 subj=`getpar subject`

2781 afiles=""

2782 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2783 do

2784 f=$tmpd/$fn

2785 if [ -s $f ]; then

2786 afiles=$afiles"${afiles:+ }$f"

2787 fi

2788 done

2789 else

2790 preface=$(cat <<-EOF

2791 $url

2792 のグループ「$grp」のメンバーである $gecos さんから、

2793 あなた宛のメッセージです。

2794 ----------------------------------------------------------

2795 EOF

2796 )

2797 fi

2798 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2799 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2800 subj="${subj:-$gecos さんからのメッセージ}"

2801 REPLYTO=$replyto

2802 MAIL_FROM=$mail_from

2803 export REPLYTO SMAIL_TO MAIL_FROM

2804 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2805 for r in $rcpts; do

2806 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2807 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2808 else

2809 # Show sender and rcpts address for guest

2810 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2811 fi

2812 if [ -n "$afiles" ];then

2813 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2814 else

2815 smail "$r" "$subj"

2816 fi <<EOF

2817 ${preface:+$preface$nl}$text

2818 EOF

2819 done

2820 if [ $? = 0 ]; then

2821 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2822 sql="select coalesce(b.val, a.name) from

2823 (select name from user where rowid in ($uids)) a

2824 left join user_s b on a.name=b.name and b.key='gecos';"

2825 html pre<<EOF

2826 `query "$sql"`

2827 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2828 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2829 EOF

2830 err SendDone: `echo $sql`

2831 fi

2832 elif [ x"$rm" = x"commission" ]; then

2833 grp_reg_adm $grid $usel

2834 elif [ x"$rm" = x"addteam" ]; then

2835 team=`getpar team|sed "s/'/''/g"` # for single quotation

2836 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2837 if [ x"$team" != x"$newteam" ]; then

2838 echo "チーム名に使えない文字を除去しました" | html p

2839 team=$newteam

2840 fi

2841 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2842 cat<<-EOF | html p

2843 有効なチーム名を入力してください。

2844 カンマだけ、「なし」という名前は使えません。

2845 EOF

2846 echo "有効なチーム名を入力してください。" | html p

2847 else

2848 grp_add_team $grid "$team" $usel

2849 fi

2850 elif [ x"$rm" = x"rmteam" ]; then

2851 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2852 rmteam=`getpar rmteam|sed "s/'/''/g"`

2853 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2854 gname='$grp' AND user='$user' AND key='team'\

2855 AND val='$rmteam';\"`" ]; then

2856 grp_rm_team $grid "$rmteam" $usel

2857 else

2858 echo "所属していないチームの除去操作はできません。"|html p

2859 fi

2860 else

2861 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2862 fi

2863 fi

2864 fi

2865 # POST count summary

2866 from=`getpar from`; to=`getpar to`

2867 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2868 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2869 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2870 # New entry

2871 sql="WITH mems AS (

2872 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2873 ON gm.user=g.name

2874 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2875 ), target_article AS (

2876 SELECT id FROM article_s

2877 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2878 ), posts AS (

2879 SELECT author, count(author) post

2880 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2881 WHERE blogid IN (SELECT id FROM blog_s

2882 WHERE key='owner'

2883 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2884 AND key='text'

2885 GROUP BY author

2886 ), teams AS (

2887 SELECT user, group_concat(val, ', ') team

2888 FROM grp_mem_m

2889 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2890 AND key='team'

2891 GROUP BY user

2892 ), user_post AS (

2893 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2894 FROM mems m LEFT JOIN posts

2895 ON m.name=posts.author

2896 GROUP by m.rowid

2897 )

2898 SELECT

2899 CASE

2900 WHEN (SELECT user FROM grp_adm

2901 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2902 AND user=up.name) IS NOT NULL

2903 then 'k'

2904 ELSE ''

2905 END || rowid || ',' || gecos NAME,

2906 post POST, team _TEAM_

2907 FROM user_post up LEFT JOIN teams t

2908 ON up.name=t.user

2909 ORDER BY gecos;"

2910 ## err grpaction: "`echo \"$sql\"`"

2911 tf=$tmpd/title.$$

2912 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2913 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2914 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2915 for=\"cmadmin\">管理者委任</label>

2916 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2917 </p></div>"

2918 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2919 title=\"Shortcut: g${nl}Remove from the Group\"

2920 for=\"conf\">GRP登録解除</label>

2921 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2922 <p>この操作による通知は本人に行きません。

2923 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2924 </div>"

2925 # Get team list to which current user belongs into $hexteams

2926 allhexteams=$(hexteams "$grp")

2927 if [ -n "$isowner" ]; then

2928 myhexteams="$allhexteams" # admin can remove all teams' attr

2929 else

2930 myhexteams=$(hexteams "$grp" "$user")

2931 fi

2932 if [ -n "$myhexteams" ]; then

2933 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2934 title=\"Shortcut: s${nl}Strip a team tag from\"

2935 for=\"cmrmteam\">チーム属性除去</label>

2936 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2937 を除去します: `cgi_checkbox teamconfirm yes` 確認

2938 <p>この操作による通知は本人に行きません。

2939 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2940 </div>

2941 "

2942 fi

2943 stf=$tmpd/selteam.$$

2944 cgi_select_h selteam "5445414d" $allhexteams > $stf

2945 b1='<label> <input type="checkbox" name="usel" value="'

2946 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2947 br='<span id="reverse" title="Reverse Selection"></span>'

2948 #b2='"> <span>' b3='</span></label>'

2949 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2950 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2951 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2952 cgi_form grpaction<<EOF \

2953 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2954 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2955 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2956 | _m4 -D_TITLE_="spaste(\`$tf')" \

2957 -D_SUBTITLE_="チェック後操作ボタン" \

2958 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2959 $layout/form+dump.m4.html \

2960 | _m4 -D_TEAM_="spaste(\`$stf')"

2961 <p>下でチェックした人を対象として:</p>

2962 <div class="foldtabs">

2963 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2964 title="Shortcut: a${nl}Add a team tag to"

2965 for="cmteam">同じチーム属性を付与</label>

2966 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2967 `cgi_datalist_h teams $allhexteams`

2968 </div>

2969 ${rmteammsg}

2970 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2971 title="Shortcut: d${nl}DirectMail to"

2972 for="sendmsg" title="Direct Message">DM送信</label>

2973 <div>

2974 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2975 title="Using email format">email書式を使う</label>

2976 <div class="folded">

2977 <table>

2978 <tr><td>From: </td><td>$user</td></tr>

2979 <tr><td>このFrom:で送る</td>

2980 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2981 </td></tr>

2982 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2983 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2984 <tr><td>ファイル添付: </td>

2985 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2986 </table>

2987 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2988 </div>

2989 <div>本文:`cgi_textarea text "" cols=72`

2990 </div>

2991 </div>

2992 ${isowner:+$cmmsg$excmsg}

2993 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2994 </div>

2995 <h4>$grp 参加者一覧</h4>$fromtonote

2996 <table class="td2r thl">

2997 `sq $db -header -html "$sql"`

2998 </table>

2999 `cgi_hidden grp $grid`

3000 `cgi_hidden myuid $myuid id="myuid"`

3001 EOF

3002 }

3003 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3004 # Create TEMPORARY VIEW

3005 query<<EOF

3006 CREATE TEMPORARY VIEW writeusers AS

3007 SELECT DISTINCT author FROM article

3008 WHERE id in (

3009 select id from article where blogid=(select id from blog where rowid=$1)

3010 );

3011 CREATE TEMPORARY VIEW movablegroups AS

3012 SELECT g.rowid growid , g.gname

3013 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3014 ON grp.gname=gm.gname -- そのユーザが属している

3015 AND user='$user') g -- グループに絞る

3016 WHERE (SELECT author FROM writeusers

3017 EXCEPT

3018 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3019 IS NULL;

3020 $2

3021 EOF

3022 }

3023 sql4readableblogs() {

3024 # Create view of blogs that can be readable to $user

3025 # Blog is readable when:

3026 # 1: blog owner is an user

3027 # 2: else, 2.1: owner-group where the $user belongs

3028 # 2.2: else, owner-group is not moderated

3029 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3030 cat<<EOF ## | tee tmp/sql.out

3031 CREATE TEMPORARY VIEW readableblogs AS

3032 SELECT blog.rowid rid, id, author

3033 FROM blog

3034 NATURAL JOIN

3035 (SELECT id,

3036 max(CASE key WHEN 'owner' THEN val END) owner,

3037 max(CASE key WHEN 'mode' THEN val END) mode

3038 FROM blog_s GROUP by id) bs

3039 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3040 THEN 1 -- blog owner is an user, READABLE

3041 WHEN (SELECT val FROM grp_s

3042 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3043 AND

3044 (SELECT user FROM grp_mem

3045 WHERE gname=bs.owner AND user='$user') IS NULL

3046 THEN 0

3047 WHEN mode IN ('quiz', 'enquete')

3048 THEN 0 -- "quiz" mode blog cannot be searched

3049 ELSE 1

3050 END;

3051 EOF

3052 }

3053 editheading() { # $1=rowid-of-heading

3054 rowid=${1%%[!A-Z0-9a-z_]*}

3055 if [ -z "$rowid" ]; then

3056 echo "話題番号が未指定です。" | html p

3057 return

3058 fi

3059 owner=`getvalbyid blog owner $rowid`

3060 title=`getvalbyid blog title $rowid`

3061 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3062 | _m4 -D_TITLE_="修正" \

3063 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3064 -D_BLOGS_="" -D_DUMPTABLE_="" \

3065 -D_FORM_="syscmd(\`cat')" \

3066 $layout/html.m4.html $layout/form+dump.m4.html

3067 # Move to group

3068 if isuser "$owner"; then

3069 crview4article $rowid

3070 n=`query "SELECT count(*) FROM writeusers;"`

3071 ## err N=$n

3072 if [ $((n)) -gt 0 ]; then

3073 ## err ROWID=$rowid

3074 sql="SELECT growid || ':' || gname FROM movablegroups;"

3075 cat<<-EOF

3076 <div class="fold">

3077 `cgi_checkbox mv send id="mv"`<label

3078 for="mv">この話題をグループ所有に移動する</label>

3079 <div>

3080 <form action="?mvart" method="POST" enctype="multipart/form-data">

3081 移動先グループ:

3082 <select name="mv2grp">

3083 EOF

3084 query ".mode html"

3085 query<<-EOF |

3086 $sql

3087 .mode list

3088 EOF

3089 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3090 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3091 cat<<-EOF

3092 </select>

3093 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3094 そのグループに加入しているものに限られます)</p>

3095 <p>`cgi_checkbox cfm yes`<label>確認

3096 (この操作は元に戻すことができません)</label></p>

3097 `cgi_hidden blogrowid $rowid`

3098 `cgi_submit 移動`

3099 `cgi_reset Reset`

3100 </form>

3101 </div>

3102 </div>

3103 EOF

3104 fi

3105 # end of isuser "$owner"

3106 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3107 [ -n "$hexteams" ];}; then

3108 none="`echo なし|hexize`"

3109 cat<<-EOF

3110 <div class="fold">

3111 `cgi_checkbox mv2team send id="mv2team"`<label

3112 for="mv2team">この話題を以下のチームのものにする</label>

3113 <div><p>現在の所属チーム設定:

3114 `query "SELECT

3115 coalesce((SELECT val FROM blog_s

3116 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3117 AND key='team'),

3118 ':なし');"`</p>

3119 <form action="?mvart" method="POST" enctype="multipart/form-data">

3120 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3121 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3122 `cgi_hidden blogrowid $rowid`<br>

3123 `cgi_submit 移動`

3124 `cgi_reset Reset`

3125 </form></div></div>

3126 EOF

3127 fi

3128 }

3129 mvart() { # move diary to some group or team

3130 # or move blog of group to team which belong to the group

3131 blogrowid=`getpar blogrowid`

3132 cfm=`getpar cfm`

3133 ##### echo move blog:$blogrowid to $mv2grp | html p

3134 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3135 . ./s4-blog.sh

3136 if [ -z "$blogrowid" ]; then

3137 echo "無効な指定です(mvart)。" | html p

3138 return

3139 elif [ x"$cfm" != x"yes" ]; then

3140 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3141 elif { mv2grp=`getpar mv2grp`

3142 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3143 [ -n "$mv2grp" ]; }; then

3144 crview4article $blogrowid

3145 ########## TRANSACTION BEGIN

3146 query "BEGIN;"

3147 n=`query "SELECT count(*) FROM writeusers;"`

3148 ## err Nwriteuser=$n

3149 if [ $((n)) -gt 0 ]; then

3150 query<<-EOF

3151 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3152 WHERE key='owner'

3153 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3154 AND $mv2grp IN (SELECT growid FROM movablegroups);

3155 EOF

3156 fi

3157 query "END;"

3158 ########## TRANSACTION END

3159 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3160 [ -n "$mv2team" ];}; then

3161 # blog owner can move it to ANY team

3162 case "$mv2team" in

3163 'なし')

3164 cat<<-EOF

3165 DELETE FROM blog_s

3166 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3167 AND key='team';

3168 EOF

3169 ;;

3170 "") ;;

3171 *)cat<<-EOF

3172 BEGIN;

3173 REPLACE INTO blog_s(id, key, val)

3174 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3175 'team', '$mv2team');

3176 REPLACE INTO blog_s(id, key, val)

3177 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3178 'notify', 'all'); -- Change notify to all

3179 END;

3180 EOF

3181 esac | query

3182 fi

3183 blog_reply $blogrowid

3184 echo yes | html p

3185 }

3186 editart() { # $1=article-rowid $2=blogrowid

3187 rowid=${1%%[!A-Z0-9a-z_]*}

3188 blogrowid=${2%%[!A-Z0-9a-z_]*}

3189 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3190 echo "表示する記事番号が未指定です。" | html p

3191 return

3192 fi

3193 owner=`getvalbyid blog owner $blogrowid`

3194 title=`getvalbyid blog title $blogrowid`

3195 author=`getvalbyid article author $rowid`

3196 ## err EDITart: owner=$owner, author=$author

3197 if isgrpowner "$user" "$owner"; then

3198 : EDIT OK

3199 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3200 echo "本人か所有者しか編集できません." | html p

3201 return

3202 fi

3203 aid=`query "select id from article where rowid=$rowid;"`

3204 tmpout=$tmpd/editart.$$.out

3205 GF_ACTION="?replyblog+$blogrowid#$aid" \

3206 edittable $formdir/article.def article $rowid \

3207 > $tmpout

3208 rm -f /tmp/editart.out

3209 # Cannot use pipelining to m4 with genform() because of stdin stack

3210 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3211 -D_FORM_="syscmd(cat $tmpout)" \

3212 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3213 -D_BLOGS_= -D_DUMPTABLE_= \

3214 $layout/html.m4.html $layout/form+dump.m4.html

3215 }

3216 send2mem() {

3217 rowid=`getpar grp`

3218 rowid=${rowid%%[!0-9]*} # Cleaning

3219 if [ -z "$rowid" ]; then

3220 echo "グループが未指定です。" | html p

3221 return

3222 fi

3223 message=`getpar message`

3224 if [ -z "$message" ]; then

3225 echo "文章を入れてください。" | html p

3226 return

3227 fi

3228 grp=`getgroupbyid $rowid`

3229 members=`collectemail $grp`

3230 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3231 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3232 mailfrom="`gecos "$user"` <$mailfrom>"

3233 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3234 msg=$(cat<<-EOF

3235 $urlbase?grp+$rowid

3236 グループ $grp に所属する

3237 `gecos $user` さんよりメッセージ:

3238

3239 $message

3240 EOF

3241 )

3242 # smail rcpt subj (file)

3243 for m in $members; do

3244 echo "$msg" |

3245 MAIL_FROM=$mailfrom \

3246 SENDER=$noreply \

3247 REPLYTO=$mailfrom \

3248 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3249 smail "$m" "$sj"

3250 done

3251 cat<<EOF

3252 <p>以下のユーザに送信しました。</p>

3253 <pre>

3254 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3255 </pre>

3256 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3257 EOF

3258 }

3259 joingrpadmit() {

3260 # $1=yes/no $2=session-key

3261 if [ -z "$2" ]; then

3262 echo "bye bye" | html p; return

3263 fi

3264 t_usr=`session=$2 getpar user`

3265 t_grp=`session=$2 getpar group`

3266 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3267 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3268 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3269 echo "無効な加入依頼です。" | html p

3270 echo "有効期限が切れたか、

3271 他の管理者がいる場合は処理済みの可能性があります。" | html p

3272 return

3273 fi

3274 if ! isgrpowner "$user" "$t_grp"; then

3275 echo "グループ管理者のみの機能です。" | html p; return

3276 fi

3277 case $1 in

3278 yes) joingrp "$t_grp" "$t_usr" yes ;;

3279 no) joingrp "$t_grp" "$t_usr" no ;;

3280 *)

3281 echo "無効な指定です($1)。" | html p

3282 return ;;

3283 esac

3284 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3285 rcpts="`getgroupadminmails $t_grp` $user"

3286 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3287 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3288 に

3289 $t_usr

3290 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3291 しました。"

3292 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3293 query "delete from session where id='$2';"

3294 echo "$body" | html p

3295 }

3296

3297 joingrprequest() {

3298 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3299 jss="joingrp-`date +%s`-`genrandom 12`"

3300 addsession $jss +${memoplimitdays}days

3301 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3302 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3303 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3304 $url

3305 $user さんから

3306 グループ $1

3307 に加入依頼がありました。

3308

3309 承認する:

3310 $urlbase?joingrpadmit+yes+$jss

3311

3312 白紙に戻す:

3313 $urlbase?joingrpadmit+no+$jss

3314 EOF

3315 echo "管理者に加入依頼を出しました。

3316 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3317 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3318 }

3319 joingrp() {

3320 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3321 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3322 if isgrpowner "$user" "$1"; then

3323 isowner="yes"

3324 elif [ -n "$5" ]; then

3325 isowner="yes"

3326 else

3327 isowner=""

3328 fi

3329 ## err jg:isgrpowner: isowner="$isowner"

3330 if [ -n "$isowner" ]; then

3331 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3332 elif [ x"$2" != x"$user" ]; then # if user is not login user

3333 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3334 return

3335 elif [ x"$3" = x"no" ]; then

3336 : # Do not pursue those who leave

3337 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3338 : # Member can change own email address for the joining moderated group

3339 else # adding user is $user itself

3340 case `getgroupattr $1 regmode` in

3341 moderated)

3342 joingrprequest "$@" # Request only

3343 return

3344 ;;

3345 *)

3346 ;;

3347 esac

3348 fi

3349 qgname=`sqlquote "$1"`

3350 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3351 cond="where gname=$qgname and user='$2'"

3352 if [ x"$3" = x"yes" ]; then

3353 query "replace into grp_mem values($qgname, '$2');"

3354 # Notify joingrp to admin

3355 action="に加入しました。"

3356 if [ -n "$4" ]; then

3357 if msg=`emaildomaincheck "$4"`; then

3358 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3359 'string', '$4', NULL);"

3360 else

3361 echo $msg

3362 fi

3363 else

3364 query "delete from grp_mem_s $cond and key='email';"

3365 fi

3366 if [ -n "$5" ]; then # as ADMIN

3367 # Coming here means newly created group

3368 sql="select case\

3369 when (select count(*) from grp_mem where gname=$qgname)=1\

3370 then (select user from grp_mem\

3371 where gname=$qgname and user='$user')\

3372 else '' end; "

3373 err NewGrpChk: $sql

3374 if [ -n "`query \"$sql\"`" ]; then

3375 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3376 query "replace into grp_adm values($qgname, '$user');"

3377 fi

3378 fi

3379 else

3380 query "begin;

3381 delete from grp_mem $cond;

3382 delete from grp_mem_s $cond;

3383 delete from grp_mem_m $cond;

3384 delete from grp_adm $cond;

3385 delete from grp_adm_s $cond;

3386 delete from grp_adm_m $cond;

3387 end;"

3388 action="から脱退しました。"

3389 fi

3390 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3391 $url?grp+$grid

3392 $user (`gecos $user`)さんが

3393 グループ $1

3394 $action

3395 EOF

3396 }

3397 grp_add_team() (

3398 # $1=grp-rowid $2=team $3...=user-rowid(s)

3399 grp=`getgroupbyid $1`

3400 team=$2; shift; shift

3401 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3402 { echo "BEGIN;"

3403 for user; do

3404 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3405 '$grp',\

3406 (SELECT name FROM user WHERE rowid=$user),\

3407 'team', 'string', '$team');"

3408 done

3409 echo "END;"

3410 } | query

3411 )

3412 grp_rm_team() (

3413 # $1=grp-rowid $2=team $3...=user-rowid(s)

3414 grid=$1

3415 qgrp=$(sqlquote "`getgroupbyid $grid`")

3416 team=$2; shift; shift

3417 [ -z "$grid" -o -z "$team" ] && return

3418 { echo "BEGIN;"

3419 for user; do

3420 echo "DELETE FROM grp_mem_m\

3421 WHERE gname=$qgrp \

3422 AND user=(SELECT name FROM user WHERE rowid=$user)\

3423 AND key='team' AND val='$team';"

3424 done

3425 cat<<-EOF

3426 DELETE FROM blog_s

3427 WHERE rowid=(

3428 SELECT rowid

3429 FROM blog_s a

3430 WHERE key='team'

3431 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3432 AND NOT EXISTS (SELECT * FROM grp_mem_m

3433 WHERE key='team' AND val=a.val -- a.val=team

3434 AND gname = (SELECT val FROM blog_s b

3435 WHERE a.id=b.id AND key='owner')

3436 ));

3437 EOF

3438

3439 echo "END;"

3440 } | query

3441 )

3442 grp_reg_adm() {

3443 # $1=grp-rowid $2...=user-rowid

3444 grid=$1

3445 grp=`getgroupbyid "$1"`

3446 if [ -z "$grp" ]; then

3447 echo "無効なグループIDです" | html p; return

3448 fi

3449 if ! isgrpowner "$user" "$grp"; then

3450 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3451 fi

3452 shift

3453 for urid; do

3454 newadm=`query "select name from user where rowid=$urid;"`

3455 if [ -z "$newadm" ]; then

3456 echo "指定ユーザIDがおかしいようです。" | html p; return

3457 fi

3458 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3459 err ismember $newadm $grp

3460 if ismember $newadm "$grp"; then

3461 # OK, go ahead

3462 getgname="(select gname from grp where rowid=$grid)"

3463 query "replace into grp_adm values($getgname, '$newadm');"

3464 # confirm insertion

3465 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3466 if [ -n "`query \"$sql;\"`" ]; then

3467 echo "追加完了: $newadm" | html p

3468 else

3469 echo "追加失敗($1 $urid)" | html p

3470 fi

3471 fi

3472 showgroup $grid

3473 done

3474 }

3475 dt_rowhack() {

3476 # From: <TR>

3477 # ....

3478 # <TD>rowclass=foo</TD>

3479 # </TR>

3480 # To: <TR class="foo">....<TD>foo</TD></TR>

3481 sed -e '

3482 /^<TR>/ {

3483 :loop

3484 s/\n//

3485 N

3486 /<\/TR>/ {

3487 s/\n//

3488 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3489 n

3490 }

3491 $q

3492 b loop

3493 }'

3494 }

3495 dumptable() {

3496 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3497 # textのフィールドだけ全てダンプにしたほうがいいか

3498 # $DT_VIEW sets link

3499 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3500 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3501 VIEW=${DT_VIEW-replyblog}

3502 if [ -n "$VIEW" ]; then

3503 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3504 fi

3505 sqlfile=$tmpd/dump.sql

3506 : > $sqlfile # ensure to be empty

3507 printf '.mode html\n.header 1\n' > $sqlfile

3508 # $DT_CHLD=ChildTable:BindColumn

3509 if [ -n "$DT_CHLD" ]; then

3510 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3511 cat<<-EOF >> $sqlfile

3512 -- presql

3513 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3514 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3515 EOF

3516 # Speed up counting of new articles

3517 cat<<-EOF >> $sqlfile

3518 -- presql2

3519 DROP TABLE IF EXISTS _counts;

3520 CREATE TEMPORARY TABLE _counts AS

3521 SELECT $_i, count($_i) cnt

3522 FROM $_t GROUP BY $_i;

3523 /* Prepare NEW count table */

3524 CREATE TEMPORARY TABLE _target AS

3525 SELECT b.rowid trowid, b.id

3526 FROM "$2" b JOIN "$2_s" s

3527 ON b.id=s.id AND s.key='owner'

3528 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3529

3530 DROP TABLE IF EXISTS _children;

3531 CREATE TEMPORARY TABLE _children AS

3532 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3533 FROM (SELECT t.trowid, t.id $_i, a.id

3534 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3535 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3536

3537 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3538 DROP TABLE IF EXISTS _news;

3539 DROP VIEW IF EXISTS _news;

3540

3541 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3542 -- INSERT INTO _news

3543 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3544 CREATE TEMPORARY VIEW _news AS

3545 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3546 FROM (SELECT DISTINCT id FROM _target)

3547 a LEFT JOIN

3548 (SELECT $_i, count(ctime) newcnt

3549 FROM _children x

3550 WHERE ctime > coalesce((SELECT time from myacclog

3551 WHERE tblrowid=x.trowid),

3552 '1970-01-01')

3553 GROUP BY $_i) b

3554 ON a.id=b.$_i;

3555 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3556 EOF

3557 # REMOVE next line until 2019/5/1

3558 cntall="(select count($_i) from $_t where $_i=a.id)"

3559 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3560 # REMOVE next assignment until 2019/5/1

3561 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3562 and id in (select id from $_t where $_i=a.id) \

3563 and val > coalesce((select time from myacclog where \

3564 tblrowid=a.rowid),\

3565 '1970-01-01'))"

3566 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3567 cnt="$cntnew as '新着', $cntall as '総数',"

3568 dt_class=" td2r td3r dumpblogs"

3569 fi

3570 # Construct join expression

3571 eav="" scols=""

3572 pk=`gettblpkey $2`

3573 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3574 substr=${substr:-%s}

3575 for col in ${3:-`gettbl_s_cols $2`}; do

3576 valvar=val

3577 case $col in

3578 gecos) scols="$scols${scols:+, }${col#}"

3579 continue ;; # built-in column name

3580 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3581 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3582 case "$as" in

3583 *:*=*) cnd=${as#*:}

3584 h=${cnd%%=*} v=${cnd#*=}

3585 h=`sqlquotestr "$h"`

3586 v=`sqlquotestr "$v"`

3587 valvar="CASE val WHEN $h THEN $v END"

3588 as=${as%%:*} ;;

3589 esac

3590 ;;

3591 *) as=${col} ;;

3592 esac

3593 ss=`printf "$substr" "$valvar"`

3594 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3595 scols="$scols${scols:+, }b.$as"

3596 done

3597 #case author when '$user' then a.rowid else '---' end as ID,

3598 if [ -n "$DT_SQL" ]; then

3599 echo "$DT_SQL"

3600 else

3601 cat<<-EOF

3602 SELECT a.rowid as LINK, $cnt $scols

3603 FROM $2 a LEFT JOIN

3604 (SELECT $pk,$eav,

3605 max(CASE key

3606 WHEN 'owner'

3607 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3608 as gecos

3609 FROM ${2}_s c GROUP BY $pk)

3610 b ON a.$pk=b.$pk $4;

3611 EOF

3612 fi >> $sqlfile

3613 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3614 sqlog<<-EOF

3615 *** SQL-file: $sqlfile ***

3616 `cat $sqlfile`

3617 EOF

3618 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3619 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3620 cat $sqlfile >> tmp/sql

3621 # query ".read $sqlfile" > $tmpd/foo

3622 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3623 cp $tmpd/foo tmp/

3624 err "----- `gdate +%FT%T.%3N` ------------555555"

3625 ## $ddd LINE exists at the end of this function

3626 fi

3627 printf '.mode list\n.header 0\n' >> $sqlfile

3628 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3629 <div>

3630 <div class="dumptable">

3631 <table class="b$dt_class">

3632 `query ".read $sqlfile"`

3633 </table>

3634 </div>

3635 </div>

3636 EOF

3637 ### `query ".read $sqlfile"`

3638 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3639

3640 ## REMOVE THIS!

3641 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3642 }

3643

3644 par2table() (

3645 # copy current parameters of par into destination table

3646 # $1=definition-file

3647 # Using $user and $session

3648 # Return value:

3649 # 0: Stored successfully

3650 # 1: Insufficient fillings

3651 # 2: No permission to modify the record

3652 # 3: Invalid rowid

3653 # 4: SUCCESS to delete

3654 # 5: Stop deletion for lack of confirm check

3655 # 6: Password length too short

3656 # 7: Password mismatch

3657 # 8: Old password incorrect

3658 # 9: Duplicated post

3659 rowid=`getpar rowid`

3660 if [ ! -e $1 ]; then

3661 echo "テーブル定義ファイルが見付かりません" | html p

3662 exit 1

3663 fi

3664 tbl=${1%.def}

3665 tbl=${tbl##*/}

3666 if [ -n "$rowid" ]; then # Modify existing entry

3667 if [ x"$tbl" = x"user" ]; then

3668 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3669 elif [ x"$tbl" = x"grp" ]; then

3670 sql="select gname from $tbl where rowid=$rowid;"

3671 ##err p2t:grp:q $sql

3672 isgrpowner "$user" "`query $sql`" && rowowner=$user

3673 elif [ x"$tbl" = x"blog" ]; then

3674 # Check if owner in blog_s

3675 blogowner=`getvalbyid blog owner "$rowid"`

3676 if isgrpowner "$user" "$blogowner"; then

3677 rowowner=$user

3678 else

3679 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3680 fi

3681 else

3682 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3683 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3684 fi

3685 ### err rowowner=$rowowner

3686 if [ x"$user" != x"$rowowner" ]; then

3687 echo "他人のレコードはいじれないの" | html p

3688 return 2

3689 elif [ -z "$rowowner" ]; then

3690 echo "指定したレコードはないみたい" | html p

3691 return 3

3692 fi

3693 rm=`getpar rm` cfm=`getpar confirm`

3694 # Editing existent entry

3695 if [ x"$rm" = x"yes" ]; then

3696 if [ x"$rm$cfm" = x"yesyes" ]; then

3697 query "delete from $tbl where rowid=$rowid;"

3698 return 4

3699 else

3700 echo "消去確認のチェックがないので消さなかったの..." | html p

3701 return 5

3702 fi

3703 fi

3704 fi

3705

3706 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3707 if [ -n "$rowid" ]; then

3708 # Update of existing record

3709 for col in `gettblcols $tbl`; do

3710 val=`getparquote $col`

3711 [ -z "$val" ] && continue

3712 ## err query "update $tbl set $col=$val where rowid=$rowid"

3713 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3714 sql="update $tbl set $col=$val where rowid=$rowid;"

3715 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3716 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3717 ## User name cannot be changed with interface provided with this

3718 ## script. But we offer the trigger to change owner user

3719 ## of blog_s table.

3720 #err "select quote($col) from $tbl where rowid=$rowid;"

3721 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3722 cat<<-EOF | query

3723 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3724 -- should use EXCLUSIVE transaction outside of this.

3725 SAVEPOINT par2table;

3726 $sql

3727 update blog_s set val=$val

3728 where key='owner' and val=$old;

3729 RELEASE SAVEPOINT par2table;

3730 EOF

3731 ## XX: DIRTY Hack Ends here

3732 ## We should keep blog's owner as a single column which has

3733 ## foreign key constraint with primary key of grp/user.

3734 else

3735 query "$sql"

3736 fi

3737 done

3738 # Then, set up $pval for further insertion of tbl_s and tbl_m

3739 for col in `gettblpkey $tbl`; do

3740 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3741 pval="$pval${pval:+, }\"$val\""

3742 done

3743 else

3744 # New entry

3745 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3746 # Avoid empty repost of article.

3747 if [ x"$tbl" = x"article" ]; then

3748 # If rowid is empty and ID exists in article-table, that is REPOST!

3749 aid=`getpar id`

3750 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3751 if [ -n "$xaid" ]; then

3752 # REPOST of article

3753 html p <<-EOF

3754 書き込み直後のリロードなので上書きを回避します。

3755 最新記事は末尾の「再読み込み」ボタンから見てください。

3756 EOF

3757 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3758 return 9 # STOP Duplicated posting

3759 fi

3760 fi

3761 # Generate values() for primary keys

3762 for col in `gettblpkey $tbl`; do

3763 # Genuine primary keys for _m and _s

3764 val=`getvalquote $tbl $col`

3765 [ -z "$val" ] && continue

3766 pval="$pval${pval:+, }$val"

3767 done

3768 ##err pval=$pval

3769 for col in `gettblfkey $tbl`; do

3770 # args for values() to insertion into master table

3771 val=`getvalquote $tbl $col`

3772 [ -z "$val" ] && continue

3773 formaster=$formaster"${formaster:+, }$val"

3774 done

3775 formaster="$pval${formaster:+, }$formaster"

3776 ## err formaster=$formaster

3777 if [ -z "$formaster" ]; then

3778 echo "項目を全て埋めてください" | html pre

3779 return 1

3780 fi

3781 ## err "replace into $tbl values($formaster);"

3782 query "replace into $tbl values($formaster);"

3783 ## Insertion to master table, done

3784 fi

3785

3786 transaction=$tmpd/sqlfile.sql; touch $transaction

3787 for kt in s m; do

3788 tb2=${tbl}_$kt

3789 for col in `gettbl_${kt}_cols $tbl`; do

3790 ptype=`getpartype $col "limit 1"`

3791

3792 # First, check update of existing entries in _m

3793 if [ $kt = m ]; then

3794 # sessID|address.1.22|string|Somewhere-x.y.z

3795 sql=""

3796 ##err dots from query "select var from par where var like '$col.%';"

3797 for v in `query "select var from par where var like '$col.%';"`; do

3798 # v=address.1.22

3799 st_rowid=${v##*.}

3800 origcol=${v%%.*} # original column derived from

3801 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3802 ##case `getpartype $v` in

3803 ## err CASE `gettbl_coltype $tbl/$origcol` in

3804 ## err edit flag = `getpar action.$v`

3805 case `getpar action.$v` in

3806 rm)

3807 if [ x`getpar confirm.$v` = x"yes" ]; then

3808 newsql="delete from $tb2"

3809 else

3810 echo "削除確認未チェック" | html p

3811 fi ;;

3812 edit)

3813 case `gettbl_coltype $tbl/$origcol` in

3814 image|document|binary)

3815 file=$tmpd/`getparfilename $v`

3816 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3817 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3818 continue

3819 fi

3820 ## err type=file=$file

3821 [ -z "$file" ] && continue

3822 bn=`sqlquotestr "${file##*/}"`

3823 bin="X'"$(hexize "$file")"'"

3824 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3825 type=\"file:$ct\"

3826 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3827 cachedir=`getcachedir "$tbl/$rowid"`

3828 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3829 rm -rf $cachedir

3830 ;;

3831 *)

3832 newsql="update $tb2 set val=(select val from par where var \

3833 like '$col.%.$st_rowid')"

3834 ;;

3835 esac

3836 ;;

3837 *) # maybe "keep", do not modify value

3838 continue

3839 ;;

3840 esac

3841 # err newsql=$newsql

3842 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3843 done

3844

3845 if [ x"$bin" = x"NULL" ]; then

3846 ## err repl:normal sql=`echo $sql`

3847 if [ -n "$transaction" ]; then

3848 cat<<-EOF >> $transaction

3849 $sql

3850 DELETE FROM $tb2 WHERE type='string' AND val='';

3851 EOF

3852 else

3853 query "$sql

3854 delete from $tb2 where type='string' and val='';"

3855 ## err repl:normal done

3856 fi

3857 else

3858 # Binary update line is TOO LONG to pipelining

3859 sqlfile="$tmpd/sqlf.$$"

3860 if [ -n "$transaction" ]; then

3861 echo "$sql" >> $transaction

3862 else

3863 echo "$sql" > $sqlfile

3864 query ".read $sqlfile"

3865 fi

3866 fi

3867 # Rest of kt==m: set multiple mode

3868 nr=`getparcount $col`

3869 else

3870 nr=1 # for kt==s, number of records is 1

3871 fi

3872

3873 i=0

3874 while [ $i -lt $nr ]; do

3875 limit="limit 1 offset $i"

3876 i=$((i+1)) # increase beforehand against continue

3877 val=`getvalquote $tbl $col "$limit"`

3878 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3879 ## err $col=$val

3880 bin=NULL

3881 ## err partype$col=`getpartype $col "$limit"`

3882 case $ptype in

3883 file) file=$tmpd/`getparfilename $col "$limit"`

3884 ## err parfile-$col=$file

3885 [ -z "$file" ] && continue

3886 bin="X'"$(hexize "$file")"'"

3887 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3888 type=\"file:$ct\" ;;

3889 "*"*) continue ;; # foreign table

3890 *) type=\"string\" ;;

3891 esac

3892 case `gettbl_coltype $tbl/$col` in

3893 password) # special care for password

3894 # name={password,pswd1,pswd2}

3895 p1=`getpar pswd1 "$limit"`

3896 if [ -z "$p1" ]; then

3897 continue # SKIP password setting, if p1 is empty

3898 else

3899 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3900 ## err pswd=$pswd

3901 if pwcheck "$pswd"; then

3902 if [ x"$p1" = x"$p2" ]; then

3903 case "$p1" in

3904 ??????????*) ;;

3905 *) echo "パスワードは10字以上にしてください。" | html p

3906 return 6;;

3907 esac

3908 val="\"`echo $p1|mypwhash`\""

3909 else

3910 echo "2つの新パスワード不一致" | html p

3911 return 7

3912 fi

3913 else

3914 echo "旧パスワード違います" | html p

3915 return 8

3916 fi

3917 fi

3918 ;;

3919 esac

3920 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3921 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3922 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3923 if [ x"$bin" = x"NULL" ]; then

3924 ## err Normal-query: `echo $sql`

3925 if [ -n "$transaction" ]; then

3926 echo "$sql" >> $transaction

3927 else

3928 query "$sql"

3929 fi

3930 else

3931 sqlfile="$tmpd/query.$$"

3932 ## err sqlfile=`ls -lF $sqlfile`

3933 if [ -n "$transaction" ]; then

3934 echo "$sql" >> $transaction

3935 else

3936 echo "$sql" > $sqlfile

3937 query ".read $sqlfile"

3938 fi

3939 fi

3940 ## err p2t done

3941 done

3942 done

3943 done

3944 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3945 -- We cannot use transaction here, because groupupdate may use it.

3946 SAVEPOINT pa2table_insert;

3947 .read $transaction

3948 RELEASE SAVEPOINT pa2table_insert;

3949 EOF

3950 rc=$?

3951 [ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag

3952 ## err "Table:$tbl update done "

3953 return $rc

3954 )

3955 genform() {

3956 # $1 = form definition file

3957 # $2, $3 (optional)= table name and ROWID

3958 # If $GF_VIEWONLY set and nonNull, output values without form

3959 # If $GF_ARGS set, use it as content-strings in the form

3960 # If $GF_OWNER set, use it as value of name="owner"

3961 # If $GF_STAGE set, use it as value of name="stage"

3962 forms="" hiddens="" rowid=$3

3963 if [ ! -e "$1" ]; then

3964 echo "そのようなデータベースはないようです($2)。" | html p

3965 return

3966 elif [ -n "$2" ]; then

3967 rec=`query "select * from $2 where rowid='$rowid';"`

3968 if [ -z "$rec" ]; then

3969 pk=`gettblpkey $2`

3970 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3971 rec=`query "select rowid from $2 where $pk='$rowid';"`

3972 rowid=$rec

3973 rec=$3

3974 fi

3975 if [ -z "$rec" ]; then

3976 echo "そんなレコードはないみたいね..." | html p

3977 return

3978 fi

3979 fi

3980 if [ -z "$GF_VIEWONLY" ]; then

3981 rm='<input id="rm" name="rm" type="checkbox"

3982 value="yes"><label for="rm">このエントリの削除</label>

3983 <span>ほんとうに消しますよ(確認)!

3984 <input name="confirm" type=checkbox value="yes">はい</span>'

3985 fi

3986 # Image Cache dir

3987 ## err genform: getcache=$2/$rowid

3988 td=`getcachedir "$2/$rowid"`

3989 while IFS=: read prompt name keytype type args; do

3990 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3991 sp="${args:+ }"

3992 form="" val=""

3993 if [ -n "$rowid" ]; then

3994 # err genform2a: Seeking for "$2.$name, type=$type"

3995 rawval=`getvalbyid $2 $name $rowid $td`

3996 val=`echo "$rawval"|htmlescape`

3997 ## err genform3a: getvalbyid $2 $name $rowid $td

3998 ## err genform3b: val="[$val]" type="$type"

3999 fi

4000 if [ -n "$GF_VIEWONLY" ]; then

4001 is_hidden "$2" "$name" && continue

4002 fi

4003 case "$type" in

4004 text*)

4005 cgiform=cgi_multi_$type

4006 if [ -s $td/$name.count -a -n "$val" ]; then

4007 form=`$cgiform $name $td`

4008 val=$(echo "$val"|

4009 while read fn; do

4010 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4011 </td></tr>$nl"

4012 done)

4013 val="<table>$nl$val$nl</table>"

4014 else

4015 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4016 form=`cgi_$type $name "$rawval" "$args"`

4017 fi

4018 ;;

4019 [Rr][Aa][Dd][Ii][Oo])

4020 fh="<label><input type=\"radio\" name=\"$name\""

4021 form="`echo $args|sed -e \

4022 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4023 ;;

4024 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4025 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4026 ;;

4027 [Ss][Ee][Ll][Ee][Cc][Tt])

4028 fh="<select name=\"$name\">$nl"

4029 form=$(for l in $args; do

4030 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4031 done)

4032 if [ -n "$val" ]; then

4033 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4034 fi

4035 form="$fh$form</select>"

4036 ;;

4037 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4038 if [ -s $td/$name.count ]; then

4039 form=`cgi_multi_file $name $td "$args"`

4040 if [ -n "$val" ]; then

4041 hrfb="$myname?showattc+$2_m"

4042 val=$(echo "$rawval" \

4043 | while read fn; do

4044 data=`percenthex "$td/$fn"`

4045 #ct=`cat $td/$fn.content-type`

4046 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4047 ri=`cat "$td/$fn.rowid"`

4048 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4049 #imgsrc="<img src=\"data:$ct,$data\">"

4050 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4051 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4052 done)

4053 fi

4054 else

4055 form="<input type=\"file\" name=\"$name\" $args>"

4056 if [ -n "$val" ]; then

4057 imgs=$(echo "$rawval"\

4058 |while read fn;do

4059 data=`percenthex "$td/$fn"`

4060 echo "<img src=\"data:image/png,$data\">$fn<br>"

4061 done)

4062 form=$form"<br>$imgs"

4063 val=$imgs # 2015-06-15

4064 else

4065 form="<input type=\"file\" name=\"$name\" $args>"

4066 fi

4067 fi

4068 ;;

4069 [Hh][Ii][Dd][Dd][Ee][Nn])

4070 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4071 args="value=\"$GF_STAGE\""

4072 fi

4073 form="<input type=\"hidden\" name=\"$name\" $args>"

4074 prompt='' # Remove prompt

4075 ;;

4076 [Aa][Uu][Tt][Hh][Oo][Rr])

4077 [ -n "$GF_VIEWONLY" ] && continue

4078 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4079 prompt="" ;;

4080 [Oo][Ww][Nn][Ee][Rr])

4081 [ -n "$GF_VIEWONLY" ] && continue

4082 val=${GF_OWNER:-$val}

4083 val=${val:-$user}

4084 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4085 prompt="" ;;

4086 [Uu][Ss][Ee][Rr])

4087 # XXX: is null $user ok?

4088 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4089 [ -n "$GF_VIEWONLY" ] && continue

4090 form="$user"

4091 ;;

4092 [Pp]assword)

4093 [ -n "$GF_VIEWONLY" ] && continue

4094 form="`cgi_passwd`"

4095 val=""

4096 ;;

4097 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4098 [ -n "$GF_VIEWONLY" ] && continue

4099 if [ -z "$rowid" ]; then

4100 val=`genserial`

4101 fi

4102 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4103 prompt="" ;;

4104 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4105 prompt=""

4106 ;;

4107 parent|path|blog*)

4108 prompt=""

4109 ;;

4110 "*"*)

4111 tail=$tail"``"

4112 continue ;;

4113 esac

4114 if [ -n "$prompt" ]; then

4115 if [ -n "${GF_VIEWONLY}" ]; then

4116 form=$val

4117 else

4118 :

4119 fi

4120 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4121 else

4122 hiddens=$hiddens$nl"$form"

4123 fi

4124 done < $1

4125 # enctype="multipart/form-data"

4126 cat<<EOF

4127 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4128 ${rowid:+$rm}

4129 <table class="b $2">

4130 $forms

4131 </table>$hiddens

4132 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4133 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4134 EOF

4135 if [ -z $GF_VIEWONLY ]; then

4136 cat<<EOF

4137 <input type="submit" name="sub" value="OK">

4138 <input type="reset" name="res" value="Reset">

4139 EOF

4140 fi

4141 cat<<EOF

4142 $GF_ARGS</form>

4143 $tail

4144 EOF

4145 }

4146 edittable() {

4147 # $1=form-def $2=table $3 rowid

4148 genform "$@"

4149 }

4150 viewtable() {

4151 GF_VIEWONLY=1 genform "$@"

4152 }

4153 showattc() {

4154 # $1=table_m $2=rowid &optional $3=RawFlag

4155 ## err \$1=$1 \$2=$2 \$3=$3

4156 if ! isfilereadable $user $1 $2; then

4157 contenttype; echo

4158 echo "このファイルは管理者のみしか見られません" | html p

4159 putfooter; exit

4160 fi

4161 idir=`umask 002; mktempd` || exit 1

4162 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4163 bin=$idir/$myname-$$.bin

4164 sql="select quote(bin) from $1 where rowid='$2';"

4165 ## err showattc: sql: $sql

4166 sq $db "$sql" | unhexize > $bin

4167 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4168 type=${tv%//*} fn=${tv#*//}

4169 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4170 ct=${type#file:}

4171 case $ct in # all text/* changed to text/plain

4172 text/*)

4173 charset=`nkf -g $bin|cut -d' ' -f1`

4174 case $charset in

4175 ASCII*) charset="" ;;

4176 esac

4177 if [ -z "$3" ]; then

4178 ct="text/html${charset:+; charset=$charset}"

4179 link="?showattc+$1+$2+raw"

4180 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4181 | sed 's,^,<span></span>,' \

4182 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4183 -D_LINK_="$link" \

4184 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4185 exit $?

4186 fi

4187 ct="text/plain${charset:+; charset=$charset}"

4188 ;;

4189 esac

4190 contenttype "$ct"

4191 echo "Content-Disposition: filename=\"$fn\""

4192 echo "Content-Length: " `cat $bin | wc -c`; echo

4193 #echo "Content-Type: " ${type#file:}; echo

4194 cat $bin

4195 }

4196 #

4197 # Some default stupid handler on CGI values

4198 #

4199 default_storedb() {

4200 # ARG: $1=table-def-file

4201 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4202 tbl=`basename $1`

4203 tbl=${tbl%.def}

4204 cols="`grep :text $1|cut -d: -f2`"

4205 col=`echo "$cols"|head -1`

4206 vcol=`getpar $col`

4207 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4208 if [ -n "$vcol" ]; then

4209 par2table $1

4210 else

4211 return 2 # No insertion occurred

4212 fi

4213 }

4214

4215 default_view() { # $1=def-file

4216 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4217 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4218 default_storedb "$@"

4219 query "select rowid from $tbl order by rowid desc;" \

4220 | while read rowid; do

4221 viewtable $1 $tbl $rowid

4222 done | _m4 -D_TITLE_="$tbl" \

4223 -D_FORM_="`genform $1`" \

4224 -D_DUMPTABLE_="syscmd(cat)" \

4225 $layout/html.m4.html $layout/form+dump.m4.html

4226 }

4227 default_viewtext() { # $1=def-file

4228 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4229 default_storedb "$@"

4230 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4231 | _m4 -D_TITLE_="$tbl" \

4232 -D_FORM_="`genform $1`" \

4233 -D_DUMPTABLE_="syscmd(cat)" \

4234 $layout/html.m4.html $layout/form+dump.m4.html

4235 }

4236 default_smail() {

4237 default_storedb "$@"

4238 if [ $? -eq 2 ]; then

4239 _m4 -D_TITLE_="入力" \

4240 -D_FORM_="`genform $1`" \

4241 -D_DUMPTABLE_="" \

4242 $layout/html.m4.html $layout/form+dump.m4.html

4243 return

4244 fi

4245 cond=""

4246 for pk in `gettblpkey $tbl`; do

4247 pv=$(sqlquote "$(getpar $pk)")

4248 cond="$cond${cond:+ and }$pk=$pv"

4249 done

4250 sql="select rowid from $tbl where $cond;"

4251 rowid=`query "$sql"`

4252 ## err smail1 - "$sql" "-> rowid=$rowid"

4253

4254 while IFS=: read prompt name keytype type args; do # Read from $1

4255 val=`getpar $name`

4256 if [ -n "$val" ]; then

4257 text="$text

4258 $prompt

4259 $name=$val

4260 ---------------------------------------------------------"

4261 fi

4262 case "$type" in

4263 image|document|file)

4264 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4265 fns=$(echo "$fn"|while read fn; do

4266 err mv $tmpd/$fn.orig $tmpd/$fn

4267 mv $tmpd/$fn.orig $tmpd/$fn

4268 rm $tmpd/$fn.rowid # Remove cache flag

4269 ## err "`ls $tmpd/$fn`"

4270 echo $fn

4271 done)

4272 files="$files $fns"

4273 ;;

4274 esac

4275 done < $1

4276 ## err FILES=$files "`ls -lF $tmpd`"

4277 subj="from ${REMOTE_ADDR}"

4278 (echo "$url"

4279 echo "への書き込みがありました。"

4280 echo "------"

4281 echo "$text"

4282 ) | (cd $tmpd &&

4283 err LS="`ls -lF`" &&

4284 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4285 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4286 echo "以下の内容で送信しました。" | html p

4287 viewtable $1 $tbl \

4288 `query "select rowid from $tbl order by rowid desc limit 1;"`

4289 echo "戻る" | html a "href=\"?\""

4290 }