s4: a868f03970b5 s4-funcs.sh

s4

view s4-funcs.sh @ 319:a868f03970b5

Profimg hiding rule revised

author	HIROSE Yuuji <yuuji@gentei.org>
date	Mon, 17 Oct 2016 09:11:05 +0859
parents	b8a8e627b376
children	18be0c210dc8

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 maxiconxy=400x400

29 maximagexy=1600x1600

30 ### maximagexy=400x400

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

32 blogreadflagrowid=0

34 tconfs=""

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

36 conftbl=_tblconf

37 nl="

38 "

39 . ./s4-cgi.sh

41 : <<EOF

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

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

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

46 レポート提出管理

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

49 [2016]

50 7/12 根本への反省

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

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

54 [以下2015]

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

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

57 Web

58 締切設定

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

61 ○自分の提出物リスト

63 7/19 ○設置

64 ○一斉送信

65 ○getparfilename の tmpd の扱い

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

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

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

69 ○添付ファイル回収

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

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

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

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

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

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

77 ○レポート提出モード

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

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

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

83 7/9 ○管理者の追加

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

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

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

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

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

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

92 ○グループ検索

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

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

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

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

101 ○6/1 par2tableを triplex 対応に

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

103 →とすると一気に

104

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

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

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

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

109

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

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

112 ## 1. passwd

113 ## 2. grp

114 ## 3. grp_mem

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

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

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

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

119

120 ## ● listの定義:

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

122 ## ● hashの定義:

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

124

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

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

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

128 ## csv2sq3 で .csv.sq3 の Makefile

129

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

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

132 ## type := root | comment

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

134 ## unique(id, filename)

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

136

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

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

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

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

141 ## できたー!

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

143 ↓

144 ↓以下に変更

145 with recursive allmem as

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

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

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

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

150

151

152 with recursive allmem as

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

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

155 where allmem.val=grp_m.gname)

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

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

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

159

160

161 ## triggerもできた。

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

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

164

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

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

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

168 ## グループ管理

169 ## 個人情報管理

170 ##

171

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

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

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

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

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

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

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

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

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

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

182

183 ## ログテーブル

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

185

186

187

188 ■表設計

189 * 3つの表に分散管理

190 id格納表 + hash表 + list表

191 * *_s *_m

192

193

194

195 user, user_map, user_col

196

197 ■抽象エントリタイプ

198 * user

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

200 * group

201 権限判定に利用

202 * serial

203 自動idとして機能

204 * password

205 入力 type=passwordで入力

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

207 * session

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

209 * text

210 入力 type=text

211 * textarea

212 入力 textarea

213 * image|document

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

215 * owner

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

217 * gowner

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

219 * timestamp

220 datetime()

221 * parent

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

223 * path

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

225

226 格納タイプ

227 * list

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

229 * hash

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

231

232 オブジェクトタイプ

233 * entry

234 id, title, owner

235 * textpart

236 id, parentID, text

237 * binarypart

238 id, parentID, contenttype, filename, content

239 * content

240 hash(textpart), list(binarypart)

241 * topic

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

243 * reply

244 id, parentID, content

245 * blog

246 list(entry)

247 blog = [topic, list(reply)]

248

249

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

251 "text" => "hogehoge ..",

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

253 "author" => "taro",

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

255 "parent" => "/",

256 "path" => "/1",

257 "text" => "blah, blah, ....",

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

259 {"serial" => 2,

260 "author" => "hanako",

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

262 "parent" => "/",

263 "path" => "/2",

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

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

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

267

268

269 user:=

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

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

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

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

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

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

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

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

278

279 変換表

280 /user/email=m

281

282 blog:=

283 シリアル:id:p:serial

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

285 所有者:owner:s:owner:

286 時刻:ctime:s:stamp:

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

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

289

290 article:=

291 シリアル:id:p:serial

292 筆者:author:s:owner

293 時刻:ctime:s:stamp:

294 参照元:parent:s:parent:

295 パス:path:s:path:

296 本文:text:s:textarea:

297 画像:image:m:image:

298

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

300

301

302 EOF

303

304 sq() {

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

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

307 }

308 dbsetup() {

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

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

311 sqi=$tmpdir/sqi.$$

312 sqo=$tmpdir/sqo.$$

313 mkfifo $sqi $sqo

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

315 sq $db < $sqi &

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

317 exec 2>> $tmpdir/error.out

318 exec 3>> $tmpdir/debug.out

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

320 rm $sqi

321 }

322 cleanup() {

323 echo .quit >&5

324 kill $sq3pid

325 kill $sq3pid

326 rm -f $sqo $sqi

327 rm -rf $tmpfiles

328 }

329 query() {

330 echo ".once $sqo" >&5

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

332 cat

333 else

334 echo "$@"

335 fi >&5

336 cat $sqo

337 }

338 _m4() {

339 #_S4NAME_=f,f,f

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

341 }

342 ismember() {

343 # $1=user, $2=group

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

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

346 }

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

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

349 }

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

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

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

353 }

354 isgrpowner() (

355 # $1=user, $2=group

356 gn=`sqlquote "$2"`

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

358 err isgrpowner: $sql

359 test -n "`query $sql`"

360 )

361 getgroupadminmails() {

362 # $1=group

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

364 email4group "$1" "$i" ;

365 done

366 }

367 getgroupadmins() { # $1=group

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

369 qgrp=`sqlquote "$1"`

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

371 }

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

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

374 getvalbyid grp $2 \

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

376 }

377 getgroupbyid() {

378 # $1=id|gname

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

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

381 # err ggbyid: `echo $sql`

382 query $sql

383 }

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

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

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

387

388 # Return true when anonymous mode

389 [ "$anonymousmode" ] && return 0

390 # case `getvalbyid blog mode $2` in

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

392 # *closed*)

393 # owner=`getvalbyid blog owner $2`

394 # if isgrp $owner; then

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

396 # elif isuser $owner; then

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

398 # fi

399 # esac

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

401 sql="with getblog as (\

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

403 select blogid from article where id in\

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

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

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

407 select case\

408 when (select author from article where\

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

410 then 'author'\

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

412 then 'open'\

413 when (select val from getmode) is null \

414 then 'open'

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

416 then (select user from grp_adm where \

417 gname=(select val from getowner) and \

418 user='$1')\

419 when (select author from article where\

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

421 then 'user+author'

422 else '' end;"

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

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

425 result=`query "$sql"`

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

427 return 2

428 }

429 linkhome() {

430 # $1=UserOrGroup

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

432 if isuser $1; then

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

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

435 else

436 echo -n "grp+$1"

437 fi

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

439 }

440 hreflink() {

441 # s4 specific notation:

442 # ^href=URL

443 # ^iframe=URL

444 # OSM umap Wikistyle Notation:

445 # [[URL]] - Simle Link

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

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

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

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

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

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

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

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

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

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

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

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

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

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

460 }

461 minitbl() {

462 sed -n '

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

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

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

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

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

468 H; # Concat this line to HoldSpace

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

470 $ b cont

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

472 }

473 :cont

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

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

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

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

478 p; # Print whole "table" element

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

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

481 }

482 x; # Back to the newest line

483 p; # Print rest'

484 }

485 acclog() (

486 # $1=table, $2=rowid

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

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

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

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

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

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

493 fi

494 )

495 gecos() (

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

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

498 )

499 setpar() {

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

501 }

502 replpar() {

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

504 }

505 getpar() {

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

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

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

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

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

511 fi

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

513 case "$var" in

514 owner)

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

516 echo $user; return

517 elif ismember $user $val; then

518 echo $val; return

519 fi ;;

520 esac

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

522 echo "$val"

523 }

524

525 getpartype() {

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

527 }

528 getparcount() {

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

530 }

531 getparfilename() {

532 # null if type of $1 is not file

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

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

535 }

536 sqlquote() {

537 (v="$1"

538 case "$v" in

539 "") return ;; # null

540 "X'"*) # quoted hex string

541 echo $1 ;;

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

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

544 echo "\"$v\""

545 return ;;

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

547 echo "\"$v\""

548 return ;;

549 *)

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

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

552 else

553 echo "\"$v\""

554 fi ;;

555 esac)

556 }

557 sqlquotestr() (

558 case "$1" in

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

560 echo "'$v'" ;;

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

562 esac

563 )

564 mktempd() {

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

566 }

567 getcachedir() { # $1=maintable

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

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

570 else

571 echo $tmpd/$thumbxy

572 fi

573 }

574 getval() {

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

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

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

578 echo "$user" ;;

579 stamp|datetime)

580 date "+%F %T" ;;

581 serial)

582 (s=`getpar $2`

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

584 *)

585 getpar "$2" "$3";;

586 esac

587 }

588

589 getvalquote() {

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

591 (v=`getval "$@"`

592 case "$v" in

593 "") echo NULL ;;

594 *) sqlquote "$v" ;;

595 esac)

596 }

597 getparquote() {

598 sqlquote `getpar $1`

599 }

600 getbinbyid() {

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

602

603 }

604 getvalbyid() {

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

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

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

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

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

610

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

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

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

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

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

616 return

617 fi

618 done

619 rowid=$3

620 pk=`gettblpkey $1`

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

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

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

624 for kt in s m; do

625 td=${4:-$tmpd}

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

627 t=${1}_$kt

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

629 vcount=1 # count(val)

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

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

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

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

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

635 if [ $kt = m ]; then

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

637 # Reset val to store filenames if type is string

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

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

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

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

642 i=0

643 err gvbid: i=$i vcount=$vcount

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

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

646 i=$((i+1))

647 fn=$c.$i

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

649 case $type in

650 file:*)

651 #file=$td/$val

652 file=$td/`query "select val from $cond $slice;"`

653 # FOR SPEED: Skip file generation if imgcache exists

654 [ -s $file -a -s $td/$fn.rowid -a -s $file.rowid ] && continue

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

656 sq $db<<EOF | unhexize > $file

657 .output $td/$fn.rowid

658 select rowid from $cond $slice;

659 .output $td/$fn

660 select val from $cond $slice;

661 .output $file.content-type

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

663 .output stdout

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

665 EOF

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

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

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

669 cp $file $file.orig 2>&3

670 ls -lh $file | awk '{print $5"B"}'|sed 's/BB/B/' > $file.size

671 case $type in

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

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

674 *|*:[Aa]pplication*)

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

676 png:- > $file

677 ;;

678 esac

679 ;;

680 *)

681 sq $db<<EOF

682 .output $td/$fn.rowid

683 select rowid from $cond $slice;

684 .output $td/$fn

685 select val from $cond $slice;

686 EOF

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

688 ;;

689 esac

690 done

691 else

692 rm -f $td/$c.count

693 case $type in

694 file:*)

695 echo "$val" \

696 | while read fn; do

697 file=$td/$fn

698 if [ ! -s $file ]; then

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

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

701 | unhexize > $file

702 echo ${type#file:} > $file.content-type

703 # err TTTTTTTTTTTTTTTT: $type

704 case $type in

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

706 *:[Aa]pplication*)

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

708 png:- > $file ;;

709 esac

710 fi

711 done

712 ;;

713 esac

714 fi

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

716 return

717 fi

718 done

719 done)

720 }

721 getvalbypkey() (

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

723 pk=`gettblpkey $1`

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

725 getvalbyid "$1" "$2" $rowid $4

726 )

727 getvalbycond() {

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

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

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

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

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

733 fi

734 }

735 getpwfield() {

736 # getpwfield user column

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

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

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

740 echo "$val"

741 return 0

742 else

743 return 1

744 fi

745 }

746 encode() {

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

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

749 sha1=sha1

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

751 sha1=sha1sum

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

753 sha1=gsha1sum

754 fi

755 fi

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

757 }

758 mycrypt() (

759 key=$1 salt=$2

760 err \$2=$2

761 case $2 in

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

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

764 esac

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

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

767 )

768 hexize() {

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

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

771 hexize="xxd -p"

772 else

773 hexize_hd() {

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

775 }

776 hexize="hexize_hd"

777 fi

778 fi

779 cat $1 | $hexize | tr -d '\n'

780 }

781 unhexize() {

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

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

784 unhex="xxd -p -r"

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

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

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

788 EOF

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

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

791 # world writable...:)

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

793 fi

794 fi

795 cat $1 | $unhex

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

797 }

798 percenthex() {

799 hexize $1 | sed 's/$..$/%\1/g'

800 }

801 htmlescape() {

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

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

804 }

805 enascii() {

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

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

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

809 else

810 enascii_now=`date +%FT%T`

811 enascii_sed() {

812 nkf -Z0Z1Z2 \

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

814 }

815 enascii="enascii_sed"

816 fi

817 fi

818 cat "$@" | $enascii

819 }

820 size_h() {

821 i="$1" oi=$1

822 set -- B B KB MB GB TB

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

824 oi=$i

825 i=$((i/1024))

826 shift

827 done

828 echo ${oi}$1

829 }

830 gettblconf() {

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

832 ## tconfs=`sq $db \

833 tconfs=`query \

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

835 fi

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

837 }

838 gettblkeys() {

839 # $1=tbl

840 gettblconf

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

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

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

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

845 case $type in

846 *p*)

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

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

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

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

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

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

853 esac

854 case $type in

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

856 esac

857 done

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

859 }

860 gettblpkey() {

861 # $1=tbl

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

863 }

864 gettblfkey() {

865 (x=`gettblkeys $1`

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

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

868 }

869 gettblcols() {

870 (x=`gettblkeys $1`

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

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

873 }

874 gettbl_s_cols() {

875 (x=`gettblkeys $1`

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

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

878 }

879 gettbl_m_cols() {

880 (x=`gettblkeys $1`

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

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

883 }

884 gettbl_h_cols() {

885 (x=`gettblkeys $1`

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

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

888 }

889 gettbl_coltype() (

890 gettblconf

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

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

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

894 )

895 is_hidden() {

896 # $1=Tbl $2=col

897 gettblconf

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

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

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

901 case $x in

902 *h*) return 0 ;;

903 *) return 1 ;;

904 esac

905 }

906

907 dbsetbyid() {

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

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

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

911 unset primary update

912 gettblconf

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

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

915 #err conf=$conf

916 case ${conf#*=} in

917 p*) primary=1 ;;

918 f*) update=1 ;;

919 u*) ;;

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

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

922 esac

923 #err t=$t

924 type=text fn=""

925 case $conf in

926 */password)

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

928 ;;

929 */image*|*/document*)

930 type=`file --mime-type $val`

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

932 ;;

933 esac

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

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

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

937 if [ "$primary" ]; then

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

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

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

941 elif [ "$update" ]; then

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

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

944 else

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

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

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

948 fi

949 )

950 }

951 expire() (

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

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

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

955 )

956 addsession() {

957 # expireをセット

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

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

960 err addsession "$1" exp=$expire

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

962 # Remove old session parameters

963 now=`expire now`

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

965 }

966 gencookie() (

967 for kv; do

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

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

970 done

971 )

972 contenttype() {

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

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

975 }

976 putheader() {

977

978 }

979 putfooter() {

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

981 }

982 getcookie() (

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

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

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

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

987 done

988 )

989 genrandom() {

990 # $1=columns (default: 10)

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

992 }

993 genserial() {

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

995 }

996 smail() {

997 # smail rcpts subj (file)

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

999 # $MAIL_FROM <- From: header value

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

1001 rcpt=`echo $1` # strip newlines

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

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

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

1005 }

1006 setviastring() {

1007 table=$1

1008 oifs="$IFS"

1009 IFS="&"

1010 for us in $2; do

1011 k=${us%%=*}

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

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

1014 #echo $k=$v

1015 done

1016 IFS="$oifs"

1017 }

1018 checkdomain() (

1019 # Check the validity of domain by referring DNS

1020 item=$1

1021 err checkdomain $1

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

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

1024 )

1025 pwcheck() {

1026 # $1=passwd

1027 dbpswd=`getpwfield $user pswd`

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

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

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

1031 }

1032 mypwhash() {

1033 mycrypt `cat` `genrandom 5`

1034 }

1035 wasureta() {

1036 user=$1

1037 if ! checkdomain $user; then

1038 contenttype; echo

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

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

1041 putfooter

1042 exit 0

1043 fi

1044 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1046 encpswd=`echo $newpswd|mypwhash`

1047 dbsetbyid user $user pswd "$encpswd"

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

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

1050 $msgdir/mail-newaccount.m4 \

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

1052 | smail $user "New Account"

1053 }

1054 checkauth() {

1055 user=`getpar user`

1056 skc=`getpar skey` # from cookie

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

1058 skey="`getpwfield $user skey`"

1059 err user=$user skey=$skey

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

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

1062 return 0

1063 fi

1064 fi

1065 pswd=`getpar pswd`

1066 quser=`sqlquotestr "$user"`

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

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

1069 return 1

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

1071 wasureta $user

1072 return 1 # wasureta error

1073 fi

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

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

1076 if pwcheck "$pswd"; then

1077 newsession=`genrandom 50`

1078 dbsetbyid user $user skey "$newsession"

1079 gencookie "user=$user" "skey=$newsession"

1080 return 0

1081 fi

1082 return 2 # Password mismatch

1083 }

1084 showlogin() {

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

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

1087 $layout/login.m4.html

1088 exit 0

1089 }

1090 dologin() {

1091 checkauth

1092 st=$?

1093 if [ $st != 0 ]; then

1094 contenttype; echo

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

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

1097 showlogin # and EXIT

1098 fi

1099 }

1100

1101 # Do instant jobs here

1102 dbsetup

1103 trap cleanup INT HUP EXIT TERM PIPE

1104 # trap cleanup INT HUP

1105

1106 err() {

1107 echo "$@" 1>&3

1108 }

1109

1110 cgiinit() {

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

1112 tmpf=tmp/stream

1113 tmpd=`tmpd=$tmpdir mktempd`

1114 tmpfiles=$tmpfiles" $tmpd"

1115 addsession $session

1116 getcookie

1117 case "$REQUEST_METHOD" in

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

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

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

1121 # cat > $tmpf # too much?

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

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

1124 s="`cat tmp/stream`"

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

1126 ;;

1127 esac

1128 case "$CONTENT_TYPE" in

1129 *boundary*)

1130 bndry=${CONTENT_TYPE#*boundary=}

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

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

1133 do

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

1135 #echo u=$us

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

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

1138 # err k=$k v=$v

1139 case "$k" in

1140 *:filename)

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

1142 (echo k=$k; ls -lF $tmpd/$v; file --mime-type $tmpd/$v) 1>&3

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

1144 [Ii]mage/x-xcf)

1145 bzip2 $tmpd/$v

1146 v=${v}.bz2

1147 ;;

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

1149 [Ii]mage/*)

1150 mogrify -resize $maximagexy'>' $tmpd/$v

1151 ;;

1152 esac

1153 ;;

1154 *)

1155 type='string'

1156 ;;

1157 esac

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

1159 setpar "$k" "$type" "$v"

1160 done

1161 ;;

1162 *)

1163 setviastring par "$s"

1164 ;;

1165 esac

1166 }

1167 email4group() {

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

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

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

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

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

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

1174 and s.key='email'

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

1176 err `echo $sql`

1177 query "$sql"

1178 }

1179 email4groupbyuid() {

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

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

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

1183 uids=`echo "$@"`

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

1185 sql="WITH

1186 grpemails AS (

1187 SELECT gname, user, val email

1188 FROM grp_mem NATURAL JOIN grp_mem_s

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

1190 useremails AS (

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

1192 FROM user

1193 LEFT JOIN user_s

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

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

1196 FROM useremails u LEFT JOIN grpemails g

1197 ON u.name=g.user

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

1199 err email4gByid `echo $sql`

1200 query "$sql"

1201 }

1202 collectemail() (

1203 # Collect email addresses for group $1

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

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

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

1207 for e; do

1208 if isuser "$e"; then

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

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

1211 else

1212 qgrp=`sqlquote "$e"`

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

1214 gmem="grp_mem"

1215 else

1216 tm=`sqlquote "$TEAM"`

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

1218 fi

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

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

1221 $gmem g left join grp_mem_s s

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

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

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

1225 err CollectEmail: `echo "$sql"`

1226 query "$sql"

1227 fi

1228 done

1229 )

1230 sendinvitation() (

1231 # $1=email

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

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

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

1235 gecos=`gecos`

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

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

1238 _m4 -D_URL_="$urlbase" \

1239 -D_USER_="$name" \

1240 -D_EMAIL_="$1" \

1241 -D_REGIST_="$regist" \

1242 -D_ADMIN_="$admin" \

1243 $msgdir/mail-invite.m4 \

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

1245 return 0

1246 )

1247 emaildomaincheck() {

1248 case "$1" in

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

1250 *@*)

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

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

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

1254 return 2

1255 fi

1256 return 0

1257 ;;

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

1259 esac

1260 }

1261 invite() {

1262 email=`getpar email`

1263 case $email in

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

1265 *@*)

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

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

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

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

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

1271 elif sendinvitation $email; then

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

1273 fi ;;

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

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

1276 esac

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

1278 err addr=$addr

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

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

1281 fi

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

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

1284 $layout/html.m4.html $layout/invite.m4.html

1285 }

1286 regist() {

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

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

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

1290 echo "bye bye" | html p

1291 reutrn

1292 fi

1293 email=`session=$1 getpar invite`

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

1295 cat<<EOF

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

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

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

1299 EOF

1300 return

1301 fi

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

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

1304 # Fake login password to wasureta

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

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

1307 wasureta $email

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

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

1310 addsession $1 # for removal after 1 minute

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

1312 $layout/login.m4.html

1313 return

1314 }

1315 group_safename() {

1316 # Convert $1 to safe group name

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

1318 }

1319 groupupdate() {

1320 gname=`getpar gname`

1321 qgname=`sqlquote $gname`

1322 err Enter:groupupdate

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

1324 # See ALSO same job in showgroup()

1325 newgname=`group_safename "$gname"`

1326 err newgname=$newgname

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

1328 err NewGNAME: gname=$newgname

1329 gname=$newgname

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

1331 replpar gname string "$gname"

1332 fi

1333 # Name confliction check

1334 parow=`getpar rowid`

1335 err parow=$parow

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

1337 query "BEGIN EXCLUSIVE;"

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

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

1340 if [ $count -gt 0 ]; then

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

1342 query "END;"

1343 return

1344 fi

1345 par2table $formdir/grp.def

1346 query "END TRANSACTION;"

1347 # Remove orphan

1348 : <<EOF

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

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

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

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

1353 EOF

1354 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1358 sql="delete from blog where id in

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

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

1361 query "$sql";

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

1363 return # and return

1364 fi

1365 fi

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

1367 fi

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

1369 grid=$(query $sql)

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

1371 grp $grid

1372 }

1373 groupman() {

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

1375

1376 GF_STAGE="grpconf"

1377 GF_STAGE=groupupdate

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

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

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

1381 -D_DUMPTABLE_="syscmd(cat)" \

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

1383 }

1384 userconf() {

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

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

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

1388 }

1389 groupconf() {

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

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

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

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

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

1395 err gcon \$1=$1 rowid=$rowid

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

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

1398 }

1399 mems() {

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

1401 kwd=`getpar kwd`

1402 listmember $kwd

1403 }

1404 grps() {

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

1406 kwd=`getpar kwd`

1407 listgroup $kwd \

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

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

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

1411 $layout/form+dump.m4.html

1412 }

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

1414 gpg=`getpar grp`

1415 grid=${1:-$gpg}

1416 grp=`getgroupbyid "$grid"`

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

1418 ## . ./s4-blog.sh

1419 jg=`getpar joingrp`

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

1421 err jg=$jg, grp=$grp

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

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

1424 fi

1425 htmlheader=$layout/html.m4.html

1426 showgroup "$grid"

1427 }

1428 listnewblogsql() { # $1=user

1429 cat<<EOF

1430 `sql4readableblogs`

1431 with article_ctime as (

1432 select id,blogid,author,val ctime

1433 from article join article_s s using(id)

1434 where s.key='ctime'

1435 ), blog_title_owner as (

1436 select blg.rid brid, id,

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

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

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

1440 ), acclog_user as (

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

1442 ), blogall as (

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

1444 from blog_title_owner b, article_ctime ac

1445 on b.id=ac.blogid

1446 )

1447 select brid LINK,

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

1449 coalesce((SELECT time FROM acclog_user

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

1451 < ctime

1452 ) "新着",

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

1454 /* owner gecos */

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

1456 from blogall bl left join acclog_user l

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

1458 group by bl.id

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

1460 limit 10;

1461 EOF

1462 }

1463

1464 search_form() {

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

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

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

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

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

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

1471 cat<<-EOF

1472 <div class="fr">

1473 <form action="$myname">

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

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

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

1477 </form>

1478 </div>

1479 EOF

1480 }

1481

1482 imgsrc_cache() (

1483 # $1 = directory for cache'ing

1484 # $2 = table

1485 # $3 = condition for choosingowner

1486 dir="$1" tbl="$2" whos="$3 ORDER BY rowid LIMIT 1"

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

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

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

1490 valtype=`query "$sql0"`

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

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

1493 cacheimg=$dir/$filename

1494 sumfile="$cacheimg.sum"

1495 tmpf=$tmpd/imgsrc_cache.$$

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

1497 if [ x"`cat $sumfile`" = x"$sum" ]; then

1498 # if cache is fresh,

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

1500 else

1501 fmt=${filename##*.}

1502 cat $tmpf | unhexize \

1503 | convert -define ${fmt}:size=${maxiconxy}x${maxiconxy} \

1504 -resize ${maxiconxy}x${maxiconxy}'>' - $cacheimg &

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

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

1507 echo '">'

1508 echo "$sum" > $sumfile

1509 fi

1510 )

1511

1512 showhome() {

1513 # $1=userRowIdToShow

1514 err showhome \$1=$1

1515 case "$1" in

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

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

1518 esac

1519 err ShowHome: uname=$uname

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

1521 gecos=`gecos "$uname"`

1522 err SH:gecos=$gecos

1523 GF_VIEWONLY=1

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

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

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

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

1528 # Display folders

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

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

1531 and type like 'file:%';"

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

1533 nfile=`query "$sql"`

1534 # err nfile=$nfile

1535 if [ $nfile -gt 0 ]; then

1536 conflink="$conflink /

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

1538 fi

1539 fi

1540 . ./s4-blog.sh

1541

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

1543 search_form > $sf

1544 echo "$gecos さん" > $tf

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

1546 viewtable $formdir/user.def user $1

1547 echo "</div>"

1548 } > $pf

1549

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

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

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

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

1554 if true; then

1555 tbl=user_m

1556 enticond="name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

1557 imgsrc_cache "$td/main" user_m "$enticond"

1558 else

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

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

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

1562 echo '">'

1563 }

1564 fi > $imf

1565 fi

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

1567 val='$uname';"`

1568 listblog $uname > $bf

1569

1570 hometail=$tmpd/tail.$$

1571 mkfifo $hometail

1572

1573 #Calling listgroupbytable, originally here

1574

1575 (

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

1577 # Display Most Recent Entry

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

1579 shortval=${shortval:-val}

1580

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

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

1583 ctime,

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

1585 (SELECT gecos FROM gecoses

1586 WHERE name=(SELECT val FROM blog_s

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

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

1589 FROM blog b

1590 JOIN

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

1592 FROM article a, article_s s

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

1594 GROUP BY blogid ORDER BY val DESC LIMIT 50

1595 ) m

1596 ON b.id=m.blogid;"

1597 # This should be as follows

1598 : <<EOF

1599 WITH arts AS(

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

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

1602 FROM article a NATURAL JOIN article_s s

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

1604 GROUP by s.id

1605 )

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

1607 FROM arts a0

1608 JOIN

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

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

1611 ORDER BY ctime DESC LIMIT 50;

1612 EOF

1613

1614 cat<<-EOF

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

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

1617 <div class="lcto">

1618 `DT_VIEW=replyblog dumptable html blog`

1619 </div>

1620 EOF

1621 unset DT_SQL

1622 # Display NEWS

1623 if false; then

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

1625 new10=`DT_CHLD=article:blogid \

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

1627 else

1628 # 2016-06-26

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

1630 acclog blog $blogreadflagrowid

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

1632 fi

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

1634 sql=`listnewblogsql "$user"`

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

1636 fi

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

1638 cont=$((cont-1))

1639 err newcount=$cont

1640 if [ $cont -gt 0 ]; then

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

1642 cgi_radio foldtabs yes 'id="new10"'

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

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

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

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

1647 `cgi_submit '確定'`

1648 `cgi_hidden read yes`

1649 EOF

1650 echo "$new10 "

1651 echo "</div>"

1652 fi

1653 fi

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

1655 #

1656 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

1662 -D_NBLOG_="$nblog" \

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

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

1665 $layout/html.m4.html $layout/home.m4.html

1666

1667 # Record access log

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

1669 }

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

1671 contenttype; echo

1672 err commission: "$@"

1673 gname=`getgroupbyid $1`

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

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

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

1677 grp_reg_adm "$@"

1678 else

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

1680 fi

1681 }

1682 listgroupbytable() {

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

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

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

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

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

1688 NGsql="select distinct tag from\

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

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

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

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

1693 err ListGRP: query sql="$sql"

1694 for tag in `query "$sql"`

1695 do

1696 err ListGrp: tag=$tag

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

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

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

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

1701 as rowid,

1702 gname,

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

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

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

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

1707 err PersonalGroupList= `echo $sql`

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

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

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

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

1712 echo '</table>'

1713 done

1714 echo '</div>'

1715 }

1716 iconhref() (

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

1718 data=`percenthex $1`

1719 ct=`file --mime-type $1|cut -d' ' -f2`

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

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

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

1723 )

1724 iconhref2() (

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

1726 src=$1

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

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

1729 )

1730 listentry() (

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

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

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

1734 cond='' hiddens=''

1735 offset=`getpar offset`

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

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

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

1739 limit=30

1740 dir=`getcachedir "$1"`

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

1742 hrb="$myname?home"

1743 deficon=person-default.png

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

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

1746 gcs=gecos

1747 else # if group

1748 hrb="$myname?grp"

1749 deficon=group-default.png

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

1751 gcs=name

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

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

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

1755 err tagconv=$tagconv

1756 fi

1757 fi

1758 if [ ! -d $dir ]; then

1759 mkdir -p $dir

1760 fi

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

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

1763 fi

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

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

1766 fi

1767

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

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

1770 ##qgrp=`sqlquote $grp`

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

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

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

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

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

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

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

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

1779 end

1780 as name,

1781 b.tag,

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

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

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

1785 then '(ADMIN)'

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

1787 then '(Member)'

1788 when '$iamowner' = '' then ''

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

1790 CASE '$entity'

1791 WHEN 'グループ'

1792 THEN coalesce(

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

1794 'open')

1795 ||

1796 CASE WHEN '$user'

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

1798 THEN ' ismember'

1799 ELSE ''

1800 END

1801 ELSE 'user'

1802 END regmode

1803 from $tbl a left join

1804 (select $nm as name,

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

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

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

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

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

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

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

1812 # Note that mtime is stored only in grp_s.

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

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

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

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

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

1818 cgi_form $stage <<EOF

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

1820 `cgi_text kwd $kwd`</label>

1821 EOF

1822 echo '</div>'

1823 fi

1824 hiddens="$hiddens

1825 `cgi_hidden kwd \"$kwd\"`

1826 `cgi_hidden stage \"$stage\"`"

1827 cat<<EOF

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

1829 EOF

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

1831 nextbtn=$(

1832 cat<<EOF

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

1834 `cgi_submit 次の${limit}件`

1835 $hiddens

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

1837 EOF

1838 )

1839 fi

1840 if [ $offset -gt 0 ]; then

1841 prevbtn=$(

1842 cat<<EOF

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

1844 `cgi_submit 前の${limit}件`

1845 $hiddens

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

1847 EOF

1848 )

1849 fi

1850 pnbtn="$nextbtn$prevbtn"

1851 echo $pnbtn

1852

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

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

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

1856 err name=$name owner=$ownerp lnk=$lnk

1857 err newlnk=$lnk regmode=$regmode

1858 icondir=$dir/$id

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

1860 # Pick up only first icon

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

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

1863 | _m4 $tagconv

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

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

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

1867 else

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

1869 fi

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

1871 echo "</div>"

1872 done

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

1874 )

1875 listmember() {

1876 listentry user "$@"

1877 }

1878 listgroup() {

1879 listentry group "$@"

1880 }

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

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

1883 query "SELECT DISTINCT hex(val) FROM grp_mem_m

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

1885 }

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

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

1888 grid=`getpar grid`

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

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

1891 else

1892 grid=$1

1893 fi

1894 grp=`getgroupbyid $grid`

1895 qgrp=`sqlquote $grp`

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

1897 if isgroup "$grp"; then

1898 tf=$tmpd/title.$$

1899 bodyclass=`query "SELECT val FROM grp_s

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

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

1902 ismember="ismember"

1903 qgrp=`sqlquote $grp`

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

1905 else

1906 ismember="" # bodyclass="group"

1907 fi

1908 bodyclass="$bodyclass grouphome"

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

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

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

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

1913 -D_BODYCLASS_="$bodyclass" \

1914 -D_DUMPTABLE_="" \

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

1916 # $htmlheader is defined in grp()

1917 else # if $grp is removed at par2table

1918 listgroup

1919 fi

1920 }

1921 showgroupsub() {

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

1923 # Using $ismember

1924 rowid=$2

1925 grp=`getgroupbyid $2`

1926 qgrp=`sqlquote $grp`

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

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

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

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

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

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

1933 return

1934 fi

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

1936 enticond="gname=$qgrp AND key='profimg' AND type LIKE 'file:image%'"

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

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

1939 cat<<-EOF

1940 <p class="groupimg">

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

1942 EOF

1943 fi

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

1945 viewtable $1 grp $rowid

1946 echo "</div>"

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

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

1949 iamowner=$rowid

1950 fi

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

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

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

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

1955 cat<<EOF

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

1957 <div class="fold clear">

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

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

1960 <div>

1961 `cgi_textarea message "" "cols=60"`

1962 `cgi_submit 送信`

1963 `cgi_reset リセット`

1964 </div>

1965 `cgi_hidden grp $rowid`

1966 </div></form>

1967 EOF

1968 fi

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

1970 err ismember $user $grp

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

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

1973 else

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

1975 fi

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

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

1978 and key='email';"`

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

1980 and key='email';"

1981 err email=$eml

1982 cat <<EOF

1983 <div class="fold clear">

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

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

1986 <div>

1987 EOF

1988 cgi_form grp <<EOF

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

1990 <table class="b">

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

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

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

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

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

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

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

1998 </table>

1999 `cgi_hidden grp $rowid`

2000 EOF

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

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

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

2004 fi

2005 echo '</div></div>'

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

2007 cgi_form searchart<<EOF

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

2009 `cgi_hidden owner $grp`

2010 EOF

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

2012 DT_CHLD=article:blogid \

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

2014

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

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

2017 cm="?commission+$rowid"

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

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

2020 # team list

2021 hexteams=`hexteams "$grp"`

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

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

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

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

2026 SELECT val TEAM,

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

2028 MEMBERS

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

2030 EOF

2031 echo '</table></div>'

2032 fi

2033 }

2034 grp_getbodyclass() {

2035 # Get css class name for document.

2036 # `moderated' for moderated groups

2037 # `ismember' for groups where user belongs

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

2039 # $user=userNameCurrentlyLogin

2040 err grp_getbodyclass: 1="$1"

2041 qgrp=`sqlquote "$1"`

2042 query<<-EOF

2043 SELECT coalesce(

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

2045 'open')

2046 ||

2047 CASE WHEN '$user'

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

2049 THEN ' ismember'

2050 ELSE ''

2051 END;

2052 EOF

2053 }

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

2055 err GRP_ACTION:IN

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

2057 grp=`getgroupbyid "$grid"`

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

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

2060 fi

2061 if ! ismember $user $grp; then

2062 echo "加入者のみに許可された操作です。" | html p; return

2063 fi

2064 echo "グループ $grp 個別選択操作" \

2065 | _m4 -D_TITLE_="syscmd(\`cat')" \

2066 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2067 $layout/html.m4.html

2068

2069 isowner=""

2070 isgrpowner "$user" "$grp" && isowner="yes"

2071 usel=`getpar usel`

2072 if [ -n "$usel" ]; then

2073 uids=$(echo `echo $usel`|tr ' ' ',')

2074 err grpaction-1: grp=$grp, `echo $sql`

2075 text=`getpar text`

2076

2077 rm=`getpar rm` cfm=`getpar confirm`

2078 err rm=$rm cfm=$cfm

2079 if [ x"$rm" = x"yes" ]; then

2080 if [ "$isowner" ]; then

2081 if [ x"$rm$cfm" = x"yesyes" ]; then

2082 # Eliminate

2083 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2084 for tbl in grp_mem grp_mem_s grp_mem_m; do

2085 sql="delete from $tbl $cond;"

2086 # echo "sql=$sql"

2087 query "$sql"

2088 err rmGRPuser "$sql"

2089 done

2090 num=`query "select count(*) from user where rowid in ($uids);"`

2091 #err num=$num

2092 if [ 0$num -gt 0 ]; then

2093 sql="select coalesce(b.val,a.name) from user a left join \

2094 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2095 # err `echo "$sql"`

2096 html pre<<EOF

2097 以下の${num}名のグループ $grp 登録を解除しました。

2098 `query "$sql"`

2099 EOF

2100 fi

2101 else

2102 echo "確認のチェックがないのでやめておきます。" | html p

2103 return

2104 fi

2105 else # not Group Owner

2106 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2107 return

2108 fi

2109 cat<<EOF

2110

2111 EOF

2112 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2113 if [ -z "$text" ]; then # if msg is empty

2114 echo "なにかメッセージを..." | html p

2115 return 0

2116 fi

2117 gecos=`gecos $user`

2118 mkfrom=`getpar mkfrom`

2119 if [ x"$mkfrom" = x"yes" ]; then

2120 safegc=`echo "$gecos" | tr -d '<>@'`

2121 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2122 fromad=`email4groupbyuid "$grp" "$myuid"`

2123 mail_from="$safegc <$fromad>"

2124 else

2125 mail_from="$admin"

2126 fi

2127 MAIL_FROM=$mail_from \

2128 smail "`email4groupbyuid "$grp" $usel` $user" \

2129 "$gecos さんからのメッセージ" <<EOF

2130 $url

2131 のグループ「$grp」のメンバーである $gecos さんから、

2132 あなた宛へのメッセージです。

2133 ----------------------------------------------------------

2134 $text

2135 EOF

2136 if [ $? = 0 ]; then

2137 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2138 sql="select coalesce(b.val, a.name) from

2139 (select name from user where rowid in ($uids)) a

2140 left join user_s b on a.name=b.name and b.key='gecos';"

2141 html pre<<EOF

2142 `query "$sql"`

2143 (送信者である $gecos さんも含まれます)

2144 EOF

2145 err SendDone: `echo $sql`

2146 fi

2147 elif [ x"$rm" = x"commission" ]; then

2148 grp_reg_adm $grid $usel

2149 elif [ x"$rm" = x"addteam" ]; then

2150 team=`getpar team|sed "s/'/''/g"` # for single quotation

2151 newteam=`echo "$team"|tr -d ,`

2152 if [ x"$team" != x"$newteam" ]; then

2153 echo "チーム名に使えない文字を除去しました" | html p

2154 team=newteam

2155 fi

2156 if [ -z "$team" -o x"$team" = x"なし" ]; then

2157 cat<<-EOF | html p

2158 有効なチーム名を入力してください。

2159 カンマだけ、「なし」という名前は使えません。

2160 EOF

2161 echo "有効なチーム名を入力してください。" | html p

2162 else

2163 grp_add_team $grid "$team" $usel

2164 fi

2165 elif [ x"$rm" = x"rmteam" ]; then

2166 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2167 rmteam=`getpar rmteam|sed "s/'/''/g"`

2168 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2169 gname='$grp' AND user='$user' AND key='team'\

2170 AND val='$rmteam';\"`" ]; then

2171 grp_rm_team $grid "$rmteam" $usel

2172 else

2173 echo "所属していないチームの除去操作はできません。"|html p

2174 fi

2175 else

2176 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2177 fi

2178 fi

2179 fi

2180 # New entry

2181 sql="select /* Ahh, ugly SQL, I wanna fix... */

2182 case

2183 when (select user from grp_adm where

2184 gname=(select gname from grp where rowid=$grid)

2185 and user=a.name) is not null

2186 then 'k'

2187 else ''

2188 end || a.rowid||

2189 ','||a.gecos as NAME,

2190 (SELECT count(author) /* Put post count for scoring 2016-08-01 */

2191 FROM article NATURAL JOIN article_s

2192 WHERE blogid IN

2193 (SELECT id FROM blog_s

2194 WHERE key='owner'

2195 AND val=(SELECT gname FROM grp where rowid=$grid))

2196 AND author=a.name AND key='text')

2197 as POST,

2198 (SELECT group_concat(val, ',')

2199 FROM grp_mem_m

2200 WHERE gname='$grp' AND user=a.name AND key='team') as TEAM

2201 FROM

2202 gecoses a

2203 WHERE name in (select user from grp_mem where

2204 gname=(select gname from grp where rowid=$grid))

2205 ORDER by a.gecos;"

2206 err grpaction: "`echo \"$sql\"`"

2207 tf=$tmpd/title.$$

2208 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2209 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2210 for=\"cmadmin\">グループ管理者委任</label>

2211 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2212 </p></div>"

2213 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2214 for=\"conf\">グループ登録解除</label>

2215 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2216 <p>この操作による通知は本人に行きません。

2217 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2218 </div>"

2219 # Get team list to which current user belongs into $hexteams

2220 myhexteams=$(hexteams "$grp" "$user")

2221 allhexteams=$(hexteams "$grp")

2222 if [ -n "$myhexteams" ]; then

2223 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2224 for=\"cmrmteam\">チーム属性除去</label>

2225 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2226 を除去します: `cgi_checkbox teamconfirm yes` 確認

2227 <p>この操作による通知は本人に行きません。

2228 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2229 </div>

2230 "

2231 fi

2232 b1='<label> <input type="checkbox" name="usel" value="'

2233 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2234 #b2='"> <span>' b3='</span></label>'

2235 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2236 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2237 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2238 cgi_form grpaction<<EOF \

2239 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2240 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2241 | _m4 -D_TITLE_="spaste(\`$tf')" \

2242 -D_SUBTITLE_="チェック後操作ボタン" \

2243 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2244 $layout/form+dump.m4.html

2245 <p>下でチェックした人を対象として:</p>

2246 <div class="foldtabs">

2247 `cgi_radio rm addteam 'id="cmteam"'`<label

2248 for="cmteam">同じチーム属性を付与</label>

2249 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2250 `cgi_datalist_h teams $allhexteams`

2251 </div>

2252 ${rmteammsg}

2253 `cgi_radio rm send id="sendmsg"`<label

2254 for="sendmsg">メッセージ送信</label>

2255 <div>

2256 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2257 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2258 `cgi_textarea text "" cols=40`

2259 </div>

2260 ${isowner:+$cmmsg$excmsg}

2261 `cgi_radio rm close id="x"`<label for="x">×</label>

2262 </div>

2263 <h4>$grp 参加者一覧</h4>

2264 <table class="td2r">

2265 `sq $db -header -html "$sql"`

2266 </table>

2267 `cgi_hidden grp $grid`

2268 EOF

2269 }

2270 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2271 # Create TEMPORARY VIEW

2272 query<<EOF

2273 CREATE TEMPORARY VIEW writeusers AS

2274 SELECT DISTINCT author FROM article

2275 WHERE id in (

2276 select id from article where blogid=(select id from blog where rowid=$1)

2277 );

2278 CREATE TEMPORARY VIEW movablegroups AS

2279 SELECT g.rowid growid , g.gname

2280 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2281 ON grp.gname=gm.gname -- そのユーザが属している

2282 AND user='$user') g -- グループに絞る

2283 WHERE (SELECT author FROM writeusers

2284 EXCEPT

2285 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2286 IS NULL;

2287 $2

2288 EOF

2289 }

2290 sql4readableblogs() {

2291 # Create view of blogs that can be readable to $user

2292 # Blog is readable when:

2293 # 1: blog owner is an user

2294 # 2: else, 2.1: owner-group where the $user belongs

2295 # 2.2: else, owner-group is not moderated

2296 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2297 cat<<EOF ## | tee tmp/sql.out

2298 CREATE TEMPORARY VIEW readableblogs AS

2299 SELECT blog.rowid rid, id, author

2300 FROM blog

2301 NATURAL JOIN

2302 (SELECT id, val owner FROM blog_s WHERE key='owner') bs

2303 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2304 THEN 1 -- blog owner is an user, READABLE

2305 WHEN (SELECT val FROM grp_s

2306 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2307 AND

2308 (SELECT user FROM grp_mem

2309 WHERE gname=bs.owner AND user='$user') IS NULL

2310 THEN 0

2311 ELSE 1

2312 END;

2313 EOF

2314 }

2315 editheading() { # $1=rowid-of-heading

2316 rowid=${1%%[!A-Z0-9a-z_]*}

2317 if [ -z "$rowid" ]; then

2318 echo "話題番号が未指定です。" | html p

2319 return

2320 fi

2321 owner=`getvalbyid blog owner $rowid`

2322 title=`getvalbyid blog title $rowid`

2323 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2324 | _m4 -D_TITLE_="修正" \

2325 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2326 -D_BLOGS_="" -D_DUMPTABLE_="" \

2327 -D_FORM_="syscmd(\`cat')" \

2328 $layout/html.m4.html $layout/form+dump.m4.html

2329 # Move to group

2330 if isuser "$owner"; then

2331 crview4article $rowid

2332 n=`query "SELECT count(*) FROM writeusers;"`

2333 err N=$n

2334 if [ $((n)) -gt 0 ]; then

2335 err ROWID=$rowid

2336 sql="SELECT growid || ':' || gname FROM movablegroups;"

2337 cat<<-EOF

2338 <div class="fold">

2339 `cgi_checkbox mv send id="mv"`<label

2340 for="mv">この話題をグループ所有に移動する</label>

2341 <div>

2342 <form action="?mvart" method="POST" enctype="multipart/form-data">

2343 移動先グループ:

2344 <select name="mv2grp">

2345 EOF

2346 query ".mode html"

2347 query<<-EOF |

2348 $sql

2349 .mode list

2350 EOF

2351 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2352 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2353 cat<<-EOF

2354 </select>

2355 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2356 そのグループに加入しているものに限られます)</p>

2357 <p>`cgi_checkbox cfm yes`<label>確認

2358 (この操作は元に戻すことができません)</label></p>

2359 `cgi_hidden blogrowid $rowid`

2360 `cgi_submit 移動`

2361 `cgi_reset Reset`

2362 </form>

2363 </div>

2364 </div>

2365 EOF

2366 fi

2367 # end of isuser "$owner"

2368 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2369 [ -n "$hexteams" ];}; then

2370 none="`echo なし|hexize`"

2371 cat<<-EOF

2372 <div class="fold">

2373 `cgi_checkbox mv2team send id="mv2team"`<label

2374 for="mv2team">この話題を以下のチームのものにする</label>

2375 <div><p>現在の所属チーム設定:

2376 `query "SELECT

2377 coalesce((SELECT val FROM blog_s

2378 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2379 AND key='team'),

2380 ':なし');"`</p>

2381 <form action="?mvart" method="POST" enctype="multipart/form-data">

2382 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2383 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2384 `cgi_hidden blogrowid $rowid`<br>

2385 `cgi_submit 移動`

2386 `cgi_reset Reset`

2387 </form></div></div>

2388 EOF

2389 fi

2390 }

2391 mvart() { # move diary to some group or team

2392 # or move blog of group to team which belong to the group

2393 blogrowid=`getpar blogrowid`

2394 cfm=`getpar cfm`

2395 ##### echo move blog:$blogrowid to $mv2grp | html p

2396 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2397 . ./s4-blog.sh

2398 if [ -z "$blogrowid" ]; then

2399 echo "無効な指定です(mvart)。" | html p

2400 return

2401 elif [ x"$cfm" != x"yes" ]; then

2402 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2403 elif { mv2grp=`getpar mv2grp`

2404 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2405 [ -n "$mv2grp" ]; }; then

2406 crview4article $blogrowid

2407 ########## TRANSACTION BEGIN

2408 query "BEGIN;"

2409 n=`query "SELECT count(*) FROM writeusers;"`

2410 err Nwriteuser=$n

2411 if [ $((n)) -gt 0 ]; then

2412 query<<-EOF

2413 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2414 WHERE key='owner'

2415 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2416 AND $mv2grp IN (SELECT growid FROM movablegroups);

2417 EOF

2418 fi

2419 query "END;"

2420 ########## TRANSACTION END

2421 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2422 [ -n "$mv2team" ];}; then

2423 # blog owner can move it to ANY team

2424 case "$mv2team" in

2425 'なし')

2426 cat<<-EOF

2427 DELETE FROM blog_s

2428 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2429 AND key='team';

2430 EOF

2431 ;;

2432 "") ;;

2433 *)cat<<-EOF

2434 BEGIN;

2435 REPLACE INTO blog_s(id, key, val)

2436 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2437 'team', '$mv2team');

2438 REPLACE INTO blog_s(id, key, val)

2439 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2440 'notify', 'all'); -- Change notify to all

2441 END;

2442 EOF

2443 esac | query

2444 fi

2445 blog_reply $blogrowid

2446 echo yes | html p

2447 }

2448 editart() { # $1=article-rowid $2=blogrowid

2449 rowid=${1%%[!A-Z0-9a-z_]*}

2450 blogrowid=${2%%[!A-Z0-9a-z_]*}

2451 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2452 echo "表示する記事番号が未指定です。" | html p

2453 return

2454 fi

2455 owner=`getvalbyid blog owner $blogrowid`

2456 title=`getvalbyid blog title $blogrowid`

2457 author=`getvalbyid article author $rowid`

2458 err EDITart: owner=$owner, author=$author

2459 if isgrpowner $user $owner; then

2460 : EDIT OK

2461 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2462 echo "本人か所有者しか編集できません." | html p

2463 return

2464 fi

2465 aid=`query "select id from article where rowid=$rowid;"`

2466 tmpout=$tmpd/editart.$$.out

2467 GF_ACTION="?replyblog+$blogrowid#$aid" \

2468 edittable $formdir/article.def article $rowid \

2469 > $tmpout

2470 rm -f /tmp/editart.out

2471 # Cannot use pipelining to m4 with genform() because of stdin stack

2472 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2473 -D_FORM_="syscmd(cat $tmpout)" \

2474 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2475 -D_BLOGS_= -D_DUMPTABLE_= \

2476 $layout/html.m4.html $layout/form+dump.m4.html

2477 }

2478 send2mem() {

2479 rowid=`getpar grp`

2480 if [ -z "$rowid" ]; then

2481 echo "グループが未指定です。" | html p

2482 return

2483 fi

2484 message=`getpar message`

2485 if [ -z "$message" ]; then

2486 echo "文章を入れてください。" | html p

2487 return

2488 fi

2489 grp=`getgroupbyid $rowid`

2490 members=`collectemail $grp`

2491 # smail rcpt subj (file)

2492 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2493 $urlbase?grp+$rowid

2494 グループ $grp に所属する

2495 `gecos $user` さんよりメッセージ:

2496

2497 $message

2498 EOF

2499 cat<<EOF

2500 <p>以下の宛先に送信しました。</p>

2501 <pre>

2502 $members

2503 </pre>

2504 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2505 EOF

2506 }

2507 joingrpadmit() {

2508 # $1=yes/no $2=session-key

2509 if [ -z "$2" ]; then

2510 echo "bye bye" | html p; return

2511 fi

2512 t_usr=`session=$2 getpar user`

2513 t_grp=`session=$2 getpar group`

2514 err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2515 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2516 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2517 echo "無効な加入依頼です。" | html p

2518 echo "有効期限が切れたか、

2519 他の管理者がいる場合は処理済みの可能性があります。" | html p

2520 return

2521 fi

2522 if ! isgrpowner "$user" $t_grp; then

2523 echo "グループ管理者のみの機能です。" | html p; return

2524 fi

2525 case $1 in

2526 yes) joingrp "$t_grp" "$t_usr" yes ;;

2527 no) joingrp "$t_grp" "$t_usr" no ;;

2528 *)

2529 echo "無効な指定です($1)。" | html p

2530 return ;;

2531 esac

2532 gid=$(query "select rowid from grp where gname=`sqlquote $t_grp`;")

2533 rcpts="`getgroupadminmails $t_grp` $user"

2534 err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2535 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2536 に

2537 $t_usr

2538 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2539 しました。"

2540 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2541 query "delete from session where id='$2';"

2542 echo "$body" | html p

2543 }

2544

2545 joingrprequest() {

2546 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2547 jss="joingrp-`date +%s`-`genrandom 12`"

2548 addsession $jss +${memoplimitdays}days

2549 query "replace into par values('$jss', 'group', 'string', `sqlquote $1`),

2550 ('$jss', 'user', 'string', `sqlquote $user`);"

2551 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2552 $url

2553 $user さんから

2554 グループ $1

2555 に加入依頼がありました。

2556

2557 承認する:

2558 $urlbase?joingrpadmit+yes+$jss

2559

2560 白紙に戻す:

2561 $urlbase?joingrpadmit+no+$jss

2562 EOF

2563 echo "管理者に加入依頼を出しました。

2564 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2565 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2566 }

2567 joingrp() {

2568 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2569 err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2570 if isgrpowner "$user" "$1"; then

2571 isowner="yes"

2572 elif [ -n "$5" ]; then

2573 isowner="yes"

2574 else

2575 isowner=""

2576 fi

2577 err jg:isgrpowner: isowner="$isowner"

2578 if [ -n "$isowner" ]; then

2579 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2580 elif [ x"$2" != x"$user" ]; then # if user is not login user

2581 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2582 return

2583 elif [ x"$3" = x"no" ]; then

2584 : # Do not pursue those who leave

2585 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2586 : # Member can change own email address for the joining moderated group

2587 else # adding user is $user itself

2588 case `getgroupattr $1 regmode` in

2589 moderated)

2590 joingrprequest "$@" # Request only

2591 return

2592 ;;

2593 *)

2594 ;;

2595 esac

2596 fi

2597 qgname=`sqlquote $1`

2598 cond="where gname=$qgname and user='$2'"

2599 if [ x"$3" = x"yes" ]; then

2600 query "replace into grp_mem values($qgname, '$2');"

2601 if [ -n "$4" ]; then

2602 if msg=`emaildomaincheck "$4"`; then

2603 err "replace into grp_mem_s values($qgname, '$user', 'email', \

2604 'string', '$4', NULL);"

2605 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2606 'string', '$4', NULL);"

2607 else

2608 echo $msg

2609 fi

2610 else

2611 query "delete from grp_mem_s $cond and key='email';"

2612 fi

2613 if [ -n "$5" ]; then # as ADMIN

2614 # Coming here means newly created group

2615 sql="select case\

2616 when (select count(*) from grp_mem where gname=$qgname)=1\

2617 then (select user from grp_mem\

2618 where gname=$qgname and user='$user')\

2619 else '' end; "

2620 err NewGrpChk: $sql

2621 if [ -n "`query \"$sql\"`" ]; then

2622 err ADMIN: "replace into grp_adm values($qgname, '$user');"

2623 query "replace into grp_adm values($qgname, '$user');"

2624 fi

2625 fi

2626 else

2627 query "delete from grp_mem $cond;

2628 delete from grp_mem_s $cond;

2629 delete from grp_mem_m $cond;"

2630 fi

2631 }

2632 grp_add_team() (

2633 # $1=grp-rowid $2=team $3...=user-rowid(s)

2634 grp=`getgroupbyid $1`

2635 team=$2; shift; shift

2636 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2637 { echo "BEGIN;"

2638 for user; do

2639 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2640 '$grp',\

2641 (SELECT name FROM user WHERE rowid=$user),\

2642 'team', 'string', '$team');"

2643 done

2644 echo "END;"

2645 } | query

2646 )

2647 grp_rm_team() (

2648 # $1=grp-rowid $2=team $3...=user-rowid(s)

2649 grid=$1

2650 qgrp=$(sqlquote "`getgroupbyid $grid`")

2651 team=$2; shift; shift

2652 [ -z "$grid" -o -z "$team" ] && return

2653 { echo "BEGIN;"

2654 for user; do

2655 echo "DELETE FROM grp_mem_m\

2656 WHERE gname=$qgrp \

2657 AND user=(SELECT name FROM user WHERE rowid=$user)\

2658 AND key='team' AND val='$team';"

2659 done

2660 cat<<-EOF

2661 DELETE FROM blog_s

2662 WHERE rowid=(

2663 SELECT rowid

2664 FROM blog_s a

2665 WHERE key='team'

2666 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2667 AND NOT EXISTS (SELECT * FROM grp_mem_m

2668 WHERE key='team' AND val=a.val -- a.val=team

2669 AND gname = (SELECT val FROM blog_s b

2670 WHERE a.id=b.id AND key='owner')

2671 ));

2672 EOF

2673

2674 echo "END;"

2675 } | query

2676 )

2677 grp_reg_adm() {

2678 # $1=grp-rowid $2...=user-rowid

2679 grid=$1

2680 grp=`getgroupbyid "$1"`

2681 if [ -z "$grp" ]; then

2682 echo "無効なグループIDです" | html p; return

2683 fi

2684 if ! isgrpowner $user "$grp"; then

2685 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2686 fi

2687 shift

2688 for urid; do

2689 newadm=`query "select name from user where rowid=$urid;"`

2690 if [ -z "$newadm" ]; then

2691 echo "指定ユーザIDがおかしいようです。" | html p; return

2692 fi

2693 err GRP_reg_adm: "replace into grp_adm values(`sqlquote $grp`, '$newadm');"

2694 err ismember $newadm $grp

2695 if ismember $newadm $grp; then

2696 # OK, go ahead

2697 getgname="(select gname from grp where rowid=$grid)"

2698 query "replace into grp_adm values($getgname, '$newadm');"

2699 # confirm insertion

2700 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2701 if [ -n "`query \"$sql;\"`" ]; then

2702 echo "追加完了: $newadm" | html p

2703 else

2704 echo "追加失敗($1 $urid)" | html p

2705 fi

2706 fi

2707 showgroup $grid

2708 done

2709 }

2710 dumptable() {

2711 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2712 # textのフィールドだけ全てダンプにしたほうがいいか

2713 # $DT_VIEW sets link

2714 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2715 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2716 VIEW=${DT_VIEW-replyblog}

2717 if [ -n "$VIEW" ]; then

2718 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VIE</a><a href=\"$myname?$VIEW+\\2#bottom\">W</a>"

2719 fi

2720 # $DT_CHLD=ChildTable:BindColumn

2721 if [ -n "$DT_CHLD" ]; then

2722 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

2723 cntall="(select count($_i) from $_t where $_i=a.id)"

2724 # XXX: Dirty workaround for slow subquery of acclog

2725 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

2726 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

2727 cntnew="(select count(val) from ${_t}_s where key='ctime' \

2728 and id in (select id from $_t where $_i=a.id) \

2729 and val > coalesce((select time from myacclog where \

2730 tblrowid=a.rowid),\

2731 '1970-01-01'))"

2732 cnt="$cntnew as '新着', $cntall as '総数',"

2733 dt_class=" td2r td3r dumpblogs"

2734 fi

2735 # Construct join expression

2736 eav="" scols=""

2737 pk=`gettblpkey $2`

2738 substr=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2739 substr=${substr:-val}

2740 for col in ${3:-`gettbl_s_cols $2`}; do

2741 case $col in

2742 gecos) scols="$scols${scols:+, }${col#}"

2743 continue ;; # built-in column name

2744 *:*) col=${col%:*} as=${col#*:} ;;

2745 *) as=${col} ;;

2746 esac

2747 eav=$eav${eav:+,}" max(case key when '$col' then $substr end) as $as"

2748 scols="$scols${scols:+, }b.$as"

2749 done

2750 #case author when '$user' then a.rowid else '---' end as ID,

2751 sql=${DT_SQL:-"select \

2752 a.rowid as LINK,\

2753 $cnt\

2754 $scols from $2 a left join\

2755 (select $pk,$eav,

2756 max(case key when 'owner'

2757 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

2758 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

2759 err SQL="`echo \"$presql$sql\"|tr -d '\n'`"

2760 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>,"

2761 <div>

2762 <div class="dumptable">

2763 <table class="b$dt_class">

2764 `sq -header -cmd ".mode $1" $db "$presql$sql"`

2765 </table>

2766 </div>

2767 </div>

2768 EOF

2769 }

2770

2771 par2table() (

2772 # copy current parameters of par into destination table

2773 # $1=definition-file

2774 # Using $user and $session

2775 # Return value:

2776 # 0: Stored successfully

2777 # 1: Insufficient fillings

2778 # 2: No permission to modify the record

2779 # 3: Invalid rowid

2780 # 4: SUCCESS to delete

2781 # 5: Stop deletion for lack of confirm check

2782 # 6: Password length too short

2783 # 7: Password mismatch

2784 # 8: Old password incorrect

2785 rowid=`getpar rowid`

2786 err ...........rowid=$rowid

2787 if [ ! -e $1 ]; then

2788 echo "テーブル定義ファイルが見付かりません" | html p

2789 exit 1

2790 fi

2791 tbl=${1%.def}

2792 tbl=${tbl##*/}

2793 if [ -n "$rowid" ]; then # Modify existing entry

2794 if [ x"$tbl" = x"user" ]; then

2795 rowowner=`query "select name from $tbl where rowid=$rowid;"`

2796 elif [ x"$tbl" = x"grp" ]; then

2797 sql="select gname from $tbl where rowid=$rowid;"

2798 ##err p2t:grp:q $sql

2799 isgrpowner $user "`query $sql`" && rowowner=$user

2800 else

2801 rowowner=`query "select owner from $tbl where rowid=$rowid;"`

2802 rowowner=${rowowner:-`query "select author from $tbl

2803 where rowid=$rowid;"`}

2804 fi

2805 ### err rowowner=$rowowner

2806 if [ x"$user" != x"$rowowner" ]; then

2807 echo "他人のレコードはいじれないの" | html p

2808 return 2

2809 elif [ -z "$rowowner" ]; then

2810 echo "指定したレコードはないみたい" | html p

2811 return 3

2812 fi

2813 rm=`getpar rm` cfm=`getpar confirm`

2814 # Editing existent entry

2815 if [ x"$rm" = x"yes" ]; then

2816 if [ x"$rm$cfm" = x"yesyes" ]; then

2817 query "delete from $tbl where rowid=$rowid;"

2818 return 4

2819 else

2820 echo "消去確認のチェックがないので消さなかったの..." | html p

2821 return 5

2822 fi

2823 fi

2824 fi

2825

2826 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

2827 if [ -n "$rowid" ]; then

2828 # Update of existing record

2829 for col in `gettblcols $tbl`; do

2830 val=`getparquote $col`

2831 [ -z "$val" ] && continue

2832 err query "update $tbl set $col=$val where rowid=$rowid"

2833 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

2834 sql="update $tbl set $col=$val where rowid=$rowid;"

2835 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

2836 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

2837 ## User name cannot be changed with interface provided with this

2838 ## script. But we offer the trigger to change owner user

2839 ## of blog_s table.

2840 err "select quote($col) from $tbl where rowid=$rowid;"

2841 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

2842 cat<<-EOF | query

2843 BEGIN;

2844 $sql

2845 update blog_s set val=$val

2846 where key='owner' and val=$old;

2847 COMMIT;

2848 EOF

2849 ## XX: DIRTY Hack Ends here

2850 ## We should keep blog's owner as a single column which has

2851 ## foreign key constraint with primary key of grp/user.

2852 aa

2853 else

2854 query "$sql"

2855 fi

2856 done

2857 # Then, set up $pval for further insertion of tbl_s and tbl_m

2858 for col in `gettblpkey $tbl`; do

2859 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

2860 pval="$pval${pval:+, }\"$val\""

2861 done

2862 else

2863 # New entry

2864 # Generate values() for primary keys

2865 for col in `gettblpkey $tbl`; do

2866 # Genuine primary keys for _m and _s

2867 val=`getvalquote $tbl $col`

2868 [ -z "$val" ] && continue

2869 pval="$pval${pval:+, }$val"

2870 done

2871 err pval=$pval

2872 for col in `gettblfkey $tbl`; do

2873 # args for values() to insertion into master table

2874 val=`getvalquote $tbl $col`

2875 [ -z "$val" ] && continue

2876 formaster=$formaster"${formaster:+, }$val"

2877 done

2878 formaster="$pval${formaster:+, }$formaster"

2879 err formaster=$formaster

2880 if [ -z "$formaster" ]; then

2881 echo "項目を全て埋めてください" | html pre

2882 return 1

2883 fi

2884 err "replace into $tbl values($formaster);"

2885 query "replace into $tbl values($formaster);"

2886 ## Insertion to master table, done

2887 fi

2888

2889 for kt in s m; do

2890 tb2=${tbl}_$kt

2891 for col in `gettbl_${kt}_cols $tbl`; do

2892 ptype=`getpartype $col "limit 1"`

2893

2894 # First, check update of existing entries in _m

2895 if [ $kt = m ]; then

2896 # sessID|address.1.22|string|Somewhere-x.y.z

2897 sql=""

2898 err dots from query "select var from par where var like '$col.%';"

2899 for v in `query "select var from par where var like '$col.%';"`; do

2900 # v=address.1.22

2901 st_rowid=${v##*.}

2902 origcol=${v%%.*} # original column derived from

2903 err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

2904 ##case `getpartype $v` in

2905 err CASE `gettbl_coltype $tbl/$origcol` in

2906 err edit flag = `getpar action.$v`

2907 case `getpar action.$v` in

2908 rm)

2909 if [ x`getpar confirm.$v` = x"yes" ]; then

2910 newsql="delete from $tb2"

2911 else

2912 echo "削除確認未チェック" | html p

2913 fi ;;

2914 edit)

2915 case `gettbl_coltype $tbl/$origcol` in

2916 image|document|binary)

2917 file=$tmpd/`getparfilename $v`

2918 err type=file=$file

2919 [ -z "$file" ] && continue

2920 bn=${file##*/}

2921 bin="X'"$(hexize $file)"'"

2922 ct=`file --mime-type $file|cut -d' ' -f2`

2923 type=\"file:$ct\"

2924 newsql="update $tb2 set val='$bn', type=$type, bin=$bin"

2925 cachedir=`getcachedir "$tbl/$rowid"`

2926 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

2927 rm -r $cachedir

2928 ;;

2929 *)

2930 newsql="update $tb2 set val=(select val from par where var \

2931 like '$col.%.$st_rowid')"

2932 ;;

2933 esac

2934 ;;

2935 *) # maybe "keep", do not modify value

2936 continue

2937 ;;

2938 esac

2939 # err newsql=$newsql

2940 sql=$sql$nl"$newsql where rowid=$st_rowid;"

2941 done

2942

2943 if [ x"$bin" = x"NULL" ]; then

2944 err repl:normal sql=`echo $sql`

2945 query "$sql

2946 delete from $tb2 where type='string' and val='';"

2947 err repl:normal done

2948 else

2949 sqlfile="$tmpd/sqlf.$$"

2950 echo "$sql" > $sqlfile

2951 err repl:sqlfile=`ls -lF $sqlfile`

2952 query ".read $sqlfile"

2953 err repl:done

2954 fi

2955 # Rest of kt==m: set multiple mode

2956 nr=`getparcount $col`

2957 else

2958 nr=1 # for kt==s, number of records is 1

2959 fi

2960

2961 i=0

2962 while [ $i -lt $nr ]; do

2963 limit="limit 1 offset $i"

2964 i=$((i+1)) # increase beforehand against continue

2965 val=`getvalquote $tbl $col "$limit"`

2966 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

2967 err $col=$val

2968 bin=NULL

2969 err partype$col=`getpartype $col "$limit"`

2970 case $ptype in

2971 file) file=$tmpd/`getparfilename $col "$limit"`

2972 err parfile-$col=$file

2973 [ -z "$file" ] && continue

2974 bin="X'"$(hexize $file)"'"

2975 ct=`file --mime-type $file|cut -d' ' -f2`

2976 type=\"file:$ct\" ;;

2977 "*"*) continue ;; # foreign table

2978 *) type=\"string\" ;;

2979 esac

2980 case `gettbl_coltype $tbl/$col` in

2981 password) # special care for password

2982 # name={password,pswd1,pswd2}

2983 p1=`getpar pswd1 "$limit"`

2984 if [ -z "$p1" ]; then

2985 continue # SKIP password setting, if p1 is empty

2986 else

2987 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

2988 ## err pswd=$pswd

2989 if pwcheck "$pswd"; then

2990 if [ x"$p1" = x"$p2" ]; then

2991 case "$p1" in

2992 ??????????*) ;;

2993 *) echo "パスワードは10字以上にしてください。" | html p

2994 return 6;;

2995 esac

2996 val="\"`echo $p1|mypwhash`\""

2997 else

2998 echo "2つの新パスワード不一致" | html p

2999 return 7

3000 fi

3001 else

3002 echo "旧パスワード違います" | html p

3003 return 8

3004 fi

3005 fi

3006 ;;

3007 esac

3008 err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3009 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3010 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3011 if [ x"$bin" = x"NULL" ]; then

3012 err Normal-query: `echo $sql`

3013 query "$sql"

3014 else

3015 sqlfile="$tmpd/query.$$"

3016 echo "$sql" > $sqlfile

3017 err sqlfile=`ls -lF $sqlfile`

3018 query ".read $sqlfile"

3019 fi

3020 err p2t done

3021 done

3022 done

3023 done

3024 return 0

3025 ##err donee

3026 )

3027 genform() {

3028 # $1 = form definition file

3029 # $2, $3 (optional)= table name and ROWID

3030 # If $GF_VIEWONLY set and nonNull, output values without form

3031 # If $GF_ARGS set, use it as content-strings in the form

3032 # If $GF_OWNER set, use it as value of name="owner"

3033 # If $GF_STAGE set, use it as value of name="stage"

3034 forms="" hiddens="" rowid=$3

3035 if [ ! -e "$1" ]; then

3036 echo "そのようなデータベースはないようです($2)。" | html p

3037 return

3038 elif [ -n "$2" ]; then

3039 err genform1: "select * from $2 where rowid='$rowid'"

3040 rec=`query "select * from $2 where rowid='$rowid';"`

3041 if [ -z "$rec" ]; then

3042 pk=`gettblpkey $2`

3043 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3044 err "select rowid from $2 where $pk='$rowid';"

3045 rec=`query "select rowid from $2 where $pk='$rowid';"`

3046 err rec-rowid=$rec

3047 rowid=$rec

3048 rec=$3

3049 fi

3050 if [ -z "$rec" ]; then

3051 echo "そんなレコードはないみたいね..." | html p

3052 return

3053 fi

3054 fi

3055 if [ -z "$GF_VIEWONLY" ]; then

3056 rm='<input id="rm" name="rm" type="checkbox"

3057 value="yes"><label for="rm">このエントリの削除</label>

3058 <span>ほんとうに消しますよ(確認)!

3059 <input name="confirm" type=checkbox value="yes">はい</span>'

3060 fi

3061 # Image Cache dir

3062 err genform: getcache=$2/$rowid

3063 td=`getcachedir "$2/$rowid"`

3064 while IFS=: read prompt name keytype type args; do

3065 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3066 sp="${args:+ }"

3067 form="" val=""

3068 if [ -n "$rowid" ]; then

3069 # err genform2a: Seeking for "$2.$name, type=$type"

3070 val=`getvalbyid $2 $name $rowid $td|htmlescape`

3071 err genform3a: getvalbyid $2 $name $rowid $td

3072 err genform3b: val="[$val]"

3073 fi

3074 if [ -n "$GF_VIEWONLY" ]; then

3075 is_hidden "$2" "$name" && continue

3076 fi

3077 case "$type" in

3078 text*)

3079 cgiform=cgi_multi_$type

3080 if [ -s $td/$name.count -a -n "$val" ]; then

3081 form=`$cgiform $name $td`

3082 val=$(echo "$val"|

3083 while read fn; do

3084 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3085 </td></tr>$nl"

3086 done)

3087 val="<table>$nl$val$nl</table>"

3088 else

3089 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3090 err genform: cgi_$type $name $val "$args"

3091 form=`cgi_$type $name "$val" "$args"`

3092 fi

3093 ;;

3094 [Rr][Aa][Dd][Ii][Oo])

3095 fh="<label><input type=\"radio\" name=\"$name\""

3096 form="`echo $args|sed -e \

3097 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3098 ;;

3099 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3100 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3101 ;;

3102 [Ss][Ee][Ll][Ee][Cc][Tt])

3103 fh="<select name=\"$name\">$nl"

3104 form=$(for l in $args; do

3105 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3106 done)

3107 if [ -n "$val" ]; then

3108 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3109 fi

3110 form="$fh$form</select>"

3111 ;;

3112 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3113 if [ -s $td/$name.count ]; then

3114 form=`cgi_multi_file $name $td "$args"`

3115 if [ -n "$val" ]; then

3116 hrfb="$myname?showattc+$2_m"

3117 val=$(echo "$val" \

3118 | while read fn; do

3119 data=`percenthex $td/$fn`

3120 #ct=`cat $td/$fn.content-type`

3121 ct=`file --mime-type $td/$fn|cut -d' ' -f2`

3122 ri=`cat $td/$fn.rowid`

3123 ## err fn=$fn, name=$name, ri=$ri; ls -lF $td 1>&3

3124 #imgsrc="<img src=\"data:$ct,$data\">"

3125 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3126 iconhref $td/$fn "$hrfb+$ri" ""

3127 done)

3128 fi

3129 else

3130 form="<input type=\"file\" name=\"$name\" $args>"

3131 if [ -n "$val" ]; then

3132 imgs=$(echo "$val"\

3133 |while read fn;do

3134 data=`percenthex $td/$fn`

3135 echo "<img src=\"data:image/png,$data\">$fn<br>"

3136 done)

3137 form=$form"<br>$imgs"

3138 val=$imgs # 2015-06-15

3139 else

3140 form="<input type=\"file\" name=\"$name\" $args>"

3141 fi

3142 fi

3143 ;;

3144 [Hh][Ii][Dd][Dd][Ee][Nn])

3145 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3146 args="value=\"$GF_STAGE\""

3147 fi

3148 form="<input type=\"hidden\" name=\"$name\" $args>"

3149 prompt='' # Remove prompt

3150 ;;

3151 [Aa][Uu][Tt][Hh][Oo][Rr])

3152 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3153 prompt="" ;;

3154 [Oo][Ww][Nn][Ee][Rr])

3155 val=${GF_OWNER:-$val}

3156 val=${val:-$user}

3157 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3158 prompt="" ;;

3159 [Uu][Ss][Ee][Rr])

3160 # XXX: is null $user ok?

3161 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3162 [ -n "$GF_VIEWONLY" ] && continue

3163 form="$user"

3164 ;;

3165 [Pp]assword)

3166 [ -n "$GF_VIEWONLY" ] && continue

3167 form="`cgi_passwd`"

3168 val=""

3169 ;;

3170 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3171 if [ -z "$rowid" ]; then

3172 val=`genserial`

3173 fi

3174 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3175 prompt="" ;;

3176 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3177 prompt=""

3178 ;;

3179 parent|path|blog*)

3180 prompt=""

3181 ;;

3182 "*"*)

3183 tail=$tail"``"

3184 continue ;;

3185 esac

3186 if [ -n "$prompt" ]; then

3187 if [ -n "${GF_VIEWONLY}" ]; then

3188 form=$val

3189 else

3190 :

3191 fi

3192 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3193 else

3194 hiddens=$hiddens$nl"$form"

3195 fi

3196 done < $1

3197 # enctype="multipart/form-data"

3198 cat<<EOF

3199 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3200 ${rowid:+$rm}

3201 <table class="b $2">

3202 $forms

3203 </table>$hiddens

3204 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3205 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3206 EOF

3207 if [ -z $GF_VIEWONLY ]; then

3208 cat<<EOF

3209 <input type="submit" name="sub" value="OK">

3210 <input type="reset" name="res" value="Reset">

3211 EOF

3212 fi

3213 cat<<EOF

3214 $GF_ARGS</form>

3215 $tail

3216 EOF

3217 }

3218 edittable() {

3219 # $1=form-def $2=table $3 rowid

3220 genform "$@"

3221 }

3222 viewtable() {

3223 GF_VIEWONLY=1 genform "$@"

3224 }

3225 showattc() {

3226 # $1=table_m $2=rowid

3227 err \$1=$1 \$2=$2

3228 if ! isfilereadable $user $1 $2; then

3229 contenttype; echo

3230 echo "このファイルは管理者のみしか見られません" | html p

3231 putfooter; exit

3232 fi

3233 idir=`umask 002; mktempd` || exit 1

3234 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3235 bin=$idir/$myname-$$.bin

3236 sql="select quote(bin) from $1 where rowid='$2';"

3237 err showattc: sql: $sql

3238 sq $db "$sql" | unhexize > $bin

3239 tv=`query "select type,val from $1 where rowid='$2';"`

3240 type=${tv%\|*} fn=${tv#*\|}

3241 err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3242 ct=${type#file:}

3243 case $ct in # all text/* changed to text/plain

3244 text/*)

3245 charset=`nkf -g $bin|cut -d' ' -f1`

3246 case $charset in

3247 ASCII*) charset="" ;;

3248 esac

3249 ct="text/plain${charset:+; charset=$charset}"

3250 ;;

3251 esac

3252 contenttype "$ct"

3253 echo "Content-Disposition: filename=\"$fn\""

3254 echo "Content-Length: " `cat $bin | wc -c`; echo

3255 #echo "Content-Type: " ${type#file:}; echo

3256 cat $bin

3257 }

3258 #

3259 # Some default stupid handler on CGI values

3260 #

3261 default_storedb() {

3262 # ARG: $1=table-def-file

3263 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3264 tbl=`basename $1`

3265 tbl=${tbl%.def}

3266 cols="`grep :text $1|cut -d: -f2`"

3267 col=`echo "$cols"|head -1`

3268 vcol=`getpar $col`

3269 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3270 if [ -n "$vcol" ]; then

3271 par2table $1

3272 else

3273 return 2 # No insertion occurred

3274 fi

3275 }

3276

3277 default_view() { # $1=def-file

3278 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3279 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3280 default_storedb "$@"

3281 query "select rowid from $tbl order by rowid desc;" \

3282 | while read rowid; do

3283 viewtable $1 $tbl $rowid

3284 done | _m4 -D_TITLE_="$tbl" \

3285 -D_FORM_="`genform $1`" \

3286 -D_DUMPTABLE_="syscmd(cat)" \

3287 $layout/html.m4.html $layout/form+dump.m4.html

3288 }

3289 default_viewtext() { # $1=def-file

3290 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3291 default_storedb "$@"

3292 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3293 | _m4 -D_TITLE_="$tbl" \

3294 -D_FORM_="`genform $1`" \

3295 -D_DUMPTABLE_="syscmd(cat)" \

3296 $layout/html.m4.html $layout/form+dump.m4.html

3297 }

3298 default_smail() {

3299 default_storedb "$@"

3300 if [ $? -eq 2 ]; then

3301 _m4 -D_TITLE_="入力" \

3302 -D_FORM_="`genform $1`" \

3303 -D_DUMPTABLE_="" \

3304 $layout/html.m4.html $layout/form+dump.m4.html

3305 return

3306 fi

3307 cond=""

3308 for pk in `gettblpkey $tbl`; do

3309 pv=$(sqlquote $(getpar $pk))

3310 cond="$cond${cond:+ and }$pk=$pv"

3311 done

3312 sql="select rowid from $tbl where $cond;"

3313 rowid=`query "$sql"`

3314 err smail1 - "$sql" "-> rowid=$rowid"

3315

3316 while IFS=: read prompt name keytype type args; do # Read from $1

3317 val=`getpar $name`

3318 if [ -n "$val" ]; then

3319 text="$text

3320 $prompt

3321 $name=$val

3322 ---------------------------------------------------------"

3323 fi

3324 case "$type" in

3325 image|document|file)

3326 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3327 fns=$(echo "$fn"|while read fn; do

3328 err mv $tmpd/$fn.orig $tmpd/$fn

3329 mv $tmpd/$fn.orig $tmpd/$fn

3330 rm $tmpd/$fn.rowid # Remove cache flag

3331 err "`ls $tmpd/$fn`"

3332 echo $fn

3333 done)

3334 files="$files $fns"

3335 ;;

3336 esac

3337 done < $1

3338 err FILES=$files "`ls -lF $tmpd`"

3339 subj="from ${REMOTE_ADDR}"

3340 (echo "$url"

3341 echo "への書き込みがありました。"

3342 echo "------"

3343 echo "$text"

3344 ) | (cd $tmpd &&

3345 err LS="`ls -lF`" &&

3346 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3347 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3348 echo "以下の内容で送信しました。" | html p

3349 viewtable $1 $tbl \

3350 `query "select rowid from $tbl order by rowid desc limit 1;"`

3351 echo "戻る" | html a "href=\"?\""

3352 }