s4: fcfed35ca6d8 s4-funcs.sh

s4

view s4-funcs.sh @ 181:fcfed35ca6d8

Gecos column of NEWS table switched to gecos instead of id

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 26 Feb 2016 23:12:16 +0859
parents	59bd085848ec
children	5ba50e0850c9

line source

1 #!/bin/sh

2 # Here's global variable table. Do not use this names.

3 # $HGid$

5 [ -f s4-config.sh ] && . ./s4-config.sh

7 myname=`basename ${SCRIPT_NAME:-$0}`

8 mydir=`dirname ${SCRIPT_FILENAME:-$0}`

9 myargs="$@"

10 PATH=/usr/local/sqlite3/bin:/usr/local/vim7/bin:/usr/iekei/ImageMagick/bin:/usr/local/ImageMagick/bin:$PATH

11 tmpdir=${TMPDIR:-tmp}

12 dbdir=${DBDIR:-db}

13 tmpfiles=""

14 db=${DB:-$dbdir/cgi.sq3}

15 admin=${ADMIN:-hostmaster@example.org}

16 templ=${TEMPL:-templ}

17 layout=${LAYOUT:-$templ/default}

18 formdir=${FORMDIR:-$templ/form}

19 imgdir=${IMGDIR:-img}

20 url=${URL:-"${REQUEST_SCHEME:-http${HTTPS:+s}}://$HTTP_HOST$REQUEST_URI"}

21 urlbase=${url%%\?*}

22 msgdir=$templ/msg

23 timeout="+2 days"

24 memoplimitdays="7"

25 dumpcollen=20

26 #thumbxy=120x120

27 thumbxy=96x96

28 maximagexy=1600x1600

29 ### maximagexy=400x400

30 tconfs=""

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

32 conftbl=_tblconf

33 nl="

34 "

35 . ./s4-cgi.sh

37 : <<EOF

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

40 レポート提出管理

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

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

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

45 Web

46 締切設定

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

49 ○自分の提出物リスト

51 7/19 ○設置

52 ○一斉送信

53 ○getparfilename の tmpd の扱い

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

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

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

57 ○添付ファイル回収

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

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

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

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

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

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

65 ○レポート提出モード

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

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

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

71 7/9 ○管理者の追加

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

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

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

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

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

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

80 ○グループ検索

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

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

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

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

89 ○6/1 par2tableを triplex 対応に

90 select "yuuji@gentei.org",var,"text",NULL,val from par where var in (select col from _tblconf where tbl="/user" and keytype in ('p', 's'));

91 →とすると一気に

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

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

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

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

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

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

100 ## 1. passwd

101 ## 2. grp

102 ## 3. grp_mem

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

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

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

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

107

108 ## ● listの定義:

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

110 ## ● hashの定義:

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

112

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

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

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

116 ## csv2sq3 で .csv.sq3 の Makefile

117

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

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

120 ## type := root | comment

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

122 ## unique(id, filename)

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

124

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

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

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

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

129 ## できたー!

130 ## with recursive allmem as (select * from grp_mem where gname='bar' union all select grp_mem.* from grp_mem,allmem where allmem.name=grp_mem.gname) select * from allmem where type='u';

131 ↓

132 ↓以下に変更

133 with recursive allmem as

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

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

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

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

138

139

140 with recursive allmem as

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

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

143 where allmem.val=grp_m.gname)

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

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

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

147

148

149 ## triggerもできた。

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

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

152

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

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

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

156 ## グループ管理

157 ## 個人情報管理

158 ##

159

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

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

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

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

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

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

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

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

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

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

170

171 ## ログテーブル

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

173

174

175

176 ■表設計

177 * 3つの表に分散管理

178 id格納表 + hash表 + list表

179 * *_s *_m

180

181

182

183 user, user_map, user_col

184

185 ■抽象エントリタイプ

186 * user

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

188 * group

189 権限判定に利用

190 * serial

191 自動idとして機能

192 * password

193 入力 type=passwordで入力

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

195 * session

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

197 * text

198 入力 type=text

199 * textarea

200 入力 textarea

201 * image|document

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

203 * owner

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

205 * gowner

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

207 * timestamp

208 datetime()

209 * parent

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

211 * path

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

213

214 格納タイプ

215 * list

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

217 * hash

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

219

220 オブジェクトタイプ

221 * entry

222 id, title, owner

223 * textpart

224 id, parentID, text

225 * binarypart

226 id, parentID, contenttype, filename, content

227 * content

228 hash(textpart), list(binarypart)

229 * topic

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

231 * reply

232 id, parentID, content

233 * blog

234 list(entry)

235 blog = [topic, list(reply)]

236

237

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

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

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

241 "author" => "taro",

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

243 "parent" => "/",

244 "path" => "/1",

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

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

247 {"serial" => 2,

248 "author" => "hanako",

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

250 "parent" => "/",

251 "path" => "/2",

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

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

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

255

256

257 user:=

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

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

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

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

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

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

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

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

266

267 変換表

268 /user/email=m

269

270 blog:=

271 シリアル:id:p:serial

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

273 所有者:owner:s:owner:

274 時刻:ctime:s:stamp:

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

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

277

278 article:=

279 シリアル:id:p:serial

280 筆者:author:s:owner

281 時刻:ctime:s:stamp:

282 参照元:parent:s:parent:

283 パス:path:s:path:

284 本文:text:s:textarea:

285 画像:image:m:image:

286

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

288

289

290 EOF

291

292 sq() {

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

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

295 }

296 dbsetup() {

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

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

299 sqi=$tmpdir/sqi.$$

300 sqo=$tmpdir/sqo.$$

301 mkfifo $sqi $sqo

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

303 sq $db < $sqi &

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

305 exec 2>> $tmpdir/error.out

306 exec 3>> $tmpdir/debug.out

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

308 rm $sqi

309 }

310 cleanup() {

311 echo .quit >&5

312 kill $sq3pid

313 kill $sq3pid

314 rm -f $sqo $sqi

315 rm -rf $tmpfiles

316 }

317 query() {

318 echo ".once $sqo" >&5

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

320 cat

321 else

322 echo "$@"

323 fi >&5

324 cat $sqo

325 }

326

327 ismember() {

328 # $1=user, $2=group

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

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

331 }

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

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

334 }

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

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

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

338 }

339 isgrpowner() (

340 # $1=user, $2=group

341 gn=`sqlquote "$2"`

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

343 err isgrpowner: $sql

344 test -n "`query $sql`"

345 )

346 getgroupadminmails() {

347 # $1=group

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

349 email4group "$1" "$i" ;

350 done

351 }

352 getgroupadmins() { # $1=group

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

354 qgrp=`sqlquote "$1"`

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

356 }

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

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

359 getvalbyid grp $2 \

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

361 }

362 getgroupbyid() {

363 # $1=id|gname

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

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

366 # err ggbyid: `echo $sql`

367 query $sql

368 }

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

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

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

372

373 # Return true when anonymous mode

374 [ "$anonymousmode" ] && return 0

375 # case `getvalbyid blog mode $2` in

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

377 # *closed*)

378 # owner=`getvalbyid blog owner $2`

379 # if isgrp $owner; then

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

381 # elif isuser $owner; then

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

383 # fi

384 # esac

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

386 sql="with getblog as (\

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

388 select blogid from article where id in\

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

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

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

392 select case\

393 when (select author from article where\

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

395 then 'author'\

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

397 then 'open'\

398 when (select val from getmode) is null \

399 then 'open'

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

401 then (select user from grp_adm where \

402 gname=(select val from getowner) and \

403 user='$1')\

404 when (select author from article where\

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

406 then 'user+author'

407 else '' end;"

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

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

410 result=`query "$sql"`

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

412 return 2

413 }

414 linkhome() {

415 # $1=UserOrGroup

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

417 if isuser $1; then

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

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

420 else

421 echo -n "grp+$1"

422 fi

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

424 }

425 acclog() (

426 # $1=table, $2=rowid

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

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

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

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

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

432 fi

433 )

434 gecos() (

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

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

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

438 # then coalesce(

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

440 # $u)

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

442 # then coalesce(

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

444 # $u)

445 # else $u

446 # end;"

447 #query "$sql"

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

449 )

450 setpar() {

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

452 }

453 replpar() {

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

455 }

456 getpar() {

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

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

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

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

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

462 fi

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

464 case "$var" in

465 owner)

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

467 echo $user; return

468 elif ismember $user $val; then

469 echo $val; return

470 fi ;;

471 esac

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

473 echo "$val"

474 }

475

476 getpartype() {

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

478 }

479 getparcount() {

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

481 }

482 getparfilename() {

483 # null if type of $1 is not file

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

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

486 }

487 sqlquote() {

488 (v="$1"

489 case "$v" in

490 "") return ;; # null

491 "X'"*) # quoted hex string

492 echo $1 ;;

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

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

495 echo "\"$v\""

496 return ;;

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

498 echo "\"$v\""

499 return ;;

500 *)

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

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

503 else

504 echo "\"$v\""

505 fi ;;

506 esac)

507 }

508 mktempd() {

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

510 }

511 getcachedir() { # $1=maintable

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

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

514 else

515 echo $tmpd/$thumbxy

516 fi

517 }

518 getval() {

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

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

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

522 echo "$user" ;;

523 stamp|datetime)

524 date "+%F %T" ;;

525 serial)

526 (s=`getpar $2`

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

528 *)

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

530 esac

531 }

532

533 getvalquote() {

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

535 (v=`getval "$@"`

536 case "$v" in

537 "") echo NULL ;;

538 *) sqlquote "$v" ;;

539 esac)

540 }

541 getparquote() {

542 sqlquote `getpar $1`

543 }

544 getbinbyid() {

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

546

547 }

548 getvalbyid() {

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

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

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

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

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

554

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

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

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

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

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

560 return

561 fi

562 done

563 pk=`gettblpkey $1`

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

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

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

567 for kt in s m; do

568 td=${4:-$tmpd}

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

570 t=${1}_$kt

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

572 vcount=1 # count(val)

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

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

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

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

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

578 if [ $kt = m ]; then

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

580 # Reset val to store filenames if type is string

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

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

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

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

585 i=0

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

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

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

589 i=$((i+1))

590 fn=$c.$i

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

592 case $type in

593 file:*)

594 #file=$td/$val

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

596 # FOR SPEED: Skip file generation if imgcache exists

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

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

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

600 .output $td/$fn.rowid

601 select rowid from $cond $slice;

602 .output $td/$fn

603 select val from $cond $slice;

604 .output $file.content-type

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

606 .output stdout

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

608 EOF

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

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

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

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

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

614 case $type in

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

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

617 *|*:[Aa]pplication*)

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

619 png:- > $file

620 ;;

621 esac

622 ;;

623 *)

624 sq $db<<EOF

625 .output $td/$fn.rowid

626 select rowid from $cond $slice;

627 .output $td/$fn

628 select val from $cond $slice;

629 EOF

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

631 ;;

632 esac

633 done

634 else

635 rm -f $td/$c.count

636 case $type in

637 file:*)

638 echo "$val" \

639 | while read fn; do

640 file=$td/$fn

641 if [ ! -s $file ]; then

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

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

644 | unhexize > $file

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

646 # err TTTTTTTTTTTTTTTT: $type

647 case $type in

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

649 *:[Aa]pplication*)

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

651 png:- > $file ;;

652 esac

653 fi

654 done

655 ;;

656 esac

657 fi

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

659 return

660 fi

661 done

662 done)

663 }

664 getvalbypkey() (

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

666 pk=`gettblpkey $1`

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

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

669 )

670 getvalbycond() {

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

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

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

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

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

676 fi

677 }

678 getpwfield() {

679 # getpwfield user column

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

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

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

683 echo "$val"

684 return 0

685 else

686 return 1

687 fi

688 }

689 encode() {

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

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

692 sha1=sha1

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

694 sha1=sha1sum

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

696 sha1=gsha1sum

697 fi

698 fi

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

700 }

701 mycrypt() (

702 key=$1 salt=$2

703 err \$2=$2

704 case $2 in

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

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

707 esac

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

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

710 )

711 hexize() {

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

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

714 hexize="xxd -p"

715 else

716 hexize_hd() {

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

718 }

719 hexize="hexize_hd"

720 fi

721 fi

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

723 }

724 unhexize() {

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

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

727 unhex="xxd -p -r"

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

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

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

731 EOF

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

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

734 # world writable...:)

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

736 fi

737 fi

738 cat $1 | $unhex

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

740 }

741 percenthex() {

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

743 }

744 htmlescape() {

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

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

747 }

748 enascii() {

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

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

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

752 else

753 enascii_now=`date +%FT%T`

754 enascii_sed() {

755 nkf -Z0Z1Z2 \

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

757 }

758 enascii="enascii_sed"

759 fi

760 fi

761 cat "$@" | $enascii

762 }

763 size_h() {

764 i="$1" oi=$1

765 set -- B B KB MB GB TB

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

767 oi=$i

768 i=$((i/1024))

769 shift

770 done

771 echo ${oi}$1

772 }

773 gettblconf() {

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

775 ## tconfs=`sq $db \

776 tconfs=`query \

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

778 fi

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

780 }

781 gettblkeys() {

782 # $1=tbl

783 gettblconf

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

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

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

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

788 case $type in

789 *p*)

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

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

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

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

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

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

796 esac

797 case $type in

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

799 esac

800 done

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

802 }

803 gettblpkey() {

804 # $1=tbl

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

806 }

807 gettblfkey() {

808 (x=`gettblkeys $1`

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

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

811 }

812 gettblcols() {

813 (x=`gettblkeys $1`

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

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

816 }

817 gettbl_s_cols() {

818 (x=`gettblkeys $1`

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

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

821 }

822 gettbl_m_cols() {

823 (x=`gettblkeys $1`

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

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

826 }

827 gettbl_h_cols() {

828 (x=`gettblkeys $1`

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

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

831 }

832 gettbl_coltype() (

833 gettblconf

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

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

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

837 )

838 is_hidden() {

839 # $1=Tbl $2=col

840 gettblconf

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

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

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

844 case $x in

845 *h*) return 0 ;;

846 *) return 1 ;;

847 esac

848 }

849

850 dbsetbyid() {

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

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

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

854 unset primary update

855 gettblconf

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

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

858 #err conf=$conf

859 case ${conf#*=} in

860 p*) primary=1 ;;

861 f*) update=1 ;;

862 u*) ;;

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

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

865 esac

866 #err t=$t

867 type=text fn=""

868 case $conf in

869 */password)

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

871 ;;

872 */image*|*/document*)

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

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

875 ;;

876 esac

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

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

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

880 if [ "$primary" ]; then

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

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

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

884 elif [ "$update" ]; then

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

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

887 else

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

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

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

891 fi

892 )

893 }

894 expire() (

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

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

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

898 )

899 addsession() {

900 # expireをセット

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

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

903 err addsession "$1" exp=$expire

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

905 # Remove old session parameters

906 now=`expire now`

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

908 }

909 gencookie() (

910 for kv; do

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

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

913 done

914 )

915 contenttype() {

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

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

918 }

919 putheader() {

920

921 }

922 putfooter() {

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

924 }

925 getcookie() (

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

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

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

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

930 done

931 )

932 genrandom() {

933 # $1=columns (default: 10)

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

935 }

936 smail() {

937 # smail rcpts subj (file)

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

939 rcpt=`echo $1` # strip newlines

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

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

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

943 }

944 setviastring() {

945 table=$1

946 oifs="$IFS"

947 IFS="&"

948 for us in $2; do

949 k=${us%%=*}

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

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

952 #echo $k=$v

953 done

954 IFS="$oifs"

955 }

956 checkdomain() (

957 # Check the validity of domain by referring DNS

958 item=$1

959 err checkdomain $1

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

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

962 )

963 pwcheck() {

964 # $1=passwd

965 dbpswd=`getpwfield $user pswd`

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

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

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

969 }

970 mypwhash() {

971 mycrypt `cat` `genrandom 5`

972 }

973 wasureta() {

974 user=$1

975 if ! checkdomain $user; then

976 contenttype; echo

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

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

979 putfooter

980 exit 0

981 fi

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

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

984 encpswd=`echo $newpswd|mypwhash`

985 dbsetbyid user $user pswd "$encpswd"

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

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

988 $msgdir/mail-newaccount.m4 \

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

990 | smail $user "New Account"

991 }

992 checkauth() {

993 user=`getpar user`

994 skc=`getpar skey` # from cookie

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

996 skey="`getpwfield $user skey`"

997 err user=$user skey=$skey

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

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

1000 return 0

1001 fi

1002 fi

1003 pswd=`getpar pswd`

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

1005 wasureta $user

1006 return 1 # wasureta error

1007 fi

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

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

1010 if pwcheck "$pswd"; then

1011 newsession=`genrandom 50`

1012 dbsetbyid user $user skey "$newsession"

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

1014 return 0

1015 fi

1016 return 2 # Password mismatch

1017 }

1018 showlogin() {

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

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

1021 $layout/login.m4.html

1022 exit 0

1023 }

1024 dologin() {

1025 checkauth

1026 st=$?

1027 if [ $st != 0 ]; then

1028 contenttype; echo

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

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

1031 showlogin # and EXIT

1032 fi

1033 }

1034

1035 # Do instant jobs here

1036 dbsetup

1037 trap cleanup INT HUP EXIT

1038 # trap cleanup INT HUP

1039

1040 err() {

1041 echo "$@" 1>&3

1042 }

1043

1044 cgiinit() {

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

1046 tmpf=tmp/stream

1047 tmpd=`tmpd=$tmpdir mktempd`

1048 tmpfiles=$tmpfiles" $tmpd"

1049 addsession $session

1050 getcookie

1051 case "$REQUEST_METHOD" in

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

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

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

1055 # cat > $tmpf # too much?

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

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

1058 s="`cat tmp/stream`"

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

1060 ;;

1061 esac

1062 case "$CONTENT_TYPE" in

1063 *boundary*)

1064 bndry=${CONTENT_TYPE#*boundary=}

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

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

1067 do

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

1069 #echo u=$us

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

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

1072 # err k=$k v=$v

1073 case "$k" in

1074 *:filename)

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

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

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

1078 [Ii]mage/x-xcf)

1079 bzip2 $tmpd/$v

1080 v=${v}.bz2

1081 ;;

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

1083 [Ii]mage/*)

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

1085 ;;

1086 esac

1087 ;;

1088 *)

1089 type='string'

1090 ;;

1091 esac

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

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

1094 done

1095 ;;

1096 *)

1097 setviastring par "$s"

1098 ;;

1099 esac

1100 }

1101 email4group() {

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

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

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

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

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

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

1108 and s.key='email'

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

1110 err `echo $sql`

1111 query "$sql"

1112 }

1113 email4groupbyuid() {

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

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

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

1117 uids=`echo "$@"`

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

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

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

1121 and s.key='email'

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

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

1124 err email4gByid `echo $sql`

1125 query "$sql"

1126 }

1127 collectemail() (

1128 # Collect email addresses for group $1

1129 for e; do

1130 if isuser "$e"; then

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

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

1133 else

1134 # sql="with recursive allmem as

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

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

1137 # where allmem.val=grp_m.gname)

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

1139 # from allmem a left join grp_mem_s b

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

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

1142 # tmp tmp tmp tmp tmp tmp tmp

1143 qgrp=`sqlquote "$e"`

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

1145 grp_mem g left join grp_mem_s s

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

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

1148 where g.gname=$qgrp;"

1149 err CollectEmail: `echo "$sql"`

1150 query "$sql"

1151 fi

1152 done

1153 )

1154 sendinvitation() (

1155 # $1=email

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

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

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

1159 gecos=`gecos`

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

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

1162 m4 -D_URL_="$urlbase" \

1163 -D_USER_="$name" \

1164 -D_EMAIL_="$1" \

1165 -D_REGIST_="$regist" \

1166 -D_ADMIN_="$admin" \

1167 $msgdir/mail-invite.m4 \

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

1169 return 0

1170 )

1171 emaildomaincheck() {

1172 case "$1" in

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

1174 *@*)

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

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

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

1178 return 2

1179 fi

1180 return 0

1181 ;;

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

1183 esac

1184 }

1185 invite() {

1186 email=`getpar email`

1187 case $email in

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

1189 *@*)

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

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

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

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

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

1195 elif sendinvitation $email; then

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

1197 fi ;;

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

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

1200 esac

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

1202 err addr=$addr

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

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

1205 fi

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

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

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

1209 }

1210 regist() {

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

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

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

1214 echo "bye bye" | html p

1215 reutrn

1216 fi

1217 email=`session=$1 getpar invite`

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

1219 cat<<EOF

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

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

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

1223 EOF

1224 return

1225 fi

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

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

1228 # Fake login password to wasureta

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

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

1231 wasureta $email

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

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

1234 addsession $1 # for removal after 1 minute

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

1236 $layout/login.m4.html

1237 return

1238 }

1239 newgrpchk() {

1240 # Check if $1 is existing and

1241 }

1242 groupupdate() {

1243 gname=`getpar gname`

1244 qgname=`sqlquote $gname`

1245 err Enter:groupupdate

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

1247 # See ALSO same job in showgroup()

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

1249 err newgname=$newgname

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

1251 err NewGNAME: gname=$newgname

1252 gname=$newgname

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

1254 replpar gname string "$gname"

1255 fi

1256 # Name confliction check

1257 parow=`getpar rowid`

1258 err parow=$parow

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

1260 query "BEGIN EXCLUSIVE;"

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

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

1263 if [ $count -gt 0 ]; then

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

1265 query "END;"

1266 return

1267 fi

1268 par2table $formdir/grp.def

1269 query "END TRANSACTION;"

1270 # Remove orphan

1271 : <<EOF

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

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

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

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

1276 EOF

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

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

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

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

1281 sql="delete from blog where id in

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

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

1284 query "$sql";

1285 fi

1286 fi

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

1288 fi

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

1290 grid=$(query $sql)

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

1292 grp $grid

1293 }

1294 groupman() {

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

1296

1297 GF_STAGE="grpconf"

1298 GF_STAGE=groupupdate

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

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

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

1302 -D_DUMPTABLE_="syscmd(cat)" \

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

1304 }

1305 userconf() {

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

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

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

1309 }

1310 groupconf() {

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

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

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

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

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

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

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

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

1319 }

1320 mems() {

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

1322 kwd=`getpar kwd`

1323 listmember $kwd

1324 }

1325 grps() {

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

1327 kwd=`getpar kwd`

1328 listgroup $kwd \

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

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

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

1332 $layout/form+dump.m4.html

1333 }

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

1335 gpg=`getpar grp`

1336 grid=${1:-$gpg}

1337 grp=`getgroupbyid "$grid"`

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

1339 ## . ./s4-blog.sh

1340 jg=`getpar joingrp`

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

1342 err jg=$jg, grp=$grp

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

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

1345 fi

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

1347 showgroup "$grid"

1348 }

1349 listnewblogsql() { # $1=user

1350 cat<<EOF

1351 with article_ctime as (

1352 select id,blogid,author,val ctime

1353 from article join article_s s using(id)

1354 where s.key='ctime'

1355 ), blog_title_owner as (

1356 select blog.rowid, id,

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

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

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

1360 ), acclog_user as (

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

1362 ), blogall as (

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

1364 from blog_title_owner b, article_ctime ac

1365 on b.id=ac.blogid

1366 )

1367 select rowid ID,

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

1369 when l.time < ctime then 1

1370 else 0

1371 end) "新着",

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

1373 /* owner gecos */

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

1375 from blogall bl left join acclog_user l

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

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

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

1379 when (select val from grp_s

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

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

1382 (select user from grp_mem

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

1384 then 0

1385 else 1

1386 end

1387 group by bl.id

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

1389 limit 10;

1390 EOF

1391 }

1392

1393 showhome() {

1394 # $1=userRowIdToShow

1395 err showhome \$1=$1

1396 case "$1" in

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

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

1399 esac

1400 err ShowHome: uname=$uname

1401 gecos=`gecos "$uname"`

1402 err SH:gecos=$gecos

1403 GF_VIEWONLY=1

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

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

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

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

1408 # Display folders

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

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

1411 and type like 'file:%';"

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

1413 nfile=`query "$sql"`

1414 err nfile=$nfile

1415 if [ $nfile -gt 0 ]; then

1416 conflink="$conflink /

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

1418 fi

1419 fi

1420 . ./s4-blog.sh

1421

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

1423 echo "$gecos さん" > $tf

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

1425

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

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

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

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

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

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

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

1433 echo '">'

1434 } > $imf

1435 fi

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

1437 val='$uname';"`

1438 listblog $uname > $bf

1439 listgroupbytable $formdir/grp.def $cond \

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

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

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

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

1444 -D_NBLOG_="$nblog" \

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

1446 $layout/html.m4.html $layout/home.m4.html

1447

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

1449 # Display NEWS

1450 if false; then

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

1452 new10=`DT_CHLD=article:blogid \

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

1454 else

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

1456 sql=`listnewblogsql "$user"`

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

1458 fi

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

1460 cont=$((cont-1))

1461 err newcount=$cont

1462 if [ $cont -gt 0 ]; then

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

1464 echo "$new10"

1465 fi

1466 cat<<EOF

1467 <div class="fold">

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

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

1470 <div>

1471 EOF

1472 cgi_form searchart<<EOF

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

1474 EOF

1475 cat<<EOF

1476 </div></div>

1477 EOF

1478 fi

1479 #

1480 # Record access log

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

1482 }

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

1484 contenttype; echo

1485 err commission: "$@"

1486 gname=`getgroupbyid $1`

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

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

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

1490 grp_reg_adm "$@"

1491 else

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

1493 fi

1494 }

1495 listgroupbytable() {

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

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

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

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

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

1501 NGsql="select distinct tag from\

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

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

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

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

1506 err ListGRP: query sql="$sql"

1507 for tag in `query "$sql"`

1508 do

1509 err ListGrp: tag=$tag

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

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

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

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

1514 as rowid,

1515 gname,

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

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

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

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

1520 err PersonalGroupList= `echo $sql`

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

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

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

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

1525 echo '</table>'

1526 done

1527 echo '</div>'

1528 }

1529 iconhref() (

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

1531 data=`percenthex $1`

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

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

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

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

1536 )

1537 iconhref2() (

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

1539 src=$1

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

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

1542 )

1543 listentry() (

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

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

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

1547 cond=''

1548 offset=`getpar offset`

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

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

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

1552 limit=30

1553 dir=`getcachedir "$1"`

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

1555 hrb="$myname?home"

1556 deficon=person-default.png

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

1558 gcs=gecos

1559 else # if group

1560 hrb="$myname?grp"

1561 deficon=group-default.png

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

1563 gcs=name

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

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

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

1567 err tagconv=$tagconv

1568 fi

1569 fi

1570 if [ ! -d $dir ]; then

1571 mkdir -p $dir

1572 fi

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

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

1575 fi

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

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

1578 fi

1579

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

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

1582 ##qgrp=`sqlquote $grp`

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

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

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

1586 b.tag,

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

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

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

1590 then '(ADMIN)'

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

1592 then '(Member)'

1593 when '$iamowner' = '' then ''

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

1595 from $tbl a left join

1596 (select $nm as name,

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

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

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

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

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

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

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

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

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

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

1607 cgi_form $stage <<EOF

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

1609 `cgi_text kwd $kwd`</label>

1610 EOF

1611 echo '</div>'

1612 fi

1613 cat<<EOF

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

1615 EOF

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

1617 cat<<EOF

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

1619 `cgi_submit 次の${limit}件`

1620 `cgi_hidden kwd "$kwd"`

1621 `cgi_hidden stage "$stage"`

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

1623 EOF

1624 fi

1625 if [ $offset -gt 0 ]; then

1626 cat<<EOF

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

1628 `cgi_submit 前の${limit}件`

1629 `cgi_hidden stage "$stage"`

1630 `cgi_hidden kwd "$kwd"`

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

1632 EOF

1633 fi

1634

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

1636

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

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

1639 err name=$name owner=$ownerp lnk=$lnk

1640 err newlnk=$lnk

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

1642 # Pick up only first icon

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

1644 | m4 $tagconv

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

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

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

1648 else

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

1650 fi

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

1652 echo "</div>"

1653 done

1654 )

1655 listmember() {

1656 listentry user "$@"

1657 }

1658 listgroup() {

1659 listentry group "$@"

1660 }

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

1662 # gname=`getpar gname`

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

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

1665 # # See ALSO same job in groupman()

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

1667 # ###newgname=$gname

1668 # err gname=$gname newgname=$newgname

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

1670 # err NewGNAME: gname=$newgname

1671 # gname=$newgname

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

1673 # replpar gname string "$gname"

1674 # fi

1675 # par2table $formdir/grp.def

1676 # fi

1677 grp=`getgroupbyid $1`

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

1679 if isgroup "$grp"; then

1680 tf=$tmpd/title.$$

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

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

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

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

1685 -D_DUMPTABLE_="" \

1686 $layout/form+dump.m4.html

1687 else # if $grp is removed at par2table

1688 listgroup

1689 fi

1690 }

1691 showgroupsub() {

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

1693 rowid=$2

1694 grp=`getgroupbyid $2`

1695 qgrp=`sqlquote $grp`

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

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

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

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

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

1701 return

1702 fi

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

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

1705 viewtable $1 grp $rowid

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

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

1708 iamowner=$rowid

1709 fi

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

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

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

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

1714 cat<<EOF

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

1716 <div class="fold">

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

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

1719 <div>

1720 `cgi_textarea message "" "cols=60"`

1721 `cgi_submit 送信`

1722 `cgi_reset リセット`

1723 </div>

1724 `cgi_hidden grp $rowid`

1725 </div></form>

1726 EOF

1727 fi

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

1729 err ismember $user $grp

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

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

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

1733 and key='email';"`

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

1735 and key='email';"

1736 err email=$eml

1737 cat <<EOF

1738 <div class="fold">

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

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

1741 <div>

1742 EOF

1743 cgi_form grp <<EOF

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

1745 <table class="b">

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

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

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

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

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

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

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

1753 </table>

1754 `cgi_hidden grp $rowid`

1755 EOF

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

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

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

1759 fi

1760 echo '</div></div>'

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

1762 cgi_form searchart<<EOF

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

1764 `cgi_hidden owner $grp`

1765 EOF

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

1767 DT_CHLD=article:blogid \

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

1769

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

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

1772 cm="?commission+$rowid"

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

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

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

1776 }

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

1778 err GRP_ACTION:IN

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

1780 grp=`getgroupbyid "$grid"`

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

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

1783 fi

1784 if ! ismember $user $grp; then

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

1786 fi

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

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

1789

1790 isowner=""

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

1792 usel=`getpar usel`

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

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

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

1796 text=`getpar text`

1797

1798 rm=`getpar rm` cfm=`getpar confirm`

1799 err rm=$rm cfm=$cfm

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

1801 if [ "$isowner" ]; then

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

1803 # Eliminate

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

1805 for tbl in grp_mem grp_mem_s grp_mem_m; do

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

1807 # echo "sql=$sql"

1808 query "$sql"

1809 err rmGRPuser "$sql"

1810 done

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

1812 #err num=$num

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

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

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

1816 # err `echo "$sql"`

1817 html pre<<EOF

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

1819 `query "$sql"`

1820 EOF

1821 fi

1822 else

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

1824 return

1825 fi

1826 else # not Group Owner

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

1828 return

1829 fi

1830 cat<<EOF

1831

1832 EOF

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

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

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

1836 return 0

1837 fi

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

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

1840 $url

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

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

1843 ----------------------------------------------------------

1844 $text

1845 EOF

1846 if [ $? = 0 ]; then

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

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

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

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

1851 html pre<<EOF

1852 `query "$sql"`

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

1854 EOF

1855 err SendDone: `echo $sql`

1856 fi

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

1858 grp_reg_adm $grid $usel

1859 fi

1860 fi

1861 # New entry

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

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

1864 from (select gname,user from grp_mem

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

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

1867 b using(gname,user))

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

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

1870 case

1871 when (select user from grp_adm where

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

1873 and user=a.name) is not null

1874 then 'k'

1875 else ''

1876 end || a.rowid||

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

1878 (select rowid,name from user where name in

1879 (select user from grp_mem where

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

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

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

1883 err grpaction: `echo $sql`

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

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

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

1887 tf=$tmpd/title.$$

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

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

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

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

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

1893 </p></div></div>"

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

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

1896 <div>

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

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

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

1900 </div>

1901 </div>"

1902 cgi_form grpaction<<EOF \

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

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

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

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

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

1908 $layout/form+dump.m4.html

1909 <div class="fold">

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

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

1912 <div>

1913 `cgi_textarea text "" cols=40`

1914 </div>

1915 </div>

1916 ${isowner:+$cmmsg$excmsg}

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

1918 <table>

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

1920 `cgi_hidden grp $grid`

1921 </table>

1922 EOF

1923 }

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

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

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

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

1928 return

1929 fi

1930 owner=`getvalbyid blog owner $rowid`

1931 title=`getvalbyid blog title $rowid`

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

1933 | m4 -D_TITLE_="修正" \

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

1935 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

1938 }

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

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

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

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

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

1944 return

1945 fi

1946 owner=`getvalbyid blog owner $blogrowid`

1947 title=`getvalbyid blog title $blogrowid`

1948 author=`getvalbyid article author $rowid`

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

1950 if isgrpowner $user $owner; then

1951 : EDIT OK

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

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

1954 return

1955 fi

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

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

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

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

1960 > $tmpout

1961 rm -f /tmp/editart.out

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

1963 m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

1964 -D_FORM_="syscmd(cat $tmpout)" \

1965 -D_SUBTITLE_="`gecos $owner`の「$title」" \

1966 -D_BLOGS_= -D_DUMPTABLE_= \

1967 $layout/html.m4.html $layout/form+dump.m4.html

1968 }

1969 send2mem() {

1970 rowid=`getpar grp`

1971 if [ -z "$rowid" ]; then

1972 echo "グループが未指定です。" | html p

1973 return

1974 fi

1975 message=`getpar message`

1976 if [ -z "$message" ]; then

1977 echo "文章を入れてください。" | html p

1978 return

1979 fi

1980 grp=`getgroupbyid $rowid`

1981 members=`collectemail $grp`

1982 # smail rcpt subj (file)

1983 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

1984 $urlbase?grp+$rowid

1985 グループ $grp に所属する

1986 `gecos $user` さんよりメッセージ:

1987

1988 $message

1989 EOF

1990 cat<<EOF

1991 <p>以下の宛先に送信しました。</p>

1992 <pre>

1993 $members

1994 </pre>

1995 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

1996 EOF

1997 }

1998 joingrpadmit() {

1999 # $1=yes/no $2=session-key

2000 if [ -z "$2" ]; then

2001 echo "bye bye" | html p; return

2002 fi

2003 t_usr=`session=$2 getpar user`

2004 t_grp=`session=$2 getpar group`

2005 err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2006 m4 -D_TITLE_="joingrp" $layout/html.m4.html

2007 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2008 echo "無効な加入依頼です。" | html p

2009 echo "有効期限が切れたか、

2010 他の管理者がいる場合は処理済みの可能性があります。" | html p

2011 return

2012 fi

2013 if ! isgrpowner "$user" $t_grp; then

2014 echo "グループ管理者のみの機能です。" | html p; return

2015 fi

2016 case $1 in

2017 yes) joingrp "$t_grp" "$t_usr" yes "$t_usr" ;;

2018 no) joingrp "$t_grp" "$t_usr" no "$t_usr" ;;

2019 *)

2020 echo "無効な指定です($1)。" | html p

2021 return ;;

2022 esac

2023 gid=$(query "select rowid from grp where gname=`sqlquote $t_grp`;")

2024 rcpts="`getgroupadminmails $t_grp` $user"

2025 err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2026 body="グループ $t_grp

2027 に

2028 $t_usr

2029 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2030 しました。"

2031 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2032 query "delete from session where id='$2';"

2033 echo "$body" | html p

2034 }

2035

2036 joingrprequest() {

2037 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2038 jss="joingrp-`date +%s`-`genrandom 12`"

2039 addsession $jss +${memoplimitdays}days

2040 query "replace into par values('$jss', 'group', 'string', `sqlquote $1`),

2041 ('$jss', 'user', 'string', `sqlquote $user`);"

2042 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2043 $url

2044 グループ $1

2045 に加入依頼がありました。

2046

2047 承認する:

2048 $urlbase?joingrpadmit+yes+$jss

2049

2050 白紙に戻す:

2051 $urlbase?joingrpadmit+no+$jss

2052 EOF

2053 echo "管理者に加入依頼を出しました。

2054 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2055 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2056 }

2057 joingrp() {

2058 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2059 err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2060 isgrpowner "$user" "$1" && isowner="yes" || isowner=""

2061 err jg:isgrpowner: isowner="$isowner"

2062 if [ -n "$isowner" ]; then

2063 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2064 elif [ x"$2" != x"$user" ]; then # if user is not login user

2065 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2066 return

2067 elif [ x"$3" = x"no" ]; then

2068 : # Do not pursue those who leave

2069 else # adding user is $user itself

2070 case `getgroupattr $1 regmode` in

2071 moderated)

2072 joingrprequest "$@" # Request only

2073 return

2074 ;;

2075 *)

2076 ;;

2077 esac

2078 fi

2079 qgname=`sqlquote $1`

2080 cond="where gname=$qgname and user='$2'"

2081 if [ x"$3" = x"yes" ]; then

2082 query "replace into grp_mem values($qgname, '$2');"

2083 if [ -n "$4" ]; then

2084 if msg=`emaildomaincheck "$4"`; then

2085 err "replace into grp_mem_s values($qgname, '$user', 'email', \

2086 'string', '$4', NULL);"

2087 query "replace into grp_mem_s values($qgname, '$user', 'email', \

2088 'string', '$4', NULL);"

2089 if [ -n "$5" ]; then # as ADMIN

2090 # Coming here means newly created group

2091 sql="select case\

2092 when (select count(*) from grp_mem where gname=$qgname)=1\

2093 then (select user from grp_mem\

2094 where gname=$qgname and user='$user')\

2095 else '' end;"

2096 err NewGrpChk: $sql

2097 if [ -n "`query \"$sql\"`" ]; then

2098 err ADMIN: "replace into grp_adm values($qgname, '$user');"

2099 query "replace into grp_adm values($qgname, '$user');"

2100 fi

2101 fi

2102 else

2103 echo $msg

2104 fi

2105 else

2106 query "delete from grp_mem_s $cond and key='email';"

2107 fi

2108 else

2109 query "delete from grp_mem $cond;

2110 delete from grp_mem_s $cond;

2111 delete from grp_mem_m $cond;"

2112 fi

2113 }

2114 grp_reg_adm() {

2115 # $1=grp-rowid $2...=user-rowid

2116 grid=$1

2117 grp=`getgroupbyid "$1"`

2118 if [ -z "$grp" ]; then

2119 echo "無効なグループIDです" | html p; return

2120 fi

2121 if ! isgrpowner $user "$grp"; then

2122 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

2123 fi

2124 shift

2125 for urid; do

2126 newadm=`query "select name from user where rowid=$urid;"`

2127 if [ -z "$newadm" ]; then

2128 echo "指定ユーザIDがおかしいようです。" | html p; return

2129 fi

2130 err GRP_reg_adm: "replace into grp_adm values(`sqlquote $grp`, '$newadm');"

2131 err ismember $newadm $grp

2132 if ismember $newadm $grp; then

2133 # OK, go ahead

2134 getgname="(select gname from grp where rowid=$grid)"

2135 query "replace into grp_adm values($getgname, '$newadm');"

2136 # confirm insertion

2137 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

2138 if [ -n "`query \"$sql;\"`" ]; then

2139 echo "追加完了: $newadm" | html p

2140 else

2141 echo "追加失敗($1 $urid)" | html p

2142 fi

2143 fi

2144 showgroup $grid

2145 done

2146 }

2147 dumptable() {

2148 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

2149 # textのフィールドだけ全てダンプにしたほうがいいか

2150 # $DT_VIEW sets link

2151 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

2152 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

2153 VIEW=${DT_VIEW-replyblog}

2154 if [ -n "$VIEW" ]; then

2155 dvlink=" <a href=\"$myname?$VIEW+\\2\">VIE</a><a href=\"$myname?$VIEW+\\2#bottom\">W</a>"

2156 fi

2157 # $DT_CHLD=ChildTable:BindColumn

2158 if [ -n "$DT_CHLD" ]; then

2159 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

2160 cntall="(select count($_i) from $_t where $_i=a.id)"

2161 cntnew="(select count(val) from ${_t}_s where key='ctime' \

2162 and id in (select id from $_t where $_i=a.id) \

2163 and val > coalesce((select time from acclog where \

2164 user='$user' and tbl='$2' and tblrowid=a.rowid),\

2165 '1970-01-01'))"

2166 cnt="$cntnew as '新着', $cntall as '総数',"

2167 dt_class=" td2r td3r"

2168 fi

2169 # Construct join expression

2170 eav="" scols=""

2171 pk=`gettblpkey $2`

2172 substr=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2173 substr=${substr:-val}

2174 for col in ${3:-`gettbl_s_cols $2`}; do

2175 case $col in

2176 gecos) scols="$scols${scols:+, }${col#}"

2177 continue ;; # built-in column name

2178 *:*) col=${col%:*} as=${col#*:} ;;

2179 *) as=${col} ;;

2180 esac

2181 eav=$eav${eav:+,}" max(case key when '$col' then $substr end) as $as"

2182 scols="$scols${scols:+, }b.$as"

2183 done

2184 #case author when '$user' then a.rowid else '---' end as ID,

2185 sql=${DT_SQL:-"select \

2186 a.rowid as ID,\

2187 $cnt\

2188 $scols from $2 a left join\

2189 (select $pk,$eav,

2190 max(case key when 'owner'

2191 then coalesce((select val from user_s us

2192 where us.key='gecos' and us.name=c.val), val) end) as gecos

2193 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}

2194 err SQL=`echo "$sql"`

2195 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$</TD>,\1$elink$dvlink</TD>,"

2196 <div class="dumptable">

2197 <table class="b$dt_class">

2198 `sq -header -cmd ".mode $1" $db "$sql"`

2199 </table>

2200 </div>

2201 EOF

2202 }

2203

2204 par2table() (

2205 # copy current parameters of par into destination table

2206 # $1=definition-file

2207 # Using $user and $session

2208 # Return value:

2209 # 0: Stored successfully

2210 # 1: Insufficient fillings

2211 # 2: No permission to modify the record

2212 # 3: Invalid rowid

2213 # 4: SUCCESS to delete

2214 # 5: Stop deletion for lack of confirm check

2215 # 6: Password length too short

2216 # 7: Password mismatch

2217 # 8: Old password incorrect

2218 rowid=`getpar rowid`

2219 err ...........rowid=$rowid

2220 if [ ! -e $1 ]; then

2221 echo "テーブル定義ファイルが見付かりません" | html p

2222 exit 1

2223 fi

2224 tbl=${1%.def}

2225 tbl=${tbl##*/}

2226 if [ -n "$rowid" ]; then # Modify existing entry

2227 if [ x"$tbl" = x"user" ]; then

2228 rowowner=`query "select name from $tbl where rowid=$rowid;"`

2229 elif [ x"$tbl" = x"grp" ]; then

2230 sql="select gname from $tbl where rowid=$rowid;"

2231 ##err p2t:grp:q $sql

2232 isgrpowner $user "`query $sql`" && rowowner=$user

2233 else

2234 rowowner=`query "select owner from $tbl where rowid=$rowid;"`

2235 rowowner=${rowowner:-`query "select author from $tbl

2236 where rowid=$rowid;"`}

2237 fi

2238 ### err rowowner=$rowowner

2239 if [ x"$user" != x"$rowowner" ]; then

2240 echo "他人のレコードはいじれないの" | html p

2241 return 2

2242 elif [ -z "$rowowner" ]; then

2243 echo "指定したレコードはないみたい" | html p

2244 return 3

2245 fi

2246 rm=`getpar rm` cfm=`getpar confirm`

2247 # Editing existent entry

2248 if [ x"$rm" = x"yes" ]; then

2249 if [ x"$rm$cfm" = x"yesyes" ]; then

2250 query "delete from $tbl where rowid=$rowid;"

2251 return 4

2252 else

2253 echo "消去確認のチェックがないので消さなかったの..." | html p

2254 return 5

2255 fi

2256 fi

2257 fi

2258 # XX: Subshelling here is unnecessary 2015-07-05

2259 (ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

2260 if [ -n "$rowid" ]; then

2261 # Update of existing record

2262 for col in `gettblcols $tbl`; do

2263 val=`getparquote $col`

2264 [ -z "$val" ] && continue

2265 err query "update $tbl set $col=$val where rowid=$rowid"

2266 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

2267 sql="update $tbl set $col=$val where rowid=$rowid;"

2268 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

2269 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

2270 ## User name cannot be changed with interface provided with this

2271 ## script. But we offer the trigger to change owner user

2272 ## of blog_s table.

2273 err "select quote($col) from $tbl where rowid=$rowid;"

2274 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

2275 cat<<-EOF | query

2276 BEGIN;

2277 $sql

2278 update blog_s set val=$val

2279 where key='owner' and val=$old;

2280 COMMIT;

2281 EOF

2282 ## XX: DIRTY Hack Ends here

2283 ## We should keep blog's owner as a single column which has

2284 ## foreign key constraint with primary key of grp/user.

2285 else

2286 query "$sql"

2287 fi

2288 done

2289 # Then, set up $pval for further insertion of tbl_s and tbl_m

2290 for col in `gettblpkey $tbl`; do

2291 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

2292 pval="$pval${pval:+, }\"$val\""

2293 done

2294 else

2295 # New entry

2296 # Generate values() for primary keys

2297 for col in `gettblpkey $tbl`; do

2298 # Genuine primary keys for _m and _s

2299 val=`getvalquote $tbl $col`

2300 [ -z "$val" ] && continue

2301 pval="$pval${pval:+, }$val"

2302 done

2303 err pval=$pval

2304 for col in `gettblfkey $tbl`; do

2305 # args for values() to insertion into master table

2306 val=`getvalquote $tbl $col`

2307 [ -z "$val" ] && continue

2308 formaster=$formaster"${formaster:+, }$val"

2309 done

2310 formaster="$pval${formaster:+, }$formaster"

2311 err formaster=$formaster

2312 if [ -z "$formaster" ]; then

2313 echo "項目を全て埋めてください" | html pre

2314 return 1

2315 fi

2316 err "replace into $tbl values($formaster);"

2317 query "replace into $tbl values($formaster);"

2318 ## Insertion to master table, done

2319 fi

2320

2321 for kt in s m; do

2322 tb2=${tbl}_$kt

2323 for col in `gettbl_${kt}_cols $tbl`; do

2324 ptype=`getpartype $col "limit 1"`

2325

2326 # First, check update of existing entries in _m

2327 if [ $kt = m ]; then

2328 # sessID|address.1.22|string|Somewhere-x.y.z

2329 sql=""

2330 err dots from query "select var from par where var like '$col.%';"

2331 for v in `query "select var from par where var like '$col.%';"`; do

2332 # v=address.1.22

2333 st_rowid=${v##*.}

2334 origcol=${v%%.*} # original column derived from

2335 err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

2336 ##case `getpartype $v` in

2337 err CASE `gettbl_coltype $tbl/$origcol` in

2338 err edit flag = `getpar action.$v`

2339 case `getpar action.$v` in

2340 rm)

2341 if [ x`getpar confirm.$v` = x"yes" ]; then

2342 newsql="delete from $tb2"

2343 else

2344 echo "削除確認未チェック" | html p

2345 fi ;;

2346 edit)

2347 case `gettbl_coltype $tbl/$origcol` in

2348 image|document|binary)

2349 file=$tmpd/`getparfilename $v`

2350 err type=file=$file

2351 [ -z "$file" ] && continue

2352 bn=${file##*/}

2353 bin="X'"$(hexize $file)"'"

2354 ct=`file --mime-type $file|cut -d' ' -f2`

2355 type=\"file:$ct\"

2356 newsql="update $tb2 set val='$bn', type=$type, bin=$bin"

2357 cachedir=`getcachedir "$tbl/$rowid"`

2358 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

2359 rm -r $cachedir

2360 ;;

2361 *)

2362 newsql="update $tb2 set val=(select val from par where var \

2363 like '$col.%.$st_rowid')"

2364 ;;

2365 esac

2366 ;;

2367 *) # maybe "keep", do not modify value

2368 continue

2369 ;;

2370 esac

2371 # err newsql=$newsql

2372 sql=$sql$nl"$newsql where rowid=$st_rowid;"

2373 done

2374

2375 if [ x"$bin" = x"NULL" ]; then

2376 err repl:normal sql=`echo $sql`

2377 query "$sql

2378 delete from $tb2 where type='string' and val='';"

2379 err repl:normal done

2380 else

2381 sqlfile="$tmpd/sqlf.$$"

2382 echo "$sql" > $sqlfile

2383 err repl:sqlfile=`ls -lF $sqlfile`

2384 query ".read $sqlfile"

2385 err repl:done

2386 fi

2387 # Rest of kt==m: set multiple mode

2388 nr=`getparcount $col`

2389 else

2390 nr=1 # for kt==s, number of records is 1

2391 fi

2392

2393 i=0

2394 while [ $i -lt $nr ]; do

2395 limit="limit 1 offset $i"

2396 i=$((i+1)) # increase beforehand against continue

2397 val=`getvalquote $tbl $col "$limit"`

2398 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

2399 err $col=$val

2400 bin=NULL

2401 err partype$col=`getpartype $col "$limit"`

2402 case $ptype in

2403 file) file=$tmpd/`getparfilename $col "$limit"`

2404 err parfile-$col=$file

2405 [ -z "$file" ] && continue

2406 bin="X'"$(hexize $file)"'"

2407 ct=`file --mime-type $file|cut -d' ' -f2`

2408 type=\"file:$ct\" ;;

2409 "*"*) continue ;; # foreign table

2410 *) type=\"string\" ;;

2411 esac

2412 case `gettbl_coltype $tbl/$col` in

2413 password) # special care for password

2414 # name={password,pswd1,pswd2}

2415 p1=`getpar pswd1 "$limit"`

2416 if [ -z "$p1" ]; then

2417 continue # SKIP password setting, if p1 is empty

2418 else

2419 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

2420 ## err pswd=$pswd

2421 if pwcheck "$pswd"; then

2422 if [ x"$p1" = x"$p2" ]; then

2423 case "$p1" in

2424 ??????????*) ;;

2425 *) echo "パスワードは10字以上にしてください。" | html p

2426 return 6;;

2427 esac

2428 val="\"`echo $p1|mypwhash`\""

2429 else

2430 echo "2つの新パスワード不一致" | html p

2431 return 7

2432 fi

2433 else

2434 echo "旧パスワード違います" | html p

2435 return 8

2436 fi

2437 fi

2438 ;;

2439 esac

2440 err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

2441 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

2442 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

2443 if [ x"$bin" = x"NULL" ]; then

2444 err Normal-query: `echo $sql`

2445 query "$sql"

2446 else

2447 sqlfile="$tmpd/query.$$"

2448 echo "$sql" > $sqlfile

2449 err sqlfile=`ls -lF $sqlfile`

2450 query ".read $sqlfile"

2451 fi

2452 err p2t done

2453 done

2454 done

2455 done

2456 return 0

2457 err donee)

2458 )

2459 par2table_old() {

2460 # copy current parameters of par into destination table

2461 # $1=dst-table $2=definition-file

2462 # Using $user and $session

2463 rowid=`getpar rowid`

2464 if [ -n "$rowid" ]; then

2465 rm=`getpar rm` cfm=`getpar confirm`

2466 if [ x"$rm$cfm" = x"yesyes" ]; then

2467 sq $db "delete from $1 where rowid=$rowid and owner=\"$user\""

2468 return

2469 fi

2470 fi

2471 cat $2 \

2472 | (cols=""

2473 while IFS=: read prompt name type args; do

2474 [ x"$name" = x"stage" ] && continue

2475 if [ -n "$rowid" ]; then

2476 val=`getpar $name|sed -e 's/\"/\"\"/g'`

2477 sq $db "update $1 set $name=\"$val\" where rowid=$rowid and owner=\"$user\""

2478 else

2479 eav=$eav${eav+,}" max(case var when '$name' then val end)"

2480 fi

2481 done

2482 [ -n "$rowid" ] && return

2483 cond="where sessid='$session' group by sessid"

2484 sq $db "replace into $1 select $eav,\"$user\" from par $cond"

2485 # Think over again about putting $user

2486 )

2487 }

2488 genform() {

2489 # $1 = form definition file

2490 # $2, $3 (optional)= table name and ROWID

2491 # If $GF_VIEWONLY set and nonNull, output values without form

2492 # If $GF_HIDDEN set, use it hidden values

2493 # If $GF_OWNER set, use it as value of name="owner"

2494 # If $GF_STAGE set, use it as value of name="stage"

2495 forms="" hiddens="" rowid=$3

2496 if [ ! -e "$1" ]; then

2497 echo "そのようなデータベースはないようです($2)。" | html p

2498 return

2499 elif [ -n "$2" ]; then

2500 err genform1: "select * from $2 where rowid='$rowid'"

2501 rec=`query "select * from $2 where rowid='$rowid';"`

2502 if [ -z "$rec" ]; then

2503 pk=`gettblpkey $2`

2504 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

2505 err "select rowid from $2 where $pk='$rowid';"

2506 rec=`query "select rowid from $2 where $pk='$rowid';"`

2507 err rec-rowid=$rec

2508 rowid=$rec

2509 rec=$3

2510 fi

2511 if [ -z "$rec" ]; then

2512 echo "そんなレコードはないみたいね..." | html p

2513 return

2514 fi

2515 fi

2516 if [ -z "$GF_VIEWONLY" ]; then

2517 rm='<input id="rm" name="rm" type="checkbox"

2518 value="yes"><label for="rm">このエントリの削除</label>

2519 <span>ほんとうに消しますよ(確認)!

2520 <input name="confirm" type=checkbox value="yes">はい</span>'

2521 fi

2522 # Image Cache dir

2523 err genform: getcache=$2/$rowid

2524 td=`getcachedir "$2/$rowid"`

2525 while IFS=: read prompt name keytype type args; do

2526 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

2527 sp="${args:+ }"

2528 form="" val=""

2529 if [ -n "$rowid" ]; then

2530 # err genform2a: Seeking for "$2.$name, type=$type"

2531 val=`getvalbyid $2 $name $rowid $td|htmlescape`

2532 err genform3a: getvalbyid $2 $name $rowid $td

2533 err genform3b: val="[$val]"

2534 fi

2535 if [ -n "$GF_VIEWONLY" ]; then

2536 is_hidden "$2" "$name" && continue

2537 fi

2538 case "$type" in

2539 text*)

2540 cgiform=cgi_multi_$type

2541 if [ -s $td/$name.count -a -n "$val" ]; then

2542 form=`$cgiform $name $td`

2543 val=$(echo "$val"|

2544 while read fn; do

2545 echo "<tr><td>`cat $td/$fn`</td></tr>$nl"

2546 done)

2547 val="<table>$nl$val$nl</table>"

2548 else

2549 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

2550 err genform: cgi_$type $name $val "$args"

2551 form=`cgi_$type $name "$val" "$args"`

2552 fi

2553 ;;

2554 [Rr][Aa][Dd][Ii][Oo])

2555 fh="<label><input type=\"radio\" name=\"$name\""

2556 form="`echo $args|sed -e \

2557 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

2558 ;;

2559 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

2560 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

2561 ;;

2562 [Ss][Ee][Ll][Ee][Cc][Tt])

2563 fh="<select name=\"$name\">$nl"

2564 form=$(for l in $args; do

2565 echo "<option value=\"${l#*=}\">${l%=*}</option>"

2566 done)

2567 if [ -n "$val" ]; then

2568 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

2569 fi

2570 form="$fh$form</select>"

2571 ;;

2572 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

2573 if [ -s $td/$name.count ]; then

2574 form=`cgi_multi_file $name $td "$args"`

2575 if [ -n "$val" ]; then

2576 hrfb="$myname?showattc+$2_m"

2577 val=$(echo "$val" \

2578 | while read fn; do

2579 data=`percenthex $td/$fn`

2580 #ct=`cat $td/$fn.content-type`

2581 ct=`file --mime-type $td/$fn|cut -d' ' -f2`

2582 ri=`cat $td/$fn.rowid`

2583 ## err fn=$fn, name=$name, ri=$ri; ls -lF $td 1>&3

2584 #imgsrc="<img src=\"data:$ct,$data\">"

2585 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

2586 iconhref $td/$fn "$hrfb+$ri" ""

2587 done)

2588 fi

2589 else

2590 form="<input type=\"file\" name=\"$name\" $args>"

2591 if [ -n "$val" ]; then

2592 imgs=$(echo "$val"\

2593 |while read fn;do

2594 data=`percenthex $td/$fn`

2595 echo "<img src=\"data:image/png,$data\">$fn<br>"

2596 done)

2597 form=$form"<br>$imgs"

2598 val=$imgs # 2015-06-15

2599 else

2600 form="<input type=\"file\" name=\"$name\" $args>"

2601 fi

2602 fi

2603 ;;

2604 [Hh][Ii][Dd][Dd][Ee][Nn])

2605 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

2606 args="value=\"$GF_STAGE\""

2607 fi

2608 form="<input type=\"hidden\" name=\"$name\" $args>"

2609 prompt='' # Remove prompt

2610 ;;

2611 [Aa][Uu][Tt][Hh][Oo][Rr])

2612 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

2613 prompt="" ;;

2614 [Oo][Ww][Nn][Ee][Rr])

2615 val=${GF_OWNER:-$val}

2616 val=${val:-$user}

2617 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

2618 prompt="" ;;

2619 [Uu][Ss][Ee][Rr])

2620 # XXX: is null $user ok?

2621 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

2622 [ -n "$GF_VIEWONLY" ] && continue

2623 form="$user"

2624 ;;

2625 [Pp]assword)

2626 [ -n "$GF_VIEWONLY" ] && continue

2627 form="`cgi_passwd`"

2628 val=""

2629 ;;

2630 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

2631 if [ -z "$rowid" ]; then

2632 val=$((($(date +%s)-1433084400)/10))c$$

2633 fi

2634 ## form="<input type=\"hidden\" name=\"serial\" value=\"$val\">"

2635 ## 2015-07-31

2636 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

2637 prompt="" ;;

2638 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

2639 prompt=""

2640 ;;

2641 parent|path|blog*)

2642 prompt=""

2643 ;;

2644 "*"*)

2645 tail=$tail"``"

2646 continue ;;

2647 esac

2648 if [ -n "$prompt" ]; then

2649 if [ -n "${GF_VIEWONLY}" ]; then

2650 form=$val

2651 else

2652 :

2653 fi

2654 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

2655 else

2656 hiddens=$hiddens$nl"$form"

2657 fi

2658 done < $1

2659 # enctype="multipart/form-data"

2660 cat<<EOF

2661 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

2662 ${rowid:+$rm}

2663 <table class="b $2">

2664 $forms

2665 </table>$hiddens

2666 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

2667 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

2668 EOF

2669 if [ -z $GF_VIEWONLY ]; then

2670 cat<<EOF

2671 <input type="submit" name="sub" value="OK">

2672 <input type="reset" name="res" value="Reset">

2673 EOF

2674 fi

2675 cat<<EOF

2676 </form>

2677 $tail

2678 EOF

2679 }

2680 edittable() {

2681 # $1=form-def $2=table $3 rowid

2682 genform "$@"

2683 }

2684 viewtable() {

2685 GF_VIEWONLY=1 genform "$@"

2686 }

2687 showattc() {

2688 # $1=table_m $2=rowid

2689 err \$1=$1 \$2=$2

2690 if ! isfilereadable $user $1 $2; then

2691 contenttype; echo

2692 echo "このファイルは管理者のみしか見られません" | html p

2693 putfooter; exit

2694 fi

2695 idir=`umask 002; mktempd` || exit 1

2696 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

2697 bin=$idir/$myname-$$.bin

2698 sql="select quote(bin) from $1 where rowid='$2';"

2699 err showattc: sql: $sql

2700 sq $db "$sql" | unhexize > $bin

2701 tv=`query "select type,val from $1 where rowid='$2';"`

2702 type=${tv%\|*} fn=${tv#*\|}

2703 err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

2704 ct=${type#file:}

2705 case $ct in # all text/* changed to text/plain

2706 text/*)

2707 charset=`nkf -g $bin|cut -d' ' -f1`

2708 case $charset in

2709 ASCII*) charset="" ;;

2710 esac

2711 ct="text/plain${charset:+; charset=$charset}"

2712 ;;

2713 esac

2714 contenttype "$ct"

2715 echo "Content-Disposition: filename=\"$fn\""

2716 echo "Content-Length: " `cat $bin | wc -c`; echo

2717 #echo "Content-Type: " ${type#file:}; echo

2718 cat $bin

2719 }

2720 #

2721 # Some default stupid handler on CGI values

2722 #

2723 default_storedb() {

2724 # ARG: $1=table-def-file

2725 # RET: $tbl=table-name, $col=mail-column, $cols=columns

2726 tbl=`basename $1`

2727 tbl=${tbl%.def}

2728 cols="`grep :text $1|cut -d: -f2`"

2729 col=`echo "$cols"|head -1`

2730 vcol=`getpar $col`

2731 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

2732 if [ -n "$vcol" ]; then

2733 par2table $1

2734 else

2735 return 2 # No insertion occurred

2736 fi

2737 }

2738

2739 default_view() { # $1=def-file

2740 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

2741 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

2742 default_storedb "$@"

2743 query "select rowid from $tbl order by rowid desc;" \

2744 | while read rowid; do

2745 viewtable $1 $tbl $rowid

2746 done | m4 -D_TITLE_="$tbl" \

2747 -D_FORM_="`genform $1`" \

2748 -D_DUMPTABLE_="syscmd(cat)" \

2749 $layout/html.m4.html $layout/form+dump.m4.html

2750 }

2751 default_viewtext() { # $1=def-file

2752 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

2753 default_storedb "$@"

2754 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

2755 | m4 -D_TITLE_="$tbl" \

2756 -D_FORM_="`genform $1`" \

2757 -D_DUMPTABLE_="syscmd(cat)" \

2758 $layout/html.m4.html $layout/form+dump.m4.html

2759 }

2760 default_smail() {

2761 default_storedb "$@"

2762 if [ $? -eq 2 ]; then

2763 m4 -D_TITLE_="入力" \

2764 -D_FORM_="`genform $1`" \

2765 -D_DUMPTABLE_="" \

2766 $layout/html.m4.html $layout/form+dump.m4.html

2767 return

2768 fi

2769 cond=""

2770 for pk in `gettblpkey $tbl`; do

2771 pv=$(sqlquote $(getpar $pk))

2772 cond="$cond${cond:+ and }$pk=$pv"

2773 done

2774 sql="select rowid from $tbl where $cond;"

2775 rowid=`query "$sql"`

2776 err smail1 - "$sql" "-> rowid=$rowid"

2777

2778 while IFS=: read prompt name keytype type args; do # Read from $1

2779 val=`getpar $name`

2780 if [ -n "$val" ]; then

2781 text="$text

2782 $prompt

2783 $name=$val

2784 ---------------------------------------------------------"

2785 fi

2786 case "$type" in

2787 image|document|file)

2788 fn="`getvalbyid $tbl $name $rowid $tmpd`"

2789 fns=$(echo "$fn"|while read fn; do

2790 err mv $tmpd/$fn.orig $tmpd/$fn

2791 mv $tmpd/$fn.orig $tmpd/$fn

2792 rm $tmpd/$fn.rowid # Remove cache flag

2793 err "`ls $tmpd/$fn`"

2794 echo $fn

2795 done)

2796 files="$files $fns"

2797 ;;

2798 esac

2799 done < $1

2800 err FILES=$files "`ls -lF $tmpd`"

2801 subj="from ${REMOTE_ADDR}"

2802 (echo "$url"

2803 echo "への書き込みがありました。"

2804 echo "------"

2805 echo "$text"

2806 ) | (cd $tmpd &&

2807 err LS="`ls -lF`" &&

2808 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

2809 m4 -D_TITLE_="入力完了" $layout/html.m4.html

2810 echo "以下の内容で送信しました。" | html p

2811 viewtable $1 $tbl \

2812 `query "select rowid from $tbl order by rowid desc limit 1;"`

2813 echo "戻る" | html a "href=\"?\""

2814 }