s4: 32557aa9af94 s4-funcs.sh

s4

view s4-funcs.sh @ 166:32557aa9af94

$SMAIL_TO controls To: header value.

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 29 Jan 2016 10:59:01 +0859
parents	0cc22764899e
children	cdade692e17d

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 cat<<EOF >&5

319 .once $sqo

320 $@

321 EOF

322 cat $sqo

323 }

324

325 ismember() {

326 # $1=user, $2=group

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

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

329 }

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

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

332 }

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

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

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

336 }

337 isgrpowner() (

338 # $1=user, $2=group

339 gn=`sqlquote "$2"`

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

341 err isgrpowner: $sql

342 test -n "`query $sql`"

343 )

344 getgroupadminmails() {

345 # $1=group

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

347 email4group "$1" "$i" ;

348 done

349 }

350 getgroupadmins() { # $1=group

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

352 qgrp=`sqlquote "$1"`

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

354 }

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

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

357 getvalbyid grp $2 \

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

359 }

360 getgroupbyid() {

361 # $1=id|gname

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

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

364 # err ggbyid: `echo $sql`

365 query $sql

366 }

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

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

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

370

371 # Return true when anonymous mode

372 [ "$anonymousmode" ] && return 0

373 # case `getvalbyid blog mode $2` in

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

375 # *closed*)

376 # owner=`getvalbyid blog owner $2`

377 # if isgrp $owner; then

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

379 # elif isuser $owner; then

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

381 # fi

382 # esac

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

384 sql="with getblog as (\

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

386 select blogid from article where id in\

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

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

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

390 select case\

391 when (select author from article where\

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

393 then 'author'\

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

395 then 'open'\

396 when (select val from getmode) is null \

397 then 'open'

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

399 then (select user from grp_adm where \

400 gname=(select val from getowner) and \

401 user='$1')\

402 when (select author from article where\

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

404 then 'user+author'

405 else '' end;"

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

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

408 result=`query "$sql"`

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

410 return 2

411 }

412 linkhome() {

413 # $1=UserOrGroup

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

415 if isuser $1; then

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

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

418 else

419 echo -n "grp+$1"

420 fi

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

422 }

423 acclog() {

424 # $1=table, $2=rowid

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

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

427 query "replace into acclog values('$user', '$1', '$2', '$now');"

428 fi

429 }

430 gecos() (

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

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

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

434 then coalesce(

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

436 $u)

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

438 then coalesce(

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

440 $u)

441 else $u

442 end;"

443 query "$sql"

444 )

445 setpar() {

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

447 }

448 replpar() {

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

450 }

451 getpar() {

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

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

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

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

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

457 fi

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

459 case "$var" in

460 owner)

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

462 echo $user; return

463 elif ismember $user $val; then

464 echo $val; return

465 fi ;;

466 esac

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

468 echo "$val"

469 }

470

471 getpartype() {

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

473 }

474 getparcount() {

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

476 }

477 getparfilename() {

478 # null if type of $1 is not file

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

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

481 }

482 sqlquote() {

483 (v="$1"

484 case "$v" in

485 "") return ;; # null

486 "X'"*) # quoted hex string

487 echo $1 ;;

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

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

490 echo "\"$v\""

491 return ;;

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

493 echo "\"$v\""

494 return ;;

495 *)

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

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

498 else

499 echo "\"$v\""

500 fi ;;

501 esac)

502 }

503 mktempd() {

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

505 }

506 getcachedir() { # $1=maintable

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

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

509 else

510 echo $tmpd/$thumbxy

511 fi

512 }

513 getval() {

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

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

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

517 echo "$user" ;;

518 stamp|datetime)

519 date "+%F %T" ;;

520 serial)

521 (s=`getpar $2`

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

523 *)

524 getpar "$2" "$3";;

525 esac

526 }

527

528 getvalquote() {

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

530 (v=`getval "$@"`

531 case "$v" in

532 "") echo NULL ;;

533 *) sqlquote "$v" ;;

534 esac)

535 }

536 getparquote() {

537 sqlquote `getpar $1`

538 }

539 getbinbyid() {

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

541

542 }

543 getvalbyid() {

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

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

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

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

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

549

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

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

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

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

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

555 return

556 fi

557 done

558 pk=`gettblpkey $1`

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

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

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

562 for kt in s m; do

563 td=${4:-$tmpd}

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

565 t=${1}_$kt

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

567 vcount=1 # count(val)

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

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

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

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

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

573 if [ $kt = m ]; then

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

575 # Reset val to store filenames if type is string

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

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

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

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

580 i=0

581 err gvbid: i=$i vcount=$vcount

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

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

584 i=$((i+1))

585 fn=$c.$i

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

587 case $type in

588 file:*)

589 #file=$td/$val

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

591 # FOR SPEED: Skip file generation if imgcache exists

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

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

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

595 .output $td/$fn.rowid

596 select rowid from $cond $slice;

597 .output $td/$fn

598 select val from $cond $slice;

599 .output $file.content-type

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

601 .output stdout

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

603 EOF

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

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

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

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

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

609 case $type in

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

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

612 *|*:[Aa]pplication*)

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

614 png:- > $file

615 ;;

616 esac

617 ;;

618 *)

619 sq $db<<EOF

620 .output $td/$fn.rowid

621 select rowid from $cond $slice;

622 .output $td/$fn

623 select val from $cond $slice;

624 EOF

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

626 ;;

627 esac

628 done

629 else

630 rm -f $td/$c.count

631 case $type in

632 file:*)

633 echo "$val" \

634 | while read fn; do

635 file=$td/$fn

636 if [ ! -s $file ]; then

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

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

639 | unhexize > $file

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

641 # err TTTTTTTTTTTTTTTT: $type

642 case $type in

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

644 *:[Aa]pplication*)

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

646 png:- > $file ;;

647 esac

648 fi

649 done

650 ;;

651 esac

652 fi

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

654 return

655 fi

656 done

657 done)

658 }

659 getvalbypkey() (

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

661 pk=`gettblpkey $1`

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

663 getvalbyid "$1" "$2" $rowid $4

664 )

665 getvalbycond() {

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

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

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

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

670 getvalbyid "$1" "$2" $rowid "$4"

671 fi

672 }

673 getpwfield() {

674 # getpwfield user column

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

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

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

678 echo "$val"

679 return 0

680 else

681 return 1

682 fi

683 }

684 encode() {

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

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

687 sha1=sha1

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

689 sha1=sha1sum

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

691 sha1=gsha1sum

692 fi

693 fi

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

695 }

696 mycrypt() (

697 key=$1 salt=$2

698 err \$2=$2

699 case $2 in

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

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

702 esac

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

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

705 )

706 hexize() {

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

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

709 hexize="xxd -p"

710 else

711 hexize_hd() {

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

713 }

714 hexize="hexize_hd"

715 fi

716 fi

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

718 }

719 unhexize() {

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

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

722 unhex="xxd -p -r"

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

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

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

726 EOF

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

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

729 # world writable...:)

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

731 fi

732 fi

733 cat $1 | $unhex

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

735 }

736 percenthex() {

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

738 }

739 htmlescape() {

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

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

742 }

743 enascii() {

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

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

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

747 else

748 enascii_now=`date +%FT%T`

749 enascii_sed() {

750 nkf -Z0Z1Z2 \

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

752 }

753 enascii="enascii_sed"

754 fi

755 fi

756 cat "$@" | $enascii

757 }

758 size_h() {

759 i="$1" oi=$1

760 set -- B B KB MB GB TB

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

762 oi=$i

763 i=$((i/1024))

764 shift

765 done

766 echo ${oi}$1

767 }

768 gettblconf() {

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

770 ## tconfs=`sq $db \

771 tconfs=`query \

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

773 fi

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

775 }

776 gettblkeys() {

777 # $1=tbl

778 gettblconf

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

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

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

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

783 case $type in

784 *p*)

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

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

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

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

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

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

791 esac

792 case $type in

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

794 esac

795 done

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

797 }

798 gettblpkey() {

799 # $1=tbl

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

801 }

802 gettblfkey() {

803 (x=`gettblkeys $1`

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

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

806 }

807 gettblcols() {

808 (x=`gettblkeys $1`

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

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

811 }

812 gettbl_s_cols() {

813 (x=`gettblkeys $1`

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

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

816 }

817 gettbl_m_cols() {

818 (x=`gettblkeys $1`

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

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

821 }

822 gettbl_h_cols() {

823 (x=`gettblkeys $1`

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

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

826 }

827 gettbl_coltype() (

828 gettblconf

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

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

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

832 )

833 is_hidden() {

834 # $1=Tbl $2=col

835 gettblconf

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

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

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

839 case $x in

840 *h*) return 0 ;;

841 *) return 1 ;;

842 esac

843 }

844

845 dbsetbyid() {

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

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

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

849 unset primary update

850 gettblconf

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

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

853 #err conf=$conf

854 case ${conf#*=} in

855 p*) primary=1 ;;

856 f*) update=1 ;;

857 u*) ;;

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

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

860 esac

861 #err t=$t

862 type=text fn=""

863 case $conf in

864 */password)

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

866 ;;

867 */image*|*/document*)

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

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

870 ;;

871 esac

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

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

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

875 if [ "$primary" ]; then

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

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

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

879 elif [ "$update" ]; then

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

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

882 else

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

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

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

886 fi

887 )

888 }

889 expire() (

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

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

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

893 )

894 addsession() {

895 # expireをセット

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

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

898 err addsession "$1" exp=$expire

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

900 # Remove old session parameters

901 now=`expire now`

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

903 }

904 gencookie() (

905 for kv; do

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

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

908 done

909 )

910 contenttype() {

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

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

913 }

914 putheader() {

915

916 }

917 putfooter() {

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

919 }

920 getcookie() (

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

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

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

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

925 done

926 )

927 genrandom() {

928 # $1=columns (default: 10)

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

930 }

931 smail() {

932 # smail rcpts subj (file)

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

934 rcpt=`echo $1` # strip newlines

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

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

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

938 }

939 setviastring() {

940 table=$1

941 oifs="$IFS"

942 IFS="&"

943 for us in $2; do

944 k=${us%%=*}

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

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

947 #echo $k=$v

948 done

949 IFS="$oifs"

950 }

951 checkdomain() (

952 # Check the validity of domain by referring DNS

953 item=$1

954 err checkdomain $1

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

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

957 )

958 pwcheck() {

959 # $1=passwd

960 dbpswd=`getpwfield $user pswd`

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

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

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

964 }

965 mypwhash() {

966 mycrypt `cat` `genrandom 5`

967 }

968 wasureta() {

969 user=$1

970 if ! checkdomain $user; then

971 contenttype; echo

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

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

974 putfooter

975 exit 0

976 fi

977 newpswd=`genrandom` # newsalt=`genrandom 5`

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

979 encpswd=`echo $newpswd|mypwhash`

980 dbsetbyid user $user pswd "$encpswd"

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

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

983 $msgdir/mail-newaccount.m4 \

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

985 | smail $user "New Account"

986 }

987 checkauth() {

988 user=`getpar user`

989 skc=`getpar skey` # from cookie

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

991 skey="`getpwfield $user skey`"

992 err user=$user skey=$skey

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

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

995 return 0

996 fi

997 fi

998 pswd=`getpar pswd`

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

1000 wasureta $user

1001 return 1 # wasureta error

1002 fi

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

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

1005 if pwcheck "$pswd"; then

1006 newsession=`genrandom 50`

1007 dbsetbyid user $user skey "$newsession"

1008 gencookie "user=$user" "skey=$newsession"

1009 return 0

1010 fi

1011 return 2 # Password mismatch

1012 }

1013 showlogin() {

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

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

1016 $layout/login.m4.html

1017 exit 0

1018 }

1019 dologin() {

1020 checkauth

1021 st=$?

1022 if [ $st != 0 ]; then

1023 contenttype; echo

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

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

1026 showlogin # and EXIT

1027 fi

1028 }

1029

1030 # Do instant jobs here

1031 dbsetup

1032 trap cleanup INT HUP EXIT

1033 # trap cleanup INT HUP

1034

1035 err() {

1036 echo "$@" 1>&3

1037 }

1038

1039 cgiinit() {

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

1041 tmpf=tmp/stream

1042 tmpd=`tmpd=$tmpdir mktempd`

1043 tmpfiles=$tmpfiles" $tmpd"

1044 addsession $session

1045 getcookie

1046 case "$REQUEST_METHOD" in

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

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

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

1050 # cat > $tmpf # too much?

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

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

1053 s="`cat tmp/stream`"

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

1055 ;;

1056 esac

1057 case "$CONTENT_TYPE" in

1058 *boundary*)

1059 bndry=${CONTENT_TYPE#*boundary=}

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

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

1062 do

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

1064 #echo u=$us

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

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

1067 # err k=$k v=$v

1068 case "$k" in

1069 *:filename)

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

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

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

1073 [Ii]mage/x-xcf)

1074 bzip2 $tmpd/$v

1075 v=${v}.bz2

1076 ;;

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

1078 [Ii]mage/*)

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

1080 ;;

1081 esac

1082 ;;

1083 *)

1084 type='string'

1085 ;;

1086 esac

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

1088 setpar "$k" "$type" "$v"

1089 done

1090 ;;

1091 *)

1092 setviastring par "$s"

1093 ;;

1094 esac

1095 }

1096 email4group() {

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

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

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

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

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

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

1103 and s.key='email'

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

1105 err `echo $sql`

1106 query "$sql"

1107 }

1108 email4groupbyuid() {

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

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

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

1112 uids=`echo "$@"`

1113 uids=`echo $uids|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

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

1119 err email4gByid `echo $sql`

1120 query "$sql"

1121 }

1122 collectemail() (

1123 # Collect email addresses for group $1

1124 for e; do

1125 if isuser "$e"; then

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

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

1128 else

1129 # sql="with recursive allmem as

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

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

1132 # where allmem.val=grp_m.gname)

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

1134 # from allmem a left join grp_mem_s b

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

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

1137 # tmp tmp tmp tmp tmp tmp tmp

1138 qgrp=`sqlquote "$e"`

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

1140 grp_mem g left join grp_mem_s s

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

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

1143 where g.gname=$qgrp;"

1144 err CollectEmail: `echo "$sql"`

1145 query "$sql"

1146 fi

1147 done

1148 )

1149 sendinvitation() (

1150 # $1=email

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

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

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

1154 gecos=`gecos`

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

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

1157 m4 -D_URL_="$urlbase" \

1158 -D_USER_="$name" \

1159 -D_EMAIL_="$1" \

1160 -D_REGIST_="$regist" \

1161 -D_ADMIN_="$admin" \

1162 $msgdir/mail-invite.m4 \

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

1164 return 0

1165 )

1166 emaildomaincheck() {

1167 case "$1" in

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

1169 *@*)

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

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

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

1173 return 2

1174 fi

1175 return 0

1176 ;;

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

1178 esac

1179 }

1180 invite() {

1181 email=`getpar email`

1182 case $email in

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

1184 *@*)

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

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

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

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

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

1190 elif sendinvitation $email; then

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

1192 fi ;;

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

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

1195 esac

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

1197 err addr=$addr

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

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

1200 fi

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

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

1203 $layout/html.m4.html $layout/invite.m4.html

1204 }

1205 regist() {

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

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

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

1209 echo "bye bye" | html p

1210 reutrn

1211 fi

1212 email=`session=$1 getpar invite`

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

1214 cat<<EOF

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

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

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

1218 EOF

1219 return

1220 fi

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

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

1223 # Fake login password to wasureta

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

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

1226 wasureta $email

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

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

1229 addsession $1 # for removal after 1 minute

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

1231 $layout/login.m4.html

1232 return

1233 }

1234 newgrpchk() {

1235 # Check if $1 is existing and

1236 }

1237 groupupdate() {

1238 gname=`getpar gname`

1239 qgname=`sqlquote $gname`

1240 err Enter:groupupdate

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

1242 # See ALSO same job in showgroup()

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

1244 err newgname=$newgname

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

1246 err NewGNAME: gname=$newgname

1247 gname=$newgname

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

1249 replpar gname string "$gname"

1250 fi

1251 # Name confliction check

1252 parow=`getpar rowid`

1253 err parow=$parow

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

1255 query "BEGIN EXCLUSIVE;"

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

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

1258 if [ $count -gt 0 ]; then

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

1260 query "END;"

1261 return

1262 fi

1263 par2table $formdir/grp.def

1264 query "END TRANSACTION;"

1265 # Remove orphan

1266 : <<EOF

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

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

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

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

1271 EOF

1272 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1276 sql="delete from blog where id in

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

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

1279 query "$sql";

1280 fi

1281 fi

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

1283 fi

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

1285 grid=$(query $sql)

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

1287 grp $grid

1288 }

1289 groupman() {

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

1291

1292 GF_STAGE="grpconf"

1293 GF_STAGE=groupupdate

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

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

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

1297 -D_DUMPTABLE_="syscmd(cat)" \

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

1299 }

1300 userconf() {

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

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

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

1304 }

1305 groupconf() {

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

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

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

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

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

1311 err gcon \$1=$1 rowid=$rowid

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

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

1314 }

1315 mems() {

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

1317 kwd=`getpar kwd`

1318 listmember $kwd

1319 }

1320 grps() {

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

1322 kwd=`getpar kwd`

1323 listgroup $kwd \

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

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

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

1327 $layout/form+dump.m4.html

1328 }

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

1330 gpg=`getpar grp`

1331 grid=${1:-$gpg}

1332 grp=`getgroupbyid "$grid"`

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

1334 ## . ./s4-blog.sh

1335 jg=`getpar joingrp`

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

1337 err jg=$jg, grp=$grp

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

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

1340 fi

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

1342 showgroup "$grid"

1343 }

1344 showhome() {

1345 # $1=userRowIdToShow

1346 err showhome \$1=$1

1347 case "$1" in

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

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

1350 esac

1351 err ShowHome: uname=$uname

1352 gecos=`gecos "$uname"`

1353 err SH:gecos=$gecos

1354 GF_VIEWONLY=1

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

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

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

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

1359 # Display folders

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

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

1362 and type like 'file:%';"

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

1364 nfile=`query "$sql"`

1365 err nfile=$nfile

1366 if [ $nfile -gt 0 ]; then

1367 conflink="$conflink /

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

1369 fi

1370 fi

1371 . ./s4-blog.sh

1372

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

1374 echo "$gecos さん" > $tf

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

1376

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

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

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

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

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

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

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

1384 echo '">'

1385 } > $imf

1386 fi

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

1388 val='$uname';"`

1389 listblog $uname > $bf

1390 listgroupbytable $formdir/grp.def $cond \

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

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

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

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

1395 -D_NBLOG_="$nblog" \

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

1397 $layout/html.m4.html $layout/home.m4.html

1398

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

1400 # Display NEWS

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

1402 new10=`DT_CHLD=article:blogid \

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

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

1405 cont=$((cont-1))

1406 err newcount=$cont

1407 if [ $cont -gt 0 ]; then

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

1409 echo "$new10"

1410 fi

1411 cat<<EOF

1412 <div class="fold">

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

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

1415 <div>

1416 EOF

1417 cgi_form searchart<<EOF

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

1419 EOF

1420 cat<<EOF

1421 </div></div>

1422 EOF

1423 fi

1424 #

1425 # Record access log

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

1427 }

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

1429 contenttype; echo

1430 err commission: "$@"

1431 gname=`getgroupbyid $1`

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

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

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

1435 grp_reg_adm "$@"

1436 else

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

1438 fi

1439 }

1440 listgroupbytable() {

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

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

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

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

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

1446 NGsql="select distinct tag from\

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

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

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

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

1451 err ListGRP: query sql="$sql"

1452 for tag in `query "$sql"`

1453 do

1454 err ListGrp: tag=$tag

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

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

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

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

1459 as rowid,

1460 gname,

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

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

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

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

1465 err PersonalGroupList= `echo $sql`

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

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

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

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

1470 echo '</table>'

1471 done

1472 echo '</div>'

1473 }

1474 iconhref() (

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

1476 data=`percenthex $1`

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

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

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

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

1481 )

1482 iconhref2() (

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

1484 src=$1

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

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

1487 )

1488 listentry() (

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

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

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

1492 cond=''

1493 offset=`getpar offset`

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

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

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

1497 limit=30

1498 dir=`getcachedir "$1"`

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

1500 hrb="$myname?home"

1501 deficon=person-default.png

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

1503 gcs=gecos

1504 else # if group

1505 hrb="$myname?grp"

1506 deficon=group-default.png

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

1508 gcs=name

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

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

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

1512 err tagconv=$tagconv

1513 fi

1514 fi

1515 if [ ! -d $dir ]; then

1516 mkdir -p $dir

1517 fi

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

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

1520 fi

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

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

1523 fi

1524

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

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

1527 ##qgrp=`sqlquote $grp`

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

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

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

1531 b.tag,

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

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

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

1535 then '(ADMIN)'

1536 when '$iamowner' = '' then ''

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

1538 from $tbl a left join

1539 (select $nm as name,

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

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

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

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

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

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

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

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

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

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

1550 cgi_form $stage <<EOF

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

1552 `cgi_text kwd $kwd`</label>

1553 EOF

1554 echo '</div>'

1555 fi

1556 cat<<EOF

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

1558 EOF

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

1560 cat<<EOF

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

1562 `cgi_submit 次の${limit}件`

1563 `cgi_hidden kwd "$kwd"`

1564 `cgi_hidden stage "$stage"`

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

1566 EOF

1567 fi

1568 if [ $offset -gt 0 ]; then

1569 cat<<EOF

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

1571 `cgi_submit 前の${limit}件`

1572 `cgi_hidden stage "$stage"`

1573 `cgi_hidden kwd "$kwd"`

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

1575 EOF

1576 fi

1577

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

1579

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

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

1582 err name=$name owner=$ownerp lnk=$lnk

1583 err newlnk=$lnk

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

1585 # Pick up only first icon

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

1587 | m4 $tagconv

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

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

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

1591 else

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

1593 fi

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

1595 echo "</div>"

1596 done

1597 )

1598 listmember() {

1599 listentry user "$@"

1600 }

1601 listgroup() {

1602 listentry group "$@"

1603 }

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

1605 # gname=`getpar gname`

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

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

1608 # # See ALSO same job in groupman()

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

1610 # ###newgname=$gname

1611 # err gname=$gname newgname=$newgname

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

1613 # err NewGNAME: gname=$newgname

1614 # gname=$newgname

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

1616 # replpar gname string "$gname"

1617 # fi

1618 # par2table $formdir/grp.def

1619 # fi

1620 grp=`getgroupbyid $1`

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

1622 if isgroup "$grp"; then

1623 tf=$tmpd/title.$$

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

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

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

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

1628 -D_DUMPTABLE_="" \

1629 $layout/form+dump.m4.html

1630 else # if $grp is removed at par2table

1631 listgroup

1632 fi

1633 }

1634 showgroupsub() {

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

1636 rowid=$2

1637 grp=`getgroupbyid $2`

1638 qgrp=`sqlquote $grp`

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

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

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

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

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

1644 return

1645 fi

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

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

1648 viewtable $1 grp $rowid

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

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

1651 iamowner=$rowid

1652 fi

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

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

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

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

1657 cat<<EOF

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

1659 <div class="fold">

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

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

1662 <div>

1663 `cgi_textarea message "" "cols=60"`

1664 `cgi_submit 送信`

1665 `cgi_reset リセット`

1666 </div>

1667 `cgi_hidden grp $rowid`

1668 </div></form>

1669 EOF

1670 fi

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

1672 err ismember $user $grp

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

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

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

1676 and key='email';"`

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

1678 and key='email';"

1679 err email=$eml

1680 cat <<EOF

1681 <div class="fold">

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

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

1684 <div>

1685 EOF

1686 cgi_form grp <<EOF

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

1688 <table class="b">

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

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

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

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

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

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

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

1696 </table>

1697 `cgi_hidden grp $rowid`

1698 EOF

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

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

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

1702 fi

1703 echo '</div></div>'

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

1705 cgi_form searchart<<EOF

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

1707 `cgi_hidden owner $grp`

1708 EOF

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

1710 DT_CHLD=article:blogid \

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

1712

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

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

1715 cm="?commission+$rowid"

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

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

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

1719 }

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

1721 err GRP_ACTION:IN

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

1723 grp=`getgroupbyid "$grid"`

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

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

1726 fi

1727 if ! ismember $user $grp; then

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

1729 fi

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

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

1732

1733 isowner=""

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

1735 usel=`getpar usel`

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

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

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

1739 text=`getpar text`

1740

1741 rm=`getpar rm` cfm=`getpar confirm`

1742 err rm=$rm cfm=$cfm

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

1744 if [ "$isowner" ]; then

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

1746 # Eliminate

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

1748 for tbl in grp_mem grp_mem_s grp_mem_m; do

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

1750 # echo "sql=$sql"

1751 query "$sql"

1752 err rmGRPuser "$sql"

1753 done

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

1755 #err num=$num

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

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

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

1759 # err `echo "$sql"`

1760 html pre<<EOF

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

1762 `query "$sql"`

1763 EOF

1764 fi

1765 else

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

1767 return

1768 fi

1769 else # not Group Owner

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

1771 return

1772 fi

1773 cat<<EOF

1774

1775 EOF

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

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

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

1779 return 0

1780 fi

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

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

1783 $url

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

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

1786 ----------------------------------------------------------

1787 $text

1788 EOF

1789 if [ $? = 0 ]; then

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

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

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

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

1794 html pre<<EOF

1795 `query "$sql"`

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

1797 EOF

1798 err SendDone: `echo $sql`

1799 fi

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

1801 grp_reg_adm $grid $usel

1802 fi

1803 fi

1804 # New entry

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

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

1807 from (select gname,user from grp_mem

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

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

1810 b using(gname,user))

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

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

1813 case

1814 when (select user from grp_adm where

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

1816 and user=a.name) is not null

1817 then 'k'

1818 else ''

1819 end || a.rowid||

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

1821 (select rowid,name from user where name in

1822 (select user from grp_mem where

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

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

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

1826 err grpaction: `echo $sql`

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

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

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

1830 tf=$tmpd/title.$$

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

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

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

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

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

1836 </p></div></div>"

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

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

1839 <div>

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

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

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

1843 </div>

1844 </div>"

1845 cgi_form grpaction<<EOF \

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

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

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

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

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

1851 $layout/form+dump.m4.html

1852 <div class="fold">

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

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

1855 <div>

1856 `cgi_textarea text "" cols=40`

1857 </div>

1858 </div>

1859 ${isowner:+$cmmsg$excmsg}

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

1861 <table>

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

1863 `cgi_hidden grp $grid`

1864 </table>

1865 EOF

1866 }

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

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

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

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

1871 return

1872 fi

1873 owner=`getvalbyid blog owner $rowid`

1874 title=`getvalbyid blog title $rowid`

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

1876 | m4 -D_TITLE_="修正" \

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

1878 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

1881 }

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

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

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

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

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

1887 return

1888 fi

1889 owner=`getvalbyid blog owner $blogrowid`

1890 title=`getvalbyid blog title $blogrowid`

1891 author=`getvalbyid article author $rowid`

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

1893 if isgrpowner $user $owner; then

1894 : EDIT OK

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

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

1897 return

1898 fi

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

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

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

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

1903 > $tmpout

1904 rm -f /tmp/editart.out

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

1906 m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

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

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

1909 -D_BLOGS_= -D_DUMPTABLE_= \

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

1911 }

1912 send2mem() {

1913 rowid=`getpar grp`

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

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

1916 return

1917 fi

1918 message=`getpar message`

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

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

1921 return

1922 fi

1923 grp=`getgroupbyid $rowid`

1924 members=`collectemail $grp`

1925 # smail rcpt subj (file)

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

1927 $urlbase?grp+$rowid

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

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

1930

1931 $message

1932 EOF

1933 cat<<EOF

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

1935 <pre>

1936 $members

1937 </pre>

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

1939 EOF

1940 }

1941 joingrpadmit() {

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

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

1944 echo "bye bye" | html p; return

1945 fi

1946 t_usr=`session=$2 getpar user`

1947 t_grp=`session=$2 getpar group`

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

1949 m4 -D_TITLE_="joingrp" $layout/html.m4.html

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

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

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

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

1954 return

1955 fi

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

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

1958 fi

1959 case $1 in

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

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

1962 *)

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

1964 return ;;

1965 esac

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

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

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

1969 body="グループ $t_grp

1970 に

1971 $t_usr

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

1973 しました。"

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

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

1976 echo "$body" | html p

1977 }

1978

1979 joingrprequest() {

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

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

1982 addsession $jss +${memoplimitdays}days

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

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

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

1986 $url

1987 グループ $1

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

1989

1990 承認する:

1991 $urlbase?joingrpadmit+yes+$jss

1992

1993 白紙に戻す:

1994 $urlbase?joingrpadmit+no+$jss

1995 EOF

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

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

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

1999 }

2000 joingrp() {

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

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

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

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

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

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

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

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

2009 return

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

2011 : # Do not pursue those who leave

2012 else # adding user is $user itself

2013 case `getgroupattr $1 regmode` in

2014 moderated)

2015 joingrprequest "$@" # Request only

2016 return

2017 ;;

2018 *)

2019 ;;

2020 esac

2021 fi

2022 qgname=`sqlquote $1`

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

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

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

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

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

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

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

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

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

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

2033 # Coming here means newly created group

2034 sql="select case\

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

2036 then (select user from grp_mem\

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

2038 else '' end;"

2039 err NewGrpChk: $sql

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

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

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

2043 fi

2044 fi

2045 else

2046 echo $msg

2047 fi

2048 else

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

2050 fi

2051 else

2052 query "delete from grp_mem $cond;

2053 delete from grp_mem_s $cond;

2054 delete from grp_mem_m $cond;"

2055 fi

2056 }

2057 grp_reg_adm() {

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

2059 grid=$1

2060 grp=`getgroupbyid "$1"`

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

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

2063 fi

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

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

2066 fi

2067 shift

2068 for urid; do

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

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

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

2072 fi

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

2074 err ismember $newadm $grp

2075 if ismember $newadm $grp; then

2076 # OK, go ahead

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

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

2079 # confirm insertion

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

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

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

2083 else

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

2085 fi

2086 fi

2087 showgroup $grid

2088 done

2089 }

2090 dumptable() {

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

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

2093 # $DT_VIEW sets link

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

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

2096 VIEW=${DT_VIEW-replyblog}

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

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

2099 fi

2100 # $DT_CHLD=ChildTable:BindColumn

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

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

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

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

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

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

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

2108 '1970-01-01'))"

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

2110 dt_class=" td2r td3r"

2111 fi

2112 # Construct join expression

2113 eav="" scols=""

2114 pk=`gettblpkey $2`

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

2116 substr=${substr:-val}

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

2118 case $col in

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

2120 continue ;; # built-in column name

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

2122 *) as=${col} ;;

2123 esac

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

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

2126 done

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

2128 sql="select \

2129 a.rowid as ID,\

2130 $cnt\

2131 $scols from $2 a left join\

2132 (select $pk,$eav,

2133 max(case key when 'owner'

2134 then coalesce((select val from user_s us

2135 where us.key='gecos' and us.name=c.val), val) end) as gecos

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

2137 err SQL=`echo "$sql"`

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

2139 <div class="dumptable">

2140 <table class="b$dt_class">

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

2142 </table>

2143 </div>

2144 EOF

2145 }

2146

2147 par2table() (

2148 # copy current parameters of par into destination table

2149 # $1=definition-file

2150 # Using $user and $session

2151 # Return value:

2152 # 0: Stored successfully

2153 # 1: Insufficient fillings

2154 # 2: No permission to modify the record

2155 # 3: Invalid rowid

2156 # 4: SUCCESS to delete

2157 # 5: Stop deletion for lack of confirm check

2158 # 6: Password length too short

2159 # 7: Password mismatch

2160 # 8: Old password incorrect

2161 rowid=`getpar rowid`

2162 err ...........rowid=$rowid

2163 if [ ! -e $1 ]; then

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

2165 exit 1

2166 fi

2167 tbl=${1%.def}

2168 tbl=${tbl##*/}

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

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

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

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

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

2174 ##err p2t:grp:q $sql

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

2176 else

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

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

2179 where rowid=$rowid;"`}

2180 fi

2181 ### err rowowner=$rowowner

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

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

2184 return 2

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

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

2187 return 3

2188 fi

2189 rm=`getpar rm` cfm=`getpar confirm`

2190 # Editing existent entry

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

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

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

2194 return 4

2195 else

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

2197 return 5

2198 fi

2199 fi

2200 fi

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

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

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

2204 # Update of existing record

2205 for col in `gettblcols $tbl`; do

2206 val=`getparquote $col`

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

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

2209 query "update $tbl set $col=$val where rowid=$rowid;"

2210 done

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

2212 for col in `gettblpkey $tbl`; do

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

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

2215 done

2216 else

2217 # New entry

2218 # Generate values() for primary keys

2219 for col in `gettblpkey $tbl`; do

2220 # Genuine primary keys for _m and _s

2221 val=`getvalquote $tbl $col`

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

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

2224 done

2225 err pval=$pval

2226 for col in `gettblfkey $tbl`; do

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

2228 val=`getvalquote $tbl $col`

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

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

2231 done

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

2233 err formaster=$formaster

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

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

2236 return 1

2237 fi

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

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

2240 ## Insertion to master table, done

2241 fi

2242

2243 for kt in s m; do

2244 tb2=${tbl}_$kt

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

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

2247

2248 # First, check update of existing entries in _m

2249 if [ $kt = m ]; then

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

2251 sql=""

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

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

2254 # v=address.1.22

2255 st_rowid=${v##*.}

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

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

2258 ##case `getpartype $v` in

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

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

2261 case `getpar action.$v` in

2262 rm)

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

2264 newsql="delete from $tb2"

2265 else

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

2267 fi ;;

2268 edit)

2269 case `gettbl_coltype $tbl/$origcol` in

2270 image|document|binary)

2271 file=$tmpd/`getparfilename $v`

2272 err type=file=$file

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

2274 bn=${file##*/}

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

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

2277 type=\"file:$ct\"

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

2279 cachedir=`getcachedir "$tbl/$rowid"`

2280 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

2281 rm -r $cachedir

2282 ;;

2283 *)

2284 newsql="update $tb2 set val=(select val from par where var \

2285 like '$col.%.$st_rowid')"

2286 ;;

2287 esac

2288 ;;

2289 *) # maybe "keep", do not modify value

2290 continue

2291 ;;

2292 esac

2293 # err newsql=$newsql

2294 sql=$sql$nl"$newsql where rowid=$st_rowid;"

2295 done

2296

2297 if [ x"$bin" = x"NULL" ]; then

2298 err repl:normal sql=`echo $sql`

2299 query "$sql

2300 delete from $tb2 where type='string' and val='';"

2301 err repl:normal done

2302 else

2303 sqlfile="$tmpd/sqlf.$$"

2304 echo "$sql" > $sqlfile

2305 err repl:sqlfile=`ls -lF $sqlfile`

2306 query ".read $sqlfile"

2307 err repl:done

2308 fi

2309 # Rest of kt==m: set multiple mode

2310 nr=`getparcount $col`

2311 else

2312 nr=1 # for kt==s, number of records is 1

2313 fi

2314

2315 i=0

2316 while [ $i -lt $nr ]; do

2317 limit="limit 1 offset $i"

2318 i=$((i+1)) # increase beforehand against continue

2319 val=`getvalquote $tbl $col "$limit"`

2320 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

2321 err $col=$val

2322 bin=NULL

2323 err partype$col=`getpartype $col "$limit"`

2324 case $ptype in

2325 file) file=$tmpd/`getparfilename $col "$limit"`

2326 err parfile-$col=$file

2327 [ -z "$file" ] && continue

2328 bin="X'"$(hexize $file)"'"

2329 ct=`file --mime-type $file|cut -d' ' -f2`

2330 type=\"file:$ct\" ;;

2331 "*"*) continue ;; # foreign table

2332 *) type=\"string\" ;;

2333 esac

2334 case `gettbl_coltype $tbl/$col` in

2335 password) # special care for password

2336 # name={password,pswd1,pswd2}

2337 p1=`getpar pswd1 "$limit"`

2338 if [ -z "$p1" ]; then

2339 continue # SKIP password setting, if p1 is empty

2340 else

2341 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

2342 ## err pswd=$pswd

2343 if pwcheck "$pswd"; then

2344 if [ x"$p1" = x"$p2" ]; then

2345 case "$p1" in

2346 ??????????*) ;;

2347 *) echo "パスワードは10字以上にしてください。" | html p

2348 return 6;;

2349 esac

2350 val="\"`echo $p1|mypwhash`\""

2351 else

2352 echo "2つの新パスワード不一致" | html p

2353 return 7

2354 fi

2355 else

2356 echo "旧パスワード違います" | html p

2357 return 8

2358 fi

2359 fi

2360 ;;

2361 esac

2362 err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

2363 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

2364 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

2365 if [ x"$bin" = x"NULL" ]; then

2366 err Normal-query: `echo $sql`

2367 query "$sql"

2368 else

2369 sqlfile="$tmpd/query.$$"

2370 echo "$sql" > $sqlfile

2371 err sqlfile=`ls -lF $sqlfile`

2372 query ".read $sqlfile"

2373 fi

2374 err p2t done

2375 done

2376 done

2377 done

2378 return 0

2379 err donee)

2380 )

2381 par2table_old() {

2382 # copy current parameters of par into destination table

2383 # $1=dst-table $2=definition-file

2384 # Using $user and $session

2385 rowid=`getpar rowid`

2386 if [ -n "$rowid" ]; then

2387 rm=`getpar rm` cfm=`getpar confirm`

2388 if [ x"$rm$cfm" = x"yesyes" ]; then

2389 sq $db "delete from $1 where rowid=$rowid and owner=\"$user\""

2390 return

2391 fi

2392 fi

2393 cat $2 \

2394 | (cols=""

2395 while IFS=: read prompt name type args; do

2396 [ x"$name" = x"stage" ] && continue

2397 if [ -n "$rowid" ]; then

2398 val=`getpar $name|sed -e 's/\"/\"\"/g'`

2399 sq $db "update $1 set $name=\"$val\" where rowid=$rowid and owner=\"$user\""

2400 else

2401 eav=$eav${eav+,}" max(case var when '$name' then val end)"

2402 fi

2403 done

2404 [ -n "$rowid" ] && return

2405 cond="where sessid='$session' group by sessid"

2406 sq $db "replace into $1 select $eav,\"$user\" from par $cond"

2407 # Think over again about putting $user

2408 )

2409 }

2410 genform() {

2411 # $1 = form definition file

2412 # $2, $3 (optional)= table name and ROWID

2413 # If $GF_VIEWONLY set and nonNull, output values without form

2414 # If $GF_HIDDEN set, use it hidden values

2415 # If $GF_OWNER set, use it as value of name="owner"

2416 # If $GF_STAGE set, use it as value of name="stage"

2417 forms="" hiddens="" rowid=$3

2418 if [ ! -e "$1" ]; then

2419 echo "そのようなデータベースはないようです($2)。" | html p

2420 return

2421 elif [ -n "$2" ]; then

2422 err genform1: "select * from $2 where rowid='$rowid'"

2423 rec=`query "select * from $2 where rowid='$rowid';"`

2424 if [ -z "$rec" ]; then

2425 pk=`gettblpkey $2`

2426 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

2427 err "select rowid from $2 where $pk='$rowid';"

2428 rec=`query "select rowid from $2 where $pk='$rowid';"`

2429 err rec-rowid=$rec

2430 rowid=$rec

2431 rec=$3

2432 fi

2433 if [ -z "$rec" ]; then

2434 echo "そんなレコードはないみたいね..." | html p

2435 return

2436 fi

2437 fi

2438 if [ -z "$GF_VIEWONLY" ]; then

2439 rm='<input id="rm" name="rm" type="checkbox"

2440 value="yes"><label for="rm">このエントリの削除</label>

2441 <span>ほんとうに消しますよ(確認)!

2442 <input name="confirm" type=checkbox value="yes">はい</span>'

2443 fi

2444 # Image Cache dir

2445 err genform: getcache=$2/$rowid

2446 td=`getcachedir "$2/$rowid"`

2447 while IFS=: read prompt name keytype type args; do

2448 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

2449 sp="${args:+ }"

2450 form="" val=""

2451 if [ -n "$rowid" ]; then

2452 # err genform2a: Seeking for "$2.$name, type=$type"

2453 val=`getvalbyid $2 $name $rowid $td|htmlescape`

2454 err genform3a: getvalbyid $2 $name $rowid $td

2455 err genform3b: val="[$val]"

2456 fi

2457 if [ -n "$GF_VIEWONLY" ]; then

2458 is_hidden "$2" "$name" && continue

2459 fi

2460 case "$type" in

2461 text*)

2462 cgiform=cgi_multi_$type

2463 if [ -s $td/$name.count -a -n "$val" ]; then

2464 form=`$cgiform $name $td`

2465 val=$(echo "$val"|

2466 while read fn; do

2467 echo "<tr><td>`cat $td/$fn`</td></tr>$nl"

2468 done)

2469 val="<table>$nl$val$nl</table>"

2470 else

2471 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

2472 err genform: cgi_$type $name $val "$args"

2473 form=`cgi_$type $name "$val" "$args"`

2474 fi

2475 ;;

2476 [Rr][Aa][Dd][Ii][Oo])

2477 fh="<label><input type=\"radio\" name=\"$name\""

2478 form="`echo $args|sed -e \

2479 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

2480 ;;

2481 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

2482 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

2483 ;;

2484 [Ss][Ee][Ll][Ee][Cc][Tt])

2485 fh="<select name=\"$name\">$nl"

2486 form=$(for l in $args; do

2487 echo "<option value=\"${l#*=}\">${l%=*}</option>"

2488 done)

2489 if [ -n "$val" ]; then

2490 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

2491 fi

2492 form="$fh$form</select>"

2493 ;;

2494 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

2495 if [ -s $td/$name.count ]; then

2496 form=`cgi_multi_file $name $td "$args"`

2497 if [ -n "$val" ]; then

2498 hrfb="$myname?showattc+$2_m"

2499 val=$(echo "$val" \

2500 | while read fn; do

2501 data=`percenthex $td/$fn`

2502 #ct=`cat $td/$fn.content-type`

2503 ct=`file --mime-type $td/$fn|cut -d' ' -f2`

2504 ri=`cat $td/$fn.rowid`

2505 ## err fn=$fn, name=$name, ri=$ri; ls -lF $td 1>&3

2506 #imgsrc="<img src=\"data:$ct,$data\">"

2507 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

2508 iconhref $td/$fn "$hrfb+$ri" ""

2509 done)

2510 fi

2511 else

2512 form="<input type=\"file\" name=\"$name\" $args>"

2513 if [ -n "$val" ]; then

2514 imgs=$(echo "$val"\

2515 |while read fn;do

2516 data=`percenthex $td/$fn`

2517 echo "<img src=\"data:image/png,$data\">$fn<br>"

2518 done)

2519 form=$form"<br>$imgs"

2520 val=$imgs # 2015-06-15

2521 else

2522 form="<input type=\"file\" name=\"$name\" $args>"

2523 fi

2524 fi

2525 ;;

2526 [Hh][Ii][Dd][Dd][Ee][Nn])

2527 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

2528 args="value=\"$GF_STAGE\""

2529 fi

2530 form="<input type=\"hidden\" name=\"$name\" $args>"

2531 prompt='' # Remove prompt

2532 ;;

2533 [Aa][Uu][Tt][Hh][Oo][Rr])

2534 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

2535 prompt="" ;;

2536 [Oo][Ww][Nn][Ee][Rr])

2537 val=${GF_OWNER:-$val}

2538 val=${val:-$user}

2539 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

2540 prompt="" ;;

2541 [Uu][Ss][Ee][Rr])

2542 # XXX: is null $user ok?

2543 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

2544 [ -n "$GF_VIEWONLY" ] && continue

2545 form="$user"

2546 ;;

2547 [Pp]assword)

2548 [ -n "$GF_VIEWONLY" ] && continue

2549 form="`cgi_passwd`"

2550 val=""

2551 ;;

2552 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

2553 if [ -z "$rowid" ]; then

2554 val=$((($(date +%s)-1433084400)/10))c$$

2555 fi

2556 ## form="<input type=\"hidden\" name=\"serial\" value=\"$val\">"

2557 ## 2015-07-31

2558 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

2559 prompt="" ;;

2560 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

2561 prompt=""

2562 ;;

2563 parent|path|blog*)

2564 prompt=""

2565 ;;

2566 "*"*)

2567 tail=$tail"``"

2568 continue ;;

2569 esac

2570 if [ -n "$prompt" ]; then

2571 if [ -n "${GF_VIEWONLY}" ]; then

2572 form=$val

2573 else

2574 :

2575 fi

2576 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

2577 else

2578 hiddens=$hiddens$nl"$form"

2579 fi

2580 done < $1

2581 # enctype="multipart/form-data"

2582 cat<<EOF

2583 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

2584 ${rowid:+$rm}

2585 <table class="b $2">

2586 $forms

2587 </table>$hiddens

2588 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

2589 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

2590 EOF

2591 if [ -z $GF_VIEWONLY ]; then

2592 cat<<EOF

2593 <input type="submit" name="sub" value="OK">

2594 <input type="reset" name="res" value="Reset">

2595 EOF

2596 fi

2597 cat<<EOF

2598 </form>

2599 $tail

2600 EOF

2601 }

2602 edittable() {

2603 # $1=form-def $2=table $3 rowid

2604 genform "$@"

2605 }

2606 viewtable() {

2607 GF_VIEWONLY=1 genform "$@"

2608 }

2609 showattc() {

2610 # $1=table_m $2=rowid

2611 err \$1=$1 \$2=$2

2612 if ! isfilereadable $user $1 $2; then

2613 contenttype; echo

2614 echo "このファイルは管理者のみしか見られません" | html p

2615 putfooter; exit

2616 fi

2617 idir=`umask 002; mktempd` || exit 1

2618 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

2619 bin=$idir/$myname-$$.bin

2620 sql="select quote(bin) from $1 where rowid='$2';"

2621 err showattc: sql: $sql

2622 sq $db "$sql" | unhexize > $bin

2623 tv=`query "select type,val from $1 where rowid='$2';"`

2624 type=${tv%\|*} fn=${tv#*\|}

2625 err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

2626 ct=${type#file:}

2627 case $ct in # all text/* changed to text/plain

2628 text/*)

2629 charset=`nkf -g $bin|cut -d' ' -f1`

2630 case $charset in

2631 ASCII*) charset="" ;;

2632 esac

2633 ct="text/plain${charset:+; charset=$charset}"

2634 ;;

2635 esac

2636 contenttype "$ct"

2637 echo "Content-Disposition: filename=\"$fn\""

2638 echo "Content-Length: " `cat $bin | wc -c`; echo

2639 #echo "Content-Type: " ${type#file:}; echo

2640 cat $bin

2641 }

2642 #

2643 # Some default stupid handler on CGI values

2644 #

2645 default_storedb() {

2646 # ARG: $1=table-def-file

2647 # RET: $tbl=table-name, $col=mail-column, $cols=columns

2648 tbl=`basename $1`

2649 tbl=${tbl%.def}

2650 cols="`grep :text $1|cut -d: -f2`"

2651 col=`echo "$cols"|head -1`

2652 vcol=`getpar $col`

2653 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

2654 if [ -n "$vcol" ]; then

2655 par2table $1

2656 else

2657 return 2 # No insertion occurred

2658 fi

2659 }

2660

2661 default_view() { # $1=def-file

2662 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

2663 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

2664 default_storedb "$@"

2665 query "select rowid from $tbl order by rowid desc;" \

2666 | while read rowid; do

2667 viewtable $1 $tbl $rowid

2668 done | m4 -D_TITLE_="$tbl" \

2669 -D_FORM_="`genform $1`" \

2670 -D_DUMPTABLE_="syscmd(cat)" \

2671 $layout/html.m4.html $layout/form+dump.m4.html

2672 }

2673 default_viewtext() { # $1=def-file

2674 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

2675 default_storedb "$@"

2676 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

2677 | m4 -D_TITLE_="$tbl" \

2678 -D_FORM_="`genform $1`" \

2679 -D_DUMPTABLE_="syscmd(cat)" \

2680 $layout/html.m4.html $layout/form+dump.m4.html

2681 }

2682 default_smail() {

2683 default_storedb "$@"

2684 if [ $? -eq 2 ]; then

2685 m4 -D_TITLE_="入力" \

2686 -D_FORM_="`genform $1`" \

2687 -D_DUMPTABLE_="" \

2688 $layout/html.m4.html $layout/form+dump.m4.html

2689 return

2690 fi

2691 cond=""

2692 for pk in `gettblpkey $tbl`; do

2693 pv=$(sqlquote $(getpar $pk))

2694 cond="$cond${cond:+ and }$pk=$pv"

2695 done

2696 sql="select rowid from $tbl where $cond;"

2697 rowid=`query "$sql"`

2698 err smail1 - "$sql" "-> rowid=$rowid"

2699

2700 while IFS=: read prompt name keytype type args; do # Read from $1

2701 val=`getpar $name`

2702 if [ -n "$val" ]; then

2703 text="$text

2704 $prompt

2705 $name=$val

2706 ---------------------------------------------------------"

2707 fi

2708 case "$type" in

2709 image|document|file)

2710 fn="`getvalbyid $tbl $name $rowid $tmpd`"

2711 fns=$(echo "$fn"|while read fn; do

2712 err mv $tmpd/$fn.orig $tmpd/$fn

2713 mv $tmpd/$fn.orig $tmpd/$fn

2714 rm $tmpd/$fn.rowid # Remove cache flag

2715 err "`ls $tmpd/$fn`"

2716 echo $fn

2717 done)

2718 files="$files $fns"

2719 ;;

2720 esac

2721 done < $1

2722 err FILES=$files "`ls -lF $tmpd`"

2723 subj="from ${REMOTE_ADDR}"

2724 (echo "$url"

2725 echo "への書き込みがありました。"

2726 echo "------"

2727 echo "$text"

2728 ) | (cd $tmpd &&

2729 err LS="`ls -lF`" &&

2730 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

2731 m4 -D_TITLE_="入力完了" $layout/html.m4.html

2732 echo "以下の内容で送信しました。" | html p

2733 viewtable $1 $tbl \

2734 `query "select rowid from $tbl order by rowid desc limit 1;"`

2735 echo "戻る" | html a "href=\"?\""

2736 }