s4: 3dc012eb1ad0 s4-funcs.sh

s4

view s4-funcs.sh @ 482:3dc012eb1ad0

URL pattern fixed to exclude URL example which starts with multibyte

author	HIROSE Yuuji <yuuji@gentei.org>
date	Mon, 15 Jan 2018 09:33:12 +0900
parents	077dc1763239
children	be821d63848c

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 blogcutoffflagrowid=-1

35 querylog=$tmpdir/query.log

37 tconfs=""

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

39 conftbl=_tblconf

40 nl="

41 "

42 iconcachekey="profimgcache_S"

43 case "$HTTP_USER_AGENT" in

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

45 *) touchpanel="" ;;

46 esac

47 . ./s4-cgi.sh

49 : <<EOF

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

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

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

54 レポート提出管理

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

57 [2016]

58 7/12 根本への反省

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

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

62 [以下2015]

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

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

65 Web

66 締切設定

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

69 ○自分の提出物リスト

71 7/19 ○設置

72 ○一斉送信

73 ○getparfilename の tmpd の扱い

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

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

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

77 ○添付ファイル回収

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

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

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

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

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

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

85 ○レポート提出モード

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

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

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

91 7/9 ○管理者の追加

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

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

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

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

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

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

100 ○グループ検索

101

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

103

104

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

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

107

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

109 ○6/1 par2tableを triplex 対応に

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

111 →とすると一気に

112

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

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

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

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

117

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

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

120 ## 1. passwd

121 ## 2. grp

122 ## 3. grp_mem

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

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

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

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

127

128 ## ● listの定義:

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

130 ## ● hashの定義:

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

132

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

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

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

136 ## csv2sq3 で .csv.sq3 の Makefile

137

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

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

140 ## type := root | comment

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

142 ## unique(id, filename)

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

144

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

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

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

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

149 ## できたー!

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

151 ↓

152 ↓以下に変更

153 with recursive allmem as

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

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

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

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

158

159

160 with recursive allmem as

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

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

163 where allmem.val=grp_m.gname)

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

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

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

167

168

169 ## triggerもできた。

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

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

172

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

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

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

176 ## グループ管理

177 ## 個人情報管理

178 ##

179

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

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

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

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

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

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

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

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

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

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

190

191 ## ログテーブル

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

193

194

195

196 ■表設計

197 * 3つの表に分散管理

198 id格納表 + hash表 + list表

199 * *_s *_m

200

201

202

203 user, user_map, user_col

204

205 ■抽象エントリタイプ

206 * user

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

208 * group

209 権限判定に利用

210 * serial

211 自動idとして機能

212 * password

213 入力 type=passwordで入力

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

215 * session

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

217 * text

218 入力 type=text

219 * textarea

220 入力 textarea

221 * image|document

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

223 * owner

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

225 * gowner

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

227 * timestamp

228 datetime()

229 * parent

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

231 * path

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

233

234 格納タイプ

235 * list

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

237 * hash

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

239

240 オブジェクトタイプ

241 * entry

242 id, title, owner

243 * textpart

244 id, parentID, text

245 * binarypart

246 id, parentID, contenttype, filename, content

247 * content

248 hash(textpart), list(binarypart)

249 * topic

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

251 * reply

252 id, parentID, content

253 * blog

254 list(entry)

255 blog = [topic, list(reply)]

256

257

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

259 "text" => "hogehoge ..",

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

261 "author" => "taro",

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

263 "parent" => "/",

264 "path" => "/1",

265 "text" => "blah, blah, ....",

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

267 {"serial" => 2,

268 "author" => "hanako",

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

270 "parent" => "/",

271 "path" => "/2",

272 "text" => "blah, blah, ....",

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

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

275

276

277 user:=

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

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

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

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

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

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

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

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

286

287 変換表

288 /user/email=m

289

290 blog:=

291 シリアル:id:p:serial

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

293 所有者:owner:s:owner:

294 時刻:ctime:s:stamp:

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

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

297

298 article:=

299 シリアル:id:p:serial

300 筆者:author:s:owner

301 時刻:ctime:s:stamp:

302 参照元:parent:s:parent:

303 パス:path:s:path:

304 本文:text:s:textarea:

305 画像:image:m:image:

306

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

308

309

310 EOF

311

312 sq() {

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

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

315 }

316 dbsetup() {

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

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

319 sqi=$tmpdir/sqi.$$

320 sqo=$tmpdir/sqo.$$

321 mkfifo $sqi $sqo

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

323 sq $db < $sqi &

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

325 exec 2>> $tmpdir/error.out

326 exec 3>> $tmpdir/debug.out

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

328 rm $sqi

329 }

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

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

332 }

333 cleanup() {

334 trap '' INT HUP EXIT TERM PIPE

335 echo .quit >&5

336 kill $sq3pid

337 kill $sq3pid

338 rm -f $sqo $sqi

339 rm -rf $tmpfiles

340 cleanup2

341 }

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

343 # simple redirection for the sake of speed.

344 query() {

345 echo ".once $sqo" >&5

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

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

348 tee -a $querylog

349 else

350 echo "$@" >> $querylog

351 echo "$@"

352 fi >&5

353 cat $sqo

354 echo '>>>' >> $querylog

355 }

356 _m4() {

357 #_S4NAME_=f,f,f

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

359 }

360 ismember() {

361 # $1=user, $2=group

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

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

364 }

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

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

367 }

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

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

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

371 }

372 isgrpowner() (

373 # $1=user, $2=group

374 gn=`sqlquote "$2"`

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

376 err isgrpowner: $sql

377 test -n "`query $sql`"

378 )

379 getgroupadminmails() {

380 # $1=group

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

382 email4group "$1" "$i" ;

383 done

384 }

385 getgroupadmins() { # $1=group

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

387 qgrp=`sqlquote "$1"`

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

389 }

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

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

392 getvalbyid grp $2 \

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

394 }

395 getgroupbyid() {

396 # $1=id|gname

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

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

399 # err ggbyid: `echo $sql`

400 query $sql

401 }

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

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

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

405

406 # Return true when anonymous mode

407 [ "$anonymousmode" ] && return 0

408 # case `getvalbyid blog mode $2` in

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

410 # *closed*)

411 # owner=`getvalbyid blog owner $2`

412 # if isgrp $owner; then

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

414 # elif isuser $owner; then

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

416 # fi

417 # esac

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

419 sql="with getblog as (\

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

421 select blogid from article where id in\

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

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

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

425 select case\

426 when (select author from article where\

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

428 then 'author'\

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

430 then 'open'\

431 when (select val from getmode) is null \

432 then 'open'

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

434 then (select user from grp_adm where \

435 gname=(select val from getowner) and \

436 user='$1')\

437 when (select author from article where\

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

439 then 'user+author'

440 else '' end;"

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

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

443 result=`query "$sql"`

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

445 return 2

446 }

447 linkhome() {

448 # $1=UserOrGroup

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

450 if isuser $1; then

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

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

453 else

454 echo -n "grp+$1"

455 fi

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

457 }

458 hreflink() {

459 # s4 specific notation:

460 # ^href=URL

461 # ^iframe=URL

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

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

464 # OSM umap Wikistyle Notation:

465 # [[URL]] - Simle Link

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

486 }

487 minitbl() {

488 sed -n '

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

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

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

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

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

494 H; # Concat this line to HoldSpace

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

496 $ b cont

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

498 }

499 :cont

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

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

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

503 # . of ^. is workaround for FreeBSD sed

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

505 p; # Print whole "table" element

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

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

508 }

509 x; # Back to the newest line

510 p; # Print rest'

511 }

512 acclog() (

513 # $1=table, $2=rowid

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

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

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

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

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

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

520 fi

521 )

522 gecos() (

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

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

525 )

526 setpar() {

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

528 }

529 replpar() {

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

531 }

532 getpar() {

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

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

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

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

537 fi

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

539 case "$var" in

540 owner)

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

542 echo $user; return

543 elif ismember $user $val; then

544 echo $val; return

545 fi ;;

546 esac

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

548 echo "$val"

549 }

550

551 getpartype() {

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

553 }

554 getparcount() {

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

556 }

557 getparfilename() {

558 # null if type of $1 is not file

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

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

561 }

562 sqlquote() {

563 (v="$1"

564 case "$v" in

565 "") return ;; # null

566 "X'"*) # quoted hex string

567 echo $1 ;;

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

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

570 echo "\"$v\""

571 return ;;

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

573 echo "\"$v\""

574 return ;;

575 *)

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

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

578 else

579 echo "\"$v\""

580 fi ;;

581 esac)

582 }

583 sqlquotestr() (

584 case "$1" in

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

586 echo "'$v'" ;;

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

588 esac

589 )

590 mktempd() {

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

592 }

593 getcachedir() { # $1=maintable

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

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

596 else

597 echo $tmpd/$thumbxy

598 fi

599 }

600 getval() {

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

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

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

604 echo "$user" ;;

605 stamp|datetime)

606 date "+%F %T" ;;

607 serial)

608 (s=`getpar $2`

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

610 *)

611 getpar "$2" "$3";;

612 esac

613 }

614

615 getvalquote() {

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

617 (v=`getval "$@"`

618 case "$v" in

619 "") echo NULL ;;

620 *) sqlquote "$v" ;;

621 esac)

622 }

623 getparquote() {

624 sqlquote "`getpar $1`"

625 }

626 getbinbyid() {

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

628

629 }

630 getvalbyid() {

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

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

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

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

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

636

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

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

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

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

641 return

642 fi

643 done

644 rowid=$3

645 pk=`gettblpkey $1`

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

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

648 td=${4:-$tmpd}

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

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

651 for kt in s m; do

652 t=${1}_$kt

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

654 vcount=1 # count(val)

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

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

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

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

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

660 if [ $kt = m ]; then

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

662 # Reset val to store filenames if type is string

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

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

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

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

667 i=0

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

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

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

671 i=$((i+1))

672 fn=$c.$i

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

674 case $type in

675 file:*)

676 #file=$td/$val

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

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

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

680 # FOR SPEED: Skip file generation if imgcache exists

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

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

683 && continue

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

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

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

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

688 select rowid from $cond $slice;

689 .output '$td/$fn'

690 select val from $cond $slice;

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

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

693 .output stdout

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

695 EOF

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

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

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

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

700 ls -lh "$file" |

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

702 case "$type" in

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

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

705 *|*:[Aa]pplication*)

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

707 png:- > "$file"

708 ;;

709 esac

710 ;;

711 *)

712 sq $db<<EOF

713 .output $td/$fn.rowid

714 select rowid from $cond $slice;

715 .output $td/$fn

716 select val from $cond $slice;

717 EOF

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

719 ;;

720 esac

721 done

722 else

723 rm -f $td/$c.count

724 case $type in

725 file:*)

726 echo "$val" \

727 | while read fn; do

728 file=$td/$fn

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

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

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

732 | unhexize > "$file"

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

734 case $type in

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

736 *:[Aa]pplication*)

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

738 png:- > $file ;;

739 esac

740 fi

741 done

742 ;;

743 esac

744 fi

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

746 return

747 fi

748 done

749 done)

750 }

751 getvalbypkey() (

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

753 pk=`gettblpkey $1`

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

755 getvalbyid "$1" "$2" $rowid $4

756 )

757 getvalbycond() {

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

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

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

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

762 getvalbyid "$1" "$2" $rowid "$4"

763 fi

764 }

765 getpwfield() {

766 # getpwfield user column

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

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

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

770 echo "$val"

771 return 0

772 else

773 return 1

774 fi

775 }

776 encode() {

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

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

779 sha1=sha1

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

781 sha1=sha1sum

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

783 sha1=gsha1sum

784 fi

785 fi

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

787 }

788 enjpeg() {

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

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

791 cjpeg="cjpeg"

792 else

793 cjpeg="convert - jpeg:-"

794 fi

795 fi

796 $cjpeg "$@"

797 }

798 mycrypt() (

799 key=$1 salt=$2

800 err \$2=$2

801 case $2 in

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

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

804 esac

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

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

807 )

808 hexize() {

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

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

811 hexize="xxd -p"

812 else

813 hexize_hd() {

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

815 }

816 hexize="hexize_hd"

817 fi

818 fi

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

820 }

821 unhexize() {

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

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

824 unhex="xxd -p -r"

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

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

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

828 EOF

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

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

831 # world writable...:)

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

833 fi

834 fi

835 cat "$@" | $unhex

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

837 }

838 percenthex() {

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

840 }

841 htmlescape() {

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

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

844 }

845 enascii() {

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

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

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

849 else

850 enascii_now=`date +%FT%T`

851 enascii_sed() {

852 nkf -Z0Z1Z2 \

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

854 }

855 enascii="enascii_sed"

856 fi

857 fi

858 cat "$@" | $enascii

859 }

860 size_h() {

861 i="$1" oi=$1

862 set -- B B KB MB GB TB

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

864 oi=$i

865 i=$((i/1024))

866 shift

867 done

868 echo ${oi}$1

869 }

870 gettblconf() {

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

872 ## tconfs=`sq $db \

873 tconfs=`query \

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

875 fi

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

877 }

878 gettblkeys() {

879 # $1=tbl

880 gettblconf

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

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

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

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

885 case $type in

886 *p*)

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

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

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

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

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

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

893 esac

894 case $type in

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

896 esac

897 done

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

899 }

900 gettblpkey() {

901 # $1=tbl

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

903 }

904 gettblfkey() {

905 (x=`gettblkeys $1`

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

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

908 }

909 gettblcols() {

910 (x=`gettblkeys $1`

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

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

913 }

914 gettbl_s_cols() {

915 (x=`gettblkeys $1`

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

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

918 }

919 gettbl_m_cols() {

920 (x=`gettblkeys $1`

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

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

923 }

924 gettbl_h_cols() {

925 (x=`gettblkeys $1`

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

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

928 }

929 gettbl_coltype() (

930 gettblconf

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

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

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

934 )

935 is_hidden() {

936 # $1=Tbl $2=col

937 gettblconf

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

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

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

941 case $x in

942 *h*) return 0 ;;

943 *) return 1 ;;

944 esac

945 }

946

947 dbsetbyid() {

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

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

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

951 quotedp=$(sqlquotestr "$p")

952 unset primary update

953 gettblconf

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

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

956 #err conf=$conf

957 case ${conf#*=} in

958 p*) primary=1 ;;

959 f*) update=1 ;;

960 u*) ;;

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

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

963 esac

964 #err t=$t

965 type=string fn=""

966 case $conf in

967 */password)

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

969 ;;

970 */image*|*/document*)

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

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

973 ;;

974 esac

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

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

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

978 if [ "$primary" ]; then

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

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

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

982 elif [ "$update" ]; then

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

984 else

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

986 fi

987 )

988 }

989 expire() (

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

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

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

993 )

994 addsession() {

995 # expireをセット

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

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

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

999 # Remove old session parameters

1000 now=`expire now`

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

1002 }

1003 gencookie() (

1004 for kv; do

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

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

1007 done

1008 )

1009 contenttype() {

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

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

1012 }

1013 putheader() {

1014

1015 }

1016 putfooter() {

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

1018 }

1019 getcookie() (

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

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

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

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

1024 done

1025 )

1026 genrandom() {

1027 # $1=columns (default: 10)

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

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

1030 }

1031 genserial() {

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

1033 }

1034 smail() {

1035 # smail rcpts subj (file)

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

1037 # $MAIL_FROM <- From: header value

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

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

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

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

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

1043 }

1044 setviastring() {

1045 table=$1

1046 oifs="$IFS"

1047 IFS="&"

1048 for us in $2; do

1049 k=${us%%=*}

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

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

1052 #echo $k=$v

1053 done

1054 IFS="$oifs"

1055 }

1056 checkdomain() (

1057 # Check the validity of domain by referring DNS

1058 item=$1

1059 err checkdomain $1

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

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

1062 )

1063 pwcheck() {

1064 # $1=passwd

1065 dbpswd=`getpwfield $user pswd`

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

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

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

1069 }

1070 mypwhash() {

1071 mycrypt `cat` `genrandom 5`

1072 }

1073 wasureta() {

1074 user=$1

1075 if ! checkdomain $user; then

1076 contenttype; echo

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

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

1079 putfooter

1080 exit 0

1081 fi

1082 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1084 encpswd=`echo $newpswd|mypwhash`

1085 dbsetbyid user $user pswd "$encpswd"

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

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

1088 $msgdir/mail-newaccount.m4 \

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

1090 | smail $user "New Account"

1091 }

1092 checkauth() {

1093 user=`getpar user`

1094 skc=`getpar skey` # from cookie

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

1096 skey="`getpwfield $user skey`"

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

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

1099 return 0

1100 fi

1101 fi

1102 pswd=`getpar pswd`

1103 quser=`sqlquotestr "$user"`

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

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

1106 return 1

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

1108 wasureta $user

1109 return 1 # wasureta error

1110 fi

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

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

1113 if pwcheck "$pswd"; then

1114 newsession=`genrandom 50`

1115 dbsetbyid user $user skey "$newsession"

1116 dbsetbyid user $user login "`date '+%F %T'`"

1117 gencookie "user=$user" "skey=$newsession"

1118 return 0

1119 fi

1120 return 2 # Password mismatch

1121 }

1122 showlogin() {

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

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

1125 $layout/login.m4.html

1126 exit 0

1127 }

1128 dologin() {

1129 checkauth

1130 st=$?

1131 if [ $st != 0 ]; then

1132 contenttype; echo

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

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

1135 showlogin # and EXIT

1136 fi

1137 }

1138

1139 # Do instant jobs here

1140 dbsetup

1141 trap cleanup INT HUP EXIT TERM PIPE

1142 # trap cleanup INT HUP

1143

1144 err() {

1145 echo "$@" 1>&3

1146 }

1147

1148 cgiinit() {

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

1150 tmpf=tmp/stream

1151 tmpd=`tmpd=$tmpdir mktempd`

1152 tmpfiles=$tmpfiles" $tmpd"

1153 addsession $session

1154 getcookie

1155 case "$REQUEST_METHOD" in

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

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

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

1159 # cat > $tmpf # too much?

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

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

1162 s="`cat tmp/stream`"

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

1164 ;;

1165 esac

1166 case "$CONTENT_TYPE" in

1167 *boundary*)

1168 bndry=${CONTENT_TYPE#*boundary=}

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

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

1171 do

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

1173 #echo u=$us

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

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

1176 # err k=$k v=$v

1177 case "$k" in

1178 *:filename)

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

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

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

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

1183 :

1184 fi

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

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

1187 [Ii]mage/x-xcf)

1188 bzip2 "$tmpd/$v"

1189 v=${v}.bz2

1190 ;;

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

1192 [Ii]mage/*)

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

1194 ;;

1195 esac

1196 ;;

1197 *)

1198 type='string'

1199 ;;

1200 esac

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

1202 setpar "$k" "$type" "$v"

1203 done

1204 ;;

1205 *)

1206 setviastring par "$s"

1207 ;;

1208 esac

1209 }

1210 email4group() {

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

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

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

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

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

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

1217 and s.key='email'

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

1219 query "$sql"

1220 }

1221 email4groupbyuid() {

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

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

1224 uids=`echo "$@"`

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

1226 sql="WITH

1227 grpemails AS (

1228 SELECT gname, user, val email

1229 FROM grp_mem NATURAL JOIN grp_mem_s

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

1231 useremails AS (

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

1233 FROM user

1234 LEFT JOIN user_s

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

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

1237 FROM useremails u LEFT JOIN grpemails g

1238 ON u.name=g.user

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

1240 ## err email4gByid `echo $sql`

1241 query "$sql"

1242 }

1243 collectmembersbyid() {

1244 # Collect user names of group specified by grid

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

1246 query "SELECT user FROM grp_mem \

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

1248 }

1249 collectmembersbyid() {

1250 # Collect user names of group name

1251 qgrp=`sqlquote "$1"`

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

1253 }

1254 collectgecosesbyid() {

1255 # Collect user gecoses of group

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

1257 query<<-EOF

1258 SELECT gecos

1259 FROM gecoses

1260 WHERE name IN (SELECT user FROM grp_mem

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

1262 EOF

1263 }

1264 collectemail() (

1265 # Collect email addresses for group $1

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

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

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

1269 for e; do

1270 if isuser "$e"; then

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

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

1273 else

1274 qgrp=`sqlquote "$e"`

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

1276 gmem="grp_mem"

1277 else

1278 tm=`sqlquote "$TEAM"`

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

1280 fi

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

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

1283 $gmem g left join grp_mem_s s

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

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

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

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

1288 query "$sql"

1289 fi

1290 done

1291 )

1292 sendinvitation() (

1293 # $1=email

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

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

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

1297 gecos=`gecos`

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

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

1300 _m4 -D_URL_="$urlbase" \

1301 -D_USER_="$name" \

1302 -D_EMAIL_="$1" \

1303 -D_REGIST_="$regist" \

1304 -D_ADMIN_="$admin" \

1305 $msgdir/mail-invite.m4 \

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

1307 return 0

1308 )

1309 emaildomaincheck() {

1310 case "$1" in

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

1312 *@*)

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

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

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

1316 return 2

1317 fi

1318 return 0

1319 ;;

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

1321 esac

1322 }

1323 invite() {

1324 email=`getpar email`

1325 case $email in

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

1327 *@*)

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

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

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

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

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

1333 elif sendinvitation $email; then

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

1335 fi ;;

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

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

1338 esac

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

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

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

1342 fi

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

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

1345 $layout/html.m4.html $layout/invite.m4.html

1346 }

1347 regist() {

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

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

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

1351 echo "bye bye" | html p

1352 reutrn

1353 fi

1354 email=`session=$1 getpar invite`

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

1356 cat<<EOF

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

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

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

1360 EOF

1361 return

1362 fi

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

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

1365 # Fake login password to wasureta

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

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

1368 wasureta $email

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

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

1371 addsession $1 # for removal after 1 minute

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

1373 $layout/login.m4.html

1374 return

1375 }

1376 group_safename() {

1377 # Convert $1 to safe group name

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

1379 }

1380 groupupdate() {

1381 gname=`getpar gname`

1382 qgname=`sqlquote "$gname"`

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

1384 # See ALSO same job in showgroup()

1385 newgname=`group_safename "$gname"`

1386 err newgname=$newgname

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

1388 err NewGNAME: gname=$newgname

1389 gname=$newgname

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

1391 replpar gname string "$gname"

1392 fi

1393 # Name confliction check

1394 parow=`getpar rowid`

1395 ## err parow=$parow

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

1397 query "BEGIN EXCLUSIVE;"

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

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

1400 if [ $count -gt 0 ]; then

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

1402 query "END;"

1403 return

1404 fi

1405 par2table $formdir/grp.def

1406 query "END TRANSACTION;"

1407 # Remove orphan

1408 : <<EOF

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

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

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

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

1413 EOF

1414 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1418 sql="delete from blog where id in

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

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

1421 query "$sql";

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

1423 return # and return

1424 fi

1425 fi

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

1427 fi

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

1429 grid=$(query $sql)

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

1431 grp $grid

1432 }

1433 groupclone() {

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

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

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

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

1438 return

1439 fi

1440 i=0

1441 while true; do

1442 copy="-copy$i"

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

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

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

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

1447 i=$((i++))

1448 continue

1449 fi

1450 break

1451 done

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

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

1454 query<<-EOF

1455 BEGIN;

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

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

1458 SELECT $newqname, key, val

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

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

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

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

1463 -- Copy members and their configuration --

1464 REPLACE INTO grp_mem SELECT $newqname, user

1465 FROM grp_mem WHERE gname=$qgrp;

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

1467 FROM grp_mem_s WHERE gname=$qgrp;

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

1469 FROM grp_mem_m WHERE gname=$qgrp;

1470 -- Copy administrators --

1471 REPLACE INTO grp_adm SELECT $newqname, user

1472 FROM grp_adm WHERE gname=$qgrp;

1473 COMMIT;

1474 EOF

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

1476 STOPCLONEMSG=1 groupconf "$newrowid"

1477 }

1478 groupman() {

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

1480

1481 GF_STAGE="grpconf"

1482 GF_STAGE=groupupdate

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

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

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

1486 -D_DUMPTABLE_="syscmd(cat)" \

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

1488 }

1489 userconf() {

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

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

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

1493 }

1494 groupconf() {

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

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

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

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

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

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

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

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

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

1504 | html p

1505 fi

1506 }

1507 mems() {

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

1509 kwd=`getpar kwd`

1510 listmember $kwd

1511 }

1512 grps() {

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

1514 kwd=`getpar kwd`

1515 listgroup $kwd \

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

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

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

1519 $layout/form+dump.m4.html

1520 }

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

1522 gpg=`getpar grp`

1523 grid=${1:-$gpg}

1524 grp=`getgroupbyid "$grid"`

1525 ## . ./s4-blog.sh

1526 jg=`getpar joingrp`

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

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

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

1530 fi

1531 htmlheader=$layout/html.m4.html

1532 showgroup "$grid"

1533 }

1534 sql4interestblogs() {

1535 cat<<EOF

1536 CREATE TEMPORARY VIEW interestblogs AS

1537 SELECT blog.rowid rid, id, author

1538 FROM blog

1539 NATURAL JOIN

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

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

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

1543 WHEN (SELECT user FROM grp_mem

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

1545 THEN 0

1546 ELSE 1

1547 END;

1548 EOF

1549 }

1550 listnewblogsql() { # $1=user

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

1552 WHERE user='$user'

1553 AND tblrowid IN

1554 ($blogreadflagrowid,

1555 $blogcutoffflagrowid)),

1556 "0");"`

1557 cat<<EOF

1558 `sql4interestblogs`

1559 WITH article_ctime as (

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

1561 FROM article join article_s s using(id)

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

1563 GROUP BY id

1564 ), blog_title_owner as (

1565 SELECT blg.rid brid, id,

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

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

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

1569 ), blogall as (

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

1571 ), news as (

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

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

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

1575 FROM blogall bl

1576 LEFT JOIN

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

1578 ON bl.brid=al.tblrowid

1579 WHERE atime < bl.ctime

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

1581 LIMIT 10

1582 ) SELECT brid LINK, "新着",

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

1584 ctime, title,

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

1586 FROM news;

1587 EOF

1588 }

1589

1590 search_form() {

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

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

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

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

1595 @week → 最近一週間

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

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

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

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

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

1601 auth=""

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

1603 case "$1" in

1604 author=*)

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

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

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

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

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

1610 ;;

1611 grp=*)

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

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

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

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

1616 ;;

1617 esac

1618 cat<<-EOF

1619 <div class="right">

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

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

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

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

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

1625 </form>

1626 </div>

1627 EOF

1628 }

1629

1630 imgsrc_cache() (

1631 # $1 = directory for cache'ing

1632 # $2 = table (user_m or grp_m)

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

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

1635 dir="$1" tbl="$2"

1636 keycond="$3"

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

1638 ORDER BY rowid DESC LIMIT 1"

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

1640 tmpf=$tmpd/imgsrc_cache.$$

1641 case "$4" in

1642 [Ss]) size=S ;;

1643 [Oo]) size=O ;;

1644 *) size=M ;;

1645 esac

1646 # ImageCache filename storing schema:

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

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

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

1650 valtype=`query "$sql0"`

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

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

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

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

1655 case "$filetype" in

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

1657 esac

1658 fi

1659 cacheimg_S=$dir/S_$filename

1660 cacheimg_M=$dir/M_$filename

1661 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1669 else

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

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

1672 fifo2=$fifo.2

1673 mkfifo $fifo $fifo2

1674 fmt=${filename##*.}

1675 ## [[ NOTE ]]

1676 ## a. convert oldimage newimage

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

1678 ## b is much smaller than a

1679 cat $tmpf | unhexize \

1680 | tee $fifo \

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

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

1683 | tee $fifo2 \

1684 | convert - "$cacheimg_M" &

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

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

1687 | convert - "$cacheimg_S" &

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

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

1690 echo '">'

1691 echo "$sum" > $sumfile

1692 fi

1693 ## Now preparing cache image, done.

1694 ## Store this information to DB

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

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

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

1698 query <<-EOF

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

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

1701 EOF

1702 )

1703

1704 showhome() {

1705 # $1=userRowIdToShow

1706 err showhome \$1=$1

1707 case "$1" in

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

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

1710 esac

1711 ## err ShowHome: uname=$uname

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

1713 gecos=`gecos "$uname"`

1714 ## err SH:gecos=$gecos

1715 GF_VIEWONLY=1

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

1717 search_form_args=""

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

1719 usermenu="<a href=\"?userconf\" accesskey=\"e\"

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

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

1722 # Display folders

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

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

1725 and type like 'file:%';"

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

1727 nfile=`query "$sql"`

1728 # err nfile=$nfile

1729 if [ $nfile -gt 0 ]; then

1730 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

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

1732 fi

1733 else

1734 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

1735 GROUP BY user;"`

1736 usermenu="<p>Last seen on $latestlog</p>"

1737 search_form_args="author=$uname"

1738 fi

1739 . ./s4-blog.sh

1740

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

1742 search_form $search_form_args > $sf

1743 echo "$gecos さん" > $tf

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

1745 viewtable $formdir/user.def user $1

1746 echo "</div>"

1747 } > $pf

1748

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

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

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

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

1753 if true; then

1754 tbl=user_m

1755 enticond="name='$uname'"

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

1757 else

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

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

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

1761 echo '">'

1762 }

1763 fi > $imf

1764 fi

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

1766 val='$uname';"`

1767 listblog $uname > $bf

1768

1769 hometail=$tmpd/tail.$$

1770 mkfifo $hometail

1771

1772 #Calling listgroupbytable, originally here

1773

1774 (

1775 # Display Most Recent Entry

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

1777 shortval=${shortval:-val}

1778

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

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

1781 ctime,

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

1783 (SELECT gecos FROM gecoses

1784 WHERE name=(SELECT val FROM blog_s

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

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

1787 FROM blog b

1788 JOIN

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

1790 FROM article a, article_s s

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

1792 GROUP BY blogid ORDER BY val DESC LIMIT 50

1793 ) m

1794 ON b.id=m.blogid;"

1795 # This should be as follows

1796 : <<EOF

1797 WITH arts AS(

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

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

1800 FROM article a NATURAL JOIN article_s s

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

1802 GROUP by s.id

1803 )

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

1805 FROM arts a0

1806 JOIN

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

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

1809 ORDER BY ctime DESC LIMIT 50;

1810 EOF

1811

1812 cat<<-EOF

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

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

1815 <div class="lcto">

1816 `DT_VIEW=replyblog dumptable html blog`

1817 </div>

1818 EOF

1819 unset DT_SQL

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

1821 # Display NEWS

1822 # 2016-06-26

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

1824 acclog blog $blogreadflagrowid

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

1826 fi

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

1828 sql=`listnewblogsql "$user"`

1829 # echo "$sql" > tmp/listnew

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

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

1832 cont=$((cont-1))

1833 err newcount=$cont

1834 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

1841 `cgi_submit '確定'`

1842 `cgi_hidden read yes`

1843 EOF

1844 echo "$new10 "

1845 echo "</div>"

1846 else # If news is 0, set log cut off flag

1847 acclog blog $blogcutoffflagrowid # for speed

1848 fi

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

1850 : # DT_SQL=

1851 fi

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

1853 #

1854 listgroupbytable $formdir/grp.def $cond |

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

1856 -D_PROFILE_="spaste(\`$pf')$usermenu" \

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

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

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

1860 -D_NBLOG_="$nblog" \

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

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

1863 $layout/html.m4.html $layout/home.m4.html

1864

1865 # Record access log

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

1867 }

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

1869 contenttype; echo

1870 ## err commission: "$@"

1871 gname=`getgroupbyid $1`

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

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

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

1875 grp_reg_adm "$@"

1876 else

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

1878 fi

1879 }

1880 listgroupbytable() {

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

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

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

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

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

1886 NGsql="select distinct tag from\

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

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

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

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

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

1892 for tag in `query "$sql"`

1893 do

1894 ## err ListGrp: tag=$tag

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

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

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

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

1899 as rowid,

1900 gname,

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

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

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

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

1905 ## err PersonalGroupList= `echo $sql`

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

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

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

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

1910 echo '</table>'

1911 done

1912 echo '</div>'

1913 }

1914 iconhref() (

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

1916 data=`percenthex "$1"`

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

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

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

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

1921 )

1922 iconhref2() (

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

1924 src=$1

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

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

1927 )

1928 listentry() (

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

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

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

1932 cond='' hiddens=''

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

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

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

1936 offset=$((offset-1))

1937 fi

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

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

1940 limit=30

1941 dir=`getcachedir "$1"`

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

1943 hrb="$myname?home"

1944 deficon=person-default.png

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

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

1947 gcs=gecos

1948 else # if group

1949 hrb="$myname?grp"

1950 deficon=group-default.png

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

1952 gcs=name

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

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

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

1956 ## err tagconv=$tagconv

1957 fi

1958 fi

1959 if [ ! -d $dir ]; then

1960 mkdir -p $dir

1961 fi

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

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

1964 fi

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

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

1967 fi

1968

1969 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

1970 # grpは呼出し元の動的スコープ変数でよくないな...

1971 ##qgrp=`sqlquote $grp`

1972 getgrp="(select gname from grp where rowid=${rowid:--1})"

1973 sql="select a.rowid, a.$link,

1974 coalesce(b.$gcs, a.$nm) as nick,

1975 quote(a.$nm) as qname,

1976 (SELECT val FROM ${tbl}_s

1977 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

1978 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

1979 || case when a.$nm in (select gname from grp)

1980 then printf('(%d名)',

1981 (select count(user) from grp_mem where gname=a.$nm))

1982 else ' <'||a.$nm||'>'

1983 end

1984 as name,

1985 b.tag,

1986 case when a.$nm in (select user from grp_adm

1987 where gname=$getgrp) then '(管理者)'

1988 when '$user' in (select user from grp_adm where gname=a.$nm)

1989 then '(ADMIN)'

1990 when '$user' in (select user from grp_mem where gname=a.$nm)

1991 then '(Member)'

1992 when '$iamowner' = '' then ''

1993 else ',not='||a.rowid end as ownerlink,

1994 CASE '$entity'

1995 WHEN 'グループ'

1996 THEN coalesce(

1997 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

1998 'open')

1999 ||

2000 CASE WHEN '$user'

2001 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2002 THEN ' ismember'

2003 ELSE ''

2004 END

2005 ELSE 'user'

2006 END regmode

2007 from $tbl a left join

2008 (select $nm as name,

2009 max(case key when 'gecos' then val end) as gecos,

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

2011 max(case key when 'mtime' then val end) as mtime,

2012 max(case key when 'wtime' then val end) as wtime,

2013 max(case key when 'login' then val end) as login

2014 from ${tbl}_s group by $nm)

2015 b on a.$nm=b.name $cond $3

2016 order by b.wtime desc, b.login desc,

2017 b.mtime desc, b.tag 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<br>$mt"

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 iconhref2 "$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 }