s4: 70fa878fe3ea s4-funcs.sh

s4

view s4-funcs.sh @ 209:70fa878fe3ea

"accept" attribute modified

author	HIROSE Yuuji <yuuji@gentei.org>
date	Wed, 27 Apr 2016 08:27:33 +0859
parents	36b6354de5cb
children	e08a4a4d3f1d

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=20

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-*"'

32 tconfs=""

33 imgcached=imgcache.`date +%Y/%m`

34 conftbl=_tblconf

35 nl="

36 "

37 . ./s4-cgi.sh

39 : <<EOF

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

42 レポート提出管理

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

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

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

47 Web

48 締切設定

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

51 ○自分の提出物リスト

53 7/19 ○設置

54 ○一斉送信

55 ○getparfilename の tmpd の扱い

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

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

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

59 ○添付ファイル回収

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

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

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

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

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

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

67 ○レポート提出モード

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

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

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

73 7/9 ○管理者の追加

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

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

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

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

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

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

82 ○グループ検索

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

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

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

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

91 ○6/1 par2tableを triplex 対応に

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

93 →とすると一気に

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

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

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

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

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

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

102 ## 1. passwd

103 ## 2. grp

104 ## 3. grp_mem

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

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

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

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

109

110 ## ● listの定義:

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

112 ## ● hashの定義:

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

114

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

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

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

118 ## csv2sq3 で .csv.sq3 の Makefile

119

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

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

122 ## type := root | comment

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

124 ## unique(id, filename)

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

126

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

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

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

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

131 ## できたー!

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

133 ↓

134 ↓以下に変更

135 with recursive allmem as

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

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

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

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

140

141

142 with recursive allmem as

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

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

145 where allmem.val=grp_m.gname)

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

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

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

149

150

151 ## triggerもできた。

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

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

154

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

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

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

158 ## グループ管理

159 ## 個人情報管理

160 ##

161

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

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

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

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

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

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

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

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

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

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

172

173 ## ログテーブル

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

175

176

177

178 ■表設計

179 * 3つの表に分散管理

180 id格納表 + hash表 + list表

181 * *_s *_m

182

183

184

185 user, user_map, user_col

186

187 ■抽象エントリタイプ

188 * user

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

190 * group

191 権限判定に利用

192 * serial

193 自動idとして機能

194 * password

195 入力 type=passwordで入力

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

197 * session

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

199 * text

200 入力 type=text

201 * textarea

202 入力 textarea

203 * image|document

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

205 * owner

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

207 * gowner

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

209 * timestamp

210 datetime()

211 * parent

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

213 * path

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

215

216 格納タイプ

217 * list

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

219 * hash

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

221

222 オブジェクトタイプ

223 * entry

224 id, title, owner

225 * textpart

226 id, parentID, text

227 * binarypart

228 id, parentID, contenttype, filename, content

229 * content

230 hash(textpart), list(binarypart)

231 * topic

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

233 * reply

234 id, parentID, content

235 * blog

236 list(entry)

237 blog = [topic, list(reply)]

238

239

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

241 "text" => "hogehoge ..",

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

243 "author" => "taro",

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

245 "parent" => "/",

246 "path" => "/1",

247 "text" => "blah, blah, ....",

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

249 {"serial" => 2,

250 "author" => "hanako",

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

252 "parent" => "/",

253 "path" => "/2",

254 "text" => "blah, blah, ....",

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

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

257

258

259 user:=

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

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

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

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

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

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

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

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

268

269 変換表

270 /user/email=m

271

272 blog:=

273 シリアル:id:p:serial

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

275 所有者:owner:s:owner:

276 時刻:ctime:s:stamp:

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

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

279

280 article:=

281 シリアル:id:p:serial

282 筆者:author:s:owner

283 時刻:ctime:s:stamp:

284 参照元:parent:s:parent:

285 パス:path:s:path:

286 本文:text:s:textarea:

287 画像:image:m:image:

288

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

290

291

292 EOF

293

294 sq() {

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

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

297 }

298 dbsetup() {

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

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

301 sqi=$tmpdir/sqi.$$

302 sqo=$tmpdir/sqo.$$

303 mkfifo $sqi $sqo

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

305 sq $db < $sqi &

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

307 exec 2>> $tmpdir/error.out

308 exec 3>> $tmpdir/debug.out

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

310 rm $sqi

311 }

312 cleanup() {

313 echo .quit >&5

314 kill $sq3pid

315 kill $sq3pid

316 rm -f $sqo $sqi

317 rm -rf $tmpfiles

318 }

319 query() {

320 echo ".once $sqo" >&5

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

322 cat

323 else

324 echo "$@"

325 fi >&5

326 cat $sqo

327 }

328 _m4() {

329 #_S4NAME_=f,f,f

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

331 }

332 ismember() {

333 # $1=user, $2=group

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

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

336 }

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

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

339 }

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

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

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

343 }

344 isgrpowner() (

345 # $1=user, $2=group

346 gn=`sqlquote "$2"`

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

348 err isgrpowner: $sql

349 test -n "`query $sql`"

350 )

351 getgroupadminmails() {

352 # $1=group

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

354 email4group "$1" "$i" ;

355 done

356 }

357 getgroupadmins() { # $1=group

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

359 qgrp=`sqlquote "$1"`

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

361 }

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

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

364 getvalbyid grp $2 \

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

366 }

367 getgroupbyid() {

368 # $1=id|gname

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

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

371 # err ggbyid: `echo $sql`

372 query $sql

373 }

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

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

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

377

378 # Return true when anonymous mode

379 [ "$anonymousmode" ] && return 0

380 # case `getvalbyid blog mode $2` in

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

382 # *closed*)

383 # owner=`getvalbyid blog owner $2`

384 # if isgrp $owner; then

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

386 # elif isuser $owner; then

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

388 # fi

389 # esac

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

391 sql="with getblog as (\

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

393 select blogid from article where id in\

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

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

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

397 select case\

398 when (select author from article where\

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

400 then 'author'\

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

402 then 'open'\

403 when (select val from getmode) is null \

404 then 'open'

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

406 then (select user from grp_adm where \

407 gname=(select val from getowner) and \

408 user='$1')\

409 when (select author from article where\

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

411 then 'user+author'

412 else '' end;"

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

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

415 result=`query "$sql"`

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

417 return 2

418 }

419 linkhome() {

420 # $1=UserOrGroup

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

422 if isuser $1; then

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

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

425 else

426 echo -n "grp+$1"

427 fi

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

429 }

430 hreflink() {

431 sed -e "s|^href=$[-A-Za-z0-9,.:/~_%#&+?]*$|<a &>\1</a>|"

432 }

433 acclog() (

434 # $1=table, $2=rowid

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

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

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

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

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

440 fi

441 )

442 gecos() (

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

444 #gecos=`query "select val from user_s where name='$u' and key='gecos';"`

445 # sql="select case when (select name from user where name=$u) is not null

446 # then coalesce(

447 # (select val from user_s where name=$u and key='gecos'),

448 # $u)

449 # when (select gname from grp where gname=$u) is not null

450 # then coalesce(

451 # (select val from grp_s where gname=$u and key='gecos'),

452 # $u)

453 # else $u

454 # end;"

455 #query "$sql"

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

457 )

458 setpar() {

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

460 }

461 replpar() {

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

463 }

464 getpar() {

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

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

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

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

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

470 fi

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

472 case "$var" in

473 owner)

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

475 echo $user; return

476 elif ismember $user $val; then

477 echo $val; return

478 fi ;;

479 esac

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

481 echo "$val"

482 }

483

484 getpartype() {

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

486 }

487 getparcount() {

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

489 }

490 getparfilename() {

491 # null if type of $1 is not file

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

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

494 }

495 sqlquote() {

496 (v="$1"

497 case "$v" in

498 "") return ;; # null

499 "X'"*) # quoted hex string

500 echo $1 ;;

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

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

503 echo "\"$v\""

504 return ;;

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

506 echo "\"$v\""

507 return ;;

508 *)

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

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

511 else

512 echo "\"$v\""

513 fi ;;

514 esac)

515 }

516 mktempd() {

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

518 }

519 getcachedir() { # $1=maintable

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

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

522 else

523 echo $tmpd/$thumbxy

524 fi

525 }

526 getval() {

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

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

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

530 echo "$user" ;;

531 stamp|datetime)

532 date "+%F %T" ;;

533 serial)

534 (s=`getpar $2`

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

536 *)

537 getpar "$2" "$3";;

538 esac

539 }

540

541 getvalquote() {

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

543 (v=`getval "$@"`

544 case "$v" in

545 "") echo NULL ;;

546 *) sqlquote "$v" ;;

547 esac)

548 }

549 getparquote() {

550 sqlquote `getpar $1`

551 }

552 getbinbyid() {

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

554

555 }

556 getvalbyid() {

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

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

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

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

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

562

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

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

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

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

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

568 return

569 fi

570 done

571 pk=`gettblpkey $1`

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

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

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

575 for kt in s m; do

576 td=${4:-$tmpd}

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

578 t=${1}_$kt

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

580 vcount=1 # count(val)

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

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

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

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

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

586 if [ $kt = m ]; then

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

588 # Reset val to store filenames if type is string

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

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

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

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

593 i=0

594 err gvbid: i=$i vcount=$vcount

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

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

597 i=$((i+1))

598 fn=$c.$i

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

600 case $type in

601 file:*)

602 #file=$td/$val

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

604 # FOR SPEED: Skip file generation if imgcache exists

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

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

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

608 .output $td/$fn.rowid

609 select rowid from $cond $slice;

610 .output $td/$fn

611 select val from $cond $slice;

612 .output $file.content-type

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

614 .output stdout

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

616 EOF

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

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

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

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

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

622 case $type in

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

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

625 *|*:[Aa]pplication*)

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

627 png:- > $file

628 ;;

629 esac

630 ;;

631 *)

632 sq $db<<EOF

633 .output $td/$fn.rowid

634 select rowid from $cond $slice;

635 .output $td/$fn

636 select val from $cond $slice;

637 EOF

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

639 ;;

640 esac

641 done

642 else

643 rm -f $td/$c.count

644 case $type in

645 file:*)

646 echo "$val" \

647 | while read fn; do

648 file=$td/$fn

649 if [ ! -s $file ]; then

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

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

652 | unhexize > $file

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

654 # err TTTTTTTTTTTTTTTT: $type

655 case $type in

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

657 *:[Aa]pplication*)

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

659 png:- > $file ;;

660 esac

661 fi

662 done

663 ;;

664 esac

665 fi

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

667 return

668 fi

669 done

670 done)

671 }

672 getvalbypkey() (

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

674 pk=`gettblpkey $1`

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

676 getvalbyid "$1" "$2" $rowid $4

677 )

678 getvalbycond() {

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

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

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

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

683 getvalbyid "$1" "$2" $rowid "$4"

684 fi

685 }

686 getpwfield() {

687 # getpwfield user column

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

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

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

691 echo "$val"

692 return 0

693 else

694 return 1

695 fi

696 }

697 encode() {

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

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

700 sha1=sha1

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

702 sha1=sha1sum

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

704 sha1=gsha1sum

705 fi

706 fi

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

708 }

709 mycrypt() (

710 key=$1 salt=$2

711 err \$2=$2

712 case $2 in

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

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

715 esac

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

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

718 )

719 hexize() {

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

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

722 hexize="xxd -p"

723 else

724 hexize_hd() {

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

726 }

727 hexize="hexize_hd"

728 fi

729 fi

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

731 }

732 unhexize() {

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

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

735 unhex="xxd -p -r"

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

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

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

739 EOF

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

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

742 # world writable...:)

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

744 fi

745 fi

746 cat $1 | $unhex

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

748 }

749 percenthex() {

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

751 }

752 htmlescape() {

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

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

755 }

756 enascii() {

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

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

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

760 else

761 enascii_now=`date +%FT%T`

762 enascii_sed() {

763 nkf -Z0Z1Z2 \

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

765 }

766 enascii="enascii_sed"

767 fi

768 fi

769 cat "$@" | $enascii

770 }

771 size_h() {

772 i="$1" oi=$1

773 set -- B B KB MB GB TB

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

775 oi=$i

776 i=$((i/1024))

777 shift

778 done

779 echo ${oi}$1

780 }

781 gettblconf() {

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

783 ## tconfs=`sq $db \

784 tconfs=`query \

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

786 fi

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

788 }

789 gettblkeys() {

790 # $1=tbl

791 gettblconf

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

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

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

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

796 case $type in

797 *p*)

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

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

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

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

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

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

804 esac

805 case $type in

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

807 esac

808 done

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

810 }

811 gettblpkey() {

812 # $1=tbl

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

814 }

815 gettblfkey() {

816 (x=`gettblkeys $1`

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

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

819 }

820 gettblcols() {

821 (x=`gettblkeys $1`

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

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

824 }

825 gettbl_s_cols() {

826 (x=`gettblkeys $1`

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

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

829 }

830 gettbl_m_cols() {

831 (x=`gettblkeys $1`

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

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

834 }

835 gettbl_h_cols() {

836 (x=`gettblkeys $1`

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

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

839 }

840 gettbl_coltype() (

841 gettblconf

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

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

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

845 )

846 is_hidden() {

847 # $1=Tbl $2=col

848 gettblconf

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

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

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

852 case $x in

853 *h*) return 0 ;;

854 *) return 1 ;;

855 esac

856 }

857

858 dbsetbyid() {

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

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

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

862 unset primary update

863 gettblconf

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

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

866 #err conf=$conf

867 case ${conf#*=} in

868 p*) primary=1 ;;

869 f*) update=1 ;;

870 u*) ;;

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

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

873 esac

874 #err t=$t

875 type=text fn=""

876 case $conf in

877 */password)

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

879 ;;

880 */image*|*/document*)

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

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

883 ;;

884 esac

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

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

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

888 if [ "$primary" ]; then

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

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

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

892 elif [ "$update" ]; then

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

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

895 else

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

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

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

899 fi

900 )

901 }

902 expire() (

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

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

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

906 )

907 addsession() {

908 # expireをセット

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

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

911 err addsession "$1" exp=$expire

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

913 # Remove old session parameters

914 now=`expire now`

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

916 }

917 gencookie() (

918 for kv; do

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

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

921 done

922 )

923 contenttype() {

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

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

926 }

927 putheader() {

928

929 }

930 putfooter() {

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

932 }

933 getcookie() (

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

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

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

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

938 done

939 )

940 genrandom() {

941 # $1=columns (default: 10)

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

943 }

944 smail() {

945 # smail rcpts subj (file)

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

947 rcpt=`echo $1` # strip newlines

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

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

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

951 }

952 setviastring() {

953 table=$1

954 oifs="$IFS"

955 IFS="&"

956 for us in $2; do

957 k=${us%%=*}

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

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

960 #echo $k=$v

961 done

962 IFS="$oifs"

963 }

964 checkdomain() (

965 # Check the validity of domain by referring DNS

966 item=$1

967 err checkdomain $1

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

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

970 )

971 pwcheck() {

972 # $1=passwd

973 dbpswd=`getpwfield $user pswd`

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

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

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

977 }

978 mypwhash() {

979 mycrypt `cat` `genrandom 5`

980 }

981 wasureta() {

982 user=$1

983 if ! checkdomain $user; then

984 contenttype; echo

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

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

987 putfooter

988 exit 0

989 fi

990 newpswd=`genrandom` # newsalt=`genrandom 5`

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

992 encpswd=`echo $newpswd|mypwhash`

993 dbsetbyid user $user pswd "$encpswd"

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

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

996 $msgdir/mail-newaccount.m4 \

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

998 | smail $user "New Account"

999 }

1000 checkauth() {

1001 user=`getpar user`

1002 skc=`getpar skey` # from cookie

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

1004 skey="`getpwfield $user skey`"

1005 err user=$user skey=$skey

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

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

1008 return 0

1009 fi

1010 fi

1011 pswd=`getpar pswd`

1012 if [ x"$pswd" = x"wasureta" ]; then

1013 wasureta $user

1014 return 1 # wasureta error

1015 fi

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

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

1018 if pwcheck "$pswd"; then

1019 newsession=`genrandom 50`

1020 dbsetbyid user $user skey "$newsession"

1021 gencookie "user=$user" "skey=$newsession"

1022 return 0

1023 fi

1024 return 2 # Password mismatch

1025 }

1026 showlogin() {

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

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

1029 $layout/login.m4.html

1030 exit 0

1031 }

1032 dologin() {

1033 checkauth

1034 st=$?

1035 if [ $st != 0 ]; then

1036 contenttype; echo

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

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

1039 showlogin # and EXIT

1040 fi

1041 }

1042

1043 # Do instant jobs here

1044 dbsetup

1045 trap cleanup INT HUP EXIT TERM PIPE

1046 # trap cleanup INT HUP

1047

1048 err() {

1049 echo "$@" 1>&3

1050 }

1051

1052 cgiinit() {

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

1054 tmpf=tmp/stream

1055 tmpd=`tmpd=$tmpdir mktempd`

1056 tmpfiles=$tmpfiles" $tmpd"

1057 addsession $session

1058 getcookie

1059 case "$REQUEST_METHOD" in

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

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

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

1063 # cat > $tmpf # too much?

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

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

1066 s="`cat tmp/stream`"

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

1068 ;;

1069 esac

1070 case "$CONTENT_TYPE" in

1071 *boundary*)

1072 bndry=${CONTENT_TYPE#*boundary=}

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

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

1075 do

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

1077 #echo u=$us

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

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

1080 # err k=$k v=$v

1081 case "$k" in

1082 *:filename)

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

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

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

1086 [Ii]mage/x-xcf)

1087 bzip2 $tmpd/$v

1088 v=${v}.bz2

1089 ;;

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

1091 [Ii]mage/*)

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

1093 ;;

1094 esac

1095 ;;

1096 *)

1097 type='string'

1098 ;;

1099 esac

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

1101 setpar "$k" "$type" "$v"

1102 done

1103 ;;

1104 *)

1105 setviastring par "$s"

1106 ;;

1107 esac

1108 }

1109 email4group() {

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

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

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

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

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

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

1116 and s.key='email'

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

1118 err `echo $sql`

1119 query "$sql"

1120 }

1121 email4groupbyuid() {

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

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

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

1125 uids=`echo "$@"`

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

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

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

1129 and s.key='email'

1130 where g.gname=$qgrp and g.user in

1131 (select name from user where rowid in ($uids));"

1132 err email4gByid `echo $sql`

1133 query "$sql"

1134 }

1135 collectemail() (

1136 # Collect email addresses for group $1

1137 for e; do

1138 if isuser "$e"; then

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

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

1141 else

1142 # sql="with recursive allmem as

1143 # (select gname,val from grp_m where gname='$1'

1144 # union all select grp_m.gname,grp_m.val from grp_m,allmem

1145 # where allmem.val=grp_m.gname)

1146 # select coalesce(b.val,a.val)

1147 # from allmem a left join grp_mem_s b

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

1149 # where a.val in (select name from user) limit 10000;"

1150 # tmp tmp tmp tmp tmp tmp tmp

1151 qgrp=`sqlquote "$e"`

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

1153 grp_mem g left join grp_mem_s s

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

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

1156 where g.gname=$qgrp;"

1157 err CollectEmail: `echo "$sql"`

1158 query "$sql"

1159 fi

1160 done

1161 )

1162 sendinvitation() (

1163 # $1=email

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

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

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

1167 gecos=`gecos`

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

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

1170 _m4 -D_URL_="$urlbase" \

1171 -D_USER_="$name" \

1172 -D_EMAIL_="$1" \

1173 -D_REGIST_="$regist" \

1174 -D_ADMIN_="$admin" \

1175 $msgdir/mail-invite.m4 \

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

1177 return 0

1178 )

1179 emaildomaincheck() {

1180 case "$1" in

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

1182 *@*)

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

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

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

1186 return 2

1187 fi

1188 return 0

1189 ;;

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

1191 esac

1192 }

1193 invite() {

1194 email=`getpar email`

1195 case $email in

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

1197 *@*)

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

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

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

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

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

1203 elif sendinvitation $email; then

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

1205 fi ;;

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

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

1208 esac

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

1210 err addr=$addr

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

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

1213 fi

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

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

1216 $layout/html.m4.html $layout/invite.m4.html

1217 }

1218 regist() {

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

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

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

1222 echo "bye bye" | html p

1223 reutrn

1224 fi

1225 email=`session=$1 getpar invite`

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

1227 cat<<EOF

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

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

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

1231 EOF

1232 return

1233 fi

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

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

1236 # Fake login password to wasureta

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

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

1239 wasureta $email

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

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

1242 addsession $1 # for removal after 1 minute

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

1244 $layout/login.m4.html

1245 return

1246 }

1247 newgrpchk() {

1248 # Check if $1 is existing and

1249 }

1250 groupupdate() {

1251 gname=`getpar gname`

1252 qgname=`sqlquote $gname`

1253 err Enter:groupupdate

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

1255 # See ALSO same job in showgroup()

1256 newgname=`echo "$gname"|tr -d '\"'"'"`

1257 err newgname=$newgname

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

1259 err NewGNAME: gname=$newgname

1260 gname=$newgname

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

1262 replpar gname string "$gname"

1263 fi

1264 # Name confliction check

1265 parow=`getpar rowid`

1266 err parow=$parow

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

1268 query "BEGIN EXCLUSIVE;"

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

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

1271 if [ $count -gt 0 ]; then

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

1273 query "END;"

1274 return

1275 fi

1276 par2table $formdir/grp.def

1277 query "END TRANSACTION;"

1278 # Remove orphan

1279 : <<EOF

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

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

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

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

1284 EOF

1285 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1289 sql="delete from blog where id in

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

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

1292 query "$sql";

1293 fi

1294 fi

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

1296 fi

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

1298 grid=$(query $sql)

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

1300 grp $grid

1301 }

1302 groupman() {

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

1304

1305 GF_STAGE="grpconf"

1306 GF_STAGE=groupupdate

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

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

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

1310 -D_DUMPTABLE_="syscmd(cat)" \

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

1312 }

1313 userconf() {

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

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

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

1317 }

1318 groupconf() {

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

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

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

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

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

1324 err gcon \$1=$1 rowid=$rowid

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

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

1327 }

1328 mems() {

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

1330 kwd=`getpar kwd`

1331 listmember $kwd

1332 }

1333 grps() {

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

1335 kwd=`getpar kwd`

1336 listgroup $kwd \

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

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

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

1340 $layout/form+dump.m4.html

1341 }

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

1343 gpg=`getpar grp`

1344 grid=${1:-$gpg}

1345 grp=`getgroupbyid "$grid"`

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

1347 ## . ./s4-blog.sh

1348 jg=`getpar joingrp`

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

1350 err jg=$jg, grp=$grp

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

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

1353 fi

1354 echo "グループ $grp"|_m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

1355 showgroup "$grid"

1356 }

1357 listnewblogsql() { # $1=user

1358 cat<<EOF

1359 with article_ctime as (

1360 select id,blogid,author,val ctime

1361 from article join article_s s using(id)

1362 where s.key='ctime'

1363 ), blog_title_owner as (

1364 select blog.rowid, id,

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

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

1367 from blog, blog_s using(id) group by id

1368 ), acclog_user as (

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

1370 ), blogall as (

1371 select b.id, b.rowid, b.owner, b.title, ctime, ac.author

1372 from blog_title_owner b, article_ctime ac

1373 on b.id=ac.blogid

1374 )

1375 select rowid LINK,

1376 sum(case when l.time is null then 1

1377 when l.time < ctime then 1

1378 else 0

1379 end) "新着",

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

1381 /* owner gecos */

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

1383 from blogall bl left join acclog_user l

1384 on bl.rowid=round(l.tblrowid)

1385 where case when (select name from user where owner=name) is not null

1386 then 1 /* user's diary is always readable */

1387 when (select val from grp_s

1388 where gname=owner and key='regmode') = 'moderated'

1389 and -- moderated group and nonmember can't read, not counting

1390 (select user from grp_mem

1391 where gname=owner and user='$user') is null

1392 then 0

1393 else 1

1394 end

1395 group by bl.id

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

1397 limit 10;

1398 EOF

1399 }

1400

1401 showhome() {

1402 # $1=userRowIdToShow

1403 err showhome \$1=$1

1404 case "$1" in

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

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

1407 esac

1408 err ShowHome: uname=$uname

1409 gecos=`gecos "$uname"`

1410 err SH:gecos=$gecos

1411 GF_VIEWONLY=1

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

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

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

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

1416 # Display folders

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

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

1419 and type like 'file:%';"

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

1421 nfile=`query "$sql"`

1422 err nfile=$nfile

1423 if [ $nfile -gt 0 ]; then

1424 conflink="$conflink /

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

1426 fi

1427 fi

1428 . ./s4-blog.sh

1429

1430 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$

1431 echo "$gecos さん" > $tf

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

1433

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

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

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

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

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

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

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

1441 echo '">'

1442 } > $imf

1443 fi

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

1445 val='$uname';"`

1446 listblog $uname > $bf

1447 listgroupbytable $formdir/grp.def $cond \

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

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

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

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

1452 -D_NBLOG_="$nblog" \

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

1454 $layout/html.m4.html $layout/home.m4.html

1455

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

1457 # Display Most Recent Entry

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

1459 shortval=${shortval:-val}

1460 DT_SQL="SELECT b.rowid LINK,

1461 ctime,

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

1463 (SELECT gecos FROM gecoses

1464 WHERE name=(SELECT val FROM blog_s

1465 WHERE key='owner' AND id=b.id)) owner

1466 FROM blog b

1467 JOIN

1468 (SELECT distinct blogid, max(val) ctime

1469 FROM article a, article_s s

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

1471 GROUP BY blogid ORDER BY val DESC LIMIT 50

1472 ) m

1473 ON b.id=m.blogid;"

1474 cat<<-EOF

1475 <div class="fold">

1476 `cgi_checkbox mrd yes id="mre"`<label for="mre">

1477 最近書き込んだ場所一覧</label>

1478 `DT_VIEW=replyblog dumptable html blog`

1479 </div>

1480 EOF

1481 unset DT_SQL

1482 # Display NEWS

1483 if false; then

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

1485 new10=`DT_CHLD=article:blogid \

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

1487 else

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

1489 sql=`listnewblogsql "$user"`

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

1491 fi

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

1493 cont=$((cont-1))

1494 err newcount=$cont

1495 if [ $cont -gt 0 ]; then

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

1497 echo "$new10"

1498 fi

1499 cat<<EOF

1500 <div class="fold">

1501 `cgi_checkbox srchall yes id="sa"`<label

1502 for="sa">SNS全体から検索</label>

1503 <div>

1504 EOF

1505 cgi_form searchart<<EOF

1506 <label>`cgi_text kwd`という語を含む記事を全部から検索</label>

1507 EOF

1508 cat<<EOF

1509 </div></div>

1510 EOF

1511 fi

1512 #

1513 # Record access log

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

1515 }

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

1517 contenttype; echo

1518 err commission: "$@"

1519 gname=`getgroupbyid $1`

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

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

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

1523 grp_reg_adm "$@"

1524 else

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

1526 fi

1527 }

1528 listgroupbytable() {

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

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

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

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

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

1534 NGsql="select distinct tag from\

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

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

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

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

1539 err ListGRP: query sql="$sql"

1540 for tag in `query "$sql"`

1541 do

1542 err ListGrp: tag=$tag

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

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

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

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

1547 as rowid,

1548 gname,

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

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

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

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

1553 err PersonalGroupList= `echo $sql`

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

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

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

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

1558 echo '</table>'

1559 done

1560 echo '</div>'

1561 }

1562 iconhref() (

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

1564 data=`percenthex $1`

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

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

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

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

1569 )

1570 iconhref2() (

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

1572 src=$1

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

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

1575 )

1576 listentry() (

1577 # $1=user/group $2=SearchKeyword $3=condition(if any)

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

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

1580 cond=''

1581 offset=`getpar offset`

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

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

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

1585 limit=30

1586 dir=`getcachedir "$1"`

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

1588 hrb="$myname?home"

1589 deficon=person-default.png

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

1591 gcs=gecos

1592 else # if group

1593 hrb="$myname?grp"

1594 deficon=group-default.png

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

1596 gcs=name

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

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

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

1600 err tagconv=$tagconv

1601 fi

1602 fi

1603 if [ ! -d $dir ]; then

1604 mkdir -p $dir

1605 fi

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

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

1608 fi

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

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

1611 fi

1612

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

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

1615 ##qgrp=`sqlquote $grp`

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

1617 sql="select a.rowid, a.$link, coalesce(b.$gcs, a.$nm) as nick,

1618 coalesce(b.gecos, a.$nm) as name,

1619 b.tag,

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

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

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

1623 then '(ADMIN)'

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

1625 then '(Member)'

1626 when '$iamowner' = '' then ''

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

1628 from $tbl a left join

1629 (select $nm as name,

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

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

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

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

1634 order by b.tag desc, a.rowid asc"

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

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

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

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

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

1640 cgi_form $stage <<EOF

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

1642 `cgi_text kwd $kwd`</label>

1643 EOF

1644 echo '</div>'

1645 fi

1646 cat<<EOF

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

1648 EOF

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

1650 cat<<EOF

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

1652 `cgi_submit 次の${limit}件`

1653 `cgi_hidden kwd "$kwd"`

1654 `cgi_hidden stage "$stage"`

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

1656 EOF

1657 fi

1658 if [ $offset -gt 0 ]; then

1659 cat<<EOF

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

1661 `cgi_submit 前の${limit}件`

1662 `cgi_hidden stage "$stage"`

1663 `cgi_hidden kwd "$kwd"`

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

1665 EOF

1666 fi

1667

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

1669

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

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

1672 err name=$name owner=$ownerp lnk=$lnk

1673 err newlnk=$lnk

1674 files=`getvalbyid $tbl profimg $id $dir`

1675 # Pick up only first icon

1676 echo "<div class=\"iconlist xy$thumbxy\"><p class=\"tag _$tag\">$tag</p>" \

1677 | _m4 $tagconv

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

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

1680 iconhref $dir/$icon "$hrb+$lnk" "$gecos"

1681 else

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

1683 fi

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

1685 echo "</div>"

1686 done

1687 )

1688 listmember() {

1689 listentry user "$@"

1690 }

1691 listgroup() {

1692 listentry group "$@"

1693 }

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

1695 # gname=`getpar gname`

1696 # if [ -n "$gname" ]; then

1697 # err UPdating/Removing of group:::::::

1698 # # See ALSO same job in groupman()

1699 # newgname=`echo "$gname"|tr -d '\"'"'"`

1700 # ###newgname=$gname

1701 # err gname=$gname newgname=$newgname

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

1703 # err NewGNAME: gname=$newgname

1704 # gname=$newgname

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

1706 # replpar gname string "$gname"

1707 # fi

1708 # par2table $formdir/grp.def

1709 # fi

1710 grp=`getgroupbyid $1`

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

1712 if isgroup "$grp"; then

1713 tf=$tmpd/title.$$

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

1715 showgroupsub $formdir/grp.def "$1" | \

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

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

1718 -D_DUMPTABLE_="" \

1719 $layout/form+dump.m4.html

1720 else # if $grp is removed at par2table

1721 listgroup

1722 fi

1723 }

1724 showgroupsub() {

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

1726 rowid=$2

1727 grp=`getgroupbyid $2`

1728 qgrp=`sqlquote $grp`

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

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

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

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

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

1734 return

1735 fi

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

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

1738 viewtable $1 grp $rowid

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

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

1741 iamowner=$rowid

1742 fi

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

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

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

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

1747 cat<<EOF

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

1749 <div class="fold">

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

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

1752 <div>

1753 `cgi_textarea message "" "cols=60"`

1754 `cgi_submit 送信`

1755 `cgi_reset リセット`

1756 </div>

1757 `cgi_hidden grp $rowid`

1758 </div></form>

1759 EOF

1760 fi

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

1762 err ismember $user $grp

1763 ismember "$user" "$grp" && ismem='checked' || nomem='checked'

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

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

1766 and key='email';"`

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

1768 and key='email';"

1769 err email=$eml

1770 cat <<EOF

1771 <div class="fold">

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

1773 for="reg">自身の参加状態を操作する</label>

1774 <div>

1775 EOF

1776 cgi_form grp <<EOF

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

1778 <table class="b">

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

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

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

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

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

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

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

1786 </table>

1787 `cgi_hidden grp $rowid`

1788 EOF

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

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

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

1792 fi

1793 echo '</div></div>'

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

1795 cgi_form searchart<<EOF

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

1797 `cgi_hidden owner $grp`

1798 EOF

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

1800 DT_CHLD=article:blogid \

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

1802

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

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

1805 cm="?commission+$rowid"

1806 thumbxy=50x50 listmember "" "$c" \

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

1808 ## |sed -e "s|$<br>$,not=$.*$|\1<a href=\"$cm+\2\">管理者委託</a>|"

1809 }

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

1811 err GRP_ACTION:IN

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

1813 grp=`getgroupbyid "$grid"`

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

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

1816 fi

1817 if ! ismember $user $grp; then

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

1819 fi

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

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

1822

1823 isowner=""

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

1825 usel=`getpar usel`

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

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

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

1829 text=`getpar text`

1830

1831 rm=`getpar rm` cfm=`getpar confirm`

1832 err rm=$rm cfm=$cfm

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

1834 if [ "$isowner" ]; then

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

1836 # Eliminate

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

1838 for tbl in grp_mem grp_mem_s grp_mem_m; do

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

1840 # echo "sql=$sql"

1841 query "$sql"

1842 err rmGRPuser "$sql"

1843 done

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

1845 #err num=$num

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

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

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

1849 # err `echo "$sql"`

1850 html pre<<EOF

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

1852 `query "$sql"`

1853 EOF

1854 fi

1855 else

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

1857 return

1858 fi

1859 else # not Group Owner

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

1861 return

1862 fi

1863 cat<<EOF

1864

1865 EOF

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

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

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

1869 return 0

1870 fi

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

1872 "`gecos $user` さんからのメッセージ" <<EOF

1873 $url

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

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

1876 ----------------------------------------------------------

1877 $text

1878 EOF

1879 if [ $? = 0 ]; then

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

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

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

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

1884 html pre<<EOF

1885 `query "$sql"`

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

1887 EOF

1888 err SendDone: `echo $sql`

1889 fi

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

1891 grp_reg_adm $grid $usel

1892 fi

1893 fi

1894 # New entry

1895 sql="select u.rowid||','||j.* from user u

1896 join (select a.user as name,coalesce(b.val,a.user)

1897 from (select gname,user from grp_mem

1898 where gname=(select gname from grp where rowid=$1))

1899 a left join (select gname,user,val from grp_mem_s where key='email')

1900 b using(gname,user))

1901 j using(name) order by u.name;"

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

1903 case

1904 when (select user from grp_adm where

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

1906 and user=a.name) is not null

1907 then 'k'

1908 else ''

1909 end || a.rowid||

1910 ','||coalesce(val,a.name) as name from

1911 (select rowid,name from user where name in

1912 (select user from grp_mem where

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

1914 a left join user_s on a.name=user_s.name and key='gecos'

1915 order by coalesce(val,a.name);"

1916 err grpaction: `echo $sql`

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

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

1919 b2='"> <span>' b3='</span></label>'

1920 tf=$tmpd/title.$$

1921 echo "グループ[$grp]参加メンバーに対する操作" > $tf

1922 cmmsg="<div class=\"fold\">

1923 `cgi_radio rm commission id=\"cmadmin\"` <label

1924 for=\"cmadmin\">下でチェックした人にグループ管理者委任</label>

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

1926 </p></div></div>"

1927 excmsg="<div class=\"fold\">

1928 `cgi_radio rm yes ` 下でチェックした人のグループ登録解除

1929 <div>

1930 本当に消します! `cgi_checkbox confirm yes` 確認

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

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

1933 </div>

1934 </div>"

1935 cgi_form grpaction<<EOF \

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

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

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

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

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

1941 $layout/form+dump.m4.html

1942 <div class="fold">

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

1944 for="sendmsg">下でチェックした人にメッセージを送信する</label>

1945 <div>

1946 `cgi_textarea text "" cols=40`

1947 </div>

1948 </div>

1949 ${isowner:+$cmmsg$excmsg}

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

1951 <table>

1952 `sq $db -html "$sql"`

1953 </table>

1954 `cgi_hidden grp $grid`

1955 EOF

1956 }

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

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

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

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

1961 return

1962 fi

1963 owner=`getvalbyid blog owner $rowid`

1964 title=`getvalbyid blog title $rowid`

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

1966 | _m4 -D_TITLE_="修正" \

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

1968 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

1971 }

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

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

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

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

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

1977 return

1978 fi

1979 owner=`getvalbyid blog owner $blogrowid`

1980 title=`getvalbyid blog title $blogrowid`

1981 author=`getvalbyid article author $rowid`

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

1983 if isgrpowner $user $owner; then

1984 : EDIT OK

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

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

1987 return

1988 fi

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

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

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

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

1993 > $tmpout

1994 rm -f /tmp/editart.out

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

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

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

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

1999 -D_BLOGS_= -D_DUMPTABLE_= \

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

2001 }

2002 send2mem() {

2003 rowid=`getpar grp`

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

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

2006 return

2007 fi

2008 message=`getpar message`

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

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

2011 return

2012 fi

2013 grp=`getgroupbyid $rowid`

2014 members=`collectemail $grp`

2015 # smail rcpt subj (file)

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

2017 $urlbase?grp+$rowid

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

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

2020

2021 $message

2022 EOF

2023 cat<<EOF

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

2025 <pre>

2026 $members

2027 </pre>

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

2029 EOF

2030 }

2031 joingrpadmit() {

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

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

2034 echo "bye bye" | html p; return

2035 fi

2036 t_usr=`session=$2 getpar user`

2037 t_grp=`session=$2 getpar group`

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

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

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

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

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

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

2044 return

2045 fi

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

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

2048 fi

2049 case $1 in

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

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

2052 *)

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

2054 return ;;

2055 esac

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

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

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

2059 body="グループ $t_grp

2060 に

2061 $t_usr

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

2063 しました。"

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

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

2066 echo "$body" | html p

2067 }

2068

2069 joingrprequest() {

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

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

2072 addsession $jss +${memoplimitdays}days

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

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

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

2076 $url

2077 グループ $1

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

2079

2080 承認する:

2081 $urlbase?joingrpadmit+yes+$jss

2082

2083 白紙に戻す:

2084 $urlbase?joingrpadmit+no+$jss

2085 EOF

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

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

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

2089 }

2090 joingrp() {

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

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

2093 isgrpowner "$user" "$1" && isowner="yes" || isowner=""

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

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

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

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

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

2099 return

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

2101 : # Do not pursue those who leave

2102 else # adding user is $user itself

2103 case `getgroupattr $1 regmode` in

2104 moderated)

2105 joingrprequest "$@" # Request only

2106 return

2107 ;;

2108 *)

2109 ;;

2110 esac

2111 fi

2112 qgname=`sqlquote $1`

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

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

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

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

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

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

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

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

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

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

2123 # Coming here means newly created group

2124 sql="select case\

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

2126 then (select user from grp_mem\

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

2128 else '' end;"

2129 err NewGrpChk: $sql

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

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

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

2133 fi

2134 fi

2135 else

2136 echo $msg

2137 fi

2138 else

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

2140 fi

2141 else

2142 query "delete from grp_mem $cond;

2143 delete from grp_mem_s $cond;

2144 delete from grp_mem_m $cond;"

2145 fi

2146 }

2147 grp_reg_adm() {

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

2149 grid=$1

2150 grp=`getgroupbyid "$1"`

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

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

2153 fi

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

2155 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2156 fi

2157 shift

2158 for urid; do

2159 newadm=`query "select name from user where rowid=$urid;"`

2160 if [ -z "$newadm" ]; then

2161 echo "指定ユーザIDがおかしいようです。" | html p; return

2162 fi

2163 err GRP_reg_adm: "replace into grp_adm values(`sqlquote $grp`, '$newadm');"

2164 err ismember $newadm $grp

2165 if ismember $newadm $grp; then

2166 # OK, go ahead

2167 getgname="(select gname from grp where rowid=$grid)"

2168 query "replace into grp_adm values($getgname, '$newadm');"

2169 # confirm insertion

2170 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2171 if [ -n "`query \"$sql;\"`" ]; then

2172 echo "追加完了: $newadm" | html p

2173 else

2174 echo "追加失敗($1 $urid)" | html p

2175 fi

2176 fi

2177 showgroup $grid

2178 done

2179 }

2180 dumptable() {

2181 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2182 # textのフィールドだけ全てダンプにしたほうがいいか

2183 # $DT_VIEW sets link

2184 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2185 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2186 VIEW=${DT_VIEW-replyblog}

2187 if [ -n "$VIEW" ]; then

2188 dvlink=" <a href=\"$myname?$VIEW+\\2\">VIE</a><a href=\"$myname?$VIEW+\\2#bottom\">W</a>"

2189 fi

2190 # $DT_CHLD=ChildTable:BindColumn

2191 if [ -n "$DT_CHLD" ]; then

2192 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

2193 cntall="(select count($_i) from $_t where $_i=a.id)"

2194 cntnew="(select count(val) from ${_t}_s where key='ctime' \

2195 and id in (select id from $_t where $_i=a.id) \

2196 and val > coalesce((select time from acclog where \

2197 user='$user' and tbl='$2' and tblrowid=a.rowid),\

2198 '1970-01-01'))"

2199 cnt="$cntnew as '新着', $cntall as '総数',"

2200 dt_class=" td2r td3r"

2201 fi

2202 # Construct join expression

2203 eav="" scols=""

2204 pk=`gettblpkey $2`

2205 substr=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2206 substr=${substr:-val}

2207 for col in ${3:-`gettbl_s_cols $2`}; do

2208 case $col in

2209 gecos) scols="$scols${scols:+, }${col#}"

2210 continue ;; # built-in column name

2211 *:*) col=${col%:*} as=${col#*:} ;;

2212 *) as=${col} ;;

2213 esac

2214 eav=$eav${eav:+,}" max(case key when '$col' then $substr end) as $as"

2215 scols="$scols${scols:+, }b.$as"

2216 done

2217 #case author when '$user' then a.rowid else '---' end as ID,

2218 sql=${DT_SQL:-"select \

2219 a.rowid as LINK,\

2220 $cnt\

2221 $scols from $2 a left join\

2222 (select $pk,$eav,

2223 max(case key when 'owner'

2224 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos

2225 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

2226 err SQL=`echo "$sql"`

2227 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$</TD>,\1$elink$dvlink</TD>,"

2228 <div>

2229 <div class="dumptable">

2230 <table class="b$dt_class">

2231 `sq -header -cmd ".mode $1" $db "$sql"`

2232 </table>

2233 </div>

2234 </div>

2235 EOF

2236 }

2237

2238 par2table() (

2239 # copy current parameters of par into destination table

2240 # $1=definition-file

2241 # Using $user and $session

2242 # Return value:

2243 # 0: Stored successfully

2244 # 1: Insufficient fillings

2245 # 2: No permission to modify the record

2246 # 3: Invalid rowid

2247 # 4: SUCCESS to delete

2248 # 5: Stop deletion for lack of confirm check

2249 # 6: Password length too short

2250 # 7: Password mismatch

2251 # 8: Old password incorrect

2252 rowid=`getpar rowid`

2253 err ...........rowid=$rowid

2254 if [ ! -e $1 ]; then

2255 echo "テーブル定義ファイルが見付かりません" | html p

2256 exit 1

2257 fi

2258 tbl=${1%.def}

2259 tbl=${tbl##*/}

2260 if [ -n "$rowid" ]; then # Modify existing entry

2261 if [ x"$tbl" = x"user" ]; then

2262 rowowner=`query "select name from $tbl where rowid=$rowid;"`

2263 elif [ x"$tbl" = x"grp" ]; then

2264 sql="select gname from $tbl where rowid=$rowid;"

2265 ##err p2t:grp:q $sql

2266 isgrpowner $user "`query $sql`" && rowowner=$user

2267 else

2268 rowowner=`query "select owner from $tbl where rowid=$rowid;"`

2269 rowowner=${rowowner:-`query "select author from $tbl

2270 where rowid=$rowid;"`}

2271 fi

2272 ### err rowowner=$rowowner

2273 if [ x"$user" != x"$rowowner" ]; then

2274 echo "他人のレコードはいじれないの" | html p

2275 return 2

2276 elif [ -z "$rowowner" ]; then

2277 echo "指定したレコードはないみたい" | html p

2278 return 3

2279 fi

2280 rm=`getpar rm` cfm=`getpar confirm`

2281 # Editing existent entry

2282 if [ x"$rm" = x"yes" ]; then

2283 if [ x"$rm$cfm" = x"yesyes" ]; then

2284 query "delete from $tbl where rowid=$rowid;"

2285 return 4

2286 else

2287 echo "消去確認のチェックがないので消さなかったの..." | html p

2288 return 5

2289 fi

2290 fi

2291 fi

2292 # XX: Subshelling here is unnecessary 2015-07-05

2293 (ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

2294 if [ -n "$rowid" ]; then

2295 # Update of existing record

2296 for col in `gettblcols $tbl`; do

2297 val=`getparquote $col`

2298 [ -z "$val" ] && continue

2299 err query "update $tbl set $col=$val where rowid=$rowid"

2300 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

2301 sql="update $tbl set $col=$val where rowid=$rowid;"

2302 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

2303 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

2304 ## User name cannot be changed with interface provided with this

2305 ## script. But we offer the trigger to change owner user

2306 ## of blog_s table.

2307 err "select quote($col) from $tbl where rowid=$rowid;"

2308 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

2309 cat<<-EOF | query

2310 BEGIN;

2311 $sql

2312 update blog_s set val=$val

2313 where key='owner' and val=$old;

2314 COMMIT;

2315 EOF

2316 ## XX: DIRTY Hack Ends here

2317 ## We should keep blog's owner as a single column which has

2318 ## foreign key constraint with primary key of grp/user.

2319 else

2320 query "$sql"

2321 fi

2322 done

2323 # Then, set up $pval for further insertion of tbl_s and tbl_m

2324 for col in `gettblpkey $tbl`; do

2325 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

2326 pval="$pval${pval:+, }\"$val\""

2327 done

2328 else

2329 # New entry

2330 # Generate values() for primary keys

2331 for col in `gettblpkey $tbl`; do

2332 # Genuine primary keys for _m and _s

2333 val=`getvalquote $tbl $col`

2334 [ -z "$val" ] && continue

2335 pval="$pval${pval:+, }$val"

2336 done

2337 err pval=$pval

2338 for col in `gettblfkey $tbl`; do

2339 # args for values() to insertion into master table

2340 val=`getvalquote $tbl $col`

2341 [ -z "$val" ] && continue

2342 formaster=$formaster"${formaster:+, }$val"

2343 done

2344 formaster="$pval${formaster:+, }$formaster"

2345 err formaster=$formaster

2346 if [ -z "$formaster" ]; then

2347 echo "項目を全て埋めてください" | html pre

2348 return 1

2349 fi

2350 err "replace into $tbl values($formaster);"

2351 query "replace into $tbl values($formaster);"

2352 ## Insertion to master table, done

2353 fi

2354

2355 for kt in s m; do

2356 tb2=${tbl}_$kt

2357 for col in `gettbl_${kt}_cols $tbl`; do

2358 ptype=`getpartype $col "limit 1"`

2359

2360 # First, check update of existing entries in _m

2361 if [ $kt = m ]; then

2362 # sessID|address.1.22|string|Somewhere-x.y.z

2363 sql=""

2364 err dots from query "select var from par where var like '$col.%';"

2365 for v in `query "select var from par where var like '$col.%';"`; do

2366 # v=address.1.22

2367 st_rowid=${v##*.}

2368 origcol=${v%%.*} # original column derived from

2369 err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

2370 ##case `getpartype $v` in

2371 err CASE `gettbl_coltype $tbl/$origcol` in

2372 err edit flag = `getpar action.$v`

2373 case `getpar action.$v` in

2374 rm)

2375 if [ x`getpar confirm.$v` = x"yes" ]; then

2376 newsql="delete from $tb2"

2377 else

2378 echo "削除確認未チェック" | html p

2379 fi ;;

2380 edit)

2381 case `gettbl_coltype $tbl/$origcol` in

2382 image|document|binary)

2383 file=$tmpd/`getparfilename $v`

2384 err type=file=$file

2385 [ -z "$file" ] && continue

2386 bn=${file##*/}

2387 bin="X'"$(hexize $file)"'"

2388 ct=`file --mime-type $file|cut -d' ' -f2`

2389 type=\"file:$ct\"

2390 newsql="update $tb2 set val='$bn', type=$type, bin=$bin"

2391 cachedir=`getcachedir "$tbl/$rowid"`

2392 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

2393 rm -r $cachedir

2394 ;;

2395 *)

2396 newsql="update $tb2 set val=(select val from par where var \

2397 like '$col.%.$st_rowid')"

2398 ;;

2399 esac

2400 ;;

2401 *) # maybe "keep", do not modify value

2402 continue

2403 ;;

2404 esac

2405 # err newsql=$newsql

2406 sql=$sql$nl"$newsql where rowid=$st_rowid;"

2407 done

2408

2409 if [ x"$bin" = x"NULL" ]; then

2410 err repl:normal sql=`echo $sql`

2411 query "$sql

2412 delete from $tb2 where type='string' and val='';"

2413 err repl:normal done

2414 else

2415 sqlfile="$tmpd/sqlf.$$"

2416 echo "$sql" > $sqlfile

2417 err repl:sqlfile=`ls -lF $sqlfile`

2418 query ".read $sqlfile"

2419 err repl:done

2420 fi

2421 # Rest of kt==m: set multiple mode

2422 nr=`getparcount $col`

2423 else

2424 nr=1 # for kt==s, number of records is 1

2425 fi

2426

2427 i=0

2428 while [ $i -lt $nr ]; do

2429 limit="limit 1 offset $i"

2430 i=$((i+1)) # increase beforehand against continue

2431 val=`getvalquote $tbl $col "$limit"`

2432 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

2433 err $col=$val

2434 bin=NULL

2435 err partype$col=`getpartype $col "$limit"`

2436 case $ptype in

2437 file) file=$tmpd/`getparfilename $col "$limit"`

2438 err parfile-$col=$file

2439 [ -z "$file" ] && continue

2440 bin="X'"$(hexize $file)"'"

2441 ct=`file --mime-type $file|cut -d' ' -f2`

2442 type=\"file:$ct\" ;;

2443 "*"*) continue ;; # foreign table

2444 *) type=\"string\" ;;

2445 esac

2446 case `gettbl_coltype $tbl/$col` in

2447 password) # special care for password

2448 # name={password,pswd1,pswd2}

2449 p1=`getpar pswd1 "$limit"`

2450 if [ -z "$p1" ]; then

2451 continue # SKIP password setting, if p1 is empty

2452 else

2453 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

2454 ## err pswd=$pswd

2455 if pwcheck "$pswd"; then

2456 if [ x"$p1" = x"$p2" ]; then

2457 case "$p1" in

2458 ??????????*) ;;

2459 *) echo "パスワードは10字以上にしてください。" | html p

2460 return 6;;

2461 esac

2462 val="\"`echo $p1|mypwhash`\""

2463 else

2464 echo "2つの新パスワード不一致" | html p

2465 return 7

2466 fi

2467 else

2468 echo "旧パスワード違います" | html p

2469 return 8

2470 fi

2471 fi

2472 ;;

2473 esac

2474 err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

2475 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

2476 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

2477 if [ x"$bin" = x"NULL" ]; then

2478 err Normal-query: `echo $sql`

2479 query "$sql"

2480 else

2481 sqlfile="$tmpd/query.$$"

2482 echo "$sql" > $sqlfile

2483 err sqlfile=`ls -lF $sqlfile`

2484 query ".read $sqlfile"

2485 fi

2486 err p2t done

2487 done

2488 done

2489 done

2490 return 0

2491 err donee)

2492 )

2493 par2table_old() {

2494 # copy current parameters of par into destination table

2495 # $1=dst-table $2=definition-file

2496 # Using $user and $session

2497 rowid=`getpar rowid`

2498 if [ -n "$rowid" ]; then

2499 rm=`getpar rm` cfm=`getpar confirm`

2500 if [ x"$rm$cfm" = x"yesyes" ]; then

2501 sq $db "delete from $1 where rowid=$rowid and owner=\"$user\""

2502 return

2503 fi

2504 fi

2505 cat $2 \

2506 | (cols=""

2507 while IFS=: read prompt name type args; do

2508 [ x"$name" = x"stage" ] && continue

2509 if [ -n "$rowid" ]; then

2510 val=`getpar $name|sed -e 's/\"/\"\"/g'`

2511 sq $db "update $1 set $name=\"$val\" where rowid=$rowid and owner=\"$user\""

2512 else

2513 eav=$eav${eav+,}" max(case var when '$name' then val end)"

2514 fi

2515 done

2516 [ -n "$rowid" ] && return

2517 cond="where sessid='$session' group by sessid"

2518 sq $db "replace into $1 select $eav,\"$user\" from par $cond"

2519 # Think over again about putting $user

2520 )

2521 }

2522 genform() {

2523 # $1 = form definition file

2524 # $2, $3 (optional)= table name and ROWID

2525 # If $GF_VIEWONLY set and nonNull, output values without form

2526 # If $GF_HIDDEN set, use it hidden values

2527 # If $GF_OWNER set, use it as value of name="owner"

2528 # If $GF_STAGE set, use it as value of name="stage"

2529 forms="" hiddens="" rowid=$3

2530 if [ ! -e "$1" ]; then

2531 echo "そのようなデータベースはないようです($2)。" | html p

2532 return

2533 elif [ -n "$2" ]; then

2534 err genform1: "select * from $2 where rowid='$rowid'"

2535 rec=`query "select * from $2 where rowid='$rowid';"`

2536 if [ -z "$rec" ]; then

2537 pk=`gettblpkey $2`

2538 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

2539 err "select rowid from $2 where $pk='$rowid';"

2540 rec=`query "select rowid from $2 where $pk='$rowid';"`

2541 err rec-rowid=$rec

2542 rowid=$rec

2543 rec=$3

2544 fi

2545 if [ -z "$rec" ]; then

2546 echo "そんなレコードはないみたいね..." | html p

2547 return

2548 fi

2549 fi

2550 if [ -z "$GF_VIEWONLY" ]; then

2551 rm='<input id="rm" name="rm" type="checkbox"

2552 value="yes"><label for="rm">このエントリの削除</label>

2553 <span>ほんとうに消しますよ(確認)!

2554 <input name="confirm" type=checkbox value="yes">はい</span>'

2555 fi

2556 # Image Cache dir

2557 err genform: getcache=$2/$rowid

2558 td=`getcachedir "$2/$rowid"`

2559 while IFS=: read prompt name keytype type args; do

2560 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

2561 sp="${args:+ }"

2562 form="" val=""

2563 if [ -n "$rowid" ]; then

2564 # err genform2a: Seeking for "$2.$name, type=$type"

2565 val=`getvalbyid $2 $name $rowid $td|htmlescape`

2566 err genform3a: getvalbyid $2 $name $rowid $td

2567 err genform3b: val="[$val]"

2568 fi

2569 if [ -n "$GF_VIEWONLY" ]; then

2570 is_hidden "$2" "$name" && continue

2571 fi

2572 case "$type" in

2573 text*)

2574 cgiform=cgi_multi_$type

2575 if [ -s $td/$name.count -a -n "$val" ]; then

2576 form=`$cgiform $name $td`

2577 val=$(echo "$val"|

2578 while read fn; do

2579 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

2580 </td></tr>$nl"

2581 done)

2582 val="<table>$nl$val$nl</table>"

2583 else

2584 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

2585 err genform: cgi_$type $name $val "$args"

2586 form=`cgi_$type $name "$val" "$args"`

2587 fi

2588 ;;

2589 [Rr][Aa][Dd][Ii][Oo])

2590 fh="<label><input type=\"radio\" name=\"$name\""

2591 form="`echo $args|sed -e \

2592 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

2593 ;;

2594 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

2595 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

2596 ;;

2597 [Ss][Ee][Ll][Ee][Cc][Tt])

2598 fh="<select name=\"$name\">$nl"

2599 form=$(for l in $args; do

2600 echo "<option value=\"${l#*=}\">${l%=*}</option>"

2601 done)

2602 if [ -n "$val" ]; then

2603 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

2604 fi

2605 form="$fh$form</select>"

2606 ;;

2607 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

2608 if [ -s $td/$name.count ]; then

2609 form=`cgi_multi_file $name $td "$args"`

2610 if [ -n "$val" ]; then

2611 hrfb="$myname?showattc+$2_m"

2612 val=$(echo "$val" \

2613 | while read fn; do

2614 data=`percenthex $td/$fn`

2615 #ct=`cat $td/$fn.content-type`

2616 ct=`file --mime-type $td/$fn|cut -d' ' -f2`

2617 ri=`cat $td/$fn.rowid`

2618 ## err fn=$fn, name=$name, ri=$ri; ls -lF $td 1>&3

2619 #imgsrc="<img src=\"data:$ct,$data\">"

2620 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

2621 iconhref $td/$fn "$hrfb+$ri" ""

2622 done)

2623 fi

2624 else

2625 form="<input type=\"file\" name=\"$name\" $args>"

2626 if [ -n "$val" ]; then

2627 imgs=$(echo "$val"\

2628 |while read fn;do

2629 data=`percenthex $td/$fn`

2630 echo "<img src=\"data:image/png,$data\">$fn<br>"

2631 done)

2632 form=$form"<br>$imgs"

2633 val=$imgs # 2015-06-15

2634 else

2635 form="<input type=\"file\" name=\"$name\" $args>"

2636 fi

2637 fi

2638 ;;

2639 [Hh][Ii][Dd][Dd][Ee][Nn])

2640 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

2641 args="value=\"$GF_STAGE\""

2642 fi

2643 form="<input type=\"hidden\" name=\"$name\" $args>"

2644 prompt='' # Remove prompt

2645 ;;

2646 [Aa][Uu][Tt][Hh][Oo][Rr])

2647 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

2648 prompt="" ;;

2649 [Oo][Ww][Nn][Ee][Rr])

2650 val=${GF_OWNER:-$val}

2651 val=${val:-$user}

2652 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

2653 prompt="" ;;

2654 [Uu][Ss][Ee][Rr])

2655 # XXX: is null $user ok?

2656 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

2657 [ -n "$GF_VIEWONLY" ] && continue

2658 form="$user"

2659 ;;

2660 [Pp]assword)

2661 [ -n "$GF_VIEWONLY" ] && continue

2662 form="`cgi_passwd`"

2663 val=""

2664 ;;

2665 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

2666 if [ -z "$rowid" ]; then

2667 val=$((($(date +%s)-1433084400)/10))c$$

2668 fi

2669 ## form="<input type=\"hidden\" name=\"serial\" value=\"$val\">"

2670 ## 2015-07-31

2671 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

2672 prompt="" ;;

2673 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

2674 prompt=""

2675 ;;

2676 parent|path|blog*)

2677 prompt=""

2678 ;;

2679 "*"*)

2680 tail=$tail"``"

2681 continue ;;

2682 esac

2683 if [ -n "$prompt" ]; then

2684 if [ -n "${GF_VIEWONLY}" ]; then

2685 form=$val

2686 else

2687 :

2688 fi

2689 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

2690 else

2691 hiddens=$hiddens$nl"$form"

2692 fi

2693 done < $1

2694 # enctype="multipart/form-data"

2695 cat<<EOF

2696 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

2697 ${rowid:+$rm}

2698 <table class="b $2">

2699 $forms

2700 </table>$hiddens

2701 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

2702 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

2703 EOF

2704 if [ -z $GF_VIEWONLY ]; then

2705 cat<<EOF

2706 <input type="submit" name="sub" value="OK">

2707 <input type="reset" name="res" value="Reset">

2708 EOF

2709 fi

2710 cat<<EOF

2711 </form>

2712 $tail

2713 EOF

2714 }

2715 edittable() {

2716 # $1=form-def $2=table $3 rowid

2717 genform "$@"

2718 }

2719 viewtable() {

2720 GF_VIEWONLY=1 genform "$@"

2721 }

2722 showattc() {

2723 # $1=table_m $2=rowid

2724 err \$1=$1 \$2=$2

2725 if ! isfilereadable $user $1 $2; then

2726 contenttype; echo

2727 echo "このファイルは管理者のみしか見られません" | html p

2728 putfooter; exit

2729 fi

2730 idir=`umask 002; mktempd` || exit 1

2731 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

2732 bin=$idir/$myname-$$.bin

2733 sql="select quote(bin) from $1 where rowid='$2';"

2734 err showattc: sql: $sql

2735 sq $db "$sql" | unhexize > $bin

2736 tv=`query "select type,val from $1 where rowid='$2';"`

2737 type=${tv%\|*} fn=${tv#*\|}

2738 err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

2739 ct=${type#file:}

2740 case $ct in # all text/* changed to text/plain

2741 text/*)

2742 charset=`nkf -g $bin|cut -d' ' -f1`

2743 case $charset in

2744 ASCII*) charset="" ;;

2745 esac

2746 ct="text/plain${charset:+; charset=$charset}"

2747 ;;

2748 esac

2749 contenttype "$ct"

2750 echo "Content-Disposition: filename=\"$fn\""

2751 echo "Content-Length: " `cat $bin | wc -c`; echo

2752 #echo "Content-Type: " ${type#file:}; echo

2753 cat $bin

2754 }

2755 #

2756 # Some default stupid handler on CGI values

2757 #

2758 default_storedb() {

2759 # ARG: $1=table-def-file

2760 # RET: $tbl=table-name, $col=mail-column, $cols=columns

2761 tbl=`basename $1`

2762 tbl=${tbl%.def}

2763 cols="`grep :text $1|cut -d: -f2`"

2764 col=`echo "$cols"|head -1`

2765 vcol=`getpar $col`

2766 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

2767 if [ -n "$vcol" ]; then

2768 par2table $1

2769 else

2770 return 2 # No insertion occurred

2771 fi

2772 }

2773

2774 default_view() { # $1=def-file

2775 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

2776 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

2777 default_storedb "$@"

2778 query "select rowid from $tbl order by rowid desc;" \

2779 | while read rowid; do

2780 viewtable $1 $tbl $rowid

2781 done | _m4 -D_TITLE_="$tbl" \

2782 -D_FORM_="`genform $1`" \

2783 -D_DUMPTABLE_="syscmd(cat)" \

2784 $layout/html.m4.html $layout/form+dump.m4.html

2785 }

2786 default_viewtext() { # $1=def-file

2787 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

2788 default_storedb "$@"

2789 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

2790 | _m4 -D_TITLE_="$tbl" \

2791 -D_FORM_="`genform $1`" \

2792 -D_DUMPTABLE_="syscmd(cat)" \

2793 $layout/html.m4.html $layout/form+dump.m4.html

2794 }

2795 default_smail() {

2796 default_storedb "$@"

2797 if [ $? -eq 2 ]; then

2798 _m4 -D_TITLE_="入力" \

2799 -D_FORM_="`genform $1`" \

2800 -D_DUMPTABLE_="" \

2801 $layout/html.m4.html $layout/form+dump.m4.html

2802 return

2803 fi

2804 cond=""

2805 for pk in `gettblpkey $tbl`; do

2806 pv=$(sqlquote $(getpar $pk))

2807 cond="$cond${cond:+ and }$pk=$pv"

2808 done

2809 sql="select rowid from $tbl where $cond;"

2810 rowid=`query "$sql"`

2811 err smail1 - "$sql" "-> rowid=$rowid"

2812

2813 while IFS=: read prompt name keytype type args; do # Read from $1

2814 val=`getpar $name`

2815 if [ -n "$val" ]; then

2816 text="$text

2817 $prompt

2818 $name=$val

2819 ---------------------------------------------------------"

2820 fi

2821 case "$type" in

2822 image|document|file)

2823 fn="`getvalbyid $tbl $name $rowid $tmpd`"

2824 fns=$(echo "$fn"|while read fn; do

2825 err mv $tmpd/$fn.orig $tmpd/$fn

2826 mv $tmpd/$fn.orig $tmpd/$fn

2827 rm $tmpd/$fn.rowid # Remove cache flag

2828 err "`ls $tmpd/$fn`"

2829 echo $fn

2830 done)

2831 files="$files $fns"

2832 ;;

2833 esac

2834 done < $1

2835 err FILES=$files "`ls -lF $tmpd`"

2836 subj="from ${REMOTE_ADDR}"

2837 (echo "$url"

2838 echo "への書き込みがありました。"

2839 echo "------"

2840 echo "$text"

2841 ) | (cd $tmpd &&

2842 err LS="`ls -lF`" &&

2843 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

2844 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

2845 echo "以下の内容で送信しました。" | html p

2846 viewtable $1 $tbl \

2847 `query "select rowid from $tbl order by rowid desc limit 1;"`

2848 echo "戻る" | html a "href=\"?\""

2849 }