s4: 59bd085848ec s4-funcs.sh

s4

view s4-funcs.sh @ 180:59bd085848ec

Group name change affects to blog owner * Now query() accepts stdin. * Update val of key='owner' in blog_s at group name change.

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 26 Feb 2016 22:36:04 +0859
parents	479b57681014
children	fcfed35ca6d8

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 tconfs=""

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

32 conftbl=_tblconf

33 nl="

34 "

35 . ./s4-cgi.sh

37 : <<EOF

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

40 レポート提出管理

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

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

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

45 Web

46 締切設定

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

49 ○自分の提出物リスト

51 7/19 ○設置

52 ○一斉送信

53 ○getparfilename の tmpd の扱い

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

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

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

57 ○添付ファイル回収

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

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

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

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

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

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

65 ○レポート提出モード

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

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

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

71 7/9 ○管理者の追加

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

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

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

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

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

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

80 ○グループ検索

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

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

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

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

89 ○6/1 par2tableを triplex 対応に

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

91 →とすると一気に

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

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

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

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

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

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

100 ## 1. passwd

101 ## 2. grp

102 ## 3. grp_mem

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

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

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

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

107

108 ## ● listの定義:

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

110 ## ● hashの定義:

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

112

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

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

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

116 ## csv2sq3 で .csv.sq3 の Makefile

117

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

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

120 ## type := root | comment

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

122 ## unique(id, filename)

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

124

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

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

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

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

129 ## できたー!

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

131 ↓

132 ↓以下に変更

133 with recursive allmem as

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

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

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

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

138

139

140 with recursive allmem as

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

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

143 where allmem.val=grp_m.gname)

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

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

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

147

148

149 ## triggerもできた。

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

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

152

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

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

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

156 ## グループ管理

157 ## 個人情報管理

158 ##

159

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

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

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

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

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

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

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

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

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

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

170

171 ## ログテーブル

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

173

174

175

176 ■表設計

177 * 3つの表に分散管理

178 id格納表 + hash表 + list表

179 * *_s *_m

180

181

182

183 user, user_map, user_col

184

185 ■抽象エントリタイプ

186 * user

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

188 * group

189 権限判定に利用

190 * serial

191 自動idとして機能

192 * password

193 入力 type=passwordで入力

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

195 * session

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

197 * text

198 入力 type=text

199 * textarea

200 入力 textarea

201 * image|document

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

203 * owner

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

205 * gowner

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

207 * timestamp

208 datetime()

209 * parent

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

211 * path

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

213

214 格納タイプ

215 * list

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

217 * hash

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

219

220 オブジェクトタイプ

221 * entry

222 id, title, owner

223 * textpart

224 id, parentID, text

225 * binarypart

226 id, parentID, contenttype, filename, content

227 * content

228 hash(textpart), list(binarypart)

229 * topic

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

231 * reply

232 id, parentID, content

233 * blog

234 list(entry)

235 blog = [topic, list(reply)]

236

237

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

239 "text" => "hogehoge ..",

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

241 "author" => "taro",

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

243 "parent" => "/",

244 "path" => "/1",

245 "text" => "blah, blah, ....",

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

247 {"serial" => 2,

248 "author" => "hanako",

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

250 "parent" => "/",

251 "path" => "/2",

252 "text" => "blah, blah, ....",

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

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

255

256

257 user:=

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

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

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

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

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

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

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

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

266

267 変換表

268 /user/email=m

269

270 blog:=

271 シリアル:id:p:serial

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

273 所有者:owner:s:owner:

274 時刻:ctime:s:stamp:

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

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

277

278 article:=

279 シリアル:id:p:serial

280 筆者:author:s:owner

281 時刻:ctime:s:stamp:

282 参照元:parent:s:parent:

283 パス:path:s:path:

284 本文:text:s:textarea:

285 画像:image:m:image:

286

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

288

289

290 EOF

291

292 sq() {

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

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

295 }

296 dbsetup() {

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

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

299 sqi=$tmpdir/sqi.$$

300 sqo=$tmpdir/sqo.$$

301 mkfifo $sqi $sqo

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

303 sq $db < $sqi &

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

305 exec 2>> $tmpdir/error.out

306 exec 3>> $tmpdir/debug.out

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

308 rm $sqi

309 }

310 cleanup() {

311 echo .quit >&5

312 kill $sq3pid

313 kill $sq3pid

314 rm -f $sqo $sqi

315 rm -rf $tmpfiles

316 }

317 query() {

318 echo ".once $sqo" >&5

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

320 cat

321 else

322 echo "$@"

323 fi >&5

324 cat $sqo

325 }

326

327 ismember() {

328 # $1=user, $2=group

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

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

331 }

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

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

334 }

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

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

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

338 }

339 isgrpowner() (

340 # $1=user, $2=group

341 gn=`sqlquote "$2"`

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

343 err isgrpowner: $sql

344 test -n "`query $sql`"

345 )

346 getgroupadminmails() {

347 # $1=group

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

349 email4group "$1" "$i" ;

350 done

351 }

352 getgroupadmins() { # $1=group

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

354 qgrp=`sqlquote "$1"`

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

356 }

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

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

359 getvalbyid grp $2 \

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

361 }

362 getgroupbyid() {

363 # $1=id|gname

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

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

366 # err ggbyid: `echo $sql`

367 query $sql

368 }

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

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

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

372

373 # Return true when anonymous mode

374 [ "$anonymousmode" ] && return 0

375 # case `getvalbyid blog mode $2` in

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

377 # *closed*)

378 # owner=`getvalbyid blog owner $2`

379 # if isgrp $owner; then

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

381 # elif isuser $owner; then

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

383 # fi

384 # esac

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

386 sql="with getblog as (\

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

388 select blogid from article where id in\

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

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

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

392 select case\

393 when (select author from article where\

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

395 then 'author'\

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

397 then 'open'\

398 when (select val from getmode) is null \

399 then 'open'

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

401 then (select user from grp_adm where \

402 gname=(select val from getowner) and \

403 user='$1')\

404 when (select author from article where\

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

406 then 'user+author'

407 else '' end;"

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

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

410 result=`query "$sql"`

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

412 return 2

413 }

414 linkhome() {

415 # $1=UserOrGroup

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

417 if isuser $1; then

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

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

420 else

421 echo -n "grp+$1"

422 fi

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

424 }

425 acclog() (

426 # $1=table, $2=rowid

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

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

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

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

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

432 fi

433 )

434 gecos() (

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

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

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

438 # then coalesce(

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

440 # $u)

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

442 # then coalesce(

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

444 # $u)

445 # else $u

446 # end;"

447 #query "$sql"

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

449 )

450 setpar() {

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

452 }

453 replpar() {

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

455 }

456 getpar() {

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

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

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

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

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

462 fi

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

464 case "$var" in

465 owner)

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

467 echo $user; return

468 elif ismember $user $val; then

469 echo $val; return

470 fi ;;

471 esac

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

473 echo "$val"

474 }

475

476 getpartype() {

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

478 }

479 getparcount() {

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

481 }

482 getparfilename() {

483 # null if type of $1 is not file

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

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

486 }

487 sqlquote() {

488 (v="$1"

489 case "$v" in

490 "") return ;; # null

491 "X'"*) # quoted hex string

492 echo $1 ;;

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

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

495 echo "\"$v\""

496 return ;;

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

498 echo "\"$v\""

499 return ;;

500 *)

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

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

503 else

504 echo "\"$v\""

505 fi ;;

506 esac)

507 }

508 mktempd() {

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

510 }

511 getcachedir() { # $1=maintable

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

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

514 else

515 echo $tmpd/$thumbxy

516 fi

517 }

518 getval() {

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

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

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

522 echo "$user" ;;

523 stamp|datetime)

524 date "+%F %T" ;;

525 serial)

526 (s=`getpar $2`

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

528 *)

529 getpar "$2" "$3";;

530 esac

531 }

532

533 getvalquote() {

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

535 (v=`getval "$@"`

536 case "$v" in

537 "") echo NULL ;;

538 *) sqlquote "$v" ;;

539 esac)

540 }

541 getparquote() {

542 sqlquote `getpar $1`

543 }

544 getbinbyid() {

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

546

547 }

548 getvalbyid() {

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

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

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

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

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

554

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

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

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

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

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

560 return

561 fi

562 done

563 pk=`gettblpkey $1`

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

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

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

567 for kt in s m; do

568 td=${4:-$tmpd}

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

570 t=${1}_$kt

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

572 vcount=1 # count(val)

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

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

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

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

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

578 if [ $kt = m ]; then

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

580 # Reset val to store filenames if type is string

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

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

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

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

585 i=0

586 err gvbid: i=$i vcount=$vcount

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

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

589 i=$((i+1))

590 fn=$c.$i

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

592 case $type in

593 file:*)

594 #file=$td/$val

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

596 # FOR SPEED: Skip file generation if imgcache exists

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

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

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

600 .output $td/$fn.rowid

601 select rowid from $cond $slice;

602 .output $td/$fn

603 select val from $cond $slice;

604 .output $file.content-type

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

606 .output stdout

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

608 EOF

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

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

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

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

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

614 case $type in

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

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

617 *|*:[Aa]pplication*)

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

619 png:- > $file

620 ;;

621 esac

622 ;;

623 *)

624 sq $db<<EOF

625 .output $td/$fn.rowid

626 select rowid from $cond $slice;

627 .output $td/$fn

628 select val from $cond $slice;

629 EOF

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

631 ;;

632 esac

633 done

634 else

635 rm -f $td/$c.count

636 case $type in

637 file:*)

638 echo "$val" \

639 | while read fn; do

640 file=$td/$fn

641 if [ ! -s $file ]; then

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

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

644 | unhexize > $file

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

646 # err TTTTTTTTTTTTTTTT: $type

647 case $type in

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

649 *:[Aa]pplication*)

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

651 png:- > $file ;;

652 esac

653 fi

654 done

655 ;;

656 esac

657 fi

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

659 return

660 fi

661 done

662 done)

663 }

664 getvalbypkey() (

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

666 pk=`gettblpkey $1`

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

668 getvalbyid "$1" "$2" $rowid $4

669 )

670 getvalbycond() {

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

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

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

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

675 getvalbyid "$1" "$2" $rowid "$4"

676 fi

677 }

678 getpwfield() {

679 # getpwfield user column

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

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

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

683 echo "$val"

684 return 0

685 else

686 return 1

687 fi

688 }

689 encode() {

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

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

692 sha1=sha1

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

694 sha1=sha1sum

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

696 sha1=gsha1sum

697 fi

698 fi

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

700 }

701 mycrypt() (

702 key=$1 salt=$2

703 err \$2=$2

704 case $2 in

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

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

707 esac

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

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

710 )

711 hexize() {

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

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

714 hexize="xxd -p"

715 else

716 hexize_hd() {

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

718 }

719 hexize="hexize_hd"

720 fi

721 fi

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

723 }

724 unhexize() {

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

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

727 unhex="xxd -p -r"

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

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

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

731 EOF

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

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

734 # world writable...:)

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

736 fi

737 fi

738 cat $1 | $unhex

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

740 }

741 percenthex() {

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

743 }

744 htmlescape() {

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

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

747 }

748 enascii() {

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

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

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

752 else

753 enascii_now=`date +%FT%T`

754 enascii_sed() {

755 nkf -Z0Z1Z2 \

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

757 }

758 enascii="enascii_sed"

759 fi

760 fi

761 cat "$@" | $enascii

762 }

763 size_h() {

764 i="$1" oi=$1

765 set -- B B KB MB GB TB

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

767 oi=$i

768 i=$((i/1024))

769 shift

770 done

771 echo ${oi}$1

772 }

773 gettblconf() {

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

775 ## tconfs=`sq $db \

776 tconfs=`query \

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

778 fi

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

780 }

781 gettblkeys() {

782 # $1=tbl

783 gettblconf

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

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

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

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

788 case $type in

789 *p*)

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

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

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

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

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

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

796 esac

797 case $type in

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

799 esac

800 done

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

802 }

803 gettblpkey() {

804 # $1=tbl

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

806 }

807 gettblfkey() {

808 (x=`gettblkeys $1`

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

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

811 }

812 gettblcols() {

813 (x=`gettblkeys $1`

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

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

816 }

817 gettbl_s_cols() {

818 (x=`gettblkeys $1`

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

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

821 }

822 gettbl_m_cols() {

823 (x=`gettblkeys $1`

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

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

826 }

827 gettbl_h_cols() {

828 (x=`gettblkeys $1`

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

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

831 }

832 gettbl_coltype() (

833 gettblconf

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

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

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

837 )

838 is_hidden() {

839 # $1=Tbl $2=col

840 gettblconf

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

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

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

844 case $x in

845 *h*) return 0 ;;

846 *) return 1 ;;

847 esac

848 }

849

850 dbsetbyid() {

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

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

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

854 unset primary update

855 gettblconf

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

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

858 #err conf=$conf

859 case ${conf#*=} in

860 p*) primary=1 ;;

861 f*) update=1 ;;

862 u*) ;;

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

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

865 esac

866 #err t=$t

867 type=text fn=""

868 case $conf in

869 */password)

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

871 ;;

872 */image*|*/document*)

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

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

875 ;;

876 esac

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

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

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

880 if [ "$primary" ]; then

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

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

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

884 elif [ "$update" ]; then

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

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

887 else

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

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

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

891 fi

892 )

893 }

894 expire() (

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

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

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

898 )

899 addsession() {

900 # expireをセット

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

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

903 err addsession "$1" exp=$expire

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

905 # Remove old session parameters

906 now=`expire now`

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

908 }

909 gencookie() (

910 for kv; do

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

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

913 done

914 )

915 contenttype() {

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

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

918 }

919 putheader() {

920

921 }

922 putfooter() {

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

924 }

925 getcookie() (

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

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

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

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

930 done

931 )

932 genrandom() {

933 # $1=columns (default: 10)

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

935 }

936 smail() {

937 # smail rcpts subj (file)

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

939 rcpt=`echo $1` # strip newlines

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

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

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

943 }

944 setviastring() {

945 table=$1

946 oifs="$IFS"

947 IFS="&"

948 for us in $2; do

949 k=${us%%=*}

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

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

952 #echo $k=$v

953 done

954 IFS="$oifs"

955 }

956 checkdomain() (

957 # Check the validity of domain by referring DNS

958 item=$1

959 err checkdomain $1

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

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

962 )

963 pwcheck() {

964 # $1=passwd

965 dbpswd=`getpwfield $user pswd`

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

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

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

969 }

970 mypwhash() {

971 mycrypt `cat` `genrandom 5`

972 }

973 wasureta() {

974 user=$1

975 if ! checkdomain $user; then

976 contenttype; echo

977 m4 -D_TITLE_='Invalid email' $layout/title-only.html

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

979 putfooter

980 exit 0

981 fi

982 newpswd=`genrandom` # newsalt=`genrandom 5`

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

984 encpswd=`echo $newpswd|mypwhash`

985 dbsetbyid user $user pswd "$encpswd"

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

987 m4 -D_PSWD_="$newpswd" -D_URL_="$url" -D_ADMIN_="$admin" \

988 $msgdir/mail-newaccount.m4 \

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

990 | smail $user "New Account"

991 }

992 checkauth() {

993 user=`getpar user`

994 skc=`getpar skey` # from cookie

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

996 skey="`getpwfield $user skey`"

997 err user=$user skey=$skey

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

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

1000 return 0

1001 fi

1002 fi

1003 pswd=`getpar pswd`

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

1005 wasureta $user

1006 return 1 # wasureta error

1007 fi

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

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

1010 if pwcheck "$pswd"; then

1011 newsession=`genrandom 50`

1012 dbsetbyid user $user skey "$newsession"

1013 gencookie "user=$user" "skey=$newsession"

1014 return 0

1015 fi

1016 return 2 # Password mismatch

1017 }

1018 showlogin() {

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

1020 m4 -D_SYSNAME_="Welcome" -D_MYNAME_="$myname${args+?}$args" \

1021 $layout/login.m4.html

1022 exit 0

1023 }

1024 dologin() {

1025 checkauth

1026 st=$?

1027 if [ $st != 0 ]; then

1028 contenttype; echo

1029 m4 -D_USER_="$user" -D_URL_="$url" -D_ADMIN_="$admin" \

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

1031 showlogin # and EXIT

1032 fi

1033 }

1034

1035 # Do instant jobs here

1036 dbsetup

1037 trap cleanup INT HUP EXIT

1038 # trap cleanup INT HUP

1039

1040 err() {

1041 echo "$@" 1>&3

1042 }

1043

1044 cgiinit() {

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

1046 tmpf=tmp/stream

1047 tmpd=`tmpd=$tmpdir mktempd`

1048 tmpfiles=$tmpfiles" $tmpd"

1049 addsession $session

1050 getcookie

1051 case "$REQUEST_METHOD" in

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

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

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

1055 # cat > $tmpf # too much?

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

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

1058 s="`cat tmp/stream`"

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

1060 ;;

1061 esac

1062 case "$CONTENT_TYPE" in

1063 *boundary*)

1064 bndry=${CONTENT_TYPE#*boundary=}

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

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

1067 do

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

1069 #echo u=$us

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

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

1072 # err k=$k v=$v

1073 case "$k" in

1074 *:filename)

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

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

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

1078 [Ii]mage/x-xcf)

1079 bzip2 $tmpd/$v

1080 v=${v}.bz2

1081 ;;

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

1083 [Ii]mage/*)

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

1085 ;;

1086 esac

1087 ;;

1088 *)

1089 type='string'

1090 ;;

1091 esac

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

1093 setpar "$k" "$type" "$v"

1094 done

1095 ;;

1096 *)

1097 setviastring par "$s"

1098 ;;

1099 esac

1100 }

1101 email4group() {

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

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

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

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

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

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

1108 and s.key='email'

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

1110 err `echo $sql`

1111 query "$sql"

1112 }

1113 email4groupbyuid() {

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

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

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

1117 uids=`echo "$@"`

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

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

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

1121 and s.key='email'

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

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

1124 err email4gByid `echo $sql`

1125 query "$sql"

1126 }

1127 collectemail() (

1128 # Collect email addresses for group $1

1129 for e; do

1130 if isuser "$e"; then

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

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

1133 else

1134 # sql="with recursive allmem as

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

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

1137 # where allmem.val=grp_m.gname)

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

1139 # from allmem a left join grp_mem_s b

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

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

1142 # tmp tmp tmp tmp tmp tmp tmp

1143 qgrp=`sqlquote "$e"`

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

1145 grp_mem g left join grp_mem_s s

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

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

1148 where g.gname=$qgrp;"

1149 err CollectEmail: `echo "$sql"`

1150 query "$sql"

1151 fi

1152 done

1153 )

1154 sendinvitation() (

1155 # $1=email

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

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

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

1159 gecos=`gecos`

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

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

1162 m4 -D_URL_="$urlbase" \

1163 -D_USER_="$name" \

1164 -D_EMAIL_="$1" \

1165 -D_REGIST_="$regist" \

1166 -D_ADMIN_="$admin" \

1167 $msgdir/mail-invite.m4 \

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

1169 return 0

1170 )

1171 emaildomaincheck() {

1172 case "$1" in

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

1174 *@*)

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

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

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

1178 return 2

1179 fi

1180 return 0

1181 ;;

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

1183 esac

1184 }

1185 invite() {

1186 email=`getpar email`

1187 case $email in

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

1189 *@*)

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

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

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

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

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

1195 elif sendinvitation $email; then

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

1197 fi ;;

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

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

1200 esac

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

1202 err addr=$addr

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

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

1205 fi

1206 m4 -D_TITLE_="招待" -D_REPORT_="\`$repo'" -D_ACTION_="?invite" \

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

1208 $layout/html.m4.html $layout/invite.m4.html

1209 }

1210 regist() {

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

1212 m4 -D_TITLE_="Invitation" $layout/html.m4.html

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

1214 echo "bye bye" | html p

1215 reutrn

1216 fi

1217 email=`session=$1 getpar invite`

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

1219 cat<<EOF

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

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

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

1223 EOF

1224 return

1225 fi

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

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

1228 # Fake login password to wasureta

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

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

1231 wasureta $email

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

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

1234 addsession $1 # for removal after 1 minute

1235 m4 -D_SYSNAME_="Initial Login" -D_MYNAME_="$myname?userconf" \

1236 $layout/login.m4.html

1237 return

1238 }

1239 newgrpchk() {

1240 # Check if $1 is existing and

1241 }

1242 groupupdate() {

1243 gname=`getpar gname`

1244 qgname=`sqlquote $gname`

1245 err Enter:groupupdate

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

1247 # See ALSO same job in showgroup()

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

1249 err newgname=$newgname

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

1251 err NewGNAME: gname=$newgname

1252 gname=$newgname

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

1254 replpar gname string "$gname"

1255 fi

1256 # Name confliction check

1257 parow=`getpar rowid`

1258 err parow=$parow

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

1260 query "BEGIN EXCLUSIVE;"

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

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

1263 if [ $count -gt 0 ]; then

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

1265 query "END;"

1266 return

1267 fi

1268 par2table $formdir/grp.def

1269 query "END TRANSACTION;"

1270 # Remove orphan

1271 : <<EOF

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

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

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

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

1276 EOF

1277 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1281 sql="delete from blog where id in

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

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

1284 query "$sql";

1285 fi

1286 fi

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

1288 fi

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

1290 grid=$(query $sql)

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

1292 grp $grid

1293 }

1294 groupman() {

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

1296

1297 GF_STAGE="grpconf"

1298 GF_STAGE=groupupdate

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

1300 |m4 -D_TITLE_="グループ作成" \

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

1302 -D_DUMPTABLE_="syscmd(cat)" \

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

1304 }

1305 userconf() {

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

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

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

1309 }

1310 groupconf() {

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

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

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

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

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

1316 err gcon \$1=$1 rowid=$rowid

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

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

1319 }

1320 mems() {

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

1322 kwd=`getpar kwd`

1323 listmember $kwd

1324 }

1325 grps() {

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

1327 kwd=`getpar kwd`

1328 listgroup $kwd \

1329 | m4 -D_DUMPTABLE_="syscmd(cat)" \

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

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

1332 $layout/form+dump.m4.html

1333 }

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

1335 gpg=`getpar grp`

1336 grid=${1:-$gpg}

1337 grp=`getgroupbyid "$grid"`

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

1339 ## . ./s4-blog.sh

1340 jg=`getpar joingrp`

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

1342 err jg=$jg, grp=$grp

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

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

1345 fi

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

1347 showgroup "$grid"

1348 }

1349 listnewblogsql() { # $1=user

1350 cat<<EOF

1351 with article_ctime as (

1352 select id,blogid,author,val ctime

1353 from article join article_s s using(id)

1354 where s.key='ctime'

1355 ), blog_title_owner as (

1356 select blog.rowid, id,

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

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

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

1360 ), acclog_user as (

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

1362 ), blogall as (

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

1364 from blog_title_owner b, article_ctime ac

1365 on b.id=ac.blogid

1366 )

1367 select rowid ID,

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

1369 when l.time < ctime then 1

1370 else 0

1371 end) "新着",

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

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

1374 from blogall bl left join acclog_user l

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

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

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

1378 when (select val from grp_s

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

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

1381 (select user from grp_mem

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

1383 then 0

1384 else 1

1385 end

1386 group by bl.id

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

1388 limit 10;

1389 EOF

1390 }

1391

1392 showhome() {

1393 # $1=userRowIdToShow

1394 err showhome \$1=$1

1395 case "$1" in

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

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

1398 esac

1399 err ShowHome: uname=$uname

1400 gecos=`gecos "$uname"`

1401 err SH:gecos=$gecos

1402 GF_VIEWONLY=1

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

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

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

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

1407 # Display folders

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

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

1410 and type like 'file:%';"

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

1412 nfile=`query "$sql"`

1413 err nfile=$nfile

1414 if [ $nfile -gt 0 ]; then

1415 conflink="$conflink /

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

1417 fi

1418 fi

1419 . ./s4-blog.sh

1420

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

1422 echo "$gecos さん" > $tf

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

1424

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

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

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

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

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

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

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

1432 echo '">'

1433 } > $imf

1434 fi

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

1436 val='$uname';"`

1437 listblog $uname > $bf

1438 listgroupbytable $formdir/grp.def $cond \

1439 | m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

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

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

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

1443 -D_NBLOG_="$nblog" \

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

1445 $layout/html.m4.html $layout/home.m4.html

1446

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

1448 # Display NEWS

1449 if false; then

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

1451 new10=`DT_CHLD=article:blogid \

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

1453 else

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

1455 sql=`listnewblogsql "$user"`

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

1457 fi

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

1459 cont=$((cont-1))

1460 err newcount=$cont

1461 if [ $cont -gt 0 ]; then

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

1463 echo "$new10"

1464 fi

1465 cat<<EOF

1466 <div class="fold">

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

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

1469 <div>

1470 EOF

1471 cgi_form searchart<<EOF

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

1473 EOF

1474 cat<<EOF

1475 </div></div>

1476 EOF

1477 fi

1478 #

1479 # Record access log

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

1481 }

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

1483 contenttype; echo

1484 err commission: "$@"

1485 gname=`getgroupbyid $1`

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

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

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

1489 grp_reg_adm "$@"

1490 else

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

1492 fi

1493 }

1494 listgroupbytable() {

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

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

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

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

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

1500 NGsql="select distinct tag from\

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

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

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

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

1505 err ListGRP: query sql="$sql"

1506 for tag in `query "$sql"`

1507 do

1508 err ListGrp: tag=$tag

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

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

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

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

1513 as rowid,

1514 gname,

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

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

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

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

1519 err PersonalGroupList= `echo $sql`

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

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

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

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

1524 echo '</table>'

1525 done

1526 echo '</div>'

1527 }

1528 iconhref() (

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

1530 data=`percenthex $1`

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

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

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

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

1535 )

1536 iconhref2() (

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

1538 src=$1

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

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

1541 )

1542 listentry() (

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

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

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

1546 cond=''

1547 offset=`getpar offset`

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

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

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

1551 limit=30

1552 dir=`getcachedir "$1"`

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

1554 hrb="$myname?home"

1555 deficon=person-default.png

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

1557 gcs=gecos

1558 else # if group

1559 hrb="$myname?grp"

1560 deficon=group-default.png

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

1562 gcs=name

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

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

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

1566 err tagconv=$tagconv

1567 fi

1568 fi

1569 if [ ! -d $dir ]; then

1570 mkdir -p $dir

1571 fi

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

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

1574 fi

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

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

1577 fi

1578

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

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

1581 ##qgrp=`sqlquote $grp`

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

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

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

1585 b.tag,

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

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

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

1589 then '(ADMIN)'

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

1591 then '(Member)'

1592 when '$iamowner' = '' then ''

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

1594 from $tbl a left join

1595 (select $nm as name,

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

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

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

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

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

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

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

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

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

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

1606 cgi_form $stage <<EOF

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

1608 `cgi_text kwd $kwd`</label>

1609 EOF

1610 echo '</div>'

1611 fi

1612 cat<<EOF

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

1614 EOF

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

1616 cat<<EOF

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

1618 `cgi_submit 次の${limit}件`

1619 `cgi_hidden kwd "$kwd"`

1620 `cgi_hidden stage "$stage"`

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

1622 EOF

1623 fi

1624 if [ $offset -gt 0 ]; then

1625 cat<<EOF

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

1627 `cgi_submit 前の${limit}件`

1628 `cgi_hidden stage "$stage"`

1629 `cgi_hidden kwd "$kwd"`

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

1631 EOF

1632 fi

1633

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

1635

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

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

1638 err name=$name owner=$ownerp lnk=$lnk

1639 err newlnk=$lnk

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

1641 # Pick up only first icon

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

1643 | m4 $tagconv

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

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

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

1647 else

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

1649 fi

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

1651 echo "</div>"

1652 done

1653 )

1654 listmember() {

1655 listentry user "$@"

1656 }

1657 listgroup() {

1658 listentry group "$@"

1659 }

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

1661 # gname=`getpar gname`

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

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

1664 # # See ALSO same job in groupman()

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

1666 # ###newgname=$gname

1667 # err gname=$gname newgname=$newgname

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

1669 # err NewGNAME: gname=$newgname

1670 # gname=$newgname

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

1672 # replpar gname string "$gname"

1673 # fi

1674 # par2table $formdir/grp.def

1675 # fi

1676 grp=`getgroupbyid $1`

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

1678 if isgroup "$grp"; then

1679 tf=$tmpd/title.$$

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

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

1682 m4 -D_TITLE_="spaste(\`$tf')" \

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

1684 -D_DUMPTABLE_="" \

1685 $layout/form+dump.m4.html

1686 else # if $grp is removed at par2table

1687 listgroup

1688 fi

1689 }

1690 showgroupsub() {

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

1692 rowid=$2

1693 grp=`getgroupbyid $2`

1694 qgrp=`sqlquote $grp`

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

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

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

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

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

1700 return

1701 fi

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

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

1704 viewtable $1 grp $rowid

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

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

1707 iamowner=$rowid

1708 fi

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

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

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

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

1713 cat<<EOF

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

1715 <div class="fold">

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

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

1718 <div>

1719 `cgi_textarea message "" "cols=60"`

1720 `cgi_submit 送信`

1721 `cgi_reset リセット`

1722 </div>

1723 `cgi_hidden grp $rowid`

1724 </div></form>

1725 EOF

1726 fi

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

1728 err ismember $user $grp

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

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

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

1732 and key='email';"`

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

1734 and key='email';"

1735 err email=$eml

1736 cat <<EOF

1737 <div class="fold">

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

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

1740 <div>

1741 EOF

1742 cgi_form grp <<EOF

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

1744 <table class="b">

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

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

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

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

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

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

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

1752 </table>

1753 `cgi_hidden grp $rowid`

1754 EOF

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

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

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

1758 fi

1759 echo '</div></div>'

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

1761 cgi_form searchart<<EOF

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

1763 `cgi_hidden owner $grp`

1764 EOF

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

1766 DT_CHLD=article:blogid \

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

1768

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

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

1771 cm="?commission+$rowid"

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

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

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

1775 }

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

1777 err GRP_ACTION:IN

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

1779 grp=`getgroupbyid "$grid"`

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

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

1782 fi

1783 if ! ismember $user $grp; then

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

1785 fi

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

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

1788

1789 isowner=""

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

1791 usel=`getpar usel`

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

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

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

1795 text=`getpar text`

1796

1797 rm=`getpar rm` cfm=`getpar confirm`

1798 err rm=$rm cfm=$cfm

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

1800 if [ "$isowner" ]; then

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

1802 # Eliminate

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

1804 for tbl in grp_mem grp_mem_s grp_mem_m; do

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

1806 # echo "sql=$sql"

1807 query "$sql"

1808 err rmGRPuser "$sql"

1809 done

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

1811 #err num=$num

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

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

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

1815 # err `echo "$sql"`

1816 html pre<<EOF

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

1818 `query "$sql"`

1819 EOF

1820 fi

1821 else

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

1823 return

1824 fi

1825 else # not Group Owner

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

1827 return

1828 fi

1829 cat<<EOF

1830

1831 EOF

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

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

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

1835 return 0

1836 fi

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

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

1839 $url

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

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

1842 ----------------------------------------------------------

1843 $text

1844 EOF

1845 if [ $? = 0 ]; then

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

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

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

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

1850 html pre<<EOF

1851 `query "$sql"`

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

1853 EOF

1854 err SendDone: `echo $sql`

1855 fi

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

1857 grp_reg_adm $grid $usel

1858 fi

1859 fi

1860 # New entry

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

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

1863 from (select gname,user from grp_mem

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

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

1866 b using(gname,user))

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

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

1869 case

1870 when (select user from grp_adm where

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

1872 and user=a.name) is not null

1873 then 'k'

1874 else ''

1875 end || a.rowid||

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

1877 (select rowid,name from user where name in

1878 (select user from grp_mem where

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

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

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

1882 err grpaction: `echo $sql`

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

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

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

1886 tf=$tmpd/title.$$

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

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

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

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

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

1892 </p></div></div>"

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

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

1895 <div>

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

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

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

1899 </div>

1900 </div>"

1901 cgi_form grpaction<<EOF \

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

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

1904 | m4 -D_TITLE_="spaste(\`$tf')" \

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

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

1907 $layout/form+dump.m4.html

1908 <div class="fold">

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

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

1911 <div>

1912 `cgi_textarea text "" cols=40`

1913 </div>

1914 </div>

1915 ${isowner:+$cmmsg$excmsg}

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

1917 <table>

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

1919 `cgi_hidden grp $grid`

1920 </table>

1921 EOF

1922 }

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

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

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

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

1927 return

1928 fi

1929 owner=`getvalbyid blog owner $rowid`

1930 title=`getvalbyid blog title $rowid`

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

1932 | m4 -D_TITLE_="修正" \

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

1934 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

1937 }

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

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

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

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

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

1943 return

1944 fi

1945 owner=`getvalbyid blog owner $blogrowid`

1946 title=`getvalbyid blog title $blogrowid`

1947 author=`getvalbyid article author $rowid`

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

1949 if isgrpowner $user $owner; then

1950 : EDIT OK

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

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

1953 return

1954 fi

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

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

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

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

1959 > $tmpout

1960 rm -f /tmp/editart.out

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

1962 m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

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

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

1965 -D_BLOGS_= -D_DUMPTABLE_= \

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

1967 }

1968 send2mem() {

1969 rowid=`getpar grp`

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

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

1972 return

1973 fi

1974 message=`getpar message`

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

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

1977 return

1978 fi

1979 grp=`getgroupbyid $rowid`

1980 members=`collectemail $grp`

1981 # smail rcpt subj (file)

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

1983 $urlbase?grp+$rowid

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

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

1986

1987 $message

1988 EOF

1989 cat<<EOF

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

1991 <pre>

1992 $members

1993 </pre>

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

1995 EOF

1996 }

1997 joingrpadmit() {

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

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

2000 echo "bye bye" | html p; return

2001 fi

2002 t_usr=`session=$2 getpar user`

2003 t_grp=`session=$2 getpar group`

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

2005 m4 -D_TITLE_="joingrp" $layout/html.m4.html

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

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

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

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

2010 return

2011 fi

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

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

2014 fi

2015 case $1 in

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

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

2018 *)

2019 echo "無効な指定です($1)。" | html p

2020 return ;;

2021 esac

2022 gid=$(query "select rowid from grp where gname=`sqlquote $t_grp`;")

2023 rcpts="`getgroupadminmails $t_grp` $user"

2024 err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2025 body="グループ $t_grp

2026 に

2027 $t_usr

2028 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2029 しました。"

2030 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2031 query "delete from session where id='$2';"

2032 echo "$body" | html p

2033 }

2034

2035 joingrprequest() {

2036 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2037 jss="joingrp-`date +%s`-`genrandom 12`"

2038 addsession $jss +${memoplimitdays}days

2039 query "replace into par values('$jss', 'group', 'string', `sqlquote $1`),

2040 ('$jss', 'user', 'string', `sqlquote $user`);"

2041 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2042 $url

2043 グループ $1

2044 に加入依頼がありました。

2045

2046 承認する:

2047 $urlbase?joingrpadmit+yes+$jss

2048

2049 白紙に戻す:

2050 $urlbase?joingrpadmit+no+$jss

2051 EOF

2052 echo "管理者に加入依頼を出しました。

2053 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2054 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2055 }

2056 joingrp() {

2057 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2058 err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2059 isgrpowner "$user" "$1" && isowner="yes" || isowner=""

2060 err jg:isgrpowner: isowner="$isowner"

2061 if [ -n "$isowner" ]; then

2062 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2063 elif [ x"$2" != x"$user" ]; then # if user is not login user

2064 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2065 return

2066 elif [ x"$3" = x"no" ]; then

2067 : # Do not pursue those who leave

2068 else # adding user is $user itself

2069 case `getgroupattr $1 regmode` in

2070 moderated)

2071 joingrprequest "$@" # Request only

2072 return

2073 ;;

2074 *)

2075 ;;

2076 esac

2077 fi

2078 qgname=`sqlquote $1`

2079 cond="where gname=$qgname and user='$2'"

2080 if [ x"$3" = x"yes" ]; then

2081 query "replace into grp_mem values($qgname, '$2');"

2082 if [ -n "$4" ]; then

2083 if msg=`emaildomaincheck "$4"`; then

2084 err "replace into grp_mem_s values($qgname, '$user', 'email', \

2085 'string', '$4', NULL);"

2086 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2087 'string', '$4', NULL);"

2088 if [ -n "$5" ]; then # as ADMIN

2089 # Coming here means newly created group

2090 sql="select case\

2091 when (select count(*) from grp_mem where gname=$qgname)=1\

2092 then (select user from grp_mem\

2093 where gname=$qgname and user='$user')\

2094 else '' end;"

2095 err NewGrpChk: $sql

2096 if [ -n "`query \"$sql\"`" ]; then

2097 err ADMIN: "replace into grp_adm values($qgname, '$user');"

2098 query "replace into grp_adm values($qgname, '$user');"

2099 fi

2100 fi

2101 else

2102 echo $msg

2103 fi

2104 else

2105 query "delete from grp_mem_s $cond and key='email';"

2106 fi

2107 else

2108 query "delete from grp_mem $cond;

2109 delete from grp_mem_s $cond;

2110 delete from grp_mem_m $cond;"

2111 fi

2112 }

2113 grp_reg_adm() {

2114 # $1=grp-rowid $2...=user-rowid

2115 grid=$1

2116 grp=`getgroupbyid "$1"`

2117 if [ -z "$grp" ]; then

2118 echo "無効なグループIDです" | html p; return

2119 fi

2120 if ! isgrpowner $user "$grp"; then

2121 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2122 fi

2123 shift

2124 for urid; do

2125 newadm=`query "select name from user where rowid=$urid;"`

2126 if [ -z "$newadm" ]; then

2127 echo "指定ユーザIDがおかしいようです。" | html p; return

2128 fi

2129 err GRP_reg_adm: "replace into grp_adm values(`sqlquote $grp`, '$newadm');"

2130 err ismember $newadm $grp

2131 if ismember $newadm $grp; then

2132 # OK, go ahead

2133 getgname="(select gname from grp where rowid=$grid)"

2134 query "replace into grp_adm values($getgname, '$newadm');"

2135 # confirm insertion

2136 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2137 if [ -n "`query \"$sql;\"`" ]; then

2138 echo "追加完了: $newadm" | html p

2139 else

2140 echo "追加失敗($1 $urid)" | html p

2141 fi

2142 fi

2143 showgroup $grid

2144 done

2145 }

2146 dumptable() {

2147 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2148 # textのフィールドだけ全てダンプにしたほうがいいか

2149 # $DT_VIEW sets link

2150 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2151 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2152 VIEW=${DT_VIEW-replyblog}

2153 if [ -n "$VIEW" ]; then

2154 dvlink=" <a href=\"$myname?$VIEW+\\2\">VIE</a><a href=\"$myname?$VIEW+\\2#bottom\">W</a>"

2155 fi

2156 # $DT_CHLD=ChildTable:BindColumn

2157 if [ -n "$DT_CHLD" ]; then

2158 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

2159 cntall="(select count($_i) from $_t where $_i=a.id)"

2160 cntnew="(select count(val) from ${_t}_s where key='ctime' \

2161 and id in (select id from $_t where $_i=a.id) \

2162 and val > coalesce((select time from acclog where \

2163 user='$user' and tbl='$2' and tblrowid=a.rowid),\

2164 '1970-01-01'))"

2165 cnt="$cntnew as '新着', $cntall as '総数',"

2166 dt_class=" td2r td3r"

2167 fi

2168 # Construct join expression

2169 eav="" scols=""

2170 pk=`gettblpkey $2`

2171 substr=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2172 substr=${substr:-val}

2173 for col in ${3:-`gettbl_s_cols $2`}; do

2174 case $col in

2175 gecos) scols="$scols${scols:+, }${col#}"

2176 continue ;; # built-in column name

2177 *:*) col=${col%:*} as=${col#*:} ;;

2178 *) as=${col} ;;

2179 esac

2180 eav=$eav${eav:+,}" max(case key when '$col' then $substr end) as $as"

2181 scols="$scols${scols:+, }b.$as"

2182 done

2183 #case author when '$user' then a.rowid else '---' end as ID,

2184 sql=${DT_SQL:-"select \

2185 a.rowid as ID,\

2186 $cnt\

2187 $scols from $2 a left join\

2188 (select $pk,$eav,

2189 max(case key when 'owner'

2190 then coalesce((select val from user_s us

2191 where us.key='gecos' and us.name=c.val), val) end) as gecos

2192 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

2193 err SQL=`echo "$sql"`

2194 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$</TD>,\1$elink$dvlink</TD>,"

2195 <div class="dumptable">

2196 <table class="b$dt_class">

2197 `sq -header -cmd ".mode $1" $db "$sql"`

2198 </table>

2199 </div>

2200 EOF

2201 }

2202

2203 par2table() (

2204 # copy current parameters of par into destination table

2205 # $1=definition-file

2206 # Using $user and $session

2207 # Return value:

2208 # 0: Stored successfully

2209 # 1: Insufficient fillings

2210 # 2: No permission to modify the record

2211 # 3: Invalid rowid

2212 # 4: SUCCESS to delete

2213 # 5: Stop deletion for lack of confirm check

2214 # 6: Password length too short

2215 # 7: Password mismatch

2216 # 8: Old password incorrect

2217 rowid=`getpar rowid`

2218 err ...........rowid=$rowid

2219 if [ ! -e $1 ]; then

2220 echo "テーブル定義ファイルが見付かりません" | html p

2221 exit 1

2222 fi

2223 tbl=${1%.def}

2224 tbl=${tbl##*/}

2225 if [ -n "$rowid" ]; then # Modify existing entry

2226 if [ x"$tbl" = x"user" ]; then

2227 rowowner=`query "select name from $tbl where rowid=$rowid;"`

2228 elif [ x"$tbl" = x"grp" ]; then

2229 sql="select gname from $tbl where rowid=$rowid;"

2230 ##err p2t:grp:q $sql

2231 isgrpowner $user "`query $sql`" && rowowner=$user

2232 else

2233 rowowner=`query "select owner from $tbl where rowid=$rowid;"`

2234 rowowner=${rowowner:-`query "select author from $tbl

2235 where rowid=$rowid;"`}

2236 fi

2237 ### err rowowner=$rowowner

2238 if [ x"$user" != x"$rowowner" ]; then

2239 echo "他人のレコードはいじれないの" | html p

2240 return 2

2241 elif [ -z "$rowowner" ]; then

2242 echo "指定したレコードはないみたい" | html p

2243 return 3

2244 fi

2245 rm=`getpar rm` cfm=`getpar confirm`

2246 # Editing existent entry

2247 if [ x"$rm" = x"yes" ]; then

2248 if [ x"$rm$cfm" = x"yesyes" ]; then

2249 query "delete from $tbl where rowid=$rowid;"

2250 return 4

2251 else

2252 echo "消去確認のチェックがないので消さなかったの..." | html p

2253 return 5

2254 fi

2255 fi

2256 fi

2257 # XX: Subshelling here is unnecessary 2015-07-05

2258 (ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

2259 if [ -n "$rowid" ]; then

2260 # Update of existing record

2261 for col in `gettblcols $tbl`; do

2262 val=`getparquote $col`

2263 [ -z "$val" ] && continue

2264 err query "update $tbl set $col=$val where rowid=$rowid"

2265 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

2266 sql="update $tbl set $col=$val where rowid=$rowid;"

2267 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

2268 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

2269 ## User name cannot be changed with interface provided with this

2270 ## script. But we offer the trigger to change owner user

2271 ## of blog_s table.

2272 err "select quote($col) from $tbl where rowid=$rowid;"

2273 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

2274 cat<<-EOF | query

2275 BEGIN;

2276 $sql

2277 update blog_s set val=$val

2278 where key='owner' and val=$old;

2279 COMMIT;

2280 EOF

2281 ## XX: DIRTY Hack Ends here

2282 ## We should keep blog's owner as a single column which has

2283 ## foreign key constraint with primary key of grp/user.

2284 else

2285 query "$sql"

2286 fi

2287 done

2288 # Then, set up $pval for further insertion of tbl_s and tbl_m

2289 for col in `gettblpkey $tbl`; do

2290 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

2291 pval="$pval${pval:+, }\"$val\""

2292 done

2293 else

2294 # New entry

2295 # Generate values() for primary keys

2296 for col in `gettblpkey $tbl`; do

2297 # Genuine primary keys for _m and _s

2298 val=`getvalquote $tbl $col`

2299 [ -z "$val" ] && continue

2300 pval="$pval${pval:+, }$val"

2301 done

2302 err pval=$pval

2303 for col in `gettblfkey $tbl`; do

2304 # args for values() to insertion into master table

2305 val=`getvalquote $tbl $col`

2306 [ -z "$val" ] && continue

2307 formaster=$formaster"${formaster:+, }$val"

2308 done

2309 formaster="$pval${formaster:+, }$formaster"

2310 err formaster=$formaster

2311 if [ -z "$formaster" ]; then

2312 echo "項目を全て埋めてください" | html pre

2313 return 1

2314 fi

2315 err "replace into $tbl values($formaster);"

2316 query "replace into $tbl values($formaster);"

2317 ## Insertion to master table, done

2318 fi

2319

2320 for kt in s m; do

2321 tb2=${tbl}_$kt

2322 for col in `gettbl_${kt}_cols $tbl`; do

2323 ptype=`getpartype $col "limit 1"`

2324

2325 # First, check update of existing entries in _m

2326 if [ $kt = m ]; then

2327 # sessID|address.1.22|string|Somewhere-x.y.z

2328 sql=""

2329 err dots from query "select var from par where var like '$col.%';"

2330 for v in `query "select var from par where var like '$col.%';"`; do

2331 # v=address.1.22

2332 st_rowid=${v##*.}

2333 origcol=${v%%.*} # original column derived from

2334 err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

2335 ##case `getpartype $v` in

2336 err CASE `gettbl_coltype $tbl/$origcol` in

2337 err edit flag = `getpar action.$v`

2338 case `getpar action.$v` in

2339 rm)

2340 if [ x`getpar confirm.$v` = x"yes" ]; then

2341 newsql="delete from $tb2"

2342 else

2343 echo "削除確認未チェック" | html p

2344 fi ;;

2345 edit)

2346 case `gettbl_coltype $tbl/$origcol` in

2347 image|document|binary)

2348 file=$tmpd/`getparfilename $v`

2349 err type=file=$file

2350 [ -z "$file" ] && continue

2351 bn=${file##*/}

2352 bin="X'"$(hexize $file)"'"

2353 ct=`file --mime-type $file|cut -d' ' -f2`

2354 type=\"file:$ct\"

2355 newsql="update $tb2 set val='$bn', type=$type, bin=$bin"

2356 cachedir=`getcachedir "$tbl/$rowid"`

2357 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

2358 rm -r $cachedir

2359 ;;

2360 *)

2361 newsql="update $tb2 set val=(select val from par where var \

2362 like '$col.%.$st_rowid')"

2363 ;;

2364 esac

2365 ;;

2366 *) # maybe "keep", do not modify value

2367 continue

2368 ;;

2369 esac

2370 # err newsql=$newsql

2371 sql=$sql$nl"$newsql where rowid=$st_rowid;"

2372 done

2373

2374 if [ x"$bin" = x"NULL" ]; then

2375 err repl:normal sql=`echo $sql`

2376 query "$sql

2377 delete from $tb2 where type='string' and val='';"

2378 err repl:normal done

2379 else

2380 sqlfile="$tmpd/sqlf.$$"

2381 echo "$sql" > $sqlfile

2382 err repl:sqlfile=`ls -lF $sqlfile`

2383 query ".read $sqlfile"

2384 err repl:done

2385 fi

2386 # Rest of kt==m: set multiple mode

2387 nr=`getparcount $col`

2388 else

2389 nr=1 # for kt==s, number of records is 1

2390 fi

2391

2392 i=0

2393 while [ $i -lt $nr ]; do

2394 limit="limit 1 offset $i"

2395 i=$((i+1)) # increase beforehand against continue

2396 val=`getvalquote $tbl $col "$limit"`

2397 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

2398 err $col=$val

2399 bin=NULL

2400 err partype$col=`getpartype $col "$limit"`

2401 case $ptype in

2402 file) file=$tmpd/`getparfilename $col "$limit"`

2403 err parfile-$col=$file

2404 [ -z "$file" ] && continue

2405 bin="X'"$(hexize $file)"'"

2406 ct=`file --mime-type $file|cut -d' ' -f2`

2407 type=\"file:$ct\" ;;

2408 "*"*) continue ;; # foreign table

2409 *) type=\"string\" ;;

2410 esac

2411 case `gettbl_coltype $tbl/$col` in

2412 password) # special care for password

2413 # name={password,pswd1,pswd2}

2414 p1=`getpar pswd1 "$limit"`

2415 if [ -z "$p1" ]; then

2416 continue # SKIP password setting, if p1 is empty

2417 else

2418 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

2419 ## err pswd=$pswd

2420 if pwcheck "$pswd"; then

2421 if [ x"$p1" = x"$p2" ]; then

2422 case "$p1" in

2423 ??????????*) ;;

2424 *) echo "パスワードは10字以上にしてください。" | html p

2425 return 6;;

2426 esac

2427 val="\"`echo $p1|mypwhash`\""

2428 else

2429 echo "2つの新パスワード不一致" | html p

2430 return 7

2431 fi

2432 else

2433 echo "旧パスワード違います" | html p

2434 return 8

2435 fi

2436 fi

2437 ;;

2438 esac

2439 err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

2440 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

2441 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

2442 if [ x"$bin" = x"NULL" ]; then

2443 err Normal-query: `echo $sql`

2444 query "$sql"

2445 else

2446 sqlfile="$tmpd/query.$$"

2447 echo "$sql" > $sqlfile

2448 err sqlfile=`ls -lF $sqlfile`

2449 query ".read $sqlfile"

2450 fi

2451 err p2t done

2452 done

2453 done

2454 done

2455 return 0

2456 err donee)

2457 )

2458 par2table_old() {

2459 # copy current parameters of par into destination table

2460 # $1=dst-table $2=definition-file

2461 # Using $user and $session

2462 rowid=`getpar rowid`

2463 if [ -n "$rowid" ]; then

2464 rm=`getpar rm` cfm=`getpar confirm`

2465 if [ x"$rm$cfm" = x"yesyes" ]; then

2466 sq $db "delete from $1 where rowid=$rowid and owner=\"$user\""

2467 return

2468 fi

2469 fi

2470 cat $2 \

2471 | (cols=""

2472 while IFS=: read prompt name type args; do

2473 [ x"$name" = x"stage" ] && continue

2474 if [ -n "$rowid" ]; then

2475 val=`getpar $name|sed -e 's/\"/\"\"/g'`

2476 sq $db "update $1 set $name=\"$val\" where rowid=$rowid and owner=\"$user\""

2477 else

2478 eav=$eav${eav+,}" max(case var when '$name' then val end)"

2479 fi

2480 done

2481 [ -n "$rowid" ] && return

2482 cond="where sessid='$session' group by sessid"

2483 sq $db "replace into $1 select $eav,\"$user\" from par $cond"

2484 # Think over again about putting $user

2485 )

2486 }

2487 genform() {

2488 # $1 = form definition file

2489 # $2, $3 (optional)= table name and ROWID

2490 # If $GF_VIEWONLY set and nonNull, output values without form

2491 # If $GF_HIDDEN set, use it hidden values

2492 # If $GF_OWNER set, use it as value of name="owner"

2493 # If $GF_STAGE set, use it as value of name="stage"

2494 forms="" hiddens="" rowid=$3

2495 if [ ! -e "$1" ]; then

2496 echo "そのようなデータベースはないようです($2)。" | html p

2497 return

2498 elif [ -n "$2" ]; then

2499 err genform1: "select * from $2 where rowid='$rowid'"

2500 rec=`query "select * from $2 where rowid='$rowid';"`

2501 if [ -z "$rec" ]; then

2502 pk=`gettblpkey $2`

2503 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

2504 err "select rowid from $2 where $pk='$rowid';"

2505 rec=`query "select rowid from $2 where $pk='$rowid';"`

2506 err rec-rowid=$rec

2507 rowid=$rec

2508 rec=$3

2509 fi

2510 if [ -z "$rec" ]; then

2511 echo "そんなレコードはないみたいね..." | html p

2512 return

2513 fi

2514 fi

2515 if [ -z "$GF_VIEWONLY" ]; then

2516 rm='<input id="rm" name="rm" type="checkbox"

2517 value="yes"><label for="rm">このエントリの削除</label>

2518 <span>ほんとうに消しますよ(確認)!

2519 <input name="confirm" type=checkbox value="yes">はい</span>'

2520 fi

2521 # Image Cache dir

2522 err genform: getcache=$2/$rowid

2523 td=`getcachedir "$2/$rowid"`

2524 while IFS=: read prompt name keytype type args; do

2525 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

2526 sp="${args:+ }"

2527 form="" val=""

2528 if [ -n "$rowid" ]; then

2529 # err genform2a: Seeking for "$2.$name, type=$type"

2530 val=`getvalbyid $2 $name $rowid $td|htmlescape`

2531 err genform3a: getvalbyid $2 $name $rowid $td

2532 err genform3b: val="[$val]"

2533 fi

2534 if [ -n "$GF_VIEWONLY" ]; then

2535 is_hidden "$2" "$name" && continue

2536 fi

2537 case "$type" in

2538 text*)

2539 cgiform=cgi_multi_$type

2540 if [ -s $td/$name.count -a -n "$val" ]; then

2541 form=`$cgiform $name $td`

2542 val=$(echo "$val"|

2543 while read fn; do

2544 echo "<tr><td>`cat $td/$fn`</td></tr>$nl"

2545 done)

2546 val="<table>$nl$val$nl</table>"

2547 else

2548 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

2549 err genform: cgi_$type $name $val "$args"

2550 form=`cgi_$type $name "$val" "$args"`

2551 fi

2552 ;;

2553 [Rr][Aa][Dd][Ii][Oo])

2554 fh="<label><input type=\"radio\" name=\"$name\""

2555 form="`echo $args|sed -e \

2556 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

2557 ;;

2558 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

2559 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

2560 ;;

2561 [Ss][Ee][Ll][Ee][Cc][Tt])

2562 fh="<select name=\"$name\">$nl"

2563 form=$(for l in $args; do

2564 echo "<option value=\"${l#*=}\">${l%=*}</option>"

2565 done)

2566 if [ -n "$val" ]; then

2567 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

2568 fi

2569 form="$fh$form</select>"

2570 ;;

2571 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

2572 if [ -s $td/$name.count ]; then

2573 form=`cgi_multi_file $name $td "$args"`

2574 if [ -n "$val" ]; then

2575 hrfb="$myname?showattc+$2_m"

2576 val=$(echo "$val" \

2577 | while read fn; do

2578 data=`percenthex $td/$fn`

2579 #ct=`cat $td/$fn.content-type`

2580 ct=`file --mime-type $td/$fn|cut -d' ' -f2`

2581 ri=`cat $td/$fn.rowid`

2582 ## err fn=$fn, name=$name, ri=$ri; ls -lF $td 1>&3

2583 #imgsrc="<img src=\"data:$ct,$data\">"

2584 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

2585 iconhref $td/$fn "$hrfb+$ri" ""

2586 done)

2587 fi

2588 else

2589 form="<input type=\"file\" name=\"$name\" $args>"

2590 if [ -n "$val" ]; then

2591 imgs=$(echo "$val"\

2592 |while read fn;do

2593 data=`percenthex $td/$fn`

2594 echo "<img src=\"data:image/png,$data\">$fn<br>"

2595 done)

2596 form=$form"<br>$imgs"

2597 val=$imgs # 2015-06-15

2598 else

2599 form="<input type=\"file\" name=\"$name\" $args>"

2600 fi

2601 fi

2602 ;;

2603 [Hh][Ii][Dd][Dd][Ee][Nn])

2604 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

2605 args="value=\"$GF_STAGE\""

2606 fi

2607 form="<input type=\"hidden\" name=\"$name\" $args>"

2608 prompt='' # Remove prompt

2609 ;;

2610 [Aa][Uu][Tt][Hh][Oo][Rr])

2611 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

2612 prompt="" ;;

2613 [Oo][Ww][Nn][Ee][Rr])

2614 val=${GF_OWNER:-$val}

2615 val=${val:-$user}

2616 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

2617 prompt="" ;;

2618 [Uu][Ss][Ee][Rr])

2619 # XXX: is null $user ok?

2620 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

2621 [ -n "$GF_VIEWONLY" ] && continue

2622 form="$user"

2623 ;;

2624 [Pp]assword)

2625 [ -n "$GF_VIEWONLY" ] && continue

2626 form="`cgi_passwd`"

2627 val=""

2628 ;;

2629 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

2630 if [ -z "$rowid" ]; then

2631 val=$((($(date +%s)-1433084400)/10))c$$

2632 fi

2633 ## form="<input type=\"hidden\" name=\"serial\" value=\"$val\">"

2634 ## 2015-07-31

2635 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

2636 prompt="" ;;

2637 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

2638 prompt=""

2639 ;;

2640 parent|path|blog*)

2641 prompt=""

2642 ;;

2643 "*"*)

2644 tail=$tail"``"

2645 continue ;;

2646 esac

2647 if [ -n "$prompt" ]; then

2648 if [ -n "${GF_VIEWONLY}" ]; then

2649 form=$val

2650 else

2651 :

2652 fi

2653 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

2654 else

2655 hiddens=$hiddens$nl"$form"

2656 fi

2657 done < $1

2658 # enctype="multipart/form-data"

2659 cat<<EOF

2660 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

2661 ${rowid:+$rm}

2662 <table class="b $2">

2663 $forms

2664 </table>$hiddens

2665 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

2666 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

2667 EOF

2668 if [ -z $GF_VIEWONLY ]; then

2669 cat<<EOF

2670 <input type="submit" name="sub" value="OK">

2671 <input type="reset" name="res" value="Reset">

2672 EOF

2673 fi

2674 cat<<EOF

2675 </form>

2676 $tail

2677 EOF

2678 }

2679 edittable() {

2680 # $1=form-def $2=table $3 rowid

2681 genform "$@"

2682 }

2683 viewtable() {

2684 GF_VIEWONLY=1 genform "$@"

2685 }

2686 showattc() {

2687 # $1=table_m $2=rowid

2688 err \$1=$1 \$2=$2

2689 if ! isfilereadable $user $1 $2; then

2690 contenttype; echo

2691 echo "このファイルは管理者のみしか見られません" | html p

2692 putfooter; exit

2693 fi

2694 idir=`umask 002; mktempd` || exit 1

2695 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

2696 bin=$idir/$myname-$$.bin

2697 sql="select quote(bin) from $1 where rowid='$2';"

2698 err showattc: sql: $sql

2699 sq $db "$sql" | unhexize > $bin

2700 tv=`query "select type,val from $1 where rowid='$2';"`

2701 type=${tv%\|*} fn=${tv#*\|}

2702 err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

2703 ct=${type#file:}

2704 case $ct in # all text/* changed to text/plain

2705 text/*)

2706 charset=`nkf -g $bin|cut -d' ' -f1`

2707 case $charset in

2708 ASCII*) charset="" ;;

2709 esac

2710 ct="text/plain${charset:+; charset=$charset}"

2711 ;;

2712 esac

2713 contenttype "$ct"

2714 echo "Content-Disposition: filename=\"$fn\""

2715 echo "Content-Length: " `cat $bin | wc -c`; echo

2716 #echo "Content-Type: " ${type#file:}; echo

2717 cat $bin

2718 }

2719 #

2720 # Some default stupid handler on CGI values

2721 #

2722 default_storedb() {

2723 # ARG: $1=table-def-file

2724 # RET: $tbl=table-name, $col=mail-column, $cols=columns

2725 tbl=`basename $1`

2726 tbl=${tbl%.def}

2727 cols="`grep :text $1|cut -d: -f2`"

2728 col=`echo "$cols"|head -1`

2729 vcol=`getpar $col`

2730 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

2731 if [ -n "$vcol" ]; then

2732 par2table $1

2733 else

2734 return 2 # No insertion occurred

2735 fi

2736 }

2737

2738 default_view() { # $1=def-file

2739 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

2740 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

2741 default_storedb "$@"

2742 query "select rowid from $tbl order by rowid desc;" \

2743 | while read rowid; do

2744 viewtable $1 $tbl $rowid

2745 done | m4 -D_TITLE_="$tbl" \

2746 -D_FORM_="`genform $1`" \

2747 -D_DUMPTABLE_="syscmd(cat)" \

2748 $layout/html.m4.html $layout/form+dump.m4.html

2749 }

2750 default_viewtext() { # $1=def-file

2751 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

2752 default_storedb "$@"

2753 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

2754 | m4 -D_TITLE_="$tbl" \

2755 -D_FORM_="`genform $1`" \

2756 -D_DUMPTABLE_="syscmd(cat)" \

2757 $layout/html.m4.html $layout/form+dump.m4.html

2758 }

2759 default_smail() {

2760 default_storedb "$@"

2761 if [ $? -eq 2 ]; then

2762 m4 -D_TITLE_="入力" \

2763 -D_FORM_="`genform $1`" \

2764 -D_DUMPTABLE_="" \

2765 $layout/html.m4.html $layout/form+dump.m4.html

2766 return

2767 fi

2768 cond=""

2769 for pk in `gettblpkey $tbl`; do

2770 pv=$(sqlquote $(getpar $pk))

2771 cond="$cond${cond:+ and }$pk=$pv"

2772 done

2773 sql="select rowid from $tbl where $cond;"

2774 rowid=`query "$sql"`

2775 err smail1 - "$sql" "-> rowid=$rowid"

2776

2777 while IFS=: read prompt name keytype type args; do # Read from $1

2778 val=`getpar $name`

2779 if [ -n "$val" ]; then

2780 text="$text

2781 $prompt

2782 $name=$val

2783 ---------------------------------------------------------"

2784 fi

2785 case "$type" in

2786 image|document|file)

2787 fn="`getvalbyid $tbl $name $rowid $tmpd`"

2788 fns=$(echo "$fn"|while read fn; do

2789 err mv $tmpd/$fn.orig $tmpd/$fn

2790 mv $tmpd/$fn.orig $tmpd/$fn

2791 rm $tmpd/$fn.rowid # Remove cache flag

2792 err "`ls $tmpd/$fn`"

2793 echo $fn

2794 done)

2795 files="$files $fns"

2796 ;;

2797 esac

2798 done < $1

2799 err FILES=$files "`ls -lF $tmpd`"

2800 subj="from ${REMOTE_ADDR}"

2801 (echo "$url"

2802 echo "への書き込みがありました。"

2803 echo "------"

2804 echo "$text"

2805 ) | (cd $tmpd &&

2806 err LS="`ls -lF`" &&

2807 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

2808 m4 -D_TITLE_="入力完了" $layout/html.m4.html

2809 echo "以下の内容で送信しました。" | html p

2810 viewtable $1 $tbl \

2811 `query "select rowid from $tbl order by rowid desc limit 1;"`

2812 echo "戻る" | html a "href=\"?\""

2813 }