s4: 929a925f10d8 s4-funcs.sh

s4

view s4-funcs.sh @ 466:929a925f10d8

Group name used at the following query should be quoted.

author	HIROSE Yuuji <yuuji@gentei.org>
date	Tue, 22 Aug 2017 08:51:06 +0859
parents	9724472a3cd2
children	c00857f0e9d2

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 PATH=/usr/local/sqlite3/bin:/usr/local/vim7/bin:/usr/iekei/ImageMagick/bin:/usr/local/ImageMagick/bin:$PATH

11 tmpdir=${TMPDIR:-tmp}

12 dbdir=${DBDIR:-db}

13 tmpfiles=""

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

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

16 templ=${TEMPL:-templ}

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

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

19 imgdir=${IMGDIR:-img}

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

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

22 msgdir=$templ/msg

23 timeout="+2 days"

24 memoplimitdays="7"

25 dumpcollen=22

26 #thumbxy=120x120

27 thumbxy=96x96

28 iconxy_S=80x80

29 iconxy_M=400x400

30 maximagexy=1600x1600

31 ### maximagexy=400x400

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

33 blogreadflagrowid=0

34 querylog=$tmpdir/query.log

36 tconfs=""

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

38 conftbl=_tblconf

39 nl="

40 "

41 iconcachekey="profimgcache_S"

42 case "$HTTP_USER_AGENT" in

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

44 *) touchpanel="" ;;

45 esac

46 . ./s4-cgi.sh

48 : <<EOF

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

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

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

53 レポート提出管理

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

56 [2016]

57 7/12 根本への反省

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

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

61 [以下2015]

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

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

64 Web

65 締切設定

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

68 ○自分の提出物リスト

70 7/19 ○設置

71 ○一斉送信

72 ○getparfilename の tmpd の扱い

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

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

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

76 ○添付ファイル回収

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

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

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

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

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

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

84 ○レポート提出モード

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

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

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

90 7/9 ○管理者の追加

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

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

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

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

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

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

99 ○グループ検索

100

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

102

103

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

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

106

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

108 ○6/1 par2tableを triplex 対応に

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

110 →とすると一気に

111

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

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

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

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

116

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

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

119 ## 1. passwd

120 ## 2. grp

121 ## 3. grp_mem

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

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

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

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

126

127 ## ● listの定義:

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

129 ## ● hashの定義:

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

131

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

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

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

135 ## csv2sq3 で .csv.sq3 の Makefile

136

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

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

139 ## type := root | comment

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

141 ## unique(id, filename)

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

143

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

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

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

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

148 ## できたー!

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

150 ↓

151 ↓以下に変更

152 with recursive allmem as

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

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

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

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

157

158

159 with recursive allmem as

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

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

162 where allmem.val=grp_m.gname)

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

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

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

166

167

168 ## triggerもできた。

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

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

171

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

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

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

175 ## グループ管理

176 ## 個人情報管理

177 ##

178

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

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

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

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

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

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

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

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

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

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

189

190 ## ログテーブル

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

192

193

194

195 ■表設計

196 * 3つの表に分散管理

197 id格納表 + hash表 + list表

198 * *_s *_m

199

200

201

202 user, user_map, user_col

203

204 ■抽象エントリタイプ

205 * user

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

207 * group

208 権限判定に利用

209 * serial

210 自動idとして機能

211 * password

212 入力 type=passwordで入力

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

214 * session

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

216 * text

217 入力 type=text

218 * textarea

219 入力 textarea

220 * image|document

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

222 * owner

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

224 * gowner

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

226 * timestamp

227 datetime()

228 * parent

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

230 * path

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

232

233 格納タイプ

234 * list

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

236 * hash

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

238

239 オブジェクトタイプ

240 * entry

241 id, title, owner

242 * textpart

243 id, parentID, text

244 * binarypart

245 id, parentID, contenttype, filename, content

246 * content

247 hash(textpart), list(binarypart)

248 * topic

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

250 * reply

251 id, parentID, content

252 * blog

253 list(entry)

254 blog = [topic, list(reply)]

255

256

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

258 "text" => "hogehoge ..",

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

260 "author" => "taro",

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

262 "parent" => "/",

263 "path" => "/1",

264 "text" => "blah, blah, ....",

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

266 {"serial" => 2,

267 "author" => "hanako",

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

269 "parent" => "/",

270 "path" => "/2",

271 "text" => "blah, blah, ....",

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

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

274

275

276 user:=

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

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

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

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

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

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

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

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

285

286 変換表

287 /user/email=m

288

289 blog:=

290 シリアル:id:p:serial

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

292 所有者:owner:s:owner:

293 時刻:ctime:s:stamp:

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

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

296

297 article:=

298 シリアル:id:p:serial

299 筆者:author:s:owner

300 時刻:ctime:s:stamp:

301 参照元:parent:s:parent:

302 パス:path:s:path:

303 本文:text:s:textarea:

304 画像:image:m:image:

305

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

307

308

309 EOF

310

311 sq() {

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

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

314 }

315 dbsetup() {

316 [ -d $tmpdir ] || mkdir -m 1777 $tmpdir

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

318 sqi=$tmpdir/sqi.$$

319 sqo=$tmpdir/sqo.$$

320 mkfifo $sqi $sqo

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

322 sq $db < $sqi &

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

324 exec 2>> $tmpdir/error.out

325 exec 3>> $tmpdir/debug.out

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

327 rm $sqi

328 }

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

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

331 }

332 cleanup() {

333 trap '' INT HUP EXIT TERM PIPE

334 echo .quit >&5

335 kill $sq3pid

336 kill $sq3pid

337 rm -f $sqo $sqi

338 rm -rf $tmpfiles

339 cleanup2

340 }

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

342 # simple redirection for the sake of speed.

343 query() {

344 echo ".once $sqo" >&5

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

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

347 tee -a $querylog

348 else

349 echo "$@" >> $querylog

350 echo "$@"

351 fi >&5

352 cat $sqo

353 echo '>>>' >> $querylog

354 }

355 _m4() {

356 #_S4NAME_=f,f,f

357 m4 ${_S4NAME_:+"-D_S4NAME_=${_S4NAME_}"} "$@"

358 }

359 ismember() {

360 # $1=user, $2=group

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

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

363 }

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

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

366 }

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

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

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

370 }

371 isgrpowner() (

372 # $1=user, $2=group

373 gn=`sqlquote "$2"`

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

375 err isgrpowner: $sql

376 test -n "`query $sql`"

377 )

378 getgroupadminmails() {

379 # $1=group

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

381 email4group "$1" "$i" ;

382 done

383 }

384 getgroupadmins() { # $1=group

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

386 qgrp=`sqlquote "$1"`

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

388 }

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

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

391 getvalbyid grp $2 \

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

393 }

394 getgroupbyid() {

395 # $1=id|gname

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

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

398 # err ggbyid: `echo $sql`

399 query $sql

400 }

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

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

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

404

405 # Return true when anonymous mode

406 [ "$anonymousmode" ] && return 0

407 # case `getvalbyid blog mode $2` in

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

409 # *closed*)

410 # owner=`getvalbyid blog owner $2`

411 # if isgrp $owner; then

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

413 # elif isuser $owner; then

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

415 # fi

416 # esac

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

418 sql="with getblog as (\

419 select key,val from blog_s where id=(\

420 select blogid from article where id in\

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

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

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

424 select case\

425 when (select author from article where\

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

427 then 'author'\

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

429 then 'open'\

430 when (select val from getmode) is null \

431 then 'open'

432 when (select val from getowner) in (select gname from grp)\

433 then (select user from grp_adm where \

434 gname=(select val from getowner) and \

435 user='$1')\

436 when (select author from article where\

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

438 then 'user+author'

439 else '' end;"

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

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

442 result=`query "$sql"`

443 [ -n "$result" ] && return 0

444 return 2

445 }

446 linkhome() {

447 # $1=UserOrGroup

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

449 if isuser $1; then

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

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

452 else

453 echo -n "grp+$1"

454 fi

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

456 }

457 hreflink() {

458 # s4 specific notation:

459 # ^href=URL

460 # ^iframe=URL

461 # OSM umap Wikistyle Notation:

462 # [[URL]] - Simle Link

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

481 }

482 minitbl() {

483 sed -n '

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

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

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

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

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

489 H; # Concat this line to HoldSpace

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

491 $ b cont

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

493 }

494 :cont

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

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

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

498 # . of ^. is workaround for FreeBSD sed

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

500 p; # Print whole "table" element

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

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

503 }

504 x; # Back to the newest line

505 p; # Print rest'

506 }

507 acclog() (

508 # $1=table, $2=rowid

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

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

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

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

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

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

515 fi

516 )

517 gecos() (

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

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

520 )

521 setpar() {

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

523 }

524 replpar() {

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

526 }

527 getpar() {

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

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

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

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

532 fi

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

534 case "$var" in

535 owner)

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

537 echo $user; return

538 elif ismember $user $val; then

539 echo $val; return

540 fi ;;

541 esac

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

543 echo "$val"

544 }

545

546 getpartype() {

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

548 }

549 getparcount() {

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

551 }

552 getparfilename() {

553 # null if type of $1 is not file

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

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

556 }

557 sqlquote() {

558 (v="$1"

559 case "$v" in

560 "") return ;; # null

561 "X'"*) # quoted hex string

562 echo $1 ;;

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

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

565 echo "\"$v\""

566 return ;;

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

568 echo "\"$v\""

569 return ;;

570 *)

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

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

573 else

574 echo "\"$v\""

575 fi ;;

576 esac)

577 }

578 sqlquotestr() (

579 case "$1" in

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

581 echo "'$v'" ;;

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

583 esac

584 )

585 mktempd() {

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

587 }

588 getcachedir() { # $1=maintable

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

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

591 else

592 echo $tmpd/$thumbxy

593 fi

594 }

595 getval() {

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

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

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

599 echo "$user" ;;

600 stamp|datetime)

601 date "+%F %T" ;;

602 serial)

603 (s=`getpar $2`

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

605 *)

606 getpar "$2" "$3";;

607 esac

608 }

609

610 getvalquote() {

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

612 (v=`getval "$@"`

613 case "$v" in

614 "") echo NULL ;;

615 *) sqlquote "$v" ;;

616 esac)

617 }

618 getparquote() {

619 sqlquote "`getpar $1`"

620 }

621 getbinbyid() {

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

623

624 }

625 getvalbyid() {

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

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

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

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

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

631

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

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

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

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

636 return

637 fi

638 done

639 rowid=$3

640 pk=`gettblpkey $1`

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

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

643 td=${4:-$tmpd}

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

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

646 for kt in s m; do

647 t=${1}_$kt

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

649 vcount=1 # count(val)

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

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

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

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

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

655 if [ $kt = m ]; then

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

657 # Reset val to store filenames if type is string

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

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

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

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

662 i=0

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

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

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

666 i=$((i+1))

667 fn=$c.$i

668 err td=$td, fn=$fn, type=$type, val="[$val]"

669 case $type in

670 file:*)

671 #file=$td/$val

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

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

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

675 # FOR SPEED: Skip file generation if imgcache exists

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

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

678 && continue

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

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

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

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

683 select rowid from $cond $slice;

684 .output '$td/$fn'

685 select val from $cond $slice;

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

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

688 .output stdout

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

690 EOF

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

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

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

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

695 ls -lh "$file" |

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

697 case "$type" in

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

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

700 *|*:[Aa]pplication*)

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

702 png:- > "$file"

703 ;;

704 esac

705 ;;

706 *)

707 sq $db<<EOF

708 .output $td/$fn.rowid

709 select rowid from $cond $slice;

710 .output $td/$fn

711 select val from $cond $slice;

712 EOF

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

714 ;;

715 esac

716 done

717 else

718 rm -f $td/$c.count

719 case $type in

720 file:*)

721 echo "$val" \

722 | while read fn; do

723 file=$td/$fn

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

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

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

727 | unhexize > "$file"

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

729 case $type in

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

731 *:[Aa]pplication*)

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

733 png:- > $file ;;

734 esac

735 fi

736 done

737 ;;

738 esac

739 fi

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

741 return

742 fi

743 done

744 done)

745 }

746 getvalbypkey() (

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

748 pk=`gettblpkey $1`

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

750 getvalbyid "$1" "$2" $rowid $4

751 )

752 getvalbycond() {

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

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

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

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

757 getvalbyid "$1" "$2" $rowid "$4"

758 fi

759 }

760 getpwfield() {

761 # getpwfield user column

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

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

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

765 echo "$val"

766 return 0

767 else

768 return 1

769 fi

770 }

771 encode() {

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

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

774 sha1=sha1

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

776 sha1=sha1sum

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

778 sha1=gsha1sum

779 fi

780 fi

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

782 }

783 enjpeg() {

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

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

786 cjpeg="cjpeg"

787 else

788 cjpeg="convert - jpeg:-"

789 fi

790 fi

791 $cjpeg "$@"

792 }

793 mycrypt() (

794 key=$1 salt=$2

795 err \$2=$2

796 case $2 in

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

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

799 esac

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

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

802 )

803 hexize() {

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

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

806 hexize="xxd -p"

807 else

808 hexize_hd() {

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

810 }

811 hexize="hexize_hd"

812 fi

813 fi

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

815 }

816 unhexize() {

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

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

819 unhex="xxd -p -r"

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

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

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

823 EOF

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

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

826 # world writable...:)

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

828 fi

829 fi

830 cat "$@" | $unhex

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

832 }

833 percenthex() {

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

835 }

836 htmlescape() {

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

838 -e "s/</\</g; s/>/\>/g"

839 }

840 enascii() {

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

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

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

844 else

845 enascii_now=`date +%FT%T`

846 enascii_sed() {

847 nkf -Z0Z1Z2 \

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

849 }

850 enascii="enascii_sed"

851 fi

852 fi

853 cat "$@" | $enascii

854 }

855 size_h() {

856 i="$1" oi=$1

857 set -- B B KB MB GB TB

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

859 oi=$i

860 i=$((i/1024))

861 shift

862 done

863 echo ${oi}$1

864 }

865 gettblconf() {

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

867 ## tconfs=`sq $db \

868 tconfs=`query \

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

870 fi

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

872 }

873 gettblkeys() {

874 # $1=tbl

875 gettblconf

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

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

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

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

880 case $type in

881 *p*)

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

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

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

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

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

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

888 esac

889 case $type in

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

891 esac

892 done

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

894 }

895 gettblpkey() {

896 # $1=tbl

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

898 }

899 gettblfkey() {

900 (x=`gettblkeys $1`

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

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

903 }

904 gettblcols() {

905 (x=`gettblkeys $1`

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

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

908 }

909 gettbl_s_cols() {

910 (x=`gettblkeys $1`

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

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

913 }

914 gettbl_m_cols() {

915 (x=`gettblkeys $1`

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

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

918 }

919 gettbl_h_cols() {

920 (x=`gettblkeys $1`

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

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

923 }

924 gettbl_coltype() (

925 gettblconf

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

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

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

929 )

930 is_hidden() {

931 # $1=Tbl $2=col

932 gettblconf

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

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

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

936 case $x in

937 *h*) return 0 ;;

938 *) return 1 ;;

939 esac

940 }

941

942 dbsetbyid() {

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

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

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

946 quotedp=$(sqlquotestr "$p")

947 unset primary update

948 gettblconf

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

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

951 #err conf=$conf

952 case ${conf#*=} in

953 p*) primary=1 ;;

954 f*) update=1 ;;

955 u*) ;;

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

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

958 esac

959 #err t=$t

960 type=string fn=""

961 case $conf in

962 */password)

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

964 ;;

965 */image*|*/document*)

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

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

968 ;;

969 esac

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

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

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

973 if [ "$primary" ]; then

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

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

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

977 elif [ "$update" ]; then

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

979 else

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

981 fi

982 )

983 }

984 expire() (

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

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

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

988 )

989 addsession() {

990 # expireをセット

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

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

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

994 # Remove old session parameters

995 now=`expire now`

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

997 }

998 gencookie() (

999 for kv; do

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

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

1002 done

1003 )

1004 contenttype() {

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

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

1007 }

1008 putheader() {

1009

1010 }

1011 putfooter() {

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

1013 }

1014 getcookie() (

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

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

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

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

1019 done

1020 )

1021 genrandom() {

1022 # $1=columns (default: 10)

1023 dd if=/dev/urandom count=1 2>/dev/null|nkf -MB|fold -w${1:-10}|sed -n 10p

1024 }

1025 genserial() {

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

1027 }

1028 smail() {

1029 # smail rcpts subj (file)

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

1031 # $MAIL_FROM <- From: header value

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

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

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

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

1036 cat $3 | nkf -jd ) | sendmail -f $admin $rcpt

1037 }

1038 setviastring() {

1039 table=$1

1040 oifs="$IFS"

1041 IFS="&"

1042 for us in $2; do

1043 k=${us%%=*}

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

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

1046 #echo $k=$v

1047 done

1048 IFS="$oifs"

1049 }

1050 checkdomain() (

1051 # Check the validity of domain by referring DNS

1052 item=$1

1053 err checkdomain $1

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

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

1056 )

1057 pwcheck() {

1058 # $1=passwd

1059 dbpswd=`getpwfield $user pswd`

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

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

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

1063 }

1064 mypwhash() {

1065 mycrypt `cat` `genrandom 5`

1066 }

1067 wasureta() {

1068 user=$1

1069 if ! checkdomain $user; then

1070 contenttype; echo

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

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

1073 putfooter

1074 exit 0

1075 fi

1076 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1078 encpswd=`echo $newpswd|mypwhash`

1079 dbsetbyid user $user pswd "$encpswd"

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

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

1082 $msgdir/mail-newaccount.m4 \

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

1084 | smail $user "New Account"

1085 }

1086 checkauth() {

1087 user=`getpar user`

1088 skc=`getpar skey` # from cookie

1089 [ -z "$user" ] && return 3

1090 skey="`getpwfield $user skey`"

1091 if [ -n "$skey" ]; then

1092 if [ x"$skey" = x"$skc" ]; then

1093 return 0

1094 fi

1095 fi

1096 pswd=`getpar pswd`

1097 quser=`sqlquotestr "$user"`

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

1099 if [ -z "$dbuser" ]; then

1100 return 1

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

1102 wasureta $user

1103 return 1 # wasureta error

1104 fi

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

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

1107 if pwcheck "$pswd"; then

1108 newsession=`genrandom 50`

1109 dbsetbyid user $user skey "$newsession"

1110 gencookie "user=$user" "skey=$newsession"

1111 return 0

1112 fi

1113 return 2 # Password mismatch

1114 }

1115 showlogin() {

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

1117 _m4 -D_SYSNAME_="Welcome" -D_MYNAME_="$myname${args+?}$args" \

1118 $layout/login.m4.html

1119 exit 0

1120 }

1121 dologin() {

1122 checkauth

1123 st=$?

1124 if [ $st != 0 ]; then

1125 contenttype; echo

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

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

1128 showlogin # and EXIT

1129 fi

1130 }

1131

1132 # Do instant jobs here

1133 dbsetup

1134 trap cleanup INT HUP EXIT TERM PIPE

1135 # trap cleanup INT HUP

1136

1137 err() {

1138 echo "$@" 1>&3

1139 }

1140

1141 cgiinit() {

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

1143 tmpf=tmp/stream

1144 tmpd=`tmpd=$tmpdir mktempd`

1145 tmpfiles=$tmpfiles" $tmpd"

1146 addsession $session

1147 getcookie

1148 case "$REQUEST_METHOD" in

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

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

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

1152 # cat > $tmpf # too much?

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

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

1155 s="`cat tmp/stream`"

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

1157 ;;

1158 esac

1159 case "$CONTENT_TYPE" in

1160 *boundary*)

1161 bndry=${CONTENT_TYPE#*boundary=}

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

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

1164 do

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

1166 #echo u=$us

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

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

1169 # err k=$k v=$v

1170 case "$k" in

1171 *:filename)

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

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

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

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

1176 :

1177 fi

1178 # (echo k=$k v="[$v]"; ls -lF "$tmpd/$v"; file --mime-type "$tmpd/$v") 1>&3

1179 case `file --mime-type - < "$tmpd/$v"|cut -d' ' -f2` in

1180 [Ii]mage/x-xcf)

1181 bzip2 "$tmpd/$v"

1182 v=${v}.bz2

1183 ;;

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

1185 [Ii]mage/*)

1186 mogrify -resize $maximagexy'>' "$tmpd/$v"

1187 ;;

1188 esac

1189 ;;

1190 *)

1191 type='string'

1192 ;;

1193 esac

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

1195 setpar "$k" "$type" "$v"

1196 done

1197 ;;

1198 *)

1199 setviastring par "$s"

1200 ;;

1201 esac

1202 }

1203 email4group() {

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

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

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

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

1208 sql="select coalesce(s.val, g.user) from grp_mem g

1209 left join grp_mem_s s on g.gname=s.gname and g.user=s.user

1210 and s.key='email'

1211 where g.gname=$qgrp and g.user in ($users);"

1212 query "$sql"

1213 }

1214 email4groupbyuid() {

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

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

1217 uids=`echo "$@"`

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

1219 sql="WITH

1220 grpemails AS (

1221 SELECT gname, user, val email

1222 FROM grp_mem NATURAL JOIN grp_mem_s

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

1224 useremails AS (

1225 SELECT user.rowid rid, user.name, val email

1226 FROM user

1227 LEFT JOIN user_s

1228 ON user.name=user_s.name AND user_s.key='email')

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

1230 FROM useremails u LEFT JOIN grpemails g

1231 ON u.name=g.user

1232 WHERE u.rid in ($uids);"

1233 ## err email4gByid `echo $sql`

1234 query "$sql"

1235 }

1236 collectmembersbyid() {

1237 # Collect user names of group specified by grid

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

1239 query "SELECT user FROM grp_mem \

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

1241 }

1242 collectmembersbyid() {

1243 # Collect user names of group name

1244 qgrp=`sqlquote "$1"`

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

1246 }

1247 collectgecosesbyid() {

1248 # Collect user gecoses of group

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

1250 query<<-EOF

1251 SELECT gecos

1252 FROM gecoses

1253 WHERE name IN (SELECT user FROM grp_mem

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

1255 EOF

1256 }

1257 collectemail() (

1258 # Collect email addresses for group $1

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

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

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

1262 for e; do

1263 if isuser "$e"; then

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

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

1266 else

1267 qgrp=`sqlquote "$e"`

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

1269 gmem="grp_mem"

1270 else

1271 tm=`sqlquote "$TEAM"`

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

1273 fi

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

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

1276 $gmem g left join grp_mem_s s

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

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

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

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

1281 query "$sql"

1282 fi

1283 done

1284 )

1285 sendinvitation() (

1286 # $1=email

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

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

1289 query "replace into par values('$iss', 'invite', 'string', \"$1\");"

1290 gecos=`gecos`

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

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

1293 _m4 -D_URL_="$urlbase" \

1294 -D_USER_="$name" \

1295 -D_EMAIL_="$1" \

1296 -D_REGIST_="$regist" \

1297 -D_ADMIN_="$admin" \

1298 $msgdir/mail-invite.m4 \

1299 | smail $1 "BBSへの御招待"

1300 return 0

1301 )

1302 emaildomaincheck() {

1303 case "$1" in

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

1305 *@*)

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

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

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

1309 return 2

1310 fi

1311 return 0

1312 ;;

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

1314 esac

1315 }

1316 invite() {

1317 email=`getpar email`

1318 case $email in

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

1320 *@*)

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

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

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

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

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

1326 elif sendinvitation $email; then

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

1328 fi ;;

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

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

1331 esac

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

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

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

1335 fi

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

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

1338 $layout/html.m4.html $layout/invite.m4.html

1339 }

1340 regist() {

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

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

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

1344 echo "bye bye" | html p

1345 reutrn

1346 fi

1347 email=`session=$1 getpar invite`

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

1349 cat<<EOF

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

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

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

1353 EOF

1354 return

1355 fi

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

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

1358 # Fake login password to wasureta

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

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

1361 wasureta $email

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

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

1364 addsession $1 # for removal after 1 minute

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

1366 $layout/login.m4.html

1367 return

1368 }

1369 group_safename() {

1370 # Convert $1 to safe group name

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

1372 }

1373 groupupdate() {

1374 gname=`getpar gname`

1375 qgname=`sqlquote "$gname"`

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

1377 # See ALSO same job in showgroup()

1378 newgname=`group_safename "$gname"`

1379 err newgname=$newgname

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

1381 err NewGNAME: gname=$newgname

1382 gname=$newgname

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

1384 replpar gname string "$gname"

1385 fi

1386 # Name confliction check

1387 parow=`getpar rowid`

1388 ## err parow=$parow

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

1390 query "BEGIN EXCLUSIVE;"

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

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

1393 if [ $count -gt 0 ]; then

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

1395 query "END;"

1396 return

1397 fi

1398 par2table $formdir/grp.def

1399 query "END TRANSACTION;"

1400 # Remove orphan

1401 : <<EOF

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

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

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

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

1406 EOF

1407 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1411 sql="delete from blog where id in

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

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

1414 query "$sql";

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

1416 return # and return

1417 fi

1418 fi

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

1420 fi

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

1422 grid=$(query $sql)

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

1424 grp $grid

1425 }

1426 groupclone() {

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

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

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

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

1431 return

1432 fi

1433 i=0

1434 while true; do

1435 copy="-copy$i"

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

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

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

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

1440 i=$((i++))

1441 continue

1442 fi

1443 break

1444 done

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

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

1447 query<<-EOF

1448 BEGIN;

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

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

1451 SELECT $newqname, key, val

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

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

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

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

1456 -- Copy members and their configuration --

1457 REPLACE INTO grp_mem SELECT $newqname, user

1458 FROM grp_mem WHERE gname=$qgrp;

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

1460 FROM grp_mem_s WHERE gname=$qgrp;

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

1462 FROM grp_mem_m WHERE gname=$qgrp;

1463 -- Copy administrators --

1464 REPLACE INTO grp_adm SELECT $newqname, user

1465 FROM grp_adm WHERE gname=$qgrp;

1466 COMMIT;

1467 EOF

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

1469 STOPCLONEMSG=1 groupconf "$newrowid"

1470 }

1471 groupman() {

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

1473

1474 GF_STAGE="grpconf"

1475 GF_STAGE=groupupdate

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

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

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

1479 -D_DUMPTABLE_="syscmd(cat)" \

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

1481 }

1482 userconf() {

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

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

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

1486 }

1487 groupconf() {

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

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

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

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

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

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

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

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

1496 echo "同じ構成員で新規グループ<a href=\"?groupclone+$rowid\">作成</a>" \

1497 | html p

1498 fi

1499 }

1500 mems() {

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

1502 kwd=`getpar kwd`

1503 listmember $kwd

1504 }

1505 grps() {

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

1507 kwd=`getpar kwd`

1508 listgroup $kwd \

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

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

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

1512 $layout/form+dump.m4.html

1513 }

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

1515 gpg=`getpar grp`

1516 grid=${1:-$gpg}

1517 grp=`getgroupbyid "$grid"`

1518 ## . ./s4-blog.sh

1519 jg=`getpar joingrp`

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

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

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

1523 fi

1524 htmlheader=$layout/html.m4.html

1525 showgroup "$grid"

1526 }

1527 sql4interestblogs() {

1528 cat<<EOF

1529 CREATE TEMPORARY VIEW interestblogs AS

1530 SELECT blog.rowid rid, id, author

1531 FROM blog

1532 NATURAL JOIN

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

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

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

1536 WHEN (SELECT user FROM grp_mem

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

1538 THEN 0

1539 ELSE 1

1540 END;

1541 EOF

1542 }

1543 listnewblogsql() { # $1=user

1544 deftime=`query "SELECT coalesce((SELECT time FROM acclog

1545 WHERE user='$user'

1546 AND tblrowid=$blogreadflagrowid),

1547 "0");"`

1548 cat<<EOF

1549 `sql4interestblogs`

1550 WITH article_ctime as (

1551 SELECT id,blogid,author,val ctime

1552 FROM article join article_s s using(id)

1553 WHERE s.key='ctime'

1554 ), blog_title_owner as (

1555 SELECT blg.rid brid, id,

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

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

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

1559 ), visited AS (

1560 SELECT b.id id, brid, b.owner owner, b.title title, ctime, ac.author author

1561 FROM blog_title_owner b, article_ctime ac

1562 JOIN acclog al

1563 ON b.id=ac.blogid

1564 AND al.tbl='blog'

1565 AND al.tblrowid=brid

1566 AND al.user='$user'

1567 AND al.time < ctime

1568 AND '$deftime' < ctime

1569 ), unvisited as (

1570 SELECT b.id id, brid, b.owner owner, b.title title, ctime, ac.author author

1571 FROM blog_title_owner b, article_ctime ac

1572 ON b.id=ac.blogid

1573 WHERE brid NOT IN (SELECT tblrowid FROM acclog

1574 WHERE tbl='blog' AND user='$user')

1575 AND ctime > '$deftime'

1576 ), blogall as (

1577 /* ---------------------------------------

1578 Collect new articles with dividing them into visited and unvisited

1579 separately, because constructing joined table of artice X acclog

1580 tends to become HUGE combinations.

1581 --------------------------------------- */

1582 SELECT * FROM visited UNION SELECT * FROM unvisited

1583 ), news as (

1584 select bl.brid brid, bl.title, bl.id blid, ctime,

1585 count(bl.id) "新着", bl.owner, bl.author

1586 from blogall bl /* left join a_u l

1587 on bl.brid=l.tblrowid */

1588 group by bl.id order by ctime desc,"新着" desc, bl.id

1589 LIMIT 10

1590 ) SELECT brid LINK, "新着",

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

1592 ctime, title,

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

1594 FROM news;

1595 EOF

1596 }

1597

1598 search_form() {

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

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

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

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

1603 @week → 最近一週間

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

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

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

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

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

1609 auth=""

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

1611 case "$1" in

1612 author=*)

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

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

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

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

1617 help="★★ $g さんの書き込みから検索します$nl$help"

1618 ;;

1619 grp=*)

1620 a=`echo "${1#grp=}"|htmlescape`

1621 g=`gecos ${1#grp=}`

1622 auth="<input type=\"hidden\" name=\"owner\" value=\"$a\">"

1623 placeholder="このグループからの検索"

1624 ;;

1625 esac

1626 cat<<-EOF

1627 <div class="right">

1628 <form action="$myname">$auth

1629 <input type="text" name="kwd" value="" title="$help"

1630 placeholder=" $placeholder " width="10" accesskey="k">

1631 ${touchpanel:+<p class="help">$help</p>}

1632 <input type="hidden" name="stage" value="searchart">

1633 </form>

1634 </div>

1635 EOF

1636 }

1637

1638 imgsrc_cache() (

1639 # $1 = directory for cache'ing

1640 # $2 = table (user_m or grp_m)

1641 # $3 = keycond (was: condition for choosingowner)

1642 # $4 = size : S = Small, M = Medium, O = Original

1643 dir="$1" tbl="$2"

1644 keycond="$3"

1645 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

1646 ORDER BY rowid DESC LIMIT 1"

1647 [ -d "$dir" ] || mkdir -p "$dir"

1648 tmpf=$tmpd/imgsrc_cache.$$

1649 case "$4" in

1650 [Ss]) size=S ;;

1651 [Oo]) size=O ;;

1652 *) size=M ;;

1653 esac

1654 # ImageCache filename storing schema:

1655 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

1656 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

1657 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

1658 valtype=`query "$sql0"`

1659 filename=${valtype%%//*}

1660 filetype=${valtype##*//file:}

1661 if [ x"$filename" = x"${filename%.*}" ]; then

1662 # If nor filename extension found, set it to image type

1663 case "$filetype" in

1664 image/*) filename=$filename.${filetype#image/} ;;

1665 esac

1666 fi

1667 cacheimg_S=$dir/S_$filename

1668 cacheimg_M=$dir/M_$filename

1669 cacheimg_O=$dir/$filename

1670 cacheimg=$dir/${size}_$filename

1671 sumfile="$dir/$filename.sum"

1672 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

1673 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

1674 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

1675 # if cache is fresh and has the same checksum,

1676 echo "<img src=\"$cacheimg\">"

1677 else

1678 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

1679 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

1680 fifo2=$fifo.2

1681 mkfifo $fifo $fifo2

1682 fmt=${filename##*.}

1683 ## [[ NOTE ]]

1684 ## a. convert oldimage newimage

1685 ## b. convert oldimage fmt:- | convert - newimage

1686 ## b is much smaller than a

1687 cat $tmpf | unhexize \

1688 | tee $fifo \

1689 | convert -define ${fmt}:size=${iconxy_M} \

1690 -resize ${iconxy_M}'>' - ${fmt}:- \

1691 | tee $fifo2 \

1692 | convert - "$cacheimg_M" &

1693 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

1694 -resize ${iconxy_S}'>' - ${fmt}:- \

1695 | convert - "$cacheimg_S" &

1696 printf '%s' "<img src=\"data:${filetype},"

1697 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

1698 echo '">'

1699 echo "$sum" > $sumfile

1700 fi

1701 ## Now preparing cache image, done.

1702 ## Store this information to DB

1703 stbl=${tbl%_m}_s # user_s or grp_s

1704 pkey=${keycond%%=*} # Primary Key name

1705 pval=${keycond#*=} # Primary Key value

1706 query <<-EOF

1707 REPLACE INTO $stbl($pkey, key, type, val)

1708 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

1709 EOF

1710 )

1711

1712 showhome() {

1713 # $1=userRowIdToShow

1714 err showhome \$1=$1

1715 case "$1" in

1716 *@*) uname=`getvalbypkey user name "$1"` ;;

1717 *) uname=`getvalbyid user name $1` ;;

1718 esac

1719 ## err ShowHome: uname=$uname

1720 td=`getcachedir home/"$1"`

1721 gecos=`gecos "$uname"`

1722 ## err SH:gecos=$gecos

1723 GF_VIEWONLY=1

1724 cond="gname in (select gname from grp_mem where user='$uname')"

1725 search_form_args=""

1726 if [ x"$user" = x"$uname" ]; then

1727 conflink="<a href=\"?userconf\" accesskey=\"e\"

1728 title=\"E\">プロフィールの編集</a> /

1729 <a href=\"?blog\" accesskey=\"n\" title=\"N\">新規話題の作成</a>"

1730 # Display folders

1731 sql="select count(id) from article_m where id

1732 in (select id from article where author='$user')

1733 and type like 'file:%';"

1734 ## err nfile-sql=`echo "$sql"`

1735 nfile=`query "$sql"`

1736 # err nfile=$nfile

1737 if [ $nfile -gt 0 ]; then

1738 conflink="$conflink / <a href=\"?lsmyfile\" accesskey=\"l\"

1739 title=\"L\">過去の提出ファイル</a>"

1740 fi

1741 else

1742 search_form_args="author=$uname"

1743 fi

1744 . ./s4-blog.sh

1745

1746 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

1747 search_form $search_form_args > $sf

1748 echo "$gecos さん" > $tf

1749 { echo "<div class=\"noprofimg\">"

1750 viewtable $formdir/user.def user $1

1751 echo "</div>"

1752 } > $pf

1753

1754 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

1755 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

1756 imf=$tmpd/profimg.$$; touch $imf

1757 if [ -n "$img" ]; then

1758 if true; then

1759 tbl=user_m

1760 enticond="name='$uname'"

1761 imgsrc_cache "$td/main" user_m "$enticond" M

1762 else

1763 { printf '%s' "<IMG src=\"data:${img#file:},"

1764 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

1765 | sed 's/$..$/%\1/g'

1766 echo '">'

1767 }

1768 fi > $imf

1769 fi

1770 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

1771 val='$uname';"`

1772 listblog $uname > $bf

1773

1774 hometail=$tmpd/tail.$$

1775 mkfifo $hometail

1776

1777 #Calling listgroupbytable, originally here

1778

1779 (

1780 # Display Most Recent Entry

1781 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

1782 shortval=${shortval:-val}

1783

1784 # The m.aid in the next line is suspicious. But works fine in SQLite3...

1785 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

1786 ctime,

1787 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

1788 (SELECT gecos FROM gecoses

1789 WHERE name=(SELECT val FROM blog_s

1790 WHERE key='owner' AND id=b.id)) owner,

1791 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

1792 FROM blog b

1793 JOIN

1794 (SELECT distinct blogid, a.id aid, max(val) ctime

1795 FROM article a, article_s s

1796 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

1797 GROUP BY blogid ORDER BY val DESC LIMIT 50

1798 ) m

1799 ON b.id=m.blogid;"

1800 # This should be as follows

1801 : <<EOF

1802 WITH arts AS(

1803 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

1804 a.blogid, a.id id, s.val ctime

1805 FROM article a NATURAL JOIN article_s s

1806 WHERE s.key = 'ctime' AND a.author='$user'

1807 GROUP by s.id

1808 )

1809 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

1810 FROM arts a0

1811 JOIN

1812 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

1813 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

1814 ORDER BY ctime DESC LIMIT 50;

1815 EOF

1816

1817 cat<<-EOF

1818 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

1819 for="mre" title="D">最近の書き込み先</label>

1820 <div class="lcto">

1821 `DT_VIEW=replyblog dumptable html blog`

1822 </div>

1823 EOF

1824 unset DT_SQL

1825 if [ x"$user" = x"$uname" ]; then

1826 # Display NEWS

1827 # 2016-06-26

1828 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

1829 acclog blog $blogreadflagrowid

1830 # echo "全部既読にしました" | html p

1831 fi

1832 # 2016-02-19 Counting NEWS without using dumptable.

1833 sql=`listnewblogsql "$user"`

1834 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

1835 cont=`echo "$new10"|grep "^<TR>"|wc -l`

1836 cont=$((cont-1))

1837 err newcount=$cont

1838 if [ $cont -gt 0 ]; then

1839 #echo "全体の新着記事${cont}傑" | html h2

1840 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

1841 echo "<label for=\"new10\" title=\"F\">新着${cont}傑</label><div>"

1842 cat<<-EOF | html form 'action="?home"'

1843 `cgi_checkbox readchk yes 'id="read"'`<label

1844 for="read">新着ふくめて全部読んだことにする</label>

1845 `cgi_submit '確定'`

1846 `cgi_hidden read yes`

1847 EOF

1848 echo "$new10 "

1849 echo "</div>"

1850 fi

1851 else # Not My Home ($user != $uname)

1852 : # DT_SQL=

1853 fi

1854 ) > $hometail & # Is background call safe to m4??

1855 #

1856 listgroupbytable $formdir/grp.def $cond |

1857 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

1858 -D_PROFILE_="spaste(\`$pf')$conflink" \

1859 -D_PROFIMG_="spaste(\`$imf')" \

1860 -D_BLOGS_="spaste(\`$bf')" \

1861 -D_SEARCH_="spaste(\`$sf')" \

1862 -D_NBLOG_="$nblog" \

1863 -D_GROUPS_="syscmd(\`cat')" \

1864 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

1865 $layout/html.m4.html $layout/home.m4.html

1866

1867 # Record access log

1868 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

1869 }

1870 commission() { # $1=grp-rowid $2=user-rowid

1871 contenttype; echo

1872 ## err commission: "$@"

1873 gname=`getgroupbyid $1`

1874 echo "グループ $gname 管理者委任" \

1875 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

1876 if [ -n "$2" ]; then

1877 grp_reg_adm "$@"

1878 else

1879 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

1880 fi

1881 }

1882 listgroupbytable() {

1883 # $1=deffile $2...=condition

1884 tagline=`grep :tag: $1`; shift

1885 and="${1:+and }" where=${1:+where }

1886 href="<a href=\"$myname?grp+"

1887 echo '<div class="listgroup">'

1888 NGsql="select distinct tag from\

1889 (select gname, max(case key when 'tag' then val end) as tag, \

1890 max(case key when 'ctime' then val end) as ctime\

1891 from grp_s group by gname order by ctime);"

1892 sql="select val from grp_s where key='tag' $and$* group by val;"

1893 ## err ListGRP: query sql="$sql"

1894 for tag in `query "$sql"`

1895 do

1896 ## err ListGrp: tag=$tag

1897 tn=${tagline%%=${tag}*}

1898 tn=${tn##*[ :]}

1899 sql="select rowid||':'||gname as 'グループ名',説明 from

1900 (select (select rowid from grp g where g.gname=grp_s.gname)

1901 as rowid,

1902 gname,

1903 max(case key when 'gecos' then val end) as '説明',

1904 max(case key when 'tag' then val end) as 'tag',

1905 max(case key when 'mtime' then val end) as mtime from grp_s

1906 $where$* group by gname having tag='$tag' order by mtime desc);"

1907 ## err PersonalGroupList= `echo $sql`

1908 echo "<h2>$tn</h2>"

1909 echo '<table class="b listgroup">'

1910 sq -header -html $db "$sql" \

1911 | sed "s,$<TR><TD>$$[0-9]*$:$[^ ]*$</TD>,\1$href\2\">\3</a>,"

1912 echo '</table>'

1913 done

1914 echo '</div>'

1915 }

1916 iconhref() (

1917 # $1=icon-file, $2=Href $3=title $4...=anchor

1918 data=`percenthex "$1"`

1919 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

1920 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

1921 href=$2; title=$3; shift 3

1922 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

1923 )

1924 iconhref2() (

1925 # $1=icon-file, $2=Href $3=title $4...=anchor

1926 src=$1

1927 href=$2; title=$3; shift 3

1928 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

1929 )

1930 listentry() (

1931 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

1932 # Referring variable $iamowner=$grp to attach owner-request links

1933 ## err listentry: \$1=$1 \$2=$2 \$3=$3

1934 cond='' hiddens=''

1935 offset=`getpar offset`; offset=${offset%%[!0-9]*}

1936 if [ -z "$offset" ]; then

1937 offset=`getpar start`; offset=${offset%%[!0-9]*}

1938 offset=$((offset-1))

1939 fi

1940 offset=$((offset + 0)) # change to numeric forcibly

1941 [ $offset -lt 0 ] && offset=0

1942 limit=30

1943 dir=`getcachedir "$1"`

1944 if [ x"$1" = x"user" ]; then

1945 hrb="$myname?home"

1946 deficon=person-default.png

1947 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

1948 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

1949 gcs=gecos

1950 else # if group

1951 hrb="$myname?grp"

1952 deficon=group-default.png

1953 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

1954 gcs=name

1955 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

1956 if [ -n "$tagline" ]; then

1957 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

1958 ## err tagconv=$tagconv

1959 fi

1960 fi

1961 if [ ! -d $dir ]; then

1962 mkdir -p $dir

1963 fi

1964 if [ ! -s $dir/$deficon ]; then

1965 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

1966 fi

1967 if [ -n "$2" ]; then

1968 cond="where nick like '%$2%' or b.name like '%$2%'"

1969 fi

1970

1971 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

1972 # grpは呼出し元の動的スコープ変数でよくないな...

1973 ##qgrp=`sqlquote $grp`

1974 getgrp="(select gname from grp where rowid=${rowid:--1})"

1975 sql="select a.rowid, a.$link,

1976 coalesce(b.$gcs, a.$nm) as nick,

1977 quote(a.$nm) as qname,

1978 (SELECT val FROM ${tbl}_s

1979 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

1980 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

1981 || case when a.$nm in (select gname from grp)

1982 then printf('(%d名)',

1983 (select count(user) from grp_mem where gname=a.$nm))

1984 else ' <'||a.$nm||'>'

1985 end

1986 as name,

1987 b.tag,

1988 case when a.$nm in (select user from grp_adm

1989 where gname=$getgrp) then '(管理者)'

1990 when '$user' in (select user from grp_adm where gname=a.$nm)

1991 then '(ADMIN)'

1992 when '$user' in (select user from grp_mem where gname=a.$nm)

1993 then '(Member)'

1994 when '$iamowner' = '' then ''

1995 else ',not='||a.rowid end as ownerlink,

1996 CASE '$entity'

1997 WHEN 'グループ'

1998 THEN coalesce(

1999 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2000 'open')

2001 ||

2002 CASE WHEN '$user'

2003 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2004 THEN ' ismember'

2005 ELSE ''

2006 END

2007 ELSE 'user'

2008 END regmode

2009 from $tbl a left join

2010 (select $nm as name,

2011 max(case key when 'gecos' then val end) as gecos,

2012 max(case key when 'tag' then val end) as tag,

2013 max(case key when 'mtime' then val end) as mtime,

2014 max(case key when 'wtime' then val end) as wtime

2015 from ${tbl}_s group by $nm)

2016 b on a.$nm=b.name $cond $3

2017 order by b.tag desc, b.wtime desc, b.mtime desc, a.rowid asc"

2018 # Give precedence to newer maintained groups (2016-09-24)

2019 # Note that mtime is stored only in grp_s.

2020 ## err LE:sql.1="$sql"

2021 total=`query "with x as ($sql) select count(*) from x;"`

2022 echo "${entity} 一覧" | html h2

2023 if [ $total -gt $limit ]; then

2024 echo '<div class="right">'

2025 cgi_form $stage <<EOF

2026 <label>次の語を含む${entity}で検索:

2027 `cgi_text kwd $kwd`</label>

2028 EOF

2029 echo '</div>'

2030 fi

2031 hiddens="$hiddens

2032 `cgi_hidden kwd \"$kwd\"`

2033 `cgi_hidden stage \"$stage\"`"

2034 cat<<EOF

2035 <form action="$myname" method="POST">

2036 <p>${total}件中の<input class="hidesub" type="text" name="start"

2037 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2038 <input type="submit" value="確定"></p>

2039 </form>

2040 EOF

2041 if [ $((offset+limit)) -lt $total ]; then

2042 nextbtn=$(

2043 cat<<EOF

2044 <div class="right clear"><form action="$myname" method="POST">

2045 `cgi_submit 次の${limit}件`

2046 $hiddens

2047 `cgi_hidden offset $((offset + limit))`</form></div>

2048 EOF

2049 )

2050 fi

2051 if [ $offset -gt 0 ]; then

2052 prevbtn=$(

2053 cat<<EOF

2054 <form action="$myname" method="POST">

2055 `cgi_submit 前の${limit}件`

2056 $hiddens

2057 `cgi_hidden offset $((offset - limit))`</form>

2058 EOF

2059 )

2060 fi

2061 pnbtn="$nextbtn$prevbtn"

2062 echo $pnbtn

2063

2064 ## err ListEntry: `echo "$sql"\;`

2065 # sq $db here??? 2016-11-28

2066 query "$sql limit $limit ${offset:+offset $offset};" \

2067 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2068 err name=$name owner=$ownerp lnk=$lnk

2069 err newlnk=$lnk regmode=$regmode

2070 icondir=$dir/$id

2071 # Pick up only last icon

2072 echo "<div class=\"iconlist xy$thumbxy $type\">

2073 <p class=\"tag _$tag\">$tag</p>" \

2074 | _m4 $tagconv

2075 if [ -n "$NOSPEEDUP" ]; then

2076 files=`getvalbyid $tbl profimg $id $icondir`

2077 if [ -n "$files" ]; then

2078 icon=`echo "$files"|tail -1`

2079 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2080 else

2081 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2082 fi

2083 elif [ -n "$icon" -a -s "$icon" ]; then

2084 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2085 else

2086 cond="$nm=$qname"

2087 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2088 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2089 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2090 # err "img=[$img]"

2091 if [ -n "$img" ]; then

2092 echo "<a href=\"$hrb+$lnk\">"

2093 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2094 echo "</a>"

2095 else

2096 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2097 fi

2098 fi

2099 echo "<br>$name${ownerp:+<br>$ownerp}"

2100 echo "</div>"

2101 done

2102 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2103 )

2104 listmember() {

2105 listentry user "$@"

2106 }

2107 listgroup() {

2108 listentry group "$@"

2109 }

2110 hexteams() { # $1=gname, $2(optional)=user

2111 cond=${2:+" AND user='$2'"}

2112 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2113 WHERE gname='$1' AND key='team'$cond;"

2114 }

2115 showgroup() { # $1=group-rowid

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

2117 grid=`getpar grid`

2118 grid=${grid%%[!0-9]*}

2119 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2120 else

2121 grid=$1

2122 fi

2123 grp=`getgroupbyid $grid`

2124 qgrp=`sqlquote "$grp"`

2125 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2126 if isgroup "$grp"; then

2127 tf=$tmpd/title.$$

2128 sf=$tmpd/search.$$

2129 bodyclass=`query "SELECT val FROM grp_s

2130 WHERE gname=$qgrp AND key='regmode';"`

2131 if ismember "$user" "$grp"; then

2132 ismember="ismember"

2133 qgrp=`sqlquote "$grp"`

2134 bodyclass="$bodyclass${bodyclass:+ }ismember"

2135 else

2136 ismember="" # bodyclass="group"

2137 fi

2138 bodyclass="$bodyclass grouphome"

2139 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2140 echo "グループ $grp" > $tf

2141

2142 showgroupsub $formdir/grp.def "$grid" | \

2143 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2144 -D_FORM_="syscmd(\`cat')" \

2145 -D_BODYCLASS_="$bodyclass" \

2146 -D_DUMPTABLE_="" \

2147 $htmlheader $sf $layout/form+dump.m4.html

2148 # $htmlheader $layout/form+dump.m4.html

2149 # $htmlheader is defined in grp()

2150 else # if $grp is removed at par2table

2151 listgroup

2152 fi

2153 }

2154 showgroupsub() {

2155 # $1=def-file $2=group-rowid

2156 # Using $ismember

2157 rowid=$2

2158 grp=`getgroupbyid $2`

2159 qgrp=`sqlquote "$grp"`

2160 td=`getcachedir grp/"$2"`

2161 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2162 if [ -z "$rowid" ]; then

2163 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2164 #grp=`sq $db "select gname from grp where rowid=$grp"`

2165 echo "showgroupsub: invalid argument($1 $2)" | html p

2166 return

2167 fi

2168 val=`getvalbyid grp profimg $rowid $tmpd`

2169 enticond="gname=$qgrp"

2170 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2171 if [ -n "$img" ]; then

2172 cat<<-EOF

2173 <p class="groupimg">

2174 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2175 EOF

2176 fi

2177 echo "<div class=\"noprofimg\">"

2178 viewtable $1 grp $rowid

2179 echo "</div>"

2180 if isgrpowner "$user" "$grp"; then

2181 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2182 iamowner=$rowid

2183 colmd=" mode"

2184 fi

2185 if [ -n "$ismember" ]; then

2186 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2187 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2188 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2189 cat<<EOF

2190 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2191 <div class="fold clear">

2192 `cgi_checkbox send yes id="send"`<label

2193 for="send">グループ全員にメッセージ送信</label>

2194 <div>

2195 `cgi_textarea message "" "cols=60"`

2196 `cgi_submit 送信`

2197 `cgi_reset リセット`

2198 </div>

2199 `cgi_hidden grp $rowid`

2200 </div></form>

2201 EOF

2202 fi

2203 # 加入ボタン + 加入者リスト

2204 if [ -n "$ismember" ]; then

2205 ismem='checked' state="(参加中)"

2206 else

2207 nomem='checked' state="(現在非加入)"

2208 fi

2209 # このグループでの加入アドレス

2210 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2211 and key='email';"`

2212 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2213 ## and key='email';"

2214 ##err email=$eml

2215 cat <<EOF

2216 <div class="fold clear">

2217 `cgi_checkbox reg yes id="reg"`<label

2218 for="reg">自身の加入状態を操作する</label>$state

2219 <div>

2220 EOF

2221 cgi_form grp <<EOF

2222 <p>このグループに</p>

2223 <table class="b">

2224 <tr><th>メンバーとして</th><td>

2225 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2226 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2227 <tr><th>参加する場合のメイルアドレス<br>

2228 <small>(メインのアドレスとは違うものにする場合に記入<br>

2229 同じでよい場合は空欄に)</small></th>

2230 <td>`cgi_text email $eml`</td></tr>

2231 </table>

2232 `cgi_hidden grp $rowid`

2233 EOF

2234 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2235 echo "moderated (承認加入の)グループなので実際に参加できるのは

2236 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2237 fi

2238 echo '</div></div>'

2239 echo '<h2>話題一覧</h2>'

2240 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2241 cat<<-EOF

2242 <form class="summary" action="$myname" title="$thelp">

2243 `cgi_hidden owner "$grp"`

2244 `cgi_hidden kwd "@week"`

2245 `cgi_hidden stage searchart`

2246 `cgi_submit "一週間のまとめ"`

2247 </form>

2248 EOF

2249 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2250 colstate="state:稼動状態:frozen=rowclass=凍結"

2251 DT_CHLD=article:blogid \

2252 DT_VIEW=replyblog dumptable html blog \

2253 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2254

2255 getgname="(select gname from grp where rowid=$rowid)"

2256 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2257 cm="?commission+$rowid"

2258 thumbxy=50x50 listmember "" "$c" "$rowid" \

2259 |sed -e "s|$<br>$,not=$.*$|\1|" # 間違って押しやすい

2260 # team list

2261 hexteams=`hexteams "$grp"`

2262 if [ -n "$hexteams" ]; then

2263 echo "チーム一覧" | html h2

2264 echo '<div class="dumptable"><table class="b">'

2265 sq $db -html -header<<-EOF

2266 SELECT val TEAM,

2267 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2268 MEMBERS

2269 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2270 EOF

2271 echo '</table></div>'

2272 fi

2273 }

2274 grp_getbodyclass() {

2275 # Get css class name for document.

2276 # `moderated' for moderated groups

2277 # `ismember' for groups where user belongs

2278 # $1=GroupName (w/o quote)

2279 # $user=userNameCurrentlyLogin

2280 ## err grp_getbodyclass: 1="$1"

2281 qgrp=`sqlquote "$1"`

2282 query<<-EOF

2283 SELECT coalesce(

2284 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2285 'open')

2286 ||

2287 CASE WHEN '$user'

2288 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2289 THEN ' ismember'

2290 ELSE ''

2291 END;

2292 EOF

2293 }

2294 grpaction() { # $1=group-rowid

2295 err GRP_ACTION:IN

2296 grid=${1:-`getpar grp`}

2297 grp=`getgroupbyid "$grid"`

2298 if [ -z "$grp" ]; then

2299 echo "無効な指定です。" | html p; return

2300 fi

2301 if ! ismember $user "$grp"; then

2302 echo "加入者のみに許可された操作です。" | html p; return

2303 fi

2304 echo "グループ $grp 個別選択操作" \

2305 | _m4 -D_TITLE_="syscmd(\`cat')" \

2306 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2307 $layout/html.m4.html

2308

2309 isowner=""

2310 isgrpowner "$user" "$grp" && isowner="yes"

2311 usel=`getpar usel`

2312 if [ -n "$usel" ]; then

2313 uids=$(echo `echo $usel`|tr ' ' ',')

2314 ## err grpaction-1: grp=$grp, `echo $sql`

2315 text=`getpar text`

2316

2317 rm=`getpar rm` cfm=`getpar confirm`

2318 ## err rm=$rm cfm=$cfm

2319 if [ x"$rm" = x"yes" ]; then

2320 if [ "$isowner" ]; then

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

2322 # Eliminate

2323 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2324 for tbl in grp_mem grp_mem_s grp_mem_m; do

2325 sql="delete from $tbl $cond;"

2326 # echo "sql=$sql"

2327 query "$sql"

2328 err rmGRPuser "$sql"

2329 done

2330 num=`query "select count(*) from user where rowid in ($uids);"`

2331 #err num=$num

2332 if [ 0$num -gt 0 ]; then

2333 sql="select coalesce(b.val,a.name) from user a left join \

2334 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2335 # err `echo "$sql"`

2336 html pre<<EOF

2337 以下の${num}名のグループ $grp 登録を解除しました。

2338 `query "$sql"`

2339 EOF

2340 fi

2341 else

2342 echo "確認のチェックがないのでやめておきます。" | html p

2343 return

2344 fi

2345 else # not Group Owner

2346 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2347 return

2348 fi

2349 cat<<EOF

2350

2351 EOF

2352 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2353 if [ -z "$text" ]; then # if msg is empty

2354 echo "なにかメッセージを..." | html p

2355 return 0

2356 fi

2357 gecos=`gecos $user`

2358 mkfrom=`getpar mkfrom`

2359 if [ x"$mkfrom" = x"yes" ]; then

2360 safegc=`echo "$gecos" | tr -d '<>@'`

2361 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2362 fromad=`email4groupbyuid "$grp" "$myuid"`

2363 mail_from="$safegc <$fromad>"

2364 else

2365 mail_from="$admin"

2366 fi

2367 MAIL_FROM=$mail_from \

2368 smail "`email4groupbyuid "$grp" $usel` $user" \

2369 "$gecos さんからのメッセージ" <<EOF

2370 $url

2371 のグループ「$grp」のメンバーである $gecos さんから、

2372 あなた宛へのメッセージです。

2373 ----------------------------------------------------------

2374 $text

2375 EOF

2376 if [ $? = 0 ]; then

2377 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2378 sql="select coalesce(b.val, a.name) from

2379 (select name from user where rowid in ($uids)) a

2380 left join user_s b on a.name=b.name and b.key='gecos';"

2381 html pre<<EOF

2382 `query "$sql"`

2383 (送信者である $gecos さんも含まれます)

2384 EOF

2385 err SendDone: `echo $sql`

2386 fi

2387 elif [ x"$rm" = x"commission" ]; then

2388 grp_reg_adm $grid $usel

2389 elif [ x"$rm" = x"addteam" ]; then

2390 team=`getpar team|sed "s/'/''/g"` # for single quotation

2391 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2392 if [ x"$team" != x"$newteam" ]; then

2393 echo "チーム名に使えない文字を除去しました" | html p

2394 team=newteam

2395 fi

2396 if [ -z "$team" -o x"$team" = x"なし" ]; then

2397 cat<<-EOF | html p

2398 有効なチーム名を入力してください。

2399 カンマだけ、「なし」という名前は使えません。

2400 EOF

2401 echo "有効なチーム名を入力してください。" | html p

2402 else

2403 grp_add_team $grid "$team" $usel

2404 fi

2405 elif [ x"$rm" = x"rmteam" ]; then

2406 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2407 rmteam=`getpar rmteam|sed "s/'/''/g"`

2408 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2409 gname='$grp' AND user='$user' AND key='team'\

2410 AND val='$rmteam';\"`" ]; then

2411 grp_rm_team $grid "$rmteam" $usel

2412 else

2413 echo "所属していないチームの除去操作はできません。"|html p

2414 fi

2415 else

2416 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2417 fi

2418 fi

2419 fi

2420 # New entry

2421 sql="WITH mems AS (

2422 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2423 ON gm.user=g.name

2424 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2425 ), posts AS (

2426 SELECT author, count(author) post

2427 FROM article NATURAL JOIN article_s

2428 WHERE blogid IN (SELECT id FROM blog_s

2429 WHERE key='owner'

2430 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2431 AND key='text'

2432 GROUP BY author

2433 ), teams AS (

2434 SELECT user, group_concat(val, ', ') team

2435 FROM grp_mem_m

2436 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2437 AND key='team'

2438 GROUP BY user

2439 ), user_post AS (

2440 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2441 FROM mems m LEFT JOIN posts

2442 ON m.name=posts.author

2443 GROUP by m.rowid

2444 )

2445 SELECT

2446 CASE

2447 WHEN (SELECT user FROM grp_adm

2448 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2449 AND user=up.name) IS NOT NULL

2450 then 'k'

2451 ELSE ''

2452 END || rowid || ',' || gecos NAME,

2453 post POST, team TEAM

2454 FROM user_post up LEFT JOIN teams t

2455 ON up.name=t.user

2456 ORDER BY gecos;"

2457 ## err grpaction: "`echo \"$sql\"`"

2458 tf=$tmpd/title.$$

2459 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2460 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2461 for=\"cmadmin\">グループ管理者委任</label>

2462 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2463 </p></div>"

2464 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2465 for=\"conf\">グループ登録解除</label>

2466 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2467 <p>この操作による通知は本人に行きません。

2468 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2469 </div>"

2470 # Get team list to which current user belongs into $hexteams

2471 myhexteams=$(hexteams "$grp" "$user")

2472 allhexteams=$(hexteams "$grp")

2473 if [ -n "$myhexteams" ]; then

2474 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2475 for=\"cmrmteam\">チーム属性除去</label>

2476 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2477 を除去します: `cgi_checkbox teamconfirm yes` 確認

2478 <p>この操作による通知は本人に行きません。

2479 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2480 </div>

2481 "

2482 fi

2483 b1='<label> <input type="checkbox" name="usel" value="'

2484 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2485 #b2='"> <span>' b3='</span></label>'

2486 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2487 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2488 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2489 cgi_form grpaction<<EOF \

2490 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2491 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2492 | _m4 -D_TITLE_="spaste(\`$tf')" \

2493 -D_SUBTITLE_="チェック後操作ボタン" \

2494 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2495 $layout/form+dump.m4.html

2496 <p>下でチェックした人を対象として:</p>

2497 <div class="foldtabs">

2498 `cgi_radio rm addteam 'id="cmteam"'`<label

2499 for="cmteam">同じチーム属性を付与</label>

2500 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2501 `cgi_datalist_h teams $allhexteams`

2502 </div>

2503 ${rmteammsg}

2504 `cgi_radio rm send id="sendmsg"`<label

2505 for="sendmsg">メッセージ送信</label>

2506 <div>

2507 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2508 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2509 `cgi_textarea text "" cols=40`

2510 </div>

2511 ${isowner:+$cmmsg$excmsg}

2512 `cgi_radio rm close id="x"`<label for="x">×</label>

2513 </div>

2514 <h4>$grp 参加者一覧</h4>

2515 <table class="td2r">

2516 `sq $db -header -html "$sql"`

2517 </table>

2518 `cgi_hidden grp $grid`

2519 EOF

2520 }

2521 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2522 # Create TEMPORARY VIEW

2523 query<<EOF

2524 CREATE TEMPORARY VIEW writeusers AS

2525 SELECT DISTINCT author FROM article

2526 WHERE id in (

2527 select id from article where blogid=(select id from blog where rowid=$1)

2528 );

2529 CREATE TEMPORARY VIEW movablegroups AS

2530 SELECT g.rowid growid , g.gname

2531 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2532 ON grp.gname=gm.gname -- そのユーザが属している

2533 AND user='$user') g -- グループに絞る

2534 WHERE (SELECT author FROM writeusers

2535 EXCEPT

2536 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2537 IS NULL;

2538 $2

2539 EOF

2540 }

2541 sql4readableblogs() {

2542 # Create view of blogs that can be readable to $user

2543 # Blog is readable when:

2544 # 1: blog owner is an user

2545 # 2: else, 2.1: owner-group where the $user belongs

2546 # 2.2: else, owner-group is not moderated

2547 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2548 cat<<EOF ## | tee tmp/sql.out

2549 CREATE TEMPORARY VIEW readableblogs AS

2550 SELECT blog.rowid rid, id, author

2551 FROM blog

2552 NATURAL JOIN

2553 (SELECT id,

2554 max(CASE key WHEN 'owner' THEN val END) owner,

2555 max(CASE key WHEN 'mode' THEN val END) mode

2556 FROM blog_s GROUP by id) bs

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

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

2559 WHEN (SELECT val FROM grp_s

2560 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2561 AND

2562 (SELECT user FROM grp_mem

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

2564 THEN 0

2565 WHEN mode = 'quiz'

2566 THEN 0 -- "quiz" mode blog cannot be searched

2567 ELSE 1

2568 END;

2569 EOF

2570 }

2571 editheading() { # $1=rowid-of-heading

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

2573 if [ -z "$rowid" ]; then

2574 echo "話題番号が未指定です。" | html p

2575 return

2576 fi

2577 owner=`getvalbyid blog owner $rowid`

2578 title=`getvalbyid blog title $rowid`

2579 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2580 | _m4 -D_TITLE_="修正" \

2581 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2582 -D_BLOGS_="" -D_DUMPTABLE_="" \

2583 -D_FORM_="syscmd(\`cat')" \

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

2585 # Move to group

2586 if isuser "$owner"; then

2587 crview4article $rowid

2588 n=`query "SELECT count(*) FROM writeusers;"`

2589 ## err N=$n

2590 if [ $((n)) -gt 0 ]; then

2591 ## err ROWID=$rowid

2592 sql="SELECT growid || ':' || gname FROM movablegroups;"

2593 cat<<-EOF

2594 <div class="fold">

2595 `cgi_checkbox mv send id="mv"`<label

2596 for="mv">この話題をグループ所有に移動する</label>

2597 <div>

2598 <form action="?mvart" method="POST" enctype="multipart/form-data">

2599 移動先グループ:

2600 <select name="mv2grp">

2601 EOF

2602 query ".mode html"

2603 query<<-EOF |

2604 $sql

2605 .mode list

2606 EOF

2607 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2608 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2609 cat<<-EOF

2610 </select>

2611 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2612 そのグループに加入しているものに限られます)</p>

2613 <p>`cgi_checkbox cfm yes`<label>確認

2614 (この操作は元に戻すことができません)</label></p>

2615 `cgi_hidden blogrowid $rowid`

2616 `cgi_submit 移動`

2617 `cgi_reset Reset`

2618 </form>

2619 </div>

2620 </div>

2621 EOF

2622 fi

2623 # end of isuser "$owner"

2624 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2625 [ -n "$hexteams" ];}; then

2626 none="`echo なし|hexize`"

2627 cat<<-EOF

2628 <div class="fold">

2629 `cgi_checkbox mv2team send id="mv2team"`<label

2630 for="mv2team">この話題を以下のチームのものにする</label>

2631 <div><p>現在の所属チーム設定:

2632 `query "SELECT

2633 coalesce((SELECT val FROM blog_s

2634 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2635 AND key='team'),

2636 ':なし');"`</p>

2637 <form action="?mvart" method="POST" enctype="multipart/form-data">

2638 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2639 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2640 `cgi_hidden blogrowid $rowid`<br>

2641 `cgi_submit 移動`

2642 `cgi_reset Reset`

2643 </form></div></div>

2644 EOF

2645 fi

2646 }

2647 mvart() { # move diary to some group or team

2648 # or move blog of group to team which belong to the group

2649 blogrowid=`getpar blogrowid`

2650 cfm=`getpar cfm`

2651 ##### echo move blog:$blogrowid to $mv2grp | html p

2652 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2653 . ./s4-blog.sh

2654 if [ -z "$blogrowid" ]; then

2655 echo "無効な指定です(mvart)。" | html p

2656 return

2657 elif [ x"$cfm" != x"yes" ]; then

2658 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2659 elif { mv2grp=`getpar mv2grp`

2660 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2661 [ -n "$mv2grp" ]; }; then

2662 crview4article $blogrowid

2663 ########## TRANSACTION BEGIN

2664 query "BEGIN;"

2665 n=`query "SELECT count(*) FROM writeusers;"`

2666 ## err Nwriteuser=$n

2667 if [ $((n)) -gt 0 ]; then

2668 query<<-EOF

2669 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2670 WHERE key='owner'

2671 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2672 AND $mv2grp IN (SELECT growid FROM movablegroups);

2673 EOF

2674 fi

2675 query "END;"

2676 ########## TRANSACTION END

2677 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2678 [ -n "$mv2team" ];}; then

2679 # blog owner can move it to ANY team

2680 case "$mv2team" in

2681 'なし')

2682 cat<<-EOF

2683 DELETE FROM blog_s

2684 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2685 AND key='team';

2686 EOF

2687 ;;

2688 "") ;;

2689 *)cat<<-EOF

2690 BEGIN;

2691 REPLACE INTO blog_s(id, key, val)

2692 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2693 'team', '$mv2team');

2694 REPLACE INTO blog_s(id, key, val)

2695 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2696 'notify', 'all'); -- Change notify to all

2697 END;

2698 EOF

2699 esac | query

2700 fi

2701 blog_reply $blogrowid

2702 echo yes | html p

2703 }

2704 editart() { # $1=article-rowid $2=blogrowid

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

2706 blogrowid=${2%%[!A-Z0-9a-z_]*}

2707 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2708 echo "表示する記事番号が未指定です。" | html p

2709 return

2710 fi

2711 owner=`getvalbyid blog owner $blogrowid`

2712 title=`getvalbyid blog title $blogrowid`

2713 author=`getvalbyid article author $rowid`

2714 ## err EDITart: owner=$owner, author=$author

2715 if isgrpowner "$user" "$owner"; then

2716 : EDIT OK

2717 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2718 echo "本人か所有者しか編集できません." | html p

2719 return

2720 fi

2721 aid=`query "select id from article where rowid=$rowid;"`

2722 tmpout=$tmpd/editart.$$.out

2723 GF_ACTION="?replyblog+$blogrowid#$aid" \

2724 edittable $formdir/article.def article $rowid \

2725 > $tmpout

2726 rm -f /tmp/editart.out

2727 # Cannot use pipelining to m4 with genform() because of stdin stack

2728 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2729 -D_FORM_="syscmd(cat $tmpout)" \

2730 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2731 -D_BLOGS_= -D_DUMPTABLE_= \

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

2733 }

2734 send2mem() {

2735 rowid=`getpar grp`

2736 rowid=${rowid%%[!0-9]*} # Cleaning

2737 if [ -z "$rowid" ]; then

2738 echo "グループが未指定です。" | html p

2739 return

2740 fi

2741 message=`getpar message`

2742 if [ -z "$message" ]; then

2743 echo "文章を入れてください。" | html p

2744 return

2745 fi

2746 grp=`getgroupbyid $rowid`

2747 members=`collectemail $grp`

2748 # smail rcpt subj (file)

2749 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$admin>" \

2750 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2751 $urlbase?grp+$rowid

2752 グループ $grp に所属する

2753 `gecos $user` さんよりメッセージ:

2754

2755 $message

2756 EOF

2757 cat<<EOF

2758 <p>以下のユーザに送信しました。</p>

2759 <pre>

2760 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

2761 </pre>

2762 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2763 EOF

2764 }

2765 joingrpadmit() {

2766 # $1=yes/no $2=session-key

2767 if [ -z "$2" ]; then

2768 echo "bye bye" | html p; return

2769 fi

2770 t_usr=`session=$2 getpar user`

2771 t_grp=`session=$2 getpar group`

2772 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2773 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2774 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2775 echo "無効な加入依頼です。" | html p

2776 echo "有効期限が切れたか、

2777 他の管理者がいる場合は処理済みの可能性があります。" | html p

2778 return

2779 fi

2780 if ! isgrpowner "$user" "$t_grp"; then

2781 echo "グループ管理者のみの機能です。" | html p; return

2782 fi

2783 case $1 in

2784 yes) joingrp "$t_grp" "$t_usr" yes ;;

2785 no) joingrp "$t_grp" "$t_usr" no ;;

2786 *)

2787 echo "無効な指定です($1)。" | html p

2788 return ;;

2789 esac

2790 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

2791 rcpts="`getgroupadminmails $t_grp` $user"

2792 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2793 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2794 に

2795 $t_usr

2796 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2797 しました。"

2798 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2799 query "delete from session where id='$2';"

2800 echo "$body" | html p

2801 }

2802

2803 joingrprequest() {

2804 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2805 jss="joingrp-`date +%s`-`genrandom 12`"

2806 addsession $jss +${memoplimitdays}days

2807 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

2808 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

2809 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2810 $url

2811 $user さんから

2812 グループ $1

2813 に加入依頼がありました。

2814

2815 承認する:

2816 $urlbase?joingrpadmit+yes+$jss

2817

2818 白紙に戻す:

2819 $urlbase?joingrpadmit+no+$jss

2820 EOF

2821 echo "管理者に加入依頼を出しました。

2822 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2823 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2824 }

2825 joingrp() {

2826 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2827 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2828 if isgrpowner "$user" "$1"; then

2829 isowner="yes"

2830 elif [ -n "$5" ]; then

2831 isowner="yes"

2832 else

2833 isowner=""

2834 fi

2835 ## err jg:isgrpowner: isowner="$isowner"

2836 if [ -n "$isowner" ]; then

2837 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2838 elif [ x"$2" != x"$user" ]; then # if user is not login user

2839 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2840 return

2841 elif [ x"$3" = x"no" ]; then

2842 : # Do not pursue those who leave

2843 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2844 : # Member can change own email address for the joining moderated group

2845 else # adding user is $user itself

2846 case `getgroupattr $1 regmode` in

2847 moderated)

2848 joingrprequest "$@" # Request only

2849 return

2850 ;;

2851 *)

2852 ;;

2853 esac

2854 fi

2855 qgname=`sqlquote "$1"`

2856 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

2857 cond="where gname=$qgname and user='$2'"

2858 if [ x"$3" = x"yes" ]; then

2859 query "replace into grp_mem values($qgname, '$2');"

2860 # Notify joingrp to admin

2861 action="に加入しました。"

2862 if [ -n "$4" ]; then

2863 if msg=`emaildomaincheck "$4"`; then

2864 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2865 'string', '$4', NULL);"

2866 else

2867 echo $msg

2868 fi

2869 else

2870 query "delete from grp_mem_s $cond and key='email';"

2871 fi

2872 if [ -n "$5" ]; then # as ADMIN

2873 # Coming here means newly created group

2874 sql="select case\

2875 when (select count(*) from grp_mem where gname=$qgname)=1\

2876 then (select user from grp_mem\

2877 where gname=$qgname and user='$user')\

2878 else '' end; "

2879 err NewGrpChk: $sql

2880 if [ -n "`query \"$sql\"`" ]; then

2881 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

2882 query "replace into grp_adm values($qgname, '$user');"

2883 fi

2884 fi

2885 else

2886 query "delete from grp_mem $cond;

2887 delete from grp_mem_s $cond;

2888 delete from grp_mem_m $cond;"

2889 action="から脱退しました。"

2890 fi

2891 smail "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

2892 $url?grp+$grid

2893 $user (`gecos $user`)さんが

2894 グループ $1

2895 $action

2896 EOF

2897 }

2898 grp_add_team() (

2899 # $1=grp-rowid $2=team $3...=user-rowid(s)

2900 grp=`getgroupbyid $1`

2901 team=$2; shift; shift

2902 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2903 { echo "BEGIN;"

2904 for user; do

2905 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2906 '$grp',\

2907 (SELECT name FROM user WHERE rowid=$user),\

2908 'team', 'string', '$team');"

2909 done

2910 echo "END;"

2911 } | query

2912 )

2913 grp_rm_team() (

2914 # $1=grp-rowid $2=team $3...=user-rowid(s)

2915 grid=$1

2916 qgrp=$(sqlquote "`getgroupbyid $grid`")

2917 team=$2; shift; shift

2918 [ -z "$grid" -o -z "$team" ] && return

2919 { echo "BEGIN;"

2920 for user; do

2921 echo "DELETE FROM grp_mem_m\

2922 WHERE gname=$qgrp \

2923 AND user=(SELECT name FROM user WHERE rowid=$user)\

2924 AND key='team' AND val='$team';"

2925 done

2926 cat<<-EOF

2927 DELETE FROM blog_s

2928 WHERE rowid=(

2929 SELECT rowid

2930 FROM blog_s a

2931 WHERE key='team'

2932 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2933 AND NOT EXISTS (SELECT * FROM grp_mem_m

2934 WHERE key='team' AND val=a.val -- a.val=team

2935 AND gname = (SELECT val FROM blog_s b

2936 WHERE a.id=b.id AND key='owner')

2937 ));

2938 EOF

2939

2940 echo "END;"

2941 } | query

2942 )

2943 grp_reg_adm() {

2944 # $1=grp-rowid $2...=user-rowid

2945 grid=$1

2946 grp=`getgroupbyid "$1"`

2947 if [ -z "$grp" ]; then

2948 echo "無効なグループIDです" | html p; return

2949 fi

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

2951 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2952 fi

2953 shift

2954 for urid; do

2955 newadm=`query "select name from user where rowid=$urid;"`

2956 if [ -z "$newadm" ]; then

2957 echo "指定ユーザIDがおかしいようです。" | html p; return

2958 fi

2959 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

2960 err ismember $newadm $grp

2961 if ismember $newadm "$grp"; then

2962 # OK, go ahead

2963 getgname="(select gname from grp where rowid=$grid)"

2964 query "replace into grp_adm values($getgname, '$newadm');"

2965 # confirm insertion

2966 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2967 if [ -n "`query \"$sql;\"`" ]; then

2968 echo "追加完了: $newadm" | html p

2969 else

2970 echo "追加失敗($1 $urid)" | html p

2971 fi

2972 fi

2973 showgroup $grid

2974 done

2975 }

2976 dt_rowhack() {

2977 # From: <TR>

2978 # ....

2979 # <TD>rowclass=foo</TD>

2980 # </TR>

2981 # To: <TR class="foo">....<TD>foo</TD></TR>

2982 sed -e '

2983 /^<TR>/ {

2984 :loop

2985 s/\n//

2986 N

2987 /<\/TR>/ {

2988 s/\n//

2989 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

2990 n

2991 }

2992 $q

2993 b loop

2994 }'

2995 }

2996 dumptable() {

2997 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2998 # textのフィールドだけ全てダンプにしたほうがいいか

2999 # $DT_VIEW sets link

3000 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3001 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3002 VIEW=${DT_VIEW-replyblog}

3003 if [ -n "$VIEW" ]; then

3004 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3005 fi

3006 # $DT_CHLD=ChildTable:BindColumn

3007 if [ -n "$DT_CHLD" ]; then

3008 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3009 cntall="(select count($_i) from $_t where $_i=a.id)"

3010 # XXX: Dirty workaround for slow subquery of acclog

3011 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3012 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

3013 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3014 and id in (select id from $_t where $_i=a.id) \

3015 and val > coalesce((select time from myacclog where \

3016 tblrowid=a.rowid),\

3017 '1970-01-01'))"

3018 cnt="$cntnew as '新着', $cntall as '総数',"

3019 dt_class=" td2r td3r dumpblogs"

3020 fi

3021 # Construct join expression

3022 eav="" scols=""

3023 pk=`gettblpkey $2`

3024 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3025 substr=${substr:-%s}

3026 for col in ${3:-`gettbl_s_cols $2`}; do

3027 valvar=val

3028 case $col in

3029 gecos) scols="$scols${scols:+, }${col#}"

3030 continue ;; # built-in column name

3031 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3032 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3033 case "$as" in

3034 *:*=*) cnd=${as#*:}

3035 h=${cnd%%=*} v=${cnd#*=}

3036 h=`sqlquotestr "$h"`

3037 v=`sqlquotestr "$v"`

3038 valvar="CASE val WHEN $h THEN $v END"

3039 as=${as%%:*} ;;

3040 esac

3041 ;;

3042 *) as=${col} ;;

3043 esac

3044 ss=`printf "$substr" "$valvar"`

3045 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3046 scols="$scols${scols:+, }b.$as"

3047 done

3048 #case author when '$user' then a.rowid else '---' end as ID,

3049 sql=${DT_SQL:-"select \

3050 a.rowid as LINK,\

3051 $cnt\

3052 $scols from $2 a left join\

3053 (select $pk,$eav,

3054 max(case key when 'owner'

3055 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

3056 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

3057 ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"

3058 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3059 <div>

3060 <div class="dumptable">

3061 <table class="b$dt_class">

3062 `sq -header -cmd ".mode $1" $db "$presql$sql"`

3063 </table>

3064 </div>

3065 </div>

3066 EOF

3067 }

3068

3069 par2table() (

3070 # copy current parameters of par into destination table

3071 # $1=definition-file

3072 # Using $user and $session

3073 # Return value:

3074 # 0: Stored successfully

3075 # 1: Insufficient fillings

3076 # 2: No permission to modify the record

3077 # 3: Invalid rowid

3078 # 4: SUCCESS to delete

3079 # 5: Stop deletion for lack of confirm check

3080 # 6: Password length too short

3081 # 7: Password mismatch

3082 # 8: Old password incorrect

3083 rowid=`getpar rowid`

3084 if [ ! -e $1 ]; then

3085 echo "テーブル定義ファイルが見付かりません" | html p

3086 exit 1

3087 fi

3088 tbl=${1%.def}

3089 tbl=${tbl##*/}

3090 if [ -n "$rowid" ]; then # Modify existing entry

3091 if [ x"$tbl" = x"user" ]; then

3092 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3093 elif [ x"$tbl" = x"grp" ]; then

3094 sql="select gname from $tbl where rowid=$rowid;"

3095 ##err p2t:grp:q $sql

3096 isgrpowner "$user" "`query $sql`" && rowowner=$user

3097 else

3098 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3099 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3100 fi

3101 ### err rowowner=$rowowner

3102 if [ x"$user" != x"$rowowner" ]; then

3103 echo "他人のレコードはいじれないの" | html p

3104 return 2

3105 elif [ -z "$rowowner" ]; then

3106 echo "指定したレコードはないみたい" | html p

3107 return 3

3108 fi

3109 rm=`getpar rm` cfm=`getpar confirm`

3110 # Editing existent entry

3111 if [ x"$rm" = x"yes" ]; then

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

3113 query "delete from $tbl where rowid=$rowid;"

3114 return 4

3115 else

3116 echo "消去確認のチェックがないので消さなかったの..." | html p

3117 return 5

3118 fi

3119 fi

3120 fi

3121

3122 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

3124 # Update of existing record

3125 for col in `gettblcols $tbl`; do

3126 val=`getparquote $col`

3127 [ -z "$val" ] && continue

3128 ## err query "update $tbl set $col=$val where rowid=$rowid"

3129 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3130 sql="update $tbl set $col=$val where rowid=$rowid;"

3131 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3132 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3133 ## User name cannot be changed with interface provided with this

3134 ## script. But we offer the trigger to change owner user

3135 ## of blog_s table.

3136 #err "select quote($col) from $tbl where rowid=$rowid;"

3137 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3138 cat<<-EOF | query

3139 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3140 -- should use EXCLUSIVE transaction outside of this.

3141 SAVEPOINT par2table;

3142 $sql

3143 update blog_s set val=$val

3144 where key='owner' and val=$old;

3145 RELEASE SAVEPOINT par2table;

3146 EOF

3147 ## XX: DIRTY Hack Ends here

3148 ## We should keep blog's owner as a single column which has

3149 ## foreign key constraint with primary key of grp/user.

3150 else

3151 query "$sql"

3152 fi

3153 done

3154 # Then, set up $pval for further insertion of tbl_s and tbl_m

3155 for col in `gettblpkey $tbl`; do

3156 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3157 pval="$pval${pval:+, }\"$val\""

3158 done

3159 else

3160 # New entry

3161 # Generate values() for primary keys

3162 for col in `gettblpkey $tbl`; do

3163 # Genuine primary keys for _m and _s

3164 val=`getvalquote $tbl $col`

3165 [ -z "$val" ] && continue

3166 pval="$pval${pval:+, }$val"

3167 done

3168 ##err pval=$pval

3169 for col in `gettblfkey $tbl`; do

3170 # args for values() to insertion into master table

3171 val=`getvalquote $tbl $col`

3172 [ -z "$val" ] && continue

3173 formaster=$formaster"${formaster:+, }$val"

3174 done

3175 formaster="$pval${formaster:+, }$formaster"

3176 ## err formaster=$formaster

3177 if [ -z "$formaster" ]; then

3178 echo "項目を全て埋めてください" | html pre

3179 return 1

3180 fi

3181 ## err "replace into $tbl values($formaster);"

3182 query "replace into $tbl values($formaster);"

3183 ## Insertion to master table, done

3184 fi

3185

3186 for kt in s m; do

3187 tb2=${tbl}_$kt

3188 for col in `gettbl_${kt}_cols $tbl`; do

3189 ptype=`getpartype $col "limit 1"`

3190

3191 # First, check update of existing entries in _m

3192 if [ $kt = m ]; then

3193 # sessID|address.1.22|string|Somewhere-x.y.z

3194 sql=""

3195 ##err dots from query "select var from par where var like '$col.%';"

3196 for v in `query "select var from par where var like '$col.%';"`; do

3197 # v=address.1.22

3198 st_rowid=${v##*.}

3199 origcol=${v%%.*} # original column derived from

3200 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3201 ##case `getpartype $v` in

3202 ## err CASE `gettbl_coltype $tbl/$origcol` in

3203 ## err edit flag = `getpar action.$v`

3204 case `getpar action.$v` in

3205 rm)

3206 if [ x`getpar confirm.$v` = x"yes" ]; then

3207 newsql="delete from $tb2"

3208 else

3209 echo "削除確認未チェック" | html p

3210 fi ;;

3211 edit)

3212 case `gettbl_coltype $tbl/$origcol` in

3213 image|document|binary)

3214 file=$tmpd/`getparfilename $v`

3215 ## err type=file=$file

3216 [ -z "$file" ] && continue

3217 bn=`sqlquotestr "${file##*/}"`

3218 bin="X'"$(hexize "$file")"'"

3219 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3220 type=\"file:$ct\"

3221 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3222 cachedir=`getcachedir "$tbl/$rowid"`

3223 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3224 rm -rf $cachedir

3225 ;;

3226 *)

3227 newsql="update $tb2 set val=(select val from par where var \

3228 like '$col.%.$st_rowid')"

3229 ;;

3230 esac

3231 ;;

3232 *) # maybe "keep", do not modify value

3233 continue

3234 ;;

3235 esac

3236 # err newsql=$newsql

3237 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3238 done

3239

3240 if [ x"$bin" = x"NULL" ]; then

3241 ## err repl:normal sql=`echo $sql`

3242 query "$sql

3243 delete from $tb2 where type='string' and val='';"

3244 ## err repl:normal done

3245 else

3246 sqlfile="$tmpd/sqlf.$$"

3247 echo "$sql" > $sqlfile

3248 ## err repl:sqlfile=`ls -lF $sqlfile`

3249 query ".read $sqlfile"

3250 ## err repl:done

3251 fi

3252 # Rest of kt==m: set multiple mode

3253 nr=`getparcount $col`

3254 else

3255 nr=1 # for kt==s, number of records is 1

3256 fi

3257

3258 i=0

3259 while [ $i -lt $nr ]; do

3260 limit="limit 1 offset $i"

3261 i=$((i+1)) # increase beforehand against continue

3262 val=`getvalquote $tbl $col "$limit"`

3263 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3264 ## err $col=$val

3265 bin=NULL

3266 ## err partype$col=`getpartype $col "$limit"`

3267 case $ptype in

3268 file) file=$tmpd/`getparfilename $col "$limit"`

3269 ## err parfile-$col=$file

3270 [ -z "$file" ] && continue

3271 bin="X'"$(hexize "$file")"'"

3272 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3273 type=\"file:$ct\" ;;

3274 "*"*) continue ;; # foreign table

3275 *) type=\"string\" ;;

3276 esac

3277 case `gettbl_coltype $tbl/$col` in

3278 password) # special care for password

3279 # name={password,pswd1,pswd2}

3280 p1=`getpar pswd1 "$limit"`

3281 if [ -z "$p1" ]; then

3282 continue # SKIP password setting, if p1 is empty

3283 else

3284 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3285 ## err pswd=$pswd

3286 if pwcheck "$pswd"; then

3287 if [ x"$p1" = x"$p2" ]; then

3288 case "$p1" in

3289 ??????????*) ;;

3290 *) echo "パスワードは10字以上にしてください。" | html p

3291 return 6;;

3292 esac

3293 val="\"`echo $p1|mypwhash`\""

3294 else

3295 echo "2つの新パスワード不一致" | html p

3296 return 7

3297 fi

3298 else

3299 echo "旧パスワード違います" | html p

3300 return 8

3301 fi

3302 fi

3303 ;;

3304 esac

3305 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3306 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3307 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3308 if [ x"$bin" = x"NULL" ]; then

3309 ## err Normal-query: `echo $sql`

3310 query "$sql"

3311 else

3312 sqlfile="$tmpd/query.$$"

3313 echo "$sql" > $sqlfile

3314 ## err sqlfile=`ls -lF $sqlfile`

3315 query ".read $sqlfile"

3316 fi

3317 ## err p2t done

3318 done

3319 done

3320 done

3321 return 0

3322 ##err donee

3323 )

3324 genform() {

3325 # $1 = form definition file

3326 # $2, $3 (optional)= table name and ROWID

3327 # If $GF_VIEWONLY set and nonNull, output values without form

3328 # If $GF_ARGS set, use it as content-strings in the form

3329 # If $GF_OWNER set, use it as value of name="owner"

3330 # If $GF_STAGE set, use it as value of name="stage"

3331 forms="" hiddens="" rowid=$3

3332 if [ ! -e "$1" ]; then

3333 echo "そのようなデータベースはないようです($2)。" | html p

3334 return

3335 elif [ -n "$2" ]; then

3336 rec=`query "select * from $2 where rowid='$rowid';"`

3337 if [ -z "$rec" ]; then

3338 pk=`gettblpkey $2`

3339 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3340 rec=`query "select rowid from $2 where $pk='$rowid';"`

3341 rowid=$rec

3342 rec=$3

3343 fi

3344 if [ -z "$rec" ]; then

3345 echo "そんなレコードはないみたいね..." | html p

3346 return

3347 fi

3348 fi

3349 if [ -z "$GF_VIEWONLY" ]; then

3350 rm='<input id="rm" name="rm" type="checkbox"

3351 value="yes"><label for="rm">このエントリの削除</label>

3352 <span>ほんとうに消しますよ(確認)!

3353 <input name="confirm" type=checkbox value="yes">はい</span>'

3354 fi

3355 # Image Cache dir

3356 ## err genform: getcache=$2/$rowid

3357 td=`getcachedir "$2/$rowid"`

3358 while IFS=: read prompt name keytype type args; do

3359 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3360 sp="${args:+ }"

3361 form="" val=""

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

3363 # err genform2a: Seeking for "$2.$name, type=$type"

3364 rawval=`getvalbyid $2 $name $rowid $td`

3365 val=`echo "$rawval"|htmlescape`

3366 ## err genform3a: getvalbyid $2 $name $rowid $td

3367 ## err genform3b: val="[$val]" type="$type"

3368 fi

3369 if [ -n "$GF_VIEWONLY" ]; then

3370 is_hidden "$2" "$name" && continue

3371 fi

3372 case "$type" in

3373 text*)

3374 cgiform=cgi_multi_$type

3375 if [ -s $td/$name.count -a -n "$val" ]; then

3376 form=`$cgiform $name $td`

3377 val=$(echo "$val"|

3378 while read fn; do

3379 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3380 </td></tr>$nl"

3381 done)

3382 val="<table>$nl$val$nl</table>"

3383 else

3384 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3385 form=`cgi_$type $name "$rawval" "$args"`

3386 fi

3387 ;;

3388 [Rr][Aa][Dd][Ii][Oo])

3389 fh="<label><input type=\"radio\" name=\"$name\""

3390 form="`echo $args|sed -e \

3391 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3392 ;;

3393 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3394 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3395 ;;

3396 [Ss][Ee][Ll][Ee][Cc][Tt])

3397 fh="<select name=\"$name\">$nl"

3398 form=$(for l in $args; do

3399 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3400 done)

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

3402 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3403 fi

3404 form="$fh$form</select>"

3405 ;;

3406 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3407 if [ -s $td/$name.count ]; then

3408 form=`cgi_multi_file $name $td "$args"`

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

3410 hrfb="$myname?showattc+$2_m"

3411 val=$(echo "$rawval" \

3412 | while read fn; do

3413 data=`percenthex "$td/$fn"`

3414 #ct=`cat $td/$fn.content-type`

3415 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3416 ri=`cat "$td/$fn.rowid"`

3417 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3418 #imgsrc="<img src=\"data:$ct,$data\">"

3419 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3420 iconhref "$td/$fn" "$hrfb+$ri" ""

3421 done)

3422 fi

3423 else

3424 form="<input type=\"file\" name=\"$name\" $args>"

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

3426 imgs=$(echo "$rawval"\

3427 |while read fn;do

3428 data=`percenthex "$td/$fn"`

3429 echo "<img src=\"data:image/png,$data\">$fn<br>"

3430 done)

3431 form=$form"<br>$imgs"

3432 val=$imgs # 2015-06-15

3433 else

3434 form="<input type=\"file\" name=\"$name\" $args>"

3435 fi

3436 fi

3437 ;;

3438 [Hh][Ii][Dd][Dd][Ee][Nn])

3439 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3440 args="value=\"$GF_STAGE\""

3441 fi

3442 form="<input type=\"hidden\" name=\"$name\" $args>"

3443 prompt='' # Remove prompt

3444 ;;

3445 [Aa][Uu][Tt][Hh][Oo][Rr])

3446 [ -n "$GF_VIEWONLY" ] && continue

3447 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3448 prompt="" ;;

3449 [Oo][Ww][Nn][Ee][Rr])

3450 [ -n "$GF_VIEWONLY" ] && continue

3451 val=${GF_OWNER:-$val}

3452 val=${val:-$user}

3453 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3454 prompt="" ;;

3455 [Uu][Ss][Ee][Rr])

3456 # XXX: is null $user ok?

3457 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3458 [ -n "$GF_VIEWONLY" ] && continue

3459 form="$user"

3460 ;;

3461 [Pp]assword)

3462 [ -n "$GF_VIEWONLY" ] && continue

3463 form="`cgi_passwd`"

3464 val=""

3465 ;;

3466 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3467 [ -n "$GF_VIEWONLY" ] && continue

3468 if [ -z "$rowid" ]; then

3469 val=`genserial`

3470 fi

3471 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3472 prompt="" ;;

3473 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3474 prompt=""

3475 ;;

3476 parent|path|blog*)

3477 prompt=""

3478 ;;

3479 "*"*)

3480 tail=$tail"``"

3481 continue ;;

3482 esac

3483 if [ -n "$prompt" ]; then

3484 if [ -n "${GF_VIEWONLY}" ]; then

3485 form=$val

3486 else

3487 :

3488 fi

3489 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3490 else

3491 hiddens=$hiddens$nl"$form"

3492 fi

3493 done < $1

3494 # enctype="multipart/form-data"

3495 cat<<EOF

3496 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3497 ${rowid:+$rm}

3498 <table class="b $2">

3499 $forms

3500 </table>$hiddens

3501 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3502 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3503 EOF

3504 if [ -z $GF_VIEWONLY ]; then

3505 cat<<EOF

3506 <input type="submit" name="sub" value="OK">

3507 <input type="reset" name="res" value="Reset">

3508 EOF

3509 fi

3510 cat<<EOF

3511 $GF_ARGS</form>

3512 $tail

3513 EOF

3514 }

3515 edittable() {

3516 # $1=form-def $2=table $3 rowid

3517 genform "$@"

3518 }

3519 viewtable() {

3520 GF_VIEWONLY=1 genform "$@"

3521 }

3522 showattc() {

3523 # $1=table_m $2=rowid &optional $3=RawFlag

3524 ## err \$1=$1 \$2=$2 \$3=$3

3525 if ! isfilereadable $user $1 $2; then

3526 contenttype; echo

3527 echo "このファイルは管理者のみしか見られません" | html p

3528 putfooter; exit

3529 fi

3530 idir=`umask 002; mktempd` || exit 1

3531 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3532 bin=$idir/$myname-$$.bin

3533 sql="select quote(bin) from $1 where rowid='$2';"

3534 ## err showattc: sql: $sql

3535 sq $db "$sql" | unhexize > $bin

3536 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3537 type=${tv%//*} fn=${tv#*//}

3538 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3539 ct=${type#file:}

3540 case $ct in # all text/* changed to text/plain

3541 text/*)

3542 charset=`nkf -g $bin|cut -d' ' -f1`

3543 case $charset in

3544 ASCII*) charset="" ;;

3545 esac

3546 if [ -z "$3" ]; then

3547 ct="text/html${charset:+; charset=$charset}"

3548 link="?showattc+$1+$2+raw"

3549 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3550 | sed 's,^,<span></span>,' \

3551 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3552 -D_LINK_="$link" \

3553 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3554 exit $?

3555 fi

3556 ct="text/plain${charset:+; charset=$charset}"

3557 ;;

3558 esac

3559 contenttype "$ct"

3560 echo "Content-Disposition: filename=\"$fn\""

3561 echo "Content-Length: " `cat $bin | wc -c`; echo

3562 #echo "Content-Type: " ${type#file:}; echo

3563 cat $bin

3564 }

3565 #

3566 # Some default stupid handler on CGI values

3567 #

3568 default_storedb() {

3569 # ARG: $1=table-def-file

3570 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3571 tbl=`basename $1`

3572 tbl=${tbl%.def}

3573 cols="`grep :text $1|cut -d: -f2`"

3574 col=`echo "$cols"|head -1`

3575 vcol=`getpar $col`

3576 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3577 if [ -n "$vcol" ]; then

3578 par2table $1

3579 else

3580 return 2 # No insertion occurred

3581 fi

3582 }

3583

3584 default_view() { # $1=def-file

3585 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3586 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3587 default_storedb "$@"

3588 query "select rowid from $tbl order by rowid desc;" \

3589 | while read rowid; do

3590 viewtable $1 $tbl $rowid

3591 done | _m4 -D_TITLE_="$tbl" \

3592 -D_FORM_="`genform $1`" \

3593 -D_DUMPTABLE_="syscmd(cat)" \

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

3595 }

3596 default_viewtext() { # $1=def-file

3597 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3598 default_storedb "$@"

3599 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3600 | _m4 -D_TITLE_="$tbl" \

3601 -D_FORM_="`genform $1`" \

3602 -D_DUMPTABLE_="syscmd(cat)" \

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

3604 }

3605 default_smail() {

3606 default_storedb "$@"

3607 if [ $? -eq 2 ]; then

3608 _m4 -D_TITLE_="入力" \

3609 -D_FORM_="`genform $1`" \

3610 -D_DUMPTABLE_="" \

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

3612 return

3613 fi

3614 cond=""

3615 for pk in `gettblpkey $tbl`; do

3616 pv=$(sqlquote "$(getpar $pk)")

3617 cond="$cond${cond:+ and }$pk=$pv"

3618 done

3619 sql="select rowid from $tbl where $cond;"

3620 rowid=`query "$sql"`

3621 ## err smail1 - "$sql" "-> rowid=$rowid"

3622

3623 while IFS=: read prompt name keytype type args; do # Read from $1

3624 val=`getpar $name`

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

3626 text="$text

3627 $prompt

3628 $name=$val

3629 ---------------------------------------------------------"

3630 fi

3631 case "$type" in

3632 image|document|file)

3633 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3634 fns=$(echo "$fn"|while read fn; do

3635 err mv $tmpd/$fn.orig $tmpd/$fn

3636 mv $tmpd/$fn.orig $tmpd/$fn

3637 rm $tmpd/$fn.rowid # Remove cache flag

3638 ## err "`ls $tmpd/$fn`"

3639 echo $fn

3640 done)

3641 files="$files $fns"

3642 ;;

3643 esac

3644 done < $1

3645 ## err FILES=$files "`ls -lF $tmpd`"

3646 subj="from ${REMOTE_ADDR}"

3647 (echo "$url"

3648 echo "への書き込みがありました。"

3649 echo "------"

3650 echo "$text"

3651 ) | (cd $tmpd &&

3652 err LS="`ls -lF`" &&

3653 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3654 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3655 echo "以下の内容で送信しました。" | html p

3656 viewtable $1 $tbl \

3657 `query "select rowid from $tbl order by rowid desc limit 1;"`

3658 echo "戻る" | html a "href=\"?\""

3659 }