s4: 5e56160ad1f5 s4-funcs.sh

s4

view s4-funcs.sh @ 327:5e56160ad1f5

Use the last profimg as profile image instead of first one.

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 21 Oct 2016 16:26:52 +0859
parents	d19e85a72a29
children	d89412dc78a1

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

35 tconfs=""

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

37 conftbl=_tblconf

38 nl="

39 "

40 iconcachekey="profimgcache_S"

41 . ./s4-cgi.sh

43 : <<EOF

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

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

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

48 レポート提出管理

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

51 [2016]

52 7/12 根本への反省

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

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

56 [以下2015]

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

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

59 Web

60 締切設定

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

63 ○自分の提出物リスト

65 7/19 ○設置

66 ○一斉送信

67 ○getparfilename の tmpd の扱い

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

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

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

71 ○添付ファイル回収

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

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

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

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

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

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

79 ○レポート提出モード

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

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

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

85 7/9 ○管理者の追加

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

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

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

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

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

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

94 ○グループ検索

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

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

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

101

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

103 ○6/1 par2tableを triplex 対応に

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

105 →とすると一気に

106

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

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

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

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

111

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

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

114 ## 1. passwd

115 ## 2. grp

116 ## 3. grp_mem

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

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

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

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

121

122 ## ● listの定義:

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

124 ## ● hashの定義:

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

126

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

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

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

130 ## csv2sq3 で .csv.sq3 の Makefile

131

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

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

134 ## type := root | comment

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

136 ## unique(id, filename)

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

138

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

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

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

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

143 ## できたー!

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

145 ↓

146 ↓以下に変更

147 with recursive allmem as

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

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

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

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

152

153

154 with recursive allmem as

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

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

157 where allmem.val=grp_m.gname)

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

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

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

161

162

163 ## triggerもできた。

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

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

166

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

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

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

170 ## グループ管理

171 ## 個人情報管理

172 ##

173

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

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

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

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

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

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

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

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

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

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

184

185 ## ログテーブル

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

187

188

189

190 ■表設計

191 * 3つの表に分散管理

192 id格納表 + hash表 + list表

193 * *_s *_m

194

195

196

197 user, user_map, user_col

198

199 ■抽象エントリタイプ

200 * user

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

202 * group

203 権限判定に利用

204 * serial

205 自動idとして機能

206 * password

207 入力 type=passwordで入力

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

209 * session

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

211 * text

212 入力 type=text

213 * textarea

214 入力 textarea

215 * image|document

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

217 * owner

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

219 * gowner

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

221 * timestamp

222 datetime()

223 * parent

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

225 * path

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

227

228 格納タイプ

229 * list

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

231 * hash

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

233

234 オブジェクトタイプ

235 * entry

236 id, title, owner

237 * textpart

238 id, parentID, text

239 * binarypart

240 id, parentID, contenttype, filename, content

241 * content

242 hash(textpart), list(binarypart)

243 * topic

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

245 * reply

246 id, parentID, content

247 * blog

248 list(entry)

249 blog = [topic, list(reply)]

250

251

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

253 "text" => "hogehoge ..",

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

255 "author" => "taro",

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

257 "parent" => "/",

258 "path" => "/1",

259 "text" => "blah, blah, ....",

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

261 {"serial" => 2,

262 "author" => "hanako",

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

264 "parent" => "/",

265 "path" => "/2",

266 "text" => "blah, blah, ....",

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

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

269

270

271 user:=

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

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

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

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

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

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

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

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

280

281 変換表

282 /user/email=m

283

284 blog:=

285 シリアル:id:p:serial

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

287 所有者:owner:s:owner:

288 時刻:ctime:s:stamp:

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

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

291

292 article:=

293 シリアル:id:p:serial

294 筆者:author:s:owner

295 時刻:ctime:s:stamp:

296 参照元:parent:s:parent:

297 パス:path:s:path:

298 本文:text:s:textarea:

299 画像:image:m:image:

300

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

302

303

304 EOF

305

306 sq() {

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

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

309 }

310 dbsetup() {

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

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

313 sqi=$tmpdir/sqi.$$

314 sqo=$tmpdir/sqo.$$

315 mkfifo $sqi $sqo

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

317 sq $db < $sqi &

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

319 exec 2>> $tmpdir/error.out

320 exec 3>> $tmpdir/debug.out

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

322 rm $sqi

323 }

324 cleanup() {

325 echo .quit >&5

326 kill $sq3pid

327 kill $sq3pid

328 rm -f $sqo $sqi

329 rm -rf $tmpfiles

330 }

331 query() {

332 echo ".once $sqo" >&5

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

334 cat

335 else

336 echo "$@"

337 fi >&5

338 cat $sqo

339 }

340 _m4() {

341 #_S4NAME_=f,f,f

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

343 }

344 ismember() {

345 # $1=user, $2=group

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

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

348 }

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

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

351 }

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

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

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

355 }

356 isgrpowner() (

357 # $1=user, $2=group

358 gn=`sqlquote "$2"`

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

360 err isgrpowner: $sql

361 test -n "`query $sql`"

362 )

363 getgroupadminmails() {

364 # $1=group

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

366 email4group "$1" "$i" ;

367 done

368 }

369 getgroupadmins() { # $1=group

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

371 qgrp=`sqlquote "$1"`

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

373 }

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

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

376 getvalbyid grp $2 \

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

378 }

379 getgroupbyid() {

380 # $1=id|gname

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

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

383 # err ggbyid: `echo $sql`

384 query $sql

385 }

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

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

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

389

390 # Return true when anonymous mode

391 [ "$anonymousmode" ] && return 0

392 # case `getvalbyid blog mode $2` in

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

394 # *closed*)

395 # owner=`getvalbyid blog owner $2`

396 # if isgrp $owner; then

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

398 # elif isuser $owner; then

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

400 # fi

401 # esac

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

403 sql="with getblog as (\

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

405 select blogid from article where id in\

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

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

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

409 select case\

410 when (select author from article where\

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

412 then 'author'\

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

414 then 'open'\

415 when (select val from getmode) is null \

416 then 'open'

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

418 then (select user from grp_adm where \

419 gname=(select val from getowner) and \

420 user='$1')\

421 when (select author from article where\

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

423 then 'user+author'

424 else '' end;"

425 err isfilereadable: sql="`echo $sql`"

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

427 result=`query "$sql"`

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

429 return 2

430 }

431 linkhome() {

432 # $1=UserOrGroup

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

434 if isuser $1; then

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

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

437 else

438 echo -n "grp+$1"

439 fi

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

441 }

442 hreflink() {

443 # s4 specific notation:

444 # ^href=URL

445 # ^iframe=URL

446 # OSM umap Wikistyle Notation:

447 # [[URL]] - Simle Link

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

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

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

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

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

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

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

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

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

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

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

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

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

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

462 }

463 minitbl() {

464 sed -n '

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

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

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

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

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

470 H; # Concat this line to HoldSpace

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

472 $ b cont

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

474 }

475 :cont

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

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

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

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

480 p; # Print whole "table" element

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

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

483 }

484 x; # Back to the newest line

485 p; # Print rest'

486 }

487 acclog() (

488 # $1=table, $2=rowid

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

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

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

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

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

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

495 fi

496 )

497 gecos() (

498 u=`sqlquote ${1:-$user}`

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

500 )

501 setpar() {

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

503 }

504 replpar() {

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

506 }

507 getpar() {

508 err getpar: "select val from par where var='$1' and sessid='$session' $2;"

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

510 err getpar/val1: "val=[$val]"

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

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

513 fi

514 err getpar/val2: "val=[$val]"

515 case "$var" in

516 owner)

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

518 echo $user; return

519 elif ismember $user $val; then

520 echo $val; return

521 fi ;;

522 esac

523 err getpar/ret: "val=[$val]"

524 echo "$val"

525 }

526

527 getpartype() {

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

529 }

530 getparcount() {

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

532 }

533 getparfilename() {

534 # null if type of $1 is not file

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

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

537 }

538 sqlquote() {

539 (v="$1"

540 case "$v" in

541 "") return ;; # null

542 "X'"*) # quoted hex string

543 echo $1 ;;

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

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

546 echo "\"$v\""

547 return ;;

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

549 echo "\"$v\""

550 return ;;

551 *)

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

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

554 else

555 echo "\"$v\""

556 fi ;;

557 esac)

558 }

559 sqlquotestr() (

560 case "$1" in

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

562 echo "'$v'" ;;

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

564 esac

565 )

566 mktempd() {

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

568 }

569 getcachedir() { # $1=maintable

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

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

572 else

573 echo $tmpd/$thumbxy

574 fi

575 }

576 getval() {

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

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

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

580 echo "$user" ;;

581 stamp|datetime)

582 date "+%F %T" ;;

583 serial)

584 (s=`getpar $2`

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

586 *)

587 getpar "$2" "$3";;

588 esac

589 }

590

591 getvalquote() {

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

593 (v=`getval "$@"`

594 case "$v" in

595 "") echo NULL ;;

596 *) sqlquote "$v" ;;

597 esac)

598 }

599 getparquote() {

600 sqlquote `getpar $1`

601 }

602 getbinbyid() {

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

604

605 }

606 getvalbyid() {

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

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

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

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

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

612

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

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

615 err "select $2 from $1 where rowid=$3"

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

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

618 return

619 fi

620 done

621 rowid=$3

622 pk=`gettblpkey $1`

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

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

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

626 for kt in s m; do

627 td=${4:-$tmpd}

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

629 t=${1}_$kt

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

631 vcount=1 # count(val)

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

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

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

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

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

637 if [ $kt = m ]; then

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

639 # Reset val to store filenames if type is string

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

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

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

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

644 i=0

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

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

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

648 i=$((i+1))

649 fn=$c.$i

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

651 case $type in

652 file:*)

653 #file=$td/$val

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

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

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

657 # FOR SPEED: Skip file generation if imgcache exists

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

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

660 && continue

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

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

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

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

665 select rowid from $cond $slice;

666 .output '$td/$fn'

667 select val from $cond $slice;

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

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

670 .output stdout

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

672 EOF

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

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

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

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

677 ls -lh "$file" |

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

679 case "$type" in

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

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

682 *|*:[Aa]pplication*)

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

684 png:- > "$file"

685 ;;

686 esac

687 ;;

688 *)

689 sq $db<<EOF

690 .output $td/$fn.rowid

691 select rowid from $cond $slice;

692 .output $td/$fn

693 select val from $cond $slice;

694 EOF

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

696 ;;

697 esac

698 done

699 else

700 rm -f $td/$c.count

701 case $type in

702 file:*)

703 echo "$val" \

704 | while read fn; do

705 file=$td/$fn

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

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

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

709 | unhexize > "$file"

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

711 case $type in

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

713 *:[Aa]pplication*)

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

715 png:- > $file ;;

716 esac

717 fi

718 done

719 ;;

720 esac

721 fi

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

723 return

724 fi

725 done

726 done)

727 }

728 getvalbypkey() (

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

730 pk=`gettblpkey $1`

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

732 getvalbyid "$1" "$2" $rowid $4

733 )

734 getvalbycond() {

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

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

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

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

739 getvalbyid "$1" "$2" $rowid "$4"

740 fi

741 }

742 getpwfield() {

743 # getpwfield user column

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

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

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

747 echo "$val"

748 return 0

749 else

750 return 1

751 fi

752 }

753 encode() {

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

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

756 sha1=sha1

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

758 sha1=sha1sum

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

760 sha1=gsha1sum

761 fi

762 fi

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

764 }

765 enjpeg() {

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

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

768 cjpeg="cjpeg"

769 else

770 cjpeg="convert - jpeg:-"

771 fi

772 fi

773 $cjpeg "$@"

774 }

775 mycrypt() (

776 key=$1 salt=$2

777 err \$2=$2

778 case $2 in

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

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

781 esac

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

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

784 )

785 hexize() {

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

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

788 hexize="xxd -p"

789 else

790 hexize_hd() {

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

792 }

793 hexize="hexize_hd"

794 fi

795 fi

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

797 }

798 unhexize() {

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

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

801 unhex="xxd -p -r"

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

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

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

805 EOF

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

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

808 # world writable...:)

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

810 fi

811 fi

812 cat "$@" | $unhex

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

814 }

815 percenthex() {

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

817 }

818 htmlescape() {

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

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

821 }

822 enascii() {

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

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

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

826 else

827 enascii_now=`date +%FT%T`

828 enascii_sed() {

829 nkf -Z0Z1Z2 \

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

831 }

832 enascii="enascii_sed"

833 fi

834 fi

835 cat "$@" | $enascii

836 }

837 size_h() {

838 i="$1" oi=$1

839 set -- B B KB MB GB TB

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

841 oi=$i

842 i=$((i/1024))

843 shift

844 done

845 echo ${oi}$1

846 }

847 gettblconf() {

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

849 ## tconfs=`sq $db \

850 tconfs=`query \

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

852 fi

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

854 }

855 gettblkeys() {

856 # $1=tbl

857 gettblconf

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

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

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

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

862 case $type in

863 *p*)

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

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

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

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

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

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

870 esac

871 case $type in

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

873 esac

874 done

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

876 }

877 gettblpkey() {

878 # $1=tbl

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

880 }

881 gettblfkey() {

882 (x=`gettblkeys $1`

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

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

885 }

886 gettblcols() {

887 (x=`gettblkeys $1`

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

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

890 }

891 gettbl_s_cols() {

892 (x=`gettblkeys $1`

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

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

895 }

896 gettbl_m_cols() {

897 (x=`gettblkeys $1`

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

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

900 }

901 gettbl_h_cols() {

902 (x=`gettblkeys $1`

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

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

905 }

906 gettbl_coltype() (

907 gettblconf

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

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

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

911 )

912 is_hidden() {

913 # $1=Tbl $2=col

914 gettblconf

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

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

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

918 case $x in

919 *h*) return 0 ;;

920 *) return 1 ;;

921 esac

922 }

923

924 dbsetbyid() {

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

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

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

928 unset primary update

929 gettblconf

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

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

932 #err conf=$conf

933 case ${conf#*=} in

934 p*) primary=1 ;;

935 f*) update=1 ;;

936 u*) ;;

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

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

939 esac

940 #err t=$t

941 type=text fn=""

942 case $conf in

943 */password)

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

945 ;;

946 */image*|*/document*)

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

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

949 ;;

950 esac

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

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

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

954 if [ "$primary" ]; then

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

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

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

958 elif [ "$update" ]; then

959 ###sq $db "update $1 set $c=\"$val\" where $pkey=\"$p\""

960 query "update $1 set $c=\"$val\" where $pkey=\"$p\";"

961 else

962 #err "replace into $t values(\"$p\", \"$c\", \"$type\", \"$val\", \"$bin\")"

963 ###sq $db "replace into $t values(\"$p\", \"$c\", \"$type\", \"$val\", \"$bin\")"

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

965 fi

966 )

967 }

968 expire() (

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

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

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

972 )

973 addsession() {

974 # expireをセット

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

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

977 err addsession "$1" exp=$expire

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

979 # Remove old session parameters

980 now=`expire now`

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

982 }

983 gencookie() (

984 for kv; do

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

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

987 done

988 )

989 contenttype() {

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

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

992 }

993 putheader() {

994

995 }

996 putfooter() {

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

998 }

999 getcookie() (

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

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

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

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

1004 done

1005 )

1006 genrandom() {

1007 # $1=columns (default: 10)

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

1009 }

1010 genserial() {

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

1012 }

1013 smail() {

1014 # smail rcpts subj (file)

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

1016 # $MAIL_FROM <- From: header value

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

1018 rcpt=`echo $1` # strip newlines

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

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

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

1022 }

1023 setviastring() {

1024 table=$1

1025 oifs="$IFS"

1026 IFS="&"

1027 for us in $2; do

1028 k=${us%%=*}

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

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

1031 #echo $k=$v

1032 done

1033 IFS="$oifs"

1034 }

1035 checkdomain() (

1036 # Check the validity of domain by referring DNS

1037 item=$1

1038 err checkdomain $1

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

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

1041 )

1042 pwcheck() {

1043 # $1=passwd

1044 dbpswd=`getpwfield $user pswd`

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

1046 err user=$user, pswd=$1, db=$dbpswd, enc=$encpswd

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

1048 }

1049 mypwhash() {

1050 mycrypt `cat` `genrandom 5`

1051 }

1052 wasureta() {

1053 user=$1

1054 if ! checkdomain $user; then

1055 contenttype; echo

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

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

1058 putfooter

1059 exit 0

1060 fi

1061 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1063 encpswd=`echo $newpswd|mypwhash`

1064 dbsetbyid user $user pswd "$encpswd"

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

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

1067 $msgdir/mail-newaccount.m4 \

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

1069 | smail $user "New Account"

1070 }

1071 checkauth() {

1072 user=`getpar user`

1073 skc=`getpar skey` # from cookie

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

1075 skey="`getpwfield $user skey`"

1076 err user=$user skey=$skey

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

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

1079 return 0

1080 fi

1081 fi

1082 pswd=`getpar pswd`

1083 quser=`sqlquotestr "$user"`

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

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

1086 return 1

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

1088 wasureta $user

1089 return 1 # wasureta error

1090 fi

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

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

1093 if pwcheck "$pswd"; then

1094 newsession=`genrandom 50`

1095 dbsetbyid user $user skey "$newsession"

1096 gencookie "user=$user" "skey=$newsession"

1097 return 0

1098 fi

1099 return 2 # Password mismatch

1100 }

1101 showlogin() {

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

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

1104 $layout/login.m4.html

1105 exit 0

1106 }

1107 dologin() {

1108 checkauth

1109 st=$?

1110 if [ $st != 0 ]; then

1111 contenttype; echo

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

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

1114 showlogin # and EXIT

1115 fi

1116 }

1117

1118 # Do instant jobs here

1119 dbsetup

1120 trap cleanup INT HUP EXIT TERM PIPE

1121 # trap cleanup INT HUP

1122

1123 err() {

1124 echo "$@" 1>&3

1125 }

1126

1127 cgiinit() {

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

1129 tmpf=tmp/stream

1130 tmpd=`tmpd=$tmpdir mktempd`

1131 tmpfiles=$tmpfiles" $tmpd"

1132 addsession $session

1133 getcookie

1134 case "$REQUEST_METHOD" in

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

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

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

1138 # cat > $tmpf # too much?

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

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

1141 s="`cat tmp/stream`"

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

1143 ;;

1144 esac

1145 case "$CONTENT_TYPE" in

1146 *boundary*)

1147 bndry=${CONTENT_TYPE#*boundary=}

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

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

1150 do

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

1152 #echo u=$us

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

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

1155 # err k=$k v=$v

1156 case "$k" in

1157 *:filename)

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

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

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

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

1162

1163 fi

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

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

1166 [Ii]mage/x-xcf)

1167 bzip2 "$tmpd/$v"

1168 v=${v}.bz2

1169 ;;

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

1171 [Ii]mage/*)

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

1173 ;;

1174 esac

1175 ;;

1176 *)

1177 type='string'

1178 ;;

1179 esac

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

1181 setpar "$k" "$type" "$v"

1182 done

1183 ;;

1184 *)

1185 setviastring par "$s"

1186 ;;

1187 esac

1188 }

1189 email4group() {

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

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

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

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

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

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

1196 and s.key='email'

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

1198 err `echo $sql`

1199 query "$sql"

1200 }

1201 email4groupbyuid() {

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

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

1204 err em4gbid-IN: "\$1=$1 qgrp=$qgrp"

1205 uids=`echo "$@"`

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

1207 sql="WITH

1208 grpemails AS (

1209 SELECT gname, user, val email

1210 FROM grp_mem NATURAL JOIN grp_mem_s

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

1212 useremails AS (

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

1214 FROM user

1215 LEFT JOIN user_s

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

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

1218 FROM useremails u LEFT JOIN grpemails g

1219 ON u.name=g.user

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

1221 err email4gByid `echo $sql`

1222 query "$sql"

1223 }

1224 collectemail() (

1225 # Collect email addresses for group $1

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

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

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

1229 for e; do

1230 if isuser "$e"; then

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

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

1233 else

1234 qgrp=`sqlquote "$e"`

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

1236 gmem="grp_mem"

1237 else

1238 tm=`sqlquote "$TEAM"`

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

1240 fi

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

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

1243 $gmem g left join grp_mem_s s

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

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

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

1247 err CollectEmail: `echo "$sql"`

1248 query "$sql"

1249 fi

1250 done

1251 )

1252 sendinvitation() (

1253 # $1=email

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

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

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

1257 gecos=`gecos`

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

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

1260 _m4 -D_URL_="$urlbase" \

1261 -D_USER_="$name" \

1262 -D_EMAIL_="$1" \

1263 -D_REGIST_="$regist" \

1264 -D_ADMIN_="$admin" \

1265 $msgdir/mail-invite.m4 \

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

1267 return 0

1268 )

1269 emaildomaincheck() {

1270 case "$1" in

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

1272 *@*)

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

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

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

1276 return 2

1277 fi

1278 return 0

1279 ;;

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

1281 esac

1282 }

1283 invite() {

1284 email=`getpar email`

1285 case $email in

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

1287 *@*)

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

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

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

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

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

1293 elif sendinvitation $email; then

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

1295 fi ;;

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

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

1298 esac

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

1300 err addr=$addr

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

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

1303 fi

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

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

1306 $layout/html.m4.html $layout/invite.m4.html

1307 }

1308 regist() {

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

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

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

1312 echo "bye bye" | html p

1313 reutrn

1314 fi

1315 email=`session=$1 getpar invite`

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

1317 cat<<EOF

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

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

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

1321 EOF

1322 return

1323 fi

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

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

1326 # Fake login password to wasureta

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

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

1329 wasureta $email

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

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

1332 addsession $1 # for removal after 1 minute

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

1334 $layout/login.m4.html

1335 return

1336 }

1337 group_safename() {

1338 # Convert $1 to safe group name

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

1340 }

1341 groupupdate() {

1342 gname=`getpar gname`

1343 qgname=`sqlquote $gname`

1344 err Enter:groupupdate

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

1346 # See ALSO same job in showgroup()

1347 newgname=`group_safename "$gname"`

1348 err newgname=$newgname

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

1350 err NewGNAME: gname=$newgname

1351 gname=$newgname

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

1353 replpar gname string "$gname"

1354 fi

1355 # Name confliction check

1356 parow=`getpar rowid`

1357 err parow=$parow

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

1359 query "BEGIN EXCLUSIVE;"

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

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

1362 if [ $count -gt 0 ]; then

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

1364 query "END;"

1365 return

1366 fi

1367 par2table $formdir/grp.def

1368 query "END TRANSACTION;"

1369 # Remove orphan

1370 : <<EOF

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

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

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

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

1375 EOF

1376 rm=`getpar rm` cfm=`getpar confirm`

1377 err groupupdate:::: after par2tbl rmcfm=$rm$cfm

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

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

1380 sql="delete from blog where id in

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

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

1383 query "$sql";

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

1385 return # and return

1386 fi

1387 fi

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

1389 fi

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

1391 grid=$(query $sql)

1392 err grpupdate:new-grid=$grid, sql=$sql

1393 grp $grid

1394 }

1395 groupman() {

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

1397

1398 GF_STAGE="grpconf"

1399 GF_STAGE=groupupdate

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

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

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

1403 -D_DUMPTABLE_="syscmd(cat)" \

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

1405 }

1406 userconf() {

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

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

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

1410 }

1411 groupconf() {

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

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

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

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

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

1417 err gcon \$1=$1 rowid=$rowid

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

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

1420 }

1421 mems() {

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

1423 kwd=`getpar kwd`

1424 listmember $kwd

1425 }

1426 grps() {

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

1428 kwd=`getpar kwd`

1429 listgroup $kwd \

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

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

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

1433 $layout/form+dump.m4.html

1434 }

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

1436 gpg=`getpar grp`

1437 grid=${1:-$gpg}

1438 grp=`getgroupbyid "$grid"`

1439 err grp: getpar-grp"(gpg)=[$grp]"

1440 ## . ./s4-blog.sh

1441 jg=`getpar joingrp`

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

1443 err jg=$jg, grp=$grp

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

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

1446 fi

1447 htmlheader=$layout/html.m4.html

1448 showgroup "$grid"

1449 }

1450 listnewblogsql() { # $1=user

1451 cat<<EOF

1452 `sql4readableblogs`

1453 with article_ctime as (

1454 select id,blogid,author,val ctime

1455 from article join article_s s using(id)

1456 where s.key='ctime'

1457 ), blog_title_owner as (

1458 select blg.rid brid, id,

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

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

1461 from readableblogs blg, blog_s using(id) group by id

1462 ), acclog_user as (

1463 select * from acclog where user='$user' and tbl='blog'

1464 ), blogall as (

1465 select b.id, brid, b.owner, b.title, ctime, ac.author

1466 from blog_title_owner b, article_ctime ac

1467 on b.id=ac.blogid

1468 )

1469 select brid LINK,

1470 sum(max(coalesce(l.time, "0"),

1471 coalesce((SELECT time FROM acclog_user

1472 WHERE tblrowid=$blogreadflagrowid), "0"))

1473 < ctime

1474 ) "新着",

1475 count(id) "総数", ctime, title,

1476 /* owner gecos */

1477 (select gecos from gecoses where name=bl.owner) gecos

1478 from blogall bl left join acclog_user l

1479 on bl.brid=round(l.tblrowid)

1480 group by bl.id

1481 having "新着" > 0 order by ctime desc, "新着" desc, bl.id

1482 limit 10;

1483 EOF

1484 }

1485

1486 search_form() {

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

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

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

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

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

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

1493 cat<<-EOF

1494 <div class="fr">

1495 <form action="$myname">

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

1497 placeholder=" 全記事からの検索 " width="10">

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

1499 </form>

1500 </div>

1501 EOF

1502 }

1503

1504 imgsrc_cache() (

1505 # $1 = directory for cache'ing

1506 # $2 = table (user_m or grp_m)

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

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

1509 dir="$1" tbl="$2"

1510 keycond="$3"

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

1512 ORDER BY rowid DESC LIMIT 1"

1513 [ -d "$dir" ] || mkdir $dir

1514 case "$4" in

1515 [Ss]) size=S ;;

1516 [Oo]) size=O ;;

1517 *) size=M ;;

1518 esac

1519 # ImageCache filename storing schema:

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

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

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

1523 valtype=`query "$sql0"`

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

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

1526 cacheimg_S=$dir/S_$filename

1527 cacheimg_M=$dir/M_$filename

1528 cacheimg_O=$dir/$filename

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

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

1531 tmpf=$tmpd/imgsrc_cache.$$

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

1533 if test -s "$sumfile" && [ x"`cat $sumfile`" = x"$sum" ]; then

1534 # if cache is fresh,

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

1536 else

1537 fifo=$tmpd/fifo; mkfifo $fifo

1538 fmt=${filename##*.}

1539 ## [[ NOTE ]]

1540 ## a. convert oldimage newimage

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

1542 ## b is much smaller than a

1543 cat $tmpf | unhexize \

1544 | tee $fifo \

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

1546 -resize ${iconxy_M}x${iconxy_M}'>' - pnm:- \

1547 | convert - "$cacheimg_M" &

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

1549 -resize ${iconxy_S}x${iconxy_S}'>' - pnm:- \

1550 | convert - "$cacheimg_S" &

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

1552 cat $tmpf | sed 's/$..$/%\1/g'

1553 echo '">'

1554 echo "$sum" > $sumfile

1555 fi

1556 ## Now preparing cache image, done.

1557 ## Store this information to DB

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

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

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

1561 query <<-EOF

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

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

1564 EOF

1565 )

1566 imgsrc_cache_clear() {

1567 # $1=table

1568 # $2=keycond

1569 query "DELETE FROM $1 WHERE $2 AND key='$iconcachekey';"

1570 }

1571

1572 showhome() {

1573 # $1=userRowIdToShow

1574 err showhome \$1=$1

1575 case "$1" in

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

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

1578 esac

1579 err ShowHome: uname=$uname

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

1581 gecos=`gecos "$uname"`

1582 err SH:gecos=$gecos

1583 GF_VIEWONLY=1

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

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

1586 conflink="<a href=\"?userconf\">プロフィールの編集</a> /

1587 <a href=\"?blog\">新規話題の作成</a>"

1588 # Display folders

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

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

1591 and type like 'file:%';"

1592 err nfile-sql=`echo "$sql"`

1593 nfile=`query "$sql"`

1594 # err nfile=$nfile

1595 if [ $nfile -gt 0 ]; then

1596 conflink="$conflink /

1597 <a href=\"?lsmyfile\">過去の提出ファイル</a>"

1598 fi

1599 fi

1600 . ./s4-blog.sh

1601

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

1603 search_form > $sf

1604 echo "$gecos さん" > $tf

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

1606 viewtable $formdir/user.def user $1

1607 echo "</div>"

1608 } > $pf

1609

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

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

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

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

1614 if true; then

1615 tbl=user_m

1616 enticond="name='$uname'"

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

1618 else

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

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

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

1622 echo '">'

1623 }

1624 fi > $imf

1625 fi

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

1627 val='$uname';"`

1628 listblog $uname > $bf

1629

1630 hometail=$tmpd/tail.$$

1631 mkfifo $hometail

1632

1633 #Calling listgroupbytable, originally here

1634

1635 (

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

1637 # Display Most Recent Entry

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

1639 shortval=${shortval:-val}

1640

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

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

1643 ctime,

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

1645 (SELECT gecos FROM gecoses

1646 WHERE name=(SELECT val FROM blog_s

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

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

1649 FROM blog b

1650 JOIN

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

1652 FROM article a, article_s s

1653 ON a.id=s.id AND a.author='$user' AND s.key='ctime'

1654 GROUP BY blogid ORDER BY val DESC LIMIT 50

1655 ) m

1656 ON b.id=m.blogid;"

1657 # This should be as follows

1658 : <<EOF

1659 WITH arts AS(

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

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

1662 FROM article a NATURAL JOIN article_s s

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

1664 GROUP by s.id

1665 )

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

1667 FROM arts a0

1668 JOIN

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

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

1671 ORDER BY ctime DESC LIMIT 50;

1672 EOF

1673

1674 cat<<-EOF

1675 `cgi_radio foldtabs yes id="mre"`<label

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

1677 <div class="lcto">

1678 `DT_VIEW=replyblog dumptable html blog`

1679 </div>

1680 EOF

1681 unset DT_SQL

1682 # Display NEWS

1683 if false; then

1684 cond="where 新着 > 0 order by 新着 desc,ctime desc limit 10"

1685 new10=`DT_CHLD=article:blogid \

1686 DT_VIEW=replyblog dumptable html blog "ctime title gecos" "$cond"`

1687 else

1688 # 2016-06-26

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

1690 acclog blog $blogreadflagrowid

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

1692 fi

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

1694 sql=`listnewblogsql "$user"`

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

1696 fi

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

1698 cont=$((cont-1))

1699 err newcount=$cont

1700 if [ $cont -gt 0 ]; then

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

1702 cgi_radio foldtabs yes 'id="new10"'

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

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

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

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

1707 `cgi_submit '確定'`

1708 `cgi_hidden read yes`

1709 EOF

1710 echo "$new10 "

1711 echo "</div>"

1712 fi

1713 fi

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

1715 #

1716 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

1722 -D_NBLOG_="$nblog" \

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

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

1725 $layout/html.m4.html $layout/home.m4.html

1726

1727 # Record access log

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

1729 }

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

1731 contenttype; echo

1732 err commission: "$@"

1733 gname=`getgroupbyid $1`

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

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

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

1737 grp_reg_adm "$@"

1738 else

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

1740 fi

1741 }

1742 listgroupbytable() {

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

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

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

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

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

1748 NGsql="select distinct tag from\

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

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

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

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

1753 err ListGRP: query sql="$sql"

1754 for tag in `query "$sql"`

1755 do

1756 err ListGrp: tag=$tag

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

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

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

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

1761 as rowid,

1762 gname,

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

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

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

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

1767 err PersonalGroupList= `echo $sql`

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

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

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

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

1772 echo '</table>'

1773 done

1774 echo '</div>'

1775 }

1776 iconhref() (

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

1778 data=`percenthex "$1"`

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

1780 err iconhref: \$1=$1 \$2=$2 \$3="$@"

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

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

1783 )

1784 iconhref2() (

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

1786 src=$1

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

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

1789 )

1790 listentry() (

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

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

1793 err listentry: \$1=$1 \$2=$2 \$3=$3

1794 cond='' hiddens=''

1795 offset=`getpar offset`

1796 offset=${offset%%[!0-9]*}

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

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

1799 limit=30

1800 dir=`getcachedir "$1"`

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

1802 hrb="$myname?home"

1803 deficon=person-default.png

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

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

1806 gcs=gecos

1807 else # if group

1808 hrb="$myname?grp"

1809 deficon=group-default.png

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

1811 gcs=name

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

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

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

1815 err tagconv=$tagconv

1816 fi

1817 fi

1818 if [ ! -d $dir ]; then

1819 mkdir -p $dir

1820 fi

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

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

1823 fi

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

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

1826 fi

1827

1828 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

1829 # grpは呼出し元の動的スコープ変数でよくないな...

1830 ##qgrp=`sqlquote $grp`

1831 getgrp="(select gname from grp where rowid=${rowid:--1})"

1832 sql="select a.rowid, a.$link,

1833 coalesce(b.$gcs, a.$nm) as nick,

1834 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

1835 || case when a.$nm in (select gname from grp)

1836 then printf('(%d名)',

1837 (select count(user) from grp_mem where gname=a.$nm))

1838 else ' <'||a.$nm||'>'

1839 end

1840 as name,

1841 b.tag,

1842 case when a.$nm in (select user from grp_adm

1843 where gname=$getgrp) then '(管理者)'

1844 when '$user' in (select user from grp_adm where gname=a.$nm)

1845 then '(ADMIN)'

1846 when '$user' in (select user from grp_mem where gname=a.$nm)

1847 then '(Member)'

1848 when '$iamowner' = '' then ''

1849 else ',not='||a.rowid end as ownerlink,

1850 CASE '$entity'

1851 WHEN 'グループ'

1852 THEN coalesce(

1853 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

1854 'open')

1855 ||

1856 CASE WHEN '$user'

1857 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

1858 THEN ' ismember'

1859 ELSE ''

1860 END

1861 ELSE 'user'

1862 END regmode

1863 from $tbl a left join

1864 (select $nm as name,

1865 max(case key when 'gecos' then val end) as gecos,

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

1867 max(case key when 'mtime' then val end) as mtime

1868 from ${tbl}_s group by $nm)

1869 b on a.$nm=b.name $cond $3

1870 order by b.tag desc, b.mtime desc, a.rowid asc"

1871 # Give precedence to newer maintained groups (2016-09-24)

1872 # Note that mtime is stored only in grp_s.

1873 err LE:sql.1="$sql"

1874 total=`query "with x as ($sql) select count(*) from x;"`

1875 echo "${entity} 一覧" | html h2

1876 if [ $total -gt $limit ]; then

1877 echo '<div class="right">'

1878 cgi_form $stage <<EOF

1879 <label>次の語を含む${entity}で検索:

1880 `cgi_text kwd $kwd`</label>

1881 EOF

1882 echo '</div>'

1883 fi

1884 hiddens="$hiddens

1885 `cgi_hidden kwd \"$kwd\"`

1886 `cgi_hidden stage \"$stage\"`"

1887 cat<<EOF

1888 <p>${total}件中の$((offset+1))件めから${kwd:+" - 検索語: $kwd"}</p>

1889 EOF

1890 if [ $((offset+limit)) -lt $total ]; then

1891 nextbtn=$(

1892 cat<<EOF

1893 <div class="right clear"><form action="$myname" method="POST">

1894 `cgi_submit 次の${limit}件`

1895 $hiddens

1896 `cgi_hidden offset $((offset + limit))`</form></div>

1897 EOF

1898 )

1899 fi

1900 if [ $offset -gt 0 ]; then

1901 prevbtn=$(

1902 cat<<EOF

1903 <form action="$myname" method="POST">

1904 `cgi_submit 前の${limit}件`

1905 $hiddens

1906 `cgi_hidden offset $((offset - limit))`</form>

1907 EOF

1908 )

1909 fi

1910 pnbtn="$nextbtn$prevbtn"

1911 echo $pnbtn

1912

1913 err ListEntry: `echo "$sql"\;`

1914 query "$sql limit $limit ${offset:+offset $offset};" \

1915 | while IFS='|' read id lnk name gecos tag ownerp type; do

1916 err name=$name owner=$ownerp lnk=$lnk

1917 err newlnk=$lnk regmode=$regmode

1918 icondir=$dir/$id

1919 files=`getvalbyid $tbl profimg $id $icondir`

1920 # Pick up only first icon

1921 echo "<div class=\"iconlist xy$thumbxy $type\">

1922 <p class=\"tag _$tag\">$tag</p>" \

1923 | _m4 $tagconv

1924 if [ -n "$files" ]; then

1925 icon=`echo "$files"|head -1`

1926 iconhref2 $icondir/$icon "$hrb+$lnk" "$gecos"

1927 else

1928 iconhref $dir/$deficon "$hrb+$lnk" "$gecos"

1929 fi

1930 echo "<br>$name${ownerp:+<br>$ownerp}"

1931 echo "</div>"

1932 done

1933 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

1934 )

1935 listmember() {

1936 listentry user "$@"

1937 }

1938 listgroup() {

1939 listentry group "$@"

1940 }

1941 hexteams() { # $1=gname, $2(optional)=user

1942 cond=${2:+" AND user='$2'"}

1943 query "SELECT DISTINCT hex(val) FROM grp_mem_m

1944 WHERE gname='$1' AND key='team'$cond;"

1945 }

1946 showgroup() { # $1=group-rowid

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

1948 grid=`getpar grid`

1949 grid=${grid%%[!0-9]*}

1950 [ -n "$grid" ] && grp=`getgroupbyid $grid`

1951 else

1952 grid=$1

1953 fi

1954 grp=`getgroupbyid $grid`

1955 qgrp=`sqlquote $grp`

1956 err showgroup2: grp=$grp qgrp="[$(sqlquote $grp)]"

1957 if isgroup "$grp"; then

1958 tf=$tmpd/title.$$

1959 bodyclass=`query "SELECT val FROM grp_s

1960 WHERE gname=$qgrp AND key='regmode';"`

1961 if ismember "$user" "$grp"; then

1962 ismember="ismember"

1963 qgrp=`sqlquote $grp`

1964 bodyclass="$bodyclass${bodyclass:+ }ismember"

1965 else

1966 ismember="" # bodyclass="group"

1967 fi

1968 bodyclass="$bodyclass grouphome"

1969 echo "グループ $grp" > $tf

1970 showgroupsub $formdir/grp.def "$grid" | \

1971 _m4 -D_TITLE_="spaste(\`$tf')" \

1972 -D_FORM_="syscmd(\`cat')" \

1973 -D_BODYCLASS_="$bodyclass" \

1974 -D_DUMPTABLE_="" \

1975 $htmlheader $layout/form+dump.m4.html

1976 # $htmlheader is defined in grp()

1977 else # if $grp is removed at par2table

1978 listgroup

1979 fi

1980 }

1981 showgroupsub() {

1982 # $1=def-file $2=group-rowid

1983 # Using $ismember

1984 rowid=$2

1985 grp=`getgroupbyid $2`

1986 qgrp=`sqlquote $grp`

1987 td=`getcachedir grp/"$2"`

1988 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

1989 if [ -z "$rowid" ]; then

1990 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

1991 #grp=`sq $db "select gname from grp where rowid=$grp"`

1992 echo "showgroupsub: invalid argument($1 $2)" | html p

1993 return

1994 fi

1995 val=`getvalbyid grp profimg $rowid $tmpd`

1996 enticond="gname=$qgrp"

1997 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

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

1999 cat<<-EOF

2000 <p class="groupimg">

2001 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2002 EOF

2003 fi

2004 echo "<div class=\"noprofimg\">"

2005 viewtable $1 grp $rowid

2006 echo "</div>"

2007 if isgrpowner "$user" "$grp"; then

2008 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2009 iamowner=$rowid

2010 fi

2011 if [ -n "$ismember" ]; then

2012 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2013 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2014 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2015 cat<<EOF

2016 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2017 <div class="fold clear">

2018 `cgi_checkbox send yes id="send"`<label

2019 for="send">グループ全員にメッセージ送信</label>

2020 <div>

2021 `cgi_textarea message "" "cols=60"`

2022 `cgi_submit 送信`

2023 `cgi_reset リセット`

2024 </div>

2025 `cgi_hidden grp $rowid`

2026 </div></form>

2027 EOF

2028 fi

2029 # 加入ボタン + 加入者リスト

2030 err ismember $user $grp

2031 if [ -n "$ismember" ]; then

2032 ismem='checked' state="(参加中)"

2033 else

2034 nomem='checked' state="(現在非加入)"

2035 fi

2036 # このグループでの加入アドレス

2037 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2038 and key='email';"`

2039 err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2040 and key='email';"

2041 err email=$eml

2042 cat <<EOF

2043 <div class="fold clear">

2044 `cgi_checkbox reg yes id="reg"`<label

2045 for="reg">自身の加入状態を操作する</label>$state

2046 <div>

2047 EOF

2048 cgi_form grp <<EOF

2049 <p>このグループに</p>

2050 <table class="b">

2051 <tr><th>メンバーとして</th><td>

2052 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2053 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2054 <tr><th>参加する場合のメイルアドレス<br>

2055 <small>(メインのアドレスとは違うものにする場合に記入<br>

2056 同じでよい場合は空欄に)</small></th>

2057 <td>`cgi_text email $eml`</td></tr>

2058 </table>

2059 `cgi_hidden grp $rowid`

2060 EOF

2061 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2062 echo "moderated (承認加入の)グループなので実際に参加できるのは

2063 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2064 fi

2065 echo '</div></div>'

2066 echo '<h2>話題一覧</h2>'

2067 cgi_form searchart<<EOF

2068 <label>`cgi_text kwd`という語を含むコメントを検索</label>

2069 `cgi_hidden owner $grp`

2070 EOF

2071 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2072 DT_CHLD=article:blogid \

2073 DT_VIEW=replyblog dumptable html blog 'ctime team title heading' "$cond"

2074

2075 getgname="(select gname from grp where rowid=$rowid)"

2076 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2077 cm="?commission+$rowid"

2078 thumbxy=50x50 listmember "" "$c" "$rowid" \

2079 |sed -e "s|$<br>$,not=$.*$|\1|" # 間違って押しやすい

2080 # team list

2081 hexteams=`hexteams "$grp"`

2082 if [ -n "$hexteams" ]; then

2083 echo "チーム一覧" | html h2

2084 echo '<div class="dumptable"><table class="b">'

2085 sq $db -html -header<<-EOF

2086 SELECT val TEAM,

2087 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2088 MEMBERS

2089 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2090 EOF

2091 echo '</table></div>'

2092 fi

2093 }

2094 grp_getbodyclass() {

2095 # Get css class name for document.

2096 # `moderated' for moderated groups

2097 # `ismember' for groups where user belongs

2098 # $1=GroupName (w/o quote)

2099 # $user=userNameCurrentlyLogin

2100 err grp_getbodyclass: 1="$1"

2101 qgrp=`sqlquote "$1"`

2102 query<<-EOF

2103 SELECT coalesce(

2104 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2105 'open')

2106 ||

2107 CASE WHEN '$user'

2108 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2109 THEN ' ismember'

2110 ELSE ''

2111 END;

2112 EOF

2113 }

2114 grpaction() { # $1=group-rowid

2115 err GRP_ACTION:IN

2116 grid=${1:-`getpar grp`}

2117 grp=`getgroupbyid "$grid"`

2118 if [ -z "$grp" ]; then

2119 echo "無効な指定です。" | html p; return

2120 fi

2121 if ! ismember $user $grp; then

2122 echo "加入者のみに許可された操作です。" | html p; return

2123 fi

2124 echo "グループ $grp 個別選択操作" \

2125 | _m4 -D_TITLE_="syscmd(\`cat')" \

2126 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2127 $layout/html.m4.html

2128

2129 isowner=""

2130 isgrpowner "$user" "$grp" && isowner="yes"

2131 usel=`getpar usel`

2132 if [ -n "$usel" ]; then

2133 uids=$(echo `echo $usel`|tr ' ' ',')

2134 err grpaction-1: grp=$grp, `echo $sql`

2135 text=`getpar text`

2136

2137 rm=`getpar rm` cfm=`getpar confirm`

2138 err rm=$rm cfm=$cfm

2139 if [ x"$rm" = x"yes" ]; then

2140 if [ "$isowner" ]; then

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

2142 # Eliminate

2143 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2144 for tbl in grp_mem grp_mem_s grp_mem_m; do

2145 sql="delete from $tbl $cond;"

2146 # echo "sql=$sql"

2147 query "$sql"

2148 err rmGRPuser "$sql"

2149 done

2150 num=`query "select count(*) from user where rowid in ($uids);"`

2151 #err num=$num

2152 if [ 0$num -gt 0 ]; then

2153 sql="select coalesce(b.val,a.name) from user a left join \

2154 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2155 # err `echo "$sql"`

2156 html pre<<EOF

2157 以下の${num}名のグループ $grp 登録を解除しました。

2158 `query "$sql"`

2159 EOF

2160 fi

2161 else

2162 echo "確認のチェックがないのでやめておきます。" | html p

2163 return

2164 fi

2165 else # not Group Owner

2166 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2167 return

2168 fi

2169 cat<<EOF

2170

2171 EOF

2172 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2173 if [ -z "$text" ]; then # if msg is empty

2174 echo "なにかメッセージを..." | html p

2175 return 0

2176 fi

2177 gecos=`gecos $user`

2178 mkfrom=`getpar mkfrom`

2179 if [ x"$mkfrom" = x"yes" ]; then

2180 safegc=`echo "$gecos" | tr -d '<>@'`

2181 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2182 fromad=`email4groupbyuid "$grp" "$myuid"`

2183 mail_from="$safegc <$fromad>"

2184 else

2185 mail_from="$admin"

2186 fi

2187 MAIL_FROM=$mail_from \

2188 smail "`email4groupbyuid "$grp" $usel` $user" \

2189 "$gecos さんからのメッセージ" <<EOF

2190 $url

2191 のグループ「$grp」のメンバーである $gecos さんから、

2192 あなた宛へのメッセージです。

2193 ----------------------------------------------------------

2194 $text

2195 EOF

2196 if [ $? = 0 ]; then

2197 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2198 sql="select coalesce(b.val, a.name) from

2199 (select name from user where rowid in ($uids)) a

2200 left join user_s b on a.name=b.name and b.key='gecos';"

2201 html pre<<EOF

2202 `query "$sql"`

2203 (送信者である $gecos さんも含まれます)

2204 EOF

2205 err SendDone: `echo $sql`

2206 fi

2207 elif [ x"$rm" = x"commission" ]; then

2208 grp_reg_adm $grid $usel

2209 elif [ x"$rm" = x"addteam" ]; then

2210 team=`getpar team|sed "s/'/''/g"` # for single quotation

2211 newteam=`echo "$team"|tr -d ,`

2212 if [ x"$team" != x"$newteam" ]; then

2213 echo "チーム名に使えない文字を除去しました" | html p

2214 team=newteam

2215 fi

2216 if [ -z "$team" -o x"$team" = x"なし" ]; then

2217 cat<<-EOF | html p

2218 有効なチーム名を入力してください。

2219 カンマだけ、「なし」という名前は使えません。

2220 EOF

2221 echo "有効なチーム名を入力してください。" | html p

2222 else

2223 grp_add_team $grid "$team" $usel

2224 fi

2225 elif [ x"$rm" = x"rmteam" ]; then

2226 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2227 rmteam=`getpar rmteam|sed "s/'/''/g"`

2228 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2229 gname='$grp' AND user='$user' AND key='team'\

2230 AND val='$rmteam';\"`" ]; then

2231 grp_rm_team $grid "$rmteam" $usel

2232 else

2233 echo "所属していないチームの除去操作はできません。"|html p

2234 fi

2235 else

2236 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2237 fi

2238 fi

2239 fi

2240 # New entry

2241 sql="select /* Ahh, ugly SQL, I wanna fix... */

2242 case

2243 when (select user from grp_adm where

2244 gname=(select gname from grp where rowid=$grid)

2245 and user=a.name) is not null

2246 then 'k'

2247 else ''

2248 end || a.rowid||

2249 ','||a.gecos as NAME,

2250 (SELECT count(author) /* Put post count for scoring 2016-08-01 */

2251 FROM article NATURAL JOIN article_s

2252 WHERE blogid IN

2253 (SELECT id FROM blog_s

2254 WHERE key='owner'

2255 AND val=(SELECT gname FROM grp where rowid=$grid))

2256 AND author=a.name AND key='text')

2257 as POST,

2258 (SELECT group_concat(val, ',')

2259 FROM grp_mem_m

2260 WHERE gname='$grp' AND user=a.name AND key='team') as TEAM

2261 FROM

2262 gecoses a

2263 WHERE name in (select user from grp_mem where

2264 gname=(select gname from grp where rowid=$grid))

2265 ORDER by a.gecos;"

2266 err grpaction: "`echo \"$sql\"`"

2267 tf=$tmpd/title.$$

2268 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2269 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2270 for=\"cmadmin\">グループ管理者委任</label>

2271 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2272 </p></div>"

2273 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2274 for=\"conf\">グループ登録解除</label>

2275 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2276 <p>この操作による通知は本人に行きません。

2277 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2278 </div>"

2279 # Get team list to which current user belongs into $hexteams

2280 myhexteams=$(hexteams "$grp" "$user")

2281 allhexteams=$(hexteams "$grp")

2282 if [ -n "$myhexteams" ]; then

2283 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2284 for=\"cmrmteam\">チーム属性除去</label>

2285 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2286 を除去します: `cgi_checkbox teamconfirm yes` 確認

2287 <p>この操作による通知は本人に行きません。

2288 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2289 </div>

2290 "

2291 fi

2292 b1='<label> <input type="checkbox" name="usel" value="'

2293 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2294 #b2='"> <span>' b3='</span></label>'

2295 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2296 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2297 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2298 cgi_form grpaction<<EOF \

2299 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2300 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2301 | _m4 -D_TITLE_="spaste(\`$tf')" \

2302 -D_SUBTITLE_="チェック後操作ボタン" \

2303 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2304 $layout/form+dump.m4.html

2305 <p>下でチェックした人を対象として:</p>

2306 <div class="foldtabs">

2307 `cgi_radio rm addteam 'id="cmteam"'`<label

2308 for="cmteam">同じチーム属性を付与</label>

2309 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2310 `cgi_datalist_h teams $allhexteams`

2311 </div>

2312 ${rmteammsg}

2313 `cgi_radio rm send id="sendmsg"`<label

2314 for="sendmsg">メッセージ送信</label>

2315 <div>

2316 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2317 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2318 `cgi_textarea text "" cols=40`

2319 </div>

2320 ${isowner:+$cmmsg$excmsg}

2321 `cgi_radio rm close id="x"`<label for="x">×</label>

2322 </div>

2323 <h4>$grp 参加者一覧</h4>

2324 <table class="td2r">

2325 `sq $db -header -html "$sql"`

2326 </table>

2327 `cgi_hidden grp $grid`

2328 EOF

2329 }

2330 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2331 # Create TEMPORARY VIEW

2332 query<<EOF

2333 CREATE TEMPORARY VIEW writeusers AS

2334 SELECT DISTINCT author FROM article

2335 WHERE id in (

2336 select id from article where blogid=(select id from blog where rowid=$1)

2337 );

2338 CREATE TEMPORARY VIEW movablegroups AS

2339 SELECT g.rowid growid , g.gname

2340 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2341 ON grp.gname=gm.gname -- そのユーザが属している

2342 AND user='$user') g -- グループに絞る

2343 WHERE (SELECT author FROM writeusers

2344 EXCEPT

2345 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2346 IS NULL;

2347 $2

2348 EOF

2349 }

2350 sql4readableblogs() {

2351 # Create view of blogs that can be readable to $user

2352 # Blog is readable when:

2353 # 1: blog owner is an user

2354 # 2: else, 2.1: owner-group where the $user belongs

2355 # 2.2: else, owner-group is not moderated

2356 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2357 cat<<EOF ## | tee tmp/sql.out

2358 CREATE TEMPORARY VIEW readableblogs AS

2359 SELECT blog.rowid rid, id, author

2360 FROM blog

2361 NATURAL JOIN

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

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

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

2365 WHEN (SELECT val FROM grp_s

2366 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2367 AND

2368 (SELECT user FROM grp_mem

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

2370 THEN 0

2371 ELSE 1

2372 END;

2373 EOF

2374 }

2375 editheading() { # $1=rowid-of-heading

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

2377 if [ -z "$rowid" ]; then

2378 echo "話題番号が未指定です。" | html p

2379 return

2380 fi

2381 owner=`getvalbyid blog owner $rowid`

2382 title=`getvalbyid blog title $rowid`

2383 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2384 | _m4 -D_TITLE_="修正" \

2385 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2386 -D_BLOGS_="" -D_DUMPTABLE_="" \

2387 -D_FORM_="syscmd(\`cat')" \

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

2389 # Move to group

2390 if isuser "$owner"; then

2391 crview4article $rowid

2392 n=`query "SELECT count(*) FROM writeusers;"`

2393 err N=$n

2394 if [ $((n)) -gt 0 ]; then

2395 err ROWID=$rowid

2396 sql="SELECT growid || ':' || gname FROM movablegroups;"

2397 cat<<-EOF

2398 <div class="fold">

2399 `cgi_checkbox mv send id="mv"`<label

2400 for="mv">この話題をグループ所有に移動する</label>

2401 <div>

2402 <form action="?mvart" method="POST" enctype="multipart/form-data">

2403 移動先グループ:

2404 <select name="mv2grp">

2405 EOF

2406 query ".mode html"

2407 query<<-EOF |

2408 $sql

2409 .mode list

2410 EOF

2411 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2412 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2413 cat<<-EOF

2414 </select>

2415 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2416 そのグループに加入しているものに限られます)</p>

2417 <p>`cgi_checkbox cfm yes`<label>確認

2418 (この操作は元に戻すことができません)</label></p>

2419 `cgi_hidden blogrowid $rowid`

2420 `cgi_submit 移動`

2421 `cgi_reset Reset`

2422 </form>

2423 </div>

2424 </div>

2425 EOF

2426 fi

2427 # end of isuser "$owner"

2428 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2429 [ -n "$hexteams" ];}; then

2430 none="`echo なし|hexize`"

2431 cat<<-EOF

2432 <div class="fold">

2433 `cgi_checkbox mv2team send id="mv2team"`<label

2434 for="mv2team">この話題を以下のチームのものにする</label>

2435 <div><p>現在の所属チーム設定:

2436 `query "SELECT

2437 coalesce((SELECT val FROM blog_s

2438 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2439 AND key='team'),

2440 ':なし');"`</p>

2441 <form action="?mvart" method="POST" enctype="multipart/form-data">

2442 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2443 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2444 `cgi_hidden blogrowid $rowid`<br>

2445 `cgi_submit 移動`

2446 `cgi_reset Reset`

2447 </form></div></div>

2448 EOF

2449 fi

2450 }

2451 mvart() { # move diary to some group or team

2452 # or move blog of group to team which belong to the group

2453 blogrowid=`getpar blogrowid`

2454 cfm=`getpar cfm`

2455 ##### echo move blog:$blogrowid to $mv2grp | html p

2456 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2457 . ./s4-blog.sh

2458 if [ -z "$blogrowid" ]; then

2459 echo "無効な指定です(mvart)。" | html p

2460 return

2461 elif [ x"$cfm" != x"yes" ]; then

2462 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2463 elif { mv2grp=`getpar mv2grp`

2464 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2465 [ -n "$mv2grp" ]; }; then

2466 crview4article $blogrowid

2467 ########## TRANSACTION BEGIN

2468 query "BEGIN;"

2469 n=`query "SELECT count(*) FROM writeusers;"`

2470 err Nwriteuser=$n

2471 if [ $((n)) -gt 0 ]; then

2472 query<<-EOF

2473 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2474 WHERE key='owner'

2475 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2476 AND $mv2grp IN (SELECT growid FROM movablegroups);

2477 EOF

2478 fi

2479 query "END;"

2480 ########## TRANSACTION END

2481 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2482 [ -n "$mv2team" ];}; then

2483 # blog owner can move it to ANY team

2484 case "$mv2team" in

2485 'なし')

2486 cat<<-EOF

2487 DELETE FROM blog_s

2488 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2489 AND key='team';

2490 EOF

2491 ;;

2492 "") ;;

2493 *)cat<<-EOF

2494 BEGIN;

2495 REPLACE INTO blog_s(id, key, val)

2496 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2497 'team', '$mv2team');

2498 REPLACE INTO blog_s(id, key, val)

2499 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2500 'notify', 'all'); -- Change notify to all

2501 END;

2502 EOF

2503 esac | query

2504 fi

2505 blog_reply $blogrowid

2506 echo yes | html p

2507 }

2508 editart() { # $1=article-rowid $2=blogrowid

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

2510 blogrowid=${2%%[!A-Z0-9a-z_]*}

2511 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2512 echo "表示する記事番号が未指定です。" | html p

2513 return

2514 fi

2515 owner=`getvalbyid blog owner $blogrowid`

2516 title=`getvalbyid blog title $blogrowid`

2517 author=`getvalbyid article author $rowid`

2518 err EDITart: owner=$owner, author=$author

2519 if isgrpowner $user $owner; then

2520 : EDIT OK

2521 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2522 echo "本人か所有者しか編集できません." | html p

2523 return

2524 fi

2525 aid=`query "select id from article where rowid=$rowid;"`

2526 tmpout=$tmpd/editart.$$.out

2527 GF_ACTION="?replyblog+$blogrowid#$aid" \

2528 edittable $formdir/article.def article $rowid \

2529 > $tmpout

2530 rm -f /tmp/editart.out

2531 # Cannot use pipelining to m4 with genform() because of stdin stack

2532 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2533 -D_FORM_="syscmd(cat $tmpout)" \

2534 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2535 -D_BLOGS_= -D_DUMPTABLE_= \

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

2537 }

2538 send2mem() {

2539 rowid=`getpar grp`

2540 if [ -z "$rowid" ]; then

2541 echo "グループが未指定です。" | html p

2542 return

2543 fi

2544 message=`getpar message`

2545 if [ -z "$message" ]; then

2546 echo "文章を入れてください。" | html p

2547 return

2548 fi

2549 grp=`getgroupbyid $rowid`

2550 members=`collectemail $grp`

2551 # smail rcpt subj (file)

2552 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2553 $urlbase?grp+$rowid

2554 グループ $grp に所属する

2555 `gecos $user` さんよりメッセージ:

2556

2557 $message

2558 EOF

2559 cat<<EOF

2560 <p>以下の宛先に送信しました。</p>

2561 <pre>

2562 $members

2563 </pre>

2564 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2565 EOF

2566 }

2567 joingrpadmit() {

2568 # $1=yes/no $2=session-key

2569 if [ -z "$2" ]; then

2570 echo "bye bye" | html p; return

2571 fi

2572 t_usr=`session=$2 getpar user`

2573 t_grp=`session=$2 getpar group`

2574 err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2575 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2576 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2577 echo "無効な加入依頼です。" | html p

2578 echo "有効期限が切れたか、

2579 他の管理者がいる場合は処理済みの可能性があります。" | html p

2580 return

2581 fi

2582 if ! isgrpowner "$user" $t_grp; then

2583 echo "グループ管理者のみの機能です。" | html p; return

2584 fi

2585 case $1 in

2586 yes) joingrp "$t_grp" "$t_usr" yes ;;

2587 no) joingrp "$t_grp" "$t_usr" no ;;

2588 *)

2589 echo "無効な指定です($1)。" | html p

2590 return ;;

2591 esac

2592 gid=$(query "select rowid from grp where gname=`sqlquote $t_grp`;")

2593 rcpts="`getgroupadminmails $t_grp` $user"

2594 err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2595 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2596 に

2597 $t_usr

2598 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2599 しました。"

2600 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2601 query "delete from session where id='$2';"

2602 echo "$body" | html p

2603 }

2604

2605 joingrprequest() {

2606 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2607 jss="joingrp-`date +%s`-`genrandom 12`"

2608 addsession $jss +${memoplimitdays}days

2609 query "replace into par values('$jss', 'group', 'string', `sqlquote $1`),

2610 ('$jss', 'user', 'string', `sqlquote $user`);"

2611 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2612 $url

2613 $user さんから

2614 グループ $1

2615 に加入依頼がありました。

2616

2617 承認する:

2618 $urlbase?joingrpadmit+yes+$jss

2619

2620 白紙に戻す:

2621 $urlbase?joingrpadmit+no+$jss

2622 EOF

2623 echo "管理者に加入依頼を出しました。

2624 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2625 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2626 }

2627 joingrp() {

2628 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2629 err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2630 if isgrpowner "$user" "$1"; then

2631 isowner="yes"

2632 elif [ -n "$5" ]; then

2633 isowner="yes"

2634 else

2635 isowner=""

2636 fi

2637 err jg:isgrpowner: isowner="$isowner"

2638 if [ -n "$isowner" ]; then

2639 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2640 elif [ x"$2" != x"$user" ]; then # if user is not login user

2641 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2642 return

2643 elif [ x"$3" = x"no" ]; then

2644 : # Do not pursue those who leave

2645 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2646 : # Member can change own email address for the joining moderated group

2647 else # adding user is $user itself

2648 case `getgroupattr $1 regmode` in

2649 moderated)

2650 joingrprequest "$@" # Request only

2651 return

2652 ;;

2653 *)

2654 ;;

2655 esac

2656 fi

2657 qgname=`sqlquote $1`

2658 cond="where gname=$qgname and user='$2'"

2659 if [ x"$3" = x"yes" ]; then

2660 query "replace into grp_mem values($qgname, '$2');"

2661 if [ -n "$4" ]; then

2662 if msg=`emaildomaincheck "$4"`; then

2663 err "replace into grp_mem_s values($qgname, '$user', 'email', \

2664 'string', '$4', NULL);"

2665 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2666 'string', '$4', NULL);"

2667 else

2668 echo $msg

2669 fi

2670 else

2671 query "delete from grp_mem_s $cond and key='email';"

2672 fi

2673 if [ -n "$5" ]; then # as ADMIN

2674 # Coming here means newly created group

2675 sql="select case\

2676 when (select count(*) from grp_mem where gname=$qgname)=1\

2677 then (select user from grp_mem\

2678 where gname=$qgname and user='$user')\

2679 else '' end; "

2680 err NewGrpChk: $sql

2681 if [ -n "`query \"$sql\"`" ]; then

2682 err ADMIN: "replace into grp_adm values($qgname, '$user');"

2683 query "replace into grp_adm values($qgname, '$user');"

2684 fi

2685 fi

2686 else

2687 query "delete from grp_mem $cond;

2688 delete from grp_mem_s $cond;

2689 delete from grp_mem_m $cond;"

2690 fi

2691 }

2692 grp_add_team() (

2693 # $1=grp-rowid $2=team $3...=user-rowid(s)

2694 grp=`getgroupbyid $1`

2695 team=$2; shift; shift

2696 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2697 { echo "BEGIN;"

2698 for user; do

2699 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2700 '$grp',\

2701 (SELECT name FROM user WHERE rowid=$user),\

2702 'team', 'string', '$team');"

2703 done

2704 echo "END;"

2705 } | query

2706 )

2707 grp_rm_team() (

2708 # $1=grp-rowid $2=team $3...=user-rowid(s)

2709 grid=$1

2710 qgrp=$(sqlquote "`getgroupbyid $grid`")

2711 team=$2; shift; shift

2712 [ -z "$grid" -o -z "$team" ] && return

2713 { echo "BEGIN;"

2714 for user; do

2715 echo "DELETE FROM grp_mem_m\

2716 WHERE gname=$qgrp \

2717 AND user=(SELECT name FROM user WHERE rowid=$user)\

2718 AND key='team' AND val='$team';"

2719 done

2720 cat<<-EOF

2721 DELETE FROM blog_s

2722 WHERE rowid=(

2723 SELECT rowid

2724 FROM blog_s a

2725 WHERE key='team'

2726 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2727 AND NOT EXISTS (SELECT * FROM grp_mem_m

2728 WHERE key='team' AND val=a.val -- a.val=team

2729 AND gname = (SELECT val FROM blog_s b

2730 WHERE a.id=b.id AND key='owner')

2731 ));

2732 EOF

2733

2734 echo "END;"

2735 } | query

2736 )

2737 grp_reg_adm() {

2738 # $1=grp-rowid $2...=user-rowid

2739 grid=$1

2740 grp=`getgroupbyid "$1"`

2741 if [ -z "$grp" ]; then

2742 echo "無効なグループIDです" | html p; return

2743 fi

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

2745 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2746 fi

2747 shift

2748 for urid; do

2749 newadm=`query "select name from user where rowid=$urid;"`

2750 if [ -z "$newadm" ]; then

2751 echo "指定ユーザIDがおかしいようです。" | html p; return

2752 fi

2753 err GRP_reg_adm: "replace into grp_adm values(`sqlquote $grp`, '$newadm');"

2754 err ismember $newadm $grp

2755 if ismember $newadm $grp; then

2756 # OK, go ahead

2757 getgname="(select gname from grp where rowid=$grid)"

2758 query "replace into grp_adm values($getgname, '$newadm');"

2759 # confirm insertion

2760 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2761 if [ -n "`query \"$sql;\"`" ]; then

2762 echo "追加完了: $newadm" | html p

2763 else

2764 echo "追加失敗($1 $urid)" | html p

2765 fi

2766 fi

2767 showgroup $grid

2768 done

2769 }

2770 dumptable() {

2771 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2772 # textのフィールドだけ全てダンプにしたほうがいいか

2773 # $DT_VIEW sets link

2774 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2775 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2776 VIEW=${DT_VIEW-replyblog}

2777 if [ -n "$VIEW" ]; then

2778 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VIE</a><a href=\"$myname?$VIEW+\\2#bottom\">W</a>"

2779 fi

2780 # $DT_CHLD=ChildTable:BindColumn

2781 if [ -n "$DT_CHLD" ]; then

2782 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

2783 cntall="(select count($_i) from $_t where $_i=a.id)"

2784 # XXX: Dirty workaround for slow subquery of acclog

2785 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

2786 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

2787 cntnew="(select count(val) from ${_t}_s where key='ctime' \

2788 and id in (select id from $_t where $_i=a.id) \

2789 and val > coalesce((select time from myacclog where \

2790 tblrowid=a.rowid),\

2791 '1970-01-01'))"

2792 cnt="$cntnew as '新着', $cntall as '総数',"

2793 dt_class=" td2r td3r dumpblogs"

2794 fi

2795 # Construct join expression

2796 eav="" scols=""

2797 pk=`gettblpkey $2`

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

2799 substr=${substr:-val}

2800 for col in ${3:-`gettbl_s_cols $2`}; do

2801 case $col in

2802 gecos) scols="$scols${scols:+, }${col#}"

2803 continue ;; # built-in column name

2804 *:*) col=${col%:*} as=${col#*:} ;;

2805 *) as=${col} ;;

2806 esac

2807 eav=$eav${eav:+,}" max(case key when '$col' then $substr end) as $as"

2808 scols="$scols${scols:+, }b.$as"

2809 done

2810 #case author when '$user' then a.rowid else '---' end as ID,

2811 sql=${DT_SQL:-"select \

2812 a.rowid as LINK,\

2813 $cnt\

2814 $scols from $2 a left join\

2815 (select $pk,$eav,

2816 max(case key when 'owner'

2817 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

2818 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

2819 err SQL="`echo \"$presql$sql\"|tr -d '\n'`"

2820 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>,"

2821 <div>

2822 <div class="dumptable">

2823 <table class="b$dt_class">

2824 `sq -header -cmd ".mode $1" $db "$presql$sql"`

2825 </table>

2826 </div>

2827 </div>

2828 EOF

2829 }

2830

2831 par2table() (

2832 # copy current parameters of par into destination table

2833 # $1=definition-file

2834 # Using $user and $session

2835 # Return value:

2836 # 0: Stored successfully

2837 # 1: Insufficient fillings

2838 # 2: No permission to modify the record

2839 # 3: Invalid rowid

2840 # 4: SUCCESS to delete

2841 # 5: Stop deletion for lack of confirm check

2842 # 6: Password length too short

2843 # 7: Password mismatch

2844 # 8: Old password incorrect

2845 rowid=`getpar rowid`

2846 err ...........rowid=$rowid

2847 if [ ! -e $1 ]; then

2848 echo "テーブル定義ファイルが見付かりません" | html p

2849 exit 1

2850 fi

2851 tbl=${1%.def}

2852 tbl=${tbl##*/}

2853 if [ -n "$rowid" ]; then # Modify existing entry

2854 if [ x"$tbl" = x"user" ]; then

2855 rowowner=`query "select name from $tbl where rowid=$rowid;"`

2856 elif [ x"$tbl" = x"grp" ]; then

2857 sql="select gname from $tbl where rowid=$rowid;"

2858 ##err p2t:grp:q $sql

2859 isgrpowner $user "`query $sql`" && rowowner=$user

2860 else

2861 rowowner=`query "select owner from $tbl where rowid=$rowid;"`

2862 rowowner=${rowowner:-`query "select author from $tbl

2863 where rowid=$rowid;"`}

2864 fi

2865 ### err rowowner=$rowowner

2866 if [ x"$user" != x"$rowowner" ]; then

2867 echo "他人のレコードはいじれないの" | html p

2868 return 2

2869 elif [ -z "$rowowner" ]; then

2870 echo "指定したレコードはないみたい" | html p

2871 return 3

2872 fi

2873 rm=`getpar rm` cfm=`getpar confirm`

2874 # Editing existent entry

2875 if [ x"$rm" = x"yes" ]; then

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

2877 query "delete from $tbl where rowid=$rowid;"

2878 return 4

2879 else

2880 echo "消去確認のチェックがないので消さなかったの..." | html p

2881 return 5

2882 fi

2883 fi

2884 fi

2885

2886 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

2888 # Update of existing record

2889 for col in `gettblcols $tbl`; do

2890 val=`getparquote $col`

2891 [ -z "$val" ] && continue

2892 err query "update $tbl set $col=$val where rowid=$rowid"

2893 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

2894 sql="update $tbl set $col=$val where rowid=$rowid;"

2895 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

2896 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

2897 ## User name cannot be changed with interface provided with this

2898 ## script. But we offer the trigger to change owner user

2899 ## of blog_s table.

2900 err "select quote($col) from $tbl where rowid=$rowid;"

2901 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

2902 cat<<-EOF | query

2903 BEGIN;

2904 $sql

2905 update blog_s set val=$val

2906 where key='owner' and val=$old;

2907 COMMIT;

2908 EOF

2909 ## XX: DIRTY Hack Ends here

2910 ## We should keep blog's owner as a single column which has

2911 ## foreign key constraint with primary key of grp/user.

2912 else

2913 query "$sql"

2914 fi

2915 done

2916 # Then, set up $pval for further insertion of tbl_s and tbl_m

2917 for col in `gettblpkey $tbl`; do

2918 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

2919 pval="$pval${pval:+, }\"$val\""

2920 done

2921 else

2922 # New entry

2923 # Generate values() for primary keys

2924 for col in `gettblpkey $tbl`; do

2925 # Genuine primary keys for _m and _s

2926 val=`getvalquote $tbl $col`

2927 [ -z "$val" ] && continue

2928 pval="$pval${pval:+, }$val"

2929 done

2930 err pval=$pval

2931 for col in `gettblfkey $tbl`; do

2932 # args for values() to insertion into master table

2933 val=`getvalquote $tbl $col`

2934 [ -z "$val" ] && continue

2935 formaster=$formaster"${formaster:+, }$val"

2936 done

2937 formaster="$pval${formaster:+, }$formaster"

2938 err formaster=$formaster

2939 if [ -z "$formaster" ]; then

2940 echo "項目を全て埋めてください" | html pre

2941 return 1

2942 fi

2943 err "replace into $tbl values($formaster);"

2944 query "replace into $tbl values($formaster);"

2945 ## Insertion to master table, done

2946 fi

2947

2948 for kt in s m; do

2949 tb2=${tbl}_$kt

2950 for col in `gettbl_${kt}_cols $tbl`; do

2951 ptype=`getpartype $col "limit 1"`

2952

2953 # First, check update of existing entries in _m

2954 if [ $kt = m ]; then

2955 # sessID|address.1.22|string|Somewhere-x.y.z

2956 sql=""

2957 err dots from query "select var from par where var like '$col.%';"

2958 for v in `query "select var from par where var like '$col.%';"`; do

2959 # v=address.1.22

2960 st_rowid=${v##*.}

2961 origcol=${v%%.*} # original column derived from

2962 err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

2963 ##case `getpartype $v` in

2964 err CASE `gettbl_coltype $tbl/$origcol` in

2965 err edit flag = `getpar action.$v`

2966 case `getpar action.$v` in

2967 rm)

2968 if [ x`getpar confirm.$v` = x"yes" ]; then

2969 newsql="delete from $tb2"

2970 else

2971 echo "削除確認未チェック" | html p

2972 fi ;;

2973 edit)

2974 case `gettbl_coltype $tbl/$origcol` in

2975 image|document|binary)

2976 file=$tmpd/`getparfilename $v`

2977 err type=file=$file

2978 [ -z "$file" ] && continue

2979 bn=`sqlquotestr "${file##*/}"`

2980 bin="X'"$(hexize "$file")"'"

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

2982 type=\"file:$ct\"

2983 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

2984 cachedir=`getcachedir "$tbl/$rowid"`

2985 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

2986 rm -rf $cachedir

2987 ;;

2988 *)

2989 newsql="update $tb2 set val=(select val from par where var \

2990 like '$col.%.$st_rowid')"

2991 ;;

2992 esac

2993 ;;

2994 *) # maybe "keep", do not modify value

2995 continue

2996 ;;

2997 esac

2998 # err newsql=$newsql

2999 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3000 done

3001

3002 if [ x"$bin" = x"NULL" ]; then

3003 err repl:normal sql=`echo $sql`

3004 query "$sql

3005 delete from $tb2 where type='string' and val='';"

3006 err repl:normal done

3007 else

3008 sqlfile="$tmpd/sqlf.$$"

3009 echo "$sql" > $sqlfile

3010 err repl:sqlfile=`ls -lF $sqlfile`

3011 query ".read $sqlfile"

3012 err repl:done

3013 fi

3014 # Rest of kt==m: set multiple mode

3015 nr=`getparcount $col`

3016 else

3017 nr=1 # for kt==s, number of records is 1

3018 fi

3019

3020 i=0

3021 while [ $i -lt $nr ]; do

3022 limit="limit 1 offset $i"

3023 i=$((i+1)) # increase beforehand against continue

3024 val=`getvalquote $tbl $col "$limit"`

3025 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3026 err $col=$val

3027 bin=NULL

3028 err partype$col=`getpartype $col "$limit"`

3029 case $ptype in

3030 file) file=$tmpd/`getparfilename $col "$limit"`

3031 err parfile-$col=$file

3032 [ -z "$file" ] && continue

3033 bin="X'"$(hexize "$file")"'"

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

3035 type=\"file:$ct\" ;;

3036 "*"*) continue ;; # foreign table

3037 *) type=\"string\" ;;

3038 esac

3039 case `gettbl_coltype $tbl/$col` in

3040 password) # special care for password

3041 # name={password,pswd1,pswd2}

3042 p1=`getpar pswd1 "$limit"`

3043 if [ -z "$p1" ]; then

3044 continue # SKIP password setting, if p1 is empty

3045 else

3046 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3047 ## err pswd=$pswd

3048 if pwcheck "$pswd"; then

3049 if [ x"$p1" = x"$p2" ]; then

3050 case "$p1" in

3051 ??????????*) ;;

3052 *) echo "パスワードは10字以上にしてください。" | html p

3053 return 6;;

3054 esac

3055 val="\"`echo $p1|mypwhash`\""

3056 else

3057 echo "2つの新パスワード不一致" | html p

3058 return 7

3059 fi

3060 else

3061 echo "旧パスワード違います" | html p

3062 return 8

3063 fi

3064 fi

3065 ;;

3066 esac

3067 err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3068 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3069 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3070 if [ x"$bin" = x"NULL" ]; then

3071 err Normal-query: `echo $sql`

3072 query "$sql"

3073 else

3074 sqlfile="$tmpd/query.$$"

3075 echo "$sql" > $sqlfile

3076 err sqlfile=`ls -lF $sqlfile`

3077 query ".read $sqlfile"

3078 fi

3079 err p2t done

3080 done

3081 done

3082 done

3083 return 0

3084 ##err donee

3085 )

3086 genform() {

3087 # $1 = form definition file

3088 # $2, $3 (optional)= table name and ROWID

3089 # If $GF_VIEWONLY set and nonNull, output values without form

3090 # If $GF_ARGS set, use it as content-strings in the form

3091 # If $GF_OWNER set, use it as value of name="owner"

3092 # If $GF_STAGE set, use it as value of name="stage"

3093 forms="" hiddens="" rowid=$3

3094 if [ ! -e "$1" ]; then

3095 echo "そのようなデータベースはないようです($2)。" | html p

3096 return

3097 elif [ -n "$2" ]; then

3098 err genform1: "select * from $2 where rowid='$rowid'"

3099 rec=`query "select * from $2 where rowid='$rowid';"`

3100 if [ -z "$rec" ]; then

3101 pk=`gettblpkey $2`

3102 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3103 err "select rowid from $2 where $pk='$rowid';"

3104 rec=`query "select rowid from $2 where $pk='$rowid';"`

3105 err rec-rowid=$rec

3106 rowid=$rec

3107 rec=$3

3108 fi

3109 if [ -z "$rec" ]; then

3110 echo "そんなレコードはないみたいね..." | html p

3111 return

3112 fi

3113 fi

3114 if [ -z "$GF_VIEWONLY" ]; then

3115 rm='<input id="rm" name="rm" type="checkbox"

3116 value="yes"><label for="rm">このエントリの削除</label>

3117 <span>ほんとうに消しますよ(確認)!

3118 <input name="confirm" type=checkbox value="yes">はい</span>'

3119 fi

3120 # Image Cache dir

3121 err genform: getcache=$2/$rowid

3122 td=`getcachedir "$2/$rowid"`

3123 while IFS=: read prompt name keytype type args; do

3124 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3125 sp="${args:+ }"

3126 form="" val=""

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

3128 # err genform2a: Seeking for "$2.$name, type=$type"

3129 rawval=`getvalbyid $2 $name $rowid $td`

3130 val=`echo "$rawval"|htmlescape`

3131 err genform3a: getvalbyid $2 $name $rowid $td

3132 err genform3b: val="[$val]" type="$type"

3133 fi

3134 if [ -n "$GF_VIEWONLY" ]; then

3135 is_hidden "$2" "$name" && continue

3136 fi

3137 case "$type" in

3138 text*)

3139 cgiform=cgi_multi_$type

3140 if [ -s $td/$name.count -a -n "$val" ]; then

3141 form=`$cgiform $name $td`

3142 val=$(echo "$val"|

3143 while read fn; do

3144 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3145 </td></tr>$nl"

3146 done)

3147 val="<table>$nl$val$nl</table>"

3148 else

3149 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3150 err genform: cgi_$type $name $val "$args"

3151 form=`cgi_$type $name "$val" "$args"`

3152 fi

3153 ;;

3154 [Rr][Aa][Dd][Ii][Oo])

3155 fh="<label><input type=\"radio\" name=\"$name\""

3156 form="`echo $args|sed -e \

3157 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3158 ;;

3159 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3160 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3161 ;;

3162 [Ss][Ee][Ll][Ee][Cc][Tt])

3163 fh="<select name=\"$name\">$nl"

3164 form=$(for l in $args; do

3165 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3166 done)

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

3168 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3169 fi

3170 form="$fh$form</select>"

3171 ;;

3172 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3173 if [ -s $td/$name.count ]; then

3174 err Calling cgi_multi_file: "nae=[$name] td=[$td] args=[$args]"

3175 form=`cgi_multi_file $name $td "$args"`

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

3177 hrfb="$myname?showattc+$2_m"

3178 val=$(echo "$rawval" \

3179 | while read fn; do

3180 data=`percenthex "$td/$fn"`

3181 #ct=`cat $td/$fn.content-type`

3182 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3183 ri=`cat "$td/$fn.rowid"`

3184 err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3185 #imgsrc="<img src=\"data:$ct,$data\">"

3186 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3187 iconhref "$td/$fn" "$hrfb+$ri" ""

3188 done)

3189 fi

3190 else

3191 form="<input type=\"file\" name=\"$name\" $args>"

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

3193 imgs=$(echo "$rawval"\

3194 |while read fn;do

3195 data=`percenthex "$td/$fn"`

3196 echo "<img src=\"data:image/png,$data\">$fn<br>"

3197 done)

3198 form=$form"<br>$imgs"

3199 val=$imgs # 2015-06-15

3200 else

3201 form="<input type=\"file\" name=\"$name\" $args>"

3202 fi

3203 fi

3204 ;;

3205 [Hh][Ii][Dd][Dd][Ee][Nn])

3206 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3207 args="value=\"$GF_STAGE\""

3208 fi

3209 form="<input type=\"hidden\" name=\"$name\" $args>"

3210 prompt='' # Remove prompt

3211 ;;

3212 [Aa][Uu][Tt][Hh][Oo][Rr])

3213 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3214 prompt="" ;;

3215 [Oo][Ww][Nn][Ee][Rr])

3216 val=${GF_OWNER:-$val}

3217 val=${val:-$user}

3218 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3219 prompt="" ;;

3220 [Uu][Ss][Ee][Rr])

3221 # XXX: is null $user ok?

3222 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3223 [ -n "$GF_VIEWONLY" ] && continue

3224 form="$user"

3225 ;;

3226 [Pp]assword)

3227 [ -n "$GF_VIEWONLY" ] && continue

3228 form="`cgi_passwd`"

3229 val=""

3230 ;;

3231 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3232 if [ -z "$rowid" ]; then

3233 val=`genserial`

3234 fi

3235 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3236 prompt="" ;;

3237 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3238 prompt=""

3239 ;;

3240 parent|path|blog*)

3241 prompt=""

3242 ;;

3243 "*"*)

3244 tail=$tail"``"

3245 continue ;;

3246 esac

3247 if [ -n "$prompt" ]; then

3248 if [ -n "${GF_VIEWONLY}" ]; then

3249 form=$val

3250 else

3251 :

3252 fi

3253 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3254 else

3255 hiddens=$hiddens$nl"$form"

3256 fi

3257 done < $1

3258 # enctype="multipart/form-data"

3259 cat<<EOF

3260 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3261 ${rowid:+$rm}

3262 <table class="b $2">

3263 $forms

3264 </table>$hiddens

3265 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3266 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3267 EOF

3268 if [ -z $GF_VIEWONLY ]; then

3269 cat<<EOF

3270 <input type="submit" name="sub" value="OK">

3271 <input type="reset" name="res" value="Reset">

3272 EOF

3273 fi

3274 cat<<EOF

3275 $GF_ARGS</form>

3276 $tail

3277 EOF

3278 }

3279 edittable() {

3280 # $1=form-def $2=table $3 rowid

3281 genform "$@"

3282 }

3283 viewtable() {

3284 GF_VIEWONLY=1 genform "$@"

3285 }

3286 showattc() {

3287 # $1=table_m $2=rowid

3288 err \$1=$1 \$2=$2

3289 if ! isfilereadable $user $1 $2; then

3290 contenttype; echo

3291 echo "このファイルは管理者のみしか見られません" | html p

3292 putfooter; exit

3293 fi

3294 idir=`umask 002; mktempd` || exit 1

3295 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3296 bin=$idir/$myname-$$.bin

3297 sql="select quote(bin) from $1 where rowid='$2';"

3298 err showattc: sql: $sql

3299 sq $db "$sql" | unhexize > $bin

3300 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3301 type=${tv%//*} fn=${tv#*//}

3302 err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3303 ct=${type#file:}

3304 case $ct in # all text/* changed to text/plain

3305 text/*)

3306 charset=`nkf -g $bin|cut -d' ' -f1`

3307 case $charset in

3308 ASCII*) charset="" ;;

3309 esac

3310 ct="text/plain${charset:+; charset=$charset}"

3311 ;;

3312 esac

3313 contenttype "$ct"

3314 echo "Content-Disposition: filename=\"$fn\""

3315 echo "Content-Length: " `cat $bin | wc -c`; echo

3316 #echo "Content-Type: " ${type#file:}; echo

3317 cat $bin

3318 }

3319 #

3320 # Some default stupid handler on CGI values

3321 #

3322 default_storedb() {

3323 # ARG: $1=table-def-file

3324 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3325 tbl=`basename $1`

3326 tbl=${tbl%.def}

3327 cols="`grep :text $1|cut -d: -f2`"

3328 col=`echo "$cols"|head -1`

3329 vcol=`getpar $col`

3330 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3331 if [ -n "$vcol" ]; then

3332 par2table $1

3333 else

3334 return 2 # No insertion occurred

3335 fi

3336 }

3337

3338 default_view() { # $1=def-file

3339 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3340 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3341 default_storedb "$@"

3342 query "select rowid from $tbl order by rowid desc;" \

3343 | while read rowid; do

3344 viewtable $1 $tbl $rowid

3345 done | _m4 -D_TITLE_="$tbl" \

3346 -D_FORM_="`genform $1`" \

3347 -D_DUMPTABLE_="syscmd(cat)" \

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

3349 }

3350 default_viewtext() { # $1=def-file

3351 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3352 default_storedb "$@"

3353 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3354 | _m4 -D_TITLE_="$tbl" \

3355 -D_FORM_="`genform $1`" \

3356 -D_DUMPTABLE_="syscmd(cat)" \

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

3358 }

3359 default_smail() {

3360 default_storedb "$@"

3361 if [ $? -eq 2 ]; then

3362 _m4 -D_TITLE_="入力" \

3363 -D_FORM_="`genform $1`" \

3364 -D_DUMPTABLE_="" \

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

3366 return

3367 fi

3368 cond=""

3369 for pk in `gettblpkey $tbl`; do

3370 pv=$(sqlquote $(getpar $pk))

3371 cond="$cond${cond:+ and }$pk=$pv"

3372 done

3373 sql="select rowid from $tbl where $cond;"

3374 rowid=`query "$sql"`

3375 err smail1 - "$sql" "-> rowid=$rowid"

3376

3377 while IFS=: read prompt name keytype type args; do # Read from $1

3378 val=`getpar $name`

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

3380 text="$text

3381 $prompt

3382 $name=$val

3383 ---------------------------------------------------------"

3384 fi

3385 case "$type" in

3386 image|document|file)

3387 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3388 fns=$(echo "$fn"|while read fn; do

3389 err mv $tmpd/$fn.orig $tmpd/$fn

3390 mv $tmpd/$fn.orig $tmpd/$fn

3391 rm $tmpd/$fn.rowid # Remove cache flag

3392 err "`ls $tmpd/$fn`"

3393 echo $fn

3394 done)

3395 files="$files $fns"

3396 ;;

3397 esac

3398 done < $1

3399 err FILES=$files "`ls -lF $tmpd`"

3400 subj="from ${REMOTE_ADDR}"

3401 (echo "$url"

3402 echo "への書き込みがありました。"

3403 echo "------"

3404 echo "$text"

3405 ) | (cd $tmpd &&

3406 err LS="`ls -lF`" &&

3407 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3408 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3409 echo "以下の内容で送信しました。" | html p

3410 viewtable $1 $tbl \

3411 `query "select rowid from $tbl order by rowid desc limit 1;"`

3412 echo "戻る" | html a "href=\"?\""

3413 }