s4: d368b937956e s4-funcs.sh

s4

view s4-funcs.sh @ 801:d368b937956e

Limit the number of articles to show by default

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sun, 14 Jun 2020 12:41:31 +0900
parents	2c5a8c5dd1ee
children	1de1319dce99

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 listartlimit=${LISTARTLIMIT:-50}

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

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

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

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

27 templ=${TEMPL:-templ}

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

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

30 imgdir=${IMGDIR:-img}

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

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

33 msgdir=$templ/msg

34 timeout="+2 days"

35 memoplimitdays="7"

36 dumpcollen=22

37 #thumbxy=120x120

38 thumbxy=96x96

39 iconxy_S=80x80

40 iconxy_M=400x400

41 maximagexy=1600x1600

42 ### maximagexy=400x400

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

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

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

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

48 file_accept_help="

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

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

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

52 "

53 file_warn="$file_accept_help

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

55 blogreadflagrowid=0

56 blogcutoffflagrowid=-1

57 whatsnewdays=${WHATS_NEW_DAYS:-14}

58 main_session=`date +%F-$$`

59 session=$main_session

61 tconfs=""

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

63 conftbl=_tblconf

64 nl="

65 "

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

67 iconcachekey="profimgcache_S"

68 case "$HTTP_USER_AGENT" in

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

70 *) touchpanel="" ;;

71 esac

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

74 : <<EOF

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

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

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

79 レポート提出管理

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

82 [2016]

83 7/12 根本への反省

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

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

87 [以下2015]

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

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

90 Web

91 締切設定

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

94 ○自分の提出物リスト

96 7/19 ○設置

97 ○一斉送信

98 ○getparfilename の tmpd の扱い

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

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

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

102 ○添付ファイル回収

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

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

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

106

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

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

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

110 ○レポート提出モード

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

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

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

114

115

116 7/9 ○管理者の追加

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

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

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

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

121

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

123

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

125 ○グループ検索

126

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

128

129

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

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

132

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

134 ○6/1 par2tableを triplex 対応に

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

136 →とすると一気に

137

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

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

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

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

142

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

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

145 ## 1. passwd

146 ## 2. grp

147 ## 3. grp_mem

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

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

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

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

152

153 ## ● listの定義:

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

155 ## ● hashの定義:

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

157

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

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

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

161 ## csv2sq3 で .csv.sq3 の Makefile

162

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

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

165 ## type := root | comment

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

167 ## unique(id, filename)

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

169

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

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

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

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

174 ## できたー!

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

176 ↓

177 ↓以下に変更

178 with recursive allmem as

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

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

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

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

183

184

185 with recursive allmem as

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

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

188 where allmem.val=grp_m.gname)

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

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

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

192

193

194 ## triggerもできた。

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

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

197

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

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

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

201 ## グループ管理

202 ## 個人情報管理

203 ##

204

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

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

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

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

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

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

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

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

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

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

215

216 ## ログテーブル

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

218

219

220

221 ■表設計

222 * 3つの表に分散管理

223 id格納表 + hash表 + list表

224 * *_s *_m

225

226

227

228 user, user_map, user_col

229

230 ■抽象エントリタイプ

231 * user

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

233 * group

234 権限判定に利用

235 * serial

236 自動idとして機能

237 * password

238 入力 type=passwordで入力

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

240 * session

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

242 * text

243 入力 type=text

244 * textarea

245 入力 textarea

246 * image|document

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

248 * owner

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

250 * gowner

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

252 * timestamp

253 datetime()

254 * parent

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

256 * path

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

258

259 格納タイプ

260 * list

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

262 * hash

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

264

265 オブジェクトタイプ

266 * entry

267 id, title, owner

268 * textpart

269 id, parentID, text

270 * binarypart

271 id, parentID, contenttype, filename, content

272 * content

273 hash(textpart), list(binarypart)

274 * topic

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

276 * reply

277 id, parentID, content

278 * blog

279 list(entry)

280 blog = [topic, list(reply)]

281

282

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

284 "text" => "hogehoge ..",

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

286 "author" => "taro",

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

288 "parent" => "/",

289 "path" => "/1",

290 "text" => "blah, blah, ....",

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

292 {"serial" => 2,

293 "author" => "hanako",

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

295 "parent" => "/",

296 "path" => "/2",

297 "text" => "blah, blah, ....",

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

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

300

301

302 user:=

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

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

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

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

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

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

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

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

311

312 変換表

313 /user/email=m

314

315 blog:=

316 シリアル:id:p:serial

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

318 所有者:owner:s:owner:

319 時刻:ctime:s:stamp:

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

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

322

323 article:=

324 シリアル:id:p:serial

325 筆者:author:s:owner

326 時刻:ctime:s:stamp:

327 参照元:parent:s:parent:

328 パス:path:s:path:

329 本文:text:s:textarea:

330 画像:image:m:image:

331

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

333

334

335 EOF

336

337 logstart() {

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

339 }

340 logend() {

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

342 }

343 sqlog() {

344 logstart

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

346 cat >> $querylog

347 else

348 echo "$*" >> $querylog

349 fi

350 logend

351 }

352 sq() {

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

354 logstart

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

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

357 else

358 echo "$@" >> $querylog

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

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

361 fi

362 logend

363 }

364 dbsetup() {

365 pipedir=$tmpdir/pipedir

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

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

368 suf=`date +%s`

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

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

371 mkfifo $sqi $sqo

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

373 sq $db < $sqi &

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

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

376 exec 2>> $tmpdir/error.out

377 fi

378 exec 3>> $tmpdir/debug.out

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

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

381 rm $sqi

382 # Attach supplemental DB

383 cat >&5 <<-EOF

384 .output /dev/null

385 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

389 .output stdout

390 EOF

391 }

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

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

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

395 fi

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

397 }

398 cleanup() {

399 trap '' INT HUP EXIT TERM PIPE

400 echo .quit >&5

401 kill $sq3pid

402 kill $sq3pid

403 rm -f $sqo $sqi

404 rm -rf $tmpfiles

405 cleanup2

406 }

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

408 # simple redirection for the sake of speed.

409 query() {

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

411 echo ".output $sqo" >&5

412 logstart

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

414 tee -a $querylog

415 else

416 echo "$@" >> $querylog

417 echo "$@"

418 fi >&5

419 echo ".output stdout" >&5

420 cat $sqo

421 rc=$?

422 logend

423 return $rc

424 }

425 _m4() {

426 #S4NAME=f,f,f

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

428 }

429 ismember() {

430 # $1=user, $2=group

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

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

433 }

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

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

436 }

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

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

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

440 }

441 isgrpowner() (

442 # $1=user, $2=group

443 gn=`sqlquote "$2"`

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

445 err isgrpowner: $sql

446 test -n "`query $sql`"

447 )

448 isgrpownerbygid() (

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

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

451 err isgrpownerbygid: $sql

452 test -n "`query $sql`"

453 )

454 getgroupadminmails() {

455 # $1=group

456 for i in $(getgroupadmins "$1"); do

457 email4group "$1" "$i" ;

458 done

459 }

460 getgroupadmins() { # $1=group

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

462 qgrp=`sqlquote "$1"`

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

464 }

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

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

467 getvalbyid grp $2 \

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

469 }

470 getgroupbyid() {

471 # $1=id|gname

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

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

474 # err ggbyid: `echo $sql`

475 query $sql

476 }

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

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

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

480

481 # Return true when anonymous mode

482 [ "$anonymousmode" ] && return 0

483 # case `getvalbyid blog mode $2` in

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

485 # *closed*)

486 # owner=`getvalbyid blog owner $2`

487 # if isgrp $owner; then

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

489 # elif isuser $owner; then

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

491 # fi

492 # esac

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

494 sql="with getblog as (

495 select key,val from blog_s where id=(

496 select blogid from article where id in

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

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

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

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

501 isgrpadm as (select user from grp_adm where

502 gname=(select val from getowner) and

503 user='$1'),

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

505 select case

506 when (select author from article where

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

508 then 'author'

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

510 then 'open'

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

512 then CASE

513 WHEN (SELECT val FROM isgrp) IS NULL

514 THEN

515 CASE WHEN (SELECT val from getowner)

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

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

518 ELSE ''

519 END

520 WHEN (select user from isgrpadm) IS NOT NULL

521 THEN 'i-am-admin'

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

523 END

524 when (select val from getmode) is null

525 then 'open'

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

527 then (SELECT user FROM isgrpadm)

528 when (select author from article where

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

530 then 'user+author'

531 else '' end;"

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

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

534 # result=`query "$sql"`

535 # err FileAccessibility=$result

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

537 }

538 linkhome() {

539 # $1=UserOrGroupRowid

540 echo -n "<a href=\"$myname?"

541 if isuser $1; then

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

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

544 name=`gecos $1|htmlescape`

545 else

546 _grid=`numericalize "$1"`

547 echo -n "grp+$1"

548 name=`query "SELECT gname FROM grp WHERE rowid=$_grid;"|htmlescape`

549 fi

550 echo "\">$name</a>"

551 }

552 hreflink() {

553 # s4 specific notation:

554 # ^href=URL

555 # ^iframe=URL

556 # ^video=URL

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

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

559 # OSM umap Wikistyle Notation:

560 # [[URL]] - Simle Link

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

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

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

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

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

566 # Other Style

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

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

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

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

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

572 # SPC+SPC+$ - <br>

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

574 checkboxON="${cb} checked>"

575 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

600

601 }

602 minitbl() {

603 sed -n '

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

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

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

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

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

609 H; # Concat this line to HoldSpace

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

611 $ b cont

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

613 }

614 :cont

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

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

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

618 # . of ^. is workaround for FreeBSD sed

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

620 p; # Print whole "table" element

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

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

623 }

624 x; # Back to the newest line

625 p; # Print rest' | miniul

626 }

627 miniul() {

628 sed -e '

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

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

631 :top

632 s/\n//;

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

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

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

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

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

638 $ b cont

639 N; # 次の行を読む

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

641 /^ /b top

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

643 p; s/.*//;

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

645 /^\* /b top

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

647 }

648

649 :cont

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

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

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

653 H; x

654 }

655 x'

656 }

657 acclog() (

658 # $1=table, $2=rowid

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

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

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

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

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

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

665 fi

666 )

667 gecos() (

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

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

670 )

671 setpar() {

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

673 if [ x"$session" = x"$main_session" ]; then

674 case "$1" in

675 user) _user="$v" ;;

676 skey) _skey="$v" ;;

677 esac

678 fi

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

680 }

681 unsetpar() {

682 for i; do

683 if [ x"$session" = x"$main_session" ]; then

684 case "$i" in

685 user|skey) unset _$i ;;

686 esac

687 fi

688 query "DELETE FROM par WHERE var='$i';"

689 done

690 }

691 replpar() {

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

693 }

694 getpar() {

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

696 val=""

697 if [ x"$session" = x"$main_session" ]; then

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

699 user) val=$_user ;;

700 skey) val=$_skey ;;

701 esac

702 fi

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

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

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

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

707 fi

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

709 case "$var" in

710 owner)

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

712 echo $user; return

713 elif ismember $user $val; then

714 echo $val; return

715 fi ;;

716 esac

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

718 echo "$val"

719 }

720 setskey() {

721 # For quick response...(?)

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

723 }

724 chkskey() {

725 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

731 return 0

732 fi

733 return 1

734 }

735 resetskey() {

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

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

738 fi

739 }

740 getpartype() {

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

742 }

743 getparcount() {

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

745 }

746 getparfilename() {

747 # null if type of $1 is not file

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

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

750 }

751 sqlquote() {

752 (v="$1"

753 case "$v" in

754 "") return ;; # null

755 "X'"*) # quoted hex string

756 echo $1 ;;

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

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

759 echo "\"$v\""

760 return ;;

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

762 echo "\"$v\""

763 return ;;

764 *)

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

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

767 else

768 echo "\"$v\""

769 fi ;;

770 esac)

771 }

772 sqlquotestr() (

773 case "$1" in

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

775 echo "'$v'" ;;

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

777 esac

778 )

779 mktempd() {

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

781 }

782 getcachedir() { # $1=maintable

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

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

785 else

786 echo $tmpd/$thumbxy

787 fi

788 }

789 getval() {

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

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

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

793 echo "$user" ;;

794 stamp|datetime)

795 date "+%F %T" ;;

796 serial)

797 (s=`getpar $2`

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

799 *)

800 getpar "$2" "$3";;

801 esac

802 }

803

804 getvalquote() {

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

806 (v=`getval "$@"`

807 case "$v" in

808 "") echo NULL ;;

809 *) sqlquote "$v" ;;

810 esac)

811 }

812 getparquote() {

813 sqlquote "`getpar $1`"

814 }

815 getbinbyid() {

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

817

818 }

819 getvalbyid() {

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

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

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

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

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

825

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

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

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

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

830 return

831 fi

832 done

833 rowid=$3

834 pk=`gettblpkey $1`

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

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

837 td=${4:-$tmpd}

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

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

840 for kt in s m; do

841 t=${1}_$kt

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

843 vcount=1 # count(val)

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

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

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

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

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

849 if [ $kt = m ]; then

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

851 # Reset val to store filenames if type is string

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

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

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

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

856 i=0

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

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

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

860 i=$((i+1))

861 fn=$c.$i

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

863 case $type in

864 file:*)

865 #file=$td/$val

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

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

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

869 # FOR SPEED: Skip file generation if imgcache exists

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

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

872 && continue

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

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

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

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

877 select rowid from $cond $slice;

878 .output '$td/$fn'

879 select val from $cond $slice;

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

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

882 .output stdout

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

884 EOF

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

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

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

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

889 ls -lh "$file" |

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

891 case "$type" in

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

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

894 *|*:[Aa]pplication*)

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

896 png:- > "$file"

897 ;;

898 esac

899 ;;

900 *)

901 sq $db<<EOF

902 .output $td/$fn.rowid

903 select rowid from $cond $slice;

904 .output $td/$fn

905 select val from $cond $slice;

906 EOF

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

908 ;;

909 esac

910 done

911 else

912 rm -f $td/$c.count

913 case $type in

914 file:*)

915 echo "$val" \

916 | while read fn; do

917 file=$td/$fn

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

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

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

921 | unhexize > "$file"

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

923 case $type in

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

925 *:[Aa]pplication*)

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

927 png:- > $file ;;

928 esac

929 fi

930 done

931 ;;

932 esac

933 fi

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

935 return

936 fi

937 done

938 done)

939 }

940 getvalbypkey() (

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

942 pk=`gettblpkey $1`

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

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

945 )

946 getvalbycond() {

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

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

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

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

951 getvalbyid "$1" "$2" $rowid "$4"

952 fi

953 }

954 getpwfield() {

955 # getpwfield user column

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

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

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

959 echo "$val"

960 return 0

961 else

962 return 1

963 fi

964 }

965 numericalize() {

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

967 }

968 encode() {

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

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

971 sha1=sha1

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

973 sha1=sha1sum

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

975 sha1=gsha1sum

976 fi

977 fi

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

979 }

980 enjpeg() {

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

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

983 cjpeg="cjpeg"

984 else

985 cjpeg="convert - jpeg:-"

986 fi

987 fi

988 $cjpeg "$@"

989 }

990 mycrypt() (

991 key=$1 salt=$2

992 # err \$2=$2

993 case $2 in

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

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

996 esac

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

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

999 )

1000 hexize() {

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

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

1003 hexize="xxd -p"

1004 else

1005 hexize_hd() {

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

1007 }

1008 hexize="hexize_hd"

1009 fi

1010 fi

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

1012 }

1013 unhexize() {

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

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

1016 unhex="xxd -p -r"

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

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

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

1020 EOF

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

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

1023 # world writable...:)

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

1025 fi

1026 fi

1027 cat "$@" | $unhex

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

1029 }

1030 percenthex() {

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

1032 }

1033 htmlescape() {

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

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

1036 -e 's/`/\&#96/'

1037 }

1038 enascii() {

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

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

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

1042 else

1043 enascii_now=`date +%FT%T`

1044 enascii_sed() {

1045 nkf -Z0Z1Z2 \

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

1047 }

1048 enascii="enascii_sed"

1049 fi

1050 fi

1051 cat "$@" | $enascii

1052 }

1053 size_h() {

1054 i="$1" oi=$1

1055 set -- B B KB MB GB TB

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

1057 oi=$i

1058 i=$((i/1024))

1059 shift

1060 done

1061 echo ${oi}$1

1062 }

1063 gettblconf() {

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

1065 ## tconfs=`sq $db \

1066 tconfs=`query \

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

1068 fi

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

1070 }

1071 gettblkeys() {

1072 # $1=tbl

1073 gettblconf

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

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

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

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

1078 case $type in

1079 *p*)

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

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

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

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

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

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

1086 esac

1087 case $type in

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

1089 esac

1090 done

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

1092 }

1093 gettblpkey() {

1094 # $1=tbl

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

1096 }

1097 gettblfkey() {

1098 (x=`gettblkeys $1`

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

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

1101 }

1102 gettblcols() {

1103 (x=`gettblkeys $1`

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

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

1106 }

1107 gettbl_s_cols() {

1108 (x=`gettblkeys $1`

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

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

1111 }

1112 gettbl_m_cols() {

1113 (x=`gettblkeys $1`

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

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

1116 }

1117 gettbl_h_cols() {

1118 (x=`gettblkeys $1`

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

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

1121 }

1122 gettbl_coltype() (

1123 gettblconf

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

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

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

1127 )

1128 is_hidden() {

1129 # $1=Tbl $2=col

1130 gettblconf

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

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

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

1134 case $x in

1135 *h*) return 0 ;;

1136 *) return 1 ;;

1137 esac

1138 }

1139

1140 dbsetbyid() {

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

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

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

1144 quotedp=$(sqlquotestr "$p")

1145 unset primary update

1146 gettblconf

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

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

1149 #err conf=$conf

1150 case ${conf#*=} in

1151 p*) primary=1 ;;

1152 f*) update=1 ;;

1153 u*) ;;

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

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

1156 esac

1157 #err t=$t

1158 type=string fn=""

1159 case $conf in

1160 */password)

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

1162 ;;

1163 */image*|*/document*)

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

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

1166 ;;

1167 esac

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

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

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

1171 if [ "$primary" ]; then

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

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

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

1175 elif [ "$update" ]; then

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

1177 else

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

1179 fi

1180 )

1181 }

1182 expire() (

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

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

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

1186 )

1187 addsession() {

1188 # expireをセット

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

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

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

1192 # Remove old session parameters

1193 now=`expire now`

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

1195 }

1196 gencookie() (

1197 path=${URL#*:/}

1198 path=${URL%/*}

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

1200 for kv; do

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

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

1203 done

1204 )

1205 contenttype() {

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

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

1208 }

1209 putheader() {

1210

1211 }

1212 putfooter() {

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

1214 }

1215 getcookie() {

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

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

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

1219 case "$k" in

1220 user) _user="$v" ;;

1221 skey) _skey="$v" ;;

1222 esac

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

1224 done

1225 }

1226 genrandom() {

1227 # $1=columns (default: 10)

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

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

1230 }

1231 genserial() {

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

1233 }

1234 smail() {

1235 # smail rcpts subj (file)

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

1237 # $MAIL_FROM <- From: header value

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

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

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

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

1242 sender=${SENDER:-$admin}

1243 # Do not call m4 with directly passing text

1244 _r=$tmpd/rcpt

1245 echo -n "${SMAIL_TO:-$rcptheader}" > $_r

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

1247 (_m4 -D_RCPT_="spaste(\`$_r')" -D_REPLYTO_="$replyto" -D_SUBJ_="\`$subj'" -D_FROM_="$from" $msgdir/mail-header.m4

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

1249 }

1250 smail_queue_flush() {

1251 # $1=timelimit

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

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

1254 SELECT rowid FROM smailq

1255 GROUP BY rcpts, subj

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

1257 EOF

1258 )

1259 for rid in $rowids; do

1260 sq $workdb \

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

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

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

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

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

1266

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

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

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

1270 .separator "\n" "------------------\n\n"

1271 SELECT time, text FROM smailq

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

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

1274 ORDER by time;

1275 EOF

1276 then

1277 cat <<-EOF | sq $workdb

1278 DELETE FROM smailq

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

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

1281 EOF

1282 fi

1283 fi

1284 done

1285 }

1286 smail_queue() {

1287 # $1=Rcpts, $@=subj

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

1289 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1298 EOF

1299 )

1300 ## XXX: Adhoc load mitigation

1301 case "$now" in

1302 *[01])

1303 err flush_queue=$mintime

1304 smail_queue_flush ;;

1305 esac

1306 fi

1307 }

1308 setviastring() {

1309 table=par

1310 oifs="$IFS"

1311 IFS="&"

1312 for us in $1; do

1313 k=${us%%=*}

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

1315 setpar "$k" "string" "$v"

1316 done

1317 IFS="$oifs"

1318 }

1319 checkdomain() (

1320 # Check the validity of domain by referring DNS

1321 item=$1

1322 err checkdomain $1

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

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

1325 )

1326 pwcheck() {

1327 # $1=passwd

1328 dbpswd=`getpwfield $user pswd`

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

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

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

1332 }

1333 mypwhash() {

1334 mycrypt "`cat`" `genrandom 5`

1335 }

1336 wasureta() {

1337 user=$1

1338 if ! checkdomain $user; then

1339 contenttype; echo

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

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

1342 putfooter

1343 exit 0

1344 fi

1345 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1347 encpswd=`echo $newpswd|mypwhash`

1348 dbsetbyid user $user pswd "$encpswd"

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

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

1351 $msgdir/mail-newaccount.m4 \

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

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

1354 }

1355 checkauth() {

1356 user=`getpar user`

1357 skc=`getpar skey` # from cookie

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

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

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

1361 if chkskey "$skc"; then

1362 return 0

1363 fi

1364 fi

1365 pswd=`getpar pswd`

1366 quser=`sqlquotestr "$user"`

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

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

1369 return 4 # 4=server too heavy

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

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

1372 return 2 # 2=login fail

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

1374 wasureta "$user"

1375 return 1 # wasureta error

1376 fi

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

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

1379 if pwcheck "$pswd"; then

1380 newsession=`genrandom 34`

1381 if setskey "$user" "$newsession" &&

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

1383 gencookie "user=$user" "skey=$newsession"

1384 return 0

1385 else

1386 return 4 # Heavy load??

1387 fi

1388 fi

1389 err "Login failed: [$user]"

1390 return 2 # Password mismatch

1391 }

1392 showlogin() {

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

1394 test -z "$args" && resetskey

1395 s4name=${S4NAME:-s4}

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

1397 cat $layout/login.m4.html

1398 echo '</body></html>'

1399 ) | _m4 \

1400 -D_BODYCLASS_="login" \

1401 -D_TITLE_="$s4name" \

1402 -D_SYSNAME_="Welcome to $s4name" \

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

1404 exit 0

1405 }

1406 dologin() {

1407 checkauth

1408 st=$?

1409 if [ $st != 0 ]; then

1410 contenttype; echo

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

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

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

1414 showlogin # and EXIT

1415 fi

1416 }

1417

1418 # Do instant jobs here

1419 dbsetup

1420 trap cleanup INT HUP EXIT TERM PIPE

1421 # trap cleanup INT HUP

1422

1423 err() {

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

1425 }

1426

1427 cgiinit() {

1428 tmpd=`tmpd=$tmpdir mktempd`

1429 tmpf=$tmpd/stream.$$

1430 tmpfiles=$tmpfiles" $tmpd"

1431 addsession $session

1432 getcookie

1433 case "$REQUEST_METHOD" in

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

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

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

1437 # cat > $tmpf # too much?

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

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

1440 s="`cat $tmpf`"

1441 ;;

1442 esac

1443 case "$CONTENT_TYPE" in

1444 *boundary*)

1445 bndry=${CONTENT_TYPE#*boundary=}

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

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

1448 do

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

1450 #echo u=$us

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

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

1453 # err k=$k v=$v

1454 case "$k" in

1455 *:filename)

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

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

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

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

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

1461 :

1462 fi

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

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

1465 case "$mimetype" in

1466 [Ii]mage/x-xcf)

1467 bzip2 "$tmpd/$v"

1468 v=${v}.bz2

1469 ;;

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

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

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

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

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

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

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

1477 v=$vjpg

1478 else

1479 mimetype="Not supported"

1480 fi

1481 ;;

1482 [Ii]mage/*)

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

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

1485 ;;

1486 esac

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

1488 then

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

1490 continue

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

1492 replpar text string \

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

1494 continue

1495 fi

1496 ;;

1497 *)

1498 type='string'

1499 ;;

1500 esac

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

1502 setpar "$k" "$type" "$v"

1503 done

1504 ;;

1505 *)

1506 setviastring "$s"

1507 ;;

1508 esac

1509 }

1510 email4group() {

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

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

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

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

1515 sql="WITH

1516 grpemails AS (

1517 SELECT gname, user, val email

1518 FROM grp_mem NATURAL JOIN grp_mem_s

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

1520 useremails AS (

1521 SELECT user.name, val email

1522 FROM user

1523 LEFT JOIN user_m

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

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

1526 FROM useremails u LEFT JOIN grpemails g

1527 ON u.name=g.user

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

1529 query "$sql"

1530 }

1531 email4groupbyuid() {

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

1533 g=$1; shift

1534 uids=`echo "$@"`

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

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

1537 email4group "$g" `query "$sql"`

1538 }

1539 myemail4group() {

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

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

1542 }

1543 collectmembersbyid() {

1544 # Collect user names of group specified by grid

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

1546 query "SELECT user FROM grp_mem \

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

1548 }

1549 collectmembersbyid() {

1550 # Collect user names of group name

1551 qgrp=`sqlquote "$1"`

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

1553 }

1554 collectgecosesbyid() {

1555 # Collect user gecoses of group

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

1557 query<<-EOF

1558 SELECT gecos

1559 FROM gecoses

1560 WHERE name IN (SELECT user FROM grp_mem

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

1562 EOF

1563 }

1564 collectemail() (

1565 # Collect email addresses for group $1

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

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

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

1569 for e; do

1570 if isuser "$e"; then

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

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

1573 else

1574 qgrp=`sqlquote "$e"`

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

1576 gmem="grp_mem"

1577 else

1578 tm=`sqlquote "$TEAM"`

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

1580 fi

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

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

1583 $gmem g left join grp_mem_s s

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

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

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

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

1588 query "$sql"

1589 fi

1590 done

1591 )

1592 sendinvitation() (

1593 # $1=email

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

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

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

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

1598 gecos=`gecos`

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

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

1601 _m4 -D_URL_="$urlbase" \

1602 -D_USER_="$name" \

1603 -D_EMAIL_="$1" \

1604 -D_REGIST_="$regist" \

1605 -D_ADMIN_="$admin" \

1606 $msgdir/mail-invite.m4 \

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

1608 return 0

1609 )

1610 emaildomaincheck() {

1611 case "$1" in

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

1613 *@*)

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

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

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

1617 return 2

1618 fi

1619 return 0

1620 ;;

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

1622 esac

1623 }

1624 invite() {

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

1626 case "$email" in

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

1628 *@*)

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

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

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

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

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

1634 elif sendinvitation $email; then

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

1636 else # Cannot be reached here

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

1638 fi ;;

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

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

1641 esac

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

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

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

1645 fi

1646 if [ -f $invite_policy ]; then

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

1648 else

1649 pol="$invite_policy"

1650 fi

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

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

1653 -D_INVITE_POLICY_="$pol" \

1654 $layout/html.m4.html $layout/invite.m4.html

1655 }

1656 regist() {

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

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

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

1660 echo "bye bye" | html p

1661 reutrn

1662 fi

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

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

1665 cat<<EOF

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

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

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

1669 EOF

1670 return

1671 fi

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

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

1674 # Fake login password to wasureta

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

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

1677 wasureta $email

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

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

1680 addsession $1 # for removal after 1 minute

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

1682 $layout/login.m4.html

1683 return

1684 }

1685 group_safename() {

1686 # Convert $1 to safe group name

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

1688 }

1689 groupupdate() {

1690 gname=`getpar gname`

1691 qgname=`sqlquote "$gname"`

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

1693 # See ALSO same job in showgroup()

1694 newgname=`group_safename "$gname"`

1695 err newgname=$newgname

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

1697 err NewGNAME: gname=$newgname

1698 gname=$newgname

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

1700 replpar gname string "$gname"

1701 fi

1702 # Name confliction check

1703 parow=`getpar rowid`

1704 ## err parow=$parow

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

1706 query "BEGIN EXCLUSIVE;"

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

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

1709 if [ $count -gt 0 ]; then

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

1711 query "END;"

1712 return

1713 fi

1714 par2table $formdir/grp.def

1715 query "END TRANSACTION;"

1716 # Remove orphan

1717 : <<EOF

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

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

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

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

1722 EOF

1723 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1727 sql="delete from blog where id in

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

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

1730 query "$sql";

1731 unsetpar tag kwd

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

1733 return # and return

1734 fi

1735 fi

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

1737 fi

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

1739 grid=$(query $sql)

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

1741 grp $grid

1742 }

1743 groupclone() {

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

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

1746 case "$1" in

1747 */noteam)

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

1749 esac

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

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

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

1753 return

1754 fi

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

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

1757 return

1758 fi

1759 i=0

1760 while true; do

1761 copy="-copy$i"

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

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

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

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

1766 i=$((i++))

1767 continue

1768 fi

1769 break

1770 done

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

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

1773 query<<-EOF

1774 BEGIN;

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

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

1777 SELECT $newqname, key, val

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

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

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

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

1782 -- Copy members and their configuration --

1783 REPLACE INTO grp_mem SELECT $newqname, user

1784 FROM grp_mem WHERE gname=$qgrp;

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

1786 FROM grp_mem_s WHERE gname=$qgrp;

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

1788 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1789 -- Copy administrators --

1790 REPLACE INTO grp_adm SELECT $newqname, user

1791 FROM grp_adm WHERE gname=$qgrp;

1792 COMMIT;

1793 EOF

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

1795 STOPCLONEMSG=1 groupconf "$newrowid"

1796 }

1797 groupman() {

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

1799

1800 GF_STAGE="grpconf"

1801 GF_STAGE=groupupdate

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

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

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

1805 -D_DUMPTABLE_="syscmd(cat)" \

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

1807 }

1808 userconf() {

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

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

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

1812 }

1813 groupconf() {

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

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

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

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

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

1819

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

1821 grp=`getgroupbyid $rowid`

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

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

1824 return

1825 fi

1826

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

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

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

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

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

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

1833 <div>

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

1835 <table>

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

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

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

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

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

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

1842 </table>

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

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

1845 </div>

1846 EOF

1847 fi

1848 }

1849 mems() {

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

1851 kwd=`getpar kwd`

1852 listmember $kwd

1853 }

1854 grps() {

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

1856 kwd=`getpar kwd`

1857 listgroup $kwd \

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

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

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

1861 $layout/form+dump.m4.html

1862 }

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

1864 gpg=`getpar grp`

1865 grid=${1:-$gpg}

1866 grp=`getgroupbyid "$grid"`

1867 ## . ./s4-blog.sh

1868 jg=`getpar joingrp`

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

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

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

1872 fi

1873 htmlheader=$layout/html.m4.html

1874 showgroup "$grid"

1875 }

1876 sql4interestblogs() {

1877 cat<<EOF

1878 CREATE TEMPORARY VIEW interestblogs AS

1879 SELECT blog.rowid rid, id, author

1880 FROM blog

1881 NATURAL JOIN

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

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

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

1885 WHEN (SELECT user FROM grp_mem

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

1887 THEN 0

1888 ELSE 1

1889 END;

1890 EOF

1891 }

1892 listnewblogsql() { # $1=user

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

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

1895 newdays=${newdays:-14}

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

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

1898 WHERE user='$user'

1899 AND tblrowid IN

1900 ($blogreadflagrowid,

1901 $blogcutoffflagrowid)),

1902 $basetime -- "0"

1903 );"`

1904 cat<<EOF

1905 `sql4interestblogs`

1906 WITH article_ctime as (

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

1908 FROM article join article_s s using(id)

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

1910 GROUP BY id

1911 ), blog_title_owner as (

1912 SELECT blg.rid brid, id,

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

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

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

1916 ), blogall as (

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

1918 ), news as (

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

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

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

1922 FROM blogall bl

1923 LEFT JOIN

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

1925 ON bl.brid=al.tblrowid

1926 WHERE atime < bl.ctime

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

1928 LIMIT 10

1929 ) SELECT brid LINK, "新着",

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

1931 ctime, title,

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

1933 FROM news;

1934 EOF

1935 }

1936

1937 search_form() {

1938 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

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

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

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

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

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

1944 @week → 最近一週間

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

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

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

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

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

1950 auth=""

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

1952 case "$1" in

1953 author=*)

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

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

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

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

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

1959 ;;

1960 grid=*)

1961 a=`echo "${1#grid=}"`; a=$((0 + $a))

1962 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

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

1964 ;;

1965 esac

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

1967 cat<<-EOF

1968 <div class="right">

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

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

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

1972

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

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

1975

1976 </form>

1977 </div>

1978 EOF

1979 }

1980

1981 imgsrc_cache() (

1982 # $1 = directory for cache'ing

1983 # $2 = table (user_m or grp_m)

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

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

1986 dir="$1" tbl="$2"

1987 keycond="$3"

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

1989 ORDER BY rowid DESC LIMIT 1"

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

1991 tmpf=$tmpd/imgsrc_cache.$$

1992 case "$4" in

1993 [Ss]) size=S ;;

1994 [Oo]) size=O ;;

1995 *) size=M ;;

1996 esac

1997 # ImageCache filename storing schema:

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

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

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

2001 valtype=`query "$sql0"`

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

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

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

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

2006 case "$filetype" in

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

2008 esac

2009 fi

2010 cacheimg_S=$dir/S_$filename

2011 cacheimg_M=$dir/M_$filename

2012 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

2020 else

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

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

2023 fifo2=$fifo.2

2024 mkfifo $fifo $fifo2

2025 fmt=${filename##*.}

2026 ## [[ NOTE ]]

2027 ## a. convert oldimage newimage

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

2029 ## b is much smaller than a

2030 cat $tmpf | unhexize \

2031 | tee $fifo \

2032 | convert -define ${fmt}:size=${iconxy_M} \

2033 -resize ${iconxy_M}'>' - ${fmt}:- \

2034 | tee $fifo2 \

2035 | convert - "$cacheimg_M" &

2036 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2037 -resize ${iconxy_S}'>' - ${fmt}:- \

2038 | convert - "$cacheimg_S" &

2039 printf '%s' "<img src=\"data:${filetype},"

2040 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2041 echo '">'

2042 echo "$sum" > $sumfile

2043 fi

2044 ## Now preparing cache image, done.

2045 ## Store this information to DB

2046 stbl=${tbl%_m}_s # user_s or grp_s

2047 pkey=${keycond%%=*} # Primary Key name

2048 pval=${keycond#*=} # Primary Key value

2049 query <<-EOF

2050 REPLACE INTO $stbl($pkey, key, type, val)

2051 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2052 EOF

2053 )

2054

2055 showhome() {

2056 # $1=userRowIdToShow

2057 err showhome \$1=$1

2058 case "$1" in

2059 *@*) uname=`getvalbypkey user name "$1"` ;;

2060 *) uname=`getvalbyid user name $1` ;;

2061 esac

2062 ## err ShowHome: uname=$uname

2063 td=`getcachedir home/"$1"`

2064 gecos=`gecos "$uname"`

2065 ## err SH:gecos=$gecos

2066 GF_VIEWONLY=1

2067 cond="gname in (select gname from grp_mem where user='$uname')"

2068 search_form_args=""

2069 if [ x"$user" = x"$uname" ]; then

2070 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2071 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> /

2072 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2073 # Display folders

2074 sql="select count(id) from article_m where id

2075 in (select id from article where author='$user')

2076 and type like 'file:%';"

2077 ## err nfile-sql=`echo "$sql"`

2078 nfile=`query "$sql"`

2079 # err nfile=$nfile

2080 if [ $nfile -gt 0 ]; then

2081 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2082 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2083 fi

2084 else

2085 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2086 GROUP BY user;"`

2087 usermenu="<p>Last seen on $latestlog</p>"

2088 search_form_args="author=$uname"

2089 fi

2090 . ./s4-blog.sh

2091

2092 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2093 search_form "$search_form_args" > $sf

2094 printf "%s さん" "$gecos"|htmlescape > $tf

2095 { echo "<div class=\"noprofimg\">"

2096 viewtable $formdir/user.def user $1

2097 echo "</div>"

2098 } > $pf

2099

2100 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2101 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2102 imf=$tmpd/profimg.$$; touch $imf

2103 if [ -n "$img" ]; then

2104 if true; then

2105 tbl=user_m

2106 enticond="name='$uname'"

2107 imgsrc_cache "$td/main" user_m "$enticond" M

2108 else

2109 { printf '%s' "<IMG src=\"data:${img#file:},"

2110 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2111 | sed 's/$..$/%\1/g'

2112 echo '">'

2113 }

2114 fi > $imf

2115 fi

2116 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2117 val='$uname';"`

2118 ## REMOVE This comment block until 2019/7/1

2119 ## err "----- `gdate +%FT%T.%3N` ------------C"

2120 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2121 listblog $uname > $bf

2122 ## unset ddd

2123 ## err "----- `gdate +%FT%T.%3N` ------------D"

2124

2125 hometail=$tmpd/tail.$$

2126 mkfifo $hometail

2127

2128 #Calling listgroupbytable, originally here

2129

2130 (

2131 # Display Most Recent Entry

2132 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2133 shortval=${shortval:-val}

2134

2135 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2136 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2137 ctime,

2138 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2139 (SELECT gecos FROM gecoses

2140 WHERE name=(SELECT val FROM blog_s

2141 WHERE key='owner' AND id=b.id)) owner,

2142 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2143 FROM blog b

2144 JOIN

2145 (SELECT distinct blogid, a.id aid, max(val) ctime

2146 FROM article a, article_s s

2147 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2148 GROUP BY blogid ORDER BY val DESC LIMIT 50

2149 ) m

2150 ON b.id=m.blogid;"

2151 # This should be as follows

2152 : <<EOF

2153 WITH arts AS(

2154 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2155 a.blogid, a.id id, s.val ctime

2156 FROM article a NATURAL JOIN article_s s

2157 WHERE s.key = 'ctime' AND a.author='$user'

2158 GROUP by s.id

2159 )

2160 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2161 FROM arts a0

2162 JOIN

2163 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2164 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2165 ORDER BY ctime DESC LIMIT 50;

2166 EOF

2167

2168 cat<<-EOF

2169 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2170 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2171 <div class="lcto">

2172 `DT_VIEW=replyblog dumptable html blog`

2173 </div>

2174 EOF

2175 unset DT_SQL

2176 if [ x"$user" = x"$uname" ]; then

2177 # Display NEWS

2178 # 2016-06-26

2179 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2180 acclog blog $blogreadflagrowid

2181 # echo "全部既読にしました" | html p

2182 fi

2183 # 2016-02-19 Counting NEWS without using dumptable.

2184 sql=`listnewblogsql "$user"`

2185 # echo "$sql" > tmp/listnew

2186 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2187 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2188 cont=$((cont-1))

2189 err newcount=$cont

2190 if [ $cont -gt 0 ]; then

2191 #echo "全体の新着記事${cont}傑" | html h2

2192 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2193 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2194 cat<<-EOF | html form 'action="?home"'

2195 `cgi_checkbox readchk yes 'id="read"'`<label

2196 for="read">新着ふくめて全部読んだことにする</label>

2197 `cgi_submit '確定'`

2198 `cgi_hidden read yes`

2199 EOF

2200 echo "$new10 "

2201 echo "</div>"

2202 else # If news is 0, set log cut off flag

2203 acclog blog $blogcutoffflagrowid # for speed

2204 fi

2205 else # Not My Home ($user != $uname)

2206 : # DT_SQL=

2207 fi

2208 ) > $hometail & # Is background call safe to m4??

2209 #

2210 listgroupbytable $formdir/grp.def $cond |

2211 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2212 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2213 -D_PROFIMG_="spaste(\`$imf')" \

2214 -D_BLOGS_="spaste(\`$bf')" \

2215 -D_SEARCH_="spaste(\`$sf')" \

2216 -D_NBLOG_="$nblog" \

2217 -D_GROUPS_="syscmd(\`cat')" \

2218 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2219 $layout/html.m4.html $layout/home.m4.html

2220

2221 # Record access log

2222 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2223 }

2224 commission() { # $1=grp-rowid $2=user-rowid

2225 contenttype; echo

2226 ## err commission: "$@"

2227 gname=`getgroupbyid $1`

2228 echo "グループ $gname 管理者委任" \

2229 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2230 if [ -n "$2" ]; then

2231 grp_reg_adm "$@"

2232 else

2233 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2234 fi

2235 }

2236 listgroupbytable() {

2237 # $1=deffile $2...=condition

2238 tagline=`grep :tag: $1`; shift

2239 and="${1:+and }" where=${1:+where }

2240 href="<a href=\"$myname?grp+"

2241 echo '<div class="listgroup">'

2242 NGsql="select distinct tag from\

2243 (select gname, max(case key when 'tag' then val end) as tag, \

2244 max(case key when 'ctime' then val end) as ctime\

2245 from grp_s group by gname order by ctime);"

2246 sql="select val from grp_s where key='tag' $and$* group by val;"

2247 ## err ListGRP: query sql="$sql"

2248 for tag in `query "$sql"`

2249 do

2250 ## err ListGrp: tag=$tag

2251 tn=${tagline%%=${tag}*}

2252 tn=${tn##*[ :]}

2253 sql="select rowid||':'||gname as 'グループ名',説明 from

2254 (select (select rowid from grp g where g.gname=grp_s.gname)

2255 as rowid,

2256 gname,

2257 max(case key when 'gecos' then val end) as '説明',

2258 max(case key when 'tag' then val end) as 'tag',

2259 max(case key when 'mtime' then val end) as mtime from grp_s

2260 $where$* group by gname having tag='$tag' order by mtime desc);"

2261 ## err PersonalGroupList= `echo $sql`

2262 echo "<h2>$tn</h2>"

2263 echo '<table class="b listgroup">'

2264 sq -header -html $db "$sql" \

2265 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2266 echo '</table>'

2267 done

2268 echo '</div>'

2269 }

2270 iconhref() (

2271 # $1=icon-file, $2=Href $3=title $4...=anchor

2272 data=`percenthex "$1"`

2273 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2274 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2275 href=$2; title=$3; shift 3

2276 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2277 )

2278 iconhref2() (

2279 # $1=icon-file, $2=Href $3=title $4...=anchor

2280 src=$1

2281 href=$2; title=$3; shift 3

2282 anchor=`echo $@|htmlescape`

2283 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2284 )

2285 listentry() (

2286 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2287 # Referring variable $iamowner=$grp to attach owner-request links

2288 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2289 cond='' hiddens=''

2290 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2291 if [ -z "$offset" ]; then

2292 offset=`getpar start`; offset=${offset%%[!0-9]*}

2293 offset=$((offset-1))

2294 fi

2295 offset=$((offset + 0)) # change to numeric forcibly

2296 [ $offset -lt 0 ] && offset=0

2297 limit=$listentlimit

2298 dir=`getcachedir "$1"`

2299 if [ x"$1" = x"user" ]; then

2300 hrb="$myname?home"

2301 deficon=person-default.png

2302 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2303 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2304 gcs=gecos

2305 else # if group

2306 hrb="$myname?grp"

2307 deficon=group-default.png

2308 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2309 gcs=name

2310 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2311 if [ -n "$tagline" ]; then

2312 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2313 ## err tagconv=$tagconv

2314 fi

2315 fi

2316 if [ ! -d $dir ]; then

2317 mkdir -p $dir

2318 fi

2319 if [ ! -s $dir/$deficon ]; then

2320 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2321 fi

2322 if [ -n "$2" ]; then

2323 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2324 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2325 fi

2326 tag=`getpar tag` tag2=`getpar tag2`

2327 if [ x"$tag" = x"NULL" ]; then

2328 tag="" tag2=""

2329 fi

2330 if [ -n "$tag$tag2" ]; then

2331 tag=${tag:-$tag2}

2332 qtag=`sqlquote "$tag"`

2333 cond2="tag=$qtag"

2334 fi

2335 if [ -n "$cond1$cond2" ]; then

2336 cond="$cond1${cond2:+ AND $cond2}"

2337 cond="WHERE ${cond# AND }"

2338 fi

2339

2340 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2341 # grpは呼出し元の動的スコープ変数でよくないな...

2342 ##qgrp=`sqlquote $grp`

2343 getgrp="(select gname from grp where rowid=${rowid:--1})"

2344 sql="select a.rowid, a.$link,

2345 coalesce(b.$gcs, a.$nm) as nick,

2346 quote(a.$nm) as qname,

2347 (SELECT val FROM ${tbl}_s

2348 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2349 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2350 || case when a.$nm in (select gname from grp)

2351 then printf('(%d名)',

2352 (select count(user) from grp_mem where gname=a.$nm))

2353 else ' <'||a.$nm||'>'

2354 end

2355 as name,

2356 b.tag,

2357 case when a.$nm in (select user from grp_adm

2358 where gname=$getgrp) then '管理者'

2359 when '$user' in (select user from grp_adm where gname=a.$nm)

2360 then 'ADMIN'

2361 when '$user' in (select user from grp_mem where gname=a.$nm)

2362 then 'Member'

2363 when '$iamowner' = '' then ''

2364 else ',not='||a.rowid end as ownerlink,

2365 CASE '$entity'

2366 WHEN 'グループ'

2367 THEN coalesce(

2368 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2369 'open')

2370 ||

2371 CASE WHEN '$user'

2372 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2373 THEN ' ismember'

2374 ELSE ''

2375 END

2376 ELSE 'user'

2377 END regmode

2378 from $tbl a left join

2379 (select $nm as name,

2380 max(case key when 'gecos' then val end) as gecos,

2381 max(case key when 'tag' then val end) as tag,

2382 max(case key when 'mtime' then val end) as mtime,

2383 max(case key when 'wtime' then val end) as wtime,

2384 max(case key when 'login' then val end) as login

2385 from ${tbl}_s group by $nm)

2386 b on a.$nm=b.name $cond $3

2387 order by b.wtime desc, b.login desc,

2388 b.mtime desc, b.tag desc, a.rowid asc"

2389 # Give precedence to newer maintained groups (2016-09-24)

2390 # Note that mtime is stored only in grp_s.

2391 ## err LE:sql.1="$sql"

2392 total=`query "with x as ($sql) select count(*) from x;"`

2393 echo "${entity} 一覧" | html h2

2394 echo '<div class="listentry">' # List-entry div

2395 # Show owner/member filter button

2396 METHOD=GET

2397 hiddens="$hiddens

2398 `cgi_hidden kwd \"$kwd\"`

2399 `cgi_hidden stage \"$stage\"`"

2400 if [ x"$tbl" = x"grp" ]; then

2401 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2402 fh="<select name=\"tag\">$nl"

2403 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2404 for l in $args; do

2405 val=${l#*=} tname=${l%=*}

2406 if [ x"$val" = x"$tag" ]; then

2407 s=" selected"

2408 selectedtags="(種別[${tname}]のみ)"

2409 else

2410 s=""

2411 fi

2412 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2413 done

2414 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2415 cat<<-EOF

2416 <form action="$myname" method="$METHOD">

2417 </form>

2418 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2419 for="ismembtn">参加中以外隠す</label>

2420 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2421 for="isadmbtn">管理者参加以外隠す</label>

2422 EOF

2423 # limit=3

2424 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2425 fi

2426 if [ $total -gt $limit ]; then

2427 echo '<div>'

2428 METHOD=GET cgi_form $stage <<EOF

2429 $form

2430 <label>次の語を含む${entity}で検索:

2431 `cgi_text kwd "$kwd"`</label>

2432 $hiddens

2433 EOF

2434 echo '</div>'

2435 else

2436 echo $selectedtags | html p

2437 fi

2438 cat<<EOF

2439 <form action="$myname" method="$METHOD">

2440 <p>${total}件中の<input class="hidesub" type="text" name="start"

2441 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2442 <input type="submit" value="確定"></p>

2443 </form>

2444 EOF

2445 if [ $((offset+limit)) -lt $total ]; then

2446 nextbtn=$(

2447 cat<<EOF

2448 <div class="right clear"><form action="$myname" method="$METHOD">

2449 `cgi_submit 次の${limit}件`

2450 $hiddens

2451 `cgi_hidden offset $((offset + limit))`</form></div>

2452 EOF

2453 )

2454 fi

2455 if [ $offset -gt 0 ]; then

2456 prevbtn=$(

2457 cat<<EOF

2458 <form action="$myname" method="$METHOD">

2459 `cgi_submit 前の${limit}件`

2460 $hiddens

2461 `cgi_hidden offset $((offset - limit))`</form>

2462 EOF

2463 )

2464 fi

2465 pnbtn="$nextbtn$prevbtn"

2466 echo $pnbtn

2467

2468 ## err ListEntry: `echo "$sql"\;`

2469 # sq $db here??? 2016-11-28

2470 query "$sql limit $limit ${offset:+offset $offset};" \

2471 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2472 # err name=$name owner=$ownerp lnk=$lnk

2473 # err newlnk=$lnk regmode=$regmode

2474 icondir=$dir/$id

2475 # Pick up only last icon

2476 htmlname=`echo $name|htmlescape`

2477 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2478 <p class=\"tag _$tag\">$tag</p>" \

2479 | _m4 $tagconv

2480 if [ -n "$NOSPEEDUP" ]; then

2481 files=`getvalbyid $tbl profimg $id $icondir`

2482 if [ -n "$files" ]; then

2483 icon=`echo "$files"|tail -1`

2484 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2485 else

2486 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2487 fi

2488 elif [ -n "$icon" -a -s "$icon" ]; then

2489 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2490 else

2491 cond="$nm=$qname"

2492 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2493 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2494 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2495 # err "img=[$img]"

2496 if [ -n "$img" ]; then

2497 echo "<a href=\"$hrb+$lnk\">"

2498 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2499 echo "</a>"

2500 else

2501 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2502 fi

2503 fi

2504 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2505 echo "</div>"

2506 done

2507 echo "</div>" # End of List-entry div

2508 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2509 )

2510 listmember() {

2511 listentry user "$@"

2512 }

2513 listgroup() {

2514 listentry group "$@"

2515 }

2516 hexteams() { # $1=gname, $2(optional)=user

2517 cond=${2:+" AND user='$2'"}

2518 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2519 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2520 }

2521 showgroup() { # $1=group-rowid

2522 if [ -z "$1" ]; then

2523 grid=`getpar grid`

2524 grid=${grid%%[!0-9]*}

2525 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2526 else

2527 grid=$1

2528 fi

2529 grp=`getgroupbyid $grid`

2530 qgrp=`sqlquote "$grp"`

2531 htmlgrp=`echo "$grp"|htmlescape`

2532 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2533 if isgroup "$grp"; then

2534 tf=$tmpd/title.$$

2535 sf=$tmpd/search.$$

2536 bodyclass=`query "SELECT val FROM grp_s

2537 WHERE gname=$qgrp AND key='regmode';"`

2538 if ismember "$user" "$grp"; then

2539 ismember="ismember"

2540 bodyclass="$bodyclass${bodyclass:+ }ismember"

2541 else

2542 ismember="" # bodyclass="group"

2543 fi

2544 bodyclass="$bodyclass grouphome"

2545 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2546 echo "グループ $htmlgrp" > $tf

2547

2548 showgroupsub $formdir/grp.def "$grid" | \

2549 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2550 -D_FORM_="syscmd(\`cat')" \

2551 -D_BODYCLASS_="$bodyclass" \

2552 -D_DUMPTABLE_="" \

2553 $htmlheader $sf $layout/form+dump.m4.html

2554 # $htmlheader $layout/form+dump.m4.html

2555 # $htmlheader is defined in grp()

2556 else # if $grp is removed at par2table

2557 listgroup

2558 fi

2559 }

2560 showgroupsub() {

2561 # $1=def-file $2=group-rowid

2562 # Using $ismember

2563 rowid=$2

2564 grp=`getgroupbyid $2`

2565 qgrp=`sqlquote "$grp"`

2566 td=`getcachedir grp/"$2"`

2567 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2568 if [ -z "$rowid" ]; then

2569 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2570 #grp=`sq $db "select gname from grp where rowid=$grp"`

2571 echo "showgroupsub: invalid argument($1 $2)" | html p

2572 return

2573 fi

2574 val=`getvalbyid grp profimg $rowid $tmpd`

2575 enticond="gname=$qgrp"

2576 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2577 if [ -n "$img" ]; then

2578 cat<<-EOF

2579 <p class="groupimg">

2580 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2581 EOF

2582 fi

2583 echo "<div class=\"noprofimg\">"

2584 viewtable $1 grp $rowid

2585 echo "</div>"

2586 if isgrpowner "$user" "$grp"; then

2587 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2588 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2589 iamowner=$rowid

2590 colmd=" mode"

2591 fi

2592 if [ -n "$ismember" ]; then

2593 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2594 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2595 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2596 cat<<-EOF

2597 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2598 href="?blog+$rowid">グループの新規話題作成</a>

2599 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2600 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2601 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2602 <div class="fold clear">

2603 `cgi_checkbox send yes id="send"`<label

2604 for="send">グループ全員にメッセージ送信</label>

2605 <div>

2606 `cgi_textarea message "" "cols=60"`

2607 `cgi_submit 送信`

2608 `cgi_reset リセット`

2609 </div>

2610 `cgi_hidden grp $rowid`

2611 </div></form>

2612 EOF

2613 fi

2614 # 加入ボタン + 加入者リスト

2615 if [ -n "$ismember" ]; then

2616 ismem='checked' state="(参加中)"

2617 else

2618 nomem='checked' state="(現在非加入)"

2619 fi

2620 # このグループでの加入アドレス

2621 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2622 and key='email';"`

2623 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2624 ## and key='email';"

2625 ##err email=$eml

2626 cat <<EOF

2627 <div class="fold clear">

2628 `cgi_checkbox reg yes id="reg"`<label

2629 for="reg">自身の加入状態を操作する</label>$state

2630 <div>

2631 EOF

2632 cgi_form grp <<EOF

2633 <p>このグループに</p>

2634 <table class="b">

2635 <tr><th>メンバーとして</th><td>

2636 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2637 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2638 <tr><th>参加する場合のメイルアドレス<br>

2639 <small>(メインのアドレスとは違うものにする場合に記入<br>

2640 同じでよい場合は空欄に)</small></th>

2641 <td>`cgi_text email $eml`</td></tr>

2642 </table>

2643 `cgi_hidden grp $rowid`

2644 EOF

2645 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2646 echo "moderated (承認加入の)グループなので実際に参加できるのは

2647 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2648 fi

2649 echo '</div></div>'

2650 echo '<h2>話題一覧</h2>'

2651 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2652 cat<<-EOF

2653 <form class="summary" action="$myname" title="$thelp">

2654 `cgi_hidden owner "$grp"`

2655 `cgi_hidden kwd "@week"`

2656 `cgi_hidden stage searchart`

2657 `cgi_submit "一週間のまとめ"`

2658 </form>

2659 EOF

2660 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2661 colstate="state:稼動状態:frozen=rowclass=凍結"

2662 DT_CHLD=article:blogid \

2663 DT_QOWNER="$qgrp" \

2664 DT_VIEW=replyblog dumptable html blog \

2665 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2666

2667 getgname="(select gname from grp where rowid=$rowid)"

2668 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2669 cm="?commission+$rowid"

2670 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2671 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2672 # team list

2673 hexteams=`hexteams "$grp"`

2674 if [ -n "$hexteams" ]; then

2675 echo "チーム一覧" | html h2

2676 echo '<div class="dumptable"><table class="b">'

2677 sq $db -html -header<<-EOF

2678 SELECT val TEAM,

2679 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2680 MEMBERS

2681 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2682 EOF

2683 echo '</table></div>'

2684 fi

2685 }

2686 grp_getbodyclass() {

2687 # Get css class name for document.

2688 # `moderated' for moderated groups

2689 # `ismember' for groups where user belongs

2690 # $1=GroupName (w/o quote)

2691 # $user=userNameCurrentlyLogin

2692 ## err grp_getbodyclass: 1="$1"

2693 qgrp=`sqlquote "$1"`

2694 query<<-EOF

2695 SELECT coalesce(

2696 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2697 'open')

2698 ||

2699 CASE WHEN '$user'

2700 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2701 THEN ' ismember'

2702 ELSE ''

2703 END;

2704 EOF

2705 }

2706 grpaction() { # $1=group-rowid

2707 err GRP_ACTION:IN

2708 grid=${1:-`getpar grp`}

2709 grp=`getgroupbyid "$grid"`

2710 htmlgrp=`echo "$grp" | htmlescape`

2711 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2712 if [ -z "$grp" ]; then

2713 echo "無効な指定です。" | html p; return

2714 fi

2715 if ! ismember $user "$grp"; then

2716 echo "加入者のみに許可された操作です。" | html p; return

2717 fi

2718 echo "グループ $grp 個別選択操作" \

2719 | _m4 -D_TITLE_="syscmd(\`cat')" \

2720 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2721 $layout/html.m4.html

2722

2723 isowner=""

2724 isgrpowner "$user" "$grp" && isowner="yes"

2725 usel=`getpar usel`

2726 if [ -n "$usel" ]; then

2727 uids=$(echo `echo $usel`|tr ' ' ',')

2728 ## err grpaction-1: grp=$grp, `echo $sql`

2729 text=`getpar text|tr -d '\r'`

2730

2731 rm=`getpar rm` cfm=`getpar confirm`

2732 ## err rm=$rm cfm=$cfm

2733 if [ x"$rm" = x"yes" ]; then

2734 if [ "$isowner" ]; then

2735 if [ x"$rm$cfm" = x"yesyes" ]; then

2736 # Eliminate

2737 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2738 for tbl in grp_mem grp_mem_s grp_mem_m; do

2739 sql="delete from $tbl $cond;"

2740 # echo "sql=$sql"

2741 query "$sql"

2742 err rmGRPuser "$sql"

2743 done

2744 num=`query "select count(*) from user where rowid in ($uids);"`

2745 #err num=$num

2746 if [ 0$num -gt 0 ]; then

2747 sql="select coalesce(b.val,a.name) from user a left join \

2748 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2749 # err `echo "$sql"`

2750 html pre<<EOF

2751 以下の${num}名のグループ $grp 登録を解除しました。

2752 `query "$sql"`

2753 EOF

2754 fi

2755 else

2756 echo "確認のチェックがないのでやめておきます。" | html p

2757 return

2758 fi

2759 else # not Group Owner

2760 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2761 return

2762 fi

2763 cat<<EOF

2764

2765 EOF

2766 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2767 if [ -z "$text" ]; then # if msg is empty

2768 echo "なにかメッセージを..." | html p

2769 return 0

2770 fi

2771 gecos=`gecos $user`

2772 safegc=`echo "$gecos" | tr -d '<>@'`

2773 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2774 fromad=`myemail4group "$grp"`

2775 ###mail_from="$safegc <$fromad>"

2776 mail_from="$safegc <$user>" # TEST: 2020/5/13

2777 test -n `getpar sender` &&

2778 export SENDER=$user # TEST: 2020/5/15

2779 replyto=$fromad

2780

2781 ## Start parse of attachment files

2782 if [ -n "`getpar email`" ]; then

2783 ar=`getpar supprcpt`

2784 if [ -n "$ar" ]; then

2785 for a in $ar; do

2786 if checkdomain "$a"; then

2787 supprcpt="$supprcpt $a"

2788 else

2789 err "SupprcptErr=[$a] by $user"

2790 removercpt="$removercpt $a"

2791 fi

2792 done

2793 fi

2794 subj=`getpar subject`

2795 afiles=""

2796 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2797 do

2798 f=$tmpd/$fn

2799 if [ -s $f ]; then

2800 afiles=$afiles"${afiles:+ }$f"

2801 fi

2802 done

2803 else

2804 preface=$(cat <<-EOF

2805 $url

2806 のグループ「$grp」のメンバーである $gecos さんから、

2807 あなた宛のメッセージです。

2808 ----------------------------------------------------------

2809 EOF

2810 )

2811 fi

2812 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2813 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2814 subj="${subj:-$gecos さんからのメッセージ}"

2815 REPLYTO=$replyto

2816 MAIL_FROM=$mail_from

2817 export REPLYTO SMAIL_TO MAIL_FROM

2818 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2819 for r in $rcpts; do

2820 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2821 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2822 else

2823 # Show sender and rcpts address for guest

2824 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2825 fi

2826 if [ -n "$afiles" ];then

2827 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2828 else

2829 smail "$r" "$subj"

2830 fi <<EOF

2831 ${preface:+$preface$nl}$text

2832 EOF

2833 done

2834 if [ $? = 0 ]; then

2835 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2836 sql="select coalesce(b.val, a.name) from

2837 (select name from user where rowid in ($uids)) a

2838 left join user_s b on a.name=b.name and b.key='gecos';"

2839 html pre<<EOF

2840 `query "$sql"`

2841 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2842 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2843 EOF

2844 err SendDone: `echo $sql`

2845 fi

2846 elif [ x"$rm" = x"commission" ]; then

2847 grp_reg_adm $grid $usel

2848 elif [ x"$rm" = x"addteam" ]; then

2849 team=`getpar team|sed "s/'/''/g"` # for single quotation

2850 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2851 if [ x"$team" != x"$newteam" ]; then

2852 echo "チーム名に使えない文字を除去しました" | html p

2853 team=$newteam

2854 fi

2855 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2856 cat<<-EOF | html p

2857 有効なチーム名を入力してください。

2858 カンマだけ、「なし」という名前は使えません。

2859 EOF

2860 echo "有効なチーム名を入力してください。" | html p

2861 else

2862 grp_add_team $grid "$team" $usel

2863 fi

2864 elif [ x"$rm" = x"rmteam" ]; then

2865 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2866 rmteam=`getpar rmteam|sed "s/'/''/g"`

2867 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2868 gname='$grp' AND user='$user' AND key='team'\

2869 AND val='$rmteam';\"`" ]; then

2870 grp_rm_team $grid "$rmteam" $usel

2871 else

2872 echo "所属していないチームの除去操作はできません。"|html p

2873 fi

2874 else

2875 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2876 fi

2877 fi

2878 fi

2879 # POST count summary

2880 from=`getpar from`; to=`getpar to`

2881 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2882 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2883 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2884 # New entry

2885 sql="WITH mems AS (

2886 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2887 ON gm.user=g.name

2888 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2889 ), target_article AS (

2890 SELECT id FROM article_s

2891 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2892 ), posts AS (

2893 SELECT author, count(author) post

2894 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2895 WHERE blogid IN (SELECT id FROM blog_s

2896 WHERE key='owner'

2897 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2898 AND key='text'

2899 GROUP BY author

2900 ), teams AS (

2901 SELECT user, group_concat(val, ', ') team

2902 FROM grp_mem_m

2903 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2904 AND key='team'

2905 GROUP BY user

2906 ), user_post AS (

2907 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2908 FROM mems m LEFT JOIN posts

2909 ON m.name=posts.author

2910 GROUP by m.rowid

2911 )

2912 SELECT

2913 CASE

2914 WHEN (SELECT user FROM grp_adm

2915 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2916 AND user=up.name) IS NOT NULL

2917 then 'k'

2918 ELSE ''

2919 END || rowid || ',' || gecos NAME,

2920 post POST, team _TEAM_

2921 FROM user_post up LEFT JOIN teams t

2922 ON up.name=t.user

2923 ORDER BY gecos;"

2924 ## err grpaction: "`echo \"$sql\"`"

2925 tf=$tmpd/title.$$

2926 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

2927 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2928 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2929 for=\"cmadmin\">管理者委任</label>

2930 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2931 </p></div>"

2932 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2933 title=\"Shortcut: g${nl}Remove from the Group\"

2934 for=\"conf\">GRP登録解除</label>

2935 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2936 <p>この操作による通知は本人に行きません。

2937 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2938 </div>"

2939 # Get team list to which current user belongs into $hexteams

2940 allhexteams=$(hexteams "$grp")

2941 if [ -n "$isowner" ]; then

2942 myhexteams="$allhexteams" # admin can remove all teams' attr

2943 else

2944 myhexteams=$(hexteams "$grp" "$user")

2945 fi

2946 if [ -n "$myhexteams" ]; then

2947 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2948 title=\"Shortcut: s${nl}Strip a team tag from\"

2949 for=\"cmrmteam\">チーム属性除去</label>

2950 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2951 を除去します: `cgi_checkbox teamconfirm yes` 確認

2952 <p>この操作による通知は本人に行きません。

2953 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2954 </div>

2955 "

2956 fi

2957 stf=$tmpd/selteam.$$

2958 cgi_select_h selteam "5445414d" $allhexteams > $stf

2959 b1='<label> <input type="checkbox" name="usel" value="'

2960 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2961 br='<span id="reverse" title="Reverse Selection"></span>'

2962 #b2='"> <span>' b3='</span></label>'

2963 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2964 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2965 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2966 cgi_form grpaction<<EOF \

2967 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2968 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2969 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2970 | _m4 -D_TITLE_="spaste(\`$tf')" \

2971 -D_SUBTITLE_="チェック後操作ボタン" \

2972 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2973 $layout/form+dump.m4.html \

2974 | _m4 -D_TEAM_="spaste(\`$stf')"

2975 <p>下でチェックした人を対象として:</p>

2976 <div class="foldtabs">

2977 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2978 title="Shortcut: a${nl}Add a team tag to"

2979 for="cmteam">同じチーム属性を付与</label>

2980 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2981 `cgi_datalist_h teams $allhexteams`

2982 </div>

2983 ${rmteammsg}

2984 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2985 title="Shortcut: d${nl}DirectMail to"

2986 for="sendmsg" title="Direct Message">DM送信</label>

2987 <div>

2988 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2989 title="Using email format">email書式を使う</label>

2990 <div class="folded">

2991 <table>

2992 <tr><td>From: </td><td>$user</td></tr>

2993 <tr><td>このFrom:で送る</td>

2994 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2995 </td></tr>

2996 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2997 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2998 <tr><td>ファイル添付: </td>

2999 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3000 </table>

3001 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

3002 </div>

3003 <div>本文:`cgi_textarea text "" cols=72`

3004 </div>

3005 </div>

3006 ${isowner:+$cmmsg$excmsg}

3007 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3008 </div>

3009 <h4>$htmlgrp 参加者一覧</h4>$fromtonote

3010 <table class="td2r thl">

3011 `sq $db -header -html "$sql"`

3012 </table>

3013 `cgi_hidden grp $grid`

3014 `cgi_hidden myuid $myuid id="myuid"`

3015 EOF

3016 }

3017 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3018 # Create TEMPORARY VIEW

3019 query<<EOF

3020 CREATE TEMPORARY VIEW writeusers AS

3021 SELECT DISTINCT author FROM article

3022 WHERE id in (

3023 select id from article where blogid=(select id from blog where rowid=$1)

3024 );

3025 CREATE TEMPORARY VIEW movablegroups AS

3026 SELECT g.rowid growid , g.gname

3027 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3028 ON grp.gname=gm.gname -- そのユーザが属している

3029 AND user='$user') g -- グループに絞る

3030 WHERE (SELECT author FROM writeusers

3031 EXCEPT

3032 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3033 IS NULL;

3034 $2

3035 EOF

3036 }

3037 sql4readableblogs() {

3038 # Create view of blogs that can be readable to $user

3039 # Blog is readable when:

3040 # 1: blog owner is an user

3041 # 2: else, 2.1: owner-group where the $user belongs

3042 # 2.2: else, owner-group is not moderated

3043 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3044 cat<<EOF ## | tee tmp/sql.out

3045 CREATE TEMPORARY VIEW readableblogs AS

3046 SELECT blog.rowid rid, id, author

3047 FROM blog

3048 NATURAL JOIN

3049 (SELECT id,

3050 max(CASE key WHEN 'owner' THEN val END) owner,

3051 max(CASE key WHEN 'mode' THEN val END) mode

3052 FROM blog_s GROUP by id) bs

3053 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3054 THEN 1 -- blog owner is an user, READABLE

3055 WHEN (SELECT val FROM grp_s

3056 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3057 AND

3058 (SELECT user FROM grp_mem

3059 WHERE gname=bs.owner AND user='$user') IS NULL

3060 THEN 0

3061 WHEN mode IN ('quiz', 'enquete')

3062 THEN 0 -- "quiz" mode blog cannot be searched

3063 ELSE 1

3064 END;

3065 EOF

3066 }

3067 editheading() { # $1=rowid-of-heading

3068 rowid=${1%%[!A-Z0-9a-z_]*}

3069 if [ -z "$rowid" ]; then

3070 echo "話題番号が未指定です。" | html p

3071 return

3072 fi

3073 owner=`getvalbyid blog owner $rowid`

3074 title=`getvalbyid blog title $rowid`

3075 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3076 | _m4 -D_TITLE_="修正" \

3077 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3078 -D_BLOGS_="" -D_DUMPTABLE_="" \

3079 -D_FORM_="syscmd(\`cat')" \

3080 $layout/html.m4.html $layout/form+dump.m4.html

3081 # Move to group

3082 if isuser "$owner"; then

3083 crview4article $rowid

3084 n=`query "SELECT count(*) FROM writeusers;"`

3085 ## err N=$n

3086 if [ $((n)) -gt 0 ]; then

3087 ## err ROWID=$rowid

3088 sql="SELECT growid || ':' || gname FROM movablegroups;"

3089 cat<<-EOF

3090 <div class="fold">

3091 `cgi_checkbox mv send id="mv"`<label

3092 for="mv">この話題をグループ所有に移動する</label>

3093 <div>

3094 <form action="?mvart" method="POST" enctype="multipart/form-data">

3095 移動先グループ:

3096 <select name="mv2grp">

3097 EOF

3098 query ".mode html"

3099 query<<-EOF |

3100 $sql

3101 .mode list

3102 EOF

3103 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3104 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3105 cat<<-EOF

3106 </select>

3107 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3108 そのグループに加入しているものに限られます)</p>

3109 <p>`cgi_checkbox cfm yes`<label>確認

3110 (この操作は元に戻すことができません)</label></p>

3111 `cgi_hidden blogrowid $rowid`

3112 `cgi_submit 移動`

3113 `cgi_reset Reset`

3114 </form>

3115 </div>

3116 </div>

3117 EOF

3118 fi

3119 # end of isuser "$owner"

3120 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3121 [ -n "$hexteams" ];}; then

3122 none="`echo なし|hexize`"

3123 cat<<-EOF

3124 <div class="fold">

3125 `cgi_checkbox mv2team send id="mv2team"`<label

3126 for="mv2team">この話題を以下のチームのものにする</label>

3127 <div><p>現在の所属チーム設定:

3128 `query "SELECT

3129 coalesce((SELECT val FROM blog_s

3130 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3131 AND key='team'),

3132 ':なし');"`</p>

3133 <form action="?mvart" method="POST" enctype="multipart/form-data">

3134 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3135 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3136 `cgi_hidden blogrowid $rowid`<br>

3137 `cgi_submit 移動`

3138 `cgi_reset Reset`

3139 </form></div></div>

3140 EOF

3141 fi

3142 }

3143 mvart() { # move diary to some group or team

3144 # or move blog of group to team which belong to the group

3145 blogrowid=`getpar blogrowid`

3146 cfm=`getpar cfm`

3147 ##### echo move blog:$blogrowid to $mv2grp | html p

3148 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3149 . ./s4-blog.sh

3150 if [ -z "$blogrowid" ]; then

3151 echo "無効な指定です(mvart)。" | html p

3152 return

3153 elif [ x"$cfm" != x"yes" ]; then

3154 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3155 elif { mv2grp=`getpar mv2grp`

3156 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3157 [ -n "$mv2grp" ]; }; then

3158 crview4article $blogrowid

3159 ########## TRANSACTION BEGIN

3160 query "BEGIN;"

3161 n=`query "SELECT count(*) FROM writeusers;"`

3162 ## err Nwriteuser=$n

3163 if [ $((n)) -gt 0 ]; then

3164 query<<-EOF

3165 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3166 WHERE key='owner'

3167 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3168 AND $mv2grp IN (SELECT growid FROM movablegroups);

3169 EOF

3170 fi

3171 query "END;"

3172 ########## TRANSACTION END

3173 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3174 [ -n "$mv2team" ];}; then

3175 # blog owner can move it to ANY team

3176 case "$mv2team" in

3177 'なし')

3178 cat<<-EOF

3179 DELETE FROM blog_s

3180 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3181 AND key='team';

3182 EOF

3183 ;;

3184 "") ;;

3185 *)cat<<-EOF

3186 BEGIN;

3187 REPLACE INTO blog_s(id, key, val)

3188 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3189 'team', '$mv2team');

3190 REPLACE INTO blog_s(id, key, val)

3191 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3192 'notify', 'all'); -- Change notify to all

3193 END;

3194 EOF

3195 esac | query

3196 fi

3197 blog_reply $blogrowid

3198 echo yes | html p

3199 }

3200 editart() { # $1=article-rowid $2=blogrowid

3201 rowid=${1%%[!A-Z0-9a-z_]*}

3202 blogrowid=${2%%[!A-Z0-9a-z_]*}

3203 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3204 echo "表示する記事番号が未指定です。" | html p

3205 return

3206 fi

3207 owner=`getvalbyid blog owner $blogrowid`

3208 title=`getvalbyid blog title $blogrowid`

3209 author=`getvalbyid article author $rowid`

3210 ## err EDITart: owner=$owner, author=$author

3211 if isgrpowner "$user" "$owner"; then

3212 : EDIT OK

3213 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3214 echo "本人か所有者しか編集できません." | html p

3215 return

3216 fi

3217 aid=`query "select id from article where rowid=$rowid;"`

3218 tmpout=$tmpd/editart.$$.out

3219 GF_ACTION="?replyblog+$blogrowid#$aid" \

3220 edittable $formdir/article.def article $rowid \

3221 > $tmpout

3222 rm -f /tmp/editart.out

3223 # Cannot use pipelining to m4 with genform() because of stdin stack

3224 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3225 -D_FORM_="syscmd(cat $tmpout)" \

3226 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3227 -D_BLOGS_= -D_DUMPTABLE_= \

3228 $layout/html.m4.html $layout/form+dump.m4.html

3229 }

3230 send2mem() {

3231 rowid=`getpar grp`

3232 rowid=${rowid%%[!0-9]*} # Cleaning

3233 if [ -z "$rowid" ]; then

3234 echo "グループが未指定です。" | html p

3235 return

3236 fi

3237 message=`getpar message`

3238 if [ -z "$message" ]; then

3239 echo "文章を入れてください。" | html p

3240 return

3241 fi

3242 grp=`getgroupbyid $rowid`

3243 members=`collectemail "$grp"`

3244 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3245 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3246 mailfrom="`gecos "$user"` <$mailfrom>"

3247 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3248 msg=$(cat<<-EOF

3249 $urlbase?grp+$rowid

3250 グループ $grp に所属する

3251 `gecos $user` さんよりメッセージ:

3252

3253 $message

3254 EOF

3255 )

3256 # smail rcpt subj (file)

3257 for m in $members; do

3258 echo "$msg" |

3259 MAIL_FROM=$mailfrom \

3260 SENDER=$noreply \

3261 REPLYTO=$mailfrom \

3262 smail "$m" "$sj"

3263 done

3264 cat<<EOF

3265 <p>以下のユーザに送信しました。</p>

3266 <pre>

3267 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3268 </pre>

3269 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3270 EOF

3271 }

3272 joingrpadmit() {

3273 # $1=yes/no $2=session-key

3274 if [ -z "$2" ]; then

3275 echo "bye bye" | html p; return

3276 fi

3277 t_usr=`session=$2 getpar adduser`

3278 t_grp=`session=$2 getpar group`

3279 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3280 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3281 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3282 echo "無効な加入依頼です。" | html p

3283 echo "有効期限が切れたか、

3284 他の管理者がいる場合は処理済みの可能性があります。" | html p

3285 return

3286 fi

3287 if ! isgrpowner "$user" "$t_grp"; then

3288 echo "グループ管理者のみの機能です。" | html p; return

3289 fi

3290 case $1 in

3291 yes) joingrp "$t_grp" "$t_usr" yes ;;

3292 no) joingrp "$t_grp" "$t_usr" no ;;

3293 *)

3294 echo "無効な指定です($1)。" | html p

3295 return ;;

3296 esac

3297 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3298 rcpts="`getgroupadminmails "$t_grp"` $user"

3299 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3300 body="に

3301 $t_usr

3302 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3303 しました。"

3304 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3305 query "delete from session where id='$2';"

3306 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3307 }

3308

3309 joingrprequest() {

3310 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3311 jss="joingrp-`date +%s`-`genrandom 12`"

3312 addsession $jss +${memoplimitdays}days

3313 grpadmins=`getgroupadmins "$1"`

3314 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3315 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3316 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3317 $url

3318 $user さんから

3319 グループ $1

3320 に加入依頼がありました。

3321

3322 承認する:

3323 $urlbase?joingrpadmit+yes+$jss

3324

3325 白紙に戻す:

3326 $urlbase?joingrpadmit+no+$jss

3327 EOF

3328 echo "管理者に加入依頼を出しました。

3329 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3330 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3331 }

3332 joingrp() {

3333 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3334 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3335 if isgrpowner "$user" "$1"; then

3336 isowner="yes"

3337 elif [ -n "$5" ]; then

3338 isowner="yes"

3339 else

3340 isowner=""

3341 fi

3342 ## err jg:isgrpowner: isowner="$isowner"

3343 if [ -n "$isowner" ]; then

3344 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3345 elif [ x"$2" != x"$user" ]; then # if user is not login user

3346 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3347 return

3348 elif [ x"$3" = x"no" ]; then

3349 : # Do not pursue those who leave

3350 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3351 : # Member can change own email address for the joining moderated group

3352 else # adding user is $user itself

3353 case `getgroupattr "$1" regmode` in

3354 moderated)

3355 joingrprequest "$@" # Request only

3356 return

3357 ;;

3358 *)

3359 ;;

3360 esac

3361 fi

3362 qgname=`sqlquote "$1"`

3363 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3364 cond="where gname=$qgname and user='$2'"

3365 if [ x"$3" = x"yes" ]; then

3366 query "replace into grp_mem values($qgname, '$2');"

3367 # Notify joingrp to admin

3368 action="に加入しました。"

3369 if [ -n "$4" ]; then

3370 if msg=`emaildomaincheck "$4"`; then

3371 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3372 'string', '$4', NULL);"

3373 else

3374 echo $msg

3375 fi

3376 else

3377 query "delete from grp_mem_s $cond and key='email';"

3378 fi

3379 if [ -n "$5" ]; then # as ADMIN

3380 # Coming here means newly created group

3381 sql="select case\

3382 when (select count(*) from grp_mem where gname=$qgname)=1\

3383 then (select user from grp_mem\

3384 where gname=$qgname and user='$user')\

3385 else '' end; "

3386 err NewGrpChk: $sql

3387 if [ -n "`query \"$sql\"`" ]; then

3388 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3389 query "replace into grp_adm values($qgname, '$user');"

3390 fi

3391 fi

3392 else

3393 query "begin;

3394 delete from grp_mem $cond;

3395 delete from grp_mem_s $cond;

3396 delete from grp_mem_m $cond;

3397 delete from grp_adm $cond;

3398 delete from grp_adm_s $cond;

3399 delete from grp_adm_m $cond;

3400 end;"

3401 action="から脱退しました。"

3402 fi

3403 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3404 $url?grp+$grid

3405 $user (`gecos $user`)さんが

3406 グループ $1

3407 $action

3408 EOF

3409 }

3410 grp_add_team() (

3411 # $1=grp-rowid $2=team $3...=user-rowid(s)

3412 grp=`getgroupbyid $1`

3413 team=$2; shift; shift

3414 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3415 { echo "BEGIN;"

3416 for user; do

3417 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3418 '$grp',\

3419 (SELECT name FROM user WHERE rowid=$user),\

3420 'team', 'string', '$team');"

3421 done

3422 echo "END;"

3423 } | query

3424 )

3425 grp_rm_team() (

3426 # $1=grp-rowid $2=team $3...=user-rowid(s)

3427 grid=$1

3428 qgrp=$(sqlquote "`getgroupbyid $grid`")

3429 team=$2; shift; shift

3430 [ -z "$grid" -o -z "$team" ] && return

3431 { echo "BEGIN;"

3432 for user; do

3433 echo "DELETE FROM grp_mem_m\

3434 WHERE gname=$qgrp \

3435 AND user=(SELECT name FROM user WHERE rowid=$user)\

3436 AND key='team' AND val='$team';"

3437 done

3438 cat<<-EOF

3439 DELETE FROM blog_s

3440 WHERE rowid=(

3441 SELECT rowid

3442 FROM blog_s a

3443 WHERE key='team'

3444 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3445 AND NOT EXISTS (SELECT * FROM grp_mem_m

3446 WHERE key='team' AND val=a.val -- a.val=team

3447 AND gname = (SELECT val FROM blog_s b

3448 WHERE a.id=b.id AND key='owner')

3449 ));

3450 EOF

3451

3452 echo "END;"

3453 } | query

3454 )

3455 grp_reg_adm() {

3456 # $1=grp-rowid $2...=user-rowid

3457 grid=$1

3458 grp=`getgroupbyid "$1"`

3459 if [ -z "$grp" ]; then

3460 echo "無効なグループIDです" | html p; return

3461 fi

3462 if ! isgrpowner "$user" "$grp"; then

3463 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3464 fi

3465 shift

3466 for urid; do

3467 newadm=`query "select name from user where rowid=$urid;"`

3468 if [ -z "$newadm" ]; then

3469 echo "指定ユーザIDがおかしいようです。" | html p; return

3470 fi

3471 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3472 err ismember $newadm $grp

3473 if ismember $newadm "$grp"; then

3474 # OK, go ahead

3475 getgname="(select gname from grp where rowid=$grid)"

3476 query "replace into grp_adm values($getgname, '$newadm');"

3477 # confirm insertion

3478 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3479 if [ -n "`query \"$sql;\"`" ]; then

3480 echo "追加完了: $newadm" | html p

3481 else

3482 echo "追加失敗($1 $urid)" | html p

3483 fi

3484 fi

3485 showgroup $grid

3486 done

3487 }

3488 dt_rowhack() {

3489 # From: <TR>

3490 # ....

3491 # <TD>rowclass=foo</TD>

3492 # </TR>

3493 # To: <TR class="foo">....<TD>foo</TD></TR>

3494 sed -e '

3495 /^<TR>/ {

3496 :loop

3497 s/\n//

3498 N

3499 /<\/TR>/ {

3500 s/\n//

3501 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3502 n

3503 }

3504 $q

3505 b loop

3506 }'

3507 }

3508 dumptable() {

3509 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3510 # textのフィールドだけ全てダンプにしたほうがいいか

3511 # $DT_VIEW sets link

3512 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3513 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3514 VIEW=${DT_VIEW-replyblog}

3515 if [ -n "$VIEW" ]; then

3516 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3517 fi

3518 sqlfile=$tmpd/dump.sql

3519 : > $sqlfile # ensure to be empty

3520 printf '.mode html\n.header 1\n' > $sqlfile

3521 # $DT_CHLD=ChildTable:BindColumn

3522 if [ -n "$DT_CHLD" ]; then

3523 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3524 cat<<-EOF >> $sqlfile

3525 -- presql

3526 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3527 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3528 EOF

3529 # Speed up counting of new articles

3530 cat<<-EOF >> $sqlfile

3531 -- presql2

3532 DROP TABLE IF EXISTS _counts;

3533 CREATE TEMPORARY TABLE _counts AS

3534 SELECT $_i, count($_i) cnt

3535 FROM $_t GROUP BY $_i;

3536 /* Prepare NEW count table */

3537 CREATE TEMPORARY TABLE _target AS

3538 SELECT b.rowid trowid, b.id

3539 FROM "$2" b JOIN "$2_s" s

3540 ON b.id=s.id AND s.key='owner'

3541 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3542

3543 DROP TABLE IF EXISTS _children;

3544 CREATE TEMPORARY TABLE _children AS

3545 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3546 FROM (SELECT t.trowid, t.id $_i, a.id

3547 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3548 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3549

3550 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3551 DROP TABLE IF EXISTS _news;

3552 DROP VIEW IF EXISTS _news;

3553

3554 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3555 -- INSERT INTO _news

3556 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3557 CREATE TEMPORARY VIEW _news AS

3558 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3559 FROM (SELECT DISTINCT id FROM _target)

3560 a LEFT JOIN

3561 (SELECT $_i, count(ctime) newcnt

3562 FROM _children x

3563 WHERE ctime > coalesce((SELECT time from myacclog

3564 WHERE tblrowid=x.trowid),

3565 '1970-01-01')

3566 GROUP BY $_i) b

3567 ON a.id=b.$_i;

3568 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3569 EOF

3570 # REMOVE next line until 2019/5/1

3571 cntall="(select count($_i) from $_t where $_i=a.id)"

3572 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3573 # REMOVE next assignment until 2019/5/1

3574 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3575 and id in (select id from $_t where $_i=a.id) \

3576 and val > coalesce((select time from myacclog where \

3577 tblrowid=a.rowid),\

3578 '1970-01-01'))"

3579 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3580 cnt="$cntnew as '新着', $cntall as '総数',"

3581 dt_class=" td2r td3r dumpblogs"

3582 fi

3583 # Construct join expression

3584 eav="" scols=""

3585 pk=`gettblpkey $2`

3586 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3587 substr=${substr:-%s}

3588 for col in ${3:-`gettbl_s_cols $2`}; do

3589 valvar=val

3590 case $col in

3591 gecos) scols="$scols${scols:+, }${col#}"

3592 continue ;; # built-in column name

3593 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3594 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3595 case "$as" in

3596 *:*=*) cnd=${as#*:}

3597 h=${cnd%%=*} v=${cnd#*=}

3598 h=`sqlquotestr "$h"`

3599 v=`sqlquotestr "$v"`

3600 valvar="CASE val WHEN $h THEN $v END"

3601 as=${as%%:*} ;;

3602 esac

3603 ;;

3604 *) as=${col} ;;

3605 esac

3606 ss=`printf "$substr" "$valvar"`

3607 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3608 scols="$scols${scols:+, }b.$as"

3609 done

3610 #case author when '$user' then a.rowid else '---' end as ID,

3611 if [ -n "$DT_SQL" ]; then

3612 echo "$DT_SQL"

3613 else

3614 cat<<-EOF

3615 SELECT a.rowid as LINK, $cnt $scols

3616 FROM $2 a LEFT JOIN

3617 (SELECT $pk,$eav,

3618 max(CASE key

3619 WHEN 'owner'

3620 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3621 as gecos

3622 FROM ${2}_s c GROUP BY $pk)

3623 b ON a.$pk=b.$pk $4;

3624 EOF

3625 fi >> $sqlfile

3626 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3627 sqlog<<-EOF

3628 *** SQL-file: $sqlfile ***

3629 `cat $sqlfile`

3630 EOF

3631 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3632 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3633 cat $sqlfile >> tmp/sql

3634 # query ".read $sqlfile" > $tmpd/foo

3635 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3636 cp $tmpd/foo tmp/

3637 err "----- `gdate +%FT%T.%3N` ------------555555"

3638 ## $ddd LINE exists at the end of this function

3639 fi

3640 printf '.mode list\n.header 0\n' >> $sqlfile

3641 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3642 <div>

3643 <div class="dumptable">

3644 <table class="b$dt_class">

3645 `query ".read $sqlfile"`

3646 </table>

3647 </div>

3648 </div>

3649 EOF

3650 ### `query ".read $sqlfile"`

3651 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3652

3653 ## REMOVE THIS!

3654 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3655 }

3656

3657 par2table() (

3658 # copy current parameters of par into destination table

3659 # $1=definition-file

3660 # Using $user and $session

3661 # Return value:

3662 # 0: Stored successfully

3663 # 1: Insufficient fillings

3664 # 2: No permission to modify the record

3665 # 3: Invalid rowid

3666 # 4: SUCCESS to delete

3667 # 5: Stop deletion for lack of confirm check

3668 # 6: Password length too short

3669 # 7: Password mismatch

3670 # 8: Old password incorrect

3671 # 9: Duplicated post

3672 rowid=`getpar rowid`

3673 if [ ! -e $1 ]; then

3674 echo "テーブル定義ファイルが見付かりません" | html p

3675 exit 1

3676 fi

3677 tbl=${1%.def}

3678 tbl=${tbl##*/}

3679 if [ -n "$rowid" ]; then # Modify existing entry

3680 if [ x"$tbl" = x"user" ]; then

3681 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3682 elif [ x"$tbl" = x"grp" ]; then

3683 sql="select gname from $tbl where rowid=$rowid;"

3684 ##err p2t:grp:q $sql

3685 isgrpowner "$user" "`query $sql`" && rowowner=$user

3686 elif [ x"$tbl" = x"blog" ]; then

3687 # Check if owner in blog_s

3688 blogowner=`getvalbyid blog owner "$rowid"`

3689 if isgrpowner "$user" "$blogowner"; then

3690 rowowner=$user

3691 else

3692 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3693 fi

3694 else

3695 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3696 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3697 fi

3698 ### err rowowner=$rowowner

3699 if [ x"$user" != x"$rowowner" ]; then

3700 echo "他人のレコードはいじれないの" | html p

3701 return 2

3702 elif [ -z "$rowowner" ]; then

3703 echo "指定したレコードはないみたい" | html p

3704 return 3

3705 fi

3706 rm=`getpar rm` cfm=`getpar confirm`

3707 # Editing existent entry

3708 if [ x"$rm" = x"yes" ]; then

3709 if [ x"$rm$cfm" = x"yesyes" ]; then

3710 query "delete from $tbl where rowid=$rowid;"

3711 return 4

3712 else

3713 echo "消去確認のチェックがないので消さなかったの..." | html p

3714 return 5

3715 fi

3716 fi

3717 fi

3718

3719 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3720 if [ -n "$rowid" ]; then

3721 # Update of existing record

3722 for col in `gettblcols $tbl`; do

3723 val=`getparquote $col`

3724 [ -z "$val" ] && continue

3725 ## err query "update $tbl set $col=$val where rowid=$rowid"

3726 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3727 sql="update $tbl set $col=$val where rowid=$rowid;"

3728 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3729 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3730 ## User name cannot be changed with interface provided with this

3731 ## script. But we offer the trigger to change owner user

3732 ## of blog_s table.

3733 #err "select quote($col) from $tbl where rowid=$rowid;"

3734 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3735 cat<<-EOF | query

3736 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3737 -- should use EXCLUSIVE transaction outside of this.

3738 SAVEPOINT par2table;

3739 $sql

3740 update blog_s set val=$val

3741 where key='owner' and val=$old;

3742 RELEASE SAVEPOINT par2table;

3743 EOF

3744 ## XX: DIRTY Hack Ends here

3745 ## We should keep blog's owner as a single column which has

3746 ## foreign key constraint with primary key of grp/user.

3747 else

3748 query "$sql"

3749 fi

3750 done

3751 # Then, set up $pval for further insertion of tbl_s and tbl_m

3752 for col in `gettblpkey $tbl`; do

3753 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3754 pval="$pval${pval:+, }\"$val\""

3755 done

3756 else

3757 # New entry

3758 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3759 # Avoid empty repost of article.

3760 if [ x"$tbl" = x"article" ]; then

3761 # If rowid is empty and ID exists in article-table, that is REPOST!

3762 aid=`getpar id`

3763 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3764 if [ -n "$xaid" ]; then

3765 # REPOST of article

3766 html p <<-EOF

3767 書き込み直後のリロードなので上書きを回避します。

3768 最新記事は末尾の「再読み込み」ボタンから見てください。

3769 EOF

3770 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3771 return 9 # STOP Duplicated posting

3772 fi

3773 fi

3774 # Generate values() for primary keys

3775 for col in `gettblpkey $tbl`; do

3776 # Genuine primary keys for _m and _s

3777 val=`getvalquote $tbl $col`

3778 [ -z "$val" ] && continue

3779 pval="$pval${pval:+, }$val"

3780 done

3781 ##err pval=$pval

3782 for col in `gettblfkey $tbl`; do

3783 # args for values() to insertion into master table

3784 val=`getvalquote $tbl $col`

3785 [ -z "$val" ] && continue

3786 formaster=$formaster"${formaster:+, }$val"

3787 done

3788 formaster="$pval${formaster:+, }$formaster"

3789 ## err formaster=$formaster

3790 if [ -z "$formaster" ]; then

3791 echo "項目を全て埋めてください" | html pre

3792 return 1

3793 fi

3794 ## err "replace into $tbl values($formaster);"

3795 query "replace into $tbl values($formaster);"

3796 ## Insertion to master table, done

3797 fi

3798

3799 transaction=$tmpd/sqlfile.sql; touch $transaction

3800 for kt in s m; do

3801 tb2=${tbl}_$kt

3802 for col in `gettbl_${kt}_cols $tbl`; do

3803 ptype=`getpartype $col "limit 1"`

3804

3805 # First, check update of existing entries in _m

3806 if [ $kt = m ]; then

3807 # sessID|address.1.22|string|Somewhere-x.y.z

3808 sql=""

3809 ##err dots from query "select var from par where var like '$col.%';"

3810 for v in `query "select var from par where var like '$col.%';"`; do

3811 # v=address.1.22

3812 st_rowid=${v##*.}

3813 origcol=${v%%.*} # original column derived from

3814 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3815 ##case `getpartype $v` in

3816 ## err CASE `gettbl_coltype $tbl/$origcol` in

3817 ## err edit flag = `getpar action.$v`

3818 case `getpar action.$v` in

3819 rm)

3820 if [ x`getpar confirm.$v` = x"yes" ]; then

3821 newsql="delete from $tb2"

3822 else

3823 echo "削除確認未チェック" | html p

3824 fi ;;

3825 edit)

3826 case `gettbl_coltype $tbl/$origcol` in

3827 image|document|binary)

3828 file=$tmpd/`getparfilename $v`

3829 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3830 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3831 continue

3832 fi

3833 ## err type=file=$file

3834 [ -z "$file" ] && continue

3835 bn=`sqlquotestr "${file##*/}"`

3836 bin="X'"$(hexize "$file")"'"

3837 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3838 type=\"file:$ct\"

3839 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3840 cachedir=`getcachedir "$tbl/$rowid"`

3841 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3842 rm -rf $cachedir

3843 ;;

3844 *)

3845 newsql="update $tb2 set val=(select val from par where var \

3846 like '$col.%.$st_rowid')"

3847 ;;

3848 esac

3849 ;;

3850 *) # maybe "keep", do not modify value

3851 continue

3852 ;;

3853 esac

3854 # err newsql=$newsql

3855 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3856 done

3857

3858 if [ x"$bin" = x"NULL" ]; then

3859 ## err repl:normal sql=`echo $sql`

3860 if [ -n "$transaction" ]; then

3861 cat<<-EOF >> $transaction

3862 $sql

3863 DELETE FROM $tb2 WHERE type='string' AND val='';

3864 EOF

3865 else

3866 query "$sql

3867 delete from $tb2 where type='string' and val='';"

3868 ## err repl:normal done

3869 fi

3870 else

3871 # Binary update line is TOO LONG to pipelining

3872 sqlfile="$tmpd/sqlf.$$"

3873 if [ -n "$transaction" ]; then

3874 echo "$sql" >> $transaction

3875 else

3876 echo "$sql" > $sqlfile

3877 query ".read $sqlfile"

3878 fi

3879 fi

3880 # Rest of kt==m: set multiple mode

3881 nr=`getparcount $col`

3882 else

3883 nr=1 # for kt==s, number of records is 1

3884 fi

3885

3886 i=0

3887 while [ $i -lt $nr ]; do

3888 limit="limit 1 offset $i"

3889 i=$((i+1)) # increase beforehand against continue

3890 val=`getvalquote $tbl $col "$limit"`

3891 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3892 ## err $col=$val

3893 bin=NULL

3894 ## err partype$col=`getpartype $col "$limit"`

3895 case $ptype in

3896 file) file=$tmpd/`getparfilename $col "$limit"`

3897 ## err parfile-$col=$file

3898 [ -z "$file" ] && continue

3899 bin="X'"$(hexize "$file")"'"

3900 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3901 type=\"file:$ct\" ;;

3902 "*"*) continue ;; # foreign table

3903 *) type=\"string\" ;;

3904 esac

3905 case `gettbl_coltype $tbl/$col` in

3906 password) # special care for password

3907 # name={password,pswd1,pswd2}

3908 p1=`getpar pswd1 "$limit"`

3909 if [ -z "$p1" ]; then

3910 continue # SKIP password setting, if p1 is empty

3911 else

3912 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3913 ## err pswd=$pswd

3914 if pwcheck "$pswd"; then

3915 if [ x"$p1" = x"$p2" ]; then

3916 case "$p1" in

3917 ??????????*) ;;

3918 *) echo "パスワードは10字以上にしてください。" | html p

3919 return 6;;

3920 esac

3921 val="\"`echo $p1|mypwhash`\""

3922 else

3923 echo "2つの新パスワード不一致" | html p

3924 return 7

3925 fi

3926 else

3927 echo "旧パスワード違います" | html p

3928 return 8

3929 fi

3930 fi

3931 ;;

3932 esac

3933 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3934 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3935 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3936 if [ x"$bin" = x"NULL" ]; then

3937 ## err Normal-query: `echo $sql`

3938 if [ -n "$transaction" ]; then

3939 echo "$sql" >> $transaction

3940 else

3941 query "$sql"

3942 fi

3943 else

3944 sqlfile="$tmpd/query.$$"

3945 ## err sqlfile=`ls -lF $sqlfile`

3946 if [ -n "$transaction" ]; then

3947 echo "$sql" >> $transaction

3948 else

3949 echo "$sql" > $sqlfile

3950 query ".read $sqlfile"

3951 fi

3952 fi

3953 ## err p2t done

3954 done

3955 done

3956 done

3957 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3958 -- We cannot use transaction here, because groupupdate may use it.

3959 SAVEPOINT pa2table_insert;

3960 .read $transaction

3961 RELEASE SAVEPOINT pa2table_insert;

3962 EOF

3963 return $?

3964 ##err donee

3965 )

3966 genform() {

3967 # $1 = form definition file

3968 # $2, $3 (optional)= table name and ROWID

3969 # If $GF_VIEWONLY set and nonNull, output values without form

3970 # If $GF_ARGS set, use it as content-strings in the form

3971 # If $GF_OWNER set, use it as value of name="owner"

3972 # If $GF_STAGE set, use it as value of name="stage"

3973 forms="" hiddens="" rowid=$3

3974 if [ ! -e "$1" ]; then

3975 echo "そのようなデータベースはないようです($2)。" | html p

3976 return

3977 elif [ -n "$2" ]; then

3978 rec=`query "select * from $2 where rowid='$rowid';"`

3979 if [ -z "$rec" ]; then

3980 pk=`gettblpkey $2`

3981 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3982 rec=`query "select rowid from $2 where $pk='$rowid';"`

3983 rowid=$rec

3984 rec=$3

3985 fi

3986 if [ -z "$rec" ]; then

3987 echo "そんなレコードはないみたいね..." | html p

3988 return

3989 fi

3990 fi

3991 if [ -z "$GF_VIEWONLY" ]; then

3992 rm='<input id="rm" name="rm" type="checkbox"

3993 value="yes"><label for="rm">このエントリの削除</label>

3994 <span>ほんとうに消しますよ(確認)!

3995 <input name="confirm" type=checkbox value="yes">はい</span>'

3996 fi

3997 # Image Cache dir

3998 ## err genform: getcache=$2/$rowid

3999 td=`getcachedir "$2/$rowid"`

4000 while IFS=: read prompt name keytype type args; do

4001 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4002 sp="${args:+ }"

4003 form="" val=""

4004 if [ -n "$rowid" ]; then

4005 # err genform2a: Seeking for "$2.$name, type=$type"

4006 rawval=`getvalbyid $2 $name $rowid $td`

4007 val=`echo "$rawval"|htmlescape`

4008 ## err genform3a: getvalbyid $2 $name $rowid $td

4009 ## err genform3b: val="[$val]" type="$type"

4010 fi

4011 if [ -n "$GF_VIEWONLY" ]; then

4012 is_hidden "$2" "$name" && continue

4013 fi

4014 case "$type" in

4015 text*)

4016 cgiform=cgi_multi_$type

4017 if [ -s $td/$name.count -a -n "$val" ]; then

4018 form=`$cgiform $name $td`

4019 val=$(echo "$val"|

4020 while read fn; do

4021 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4022 </td></tr>$nl"

4023 done)

4024 val="<table>$nl$val$nl</table>"

4025 else

4026 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4027 form=`cgi_$type $name "$rawval" "$args"`

4028 fi

4029 ;;

4030 [Rr][Aa][Dd][Ii][Oo])

4031 fh="<label><input type=\"radio\" name=\"$name\""

4032 form="`echo $args|sed -e \

4033 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4034 ;;

4035 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4036 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4037 ;;

4038 [Ss][Ee][Ll][Ee][Cc][Tt])

4039 fh="<select name=\"$name\">$nl"

4040 form=$(for l in $args; do

4041 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4042 done)

4043 if [ -n "$val" ]; then

4044 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4045 fi

4046 form="$fh$form</select>"

4047 ;;

4048 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4049 if [ -s $td/$name.count ]; then

4050 form=`cgi_multi_file $name $td "$args"`

4051 if [ -n "$val" ]; then

4052 hrfb="$myname?showattc+$2_m"

4053 val=$(echo "$rawval" \

4054 | while read fn; do

4055 data=`percenthex "$td/$fn"`

4056 #ct=`cat $td/$fn.content-type`

4057 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4058 ri=`cat "$td/$fn.rowid"`

4059 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4060 #imgsrc="<img src=\"data:$ct,$data\">"

4061 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4062 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4063 done)

4064 fi

4065 else

4066 form="<input type=\"file\" name=\"$name\" $args>"

4067 if [ -n "$val" ]; then

4068 imgs=$(echo "$rawval"\

4069 |while read fn;do

4070 data=`percenthex "$td/$fn"`

4071 echo "<img src=\"data:image/png,$data\">$fn<br>"

4072 done)

4073 form=$form"<br>$imgs"

4074 val=$imgs # 2015-06-15

4075 else

4076 form="<input type=\"file\" name=\"$name\" $args>"

4077 fi

4078 fi

4079 ;;

4080 [Hh][Ii][Dd][Dd][Ee][Nn])

4081 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4082 args="value=\"$GF_STAGE\""

4083 fi

4084 form="<input type=\"hidden\" name=\"$name\" $args>"

4085 prompt='' # Remove prompt

4086 ;;

4087 [Aa][Uu][Tt][Hh][Oo][Rr])

4088 [ -n "$GF_VIEWONLY" ] && continue

4089 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4090 prompt="" ;;

4091 [Oo][Ww][Nn][Ee][Rr])

4092 [ -n "$GF_VIEWONLY" ] && continue

4093 val=${GF_OWNER:-$val}

4094 val=${val:-$user}

4095 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4096 prompt="" ;;

4097 [Uu][Ss][Ee][Rr])

4098 # XXX: is null $user ok?

4099 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4100 [ -n "$GF_VIEWONLY" ] && continue

4101 form="$user"

4102 ;;

4103 [Pp]assword)

4104 [ -n "$GF_VIEWONLY" ] && continue

4105 form="`cgi_passwd`"

4106 val=""

4107 ;;

4108 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4109 [ -n "$GF_VIEWONLY" ] && continue

4110 if [ -z "$rowid" ]; then

4111 val=`genserial`

4112 fi

4113 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4114 prompt="" ;;

4115 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4116 prompt=""

4117 ;;

4118 parent|path|blog*)

4119 prompt=""

4120 ;;

4121 "*"*)

4122 tail=$tail"``"

4123 continue ;;

4124 esac

4125 if [ -n "$prompt" ]; then

4126 if [ -n "${GF_VIEWONLY}" ]; then

4127 form=$val

4128 else

4129 :

4130 fi

4131 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4132 else

4133 hiddens=$hiddens$nl"$form"

4134 fi

4135 done < $1

4136 # enctype="multipart/form-data"

4137 cat<<EOF

4138 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4139 ${rowid:+$rm}

4140 <table class="b $2">

4141 $forms

4142 </table>$hiddens

4143 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4144 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4145 EOF

4146 if [ -z $GF_VIEWONLY ]; then

4147 cat<<EOF

4148 <input type="submit" name="sub" value="OK">

4149 <input type="reset" name="res" value="Reset">

4150 EOF

4151 fi

4152 cat<<EOF

4153 $GF_ARGS</form>

4154 $tail

4155 EOF

4156 }

4157 edittable() {

4158 # $1=form-def $2=table $3 rowid

4159 genform "$@"

4160 }

4161 viewtable() {

4162 GF_VIEWONLY=1 genform "$@"

4163 }

4164 showattc() {

4165 # $1=table_m $2=rowid &optional $3=RawFlag

4166 ## err \$1=$1 \$2=$2 \$3=$3

4167 if ! isfilereadable $user $1 $2; then

4168 contenttype; echo

4169 echo "このファイルは管理者のみしか見られません" | html p

4170 putfooter; exit

4171 fi

4172 idir=`umask 002; mktempd` || exit 1

4173 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4174 bin=$idir/$myname-$$.bin

4175 sql="select quote(bin) from $1 where rowid='$2';"

4176 ## err showattc: sql: $sql

4177 sq $db "$sql" | unhexize > $bin

4178 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4179 type=${tv%//*} fn=${tv#*//}

4180 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4181 ct=${type#file:}

4182 case $ct in # all text/* changed to text/plain

4183 text/*)

4184 charset=`nkf -g $bin|cut -d' ' -f1`

4185 case $charset in

4186 ASCII*) charset="" ;;

4187 esac

4188 if [ -z "$3" ]; then

4189 ct="text/html${charset:+; charset=$charset}"

4190 link="?showattc+$1+$2+raw"

4191 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4192 | sed 's,^,<span></span>,' \

4193 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4194 -D_LINK_="$link" \

4195 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4196 exit $?

4197 fi

4198 ct="text/plain${charset:+; charset=$charset}"

4199 ;;

4200 esac

4201 contenttype "$ct"

4202 echo "Content-Disposition: filename=\"$fn\""

4203 echo "Content-Length: " `cat $bin | wc -c`; echo

4204 #echo "Content-Type: " ${type#file:}; echo

4205 cat $bin

4206 }

4207 #

4208 # Some default stupid handler on CGI values

4209 #

4210 default_storedb() {

4211 # ARG: $1=table-def-file

4212 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4213 tbl=`basename $1`

4214 tbl=${tbl%.def}

4215 cols="`grep :text $1|cut -d: -f2`"

4216 col=`echo "$cols"|head -1`

4217 vcol=`getpar $col`

4218 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4219 if [ -n "$vcol" ]; then

4220 par2table $1

4221 else

4222 return 2 # No insertion occurred

4223 fi

4224 }

4225

4226 default_view() { # $1=def-file

4227 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4228 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4229 default_storedb "$@"

4230 query "select rowid from $tbl order by rowid desc;" \

4231 | while read rowid; do

4232 viewtable $1 $tbl $rowid

4233 done | _m4 -D_TITLE_="$tbl" \

4234 -D_FORM_="`genform $1`" \

4235 -D_DUMPTABLE_="syscmd(cat)" \

4236 $layout/html.m4.html $layout/form+dump.m4.html

4237 }

4238 default_viewtext() { # $1=def-file

4239 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4240 default_storedb "$@"

4241 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4242 | _m4 -D_TITLE_="$tbl" \

4243 -D_FORM_="`genform $1`" \

4244 -D_DUMPTABLE_="syscmd(cat)" \

4245 $layout/html.m4.html $layout/form+dump.m4.html

4246 }

4247 default_smail() {

4248 default_storedb "$@"

4249 if [ $? -eq 2 ]; then

4250 _m4 -D_TITLE_="入力" \

4251 -D_FORM_="`genform $1`" \

4252 -D_DUMPTABLE_="" \

4253 $layout/html.m4.html $layout/form+dump.m4.html

4254 return

4255 fi

4256 cond=""

4257 for pk in `gettblpkey $tbl`; do

4258 pv=$(sqlquote "$(getpar $pk)")

4259 cond="$cond${cond:+ and }$pk=$pv"

4260 done

4261 sql="select rowid from $tbl where $cond;"

4262 rowid=`query "$sql"`

4263 ## err smail1 - "$sql" "-> rowid=$rowid"

4264

4265 while IFS=: read prompt name keytype type args; do # Read from $1

4266 val=`getpar $name`

4267 if [ -n "$val" ]; then

4268 text="$text

4269 $prompt

4270 $name=$val

4271 ---------------------------------------------------------"

4272 fi

4273 case "$type" in

4274 image|document|file)

4275 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4276 fns=$(echo "$fn"|while read fn; do

4277 err mv $tmpd/$fn.orig $tmpd/$fn

4278 mv $tmpd/$fn.orig $tmpd/$fn

4279 rm $tmpd/$fn.rowid # Remove cache flag

4280 ## err "`ls $tmpd/$fn`"

4281 echo $fn

4282 done)

4283 files="$files $fns"

4284 ;;

4285 esac

4286 done < $1

4287 ## err FILES=$files "`ls -lF $tmpd`"

4288 subj="from ${REMOTE_ADDR}"

4289 (echo "$url"

4290 echo "への書き込みがありました。"

4291 echo "------"

4292 echo "$text"

4293 ) | (cd $tmpd &&

4294 err LS="`ls -lF`" &&

4295 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4296 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4297 echo "以下の内容で送信しました。" | html p

4298 viewtable $1 $tbl \

4299 `query "select rowid from $tbl order by rowid desc limit 1;"`

4300 echo "戻る" | html a "href=\"?\""

4301 }