s4: 1b294b80544e s4-funcs.sh

s4

view s4-funcs.sh @ 727:1b294b80544e

[TEST]Debug code added for login failure

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sun, 31 May 2020 20:42:02 +0900
parents	78cc4ba7bf42
children	398df4c7cd27

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 db=${DB:-$dbdir/cgi.sq3}

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

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

18 workdb=$dbdir/tmpdata.sq3

19 sessdb=$dbdir/sess.sq3

20 sesstb=tmp.sess

21 listentlimit=${LISTENTLIMIT:-30}

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

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

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

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

26 templ=${TEMPL:-templ}

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

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

29 imgdir=${IMGDIR:-img}

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

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

32 msgdir=$templ/msg

33 timeout="+2 days"

34 memoplimitdays="7"

35 dumpcollen=22

36 #thumbxy=120x120

37 thumbxy=96x96

38 iconxy_S=80x80

39 iconxy_M=400x400

40 maximagexy=1600x1600

41 ### maximagexy=400x400

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

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

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

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

47 file_accept_help="

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

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

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

51 "

52 file_warn="$file_accept_help

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

54 blogreadflagrowid=0

55 blogcutoffflagrowid=-1

56 whatsnewdays=${WHATS_NEW_DAYS:-14}

58 tconfs=""

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

60 conftbl=_tblconf

61 nl="

62 "

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

64 iconcachekey="profimgcache_S"

65 case "$HTTP_USER_AGENT" in

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

67 *) touchpanel="" ;;

68 esac

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

71 : <<EOF

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

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

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

76 レポート提出管理

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

79 [2016]

80 7/12 根本への反省

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

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

84 [以下2015]

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

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

87 Web

88 締切設定

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

91 ○自分の提出物リスト

93 7/19 ○設置

94 ○一斉送信

95 ○getparfilename の tmpd の扱い

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

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

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

99 ○添付ファイル回収

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

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

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

103

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

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

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

107 ○レポート提出モード

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

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

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

111

112

113 7/9 ○管理者の追加

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

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

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

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

118

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

120

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

122 ○グループ検索

123

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

125

126

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

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

129

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

131 ○6/1 par2tableを triplex 対応に

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

133 →とすると一気に

134

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

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

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

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

139

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

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

142 ## 1. passwd

143 ## 2. grp

144 ## 3. grp_mem

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

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

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

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

149

150 ## ● listの定義:

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

152 ## ● hashの定義:

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

154

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

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

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

158 ## csv2sq3 で .csv.sq3 の Makefile

159

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

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

162 ## type := root | comment

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

164 ## unique(id, filename)

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

166

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

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

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

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

171 ## できたー!

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

173 ↓

174 ↓以下に変更

175 with recursive allmem as

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

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

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

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

180

181

182 with recursive allmem as

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

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

185 where allmem.val=grp_m.gname)

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

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

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

189

190

191 ## triggerもできた。

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

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

194

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

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

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

198 ## グループ管理

199 ## 個人情報管理

200 ##

201

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

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

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

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

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

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

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

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

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

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

212

213 ## ログテーブル

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

215

216

217

218 ■表設計

219 * 3つの表に分散管理

220 id格納表 + hash表 + list表

221 * *_s *_m

222

223

224

225 user, user_map, user_col

226

227 ■抽象エントリタイプ

228 * user

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

230 * group

231 権限判定に利用

232 * serial

233 自動idとして機能

234 * password

235 入力 type=passwordで入力

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

237 * session

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

239 * text

240 入力 type=text

241 * textarea

242 入力 textarea

243 * image|document

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

245 * owner

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

247 * gowner

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

249 * timestamp

250 datetime()

251 * parent

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

253 * path

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

255

256 格納タイプ

257 * list

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

259 * hash

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

261

262 オブジェクトタイプ

263 * entry

264 id, title, owner

265 * textpart

266 id, parentID, text

267 * binarypart

268 id, parentID, contenttype, filename, content

269 * content

270 hash(textpart), list(binarypart)

271 * topic

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

273 * reply

274 id, parentID, content

275 * blog

276 list(entry)

277 blog = [topic, list(reply)]

278

279

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

281 "text" => "hogehoge ..",

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

283 "author" => "taro",

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

285 "parent" => "/",

286 "path" => "/1",

287 "text" => "blah, blah, ....",

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

289 {"serial" => 2,

290 "author" => "hanako",

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

292 "parent" => "/",

293 "path" => "/2",

294 "text" => "blah, blah, ....",

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

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

297

298

299 user:=

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

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

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

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

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

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

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

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

308

309 変換表

310 /user/email=m

311

312 blog:=

313 シリアル:id:p:serial

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

315 所有者:owner:s:owner:

316 時刻:ctime:s:stamp:

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

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

319

320 article:=

321 シリアル:id:p:serial

322 筆者:author:s:owner

323 時刻:ctime:s:stamp:

324 参照元:parent:s:parent:

325 パス:path:s:path:

326 本文:text:s:textarea:

327 画像:image:m:image:

328

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

330

331

332 EOF

333

334 logstart() {

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

336 }

337 logend() {

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

339 }

340 sqlog() {

341 logstart

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

343 cat >> $querylog

344 else

345 echo "$*" >> $querylog

346 fi

347 logend

348 }

349 sq() {

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

351 logstart

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

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

354 else

355 echo "$@" >> $querylog

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

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

358 fi

359 logend

360 }

361 dbsetup() {

362 pipedir=$tmpdir/pipedir

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

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

365 suf=`date +%s`

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

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

368 mkfifo $sqi $sqo

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

370 sq $db < $sqi &

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

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

373 exec 2>> $tmpdir/error.out

374 fi

375 exec 3>> $tmpdir/debug.out

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

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

378 rm $sqi

379 # Attach supplemental DB

380 cat >&5 <<-EOF

381 .output /dev/null

382 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

386 .output stdout

387 EOF

388 }

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

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

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

392 fi

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

394 }

395 cleanup() {

396 trap '' INT HUP EXIT TERM PIPE

397 echo .quit >&5

398 kill $sq3pid

399 kill $sq3pid

400 rm -f $sqo $sqi

401 rm -rf $tmpfiles

402 cleanup2

403 }

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

405 # simple redirection for the sake of speed.

406 query() {

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

408 echo ".output $sqo" >&5

409 logstart

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

411 tee -a $querylog

412 else

413 echo "$@" >> $querylog

414 echo "$@"

415 fi >&5

416 echo ".output stdout" >&5

417 cat $sqo

418 rc=$?

419 logend

420 return $rc

421 }

422 _m4() {

423 #S4NAME=f,f,f

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

425 }

426 ismember() {

427 # $1=user, $2=group

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

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

430 }

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

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

433 }

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

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

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

437 }

438 isgrpowner() (

439 # $1=user, $2=group

440 gn=`sqlquote "$2"`

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

442 err isgrpowner: $sql

443 test -n "`query $sql`"

444 )

445 isgrpownerbygid() (

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

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

448 err isgrpownerbygid: $sql

449 test -n "`query $sql`"

450 )

451 getgroupadminmails() {

452 # $1=group

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

454 email4group "$1" "$i" ;

455 done

456 }

457 getgroupadmins() { # $1=group

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

459 qgrp=`sqlquote "$1"`

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

461 }

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

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

464 getvalbyid grp $2 \

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

466 }

467 getgroupbyid() {

468 # $1=id|gname

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

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

471 # err ggbyid: `echo $sql`

472 query $sql

473 }

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

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

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

477

478 # Return true when anonymous mode

479 [ "$anonymousmode" ] && return 0

480 # case `getvalbyid blog mode $2` in

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

482 # *closed*)

483 # owner=`getvalbyid blog owner $2`

484 # if isgrp $owner; then

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

486 # elif isuser $owner; then

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

488 # fi

489 # esac

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

491 sql="with getblog as (

492 select key,val from blog_s where id=(

493 select blogid from article where id in

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

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

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

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

498 isgrpadm as (select user from grp_adm where

499 gname=(select val from getowner) and

500 user='$1'),

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

502 select case

503 when (select author from article where

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

505 then 'author'

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

507 then 'open'

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

509 then CASE

510 WHEN (SELECT val FROM isgrp) IS NULL

511 THEN

512 CASE WHEN (SELECT val from getowner)

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

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

515 ELSE ''

516 END

517 WHEN (select user from isgrpadm) IS NOT NULL

518 THEN 'i-am-admin'

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

520 END

521 when (select val from getmode) is null

522 then 'open'

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

524 then (SELECT user FROM isgrpadm)

525 when (select author from article where

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

527 then 'user+author'

528 else '' end;"

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

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

531 # result=`query "$sql"`

532 # err FileAccessibility=$result

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

534 }

535 linkhome() {

536 # $1=UserOrGroup

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

538 if isuser $1; then

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

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

541 else

542 echo -n "grp+$1"

543 fi

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

545 }

546 hreflink() {

547 # s4 specific notation:

548 # ^href=URL

549 # ^iframe=URL

550 # ^video=URL

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

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

553 # OSM umap Wikistyle Notation:

554 # [[URL]] - Simle Link

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

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

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

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

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

560 # Other Style

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

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

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

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

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

566 # SPC+SPC+$ - <br>

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

568 checkboxON="${cb} checked>"

569 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

594

595 }

596 minitbl() {

597 sed -n '

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

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

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

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

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

603 H; # Concat this line to HoldSpace

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

605 $ b cont

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

607 }

608 :cont

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

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

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

612 # . of ^. is workaround for FreeBSD sed

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

614 p; # Print whole "table" element

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

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

617 }

618 x; # Back to the newest line

619 p; # Print rest' | miniul

620 }

621 miniul() {

622 sed -e '

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

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

625 :top

626 s/\n//;

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

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

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

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

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

632 $ b cont

633 N; # 次の行を読む

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

635 /^ /b top

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

637 p; s/.*//;

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

639 /^\* /b top

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

641 }

642

643 :cont

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

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

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

647 H; x

648 }

649 x'

650 }

651 acclog() (

652 # $1=table, $2=rowid

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

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

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

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

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

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

659 fi

660 )

661 gecos() (

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

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

664 )

665 setpar() {

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

667 case "$1" in

668 user) _user="$v" ;;

669 skey) _skey="$v" ;;

670 esac

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

672 }

673 replpar() {

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

675 }

676 getpar() {

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

678 val=""

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

680 user) val=$_user ;;

681 skey) val=$_skey ;;

682 esac

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

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

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

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

687 fi

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

689 case "$var" in

690 owner)

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

692 echo $user; return

693 elif ismember $user $val; then

694 echo $val; return

695 fi ;;

696 esac

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

698 echo "$val"

699 }

700 setskey() {

701 # For quick response...(?)

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

703 }

704 chkskey() {

705 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

711 return 0

712 fi

713 return 1

714 }

715 resetskey() {

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

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

718 fi

719 }

720 getpartype() {

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

722 }

723 getparcount() {

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

725 }

726 getparfilename() {

727 # null if type of $1 is not file

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

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

730 }

731 sqlquote() {

732 (v="$1"

733 case "$v" in

734 "") return ;; # null

735 "X'"*) # quoted hex string

736 echo $1 ;;

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

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

739 echo "\"$v\""

740 return ;;

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

742 echo "\"$v\""

743 return ;;

744 *)

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

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

747 else

748 echo "\"$v\""

749 fi ;;

750 esac)

751 }

752 sqlquotestr() (

753 case "$1" in

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

755 echo "'$v'" ;;

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

757 esac

758 )

759 mktempd() {

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

761 }

762 getcachedir() { # $1=maintable

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

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

765 else

766 echo $tmpd/$thumbxy

767 fi

768 }

769 getval() {

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

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

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

773 echo "$user" ;;

774 stamp|datetime)

775 date "+%F %T" ;;

776 serial)

777 (s=`getpar $2`

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

779 *)

780 getpar "$2" "$3";;

781 esac

782 }

783

784 getvalquote() {

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

786 (v=`getval "$@"`

787 case "$v" in

788 "") echo NULL ;;

789 *) sqlquote "$v" ;;

790 esac)

791 }

792 getparquote() {

793 sqlquote "`getpar $1`"

794 }

795 getbinbyid() {

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

797

798 }

799 getvalbyid() {

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

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

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

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

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

805

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

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

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

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

810 return

811 fi

812 done

813 rowid=$3

814 pk=`gettblpkey $1`

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

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

817 td=${4:-$tmpd}

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

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

820 for kt in s m; do

821 t=${1}_$kt

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

823 vcount=1 # count(val)

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

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

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

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

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

829 if [ $kt = m ]; then

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

831 # Reset val to store filenames if type is string

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

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

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

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

836 i=0

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

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

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

840 i=$((i+1))

841 fn=$c.$i

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

843 case $type in

844 file:*)

845 #file=$td/$val

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

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

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

849 # FOR SPEED: Skip file generation if imgcache exists

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

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

852 && continue

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

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

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

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

857 select rowid from $cond $slice;

858 .output '$td/$fn'

859 select val from $cond $slice;

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

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

862 .output stdout

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

864 EOF

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

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

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

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

869 ls -lh "$file" |

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

871 case "$type" in

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

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

874 *|*:[Aa]pplication*)

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

876 png:- > "$file"

877 ;;

878 esac

879 ;;

880 *)

881 sq $db<<EOF

882 .output $td/$fn.rowid

883 select rowid from $cond $slice;

884 .output $td/$fn

885 select val from $cond $slice;

886 EOF

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

888 ;;

889 esac

890 done

891 else

892 rm -f $td/$c.count

893 case $type in

894 file:*)

895 echo "$val" \

896 | while read fn; do

897 file=$td/$fn

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

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

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

901 | unhexize > "$file"

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

903 case $type in

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

905 *:[Aa]pplication*)

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

907 png:- > $file ;;

908 esac

909 fi

910 done

911 ;;

912 esac

913 fi

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

915 return

916 fi

917 done

918 done)

919 }

920 getvalbypkey() (

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

922 pk=`gettblpkey $1`

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

924 getvalbyid "$1" "$2" $rowid $4

925 )

926 getvalbycond() {

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

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

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

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

931 getvalbyid "$1" "$2" $rowid "$4"

932 fi

933 }

934 getpwfield() {

935 # getpwfield user column

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

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

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

939 echo "$val"

940 return 0

941 else

942 return 1

943 fi

944 }

945 numericalize() {

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

947 }

948 encode() {

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

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

951 sha1=sha1

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

953 sha1=sha1sum

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

955 sha1=gsha1sum

956 fi

957 fi

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

959 }

960 enjpeg() {

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

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

963 cjpeg="cjpeg"

964 else

965 cjpeg="convert - jpeg:-"

966 fi

967 fi

968 $cjpeg "$@"

969 }

970 mycrypt() (

971 key=$1 salt=$2

972 # err \$2=$2

973 case $2 in

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

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

976 esac

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

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

979 )

980 hexize() {

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

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

983 hexize="xxd -p"

984 else

985 hexize_hd() {

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

987 }

988 hexize="hexize_hd"

989 fi

990 fi

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

992 }

993 unhexize() {

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

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

996 unhex="xxd -p -r"

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

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

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

1000 EOF

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

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

1003 # world writable...:)

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

1005 fi

1006 fi

1007 cat "$@" | $unhex

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

1009 }

1010 percenthex() {

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

1012 }

1013 htmlescape() {

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

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

1016 }

1017 enascii() {

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

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

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

1021 else

1022 enascii_now=`date +%FT%T`

1023 enascii_sed() {

1024 nkf -Z0Z1Z2 \

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

1026 }

1027 enascii="enascii_sed"

1028 fi

1029 fi

1030 cat "$@" | $enascii

1031 }

1032 size_h() {

1033 i="$1" oi=$1

1034 set -- B B KB MB GB TB

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

1036 oi=$i

1037 i=$((i/1024))

1038 shift

1039 done

1040 echo ${oi}$1

1041 }

1042 gettblconf() {

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

1044 ## tconfs=`sq $db \

1045 tconfs=`query \

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

1047 fi

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

1049 }

1050 gettblkeys() {

1051 # $1=tbl

1052 gettblconf

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

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

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

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

1057 case $type in

1058 *p*)

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

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

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

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

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

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

1065 esac

1066 case $type in

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

1068 esac

1069 done

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

1071 }

1072 gettblpkey() {

1073 # $1=tbl

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

1075 }

1076 gettblfkey() {

1077 (x=`gettblkeys $1`

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

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

1080 }

1081 gettblcols() {

1082 (x=`gettblkeys $1`

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

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

1085 }

1086 gettbl_s_cols() {

1087 (x=`gettblkeys $1`

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

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

1090 }

1091 gettbl_m_cols() {

1092 (x=`gettblkeys $1`

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

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

1095 }

1096 gettbl_h_cols() {

1097 (x=`gettblkeys $1`

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

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

1100 }

1101 gettbl_coltype() (

1102 gettblconf

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

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

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

1106 )

1107 is_hidden() {

1108 # $1=Tbl $2=col

1109 gettblconf

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

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

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

1113 case $x in

1114 *h*) return 0 ;;

1115 *) return 1 ;;

1116 esac

1117 }

1118

1119 dbsetbyid() {

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

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

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

1123 quotedp=$(sqlquotestr "$p")

1124 unset primary update

1125 gettblconf

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

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

1128 #err conf=$conf

1129 case ${conf#*=} in

1130 p*) primary=1 ;;

1131 f*) update=1 ;;

1132 u*) ;;

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

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

1135 esac

1136 #err t=$t

1137 type=string fn=""

1138 case $conf in

1139 */password)

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

1141 ;;

1142 */image*|*/document*)

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

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

1145 ;;

1146 esac

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

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

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

1150 if [ "$primary" ]; then

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

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

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

1154 elif [ "$update" ]; then

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

1156 else

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

1158 fi

1159 )

1160 }

1161 expire() (

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

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

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

1165 )

1166 addsession() {

1167 # expireをセット

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

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

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

1171 # Remove old session parameters

1172 now=`expire now`

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

1174 }

1175 gencookie() (

1176 path=${URL#*:/}

1177 path=${URL%/*}

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

1179 for kv; do

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

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

1182 done

1183 )

1184 contenttype() {

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

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

1187 }

1188 putheader() {

1189

1190 }

1191 putfooter() {

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

1193 }

1194 getcookie() {

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

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

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

1198 case "$k" in

1199 user) _user="$v" ;;

1200 skey) _skey="$v" ;;

1201 esac

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

1203 done

1204 }

1205 genrandom() {

1206 # $1=columns (default: 10)

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

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

1209 }

1210 genserial() {

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

1212 }

1213 smail() {

1214 # smail rcpts subj (file)

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

1216 # $MAIL_FROM <- From: header value

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

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

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

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

1221 sender=${SENDER:-$admin}

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

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

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

1225 }

1226 smail_queue_flush() {

1227 # $1=timelimit

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

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

1230 SELECT rowid FROM smailq

1231 GROUP BY rcpts, subj

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

1233 EOF

1234 )

1235 for rid in $rowids; do

1236 sq $workdb \

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

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

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

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

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

1242

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

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

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

1246 .separator "\n" "------------------\n\n"

1247 SELECT time, text FROM smailq

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

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

1250 ORDER by time;

1251 EOF

1252 then

1253 echo rowid=$rid

1254 cat <<-EOF | sq $workdb

1255 DELETE FROM smailq

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

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

1258 EOF

1259 fi

1260 fi

1261 done

1262 }

1263 smail_queue() {

1264 # $1=Rcpts, $@=subj

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

1266 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1275 EOF

1276 )

1277 ## XXX: Adhoc load mitigation

1278 case "$now" in

1279 *[01])

1280 err flush_queue=$mintime

1281 smail_queue_flush ;;

1282 esac

1283 fi

1284 }

1285 setviastring() {

1286 table=par

1287 oifs="$IFS"

1288 IFS="&"

1289 for us in $1; do

1290 k=${us%%=*}

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

1292 setpar "$k" "string" "$v"

1293 done

1294 IFS="$oifs"

1295 }

1296 checkdomain() (

1297 # Check the validity of domain by referring DNS

1298 item=$1

1299 err checkdomain $1

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

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

1302 )

1303 pwcheck() {

1304 # $1=passwd

1305 dbpswd=`getpwfield $user pswd`

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

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

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

1309 }

1310 mypwhash() {

1311 mycrypt "`cat`" `genrandom 5`

1312 }

1313 wasureta() {

1314 user=$1

1315 if ! checkdomain $user; then

1316 contenttype; echo

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

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

1319 putfooter

1320 exit 0

1321 fi

1322 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1324 encpswd=`echo $newpswd|mypwhash`

1325 dbsetbyid user $user pswd "$encpswd"

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

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

1328 $msgdir/mail-newaccount.m4 \

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

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

1331 }

1332 checkauth() {

1333 user=`getpar user`

1334 skc=`getpar skey` # from cookie

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

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

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

1338 if chkskey "$skc"; then

1339 return 0

1340 fi

1341 fi

1342 pswd=`getpar pswd`

1343 quser=`sqlquotestr "$user"`

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

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

1346 return 4 # 4=server too heavy

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

1348 return 2 # 2=login fail

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

1350 wasureta "$user"

1351 return 1 # wasureta error

1352 fi

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

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

1355 if pwcheck "$pswd"; then

1356 newsession=`genrandom 34`

1357 if setskey "$user" "$newsession" &&

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

1359 gencookie "user=$user" "skey=$newsession"

1360 return 0

1361 else

1362 return 4 # Heavy load??

1363 fi

1364 fi

1365 err "Login failed: [$user]"

1366 return 2 # Password mismatch

1367 }

1368 showlogin() {

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

1370 test -z "$args" && resetskey

1371 s4name=${S4NAME:-s4}

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

1373 cat $layout/login.m4.html

1374 echo '</body></html>'

1375 ) | _m4 \

1376 -D_BODYCLASS_="login" \

1377 -D_TITLE_="$s4name" \

1378 -D_SYSNAME_="Welcome to $s4name" \

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

1380 exit 0

1381 }

1382 dologin() {

1383 checkauth

1384 st=$?

1385 if [ $st != 0 ]; then

1386 contenttype; echo

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

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

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

1390 showlogin # and EXIT

1391 fi

1392 }

1393

1394 # Do instant jobs here

1395 dbsetup

1396 trap cleanup INT HUP EXIT TERM PIPE

1397 # trap cleanup INT HUP

1398

1399 err() {

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

1401 }

1402

1403 cgiinit() {

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

1405 tmpf=tmp/stream

1406 tmpd=`tmpd=$tmpdir mktempd`

1407 tmpfiles=$tmpfiles" $tmpd"

1408 addsession $session

1409 getcookie

1410 case "$REQUEST_METHOD" in

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

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

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

1414 # cat > $tmpf # too much?

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

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

1417 cp tmp/stream /tmp # 2020-0531

1418 s="`cat tmp/stream`"

1419 tmpfiles=$tmpfiles"${tmpfiles+ }$tmpf"

1420 ;;

1421 esac

1422 case "$CONTENT_TYPE" in

1423 *boundary*)

1424 bndry=${CONTENT_TYPE#*boundary=}

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

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

1427 do

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

1429 #echo u=$us

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

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

1432 # err k=$k v=$v

1433 case "$k" in

1434 *:filename)

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

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

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

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

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

1440 :

1441 fi

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

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

1444 case "$mimetype" in

1445 [Ii]mage/x-xcf)

1446 bzip2 "$tmpd/$v"

1447 v=${v}.bz2

1448 ;;

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

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

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

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

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

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

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

1456 v=$vjpg

1457 else

1458 mimetype="Not supported"

1459 fi

1460 ;;

1461 [Ii]mage/*)

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

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

1464 ;;

1465 esac

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

1467 then

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

1469 continue

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

1471 replpar text string \

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

1473 continue

1474 fi

1475 ;;

1476 *)

1477 type='string'

1478 ;;

1479 esac

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

1481 setpar "$k" "$type" "$v"

1482 done

1483 ;;

1484 *)

1485 setviastring "$s"

1486 ;;

1487 esac

1488 }

1489 email4group() {

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

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

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

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

1494 sql="WITH

1495 grpemails AS (

1496 SELECT gname, user, val email

1497 FROM grp_mem NATURAL JOIN grp_mem_s

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

1499 useremails AS (

1500 SELECT user.name, val email

1501 FROM user

1502 LEFT JOIN user_m

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

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

1505 FROM useremails u LEFT JOIN grpemails g

1506 ON u.name=g.user

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

1508 query "$sql"

1509 }

1510 email4groupbyuid() {

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

1512 g=$1; shift

1513 uids=`echo "$@"`

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

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

1516 email4group "$g" `query "$sql"`

1517 }

1518 myemail4group() {

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

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

1521 }

1522 collectmembersbyid() {

1523 # Collect user names of group specified by grid

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

1525 query "SELECT user FROM grp_mem \

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

1527 }

1528 collectmembersbyid() {

1529 # Collect user names of group name

1530 qgrp=`sqlquote "$1"`

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

1532 }

1533 collectgecosesbyid() {

1534 # Collect user gecoses of group

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

1536 query<<-EOF

1537 SELECT gecos

1538 FROM gecoses

1539 WHERE name IN (SELECT user FROM grp_mem

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

1541 EOF

1542 }

1543 collectemail() (

1544 # Collect email addresses for group $1

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

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

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

1548 for e; do

1549 if isuser "$e"; then

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

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

1552 else

1553 qgrp=`sqlquote "$e"`

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

1555 gmem="grp_mem"

1556 else

1557 tm=`sqlquote "$TEAM"`

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

1559 fi

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

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

1562 $gmem g left join grp_mem_s s

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

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

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

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

1567 query "$sql"

1568 fi

1569 done

1570 )

1571 sendinvitation() (

1572 # $1=email

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

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

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

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

1577 gecos=`gecos`

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

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

1580 _m4 -D_URL_="$urlbase" \

1581 -D_USER_="$name" \

1582 -D_EMAIL_="$1" \

1583 -D_REGIST_="$regist" \

1584 -D_ADMIN_="$admin" \

1585 $msgdir/mail-invite.m4 \

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

1587 return 0

1588 )

1589 emaildomaincheck() {

1590 case "$1" in

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

1592 *@*)

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

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

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

1596 return 2

1597 fi

1598 return 0

1599 ;;

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

1601 esac

1602 }

1603 invite() {

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

1605 case "$email" in

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

1607 *@*)

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

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

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

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

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

1613 elif sendinvitation $email; then

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

1615 else # Cannot be reached here

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

1617 fi ;;

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

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

1620 esac

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

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

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

1624 fi

1625 if [ -f $invite_policy ]; then

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

1627 else

1628 pol="$invite_policy"

1629 fi

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

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

1632 -D_INVITE_POLICY_="$pol" \

1633 $layout/html.m4.html $layout/invite.m4.html

1634 }

1635 regist() {

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

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

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

1639 echo "bye bye" | html p

1640 reutrn

1641 fi

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

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

1644 cat<<EOF

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

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

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

1648 EOF

1649 return

1650 fi

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

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

1653 # Fake login password to wasureta

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

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

1656 wasureta $email

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

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

1659 addsession $1 # for removal after 1 minute

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

1661 $layout/login.m4.html

1662 return

1663 }

1664 group_safename() {

1665 # Convert $1 to safe group name

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

1667 }

1668 groupupdate() {

1669 gname=`getpar gname`

1670 qgname=`sqlquote "$gname"`

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

1672 # See ALSO same job in showgroup()

1673 newgname=`group_safename "$gname"`

1674 err newgname=$newgname

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

1676 err NewGNAME: gname=$newgname

1677 gname=$newgname

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

1679 replpar gname string "$gname"

1680 fi

1681 # Name confliction check

1682 parow=`getpar rowid`

1683 ## err parow=$parow

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

1685 query "BEGIN EXCLUSIVE;"

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

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

1688 if [ $count -gt 0 ]; then

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

1690 query "END;"

1691 return

1692 fi

1693 par2table $formdir/grp.def

1694 query "END TRANSACTION;"

1695 # Remove orphan

1696 : <<EOF

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

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

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

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

1701 EOF

1702 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1706 sql="delete from blog where id in

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

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

1709 query "$sql";

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

1711 return # and return

1712 fi

1713 fi

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

1715 fi

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

1717 grid=$(query $sql)

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

1719 grp $grid

1720 }

1721 groupclone() {

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

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

1724 case "$1" in

1725 */noteam)

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

1727 esac

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

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

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

1731 return

1732 fi

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

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

1735 return

1736 fi

1737 i=0

1738 while true; do

1739 copy="-copy$i"

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

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

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

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

1744 i=$((i++))

1745 continue

1746 fi

1747 break

1748 done

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

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

1751 query<<-EOF

1752 BEGIN;

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

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

1755 SELECT $newqname, key, val

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

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

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

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

1760 -- Copy members and their configuration --

1761 REPLACE INTO grp_mem SELECT $newqname, user

1762 FROM grp_mem WHERE gname=$qgrp;

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

1764 FROM grp_mem_s WHERE gname=$qgrp;

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

1766 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1767 -- Copy administrators --

1768 REPLACE INTO grp_adm SELECT $newqname, user

1769 FROM grp_adm WHERE gname=$qgrp;

1770 COMMIT;

1771 EOF

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

1773 STOPCLONEMSG=1 groupconf "$newrowid"

1774 }

1775 groupman() {

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

1777

1778 GF_STAGE="grpconf"

1779 GF_STAGE=groupupdate

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

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

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

1783 -D_DUMPTABLE_="syscmd(cat)" \

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

1785 }

1786 userconf() {

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

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

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

1790 }

1791 groupconf() {

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

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

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

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

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

1797

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

1799 grp=`getgroupbyid $rowid`

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

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

1802 return

1803 fi

1804

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

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

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

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

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

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

1811 <div>

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

1813 <table>

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

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

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

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

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

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

1820 </table>

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

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

1823 </div>

1824 EOF

1825 fi

1826 }

1827 mems() {

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

1829 kwd=`getpar kwd`

1830 listmember $kwd

1831 }

1832 grps() {

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

1834 kwd=`getpar kwd`

1835 listgroup $kwd \

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

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

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

1839 $layout/form+dump.m4.html

1840 }

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

1842 gpg=`getpar grp`

1843 grid=${1:-$gpg}

1844 grp=`getgroupbyid "$grid"`

1845 ## . ./s4-blog.sh

1846 jg=`getpar joingrp`

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

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

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

1850 fi

1851 htmlheader=$layout/html.m4.html

1852 showgroup "$grid"

1853 }

1854 sql4interestblogs() {

1855 cat<<EOF

1856 CREATE TEMPORARY VIEW interestblogs AS

1857 SELECT blog.rowid rid, id, author

1858 FROM blog

1859 NATURAL JOIN

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

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

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

1863 WHEN (SELECT user FROM grp_mem

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

1865 THEN 0

1866 ELSE 1

1867 END;

1868 EOF

1869 }

1870 listnewblogsql() { # $1=user

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

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

1873 newdays=${newdays:-14}

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

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

1876 WHERE user='$user'

1877 AND tblrowid IN

1878 ($blogreadflagrowid,

1879 $blogcutoffflagrowid)),

1880 $basetime -- "0"

1881 );"`

1882 cat<<EOF

1883 `sql4interestblogs`

1884 WITH article_ctime as (

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

1886 FROM article join article_s s using(id)

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

1888 GROUP BY id

1889 ), blog_title_owner as (

1890 SELECT blg.rid brid, id,

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

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

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

1894 ), blogall as (

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

1896 ), news as (

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

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

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

1900 FROM blogall bl

1901 LEFT JOIN

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

1903 ON bl.brid=al.tblrowid

1904 WHERE atime < bl.ctime

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

1906 LIMIT 10

1907 ) SELECT brid LINK, "新着",

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

1909 ctime, title,

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

1911 FROM news;

1912 EOF

1913 }

1914

1915 search_form() {

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

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

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

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

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

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

1922 @week → 最近一週間

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

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

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

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

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

1928 auth=""

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

1930 case "$1" in

1931 author=*)

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

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

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

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

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

1937 ;;

1938 grp=*)

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

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

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

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

1943 ;;

1944 esac

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

1946 cat<<-EOF

1947 <div class="right">

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

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

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

1951

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

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

1954

1955 </form>

1956 </div>

1957 EOF

1958 }

1959

1960 imgsrc_cache() (

1961 # $1 = directory for cache'ing

1962 # $2 = table (user_m or grp_m)

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

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

1965 dir="$1" tbl="$2"

1966 keycond="$3"

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

1968 ORDER BY rowid DESC LIMIT 1"

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

1970 tmpf=$tmpd/imgsrc_cache.$$

1971 case "$4" in

1972 [Ss]) size=S ;;

1973 [Oo]) size=O ;;

1974 *) size=M ;;

1975 esac

1976 # ImageCache filename storing schema:

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

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

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

1980 valtype=`query "$sql0"`

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

1982 filetype=${valtype##*//file:}

1983 if [ x"$filename" = x"${filename%.*}" ]; then

1984 # If nor filename extension found, set it to image type

1985 case "$filetype" in

1986 image/*) filename=$filename.${filetype#image/} ;;

1987 esac

1988 fi

1989 cacheimg_S=$dir/S_$filename

1990 cacheimg_M=$dir/M_$filename

1991 cacheimg_O=$dir/$filename

1992 cacheimg=$dir/${size}_$filename

1993 sumfile="$dir/$filename.sum"

1994 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

1995 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

1996 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

1997 # if cache is fresh and has the same checksum,

1998 echo "<img src=\"$cacheimg\">"

1999 else

2000 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2001 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2002 fifo2=$fifo.2

2003 mkfifo $fifo $fifo2

2004 fmt=${filename##*.}

2005 ## [[ NOTE ]]

2006 ## a. convert oldimage newimage

2007 ## b. convert oldimage fmt:- | convert - newimage

2008 ## b is much smaller than a

2009 cat $tmpf | unhexize \

2010 | tee $fifo \

2011 | convert -define ${fmt}:size=${iconxy_M} \

2012 -resize ${iconxy_M}'>' - ${fmt}:- \

2013 | tee $fifo2 \

2014 | convert - "$cacheimg_M" &

2015 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2016 -resize ${iconxy_S}'>' - ${fmt}:- \

2017 | convert - "$cacheimg_S" &

2018 printf '%s' "<img src=\"data:${filetype},"

2019 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2020 echo '">'

2021 echo "$sum" > $sumfile

2022 fi

2023 ## Now preparing cache image, done.

2024 ## Store this information to DB

2025 stbl=${tbl%_m}_s # user_s or grp_s

2026 pkey=${keycond%%=*} # Primary Key name

2027 pval=${keycond#*=} # Primary Key value

2028 query <<-EOF

2029 REPLACE INTO $stbl($pkey, key, type, val)

2030 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2031 EOF

2032 )

2033

2034 showhome() {

2035 # $1=userRowIdToShow

2036 err showhome \$1=$1

2037 case "$1" in

2038 *@*) uname=`getvalbypkey user name "$1"` ;;

2039 *) uname=`getvalbyid user name $1` ;;

2040 esac

2041 ## err ShowHome: uname=$uname

2042 td=`getcachedir home/"$1"`

2043 gecos=`gecos "$uname"`

2044 ## err SH:gecos=$gecos

2045 GF_VIEWONLY=1

2046 cond="gname in (select gname from grp_mem where user='$uname')"

2047 search_form_args=""

2048 if [ x"$user" = x"$uname" ]; then

2049 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2050 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> /

2051 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2052 # Display folders

2053 sql="select count(id) from article_m where id

2054 in (select id from article where author='$user')

2055 and type like 'file:%';"

2056 ## err nfile-sql=`echo "$sql"`

2057 nfile=`query "$sql"`

2058 # err nfile=$nfile

2059 if [ $nfile -gt 0 ]; then

2060 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2061 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2062 fi

2063 else

2064 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2065 GROUP BY user;"`

2066 usermenu="<p>Last seen on $latestlog</p>"

2067 search_form_args="author=$uname"

2068 fi

2069 . ./s4-blog.sh

2070

2071 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2072 search_form "$search_form_args" > $sf

2073 printf "%s さん" "$gecos" > $tf

2074 { echo "<div class=\"noprofimg\">"

2075 viewtable $formdir/user.def user $1

2076 echo "</div>"

2077 } > $pf

2078

2079 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2080 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2081 imf=$tmpd/profimg.$$; touch $imf

2082 if [ -n "$img" ]; then

2083 if true; then

2084 tbl=user_m

2085 enticond="name='$uname'"

2086 imgsrc_cache "$td/main" user_m "$enticond" M

2087 else

2088 { printf '%s' "<IMG src=\"data:${img#file:},"

2089 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2090 | sed 's/$..$/%\1/g'

2091 echo '">'

2092 }

2093 fi > $imf

2094 fi

2095 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2096 val='$uname';"`

2097 ## REMOVE This comment block until 2019/7/1

2098 ## err "----- `gdate +%FT%T.%3N` ------------C"

2099 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2100 listblog $uname > $bf

2101 ## unset ddd

2102 ## err "----- `gdate +%FT%T.%3N` ------------D"

2103

2104 hometail=$tmpd/tail.$$

2105 mkfifo $hometail

2106

2107 #Calling listgroupbytable, originally here

2108

2109 (

2110 # Display Most Recent Entry

2111 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2112 shortval=${shortval:-val}

2113

2114 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2115 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2116 ctime,

2117 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2118 (SELECT gecos FROM gecoses

2119 WHERE name=(SELECT val FROM blog_s

2120 WHERE key='owner' AND id=b.id)) owner,

2121 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2122 FROM blog b

2123 JOIN

2124 (SELECT distinct blogid, a.id aid, max(val) ctime

2125 FROM article a, article_s s

2126 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2127 GROUP BY blogid ORDER BY val DESC LIMIT 50

2128 ) m

2129 ON b.id=m.blogid;"

2130 # This should be as follows

2131 : <<EOF

2132 WITH arts AS(

2133 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2134 a.blogid, a.id id, s.val ctime

2135 FROM article a NATURAL JOIN article_s s

2136 WHERE s.key = 'ctime' AND a.author='$user'

2137 GROUP by s.id

2138 )

2139 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2140 FROM arts a0

2141 JOIN

2142 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2143 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2144 ORDER BY ctime DESC LIMIT 50;

2145 EOF

2146

2147 cat<<-EOF

2148 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2149 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2150 <div class="lcto">

2151 `DT_VIEW=replyblog dumptable html blog`

2152 </div>

2153 EOF

2154 unset DT_SQL

2155 if [ x"$user" = x"$uname" ]; then

2156 # Display NEWS

2157 # 2016-06-26

2158 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2159 acclog blog $blogreadflagrowid

2160 # echo "全部既読にしました" | html p

2161 fi

2162 # 2016-02-19 Counting NEWS without using dumptable.

2163 sql=`listnewblogsql "$user"`

2164 # echo "$sql" > tmp/listnew

2165 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2166 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2167 cont=$((cont-1))

2168 err newcount=$cont

2169 if [ $cont -gt 0 ]; then

2170 #echo "全体の新着記事${cont}傑" | html h2

2171 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2172 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2173 cat<<-EOF | html form 'action="?home"'

2174 `cgi_checkbox readchk yes 'id="read"'`<label

2175 for="read">新着ふくめて全部読んだことにする</label>

2176 `cgi_submit '確定'`

2177 `cgi_hidden read yes`

2178 EOF

2179 echo "$new10 "

2180 echo "</div>"

2181 else # If news is 0, set log cut off flag

2182 acclog blog $blogcutoffflagrowid # for speed

2183 fi

2184 else # Not My Home ($user != $uname)

2185 : # DT_SQL=

2186 fi

2187 ) > $hometail & # Is background call safe to m4??

2188 #

2189 listgroupbytable $formdir/grp.def $cond |

2190 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2191 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2192 -D_PROFIMG_="spaste(\`$imf')" \

2193 -D_BLOGS_="spaste(\`$bf')" \

2194 -D_SEARCH_="spaste(\`$sf')" \

2195 -D_NBLOG_="$nblog" \

2196 -D_GROUPS_="syscmd(\`cat')" \

2197 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2198 $layout/html.m4.html $layout/home.m4.html

2199

2200 # Record access log

2201 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2202 }

2203 commission() { # $1=grp-rowid $2=user-rowid

2204 contenttype; echo

2205 ## err commission: "$@"

2206 gname=`getgroupbyid $1`

2207 echo "グループ $gname 管理者委任" \

2208 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2209 if [ -n "$2" ]; then

2210 grp_reg_adm "$@"

2211 else

2212 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2213 fi

2214 }

2215 listgroupbytable() {

2216 # $1=deffile $2...=condition

2217 tagline=`grep :tag: $1`; shift

2218 and="${1:+and }" where=${1:+where }

2219 href="<a href=\"$myname?grp+"

2220 echo '<div class="listgroup">'

2221 NGsql="select distinct tag from\

2222 (select gname, max(case key when 'tag' then val end) as tag, \

2223 max(case key when 'ctime' then val end) as ctime\

2224 from grp_s group by gname order by ctime);"

2225 sql="select val from grp_s where key='tag' $and$* group by val;"

2226 ## err ListGRP: query sql="$sql"

2227 for tag in `query "$sql"`

2228 do

2229 ## err ListGrp: tag=$tag

2230 tn=${tagline%%=${tag}*}

2231 tn=${tn##*[ :]}

2232 sql="select rowid||':'||gname as 'グループ名',説明 from

2233 (select (select rowid from grp g where g.gname=grp_s.gname)

2234 as rowid,

2235 gname,

2236 max(case key when 'gecos' then val end) as '説明',

2237 max(case key when 'tag' then val end) as 'tag',

2238 max(case key when 'mtime' then val end) as mtime from grp_s

2239 $where$* group by gname having tag='$tag' order by mtime desc);"

2240 ## err PersonalGroupList= `echo $sql`

2241 echo "<h2>$tn</h2>"

2242 echo '<table class="b listgroup">'

2243 sq -header -html $db "$sql" \

2244 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2245 echo '</table>'

2246 done

2247 echo '</div>'

2248 }

2249 iconhref() (

2250 # $1=icon-file, $2=Href $3=title $4...=anchor

2251 data=`percenthex "$1"`

2252 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2253 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2254 href=$2; title=$3; shift 3

2255 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2256 )

2257 iconhref2() (

2258 # $1=icon-file, $2=Href $3=title $4...=anchor

2259 src=$1

2260 href=$2; title=$3; shift 3

2261 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2262 )

2263 listentry() (

2264 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2265 # Referring variable $iamowner=$grp to attach owner-request links

2266 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2267 cond='' hiddens=''

2268 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2269 if [ -z "$offset" ]; then

2270 offset=`getpar start`; offset=${offset%%[!0-9]*}

2271 offset=$((offset-1))

2272 fi

2273 offset=$((offset + 0)) # change to numeric forcibly

2274 [ $offset -lt 0 ] && offset=0

2275 limit=$listentlimit

2276 dir=`getcachedir "$1"`

2277 if [ x"$1" = x"user" ]; then

2278 hrb="$myname?home"

2279 deficon=person-default.png

2280 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2281 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2282 gcs=gecos

2283 else # if group

2284 hrb="$myname?grp"

2285 deficon=group-default.png

2286 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2287 gcs=name

2288 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2289 if [ -n "$tagline" ]; then

2290 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2291 ## err tagconv=$tagconv

2292 fi

2293 fi

2294 if [ ! -d $dir ]; then

2295 mkdir -p $dir

2296 fi

2297 if [ ! -s $dir/$deficon ]; then

2298 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2299 fi

2300 if [ -n "$2" ]; then

2301 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2302 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2303 fi

2304 tag=`getpar tag` tag2=`getpar tag2`

2305 if [ x"$tag" = x"NULL" ]; then

2306 tag="" tag2=""

2307 fi

2308 if [ -n "$tag$tag2" ]; then

2309 tag=${tag:-$tag2}

2310 qtag=`sqlquote "$tag"`

2311 cond2="tag=$qtag"

2312 fi

2313 if [ -n "$cond1$cond2" ]; then

2314 cond="$cond1${cond2:+ AND $cond2}"

2315 cond="WHERE ${cond# AND }"

2316 fi

2317

2318 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2319 # grpは呼出し元の動的スコープ変数でよくないな...

2320 ##qgrp=`sqlquote $grp`

2321 getgrp="(select gname from grp where rowid=${rowid:--1})"

2322 sql="select a.rowid, a.$link,

2323 coalesce(b.$gcs, a.$nm) as nick,

2324 quote(a.$nm) as qname,

2325 (SELECT val FROM ${tbl}_s

2326 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2327 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2328 || case when a.$nm in (select gname from grp)

2329 then printf('(%d名)',

2330 (select count(user) from grp_mem where gname=a.$nm))

2331 else ' <'||a.$nm||'>'

2332 end

2333 as name,

2334 b.tag,

2335 case when a.$nm in (select user from grp_adm

2336 where gname=$getgrp) then '管理者'

2337 when '$user' in (select user from grp_adm where gname=a.$nm)

2338 then 'ADMIN'

2339 when '$user' in (select user from grp_mem where gname=a.$nm)

2340 then 'Member'

2341 when '$iamowner' = '' then ''

2342 else ',not='||a.rowid end as ownerlink,

2343 CASE '$entity'

2344 WHEN 'グループ'

2345 THEN coalesce(

2346 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2347 'open')

2348 ||

2349 CASE WHEN '$user'

2350 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2351 THEN ' ismember'

2352 ELSE ''

2353 END

2354 ELSE 'user'

2355 END regmode

2356 from $tbl a left join

2357 (select $nm as name,

2358 max(case key when 'gecos' then val end) as gecos,

2359 max(case key when 'tag' then val end) as tag,

2360 max(case key when 'mtime' then val end) as mtime,

2361 max(case key when 'wtime' then val end) as wtime,

2362 max(case key when 'login' then val end) as login

2363 from ${tbl}_s group by $nm)

2364 b on a.$nm=b.name $cond $3

2365 order by b.wtime desc, b.login desc,

2366 b.mtime desc, b.tag desc, a.rowid asc"

2367 # Give precedence to newer maintained groups (2016-09-24)

2368 # Note that mtime is stored only in grp_s.

2369 ## err LE:sql.1="$sql"

2370 total=`query "with x as ($sql) select count(*) from x;"`

2371 echo "${entity} 一覧" | html h2

2372 echo '<div class="listentry">' # List-entry div

2373 # Show owner/member filter button

2374 METHOD=GET

2375 hiddens="$hiddens

2376 `cgi_hidden kwd \"$kwd\"`

2377 `cgi_hidden stage \"$stage\"`"

2378 if [ x"$tbl" = x"grp" ]; then

2379 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2380 fh="<select name=\"tag\">$nl"

2381 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2382 for l in $args; do

2383 val=${l#*=} tname=${l%=*}

2384 if [ x"$val" = x"$tag" ]; then

2385 s=" selected"

2386 selectedtags="(種別[${tname}]のみ)"

2387 else

2388 s=""

2389 fi

2390 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2391 done

2392 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2393 cat<<-EOF

2394 <form action="$myname" method="$METHOD">

2395 </form>

2396 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2397 for="ismembtn">参加中以外隠す</label>

2398 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2399 for="isadmbtn">管理者参加以外隠す</label>

2400 EOF

2401 # limit=3

2402 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2403 fi

2404 if [ $total -gt $limit ]; then

2405 echo '<div>'

2406 METHOD=GET cgi_form $stage <<EOF

2407 $form

2408 <label>次の語を含む${entity}で検索:

2409 `cgi_text kwd "$kwd"`</label>

2410 $hiddens

2411 EOF

2412 echo '</div>'

2413 else

2414 echo $selectedtags | html p

2415 fi

2416 cat<<EOF

2417 <form action="$myname" method="$METHOD">

2418 <p>${total}件中の<input class="hidesub" type="text" name="start"

2419 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2420 <input type="submit" value="確定"></p>

2421 </form>

2422 EOF

2423 if [ $((offset+limit)) -lt $total ]; then

2424 nextbtn=$(

2425 cat<<EOF

2426 <div class="right clear"><form action="$myname" method="$METHOD">

2427 `cgi_submit 次の${limit}件`

2428 $hiddens

2429 `cgi_hidden offset $((offset + limit))`</form></div>

2430 EOF

2431 )

2432 fi

2433 if [ $offset -gt 0 ]; then

2434 prevbtn=$(

2435 cat<<EOF

2436 <form action="$myname" method="$METHOD">

2437 `cgi_submit 前の${limit}件`

2438 $hiddens

2439 `cgi_hidden offset $((offset - limit))`</form>

2440 EOF

2441 )

2442 fi

2443 pnbtn="$nextbtn$prevbtn"

2444 echo $pnbtn

2445

2446 ## err ListEntry: `echo "$sql"\;`

2447 # sq $db here??? 2016-11-28

2448 query "$sql limit $limit ${offset:+offset $offset};" \

2449 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2450 # err name=$name owner=$ownerp lnk=$lnk

2451 # err newlnk=$lnk regmode=$regmode

2452 icondir=$dir/$id

2453 # Pick up only last icon

2454 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2455 <p class=\"tag _$tag\">$tag</p>" \

2456 | _m4 $tagconv

2457 if [ -n "$NOSPEEDUP" ]; then

2458 files=`getvalbyid $tbl profimg $id $icondir`

2459 if [ -n "$files" ]; then

2460 icon=`echo "$files"|tail -1`

2461 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2462 else

2463 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2464 fi

2465 elif [ -n "$icon" -a -s "$icon" ]; then

2466 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2467 else

2468 cond="$nm=$qname"

2469 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2470 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2471 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2472 # err "img=[$img]"

2473 if [ -n "$img" ]; then

2474 echo "<a href=\"$hrb+$lnk\">"

2475 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2476 echo "</a>"

2477 else

2478 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2479 fi

2480 fi

2481 echo "<br>$name${ownerp:+<br>($ownerp)}"

2482 echo "</div>"

2483 done

2484 echo "</div>" # End of List-entry div

2485 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2486 )

2487 listmember() {

2488 listentry user "$@"

2489 }

2490 listgroup() {

2491 listentry group "$@"

2492 }

2493 hexteams() { # $1=gname, $2(optional)=user

2494 cond=${2:+" AND user='$2'"}

2495 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2496 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2497 }

2498 showgroup() { # $1=group-rowid

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

2500 grid=`getpar grid`

2501 grid=${grid%%[!0-9]*}

2502 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2503 else

2504 grid=$1

2505 fi

2506 grp=`getgroupbyid $grid`

2507 qgrp=`sqlquote "$grp"`

2508 htmlgrp=`echo "$grp"|htmlescape`

2509 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2510 if isgroup "$grp"; then

2511 tf=$tmpd/title.$$

2512 sf=$tmpd/search.$$

2513 bodyclass=`query "SELECT val FROM grp_s

2514 WHERE gname=$qgrp AND key='regmode';"`

2515 if ismember "$user" "$grp"; then

2516 ismember="ismember"

2517 bodyclass="$bodyclass${bodyclass:+ }ismember"

2518 else

2519 ismember="" # bodyclass="group"

2520 fi

2521 bodyclass="$bodyclass grouphome"

2522 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2523 echo "グループ $htmlgrp" > $tf

2524

2525 showgroupsub $formdir/grp.def "$grid" | \

2526 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2527 -D_FORM_="syscmd(\`cat')" \

2528 -D_BODYCLASS_="$bodyclass" \

2529 -D_DUMPTABLE_="" \

2530 $htmlheader $sf $layout/form+dump.m4.html

2531 # $htmlheader $layout/form+dump.m4.html

2532 # $htmlheader is defined in grp()

2533 else # if $grp is removed at par2table

2534 listgroup

2535 fi

2536 }

2537 showgroupsub() {

2538 # $1=def-file $2=group-rowid

2539 # Using $ismember

2540 rowid=$2

2541 grp=`getgroupbyid $2`

2542 qgrp=`sqlquote "$grp"`

2543 td=`getcachedir grp/"$2"`

2544 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2545 if [ -z "$rowid" ]; then

2546 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2547 #grp=`sq $db "select gname from grp where rowid=$grp"`

2548 echo "showgroupsub: invalid argument($1 $2)" | html p

2549 return

2550 fi

2551 val=`getvalbyid grp profimg $rowid $tmpd`

2552 enticond="gname=$qgrp"

2553 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2554 if [ -n "$img" ]; then

2555 cat<<-EOF

2556 <p class="groupimg">

2557 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2558 EOF

2559 fi

2560 echo "<div class=\"noprofimg\">"

2561 viewtable $1 grp $rowid

2562 echo "</div>"

2563 if isgrpowner "$user" "$grp"; then

2564 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2565 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2566 iamowner=$rowid

2567 colmd=" mode"

2568 fi

2569 if [ -n "$ismember" ]; then

2570 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2571 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2572 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2573 cat<<-EOF

2574 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2575 href="?blog+$rowid">グループの新規話題作成</a>

2576 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2577 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2578 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2579 <div class="fold clear">

2580 `cgi_checkbox send yes id="send"`<label

2581 for="send">グループ全員にメッセージ送信</label>

2582 <div>

2583 `cgi_textarea message "" "cols=60"`

2584 `cgi_submit 送信`

2585 `cgi_reset リセット`

2586 </div>

2587 `cgi_hidden grp $rowid`

2588 </div></form>

2589 EOF

2590 fi

2591 # 加入ボタン + 加入者リスト

2592 if [ -n "$ismember" ]; then

2593 ismem='checked' state="(参加中)"

2594 else

2595 nomem='checked' state="(現在非加入)"

2596 fi

2597 # このグループでの加入アドレス

2598 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2599 and key='email';"`

2600 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2601 ## and key='email';"

2602 ##err email=$eml

2603 cat <<EOF

2604 <div class="fold clear">

2605 `cgi_checkbox reg yes id="reg"`<label

2606 for="reg">自身の加入状態を操作する</label>$state

2607 <div>

2608 EOF

2609 cgi_form grp <<EOF

2610 <p>このグループに</p>

2611 <table class="b">

2612 <tr><th>メンバーとして</th><td>

2613 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2614 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2615 <tr><th>参加する場合のメイルアドレス<br>

2616 <small>(メインのアドレスとは違うものにする場合に記入<br>

2617 同じでよい場合は空欄に)</small></th>

2618 <td>`cgi_text email $eml`</td></tr>

2619 </table>

2620 `cgi_hidden grp $rowid`

2621 EOF

2622 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2623 echo "moderated (承認加入の)グループなので実際に参加できるのは

2624 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2625 fi

2626 echo '</div></div>'

2627 echo '<h2>話題一覧</h2>'

2628 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2629 cat<<-EOF

2630 <form class="summary" action="$myname" title="$thelp">

2631 `cgi_hidden owner "$grp"`

2632 `cgi_hidden kwd "@week"`

2633 `cgi_hidden stage searchart`

2634 `cgi_submit "一週間のまとめ"`

2635 </form>

2636 EOF

2637 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2638 colstate="state:稼動状態:frozen=rowclass=凍結"

2639 DT_CHLD=article:blogid \

2640 DT_QOWNER="$qgrp" \

2641 DT_VIEW=replyblog dumptable html blog \

2642 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2643

2644 getgname="(select gname from grp where rowid=$rowid)"

2645 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2646 cm="?commission+$rowid"

2647 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2648 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2649 # team list

2650 hexteams=`hexteams "$grp"`

2651 if [ -n "$hexteams" ]; then

2652 echo "チーム一覧" | html h2

2653 echo '<div class="dumptable"><table class="b">'

2654 sq $db -html -header<<-EOF

2655 SELECT val TEAM,

2656 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2657 MEMBERS

2658 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2659 EOF

2660 echo '</table></div>'

2661 fi

2662 }

2663 grp_getbodyclass() {

2664 # Get css class name for document.

2665 # `moderated' for moderated groups

2666 # `ismember' for groups where user belongs

2667 # $1=GroupName (w/o quote)

2668 # $user=userNameCurrentlyLogin

2669 ## err grp_getbodyclass: 1="$1"

2670 qgrp=`sqlquote "$1"`

2671 query<<-EOF

2672 SELECT coalesce(

2673 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2674 'open')

2675 ||

2676 CASE WHEN '$user'

2677 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2678 THEN ' ismember'

2679 ELSE ''

2680 END;

2681 EOF

2682 }

2683 grpaction() { # $1=group-rowid

2684 err GRP_ACTION:IN

2685 grid=${1:-`getpar grp`}

2686 grp=`getgroupbyid "$grid"`

2687 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2688 if [ -z "$grp" ]; then

2689 echo "無効な指定です。" | html p; return

2690 fi

2691 if ! ismember $user "$grp"; then

2692 echo "加入者のみに許可された操作です。" | html p; return

2693 fi

2694 echo "グループ $grp 個別選択操作" \

2695 | _m4 -D_TITLE_="syscmd(\`cat')" \

2696 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2697 $layout/html.m4.html

2698

2699 isowner=""

2700 isgrpowner "$user" "$grp" && isowner="yes"

2701 usel=`getpar usel`

2702 if [ -n "$usel" ]; then

2703 uids=$(echo `echo $usel`|tr ' ' ',')

2704 ## err grpaction-1: grp=$grp, `echo $sql`

2705 text=`getpar text|tr -d '\r'`

2706

2707 rm=`getpar rm` cfm=`getpar confirm`

2708 ## err rm=$rm cfm=$cfm

2709 if [ x"$rm" = x"yes" ]; then

2710 if [ "$isowner" ]; then

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

2712 # Eliminate

2713 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2714 for tbl in grp_mem grp_mem_s grp_mem_m; do

2715 sql="delete from $tbl $cond;"

2716 # echo "sql=$sql"

2717 query "$sql"

2718 err rmGRPuser "$sql"

2719 done

2720 num=`query "select count(*) from user where rowid in ($uids);"`

2721 #err num=$num

2722 if [ 0$num -gt 0 ]; then

2723 sql="select coalesce(b.val,a.name) from user a left join \

2724 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2725 # err `echo "$sql"`

2726 html pre<<EOF

2727 以下の${num}名のグループ $grp 登録を解除しました。

2728 `query "$sql"`

2729 EOF

2730 fi

2731 else

2732 echo "確認のチェックがないのでやめておきます。" | html p

2733 return

2734 fi

2735 else # not Group Owner

2736 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2737 return

2738 fi

2739 cat<<EOF

2740

2741 EOF

2742 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2743 if [ -z "$text" ]; then # if msg is empty

2744 echo "なにかメッセージを..." | html p

2745 return 0

2746 fi

2747 gecos=`gecos $user`

2748 safegc=`echo "$gecos" | tr -d '<>@'`

2749 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2750 fromad=`myemail4group "$grp"`

2751 ###mail_from="$safegc <$fromad>"

2752 mail_from="$safegc <$user>" # TEST: 2020/5/13

2753 test -n `getpar sender` &&

2754 export SENDER=$user # TEST: 2020/5/15

2755 replyto=$fromad

2756

2757 ## Start parse of attachment files

2758 if [ -n "`getpar email`" ]; then

2759 ar=`getpar supprcpt`

2760 if [ -n "$ar" ]; then

2761 for a in $ar; do

2762 if checkdomain "$a"; then

2763 supprcpt="$supprcpt $a"

2764 else

2765 err "SupprcptErr=[$a] by $user"

2766 removercpt="$removercpt $a"

2767 fi

2768 done

2769 fi

2770 subj=`getpar subject`

2771 afiles=""

2772 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2773 do

2774 f=$tmpd/$fn

2775 if [ -s $f ]; then

2776 afiles=$afiles"${afiles:+ }$f"

2777 fi

2778 done

2779 else

2780 preface=$(cat <<-EOF

2781 $url

2782 のグループ「$grp」のメンバーである $gecos さんから、

2783 あなた宛のメッセージです。

2784 ----------------------------------------------------------

2785 EOF

2786 )

2787 fi

2788 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2789 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2790 subj="${subj:-$gecos さんからのメッセージ}"

2791 REPLYTO=$replyto

2792 MAIL_FROM=$mail_from

2793 export REPLYTO SMAIL_TO MAIL_FROM

2794 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2795 for r in $rcpts; do

2796 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2797 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2798 else

2799 # Show sender and rcpts address for guest

2800 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2801 fi

2802 if [ -n "$afiles" ];then

2803 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2804 else

2805 smail "$r" "$subj"

2806 fi <<EOF

2807 ${preface:+$preface$nl}$text

2808 EOF

2809 done

2810 if [ $? = 0 ]; then

2811 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2812 sql="select coalesce(b.val, a.name) from

2813 (select name from user where rowid in ($uids)) a

2814 left join user_s b on a.name=b.name and b.key='gecos';"

2815 html pre<<EOF

2816 `query "$sql"`

2817 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2818 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2819 EOF

2820 err SendDone: `echo $sql`

2821 fi

2822 elif [ x"$rm" = x"commission" ]; then

2823 grp_reg_adm $grid $usel

2824 elif [ x"$rm" = x"addteam" ]; then

2825 team=`getpar team|sed "s/'/''/g"` # for single quotation

2826 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2827 if [ x"$team" != x"$newteam" ]; then

2828 echo "チーム名に使えない文字を除去しました" | html p

2829 team=$newteam

2830 fi

2831 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2832 cat<<-EOF | html p

2833 有効なチーム名を入力してください。

2834 カンマだけ、「なし」という名前は使えません。

2835 EOF

2836 echo "有効なチーム名を入力してください。" | html p

2837 else

2838 grp_add_team $grid "$team" $usel

2839 fi

2840 elif [ x"$rm" = x"rmteam" ]; then

2841 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2842 rmteam=`getpar rmteam|sed "s/'/''/g"`

2843 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2844 gname='$grp' AND user='$user' AND key='team'\

2845 AND val='$rmteam';\"`" ]; then

2846 grp_rm_team $grid "$rmteam" $usel

2847 else

2848 echo "所属していないチームの除去操作はできません。"|html p

2849 fi

2850 else

2851 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2852 fi

2853 fi

2854 fi

2855 # POST count summary

2856 from=`getpar from`; to=`getpar to`

2857 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2858 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2859 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2860 # New entry

2861 sql="WITH mems AS (

2862 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2863 ON gm.user=g.name

2864 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2865 ), target_article AS (

2866 SELECT id FROM article_s

2867 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2868 ), posts AS (

2869 SELECT author, count(author) post

2870 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2871 WHERE blogid IN (SELECT id FROM blog_s

2872 WHERE key='owner'

2873 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2874 AND key='text'

2875 GROUP BY author

2876 ), teams AS (

2877 SELECT user, group_concat(val, ', ') team

2878 FROM grp_mem_m

2879 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2880 AND key='team'

2881 GROUP BY user

2882 ), user_post AS (

2883 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2884 FROM mems m LEFT JOIN posts

2885 ON m.name=posts.author

2886 GROUP by m.rowid

2887 )

2888 SELECT

2889 CASE

2890 WHEN (SELECT user FROM grp_adm

2891 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2892 AND user=up.name) IS NOT NULL

2893 then 'k'

2894 ELSE ''

2895 END || rowid || ',' || gecos NAME,

2896 post POST, team _TEAM_

2897 FROM user_post up LEFT JOIN teams t

2898 ON up.name=t.user

2899 ORDER BY gecos;"

2900 ## err grpaction: "`echo \"$sql\"`"

2901 tf=$tmpd/title.$$

2902 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2903 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2904 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2905 for=\"cmadmin\">管理者委任</label>

2906 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2907 </p></div>"

2908 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2909 title=\"Shortcut: g${nl}Remove from the Group\"

2910 for=\"conf\">GRP登録解除</label>

2911 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2912 <p>この操作による通知は本人に行きません。

2913 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2914 </div>"

2915 # Get team list to which current user belongs into $hexteams

2916 allhexteams=$(hexteams "$grp")

2917 if [ -n "$isowner" ]; then

2918 myhexteams="$allhexteams" # admin can remove all teams' attr

2919 else

2920 myhexteams=$(hexteams "$grp" "$user")

2921 fi

2922 if [ -n "$myhexteams" ]; then

2923 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2924 title=\"Shortcut: s${nl}Strip a team tag from\"

2925 for=\"cmrmteam\">チーム属性除去</label>

2926 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2927 を除去します: `cgi_checkbox teamconfirm yes` 確認

2928 <p>この操作による通知は本人に行きません。

2929 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2930 </div>

2931 "

2932 fi

2933 stf=$tmpd/selteam.$$

2934 cgi_select_h selteam "5445414d" $allhexteams > $stf

2935 b1='<label> <input type="checkbox" name="usel" value="'

2936 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2937 br='<span id="reverse" title="Reverse Selection"></span>'

2938 #b2='"> <span>' b3='</span></label>'

2939 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2940 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2941 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2942 cgi_form grpaction<<EOF \

2943 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2944 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2945 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2946 | _m4 -D_TITLE_="spaste(\`$tf')" \

2947 -D_SUBTITLE_="チェック後操作ボタン" \

2948 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2949 $layout/form+dump.m4.html \

2950 | _m4 -D_TEAM_="spaste(\`$stf')"

2951 <p>下でチェックした人を対象として:</p>

2952 <div class="foldtabs">

2953 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2954 title="Shortcut: a${nl}Add a team tag to"

2955 for="cmteam">同じチーム属性を付与</label>

2956 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2957 `cgi_datalist_h teams $allhexteams`

2958 </div>

2959 ${rmteammsg}

2960 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2961 title="Shortcut: d${nl}DirectMail to"

2962 for="sendmsg" title="Direct Message">DM送信</label>

2963 <div>

2964 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2965 title="Using email format">email書式を使う</label>

2966 <div class="folded">

2967 <table>

2968 <tr><td>From: </td><td>$user</td></tr>

2969 <tr><td>このFrom:で送る</td>

2970 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2971 </td></tr>

2972 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2973 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2974 <tr><td>ファイル添付: </td>

2975 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2976 </table>

2977 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2978 </div>

2979 <div>本文:`cgi_textarea text "" cols=72`

2980 </div>

2981 </div>

2982 ${isowner:+$cmmsg$excmsg}

2983 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2984 </div>

2985 <h4>$grp 参加者一覧</h4>$fromtonote

2986 <table class="td2r thl">

2987 `sq $db -header -html "$sql"`

2988 </table>

2989 `cgi_hidden grp $grid`

2990 `cgi_hidden myuid $myuid id="myuid"`

2991 EOF

2992 }

2993 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2994 # Create TEMPORARY VIEW

2995 query<<EOF

2996 CREATE TEMPORARY VIEW writeusers AS

2997 SELECT DISTINCT author FROM article

2998 WHERE id in (

2999 select id from article where blogid=(select id from blog where rowid=$1)

3000 );

3001 CREATE TEMPORARY VIEW movablegroups AS

3002 SELECT g.rowid growid , g.gname

3003 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3004 ON grp.gname=gm.gname -- そのユーザが属している

3005 AND user='$user') g -- グループに絞る

3006 WHERE (SELECT author FROM writeusers

3007 EXCEPT

3008 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3009 IS NULL;

3010 $2

3011 EOF

3012 }

3013 sql4readableblogs() {

3014 # Create view of blogs that can be readable to $user

3015 # Blog is readable when:

3016 # 1: blog owner is an user

3017 # 2: else, 2.1: owner-group where the $user belongs

3018 # 2.2: else, owner-group is not moderated

3019 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3020 cat<<EOF ## | tee tmp/sql.out

3021 CREATE TEMPORARY VIEW readableblogs AS

3022 SELECT blog.rowid rid, id, author

3023 FROM blog

3024 NATURAL JOIN

3025 (SELECT id,

3026 max(CASE key WHEN 'owner' THEN val END) owner,

3027 max(CASE key WHEN 'mode' THEN val END) mode

3028 FROM blog_s GROUP by id) bs

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

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

3031 WHEN (SELECT val FROM grp_s

3032 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3033 AND

3034 (SELECT user FROM grp_mem

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

3036 THEN 0

3037 WHEN mode IN ('quiz', 'enquete')

3038 THEN 0 -- "quiz" mode blog cannot be searched

3039 ELSE 1

3040 END;

3041 EOF

3042 }

3043 editheading() { # $1=rowid-of-heading

3044 rowid=${1%%[!A-Z0-9a-z_]*}

3045 if [ -z "$rowid" ]; then

3046 echo "話題番号が未指定です。" | html p

3047 return

3048 fi

3049 owner=`getvalbyid blog owner $rowid`

3050 title=`getvalbyid blog title $rowid`

3051 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3052 | _m4 -D_TITLE_="修正" \

3053 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3054 -D_BLOGS_="" -D_DUMPTABLE_="" \

3055 -D_FORM_="syscmd(\`cat')" \

3056 $layout/html.m4.html $layout/form+dump.m4.html

3057 # Move to group

3058 if isuser "$owner"; then

3059 crview4article $rowid

3060 n=`query "SELECT count(*) FROM writeusers;"`

3061 ## err N=$n

3062 if [ $((n)) -gt 0 ]; then

3063 ## err ROWID=$rowid

3064 sql="SELECT growid || ':' || gname FROM movablegroups;"

3065 cat<<-EOF

3066 <div class="fold">

3067 `cgi_checkbox mv send id="mv"`<label

3068 for="mv">この話題をグループ所有に移動する</label>

3069 <div>

3070 <form action="?mvart" method="POST" enctype="multipart/form-data">

3071 移動先グループ:

3072 <select name="mv2grp">

3073 EOF

3074 query ".mode html"

3075 query<<-EOF |

3076 $sql

3077 .mode list

3078 EOF

3079 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3080 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3081 cat<<-EOF

3082 </select>

3083 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3084 そのグループに加入しているものに限られます)</p>

3085 <p>`cgi_checkbox cfm yes`<label>確認

3086 (この操作は元に戻すことができません)</label></p>

3087 `cgi_hidden blogrowid $rowid`

3088 `cgi_submit 移動`

3089 `cgi_reset Reset`

3090 </form>

3091 </div>

3092 </div>

3093 EOF

3094 fi

3095 # end of isuser "$owner"

3096 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3097 [ -n "$hexteams" ];}; then

3098 none="`echo なし|hexize`"

3099 cat<<-EOF

3100 <div class="fold">

3101 `cgi_checkbox mv2team send id="mv2team"`<label

3102 for="mv2team">この話題を以下のチームのものにする</label>

3103 <div><p>現在の所属チーム設定:

3104 `query "SELECT

3105 coalesce((SELECT val FROM blog_s

3106 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3107 AND key='team'),

3108 ':なし');"`</p>

3109 <form action="?mvart" method="POST" enctype="multipart/form-data">

3110 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3111 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3112 `cgi_hidden blogrowid $rowid`<br>

3113 `cgi_submit 移動`

3114 `cgi_reset Reset`

3115 </form></div></div>

3116 EOF

3117 fi

3118 }

3119 mvart() { # move diary to some group or team

3120 # or move blog of group to team which belong to the group

3121 blogrowid=`getpar blogrowid`

3122 cfm=`getpar cfm`

3123 ##### echo move blog:$blogrowid to $mv2grp | html p

3124 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3125 . ./s4-blog.sh

3126 if [ -z "$blogrowid" ]; then

3127 echo "無効な指定です(mvart)。" | html p

3128 return

3129 elif [ x"$cfm" != x"yes" ]; then

3130 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3131 elif { mv2grp=`getpar mv2grp`

3132 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3133 [ -n "$mv2grp" ]; }; then

3134 crview4article $blogrowid

3135 ########## TRANSACTION BEGIN

3136 query "BEGIN;"

3137 n=`query "SELECT count(*) FROM writeusers;"`

3138 ## err Nwriteuser=$n

3139 if [ $((n)) -gt 0 ]; then

3140 query<<-EOF

3141 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3142 WHERE key='owner'

3143 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3144 AND $mv2grp IN (SELECT growid FROM movablegroups);

3145 EOF

3146 fi

3147 query "END;"

3148 ########## TRANSACTION END

3149 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3150 [ -n "$mv2team" ];}; then

3151 # blog owner can move it to ANY team

3152 case "$mv2team" in

3153 'なし')

3154 cat<<-EOF

3155 DELETE FROM blog_s

3156 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3157 AND key='team';

3158 EOF

3159 ;;

3160 "") ;;

3161 *)cat<<-EOF

3162 BEGIN;

3163 REPLACE INTO blog_s(id, key, val)

3164 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3165 'team', '$mv2team');

3166 REPLACE INTO blog_s(id, key, val)

3167 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3168 'notify', 'all'); -- Change notify to all

3169 END;

3170 EOF

3171 esac | query

3172 fi

3173 blog_reply $blogrowid

3174 echo yes | html p

3175 }

3176 editart() { # $1=article-rowid $2=blogrowid

3177 rowid=${1%%[!A-Z0-9a-z_]*}

3178 blogrowid=${2%%[!A-Z0-9a-z_]*}

3179 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3180 echo "表示する記事番号が未指定です。" | html p

3181 return

3182 fi

3183 owner=`getvalbyid blog owner $blogrowid`

3184 title=`getvalbyid blog title $blogrowid`

3185 author=`getvalbyid article author $rowid`

3186 ## err EDITart: owner=$owner, author=$author

3187 if isgrpowner "$user" "$owner"; then

3188 : EDIT OK

3189 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3190 echo "本人か所有者しか編集できません." | html p

3191 return

3192 fi

3193 aid=`query "select id from article where rowid=$rowid;"`

3194 tmpout=$tmpd/editart.$$.out

3195 GF_ACTION="?replyblog+$blogrowid#$aid" \

3196 edittable $formdir/article.def article $rowid \

3197 > $tmpout

3198 rm -f /tmp/editart.out

3199 # Cannot use pipelining to m4 with genform() because of stdin stack

3200 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3201 -D_FORM_="syscmd(cat $tmpout)" \

3202 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3203 -D_BLOGS_= -D_DUMPTABLE_= \

3204 $layout/html.m4.html $layout/form+dump.m4.html

3205 }

3206 send2mem() {

3207 rowid=`getpar grp`

3208 rowid=${rowid%%[!0-9]*} # Cleaning

3209 if [ -z "$rowid" ]; then

3210 echo "グループが未指定です。" | html p

3211 return

3212 fi

3213 message=`getpar message`

3214 if [ -z "$message" ]; then

3215 echo "文章を入れてください。" | html p

3216 return

3217 fi

3218 grp=`getgroupbyid $rowid`

3219 members=`collectemail $grp`

3220 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3221 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3222 mailfrom="`gecos "$user"` <$mailfrom>"

3223 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3224 msg=$(cat<<-EOF

3225 $urlbase?grp+$rowid

3226 グループ $grp に所属する

3227 `gecos $user` さんよりメッセージ:

3228

3229 $message

3230 EOF

3231 )

3232 # smail rcpt subj (file)

3233 for m in $members; do

3234 echo "$msg" |

3235 MAIL_FROM=$mailfrom \

3236 SENDER=$noreply \

3237 REPLYTO=$mailfrom \

3238 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3239 smail "$m" "$sj"

3240 done

3241 cat<<EOF

3242 <p>以下のユーザに送信しました。</p>

3243 <pre>

3244 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3245 </pre>

3246 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3247 EOF

3248 }

3249 joingrpadmit() {

3250 # $1=yes/no $2=session-key

3251 if [ -z "$2" ]; then

3252 echo "bye bye" | html p; return

3253 fi

3254 t_usr=`session=$2 getpar user`

3255 t_grp=`session=$2 getpar group`

3256 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3257 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3258 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3259 echo "無効な加入依頼です。" | html p

3260 echo "有効期限が切れたか、

3261 他の管理者がいる場合は処理済みの可能性があります。" | html p

3262 return

3263 fi

3264 if ! isgrpowner "$user" "$t_grp"; then

3265 echo "グループ管理者のみの機能です。" | html p; return

3266 fi

3267 case $1 in

3268 yes) joingrp "$t_grp" "$t_usr" yes ;;

3269 no) joingrp "$t_grp" "$t_usr" no ;;

3270 *)

3271 echo "無効な指定です($1)。" | html p

3272 return ;;

3273 esac

3274 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3275 rcpts="`getgroupadminmails $t_grp` $user"

3276 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3277 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3278 に

3279 $t_usr

3280 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3281 しました。"

3282 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3283 query "delete from session where id='$2';"

3284 echo "$body" | html p

3285 }

3286

3287 joingrprequest() {

3288 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3289 jss="joingrp-`date +%s`-`genrandom 12`"

3290 addsession $jss +${memoplimitdays}days

3291 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3292 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3293 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3294 $url

3295 $user さんから

3296 グループ $1

3297 に加入依頼がありました。

3298

3299 承認する:

3300 $urlbase?joingrpadmit+yes+$jss

3301

3302 白紙に戻す:

3303 $urlbase?joingrpadmit+no+$jss

3304 EOF

3305 echo "管理者に加入依頼を出しました。

3306 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3307 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3308 }

3309 joingrp() {

3310 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3311 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3312 if isgrpowner "$user" "$1"; then

3313 isowner="yes"

3314 elif [ -n "$5" ]; then

3315 isowner="yes"

3316 else

3317 isowner=""

3318 fi

3319 ## err jg:isgrpowner: isowner="$isowner"

3320 if [ -n "$isowner" ]; then

3321 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3322 elif [ x"$2" != x"$user" ]; then # if user is not login user

3323 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3324 return

3325 elif [ x"$3" = x"no" ]; then

3326 : # Do not pursue those who leave

3327 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3328 : # Member can change own email address for the joining moderated group

3329 else # adding user is $user itself

3330 case `getgroupattr $1 regmode` in

3331 moderated)

3332 joingrprequest "$@" # Request only

3333 return

3334 ;;

3335 *)

3336 ;;

3337 esac

3338 fi

3339 qgname=`sqlquote "$1"`

3340 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3341 cond="where gname=$qgname and user='$2'"

3342 if [ x"$3" = x"yes" ]; then

3343 query "replace into grp_mem values($qgname, '$2');"

3344 # Notify joingrp to admin

3345 action="に加入しました。"

3346 if [ -n "$4" ]; then

3347 if msg=`emaildomaincheck "$4"`; then

3348 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3349 'string', '$4', NULL);"

3350 else

3351 echo $msg

3352 fi

3353 else

3354 query "delete from grp_mem_s $cond and key='email';"

3355 fi

3356 if [ -n "$5" ]; then # as ADMIN

3357 # Coming here means newly created group

3358 sql="select case\

3359 when (select count(*) from grp_mem where gname=$qgname)=1\

3360 then (select user from grp_mem\

3361 where gname=$qgname and user='$user')\

3362 else '' end; "

3363 err NewGrpChk: $sql

3364 if [ -n "`query \"$sql\"`" ]; then

3365 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3366 query "replace into grp_adm values($qgname, '$user');"

3367 fi

3368 fi

3369 else

3370 query "begin;

3371 delete from grp_mem $cond;

3372 delete from grp_mem_s $cond;

3373 delete from grp_mem_m $cond;

3374 delete from grp_adm $cond;

3375 delete from grp_adm_s $cond;

3376 delete from grp_adm_m $cond;

3377 end;"

3378 action="から脱退しました。"

3379 fi

3380 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3381 $url?grp+$grid

3382 $user (`gecos $user`)さんが

3383 グループ $1

3384 $action

3385 EOF

3386 }

3387 grp_add_team() (

3388 # $1=grp-rowid $2=team $3...=user-rowid(s)

3389 grp=`getgroupbyid $1`

3390 team=$2; shift; shift

3391 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3392 { echo "BEGIN;"

3393 for user; do

3394 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3395 '$grp',\

3396 (SELECT name FROM user WHERE rowid=$user),\

3397 'team', 'string', '$team');"

3398 done

3399 echo "END;"

3400 } | query

3401 )

3402 grp_rm_team() (

3403 # $1=grp-rowid $2=team $3...=user-rowid(s)

3404 grid=$1

3405 qgrp=$(sqlquote "`getgroupbyid $grid`")

3406 team=$2; shift; shift

3407 [ -z "$grid" -o -z "$team" ] && return

3408 { echo "BEGIN;"

3409 for user; do

3410 echo "DELETE FROM grp_mem_m\

3411 WHERE gname=$qgrp \

3412 AND user=(SELECT name FROM user WHERE rowid=$user)\

3413 AND key='team' AND val='$team';"

3414 done

3415 cat<<-EOF

3416 DELETE FROM blog_s

3417 WHERE rowid=(

3418 SELECT rowid

3419 FROM blog_s a

3420 WHERE key='team'

3421 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3422 AND NOT EXISTS (SELECT * FROM grp_mem_m

3423 WHERE key='team' AND val=a.val -- a.val=team

3424 AND gname = (SELECT val FROM blog_s b

3425 WHERE a.id=b.id AND key='owner')

3426 ));

3427 EOF

3428

3429 echo "END;"

3430 } | query

3431 )

3432 grp_reg_adm() {

3433 # $1=grp-rowid $2...=user-rowid

3434 grid=$1

3435 grp=`getgroupbyid "$1"`

3436 if [ -z "$grp" ]; then

3437 echo "無効なグループIDです" | html p; return

3438 fi

3439 if ! isgrpowner "$user" "$grp"; then

3440 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3441 fi

3442 shift

3443 for urid; do

3444 newadm=`query "select name from user where rowid=$urid;"`

3445 if [ -z "$newadm" ]; then

3446 echo "指定ユーザIDがおかしいようです。" | html p; return

3447 fi

3448 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3449 err ismember $newadm $grp

3450 if ismember $newadm "$grp"; then

3451 # OK, go ahead

3452 getgname="(select gname from grp where rowid=$grid)"

3453 query "replace into grp_adm values($getgname, '$newadm');"

3454 # confirm insertion

3455 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3456 if [ -n "`query \"$sql;\"`" ]; then

3457 echo "追加完了: $newadm" | html p

3458 else

3459 echo "追加失敗($1 $urid)" | html p

3460 fi

3461 fi

3462 showgroup $grid

3463 done

3464 }

3465 dt_rowhack() {

3466 # From: <TR>

3467 # ....

3468 # <TD>rowclass=foo</TD>

3469 # </TR>

3470 # To: <TR class="foo">....<TD>foo</TD></TR>

3471 sed -e '

3472 /^<TR>/ {

3473 :loop

3474 s/\n//

3475 N

3476 /<\/TR>/ {

3477 s/\n//

3478 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3479 n

3480 }

3481 $q

3482 b loop

3483 }'

3484 }

3485 dumptable() {

3486 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3487 # textのフィールドだけ全てダンプにしたほうがいいか

3488 # $DT_VIEW sets link

3489 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3490 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3491 VIEW=${DT_VIEW-replyblog}

3492 if [ -n "$VIEW" ]; then

3493 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3494 fi

3495 sqlfile=$tmpd/dump.sql

3496 : > $sqlfile # ensure to be empty

3497 printf '.mode html\n.header 1\n' > $sqlfile

3498 # $DT_CHLD=ChildTable:BindColumn

3499 if [ -n "$DT_CHLD" ]; then

3500 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3501 cat<<-EOF >> $sqlfile

3502 -- presql

3503 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3504 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3505 EOF

3506 # Speed up counting of new articles

3507 cat<<-EOF >> $sqlfile

3508 -- presql2

3509 DROP TABLE IF EXISTS _counts;

3510 CREATE TEMPORARY TABLE _counts AS

3511 SELECT $_i, count($_i) cnt

3512 FROM $_t GROUP BY $_i;

3513 /* Prepare NEW count table */

3514 CREATE TEMPORARY TABLE _target AS

3515 SELECT b.rowid trowid, b.id

3516 FROM "$2" b JOIN "$2_s" s

3517 ON b.id=s.id AND s.key='owner'

3518 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3519

3520 DROP TABLE IF EXISTS _children;

3521 CREATE TEMPORARY TABLE _children AS

3522 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3523 FROM (SELECT t.trowid, t.id $_i, a.id

3524 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3525 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3526

3527 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3528 DROP TABLE IF EXISTS _news;

3529 DROP VIEW IF EXISTS _news;

3530

3531 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3532 -- INSERT INTO _news

3533 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3534 CREATE TEMPORARY VIEW _news AS

3535 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3536 FROM (SELECT DISTINCT id FROM _target)

3537 a LEFT JOIN

3538 (SELECT $_i, count(ctime) newcnt

3539 FROM _children x

3540 WHERE ctime > coalesce((SELECT time from myacclog

3541 WHERE tblrowid=x.trowid),

3542 '1970-01-01')

3543 GROUP BY $_i) b

3544 ON a.id=b.$_i;

3545 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3546 EOF

3547 # REMOVE next line until 2019/5/1

3548 cntall="(select count($_i) from $_t where $_i=a.id)"

3549 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3550 # REMOVE next assignment until 2019/5/1

3551 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3552 and id in (select id from $_t where $_i=a.id) \

3553 and val > coalesce((select time from myacclog where \

3554 tblrowid=a.rowid),\

3555 '1970-01-01'))"

3556 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3557 cnt="$cntnew as '新着', $cntall as '総数',"

3558 dt_class=" td2r td3r dumpblogs"

3559 fi

3560 # Construct join expression

3561 eav="" scols=""

3562 pk=`gettblpkey $2`

3563 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3564 substr=${substr:-%s}

3565 for col in ${3:-`gettbl_s_cols $2`}; do

3566 valvar=val

3567 case $col in

3568 gecos) scols="$scols${scols:+, }${col#}"

3569 continue ;; # built-in column name

3570 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3571 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3572 case "$as" in

3573 *:*=*) cnd=${as#*:}

3574 h=${cnd%%=*} v=${cnd#*=}

3575 h=`sqlquotestr "$h"`

3576 v=`sqlquotestr "$v"`

3577 valvar="CASE val WHEN $h THEN $v END"

3578 as=${as%%:*} ;;

3579 esac

3580 ;;

3581 *) as=${col} ;;

3582 esac

3583 ss=`printf "$substr" "$valvar"`

3584 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3585 scols="$scols${scols:+, }b.$as"

3586 done

3587 #case author when '$user' then a.rowid else '---' end as ID,

3588 if [ -n "$DT_SQL" ]; then

3589 echo "$DT_SQL"

3590 else

3591 cat<<-EOF

3592 SELECT a.rowid as LINK, $cnt $scols

3593 FROM $2 a LEFT JOIN

3594 (SELECT $pk,$eav,

3595 max(CASE key

3596 WHEN 'owner'

3597 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3598 as gecos

3599 FROM ${2}_s c GROUP BY $pk)

3600 b ON a.$pk=b.$pk $4;

3601 EOF

3602 fi >> $sqlfile

3603 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3604 sqlog<<-EOF

3605 *** SQL-file: $sqlfile ***

3606 `cat $sqlfile`

3607 EOF

3608 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3609 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3610 cat $sqlfile >> tmp/sql

3611 # query ".read $sqlfile" > $tmpd/foo

3612 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3613 cp $tmpd/foo tmp/

3614 err "----- `gdate +%FT%T.%3N` ------------555555"

3615 ## $ddd LINE exists at the end of this function

3616 fi

3617 printf '.mode list\n.header 0\n' >> $sqlfile

3618 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3619 <div>

3620 <div class="dumptable">

3621 <table class="b$dt_class">

3622 `query ".read $sqlfile"`

3623 </table>

3624 </div>

3625 </div>

3626 EOF

3627 ### `query ".read $sqlfile"`

3628 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3629

3630 ## REMOVE THIS!

3631 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3632 }

3633

3634 par2table() (

3635 # copy current parameters of par into destination table

3636 # $1=definition-file

3637 # Using $user and $session

3638 # Return value:

3639 # 0: Stored successfully

3640 # 1: Insufficient fillings

3641 # 2: No permission to modify the record

3642 # 3: Invalid rowid

3643 # 4: SUCCESS to delete

3644 # 5: Stop deletion for lack of confirm check

3645 # 6: Password length too short

3646 # 7: Password mismatch

3647 # 8: Old password incorrect

3648 rowid=`getpar rowid`

3649 if [ ! -e $1 ]; then

3650 echo "テーブル定義ファイルが見付かりません" | html p

3651 exit 1

3652 fi

3653 tbl=${1%.def}

3654 tbl=${tbl##*/}

3655 if [ -n "$rowid" ]; then # Modify existing entry

3656 if [ x"$tbl" = x"user" ]; then

3657 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3658 elif [ x"$tbl" = x"grp" ]; then

3659 sql="select gname from $tbl where rowid=$rowid;"

3660 ##err p2t:grp:q $sql

3661 isgrpowner "$user" "`query $sql`" && rowowner=$user

3662 elif [ x"$tbl" = x"blog" ]; then

3663 # Check if owner in blog_s

3664 blogowner=`getvalbyid blog owner "$rowid"`

3665 if isgrpowner "$user" "$blogowner"; then

3666 rowowner=$user

3667 else

3668 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3669 fi

3670 else

3671 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3672 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3673 fi

3674 ### err rowowner=$rowowner

3675 if [ x"$user" != x"$rowowner" ]; then

3676 echo "他人のレコードはいじれないの" | html p

3677 return 2

3678 elif [ -z "$rowowner" ]; then

3679 echo "指定したレコードはないみたい" | html p

3680 return 3

3681 fi

3682 rm=`getpar rm` cfm=`getpar confirm`

3683 # Editing existent entry

3684 if [ x"$rm" = x"yes" ]; then

3685 if [ x"$rm$cfm" = x"yesyes" ]; then

3686 query "delete from $tbl where rowid=$rowid;"

3687 return 4

3688 else

3689 echo "消去確認のチェックがないので消さなかったの..." | html p

3690 return 5

3691 fi

3692 fi

3693 fi

3694

3695 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3696 if [ -n "$rowid" ]; then

3697 # Update of existing record

3698 for col in `gettblcols $tbl`; do

3699 val=`getparquote $col`

3700 [ -z "$val" ] && continue

3701 ## err query "update $tbl set $col=$val where rowid=$rowid"

3702 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3703 sql="update $tbl set $col=$val where rowid=$rowid;"

3704 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3705 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3706 ## User name cannot be changed with interface provided with this

3707 ## script. But we offer the trigger to change owner user

3708 ## of blog_s table.

3709 #err "select quote($col) from $tbl where rowid=$rowid;"

3710 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3711 cat<<-EOF | query

3712 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3713 -- should use EXCLUSIVE transaction outside of this.

3714 SAVEPOINT par2table;

3715 $sql

3716 update blog_s set val=$val

3717 where key='owner' and val=$old;

3718 RELEASE SAVEPOINT par2table;

3719 EOF

3720 ## XX: DIRTY Hack Ends here

3721 ## We should keep blog's owner as a single column which has

3722 ## foreign key constraint with primary key of grp/user.

3723 else

3724 query "$sql"

3725 fi

3726 done

3727 # Then, set up $pval for further insertion of tbl_s and tbl_m

3728 for col in `gettblpkey $tbl`; do

3729 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3730 pval="$pval${pval:+, }\"$val\""

3731 done

3732 else

3733 # New entry

3734 # Generate values() for primary keys

3735 for col in `gettblpkey $tbl`; do

3736 # Genuine primary keys for _m and _s

3737 val=`getvalquote $tbl $col`

3738 [ -z "$val" ] && continue

3739 pval="$pval${pval:+, }$val"

3740 done

3741 ##err pval=$pval

3742 for col in `gettblfkey $tbl`; do

3743 # args for values() to insertion into master table

3744 val=`getvalquote $tbl $col`

3745 [ -z "$val" ] && continue

3746 formaster=$formaster"${formaster:+, }$val"

3747 done

3748 formaster="$pval${formaster:+, }$formaster"

3749 ## err formaster=$formaster

3750 if [ -z "$formaster" ]; then

3751 echo "項目を全て埋めてください" | html pre

3752 return 1

3753 fi

3754 ## err "replace into $tbl values($formaster);"

3755 query "replace into $tbl values($formaster);"

3756 ## Insertion to master table, done

3757 fi

3758

3759 transaction=$tmpd/sqlfile.sql; touch $transaction

3760 for kt in s m; do

3761 tb2=${tbl}_$kt

3762 for col in `gettbl_${kt}_cols $tbl`; do

3763 ptype=`getpartype $col "limit 1"`

3764

3765 # First, check update of existing entries in _m

3766 if [ $kt = m ]; then

3767 # sessID|address.1.22|string|Somewhere-x.y.z

3768 sql=""

3769 ##err dots from query "select var from par where var like '$col.%';"

3770 for v in `query "select var from par where var like '$col.%';"`; do

3771 # v=address.1.22

3772 st_rowid=${v##*.}

3773 origcol=${v%%.*} # original column derived from

3774 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3775 ##case `getpartype $v` in

3776 ## err CASE `gettbl_coltype $tbl/$origcol` in

3777 ## err edit flag = `getpar action.$v`

3778 case `getpar action.$v` in

3779 rm)

3780 if [ x`getpar confirm.$v` = x"yes" ]; then

3781 newsql="delete from $tb2"

3782 else

3783 echo "削除確認未チェック" | html p

3784 fi ;;

3785 edit)

3786 case `gettbl_coltype $tbl/$origcol` in

3787 image|document|binary)

3788 file=$tmpd/`getparfilename $v`

3789 ## err type=file=$file

3790 [ -z "$file" ] && continue

3791 bn=`sqlquotestr "${file##*/}"`

3792 bin="X'"$(hexize "$file")"'"

3793 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3794 type=\"file:$ct\"

3795 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3796 cachedir=`getcachedir "$tbl/$rowid"`

3797 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3798 rm -rf $cachedir

3799 ;;

3800 *)

3801 newsql="update $tb2 set val=(select val from par where var \

3802 like '$col.%.$st_rowid')"

3803 ;;

3804 esac

3805 ;;

3806 *) # maybe "keep", do not modify value

3807 continue

3808 ;;

3809 esac

3810 # err newsql=$newsql

3811 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3812 done

3813

3814 if [ x"$bin" = x"NULL" ]; then

3815 ## err repl:normal sql=`echo $sql`

3816 if [ -n "$transaction" ]; then

3817 cat<<-EOF >> $transaction

3818 $sql

3819 DELETE FROM $tb2 WHERE type='string' AND val='';

3820 EOF

3821 else

3822 query "$sql

3823 delete from $tb2 where type='string' and val='';"

3824 ## err repl:normal done

3825 fi

3826 else

3827 # Binary update line is TOO LONG to pipelining

3828 sqlfile="$tmpd/sqlf.$$"

3829 if [ -n "$transaction" ]; then

3830 echo "$sql" >> $transaction

3831 else

3832 echo "$sql" > $sqlfile

3833 query ".read $sqlfile"

3834 fi

3835 fi

3836 # Rest of kt==m: set multiple mode

3837 nr=`getparcount $col`

3838 else

3839 nr=1 # for kt==s, number of records is 1

3840 fi

3841

3842 i=0

3843 while [ $i -lt $nr ]; do

3844 limit="limit 1 offset $i"

3845 i=$((i+1)) # increase beforehand against continue

3846 val=`getvalquote $tbl $col "$limit"`

3847 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3848 ## err $col=$val

3849 bin=NULL

3850 ## err partype$col=`getpartype $col "$limit"`

3851 case $ptype in

3852 file) file=$tmpd/`getparfilename $col "$limit"`

3853 ## err parfile-$col=$file

3854 [ -z "$file" ] && continue

3855 bin="X'"$(hexize "$file")"'"

3856 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3857 type=\"file:$ct\" ;;

3858 "*"*) continue ;; # foreign table

3859 *) type=\"string\" ;;

3860 esac

3861 case `gettbl_coltype $tbl/$col` in

3862 password) # special care for password

3863 # name={password,pswd1,pswd2}

3864 p1=`getpar pswd1 "$limit"`

3865 if [ -z "$p1" ]; then

3866 continue # SKIP password setting, if p1 is empty

3867 else

3868 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3869 ## err pswd=$pswd

3870 if pwcheck "$pswd"; then

3871 if [ x"$p1" = x"$p2" ]; then

3872 case "$p1" in

3873 ??????????*) ;;

3874 *) echo "パスワードは10字以上にしてください。" | html p

3875 return 6;;

3876 esac

3877 val="\"`echo $p1|mypwhash`\""

3878 else

3879 echo "2つの新パスワード不一致" | html p

3880 return 7

3881 fi

3882 else

3883 echo "旧パスワード違います" | html p

3884 return 8

3885 fi

3886 fi

3887 ;;

3888 esac

3889 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3890 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3891 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3892 if [ x"$bin" = x"NULL" ]; then

3893 ## err Normal-query: `echo $sql`

3894 if [ -n "$transaction" ]; then

3895 echo "$sql" >> $transaction

3896 else

3897 query "$sql"

3898 fi

3899 else

3900 sqlfile="$tmpd/query.$$"

3901 ## err sqlfile=`ls -lF $sqlfile`

3902 if [ -n "$transaction" ]; then

3903 echo "$sql" >> $transaction

3904 else

3905 echo "$sql" > $sqlfile

3906 query ".read $sqlfile"

3907 fi

3908 fi

3909 ## err p2t done

3910 done

3911 done

3912 done

3913 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3914 -- We cannot use transaction here, because groupupdate may use it.

3915 SAVEPOINT pa2table_insert;

3916 .read $transaction

3917 RELEASE SAVEPOINT pa2table_insert;

3918 EOF

3919 return $?

3920 ##err donee

3921 )

3922 genform() {

3923 # $1 = form definition file

3924 # $2, $3 (optional)= table name and ROWID

3925 # If $GF_VIEWONLY set and nonNull, output values without form

3926 # If $GF_ARGS set, use it as content-strings in the form

3927 # If $GF_OWNER set, use it as value of name="owner"

3928 # If $GF_STAGE set, use it as value of name="stage"

3929 forms="" hiddens="" rowid=$3

3930 if [ ! -e "$1" ]; then

3931 echo "そのようなデータベースはないようです($2)。" | html p

3932 return

3933 elif [ -n "$2" ]; then

3934 rec=`query "select * from $2 where rowid='$rowid';"`

3935 if [ -z "$rec" ]; then

3936 pk=`gettblpkey $2`

3937 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3938 rec=`query "select rowid from $2 where $pk='$rowid';"`

3939 rowid=$rec

3940 rec=$3

3941 fi

3942 if [ -z "$rec" ]; then

3943 echo "そんなレコードはないみたいね..." | html p

3944 return

3945 fi

3946 fi

3947 if [ -z "$GF_VIEWONLY" ]; then

3948 rm='<input id="rm" name="rm" type="checkbox"

3949 value="yes"><label for="rm">このエントリの削除</label>

3950 <span>ほんとうに消しますよ(確認)!

3951 <input name="confirm" type=checkbox value="yes">はい</span>'

3952 fi

3953 # Image Cache dir

3954 ## err genform: getcache=$2/$rowid

3955 td=`getcachedir "$2/$rowid"`

3956 while IFS=: read prompt name keytype type args; do

3957 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3958 sp="${args:+ }"

3959 form="" val=""

3960 if [ -n "$rowid" ]; then

3961 # err genform2a: Seeking for "$2.$name, type=$type"

3962 rawval=`getvalbyid $2 $name $rowid $td`

3963 val=`echo "$rawval"|htmlescape`

3964 ## err genform3a: getvalbyid $2 $name $rowid $td

3965 ## err genform3b: val="[$val]" type="$type"

3966 fi

3967 if [ -n "$GF_VIEWONLY" ]; then

3968 is_hidden "$2" "$name" && continue

3969 fi

3970 case "$type" in

3971 text*)

3972 cgiform=cgi_multi_$type

3973 if [ -s $td/$name.count -a -n "$val" ]; then

3974 form=`$cgiform $name $td`

3975 val=$(echo "$val"|

3976 while read fn; do

3977 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3978 </td></tr>$nl"

3979 done)

3980 val="<table>$nl$val$nl</table>"

3981 else

3982 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3983 form=`cgi_$type $name "$rawval" "$args"`

3984 fi

3985 ;;

3986 [Rr][Aa][Dd][Ii][Oo])

3987 fh="<label><input type=\"radio\" name=\"$name\""

3988 form="`echo $args|sed -e \

3989 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3990 ;;

3991 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3992 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3993 ;;

3994 [Ss][Ee][Ll][Ee][Cc][Tt])

3995 fh="<select name=\"$name\">$nl"

3996 form=$(for l in $args; do

3997 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3998 done)

3999 if [ -n "$val" ]; then

4000 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4001 fi

4002 form="$fh$form</select>"

4003 ;;

4004 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4005 if [ -s $td/$name.count ]; then

4006 form=`cgi_multi_file $name $td "$args"`

4007 if [ -n "$val" ]; then

4008 hrfb="$myname?showattc+$2_m"

4009 val=$(echo "$rawval" \

4010 | while read fn; do

4011 data=`percenthex "$td/$fn"`

4012 #ct=`cat $td/$fn.content-type`

4013 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4014 ri=`cat "$td/$fn.rowid"`

4015 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4016 #imgsrc="<img src=\"data:$ct,$data\">"

4017 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4018 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4019 done)

4020 fi

4021 else

4022 form="<input type=\"file\" name=\"$name\" $args>"

4023 if [ -n "$val" ]; then

4024 imgs=$(echo "$rawval"\

4025 |while read fn;do

4026 data=`percenthex "$td/$fn"`

4027 echo "<img src=\"data:image/png,$data\">$fn<br>"

4028 done)

4029 form=$form"<br>$imgs"

4030 val=$imgs # 2015-06-15

4031 else

4032 form="<input type=\"file\" name=\"$name\" $args>"

4033 fi

4034 fi

4035 ;;

4036 [Hh][Ii][Dd][Dd][Ee][Nn])

4037 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4038 args="value=\"$GF_STAGE\""

4039 fi

4040 form="<input type=\"hidden\" name=\"$name\" $args>"

4041 prompt='' # Remove prompt

4042 ;;

4043 [Aa][Uu][Tt][Hh][Oo][Rr])

4044 [ -n "$GF_VIEWONLY" ] && continue

4045 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4046 prompt="" ;;

4047 [Oo][Ww][Nn][Ee][Rr])

4048 [ -n "$GF_VIEWONLY" ] && continue

4049 val=${GF_OWNER:-$val}

4050 val=${val:-$user}

4051 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4052 prompt="" ;;

4053 [Uu][Ss][Ee][Rr])

4054 # XXX: is null $user ok?

4055 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4056 [ -n "$GF_VIEWONLY" ] && continue

4057 form="$user"

4058 ;;

4059 [Pp]assword)

4060 [ -n "$GF_VIEWONLY" ] && continue

4061 form="`cgi_passwd`"

4062 val=""

4063 ;;

4064 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4065 [ -n "$GF_VIEWONLY" ] && continue

4066 if [ -z "$rowid" ]; then

4067 val=`genserial`

4068 fi

4069 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4070 prompt="" ;;

4071 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4072 prompt=""

4073 ;;

4074 parent|path|blog*)

4075 prompt=""

4076 ;;

4077 "*"*)

4078 tail=$tail"``"

4079 continue ;;

4080 esac

4081 if [ -n "$prompt" ]; then

4082 if [ -n "${GF_VIEWONLY}" ]; then

4083 form=$val

4084 else

4085 :

4086 fi

4087 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4088 else

4089 hiddens=$hiddens$nl"$form"

4090 fi

4091 done < $1

4092 # enctype="multipart/form-data"

4093 cat<<EOF

4094 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4095 ${rowid:+$rm}

4096 <table class="b $2">

4097 $forms

4098 </table>$hiddens

4099 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4100 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4101 EOF

4102 if [ -z $GF_VIEWONLY ]; then

4103 cat<<EOF

4104 <input type="submit" name="sub" value="OK">

4105 <input type="reset" name="res" value="Reset">

4106 EOF

4107 fi

4108 cat<<EOF

4109 $GF_ARGS</form>

4110 $tail

4111 EOF

4112 }

4113 edittable() {

4114 # $1=form-def $2=table $3 rowid

4115 genform "$@"

4116 }

4117 viewtable() {

4118 GF_VIEWONLY=1 genform "$@"

4119 }

4120 showattc() {

4121 # $1=table_m $2=rowid &optional $3=RawFlag

4122 ## err \$1=$1 \$2=$2 \$3=$3

4123 if ! isfilereadable $user $1 $2; then

4124 contenttype; echo

4125 echo "このファイルは管理者のみしか見られません" | html p

4126 putfooter; exit

4127 fi

4128 idir=`umask 002; mktempd` || exit 1

4129 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4130 bin=$idir/$myname-$$.bin

4131 sql="select quote(bin) from $1 where rowid='$2';"

4132 ## err showattc: sql: $sql

4133 sq $db "$sql" | unhexize > $bin

4134 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4135 type=${tv%//*} fn=${tv#*//}

4136 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4137 ct=${type#file:}

4138 case $ct in # all text/* changed to text/plain

4139 text/*)

4140 charset=`nkf -g $bin|cut -d' ' -f1`

4141 case $charset in

4142 ASCII*) charset="" ;;

4143 esac

4144 if [ -z "$3" ]; then

4145 ct="text/html${charset:+; charset=$charset}"

4146 link="?showattc+$1+$2+raw"

4147 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4148 | sed 's,^,<span></span>,' \

4149 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4150 -D_LINK_="$link" \

4151 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4152 exit $?

4153 fi

4154 ct="text/plain${charset:+; charset=$charset}"

4155 ;;

4156 esac

4157 contenttype "$ct"

4158 echo "Content-Disposition: filename=\"$fn\""

4159 echo "Content-Length: " `cat $bin | wc -c`; echo

4160 #echo "Content-Type: " ${type#file:}; echo

4161 cat $bin

4162 }

4163 #

4164 # Some default stupid handler on CGI values

4165 #

4166 default_storedb() {

4167 # ARG: $1=table-def-file

4168 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4169 tbl=`basename $1`

4170 tbl=${tbl%.def}

4171 cols="`grep :text $1|cut -d: -f2`"

4172 col=`echo "$cols"|head -1`

4173 vcol=`getpar $col`

4174 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4175 if [ -n "$vcol" ]; then

4176 par2table $1

4177 else

4178 return 2 # No insertion occurred

4179 fi

4180 }

4181

4182 default_view() { # $1=def-file

4183 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4184 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4185 default_storedb "$@"

4186 query "select rowid from $tbl order by rowid desc;" \

4187 | while read rowid; do

4188 viewtable $1 $tbl $rowid

4189 done | _m4 -D_TITLE_="$tbl" \

4190 -D_FORM_="`genform $1`" \

4191 -D_DUMPTABLE_="syscmd(cat)" \

4192 $layout/html.m4.html $layout/form+dump.m4.html

4193 }

4194 default_viewtext() { # $1=def-file

4195 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4196 default_storedb "$@"

4197 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4198 | _m4 -D_TITLE_="$tbl" \

4199 -D_FORM_="`genform $1`" \

4200 -D_DUMPTABLE_="syscmd(cat)" \

4201 $layout/html.m4.html $layout/form+dump.m4.html

4202 }

4203 default_smail() {

4204 default_storedb "$@"

4205 if [ $? -eq 2 ]; then

4206 _m4 -D_TITLE_="入力" \

4207 -D_FORM_="`genform $1`" \

4208 -D_DUMPTABLE_="" \

4209 $layout/html.m4.html $layout/form+dump.m4.html

4210 return

4211 fi

4212 cond=""

4213 for pk in `gettblpkey $tbl`; do

4214 pv=$(sqlquote "$(getpar $pk)")

4215 cond="$cond${cond:+ and }$pk=$pv"

4216 done

4217 sql="select rowid from $tbl where $cond;"

4218 rowid=`query "$sql"`

4219 ## err smail1 - "$sql" "-> rowid=$rowid"

4220

4221 while IFS=: read prompt name keytype type args; do # Read from $1

4222 val=`getpar $name`

4223 if [ -n "$val" ]; then

4224 text="$text

4225 $prompt

4226 $name=$val

4227 ---------------------------------------------------------"

4228 fi

4229 case "$type" in

4230 image|document|file)

4231 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4232 fns=$(echo "$fn"|while read fn; do

4233 err mv $tmpd/$fn.orig $tmpd/$fn

4234 mv $tmpd/$fn.orig $tmpd/$fn

4235 rm $tmpd/$fn.rowid # Remove cache flag

4236 ## err "`ls $tmpd/$fn`"

4237 echo $fn

4238 done)

4239 files="$files $fns"

4240 ;;

4241 esac

4242 done < $1

4243 ## err FILES=$files "`ls -lF $tmpd`"

4244 subj="from ${REMOTE_ADDR}"

4245 (echo "$url"

4246 echo "への書き込みがありました。"

4247 echo "------"

4248 echo "$text"

4249 ) | (cd $tmpd &&

4250 err LS="`ls -lF`" &&

4251 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4252 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4253 echo "以下の内容で送信しました。" | html p

4254 viewtable $1 $tbl \

4255 `query "select rowid from $tbl order by rowid desc limit 1;"`

4256 echo "戻る" | html a "href=\"?\""

4257 }