s4: 4188f5b3fd67 s4-funcs.sh

s4

view s4-funcs.sh @ 309:4188f5b3fd67

Use "quier()" instead of "sq $db" at parameter setup

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sat, 15 Oct 2016 16:22:39 +0859
parents	e38ea2702570
children	e8f1847eebca

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 query "replace into session values('$1', '$expire');"

961 # Remove old session parameters

962 now=`expire now`

963 query "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 query "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 grps # When removing a group, switch to grp-list

1362 return # and return

1363 fi

1364 fi

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

1366 fi

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

1368 grid=$(query $sql)

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

1370 grp $grid

1371 }

1372 groupman() {

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

1374

1375 GF_STAGE="grpconf"

1376 GF_STAGE=groupupdate

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

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

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

1380 -D_DUMPTABLE_="syscmd(cat)" \

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

1382 }

1383 userconf() {

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

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

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

1387 }

1388 groupconf() {

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

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

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

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

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

1394 err gcon \$1=$1 rowid=$rowid

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

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

1397 }

1398 mems() {

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

1400 kwd=`getpar kwd`

1401 listmember $kwd

1402 }

1403 grps() {

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

1405 kwd=`getpar kwd`

1406 listgroup $kwd \

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

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

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

1410 $layout/form+dump.m4.html

1411 }

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

1413 gpg=`getpar grp`

1414 grid=${1:-$gpg}

1415 grp=`getgroupbyid "$grid"`

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

1417 ## . ./s4-blog.sh

1418 jg=`getpar joingrp`

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

1420 err jg=$jg, grp=$grp

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

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

1423 fi

1424 htmlheader=$layout/html.m4.html

1425 showgroup "$grid"

1426 }

1427 listnewblogsql() { # $1=user

1428 cat<<EOF

1429 `sql4readableblogs`

1430 with article_ctime as (

1431 select id,blogid,author,val ctime

1432 from article join article_s s using(id)

1433 where s.key='ctime'

1434 ), blog_title_owner as (

1435 select blg.rid brid, id,

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

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

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

1439 ), acclog_user as (

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

1441 ), blogall as (

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

1443 from blog_title_owner b, article_ctime ac

1444 on b.id=ac.blogid

1445 )

1446 select brid LINK,

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

1448 coalesce((SELECT time FROM acclog_user

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

1450 < ctime

1451 ) "新着",

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

1453 /* owner gecos */

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

1455 from blogall bl left join acclog_user l

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

1457 group by bl.id

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

1459 limit 10;

1460 EOF

1461 }

1462

1463 search_form() {

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

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

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

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

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

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

1470 cat<<-EOF

1471 <div class="fr">

1472 <form action="$myname">

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

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

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

1476 </form>

1477 </div>

1478 EOF

1479 }

1480

1481 showhome() {

1482 # $1=userRowIdToShow

1483 err showhome \$1=$1

1484 case "$1" in

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

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

1487 esac

1488 err ShowHome: uname=$uname

1489 gecos=`gecos "$uname"`

1490 err SH:gecos=$gecos

1491 GF_VIEWONLY=1

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

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

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

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

1496 # Display folders

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

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

1499 and type like 'file:%';"

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

1501 nfile=`query "$sql"`

1502 err nfile=$nfile

1503 if [ $nfile -gt 0 ]; then

1504 conflink="$conflink /

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

1506 fi

1507 fi

1508 . ./s4-blog.sh

1509

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

1511 search_form > $sf

1512 echo "$gecos さん" > $tf

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

1514

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

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

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

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

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

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

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

1522 echo '">'

1523 } > $imf

1524 fi

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

1526 val='$uname';"`

1527 listblog $uname > $bf

1528

1529 hometail=$tmpd/tail.$$

1530 mkfifo $hometail

1531

1532 #Calling listgroupbytable, originally here

1533

1534 (

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

1536 # Display Most Recent Entry

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

1538 shortval=${shortval:-val}

1539

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

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

1542 ctime,

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

1544 (SELECT gecos FROM gecoses

1545 WHERE name=(SELECT val FROM blog_s

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

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

1548 FROM blog b

1549 JOIN

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

1551 FROM article a, article_s s

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

1553 GROUP BY blogid ORDER BY val DESC LIMIT 50

1554 ) m

1555 ON b.id=m.blogid;"

1556 # This should be as follows

1557 : <<EOF

1558 WITH arts AS(

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

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

1561 FROM article a NATURAL JOIN article_s s

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

1563 GROUP by s.id

1564 )

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

1566 FROM arts a0

1567 JOIN

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

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

1570 ORDER BY ctime DESC LIMIT 50;

1571 EOF

1572

1573 cat<<-EOF

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

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

1576 <div class="lcto">

1577 `DT_VIEW=replyblog dumptable html blog`

1578 </div>

1579 EOF

1580 unset DT_SQL

1581 # Display NEWS

1582 if false; then

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

1584 new10=`DT_CHLD=article:blogid \

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

1586 else

1587 # 2016-06-26

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

1589 acclog blog $blogreadflagrowid

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

1591 fi

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

1593 sql=`listnewblogsql "$user"`

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

1595 fi

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

1597 cont=$((cont-1))

1598 err newcount=$cont

1599 if [ $cont -gt 0 ]; then

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

1601 cgi_radio foldtabs yes 'id="new10"'

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

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

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

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

1606 `cgi_submit '確定'`

1607 `cgi_hidden read yes`

1608 EOF

1609 echo "$new10 "

1610 echo "</div>"

1611 fi

1612 fi

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

1614 #

1615 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

1621 -D_NBLOG_="$nblog" \

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

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

1624 $layout/html.m4.html $layout/home.m4.html

1625

1626 # Record access log

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

1628 }

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

1630 contenttype; echo

1631 err commission: "$@"

1632 gname=`getgroupbyid $1`

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

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

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

1636 grp_reg_adm "$@"

1637 else

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

1639 fi

1640 }

1641 listgroupbytable() {

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

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

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

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

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

1647 NGsql="select distinct tag from\

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

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

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

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

1652 err ListGRP: query sql="$sql"

1653 for tag in `query "$sql"`

1654 do

1655 err ListGrp: tag=$tag

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

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

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

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

1660 as rowid,

1661 gname,

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

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

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

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

1666 err PersonalGroupList= `echo $sql`

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

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

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

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

1671 echo '</table>'

1672 done

1673 echo '</div>'

1674 }

1675 iconhref() (

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

1677 data=`percenthex $1`

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

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

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

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

1682 )

1683 iconhref2() (

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

1685 src=$1

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

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

1688 )

1689 listentry() (

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

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

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

1693 cond='' hiddens=''

1694 offset=`getpar offset`

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

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

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

1698 limit=30

1699 dir=`getcachedir "$1"`

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

1701 hrb="$myname?home"

1702 deficon=person-default.png

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

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

1705 gcs=gecos

1706 else # if group

1707 hrb="$myname?grp"

1708 deficon=group-default.png

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

1710 gcs=name

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

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

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

1714 err tagconv=$tagconv

1715 fi

1716 fi

1717 if [ ! -d $dir ]; then

1718 mkdir -p $dir

1719 fi

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

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

1722 fi

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

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

1725 fi

1726

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

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

1729 ##qgrp=`sqlquote $grp`

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

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

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

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

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

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

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

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

1738 end

1739 as name,

1740 b.tag,

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

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

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

1744 then '(ADMIN)'

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

1746 then '(Member)'

1747 when '$iamowner' = '' then ''

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

1749 CASE '$entity'

1750 WHEN 'グループ'

1751 THEN coalesce(

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

1753 'open')

1754 ||

1755 CASE WHEN '$user'

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

1757 THEN ' ismember'

1758 ELSE ''

1759 END

1760 ELSE 'user'

1761 END regmode

1762 from $tbl a left join

1763 (select $nm as name,

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

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

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

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

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

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

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

1771 # Note that mtime is stored only in grp_s.

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

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

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

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

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

1777 cgi_form $stage <<EOF

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

1779 `cgi_text kwd $kwd`</label>

1780 EOF

1781 echo '</div>'

1782 fi

1783 hiddens="$hiddens

1784 `cgi_hidden kwd \"$kwd\"`

1785 `cgi_hidden stage \"$stage\"`"

1786 cat<<EOF

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

1788 EOF

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

1790 nextbtn=$(

1791 cat<<EOF

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

1793 `cgi_submit 次の${limit}件`

1794 $hiddens

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

1796 EOF

1797 )

1798 fi

1799 if [ $offset -gt 0 ]; then

1800 prevbtn=$(

1801 cat<<EOF

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

1803 `cgi_submit 前の${limit}件`

1804 $hiddens

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

1806 EOF

1807 )

1808 fi

1809 pnbtn="$nextbtn$prevbtn"

1810 echo $pnbtn

1811

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

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

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

1815 err name=$name owner=$ownerp lnk=$lnk

1816 err newlnk=$lnk regmode=$regmode

1817 icondir=$dir/$id

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

1819 # Pick up only first icon

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

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

1822 | _m4 $tagconv

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

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

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

1826 else

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

1828 fi

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

1830 echo "</div>"

1831 done

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

1833 )

1834 listmember() {

1835 listentry user "$@"

1836 }

1837 listgroup() {

1838 listentry group "$@"

1839 }

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

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

1842 query "SELECT DISTINCT hex(val) FROM grp_mem_m

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

1844 }

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

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

1847 grid=`getpar grid`

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

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

1850 else

1851 grid=$1

1852 fi

1853 grp=`getgroupbyid $grid`

1854 qgrp=`sqlquote $grp`

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

1856 if isgroup "$grp"; then

1857 tf=$tmpd/title.$$

1858 bodyclass=`query "SELECT val FROM grp_s

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

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

1861 ismember="ismember"

1862 qgrp=`sqlquote $grp`

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

1864 else

1865 ismember="" # bodyclass="group"

1866 fi

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

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

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

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

1871 -D_BODYCLASS_="$bodyclass" \

1872 -D_DUMPTABLE_="" \

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

1874 # $htmlheader is defined in grp()

1875 else # if $grp is removed at par2table

1876 listgroup

1877 fi

1878 }

1879 showgroupsub() {

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

1881 # Using $ismember

1882 rowid=$2

1883 grp=`getgroupbyid $2`

1884 qgrp=`sqlquote $grp`

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

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

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

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

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

1890 return

1891 fi

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

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

1894 viewtable $1 grp $rowid

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

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

1897 iamowner=$rowid

1898 fi

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

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

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

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

1903 cat<<EOF

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

1905 <div class="fold">

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

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

1908 <div>

1909 `cgi_textarea message "" "cols=60"`

1910 `cgi_submit 送信`

1911 `cgi_reset リセット`

1912 </div>

1913 `cgi_hidden grp $rowid`

1914 </div></form>

1915 EOF

1916 fi

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

1918 err ismember $user $grp

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

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

1921 else

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

1923 fi

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

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

1926 and key='email';"`

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

1928 and key='email';"

1929 err email=$eml

1930 cat <<EOF

1931 <div class="fold">

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

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

1934 <div>

1935 EOF

1936 cgi_form grp <<EOF

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

1938 <table class="b">

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

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

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

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

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

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

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

1946 </table>

1947 `cgi_hidden grp $rowid`

1948 EOF

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

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

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

1952 fi

1953 echo '</div></div>'

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

1955 cgi_form searchart<<EOF

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

1957 `cgi_hidden owner $grp`

1958 EOF

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

1960 DT_CHLD=article:blogid \

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

1962

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

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

1965 cm="?commission+$rowid"

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

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

1968 # team list

1969 hexteams=`hexteams "$grp"`

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

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

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

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

1974 SELECT val TEAM,

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

1976 MEMBERS

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

1978 EOF

1979 echo '</table></div>'

1980 fi

1981 }

1982 grp_getbodyclass() {

1983 # Get css class name for document.

1984 # `moderated' for moderated groups

1985 # `ismember' for groups where user belongs

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

1987 # $user=userNameCurrentlyLogin

1988 err grp_getbodyclass: 1="$1"

1989 qgrp=`sqlquote "$1"`

1990 query<<-EOF

1991 SELECT coalesce(

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

1993 'open')

1994 ||

1995 CASE WHEN '$user'

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

1997 THEN ' ismember'

1998 ELSE ''

1999 END;

2000 EOF

2001 }

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

2003 err GRP_ACTION:IN

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

2005 grp=`getgroupbyid "$grid"`

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

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

2008 fi

2009 if ! ismember $user $grp; then

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

2011 fi

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

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

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

2015 $layout/html.m4.html

2016

2017 isowner=""

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

2019 usel=`getpar usel`

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

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

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

2023 text=`getpar text`

2024

2025 rm=`getpar rm` cfm=`getpar confirm`

2026 err rm=$rm cfm=$cfm

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

2028 if [ "$isowner" ]; then

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

2030 # Eliminate

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

2032 for tbl in grp_mem grp_mem_s grp_mem_m; do

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

2034 # echo "sql=$sql"

2035 query "$sql"

2036 err rmGRPuser "$sql"

2037 done

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

2039 #err num=$num

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

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

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

2043 # err `echo "$sql"`

2044 html pre<<EOF

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

2046 `query "$sql"`

2047 EOF

2048 fi

2049 else

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

2051 return

2052 fi

2053 else # not Group Owner

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

2055 return

2056 fi

2057 cat<<EOF

2058

2059 EOF

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

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

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

2063 return 0

2064 fi

2065 gecos=`gecos $user`

2066 mkfrom=`getpar mkfrom`

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

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

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

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

2071 mail_from="$safegc <$fromad>"

2072 else

2073 mail_from="$admin"

2074 fi

2075 MAIL_FROM=$mail_from \

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

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

2078 $url

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

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

2081 ----------------------------------------------------------

2082 $text

2083 EOF

2084 if [ $? = 0 ]; then

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

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

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

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

2089 html pre<<EOF

2090 `query "$sql"`

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

2092 EOF

2093 err SendDone: `echo $sql`

2094 fi

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

2096 grp_reg_adm $grid $usel

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

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

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

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

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

2102 team=newteam

2103 fi

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

2105 cat<<-EOF | html p

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

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

2108 EOF

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

2110 else

2111 grp_add_team $grid "$team" $usel

2112 fi

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

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

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

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

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

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

2119 grp_rm_team $grid "$rmteam" $usel

2120 else

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

2122 fi

2123 else

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

2125 fi

2126 fi

2127 fi

2128 # New entry

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

2130 case

2131 when (select user from grp_adm where

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

2133 and user=a.name) is not null

2134 then 'k'

2135 else ''

2136 end || a.rowid||

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

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

2139 FROM article NATURAL JOIN article_s

2140 WHERE blogid IN

2141 (SELECT id FROM blog_s

2142 WHERE key='owner'

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

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

2145 as POST,

2146 (SELECT group_concat(val, ',')

2147 FROM grp_mem_m

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

2149 FROM

2150 gecoses a

2151 WHERE name in (select user from grp_mem where

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

2153 ORDER by a.gecos;"

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

2155 tf=$tmpd/title.$$

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

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

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

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

2160 </p></div>"

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

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

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

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

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

2166 </div>"

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

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

2169 allhexteams=$(hexteams "$grp")

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

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

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

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

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

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

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

2177 </div>

2178 "

2179 fi

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

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

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

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

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

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

2186 cgi_form grpaction<<EOF \

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

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

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

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

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

2192 $layout/form+dump.m4.html

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

2194 <div class="foldtabs">

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

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

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

2198 `cgi_datalist_h teams $allhexteams`

2199 </div>

2200 ${rmteammsg}

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

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

2203 <div>

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

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

2206 `cgi_textarea text "" cols=40`

2207 </div>

2208 ${isowner:+$cmmsg$excmsg}

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

2210 </div>

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

2212 <table class="td2r">

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

2214 </table>

2215 `cgi_hidden grp $grid`

2216 EOF

2217 }

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

2219 # Create TEMPORARY VIEW

2220 query<<EOF

2221 CREATE TEMPORARY VIEW writeusers AS

2222 SELECT DISTINCT author FROM article

2223 WHERE id in (

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

2225 );

2226 CREATE TEMPORARY VIEW movablegroups AS

2227 SELECT g.rowid growid , g.gname

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

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

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

2231 WHERE (SELECT author FROM writeusers

2232 EXCEPT

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

2234 IS NULL;

2235 $2

2236 EOF

2237 }

2238 sql4readableblogs() {

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

2240 # Blog is readable when:

2241 # 1: blog owner is an user

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

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

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

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

2246 CREATE TEMPORARY VIEW readableblogs AS

2247 SELECT blog.rowid rid, id, author

2248 FROM blog

2249 NATURAL JOIN

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

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

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

2253 WHEN (SELECT val FROM grp_s

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

2255 AND

2256 (SELECT user FROM grp_mem

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

2258 THEN 0

2259 ELSE 1

2260 END;

2261 EOF

2262 }

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

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

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

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

2267 return

2268 fi

2269 owner=`getvalbyid blog owner $rowid`

2270 title=`getvalbyid blog title $rowid`

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

2272 | _m4 -D_TITLE_="修正" \

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

2274 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

2277 # Move to group

2278 if isuser "$owner"; then

2279 crview4article $rowid

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

2281 err N=$n

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

2283 err ROWID=$rowid

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

2285 cat<<-EOF

2286 <div class="fold">

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

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

2289 <div>

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

2291 移動先グループ:

2292 <select name="mv2grp">

2293 EOF

2294 query ".mode html"

2295 query<<-EOF |

2296 $sql

2297 .mode list

2298 EOF

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

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

2301 cat<<-EOF

2302 </select>

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

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

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

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

2307 `cgi_hidden blogrowid $rowid`

2308 `cgi_submit 移動`

2309 `cgi_reset Reset`

2310 </form>

2311 </div>

2312 </div>

2313 EOF

2314 fi

2315 # end of isuser "$owner"

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

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

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

2319 cat<<-EOF

2320 <div class="fold">

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

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

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

2324 `query "SELECT

2325 coalesce((SELECT val FROM blog_s

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

2327 AND key='team'),

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

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

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

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

2332 `cgi_hidden blogrowid $rowid`<br>

2333 `cgi_submit 移動`

2334 `cgi_reset Reset`

2335 </form></div></div>

2336 EOF

2337 fi

2338 }

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

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

2341 blogrowid=`getpar blogrowid`

2342 cfm=`getpar cfm`

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

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

2345 . ./s4-blog.sh

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

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

2348 return

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

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

2351 elif { mv2grp=`getpar mv2grp`

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

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

2354 crview4article $blogrowid

2355 ########## TRANSACTION BEGIN

2356 query "BEGIN;"

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

2358 err Nwriteuser=$n

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

2360 query<<-EOF

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

2362 WHERE key='owner'

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

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

2365 EOF

2366 fi

2367 query "END;"

2368 ########## TRANSACTION END

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

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

2371 # blog owner can move it to ANY team

2372 case "$mv2team" in

2373 'なし')

2374 cat<<-EOF

2375 DELETE FROM blog_s

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

2377 AND key='team';

2378 EOF

2379 ;;

2380 "") ;;

2381 *)cat<<-EOF

2382 BEGIN;

2383 REPLACE INTO blog_s(id, key, val)

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

2385 'team', '$mv2team');

2386 REPLACE INTO blog_s(id, key, val)

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

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

2389 END;

2390 EOF

2391 esac | query

2392 fi

2393 blog_reply $blogrowid

2394 echo yes | html p

2395 }

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

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

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

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

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

2401 return

2402 fi

2403 owner=`getvalbyid blog owner $blogrowid`

2404 title=`getvalbyid blog title $blogrowid`

2405 author=`getvalbyid article author $rowid`

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

2407 if isgrpowner $user $owner; then

2408 : EDIT OK

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

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

2411 return

2412 fi

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

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

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

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

2417 > $tmpout

2418 rm -f /tmp/editart.out

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

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

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

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

2423 -D_BLOGS_= -D_DUMPTABLE_= \

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

2425 }

2426 send2mem() {

2427 rowid=`getpar grp`

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

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

2430 return

2431 fi

2432 message=`getpar message`

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

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

2435 return

2436 fi

2437 grp=`getgroupbyid $rowid`

2438 members=`collectemail $grp`

2439 # smail rcpt subj (file)

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

2441 $urlbase?grp+$rowid

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

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

2444

2445 $message

2446 EOF

2447 cat<<EOF

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

2449 <pre>

2450 $members

2451 </pre>

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

2453 EOF

2454 }

2455 joingrpadmit() {

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

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

2458 echo "bye bye" | html p; return

2459 fi

2460 t_usr=`session=$2 getpar user`

2461 t_grp=`session=$2 getpar group`

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

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

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

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

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

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

2468 return

2469 fi

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

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

2472 fi

2473 case $1 in

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

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

2476 *)

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

2478 return ;;

2479 esac

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

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

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

2483 body="グループ $t_grp

2484 に

2485 $t_usr

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

2487 しました。"

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

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

2490 echo "$body" | html p

2491 }

2492

2493 joingrprequest() {

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

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

2496 addsession $jss +${memoplimitdays}days

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

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

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

2500 $url

2501 $user さんから

2502 グループ $1

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

2504

2505 承認する:

2506 $urlbase?joingrpadmit+yes+$jss

2507

2508 白紙に戻す:

2509 $urlbase?joingrpadmit+no+$jss

2510 EOF

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

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

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

2514 }

2515 joingrp() {

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

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

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

2519 isowner="yes"

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

2521 isowner="yes"

2522 else

2523 isowner=""

2524 fi

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

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

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

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

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

2530 return

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

2532 : # Do not pursue those who leave

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

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

2535 else # adding user is $user itself

2536 case `getgroupattr $1 regmode` in

2537 moderated)

2538 joingrprequest "$@" # Request only

2539 return

2540 ;;

2541 *)

2542 ;;

2543 esac

2544 fi

2545 qgname=`sqlquote $1`

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

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

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

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

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

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

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

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

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

2555 else

2556 echo $msg

2557 fi

2558 else

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

2560 fi

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

2562 # Coming here means newly created group

2563 sql="select case\

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

2565 then (select user from grp_mem\

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

2567 else '' end; "

2568 err NewGrpChk: $sql

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

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

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

2572 fi

2573 fi

2574 else

2575 query "delete from grp_mem $cond;

2576 delete from grp_mem_s $cond;

2577 delete from grp_mem_m $cond;"

2578 fi

2579 }

2580 grp_add_team() (

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

2582 grp=`getgroupbyid $1`

2583 team=$2; shift; shift

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

2585 { echo "BEGIN;"

2586 for user; do

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

2588 '$grp',\

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

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

2591 done

2592 echo "END;"

2593 } | query

2594 )

2595 grp_rm_team() (

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

2597 grid=$1

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

2599 team=$2; shift; shift

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

2601 { echo "BEGIN;"

2602 for user; do

2603 echo "DELETE FROM grp_mem_m\

2604 WHERE gname=$qgrp \

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

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

2607 done

2608 cat<<-EOF

2609 DELETE FROM blog_s

2610 WHERE rowid=(

2611 SELECT rowid

2612 FROM blog_s a

2613 WHERE key='team'

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

2615 AND NOT EXISTS (SELECT * FROM grp_mem_m

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

2617 AND gname = (SELECT val FROM blog_s b

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

2619 ));

2620 EOF

2621

2622 echo "END;"

2623 } | query

2624 )

2625 grp_reg_adm() {

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

2627 grid=$1

2628 grp=`getgroupbyid "$1"`

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

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

2631 fi

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

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

2634 fi

2635 shift

2636 for urid; do

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

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

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

2640 fi

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

2642 err ismember $newadm $grp

2643 if ismember $newadm $grp; then

2644 # OK, go ahead

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

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

2647 # confirm insertion

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

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

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

2651 else

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

2653 fi

2654 fi

2655 showgroup $grid

2656 done

2657 }

2658 dumptable() {

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

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

2661 # $DT_VIEW sets link

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

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

2664 VIEW=${DT_VIEW-replyblog}

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

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

2667 fi

2668 # $DT_CHLD=ChildTable:BindColumn

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

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

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

2672 # XXX: Dirty workaround for slow subquery of acclog

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

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

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

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

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

2678 tblrowid=a.rowid),\

2679 '1970-01-01'))"

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

2681 dt_class=" td2r td3r dumpblogs"

2682 fi

2683 # Construct join expression

2684 eav="" scols=""

2685 pk=`gettblpkey $2`

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

2687 substr=${substr:-val}

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

2689 case $col in

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

2691 continue ;; # built-in column name

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

2693 *) as=${col} ;;

2694 esac

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

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

2697 done

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

2699 sql=${DT_SQL:-"select \

2700 a.rowid as LINK,\

2701 $cnt\

2702 $scols from $2 a left join\

2703 (select $pk,$eav,

2704 max(case key when 'owner'

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

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

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

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

2709 <div>

2710 <div class="dumptable">

2711 <table class="b$dt_class">

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

2713 </table>

2714 </div>

2715 </div>

2716 EOF

2717 }

2718

2719 par2table() (

2720 # copy current parameters of par into destination table

2721 # $1=definition-file

2722 # Using $user and $session

2723 # Return value:

2724 # 0: Stored successfully

2725 # 1: Insufficient fillings

2726 # 2: No permission to modify the record

2727 # 3: Invalid rowid

2728 # 4: SUCCESS to delete

2729 # 5: Stop deletion for lack of confirm check

2730 # 6: Password length too short

2731 # 7: Password mismatch

2732 # 8: Old password incorrect

2733 rowid=`getpar rowid`

2734 err ...........rowid=$rowid

2735 if [ ! -e $1 ]; then

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

2737 exit 1

2738 fi

2739 tbl=${1%.def}

2740 tbl=${tbl##*/}

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

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

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

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

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

2746 ##err p2t:grp:q $sql

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

2748 else

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

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

2751 where rowid=$rowid;"`}

2752 fi

2753 ### err rowowner=$rowowner

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

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

2756 return 2

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

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

2759 return 3

2760 fi

2761 rm=`getpar rm` cfm=`getpar confirm`

2762 # Editing existent entry

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

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

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

2766 return 4

2767 else

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

2769 return 5

2770 fi

2771 fi

2772 fi

2773

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

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

2776 # Update of existing record

2777 for col in `gettblcols $tbl`; do

2778 val=`getparquote $col`

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

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

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

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

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

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

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

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

2787 ## of blog_s table.

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

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

2790 cat<<-EOF | query

2791 BEGIN;

2792 $sql

2793 update blog_s set val=$val

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

2795 COMMIT;

2796 EOF

2797 ## XX: DIRTY Hack Ends here

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

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

2800 aa

2801 else

2802 query "$sql"

2803 fi

2804 done

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

2806 for col in `gettblpkey $tbl`; do

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

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

2809 done

2810 else

2811 # New entry

2812 # Generate values() for primary keys

2813 for col in `gettblpkey $tbl`; do

2814 # Genuine primary keys for _m and _s

2815 val=`getvalquote $tbl $col`

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

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

2818 done

2819 err pval=$pval

2820 for col in `gettblfkey $tbl`; do

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

2822 val=`getvalquote $tbl $col`

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

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

2825 done

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

2827 err formaster=$formaster

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

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

2830 return 1

2831 fi

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

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

2834 ## Insertion to master table, done

2835 fi

2836

2837 for kt in s m; do

2838 tb2=${tbl}_$kt

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

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

2841

2842 # First, check update of existing entries in _m

2843 if [ $kt = m ]; then

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

2845 sql=""

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

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

2848 # v=address.1.22

2849 st_rowid=${v##*.}

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

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

2852 ##case `getpartype $v` in

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

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

2855 case `getpar action.$v` in

2856 rm)

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

2858 newsql="delete from $tb2"

2859 else

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

2861 fi ;;

2862 edit)

2863 case `gettbl_coltype $tbl/$origcol` in

2864 image|document|binary)

2865 file=$tmpd/`getparfilename $v`

2866 err type=file=$file

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

2868 bn=${file##*/}

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

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

2871 type=\"file:$ct\"

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

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

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

2875 rm -r $cachedir

2876 ;;

2877 *)

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

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

2880 ;;

2881 esac

2882 ;;

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

2884 continue

2885 ;;

2886 esac

2887 # err newsql=$newsql

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

2889 done

2890

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

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

2893 query "$sql

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

2895 err repl:normal done

2896 else

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

2898 echo "$sql" > $sqlfile

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

2900 query ".read $sqlfile"

2901 err repl:done

2902 fi

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

2904 nr=`getparcount $col`

2905 else

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

2907 fi

2908

2909 i=0

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

2911 limit="limit 1 offset $i"

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

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

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

2915 err $col=$val

2916 bin=NULL

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

2918 case $ptype in

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

2920 err parfile-$col=$file

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

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

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

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

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

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

2927 esac

2928 case `gettbl_coltype $tbl/$col` in

2929 password) # special care for password

2930 # name={password,pswd1,pswd2}

2931 p1=`getpar pswd1 "$limit"`

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

2933 continue # SKIP password setting, if p1 is empty

2934 else

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

2936 ## err pswd=$pswd

2937 if pwcheck "$pswd"; then

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

2939 case "$p1" in

2940 ??????????*) ;;

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

2942 return 6;;

2943 esac

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

2945 else

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

2947 return 7

2948 fi

2949 else

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

2951 return 8

2952 fi

2953 fi

2954 ;;

2955 esac

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

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

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

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

2960 err Normal-query: `echo $sql`

2961 query "$sql"

2962 else

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

2964 echo "$sql" > $sqlfile

2965 err sqlfile=`ls -lF $sqlfile`

2966 query ".read $sqlfile"

2967 fi

2968 err p2t done

2969 done

2970 done

2971 done

2972 return 0

2973 ##err donee

2974 )

2975 genform() {

2976 # $1 = form definition file

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

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

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

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

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

2982 forms="" hiddens="" rowid=$3

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

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

2985 return

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

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

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

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

2990 pk=`gettblpkey $2`

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

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

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

2994 err rec-rowid=$rec

2995 rowid=$rec

2996 rec=$3

2997 fi

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

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

3000 return

3001 fi

3002 fi

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

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

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

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

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

3008 fi

3009 # Image Cache dir

3010 err genform: getcache=$2/$rowid

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

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

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

3014 sp="${args:+ }"

3015 form="" val=""

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

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

3018 val=`getvalbyid $2 $name $rowid $td|htmlescape`

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

3020 err genform3b: val="[$val]"

3021 fi

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

3023 is_hidden "$2" "$name" && continue

3024 fi

3025 case "$type" in

3026 text*)

3027 cgiform=cgi_multi_$type

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

3029 form=`$cgiform $name $td`

3030 val=$(echo "$val"|

3031 while read fn; do

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

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

3034 done)

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

3036 else

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

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

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

3040 fi

3041 ;;

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

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

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

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

3046 ;;

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

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

3049 ;;

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

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

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

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

3054 done)

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

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

3057 fi

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

3059 ;;

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

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

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

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

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

3065 val=$(echo "$val" \

3066 | while read fn; do

3067 data=`percenthex $td/$fn`

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

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

3070 ri=`cat $td/$fn.rowid`

3071 ## err fn=$fn, name=$name, ri=$ri; ls -lF $td 1>&3

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

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

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

3075 done)

3076 fi

3077 else

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

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

3080 imgs=$(echo "$val"\

3081 |while read fn;do

3082 data=`percenthex $td/$fn`

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

3084 done)

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

3086 val=$imgs # 2015-06-15

3087 else

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

3089 fi

3090 fi

3091 ;;

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

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

3094 args="value=\"$GF_STAGE\""

3095 fi

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

3097 prompt='' # Remove prompt

3098 ;;

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

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

3101 prompt="" ;;

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

3103 val=${GF_OWNER:-$val}

3104 val=${val:-$user}

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

3106 prompt="" ;;

3107 [Uu][Ss][Ee][Rr])

3108 # XXX: is null $user ok?

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

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

3111 form="$user"

3112 ;;

3113 [Pp]assword)

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

3115 form="`cgi_passwd`"

3116 val=""

3117 ;;

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

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

3120 val=`genserial`

3121 fi

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

3123 prompt="" ;;

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

3125 prompt=""

3126 ;;

3127 parent|path|blog*)

3128 prompt=""

3129 ;;

3130 "*"*)

3131 tail=$tail"``"

3132 continue ;;

3133 esac

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

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

3136 form=$val

3137 else

3138 :

3139 fi

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

3141 else

3142 hiddens=$hiddens$nl"$form"

3143 fi

3144 done < $1

3145 # enctype="multipart/form-data"

3146 cat<<EOF

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

3148 ${rowid:+$rm}

3149 <table class="b $2">

3150 $forms

3151 </table>$hiddens

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

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

3154 EOF

3155 if [ -z $GF_VIEWONLY ]; then

3156 cat<<EOF

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

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

3159 EOF

3160 fi

3161 cat<<EOF

3162 $GF_ARGS</form>

3163 $tail

3164 EOF

3165 }

3166 edittable() {

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

3168 genform "$@"

3169 }

3170 viewtable() {

3171 GF_VIEWONLY=1 genform "$@"

3172 }

3173 showattc() {

3174 # $1=table_m $2=rowid

3175 err \$1=$1 \$2=$2

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

3177 contenttype; echo

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

3179 putfooter; exit

3180 fi

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

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

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

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

3185 err showattc: sql: $sql

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

3187 tv=`query "select type,val from $1 where rowid='$2';"`

3188 type=${tv%\|*} fn=${tv#*\|}

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

3190 ct=${type#file:}

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

3192 text/*)

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

3194 case $charset in

3195 ASCII*) charset="" ;;

3196 esac

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

3198 ;;

3199 esac

3200 contenttype "$ct"

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

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

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

3204 cat $bin

3205 }

3206 #

3207 # Some default stupid handler on CGI values

3208 #

3209 default_storedb() {

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

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

3212 tbl=`basename $1`

3213 tbl=${tbl%.def}

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

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

3216 vcol=`getpar $col`

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

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

3219 par2table $1

3220 else

3221 return 2 # No insertion occurred

3222 fi

3223 }

3224

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

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

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

3228 default_storedb "$@"

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

3230 | while read rowid; do

3231 viewtable $1 $tbl $rowid

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

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

3234 -D_DUMPTABLE_="syscmd(cat)" \

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

3236 }

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

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

3239 default_storedb "$@"

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

3241 | _m4 -D_TITLE_="$tbl" \

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

3243 -D_DUMPTABLE_="syscmd(cat)" \

3244 $layout/html.m4.html $layout/form+dump.m4.html

3245 }

3246 default_smail() {

3247 default_storedb "$@"

3248 if [ $? -eq 2 ]; then

3249 _m4 -D_TITLE_="入力" \

3250 -D_FORM_="`genform $1`" \

3251 -D_DUMPTABLE_="" \

3252 $layout/html.m4.html $layout/form+dump.m4.html

3253 return

3254 fi

3255 cond=""

3256 for pk in `gettblpkey $tbl`; do

3257 pv=$(sqlquote $(getpar $pk))

3258 cond="$cond${cond:+ and }$pk=$pv"

3259 done

3260 sql="select rowid from $tbl where $cond;"

3261 rowid=`query "$sql"`

3262 err smail1 - "$sql" "-> rowid=$rowid"

3263

3264 while IFS=: read prompt name keytype type args; do # Read from $1

3265 val=`getpar $name`

3266 if [ -n "$val" ]; then

3267 text="$text

3268 $prompt

3269 $name=$val

3270 ---------------------------------------------------------"

3271 fi

3272 case "$type" in

3273 image|document|file)

3274 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3275 fns=$(echo "$fn"|while read fn; do

3276 err mv $tmpd/$fn.orig $tmpd/$fn

3277 mv $tmpd/$fn.orig $tmpd/$fn

3278 rm $tmpd/$fn.rowid # Remove cache flag

3279 err "`ls $tmpd/$fn`"

3280 echo $fn

3281 done)

3282 files="$files $fns"

3283 ;;

3284 esac

3285 done < $1

3286 err FILES=$files "`ls -lF $tmpd`"

3287 subj="from ${REMOTE_ADDR}"

3288 (echo "$url"

3289 echo "への書き込みがありました。"

3290 echo "------"

3291 echo "$text"

3292 ) | (cd $tmpd &&

3293 err LS="`ls -lF`" &&

3294 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3295 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3296 echo "以下の内容で送信しました。" | html p

3297 viewtable $1 $tbl \

3298 `query "select rowid from $tbl order by rowid desc limit 1;"`

3299 echo "戻る" | html a "href=\"?\""

3300 }