s4: c81511a6b7e0 s4-funcs.sh

s4

view s4-funcs.sh @ 576:c81511a6b7e0

Add feature of "clone without team"

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 14 Jun 2019 09:01:51 +0900
parents	618ecf019115
children	67ecd5901590

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 test -n "$HTTP_HOST" && isCGI=true

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

12 tmpdir=${TMPDIR:-tmp}

13 dbdir=${DBDIR:-db}

14 tmpfiles=""

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

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

17 templ=${TEMPL:-templ}

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

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

20 imgdir=${IMGDIR:-img}

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

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

23 msgdir=$templ/msg

24 timeout="+2 days"

25 memoplimitdays="7"

26 dumpcollen=22

27 #thumbxy=120x120

28 thumbxy=96x96

29 iconxy_S=80x80

30 iconxy_M=400x400

31 maximagexy=1600x1600

32 ### maximagexy=400x400

33 filesize_max=$((5*1024*1024))

34 filesize_max_MB="$((filesize_max/1024/1024))MB"

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

37 file_accept_help="

38 添付可能ファイル: テキスト、画像、音声、動画、ODF、PDF、

39 圧縮ファイル、データベースファイル

40 (いずれも ${filesize_max_MB} 以内)

41 "

42 file_warn="$file_accept_help

43 [編集]リンクから修正してください。"

44 blogreadflagrowid=0

45 blogcutoffflagrowid=-1

46 querylog=$tmpdir/query.log

47 searchlog=$tmpdir/search.log

49 tconfs=""

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

51 conftbl=_tblconf

52 nl="

53 "

54 likeesc=`printf '\037'` # ESCAPE char of LIKE operator

55 iconcachekey="profimgcache_S"

56 case "$HTTP_USER_AGENT" in

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

58 *) touchpanel="" ;;

59 esac

60 [ -f ./s4-cgi.sh ] && . ./s4-cgi.sh

62 : <<EOF

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

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

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

67 レポート提出管理

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

70 [2016]

71 7/12 根本への反省

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

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

75 [以下2015]

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

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

78 Web

79 締切設定

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

82 ○自分の提出物リスト

84 7/19 ○設置

85 ○一斉送信

86 ○getparfilename の tmpd の扱い

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

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

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

90 ○添付ファイル回収

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

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

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

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

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

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

98 ○レポート提出モード

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

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

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

102

103

104 7/9 ○管理者の追加

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

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

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

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

109

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

111

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

113 ○グループ検索

114

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

116

117

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

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

120

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

122 ○6/1 par2tableを triplex 対応に

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

124 →とすると一気に

125

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

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

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

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

130

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

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

133 ## 1. passwd

134 ## 2. grp

135 ## 3. grp_mem

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

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

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

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

140

141 ## ● listの定義:

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

143 ## ● hashの定義:

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

145

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

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

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

149 ## csv2sq3 で .csv.sq3 の Makefile

150

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

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

153 ## type := root | comment

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

155 ## unique(id, filename)

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

157

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

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

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

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

162 ## できたー!

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

164 ↓

165 ↓以下に変更

166 with recursive allmem as

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

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

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

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

171

172

173 with recursive allmem as

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

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

176 where allmem.val=grp_m.gname)

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

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

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

180

181

182 ## triggerもできた。

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

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

185

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

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

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

189 ## グループ管理

190 ## 個人情報管理

191 ##

192

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

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

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

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

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

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

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

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

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

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

203

204 ## ログテーブル

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

206

207

208

209 ■表設計

210 * 3つの表に分散管理

211 id格納表 + hash表 + list表

212 * *_s *_m

213

214

215

216 user, user_map, user_col

217

218 ■抽象エントリタイプ

219 * user

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

221 * group

222 権限判定に利用

223 * serial

224 自動idとして機能

225 * password

226 入力 type=passwordで入力

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

228 * session

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

230 * text

231 入力 type=text

232 * textarea

233 入力 textarea

234 * image|document

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

236 * owner

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

238 * gowner

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

240 * timestamp

241 datetime()

242 * parent

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

244 * path

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

246

247 格納タイプ

248 * list

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

250 * hash

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

252

253 オブジェクトタイプ

254 * entry

255 id, title, owner

256 * textpart

257 id, parentID, text

258 * binarypart

259 id, parentID, contenttype, filename, content

260 * content

261 hash(textpart), list(binarypart)

262 * topic

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

264 * reply

265 id, parentID, content

266 * blog

267 list(entry)

268 blog = [topic, list(reply)]

269

270

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

272 "text" => "hogehoge ..",

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

274 "author" => "taro",

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

276 "parent" => "/",

277 "path" => "/1",

278 "text" => "blah, blah, ....",

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

280 {"serial" => 2,

281 "author" => "hanako",

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

283 "parent" => "/",

284 "path" => "/2",

285 "text" => "blah, blah, ....",

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

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

288

289

290 user:=

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

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

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

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

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

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

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

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

299

300 変換表

301 /user/email=m

302

303 blog:=

304 シリアル:id:p:serial

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

306 所有者:owner:s:owner:

307 時刻:ctime:s:stamp:

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

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

310

311 article:=

312 シリアル:id:p:serial

313 筆者:author:s:owner

314 時刻:ctime:s:stamp:

315 参照元:parent:s:parent:

316 パス:path:s:path:

317 本文:text:s:textarea:

318 画像:image:m:image:

319

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

321

322

323 EOF

324

325 logstart() {

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

327 }

328 logend() {

329 echo ">>>" >> ${1:-$querylog}

330 }

331 sqlog() {

332 logstart

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

334 cat >> $querylog

335 else

336 echo "$*" >> $querylog

337 fi

338 logend

339 }

340 sq() {

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

342 logstart

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

344 tee -a $querylog|sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000"

345 else

346 echo "$@" >> $querylog

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

348 fi

349 logend

350 }

351 dbsetup() {

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

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

354 sqi=$tmpdir/sqi.$$

355 sqo=$tmpdir/sqo.$$

356 mkfifo $sqi $sqo

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

358 sq $db < $sqi &

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

360 if [ -n "$isCGI" ]; then

361 exec 2>> $tmpdir/error.out

362 fi

363 exec 3>> $tmpdir/debug.out

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

365 rm $sqi

366 }

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

368 if [ -n "$HTTP_USER_AGENT" ]; then # When called from httpd

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

370 fi

371 }

372 cleanup() {

373 trap '' INT HUP EXIT TERM PIPE

374 echo .quit >&5

375 kill $sq3pid

376 kill $sq3pid

377 rm -f $sqo $sqi

378 rm -rf $tmpfiles

379 cleanup2

380 }

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

382 # simple redirection for the sake of speed.

383 query() {

384 # echo ".once $sqo" >&5

385 echo ".output $sqo" >&5

386 logstart

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

388 tee -a $querylog

389 else

390 echo "$@" >> $querylog

391 echo "$@"

392 fi >&5

393 echo ".output stdout" >&5

394 cat $sqo

395 logend

396 }

397 _m4() {

398 #S4NAME=f,f,f

399 m4 ${S4NAME:+"-D_S4NAME_=${S4NAME}"} ${S4CSS:+-D_S4CSS_="$S4CSS"} "$@"

400 }

401 ismember() {

402 # $1=user, $2=group

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

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

405 }

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

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

408 }

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

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

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

412 }

413 isgrpowner() (

414 # $1=user, $2=group

415 gn=`sqlquote "$2"`

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

417 err isgrpowner: $sql

418 test -n "`query $sql`"

419 )

420 getgroupadminmails() {

421 # $1=group

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

423 email4group "$1" "$i" ;

424 done

425 }

426 getgroupadmins() { # $1=group

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

428 qgrp=`sqlquote "$1"`

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

430 }

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

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

433 getvalbyid grp $2 \

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

435 }

436 getgroupbyid() {

437 # $1=id|gname

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

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

440 # err ggbyid: `echo $sql`

441 query $sql

442 }

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

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

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

446

447 # Return true when anonymous mode

448 [ "$anonymousmode" ] && return 0

449 # case `getvalbyid blog mode $2` in

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

451 # *closed*)

452 # owner=`getvalbyid blog owner $2`

453 # if isgrp $owner; then

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

455 # elif isuser $owner; then

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

457 # fi

458 # esac

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

460 sql="with getblog as (\

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

462 select blogid from article where id in\

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

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

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

466 select case\

467 when (select author from article where\

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

469 then 'author'\

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

471 then 'open'\

472 when (select val from getmode) is null \

473 then 'open'

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

475 then (select user from grp_adm where \

476 gname=(select val from getowner) and \

477 user='$1')\

478 when (select author from article where\

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

480 then 'user+author'

481 else '' end;"

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

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

484 result=`query "$sql"`

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

486 return 2

487 }

488 linkhome() {

489 # $1=UserOrGroup

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

491 if isuser $1; then

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

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

494 else

495 echo -n "grp+$1"

496 fi

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

498 }

499 hreflink() {

500 # s4 specific notation:

501 # ^href=URL

502 # ^iframe=URL

503 # ^video=URL

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

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

506 # OSM umap Wikistyle Notation:

507 # [[URL]] - Simle Link

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

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

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

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

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

513 # Other Style

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

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

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

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

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

519 # SPC+SPC+$ - <br>

520 cb='<input type="checkbox" class="s4-checkbox" disabled'

521 checkboxON="${cb} checked>"

522 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

535 -e "s|^video=$$_hrefptn$|<video controls><source height=\"320\" src=\"\1\"></video>|" \

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

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

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

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

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

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

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

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

544 -e 's, $,<br>,' \

545 -e "s,- \[ *\]$[^|-]*$,${checkboxOFF}<label>\\1</label>,g" \

546 -e "s,- \[[^ ]\]$[^|-]*$,${checkboxON}<label>\\1</label>,g" \

547

548 }

549 minitbl() {

550 sed -n '

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

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

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

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

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

556 H; # Concat this line to HoldSpace

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

558 $ b cont

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

560 }

561 :cont

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

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

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

565 # . of ^. is workaround for FreeBSD sed

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

567 p; # Print whole "table" element

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

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

570 }

571 x; # Back to the newest line

572 p; # Print rest' | miniul

573 }

574 miniul() {

575 sed -e '

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

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

578 :top

579 s/\n//;

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

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

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

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

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

585 $ b cont

586 N; # 次の行を読む

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

588 /^ /b top

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

590 p; s/.*//;

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

592 /^\* /b top

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

594 }

595

596 :cont

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

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

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

600 H; x

601 }

602 x'

603 }

604 acclog() (

605 # $1=table, $2=rowid

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

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

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

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

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

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

612 fi

613 )

614 gecos() (

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

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

617 )

618 setpar() {

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

620 }

621 replpar() {

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

623 }

624 getpar() {

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

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

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

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

629 fi

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

631 case "$var" in

632 owner)

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

634 echo $user; return

635 elif ismember $user $val; then

636 echo $val; return

637 fi ;;

638 esac

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

640 echo "$val"

641 }

642

643 getpartype() {

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

645 }

646 getparcount() {

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

648 }

649 getparfilename() {

650 # null if type of $1 is not file

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

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

653 }

654 sqlquote() {

655 (v="$1"

656 case "$v" in

657 "") return ;; # null

658 "X'"*) # quoted hex string

659 echo $1 ;;

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

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

662 echo "\"$v\""

663 return ;;

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

665 echo "\"$v\""

666 return ;;

667 *)

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

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

670 else

671 echo "\"$v\""

672 fi ;;

673 esac)

674 }

675 sqlquotestr() (

676 case "$1" in

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

678 echo "'$v'" ;;

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

680 esac

681 )

682 mktempd() {

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

684 }

685 getcachedir() { # $1=maintable

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

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

688 else

689 echo $tmpd/$thumbxy

690 fi

691 }

692 getval() {

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

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

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

696 echo "$user" ;;

697 stamp|datetime)

698 date "+%F %T" ;;

699 serial)

700 (s=`getpar $2`

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

702 *)

703 getpar "$2" "$3";;

704 esac

705 }

706

707 getvalquote() {

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

709 (v=`getval "$@"`

710 case "$v" in

711 "") echo NULL ;;

712 *) sqlquote "$v" ;;

713 esac)

714 }

715 getparquote() {

716 sqlquote "`getpar $1`"

717 }

718 getbinbyid() {

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

720

721 }

722 getvalbyid() {

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

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

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

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

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

728

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

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

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

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

733 return

734 fi

735 done

736 rowid=$3

737 pk=`gettblpkey $1`

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

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

740 td=${4:-$tmpd}

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

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

743 for kt in s m; do

744 t=${1}_$kt

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

746 vcount=1 # count(val)

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

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

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

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

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

752 if [ $kt = m ]; then

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

754 # Reset val to store filenames if type is string

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

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

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

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

759 i=0

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

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

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

763 i=$((i+1))

764 fn=$c.$i

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

766 case $type in

767 file:*)

768 #file=$td/$val

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

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

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

772 # FOR SPEED: Skip file generation if imgcache exists

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

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

775 && continue

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

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

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

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

780 select rowid from $cond $slice;

781 .output '$td/$fn'

782 select val from $cond $slice;

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

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

785 .output stdout

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

787 EOF

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

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

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

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

792 ls -lh "$file" |

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

794 case "$type" in

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

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

797 *|*:[Aa]pplication*)

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

799 png:- > "$file"

800 ;;

801 esac

802 ;;

803 *)

804 sq $db<<EOF

805 .output $td/$fn.rowid

806 select rowid from $cond $slice;

807 .output $td/$fn

808 select val from $cond $slice;

809 EOF

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

811 ;;

812 esac

813 done

814 else

815 rm -f $td/$c.count

816 case $type in

817 file:*)

818 echo "$val" \

819 | while read fn; do

820 file=$td/$fn

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

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

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

824 | unhexize > "$file"

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

826 case $type in

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

828 *:[Aa]pplication*)

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

830 png:- > $file ;;

831 esac

832 fi

833 done

834 ;;

835 esac

836 fi

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

838 return

839 fi

840 done

841 done)

842 }

843 getvalbypkey() (

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

845 pk=`gettblpkey $1`

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

847 getvalbyid "$1" "$2" $rowid $4

848 )

849 getvalbycond() {

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

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

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

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

854 getvalbyid "$1" "$2" $rowid "$4"

855 fi

856 }

857 getpwfield() {

858 # getpwfield user column

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

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

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

862 echo "$val"

863 return 0

864 else

865 return 1

866 fi

867 }

868 encode() {

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

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

871 sha1=sha1

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

873 sha1=sha1sum

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

875 sha1=gsha1sum

876 fi

877 fi

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

879 }

880 enjpeg() {

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

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

883 cjpeg="cjpeg"

884 else

885 cjpeg="convert - jpeg:-"

886 fi

887 fi

888 $cjpeg "$@"

889 }

890 mycrypt() (

891 key=$1 salt=$2

892 err \$2=$2

893 case $2 in

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

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

896 esac

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

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

899 )

900 hexize() {

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

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

903 hexize="xxd -p"

904 else

905 hexize_hd() {

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

907 }

908 hexize="hexize_hd"

909 fi

910 fi

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

912 }

913 unhexize() {

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

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

916 unhex="xxd -p -r"

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

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

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

920 EOF

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

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

923 # world writable...:)

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

925 fi

926 fi

927 cat "$@" | $unhex

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

929 }

930 percenthex() {

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

932 }

933 htmlescape() {

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

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

936 }

937 enascii() {

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

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

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

941 else

942 enascii_now=`date +%FT%T`

943 enascii_sed() {

944 nkf -Z0Z1Z2 \

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

946 }

947 enascii="enascii_sed"

948 fi

949 fi

950 cat "$@" | $enascii

951 }

952 size_h() {

953 i="$1" oi=$1

954 set -- B B KB MB GB TB

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

956 oi=$i

957 i=$((i/1024))

958 shift

959 done

960 echo ${oi}$1

961 }

962 gettblconf() {

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

964 ## tconfs=`sq $db \

965 tconfs=`query \

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

967 fi

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

969 }

970 gettblkeys() {

971 # $1=tbl

972 gettblconf

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

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

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

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

977 case $type in

978 *p*)

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

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

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

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

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

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

985 esac

986 case $type in

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

988 esac

989 done

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

991 }

992 gettblpkey() {

993 # $1=tbl

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

995 }

996 gettblfkey() {

997 (x=`gettblkeys $1`

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

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

1000 }

1001 gettblcols() {

1002 (x=`gettblkeys $1`

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

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

1005 }

1006 gettbl_s_cols() {

1007 (x=`gettblkeys $1`

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

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

1010 }

1011 gettbl_m_cols() {

1012 (x=`gettblkeys $1`

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

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

1015 }

1016 gettbl_h_cols() {

1017 (x=`gettblkeys $1`

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

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

1020 }

1021 gettbl_coltype() (

1022 gettblconf

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

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

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

1026 )

1027 is_hidden() {

1028 # $1=Tbl $2=col

1029 gettblconf

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

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

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

1033 case $x in

1034 *h*) return 0 ;;

1035 *) return 1 ;;

1036 esac

1037 }

1038

1039 dbsetbyid() {

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

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

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

1043 quotedp=$(sqlquotestr "$p")

1044 unset primary update

1045 gettblconf

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

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

1048 #err conf=$conf

1049 case ${conf#*=} in

1050 p*) primary=1 ;;

1051 f*) update=1 ;;

1052 u*) ;;

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

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

1055 esac

1056 #err t=$t

1057 type=string fn=""

1058 case $conf in

1059 */password)

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

1061 ;;

1062 */image*|*/document*)

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

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

1065 ;;

1066 esac

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

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

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

1070 if [ "$primary" ]; then

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

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

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

1074 elif [ "$update" ]; then

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

1076 else

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

1078 fi

1079 )

1080 }

1081 expire() (

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

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

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

1085 )

1086 addsession() {

1087 # expireをセット

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

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

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

1091 # Remove old session parameters

1092 now=`expire now`

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

1094 }

1095 gencookie() (

1096 for kv; do

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

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

1099 done

1100 )

1101 contenttype() {

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

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

1104 }

1105 putheader() {

1106

1107 }

1108 putfooter() {

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

1110 }

1111 getcookie() (

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

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

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

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

1116 done

1117 )

1118 genrandom() {

1119 # $1=columns (default: 10)

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

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

1122 }

1123 genserial() {

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

1125 }

1126 smail() {

1127 # smail rcpts subj (file)

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

1129 # $MAIL_FROM <- From: header value

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

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

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

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

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

1135 }

1136 setviastring() {

1137 table=$1

1138 oifs="$IFS"

1139 IFS="&"

1140 for us in $2; do

1141 k=${us%%=*}

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

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

1144 #echo $k=$v

1145 done

1146 IFS="$oifs"

1147 }

1148 checkdomain() (

1149 # Check the validity of domain by referring DNS

1150 item=$1

1151 err checkdomain $1

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

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

1154 )

1155 pwcheck() {

1156 # $1=passwd

1157 dbpswd=`getpwfield $user pswd`

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

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

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

1161 }

1162 mypwhash() {

1163 mycrypt "`cat`" `genrandom 5`

1164 }

1165 wasureta() {

1166 user=$1

1167 if ! checkdomain $user; then

1168 contenttype; echo

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

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

1171 putfooter

1172 exit 0

1173 fi

1174 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1176 encpswd=`echo $newpswd|mypwhash`

1177 dbsetbyid user $user pswd "$encpswd"

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

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

1180 $msgdir/mail-newaccount.m4 \

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

1182 | smail $user "New Account"

1183 }

1184 checkauth() {

1185 user=`getpar user`

1186 skc=`getpar skey` # from cookie

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

1188 skey="`getpwfield $user skey`"

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

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

1191 return 0

1192 fi

1193 fi

1194 pswd=`getpar pswd`

1195 quser=`sqlquotestr "$user"`

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

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

1198 return 1

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

1200 wasureta "$user"

1201 return 1 # wasureta error

1202 fi

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

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

1205 if pwcheck "$pswd"; then

1206 newsession=`genrandom 50`

1207 dbsetbyid user "$user" skey "$newsession"

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

1209 gencookie "user=$user" "skey=$newsession"

1210 return 0

1211 fi

1212 return 2 # Password mismatch

1213 }

1214 showlogin() {

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

1216 s4name=${S4NAME:-s4}

1217 ( sed '/^<body/q' $layout/html.m4.html

1218 cat $layout/login.m4.html

1219 echo '</body></html>'

1220 ) | _m4 \

1221 -D_BODYCLASS_="login" \

1222 -D_TITLE_="$s4name" \

1223 -D_SYSNAME_="Welcome to $s4name" \

1224 -D_MYNAME_="$myname${args+?}$args" ${S4CSS:+-D_S4CSS_="$S4CSS"}

1225 exit 0

1226 }

1227 dologin() {

1228 checkauth

1229 st=$?

1230 if [ $st != 0 ]; then

1231 contenttype; echo

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

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

1234 showlogin # and EXIT

1235 fi

1236 }

1237

1238 # Do instant jobs here

1239 dbsetup

1240 trap cleanup INT HUP EXIT TERM PIPE

1241 # trap cleanup INT HUP

1242

1243 err() {

1244 echo "$@" 1>&3

1245 }

1246

1247 cgiinit() {

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

1249 tmpf=tmp/stream

1250 tmpd=`tmpd=$tmpdir mktempd`

1251 tmpfiles=$tmpfiles" $tmpd"

1252 addsession $session

1253 getcookie

1254 case "$REQUEST_METHOD" in

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

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

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

1258 # cat > $tmpf # too much?

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

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

1261 s="`cat tmp/stream`"

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

1263 ;;

1264 esac

1265 case "$CONTENT_TYPE" in

1266 *boundary*)

1267 bndry=${CONTENT_TYPE#*boundary=}

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

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

1270 do

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

1272 #echo u=$us

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

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

1275 # err k=$k v=$v

1276 case "$k" in

1277 *:filename)

1278 mimetype=`file --mime-type - < "$tmpd/$v"|cut -d' ' -f2`

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

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

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

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

1283 :

1284 fi

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

1286 case "$mimetype" in

1287 [Ii]mage/x-xcf)

1288 bzip2 "$tmpd/$v"

1289 v=${v}.bz2

1290 ;;

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

1292 [Ii]mage/*)

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

1294 ;;

1295 esac

1296 if ! echo "$mimetype" | egrep "$file_accept_egrep" >/dev/null 2>&1

1297 then

1298 replpar text string " *添付できない形式です($v)* $file_warn"

1299 continue

1300 elif [ `wc -c < "$tmpd/$v"` -gt "$filesize_max" ]; then

1301 replpar text string \

1302 " *添付ファイル($v)は${filesize_max}バイト以下にしてください* $file_warn"

1303 continue

1304 fi

1305 ;;

1306 *)

1307 type='string'

1308 ;;

1309 esac

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

1311 setpar "$k" "$type" "$v"

1312 done

1313 ;;

1314 *)

1315 setviastring par "$s"

1316 ;;

1317 esac

1318 }

1319 email4group() {

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

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

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

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

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

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

1326 and s.key='email'

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

1328 query "$sql"

1329 }

1330 email4groupbyuid() {

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

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

1333 uids=`echo "$@"`

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

1335 sql="WITH

1336 grpemails AS (

1337 SELECT gname, user, val email

1338 FROM grp_mem NATURAL JOIN grp_mem_s

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

1340 useremails AS (

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

1342 FROM user

1343 LEFT JOIN user_m

1344 ON user.name=user_m.name AND user_m.key='email')

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

1346 FROM useremails u LEFT JOIN grpemails g

1347 ON u.name=g.user

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

1349 ## err email4gByid `echo $sql`

1350 query "$sql"

1351 }

1352 collectmembersbyid() {

1353 # Collect user names of group specified by grid

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

1355 query "SELECT user FROM grp_mem \

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

1357 }

1358 collectmembersbyid() {

1359 # Collect user names of group name

1360 qgrp=`sqlquote "$1"`

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

1362 }

1363 collectgecosesbyid() {

1364 # Collect user gecoses of group

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

1366 query<<-EOF

1367 SELECT gecos

1368 FROM gecoses

1369 WHERE name IN (SELECT user FROM grp_mem

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

1371 EOF

1372 }

1373 collectemail() (

1374 # Collect email addresses for group $1

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

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

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

1378 for e; do

1379 if isuser "$e"; then

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

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

1382 else

1383 qgrp=`sqlquote "$e"`

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

1385 gmem="grp_mem"

1386 else

1387 tm=`sqlquote "$TEAM"`

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

1389 fi

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

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

1392 $gmem g left join grp_mem_s s

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

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

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

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

1397 query "$sql"

1398 fi

1399 done

1400 )

1401 sendinvitation() (

1402 # $1=email

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

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

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

1406 gecos=`gecos`

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

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

1409 _m4 -D_URL_="$urlbase" \

1410 -D_USER_="$name" \

1411 -D_EMAIL_="$1" \

1412 -D_REGIST_="$regist" \

1413 -D_ADMIN_="$admin" \

1414 $msgdir/mail-invite.m4 \

1415 | smail $1 "SNSへの御招待"

1416 return 0

1417 )

1418 emaildomaincheck() {

1419 case "$1" in

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

1421 *@*)

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

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

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

1425 return 2

1426 fi

1427 return 0

1428 ;;

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

1430 esac

1431 }

1432 invite() {

1433 email=`getpar email`

1434 case "$email" in

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

1436 *@*)

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

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

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

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

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

1442 elif sendinvitation $email; then

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

1444 fi ;;

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

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

1447 esac

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

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

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

1451 fi

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

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

1454 $layout/html.m4.html $layout/invite.m4.html

1455 }

1456 regist() {

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

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

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

1460 echo "bye bye" | html p

1461 reutrn

1462 fi

1463 email=`session=$1 getpar invite`

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

1465 cat<<EOF

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

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

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

1469 EOF

1470 return

1471 fi

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

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

1474 # Fake login password to wasureta

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

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

1477 wasureta $email

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

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

1480 addsession $1 # for removal after 1 minute

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

1482 $layout/login.m4.html

1483 return

1484 }

1485 group_safename() {

1486 # Convert $1 to safe group name

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

1488 }

1489 groupupdate() {

1490 gname=`getpar gname`

1491 qgname=`sqlquote "$gname"`

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

1493 # See ALSO same job in showgroup()

1494 newgname=`group_safename "$gname"`

1495 err newgname=$newgname

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

1497 err NewGNAME: gname=$newgname

1498 gname=$newgname

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

1500 replpar gname string "$gname"

1501 fi

1502 # Name confliction check

1503 parow=`getpar rowid`

1504 ## err parow=$parow

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

1506 query "BEGIN EXCLUSIVE;"

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

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

1509 if [ $count -gt 0 ]; then

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

1511 query "END;"

1512 return

1513 fi

1514 par2table $formdir/grp.def

1515 query "END TRANSACTION;"

1516 # Remove orphan

1517 : <<EOF

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

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

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

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

1522 EOF

1523 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1527 sql="delete from blog where id in

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

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

1530 query "$sql";

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

1532 return # and return

1533 fi

1534 fi

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

1536 fi

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

1538 grid=$(query $sql)

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

1540 grp $grid

1541 }

1542 groupclone() {

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

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

1545 case "$1" in

1546 */noteam)

1547 noteam="AND key != 'team'" ;;

1548 esac

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

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

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

1552 return

1553 fi

1554 i=0

1555 while true; do

1556 copy="-copy$i"

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

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

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

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

1561 i=$((i++))

1562 continue

1563 fi

1564 break

1565 done

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

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

1568 query<<-EOF

1569 BEGIN;

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

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

1572 SELECT $newqname, key, val

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

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

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

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

1577 -- Copy members and their configuration --

1578 REPLACE INTO grp_mem SELECT $newqname, user

1579 FROM grp_mem WHERE gname=$qgrp;

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

1581 FROM grp_mem_s WHERE gname=$qgrp;

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

1583 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1584 -- Copy administrators --

1585 REPLACE INTO grp_adm SELECT $newqname, user

1586 FROM grp_adm WHERE gname=$qgrp;

1587 COMMIT;

1588 EOF

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

1590 STOPCLONEMSG=1 groupconf "$newrowid"

1591 }

1592 groupman() {

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

1594

1595 GF_STAGE="grpconf"

1596 GF_STAGE=groupupdate

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

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

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

1600 -D_DUMPTABLE_="syscmd(cat)" \

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

1602 }

1603 userconf() {

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

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

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

1607 }

1608 groupconf() {

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

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

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

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

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

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

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

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

1617 html div 'class="fold"' <<-EOF

1618 `cgi_checkbox clone yes id="clone"`<label

1619 for="clone">同一メンバーで別グループを作成する</label>

1620 <div>

1621 <p>構成メンバーが同じ新規グループを作成します。</p>

1622 <table>

1623 <tr><td><a href="?groupclone+$rowid">

1624 <button>クローン作成(チームも複製)</button></a></td>

1625 <td><p>(チームなどもそのままで掲示板なしの状態から)</p></td></tr>

1626 <tr><td><a href="?groupclone+$rowid/noteam">

1627 <button>作成(チームなし)</button></a></td>

1628 <td>(チームは引き継がずメンバーのみ同じグループを作成)</td></tr>

1629 </table>

1630 <p>ボタンを押すと即作成します。不要な場合はグループ編集で

1631 削除してください。</p>

1632 </div>

1633 EOF

1634 fi

1635 }

1636 mems() {

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

1638 kwd=`getpar kwd`

1639 listmember $kwd

1640 }

1641 grps() {

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

1643 kwd=`getpar kwd`

1644 listgroup $kwd \

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

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

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

1648 $layout/form+dump.m4.html

1649 }

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

1651 gpg=`getpar grp`

1652 grid=${1:-$gpg}

1653 grp=`getgroupbyid "$grid"`

1654 ## . ./s4-blog.sh

1655 jg=`getpar joingrp`

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

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

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

1659 fi

1660 htmlheader=$layout/html.m4.html

1661 showgroup "$grid"

1662 }

1663 sql4interestblogs() {

1664 cat<<EOF

1665 CREATE TEMPORARY VIEW interestblogs AS

1666 SELECT blog.rowid rid, id, author

1667 FROM blog

1668 NATURAL JOIN

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

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

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

1672 WHEN (SELECT user FROM grp_mem

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

1674 THEN 0

1675 ELSE 1

1676 END;

1677 EOF

1678 }

1679 listnewblogsql() { # $1=user

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

1681 WHERE user='$user'

1682 AND tblrowid IN

1683 ($blogreadflagrowid,

1684 $blogcutoffflagrowid)),

1685 "0");"`

1686 cat<<EOF

1687 `sql4interestblogs`

1688 WITH article_ctime as (

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

1690 FROM article join article_s s using(id)

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

1692 GROUP BY id

1693 ), blog_title_owner as (

1694 SELECT blg.rid brid, id,

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

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

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

1698 ), blogall as (

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

1700 ), news as (

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

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

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

1704 FROM blogall bl

1705 LEFT JOIN

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

1707 ON bl.brid=al.tblrowid

1708 WHERE atime < bl.ctime

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

1710 LIMIT 10

1711 ) SELECT brid LINK, "新着",

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

1713 ctime, title,

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

1715 FROM news;

1716 EOF

1717 }

1718

1719 search_form() {

1720 # $1 = { author=<AUTHOR> | grp=<GROUP> }

1721 # $2(optional) = pre-input keywords

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

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

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

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

1726 @week → 最近一週間

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

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

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

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

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

1732 auth=""

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

1734 case "$1" in

1735 author=*)

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

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

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

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

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

1741 ;;

1742 grp=*)

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

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

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

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

1747 ;;

1748 esac

1749 inikwd="$2" # no need to htmlescape

1750 cat<<-EOF

1751 <div class="right">

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

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

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

1755

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

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

1758

1759 </form>

1760 </div>

1761 EOF

1762 }

1763

1764 imgsrc_cache() (

1765 # $1 = directory for cache'ing

1766 # $2 = table (user_m or grp_m)

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

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

1769 dir="$1" tbl="$2"

1770 keycond="$3"

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

1772 ORDER BY rowid DESC LIMIT 1"

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

1774 tmpf=$tmpd/imgsrc_cache.$$

1775 case "$4" in

1776 [Ss]) size=S ;;

1777 [Oo]) size=O ;;

1778 *) size=M ;;

1779 esac

1780 # ImageCache filename storing schema:

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

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

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

1784 valtype=`query "$sql0"`

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

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

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

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

1789 case "$filetype" in

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

1791 esac

1792 fi

1793 cacheimg_S=$dir/S_$filename

1794 cacheimg_M=$dir/M_$filename

1795 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1803 else

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

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

1806 fifo2=$fifo.2

1807 mkfifo $fifo $fifo2

1808 fmt=${filename##*.}

1809 ## [[ NOTE ]]

1810 ## a. convert oldimage newimage

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

1812 ## b is much smaller than a

1813 cat $tmpf | unhexize \

1814 | tee $fifo \

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

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

1817 | tee $fifo2 \

1818 | convert - "$cacheimg_M" &

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

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

1821 | convert - "$cacheimg_S" &

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

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

1824 echo '">'

1825 echo "$sum" > $sumfile

1826 fi

1827 ## Now preparing cache image, done.

1828 ## Store this information to DB

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

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

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

1832 query <<-EOF

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

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

1835 EOF

1836 )

1837

1838 showhome() {

1839 # $1=userRowIdToShow

1840 err showhome \$1=$1

1841 case "$1" in

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

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

1844 esac

1845 ## err ShowHome: uname=$uname

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

1847 gecos=`gecos "$uname"`

1848 ## err SH:gecos=$gecos

1849 GF_VIEWONLY=1

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

1851 search_form_args=""

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

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

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

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

1856 # Display folders

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

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

1859 and type like 'file:%';"

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

1861 nfile=`query "$sql"`

1862 # err nfile=$nfile

1863 if [ $nfile -gt 0 ]; then

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

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

1866 fi

1867 else

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

1869 GROUP BY user;"`

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

1871 search_form_args="author=$uname"

1872 fi

1873 . ./s4-blog.sh

1874

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

1876 search_form "$search_form_args" > $sf

1877 printf "%s さん" "$gecos" > $tf

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

1879 viewtable $formdir/user.def user $1

1880 echo "</div>"

1881 } > $pf

1882

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

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

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

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

1887 if true; then

1888 tbl=user_m

1889 enticond="name='$uname'"

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

1891 else

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

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

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

1895 echo '">'

1896 }

1897 fi > $imf

1898 fi

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

1900 val='$uname';"`

1901 ## REMOVE This comment block until 2019/7/1

1902 ## err "----- `gdate +%FT%T.%3N` ------------C"

1903 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

1904 listblog $uname > $bf

1905 ## unset ddd

1906 ## err "----- `gdate +%FT%T.%3N` ------------D"

1907

1908 hometail=$tmpd/tail.$$

1909 mkfifo $hometail

1910

1911 #Calling listgroupbytable, originally here

1912

1913 (

1914 # Display Most Recent Entry

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

1916 shortval=${shortval:-val}

1917

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

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

1920 ctime,

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

1922 (SELECT gecos FROM gecoses

1923 WHERE name=(SELECT val FROM blog_s

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

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

1926 FROM blog b

1927 JOIN

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

1929 FROM article a, article_s s

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

1931 GROUP BY blogid ORDER BY val DESC LIMIT 50

1932 ) m

1933 ON b.id=m.blogid;"

1934 # This should be as follows

1935 : <<EOF

1936 WITH arts AS(

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

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

1939 FROM article a NATURAL JOIN article_s s

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

1941 GROUP by s.id

1942 )

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

1944 FROM arts a0

1945 JOIN

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

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

1948 ORDER BY ctime DESC LIMIT 50;

1949 EOF

1950

1951 cat<<-EOF

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

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

1954 <div class="lcto">

1955 `DT_VIEW=replyblog dumptable html blog`

1956 </div>

1957 EOF

1958 unset DT_SQL

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

1960 # Display NEWS

1961 # 2016-06-26

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

1963 acclog blog $blogreadflagrowid

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

1965 fi

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

1967 sql=`listnewblogsql "$user"`

1968 # echo "$sql" > tmp/listnew

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

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

1971 cont=$((cont-1))

1972 err newcount=$cont

1973 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

1980 `cgi_submit '確定'`

1981 `cgi_hidden read yes`

1982 EOF

1983 echo "$new10 "

1984 echo "</div>"

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

1986 acclog blog $blogcutoffflagrowid # for speed

1987 fi

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

1989 : # DT_SQL=

1990 fi

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

1992 #

1993 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

1999 -D_NBLOG_="$nblog" \

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

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

2002 $layout/html.m4.html $layout/home.m4.html

2003

2004 # Record access log

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

2006 }

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

2008 contenttype; echo

2009 ## err commission: "$@"

2010 gname=`getgroupbyid $1`

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

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

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

2014 grp_reg_adm "$@"

2015 else

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

2017 fi

2018 }

2019 listgroupbytable() {

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

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

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

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

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

2025 NGsql="select distinct tag from\

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

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

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

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

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

2031 for tag in `query "$sql"`

2032 do

2033 ## err ListGrp: tag=$tag

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

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

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

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

2038 as rowid,

2039 gname,

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

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

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

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

2044 ## err PersonalGroupList= `echo $sql`

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

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

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

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

2049 echo '</table>'

2050 done

2051 echo '</div>'

2052 }

2053 iconhref() (

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

2055 data=`percenthex "$1"`

2056 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2057 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

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

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

2060 )

2061 iconhref2() (

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

2063 src=$1

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

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

2066 )

2067 listentry() (

2068 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

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

2070 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2071 cond='' hiddens=''

2072 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2073 if [ -z "$offset" ]; then

2074 offset=`getpar start`; offset=${offset%%[!0-9]*}

2075 offset=$((offset-1))

2076 fi

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

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

2079 limit=30

2080 dir=`getcachedir "$1"`

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

2082 hrb="$myname?home"

2083 deficon=person-default.png

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

2085 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2086 gcs=gecos

2087 else # if group

2088 hrb="$myname?grp"

2089 deficon=group-default.png

2090 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2091 gcs=name

2092 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2093 if [ -n "$tagline" ]; then

2094 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2095 ## err tagconv=$tagconv

2096 fi

2097 fi

2098 if [ ! -d $dir ]; then

2099 mkdir -p $dir

2100 fi

2101 if [ ! -s $dir/$deficon ]; then

2102 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2103 fi

2104 if [ -n "$2" ]; then

2105 cond="where nick like '%$2%' or b.name like '%$2%'"

2106 fi

2107

2108 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2109 # grpは呼出し元の動的スコープ変数でよくないな...

2110 ##qgrp=`sqlquote $grp`

2111 getgrp="(select gname from grp where rowid=${rowid:--1})"

2112 sql="select a.rowid, a.$link,

2113 coalesce(b.$gcs, a.$nm) as nick,

2114 quote(a.$nm) as qname,

2115 (SELECT val FROM ${tbl}_s

2116 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2117 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2118 || case when a.$nm in (select gname from grp)

2119 then printf('(%d名)',

2120 (select count(user) from grp_mem where gname=a.$nm))

2121 else ' <'||a.$nm||'>'

2122 end

2123 as name,

2124 b.tag,

2125 case when a.$nm in (select user from grp_adm

2126 where gname=$getgrp) then '(管理者)'

2127 when '$user' in (select user from grp_adm where gname=a.$nm)

2128 then '(ADMIN)'

2129 when '$user' in (select user from grp_mem where gname=a.$nm)

2130 then '(Member)'

2131 when '$iamowner' = '' then ''

2132 else ',not='||a.rowid end as ownerlink,

2133 CASE '$entity'

2134 WHEN 'グループ'

2135 THEN coalesce(

2136 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2137 'open')

2138 ||

2139 CASE WHEN '$user'

2140 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2141 THEN ' ismember'

2142 ELSE ''

2143 END

2144 ELSE 'user'

2145 END regmode

2146 from $tbl a left join

2147 (select $nm as name,

2148 max(case key when 'gecos' then val end) as gecos,

2149 max(case key when 'tag' then val end) as tag,

2150 max(case key when 'mtime' then val end) as mtime,

2151 max(case key when 'wtime' then val end) as wtime,

2152 max(case key when 'login' then val end) as login

2153 from ${tbl}_s group by $nm)

2154 b on a.$nm=b.name $cond $3

2155 order by b.wtime desc, b.login desc,

2156 b.mtime desc, b.tag desc, a.rowid asc"

2157 # Give precedence to newer maintained groups (2016-09-24)

2158 # Note that mtime is stored only in grp_s.

2159 ## err LE:sql.1="$sql"

2160 total=`query "with x as ($sql) select count(*) from x;"`

2161 echo "${entity} 一覧" | html h2

2162 if [ $total -gt $limit ]; then

2163 echo '<div class="right">'

2164 cgi_form $stage <<EOF

2165 <label>次の語を含む${entity}で検索:

2166 `cgi_text kwd $kwd`</label>

2167 EOF

2168 echo '</div>'

2169 fi

2170 hiddens="$hiddens

2171 `cgi_hidden kwd \"$kwd\"`

2172 `cgi_hidden stage \"$stage\"`"

2173 cat<<EOF

2174 <form action="$myname" method="POST">

2175 <p>${total}件中の<input class="hidesub" type="text" name="start"

2176 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2177 <input type="submit" value="確定"></p>

2178 </form>

2179 EOF

2180 if [ $((offset+limit)) -lt $total ]; then

2181 nextbtn=$(

2182 cat<<EOF

2183 <div class="right clear"><form action="$myname" method="POST">

2184 `cgi_submit 次の${limit}件`

2185 $hiddens

2186 `cgi_hidden offset $((offset + limit))`</form></div>

2187 EOF

2188 )

2189 fi

2190 if [ $offset -gt 0 ]; then

2191 prevbtn=$(

2192 cat<<EOF

2193 <form action="$myname" method="POST">

2194 `cgi_submit 前の${limit}件`

2195 $hiddens

2196 `cgi_hidden offset $((offset - limit))`</form>

2197 EOF

2198 )

2199 fi

2200 pnbtn="$nextbtn$prevbtn"

2201 echo $pnbtn

2202

2203 ## err ListEntry: `echo "$sql"\;`

2204 # sq $db here??? 2016-11-28

2205 query "$sql limit $limit ${offset:+offset $offset};" \

2206 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2207 err name=$name owner=$ownerp lnk=$lnk

2208 err newlnk=$lnk regmode=$regmode

2209 icondir=$dir/$id

2210 # Pick up only last icon

2211 echo "<div class=\"iconlist xy$thumbxy $type\">

2212 <p class=\"tag _$tag\">$tag</p>" \

2213 | _m4 $tagconv

2214 if [ -n "$NOSPEEDUP" ]; then

2215 files=`getvalbyid $tbl profimg $id $icondir`

2216 if [ -n "$files" ]; then

2217 icon=`echo "$files"|tail -1`

2218 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2219 else

2220 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2221 fi

2222 elif [ -n "$icon" -a -s "$icon" ]; then

2223 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2224 else

2225 cond="$nm=$qname"

2226 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2227 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2228 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2229 # err "img=[$img]"

2230 if [ -n "$img" ]; then

2231 echo "<a href=\"$hrb+$lnk\">"

2232 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2233 echo "</a>"

2234 else

2235 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2236 fi

2237 fi

2238 echo "<br>$name${ownerp:+<br>$ownerp}"

2239 echo "</div>"

2240 done

2241 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2242 )

2243 listmember() {

2244 listentry user "$@"

2245 }

2246 listgroup() {

2247 listentry group "$@"

2248 }

2249 hexteams() { # $1=gname, $2(optional)=user

2250 cond=${2:+" AND user='$2'"}

2251 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2252 WHERE gname='$1' AND key='team'$cond;"

2253 }

2254 showgroup() { # $1=group-rowid

2255 if [ -z "$1" ]; then

2256 grid=`getpar grid`

2257 grid=${grid%%[!0-9]*}

2258 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2259 else

2260 grid=$1

2261 fi

2262 grp=`getgroupbyid $grid`

2263 qgrp=`sqlquote "$grp"`

2264 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2265 if isgroup "$grp"; then

2266 tf=$tmpd/title.$$

2267 sf=$tmpd/search.$$

2268 bodyclass=`query "SELECT val FROM grp_s

2269 WHERE gname=$qgrp AND key='regmode';"`

2270 if ismember "$user" "$grp"; then

2271 ismember="ismember"

2272 qgrp=`sqlquote "$grp"`

2273 bodyclass="$bodyclass${bodyclass:+ }ismember"

2274 else

2275 ismember="" # bodyclass="group"

2276 fi

2277 bodyclass="$bodyclass grouphome"

2278 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2279 echo "グループ $grp" > $tf

2280

2281 showgroupsub $formdir/grp.def "$grid" | \

2282 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2283 -D_FORM_="syscmd(\`cat')" \

2284 -D_BODYCLASS_="$bodyclass" \

2285 -D_DUMPTABLE_="" \

2286 $htmlheader $sf $layout/form+dump.m4.html

2287 # $htmlheader $layout/form+dump.m4.html

2288 # $htmlheader is defined in grp()

2289 else # if $grp is removed at par2table

2290 listgroup

2291 fi

2292 }

2293 showgroupsub() {

2294 # $1=def-file $2=group-rowid

2295 # Using $ismember

2296 rowid=$2

2297 grp=`getgroupbyid $2`

2298 qgrp=`sqlquote "$grp"`

2299 td=`getcachedir grp/"$2"`

2300 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2301 if [ -z "$rowid" ]; then

2302 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2303 #grp=`sq $db "select gname from grp where rowid=$grp"`

2304 echo "showgroupsub: invalid argument($1 $2)" | html p

2305 return

2306 fi

2307 val=`getvalbyid grp profimg $rowid $tmpd`

2308 enticond="gname=$qgrp"

2309 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2310 if [ -n "$img" ]; then

2311 cat<<-EOF

2312 <p class="groupimg">

2313 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2314 EOF

2315 fi

2316 echo "<div class=\"noprofimg\">"

2317 viewtable $1 grp $rowid

2318 echo "</div>"

2319 if isgrpowner "$user" "$grp"; then

2320 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2321 iamowner=$rowid

2322 colmd=" mode"

2323 fi

2324 if [ -n "$ismember" ]; then

2325 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2326 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2327 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2328 cat<<EOF

2329 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2330 <div class="fold clear">

2331 `cgi_checkbox send yes id="send"`<label

2332 for="send">グループ全員にメッセージ送信</label>

2333 <div>

2334 `cgi_textarea message "" "cols=60"`

2335 `cgi_submit 送信`

2336 `cgi_reset リセット`

2337 </div>

2338 `cgi_hidden grp $rowid`

2339 </div></form>

2340 EOF

2341 fi

2342 # 加入ボタン + 加入者リスト

2343 if [ -n "$ismember" ]; then

2344 ismem='checked' state="(参加中)"

2345 else

2346 nomem='checked' state="(現在非加入)"

2347 fi

2348 # このグループでの加入アドレス

2349 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2350 and key='email';"`

2351 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2352 ## and key='email';"

2353 ##err email=$eml

2354 cat <<EOF

2355 <div class="fold clear">

2356 `cgi_checkbox reg yes id="reg"`<label

2357 for="reg">自身の加入状態を操作する</label>$state

2358 <div>

2359 EOF

2360 cgi_form grp <<EOF

2361 <p>このグループに</p>

2362 <table class="b">

2363 <tr><th>メンバーとして</th><td>

2364 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2365 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2366 <tr><th>参加する場合のメイルアドレス<br>

2367 <small>(メインのアドレスとは違うものにする場合に記入<br>

2368 同じでよい場合は空欄に)</small></th>

2369 <td>`cgi_text email $eml`</td></tr>

2370 </table>

2371 `cgi_hidden grp $rowid`

2372 EOF

2373 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2374 echo "moderated (承認加入の)グループなので実際に参加できるのは

2375 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2376 fi

2377 echo '</div></div>'

2378 echo '<h2>話題一覧</h2>'

2379 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2380 cat<<-EOF

2381 <form class="summary" action="$myname" title="$thelp">

2382 `cgi_hidden owner "$grp"`

2383 `cgi_hidden kwd "@week"`

2384 `cgi_hidden stage searchart`

2385 `cgi_submit "一週間のまとめ"`

2386 </form>

2387 EOF

2388 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2389 colstate="state:稼動状態:frozen=rowclass=凍結"

2390 DT_CHLD=article:blogid \

2391 DT_QOWNER="$qgrp" \

2392 DT_VIEW=replyblog dumptable html blog \

2393 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2394

2395 getgname="(select gname from grp where rowid=$rowid)"

2396 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2397 cm="?commission+$rowid"

2398 thumbxy=50x50 listmember "" "$c" "$rowid" \

2399 |sed -e "s|$<br>$,not=$.*$|\1|" # 間違って押しやすい

2400 # team list

2401 hexteams=`hexteams "$grp"`

2402 if [ -n "$hexteams" ]; then

2403 echo "チーム一覧" | html h2

2404 echo '<div class="dumptable"><table class="b">'

2405 sq $db -html -header<<-EOF

2406 SELECT val TEAM,

2407 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2408 MEMBERS

2409 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2410 EOF

2411 echo '</table></div>'

2412 fi

2413 }

2414 grp_getbodyclass() {

2415 # Get css class name for document.

2416 # `moderated' for moderated groups

2417 # `ismember' for groups where user belongs

2418 # $1=GroupName (w/o quote)

2419 # $user=userNameCurrentlyLogin

2420 ## err grp_getbodyclass: 1="$1"

2421 qgrp=`sqlquote "$1"`

2422 query<<-EOF

2423 SELECT coalesce(

2424 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2425 'open')

2426 ||

2427 CASE WHEN '$user'

2428 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2429 THEN ' ismember'

2430 ELSE ''

2431 END;

2432 EOF

2433 }

2434 grpaction() { # $1=group-rowid

2435 err GRP_ACTION:IN

2436 grid=${1:-`getpar grp`}

2437 grp=`getgroupbyid "$grid"`

2438 if [ -z "$grp" ]; then

2439 echo "無効な指定です。" | html p; return

2440 fi

2441 if ! ismember $user "$grp"; then

2442 echo "加入者のみに許可された操作です。" | html p; return

2443 fi

2444 echo "グループ $grp 個別選択操作" \

2445 | _m4 -D_TITLE_="syscmd(\`cat')" \

2446 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2447 $layout/html.m4.html

2448

2449 isowner=""

2450 isgrpowner "$user" "$grp" && isowner="yes"

2451 usel=`getpar usel`

2452 if [ -n "$usel" ]; then

2453 uids=$(echo `echo $usel`|tr ' ' ',')

2454 ## err grpaction-1: grp=$grp, `echo $sql`

2455 text=`getpar text`

2456

2457 rm=`getpar rm` cfm=`getpar confirm`

2458 ## err rm=$rm cfm=$cfm

2459 if [ x"$rm" = x"yes" ]; then

2460 if [ "$isowner" ]; then

2461 if [ x"$rm$cfm" = x"yesyes" ]; then

2462 # Eliminate

2463 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2464 for tbl in grp_mem grp_mem_s grp_mem_m; do

2465 sql="delete from $tbl $cond;"

2466 # echo "sql=$sql"

2467 query "$sql"

2468 err rmGRPuser "$sql"

2469 done

2470 num=`query "select count(*) from user where rowid in ($uids);"`

2471 #err num=$num

2472 if [ 0$num -gt 0 ]; then

2473 sql="select coalesce(b.val,a.name) from user a left join \

2474 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2475 # err `echo "$sql"`

2476 html pre<<EOF

2477 以下の${num}名のグループ $grp 登録を解除しました。

2478 `query "$sql"`

2479 EOF

2480 fi

2481 else

2482 echo "確認のチェックがないのでやめておきます。" | html p

2483 return

2484 fi

2485 else # not Group Owner

2486 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2487 return

2488 fi

2489 cat<<EOF

2490

2491 EOF

2492 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2493 if [ -z "$text" ]; then # if msg is empty

2494 echo "なにかメッセージを..." | html p

2495 return 0

2496 fi

2497 gecos=`gecos $user`

2498 mkfrom=`getpar mkfrom`

2499 if [ x"$mkfrom" = x"yes" ]; then

2500 safegc=`echo "$gecos" | tr -d '<>@'`

2501 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2502 fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2503 mail_from="$safegc <$fromad>"

2504 else

2505 mail_from="$admin"

2506 fi

2507 MAIL_FROM=$mail_from \

2508 smail "`email4groupbyuid "$grp" $usel` $user" \

2509 "$gecos さんからのメッセージ" <<EOF

2510 $url

2511 のグループ「$grp」のメンバーである $gecos さんから、

2512 あなた宛へのメッセージです。

2513 ----------------------------------------------------------

2514 $text

2515 EOF

2516 if [ $? = 0 ]; then

2517 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2518 sql="select coalesce(b.val, a.name) from

2519 (select name from user where rowid in ($uids)) a

2520 left join user_s b on a.name=b.name and b.key='gecos';"

2521 html pre<<EOF

2522 `query "$sql"`

2523 (送信者である $gecos さんも含まれます)

2524 EOF

2525 err SendDone: `echo $sql`

2526 fi

2527 elif [ x"$rm" = x"commission" ]; then

2528 grp_reg_adm $grid $usel

2529 elif [ x"$rm" = x"addteam" ]; then

2530 team=`getpar team|sed "s/'/''/g"` # for single quotation

2531 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2532 if [ x"$team" != x"$newteam" ]; then

2533 echo "チーム名に使えない文字を除去しました" | html p

2534 team=newteam

2535 fi

2536 if [ -z "$team" -o x"$team" = x"なし" ]; then

2537 cat<<-EOF | html p

2538 有効なチーム名を入力してください。

2539 カンマだけ、「なし」という名前は使えません。

2540 EOF

2541 echo "有効なチーム名を入力してください。" | html p

2542 else

2543 grp_add_team $grid "$team" $usel

2544 fi

2545 elif [ x"$rm" = x"rmteam" ]; then

2546 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2547 rmteam=`getpar rmteam|sed "s/'/''/g"`

2548 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2549 gname='$grp' AND user='$user' AND key='team'\

2550 AND val='$rmteam';\"`" ]; then

2551 grp_rm_team $grid "$rmteam" $usel

2552 else

2553 echo "所属していないチームの除去操作はできません。"|html p

2554 fi

2555 else

2556 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2557 fi

2558 fi

2559 fi

2560 # POST count summary

2561 from=`getpar from`; to=`getpar to`

2562 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2563 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2564 fromtonote="<p>POST集計: $from_input - $to_input</p>"

2565 # New entry

2566 sql="WITH mems AS (

2567 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2568 ON gm.user=g.name

2569 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2570 ), target_article AS (

2571 SELECT id FROM article_s

2572 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2573 ), posts AS (

2574 SELECT author, count(author) post

2575 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2576 WHERE blogid IN (SELECT id FROM blog_s

2577 WHERE key='owner'

2578 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2579 AND key='text'

2580 GROUP BY author

2581 ), teams AS (

2582 SELECT user, group_concat(val, ', ') team

2583 FROM grp_mem_m

2584 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2585 AND key='team'

2586 GROUP BY user

2587 ), user_post AS (

2588 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2589 FROM mems m LEFT JOIN posts

2590 ON m.name=posts.author

2591 GROUP by m.rowid

2592 )

2593 SELECT

2594 CASE

2595 WHEN (SELECT user FROM grp_adm

2596 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2597 AND user=up.name) IS NOT NULL

2598 then 'k'

2599 ELSE ''

2600 END || rowid || ',' || gecos NAME,

2601 post POST, team TEAM

2602 FROM user_post up LEFT JOIN teams t

2603 ON up.name=t.user

2604 ORDER BY gecos;"

2605 ## err grpaction: "`echo \"$sql\"`"

2606 tf=$tmpd/title.$$

2607 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2608 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2609 for=\"cmadmin\">グループ管理者委任</label>

2610 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2611 </p></div>"

2612 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2613 for=\"conf\">グループ登録解除</label>

2614 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2615 <p>この操作による通知は本人に行きません。

2616 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2617 </div>"

2618 # Get team list to which current user belongs into $hexteams

2619 myhexteams=$(hexteams "$grp" "$user")

2620 allhexteams=$(hexteams "$grp")

2621 if [ -n "$myhexteams" ]; then

2622 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2623 for=\"cmrmteam\">チーム属性除去</label>

2624 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2625 を除去します: `cgi_checkbox teamconfirm yes` 確認

2626 <p>この操作による通知は本人に行きません。

2627 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2628 </div>

2629 "

2630 fi

2631 b1='<label> <input type="checkbox" name="usel" value="'

2632 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2633 #b2='"> <span>' b3='</span></label>'

2634 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2635 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2636 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2637 cgi_form grpaction<<EOF \

2638 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2639 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2640 | _m4 -D_TITLE_="spaste(\`$tf')" \

2641 -D_SUBTITLE_="チェック後操作ボタン" \

2642 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2643 $layout/form+dump.m4.html

2644 <p>下でチェックした人を対象として:</p>

2645 <div class="foldtabs">

2646 `cgi_radio rm addteam 'id="cmteam"'`<label

2647 for="cmteam">同じチーム属性を付与</label>

2648 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2649 `cgi_datalist_h teams $allhexteams`

2650 </div>

2651 ${rmteammsg}

2652 `cgi_radio rm send id="sendmsg"`<label

2653 for="sendmsg">メッセージ送信</label>

2654 <div>

2655 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2656 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2657 `cgi_textarea text "" cols=40`

2658 </div>

2659 ${isowner:+$cmmsg$excmsg}

2660 `cgi_radio rm close id="x"`<label for="x">×</label>

2661 </div>

2662 <h4>$grp 参加者一覧</h4>$fromtonote

2663 <table class="td2r">

2664 `sq $db -header -html "$sql"`

2665 </table>

2666 `cgi_hidden grp $grid`

2667 EOF

2668 }

2669 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2670 # Create TEMPORARY VIEW

2671 query<<EOF

2672 CREATE TEMPORARY VIEW writeusers AS

2673 SELECT DISTINCT author FROM article

2674 WHERE id in (

2675 select id from article where blogid=(select id from blog where rowid=$1)

2676 );

2677 CREATE TEMPORARY VIEW movablegroups AS

2678 SELECT g.rowid growid , g.gname

2679 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2680 ON grp.gname=gm.gname -- そのユーザが属している

2681 AND user='$user') g -- グループに絞る

2682 WHERE (SELECT author FROM writeusers

2683 EXCEPT

2684 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2685 IS NULL;

2686 $2

2687 EOF

2688 }

2689 sql4readableblogs() {

2690 # Create view of blogs that can be readable to $user

2691 # Blog is readable when:

2692 # 1: blog owner is an user

2693 # 2: else, 2.1: owner-group where the $user belongs

2694 # 2.2: else, owner-group is not moderated

2695 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2696 cat<<EOF ## | tee tmp/sql.out

2697 CREATE TEMPORARY VIEW readableblogs AS

2698 SELECT blog.rowid rid, id, author

2699 FROM blog

2700 NATURAL JOIN

2701 (SELECT id,

2702 max(CASE key WHEN 'owner' THEN val END) owner,

2703 max(CASE key WHEN 'mode' THEN val END) mode

2704 FROM blog_s GROUP by id) bs

2705 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2706 THEN 1 -- blog owner is an user, READABLE

2707 WHEN (SELECT val FROM grp_s

2708 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2709 AND

2710 (SELECT user FROM grp_mem

2711 WHERE gname=bs.owner AND user='$user') IS NULL

2712 THEN 0

2713 WHEN mode = 'quiz'

2714 THEN 0 -- "quiz" mode blog cannot be searched

2715 ELSE 1

2716 END;

2717 EOF

2718 }

2719 editheading() { # $1=rowid-of-heading

2720 rowid=${1%%[!A-Z0-9a-z_]*}

2721 if [ -z "$rowid" ]; then

2722 echo "話題番号が未指定です。" | html p

2723 return

2724 fi

2725 owner=`getvalbyid blog owner $rowid`

2726 title=`getvalbyid blog title $rowid`

2727 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2728 | _m4 -D_TITLE_="修正" \

2729 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2730 -D_BLOGS_="" -D_DUMPTABLE_="" \

2731 -D_FORM_="syscmd(\`cat')" \

2732 $layout/html.m4.html $layout/form+dump.m4.html

2733 # Move to group

2734 if isuser "$owner"; then

2735 crview4article $rowid

2736 n=`query "SELECT count(*) FROM writeusers;"`

2737 ## err N=$n

2738 if [ $((n)) -gt 0 ]; then

2739 ## err ROWID=$rowid

2740 sql="SELECT growid || ':' || gname FROM movablegroups;"

2741 cat<<-EOF

2742 <div class="fold">

2743 `cgi_checkbox mv send id="mv"`<label

2744 for="mv">この話題をグループ所有に移動する</label>

2745 <div>

2746 <form action="?mvart" method="POST" enctype="multipart/form-data">

2747 移動先グループ:

2748 <select name="mv2grp">

2749 EOF

2750 query ".mode html"

2751 query<<-EOF |

2752 $sql

2753 .mode list

2754 EOF

2755 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2756 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2757 cat<<-EOF

2758 </select>

2759 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2760 そのグループに加入しているものに限られます)</p>

2761 <p>`cgi_checkbox cfm yes`<label>確認

2762 (この操作は元に戻すことができません)</label></p>

2763 `cgi_hidden blogrowid $rowid`

2764 `cgi_submit 移動`

2765 `cgi_reset Reset`

2766 </form>

2767 </div>

2768 </div>

2769 EOF

2770 fi

2771 # end of isuser "$owner"

2772 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2773 [ -n "$hexteams" ];}; then

2774 none="`echo なし|hexize`"

2775 cat<<-EOF

2776 <div class="fold">

2777 `cgi_checkbox mv2team send id="mv2team"`<label

2778 for="mv2team">この話題を以下のチームのものにする</label>

2779 <div><p>現在の所属チーム設定:

2780 `query "SELECT

2781 coalesce((SELECT val FROM blog_s

2782 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2783 AND key='team'),

2784 ':なし');"`</p>

2785 <form action="?mvart" method="POST" enctype="multipart/form-data">

2786 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2787 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2788 `cgi_hidden blogrowid $rowid`<br>

2789 `cgi_submit 移動`

2790 `cgi_reset Reset`

2791 </form></div></div>

2792 EOF

2793 fi

2794 }

2795 mvart() { # move diary to some group or team

2796 # or move blog of group to team which belong to the group

2797 blogrowid=`getpar blogrowid`

2798 cfm=`getpar cfm`

2799 ##### echo move blog:$blogrowid to $mv2grp | html p

2800 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2801 . ./s4-blog.sh

2802 if [ -z "$blogrowid" ]; then

2803 echo "無効な指定です(mvart)。" | html p

2804 return

2805 elif [ x"$cfm" != x"yes" ]; then

2806 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2807 elif { mv2grp=`getpar mv2grp`

2808 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2809 [ -n "$mv2grp" ]; }; then

2810 crview4article $blogrowid

2811 ########## TRANSACTION BEGIN

2812 query "BEGIN;"

2813 n=`query "SELECT count(*) FROM writeusers;"`

2814 ## err Nwriteuser=$n

2815 if [ $((n)) -gt 0 ]; then

2816 query<<-EOF

2817 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2818 WHERE key='owner'

2819 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2820 AND $mv2grp IN (SELECT growid FROM movablegroups);

2821 EOF

2822 fi

2823 query "END;"

2824 ########## TRANSACTION END

2825 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2826 [ -n "$mv2team" ];}; then

2827 # blog owner can move it to ANY team

2828 case "$mv2team" in

2829 'なし')

2830 cat<<-EOF

2831 DELETE FROM blog_s

2832 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2833 AND key='team';

2834 EOF

2835 ;;

2836 "") ;;

2837 *)cat<<-EOF

2838 BEGIN;

2839 REPLACE INTO blog_s(id, key, val)

2840 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2841 'team', '$mv2team');

2842 REPLACE INTO blog_s(id, key, val)

2843 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2844 'notify', 'all'); -- Change notify to all

2845 END;

2846 EOF

2847 esac | query

2848 fi

2849 blog_reply $blogrowid

2850 echo yes | html p

2851 }

2852 editart() { # $1=article-rowid $2=blogrowid

2853 rowid=${1%%[!A-Z0-9a-z_]*}

2854 blogrowid=${2%%[!A-Z0-9a-z_]*}

2855 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2856 echo "表示する記事番号が未指定です。" | html p

2857 return

2858 fi

2859 owner=`getvalbyid blog owner $blogrowid`

2860 title=`getvalbyid blog title $blogrowid`

2861 author=`getvalbyid article author $rowid`

2862 ## err EDITart: owner=$owner, author=$author

2863 if isgrpowner "$user" "$owner"; then

2864 : EDIT OK

2865 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2866 echo "本人か所有者しか編集できません." | html p

2867 return

2868 fi

2869 aid=`query "select id from article where rowid=$rowid;"`

2870 tmpout=$tmpd/editart.$$.out

2871 GF_ACTION="?replyblog+$blogrowid#$aid" \

2872 edittable $formdir/article.def article $rowid \

2873 > $tmpout

2874 rm -f /tmp/editart.out

2875 # Cannot use pipelining to m4 with genform() because of stdin stack

2876 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2877 -D_FORM_="syscmd(cat $tmpout)" \

2878 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2879 -D_BLOGS_= -D_DUMPTABLE_= \

2880 $layout/html.m4.html $layout/form+dump.m4.html

2881 }

2882 send2mem() {

2883 rowid=`getpar grp`

2884 rowid=${rowid%%[!0-9]*} # Cleaning

2885 if [ -z "$rowid" ]; then

2886 echo "グループが未指定です。" | html p

2887 return

2888 fi

2889 message=`getpar message`

2890 if [ -z "$message" ]; then

2891 echo "文章を入れてください。" | html p

2892 return

2893 fi

2894 grp=`getgroupbyid $rowid`

2895 members=`collectemail $grp`

2896 # smail rcpt subj (file)

2897 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$admin>" \

2898 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2899 $urlbase?grp+$rowid

2900 グループ $grp に所属する

2901 `gecos $user` さんよりメッセージ:

2902

2903 $message

2904 EOF

2905 cat<<EOF

2906 <p>以下のユーザに送信しました。</p>

2907 <pre>

2908 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

2909 </pre>

2910 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2911 EOF

2912 }

2913 joingrpadmit() {

2914 # $1=yes/no $2=session-key

2915 if [ -z "$2" ]; then

2916 echo "bye bye" | html p; return

2917 fi

2918 t_usr=`session=$2 getpar user`

2919 t_grp=`session=$2 getpar group`

2920 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2921 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2922 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2923 echo "無効な加入依頼です。" | html p

2924 echo "有効期限が切れたか、

2925 他の管理者がいる場合は処理済みの可能性があります。" | html p

2926 return

2927 fi

2928 if ! isgrpowner "$user" "$t_grp"; then

2929 echo "グループ管理者のみの機能です。" | html p; return

2930 fi

2931 case $1 in

2932 yes) joingrp "$t_grp" "$t_usr" yes ;;

2933 no) joingrp "$t_grp" "$t_usr" no ;;

2934 *)

2935 echo "無効な指定です($1)。" | html p

2936 return ;;

2937 esac

2938 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

2939 rcpts="`getgroupadminmails $t_grp` $user"

2940 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2941 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2942 に

2943 $t_usr

2944 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2945 しました。"

2946 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2947 query "delete from session where id='$2';"

2948 echo "$body" | html p

2949 }

2950

2951 joingrprequest() {

2952 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2953 jss="joingrp-`date +%s`-`genrandom 12`"

2954 addsession $jss +${memoplimitdays}days

2955 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

2956 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

2957 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2958 $url

2959 $user さんから

2960 グループ $1

2961 に加入依頼がありました。

2962

2963 承認する:

2964 $urlbase?joingrpadmit+yes+$jss

2965

2966 白紙に戻す:

2967 $urlbase?joingrpadmit+no+$jss

2968 EOF

2969 echo "管理者に加入依頼を出しました。

2970 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2971 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2972 }

2973 joingrp() {

2974 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2975 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2976 if isgrpowner "$user" "$1"; then

2977 isowner="yes"

2978 elif [ -n "$5" ]; then

2979 isowner="yes"

2980 else

2981 isowner=""

2982 fi

2983 ## err jg:isgrpowner: isowner="$isowner"

2984 if [ -n "$isowner" ]; then

2985 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

2986 elif [ x"$2" != x"$user" ]; then # if user is not login user

2987 echo "本人か、グループ管理者しか加入操作はできません。" | html p

2988 return

2989 elif [ x"$3" = x"no" ]; then

2990 : # Do not pursue those who leave

2991 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

2992 : # Member can change own email address for the joining moderated group

2993 else # adding user is $user itself

2994 case `getgroupattr $1 regmode` in

2995 moderated)

2996 joingrprequest "$@" # Request only

2997 return

2998 ;;

2999 *)

3000 ;;

3001 esac

3002 fi

3003 qgname=`sqlquote "$1"`

3004 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3005 cond="where gname=$qgname and user='$2'"

3006 if [ x"$3" = x"yes" ]; then

3007 query "replace into grp_mem values($qgname, '$2');"

3008 # Notify joingrp to admin

3009 action="に加入しました。"

3010 if [ -n "$4" ]; then

3011 if msg=`emaildomaincheck "$4"`; then

3012 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3013 'string', '$4', NULL);"

3014 else

3015 echo $msg

3016 fi

3017 else

3018 query "delete from grp_mem_s $cond and key='email';"

3019 fi

3020 if [ -n "$5" ]; then # as ADMIN

3021 # Coming here means newly created group

3022 sql="select case\

3023 when (select count(*) from grp_mem where gname=$qgname)=1\

3024 then (select user from grp_mem\

3025 where gname=$qgname and user='$user')\

3026 else '' end; "

3027 err NewGrpChk: $sql

3028 if [ -n "`query \"$sql\"`" ]; then

3029 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3030 query "replace into grp_adm values($qgname, '$user');"

3031 fi

3032 fi

3033 else

3034 query "begin;

3035 delete from grp_mem $cond;

3036 delete from grp_mem_s $cond;

3037 delete from grp_mem_m $cond;

3038 delete from grp_adm $cond;

3039 delete from grp_adm_s $cond;

3040 delete from grp_adm_m $cond;

3041 end;"

3042 action="から脱退しました。"

3043 fi

3044 smail "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3045 $url?grp+$grid

3046 $user (`gecos $user`)さんが

3047 グループ $1

3048 $action

3049 EOF

3050 }

3051 grp_add_team() (

3052 # $1=grp-rowid $2=team $3...=user-rowid(s)

3053 grp=`getgroupbyid $1`

3054 team=$2; shift; shift

3055 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3056 { echo "BEGIN;"

3057 for user; do

3058 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3059 '$grp',\

3060 (SELECT name FROM user WHERE rowid=$user),\

3061 'team', 'string', '$team');"

3062 done

3063 echo "END;"

3064 } | query

3065 )

3066 grp_rm_team() (

3067 # $1=grp-rowid $2=team $3...=user-rowid(s)

3068 grid=$1

3069 qgrp=$(sqlquote "`getgroupbyid $grid`")

3070 team=$2; shift; shift

3071 [ -z "$grid" -o -z "$team" ] && return

3072 { echo "BEGIN;"

3073 for user; do

3074 echo "DELETE FROM grp_mem_m\

3075 WHERE gname=$qgrp \

3076 AND user=(SELECT name FROM user WHERE rowid=$user)\

3077 AND key='team' AND val='$team';"

3078 done

3079 cat<<-EOF

3080 DELETE FROM blog_s

3081 WHERE rowid=(

3082 SELECT rowid

3083 FROM blog_s a

3084 WHERE key='team'

3085 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3086 AND NOT EXISTS (SELECT * FROM grp_mem_m

3087 WHERE key='team' AND val=a.val -- a.val=team

3088 AND gname = (SELECT val FROM blog_s b

3089 WHERE a.id=b.id AND key='owner')

3090 ));

3091 EOF

3092

3093 echo "END;"

3094 } | query

3095 )

3096 grp_reg_adm() {

3097 # $1=grp-rowid $2...=user-rowid

3098 grid=$1

3099 grp=`getgroupbyid "$1"`

3100 if [ -z "$grp" ]; then

3101 echo "無効なグループIDです" | html p; return

3102 fi

3103 if ! isgrpowner "$user" "$grp"; then

3104 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3105 fi

3106 shift

3107 for urid; do

3108 newadm=`query "select name from user where rowid=$urid;"`

3109 if [ -z "$newadm" ]; then

3110 echo "指定ユーザIDがおかしいようです。" | html p; return

3111 fi

3112 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3113 err ismember $newadm $grp

3114 if ismember $newadm "$grp"; then

3115 # OK, go ahead

3116 getgname="(select gname from grp where rowid=$grid)"

3117 query "replace into grp_adm values($getgname, '$newadm');"

3118 # confirm insertion

3119 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3120 if [ -n "`query \"$sql;\"`" ]; then

3121 echo "追加完了: $newadm" | html p

3122 else

3123 echo "追加失敗($1 $urid)" | html p

3124 fi

3125 fi

3126 showgroup $grid

3127 done

3128 }

3129 dt_rowhack() {

3130 # From: <TR>

3131 # ....

3132 # <TD>rowclass=foo</TD>

3133 # </TR>

3134 # To: <TR class="foo">....<TD>foo</TD></TR>

3135 sed -e '

3136 /^<TR>/ {

3137 :loop

3138 s/\n//

3139 N

3140 /<\/TR>/ {

3141 s/\n//

3142 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3143 n

3144 }

3145 $q

3146 b loop

3147 }'

3148 }

3149 dumptable() {

3150 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3151 # textのフィールドだけ全てダンプにしたほうがいいか

3152 # $DT_VIEW sets link

3153 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3154 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3155 VIEW=${DT_VIEW-replyblog}

3156 if [ -n "$VIEW" ]; then

3157 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3158 fi

3159 sqlfile=$tmpd/dump.sql

3160 : > $sqlfile # ensure to be empty

3161 printf '.mode html\n.header 1\n' > $sqlfile

3162 # $DT_CHLD=ChildTable:BindColumn

3163 if [ -n "$DT_CHLD" ]; then

3164 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3165 cat<<-EOF >> $sqlfile

3166 -- presql

3167 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3168 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3169 EOF

3170 # Speed up counting of new articles

3171 cat<<-EOF >> $sqlfile

3172 -- presql2

3173 DROP TABLE IF EXISTS _counts;

3174 CREATE TEMPORARY TABLE _counts AS

3175 SELECT $_i, count($_i) cnt

3176 FROM $_t GROUP BY $_i;

3177 /* Prepare NEW count table */

3178 CREATE TEMPORARY TABLE _target AS

3179 SELECT b.rowid trowid, b.id

3180 FROM "$2" b JOIN "$2_s" s

3181 ON b.id=s.id AND s.key='owner'

3182 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3183

3184 DROP TABLE IF EXISTS _children;

3185 CREATE TEMPORARY TABLE _children AS

3186 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3187 FROM (SELECT t.trowid, t.id $_i, a.id

3188 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3189 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3190

3191 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3192 DROP TABLE IF EXISTS _news;

3193 DROP VIEW IF EXISTS _news;

3194

3195 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3196 -- INSERT INTO _news

3197 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3198 CREATE TEMPORARY VIEW _news AS

3199 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3200 FROM (SELECT DISTINCT id FROM _target)

3201 a LEFT JOIN

3202 (SELECT $_i, count(ctime) newcnt

3203 FROM _children x

3204 WHERE ctime > coalesce((SELECT time from myacclog

3205 WHERE tblrowid=x.trowid),

3206 '1970-01-01')

3207 GROUP BY $_i) b

3208 ON a.id=b.$_i;

3209 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3210 EOF

3211 # REMOVE next line until 2019/5/1

3212 cntall="(select count($_i) from $_t where $_i=a.id)"

3213 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3214 # REMOVE next assignment until 2019/5/1

3215 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3216 and id in (select id from $_t where $_i=a.id) \

3217 and val > coalesce((select time from myacclog where \

3218 tblrowid=a.rowid),\

3219 '1970-01-01'))"

3220 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3221 cnt="$cntnew as '新着', $cntall as '総数',"

3222 dt_class=" td2r td3r dumpblogs"

3223 fi

3224 # Construct join expression

3225 eav="" scols=""

3226 pk=`gettblpkey $2`

3227 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3228 substr=${substr:-%s}

3229 for col in ${3:-`gettbl_s_cols $2`}; do

3230 valvar=val

3231 case $col in

3232 gecos) scols="$scols${scols:+, }${col#}"

3233 continue ;; # built-in column name

3234 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3235 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3236 case "$as" in

3237 *:*=*) cnd=${as#*:}

3238 h=${cnd%%=*} v=${cnd#*=}

3239 h=`sqlquotestr "$h"`

3240 v=`sqlquotestr "$v"`

3241 valvar="CASE val WHEN $h THEN $v END"

3242 as=${as%%:*} ;;

3243 esac

3244 ;;

3245 *) as=${col} ;;

3246 esac

3247 ss=`printf "$substr" "$valvar"`

3248 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3249 scols="$scols${scols:+, }b.$as"

3250 done

3251 #case author when '$user' then a.rowid else '---' end as ID,

3252 if [ -n "$DT_SQL" ]; then

3253 echo "$DT_SQL"

3254 else

3255 cat<<-EOF

3256 SELECT a.rowid as LINK, $cnt $scols

3257 FROM $2 a LEFT JOIN

3258 (SELECT $pk,$eav,

3259 max(CASE key

3260 WHEN 'owner'

3261 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3262 as gecos

3263 FROM ${2}_s c GROUP BY $pk)

3264 b ON a.$pk=b.$pk $4;

3265 EOF

3266 fi >> $sqlfile

3267 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3268 sqlog<<-EOF

3269 *** SQL-file: $sqlfile ***

3270 `cat $sqlfile`

3271 EOF

3272 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3273 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3274 cat $sqlfile >> tmp/sql

3275 # query ".read $sqlfile" > $tmpd/foo

3276 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3277 cp $tmpd/foo tmp/

3278 err "----- `gdate +%FT%T.%3N` ------------555555"

3279 ## $ddd LINE exists at the end of this function

3280 fi

3281 printf '.mode list\n.header 0\n' >> $sqlfile

3282 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3283 <div>

3284 <div class="dumptable">

3285 <table class="b$dt_class">

3286 `query ".read $sqlfile"`

3287 </table>

3288 </div>

3289 </div>

3290 EOF

3291 ### `query ".read $sqlfile"`

3292 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3293

3294 ## REMOVE THIS!

3295 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3296 }

3297

3298 par2table() (

3299 # copy current parameters of par into destination table

3300 # $1=definition-file

3301 # Using $user and $session

3302 # Return value:

3303 # 0: Stored successfully

3304 # 1: Insufficient fillings

3305 # 2: No permission to modify the record

3306 # 3: Invalid rowid

3307 # 4: SUCCESS to delete

3308 # 5: Stop deletion for lack of confirm check

3309 # 6: Password length too short

3310 # 7: Password mismatch

3311 # 8: Old password incorrect

3312 rowid=`getpar rowid`

3313 if [ ! -e $1 ]; then

3314 echo "テーブル定義ファイルが見付かりません" | html p

3315 exit 1

3316 fi

3317 tbl=${1%.def}

3318 tbl=${tbl##*/}

3319 if [ -n "$rowid" ]; then # Modify existing entry

3320 if [ x"$tbl" = x"user" ]; then

3321 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3322 elif [ x"$tbl" = x"grp" ]; then

3323 sql="select gname from $tbl where rowid=$rowid;"

3324 ##err p2t:grp:q $sql

3325 isgrpowner "$user" "`query $sql`" && rowowner=$user

3326 else

3327 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3328 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3329 fi

3330 ### err rowowner=$rowowner

3331 if [ x"$user" != x"$rowowner" ]; then

3332 echo "他人のレコードはいじれないの" | html p

3333 return 2

3334 elif [ -z "$rowowner" ]; then

3335 echo "指定したレコードはないみたい" | html p

3336 return 3

3337 fi

3338 rm=`getpar rm` cfm=`getpar confirm`

3339 # Editing existent entry

3340 if [ x"$rm" = x"yes" ]; then

3341 if [ x"$rm$cfm" = x"yesyes" ]; then

3342 query "delete from $tbl where rowid=$rowid;"

3343 return 4

3344 else

3345 echo "消去確認のチェックがないので消さなかったの..." | html p

3346 return 5

3347 fi

3348 fi

3349 fi

3350

3351 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3352 if [ -n "$rowid" ]; then

3353 # Update of existing record

3354 for col in `gettblcols $tbl`; do

3355 val=`getparquote $col`

3356 [ -z "$val" ] && continue

3357 ## err query "update $tbl set $col=$val where rowid=$rowid"

3358 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3359 sql="update $tbl set $col=$val where rowid=$rowid;"

3360 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3361 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3362 ## User name cannot be changed with interface provided with this

3363 ## script. But we offer the trigger to change owner user

3364 ## of blog_s table.

3365 #err "select quote($col) from $tbl where rowid=$rowid;"

3366 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3367 cat<<-EOF | query

3368 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3369 -- should use EXCLUSIVE transaction outside of this.

3370 SAVEPOINT par2table;

3371 $sql

3372 update blog_s set val=$val

3373 where key='owner' and val=$old;

3374 RELEASE SAVEPOINT par2table;

3375 EOF

3376 ## XX: DIRTY Hack Ends here

3377 ## We should keep blog's owner as a single column which has

3378 ## foreign key constraint with primary key of grp/user.

3379 else

3380 query "$sql"

3381 fi

3382 done

3383 # Then, set up $pval for further insertion of tbl_s and tbl_m

3384 for col in `gettblpkey $tbl`; do

3385 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3386 pval="$pval${pval:+, }\"$val\""

3387 done

3388 else

3389 # New entry

3390 # Generate values() for primary keys

3391 for col in `gettblpkey $tbl`; do

3392 # Genuine primary keys for _m and _s

3393 val=`getvalquote $tbl $col`

3394 [ -z "$val" ] && continue

3395 pval="$pval${pval:+, }$val"

3396 done

3397 ##err pval=$pval

3398 for col in `gettblfkey $tbl`; do

3399 # args for values() to insertion into master table

3400 val=`getvalquote $tbl $col`

3401 [ -z "$val" ] && continue

3402 formaster=$formaster"${formaster:+, }$val"

3403 done

3404 formaster="$pval${formaster:+, }$formaster"

3405 ## err formaster=$formaster

3406 if [ -z "$formaster" ]; then

3407 echo "項目を全て埋めてください" | html pre

3408 return 1

3409 fi

3410 ## err "replace into $tbl values($formaster);"

3411 query "replace into $tbl values($formaster);"

3412 ## Insertion to master table, done

3413 fi

3414

3415 for kt in s m; do

3416 tb2=${tbl}_$kt

3417 for col in `gettbl_${kt}_cols $tbl`; do

3418 ptype=`getpartype $col "limit 1"`

3419

3420 # First, check update of existing entries in _m

3421 if [ $kt = m ]; then

3422 # sessID|address.1.22|string|Somewhere-x.y.z

3423 sql=""

3424 ##err dots from query "select var from par where var like '$col.%';"

3425 for v in `query "select var from par where var like '$col.%';"`; do

3426 # v=address.1.22

3427 st_rowid=${v##*.}

3428 origcol=${v%%.*} # original column derived from

3429 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3430 ##case `getpartype $v` in

3431 ## err CASE `gettbl_coltype $tbl/$origcol` in

3432 ## err edit flag = `getpar action.$v`

3433 case `getpar action.$v` in

3434 rm)

3435 if [ x`getpar confirm.$v` = x"yes" ]; then

3436 newsql="delete from $tb2"

3437 else

3438 echo "削除確認未チェック" | html p

3439 fi ;;

3440 edit)

3441 case `gettbl_coltype $tbl/$origcol` in

3442 image|document|binary)

3443 file=$tmpd/`getparfilename $v`

3444 ## err type=file=$file

3445 [ -z "$file" ] && continue

3446 bn=`sqlquotestr "${file##*/}"`

3447 bin="X'"$(hexize "$file")"'"

3448 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3449 type=\"file:$ct\"

3450 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3451 cachedir=`getcachedir "$tbl/$rowid"`

3452 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3453 rm -rf $cachedir

3454 ;;

3455 *)

3456 newsql="update $tb2 set val=(select val from par where var \

3457 like '$col.%.$st_rowid')"

3458 ;;

3459 esac

3460 ;;

3461 *) # maybe "keep", do not modify value

3462 continue

3463 ;;

3464 esac

3465 # err newsql=$newsql

3466 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3467 done

3468

3469 if [ x"$bin" = x"NULL" ]; then

3470 ## err repl:normal sql=`echo $sql`

3471 query "$sql

3472 delete from $tb2 where type='string' and val='';"

3473 ## err repl:normal done

3474 else

3475 sqlfile="$tmpd/sqlf.$$"

3476 echo "$sql" > $sqlfile

3477 ## err repl:sqlfile=`ls -lF $sqlfile`

3478 query ".read $sqlfile"

3479 ## err repl:done

3480 fi

3481 # Rest of kt==m: set multiple mode

3482 nr=`getparcount $col`

3483 else

3484 nr=1 # for kt==s, number of records is 1

3485 fi

3486

3487 i=0

3488 while [ $i -lt $nr ]; do

3489 limit="limit 1 offset $i"

3490 i=$((i+1)) # increase beforehand against continue

3491 val=`getvalquote $tbl $col "$limit"`

3492 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3493 ## err $col=$val

3494 bin=NULL

3495 ## err partype$col=`getpartype $col "$limit"`

3496 case $ptype in

3497 file) file=$tmpd/`getparfilename $col "$limit"`

3498 ## err parfile-$col=$file

3499 [ -z "$file" ] && continue

3500 bin="X'"$(hexize "$file")"'"

3501 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3502 type=\"file:$ct\" ;;

3503 "*"*) continue ;; # foreign table

3504 *) type=\"string\" ;;

3505 esac

3506 case `gettbl_coltype $tbl/$col` in

3507 password) # special care for password

3508 # name={password,pswd1,pswd2}

3509 p1=`getpar pswd1 "$limit"`

3510 if [ -z "$p1" ]; then

3511 continue # SKIP password setting, if p1 is empty

3512 else

3513 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3514 ## err pswd=$pswd

3515 if pwcheck "$pswd"; then

3516 if [ x"$p1" = x"$p2" ]; then

3517 case "$p1" in

3518 ??????????*) ;;

3519 *) echo "パスワードは10字以上にしてください。" | html p

3520 return 6;;

3521 esac

3522 val="\"`echo $p1|mypwhash`\""

3523 else

3524 echo "2つの新パスワード不一致" | html p

3525 return 7

3526 fi

3527 else

3528 echo "旧パスワード違います" | html p

3529 return 8

3530 fi

3531 fi

3532 ;;

3533 esac

3534 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3535 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3536 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3537 if [ x"$bin" = x"NULL" ]; then

3538 ## err Normal-query: `echo $sql`

3539 query "$sql"

3540 else

3541 sqlfile="$tmpd/query.$$"

3542 echo "$sql" > $sqlfile

3543 ## err sqlfile=`ls -lF $sqlfile`

3544 query ".read $sqlfile"

3545 fi

3546 ## err p2t done

3547 done

3548 done

3549 done

3550 return 0

3551 ##err donee

3552 )

3553 genform() {

3554 # $1 = form definition file

3555 # $2, $3 (optional)= table name and ROWID

3556 # If $GF_VIEWONLY set and nonNull, output values without form

3557 # If $GF_ARGS set, use it as content-strings in the form

3558 # If $GF_OWNER set, use it as value of name="owner"

3559 # If $GF_STAGE set, use it as value of name="stage"

3560 forms="" hiddens="" rowid=$3

3561 if [ ! -e "$1" ]; then

3562 echo "そのようなデータベースはないようです($2)。" | html p

3563 return

3564 elif [ -n "$2" ]; then

3565 rec=`query "select * from $2 where rowid='$rowid';"`

3566 if [ -z "$rec" ]; then

3567 pk=`gettblpkey $2`

3568 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3569 rec=`query "select rowid from $2 where $pk='$rowid';"`

3570 rowid=$rec

3571 rec=$3

3572 fi

3573 if [ -z "$rec" ]; then

3574 echo "そんなレコードはないみたいね..." | html p

3575 return

3576 fi

3577 fi

3578 if [ -z "$GF_VIEWONLY" ]; then

3579 rm='<input id="rm" name="rm" type="checkbox"

3580 value="yes"><label for="rm">このエントリの削除</label>

3581 <span>ほんとうに消しますよ(確認)!

3582 <input name="confirm" type=checkbox value="yes">はい</span>'

3583 fi

3584 # Image Cache dir

3585 ## err genform: getcache=$2/$rowid

3586 td=`getcachedir "$2/$rowid"`

3587 while IFS=: read prompt name keytype type args; do

3588 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3589 sp="${args:+ }"

3590 form="" val=""

3591 if [ -n "$rowid" ]; then

3592 # err genform2a: Seeking for "$2.$name, type=$type"

3593 rawval=`getvalbyid $2 $name $rowid $td`

3594 val=`echo "$rawval"|htmlescape`

3595 ## err genform3a: getvalbyid $2 $name $rowid $td

3596 ## err genform3b: val="[$val]" type="$type"

3597 fi

3598 if [ -n "$GF_VIEWONLY" ]; then

3599 is_hidden "$2" "$name" && continue

3600 fi

3601 case "$type" in

3602 text*)

3603 cgiform=cgi_multi_$type

3604 if [ -s $td/$name.count -a -n "$val" ]; then

3605 form=`$cgiform $name $td`

3606 val=$(echo "$val"|

3607 while read fn; do

3608 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3609 </td></tr>$nl"

3610 done)

3611 val="<table>$nl$val$nl</table>"

3612 else

3613 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3614 form=`cgi_$type $name "$rawval" "$args"`

3615 fi

3616 ;;

3617 [Rr][Aa][Dd][Ii][Oo])

3618 fh="<label><input type=\"radio\" name=\"$name\""

3619 form="`echo $args|sed -e \

3620 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3621 ;;

3622 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3623 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3624 ;;

3625 [Ss][Ee][Ll][Ee][Cc][Tt])

3626 fh="<select name=\"$name\">$nl"

3627 form=$(for l in $args; do

3628 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3629 done)

3630 if [ -n "$val" ]; then

3631 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3632 fi

3633 form="$fh$form</select>"

3634 ;;

3635 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3636 if [ -s $td/$name.count ]; then

3637 form=`cgi_multi_file $name $td "$args"`

3638 if [ -n "$val" ]; then

3639 hrfb="$myname?showattc+$2_m"

3640 val=$(echo "$rawval" \

3641 | while read fn; do

3642 data=`percenthex "$td/$fn"`

3643 #ct=`cat $td/$fn.content-type`

3644 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3645 ri=`cat "$td/$fn.rowid"`

3646 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3647 #imgsrc="<img src=\"data:$ct,$data\">"

3648 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3649 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3650 done)

3651 fi

3652 else

3653 form="<input type=\"file\" name=\"$name\" $args>"

3654 if [ -n "$val" ]; then

3655 imgs=$(echo "$rawval"\

3656 |while read fn;do

3657 data=`percenthex "$td/$fn"`

3658 echo "<img src=\"data:image/png,$data\">$fn<br>"

3659 done)

3660 form=$form"<br>$imgs"

3661 val=$imgs # 2015-06-15

3662 else

3663 form="<input type=\"file\" name=\"$name\" $args>"

3664 fi

3665 fi

3666 ;;

3667 [Hh][Ii][Dd][Dd][Ee][Nn])

3668 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3669 args="value=\"$GF_STAGE\""

3670 fi

3671 form="<input type=\"hidden\" name=\"$name\" $args>"

3672 prompt='' # Remove prompt

3673 ;;

3674 [Aa][Uu][Tt][Hh][Oo][Rr])

3675 [ -n "$GF_VIEWONLY" ] && continue

3676 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3677 prompt="" ;;

3678 [Oo][Ww][Nn][Ee][Rr])

3679 [ -n "$GF_VIEWONLY" ] && continue

3680 val=${GF_OWNER:-$val}

3681 val=${val:-$user}

3682 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3683 prompt="" ;;

3684 [Uu][Ss][Ee][Rr])

3685 # XXX: is null $user ok?

3686 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3687 [ -n "$GF_VIEWONLY" ] && continue

3688 form="$user"

3689 ;;

3690 [Pp]assword)

3691 [ -n "$GF_VIEWONLY" ] && continue

3692 form="`cgi_passwd`"

3693 val=""

3694 ;;

3695 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3696 [ -n "$GF_VIEWONLY" ] && continue

3697 if [ -z "$rowid" ]; then

3698 val=`genserial`

3699 fi

3700 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3701 prompt="" ;;

3702 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3703 prompt=""

3704 ;;

3705 parent|path|blog*)

3706 prompt=""

3707 ;;

3708 "*"*)

3709 tail=$tail"``"

3710 continue ;;

3711 esac

3712 if [ -n "$prompt" ]; then

3713 if [ -n "${GF_VIEWONLY}" ]; then

3714 form=$val

3715 else

3716 :

3717 fi

3718 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3719 else

3720 hiddens=$hiddens$nl"$form"

3721 fi

3722 done < $1

3723 # enctype="multipart/form-data"

3724 cat<<EOF

3725 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3726 ${rowid:+$rm}

3727 <table class="b $2">

3728 $forms

3729 </table>$hiddens

3730 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3731 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3732 EOF

3733 if [ -z $GF_VIEWONLY ]; then

3734 cat<<EOF

3735 <input type="submit" name="sub" value="OK">

3736 <input type="reset" name="res" value="Reset">

3737 EOF

3738 fi

3739 cat<<EOF

3740 $GF_ARGS</form>

3741 $tail

3742 EOF

3743 }

3744 edittable() {

3745 # $1=form-def $2=table $3 rowid

3746 genform "$@"

3747 }

3748 viewtable() {

3749 GF_VIEWONLY=1 genform "$@"

3750 }

3751 showattc() {

3752 # $1=table_m $2=rowid &optional $3=RawFlag

3753 ## err \$1=$1 \$2=$2 \$3=$3

3754 if ! isfilereadable $user $1 $2; then

3755 contenttype; echo

3756 echo "このファイルは管理者のみしか見られません" | html p

3757 putfooter; exit

3758 fi

3759 idir=`umask 002; mktempd` || exit 1

3760 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3761 bin=$idir/$myname-$$.bin

3762 sql="select quote(bin) from $1 where rowid='$2';"

3763 ## err showattc: sql: $sql

3764 sq $db "$sql" | unhexize > $bin

3765 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3766 type=${tv%//*} fn=${tv#*//}

3767 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3768 ct=${type#file:}

3769 case $ct in # all text/* changed to text/plain

3770 text/*)

3771 charset=`nkf -g $bin|cut -d' ' -f1`

3772 case $charset in

3773 ASCII*) charset="" ;;

3774 esac

3775 if [ -z "$3" ]; then

3776 ct="text/html${charset:+; charset=$charset}"

3777 link="?showattc+$1+$2+raw"

3778 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3779 | sed 's,^,<span></span>,' \

3780 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3781 -D_LINK_="$link" \

3782 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3783 exit $?

3784 fi

3785 ct="text/plain${charset:+; charset=$charset}"

3786 ;;

3787 esac

3788 contenttype "$ct"

3789 echo "Content-Disposition: filename=\"$fn\""

3790 echo "Content-Length: " `cat $bin | wc -c`; echo

3791 #echo "Content-Type: " ${type#file:}; echo

3792 cat $bin

3793 }

3794 #

3795 # Some default stupid handler on CGI values

3796 #

3797 default_storedb() {

3798 # ARG: $1=table-def-file

3799 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3800 tbl=`basename $1`

3801 tbl=${tbl%.def}

3802 cols="`grep :text $1|cut -d: -f2`"

3803 col=`echo "$cols"|head -1`

3804 vcol=`getpar $col`

3805 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3806 if [ -n "$vcol" ]; then

3807 par2table $1

3808 else

3809 return 2 # No insertion occurred

3810 fi

3811 }

3812

3813 default_view() { # $1=def-file

3814 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3815 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3816 default_storedb "$@"

3817 query "select rowid from $tbl order by rowid desc;" \

3818 | while read rowid; do

3819 viewtable $1 $tbl $rowid

3820 done | _m4 -D_TITLE_="$tbl" \

3821 -D_FORM_="`genform $1`" \

3822 -D_DUMPTABLE_="syscmd(cat)" \

3823 $layout/html.m4.html $layout/form+dump.m4.html

3824 }

3825 default_viewtext() { # $1=def-file

3826 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3827 default_storedb "$@"

3828 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3829 | _m4 -D_TITLE_="$tbl" \

3830 -D_FORM_="`genform $1`" \

3831 -D_DUMPTABLE_="syscmd(cat)" \

3832 $layout/html.m4.html $layout/form+dump.m4.html

3833 }

3834 default_smail() {

3835 default_storedb "$@"

3836 if [ $? -eq 2 ]; then

3837 _m4 -D_TITLE_="入力" \

3838 -D_FORM_="`genform $1`" \

3839 -D_DUMPTABLE_="" \

3840 $layout/html.m4.html $layout/form+dump.m4.html

3841 return

3842 fi

3843 cond=""

3844 for pk in `gettblpkey $tbl`; do

3845 pv=$(sqlquote "$(getpar $pk)")

3846 cond="$cond${cond:+ and }$pk=$pv"

3847 done

3848 sql="select rowid from $tbl where $cond;"

3849 rowid=`query "$sql"`

3850 ## err smail1 - "$sql" "-> rowid=$rowid"

3851

3852 while IFS=: read prompt name keytype type args; do # Read from $1

3853 val=`getpar $name`

3854 if [ -n "$val" ]; then

3855 text="$text

3856 $prompt

3857 $name=$val

3858 ---------------------------------------------------------"

3859 fi

3860 case "$type" in

3861 image|document|file)

3862 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3863 fns=$(echo "$fn"|while read fn; do

3864 err mv $tmpd/$fn.orig $tmpd/$fn

3865 mv $tmpd/$fn.orig $tmpd/$fn

3866 rm $tmpd/$fn.rowid # Remove cache flag

3867 ## err "`ls $tmpd/$fn`"

3868 echo $fn

3869 done)

3870 files="$files $fns"

3871 ;;

3872 esac

3873 done < $1

3874 ## err FILES=$files "`ls -lF $tmpd`"

3875 subj="from ${REMOTE_ADDR}"

3876 (echo "$url"

3877 echo "への書き込みがありました。"

3878 echo "------"

3879 echo "$text"

3880 ) | (cd $tmpd &&

3881 err LS="`ls -lF`" &&

3882 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3883 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3884 echo "以下の内容で送信しました。" | html p

3885 viewtable $1 $tbl \

3886 `query "select rowid from $tbl order by rowid desc limit 1;"`

3887 echo "戻る" | html a "href=\"?\""

3888 }