s4: 33df7d50532e s4-funcs.sh

s4

view s4-funcs.sh @ 731:33df7d50532e

Empty-repost workaround in EDIT mode added

author	HIROSE Yuuji <yuuji@gentei.org>
date	Mon, 01 Jun 2020 20:25:02 +0900
parents	2c0c25fc9f68
children	98702a1251cd

line source

1 #!/bin/sh

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

3 # $HGid$

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

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

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

9 myargs="$@"

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

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

12 tmpdir=${TMPDIR:-tmp}

13 dbdir=${DBDIR:-db}

14 tmpfiles=""

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

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

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

18 workdb=$dbdir/tmpdata.sq3

19 sessdb=$dbdir/sess.sq3

20 sesstb=tmp.sess

21 listentlimit=${LISTENTLIMIT:-30}

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

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

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

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

26 templ=${TEMPL:-templ}

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

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

29 imgdir=${IMGDIR:-img}

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

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

32 msgdir=$templ/msg

33 timeout="+2 days"

34 memoplimitdays="7"

35 dumpcollen=22

36 #thumbxy=120x120

37 thumbxy=96x96

38 iconxy_S=80x80

39 iconxy_M=400x400

40 maximagexy=1600x1600

41 ### maximagexy=400x400

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

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

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

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

47 file_accept_help="

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

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

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

51 "

52 file_warn="$file_accept_help

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

54 blogreadflagrowid=0

55 blogcutoffflagrowid=-1

56 whatsnewdays=${WHATS_NEW_DAYS:-14}

58 tconfs=""

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

60 conftbl=_tblconf

61 nl="

62 "

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

64 iconcachekey="profimgcache_S"

65 case "$HTTP_USER_AGENT" in

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

67 *) touchpanel="" ;;

68 esac

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

71 : <<EOF

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

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

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

76 レポート提出管理

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

79 [2016]

80 7/12 根本への反省

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

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

84 [以下2015]

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

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

87 Web

88 締切設定

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

91 ○自分の提出物リスト

93 7/19 ○設置

94 ○一斉送信

95 ○getparfilename の tmpd の扱い

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

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

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

99 ○添付ファイル回収

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

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

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

103

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

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

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

107 ○レポート提出モード

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

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

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

111

112

113 7/9 ○管理者の追加

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

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

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

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

118

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

120

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

122 ○グループ検索

123

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

125

126

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

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

129

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

131 ○6/1 par2tableを triplex 対応に

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

133 →とすると一気に

134

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

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

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

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

139

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

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

142 ## 1. passwd

143 ## 2. grp

144 ## 3. grp_mem

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

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

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

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

149

150 ## ● listの定義:

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

152 ## ● hashの定義:

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

154

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

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

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

158 ## csv2sq3 で .csv.sq3 の Makefile

159

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

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

162 ## type := root | comment

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

164 ## unique(id, filename)

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

166

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

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

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

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

171 ## できたー!

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

173 ↓

174 ↓以下に変更

175 with recursive allmem as

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

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

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

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

180

181

182 with recursive allmem as

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

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

185 where allmem.val=grp_m.gname)

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

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

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

189

190

191 ## triggerもできた。

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

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

194

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

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

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

198 ## グループ管理

199 ## 個人情報管理

200 ##

201

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

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

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

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

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

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

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

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

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

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

212

213 ## ログテーブル

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

215

216

217

218 ■表設計

219 * 3つの表に分散管理

220 id格納表 + hash表 + list表

221 * *_s *_m

222

223

224

225 user, user_map, user_col

226

227 ■抽象エントリタイプ

228 * user

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

230 * group

231 権限判定に利用

232 * serial

233 自動idとして機能

234 * password

235 入力 type=passwordで入力

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

237 * session

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

239 * text

240 入力 type=text

241 * textarea

242 入力 textarea

243 * image|document

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

245 * owner

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

247 * gowner

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

249 * timestamp

250 datetime()

251 * parent

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

253 * path

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

255

256 格納タイプ

257 * list

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

259 * hash

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

261

262 オブジェクトタイプ

263 * entry

264 id, title, owner

265 * textpart

266 id, parentID, text

267 * binarypart

268 id, parentID, contenttype, filename, content

269 * content

270 hash(textpart), list(binarypart)

271 * topic

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

273 * reply

274 id, parentID, content

275 * blog

276 list(entry)

277 blog = [topic, list(reply)]

278

279

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

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

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

283 "author" => "taro",

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

285 "parent" => "/",

286 "path" => "/1",

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

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

289 {"serial" => 2,

290 "author" => "hanako",

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

292 "parent" => "/",

293 "path" => "/2",

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

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

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

297

298

299 user:=

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

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

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

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

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

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

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

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

308

309 変換表

310 /user/email=m

311

312 blog:=

313 シリアル:id:p:serial

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

315 所有者:owner:s:owner:

316 時刻:ctime:s:stamp:

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

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

319

320 article:=

321 シリアル:id:p:serial

322 筆者:author:s:owner

323 時刻:ctime:s:stamp:

324 参照元:parent:s:parent:

325 パス:path:s:path:

326 本文:text:s:textarea:

327 画像:image:m:image:

328

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

330

331

332 EOF

333

334 logstart() {

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

336 }

337 logend() {

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

339 }

340 sqlog() {

341 logstart

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

343 cat >> $querylog

344 else

345 echo "$*" >> $querylog

346 fi

347 logend

348 }

349 sq() {

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

351 logstart

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

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

354 else

355 echo "$@" >> $querylog

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

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

358 fi

359 logend

360 }

361 dbsetup() {

362 pipedir=$tmpdir/pipedir

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

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

365 suf=`date +%s`

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

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

368 mkfifo $sqi $sqo

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

370 sq $db < $sqi &

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

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

373 exec 2>> $tmpdir/error.out

374 fi

375 exec 3>> $tmpdir/debug.out

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

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

378 rm $sqi

379 # Attach supplemental DB

380 cat >&5 <<-EOF

381 .output /dev/null

382 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

386 .output stdout

387 EOF

388 }

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

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

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

392 fi

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

394 }

395 cleanup() {

396 trap '' INT HUP EXIT TERM PIPE

397 echo .quit >&5

398 kill $sq3pid

399 kill $sq3pid

400 rm -f $sqo $sqi

401 rm -rf $tmpfiles

402 cleanup2

403 }

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

405 # simple redirection for the sake of speed.

406 query() {

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

408 echo ".output $sqo" >&5

409 logstart

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

411 tee -a $querylog

412 else

413 echo "$@" >> $querylog

414 echo "$@"

415 fi >&5

416 echo ".output stdout" >&5

417 cat $sqo

418 rc=$?

419 logend

420 return $rc

421 }

422 _m4() {

423 #S4NAME=f,f,f

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

425 }

426 ismember() {

427 # $1=user, $2=group

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

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

430 }

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

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

433 }

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

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

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

437 }

438 isgrpowner() (

439 # $1=user, $2=group

440 gn=`sqlquote "$2"`

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

442 err isgrpowner: $sql

443 test -n "`query $sql`"

444 )

445 isgrpownerbygid() (

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

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

448 err isgrpownerbygid: $sql

449 test -n "`query $sql`"

450 )

451 getgroupadminmails() {

452 # $1=group

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

454 email4group "$1" "$i" ;

455 done

456 }

457 getgroupadmins() { # $1=group

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

459 qgrp=`sqlquote "$1"`

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

461 }

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

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

464 getvalbyid grp $2 \

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

466 }

467 getgroupbyid() {

468 # $1=id|gname

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

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

471 # err ggbyid: `echo $sql`

472 query $sql

473 }

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

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

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

477

478 # Return true when anonymous mode

479 [ "$anonymousmode" ] && return 0

480 # case `getvalbyid blog mode $2` in

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

482 # *closed*)

483 # owner=`getvalbyid blog owner $2`

484 # if isgrp $owner; then

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

486 # elif isuser $owner; then

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

488 # fi

489 # esac

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

491 sql="with getblog as (

492 select key,val from blog_s where id=(

493 select blogid from article where id in

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

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

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

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

498 isgrpadm as (select user from grp_adm where

499 gname=(select val from getowner) and

500 user='$1'),

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

502 select case

503 when (select author from article where

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

505 then 'author'

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

507 then 'open'

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

509 then CASE

510 WHEN (SELECT val FROM isgrp) IS NULL

511 THEN

512 CASE WHEN (SELECT val from getowner)

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

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

515 ELSE ''

516 END

517 WHEN (select user from isgrpadm) IS NOT NULL

518 THEN 'i-am-admin'

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

520 END

521 when (select val from getmode) is null

522 then 'open'

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

524 then (SELECT user FROM isgrpadm)

525 when (select author from article where

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

527 then 'user+author'

528 else '' end;"

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

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

531 # result=`query "$sql"`

532 # err FileAccessibility=$result

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

534 }

535 linkhome() {

536 # $1=UserOrGroup

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

538 if isuser $1; then

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

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

541 else

542 echo -n "grp+$1"

543 fi

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

545 }

546 hreflink() {

547 # s4 specific notation:

548 # ^href=URL

549 # ^iframe=URL

550 # ^video=URL

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

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

553 # OSM umap Wikistyle Notation:

554 # [[URL]] - Simle Link

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

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

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

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

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

560 # Other Style

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

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

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

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

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

566 # SPC+SPC+$ - <br>

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

568 checkboxON="${cb} checked>"

569 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

594

595 }

596 minitbl() {

597 sed -n '

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

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

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

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

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

603 H; # Concat this line to HoldSpace

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

605 $ b cont

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

607 }

608 :cont

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

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

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

612 # . of ^. is workaround for FreeBSD sed

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

614 p; # Print whole "table" element

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

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

617 }

618 x; # Back to the newest line

619 p; # Print rest' | miniul

620 }

621 miniul() {

622 sed -e '

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

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

625 :top

626 s/\n//;

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

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

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

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

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

632 $ b cont

633 N; # 次の行を読む

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

635 /^ /b top

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

637 p; s/.*//;

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

639 /^\* /b top

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

641 }

642

643 :cont

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

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

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

647 H; x

648 }

649 x'

650 }

651 acclog() (

652 # $1=table, $2=rowid

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

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

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

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

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

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

659 fi

660 )

661 gecos() (

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

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

664 )

665 setpar() {

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

667 case "$1" in

668 user) _user="$v" ;;

669 skey) _skey="$v" ;;

670 esac

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

672 }

673 replpar() {

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

675 }

676 getpar() {

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

678 val=""

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

680 user) val=$_user ;;

681 skey) val=$_skey ;;

682 esac

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

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

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

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

687 fi

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

689 case "$var" in

690 owner)

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

692 echo $user; return

693 elif ismember $user $val; then

694 echo $val; return

695 fi ;;

696 esac

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

698 echo "$val"

699 }

700 setskey() {

701 # For quick response...(?)

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

703 }

704 chkskey() {

705 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

711 return 0

712 fi

713 return 1

714 }

715 resetskey() {

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

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

718 fi

719 }

720 getpartype() {

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

722 }

723 getparcount() {

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

725 }

726 getparfilename() {

727 # null if type of $1 is not file

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

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

730 }

731 sqlquote() {

732 (v="$1"

733 case "$v" in

734 "") return ;; # null

735 "X'"*) # quoted hex string

736 echo $1 ;;

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

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

739 echo "\"$v\""

740 return ;;

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

742 echo "\"$v\""

743 return ;;

744 *)

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

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

747 else

748 echo "\"$v\""

749 fi ;;

750 esac)

751 }

752 sqlquotestr() (

753 case "$1" in

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

755 echo "'$v'" ;;

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

757 esac

758 )

759 mktempd() {

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

761 }

762 getcachedir() { # $1=maintable

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

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

765 else

766 echo $tmpd/$thumbxy

767 fi

768 }

769 getval() {

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

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

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

773 echo "$user" ;;

774 stamp|datetime)

775 date "+%F %T" ;;

776 serial)

777 (s=`getpar $2`

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

779 *)

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

781 esac

782 }

783

784 getvalquote() {

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

786 (v=`getval "$@"`

787 case "$v" in

788 "") echo NULL ;;

789 *) sqlquote "$v" ;;

790 esac)

791 }

792 getparquote() {

793 sqlquote "`getpar $1`"

794 }

795 getbinbyid() {

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

797

798 }

799 getvalbyid() {

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

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

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

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

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

805

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

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

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

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

810 return

811 fi

812 done

813 rowid=$3

814 pk=`gettblpkey $1`

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

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

817 td=${4:-$tmpd}

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

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

820 for kt in s m; do

821 t=${1}_$kt

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

823 vcount=1 # count(val)

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

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

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

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

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

829 if [ $kt = m ]; then

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

831 # Reset val to store filenames if type is string

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

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

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

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

836 i=0

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

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

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

840 i=$((i+1))

841 fn=$c.$i

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

843 case $type in

844 file:*)

845 #file=$td/$val

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

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

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

849 # FOR SPEED: Skip file generation if imgcache exists

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

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

852 && continue

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

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

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

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

857 select rowid from $cond $slice;

858 .output '$td/$fn'

859 select val from $cond $slice;

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

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

862 .output stdout

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

864 EOF

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

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

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

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

869 ls -lh "$file" |

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

871 case "$type" in

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

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

874 *|*:[Aa]pplication*)

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

876 png:- > "$file"

877 ;;

878 esac

879 ;;

880 *)

881 sq $db<<EOF

882 .output $td/$fn.rowid

883 select rowid from $cond $slice;

884 .output $td/$fn

885 select val from $cond $slice;

886 EOF

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

888 ;;

889 esac

890 done

891 else

892 rm -f $td/$c.count

893 case $type in

894 file:*)

895 echo "$val" \

896 | while read fn; do

897 file=$td/$fn

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

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

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

901 | unhexize > "$file"

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

903 case $type in

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

905 *:[Aa]pplication*)

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

907 png:- > $file ;;

908 esac

909 fi

910 done

911 ;;

912 esac

913 fi

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

915 return

916 fi

917 done

918 done)

919 }

920 getvalbypkey() (

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

922 pk=`gettblpkey $1`

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

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

925 )

926 getvalbycond() {

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

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

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

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

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

932 fi

933 }

934 getpwfield() {

935 # getpwfield user column

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

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

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

939 echo "$val"

940 return 0

941 else

942 return 1

943 fi

944 }

945 numericalize() {

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

947 }

948 encode() {

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

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

951 sha1=sha1

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

953 sha1=sha1sum

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

955 sha1=gsha1sum

956 fi

957 fi

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

959 }

960 enjpeg() {

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

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

963 cjpeg="cjpeg"

964 else

965 cjpeg="convert - jpeg:-"

966 fi

967 fi

968 $cjpeg "$@"

969 }

970 mycrypt() (

971 key=$1 salt=$2

972 # err \$2=$2

973 case $2 in

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

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

976 esac

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

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

979 )

980 hexize() {

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

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

983 hexize="xxd -p"

984 else

985 hexize_hd() {

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

987 }

988 hexize="hexize_hd"

989 fi

990 fi

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

992 }

993 unhexize() {

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

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

996 unhex="xxd -p -r"

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

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

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

1000 EOF

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

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

1003 # world writable...:)

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

1005 fi

1006 fi

1007 cat "$@" | $unhex

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

1009 }

1010 percenthex() {

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

1012 }

1013 htmlescape() {

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

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

1016 }

1017 enascii() {

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

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

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

1021 else

1022 enascii_now=`date +%FT%T`

1023 enascii_sed() {

1024 nkf -Z0Z1Z2 \

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

1026 }

1027 enascii="enascii_sed"

1028 fi

1029 fi

1030 cat "$@" | $enascii

1031 }

1032 size_h() {

1033 i="$1" oi=$1

1034 set -- B B KB MB GB TB

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

1036 oi=$i

1037 i=$((i/1024))

1038 shift

1039 done

1040 echo ${oi}$1

1041 }

1042 gettblconf() {

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

1044 ## tconfs=`sq $db \

1045 tconfs=`query \

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

1047 fi

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

1049 }

1050 gettblkeys() {

1051 # $1=tbl

1052 gettblconf

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

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

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

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

1057 case $type in

1058 *p*)

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

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

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

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

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

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

1065 esac

1066 case $type in

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

1068 esac

1069 done

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

1071 }

1072 gettblpkey() {

1073 # $1=tbl

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

1075 }

1076 gettblfkey() {

1077 (x=`gettblkeys $1`

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

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

1080 }

1081 gettblcols() {

1082 (x=`gettblkeys $1`

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

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

1085 }

1086 gettbl_s_cols() {

1087 (x=`gettblkeys $1`

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

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

1090 }

1091 gettbl_m_cols() {

1092 (x=`gettblkeys $1`

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

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

1095 }

1096 gettbl_h_cols() {

1097 (x=`gettblkeys $1`

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

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

1100 }

1101 gettbl_coltype() (

1102 gettblconf

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

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

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

1106 )

1107 is_hidden() {

1108 # $1=Tbl $2=col

1109 gettblconf

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

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

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

1113 case $x in

1114 *h*) return 0 ;;

1115 *) return 1 ;;

1116 esac

1117 }

1118

1119 dbsetbyid() {

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

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

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

1123 quotedp=$(sqlquotestr "$p")

1124 unset primary update

1125 gettblconf

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

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

1128 #err conf=$conf

1129 case ${conf#*=} in

1130 p*) primary=1 ;;

1131 f*) update=1 ;;

1132 u*) ;;

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

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

1135 esac

1136 #err t=$t

1137 type=string fn=""

1138 case $conf in

1139 */password)

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

1141 ;;

1142 */image*|*/document*)

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

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

1145 ;;

1146 esac

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

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

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

1150 if [ "$primary" ]; then

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

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

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

1154 elif [ "$update" ]; then

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

1156 else

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

1158 fi

1159 )

1160 }

1161 expire() (

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

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

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

1165 )

1166 addsession() {

1167 # expireをセット

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

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

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

1171 # Remove old session parameters

1172 now=`expire now`

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

1174 }

1175 gencookie() (

1176 path=${URL#*:/}

1177 path=${URL%/*}

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

1179 for kv; do

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

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

1182 done

1183 )

1184 contenttype() {

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

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

1187 }

1188 putheader() {

1189

1190 }

1191 putfooter() {

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

1193 }

1194 getcookie() {

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

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

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

1198 case "$k" in

1199 user) _user="$v" ;;

1200 skey) _skey="$v" ;;

1201 esac

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

1203 done

1204 }

1205 genrandom() {

1206 # $1=columns (default: 10)

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

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

1209 }

1210 genserial() {

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

1212 }

1213 smail() {

1214 # smail rcpts subj (file)

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

1216 # $MAIL_FROM <- From: header value

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

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

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

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

1221 sender=${SENDER:-$admin}

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

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

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

1225 }

1226 smail_queue_flush() {

1227 # $1=timelimit

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

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

1230 SELECT rowid FROM smailq

1231 GROUP BY rcpts, subj

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

1233 EOF

1234 )

1235 for rid in $rowids; do

1236 sq $workdb \

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

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

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

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

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

1242

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

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

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

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

1247 SELECT time, text FROM smailq

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

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

1250 ORDER by time;

1251 EOF

1252 then

1253 echo rowid=$rid

1254 cat <<-EOF | sq $workdb

1255 DELETE FROM smailq

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

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

1258 EOF

1259 fi

1260 fi

1261 done

1262 }

1263 smail_queue() {

1264 # $1=Rcpts, $@=subj

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

1266 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1275 EOF

1276 )

1277 ## XXX: Adhoc load mitigation

1278 case "$now" in

1279 *[01])

1280 err flush_queue=$mintime

1281 smail_queue_flush ;;

1282 esac

1283 fi

1284 }

1285 setviastring() {

1286 table=par

1287 oifs="$IFS"

1288 IFS="&"

1289 for us in $1; do

1290 k=${us%%=*}

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

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

1293 done

1294 IFS="$oifs"

1295 }

1296 checkdomain() (

1297 # Check the validity of domain by referring DNS

1298 item=$1

1299 err checkdomain $1

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

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

1302 )

1303 pwcheck() {

1304 # $1=passwd

1305 dbpswd=`getpwfield $user pswd`

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

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

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

1309 }

1310 mypwhash() {

1311 mycrypt "`cat`" `genrandom 5`

1312 }

1313 wasureta() {

1314 user=$1

1315 if ! checkdomain $user; then

1316 contenttype; echo

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

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

1319 putfooter

1320 exit 0

1321 fi

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

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

1324 encpswd=`echo $newpswd|mypwhash`

1325 dbsetbyid user $user pswd "$encpswd"

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

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

1328 $msgdir/mail-newaccount.m4 \

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

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

1331 }

1332 checkauth() {

1333 user=`getpar user`

1334 skc=`getpar skey` # from cookie

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

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

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

1338 if chkskey "$skc"; then

1339 return 0

1340 fi

1341 fi

1342 pswd=`getpar pswd`

1343 quser=`sqlquotestr "$user"`

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

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

1346 return 4 # 4=server too heavy

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

1348 return 2 # 2=login fail

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

1350 wasureta "$user"

1351 return 1 # wasureta error

1352 fi

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

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

1355 if pwcheck "$pswd"; then

1356 newsession=`genrandom 34`

1357 if setskey "$user" "$newsession" &&

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

1359 gencookie "user=$user" "skey=$newsession"

1360 return 0

1361 else

1362 return 4 # Heavy load??

1363 fi

1364 fi

1365 return 2 # Password mismatch

1366 }

1367 showlogin() {

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

1369 test -z "$args" && resetskey

1370 s4name=${S4NAME:-s4}

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

1372 cat $layout/login.m4.html

1373 echo '</body></html>'

1374 ) | _m4 \

1375 -D_BODYCLASS_="login" \

1376 -D_TITLE_="$s4name" \

1377 -D_SYSNAME_="Welcome to $s4name" \

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

1379 exit 0

1380 }

1381 dologin() {

1382 checkauth

1383 st=$?

1384 if [ $st != 0 ]; then

1385 contenttype; echo

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

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

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

1389 showlogin # and EXIT

1390 fi

1391 }

1392

1393 # Do instant jobs here

1394 dbsetup

1395 trap cleanup INT HUP EXIT TERM PIPE

1396 # trap cleanup INT HUP

1397

1398 err() {

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

1400 }

1401

1402 cgiinit() {

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

1404 tmpd=`tmpd=$tmpdir mktempd`

1405 tmpf=$tmpd/stream.$$

1406 tmpfiles=$tmpfiles" $tmpd"

1407 addsession $session

1408 getcookie

1409 case "$REQUEST_METHOD" in

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

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

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

1413 # cat > $tmpf # too much?

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

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

1416 s="`cat $tmpf`"

1417 ;;

1418 esac

1419 case "$CONTENT_TYPE" in

1420 *boundary*)

1421 bndry=${CONTENT_TYPE#*boundary=}

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

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

1424 do

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

1426 #echo u=$us

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

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

1429 # err k=$k v=$v

1430 case "$k" in

1431 *:filename)

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

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

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

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

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

1437 :

1438 fi

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

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

1441 case "$mimetype" in

1442 [Ii]mage/x-xcf)

1443 bzip2 "$tmpd/$v"

1444 v=${v}.bz2

1445 ;;

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

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

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

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

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

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

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

1453 v=$vjpg

1454 else

1455 mimetype="Not supported"

1456 fi

1457 ;;

1458 [Ii]mage/*)

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

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

1461 ;;

1462 esac

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

1464 then

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

1466 continue

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

1468 replpar text string \

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

1470 continue

1471 fi

1472 ;;

1473 *)

1474 type='string'

1475 ;;

1476 esac

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

1478 setpar "$k" "$type" "$v"

1479 done

1480 ;;

1481 *)

1482 setviastring "$s"

1483 ;;

1484 esac

1485 }

1486 email4group() {

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

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

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

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

1491 sql="WITH

1492 grpemails AS (

1493 SELECT gname, user, val email

1494 FROM grp_mem NATURAL JOIN grp_mem_s

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

1496 useremails AS (

1497 SELECT user.name, val email

1498 FROM user

1499 LEFT JOIN user_m

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

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

1502 FROM useremails u LEFT JOIN grpemails g

1503 ON u.name=g.user

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

1505 query "$sql"

1506 }

1507 email4groupbyuid() {

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

1509 g=$1; shift

1510 uids=`echo "$@"`

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

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

1513 email4group "$g" `query "$sql"`

1514 }

1515 myemail4group() {

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

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

1518 }

1519 collectmembersbyid() {

1520 # Collect user names of group specified by grid

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

1522 query "SELECT user FROM grp_mem \

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

1524 }

1525 collectmembersbyid() {

1526 # Collect user names of group name

1527 qgrp=`sqlquote "$1"`

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

1529 }

1530 collectgecosesbyid() {

1531 # Collect user gecoses of group

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

1533 query<<-EOF

1534 SELECT gecos

1535 FROM gecoses

1536 WHERE name IN (SELECT user FROM grp_mem

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

1538 EOF

1539 }

1540 collectemail() (

1541 # Collect email addresses for group $1

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

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

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

1545 for e; do

1546 if isuser "$e"; then

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

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

1549 else

1550 qgrp=`sqlquote "$e"`

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

1552 gmem="grp_mem"

1553 else

1554 tm=`sqlquote "$TEAM"`

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

1556 fi

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

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

1559 $gmem g left join grp_mem_s s

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

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

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

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

1564 query "$sql"

1565 fi

1566 done

1567 )

1568 sendinvitation() (

1569 # $1=email

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

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

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

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

1574 gecos=`gecos`

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

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

1577 _m4 -D_URL_="$urlbase" \

1578 -D_USER_="$name" \

1579 -D_EMAIL_="$1" \

1580 -D_REGIST_="$regist" \

1581 -D_ADMIN_="$admin" \

1582 $msgdir/mail-invite.m4 \

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

1584 return 0

1585 )

1586 emaildomaincheck() {

1587 case "$1" in

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

1589 *@*)

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

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

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

1593 return 2

1594 fi

1595 return 0

1596 ;;

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

1598 esac

1599 }

1600 invite() {

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

1602 case "$email" in

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

1604 *@*)

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

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

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

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

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

1610 elif sendinvitation $email; then

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

1612 else # Cannot be reached here

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

1614 fi ;;

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

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

1617 esac

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

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

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

1621 fi

1622 if [ -f $invite_policy ]; then

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

1624 else

1625 pol="$invite_policy"

1626 fi

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

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

1629 -D_INVITE_POLICY_="$pol" \

1630 $layout/html.m4.html $layout/invite.m4.html

1631 }

1632 regist() {

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

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

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

1636 echo "bye bye" | html p

1637 reutrn

1638 fi

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

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

1641 cat<<EOF

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

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

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

1645 EOF

1646 return

1647 fi

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

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

1650 # Fake login password to wasureta

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

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

1653 wasureta $email

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

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

1656 addsession $1 # for removal after 1 minute

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

1658 $layout/login.m4.html

1659 return

1660 }

1661 group_safename() {

1662 # Convert $1 to safe group name

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

1664 }

1665 groupupdate() {

1666 gname=`getpar gname`

1667 qgname=`sqlquote "$gname"`

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

1669 # See ALSO same job in showgroup()

1670 newgname=`group_safename "$gname"`

1671 err newgname=$newgname

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

1673 err NewGNAME: gname=$newgname

1674 gname=$newgname

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

1676 replpar gname string "$gname"

1677 fi

1678 # Name confliction check

1679 parow=`getpar rowid`

1680 ## err parow=$parow

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

1682 query "BEGIN EXCLUSIVE;"

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

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

1685 if [ $count -gt 0 ]; then

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

1687 query "END;"

1688 return

1689 fi

1690 par2table $formdir/grp.def

1691 query "END TRANSACTION;"

1692 # Remove orphan

1693 : <<EOF

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

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

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

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

1698 EOF

1699 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1703 sql="delete from blog where id in

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

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

1706 query "$sql";

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

1708 return # and return

1709 fi

1710 fi

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

1712 fi

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

1714 grid=$(query $sql)

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

1716 grp $grid

1717 }

1718 groupclone() {

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

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

1721 case "$1" in

1722 */noteam)

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

1724 esac

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

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

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

1728 return

1729 fi

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

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

1732 return

1733 fi

1734 i=0

1735 while true; do

1736 copy="-copy$i"

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

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

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

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

1741 i=$((i++))

1742 continue

1743 fi

1744 break

1745 done

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

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

1748 query<<-EOF

1749 BEGIN;

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

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

1752 SELECT $newqname, key, val

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

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

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

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

1757 -- Copy members and their configuration --

1758 REPLACE INTO grp_mem SELECT $newqname, user

1759 FROM grp_mem WHERE gname=$qgrp;

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

1761 FROM grp_mem_s WHERE gname=$qgrp;

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

1763 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1764 -- Copy administrators --

1765 REPLACE INTO grp_adm SELECT $newqname, user

1766 FROM grp_adm WHERE gname=$qgrp;

1767 COMMIT;

1768 EOF

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

1770 STOPCLONEMSG=1 groupconf "$newrowid"

1771 }

1772 groupman() {

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

1774

1775 GF_STAGE="grpconf"

1776 GF_STAGE=groupupdate

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

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

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

1780 -D_DUMPTABLE_="syscmd(cat)" \

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

1782 }

1783 userconf() {

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

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

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

1787 }

1788 groupconf() {

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

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

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

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

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

1794

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

1796 grp=`getgroupbyid $rowid`

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

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

1799 return

1800 fi

1801

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

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

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

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

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

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

1808 <div>

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

1810 <table>

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

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

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

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

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

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

1817 </table>

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

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

1820 </div>

1821 EOF

1822 fi

1823 }

1824 mems() {

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

1826 kwd=`getpar kwd`

1827 listmember $kwd

1828 }

1829 grps() {

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

1831 kwd=`getpar kwd`

1832 listgroup $kwd \

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

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

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

1836 $layout/form+dump.m4.html

1837 }

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

1839 gpg=`getpar grp`

1840 grid=${1:-$gpg}

1841 grp=`getgroupbyid "$grid"`

1842 ## . ./s4-blog.sh

1843 jg=`getpar joingrp`

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

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

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

1847 fi

1848 htmlheader=$layout/html.m4.html

1849 showgroup "$grid"

1850 }

1851 sql4interestblogs() {

1852 cat<<EOF

1853 CREATE TEMPORARY VIEW interestblogs AS

1854 SELECT blog.rowid rid, id, author

1855 FROM blog

1856 NATURAL JOIN

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

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

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

1860 WHEN (SELECT user FROM grp_mem

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

1862 THEN 0

1863 ELSE 1

1864 END;

1865 EOF

1866 }

1867 listnewblogsql() { # $1=user

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

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

1870 newdays=${newdays:-14}

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

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

1873 WHERE user='$user'

1874 AND tblrowid IN

1875 ($blogreadflagrowid,

1876 $blogcutoffflagrowid)),

1877 $basetime -- "0"

1878 );"`

1879 cat<<EOF

1880 `sql4interestblogs`

1881 WITH article_ctime as (

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

1883 FROM article join article_s s using(id)

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

1885 GROUP BY id

1886 ), blog_title_owner as (

1887 SELECT blg.rid brid, id,

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

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

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

1891 ), blogall as (

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

1893 ), news as (

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

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

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

1897 FROM blogall bl

1898 LEFT JOIN

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

1900 ON bl.brid=al.tblrowid

1901 WHERE atime < bl.ctime

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

1903 LIMIT 10

1904 ) SELECT brid LINK, "新着",

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

1906 ctime, title,

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

1908 FROM news;

1909 EOF

1910 }

1911

1912 search_form() {

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

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

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

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

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

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

1919 @week → 最近一週間

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

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

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

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

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

1925 auth=""

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

1927 case "$1" in

1928 author=*)

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

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

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

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

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

1934 ;;

1935 grp=*)

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

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

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

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

1940 ;;

1941 esac

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

1943 cat<<-EOF

1944 <div class="right">

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

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

1947 placeholder=" $placeholder " width="10" accesskey="k">

1948

1949 ${touchpanel:+<p class="help">$help</p>}

1950 <input type="hidden" name="stage" value="searchart">

1951

1952 </form>

1953 </div>

1954 EOF

1955 }

1956

1957 imgsrc_cache() (

1958 # $1 = directory for cache'ing

1959 # $2 = table (user_m or grp_m)

1960 # $3 = keycond (was: condition for choosingowner)

1961 # $4 = size : S = Small, M = Medium, O = Original

1962 dir="$1" tbl="$2"

1963 keycond="$3"

1964 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

1965 ORDER BY rowid DESC LIMIT 1"

1966 [ -d "$dir" ] || mkdir -p "$dir"

1967 tmpf=$tmpd/imgsrc_cache.$$

1968 case "$4" in

1969 [Ss]) size=S ;;

1970 [Oo]) size=O ;;

1971 *) size=M ;;

1972 esac

1973 # ImageCache filename storing schema:

1974 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

1975 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

1976 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

1977 valtype=`query "$sql0"`

1978 filename=${valtype%%//*}

1979 filetype=${valtype##*//file:}

1980 if [ x"$filename" = x"${filename%.*}" ]; then

1981 # If nor filename extension found, set it to image type

1982 case "$filetype" in

1983 image/*) filename=$filename.${filetype#image/} ;;

1984 esac

1985 fi

1986 cacheimg_S=$dir/S_$filename

1987 cacheimg_M=$dir/M_$filename

1988 cacheimg_O=$dir/$filename

1989 cacheimg=$dir/${size}_$filename

1990 sumfile="$dir/$filename.sum"

1991 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

1992 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

1993 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

1994 # if cache is fresh and has the same checksum,

1995 echo "<img src=\"$cacheimg\">"

1996 else

1997 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

1998 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

1999 fifo2=$fifo.2

2000 mkfifo $fifo $fifo2

2001 fmt=${filename##*.}

2002 ## [[ NOTE ]]

2003 ## a. convert oldimage newimage

2004 ## b. convert oldimage fmt:- | convert - newimage

2005 ## b is much smaller than a

2006 cat $tmpf | unhexize \

2007 | tee $fifo \

2008 | convert -define ${fmt}:size=${iconxy_M} \

2009 -resize ${iconxy_M}'>' - ${fmt}:- \

2010 | tee $fifo2 \

2011 | convert - "$cacheimg_M" &

2012 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2013 -resize ${iconxy_S}'>' - ${fmt}:- \

2014 | convert - "$cacheimg_S" &

2015 printf '%s' "<img src=\"data:${filetype},"

2016 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2017 echo '">'

2018 echo "$sum" > $sumfile

2019 fi

2020 ## Now preparing cache image, done.

2021 ## Store this information to DB

2022 stbl=${tbl%_m}_s # user_s or grp_s

2023 pkey=${keycond%%=*} # Primary Key name

2024 pval=${keycond#*=} # Primary Key value

2025 query <<-EOF

2026 REPLACE INTO $stbl($pkey, key, type, val)

2027 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2028 EOF

2029 )

2030

2031 showhome() {

2032 # $1=userRowIdToShow

2033 err showhome \$1=$1

2034 case "$1" in

2035 *@*) uname=`getvalbypkey user name "$1"` ;;

2036 *) uname=`getvalbyid user name $1` ;;

2037 esac

2038 ## err ShowHome: uname=$uname

2039 td=`getcachedir home/"$1"`

2040 gecos=`gecos "$uname"`

2041 ## err SH:gecos=$gecos

2042 GF_VIEWONLY=1

2043 cond="gname in (select gname from grp_mem where user='$uname')"

2044 search_form_args=""

2045 if [ x"$user" = x"$uname" ]; then

2046 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2047 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> /

2048 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2049 # Display folders

2050 sql="select count(id) from article_m where id

2051 in (select id from article where author='$user')

2052 and type like 'file:%';"

2053 ## err nfile-sql=`echo "$sql"`

2054 nfile=`query "$sql"`

2055 # err nfile=$nfile

2056 if [ $nfile -gt 0 ]; then

2057 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2058 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2059 fi

2060 else

2061 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2062 GROUP BY user;"`

2063 usermenu="<p>Last seen on $latestlog</p>"

2064 search_form_args="author=$uname"

2065 fi

2066 . ./s4-blog.sh

2067

2068 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2069 search_form "$search_form_args" > $sf

2070 printf "%s さん" "$gecos" > $tf

2071 { echo "<div class=\"noprofimg\">"

2072 viewtable $formdir/user.def user $1

2073 echo "</div>"

2074 } > $pf

2075

2076 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2077 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2078 imf=$tmpd/profimg.$$; touch $imf

2079 if [ -n "$img" ]; then

2080 if true; then

2081 tbl=user_m

2082 enticond="name='$uname'"

2083 imgsrc_cache "$td/main" user_m "$enticond" M

2084 else

2085 { printf '%s' "<IMG src=\"data:${img#file:},"

2086 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2087 | sed 's/$..$/%\1/g'

2088 echo '">'

2089 }

2090 fi > $imf

2091 fi

2092 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2093 val='$uname';"`

2094 ## REMOVE This comment block until 2019/7/1

2095 ## err "----- `gdate +%FT%T.%3N` ------------C"

2096 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2097 listblog $uname > $bf

2098 ## unset ddd

2099 ## err "----- `gdate +%FT%T.%3N` ------------D"

2100

2101 hometail=$tmpd/tail.$$

2102 mkfifo $hometail

2103

2104 #Calling listgroupbytable, originally here

2105

2106 (

2107 # Display Most Recent Entry

2108 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2109 shortval=${shortval:-val}

2110

2111 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2112 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2113 ctime,

2114 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2115 (SELECT gecos FROM gecoses

2116 WHERE name=(SELECT val FROM blog_s

2117 WHERE key='owner' AND id=b.id)) owner,

2118 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2119 FROM blog b

2120 JOIN

2121 (SELECT distinct blogid, a.id aid, max(val) ctime

2122 FROM article a, article_s s

2123 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2124 GROUP BY blogid ORDER BY val DESC LIMIT 50

2125 ) m

2126 ON b.id=m.blogid;"

2127 # This should be as follows

2128 : <<EOF

2129 WITH arts AS(

2130 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2131 a.blogid, a.id id, s.val ctime

2132 FROM article a NATURAL JOIN article_s s

2133 WHERE s.key = 'ctime' AND a.author='$user'

2134 GROUP by s.id

2135 )

2136 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2137 FROM arts a0

2138 JOIN

2139 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2140 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2141 ORDER BY ctime DESC LIMIT 50;

2142 EOF

2143

2144 cat<<-EOF

2145 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2146 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2147 <div class="lcto">

2148 `DT_VIEW=replyblog dumptable html blog`

2149 </div>

2150 EOF

2151 unset DT_SQL

2152 if [ x"$user" = x"$uname" ]; then

2153 # Display NEWS

2154 # 2016-06-26

2155 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2156 acclog blog $blogreadflagrowid

2157 # echo "全部既読にしました" | html p

2158 fi

2159 # 2016-02-19 Counting NEWS without using dumptable.

2160 sql=`listnewblogsql "$user"`

2161 # echo "$sql" > tmp/listnew

2162 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2163 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2164 cont=$((cont-1))

2165 err newcount=$cont

2166 if [ $cont -gt 0 ]; then

2167 #echo "全体の新着記事${cont}傑" | html h2

2168 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2169 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2170 cat<<-EOF | html form 'action="?home"'

2171 `cgi_checkbox readchk yes 'id="read"'`<label

2172 for="read">新着ふくめて全部読んだことにする</label>

2173 `cgi_submit '確定'`

2174 `cgi_hidden read yes`

2175 EOF

2176 echo "$new10 "

2177 echo "</div>"

2178 else # If news is 0, set log cut off flag

2179 acclog blog $blogcutoffflagrowid # for speed

2180 fi

2181 else # Not My Home ($user != $uname)

2182 : # DT_SQL=

2183 fi

2184 ) > $hometail & # Is background call safe to m4??

2185 #

2186 listgroupbytable $formdir/grp.def $cond |

2187 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2188 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2189 -D_PROFIMG_="spaste(\`$imf')" \

2190 -D_BLOGS_="spaste(\`$bf')" \

2191 -D_SEARCH_="spaste(\`$sf')" \

2192 -D_NBLOG_="$nblog" \

2193 -D_GROUPS_="syscmd(\`cat')" \

2194 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2195 $layout/html.m4.html $layout/home.m4.html

2196

2197 # Record access log

2198 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2199 }

2200 commission() { # $1=grp-rowid $2=user-rowid

2201 contenttype; echo

2202 ## err commission: "$@"

2203 gname=`getgroupbyid $1`

2204 echo "グループ $gname 管理者委任" \

2205 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2206 if [ -n "$2" ]; then

2207 grp_reg_adm "$@"

2208 else

2209 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2210 fi

2211 }

2212 listgroupbytable() {

2213 # $1=deffile $2...=condition

2214 tagline=`grep :tag: $1`; shift

2215 and="${1:+and }" where=${1:+where }

2216 href="<a href=\"$myname?grp+"

2217 echo '<div class="listgroup">'

2218 NGsql="select distinct tag from\

2219 (select gname, max(case key when 'tag' then val end) as tag, \

2220 max(case key when 'ctime' then val end) as ctime\

2221 from grp_s group by gname order by ctime);"

2222 sql="select val from grp_s where key='tag' $and$* group by val;"

2223 ## err ListGRP: query sql="$sql"

2224 for tag in `query "$sql"`

2225 do

2226 ## err ListGrp: tag=$tag

2227 tn=${tagline%%=${tag}*}

2228 tn=${tn##*[ :]}

2229 sql="select rowid||':'||gname as 'グループ名',説明 from

2230 (select (select rowid from grp g where g.gname=grp_s.gname)

2231 as rowid,

2232 gname,

2233 max(case key when 'gecos' then val end) as '説明',

2234 max(case key when 'tag' then val end) as 'tag',

2235 max(case key when 'mtime' then val end) as mtime from grp_s

2236 $where$* group by gname having tag='$tag' order by mtime desc);"

2237 ## err PersonalGroupList= `echo $sql`

2238 echo "<h2>$tn</h2>"

2239 echo '<table class="b listgroup">'

2240 sq -header -html $db "$sql" \

2241 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2242 echo '</table>'

2243 done

2244 echo '</div>'

2245 }

2246 iconhref() (

2247 # $1=icon-file, $2=Href $3=title $4...=anchor

2248 data=`percenthex "$1"`

2249 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2250 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2251 href=$2; title=$3; shift 3

2252 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2253 )

2254 iconhref2() (

2255 # $1=icon-file, $2=Href $3=title $4...=anchor

2256 src=$1

2257 href=$2; title=$3; shift 3

2258 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2259 )

2260 listentry() (

2261 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2262 # Referring variable $iamowner=$grp to attach owner-request links

2263 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2264 cond='' hiddens=''

2265 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2266 if [ -z "$offset" ]; then

2267 offset=`getpar start`; offset=${offset%%[!0-9]*}

2268 offset=$((offset-1))

2269 fi

2270 offset=$((offset + 0)) # change to numeric forcibly

2271 [ $offset -lt 0 ] && offset=0

2272 limit=$listentlimit

2273 dir=`getcachedir "$1"`

2274 if [ x"$1" = x"user" ]; then

2275 hrb="$myname?home"

2276 deficon=person-default.png

2277 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2278 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2279 gcs=gecos

2280 else # if group

2281 hrb="$myname?grp"

2282 deficon=group-default.png

2283 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2284 gcs=name

2285 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2286 if [ -n "$tagline" ]; then

2287 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2288 ## err tagconv=$tagconv

2289 fi

2290 fi

2291 if [ ! -d $dir ]; then

2292 mkdir -p $dir

2293 fi

2294 if [ ! -s $dir/$deficon ]; then

2295 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2296 fi

2297 if [ -n "$2" ]; then

2298 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2299 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2300 fi

2301 tag=`getpar tag` tag2=`getpar tag2`

2302 if [ x"$tag" = x"NULL" ]; then

2303 tag="" tag2=""

2304 fi

2305 if [ -n "$tag$tag2" ]; then

2306 tag=${tag:-$tag2}

2307 qtag=`sqlquote "$tag"`

2308 cond2="tag=$qtag"

2309 fi

2310 if [ -n "$cond1$cond2" ]; then

2311 cond="$cond1${cond2:+ AND $cond2}"

2312 cond="WHERE ${cond# AND }"

2313 fi

2314

2315 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2316 # grpは呼出し元の動的スコープ変数でよくないな...

2317 ##qgrp=`sqlquote $grp`

2318 getgrp="(select gname from grp where rowid=${rowid:--1})"

2319 sql="select a.rowid, a.$link,

2320 coalesce(b.$gcs, a.$nm) as nick,

2321 quote(a.$nm) as qname,

2322 (SELECT val FROM ${tbl}_s

2323 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2324 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2325 || case when a.$nm in (select gname from grp)

2326 then printf('(%d名)',

2327 (select count(user) from grp_mem where gname=a.$nm))

2328 else ' <'||a.$nm||'>'

2329 end

2330 as name,

2331 b.tag,

2332 case when a.$nm in (select user from grp_adm

2333 where gname=$getgrp) then '管理者'

2334 when '$user' in (select user from grp_adm where gname=a.$nm)

2335 then 'ADMIN'

2336 when '$user' in (select user from grp_mem where gname=a.$nm)

2337 then 'Member'

2338 when '$iamowner' = '' then ''

2339 else ',not='||a.rowid end as ownerlink,

2340 CASE '$entity'

2341 WHEN 'グループ'

2342 THEN coalesce(

2343 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2344 'open')

2345 ||

2346 CASE WHEN '$user'

2347 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2348 THEN ' ismember'

2349 ELSE ''

2350 END

2351 ELSE 'user'

2352 END regmode

2353 from $tbl a left join

2354 (select $nm as name,

2355 max(case key when 'gecos' then val end) as gecos,

2356 max(case key when 'tag' then val end) as tag,

2357 max(case key when 'mtime' then val end) as mtime,

2358 max(case key when 'wtime' then val end) as wtime,

2359 max(case key when 'login' then val end) as login

2360 from ${tbl}_s group by $nm)

2361 b on a.$nm=b.name $cond $3

2362 order by b.wtime desc, b.login desc,

2363 b.mtime desc, b.tag desc, a.rowid asc"

2364 # Give precedence to newer maintained groups (2016-09-24)

2365 # Note that mtime is stored only in grp_s.

2366 ## err LE:sql.1="$sql"

2367 total=`query "with x as ($sql) select count(*) from x;"`

2368 echo "${entity} 一覧" | html h2

2369 echo '<div class="listentry">' # List-entry div

2370 # Show owner/member filter button

2371 METHOD=GET

2372 hiddens="$hiddens

2373 `cgi_hidden kwd \"$kwd\"`

2374 `cgi_hidden stage \"$stage\"`"

2375 if [ x"$tbl" = x"grp" ]; then

2376 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2377 fh="<select name=\"tag\">$nl"

2378 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2379 for l in $args; do

2380 val=${l#*=} tname=${l%=*}

2381 if [ x"$val" = x"$tag" ]; then

2382 s=" selected"

2383 selectedtags="(種別[${tname}]のみ)"

2384 else

2385 s=""

2386 fi

2387 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2388 done

2389 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2390 cat<<-EOF

2391 <form action="$myname" method="$METHOD">

2392 </form>

2393 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2394 for="ismembtn">参加中以外隠す</label>

2395 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2396 for="isadmbtn">管理者参加以外隠す</label>

2397 EOF

2398 # limit=3

2399 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2400 fi

2401 if [ $total -gt $limit ]; then

2402 echo '<div>'

2403 METHOD=GET cgi_form $stage <<EOF

2404 $form

2405 <label>次の語を含む${entity}で検索:

2406 `cgi_text kwd "$kwd"`</label>

2407 $hiddens

2408 EOF

2409 echo '</div>'

2410 else

2411 echo $selectedtags | html p

2412 fi

2413 cat<<EOF

2414 <form action="$myname" method="$METHOD">

2415 <p>${total}件中の<input class="hidesub" type="text" name="start"

2416 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2417 <input type="submit" value="確定"></p>

2418 </form>

2419 EOF

2420 if [ $((offset+limit)) -lt $total ]; then

2421 nextbtn=$(

2422 cat<<EOF

2423 <div class="right clear"><form action="$myname" method="$METHOD">

2424 `cgi_submit 次の${limit}件`

2425 $hiddens

2426 `cgi_hidden offset $((offset + limit))`</form></div>

2427 EOF

2428 )

2429 fi

2430 if [ $offset -gt 0 ]; then

2431 prevbtn=$(

2432 cat<<EOF

2433 <form action="$myname" method="$METHOD">

2434 `cgi_submit 前の${limit}件`

2435 $hiddens

2436 `cgi_hidden offset $((offset - limit))`</form>

2437 EOF

2438 )

2439 fi

2440 pnbtn="$nextbtn$prevbtn"

2441 echo $pnbtn

2442

2443 ## err ListEntry: `echo "$sql"\;`

2444 # sq $db here??? 2016-11-28

2445 query "$sql limit $limit ${offset:+offset $offset};" \

2446 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2447 # err name=$name owner=$ownerp lnk=$lnk

2448 # err newlnk=$lnk regmode=$regmode

2449 icondir=$dir/$id

2450 # Pick up only last icon

2451 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2452 <p class=\"tag _$tag\">$tag</p>" \

2453 | _m4 $tagconv

2454 if [ -n "$NOSPEEDUP" ]; then

2455 files=`getvalbyid $tbl profimg $id $icondir`

2456 if [ -n "$files" ]; then

2457 icon=`echo "$files"|tail -1`

2458 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2459 else

2460 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2461 fi

2462 elif [ -n "$icon" -a -s "$icon" ]; then

2463 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2464 else

2465 cond="$nm=$qname"

2466 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2467 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2468 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2469 # err "img=[$img]"

2470 if [ -n "$img" ]; then

2471 echo "<a href=\"$hrb+$lnk\">"

2472 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2473 echo "</a>"

2474 else

2475 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2476 fi

2477 fi

2478 echo "<br>$name${ownerp:+<br>($ownerp)}"

2479 echo "</div>"

2480 done

2481 echo "</div>" # End of List-entry div

2482 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2483 )

2484 listmember() {

2485 listentry user "$@"

2486 }

2487 listgroup() {

2488 listentry group "$@"

2489 }

2490 hexteams() { # $1=gname, $2(optional)=user

2491 cond=${2:+" AND user='$2'"}

2492 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2493 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2494 }

2495 showgroup() { # $1=group-rowid

2496 if [ -z "$1" ]; then

2497 grid=`getpar grid`

2498 grid=${grid%%[!0-9]*}

2499 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2500 else

2501 grid=$1

2502 fi

2503 grp=`getgroupbyid $grid`

2504 qgrp=`sqlquote "$grp"`

2505 htmlgrp=`echo "$grp"|htmlescape`

2506 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2507 if isgroup "$grp"; then

2508 tf=$tmpd/title.$$

2509 sf=$tmpd/search.$$

2510 bodyclass=`query "SELECT val FROM grp_s

2511 WHERE gname=$qgrp AND key='regmode';"`

2512 if ismember "$user" "$grp"; then

2513 ismember="ismember"

2514 bodyclass="$bodyclass${bodyclass:+ }ismember"

2515 else

2516 ismember="" # bodyclass="group"

2517 fi

2518 bodyclass="$bodyclass grouphome"

2519 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2520 echo "グループ $htmlgrp" > $tf

2521

2522 showgroupsub $formdir/grp.def "$grid" | \

2523 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2524 -D_FORM_="syscmd(\`cat')" \

2525 -D_BODYCLASS_="$bodyclass" \

2526 -D_DUMPTABLE_="" \

2527 $htmlheader $sf $layout/form+dump.m4.html

2528 # $htmlheader $layout/form+dump.m4.html

2529 # $htmlheader is defined in grp()

2530 else # if $grp is removed at par2table

2531 listgroup

2532 fi

2533 }

2534 showgroupsub() {

2535 # $1=def-file $2=group-rowid

2536 # Using $ismember

2537 rowid=$2

2538 grp=`getgroupbyid $2`

2539 qgrp=`sqlquote "$grp"`

2540 td=`getcachedir grp/"$2"`

2541 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2542 if [ -z "$rowid" ]; then

2543 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2544 #grp=`sq $db "select gname from grp where rowid=$grp"`

2545 echo "showgroupsub: invalid argument($1 $2)" | html p

2546 return

2547 fi

2548 val=`getvalbyid grp profimg $rowid $tmpd`

2549 enticond="gname=$qgrp"

2550 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2551 if [ -n "$img" ]; then

2552 cat<<-EOF

2553 <p class="groupimg">

2554 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2555 EOF

2556 fi

2557 echo "<div class=\"noprofimg\">"

2558 viewtable $1 grp $rowid

2559 echo "</div>"

2560 if isgrpowner "$user" "$grp"; then

2561 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2562 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2563 iamowner=$rowid

2564 colmd=" mode"

2565 fi

2566 if [ -n "$ismember" ]; then

2567 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2568 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2569 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2570 cat<<-EOF

2571 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2572 href="?blog+$rowid">グループの新規話題作成</a>

2573 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2574 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2575 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2576 <div class="fold clear">

2577 `cgi_checkbox send yes id="send"`<label

2578 for="send">グループ全員にメッセージ送信</label>

2579 <div>

2580 `cgi_textarea message "" "cols=60"`

2581 `cgi_submit 送信`

2582 `cgi_reset リセット`

2583 </div>

2584 `cgi_hidden grp $rowid`

2585 </div></form>

2586 EOF

2587 fi

2588 # 加入ボタン + 加入者リスト

2589 if [ -n "$ismember" ]; then

2590 ismem='checked' state="(参加中)"

2591 else

2592 nomem='checked' state="(現在非加入)"

2593 fi

2594 # このグループでの加入アドレス

2595 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2596 and key='email';"`

2597 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2598 ## and key='email';"

2599 ##err email=$eml

2600 cat <<EOF

2601 <div class="fold clear">

2602 `cgi_checkbox reg yes id="reg"`<label

2603 for="reg">自身の加入状態を操作する</label>$state

2604 <div>

2605 EOF

2606 cgi_form grp <<EOF

2607 <p>このグループに</p>

2608 <table class="b">

2609 <tr><th>メンバーとして</th><td>

2610 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2611 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2612 <tr><th>参加する場合のメイルアドレス<br>

2613 <small>(メインのアドレスとは違うものにする場合に記入<br>

2614 同じでよい場合は空欄に)</small></th>

2615 <td>`cgi_text email $eml`</td></tr>

2616 </table>

2617 `cgi_hidden grp $rowid`

2618 EOF

2619 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2620 echo "moderated (承認加入の)グループなので実際に参加できるのは

2621 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2622 fi

2623 echo '</div></div>'

2624 echo '<h2>話題一覧</h2>'

2625 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2626 cat<<-EOF

2627 <form class="summary" action="$myname" title="$thelp">

2628 `cgi_hidden owner "$grp"`

2629 `cgi_hidden kwd "@week"`

2630 `cgi_hidden stage searchart`

2631 `cgi_submit "一週間のまとめ"`

2632 </form>

2633 EOF

2634 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2635 colstate="state:稼動状態:frozen=rowclass=凍結"

2636 DT_CHLD=article:blogid \

2637 DT_QOWNER="$qgrp" \

2638 DT_VIEW=replyblog dumptable html blog \

2639 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2640

2641 getgname="(select gname from grp where rowid=$rowid)"

2642 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2643 cm="?commission+$rowid"

2644 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2645 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2646 # team list

2647 hexteams=`hexteams "$grp"`

2648 if [ -n "$hexteams" ]; then

2649 echo "チーム一覧" | html h2

2650 echo '<div class="dumptable"><table class="b">'

2651 sq $db -html -header<<-EOF

2652 SELECT val TEAM,

2653 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2654 MEMBERS

2655 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2656 EOF

2657 echo '</table></div>'

2658 fi

2659 }

2660 grp_getbodyclass() {

2661 # Get css class name for document.

2662 # `moderated' for moderated groups

2663 # `ismember' for groups where user belongs

2664 # $1=GroupName (w/o quote)

2665 # $user=userNameCurrentlyLogin

2666 ## err grp_getbodyclass: 1="$1"

2667 qgrp=`sqlquote "$1"`

2668 query<<-EOF

2669 SELECT coalesce(

2670 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2671 'open')

2672 ||

2673 CASE WHEN '$user'

2674 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2675 THEN ' ismember'

2676 ELSE ''

2677 END;

2678 EOF

2679 }

2680 grpaction() { # $1=group-rowid

2681 err GRP_ACTION:IN

2682 grid=${1:-`getpar grp`}

2683 grp=`getgroupbyid "$grid"`

2684 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2685 if [ -z "$grp" ]; then

2686 echo "無効な指定です。" | html p; return

2687 fi

2688 if ! ismember $user "$grp"; then

2689 echo "加入者のみに許可された操作です。" | html p; return

2690 fi

2691 echo "グループ $grp 個別選択操作" \

2692 | _m4 -D_TITLE_="syscmd(\`cat')" \

2693 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2694 $layout/html.m4.html

2695

2696 isowner=""

2697 isgrpowner "$user" "$grp" && isowner="yes"

2698 usel=`getpar usel`

2699 if [ -n "$usel" ]; then

2700 uids=$(echo `echo $usel`|tr ' ' ',')

2701 ## err grpaction-1: grp=$grp, `echo $sql`

2702 text=`getpar text|tr -d '\r'`

2703

2704 rm=`getpar rm` cfm=`getpar confirm`

2705 ## err rm=$rm cfm=$cfm

2706 if [ x"$rm" = x"yes" ]; then

2707 if [ "$isowner" ]; then

2708 if [ x"$rm$cfm" = x"yesyes" ]; then

2709 # Eliminate

2710 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2711 for tbl in grp_mem grp_mem_s grp_mem_m; do

2712 sql="delete from $tbl $cond;"

2713 # echo "sql=$sql"

2714 query "$sql"

2715 err rmGRPuser "$sql"

2716 done

2717 num=`query "select count(*) from user where rowid in ($uids);"`

2718 #err num=$num

2719 if [ 0$num -gt 0 ]; then

2720 sql="select coalesce(b.val,a.name) from user a left join \

2721 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2722 # err `echo "$sql"`

2723 html pre<<EOF

2724 以下の${num}名のグループ $grp 登録を解除しました。

2725 `query "$sql"`

2726 EOF

2727 fi

2728 else

2729 echo "確認のチェックがないのでやめておきます。" | html p

2730 return

2731 fi

2732 else # not Group Owner

2733 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2734 return

2735 fi

2736 cat<<EOF

2737

2738 EOF

2739 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2740 if [ -z "$text" ]; then # if msg is empty

2741 echo "なにかメッセージを..." | html p

2742 return 0

2743 fi

2744 gecos=`gecos $user`

2745 safegc=`echo "$gecos" | tr -d '<>@'`

2746 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2747 fromad=`myemail4group "$grp"`

2748 ###mail_from="$safegc <$fromad>"

2749 mail_from="$safegc <$user>" # TEST: 2020/5/13

2750 test -n `getpar sender` &&

2751 export SENDER=$user # TEST: 2020/5/15

2752 replyto=$fromad

2753

2754 ## Start parse of attachment files

2755 if [ -n "`getpar email`" ]; then

2756 ar=`getpar supprcpt`

2757 if [ -n "$ar" ]; then

2758 for a in $ar; do

2759 if checkdomain "$a"; then

2760 supprcpt="$supprcpt $a"

2761 else

2762 err "SupprcptErr=[$a] by $user"

2763 removercpt="$removercpt $a"

2764 fi

2765 done

2766 fi

2767 subj=`getpar subject`

2768 afiles=""

2769 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2770 do

2771 f=$tmpd/$fn

2772 if [ -s $f ]; then

2773 afiles=$afiles"${afiles:+ }$f"

2774 fi

2775 done

2776 else

2777 preface=$(cat <<-EOF

2778 $url

2779 のグループ「$grp」のメンバーである $gecos さんから、

2780 あなた宛のメッセージです。

2781 ----------------------------------------------------------

2782 EOF

2783 )

2784 fi

2785 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2786 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2787 subj="${subj:-$gecos さんからのメッセージ}"

2788 REPLYTO=$replyto

2789 MAIL_FROM=$mail_from

2790 export REPLYTO SMAIL_TO MAIL_FROM

2791 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2792 for r in $rcpts; do

2793 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2794 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2795 else

2796 # Show sender and rcpts address for guest

2797 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2798 fi

2799 if [ -n "$afiles" ];then

2800 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2801 else

2802 smail "$r" "$subj"

2803 fi <<EOF

2804 ${preface:+$preface$nl}$text

2805 EOF

2806 done

2807 if [ $? = 0 ]; then

2808 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2809 sql="select coalesce(b.val, a.name) from

2810 (select name from user where rowid in ($uids)) a

2811 left join user_s b on a.name=b.name and b.key='gecos';"

2812 html pre<<EOF

2813 `query "$sql"`

2814 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2815 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2816 EOF

2817 err SendDone: `echo $sql`

2818 fi

2819 elif [ x"$rm" = x"commission" ]; then

2820 grp_reg_adm $grid $usel

2821 elif [ x"$rm" = x"addteam" ]; then

2822 team=`getpar team|sed "s/'/''/g"` # for single quotation

2823 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2824 if [ x"$team" != x"$newteam" ]; then

2825 echo "チーム名に使えない文字を除去しました" | html p

2826 team=$newteam

2827 fi

2828 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2829 cat<<-EOF | html p

2830 有効なチーム名を入力してください。

2831 カンマだけ、「なし」という名前は使えません。

2832 EOF

2833 echo "有効なチーム名を入力してください。" | html p

2834 else

2835 grp_add_team $grid "$team" $usel

2836 fi

2837 elif [ x"$rm" = x"rmteam" ]; then

2838 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2839 rmteam=`getpar rmteam|sed "s/'/''/g"`

2840 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2841 gname='$grp' AND user='$user' AND key='team'\

2842 AND val='$rmteam';\"`" ]; then

2843 grp_rm_team $grid "$rmteam" $usel

2844 else

2845 echo "所属していないチームの除去操作はできません。"|html p

2846 fi

2847 else

2848 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2849 fi

2850 fi

2851 fi

2852 # POST count summary

2853 from=`getpar from`; to=`getpar to`

2854 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2855 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2856 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2857 # New entry

2858 sql="WITH mems AS (

2859 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2860 ON gm.user=g.name

2861 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2862 ), target_article AS (

2863 SELECT id FROM article_s

2864 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2865 ), posts AS (

2866 SELECT author, count(author) post

2867 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2868 WHERE blogid IN (SELECT id FROM blog_s

2869 WHERE key='owner'

2870 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2871 AND key='text'

2872 GROUP BY author

2873 ), teams AS (

2874 SELECT user, group_concat(val, ', ') team

2875 FROM grp_mem_m

2876 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2877 AND key='team'

2878 GROUP BY user

2879 ), user_post AS (

2880 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2881 FROM mems m LEFT JOIN posts

2882 ON m.name=posts.author

2883 GROUP by m.rowid

2884 )

2885 SELECT

2886 CASE

2887 WHEN (SELECT user FROM grp_adm

2888 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2889 AND user=up.name) IS NOT NULL

2890 then 'k'

2891 ELSE ''

2892 END || rowid || ',' || gecos NAME,

2893 post POST, team _TEAM_

2894 FROM user_post up LEFT JOIN teams t

2895 ON up.name=t.user

2896 ORDER BY gecos;"

2897 ## err grpaction: "`echo \"$sql\"`"

2898 tf=$tmpd/title.$$

2899 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2900 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2901 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2902 for=\"cmadmin\">管理者委任</label>

2903 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2904 </p></div>"

2905 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2906 title=\"Shortcut: g${nl}Remove from the Group\"

2907 for=\"conf\">GRP登録解除</label>

2908 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2909 <p>この操作による通知は本人に行きません。

2910 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2911 </div>"

2912 # Get team list to which current user belongs into $hexteams

2913 allhexteams=$(hexteams "$grp")

2914 if [ -n "$isowner" ]; then

2915 myhexteams="$allhexteams" # admin can remove all teams' attr

2916 else

2917 myhexteams=$(hexteams "$grp" "$user")

2918 fi

2919 if [ -n "$myhexteams" ]; then

2920 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2921 title=\"Shortcut: s${nl}Strip a team tag from\"

2922 for=\"cmrmteam\">チーム属性除去</label>

2923 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2924 を除去します: `cgi_checkbox teamconfirm yes` 確認

2925 <p>この操作による通知は本人に行きません。

2926 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2927 </div>

2928 "

2929 fi

2930 stf=$tmpd/selteam.$$

2931 cgi_select_h selteam "5445414d" $allhexteams > $stf

2932 b1='<label> <input type="checkbox" name="usel" value="'

2933 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2934 br='<span id="reverse" title="Reverse Selection"></span>'

2935 #b2='"> <span>' b3='</span></label>'

2936 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2937 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2938 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2939 cgi_form grpaction<<EOF \

2940 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2941 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2942 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2943 | _m4 -D_TITLE_="spaste(\`$tf')" \

2944 -D_SUBTITLE_="チェック後操作ボタン" \

2945 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2946 $layout/form+dump.m4.html \

2947 | _m4 -D_TEAM_="spaste(\`$stf')"

2948 <p>下でチェックした人を対象として:</p>

2949 <div class="foldtabs">

2950 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2951 title="Shortcut: a${nl}Add a team tag to"

2952 for="cmteam">同じチーム属性を付与</label>

2953 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2954 `cgi_datalist_h teams $allhexteams`

2955 </div>

2956 ${rmteammsg}

2957 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2958 title="Shortcut: d${nl}DirectMail to"

2959 for="sendmsg" title="Direct Message">DM送信</label>

2960 <div>

2961 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2962 title="Using email format">email書式を使う</label>

2963 <div class="folded">

2964 <table>

2965 <tr><td>From: </td><td>$user</td></tr>

2966 <tr><td>このFrom:で送る</td>

2967 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2968 </td></tr>

2969 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2970 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2971 <tr><td>ファイル添付: </td>

2972 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2973 </table>

2974 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2975 </div>

2976 <div>本文:`cgi_textarea text "" cols=72`

2977 </div>

2978 </div>

2979 ${isowner:+$cmmsg$excmsg}

2980 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2981 </div>

2982 <h4>$grp 参加者一覧</h4>$fromtonote

2983 <table class="td2r thl">

2984 `sq $db -header -html "$sql"`

2985 </table>

2986 `cgi_hidden grp $grid`

2987 `cgi_hidden myuid $myuid id="myuid"`

2988 EOF

2989 }

2990 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2991 # Create TEMPORARY VIEW

2992 query<<EOF

2993 CREATE TEMPORARY VIEW writeusers AS

2994 SELECT DISTINCT author FROM article

2995 WHERE id in (

2996 select id from article where blogid=(select id from blog where rowid=$1)

2997 );

2998 CREATE TEMPORARY VIEW movablegroups AS

2999 SELECT g.rowid growid , g.gname

3000 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3001 ON grp.gname=gm.gname -- そのユーザが属している

3002 AND user='$user') g -- グループに絞る

3003 WHERE (SELECT author FROM writeusers

3004 EXCEPT

3005 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3006 IS NULL;

3007 $2

3008 EOF

3009 }

3010 sql4readableblogs() {

3011 # Create view of blogs that can be readable to $user

3012 # Blog is readable when:

3013 # 1: blog owner is an user

3014 # 2: else, 2.1: owner-group where the $user belongs

3015 # 2.2: else, owner-group is not moderated

3016 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3017 cat<<EOF ## | tee tmp/sql.out

3018 CREATE TEMPORARY VIEW readableblogs AS

3019 SELECT blog.rowid rid, id, author

3020 FROM blog

3021 NATURAL JOIN

3022 (SELECT id,

3023 max(CASE key WHEN 'owner' THEN val END) owner,

3024 max(CASE key WHEN 'mode' THEN val END) mode

3025 FROM blog_s GROUP by id) bs

3026 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3027 THEN 1 -- blog owner is an user, READABLE

3028 WHEN (SELECT val FROM grp_s

3029 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3030 AND

3031 (SELECT user FROM grp_mem

3032 WHERE gname=bs.owner AND user='$user') IS NULL

3033 THEN 0

3034 WHEN mode IN ('quiz', 'enquete')

3035 THEN 0 -- "quiz" mode blog cannot be searched

3036 ELSE 1

3037 END;

3038 EOF

3039 }

3040 editheading() { # $1=rowid-of-heading

3041 rowid=${1%%[!A-Z0-9a-z_]*}

3042 if [ -z "$rowid" ]; then

3043 echo "話題番号が未指定です。" | html p

3044 return

3045 fi

3046 owner=`getvalbyid blog owner $rowid`

3047 title=`getvalbyid blog title $rowid`

3048 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3049 | _m4 -D_TITLE_="修正" \

3050 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3051 -D_BLOGS_="" -D_DUMPTABLE_="" \

3052 -D_FORM_="syscmd(\`cat')" \

3053 $layout/html.m4.html $layout/form+dump.m4.html

3054 # Move to group

3055 if isuser "$owner"; then

3056 crview4article $rowid

3057 n=`query "SELECT count(*) FROM writeusers;"`

3058 ## err N=$n

3059 if [ $((n)) -gt 0 ]; then

3060 ## err ROWID=$rowid

3061 sql="SELECT growid || ':' || gname FROM movablegroups;"

3062 cat<<-EOF

3063 <div class="fold">

3064 `cgi_checkbox mv send id="mv"`<label

3065 for="mv">この話題をグループ所有に移動する</label>

3066 <div>

3067 <form action="?mvart" method="POST" enctype="multipart/form-data">

3068 移動先グループ:

3069 <select name="mv2grp">

3070 EOF

3071 query ".mode html"

3072 query<<-EOF |

3073 $sql

3074 .mode list

3075 EOF

3076 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3077 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3078 cat<<-EOF

3079 </select>

3080 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3081 そのグループに加入しているものに限られます)</p>

3082 <p>`cgi_checkbox cfm yes`<label>確認

3083 (この操作は元に戻すことができません)</label></p>

3084 `cgi_hidden blogrowid $rowid`

3085 `cgi_submit 移動`

3086 `cgi_reset Reset`

3087 </form>

3088 </div>

3089 </div>

3090 EOF

3091 fi

3092 # end of isuser "$owner"

3093 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3094 [ -n "$hexteams" ];}; then

3095 none="`echo なし|hexize`"

3096 cat<<-EOF

3097 <div class="fold">

3098 `cgi_checkbox mv2team send id="mv2team"`<label

3099 for="mv2team">この話題を以下のチームのものにする</label>

3100 <div><p>現在の所属チーム設定:

3101 `query "SELECT

3102 coalesce((SELECT val FROM blog_s

3103 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3104 AND key='team'),

3105 ':なし');"`</p>

3106 <form action="?mvart" method="POST" enctype="multipart/form-data">

3107 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3108 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3109 `cgi_hidden blogrowid $rowid`<br>

3110 `cgi_submit 移動`

3111 `cgi_reset Reset`

3112 </form></div></div>

3113 EOF

3114 fi

3115 }

3116 mvart() { # move diary to some group or team

3117 # or move blog of group to team which belong to the group

3118 blogrowid=`getpar blogrowid`

3119 cfm=`getpar cfm`

3120 ##### echo move blog:$blogrowid to $mv2grp | html p

3121 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3122 . ./s4-blog.sh

3123 if [ -z "$blogrowid" ]; then

3124 echo "無効な指定です(mvart)。" | html p

3125 return

3126 elif [ x"$cfm" != x"yes" ]; then

3127 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3128 elif { mv2grp=`getpar mv2grp`

3129 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3130 [ -n "$mv2grp" ]; }; then

3131 crview4article $blogrowid

3132 ########## TRANSACTION BEGIN

3133 query "BEGIN;"

3134 n=`query "SELECT count(*) FROM writeusers;"`

3135 ## err Nwriteuser=$n

3136 if [ $((n)) -gt 0 ]; then

3137 query<<-EOF

3138 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3139 WHERE key='owner'

3140 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3141 AND $mv2grp IN (SELECT growid FROM movablegroups);

3142 EOF

3143 fi

3144 query "END;"

3145 ########## TRANSACTION END

3146 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3147 [ -n "$mv2team" ];}; then

3148 # blog owner can move it to ANY team

3149 case "$mv2team" in

3150 'なし')

3151 cat<<-EOF

3152 DELETE FROM blog_s

3153 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3154 AND key='team';

3155 EOF

3156 ;;

3157 "") ;;

3158 *)cat<<-EOF

3159 BEGIN;

3160 REPLACE INTO blog_s(id, key, val)

3161 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3162 'team', '$mv2team');

3163 REPLACE INTO blog_s(id, key, val)

3164 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3165 'notify', 'all'); -- Change notify to all

3166 END;

3167 EOF

3168 esac | query

3169 fi

3170 blog_reply $blogrowid

3171 echo yes | html p

3172 }

3173 editart() { # $1=article-rowid $2=blogrowid

3174 rowid=${1%%[!A-Z0-9a-z_]*}

3175 blogrowid=${2%%[!A-Z0-9a-z_]*}

3176 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3177 echo "表示する記事番号が未指定です。" | html p

3178 return

3179 fi

3180 owner=`getvalbyid blog owner $blogrowid`

3181 title=`getvalbyid blog title $blogrowid`

3182 author=`getvalbyid article author $rowid`

3183 ## err EDITart: owner=$owner, author=$author

3184 if isgrpowner "$user" "$owner"; then

3185 : EDIT OK

3186 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3187 echo "本人か所有者しか編集できません." | html p

3188 return

3189 fi

3190 aid=`query "select id from article where rowid=$rowid;"`

3191 tmpout=$tmpd/editart.$$.out

3192 GF_ACTION="?replyblog+$blogrowid#$aid" \

3193 edittable $formdir/article.def article $rowid \

3194 > $tmpout

3195 rm -f /tmp/editart.out

3196 # Cannot use pipelining to m4 with genform() because of stdin stack

3197 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3198 -D_FORM_="syscmd(cat $tmpout)" \

3199 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3200 -D_BLOGS_= -D_DUMPTABLE_= \

3201 $layout/html.m4.html $layout/form+dump.m4.html

3202 }

3203 send2mem() {

3204 rowid=`getpar grp`

3205 rowid=${rowid%%[!0-9]*} # Cleaning

3206 if [ -z "$rowid" ]; then

3207 echo "グループが未指定です。" | html p

3208 return

3209 fi

3210 message=`getpar message`

3211 if [ -z "$message" ]; then

3212 echo "文章を入れてください。" | html p

3213 return

3214 fi

3215 grp=`getgroupbyid $rowid`

3216 members=`collectemail $grp`

3217 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3218 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3219 mailfrom="`gecos "$user"` <$mailfrom>"

3220 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3221 msg=$(cat<<-EOF

3222 $urlbase?grp+$rowid

3223 グループ $grp に所属する

3224 `gecos $user` さんよりメッセージ:

3225

3226 $message

3227 EOF

3228 )

3229 # smail rcpt subj (file)

3230 for m in $members; do

3231 echo "$msg" |

3232 MAIL_FROM=$mailfrom \

3233 SENDER=$noreply \

3234 REPLYTO=$mailfrom \

3235 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3236 smail "$m" "$sj"

3237 done

3238 cat<<EOF

3239 <p>以下のユーザに送信しました。</p>

3240 <pre>

3241 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3242 </pre>

3243 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3244 EOF

3245 }

3246 joingrpadmit() {

3247 # $1=yes/no $2=session-key

3248 if [ -z "$2" ]; then

3249 echo "bye bye" | html p; return

3250 fi

3251 t_usr=`session=$2 getpar user`

3252 t_grp=`session=$2 getpar group`

3253 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3254 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3255 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3256 echo "無効な加入依頼です。" | html p

3257 echo "有効期限が切れたか、

3258 他の管理者がいる場合は処理済みの可能性があります。" | html p

3259 return

3260 fi

3261 if ! isgrpowner "$user" "$t_grp"; then

3262 echo "グループ管理者のみの機能です。" | html p; return

3263 fi

3264 case $1 in

3265 yes) joingrp "$t_grp" "$t_usr" yes ;;

3266 no) joingrp "$t_grp" "$t_usr" no ;;

3267 *)

3268 echo "無効な指定です($1)。" | html p

3269 return ;;

3270 esac

3271 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3272 rcpts="`getgroupadminmails $t_grp` $user"

3273 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3274 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3275 に

3276 $t_usr

3277 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3278 しました。"

3279 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3280 query "delete from session where id='$2';"

3281 echo "$body" | html p

3282 }

3283

3284 joingrprequest() {

3285 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3286 jss="joingrp-`date +%s`-`genrandom 12`"

3287 addsession $jss +${memoplimitdays}days

3288 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3289 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3290 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3291 $url

3292 $user さんから

3293 グループ $1

3294 に加入依頼がありました。

3295

3296 承認する:

3297 $urlbase?joingrpadmit+yes+$jss

3298

3299 白紙に戻す:

3300 $urlbase?joingrpadmit+no+$jss

3301 EOF

3302 echo "管理者に加入依頼を出しました。

3303 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3304 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3305 }

3306 joingrp() {

3307 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3308 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3309 if isgrpowner "$user" "$1"; then

3310 isowner="yes"

3311 elif [ -n "$5" ]; then

3312 isowner="yes"

3313 else

3314 isowner=""

3315 fi

3316 ## err jg:isgrpowner: isowner="$isowner"

3317 if [ -n "$isowner" ]; then

3318 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3319 elif [ x"$2" != x"$user" ]; then # if user is not login user

3320 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3321 return

3322 elif [ x"$3" = x"no" ]; then

3323 : # Do not pursue those who leave

3324 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3325 : # Member can change own email address for the joining moderated group

3326 else # adding user is $user itself

3327 case `getgroupattr $1 regmode` in

3328 moderated)

3329 joingrprequest "$@" # Request only

3330 return

3331 ;;

3332 *)

3333 ;;

3334 esac

3335 fi

3336 qgname=`sqlquote "$1"`

3337 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3338 cond="where gname=$qgname and user='$2'"

3339 if [ x"$3" = x"yes" ]; then

3340 query "replace into grp_mem values($qgname, '$2');"

3341 # Notify joingrp to admin

3342 action="に加入しました。"

3343 if [ -n "$4" ]; then

3344 if msg=`emaildomaincheck "$4"`; then

3345 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3346 'string', '$4', NULL);"

3347 else

3348 echo $msg

3349 fi

3350 else

3351 query "delete from grp_mem_s $cond and key='email';"

3352 fi

3353 if [ -n "$5" ]; then # as ADMIN

3354 # Coming here means newly created group

3355 sql="select case\

3356 when (select count(*) from grp_mem where gname=$qgname)=1\

3357 then (select user from grp_mem\

3358 where gname=$qgname and user='$user')\

3359 else '' end; "

3360 err NewGrpChk: $sql

3361 if [ -n "`query \"$sql\"`" ]; then

3362 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3363 query "replace into grp_adm values($qgname, '$user');"

3364 fi

3365 fi

3366 else

3367 query "begin;

3368 delete from grp_mem $cond;

3369 delete from grp_mem_s $cond;

3370 delete from grp_mem_m $cond;

3371 delete from grp_adm $cond;

3372 delete from grp_adm_s $cond;

3373 delete from grp_adm_m $cond;

3374 end;"

3375 action="から脱退しました。"

3376 fi

3377 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3378 $url?grp+$grid

3379 $user (`gecos $user`)さんが

3380 グループ $1

3381 $action

3382 EOF

3383 }

3384 grp_add_team() (

3385 # $1=grp-rowid $2=team $3...=user-rowid(s)

3386 grp=`getgroupbyid $1`

3387 team=$2; shift; shift

3388 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3389 { echo "BEGIN;"

3390 for user; do

3391 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3392 '$grp',\

3393 (SELECT name FROM user WHERE rowid=$user),\

3394 'team', 'string', '$team');"

3395 done

3396 echo "END;"

3397 } | query

3398 )

3399 grp_rm_team() (

3400 # $1=grp-rowid $2=team $3...=user-rowid(s)

3401 grid=$1

3402 qgrp=$(sqlquote "`getgroupbyid $grid`")

3403 team=$2; shift; shift

3404 [ -z "$grid" -o -z "$team" ] && return

3405 { echo "BEGIN;"

3406 for user; do

3407 echo "DELETE FROM grp_mem_m\

3408 WHERE gname=$qgrp \

3409 AND user=(SELECT name FROM user WHERE rowid=$user)\

3410 AND key='team' AND val='$team';"

3411 done

3412 cat<<-EOF

3413 DELETE FROM blog_s

3414 WHERE rowid=(

3415 SELECT rowid

3416 FROM blog_s a

3417 WHERE key='team'

3418 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3419 AND NOT EXISTS (SELECT * FROM grp_mem_m

3420 WHERE key='team' AND val=a.val -- a.val=team

3421 AND gname = (SELECT val FROM blog_s b

3422 WHERE a.id=b.id AND key='owner')

3423 ));

3424 EOF

3425

3426 echo "END;"

3427 } | query

3428 )

3429 grp_reg_adm() {

3430 # $1=grp-rowid $2...=user-rowid

3431 grid=$1

3432 grp=`getgroupbyid "$1"`

3433 if [ -z "$grp" ]; then

3434 echo "無効なグループIDです" | html p; return

3435 fi

3436 if ! isgrpowner "$user" "$grp"; then

3437 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3438 fi

3439 shift

3440 for urid; do

3441 newadm=`query "select name from user where rowid=$urid;"`

3442 if [ -z "$newadm" ]; then

3443 echo "指定ユーザIDがおかしいようです。" | html p; return

3444 fi

3445 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3446 err ismember $newadm $grp

3447 if ismember $newadm "$grp"; then

3448 # OK, go ahead

3449 getgname="(select gname from grp where rowid=$grid)"

3450 query "replace into grp_adm values($getgname, '$newadm');"

3451 # confirm insertion

3452 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3453 if [ -n "`query \"$sql;\"`" ]; then

3454 echo "追加完了: $newadm" | html p

3455 else

3456 echo "追加失敗($1 $urid)" | html p

3457 fi

3458 fi

3459 showgroup $grid

3460 done

3461 }

3462 dt_rowhack() {

3463 # From: <TR>

3464 # ....

3465 # <TD>rowclass=foo</TD>

3466 # </TR>

3467 # To: <TR class="foo">....<TD>foo</TD></TR>

3468 sed -e '

3469 /^<TR>/ {

3470 :loop

3471 s/\n//

3472 N

3473 /<\/TR>/ {

3474 s/\n//

3475 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3476 n

3477 }

3478 $q

3479 b loop

3480 }'

3481 }

3482 dumptable() {

3483 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3484 # textのフィールドだけ全てダンプにしたほうがいいか

3485 # $DT_VIEW sets link

3486 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3487 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3488 VIEW=${DT_VIEW-replyblog}

3489 if [ -n "$VIEW" ]; then

3490 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3491 fi

3492 sqlfile=$tmpd/dump.sql

3493 : > $sqlfile # ensure to be empty

3494 printf '.mode html\n.header 1\n' > $sqlfile

3495 # $DT_CHLD=ChildTable:BindColumn

3496 if [ -n "$DT_CHLD" ]; then

3497 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3498 cat<<-EOF >> $sqlfile

3499 -- presql

3500 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3501 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3502 EOF

3503 # Speed up counting of new articles

3504 cat<<-EOF >> $sqlfile

3505 -- presql2

3506 DROP TABLE IF EXISTS _counts;

3507 CREATE TEMPORARY TABLE _counts AS

3508 SELECT $_i, count($_i) cnt

3509 FROM $_t GROUP BY $_i;

3510 /* Prepare NEW count table */

3511 CREATE TEMPORARY TABLE _target AS

3512 SELECT b.rowid trowid, b.id

3513 FROM "$2" b JOIN "$2_s" s

3514 ON b.id=s.id AND s.key='owner'

3515 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3516

3517 DROP TABLE IF EXISTS _children;

3518 CREATE TEMPORARY TABLE _children AS

3519 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3520 FROM (SELECT t.trowid, t.id $_i, a.id

3521 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3522 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3523

3524 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3525 DROP TABLE IF EXISTS _news;

3526 DROP VIEW IF EXISTS _news;

3527

3528 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3529 -- INSERT INTO _news

3530 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3531 CREATE TEMPORARY VIEW _news AS

3532 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3533 FROM (SELECT DISTINCT id FROM _target)

3534 a LEFT JOIN

3535 (SELECT $_i, count(ctime) newcnt

3536 FROM _children x

3537 WHERE ctime > coalesce((SELECT time from myacclog

3538 WHERE tblrowid=x.trowid),

3539 '1970-01-01')

3540 GROUP BY $_i) b

3541 ON a.id=b.$_i;

3542 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3543 EOF

3544 # REMOVE next line until 2019/5/1

3545 cntall="(select count($_i) from $_t where $_i=a.id)"

3546 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3547 # REMOVE next assignment until 2019/5/1

3548 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3549 and id in (select id from $_t where $_i=a.id) \

3550 and val > coalesce((select time from myacclog where \

3551 tblrowid=a.rowid),\

3552 '1970-01-01'))"

3553 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3554 cnt="$cntnew as '新着', $cntall as '総数',"

3555 dt_class=" td2r td3r dumpblogs"

3556 fi

3557 # Construct join expression

3558 eav="" scols=""

3559 pk=`gettblpkey $2`

3560 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3561 substr=${substr:-%s}

3562 for col in ${3:-`gettbl_s_cols $2`}; do

3563 valvar=val

3564 case $col in

3565 gecos) scols="$scols${scols:+, }${col#}"

3566 continue ;; # built-in column name

3567 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3568 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3569 case "$as" in

3570 *:*=*) cnd=${as#*:}

3571 h=${cnd%%=*} v=${cnd#*=}

3572 h=`sqlquotestr "$h"`

3573 v=`sqlquotestr "$v"`

3574 valvar="CASE val WHEN $h THEN $v END"

3575 as=${as%%:*} ;;

3576 esac

3577 ;;

3578 *) as=${col} ;;

3579 esac

3580 ss=`printf "$substr" "$valvar"`

3581 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3582 scols="$scols${scols:+, }b.$as"

3583 done

3584 #case author when '$user' then a.rowid else '---' end as ID,

3585 if [ -n "$DT_SQL" ]; then

3586 echo "$DT_SQL"

3587 else

3588 cat<<-EOF

3589 SELECT a.rowid as LINK, $cnt $scols

3590 FROM $2 a LEFT JOIN

3591 (SELECT $pk,$eav,

3592 max(CASE key

3593 WHEN 'owner'

3594 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3595 as gecos

3596 FROM ${2}_s c GROUP BY $pk)

3597 b ON a.$pk=b.$pk $4;

3598 EOF

3599 fi >> $sqlfile

3600 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3601 sqlog<<-EOF

3602 *** SQL-file: $sqlfile ***

3603 `cat $sqlfile`

3604 EOF

3605 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3606 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3607 cat $sqlfile >> tmp/sql

3608 # query ".read $sqlfile" > $tmpd/foo

3609 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3610 cp $tmpd/foo tmp/

3611 err "----- `gdate +%FT%T.%3N` ------------555555"

3612 ## $ddd LINE exists at the end of this function

3613 fi

3614 printf '.mode list\n.header 0\n' >> $sqlfile

3615 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3616 <div>

3617 <div class="dumptable">

3618 <table class="b$dt_class">

3619 `query ".read $sqlfile"`

3620 </table>

3621 </div>

3622 </div>

3623 EOF

3624 ### `query ".read $sqlfile"`

3625 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3626

3627 ## REMOVE THIS!

3628 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3629 }

3630

3631 par2table() (

3632 # copy current parameters of par into destination table

3633 # $1=definition-file

3634 # Using $user and $session

3635 # Return value:

3636 # 0: Stored successfully

3637 # 1: Insufficient fillings

3638 # 2: No permission to modify the record

3639 # 3: Invalid rowid

3640 # 4: SUCCESS to delete

3641 # 5: Stop deletion for lack of confirm check

3642 # 6: Password length too short

3643 # 7: Password mismatch

3644 # 8: Old password incorrect

3645 # 9: Duplicated post

3646 rowid=`getpar rowid`

3647 if [ ! -e $1 ]; then

3648 echo "テーブル定義ファイルが見付かりません" | html p

3649 exit 1

3650 fi

3651 tbl=${1%.def}

3652 tbl=${tbl##*/}

3653 if [ -n "$rowid" ]; then # Modify existing entry

3654 if [ x"$tbl" = x"user" ]; then

3655 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3656 elif [ x"$tbl" = x"grp" ]; then

3657 sql="select gname from $tbl where rowid=$rowid;"

3658 ##err p2t:grp:q $sql

3659 isgrpowner "$user" "`query $sql`" && rowowner=$user

3660 elif [ x"$tbl" = x"blog" ]; then

3661 # Check if owner in blog_s

3662 blogowner=`getvalbyid blog owner "$rowid"`

3663 if isgrpowner "$user" "$blogowner"; then

3664 rowowner=$user

3665 else

3666 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3667 fi

3668 else

3669 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3670 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3671 fi

3672 ### err rowowner=$rowowner

3673 if [ x"$user" != x"$rowowner" ]; then

3674 echo "他人のレコードはいじれないの" | html p

3675 return 2

3676 elif [ -z "$rowowner" ]; then

3677 echo "指定したレコードはないみたい" | html p

3678 return 3

3679 fi

3680 rm=`getpar rm` cfm=`getpar confirm`

3681 # Editing existent entry

3682 if [ x"$rm" = x"yes" ]; then

3683 if [ x"$rm$cfm" = x"yesyes" ]; then

3684 query "delete from $tbl where rowid=$rowid;"

3685 return 4

3686 else

3687 echo "消去確認のチェックがないので消さなかったの..." | html p

3688 return 5

3689 fi

3690 fi

3691 fi

3692

3693 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3694 if [ -n "$rowid" ]; then

3695 # Update of existing record

3696 for col in `gettblcols $tbl`; do

3697 val=`getparquote $col`

3698 [ -z "$val" ] && continue

3699 ## err query "update $tbl set $col=$val where rowid=$rowid"

3700 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3701 sql="update $tbl set $col=$val where rowid=$rowid;"

3702 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3703 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3704 ## User name cannot be changed with interface provided with this

3705 ## script. But we offer the trigger to change owner user

3706 ## of blog_s table.

3707 #err "select quote($col) from $tbl where rowid=$rowid;"

3708 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3709 cat<<-EOF | query

3710 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3711 -- should use EXCLUSIVE transaction outside of this.

3712 SAVEPOINT par2table;

3713 $sql

3714 update blog_s set val=$val

3715 where key='owner' and val=$old;

3716 RELEASE SAVEPOINT par2table;

3717 EOF

3718 ## XX: DIRTY Hack Ends here

3719 ## We should keep blog's owner as a single column which has

3720 ## foreign key constraint with primary key of grp/user.

3721 else

3722 query "$sql"

3723 fi

3724 done

3725 # Then, set up $pval for further insertion of tbl_s and tbl_m

3726 for col in `gettblpkey $tbl`; do

3727 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3728 pval="$pval${pval:+, }\"$val\""

3729 done

3730 else

3731 # New entry

3732 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3733 # Avoid empty repost of article.

3734 if [ x"$tbl" = x"article" ]; then

3735 # If rowid is empty and ID exists in article-table, that is REPOST!

3736 aid=`getpar id`

3737 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3738 if [ -n "$xaid" ]; then

3739 # REPOST of article

3740 html p <<-EOF

3741 書き込み直後のリロードなので上書きを回避します。

3742 最新記事は末尾の「再読み込み」ボタンから見てください。

3743 EOF

3744 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3745 return 9 # STOP Duplicated posting

3746 fi

3747 fi

3748 # Generate values() for primary keys

3749 for col in `gettblpkey $tbl`; do

3750 # Genuine primary keys for _m and _s

3751 val=`getvalquote $tbl $col`

3752 [ -z "$val" ] && continue

3753 pval="$pval${pval:+, }$val"

3754 done

3755 ##err pval=$pval

3756 for col in `gettblfkey $tbl`; do

3757 # args for values() to insertion into master table

3758 val=`getvalquote $tbl $col`

3759 [ -z "$val" ] && continue

3760 formaster=$formaster"${formaster:+, }$val"

3761 done

3762 formaster="$pval${formaster:+, }$formaster"

3763 ## err formaster=$formaster

3764 if [ -z "$formaster" ]; then

3765 echo "項目を全て埋めてください" | html pre

3766 return 1

3767 fi

3768 ## err "replace into $tbl values($formaster);"

3769 query "replace into $tbl values($formaster);"

3770 ## Insertion to master table, done

3771 fi

3772

3773 transaction=$tmpd/sqlfile.sql; touch $transaction

3774 for kt in s m; do

3775 tb2=${tbl}_$kt

3776 for col in `gettbl_${kt}_cols $tbl`; do

3777 ptype=`getpartype $col "limit 1"`

3778

3779 # First, check update of existing entries in _m

3780 if [ $kt = m ]; then

3781 # sessID|address.1.22|string|Somewhere-x.y.z

3782 sql=""

3783 ##err dots from query "select var from par where var like '$col.%';"

3784 for v in `query "select var from par where var like '$col.%';"`; do

3785 # v=address.1.22

3786 st_rowid=${v##*.}

3787 origcol=${v%%.*} # original column derived from

3788 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3789 ##case `getpartype $v` in

3790 ## err CASE `gettbl_coltype $tbl/$origcol` in

3791 ## err edit flag = `getpar action.$v`

3792 case `getpar action.$v` in

3793 rm)

3794 if [ x`getpar confirm.$v` = x"yes" ]; then

3795 newsql="delete from $tb2"

3796 else

3797 echo "削除確認未チェック" | html p

3798 fi ;;

3799 edit)

3800 case `gettbl_coltype $tbl/$origcol` in

3801 image|document|binary)

3802 file=$tmpd/`getparfilename $v`

3803 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3804 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3805 continue

3806 fi

3807 ## err type=file=$file

3808 [ -z "$file" ] && continue

3809 bn=`sqlquotestr "${file##*/}"`

3810 bin="X'"$(hexize "$file")"'"

3811 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3812 type=\"file:$ct\"

3813 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3814 cachedir=`getcachedir "$tbl/$rowid"`

3815 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3816 rm -rf $cachedir

3817 ;;

3818 *)

3819 newsql="update $tb2 set val=(select val from par where var \

3820 like '$col.%.$st_rowid')"

3821 ;;

3822 esac

3823 ;;

3824 *) # maybe "keep", do not modify value

3825 continue

3826 ;;

3827 esac

3828 # err newsql=$newsql

3829 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3830 done

3831

3832 if [ x"$bin" = x"NULL" ]; then

3833 ## err repl:normal sql=`echo $sql`

3834 if [ -n "$transaction" ]; then

3835 cat<<-EOF >> $transaction

3836 $sql

3837 DELETE FROM $tb2 WHERE type='string' AND val='';

3838 EOF

3839 else

3840 query "$sql

3841 delete from $tb2 where type='string' and val='';"

3842 ## err repl:normal done

3843 fi

3844 else

3845 # Binary update line is TOO LONG to pipelining

3846 sqlfile="$tmpd/sqlf.$$"

3847 if [ -n "$transaction" ]; then

3848 echo "$sql" >> $transaction

3849 else

3850 echo "$sql" > $sqlfile

3851 query ".read $sqlfile"

3852 fi

3853 fi

3854 # Rest of kt==m: set multiple mode

3855 nr=`getparcount $col`

3856 else

3857 nr=1 # for kt==s, number of records is 1

3858 fi

3859

3860 i=0

3861 while [ $i -lt $nr ]; do

3862 limit="limit 1 offset $i"

3863 i=$((i+1)) # increase beforehand against continue

3864 val=`getvalquote $tbl $col "$limit"`

3865 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3866 ## err $col=$val

3867 bin=NULL

3868 ## err partype$col=`getpartype $col "$limit"`

3869 case $ptype in

3870 file) file=$tmpd/`getparfilename $col "$limit"`

3871 ## err parfile-$col=$file

3872 [ -z "$file" ] && continue

3873 bin="X'"$(hexize "$file")"'"

3874 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3875 type=\"file:$ct\" ;;

3876 "*"*) continue ;; # foreign table

3877 *) type=\"string\" ;;

3878 esac

3879 case `gettbl_coltype $tbl/$col` in

3880 password) # special care for password

3881 # name={password,pswd1,pswd2}

3882 p1=`getpar pswd1 "$limit"`

3883 if [ -z "$p1" ]; then

3884 continue # SKIP password setting, if p1 is empty

3885 else

3886 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3887 ## err pswd=$pswd

3888 if pwcheck "$pswd"; then

3889 if [ x"$p1" = x"$p2" ]; then

3890 case "$p1" in

3891 ??????????*) ;;

3892 *) echo "パスワードは10字以上にしてください。" | html p

3893 return 6;;

3894 esac

3895 val="\"`echo $p1|mypwhash`\""

3896 else

3897 echo "2つの新パスワード不一致" | html p

3898 return 7

3899 fi

3900 else

3901 echo "旧パスワード違います" | html p

3902 return 8

3903 fi

3904 fi

3905 ;;

3906 esac

3907 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3908 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3909 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3910 if [ x"$bin" = x"NULL" ]; then

3911 ## err Normal-query: `echo $sql`

3912 if [ -n "$transaction" ]; then

3913 echo "$sql" >> $transaction

3914 else

3915 query "$sql"

3916 fi

3917 else

3918 sqlfile="$tmpd/query.$$"

3919 ## err sqlfile=`ls -lF $sqlfile`

3920 if [ -n "$transaction" ]; then

3921 echo "$sql" >> $transaction

3922 else

3923 echo "$sql" > $sqlfile

3924 query ".read $sqlfile"

3925 fi

3926 fi

3927 ## err p2t done

3928 done

3929 done

3930 done

3931 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3932 -- We cannot use transaction here, because groupupdate may use it.

3933 SAVEPOINT pa2table_insert;

3934 .read $transaction

3935 RELEASE SAVEPOINT pa2table_insert;

3936 EOF

3937 return $?

3938 ##err donee

3939 )

3940 genform() {

3941 # $1 = form definition file

3942 # $2, $3 (optional)= table name and ROWID

3943 # If $GF_VIEWONLY set and nonNull, output values without form

3944 # If $GF_ARGS set, use it as content-strings in the form

3945 # If $GF_OWNER set, use it as value of name="owner"

3946 # If $GF_STAGE set, use it as value of name="stage"

3947 forms="" hiddens="" rowid=$3

3948 if [ ! -e "$1" ]; then

3949 echo "そのようなデータベースはないようです($2)。" | html p

3950 return

3951 elif [ -n "$2" ]; then

3952 rec=`query "select * from $2 where rowid='$rowid';"`

3953 if [ -z "$rec" ]; then

3954 pk=`gettblpkey $2`

3955 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3956 rec=`query "select rowid from $2 where $pk='$rowid';"`

3957 rowid=$rec

3958 rec=$3

3959 fi

3960 if [ -z "$rec" ]; then

3961 echo "そんなレコードはないみたいね..." | html p

3962 return

3963 fi

3964 fi

3965 if [ -z "$GF_VIEWONLY" ]; then

3966 rm='<input id="rm" name="rm" type="checkbox"

3967 value="yes"><label for="rm">このエントリの削除</label>

3968 <span>ほんとうに消しますよ(確認)!

3969 <input name="confirm" type=checkbox value="yes">はい</span>'

3970 fi

3971 # Image Cache dir

3972 ## err genform: getcache=$2/$rowid

3973 td=`getcachedir "$2/$rowid"`

3974 while IFS=: read prompt name keytype type args; do

3975 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3976 sp="${args:+ }"

3977 form="" val=""

3978 if [ -n "$rowid" ]; then

3979 # err genform2a: Seeking for "$2.$name, type=$type"

3980 rawval=`getvalbyid $2 $name $rowid $td`

3981 val=`echo "$rawval"|htmlescape`

3982 ## err genform3a: getvalbyid $2 $name $rowid $td

3983 ## err genform3b: val="[$val]" type="$type"

3984 fi

3985 if [ -n "$GF_VIEWONLY" ]; then

3986 is_hidden "$2" "$name" && continue

3987 fi

3988 case "$type" in

3989 text*)

3990 cgiform=cgi_multi_$type

3991 if [ -s $td/$name.count -a -n "$val" ]; then

3992 form=`$cgiform $name $td`

3993 val=$(echo "$val"|

3994 while read fn; do

3995 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3996 </td></tr>$nl"

3997 done)

3998 val="<table>$nl$val$nl</table>"

3999 else

4000 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4001 form=`cgi_$type $name "$rawval" "$args"`

4002 fi

4003 ;;

4004 [Rr][Aa][Dd][Ii][Oo])

4005 fh="<label><input type=\"radio\" name=\"$name\""

4006 form="`echo $args|sed -e \

4007 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4008 ;;

4009 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4010 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4011 ;;

4012 [Ss][Ee][Ll][Ee][Cc][Tt])

4013 fh="<select name=\"$name\">$nl"

4014 form=$(for l in $args; do

4015 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4016 done)

4017 if [ -n "$val" ]; then

4018 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4019 fi

4020 form="$fh$form</select>"

4021 ;;

4022 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4023 if [ -s $td/$name.count ]; then

4024 form=`cgi_multi_file $name $td "$args"`

4025 if [ -n "$val" ]; then

4026 hrfb="$myname?showattc+$2_m"

4027 val=$(echo "$rawval" \

4028 | while read fn; do

4029 data=`percenthex "$td/$fn"`

4030 #ct=`cat $td/$fn.content-type`

4031 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4032 ri=`cat "$td/$fn.rowid"`

4033 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4034 #imgsrc="<img src=\"data:$ct,$data\">"

4035 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4036 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4037 done)

4038 fi

4039 else

4040 form="<input type=\"file\" name=\"$name\" $args>"

4041 if [ -n "$val" ]; then

4042 imgs=$(echo "$rawval"\

4043 |while read fn;do

4044 data=`percenthex "$td/$fn"`

4045 echo "<img src=\"data:image/png,$data\">$fn<br>"

4046 done)

4047 form=$form"<br>$imgs"

4048 val=$imgs # 2015-06-15

4049 else

4050 form="<input type=\"file\" name=\"$name\" $args>"

4051 fi

4052 fi

4053 ;;

4054 [Hh][Ii][Dd][Dd][Ee][Nn])

4055 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4056 args="value=\"$GF_STAGE\""

4057 fi

4058 form="<input type=\"hidden\" name=\"$name\" $args>"

4059 prompt='' # Remove prompt

4060 ;;

4061 [Aa][Uu][Tt][Hh][Oo][Rr])

4062 [ -n "$GF_VIEWONLY" ] && continue

4063 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4064 prompt="" ;;

4065 [Oo][Ww][Nn][Ee][Rr])

4066 [ -n "$GF_VIEWONLY" ] && continue

4067 val=${GF_OWNER:-$val}

4068 val=${val:-$user}

4069 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4070 prompt="" ;;

4071 [Uu][Ss][Ee][Rr])

4072 # XXX: is null $user ok?

4073 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4074 [ -n "$GF_VIEWONLY" ] && continue

4075 form="$user"

4076 ;;

4077 [Pp]assword)

4078 [ -n "$GF_VIEWONLY" ] && continue

4079 form="`cgi_passwd`"

4080 val=""

4081 ;;

4082 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4083 [ -n "$GF_VIEWONLY" ] && continue

4084 if [ -z "$rowid" ]; then

4085 val=`genserial`

4086 fi

4087 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4088 prompt="" ;;

4089 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4090 prompt=""

4091 ;;

4092 parent|path|blog*)

4093 prompt=""

4094 ;;

4095 "*"*)

4096 tail=$tail"``"

4097 continue ;;

4098 esac

4099 if [ -n "$prompt" ]; then

4100 if [ -n "${GF_VIEWONLY}" ]; then

4101 form=$val

4102 else

4103 :

4104 fi

4105 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4106 else

4107 hiddens=$hiddens$nl"$form"

4108 fi

4109 done < $1

4110 # enctype="multipart/form-data"

4111 cat<<EOF

4112 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4113 ${rowid:+$rm}

4114 <table class="b $2">

4115 $forms

4116 </table>$hiddens

4117 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4118 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4119 EOF

4120 if [ -z $GF_VIEWONLY ]; then

4121 cat<<EOF

4122 <input type="submit" name="sub" value="OK">

4123 <input type="reset" name="res" value="Reset">

4124 EOF

4125 fi

4126 cat<<EOF

4127 $GF_ARGS</form>

4128 $tail

4129 EOF

4130 }

4131 edittable() {

4132 # $1=form-def $2=table $3 rowid

4133 genform "$@"

4134 }

4135 viewtable() {

4136 GF_VIEWONLY=1 genform "$@"

4137 }

4138 showattc() {

4139 # $1=table_m $2=rowid &optional $3=RawFlag

4140 ## err \$1=$1 \$2=$2 \$3=$3

4141 if ! isfilereadable $user $1 $2; then

4142 contenttype; echo

4143 echo "このファイルは管理者のみしか見られません" | html p

4144 putfooter; exit

4145 fi

4146 idir=`umask 002; mktempd` || exit 1

4147 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4148 bin=$idir/$myname-$$.bin

4149 sql="select quote(bin) from $1 where rowid='$2';"

4150 ## err showattc: sql: $sql

4151 sq $db "$sql" | unhexize > $bin

4152 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4153 type=${tv%//*} fn=${tv#*//}

4154 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4155 ct=${type#file:}

4156 case $ct in # all text/* changed to text/plain

4157 text/*)

4158 charset=`nkf -g $bin|cut -d' ' -f1`

4159 case $charset in

4160 ASCII*) charset="" ;;

4161 esac

4162 if [ -z "$3" ]; then

4163 ct="text/html${charset:+; charset=$charset}"

4164 link="?showattc+$1+$2+raw"

4165 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4166 | sed 's,^,<span></span>,' \

4167 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4168 -D_LINK_="$link" \

4169 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4170 exit $?

4171 fi

4172 ct="text/plain${charset:+; charset=$charset}"

4173 ;;

4174 esac

4175 contenttype "$ct"

4176 echo "Content-Disposition: filename=\"$fn\""

4177 echo "Content-Length: " `cat $bin | wc -c`; echo

4178 #echo "Content-Type: " ${type#file:}; echo

4179 cat $bin

4180 }

4181 #

4182 # Some default stupid handler on CGI values

4183 #

4184 default_storedb() {

4185 # ARG: $1=table-def-file

4186 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4187 tbl=`basename $1`

4188 tbl=${tbl%.def}

4189 cols="`grep :text $1|cut -d: -f2`"

4190 col=`echo "$cols"|head -1`

4191 vcol=`getpar $col`

4192 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4193 if [ -n "$vcol" ]; then

4194 par2table $1

4195 else

4196 return 2 # No insertion occurred

4197 fi

4198 }

4199

4200 default_view() { # $1=def-file

4201 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4202 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4203 default_storedb "$@"

4204 query "select rowid from $tbl order by rowid desc;" \

4205 | while read rowid; do

4206 viewtable $1 $tbl $rowid

4207 done | _m4 -D_TITLE_="$tbl" \

4208 -D_FORM_="`genform $1`" \

4209 -D_DUMPTABLE_="syscmd(cat)" \

4210 $layout/html.m4.html $layout/form+dump.m4.html

4211 }

4212 default_viewtext() { # $1=def-file

4213 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4214 default_storedb "$@"

4215 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4216 | _m4 -D_TITLE_="$tbl" \

4217 -D_FORM_="`genform $1`" \

4218 -D_DUMPTABLE_="syscmd(cat)" \

4219 $layout/html.m4.html $layout/form+dump.m4.html

4220 }

4221 default_smail() {

4222 default_storedb "$@"

4223 if [ $? -eq 2 ]; then

4224 _m4 -D_TITLE_="入力" \

4225 -D_FORM_="`genform $1`" \

4226 -D_DUMPTABLE_="" \

4227 $layout/html.m4.html $layout/form+dump.m4.html

4228 return

4229 fi

4230 cond=""

4231 for pk in `gettblpkey $tbl`; do

4232 pv=$(sqlquote "$(getpar $pk)")

4233 cond="$cond${cond:+ and }$pk=$pv"

4234 done

4235 sql="select rowid from $tbl where $cond;"

4236 rowid=`query "$sql"`

4237 ## err smail1 - "$sql" "-> rowid=$rowid"

4238

4239 while IFS=: read prompt name keytype type args; do # Read from $1

4240 val=`getpar $name`

4241 if [ -n "$val" ]; then

4242 text="$text

4243 $prompt

4244 $name=$val

4245 ---------------------------------------------------------"

4246 fi

4247 case "$type" in

4248 image|document|file)

4249 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4250 fns=$(echo "$fn"|while read fn; do

4251 err mv $tmpd/$fn.orig $tmpd/$fn

4252 mv $tmpd/$fn.orig $tmpd/$fn

4253 rm $tmpd/$fn.rowid # Remove cache flag

4254 ## err "`ls $tmpd/$fn`"

4255 echo $fn

4256 done)

4257 files="$files $fns"

4258 ;;

4259 esac

4260 done < $1

4261 ## err FILES=$files "`ls -lF $tmpd`"

4262 subj="from ${REMOTE_ADDR}"

4263 (echo "$url"

4264 echo "への書き込みがありました。"

4265 echo "------"

4266 echo "$text"

4267 ) | (cd $tmpd &&

4268 err LS="`ls -lF`" &&

4269 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4270 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4271 echo "以下の内容で送信しました。" | html p

4272 viewtable $1 $tbl \

4273 `query "select rowid from $tbl order by rowid desc limit 1;"`

4274 echo "戻る" | html a "href=\"?\""

4275 }