s4: e4b929e40fa8 s4-funcs.sh

s4

view s4-funcs.sh @ 490:e4b929e40fa8

Add feature of limiting date range of POST count

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 08 Jun 2018 09:36:21 +0900
parents	075897fee2c0
children	c5d6e12306d4

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

26 #thumbxy=120x120

27 thumbxy=96x96

28 iconxy_S=80x80

29 iconxy_M=400x400

30 maximagexy=1600x1600

31 ### maximagexy=400x400

32 file_accept='accept="image/*,text/*,audio/*,application/vnd.oasis.*,application/pdf,application/x-*"'

33 blogreadflagrowid=0

34 blogcutoffflagrowid=-1

35 querylog=$tmpdir/query.log

37 tconfs=""

38 imgcached=cache/img.`date +%Y/%m`

39 conftbl=_tblconf

40 nl="

41 "

42 iconcachekey="profimgcache_S"

43 case "$HTTP_USER_AGENT" in

44 *i[Pp]hone*|*[Aa]ndroid*) touchpanel=1 ;;

45 *) touchpanel="" ;;

46 esac

47 . ./s4-cgi.sh

49 : <<EOF

51 !! 検索等でblogテーブル参照時は sql4readableblogs() で定義される

52 !! readableblogs テーブルを使うこと

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

54 レポート提出管理

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

57 [2016]

58 7/12 根本への反省

59 * cgi自身の $1, $2 での切り替えでなく、CGI変数での受け渡しにすべき。

60 arg1/arg2/arg3 的に $1 に / 区切りでつけた方がよかったかな。

62 [以下2015]

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

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

65 Web

66 締切設定

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

69 ○自分の提出物リスト

71 7/19 ○設置

72 ○一斉送信

73 ○getparfilename の tmpd の扱い

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

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

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

77 ○添付ファイル回収

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

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

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

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

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

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

85 ○レポート提出モード

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

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

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

91 7/9 ○管理者の追加

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

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

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

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

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

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

100 ○グループ検索

101

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

103

104

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

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

107

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

109 ○6/1 par2tableを triplex 対応に

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

111 →とすると一気に

112

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

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

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

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

117

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

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

120 ## 1. passwd

121 ## 2. grp

122 ## 3. grp_mem

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

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

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

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

127

128 ## ● listの定義:

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

130 ## ● hashの定義:

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

132

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

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

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

136 ## csv2sq3 で .csv.sq3 の Makefile

137

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

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

140 ## type := root | comment

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

142 ## unique(id, filename)

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

144

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

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

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

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

149 ## できたー!

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

151 ↓

152 ↓以下に変更

153 with recursive allmem as

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

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

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

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

158

159

160 with recursive allmem as

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

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

163 where allmem.val=grp_m.gname)

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

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

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

167

168

169 ## triggerもできた。

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

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

172

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

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

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

176 ## グループ管理

177 ## 個人情報管理

178 ##

179

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

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

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

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

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

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

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

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

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

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

190

191 ## ログテーブル

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

193

194

195

196 ■表設計

197 * 3つの表に分散管理

198 id格納表 + hash表 + list表

199 * *_s *_m

200

201

202

203 user, user_map, user_col

204

205 ■抽象エントリタイプ

206 * user

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

208 * group

209 権限判定に利用

210 * serial

211 自動idとして機能

212 * password

213 入力 type=passwordで入力

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

215 * session

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

217 * text

218 入力 type=text

219 * textarea

220 入力 textarea

221 * image|document

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

223 * owner

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

225 * gowner

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

227 * timestamp

228 datetime()

229 * parent

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

231 * path

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

233

234 格納タイプ

235 * list

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

237 * hash

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

239

240 オブジェクトタイプ

241 * entry

242 id, title, owner

243 * textpart

244 id, parentID, text

245 * binarypart

246 id, parentID, contenttype, filename, content

247 * content

248 hash(textpart), list(binarypart)

249 * topic

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

251 * reply

252 id, parentID, content

253 * blog

254 list(entry)

255 blog = [topic, list(reply)]

256

257

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

259 "text" => "hogehoge ..",

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

261 "author" => "taro",

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

263 "parent" => "/",

264 "path" => "/1",

265 "text" => "blah, blah, ....",

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

267 {"serial" => 2,

268 "author" => "hanako",

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

270 "parent" => "/",

271 "path" => "/2",

272 "text" => "blah, blah, ....",

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

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

275

276

277 user:=

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

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

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

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

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

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

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

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

286

287 変換表

288 /user/email=m

289

290 blog:=

291 シリアル:id:p:serial

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

293 所有者:owner:s:owner:

294 時刻:ctime:s:stamp:

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

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

297

298 article:=

299 シリアル:id:p:serial

300 筆者:author:s:owner

301 時刻:ctime:s:stamp:

302 参照元:parent:s:parent:

303 パス:path:s:path:

304 本文:text:s:textarea:

305 画像:image:m:image:

306

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

308

309

310 EOF

311

312 sq() {

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

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

315 }

316 dbsetup() {

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

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

319 sqi=$tmpdir/sqi.$$

320 sqo=$tmpdir/sqo.$$

321 mkfifo $sqi $sqo

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

323 sq $db < $sqi &

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

325 exec 2>> $tmpdir/error.out

326 exec 3>> $tmpdir/debug.out

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

328 rm $sqi

329 }

330 cleanup2() { # Dirty workaround for produced zombie processes

331 pkill -9 -u `id -u` -P 1

332 }

333 cleanup() {

334 trap '' INT HUP EXIT TERM PIPE

335 echo .quit >&5

336 kill $sq3pid

337 kill $sq3pid

338 rm -f $sqo $sqi

339 rm -rf $tmpfiles

340 cleanup2

341 }

342 # We want to use piped function to put querylog, but we use

343 # simple redirection for the sake of speed.

344 query() {

345 echo ".once $sqo" >&5

346 echo "`date '+%F %T'`:[${user:-NULL}] <<<" >> $querylog

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

348 tee -a $querylog

349 else

350 echo "$@" >> $querylog

351 echo "$@"

352 fi >&5

353 cat $sqo

354 echo '>>>' >> $querylog

355 }

356 _m4() {

357 #_S4NAME_=f,f,f

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

359 }

360 ismember() {

361 # $1=user, $2=group

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

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

364 }

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

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

367 }

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

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

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

371 }

372 isgrpowner() (

373 # $1=user, $2=group

374 gn=`sqlquote "$2"`

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

376 err isgrpowner: $sql

377 test -n "`query $sql`"

378 )

379 getgroupadminmails() {

380 # $1=group

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

382 email4group "$1" "$i" ;

383 done

384 }

385 getgroupadmins() { # $1=group

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

387 qgrp=`sqlquote "$1"`

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

389 }

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

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

392 getvalbyid grp $2 \

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

394 }

395 getgroupbyid() {

396 # $1=id|gname

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

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

399 # err ggbyid: `echo $sql`

400 query $sql

401 }

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

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

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

405

406 # Return true when anonymous mode

407 [ "$anonymousmode" ] && return 0

408 # case `getvalbyid blog mode $2` in

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

410 # *closed*)

411 # owner=`getvalbyid blog owner $2`

412 # if isgrp $owner; then

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

414 # elif isuser $owner; then

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

416 # fi

417 # esac

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

419 sql="with getblog as (\

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

421 select blogid from article where id in\

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

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

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

425 select case\

426 when (select author from article where\

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

428 then 'author'\

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

430 then 'open'\

431 when (select val from getmode) is null \

432 then 'open'

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

434 then (select user from grp_adm where \

435 gname=(select val from getowner) and \

436 user='$1')\

437 when (select author from article where\

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

439 then 'user+author'

440 else '' end;"

441 ## err isfilereadable: sql="`echo $sql`"

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

443 result=`query "$sql"`

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

445 return 2

446 }

447 linkhome() {

448 # $1=UserOrGroup

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

450 if isuser $1; then

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

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

453 else

454 echo -n "grp+$1"

455 fi

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

457 }

458 hreflink() {

459 # s4 specific notation:

460 # ^href=URL

461 # ^iframe=URL

462 # [[#NUM]] - Jump to article ID NUM

463 # [[#Keyword] - Jump to keywrod search for "Keyword"

464 # OSM umap Wikistyle Notation:

465 # [[URL]] - Simle Link

466 # [[URL|Word]] - Link with anchor word

467 # {{URL}} - <img src="URL">

468 # {{URL|width}} - <img src="URL" width="width">

469 # {{{URL}} } - <iframe src="URL"></iframe>

470 # {{{URL|height}} - <iframe src="URL" height="height"></iframe>

471 # Other Style

472 # ---- - <hr> (In the beginning of line)

473 # *Word* - <em>Word</em>

474 # _Word_ - <em>Word</em>

475 # **Word** - <strong>Word</strong>

476 # __Word__ - <strong>Word</strong>

477 _hrefptn="[-A-Za-z0-9,.:;/~_%#&+?=@!]*"

478 _hrefptn="[A-Za-z0-9/~%+?=@!.][^][()<> ]*" # URL should start with ASCII

479 sed -e "s|\[\[\#$[0-9][0-9]*$\]\]|<a href=\"?aid\1\">#\1</a>|g" \

480 -e "s|\[\[#$[^]&]*$\]\]|<a href=\"?kwd=\1\&stage=searchart\">\#\1</a>|g" \

481 -e "s|\[\[$$_hrefptn$\|$[^]]*$\]\]|<a href=\"\1\">\2</a>|g" \

482 -e "s|\[\[$$_hrefptn$\]\]|<a href=\"\1\">\1</a>|" \

483 -e "s|{{{$$_hrefptn$\|$.*$}}}|<iframe src=\"\1\" height=\"\2\"></iframe>|g" \

484 -e "s|{{{$$_hrefptn$}}}|<iframe src=\"\1\"></iframe>|g" \

485 -e "s|{{$$_hrefptn$\|$.*$}}|<img src=\"\1\" width=\"\2\">|g" \

486 -e "s|{{$$_hrefptn$}}|<img src=\"\1\">|g"\

487 -e "s|^href=$$_hrefptn$|<a &>\1</a>|" \

488 -e "s|^iframe=$$_hrefptn$|<iframe src=\"\1\"></iframe>|" \

489 -e "s,^#### *$.*$,<h4>\1</h4>," \

490 -e "s,^### *$.*$,<h3>\1</h3>," \

491 -e "s,^## *$.*$,<h2>\1</h2>," \

492 -e 's,^----*$,<hr>,' \

493 -e 's, \*\*$[^* |][^*|]*[^ |]$\*\* ,<strong>\1</strong>,g' \

494 -e 's, __$[^_ |][^_]*[^ ]$__ ,<strong>\1</strong>,g' \

495 -e 's, \*$[^* |][^*|]*[^ |]$\* ,<em>\1</em>,g' \

496 -e 's, _$[^_ ][^_]*[^ ]$_ ,<em>\1</em>,g'

497 }

498 minitbl() {

499 sed -n '

500 /^|.*|/ {; # If the line begin with "|" and has 2 or more "|"

501 s,|$,,; # Remove trailing "|" first

502 s,|\* *$[^|]*$ *,<th>\1</th>,g; # "|*..." to "<th>...</th>"

503 s,| *$[^|]*$ *,<td>\1</td>,g; # "|..." to "<td>...</td>"

504 s,^,<tr>,; s,$,</tr>,; # Enclose with "<tr>" and "</tr>"

505 H; # Concat this line to HoldSpace

506 s/.*//; # Delete PatternSpace for finalization

507 $ b cont

508 d; # If in final line, output the rest, else jump to next turn

509 }

510 :cont

511 x; # For non-"|" lines, check HoldSpace

512 /^./ {; # If HoldSpace has "|" table elements

513 s|^.|<table class="mini">|; # Enclose whole elements like this:

514 # . of ^. is workaround for FreeBSD sed

515 # s|$|</table>|; # <table class="mini">..\n..</table>

516 p; # Print whole "table" element

517 s/.*//; # Erase all when done.

518 x; s|^|</table>|; x; # Preppend /table to the next line

519 }

520 x; # Back to the newest line

521 p; # Print rest' | miniul

522 }

523 miniul() {

524 sed -e '

525 /^\* / {; # 行頭 "* "

526 x; s,^,<ul>,; x; # 1週目: ホールドスペース先頭に <ul> を

527 :top

528 s/\n//;

529 s/^ *//; # 2周目以降: 行頭空白削除

530 s,\* ,,; # まず行頭の "* " を消しておく

531 H; # 置き換え結果をホールドスペースに追加

532 s/.*//; # パターンスペースは消しておく

533 # ↓最終行なら残ったホールドスペース処理のため :cont へ

534 $ b cont

535 N; # 次の行を読む

536 s/\n//; # 空白始まりは継続行

537 /^ /b top

538 x; s/\n/<li>/; s,$,</li>,; # 継続行でなければ <li></li> で囲む

539 p; s/.*//;

540 x; # 次も "* " ならループを抜けない

541 /^\* /b top

542 s,^,</ul>,; # 次が一般行なら箇条書終わり

543 }

544

545 :cont

546 x; # 行頭| 以外の行:

547 /./ {; # ホールドスペースに文字列があれば

548 s/^\n/<li>/; s,$,</li></ul>,; # 箇条書を書き切って終わり

549 H; x

550 }

551 x'

552 }

553 acclog() (

554 # $1=table, $2=rowid

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

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

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

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

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

560 query "replace into tblaccesses values('$user', '$1', $n, '$now');"

561 fi

562 )

563 gecos() (

564 u=`sqlquote "${1:-$user}"`

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

566 )

567 setpar() {

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

569 }

570 replpar() {

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

572 }

573 getpar() {

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

575 ## err getpar/val1: "val=[$val]"

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

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

578 fi

579 ## err getpar/val2: "val=[$val]"

580 case "$var" in

581 owner)

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

583 echo $user; return

584 elif ismember $user $val; then

585 echo $val; return

586 fi ;;

587 esac

588 ## err getpar/ret: "val=[$val]"

589 echo "$val"

590 }

591

592 getpartype() {

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

594 }

595 getparcount() {

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

597 }

598 getparfilename() {

599 # null if type of $1 is not file

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

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

602 }

603 sqlquote() {

604 (v="$1"

605 case "$v" in

606 "") return ;; # null

607 "X'"*) # quoted hex string

608 echo $1 ;;

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

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

611 echo "\"$v\""

612 return ;;

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

614 echo "\"$v\""

615 return ;;

616 *)

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

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

619 else

620 echo "\"$v\""

621 fi ;;

622 esac)

623 }

624 sqlquotestr() (

625 case "$1" in

626 *\'*) v=`echo "$1"| sed "s/'/''/g"`

627 echo "'$v'" ;;

628 *) echo "'$1'" ;;

629 esac

630 )

631 mktempd() {

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

633 }

634 getcachedir() { # $1=maintable

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

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

637 else

638 echo $tmpd/$thumbxy

639 fi

640 }

641 getval() {

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

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

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

645 echo "$user" ;;

646 stamp|datetime)

647 date "+%F %T" ;;

648 serial)

649 (s=`getpar $2`

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

651 *)

652 getpar "$2" "$3";;

653 esac

654 }

655

656 getvalquote() {

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

658 (v=`getval "$@"`

659 case "$v" in

660 "") echo NULL ;;

661 *) sqlquote "$v" ;;

662 esac)

663 }

664 getparquote() {

665 sqlquote "`getpar $1`"

666 }

667 getbinbyid() {

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

669

670 }

671 getvalbyid() {

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

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

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

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

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

677

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

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

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

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

682 return

683 fi

684 done

685 rowid=$3

686 pk=`gettblpkey $1`

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

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

689 td=${4:-$tmpd}

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

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

692 for kt in s m; do

693 t=${1}_$kt

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

695 vcount=1 # count(val)

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

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

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

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

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

701 if [ $kt = m ]; then

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

703 # Reset val to store filenames if type is string

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

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

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

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

708 i=0

709 ## err gvbid: i=$i vcount=$vcount

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

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

712 i=$((i+1))

713 fn=$c.$i

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

715 case $type in

716 file:*)

717 #file=$td/$val

718 r_f=`query "select rowid||'//'||val from $cond $slice;"`

719 f_rid=${r_f%%//*}

720 file=$td/${r_f##*//}

721 # FOR SPEED: Skip file generation if imgcache exists

722 [ -s "$file" -a -s "$td/$fn.rowid" -a -s "$file.rowid" ] \

723 && [ x"$f_rid" = x"`cat $td/$fn.rowid`" ] \

724 && continue

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

726 ## err output: "fn=[$fn] file=[$file]"

727 sq $db<<EOF | unhexize > "$file"

728 .output '$td/$fn.rowid'

729 select rowid from $cond $slice;

730 .output '$td/$fn'

731 select val from $cond $slice;

732 .output '$td/${fn}.content-type'

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

734 .output stdout

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

736 EOF

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

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

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

740 cp "$file" "$file.orig" 2>&3

741 ls -lh "$file" |

742 awk '{print $5"B"}'|sed 's/BB/B/' > "$file.size"

743 case "$type" in

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

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

746 *|*:[Aa]pplication*)

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

748 png:- > "$file"

749 ;;

750 esac

751 ;;

752 *)

753 sq $db<<EOF

754 .output $td/$fn.rowid

755 select rowid from $cond $slice;

756 .output $td/$fn

757 select val from $cond $slice;

758 EOF

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

760 ;;

761 esac

762 done

763 else

764 rm -f $td/$c.count

765 case $type in

766 file:*)

767 echo "$val" \

768 | while read fn; do

769 file=$td/$fn

770 if [ ! -s "$file" ]; then

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

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

773 | unhexize > "$file"

774 ##@@## -- echo ${type#file:} > "$file.content-type"

775 case $type in

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

777 *:[Aa]pplication*)

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

779 png:- > $file ;;

780 esac

781 fi

782 done

783 ;;

784 esac

785 fi

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

787 return

788 fi

789 done

790 done)

791 }

792 getvalbypkey() (

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

794 pk=`gettblpkey $1`

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

796 getvalbyid "$1" "$2" $rowid $4

797 )

798 getvalbycond() {

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

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

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

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

803 getvalbyid "$1" "$2" $rowid "$4"

804 fi

805 }

806 getpwfield() {

807 # getpwfield user column

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

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

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

811 echo "$val"

812 return 0

813 else

814 return 1

815 fi

816 }

817 encode() {

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

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

820 sha1=sha1

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

822 sha1=sha1sum

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

824 sha1=gsha1sum

825 fi

826 fi

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

828 }

829 enjpeg() {

830 if [ -z "$cjpeg" ]; then

831 if type cjpeg >/dev/null 2>&1; then

832 cjpeg="cjpeg"

833 else

834 cjpeg="convert - jpeg:-"

835 fi

836 fi

837 $cjpeg "$@"

838 }

839 mycrypt() (

840 key=$1 salt=$2

841 err \$2=$2

842 case $2 in

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

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

845 esac

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

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

848 )

849 hexize() {

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

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

852 hexize="xxd -p"

853 else

854 hexize_hd() {

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

856 }

857 hexize="hexize_hd"

858 fi

859 fi

860 cat "$@" | $hexize | tr -d '\n'

861 }

862 unhexize() {

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

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

865 unhex="xxd -p -r"

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

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

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

869 EOF

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

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

872 # world writable...:)

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

874 fi

875 fi

876 cat "$@" | $unhex

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

878 }

879 percenthex() {

880 hexize "$@" | sed 's/$..$/%\1/g'

881 }

882 htmlescape() {

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

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

885 }

886 enascii() {

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

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

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

890 else

891 enascii_now=`date +%FT%T`

892 enascii_sed() {

893 nkf -Z0Z1Z2 \

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

895 }

896 enascii="enascii_sed"

897 fi

898 fi

899 cat "$@" | $enascii

900 }

901 size_h() {

902 i="$1" oi=$1

903 set -- B B KB MB GB TB

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

905 oi=$i

906 i=$((i/1024))

907 shift

908 done

909 echo ${oi}$1

910 }

911 gettblconf() {

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

913 ## tconfs=`sq $db \

914 tconfs=`query \

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

916 fi

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

918 }

919 gettblkeys() {

920 # $1=tbl

921 gettblconf

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

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

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

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

926 case $type in

927 *p*)

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

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

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

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

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

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

934 esac

935 case $type in

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

937 esac

938 done

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

940 }

941 gettblpkey() {

942 # $1=tbl

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

944 }

945 gettblfkey() {

946 (x=`gettblkeys $1`

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

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

949 }

950 gettblcols() {

951 (x=`gettblkeys $1`

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

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

954 }

955 gettbl_s_cols() {

956 (x=`gettblkeys $1`

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

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

959 }

960 gettbl_m_cols() {

961 (x=`gettblkeys $1`

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

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

964 }

965 gettbl_h_cols() {

966 (x=`gettblkeys $1`

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

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

969 }

970 gettbl_coltype() (

971 gettblconf

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

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

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

975 )

976 is_hidden() {

977 # $1=Tbl $2=col

978 gettblconf

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

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

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

982 case $x in

983 *h*) return 0 ;;

984 *) return 1 ;;

985 esac

986 }

987

988 dbsetbyid() {

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

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

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

992 quotedp=$(sqlquotestr "$p")

993 unset primary update

994 gettblconf

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

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

997 #err conf=$conf

998 case ${conf#*=} in

999 p*) primary=1 ;;

1000 f*) update=1 ;;

1001 u*) ;;

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

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

1004 esac

1005 #err t=$t

1006 type=string fn=""

1007 case $conf in

1008 */password)

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

1010 ;;

1011 */image*|*/document*)

1012 type=`file --mime-type - < "$val" | cut -d' ' -f2`

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

1014 ;;

1015 esac

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

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

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

1019 if [ "$primary" ]; then

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

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

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

1023 elif [ "$update" ]; then

1024 query "update $1 set $c=\"$val\" where $pkey=$quotedp;"

1025 else

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

1027 fi

1028 )

1029 }

1030 expire() (

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

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

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

1034 )

1035 addsession() {

1036 # expireをセット

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

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

1039 query "replace into session values('$1', '$expire');"

1040 # Remove old session parameters

1041 now=`expire now`

1042 query "delete from session where expire < '$now';"

1043 }

1044 gencookie() (

1045 for kv; do

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

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

1048 done

1049 )

1050 contenttype() {

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

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

1053 }

1054 putheader() {

1055

1056 }

1057 putfooter() {

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

1059 }

1060 getcookie() (

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

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

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

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

1065 done

1066 )

1067 genrandom() {

1068 # $1=columns (default: 10)

1069 dd if=/dev/urandom count=1 2>/dev/null|nkf -MB \

1070 | tr -d '+='|fold -w${1:-10}|sed -n 10p

1071 }

1072 genserial() {

1073 echo $((($(date +%s)-1433084400)/10))c$$

1074 }

1075 smail() {

1076 # smail rcpts subj (file)

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

1078 # $MAIL_FROM <- From: header value

1079 from=`echo "${MAIL_FROM:-$admin}"|nkf -jM|tr -d '\n'`

1080 rcpt=`echo $1|tr ' ' '\n'|sort -u|tr '\n' ' '` # uniq and strip newlines

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

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

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

1084 }

1085 setviastring() {

1086 table=$1

1087 oifs="$IFS"

1088 IFS="&"

1089 for us in $2; do

1090 k=${us%%=*}

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

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

1093 #echo $k=$v

1094 done

1095 IFS="$oifs"

1096 }

1097 checkdomain() (

1098 # Check the validity of domain by referring DNS

1099 item=$1

1100 err checkdomain $1

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

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

1103 )

1104 pwcheck() {

1105 # $1=passwd

1106 dbpswd=`getpwfield $user pswd`

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

1108 ## err user=$user, pswd=$1, db=$dbpswd, enc=$encpswd

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

1110 }

1111 mypwhash() {

1112 mycrypt `cat` `genrandom 5`

1113 }

1114 wasureta() {

1115 user=$1

1116 if ! checkdomain $user; then

1117 contenttype; echo

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

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

1120 putfooter

1121 exit 0

1122 fi

1123 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1125 encpswd=`echo $newpswd|mypwhash`

1126 dbsetbyid user $user pswd "$encpswd"

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

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

1129 $msgdir/mail-newaccount.m4 \

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

1131 | smail $user "New Account"

1132 }

1133 checkauth() {

1134 user=`getpar user`

1135 skc=`getpar skey` # from cookie

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

1137 skey="`getpwfield $user skey`"

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

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

1140 return 0

1141 fi

1142 fi

1143 pswd=`getpar pswd`

1144 quser=`sqlquotestr "$user"`

1145 dbuser=`query "SELECT name FROM user WHERE name=$quser;"`

1146 if [ -z "$dbuser" ]; then

1147 return 1

1148 elif [ x"$pswd" = x"wasureta" ]; then

1149 wasureta $user

1150 return 1 # wasureta error

1151 fi

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

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

1154 if pwcheck "$pswd"; then

1155 newsession=`genrandom 50`

1156 dbsetbyid user $user skey "$newsession"

1157 dbsetbyid user $user login "`date '+%F %T'`"

1158 gencookie "user=$user" "skey=$newsession"

1159 return 0

1160 fi

1161 return 2 # Password mismatch

1162 }

1163 showlogin() {

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

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

1166 $layout/login.m4.html

1167 exit 0

1168 }

1169 dologin() {

1170 checkauth

1171 st=$?

1172 if [ $st != 0 ]; then

1173 contenttype; echo

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

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

1176 showlogin # and EXIT

1177 fi

1178 }

1179

1180 # Do instant jobs here

1181 dbsetup

1182 trap cleanup INT HUP EXIT TERM PIPE

1183 # trap cleanup INT HUP

1184

1185 err() {

1186 echo "$@" 1>&3

1187 }

1188

1189 cgiinit() {

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

1191 tmpf=tmp/stream

1192 tmpd=`tmpd=$tmpdir mktempd`

1193 tmpfiles=$tmpfiles" $tmpd"

1194 addsession $session

1195 getcookie

1196 case "$REQUEST_METHOD" in

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

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

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

1200 # cat > $tmpf # too much?

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

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

1203 s="`cat tmp/stream`"

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

1205 ;;

1206 esac

1207 case "$CONTENT_TYPE" in

1208 *boundary*)

1209 bndry=${CONTENT_TYPE#*boundary=}

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

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

1212 do

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

1214 #echo u=$us

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

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

1217 # err k=$k v=$v

1218 case "$k" in

1219 *:filename)

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

1221 # DO NOT ALLOW Space and '|' in file names

1222 newv=`echo "$v"|sed 's/[ \|]/X/g'`

1223 if [ x"$v" != x"$newv" ]; then

1224 :

1225 fi

1226 # (echo k=$k v="[$v]"; ls -lF "$tmpd/$v"; file --mime-type "$tmpd/$v") 1>&3

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

1228 [Ii]mage/x-xcf)

1229 bzip2 "$tmpd/$v"

1230 v=${v}.bz2

1231 ;;

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

1233 [Ii]mage/*)

1234 mogrify -resize $maximagexy'>' "$tmpd/$v"

1235 ;;

1236 esac

1237 ;;

1238 *)

1239 type='string'

1240 ;;

1241 esac

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

1243 setpar "$k" "$type" "$v"

1244 done

1245 ;;

1246 *)

1247 setviastring par "$s"

1248 ;;

1249 esac

1250 }

1251 email4group() {

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

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

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

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

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

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

1258 and s.key='email'

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

1260 query "$sql"

1261 }

1262 email4groupbyuid() {

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

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

1265 uids=`echo "$@"`

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

1267 sql="WITH

1268 grpemails AS (

1269 SELECT gname, user, val email

1270 FROM grp_mem NATURAL JOIN grp_mem_s

1271 WHERE key='email' AND gname=$qgrp),

1272 useremails AS (

1273 SELECT user.rowid rid, user.name, val email

1274 FROM user

1275 LEFT JOIN user_s

1276 ON user.name=user_s.name AND user_s.key='email')

1277 SELECT DISTINCT coalesce(g.email, u.name)

1278 FROM useremails u LEFT JOIN grpemails g

1279 ON u.name=g.user

1280 WHERE u.rid in ($uids);"

1281 ## err email4gByid `echo $sql`

1282 query "$sql"

1283 }

1284 collectmembersbyid() {

1285 # Collect user names of group specified by grid

1286 rid=${1%%[!0-9]*} # Cleaning

1287 query "SELECT user FROM grp_mem \

1288 WHERE gname=(SELECT gname FROM grp WHERE rowid=$rid);"

1289 }

1290 collectmembersbyid() {

1291 # Collect user names of group name

1292 qgrp=`sqlquote "$1"`

1293 query "SELECT user FROM grp_mem WHERE gname=$qgrp;"

1294 }

1295 collectgecosesbyid() {

1296 # Collect user gecoses of group

1297 rid=${1%%[!0-9]*} # Cleaning

1298 query<<-EOF

1299 SELECT gecos

1300 FROM gecoses

1301 WHERE name IN (SELECT user FROM grp_mem

1302 WHERE gname=(SELECT gname FROM grp WHERE rowid=$rid));

1303 EOF

1304 }

1305 collectemail() (

1306 # Collect email addresses for group $1

1307 # If $TEAM is set, filter by team name

1308 # If $EXCEPT is set as username(s) delimited by comma,

1309 # remove $EXCEPT from list: ....NOT IN ($EXCEPT)

1310 for e; do

1311 if isuser "$e"; then

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

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

1314 else

1315 qgrp=`sqlquote "$e"`

1316 if [ -z "$TEAM" ]; then

1317 gmem="grp_mem"

1318 else

1319 tm=`sqlquote "$TEAM"`

1320 gmem="(SELECT gname, user FROM grp_mem_m WHERE gname='$e' AND key='team' AND val=$tm)"

1321 fi

1322 ex=${EXCEPT:+"AND g.user NOT IN ($EXCEPT)"}

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

1324 $gmem g left join grp_mem_s s

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

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

1327 where g.gname=$qgrp $ex;"

1328 ## err CollectEmail: `echo "$sql"`

1329 query "$sql"

1330 fi

1331 done

1332 )

1333 sendinvitation() (

1334 # $1=email

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

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

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

1338 gecos=`gecos`

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

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

1341 _m4 -D_URL_="$urlbase" \

1342 -D_USER_="$name" \

1343 -D_EMAIL_="$1" \

1344 -D_REGIST_="$regist" \

1345 -D_ADMIN_="$admin" \

1346 $msgdir/mail-invite.m4 \

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

1348 return 0

1349 )

1350 emaildomaincheck() {

1351 case "$1" in

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

1353 *@*)

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

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

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

1357 return 2

1358 fi

1359 return 0

1360 ;;

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

1362 esac

1363 }

1364 invite() {

1365 email=`getpar email`

1366 case $email in

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

1368 *@*)

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

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

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

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

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

1374 elif sendinvitation $email; then

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

1376 fi ;;

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

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

1379 esac

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

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

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

1383 fi

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

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

1386 $layout/html.m4.html $layout/invite.m4.html

1387 }

1388 regist() {

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

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

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

1392 echo "bye bye" | html p

1393 reutrn

1394 fi

1395 email=`session=$1 getpar invite`

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

1397 cat<<EOF

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

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

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

1401 EOF

1402 return

1403 fi

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

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

1406 # Fake login password to wasureta

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

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

1409 wasureta $email

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

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

1412 addsession $1 # for removal after 1 minute

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

1414 $layout/login.m4.html

1415 return

1416 }

1417 group_safename() {

1418 # Convert $1 to safe group name

1419 echo "$1" | tr -d '"'"'",

1420 }

1421 groupupdate() {

1422 gname=`getpar gname`

1423 qgname=`sqlquote "$gname"`

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

1425 # See ALSO same job in showgroup()

1426 newgname=`group_safename "$gname"`

1427 err newgname=$newgname

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

1429 err NewGNAME: gname=$newgname

1430 gname=$newgname

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

1432 replpar gname string "$gname"

1433 fi

1434 # Name confliction check

1435 parow=`getpar rowid`

1436 ## err parow=$parow

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

1438 query "BEGIN EXCLUSIVE;"

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

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

1441 if [ $count -gt 0 ]; then

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

1443 query "END;"

1444 return

1445 fi

1446 par2table $formdir/grp.def

1447 query "END TRANSACTION;"

1448 # Remove orphan

1449 : <<EOF

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

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

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

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

1454 EOF

1455 rm=`getpar rm` cfm=`getpar confirm`

1456 ## err groupupdate:::: after par2tbl rmcfm=$rm$cfm

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

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

1459 sql="delete from blog where id in

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

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

1462 query "$sql";

1463 grps # When removing a group, switch to grp-list

1464 return # and return

1465 fi

1466 fi

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

1468 fi

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

1470 grid=$(query $sql)

1471 ## err grpupdate:new-grid=$grid, sql=$sql

1472 grp $grid

1473 }

1474 groupclone() {

1475 # $1=grp-rowid of clone-base group

1476 qgrp=`query "SELECT quote(gname) FROM grp WHERE rowid=$1;"`

1477 if [ -z "$qgrp" ]; then

1478 echo "無効なグループIDです($1)" | html p

1479 return

1480 fi

1481 i=0

1482 while true; do

1483 copy="-copy$i"

1484 newqname=`query "SELECT quote($qgrp || '$copy');"`

1485 # err Trying new grp=$newqname with copy=$copy

1486 test=`query "SELECT gname FROM grp WHERE gname=$newqname;"`

1487 if [ -n "$test" ]; then

1488 i=$((i++))

1489 continue

1490 fi

1491 break

1492 done

1493 # Creating New group "$newqname" with members of old group

1494 # err Creating new grp=$newqname with copy=$copy

1495 query<<-EOF

1496 BEGIN;

1497 INSERT INTO grp VALUES($newqname); -- Create NEW one

1498 REPLACE INTO grp_s(gname, key, val) -- Copy tag

1499 SELECT $newqname, key, val

1500 FROM grp_s WHERE gname=$qgrp AND key IN ('tag', 'mode');

1501 REPLACE INTO grp_s(gname, key, type, val) -- Copy gecos with "copy$n"

1502 SELECT $newqname, key, type, val || '$copy'

1503 FROM grp_s WHERE gname=$qgrp AND key='gecos';

1504 -- Copy members and their configuration --

1505 REPLACE INTO grp_mem SELECT $newqname, user

1506 FROM grp_mem WHERE gname=$qgrp;

1507 REPLACE INTO grp_mem_s SELECT $newqname, user, key, type, val, bin

1508 FROM grp_mem_s WHERE gname=$qgrp;

1509 REPLACE INTO grp_mem_m SELECT $newqname, user, key, type, val, bin

1510 FROM grp_mem_m WHERE gname=$qgrp;

1511 -- Copy administrators --

1512 REPLACE INTO grp_adm SELECT $newqname, user

1513 FROM grp_adm WHERE gname=$qgrp;

1514 COMMIT;

1515 EOF

1516 newrowid=`query "SELECT rowid FROM grp WHERE gname=$newqname;"`

1517 STOPCLONEMSG=1 groupconf "$newrowid"

1518 }

1519 groupman() {

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

1521

1522 GF_STAGE="grpconf"

1523 GF_STAGE=groupupdate

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

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

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

1527 -D_DUMPTABLE_="syscmd(cat)" \

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

1529 }

1530 userconf() {

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

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

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

1534 }

1535 groupconf() {

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

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

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

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

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

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

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

1543 if [ -z "$STOPCLONEMSG" ]; then

1544 echo "同じ構成員で新規グループ<a href=\"?groupclone+$rowid\">作成</a>" \

1545 | html p

1546 fi

1547 }

1548 mems() {

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

1550 kwd=`getpar kwd`

1551 listmember $kwd

1552 }

1553 grps() {

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

1555 kwd=`getpar kwd`

1556 listgroup $kwd \

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

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

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

1560 $layout/form+dump.m4.html

1561 }

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

1563 gpg=`getpar grp`

1564 grid=${1:-$gpg}

1565 grp=`getgroupbyid "$grid"`

1566 ## . ./s4-blog.sh

1567 jg=`getpar joingrp`

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

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

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

1571 fi

1572 htmlheader=$layout/html.m4.html

1573 showgroup "$grid"

1574 }

1575 sql4interestblogs() {

1576 cat<<EOF

1577 CREATE TEMPORARY VIEW interestblogs AS

1578 SELECT blog.rowid rid, id, author

1579 FROM blog

1580 NATURAL JOIN

1581 (SELECT id, val owner FROM blog_s WHERE key='owner') bs

1582 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

1583 THEN 1 -- blog owner is an user, READABLE

1584 WHEN (SELECT user FROM grp_mem

1585 WHERE gname=bs.owner AND user='$user') IS NULL

1586 THEN 0

1587 ELSE 1

1588 END;

1589 EOF

1590 }

1591 listnewblogsql() { # $1=user

1592 deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog

1593 WHERE user='$user'

1594 AND tblrowid IN

1595 ($blogreadflagrowid,

1596 $blogcutoffflagrowid)),

1597 "0");"`

1598 cat<<EOF

1599 `sql4interestblogs`

1600 WITH article_ctime as (

1601 SELECT id,blogid,author,max(val) ctime

1602 FROM article join article_s s using(id)

1603 WHERE s.key='ctime' AND s.val > '$deftime'

1604 GROUP BY id

1605 ), blog_title_owner as (

1606 SELECT blg.rid brid, id,

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

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

1609 FROM interestblogs blg, blog_s using(id) group by id

1610 ), blogall as (

1611 SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid

1612 ), news as (

1613 SELECT brid, bl.id blid, bl.title, ctime,

1614 coalesce(al.time, '$deftime') atime,

1615 count(bl.id) "新着", bl.author

1616 FROM blogall bl

1617 LEFT JOIN

1618 (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al

1619 ON bl.brid=al.tblrowid

1620 WHERE atime < bl.ctime

1621 GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id

1622 LIMIT 10

1623 ) SELECT brid LINK, "新着",

1624 (SELECT count(*) FROM article WHERE blogid=blid) "総数",

1625 ctime, title,

1626 (SELECT gecos FROM gecoses WHERE name=author) gecos

1627 FROM news;

1628 EOF

1629 }

1630

1631 search_form() {

1632 help="(1)空白区切りの単語で本文検索

1633 (2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索

1634 @2016-0[1-6] → 2016年1月から6月

1635 @>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間

1636 @week → 最近一週間

1637 (3)#番号で記事ID検索

1638 (1)と(2)は組み合わせOK

1639 例: @2016-10-0[1-9] 芋煮

1640 → 2016年10月上旬でキーワード「芋煮」を含む記事検索

1641 ※クイズ板は検索対象から外されます。"

1642 auth=""

1643 placeholder="全記事からの検索"

1644 case "$1" in

1645 author=*)

1646 a=`echo "${1#author=}"|htmlescape`

1647 g=`gecos ${1#author=}`

1648 auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"

1649 placeholder="このユーザの書込検索"

1650 help="★★ $g さんの書き込みから検索します$nl$help"

1651 ;;

1652 grp=*)

1653 a=`echo "${1#grp=}"|htmlescape`

1654 g=`gecos ${1#grp=}`

1655 auth="<input type=\"hidden\" name=\"owner\" value=\"$a\">"

1656 placeholder="このグループからの検索"

1657 ;;

1658 esac

1659 cat<<-EOF

1660 <div class="right">

1661 <form action="$myname">$auth

1662 <input type="text" name="kwd" value="" title="$help"

1663 placeholder=" $placeholder " width="10" accesskey="k">

1664 ${touchpanel:+<p class="help">$help</p>}

1665 <input type="hidden" name="stage" value="searchart">

1666 </form>

1667 </div>

1668 EOF

1669 }

1670

1671 imgsrc_cache() (

1672 # $1 = directory for cache'ing

1673 # $2 = table (user_m or grp_m)

1674 # $3 = keycond (was: condition for choosingowner)

1675 # $4 = size : S = Small, M = Medium, O = Original

1676 dir="$1" tbl="$2"

1677 keycond="$3"

1678 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

1679 ORDER BY rowid DESC LIMIT 1"

1680 [ -d "$dir" ] || mkdir -p "$dir"

1681 tmpf=$tmpd/imgsrc_cache.$$

1682 case "$4" in

1683 [Ss]) size=S ;;

1684 [Oo]) size=O ;;

1685 *) size=M ;;

1686 esac

1687 # ImageCache filename storing schema:

1688 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

1689 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

1690 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

1691 valtype=`query "$sql0"`

1692 filename=${valtype%%//*}

1693 filetype=${valtype##*//file:}

1694 if [ x"$filename" = x"${filename%.*}" ]; then

1695 # If nor filename extension found, set it to image type

1696 case "$filetype" in

1697 image/*) filename=$filename.${filetype#image/} ;;

1698 esac

1699 fi

1700 cacheimg_S=$dir/S_$filename

1701 cacheimg_M=$dir/M_$filename

1702 cacheimg_O=$dir/$filename

1703 cacheimg=$dir/${size}_$filename

1704 sumfile="$dir/$filename.sum"

1705 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

1706 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

1707 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

1708 # if cache is fresh and has the same checksum,

1709 echo "<img src=\"$cacheimg\">"

1710 else

1711 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

1712 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

1713 fifo2=$fifo.2

1714 mkfifo $fifo $fifo2

1715 fmt=${filename##*.}

1716 ## [[ NOTE ]]

1717 ## a. convert oldimage newimage

1718 ## b. convert oldimage fmt:- | convert - newimage

1719 ## b is much smaller than a

1720 cat $tmpf | unhexize \

1721 | tee $fifo \

1722 | convert -define ${fmt}:size=${iconxy_M} \

1723 -resize ${iconxy_M}'>' - ${fmt}:- \

1724 | tee $fifo2 \

1725 | convert - "$cacheimg_M" &

1726 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

1727 -resize ${iconxy_S}'>' - ${fmt}:- \

1728 | convert - "$cacheimg_S" &

1729 printf '%s' "<img src=\"data:${filetype},"

1730 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

1731 echo '">'

1732 echo "$sum" > $sumfile

1733 fi

1734 ## Now preparing cache image, done.

1735 ## Store this information to DB

1736 stbl=${tbl%_m}_s # user_s or grp_s

1737 pkey=${keycond%%=*} # Primary Key name

1738 pval=${keycond#*=} # Primary Key value

1739 query <<-EOF

1740 REPLACE INTO $stbl($pkey, key, type, val)

1741 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

1742 EOF

1743 )

1744

1745 showhome() {

1746 # $1=userRowIdToShow

1747 err showhome \$1=$1

1748 case "$1" in

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

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

1751 esac

1752 ## err ShowHome: uname=$uname

1753 td=`getcachedir home/"$1"`

1754 gecos=`gecos "$uname"`

1755 ## err SH:gecos=$gecos

1756 GF_VIEWONLY=1

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

1758 search_form_args=""

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

1760 usermenu="<a href=\"?userconf\" accesskey=\"e\"

1761 title=\"E\">プロフィールの編集</a> /

1762 <a href=\"?blog\" accesskey=\"n\" title=\"N\">新規話題の作成</a>"

1763 # Display folders

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

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

1766 and type like 'file:%';"

1767 ## err nfile-sql=`echo "$sql"`

1768 nfile=`query "$sql"`

1769 # err nfile=$nfile

1770 if [ $nfile -gt 0 ]; then

1771 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

1772 title=\"L\">過去の提出ファイル</a>"

1773 fi

1774 else

1775 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

1776 GROUP BY user;"`

1777 usermenu="<p>Last seen on $latestlog</p>"

1778 search_form_args="author=$uname"

1779 fi

1780 . ./s4-blog.sh

1781

1782 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

1783 search_form $search_form_args > $sf

1784 echo "$gecos さん" > $tf

1785 { echo "<div class=\"noprofimg\">"

1786 viewtable $formdir/user.def user $1

1787 echo "</div>"

1788 } > $pf

1789

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

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

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

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

1794 if true; then

1795 tbl=user_m

1796 enticond="name='$uname'"

1797 imgsrc_cache "$td/main" user_m "$enticond" M

1798 else

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

1800 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

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

1802 echo '">'

1803 }

1804 fi > $imf

1805 fi

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

1807 val='$uname';"`

1808 listblog $uname > $bf

1809

1810 hometail=$tmpd/tail.$$

1811 mkfifo $hometail

1812

1813 #Calling listgroupbytable, originally here

1814

1815 (

1816 # Display Most Recent Entry

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

1818 shortval=${shortval:-val}

1819

1820 # The m.aid in the next line is suspicious. But works fine in SQLite3...

1821 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

1822 ctime,

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

1824 (SELECT gecos FROM gecoses

1825 WHERE name=(SELECT val FROM blog_s

1826 WHERE key='owner' AND id=b.id)) owner,

1827 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

1828 FROM blog b

1829 JOIN

1830 (SELECT distinct blogid, a.id aid, max(val) ctime

1831 FROM article a, article_s s

1832 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

1833 GROUP BY blogid ORDER BY val DESC LIMIT 50

1834 ) m

1835 ON b.id=m.blogid;"

1836 # This should be as follows

1837 : <<EOF

1838 WITH arts AS(

1839 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

1840 a.blogid, a.id id, s.val ctime

1841 FROM article a NATURAL JOIN article_s s

1842 WHERE s.key = 'ctime' AND a.author='$user'

1843 GROUP by s.id

1844 )

1845 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

1846 FROM arts a0

1847 JOIN

1848 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

1849 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

1850 ORDER BY ctime DESC LIMIT 50;

1851 EOF

1852

1853 cat<<-EOF

1854 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

1855 for="mre" title="D">最近の書き込み先</label>

1856 <div class="lcto">

1857 `DT_VIEW=replyblog dumptable html blog`

1858 </div>

1859 EOF

1860 unset DT_SQL

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

1862 # Display NEWS

1863 # 2016-06-26

1864 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

1865 acclog blog $blogreadflagrowid

1866 # echo "全部既読にしました" | html p

1867 fi

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

1869 sql=`listnewblogsql "$user"`

1870 # echo "$sql" > tmp/listnew

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

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

1873 cont=$((cont-1))

1874 err newcount=$cont

1875 if [ $cont -gt 0 ]; then

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

1877 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

1878 echo "<label for=\"new10\" title=\"F\">新着${cont}傑</label><div>"

1879 cat<<-EOF | html form 'action="?home"'

1880 `cgi_checkbox readchk yes 'id="read"'`<label

1881 for="read">新着ふくめて全部読んだことにする</label>

1882 `cgi_submit '確定'`

1883 `cgi_hidden read yes`

1884 EOF

1885 echo "$new10 "

1886 echo "</div>"

1887 else # If news is 0, set log cut off flag

1888 acclog blog $blogcutoffflagrowid # for speed

1889 fi

1890 else # Not My Home ($user != $uname)

1891 : # DT_SQL=

1892 fi

1893 ) > $hometail & # Is background call safe to m4??

1894 #

1895 listgroupbytable $formdir/grp.def $cond |

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

1897 -D_PROFILE_="spaste(\`$pf')$usermenu" \

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

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

1900 -D_SEARCH_="spaste(\`$sf')" \

1901 -D_NBLOG_="$nblog" \

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

1903 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

1904 $layout/html.m4.html $layout/home.m4.html

1905

1906 # Record access log

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

1908 }

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

1910 contenttype; echo

1911 ## err commission: "$@"

1912 gname=`getgroupbyid $1`

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

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

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

1916 grp_reg_adm "$@"

1917 else

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

1919 fi

1920 }

1921 listgroupbytable() {

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

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

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

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

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

1927 NGsql="select distinct tag from\

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

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

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

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

1932 ## err ListGRP: query sql="$sql"

1933 for tag in `query "$sql"`

1934 do

1935 ## err ListGrp: tag=$tag

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

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

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

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

1940 as rowid,

1941 gname,

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

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

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

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

1946 ## err PersonalGroupList= `echo $sql`

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

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

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

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

1951 echo '</table>'

1952 done

1953 echo '</div>'

1954 }

1955 iconhref() (

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

1957 data=`percenthex "$1"`

1958 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

1959 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

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

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

1962 )

1963 iconhref2() (

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

1965 src=$1

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

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

1968 )

1969 listentry() (

1970 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

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

1972 ## err listentry: \$1=$1 \$2=$2 \$3=$3

1973 cond='' hiddens=''

1974 offset=`getpar offset`; offset=${offset%%[!0-9]*}

1975 if [ -z "$offset" ]; then

1976 offset=`getpar start`; offset=${offset%%[!0-9]*}

1977 offset=$((offset-1))

1978 fi

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

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

1981 limit=30

1982 dir=`getcachedir "$1"`

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

1984 hrb="$myname?home"

1985 deficon=person-default.png

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

1987 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

1988 gcs=gecos

1989 else # if group

1990 hrb="$myname?grp"

1991 deficon=group-default.png

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

1993 gcs=name

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

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

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

1997 ## err tagconv=$tagconv

1998 fi

1999 fi

2000 if [ ! -d $dir ]; then

2001 mkdir -p $dir

2002 fi

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

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

2005 fi

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

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

2008 fi

2009

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

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

2012 ##qgrp=`sqlquote $grp`

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

2014 sql="select a.rowid, a.$link,

2015 coalesce(b.$gcs, a.$nm) as nick,

2016 quote(a.$nm) as qname,

2017 (SELECT val FROM ${tbl}_s

2018 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2019 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2020 || case when a.$nm in (select gname from grp)

2021 then printf('(%d名)',

2022 (select count(user) from grp_mem where gname=a.$nm))

2023 else ' <'||a.$nm||'>'

2024 end

2025 as name,

2026 b.tag,

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

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

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

2030 then '(ADMIN)'

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

2032 then '(Member)'

2033 when '$iamowner' = '' then ''

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

2035 CASE '$entity'

2036 WHEN 'グループ'

2037 THEN coalesce(

2038 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2039 'open')

2040 ||

2041 CASE WHEN '$user'

2042 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2043 THEN ' ismember'

2044 ELSE ''

2045 END

2046 ELSE 'user'

2047 END regmode

2048 from $tbl a left join

2049 (select $nm as name,

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

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

2052 max(case key when 'mtime' then val end) as mtime,

2053 max(case key when 'wtime' then val end) as wtime,

2054 max(case key when 'login' then val end) as login

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

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

2057 order by b.wtime desc, b.login desc,

2058 b.mtime desc, b.tag desc, a.rowid asc"

2059 # Give precedence to newer maintained groups (2016-09-24)

2060 # Note that mtime is stored only in grp_s.

2061 ## err LE:sql.1="$sql"

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

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

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

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

2066 cgi_form $stage <<EOF

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

2068 `cgi_text kwd $kwd`</label>

2069 EOF

2070 echo '</div>'

2071 fi

2072 hiddens="$hiddens

2073 `cgi_hidden kwd \"$kwd\"`

2074 `cgi_hidden stage \"$stage\"`"

2075 cat<<EOF

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

2077 <p>${total}件中の<input class="hidesub" type="text" name="start"

2078 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2079 <input type="submit" value="確定"></p>

2080 </form>

2081 EOF

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

2083 nextbtn=$(

2084 cat<<EOF

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

2086 `cgi_submit 次の${limit}件`

2087 $hiddens

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

2089 EOF

2090 )

2091 fi

2092 if [ $offset -gt 0 ]; then

2093 prevbtn=$(

2094 cat<<EOF

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

2096 `cgi_submit 前の${limit}件`

2097 $hiddens

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

2099 EOF

2100 )

2101 fi

2102 pnbtn="$nextbtn$prevbtn"

2103 echo $pnbtn

2104

2105 ## err ListEntry: `echo "$sql"\;`

2106 # sq $db here??? 2016-11-28

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

2108 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2109 err name=$name owner=$ownerp lnk=$lnk

2110 err newlnk=$lnk regmode=$regmode

2111 icondir=$dir/$id

2112 # Pick up only last icon

2113 echo "<div class=\"iconlist xy$thumbxy $type\">

2114 <p class=\"tag _$tag\">$tag</p>" \

2115 | _m4 $tagconv

2116 if [ -n "$NOSPEEDUP" ]; then

2117 files=`getvalbyid $tbl profimg $id $icondir`

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

2119 icon=`echo "$files"|tail -1`

2120 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2121 else

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

2123 fi

2124 elif [ -n "$icon" -a -s "$icon" ]; then

2125 iconhref2 "$icon" "$hrb+$lnk" "$gecos<br>$mt"

2126 else

2127 cond="$nm=$qname"

2128 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2129 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2130 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2131 # err "img=[$img]"

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

2133 echo "<a href=\"$hrb+$lnk\">"

2134 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2135 echo "</a>"

2136 else

2137 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2138 fi

2139 fi

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

2141 echo "</div>"

2142 done

2143 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2144 )

2145 listmember() {

2146 listentry user "$@"

2147 }

2148 listgroup() {

2149 listentry group "$@"

2150 }

2151 hexteams() { # $1=gname, $2(optional)=user

2152 cond=${2:+" AND user='$2'"}

2153 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2154 WHERE gname='$1' AND key='team'$cond;"

2155 }

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

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

2158 grid=`getpar grid`

2159 grid=${grid%%[!0-9]*}

2160 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2161 else

2162 grid=$1

2163 fi

2164 grp=`getgroupbyid $grid`

2165 qgrp=`sqlquote "$grp"`

2166 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2167 if isgroup "$grp"; then

2168 tf=$tmpd/title.$$

2169 sf=$tmpd/search.$$

2170 bodyclass=`query "SELECT val FROM grp_s

2171 WHERE gname=$qgrp AND key='regmode';"`

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

2173 ismember="ismember"

2174 qgrp=`sqlquote "$grp"`

2175 bodyclass="$bodyclass${bodyclass:+ }ismember"

2176 else

2177 ismember="" # bodyclass="group"

2178 fi

2179 bodyclass="$bodyclass grouphome"

2180 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

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

2182

2183 showgroupsub $formdir/grp.def "$grid" | \

2184 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

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

2186 -D_BODYCLASS_="$bodyclass" \

2187 -D_DUMPTABLE_="" \

2188 $htmlheader $sf $layout/form+dump.m4.html

2189 # $htmlheader $layout/form+dump.m4.html

2190 # $htmlheader is defined in grp()

2191 else # if $grp is removed at par2table

2192 listgroup

2193 fi

2194 }

2195 showgroupsub() {

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

2197 # Using $ismember

2198 rowid=$2

2199 grp=`getgroupbyid $2`

2200 qgrp=`sqlquote "$grp"`

2201 td=`getcachedir grp/"$2"`

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

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

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

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

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

2207 return

2208 fi

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

2210 enticond="gname=$qgrp"

2211 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

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

2213 cat<<-EOF

2214 <p class="groupimg">

2215 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2216 EOF

2217 fi

2218 echo "<div class=\"noprofimg\">"

2219 viewtable $1 grp $rowid

2220 echo "</div>"

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

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

2223 iamowner=$rowid

2224 colmd=" mode"

2225 fi

2226 if [ -n "$ismember" ]; then

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

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

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

2230 cat<<EOF

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

2232 <div class="fold clear">

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

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

2235 <div>

2236 `cgi_textarea message "" "cols=60"`

2237 `cgi_submit 送信`

2238 `cgi_reset リセット`

2239 </div>

2240 `cgi_hidden grp $rowid`

2241 </div></form>

2242 EOF

2243 fi

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

2245 if [ -n "$ismember" ]; then

2246 ismem='checked' state="(参加中)"

2247 else

2248 nomem='checked' state="(現在非加入)"

2249 fi

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

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

2252 and key='email';"`

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

2254 ## and key='email';"

2255 ##err email=$eml

2256 cat <<EOF

2257 <div class="fold clear">

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

2259 for="reg">自身の加入状態を操作する</label>$state

2260 <div>

2261 EOF

2262 cgi_form grp <<EOF

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

2264 <table class="b">

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

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

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

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

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

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

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

2272 </table>

2273 `cgi_hidden grp $rowid`

2274 EOF

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

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

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

2278 fi

2279 echo '</div></div>'

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

2281 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2282 cat<<-EOF

2283 <form class="summary" action="$myname" title="$thelp">

2284 `cgi_hidden owner "$grp"`

2285 `cgi_hidden kwd "@week"`

2286 `cgi_hidden stage searchart`

2287 `cgi_submit "一週間のまとめ"`

2288 </form>

2289 EOF

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

2291 colstate="state:稼動状態:frozen=rowclass=凍結"

2292 DT_CHLD=article:blogid \

2293 DT_VIEW=replyblog dumptable html blog \

2294 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2295

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

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

2298 cm="?commission+$rowid"

2299 thumbxy=50x50 listmember "" "$c" "$rowid" \

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

2301 # team list

2302 hexteams=`hexteams "$grp"`

2303 if [ -n "$hexteams" ]; then

2304 echo "チーム一覧" | html h2

2305 echo '<div class="dumptable"><table class="b">'

2306 sq $db -html -header<<-EOF

2307 SELECT val TEAM,

2308 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2309 MEMBERS

2310 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2311 EOF

2312 echo '</table></div>'

2313 fi

2314 }

2315 grp_getbodyclass() {

2316 # Get css class name for document.

2317 # `moderated' for moderated groups

2318 # `ismember' for groups where user belongs

2319 # $1=GroupName (w/o quote)

2320 # $user=userNameCurrentlyLogin

2321 ## err grp_getbodyclass: 1="$1"

2322 qgrp=`sqlquote "$1"`

2323 query<<-EOF

2324 SELECT coalesce(

2325 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2326 'open')

2327 ||

2328 CASE WHEN '$user'

2329 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2330 THEN ' ismember'

2331 ELSE ''

2332 END;

2333 EOF

2334 }

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

2336 err GRP_ACTION:IN

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

2338 grp=`getgroupbyid "$grid"`

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

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

2341 fi

2342 if ! ismember $user "$grp"; then

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

2344 fi

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

2346 | _m4 -D_TITLE_="syscmd(\`cat')" \

2347 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2348 $layout/html.m4.html

2349

2350 isowner=""

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

2352 usel=`getpar usel`

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

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

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

2356 text=`getpar text`

2357

2358 rm=`getpar rm` cfm=`getpar confirm`

2359 ## err rm=$rm cfm=$cfm

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

2361 if [ "$isowner" ]; then

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

2363 # Eliminate

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

2365 for tbl in grp_mem grp_mem_s grp_mem_m; do

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

2367 # echo "sql=$sql"

2368 query "$sql"

2369 err rmGRPuser "$sql"

2370 done

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

2372 #err num=$num

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

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

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

2376 # err `echo "$sql"`

2377 html pre<<EOF

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

2379 `query "$sql"`

2380 EOF

2381 fi

2382 else

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

2384 return

2385 fi

2386 else # not Group Owner

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

2388 return

2389 fi

2390 cat<<EOF

2391

2392 EOF

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

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

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

2396 return 0

2397 fi

2398 gecos=`gecos $user`

2399 mkfrom=`getpar mkfrom`

2400 if [ x"$mkfrom" = x"yes" ]; then

2401 safegc=`echo "$gecos" | tr -d '<>@'`

2402 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2403 fromad=`email4groupbyuid "$grp" "$myuid"`

2404 mail_from="$safegc <$fromad>"

2405 else

2406 mail_from="$admin"

2407 fi

2408 MAIL_FROM=$mail_from \

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

2410 "$gecos さんからのメッセージ" <<EOF

2411 $url

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

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

2414 ----------------------------------------------------------

2415 $text

2416 EOF

2417 if [ $? = 0 ]; then

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

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

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

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

2422 html pre<<EOF

2423 `query "$sql"`

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

2425 EOF

2426 err SendDone: `echo $sql`

2427 fi

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

2429 grp_reg_adm $grid $usel

2430 elif [ x"$rm" = x"addteam" ]; then

2431 team=`getpar team|sed "s/'/''/g"` # for single quotation

2432 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2433 if [ x"$team" != x"$newteam" ]; then

2434 echo "チーム名に使えない文字を除去しました" | html p

2435 team=newteam

2436 fi

2437 if [ -z "$team" -o x"$team" = x"なし" ]; then

2438 cat<<-EOF | html p

2439 有効なチーム名を入力してください。

2440 カンマだけ、「なし」という名前は使えません。

2441 EOF

2442 echo "有効なチーム名を入力してください。" | html p

2443 else

2444 grp_add_team $grid "$team" $usel

2445 fi

2446 elif [ x"$rm" = x"rmteam" ]; then

2447 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2448 rmteam=`getpar rmteam|sed "s/'/''/g"`

2449 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2450 gname='$grp' AND user='$user' AND key='team'\

2451 AND val='$rmteam';\"`" ]; then

2452 grp_rm_team $grid "$rmteam" $usel

2453 else

2454 echo "所属していないチームの除去操作はできません。"|html p

2455 fi

2456 else

2457 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2458 fi

2459 fi

2460 fi

2461 # POST count summary

2462 from=`getpar from`; to=`getpar to`

2463 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2464 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2465 fromtonote="<p>POST集計: $from_input - $to_input</p>$from - $to"

2466 # New entry

2467 sql="WITH mems AS (

2468 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2469 ON gm.user=g.name

2470 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2471 ), target_article AS (

2472 SELECT id FROM article_s

2473 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2474 ), posts AS (

2475 SELECT author, count(author) post

2476 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2477 WHERE blogid IN (SELECT id FROM blog_s

2478 WHERE key='owner'

2479 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2480 AND key='text'

2481 GROUP BY author

2482 ), teams AS (

2483 SELECT user, group_concat(val, ', ') team

2484 FROM grp_mem_m

2485 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2486 AND key='team'

2487 GROUP BY user

2488 ), user_post AS (

2489 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2490 FROM mems m LEFT JOIN posts

2491 ON m.name=posts.author

2492 GROUP by m.rowid

2493 )

2494 SELECT

2495 CASE

2496 WHEN (SELECT user FROM grp_adm

2497 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2498 AND user=up.name) IS NOT NULL

2499 then 'k'

2500 ELSE ''

2501 END || rowid || ',' || gecos NAME,

2502 post POST, team TEAM

2503 FROM user_post up LEFT JOIN teams t

2504 ON up.name=t.user

2505 ORDER BY gecos;"

2506 ## err grpaction: "`echo \"$sql\"`"

2507 tf=$tmpd/title.$$

2508 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2509 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2510 for=\"cmadmin\">グループ管理者委任</label>

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

2512 </p></div>"

2513 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2514 for=\"conf\">グループ登録解除</label>

2515 <div>本当に消します! `cgi_checkbox confirm yes` 確認

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

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

2518 </div>"

2519 # Get team list to which current user belongs into $hexteams

2520 myhexteams=$(hexteams "$grp" "$user")

2521 allhexteams=$(hexteams "$grp")

2522 if [ -n "$myhexteams" ]; then

2523 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2524 for=\"cmrmteam\">チーム属性除去</label>

2525 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2526 を除去します: `cgi_checkbox teamconfirm yes` 確認

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

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

2529 </div>

2530 "

2531 fi

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

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

2534 #b2='"> <span>' b3='</span></label>'

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

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

2537 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2538 cgi_form grpaction<<EOF \

2539 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2540 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

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

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

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

2544 $layout/form+dump.m4.html

2545 <p>下でチェックした人を対象として:</p>

2546 <div class="foldtabs">

2547 `cgi_radio rm addteam 'id="cmteam"'`<label

2548 for="cmteam">同じチーム属性を付与</label>

2549 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2550 `cgi_datalist_h teams $allhexteams`

2551 </div>

2552 ${rmteammsg}

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

2554 for="sendmsg">メッセージ送信</label>

2555 <div>

2556 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2557 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2558 `cgi_textarea text "" cols=40`

2559 </div>

2560 ${isowner:+$cmmsg$excmsg}

2561 `cgi_radio rm close id="x"`<label for="x">×</label>

2562 </div>

2563 <h4>$grp 参加者一覧</h4>$fromtonote

2564 <table class="td2r">

2565 `sq $db -header -html "$sql"`

2566 </table>

2567 `cgi_hidden grp $grid`

2568 EOF

2569 }

2570 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2571 # Create TEMPORARY VIEW

2572 query<<EOF

2573 CREATE TEMPORARY VIEW writeusers AS

2574 SELECT DISTINCT author FROM article

2575 WHERE id in (

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

2577 );

2578 CREATE TEMPORARY VIEW movablegroups AS

2579 SELECT g.rowid growid , g.gname

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

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

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

2583 WHERE (SELECT author FROM writeusers

2584 EXCEPT

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

2586 IS NULL;

2587 $2

2588 EOF

2589 }

2590 sql4readableblogs() {

2591 # Create view of blogs that can be readable to $user

2592 # Blog is readable when:

2593 # 1: blog owner is an user

2594 # 2: else, 2.1: owner-group where the $user belongs

2595 # 2.2: else, owner-group is not moderated

2596 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2597 cat<<EOF ## | tee tmp/sql.out

2598 CREATE TEMPORARY VIEW readableblogs AS

2599 SELECT blog.rowid rid, id, author

2600 FROM blog

2601 NATURAL JOIN

2602 (SELECT id,

2603 max(CASE key WHEN 'owner' THEN val END) owner,

2604 max(CASE key WHEN 'mode' THEN val END) mode

2605 FROM blog_s GROUP by id) bs

2606 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2607 THEN 1 -- blog owner is an user, READABLE

2608 WHEN (SELECT val FROM grp_s

2609 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2610 AND

2611 (SELECT user FROM grp_mem

2612 WHERE gname=bs.owner AND user='$user') IS NULL

2613 THEN 0

2614 WHEN mode = 'quiz'

2615 THEN 0 -- "quiz" mode blog cannot be searched

2616 ELSE 1

2617 END;

2618 EOF

2619 }

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

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

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

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

2624 return

2625 fi

2626 owner=`getvalbyid blog owner $rowid`

2627 title=`getvalbyid blog title $rowid`

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

2629 | _m4 -D_TITLE_="修正" \

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

2631 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

2634 # Move to group

2635 if isuser "$owner"; then

2636 crview4article $rowid

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

2638 ## err N=$n

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

2640 ## err ROWID=$rowid

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

2642 cat<<-EOF

2643 <div class="fold">

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

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

2646 <div>

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

2648 移動先グループ:

2649 <select name="mv2grp">

2650 EOF

2651 query ".mode html"

2652 query<<-EOF |

2653 $sql

2654 .mode list

2655 EOF

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

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

2658 cat<<-EOF

2659 </select>

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

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

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

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

2664 `cgi_hidden blogrowid $rowid`

2665 `cgi_submit 移動`

2666 `cgi_reset Reset`

2667 </form>

2668 </div>

2669 </div>

2670 EOF

2671 fi

2672 # end of isuser "$owner"

2673 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2674 [ -n "$hexteams" ];}; then

2675 none="`echo なし|hexize`"

2676 cat<<-EOF

2677 <div class="fold">

2678 `cgi_checkbox mv2team send id="mv2team"`<label

2679 for="mv2team">この話題を以下のチームのものにする</label>

2680 <div><p>現在の所属チーム設定:

2681 `query "SELECT

2682 coalesce((SELECT val FROM blog_s

2683 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2684 AND key='team'),

2685 ':なし');"`</p>

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

2687 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2688 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2689 `cgi_hidden blogrowid $rowid`<br>

2690 `cgi_submit 移動`

2691 `cgi_reset Reset`

2692 </form></div></div>

2693 EOF

2694 fi

2695 }

2696 mvart() { # move diary to some group or team

2697 # or move blog of group to team which belong to the group

2698 blogrowid=`getpar blogrowid`

2699 cfm=`getpar cfm`

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

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

2702 . ./s4-blog.sh

2703 if [ -z "$blogrowid" ]; then

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

2705 return

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

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

2708 elif { mv2grp=`getpar mv2grp`

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

2710 [ -n "$mv2grp" ]; }; then

2711 crview4article $blogrowid

2712 ########## TRANSACTION BEGIN

2713 query "BEGIN;"

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

2715 ## err Nwriteuser=$n

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

2717 query<<-EOF

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

2719 WHERE key='owner'

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

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

2722 EOF

2723 fi

2724 query "END;"

2725 ########## TRANSACTION END

2726 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2727 [ -n "$mv2team" ];}; then

2728 # blog owner can move it to ANY team

2729 case "$mv2team" in

2730 'なし')

2731 cat<<-EOF

2732 DELETE FROM blog_s

2733 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2734 AND key='team';

2735 EOF

2736 ;;

2737 "") ;;

2738 *)cat<<-EOF

2739 BEGIN;

2740 REPLACE INTO blog_s(id, key, val)

2741 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2742 'team', '$mv2team');

2743 REPLACE INTO blog_s(id, key, val)

2744 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2745 'notify', 'all'); -- Change notify to all

2746 END;

2747 EOF

2748 esac | query

2749 fi

2750 blog_reply $blogrowid

2751 echo yes | html p

2752 }

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

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

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

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

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

2758 return

2759 fi

2760 owner=`getvalbyid blog owner $blogrowid`

2761 title=`getvalbyid blog title $blogrowid`

2762 author=`getvalbyid article author $rowid`

2763 ## err EDITart: owner=$owner, author=$author

2764 if isgrpowner "$user" "$owner"; then

2765 : EDIT OK

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

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

2768 return

2769 fi

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

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

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

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

2774 > $tmpout

2775 rm -f /tmp/editart.out

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

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

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

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

2780 -D_BLOGS_= -D_DUMPTABLE_= \

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

2782 }

2783 send2mem() {

2784 rowid=`getpar grp`

2785 rowid=${rowid%%[!0-9]*} # Cleaning

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

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

2788 return

2789 fi

2790 message=`getpar message`

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

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

2793 return

2794 fi

2795 grp=`getgroupbyid $rowid`

2796 members=`collectemail $grp`

2797 # smail rcpt subj (file)

2798 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$admin>" \

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

2800 $urlbase?grp+$rowid

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

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

2803

2804 $message

2805 EOF

2806 cat<<EOF

2807 <p>以下のユーザに送信しました。</p>

2808 <pre>

2809 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

2810 </pre>

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

2812 EOF

2813 }

2814 joingrpadmit() {

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

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

2817 echo "bye bye" | html p; return

2818 fi

2819 t_usr=`session=$2 getpar user`

2820 t_grp=`session=$2 getpar group`

2821 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

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

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

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

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

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

2827 return

2828 fi

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

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

2831 fi

2832 case $1 in

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

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

2835 *)

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

2837 return ;;

2838 esac

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

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

2841 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2842 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2843 に

2844 $t_usr

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

2846 しました。"

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

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

2849 echo "$body" | html p

2850 }

2851

2852 joingrprequest() {

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

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

2855 addsession $jss +${memoplimitdays}days

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

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

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

2859 $url

2860 $user さんから

2861 グループ $1

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

2863

2864 承認する:

2865 $urlbase?joingrpadmit+yes+$jss

2866

2867 白紙に戻す:

2868 $urlbase?joingrpadmit+no+$jss

2869 EOF

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

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

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

2873 }

2874 joingrp() {

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

2876 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2877 if isgrpowner "$user" "$1"; then

2878 isowner="yes"

2879 elif [ -n "$5" ]; then

2880 isowner="yes"

2881 else

2882 isowner=""

2883 fi

2884 ## err jg:isgrpowner: isowner="$isowner"

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

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

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

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

2889 return

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

2891 : # Do not pursue those who leave

2892 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2893 : # Member can change own email address for the joining moderated group

2894 else # adding user is $user itself

2895 case `getgroupattr $1 regmode` in

2896 moderated)

2897 joingrprequest "$@" # Request only

2898 return

2899 ;;

2900 *)

2901 ;;

2902 esac

2903 fi

2904 qgname=`sqlquote "$1"`

2905 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

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

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

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

2909 # Notify joingrp to admin

2910 action="に加入しました。"

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

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

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

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

2915 else

2916 echo $msg

2917 fi

2918 else

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

2920 fi

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

2922 # Coming here means newly created group

2923 sql="select case\

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

2925 then (select user from grp_mem\

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

2927 else '' end; "

2928 err NewGrpChk: $sql

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

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

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

2932 fi

2933 fi

2934 else

2935 query "delete from grp_mem $cond;

2936 delete from grp_mem_s $cond;

2937 delete from grp_mem_m $cond;"

2938 action="から脱退しました。"

2939 fi

2940 smail "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

2941 $url?grp+$grid

2942 $user (`gecos $user`)さんが

2943 グループ $1

2944 $action

2945 EOF

2946 }

2947 grp_add_team() (

2948 # $1=grp-rowid $2=team $3...=user-rowid(s)

2949 grp=`getgroupbyid $1`

2950 team=$2; shift; shift

2951 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

2952 { echo "BEGIN;"

2953 for user; do

2954 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

2955 '$grp',\

2956 (SELECT name FROM user WHERE rowid=$user),\

2957 'team', 'string', '$team');"

2958 done

2959 echo "END;"

2960 } | query

2961 )

2962 grp_rm_team() (

2963 # $1=grp-rowid $2=team $3...=user-rowid(s)

2964 grid=$1

2965 qgrp=$(sqlquote "`getgroupbyid $grid`")

2966 team=$2; shift; shift

2967 [ -z "$grid" -o -z "$team" ] && return

2968 { echo "BEGIN;"

2969 for user; do

2970 echo "DELETE FROM grp_mem_m\

2971 WHERE gname=$qgrp \

2972 AND user=(SELECT name FROM user WHERE rowid=$user)\

2973 AND key='team' AND val='$team';"

2974 done

2975 cat<<-EOF

2976 DELETE FROM blog_s

2977 WHERE rowid=(

2978 SELECT rowid

2979 FROM blog_s a

2980 WHERE key='team'

2981 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

2982 AND NOT EXISTS (SELECT * FROM grp_mem_m

2983 WHERE key='team' AND val=a.val -- a.val=team

2984 AND gname = (SELECT val FROM blog_s b

2985 WHERE a.id=b.id AND key='owner')

2986 ));

2987 EOF

2988

2989 echo "END;"

2990 } | query

2991 )

2992 grp_reg_adm() {

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

2994 grid=$1

2995 grp=`getgroupbyid "$1"`

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

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

2998 fi

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

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

3001 fi

3002 shift

3003 for urid; do

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

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

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

3007 fi

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

3009 err ismember $newadm $grp

3010 if ismember $newadm "$grp"; then

3011 # OK, go ahead

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

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

3014 # confirm insertion

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

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

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

3018 else

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

3020 fi

3021 fi

3022 showgroup $grid

3023 done

3024 }

3025 dt_rowhack() {

3026 # From: <TR>

3027 # ....

3028 # <TD>rowclass=foo</TD>

3029 # </TR>

3030 # To: <TR class="foo">....<TD>foo</TD></TR>

3031 sed -e '

3032 /^<TR>/ {

3033 :loop

3034 s/\n//

3035 N

3036 /<\/TR>/ {

3037 s/\n//

3038 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3039 n

3040 }

3041 $q

3042 b loop

3043 }'

3044 }

3045 dumptable() {

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

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

3048 # $DT_VIEW sets link

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

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

3051 VIEW=${DT_VIEW-replyblog}

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

3053 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3054 fi

3055 # $DT_CHLD=ChildTable:BindColumn

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

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

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

3059 # XXX: Dirty workaround for slow subquery of acclog

3060 presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3061 SELECT * FROM acclog WHERE user='$user' and tbl='$2';"

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

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

3064 and val > coalesce((select time from myacclog where \

3065 tblrowid=a.rowid),\

3066 '1970-01-01'))"

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

3068 dt_class=" td2r td3r dumpblogs"

3069 fi

3070 # Construct join expression

3071 eav="" scols=""

3072 pk=`gettblpkey $2`

3073 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3074 substr=${substr:-%s}

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

3076 valvar=val

3077 case $col in

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

3079 continue ;; # built-in column name

3080 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3081 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3082 case "$as" in

3083 *:*=*) cnd=${as#*:}

3084 h=${cnd%%=*} v=${cnd#*=}

3085 h=`sqlquotestr "$h"`

3086 v=`sqlquotestr "$v"`

3087 valvar="CASE val WHEN $h THEN $v END"

3088 as=${as%%:*} ;;

3089 esac

3090 ;;

3091 *) as=${col} ;;

3092 esac

3093 ss=`printf "$substr" "$valvar"`

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

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

3096 done

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

3098 sql=${DT_SQL:-"select \

3099 a.rowid as LINK,\

3100 $cnt\

3101 $scols from $2 a left join\

3102 (select $pk,$eav,

3103 max(case key when 'owner'

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

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

3106 ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"

3107 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3108 <div>

3109 <div class="dumptable">

3110 <table class="b$dt_class">

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

3112 </table>

3113 </div>

3114 </div>

3115 EOF

3116 }

3117

3118 par2table() (

3119 # copy current parameters of par into destination table

3120 # $1=definition-file

3121 # Using $user and $session

3122 # Return value:

3123 # 0: Stored successfully

3124 # 1: Insufficient fillings

3125 # 2: No permission to modify the record

3126 # 3: Invalid rowid

3127 # 4: SUCCESS to delete

3128 # 5: Stop deletion for lack of confirm check

3129 # 6: Password length too short

3130 # 7: Password mismatch

3131 # 8: Old password incorrect

3132 rowid=`getpar rowid`

3133 if [ ! -e $1 ]; then

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

3135 exit 1

3136 fi

3137 tbl=${1%.def}

3138 tbl=${tbl##*/}

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

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

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

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

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

3144 ##err p2t:grp:q $sql

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

3146 else

3147 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3148 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3149 fi

3150 ### err rowowner=$rowowner

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

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

3153 return 2

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

3155 echo "指定したレコードはないみたい" | html p

3156 return 3

3157 fi

3158 rm=`getpar rm` cfm=`getpar confirm`

3159 # Editing existent entry

3160 if [ x"$rm" = x"yes" ]; then

3161 if [ x"$rm$cfm" = x"yesyes" ]; then

3162 query "delete from $tbl where rowid=$rowid;"

3163 return 4

3164 else

3165 echo "消去確認のチェックがないので消さなかったの..." | html p

3166 return 5

3167 fi

3168 fi

3169 fi

3170

3171 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3172 if [ -n "$rowid" ]; then

3173 # Update of existing record

3174 for col in `gettblcols $tbl`; do

3175 val=`getparquote $col`

3176 [ -z "$val" ] && continue

3177 ## err query "update $tbl set $col=$val where rowid=$rowid"

3178 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3179 sql="update $tbl set $col=$val where rowid=$rowid;"

3180 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3181 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3182 ## User name cannot be changed with interface provided with this

3183 ## script. But we offer the trigger to change owner user

3184 ## of blog_s table.

3185 #err "select quote($col) from $tbl where rowid=$rowid;"

3186 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3187 cat<<-EOF | query

3188 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3189 -- should use EXCLUSIVE transaction outside of this.

3190 SAVEPOINT par2table;

3191 $sql

3192 update blog_s set val=$val

3193 where key='owner' and val=$old;

3194 RELEASE SAVEPOINT par2table;

3195 EOF

3196 ## XX: DIRTY Hack Ends here

3197 ## We should keep blog's owner as a single column which has

3198 ## foreign key constraint with primary key of grp/user.

3199 else

3200 query "$sql"

3201 fi

3202 done

3203 # Then, set up $pval for further insertion of tbl_s and tbl_m

3204 for col in `gettblpkey $tbl`; do

3205 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3206 pval="$pval${pval:+, }\"$val\""

3207 done

3208 else

3209 # New entry

3210 # Generate values() for primary keys

3211 for col in `gettblpkey $tbl`; do

3212 # Genuine primary keys for _m and _s

3213 val=`getvalquote $tbl $col`

3214 [ -z "$val" ] && continue

3215 pval="$pval${pval:+, }$val"

3216 done

3217 ##err pval=$pval

3218 for col in `gettblfkey $tbl`; do

3219 # args for values() to insertion into master table

3220 val=`getvalquote $tbl $col`

3221 [ -z "$val" ] && continue

3222 formaster=$formaster"${formaster:+, }$val"

3223 done

3224 formaster="$pval${formaster:+, }$formaster"

3225 ## err formaster=$formaster

3226 if [ -z "$formaster" ]; then

3227 echo "項目を全て埋めてください" | html pre

3228 return 1

3229 fi

3230 ## err "replace into $tbl values($formaster);"

3231 query "replace into $tbl values($formaster);"

3232 ## Insertion to master table, done

3233 fi

3234

3235 for kt in s m; do

3236 tb2=${tbl}_$kt

3237 for col in `gettbl_${kt}_cols $tbl`; do

3238 ptype=`getpartype $col "limit 1"`

3239

3240 # First, check update of existing entries in _m

3241 if [ $kt = m ]; then

3242 # sessID|address.1.22|string|Somewhere-x.y.z

3243 sql=""

3244 ##err dots from query "select var from par where var like '$col.%';"

3245 for v in `query "select var from par where var like '$col.%';"`; do

3246 # v=address.1.22

3247 st_rowid=${v##*.}

3248 origcol=${v%%.*} # original column derived from

3249 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3250 ##case `getpartype $v` in

3251 ## err CASE `gettbl_coltype $tbl/$origcol` in

3252 ## err edit flag = `getpar action.$v`

3253 case `getpar action.$v` in

3254 rm)

3255 if [ x`getpar confirm.$v` = x"yes" ]; then

3256 newsql="delete from $tb2"

3257 else

3258 echo "削除確認未チェック" | html p

3259 fi ;;

3260 edit)

3261 case `gettbl_coltype $tbl/$origcol` in

3262 image|document|binary)

3263 file=$tmpd/`getparfilename $v`

3264 ## err type=file=$file

3265 [ -z "$file" ] && continue

3266 bn=`sqlquotestr "${file##*/}"`

3267 bin="X'"$(hexize "$file")"'"

3268 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3269 type=\"file:$ct\"

3270 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3271 cachedir=`getcachedir "$tbl/$rowid"`

3272 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3273 rm -rf $cachedir

3274 ;;

3275 *)

3276 newsql="update $tb2 set val=(select val from par where var \

3277 like '$col.%.$st_rowid')"

3278 ;;

3279 esac

3280 ;;

3281 *) # maybe "keep", do not modify value

3282 continue

3283 ;;

3284 esac

3285 # err newsql=$newsql

3286 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3287 done

3288

3289 if [ x"$bin" = x"NULL" ]; then

3290 ## err repl:normal sql=`echo $sql`

3291 query "$sql

3292 delete from $tb2 where type='string' and val='';"

3293 ## err repl:normal done

3294 else

3295 sqlfile="$tmpd/sqlf.$$"

3296 echo "$sql" > $sqlfile

3297 ## err repl:sqlfile=`ls -lF $sqlfile`

3298 query ".read $sqlfile"

3299 ## err repl:done

3300 fi

3301 # Rest of kt==m: set multiple mode

3302 nr=`getparcount $col`

3303 else

3304 nr=1 # for kt==s, number of records is 1

3305 fi

3306

3307 i=0

3308 while [ $i -lt $nr ]; do

3309 limit="limit 1 offset $i"

3310 i=$((i+1)) # increase beforehand against continue

3311 val=`getvalquote $tbl $col "$limit"`

3312 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3313 ## err $col=$val

3314 bin=NULL

3315 ## err partype$col=`getpartype $col "$limit"`

3316 case $ptype in

3317 file) file=$tmpd/`getparfilename $col "$limit"`

3318 ## err parfile-$col=$file

3319 [ -z "$file" ] && continue

3320 bin="X'"$(hexize "$file")"'"

3321 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3322 type=\"file:$ct\" ;;

3323 "*"*) continue ;; # foreign table

3324 *) type=\"string\" ;;

3325 esac

3326 case `gettbl_coltype $tbl/$col` in

3327 password) # special care for password

3328 # name={password,pswd1,pswd2}

3329 p1=`getpar pswd1 "$limit"`

3330 if [ -z "$p1" ]; then

3331 continue # SKIP password setting, if p1 is empty

3332 else

3333 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3334 ## err pswd=$pswd

3335 if pwcheck "$pswd"; then

3336 if [ x"$p1" = x"$p2" ]; then

3337 case "$p1" in

3338 ??????????*) ;;

3339 *) echo "パスワードは10字以上にしてください。" | html p

3340 return 6;;

3341 esac

3342 val="\"`echo $p1|mypwhash`\""

3343 else

3344 echo "2つの新パスワード不一致" | html p

3345 return 7

3346 fi

3347 else

3348 echo "旧パスワード違います" | html p

3349 return 8

3350 fi

3351 fi

3352 ;;

3353 esac

3354 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3355 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3356 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3357 if [ x"$bin" = x"NULL" ]; then

3358 ## err Normal-query: `echo $sql`

3359 query "$sql"

3360 else

3361 sqlfile="$tmpd/query.$$"

3362 echo "$sql" > $sqlfile

3363 ## err sqlfile=`ls -lF $sqlfile`

3364 query ".read $sqlfile"

3365 fi

3366 ## err p2t done

3367 done

3368 done

3369 done

3370 return 0

3371 ##err donee

3372 )

3373 genform() {

3374 # $1 = form definition file

3375 # $2, $3 (optional)= table name and ROWID

3376 # If $GF_VIEWONLY set and nonNull, output values without form

3377 # If $GF_ARGS set, use it as content-strings in the form

3378 # If $GF_OWNER set, use it as value of name="owner"

3379 # If $GF_STAGE set, use it as value of name="stage"

3380 forms="" hiddens="" rowid=$3

3381 if [ ! -e "$1" ]; then

3382 echo "そのようなデータベースはないようです($2)。" | html p

3383 return

3384 elif [ -n "$2" ]; then

3385 rec=`query "select * from $2 where rowid='$rowid';"`

3386 if [ -z "$rec" ]; then

3387 pk=`gettblpkey $2`

3388 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3389 rec=`query "select rowid from $2 where $pk='$rowid';"`

3390 rowid=$rec

3391 rec=$3

3392 fi

3393 if [ -z "$rec" ]; then

3394 echo "そんなレコードはないみたいね..." | html p

3395 return

3396 fi

3397 fi

3398 if [ -z "$GF_VIEWONLY" ]; then

3399 rm='<input id="rm" name="rm" type="checkbox"

3400 value="yes"><label for="rm">このエントリの削除</label>

3401 <span>ほんとうに消しますよ(確認)!

3402 <input name="confirm" type=checkbox value="yes">はい</span>'

3403 fi

3404 # Image Cache dir

3405 ## err genform: getcache=$2/$rowid

3406 td=`getcachedir "$2/$rowid"`

3407 while IFS=: read prompt name keytype type args; do

3408 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3409 sp="${args:+ }"

3410 form="" val=""

3411 if [ -n "$rowid" ]; then

3412 # err genform2a: Seeking for "$2.$name, type=$type"

3413 rawval=`getvalbyid $2 $name $rowid $td`

3414 val=`echo "$rawval"|htmlescape`

3415 ## err genform3a: getvalbyid $2 $name $rowid $td

3416 ## err genform3b: val="[$val]" type="$type"

3417 fi

3418 if [ -n "$GF_VIEWONLY" ]; then

3419 is_hidden "$2" "$name" && continue

3420 fi

3421 case "$type" in

3422 text*)

3423 cgiform=cgi_multi_$type

3424 if [ -s $td/$name.count -a -n "$val" ]; then

3425 form=`$cgiform $name $td`

3426 val=$(echo "$val"|

3427 while read fn; do

3428 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3429 </td></tr>$nl"

3430 done)

3431 val="<table>$nl$val$nl</table>"

3432 else

3433 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3434 form=`cgi_$type $name "$rawval" "$args"`

3435 fi

3436 ;;

3437 [Rr][Aa][Dd][Ii][Oo])

3438 fh="<label><input type=\"radio\" name=\"$name\""

3439 form="`echo $args|sed -e \

3440 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3441 ;;

3442 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3443 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3444 ;;

3445 [Ss][Ee][Ll][Ee][Cc][Tt])

3446 fh="<select name=\"$name\">$nl"

3447 form=$(for l in $args; do

3448 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3449 done)

3450 if [ -n "$val" ]; then

3451 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3452 fi

3453 form="$fh$form</select>"

3454 ;;

3455 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3456 if [ -s $td/$name.count ]; then

3457 form=`cgi_multi_file $name $td "$args"`

3458 if [ -n "$val" ]; then

3459 hrfb="$myname?showattc+$2_m"

3460 val=$(echo "$rawval" \

3461 | while read fn; do

3462 data=`percenthex "$td/$fn"`

3463 #ct=`cat $td/$fn.content-type`

3464 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3465 ri=`cat "$td/$fn.rowid"`

3466 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3467 #imgsrc="<img src=\"data:$ct,$data\">"

3468 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3469 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3470 done)

3471 fi

3472 else

3473 form="<input type=\"file\" name=\"$name\" $args>"

3474 if [ -n "$val" ]; then

3475 imgs=$(echo "$rawval"\

3476 |while read fn;do

3477 data=`percenthex "$td/$fn"`

3478 echo "<img src=\"data:image/png,$data\">$fn<br>"

3479 done)

3480 form=$form"<br>$imgs"

3481 val=$imgs # 2015-06-15

3482 else

3483 form="<input type=\"file\" name=\"$name\" $args>"

3484 fi

3485 fi

3486 ;;

3487 [Hh][Ii][Dd][Dd][Ee][Nn])

3488 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3489 args="value=\"$GF_STAGE\""

3490 fi

3491 form="<input type=\"hidden\" name=\"$name\" $args>"

3492 prompt='' # Remove prompt

3493 ;;

3494 [Aa][Uu][Tt][Hh][Oo][Rr])

3495 [ -n "$GF_VIEWONLY" ] && continue

3496 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3497 prompt="" ;;

3498 [Oo][Ww][Nn][Ee][Rr])

3499 [ -n "$GF_VIEWONLY" ] && continue

3500 val=${GF_OWNER:-$val}

3501 val=${val:-$user}

3502 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3503 prompt="" ;;

3504 [Uu][Ss][Ee][Rr])

3505 # XXX: is null $user ok?

3506 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3507 [ -n "$GF_VIEWONLY" ] && continue

3508 form="$user"

3509 ;;

3510 [Pp]assword)

3511 [ -n "$GF_VIEWONLY" ] && continue

3512 form="`cgi_passwd`"

3513 val=""

3514 ;;

3515 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3516 [ -n "$GF_VIEWONLY" ] && continue

3517 if [ -z "$rowid" ]; then

3518 val=`genserial`

3519 fi

3520 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3521 prompt="" ;;

3522 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3523 prompt=""

3524 ;;

3525 parent|path|blog*)

3526 prompt=""

3527 ;;

3528 "*"*)

3529 tail=$tail"``"

3530 continue ;;

3531 esac

3532 if [ -n "$prompt" ]; then

3533 if [ -n "${GF_VIEWONLY}" ]; then

3534 form=$val

3535 else

3536 :

3537 fi

3538 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3539 else

3540 hiddens=$hiddens$nl"$form"

3541 fi

3542 done < $1

3543 # enctype="multipart/form-data"

3544 cat<<EOF

3545 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3546 ${rowid:+$rm}

3547 <table class="b $2">

3548 $forms

3549 </table>$hiddens

3550 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3551 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3552 EOF

3553 if [ -z $GF_VIEWONLY ]; then

3554 cat<<EOF

3555 <input type="submit" name="sub" value="OK">

3556 <input type="reset" name="res" value="Reset">

3557 EOF

3558 fi

3559 cat<<EOF

3560 $GF_ARGS</form>

3561 $tail

3562 EOF

3563 }

3564 edittable() {

3565 # $1=form-def $2=table $3 rowid

3566 genform "$@"

3567 }

3568 viewtable() {

3569 GF_VIEWONLY=1 genform "$@"

3570 }

3571 showattc() {

3572 # $1=table_m $2=rowid &optional $3=RawFlag

3573 ## err \$1=$1 \$2=$2 \$3=$3

3574 if ! isfilereadable $user $1 $2; then

3575 contenttype; echo

3576 echo "このファイルは管理者のみしか見られません" | html p

3577 putfooter; exit

3578 fi

3579 idir=`umask 002; mktempd` || exit 1

3580 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3581 bin=$idir/$myname-$$.bin

3582 sql="select quote(bin) from $1 where rowid='$2';"

3583 ## err showattc: sql: $sql

3584 sq $db "$sql" | unhexize > $bin

3585 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3586 type=${tv%//*} fn=${tv#*//}

3587 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3588 ct=${type#file:}

3589 case $ct in # all text/* changed to text/plain

3590 text/*)

3591 charset=`nkf -g $bin|cut -d' ' -f1`

3592 case $charset in

3593 ASCII*) charset="" ;;

3594 esac

3595 if [ -z "$3" ]; then

3596 ct="text/html${charset:+; charset=$charset}"

3597 link="?showattc+$1+$2+raw"

3598 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3599 | sed 's,^,<span></span>,' \

3600 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3601 -D_LINK_="$link" \

3602 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3603 exit $?

3604 fi

3605 ct="text/plain${charset:+; charset=$charset}"

3606 ;;

3607 esac

3608 contenttype "$ct"

3609 echo "Content-Disposition: filename=\"$fn\""

3610 echo "Content-Length: " `cat $bin | wc -c`; echo

3611 #echo "Content-Type: " ${type#file:}; echo

3612 cat $bin

3613 }

3614 #

3615 # Some default stupid handler on CGI values

3616 #

3617 default_storedb() {

3618 # ARG: $1=table-def-file

3619 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3620 tbl=`basename $1`

3621 tbl=${tbl%.def}

3622 cols="`grep :text $1|cut -d: -f2`"

3623 col=`echo "$cols"|head -1`

3624 vcol=`getpar $col`

3625 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3626 if [ -n "$vcol" ]; then

3627 par2table $1

3628 else

3629 return 2 # No insertion occurred

3630 fi

3631 }

3632

3633 default_view() { # $1=def-file

3634 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3635 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3636 default_storedb "$@"

3637 query "select rowid from $tbl order by rowid desc;" \

3638 | while read rowid; do

3639 viewtable $1 $tbl $rowid

3640 done | _m4 -D_TITLE_="$tbl" \

3641 -D_FORM_="`genform $1`" \

3642 -D_DUMPTABLE_="syscmd(cat)" \

3643 $layout/html.m4.html $layout/form+dump.m4.html

3644 }

3645 default_viewtext() { # $1=def-file

3646 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3647 default_storedb "$@"

3648 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3649 | _m4 -D_TITLE_="$tbl" \

3650 -D_FORM_="`genform $1`" \

3651 -D_DUMPTABLE_="syscmd(cat)" \

3652 $layout/html.m4.html $layout/form+dump.m4.html

3653 }

3654 default_smail() {

3655 default_storedb "$@"

3656 if [ $? -eq 2 ]; then

3657 _m4 -D_TITLE_="入力" \

3658 -D_FORM_="`genform $1`" \

3659 -D_DUMPTABLE_="" \

3660 $layout/html.m4.html $layout/form+dump.m4.html

3661 return

3662 fi

3663 cond=""

3664 for pk in `gettblpkey $tbl`; do

3665 pv=$(sqlquote "$(getpar $pk)")

3666 cond="$cond${cond:+ and }$pk=$pv"

3667 done

3668 sql="select rowid from $tbl where $cond;"

3669 rowid=`query "$sql"`

3670 ## err smail1 - "$sql" "-> rowid=$rowid"

3671

3672 while IFS=: read prompt name keytype type args; do # Read from $1

3673 val=`getpar $name`

3674 if [ -n "$val" ]; then

3675 text="$text

3676 $prompt

3677 $name=$val

3678 ---------------------------------------------------------"

3679 fi

3680 case "$type" in

3681 image|document|file)

3682 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3683 fns=$(echo "$fn"|while read fn; do

3684 err mv $tmpd/$fn.orig $tmpd/$fn

3685 mv $tmpd/$fn.orig $tmpd/$fn

3686 rm $tmpd/$fn.rowid # Remove cache flag

3687 ## err "`ls $tmpd/$fn`"

3688 echo $fn

3689 done)

3690 files="$files $fns"

3691 ;;

3692 esac

3693 done < $1

3694 ## err FILES=$files "`ls -lF $tmpd`"

3695 subj="from ${REMOTE_ADDR}"

3696 (echo "$url"

3697 echo "への書き込みがありました。"

3698 echo "------"

3699 echo "$text"

3700 ) | (cd $tmpd &&

3701 err LS="`ls -lF`" &&

3702 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3703 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3704 echo "以下の内容で送信しました。" | html p

3705 viewtable $1 $tbl \

3706 `query "select rowid from $tbl order by rowid desc limit 1;"`

3707 echo "戻る" | html a "href=\"?\""

3708 }