s4: 0f7dde18024b s4-funcs.sh

s4

view s4-funcs.sh @ 717:0f7dde18024b

Add debug code for "0-byte file attachment" problem

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sun, 31 May 2020 06:55:17 +0900
parents	d890694ff8ab
children	c56f8e76dff2

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 for kv; do

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

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

1179 done

1180 )

1181 contenttype() {

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

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

1184 }

1185 putheader() {

1186

1187 }

1188 putfooter() {

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

1190 }

1191 getcookie() {

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

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

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

1195 case "$k" in

1196 user) _user="$v" ;;

1197 skey) _skey="$v" ;;

1198 esac

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

1200 done

1201 }

1202 genrandom() {

1203 # $1=columns (default: 10)

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

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

1206 }

1207 genserial() {

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

1209 }

1210 smail() {

1211 # smail rcpts subj (file)

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

1213 # $MAIL_FROM <- From: header value

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

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

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

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

1218 sender=${SENDER:-$admin}

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

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

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

1222 }

1223 smail_queue_flush() {

1224 # $1=timelimit

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

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

1227 SELECT rowid FROM smailq

1228 GROUP BY rcpts, subj

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

1230 EOF

1231 )

1232 for rid in $rowids; do

1233 sq $workdb \

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

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

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

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

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

1239

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

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

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

1243 .separator "\n" "------------------\n\n"

1244 SELECT time, text FROM smailq

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

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

1247 ORDER by time;

1248 EOF

1249 then

1250 echo rowid=$rid

1251 cat <<-EOF | sq $workdb

1252 DELETE FROM smailq

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

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

1255 EOF

1256 fi

1257 fi

1258 done

1259 }

1260 smail_queue() {

1261 # $1=Rcpts, $@=subj

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

1263 if [ $? -eq 0 ]; then

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

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

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

1267 err "rcpts=[$rcpts] s=[$subj] t=[$text]"

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

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

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

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

1272 EOF

1273 )

1274 ## XXX: Adhoc load mitigation

1275 case "$now" in

1276 *[01])

1277 err flush_queue=$mintime

1278 smail_queue_flush ;;

1279 esac

1280 fi

1281 }

1282 setviastring() {

1283 table=par

1284 oifs="$IFS"

1285 IFS="&"

1286 for us in $1; do

1287 k=${us%%=*}

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

1289 setpar "$k" "string" "$v"

1290 done

1291 IFS="$oifs"

1292 }

1293 checkdomain() (

1294 # Check the validity of domain by referring DNS

1295 item=$1

1296 err checkdomain $1

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

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

1299 )

1300 pwcheck() {

1301 # $1=passwd

1302 dbpswd=`getpwfield $user pswd`

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

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

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

1306 }

1307 mypwhash() {

1308 mycrypt "`cat`" `genrandom 5`

1309 }

1310 wasureta() {

1311 user=$1

1312 if ! checkdomain $user; then

1313 contenttype; echo

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

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

1316 putfooter

1317 exit 0

1318 fi

1319 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1321 encpswd=`echo $newpswd|mypwhash`

1322 dbsetbyid user $user pswd "$encpswd"

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

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

1325 $msgdir/mail-newaccount.m4 \

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

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

1328 }

1329 checkauth() {

1330 user=`getpar user`

1331 skc=`getpar skey` # from cookie

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

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

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

1335 if chkskey "$skc"; then

1336 return 0

1337 fi

1338 fi

1339 pswd=`getpar pswd`

1340 quser=`sqlquotestr "$user"`

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

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

1343 return 4 # 4=server too heavy

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

1345 return 2 # 2=login fail

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

1347 wasureta "$user"

1348 return 1 # wasureta error

1349 fi

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

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

1352 if pwcheck "$pswd"; then

1353 newsession=`genrandom 34`

1354 if setskey "$user" "$newsession" &&

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

1356 gencookie "user=$user" "skey=$newsession"

1357 return 0

1358 else

1359 return 4 # Heavy load??

1360 fi

1361 fi

1362 return 2 # Password mismatch

1363 }

1364 showlogin() {

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

1366 test -z "$args" && resetskey

1367 s4name=${S4NAME:-s4}

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

1369 cat $layout/login.m4.html

1370 echo '</body></html>'

1371 ) | _m4 \

1372 -D_BODYCLASS_="login" \

1373 -D_TITLE_="$s4name" \

1374 -D_SYSNAME_="Welcome to $s4name" \

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

1376 exit 0

1377 }

1378 dologin() {

1379 checkauth

1380 st=$?

1381 if [ $st != 0 ]; then

1382 contenttype; echo

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

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

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

1386 showlogin # and EXIT

1387 fi

1388 }

1389

1390 # Do instant jobs here

1391 dbsetup

1392 trap cleanup INT HUP EXIT TERM PIPE

1393 # trap cleanup INT HUP

1394

1395 err() {

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

1397 }

1398

1399 cgiinit() {

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

1401 tmpf=tmp/stream

1402 tmpd=`tmpd=$tmpdir mktempd`

1403 tmpfiles=$tmpfiles" $tmpd"

1404 addsession $session

1405 getcookie

1406 case "$REQUEST_METHOD" in

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

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

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

1410 # cat > $tmpf # too much?

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

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

1413 s="`cat tmp/stream`"

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

1415 ;;

1416 esac

1417 case "$CONTENT_TYPE" in

1418 *boundary*)

1419 bndry=${CONTENT_TYPE#*boundary=}

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

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

1422 do

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

1424 #echo u=$us

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

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

1427 # err k=$k v=$v

1428 case "$k" in

1429 *:filename)

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

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

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

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

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

1435 :

1436 fi

1437 err "user=$user k=[$k] v=[$v]" # debug@2020-0531

1438 err "`ls -lF "$tmpd/$v"; file --mime-type "$tmpd/$v"`"

1439 case "$mimetype" in

1440 [Ii]mage/x-xcf)

1441 bzip2 "$tmpd/$v"

1442 v=${v}.bz2

1443 ;;

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

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

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

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

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

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

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

1451 v=$vjpg

1452 else

1453 mimetype="Not supported"

1454 fi

1455 ;;

1456 [Ii]mage/*)

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

1458 ;;

1459 esac

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

1461 then

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

1463 continue

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

1465 replpar text string \

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

1467 continue

1468 fi

1469 ;;

1470 *)

1471 type='string'

1472 ;;

1473 esac

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

1475 setpar "$k" "$type" "$v"

1476 done

1477 ;;

1478 *)

1479 setviastring "$s"

1480 ;;

1481 esac

1482 }

1483 email4group() {

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

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

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

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

1488 sql="WITH

1489 grpemails AS (

1490 SELECT gname, user, val email

1491 FROM grp_mem NATURAL JOIN grp_mem_s

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

1493 useremails AS (

1494 SELECT user.name, val email

1495 FROM user

1496 LEFT JOIN user_m

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

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

1499 FROM useremails u LEFT JOIN grpemails g

1500 ON u.name=g.user

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

1502 query "$sql"

1503 }

1504 email4groupbyuid() {

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

1506 g=$1; shift

1507 uids=`echo "$@"`

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

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

1510 email4group "$g" `query "$sql"`

1511 }

1512 myemail4group() {

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

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

1515 }

1516 collectmembersbyid() {

1517 # Collect user names of group specified by grid

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

1519 query "SELECT user FROM grp_mem \

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

1521 }

1522 collectmembersbyid() {

1523 # Collect user names of group name

1524 qgrp=`sqlquote "$1"`

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

1526 }

1527 collectgecosesbyid() {

1528 # Collect user gecoses of group

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

1530 query<<-EOF

1531 SELECT gecos

1532 FROM gecoses

1533 WHERE name IN (SELECT user FROM grp_mem

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

1535 EOF

1536 }

1537 collectemail() (

1538 # Collect email addresses for group $1

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

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

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

1542 for e; do

1543 if isuser "$e"; then

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

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

1546 else

1547 qgrp=`sqlquote "$e"`

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

1549 gmem="grp_mem"

1550 else

1551 tm=`sqlquote "$TEAM"`

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

1553 fi

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

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

1556 $gmem g left join grp_mem_s s

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

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

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

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

1561 query "$sql"

1562 fi

1563 done

1564 )

1565 sendinvitation() (

1566 # $1=email

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

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

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

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

1571 gecos=`gecos`

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

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

1574 _m4 -D_URL_="$urlbase" \

1575 -D_USER_="$name" \

1576 -D_EMAIL_="$1" \

1577 -D_REGIST_="$regist" \

1578 -D_ADMIN_="$admin" \

1579 $msgdir/mail-invite.m4 \

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

1581 return 0

1582 )

1583 emaildomaincheck() {

1584 case "$1" in

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

1586 *@*)

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

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

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

1590 return 2

1591 fi

1592 return 0

1593 ;;

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

1595 esac

1596 }

1597 invite() {

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

1599 case "$email" in

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

1601 *@*)

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

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

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

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

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

1607 elif sendinvitation $email; then

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

1609 else # Cannot be reached here

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

1611 fi ;;

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

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

1614 esac

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

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

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

1618 fi

1619 if [ -f $invite_policy ]; then

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

1621 else

1622 pol="$invite_policy"

1623 fi

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

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

1626 -D_INVITE_POLICY_="$pol" \

1627 $layout/html.m4.html $layout/invite.m4.html

1628 }

1629 regist() {

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

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

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

1633 echo "bye bye" | html p

1634 reutrn

1635 fi

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

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

1638 cat<<EOF

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

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

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

1642 EOF

1643 return

1644 fi

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

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

1647 # Fake login password to wasureta

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

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

1650 wasureta $email

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

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

1653 addsession $1 # for removal after 1 minute

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

1655 $layout/login.m4.html

1656 return

1657 }

1658 group_safename() {

1659 # Convert $1 to safe group name

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

1661 }

1662 groupupdate() {

1663 gname=`getpar gname`

1664 qgname=`sqlquote "$gname"`

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

1666 # See ALSO same job in showgroup()

1667 newgname=`group_safename "$gname"`

1668 err newgname=$newgname

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

1670 err NewGNAME: gname=$newgname

1671 gname=$newgname

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

1673 replpar gname string "$gname"

1674 fi

1675 # Name confliction check

1676 parow=`getpar rowid`

1677 ## err parow=$parow

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

1679 query "BEGIN EXCLUSIVE;"

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

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

1682 if [ $count -gt 0 ]; then

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

1684 query "END;"

1685 return

1686 fi

1687 par2table $formdir/grp.def

1688 query "END TRANSACTION;"

1689 # Remove orphan

1690 : <<EOF

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

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

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

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

1695 EOF

1696 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1700 sql="delete from blog where id in

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

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

1703 query "$sql";

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

1705 return # and return

1706 fi

1707 fi

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

1709 fi

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

1711 grid=$(query $sql)

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

1713 grp $grid

1714 }

1715 groupclone() {

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

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

1718 case "$1" in

1719 */noteam)

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

1721 esac

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

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

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

1725 return

1726 fi

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

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

1729 return

1730 fi

1731 i=0

1732 while true; do

1733 copy="-copy$i"

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

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

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

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

1738 i=$((i++))

1739 continue

1740 fi

1741 break

1742 done

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

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

1745 query<<-EOF

1746 BEGIN;

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

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

1749 SELECT $newqname, key, val

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

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

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

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

1754 -- Copy members and their configuration --

1755 REPLACE INTO grp_mem SELECT $newqname, user

1756 FROM grp_mem WHERE gname=$qgrp;

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

1758 FROM grp_mem_s WHERE gname=$qgrp;

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

1760 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1761 -- Copy administrators --

1762 REPLACE INTO grp_adm SELECT $newqname, user

1763 FROM grp_adm WHERE gname=$qgrp;

1764 COMMIT;

1765 EOF

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

1767 STOPCLONEMSG=1 groupconf "$newrowid"

1768 }

1769 groupman() {

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

1771

1772 GF_STAGE="grpconf"

1773 GF_STAGE=groupupdate

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

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

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

1777 -D_DUMPTABLE_="syscmd(cat)" \

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

1779 }

1780 userconf() {

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

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

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

1784 }

1785 groupconf() {

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

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

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

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

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

1791

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

1793 grp=`getgroupbyid $rowid`

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

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

1796 return

1797 fi

1798

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

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

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

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

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

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

1805 <div>

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

1807 <table>

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

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

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

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

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

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

1814 </table>

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

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

1817 </div>

1818 EOF

1819 fi

1820 }

1821 mems() {

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

1823 kwd=`getpar kwd`

1824 listmember $kwd

1825 }

1826 grps() {

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

1828 kwd=`getpar kwd`

1829 listgroup $kwd \

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

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

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

1833 $layout/form+dump.m4.html

1834 }

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

1836 gpg=`getpar grp`

1837 grid=${1:-$gpg}

1838 grp=`getgroupbyid "$grid"`

1839 ## . ./s4-blog.sh

1840 jg=`getpar joingrp`

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

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

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

1844 fi

1845 htmlheader=$layout/html.m4.html

1846 showgroup "$grid"

1847 }

1848 sql4interestblogs() {

1849 cat<<EOF

1850 CREATE TEMPORARY VIEW interestblogs AS

1851 SELECT blog.rowid rid, id, author

1852 FROM blog

1853 NATURAL JOIN

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

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

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

1857 WHEN (SELECT user FROM grp_mem

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

1859 THEN 0

1860 ELSE 1

1861 END;

1862 EOF

1863 }

1864 listnewblogsql() { # $1=user

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

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

1867 newdays=${newdays:-14}

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

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

1870 WHERE user='$user'

1871 AND tblrowid IN

1872 ($blogreadflagrowid,

1873 $blogcutoffflagrowid)),

1874 $basetime -- "0"

1875 );"`

1876 cat<<EOF

1877 `sql4interestblogs`

1878 WITH article_ctime as (

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

1880 FROM article join article_s s using(id)

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

1882 GROUP BY id

1883 ), blog_title_owner as (

1884 SELECT blg.rid brid, id,

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

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

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

1888 ), blogall as (

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

1890 ), news as (

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

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

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

1894 FROM blogall bl

1895 LEFT JOIN

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

1897 ON bl.brid=al.tblrowid

1898 WHERE atime < bl.ctime

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

1900 LIMIT 10

1901 ) SELECT brid LINK, "新着",

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

1903 ctime, title,

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

1905 FROM news;

1906 EOF

1907 }

1908

1909 search_form() {

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

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

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

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

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

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

1916 @week → 最近一週間

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

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

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

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

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

1922 auth=""

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

1924 case "$1" in

1925 author=*)

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

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

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

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

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

1931 ;;

1932 grp=*)

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

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

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

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

1937 ;;

1938 esac

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

1940 cat<<-EOF

1941 <div class="right">

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

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

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

1945

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

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

1948

1949 </form>

1950 </div>

1951 EOF

1952 }

1953

1954 imgsrc_cache() (

1955 # $1 = directory for cache'ing

1956 # $2 = table (user_m or grp_m)

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

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

1959 dir="$1" tbl="$2"

1960 keycond="$3"

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

1962 ORDER BY rowid DESC LIMIT 1"

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

1964 tmpf=$tmpd/imgsrc_cache.$$

1965 case "$4" in

1966 [Ss]) size=S ;;

1967 [Oo]) size=O ;;

1968 *) size=M ;;

1969 esac

1970 # ImageCache filename storing schema:

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

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

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

1974 valtype=`query "$sql0"`

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

1976 filetype=${valtype##*//file:}

1977 if [ x"$filename" = x"${filename%.*}" ]; then

1978 # If nor filename extension found, set it to image type

1979 case "$filetype" in

1980 image/*) filename=$filename.${filetype#image/} ;;

1981 esac

1982 fi

1983 cacheimg_S=$dir/S_$filename

1984 cacheimg_M=$dir/M_$filename

1985 cacheimg_O=$dir/$filename

1986 cacheimg=$dir/${size}_$filename

1987 sumfile="$dir/$filename.sum"

1988 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

1989 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

1990 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

1991 # if cache is fresh and has the same checksum,

1992 echo "<img src=\"$cacheimg\">"

1993 else

1994 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

1995 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

1996 fifo2=$fifo.2

1997 mkfifo $fifo $fifo2

1998 fmt=${filename##*.}

1999 ## [[ NOTE ]]

2000 ## a. convert oldimage newimage

2001 ## b. convert oldimage fmt:- | convert - newimage

2002 ## b is much smaller than a

2003 cat $tmpf | unhexize \

2004 | tee $fifo \

2005 | convert -define ${fmt}:size=${iconxy_M} \

2006 -resize ${iconxy_M}'>' - ${fmt}:- \

2007 | tee $fifo2 \

2008 | convert - "$cacheimg_M" &

2009 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2010 -resize ${iconxy_S}'>' - ${fmt}:- \

2011 | convert - "$cacheimg_S" &

2012 printf '%s' "<img src=\"data:${filetype},"

2013 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2014 echo '">'

2015 echo "$sum" > $sumfile

2016 fi

2017 ## Now preparing cache image, done.

2018 ## Store this information to DB

2019 stbl=${tbl%_m}_s # user_s or grp_s

2020 pkey=${keycond%%=*} # Primary Key name

2021 pval=${keycond#*=} # Primary Key value

2022 query <<-EOF

2023 REPLACE INTO $stbl($pkey, key, type, val)

2024 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2025 EOF

2026 )

2027

2028 showhome() {

2029 # $1=userRowIdToShow

2030 err showhome \$1=$1

2031 case "$1" in

2032 *@*) uname=`getvalbypkey user name "$1"` ;;

2033 *) uname=`getvalbyid user name $1` ;;

2034 esac

2035 ## err ShowHome: uname=$uname

2036 td=`getcachedir home/"$1"`

2037 gecos=`gecos "$uname"`

2038 ## err SH:gecos=$gecos

2039 GF_VIEWONLY=1

2040 cond="gname in (select gname from grp_mem where user='$uname')"

2041 search_form_args=""

2042 if [ x"$user" = x"$uname" ]; then

2043 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2044 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> /

2045 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2046 # Display folders

2047 sql="select count(id) from article_m where id

2048 in (select id from article where author='$user')

2049 and type like 'file:%';"

2050 ## err nfile-sql=`echo "$sql"`

2051 nfile=`query "$sql"`

2052 # err nfile=$nfile

2053 if [ $nfile -gt 0 ]; then

2054 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2055 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2056 fi

2057 else

2058 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2059 GROUP BY user;"`

2060 usermenu="<p>Last seen on $latestlog</p>"

2061 search_form_args="author=$uname"

2062 fi

2063 . ./s4-blog.sh

2064

2065 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2066 search_form "$search_form_args" > $sf

2067 printf "%s さん" "$gecos" > $tf

2068 { echo "<div class=\"noprofimg\">"

2069 viewtable $formdir/user.def user $1

2070 echo "</div>"

2071 } > $pf

2072

2073 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2074 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2075 imf=$tmpd/profimg.$$; touch $imf

2076 if [ -n "$img" ]; then

2077 if true; then

2078 tbl=user_m

2079 enticond="name='$uname'"

2080 imgsrc_cache "$td/main" user_m "$enticond" M

2081 else

2082 { printf '%s' "<IMG src=\"data:${img#file:},"

2083 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2084 | sed 's/$..$/%\1/g'

2085 echo '">'

2086 }

2087 fi > $imf

2088 fi

2089 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2090 val='$uname';"`

2091 ## REMOVE This comment block until 2019/7/1

2092 ## err "----- `gdate +%FT%T.%3N` ------------C"

2093 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2094 listblog $uname > $bf

2095 ## unset ddd

2096 ## err "----- `gdate +%FT%T.%3N` ------------D"

2097

2098 hometail=$tmpd/tail.$$

2099 mkfifo $hometail

2100

2101 #Calling listgroupbytable, originally here

2102

2103 (

2104 # Display Most Recent Entry

2105 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2106 shortval=${shortval:-val}

2107

2108 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2109 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2110 ctime,

2111 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2112 (SELECT gecos FROM gecoses

2113 WHERE name=(SELECT val FROM blog_s

2114 WHERE key='owner' AND id=b.id)) owner,

2115 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2116 FROM blog b

2117 JOIN

2118 (SELECT distinct blogid, a.id aid, max(val) ctime

2119 FROM article a, article_s s

2120 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2121 GROUP BY blogid ORDER BY val DESC LIMIT 50

2122 ) m

2123 ON b.id=m.blogid;"

2124 # This should be as follows

2125 : <<EOF

2126 WITH arts AS(

2127 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2128 a.blogid, a.id id, s.val ctime

2129 FROM article a NATURAL JOIN article_s s

2130 WHERE s.key = 'ctime' AND a.author='$user'

2131 GROUP by s.id

2132 )

2133 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2134 FROM arts a0

2135 JOIN

2136 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2137 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2138 ORDER BY ctime DESC LIMIT 50;

2139 EOF

2140

2141 cat<<-EOF

2142 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2143 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2144 <div class="lcto">

2145 `DT_VIEW=replyblog dumptable html blog`

2146 </div>

2147 EOF

2148 unset DT_SQL

2149 if [ x"$user" = x"$uname" ]; then

2150 # Display NEWS

2151 # 2016-06-26

2152 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2153 acclog blog $blogreadflagrowid

2154 # echo "全部既読にしました" | html p

2155 fi

2156 # 2016-02-19 Counting NEWS without using dumptable.

2157 sql=`listnewblogsql "$user"`

2158 # echo "$sql" > tmp/listnew

2159 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2160 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2161 cont=$((cont-1))

2162 err newcount=$cont

2163 if [ $cont -gt 0 ]; then

2164 #echo "全体の新着記事${cont}傑" | html h2

2165 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2166 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2167 cat<<-EOF | html form 'action="?home"'

2168 `cgi_checkbox readchk yes 'id="read"'`<label

2169 for="read">新着ふくめて全部読んだことにする</label>

2170 `cgi_submit '確定'`

2171 `cgi_hidden read yes`

2172 EOF

2173 echo "$new10 "

2174 echo "</div>"

2175 else # If news is 0, set log cut off flag

2176 acclog blog $blogcutoffflagrowid # for speed

2177 fi

2178 else # Not My Home ($user != $uname)

2179 : # DT_SQL=

2180 fi

2181 ) > $hometail & # Is background call safe to m4??

2182 #

2183 listgroupbytable $formdir/grp.def $cond |

2184 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2185 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2186 -D_PROFIMG_="spaste(\`$imf')" \

2187 -D_BLOGS_="spaste(\`$bf')" \

2188 -D_SEARCH_="spaste(\`$sf')" \

2189 -D_NBLOG_="$nblog" \

2190 -D_GROUPS_="syscmd(\`cat')" \

2191 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2192 $layout/html.m4.html $layout/home.m4.html

2193

2194 # Record access log

2195 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2196 }

2197 commission() { # $1=grp-rowid $2=user-rowid

2198 contenttype; echo

2199 ## err commission: "$@"

2200 gname=`getgroupbyid $1`

2201 echo "グループ $gname 管理者委任" \

2202 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2203 if [ -n "$2" ]; then

2204 grp_reg_adm "$@"

2205 else

2206 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2207 fi

2208 }

2209 listgroupbytable() {

2210 # $1=deffile $2...=condition

2211 tagline=`grep :tag: $1`; shift

2212 and="${1:+and }" where=${1:+where }

2213 href="<a href=\"$myname?grp+"

2214 echo '<div class="listgroup">'

2215 NGsql="select distinct tag from\

2216 (select gname, max(case key when 'tag' then val end) as tag, \

2217 max(case key when 'ctime' then val end) as ctime\

2218 from grp_s group by gname order by ctime);"

2219 sql="select val from grp_s where key='tag' $and$* group by val;"

2220 ## err ListGRP: query sql="$sql"

2221 for tag in `query "$sql"`

2222 do

2223 ## err ListGrp: tag=$tag

2224 tn=${tagline%%=${tag}*}

2225 tn=${tn##*[ :]}

2226 sql="select rowid||':'||gname as 'グループ名',説明 from

2227 (select (select rowid from grp g where g.gname=grp_s.gname)

2228 as rowid,

2229 gname,

2230 max(case key when 'gecos' then val end) as '説明',

2231 max(case key when 'tag' then val end) as 'tag',

2232 max(case key when 'mtime' then val end) as mtime from grp_s

2233 $where$* group by gname having tag='$tag' order by mtime desc);"

2234 ## err PersonalGroupList= `echo $sql`

2235 echo "<h2>$tn</h2>"

2236 echo '<table class="b listgroup">'

2237 sq -header -html $db "$sql" \

2238 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2239 echo '</table>'

2240 done

2241 echo '</div>'

2242 }

2243 iconhref() (

2244 # $1=icon-file, $2=Href $3=title $4...=anchor

2245 data=`percenthex "$1"`

2246 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2247 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2248 href=$2; title=$3; shift 3

2249 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2250 )

2251 iconhref2() (

2252 # $1=icon-file, $2=Href $3=title $4...=anchor

2253 src=$1

2254 href=$2; title=$3; shift 3

2255 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2256 )

2257 listentry() (

2258 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2259 # Referring variable $iamowner=$grp to attach owner-request links

2260 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2261 cond='' hiddens=''

2262 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2263 if [ -z "$offset" ]; then

2264 offset=`getpar start`; offset=${offset%%[!0-9]*}

2265 offset=$((offset-1))

2266 fi

2267 offset=$((offset + 0)) # change to numeric forcibly

2268 [ $offset -lt 0 ] && offset=0

2269 limit=$listentlimit

2270 dir=`getcachedir "$1"`

2271 if [ x"$1" = x"user" ]; then

2272 hrb="$myname?home"

2273 deficon=person-default.png

2274 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2275 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2276 gcs=gecos

2277 else # if group

2278 hrb="$myname?grp"

2279 deficon=group-default.png

2280 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2281 gcs=name

2282 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2283 if [ -n "$tagline" ]; then

2284 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2285 ## err tagconv=$tagconv

2286 fi

2287 fi

2288 if [ ! -d $dir ]; then

2289 mkdir -p $dir

2290 fi

2291 if [ ! -s $dir/$deficon ]; then

2292 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2293 fi

2294 if [ -n "$2" ]; then

2295 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2296 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2297 fi

2298 tag=`getpar tag` tag2=`getpar tag2`

2299 if [ x"$tag" = x"NULL" ]; then

2300 tag="" tag2=""

2301 fi

2302 if [ -n "$tag$tag2" ]; then

2303 tag=${tag:-$tag2}

2304 qtag=`sqlquote "$tag"`

2305 cond2="tag=$qtag"

2306 fi

2307 if [ -n "$cond1$cond2" ]; then

2308 cond="$cond1${cond2:+ AND $cond2}"

2309 cond="WHERE ${cond# AND }"

2310 fi

2311

2312 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2313 # grpは呼出し元の動的スコープ変数でよくないな...

2314 ##qgrp=`sqlquote $grp`

2315 getgrp="(select gname from grp where rowid=${rowid:--1})"

2316 sql="select a.rowid, a.$link,

2317 coalesce(b.$gcs, a.$nm) as nick,

2318 quote(a.$nm) as qname,

2319 (SELECT val FROM ${tbl}_s

2320 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2321 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2322 || case when a.$nm in (select gname from grp)

2323 then printf('(%d名)',

2324 (select count(user) from grp_mem where gname=a.$nm))

2325 else ' <'||a.$nm||'>'

2326 end

2327 as name,

2328 b.tag,

2329 case when a.$nm in (select user from grp_adm

2330 where gname=$getgrp) then '管理者'

2331 when '$user' in (select user from grp_adm where gname=a.$nm)

2332 then 'ADMIN'

2333 when '$user' in (select user from grp_mem where gname=a.$nm)

2334 then 'Member'

2335 when '$iamowner' = '' then ''

2336 else ',not='||a.rowid end as ownerlink,

2337 CASE '$entity'

2338 WHEN 'グループ'

2339 THEN coalesce(

2340 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2341 'open')

2342 ||

2343 CASE WHEN '$user'

2344 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2345 THEN ' ismember'

2346 ELSE ''

2347 END

2348 ELSE 'user'

2349 END regmode

2350 from $tbl a left join

2351 (select $nm as name,

2352 max(case key when 'gecos' then val end) as gecos,

2353 max(case key when 'tag' then val end) as tag,

2354 max(case key when 'mtime' then val end) as mtime,

2355 max(case key when 'wtime' then val end) as wtime,

2356 max(case key when 'login' then val end) as login

2357 from ${tbl}_s group by $nm)

2358 b on a.$nm=b.name $cond $3

2359 order by b.wtime desc, b.login desc,

2360 b.mtime desc, b.tag desc, a.rowid asc"

2361 # Give precedence to newer maintained groups (2016-09-24)

2362 # Note that mtime is stored only in grp_s.

2363 ## err LE:sql.1="$sql"

2364 total=`query "with x as ($sql) select count(*) from x;"`

2365 echo "${entity} 一覧" | html h2

2366 echo '<div class="listentry">' # List-entry div

2367 # Show owner/member filter button

2368 METHOD=GET

2369 hiddens="$hiddens

2370 `cgi_hidden kwd \"$kwd\"`

2371 `cgi_hidden stage \"$stage\"`"

2372 if [ x"$tbl" = x"grp" ]; then

2373 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2374 fh="<select name=\"tag\">$nl"

2375 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2376 for l in $args; do

2377 val=${l#*=} tname=${l%=*}

2378 if [ x"$val" = x"$tag" ]; then

2379 s=" selected"

2380 selectedtags="(種別[${tname}]のみ)"

2381 else

2382 s=""

2383 fi

2384 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2385 done

2386 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2387 cat<<-EOF

2388 <form action="$myname" method="$METHOD">

2389 </form>

2390 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2391 for="ismembtn">参加中以外隠す</label>

2392 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2393 for="isadmbtn">管理者参加以外隠す</label>

2394 EOF

2395 # limit=3

2396 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2397 fi

2398 if [ $total -gt $limit ]; then

2399 echo '<div>'

2400 METHOD=GET cgi_form $stage <<EOF

2401 $form

2402 <label>次の語を含む${entity}で検索:

2403 `cgi_text kwd "$kwd"`</label>

2404 $hiddens

2405 EOF

2406 echo '</div>'

2407 else

2408 echo $selectedtags | html p

2409 fi

2410 cat<<EOF

2411 <form action="$myname" method="$METHOD">

2412 <p>${total}件中の<input class="hidesub" type="text" name="start"

2413 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2414 <input type="submit" value="確定"></p>

2415 </form>

2416 EOF

2417 if [ $((offset+limit)) -lt $total ]; then

2418 nextbtn=$(

2419 cat<<EOF

2420 <div class="right clear"><form action="$myname" method="$METHOD">

2421 `cgi_submit 次の${limit}件`

2422 $hiddens

2423 `cgi_hidden offset $((offset + limit))`</form></div>

2424 EOF

2425 )

2426 fi

2427 if [ $offset -gt 0 ]; then

2428 prevbtn=$(

2429 cat<<EOF

2430 <form action="$myname" method="$METHOD">

2431 `cgi_submit 前の${limit}件`

2432 $hiddens

2433 `cgi_hidden offset $((offset - limit))`</form>

2434 EOF

2435 )

2436 fi

2437 pnbtn="$nextbtn$prevbtn"

2438 echo $pnbtn

2439

2440 ## err ListEntry: `echo "$sql"\;`

2441 # sq $db here??? 2016-11-28

2442 query "$sql limit $limit ${offset:+offset $offset};" \

2443 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2444 # err name=$name owner=$ownerp lnk=$lnk

2445 # err newlnk=$lnk regmode=$regmode

2446 icondir=$dir/$id

2447 # Pick up only last icon

2448 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2449 <p class=\"tag _$tag\">$tag</p>" \

2450 | _m4 $tagconv

2451 if [ -n "$NOSPEEDUP" ]; then

2452 files=`getvalbyid $tbl profimg $id $icondir`

2453 if [ -n "$files" ]; then

2454 icon=`echo "$files"|tail -1`

2455 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2456 else

2457 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2458 fi

2459 elif [ -n "$icon" -a -s "$icon" ]; then

2460 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2461 else

2462 cond="$nm=$qname"

2463 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2464 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2465 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2466 # err "img=[$img]"

2467 if [ -n "$img" ]; then

2468 echo "<a href=\"$hrb+$lnk\">"

2469 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2470 echo "</a>"

2471 else

2472 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2473 fi

2474 fi

2475 echo "<br>$name${ownerp:+<br>($ownerp)}"

2476 echo "</div>"

2477 done

2478 echo "</div>" # End of List-entry div

2479 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2480 )

2481 listmember() {

2482 listentry user "$@"

2483 }

2484 listgroup() {

2485 listentry group "$@"

2486 }

2487 hexteams() { # $1=gname, $2(optional)=user

2488 cond=${2:+" AND user='$2'"}

2489 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2490 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2491 }

2492 showgroup() { # $1=group-rowid

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

2494 grid=`getpar grid`

2495 grid=${grid%%[!0-9]*}

2496 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2497 else

2498 grid=$1

2499 fi

2500 grp=`getgroupbyid $grid`

2501 qgrp=`sqlquote "$grp"`

2502 htmlgrp=`echo "$grp"|htmlescape`

2503 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2504 if isgroup "$grp"; then

2505 tf=$tmpd/title.$$

2506 sf=$tmpd/search.$$

2507 bodyclass=`query "SELECT val FROM grp_s

2508 WHERE gname=$qgrp AND key='regmode';"`

2509 if ismember "$user" "$grp"; then

2510 ismember="ismember"

2511 bodyclass="$bodyclass${bodyclass:+ }ismember"

2512 else

2513 ismember="" # bodyclass="group"

2514 fi

2515 bodyclass="$bodyclass grouphome"

2516 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2517 echo "グループ $htmlgrp" > $tf

2518

2519 showgroupsub $formdir/grp.def "$grid" | \

2520 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2521 -D_FORM_="syscmd(\`cat')" \

2522 -D_BODYCLASS_="$bodyclass" \

2523 -D_DUMPTABLE_="" \

2524 $htmlheader $sf $layout/form+dump.m4.html

2525 # $htmlheader $layout/form+dump.m4.html

2526 # $htmlheader is defined in grp()

2527 else # if $grp is removed at par2table

2528 listgroup

2529 fi

2530 }

2531 showgroupsub() {

2532 # $1=def-file $2=group-rowid

2533 # Using $ismember

2534 rowid=$2

2535 grp=`getgroupbyid $2`

2536 qgrp=`sqlquote "$grp"`

2537 td=`getcachedir grp/"$2"`

2538 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2539 if [ -z "$rowid" ]; then

2540 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2541 #grp=`sq $db "select gname from grp where rowid=$grp"`

2542 echo "showgroupsub: invalid argument($1 $2)" | html p

2543 return

2544 fi

2545 val=`getvalbyid grp profimg $rowid $tmpd`

2546 enticond="gname=$qgrp"

2547 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2548 if [ -n "$img" ]; then

2549 cat<<-EOF

2550 <p class="groupimg">

2551 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2552 EOF

2553 fi

2554 echo "<div class=\"noprofimg\">"

2555 viewtable $1 grp $rowid

2556 echo "</div>"

2557 if isgrpowner "$user" "$grp"; then

2558 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2559 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2560 iamowner=$rowid

2561 colmd=" mode"

2562 fi

2563 if [ -n "$ismember" ]; then

2564 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2565 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2566 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2567 cat<<-EOF

2568 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2569 href="?blog+$rowid">グループの新規話題作成</a>

2570 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2571 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2572 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2573 <div class="fold clear">

2574 `cgi_checkbox send yes id="send"`<label

2575 for="send">グループ全員にメッセージ送信</label>

2576 <div>

2577 `cgi_textarea message "" "cols=60"`

2578 `cgi_submit 送信`

2579 `cgi_reset リセット`

2580 </div>

2581 `cgi_hidden grp $rowid`

2582 </div></form>

2583 EOF

2584 fi

2585 # 加入ボタン + 加入者リスト

2586 if [ -n "$ismember" ]; then

2587 ismem='checked' state="(参加中)"

2588 else

2589 nomem='checked' state="(現在非加入)"

2590 fi

2591 # このグループでの加入アドレス

2592 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2593 and key='email';"`

2594 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2595 ## and key='email';"

2596 ##err email=$eml

2597 cat <<EOF

2598 <div class="fold clear">

2599 `cgi_checkbox reg yes id="reg"`<label

2600 for="reg">自身の加入状態を操作する</label>$state

2601 <div>

2602 EOF

2603 cgi_form grp <<EOF

2604 <p>このグループに</p>

2605 <table class="b">

2606 <tr><th>メンバーとして</th><td>

2607 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2608 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2609 <tr><th>参加する場合のメイルアドレス<br>

2610 <small>(メインのアドレスとは違うものにする場合に記入<br>

2611 同じでよい場合は空欄に)</small></th>

2612 <td>`cgi_text email $eml`</td></tr>

2613 </table>

2614 `cgi_hidden grp $rowid`

2615 EOF

2616 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2617 echo "moderated (承認加入の)グループなので実際に参加できるのは

2618 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2619 fi

2620 echo '</div></div>'

2621 echo '<h2>話題一覧</h2>'

2622 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2623 cat<<-EOF

2624 <form class="summary" action="$myname" title="$thelp">

2625 `cgi_hidden owner "$grp"`

2626 `cgi_hidden kwd "@week"`

2627 `cgi_hidden stage searchart`

2628 `cgi_submit "一週間のまとめ"`

2629 </form>

2630 EOF

2631 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2632 colstate="state:稼動状態:frozen=rowclass=凍結"

2633 DT_CHLD=article:blogid \

2634 DT_QOWNER="$qgrp" \

2635 DT_VIEW=replyblog dumptable html blog \

2636 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2637

2638 getgname="(select gname from grp where rowid=$rowid)"

2639 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2640 cm="?commission+$rowid"

2641 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2642 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2643 # team list

2644 hexteams=`hexteams "$grp"`

2645 if [ -n "$hexteams" ]; then

2646 echo "チーム一覧" | html h2

2647 echo '<div class="dumptable"><table class="b">'

2648 sq $db -html -header<<-EOF

2649 SELECT val TEAM,

2650 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2651 MEMBERS

2652 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2653 EOF

2654 echo '</table></div>'

2655 fi

2656 }

2657 grp_getbodyclass() {

2658 # Get css class name for document.

2659 # `moderated' for moderated groups

2660 # `ismember' for groups where user belongs

2661 # $1=GroupName (w/o quote)

2662 # $user=userNameCurrentlyLogin

2663 ## err grp_getbodyclass: 1="$1"

2664 qgrp=`sqlquote "$1"`

2665 query<<-EOF

2666 SELECT coalesce(

2667 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2668 'open')

2669 ||

2670 CASE WHEN '$user'

2671 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2672 THEN ' ismember'

2673 ELSE ''

2674 END;

2675 EOF

2676 }

2677 grpaction() { # $1=group-rowid

2678 err GRP_ACTION:IN

2679 grid=${1:-`getpar grp`}

2680 grp=`getgroupbyid "$grid"`

2681 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2682 if [ -z "$grp" ]; then

2683 echo "無効な指定です。" | html p; return

2684 fi

2685 if ! ismember $user "$grp"; then

2686 echo "加入者のみに許可された操作です。" | html p; return

2687 fi

2688 echo "グループ $grp 個別選択操作" \

2689 | _m4 -D_TITLE_="syscmd(\`cat')" \

2690 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2691 $layout/html.m4.html

2692

2693 isowner=""

2694 isgrpowner "$user" "$grp" && isowner="yes"

2695 usel=`getpar usel`

2696 if [ -n "$usel" ]; then

2697 uids=$(echo `echo $usel`|tr ' ' ',')

2698 ## err grpaction-1: grp=$grp, `echo $sql`

2699 text=`getpar text|tr -d '\r'`

2700

2701 rm=`getpar rm` cfm=`getpar confirm`

2702 ## err rm=$rm cfm=$cfm

2703 if [ x"$rm" = x"yes" ]; then

2704 if [ "$isowner" ]; then

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

2706 # Eliminate

2707 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2708 for tbl in grp_mem grp_mem_s grp_mem_m; do

2709 sql="delete from $tbl $cond;"

2710 # echo "sql=$sql"

2711 query "$sql"

2712 err rmGRPuser "$sql"

2713 done

2714 num=`query "select count(*) from user where rowid in ($uids);"`

2715 #err num=$num

2716 if [ 0$num -gt 0 ]; then

2717 sql="select coalesce(b.val,a.name) from user a left join \

2718 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2719 # err `echo "$sql"`

2720 html pre<<EOF

2721 以下の${num}名のグループ $grp 登録を解除しました。

2722 `query "$sql"`

2723 EOF

2724 fi

2725 else

2726 echo "確認のチェックがないのでやめておきます。" | html p

2727 return

2728 fi

2729 else # not Group Owner

2730 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2731 return

2732 fi

2733 cat<<EOF

2734

2735 EOF

2736 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2737 if [ -z "$text" ]; then # if msg is empty

2738 echo "なにかメッセージを..." | html p

2739 return 0

2740 fi

2741 gecos=`gecos $user`

2742 safegc=`echo "$gecos" | tr -d '<>@'`

2743 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2744 fromad=`myemail4group "$grp"`

2745 ###mail_from="$safegc <$fromad>"

2746 mail_from="$safegc <$user>" # TEST: 2020/5/13

2747 test -n `getpar sender` &&

2748 export SENDER=$user # TEST: 2020/5/15

2749 replyto=$fromad

2750

2751 ## Start parse of attachment files

2752 if [ -n "`getpar email`" ]; then

2753 ar=`getpar supprcpt`

2754 if [ -n "$ar" ]; then

2755 for a in $ar; do

2756 if checkdomain "$a"; then

2757 supprcpt="$supprcpt $a"

2758 else

2759 err "SupprcptErr=[$a] by $user"

2760 removercpt="$removercpt $a"

2761 fi

2762 done

2763 fi

2764 subj=`getpar subject`

2765 afiles=""

2766 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2767 do

2768 f=$tmpd/$fn

2769 if [ -s $f ]; then

2770 afiles=$afiles"${afiles:+ }$f"

2771 fi

2772 done

2773 else

2774 preface=$(cat <<-EOF

2775 $url

2776 のグループ「$grp」のメンバーである $gecos さんから、

2777 あなた宛のメッセージです。

2778 ----------------------------------------------------------

2779 EOF

2780 )

2781 fi

2782 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2783 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2784 subj="${subj:-$gecos さんからのメッセージ}"

2785 REPLYTO=$replyto

2786 MAIL_FROM=$mail_from

2787 export REPLYTO SMAIL_TO MAIL_FROM

2788 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2789 for r in $rcpts; do

2790 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2791 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2792 else

2793 # Show sender and rcpts address for guest

2794 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2795 fi

2796 if [ -n "$afiles" ];then

2797 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2798 else

2799 smail "$r" "$subj"

2800 fi <<EOF

2801 ${preface:+$preface$nl}$text

2802 EOF

2803 done

2804 if [ $? = 0 ]; then

2805 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2806 sql="select coalesce(b.val, a.name) from

2807 (select name from user where rowid in ($uids)) a

2808 left join user_s b on a.name=b.name and b.key='gecos';"

2809 html pre<<EOF

2810 `query "$sql"`

2811 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2812 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2813 EOF

2814 err SendDone: `echo $sql`

2815 fi

2816 elif [ x"$rm" = x"commission" ]; then

2817 grp_reg_adm $grid $usel

2818 elif [ x"$rm" = x"addteam" ]; then

2819 team=`getpar team|sed "s/'/''/g"` # for single quotation

2820 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2821 if [ x"$team" != x"$newteam" ]; then

2822 echo "チーム名に使えない文字を除去しました" | html p

2823 team=$newteam

2824 fi

2825 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2826 cat<<-EOF | html p

2827 有効なチーム名を入力してください。

2828 カンマだけ、「なし」という名前は使えません。

2829 EOF

2830 echo "有効なチーム名を入力してください。" | html p

2831 else

2832 grp_add_team $grid "$team" $usel

2833 fi

2834 elif [ x"$rm" = x"rmteam" ]; then

2835 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2836 rmteam=`getpar rmteam|sed "s/'/''/g"`

2837 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2838 gname='$grp' AND user='$user' AND key='team'\

2839 AND val='$rmteam';\"`" ]; then

2840 grp_rm_team $grid "$rmteam" $usel

2841 else

2842 echo "所属していないチームの除去操作はできません。"|html p

2843 fi

2844 else

2845 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2846 fi

2847 fi

2848 fi

2849 # POST count summary

2850 from=`getpar from`; to=`getpar to`

2851 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2852 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2853 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2854 # New entry

2855 sql="WITH mems AS (

2856 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2857 ON gm.user=g.name

2858 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2859 ), target_article AS (

2860 SELECT id FROM article_s

2861 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2862 ), posts AS (

2863 SELECT author, count(author) post

2864 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2865 WHERE blogid IN (SELECT id FROM blog_s

2866 WHERE key='owner'

2867 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2868 AND key='text'

2869 GROUP BY author

2870 ), teams AS (

2871 SELECT user, group_concat(val, ', ') team

2872 FROM grp_mem_m

2873 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2874 AND key='team'

2875 GROUP BY user

2876 ), user_post AS (

2877 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2878 FROM mems m LEFT JOIN posts

2879 ON m.name=posts.author

2880 GROUP by m.rowid

2881 )

2882 SELECT

2883 CASE

2884 WHEN (SELECT user FROM grp_adm

2885 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2886 AND user=up.name) IS NOT NULL

2887 then 'k'

2888 ELSE ''

2889 END || rowid || ',' || gecos NAME,

2890 post POST, team _TEAM_

2891 FROM user_post up LEFT JOIN teams t

2892 ON up.name=t.user

2893 ORDER BY gecos;"

2894 ## err grpaction: "`echo \"$sql\"`"

2895 tf=$tmpd/title.$$

2896 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2897 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2898 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2899 for=\"cmadmin\">管理者委任</label>

2900 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2901 </p></div>"

2902 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2903 title=\"Shortcut: g${nl}Remove from the Group\"

2904 for=\"conf\">GRP登録解除</label>

2905 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2906 <p>この操作による通知は本人に行きません。

2907 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2908 </div>"

2909 # Get team list to which current user belongs into $hexteams

2910 allhexteams=$(hexteams "$grp")

2911 if [ -n "$isowner" ]; then

2912 myhexteams="$allhexteams" # admin can remove all teams' attr

2913 else

2914 myhexteams=$(hexteams "$grp" "$user")

2915 fi

2916 if [ -n "$myhexteams" ]; then

2917 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2918 title=\"Shortcut: s${nl}Strip a team tag from\"

2919 for=\"cmrmteam\">チーム属性除去</label>

2920 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2921 を除去します: `cgi_checkbox teamconfirm yes` 確認

2922 <p>この操作による通知は本人に行きません。

2923 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2924 </div>

2925 "

2926 fi

2927 stf=$tmpd/selteam.$$

2928 cgi_select_h selteam "5445414d" $allhexteams > $stf

2929 b1='<label> <input type="checkbox" name="usel" value="'

2930 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2931 br='<span id="reverse" title="Reverse Selection"></span>'

2932 #b2='"> <span>' b3='</span></label>'

2933 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2934 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2935 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2936 cgi_form grpaction<<EOF \

2937 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2938 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2939 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2940 | _m4 -D_TITLE_="spaste(\`$tf')" \

2941 -D_SUBTITLE_="チェック後操作ボタン" \

2942 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2943 $layout/form+dump.m4.html \

2944 | _m4 -D_TEAM_="spaste(\`$stf')"

2945 <p>下でチェックした人を対象として:</p>

2946 <div class="foldtabs">

2947 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2948 title="Shortcut: a${nl}Add a team tag to"

2949 for="cmteam">同じチーム属性を付与</label>

2950 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2951 `cgi_datalist_h teams $allhexteams`

2952 </div>

2953 ${rmteammsg}

2954 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2955 title="Shortcut: d${nl}DirectMail to"

2956 for="sendmsg" title="Direct Message">DM送信</label>

2957 <div>

2958 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2959 title="Using email format">email書式を使う</label>

2960 <div class="folded">

2961 <table>

2962 <tr><td>From: </td><td>$user</td></tr>

2963 <tr><td>このFrom:で送る</td>

2964 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2965 </td></tr>

2966 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2967 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2968 <tr><td>ファイル添付: </td>

2969 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2970 </table>

2971 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2972 </div>

2973 <div>本文:`cgi_textarea text "" cols=72`

2974 </div>

2975 </div>

2976 ${isowner:+$cmmsg$excmsg}

2977 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2978 </div>

2979 <h4>$grp 参加者一覧</h4>$fromtonote

2980 <table class="td2r thl">

2981 `sq $db -header -html "$sql"`

2982 </table>

2983 `cgi_hidden grp $grid`

2984 `cgi_hidden myuid $myuid id="myuid"`

2985 EOF

2986 }

2987 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2988 # Create TEMPORARY VIEW

2989 query<<EOF

2990 CREATE TEMPORARY VIEW writeusers AS

2991 SELECT DISTINCT author FROM article

2992 WHERE id in (

2993 select id from article where blogid=(select id from blog where rowid=$1)

2994 );

2995 CREATE TEMPORARY VIEW movablegroups AS

2996 SELECT g.rowid growid , g.gname

2997 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2998 ON grp.gname=gm.gname -- そのユーザが属している

2999 AND user='$user') g -- グループに絞る

3000 WHERE (SELECT author FROM writeusers

3001 EXCEPT

3002 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3003 IS NULL;

3004 $2

3005 EOF

3006 }

3007 sql4readableblogs() {

3008 # Create view of blogs that can be readable to $user

3009 # Blog is readable when:

3010 # 1: blog owner is an user

3011 # 2: else, 2.1: owner-group where the $user belongs

3012 # 2.2: else, owner-group is not moderated

3013 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3014 cat<<EOF ## | tee tmp/sql.out

3015 CREATE TEMPORARY VIEW readableblogs AS

3016 SELECT blog.rowid rid, id, author

3017 FROM blog

3018 NATURAL JOIN

3019 (SELECT id,

3020 max(CASE key WHEN 'owner' THEN val END) owner,

3021 max(CASE key WHEN 'mode' THEN val END) mode

3022 FROM blog_s GROUP by id) bs

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

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

3025 WHEN (SELECT val FROM grp_s

3026 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3027 AND

3028 (SELECT user FROM grp_mem

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

3030 THEN 0

3031 WHEN mode IN ('quiz', 'enquete')

3032 THEN 0 -- "quiz" mode blog cannot be searched

3033 ELSE 1

3034 END;

3035 EOF

3036 }

3037 editheading() { # $1=rowid-of-heading

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

3039 if [ -z "$rowid" ]; then

3040 echo "話題番号が未指定です。" | html p

3041 return

3042 fi

3043 owner=`getvalbyid blog owner $rowid`

3044 title=`getvalbyid blog title $rowid`

3045 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3046 | _m4 -D_TITLE_="修正" \

3047 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3048 -D_BLOGS_="" -D_DUMPTABLE_="" \

3049 -D_FORM_="syscmd(\`cat')" \

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

3051 # Move to group

3052 if isuser "$owner"; then

3053 crview4article $rowid

3054 n=`query "SELECT count(*) FROM writeusers;"`

3055 ## err N=$n

3056 if [ $((n)) -gt 0 ]; then

3057 ## err ROWID=$rowid

3058 sql="SELECT growid || ':' || gname FROM movablegroups;"

3059 cat<<-EOF

3060 <div class="fold">

3061 `cgi_checkbox mv send id="mv"`<label

3062 for="mv">この話題をグループ所有に移動する</label>

3063 <div>

3064 <form action="?mvart" method="POST" enctype="multipart/form-data">

3065 移動先グループ:

3066 <select name="mv2grp">

3067 EOF

3068 query ".mode html"

3069 query<<-EOF |

3070 $sql

3071 .mode list

3072 EOF

3073 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3074 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3075 cat<<-EOF

3076 </select>

3077 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3078 そのグループに加入しているものに限られます)</p>

3079 <p>`cgi_checkbox cfm yes`<label>確認

3080 (この操作は元に戻すことができません)</label></p>

3081 `cgi_hidden blogrowid $rowid`

3082 `cgi_submit 移動`

3083 `cgi_reset Reset`

3084 </form>

3085 </div>

3086 </div>

3087 EOF

3088 fi

3089 # end of isuser "$owner"

3090 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3091 [ -n "$hexteams" ];}; then

3092 none="`echo なし|hexize`"

3093 cat<<-EOF

3094 <div class="fold">

3095 `cgi_checkbox mv2team send id="mv2team"`<label

3096 for="mv2team">この話題を以下のチームのものにする</label>

3097 <div><p>現在の所属チーム設定:

3098 `query "SELECT

3099 coalesce((SELECT val FROM blog_s

3100 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3101 AND key='team'),

3102 ':なし');"`</p>

3103 <form action="?mvart" method="POST" enctype="multipart/form-data">

3104 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3105 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3106 `cgi_hidden blogrowid $rowid`<br>

3107 `cgi_submit 移動`

3108 `cgi_reset Reset`

3109 </form></div></div>

3110 EOF

3111 fi

3112 }

3113 mvart() { # move diary to some group or team

3114 # or move blog of group to team which belong to the group

3115 blogrowid=`getpar blogrowid`

3116 cfm=`getpar cfm`

3117 ##### echo move blog:$blogrowid to $mv2grp | html p

3118 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3119 . ./s4-blog.sh

3120 if [ -z "$blogrowid" ]; then

3121 echo "無効な指定です(mvart)。" | html p

3122 return

3123 elif [ x"$cfm" != x"yes" ]; then

3124 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3125 elif { mv2grp=`getpar mv2grp`

3126 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3127 [ -n "$mv2grp" ]; }; then

3128 crview4article $blogrowid

3129 ########## TRANSACTION BEGIN

3130 query "BEGIN;"

3131 n=`query "SELECT count(*) FROM writeusers;"`

3132 ## err Nwriteuser=$n

3133 if [ $((n)) -gt 0 ]; then

3134 query<<-EOF

3135 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3136 WHERE key='owner'

3137 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3138 AND $mv2grp IN (SELECT growid FROM movablegroups);

3139 EOF

3140 fi

3141 query "END;"

3142 ########## TRANSACTION END

3143 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3144 [ -n "$mv2team" ];}; then

3145 # blog owner can move it to ANY team

3146 case "$mv2team" in

3147 'なし')

3148 cat<<-EOF

3149 DELETE FROM blog_s

3150 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3151 AND key='team';

3152 EOF

3153 ;;

3154 "") ;;

3155 *)cat<<-EOF

3156 BEGIN;

3157 REPLACE INTO blog_s(id, key, val)

3158 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3159 'team', '$mv2team');

3160 REPLACE INTO blog_s(id, key, val)

3161 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3162 'notify', 'all'); -- Change notify to all

3163 END;

3164 EOF

3165 esac | query

3166 fi

3167 blog_reply $blogrowid

3168 echo yes | html p

3169 }

3170 editart() { # $1=article-rowid $2=blogrowid

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

3172 blogrowid=${2%%[!A-Z0-9a-z_]*}

3173 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3174 echo "表示する記事番号が未指定です。" | html p

3175 return

3176 fi

3177 owner=`getvalbyid blog owner $blogrowid`

3178 title=`getvalbyid blog title $blogrowid`

3179 author=`getvalbyid article author $rowid`

3180 ## err EDITart: owner=$owner, author=$author

3181 if isgrpowner "$user" "$owner"; then

3182 : EDIT OK

3183 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3184 echo "本人か所有者しか編集できません." | html p

3185 return

3186 fi

3187 aid=`query "select id from article where rowid=$rowid;"`

3188 tmpout=$tmpd/editart.$$.out

3189 GF_ACTION="?replyblog+$blogrowid#$aid" \

3190 edittable $formdir/article.def article $rowid \

3191 > $tmpout

3192 rm -f /tmp/editart.out

3193 # Cannot use pipelining to m4 with genform() because of stdin stack

3194 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3195 -D_FORM_="syscmd(cat $tmpout)" \

3196 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3197 -D_BLOGS_= -D_DUMPTABLE_= \

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

3199 }

3200 send2mem() {

3201 rowid=`getpar grp`

3202 rowid=${rowid%%[!0-9]*} # Cleaning

3203 if [ -z "$rowid" ]; then

3204 echo "グループが未指定です。" | html p

3205 return

3206 fi

3207 message=`getpar message`

3208 if [ -z "$message" ]; then

3209 echo "文章を入れてください。" | html p

3210 return

3211 fi

3212 grp=`getgroupbyid $rowid`

3213 members=`collectemail $grp`

3214 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3215 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3216 mailfrom="`gecos "$user"` <$mailfrom>"

3217 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3218 msg=$(cat<<-EOF

3219 $urlbase?grp+$rowid

3220 グループ $grp に所属する

3221 `gecos $user` さんよりメッセージ:

3222

3223 $message

3224 EOF

3225 )

3226 # smail rcpt subj (file)

3227 for m in $members; do

3228 echo "$msg" |

3229 MAIL_FROM=$mailfrom \

3230 SENDER=$noreply \

3231 REPLYTO=$mailfrom \

3232 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3233 smail "$m" "$sj"

3234 done

3235 cat<<EOF

3236 <p>以下のユーザに送信しました。</p>

3237 <pre>

3238 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3239 </pre>

3240 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3241 EOF

3242 }

3243 joingrpadmit() {

3244 # $1=yes/no $2=session-key

3245 if [ -z "$2" ]; then

3246 echo "bye bye" | html p; return

3247 fi

3248 t_usr=`session=$2 getpar user`

3249 t_grp=`session=$2 getpar group`

3250 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3251 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3252 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3253 echo "無効な加入依頼です。" | html p

3254 echo "有効期限が切れたか、

3255 他の管理者がいる場合は処理済みの可能性があります。" | html p

3256 return

3257 fi

3258 if ! isgrpowner "$user" "$t_grp"; then

3259 echo "グループ管理者のみの機能です。" | html p; return

3260 fi

3261 case $1 in

3262 yes) joingrp "$t_grp" "$t_usr" yes ;;

3263 no) joingrp "$t_grp" "$t_usr" no ;;

3264 *)

3265 echo "無効な指定です($1)。" | html p

3266 return ;;

3267 esac

3268 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3269 rcpts="`getgroupadminmails $t_grp` $user"

3270 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3271 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3272 に

3273 $t_usr

3274 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3275 しました。"

3276 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3277 query "delete from session where id='$2';"

3278 echo "$body" | html p

3279 }

3280

3281 joingrprequest() {

3282 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3283 jss="joingrp-`date +%s`-`genrandom 12`"

3284 addsession $jss +${memoplimitdays}days

3285 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3286 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3287 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3288 $url

3289 $user さんから

3290 グループ $1

3291 に加入依頼がありました。

3292

3293 承認する:

3294 $urlbase?joingrpadmit+yes+$jss

3295

3296 白紙に戻す:

3297 $urlbase?joingrpadmit+no+$jss

3298 EOF

3299 echo "管理者に加入依頼を出しました。

3300 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3301 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3302 }

3303 joingrp() {

3304 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3305 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3306 if isgrpowner "$user" "$1"; then

3307 isowner="yes"

3308 elif [ -n "$5" ]; then

3309 isowner="yes"

3310 else

3311 isowner=""

3312 fi

3313 ## err jg:isgrpowner: isowner="$isowner"

3314 if [ -n "$isowner" ]; then

3315 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3316 elif [ x"$2" != x"$user" ]; then # if user is not login user

3317 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3318 return

3319 elif [ x"$3" = x"no" ]; then

3320 : # Do not pursue those who leave

3321 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3322 : # Member can change own email address for the joining moderated group

3323 else # adding user is $user itself

3324 case `getgroupattr $1 regmode` in

3325 moderated)

3326 joingrprequest "$@" # Request only

3327 return

3328 ;;

3329 *)

3330 ;;

3331 esac

3332 fi

3333 qgname=`sqlquote "$1"`

3334 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3335 cond="where gname=$qgname and user='$2'"

3336 if [ x"$3" = x"yes" ]; then

3337 query "replace into grp_mem values($qgname, '$2');"

3338 # Notify joingrp to admin

3339 action="に加入しました。"

3340 if [ -n "$4" ]; then

3341 if msg=`emaildomaincheck "$4"`; then

3342 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3343 'string', '$4', NULL);"

3344 else

3345 echo $msg

3346 fi

3347 else

3348 query "delete from grp_mem_s $cond and key='email';"

3349 fi

3350 if [ -n "$5" ]; then # as ADMIN

3351 # Coming here means newly created group

3352 sql="select case\

3353 when (select count(*) from grp_mem where gname=$qgname)=1\

3354 then (select user from grp_mem\

3355 where gname=$qgname and user='$user')\

3356 else '' end; "

3357 err NewGrpChk: $sql

3358 if [ -n "`query \"$sql\"`" ]; then

3359 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3360 query "replace into grp_adm values($qgname, '$user');"

3361 fi

3362 fi

3363 else

3364 query "begin;

3365 delete from grp_mem $cond;

3366 delete from grp_mem_s $cond;

3367 delete from grp_mem_m $cond;

3368 delete from grp_adm $cond;

3369 delete from grp_adm_s $cond;

3370 delete from grp_adm_m $cond;

3371 end;"

3372 action="から脱退しました。"

3373 fi

3374 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3375 $url?grp+$grid

3376 $user (`gecos $user`)さんが

3377 グループ $1

3378 $action

3379 EOF

3380 }

3381 grp_add_team() (

3382 # $1=grp-rowid $2=team $3...=user-rowid(s)

3383 grp=`getgroupbyid $1`

3384 team=$2; shift; shift

3385 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3386 { echo "BEGIN;"

3387 for user; do

3388 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3389 '$grp',\

3390 (SELECT name FROM user WHERE rowid=$user),\

3391 'team', 'string', '$team');"

3392 done

3393 echo "END;"

3394 } | query

3395 )

3396 grp_rm_team() (

3397 # $1=grp-rowid $2=team $3...=user-rowid(s)

3398 grid=$1

3399 qgrp=$(sqlquote "`getgroupbyid $grid`")

3400 team=$2; shift; shift

3401 [ -z "$grid" -o -z "$team" ] && return

3402 { echo "BEGIN;"

3403 for user; do

3404 echo "DELETE FROM grp_mem_m\

3405 WHERE gname=$qgrp \

3406 AND user=(SELECT name FROM user WHERE rowid=$user)\

3407 AND key='team' AND val='$team';"

3408 done

3409 cat<<-EOF

3410 DELETE FROM blog_s

3411 WHERE rowid=(

3412 SELECT rowid

3413 FROM blog_s a

3414 WHERE key='team'

3415 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3416 AND NOT EXISTS (SELECT * FROM grp_mem_m

3417 WHERE key='team' AND val=a.val -- a.val=team

3418 AND gname = (SELECT val FROM blog_s b

3419 WHERE a.id=b.id AND key='owner')

3420 ));

3421 EOF

3422

3423 echo "END;"

3424 } | query

3425 )

3426 grp_reg_adm() {

3427 # $1=grp-rowid $2...=user-rowid

3428 grid=$1

3429 grp=`getgroupbyid "$1"`

3430 if [ -z "$grp" ]; then

3431 echo "無効なグループIDです" | html p; return

3432 fi

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

3434 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3435 fi

3436 shift

3437 for urid; do

3438 newadm=`query "select name from user where rowid=$urid;"`

3439 if [ -z "$newadm" ]; then

3440 echo "指定ユーザIDがおかしいようです。" | html p; return

3441 fi

3442 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3443 err ismember $newadm $grp

3444 if ismember $newadm "$grp"; then

3445 # OK, go ahead

3446 getgname="(select gname from grp where rowid=$grid)"

3447 query "replace into grp_adm values($getgname, '$newadm');"

3448 # confirm insertion

3449 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3450 if [ -n "`query \"$sql;\"`" ]; then

3451 echo "追加完了: $newadm" | html p

3452 else

3453 echo "追加失敗($1 $urid)" | html p

3454 fi

3455 fi

3456 showgroup $grid

3457 done

3458 }

3459 dt_rowhack() {

3460 # From: <TR>

3461 # ....

3462 # <TD>rowclass=foo</TD>

3463 # </TR>

3464 # To: <TR class="foo">....<TD>foo</TD></TR>

3465 sed -e '

3466 /^<TR>/ {

3467 :loop

3468 s/\n//

3469 N

3470 /<\/TR>/ {

3471 s/\n//

3472 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3473 n

3474 }

3475 $q

3476 b loop

3477 }'

3478 }

3479 dumptable() {

3480 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3481 # textのフィールドだけ全てダンプにしたほうがいいか

3482 # $DT_VIEW sets link

3483 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3484 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3485 VIEW=${DT_VIEW-replyblog}

3486 if [ -n "$VIEW" ]; then

3487 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3488 fi

3489 sqlfile=$tmpd/dump.sql

3490 : > $sqlfile # ensure to be empty

3491 printf '.mode html\n.header 1\n' > $sqlfile

3492 # $DT_CHLD=ChildTable:BindColumn

3493 if [ -n "$DT_CHLD" ]; then

3494 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3495 cat<<-EOF >> $sqlfile

3496 -- presql

3497 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3498 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3499 EOF

3500 # Speed up counting of new articles

3501 cat<<-EOF >> $sqlfile

3502 -- presql2

3503 DROP TABLE IF EXISTS _counts;

3504 CREATE TEMPORARY TABLE _counts AS

3505 SELECT $_i, count($_i) cnt

3506 FROM $_t GROUP BY $_i;

3507 /* Prepare NEW count table */

3508 CREATE TEMPORARY TABLE _target AS

3509 SELECT b.rowid trowid, b.id

3510 FROM "$2" b JOIN "$2_s" s

3511 ON b.id=s.id AND s.key='owner'

3512 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3513

3514 DROP TABLE IF EXISTS _children;

3515 CREATE TEMPORARY TABLE _children AS

3516 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3517 FROM (SELECT t.trowid, t.id $_i, a.id

3518 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3519 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3520

3521 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3522 DROP TABLE IF EXISTS _news;

3523 DROP VIEW IF EXISTS _news;

3524

3525 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3526 -- INSERT INTO _news

3527 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3528 CREATE TEMPORARY VIEW _news AS

3529 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3530 FROM (SELECT DISTINCT id FROM _target)

3531 a LEFT JOIN

3532 (SELECT $_i, count(ctime) newcnt

3533 FROM _children x

3534 WHERE ctime > coalesce((SELECT time from myacclog

3535 WHERE tblrowid=x.trowid),

3536 '1970-01-01')

3537 GROUP BY $_i) b

3538 ON a.id=b.$_i;

3539 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3540 EOF

3541 # REMOVE next line until 2019/5/1

3542 cntall="(select count($_i) from $_t where $_i=a.id)"

3543 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3544 # REMOVE next assignment until 2019/5/1

3545 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3546 and id in (select id from $_t where $_i=a.id) \

3547 and val > coalesce((select time from myacclog where \

3548 tblrowid=a.rowid),\

3549 '1970-01-01'))"

3550 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3551 cnt="$cntnew as '新着', $cntall as '総数',"

3552 dt_class=" td2r td3r dumpblogs"

3553 fi

3554 # Construct join expression

3555 eav="" scols=""

3556 pk=`gettblpkey $2`

3557 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3558 substr=${substr:-%s}

3559 for col in ${3:-`gettbl_s_cols $2`}; do

3560 valvar=val

3561 case $col in

3562 gecos) scols="$scols${scols:+, }${col#}"

3563 continue ;; # built-in column name

3564 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3565 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3566 case "$as" in

3567 *:*=*) cnd=${as#*:}

3568 h=${cnd%%=*} v=${cnd#*=}

3569 h=`sqlquotestr "$h"`

3570 v=`sqlquotestr "$v"`

3571 valvar="CASE val WHEN $h THEN $v END"

3572 as=${as%%:*} ;;

3573 esac

3574 ;;

3575 *) as=${col} ;;

3576 esac

3577 ss=`printf "$substr" "$valvar"`

3578 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3579 scols="$scols${scols:+, }b.$as"

3580 done

3581 #case author when '$user' then a.rowid else '---' end as ID,

3582 if [ -n "$DT_SQL" ]; then

3583 echo "$DT_SQL"

3584 else

3585 cat<<-EOF

3586 SELECT a.rowid as LINK, $cnt $scols

3587 FROM $2 a LEFT JOIN

3588 (SELECT $pk,$eav,

3589 max(CASE key

3590 WHEN 'owner'

3591 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3592 as gecos

3593 FROM ${2}_s c GROUP BY $pk)

3594 b ON a.$pk=b.$pk $4;

3595 EOF

3596 fi >> $sqlfile

3597 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3598 sqlog<<-EOF

3599 *** SQL-file: $sqlfile ***

3600 `cat $sqlfile`

3601 EOF

3602 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3603 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3604 cat $sqlfile >> tmp/sql

3605 # query ".read $sqlfile" > $tmpd/foo

3606 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3607 cp $tmpd/foo tmp/

3608 err "----- `gdate +%FT%T.%3N` ------------555555"

3609 ## $ddd LINE exists at the end of this function

3610 fi

3611 printf '.mode list\n.header 0\n' >> $sqlfile

3612 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3613 <div>

3614 <div class="dumptable">

3615 <table class="b$dt_class">

3616 `query ".read $sqlfile"`

3617 </table>

3618 </div>

3619 </div>

3620 EOF

3621 ### `query ".read $sqlfile"`

3622 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3623

3624 ## REMOVE THIS!

3625 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3626 }

3627

3628 par2table() (

3629 # copy current parameters of par into destination table

3630 # $1=definition-file

3631 # Using $user and $session

3632 # Return value:

3633 # 0: Stored successfully

3634 # 1: Insufficient fillings

3635 # 2: No permission to modify the record

3636 # 3: Invalid rowid

3637 # 4: SUCCESS to delete

3638 # 5: Stop deletion for lack of confirm check

3639 # 6: Password length too short

3640 # 7: Password mismatch

3641 # 8: Old password incorrect

3642 rowid=`getpar rowid`

3643 if [ ! -e $1 ]; then

3644 echo "テーブル定義ファイルが見付かりません" | html p

3645 exit 1

3646 fi

3647 tbl=${1%.def}

3648 tbl=${tbl##*/}

3649 if [ -n "$rowid" ]; then # Modify existing entry

3650 if [ x"$tbl" = x"user" ]; then

3651 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3652 elif [ x"$tbl" = x"grp" ]; then

3653 sql="select gname from $tbl where rowid=$rowid;"

3654 ##err p2t:grp:q $sql

3655 isgrpowner "$user" "`query $sql`" && rowowner=$user

3656 elif [ x"$tbl" = x"blog" ]; then

3657 # Check if owner in blog_s

3658 blogowner=`getvalbyid blog owner "$rowid"`

3659 if isgrpowner "$user" "$blogowner"; then

3660 rowowner=$user

3661 else

3662 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3663 fi

3664 else

3665 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3666 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3667 fi

3668 ### err rowowner=$rowowner

3669 if [ x"$user" != x"$rowowner" ]; then

3670 echo "他人のレコードはいじれないの" | html p

3671 return 2

3672 elif [ -z "$rowowner" ]; then

3673 echo "指定したレコードはないみたい" | html p

3674 return 3

3675 fi

3676 rm=`getpar rm` cfm=`getpar confirm`

3677 # Editing existent entry

3678 if [ x"$rm" = x"yes" ]; then

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

3680 query "delete from $tbl where rowid=$rowid;"

3681 return 4

3682 else

3683 echo "消去確認のチェックがないので消さなかったの..." | html p

3684 return 5

3685 fi

3686 fi

3687 fi

3688

3689 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

3691 # Update of existing record

3692 for col in `gettblcols $tbl`; do

3693 val=`getparquote $col`

3694 [ -z "$val" ] && continue

3695 ## err query "update $tbl set $col=$val where rowid=$rowid"

3696 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3697 sql="update $tbl set $col=$val where rowid=$rowid;"

3698 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3699 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3700 ## User name cannot be changed with interface provided with this

3701 ## script. But we offer the trigger to change owner user

3702 ## of blog_s table.

3703 #err "select quote($col) from $tbl where rowid=$rowid;"

3704 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3705 cat<<-EOF | query

3706 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3707 -- should use EXCLUSIVE transaction outside of this.

3708 SAVEPOINT par2table;

3709 $sql

3710 update blog_s set val=$val

3711 where key='owner' and val=$old;

3712 RELEASE SAVEPOINT par2table;

3713 EOF

3714 ## XX: DIRTY Hack Ends here

3715 ## We should keep blog's owner as a single column which has

3716 ## foreign key constraint with primary key of grp/user.

3717 else

3718 query "$sql"

3719 fi

3720 done

3721 # Then, set up $pval for further insertion of tbl_s and tbl_m

3722 for col in `gettblpkey $tbl`; do

3723 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3724 pval="$pval${pval:+, }\"$val\""

3725 done

3726 else

3727 # New entry

3728 # Generate values() for primary keys

3729 for col in `gettblpkey $tbl`; do

3730 # Genuine primary keys for _m and _s

3731 val=`getvalquote $tbl $col`

3732 [ -z "$val" ] && continue

3733 pval="$pval${pval:+, }$val"

3734 done

3735 ##err pval=$pval

3736 for col in `gettblfkey $tbl`; do

3737 # args for values() to insertion into master table

3738 val=`getvalquote $tbl $col`

3739 [ -z "$val" ] && continue

3740 formaster=$formaster"${formaster:+, }$val"

3741 done

3742 formaster="$pval${formaster:+, }$formaster"

3743 ## err formaster=$formaster

3744 if [ -z "$formaster" ]; then

3745 echo "項目を全て埋めてください" | html pre

3746 return 1

3747 fi

3748 ## err "replace into $tbl values($formaster);"

3749 query "replace into $tbl values($formaster);"

3750 ## Insertion to master table, done

3751 fi

3752

3753 transaction=$tmpd/sqlfile.sql; touch $transaction

3754 for kt in s m; do

3755 tb2=${tbl}_$kt

3756 for col in `gettbl_${kt}_cols $tbl`; do

3757 ptype=`getpartype $col "limit 1"`

3758

3759 # First, check update of existing entries in _m

3760 if [ $kt = m ]; then

3761 # sessID|address.1.22|string|Somewhere-x.y.z

3762 sql=""

3763 ##err dots from query "select var from par where var like '$col.%';"

3764 for v in `query "select var from par where var like '$col.%';"`; do

3765 # v=address.1.22

3766 st_rowid=${v##*.}

3767 origcol=${v%%.*} # original column derived from

3768 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3769 ##case `getpartype $v` in

3770 ## err CASE `gettbl_coltype $tbl/$origcol` in

3771 ## err edit flag = `getpar action.$v`

3772 case `getpar action.$v` in

3773 rm)

3774 if [ x`getpar confirm.$v` = x"yes" ]; then

3775 newsql="delete from $tb2"

3776 else

3777 echo "削除確認未チェック" | html p

3778 fi ;;

3779 edit)

3780 case `gettbl_coltype $tbl/$origcol` in

3781 image|document|binary)

3782 file=$tmpd/`getparfilename $v`

3783 ## err type=file=$file

3784 [ -z "$file" ] && continue

3785 bn=`sqlquotestr "${file##*/}"`

3786 bin="X'"$(hexize "$file")"'"

3787 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3788 type=\"file:$ct\"

3789 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3790 cachedir=`getcachedir "$tbl/$rowid"`

3791 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3792 rm -rf $cachedir

3793 ;;

3794 *)

3795 newsql="update $tb2 set val=(select val from par where var \

3796 like '$col.%.$st_rowid')"

3797 ;;

3798 esac

3799 ;;

3800 *) # maybe "keep", do not modify value

3801 continue

3802 ;;

3803 esac

3804 # err newsql=$newsql

3805 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3806 done

3807

3808 if [ x"$bin" = x"NULL" ]; then

3809 ## err repl:normal sql=`echo $sql`

3810 if [ -n "$transaction" ]; then

3811 cat<<-EOF >> $transaction

3812 $sql

3813 DELETE FROM $tb2 WHERE type='string' AND val='';

3814 EOF

3815 else

3816 query "$sql

3817 delete from $tb2 where type='string' and val='';"

3818 ## err repl:normal done

3819 fi

3820 else

3821 # Binary update line is TOO LONG to pipelining

3822 sqlfile="$tmpd/sqlf.$$"

3823 if [ -n "$transaction" ]; then

3824 echo "$sql" >> $transaction

3825 else

3826 echo "$sql" > $sqlfile

3827 query ".read $sqlfile"

3828 fi

3829 fi

3830 # Rest of kt==m: set multiple mode

3831 nr=`getparcount $col`

3832 else

3833 nr=1 # for kt==s, number of records is 1

3834 fi

3835

3836 i=0

3837 while [ $i -lt $nr ]; do

3838 limit="limit 1 offset $i"

3839 i=$((i+1)) # increase beforehand against continue

3840 val=`getvalquote $tbl $col "$limit"`

3841 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3842 ## err $col=$val

3843 bin=NULL

3844 ## err partype$col=`getpartype $col "$limit"`

3845 case $ptype in

3846 file) file=$tmpd/`getparfilename $col "$limit"`

3847 ## err parfile-$col=$file

3848 [ -z "$file" ] && continue

3849 bin="X'"$(hexize "$file")"'"

3850 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3851 type=\"file:$ct\" ;;

3852 "*"*) continue ;; # foreign table

3853 *) type=\"string\" ;;

3854 esac

3855 case `gettbl_coltype $tbl/$col` in

3856 password) # special care for password

3857 # name={password,pswd1,pswd2}

3858 p1=`getpar pswd1 "$limit"`

3859 if [ -z "$p1" ]; then

3860 continue # SKIP password setting, if p1 is empty

3861 else

3862 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3863 ## err pswd=$pswd

3864 if pwcheck "$pswd"; then

3865 if [ x"$p1" = x"$p2" ]; then

3866 case "$p1" in

3867 ??????????*) ;;

3868 *) echo "パスワードは10字以上にしてください。" | html p

3869 return 6;;

3870 esac

3871 val="\"`echo $p1|mypwhash`\""

3872 else

3873 echo "2つの新パスワード不一致" | html p

3874 return 7

3875 fi

3876 else

3877 echo "旧パスワード違います" | html p

3878 return 8

3879 fi

3880 fi

3881 ;;

3882 esac

3883 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3884 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3885 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3886 if [ x"$bin" = x"NULL" ]; then

3887 ## err Normal-query: `echo $sql`

3888 if [ -n "$transaction" ]; then

3889 echo "$sql" >> $transaction

3890 else

3891 query "$sql"

3892 fi

3893 else

3894 sqlfile="$tmpd/query.$$"

3895 ## err sqlfile=`ls -lF $sqlfile`

3896 if [ -n "$transaction" ]; then

3897 echo "$sql" >> $transaction

3898 else

3899 echo "$sql" > $sqlfile

3900 query ".read $sqlfile"

3901 fi

3902 fi

3903 ## err p2t done

3904 done

3905 done

3906 done

3907 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3908 -- We cannot use transaction here, because groupupdate may use it.

3909 SAVEPOINT pa2table_insert;

3910 .read $transaction

3911 RELEASE SAVEPOINT pa2table_insert;

3912 EOF

3913 return $?

3914 ##err donee

3915 )

3916 genform() {

3917 # $1 = form definition file

3918 # $2, $3 (optional)= table name and ROWID

3919 # If $GF_VIEWONLY set and nonNull, output values without form

3920 # If $GF_ARGS set, use it as content-strings in the form

3921 # If $GF_OWNER set, use it as value of name="owner"

3922 # If $GF_STAGE set, use it as value of name="stage"

3923 forms="" hiddens="" rowid=$3

3924 if [ ! -e "$1" ]; then

3925 echo "そのようなデータベースはないようです($2)。" | html p

3926 return

3927 elif [ -n "$2" ]; then

3928 rec=`query "select * from $2 where rowid='$rowid';"`

3929 if [ -z "$rec" ]; then

3930 pk=`gettblpkey $2`

3931 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3932 rec=`query "select rowid from $2 where $pk='$rowid';"`

3933 rowid=$rec

3934 rec=$3

3935 fi

3936 if [ -z "$rec" ]; then

3937 echo "そんなレコードはないみたいね..." | html p

3938 return

3939 fi

3940 fi

3941 if [ -z "$GF_VIEWONLY" ]; then

3942 rm='<input id="rm" name="rm" type="checkbox"

3943 value="yes"><label for="rm">このエントリの削除</label>

3944 <span>ほんとうに消しますよ(確認)!

3945 <input name="confirm" type=checkbox value="yes">はい</span>'

3946 fi

3947 # Image Cache dir

3948 ## err genform: getcache=$2/$rowid

3949 td=`getcachedir "$2/$rowid"`

3950 while IFS=: read prompt name keytype type args; do

3951 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3952 sp="${args:+ }"

3953 form="" val=""

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

3955 # err genform2a: Seeking for "$2.$name, type=$type"

3956 rawval=`getvalbyid $2 $name $rowid $td`

3957 val=`echo "$rawval"|htmlescape`

3958 ## err genform3a: getvalbyid $2 $name $rowid $td

3959 ## err genform3b: val="[$val]" type="$type"

3960 fi

3961 if [ -n "$GF_VIEWONLY" ]; then

3962 is_hidden "$2" "$name" && continue

3963 fi

3964 case "$type" in

3965 text*)

3966 cgiform=cgi_multi_$type

3967 if [ -s $td/$name.count -a -n "$val" ]; then

3968 form=`$cgiform $name $td`

3969 val=$(echo "$val"|

3970 while read fn; do

3971 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3972 </td></tr>$nl"

3973 done)

3974 val="<table>$nl$val$nl</table>"

3975 else

3976 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3977 form=`cgi_$type $name "$rawval" "$args"`

3978 fi

3979 ;;

3980 [Rr][Aa][Dd][Ii][Oo])

3981 fh="<label><input type=\"radio\" name=\"$name\""

3982 form="`echo $args|sed -e \

3983 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3984 ;;

3985 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3986 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3987 ;;

3988 [Ss][Ee][Ll][Ee][Cc][Tt])

3989 fh="<select name=\"$name\">$nl"

3990 form=$(for l in $args; do

3991 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3992 done)

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

3994 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3995 fi

3996 form="$fh$form</select>"

3997 ;;

3998 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3999 if [ -s $td/$name.count ]; then

4000 form=`cgi_multi_file $name $td "$args"`

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

4002 hrfb="$myname?showattc+$2_m"

4003 val=$(echo "$rawval" \

4004 | while read fn; do

4005 data=`percenthex "$td/$fn"`

4006 #ct=`cat $td/$fn.content-type`

4007 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4008 ri=`cat "$td/$fn.rowid"`

4009 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4010 #imgsrc="<img src=\"data:$ct,$data\">"

4011 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4012 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4013 done)

4014 fi

4015 else

4016 form="<input type=\"file\" name=\"$name\" $args>"

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

4018 imgs=$(echo "$rawval"\

4019 |while read fn;do

4020 data=`percenthex "$td/$fn"`

4021 echo "<img src=\"data:image/png,$data\">$fn<br>"

4022 done)

4023 form=$form"<br>$imgs"

4024 val=$imgs # 2015-06-15

4025 else

4026 form="<input type=\"file\" name=\"$name\" $args>"

4027 fi

4028 fi

4029 ;;

4030 [Hh][Ii][Dd][Dd][Ee][Nn])

4031 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4032 args="value=\"$GF_STAGE\""

4033 fi

4034 form="<input type=\"hidden\" name=\"$name\" $args>"

4035 prompt='' # Remove prompt

4036 ;;

4037 [Aa][Uu][Tt][Hh][Oo][Rr])

4038 [ -n "$GF_VIEWONLY" ] && continue

4039 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4040 prompt="" ;;

4041 [Oo][Ww][Nn][Ee][Rr])

4042 [ -n "$GF_VIEWONLY" ] && continue

4043 val=${GF_OWNER:-$val}

4044 val=${val:-$user}

4045 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4046 prompt="" ;;

4047 [Uu][Ss][Ee][Rr])

4048 # XXX: is null $user ok?

4049 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4050 [ -n "$GF_VIEWONLY" ] && continue

4051 form="$user"

4052 ;;

4053 [Pp]assword)

4054 [ -n "$GF_VIEWONLY" ] && continue

4055 form="`cgi_passwd`"

4056 val=""

4057 ;;

4058 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4059 [ -n "$GF_VIEWONLY" ] && continue

4060 if [ -z "$rowid" ]; then

4061 val=`genserial`

4062 fi

4063 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4064 prompt="" ;;

4065 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4066 prompt=""

4067 ;;

4068 parent|path|blog*)

4069 prompt=""

4070 ;;

4071 "*"*)

4072 tail=$tail"``"

4073 continue ;;

4074 esac

4075 if [ -n "$prompt" ]; then

4076 if [ -n "${GF_VIEWONLY}" ]; then

4077 form=$val

4078 else

4079 :

4080 fi

4081 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4082 else

4083 hiddens=$hiddens$nl"$form"

4084 fi

4085 done < $1

4086 # enctype="multipart/form-data"

4087 cat<<EOF

4088 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4089 ${rowid:+$rm}

4090 <table class="b $2">

4091 $forms

4092 </table>$hiddens

4093 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4094 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4095 EOF

4096 if [ -z $GF_VIEWONLY ]; then

4097 cat<<EOF

4098 <input type="submit" name="sub" value="OK">

4099 <input type="reset" name="res" value="Reset">

4100 EOF

4101 fi

4102 cat<<EOF

4103 $GF_ARGS</form>

4104 $tail

4105 EOF

4106 }

4107 edittable() {

4108 # $1=form-def $2=table $3 rowid

4109 genform "$@"

4110 }

4111 viewtable() {

4112 GF_VIEWONLY=1 genform "$@"

4113 }

4114 showattc() {

4115 # $1=table_m $2=rowid &optional $3=RawFlag

4116 ## err \$1=$1 \$2=$2 \$3=$3

4117 if ! isfilereadable $user $1 $2; then

4118 contenttype; echo

4119 echo "このファイルは管理者のみしか見られません" | html p

4120 putfooter; exit

4121 fi

4122 idir=`umask 002; mktempd` || exit 1

4123 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4124 bin=$idir/$myname-$$.bin

4125 sql="select quote(bin) from $1 where rowid='$2';"

4126 ## err showattc: sql: $sql

4127 sq $db "$sql" | unhexize > $bin

4128 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4129 type=${tv%//*} fn=${tv#*//}

4130 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4131 ct=${type#file:}

4132 case $ct in # all text/* changed to text/plain

4133 text/*)

4134 charset=`nkf -g $bin|cut -d' ' -f1`

4135 case $charset in

4136 ASCII*) charset="" ;;

4137 esac

4138 if [ -z "$3" ]; then

4139 ct="text/html${charset:+; charset=$charset}"

4140 link="?showattc+$1+$2+raw"

4141 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4142 | sed 's,^,<span></span>,' \

4143 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4144 -D_LINK_="$link" \

4145 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4146 exit $?

4147 fi

4148 ct="text/plain${charset:+; charset=$charset}"

4149 ;;

4150 esac

4151 contenttype "$ct"

4152 echo "Content-Disposition: filename=\"$fn\""

4153 echo "Content-Length: " `cat $bin | wc -c`; echo

4154 #echo "Content-Type: " ${type#file:}; echo

4155 cat $bin

4156 }

4157 #

4158 # Some default stupid handler on CGI values

4159 #

4160 default_storedb() {

4161 # ARG: $1=table-def-file

4162 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4163 tbl=`basename $1`

4164 tbl=${tbl%.def}

4165 cols="`grep :text $1|cut -d: -f2`"

4166 col=`echo "$cols"|head -1`

4167 vcol=`getpar $col`

4168 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4169 if [ -n "$vcol" ]; then

4170 par2table $1

4171 else

4172 return 2 # No insertion occurred

4173 fi

4174 }

4175

4176 default_view() { # $1=def-file

4177 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4178 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4179 default_storedb "$@"

4180 query "select rowid from $tbl order by rowid desc;" \

4181 | while read rowid; do

4182 viewtable $1 $tbl $rowid

4183 done | _m4 -D_TITLE_="$tbl" \

4184 -D_FORM_="`genform $1`" \

4185 -D_DUMPTABLE_="syscmd(cat)" \

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

4187 }

4188 default_viewtext() { # $1=def-file

4189 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4190 default_storedb "$@"

4191 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4192 | _m4 -D_TITLE_="$tbl" \

4193 -D_FORM_="`genform $1`" \

4194 -D_DUMPTABLE_="syscmd(cat)" \

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

4196 }

4197 default_smail() {

4198 default_storedb "$@"

4199 if [ $? -eq 2 ]; then

4200 _m4 -D_TITLE_="入力" \

4201 -D_FORM_="`genform $1`" \

4202 -D_DUMPTABLE_="" \

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

4204 return

4205 fi

4206 cond=""

4207 for pk in `gettblpkey $tbl`; do

4208 pv=$(sqlquote "$(getpar $pk)")

4209 cond="$cond${cond:+ and }$pk=$pv"

4210 done

4211 sql="select rowid from $tbl where $cond;"

4212 rowid=`query "$sql"`

4213 ## err smail1 - "$sql" "-> rowid=$rowid"

4214

4215 while IFS=: read prompt name keytype type args; do # Read from $1

4216 val=`getpar $name`

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

4218 text="$text

4219 $prompt

4220 $name=$val

4221 ---------------------------------------------------------"

4222 fi

4223 case "$type" in

4224 image|document|file)

4225 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4226 fns=$(echo "$fn"|while read fn; do

4227 err mv $tmpd/$fn.orig $tmpd/$fn

4228 mv $tmpd/$fn.orig $tmpd/$fn

4229 rm $tmpd/$fn.rowid # Remove cache flag

4230 ## err "`ls $tmpd/$fn`"

4231 echo $fn

4232 done)

4233 files="$files $fns"

4234 ;;

4235 esac

4236 done < $1

4237 ## err FILES=$files "`ls -lF $tmpd`"

4238 subj="from ${REMOTE_ADDR}"

4239 (echo "$url"

4240 echo "への書き込みがありました。"

4241 echo "------"

4242 echo "$text"

4243 ) | (cd $tmpd &&

4244 err LS="`ls -lF`" &&

4245 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4246 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4247 echo "以下の内容で送信しました。" | html p

4248 viewtable $1 $tbl \

4249 `query "select rowid from $tbl order by rowid desc limit 1;"`

4250 echo "戻る" | html a "href=\"?\""

4251 }