s4: 98702a1251cd s4-funcs.sh

s4

view s4-funcs.sh @ 732:98702a1251cd

merged

author	HIROSE Yuuji <yuuji@gentei.org>
date	Tue, 02 Jun 2020 07:52:04 +0900
parents	33df7d50532e b73e2399994c
children	ebc1c0b19bf7 1ab3cc61a462

line source

1 #!/bin/sh

2 # Here's global variable table. Do not use this names.

3 # $HGid$

5 [ -f s4-config.sh ] && . ./s4-config.sh

7 myname=`basename ${SCRIPT_NAME:-$0}`

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

9 myargs="$@"

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

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

12 tmpdir=${TMPDIR:-tmp}

13 dbdir=${DBDIR:-db}

14 tmpfiles=""

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

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

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

18 workdb=$dbdir/tmpdata.sq3

19 sessdb=$dbdir/sess.sq3

20 sesstb=tmp.sess

21 listentlimit=${LISTENTLIMIT:-30}

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

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

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

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

26 templ=${TEMPL:-templ}

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

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

29 imgdir=${IMGDIR:-img}

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

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

32 msgdir=$templ/msg

33 timeout="+2 days"

34 memoplimitdays="7"

35 dumpcollen=22

36 #thumbxy=120x120

37 thumbxy=96x96

38 iconxy_S=80x80

39 iconxy_M=400x400

40 maximagexy=1600x1600

41 ### maximagexy=400x400

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

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

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

45 file_accept='accept=".jpg,.jpeg,.gif,.png,.tiff,.pdf,.odt,.ods,.odp,.odg,.mp3,.mp4,.avi,.ogg,.mov,.webm,.gpx,.json,.geojson,.kml,.html,.css,.rb,.c,.txt,.zip,.xcf,.bz2,.gz,.xz,.7z,.csv,.dat"'

47 file_accept_help="

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

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

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

51 "

52 file_warn="$file_accept_help

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

54 blogreadflagrowid=0

55 blogcutoffflagrowid=-1

56 whatsnewdays=${WHATS_NEW_DAYS:-14}

58 tconfs=""

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

60 conftbl=_tblconf

61 nl="

62 "

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

64 iconcachekey="profimgcache_S"

65 case "$HTTP_USER_AGENT" in

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

67 *) touchpanel="" ;;

68 esac

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

71 : <<EOF

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

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

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

76 レポート提出管理

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

79 [2016]

80 7/12 根本への反省

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

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

84 [以下2015]

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

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

87 Web

88 締切設定

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

91 ○自分の提出物リスト

93 7/19 ○設置

94 ○一斉送信

95 ○getparfilename の tmpd の扱い

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

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

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

99 ○添付ファイル回収

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

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

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

103

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

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

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

107 ○レポート提出モード

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

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

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

111

112

113 7/9 ○管理者の追加

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

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

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

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

118

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

120

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

122 ○グループ検索

123

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

125

126

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

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

129

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

131 ○6/1 par2tableを triplex 対応に

132 select "yuuji@gentei.org",var,"text",NULL,val from par where var in (select col from _tblconf where tbl="/user" and keytype in ('p', 's'));

133 →とすると一気に

134

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

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

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

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

139

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

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

142 ## 1. passwd

143 ## 2. grp

144 ## 3. grp_mem

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

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

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

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

149

150 ## ● listの定義:

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

152 ## ● hashの定義:

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

154

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

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

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

158 ## csv2sq3 で .csv.sq3 の Makefile

159

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

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

162 ## type := root | comment

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

164 ## unique(id, filename)

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

166

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

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

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

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

171 ## できたー!

172 ## with recursive allmem as (select * from grp_mem where gname='bar' union all select grp_mem.* from grp_mem,allmem where allmem.name=grp_mem.gname) select * from allmem where type='u';

173 ↓

174 ↓以下に変更

175 with recursive allmem as

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

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

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

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

180

181

182 with recursive allmem as

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

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

185 where allmem.val=grp_m.gname)

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

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

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

189

190

191 ## triggerもできた。

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

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

194

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

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

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

198 ## グループ管理

199 ## 個人情報管理

200 ##

201

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

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

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

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

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

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

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

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

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

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

212

213 ## ログテーブル

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

215

216

217

218 ■表設計

219 * 3つの表に分散管理

220 id格納表 + hash表 + list表

221 * *_s *_m

222

223

224

225 user, user_map, user_col

226

227 ■抽象エントリタイプ

228 * user

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

230 * group

231 権限判定に利用

232 * serial

233 自動idとして機能

234 * password

235 入力 type=passwordで入力

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

237 * session

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

239 * text

240 入力 type=text

241 * textarea

242 入力 textarea

243 * image|document

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

245 * owner

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

247 * gowner

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

249 * timestamp

250 datetime()

251 * parent

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

253 * path

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

255

256 格納タイプ

257 * list

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

259 * hash

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

261

262 オブジェクトタイプ

263 * entry

264 id, title, owner

265 * textpart

266 id, parentID, text

267 * binarypart

268 id, parentID, contenttype, filename, content

269 * content

270 hash(textpart), list(binarypart)

271 * topic

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

273 * reply

274 id, parentID, content

275 * blog

276 list(entry)

277 blog = [topic, list(reply)]

278

279

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

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

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

283 "author" => "taro",

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

285 "parent" => "/",

286 "path" => "/1",

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

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

289 {"serial" => 2,

290 "author" => "hanako",

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

292 "parent" => "/",

293 "path" => "/2",

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

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

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

297

298

299 user:=

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

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

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

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

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

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

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

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

308

309 変換表

310 /user/email=m

311

312 blog:=

313 シリアル:id:p:serial

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

315 所有者:owner:s:owner:

316 時刻:ctime:s:stamp:

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

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

319

320 article:=

321 シリアル:id:p:serial

322 筆者:author:s:owner

323 時刻:ctime:s:stamp:

324 参照元:parent:s:parent:

325 パス:path:s:path:

326 本文:text:s:textarea:

327 画像:image:m:image:

328

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

330

331

332 EOF

333

334 logstart() {

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

336 }

337 logend() {

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

339 }

340 sqlog() {

341 logstart

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

343 cat >> $querylog

344 else

345 echo "$*" >> $querylog

346 fi

347 logend

348 }

349 sq() {

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

351 logstart

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

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

354 else

355 echo "$@" >> $querylog

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

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

358 fi

359 logend

360 }

361 dbsetup() {

362 pipedir=$tmpdir/pipedir

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

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

365 suf=`date +%s`

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

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

368 mkfifo $sqi $sqo

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

370 sq $db < $sqi &

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

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

373 exec 2>> $tmpdir/error.out

374 fi

375 exec 3>> $tmpdir/debug.out

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

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

378 rm $sqi

379 # Attach supplemental DB

380 cat >&5 <<-EOF

381 .output /dev/null

382 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

386 .output stdout

387 EOF

388 }

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

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

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

392 fi

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

394 }

395 cleanup() {

396 trap '' INT HUP EXIT TERM PIPE

397 echo .quit >&5

398 kill $sq3pid

399 kill $sq3pid

400 rm -f $sqo $sqi

401 rm -rf $tmpfiles

402 cleanup2

403 }

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

405 # simple redirection for the sake of speed.

406 query() {

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

408 echo ".output $sqo" >&5

409 logstart

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

411 tee -a $querylog

412 else

413 echo "$@" >> $querylog

414 echo "$@"

415 fi >&5

416 echo ".output stdout" >&5

417 cat $sqo

418 rc=$?

419 logend

420 return $rc

421 }

422 _m4() {

423 #S4NAME=f,f,f

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

425 }

426 ismember() {

427 # $1=user, $2=group

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

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

430 }

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

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

433 }

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

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

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

437 }

438 isgrpowner() (

439 # $1=user, $2=group

440 gn=`sqlquote "$2"`

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

442 err isgrpowner: $sql

443 test -n "`query $sql`"

444 )

445 isgrpownerbygid() (

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

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

448 err isgrpownerbygid: $sql

449 test -n "`query $sql`"

450 )

451 getgroupadminmails() {

452 # $1=group

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

454 email4group "$1" "$i" ;

455 done

456 }

457 getgroupadmins() { # $1=group

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

459 qgrp=`sqlquote "$1"`

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

461 }

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

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

464 getvalbyid grp $2 \

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

466 }

467 getgroupbyid() {

468 # $1=id|gname

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

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

471 # err ggbyid: `echo $sql`

472 query $sql

473 }

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

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

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

477

478 # Return true when anonymous mode

479 [ "$anonymousmode" ] && return 0

480 # case `getvalbyid blog mode $2` in

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

482 # *closed*)

483 # owner=`getvalbyid blog owner $2`

484 # if isgrp $owner; then

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

486 # elif isuser $owner; then

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

488 # fi

489 # esac

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

491 sql="with getblog as (

492 select key,val from blog_s where id=(

493 select blogid from article where id in

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

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

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

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

498 isgrpadm as (select user from grp_adm where

499 gname=(select val from getowner) and

500 user='$1'),

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

502 select case

503 when (select author from article where

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

505 then 'author'

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

507 then 'open'

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

509 then CASE

510 WHEN (SELECT val FROM isgrp) IS NULL

511 THEN

512 CASE WHEN (SELECT val from getowner)

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

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

515 ELSE ''

516 END

517 WHEN (select user from isgrpadm) IS NOT NULL

518 THEN 'i-am-admin'

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

520 END

521 when (select val from getmode) is null

522 then 'open'

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

524 then (SELECT user FROM isgrpadm)

525 when (select author from article where

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

527 then 'user+author'

528 else '' end;"

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

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

531 # result=`query "$sql"`

532 # err FileAccessibility=$result

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

534 }

535 linkhome() {

536 # $1=UserOrGroup

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

538 if isuser $1; then

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

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

541 else

542 echo -n "grp+$1"

543 fi

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

545 }

546 hreflink() {

547 # s4 specific notation:

548 # ^href=URL

549 # ^iframe=URL

550 # ^video=URL

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

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

553 # OSM umap Wikistyle Notation:

554 # [[URL]] - Simle Link

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

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

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

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

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

560 # Other Style

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

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

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

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

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

566 # SPC+SPC+$ - <br>

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

568 checkboxON="${cb} checked>"

569 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

594

595 }

596 minitbl() {

597 sed -n '

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

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

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

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

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

603 H; # Concat this line to HoldSpace

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

605 $ b cont

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

607 }

608 :cont

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

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

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

612 # . of ^. is workaround for FreeBSD sed

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

614 p; # Print whole "table" element

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

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

617 }

618 x; # Back to the newest line

619 p; # Print rest' | miniul

620 }

621 miniul() {

622 sed -e '

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

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

625 :top

626 s/\n//;

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

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

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

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

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

632 $ b cont

633 N; # 次の行を読む

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

635 /^ /b top

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

637 p; s/.*//;

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

639 /^\* /b top

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

641 }

642

643 :cont

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

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

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

647 H; x

648 }

649 x'

650 }

651 acclog() (

652 # $1=table, $2=rowid

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

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

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

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

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

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

659 fi

660 )

661 gecos() (

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

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

664 )

665 setpar() {

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

667 case "$1" in

668 user) _user="$v" ;;

669 skey) _skey="$v" ;;

670 esac

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

672 }

673 replpar() {

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

675 }

676 getpar() {

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

678 val=""

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

680 user) val=$_user ;;

681 skey) val=$_skey ;;

682 esac

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

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

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

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

687 fi

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

689 case "$var" in

690 owner)

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

692 echo $user; return

693 elif ismember $user $val; then

694 echo $val; return

695 fi ;;

696 esac

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

698 echo "$val"

699 }

700 setskey() {

701 # For quick response...(?)

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

703 }

704 chkskey() {

705 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

711 return 0

712 fi

713 return 1

714 }

715 resetskey() {

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

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

718 fi

719 }

720 getpartype() {

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

722 }

723 getparcount() {

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

725 }

726 getparfilename() {

727 # null if type of $1 is not file

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

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

730 }

731 sqlquote() {

732 (v="$1"

733 case "$v" in

734 "") return ;; # null

735 "X'"*) # quoted hex string

736 echo $1 ;;

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

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

739 echo "\"$v\""

740 return ;;

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

742 echo "\"$v\""

743 return ;;

744 *)

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

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

747 else

748 echo "\"$v\""

749 fi ;;

750 esac)

751 }

752 sqlquotestr() (

753 case "$1" in

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

755 echo "'$v'" ;;

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

757 esac

758 )

759 mktempd() {

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

761 }

762 getcachedir() { # $1=maintable

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

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

765 else

766 echo $tmpd/$thumbxy

767 fi

768 }

769 getval() {

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

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

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

773 echo "$user" ;;

774 stamp|datetime)

775 date "+%F %T" ;;

776 serial)

777 (s=`getpar $2`

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

779 *)

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

781 esac

782 }

783

784 getvalquote() {

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

786 (v=`getval "$@"`

787 case "$v" in

788 "") echo NULL ;;

789 *) sqlquote "$v" ;;

790 esac)

791 }

792 getparquote() {

793 sqlquote "`getpar $1`"

794 }

795 getbinbyid() {

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

797

798 }

799 getvalbyid() {

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

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

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

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

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

805

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

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

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

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

810 return

811 fi

812 done

813 rowid=$3

814 pk=`gettblpkey $1`

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

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

817 td=${4:-$tmpd}

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

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

820 for kt in s m; do

821 t=${1}_$kt

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

823 vcount=1 # count(val)

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

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

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

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

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

829 if [ $kt = m ]; then

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

831 # Reset val to store filenames if type is string

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

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

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

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

836 i=0

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

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

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

840 i=$((i+1))

841 fn=$c.$i

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

843 case $type in

844 file:*)

845 #file=$td/$val

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

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

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

849 # FOR SPEED: Skip file generation if imgcache exists

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

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

852 && continue

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

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

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

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

857 select rowid from $cond $slice;

858 .output '$td/$fn'

859 select val from $cond $slice;

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

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

862 .output stdout

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

864 EOF

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

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

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

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

869 ls -lh "$file" |

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

871 case "$type" in

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

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

874 *|*:[Aa]pplication*)

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

876 png:- > "$file"

877 ;;

878 esac

879 ;;

880 *)

881 sq $db<<EOF

882 .output $td/$fn.rowid

883 select rowid from $cond $slice;

884 .output $td/$fn

885 select val from $cond $slice;

886 EOF

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

888 ;;

889 esac

890 done

891 else

892 rm -f $td/$c.count

893 case $type in

894 file:*)

895 echo "$val" \

896 | while read fn; do

897 file=$td/$fn

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

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

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

901 | unhexize > "$file"

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

903 case $type in

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

905 *:[Aa]pplication*)

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

907 png:- > $file ;;

908 esac

909 fi

910 done

911 ;;

912 esac

913 fi

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

915 return

916 fi

917 done

918 done)

919 }

920 getvalbypkey() (

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

922 pk=`gettblpkey $1`

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

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

925 )

926 getvalbycond() {

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

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

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

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

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

932 fi

933 }

934 getpwfield() {

935 # getpwfield user column

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

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

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

939 echo "$val"

940 return 0

941 else

942 return 1

943 fi

944 }

945 numericalize() {

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

947 }

948 encode() {

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

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

951 sha1=sha1

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

953 sha1=sha1sum

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

955 sha1=gsha1sum

956 fi

957 fi

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

959 }

960 enjpeg() {

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

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

963 cjpeg="cjpeg"

964 else

965 cjpeg="convert - jpeg:-"

966 fi

967 fi

968 $cjpeg "$@"

969 }

970 mycrypt() (

971 key=$1 salt=$2

972 # err \$2=$2

973 case $2 in

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

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

976 esac

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

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

979 )

980 hexize() {

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

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

983 hexize="xxd -p"

984 else

985 hexize_hd() {

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

987 }

988 hexize="hexize_hd"

989 fi

990 fi

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

992 }

993 unhexize() {

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

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

996 unhex="xxd -p -r"

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

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

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

1000 EOF

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

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

1003 # world writable...:)

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

1005 fi

1006 fi

1007 cat "$@" | $unhex

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

1009 }

1010 percenthex() {

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

1012 }

1013 htmlescape() {

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

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

1016 }

1017 enascii() {

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

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

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

1021 else

1022 enascii_now=`date +%FT%T`

1023 enascii_sed() {

1024 nkf -Z0Z1Z2 \

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

1026 }

1027 enascii="enascii_sed"

1028 fi

1029 fi

1030 cat "$@" | $enascii

1031 }

1032 size_h() {

1033 i="$1" oi=$1

1034 set -- B B KB MB GB TB

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

1036 oi=$i

1037 i=$((i/1024))

1038 shift

1039 done

1040 echo ${oi}$1

1041 }

1042 gettblconf() {

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

1044 ## tconfs=`sq $db \

1045 tconfs=`query \

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

1047 fi

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

1049 }

1050 gettblkeys() {

1051 # $1=tbl

1052 gettblconf

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

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

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

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

1057 case $type in

1058 *p*)

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

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

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

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

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

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

1065 esac

1066 case $type in

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

1068 esac

1069 done

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

1071 }

1072 gettblpkey() {

1073 # $1=tbl

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

1075 }

1076 gettblfkey() {

1077 (x=`gettblkeys $1`

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

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

1080 }

1081 gettblcols() {

1082 (x=`gettblkeys $1`

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

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

1085 }

1086 gettbl_s_cols() {

1087 (x=`gettblkeys $1`

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

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

1090 }

1091 gettbl_m_cols() {

1092 (x=`gettblkeys $1`

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

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

1095 }

1096 gettbl_h_cols() {

1097 (x=`gettblkeys $1`

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

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

1100 }

1101 gettbl_coltype() (

1102 gettblconf

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

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

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

1106 )

1107 is_hidden() {

1108 # $1=Tbl $2=col

1109 gettblconf

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

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

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

1113 case $x in

1114 *h*) return 0 ;;

1115 *) return 1 ;;

1116 esac

1117 }

1118

1119 dbsetbyid() {

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

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

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

1123 quotedp=$(sqlquotestr "$p")

1124 unset primary update

1125 gettblconf

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

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

1128 #err conf=$conf

1129 case ${conf#*=} in

1130 p*) primary=1 ;;

1131 f*) update=1 ;;

1132 u*) ;;

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

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

1135 esac

1136 #err t=$t

1137 type=string fn=""

1138 case $conf in

1139 */password)

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

1141 ;;

1142 */image*|*/document*)

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

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

1145 ;;

1146 esac

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

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

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

1150 if [ "$primary" ]; then

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

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

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

1154 elif [ "$update" ]; then

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

1156 else

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

1158 fi

1159 )

1160 }

1161 expire() (

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

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

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

1165 )

1166 addsession() {

1167 # expireをセット

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

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

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

1171 # Remove old session parameters

1172 now=`expire now`

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

1174 }

1175 gencookie() (

1176 path=${URL#*:/}

1177 path=${URL%/*}

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

1179 for kv; do

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

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

1182 done

1183 )

1184 contenttype() {

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

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

1187 }

1188 putheader() {

1189

1190 }

1191 putfooter() {

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

1193 }

1194 getcookie() {

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

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

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

1198 case "$k" in

1199 user) _user="$v" ;;

1200 skey) _skey="$v" ;;

1201 esac

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

1203 done

1204 }

1205 genrandom() {

1206 # $1=columns (default: 10)

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

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

1209 }

1210 genserial() {

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

1212 }

1213 smail() {

1214 # smail rcpts subj (file)

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

1216 # $MAIL_FROM <- From: header value

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

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

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

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

1221 sender=${SENDER:-$admin}

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

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

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

1225 }

1226 smail_queue_flush() {

1227 # $1=timelimit

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

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

1230 SELECT rowid FROM smailq

1231 GROUP BY rcpts, subj

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

1233 EOF

1234 )

1235 for rid in $rowids; do

1236 sq $workdb \

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

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

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

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

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

1242

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

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

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

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

1247 SELECT time, text FROM smailq

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

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

1250 ORDER by time;

1251 EOF

1252 then

1253 echo rowid=$rid

1254 cat <<-EOF | sq $workdb

1255 DELETE FROM smailq

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

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

1258 EOF

1259 fi

1260 fi

1261 done

1262 }

1263 smail_queue() {

1264 # $1=Rcpts, $@=subj

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

1266 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1275 EOF

1276 )

1277 ## XXX: Adhoc load mitigation

1278 case "$now" in

1279 *[01])

1280 err flush_queue=$mintime

1281 smail_queue_flush ;;

1282 esac

1283 fi

1284 }

1285 setviastring() {

1286 table=par

1287 oifs="$IFS"

1288 IFS="&"

1289 for us in $1; do

1290 k=${us%%=*}

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

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

1293 done

1294 IFS="$oifs"

1295 }

1296 checkdomain() (

1297 # Check the validity of domain by referring DNS

1298 item=$1

1299 err checkdomain $1

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

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

1302 )

1303 pwcheck() {

1304 # $1=passwd

1305 dbpswd=`getpwfield $user pswd`

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

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

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

1309 }

1310 mypwhash() {

1311 mycrypt "`cat`" `genrandom 5`

1312 }

1313 wasureta() {

1314 user=$1

1315 if ! checkdomain $user; then

1316 contenttype; echo

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

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

1319 putfooter

1320 exit 0

1321 fi

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

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

1324 encpswd=`echo $newpswd|mypwhash`

1325 dbsetbyid user $user pswd "$encpswd"

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

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

1328 $msgdir/mail-newaccount.m4 \

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

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

1331 }

1332 checkauth() {

1333 user=`getpar user`

1334 skc=`getpar skey` # from cookie

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

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

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

1338 if chkskey "$skc"; then

1339 return 0

1340 fi

1341 fi

1342 pswd=`getpar pswd`

1343 quser=`sqlquotestr "$user"`

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

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

1346 return 4 # 4=server too heavy

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

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

1349 return 2 # 2=login fail

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

1351 wasureta "$user"

1352 return 1 # wasureta error

1353 fi

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

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

1356 if pwcheck "$pswd"; then

1357 newsession=`genrandom 34`

1358 if setskey "$user" "$newsession" &&

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

1360 gencookie "user=$user" "skey=$newsession"

1361 return 0

1362 else

1363 return 4 # Heavy load??

1364 fi

1365 fi

1366 err "Login failed: [$user]"

1367 return 2 # Password mismatch

1368 }

1369 showlogin() {

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

1371 test -z "$args" && resetskey

1372 s4name=${S4NAME:-s4}

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

1374 cat $layout/login.m4.html

1375 echo '</body></html>'

1376 ) | _m4 \

1377 -D_BODYCLASS_="login" \

1378 -D_TITLE_="$s4name" \

1379 -D_SYSNAME_="Welcome to $s4name" \

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

1381 exit 0

1382 }

1383 dologin() {

1384 checkauth

1385 st=$?

1386 if [ $st != 0 ]; then

1387 contenttype; echo

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

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

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

1391 showlogin # and EXIT

1392 fi

1393 }

1394

1395 # Do instant jobs here

1396 dbsetup

1397 trap cleanup INT HUP EXIT TERM PIPE

1398 # trap cleanup INT HUP

1399

1400 err() {

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

1402 }

1403

1404 cgiinit() {

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

1406 tmpd=`tmpd=$tmpdir mktempd`

1407 tmpf=$tmpd/stream.$$

1408 tmpfiles=$tmpfiles" $tmpd"

1409 addsession $session

1410 getcookie

1411 case "$REQUEST_METHOD" in

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

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

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

1415 # cat > $tmpf # too much?

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

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

1418 s="`cat $tmpf`"

1419 ;;

1420 esac

1421 case "$CONTENT_TYPE" in

1422 *boundary*)

1423 bndry=${CONTENT_TYPE#*boundary=}

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

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

1426 do

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

1428 #echo u=$us

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

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

1431 # err k=$k v=$v

1432 case "$k" in

1433 *:filename)

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

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

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

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

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

1439 :

1440 fi

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

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

1443 case "$mimetype" in

1444 [Ii]mage/x-xcf)

1445 bzip2 "$tmpd/$v"

1446 v=${v}.bz2

1447 ;;

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

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

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

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

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

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

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

1455 v=$vjpg

1456 else

1457 mimetype="Not supported"

1458 fi

1459 ;;

1460 [Ii]mage/*)

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

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

1463 ;;

1464 esac

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

1466 then

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

1468 continue

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

1470 replpar text string \

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

1472 continue

1473 fi

1474 ;;

1475 *)

1476 type='string'

1477 ;;

1478 esac

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

1480 setpar "$k" "$type" "$v"

1481 done

1482 ;;

1483 *)

1484 setviastring "$s"

1485 ;;

1486 esac

1487 }

1488 email4group() {

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

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

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

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

1493 sql="WITH

1494 grpemails AS (

1495 SELECT gname, user, val email

1496 FROM grp_mem NATURAL JOIN grp_mem_s

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

1498 useremails AS (

1499 SELECT user.name, val email

1500 FROM user

1501 LEFT JOIN user_m

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

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

1504 FROM useremails u LEFT JOIN grpemails g

1505 ON u.name=g.user

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

1507 query "$sql"

1508 }

1509 email4groupbyuid() {

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

1511 g=$1; shift

1512 uids=`echo "$@"`

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

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

1515 email4group "$g" `query "$sql"`

1516 }

1517 myemail4group() {

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

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

1520 }

1521 collectmembersbyid() {

1522 # Collect user names of group specified by grid

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

1524 query "SELECT user FROM grp_mem \

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

1526 }

1527 collectmembersbyid() {

1528 # Collect user names of group name

1529 qgrp=`sqlquote "$1"`

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

1531 }

1532 collectgecosesbyid() {

1533 # Collect user gecoses of group

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

1535 query<<-EOF

1536 SELECT gecos

1537 FROM gecoses

1538 WHERE name IN (SELECT user FROM grp_mem

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

1540 EOF

1541 }

1542 collectemail() (

1543 # Collect email addresses for group $1

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

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

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

1547 for e; do

1548 if isuser "$e"; then

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

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

1551 else

1552 qgrp=`sqlquote "$e"`

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

1554 gmem="grp_mem"

1555 else

1556 tm=`sqlquote "$TEAM"`

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

1558 fi

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

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

1561 $gmem g left join grp_mem_s s

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

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

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

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

1566 query "$sql"

1567 fi

1568 done

1569 )

1570 sendinvitation() (

1571 # $1=email

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

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

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

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

1576 gecos=`gecos`

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

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

1579 _m4 -D_URL_="$urlbase" \

1580 -D_USER_="$name" \

1581 -D_EMAIL_="$1" \

1582 -D_REGIST_="$regist" \

1583 -D_ADMIN_="$admin" \

1584 $msgdir/mail-invite.m4 \

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

1586 return 0

1587 )

1588 emaildomaincheck() {

1589 case "$1" in

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

1591 *@*)

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

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

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

1595 return 2

1596 fi

1597 return 0

1598 ;;

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

1600 esac

1601 }

1602 invite() {

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

1604 case "$email" in

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

1606 *@*)

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

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

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

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

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

1612 elif sendinvitation $email; then

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

1614 else # Cannot be reached here

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

1616 fi ;;

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

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

1619 esac

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

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

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

1623 fi

1624 if [ -f $invite_policy ]; then

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

1626 else

1627 pol="$invite_policy"

1628 fi

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

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

1631 -D_INVITE_POLICY_="$pol" \

1632 $layout/html.m4.html $layout/invite.m4.html

1633 }

1634 regist() {

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

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

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

1638 echo "bye bye" | html p

1639 reutrn

1640 fi

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

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

1643 cat<<EOF

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

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

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

1647 EOF

1648 return

1649 fi

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

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

1652 # Fake login password to wasureta

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

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

1655 wasureta $email

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

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

1658 addsession $1 # for removal after 1 minute

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

1660 $layout/login.m4.html

1661 return

1662 }

1663 group_safename() {

1664 # Convert $1 to safe group name

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

1666 }

1667 groupupdate() {

1668 gname=`getpar gname`

1669 qgname=`sqlquote "$gname"`

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

1671 # See ALSO same job in showgroup()

1672 newgname=`group_safename "$gname"`

1673 err newgname=$newgname

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

1675 err NewGNAME: gname=$newgname

1676 gname=$newgname

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

1678 replpar gname string "$gname"

1679 fi

1680 # Name confliction check

1681 parow=`getpar rowid`

1682 ## err parow=$parow

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

1684 query "BEGIN EXCLUSIVE;"

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

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

1687 if [ $count -gt 0 ]; then

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

1689 query "END;"

1690 return

1691 fi

1692 par2table $formdir/grp.def

1693 query "END TRANSACTION;"

1694 # Remove orphan

1695 : <<EOF

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

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

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

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

1700 EOF

1701 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1705 sql="delete from blog where id in

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

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

1708 query "$sql";

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

1710 return # and return

1711 fi

1712 fi

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

1714 fi

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

1716 grid=$(query $sql)

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

1718 grp $grid

1719 }

1720 groupclone() {

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

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

1723 case "$1" in

1724 */noteam)

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

1726 esac

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

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

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

1730 return

1731 fi

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

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

1734 return

1735 fi

1736 i=0

1737 while true; do

1738 copy="-copy$i"

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

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

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

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

1743 i=$((i++))

1744 continue

1745 fi

1746 break

1747 done

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

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

1750 query<<-EOF

1751 BEGIN;

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

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

1754 SELECT $newqname, key, val

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

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

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

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

1759 -- Copy members and their configuration --

1760 REPLACE INTO grp_mem SELECT $newqname, user

1761 FROM grp_mem WHERE gname=$qgrp;

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

1763 FROM grp_mem_s WHERE gname=$qgrp;

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

1765 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1766 -- Copy administrators --

1767 REPLACE INTO grp_adm SELECT $newqname, user

1768 FROM grp_adm WHERE gname=$qgrp;

1769 COMMIT;

1770 EOF

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

1772 STOPCLONEMSG=1 groupconf "$newrowid"

1773 }

1774 groupman() {

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

1776

1777 GF_STAGE="grpconf"

1778 GF_STAGE=groupupdate

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

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

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

1782 -D_DUMPTABLE_="syscmd(cat)" \

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

1784 }

1785 userconf() {

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

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

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

1789 }

1790 groupconf() {

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

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

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

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

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

1796

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

1798 grp=`getgroupbyid $rowid`

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

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

1801 return

1802 fi

1803

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

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

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

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

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

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

1810 <div>

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

1812 <table>

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

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

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

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

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

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

1819 </table>

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

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

1822 </div>

1823 EOF

1824 fi

1825 }

1826 mems() {

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

1828 kwd=`getpar kwd`

1829 listmember $kwd

1830 }

1831 grps() {

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

1833 kwd=`getpar kwd`

1834 listgroup $kwd \

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

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

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

1838 $layout/form+dump.m4.html

1839 }

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

1841 gpg=`getpar grp`

1842 grid=${1:-$gpg}

1843 grp=`getgroupbyid "$grid"`

1844 ## . ./s4-blog.sh

1845 jg=`getpar joingrp`

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

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

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

1849 fi

1850 htmlheader=$layout/html.m4.html

1851 showgroup "$grid"

1852 }

1853 sql4interestblogs() {

1854 cat<<EOF

1855 CREATE TEMPORARY VIEW interestblogs AS

1856 SELECT blog.rowid rid, id, author

1857 FROM blog

1858 NATURAL JOIN

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

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

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

1862 WHEN (SELECT user FROM grp_mem

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

1864 THEN 0

1865 ELSE 1

1866 END;

1867 EOF

1868 }

1869 listnewblogsql() { # $1=user

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

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

1872 newdays=${newdays:-14}

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

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

1875 WHERE user='$user'

1876 AND tblrowid IN

1877 ($blogreadflagrowid,

1878 $blogcutoffflagrowid)),

1879 $basetime -- "0"

1880 );"`

1881 cat<<EOF

1882 `sql4interestblogs`

1883 WITH article_ctime as (

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

1885 FROM article join article_s s using(id)

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

1887 GROUP BY id

1888 ), blog_title_owner as (

1889 SELECT blg.rid brid, id,

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

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

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

1893 ), blogall as (

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

1895 ), news as (

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

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

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

1899 FROM blogall bl

1900 LEFT JOIN

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

1902 ON bl.brid=al.tblrowid

1903 WHERE atime < bl.ctime

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

1905 LIMIT 10

1906 ) SELECT brid LINK, "新着",

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

1908 ctime, title,

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

1910 FROM news;

1911 EOF

1912 }

1913

1914 search_form() {

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

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

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

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

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

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

1921 @week → 最近一週間

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

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

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

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

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

1927 auth=""

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

1929 case "$1" in

1930 author=*)

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

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

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

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

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

1936 ;;

1937 grp=*)

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

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

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

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

1942 ;;

1943 esac

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

1945 cat<<-EOF

1946 <div class="right">

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

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

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

1950

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

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

1953

1954 </form>

1955 </div>

1956 EOF

1957 }

1958

1959 imgsrc_cache() (

1960 # $1 = directory for cache'ing

1961 # $2 = table (user_m or grp_m)

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

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

1964 dir="$1" tbl="$2"

1965 keycond="$3"

1966 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

1967 ORDER BY rowid DESC LIMIT 1"

1968 [ -d "$dir" ] || mkdir -p "$dir"

1969 tmpf=$tmpd/imgsrc_cache.$$

1970 case "$4" in

1971 [Ss]) size=S ;;

1972 [Oo]) size=O ;;

1973 *) size=M ;;

1974 esac

1975 # ImageCache filename storing schema:

1976 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

1977 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

1978 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

1979 valtype=`query "$sql0"`

1980 filename=${valtype%%//*}

1981 filetype=${valtype##*//file:}

1982 if [ x"$filename" = x"${filename%.*}" ]; then

1983 # If nor filename extension found, set it to image type

1984 case "$filetype" in

1985 image/*) filename=$filename.${filetype#image/} ;;

1986 esac

1987 fi

1988 cacheimg_S=$dir/S_$filename

1989 cacheimg_M=$dir/M_$filename

1990 cacheimg_O=$dir/$filename

1991 cacheimg=$dir/${size}_$filename

1992 sumfile="$dir/$filename.sum"

1993 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

1994 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

1995 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

1996 # if cache is fresh and has the same checksum,

1997 echo "<img src=\"$cacheimg\">"

1998 else

1999 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2000 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2001 fifo2=$fifo.2

2002 mkfifo $fifo $fifo2

2003 fmt=${filename##*.}

2004 ## [[ NOTE ]]

2005 ## a. convert oldimage newimage

2006 ## b. convert oldimage fmt:- | convert - newimage

2007 ## b is much smaller than a

2008 cat $tmpf | unhexize \

2009 | tee $fifo \

2010 | convert -define ${fmt}:size=${iconxy_M} \

2011 -resize ${iconxy_M}'>' - ${fmt}:- \

2012 | tee $fifo2 \

2013 | convert - "$cacheimg_M" &

2014 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2015 -resize ${iconxy_S}'>' - ${fmt}:- \

2016 | convert - "$cacheimg_S" &

2017 printf '%s' "<img src=\"data:${filetype},"

2018 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2019 echo '">'

2020 echo "$sum" > $sumfile

2021 fi

2022 ## Now preparing cache image, done.

2023 ## Store this information to DB

2024 stbl=${tbl%_m}_s # user_s or grp_s

2025 pkey=${keycond%%=*} # Primary Key name

2026 pval=${keycond#*=} # Primary Key value

2027 query <<-EOF

2028 REPLACE INTO $stbl($pkey, key, type, val)

2029 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2030 EOF

2031 )

2032

2033 showhome() {

2034 # $1=userRowIdToShow

2035 err showhome \$1=$1

2036 case "$1" in

2037 *@*) uname=`getvalbypkey user name "$1"` ;;

2038 *) uname=`getvalbyid user name $1` ;;

2039 esac

2040 ## err ShowHome: uname=$uname

2041 td=`getcachedir home/"$1"`

2042 gecos=`gecos "$uname"`

2043 ## err SH:gecos=$gecos

2044 GF_VIEWONLY=1

2045 cond="gname in (select gname from grp_mem where user='$uname')"

2046 search_form_args=""

2047 if [ x"$user" = x"$uname" ]; then

2048 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2049 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> /

2050 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2051 # Display folders

2052 sql="select count(id) from article_m where id

2053 in (select id from article where author='$user')

2054 and type like 'file:%';"

2055 ## err nfile-sql=`echo "$sql"`

2056 nfile=`query "$sql"`

2057 # err nfile=$nfile

2058 if [ $nfile -gt 0 ]; then

2059 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2060 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2061 fi

2062 else

2063 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2064 GROUP BY user;"`

2065 usermenu="<p>Last seen on $latestlog</p>"

2066 search_form_args="author=$uname"

2067 fi

2068 . ./s4-blog.sh

2069

2070 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2071 search_form "$search_form_args" > $sf

2072 printf "%s さん" "$gecos" > $tf

2073 { echo "<div class=\"noprofimg\">"

2074 viewtable $formdir/user.def user $1

2075 echo "</div>"

2076 } > $pf

2077

2078 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2079 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2080 imf=$tmpd/profimg.$$; touch $imf

2081 if [ -n "$img" ]; then

2082 if true; then

2083 tbl=user_m

2084 enticond="name='$uname'"

2085 imgsrc_cache "$td/main" user_m "$enticond" M

2086 else

2087 { printf '%s' "<IMG src=\"data:${img#file:},"

2088 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2089 | sed 's/$..$/%\1/g'

2090 echo '">'

2091 }

2092 fi > $imf

2093 fi

2094 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2095 val='$uname';"`

2096 ## REMOVE This comment block until 2019/7/1

2097 ## err "----- `gdate +%FT%T.%3N` ------------C"

2098 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2099 listblog $uname > $bf

2100 ## unset ddd

2101 ## err "----- `gdate +%FT%T.%3N` ------------D"

2102

2103 hometail=$tmpd/tail.$$

2104 mkfifo $hometail

2105

2106 #Calling listgroupbytable, originally here

2107

2108 (

2109 # Display Most Recent Entry

2110 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2111 shortval=${shortval:-val}

2112

2113 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2114 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2115 ctime,

2116 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2117 (SELECT gecos FROM gecoses

2118 WHERE name=(SELECT val FROM blog_s

2119 WHERE key='owner' AND id=b.id)) owner,

2120 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2121 FROM blog b

2122 JOIN

2123 (SELECT distinct blogid, a.id aid, max(val) ctime

2124 FROM article a, article_s s

2125 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2126 GROUP BY blogid ORDER BY val DESC LIMIT 50

2127 ) m

2128 ON b.id=m.blogid;"

2129 # This should be as follows

2130 : <<EOF

2131 WITH arts AS(

2132 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2133 a.blogid, a.id id, s.val ctime

2134 FROM article a NATURAL JOIN article_s s

2135 WHERE s.key = 'ctime' AND a.author='$user'

2136 GROUP by s.id

2137 )

2138 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2139 FROM arts a0

2140 JOIN

2141 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2142 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2143 ORDER BY ctime DESC LIMIT 50;

2144 EOF

2145

2146 cat<<-EOF

2147 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2148 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2149 <div class="lcto">

2150 `DT_VIEW=replyblog dumptable html blog`

2151 </div>

2152 EOF

2153 unset DT_SQL

2154 if [ x"$user" = x"$uname" ]; then

2155 # Display NEWS

2156 # 2016-06-26

2157 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2158 acclog blog $blogreadflagrowid

2159 # echo "全部既読にしました" | html p

2160 fi

2161 # 2016-02-19 Counting NEWS without using dumptable.

2162 sql=`listnewblogsql "$user"`

2163 # echo "$sql" > tmp/listnew

2164 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2165 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2166 cont=$((cont-1))

2167 err newcount=$cont

2168 if [ $cont -gt 0 ]; then

2169 #echo "全体の新着記事${cont}傑" | html h2

2170 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2171 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2172 cat<<-EOF | html form 'action="?home"'

2173 `cgi_checkbox readchk yes 'id="read"'`<label

2174 for="read">新着ふくめて全部読んだことにする</label>

2175 `cgi_submit '確定'`

2176 `cgi_hidden read yes`

2177 EOF

2178 echo "$new10 "

2179 echo "</div>"

2180 else # If news is 0, set log cut off flag

2181 acclog blog $blogcutoffflagrowid # for speed

2182 fi

2183 else # Not My Home ($user != $uname)

2184 : # DT_SQL=

2185 fi

2186 ) > $hometail & # Is background call safe to m4??

2187 #

2188 listgroupbytable $formdir/grp.def $cond |

2189 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2190 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2191 -D_PROFIMG_="spaste(\`$imf')" \

2192 -D_BLOGS_="spaste(\`$bf')" \

2193 -D_SEARCH_="spaste(\`$sf')" \

2194 -D_NBLOG_="$nblog" \

2195 -D_GROUPS_="syscmd(\`cat')" \

2196 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2197 $layout/html.m4.html $layout/home.m4.html

2198

2199 # Record access log

2200 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2201 }

2202 commission() { # $1=grp-rowid $2=user-rowid

2203 contenttype; echo

2204 ## err commission: "$@"

2205 gname=`getgroupbyid $1`

2206 echo "グループ $gname 管理者委任" \

2207 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2208 if [ -n "$2" ]; then

2209 grp_reg_adm "$@"

2210 else

2211 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2212 fi

2213 }

2214 listgroupbytable() {

2215 # $1=deffile $2...=condition

2216 tagline=`grep :tag: $1`; shift

2217 and="${1:+and }" where=${1:+where }

2218 href="<a href=\"$myname?grp+"

2219 echo '<div class="listgroup">'

2220 NGsql="select distinct tag from\

2221 (select gname, max(case key when 'tag' then val end) as tag, \

2222 max(case key when 'ctime' then val end) as ctime\

2223 from grp_s group by gname order by ctime);"

2224 sql="select val from grp_s where key='tag' $and$* group by val;"

2225 ## err ListGRP: query sql="$sql"

2226 for tag in `query "$sql"`

2227 do

2228 ## err ListGrp: tag=$tag

2229 tn=${tagline%%=${tag}*}

2230 tn=${tn##*[ :]}

2231 sql="select rowid||':'||gname as 'グループ名',説明 from

2232 (select (select rowid from grp g where g.gname=grp_s.gname)

2233 as rowid,

2234 gname,

2235 max(case key when 'gecos' then val end) as '説明',

2236 max(case key when 'tag' then val end) as 'tag',

2237 max(case key when 'mtime' then val end) as mtime from grp_s

2238 $where$* group by gname having tag='$tag' order by mtime desc);"

2239 ## err PersonalGroupList= `echo $sql`

2240 echo "<h2>$tn</h2>"

2241 echo '<table class="b listgroup">'

2242 sq -header -html $db "$sql" \

2243 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2244 echo '</table>'

2245 done

2246 echo '</div>'

2247 }

2248 iconhref() (

2249 # $1=icon-file, $2=Href $3=title $4...=anchor

2250 data=`percenthex "$1"`

2251 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2252 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2253 href=$2; title=$3; shift 3

2254 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2255 )

2256 iconhref2() (

2257 # $1=icon-file, $2=Href $3=title $4...=anchor

2258 src=$1

2259 href=$2; title=$3; shift 3

2260 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2261 )

2262 listentry() (

2263 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2264 # Referring variable $iamowner=$grp to attach owner-request links

2265 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2266 cond='' hiddens=''

2267 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2268 if [ -z "$offset" ]; then

2269 offset=`getpar start`; offset=${offset%%[!0-9]*}

2270 offset=$((offset-1))

2271 fi

2272 offset=$((offset + 0)) # change to numeric forcibly

2273 [ $offset -lt 0 ] && offset=0

2274 limit=$listentlimit

2275 dir=`getcachedir "$1"`

2276 if [ x"$1" = x"user" ]; then

2277 hrb="$myname?home"

2278 deficon=person-default.png

2279 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2280 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2281 gcs=gecos

2282 else # if group

2283 hrb="$myname?grp"

2284 deficon=group-default.png

2285 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2286 gcs=name

2287 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2288 if [ -n "$tagline" ]; then

2289 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2290 ## err tagconv=$tagconv

2291 fi

2292 fi

2293 if [ ! -d $dir ]; then

2294 mkdir -p $dir

2295 fi

2296 if [ ! -s $dir/$deficon ]; then

2297 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2298 fi

2299 if [ -n "$2" ]; then

2300 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2301 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2302 fi

2303 tag=`getpar tag` tag2=`getpar tag2`

2304 if [ x"$tag" = x"NULL" ]; then

2305 tag="" tag2=""

2306 fi

2307 if [ -n "$tag$tag2" ]; then

2308 tag=${tag:-$tag2}

2309 qtag=`sqlquote "$tag"`

2310 cond2="tag=$qtag"

2311 fi

2312 if [ -n "$cond1$cond2" ]; then

2313 cond="$cond1${cond2:+ AND $cond2}"

2314 cond="WHERE ${cond# AND }"

2315 fi

2316

2317 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2318 # grpは呼出し元の動的スコープ変数でよくないな...

2319 ##qgrp=`sqlquote $grp`

2320 getgrp="(select gname from grp where rowid=${rowid:--1})"

2321 sql="select a.rowid, a.$link,

2322 coalesce(b.$gcs, a.$nm) as nick,

2323 quote(a.$nm) as qname,

2324 (SELECT val FROM ${tbl}_s

2325 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2326 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2327 || case when a.$nm in (select gname from grp)

2328 then printf('(%d名)',

2329 (select count(user) from grp_mem where gname=a.$nm))

2330 else ' <'||a.$nm||'>'

2331 end

2332 as name,

2333 b.tag,

2334 case when a.$nm in (select user from grp_adm

2335 where gname=$getgrp) then '管理者'

2336 when '$user' in (select user from grp_adm where gname=a.$nm)

2337 then 'ADMIN'

2338 when '$user' in (select user from grp_mem where gname=a.$nm)

2339 then 'Member'

2340 when '$iamowner' = '' then ''

2341 else ',not='||a.rowid end as ownerlink,

2342 CASE '$entity'

2343 WHEN 'グループ'

2344 THEN coalesce(

2345 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2346 'open')

2347 ||

2348 CASE WHEN '$user'

2349 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2350 THEN ' ismember'

2351 ELSE ''

2352 END

2353 ELSE 'user'

2354 END regmode

2355 from $tbl a left join

2356 (select $nm as name,

2357 max(case key when 'gecos' then val end) as gecos,

2358 max(case key when 'tag' then val end) as tag,

2359 max(case key when 'mtime' then val end) as mtime,

2360 max(case key when 'wtime' then val end) as wtime,

2361 max(case key when 'login' then val end) as login

2362 from ${tbl}_s group by $nm)

2363 b on a.$nm=b.name $cond $3

2364 order by b.wtime desc, b.login desc,

2365 b.mtime desc, b.tag desc, a.rowid asc"

2366 # Give precedence to newer maintained groups (2016-09-24)

2367 # Note that mtime is stored only in grp_s.

2368 ## err LE:sql.1="$sql"

2369 total=`query "with x as ($sql) select count(*) from x;"`

2370 echo "${entity} 一覧" | html h2

2371 echo '<div class="listentry">' # List-entry div

2372 # Show owner/member filter button

2373 METHOD=GET

2374 hiddens="$hiddens

2375 `cgi_hidden kwd \"$kwd\"`

2376 `cgi_hidden stage \"$stage\"`"

2377 if [ x"$tbl" = x"grp" ]; then

2378 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2379 fh="<select name=\"tag\">$nl"

2380 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2381 for l in $args; do

2382 val=${l#*=} tname=${l%=*}

2383 if [ x"$val" = x"$tag" ]; then

2384 s=" selected"

2385 selectedtags="(種別[${tname}]のみ)"

2386 else

2387 s=""

2388 fi

2389 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2390 done

2391 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2392 cat<<-EOF

2393 <form action="$myname" method="$METHOD">

2394 </form>

2395 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2396 for="ismembtn">参加中以外隠す</label>

2397 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2398 for="isadmbtn">管理者参加以外隠す</label>

2399 EOF

2400 # limit=3

2401 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2402 fi

2403 if [ $total -gt $limit ]; then

2404 echo '<div>'

2405 METHOD=GET cgi_form $stage <<EOF

2406 $form

2407 <label>次の語を含む${entity}で検索:

2408 `cgi_text kwd "$kwd"`</label>

2409 $hiddens

2410 EOF

2411 echo '</div>'

2412 else

2413 echo $selectedtags | html p

2414 fi

2415 cat<<EOF

2416 <form action="$myname" method="$METHOD">

2417 <p>${total}件中の<input class="hidesub" type="text" name="start"

2418 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2419 <input type="submit" value="確定"></p>

2420 </form>

2421 EOF

2422 if [ $((offset+limit)) -lt $total ]; then

2423 nextbtn=$(

2424 cat<<EOF

2425 <div class="right clear"><form action="$myname" method="$METHOD">

2426 `cgi_submit 次の${limit}件`

2427 $hiddens

2428 `cgi_hidden offset $((offset + limit))`</form></div>

2429 EOF

2430 )

2431 fi

2432 if [ $offset -gt 0 ]; then

2433 prevbtn=$(

2434 cat<<EOF

2435 <form action="$myname" method="$METHOD">

2436 `cgi_submit 前の${limit}件`

2437 $hiddens

2438 `cgi_hidden offset $((offset - limit))`</form>

2439 EOF

2440 )

2441 fi

2442 pnbtn="$nextbtn$prevbtn"

2443 echo $pnbtn

2444

2445 ## err ListEntry: `echo "$sql"\;`

2446 # sq $db here??? 2016-11-28

2447 query "$sql limit $limit ${offset:+offset $offset};" \

2448 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2449 # err name=$name owner=$ownerp lnk=$lnk

2450 # err newlnk=$lnk regmode=$regmode

2451 icondir=$dir/$id

2452 # Pick up only last icon

2453 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2454 <p class=\"tag _$tag\">$tag</p>" \

2455 | _m4 $tagconv

2456 if [ -n "$NOSPEEDUP" ]; then

2457 files=`getvalbyid $tbl profimg $id $icondir`

2458 if [ -n "$files" ]; then

2459 icon=`echo "$files"|tail -1`

2460 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2461 else

2462 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2463 fi

2464 elif [ -n "$icon" -a -s "$icon" ]; then

2465 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2466 else

2467 cond="$nm=$qname"

2468 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2469 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2470 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2471 # err "img=[$img]"

2472 if [ -n "$img" ]; then

2473 echo "<a href=\"$hrb+$lnk\">"

2474 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2475 echo "</a>"

2476 else

2477 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2478 fi

2479 fi

2480 echo "<br>$name${ownerp:+<br>($ownerp)}"

2481 echo "</div>"

2482 done

2483 echo "</div>" # End of List-entry div

2484 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2485 )

2486 listmember() {

2487 listentry user "$@"

2488 }

2489 listgroup() {

2490 listentry group "$@"

2491 }

2492 hexteams() { # $1=gname, $2(optional)=user

2493 cond=${2:+" AND user='$2'"}

2494 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2495 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2496 }

2497 showgroup() { # $1=group-rowid

2498 if [ -z "$1" ]; then

2499 grid=`getpar grid`

2500 grid=${grid%%[!0-9]*}

2501 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2502 else

2503 grid=$1

2504 fi

2505 grp=`getgroupbyid $grid`

2506 qgrp=`sqlquote "$grp"`

2507 htmlgrp=`echo "$grp"|htmlescape`

2508 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2509 if isgroup "$grp"; then

2510 tf=$tmpd/title.$$

2511 sf=$tmpd/search.$$

2512 bodyclass=`query "SELECT val FROM grp_s

2513 WHERE gname=$qgrp AND key='regmode';"`

2514 if ismember "$user" "$grp"; then

2515 ismember="ismember"

2516 bodyclass="$bodyclass${bodyclass:+ }ismember"

2517 else

2518 ismember="" # bodyclass="group"

2519 fi

2520 bodyclass="$bodyclass grouphome"

2521 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2522 echo "グループ $htmlgrp" > $tf

2523

2524 showgroupsub $formdir/grp.def "$grid" | \

2525 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2526 -D_FORM_="syscmd(\`cat')" \

2527 -D_BODYCLASS_="$bodyclass" \

2528 -D_DUMPTABLE_="" \

2529 $htmlheader $sf $layout/form+dump.m4.html

2530 # $htmlheader $layout/form+dump.m4.html

2531 # $htmlheader is defined in grp()

2532 else # if $grp is removed at par2table

2533 listgroup

2534 fi

2535 }

2536 showgroupsub() {

2537 # $1=def-file $2=group-rowid

2538 # Using $ismember

2539 rowid=$2

2540 grp=`getgroupbyid $2`

2541 qgrp=`sqlquote "$grp"`

2542 td=`getcachedir grp/"$2"`

2543 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2544 if [ -z "$rowid" ]; then

2545 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2546 #grp=`sq $db "select gname from grp where rowid=$grp"`

2547 echo "showgroupsub: invalid argument($1 $2)" | html p

2548 return

2549 fi

2550 val=`getvalbyid grp profimg $rowid $tmpd`

2551 enticond="gname=$qgrp"

2552 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2553 if [ -n "$img" ]; then

2554 cat<<-EOF

2555 <p class="groupimg">

2556 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2557 EOF

2558 fi

2559 echo "<div class=\"noprofimg\">"

2560 viewtable $1 grp $rowid

2561 echo "</div>"

2562 if isgrpowner "$user" "$grp"; then

2563 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2564 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2565 iamowner=$rowid

2566 colmd=" mode"

2567 fi

2568 if [ -n "$ismember" ]; then

2569 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2570 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2571 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2572 cat<<-EOF

2573 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2574 href="?blog+$rowid">グループの新規話題作成</a>

2575 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2576 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2577 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2578 <div class="fold clear">

2579 `cgi_checkbox send yes id="send"`<label

2580 for="send">グループ全員にメッセージ送信</label>

2581 <div>

2582 `cgi_textarea message "" "cols=60"`

2583 `cgi_submit 送信`

2584 `cgi_reset リセット`

2585 </div>

2586 `cgi_hidden grp $rowid`

2587 </div></form>

2588 EOF

2589 fi

2590 # 加入ボタン + 加入者リスト

2591 if [ -n "$ismember" ]; then

2592 ismem='checked' state="(参加中)"

2593 else

2594 nomem='checked' state="(現在非加入)"

2595 fi

2596 # このグループでの加入アドレス

2597 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2598 and key='email';"`

2599 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2600 ## and key='email';"

2601 ##err email=$eml

2602 cat <<EOF

2603 <div class="fold clear">

2604 `cgi_checkbox reg yes id="reg"`<label

2605 for="reg">自身の加入状態を操作する</label>$state

2606 <div>

2607 EOF

2608 cgi_form grp <<EOF

2609 <p>このグループに</p>

2610 <table class="b">

2611 <tr><th>メンバーとして</th><td>

2612 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2613 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2614 <tr><th>参加する場合のメイルアドレス<br>

2615 <small>(メインのアドレスとは違うものにする場合に記入<br>

2616 同じでよい場合は空欄に)</small></th>

2617 <td>`cgi_text email $eml`</td></tr>

2618 </table>

2619 `cgi_hidden grp $rowid`

2620 EOF

2621 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2622 echo "moderated (承認加入の)グループなので実際に参加できるのは

2623 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2624 fi

2625 echo '</div></div>'

2626 echo '<h2>話題一覧</h2>'

2627 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2628 cat<<-EOF

2629 <form class="summary" action="$myname" title="$thelp">

2630 `cgi_hidden owner "$grp"`

2631 `cgi_hidden kwd "@week"`

2632 `cgi_hidden stage searchart`

2633 `cgi_submit "一週間のまとめ"`

2634 </form>

2635 EOF

2636 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2637 colstate="state:稼動状態:frozen=rowclass=凍結"

2638 DT_CHLD=article:blogid \

2639 DT_QOWNER="$qgrp" \

2640 DT_VIEW=replyblog dumptable html blog \

2641 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2642

2643 getgname="(select gname from grp where rowid=$rowid)"

2644 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2645 cm="?commission+$rowid"

2646 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2647 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2648 # team list

2649 hexteams=`hexteams "$grp"`

2650 if [ -n "$hexteams" ]; then

2651 echo "チーム一覧" | html h2

2652 echo '<div class="dumptable"><table class="b">'

2653 sq $db -html -header<<-EOF

2654 SELECT val TEAM,

2655 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2656 MEMBERS

2657 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2658 EOF

2659 echo '</table></div>'

2660 fi

2661 }

2662 grp_getbodyclass() {

2663 # Get css class name for document.

2664 # `moderated' for moderated groups

2665 # `ismember' for groups where user belongs

2666 # $1=GroupName (w/o quote)

2667 # $user=userNameCurrentlyLogin

2668 ## err grp_getbodyclass: 1="$1"

2669 qgrp=`sqlquote "$1"`

2670 query<<-EOF

2671 SELECT coalesce(

2672 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2673 'open')

2674 ||

2675 CASE WHEN '$user'

2676 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2677 THEN ' ismember'

2678 ELSE ''

2679 END;

2680 EOF

2681 }

2682 grpaction() { # $1=group-rowid

2683 err GRP_ACTION:IN

2684 grid=${1:-`getpar grp`}

2685 grp=`getgroupbyid "$grid"`

2686 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2687 if [ -z "$grp" ]; then

2688 echo "無効な指定です。" | html p; return

2689 fi

2690 if ! ismember $user "$grp"; then

2691 echo "加入者のみに許可された操作です。" | html p; return

2692 fi

2693 echo "グループ $grp 個別選択操作" \

2694 | _m4 -D_TITLE_="syscmd(\`cat')" \

2695 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2696 $layout/html.m4.html

2697

2698 isowner=""

2699 isgrpowner "$user" "$grp" && isowner="yes"

2700 usel=`getpar usel`

2701 if [ -n "$usel" ]; then

2702 uids=$(echo `echo $usel`|tr ' ' ',')

2703 ## err grpaction-1: grp=$grp, `echo $sql`

2704 text=`getpar text|tr -d '\r'`

2705

2706 rm=`getpar rm` cfm=`getpar confirm`

2707 ## err rm=$rm cfm=$cfm

2708 if [ x"$rm" = x"yes" ]; then

2709 if [ "$isowner" ]; then

2710 if [ x"$rm$cfm" = x"yesyes" ]; then

2711 # Eliminate

2712 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2713 for tbl in grp_mem grp_mem_s grp_mem_m; do

2714 sql="delete from $tbl $cond;"

2715 # echo "sql=$sql"

2716 query "$sql"

2717 err rmGRPuser "$sql"

2718 done

2719 num=`query "select count(*) from user where rowid in ($uids);"`

2720 #err num=$num

2721 if [ 0$num -gt 0 ]; then

2722 sql="select coalesce(b.val,a.name) from user a left join \

2723 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2724 # err `echo "$sql"`

2725 html pre<<EOF

2726 以下の${num}名のグループ $grp 登録を解除しました。

2727 `query "$sql"`

2728 EOF

2729 fi

2730 else

2731 echo "確認のチェックがないのでやめておきます。" | html p

2732 return

2733 fi

2734 else # not Group Owner

2735 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2736 return

2737 fi

2738 cat<<EOF

2739

2740 EOF

2741 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2742 if [ -z "$text" ]; then # if msg is empty

2743 echo "なにかメッセージを..." | html p

2744 return 0

2745 fi

2746 gecos=`gecos $user`

2747 safegc=`echo "$gecos" | tr -d '<>@'`

2748 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2749 fromad=`myemail4group "$grp"`

2750 ###mail_from="$safegc <$fromad>"

2751 mail_from="$safegc <$user>" # TEST: 2020/5/13

2752 test -n `getpar sender` &&

2753 export SENDER=$user # TEST: 2020/5/15

2754 replyto=$fromad

2755

2756 ## Start parse of attachment files

2757 if [ -n "`getpar email`" ]; then

2758 ar=`getpar supprcpt`

2759 if [ -n "$ar" ]; then

2760 for a in $ar; do

2761 if checkdomain "$a"; then

2762 supprcpt="$supprcpt $a"

2763 else

2764 err "SupprcptErr=[$a] by $user"

2765 removercpt="$removercpt $a"

2766 fi

2767 done

2768 fi

2769 subj=`getpar subject`

2770 afiles=""

2771 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2772 do

2773 f=$tmpd/$fn

2774 if [ -s $f ]; then

2775 afiles=$afiles"${afiles:+ }$f"

2776 fi

2777 done

2778 else

2779 preface=$(cat <<-EOF

2780 $url

2781 のグループ「$grp」のメンバーである $gecos さんから、

2782 あなた宛のメッセージです。

2783 ----------------------------------------------------------

2784 EOF

2785 )

2786 fi

2787 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2788 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2789 subj="${subj:-$gecos さんからのメッセージ}"

2790 REPLYTO=$replyto

2791 MAIL_FROM=$mail_from

2792 export REPLYTO SMAIL_TO MAIL_FROM

2793 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2794 for r in $rcpts; do

2795 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2796 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2797 else

2798 # Show sender and rcpts address for guest

2799 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2800 fi

2801 if [ -n "$afiles" ];then

2802 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2803 else

2804 smail "$r" "$subj"

2805 fi <<EOF

2806 ${preface:+$preface$nl}$text

2807 EOF

2808 done

2809 if [ $? = 0 ]; then

2810 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2811 sql="select coalesce(b.val, a.name) from

2812 (select name from user where rowid in ($uids)) a

2813 left join user_s b on a.name=b.name and b.key='gecos';"

2814 html pre<<EOF

2815 `query "$sql"`

2816 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2817 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2818 EOF

2819 err SendDone: `echo $sql`

2820 fi

2821 elif [ x"$rm" = x"commission" ]; then

2822 grp_reg_adm $grid $usel

2823 elif [ x"$rm" = x"addteam" ]; then

2824 team=`getpar team|sed "s/'/''/g"` # for single quotation

2825 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2826 if [ x"$team" != x"$newteam" ]; then

2827 echo "チーム名に使えない文字を除去しました" | html p

2828 team=$newteam

2829 fi

2830 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2831 cat<<-EOF | html p

2832 有効なチーム名を入力してください。

2833 カンマだけ、「なし」という名前は使えません。

2834 EOF

2835 echo "有効なチーム名を入力してください。" | html p

2836 else

2837 grp_add_team $grid "$team" $usel

2838 fi

2839 elif [ x"$rm" = x"rmteam" ]; then

2840 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2841 rmteam=`getpar rmteam|sed "s/'/''/g"`

2842 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2843 gname='$grp' AND user='$user' AND key='team'\

2844 AND val='$rmteam';\"`" ]; then

2845 grp_rm_team $grid "$rmteam" $usel

2846 else

2847 echo "所属していないチームの除去操作はできません。"|html p

2848 fi

2849 else

2850 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2851 fi

2852 fi

2853 fi

2854 # POST count summary

2855 from=`getpar from`; to=`getpar to`

2856 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2857 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2858 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2859 # New entry

2860 sql="WITH mems AS (

2861 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2862 ON gm.user=g.name

2863 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2864 ), target_article AS (

2865 SELECT id FROM article_s

2866 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2867 ), posts AS (

2868 SELECT author, count(author) post

2869 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2870 WHERE blogid IN (SELECT id FROM blog_s

2871 WHERE key='owner'

2872 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2873 AND key='text'

2874 GROUP BY author

2875 ), teams AS (

2876 SELECT user, group_concat(val, ', ') team

2877 FROM grp_mem_m

2878 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2879 AND key='team'

2880 GROUP BY user

2881 ), user_post AS (

2882 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2883 FROM mems m LEFT JOIN posts

2884 ON m.name=posts.author

2885 GROUP by m.rowid

2886 )

2887 SELECT

2888 CASE

2889 WHEN (SELECT user FROM grp_adm

2890 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2891 AND user=up.name) IS NOT NULL

2892 then 'k'

2893 ELSE ''

2894 END || rowid || ',' || gecos NAME,

2895 post POST, team _TEAM_

2896 FROM user_post up LEFT JOIN teams t

2897 ON up.name=t.user

2898 ORDER BY gecos;"

2899 ## err grpaction: "`echo \"$sql\"`"

2900 tf=$tmpd/title.$$

2901 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2902 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2903 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2904 for=\"cmadmin\">管理者委任</label>

2905 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2906 </p></div>"

2907 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2908 title=\"Shortcut: g${nl}Remove from the Group\"

2909 for=\"conf\">GRP登録解除</label>

2910 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2911 <p>この操作による通知は本人に行きません。

2912 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2913 </div>"

2914 # Get team list to which current user belongs into $hexteams

2915 allhexteams=$(hexteams "$grp")

2916 if [ -n "$isowner" ]; then

2917 myhexteams="$allhexteams" # admin can remove all teams' attr

2918 else

2919 myhexteams=$(hexteams "$grp" "$user")

2920 fi

2921 if [ -n "$myhexteams" ]; then

2922 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2923 title=\"Shortcut: s${nl}Strip a team tag from\"

2924 for=\"cmrmteam\">チーム属性除去</label>

2925 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2926 を除去します: `cgi_checkbox teamconfirm yes` 確認

2927 <p>この操作による通知は本人に行きません。

2928 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2929 </div>

2930 "

2931 fi

2932 stf=$tmpd/selteam.$$

2933 cgi_select_h selteam "5445414d" $allhexteams > $stf

2934 b1='<label> <input type="checkbox" name="usel" value="'

2935 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2936 br='<span id="reverse" title="Reverse Selection"></span>'

2937 #b2='"> <span>' b3='</span></label>'

2938 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2939 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2940 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2941 cgi_form grpaction<<EOF \

2942 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2943 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2944 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2945 | _m4 -D_TITLE_="spaste(\`$tf')" \

2946 -D_SUBTITLE_="チェック後操作ボタン" \

2947 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2948 $layout/form+dump.m4.html \

2949 | _m4 -D_TEAM_="spaste(\`$stf')"

2950 <p>下でチェックした人を対象として:</p>

2951 <div class="foldtabs">

2952 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2953 title="Shortcut: a${nl}Add a team tag to"

2954 for="cmteam">同じチーム属性を付与</label>

2955 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2956 `cgi_datalist_h teams $allhexteams`

2957 </div>

2958 ${rmteammsg}

2959 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2960 title="Shortcut: d${nl}DirectMail to"

2961 for="sendmsg" title="Direct Message">DM送信</label>

2962 <div>

2963 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2964 title="Using email format">email書式を使う</label>

2965 <div class="folded">

2966 <table>

2967 <tr><td>From: </td><td>$user</td></tr>

2968 <tr><td>このFrom:で送る</td>

2969 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2970 </td></tr>

2971 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2972 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2973 <tr><td>ファイル添付: </td>

2974 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2975 </table>

2976 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2977 </div>

2978 <div>本文:`cgi_textarea text "" cols=72`

2979 </div>

2980 </div>

2981 ${isowner:+$cmmsg$excmsg}

2982 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2983 </div>

2984 <h4>$grp 参加者一覧</h4>$fromtonote

2985 <table class="td2r thl">

2986 `sq $db -header -html "$sql"`

2987 </table>

2988 `cgi_hidden grp $grid`

2989 `cgi_hidden myuid $myuid id="myuid"`

2990 EOF

2991 }

2992 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2993 # Create TEMPORARY VIEW

2994 query<<EOF

2995 CREATE TEMPORARY VIEW writeusers AS

2996 SELECT DISTINCT author FROM article

2997 WHERE id in (

2998 select id from article where blogid=(select id from blog where rowid=$1)

2999 );

3000 CREATE TEMPORARY VIEW movablegroups AS

3001 SELECT g.rowid growid , g.gname

3002 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3003 ON grp.gname=gm.gname -- そのユーザが属している

3004 AND user='$user') g -- グループに絞る

3005 WHERE (SELECT author FROM writeusers

3006 EXCEPT

3007 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3008 IS NULL;

3009 $2

3010 EOF

3011 }

3012 sql4readableblogs() {

3013 # Create view of blogs that can be readable to $user

3014 # Blog is readable when:

3015 # 1: blog owner is an user

3016 # 2: else, 2.1: owner-group where the $user belongs

3017 # 2.2: else, owner-group is not moderated

3018 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3019 cat<<EOF ## | tee tmp/sql.out

3020 CREATE TEMPORARY VIEW readableblogs AS

3021 SELECT blog.rowid rid, id, author

3022 FROM blog

3023 NATURAL JOIN

3024 (SELECT id,

3025 max(CASE key WHEN 'owner' THEN val END) owner,

3026 max(CASE key WHEN 'mode' THEN val END) mode

3027 FROM blog_s GROUP by id) bs

3028 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3029 THEN 1 -- blog owner is an user, READABLE

3030 WHEN (SELECT val FROM grp_s

3031 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3032 AND

3033 (SELECT user FROM grp_mem

3034 WHERE gname=bs.owner AND user='$user') IS NULL

3035 THEN 0

3036 WHEN mode IN ('quiz', 'enquete')

3037 THEN 0 -- "quiz" mode blog cannot be searched

3038 ELSE 1

3039 END;

3040 EOF

3041 }

3042 editheading() { # $1=rowid-of-heading

3043 rowid=${1%%[!A-Z0-9a-z_]*}

3044 if [ -z "$rowid" ]; then

3045 echo "話題番号が未指定です。" | html p

3046 return

3047 fi

3048 owner=`getvalbyid blog owner $rowid`

3049 title=`getvalbyid blog title $rowid`

3050 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3051 | _m4 -D_TITLE_="修正" \

3052 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3053 -D_BLOGS_="" -D_DUMPTABLE_="" \

3054 -D_FORM_="syscmd(\`cat')" \

3055 $layout/html.m4.html $layout/form+dump.m4.html

3056 # Move to group

3057 if isuser "$owner"; then

3058 crview4article $rowid

3059 n=`query "SELECT count(*) FROM writeusers;"`

3060 ## err N=$n

3061 if [ $((n)) -gt 0 ]; then

3062 ## err ROWID=$rowid

3063 sql="SELECT growid || ':' || gname FROM movablegroups;"

3064 cat<<-EOF

3065 <div class="fold">

3066 `cgi_checkbox mv send id="mv"`<label

3067 for="mv">この話題をグループ所有に移動する</label>

3068 <div>

3069 <form action="?mvart" method="POST" enctype="multipart/form-data">

3070 移動先グループ:

3071 <select name="mv2grp">

3072 EOF

3073 query ".mode html"

3074 query<<-EOF |

3075 $sql

3076 .mode list

3077 EOF

3078 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3079 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3080 cat<<-EOF

3081 </select>

3082 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3083 そのグループに加入しているものに限られます)</p>

3084 <p>`cgi_checkbox cfm yes`<label>確認

3085 (この操作は元に戻すことができません)</label></p>

3086 `cgi_hidden blogrowid $rowid`

3087 `cgi_submit 移動`

3088 `cgi_reset Reset`

3089 </form>

3090 </div>

3091 </div>

3092 EOF

3093 fi

3094 # end of isuser "$owner"

3095 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3096 [ -n "$hexteams" ];}; then

3097 none="`echo なし|hexize`"

3098 cat<<-EOF

3099 <div class="fold">

3100 `cgi_checkbox mv2team send id="mv2team"`<label

3101 for="mv2team">この話題を以下のチームのものにする</label>

3102 <div><p>現在の所属チーム設定:

3103 `query "SELECT

3104 coalesce((SELECT val FROM blog_s

3105 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3106 AND key='team'),

3107 ':なし');"`</p>

3108 <form action="?mvart" method="POST" enctype="multipart/form-data">

3109 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3110 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3111 `cgi_hidden blogrowid $rowid`<br>

3112 `cgi_submit 移動`

3113 `cgi_reset Reset`

3114 </form></div></div>

3115 EOF

3116 fi

3117 }

3118 mvart() { # move diary to some group or team

3119 # or move blog of group to team which belong to the group

3120 blogrowid=`getpar blogrowid`

3121 cfm=`getpar cfm`

3122 ##### echo move blog:$blogrowid to $mv2grp | html p

3123 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3124 . ./s4-blog.sh

3125 if [ -z "$blogrowid" ]; then

3126 echo "無効な指定です(mvart)。" | html p

3127 return

3128 elif [ x"$cfm" != x"yes" ]; then

3129 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3130 elif { mv2grp=`getpar mv2grp`

3131 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3132 [ -n "$mv2grp" ]; }; then

3133 crview4article $blogrowid

3134 ########## TRANSACTION BEGIN

3135 query "BEGIN;"

3136 n=`query "SELECT count(*) FROM writeusers;"`

3137 ## err Nwriteuser=$n

3138 if [ $((n)) -gt 0 ]; then

3139 query<<-EOF

3140 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3141 WHERE key='owner'

3142 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3143 AND $mv2grp IN (SELECT growid FROM movablegroups);

3144 EOF

3145 fi

3146 query "END;"

3147 ########## TRANSACTION END

3148 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3149 [ -n "$mv2team" ];}; then

3150 # blog owner can move it to ANY team

3151 case "$mv2team" in

3152 'なし')

3153 cat<<-EOF

3154 DELETE FROM blog_s

3155 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3156 AND key='team';

3157 EOF

3158 ;;

3159 "") ;;

3160 *)cat<<-EOF

3161 BEGIN;

3162 REPLACE INTO blog_s(id, key, val)

3163 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3164 'team', '$mv2team');

3165 REPLACE INTO blog_s(id, key, val)

3166 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3167 'notify', 'all'); -- Change notify to all

3168 END;

3169 EOF

3170 esac | query

3171 fi

3172 blog_reply $blogrowid

3173 echo yes | html p

3174 }

3175 editart() { # $1=article-rowid $2=blogrowid

3176 rowid=${1%%[!A-Z0-9a-z_]*}

3177 blogrowid=${2%%[!A-Z0-9a-z_]*}

3178 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3179 echo "表示する記事番号が未指定です。" | html p

3180 return

3181 fi

3182 owner=`getvalbyid blog owner $blogrowid`

3183 title=`getvalbyid blog title $blogrowid`

3184 author=`getvalbyid article author $rowid`

3185 ## err EDITart: owner=$owner, author=$author

3186 if isgrpowner "$user" "$owner"; then

3187 : EDIT OK

3188 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3189 echo "本人か所有者しか編集できません." | html p

3190 return

3191 fi

3192 aid=`query "select id from article where rowid=$rowid;"`

3193 tmpout=$tmpd/editart.$$.out

3194 GF_ACTION="?replyblog+$blogrowid#$aid" \

3195 edittable $formdir/article.def article $rowid \

3196 > $tmpout

3197 rm -f /tmp/editart.out

3198 # Cannot use pipelining to m4 with genform() because of stdin stack

3199 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3200 -D_FORM_="syscmd(cat $tmpout)" \

3201 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3202 -D_BLOGS_= -D_DUMPTABLE_= \

3203 $layout/html.m4.html $layout/form+dump.m4.html

3204 }

3205 send2mem() {

3206 rowid=`getpar grp`

3207 rowid=${rowid%%[!0-9]*} # Cleaning

3208 if [ -z "$rowid" ]; then

3209 echo "グループが未指定です。" | html p

3210 return

3211 fi

3212 message=`getpar message`

3213 if [ -z "$message" ]; then

3214 echo "文章を入れてください。" | html p

3215 return

3216 fi

3217 grp=`getgroupbyid $rowid`

3218 members=`collectemail $grp`

3219 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3220 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3221 mailfrom="`gecos "$user"` <$mailfrom>"

3222 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3223 msg=$(cat<<-EOF

3224 $urlbase?grp+$rowid

3225 グループ $grp に所属する

3226 `gecos $user` さんよりメッセージ:

3227

3228 $message

3229 EOF

3230 )

3231 # smail rcpt subj (file)

3232 for m in $members; do

3233 echo "$msg" |

3234 MAIL_FROM=$mailfrom \

3235 SENDER=$noreply \

3236 REPLYTO=$mailfrom \

3237 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3238 smail "$m" "$sj"

3239 done

3240 cat<<EOF

3241 <p>以下のユーザに送信しました。</p>

3242 <pre>

3243 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3244 </pre>

3245 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3246 EOF

3247 }

3248 joingrpadmit() {

3249 # $1=yes/no $2=session-key

3250 if [ -z "$2" ]; then

3251 echo "bye bye" | html p; return

3252 fi

3253 t_usr=`session=$2 getpar user`

3254 t_grp=`session=$2 getpar group`

3255 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3256 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3257 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3258 echo "無効な加入依頼です。" | html p

3259 echo "有効期限が切れたか、

3260 他の管理者がいる場合は処理済みの可能性があります。" | html p

3261 return

3262 fi

3263 if ! isgrpowner "$user" "$t_grp"; then

3264 echo "グループ管理者のみの機能です。" | html p; return

3265 fi

3266 case $1 in

3267 yes) joingrp "$t_grp" "$t_usr" yes ;;

3268 no) joingrp "$t_grp" "$t_usr" no ;;

3269 *)

3270 echo "無効な指定です($1)。" | html p

3271 return ;;

3272 esac

3273 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3274 rcpts="`getgroupadminmails $t_grp` $user"

3275 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3276 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3277 に

3278 $t_usr

3279 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3280 しました。"

3281 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3282 query "delete from session where id='$2';"

3283 echo "$body" | html p

3284 }

3285

3286 joingrprequest() {

3287 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3288 jss="joingrp-`date +%s`-`genrandom 12`"

3289 addsession $jss +${memoplimitdays}days

3290 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3291 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3292 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3293 $url

3294 $user さんから

3295 グループ $1

3296 に加入依頼がありました。

3297

3298 承認する:

3299 $urlbase?joingrpadmit+yes+$jss

3300

3301 白紙に戻す:

3302 $urlbase?joingrpadmit+no+$jss

3303 EOF

3304 echo "管理者に加入依頼を出しました。

3305 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3306 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3307 }

3308 joingrp() {

3309 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3310 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3311 if isgrpowner "$user" "$1"; then

3312 isowner="yes"

3313 elif [ -n "$5" ]; then

3314 isowner="yes"

3315 else

3316 isowner=""

3317 fi

3318 ## err jg:isgrpowner: isowner="$isowner"

3319 if [ -n "$isowner" ]; then

3320 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3321 elif [ x"$2" != x"$user" ]; then # if user is not login user

3322 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3323 return

3324 elif [ x"$3" = x"no" ]; then

3325 : # Do not pursue those who leave

3326 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3327 : # Member can change own email address for the joining moderated group

3328 else # adding user is $user itself

3329 case `getgroupattr $1 regmode` in

3330 moderated)

3331 joingrprequest "$@" # Request only

3332 return

3333 ;;

3334 *)

3335 ;;

3336 esac

3337 fi

3338 qgname=`sqlquote "$1"`

3339 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3340 cond="where gname=$qgname and user='$2'"

3341 if [ x"$3" = x"yes" ]; then

3342 query "replace into grp_mem values($qgname, '$2');"

3343 # Notify joingrp to admin

3344 action="に加入しました。"

3345 if [ -n "$4" ]; then

3346 if msg=`emaildomaincheck "$4"`; then

3347 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3348 'string', '$4', NULL);"

3349 else

3350 echo $msg

3351 fi

3352 else

3353 query "delete from grp_mem_s $cond and key='email';"

3354 fi

3355 if [ -n "$5" ]; then # as ADMIN

3356 # Coming here means newly created group

3357 sql="select case\

3358 when (select count(*) from grp_mem where gname=$qgname)=1\

3359 then (select user from grp_mem\

3360 where gname=$qgname and user='$user')\

3361 else '' end; "

3362 err NewGrpChk: $sql

3363 if [ -n "`query \"$sql\"`" ]; then

3364 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3365 query "replace into grp_adm values($qgname, '$user');"

3366 fi

3367 fi

3368 else

3369 query "begin;

3370 delete from grp_mem $cond;

3371 delete from grp_mem_s $cond;

3372 delete from grp_mem_m $cond;

3373 delete from grp_adm $cond;

3374 delete from grp_adm_s $cond;

3375 delete from grp_adm_m $cond;

3376 end;"

3377 action="から脱退しました。"

3378 fi

3379 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3380 $url?grp+$grid

3381 $user (`gecos $user`)さんが

3382 グループ $1

3383 $action

3384 EOF

3385 }

3386 grp_add_team() (

3387 # $1=grp-rowid $2=team $3...=user-rowid(s)

3388 grp=`getgroupbyid $1`

3389 team=$2; shift; shift

3390 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3391 { echo "BEGIN;"

3392 for user; do

3393 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3394 '$grp',\

3395 (SELECT name FROM user WHERE rowid=$user),\

3396 'team', 'string', '$team');"

3397 done

3398 echo "END;"

3399 } | query

3400 )

3401 grp_rm_team() (

3402 # $1=grp-rowid $2=team $3...=user-rowid(s)

3403 grid=$1

3404 qgrp=$(sqlquote "`getgroupbyid $grid`")

3405 team=$2; shift; shift

3406 [ -z "$grid" -o -z "$team" ] && return

3407 { echo "BEGIN;"

3408 for user; do

3409 echo "DELETE FROM grp_mem_m\

3410 WHERE gname=$qgrp \

3411 AND user=(SELECT name FROM user WHERE rowid=$user)\

3412 AND key='team' AND val='$team';"

3413 done

3414 cat<<-EOF

3415 DELETE FROM blog_s

3416 WHERE rowid=(

3417 SELECT rowid

3418 FROM blog_s a

3419 WHERE key='team'

3420 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3421 AND NOT EXISTS (SELECT * FROM grp_mem_m

3422 WHERE key='team' AND val=a.val -- a.val=team

3423 AND gname = (SELECT val FROM blog_s b

3424 WHERE a.id=b.id AND key='owner')

3425 ));

3426 EOF

3427

3428 echo "END;"

3429 } | query

3430 )

3431 grp_reg_adm() {

3432 # $1=grp-rowid $2...=user-rowid

3433 grid=$1

3434 grp=`getgroupbyid "$1"`

3435 if [ -z "$grp" ]; then

3436 echo "無効なグループIDです" | html p; return

3437 fi

3438 if ! isgrpowner "$user" "$grp"; then

3439 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3440 fi

3441 shift

3442 for urid; do

3443 newadm=`query "select name from user where rowid=$urid;"`

3444 if [ -z "$newadm" ]; then

3445 echo "指定ユーザIDがおかしいようです。" | html p; return

3446 fi

3447 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3448 err ismember $newadm $grp

3449 if ismember $newadm "$grp"; then

3450 # OK, go ahead

3451 getgname="(select gname from grp where rowid=$grid)"

3452 query "replace into grp_adm values($getgname, '$newadm');"

3453 # confirm insertion

3454 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3455 if [ -n "`query \"$sql;\"`" ]; then

3456 echo "追加完了: $newadm" | html p

3457 else

3458 echo "追加失敗($1 $urid)" | html p

3459 fi

3460 fi

3461 showgroup $grid

3462 done

3463 }

3464 dt_rowhack() {

3465 # From: <TR>

3466 # ....

3467 # <TD>rowclass=foo</TD>

3468 # </TR>

3469 # To: <TR class="foo">....<TD>foo</TD></TR>

3470 sed -e '

3471 /^<TR>/ {

3472 :loop

3473 s/\n//

3474 N

3475 /<\/TR>/ {

3476 s/\n//

3477 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3478 n

3479 }

3480 $q

3481 b loop

3482 }'

3483 }

3484 dumptable() {

3485 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3486 # textのフィールドだけ全てダンプにしたほうがいいか

3487 # $DT_VIEW sets link

3488 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3489 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3490 VIEW=${DT_VIEW-replyblog}

3491 if [ -n "$VIEW" ]; then

3492 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3493 fi

3494 sqlfile=$tmpd/dump.sql

3495 : > $sqlfile # ensure to be empty

3496 printf '.mode html\n.header 1\n' > $sqlfile

3497 # $DT_CHLD=ChildTable:BindColumn

3498 if [ -n "$DT_CHLD" ]; then

3499 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3500 cat<<-EOF >> $sqlfile

3501 -- presql

3502 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3503 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3504 EOF

3505 # Speed up counting of new articles

3506 cat<<-EOF >> $sqlfile

3507 -- presql2

3508 DROP TABLE IF EXISTS _counts;

3509 CREATE TEMPORARY TABLE _counts AS

3510 SELECT $_i, count($_i) cnt

3511 FROM $_t GROUP BY $_i;

3512 /* Prepare NEW count table */

3513 CREATE TEMPORARY TABLE _target AS

3514 SELECT b.rowid trowid, b.id

3515 FROM "$2" b JOIN "$2_s" s

3516 ON b.id=s.id AND s.key='owner'

3517 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3518

3519 DROP TABLE IF EXISTS _children;

3520 CREATE TEMPORARY TABLE _children AS

3521 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3522 FROM (SELECT t.trowid, t.id $_i, a.id

3523 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3524 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3525

3526 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3527 DROP TABLE IF EXISTS _news;

3528 DROP VIEW IF EXISTS _news;

3529

3530 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3531 -- INSERT INTO _news

3532 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3533 CREATE TEMPORARY VIEW _news AS

3534 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3535 FROM (SELECT DISTINCT id FROM _target)

3536 a LEFT JOIN

3537 (SELECT $_i, count(ctime) newcnt

3538 FROM _children x

3539 WHERE ctime > coalesce((SELECT time from myacclog

3540 WHERE tblrowid=x.trowid),

3541 '1970-01-01')

3542 GROUP BY $_i) b

3543 ON a.id=b.$_i;

3544 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3545 EOF

3546 # REMOVE next line until 2019/5/1

3547 cntall="(select count($_i) from $_t where $_i=a.id)"

3548 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3549 # REMOVE next assignment until 2019/5/1

3550 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3551 and id in (select id from $_t where $_i=a.id) \

3552 and val > coalesce((select time from myacclog where \

3553 tblrowid=a.rowid),\

3554 '1970-01-01'))"

3555 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3556 cnt="$cntnew as '新着', $cntall as '総数',"

3557 dt_class=" td2r td3r dumpblogs"

3558 fi

3559 # Construct join expression

3560 eav="" scols=""

3561 pk=`gettblpkey $2`

3562 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3563 substr=${substr:-%s}

3564 for col in ${3:-`gettbl_s_cols $2`}; do

3565 valvar=val

3566 case $col in

3567 gecos) scols="$scols${scols:+, }${col#}"

3568 continue ;; # built-in column name

3569 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3570 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3571 case "$as" in

3572 *:*=*) cnd=${as#*:}

3573 h=${cnd%%=*} v=${cnd#*=}

3574 h=`sqlquotestr "$h"`

3575 v=`sqlquotestr "$v"`

3576 valvar="CASE val WHEN $h THEN $v END"

3577 as=${as%%:*} ;;

3578 esac

3579 ;;

3580 *) as=${col} ;;

3581 esac

3582 ss=`printf "$substr" "$valvar"`

3583 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3584 scols="$scols${scols:+, }b.$as"

3585 done

3586 #case author when '$user' then a.rowid else '---' end as ID,

3587 if [ -n "$DT_SQL" ]; then

3588 echo "$DT_SQL"

3589 else

3590 cat<<-EOF

3591 SELECT a.rowid as LINK, $cnt $scols

3592 FROM $2 a LEFT JOIN

3593 (SELECT $pk,$eav,

3594 max(CASE key

3595 WHEN 'owner'

3596 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3597 as gecos

3598 FROM ${2}_s c GROUP BY $pk)

3599 b ON a.$pk=b.$pk $4;

3600 EOF

3601 fi >> $sqlfile

3602 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3603 sqlog<<-EOF

3604 *** SQL-file: $sqlfile ***

3605 `cat $sqlfile`

3606 EOF

3607 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3608 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3609 cat $sqlfile >> tmp/sql

3610 # query ".read $sqlfile" > $tmpd/foo

3611 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3612 cp $tmpd/foo tmp/

3613 err "----- `gdate +%FT%T.%3N` ------------555555"

3614 ## $ddd LINE exists at the end of this function

3615 fi

3616 printf '.mode list\n.header 0\n' >> $sqlfile

3617 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3618 <div>

3619 <div class="dumptable">

3620 <table class="b$dt_class">

3621 `query ".read $sqlfile"`

3622 </table>

3623 </div>

3624 </div>

3625 EOF

3626 ### `query ".read $sqlfile"`

3627 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3628

3629 ## REMOVE THIS!

3630 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3631 }

3632

3633 par2table() (

3634 # copy current parameters of par into destination table

3635 # $1=definition-file

3636 # Using $user and $session

3637 # Return value:

3638 # 0: Stored successfully

3639 # 1: Insufficient fillings

3640 # 2: No permission to modify the record

3641 # 3: Invalid rowid

3642 # 4: SUCCESS to delete

3643 # 5: Stop deletion for lack of confirm check

3644 # 6: Password length too short

3645 # 7: Password mismatch

3646 # 8: Old password incorrect

3647 # 9: Duplicated post

3648 rowid=`getpar rowid`

3649 if [ ! -e $1 ]; then

3650 echo "テーブル定義ファイルが見付かりません" | html p

3651 exit 1

3652 fi

3653 tbl=${1%.def}

3654 tbl=${tbl##*/}

3655 if [ -n "$rowid" ]; then # Modify existing entry

3656 if [ x"$tbl" = x"user" ]; then

3657 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3658 elif [ x"$tbl" = x"grp" ]; then

3659 sql="select gname from $tbl where rowid=$rowid;"

3660 ##err p2t:grp:q $sql

3661 isgrpowner "$user" "`query $sql`" && rowowner=$user

3662 elif [ x"$tbl" = x"blog" ]; then

3663 # Check if owner in blog_s

3664 blogowner=`getvalbyid blog owner "$rowid"`

3665 if isgrpowner "$user" "$blogowner"; then

3666 rowowner=$user

3667 else

3668 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3669 fi

3670 else

3671 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3672 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3673 fi

3674 ### err rowowner=$rowowner

3675 if [ x"$user" != x"$rowowner" ]; then

3676 echo "他人のレコードはいじれないの" | html p

3677 return 2

3678 elif [ -z "$rowowner" ]; then

3679 echo "指定したレコードはないみたい" | html p

3680 return 3

3681 fi

3682 rm=`getpar rm` cfm=`getpar confirm`

3683 # Editing existent entry

3684 if [ x"$rm" = x"yes" ]; then

3685 if [ x"$rm$cfm" = x"yesyes" ]; then

3686 query "delete from $tbl where rowid=$rowid;"

3687 return 4

3688 else

3689 echo "消去確認のチェックがないので消さなかったの..." | html p

3690 return 5

3691 fi

3692 fi

3693 fi

3694

3695 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3696 if [ -n "$rowid" ]; then

3697 # Update of existing record

3698 for col in `gettblcols $tbl`; do

3699 val=`getparquote $col`

3700 [ -z "$val" ] && continue

3701 ## err query "update $tbl set $col=$val where rowid=$rowid"

3702 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3703 sql="update $tbl set $col=$val where rowid=$rowid;"

3704 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3705 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3706 ## User name cannot be changed with interface provided with this

3707 ## script. But we offer the trigger to change owner user

3708 ## of blog_s table.

3709 #err "select quote($col) from $tbl where rowid=$rowid;"

3710 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3711 cat<<-EOF | query

3712 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3713 -- should use EXCLUSIVE transaction outside of this.

3714 SAVEPOINT par2table;

3715 $sql

3716 update blog_s set val=$val

3717 where key='owner' and val=$old;

3718 RELEASE SAVEPOINT par2table;

3719 EOF

3720 ## XX: DIRTY Hack Ends here

3721 ## We should keep blog's owner as a single column which has

3722 ## foreign key constraint with primary key of grp/user.

3723 else

3724 query "$sql"

3725 fi

3726 done

3727 # Then, set up $pval for further insertion of tbl_s and tbl_m

3728 for col in `gettblpkey $tbl`; do

3729 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3730 pval="$pval${pval:+, }\"$val\""

3731 done

3732 else

3733 # New entry

3734 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3735 # Avoid empty repost of article.

3736 if [ x"$tbl" = x"article" ]; then

3737 # If rowid is empty and ID exists in article-table, that is REPOST!

3738 aid=`getpar id`

3739 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3740 if [ -n "$xaid" ]; then

3741 # REPOST of article

3742 html p <<-EOF

3743 書き込み直後のリロードなので上書きを回避します。

3744 最新記事は末尾の「再読み込み」ボタンから見てください。

3745 EOF

3746 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3747 return 9 # STOP Duplicated posting

3748 fi

3749 fi

3750 # Generate values() for primary keys

3751 for col in `gettblpkey $tbl`; do

3752 # Genuine primary keys for _m and _s

3753 val=`getvalquote $tbl $col`

3754 [ -z "$val" ] && continue

3755 pval="$pval${pval:+, }$val"

3756 done

3757 ##err pval=$pval

3758 for col in `gettblfkey $tbl`; do

3759 # args for values() to insertion into master table

3760 val=`getvalquote $tbl $col`

3761 [ -z "$val" ] && continue

3762 formaster=$formaster"${formaster:+, }$val"

3763 done

3764 formaster="$pval${formaster:+, }$formaster"

3765 ## err formaster=$formaster

3766 if [ -z "$formaster" ]; then

3767 echo "項目を全て埋めてください" | html pre

3768 return 1

3769 fi

3770 ## err "replace into $tbl values($formaster);"

3771 query "replace into $tbl values($formaster);"

3772 ## Insertion to master table, done

3773 fi

3774

3775 transaction=$tmpd/sqlfile.sql; touch $transaction

3776 for kt in s m; do

3777 tb2=${tbl}_$kt

3778 for col in `gettbl_${kt}_cols $tbl`; do

3779 ptype=`getpartype $col "limit 1"`

3780

3781 # First, check update of existing entries in _m

3782 if [ $kt = m ]; then

3783 # sessID|address.1.22|string|Somewhere-x.y.z

3784 sql=""

3785 ##err dots from query "select var from par where var like '$col.%';"

3786 for v in `query "select var from par where var like '$col.%';"`; do

3787 # v=address.1.22

3788 st_rowid=${v##*.}

3789 origcol=${v%%.*} # original column derived from

3790 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3791 ##case `getpartype $v` in

3792 ## err CASE `gettbl_coltype $tbl/$origcol` in

3793 ## err edit flag = `getpar action.$v`

3794 case `getpar action.$v` in

3795 rm)

3796 if [ x`getpar confirm.$v` = x"yes" ]; then

3797 newsql="delete from $tb2"

3798 else

3799 echo "削除確認未チェック" | html p

3800 fi ;;

3801 edit)

3802 case `gettbl_coltype $tbl/$origcol` in

3803 image|document|binary)

3804 file=$tmpd/`getparfilename $v`

3805 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3806 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3807 continue

3808 fi

3809 ## err type=file=$file

3810 [ -z "$file" ] && continue

3811 bn=`sqlquotestr "${file##*/}"`

3812 bin="X'"$(hexize "$file")"'"

3813 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3814 type=\"file:$ct\"

3815 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3816 cachedir=`getcachedir "$tbl/$rowid"`

3817 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3818 rm -rf $cachedir

3819 ;;

3820 *)

3821 newsql="update $tb2 set val=(select val from par where var \

3822 like '$col.%.$st_rowid')"

3823 ;;

3824 esac

3825 ;;

3826 *) # maybe "keep", do not modify value

3827 continue

3828 ;;

3829 esac

3830 # err newsql=$newsql

3831 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3832 done

3833

3834 if [ x"$bin" = x"NULL" ]; then

3835 ## err repl:normal sql=`echo $sql`

3836 if [ -n "$transaction" ]; then

3837 cat<<-EOF >> $transaction

3838 $sql

3839 DELETE FROM $tb2 WHERE type='string' AND val='';

3840 EOF

3841 else

3842 query "$sql

3843 delete from $tb2 where type='string' and val='';"

3844 ## err repl:normal done

3845 fi

3846 else

3847 # Binary update line is TOO LONG to pipelining

3848 sqlfile="$tmpd/sqlf.$$"

3849 if [ -n "$transaction" ]; then

3850 echo "$sql" >> $transaction

3851 else

3852 echo "$sql" > $sqlfile

3853 query ".read $sqlfile"

3854 fi

3855 fi

3856 # Rest of kt==m: set multiple mode

3857 nr=`getparcount $col`

3858 else

3859 nr=1 # for kt==s, number of records is 1

3860 fi

3861

3862 i=0

3863 while [ $i -lt $nr ]; do

3864 limit="limit 1 offset $i"

3865 i=$((i+1)) # increase beforehand against continue

3866 val=`getvalquote $tbl $col "$limit"`

3867 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3868 ## err $col=$val

3869 bin=NULL

3870 ## err partype$col=`getpartype $col "$limit"`

3871 case $ptype in

3872 file) file=$tmpd/`getparfilename $col "$limit"`

3873 ## err parfile-$col=$file

3874 [ -z "$file" ] && continue

3875 bin="X'"$(hexize "$file")"'"

3876 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3877 type=\"file:$ct\" ;;

3878 "*"*) continue ;; # foreign table

3879 *) type=\"string\" ;;

3880 esac

3881 case `gettbl_coltype $tbl/$col` in

3882 password) # special care for password

3883 # name={password,pswd1,pswd2}

3884 p1=`getpar pswd1 "$limit"`

3885 if [ -z "$p1" ]; then

3886 continue # SKIP password setting, if p1 is empty

3887 else

3888 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3889 ## err pswd=$pswd

3890 if pwcheck "$pswd"; then

3891 if [ x"$p1" = x"$p2" ]; then

3892 case "$p1" in

3893 ??????????*) ;;

3894 *) echo "パスワードは10字以上にしてください。" | html p

3895 return 6;;

3896 esac

3897 val="\"`echo $p1|mypwhash`\""

3898 else

3899 echo "2つの新パスワード不一致" | html p

3900 return 7

3901 fi

3902 else

3903 echo "旧パスワード違います" | html p

3904 return 8

3905 fi

3906 fi

3907 ;;

3908 esac

3909 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3910 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3911 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3912 if [ x"$bin" = x"NULL" ]; then

3913 ## err Normal-query: `echo $sql`

3914 if [ -n "$transaction" ]; then

3915 echo "$sql" >> $transaction

3916 else

3917 query "$sql"

3918 fi

3919 else

3920 sqlfile="$tmpd/query.$$"

3921 ## err sqlfile=`ls -lF $sqlfile`

3922 if [ -n "$transaction" ]; then

3923 echo "$sql" >> $transaction

3924 else

3925 echo "$sql" > $sqlfile

3926 query ".read $sqlfile"

3927 fi

3928 fi

3929 ## err p2t done

3930 done

3931 done

3932 done

3933 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3934 -- We cannot use transaction here, because groupupdate may use it.

3935 SAVEPOINT pa2table_insert;

3936 .read $transaction

3937 RELEASE SAVEPOINT pa2table_insert;

3938 EOF

3939 return $?

3940 ##err donee

3941 )

3942 genform() {

3943 # $1 = form definition file

3944 # $2, $3 (optional)= table name and ROWID

3945 # If $GF_VIEWONLY set and nonNull, output values without form

3946 # If $GF_ARGS set, use it as content-strings in the form

3947 # If $GF_OWNER set, use it as value of name="owner"

3948 # If $GF_STAGE set, use it as value of name="stage"

3949 forms="" hiddens="" rowid=$3

3950 if [ ! -e "$1" ]; then

3951 echo "そのようなデータベースはないようです($2)。" | html p

3952 return

3953 elif [ -n "$2" ]; then

3954 rec=`query "select * from $2 where rowid='$rowid';"`

3955 if [ -z "$rec" ]; then

3956 pk=`gettblpkey $2`

3957 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3958 rec=`query "select rowid from $2 where $pk='$rowid';"`

3959 rowid=$rec

3960 rec=$3

3961 fi

3962 if [ -z "$rec" ]; then

3963 echo "そんなレコードはないみたいね..." | html p

3964 return

3965 fi

3966 fi

3967 if [ -z "$GF_VIEWONLY" ]; then

3968 rm='<input id="rm" name="rm" type="checkbox"

3969 value="yes"><label for="rm">このエントリの削除</label>

3970 <span>ほんとうに消しますよ(確認)!

3971 <input name="confirm" type=checkbox value="yes">はい</span>'

3972 fi

3973 # Image Cache dir

3974 ## err genform: getcache=$2/$rowid

3975 td=`getcachedir "$2/$rowid"`

3976 while IFS=: read prompt name keytype type args; do

3977 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3978 sp="${args:+ }"

3979 form="" val=""

3980 if [ -n "$rowid" ]; then

3981 # err genform2a: Seeking for "$2.$name, type=$type"

3982 rawval=`getvalbyid $2 $name $rowid $td`

3983 val=`echo "$rawval"|htmlescape`

3984 ## err genform3a: getvalbyid $2 $name $rowid $td

3985 ## err genform3b: val="[$val]" type="$type"

3986 fi

3987 if [ -n "$GF_VIEWONLY" ]; then

3988 is_hidden "$2" "$name" && continue

3989 fi

3990 case "$type" in

3991 text*)

3992 cgiform=cgi_multi_$type

3993 if [ -s $td/$name.count -a -n "$val" ]; then

3994 form=`$cgiform $name $td`

3995 val=$(echo "$val"|

3996 while read fn; do

3997 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3998 </td></tr>$nl"

3999 done)

4000 val="<table>$nl$val$nl</table>"

4001 else

4002 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4003 form=`cgi_$type $name "$rawval" "$args"`

4004 fi

4005 ;;

4006 [Rr][Aa][Dd][Ii][Oo])

4007 fh="<label><input type=\"radio\" name=\"$name\""

4008 form="`echo $args|sed -e \

4009 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4010 ;;

4011 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4012 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4013 ;;

4014 [Ss][Ee][Ll][Ee][Cc][Tt])

4015 fh="<select name=\"$name\">$nl"

4016 form=$(for l in $args; do

4017 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4018 done)

4019 if [ -n "$val" ]; then

4020 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4021 fi

4022 form="$fh$form</select>"

4023 ;;

4024 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4025 if [ -s $td/$name.count ]; then

4026 form=`cgi_multi_file $name $td "$args"`

4027 if [ -n "$val" ]; then

4028 hrfb="$myname?showattc+$2_m"

4029 val=$(echo "$rawval" \

4030 | while read fn; do

4031 data=`percenthex "$td/$fn"`

4032 #ct=`cat $td/$fn.content-type`

4033 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4034 ri=`cat "$td/$fn.rowid"`

4035 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4036 #imgsrc="<img src=\"data:$ct,$data\">"

4037 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4038 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4039 done)

4040 fi

4041 else

4042 form="<input type=\"file\" name=\"$name\" $args>"

4043 if [ -n "$val" ]; then

4044 imgs=$(echo "$rawval"\

4045 |while read fn;do

4046 data=`percenthex "$td/$fn"`

4047 echo "<img src=\"data:image/png,$data\">$fn<br>"

4048 done)

4049 form=$form"<br>$imgs"

4050 val=$imgs # 2015-06-15

4051 else

4052 form="<input type=\"file\" name=\"$name\" $args>"

4053 fi

4054 fi

4055 ;;

4056 [Hh][Ii][Dd][Dd][Ee][Nn])

4057 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4058 args="value=\"$GF_STAGE\""

4059 fi

4060 form="<input type=\"hidden\" name=\"$name\" $args>"

4061 prompt='' # Remove prompt

4062 ;;

4063 [Aa][Uu][Tt][Hh][Oo][Rr])

4064 [ -n "$GF_VIEWONLY" ] && continue

4065 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4066 prompt="" ;;

4067 [Oo][Ww][Nn][Ee][Rr])

4068 [ -n "$GF_VIEWONLY" ] && continue

4069 val=${GF_OWNER:-$val}

4070 val=${val:-$user}

4071 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4072 prompt="" ;;

4073 [Uu][Ss][Ee][Rr])

4074 # XXX: is null $user ok?

4075 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4076 [ -n "$GF_VIEWONLY" ] && continue

4077 form="$user"

4078 ;;

4079 [Pp]assword)

4080 [ -n "$GF_VIEWONLY" ] && continue

4081 form="`cgi_passwd`"

4082 val=""

4083 ;;

4084 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4085 [ -n "$GF_VIEWONLY" ] && continue

4086 if [ -z "$rowid" ]; then

4087 val=`genserial`

4088 fi

4089 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4090 prompt="" ;;

4091 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4092 prompt=""

4093 ;;

4094 parent|path|blog*)

4095 prompt=""

4096 ;;

4097 "*"*)

4098 tail=$tail"``"

4099 continue ;;

4100 esac

4101 if [ -n "$prompt" ]; then

4102 if [ -n "${GF_VIEWONLY}" ]; then

4103 form=$val

4104 else

4105 :

4106 fi

4107 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4108 else

4109 hiddens=$hiddens$nl"$form"

4110 fi

4111 done < $1

4112 # enctype="multipart/form-data"

4113 cat<<EOF

4114 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4115 ${rowid:+$rm}

4116 <table class="b $2">

4117 $forms

4118 </table>$hiddens

4119 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4120 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4121 EOF

4122 if [ -z $GF_VIEWONLY ]; then

4123 cat<<EOF

4124 <input type="submit" name="sub" value="OK">

4125 <input type="reset" name="res" value="Reset">

4126 EOF

4127 fi

4128 cat<<EOF

4129 $GF_ARGS</form>

4130 $tail

4131 EOF

4132 }

4133 edittable() {

4134 # $1=form-def $2=table $3 rowid

4135 genform "$@"

4136 }

4137 viewtable() {

4138 GF_VIEWONLY=1 genform "$@"

4139 }

4140 showattc() {

4141 # $1=table_m $2=rowid &optional $3=RawFlag

4142 ## err \$1=$1 \$2=$2 \$3=$3

4143 if ! isfilereadable $user $1 $2; then

4144 contenttype; echo

4145 echo "このファイルは管理者のみしか見られません" | html p

4146 putfooter; exit

4147 fi

4148 idir=`umask 002; mktempd` || exit 1

4149 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4150 bin=$idir/$myname-$$.bin

4151 sql="select quote(bin) from $1 where rowid='$2';"

4152 ## err showattc: sql: $sql

4153 sq $db "$sql" | unhexize > $bin

4154 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4155 type=${tv%//*} fn=${tv#*//}

4156 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4157 ct=${type#file:}

4158 case $ct in # all text/* changed to text/plain

4159 text/*)

4160 charset=`nkf -g $bin|cut -d' ' -f1`

4161 case $charset in

4162 ASCII*) charset="" ;;

4163 esac

4164 if [ -z "$3" ]; then

4165 ct="text/html${charset:+; charset=$charset}"

4166 link="?showattc+$1+$2+raw"

4167 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4168 | sed 's,^,<span></span>,' \

4169 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4170 -D_LINK_="$link" \

4171 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4172 exit $?

4173 fi

4174 ct="text/plain${charset:+; charset=$charset}"

4175 ;;

4176 esac

4177 contenttype "$ct"

4178 echo "Content-Disposition: filename=\"$fn\""

4179 echo "Content-Length: " `cat $bin | wc -c`; echo

4180 #echo "Content-Type: " ${type#file:}; echo

4181 cat $bin

4182 }

4183 #

4184 # Some default stupid handler on CGI values

4185 #

4186 default_storedb() {

4187 # ARG: $1=table-def-file

4188 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4189 tbl=`basename $1`

4190 tbl=${tbl%.def}

4191 cols="`grep :text $1|cut -d: -f2`"

4192 col=`echo "$cols"|head -1`

4193 vcol=`getpar $col`

4194 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4195 if [ -n "$vcol" ]; then

4196 par2table $1

4197 else

4198 return 2 # No insertion occurred

4199 fi

4200 }

4201

4202 default_view() { # $1=def-file

4203 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4204 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4205 default_storedb "$@"

4206 query "select rowid from $tbl order by rowid desc;" \

4207 | while read rowid; do

4208 viewtable $1 $tbl $rowid

4209 done | _m4 -D_TITLE_="$tbl" \

4210 -D_FORM_="`genform $1`" \

4211 -D_DUMPTABLE_="syscmd(cat)" \

4212 $layout/html.m4.html $layout/form+dump.m4.html

4213 }

4214 default_viewtext() { # $1=def-file

4215 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4216 default_storedb "$@"

4217 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4218 | _m4 -D_TITLE_="$tbl" \

4219 -D_FORM_="`genform $1`" \

4220 -D_DUMPTABLE_="syscmd(cat)" \

4221 $layout/html.m4.html $layout/form+dump.m4.html

4222 }

4223 default_smail() {

4224 default_storedb "$@"

4225 if [ $? -eq 2 ]; then

4226 _m4 -D_TITLE_="入力" \

4227 -D_FORM_="`genform $1`" \

4228 -D_DUMPTABLE_="" \

4229 $layout/html.m4.html $layout/form+dump.m4.html

4230 return

4231 fi

4232 cond=""

4233 for pk in `gettblpkey $tbl`; do

4234 pv=$(sqlquote "$(getpar $pk)")

4235 cond="$cond${cond:+ and }$pk=$pv"

4236 done

4237 sql="select rowid from $tbl where $cond;"

4238 rowid=`query "$sql"`

4239 ## err smail1 - "$sql" "-> rowid=$rowid"

4240

4241 while IFS=: read prompt name keytype type args; do # Read from $1

4242 val=`getpar $name`

4243 if [ -n "$val" ]; then

4244 text="$text

4245 $prompt

4246 $name=$val

4247 ---------------------------------------------------------"

4248 fi

4249 case "$type" in

4250 image|document|file)

4251 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4252 fns=$(echo "$fn"|while read fn; do

4253 err mv $tmpd/$fn.orig $tmpd/$fn

4254 mv $tmpd/$fn.orig $tmpd/$fn

4255 rm $tmpd/$fn.rowid # Remove cache flag

4256 ## err "`ls $tmpd/$fn`"

4257 echo $fn

4258 done)

4259 files="$files $fns"

4260 ;;

4261 esac

4262 done < $1

4263 ## err FILES=$files "`ls -lF $tmpd`"

4264 subj="from ${REMOTE_ADDR}"

4265 (echo "$url"

4266 echo "への書き込みがありました。"

4267 echo "------"

4268 echo "$text"

4269 ) | (cd $tmpd &&

4270 err LS="`ls -lF`" &&

4271 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4272 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4273 echo "以下の内容で送信しました。" | html p

4274 viewtable $1 $tbl \

4275 `query "select rowid from $tbl order by rowid desc limit 1;"`

4276 echo "戻る" | html a "href=\"?\""

4277 }