s4: f86dac0373b9 s4-funcs.sh

s4

view s4-funcs.sh @ 769:f86dac0373b9

Escape $gecos in showhome()

author	HIROSE Yuuji <yuuji@gentei.org>
date	Mon, 08 Jun 2020 20:14:01 +0900
parents	891f1f5a8153
children	c06c2df8a25a 2c5a8c5dd1ee

line source

1 #!/bin/sh

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

3 # $HGid$

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

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

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

9 myargs="$@"

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

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

12 tmpdir=${TMPDIR:-tmp}

13 dbdir=${DBDIR:-db}

14 tmpfiles=""

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

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

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

18 workdb=$dbdir/tmpdata.sq3

19 sessdb=$dbdir/sess.sq3

20 sesstb=tmp.sess

21 listentlimit=${LISTENTLIMIT:-30}

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

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

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

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

26 templ=${TEMPL:-templ}

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

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

29 imgdir=${IMGDIR:-img}

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

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

32 msgdir=$templ/msg

33 timeout="+2 days"

34 memoplimitdays="7"

35 dumpcollen=22

36 #thumbxy=120x120

37 thumbxy=96x96

38 iconxy_S=80x80

39 iconxy_M=400x400

40 maximagexy=1600x1600

41 ### maximagexy=400x400

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

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

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

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

47 file_accept_help="

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

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

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

51 "

52 file_warn="$file_accept_help

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

54 blogreadflagrowid=0

55 blogcutoffflagrowid=-1

56 whatsnewdays=${WHATS_NEW_DAYS:-14}

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

58 session=$main_session

60 tconfs=""

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

62 conftbl=_tblconf

63 nl="

64 "

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

66 iconcachekey="profimgcache_S"

67 case "$HTTP_USER_AGENT" in

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

69 *) touchpanel="" ;;

70 esac

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

73 : <<EOF

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

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

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

78 レポート提出管理

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

81 [2016]

82 7/12 根本への反省

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

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

86 [以下2015]

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

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

89 Web

90 締切設定

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

93 ○自分の提出物リスト

95 7/19 ○設置

96 ○一斉送信

97 ○getparfilename の tmpd の扱い

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

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

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

101 ○添付ファイル回収

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

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

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

105

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

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

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

109 ○レポート提出モード

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

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

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

113

114

115 7/9 ○管理者の追加

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

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

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

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

120

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

122

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

124 ○グループ検索

125

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

127

128

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

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

131

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

133 ○6/1 par2tableを triplex 対応に

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

135 →とすると一気に

136

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

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

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

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

141

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

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

144 ## 1. passwd

145 ## 2. grp

146 ## 3. grp_mem

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

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

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

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

151

152 ## ● listの定義:

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

154 ## ● hashの定義:

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

156

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

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

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

160 ## csv2sq3 で .csv.sq3 の Makefile

161

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

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

164 ## type := root | comment

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

166 ## unique(id, filename)

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

168

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

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

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

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

173 ## できたー!

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

175 ↓

176 ↓以下に変更

177 with recursive allmem as

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

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

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

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

182

183

184 with recursive allmem as

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

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

187 where allmem.val=grp_m.gname)

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

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

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

191

192

193 ## triggerもできた。

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

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

196

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

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

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

200 ## グループ管理

201 ## 個人情報管理

202 ##

203

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

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

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

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

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

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

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

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

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

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

214

215 ## ログテーブル

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

217

218

219

220 ■表設計

221 * 3つの表に分散管理

222 id格納表 + hash表 + list表

223 * *_s *_m

224

225

226

227 user, user_map, user_col

228

229 ■抽象エントリタイプ

230 * user

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

232 * group

233 権限判定に利用

234 * serial

235 自動idとして機能

236 * password

237 入力 type=passwordで入力

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

239 * session

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

241 * text

242 入力 type=text

243 * textarea

244 入力 textarea

245 * image|document

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

247 * owner

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

249 * gowner

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

251 * timestamp

252 datetime()

253 * parent

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

255 * path

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

257

258 格納タイプ

259 * list

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

261 * hash

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

263

264 オブジェクトタイプ

265 * entry

266 id, title, owner

267 * textpart

268 id, parentID, text

269 * binarypart

270 id, parentID, contenttype, filename, content

271 * content

272 hash(textpart), list(binarypart)

273 * topic

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

275 * reply

276 id, parentID, content

277 * blog

278 list(entry)

279 blog = [topic, list(reply)]

280

281

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

283 "text" => "hogehoge ..",

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

285 "author" => "taro",

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

287 "parent" => "/",

288 "path" => "/1",

289 "text" => "blah, blah, ....",

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

291 {"serial" => 2,

292 "author" => "hanako",

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

294 "parent" => "/",

295 "path" => "/2",

296 "text" => "blah, blah, ....",

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

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

299

300

301 user:=

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

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

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

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

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

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

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

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

310

311 変換表

312 /user/email=m

313

314 blog:=

315 シリアル:id:p:serial

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

317 所有者:owner:s:owner:

318 時刻:ctime:s:stamp:

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

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

321

322 article:=

323 シリアル:id:p:serial

324 筆者:author:s:owner

325 時刻:ctime:s:stamp:

326 参照元:parent:s:parent:

327 パス:path:s:path:

328 本文:text:s:textarea:

329 画像:image:m:image:

330

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

332

333

334 EOF

335

336 logstart() {

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

338 }

339 logend() {

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

341 }

342 sqlog() {

343 logstart

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

345 cat >> $querylog

346 else

347 echo "$*" >> $querylog

348 fi

349 logend

350 }

351 sq() {

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

353 logstart

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

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

356 else

357 echo "$@" >> $querylog

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

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

360 fi

361 logend

362 }

363 dbsetup() {

364 pipedir=$tmpdir/pipedir

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

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

367 suf=`date +%s`

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

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

370 mkfifo $sqi $sqo

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

372 sq $db < $sqi &

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

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

375 exec 2>> $tmpdir/error.out

376 fi

377 exec 3>> $tmpdir/debug.out

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

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

380 rm $sqi

381 # Attach supplemental DB

382 cat >&5 <<-EOF

383 .output /dev/null

384 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

388 .output stdout

389 EOF

390 }

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

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

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

394 fi

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

396 }

397 cleanup() {

398 trap '' INT HUP EXIT TERM PIPE

399 echo .quit >&5

400 kill $sq3pid

401 kill $sq3pid

402 rm -f $sqo $sqi

403 rm -rf $tmpfiles

404 cleanup2

405 }

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

407 # simple redirection for the sake of speed.

408 query() {

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

410 echo ".output $sqo" >&5

411 logstart

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

413 tee -a $querylog

414 else

415 echo "$@" >> $querylog

416 echo "$@"

417 fi >&5

418 echo ".output stdout" >&5

419 cat $sqo

420 rc=$?

421 logend

422 return $rc

423 }

424 _m4() {

425 #S4NAME=f,f,f

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

427 }

428 ismember() {

429 # $1=user, $2=group

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

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

432 }

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

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

435 }

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

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

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

439 }

440 isgrpowner() (

441 # $1=user, $2=group

442 gn=`sqlquote "$2"`

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

444 err isgrpowner: $sql

445 test -n "`query $sql`"

446 )

447 isgrpownerbygid() (

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

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

450 err isgrpownerbygid: $sql

451 test -n "`query $sql`"

452 )

453 getgroupadminmails() {

454 # $1=group

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

456 email4group "$1" "$i" ;

457 done

458 }

459 getgroupadmins() { # $1=group

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

461 qgrp=`sqlquote "$1"`

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

463 }

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

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

466 getvalbyid grp $2 \

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

468 }

469 getgroupbyid() {

470 # $1=id|gname

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

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

473 # err ggbyid: `echo $sql`

474 query $sql

475 }

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

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

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

479

480 # Return true when anonymous mode

481 [ "$anonymousmode" ] && return 0

482 # case `getvalbyid blog mode $2` in

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

484 # *closed*)

485 # owner=`getvalbyid blog owner $2`

486 # if isgrp $owner; then

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

488 # elif isuser $owner; then

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

490 # fi

491 # esac

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

493 sql="with getblog as (

494 select key,val from blog_s where id=(

495 select blogid from article where id in

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

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

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

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

500 isgrpadm as (select user from grp_adm where

501 gname=(select val from getowner) and

502 user='$1'),

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

504 select case

505 when (select author from article where

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

507 then 'author'

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

509 then 'open'

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

511 then CASE

512 WHEN (SELECT val FROM isgrp) IS NULL

513 THEN

514 CASE WHEN (SELECT val from getowner)

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

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

517 ELSE ''

518 END

519 WHEN (select user from isgrpadm) IS NOT NULL

520 THEN 'i-am-admin'

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

522 END

523 when (select val from getmode) is null

524 then 'open'

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

526 then (SELECT user FROM isgrpadm)

527 when (select author from article where

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

529 then 'user+author'

530 else '' end;"

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

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

533 # result=`query "$sql"`

534 # err FileAccessibility=$result

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

536 }

537 linkhome() {

538 # $1=UserOrGroupRowid

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

540 if isuser $1; then

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

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

543 name=`gecos $1|htmlescape`

544 else

545 _grid=`numericalize "$1"`

546 echo -n "grp+$1"

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

548 fi

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

550 }

551 hreflink() {

552 # s4 specific notation:

553 # ^href=URL

554 # ^iframe=URL

555 # ^video=URL

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

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

558 # OSM umap Wikistyle Notation:

559 # [[URL]] - Simle Link

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

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

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

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

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

565 # Other Style

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

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

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

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

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

571 # SPC+SPC+$ - <br>

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

573 checkboxON="${cb} checked>"

574 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

599

600 }

601 minitbl() {

602 sed -n '

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

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

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

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

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

608 H; # Concat this line to HoldSpace

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

610 $ b cont

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

612 }

613 :cont

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

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

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

617 # . of ^. is workaround for FreeBSD sed

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

619 p; # Print whole "table" element

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

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

622 }

623 x; # Back to the newest line

624 p; # Print rest' | miniul

625 }

626 miniul() {

627 sed -e '

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

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

630 :top

631 s/\n//;

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

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

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

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

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

637 $ b cont

638 N; # 次の行を読む

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

640 /^ /b top

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

642 p; s/.*//;

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

644 /^\* /b top

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

646 }

647

648 :cont

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

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

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

652 H; x

653 }

654 x'

655 }

656 acclog() (

657 # $1=table, $2=rowid

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

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

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

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

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

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

664 fi

665 )

666 gecos() (

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

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

669 )

670 setpar() {

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

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

673 case "$1" in

674 user) _user="$v" ;;

675 skey) _skey="$v" ;;

676 esac

677 fi

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

679 }

680 unsetpar() {

681 for i; do

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

683 case "$i" in

684 user|skey) unset _$i ;;

685 esac

686 fi

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

688 done

689 }

690 replpar() {

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

692 }

693 getpar() {

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

695 val=""

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

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

698 user) val=$_user ;;

699 skey) val=$_skey ;;

700 esac

701 fi

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

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

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

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

706 fi

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

708 case "$var" in

709 owner)

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

711 echo $user; return

712 elif ismember $user $val; then

713 echo $val; return

714 fi ;;

715 esac

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

717 echo "$val"

718 }

719 setskey() {

720 # For quick response...(?)

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

722 }

723 chkskey() {

724 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

730 return 0

731 fi

732 return 1

733 }

734 resetskey() {

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

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

737 fi

738 }

739 getpartype() {

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

741 }

742 getparcount() {

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

744 }

745 getparfilename() {

746 # null if type of $1 is not file

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

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

749 }

750 sqlquote() {

751 (v="$1"

752 case "$v" in

753 "") return ;; # null

754 "X'"*) # quoted hex string

755 echo $1 ;;

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

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

758 echo "\"$v\""

759 return ;;

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

761 echo "\"$v\""

762 return ;;

763 *)

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

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

766 else

767 echo "\"$v\""

768 fi ;;

769 esac)

770 }

771 sqlquotestr() (

772 case "$1" in

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

774 echo "'$v'" ;;

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

776 esac

777 )

778 mktempd() {

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

780 }

781 getcachedir() { # $1=maintable

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

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

784 else

785 echo $tmpd/$thumbxy

786 fi

787 }

788 getval() {

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

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

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

792 echo "$user" ;;

793 stamp|datetime)

794 date "+%F %T" ;;

795 serial)

796 (s=`getpar $2`

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

798 *)

799 getpar "$2" "$3";;

800 esac

801 }

802

803 getvalquote() {

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

805 (v=`getval "$@"`

806 case "$v" in

807 "") echo NULL ;;

808 *) sqlquote "$v" ;;

809 esac)

810 }

811 getparquote() {

812 sqlquote "`getpar $1`"

813 }

814 getbinbyid() {

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

816

817 }

818 getvalbyid() {

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

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

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

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

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

824

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

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

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

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

829 return

830 fi

831 done

832 rowid=$3

833 pk=`gettblpkey $1`

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

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

836 td=${4:-$tmpd}

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

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

839 for kt in s m; do

840 t=${1}_$kt

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

842 vcount=1 # count(val)

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

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

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

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

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

848 if [ $kt = m ]; then

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

850 # Reset val to store filenames if type is string

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

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

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

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

855 i=0

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

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

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

859 i=$((i+1))

860 fn=$c.$i

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

862 case $type in

863 file:*)

864 #file=$td/$val

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

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

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

868 # FOR SPEED: Skip file generation if imgcache exists

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

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

871 && continue

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

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

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

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

876 select rowid from $cond $slice;

877 .output '$td/$fn'

878 select val from $cond $slice;

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

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

881 .output stdout

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

883 EOF

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

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

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

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

888 ls -lh "$file" |

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

890 case "$type" in

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

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

893 *|*:[Aa]pplication*)

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

895 png:- > "$file"

896 ;;

897 esac

898 ;;

899 *)

900 sq $db<<EOF

901 .output $td/$fn.rowid

902 select rowid from $cond $slice;

903 .output $td/$fn

904 select val from $cond $slice;

905 EOF

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

907 ;;

908 esac

909 done

910 else

911 rm -f $td/$c.count

912 case $type in

913 file:*)

914 echo "$val" \

915 | while read fn; do

916 file=$td/$fn

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

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

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

920 | unhexize > "$file"

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

922 case $type in

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

924 *:[Aa]pplication*)

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

926 png:- > $file ;;

927 esac

928 fi

929 done

930 ;;

931 esac

932 fi

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

934 return

935 fi

936 done

937 done)

938 }

939 getvalbypkey() (

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

941 pk=`gettblpkey $1`

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

943 getvalbyid "$1" "$2" $rowid $4

944 )

945 getvalbycond() {

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

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

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

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

950 getvalbyid "$1" "$2" $rowid "$4"

951 fi

952 }

953 getpwfield() {

954 # getpwfield user column

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

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

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

958 echo "$val"

959 return 0

960 else

961 return 1

962 fi

963 }

964 numericalize() {

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

966 }

967 encode() {

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

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

970 sha1=sha1

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

972 sha1=sha1sum

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

974 sha1=gsha1sum

975 fi

976 fi

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

978 }

979 enjpeg() {

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

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

982 cjpeg="cjpeg"

983 else

984 cjpeg="convert - jpeg:-"

985 fi

986 fi

987 $cjpeg "$@"

988 }

989 mycrypt() (

990 key=$1 salt=$2

991 # err \$2=$2

992 case $2 in

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

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

995 esac

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

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

998 )

999 hexize() {

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

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

1002 hexize="xxd -p"

1003 else

1004 hexize_hd() {

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

1006 }

1007 hexize="hexize_hd"

1008 fi

1009 fi

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

1011 }

1012 unhexize() {

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

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

1015 unhex="xxd -p -r"

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

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

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

1019 EOF

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

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

1022 # world writable...:)

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

1024 fi

1025 fi

1026 cat "$@" | $unhex

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

1028 }

1029 percenthex() {

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

1031 }

1032 htmlescape() {

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

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

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

1036 }

1037 enascii() {

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

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

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

1041 else

1042 enascii_now=`date +%FT%T`

1043 enascii_sed() {

1044 nkf -Z0Z1Z2 \

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

1046 }

1047 enascii="enascii_sed"

1048 fi

1049 fi

1050 cat "$@" | $enascii

1051 }

1052 size_h() {

1053 i="$1" oi=$1

1054 set -- B B KB MB GB TB

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

1056 oi=$i

1057 i=$((i/1024))

1058 shift

1059 done

1060 echo ${oi}$1

1061 }

1062 gettblconf() {

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

1064 ## tconfs=`sq $db \

1065 tconfs=`query \

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

1067 fi

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

1069 }

1070 gettblkeys() {

1071 # $1=tbl

1072 gettblconf

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

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

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

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

1077 case $type in

1078 *p*)

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

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

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

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

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

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

1085 esac

1086 case $type in

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

1088 esac

1089 done

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

1091 }

1092 gettblpkey() {

1093 # $1=tbl

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

1095 }

1096 gettblfkey() {

1097 (x=`gettblkeys $1`

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

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

1100 }

1101 gettblcols() {

1102 (x=`gettblkeys $1`

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

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

1105 }

1106 gettbl_s_cols() {

1107 (x=`gettblkeys $1`

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

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

1110 }

1111 gettbl_m_cols() {

1112 (x=`gettblkeys $1`

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

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

1115 }

1116 gettbl_h_cols() {

1117 (x=`gettblkeys $1`

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

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

1120 }

1121 gettbl_coltype() (

1122 gettblconf

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

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

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

1126 )

1127 is_hidden() {

1128 # $1=Tbl $2=col

1129 gettblconf

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

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

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

1133 case $x in

1134 *h*) return 0 ;;

1135 *) return 1 ;;

1136 esac

1137 }

1138

1139 dbsetbyid() {

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

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

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

1143 quotedp=$(sqlquotestr "$p")

1144 unset primary update

1145 gettblconf

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

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

1148 #err conf=$conf

1149 case ${conf#*=} in

1150 p*) primary=1 ;;

1151 f*) update=1 ;;

1152 u*) ;;

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

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

1155 esac

1156 #err t=$t

1157 type=string fn=""

1158 case $conf in

1159 */password)

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

1161 ;;

1162 */image*|*/document*)

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

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

1165 ;;

1166 esac

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

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

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

1170 if [ "$primary" ]; then

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

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

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

1174 elif [ "$update" ]; then

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

1176 else

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

1178 fi

1179 )

1180 }

1181 expire() (

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

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

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

1185 )

1186 addsession() {

1187 # expireをセット

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

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

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

1191 # Remove old session parameters

1192 now=`expire now`

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

1194 }

1195 gencookie() (

1196 path=${URL#*:/}

1197 path=${URL%/*}

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

1199 for kv; do

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

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

1202 done

1203 )

1204 contenttype() {

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

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

1207 }

1208 putheader() {

1209

1210 }

1211 putfooter() {

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

1213 }

1214 getcookie() {

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

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

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

1218 case "$k" in

1219 user) _user="$v" ;;

1220 skey) _skey="$v" ;;

1221 esac

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

1223 done

1224 }

1225 genrandom() {

1226 # $1=columns (default: 10)

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

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

1229 }

1230 genserial() {

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

1232 }

1233 smail() {

1234 # smail rcpts subj (file)

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

1236 # $MAIL_FROM <- From: header value

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

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

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

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

1241 sender=${SENDER:-$admin}

1242 # Do not call m4 with directly passing text

1243 _r=$tmpd/rcpt

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

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

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

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

1248 }

1249 smail_queue_flush() {

1250 # $1=timelimit

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

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

1253 SELECT rowid FROM smailq

1254 GROUP BY rcpts, subj

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

1256 EOF

1257 )

1258 for rid in $rowids; do

1259 sq $workdb \

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

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

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

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

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

1265

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

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

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

1269 .separator "\n" "------------------\n\n"

1270 SELECT time, text FROM smailq

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

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

1273 ORDER by time;

1274 EOF

1275 then

1276 cat <<-EOF | sq $workdb

1277 DELETE FROM smailq

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

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

1280 EOF

1281 fi

1282 fi

1283 done

1284 }

1285 smail_queue() {

1286 # $1=Rcpts, $@=subj

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

1288 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1297 EOF

1298 )

1299 ## XXX: Adhoc load mitigation

1300 case "$now" in

1301 *[01])

1302 err flush_queue=$mintime

1303 smail_queue_flush ;;

1304 esac

1305 fi

1306 }

1307 setviastring() {

1308 table=par

1309 oifs="$IFS"

1310 IFS="&"

1311 for us in $1; do

1312 k=${us%%=*}

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

1314 setpar "$k" "string" "$v"

1315 done

1316 IFS="$oifs"

1317 }

1318 checkdomain() (

1319 # Check the validity of domain by referring DNS

1320 item=$1

1321 err checkdomain $1

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

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

1324 )

1325 pwcheck() {

1326 # $1=passwd

1327 dbpswd=`getpwfield $user pswd`

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

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

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

1331 }

1332 mypwhash() {

1333 mycrypt "`cat`" `genrandom 5`

1334 }

1335 wasureta() {

1336 user=$1

1337 if ! checkdomain $user; then

1338 contenttype; echo

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

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

1341 putfooter

1342 exit 0

1343 fi

1344 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1346 encpswd=`echo $newpswd|mypwhash`

1347 dbsetbyid user $user pswd "$encpswd"

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

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

1350 $msgdir/mail-newaccount.m4 \

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

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

1353 }

1354 checkauth() {

1355 user=`getpar user`

1356 skc=`getpar skey` # from cookie

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

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

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

1360 if chkskey "$skc"; then

1361 return 0

1362 fi

1363 fi

1364 pswd=`getpar pswd`

1365 quser=`sqlquotestr "$user"`

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

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

1368 return 4 # 4=server too heavy

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

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

1371 return 2 # 2=login fail

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

1373 wasureta "$user"

1374 return 1 # wasureta error

1375 fi

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

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

1378 if pwcheck "$pswd"; then

1379 newsession=`genrandom 34`

1380 if setskey "$user" "$newsession" &&

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

1382 gencookie "user=$user" "skey=$newsession"

1383 return 0

1384 else

1385 return 4 # Heavy load??

1386 fi

1387 fi

1388 err "Login failed: [$user]"

1389 return 2 # Password mismatch

1390 }

1391 showlogin() {

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

1393 test -z "$args" && resetskey

1394 s4name=${S4NAME:-s4}

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

1396 cat $layout/login.m4.html

1397 echo '</body></html>'

1398 ) | _m4 \

1399 -D_BODYCLASS_="login" \

1400 -D_TITLE_="$s4name" \

1401 -D_SYSNAME_="Welcome to $s4name" \

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

1403 exit 0

1404 }

1405 dologin() {

1406 checkauth

1407 st=$?

1408 if [ $st != 0 ]; then

1409 contenttype; echo

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

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

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

1413 showlogin # and EXIT

1414 fi

1415 }

1416

1417 # Do instant jobs here

1418 dbsetup

1419 trap cleanup INT HUP EXIT TERM PIPE

1420 # trap cleanup INT HUP

1421

1422 err() {

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

1424 }

1425

1426 cgiinit() {

1427 tmpd=`tmpd=$tmpdir mktempd`

1428 tmpf=$tmpd/stream.$$

1429 tmpfiles=$tmpfiles" $tmpd"

1430 addsession $session

1431 getcookie

1432 case "$REQUEST_METHOD" in

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

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

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

1436 # cat > $tmpf # too much?

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

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

1439 s="`cat $tmpf`"

1440 ;;

1441 esac

1442 case "$CONTENT_TYPE" in

1443 *boundary*)

1444 bndry=${CONTENT_TYPE#*boundary=}

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

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

1447 do

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

1449 #echo u=$us

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

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

1452 # err k=$k v=$v

1453 case "$k" in

1454 *:filename)

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

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

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

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

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

1460 :

1461 fi

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

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

1464 case "$mimetype" in

1465 [Ii]mage/x-xcf)

1466 bzip2 "$tmpd/$v"

1467 v=${v}.bz2

1468 ;;

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

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

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

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

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

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

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

1476 v=$vjpg

1477 else

1478 mimetype="Not supported"

1479 fi

1480 ;;

1481 [Ii]mage/*)

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

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

1484 ;;

1485 esac

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

1487 then

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

1489 continue

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

1491 replpar text string \

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

1493 continue

1494 fi

1495 ;;

1496 *)

1497 type='string'

1498 ;;

1499 esac

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

1501 setpar "$k" "$type" "$v"

1502 done

1503 ;;

1504 *)

1505 setviastring "$s"

1506 ;;

1507 esac

1508 }

1509 email4group() {

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

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

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

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

1514 sql="WITH

1515 grpemails AS (

1516 SELECT gname, user, val email

1517 FROM grp_mem NATURAL JOIN grp_mem_s

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

1519 useremails AS (

1520 SELECT user.name, val email

1521 FROM user

1522 LEFT JOIN user_m

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

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

1525 FROM useremails u LEFT JOIN grpemails g

1526 ON u.name=g.user

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

1528 query "$sql"

1529 }

1530 email4groupbyuid() {

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

1532 g=$1; shift

1533 uids=`echo "$@"`

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

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

1536 email4group "$g" `query "$sql"`

1537 }

1538 myemail4group() {

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

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

1541 }

1542 collectmembersbyid() {

1543 # Collect user names of group specified by grid

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

1545 query "SELECT user FROM grp_mem \

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

1547 }

1548 collectmembersbyid() {

1549 # Collect user names of group name

1550 qgrp=`sqlquote "$1"`

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

1552 }

1553 collectgecosesbyid() {

1554 # Collect user gecoses of group

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

1556 query<<-EOF

1557 SELECT gecos

1558 FROM gecoses

1559 WHERE name IN (SELECT user FROM grp_mem

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

1561 EOF

1562 }

1563 collectemail() (

1564 # Collect email addresses for group $1

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

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

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

1568 for e; do

1569 if isuser "$e"; then

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

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

1572 else

1573 qgrp=`sqlquote "$e"`

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

1575 gmem="grp_mem"

1576 else

1577 tm=`sqlquote "$TEAM"`

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

1579 fi

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

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

1582 $gmem g left join grp_mem_s s

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

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

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

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

1587 query "$sql"

1588 fi

1589 done

1590 )

1591 sendinvitation() (

1592 # $1=email

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

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

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

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

1597 gecos=`gecos`

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

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

1600 _m4 -D_URL_="$urlbase" \

1601 -D_USER_="$name" \

1602 -D_EMAIL_="$1" \

1603 -D_REGIST_="$regist" \

1604 -D_ADMIN_="$admin" \

1605 $msgdir/mail-invite.m4 \

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

1607 return 0

1608 )

1609 emaildomaincheck() {

1610 case "$1" in

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

1612 *@*)

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

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

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

1616 return 2

1617 fi

1618 return 0

1619 ;;

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

1621 esac

1622 }

1623 invite() {

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

1625 case "$email" in

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

1627 *@*)

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

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

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

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

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

1633 elif sendinvitation $email; then

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

1635 else # Cannot be reached here

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

1637 fi ;;

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

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

1640 esac

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

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

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

1644 fi

1645 if [ -f $invite_policy ]; then

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

1647 else

1648 pol="$invite_policy"

1649 fi

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

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

1652 -D_INVITE_POLICY_="$pol" \

1653 $layout/html.m4.html $layout/invite.m4.html

1654 }

1655 regist() {

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

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

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

1659 echo "bye bye" | html p

1660 reutrn

1661 fi

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

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

1664 cat<<EOF

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

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

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

1668 EOF

1669 return

1670 fi

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

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

1673 # Fake login password to wasureta

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

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

1676 wasureta $email

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

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

1679 addsession $1 # for removal after 1 minute

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

1681 $layout/login.m4.html

1682 return

1683 }

1684 group_safename() {

1685 # Convert $1 to safe group name

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

1687 }

1688 groupupdate() {

1689 gname=`getpar gname`

1690 qgname=`sqlquote "$gname"`

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

1692 # See ALSO same job in showgroup()

1693 newgname=`group_safename "$gname"`

1694 err newgname=$newgname

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

1696 err NewGNAME: gname=$newgname

1697 gname=$newgname

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

1699 replpar gname string "$gname"

1700 fi

1701 # Name confliction check

1702 parow=`getpar rowid`

1703 ## err parow=$parow

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

1705 query "BEGIN EXCLUSIVE;"

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

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

1708 if [ $count -gt 0 ]; then

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

1710 query "END;"

1711 return

1712 fi

1713 par2table $formdir/grp.def

1714 query "END TRANSACTION;"

1715 # Remove orphan

1716 : <<EOF

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

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

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

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

1721 EOF

1722 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1726 sql="delete from blog where id in

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

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

1729 query "$sql";

1730 unsetpar tag kwd

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

1732 return # and return

1733 fi

1734 fi

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

1736 fi

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

1738 grid=$(query $sql)

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

1740 grp $grid

1741 }

1742 groupclone() {

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

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

1745 case "$1" in

1746 */noteam)

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

1748 esac

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

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

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

1752 return

1753 fi

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

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

1756 return

1757 fi

1758 i=0

1759 while true; do

1760 copy="-copy$i"

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

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

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

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

1765 i=$((i++))

1766 continue

1767 fi

1768 break

1769 done

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

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

1772 query<<-EOF

1773 BEGIN;

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

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

1776 SELECT $newqname, key, val

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

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

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

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

1781 -- Copy members and their configuration --

1782 REPLACE INTO grp_mem SELECT $newqname, user

1783 FROM grp_mem WHERE gname=$qgrp;

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

1785 FROM grp_mem_s WHERE gname=$qgrp;

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

1787 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1788 -- Copy administrators --

1789 REPLACE INTO grp_adm SELECT $newqname, user

1790 FROM grp_adm WHERE gname=$qgrp;

1791 COMMIT;

1792 EOF

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

1794 STOPCLONEMSG=1 groupconf "$newrowid"

1795 }

1796 groupman() {

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

1798

1799 GF_STAGE="grpconf"

1800 GF_STAGE=groupupdate

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

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

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

1804 -D_DUMPTABLE_="syscmd(cat)" \

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

1806 }

1807 userconf() {

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

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

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

1811 }

1812 groupconf() {

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

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

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

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

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

1818

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

1820 grp=`getgroupbyid $rowid`

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

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

1823 return

1824 fi

1825

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

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

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

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

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

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

1832 <div>

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

1834 <table>

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

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

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

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

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

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

1841 </table>

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

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

1844 </div>

1845 EOF

1846 fi

1847 }

1848 mems() {

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

1850 kwd=`getpar kwd`

1851 listmember $kwd

1852 }

1853 grps() {

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

1855 kwd=`getpar kwd`

1856 listgroup $kwd \

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

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

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

1860 $layout/form+dump.m4.html

1861 }

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

1863 gpg=`getpar grp`

1864 grid=${1:-$gpg}

1865 grp=`getgroupbyid "$grid"`

1866 ## . ./s4-blog.sh

1867 jg=`getpar joingrp`

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

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

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

1871 fi

1872 htmlheader=$layout/html.m4.html

1873 showgroup "$grid"

1874 }

1875 sql4interestblogs() {

1876 cat<<EOF

1877 CREATE TEMPORARY VIEW interestblogs AS

1878 SELECT blog.rowid rid, id, author

1879 FROM blog

1880 NATURAL JOIN

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

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

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

1884 WHEN (SELECT user FROM grp_mem

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

1886 THEN 0

1887 ELSE 1

1888 END;

1889 EOF

1890 }

1891 listnewblogsql() { # $1=user

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

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

1894 newdays=${newdays:-14}

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

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

1897 WHERE user='$user'

1898 AND tblrowid IN

1899 ($blogreadflagrowid,

1900 $blogcutoffflagrowid)),

1901 $basetime -- "0"

1902 );"`

1903 cat<<EOF

1904 `sql4interestblogs`

1905 WITH article_ctime as (

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

1907 FROM article join article_s s using(id)

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

1909 GROUP BY id

1910 ), blog_title_owner as (

1911 SELECT blg.rid brid, id,

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

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

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

1915 ), blogall as (

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

1917 ), news as (

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

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

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

1921 FROM blogall bl

1922 LEFT JOIN

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

1924 ON bl.brid=al.tblrowid

1925 WHERE atime < bl.ctime

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

1927 LIMIT 10

1928 ) SELECT brid LINK, "新着",

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

1930 ctime, title,

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

1932 FROM news;

1933 EOF

1934 }

1935

1936 search_form() {

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

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

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

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

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

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

1943 @week → 最近一週間

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

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

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

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

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

1949 auth=""

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

1951 case "$1" in

1952 author=*)

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

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

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

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

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

1958 ;;

1959 grid=*)

1960 a=`echo "${1#grid=}"`; a=$((0 + $a))

1961 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

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

1963 ;;

1964 esac

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

1966 cat<<-EOF

1967 <div class="right">

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

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

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

1971

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

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

1974

1975 </form>

1976 </div>

1977 EOF

1978 }

1979

1980 imgsrc_cache() (

1981 # $1 = directory for cache'ing

1982 # $2 = table (user_m or grp_m)

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

1984 # $4 = size : S = Small, M = Medium, O = Original

1985 dir="$1" tbl="$2"

1986 keycond="$3"

1987 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

1988 ORDER BY rowid DESC LIMIT 1"

1989 [ -d "$dir" ] || mkdir -p "$dir"

1990 tmpf=$tmpd/imgsrc_cache.$$

1991 case "$4" in

1992 [Ss]) size=S ;;

1993 [Oo]) size=O ;;

1994 *) size=M ;;

1995 esac

1996 # ImageCache filename storing schema:

1997 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

1998 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

1999 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

2000 valtype=`query "$sql0"`

2001 filename=${valtype%%//*}

2002 filetype=${valtype##*//file:}

2003 if [ x"$filename" = x"${filename%.*}" ]; then

2004 # If nor filename extension found, set it to image type

2005 case "$filetype" in

2006 image/*) filename=$filename.${filetype#image/} ;;

2007 esac

2008 fi

2009 cacheimg_S=$dir/S_$filename

2010 cacheimg_M=$dir/M_$filename

2011 cacheimg_O=$dir/$filename

2012 cacheimg=$dir/${size}_$filename

2013 sumfile="$dir/$filename.sum"

2014 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

2015 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2016 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2017 # if cache is fresh and has the same checksum,

2018 echo "<img src=\"$cacheimg\">"

2019 else

2020 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2021 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2022 fifo2=$fifo.2

2023 mkfifo $fifo $fifo2

2024 fmt=${filename##*.}

2025 ## [[ NOTE ]]

2026 ## a. convert oldimage newimage

2027 ## b. convert oldimage fmt:- | convert - newimage

2028 ## b is much smaller than a

2029 cat $tmpf | unhexize \

2030 | tee $fifo \

2031 | convert -define ${fmt}:size=${iconxy_M} \

2032 -resize ${iconxy_M}'>' - ${fmt}:- \

2033 | tee $fifo2 \

2034 | convert - "$cacheimg_M" &

2035 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2036 -resize ${iconxy_S}'>' - ${fmt}:- \

2037 | convert - "$cacheimg_S" &

2038 printf '%s' "<img src=\"data:${filetype},"

2039 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2040 echo '">'

2041 echo "$sum" > $sumfile

2042 fi

2043 ## Now preparing cache image, done.

2044 ## Store this information to DB

2045 stbl=${tbl%_m}_s # user_s or grp_s

2046 pkey=${keycond%%=*} # Primary Key name

2047 pval=${keycond#*=} # Primary Key value

2048 query <<-EOF

2049 REPLACE INTO $stbl($pkey, key, type, val)

2050 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2051 EOF

2052 )

2053

2054 showhome() {

2055 # $1=userRowIdToShow

2056 err showhome \$1=$1

2057 case "$1" in

2058 *@*) uname=`getvalbypkey user name "$1"` ;;

2059 *) uname=`getvalbyid user name $1` ;;

2060 esac

2061 ## err ShowHome: uname=$uname

2062 td=`getcachedir home/"$1"`

2063 gecos=`gecos "$uname"`

2064 ## err SH:gecos=$gecos

2065 GF_VIEWONLY=1

2066 cond="gname in (select gname from grp_mem where user='$uname')"

2067 search_form_args=""

2068 if [ x"$user" = x"$uname" ]; then

2069 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2070 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> /

2071 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2072 # Display folders

2073 sql="select count(id) from article_m where id

2074 in (select id from article where author='$user')

2075 and type like 'file:%';"

2076 ## err nfile-sql=`echo "$sql"`

2077 nfile=`query "$sql"`

2078 # err nfile=$nfile

2079 if [ $nfile -gt 0 ]; then

2080 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2081 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2082 fi

2083 else

2084 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2085 GROUP BY user;"`

2086 usermenu="<p>Last seen on $latestlog</p>"

2087 search_form_args="author=$uname"

2088 fi

2089 . ./s4-blog.sh

2090

2091 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2092 search_form "$search_form_args" > $sf

2093 printf "%s さん" "$gecos"|htmlescape > $tf

2094 { echo "<div class=\"noprofimg\">"

2095 viewtable $formdir/user.def user $1

2096 echo "</div>"

2097 } > $pf

2098

2099 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2100 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2101 imf=$tmpd/profimg.$$; touch $imf

2102 if [ -n "$img" ]; then

2103 if true; then

2104 tbl=user_m

2105 enticond="name='$uname'"

2106 imgsrc_cache "$td/main" user_m "$enticond" M

2107 else

2108 { printf '%s' "<IMG src=\"data:${img#file:},"

2109 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2110 | sed 's/$..$/%\1/g'

2111 echo '">'

2112 }

2113 fi > $imf

2114 fi

2115 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2116 val='$uname';"`

2117 ## REMOVE This comment block until 2019/7/1

2118 ## err "----- `gdate +%FT%T.%3N` ------------C"

2119 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2120 listblog $uname > $bf

2121 ## unset ddd

2122 ## err "----- `gdate +%FT%T.%3N` ------------D"

2123

2124 hometail=$tmpd/tail.$$

2125 mkfifo $hometail

2126

2127 #Calling listgroupbytable, originally here

2128

2129 (

2130 # Display Most Recent Entry

2131 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2132 shortval=${shortval:-val}

2133

2134 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2135 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2136 ctime,

2137 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2138 (SELECT gecos FROM gecoses

2139 WHERE name=(SELECT val FROM blog_s

2140 WHERE key='owner' AND id=b.id)) owner,

2141 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2142 FROM blog b

2143 JOIN

2144 (SELECT distinct blogid, a.id aid, max(val) ctime

2145 FROM article a, article_s s

2146 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2147 GROUP BY blogid ORDER BY val DESC LIMIT 50

2148 ) m

2149 ON b.id=m.blogid;"

2150 # This should be as follows

2151 : <<EOF

2152 WITH arts AS(

2153 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2154 a.blogid, a.id id, s.val ctime

2155 FROM article a NATURAL JOIN article_s s

2156 WHERE s.key = 'ctime' AND a.author='$user'

2157 GROUP by s.id

2158 )

2159 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2160 FROM arts a0

2161 JOIN

2162 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2163 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2164 ORDER BY ctime DESC LIMIT 50;

2165 EOF

2166

2167 cat<<-EOF

2168 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2169 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2170 <div class="lcto">

2171 `DT_VIEW=replyblog dumptable html blog`

2172 </div>

2173 EOF

2174 unset DT_SQL

2175 if [ x"$user" = x"$uname" ]; then

2176 # Display NEWS

2177 # 2016-06-26

2178 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2179 acclog blog $blogreadflagrowid

2180 # echo "全部既読にしました" | html p

2181 fi

2182 # 2016-02-19 Counting NEWS without using dumptable.

2183 sql=`listnewblogsql "$user"`

2184 # echo "$sql" > tmp/listnew

2185 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2186 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2187 cont=$((cont-1))

2188 err newcount=$cont

2189 if [ $cont -gt 0 ]; then

2190 #echo "全体の新着記事${cont}傑" | html h2

2191 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2192 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2193 cat<<-EOF | html form 'action="?home"'

2194 `cgi_checkbox readchk yes 'id="read"'`<label

2195 for="read">新着ふくめて全部読んだことにする</label>

2196 `cgi_submit '確定'`

2197 `cgi_hidden read yes`

2198 EOF

2199 echo "$new10 "

2200 echo "</div>"

2201 else # If news is 0, set log cut off flag

2202 acclog blog $blogcutoffflagrowid # for speed

2203 fi

2204 else # Not My Home ($user != $uname)

2205 : # DT_SQL=

2206 fi

2207 ) > $hometail & # Is background call safe to m4??

2208 #

2209 listgroupbytable $formdir/grp.def $cond |

2210 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2211 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2212 -D_PROFIMG_="spaste(\`$imf')" \

2213 -D_BLOGS_="spaste(\`$bf')" \

2214 -D_SEARCH_="spaste(\`$sf')" \

2215 -D_NBLOG_="$nblog" \

2216 -D_GROUPS_="syscmd(\`cat')" \

2217 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2218 $layout/html.m4.html $layout/home.m4.html

2219

2220 # Record access log

2221 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2222 }

2223 commission() { # $1=grp-rowid $2=user-rowid

2224 contenttype; echo

2225 ## err commission: "$@"

2226 gname=`getgroupbyid $1`

2227 echo "グループ $gname 管理者委任" \

2228 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2229 if [ -n "$2" ]; then

2230 grp_reg_adm "$@"

2231 else

2232 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2233 fi

2234 }

2235 listgroupbytable() {

2236 # $1=deffile $2...=condition

2237 tagline=`grep :tag: $1`; shift

2238 and="${1:+and }" where=${1:+where }

2239 href="<a href=\"$myname?grp+"

2240 echo '<div class="listgroup">'

2241 NGsql="select distinct tag from\

2242 (select gname, max(case key when 'tag' then val end) as tag, \

2243 max(case key when 'ctime' then val end) as ctime\

2244 from grp_s group by gname order by ctime);"

2245 sql="select val from grp_s where key='tag' $and$* group by val;"

2246 ## err ListGRP: query sql="$sql"

2247 for tag in `query "$sql"`

2248 do

2249 ## err ListGrp: tag=$tag

2250 tn=${tagline%%=${tag}*}

2251 tn=${tn##*[ :]}

2252 sql="select rowid||':'||gname as 'グループ名',説明 from

2253 (select (select rowid from grp g where g.gname=grp_s.gname)

2254 as rowid,

2255 gname,

2256 max(case key when 'gecos' then val end) as '説明',

2257 max(case key when 'tag' then val end) as 'tag',

2258 max(case key when 'mtime' then val end) as mtime from grp_s

2259 $where$* group by gname having tag='$tag' order by mtime desc);"

2260 ## err PersonalGroupList= `echo $sql`

2261 echo "<h2>$tn</h2>"

2262 echo '<table class="b listgroup">'

2263 sq -header -html $db "$sql" \

2264 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2265 echo '</table>'

2266 done

2267 echo '</div>'

2268 }

2269 iconhref() (

2270 # $1=icon-file, $2=Href $3=title $4...=anchor

2271 data=`percenthex "$1"`

2272 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2273 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2274 href=$2; title=$3; shift 3

2275 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2276 )

2277 iconhref2() (

2278 # $1=icon-file, $2=Href $3=title $4...=anchor

2279 src=$1

2280 href=$2; title=$3; shift 3

2281 anchor=`echo $@|htmlescape`

2282 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2283 )

2284 listentry() (

2285 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2286 # Referring variable $iamowner=$grp to attach owner-request links

2287 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2288 cond='' hiddens=''

2289 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2290 if [ -z "$offset" ]; then

2291 offset=`getpar start`; offset=${offset%%[!0-9]*}

2292 offset=$((offset-1))

2293 fi

2294 offset=$((offset + 0)) # change to numeric forcibly

2295 [ $offset -lt 0 ] && offset=0

2296 limit=$listentlimit

2297 dir=`getcachedir "$1"`

2298 if [ x"$1" = x"user" ]; then

2299 hrb="$myname?home"

2300 deficon=person-default.png

2301 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2302 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2303 gcs=gecos

2304 else # if group

2305 hrb="$myname?grp"

2306 deficon=group-default.png

2307 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2308 gcs=name

2309 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2310 if [ -n "$tagline" ]; then

2311 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2312 ## err tagconv=$tagconv

2313 fi

2314 fi

2315 if [ ! -d $dir ]; then

2316 mkdir -p $dir

2317 fi

2318 if [ ! -s $dir/$deficon ]; then

2319 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2320 fi

2321 if [ -n "$2" ]; then

2322 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2323 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2324 fi

2325 tag=`getpar tag` tag2=`getpar tag2`

2326 if [ x"$tag" = x"NULL" ]; then

2327 tag="" tag2=""

2328 fi

2329 if [ -n "$tag$tag2" ]; then

2330 tag=${tag:-$tag2}

2331 qtag=`sqlquote "$tag"`

2332 cond2="tag=$qtag"

2333 fi

2334 if [ -n "$cond1$cond2" ]; then

2335 cond="$cond1${cond2:+ AND $cond2}"

2336 cond="WHERE ${cond# AND }"

2337 fi

2338

2339 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2340 # grpは呼出し元の動的スコープ変数でよくないな...

2341 ##qgrp=`sqlquote $grp`

2342 getgrp="(select gname from grp where rowid=${rowid:--1})"

2343 sql="select a.rowid, a.$link,

2344 coalesce(b.$gcs, a.$nm) as nick,

2345 quote(a.$nm) as qname,

2346 (SELECT val FROM ${tbl}_s

2347 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2348 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2349 || case when a.$nm in (select gname from grp)

2350 then printf('(%d名)',

2351 (select count(user) from grp_mem where gname=a.$nm))

2352 else ' <'||a.$nm||'>'

2353 end

2354 as name,

2355 b.tag,

2356 case when a.$nm in (select user from grp_adm

2357 where gname=$getgrp) then '管理者'

2358 when '$user' in (select user from grp_adm where gname=a.$nm)

2359 then 'ADMIN'

2360 when '$user' in (select user from grp_mem where gname=a.$nm)

2361 then 'Member'

2362 when '$iamowner' = '' then ''

2363 else ',not='||a.rowid end as ownerlink,

2364 CASE '$entity'

2365 WHEN 'グループ'

2366 THEN coalesce(

2367 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2368 'open')

2369 ||

2370 CASE WHEN '$user'

2371 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2372 THEN ' ismember'

2373 ELSE ''

2374 END

2375 ELSE 'user'

2376 END regmode

2377 from $tbl a left join

2378 (select $nm as name,

2379 max(case key when 'gecos' then val end) as gecos,

2380 max(case key when 'tag' then val end) as tag,

2381 max(case key when 'mtime' then val end) as mtime,

2382 max(case key when 'wtime' then val end) as wtime,

2383 max(case key when 'login' then val end) as login

2384 from ${tbl}_s group by $nm)

2385 b on a.$nm=b.name $cond $3

2386 order by b.wtime desc, b.login desc,

2387 b.mtime desc, b.tag desc, a.rowid asc"

2388 # Give precedence to newer maintained groups (2016-09-24)

2389 # Note that mtime is stored only in grp_s.

2390 ## err LE:sql.1="$sql"

2391 total=`query "with x as ($sql) select count(*) from x;"`

2392 echo "${entity} 一覧" | html h2

2393 echo '<div class="listentry">' # List-entry div

2394 # Show owner/member filter button

2395 METHOD=GET

2396 hiddens="$hiddens

2397 `cgi_hidden kwd \"$kwd\"`

2398 `cgi_hidden stage \"$stage\"`"

2399 if [ x"$tbl" = x"grp" ]; then

2400 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2401 fh="<select name=\"tag\">$nl"

2402 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2403 for l in $args; do

2404 val=${l#*=} tname=${l%=*}

2405 if [ x"$val" = x"$tag" ]; then

2406 s=" selected"

2407 selectedtags="(種別[${tname}]のみ)"

2408 else

2409 s=""

2410 fi

2411 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2412 done

2413 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2414 cat<<-EOF

2415 <form action="$myname" method="$METHOD">

2416 </form>

2417 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2418 for="ismembtn">参加中以外隠す</label>

2419 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2420 for="isadmbtn">管理者参加以外隠す</label>

2421 EOF

2422 # limit=3

2423 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2424 fi

2425 if [ $total -gt $limit ]; then

2426 echo '<div>'

2427 METHOD=GET cgi_form $stage <<EOF

2428 $form

2429 <label>次の語を含む${entity}で検索:

2430 `cgi_text kwd "$kwd"`</label>

2431 $hiddens

2432 EOF

2433 echo '</div>'

2434 else

2435 echo $selectedtags | html p

2436 fi

2437 cat<<EOF

2438 <form action="$myname" method="$METHOD">

2439 <p>${total}件中の<input class="hidesub" type="text" name="start"

2440 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2441 <input type="submit" value="確定"></p>

2442 </form>

2443 EOF

2444 if [ $((offset+limit)) -lt $total ]; then

2445 nextbtn=$(

2446 cat<<EOF

2447 <div class="right clear"><form action="$myname" method="$METHOD">

2448 `cgi_submit 次の${limit}件`

2449 $hiddens

2450 `cgi_hidden offset $((offset + limit))`</form></div>

2451 EOF

2452 )

2453 fi

2454 if [ $offset -gt 0 ]; then

2455 prevbtn=$(

2456 cat<<EOF

2457 <form action="$myname" method="$METHOD">

2458 `cgi_submit 前の${limit}件`

2459 $hiddens

2460 `cgi_hidden offset $((offset - limit))`</form>

2461 EOF

2462 )

2463 fi

2464 pnbtn="$nextbtn$prevbtn"

2465 echo $pnbtn

2466

2467 ## err ListEntry: `echo "$sql"\;`

2468 # sq $db here??? 2016-11-28

2469 query "$sql limit $limit ${offset:+offset $offset};" \

2470 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2471 # err name=$name owner=$ownerp lnk=$lnk

2472 # err newlnk=$lnk regmode=$regmode

2473 icondir=$dir/$id

2474 # Pick up only last icon

2475 htmlname=`echo $name|htmlescape`

2476 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2477 <p class=\"tag _$tag\">$tag</p>" \

2478 | _m4 $tagconv

2479 if [ -n "$NOSPEEDUP" ]; then

2480 files=`getvalbyid $tbl profimg $id $icondir`

2481 if [ -n "$files" ]; then

2482 icon=`echo "$files"|tail -1`

2483 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2484 else

2485 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2486 fi

2487 elif [ -n "$icon" -a -s "$icon" ]; then

2488 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2489 else

2490 cond="$nm=$qname"

2491 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2492 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2493 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2494 # err "img=[$img]"

2495 if [ -n "$img" ]; then

2496 echo "<a href=\"$hrb+$lnk\">"

2497 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2498 echo "</a>"

2499 else

2500 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2501 fi

2502 fi

2503 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2504 echo "</div>"

2505 done

2506 echo "</div>" # End of List-entry div

2507 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2508 )

2509 listmember() {

2510 listentry user "$@"

2511 }

2512 listgroup() {

2513 listentry group "$@"

2514 }

2515 hexteams() { # $1=gname, $2(optional)=user

2516 cond=${2:+" AND user='$2'"}

2517 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2518 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2519 }

2520 showgroup() { # $1=group-rowid

2521 if [ -z "$1" ]; then

2522 grid=`getpar grid`

2523 grid=${grid%%[!0-9]*}

2524 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2525 else

2526 grid=$1

2527 fi

2528 grp=`getgroupbyid $grid`

2529 qgrp=`sqlquote "$grp"`

2530 htmlgrp=`echo "$grp"|htmlescape`

2531 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2532 if isgroup "$grp"; then

2533 tf=$tmpd/title.$$

2534 sf=$tmpd/search.$$

2535 bodyclass=`query "SELECT val FROM grp_s

2536 WHERE gname=$qgrp AND key='regmode';"`

2537 if ismember "$user" "$grp"; then

2538 ismember="ismember"

2539 bodyclass="$bodyclass${bodyclass:+ }ismember"

2540 else

2541 ismember="" # bodyclass="group"

2542 fi

2543 bodyclass="$bodyclass grouphome"

2544 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2545 echo "グループ $htmlgrp" > $tf

2546

2547 showgroupsub $formdir/grp.def "$grid" | \

2548 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2549 -D_FORM_="syscmd(\`cat')" \

2550 -D_BODYCLASS_="$bodyclass" \

2551 -D_DUMPTABLE_="" \

2552 $htmlheader $sf $layout/form+dump.m4.html

2553 # $htmlheader $layout/form+dump.m4.html

2554 # $htmlheader is defined in grp()

2555 else # if $grp is removed at par2table

2556 listgroup

2557 fi

2558 }

2559 showgroupsub() {

2560 # $1=def-file $2=group-rowid

2561 # Using $ismember

2562 rowid=$2

2563 grp=`getgroupbyid $2`

2564 qgrp=`sqlquote "$grp"`

2565 td=`getcachedir grp/"$2"`

2566 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2567 if [ -z "$rowid" ]; then

2568 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2569 #grp=`sq $db "select gname from grp where rowid=$grp"`

2570 echo "showgroupsub: invalid argument($1 $2)" | html p

2571 return

2572 fi

2573 val=`getvalbyid grp profimg $rowid $tmpd`

2574 enticond="gname=$qgrp"

2575 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2576 if [ -n "$img" ]; then

2577 cat<<-EOF

2578 <p class="groupimg">

2579 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2580 EOF

2581 fi

2582 echo "<div class=\"noprofimg\">"

2583 viewtable $1 grp $rowid

2584 echo "</div>"

2585 if isgrpowner "$user" "$grp"; then

2586 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2587 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2588 iamowner=$rowid

2589 colmd=" mode"

2590 fi

2591 if [ -n "$ismember" ]; then

2592 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2593 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2594 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2595 cat<<-EOF

2596 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2597 href="?blog+$rowid">グループの新規話題作成</a>

2598 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2599 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2600 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2601 <div class="fold clear">

2602 `cgi_checkbox send yes id="send"`<label

2603 for="send">グループ全員にメッセージ送信</label>

2604 <div>

2605 `cgi_textarea message "" "cols=60"`

2606 `cgi_submit 送信`

2607 `cgi_reset リセット`

2608 </div>

2609 `cgi_hidden grp $rowid`

2610 </div></form>

2611 EOF

2612 fi

2613 # 加入ボタン + 加入者リスト

2614 if [ -n "$ismember" ]; then

2615 ismem='checked' state="(参加中)"

2616 else

2617 nomem='checked' state="(現在非加入)"

2618 fi

2619 # このグループでの加入アドレス

2620 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2621 and key='email';"`

2622 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2623 ## and key='email';"

2624 ##err email=$eml

2625 cat <<EOF

2626 <div class="fold clear">

2627 `cgi_checkbox reg yes id="reg"`<label

2628 for="reg">自身の加入状態を操作する</label>$state

2629 <div>

2630 EOF

2631 cgi_form grp <<EOF

2632 <p>このグループに</p>

2633 <table class="b">

2634 <tr><th>メンバーとして</th><td>

2635 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2636 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2637 <tr><th>参加する場合のメイルアドレス<br>

2638 <small>(メインのアドレスとは違うものにする場合に記入<br>

2639 同じでよい場合は空欄に)</small></th>

2640 <td>`cgi_text email $eml`</td></tr>

2641 </table>

2642 `cgi_hidden grp $rowid`

2643 EOF

2644 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2645 echo "moderated (承認加入の)グループなので実際に参加できるのは

2646 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2647 fi

2648 echo '</div></div>'

2649 echo '<h2>話題一覧</h2>'

2650 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2651 cat<<-EOF

2652 <form class="summary" action="$myname" title="$thelp">

2653 `cgi_hidden owner "$grp"`

2654 `cgi_hidden kwd "@week"`

2655 `cgi_hidden stage searchart`

2656 `cgi_submit "一週間のまとめ"`

2657 </form>

2658 EOF

2659 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2660 colstate="state:稼動状態:frozen=rowclass=凍結"

2661 DT_CHLD=article:blogid \

2662 DT_QOWNER="$qgrp" \

2663 DT_VIEW=replyblog dumptable html blog \

2664 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2665

2666 getgname="(select gname from grp where rowid=$rowid)"

2667 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2668 cm="?commission+$rowid"

2669 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2670 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2671 # team list

2672 hexteams=`hexteams "$grp"`

2673 if [ -n "$hexteams" ]; then

2674 echo "チーム一覧" | html h2

2675 echo '<div class="dumptable"><table class="b">'

2676 sq $db -html -header<<-EOF

2677 SELECT val TEAM,

2678 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2679 MEMBERS

2680 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2681 EOF

2682 echo '</table></div>'

2683 fi

2684 }

2685 grp_getbodyclass() {

2686 # Get css class name for document.

2687 # `moderated' for moderated groups

2688 # `ismember' for groups where user belongs

2689 # $1=GroupName (w/o quote)

2690 # $user=userNameCurrentlyLogin

2691 ## err grp_getbodyclass: 1="$1"

2692 qgrp=`sqlquote "$1"`

2693 query<<-EOF

2694 SELECT coalesce(

2695 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2696 'open')

2697 ||

2698 CASE WHEN '$user'

2699 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2700 THEN ' ismember'

2701 ELSE ''

2702 END;

2703 EOF

2704 }

2705 grpaction() { # $1=group-rowid

2706 err GRP_ACTION:IN

2707 grid=${1:-`getpar grp`}

2708 grp=`getgroupbyid "$grid"`

2709 htmlgrp=`echo "$grp" | htmlescape`

2710 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2711 if [ -z "$grp" ]; then

2712 echo "無効な指定です。" | html p; return

2713 fi

2714 if ! ismember $user "$grp"; then

2715 echo "加入者のみに許可された操作です。" | html p; return

2716 fi

2717 echo "グループ $grp 個別選択操作" \

2718 | _m4 -D_TITLE_="syscmd(\`cat')" \

2719 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2720 $layout/html.m4.html

2721

2722 isowner=""

2723 isgrpowner "$user" "$grp" && isowner="yes"

2724 usel=`getpar usel`

2725 if [ -n "$usel" ]; then

2726 uids=$(echo `echo $usel`|tr ' ' ',')

2727 ## err grpaction-1: grp=$grp, `echo $sql`

2728 text=`getpar text|tr -d '\r'`

2729

2730 rm=`getpar rm` cfm=`getpar confirm`

2731 ## err rm=$rm cfm=$cfm

2732 if [ x"$rm" = x"yes" ]; then

2733 if [ "$isowner" ]; then

2734 if [ x"$rm$cfm" = x"yesyes" ]; then

2735 # Eliminate

2736 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2737 for tbl in grp_mem grp_mem_s grp_mem_m; do

2738 sql="delete from $tbl $cond;"

2739 # echo "sql=$sql"

2740 query "$sql"

2741 err rmGRPuser "$sql"

2742 done

2743 num=`query "select count(*) from user where rowid in ($uids);"`

2744 #err num=$num

2745 if [ 0$num -gt 0 ]; then

2746 sql="select coalesce(b.val,a.name) from user a left join \

2747 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2748 # err `echo "$sql"`

2749 html pre<<EOF

2750 以下の${num}名のグループ $grp 登録を解除しました。

2751 `query "$sql"`

2752 EOF

2753 fi

2754 else

2755 echo "確認のチェックがないのでやめておきます。" | html p

2756 return

2757 fi

2758 else # not Group Owner

2759 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2760 return

2761 fi

2762 cat<<EOF

2763

2764 EOF

2765 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2766 if [ -z "$text" ]; then # if msg is empty

2767 echo "なにかメッセージを..." | html p

2768 return 0

2769 fi

2770 gecos=`gecos $user`

2771 safegc=`echo "$gecos" | tr -d '<>@'`

2772 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2773 fromad=`myemail4group "$grp"`

2774 ###mail_from="$safegc <$fromad>"

2775 mail_from="$safegc <$user>" # TEST: 2020/5/13

2776 test -n `getpar sender` &&

2777 export SENDER=$user # TEST: 2020/5/15

2778 replyto=$fromad

2779

2780 ## Start parse of attachment files

2781 if [ -n "`getpar email`" ]; then

2782 ar=`getpar supprcpt`

2783 if [ -n "$ar" ]; then

2784 for a in $ar; do

2785 if checkdomain "$a"; then

2786 supprcpt="$supprcpt $a"

2787 else

2788 err "SupprcptErr=[$a] by $user"

2789 removercpt="$removercpt $a"

2790 fi

2791 done

2792 fi

2793 subj=`getpar subject`

2794 afiles=""

2795 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2796 do

2797 f=$tmpd/$fn

2798 if [ -s $f ]; then

2799 afiles=$afiles"${afiles:+ }$f"

2800 fi

2801 done

2802 else

2803 preface=$(cat <<-EOF

2804 $url

2805 のグループ「$grp」のメンバーである $gecos さんから、

2806 あなた宛のメッセージです。

2807 ----------------------------------------------------------

2808 EOF

2809 )

2810 fi

2811 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2812 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2813 subj="${subj:-$gecos さんからのメッセージ}"

2814 REPLYTO=$replyto

2815 MAIL_FROM=$mail_from

2816 export REPLYTO SMAIL_TO MAIL_FROM

2817 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2818 for r in $rcpts; do

2819 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2820 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2821 else

2822 # Show sender and rcpts address for guest

2823 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2824 fi

2825 if [ -n "$afiles" ];then

2826 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2827 else

2828 smail "$r" "$subj"

2829 fi <<EOF

2830 ${preface:+$preface$nl}$text

2831 EOF

2832 done

2833 if [ $? = 0 ]; then

2834 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2835 sql="select coalesce(b.val, a.name) from

2836 (select name from user where rowid in ($uids)) a

2837 left join user_s b on a.name=b.name and b.key='gecos';"

2838 html pre<<EOF

2839 `query "$sql"`

2840 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2841 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2842 EOF

2843 err SendDone: `echo $sql`

2844 fi

2845 elif [ x"$rm" = x"commission" ]; then

2846 grp_reg_adm $grid $usel

2847 elif [ x"$rm" = x"addteam" ]; then

2848 team=`getpar team|sed "s/'/''/g"` # for single quotation

2849 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2850 if [ x"$team" != x"$newteam" ]; then

2851 echo "チーム名に使えない文字を除去しました" | html p

2852 team=$newteam

2853 fi

2854 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2855 cat<<-EOF | html p

2856 有効なチーム名を入力してください。

2857 カンマだけ、「なし」という名前は使えません。

2858 EOF

2859 echo "有効なチーム名を入力してください。" | html p

2860 else

2861 grp_add_team $grid "$team" $usel

2862 fi

2863 elif [ x"$rm" = x"rmteam" ]; then

2864 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2865 rmteam=`getpar rmteam|sed "s/'/''/g"`

2866 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2867 gname='$grp' AND user='$user' AND key='team'\

2868 AND val='$rmteam';\"`" ]; then

2869 grp_rm_team $grid "$rmteam" $usel

2870 else

2871 echo "所属していないチームの除去操作はできません。"|html p

2872 fi

2873 else

2874 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2875 fi

2876 fi

2877 fi

2878 # POST count summary

2879 from=`getpar from`; to=`getpar to`

2880 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2881 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2882 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2883 # New entry

2884 sql="WITH mems AS (

2885 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2886 ON gm.user=g.name

2887 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2888 ), target_article AS (

2889 SELECT id FROM article_s

2890 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2891 ), posts AS (

2892 SELECT author, count(author) post

2893 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2894 WHERE blogid IN (SELECT id FROM blog_s

2895 WHERE key='owner'

2896 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2897 AND key='text'

2898 GROUP BY author

2899 ), teams AS (

2900 SELECT user, group_concat(val, ', ') team

2901 FROM grp_mem_m

2902 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2903 AND key='team'

2904 GROUP BY user

2905 ), user_post AS (

2906 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2907 FROM mems m LEFT JOIN posts

2908 ON m.name=posts.author

2909 GROUP by m.rowid

2910 )

2911 SELECT

2912 CASE

2913 WHEN (SELECT user FROM grp_adm

2914 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2915 AND user=up.name) IS NOT NULL

2916 then 'k'

2917 ELSE ''

2918 END || rowid || ',' || gecos NAME,

2919 post POST, team _TEAM_

2920 FROM user_post up LEFT JOIN teams t

2921 ON up.name=t.user

2922 ORDER BY gecos;"

2923 ## err grpaction: "`echo \"$sql\"`"

2924 tf=$tmpd/title.$$

2925 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

2926 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2927 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2928 for=\"cmadmin\">管理者委任</label>

2929 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2930 </p></div>"

2931 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2932 title=\"Shortcut: g${nl}Remove from the Group\"

2933 for=\"conf\">GRP登録解除</label>

2934 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2935 <p>この操作による通知は本人に行きません。

2936 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2937 </div>"

2938 # Get team list to which current user belongs into $hexteams

2939 allhexteams=$(hexteams "$grp")

2940 if [ -n "$isowner" ]; then

2941 myhexteams="$allhexteams" # admin can remove all teams' attr

2942 else

2943 myhexteams=$(hexteams "$grp" "$user")

2944 fi

2945 if [ -n "$myhexteams" ]; then

2946 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2947 title=\"Shortcut: s${nl}Strip a team tag from\"

2948 for=\"cmrmteam\">チーム属性除去</label>

2949 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2950 を除去します: `cgi_checkbox teamconfirm yes` 確認

2951 <p>この操作による通知は本人に行きません。

2952 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2953 </div>

2954 "

2955 fi

2956 stf=$tmpd/selteam.$$

2957 cgi_select_h selteam "5445414d" $allhexteams > $stf

2958 b1='<label> <input type="checkbox" name="usel" value="'

2959 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2960 br='<span id="reverse" title="Reverse Selection"></span>'

2961 #b2='"> <span>' b3='</span></label>'

2962 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2963 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2964 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2965 cgi_form grpaction<<EOF \

2966 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2967 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2968 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2969 | _m4 -D_TITLE_="spaste(\`$tf')" \

2970 -D_SUBTITLE_="チェック後操作ボタン" \

2971 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2972 $layout/form+dump.m4.html \

2973 | _m4 -D_TEAM_="spaste(\`$stf')"

2974 <p>下でチェックした人を対象として:</p>

2975 <div class="foldtabs">

2976 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2977 title="Shortcut: a${nl}Add a team tag to"

2978 for="cmteam">同じチーム属性を付与</label>

2979 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2980 `cgi_datalist_h teams $allhexteams`

2981 </div>

2982 ${rmteammsg}

2983 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2984 title="Shortcut: d${nl}DirectMail to"

2985 for="sendmsg" title="Direct Message">DM送信</label>

2986 <div>

2987 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2988 title="Using email format">email書式を使う</label>

2989 <div class="folded">

2990 <table>

2991 <tr><td>From: </td><td>$user</td></tr>

2992 <tr><td>このFrom:で送る</td>

2993 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2994 </td></tr>

2995 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2996 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2997 <tr><td>ファイル添付: </td>

2998 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2999 </table>

3000 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

3001 </div>

3002 <div>本文:`cgi_textarea text "" cols=72`

3003 </div>

3004 </div>

3005 ${isowner:+$cmmsg$excmsg}

3006 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3007 </div>

3008 <h4>$htmlgrp 参加者一覧</h4>$fromtonote

3009 <table class="td2r thl">

3010 `sq $db -header -html "$sql"`

3011 </table>

3012 `cgi_hidden grp $grid`

3013 `cgi_hidden myuid $myuid id="myuid"`

3014 EOF

3015 }

3016 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3017 # Create TEMPORARY VIEW

3018 query<<EOF

3019 CREATE TEMPORARY VIEW writeusers AS

3020 SELECT DISTINCT author FROM article

3021 WHERE id in (

3022 select id from article where blogid=(select id from blog where rowid=$1)

3023 );

3024 CREATE TEMPORARY VIEW movablegroups AS

3025 SELECT g.rowid growid , g.gname

3026 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3027 ON grp.gname=gm.gname -- そのユーザが属している

3028 AND user='$user') g -- グループに絞る

3029 WHERE (SELECT author FROM writeusers

3030 EXCEPT

3031 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3032 IS NULL;

3033 $2

3034 EOF

3035 }

3036 sql4readableblogs() {

3037 # Create view of blogs that can be readable to $user

3038 # Blog is readable when:

3039 # 1: blog owner is an user

3040 # 2: else, 2.1: owner-group where the $user belongs

3041 # 2.2: else, owner-group is not moderated

3042 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3043 cat<<EOF ## | tee tmp/sql.out

3044 CREATE TEMPORARY VIEW readableblogs AS

3045 SELECT blog.rowid rid, id, author

3046 FROM blog

3047 NATURAL JOIN

3048 (SELECT id,

3049 max(CASE key WHEN 'owner' THEN val END) owner,

3050 max(CASE key WHEN 'mode' THEN val END) mode

3051 FROM blog_s GROUP by id) bs

3052 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3053 THEN 1 -- blog owner is an user, READABLE

3054 WHEN (SELECT val FROM grp_s

3055 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3056 AND

3057 (SELECT user FROM grp_mem

3058 WHERE gname=bs.owner AND user='$user') IS NULL

3059 THEN 0

3060 WHEN mode IN ('quiz', 'enquete')

3061 THEN 0 -- "quiz" mode blog cannot be searched

3062 ELSE 1

3063 END;

3064 EOF

3065 }

3066 editheading() { # $1=rowid-of-heading

3067 rowid=${1%%[!A-Z0-9a-z_]*}

3068 if [ -z "$rowid" ]; then

3069 echo "話題番号が未指定です。" | html p

3070 return

3071 fi

3072 owner=`getvalbyid blog owner $rowid`

3073 title=`getvalbyid blog title $rowid`

3074 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3075 | _m4 -D_TITLE_="修正" \

3076 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3077 -D_BLOGS_="" -D_DUMPTABLE_="" \

3078 -D_FORM_="syscmd(\`cat')" \

3079 $layout/html.m4.html $layout/form+dump.m4.html

3080 # Move to group

3081 if isuser "$owner"; then

3082 crview4article $rowid

3083 n=`query "SELECT count(*) FROM writeusers;"`

3084 ## err N=$n

3085 if [ $((n)) -gt 0 ]; then

3086 ## err ROWID=$rowid

3087 sql="SELECT growid || ':' || gname FROM movablegroups;"

3088 cat<<-EOF

3089 <div class="fold">

3090 `cgi_checkbox mv send id="mv"`<label

3091 for="mv">この話題をグループ所有に移動する</label>

3092 <div>

3093 <form action="?mvart" method="POST" enctype="multipart/form-data">

3094 移動先グループ:

3095 <select name="mv2grp">

3096 EOF

3097 query ".mode html"

3098 query<<-EOF |

3099 $sql

3100 .mode list

3101 EOF

3102 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3103 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3104 cat<<-EOF

3105 </select>

3106 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3107 そのグループに加入しているものに限られます)</p>

3108 <p>`cgi_checkbox cfm yes`<label>確認

3109 (この操作は元に戻すことができません)</label></p>

3110 `cgi_hidden blogrowid $rowid`

3111 `cgi_submit 移動`

3112 `cgi_reset Reset`

3113 </form>

3114 </div>

3115 </div>

3116 EOF

3117 fi

3118 # end of isuser "$owner"

3119 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3120 [ -n "$hexteams" ];}; then

3121 none="`echo なし|hexize`"

3122 cat<<-EOF

3123 <div class="fold">

3124 `cgi_checkbox mv2team send id="mv2team"`<label

3125 for="mv2team">この話題を以下のチームのものにする</label>

3126 <div><p>現在の所属チーム設定:

3127 `query "SELECT

3128 coalesce((SELECT val FROM blog_s

3129 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3130 AND key='team'),

3131 ':なし');"`</p>

3132 <form action="?mvart" method="POST" enctype="multipart/form-data">

3133 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3134 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3135 `cgi_hidden blogrowid $rowid`<br>

3136 `cgi_submit 移動`

3137 `cgi_reset Reset`

3138 </form></div></div>

3139 EOF

3140 fi

3141 }

3142 mvart() { # move diary to some group or team

3143 # or move blog of group to team which belong to the group

3144 blogrowid=`getpar blogrowid`

3145 cfm=`getpar cfm`

3146 ##### echo move blog:$blogrowid to $mv2grp | html p

3147 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3148 . ./s4-blog.sh

3149 if [ -z "$blogrowid" ]; then

3150 echo "無効な指定です(mvart)。" | html p

3151 return

3152 elif [ x"$cfm" != x"yes" ]; then

3153 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3154 elif { mv2grp=`getpar mv2grp`

3155 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3156 [ -n "$mv2grp" ]; }; then

3157 crview4article $blogrowid

3158 ########## TRANSACTION BEGIN

3159 query "BEGIN;"

3160 n=`query "SELECT count(*) FROM writeusers;"`

3161 ## err Nwriteuser=$n

3162 if [ $((n)) -gt 0 ]; then

3163 query<<-EOF

3164 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3165 WHERE key='owner'

3166 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3167 AND $mv2grp IN (SELECT growid FROM movablegroups);

3168 EOF

3169 fi

3170 query "END;"

3171 ########## TRANSACTION END

3172 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3173 [ -n "$mv2team" ];}; then

3174 # blog owner can move it to ANY team

3175 case "$mv2team" in

3176 'なし')

3177 cat<<-EOF

3178 DELETE FROM blog_s

3179 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3180 AND key='team';

3181 EOF

3182 ;;

3183 "") ;;

3184 *)cat<<-EOF

3185 BEGIN;

3186 REPLACE INTO blog_s(id, key, val)

3187 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3188 'team', '$mv2team');

3189 REPLACE INTO blog_s(id, key, val)

3190 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3191 'notify', 'all'); -- Change notify to all

3192 END;

3193 EOF

3194 esac | query

3195 fi

3196 blog_reply $blogrowid

3197 echo yes | html p

3198 }

3199 editart() { # $1=article-rowid $2=blogrowid

3200 rowid=${1%%[!A-Z0-9a-z_]*}

3201 blogrowid=${2%%[!A-Z0-9a-z_]*}

3202 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3203 echo "表示する記事番号が未指定です。" | html p

3204 return

3205 fi

3206 owner=`getvalbyid blog owner $blogrowid`

3207 title=`getvalbyid blog title $blogrowid`

3208 author=`getvalbyid article author $rowid`

3209 ## err EDITart: owner=$owner, author=$author

3210 if isgrpowner "$user" "$owner"; then

3211 : EDIT OK

3212 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3213 echo "本人か所有者しか編集できません." | html p

3214 return

3215 fi

3216 aid=`query "select id from article where rowid=$rowid;"`

3217 tmpout=$tmpd/editart.$$.out

3218 GF_ACTION="?replyblog+$blogrowid#$aid" \

3219 edittable $formdir/article.def article $rowid \

3220 > $tmpout

3221 rm -f /tmp/editart.out

3222 # Cannot use pipelining to m4 with genform() because of stdin stack

3223 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3224 -D_FORM_="syscmd(cat $tmpout)" \

3225 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3226 -D_BLOGS_= -D_DUMPTABLE_= \

3227 $layout/html.m4.html $layout/form+dump.m4.html

3228 }

3229 send2mem() {

3230 rowid=`getpar grp`

3231 rowid=${rowid%%[!0-9]*} # Cleaning

3232 if [ -z "$rowid" ]; then

3233 echo "グループが未指定です。" | html p

3234 return

3235 fi

3236 message=`getpar message`

3237 if [ -z "$message" ]; then

3238 echo "文章を入れてください。" | html p

3239 return

3240 fi

3241 grp=`getgroupbyid $rowid`

3242 members=`collectemail "$grp"`

3243 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3244 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3245 mailfrom="`gecos "$user"` <$mailfrom>"

3246 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3247 msg=$(cat<<-EOF

3248 $urlbase?grp+$rowid

3249 グループ $grp に所属する

3250 `gecos $user` さんよりメッセージ:

3251

3252 $message

3253 EOF

3254 )

3255 # smail rcpt subj (file)

3256 for m in $members; do

3257 echo "$msg" |

3258 MAIL_FROM=$mailfrom \

3259 SENDER=$noreply \

3260 REPLYTO=$mailfrom \

3261 smail "$m" "$sj"

3262 done

3263 cat<<EOF

3264 <p>以下のユーザに送信しました。</p>

3265 <pre>

3266 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3267 </pre>

3268 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3269 EOF

3270 }

3271 joingrpadmit() {

3272 # $1=yes/no $2=session-key

3273 if [ -z "$2" ]; then

3274 echo "bye bye" | html p; return

3275 fi

3276 t_usr=`session=$2 getpar adduser`

3277 t_grp=`session=$2 getpar group`

3278 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3279 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3280 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3281 echo "無効な加入依頼です。" | html p

3282 echo "有効期限が切れたか、

3283 他の管理者がいる場合は処理済みの可能性があります。" | html p

3284 return

3285 fi

3286 if ! isgrpowner "$user" "$t_grp"; then

3287 echo "グループ管理者のみの機能です。" | html p; return

3288 fi

3289 case $1 in

3290 yes) joingrp "$t_grp" "$t_usr" yes ;;

3291 no) joingrp "$t_grp" "$t_usr" no ;;

3292 *)

3293 echo "無効な指定です($1)。" | html p

3294 return ;;

3295 esac

3296 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3297 rcpts="`getgroupadminmails "$t_grp"` $user"

3298 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3299 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3300 に

3301 $t_usr

3302 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3303 しました。"

3304 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3305 query "delete from session where id='$2';"

3306 echo "$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 }