s4: 71b9f2af4da1 s4-funcs.sh

s4

view s4-funcs.sh @ 215:71b9f2af4da1

Allow '=' sign in URL strings

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 10 Jun 2016 19:18:09 +0859
parents	e4dd3cf443b0
children	0698f0aa118f

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

1477 for="mre">最近書き込んだ場所一覧</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 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

1958 query<<EOF

1959 CREATE TEMPORARY VIEW writeusers AS

1960 SELECT DISTINCT author FROM article

1961 WHERE id in (

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

1963 );

1964 CREATE TEMPORARY VIEW movablegroups AS

1965 SELECT g.rowid growid , g.gname

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

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

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

1969 WHERE (SELECT author FROM writeusers

1970 EXCEPT

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

1972 IS NULL;

1973 $2

1974 EOF

1975 }

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

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

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

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

1980 return

1981 fi

1982 owner=`getvalbyid blog owner $rowid`

1983 title=`getvalbyid blog title $rowid`

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

1985 | _m4 -D_TITLE_="修正" \

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

1987 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

1990 # Move to group

1991 if isuser "$owner"; then

1992 crview4article $rowid

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

1994 err N=$n

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

1996 err ROWID=$rowid

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

1998 cat<<-EOF

1999 <div class="fold">

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

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

2002 <div>

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

2004 移動先グループ:

2005 <select name="mv2grp">

2006 EOF

2007 query ".mode html"

2008 query<<-EOF |

2009 $sql

2010 .mode list

2011 EOF

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

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

2014 cat<<-EOF

2015 </select>

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

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

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

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

2020 `cgi_hidden blogrowid $rowid`

2021 `cgi_submit 移動`

2022 `cgi_reset Reset`

2023 </form>

2024 </div>

2025 </div>

2026 EOF

2027 fi

2028 fi

2029 }

2030 mvart() {

2031 blogrowid=`getpar blogrowid`

2032 mv2grp=`getpar mv2grp`

2033 cfm=`getpar cfm`

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

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

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

2037 . ./s4-blog.sh

2038 if [ -z "$blogrowid" -o -z "$mv2grp" ]; then

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

2040 return

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

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

2043 else # OK to go

2044 crview4article $blogrowid

2045 ########## TRANSACTION BEGIN

2046 query "BEGIN;"

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

2048 err Nwriteuser=$n

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

2050 query<<-EOF

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

2052 WHERE key='owner'

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

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

2055 EOF

2056 fi

2057 query "END;"

2058 ########## TRANSACTION END

2059 fi

2060 blog_reply $blogrowid

2061 echo yes | html p

2062 }

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

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

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

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

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

2068 return

2069 fi

2070 owner=`getvalbyid blog owner $blogrowid`

2071 title=`getvalbyid blog title $blogrowid`

2072 author=`getvalbyid article author $rowid`

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

2074 if isgrpowner $user $owner; then

2075 : EDIT OK

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

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

2078 return

2079 fi

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

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

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

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

2084 > $tmpout

2085 rm -f /tmp/editart.out

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

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

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

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

2090 -D_BLOGS_= -D_DUMPTABLE_= \

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

2092 }

2093 send2mem() {

2094 rowid=`getpar grp`

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

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

2097 return

2098 fi

2099 message=`getpar message`

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

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

2102 return

2103 fi

2104 grp=`getgroupbyid $rowid`

2105 members=`collectemail $grp`

2106 # smail rcpt subj (file)

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

2108 $urlbase?grp+$rowid

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

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

2111

2112 $message

2113 EOF

2114 cat<<EOF

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

2116 <pre>

2117 $members

2118 </pre>

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

2120 EOF

2121 }

2122 joingrpadmit() {

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

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

2125 echo "bye bye" | html p; return

2126 fi

2127 t_usr=`session=$2 getpar user`

2128 t_grp=`session=$2 getpar group`

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

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

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

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

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

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

2135 return

2136 fi

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

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

2139 fi

2140 case $1 in

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

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

2143 *)

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

2145 return ;;

2146 esac

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

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

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

2150 body="グループ $t_grp

2151 に

2152 $t_usr

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

2154 しました。"

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

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

2157 echo "$body" | html p

2158 }

2159

2160 joingrprequest() {

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

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

2163 addsession $jss +${memoplimitdays}days

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

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

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

2167 $url

2168 グループ $1

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

2170

2171 承認する:

2172 $urlbase?joingrpadmit+yes+$jss

2173

2174 白紙に戻す:

2175 $urlbase?joingrpadmit+no+$jss

2176 EOF

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

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

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

2180 }

2181 joingrp() {

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

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

2184 isgrpowner "$user" "$1" && isowner="yes" || isowner=""

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

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

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

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

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

2190 return

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

2192 : # Do not pursue those who leave

2193 else # adding user is $user itself

2194 case `getgroupattr $1 regmode` in

2195 moderated)

2196 joingrprequest "$@" # Request only

2197 return

2198 ;;

2199 *)

2200 ;;

2201 esac

2202 fi

2203 qgname=`sqlquote $1`

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

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

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

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

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

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

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

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

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

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

2214 # Coming here means newly created group

2215 sql="select case\

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

2217 then (select user from grp_mem\

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

2219 else '' end;"

2220 err NewGrpChk: $sql

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

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

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

2224 fi

2225 fi

2226 else

2227 echo $msg

2228 fi

2229 else

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

2231 fi

2232 else

2233 query "delete from grp_mem $cond;

2234 delete from grp_mem_s $cond;

2235 delete from grp_mem_m $cond;"

2236 fi

2237 }

2238 grp_reg_adm() {

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

2240 grid=$1

2241 grp=`getgroupbyid "$1"`

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

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

2244 fi

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

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

2247 fi

2248 shift

2249 for urid; do

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

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

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

2253 fi

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

2255 err ismember $newadm $grp

2256 if ismember $newadm $grp; then

2257 # OK, go ahead

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

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

2260 # confirm insertion

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

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

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

2264 else

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

2266 fi

2267 fi

2268 showgroup $grid

2269 done

2270 }

2271 dumptable() {

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

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

2274 # $DT_VIEW sets link

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

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

2277 VIEW=${DT_VIEW-replyblog}

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

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

2280 fi

2281 # $DT_CHLD=ChildTable:BindColumn

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

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

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

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

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

2287 and val > coalesce((select time from acclog where \

2288 user='$user' and tbl='$2' and tblrowid=a.rowid),\

2289 '1970-01-01'))"

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

2291 dt_class=" td2r td3r"

2292 fi

2293 # Construct join expression

2294 eav="" scols=""

2295 pk=`gettblpkey $2`

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

2297 substr=${substr:-val}

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

2299 case $col in

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

2301 continue ;; # built-in column name

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

2303 *) as=${col} ;;

2304 esac

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

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

2307 done

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

2309 sql=${DT_SQL:-"select \

2310 a.rowid as LINK,\

2311 $cnt\

2312 $scols from $2 a left join\

2313 (select $pk,$eav,

2314 max(case key when 'owner'

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

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

2317 err SQL=`echo "$sql"`

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

2319 <div>

2320 <div class="dumptable">

2321 <table class="b$dt_class">

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

2323 </table>

2324 </div>

2325 </div>

2326 EOF

2327 }

2328

2329 par2table() (

2330 # copy current parameters of par into destination table

2331 # $1=definition-file

2332 # Using $user and $session

2333 # Return value:

2334 # 0: Stored successfully

2335 # 1: Insufficient fillings

2336 # 2: No permission to modify the record

2337 # 3: Invalid rowid

2338 # 4: SUCCESS to delete

2339 # 5: Stop deletion for lack of confirm check

2340 # 6: Password length too short

2341 # 7: Password mismatch

2342 # 8: Old password incorrect

2343 rowid=`getpar rowid`

2344 err ...........rowid=$rowid

2345 if [ ! -e $1 ]; then

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

2347 exit 1

2348 fi

2349 tbl=${1%.def}

2350 tbl=${tbl##*/}

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

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

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

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

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

2356 ##err p2t:grp:q $sql

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

2358 else

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

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

2361 where rowid=$rowid;"`}

2362 fi

2363 ### err rowowner=$rowowner

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

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

2366 return 2

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

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

2369 return 3

2370 fi

2371 rm=`getpar rm` cfm=`getpar confirm`

2372 # Editing existent entry

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

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

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

2376 return 4

2377 else

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

2379 return 5

2380 fi

2381 fi

2382 fi

2383 # XX: Subshelling here is unnecessary 2015-07-05

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

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

2386 # Update of existing record

2387 for col in `gettblcols $tbl`; do

2388 val=`getparquote $col`

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

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

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

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

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

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

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

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

2397 ## of blog_s table.

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

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

2400 cat<<-EOF | query

2401 BEGIN;

2402 $sql

2403 update blog_s set val=$val

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

2405 COMMIT;

2406 EOF

2407 ## XX: DIRTY Hack Ends here

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

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

2410 else

2411 query "$sql"

2412 fi

2413 done

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

2415 for col in `gettblpkey $tbl`; do

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

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

2418 done

2419 else

2420 # New entry

2421 # Generate values() for primary keys

2422 for col in `gettblpkey $tbl`; do

2423 # Genuine primary keys for _m and _s

2424 val=`getvalquote $tbl $col`

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

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

2427 done

2428 err pval=$pval

2429 for col in `gettblfkey $tbl`; do

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

2431 val=`getvalquote $tbl $col`

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

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

2434 done

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

2436 err formaster=$formaster

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

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

2439 return 1

2440 fi

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

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

2443 ## Insertion to master table, done

2444 fi

2445

2446 for kt in s m; do

2447 tb2=${tbl}_$kt

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

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

2450

2451 # First, check update of existing entries in _m

2452 if [ $kt = m ]; then

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

2454 sql=""

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

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

2457 # v=address.1.22

2458 st_rowid=${v##*.}

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

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

2461 ##case `getpartype $v` in

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

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

2464 case `getpar action.$v` in

2465 rm)

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

2467 newsql="delete from $tb2"

2468 else

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

2470 fi ;;

2471 edit)

2472 case `gettbl_coltype $tbl/$origcol` in

2473 image|document|binary)

2474 file=$tmpd/`getparfilename $v`

2475 err type=file=$file

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

2477 bn=${file##*/}

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

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

2480 type=\"file:$ct\"

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

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

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

2484 rm -r $cachedir

2485 ;;

2486 *)

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

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

2489 ;;

2490 esac

2491 ;;

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

2493 continue

2494 ;;

2495 esac

2496 # err newsql=$newsql

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

2498 done

2499

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

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

2502 query "$sql

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

2504 err repl:normal done

2505 else

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

2507 echo "$sql" > $sqlfile

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

2509 query ".read $sqlfile"

2510 err repl:done

2511 fi

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

2513 nr=`getparcount $col`

2514 else

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

2516 fi

2517

2518 i=0

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

2520 limit="limit 1 offset $i"

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

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

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

2524 err $col=$val

2525 bin=NULL

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

2527 case $ptype in

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

2529 err parfile-$col=$file

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

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

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

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

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

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

2536 esac

2537 case `gettbl_coltype $tbl/$col` in

2538 password) # special care for password

2539 # name={password,pswd1,pswd2}

2540 p1=`getpar pswd1 "$limit"`

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

2542 continue # SKIP password setting, if p1 is empty

2543 else

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

2545 ## err pswd=$pswd

2546 if pwcheck "$pswd"; then

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

2548 case "$p1" in

2549 ??????????*) ;;

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

2551 return 6;;

2552 esac

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

2554 else

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

2556 return 7

2557 fi

2558 else

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

2560 return 8

2561 fi

2562 fi

2563 ;;

2564 esac

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

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

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

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

2569 err Normal-query: `echo $sql`

2570 query "$sql"

2571 else

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

2573 echo "$sql" > $sqlfile

2574 err sqlfile=`ls -lF $sqlfile`

2575 query ".read $sqlfile"

2576 fi

2577 err p2t done

2578 done

2579 done

2580 done

2581 return 0

2582 err donee)

2583 )

2584 par2table_old() {

2585 # copy current parameters of par into destination table

2586 # $1=dst-table $2=definition-file

2587 # Using $user and $session

2588 rowid=`getpar rowid`

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

2590 rm=`getpar rm` cfm=`getpar confirm`

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

2592 sq $db "delete from $1 where rowid=$rowid and owner=\"$user\""

2593 return

2594 fi

2595 fi

2596 cat $2 \

2597 | (cols=""

2598 while IFS=: read prompt name type args; do

2599 [ x"$name" = x"stage" ] && continue

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

2601 val=`getpar $name|sed -e 's/\"/\"\"/g'`

2602 sq $db "update $1 set $name=\"$val\" where rowid=$rowid and owner=\"$user\""

2603 else

2604 eav=$eav${eav+,}" max(case var when '$name' then val end)"

2605 fi

2606 done

2607 [ -n "$rowid" ] && return

2608 cond="where sessid='$session' group by sessid"

2609 sq $db "replace into $1 select $eav,\"$user\" from par $cond"

2610 # Think over again about putting $user

2611 )

2612 }

2613 genform() {

2614 # $1 = form definition file

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

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

2617 # If $GF_HIDDEN set, use it hidden values

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

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

2620 forms="" hiddens="" rowid=$3

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

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

2623 return

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

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

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

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

2628 pk=`gettblpkey $2`

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

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

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

2632 err rec-rowid=$rec

2633 rowid=$rec

2634 rec=$3

2635 fi

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

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

2638 return

2639 fi

2640 fi

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

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

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

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

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

2646 fi

2647 # Image Cache dir

2648 err genform: getcache=$2/$rowid

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

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

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

2652 sp="${args:+ }"

2653 form="" val=""

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

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

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

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

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

2659 fi

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

2661 is_hidden "$2" "$name" && continue

2662 fi

2663 case "$type" in

2664 text*)

2665 cgiform=cgi_multi_$type

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

2667 form=`$cgiform $name $td`

2668 val=$(echo "$val"|

2669 while read fn; do

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

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

2672 done)

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

2674 else

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

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

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

2678 fi

2679 ;;

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

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

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

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

2684 ;;

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

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

2687 ;;

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

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

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

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

2692 done)

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

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

2695 fi

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

2697 ;;

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

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

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

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

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

2703 val=$(echo "$val" \

2704 | while read fn; do

2705 data=`percenthex $td/$fn`

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

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

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

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

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

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

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

2713 done)

2714 fi

2715 else

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

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

2718 imgs=$(echo "$val"\

2719 |while read fn;do

2720 data=`percenthex $td/$fn`

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

2722 done)

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

2724 val=$imgs # 2015-06-15

2725 else

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

2727 fi

2728 fi

2729 ;;

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

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

2732 args="value=\"$GF_STAGE\""

2733 fi

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

2735 prompt='' # Remove prompt

2736 ;;

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

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

2739 prompt="" ;;

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

2741 val=${GF_OWNER:-$val}

2742 val=${val:-$user}

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

2744 prompt="" ;;

2745 [Uu][Ss][Ee][Rr])

2746 # XXX: is null $user ok?

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

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

2749 form="$user"

2750 ;;

2751 [Pp]assword)

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

2753 form="`cgi_passwd`"

2754 val=""

2755 ;;

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

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

2758 val=$((($(date +%s)-1433084400)/10))c$$

2759 fi

2760 ## form="<input type=\"hidden\" name=\"serial\" value=\"$val\">"

2761 ## 2015-07-31

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

2763 prompt="" ;;

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

2765 prompt=""

2766 ;;

2767 parent|path|blog*)

2768 prompt=""

2769 ;;

2770 "*"*)

2771 tail=$tail"``"

2772 continue ;;

2773 esac

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

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

2776 form=$val

2777 else

2778 :

2779 fi

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

2781 else

2782 hiddens=$hiddens$nl"$form"

2783 fi

2784 done < $1

2785 # enctype="multipart/form-data"

2786 cat<<EOF

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

2788 ${rowid:+$rm}

2789 <table class="b $2">

2790 $forms

2791 </table>$hiddens

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

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

2794 EOF

2795 if [ -z $GF_VIEWONLY ]; then

2796 cat<<EOF

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

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

2799 EOF

2800 fi

2801 cat<<EOF

2802 </form>

2803 $tail

2804 EOF

2805 }

2806 edittable() {

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

2808 genform "$@"

2809 }

2810 viewtable() {

2811 GF_VIEWONLY=1 genform "$@"

2812 }

2813 showattc() {

2814 # $1=table_m $2=rowid

2815 err \$1=$1 \$2=$2

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

2817 contenttype; echo

2818 echo "このファイルは管理者のみしか見られません" | html p

2819 putfooter; exit

2820 fi

2821 idir=`umask 002; mktempd` || exit 1

2822 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

2823 bin=$idir/$myname-$$.bin

2824 sql="select quote(bin) from $1 where rowid='$2';"

2825 err showattc: sql: $sql

2826 sq $db "$sql" | unhexize > $bin

2827 tv=`query "select type,val from $1 where rowid='$2';"`

2828 type=${tv%\|*} fn=${tv#*\|}

2829 err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

2830 ct=${type#file:}

2831 case $ct in # all text/* changed to text/plain

2832 text/*)

2833 charset=`nkf -g $bin|cut -d' ' -f1`

2834 case $charset in

2835 ASCII*) charset="" ;;

2836 esac

2837 ct="text/plain${charset:+; charset=$charset}"

2838 ;;

2839 esac

2840 contenttype "$ct"

2841 echo "Content-Disposition: filename=\"$fn\""

2842 echo "Content-Length: " `cat $bin | wc -c`; echo

2843 #echo "Content-Type: " ${type#file:}; echo

2844 cat $bin

2845 }

2846 #

2847 # Some default stupid handler on CGI values

2848 #

2849 default_storedb() {

2850 # ARG: $1=table-def-file

2851 # RET: $tbl=table-name, $col=mail-column, $cols=columns

2852 tbl=`basename $1`

2853 tbl=${tbl%.def}

2854 cols="`grep :text $1|cut -d: -f2`"

2855 col=`echo "$cols"|head -1`

2856 vcol=`getpar $col`

2857 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

2858 if [ -n "$vcol" ]; then

2859 par2table $1

2860 else

2861 return 2 # No insertion occurred

2862 fi

2863 }

2864

2865 default_view() { # $1=def-file

2866 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

2867 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

2868 default_storedb "$@"

2869 query "select rowid from $tbl order by rowid desc;" \

2870 | while read rowid; do

2871 viewtable $1 $tbl $rowid

2872 done | _m4 -D_TITLE_="$tbl" \

2873 -D_FORM_="`genform $1`" \

2874 -D_DUMPTABLE_="syscmd(cat)" \

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

2876 }

2877 default_viewtext() { # $1=def-file

2878 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

2879 default_storedb "$@"

2880 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

2881 | _m4 -D_TITLE_="$tbl" \

2882 -D_FORM_="`genform $1`" \

2883 -D_DUMPTABLE_="syscmd(cat)" \

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

2885 }

2886 default_smail() {

2887 default_storedb "$@"

2888 if [ $? -eq 2 ]; then

2889 _m4 -D_TITLE_="入力" \

2890 -D_FORM_="`genform $1`" \

2891 -D_DUMPTABLE_="" \

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

2893 return

2894 fi

2895 cond=""

2896 for pk in `gettblpkey $tbl`; do

2897 pv=$(sqlquote $(getpar $pk))

2898 cond="$cond${cond:+ and }$pk=$pv"

2899 done

2900 sql="select rowid from $tbl where $cond;"

2901 rowid=`query "$sql"`

2902 err smail1 - "$sql" "-> rowid=$rowid"

2903

2904 while IFS=: read prompt name keytype type args; do # Read from $1

2905 val=`getpar $name`

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

2907 text="$text

2908 $prompt

2909 $name=$val

2910 ---------------------------------------------------------"

2911 fi

2912 case "$type" in

2913 image|document|file)

2914 fn="`getvalbyid $tbl $name $rowid $tmpd`"

2915 fns=$(echo "$fn"|while read fn; do

2916 err mv $tmpd/$fn.orig $tmpd/$fn

2917 mv $tmpd/$fn.orig $tmpd/$fn

2918 rm $tmpd/$fn.rowid # Remove cache flag

2919 err "`ls $tmpd/$fn`"

2920 echo $fn

2921 done)

2922 files="$files $fns"

2923 ;;

2924 esac

2925 done < $1

2926 err FILES=$files "`ls -lF $tmpd`"

2927 subj="from ${REMOTE_ADDR}"

2928 (echo "$url"

2929 echo "への書き込みがありました。"

2930 echo "------"

2931 echo "$text"

2932 ) | (cd $tmpd &&

2933 err LS="`ls -lF`" &&

2934 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

2935 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

2936 echo "以下の内容で送信しました。" | html p

2937 viewtable $1 $tbl \

2938 `query "select rowid from $tbl order by rowid desc limit 1;"`

2939 echo "戻る" | html a "href=\"?\""

2940 }