s4: fce9d5977822 s4-funcs.sh

s4

view s4-funcs.sh @ 721:fce9d5977822

Put strings before hexizing to smail_queue log

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sun, 31 May 2020 10:42:15 +0900
parents	093be7d30a6e
children	78cc4ba7bf42

line source

1 #!/bin/sh

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

3 # $HGid$

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

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

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

9 myargs="$@"

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

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

12 tmpdir=${TMPDIR:-tmp}

13 dbdir=${DBDIR:-db}

14 tmpfiles=""

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

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

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

18 workdb=$dbdir/tmpdata.sq3

19 sessdb=$dbdir/sess.sq3

20 sesstb=tmp.sess

21 listentlimit=${LISTENTLIMIT:-30}

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

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

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

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

26 templ=${TEMPL:-templ}

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

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

29 imgdir=${IMGDIR:-img}

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

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

32 msgdir=$templ/msg

33 timeout="+2 days"

34 memoplimitdays="7"

35 dumpcollen=22

36 #thumbxy=120x120

37 thumbxy=96x96

38 iconxy_S=80x80

39 iconxy_M=400x400

40 maximagexy=1600x1600

41 ### maximagexy=400x400

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

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

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

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

47 file_accept_help="

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

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

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

51 "

52 file_warn="$file_accept_help

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

54 blogreadflagrowid=0

55 blogcutoffflagrowid=-1

56 whatsnewdays=${WHATS_NEW_DAYS:-14}

58 tconfs=""

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

60 conftbl=_tblconf

61 nl="

62 "

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

64 iconcachekey="profimgcache_S"

65 case "$HTTP_USER_AGENT" in

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

67 *) touchpanel="" ;;

68 esac

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

71 : <<EOF

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

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

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

76 レポート提出管理

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

79 [2016]

80 7/12 根本への反省

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

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

84 [以下2015]

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

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

87 Web

88 締切設定

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

91 ○自分の提出物リスト

93 7/19 ○設置

94 ○一斉送信

95 ○getparfilename の tmpd の扱い

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

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

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

99 ○添付ファイル回収

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

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

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

103

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

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

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

107 ○レポート提出モード

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

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

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

111

112

113 7/9 ○管理者の追加

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

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

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

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

118

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

120

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

122 ○グループ検索

123

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

125

126

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

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

129

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

131 ○6/1 par2tableを triplex 対応に

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

133 →とすると一気に

134

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

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

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

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

139

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

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

142 ## 1. passwd

143 ## 2. grp

144 ## 3. grp_mem

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

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

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

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

149

150 ## ● listの定義:

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

152 ## ● hashの定義:

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

154

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

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

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

158 ## csv2sq3 で .csv.sq3 の Makefile

159

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

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

162 ## type := root | comment

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

164 ## unique(id, filename)

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

166

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

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

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

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

171 ## できたー!

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

173 ↓

174 ↓以下に変更

175 with recursive allmem as

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

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

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

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

180

181

182 with recursive allmem as

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

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

185 where allmem.val=grp_m.gname)

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

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

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

189

190

191 ## triggerもできた。

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

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

194

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

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

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

198 ## グループ管理

199 ## 個人情報管理

200 ##

201

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

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

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

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

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

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

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

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

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

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

212

213 ## ログテーブル

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

215

216

217

218 ■表設計

219 * 3つの表に分散管理

220 id格納表 + hash表 + list表

221 * *_s *_m

222

223

224

225 user, user_map, user_col

226

227 ■抽象エントリタイプ

228 * user

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

230 * group

231 権限判定に利用

232 * serial

233 自動idとして機能

234 * password

235 入力 type=passwordで入力

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

237 * session

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

239 * text

240 入力 type=text

241 * textarea

242 入力 textarea

243 * image|document

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

245 * owner

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

247 * gowner

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

249 * timestamp

250 datetime()

251 * parent

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

253 * path

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

255

256 格納タイプ

257 * list

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

259 * hash

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

261

262 オブジェクトタイプ

263 * entry

264 id, title, owner

265 * textpart

266 id, parentID, text

267 * binarypart

268 id, parentID, contenttype, filename, content

269 * content

270 hash(textpart), list(binarypart)

271 * topic

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

273 * reply

274 id, parentID, content

275 * blog

276 list(entry)

277 blog = [topic, list(reply)]

278

279

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

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

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

283 "author" => "taro",

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

285 "parent" => "/",

286 "path" => "/1",

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

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

289 {"serial" => 2,

290 "author" => "hanako",

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

292 "parent" => "/",

293 "path" => "/2",

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

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

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

297

298

299 user:=

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

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

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

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

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

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

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

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

308

309 変換表

310 /user/email=m

311

312 blog:=

313 シリアル:id:p:serial

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

315 所有者:owner:s:owner:

316 時刻:ctime:s:stamp:

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

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

319

320 article:=

321 シリアル:id:p:serial

322 筆者:author:s:owner

323 時刻:ctime:s:stamp:

324 参照元:parent:s:parent:

325 パス:path:s:path:

326 本文:text:s:textarea:

327 画像:image:m:image:

328

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

330

331

332 EOF

333

334 logstart() {

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

336 }

337 logend() {

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

339 }

340 sqlog() {

341 logstart

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

343 cat >> $querylog

344 else

345 echo "$*" >> $querylog

346 fi

347 logend

348 }

349 sq() {

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

351 logstart

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

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

354 else

355 echo "$@" >> $querylog

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

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

358 fi

359 logend

360 }

361 dbsetup() {

362 pipedir=$tmpdir/pipedir

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

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

365 suf=`date +%s`

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

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

368 mkfifo $sqi $sqo

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

370 sq $db < $sqi &

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

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

373 exec 2>> $tmpdir/error.out

374 fi

375 exec 3>> $tmpdir/debug.out

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

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

378 rm $sqi

379 # Attach supplemental DB

380 cat >&5 <<-EOF

381 .output /dev/null

382 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

386 .output stdout

387 EOF

388 }

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

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

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

392 fi

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

394 }

395 cleanup() {

396 trap '' INT HUP EXIT TERM PIPE

397 echo .quit >&5

398 kill $sq3pid

399 kill $sq3pid

400 rm -f $sqo $sqi

401 rm -rf $tmpfiles

402 cleanup2

403 }

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

405 # simple redirection for the sake of speed.

406 query() {

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

408 echo ".output $sqo" >&5

409 logstart

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

411 tee -a $querylog

412 else

413 echo "$@" >> $querylog

414 echo "$@"

415 fi >&5

416 echo ".output stdout" >&5

417 cat $sqo

418 rc=$?

419 logend

420 return $rc

421 }

422 _m4() {

423 #S4NAME=f,f,f

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

425 }

426 ismember() {

427 # $1=user, $2=group

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

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

430 }

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

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

433 }

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

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

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

437 }

438 isgrpowner() (

439 # $1=user, $2=group

440 gn=`sqlquote "$2"`

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

442 err isgrpowner: $sql

443 test -n "`query $sql`"

444 )

445 isgrpownerbygid() (

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

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

448 err isgrpownerbygid: $sql

449 test -n "`query $sql`"

450 )

451 getgroupadminmails() {

452 # $1=group

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

454 email4group "$1" "$i" ;

455 done

456 }

457 getgroupadmins() { # $1=group

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

459 qgrp=`sqlquote "$1"`

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

461 }

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

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

464 getvalbyid grp $2 \

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

466 }

467 getgroupbyid() {

468 # $1=id|gname

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

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

471 # err ggbyid: `echo $sql`

472 query $sql

473 }

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

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

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

477

478 # Return true when anonymous mode

479 [ "$anonymousmode" ] && return 0

480 # case `getvalbyid blog mode $2` in

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

482 # *closed*)

483 # owner=`getvalbyid blog owner $2`

484 # if isgrp $owner; then

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

486 # elif isuser $owner; then

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

488 # fi

489 # esac

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

491 sql="with getblog as (

492 select key,val from blog_s where id=(

493 select blogid from article where id in

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

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

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

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

498 isgrpadm as (select user from grp_adm where

499 gname=(select val from getowner) and

500 user='$1'),

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

502 select case

503 when (select author from article where

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

505 then 'author'

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

507 then 'open'

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

509 then CASE

510 WHEN (SELECT val FROM isgrp) IS NULL

511 THEN

512 CASE WHEN (SELECT val from getowner)

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

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

515 ELSE ''

516 END

517 WHEN (select user from isgrpadm) IS NOT NULL

518 THEN 'i-am-admin'

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

520 END

521 when (select val from getmode) is null

522 then 'open'

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

524 then (SELECT user FROM isgrpadm)

525 when (select author from article where

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

527 then 'user+author'

528 else '' end;"

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

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

531 # result=`query "$sql"`

532 # err FileAccessibility=$result

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

534 }

535 linkhome() {

536 # $1=UserOrGroup

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

538 if isuser $1; then

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

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

541 else

542 echo -n "grp+$1"

543 fi

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

545 }

546 hreflink() {

547 # s4 specific notation:

548 # ^href=URL

549 # ^iframe=URL

550 # ^video=URL

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

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

553 # OSM umap Wikistyle Notation:

554 # [[URL]] - Simle Link

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

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

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

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

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

560 # Other Style

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

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

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

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

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

566 # SPC+SPC+$ - <br>

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

568 checkboxON="${cb} checked>"

569 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

594

595 }

596 minitbl() {

597 sed -n '

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

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

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

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

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

603 H; # Concat this line to HoldSpace

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

605 $ b cont

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

607 }

608 :cont

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

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

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

612 # . of ^. is workaround for FreeBSD sed

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

614 p; # Print whole "table" element

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

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

617 }

618 x; # Back to the newest line

619 p; # Print rest' | miniul

620 }

621 miniul() {

622 sed -e '

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

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

625 :top

626 s/\n//;

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

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

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

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

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

632 $ b cont

633 N; # 次の行を読む

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

635 /^ /b top

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

637 p; s/.*//;

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

639 /^\* /b top

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

641 }

642

643 :cont

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

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

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

647 H; x

648 }

649 x'

650 }

651 acclog() (

652 # $1=table, $2=rowid

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

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

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

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

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

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

659 fi

660 )

661 gecos() (

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

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

664 )

665 setpar() {

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

667 case "$1" in

668 user) _user="$v" ;;

669 skey) _skey="$v" ;;

670 esac

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

672 }

673 replpar() {

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

675 }

676 getpar() {

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

678 val=""

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

680 user) val=$_user ;;

681 skey) val=$_skey ;;

682 esac

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

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

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

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

687 fi

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

689 case "$var" in

690 owner)

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

692 echo $user; return

693 elif ismember $user $val; then

694 echo $val; return

695 fi ;;

696 esac

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

698 echo "$val"

699 }

700 setskey() {

701 # For quick response...(?)

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

703 }

704 chkskey() {

705 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

711 return 0

712 fi

713 return 1

714 }

715 resetskey() {

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

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

718 fi

719 }

720 getpartype() {

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

722 }

723 getparcount() {

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

725 }

726 getparfilename() {

727 # null if type of $1 is not file

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

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

730 }

731 sqlquote() {

732 (v="$1"

733 case "$v" in

734 "") return ;; # null

735 "X'"*) # quoted hex string

736 echo $1 ;;

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

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

739 echo "\"$v\""

740 return ;;

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

742 echo "\"$v\""

743 return ;;

744 *)

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

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

747 else

748 echo "\"$v\""

749 fi ;;

750 esac)

751 }

752 sqlquotestr() (

753 case "$1" in

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

755 echo "'$v'" ;;

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

757 esac

758 )

759 mktempd() {

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

761 }

762 getcachedir() { # $1=maintable

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

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

765 else

766 echo $tmpd/$thumbxy

767 fi

768 }

769 getval() {

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

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

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

773 echo "$user" ;;

774 stamp|datetime)

775 date "+%F %T" ;;

776 serial)

777 (s=`getpar $2`

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

779 *)

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

781 esac

782 }

783

784 getvalquote() {

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

786 (v=`getval "$@"`

787 case "$v" in

788 "") echo NULL ;;

789 *) sqlquote "$v" ;;

790 esac)

791 }

792 getparquote() {

793 sqlquote "`getpar $1`"

794 }

795 getbinbyid() {

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

797

798 }

799 getvalbyid() {

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

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

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

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

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

805

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

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

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

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

810 return

811 fi

812 done

813 rowid=$3

814 pk=`gettblpkey $1`

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

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

817 td=${4:-$tmpd}

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

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

820 for kt in s m; do

821 t=${1}_$kt

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

823 vcount=1 # count(val)

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

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

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

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

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

829 if [ $kt = m ]; then

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

831 # Reset val to store filenames if type is string

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

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

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

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

836 i=0

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

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

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

840 i=$((i+1))

841 fn=$c.$i

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

843 case $type in

844 file:*)

845 #file=$td/$val

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

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

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

849 # FOR SPEED: Skip file generation if imgcache exists

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

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

852 && continue

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

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

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

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

857 select rowid from $cond $slice;

858 .output '$td/$fn'

859 select val from $cond $slice;

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

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

862 .output stdout

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

864 EOF

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

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

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

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

869 ls -lh "$file" |

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

871 case "$type" in

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

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

874 *|*:[Aa]pplication*)

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

876 png:- > "$file"

877 ;;

878 esac

879 ;;

880 *)

881 sq $db<<EOF

882 .output $td/$fn.rowid

883 select rowid from $cond $slice;

884 .output $td/$fn

885 select val from $cond $slice;

886 EOF

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

888 ;;

889 esac

890 done

891 else

892 rm -f $td/$c.count

893 case $type in

894 file:*)

895 echo "$val" \

896 | while read fn; do

897 file=$td/$fn

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

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

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

901 | unhexize > "$file"

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

903 case $type in

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

905 *:[Aa]pplication*)

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

907 png:- > $file ;;

908 esac

909 fi

910 done

911 ;;

912 esac

913 fi

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

915 return

916 fi

917 done

918 done)

919 }

920 getvalbypkey() (

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

922 pk=`gettblpkey $1`

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

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

925 )

926 getvalbycond() {

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

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

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

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

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

932 fi

933 }

934 getpwfield() {

935 # getpwfield user column

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

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

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

939 echo "$val"

940 return 0

941 else

942 return 1

943 fi

944 }

945 numericalize() {

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

947 }

948 encode() {

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

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

951 sha1=sha1

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

953 sha1=sha1sum

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

955 sha1=gsha1sum

956 fi

957 fi

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

959 }

960 enjpeg() {

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

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

963 cjpeg="cjpeg"

964 else

965 cjpeg="convert - jpeg:-"

966 fi

967 fi

968 $cjpeg "$@"

969 }

970 mycrypt() (

971 key=$1 salt=$2

972 # err \$2=$2

973 case $2 in

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

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

976 esac

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

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

979 )

980 hexize() {

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

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

983 hexize="xxd -p"

984 else

985 hexize_hd() {

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

987 }

988 hexize="hexize_hd"

989 fi

990 fi

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

992 }

993 unhexize() {

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

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

996 unhex="xxd -p -r"

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

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

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

1000 EOF

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

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

1003 # world writable...:)

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

1005 fi

1006 fi

1007 cat "$@" | $unhex

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

1009 }

1010 percenthex() {

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

1012 }

1013 htmlescape() {

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

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

1016 }

1017 enascii() {

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

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

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

1021 else

1022 enascii_now=`date +%FT%T`

1023 enascii_sed() {

1024 nkf -Z0Z1Z2 \

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

1026 }

1027 enascii="enascii_sed"

1028 fi

1029 fi

1030 cat "$@" | $enascii

1031 }

1032 size_h() {

1033 i="$1" oi=$1

1034 set -- B B KB MB GB TB

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

1036 oi=$i

1037 i=$((i/1024))

1038 shift

1039 done

1040 echo ${oi}$1

1041 }

1042 gettblconf() {

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

1044 ## tconfs=`sq $db \

1045 tconfs=`query \

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

1047 fi

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

1049 }

1050 gettblkeys() {

1051 # $1=tbl

1052 gettblconf

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

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

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

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

1057 case $type in

1058 *p*)

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

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

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

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

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

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

1065 esac

1066 case $type in

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

1068 esac

1069 done

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

1071 }

1072 gettblpkey() {

1073 # $1=tbl

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

1075 }

1076 gettblfkey() {

1077 (x=`gettblkeys $1`

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

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

1080 }

1081 gettblcols() {

1082 (x=`gettblkeys $1`

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

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

1085 }

1086 gettbl_s_cols() {

1087 (x=`gettblkeys $1`

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

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

1090 }

1091 gettbl_m_cols() {

1092 (x=`gettblkeys $1`

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

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

1095 }

1096 gettbl_h_cols() {

1097 (x=`gettblkeys $1`

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

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

1100 }

1101 gettbl_coltype() (

1102 gettblconf

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

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

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

1106 )

1107 is_hidden() {

1108 # $1=Tbl $2=col

1109 gettblconf

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

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

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

1113 case $x in

1114 *h*) return 0 ;;

1115 *) return 1 ;;

1116 esac

1117 }

1118

1119 dbsetbyid() {

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

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

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

1123 quotedp=$(sqlquotestr "$p")

1124 unset primary update

1125 gettblconf

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

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

1128 #err conf=$conf

1129 case ${conf#*=} in

1130 p*) primary=1 ;;

1131 f*) update=1 ;;

1132 u*) ;;

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

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

1135 esac

1136 #err t=$t

1137 type=string fn=""

1138 case $conf in

1139 */password)

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

1141 ;;

1142 */image*|*/document*)

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

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

1145 ;;

1146 esac

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

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

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

1150 if [ "$primary" ]; then

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

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

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

1154 elif [ "$update" ]; then

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

1156 else

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

1158 fi

1159 )

1160 }

1161 expire() (

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

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

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

1165 )

1166 addsession() {

1167 # expireをセット

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

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

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

1171 # Remove old session parameters

1172 now=`expire now`

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

1174 }

1175 gencookie() (

1176 for kv; do

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

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

1179 done

1180 )

1181 contenttype() {

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

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

1184 }

1185 putheader() {

1186

1187 }

1188 putfooter() {

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

1190 }

1191 getcookie() {

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

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

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

1195 case "$k" in

1196 user) _user="$v" ;;

1197 skey) _skey="$v" ;;

1198 esac

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

1200 done

1201 }

1202 genrandom() {

1203 # $1=columns (default: 10)

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

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

1206 }

1207 genserial() {

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

1209 }

1210 smail() {

1211 # smail rcpts subj (file)

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

1213 # $MAIL_FROM <- From: header value

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

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

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

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

1218 sender=${SENDER:-$admin}

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

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

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

1222 }

1223 smail_queue_flush() {

1224 # $1=timelimit

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

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

1227 SELECT rowid FROM smailq

1228 GROUP BY rcpts, subj

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

1230 EOF

1231 )

1232 for rid in $rowids; do

1233 sq $workdb \

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

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

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

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

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

1239

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

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

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

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

1244 SELECT time, text FROM smailq

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

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

1247 ORDER by time;

1248 EOF

1249 then

1250 echo rowid=$rid

1251 cat <<-EOF | sq $workdb

1252 DELETE FROM smailq

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

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

1255 EOF

1256 fi

1257 fi

1258 done

1259 }

1260 smail_queue() {

1261 # $1=Rcpts, $@=subj

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

1263 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1272 EOF

1273 )

1274 ## XXX: Adhoc load mitigation

1275 case "$now" in

1276 *[01])

1277 err flush_queue=$mintime

1278 smail_queue_flush ;;

1279 esac

1280 fi

1281 }

1282 setviastring() {

1283 table=par

1284 oifs="$IFS"

1285 IFS="&"

1286 for us in $1; do

1287 k=${us%%=*}

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

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

1290 done

1291 IFS="$oifs"

1292 }

1293 checkdomain() (

1294 # Check the validity of domain by referring DNS

1295 item=$1

1296 err checkdomain $1

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

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

1299 )

1300 pwcheck() {

1301 # $1=passwd

1302 dbpswd=`getpwfield $user pswd`

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

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

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

1306 }

1307 mypwhash() {

1308 mycrypt "`cat`" `genrandom 5`

1309 }

1310 wasureta() {

1311 user=$1

1312 if ! checkdomain $user; then

1313 contenttype; echo

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

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

1316 putfooter

1317 exit 0

1318 fi

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

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

1321 encpswd=`echo $newpswd|mypwhash`

1322 dbsetbyid user $user pswd "$encpswd"

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

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

1325 $msgdir/mail-newaccount.m4 \

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

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

1328 }

1329 checkauth() {

1330 user=`getpar user`

1331 skc=`getpar skey` # from cookie

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

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

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

1335 if chkskey "$skc"; then

1336 return 0

1337 fi

1338 fi

1339 pswd=`getpar pswd`

1340 quser=`sqlquotestr "$user"`

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

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

1343 return 4 # 4=server too heavy

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

1345 return 2 # 2=login fail

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

1347 wasureta "$user"

1348 return 1 # wasureta error

1349 fi

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

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

1352 if pwcheck "$pswd"; then

1353 newsession=`genrandom 34`

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

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

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

1357 return 0

1358 else

1359 return 4 # Heavy load??

1360 fi

1361 fi

1362 return 2 # Password mismatch

1363 }

1364 showlogin() {

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

1366 test -z "$args" && resetskey

1367 s4name=${S4NAME:-s4}

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

1369 cat $layout/login.m4.html

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

1371 ) | _m4 \

1372 -D_BODYCLASS_="login" \

1373 -D_TITLE_="$s4name" \

1374 -D_SYSNAME_="Welcome to $s4name" \

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

1376 exit 0

1377 }

1378 dologin() {

1379 checkauth

1380 st=$?

1381 if [ $st != 0 ]; then

1382 contenttype; echo

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

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

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

1386 showlogin # and EXIT

1387 fi

1388 }

1389

1390 # Do instant jobs here

1391 dbsetup

1392 trap cleanup INT HUP EXIT TERM PIPE

1393 # trap cleanup INT HUP

1394

1395 err() {

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

1397 }

1398

1399 cgiinit() {

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

1401 tmpf=tmp/stream

1402 tmpd=`tmpd=$tmpdir mktempd`

1403 tmpfiles=$tmpfiles" $tmpd"

1404 addsession $session

1405 getcookie

1406 case "$REQUEST_METHOD" in

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

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

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

1410 # cat > $tmpf # too much?

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

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

1413 s="`cat tmp/stream`"

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

1415 ;;

1416 esac

1417 case "$CONTENT_TYPE" in

1418 *boundary*)

1419 bndry=${CONTENT_TYPE#*boundary=}

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

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

1422 do

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

1424 #echo u=$us

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

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

1427 # err k=$k v=$v

1428 case "$k" in

1429 *:filename)

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

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

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

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

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

1435 :

1436 fi

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

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

1439 case "$mimetype" in

1440 [Ii]mage/x-xcf)

1441 bzip2 "$tmpd/$v"

1442 v=${v}.bz2

1443 ;;

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

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

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

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

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

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

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

1451 v=$vjpg

1452 else

1453 mimetype="Not supported"

1454 fi

1455 ;;

1456 [Ii]mage/*)

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

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

1459 ;;

1460 esac

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

1462 then

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

1464 continue

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

1466 replpar text string \

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

1468 continue

1469 fi

1470 ;;

1471 *)

1472 type='string'

1473 ;;

1474 esac

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

1476 setpar "$k" "$type" "$v"

1477 done

1478 ;;

1479 *)

1480 setviastring "$s"

1481 ;;

1482 esac

1483 }

1484 email4group() {

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

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

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

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

1489 sql="WITH

1490 grpemails AS (

1491 SELECT gname, user, val email

1492 FROM grp_mem NATURAL JOIN grp_mem_s

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

1494 useremails AS (

1495 SELECT user.name, val email

1496 FROM user

1497 LEFT JOIN user_m

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

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

1500 FROM useremails u LEFT JOIN grpemails g

1501 ON u.name=g.user

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

1503 query "$sql"

1504 }

1505 email4groupbyuid() {

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

1507 g=$1; shift

1508 uids=`echo "$@"`

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

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

1511 email4group "$g" `query "$sql"`

1512 }

1513 myemail4group() {

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

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

1516 }

1517 collectmembersbyid() {

1518 # Collect user names of group specified by grid

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

1520 query "SELECT user FROM grp_mem \

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

1522 }

1523 collectmembersbyid() {

1524 # Collect user names of group name

1525 qgrp=`sqlquote "$1"`

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

1527 }

1528 collectgecosesbyid() {

1529 # Collect user gecoses of group

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

1531 query<<-EOF

1532 SELECT gecos

1533 FROM gecoses

1534 WHERE name IN (SELECT user FROM grp_mem

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

1536 EOF

1537 }

1538 collectemail() (

1539 # Collect email addresses for group $1

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

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

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

1543 for e; do

1544 if isuser "$e"; then

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

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

1547 else

1548 qgrp=`sqlquote "$e"`

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

1550 gmem="grp_mem"

1551 else

1552 tm=`sqlquote "$TEAM"`

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

1554 fi

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

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

1557 $gmem g left join grp_mem_s s

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

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

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

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

1562 query "$sql"

1563 fi

1564 done

1565 )

1566 sendinvitation() (

1567 # $1=email

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

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

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

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

1572 gecos=`gecos`

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

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

1575 _m4 -D_URL_="$urlbase" \

1576 -D_USER_="$name" \

1577 -D_EMAIL_="$1" \

1578 -D_REGIST_="$regist" \

1579 -D_ADMIN_="$admin" \

1580 $msgdir/mail-invite.m4 \

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

1582 return 0

1583 )

1584 emaildomaincheck() {

1585 case "$1" in

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

1587 *@*)

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

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

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

1591 return 2

1592 fi

1593 return 0

1594 ;;

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

1596 esac

1597 }

1598 invite() {

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

1600 case "$email" in

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

1602 *@*)

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

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

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

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

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

1608 elif sendinvitation $email; then

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

1610 else # Cannot be reached here

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

1612 fi ;;

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

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

1615 esac

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

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

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

1619 fi

1620 if [ -f $invite_policy ]; then

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

1622 else

1623 pol="$invite_policy"

1624 fi

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

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

1627 -D_INVITE_POLICY_="$pol" \

1628 $layout/html.m4.html $layout/invite.m4.html

1629 }

1630 regist() {

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

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

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

1634 echo "bye bye" | html p

1635 reutrn

1636 fi

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

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

1639 cat<<EOF

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

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

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

1643 EOF

1644 return

1645 fi

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

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

1648 # Fake login password to wasureta

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

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

1651 wasureta $email

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

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

1654 addsession $1 # for removal after 1 minute

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

1656 $layout/login.m4.html

1657 return

1658 }

1659 group_safename() {

1660 # Convert $1 to safe group name

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

1662 }

1663 groupupdate() {

1664 gname=`getpar gname`

1665 qgname=`sqlquote "$gname"`

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

1667 # See ALSO same job in showgroup()

1668 newgname=`group_safename "$gname"`

1669 err newgname=$newgname

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

1671 err NewGNAME: gname=$newgname

1672 gname=$newgname

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

1674 replpar gname string "$gname"

1675 fi

1676 # Name confliction check

1677 parow=`getpar rowid`

1678 ## err parow=$parow

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

1680 query "BEGIN EXCLUSIVE;"

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

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

1683 if [ $count -gt 0 ]; then

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

1685 query "END;"

1686 return

1687 fi

1688 par2table $formdir/grp.def

1689 query "END TRANSACTION;"

1690 # Remove orphan

1691 : <<EOF

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

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

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

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

1696 EOF

1697 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1701 sql="delete from blog where id in

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

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

1704 query "$sql";

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

1706 return # and return

1707 fi

1708 fi

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

1710 fi

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

1712 grid=$(query $sql)

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

1714 grp $grid

1715 }

1716 groupclone() {

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

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

1719 case "$1" in

1720 */noteam)

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

1722 esac

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

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

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

1726 return

1727 fi

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

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

1730 return

1731 fi

1732 i=0

1733 while true; do

1734 copy="-copy$i"

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

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

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

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

1739 i=$((i++))

1740 continue

1741 fi

1742 break

1743 done

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

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

1746 query<<-EOF

1747 BEGIN;

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

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

1750 SELECT $newqname, key, val

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

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

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

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

1755 -- Copy members and their configuration --

1756 REPLACE INTO grp_mem SELECT $newqname, user

1757 FROM grp_mem WHERE gname=$qgrp;

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

1759 FROM grp_mem_s WHERE gname=$qgrp;

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

1761 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1762 -- Copy administrators --

1763 REPLACE INTO grp_adm SELECT $newqname, user

1764 FROM grp_adm WHERE gname=$qgrp;

1765 COMMIT;

1766 EOF

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

1768 STOPCLONEMSG=1 groupconf "$newrowid"

1769 }

1770 groupman() {

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

1772

1773 GF_STAGE="grpconf"

1774 GF_STAGE=groupupdate

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

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

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

1778 -D_DUMPTABLE_="syscmd(cat)" \

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

1780 }

1781 userconf() {

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

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

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

1785 }

1786 groupconf() {

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

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

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

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

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

1792

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

1794 grp=`getgroupbyid $rowid`

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

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

1797 return

1798 fi

1799

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

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

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

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

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

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

1806 <div>

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

1808 <table>

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

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

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

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

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

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

1815 </table>

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

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

1818 </div>

1819 EOF

1820 fi

1821 }

1822 mems() {

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

1824 kwd=`getpar kwd`

1825 listmember $kwd

1826 }

1827 grps() {

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

1829 kwd=`getpar kwd`

1830 listgroup $kwd \

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

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

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

1834 $layout/form+dump.m4.html

1835 }

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

1837 gpg=`getpar grp`

1838 grid=${1:-$gpg}

1839 grp=`getgroupbyid "$grid"`

1840 ## . ./s4-blog.sh

1841 jg=`getpar joingrp`

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

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

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

1845 fi

1846 htmlheader=$layout/html.m4.html

1847 showgroup "$grid"

1848 }

1849 sql4interestblogs() {

1850 cat<<EOF

1851 CREATE TEMPORARY VIEW interestblogs AS

1852 SELECT blog.rowid rid, id, author

1853 FROM blog

1854 NATURAL JOIN

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

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

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

1858 WHEN (SELECT user FROM grp_mem

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

1860 THEN 0

1861 ELSE 1

1862 END;

1863 EOF

1864 }

1865 listnewblogsql() { # $1=user

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

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

1868 newdays=${newdays:-14}

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

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

1871 WHERE user='$user'

1872 AND tblrowid IN

1873 ($blogreadflagrowid,

1874 $blogcutoffflagrowid)),

1875 $basetime -- "0"

1876 );"`

1877 cat<<EOF

1878 `sql4interestblogs`

1879 WITH article_ctime as (

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

1881 FROM article join article_s s using(id)

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

1883 GROUP BY id

1884 ), blog_title_owner as (

1885 SELECT blg.rid brid, id,

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

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

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

1889 ), blogall as (

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

1891 ), news as (

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

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

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

1895 FROM blogall bl

1896 LEFT JOIN

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

1898 ON bl.brid=al.tblrowid

1899 WHERE atime < bl.ctime

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

1901 LIMIT 10

1902 ) SELECT brid LINK, "新着",

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

1904 ctime, title,

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

1906 FROM news;

1907 EOF

1908 }

1909

1910 search_form() {

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

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

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

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

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

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

1917 @week → 最近一週間

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

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

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

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

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

1923 auth=""

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

1925 case "$1" in

1926 author=*)

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

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

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

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

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

1932 ;;

1933 grp=*)

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

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

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

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

1938 ;;

1939 esac

1940 inikwd="$2" # no need to htmlescape

1941 cat<<-EOF

1942 <div class="right">

1943 <form action="$myname">$auth

1944 <input type="text" name="kwd" value="$inikwd" title="$help"

1945 placeholder=" $placeholder " width="10" accesskey="k">

1946

1947 ${touchpanel:+<p class="help">$help</p>}

1948 <input type="hidden" name="stage" value="searchart">

1949

1950 </form>

1951 </div>

1952 EOF

1953 }

1954

1955 imgsrc_cache() (

1956 # $1 = directory for cache'ing

1957 # $2 = table (user_m or grp_m)

1958 # $3 = keycond (was: condition for choosingowner)

1959 # $4 = size : S = Small, M = Medium, O = Original

1960 dir="$1" tbl="$2"

1961 keycond="$3"

1962 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

1963 ORDER BY rowid DESC LIMIT 1"

1964 [ -d "$dir" ] || mkdir -p "$dir"

1965 tmpf=$tmpd/imgsrc_cache.$$

1966 case "$4" in

1967 [Ss]) size=S ;;

1968 [Oo]) size=O ;;

1969 *) size=M ;;

1970 esac

1971 # ImageCache filename storing schema:

1972 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

1973 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

1974 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

1975 valtype=`query "$sql0"`

1976 filename=${valtype%%//*}

1977 filetype=${valtype##*//file:}

1978 if [ x"$filename" = x"${filename%.*}" ]; then

1979 # If nor filename extension found, set it to image type

1980 case "$filetype" in

1981 image/*) filename=$filename.${filetype#image/} ;;

1982 esac

1983 fi

1984 cacheimg_S=$dir/S_$filename

1985 cacheimg_M=$dir/M_$filename

1986 cacheimg_O=$dir/$filename

1987 cacheimg=$dir/${size}_$filename

1988 sumfile="$dir/$filename.sum"

1989 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

1990 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

1991 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

1992 # if cache is fresh and has the same checksum,

1993 echo "<img src=\"$cacheimg\">"

1994 else

1995 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

1996 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

1997 fifo2=$fifo.2

1998 mkfifo $fifo $fifo2

1999 fmt=${filename##*.}

2000 ## [[ NOTE ]]

2001 ## a. convert oldimage newimage

2002 ## b. convert oldimage fmt:- | convert - newimage

2003 ## b is much smaller than a

2004 cat $tmpf | unhexize \

2005 | tee $fifo \

2006 | convert -define ${fmt}:size=${iconxy_M} \

2007 -resize ${iconxy_M}'>' - ${fmt}:- \

2008 | tee $fifo2 \

2009 | convert - "$cacheimg_M" &

2010 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2011 -resize ${iconxy_S}'>' - ${fmt}:- \

2012 | convert - "$cacheimg_S" &

2013 printf '%s' "<img src=\"data:${filetype},"

2014 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2015 echo '">'

2016 echo "$sum" > $sumfile

2017 fi

2018 ## Now preparing cache image, done.

2019 ## Store this information to DB

2020 stbl=${tbl%_m}_s # user_s or grp_s

2021 pkey=${keycond%%=*} # Primary Key name

2022 pval=${keycond#*=} # Primary Key value

2023 query <<-EOF

2024 REPLACE INTO $stbl($pkey, key, type, val)

2025 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2026 EOF

2027 )

2028

2029 showhome() {

2030 # $1=userRowIdToShow

2031 err showhome \$1=$1

2032 case "$1" in

2033 *@*) uname=`getvalbypkey user name "$1"` ;;

2034 *) uname=`getvalbyid user name $1` ;;

2035 esac

2036 ## err ShowHome: uname=$uname

2037 td=`getcachedir home/"$1"`

2038 gecos=`gecos "$uname"`

2039 ## err SH:gecos=$gecos

2040 GF_VIEWONLY=1

2041 cond="gname in (select gname from grp_mem where user='$uname')"

2042 search_form_args=""

2043 if [ x"$user" = x"$uname" ]; then

2044 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2045 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> /

2046 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2047 # Display folders

2048 sql="select count(id) from article_m where id

2049 in (select id from article where author='$user')

2050 and type like 'file:%';"

2051 ## err nfile-sql=`echo "$sql"`

2052 nfile=`query "$sql"`

2053 # err nfile=$nfile

2054 if [ $nfile -gt 0 ]; then

2055 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2056 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2057 fi

2058 else

2059 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2060 GROUP BY user;"`

2061 usermenu="<p>Last seen on $latestlog</p>"

2062 search_form_args="author=$uname"

2063 fi

2064 . ./s4-blog.sh

2065

2066 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2067 search_form "$search_form_args" > $sf

2068 printf "%s さん" "$gecos" > $tf

2069 { echo "<div class=\"noprofimg\">"

2070 viewtable $formdir/user.def user $1

2071 echo "</div>"

2072 } > $pf

2073

2074 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2075 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2076 imf=$tmpd/profimg.$$; touch $imf

2077 if [ -n "$img" ]; then

2078 if true; then

2079 tbl=user_m

2080 enticond="name='$uname'"

2081 imgsrc_cache "$td/main" user_m "$enticond" M

2082 else

2083 { printf '%s' "<IMG src=\"data:${img#file:},"

2084 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2085 | sed 's/$..$/%\1/g'

2086 echo '">'

2087 }

2088 fi > $imf

2089 fi

2090 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2091 val='$uname';"`

2092 ## REMOVE This comment block until 2019/7/1

2093 ## err "----- `gdate +%FT%T.%3N` ------------C"

2094 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2095 listblog $uname > $bf

2096 ## unset ddd

2097 ## err "----- `gdate +%FT%T.%3N` ------------D"

2098

2099 hometail=$tmpd/tail.$$

2100 mkfifo $hometail

2101

2102 #Calling listgroupbytable, originally here

2103

2104 (

2105 # Display Most Recent Entry

2106 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2107 shortval=${shortval:-val}

2108

2109 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2110 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2111 ctime,

2112 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2113 (SELECT gecos FROM gecoses

2114 WHERE name=(SELECT val FROM blog_s

2115 WHERE key='owner' AND id=b.id)) owner,

2116 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2117 FROM blog b

2118 JOIN

2119 (SELECT distinct blogid, a.id aid, max(val) ctime

2120 FROM article a, article_s s

2121 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2122 GROUP BY blogid ORDER BY val DESC LIMIT 50

2123 ) m

2124 ON b.id=m.blogid;"

2125 # This should be as follows

2126 : <<EOF

2127 WITH arts AS(

2128 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2129 a.blogid, a.id id, s.val ctime

2130 FROM article a NATURAL JOIN article_s s

2131 WHERE s.key = 'ctime' AND a.author='$user'

2132 GROUP by s.id

2133 )

2134 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2135 FROM arts a0

2136 JOIN

2137 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2138 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2139 ORDER BY ctime DESC LIMIT 50;

2140 EOF

2141

2142 cat<<-EOF

2143 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2144 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2145 <div class="lcto">

2146 `DT_VIEW=replyblog dumptable html blog`

2147 </div>

2148 EOF

2149 unset DT_SQL

2150 if [ x"$user" = x"$uname" ]; then

2151 # Display NEWS

2152 # 2016-06-26

2153 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2154 acclog blog $blogreadflagrowid

2155 # echo "全部既読にしました" | html p

2156 fi

2157 # 2016-02-19 Counting NEWS without using dumptable.

2158 sql=`listnewblogsql "$user"`

2159 # echo "$sql" > tmp/listnew

2160 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2161 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2162 cont=$((cont-1))

2163 err newcount=$cont

2164 if [ $cont -gt 0 ]; then

2165 #echo "全体の新着記事${cont}傑" | html h2

2166 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2167 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2168 cat<<-EOF | html form 'action="?home"'

2169 `cgi_checkbox readchk yes 'id="read"'`<label

2170 for="read">新着ふくめて全部読んだことにする</label>

2171 `cgi_submit '確定'`

2172 `cgi_hidden read yes`

2173 EOF

2174 echo "$new10 "

2175 echo "</div>"

2176 else # If news is 0, set log cut off flag

2177 acclog blog $blogcutoffflagrowid # for speed

2178 fi

2179 else # Not My Home ($user != $uname)

2180 : # DT_SQL=

2181 fi

2182 ) > $hometail & # Is background call safe to m4??

2183 #

2184 listgroupbytable $formdir/grp.def $cond |

2185 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2186 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2187 -D_PROFIMG_="spaste(\`$imf')" \

2188 -D_BLOGS_="spaste(\`$bf')" \

2189 -D_SEARCH_="spaste(\`$sf')" \

2190 -D_NBLOG_="$nblog" \

2191 -D_GROUPS_="syscmd(\`cat')" \

2192 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2193 $layout/html.m4.html $layout/home.m4.html

2194

2195 # Record access log

2196 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2197 }

2198 commission() { # $1=grp-rowid $2=user-rowid

2199 contenttype; echo

2200 ## err commission: "$@"

2201 gname=`getgroupbyid $1`

2202 echo "グループ $gname 管理者委任" \

2203 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2204 if [ -n "$2" ]; then

2205 grp_reg_adm "$@"

2206 else

2207 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2208 fi

2209 }

2210 listgroupbytable() {

2211 # $1=deffile $2...=condition

2212 tagline=`grep :tag: $1`; shift

2213 and="${1:+and }" where=${1:+where }

2214 href="<a href=\"$myname?grp+"

2215 echo '<div class="listgroup">'

2216 NGsql="select distinct tag from\

2217 (select gname, max(case key when 'tag' then val end) as tag, \

2218 max(case key when 'ctime' then val end) as ctime\

2219 from grp_s group by gname order by ctime);"

2220 sql="select val from grp_s where key='tag' $and$* group by val;"

2221 ## err ListGRP: query sql="$sql"

2222 for tag in `query "$sql"`

2223 do

2224 ## err ListGrp: tag=$tag

2225 tn=${tagline%%=${tag}*}

2226 tn=${tn##*[ :]}

2227 sql="select rowid||':'||gname as 'グループ名',説明 from

2228 (select (select rowid from grp g where g.gname=grp_s.gname)

2229 as rowid,

2230 gname,

2231 max(case key when 'gecos' then val end) as '説明',

2232 max(case key when 'tag' then val end) as 'tag',

2233 max(case key when 'mtime' then val end) as mtime from grp_s

2234 $where$* group by gname having tag='$tag' order by mtime desc);"

2235 ## err PersonalGroupList= `echo $sql`

2236 echo "<h2>$tn</h2>"

2237 echo '<table class="b listgroup">'

2238 sq -header -html $db "$sql" \

2239 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2240 echo '</table>'

2241 done

2242 echo '</div>'

2243 }

2244 iconhref() (

2245 # $1=icon-file, $2=Href $3=title $4...=anchor

2246 data=`percenthex "$1"`

2247 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2248 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2249 href=$2; title=$3; shift 3

2250 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2251 )

2252 iconhref2() (

2253 # $1=icon-file, $2=Href $3=title $4...=anchor

2254 src=$1

2255 href=$2; title=$3; shift 3

2256 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2257 )

2258 listentry() (

2259 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2260 # Referring variable $iamowner=$grp to attach owner-request links

2261 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2262 cond='' hiddens=''

2263 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2264 if [ -z "$offset" ]; then

2265 offset=`getpar start`; offset=${offset%%[!0-9]*}

2266 offset=$((offset-1))

2267 fi

2268 offset=$((offset + 0)) # change to numeric forcibly

2269 [ $offset -lt 0 ] && offset=0

2270 limit=$listentlimit

2271 dir=`getcachedir "$1"`

2272 if [ x"$1" = x"user" ]; then

2273 hrb="$myname?home"

2274 deficon=person-default.png

2275 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2276 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2277 gcs=gecos

2278 else # if group

2279 hrb="$myname?grp"

2280 deficon=group-default.png

2281 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2282 gcs=name

2283 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2284 if [ -n "$tagline" ]; then

2285 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2286 ## err tagconv=$tagconv

2287 fi

2288 fi

2289 if [ ! -d $dir ]; then

2290 mkdir -p $dir

2291 fi

2292 if [ ! -s $dir/$deficon ]; then

2293 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2294 fi

2295 if [ -n "$2" ]; then

2296 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2297 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2298 fi

2299 tag=`getpar tag` tag2=`getpar tag2`

2300 if [ x"$tag" = x"NULL" ]; then

2301 tag="" tag2=""

2302 fi

2303 if [ -n "$tag$tag2" ]; then

2304 tag=${tag:-$tag2}

2305 qtag=`sqlquote "$tag"`

2306 cond2="tag=$qtag"

2307 fi

2308 if [ -n "$cond1$cond2" ]; then

2309 cond="$cond1${cond2:+ AND $cond2}"

2310 cond="WHERE ${cond# AND }"

2311 fi

2312

2313 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2314 # grpは呼出し元の動的スコープ変数でよくないな...

2315 ##qgrp=`sqlquote $grp`

2316 getgrp="(select gname from grp where rowid=${rowid:--1})"

2317 sql="select a.rowid, a.$link,

2318 coalesce(b.$gcs, a.$nm) as nick,

2319 quote(a.$nm) as qname,

2320 (SELECT val FROM ${tbl}_s

2321 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2322 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2323 || case when a.$nm in (select gname from grp)

2324 then printf('(%d名)',

2325 (select count(user) from grp_mem where gname=a.$nm))

2326 else ' <'||a.$nm||'>'

2327 end

2328 as name,

2329 b.tag,

2330 case when a.$nm in (select user from grp_adm

2331 where gname=$getgrp) then '管理者'

2332 when '$user' in (select user from grp_adm where gname=a.$nm)

2333 then 'ADMIN'

2334 when '$user' in (select user from grp_mem where gname=a.$nm)

2335 then 'Member'

2336 when '$iamowner' = '' then ''

2337 else ',not='||a.rowid end as ownerlink,

2338 CASE '$entity'

2339 WHEN 'グループ'

2340 THEN coalesce(

2341 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2342 'open')

2343 ||

2344 CASE WHEN '$user'

2345 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2346 THEN ' ismember'

2347 ELSE ''

2348 END

2349 ELSE 'user'

2350 END regmode

2351 from $tbl a left join

2352 (select $nm as name,

2353 max(case key when 'gecos' then val end) as gecos,

2354 max(case key when 'tag' then val end) as tag,

2355 max(case key when 'mtime' then val end) as mtime,

2356 max(case key when 'wtime' then val end) as wtime,

2357 max(case key when 'login' then val end) as login

2358 from ${tbl}_s group by $nm)

2359 b on a.$nm=b.name $cond $3

2360 order by b.wtime desc, b.login desc,

2361 b.mtime desc, b.tag desc, a.rowid asc"

2362 # Give precedence to newer maintained groups (2016-09-24)

2363 # Note that mtime is stored only in grp_s.

2364 ## err LE:sql.1="$sql"

2365 total=`query "with x as ($sql) select count(*) from x;"`

2366 echo "${entity} 一覧" | html h2

2367 echo '<div class="listentry">' # List-entry div

2368 # Show owner/member filter button

2369 METHOD=GET

2370 hiddens="$hiddens

2371 `cgi_hidden kwd \"$kwd\"`

2372 `cgi_hidden stage \"$stage\"`"

2373 if [ x"$tbl" = x"grp" ]; then

2374 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2375 fh="<select name=\"tag\">$nl"

2376 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2377 for l in $args; do

2378 val=${l#*=} tname=${l%=*}

2379 if [ x"$val" = x"$tag" ]; then

2380 s=" selected"

2381 selectedtags="(種別[${tname}]のみ)"

2382 else

2383 s=""

2384 fi

2385 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2386 done

2387 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2388 cat<<-EOF

2389 <form action="$myname" method="$METHOD">

2390 </form>

2391 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2392 for="ismembtn">参加中以外隠す</label>

2393 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2394 for="isadmbtn">管理者参加以外隠す</label>

2395 EOF

2396 # limit=3

2397 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2398 fi

2399 if [ $total -gt $limit ]; then

2400 echo '<div>'

2401 METHOD=GET cgi_form $stage <<EOF

2402 $form

2403 <label>次の語を含む${entity}で検索:

2404 `cgi_text kwd "$kwd"`</label>

2405 $hiddens

2406 EOF

2407 echo '</div>'

2408 else

2409 echo $selectedtags | html p

2410 fi

2411 cat<<EOF

2412 <form action="$myname" method="$METHOD">

2413 <p>${total}件中の<input class="hidesub" type="text" name="start"

2414 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2415 <input type="submit" value="確定"></p>

2416 </form>

2417 EOF

2418 if [ $((offset+limit)) -lt $total ]; then

2419 nextbtn=$(

2420 cat<<EOF

2421 <div class="right clear"><form action="$myname" method="$METHOD">

2422 `cgi_submit 次の${limit}件`

2423 $hiddens

2424 `cgi_hidden offset $((offset + limit))`</form></div>

2425 EOF

2426 )

2427 fi

2428 if [ $offset -gt 0 ]; then

2429 prevbtn=$(

2430 cat<<EOF

2431 <form action="$myname" method="$METHOD">

2432 `cgi_submit 前の${limit}件`

2433 $hiddens

2434 `cgi_hidden offset $((offset - limit))`</form>

2435 EOF

2436 )

2437 fi

2438 pnbtn="$nextbtn$prevbtn"

2439 echo $pnbtn

2440

2441 ## err ListEntry: `echo "$sql"\;`

2442 # sq $db here??? 2016-11-28

2443 query "$sql limit $limit ${offset:+offset $offset};" \

2444 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2445 # err name=$name owner=$ownerp lnk=$lnk

2446 # err newlnk=$lnk regmode=$regmode

2447 icondir=$dir/$id

2448 # Pick up only last icon

2449 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2450 <p class=\"tag _$tag\">$tag</p>" \

2451 | _m4 $tagconv

2452 if [ -n "$NOSPEEDUP" ]; then

2453 files=`getvalbyid $tbl profimg $id $icondir`

2454 if [ -n "$files" ]; then

2455 icon=`echo "$files"|tail -1`

2456 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2457 else

2458 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2459 fi

2460 elif [ -n "$icon" -a -s "$icon" ]; then

2461 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2462 else

2463 cond="$nm=$qname"

2464 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2465 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2466 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2467 # err "img=[$img]"

2468 if [ -n "$img" ]; then

2469 echo "<a href=\"$hrb+$lnk\">"

2470 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2471 echo "</a>"

2472 else

2473 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2474 fi

2475 fi

2476 echo "<br>$name${ownerp:+<br>($ownerp)}"

2477 echo "</div>"

2478 done

2479 echo "</div>" # End of List-entry div

2480 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2481 )

2482 listmember() {

2483 listentry user "$@"

2484 }

2485 listgroup() {

2486 listentry group "$@"

2487 }

2488 hexteams() { # $1=gname, $2(optional)=user

2489 cond=${2:+" AND user='$2'"}

2490 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2491 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2492 }

2493 showgroup() { # $1=group-rowid

2494 if [ -z "$1" ]; then

2495 grid=`getpar grid`

2496 grid=${grid%%[!0-9]*}

2497 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2498 else

2499 grid=$1

2500 fi

2501 grp=`getgroupbyid $grid`

2502 qgrp=`sqlquote "$grp"`

2503 htmlgrp=`echo "$grp"|htmlescape`

2504 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2505 if isgroup "$grp"; then

2506 tf=$tmpd/title.$$

2507 sf=$tmpd/search.$$

2508 bodyclass=`query "SELECT val FROM grp_s

2509 WHERE gname=$qgrp AND key='regmode';"`

2510 if ismember "$user" "$grp"; then

2511 ismember="ismember"

2512 bodyclass="$bodyclass${bodyclass:+ }ismember"

2513 else

2514 ismember="" # bodyclass="group"

2515 fi

2516 bodyclass="$bodyclass grouphome"

2517 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2518 echo "グループ $htmlgrp" > $tf

2519

2520 showgroupsub $formdir/grp.def "$grid" | \

2521 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2522 -D_FORM_="syscmd(\`cat')" \

2523 -D_BODYCLASS_="$bodyclass" \

2524 -D_DUMPTABLE_="" \

2525 $htmlheader $sf $layout/form+dump.m4.html

2526 # $htmlheader $layout/form+dump.m4.html

2527 # $htmlheader is defined in grp()

2528 else # if $grp is removed at par2table

2529 listgroup

2530 fi

2531 }

2532 showgroupsub() {

2533 # $1=def-file $2=group-rowid

2534 # Using $ismember

2535 rowid=$2

2536 grp=`getgroupbyid $2`

2537 qgrp=`sqlquote "$grp"`

2538 td=`getcachedir grp/"$2"`

2539 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2540 if [ -z "$rowid" ]; then

2541 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2542 #grp=`sq $db "select gname from grp where rowid=$grp"`

2543 echo "showgroupsub: invalid argument($1 $2)" | html p

2544 return

2545 fi

2546 val=`getvalbyid grp profimg $rowid $tmpd`

2547 enticond="gname=$qgrp"

2548 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2549 if [ -n "$img" ]; then

2550 cat<<-EOF

2551 <p class="groupimg">

2552 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2553 EOF

2554 fi

2555 echo "<div class=\"noprofimg\">"

2556 viewtable $1 grp $rowid

2557 echo "</div>"

2558 if isgrpowner "$user" "$grp"; then

2559 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2560 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2561 iamowner=$rowid

2562 colmd=" mode"

2563 fi

2564 if [ -n "$ismember" ]; then

2565 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2566 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2567 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2568 cat<<-EOF

2569 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2570 href="?blog+$rowid">グループの新規話題作成</a>

2571 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2572 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2573 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2574 <div class="fold clear">

2575 `cgi_checkbox send yes id="send"`<label

2576 for="send">グループ全員にメッセージ送信</label>

2577 <div>

2578 `cgi_textarea message "" "cols=60"`

2579 `cgi_submit 送信`

2580 `cgi_reset リセット`

2581 </div>

2582 `cgi_hidden grp $rowid`

2583 </div></form>

2584 EOF

2585 fi

2586 # 加入ボタン + 加入者リスト

2587 if [ -n "$ismember" ]; then

2588 ismem='checked' state="(参加中)"

2589 else

2590 nomem='checked' state="(現在非加入)"

2591 fi

2592 # このグループでの加入アドレス

2593 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2594 and key='email';"`

2595 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2596 ## and key='email';"

2597 ##err email=$eml

2598 cat <<EOF

2599 <div class="fold clear">

2600 `cgi_checkbox reg yes id="reg"`<label

2601 for="reg">自身の加入状態を操作する</label>$state

2602 <div>

2603 EOF

2604 cgi_form grp <<EOF

2605 <p>このグループに</p>

2606 <table class="b">

2607 <tr><th>メンバーとして</th><td>

2608 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2609 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2610 <tr><th>参加する場合のメイルアドレス<br>

2611 <small>(メインのアドレスとは違うものにする場合に記入<br>

2612 同じでよい場合は空欄に)</small></th>

2613 <td>`cgi_text email $eml`</td></tr>

2614 </table>

2615 `cgi_hidden grp $rowid`

2616 EOF

2617 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2618 echo "moderated (承認加入の)グループなので実際に参加できるのは

2619 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2620 fi

2621 echo '</div></div>'

2622 echo '<h2>話題一覧</h2>'

2623 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2624 cat<<-EOF

2625 <form class="summary" action="$myname" title="$thelp">

2626 `cgi_hidden owner "$grp"`

2627 `cgi_hidden kwd "@week"`

2628 `cgi_hidden stage searchart`

2629 `cgi_submit "一週間のまとめ"`

2630 </form>

2631 EOF

2632 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2633 colstate="state:稼動状態:frozen=rowclass=凍結"

2634 DT_CHLD=article:blogid \

2635 DT_QOWNER="$qgrp" \

2636 DT_VIEW=replyblog dumptable html blog \

2637 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2638

2639 getgname="(select gname from grp where rowid=$rowid)"

2640 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2641 cm="?commission+$rowid"

2642 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2643 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2644 # team list

2645 hexteams=`hexteams "$grp"`

2646 if [ -n "$hexteams" ]; then

2647 echo "チーム一覧" | html h2

2648 echo '<div class="dumptable"><table class="b">'

2649 sq $db -html -header<<-EOF

2650 SELECT val TEAM,

2651 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2652 MEMBERS

2653 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2654 EOF

2655 echo '</table></div>'

2656 fi

2657 }

2658 grp_getbodyclass() {

2659 # Get css class name for document.

2660 # `moderated' for moderated groups

2661 # `ismember' for groups where user belongs

2662 # $1=GroupName (w/o quote)

2663 # $user=userNameCurrentlyLogin

2664 ## err grp_getbodyclass: 1="$1"

2665 qgrp=`sqlquote "$1"`

2666 query<<-EOF

2667 SELECT coalesce(

2668 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2669 'open')

2670 ||

2671 CASE WHEN '$user'

2672 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2673 THEN ' ismember'

2674 ELSE ''

2675 END;

2676 EOF

2677 }

2678 grpaction() { # $1=group-rowid

2679 err GRP_ACTION:IN

2680 grid=${1:-`getpar grp`}

2681 grp=`getgroupbyid "$grid"`

2682 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2683 if [ -z "$grp" ]; then

2684 echo "無効な指定です。" | html p; return

2685 fi

2686 if ! ismember $user "$grp"; then

2687 echo "加入者のみに許可された操作です。" | html p; return

2688 fi

2689 echo "グループ $grp 個別選択操作" \

2690 | _m4 -D_TITLE_="syscmd(\`cat')" \

2691 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2692 $layout/html.m4.html

2693

2694 isowner=""

2695 isgrpowner "$user" "$grp" && isowner="yes"

2696 usel=`getpar usel`

2697 if [ -n "$usel" ]; then

2698 uids=$(echo `echo $usel`|tr ' ' ',')

2699 ## err grpaction-1: grp=$grp, `echo $sql`

2700 text=`getpar text|tr -d '\r'`

2701

2702 rm=`getpar rm` cfm=`getpar confirm`

2703 ## err rm=$rm cfm=$cfm

2704 if [ x"$rm" = x"yes" ]; then

2705 if [ "$isowner" ]; then

2706 if [ x"$rm$cfm" = x"yesyes" ]; then

2707 # Eliminate

2708 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2709 for tbl in grp_mem grp_mem_s grp_mem_m; do

2710 sql="delete from $tbl $cond;"

2711 # echo "sql=$sql"

2712 query "$sql"

2713 err rmGRPuser "$sql"

2714 done

2715 num=`query "select count(*) from user where rowid in ($uids);"`

2716 #err num=$num

2717 if [ 0$num -gt 0 ]; then

2718 sql="select coalesce(b.val,a.name) from user a left join \

2719 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2720 # err `echo "$sql"`

2721 html pre<<EOF

2722 以下の${num}名のグループ $grp 登録を解除しました。

2723 `query "$sql"`

2724 EOF

2725 fi

2726 else

2727 echo "確認のチェックがないのでやめておきます。" | html p

2728 return

2729 fi

2730 else # not Group Owner

2731 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2732 return

2733 fi

2734 cat<<EOF

2735

2736 EOF

2737 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2738 if [ -z "$text" ]; then # if msg is empty

2739 echo "なにかメッセージを..." | html p

2740 return 0

2741 fi

2742 gecos=`gecos $user`

2743 safegc=`echo "$gecos" | tr -d '<>@'`

2744 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2745 fromad=`myemail4group "$grp"`

2746 ###mail_from="$safegc <$fromad>"

2747 mail_from="$safegc <$user>" # TEST: 2020/5/13

2748 test -n `getpar sender` &&

2749 export SENDER=$user # TEST: 2020/5/15

2750 replyto=$fromad

2751

2752 ## Start parse of attachment files

2753 if [ -n "`getpar email`" ]; then

2754 ar=`getpar supprcpt`

2755 if [ -n "$ar" ]; then

2756 for a in $ar; do

2757 if checkdomain "$a"; then

2758 supprcpt="$supprcpt $a"

2759 else

2760 err "SupprcptErr=[$a] by $user"

2761 removercpt="$removercpt $a"

2762 fi

2763 done

2764 fi

2765 subj=`getpar subject`

2766 afiles=""

2767 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2768 do

2769 f=$tmpd/$fn

2770 if [ -s $f ]; then

2771 afiles=$afiles"${afiles:+ }$f"

2772 fi

2773 done

2774 else

2775 preface=$(cat <<-EOF

2776 $url

2777 のグループ「$grp」のメンバーである $gecos さんから、

2778 あなた宛のメッセージです。

2779 ----------------------------------------------------------

2780 EOF

2781 )

2782 fi

2783 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2784 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2785 subj="${subj:-$gecos さんからのメッセージ}"

2786 REPLYTO=$replyto

2787 MAIL_FROM=$mail_from

2788 export REPLYTO SMAIL_TO MAIL_FROM

2789 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2790 for r in $rcpts; do

2791 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2792 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2793 else

2794 # Show sender and rcpts address for guest

2795 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2796 fi

2797 if [ -n "$afiles" ];then

2798 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2799 else

2800 smail "$r" "$subj"

2801 fi <<EOF

2802 ${preface:+$preface$nl}$text

2803 EOF

2804 done

2805 if [ $? = 0 ]; then

2806 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2807 sql="select coalesce(b.val, a.name) from

2808 (select name from user where rowid in ($uids)) a

2809 left join user_s b on a.name=b.name and b.key='gecos';"

2810 html pre<<EOF

2811 `query "$sql"`

2812 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2813 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2814 EOF

2815 err SendDone: `echo $sql`

2816 fi

2817 elif [ x"$rm" = x"commission" ]; then

2818 grp_reg_adm $grid $usel

2819 elif [ x"$rm" = x"addteam" ]; then

2820 team=`getpar team|sed "s/'/''/g"` # for single quotation

2821 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2822 if [ x"$team" != x"$newteam" ]; then

2823 echo "チーム名に使えない文字を除去しました" | html p

2824 team=$newteam

2825 fi

2826 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2827 cat<<-EOF | html p

2828 有効なチーム名を入力してください。

2829 カンマだけ、「なし」という名前は使えません。

2830 EOF

2831 echo "有効なチーム名を入力してください。" | html p

2832 else

2833 grp_add_team $grid "$team" $usel

2834 fi

2835 elif [ x"$rm" = x"rmteam" ]; then

2836 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2837 rmteam=`getpar rmteam|sed "s/'/''/g"`

2838 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2839 gname='$grp' AND user='$user' AND key='team'\

2840 AND val='$rmteam';\"`" ]; then

2841 grp_rm_team $grid "$rmteam" $usel

2842 else

2843 echo "所属していないチームの除去操作はできません。"|html p

2844 fi

2845 else

2846 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2847 fi

2848 fi

2849 fi

2850 # POST count summary

2851 from=`getpar from`; to=`getpar to`

2852 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2853 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2854 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2855 # New entry

2856 sql="WITH mems AS (

2857 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2858 ON gm.user=g.name

2859 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2860 ), target_article AS (

2861 SELECT id FROM article_s

2862 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2863 ), posts AS (

2864 SELECT author, count(author) post

2865 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2866 WHERE blogid IN (SELECT id FROM blog_s

2867 WHERE key='owner'

2868 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2869 AND key='text'

2870 GROUP BY author

2871 ), teams AS (

2872 SELECT user, group_concat(val, ', ') team

2873 FROM grp_mem_m

2874 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2875 AND key='team'

2876 GROUP BY user

2877 ), user_post AS (

2878 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2879 FROM mems m LEFT JOIN posts

2880 ON m.name=posts.author

2881 GROUP by m.rowid

2882 )

2883 SELECT

2884 CASE

2885 WHEN (SELECT user FROM grp_adm

2886 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2887 AND user=up.name) IS NOT NULL

2888 then 'k'

2889 ELSE ''

2890 END || rowid || ',' || gecos NAME,

2891 post POST, team _TEAM_

2892 FROM user_post up LEFT JOIN teams t

2893 ON up.name=t.user

2894 ORDER BY gecos;"

2895 ## err grpaction: "`echo \"$sql\"`"

2896 tf=$tmpd/title.$$

2897 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2898 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2899 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2900 for=\"cmadmin\">管理者委任</label>

2901 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2902 </p></div>"

2903 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2904 title=\"Shortcut: g${nl}Remove from the Group\"

2905 for=\"conf\">GRP登録解除</label>

2906 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2907 <p>この操作による通知は本人に行きません。

2908 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2909 </div>"

2910 # Get team list to which current user belongs into $hexteams

2911 allhexteams=$(hexteams "$grp")

2912 if [ -n "$isowner" ]; then

2913 myhexteams="$allhexteams" # admin can remove all teams' attr

2914 else

2915 myhexteams=$(hexteams "$grp" "$user")

2916 fi

2917 if [ -n "$myhexteams" ]; then

2918 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2919 title=\"Shortcut: s${nl}Strip a team tag from\"

2920 for=\"cmrmteam\">チーム属性除去</label>

2921 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2922 を除去します: `cgi_checkbox teamconfirm yes` 確認

2923 <p>この操作による通知は本人に行きません。

2924 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2925 </div>

2926 "

2927 fi

2928 stf=$tmpd/selteam.$$

2929 cgi_select_h selteam "5445414d" $allhexteams > $stf

2930 b1='<label> <input type="checkbox" name="usel" value="'

2931 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2932 br='<span id="reverse" title="Reverse Selection"></span>'

2933 #b2='"> <span>' b3='</span></label>'

2934 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2935 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2936 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2937 cgi_form grpaction<<EOF \

2938 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2939 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2940 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2941 | _m4 -D_TITLE_="spaste(\`$tf')" \

2942 -D_SUBTITLE_="チェック後操作ボタン" \

2943 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2944 $layout/form+dump.m4.html \

2945 | _m4 -D_TEAM_="spaste(\`$stf')"

2946 <p>下でチェックした人を対象として:</p>

2947 <div class="foldtabs">

2948 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2949 title="Shortcut: a${nl}Add a team tag to"

2950 for="cmteam">同じチーム属性を付与</label>

2951 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2952 `cgi_datalist_h teams $allhexteams`

2953 </div>

2954 ${rmteammsg}

2955 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2956 title="Shortcut: d${nl}DirectMail to"

2957 for="sendmsg" title="Direct Message">DM送信</label>

2958 <div>

2959 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2960 title="Using email format">email書式を使う</label>

2961 <div class="folded">

2962 <table>

2963 <tr><td>From: </td><td>$user</td></tr>

2964 <tr><td>このFrom:で送る</td>

2965 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2966 </td></tr>

2967 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2968 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2969 <tr><td>ファイル添付: </td>

2970 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2971 </table>

2972 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2973 </div>

2974 <div>本文:`cgi_textarea text "" cols=72`

2975 </div>

2976 </div>

2977 ${isowner:+$cmmsg$excmsg}

2978 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2979 </div>

2980 <h4>$grp 参加者一覧</h4>$fromtonote

2981 <table class="td2r thl">

2982 `sq $db -header -html "$sql"`

2983 </table>

2984 `cgi_hidden grp $grid`

2985 `cgi_hidden myuid $myuid id="myuid"`

2986 EOF

2987 }

2988 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2989 # Create TEMPORARY VIEW

2990 query<<EOF

2991 CREATE TEMPORARY VIEW writeusers AS

2992 SELECT DISTINCT author FROM article

2993 WHERE id in (

2994 select id from article where blogid=(select id from blog where rowid=$1)

2995 );

2996 CREATE TEMPORARY VIEW movablegroups AS

2997 SELECT g.rowid growid , g.gname

2998 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2999 ON grp.gname=gm.gname -- そのユーザが属している

3000 AND user='$user') g -- グループに絞る

3001 WHERE (SELECT author FROM writeusers

3002 EXCEPT

3003 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3004 IS NULL;

3005 $2

3006 EOF

3007 }

3008 sql4readableblogs() {

3009 # Create view of blogs that can be readable to $user

3010 # Blog is readable when:

3011 # 1: blog owner is an user

3012 # 2: else, 2.1: owner-group where the $user belongs

3013 # 2.2: else, owner-group is not moderated

3014 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3015 cat<<EOF ## | tee tmp/sql.out

3016 CREATE TEMPORARY VIEW readableblogs AS

3017 SELECT blog.rowid rid, id, author

3018 FROM blog

3019 NATURAL JOIN

3020 (SELECT id,

3021 max(CASE key WHEN 'owner' THEN val END) owner,

3022 max(CASE key WHEN 'mode' THEN val END) mode

3023 FROM blog_s GROUP by id) bs

3024 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3025 THEN 1 -- blog owner is an user, READABLE

3026 WHEN (SELECT val FROM grp_s

3027 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3028 AND

3029 (SELECT user FROM grp_mem

3030 WHERE gname=bs.owner AND user='$user') IS NULL

3031 THEN 0

3032 WHEN mode IN ('quiz', 'enquete')

3033 THEN 0 -- "quiz" mode blog cannot be searched

3034 ELSE 1

3035 END;

3036 EOF

3037 }

3038 editheading() { # $1=rowid-of-heading

3039 rowid=${1%%[!A-Z0-9a-z_]*}

3040 if [ -z "$rowid" ]; then

3041 echo "話題番号が未指定です。" | html p

3042 return

3043 fi

3044 owner=`getvalbyid blog owner $rowid`

3045 title=`getvalbyid blog title $rowid`

3046 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3047 | _m4 -D_TITLE_="修正" \

3048 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3049 -D_BLOGS_="" -D_DUMPTABLE_="" \

3050 -D_FORM_="syscmd(\`cat')" \

3051 $layout/html.m4.html $layout/form+dump.m4.html

3052 # Move to group

3053 if isuser "$owner"; then

3054 crview4article $rowid

3055 n=`query "SELECT count(*) FROM writeusers;"`

3056 ## err N=$n

3057 if [ $((n)) -gt 0 ]; then

3058 ## err ROWID=$rowid

3059 sql="SELECT growid || ':' || gname FROM movablegroups;"

3060 cat<<-EOF

3061 <div class="fold">

3062 `cgi_checkbox mv send id="mv"`<label

3063 for="mv">この話題をグループ所有に移動する</label>

3064 <div>

3065 <form action="?mvart" method="POST" enctype="multipart/form-data">

3066 移動先グループ:

3067 <select name="mv2grp">

3068 EOF

3069 query ".mode html"

3070 query<<-EOF |

3071 $sql

3072 .mode list

3073 EOF

3074 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3075 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3076 cat<<-EOF

3077 </select>

3078 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3079 そのグループに加入しているものに限られます)</p>

3080 <p>`cgi_checkbox cfm yes`<label>確認

3081 (この操作は元に戻すことができません)</label></p>

3082 `cgi_hidden blogrowid $rowid`

3083 `cgi_submit 移動`

3084 `cgi_reset Reset`

3085 </form>

3086 </div>

3087 </div>

3088 EOF

3089 fi

3090 # end of isuser "$owner"

3091 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3092 [ -n "$hexteams" ];}; then

3093 none="`echo なし|hexize`"

3094 cat<<-EOF

3095 <div class="fold">

3096 `cgi_checkbox mv2team send id="mv2team"`<label

3097 for="mv2team">この話題を以下のチームのものにする</label>

3098 <div><p>現在の所属チーム設定:

3099 `query "SELECT

3100 coalesce((SELECT val FROM blog_s

3101 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3102 AND key='team'),

3103 ':なし');"`</p>

3104 <form action="?mvart" method="POST" enctype="multipart/form-data">

3105 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3106 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3107 `cgi_hidden blogrowid $rowid`<br>

3108 `cgi_submit 移動`

3109 `cgi_reset Reset`

3110 </form></div></div>

3111 EOF

3112 fi

3113 }

3114 mvart() { # move diary to some group or team

3115 # or move blog of group to team which belong to the group

3116 blogrowid=`getpar blogrowid`

3117 cfm=`getpar cfm`

3118 ##### echo move blog:$blogrowid to $mv2grp | html p

3119 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3120 . ./s4-blog.sh

3121 if [ -z "$blogrowid" ]; then

3122 echo "無効な指定です(mvart)。" | html p

3123 return

3124 elif [ x"$cfm" != x"yes" ]; then

3125 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3126 elif { mv2grp=`getpar mv2grp`

3127 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3128 [ -n "$mv2grp" ]; }; then

3129 crview4article $blogrowid

3130 ########## TRANSACTION BEGIN

3131 query "BEGIN;"

3132 n=`query "SELECT count(*) FROM writeusers;"`

3133 ## err Nwriteuser=$n

3134 if [ $((n)) -gt 0 ]; then

3135 query<<-EOF

3136 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3137 WHERE key='owner'

3138 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3139 AND $mv2grp IN (SELECT growid FROM movablegroups);

3140 EOF

3141 fi

3142 query "END;"

3143 ########## TRANSACTION END

3144 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3145 [ -n "$mv2team" ];}; then

3146 # blog owner can move it to ANY team

3147 case "$mv2team" in

3148 'なし')

3149 cat<<-EOF

3150 DELETE FROM blog_s

3151 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3152 AND key='team';

3153 EOF

3154 ;;

3155 "") ;;

3156 *)cat<<-EOF

3157 BEGIN;

3158 REPLACE INTO blog_s(id, key, val)

3159 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3160 'team', '$mv2team');

3161 REPLACE INTO blog_s(id, key, val)

3162 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3163 'notify', 'all'); -- Change notify to all

3164 END;

3165 EOF

3166 esac | query

3167 fi

3168 blog_reply $blogrowid

3169 echo yes | html p

3170 }

3171 editart() { # $1=article-rowid $2=blogrowid

3172 rowid=${1%%[!A-Z0-9a-z_]*}

3173 blogrowid=${2%%[!A-Z0-9a-z_]*}

3174 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3175 echo "表示する記事番号が未指定です。" | html p

3176 return

3177 fi

3178 owner=`getvalbyid blog owner $blogrowid`

3179 title=`getvalbyid blog title $blogrowid`

3180 author=`getvalbyid article author $rowid`

3181 ## err EDITart: owner=$owner, author=$author

3182 if isgrpowner "$user" "$owner"; then

3183 : EDIT OK

3184 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3185 echo "本人か所有者しか編集できません." | html p

3186 return

3187 fi

3188 aid=`query "select id from article where rowid=$rowid;"`

3189 tmpout=$tmpd/editart.$$.out

3190 GF_ACTION="?replyblog+$blogrowid#$aid" \

3191 edittable $formdir/article.def article $rowid \

3192 > $tmpout

3193 rm -f /tmp/editart.out

3194 # Cannot use pipelining to m4 with genform() because of stdin stack

3195 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3196 -D_FORM_="syscmd(cat $tmpout)" \

3197 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3198 -D_BLOGS_= -D_DUMPTABLE_= \

3199 $layout/html.m4.html $layout/form+dump.m4.html

3200 }

3201 send2mem() {

3202 rowid=`getpar grp`

3203 rowid=${rowid%%[!0-9]*} # Cleaning

3204 if [ -z "$rowid" ]; then

3205 echo "グループが未指定です。" | html p

3206 return

3207 fi

3208 message=`getpar message`

3209 if [ -z "$message" ]; then

3210 echo "文章を入れてください。" | html p

3211 return

3212 fi

3213 grp=`getgroupbyid $rowid`

3214 members=`collectemail $grp`

3215 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3216 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3217 mailfrom="`gecos "$user"` <$mailfrom>"

3218 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3219 msg=$(cat<<-EOF

3220 $urlbase?grp+$rowid

3221 グループ $grp に所属する

3222 `gecos $user` さんよりメッセージ:

3223

3224 $message

3225 EOF

3226 )

3227 # smail rcpt subj (file)

3228 for m in $members; do

3229 echo "$msg" |

3230 MAIL_FROM=$mailfrom \

3231 SENDER=$noreply \

3232 REPLYTO=$mailfrom \

3233 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3234 smail "$m" "$sj"

3235 done

3236 cat<<EOF

3237 <p>以下のユーザに送信しました。</p>

3238 <pre>

3239 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3240 </pre>

3241 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3242 EOF

3243 }

3244 joingrpadmit() {

3245 # $1=yes/no $2=session-key

3246 if [ -z "$2" ]; then

3247 echo "bye bye" | html p; return

3248 fi

3249 t_usr=`session=$2 getpar user`

3250 t_grp=`session=$2 getpar group`

3251 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3252 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3253 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3254 echo "無効な加入依頼です。" | html p

3255 echo "有効期限が切れたか、

3256 他の管理者がいる場合は処理済みの可能性があります。" | html p

3257 return

3258 fi

3259 if ! isgrpowner "$user" "$t_grp"; then

3260 echo "グループ管理者のみの機能です。" | html p; return

3261 fi

3262 case $1 in

3263 yes) joingrp "$t_grp" "$t_usr" yes ;;

3264 no) joingrp "$t_grp" "$t_usr" no ;;

3265 *)

3266 echo "無効な指定です($1)。" | html p

3267 return ;;

3268 esac

3269 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3270 rcpts="`getgroupadminmails $t_grp` $user"

3271 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3272 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3273 に

3274 $t_usr

3275 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3276 しました。"

3277 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3278 query "delete from session where id='$2';"

3279 echo "$body" | html p

3280 }

3281

3282 joingrprequest() {

3283 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3284 jss="joingrp-`date +%s`-`genrandom 12`"

3285 addsession $jss +${memoplimitdays}days

3286 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3287 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3288 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3289 $url

3290 $user さんから

3291 グループ $1

3292 に加入依頼がありました。

3293

3294 承認する:

3295 $urlbase?joingrpadmit+yes+$jss

3296

3297 白紙に戻す:

3298 $urlbase?joingrpadmit+no+$jss

3299 EOF

3300 echo "管理者に加入依頼を出しました。

3301 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3302 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3303 }

3304 joingrp() {

3305 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3306 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3307 if isgrpowner "$user" "$1"; then

3308 isowner="yes"

3309 elif [ -n "$5" ]; then

3310 isowner="yes"

3311 else

3312 isowner=""

3313 fi

3314 ## err jg:isgrpowner: isowner="$isowner"

3315 if [ -n "$isowner" ]; then

3316 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3317 elif [ x"$2" != x"$user" ]; then # if user is not login user

3318 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3319 return

3320 elif [ x"$3" = x"no" ]; then

3321 : # Do not pursue those who leave

3322 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3323 : # Member can change own email address for the joining moderated group

3324 else # adding user is $user itself

3325 case `getgroupattr $1 regmode` in

3326 moderated)

3327 joingrprequest "$@" # Request only

3328 return

3329 ;;

3330 *)

3331 ;;

3332 esac

3333 fi

3334 qgname=`sqlquote "$1"`

3335 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3336 cond="where gname=$qgname and user='$2'"

3337 if [ x"$3" = x"yes" ]; then

3338 query "replace into grp_mem values($qgname, '$2');"

3339 # Notify joingrp to admin

3340 action="に加入しました。"

3341 if [ -n "$4" ]; then

3342 if msg=`emaildomaincheck "$4"`; then

3343 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3344 'string', '$4', NULL);"

3345 else

3346 echo $msg

3347 fi

3348 else

3349 query "delete from grp_mem_s $cond and key='email';"

3350 fi

3351 if [ -n "$5" ]; then # as ADMIN

3352 # Coming here means newly created group

3353 sql="select case\

3354 when (select count(*) from grp_mem where gname=$qgname)=1\

3355 then (select user from grp_mem\

3356 where gname=$qgname and user='$user')\

3357 else '' end; "

3358 err NewGrpChk: $sql

3359 if [ -n "`query \"$sql\"`" ]; then

3360 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3361 query "replace into grp_adm values($qgname, '$user');"

3362 fi

3363 fi

3364 else

3365 query "begin;

3366 delete from grp_mem $cond;

3367 delete from grp_mem_s $cond;

3368 delete from grp_mem_m $cond;

3369 delete from grp_adm $cond;

3370 delete from grp_adm_s $cond;

3371 delete from grp_adm_m $cond;

3372 end;"

3373 action="から脱退しました。"

3374 fi

3375 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3376 $url?grp+$grid

3377 $user (`gecos $user`)さんが

3378 グループ $1

3379 $action

3380 EOF

3381 }

3382 grp_add_team() (

3383 # $1=grp-rowid $2=team $3...=user-rowid(s)

3384 grp=`getgroupbyid $1`

3385 team=$2; shift; shift

3386 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3387 { echo "BEGIN;"

3388 for user; do

3389 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3390 '$grp',\

3391 (SELECT name FROM user WHERE rowid=$user),\

3392 'team', 'string', '$team');"

3393 done

3394 echo "END;"

3395 } | query

3396 )

3397 grp_rm_team() (

3398 # $1=grp-rowid $2=team $3...=user-rowid(s)

3399 grid=$1

3400 qgrp=$(sqlquote "`getgroupbyid $grid`")

3401 team=$2; shift; shift

3402 [ -z "$grid" -o -z "$team" ] && return

3403 { echo "BEGIN;"

3404 for user; do

3405 echo "DELETE FROM grp_mem_m\

3406 WHERE gname=$qgrp \

3407 AND user=(SELECT name FROM user WHERE rowid=$user)\

3408 AND key='team' AND val='$team';"

3409 done

3410 cat<<-EOF

3411 DELETE FROM blog_s

3412 WHERE rowid=(

3413 SELECT rowid

3414 FROM blog_s a

3415 WHERE key='team'

3416 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3417 AND NOT EXISTS (SELECT * FROM grp_mem_m

3418 WHERE key='team' AND val=a.val -- a.val=team

3419 AND gname = (SELECT val FROM blog_s b

3420 WHERE a.id=b.id AND key='owner')

3421 ));

3422 EOF

3423

3424 echo "END;"

3425 } | query

3426 )

3427 grp_reg_adm() {

3428 # $1=grp-rowid $2...=user-rowid

3429 grid=$1

3430 grp=`getgroupbyid "$1"`

3431 if [ -z "$grp" ]; then

3432 echo "無効なグループIDです" | html p; return

3433 fi

3434 if ! isgrpowner "$user" "$grp"; then

3435 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3436 fi

3437 shift

3438 for urid; do

3439 newadm=`query "select name from user where rowid=$urid;"`

3440 if [ -z "$newadm" ]; then

3441 echo "指定ユーザIDがおかしいようです。" | html p; return

3442 fi

3443 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3444 err ismember $newadm $grp

3445 if ismember $newadm "$grp"; then

3446 # OK, go ahead

3447 getgname="(select gname from grp where rowid=$grid)"

3448 query "replace into grp_adm values($getgname, '$newadm');"

3449 # confirm insertion

3450 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3451 if [ -n "`query \"$sql;\"`" ]; then

3452 echo "追加完了: $newadm" | html p

3453 else

3454 echo "追加失敗($1 $urid)" | html p

3455 fi

3456 fi

3457 showgroup $grid

3458 done

3459 }

3460 dt_rowhack() {

3461 # From: <TR>

3462 # ....

3463 # <TD>rowclass=foo</TD>

3464 # </TR>

3465 # To: <TR class="foo">....<TD>foo</TD></TR>

3466 sed -e '

3467 /^<TR>/ {

3468 :loop

3469 s/\n//

3470 N

3471 /<\/TR>/ {

3472 s/\n//

3473 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3474 n

3475 }

3476 $q

3477 b loop

3478 }'

3479 }

3480 dumptable() {

3481 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3482 # textのフィールドだけ全てダンプにしたほうがいいか

3483 # $DT_VIEW sets link

3484 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3485 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3486 VIEW=${DT_VIEW-replyblog}

3487 if [ -n "$VIEW" ]; then

3488 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3489 fi

3490 sqlfile=$tmpd/dump.sql

3491 : > $sqlfile # ensure to be empty

3492 printf '.mode html\n.header 1\n' > $sqlfile

3493 # $DT_CHLD=ChildTable:BindColumn

3494 if [ -n "$DT_CHLD" ]; then

3495 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3496 cat<<-EOF >> $sqlfile

3497 -- presql

3498 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3499 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3500 EOF

3501 # Speed up counting of new articles

3502 cat<<-EOF >> $sqlfile

3503 -- presql2

3504 DROP TABLE IF EXISTS _counts;

3505 CREATE TEMPORARY TABLE _counts AS

3506 SELECT $_i, count($_i) cnt

3507 FROM $_t GROUP BY $_i;

3508 /* Prepare NEW count table */

3509 CREATE TEMPORARY TABLE _target AS

3510 SELECT b.rowid trowid, b.id

3511 FROM "$2" b JOIN "$2_s" s

3512 ON b.id=s.id AND s.key='owner'

3513 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3514

3515 DROP TABLE IF EXISTS _children;

3516 CREATE TEMPORARY TABLE _children AS

3517 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3518 FROM (SELECT t.trowid, t.id $_i, a.id

3519 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3520 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3521

3522 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3523 DROP TABLE IF EXISTS _news;

3524 DROP VIEW IF EXISTS _news;

3525

3526 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3527 -- INSERT INTO _news

3528 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3529 CREATE TEMPORARY VIEW _news AS

3530 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3531 FROM (SELECT DISTINCT id FROM _target)

3532 a LEFT JOIN

3533 (SELECT $_i, count(ctime) newcnt

3534 FROM _children x

3535 WHERE ctime > coalesce((SELECT time from myacclog

3536 WHERE tblrowid=x.trowid),

3537 '1970-01-01')

3538 GROUP BY $_i) b

3539 ON a.id=b.$_i;

3540 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3541 EOF

3542 # REMOVE next line until 2019/5/1

3543 cntall="(select count($_i) from $_t where $_i=a.id)"

3544 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3545 # REMOVE next assignment until 2019/5/1

3546 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3547 and id in (select id from $_t where $_i=a.id) \

3548 and val > coalesce((select time from myacclog where \

3549 tblrowid=a.rowid),\

3550 '1970-01-01'))"

3551 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3552 cnt="$cntnew as '新着', $cntall as '総数',"

3553 dt_class=" td2r td3r dumpblogs"

3554 fi

3555 # Construct join expression

3556 eav="" scols=""

3557 pk=`gettblpkey $2`

3558 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3559 substr=${substr:-%s}

3560 for col in ${3:-`gettbl_s_cols $2`}; do

3561 valvar=val

3562 case $col in

3563 gecos) scols="$scols${scols:+, }${col#}"

3564 continue ;; # built-in column name

3565 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3566 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3567 case "$as" in

3568 *:*=*) cnd=${as#*:}

3569 h=${cnd%%=*} v=${cnd#*=}

3570 h=`sqlquotestr "$h"`

3571 v=`sqlquotestr "$v"`

3572 valvar="CASE val WHEN $h THEN $v END"

3573 as=${as%%:*} ;;

3574 esac

3575 ;;

3576 *) as=${col} ;;

3577 esac

3578 ss=`printf "$substr" "$valvar"`

3579 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3580 scols="$scols${scols:+, }b.$as"

3581 done

3582 #case author when '$user' then a.rowid else '---' end as ID,

3583 if [ -n "$DT_SQL" ]; then

3584 echo "$DT_SQL"

3585 else

3586 cat<<-EOF

3587 SELECT a.rowid as LINK, $cnt $scols

3588 FROM $2 a LEFT JOIN

3589 (SELECT $pk,$eav,

3590 max(CASE key

3591 WHEN 'owner'

3592 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3593 as gecos

3594 FROM ${2}_s c GROUP BY $pk)

3595 b ON a.$pk=b.$pk $4;

3596 EOF

3597 fi >> $sqlfile

3598 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3599 sqlog<<-EOF

3600 *** SQL-file: $sqlfile ***

3601 `cat $sqlfile`

3602 EOF

3603 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3604 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3605 cat $sqlfile >> tmp/sql

3606 # query ".read $sqlfile" > $tmpd/foo

3607 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3608 cp $tmpd/foo tmp/

3609 err "----- `gdate +%FT%T.%3N` ------------555555"

3610 ## $ddd LINE exists at the end of this function

3611 fi

3612 printf '.mode list\n.header 0\n' >> $sqlfile

3613 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3614 <div>

3615 <div class="dumptable">

3616 <table class="b$dt_class">

3617 `query ".read $sqlfile"`

3618 </table>

3619 </div>

3620 </div>

3621 EOF

3622 ### `query ".read $sqlfile"`

3623 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3624

3625 ## REMOVE THIS!

3626 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3627 }

3628

3629 par2table() (

3630 # copy current parameters of par into destination table

3631 # $1=definition-file

3632 # Using $user and $session

3633 # Return value:

3634 # 0: Stored successfully

3635 # 1: Insufficient fillings

3636 # 2: No permission to modify the record

3637 # 3: Invalid rowid

3638 # 4: SUCCESS to delete

3639 # 5: Stop deletion for lack of confirm check

3640 # 6: Password length too short

3641 # 7: Password mismatch

3642 # 8: Old password incorrect

3643 rowid=`getpar rowid`

3644 if [ ! -e $1 ]; then

3645 echo "テーブル定義ファイルが見付かりません" | html p

3646 exit 1

3647 fi

3648 tbl=${1%.def}

3649 tbl=${tbl##*/}

3650 if [ -n "$rowid" ]; then # Modify existing entry

3651 if [ x"$tbl" = x"user" ]; then

3652 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3653 elif [ x"$tbl" = x"grp" ]; then

3654 sql="select gname from $tbl where rowid=$rowid;"

3655 ##err p2t:grp:q $sql

3656 isgrpowner "$user" "`query $sql`" && rowowner=$user

3657 elif [ x"$tbl" = x"blog" ]; then

3658 # Check if owner in blog_s

3659 blogowner=`getvalbyid blog owner "$rowid"`

3660 if isgrpowner "$user" "$blogowner"; then

3661 rowowner=$user

3662 else

3663 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3664 fi

3665 else

3666 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3667 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3668 fi

3669 ### err rowowner=$rowowner

3670 if [ x"$user" != x"$rowowner" ]; then

3671 echo "他人のレコードはいじれないの" | html p

3672 return 2

3673 elif [ -z "$rowowner" ]; then

3674 echo "指定したレコードはないみたい" | html p

3675 return 3

3676 fi

3677 rm=`getpar rm` cfm=`getpar confirm`

3678 # Editing existent entry

3679 if [ x"$rm" = x"yes" ]; then

3680 if [ x"$rm$cfm" = x"yesyes" ]; then

3681 query "delete from $tbl where rowid=$rowid;"

3682 return 4

3683 else

3684 echo "消去確認のチェックがないので消さなかったの..." | html p

3685 return 5

3686 fi

3687 fi

3688 fi

3689

3690 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3691 if [ -n "$rowid" ]; then

3692 # Update of existing record

3693 for col in `gettblcols $tbl`; do

3694 val=`getparquote $col`

3695 [ -z "$val" ] && continue

3696 ## err query "update $tbl set $col=$val where rowid=$rowid"

3697 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3698 sql="update $tbl set $col=$val where rowid=$rowid;"

3699 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3700 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3701 ## User name cannot be changed with interface provided with this

3702 ## script. But we offer the trigger to change owner user

3703 ## of blog_s table.

3704 #err "select quote($col) from $tbl where rowid=$rowid;"

3705 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3706 cat<<-EOF | query

3707 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3708 -- should use EXCLUSIVE transaction outside of this.

3709 SAVEPOINT par2table;

3710 $sql

3711 update blog_s set val=$val

3712 where key='owner' and val=$old;

3713 RELEASE SAVEPOINT par2table;

3714 EOF

3715 ## XX: DIRTY Hack Ends here

3716 ## We should keep blog's owner as a single column which has

3717 ## foreign key constraint with primary key of grp/user.

3718 else

3719 query "$sql"

3720 fi

3721 done

3722 # Then, set up $pval for further insertion of tbl_s and tbl_m

3723 for col in `gettblpkey $tbl`; do

3724 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3725 pval="$pval${pval:+, }\"$val\""

3726 done

3727 else

3728 # New entry

3729 # Generate values() for primary keys

3730 for col in `gettblpkey $tbl`; do

3731 # Genuine primary keys for _m and _s

3732 val=`getvalquote $tbl $col`

3733 [ -z "$val" ] && continue

3734 pval="$pval${pval:+, }$val"

3735 done

3736 ##err pval=$pval

3737 for col in `gettblfkey $tbl`; do

3738 # args for values() to insertion into master table

3739 val=`getvalquote $tbl $col`

3740 [ -z "$val" ] && continue

3741 formaster=$formaster"${formaster:+, }$val"

3742 done

3743 formaster="$pval${formaster:+, }$formaster"

3744 ## err formaster=$formaster

3745 if [ -z "$formaster" ]; then

3746 echo "項目を全て埋めてください" | html pre

3747 return 1

3748 fi

3749 ## err "replace into $tbl values($formaster);"

3750 query "replace into $tbl values($formaster);"

3751 ## Insertion to master table, done

3752 fi

3753

3754 transaction=$tmpd/sqlfile.sql; touch $transaction

3755 for kt in s m; do

3756 tb2=${tbl}_$kt

3757 for col in `gettbl_${kt}_cols $tbl`; do

3758 ptype=`getpartype $col "limit 1"`

3759

3760 # First, check update of existing entries in _m

3761 if [ $kt = m ]; then

3762 # sessID|address.1.22|string|Somewhere-x.y.z

3763 sql=""

3764 ##err dots from query "select var from par where var like '$col.%';"

3765 for v in `query "select var from par where var like '$col.%';"`; do

3766 # v=address.1.22

3767 st_rowid=${v##*.}

3768 origcol=${v%%.*} # original column derived from

3769 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3770 ##case `getpartype $v` in

3771 ## err CASE `gettbl_coltype $tbl/$origcol` in

3772 ## err edit flag = `getpar action.$v`

3773 case `getpar action.$v` in

3774 rm)

3775 if [ x`getpar confirm.$v` = x"yes" ]; then

3776 newsql="delete from $tb2"

3777 else

3778 echo "削除確認未チェック" | html p

3779 fi ;;

3780 edit)

3781 case `gettbl_coltype $tbl/$origcol` in

3782 image|document|binary)

3783 file=$tmpd/`getparfilename $v`

3784 ## err type=file=$file

3785 [ -z "$file" ] && continue

3786 bn=`sqlquotestr "${file##*/}"`

3787 bin="X'"$(hexize "$file")"'"

3788 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3789 type=\"file:$ct\"

3790 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3791 cachedir=`getcachedir "$tbl/$rowid"`

3792 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3793 rm -rf $cachedir

3794 ;;

3795 *)

3796 newsql="update $tb2 set val=(select val from par where var \

3797 like '$col.%.$st_rowid')"

3798 ;;

3799 esac

3800 ;;

3801 *) # maybe "keep", do not modify value

3802 continue

3803 ;;

3804 esac

3805 # err newsql=$newsql

3806 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3807 done

3808

3809 if [ x"$bin" = x"NULL" ]; then

3810 ## err repl:normal sql=`echo $sql`

3811 if [ -n "$transaction" ]; then

3812 cat<<-EOF >> $transaction

3813 $sql

3814 DELETE FROM $tb2 WHERE type='string' AND val='';

3815 EOF

3816 else

3817 query "$sql

3818 delete from $tb2 where type='string' and val='';"

3819 ## err repl:normal done

3820 fi

3821 else

3822 # Binary update line is TOO LONG to pipelining

3823 sqlfile="$tmpd/sqlf.$$"

3824 if [ -n "$transaction" ]; then

3825 echo "$sql" >> $transaction

3826 else

3827 echo "$sql" > $sqlfile

3828 query ".read $sqlfile"

3829 fi

3830 fi

3831 # Rest of kt==m: set multiple mode

3832 nr=`getparcount $col`

3833 else

3834 nr=1 # for kt==s, number of records is 1

3835 fi

3836

3837 i=0

3838 while [ $i -lt $nr ]; do

3839 limit="limit 1 offset $i"

3840 i=$((i+1)) # increase beforehand against continue

3841 val=`getvalquote $tbl $col "$limit"`

3842 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3843 ## err $col=$val

3844 bin=NULL

3845 ## err partype$col=`getpartype $col "$limit"`

3846 case $ptype in

3847 file) file=$tmpd/`getparfilename $col "$limit"`

3848 ## err parfile-$col=$file

3849 [ -z "$file" ] && continue

3850 bin="X'"$(hexize "$file")"'"

3851 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3852 type=\"file:$ct\" ;;

3853 "*"*) continue ;; # foreign table

3854 *) type=\"string\" ;;

3855 esac

3856 case `gettbl_coltype $tbl/$col` in

3857 password) # special care for password

3858 # name={password,pswd1,pswd2}

3859 p1=`getpar pswd1 "$limit"`

3860 if [ -z "$p1" ]; then

3861 continue # SKIP password setting, if p1 is empty

3862 else

3863 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3864 ## err pswd=$pswd

3865 if pwcheck "$pswd"; then

3866 if [ x"$p1" = x"$p2" ]; then

3867 case "$p1" in

3868 ??????????*) ;;

3869 *) echo "パスワードは10字以上にしてください。" | html p

3870 return 6;;

3871 esac

3872 val="\"`echo $p1|mypwhash`\""

3873 else

3874 echo "2つの新パスワード不一致" | html p

3875 return 7

3876 fi

3877 else

3878 echo "旧パスワード違います" | html p

3879 return 8

3880 fi

3881 fi

3882 ;;

3883 esac

3884 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3885 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3886 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3887 if [ x"$bin" = x"NULL" ]; then

3888 ## err Normal-query: `echo $sql`

3889 if [ -n "$transaction" ]; then

3890 echo "$sql" >> $transaction

3891 else

3892 query "$sql"

3893 fi

3894 else

3895 sqlfile="$tmpd/query.$$"

3896 ## err sqlfile=`ls -lF $sqlfile`

3897 if [ -n "$transaction" ]; then

3898 echo "$sql" >> $transaction

3899 else

3900 echo "$sql" > $sqlfile

3901 query ".read $sqlfile"

3902 fi

3903 fi

3904 ## err p2t done

3905 done

3906 done

3907 done

3908 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3909 -- We cannot use transaction here, because groupupdate may use it.

3910 SAVEPOINT pa2table_insert;

3911 .read $transaction

3912 RELEASE SAVEPOINT pa2table_insert;

3913 EOF

3914 return $?

3915 ##err donee

3916 )

3917 genform() {

3918 # $1 = form definition file

3919 # $2, $3 (optional)= table name and ROWID

3920 # If $GF_VIEWONLY set and nonNull, output values without form

3921 # If $GF_ARGS set, use it as content-strings in the form

3922 # If $GF_OWNER set, use it as value of name="owner"

3923 # If $GF_STAGE set, use it as value of name="stage"

3924 forms="" hiddens="" rowid=$3

3925 if [ ! -e "$1" ]; then

3926 echo "そのようなデータベースはないようです($2)。" | html p

3927 return

3928 elif [ -n "$2" ]; then

3929 rec=`query "select * from $2 where rowid='$rowid';"`

3930 if [ -z "$rec" ]; then

3931 pk=`gettblpkey $2`

3932 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3933 rec=`query "select rowid from $2 where $pk='$rowid';"`

3934 rowid=$rec

3935 rec=$3

3936 fi

3937 if [ -z "$rec" ]; then

3938 echo "そんなレコードはないみたいね..." | html p

3939 return

3940 fi

3941 fi

3942 if [ -z "$GF_VIEWONLY" ]; then

3943 rm='<input id="rm" name="rm" type="checkbox"

3944 value="yes"><label for="rm">このエントリの削除</label>

3945 <span>ほんとうに消しますよ(確認)!

3946 <input name="confirm" type=checkbox value="yes">はい</span>'

3947 fi

3948 # Image Cache dir

3949 ## err genform: getcache=$2/$rowid

3950 td=`getcachedir "$2/$rowid"`

3951 while IFS=: read prompt name keytype type args; do

3952 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3953 sp="${args:+ }"

3954 form="" val=""

3955 if [ -n "$rowid" ]; then

3956 # err genform2a: Seeking for "$2.$name, type=$type"

3957 rawval=`getvalbyid $2 $name $rowid $td`

3958 val=`echo "$rawval"|htmlescape`

3959 ## err genform3a: getvalbyid $2 $name $rowid $td

3960 ## err genform3b: val="[$val]" type="$type"

3961 fi

3962 if [ -n "$GF_VIEWONLY" ]; then

3963 is_hidden "$2" "$name" && continue

3964 fi

3965 case "$type" in

3966 text*)

3967 cgiform=cgi_multi_$type

3968 if [ -s $td/$name.count -a -n "$val" ]; then

3969 form=`$cgiform $name $td`

3970 val=$(echo "$val"|

3971 while read fn; do

3972 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3973 </td></tr>$nl"

3974 done)

3975 val="<table>$nl$val$nl</table>"

3976 else

3977 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3978 form=`cgi_$type $name "$rawval" "$args"`

3979 fi

3980 ;;

3981 [Rr][Aa][Dd][Ii][Oo])

3982 fh="<label><input type=\"radio\" name=\"$name\""

3983 form="`echo $args|sed -e \

3984 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3985 ;;

3986 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3987 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3988 ;;

3989 [Ss][Ee][Ll][Ee][Cc][Tt])

3990 fh="<select name=\"$name\">$nl"

3991 form=$(for l in $args; do

3992 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3993 done)

3994 if [ -n "$val" ]; then

3995 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3996 fi

3997 form="$fh$form</select>"

3998 ;;

3999 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4000 if [ -s $td/$name.count ]; then

4001 form=`cgi_multi_file $name $td "$args"`

4002 if [ -n "$val" ]; then

4003 hrfb="$myname?showattc+$2_m"

4004 val=$(echo "$rawval" \

4005 | while read fn; do

4006 data=`percenthex "$td/$fn"`

4007 #ct=`cat $td/$fn.content-type`

4008 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4009 ri=`cat "$td/$fn.rowid"`

4010 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4011 #imgsrc="<img src=\"data:$ct,$data\">"

4012 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4013 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4014 done)

4015 fi

4016 else

4017 form="<input type=\"file\" name=\"$name\" $args>"

4018 if [ -n "$val" ]; then

4019 imgs=$(echo "$rawval"\

4020 |while read fn;do

4021 data=`percenthex "$td/$fn"`

4022 echo "<img src=\"data:image/png,$data\">$fn<br>"

4023 done)

4024 form=$form"<br>$imgs"

4025 val=$imgs # 2015-06-15

4026 else

4027 form="<input type=\"file\" name=\"$name\" $args>"

4028 fi

4029 fi

4030 ;;

4031 [Hh][Ii][Dd][Dd][Ee][Nn])

4032 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4033 args="value=\"$GF_STAGE\""

4034 fi

4035 form="<input type=\"hidden\" name=\"$name\" $args>"

4036 prompt='' # Remove prompt

4037 ;;

4038 [Aa][Uu][Tt][Hh][Oo][Rr])

4039 [ -n "$GF_VIEWONLY" ] && continue

4040 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4041 prompt="" ;;

4042 [Oo][Ww][Nn][Ee][Rr])

4043 [ -n "$GF_VIEWONLY" ] && continue

4044 val=${GF_OWNER:-$val}

4045 val=${val:-$user}

4046 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4047 prompt="" ;;

4048 [Uu][Ss][Ee][Rr])

4049 # XXX: is null $user ok?

4050 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4051 [ -n "$GF_VIEWONLY" ] && continue

4052 form="$user"

4053 ;;

4054 [Pp]assword)

4055 [ -n "$GF_VIEWONLY" ] && continue

4056 form="`cgi_passwd`"

4057 val=""

4058 ;;

4059 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4060 [ -n "$GF_VIEWONLY" ] && continue

4061 if [ -z "$rowid" ]; then

4062 val=`genserial`

4063 fi

4064 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4065 prompt="" ;;

4066 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4067 prompt=""

4068 ;;

4069 parent|path|blog*)

4070 prompt=""

4071 ;;

4072 "*"*)

4073 tail=$tail"``"

4074 continue ;;

4075 esac

4076 if [ -n "$prompt" ]; then

4077 if [ -n "${GF_VIEWONLY}" ]; then

4078 form=$val

4079 else

4080 :

4081 fi

4082 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4083 else

4084 hiddens=$hiddens$nl"$form"

4085 fi

4086 done < $1

4087 # enctype="multipart/form-data"

4088 cat<<EOF

4089 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4090 ${rowid:+$rm}

4091 <table class="b $2">

4092 $forms

4093 </table>$hiddens

4094 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4095 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4096 EOF

4097 if [ -z $GF_VIEWONLY ]; then

4098 cat<<EOF

4099 <input type="submit" name="sub" value="OK">

4100 <input type="reset" name="res" value="Reset">

4101 EOF

4102 fi

4103 cat<<EOF

4104 $GF_ARGS</form>

4105 $tail

4106 EOF

4107 }

4108 edittable() {

4109 # $1=form-def $2=table $3 rowid

4110 genform "$@"

4111 }

4112 viewtable() {

4113 GF_VIEWONLY=1 genform "$@"

4114 }

4115 showattc() {

4116 # $1=table_m $2=rowid &optional $3=RawFlag

4117 ## err \$1=$1 \$2=$2 \$3=$3

4118 if ! isfilereadable $user $1 $2; then

4119 contenttype; echo

4120 echo "このファイルは管理者のみしか見られません" | html p

4121 putfooter; exit

4122 fi

4123 idir=`umask 002; mktempd` || exit 1

4124 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4125 bin=$idir/$myname-$$.bin

4126 sql="select quote(bin) from $1 where rowid='$2';"

4127 ## err showattc: sql: $sql

4128 sq $db "$sql" | unhexize > $bin

4129 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4130 type=${tv%//*} fn=${tv#*//}

4131 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4132 ct=${type#file:}

4133 case $ct in # all text/* changed to text/plain

4134 text/*)

4135 charset=`nkf -g $bin|cut -d' ' -f1`

4136 case $charset in

4137 ASCII*) charset="" ;;

4138 esac

4139 if [ -z "$3" ]; then

4140 ct="text/html${charset:+; charset=$charset}"

4141 link="?showattc+$1+$2+raw"

4142 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4143 | sed 's,^,<span></span>,' \

4144 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4145 -D_LINK_="$link" \

4146 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4147 exit $?

4148 fi

4149 ct="text/plain${charset:+; charset=$charset}"

4150 ;;

4151 esac

4152 contenttype "$ct"

4153 echo "Content-Disposition: filename=\"$fn\""

4154 echo "Content-Length: " `cat $bin | wc -c`; echo

4155 #echo "Content-Type: " ${type#file:}; echo

4156 cat $bin

4157 }

4158 #

4159 # Some default stupid handler on CGI values

4160 #

4161 default_storedb() {

4162 # ARG: $1=table-def-file

4163 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4164 tbl=`basename $1`

4165 tbl=${tbl%.def}

4166 cols="`grep :text $1|cut -d: -f2`"

4167 col=`echo "$cols"|head -1`

4168 vcol=`getpar $col`

4169 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4170 if [ -n "$vcol" ]; then

4171 par2table $1

4172 else

4173 return 2 # No insertion occurred

4174 fi

4175 }

4176

4177 default_view() { # $1=def-file

4178 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4179 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4180 default_storedb "$@"

4181 query "select rowid from $tbl order by rowid desc;" \

4182 | while read rowid; do

4183 viewtable $1 $tbl $rowid

4184 done | _m4 -D_TITLE_="$tbl" \

4185 -D_FORM_="`genform $1`" \

4186 -D_DUMPTABLE_="syscmd(cat)" \

4187 $layout/html.m4.html $layout/form+dump.m4.html

4188 }

4189 default_viewtext() { # $1=def-file

4190 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4191 default_storedb "$@"

4192 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4193 | _m4 -D_TITLE_="$tbl" \

4194 -D_FORM_="`genform $1`" \

4195 -D_DUMPTABLE_="syscmd(cat)" \

4196 $layout/html.m4.html $layout/form+dump.m4.html

4197 }

4198 default_smail() {

4199 default_storedb "$@"

4200 if [ $? -eq 2 ]; then

4201 _m4 -D_TITLE_="入力" \

4202 -D_FORM_="`genform $1`" \

4203 -D_DUMPTABLE_="" \

4204 $layout/html.m4.html $layout/form+dump.m4.html

4205 return

4206 fi

4207 cond=""

4208 for pk in `gettblpkey $tbl`; do

4209 pv=$(sqlquote "$(getpar $pk)")

4210 cond="$cond${cond:+ and }$pk=$pv"

4211 done

4212 sql="select rowid from $tbl where $cond;"

4213 rowid=`query "$sql"`

4214 ## err smail1 - "$sql" "-> rowid=$rowid"

4215

4216 while IFS=: read prompt name keytype type args; do # Read from $1

4217 val=`getpar $name`

4218 if [ -n "$val" ]; then

4219 text="$text

4220 $prompt

4221 $name=$val

4222 ---------------------------------------------------------"

4223 fi

4224 case "$type" in

4225 image|document|file)

4226 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4227 fns=$(echo "$fn"|while read fn; do

4228 err mv $tmpd/$fn.orig $tmpd/$fn

4229 mv $tmpd/$fn.orig $tmpd/$fn

4230 rm $tmpd/$fn.rowid # Remove cache flag

4231 ## err "`ls $tmpd/$fn`"

4232 echo $fn

4233 done)

4234 files="$files $fns"

4235 ;;

4236 esac

4237 done < $1

4238 ## err FILES=$files "`ls -lF $tmpd`"

4239 subj="from ${REMOTE_ADDR}"

4240 (echo "$url"

4241 echo "への書き込みがありました。"

4242 echo "------"

4243 echo "$text"

4244 ) | (cd $tmpd &&

4245 err LS="`ls -lF`" &&

4246 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4247 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4248 echo "以下の内容で送信しました。" | html p

4249 viewtable $1 $tbl \

4250 `query "select rowid from $tbl order by rowid desc limit 1;"`

4251 echo "戻る" | html a "href=\"?\""

4252 }