s4: d89412dc78a1 s4-funcs.sh

s4

view s4-funcs.sh @ 328:d89412dc78a1

List recent subscriptions in other user's home

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 21 Oct 2016 16:48:37 +0859
parents	5e56160ad1f5
children	e4fedb216119

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 # Display Most Recent Entry

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

1638 shortval=${shortval:-val}

1639

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

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

1642 ctime,

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

1644 (SELECT gecos FROM gecoses

1645 WHERE name=(SELECT val FROM blog_s

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

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

1648 FROM blog b

1649 JOIN

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

1651 FROM article a, article_s s

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

1653 GROUP BY blogid ORDER BY val DESC LIMIT 50

1654 ) m

1655 ON b.id=m.blogid;"

1656 # This should be as follows

1657 : <<EOF

1658 WITH arts AS(

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

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

1661 FROM article a NATURAL JOIN article_s s

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

1663 GROUP by s.id

1664 )

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

1666 FROM arts a0

1667 JOIN

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

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

1670 ORDER BY ctime DESC LIMIT 50;

1671 EOF

1672

1673 cat<<-EOF

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

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

1676 <div class="lcto">

1677 `DT_VIEW=replyblog dumptable html blog`

1678 </div>

1679 EOF

1680 unset DT_SQL

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

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 else # Not My Home ($user != $uname)

1714 # DT_SQL=

1715 fi

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

1717 #

1718 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

1724 -D_NBLOG_="$nblog" \

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

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

1727 $layout/html.m4.html $layout/home.m4.html

1728

1729 # Record access log

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

1731 }

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

1733 contenttype; echo

1734 err commission: "$@"

1735 gname=`getgroupbyid $1`

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

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

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

1739 grp_reg_adm "$@"

1740 else

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

1742 fi

1743 }

1744 listgroupbytable() {

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

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

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

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

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

1750 NGsql="select distinct tag from\

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

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

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

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

1755 err ListGRP: query sql="$sql"

1756 for tag in `query "$sql"`

1757 do

1758 err ListGrp: tag=$tag

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

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

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

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

1763 as rowid,

1764 gname,

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

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

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

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

1769 err PersonalGroupList= `echo $sql`

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

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

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

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

1774 echo '</table>'

1775 done

1776 echo '</div>'

1777 }

1778 iconhref() (

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

1780 data=`percenthex "$1"`

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

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

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

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

1785 )

1786 iconhref2() (

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

1788 src=$1

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

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

1791 )

1792 listentry() (

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

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

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

1796 cond='' hiddens=''

1797 offset=`getpar offset`

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

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

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

1801 limit=30

1802 dir=`getcachedir "$1"`

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

1804 hrb="$myname?home"

1805 deficon=person-default.png

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

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

1808 gcs=gecos

1809 else # if group

1810 hrb="$myname?grp"

1811 deficon=group-default.png

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

1813 gcs=name

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

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

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

1817 err tagconv=$tagconv

1818 fi

1819 fi

1820 if [ ! -d $dir ]; then

1821 mkdir -p $dir

1822 fi

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

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

1825 fi

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

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

1828 fi

1829

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

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

1832 ##qgrp=`sqlquote $grp`

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

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

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

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

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

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

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

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

1841 end

1842 as name,

1843 b.tag,

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

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

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

1847 then '(ADMIN)'

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

1849 then '(Member)'

1850 when '$iamowner' = '' then ''

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

1852 CASE '$entity'

1853 WHEN 'グループ'

1854 THEN coalesce(

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

1856 'open')

1857 ||

1858 CASE WHEN '$user'

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

1860 THEN ' ismember'

1861 ELSE ''

1862 END

1863 ELSE 'user'

1864 END regmode

1865 from $tbl a left join

1866 (select $nm as name,

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

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

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

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

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

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

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

1874 # Note that mtime is stored only in grp_s.

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

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

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

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

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

1880 cgi_form $stage <<EOF

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

1882 `cgi_text kwd $kwd`</label>

1883 EOF

1884 echo '</div>'

1885 fi

1886 hiddens="$hiddens

1887 `cgi_hidden kwd \"$kwd\"`

1888 `cgi_hidden stage \"$stage\"`"

1889 cat<<EOF

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

1891 EOF

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

1893 nextbtn=$(

1894 cat<<EOF

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

1896 `cgi_submit 次の${limit}件`

1897 $hiddens

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

1899 EOF

1900 )

1901 fi

1902 if [ $offset -gt 0 ]; then

1903 prevbtn=$(

1904 cat<<EOF

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

1906 `cgi_submit 前の${limit}件`

1907 $hiddens

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

1909 EOF

1910 )

1911 fi

1912 pnbtn="$nextbtn$prevbtn"

1913 echo $pnbtn

1914

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

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

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

1918 err name=$name owner=$ownerp lnk=$lnk

1919 err newlnk=$lnk regmode=$regmode

1920 icondir=$dir/$id

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

1922 # Pick up only first icon

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

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

1925 | _m4 $tagconv

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

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

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

1929 else

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

1931 fi

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

1933 echo "</div>"

1934 done

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

1936 )

1937 listmember() {

1938 listentry user "$@"

1939 }

1940 listgroup() {

1941 listentry group "$@"

1942 }

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

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

1945 query "SELECT DISTINCT hex(val) FROM grp_mem_m

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

1947 }

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

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

1950 grid=`getpar grid`

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

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

1953 else

1954 grid=$1

1955 fi

1956 grp=`getgroupbyid $grid`

1957 qgrp=`sqlquote $grp`

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

1959 if isgroup "$grp"; then

1960 tf=$tmpd/title.$$

1961 bodyclass=`query "SELECT val FROM grp_s

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

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

1964 ismember="ismember"

1965 qgrp=`sqlquote $grp`

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

1967 else

1968 ismember="" # bodyclass="group"

1969 fi

1970 bodyclass="$bodyclass grouphome"

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

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

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

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

1975 -D_BODYCLASS_="$bodyclass" \

1976 -D_DUMPTABLE_="" \

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

1978 # $htmlheader is defined in grp()

1979 else # if $grp is removed at par2table

1980 listgroup

1981 fi

1982 }

1983 showgroupsub() {

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

1985 # Using $ismember

1986 rowid=$2

1987 grp=`getgroupbyid $2`

1988 qgrp=`sqlquote $grp`

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

1990 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

1991 if [ -z "$rowid" ]; then

1992 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

1993 #grp=`sq $db "select gname from grp where rowid=$grp"`

1994 echo "showgroupsub: invalid argument($1 $2)" | html p

1995 return

1996 fi

1997 val=`getvalbyid grp profimg $rowid $tmpd`

1998 enticond="gname=$qgrp"

1999 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

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

2001 cat<<-EOF

2002 <p class="groupimg">

2003 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2004 EOF

2005 fi

2006 echo "<div class=\"noprofimg\">"

2007 viewtable $1 grp $rowid

2008 echo "</div>"

2009 if isgrpowner "$user" "$grp"; then

2010 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2011 iamowner=$rowid

2012 fi

2013 if [ -n "$ismember" ]; then

2014 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2015 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2016 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2017 cat<<EOF

2018 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2019 <div class="fold clear">

2020 `cgi_checkbox send yes id="send"`<label

2021 for="send">グループ全員にメッセージ送信</label>

2022 <div>

2023 `cgi_textarea message "" "cols=60"`

2024 `cgi_submit 送信`

2025 `cgi_reset リセット`

2026 </div>

2027 `cgi_hidden grp $rowid`

2028 </div></form>

2029 EOF

2030 fi

2031 # 加入ボタン + 加入者リスト

2032 err ismember $user $grp

2033 if [ -n "$ismember" ]; then

2034 ismem='checked' state="(参加中)"

2035 else

2036 nomem='checked' state="(現在非加入)"

2037 fi

2038 # このグループでの加入アドレス

2039 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2040 and key='email';"`

2041 err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2042 and key='email';"

2043 err email=$eml

2044 cat <<EOF

2045 <div class="fold clear">

2046 `cgi_checkbox reg yes id="reg"`<label

2047 for="reg">自身の加入状態を操作する</label>$state

2048 <div>

2049 EOF

2050 cgi_form grp <<EOF

2051 <p>このグループに</p>

2052 <table class="b">

2053 <tr><th>メンバーとして</th><td>

2054 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2055 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2056 <tr><th>参加する場合のメイルアドレス<br>

2057 <small>(メインのアドレスとは違うものにする場合に記入<br>

2058 同じでよい場合は空欄に)</small></th>

2059 <td>`cgi_text email $eml`</td></tr>

2060 </table>

2061 `cgi_hidden grp $rowid`

2062 EOF

2063 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2064 echo "moderated (承認加入の)グループなので実際に参加できるのは

2065 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2066 fi

2067 echo '</div></div>'

2068 echo '<h2>話題一覧</h2>'

2069 cgi_form searchart<<EOF

2070 <label>`cgi_text kwd`という語を含むコメントを検索</label>

2071 `cgi_hidden owner $grp`

2072 EOF

2073 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2074 DT_CHLD=article:blogid \

2075 DT_VIEW=replyblog dumptable html blog 'ctime team title heading' "$cond"

2076

2077 getgname="(select gname from grp where rowid=$rowid)"

2078 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2079 cm="?commission+$rowid"

2080 thumbxy=50x50 listmember "" "$c" "$rowid" \

2081 |sed -e "s|$<br>$,not=$.*$|\1|" # 間違って押しやすい

2082 # team list

2083 hexteams=`hexteams "$grp"`

2084 if [ -n "$hexteams" ]; then

2085 echo "チーム一覧" | html h2

2086 echo '<div class="dumptable"><table class="b">'

2087 sq $db -html -header<<-EOF

2088 SELECT val TEAM,

2089 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2090 MEMBERS

2091 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2092 EOF

2093 echo '</table></div>'

2094 fi

2095 }

2096 grp_getbodyclass() {

2097 # Get css class name for document.

2098 # `moderated' for moderated groups

2099 # `ismember' for groups where user belongs

2100 # $1=GroupName (w/o quote)

2101 # $user=userNameCurrentlyLogin

2102 err grp_getbodyclass: 1="$1"

2103 qgrp=`sqlquote "$1"`

2104 query<<-EOF

2105 SELECT coalesce(

2106 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2107 'open')

2108 ||

2109 CASE WHEN '$user'

2110 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2111 THEN ' ismember'

2112 ELSE ''

2113 END;

2114 EOF

2115 }

2116 grpaction() { # $1=group-rowid

2117 err GRP_ACTION:IN

2118 grid=${1:-`getpar grp`}

2119 grp=`getgroupbyid "$grid"`

2120 if [ -z "$grp" ]; then

2121 echo "無効な指定です。" | html p; return

2122 fi

2123 if ! ismember $user $grp; then

2124 echo "加入者のみに許可された操作です。" | html p; return

2125 fi

2126 echo "グループ $grp 個別選択操作" \

2127 | _m4 -D_TITLE_="syscmd(\`cat')" \

2128 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2129 $layout/html.m4.html

2130

2131 isowner=""

2132 isgrpowner "$user" "$grp" && isowner="yes"

2133 usel=`getpar usel`

2134 if [ -n "$usel" ]; then

2135 uids=$(echo `echo $usel`|tr ' ' ',')

2136 err grpaction-1: grp=$grp, `echo $sql`

2137 text=`getpar text`

2138

2139 rm=`getpar rm` cfm=`getpar confirm`

2140 err rm=$rm cfm=$cfm

2141 if [ x"$rm" = x"yes" ]; then

2142 if [ "$isowner" ]; then

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

2144 # Eliminate

2145 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2146 for tbl in grp_mem grp_mem_s grp_mem_m; do

2147 sql="delete from $tbl $cond;"

2148 # echo "sql=$sql"

2149 query "$sql"

2150 err rmGRPuser "$sql"

2151 done

2152 num=`query "select count(*) from user where rowid in ($uids);"`

2153 #err num=$num

2154 if [ 0$num -gt 0 ]; then

2155 sql="select coalesce(b.val,a.name) from user a left join \

2156 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2157 # err `echo "$sql"`

2158 html pre<<EOF

2159 以下の${num}名のグループ $grp 登録を解除しました。

2160 `query "$sql"`

2161 EOF

2162 fi

2163 else

2164 echo "確認のチェックがないのでやめておきます。" | html p

2165 return

2166 fi

2167 else # not Group Owner

2168 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2169 return

2170 fi

2171 cat<<EOF

2172

2173 EOF

2174 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2175 if [ -z "$text" ]; then # if msg is empty

2176 echo "なにかメッセージを..." | html p

2177 return 0

2178 fi

2179 gecos=`gecos $user`

2180 mkfrom=`getpar mkfrom`

2181 if [ x"$mkfrom" = x"yes" ]; then

2182 safegc=`echo "$gecos" | tr -d '<>@'`

2183 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2184 fromad=`email4groupbyuid "$grp" "$myuid"`

2185 mail_from="$safegc <$fromad>"

2186 else

2187 mail_from="$admin"

2188 fi

2189 MAIL_FROM=$mail_from \

2190 smail "`email4groupbyuid "$grp" $usel` $user" \

2191 "$gecos さんからのメッセージ" <<EOF

2192 $url

2193 のグループ「$grp」のメンバーである $gecos さんから、

2194 あなた宛へのメッセージです。

2195 ----------------------------------------------------------

2196 $text

2197 EOF

2198 if [ $? = 0 ]; then

2199 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2200 sql="select coalesce(b.val, a.name) from

2201 (select name from user where rowid in ($uids)) a

2202 left join user_s b on a.name=b.name and b.key='gecos';"

2203 html pre<<EOF

2204 `query "$sql"`

2205 (送信者である $gecos さんも含まれます)

2206 EOF

2207 err SendDone: `echo $sql`

2208 fi

2209 elif [ x"$rm" = x"commission" ]; then

2210 grp_reg_adm $grid $usel

2211 elif [ x"$rm" = x"addteam" ]; then

2212 team=`getpar team|sed "s/'/''/g"` # for single quotation

2213 newteam=`echo "$team"|tr -d ,`

2214 if [ x"$team" != x"$newteam" ]; then

2215 echo "チーム名に使えない文字を除去しました" | html p

2216 team=newteam

2217 fi

2218 if [ -z "$team" -o x"$team" = x"なし" ]; then

2219 cat<<-EOF | html p

2220 有効なチーム名を入力してください。

2221 カンマだけ、「なし」という名前は使えません。

2222 EOF

2223 echo "有効なチーム名を入力してください。" | html p

2224 else

2225 grp_add_team $grid "$team" $usel

2226 fi

2227 elif [ x"$rm" = x"rmteam" ]; then

2228 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2229 rmteam=`getpar rmteam|sed "s/'/''/g"`

2230 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2231 gname='$grp' AND user='$user' AND key='team'\

2232 AND val='$rmteam';\"`" ]; then

2233 grp_rm_team $grid "$rmteam" $usel

2234 else

2235 echo "所属していないチームの除去操作はできません。"|html p

2236 fi

2237 else

2238 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2239 fi

2240 fi

2241 fi

2242 # New entry

2243 sql="select /* Ahh, ugly SQL, I wanna fix... */

2244 case

2245 when (select user from grp_adm where

2246 gname=(select gname from grp where rowid=$grid)

2247 and user=a.name) is not null

2248 then 'k'

2249 else ''

2250 end || a.rowid||

2251 ','||a.gecos as NAME,

2252 (SELECT count(author) /* Put post count for scoring 2016-08-01 */

2253 FROM article NATURAL JOIN article_s

2254 WHERE blogid IN

2255 (SELECT id FROM blog_s

2256 WHERE key='owner'

2257 AND val=(SELECT gname FROM grp where rowid=$grid))

2258 AND author=a.name AND key='text')

2259 as POST,

2260 (SELECT group_concat(val, ',')

2261 FROM grp_mem_m

2262 WHERE gname='$grp' AND user=a.name AND key='team') as TEAM

2263 FROM

2264 gecoses a

2265 WHERE name in (select user from grp_mem where

2266 gname=(select gname from grp where rowid=$grid))

2267 ORDER by a.gecos;"

2268 err grpaction: "`echo \"$sql\"`"

2269 tf=$tmpd/title.$$

2270 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2271 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2272 for=\"cmadmin\">グループ管理者委任</label>

2273 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2274 </p></div>"

2275 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2276 for=\"conf\">グループ登録解除</label>

2277 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2278 <p>この操作による通知は本人に行きません。

2279 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2280 </div>"

2281 # Get team list to which current user belongs into $hexteams

2282 myhexteams=$(hexteams "$grp" "$user")

2283 allhexteams=$(hexteams "$grp")

2284 if [ -n "$myhexteams" ]; then

2285 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2286 for=\"cmrmteam\">チーム属性除去</label>

2287 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2288 を除去します: `cgi_checkbox teamconfirm yes` 確認

2289 <p>この操作による通知は本人に行きません。

2290 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2291 </div>

2292 "

2293 fi

2294 b1='<label> <input type="checkbox" name="usel" value="'

2295 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2296 #b2='"> <span>' b3='</span></label>'

2297 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2298 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2299 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2300 cgi_form grpaction<<EOF \

2301 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2302 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

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

2304 -D_SUBTITLE_="チェック後操作ボタン" \

2305 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2306 $layout/form+dump.m4.html

2307 <p>下でチェックした人を対象として:</p>

2308 <div class="foldtabs">

2309 `cgi_radio rm addteam 'id="cmteam"'`<label

2310 for="cmteam">同じチーム属性を付与</label>

2311 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2312 `cgi_datalist_h teams $allhexteams`

2313 </div>

2314 ${rmteammsg}

2315 `cgi_radio rm send id="sendmsg"`<label

2316 for="sendmsg">メッセージ送信</label>

2317 <div>

2318 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2319 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2320 `cgi_textarea text "" cols=40`

2321 </div>

2322 ${isowner:+$cmmsg$excmsg}

2323 `cgi_radio rm close id="x"`<label for="x">×</label>

2324 </div>

2325 <h4>$grp 参加者一覧</h4>

2326 <table class="td2r">

2327 `sq $db -header -html "$sql"`

2328 </table>

2329 `cgi_hidden grp $grid`

2330 EOF

2331 }

2332 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2333 # Create TEMPORARY VIEW

2334 query<<EOF

2335 CREATE TEMPORARY VIEW writeusers AS

2336 SELECT DISTINCT author FROM article

2337 WHERE id in (

2338 select id from article where blogid=(select id from blog where rowid=$1)

2339 );

2340 CREATE TEMPORARY VIEW movablegroups AS

2341 SELECT g.rowid growid , g.gname

2342 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2343 ON grp.gname=gm.gname -- そのユーザが属している

2344 AND user='$user') g -- グループに絞る

2345 WHERE (SELECT author FROM writeusers

2346 EXCEPT

2347 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2348 IS NULL;

2349 $2

2350 EOF

2351 }

2352 sql4readableblogs() {

2353 # Create view of blogs that can be readable to $user

2354 # Blog is readable when:

2355 # 1: blog owner is an user

2356 # 2: else, 2.1: owner-group where the $user belongs

2357 # 2.2: else, owner-group is not moderated

2358 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2359 cat<<EOF ## | tee tmp/sql.out

2360 CREATE TEMPORARY VIEW readableblogs AS

2361 SELECT blog.rowid rid, id, author

2362 FROM blog

2363 NATURAL JOIN

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

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

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

2367 WHEN (SELECT val FROM grp_s

2368 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2369 AND

2370 (SELECT user FROM grp_mem

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

2372 THEN 0

2373 ELSE 1

2374 END;

2375 EOF

2376 }

2377 editheading() { # $1=rowid-of-heading

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

2379 if [ -z "$rowid" ]; then

2380 echo "話題番号が未指定です。" | html p

2381 return

2382 fi

2383 owner=`getvalbyid blog owner $rowid`

2384 title=`getvalbyid blog title $rowid`

2385 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2386 | _m4 -D_TITLE_="修正" \

2387 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2388 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

2391 # Move to group

2392 if isuser "$owner"; then

2393 crview4article $rowid

2394 n=`query "SELECT count(*) FROM writeusers;"`

2395 err N=$n

2396 if [ $((n)) -gt 0 ]; then

2397 err ROWID=$rowid

2398 sql="SELECT growid || ':' || gname FROM movablegroups;"

2399 cat<<-EOF

2400 <div class="fold">

2401 `cgi_checkbox mv send id="mv"`<label

2402 for="mv">この話題をグループ所有に移動する</label>

2403 <div>

2404 <form action="?mvart" method="POST" enctype="multipart/form-data">

2405 移動先グループ:

2406 <select name="mv2grp">

2407 EOF

2408 query ".mode html"

2409 query<<-EOF |

2410 $sql

2411 .mode list

2412 EOF

2413 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2414 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2415 cat<<-EOF

2416 </select>

2417 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2418 そのグループに加入しているものに限られます)</p>

2419 <p>`cgi_checkbox cfm yes`<label>確認

2420 (この操作は元に戻すことができません)</label></p>

2421 `cgi_hidden blogrowid $rowid`

2422 `cgi_submit 移動`

2423 `cgi_reset Reset`

2424 </form>

2425 </div>

2426 </div>

2427 EOF

2428 fi

2429 # end of isuser "$owner"

2430 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2431 [ -n "$hexteams" ];}; then

2432 none="`echo なし|hexize`"

2433 cat<<-EOF

2434 <div class="fold">

2435 `cgi_checkbox mv2team send id="mv2team"`<label

2436 for="mv2team">この話題を以下のチームのものにする</label>

2437 <div><p>現在の所属チーム設定:

2438 `query "SELECT

2439 coalesce((SELECT val FROM blog_s

2440 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2441 AND key='team'),

2442 ':なし');"`</p>

2443 <form action="?mvart" method="POST" enctype="multipart/form-data">

2444 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2445 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2446 `cgi_hidden blogrowid $rowid`<br>

2447 `cgi_submit 移動`

2448 `cgi_reset Reset`

2449 </form></div></div>

2450 EOF

2451 fi

2452 }

2453 mvart() { # move diary to some group or team

2454 # or move blog of group to team which belong to the group

2455 blogrowid=`getpar blogrowid`

2456 cfm=`getpar cfm`

2457 ##### echo move blog:$blogrowid to $mv2grp | html p

2458 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2459 . ./s4-blog.sh

2460 if [ -z "$blogrowid" ]; then

2461 echo "無効な指定です(mvart)。" | html p

2462 return

2463 elif [ x"$cfm" != x"yes" ]; then

2464 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2465 elif { mv2grp=`getpar mv2grp`

2466 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2467 [ -n "$mv2grp" ]; }; then

2468 crview4article $blogrowid

2469 ########## TRANSACTION BEGIN

2470 query "BEGIN;"

2471 n=`query "SELECT count(*) FROM writeusers;"`

2472 err Nwriteuser=$n

2473 if [ $((n)) -gt 0 ]; then

2474 query<<-EOF

2475 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2476 WHERE key='owner'

2477 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2478 AND $mv2grp IN (SELECT growid FROM movablegroups);

2479 EOF

2480 fi

2481 query "END;"

2482 ########## TRANSACTION END

2483 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2484 [ -n "$mv2team" ];}; then

2485 # blog owner can move it to ANY team

2486 case "$mv2team" in

2487 'なし')

2488 cat<<-EOF

2489 DELETE FROM blog_s

2490 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2491 AND key='team';

2492 EOF

2493 ;;

2494 "") ;;

2495 *)cat<<-EOF

2496 BEGIN;

2497 REPLACE INTO blog_s(id, key, val)

2498 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2499 'team', '$mv2team');

2500 REPLACE INTO blog_s(id, key, val)

2501 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2502 'notify', 'all'); -- Change notify to all

2503 END;

2504 EOF

2505 esac | query

2506 fi

2507 blog_reply $blogrowid

2508 echo yes | html p

2509 }

2510 editart() { # $1=article-rowid $2=blogrowid

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

2512 blogrowid=${2%%[!A-Z0-9a-z_]*}

2513 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2514 echo "表示する記事番号が未指定です。" | html p

2515 return

2516 fi

2517 owner=`getvalbyid blog owner $blogrowid`

2518 title=`getvalbyid blog title $blogrowid`

2519 author=`getvalbyid article author $rowid`

2520 err EDITart: owner=$owner, author=$author

2521 if isgrpowner $user $owner; then

2522 : EDIT OK

2523 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2524 echo "本人か所有者しか編集できません." | html p

2525 return

2526 fi

2527 aid=`query "select id from article where rowid=$rowid;"`

2528 tmpout=$tmpd/editart.$$.out

2529 GF_ACTION="?replyblog+$blogrowid#$aid" \

2530 edittable $formdir/article.def article $rowid \

2531 > $tmpout

2532 rm -f /tmp/editart.out

2533 # Cannot use pipelining to m4 with genform() because of stdin stack

2534 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2535 -D_FORM_="syscmd(cat $tmpout)" \

2536 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2537 -D_BLOGS_= -D_DUMPTABLE_= \

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

2539 }

2540 send2mem() {

2541 rowid=`getpar grp`

2542 if [ -z "$rowid" ]; then

2543 echo "グループが未指定です。" | html p

2544 return

2545 fi

2546 message=`getpar message`

2547 if [ -z "$message" ]; then

2548 echo "文章を入れてください。" | html p

2549 return

2550 fi

2551 grp=`getgroupbyid $rowid`

2552 members=`collectemail $grp`

2553 # smail rcpt subj (file)

2554 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2555 $urlbase?grp+$rowid

2556 グループ $grp に所属する

2557 `gecos $user` さんよりメッセージ:

2558

2559 $message

2560 EOF

2561 cat<<EOF

2562 <p>以下の宛先に送信しました。</p>

2563 <pre>

2564 $members

2565 </pre>

2566 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2567 EOF

2568 }

2569 joingrpadmit() {

2570 # $1=yes/no $2=session-key

2571 if [ -z "$2" ]; then

2572 echo "bye bye" | html p; return

2573 fi

2574 t_usr=`session=$2 getpar user`

2575 t_grp=`session=$2 getpar group`

2576 err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2577 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2578 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2579 echo "無効な加入依頼です。" | html p

2580 echo "有効期限が切れたか、

2581 他の管理者がいる場合は処理済みの可能性があります。" | html p

2582 return

2583 fi

2584 if ! isgrpowner "$user" $t_grp; then

2585 echo "グループ管理者のみの機能です。" | html p; return

2586 fi

2587 case $1 in

2588 yes) joingrp "$t_grp" "$t_usr" yes ;;

2589 no) joingrp "$t_grp" "$t_usr" no ;;

2590 *)

2591 echo "無効な指定です($1)。" | html p

2592 return ;;

2593 esac

2594 gid=$(query "select rowid from grp where gname=`sqlquote $t_grp`;")

2595 rcpts="`getgroupadminmails $t_grp` $user"

2596 err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2597 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2598 に

2599 $t_usr

2600 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2601 しました。"

2602 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2603 query "delete from session where id='$2';"

2604 echo "$body" | html p

2605 }

2606

2607 joingrprequest() {

2608 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2609 jss="joingrp-`date +%s`-`genrandom 12`"

2610 addsession $jss +${memoplimitdays}days

2611 query "replace into par values('$jss', 'group', 'string', `sqlquote $1`),

2612 ('$jss', 'user', 'string', `sqlquote $user`);"

2613 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2614 $url

2615 $user さんから

2616 グループ $1

2617 に加入依頼がありました。

2618

2619 承認する:

2620 $urlbase?joingrpadmit+yes+$jss

2621

2622 白紙に戻す:

2623 $urlbase?joingrpadmit+no+$jss

2624 EOF

2625 echo "管理者に加入依頼を出しました。

2626 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2627 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2628 }

2629 joingrp() {

2630 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2631 err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2632 if isgrpowner "$user" "$1"; then

2633 isowner="yes"

2634 elif [ -n "$5" ]; then

2635 isowner="yes"

2636 else

2637 isowner=""

2638 fi

2639 err jg:isgrpowner: isowner="$isowner"

2640 if [ -n "$isowner" ]; then

2641 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2642 elif [ x"$2" != x"$user" ]; then # if user is not login user

2643 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2644 return

2645 elif [ x"$3" = x"no" ]; then

2646 : # Do not pursue those who leave

2647 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2648 : # Member can change own email address for the joining moderated group

2649 else # adding user is $user itself

2650 case `getgroupattr $1 regmode` in

2651 moderated)

2652 joingrprequest "$@" # Request only

2653 return

2654 ;;

2655 *)

2656 ;;

2657 esac

2658 fi

2659 qgname=`sqlquote $1`

2660 cond="where gname=$qgname and user='$2'"

2661 if [ x"$3" = x"yes" ]; then

2662 query "replace into grp_mem values($qgname, '$2');"

2663 if [ -n "$4" ]; then

2664 if msg=`emaildomaincheck "$4"`; then

2665 err "replace into grp_mem_s values($qgname, '$user', 'email', \

2666 'string', '$4', NULL);"

2667 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2668 'string', '$4', NULL);"

2669 else

2670 echo $msg

2671 fi

2672 else

2673 query "delete from grp_mem_s $cond and key='email';"

2674 fi

2675 if [ -n "$5" ]; then # as ADMIN

2676 # Coming here means newly created group

2677 sql="select case\

2678 when (select count(*) from grp_mem where gname=$qgname)=1\

2679 then (select user from grp_mem\

2680 where gname=$qgname and user='$user')\

2681 else '' end; "

2682 err NewGrpChk: $sql

2683 if [ -n "`query \"$sql\"`" ]; then

2684 err ADMIN: "replace into grp_adm values($qgname, '$user');"

2685 query "replace into grp_adm values($qgname, '$user');"

2686 fi

2687 fi

2688 else

2689 query "delete from grp_mem $cond;

2690 delete from grp_mem_s $cond;

2691 delete from grp_mem_m $cond;"

2692 fi

2693 }

2694 grp_add_team() (

2695 # $1=grp-rowid $2=team $3...=user-rowid(s)

2696 grp=`getgroupbyid $1`

2697 team=$2; shift; shift

2698 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2699 { echo "BEGIN;"

2700 for user; do

2701 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2702 '$grp',\

2703 (SELECT name FROM user WHERE rowid=$user),\

2704 'team', 'string', '$team');"

2705 done

2706 echo "END;"

2707 } | query

2708 )

2709 grp_rm_team() (

2710 # $1=grp-rowid $2=team $3...=user-rowid(s)

2711 grid=$1

2712 qgrp=$(sqlquote "`getgroupbyid $grid`")

2713 team=$2; shift; shift

2714 [ -z "$grid" -o -z "$team" ] && return

2715 { echo "BEGIN;"

2716 for user; do

2717 echo "DELETE FROM grp_mem_m\

2718 WHERE gname=$qgrp \

2719 AND user=(SELECT name FROM user WHERE rowid=$user)\

2720 AND key='team' AND val='$team';"

2721 done

2722 cat<<-EOF

2723 DELETE FROM blog_s

2724 WHERE rowid=(

2725 SELECT rowid

2726 FROM blog_s a

2727 WHERE key='team'

2728 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2729 AND NOT EXISTS (SELECT * FROM grp_mem_m

2730 WHERE key='team' AND val=a.val -- a.val=team

2731 AND gname = (SELECT val FROM blog_s b

2732 WHERE a.id=b.id AND key='owner')

2733 ));

2734 EOF

2735

2736 echo "END;"

2737 } | query

2738 )

2739 grp_reg_adm() {

2740 # $1=grp-rowid $2...=user-rowid

2741 grid=$1

2742 grp=`getgroupbyid "$1"`

2743 if [ -z "$grp" ]; then

2744 echo "無効なグループIDです" | html p; return

2745 fi

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

2747 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2748 fi

2749 shift

2750 for urid; do

2751 newadm=`query "select name from user where rowid=$urid;"`

2752 if [ -z "$newadm" ]; then

2753 echo "指定ユーザIDがおかしいようです。" | html p; return

2754 fi

2755 err GRP_reg_adm: "replace into grp_adm values(`sqlquote $grp`, '$newadm');"

2756 err ismember $newadm $grp

2757 if ismember $newadm $grp; then

2758 # OK, go ahead

2759 getgname="(select gname from grp where rowid=$grid)"

2760 query "replace into grp_adm values($getgname, '$newadm');"

2761 # confirm insertion

2762 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2763 if [ -n "`query \"$sql;\"`" ]; then

2764 echo "追加完了: $newadm" | html p

2765 else

2766 echo "追加失敗($1 $urid)" | html p

2767 fi

2768 fi

2769 showgroup $grid

2770 done

2771 }

2772 dumptable() {

2773 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2774 # textのフィールドだけ全てダンプにしたほうがいいか

2775 # $DT_VIEW sets link

2776 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2777 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2778 VIEW=${DT_VIEW-replyblog}

2779 if [ -n "$VIEW" ]; then

2780 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VIE</a><a href=\"$myname?$VIEW+\\2#bottom\">W</a>"

2781 fi

2782 # $DT_CHLD=ChildTable:BindColumn

2783 if [ -n "$DT_CHLD" ]; then

2784 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

2785 cntall="(select count($_i) from $_t where $_i=a.id)"

2786 # XXX: Dirty workaround for slow subquery of acclog

2787 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

2788 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

2789 cntnew="(select count(val) from ${_t}_s where key='ctime' \

2790 and id in (select id from $_t where $_i=a.id) \

2791 and val > coalesce((select time from myacclog where \

2792 tblrowid=a.rowid),\

2793 '1970-01-01'))"

2794 cnt="$cntnew as '新着', $cntall as '総数',"

2795 dt_class=" td2r td3r dumpblogs"

2796 fi

2797 # Construct join expression

2798 eav="" scols=""

2799 pk=`gettblpkey $2`

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

2801 substr=${substr:-val}

2802 for col in ${3:-`gettbl_s_cols $2`}; do

2803 case $col in

2804 gecos) scols="$scols${scols:+, }${col#}"

2805 continue ;; # built-in column name

2806 *:*) col=${col%:*} as=${col#*:} ;;

2807 *) as=${col} ;;

2808 esac

2809 eav=$eav${eav:+,}" max(case key when '$col' then $substr end) as $as"

2810 scols="$scols${scols:+, }b.$as"

2811 done

2812 #case author when '$user' then a.rowid else '---' end as ID,

2813 sql=${DT_SQL:-"select \

2814 a.rowid as LINK,\

2815 $cnt\

2816 $scols from $2 a left join\

2817 (select $pk,$eav,

2818 max(case key when 'owner'

2819 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

2820 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

2821 err SQL="`echo \"$presql$sql\"|tr -d '\n'`"

2822 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>,"

2823 <div>

2824 <div class="dumptable">

2825 <table class="b$dt_class">

2826 `sq -header -cmd ".mode $1" $db "$presql$sql"`

2827 </table>

2828 </div>

2829 </div>

2830 EOF

2831 }

2832

2833 par2table() (

2834 # copy current parameters of par into destination table

2835 # $1=definition-file

2836 # Using $user and $session

2837 # Return value:

2838 # 0: Stored successfully

2839 # 1: Insufficient fillings

2840 # 2: No permission to modify the record

2841 # 3: Invalid rowid

2842 # 4: SUCCESS to delete

2843 # 5: Stop deletion for lack of confirm check

2844 # 6: Password length too short

2845 # 7: Password mismatch

2846 # 8: Old password incorrect

2847 rowid=`getpar rowid`

2848 err ...........rowid=$rowid

2849 if [ ! -e $1 ]; then

2850 echo "テーブル定義ファイルが見付かりません" | html p

2851 exit 1

2852 fi

2853 tbl=${1%.def}

2854 tbl=${tbl##*/}

2855 if [ -n "$rowid" ]; then # Modify existing entry

2856 if [ x"$tbl" = x"user" ]; then

2857 rowowner=`query "select name from $tbl where rowid=$rowid;"`

2858 elif [ x"$tbl" = x"grp" ]; then

2859 sql="select gname from $tbl where rowid=$rowid;"

2860 ##err p2t:grp:q $sql

2861 isgrpowner $user "`query $sql`" && rowowner=$user

2862 else

2863 rowowner=`query "select owner from $tbl where rowid=$rowid;"`

2864 rowowner=${rowowner:-`query "select author from $tbl

2865 where rowid=$rowid;"`}

2866 fi

2867 ### err rowowner=$rowowner

2868 if [ x"$user" != x"$rowowner" ]; then

2869 echo "他人のレコードはいじれないの" | html p

2870 return 2

2871 elif [ -z "$rowowner" ]; then

2872 echo "指定したレコードはないみたい" | html p

2873 return 3

2874 fi

2875 rm=`getpar rm` cfm=`getpar confirm`

2876 # Editing existent entry

2877 if [ x"$rm" = x"yes" ]; then

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

2879 query "delete from $tbl where rowid=$rowid;"

2880 return 4

2881 else

2882 echo "消去確認のチェックがないので消さなかったの..." | html p

2883 return 5

2884 fi

2885 fi

2886 fi

2887

2888 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

2890 # Update of existing record

2891 for col in `gettblcols $tbl`; do

2892 val=`getparquote $col`

2893 [ -z "$val" ] && continue

2894 err query "update $tbl set $col=$val where rowid=$rowid"

2895 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

2896 sql="update $tbl set $col=$val where rowid=$rowid;"

2897 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

2898 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

2899 ## User name cannot be changed with interface provided with this

2900 ## script. But we offer the trigger to change owner user

2901 ## of blog_s table.

2902 err "select quote($col) from $tbl where rowid=$rowid;"

2903 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

2904 cat<<-EOF | query

2905 BEGIN;

2906 $sql

2907 update blog_s set val=$val

2908 where key='owner' and val=$old;

2909 COMMIT;

2910 EOF

2911 ## XX: DIRTY Hack Ends here

2912 ## We should keep blog's owner as a single column which has

2913 ## foreign key constraint with primary key of grp/user.

2914 else

2915 query "$sql"

2916 fi

2917 done

2918 # Then, set up $pval for further insertion of tbl_s and tbl_m

2919 for col in `gettblpkey $tbl`; do

2920 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

2921 pval="$pval${pval:+, }\"$val\""

2922 done

2923 else

2924 # New entry

2925 # Generate values() for primary keys

2926 for col in `gettblpkey $tbl`; do

2927 # Genuine primary keys for _m and _s

2928 val=`getvalquote $tbl $col`

2929 [ -z "$val" ] && continue

2930 pval="$pval${pval:+, }$val"

2931 done

2932 err pval=$pval

2933 for col in `gettblfkey $tbl`; do

2934 # args for values() to insertion into master table

2935 val=`getvalquote $tbl $col`

2936 [ -z "$val" ] && continue

2937 formaster=$formaster"${formaster:+, }$val"

2938 done

2939 formaster="$pval${formaster:+, }$formaster"

2940 err formaster=$formaster

2941 if [ -z "$formaster" ]; then

2942 echo "項目を全て埋めてください" | html pre

2943 return 1

2944 fi

2945 err "replace into $tbl values($formaster);"

2946 query "replace into $tbl values($formaster);"

2947 ## Insertion to master table, done

2948 fi

2949

2950 for kt in s m; do

2951 tb2=${tbl}_$kt

2952 for col in `gettbl_${kt}_cols $tbl`; do

2953 ptype=`getpartype $col "limit 1"`

2954

2955 # First, check update of existing entries in _m

2956 if [ $kt = m ]; then

2957 # sessID|address.1.22|string|Somewhere-x.y.z

2958 sql=""

2959 err dots from query "select var from par where var like '$col.%';"

2960 for v in `query "select var from par where var like '$col.%';"`; do

2961 # v=address.1.22

2962 st_rowid=${v##*.}

2963 origcol=${v%%.*} # original column derived from

2964 err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

2965 ##case `getpartype $v` in

2966 err CASE `gettbl_coltype $tbl/$origcol` in

2967 err edit flag = `getpar action.$v`

2968 case `getpar action.$v` in

2969 rm)

2970 if [ x`getpar confirm.$v` = x"yes" ]; then

2971 newsql="delete from $tb2"

2972 else

2973 echo "削除確認未チェック" | html p

2974 fi ;;

2975 edit)

2976 case `gettbl_coltype $tbl/$origcol` in

2977 image|document|binary)

2978 file=$tmpd/`getparfilename $v`

2979 err type=file=$file

2980 [ -z "$file" ] && continue

2981 bn=`sqlquotestr "${file##*/}"`

2982 bin="X'"$(hexize "$file")"'"

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

2984 type=\"file:$ct\"

2985 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

2986 cachedir=`getcachedir "$tbl/$rowid"`

2987 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

2988 rm -rf $cachedir

2989 ;;

2990 *)

2991 newsql="update $tb2 set val=(select val from par where var \

2992 like '$col.%.$st_rowid')"

2993 ;;

2994 esac

2995 ;;

2996 *) # maybe "keep", do not modify value

2997 continue

2998 ;;

2999 esac

3000 # err newsql=$newsql

3001 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3002 done

3003

3004 if [ x"$bin" = x"NULL" ]; then

3005 err repl:normal sql=`echo $sql`

3006 query "$sql

3007 delete from $tb2 where type='string' and val='';"

3008 err repl:normal done

3009 else

3010 sqlfile="$tmpd/sqlf.$$"

3011 echo "$sql" > $sqlfile

3012 err repl:sqlfile=`ls -lF $sqlfile`

3013 query ".read $sqlfile"

3014 err repl:done

3015 fi

3016 # Rest of kt==m: set multiple mode

3017 nr=`getparcount $col`

3018 else

3019 nr=1 # for kt==s, number of records is 1

3020 fi

3021

3022 i=0

3023 while [ $i -lt $nr ]; do

3024 limit="limit 1 offset $i"

3025 i=$((i+1)) # increase beforehand against continue

3026 val=`getvalquote $tbl $col "$limit"`

3027 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3028 err $col=$val

3029 bin=NULL

3030 err partype$col=`getpartype $col "$limit"`

3031 case $ptype in

3032 file) file=$tmpd/`getparfilename $col "$limit"`

3033 err parfile-$col=$file

3034 [ -z "$file" ] && continue

3035 bin="X'"$(hexize "$file")"'"

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

3037 type=\"file:$ct\" ;;

3038 "*"*) continue ;; # foreign table

3039 *) type=\"string\" ;;

3040 esac

3041 case `gettbl_coltype $tbl/$col` in

3042 password) # special care for password

3043 # name={password,pswd1,pswd2}

3044 p1=`getpar pswd1 "$limit"`

3045 if [ -z "$p1" ]; then

3046 continue # SKIP password setting, if p1 is empty

3047 else

3048 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3049 ## err pswd=$pswd

3050 if pwcheck "$pswd"; then

3051 if [ x"$p1" = x"$p2" ]; then

3052 case "$p1" in

3053 ??????????*) ;;

3054 *) echo "パスワードは10字以上にしてください。" | html p

3055 return 6;;

3056 esac

3057 val="\"`echo $p1|mypwhash`\""

3058 else

3059 echo "2つの新パスワード不一致" | html p

3060 return 7

3061 fi

3062 else

3063 echo "旧パスワード違います" | html p

3064 return 8

3065 fi

3066 fi

3067 ;;

3068 esac

3069 err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3070 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3071 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3072 if [ x"$bin" = x"NULL" ]; then

3073 err Normal-query: `echo $sql`

3074 query "$sql"

3075 else

3076 sqlfile="$tmpd/query.$$"

3077 echo "$sql" > $sqlfile

3078 err sqlfile=`ls -lF $sqlfile`

3079 query ".read $sqlfile"

3080 fi

3081 err p2t done

3082 done

3083 done

3084 done

3085 return 0

3086 ##err donee

3087 )

3088 genform() {

3089 # $1 = form definition file

3090 # $2, $3 (optional)= table name and ROWID

3091 # If $GF_VIEWONLY set and nonNull, output values without form

3092 # If $GF_ARGS set, use it as content-strings in the form

3093 # If $GF_OWNER set, use it as value of name="owner"

3094 # If $GF_STAGE set, use it as value of name="stage"

3095 forms="" hiddens="" rowid=$3

3096 if [ ! -e "$1" ]; then

3097 echo "そのようなデータベースはないようです($2)。" | html p

3098 return

3099 elif [ -n "$2" ]; then

3100 err genform1: "select * from $2 where rowid='$rowid'"

3101 rec=`query "select * from $2 where rowid='$rowid';"`

3102 if [ -z "$rec" ]; then

3103 pk=`gettblpkey $2`

3104 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3105 err "select rowid from $2 where $pk='$rowid';"

3106 rec=`query "select rowid from $2 where $pk='$rowid';"`

3107 err rec-rowid=$rec

3108 rowid=$rec

3109 rec=$3

3110 fi

3111 if [ -z "$rec" ]; then

3112 echo "そんなレコードはないみたいね..." | html p

3113 return

3114 fi

3115 fi

3116 if [ -z "$GF_VIEWONLY" ]; then

3117 rm='<input id="rm" name="rm" type="checkbox"

3118 value="yes"><label for="rm">このエントリの削除</label>

3119 <span>ほんとうに消しますよ(確認)!

3120 <input name="confirm" type=checkbox value="yes">はい</span>'

3121 fi

3122 # Image Cache dir

3123 err genform: getcache=$2/$rowid

3124 td=`getcachedir "$2/$rowid"`

3125 while IFS=: read prompt name keytype type args; do

3126 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3127 sp="${args:+ }"

3128 form="" val=""

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

3130 # err genform2a: Seeking for "$2.$name, type=$type"

3131 rawval=`getvalbyid $2 $name $rowid $td`

3132 val=`echo "$rawval"|htmlescape`

3133 err genform3a: getvalbyid $2 $name $rowid $td

3134 err genform3b: val="[$val]" type="$type"

3135 fi

3136 if [ -n "$GF_VIEWONLY" ]; then

3137 is_hidden "$2" "$name" && continue

3138 fi

3139 case "$type" in

3140 text*)

3141 cgiform=cgi_multi_$type

3142 if [ -s $td/$name.count -a -n "$val" ]; then

3143 form=`$cgiform $name $td`

3144 val=$(echo "$val"|

3145 while read fn; do

3146 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3147 </td></tr>$nl"

3148 done)

3149 val="<table>$nl$val$nl</table>"

3150 else

3151 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3152 err genform: cgi_$type $name $val "$args"

3153 form=`cgi_$type $name "$val" "$args"`

3154 fi

3155 ;;

3156 [Rr][Aa][Dd][Ii][Oo])

3157 fh="<label><input type=\"radio\" name=\"$name\""

3158 form="`echo $args|sed -e \

3159 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3160 ;;

3161 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3162 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3163 ;;

3164 [Ss][Ee][Ll][Ee][Cc][Tt])

3165 fh="<select name=\"$name\">$nl"

3166 form=$(for l in $args; do

3167 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3168 done)

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

3170 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3171 fi

3172 form="$fh$form</select>"

3173 ;;

3174 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3175 if [ -s $td/$name.count ]; then

3176 err Calling cgi_multi_file: "nae=[$name] td=[$td] args=[$args]"

3177 form=`cgi_multi_file $name $td "$args"`

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

3179 hrfb="$myname?showattc+$2_m"

3180 val=$(echo "$rawval" \

3181 | while read fn; do

3182 data=`percenthex "$td/$fn"`

3183 #ct=`cat $td/$fn.content-type`

3184 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3185 ri=`cat "$td/$fn.rowid"`

3186 err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3187 #imgsrc="<img src=\"data:$ct,$data\">"

3188 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3189 iconhref "$td/$fn" "$hrfb+$ri" ""

3190 done)

3191 fi

3192 else

3193 form="<input type=\"file\" name=\"$name\" $args>"

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

3195 imgs=$(echo "$rawval"\

3196 |while read fn;do

3197 data=`percenthex "$td/$fn"`

3198 echo "<img src=\"data:image/png,$data\">$fn<br>"

3199 done)

3200 form=$form"<br>$imgs"

3201 val=$imgs # 2015-06-15

3202 else

3203 form="<input type=\"file\" name=\"$name\" $args>"

3204 fi

3205 fi

3206 ;;

3207 [Hh][Ii][Dd][Dd][Ee][Nn])

3208 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3209 args="value=\"$GF_STAGE\""

3210 fi

3211 form="<input type=\"hidden\" name=\"$name\" $args>"

3212 prompt='' # Remove prompt

3213 ;;

3214 [Aa][Uu][Tt][Hh][Oo][Rr])

3215 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3216 prompt="" ;;

3217 [Oo][Ww][Nn][Ee][Rr])

3218 val=${GF_OWNER:-$val}

3219 val=${val:-$user}

3220 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3221 prompt="" ;;

3222 [Uu][Ss][Ee][Rr])

3223 # XXX: is null $user ok?

3224 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3225 [ -n "$GF_VIEWONLY" ] && continue

3226 form="$user"

3227 ;;

3228 [Pp]assword)

3229 [ -n "$GF_VIEWONLY" ] && continue

3230 form="`cgi_passwd`"

3231 val=""

3232 ;;

3233 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3234 if [ -z "$rowid" ]; then

3235 val=`genserial`

3236 fi

3237 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3238 prompt="" ;;

3239 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3240 prompt=""

3241 ;;

3242 parent|path|blog*)

3243 prompt=""

3244 ;;

3245 "*"*)

3246 tail=$tail"``"

3247 continue ;;

3248 esac

3249 if [ -n "$prompt" ]; then

3250 if [ -n "${GF_VIEWONLY}" ]; then

3251 form=$val

3252 else

3253 :

3254 fi

3255 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3256 else

3257 hiddens=$hiddens$nl"$form"

3258 fi

3259 done < $1

3260 # enctype="multipart/form-data"

3261 cat<<EOF

3262 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3263 ${rowid:+$rm}

3264 <table class="b $2">

3265 $forms

3266 </table>$hiddens

3267 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3268 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3269 EOF

3270 if [ -z $GF_VIEWONLY ]; then

3271 cat<<EOF

3272 <input type="submit" name="sub" value="OK">

3273 <input type="reset" name="res" value="Reset">

3274 EOF

3275 fi

3276 cat<<EOF

3277 $GF_ARGS</form>

3278 $tail

3279 EOF

3280 }

3281 edittable() {

3282 # $1=form-def $2=table $3 rowid

3283 genform "$@"

3284 }

3285 viewtable() {

3286 GF_VIEWONLY=1 genform "$@"

3287 }

3288 showattc() {

3289 # $1=table_m $2=rowid

3290 err \$1=$1 \$2=$2

3291 if ! isfilereadable $user $1 $2; then

3292 contenttype; echo

3293 echo "このファイルは管理者のみしか見られません" | html p

3294 putfooter; exit

3295 fi

3296 idir=`umask 002; mktempd` || exit 1

3297 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3298 bin=$idir/$myname-$$.bin

3299 sql="select quote(bin) from $1 where rowid='$2';"

3300 err showattc: sql: $sql

3301 sq $db "$sql" | unhexize > $bin

3302 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3303 type=${tv%//*} fn=${tv#*//}

3304 err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3305 ct=${type#file:}

3306 case $ct in # all text/* changed to text/plain

3307 text/*)

3308 charset=`nkf -g $bin|cut -d' ' -f1`

3309 case $charset in

3310 ASCII*) charset="" ;;

3311 esac

3312 ct="text/plain${charset:+; charset=$charset}"

3313 ;;

3314 esac

3315 contenttype "$ct"

3316 echo "Content-Disposition: filename=\"$fn\""

3317 echo "Content-Length: " `cat $bin | wc -c`; echo

3318 #echo "Content-Type: " ${type#file:}; echo

3319 cat $bin

3320 }

3321 #

3322 # Some default stupid handler on CGI values

3323 #

3324 default_storedb() {

3325 # ARG: $1=table-def-file

3326 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3327 tbl=`basename $1`

3328 tbl=${tbl%.def}

3329 cols="`grep :text $1|cut -d: -f2`"

3330 col=`echo "$cols"|head -1`

3331 vcol=`getpar $col`

3332 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3333 if [ -n "$vcol" ]; then

3334 par2table $1

3335 else

3336 return 2 # No insertion occurred

3337 fi

3338 }

3339

3340 default_view() { # $1=def-file

3341 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3342 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3343 default_storedb "$@"

3344 query "select rowid from $tbl order by rowid desc;" \

3345 | while read rowid; do

3346 viewtable $1 $tbl $rowid

3347 done | _m4 -D_TITLE_="$tbl" \

3348 -D_FORM_="`genform $1`" \

3349 -D_DUMPTABLE_="syscmd(cat)" \

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

3351 }

3352 default_viewtext() { # $1=def-file

3353 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3354 default_storedb "$@"

3355 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3356 | _m4 -D_TITLE_="$tbl" \

3357 -D_FORM_="`genform $1`" \

3358 -D_DUMPTABLE_="syscmd(cat)" \

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

3360 }

3361 default_smail() {

3362 default_storedb "$@"

3363 if [ $? -eq 2 ]; then

3364 _m4 -D_TITLE_="入力" \

3365 -D_FORM_="`genform $1`" \

3366 -D_DUMPTABLE_="" \

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

3368 return

3369 fi

3370 cond=""

3371 for pk in `gettblpkey $tbl`; do

3372 pv=$(sqlquote $(getpar $pk))

3373 cond="$cond${cond:+ and }$pk=$pv"

3374 done

3375 sql="select rowid from $tbl where $cond;"

3376 rowid=`query "$sql"`

3377 err smail1 - "$sql" "-> rowid=$rowid"

3378

3379 while IFS=: read prompt name keytype type args; do # Read from $1

3380 val=`getpar $name`

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

3382 text="$text

3383 $prompt

3384 $name=$val

3385 ---------------------------------------------------------"

3386 fi

3387 case "$type" in

3388 image|document|file)

3389 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3390 fns=$(echo "$fn"|while read fn; do

3391 err mv $tmpd/$fn.orig $tmpd/$fn

3392 mv $tmpd/$fn.orig $tmpd/$fn

3393 rm $tmpd/$fn.rowid # Remove cache flag

3394 err "`ls $tmpd/$fn`"

3395 echo $fn

3396 done)

3397 files="$files $fns"

3398 ;;

3399 esac

3400 done < $1

3401 err FILES=$files "`ls -lF $tmpd`"

3402 subj="from ${REMOTE_ADDR}"

3403 (echo "$url"

3404 echo "への書き込みがありました。"

3405 echo "------"

3406 echo "$text"

3407 ) | (cd $tmpd &&

3408 err LS="`ls -lF`" &&

3409 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3410 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3411 echo "以下の内容で送信しました。" | html p

3412 viewtable $1 $tbl \

3413 `query "select rowid from $tbl order by rowid desc limit 1;"`

3414 echo "戻る" | html a "href=\"?\""

3415 }