s4: 1546f1f82dcf s4-funcs.sh

s4

view s4-funcs.sh @ 611:1546f1f82dcf

Remove user-rowid flag from member list.

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 17 Apr 2020 11:35:37 +0900
parents	dfd83a6d09fb
children	d3e25f4be330

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 listentlimit=${LISTENTLIMIT:-30}

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

18 templ=${TEMPL:-templ}

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

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

21 imgdir=${IMGDIR:-img}

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

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

24 msgdir=$templ/msg

25 timeout="+2 days"

26 memoplimitdays="7"

27 dumpcollen=22

28 #thumbxy=120x120

29 thumbxy=96x96

30 iconxy_S=80x80

31 iconxy_M=400x400

32 maximagexy=1600x1600

33 ### maximagexy=400x400

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

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

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

38 file_accept_help="

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

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

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

42 "

43 file_warn="$file_accept_help

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

45 blogreadflagrowid=0

46 blogcutoffflagrowid=-1

47 querylog=$tmpdir/query.log

48 searchlog=$tmpdir/search.log

50 tconfs=""

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

52 conftbl=_tblconf

53 nl="

54 "

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

56 iconcachekey="profimgcache_S"

57 case "$HTTP_USER_AGENT" in

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

59 *) touchpanel="" ;;

60 esac

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

63 : <<EOF

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

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

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

68 レポート提出管理

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

71 [2016]

72 7/12 根本への反省

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

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

76 [以下2015]

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

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

79 Web

80 締切設定

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

83 ○自分の提出物リスト

85 7/19 ○設置

86 ○一斉送信

87 ○getparfilename の tmpd の扱い

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

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

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

91 ○添付ファイル回収

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

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

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

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

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

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

99 ○レポート提出モード

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

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

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

103

104

105 7/9 ○管理者の追加

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

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

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

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

110

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

112

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

114 ○グループ検索

115

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

117

118

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

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

121

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

123 ○6/1 par2tableを triplex 対応に

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

125 →とすると一気に

126

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

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

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

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

131

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

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

134 ## 1. passwd

135 ## 2. grp

136 ## 3. grp_mem

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

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

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

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

141

142 ## ● listの定義:

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

144 ## ● hashの定義:

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

146

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

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

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

150 ## csv2sq3 で .csv.sq3 の Makefile

151

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

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

154 ## type := root | comment

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

156 ## unique(id, filename)

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

158

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

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

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

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

163 ## できたー!

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

165 ↓

166 ↓以下に変更

167 with recursive allmem as

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

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

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

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

172

173

174 with recursive allmem as

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

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

177 where allmem.val=grp_m.gname)

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

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

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

181

182

183 ## triggerもできた。

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

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

186

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

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

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

190 ## グループ管理

191 ## 個人情報管理

192 ##

193

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

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

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

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

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

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

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

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

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

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

204

205 ## ログテーブル

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

207

208

209

210 ■表設計

211 * 3つの表に分散管理

212 id格納表 + hash表 + list表

213 * *_s *_m

214

215

216

217 user, user_map, user_col

218

219 ■抽象エントリタイプ

220 * user

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

222 * group

223 権限判定に利用

224 * serial

225 自動idとして機能

226 * password

227 入力 type=passwordで入力

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

229 * session

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

231 * text

232 入力 type=text

233 * textarea

234 入力 textarea

235 * image|document

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

237 * owner

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

239 * gowner

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

241 * timestamp

242 datetime()

243 * parent

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

245 * path

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

247

248 格納タイプ

249 * list

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

251 * hash

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

253

254 オブジェクトタイプ

255 * entry

256 id, title, owner

257 * textpart

258 id, parentID, text

259 * binarypart

260 id, parentID, contenttype, filename, content

261 * content

262 hash(textpart), list(binarypart)

263 * topic

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

265 * reply

266 id, parentID, content

267 * blog

268 list(entry)

269 blog = [topic, list(reply)]

270

271

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

273 "text" => "hogehoge ..",

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

275 "author" => "taro",

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

277 "parent" => "/",

278 "path" => "/1",

279 "text" => "blah, blah, ....",

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

281 {"serial" => 2,

282 "author" => "hanako",

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

284 "parent" => "/",

285 "path" => "/2",

286 "text" => "blah, blah, ....",

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

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

289

290

291 user:=

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

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

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

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

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

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

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

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

300

301 変換表

302 /user/email=m

303

304 blog:=

305 シリアル:id:p:serial

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

307 所有者:owner:s:owner:

308 時刻:ctime:s:stamp:

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

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

311

312 article:=

313 シリアル:id:p:serial

314 筆者:author:s:owner

315 時刻:ctime:s:stamp:

316 参照元:parent:s:parent:

317 パス:path:s:path:

318 本文:text:s:textarea:

319 画像:image:m:image:

320

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

322

323

324 EOF

325

326 logstart() {

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

328 }

329 logend() {

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

331 }

332 sqlog() {

333 logstart

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

335 cat >> $querylog

336 else

337 echo "$*" >> $querylog

338 fi

339 logend

340 }

341 sq() {

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

343 logstart

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

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

346 else

347 echo "$@" >> $querylog

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

349 fi

350 logend

351 }

352 dbsetup() {

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

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

355 sqi=$tmpdir/sqi.$$

356 sqo=$tmpdir/sqo.$$

357 mkfifo $sqi $sqo

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

359 sq $db < $sqi &

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

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

362 exec 2>> $tmpdir/error.out

363 fi

364 exec 3>> $tmpdir/debug.out

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

366 chmod o-r $tmpdir/error.out $tmpdir/debug.out

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

1633 ### If user is not admin, lead to group home

1634 grp=`getgroupbyid $rowid`

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

1636 echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"

1637 return

1638 fi

1639

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

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

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

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

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

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

1646 <div>

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

1648 <table>

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

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

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

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

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

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

1655 </table>

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

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

1658 </div>

1659 EOF

1660 fi

1661 }

1662 mems() {

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

1664 kwd=`getpar kwd`

1665 listmember $kwd

1666 }

1667 grps() {

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

1669 kwd=`getpar kwd`

1670 listgroup $kwd \

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

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

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

1674 $layout/form+dump.m4.html

1675 }

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

1677 gpg=`getpar grp`

1678 grid=${1:-$gpg}

1679 grp=`getgroupbyid "$grid"`

1680 ## . ./s4-blog.sh

1681 jg=`getpar joingrp`

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

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

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

1685 fi

1686 htmlheader=$layout/html.m4.html

1687 showgroup "$grid"

1688 }

1689 sql4interestblogs() {

1690 cat<<EOF

1691 CREATE TEMPORARY VIEW interestblogs AS

1692 SELECT blog.rowid rid, id, author

1693 FROM blog

1694 NATURAL JOIN

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

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

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

1698 WHEN (SELECT user FROM grp_mem

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

1700 THEN 0

1701 ELSE 1

1702 END;

1703 EOF

1704 }

1705 listnewblogsql() { # $1=user

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

1707 WHERE user='$user'

1708 AND tblrowid IN

1709 ($blogreadflagrowid,

1710 $blogcutoffflagrowid)),

1711 "0");"`

1712 cat<<EOF

1713 `sql4interestblogs`

1714 WITH article_ctime as (

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

1716 FROM article join article_s s using(id)

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

1718 GROUP BY id

1719 ), blog_title_owner as (

1720 SELECT blg.rid brid, id,

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

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

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

1724 ), blogall as (

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

1726 ), news as (

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

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

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

1730 FROM blogall bl

1731 LEFT JOIN

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

1733 ON bl.brid=al.tblrowid

1734 WHERE atime < bl.ctime

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

1736 LIMIT 10

1737 ) SELECT brid LINK, "新着",

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

1739 ctime, title,

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

1741 FROM news;

1742 EOF

1743 }

1744

1745 search_form() {

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

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

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

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

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

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

1752 @week → 最近一週間

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

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

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

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

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

1758 auth=""

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

1760 case "$1" in

1761 author=*)

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

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

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

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

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

1767 ;;

1768 grp=*)

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

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

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

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

1773 ;;

1774 esac

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

1776 cat<<-EOF

1777 <div class="right">

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

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

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

1781

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

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

1784

1785 </form>

1786 </div>

1787 EOF

1788 }

1789

1790 imgsrc_cache() (

1791 # $1 = directory for cache'ing

1792 # $2 = table (user_m or grp_m)

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

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

1795 dir="$1" tbl="$2"

1796 keycond="$3"

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

1798 ORDER BY rowid DESC LIMIT 1"

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

1800 tmpf=$tmpd/imgsrc_cache.$$

1801 case "$4" in

1802 [Ss]) size=S ;;

1803 [Oo]) size=O ;;

1804 *) size=M ;;

1805 esac

1806 # ImageCache filename storing schema:

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

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

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

1810 valtype=`query "$sql0"`

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

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

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

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

1815 case "$filetype" in

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

1817 esac

1818 fi

1819 cacheimg_S=$dir/S_$filename

1820 cacheimg_M=$dir/M_$filename

1821 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1829 else

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

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

1832 fifo2=$fifo.2

1833 mkfifo $fifo $fifo2

1834 fmt=${filename##*.}

1835 ## [[ NOTE ]]

1836 ## a. convert oldimage newimage

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

1838 ## b is much smaller than a

1839 cat $tmpf | unhexize \

1840 | tee $fifo \

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

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

1843 | tee $fifo2 \

1844 | convert - "$cacheimg_M" &

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

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

1847 | convert - "$cacheimg_S" &

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

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

1850 echo '">'

1851 echo "$sum" > $sumfile

1852 fi

1853 ## Now preparing cache image, done.

1854 ## Store this information to DB

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

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

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

1858 query <<-EOF

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

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

1861 EOF

1862 )

1863

1864 showhome() {

1865 # $1=userRowIdToShow

1866 err showhome \$1=$1

1867 case "$1" in

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

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

1870 esac

1871 ## err ShowHome: uname=$uname

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

1873 gecos=`gecos "$uname"`

1874 ## err SH:gecos=$gecos

1875 GF_VIEWONLY=1

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

1877 search_form_args=""

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

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

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

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

1882 # Display folders

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

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

1885 and type like 'file:%';"

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

1887 nfile=`query "$sql"`

1888 # err nfile=$nfile

1889 if [ $nfile -gt 0 ]; then

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

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

1892 fi

1893 else

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

1895 GROUP BY user;"`

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

1897 search_form_args="author=$uname"

1898 fi

1899 . ./s4-blog.sh

1900

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

1902 search_form "$search_form_args" > $sf

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

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

1905 viewtable $formdir/user.def user $1

1906 echo "</div>"

1907 } > $pf

1908

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

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

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

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

1913 if true; then

1914 tbl=user_m

1915 enticond="name='$uname'"

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

1917 else

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

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

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

1921 echo '">'

1922 }

1923 fi > $imf

1924 fi

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

1926 val='$uname';"`

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

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

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

1930 listblog $uname > $bf

1931 ## unset ddd

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

1933

1934 hometail=$tmpd/tail.$$

1935 mkfifo $hometail

1936

1937 #Calling listgroupbytable, originally here

1938

1939 (

1940 # Display Most Recent Entry

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

1942 shortval=${shortval:-val}

1943

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

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

1946 ctime,

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

1948 (SELECT gecos FROM gecoses

1949 WHERE name=(SELECT val FROM blog_s

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

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

1952 FROM blog b

1953 JOIN

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

1955 FROM article a, article_s s

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

1957 GROUP BY blogid ORDER BY val DESC LIMIT 50

1958 ) m

1959 ON b.id=m.blogid;"

1960 # This should be as follows

1961 : <<EOF

1962 WITH arts AS(

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

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

1965 FROM article a NATURAL JOIN article_s s

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

1967 GROUP by s.id

1968 )

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

1970 FROM arts a0

1971 JOIN

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

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

1974 ORDER BY ctime DESC LIMIT 50;

1975 EOF

1976

1977 cat<<-EOF

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

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

1980 <div class="lcto">

1981 `DT_VIEW=replyblog dumptable html blog`

1982 </div>

1983 EOF

1984 unset DT_SQL

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

1986 # Display NEWS

1987 # 2016-06-26

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

1989 acclog blog $blogreadflagrowid

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

1991 fi

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

1993 sql=`listnewblogsql "$user"`

1994 # echo "$sql" > tmp/listnew

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

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

1997 cont=$((cont-1))

1998 err newcount=$cont

1999 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

2006 `cgi_submit '確定'`

2007 `cgi_hidden read yes`

2008 EOF

2009 echo "$new10 "

2010 echo "</div>"

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

2012 acclog blog $blogcutoffflagrowid # for speed

2013 fi

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

2015 : # DT_SQL=

2016 fi

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

2018 #

2019 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

2025 -D_NBLOG_="$nblog" \

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

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

2028 $layout/html.m4.html $layout/home.m4.html

2029

2030 # Record access log

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

2032 }

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

2034 contenttype; echo

2035 ## err commission: "$@"

2036 gname=`getgroupbyid $1`

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

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

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

2040 grp_reg_adm "$@"

2041 else

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

2043 fi

2044 }

2045 listgroupbytable() {

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

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

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

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

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

2051 NGsql="select distinct tag from\

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

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

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

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

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

2057 for tag in `query "$sql"`

2058 do

2059 ## err ListGrp: tag=$tag

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

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

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

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

2064 as rowid,

2065 gname,

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

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

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

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

2070 ## err PersonalGroupList= `echo $sql`

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

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

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

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

2075 echo '</table>'

2076 done

2077 echo '</div>'

2078 }

2079 iconhref() (

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

2081 data=`percenthex "$1"`

2082 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2083 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

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

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

2086 )

2087 iconhref2() (

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

2089 src=$1

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

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

2092 )

2093 listentry() (

2094 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

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

2096 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2097 cond='' hiddens=''

2098 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2099 if [ -z "$offset" ]; then

2100 offset=`getpar start`; offset=${offset%%[!0-9]*}

2101 offset=$((offset-1))

2102 fi

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

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

2105 limit=$listentlimit

2106 dir=`getcachedir "$1"`

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

2108 hrb="$myname?home"

2109 deficon=person-default.png

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

2111 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2112 gcs=gecos

2113 else # if group

2114 hrb="$myname?grp"

2115 deficon=group-default.png

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

2117 gcs=name

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

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

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

2121 ## err tagconv=$tagconv

2122 fi

2123 fi

2124 if [ ! -d $dir ]; then

2125 mkdir -p $dir

2126 fi

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

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

2129 fi

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

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

2132 fi

2133

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

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

2136 ##qgrp=`sqlquote $grp`

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

2138 sql="select a.rowid, a.$link,

2139 coalesce(b.$gcs, a.$nm) as nick,

2140 quote(a.$nm) as qname,

2141 (SELECT val FROM ${tbl}_s

2142 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2143 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2144 || case when a.$nm in (select gname from grp)

2145 then printf('(%d名)',

2146 (select count(user) from grp_mem where gname=a.$nm))

2147 else ' <'||a.$nm||'>'

2148 end

2149 as name,

2150 b.tag,

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

2152 where gname=$getgrp) then '管理者'

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

2154 then 'ADMIN'

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

2156 then 'Member'

2157 when '$iamowner' = '' then ''

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

2159 CASE '$entity'

2160 WHEN 'グループ'

2161 THEN coalesce(

2162 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2163 'open')

2164 ||

2165 CASE WHEN '$user'

2166 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2167 THEN ' ismember'

2168 ELSE ''

2169 END

2170 ELSE 'user'

2171 END regmode

2172 from $tbl a left join

2173 (select $nm as name,

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

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

2176 max(case key when 'mtime' then val end) as mtime,

2177 max(case key when 'wtime' then val end) as wtime,

2178 max(case key when 'login' then val end) as login

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

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

2181 order by b.wtime desc, b.login desc,

2182 b.mtime desc, b.tag desc, a.rowid asc"

2183 # Give precedence to newer maintained groups (2016-09-24)

2184 # Note that mtime is stored only in grp_s.

2185 ## err LE:sql.1="$sql"

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

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

2188 echo '<div class="listentry">' # List-entry div

2189 # Show owner/member filter button

2190 cat<<-EOF

2191 `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2192 for="ismembtn">参加中以外隠す</label>

2193 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2194 for="isadmbtn">管理者参加以外隠す</label>

2195 EOF

2196 METHOD=GET

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

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

2199 cgi_form $stage <<EOF

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

2201 `cgi_text kwd $kwd`</label>

2202 EOF

2203 echo '</div>'

2204 fi

2205 hiddens="$hiddens

2206 `cgi_hidden kwd \"$kwd\"`

2207 `cgi_hidden stage \"$stage\"`"

2208 cat<<EOF

2209 <form action="$myname" method="$METHOD">

2210 <p>${total}件中の<input class="hidesub" type="text" name="start"

2211 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2212 <input type="submit" value="確定"></p>

2213 </form>

2214 EOF

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

2216 nextbtn=$(

2217 cat<<EOF

2218 <div class="right clear"><form action="$myname" method="$METHOD">

2219 `cgi_submit 次の${limit}件`

2220 $hiddens

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

2222 EOF

2223 )

2224 fi

2225 if [ $offset -gt 0 ]; then

2226 prevbtn=$(

2227 cat<<EOF

2228 <form action="$myname" method="$METHOD">

2229 `cgi_submit 前の${limit}件`

2230 $hiddens

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

2232 EOF

2233 )

2234 fi

2235 pnbtn="$nextbtn$prevbtn"

2236 echo $pnbtn

2237

2238 ## err ListEntry: `echo "$sql"\;`

2239 # sq $db here??? 2016-11-28

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

2241 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2242 # err name=$name owner=$ownerp lnk=$lnk

2243 # err newlnk=$lnk regmode=$regmode

2244 icondir=$dir/$id

2245 # Pick up only last icon

2246 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2247 <p class=\"tag _$tag\">$tag</p>" \

2248 | _m4 $tagconv

2249 if [ -n "$NOSPEEDUP" ]; then

2250 files=`getvalbyid $tbl profimg $id $icondir`

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

2252 icon=`echo "$files"|tail -1`

2253 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2254 else

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

2256 fi

2257 elif [ -n "$icon" -a -s "$icon" ]; then

2258 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2259 else

2260 cond="$nm=$qname"

2261 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2262 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2263 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2264 # err "img=[$img]"

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

2266 echo "<a href=\"$hrb+$lnk\">"

2267 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2268 echo "</a>"

2269 else

2270 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2271 fi

2272 fi

2273 echo "<br>$name${ownerp:+<br>($ownerp)}"

2274 echo "</div>"

2275 done

2276 echo "</div>" # End of List-entry div

2277 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2278 )

2279 listmember() {

2280 listentry user "$@"

2281 }

2282 listgroup() {

2283 listentry group "$@"

2284 }

2285 hexteams() { # $1=gname, $2(optional)=user

2286 cond=${2:+" AND user='$2'"}

2287 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2288 WHERE gname='$1' AND key='team'$cond;"

2289 }

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

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

2292 grid=`getpar grid`

2293 grid=${grid%%[!0-9]*}

2294 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2295 else

2296 grid=$1

2297 fi

2298 grp=`getgroupbyid $grid`

2299 qgrp=`sqlquote "$grp"`

2300 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2301 if isgroup "$grp"; then

2302 tf=$tmpd/title.$$

2303 sf=$tmpd/search.$$

2304 bodyclass=`query "SELECT val FROM grp_s

2305 WHERE gname=$qgrp AND key='regmode';"`

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

2307 ismember="ismember"

2308 qgrp=`sqlquote "$grp"`

2309 bodyclass="$bodyclass${bodyclass:+ }ismember"

2310 else

2311 ismember="" # bodyclass="group"

2312 fi

2313 bodyclass="$bodyclass grouphome"

2314 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

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

2316

2317 showgroupsub $formdir/grp.def "$grid" | \

2318 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

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

2320 -D_BODYCLASS_="$bodyclass" \

2321 -D_DUMPTABLE_="" \

2322 $htmlheader $sf $layout/form+dump.m4.html

2323 # $htmlheader $layout/form+dump.m4.html

2324 # $htmlheader is defined in grp()

2325 else # if $grp is removed at par2table

2326 listgroup

2327 fi

2328 }

2329 showgroupsub() {

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

2331 # Using $ismember

2332 rowid=$2

2333 grp=`getgroupbyid $2`

2334 qgrp=`sqlquote "$grp"`

2335 td=`getcachedir grp/"$2"`

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

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

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

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

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

2341 return

2342 fi

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

2344 enticond="gname=$qgrp"

2345 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

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

2347 cat<<-EOF

2348 <p class="groupimg">

2349 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2350 EOF

2351 fi

2352 echo "<div class=\"noprofimg\">"

2353 viewtable $1 grp $rowid

2354 echo "</div>"

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

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

2357 iamowner=$rowid

2358 colmd=" mode"

2359 fi

2360 if [ -n "$ismember" ]; then

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

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

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

2364 cat<<EOF

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

2366 <div class="fold clear">

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

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

2369 <div>

2370 `cgi_textarea message "" "cols=60"`

2371 `cgi_submit 送信`

2372 `cgi_reset リセット`

2373 </div>

2374 `cgi_hidden grp $rowid`

2375 </div></form>

2376 EOF

2377 fi

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

2379 if [ -n "$ismember" ]; then

2380 ismem='checked' state="(参加中)"

2381 else

2382 nomem='checked' state="(現在非加入)"

2383 fi

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

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

2386 and key='email';"`

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

2388 ## and key='email';"

2389 ##err email=$eml

2390 cat <<EOF

2391 <div class="fold clear">

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

2393 for="reg">自身の加入状態を操作する</label>$state

2394 <div>

2395 EOF

2396 cgi_form grp <<EOF

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

2398 <table class="b">

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

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

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

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

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

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

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

2406 </table>

2407 `cgi_hidden grp $rowid`

2408 EOF

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

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

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

2412 fi

2413 echo '</div></div>'

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

2415 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2416 cat<<-EOF

2417 <form class="summary" action="$myname" title="$thelp">

2418 `cgi_hidden owner "$grp"`

2419 `cgi_hidden kwd "@week"`

2420 `cgi_hidden stage searchart`

2421 `cgi_submit "一週間のまとめ"`

2422 </form>

2423 EOF

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

2425 colstate="state:稼動状態:frozen=rowclass=凍結"

2426 DT_CHLD=article:blogid \

2427 DT_QOWNER="$qgrp" \

2428 DT_VIEW=replyblog dumptable html blog \

2429 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2430

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

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

2433 cm="?commission+$rowid"

2434 thumbxy=50x50 listmember "" "$c" "$rowid" \

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

2436 # team list

2437 hexteams=`hexteams "$grp"`

2438 if [ -n "$hexteams" ]; then

2439 echo "チーム一覧" | html h2

2440 echo '<div class="dumptable"><table class="b">'

2441 sq $db -html -header<<-EOF

2442 SELECT val TEAM,

2443 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2444 MEMBERS

2445 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2446 EOF

2447 echo '</table></div>'

2448 fi

2449 }

2450 grp_getbodyclass() {

2451 # Get css class name for document.

2452 # `moderated' for moderated groups

2453 # `ismember' for groups where user belongs

2454 # $1=GroupName (w/o quote)

2455 # $user=userNameCurrentlyLogin

2456 ## err grp_getbodyclass: 1="$1"

2457 qgrp=`sqlquote "$1"`

2458 query<<-EOF

2459 SELECT coalesce(

2460 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2461 'open')

2462 ||

2463 CASE WHEN '$user'

2464 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2465 THEN ' ismember'

2466 ELSE ''

2467 END;

2468 EOF

2469 }

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

2471 err GRP_ACTION:IN

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

2473 grp=`getgroupbyid "$grid"`

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

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

2476 fi

2477 if ! ismember $user "$grp"; then

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

2479 fi

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

2481 | _m4 -D_TITLE_="syscmd(\`cat')" \

2482 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2483 $layout/html.m4.html

2484

2485 isowner=""

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

2487 usel=`getpar usel`

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

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

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

2491 text=`getpar text`

2492

2493 rm=`getpar rm` cfm=`getpar confirm`

2494 ## err rm=$rm cfm=$cfm

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

2496 if [ "$isowner" ]; then

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

2498 # Eliminate

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

2500 for tbl in grp_mem grp_mem_s grp_mem_m; do

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

2502 # echo "sql=$sql"

2503 query "$sql"

2504 err rmGRPuser "$sql"

2505 done

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

2507 #err num=$num

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

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

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

2511 # err `echo "$sql"`

2512 html pre<<EOF

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

2514 `query "$sql"`

2515 EOF

2516 fi

2517 else

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

2519 return

2520 fi

2521 else # not Group Owner

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

2523 return

2524 fi

2525 cat<<EOF

2526

2527 EOF

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

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

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

2531 return 0

2532 fi

2533 gecos=`gecos $user`

2534 mkfrom=`getpar mkfrom`

2535 if [ x"$mkfrom" = x"yes" ]; then

2536 safegc=`echo "$gecos" | tr -d '<>@'`

2537 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2538 fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2539 mail_from="$safegc <$fromad>"

2540 else

2541 mail_from="$admin"

2542 fi

2543 MAIL_FROM=$mail_from \

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

2545 "$gecos さんからのメッセージ" <<EOF

2546 $url

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

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

2549 ----------------------------------------------------------

2550 $text

2551 EOF

2552 if [ $? = 0 ]; then

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

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

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

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

2557 html pre<<EOF

2558 `query "$sql"`

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

2560 EOF

2561 err SendDone: `echo $sql`

2562 fi

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

2564 grp_reg_adm $grid $usel

2565 elif [ x"$rm" = x"addteam" ]; then

2566 team=`getpar team|sed "s/'/''/g"` # for single quotation

2567 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2568 if [ x"$team" != x"$newteam" ]; then

2569 echo "チーム名に使えない文字を除去しました" | html p

2570 team=newteam

2571 fi

2572 if [ -z "$team" -o x"$team" = x"なし" ]; then

2573 cat<<-EOF | html p

2574 有効なチーム名を入力してください。

2575 カンマだけ、「なし」という名前は使えません。

2576 EOF

2577 echo "有効なチーム名を入力してください。" | html p

2578 else

2579 grp_add_team $grid "$team" $usel

2580 fi

2581 elif [ x"$rm" = x"rmteam" ]; then

2582 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2583 rmteam=`getpar rmteam|sed "s/'/''/g"`

2584 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2585 gname='$grp' AND user='$user' AND key='team'\

2586 AND val='$rmteam';\"`" ]; then

2587 grp_rm_team $grid "$rmteam" $usel

2588 else

2589 echo "所属していないチームの除去操作はできません。"|html p

2590 fi

2591 else

2592 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2593 fi

2594 fi

2595 fi

2596 # POST count summary

2597 from=`getpar from`; to=`getpar to`

2598 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2599 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2600 fromtonote="<p>POST集計: $from_input - $to_input</p>"

2601 # New entry

2602 sql="WITH mems AS (

2603 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2604 ON gm.user=g.name

2605 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2606 ), target_article AS (

2607 SELECT id FROM article_s

2608 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2609 ), posts AS (

2610 SELECT author, count(author) post

2611 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2612 WHERE blogid IN (SELECT id FROM blog_s

2613 WHERE key='owner'

2614 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2615 AND key='text'

2616 GROUP BY author

2617 ), teams AS (

2618 SELECT user, group_concat(val, ', ') team

2619 FROM grp_mem_m

2620 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2621 AND key='team'

2622 GROUP BY user

2623 ), user_post AS (

2624 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2625 FROM mems m LEFT JOIN posts

2626 ON m.name=posts.author

2627 GROUP by m.rowid

2628 )

2629 SELECT

2630 CASE

2631 WHEN (SELECT user FROM grp_adm

2632 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2633 AND user=up.name) IS NOT NULL

2634 then 'k'

2635 ELSE ''

2636 END || rowid || ',' || gecos NAME,

2637 post POST, team TEAM

2638 FROM user_post up LEFT JOIN teams t

2639 ON up.name=t.user

2640 ORDER BY gecos;"

2641 ## err grpaction: "`echo \"$sql\"`"

2642 tf=$tmpd/title.$$

2643 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2644 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2645 for=\"cmadmin\">グループ管理者委任</label>

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

2647 </p></div>"

2648 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2649 for=\"conf\">グループ登録解除</label>

2650 <div>本当に消します! `cgi_checkbox confirm yes` 確認

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

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

2653 </div>"

2654 # Get team list to which current user belongs into $hexteams

2655 allhexteams=$(hexteams "$grp")

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

2657 myhexteams="$allhexteams" # admin can remove all teams' attr

2658 else

2659 myhexteams=$(hexteams "$grp" "$user")

2660 fi

2661 if [ -n "$myhexteams" ]; then

2662 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2663 for=\"cmrmteam\">チーム属性除去</label>

2664 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2665 を除去します: `cgi_checkbox teamconfirm yes` 確認

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

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

2668 </div>

2669 "

2670 fi

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

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

2673 #b2='"> <span>' b3='</span></label>'

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

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

2676 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2677 cgi_form grpaction<<EOF \

2678 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2679 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2680 | _m4 -D_TITLE_="spaste(\`$tf')" \

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

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

2683 $layout/form+dump.m4.html

2684 <p>下でチェックした人を対象として:</p>

2685 <div class="foldtabs">

2686 `cgi_radio rm addteam 'id="cmteam"'`<label

2687 for="cmteam">同じチーム属性を付与</label>

2688 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2689 `cgi_datalist_h teams $allhexteams`

2690 </div>

2691 ${rmteammsg}

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

2693 for="sendmsg">メッセージ送信</label>

2694 <div>

2695 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2696 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2697 `cgi_textarea text "" cols=40`

2698 </div>

2699 ${isowner:+$cmmsg$excmsg}

2700 `cgi_radio rm close id="x"`<label for="x">×</label>

2701 </div>

2702 <h4>$grp 参加者一覧</h4>$fromtonote

2703 <table class="td2r">

2704 `sq $db -header -html "$sql"`

2705 </table>

2706 `cgi_hidden grp $grid`

2707 EOF

2708 }

2709 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2710 # Create TEMPORARY VIEW

2711 query<<EOF

2712 CREATE TEMPORARY VIEW writeusers AS

2713 SELECT DISTINCT author FROM article

2714 WHERE id in (

2715 select id from article where blogid=(select id from blog where rowid=$1)

2716 );

2717 CREATE TEMPORARY VIEW movablegroups AS

2718 SELECT g.rowid growid , g.gname

2719 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2720 ON grp.gname=gm.gname -- そのユーザが属している

2721 AND user='$user') g -- グループに絞る

2722 WHERE (SELECT author FROM writeusers

2723 EXCEPT

2724 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2725 IS NULL;

2726 $2

2727 EOF

2728 }

2729 sql4readableblogs() {

2730 # Create view of blogs that can be readable to $user

2731 # Blog is readable when:

2732 # 1: blog owner is an user

2733 # 2: else, 2.1: owner-group where the $user belongs

2734 # 2.2: else, owner-group is not moderated

2735 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2736 cat<<EOF ## | tee tmp/sql.out

2737 CREATE TEMPORARY VIEW readableblogs AS

2738 SELECT blog.rowid rid, id, author

2739 FROM blog

2740 NATURAL JOIN

2741 (SELECT id,

2742 max(CASE key WHEN 'owner' THEN val END) owner,

2743 max(CASE key WHEN 'mode' THEN val END) mode

2744 FROM blog_s GROUP by id) bs

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

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

2747 WHEN (SELECT val FROM grp_s

2748 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2749 AND

2750 (SELECT user FROM grp_mem

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

2752 THEN 0

2753 WHEN mode IN ('quiz', 'enquete')

2754 THEN 0 -- "quiz" mode blog cannot be searched

2755 ELSE 1

2756 END;

2757 EOF

2758 }

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

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

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

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

2763 return

2764 fi

2765 owner=`getvalbyid blog owner $rowid`

2766 title=`getvalbyid blog title $rowid`

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

2768 | _m4 -D_TITLE_="修正" \

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

2770 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

2773 # Move to group

2774 if isuser "$owner"; then

2775 crview4article $rowid

2776 n=`query "SELECT count(*) FROM writeusers;"`

2777 ## err N=$n

2778 if [ $((n)) -gt 0 ]; then

2779 ## err ROWID=$rowid

2780 sql="SELECT growid || ':' || gname FROM movablegroups;"

2781 cat<<-EOF

2782 <div class="fold">

2783 `cgi_checkbox mv send id="mv"`<label

2784 for="mv">この話題をグループ所有に移動する</label>

2785 <div>

2786 <form action="?mvart" method="POST" enctype="multipart/form-data">

2787 移動先グループ:

2788 <select name="mv2grp">

2789 EOF

2790 query ".mode html"

2791 query<<-EOF |

2792 $sql

2793 .mode list

2794 EOF

2795 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2796 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2797 cat<<-EOF

2798 </select>

2799 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2800 そのグループに加入しているものに限られます)</p>

2801 <p>`cgi_checkbox cfm yes`<label>確認

2802 (この操作は元に戻すことができません)</label></p>

2803 `cgi_hidden blogrowid $rowid`

2804 `cgi_submit 移動`

2805 `cgi_reset Reset`

2806 </form>

2807 </div>

2808 </div>

2809 EOF

2810 fi

2811 # end of isuser "$owner"

2812 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2813 [ -n "$hexteams" ];}; then

2814 none="`echo なし|hexize`"

2815 cat<<-EOF

2816 <div class="fold">

2817 `cgi_checkbox mv2team send id="mv2team"`<label

2818 for="mv2team">この話題を以下のチームのものにする</label>

2819 <div><p>現在の所属チーム設定:

2820 `query "SELECT

2821 coalesce((SELECT val FROM blog_s

2822 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2823 AND key='team'),

2824 ':なし');"`</p>

2825 <form action="?mvart" method="POST" enctype="multipart/form-data">

2826 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2827 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2828 `cgi_hidden blogrowid $rowid`<br>

2829 `cgi_submit 移動`

2830 `cgi_reset Reset`

2831 </form></div></div>

2832 EOF

2833 fi

2834 }

2835 mvart() { # move diary to some group or team

2836 # or move blog of group to team which belong to the group

2837 blogrowid=`getpar blogrowid`

2838 cfm=`getpar cfm`

2839 ##### echo move blog:$blogrowid to $mv2grp | html p

2840 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2841 . ./s4-blog.sh

2842 if [ -z "$blogrowid" ]; then

2843 echo "無効な指定です(mvart)。" | html p

2844 return

2845 elif [ x"$cfm" != x"yes" ]; then

2846 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2847 elif { mv2grp=`getpar mv2grp`

2848 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2849 [ -n "$mv2grp" ]; }; then

2850 crview4article $blogrowid

2851 ########## TRANSACTION BEGIN

2852 query "BEGIN;"

2853 n=`query "SELECT count(*) FROM writeusers;"`

2854 ## err Nwriteuser=$n

2855 if [ $((n)) -gt 0 ]; then

2856 query<<-EOF

2857 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2858 WHERE key='owner'

2859 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2860 AND $mv2grp IN (SELECT growid FROM movablegroups);

2861 EOF

2862 fi

2863 query "END;"

2864 ########## TRANSACTION END

2865 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2866 [ -n "$mv2team" ];}; then

2867 # blog owner can move it to ANY team

2868 case "$mv2team" in

2869 'なし')

2870 cat<<-EOF

2871 DELETE FROM blog_s

2872 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2873 AND key='team';

2874 EOF

2875 ;;

2876 "") ;;

2877 *)cat<<-EOF

2878 BEGIN;

2879 REPLACE INTO blog_s(id, key, val)

2880 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2881 'team', '$mv2team');

2882 REPLACE INTO blog_s(id, key, val)

2883 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2884 'notify', 'all'); -- Change notify to all

2885 END;

2886 EOF

2887 esac | query

2888 fi

2889 blog_reply $blogrowid

2890 echo yes | html p

2891 }

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

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

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

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

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

2897 return

2898 fi

2899 owner=`getvalbyid blog owner $blogrowid`

2900 title=`getvalbyid blog title $blogrowid`

2901 author=`getvalbyid article author $rowid`

2902 ## err EDITart: owner=$owner, author=$author

2903 if isgrpowner "$user" "$owner"; then

2904 : EDIT OK

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

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

2907 return

2908 fi

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

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

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

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

2913 > $tmpout

2914 rm -f /tmp/editart.out

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

2916 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

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

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

2919 -D_BLOGS_= -D_DUMPTABLE_= \

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

2921 }

2922 send2mem() {

2923 rowid=`getpar grp`

2924 rowid=${rowid%%[!0-9]*} # Cleaning

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

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

2927 return

2928 fi

2929 message=`getpar message`

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

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

2932 return

2933 fi

2934 grp=`getgroupbyid $rowid`

2935 members=`collectemail $grp`

2936 # smail rcpt subj (file)

2937 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$admin>" \

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

2939 $urlbase?grp+$rowid

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

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

2942

2943 $message

2944 EOF

2945 cat<<EOF

2946 <p>以下のユーザに送信しました。</p>

2947 <pre>

2948 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

2949 </pre>

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

2951 EOF

2952 }

2953 joingrpadmit() {

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

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

2956 echo "bye bye" | html p; return

2957 fi

2958 t_usr=`session=$2 getpar user`

2959 t_grp=`session=$2 getpar group`

2960 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2961 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

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

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

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

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

2966 return

2967 fi

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

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

2970 fi

2971 case $1 in

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

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

2974 *)

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

2976 return ;;

2977 esac

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

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

2980 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2981 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2982 に

2983 $t_usr

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

2985 しました。"

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

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

2988 echo "$body" | html p

2989 }

2990

2991 joingrprequest() {

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

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

2994 addsession $jss +${memoplimitdays}days

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

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

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

2998 $url

2999 $user さんから

3000 グループ $1

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

3002

3003 承認する:

3004 $urlbase?joingrpadmit+yes+$jss

3005

3006 白紙に戻す:

3007 $urlbase?joingrpadmit+no+$jss

3008 EOF

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

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

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

3012 }

3013 joingrp() {

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

3015 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3016 if isgrpowner "$user" "$1"; then

3017 isowner="yes"

3018 elif [ -n "$5" ]; then

3019 isowner="yes"

3020 else

3021 isowner=""

3022 fi

3023 ## err jg:isgrpowner: isowner="$isowner"

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

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

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

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

3028 return

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

3030 : # Do not pursue those who leave

3031 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3032 : # Member can change own email address for the joining moderated group

3033 else # adding user is $user itself

3034 case `getgroupattr $1 regmode` in

3035 moderated)

3036 joingrprequest "$@" # Request only

3037 return

3038 ;;

3039 *)

3040 ;;

3041 esac

3042 fi

3043 qgname=`sqlquote "$1"`

3044 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

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

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

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

3048 # Notify joingrp to admin

3049 action="に加入しました。"

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

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

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

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

3054 else

3055 echo $msg

3056 fi

3057 else

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

3059 fi

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

3061 # Coming here means newly created group

3062 sql="select case\

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

3064 then (select user from grp_mem\

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

3066 else '' end; "

3067 err NewGrpChk: $sql

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

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

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

3071 fi

3072 fi

3073 else

3074 query "begin;

3075 delete from grp_mem $cond;

3076 delete from grp_mem_s $cond;

3077 delete from grp_mem_m $cond;

3078 delete from grp_adm $cond;

3079 delete from grp_adm_s $cond;

3080 delete from grp_adm_m $cond;

3081 end;"

3082 action="から脱退しました。"

3083 fi

3084 smail "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3085 $url?grp+$grid

3086 $user (`gecos $user`)さんが

3087 グループ $1

3088 $action

3089 EOF

3090 }

3091 grp_add_team() (

3092 # $1=grp-rowid $2=team $3...=user-rowid(s)

3093 grp=`getgroupbyid $1`

3094 team=$2; shift; shift

3095 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3096 { echo "BEGIN;"

3097 for user; do

3098 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3099 '$grp',\

3100 (SELECT name FROM user WHERE rowid=$user),\

3101 'team', 'string', '$team');"

3102 done

3103 echo "END;"

3104 } | query

3105 )

3106 grp_rm_team() (

3107 # $1=grp-rowid $2=team $3...=user-rowid(s)

3108 grid=$1

3109 qgrp=$(sqlquote "`getgroupbyid $grid`")

3110 team=$2; shift; shift

3111 [ -z "$grid" -o -z "$team" ] && return

3112 { echo "BEGIN;"

3113 for user; do

3114 echo "DELETE FROM grp_mem_m\

3115 WHERE gname=$qgrp \

3116 AND user=(SELECT name FROM user WHERE rowid=$user)\

3117 AND key='team' AND val='$team';"

3118 done

3119 cat<<-EOF

3120 DELETE FROM blog_s

3121 WHERE rowid=(

3122 SELECT rowid

3123 FROM blog_s a

3124 WHERE key='team'

3125 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3126 AND NOT EXISTS (SELECT * FROM grp_mem_m

3127 WHERE key='team' AND val=a.val -- a.val=team

3128 AND gname = (SELECT val FROM blog_s b

3129 WHERE a.id=b.id AND key='owner')

3130 ));

3131 EOF

3132

3133 echo "END;"

3134 } | query

3135 )

3136 grp_reg_adm() {

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

3138 grid=$1

3139 grp=`getgroupbyid "$1"`

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

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

3142 fi

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

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

3145 fi

3146 shift

3147 for urid; do

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

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

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

3151 fi

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

3153 err ismember $newadm $grp

3154 if ismember $newadm "$grp"; then

3155 # OK, go ahead

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

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

3158 # confirm insertion

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

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

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

3162 else

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

3164 fi

3165 fi

3166 showgroup $grid

3167 done

3168 }

3169 dt_rowhack() {

3170 # From: <TR>

3171 # ....

3172 # <TD>rowclass=foo</TD>

3173 # </TR>

3174 # To: <TR class="foo">....<TD>foo</TD></TR>

3175 sed -e '

3176 /^<TR>/ {

3177 :loop

3178 s/\n//

3179 N

3180 /<\/TR>/ {

3181 s/\n//

3182 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3183 n

3184 }

3185 $q

3186 b loop

3187 }'

3188 }

3189 dumptable() {

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

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

3192 # $DT_VIEW sets link

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

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

3195 VIEW=${DT_VIEW-replyblog}

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

3197 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3198 fi

3199 sqlfile=$tmpd/dump.sql

3200 : > $sqlfile # ensure to be empty

3201 printf '.mode html\n.header 1\n' > $sqlfile

3202 # $DT_CHLD=ChildTable:BindColumn

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

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

3205 cat<<-EOF >> $sqlfile

3206 -- presql

3207 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3208 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3209 EOF

3210 # Speed up counting of new articles

3211 cat<<-EOF >> $sqlfile

3212 -- presql2

3213 DROP TABLE IF EXISTS _counts;

3214 CREATE TEMPORARY TABLE _counts AS

3215 SELECT $_i, count($_i) cnt

3216 FROM $_t GROUP BY $_i;

3217 /* Prepare NEW count table */

3218 CREATE TEMPORARY TABLE _target AS

3219 SELECT b.rowid trowid, b.id

3220 FROM "$2" b JOIN "$2_s" s

3221 ON b.id=s.id AND s.key='owner'

3222 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3223

3224 DROP TABLE IF EXISTS _children;

3225 CREATE TEMPORARY TABLE _children AS

3226 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3227 FROM (SELECT t.trowid, t.id $_i, a.id

3228 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3229 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3230

3231 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3232 DROP TABLE IF EXISTS _news;

3233 DROP VIEW IF EXISTS _news;

3234

3235 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3236 -- INSERT INTO _news

3237 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3238 CREATE TEMPORARY VIEW _news AS

3239 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3240 FROM (SELECT DISTINCT id FROM _target)

3241 a LEFT JOIN

3242 (SELECT $_i, count(ctime) newcnt

3243 FROM _children x

3244 WHERE ctime > coalesce((SELECT time from myacclog

3245 WHERE tblrowid=x.trowid),

3246 '1970-01-01')

3247 GROUP BY $_i) b

3248 ON a.id=b.$_i;

3249 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3250 EOF

3251 # REMOVE next line until 2019/5/1

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

3253 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3254 # REMOVE next assignment until 2019/5/1

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

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

3257 and val > coalesce((select time from myacclog where \

3258 tblrowid=a.rowid),\

3259 '1970-01-01'))"

3260 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

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

3262 dt_class=" td2r td3r dumpblogs"

3263 fi

3264 # Construct join expression

3265 eav="" scols=""

3266 pk=`gettblpkey $2`

3267 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3268 substr=${substr:-%s}

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

3270 valvar=val

3271 case $col in

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

3273 continue ;; # built-in column name

3274 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3275 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3276 case "$as" in

3277 *:*=*) cnd=${as#*:}

3278 h=${cnd%%=*} v=${cnd#*=}

3279 h=`sqlquotestr "$h"`

3280 v=`sqlquotestr "$v"`

3281 valvar="CASE val WHEN $h THEN $v END"

3282 as=${as%%:*} ;;

3283 esac

3284 ;;

3285 *) as=${col} ;;

3286 esac

3287 ss=`printf "$substr" "$valvar"`

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

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

3290 done

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

3292 if [ -n "$DT_SQL" ]; then

3293 echo "$DT_SQL"

3294 else

3295 cat<<-EOF

3296 SELECT a.rowid as LINK, $cnt $scols

3297 FROM $2 a LEFT JOIN

3298 (SELECT $pk,$eav,

3299 max(CASE key

3300 WHEN 'owner'

3301 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3302 as gecos

3303 FROM ${2}_s c GROUP BY $pk)

3304 b ON a.$pk=b.$pk $4;

3305 EOF

3306 fi >> $sqlfile

3307 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3308 sqlog<<-EOF

3309 *** SQL-file: $sqlfile ***

3310 `cat $sqlfile`

3311 EOF

3312 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3313 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3314 cat $sqlfile >> tmp/sql

3315 # query ".read $sqlfile" > $tmpd/foo

3316 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3317 cp $tmpd/foo tmp/

3318 err "----- `gdate +%FT%T.%3N` ------------555555"

3319 ## $ddd LINE exists at the end of this function

3320 fi

3321 printf '.mode list\n.header 0\n' >> $sqlfile

3322 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3323 <div>

3324 <div class="dumptable">

3325 <table class="b$dt_class">

3326 `query ".read $sqlfile"`

3327 </table>

3328 </div>

3329 </div>

3330 EOF

3331 ### `query ".read $sqlfile"`

3332 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3333

3334 ## REMOVE THIS!

3335 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3336 }

3337

3338 par2table() (

3339 # copy current parameters of par into destination table

3340 # $1=definition-file

3341 # Using $user and $session

3342 # Return value:

3343 # 0: Stored successfully

3344 # 1: Insufficient fillings

3345 # 2: No permission to modify the record

3346 # 3: Invalid rowid

3347 # 4: SUCCESS to delete

3348 # 5: Stop deletion for lack of confirm check

3349 # 6: Password length too short

3350 # 7: Password mismatch

3351 # 8: Old password incorrect

3352 rowid=`getpar rowid`

3353 if [ ! -e $1 ]; then

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

3355 exit 1

3356 fi

3357 tbl=${1%.def}

3358 tbl=${tbl##*/}

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

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

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

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

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

3364 ##err p2t:grp:q $sql

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

3366 elif [ x"$tbl" = x"blog" ]; then

3367 # Check if owner in blog_s

3368 blogowner=`getvalbyid blog owner "$rowid"`

3369 if isgrpowner "$user" "$blogowner"; then

3370 rowowner=$user

3371 else

3372 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3373 fi

3374 else

3375 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3376 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3377 fi

3378 ### err rowowner=$rowowner

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

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

3381 return 2

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

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

3384 return 3

3385 fi

3386 rm=`getpar rm` cfm=`getpar confirm`

3387 # Editing existent entry

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

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

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

3391 return 4

3392 else

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

3394 return 5

3395 fi

3396 fi

3397 fi

3398

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

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

3401 # Update of existing record

3402 for col in `gettblcols $tbl`; do

3403 val=`getparquote $col`

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

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

3406 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3407 sql="update $tbl set $col=$val where rowid=$rowid;"

3408 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3409 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3410 ## User name cannot be changed with interface provided with this

3411 ## script. But we offer the trigger to change owner user

3412 ## of blog_s table.

3413 #err "select quote($col) from $tbl where rowid=$rowid;"

3414 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3415 cat<<-EOF | query

3416 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3417 -- should use EXCLUSIVE transaction outside of this.

3418 SAVEPOINT par2table;

3419 $sql

3420 update blog_s set val=$val

3421 where key='owner' and val=$old;

3422 RELEASE SAVEPOINT par2table;

3423 EOF

3424 ## XX: DIRTY Hack Ends here

3425 ## We should keep blog's owner as a single column which has

3426 ## foreign key constraint with primary key of grp/user.

3427 else

3428 query "$sql"

3429 fi

3430 done

3431 # Then, set up $pval for further insertion of tbl_s and tbl_m

3432 for col in `gettblpkey $tbl`; do

3433 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3434 pval="$pval${pval:+, }\"$val\""

3435 done

3436 else

3437 # New entry

3438 # Generate values() for primary keys

3439 for col in `gettblpkey $tbl`; do

3440 # Genuine primary keys for _m and _s

3441 val=`getvalquote $tbl $col`

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

3443 pval="$pval${pval:+, }$val"

3444 done

3445 ##err pval=$pval

3446 for col in `gettblfkey $tbl`; do

3447 # args for values() to insertion into master table

3448 val=`getvalquote $tbl $col`

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

3450 formaster=$formaster"${formaster:+, }$val"

3451 done

3452 formaster="$pval${formaster:+, }$formaster"

3453 ## err formaster=$formaster

3454 if [ -z "$formaster" ]; then

3455 echo "項目を全て埋めてください" | html pre

3456 return 1

3457 fi

3458 ## err "replace into $tbl values($formaster);"

3459 query "replace into $tbl values($formaster);"

3460 ## Insertion to master table, done

3461 fi

3462

3463 for kt in s m; do

3464 tb2=${tbl}_$kt

3465 for col in `gettbl_${kt}_cols $tbl`; do

3466 ptype=`getpartype $col "limit 1"`

3467

3468 # First, check update of existing entries in _m

3469 if [ $kt = m ]; then

3470 # sessID|address.1.22|string|Somewhere-x.y.z

3471 sql=""

3472 ##err dots from query "select var from par where var like '$col.%';"

3473 for v in `query "select var from par where var like '$col.%';"`; do

3474 # v=address.1.22

3475 st_rowid=${v##*.}

3476 origcol=${v%%.*} # original column derived from

3477 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3478 ##case `getpartype $v` in

3479 ## err CASE `gettbl_coltype $tbl/$origcol` in

3480 ## err edit flag = `getpar action.$v`

3481 case `getpar action.$v` in

3482 rm)

3483 if [ x`getpar confirm.$v` = x"yes" ]; then

3484 newsql="delete from $tb2"

3485 else

3486 echo "削除確認未チェック" | html p

3487 fi ;;

3488 edit)

3489 case `gettbl_coltype $tbl/$origcol` in

3490 image|document|binary)

3491 file=$tmpd/`getparfilename $v`

3492 ## err type=file=$file

3493 [ -z "$file" ] && continue

3494 bn=`sqlquotestr "${file##*/}"`

3495 bin="X'"$(hexize "$file")"'"

3496 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3497 type=\"file:$ct\"

3498 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3499 cachedir=`getcachedir "$tbl/$rowid"`

3500 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3501 rm -rf $cachedir

3502 ;;

3503 *)

3504 newsql="update $tb2 set val=(select val from par where var \

3505 like '$col.%.$st_rowid')"

3506 ;;

3507 esac

3508 ;;

3509 *) # maybe "keep", do not modify value

3510 continue

3511 ;;

3512 esac

3513 # err newsql=$newsql

3514 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3515 done

3516

3517 if [ x"$bin" = x"NULL" ]; then

3518 ## err repl:normal sql=`echo $sql`

3519 query "$sql

3520 delete from $tb2 where type='string' and val='';"

3521 ## err repl:normal done

3522 else

3523 sqlfile="$tmpd/sqlf.$$"

3524 echo "$sql" > $sqlfile

3525 ## err repl:sqlfile=`ls -lF $sqlfile`

3526 query ".read $sqlfile"

3527 ## err repl:done

3528 fi

3529 # Rest of kt==m: set multiple mode

3530 nr=`getparcount $col`

3531 else

3532 nr=1 # for kt==s, number of records is 1

3533 fi

3534

3535 i=0

3536 while [ $i -lt $nr ]; do

3537 limit="limit 1 offset $i"

3538 i=$((i+1)) # increase beforehand against continue

3539 val=`getvalquote $tbl $col "$limit"`

3540 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3541 ## err $col=$val

3542 bin=NULL

3543 ## err partype$col=`getpartype $col "$limit"`

3544 case $ptype in

3545 file) file=$tmpd/`getparfilename $col "$limit"`

3546 ## err parfile-$col=$file

3547 [ -z "$file" ] && continue

3548 bin="X'"$(hexize "$file")"'"

3549 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3550 type=\"file:$ct\" ;;

3551 "*"*) continue ;; # foreign table

3552 *) type=\"string\" ;;

3553 esac

3554 case `gettbl_coltype $tbl/$col` in

3555 password) # special care for password

3556 # name={password,pswd1,pswd2}

3557 p1=`getpar pswd1 "$limit"`

3558 if [ -z "$p1" ]; then

3559 continue # SKIP password setting, if p1 is empty

3560 else

3561 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3562 ## err pswd=$pswd

3563 if pwcheck "$pswd"; then

3564 if [ x"$p1" = x"$p2" ]; then

3565 case "$p1" in

3566 ??????????*) ;;

3567 *) echo "パスワードは10字以上にしてください。" | html p

3568 return 6;;

3569 esac

3570 val="\"`echo $p1|mypwhash`\""

3571 else

3572 echo "2つの新パスワード不一致" | html p

3573 return 7

3574 fi

3575 else

3576 echo "旧パスワード違います" | html p

3577 return 8

3578 fi

3579 fi

3580 ;;

3581 esac

3582 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3583 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3584 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3585 if [ x"$bin" = x"NULL" ]; then

3586 ## err Normal-query: `echo $sql`

3587 query "$sql"

3588 else

3589 sqlfile="$tmpd/query.$$"

3590 echo "$sql" > $sqlfile

3591 ## err sqlfile=`ls -lF $sqlfile`

3592 query ".read $sqlfile"

3593 fi

3594 ## err p2t done

3595 done

3596 done

3597 done

3598 return 0

3599 ##err donee

3600 )

3601 genform() {

3602 # $1 = form definition file

3603 # $2, $3 (optional)= table name and ROWID

3604 # If $GF_VIEWONLY set and nonNull, output values without form

3605 # If $GF_ARGS set, use it as content-strings in the form

3606 # If $GF_OWNER set, use it as value of name="owner"

3607 # If $GF_STAGE set, use it as value of name="stage"

3608 forms="" hiddens="" rowid=$3

3609 if [ ! -e "$1" ]; then

3610 echo "そのようなデータベースはないようです($2)。" | html p

3611 return

3612 elif [ -n "$2" ]; then

3613 rec=`query "select * from $2 where rowid='$rowid';"`

3614 if [ -z "$rec" ]; then

3615 pk=`gettblpkey $2`

3616 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3617 rec=`query "select rowid from $2 where $pk='$rowid';"`

3618 rowid=$rec

3619 rec=$3

3620 fi

3621 if [ -z "$rec" ]; then

3622 echo "そんなレコードはないみたいね..." | html p

3623 return

3624 fi

3625 fi

3626 if [ -z "$GF_VIEWONLY" ]; then

3627 rm='<input id="rm" name="rm" type="checkbox"

3628 value="yes"><label for="rm">このエントリの削除</label>

3629 <span>ほんとうに消しますよ(確認)!

3630 <input name="confirm" type=checkbox value="yes">はい</span>'

3631 fi

3632 # Image Cache dir

3633 ## err genform: getcache=$2/$rowid

3634 td=`getcachedir "$2/$rowid"`

3635 while IFS=: read prompt name keytype type args; do

3636 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3637 sp="${args:+ }"

3638 form="" val=""

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

3640 # err genform2a: Seeking for "$2.$name, type=$type"

3641 rawval=`getvalbyid $2 $name $rowid $td`

3642 val=`echo "$rawval"|htmlescape`

3643 ## err genform3a: getvalbyid $2 $name $rowid $td

3644 ## err genform3b: val="[$val]" type="$type"

3645 fi

3646 if [ -n "$GF_VIEWONLY" ]; then

3647 is_hidden "$2" "$name" && continue

3648 fi

3649 case "$type" in

3650 text*)

3651 cgiform=cgi_multi_$type

3652 if [ -s $td/$name.count -a -n "$val" ]; then

3653 form=`$cgiform $name $td`

3654 val=$(echo "$val"|

3655 while read fn; do

3656 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3657 </td></tr>$nl"

3658 done)

3659 val="<table>$nl$val$nl</table>"

3660 else

3661 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3662 form=`cgi_$type $name "$rawval" "$args"`

3663 fi

3664 ;;

3665 [Rr][Aa][Dd][Ii][Oo])

3666 fh="<label><input type=\"radio\" name=\"$name\""

3667 form="`echo $args|sed -e \

3668 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3669 ;;

3670 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3671 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3672 ;;

3673 [Ss][Ee][Ll][Ee][Cc][Tt])

3674 fh="<select name=\"$name\">$nl"

3675 form=$(for l in $args; do

3676 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3677 done)

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

3679 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3680 fi

3681 form="$fh$form</select>"

3682 ;;

3683 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3684 if [ -s $td/$name.count ]; then

3685 form=`cgi_multi_file $name $td "$args"`

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

3687 hrfb="$myname?showattc+$2_m"

3688 val=$(echo "$rawval" \

3689 | while read fn; do

3690 data=`percenthex "$td/$fn"`

3691 #ct=`cat $td/$fn.content-type`

3692 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3693 ri=`cat "$td/$fn.rowid"`

3694 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3695 #imgsrc="<img src=\"data:$ct,$data\">"

3696 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3697 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3698 done)

3699 fi

3700 else

3701 form="<input type=\"file\" name=\"$name\" $args>"

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

3703 imgs=$(echo "$rawval"\

3704 |while read fn;do

3705 data=`percenthex "$td/$fn"`

3706 echo "<img src=\"data:image/png,$data\">$fn<br>"

3707 done)

3708 form=$form"<br>$imgs"

3709 val=$imgs # 2015-06-15

3710 else

3711 form="<input type=\"file\" name=\"$name\" $args>"

3712 fi

3713 fi

3714 ;;

3715 [Hh][Ii][Dd][Dd][Ee][Nn])

3716 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3717 args="value=\"$GF_STAGE\""

3718 fi

3719 form="<input type=\"hidden\" name=\"$name\" $args>"

3720 prompt='' # Remove prompt

3721 ;;

3722 [Aa][Uu][Tt][Hh][Oo][Rr])

3723 [ -n "$GF_VIEWONLY" ] && continue

3724 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3725 prompt="" ;;

3726 [Oo][Ww][Nn][Ee][Rr])

3727 [ -n "$GF_VIEWONLY" ] && continue

3728 val=${GF_OWNER:-$val}

3729 val=${val:-$user}

3730 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3731 prompt="" ;;

3732 [Uu][Ss][Ee][Rr])

3733 # XXX: is null $user ok?

3734 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3735 [ -n "$GF_VIEWONLY" ] && continue

3736 form="$user"

3737 ;;

3738 [Pp]assword)

3739 [ -n "$GF_VIEWONLY" ] && continue

3740 form="`cgi_passwd`"

3741 val=""

3742 ;;

3743 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3744 [ -n "$GF_VIEWONLY" ] && continue

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

3746 val=`genserial`

3747 fi

3748 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3749 prompt="" ;;

3750 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3751 prompt=""

3752 ;;

3753 parent|path|blog*)

3754 prompt=""

3755 ;;

3756 "*"*)

3757 tail=$tail"``"

3758 continue ;;

3759 esac

3760 if [ -n "$prompt" ]; then

3761 if [ -n "${GF_VIEWONLY}" ]; then

3762 form=$val

3763 else

3764 :

3765 fi

3766 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3767 else

3768 hiddens=$hiddens$nl"$form"

3769 fi

3770 done < $1

3771 # enctype="multipart/form-data"

3772 cat<<EOF

3773 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3774 ${rowid:+$rm}

3775 <table class="b $2">

3776 $forms

3777 </table>$hiddens

3778 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3779 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3780 EOF

3781 if [ -z $GF_VIEWONLY ]; then

3782 cat<<EOF

3783 <input type="submit" name="sub" value="OK">

3784 <input type="reset" name="res" value="Reset">

3785 EOF

3786 fi

3787 cat<<EOF

3788 $GF_ARGS</form>

3789 $tail

3790 EOF

3791 }

3792 edittable() {

3793 # $1=form-def $2=table $3 rowid

3794 genform "$@"

3795 }

3796 viewtable() {

3797 GF_VIEWONLY=1 genform "$@"

3798 }

3799 showattc() {

3800 # $1=table_m $2=rowid &optional $3=RawFlag

3801 ## err \$1=$1 \$2=$2 \$3=$3

3802 if ! isfilereadable $user $1 $2; then

3803 contenttype; echo

3804 echo "このファイルは管理者のみしか見られません" | html p

3805 putfooter; exit

3806 fi

3807 idir=`umask 002; mktempd` || exit 1

3808 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3809 bin=$idir/$myname-$$.bin

3810 sql="select quote(bin) from $1 where rowid='$2';"

3811 ## err showattc: sql: $sql

3812 sq $db "$sql" | unhexize > $bin

3813 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3814 type=${tv%//*} fn=${tv#*//}

3815 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3816 ct=${type#file:}

3817 case $ct in # all text/* changed to text/plain

3818 text/*)

3819 charset=`nkf -g $bin|cut -d' ' -f1`

3820 case $charset in

3821 ASCII*) charset="" ;;

3822 esac

3823 if [ -z "$3" ]; then

3824 ct="text/html${charset:+; charset=$charset}"

3825 link="?showattc+$1+$2+raw"

3826 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3827 | sed 's,^,<span></span>,' \

3828 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3829 -D_LINK_="$link" \

3830 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3831 exit $?

3832 fi

3833 ct="text/plain${charset:+; charset=$charset}"

3834 ;;

3835 esac

3836 contenttype "$ct"

3837 echo "Content-Disposition: filename=\"$fn\""

3838 echo "Content-Length: " `cat $bin | wc -c`; echo

3839 #echo "Content-Type: " ${type#file:}; echo

3840 cat $bin

3841 }

3842 #

3843 # Some default stupid handler on CGI values

3844 #

3845 default_storedb() {

3846 # ARG: $1=table-def-file

3847 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3848 tbl=`basename $1`

3849 tbl=${tbl%.def}

3850 cols="`grep :text $1|cut -d: -f2`"

3851 col=`echo "$cols"|head -1`

3852 vcol=`getpar $col`

3853 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3854 if [ -n "$vcol" ]; then

3855 par2table $1

3856 else

3857 return 2 # No insertion occurred

3858 fi

3859 }

3860

3861 default_view() { # $1=def-file

3862 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3863 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3864 default_storedb "$@"

3865 query "select rowid from $tbl order by rowid desc;" \

3866 | while read rowid; do

3867 viewtable $1 $tbl $rowid

3868 done | _m4 -D_TITLE_="$tbl" \

3869 -D_FORM_="`genform $1`" \

3870 -D_DUMPTABLE_="syscmd(cat)" \

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

3872 }

3873 default_viewtext() { # $1=def-file

3874 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3875 default_storedb "$@"

3876 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3877 | _m4 -D_TITLE_="$tbl" \

3878 -D_FORM_="`genform $1`" \

3879 -D_DUMPTABLE_="syscmd(cat)" \

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

3881 }

3882 default_smail() {

3883 default_storedb "$@"

3884 if [ $? -eq 2 ]; then

3885 _m4 -D_TITLE_="入力" \

3886 -D_FORM_="`genform $1`" \

3887 -D_DUMPTABLE_="" \

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

3889 return

3890 fi

3891 cond=""

3892 for pk in `gettblpkey $tbl`; do

3893 pv=$(sqlquote "$(getpar $pk)")

3894 cond="$cond${cond:+ and }$pk=$pv"

3895 done

3896 sql="select rowid from $tbl where $cond;"

3897 rowid=`query "$sql"`

3898 ## err smail1 - "$sql" "-> rowid=$rowid"

3899

3900 while IFS=: read prompt name keytype type args; do # Read from $1

3901 val=`getpar $name`

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

3903 text="$text

3904 $prompt

3905 $name=$val

3906 ---------------------------------------------------------"

3907 fi

3908 case "$type" in

3909 image|document|file)

3910 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3911 fns=$(echo "$fn"|while read fn; do

3912 err mv $tmpd/$fn.orig $tmpd/$fn

3913 mv $tmpd/$fn.orig $tmpd/$fn

3914 rm $tmpd/$fn.rowid # Remove cache flag

3915 ## err "`ls $tmpd/$fn`"

3916 echo $fn

3917 done)

3918 files="$files $fns"

3919 ;;

3920 esac

3921 done < $1

3922 ## err FILES=$files "`ls -lF $tmpd`"

3923 subj="from ${REMOTE_ADDR}"

3924 (echo "$url"

3925 echo "への書き込みがありました。"

3926 echo "------"

3927 echo "$text"

3928 ) | (cd $tmpd &&

3929 err LS="`ls -lF`" &&

3930 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3931 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3932 echo "以下の内容で送信しました。" | html p

3933 viewtable $1 $tbl \

3934 `query "select rowid from $tbl order by rowid desc limit 1;"`

3935 echo "戻る" | html a "href=\"?\""

3936 }