s4: 93f770f87662 s4-funcs.sh

s4

view s4-funcs.sh @ 604:93f770f87662

Set umask severely

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sun, 12 Apr 2020 14:10:06 +0900
parents	6d89f4a15778
children	3b614fa74617

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

48 oumask=`umask`

49 umask 027

51 tconfs=""

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

53 conftbl=_tblconf

54 nl="

55 "

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

57 iconcachekey="profimgcache_S"

58 case "$HTTP_USER_AGENT" in

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

60 *) touchpanel="" ;;

61 esac

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

64 : <<EOF

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

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

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

69 レポート提出管理

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

72 [2016]

73 7/12 根本への反省

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

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

77 [以下2015]

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

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

80 Web

81 締切設定

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

84 ○自分の提出物リスト

86 7/19 ○設置

87 ○一斉送信

88 ○getparfilename の tmpd の扱い

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

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

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

92 ○添付ファイル回収

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

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

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

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

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

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

100 ○レポート提出モード

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

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

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

104

105

106 7/9 ○管理者の追加

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

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

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

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

111

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

113

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

115 ○グループ検索

116

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

118

119

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

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

122

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

124 ○6/1 par2tableを triplex 対応に

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

126 →とすると一気に

127

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

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

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

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

132

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

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

135 ## 1. passwd

136 ## 2. grp

137 ## 3. grp_mem

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

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

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

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

142

143 ## ● listの定義:

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

145 ## ● hashの定義:

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

147

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

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

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

151 ## csv2sq3 で .csv.sq3 の Makefile

152

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

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

155 ## type := root | comment

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

157 ## unique(id, filename)

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

159

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

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

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

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

164 ## できたー!

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

166 ↓

167 ↓以下に変更

168 with recursive allmem as

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

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

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

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

173

174

175 with recursive allmem as

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

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

178 where allmem.val=grp_m.gname)

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

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

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

182

183

184 ## triggerもできた。

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

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

187

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

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

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

191 ## グループ管理

192 ## 個人情報管理

193 ##

194

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

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

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

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

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

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

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

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

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

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

205

206 ## ログテーブル

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

208

209

210

211 ■表設計

212 * 3つの表に分散管理

213 id格納表 + hash表 + list表

214 * *_s *_m

215

216

217

218 user, user_map, user_col

219

220 ■抽象エントリタイプ

221 * user

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

223 * group

224 権限判定に利用

225 * serial

226 自動idとして機能

227 * password

228 入力 type=passwordで入力

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

230 * session

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

232 * text

233 入力 type=text

234 * textarea

235 入力 textarea

236 * image|document

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

238 * owner

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

240 * gowner

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

242 * timestamp

243 datetime()

244 * parent

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

246 * path

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

248

249 格納タイプ

250 * list

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

252 * hash

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

254

255 オブジェクトタイプ

256 * entry

257 id, title, owner

258 * textpart

259 id, parentID, text

260 * binarypart

261 id, parentID, contenttype, filename, content

262 * content

263 hash(textpart), list(binarypart)

264 * topic

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

266 * reply

267 id, parentID, content

268 * blog

269 list(entry)

270 blog = [topic, list(reply)]

271

272

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

274 "text" => "hogehoge ..",

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

276 "author" => "taro",

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

278 "parent" => "/",

279 "path" => "/1",

280 "text" => "blah, blah, ....",

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

282 {"serial" => 2,

283 "author" => "hanako",

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

285 "parent" => "/",

286 "path" => "/2",

287 "text" => "blah, blah, ....",

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

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

290

291

292 user:=

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

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

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

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

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

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

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

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

301

302 変換表

303 /user/email=m

304

305 blog:=

306 シリアル:id:p:serial

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

308 所有者:owner:s:owner:

309 時刻:ctime:s:stamp:

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

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

312

313 article:=

314 シリアル:id:p:serial

315 筆者:author:s:owner

316 時刻:ctime:s:stamp:

317 参照元:parent:s:parent:

318 パス:path:s:path:

319 本文:text:s:textarea:

320 画像:image:m:image:

321

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

323

324

325 EOF

326

327 logstart() {

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

329 }

330 logend() {

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

332 }

333 sqlog() {

334 logstart

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

336 cat >> $querylog

337 else

338 echo "$*" >> $querylog

339 fi

340 logend

341 }

342 sq() {

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

344 logstart

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

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

347 else

348 echo "$@" >> $querylog

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

350 fi

351 logend

352 }

353 dbsetup() {

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

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

356 sqi=$tmpdir/sqi.$$

357 sqo=$tmpdir/sqo.$$

358 mkfifo $sqi $sqo

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

360 sq $db < $sqi &

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

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

363 exec 2>> $tmpdir/error.out

364 fi

365 exec 3>> $tmpdir/debug.out

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

367 rm $sqi

368 }

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

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

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

372 fi

373 }

374 cleanup() {

375 trap '' INT HUP EXIT TERM PIPE

376 echo .quit >&5

377 kill $sq3pid

378 kill $sq3pid

379 rm -f $sqo $sqi

380 rm -rf $tmpfiles

381 cleanup2

382 }

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

384 # simple redirection for the sake of speed.

385 query() {

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

387 echo ".output $sqo" >&5

388 logstart

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

390 tee -a $querylog

391 else

392 echo "$@" >> $querylog

393 echo "$@"

394 fi >&5

395 echo ".output stdout" >&5

396 cat $sqo

397 logend

398 }

399 _m4() {

400 #S4NAME=f,f,f

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

402 }

403 ismember() {

404 # $1=user, $2=group

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

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

407 }

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

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

410 }

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

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

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

414 }

415 isgrpowner() (

416 # $1=user, $2=group

417 gn=`sqlquote "$2"`

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

419 err isgrpowner: $sql

420 test -n "`query $sql`"

421 )

422 isgrpownerbygid() (

423 # $1=user, $2=group-rowid

424 sql="select user from grp_adm where gname=(select gname from grp where rowid=$2) and user='$1';"

425 err isgrpownerbygid: $sql

426 test -n "`query $sql`"

427 )

428 getgroupadminmails() {

429 # $1=group

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

431 email4group "$1" "$i" ;

432 done

433 }

434 getgroupadmins() { # $1=group

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

436 qgrp=`sqlquote "$1"`

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

438 }

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

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

441 getvalbyid grp $2 \

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

443 }

444 getgroupbyid() {

445 # $1=id|gname

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

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

448 # err ggbyid: `echo $sql`

449 query $sql

450 }

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

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

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

454

455 # Return true when anonymous mode

456 [ "$anonymousmode" ] && return 0

457 # case `getvalbyid blog mode $2` in

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

459 # *closed*)

460 # owner=`getvalbyid blog owner $2`

461 # if isgrp $owner; then

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

463 # elif isuser $owner; then

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

465 # fi

466 # esac

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

468 sql="with getblog as (\

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

470 select blogid from article where id in\

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

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

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

474 select case\

475 when (select author from article where\

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

477 then 'author'\

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

479 then 'open'\

480 when (select val from getmode) is null \

481 then 'open'

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

483 then (select user from grp_adm where \

484 gname=(select val from getowner) and \

485 user='$1')\

486 when (select author from article where\

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

488 then 'user+author'

489 else '' end;"

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

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

492 result=`query "$sql"`

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

494 return 2

495 }

496 linkhome() {

497 # $1=UserOrGroup

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

499 if isuser $1; then

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

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

502 else

503 echo -n "grp+$1"

504 fi

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

506 }

507 hreflink() {

508 # s4 specific notation:

509 # ^href=URL

510 # ^iframe=URL

511 # ^video=URL

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

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

514 # OSM umap Wikistyle Notation:

515 # [[URL]] - Simle Link

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

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

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

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

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

521 # Other Style

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

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

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

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

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

527 # SPC+SPC+$ - <br>

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

529 checkboxON="${cb} checked>"

530 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

555

556 }

557 minitbl() {

558 sed -n '

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

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

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

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

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

564 H; # Concat this line to HoldSpace

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

566 $ b cont

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

568 }

569 :cont

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

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

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

573 # . of ^. is workaround for FreeBSD sed

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

575 p; # Print whole "table" element

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

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

578 }

579 x; # Back to the newest line

580 p; # Print rest' | miniul

581 }

582 miniul() {

583 sed -e '

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

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

586 :top

587 s/\n//;

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

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

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

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

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

593 $ b cont

594 N; # 次の行を読む

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

596 /^ /b top

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

598 p; s/.*//;

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

600 /^\* /b top

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

602 }

603

604 :cont

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

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

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

608 H; x

609 }

610 x'

611 }

612 acclog() (

613 # $1=table, $2=rowid

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

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

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

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

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

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

620 fi

621 )

622 gecos() (

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

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

625 )

626 setpar() {

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

628 }

629 replpar() {

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

631 }

632 getpar() {

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

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

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

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

637 fi

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

639 case "$var" in

640 owner)

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

642 echo $user; return

643 elif ismember $user $val; then

644 echo $val; return

645 fi ;;

646 esac

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

648 echo "$val"

649 }

650

651 getpartype() {

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

653 }

654 getparcount() {

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

656 }

657 getparfilename() {

658 # null if type of $1 is not file

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

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

661 }

662 sqlquote() {

663 (v="$1"

664 case "$v" in

665 "") return ;; # null

666 "X'"*) # quoted hex string

667 echo $1 ;;

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

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

670 echo "\"$v\""

671 return ;;

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

673 echo "\"$v\""

674 return ;;

675 *)

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

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

678 else

679 echo "\"$v\""

680 fi ;;

681 esac)

682 }

683 sqlquotestr() (

684 case "$1" in

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

686 echo "'$v'" ;;

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

688 esac

689 )

690 mktempd() {

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

692 }

693 getcachedir() { # $1=maintable

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

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

696 else

697 echo $tmpd/$thumbxy

698 fi

699 }

700 getval() {

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

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

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

704 echo "$user" ;;

705 stamp|datetime)

706 date "+%F %T" ;;

707 serial)

708 (s=`getpar $2`

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

710 *)

711 getpar "$2" "$3";;

712 esac

713 }

714

715 getvalquote() {

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

717 (v=`getval "$@"`

718 case "$v" in

719 "") echo NULL ;;

720 *) sqlquote "$v" ;;

721 esac)

722 }

723 getparquote() {

724 sqlquote "`getpar $1`"

725 }

726 getbinbyid() {

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

728

729 }

730 getvalbyid() {

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

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

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

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

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

736

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

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

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

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

741 return

742 fi

743 done

744 rowid=$3

745 pk=`gettblpkey $1`

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

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

748 td=${4:-$tmpd}

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

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

751 for kt in s m; do

752 t=${1}_$kt

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

754 vcount=1 # count(val)

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

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

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

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

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

760 if [ $kt = m ]; then

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

762 # Reset val to store filenames if type is string

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

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

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

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

767 i=0

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

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

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

771 i=$((i+1))

772 fn=$c.$i

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

774 case $type in

775 file:*)

776 #file=$td/$val

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

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

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

780 # FOR SPEED: Skip file generation if imgcache exists

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

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

783 && continue

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

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

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

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

788 select rowid from $cond $slice;

789 .output '$td/$fn'

790 select val from $cond $slice;

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

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

793 .output stdout

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

795 EOF

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

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

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

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

800 ls -lh "$file" |

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

802 case "$type" in

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

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

805 *|*:[Aa]pplication*)

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

807 png:- > "$file"

808 ;;

809 esac

810 ;;

811 *)

812 sq $db<<EOF

813 .output $td/$fn.rowid

814 select rowid from $cond $slice;

815 .output $td/$fn

816 select val from $cond $slice;

817 EOF

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

819 ;;

820 esac

821 done

822 else

823 rm -f $td/$c.count

824 case $type in

825 file:*)

826 echo "$val" \

827 | while read fn; do

828 file=$td/$fn

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

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

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

832 | unhexize > "$file"

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

834 case $type in

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

836 *:[Aa]pplication*)

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

838 png:- > $file ;;

839 esac

840 fi

841 done

842 ;;

843 esac

844 fi

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

846 return

847 fi

848 done

849 done)

850 }

851 getvalbypkey() (

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

853 pk=`gettblpkey $1`

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

855 getvalbyid "$1" "$2" $rowid $4

856 )

857 getvalbycond() {

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

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

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

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

862 getvalbyid "$1" "$2" $rowid "$4"

863 fi

864 }

865 getpwfield() {

866 # getpwfield user column

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

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

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

870 echo "$val"

871 return 0

872 else

873 return 1

874 fi

875 }

876 numericalize() {

877 echo "${1%%[!0-9]*}"

878 }

879 encode() {

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

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

882 sha1=sha1

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

884 sha1=sha1sum

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

886 sha1=gsha1sum

887 fi

888 fi

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

890 }

891 enjpeg() {

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

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

894 cjpeg="cjpeg"

895 else

896 cjpeg="convert - jpeg:-"

897 fi

898 fi

899 $cjpeg "$@"

900 }

901 mycrypt() (

902 key=$1 salt=$2

903 # err \$2=$2

904 case $2 in

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

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

907 esac

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

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

910 )

911 hexize() {

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

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

914 hexize="xxd -p"

915 else

916 hexize_hd() {

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

918 }

919 hexize="hexize_hd"

920 fi

921 fi

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

923 }

924 unhexize() {

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

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

927 unhex="xxd -p -r"

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

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

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

931 EOF

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

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

934 # world writable...:)

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

936 fi

937 fi

938 cat "$@" | $unhex

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

940 }

941 percenthex() {

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

943 }

944 htmlescape() {

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

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

947 }

948 enascii() {

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

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

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

952 else

953 enascii_now=`date +%FT%T`

954 enascii_sed() {

955 nkf -Z0Z1Z2 \

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

957 }

958 enascii="enascii_sed"

959 fi

960 fi

961 cat "$@" | $enascii

962 }

963 size_h() {

964 i="$1" oi=$1

965 set -- B B KB MB GB TB

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

967 oi=$i

968 i=$((i/1024))

969 shift

970 done

971 echo ${oi}$1

972 }

973 gettblconf() {

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

975 ## tconfs=`sq $db \

976 tconfs=`query \

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

978 fi

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

980 }

981 gettblkeys() {

982 # $1=tbl

983 gettblconf

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

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

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

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

988 case $type in

989 *p*)

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

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

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

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

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

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

996 esac

997 case $type in

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

999 esac

1000 done

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

1002 }

1003 gettblpkey() {

1004 # $1=tbl

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

1006 }

1007 gettblfkey() {

1008 (x=`gettblkeys $1`

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

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

1011 }

1012 gettblcols() {

1013 (x=`gettblkeys $1`

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

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

1016 }

1017 gettbl_s_cols() {

1018 (x=`gettblkeys $1`

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

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

1021 }

1022 gettbl_m_cols() {

1023 (x=`gettblkeys $1`

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

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

1026 }

1027 gettbl_h_cols() {

1028 (x=`gettblkeys $1`

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

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

1031 }

1032 gettbl_coltype() (

1033 gettblconf

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

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

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

1037 )

1038 is_hidden() {

1039 # $1=Tbl $2=col

1040 gettblconf

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

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

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

1044 case $x in

1045 *h*) return 0 ;;

1046 *) return 1 ;;

1047 esac

1048 }

1049

1050 dbsetbyid() {

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

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

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

1054 quotedp=$(sqlquotestr "$p")

1055 unset primary update

1056 gettblconf

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

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

1059 #err conf=$conf

1060 case ${conf#*=} in

1061 p*) primary=1 ;;

1062 f*) update=1 ;;

1063 u*) ;;

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

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

1066 esac

1067 #err t=$t

1068 type=string fn=""

1069 case $conf in

1070 */password)

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

1072 ;;

1073 */image*|*/document*)

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

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

1076 ;;

1077 esac

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

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

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

1081 if [ "$primary" ]; then

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

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

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

1085 elif [ "$update" ]; then

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

1087 else

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

1089 fi

1090 )

1091 }

1092 expire() (

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

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

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

1096 )

1097 addsession() {

1098 # expireをセット

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

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

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

1102 # Remove old session parameters

1103 now=`expire now`

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

1105 }

1106 gencookie() (

1107 for kv; do

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

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

1110 done

1111 )

1112 contenttype() {

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

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

1115 }

1116 putheader() {

1117

1118 }

1119 putfooter() {

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

1121 }

1122 getcookie() (

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

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

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

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

1127 done

1128 )

1129 genrandom() {

1130 # $1=columns (default: 10)

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

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

1133 }

1134 genserial() {

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

1136 }

1137 smail() {

1138 # smail rcpts subj (file)

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

1140 # $MAIL_FROM <- From: header value

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

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

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

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

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

1146 }

1147 setviastring() {

1148 table=$1

1149 oifs="$IFS"

1150 IFS="&"

1151 for us in $2; do

1152 k=${us%%=*}

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

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

1155 #echo $k=$v

1156 done

1157 IFS="$oifs"

1158 }

1159 checkdomain() (

1160 # Check the validity of domain by referring DNS

1161 item=$1

1162 err checkdomain $1

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

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

1165 )

1166 pwcheck() {

1167 # $1=passwd

1168 dbpswd=`getpwfield $user pswd`

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

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

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

1172 }

1173 mypwhash() {

1174 mycrypt "`cat`" `genrandom 5`

1175 }

1176 wasureta() {

1177 user=$1

1178 if ! checkdomain $user; then

1179 contenttype; echo

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

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

1182 putfooter

1183 exit 0

1184 fi

1185 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1187 encpswd=`echo $newpswd|mypwhash`

1188 dbsetbyid user $user pswd "$encpswd"

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

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

1191 $msgdir/mail-newaccount.m4 \

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

1193 | smail "`collectemail $user`" "New Account"

1194 }

1195 checkauth() {

1196 user=`getpar user`

1197 skc=`getpar skey` # from cookie

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

1199 skey="`getpwfield $user skey`"

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

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

1202 return 0

1203 fi

1204 fi

1205 pswd=`getpar pswd`

1206 quser=`sqlquotestr "$user"`

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

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

1209 return 1

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

1211 wasureta "$user"

1212 return 1 # wasureta error

1213 fi

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

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

1216 if pwcheck "$pswd"; then

1217 newsession=`genrandom 50`

1218 dbsetbyid user "$user" skey "$newsession"

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

1220 gencookie "user=$user" "skey=$newsession"

1221 return 0

1222 fi

1223 return 2 # Password mismatch

1224 }

1225 showlogin() {

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

1227 s4name=${S4NAME:-s4}

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

1229 cat $layout/login.m4.html

1230 echo '</body></html>'

1231 ) | _m4 \

1232 -D_BODYCLASS_="login" \

1233 -D_TITLE_="$s4name" \

1234 -D_SYSNAME_="Welcome to $s4name" \

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

1236 exit 0

1237 }

1238 dologin() {

1239 checkauth

1240 st=$?

1241 if [ $st != 0 ]; then

1242 contenttype; echo

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

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

1245 showlogin # and EXIT

1246 fi

1247 }

1248

1249 # Do instant jobs here

1250 dbsetup

1251 trap cleanup INT HUP EXIT TERM PIPE

1252 # trap cleanup INT HUP

1253

1254 err() {

1255 echo "$@" 1>&3

1256 }

1257

1258 cgiinit() {

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

1260 tmpf=tmp/stream

1261 tmpd=`tmpd=$tmpdir mktempd`

1262 tmpfiles=$tmpfiles" $tmpd"

1263 addsession $session

1264 getcookie

1265 case "$REQUEST_METHOD" in

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

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

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

1269 # cat > $tmpf # too much?

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

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

1272 s="`cat tmp/stream`"

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

1274 ;;

1275 esac

1276 case "$CONTENT_TYPE" in

1277 *boundary*)

1278 bndry=${CONTENT_TYPE#*boundary=}

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

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

1281 do

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

1283 #echo u=$us

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

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

1286 # err k=$k v=$v

1287 case "$k" in

1288 *:filename)

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

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

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

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

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

1294 :

1295 fi

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

1297 case "$mimetype" in

1298 [Ii]mage/x-xcf)

1299 bzip2 "$tmpd/$v"

1300 v=${v}.bz2

1301 ;;

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

1303 [Ii]mage/*)

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

1305 ;;

1306 esac

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

1308 then

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

1310 continue

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

1312 replpar text string \

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

1314 continue

1315 fi

1316 ;;

1317 *)

1318 type='string'

1319 ;;

1320 esac

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

1322 setpar "$k" "$type" "$v"

1323 done

1324 ;;

1325 *)

1326 setviastring par "$s"

1327 ;;

1328 esac

1329 }

1330 email4group() {

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

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

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

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

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

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

1337 and s.key='email'

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

1339 query "$sql"

1340 }

1341 email4groupbyuid() {

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

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

1344 uids=`echo "$@"`

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

1346 sql="WITH

1347 grpemails AS (

1348 SELECT gname, user, val email

1349 FROM grp_mem NATURAL JOIN grp_mem_s

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

1351 useremails AS (

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

1353 FROM user

1354 LEFT JOIN user_m

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

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

1357 FROM useremails u LEFT JOIN grpemails g

1358 ON u.name=g.user

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

1360 ## err email4gByid `echo $sql`

1361 query "$sql"

1362 }

1363 collectmembersbyid() {

1364 # Collect user names of group specified by grid

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

1366 query "SELECT user FROM grp_mem \

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

1368 }

1369 collectmembersbyid() {

1370 # Collect user names of group name

1371 qgrp=`sqlquote "$1"`

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

1373 }

1374 collectgecosesbyid() {

1375 # Collect user gecoses of group

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

1377 query<<-EOF

1378 SELECT gecos

1379 FROM gecoses

1380 WHERE name IN (SELECT user FROM grp_mem

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

1382 EOF

1383 }

1384 collectemail() (

1385 # Collect email addresses for group $1

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

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

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

1389 for e; do

1390 if isuser "$e"; then

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

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

1393 else

1394 qgrp=`sqlquote "$e"`

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

1396 gmem="grp_mem"

1397 else

1398 tm=`sqlquote "$TEAM"`

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

1400 fi

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

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

1403 $gmem g left join grp_mem_s s

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

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

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

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

1408 query "$sql"

1409 fi

1410 done

1411 )

1412 sendinvitation() (

1413 # $1=email

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

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

1416 query "DELETE FROM par WHERE var='invite' AND val='$1';"

1417 query "REPLACE INTO par VALUES('$iss', 'invite', 'string', '$1');"

1418 gecos=`gecos`

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

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

1421 _m4 -D_URL_="$urlbase" \

1422 -D_USER_="$name" \

1423 -D_EMAIL_="$1" \

1424 -D_REGIST_="$regist" \

1425 -D_ADMIN_="$admin" \

1426 $msgdir/mail-invite.m4 \

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

1428 return 0

1429 )

1430 emaildomaincheck() {

1431 case "$1" in

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

1433 *@*)

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

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

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

1437 return 2

1438 fi

1439 return 0

1440 ;;

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

1442 esac

1443 }

1444 invite() {

1445 email=`getpar email | tr '[A-Z]' '[a-z]'`

1446 case "$email" in

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

1448 *@*)

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

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

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

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

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

1454 elif sendinvitation $email; then

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

1456 else # Cannot be reached here

1457 repo="自動登録できない状況です。管理者に依頼してください。"

1458 fi ;;

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

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

1461 esac

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

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

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

1465 fi

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

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

1468 $layout/html.m4.html $layout/invite.m4.html

1469 }

1470 regist() {

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

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

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

1474 echo "bye bye" | html p

1475 reutrn

1476 fi

1477 email=`session=$1 getpar invite | tr '[A-Z]' '[a-z]'` # Ensure lower case

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

1479 cat<<EOF

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

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

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

1483 EOF

1484 return

1485 fi

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

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

1488 # Fake login password to wasureta

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

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

1491 wasureta $email

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

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

1494 addsession $1 # for removal after 1 minute

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

1496 $layout/login.m4.html

1497 return

1498 }

1499 group_safename() {

1500 # Convert $1 to safe group name

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

1502 }

1503 groupupdate() {

1504 gname=`getpar gname`

1505 qgname=`sqlquote "$gname"`

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

1507 # See ALSO same job in showgroup()

1508 newgname=`group_safename "$gname"`

1509 err newgname=$newgname

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

1511 err NewGNAME: gname=$newgname

1512 gname=$newgname

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

1514 replpar gname string "$gname"

1515 fi

1516 # Name confliction check

1517 parow=`getpar rowid`

1518 ## err parow=$parow

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

1520 query "BEGIN EXCLUSIVE;"

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

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

1523 if [ $count -gt 0 ]; then

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

1525 query "END;"

1526 return

1527 fi

1528 par2table $formdir/grp.def

1529 query "END TRANSACTION;"

1530 # Remove orphan

1531 : <<EOF

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

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

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

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

1536 EOF

1537 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1541 sql="delete from blog where id in

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

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

1544 query "$sql";

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

1546 return # and return

1547 fi

1548 fi

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

1550 fi

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

1552 grid=$(query $sql)

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

1554 grp $grid

1555 }

1556 groupclone() {

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

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

1559 case "$1" in

1560 */noteam)

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

1562 esac

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

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

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

1566 return

1567 fi

1568 if ! isgrpownerbygid "$user" "$rid"; then

1569 echo "グループ管理者のみがクローン可能です" | html p

1570 return

1571 fi

1572 i=0

1573 while true; do

1574 copy="-copy$i"

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

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

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

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

1579 i=$((i++))

1580 continue

1581 fi

1582 break

1583 done

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

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

1586 query<<-EOF

1587 BEGIN;

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

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

1590 SELECT $newqname, key, val

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

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

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

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

1595 -- Copy members and their configuration --

1596 REPLACE INTO grp_mem SELECT $newqname, user

1597 FROM grp_mem WHERE gname=$qgrp;

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

1599 FROM grp_mem_s WHERE gname=$qgrp;

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

1601 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1602 -- Copy administrators --

1603 REPLACE INTO grp_adm SELECT $newqname, user

1604 FROM grp_adm WHERE gname=$qgrp;

1605 COMMIT;

1606 EOF

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

1608 STOPCLONEMSG=1 groupconf "$newrowid"

1609 }

1610 groupman() {

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

1612

1613 GF_STAGE="grpconf"

1614 GF_STAGE=groupupdate

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

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

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

1618 -D_DUMPTABLE_="syscmd(cat)" \

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

1620 }

1621 userconf() {

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

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

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

1625 }

1626 groupconf() {

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

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

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

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

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

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

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

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

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

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

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

1638 <div>

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

1640 <table>

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

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

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

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

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

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

1647 </table>

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

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

1650 </div>

1651 EOF

1652 fi

1653 }

1654 mems() {

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

1656 kwd=`getpar kwd`

1657 listmember $kwd

1658 }

1659 grps() {

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

1661 kwd=`getpar kwd`

1662 listgroup $kwd \

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

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

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

1666 $layout/form+dump.m4.html

1667 }

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

1669 gpg=`getpar grp`

1670 grid=${1:-$gpg}

1671 grp=`getgroupbyid "$grid"`

1672 ## . ./s4-blog.sh

1673 jg=`getpar joingrp`

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

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

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

1677 fi

1678 htmlheader=$layout/html.m4.html

1679 showgroup "$grid"

1680 }

1681 sql4interestblogs() {

1682 cat<<EOF

1683 CREATE TEMPORARY VIEW interestblogs AS

1684 SELECT blog.rowid rid, id, author

1685 FROM blog

1686 NATURAL JOIN

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

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

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

1690 WHEN (SELECT user FROM grp_mem

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

1692 THEN 0

1693 ELSE 1

1694 END;

1695 EOF

1696 }

1697 listnewblogsql() { # $1=user

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

1699 WHERE user='$user'

1700 AND tblrowid IN

1701 ($blogreadflagrowid,

1702 $blogcutoffflagrowid)),

1703 "0");"`

1704 cat<<EOF

1705 `sql4interestblogs`

1706 WITH article_ctime as (

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

1708 FROM article join article_s s using(id)

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

1710 GROUP BY id

1711 ), blog_title_owner as (

1712 SELECT blg.rid brid, id,

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

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

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

1716 ), blogall as (

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

1718 ), news as (

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

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

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

1722 FROM blogall bl

1723 LEFT JOIN

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

1725 ON bl.brid=al.tblrowid

1726 WHERE atime < bl.ctime

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

1728 LIMIT 10

1729 ) SELECT brid LINK, "新着",

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

1731 ctime, title,

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

1733 FROM news;

1734 EOF

1735 }

1736

1737 search_form() {

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

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

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

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

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

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

1744 @week → 最近一週間

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

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

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

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

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

1750 auth=""

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

1752 case "$1" in

1753 author=*)

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

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

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

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

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

1759 ;;

1760 grp=*)

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

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

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

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

1765 ;;

1766 esac

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

1768 cat<<-EOF

1769 <div class="right">

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

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

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

1773

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

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

1776

1777 </form>

1778 </div>

1779 EOF

1780 }

1781

1782 imgsrc_cache() (

1783 # $1 = directory for cache'ing

1784 # $2 = table (user_m or grp_m)

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

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

1787 dir="$1" tbl="$2"

1788 keycond="$3"

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

1790 ORDER BY rowid DESC LIMIT 1"

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

1792 tmpf=$tmpd/imgsrc_cache.$$

1793 case "$4" in

1794 [Ss]) size=S ;;

1795 [Oo]) size=O ;;

1796 *) size=M ;;

1797 esac

1798 # ImageCache filename storing schema:

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

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

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

1802 valtype=`query "$sql0"`

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

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

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

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

1807 case "$filetype" in

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

1809 esac

1810 fi

1811 cacheimg_S=$dir/S_$filename

1812 cacheimg_M=$dir/M_$filename

1813 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1821 else

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

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

1824 fifo2=$fifo.2

1825 mkfifo $fifo $fifo2

1826 fmt=${filename##*.}

1827 ## [[ NOTE ]]

1828 ## a. convert oldimage newimage

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

1830 ## b is much smaller than a

1831 cat $tmpf | unhexize \

1832 | tee $fifo \

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

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

1835 | tee $fifo2 \

1836 | convert - "$cacheimg_M" &

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

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

1839 | convert - "$cacheimg_S" &

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

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

1842 echo '">'

1843 echo "$sum" > $sumfile

1844 fi

1845 ## Now preparing cache image, done.

1846 ## Store this information to DB

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

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

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

1850 query <<-EOF

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

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

1853 EOF

1854 )

1855

1856 showhome() {

1857 # $1=userRowIdToShow

1858 err showhome \$1=$1

1859 case "$1" in

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

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

1862 esac

1863 ## err ShowHome: uname=$uname

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

1865 gecos=`gecos "$uname"`

1866 ## err SH:gecos=$gecos

1867 GF_VIEWONLY=1

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

1869 search_form_args=""

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

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

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

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

1874 # Display folders

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

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

1877 and type like 'file:%';"

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

1879 nfile=`query "$sql"`

1880 # err nfile=$nfile

1881 if [ $nfile -gt 0 ]; then

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

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

1884 fi

1885 else

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

1887 GROUP BY user;"`

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

1889 search_form_args="author=$uname"

1890 fi

1891 . ./s4-blog.sh

1892

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

1894 search_form "$search_form_args" > $sf

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

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

1897 viewtable $formdir/user.def user $1

1898 echo "</div>"

1899 } > $pf

1900

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

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

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

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

1905 if true; then

1906 tbl=user_m

1907 enticond="name='$uname'"

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

1909 else

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

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

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

1913 echo '">'

1914 }

1915 fi > $imf

1916 fi

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

1918 val='$uname';"`

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

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

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

1922 listblog $uname > $bf

1923 ## unset ddd

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

1925

1926 hometail=$tmpd/tail.$$

1927 mkfifo $hometail

1928

1929 #Calling listgroupbytable, originally here

1930

1931 (

1932 # Display Most Recent Entry

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

1934 shortval=${shortval:-val}

1935

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

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

1938 ctime,

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

1940 (SELECT gecos FROM gecoses

1941 WHERE name=(SELECT val FROM blog_s

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

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

1944 FROM blog b

1945 JOIN

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

1947 FROM article a, article_s s

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

1949 GROUP BY blogid ORDER BY val DESC LIMIT 50

1950 ) m

1951 ON b.id=m.blogid;"

1952 # This should be as follows

1953 : <<EOF

1954 WITH arts AS(

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

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

1957 FROM article a NATURAL JOIN article_s s

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

1959 GROUP by s.id

1960 )

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

1962 FROM arts a0

1963 JOIN

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

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

1966 ORDER BY ctime DESC LIMIT 50;

1967 EOF

1968

1969 cat<<-EOF

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

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

1972 <div class="lcto">

1973 `DT_VIEW=replyblog dumptable html blog`

1974 </div>

1975 EOF

1976 unset DT_SQL

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

1978 # Display NEWS

1979 # 2016-06-26

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

1981 acclog blog $blogreadflagrowid

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

1983 fi

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

1985 sql=`listnewblogsql "$user"`

1986 # echo "$sql" > tmp/listnew

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

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

1989 cont=$((cont-1))

1990 err newcount=$cont

1991 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

1998 `cgi_submit '確定'`

1999 `cgi_hidden read yes`

2000 EOF

2001 echo "$new10 "

2002 echo "</div>"

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

2004 acclog blog $blogcutoffflagrowid # for speed

2005 fi

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

2007 : # DT_SQL=

2008 fi

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

2010 #

2011 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

2017 -D_NBLOG_="$nblog" \

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

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

2020 $layout/html.m4.html $layout/home.m4.html

2021

2022 # Record access log

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

2024 }

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

2026 contenttype; echo

2027 ## err commission: "$@"

2028 gname=`getgroupbyid $1`

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

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

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

2032 grp_reg_adm "$@"

2033 else

2034 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2035 fi

2036 }

2037 listgroupbytable() {

2038 # $1=deffile $2...=condition

2039 tagline=`grep :tag: $1`; shift

2040 and="${1:+and }" where=${1:+where }

2041 href="<a href=\"$myname?grp+"

2042 echo '<div class="listgroup">'

2043 NGsql="select distinct tag from\

2044 (select gname, max(case key when 'tag' then val end) as tag, \

2045 max(case key when 'ctime' then val end) as ctime\

2046 from grp_s group by gname order by ctime);"

2047 sql="select val from grp_s where key='tag' $and$* group by val;"

2048 ## err ListGRP: query sql="$sql"

2049 for tag in `query "$sql"`

2050 do

2051 ## err ListGrp: tag=$tag

2052 tn=${tagline%%=${tag}*}

2053 tn=${tn##*[ :]}

2054 sql="select rowid||':'||gname as 'グループ名',説明 from

2055 (select (select rowid from grp g where g.gname=grp_s.gname)

2056 as rowid,

2057 gname,

2058 max(case key when 'gecos' then val end) as '説明',

2059 max(case key when 'tag' then val end) as 'tag',

2060 max(case key when 'mtime' then val end) as mtime from grp_s

2061 $where$* group by gname having tag='$tag' order by mtime desc);"

2062 ## err PersonalGroupList= `echo $sql`

2063 echo "<h2>$tn</h2>"

2064 echo '<table class="b listgroup">'

2065 sq -header -html $db "$sql" \

2066 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2067 echo '</table>'

2068 done

2069 echo '</div>'

2070 }

2071 iconhref() (

2072 # $1=icon-file, $2=Href $3=title $4...=anchor

2073 data=`percenthex "$1"`

2074 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2075 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2076 href=$2; title=$3; shift 3

2077 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2078 )

2079 iconhref2() (

2080 # $1=icon-file, $2=Href $3=title $4...=anchor

2081 src=$1

2082 href=$2; title=$3; shift 3

2083 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2084 )

2085 listentry() (

2086 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2087 # Referring variable $iamowner=$grp to attach owner-request links

2088 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2089 cond='' hiddens=''

2090 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2091 if [ -z "$offset" ]; then

2092 offset=`getpar start`; offset=${offset%%[!0-9]*}

2093 offset=$((offset-1))

2094 fi

2095 offset=$((offset + 0)) # change to numeric forcibly

2096 [ $offset -lt 0 ] && offset=0

2097 limit=30

2098 dir=`getcachedir "$1"`

2099 if [ x"$1" = x"user" ]; then

2100 hrb="$myname?home"

2101 deficon=person-default.png

2102 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2103 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2104 gcs=gecos

2105 else # if group

2106 hrb="$myname?grp"

2107 deficon=group-default.png

2108 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2109 gcs=name

2110 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2111 if [ -n "$tagline" ]; then

2112 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2113 ## err tagconv=$tagconv

2114 fi

2115 fi

2116 if [ ! -d $dir ]; then

2117 mkdir -p $dir

2118 fi

2119 if [ ! -s $dir/$deficon ]; then

2120 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2121 fi

2122 if [ -n "$2" ]; then

2123 cond="where nick like '%$2%' or b.name like '%$2%'"

2124 fi

2125

2126 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2127 # grpは呼出し元の動的スコープ変数でよくないな...

2128 ##qgrp=`sqlquote $grp`

2129 getgrp="(select gname from grp where rowid=${rowid:--1})"

2130 sql="select a.rowid, a.$link,

2131 coalesce(b.$gcs, a.$nm) as nick,

2132 quote(a.$nm) as qname,

2133 (SELECT val FROM ${tbl}_s

2134 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2135 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2136 || case when a.$nm in (select gname from grp)

2137 then printf('(%d名)',

2138 (select count(user) from grp_mem where gname=a.$nm))

2139 else ' <'||a.$nm||'>'

2140 end

2141 as name,

2142 b.tag,

2143 case when a.$nm in (select user from grp_adm

2144 where gname=$getgrp) then '(管理者)'

2145 when '$user' in (select user from grp_adm where gname=a.$nm)

2146 then '(ADMIN)'

2147 when '$user' in (select user from grp_mem where gname=a.$nm)

2148 then '(Member)'

2149 when '$iamowner' = '' then ''

2150 else ',not='||a.rowid end as ownerlink,

2151 CASE '$entity'

2152 WHEN 'グループ'

2153 THEN coalesce(

2154 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2155 'open')

2156 ||

2157 CASE WHEN '$user'

2158 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2159 THEN ' ismember'

2160 ELSE ''

2161 END

2162 ELSE 'user'

2163 END regmode

2164 from $tbl a left join

2165 (select $nm as name,

2166 max(case key when 'gecos' then val end) as gecos,

2167 max(case key when 'tag' then val end) as tag,

2168 max(case key when 'mtime' then val end) as mtime,

2169 max(case key when 'wtime' then val end) as wtime,

2170 max(case key when 'login' then val end) as login

2171 from ${tbl}_s group by $nm)

2172 b on a.$nm=b.name $cond $3

2173 order by b.wtime desc, b.login desc,

2174 b.mtime desc, b.tag desc, a.rowid asc"

2175 # Give precedence to newer maintained groups (2016-09-24)

2176 # Note that mtime is stored only in grp_s.

2177 ## err LE:sql.1="$sql"

2178 total=`query "with x as ($sql) select count(*) from x;"`

2179 echo "${entity} 一覧" | html h2

2180 if [ $total -gt $limit ]; then

2181 echo '<div class="right">'

2182 cgi_form $stage <<EOF

2183 <label>次の語を含む${entity}で検索:

2184 `cgi_text kwd $kwd`</label>

2185 EOF

2186 echo '</div>'

2187 fi

2188 hiddens="$hiddens

2189 `cgi_hidden kwd \"$kwd\"`

2190 `cgi_hidden stage \"$stage\"`"

2191 cat<<EOF

2192 <form action="$myname" method="POST">

2193 <p>${total}件中の<input class="hidesub" type="text" name="start"

2194 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2195 <input type="submit" value="確定"></p>

2196 </form>

2197 EOF

2198 if [ $((offset+limit)) -lt $total ]; then

2199 nextbtn=$(

2200 cat<<EOF

2201 <div class="right clear"><form action="$myname" method="POST">

2202 `cgi_submit 次の${limit}件`

2203 $hiddens

2204 `cgi_hidden offset $((offset + limit))`</form></div>

2205 EOF

2206 )

2207 fi

2208 if [ $offset -gt 0 ]; then

2209 prevbtn=$(

2210 cat<<EOF

2211 <form action="$myname" method="POST">

2212 `cgi_submit 前の${limit}件`

2213 $hiddens

2214 `cgi_hidden offset $((offset - limit))`</form>

2215 EOF

2216 )

2217 fi

2218 pnbtn="$nextbtn$prevbtn"

2219 echo $pnbtn

2220

2221 ## err ListEntry: `echo "$sql"\;`

2222 # sq $db here??? 2016-11-28

2223 query "$sql limit $limit ${offset:+offset $offset};" \

2224 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2225 # err name=$name owner=$ownerp lnk=$lnk

2226 # err newlnk=$lnk regmode=$regmode

2227 icondir=$dir/$id

2228 # Pick up only last icon

2229 echo "<div class=\"iconlist xy$thumbxy $type\">

2230 <p class=\"tag _$tag\">$tag</p>" \

2231 | _m4 $tagconv

2232 if [ -n "$NOSPEEDUP" ]; then

2233 files=`getvalbyid $tbl profimg $id $icondir`

2234 if [ -n "$files" ]; then

2235 icon=`echo "$files"|tail -1`

2236 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2237 else

2238 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2239 fi

2240 elif [ -n "$icon" -a -s "$icon" ]; then

2241 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2242 else

2243 cond="$nm=$qname"

2244 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2245 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2246 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2247 # err "img=[$img]"

2248 if [ -n "$img" ]; then

2249 echo "<a href=\"$hrb+$lnk\">"

2250 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2251 echo "</a>"

2252 else

2253 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2254 fi

2255 fi

2256 echo "<br>$name${ownerp:+<br>$ownerp}"

2257 echo "</div>"

2258 done

2259 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2260 )

2261 listmember() {

2262 listentry user "$@"

2263 }

2264 listgroup() {

2265 listentry group "$@"

2266 }

2267 hexteams() { # $1=gname, $2(optional)=user

2268 cond=${2:+" AND user='$2'"}

2269 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2270 WHERE gname='$1' AND key='team'$cond;"

2271 }

2272 showgroup() { # $1=group-rowid

2273 if [ -z "$1" ]; then

2274 grid=`getpar grid`

2275 grid=${grid%%[!0-9]*}

2276 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2277 else

2278 grid=$1

2279 fi

2280 grp=`getgroupbyid $grid`

2281 qgrp=`sqlquote "$grp"`

2282 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2283 if isgroup "$grp"; then

2284 tf=$tmpd/title.$$

2285 sf=$tmpd/search.$$

2286 bodyclass=`query "SELECT val FROM grp_s

2287 WHERE gname=$qgrp AND key='regmode';"`

2288 if ismember "$user" "$grp"; then

2289 ismember="ismember"

2290 qgrp=`sqlquote "$grp"`

2291 bodyclass="$bodyclass${bodyclass:+ }ismember"

2292 else

2293 ismember="" # bodyclass="group"

2294 fi

2295 bodyclass="$bodyclass grouphome"

2296 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2297 echo "グループ $grp" > $tf

2298

2299 showgroupsub $formdir/grp.def "$grid" | \

2300 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2301 -D_FORM_="syscmd(\`cat')" \

2302 -D_BODYCLASS_="$bodyclass" \

2303 -D_DUMPTABLE_="" \

2304 $htmlheader $sf $layout/form+dump.m4.html

2305 # $htmlheader $layout/form+dump.m4.html

2306 # $htmlheader is defined in grp()

2307 else # if $grp is removed at par2table

2308 listgroup

2309 fi

2310 }

2311 showgroupsub() {

2312 # $1=def-file $2=group-rowid

2313 # Using $ismember

2314 rowid=$2

2315 grp=`getgroupbyid $2`

2316 qgrp=`sqlquote "$grp"`

2317 td=`getcachedir grp/"$2"`

2318 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2319 if [ -z "$rowid" ]; then

2320 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2321 #grp=`sq $db "select gname from grp where rowid=$grp"`

2322 echo "showgroupsub: invalid argument($1 $2)" | html p

2323 return

2324 fi

2325 val=`getvalbyid grp profimg $rowid $tmpd`

2326 enticond="gname=$qgrp"

2327 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2328 if [ -n "$img" ]; then

2329 cat<<-EOF

2330 <p class="groupimg">

2331 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2332 EOF

2333 fi

2334 echo "<div class=\"noprofimg\">"

2335 viewtable $1 grp $rowid

2336 echo "</div>"

2337 if isgrpowner "$user" "$grp"; then

2338 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2339 iamowner=$rowid

2340 colmd=" mode"

2341 fi

2342 if [ -n "$ismember" ]; then

2343 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2344 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2345 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2346 cat<<EOF

2347 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2348 <div class="fold clear">

2349 `cgi_checkbox send yes id="send"`<label

2350 for="send">グループ全員にメッセージ送信</label>

2351 <div>

2352 `cgi_textarea message "" "cols=60"`

2353 `cgi_submit 送信`

2354 `cgi_reset リセット`

2355 </div>

2356 `cgi_hidden grp $rowid`

2357 </div></form>

2358 EOF

2359 fi

2360 # 加入ボタン + 加入者リスト

2361 if [ -n "$ismember" ]; then

2362 ismem='checked' state="(参加中)"

2363 else

2364 nomem='checked' state="(現在非加入)"

2365 fi

2366 # このグループでの加入アドレス

2367 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2368 and key='email';"`

2369 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2370 ## and key='email';"

2371 ##err email=$eml

2372 cat <<EOF

2373 <div class="fold clear">

2374 `cgi_checkbox reg yes id="reg"`<label

2375 for="reg">自身の加入状態を操作する</label>$state

2376 <div>

2377 EOF

2378 cgi_form grp <<EOF

2379 <p>このグループに</p>

2380 <table class="b">

2381 <tr><th>メンバーとして</th><td>

2382 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2383 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2384 <tr><th>参加する場合のメイルアドレス<br>

2385 <small>(メインのアドレスとは違うものにする場合に記入<br>

2386 同じでよい場合は空欄に)</small></th>

2387 <td>`cgi_text email $eml`</td></tr>

2388 </table>

2389 `cgi_hidden grp $rowid`

2390 EOF

2391 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2392 echo "moderated (承認加入の)グループなので実際に参加できるのは

2393 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2394 fi

2395 echo '</div></div>'

2396 echo '<h2>話題一覧</h2>'

2397 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2398 cat<<-EOF

2399 <form class="summary" action="$myname" title="$thelp">

2400 `cgi_hidden owner "$grp"`

2401 `cgi_hidden kwd "@week"`

2402 `cgi_hidden stage searchart`

2403 `cgi_submit "一週間のまとめ"`

2404 </form>

2405 EOF

2406 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2407 colstate="state:稼動状態:frozen=rowclass=凍結"

2408 DT_CHLD=article:blogid \

2409 DT_QOWNER="$qgrp" \

2410 DT_VIEW=replyblog dumptable html blog \

2411 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2412

2413 getgname="(select gname from grp where rowid=$rowid)"

2414 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2415 cm="?commission+$rowid"

2416 thumbxy=50x50 listmember "" "$c" "$rowid" \

2417 |sed -e "s|$<br>$,not=$.*$|\1|" # 間違って押しやすい

2418 # team list

2419 hexteams=`hexteams "$grp"`

2420 if [ -n "$hexteams" ]; then

2421 echo "チーム一覧" | html h2

2422 echo '<div class="dumptable"><table class="b">'

2423 sq $db -html -header<<-EOF

2424 SELECT val TEAM,

2425 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2426 MEMBERS

2427 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2428 EOF

2429 echo '</table></div>'

2430 fi

2431 }

2432 grp_getbodyclass() {

2433 # Get css class name for document.

2434 # `moderated' for moderated groups

2435 # `ismember' for groups where user belongs

2436 # $1=GroupName (w/o quote)

2437 # $user=userNameCurrentlyLogin

2438 ## err grp_getbodyclass: 1="$1"

2439 qgrp=`sqlquote "$1"`

2440 query<<-EOF

2441 SELECT coalesce(

2442 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2443 'open')

2444 ||

2445 CASE WHEN '$user'

2446 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2447 THEN ' ismember'

2448 ELSE ''

2449 END;

2450 EOF

2451 }

2452 grpaction() { # $1=group-rowid

2453 err GRP_ACTION:IN

2454 grid=${1:-`getpar grp`}

2455 grp=`getgroupbyid "$grid"`

2456 if [ -z "$grp" ]; then

2457 echo "無効な指定です。" | html p; return

2458 fi

2459 if ! ismember $user "$grp"; then

2460 echo "加入者のみに許可された操作です。" | html p; return

2461 fi

2462 echo "グループ $grp 個別選択操作" \

2463 | _m4 -D_TITLE_="syscmd(\`cat')" \

2464 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2465 $layout/html.m4.html

2466

2467 isowner=""

2468 isgrpowner "$user" "$grp" && isowner="yes"

2469 usel=`getpar usel`

2470 if [ -n "$usel" ]; then

2471 uids=$(echo `echo $usel`|tr ' ' ',')

2472 ## err grpaction-1: grp=$grp, `echo $sql`

2473 text=`getpar text`

2474

2475 rm=`getpar rm` cfm=`getpar confirm`

2476 ## err rm=$rm cfm=$cfm

2477 if [ x"$rm" = x"yes" ]; then

2478 if [ "$isowner" ]; then

2479 if [ x"$rm$cfm" = x"yesyes" ]; then

2480 # Eliminate

2481 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2482 for tbl in grp_mem grp_mem_s grp_mem_m; do

2483 sql="delete from $tbl $cond;"

2484 # echo "sql=$sql"

2485 query "$sql"

2486 err rmGRPuser "$sql"

2487 done

2488 num=`query "select count(*) from user where rowid in ($uids);"`

2489 #err num=$num

2490 if [ 0$num -gt 0 ]; then

2491 sql="select coalesce(b.val,a.name) from user a left join \

2492 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2493 # err `echo "$sql"`

2494 html pre<<EOF

2495 以下の${num}名のグループ $grp 登録を解除しました。

2496 `query "$sql"`

2497 EOF

2498 fi

2499 else

2500 echo "確認のチェックがないのでやめておきます。" | html p

2501 return

2502 fi

2503 else # not Group Owner

2504 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2505 return

2506 fi

2507 cat<<EOF

2508

2509 EOF

2510 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2511 if [ -z "$text" ]; then # if msg is empty

2512 echo "なにかメッセージを..." | html p

2513 return 0

2514 fi

2515 gecos=`gecos $user`

2516 mkfrom=`getpar mkfrom`

2517 if [ x"$mkfrom" = x"yes" ]; then

2518 safegc=`echo "$gecos" | tr -d '<>@'`

2519 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2520 fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2521 mail_from="$safegc <$fromad>"

2522 else

2523 mail_from="$admin"

2524 fi

2525 MAIL_FROM=$mail_from \

2526 smail "`email4groupbyuid "$grp" $usel` $user" \

2527 "$gecos さんからのメッセージ" <<EOF

2528 $url

2529 のグループ「$grp」のメンバーである $gecos さんから、

2530 あなた宛へのメッセージです。

2531 ----------------------------------------------------------

2532 $text

2533 EOF

2534 if [ $? = 0 ]; then

2535 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2536 sql="select coalesce(b.val, a.name) from

2537 (select name from user where rowid in ($uids)) a

2538 left join user_s b on a.name=b.name and b.key='gecos';"

2539 html pre<<EOF

2540 `query "$sql"`

2541 (送信者である $gecos さんも含まれます)

2542 EOF

2543 err SendDone: `echo $sql`

2544 fi

2545 elif [ x"$rm" = x"commission" ]; then

2546 grp_reg_adm $grid $usel

2547 elif [ x"$rm" = x"addteam" ]; then

2548 team=`getpar team|sed "s/'/''/g"` # for single quotation

2549 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2550 if [ x"$team" != x"$newteam" ]; then

2551 echo "チーム名に使えない文字を除去しました" | html p

2552 team=newteam

2553 fi

2554 if [ -z "$team" -o x"$team" = x"なし" ]; then

2555 cat<<-EOF | html p

2556 有効なチーム名を入力してください。

2557 カンマだけ、「なし」という名前は使えません。

2558 EOF

2559 echo "有効なチーム名を入力してください。" | html p

2560 else

2561 grp_add_team $grid "$team" $usel

2562 fi

2563 elif [ x"$rm" = x"rmteam" ]; then

2564 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2565 rmteam=`getpar rmteam|sed "s/'/''/g"`

2566 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2567 gname='$grp' AND user='$user' AND key='team'\

2568 AND val='$rmteam';\"`" ]; then

2569 grp_rm_team $grid "$rmteam" $usel

2570 else

2571 echo "所属していないチームの除去操作はできません。"|html p

2572 fi

2573 else

2574 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2575 fi

2576 fi

2577 fi

2578 # POST count summary

2579 from=`getpar from`; to=`getpar to`

2580 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2581 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2582 fromtonote="<p>POST集計: $from_input - $to_input</p>"

2583 # New entry

2584 sql="WITH mems AS (

2585 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2586 ON gm.user=g.name

2587 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2588 ), target_article AS (

2589 SELECT id FROM article_s

2590 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2591 ), posts AS (

2592 SELECT author, count(author) post

2593 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2594 WHERE blogid IN (SELECT id FROM blog_s

2595 WHERE key='owner'

2596 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2597 AND key='text'

2598 GROUP BY author

2599 ), teams AS (

2600 SELECT user, group_concat(val, ', ') team

2601 FROM grp_mem_m

2602 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2603 AND key='team'

2604 GROUP BY user

2605 ), user_post AS (

2606 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2607 FROM mems m LEFT JOIN posts

2608 ON m.name=posts.author

2609 GROUP by m.rowid

2610 )

2611 SELECT

2612 CASE

2613 WHEN (SELECT user FROM grp_adm

2614 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2615 AND user=up.name) IS NOT NULL

2616 then 'k'

2617 ELSE ''

2618 END || rowid || ',' || gecos NAME,

2619 post POST, team TEAM

2620 FROM user_post up LEFT JOIN teams t

2621 ON up.name=t.user

2622 ORDER BY gecos;"

2623 ## err grpaction: "`echo \"$sql\"`"

2624 tf=$tmpd/title.$$

2625 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2626 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2627 for=\"cmadmin\">グループ管理者委任</label>

2628 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2629 </p></div>"

2630 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2631 for=\"conf\">グループ登録解除</label>

2632 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2633 <p>この操作による通知は本人に行きません。

2634 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2635 </div>"

2636 # Get team list to which current user belongs into $hexteams

2637 myhexteams=$(hexteams "$grp" "$user")

2638 allhexteams=$(hexteams "$grp")

2639 if [ -n "$myhexteams" ]; then

2640 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2641 for=\"cmrmteam\">チーム属性除去</label>

2642 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2643 を除去します: `cgi_checkbox teamconfirm yes` 確認

2644 <p>この操作による通知は本人に行きません。

2645 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2646 </div>

2647 "

2648 fi

2649 b1='<label> <input type="checkbox" name="usel" value="'

2650 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2651 #b2='"> <span>' b3='</span></label>'

2652 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2653 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2654 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2655 cgi_form grpaction<<EOF \

2656 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2657 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2658 | _m4 -D_TITLE_="spaste(\`$tf')" \

2659 -D_SUBTITLE_="チェック後操作ボタン" \

2660 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2661 $layout/form+dump.m4.html

2662 <p>下でチェックした人を対象として:</p>

2663 <div class="foldtabs">

2664 `cgi_radio rm addteam 'id="cmteam"'`<label

2665 for="cmteam">同じチーム属性を付与</label>

2666 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2667 `cgi_datalist_h teams $allhexteams`

2668 </div>

2669 ${rmteammsg}

2670 `cgi_radio rm send id="sendmsg"`<label

2671 for="sendmsg">メッセージ送信</label>

2672 <div>

2673 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2674 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2675 `cgi_textarea text "" cols=40`

2676 </div>

2677 ${isowner:+$cmmsg$excmsg}

2678 `cgi_radio rm close id="x"`<label for="x">×</label>

2679 </div>

2680 <h4>$grp 参加者一覧</h4>$fromtonote

2681 <table class="td2r">

2682 `sq $db -header -html "$sql"`

2683 </table>

2684 `cgi_hidden grp $grid`

2685 EOF

2686 }

2687 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2688 # Create TEMPORARY VIEW

2689 query<<EOF

2690 CREATE TEMPORARY VIEW writeusers AS

2691 SELECT DISTINCT author FROM article

2692 WHERE id in (

2693 select id from article where blogid=(select id from blog where rowid=$1)

2694 );

2695 CREATE TEMPORARY VIEW movablegroups AS

2696 SELECT g.rowid growid , g.gname

2697 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2698 ON grp.gname=gm.gname -- そのユーザが属している

2699 AND user='$user') g -- グループに絞る

2700 WHERE (SELECT author FROM writeusers

2701 EXCEPT

2702 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2703 IS NULL;

2704 $2

2705 EOF

2706 }

2707 sql4readableblogs() {

2708 # Create view of blogs that can be readable to $user

2709 # Blog is readable when:

2710 # 1: blog owner is an user

2711 # 2: else, 2.1: owner-group where the $user belongs

2712 # 2.2: else, owner-group is not moderated

2713 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2714 cat<<EOF ## | tee tmp/sql.out

2715 CREATE TEMPORARY VIEW readableblogs AS

2716 SELECT blog.rowid rid, id, author

2717 FROM blog

2718 NATURAL JOIN

2719 (SELECT id,

2720 max(CASE key WHEN 'owner' THEN val END) owner,

2721 max(CASE key WHEN 'mode' THEN val END) mode

2722 FROM blog_s GROUP by id) bs

2723 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2724 THEN 1 -- blog owner is an user, READABLE

2725 WHEN (SELECT val FROM grp_s

2726 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2727 AND

2728 (SELECT user FROM grp_mem

2729 WHERE gname=bs.owner AND user='$user') IS NULL

2730 THEN 0

2731 WHEN mode IN ('quiz', 'enquete')

2732 THEN 0 -- "quiz" mode blog cannot be searched

2733 ELSE 1

2734 END;

2735 EOF

2736 }

2737 editheading() { # $1=rowid-of-heading

2738 rowid=${1%%[!A-Z0-9a-z_]*}

2739 if [ -z "$rowid" ]; then

2740 echo "話題番号が未指定です。" | html p

2741 return

2742 fi

2743 owner=`getvalbyid blog owner $rowid`

2744 title=`getvalbyid blog title $rowid`

2745 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2746 | _m4 -D_TITLE_="修正" \

2747 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2748 -D_BLOGS_="" -D_DUMPTABLE_="" \

2749 -D_FORM_="syscmd(\`cat')" \

2750 $layout/html.m4.html $layout/form+dump.m4.html

2751 # Move to group

2752 if isuser "$owner"; then

2753 crview4article $rowid

2754 n=`query "SELECT count(*) FROM writeusers;"`

2755 ## err N=$n

2756 if [ $((n)) -gt 0 ]; then

2757 ## err ROWID=$rowid

2758 sql="SELECT growid || ':' || gname FROM movablegroups;"

2759 cat<<-EOF

2760 <div class="fold">

2761 `cgi_checkbox mv send id="mv"`<label

2762 for="mv">この話題をグループ所有に移動する</label>

2763 <div>

2764 <form action="?mvart" method="POST" enctype="multipart/form-data">

2765 移動先グループ:

2766 <select name="mv2grp">

2767 EOF

2768 query ".mode html"

2769 query<<-EOF |

2770 $sql

2771 .mode list

2772 EOF

2773 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2774 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2775 cat<<-EOF

2776 </select>

2777 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2778 そのグループに加入しているものに限られます)</p>

2779 <p>`cgi_checkbox cfm yes`<label>確認

2780 (この操作は元に戻すことができません)</label></p>

2781 `cgi_hidden blogrowid $rowid`

2782 `cgi_submit 移動`

2783 `cgi_reset Reset`

2784 </form>

2785 </div>

2786 </div>

2787 EOF

2788 fi

2789 # end of isuser "$owner"

2790 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2791 [ -n "$hexteams" ];}; then

2792 none="`echo なし|hexize`"

2793 cat<<-EOF

2794 <div class="fold">

2795 `cgi_checkbox mv2team send id="mv2team"`<label

2796 for="mv2team">この話題を以下のチームのものにする</label>

2797 <div><p>現在の所属チーム設定:

2798 `query "SELECT

2799 coalesce((SELECT val FROM blog_s

2800 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2801 AND key='team'),

2802 ':なし');"`</p>

2803 <form action="?mvart" method="POST" enctype="multipart/form-data">

2804 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2805 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2806 `cgi_hidden blogrowid $rowid`<br>

2807 `cgi_submit 移動`

2808 `cgi_reset Reset`

2809 </form></div></div>

2810 EOF

2811 fi

2812 }

2813 mvart() { # move diary to some group or team

2814 # or move blog of group to team which belong to the group

2815 blogrowid=`getpar blogrowid`

2816 cfm=`getpar cfm`

2817 ##### echo move blog:$blogrowid to $mv2grp | html p

2818 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2819 . ./s4-blog.sh

2820 if [ -z "$blogrowid" ]; then

2821 echo "無効な指定です(mvart)。" | html p

2822 return

2823 elif [ x"$cfm" != x"yes" ]; then

2824 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2825 elif { mv2grp=`getpar mv2grp`

2826 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2827 [ -n "$mv2grp" ]; }; then

2828 crview4article $blogrowid

2829 ########## TRANSACTION BEGIN

2830 query "BEGIN;"

2831 n=`query "SELECT count(*) FROM writeusers;"`

2832 ## err Nwriteuser=$n

2833 if [ $((n)) -gt 0 ]; then

2834 query<<-EOF

2835 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2836 WHERE key='owner'

2837 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2838 AND $mv2grp IN (SELECT growid FROM movablegroups);

2839 EOF

2840 fi

2841 query "END;"

2842 ########## TRANSACTION END

2843 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2844 [ -n "$mv2team" ];}; then

2845 # blog owner can move it to ANY team

2846 case "$mv2team" in

2847 'なし')

2848 cat<<-EOF

2849 DELETE FROM blog_s

2850 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2851 AND key='team';

2852 EOF

2853 ;;

2854 "") ;;

2855 *)cat<<-EOF

2856 BEGIN;

2857 REPLACE INTO blog_s(id, key, val)

2858 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2859 'team', '$mv2team');

2860 REPLACE INTO blog_s(id, key, val)

2861 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2862 'notify', 'all'); -- Change notify to all

2863 END;

2864 EOF

2865 esac | query

2866 fi

2867 blog_reply $blogrowid

2868 echo yes | html p

2869 }

2870 editart() { # $1=article-rowid $2=blogrowid

2871 rowid=${1%%[!A-Z0-9a-z_]*}

2872 blogrowid=${2%%[!A-Z0-9a-z_]*}

2873 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2874 echo "表示する記事番号が未指定です。" | html p

2875 return

2876 fi

2877 owner=`getvalbyid blog owner $blogrowid`

2878 title=`getvalbyid blog title $blogrowid`

2879 author=`getvalbyid article author $rowid`

2880 ## err EDITart: owner=$owner, author=$author

2881 if isgrpowner "$user" "$owner"; then

2882 : EDIT OK

2883 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2884 echo "本人か所有者しか編集できません." | html p

2885 return

2886 fi

2887 aid=`query "select id from article where rowid=$rowid;"`

2888 tmpout=$tmpd/editart.$$.out

2889 GF_ACTION="?replyblog+$blogrowid#$aid" \

2890 edittable $formdir/article.def article $rowid \

2891 > $tmpout

2892 rm -f /tmp/editart.out

2893 # Cannot use pipelining to m4 with genform() because of stdin stack

2894 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2895 -D_FORM_="syscmd(cat $tmpout)" \

2896 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2897 -D_BLOGS_= -D_DUMPTABLE_= \

2898 $layout/html.m4.html $layout/form+dump.m4.html

2899 }

2900 send2mem() {

2901 rowid=`getpar grp`

2902 rowid=${rowid%%[!0-9]*} # Cleaning

2903 if [ -z "$rowid" ]; then

2904 echo "グループが未指定です。" | html p

2905 return

2906 fi

2907 message=`getpar message`

2908 if [ -z "$message" ]; then

2909 echo "文章を入れてください。" | html p

2910 return

2911 fi

2912 grp=`getgroupbyid $rowid`

2913 members=`collectemail $grp`

2914 # smail rcpt subj (file)

2915 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$admin>" \

2916 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2917 $urlbase?grp+$rowid

2918 グループ $grp に所属する

2919 `gecos $user` さんよりメッセージ:

2920

2921 $message

2922 EOF

2923 cat<<EOF

2924 <p>以下のユーザに送信しました。</p>

2925 <pre>

2926 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

2927 </pre>

2928 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2929 EOF

2930 }

2931 joingrpadmit() {

2932 # $1=yes/no $2=session-key

2933 if [ -z "$2" ]; then

2934 echo "bye bye" | html p; return

2935 fi

2936 t_usr=`session=$2 getpar user`

2937 t_grp=`session=$2 getpar group`

2938 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2939 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2940 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2941 echo "無効な加入依頼です。" | html p

2942 echo "有効期限が切れたか、

2943 他の管理者がいる場合は処理済みの可能性があります。" | html p

2944 return

2945 fi

2946 if ! isgrpowner "$user" "$t_grp"; then

2947 echo "グループ管理者のみの機能です。" | html p; return

2948 fi

2949 case $1 in

2950 yes) joingrp "$t_grp" "$t_usr" yes ;;

2951 no) joingrp "$t_grp" "$t_usr" no ;;

2952 *)

2953 echo "無効な指定です($1)。" | html p

2954 return ;;

2955 esac

2956 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

2957 rcpts="`getgroupadminmails $t_grp` $user"

2958 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2959 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2960 に

2961 $t_usr

2962 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2963 しました。"

2964 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2965 query "delete from session where id='$2';"

2966 echo "$body" | html p

2967 }

2968

2969 joingrprequest() {

2970 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2971 jss="joingrp-`date +%s`-`genrandom 12`"

2972 addsession $jss +${memoplimitdays}days

2973 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

2974 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

2975 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2976 $url

2977 $user さんから

2978 グループ $1

2979 に加入依頼がありました。

2980

2981 承認する:

2982 $urlbase?joingrpadmit+yes+$jss

2983

2984 白紙に戻す:

2985 $urlbase?joingrpadmit+no+$jss

2986 EOF

2987 echo "管理者に加入依頼を出しました。

2988 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2989 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2990 }

2991 joingrp() {

2992 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2993 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2994 if isgrpowner "$user" "$1"; then

2995 isowner="yes"

2996 elif [ -n "$5" ]; then

2997 isowner="yes"

2998 else

2999 isowner=""

3000 fi

3001 ## err jg:isgrpowner: isowner="$isowner"

3002 if [ -n "$isowner" ]; then

3003 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3004 elif [ x"$2" != x"$user" ]; then # if user is not login user

3005 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3006 return

3007 elif [ x"$3" = x"no" ]; then

3008 : # Do not pursue those who leave

3009 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3010 : # Member can change own email address for the joining moderated group

3011 else # adding user is $user itself

3012 case `getgroupattr $1 regmode` in

3013 moderated)

3014 joingrprequest "$@" # Request only

3015 return

3016 ;;

3017 *)

3018 ;;

3019 esac

3020 fi

3021 qgname=`sqlquote "$1"`

3022 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3023 cond="where gname=$qgname and user='$2'"

3024 if [ x"$3" = x"yes" ]; then

3025 query "replace into grp_mem values($qgname, '$2');"

3026 # Notify joingrp to admin

3027 action="に加入しました。"

3028 if [ -n "$4" ]; then

3029 if msg=`emaildomaincheck "$4"`; then

3030 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3031 'string', '$4', NULL);"

3032 else

3033 echo $msg

3034 fi

3035 else

3036 query "delete from grp_mem_s $cond and key='email';"

3037 fi

3038 if [ -n "$5" ]; then # as ADMIN

3039 # Coming here means newly created group

3040 sql="select case\

3041 when (select count(*) from grp_mem where gname=$qgname)=1\

3042 then (select user from grp_mem\

3043 where gname=$qgname and user='$user')\

3044 else '' end; "

3045 err NewGrpChk: $sql

3046 if [ -n "`query \"$sql\"`" ]; then

3047 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3048 query "replace into grp_adm values($qgname, '$user');"

3049 fi

3050 fi

3051 else

3052 query "begin;

3053 delete from grp_mem $cond;

3054 delete from grp_mem_s $cond;

3055 delete from grp_mem_m $cond;

3056 delete from grp_adm $cond;

3057 delete from grp_adm_s $cond;

3058 delete from grp_adm_m $cond;

3059 end;"

3060 action="から脱退しました。"

3061 fi

3062 smail "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3063 $url?grp+$grid

3064 $user (`gecos $user`)さんが

3065 グループ $1

3066 $action

3067 EOF

3068 }

3069 grp_add_team() (

3070 # $1=grp-rowid $2=team $3...=user-rowid(s)

3071 grp=`getgroupbyid $1`

3072 team=$2; shift; shift

3073 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3074 { echo "BEGIN;"

3075 for user; do

3076 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3077 '$grp',\

3078 (SELECT name FROM user WHERE rowid=$user),\

3079 'team', 'string', '$team');"

3080 done

3081 echo "END;"

3082 } | query

3083 )

3084 grp_rm_team() (

3085 # $1=grp-rowid $2=team $3...=user-rowid(s)

3086 grid=$1

3087 qgrp=$(sqlquote "`getgroupbyid $grid`")

3088 team=$2; shift; shift

3089 [ -z "$grid" -o -z "$team" ] && return

3090 { echo "BEGIN;"

3091 for user; do

3092 echo "DELETE FROM grp_mem_m\

3093 WHERE gname=$qgrp \

3094 AND user=(SELECT name FROM user WHERE rowid=$user)\

3095 AND key='team' AND val='$team';"

3096 done

3097 cat<<-EOF

3098 DELETE FROM blog_s

3099 WHERE rowid=(

3100 SELECT rowid

3101 FROM blog_s a

3102 WHERE key='team'

3103 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3104 AND NOT EXISTS (SELECT * FROM grp_mem_m

3105 WHERE key='team' AND val=a.val -- a.val=team

3106 AND gname = (SELECT val FROM blog_s b

3107 WHERE a.id=b.id AND key='owner')

3108 ));

3109 EOF

3110

3111 echo "END;"

3112 } | query

3113 )

3114 grp_reg_adm() {

3115 # $1=grp-rowid $2...=user-rowid

3116 grid=$1

3117 grp=`getgroupbyid "$1"`

3118 if [ -z "$grp" ]; then

3119 echo "無効なグループIDです" | html p; return

3120 fi

3121 if ! isgrpowner "$user" "$grp"; then

3122 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3123 fi

3124 shift

3125 for urid; do

3126 newadm=`query "select name from user where rowid=$urid;"`

3127 if [ -z "$newadm" ]; then

3128 echo "指定ユーザIDがおかしいようです。" | html p; return

3129 fi

3130 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3131 err ismember $newadm $grp

3132 if ismember $newadm "$grp"; then

3133 # OK, go ahead

3134 getgname="(select gname from grp where rowid=$grid)"

3135 query "replace into grp_adm values($getgname, '$newadm');"

3136 # confirm insertion

3137 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3138 if [ -n "`query \"$sql;\"`" ]; then

3139 echo "追加完了: $newadm" | html p

3140 else

3141 echo "追加失敗($1 $urid)" | html p

3142 fi

3143 fi

3144 showgroup $grid

3145 done

3146 }

3147 dt_rowhack() {

3148 # From: <TR>

3149 # ....

3150 # <TD>rowclass=foo</TD>

3151 # </TR>

3152 # To: <TR class="foo">....<TD>foo</TD></TR>

3153 sed -e '

3154 /^<TR>/ {

3155 :loop

3156 s/\n//

3157 N

3158 /<\/TR>/ {

3159 s/\n//

3160 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3161 n

3162 }

3163 $q

3164 b loop

3165 }'

3166 }

3167 dumptable() {

3168 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3169 # textのフィールドだけ全てダンプにしたほうがいいか

3170 # $DT_VIEW sets link

3171 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3172 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3173 VIEW=${DT_VIEW-replyblog}

3174 if [ -n "$VIEW" ]; then

3175 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3176 fi

3177 sqlfile=$tmpd/dump.sql

3178 : > $sqlfile # ensure to be empty

3179 printf '.mode html\n.header 1\n' > $sqlfile

3180 # $DT_CHLD=ChildTable:BindColumn

3181 if [ -n "$DT_CHLD" ]; then

3182 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3183 cat<<-EOF >> $sqlfile

3184 -- presql

3185 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3186 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3187 EOF

3188 # Speed up counting of new articles

3189 cat<<-EOF >> $sqlfile

3190 -- presql2

3191 DROP TABLE IF EXISTS _counts;

3192 CREATE TEMPORARY TABLE _counts AS

3193 SELECT $_i, count($_i) cnt

3194 FROM $_t GROUP BY $_i;

3195 /* Prepare NEW count table */

3196 CREATE TEMPORARY TABLE _target AS

3197 SELECT b.rowid trowid, b.id

3198 FROM "$2" b JOIN "$2_s" s

3199 ON b.id=s.id AND s.key='owner'

3200 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3201

3202 DROP TABLE IF EXISTS _children;

3203 CREATE TEMPORARY TABLE _children AS

3204 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3205 FROM (SELECT t.trowid, t.id $_i, a.id

3206 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3207 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3208

3209 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3210 DROP TABLE IF EXISTS _news;

3211 DROP VIEW IF EXISTS _news;

3212

3213 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3214 -- INSERT INTO _news

3215 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3216 CREATE TEMPORARY VIEW _news AS

3217 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3218 FROM (SELECT DISTINCT id FROM _target)

3219 a LEFT JOIN

3220 (SELECT $_i, count(ctime) newcnt

3221 FROM _children x

3222 WHERE ctime > coalesce((SELECT time from myacclog

3223 WHERE tblrowid=x.trowid),

3224 '1970-01-01')

3225 GROUP BY $_i) b

3226 ON a.id=b.$_i;

3227 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3228 EOF

3229 # REMOVE next line until 2019/5/1

3230 cntall="(select count($_i) from $_t where $_i=a.id)"

3231 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3232 # REMOVE next assignment until 2019/5/1

3233 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3234 and id in (select id from $_t where $_i=a.id) \

3235 and val > coalesce((select time from myacclog where \

3236 tblrowid=a.rowid),\

3237 '1970-01-01'))"

3238 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3239 cnt="$cntnew as '新着', $cntall as '総数',"

3240 dt_class=" td2r td3r dumpblogs"

3241 fi

3242 # Construct join expression

3243 eav="" scols=""

3244 pk=`gettblpkey $2`

3245 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3246 substr=${substr:-%s}

3247 for col in ${3:-`gettbl_s_cols $2`}; do

3248 valvar=val

3249 case $col in

3250 gecos) scols="$scols${scols:+, }${col#}"

3251 continue ;; # built-in column name

3252 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3253 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3254 case "$as" in

3255 *:*=*) cnd=${as#*:}

3256 h=${cnd%%=*} v=${cnd#*=}

3257 h=`sqlquotestr "$h"`

3258 v=`sqlquotestr "$v"`

3259 valvar="CASE val WHEN $h THEN $v END"

3260 as=${as%%:*} ;;

3261 esac

3262 ;;

3263 *) as=${col} ;;

3264 esac

3265 ss=`printf "$substr" "$valvar"`

3266 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3267 scols="$scols${scols:+, }b.$as"

3268 done

3269 #case author when '$user' then a.rowid else '---' end as ID,

3270 if [ -n "$DT_SQL" ]; then

3271 echo "$DT_SQL"

3272 else

3273 cat<<-EOF

3274 SELECT a.rowid as LINK, $cnt $scols

3275 FROM $2 a LEFT JOIN

3276 (SELECT $pk,$eav,

3277 max(CASE key

3278 WHEN 'owner'

3279 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3280 as gecos

3281 FROM ${2}_s c GROUP BY $pk)

3282 b ON a.$pk=b.$pk $4;

3283 EOF

3284 fi >> $sqlfile

3285 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3286 sqlog<<-EOF

3287 *** SQL-file: $sqlfile ***

3288 `cat $sqlfile`

3289 EOF

3290 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3291 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3292 cat $sqlfile >> tmp/sql

3293 # query ".read $sqlfile" > $tmpd/foo

3294 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3295 cp $tmpd/foo tmp/

3296 err "----- `gdate +%FT%T.%3N` ------------555555"

3297 ## $ddd LINE exists at the end of this function

3298 fi

3299 printf '.mode list\n.header 0\n' >> $sqlfile

3300 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3301 <div>

3302 <div class="dumptable">

3303 <table class="b$dt_class">

3304 `query ".read $sqlfile"`

3305 </table>

3306 </div>

3307 </div>

3308 EOF

3309 ### `query ".read $sqlfile"`

3310 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3311

3312 ## REMOVE THIS!

3313 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3314 }

3315

3316 par2table() (

3317 # copy current parameters of par into destination table

3318 # $1=definition-file

3319 # Using $user and $session

3320 # Return value:

3321 # 0: Stored successfully

3322 # 1: Insufficient fillings

3323 # 2: No permission to modify the record

3324 # 3: Invalid rowid

3325 # 4: SUCCESS to delete

3326 # 5: Stop deletion for lack of confirm check

3327 # 6: Password length too short

3328 # 7: Password mismatch

3329 # 8: Old password incorrect

3330 rowid=`getpar rowid`

3331 if [ ! -e $1 ]; then

3332 echo "テーブル定義ファイルが見付かりません" | html p

3333 exit 1

3334 fi

3335 tbl=${1%.def}

3336 tbl=${tbl##*/}

3337 if [ -n "$rowid" ]; then # Modify existing entry

3338 if [ x"$tbl" = x"user" ]; then

3339 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3340 elif [ x"$tbl" = x"grp" ]; then

3341 sql="select gname from $tbl where rowid=$rowid;"

3342 ##err p2t:grp:q $sql

3343 isgrpowner "$user" "`query $sql`" && rowowner=$user

3344 elif [ x"$tbl" = x"blog" ]; then

3345 # Check if owner in blog_s

3346 blogowner=`getvalbyid blog owner "$rowid"`

3347 if isgrpowner "$user" "$blogowner"; then

3348 rowowner=$user

3349 else

3350 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3351 fi

3352 else

3353 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3354 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3355 fi

3356 ### err rowowner=$rowowner

3357 if [ x"$user" != x"$rowowner" ]; then

3358 echo "他人のレコードはいじれないの" | html p

3359 return 2

3360 elif [ -z "$rowowner" ]; then

3361 echo "指定したレコードはないみたい" | html p

3362 return 3

3363 fi

3364 rm=`getpar rm` cfm=`getpar confirm`

3365 # Editing existent entry

3366 if [ x"$rm" = x"yes" ]; then

3367 if [ x"$rm$cfm" = x"yesyes" ]; then

3368 query "delete from $tbl where rowid=$rowid;"

3369 return 4

3370 else

3371 echo "消去確認のチェックがないので消さなかったの..." | html p

3372 return 5

3373 fi

3374 fi

3375 fi

3376

3377 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3378 if [ -n "$rowid" ]; then

3379 # Update of existing record

3380 for col in `gettblcols $tbl`; do

3381 val=`getparquote $col`

3382 [ -z "$val" ] && continue

3383 ## err query "update $tbl set $col=$val where rowid=$rowid"

3384 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3385 sql="update $tbl set $col=$val where rowid=$rowid;"

3386 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3387 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3388 ## User name cannot be changed with interface provided with this

3389 ## script. But we offer the trigger to change owner user

3390 ## of blog_s table.

3391 #err "select quote($col) from $tbl where rowid=$rowid;"

3392 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3393 cat<<-EOF | query

3394 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3395 -- should use EXCLUSIVE transaction outside of this.

3396 SAVEPOINT par2table;

3397 $sql

3398 update blog_s set val=$val

3399 where key='owner' and val=$old;

3400 RELEASE SAVEPOINT par2table;

3401 EOF

3402 ## XX: DIRTY Hack Ends here

3403 ## We should keep blog's owner as a single column which has

3404 ## foreign key constraint with primary key of grp/user.

3405 else

3406 query "$sql"

3407 fi

3408 done

3409 # Then, set up $pval for further insertion of tbl_s and tbl_m

3410 for col in `gettblpkey $tbl`; do

3411 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3412 pval="$pval${pval:+, }\"$val\""

3413 done

3414 else

3415 # New entry

3416 # Generate values() for primary keys

3417 for col in `gettblpkey $tbl`; do

3418 # Genuine primary keys for _m and _s

3419 val=`getvalquote $tbl $col`

3420 [ -z "$val" ] && continue

3421 pval="$pval${pval:+, }$val"

3422 done

3423 ##err pval=$pval

3424 for col in `gettblfkey $tbl`; do

3425 # args for values() to insertion into master table

3426 val=`getvalquote $tbl $col`

3427 [ -z "$val" ] && continue

3428 formaster=$formaster"${formaster:+, }$val"

3429 done

3430 formaster="$pval${formaster:+, }$formaster"

3431 ## err formaster=$formaster

3432 if [ -z "$formaster" ]; then

3433 echo "項目を全て埋めてください" | html pre

3434 return 1

3435 fi

3436 ## err "replace into $tbl values($formaster);"

3437 query "replace into $tbl values($formaster);"

3438 ## Insertion to master table, done

3439 fi

3440

3441 for kt in s m; do

3442 tb2=${tbl}_$kt

3443 for col in `gettbl_${kt}_cols $tbl`; do

3444 ptype=`getpartype $col "limit 1"`

3445

3446 # First, check update of existing entries in _m

3447 if [ $kt = m ]; then

3448 # sessID|address.1.22|string|Somewhere-x.y.z

3449 sql=""

3450 ##err dots from query "select var from par where var like '$col.%';"

3451 for v in `query "select var from par where var like '$col.%';"`; do

3452 # v=address.1.22

3453 st_rowid=${v##*.}

3454 origcol=${v%%.*} # original column derived from

3455 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3456 ##case `getpartype $v` in

3457 ## err CASE `gettbl_coltype $tbl/$origcol` in

3458 ## err edit flag = `getpar action.$v`

3459 case `getpar action.$v` in

3460 rm)

3461 if [ x`getpar confirm.$v` = x"yes" ]; then

3462 newsql="delete from $tb2"

3463 else

3464 echo "削除確認未チェック" | html p

3465 fi ;;

3466 edit)

3467 case `gettbl_coltype $tbl/$origcol` in

3468 image|document|binary)

3469 file=$tmpd/`getparfilename $v`

3470 ## err type=file=$file

3471 [ -z "$file" ] && continue

3472 bn=`sqlquotestr "${file##*/}"`

3473 bin="X'"$(hexize "$file")"'"

3474 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3475 type=\"file:$ct\"

3476 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3477 cachedir=`getcachedir "$tbl/$rowid"`

3478 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3479 rm -rf $cachedir

3480 ;;

3481 *)

3482 newsql="update $tb2 set val=(select val from par where var \

3483 like '$col.%.$st_rowid')"

3484 ;;

3485 esac

3486 ;;

3487 *) # maybe "keep", do not modify value

3488 continue

3489 ;;

3490 esac

3491 # err newsql=$newsql

3492 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3493 done

3494

3495 if [ x"$bin" = x"NULL" ]; then

3496 ## err repl:normal sql=`echo $sql`

3497 query "$sql

3498 delete from $tb2 where type='string' and val='';"

3499 ## err repl:normal done

3500 else

3501 sqlfile="$tmpd/sqlf.$$"

3502 echo "$sql" > $sqlfile

3503 ## err repl:sqlfile=`ls -lF $sqlfile`

3504 query ".read $sqlfile"

3505 ## err repl:done

3506 fi

3507 # Rest of kt==m: set multiple mode

3508 nr=`getparcount $col`

3509 else

3510 nr=1 # for kt==s, number of records is 1

3511 fi

3512

3513 i=0

3514 while [ $i -lt $nr ]; do

3515 limit="limit 1 offset $i"

3516 i=$((i+1)) # increase beforehand against continue

3517 val=`getvalquote $tbl $col "$limit"`

3518 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3519 ## err $col=$val

3520 bin=NULL

3521 ## err partype$col=`getpartype $col "$limit"`

3522 case $ptype in

3523 file) file=$tmpd/`getparfilename $col "$limit"`

3524 ## err parfile-$col=$file

3525 [ -z "$file" ] && continue

3526 bin="X'"$(hexize "$file")"'"

3527 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3528 type=\"file:$ct\" ;;

3529 "*"*) continue ;; # foreign table

3530 *) type=\"string\" ;;

3531 esac

3532 case `gettbl_coltype $tbl/$col` in

3533 password) # special care for password

3534 # name={password,pswd1,pswd2}

3535 p1=`getpar pswd1 "$limit"`

3536 if [ -z "$p1" ]; then

3537 continue # SKIP password setting, if p1 is empty

3538 else

3539 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3540 ## err pswd=$pswd

3541 if pwcheck "$pswd"; then

3542 if [ x"$p1" = x"$p2" ]; then

3543 case "$p1" in

3544 ??????????*) ;;

3545 *) echo "パスワードは10字以上にしてください。" | html p

3546 return 6;;

3547 esac

3548 val="\"`echo $p1|mypwhash`\""

3549 else

3550 echo "2つの新パスワード不一致" | html p

3551 return 7

3552 fi

3553 else

3554 echo "旧パスワード違います" | html p

3555 return 8

3556 fi

3557 fi

3558 ;;

3559 esac

3560 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3561 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3562 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3563 if [ x"$bin" = x"NULL" ]; then

3564 ## err Normal-query: `echo $sql`

3565 query "$sql"

3566 else

3567 sqlfile="$tmpd/query.$$"

3568 echo "$sql" > $sqlfile

3569 ## err sqlfile=`ls -lF $sqlfile`

3570 query ".read $sqlfile"

3571 fi

3572 ## err p2t done

3573 done

3574 done

3575 done

3576 return 0

3577 ##err donee

3578 )

3579 genform() {

3580 # $1 = form definition file

3581 # $2, $3 (optional)= table name and ROWID

3582 # If $GF_VIEWONLY set and nonNull, output values without form

3583 # If $GF_ARGS set, use it as content-strings in the form

3584 # If $GF_OWNER set, use it as value of name="owner"

3585 # If $GF_STAGE set, use it as value of name="stage"

3586 forms="" hiddens="" rowid=$3

3587 if [ ! -e "$1" ]; then

3588 echo "そのようなデータベースはないようです($2)。" | html p

3589 return

3590 elif [ -n "$2" ]; then

3591 rec=`query "select * from $2 where rowid='$rowid';"`

3592 if [ -z "$rec" ]; then

3593 pk=`gettblpkey $2`

3594 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3595 rec=`query "select rowid from $2 where $pk='$rowid';"`

3596 rowid=$rec

3597 rec=$3

3598 fi

3599 if [ -z "$rec" ]; then

3600 echo "そんなレコードはないみたいね..." | html p

3601 return

3602 fi

3603 fi

3604 if [ -z "$GF_VIEWONLY" ]; then

3605 rm='<input id="rm" name="rm" type="checkbox"

3606 value="yes"><label for="rm">このエントリの削除</label>

3607 <span>ほんとうに消しますよ(確認)!

3608 <input name="confirm" type=checkbox value="yes">はい</span>'

3609 fi

3610 # Image Cache dir

3611 ## err genform: getcache=$2/$rowid

3612 td=`getcachedir "$2/$rowid"`

3613 while IFS=: read prompt name keytype type args; do

3614 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3615 sp="${args:+ }"

3616 form="" val=""

3617 if [ -n "$rowid" ]; then

3618 # err genform2a: Seeking for "$2.$name, type=$type"

3619 rawval=`getvalbyid $2 $name $rowid $td`

3620 val=`echo "$rawval"|htmlescape`

3621 ## err genform3a: getvalbyid $2 $name $rowid $td

3622 ## err genform3b: val="[$val]" type="$type"

3623 fi

3624 if [ -n "$GF_VIEWONLY" ]; then

3625 is_hidden "$2" "$name" && continue

3626 fi

3627 case "$type" in

3628 text*)

3629 cgiform=cgi_multi_$type

3630 if [ -s $td/$name.count -a -n "$val" ]; then

3631 form=`$cgiform $name $td`

3632 val=$(echo "$val"|

3633 while read fn; do

3634 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3635 </td></tr>$nl"

3636 done)

3637 val="<table>$nl$val$nl</table>"

3638 else

3639 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3640 form=`cgi_$type $name "$rawval" "$args"`

3641 fi

3642 ;;

3643 [Rr][Aa][Dd][Ii][Oo])

3644 fh="<label><input type=\"radio\" name=\"$name\""

3645 form="`echo $args|sed -e \

3646 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3647 ;;

3648 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3649 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3650 ;;

3651 [Ss][Ee][Ll][Ee][Cc][Tt])

3652 fh="<select name=\"$name\">$nl"

3653 form=$(for l in $args; do

3654 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3655 done)

3656 if [ -n "$val" ]; then

3657 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3658 fi

3659 form="$fh$form</select>"

3660 ;;

3661 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3662 if [ -s $td/$name.count ]; then

3663 form=`cgi_multi_file $name $td "$args"`

3664 if [ -n "$val" ]; then

3665 hrfb="$myname?showattc+$2_m"

3666 val=$(echo "$rawval" \

3667 | while read fn; do

3668 data=`percenthex "$td/$fn"`

3669 #ct=`cat $td/$fn.content-type`

3670 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3671 ri=`cat "$td/$fn.rowid"`

3672 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3673 #imgsrc="<img src=\"data:$ct,$data\">"

3674 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3675 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3676 done)

3677 fi

3678 else

3679 form="<input type=\"file\" name=\"$name\" $args>"

3680 if [ -n "$val" ]; then

3681 imgs=$(echo "$rawval"\

3682 |while read fn;do

3683 data=`percenthex "$td/$fn"`

3684 echo "<img src=\"data:image/png,$data\">$fn<br>"

3685 done)

3686 form=$form"<br>$imgs"

3687 val=$imgs # 2015-06-15

3688 else

3689 form="<input type=\"file\" name=\"$name\" $args>"

3690 fi

3691 fi

3692 ;;

3693 [Hh][Ii][Dd][Dd][Ee][Nn])

3694 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3695 args="value=\"$GF_STAGE\""

3696 fi

3697 form="<input type=\"hidden\" name=\"$name\" $args>"

3698 prompt='' # Remove prompt

3699 ;;

3700 [Aa][Uu][Tt][Hh][Oo][Rr])

3701 [ -n "$GF_VIEWONLY" ] && continue

3702 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3703 prompt="" ;;

3704 [Oo][Ww][Nn][Ee][Rr])

3705 [ -n "$GF_VIEWONLY" ] && continue

3706 val=${GF_OWNER:-$val}

3707 val=${val:-$user}

3708 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3709 prompt="" ;;

3710 [Uu][Ss][Ee][Rr])

3711 # XXX: is null $user ok?

3712 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3713 [ -n "$GF_VIEWONLY" ] && continue

3714 form="$user"

3715 ;;

3716 [Pp]assword)

3717 [ -n "$GF_VIEWONLY" ] && continue

3718 form="`cgi_passwd`"

3719 val=""

3720 ;;

3721 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3722 [ -n "$GF_VIEWONLY" ] && continue

3723 if [ -z "$rowid" ]; then

3724 val=`genserial`

3725 fi

3726 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3727 prompt="" ;;

3728 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3729 prompt=""

3730 ;;

3731 parent|path|blog*)

3732 prompt=""

3733 ;;

3734 "*"*)

3735 tail=$tail"``"

3736 continue ;;

3737 esac

3738 if [ -n "$prompt" ]; then

3739 if [ -n "${GF_VIEWONLY}" ]; then

3740 form=$val

3741 else

3742 :

3743 fi

3744 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3745 else

3746 hiddens=$hiddens$nl"$form"

3747 fi

3748 done < $1

3749 # enctype="multipart/form-data"

3750 cat<<EOF

3751 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3752 ${rowid:+$rm}

3753 <table class="b $2">

3754 $forms

3755 </table>$hiddens

3756 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3757 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3758 EOF

3759 if [ -z $GF_VIEWONLY ]; then

3760 cat<<EOF

3761 <input type="submit" name="sub" value="OK">

3762 <input type="reset" name="res" value="Reset">

3763 EOF

3764 fi

3765 cat<<EOF

3766 $GF_ARGS</form>

3767 $tail

3768 EOF

3769 }

3770 edittable() {

3771 # $1=form-def $2=table $3 rowid

3772 genform "$@"

3773 }

3774 viewtable() {

3775 GF_VIEWONLY=1 genform "$@"

3776 }

3777 showattc() {

3778 # $1=table_m $2=rowid &optional $3=RawFlag

3779 ## err \$1=$1 \$2=$2 \$3=$3

3780 if ! isfilereadable $user $1 $2; then

3781 contenttype; echo

3782 echo "このファイルは管理者のみしか見られません" | html p

3783 putfooter; exit

3784 fi

3785 idir=`umask 002; mktempd` || exit 1

3786 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3787 bin=$idir/$myname-$$.bin

3788 sql="select quote(bin) from $1 where rowid='$2';"

3789 ## err showattc: sql: $sql

3790 sq $db "$sql" | unhexize > $bin

3791 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3792 type=${tv%//*} fn=${tv#*//}

3793 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3794 ct=${type#file:}

3795 case $ct in # all text/* changed to text/plain

3796 text/*)

3797 charset=`nkf -g $bin|cut -d' ' -f1`

3798 case $charset in

3799 ASCII*) charset="" ;;

3800 esac

3801 if [ -z "$3" ]; then

3802 ct="text/html${charset:+; charset=$charset}"

3803 link="?showattc+$1+$2+raw"

3804 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3805 | sed 's,^,<span></span>,' \

3806 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3807 -D_LINK_="$link" \

3808 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3809 exit $?

3810 fi

3811 ct="text/plain${charset:+; charset=$charset}"

3812 ;;

3813 esac

3814 contenttype "$ct"

3815 echo "Content-Disposition: filename=\"$fn\""

3816 echo "Content-Length: " `cat $bin | wc -c`; echo

3817 #echo "Content-Type: " ${type#file:}; echo

3818 cat $bin

3819 }

3820 #

3821 # Some default stupid handler on CGI values

3822 #

3823 default_storedb() {

3824 # ARG: $1=table-def-file

3825 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3826 tbl=`basename $1`

3827 tbl=${tbl%.def}

3828 cols="`grep :text $1|cut -d: -f2`"

3829 col=`echo "$cols"|head -1`

3830 vcol=`getpar $col`

3831 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3832 if [ -n "$vcol" ]; then

3833 par2table $1

3834 else

3835 return 2 # No insertion occurred

3836 fi

3837 }

3838

3839 default_view() { # $1=def-file

3840 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3841 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3842 default_storedb "$@"

3843 query "select rowid from $tbl order by rowid desc;" \

3844 | while read rowid; do

3845 viewtable $1 $tbl $rowid

3846 done | _m4 -D_TITLE_="$tbl" \

3847 -D_FORM_="`genform $1`" \

3848 -D_DUMPTABLE_="syscmd(cat)" \

3849 $layout/html.m4.html $layout/form+dump.m4.html

3850 }

3851 default_viewtext() { # $1=def-file

3852 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3853 default_storedb "$@"

3854 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3855 | _m4 -D_TITLE_="$tbl" \

3856 -D_FORM_="`genform $1`" \

3857 -D_DUMPTABLE_="syscmd(cat)" \

3858 $layout/html.m4.html $layout/form+dump.m4.html

3859 }

3860 default_smail() {

3861 default_storedb "$@"

3862 if [ $? -eq 2 ]; then

3863 _m4 -D_TITLE_="入力" \

3864 -D_FORM_="`genform $1`" \

3865 -D_DUMPTABLE_="" \

3866 $layout/html.m4.html $layout/form+dump.m4.html

3867 return

3868 fi

3869 cond=""

3870 for pk in `gettblpkey $tbl`; do

3871 pv=$(sqlquote "$(getpar $pk)")

3872 cond="$cond${cond:+ and }$pk=$pv"

3873 done

3874 sql="select rowid from $tbl where $cond;"

3875 rowid=`query "$sql"`

3876 ## err smail1 - "$sql" "-> rowid=$rowid"

3877

3878 while IFS=: read prompt name keytype type args; do # Read from $1

3879 val=`getpar $name`

3880 if [ -n "$val" ]; then

3881 text="$text

3882 $prompt

3883 $name=$val

3884 ---------------------------------------------------------"

3885 fi

3886 case "$type" in

3887 image|document|file)

3888 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3889 fns=$(echo "$fn"|while read fn; do

3890 err mv $tmpd/$fn.orig $tmpd/$fn

3891 mv $tmpd/$fn.orig $tmpd/$fn

3892 rm $tmpd/$fn.rowid # Remove cache flag

3893 ## err "`ls $tmpd/$fn`"

3894 echo $fn

3895 done)

3896 files="$files $fns"

3897 ;;

3898 esac

3899 done < $1

3900 ## err FILES=$files "`ls -lF $tmpd`"

3901 subj="from ${REMOTE_ADDR}"

3902 (echo "$url"

3903 echo "への書き込みがありました。"

3904 echo "------"

3905 echo "$text"

3906 ) | (cd $tmpd &&

3907 err LS="`ls -lF`" &&

3908 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3909 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3910 echo "以下の内容で送信しました。" | html p

3911 viewtable $1 $tbl \

3912 `query "select rowid from $tbl order by rowid desc limit 1;"`

3913 echo "戻る" | html a "href=\"?\""

3914 }