s4: 827dd5d8653e s4-funcs.sh

s4

view s4-funcs.sh @ 306:827dd5d8653e

Set admin value to $5 when $4 is empty(in joingrp())

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sat, 15 Oct 2016 10:25:10 +0859
parents	e751156de740
children	e7a7f6cb9627

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 maximagexy=1600x1600

29 ### maximagexy=400x400

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

31 blogreadflagrowid=0

33 tconfs=""

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

35 conftbl=_tblconf

36 nl="

37 "

38 . ./s4-cgi.sh

40 : <<EOF

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

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

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

45 レポート提出管理

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

48 [2016]

49 7/12 根本への反省

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

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

53 [以下2015]

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

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

56 Web

57 締切設定

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

60 ○自分の提出物リスト

62 7/19 ○設置

63 ○一斉送信

64 ○getparfilename の tmpd の扱い

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

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

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

68 ○添付ファイル回収

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

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

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

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

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

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

76 ○レポート提出モード

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

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

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

82 7/9 ○管理者の追加

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

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

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

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

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

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

91 ○グループ検索

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

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

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

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

100 ○6/1 par2tableを triplex 対応に

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

102 →とすると一気に

103

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

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

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

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

108

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

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

111 ## 1. passwd

112 ## 2. grp

113 ## 3. grp_mem

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

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

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

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

118

119 ## ● listの定義:

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

121 ## ● hashの定義:

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

123

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

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

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

127 ## csv2sq3 で .csv.sq3 の Makefile

128

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

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

131 ## type := root | comment

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

133 ## unique(id, filename)

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

135

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

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

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

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

140 ## できたー!

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

142 ↓

143 ↓以下に変更

144 with recursive allmem as

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

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

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

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

149

150

151 with recursive allmem as

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

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

154 where allmem.val=grp_m.gname)

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

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

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

158

159

160 ## triggerもできた。

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

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

163

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

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

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

167 ## グループ管理

168 ## 個人情報管理

169 ##

170

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

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

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

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

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

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

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

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

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

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

181

182 ## ログテーブル

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

184

185

186

187 ■表設計

188 * 3つの表に分散管理

189 id格納表 + hash表 + list表

190 * *_s *_m

191

192

193

194 user, user_map, user_col

195

196 ■抽象エントリタイプ

197 * user

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

199 * group

200 権限判定に利用

201 * serial

202 自動idとして機能

203 * password

204 入力 type=passwordで入力

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

206 * session

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

208 * text

209 入力 type=text

210 * textarea

211 入力 textarea

212 * image|document

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

214 * owner

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

216 * gowner

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

218 * timestamp

219 datetime()

220 * parent

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

222 * path

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

224

225 格納タイプ

226 * list

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

228 * hash

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

230

231 オブジェクトタイプ

232 * entry

233 id, title, owner

234 * textpart

235 id, parentID, text

236 * binarypart

237 id, parentID, contenttype, filename, content

238 * content

239 hash(textpart), list(binarypart)

240 * topic

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

242 * reply

243 id, parentID, content

244 * blog

245 list(entry)

246 blog = [topic, list(reply)]

247

248

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

250 "text" => "hogehoge ..",

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

252 "author" => "taro",

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

254 "parent" => "/",

255 "path" => "/1",

256 "text" => "blah, blah, ....",

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

258 {"serial" => 2,

259 "author" => "hanako",

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

261 "parent" => "/",

262 "path" => "/2",

263 "text" => "blah, blah, ....",

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

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

266

267

268 user:=

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

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

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

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

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

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

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

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

277

278 変換表

279 /user/email=m

280

281 blog:=

282 シリアル:id:p:serial

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

284 所有者:owner:s:owner:

285 時刻:ctime:s:stamp:

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

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

288

289 article:=

290 シリアル:id:p:serial

291 筆者:author:s:owner

292 時刻:ctime:s:stamp:

293 参照元:parent:s:parent:

294 パス:path:s:path:

295 本文:text:s:textarea:

296 画像:image:m:image:

297

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

299

300

301 EOF

302

303 sq() {

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

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

306 }

307 dbsetup() {

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

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

310 sqi=$tmpdir/sqi.$$

311 sqo=$tmpdir/sqo.$$

312 mkfifo $sqi $sqo

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

314 sq $db < $sqi &

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

316 exec 2>> $tmpdir/error.out

317 exec 3>> $tmpdir/debug.out

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

319 rm $sqi

320 }

321 cleanup() {

322 echo .quit >&5

323 kill $sq3pid

324 kill $sq3pid

325 rm -f $sqo $sqi

326 rm -rf $tmpfiles

327 }

328 query() {

329 echo ".once $sqo" >&5

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

331 cat

332 else

333 echo "$@"

334 fi >&5

335 cat $sqo

336 }

337 _m4() {

338 #_S4NAME_=f,f,f

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

340 }

341 ismember() {

342 # $1=user, $2=group

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

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

345 }

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

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

348 }

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

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

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

352 }

353 isgrpowner() (

354 # $1=user, $2=group

355 gn=`sqlquote "$2"`

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

357 err isgrpowner: $sql

358 test -n "`query $sql`"

359 )

360 getgroupadminmails() {

361 # $1=group

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

363 email4group "$1" "$i" ;

364 done

365 }

366 getgroupadmins() { # $1=group

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

368 qgrp=`sqlquote "$1"`

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

370 }

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

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

373 getvalbyid grp $2 \

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

375 }

376 getgroupbyid() {

377 # $1=id|gname

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

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

380 # err ggbyid: `echo $sql`

381 query $sql

382 }

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

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

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

386

387 # Return true when anonymous mode

388 [ "$anonymousmode" ] && return 0

389 # case `getvalbyid blog mode $2` in

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

391 # *closed*)

392 # owner=`getvalbyid blog owner $2`

393 # if isgrp $owner; then

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

395 # elif isuser $owner; then

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

397 # fi

398 # esac

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

400 sql="with getblog as (\

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

402 select blogid from article where id in\

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

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

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

406 select case\

407 when (select author from article where\

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

409 then 'author'\

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

411 then 'open'\

412 when (select val from getmode) is null \

413 then 'open'

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

415 then (select user from grp_adm where \

416 gname=(select val from getowner) and \

417 user='$1')\

418 when (select author from article where\

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

420 then 'user+author'

421 else '' end;"

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

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

424 result=`query "$sql"`

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

426 return 2

427 }

428 linkhome() {

429 # $1=UserOrGroup

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

431 if isuser $1; then

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

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

434 else

435 echo -n "grp+$1"

436 fi

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

438 }

439 hreflink() {

440 # s4 specific notation:

441 # ^href=URL

442 # ^iframe=URL

443 # OSM umap Wikistyle Notation:

444 # [[URL]] - Simle Link

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

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

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

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

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

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

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

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

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

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

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

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

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

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

459 }

460 minitbl() {

461 sed -n '

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

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

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

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

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

467 H; # Concat this line to HoldSpace

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

469 $ b cont

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

471 }

472 :cont

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

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

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

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

477 p; # Print whole "table" element

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

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

480 }

481 x; # Back to the newest line

482 p; # Print rest'

483 }

484 acclog() (

485 # $1=table, $2=rowid

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

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

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

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

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

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

492 fi

493 )

494 gecos() (

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

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

497 )

498 setpar() {

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

500 }

501 replpar() {

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

503 }

504 getpar() {

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

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

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

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

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

510 fi

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

512 case "$var" in

513 owner)

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

515 echo $user; return

516 elif ismember $user $val; then

517 echo $val; return

518 fi ;;

519 esac

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

521 echo "$val"

522 }

523

524 getpartype() {

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

526 }

527 getparcount() {

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

529 }

530 getparfilename() {

531 # null if type of $1 is not file

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

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

534 }

535 sqlquote() {

536 (v="$1"

537 case "$v" in

538 "") return ;; # null

539 "X'"*) # quoted hex string

540 echo $1 ;;

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

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

543 echo "\"$v\""

544 return ;;

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

546 echo "\"$v\""

547 return ;;

548 *)

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

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

551 else

552 echo "\"$v\""

553 fi ;;

554 esac)

555 }

556 sqlquotestr() (

557 case "$1" in

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

559 echo "'$v'" ;;

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

561 esac

562 )

563 mktempd() {

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

565 }

566 getcachedir() { # $1=maintable

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

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

569 else

570 echo $tmpd/$thumbxy

571 fi

572 }

573 getval() {

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

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

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

577 echo "$user" ;;

578 stamp|datetime)

579 date "+%F %T" ;;

580 serial)

581 (s=`getpar $2`

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

583 *)

584 getpar "$2" "$3";;

585 esac

586 }

587

588 getvalquote() {

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

590 (v=`getval "$@"`

591 case "$v" in

592 "") echo NULL ;;

593 *) sqlquote "$v" ;;

594 esac)

595 }

596 getparquote() {

597 sqlquote `getpar $1`

598 }

599 getbinbyid() {

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

601

602 }

603 getvalbyid() {

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

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

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

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

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

609

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

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

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

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

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

615 return

616 fi

617 done

618 rowid=$3

619 pk=`gettblpkey $1`

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

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

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

623 for kt in s m; do

624 td=${4:-$tmpd}

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

626 t=${1}_$kt

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

628 vcount=1 # count(val)

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

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

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

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

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

634 if [ $kt = m ]; then

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

636 # Reset val to store filenames if type is string

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

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

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

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

641 i=0

642 err gvbid: i=$i vcount=$vcount

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

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

645 i=$((i+1))

646 fn=$c.$i

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

648 case $type in

649 file:*)

650 #file=$td/$val

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

652 # FOR SPEED: Skip file generation if imgcache exists

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

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

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

656 .output $td/$fn.rowid

657 select rowid from $cond $slice;

658 .output $td/$fn

659 select val from $cond $slice;

660 .output $file.content-type

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

662 .output stdout

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

664 EOF

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

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

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

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

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

670 case $type in

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

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

673 *|*:[Aa]pplication*)

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

675 png:- > $file

676 ;;

677 esac

678 ;;

679 *)

680 sq $db<<EOF

681 .output $td/$fn.rowid

682 select rowid from $cond $slice;

683 .output $td/$fn

684 select val from $cond $slice;

685 EOF

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

687 ;;

688 esac

689 done

690 else

691 rm -f $td/$c.count

692 case $type in

693 file:*)

694 echo "$val" \

695 | while read fn; do

696 file=$td/$fn

697 if [ ! -s $file ]; then

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

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

700 | unhexize > $file

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

702 # err TTTTTTTTTTTTTTTT: $type

703 case $type in

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

705 *:[Aa]pplication*)

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

707 png:- > $file ;;

708 esac

709 fi

710 done

711 ;;

712 esac

713 fi

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

715 return

716 fi

717 done

718 done)

719 }

720 getvalbypkey() (

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

722 pk=`gettblpkey $1`

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

724 getvalbyid "$1" "$2" $rowid $4

725 )

726 getvalbycond() {

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

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

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

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

731 getvalbyid "$1" "$2" $rowid "$4"

732 fi

733 }

734 getpwfield() {

735 # getpwfield user column

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

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

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

739 echo "$val"

740 return 0

741 else

742 return 1

743 fi

744 }

745 encode() {

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

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

748 sha1=sha1

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

750 sha1=sha1sum

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

752 sha1=gsha1sum

753 fi

754 fi

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

756 }

757 mycrypt() (

758 key=$1 salt=$2

759 err \$2=$2

760 case $2 in

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

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

763 esac

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

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

766 )

767 hexize() {

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

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

770 hexize="xxd -p"

771 else

772 hexize_hd() {

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

774 }

775 hexize="hexize_hd"

776 fi

777 fi

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

779 }

780 unhexize() {

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

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

783 unhex="xxd -p -r"

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

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

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

787 EOF

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

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

790 # world writable...:)

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

792 fi

793 fi

794 cat $1 | $unhex

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

796 }

797 percenthex() {

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

799 }

800 htmlescape() {

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

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

803 }

804 enascii() {

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

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

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

808 else

809 enascii_now=`date +%FT%T`

810 enascii_sed() {

811 nkf -Z0Z1Z2 \

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

813 }

814 enascii="enascii_sed"

815 fi

816 fi

817 cat "$@" | $enascii

818 }

819 size_h() {

820 i="$1" oi=$1

821 set -- B B KB MB GB TB

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

823 oi=$i

824 i=$((i/1024))

825 shift

826 done

827 echo ${oi}$1

828 }

829 gettblconf() {

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

831 ## tconfs=`sq $db \

832 tconfs=`query \

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

834 fi

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

836 }

837 gettblkeys() {

838 # $1=tbl

839 gettblconf

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

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

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

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

844 case $type in

845 *p*)

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

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

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

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

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

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

852 esac

853 case $type in

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

855 esac

856 done

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

858 }

859 gettblpkey() {

860 # $1=tbl

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

862 }

863 gettblfkey() {

864 (x=`gettblkeys $1`

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

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

867 }

868 gettblcols() {

869 (x=`gettblkeys $1`

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

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

872 }

873 gettbl_s_cols() {

874 (x=`gettblkeys $1`

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

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

877 }

878 gettbl_m_cols() {

879 (x=`gettblkeys $1`

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

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

882 }

883 gettbl_h_cols() {

884 (x=`gettblkeys $1`

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

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

887 }

888 gettbl_coltype() (

889 gettblconf

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

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

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

893 )

894 is_hidden() {

895 # $1=Tbl $2=col

896 gettblconf

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

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

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

900 case $x in

901 *h*) return 0 ;;

902 *) return 1 ;;

903 esac

904 }

905

906 dbsetbyid() {

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

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

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

910 unset primary update

911 gettblconf

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

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

914 #err conf=$conf

915 case ${conf#*=} in

916 p*) primary=1 ;;

917 f*) update=1 ;;

918 u*) ;;

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

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

921 esac

922 #err t=$t

923 type=text fn=""

924 case $conf in

925 */password)

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

927 ;;

928 */image*|*/document*)

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

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

931 ;;

932 esac

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

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

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

936 if [ "$primary" ]; then

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

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

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

940 elif [ "$update" ]; then

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

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

943 else

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

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

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

947 fi

948 )

949 }

950 expire() (

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

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

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

954 )

955 addsession() {

956 # expireをセット

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

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

959 err addsession "$1" exp=$expire

960 sq $db "replace into session values('$1', '$expire')"

961 # Remove old session parameters

962 now=`expire now`

963 sq $db "delete from session where expire < '$now'"

964 }

965 gencookie() (

966 for kv; do

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

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

969 done

970 )

971 contenttype() {

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

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

974 }

975 putheader() {

976

977 }

978 putfooter() {

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

980 }

981 getcookie() (

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

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

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

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

986 done

987 )

988 genrandom() {

989 # $1=columns (default: 10)

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

991 }

992 genserial() {

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

994 }

995 smail() {

996 # smail rcpts subj (file)

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

998 # $MAIL_FROM <- From: header value

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

1000 rcpt=`echo $1` # strip newlines

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

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

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

1004 }

1005 setviastring() {

1006 table=$1

1007 oifs="$IFS"

1008 IFS="&"

1009 for us in $2; do

1010 k=${us%%=*}

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

1012 sq $db "replace into $table values('$session', '$k', 'string', \"$v\")";

1013 #echo $k=$v

1014 done

1015 IFS="$oifs"

1016 }

1017 checkdomain() (

1018 # Check the validity of domain by referring DNS

1019 item=$1

1020 err checkdomain $1

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

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

1023 )

1024 pwcheck() {

1025 # $1=passwd

1026 dbpswd=`getpwfield $user pswd`

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

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

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

1030 }

1031 mypwhash() {

1032 mycrypt `cat` `genrandom 5`

1033 }

1034 wasureta() {

1035 user=$1

1036 if ! checkdomain $user; then

1037 contenttype; echo

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

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

1040 putfooter

1041 exit 0

1042 fi

1043 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1045 encpswd=`echo $newpswd|mypwhash`

1046 dbsetbyid user $user pswd "$encpswd"

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

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

1049 $msgdir/mail-newaccount.m4 \

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

1051 | smail $user "New Account"

1052 }

1053 checkauth() {

1054 user=`getpar user`

1055 skc=`getpar skey` # from cookie

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

1057 skey="`getpwfield $user skey`"

1058 err user=$user skey=$skey

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

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

1061 return 0

1062 fi

1063 fi

1064 pswd=`getpar pswd`

1065 quser=`sqlquotestr "$user"`

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

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

1068 return 1

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

1070 wasureta $user

1071 return 1 # wasureta error

1072 fi

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

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

1075 if pwcheck "$pswd"; then

1076 newsession=`genrandom 50`

1077 dbsetbyid user $user skey "$newsession"

1078 gencookie "user=$user" "skey=$newsession"

1079 return 0

1080 fi

1081 return 2 # Password mismatch

1082 }

1083 showlogin() {

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

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

1086 $layout/login.m4.html

1087 exit 0

1088 }

1089 dologin() {

1090 checkauth

1091 st=$?

1092 if [ $st != 0 ]; then

1093 contenttype; echo

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

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

1096 showlogin # and EXIT

1097 fi

1098 }

1099

1100 # Do instant jobs here

1101 dbsetup

1102 trap cleanup INT HUP EXIT TERM PIPE

1103 # trap cleanup INT HUP

1104

1105 err() {

1106 echo "$@" 1>&3

1107 }

1108

1109 cgiinit() {

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

1111 tmpf=tmp/stream

1112 tmpd=`tmpd=$tmpdir mktempd`

1113 tmpfiles=$tmpfiles" $tmpd"

1114 addsession $session

1115 getcookie

1116 case "$REQUEST_METHOD" in

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

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

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

1120 # cat > $tmpf # too much?

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

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

1123 s="`cat tmp/stream`"

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

1125 ;;

1126 esac

1127 case "$CONTENT_TYPE" in

1128 *boundary*)

1129 bndry=${CONTENT_TYPE#*boundary=}

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

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

1132 do

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

1134 #echo u=$us

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

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

1137 # err k=$k v=$v

1138 case "$k" in

1139 *:filename)

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

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

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

1143 [Ii]mage/x-xcf)

1144 bzip2 $tmpd/$v

1145 v=${v}.bz2

1146 ;;

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

1148 [Ii]mage/*)

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

1150 ;;

1151 esac

1152 ;;

1153 *)

1154 type='string'

1155 ;;

1156 esac

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

1158 setpar "$k" "$type" "$v"

1159 done

1160 ;;

1161 *)

1162 setviastring par "$s"

1163 ;;

1164 esac

1165 }

1166 email4group() {

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

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

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

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

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

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

1173 and s.key='email'

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

1175 err `echo $sql`

1176 query "$sql"

1177 }

1178 email4groupbyuid() {

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

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

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

1182 uids=`echo "$@"`

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

1184 sql="WITH

1185 grpemails AS (

1186 SELECT gname, user, val email

1187 FROM grp_mem NATURAL JOIN grp_mem_s

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

1189 useremails AS (

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

1191 FROM user

1192 LEFT JOIN user_s

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

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

1195 FROM useremails u LEFT JOIN grpemails g

1196 ON u.name=g.user

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

1198 err email4gByid `echo $sql`

1199 query "$sql"

1200 }

1201 collectemail() (

1202 # Collect email addresses for group $1

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

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

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

1206 for e; do

1207 if isuser "$e"; then

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

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

1210 else

1211 qgrp=`sqlquote "$e"`

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

1213 gmem="grp_mem"

1214 else

1215 tm=`sqlquote "$TEAM"`

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

1217 fi

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

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

1220 $gmem g left join grp_mem_s s

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

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

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

1224 err CollectEmail: `echo "$sql"`

1225 query "$sql"

1226 fi

1227 done

1228 )

1229 sendinvitation() (

1230 # $1=email

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

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

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

1234 gecos=`gecos`

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

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

1237 _m4 -D_URL_="$urlbase" \

1238 -D_USER_="$name" \

1239 -D_EMAIL_="$1" \

1240 -D_REGIST_="$regist" \

1241 -D_ADMIN_="$admin" \

1242 $msgdir/mail-invite.m4 \

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

1244 return 0

1245 )

1246 emaildomaincheck() {

1247 case "$1" in

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

1249 *@*)

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

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

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

1253 return 2

1254 fi

1255 return 0

1256 ;;

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

1258 esac

1259 }

1260 invite() {

1261 email=`getpar email`

1262 case $email in

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

1264 *@*)

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

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

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

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

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

1270 elif sendinvitation $email; then

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

1272 fi ;;

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

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

1275 esac

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

1277 err addr=$addr

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

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

1280 fi

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

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

1283 $layout/html.m4.html $layout/invite.m4.html

1284 }

1285 regist() {

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

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

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

1289 echo "bye bye" | html p

1290 reutrn

1291 fi

1292 email=`session=$1 getpar invite`

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

1294 cat<<EOF

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

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

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

1298 EOF

1299 return

1300 fi

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

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

1303 # Fake login password to wasureta

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

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

1306 wasureta $email

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

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

1309 addsession $1 # for removal after 1 minute

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

1311 $layout/login.m4.html

1312 return

1313 }

1314 group_safename() {

1315 # Convert $1 to safe group name

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

1317 }

1318 groupupdate() {

1319 gname=`getpar gname`

1320 qgname=`sqlquote $gname`

1321 err Enter:groupupdate

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

1323 # See ALSO same job in showgroup()

1324 newgname=`group_safename "$gname"`

1325 err newgname=$newgname

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

1327 err NewGNAME: gname=$newgname

1328 gname=$newgname

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

1330 replpar gname string "$gname"

1331 fi

1332 # Name confliction check

1333 parow=`getpar rowid`

1334 err parow=$parow

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

1336 query "BEGIN EXCLUSIVE;"

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

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

1339 if [ $count -gt 0 ]; then

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

1341 query "END;"

1342 return

1343 fi

1344 par2table $formdir/grp.def

1345 query "END TRANSACTION;"

1346 # Remove orphan

1347 : <<EOF

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

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

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

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

1352 EOF

1353 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1357 sql="delete from blog where id in

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

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

1360 query "$sql";

1361 fi

1362 fi

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

1364 fi

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

1366 grid=$(query $sql)

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

1368 grp $grid

1369 }

1370 groupman() {

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

1372

1373 GF_STAGE="grpconf"

1374 GF_STAGE=groupupdate

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

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

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

1378 -D_DUMPTABLE_="syscmd(cat)" \

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

1380 }

1381 userconf() {

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

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

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

1385 }

1386 groupconf() {

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

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

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

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

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

1392 err gcon \$1=$1 rowid=$rowid

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

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

1395 }

1396 mems() {

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

1398 kwd=`getpar kwd`

1399 listmember $kwd

1400 }

1401 grps() {

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

1403 kwd=`getpar kwd`

1404 listgroup $kwd \

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

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

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

1408 $layout/form+dump.m4.html

1409 }

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

1411 gpg=`getpar grp`

1412 grid=${1:-$gpg}

1413 grp=`getgroupbyid "$grid"`

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

1415 ## . ./s4-blog.sh

1416 jg=`getpar joingrp`

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

1418 err jg=$jg, grp=$grp

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

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

1421 fi

1422 htmlheader=$layout/html.m4.html

1423 showgroup "$grid"

1424 }

1425 listnewblogsql() { # $1=user

1426 cat<<EOF

1427 `sql4readableblogs`

1428 with article_ctime as (

1429 select id,blogid,author,val ctime

1430 from article join article_s s using(id)

1431 where s.key='ctime'

1432 ), blog_title_owner as (

1433 select blg.rid brid, id,

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

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

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

1437 ), acclog_user as (

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

1439 ), blogall as (

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

1441 from blog_title_owner b, article_ctime ac

1442 on b.id=ac.blogid

1443 )

1444 select brid LINK,

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

1446 coalesce((SELECT time FROM acclog_user

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

1448 < ctime

1449 ) "新着",

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

1451 /* owner gecos */

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

1453 from blogall bl left join acclog_user l

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

1455 group by bl.id

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

1457 limit 10;

1458 EOF

1459 }

1460

1461 search_form() {

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

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

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

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

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

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

1468 cat<<-EOF

1469 <div class="fr">

1470 <form action="$myname">

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

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

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

1474 </form>

1475 </div>

1476 EOF

1477 }

1478

1479 showhome() {

1480 # $1=userRowIdToShow

1481 err showhome \$1=$1

1482 case "$1" in

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

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

1485 esac

1486 err ShowHome: uname=$uname

1487 gecos=`gecos "$uname"`

1488 err SH:gecos=$gecos

1489 GF_VIEWONLY=1

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

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

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

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

1494 # Display folders

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

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

1497 and type like 'file:%';"

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

1499 nfile=`query "$sql"`

1500 err nfile=$nfile

1501 if [ $nfile -gt 0 ]; then

1502 conflink="$conflink /

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

1504 fi

1505 fi

1506 . ./s4-blog.sh

1507

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

1509 search_form > $sf

1510 echo "$gecos さん" > $tf

1511 viewtable $formdir/user.def user $1 > $pf

1512

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

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

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

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

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

1518 query "SELECT hex(bin) FROM user_m $sqcond LIMIT 1;" \

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

1520 echo '">'

1521 } > $imf

1522 fi

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

1524 val='$uname';"`

1525 listblog $uname > $bf

1526

1527 hometail=$tmpd/tail.$$

1528 mkfifo $hometail

1529

1530 #Calling listgroupbytable, originally here

1531

1532 (

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

1534 # Display Most Recent Entry

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

1536 shortval=${shortval:-val}

1537

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

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

1540 ctime,

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

1542 (SELECT gecos FROM gecoses

1543 WHERE name=(SELECT val FROM blog_s

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

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

1546 FROM blog b

1547 JOIN

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

1549 FROM article a, article_s s

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

1551 GROUP BY blogid ORDER BY val DESC LIMIT 50

1552 ) m

1553 ON b.id=m.blogid;"

1554 # This should be as follows

1555 : <<EOF

1556 WITH arts AS(

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

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

1559 FROM article a NATURAL JOIN article_s s

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

1561 GROUP by s.id

1562 )

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

1564 FROM arts a0

1565 JOIN

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

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

1568 ORDER BY ctime DESC LIMIT 50;

1569 EOF

1570

1571 cat<<-EOF

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

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

1574 <div class="lcto">

1575 `DT_VIEW=replyblog dumptable html blog`

1576 </div>

1577 EOF

1578 unset DT_SQL

1579 # Display NEWS

1580 if false; then

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

1582 new10=`DT_CHLD=article:blogid \

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

1584 else

1585 # 2016-06-26

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

1587 acclog blog $blogreadflagrowid

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

1589 fi

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

1591 sql=`listnewblogsql "$user"`

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

1593 fi

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

1595 cont=$((cont-1))

1596 err newcount=$cont

1597 if [ $cont -gt 0 ]; then

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

1599 cgi_radio foldtabs yes 'id="new10"'

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

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

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

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

1604 `cgi_submit '確定'`

1605 `cgi_hidden read yes`

1606 EOF

1607 echo "$new10 "

1608 echo "</div>"

1609 fi

1610 fi

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

1612 #

1613 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

1619 -D_NBLOG_="$nblog" \

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

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

1622 $layout/html.m4.html $layout/home.m4.html

1623

1624 # Record access log

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

1626 }

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

1628 contenttype; echo

1629 err commission: "$@"

1630 gname=`getgroupbyid $1`

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

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

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

1634 grp_reg_adm "$@"

1635 else

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

1637 fi

1638 }

1639 listgroupbytable() {

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

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

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

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

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

1645 NGsql="select distinct tag from\

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

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

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

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

1650 err ListGRP: query sql="$sql"

1651 for tag in `query "$sql"`

1652 do

1653 err ListGrp: tag=$tag

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

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

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

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

1658 as rowid,

1659 gname,

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

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

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

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

1664 err PersonalGroupList= `echo $sql`

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

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

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

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

1669 echo '</table>'

1670 done

1671 echo '</div>'

1672 }

1673 iconhref() (

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

1675 data=`percenthex $1`

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

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

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

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

1680 )

1681 iconhref2() (

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

1683 src=$1

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

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

1686 )

1687 listentry() (

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

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

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

1691 cond='' hiddens=''

1692 offset=`getpar offset`

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

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

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

1696 limit=30

1697 dir=`getcachedir "$1"`

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

1699 hrb="$myname?home"

1700 deficon=person-default.png

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

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

1703 gcs=gecos

1704 else # if group

1705 hrb="$myname?grp"

1706 deficon=group-default.png

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

1708 gcs=name

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

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

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

1712 err tagconv=$tagconv

1713 fi

1714 fi

1715 if [ ! -d $dir ]; then

1716 mkdir -p $dir

1717 fi

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

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

1720 fi

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

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

1723 fi

1724

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

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

1727 ##qgrp=`sqlquote $grp`

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

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

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

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

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

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

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

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

1736 end

1737 as name,

1738 b.tag,

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

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

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

1742 then '(ADMIN)'

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

1744 then '(Member)'

1745 when '$iamowner' = '' then ''

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

1747 CASE '$entity'

1748 WHEN 'グループ'

1749 THEN coalesce(

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

1751 'open')

1752 ||

1753 CASE WHEN '$user'

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

1755 THEN ' ismember'

1756 ELSE ''

1757 END

1758 ELSE 'user'

1759 END regmode

1760 from $tbl a left join

1761 (select $nm as name,

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

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

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

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

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

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

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

1769 # Note that mtime is stored only in grp_s.

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

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

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

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

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

1775 cgi_form $stage <<EOF

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

1777 `cgi_text kwd $kwd`</label>

1778 EOF

1779 echo '</div>'

1780 fi

1781 hiddens="$hiddens

1782 `cgi_hidden kwd \"$kwd\"`

1783 `cgi_hidden stage \"$stage\"`"

1784 cat<<EOF

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

1786 EOF

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

1788 nextbtn=$(

1789 cat<<EOF

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

1791 `cgi_submit 次の${limit}件`

1792 $hiddens

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

1794 EOF

1795 )

1796 fi

1797 if [ $offset -gt 0 ]; then

1798 prevbtn=$(

1799 cat<<EOF

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

1801 `cgi_submit 前の${limit}件`

1802 $hiddens

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

1804 EOF

1805 )

1806 fi

1807 pnbtn="$nextbtn$prevbtn"

1808 echo $pnbtn

1809

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

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

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

1813 err name=$name owner=$ownerp lnk=$lnk

1814 err newlnk=$lnk regmode=$regmode

1815 icondir=$dir/$id

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

1817 # Pick up only first icon

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

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

1820 | _m4 $tagconv

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

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

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

1824 else

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

1826 fi

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

1828 echo "</div>"

1829 done

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

1831 )

1832 listmember() {

1833 listentry user "$@"

1834 }

1835 listgroup() {

1836 listentry group "$@"

1837 }

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

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

1840 query "SELECT DISTINCT hex(val) FROM grp_mem_m

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

1842 }

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

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

1845 grid=`getpar grid`

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

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

1848 else

1849 grid=$1

1850 fi

1851 grp=`getgroupbyid $grid`

1852 qgrp=`sqlquote $grp`

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

1854 if isgroup "$grp"; then

1855 tf=$tmpd/title.$$

1856 bodyclass=`query "SELECT val FROM grp_s

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

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

1859 ismember="ismember"

1860 qgrp=`sqlquote $grp`

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

1862 else

1863 ismember="" # bodyclass="group"

1864 fi

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

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

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

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

1869 -D_BODYCLASS_="$bodyclass" \

1870 -D_DUMPTABLE_="" \

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

1872 # $htmlheader is defined in grp()

1873 else # if $grp is removed at par2table

1874 listgroup

1875 fi

1876 }

1877 showgroupsub() {

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

1879 # Using $ismember

1880 rowid=$2

1881 grp=`getgroupbyid $2`

1882 qgrp=`sqlquote $grp`

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

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

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

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

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

1888 return

1889 fi

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

1891 # 6/14の次グループのHOMEで出す情報を作る Done

1892 viewtable $1 grp $rowid

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

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

1895 iamowner=$rowid

1896 fi

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

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

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

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

1901 cat<<EOF

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

1903 <div class="fold">

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

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

1906 <div>

1907 `cgi_textarea message "" "cols=60"`

1908 `cgi_submit 送信`

1909 `cgi_reset リセット`

1910 </div>

1911 `cgi_hidden grp $rowid`

1912 </div></form>

1913 EOF

1914 fi

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

1916 err ismember $user $grp

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

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

1919 else

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

1921 fi

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

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

1924 and key='email';"`

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

1926 and key='email';"

1927 err email=$eml

1928 cat <<EOF

1929 <div class="fold">

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

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

1932 <div>

1933 EOF

1934 cgi_form grp <<EOF

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

1936 <table class="b">

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

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

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

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

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

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

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

1944 </table>

1945 `cgi_hidden grp $rowid`

1946 EOF

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

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

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

1950 fi

1951 echo '</div></div>'

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

1953 cgi_form searchart<<EOF

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

1955 `cgi_hidden owner $grp`

1956 EOF

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

1958 DT_CHLD=article:blogid \

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

1960

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

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

1963 cm="?commission+$rowid"

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

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

1966 # team list

1967 hexteams=`hexteams "$grp"`

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

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

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

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

1972 SELECT val TEAM,

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

1974 MEMBERS

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

1976 EOF

1977 echo '</table></div>'

1978 fi

1979 }

1980 grp_getbodyclass() {

1981 # Get css class name for document.

1982 # `moderated' for moderated groups

1983 # `ismember' for groups where user belongs

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

1985 # $user=userNameCurrentlyLogin

1986 err grp_getbodyclass: 1="$1"

1987 qgrp=`sqlquote "$1"`

1988 query<<-EOF

1989 SELECT coalesce(

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

1991 'open')

1992 ||

1993 CASE WHEN '$user'

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

1995 THEN ' ismember'

1996 ELSE ''

1997 END;

1998 EOF

1999 }

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

2001 err GRP_ACTION:IN

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

2003 grp=`getgroupbyid "$grid"`

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

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

2006 fi

2007 if ! ismember $user $grp; then

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

2009 fi

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

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

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

2013 $layout/html.m4.html

2014

2015 isowner=""

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

2017 usel=`getpar usel`

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

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

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

2021 text=`getpar text`

2022

2023 rm=`getpar rm` cfm=`getpar confirm`

2024 err rm=$rm cfm=$cfm

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

2026 if [ "$isowner" ]; then

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

2028 # Eliminate

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

2030 for tbl in grp_mem grp_mem_s grp_mem_m; do

2031 sql="delete from $tbl $cond;"

2032 # echo "sql=$sql"

2033 query "$sql"

2034 err rmGRPuser "$sql"

2035 done

2036 num=`query "select count(*) from user where rowid in ($uids);"`

2037 #err num=$num

2038 if [ 0$num -gt 0 ]; then

2039 sql="select coalesce(b.val,a.name) from user a left join \

2040 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2041 # err `echo "$sql"`

2042 html pre<<EOF

2043 以下の${num}名のグループ $grp 登録を解除しました。

2044 `query "$sql"`

2045 EOF

2046 fi

2047 else

2048 echo "確認のチェックがないのでやめておきます。" | html p

2049 return

2050 fi

2051 else # not Group Owner

2052 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2053 return

2054 fi

2055 cat<<EOF

2056

2057 EOF

2058 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2059 if [ -z "$text" ]; then # if msg is empty

2060 echo "なにかメッセージを..." | html p

2061 return 0

2062 fi

2063 gecos=`gecos $user`

2064 mkfrom=`getpar mkfrom`

2065 if [ x"$mkfrom" = x"yes" ]; then

2066 safegc=`echo "$gecos" | tr -d '<>@'`

2067 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2068 fromad=`email4groupbyuid "$grp" "$myuid"`

2069 mail_from="$safegc <$fromad>"

2070 else

2071 mail_from="$admin"

2072 fi

2073 MAIL_FROM=$mail_from \

2074 smail "`email4groupbyuid "$grp" $usel` $user" \

2075 "$gecos さんからのメッセージ" <<EOF

2076 $url

2077 のグループ「$grp」のメンバーである $gecos さんから、

2078 あなた宛へのメッセージです。

2079 ----------------------------------------------------------

2080 $text

2081 EOF

2082 if [ $? = 0 ]; then

2083 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2084 sql="select coalesce(b.val, a.name) from

2085 (select name from user where rowid in ($uids)) a

2086 left join user_s b on a.name=b.name and b.key='gecos';"

2087 html pre<<EOF

2088 `query "$sql"`

2089 (送信者である $gecos さんも含まれます)

2090 EOF

2091 err SendDone: `echo $sql`

2092 fi

2093 elif [ x"$rm" = x"commission" ]; then

2094 grp_reg_adm $grid $usel

2095 elif [ x"$rm" = x"addteam" ]; then

2096 team=`getpar team|sed "s/'/''/g"` # for single quotation

2097 newteam=`echo "$team"|tr -d ,`

2098 if [ x"$team" != x"$newteam" ]; then

2099 echo "チーム名に使えない文字を除去しました" | html p

2100 team=newteam

2101 fi

2102 if [ -z "$team" -o x"$team" = x"なし" ]; then

2103 cat<<-EOF | html p

2104 有効なチーム名を入力してください。

2105 カンマだけ、「なし」という名前は使えません。

2106 EOF

2107 echo "有効なチーム名を入力してください。" | html p

2108 else

2109 grp_add_team $grid "$team" $usel

2110 fi

2111 elif [ x"$rm" = x"rmteam" ]; then

2112 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2113 rmteam=`getpar rmteam|sed "s/'/''/g"`

2114 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2115 gname='$grp' AND user='$user' AND key='team'\

2116 AND val='$rmteam';\"`" ]; then

2117 grp_rm_team $grid "$rmteam" $usel

2118 else

2119 echo "所属していないチームの除去操作はできません。"|html p

2120 fi

2121 else

2122 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2123 fi

2124 fi

2125 fi

2126 # New entry

2127 sql="select /* Ahh, ugly SQL, I wanna fix... */

2128 case

2129 when (select user from grp_adm where

2130 gname=(select gname from grp where rowid=$grid)

2131 and user=a.name) is not null

2132 then 'k'

2133 else ''

2134 end || a.rowid||

2135 ','||a.gecos as NAME,

2136 (SELECT count(author) /* Put post count for scoring 2016-08-01 */

2137 FROM article NATURAL JOIN article_s

2138 WHERE blogid IN

2139 (SELECT id FROM blog_s

2140 WHERE key='owner'

2141 AND val=(SELECT gname FROM grp where rowid=$grid))

2142 AND author=a.name AND key='text')

2143 as POST,

2144 (SELECT group_concat(val, ',')

2145 FROM grp_mem_m

2146 WHERE gname='$grp' AND user=a.name AND key='team') as TEAM

2147 FROM

2148 gecoses a

2149 WHERE name in (select user from grp_mem where

2150 gname=(select gname from grp where rowid=$grid))

2151 ORDER by a.gecos;"

2152 err grpaction: "`echo \"$sql\"`"

2153 tf=$tmpd/title.$$

2154 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2155 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2156 for=\"cmadmin\">グループ管理者委任</label>

2157 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2158 </p></div>"

2159 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2160 for=\"conf\">グループ登録解除</label>

2161 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2162 <p>この操作による通知は本人に行きません。

2163 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2164 </div>"

2165 # Get team list to which current user belongs into $hexteams

2166 myhexteams=$(hexteams "$grp" "$user")

2167 allhexteams=$(hexteams "$grp")

2168 if [ -n "$myhexteams" ]; then

2169 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2170 for=\"cmrmteam\">チーム属性除去</label>

2171 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2172 を除去します: `cgi_checkbox teamconfirm yes` 確認

2173 <p>この操作による通知は本人に行きません。

2174 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2175 </div>

2176 "

2177 fi

2178 b1='<label> <input type="checkbox" name="usel" value="'

2179 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2180 #b2='"> <span>' b3='</span></label>'

2181 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2182 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2183 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2184 cgi_form grpaction<<EOF \

2185 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2186 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2187 | _m4 -D_TITLE_="spaste(\`$tf')" \

2188 -D_SUBTITLE_="チェック後操作ボタン" \

2189 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2190 $layout/form+dump.m4.html

2191 <p>下でチェックした人を対象として:</p>

2192 <div class="foldtabs">

2193 `cgi_radio rm addteam 'id="cmteam"'`<label

2194 for="cmteam">同じチーム属性を付与</label>

2195 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2196 `cgi_datalist_h teams $allhexteams`

2197 </div>

2198 ${rmteammsg}

2199 `cgi_radio rm send id="sendmsg"`<label

2200 for="sendmsg">メッセージ送信</label>

2201 <div>

2202 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2203 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2204 `cgi_textarea text "" cols=40`

2205 </div>

2206 ${isowner:+$cmmsg$excmsg}

2207 `cgi_radio rm close id="x"`<label for="x">×</label>

2208 </div>

2209 <h4>$grp 参加者一覧</h4>

2210 <table class="td2r">

2211 `sq $db -header -html "$sql"`

2212 </table>

2213 `cgi_hidden grp $grid`

2214 EOF

2215 }

2216 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2217 # Create TEMPORARY VIEW

2218 query<<EOF

2219 CREATE TEMPORARY VIEW writeusers AS

2220 SELECT DISTINCT author FROM article

2221 WHERE id in (

2222 select id from article where blogid=(select id from blog where rowid=$1)

2223 );

2224 CREATE TEMPORARY VIEW movablegroups AS

2225 SELECT g.rowid growid , g.gname

2226 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2227 ON grp.gname=gm.gname -- そのユーザが属している

2228 AND user='$user') g -- グループに絞る

2229 WHERE (SELECT author FROM writeusers

2230 EXCEPT

2231 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2232 IS NULL;

2233 $2

2234 EOF

2235 }

2236 sql4readableblogs() {

2237 # Create view of blogs that can be readable to $user

2238 # Blog is readable when:

2239 # 1: blog owner is an user

2240 # 2: else, 2.1: owner-group where the $user belongs

2241 # 2.2: else, owner-group is not moderated

2242 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2243 cat<<EOF ## | tee tmp/sql.out

2244 CREATE TEMPORARY VIEW readableblogs AS

2245 SELECT blog.rowid rid, id, author

2246 FROM blog

2247 NATURAL JOIN

2248 (SELECT id, val owner FROM blog_s WHERE key='owner') bs

2249 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2250 THEN 1 -- blog owner is an user, READABLE

2251 WHEN (SELECT val FROM grp_s

2252 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2253 AND

2254 (SELECT user FROM grp_mem

2255 WHERE gname=bs.owner AND user='$user') IS NULL

2256 THEN 0

2257 ELSE 1

2258 END;

2259 EOF

2260 }

2261 editheading() { # $1=rowid-of-heading

2262 rowid=${1%%[!A-Z0-9a-z_]*}

2263 if [ -z "$rowid" ]; then

2264 echo "話題番号が未指定です。" | html p

2265 return

2266 fi

2267 owner=`getvalbyid blog owner $rowid`

2268 title=`getvalbyid blog title $rowid`

2269 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2270 | _m4 -D_TITLE_="修正" \

2271 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2272 -D_BLOGS_="" -D_DUMPTABLE_="" \

2273 -D_FORM_="syscmd(\`cat')" \

2274 $layout/html.m4.html $layout/form+dump.m4.html

2275 # Move to group

2276 if isuser "$owner"; then

2277 crview4article $rowid

2278 n=`query "SELECT count(*) FROM writeusers;"`

2279 err N=$n

2280 if [ $((n)) -gt 0 ]; then

2281 err ROWID=$rowid

2282 sql="SELECT growid || ':' || gname FROM movablegroups;"

2283 cat<<-EOF

2284 <div class="fold">

2285 `cgi_checkbox mv send id="mv"`<label

2286 for="mv">この話題をグループ所有に移動する</label>

2287 <div>

2288 <form action="?mvart" method="POST" enctype="multipart/form-data">

2289 移動先グループ:

2290 <select name="mv2grp">

2291 EOF

2292 query ".mode html"

2293 query<<-EOF |

2294 $sql

2295 .mode list

2296 EOF

2297 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2298 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2299 cat<<-EOF

2300 </select>

2301 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2302 そのグループに加入しているものに限られます)</p>

2303 <p>`cgi_checkbox cfm yes`<label>確認

2304 (この操作は元に戻すことができません)</label></p>

2305 `cgi_hidden blogrowid $rowid`

2306 `cgi_submit 移動`

2307 `cgi_reset Reset`

2308 </form>

2309 </div>

2310 </div>

2311 EOF

2312 fi

2313 # end of isuser "$owner"

2314 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2315 [ -n "$hexteams" ];}; then

2316 none="`echo なし|hexize`"

2317 cat<<-EOF

2318 <div class="fold">

2319 `cgi_checkbox mv2team send id="mv2team"`<label

2320 for="mv2team">この話題を以下のチームのものにする</label>

2321 <div><p>現在の所属チーム設定:

2322 `query "SELECT

2323 coalesce((SELECT val FROM blog_s

2324 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2325 AND key='team'),

2326 ':なし');"`</p>

2327 <form action="?mvart" method="POST" enctype="multipart/form-data">

2328 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2329 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2330 `cgi_hidden blogrowid $rowid`<br>

2331 `cgi_submit 移動`

2332 `cgi_reset Reset`

2333 </form></div></div>

2334 EOF

2335 fi

2336 }

2337 mvart() { # move diary to some group or team

2338 # or move blog of group to team which belong to the group

2339 blogrowid=`getpar blogrowid`

2340 cfm=`getpar cfm`

2341 ##### echo move blog:$blogrowid to $mv2grp | html p

2342 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2343 . ./s4-blog.sh

2344 if [ -z "$blogrowid" ]; then

2345 echo "無効な指定です(mvart)。" | html p

2346 return

2347 elif [ x"$cfm" != x"yes" ]; then

2348 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2349 elif { mv2grp=`getpar mv2grp`

2350 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2351 [ -n "$mv2grp" ]; }; then

2352 crview4article $blogrowid

2353 ########## TRANSACTION BEGIN

2354 query "BEGIN;"

2355 n=`query "SELECT count(*) FROM writeusers;"`

2356 err Nwriteuser=$n

2357 if [ $((n)) -gt 0 ]; then

2358 query<<-EOF

2359 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2360 WHERE key='owner'

2361 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2362 AND $mv2grp IN (SELECT growid FROM movablegroups);

2363 EOF

2364 fi

2365 query "END;"

2366 ########## TRANSACTION END

2367 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2368 [ -n "$mv2team" ];}; then

2369 # blog owner can move it to ANY team

2370 case "$mv2team" in

2371 'なし')

2372 cat<<-EOF

2373 DELETE FROM blog_s

2374 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2375 AND key='team';

2376 EOF

2377 ;;

2378 "") ;;

2379 *)cat<<-EOF

2380 BEGIN;

2381 REPLACE INTO blog_s(id, key, val)

2382 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2383 'team', '$mv2team');

2384 REPLACE INTO blog_s(id, key, val)

2385 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2386 'notify', 'all'); -- Change notify to all

2387 END;

2388 EOF

2389 esac | query

2390 fi

2391 blog_reply $blogrowid

2392 echo yes | html p

2393 }

2394 editart() { # $1=article-rowid $2=blogrowid

2395 rowid=${1%%[!A-Z0-9a-z_]*}

2396 blogrowid=${2%%[!A-Z0-9a-z_]*}

2397 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2398 echo "表示する記事番号が未指定です。" | html p

2399 return

2400 fi

2401 owner=`getvalbyid blog owner $blogrowid`

2402 title=`getvalbyid blog title $blogrowid`

2403 author=`getvalbyid article author $rowid`

2404 err EDITart: owner=$owner, author=$author

2405 if isgrpowner $user $owner; then

2406 : EDIT OK

2407 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2408 echo "本人か所有者しか編集できません." | html p

2409 return

2410 fi

2411 aid=`query "select id from article where rowid=$rowid;"`

2412 tmpout=$tmpd/editart.$$.out

2413 GF_ACTION="?replyblog+$blogrowid#$aid" \

2414 edittable $formdir/article.def article $rowid \

2415 > $tmpout

2416 rm -f /tmp/editart.out

2417 # Cannot use pipelining to m4 with genform() because of stdin stack

2418 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2419 -D_FORM_="syscmd(cat $tmpout)" \

2420 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2421 -D_BLOGS_= -D_DUMPTABLE_= \

2422 $layout/html.m4.html $layout/form+dump.m4.html

2423 }

2424 send2mem() {

2425 rowid=`getpar grp`

2426 if [ -z "$rowid" ]; then

2427 echo "グループが未指定です。" | html p

2428 return

2429 fi

2430 message=`getpar message`

2431 if [ -z "$message" ]; then

2432 echo "文章を入れてください。" | html p

2433 return

2434 fi

2435 grp=`getgroupbyid $rowid`

2436 members=`collectemail $grp`

2437 # smail rcpt subj (file)

2438 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2439 $urlbase?grp+$rowid

2440 グループ $grp に所属する

2441 `gecos $user` さんよりメッセージ:

2442

2443 $message

2444 EOF

2445 cat<<EOF

2446 <p>以下の宛先に送信しました。</p>

2447 <pre>

2448 $members

2449 </pre>

2450 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2451 EOF

2452 }

2453 joingrpadmit() {

2454 # $1=yes/no $2=session-key

2455 if [ -z "$2" ]; then

2456 echo "bye bye" | html p; return

2457 fi

2458 t_usr=`session=$2 getpar user`

2459 t_grp=`session=$2 getpar group`

2460 err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2461 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2462 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2463 echo "無効な加入依頼です。" | html p

2464 echo "有効期限が切れたか、

2465 他の管理者がいる場合は処理済みの可能性があります。" | html p

2466 return

2467 fi

2468 if ! isgrpowner "$user" $t_grp; then

2469 echo "グループ管理者のみの機能です。" | html p; return

2470 fi

2471 case $1 in

2472 yes) joingrp "$t_grp" "$t_usr" yes "$t_usr" ;;

2473 no) joingrp "$t_grp" "$t_usr" no "$t_usr" ;;

2474 *)

2475 echo "無効な指定です($1)。" | html p

2476 return ;;

2477 esac

2478 gid=$(query "select rowid from grp where gname=`sqlquote $t_grp`;")

2479 rcpts="`getgroupadminmails $t_grp` $user"

2480 err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2481 body="グループ $t_grp

2482 に

2483 $t_usr

2484 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2485 しました。"

2486 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2487 query "delete from session where id='$2';"

2488 echo "$body" | html p

2489 }

2490

2491 joingrprequest() {

2492 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2493 jss="joingrp-`date +%s`-`genrandom 12`"

2494 addsession $jss +${memoplimitdays}days

2495 query "replace into par values('$jss', 'group', 'string', `sqlquote $1`),

2496 ('$jss', 'user', 'string', `sqlquote $user`);"

2497 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2498 $url

2499 $user さんから

2500 グループ $1

2501 に加入依頼がありました。

2502

2503 承認する:

2504 $urlbase?joingrpadmit+yes+$jss

2505

2506 白紙に戻す:

2507 $urlbase?joingrpadmit+no+$jss

2508 EOF

2509 echo "管理者に加入依頼を出しました。

2510 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2511 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2512 }

2513 joingrp() {

2514 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2515 err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2516 if isgrpowner "$user" "$1"; then

2517 isowner="yes"

2518 elif [ -n "$5" ]; then

2519 isowner="yes"

2520 else

2521 isowner=""

2522 fi

2523 err jg:isgrpowner: isowner="$isowner"

2524 if [ -n "$isowner" ]; then

2525 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2526 elif [ x"$2" != x"$user" ]; then # if user is not login user

2527 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2528 return

2529 elif [ x"$3" = x"no" ]; then

2530 : # Do not pursue those who leave

2531 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2532 : # Member can change own email address for the joining moderated group

2533 else # adding user is $user itself

2534 case `getgroupattr $1 regmode` in

2535 moderated)

2536 joingrprequest "$@" # Request only

2537 return

2538 ;;

2539 *)

2540 ;;

2541 esac

2542 fi

2543 qgname=`sqlquote $1`

2544 cond="where gname=$qgname and user='$2'"

2545 if [ x"$3" = x"yes" ]; then

2546 query "replace into grp_mem values($qgname, '$2');"

2547 if [ -n "$4" ]; then

2548 if msg=`emaildomaincheck "$4"`; then

2549 err "replace into grp_mem_s values($qgname, '$user', 'email', \

2550 'string', '$4', NULL);"

2551 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2552 'string', '$4', NULL);"

2553 else

2554 echo $msg

2555 fi

2556 else

2557 query "delete from grp_mem_s $cond and key='email';"

2558 fi

2559 if [ -n "$5" ]; then # as ADMIN

2560 # Coming here means newly created group

2561 sql="select case\

2562 when (select count(*) from grp_mem where gname=$qgname)=1\

2563 then (select user from grp_mem\

2564 where gname=$qgname and user='$user')\

2565 else '' end; "

2566 err NewGrpChk: $sql

2567 if [ -n "`query \"$sql\"`" ]; then

2568 err ADMIN: "replace into grp_adm values($qgname, '$user');"

2569 query "replace into grp_adm values($qgname, '$user');"

2570 fi

2571 fi

2572 else

2573 query "delete from grp_mem $cond;

2574 delete from grp_mem_s $cond;

2575 delete from grp_mem_m $cond;"

2576 fi

2577 }

2578 grp_add_team() (

2579 # $1=grp-rowid $2=team $3...=user-rowid(s)

2580 grp=`getgroupbyid $1`

2581 team=$2; shift; shift

2582 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2583 { echo "BEGIN;"

2584 for user; do

2585 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2586 '$grp',\

2587 (SELECT name FROM user WHERE rowid=$user),\

2588 'team', 'string', '$team');"

2589 done

2590 echo "END;"

2591 } | query

2592 )

2593 grp_rm_team() (

2594 # $1=grp-rowid $2=team $3...=user-rowid(s)

2595 grid=$1

2596 qgrp=$(sqlquote "`getgroupbyid $grid`")

2597 team=$2; shift; shift

2598 [ -z "$grid" -o -z "$team" ] && return

2599 { echo "BEGIN;"

2600 for user; do

2601 echo "DELETE FROM grp_mem_m\

2602 WHERE gname=$qgrp \

2603 AND user=(SELECT name FROM user WHERE rowid=$user)\

2604 AND key='team' AND val='$team';"

2605 done

2606 cat<<-EOF

2607 DELETE FROM blog_s

2608 WHERE rowid=(

2609 SELECT rowid

2610 FROM blog_s a

2611 WHERE key='team'

2612 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2613 AND NOT EXISTS (SELECT * FROM grp_mem_m

2614 WHERE key='team' AND val=a.val -- a.val=team

2615 AND gname = (SELECT val FROM blog_s b

2616 WHERE a.id=b.id AND key='owner')

2617 ));

2618 EOF

2619

2620 echo "END;"

2621 } | query

2622 )

2623 grp_reg_adm() {

2624 # $1=grp-rowid $2...=user-rowid

2625 grid=$1

2626 grp=`getgroupbyid "$1"`

2627 if [ -z "$grp" ]; then

2628 echo "無効なグループIDです" | html p; return

2629 fi

2630 if ! isgrpowner $user "$grp"; then

2631 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2632 fi

2633 shift

2634 for urid; do

2635 newadm=`query "select name from user where rowid=$urid;"`

2636 if [ -z "$newadm" ]; then

2637 echo "指定ユーザIDがおかしいようです。" | html p; return

2638 fi

2639 err GRP_reg_adm: "replace into grp_adm values(`sqlquote $grp`, '$newadm');"

2640 err ismember $newadm $grp

2641 if ismember $newadm $grp; then

2642 # OK, go ahead

2643 getgname="(select gname from grp where rowid=$grid)"

2644 query "replace into grp_adm values($getgname, '$newadm');"

2645 # confirm insertion

2646 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2647 if [ -n "`query \"$sql;\"`" ]; then

2648 echo "追加完了: $newadm" | html p

2649 else

2650 echo "追加失敗($1 $urid)" | html p

2651 fi

2652 fi

2653 showgroup $grid

2654 done

2655 }

2656 dumptable() {

2657 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2658 # textのフィールドだけ全てダンプにしたほうがいいか

2659 # $DT_VIEW sets link

2660 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2661 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2662 VIEW=${DT_VIEW-replyblog}

2663 if [ -n "$VIEW" ]; then

2664 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VIE</a><a href=\"$myname?$VIEW+\\2#bottom\">W</a>"

2665 fi

2666 # $DT_CHLD=ChildTable:BindColumn

2667 if [ -n "$DT_CHLD" ]; then

2668 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

2669 cntall="(select count($_i) from $_t where $_i=a.id)"

2670 # XXX: Dirty workaround for slow subquery of acclog

2671 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

2672 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

2673 cntnew="(select count(val) from ${_t}_s where key='ctime' \

2674 and id in (select id from $_t where $_i=a.id) \

2675 and val > coalesce((select time from myacclog where \

2676 tblrowid=a.rowid),\

2677 '1970-01-01'))"

2678 cnt="$cntnew as '新着', $cntall as '総数',"

2679 dt_class=" td2r td3r dumpblogs"

2680 fi

2681 # Construct join expression

2682 eav="" scols=""

2683 pk=`gettblpkey $2`

2684 substr=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2685 substr=${substr:-val}

2686 for col in ${3:-`gettbl_s_cols $2`}; do

2687 case $col in

2688 gecos) scols="$scols${scols:+, }${col#}"

2689 continue ;; # built-in column name

2690 *:*) col=${col%:*} as=${col#*:} ;;

2691 *) as=${col} ;;

2692 esac

2693 eav=$eav${eav:+,}" max(case key when '$col' then $substr end) as $as"

2694 scols="$scols${scols:+, }b.$as"

2695 done

2696 #case author when '$user' then a.rowid else '---' end as ID,

2697 sql=${DT_SQL:-"select \

2698 a.rowid as LINK,\

2699 $cnt\

2700 $scols from $2 a left join\

2701 (select $pk,$eav,

2702 max(case key when 'owner'

2703 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

2704 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

2705 err SQL="`echo \"$presql$sql\"|tr -d '\n'`"

2706 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>,"

2707 <div>

2708 <div class="dumptable">

2709 <table class="b$dt_class">

2710 `sq -header -cmd ".mode $1" $db "$presql$sql"`

2711 </table>

2712 </div>

2713 </div>

2714 EOF

2715 }

2716

2717 par2table() (

2718 # copy current parameters of par into destination table

2719 # $1=definition-file

2720 # Using $user and $session

2721 # Return value:

2722 # 0: Stored successfully

2723 # 1: Insufficient fillings

2724 # 2: No permission to modify the record

2725 # 3: Invalid rowid

2726 # 4: SUCCESS to delete

2727 # 5: Stop deletion for lack of confirm check

2728 # 6: Password length too short

2729 # 7: Password mismatch

2730 # 8: Old password incorrect

2731 rowid=`getpar rowid`

2732 err ...........rowid=$rowid

2733 if [ ! -e $1 ]; then

2734 echo "テーブル定義ファイルが見付かりません" | html p

2735 exit 1

2736 fi

2737 tbl=${1%.def}

2738 tbl=${tbl##*/}

2739 if [ -n "$rowid" ]; then # Modify existing entry

2740 if [ x"$tbl" = x"user" ]; then

2741 rowowner=`query "select name from $tbl where rowid=$rowid;"`

2742 elif [ x"$tbl" = x"grp" ]; then

2743 sql="select gname from $tbl where rowid=$rowid;"

2744 ##err p2t:grp:q $sql

2745 isgrpowner $user "`query $sql`" && rowowner=$user

2746 else

2747 rowowner=`query "select owner from $tbl where rowid=$rowid;"`

2748 rowowner=${rowowner:-`query "select author from $tbl

2749 where rowid=$rowid;"`}

2750 fi

2751 ### err rowowner=$rowowner

2752 if [ x"$user" != x"$rowowner" ]; then

2753 echo "他人のレコードはいじれないの" | html p

2754 return 2

2755 elif [ -z "$rowowner" ]; then

2756 echo "指定したレコードはないみたい" | html p

2757 return 3

2758 fi

2759 rm=`getpar rm` cfm=`getpar confirm`

2760 # Editing existent entry

2761 if [ x"$rm" = x"yes" ]; then

2762 if [ x"$rm$cfm" = x"yesyes" ]; then

2763 query "delete from $tbl where rowid=$rowid;"

2764 return 4

2765 else

2766 echo "消去確認のチェックがないので消さなかったの..." | html p

2767 return 5

2768 fi

2769 fi

2770 fi

2771

2772 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

2773 if [ -n "$rowid" ]; then

2774 # Update of existing record

2775 for col in `gettblcols $tbl`; do

2776 val=`getparquote $col`

2777 [ -z "$val" ] && continue

2778 err query "update $tbl set $col=$val where rowid=$rowid"

2779 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

2780 sql="update $tbl set $col=$val where rowid=$rowid;"

2781 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

2782 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

2783 ## User name cannot be changed with interface provided with this

2784 ## script. But we offer the trigger to change owner user

2785 ## of blog_s table.

2786 err "select quote($col) from $tbl where rowid=$rowid;"

2787 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

2788 cat<<-EOF | query

2789 BEGIN;

2790 $sql

2791 update blog_s set val=$val

2792 where key='owner' and val=$old;

2793 COMMIT;

2794 EOF

2795 ## XX: DIRTY Hack Ends here

2796 ## We should keep blog's owner as a single column which has

2797 ## foreign key constraint with primary key of grp/user.

2798 aa

2799 else

2800 query "$sql"

2801 fi

2802 done

2803 # Then, set up $pval for further insertion of tbl_s and tbl_m

2804 for col in `gettblpkey $tbl`; do

2805 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

2806 pval="$pval${pval:+, }\"$val\""

2807 done

2808 else

2809 # New entry

2810 # Generate values() for primary keys

2811 for col in `gettblpkey $tbl`; do

2812 # Genuine primary keys for _m and _s

2813 val=`getvalquote $tbl $col`

2814 [ -z "$val" ] && continue

2815 pval="$pval${pval:+, }$val"

2816 done

2817 err pval=$pval

2818 for col in `gettblfkey $tbl`; do

2819 # args for values() to insertion into master table

2820 val=`getvalquote $tbl $col`

2821 [ -z "$val" ] && continue

2822 formaster=$formaster"${formaster:+, }$val"

2823 done

2824 formaster="$pval${formaster:+, }$formaster"

2825 err formaster=$formaster

2826 if [ -z "$formaster" ]; then

2827 echo "項目を全て埋めてください" | html pre

2828 return 1

2829 fi

2830 err "replace into $tbl values($formaster);"

2831 query "replace into $tbl values($formaster);"

2832 ## Insertion to master table, done

2833 fi

2834

2835 for kt in s m; do

2836 tb2=${tbl}_$kt

2837 for col in `gettbl_${kt}_cols $tbl`; do

2838 ptype=`getpartype $col "limit 1"`

2839

2840 # First, check update of existing entries in _m

2841 if [ $kt = m ]; then

2842 # sessID|address.1.22|string|Somewhere-x.y.z

2843 sql=""

2844 err dots from query "select var from par where var like '$col.%';"

2845 for v in `query "select var from par where var like '$col.%';"`; do

2846 # v=address.1.22

2847 st_rowid=${v##*.}

2848 origcol=${v%%.*} # original column derived from

2849 err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

2850 ##case `getpartype $v` in

2851 err CASE `gettbl_coltype $tbl/$origcol` in

2852 err edit flag = `getpar action.$v`

2853 case `getpar action.$v` in

2854 rm)

2855 if [ x`getpar confirm.$v` = x"yes" ]; then

2856 newsql="delete from $tb2"

2857 else

2858 echo "削除確認未チェック" | html p

2859 fi ;;

2860 edit)

2861 case `gettbl_coltype $tbl/$origcol` in

2862 image|document|binary)

2863 file=$tmpd/`getparfilename $v`

2864 err type=file=$file

2865 [ -z "$file" ] && continue

2866 bn=${file##*/}

2867 bin="X'"$(hexize $file)"'"

2868 ct=`file --mime-type $file|cut -d' ' -f2`

2869 type=\"file:$ct\"

2870 newsql="update $tb2 set val='$bn', type=$type, bin=$bin"

2871 cachedir=`getcachedir "$tbl/$rowid"`

2872 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

2873 rm -r $cachedir

2874 ;;

2875 *)

2876 newsql="update $tb2 set val=(select val from par where var \

2877 like '$col.%.$st_rowid')"

2878 ;;

2879 esac

2880 ;;

2881 *) # maybe "keep", do not modify value

2882 continue

2883 ;;

2884 esac

2885 # err newsql=$newsql

2886 sql=$sql$nl"$newsql where rowid=$st_rowid;"

2887 done

2888

2889 if [ x"$bin" = x"NULL" ]; then

2890 err repl:normal sql=`echo $sql`

2891 query "$sql

2892 delete from $tb2 where type='string' and val='';"

2893 err repl:normal done

2894 else

2895 sqlfile="$tmpd/sqlf.$$"

2896 echo "$sql" > $sqlfile

2897 err repl:sqlfile=`ls -lF $sqlfile`

2898 query ".read $sqlfile"

2899 err repl:done

2900 fi

2901 # Rest of kt==m: set multiple mode

2902 nr=`getparcount $col`

2903 else

2904 nr=1 # for kt==s, number of records is 1

2905 fi

2906

2907 i=0

2908 while [ $i -lt $nr ]; do

2909 limit="limit 1 offset $i"

2910 i=$((i+1)) # increase beforehand against continue

2911 val=`getvalquote $tbl $col "$limit"`

2912 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

2913 err $col=$val

2914 bin=NULL

2915 err partype$col=`getpartype $col "$limit"`

2916 case $ptype in

2917 file) file=$tmpd/`getparfilename $col "$limit"`

2918 err parfile-$col=$file

2919 [ -z "$file" ] && continue

2920 bin="X'"$(hexize $file)"'"

2921 ct=`file --mime-type $file|cut -d' ' -f2`

2922 type=\"file:$ct\" ;;

2923 "*"*) continue ;; # foreign table

2924 *) type=\"string\" ;;

2925 esac

2926 case `gettbl_coltype $tbl/$col` in

2927 password) # special care for password

2928 # name={password,pswd1,pswd2}

2929 p1=`getpar pswd1 "$limit"`

2930 if [ -z "$p1" ]; then

2931 continue # SKIP password setting, if p1 is empty

2932 else

2933 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

2934 ## err pswd=$pswd

2935 if pwcheck "$pswd"; then

2936 if [ x"$p1" = x"$p2" ]; then

2937 case "$p1" in

2938 ??????????*) ;;

2939 *) echo "パスワードは10字以上にしてください。" | html p

2940 return 6;;

2941 esac

2942 val="\"`echo $p1|mypwhash`\""

2943 else

2944 echo "2つの新パスワード不一致" | html p

2945 return 7

2946 fi

2947 else

2948 echo "旧パスワード違います" | html p

2949 return 8

2950 fi

2951 fi

2952 ;;

2953 esac

2954 err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

2955 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

2956 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

2957 if [ x"$bin" = x"NULL" ]; then

2958 err Normal-query: `echo $sql`

2959 query "$sql"

2960 else

2961 sqlfile="$tmpd/query.$$"

2962 echo "$sql" > $sqlfile

2963 err sqlfile=`ls -lF $sqlfile`

2964 query ".read $sqlfile"

2965 fi

2966 err p2t done

2967 done

2968 done

2969 done

2970 return 0

2971 ##err donee

2972 )

2973 genform() {

2974 # $1 = form definition file

2975 # $2, $3 (optional)= table name and ROWID

2976 # If $GF_VIEWONLY set and nonNull, output values without form

2977 # If $GF_ARGS set, use it as content-strings in the form

2978 # If $GF_OWNER set, use it as value of name="owner"

2979 # If $GF_STAGE set, use it as value of name="stage"

2980 forms="" hiddens="" rowid=$3

2981 if [ ! -e "$1" ]; then

2982 echo "そのようなデータベースはないようです($2)。" | html p

2983 return

2984 elif [ -n "$2" ]; then

2985 err genform1: "select * from $2 where rowid='$rowid'"

2986 rec=`query "select * from $2 where rowid='$rowid';"`

2987 if [ -z "$rec" ]; then

2988 pk=`gettblpkey $2`

2989 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

2990 err "select rowid from $2 where $pk='$rowid';"

2991 rec=`query "select rowid from $2 where $pk='$rowid';"`

2992 err rec-rowid=$rec

2993 rowid=$rec

2994 rec=$3

2995 fi

2996 if [ -z "$rec" ]; then

2997 echo "そんなレコードはないみたいね..." | html p

2998 return

2999 fi

3000 fi

3001 if [ -z "$GF_VIEWONLY" ]; then

3002 rm='<input id="rm" name="rm" type="checkbox"

3003 value="yes"><label for="rm">このエントリの削除</label>

3004 <span>ほんとうに消しますよ(確認)!

3005 <input name="confirm" type=checkbox value="yes">はい</span>'

3006 fi

3007 # Image Cache dir

3008 err genform: getcache=$2/$rowid

3009 td=`getcachedir "$2/$rowid"`

3010 while IFS=: read prompt name keytype type args; do

3011 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3012 sp="${args:+ }"

3013 form="" val=""

3014 if [ -n "$rowid" ]; then

3015 # err genform2a: Seeking for "$2.$name, type=$type"

3016 val=`getvalbyid $2 $name $rowid $td|htmlescape`

3017 err genform3a: getvalbyid $2 $name $rowid $td

3018 err genform3b: val="[$val]"

3019 fi

3020 if [ -n "$GF_VIEWONLY" ]; then

3021 is_hidden "$2" "$name" && continue

3022 fi

3023 case "$type" in

3024 text*)

3025 cgiform=cgi_multi_$type

3026 if [ -s $td/$name.count -a -n "$val" ]; then

3027 form=`$cgiform $name $td`

3028 val=$(echo "$val"|

3029 while read fn; do

3030 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3031 </td></tr>$nl"

3032 done)

3033 val="<table>$nl$val$nl</table>"

3034 else

3035 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3036 err genform: cgi_$type $name $val "$args"

3037 form=`cgi_$type $name "$val" "$args"`

3038 fi

3039 ;;

3040 [Rr][Aa][Dd][Ii][Oo])

3041 fh="<label><input type=\"radio\" name=\"$name\""

3042 form="`echo $args|sed -e \

3043 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3044 ;;

3045 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3046 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3047 ;;

3048 [Ss][Ee][Ll][Ee][Cc][Tt])

3049 fh="<select name=\"$name\">$nl"

3050 form=$(for l in $args; do

3051 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3052 done)

3053 if [ -n "$val" ]; then

3054 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3055 fi

3056 form="$fh$form</select>"

3057 ;;

3058 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3059 if [ -s $td/$name.count ]; then

3060 form=`cgi_multi_file $name $td "$args"`

3061 if [ -n "$val" ]; then

3062 hrfb="$myname?showattc+$2_m"

3063 val=$(echo "$val" \

3064 | while read fn; do

3065 data=`percenthex $td/$fn`

3066 #ct=`cat $td/$fn.content-type`

3067 ct=`file --mime-type $td/$fn|cut -d' ' -f2`

3068 ri=`cat $td/$fn.rowid`

3069 ## err fn=$fn, name=$name, ri=$ri; ls -lF $td 1>&3

3070 #imgsrc="<img src=\"data:$ct,$data\">"

3071 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3072 iconhref $td/$fn "$hrfb+$ri" ""

3073 done)

3074 fi

3075 else

3076 form="<input type=\"file\" name=\"$name\" $args>"

3077 if [ -n "$val" ]; then

3078 imgs=$(echo "$val"\

3079 |while read fn;do

3080 data=`percenthex $td/$fn`

3081 echo "<img src=\"data:image/png,$data\">$fn<br>"

3082 done)

3083 form=$form"<br>$imgs"

3084 val=$imgs # 2015-06-15

3085 else

3086 form="<input type=\"file\" name=\"$name\" $args>"

3087 fi

3088 fi

3089 ;;

3090 [Hh][Ii][Dd][Dd][Ee][Nn])

3091 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3092 args="value=\"$GF_STAGE\""

3093 fi

3094 form="<input type=\"hidden\" name=\"$name\" $args>"

3095 prompt='' # Remove prompt

3096 ;;

3097 [Aa][Uu][Tt][Hh][Oo][Rr])

3098 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3099 prompt="" ;;

3100 [Oo][Ww][Nn][Ee][Rr])

3101 val=${GF_OWNER:-$val}

3102 val=${val:-$user}

3103 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3104 prompt="" ;;

3105 [Uu][Ss][Ee][Rr])

3106 # XXX: is null $user ok?

3107 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3108 [ -n "$GF_VIEWONLY" ] && continue

3109 form="$user"

3110 ;;

3111 [Pp]assword)

3112 [ -n "$GF_VIEWONLY" ] && continue

3113 form="`cgi_passwd`"

3114 val=""

3115 ;;

3116 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3117 if [ -z "$rowid" ]; then

3118 val=`genserial`

3119 fi

3120 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3121 prompt="" ;;

3122 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3123 prompt=""

3124 ;;

3125 parent|path|blog*)

3126 prompt=""

3127 ;;

3128 "*"*)

3129 tail=$tail"``"

3130 continue ;;

3131 esac

3132 if [ -n "$prompt" ]; then

3133 if [ -n "${GF_VIEWONLY}" ]; then

3134 form=$val

3135 else

3136 :

3137 fi

3138 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3139 else

3140 hiddens=$hiddens$nl"$form"

3141 fi

3142 done < $1

3143 # enctype="multipart/form-data"

3144 cat<<EOF

3145 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3146 ${rowid:+$rm}

3147 <table class="b $2">

3148 $forms

3149 </table>$hiddens

3150 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3151 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3152 EOF

3153 if [ -z $GF_VIEWONLY ]; then

3154 cat<<EOF

3155 <input type="submit" name="sub" value="OK">

3156 <input type="reset" name="res" value="Reset">

3157 EOF

3158 fi

3159 cat<<EOF

3160 $GF_ARGS</form>

3161 $tail

3162 EOF

3163 }

3164 edittable() {

3165 # $1=form-def $2=table $3 rowid

3166 genform "$@"

3167 }

3168 viewtable() {

3169 GF_VIEWONLY=1 genform "$@"

3170 }

3171 showattc() {

3172 # $1=table_m $2=rowid

3173 err \$1=$1 \$2=$2

3174 if ! isfilereadable $user $1 $2; then

3175 contenttype; echo

3176 echo "このファイルは管理者のみしか見られません" | html p

3177 putfooter; exit

3178 fi

3179 idir=`umask 002; mktempd` || exit 1

3180 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3181 bin=$idir/$myname-$$.bin

3182 sql="select quote(bin) from $1 where rowid='$2';"

3183 err showattc: sql: $sql

3184 sq $db "$sql" | unhexize > $bin

3185 tv=`query "select type,val from $1 where rowid='$2';"`

3186 type=${tv%\|*} fn=${tv#*\|}

3187 err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3188 ct=${type#file:}

3189 case $ct in # all text/* changed to text/plain

3190 text/*)

3191 charset=`nkf -g $bin|cut -d' ' -f1`

3192 case $charset in

3193 ASCII*) charset="" ;;

3194 esac

3195 ct="text/plain${charset:+; charset=$charset}"

3196 ;;

3197 esac

3198 contenttype "$ct"

3199 echo "Content-Disposition: filename=\"$fn\""

3200 echo "Content-Length: " `cat $bin | wc -c`; echo

3201 #echo "Content-Type: " ${type#file:}; echo

3202 cat $bin

3203 }

3204 #

3205 # Some default stupid handler on CGI values

3206 #

3207 default_storedb() {

3208 # ARG: $1=table-def-file

3209 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3210 tbl=`basename $1`

3211 tbl=${tbl%.def}

3212 cols="`grep :text $1|cut -d: -f2`"

3213 col=`echo "$cols"|head -1`

3214 vcol=`getpar $col`

3215 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3216 if [ -n "$vcol" ]; then

3217 par2table $1

3218 else

3219 return 2 # No insertion occurred

3220 fi

3221 }

3222

3223 default_view() { # $1=def-file

3224 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3225 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3226 default_storedb "$@"

3227 query "select rowid from $tbl order by rowid desc;" \

3228 | while read rowid; do

3229 viewtable $1 $tbl $rowid

3230 done | _m4 -D_TITLE_="$tbl" \

3231 -D_FORM_="`genform $1`" \

3232 -D_DUMPTABLE_="syscmd(cat)" \

3233 $layout/html.m4.html $layout/form+dump.m4.html

3234 }

3235 default_viewtext() { # $1=def-file

3236 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3237 default_storedb "$@"

3238 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3239 | _m4 -D_TITLE_="$tbl" \

3240 -D_FORM_="`genform $1`" \

3241 -D_DUMPTABLE_="syscmd(cat)" \

3242 $layout/html.m4.html $layout/form+dump.m4.html

3243 }

3244 default_smail() {

3245 default_storedb "$@"

3246 if [ $? -eq 2 ]; then

3247 _m4 -D_TITLE_="入力" \

3248 -D_FORM_="`genform $1`" \

3249 -D_DUMPTABLE_="" \

3250 $layout/html.m4.html $layout/form+dump.m4.html

3251 return

3252 fi

3253 cond=""

3254 for pk in `gettblpkey $tbl`; do

3255 pv=$(sqlquote $(getpar $pk))

3256 cond="$cond${cond:+ and }$pk=$pv"

3257 done

3258 sql="select rowid from $tbl where $cond;"

3259 rowid=`query "$sql"`

3260 err smail1 - "$sql" "-> rowid=$rowid"

3261

3262 while IFS=: read prompt name keytype type args; do # Read from $1

3263 val=`getpar $name`

3264 if [ -n "$val" ]; then

3265 text="$text

3266 $prompt

3267 $name=$val

3268 ---------------------------------------------------------"

3269 fi

3270 case "$type" in

3271 image|document|file)

3272 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3273 fns=$(echo "$fn"|while read fn; do

3274 err mv $tmpd/$fn.orig $tmpd/$fn

3275 mv $tmpd/$fn.orig $tmpd/$fn

3276 rm $tmpd/$fn.rowid # Remove cache flag

3277 err "`ls $tmpd/$fn`"

3278 echo $fn

3279 done)

3280 files="$files $fns"

3281 ;;

3282 esac

3283 done < $1

3284 err FILES=$files "`ls -lF $tmpd`"

3285 subj="from ${REMOTE_ADDR}"

3286 (echo "$url"

3287 echo "への書き込みがありました。"

3288 echo "------"

3289 echo "$text"

3290 ) | (cd $tmpd &&

3291 err LS="`ls -lF`" &&

3292 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3293 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3294 echo "以下の内容で送信しました。" | html p

3295 viewtable $1 $tbl \

3296 `query "select rowid from $tbl order by rowid desc limit 1;"`

3297 echo "戻る" | html a "href=\"?\""

3298 }