s4: a50e83239b51 s4-funcs.sh

s4

view s4-funcs.sh @ 592:a50e83239b51

Add "enquete" mode

author	HIROSE Yuuji <yuuji@gentei.org>
date	Tue, 03 Dec 2019 11:41:39 +0900
parents	dfa90fbc05bd
children	7ece8cc62a7a

line source

1 #!/bin/sh

2 # Here's global variable table. Do not use this names.

3 # $HGid$

5 [ -f s4-config.sh ] && . ./s4-config.sh

7 myname=`basename ${SCRIPT_NAME:-$0}`

8 mydir=`dirname ${SCRIPT_FILENAME:-$0}`

9 myargs="$@"

10 test -n "$HTTP_HOST" && isCGI=true

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

12 tmpdir=${TMPDIR:-tmp}

13 dbdir=${DBDIR:-db}

14 tmpfiles=""

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

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

17 templ=${TEMPL:-templ}

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

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

20 imgdir=${IMGDIR:-img}

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

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

23 msgdir=$templ/msg

24 timeout="+2 days"

25 memoplimitdays="7"

26 dumpcollen=22

27 #thumbxy=120x120

28 thumbxy=96x96

29 iconxy_S=80x80

30 iconxy_M=400x400

31 maximagexy=1600x1600

32 ### maximagexy=400x400

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

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

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

37 file_accept_help="

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

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

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

41 "

42 file_warn="$file_accept_help

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

44 blogreadflagrowid=0

45 blogcutoffflagrowid=-1

46 querylog=$tmpdir/query.log

47 searchlog=$tmpdir/search.log

49 tconfs=""

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

51 conftbl=_tblconf

52 nl="

53 "

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

55 iconcachekey="profimgcache_S"

56 case "$HTTP_USER_AGENT" in

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

58 *) touchpanel="" ;;

59 esac

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

62 : <<EOF

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

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

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

67 レポート提出管理

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

70 [2016]

71 7/12 根本への反省

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

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

75 [以下2015]

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

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

78 Web

79 締切設定

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

82 ○自分の提出物リスト

84 7/19 ○設置

85 ○一斉送信

86 ○getparfilename の tmpd の扱い

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

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

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

90 ○添付ファイル回収

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

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

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

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

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

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

98 ○レポート提出モード

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

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

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

102

103

104 7/9 ○管理者の追加

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

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

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

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

109

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

111

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

113 ○グループ検索

114

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

116

117

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

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

120

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

122 ○6/1 par2tableを triplex 対応に

123 select "yuuji@gentei.org",var,"text",NULL,val from par where var in (select col from _tblconf where tbl="/user" and keytype in ('p', 's'));

124 →とすると一気に

125

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

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

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

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

130

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

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

133 ## 1. passwd

134 ## 2. grp

135 ## 3. grp_mem

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

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

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

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

140

141 ## ● listの定義:

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

143 ## ● hashの定義:

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

145

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

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

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

149 ## csv2sq3 で .csv.sq3 の Makefile

150

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

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

153 ## type := root | comment

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

155 ## unique(id, filename)

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

157

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

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

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

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

162 ## できたー!

163 ## with recursive allmem as (select * from grp_mem where gname='bar' union all select grp_mem.* from grp_mem,allmem where allmem.name=grp_mem.gname) select * from allmem where type='u';

164 ↓

165 ↓以下に変更

166 with recursive allmem as

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

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

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

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

171

172

173 with recursive allmem as

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

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

176 where allmem.val=grp_m.gname)

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

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

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

180

181

182 ## triggerもできた。

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

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

185

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

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

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

189 ## グループ管理

190 ## 個人情報管理

191 ##

192

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

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

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

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

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

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

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

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

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

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

203

204 ## ログテーブル

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

206

207

208

209 ■表設計

210 * 3つの表に分散管理

211 id格納表 + hash表 + list表

212 * *_s *_m

213

214

215

216 user, user_map, user_col

217

218 ■抽象エントリタイプ

219 * user

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

221 * group

222 権限判定に利用

223 * serial

224 自動idとして機能

225 * password

226 入力 type=passwordで入力

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

228 * session

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

230 * text

231 入力 type=text

232 * textarea

233 入力 textarea

234 * image|document

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

236 * owner

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

238 * gowner

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

240 * timestamp

241 datetime()

242 * parent

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

244 * path

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

246

247 格納タイプ

248 * list

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

250 * hash

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

252

253 オブジェクトタイプ

254 * entry

255 id, title, owner

256 * textpart

257 id, parentID, text

258 * binarypart

259 id, parentID, contenttype, filename, content

260 * content

261 hash(textpart), list(binarypart)

262 * topic

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

264 * reply

265 id, parentID, content

266 * blog

267 list(entry)

268 blog = [topic, list(reply)]

269

270

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

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

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

274 "author" => "taro",

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

276 "parent" => "/",

277 "path" => "/1",

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

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

280 {"serial" => 2,

281 "author" => "hanako",

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

283 "parent" => "/",

284 "path" => "/2",

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

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

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

288

289

290 user:=

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

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

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

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

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

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

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

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

299

300 変換表

301 /user/email=m

302

303 blog:=

304 シリアル:id:p:serial

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

306 所有者:owner:s:owner:

307 時刻:ctime:s:stamp:

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

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

310

311 article:=

312 シリアル:id:p:serial

313 筆者:author:s:owner

314 時刻:ctime:s:stamp:

315 参照元:parent:s:parent:

316 パス:path:s:path:

317 本文:text:s:textarea:

318 画像:image:m:image:

319

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

321

322

323 EOF

324

325 logstart() {

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

327 }

328 logend() {

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

330 }

331 sqlog() {

332 logstart

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

334 cat >> $querylog

335 else

336 echo "$*" >> $querylog

337 fi

338 logend

339 }

340 sq() {

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

342 logstart

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

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

345 else

346 echo "$@" >> $querylog

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

348 fi

349 logend

350 }

351 dbsetup() {

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

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

354 sqi=$tmpdir/sqi.$$

355 sqo=$tmpdir/sqo.$$

356 mkfifo $sqi $sqo

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

358 sq $db < $sqi &

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

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

361 exec 2>> $tmpdir/error.out

362 fi

363 exec 3>> $tmpdir/debug.out

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

365 rm $sqi

366 }

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

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

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

370 fi

371 }

372 cleanup() {

373 trap '' INT HUP EXIT TERM PIPE

374 echo .quit >&5

375 kill $sq3pid

376 kill $sq3pid

377 rm -f $sqo $sqi

378 rm -rf $tmpfiles

379 cleanup2

380 }

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

382 # simple redirection for the sake of speed.

383 query() {

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

385 echo ".output $sqo" >&5

386 logstart

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

388 tee -a $querylog

389 else

390 echo "$@" >> $querylog

391 echo "$@"

392 fi >&5

393 echo ".output stdout" >&5

394 cat $sqo

395 logend

396 }

397 _m4() {

398 #S4NAME=f,f,f

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

400 }

401 ismember() {

402 # $1=user, $2=group

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

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

405 }

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

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

408 }

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

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

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

412 }

413 isgrpowner() (

414 # $1=user, $2=group

415 gn=`sqlquote "$2"`

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

417 err isgrpowner: $sql

418 test -n "`query $sql`"

419 )

420 isgrpownerbygid() (

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

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

423 err isgrpownerbygid: $sql

424 test -n "`query $sql`"

425 )

426 getgroupadminmails() {

427 # $1=group

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

429 email4group "$1" "$i" ;

430 done

431 }

432 getgroupadmins() { # $1=group

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

434 qgrp=`sqlquote "$1"`

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

436 }

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

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

439 getvalbyid grp $2 \

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

441 }

442 getgroupbyid() {

443 # $1=id|gname

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

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

446 # err ggbyid: `echo $sql`

447 query $sql

448 }

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

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

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

452

453 # Return true when anonymous mode

454 [ "$anonymousmode" ] && return 0

455 # case `getvalbyid blog mode $2` in

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

457 # *closed*)

458 # owner=`getvalbyid blog owner $2`

459 # if isgrp $owner; then

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

461 # elif isuser $owner; then

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

463 # fi

464 # esac

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

466 sql="with getblog as (\

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

468 select blogid from article where id in\

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

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

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

472 select case\

473 when (select author from article where\

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

475 then 'author'\

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

477 then 'open'\

478 when (select val from getmode) is null \

479 then 'open'

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

481 then (select user from grp_adm where \

482 gname=(select val from getowner) and \

483 user='$1')\

484 when (select author from article where\

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

486 then 'user+author'

487 else '' end;"

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

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

490 result=`query "$sql"`

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

492 return 2

493 }

494 linkhome() {

495 # $1=UserOrGroup

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

497 if isuser $1; then

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

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

500 else

501 echo -n "grp+$1"

502 fi

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

504 }

505 hreflink() {

506 # s4 specific notation:

507 # ^href=URL

508 # ^iframe=URL

509 # ^video=URL

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

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

512 # OSM umap Wikistyle Notation:

513 # [[URL]] - Simle Link

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

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

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

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

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

519 # Other Style

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

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

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

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

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

525 # SPC+SPC+$ - <br>

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

527 checkboxON="${cb} checked>"

528 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

553

554 }

555 minitbl() {

556 sed -n '

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

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

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

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

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

562 H; # Concat this line to HoldSpace

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

564 $ b cont

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

566 }

567 :cont

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

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

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

571 # . of ^. is workaround for FreeBSD sed

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

573 p; # Print whole "table" element

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

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

576 }

577 x; # Back to the newest line

578 p; # Print rest' | miniul

579 }

580 miniul() {

581 sed -e '

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

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

584 :top

585 s/\n//;

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

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

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

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

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

591 $ b cont

592 N; # 次の行を読む

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

594 /^ /b top

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

596 p; s/.*//;

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

598 /^\* /b top

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

600 }

601

602 :cont

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

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

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

606 H; x

607 }

608 x'

609 }

610 acclog() (

611 # $1=table, $2=rowid

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

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

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

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

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

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

618 fi

619 )

620 gecos() (

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

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

623 )

624 setpar() {

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

626 }

627 replpar() {

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

629 }

630 getpar() {

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

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

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

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

635 fi

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

637 case "$var" in

638 owner)

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

640 echo $user; return

641 elif ismember $user $val; then

642 echo $val; return

643 fi ;;

644 esac

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

646 echo "$val"

647 }

648

649 getpartype() {

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

651 }

652 getparcount() {

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

654 }

655 getparfilename() {

656 # null if type of $1 is not file

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

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

659 }

660 sqlquote() {

661 (v="$1"

662 case "$v" in

663 "") return ;; # null

664 "X'"*) # quoted hex string

665 echo $1 ;;

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

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

668 echo "\"$v\""

669 return ;;

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

671 echo "\"$v\""

672 return ;;

673 *)

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

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

676 else

677 echo "\"$v\""

678 fi ;;

679 esac)

680 }

681 sqlquotestr() (

682 case "$1" in

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

684 echo "'$v'" ;;

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

686 esac

687 )

688 mktempd() {

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

690 }

691 getcachedir() { # $1=maintable

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

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

694 else

695 echo $tmpd/$thumbxy

696 fi

697 }

698 getval() {

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

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

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

702 echo "$user" ;;

703 stamp|datetime)

704 date "+%F %T" ;;

705 serial)

706 (s=`getpar $2`

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

708 *)

709 getpar "$2" "$3";;

710 esac

711 }

712

713 getvalquote() {

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

715 (v=`getval "$@"`

716 case "$v" in

717 "") echo NULL ;;

718 *) sqlquote "$v" ;;

719 esac)

720 }

721 getparquote() {

722 sqlquote "`getpar $1`"

723 }

724 getbinbyid() {

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

726

727 }

728 getvalbyid() {

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

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

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

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

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

734

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

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

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

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

739 return

740 fi

741 done

742 rowid=$3

743 pk=`gettblpkey $1`

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

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

746 td=${4:-$tmpd}

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

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

749 for kt in s m; do

750 t=${1}_$kt

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

752 vcount=1 # count(val)

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

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

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

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

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

758 if [ $kt = m ]; then

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

760 # Reset val to store filenames if type is string

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

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

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

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

765 i=0

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

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

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

769 i=$((i+1))

770 fn=$c.$i

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

772 case $type in

773 file:*)

774 #file=$td/$val

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

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

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

778 # FOR SPEED: Skip file generation if imgcache exists

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

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

781 && continue

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

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

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

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

786 select rowid from $cond $slice;

787 .output '$td/$fn'

788 select val from $cond $slice;

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

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

791 .output stdout

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

793 EOF

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

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

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

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

798 ls -lh "$file" |

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

800 case "$type" in

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

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

803 *|*:[Aa]pplication*)

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

805 png:- > "$file"

806 ;;

807 esac

808 ;;

809 *)

810 sq $db<<EOF

811 .output $td/$fn.rowid

812 select rowid from $cond $slice;

813 .output $td/$fn

814 select val from $cond $slice;

815 EOF

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

817 ;;

818 esac

819 done

820 else

821 rm -f $td/$c.count

822 case $type in

823 file:*)

824 echo "$val" \

825 | while read fn; do

826 file=$td/$fn

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

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

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

830 | unhexize > "$file"

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

832 case $type in

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

834 *:[Aa]pplication*)

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

836 png:- > $file ;;

837 esac

838 fi

839 done

840 ;;

841 esac

842 fi

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

844 return

845 fi

846 done

847 done)

848 }

849 getvalbypkey() (

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

851 pk=`gettblpkey $1`

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

853 getvalbyid "$1" "$2" $rowid $4

854 )

855 getvalbycond() {

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

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

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

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

860 getvalbyid "$1" "$2" $rowid "$4"

861 fi

862 }

863 getpwfield() {

864 # getpwfield user column

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

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

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

868 echo "$val"

869 return 0

870 else

871 return 1

872 fi

873 }

874 numericalize() {

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

876 }

877 encode() {

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

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

880 sha1=sha1

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

882 sha1=sha1sum

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

884 sha1=gsha1sum

885 fi

886 fi

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

888 }

889 enjpeg() {

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

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

892 cjpeg="cjpeg"

893 else

894 cjpeg="convert - jpeg:-"

895 fi

896 fi

897 $cjpeg "$@"

898 }

899 mycrypt() (

900 key=$1 salt=$2

901 err \$2=$2

902 case $2 in

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

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

905 esac

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

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

908 )

909 hexize() {

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

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

912 hexize="xxd -p"

913 else

914 hexize_hd() {

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

916 }

917 hexize="hexize_hd"

918 fi

919 fi

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

921 }

922 unhexize() {

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

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

925 unhex="xxd -p -r"

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

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

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

929 EOF

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

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

932 # world writable...:)

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

934 fi

935 fi

936 cat "$@" | $unhex

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

938 }

939 percenthex() {

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

941 }

942 htmlescape() {

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

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

945 }

946 enascii() {

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

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

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

950 else

951 enascii_now=`date +%FT%T`

952 enascii_sed() {

953 nkf -Z0Z1Z2 \

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

955 }

956 enascii="enascii_sed"

957 fi

958 fi

959 cat "$@" | $enascii

960 }

961 size_h() {

962 i="$1" oi=$1

963 set -- B B KB MB GB TB

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

965 oi=$i

966 i=$((i/1024))

967 shift

968 done

969 echo ${oi}$1

970 }

971 gettblconf() {

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

973 ## tconfs=`sq $db \

974 tconfs=`query \

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

976 fi

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

978 }

979 gettblkeys() {

980 # $1=tbl

981 gettblconf

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

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

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

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

986 case $type in

987 *p*)

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

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

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

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

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

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

994 esac

995 case $type in

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

997 esac

998 done

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

1000 }

1001 gettblpkey() {

1002 # $1=tbl

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

1004 }

1005 gettblfkey() {

1006 (x=`gettblkeys $1`

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

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

1009 }

1010 gettblcols() {

1011 (x=`gettblkeys $1`

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

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

1014 }

1015 gettbl_s_cols() {

1016 (x=`gettblkeys $1`

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

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

1019 }

1020 gettbl_m_cols() {

1021 (x=`gettblkeys $1`

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

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

1024 }

1025 gettbl_h_cols() {

1026 (x=`gettblkeys $1`

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

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

1029 }

1030 gettbl_coltype() (

1031 gettblconf

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

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

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

1035 )

1036 is_hidden() {

1037 # $1=Tbl $2=col

1038 gettblconf

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

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

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

1042 case $x in

1043 *h*) return 0 ;;

1044 *) return 1 ;;

1045 esac

1046 }

1047

1048 dbsetbyid() {

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

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

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

1052 quotedp=$(sqlquotestr "$p")

1053 unset primary update

1054 gettblconf

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

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

1057 #err conf=$conf

1058 case ${conf#*=} in

1059 p*) primary=1 ;;

1060 f*) update=1 ;;

1061 u*) ;;

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

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

1064 esac

1065 #err t=$t

1066 type=string fn=""

1067 case $conf in

1068 */password)

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

1070 ;;

1071 */image*|*/document*)

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

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

1074 ;;

1075 esac

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

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

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

1079 if [ "$primary" ]; then

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

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

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

1083 elif [ "$update" ]; then

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

1085 else

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

1087 fi

1088 )

1089 }

1090 expire() (

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

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

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

1094 )

1095 addsession() {

1096 # expireをセット

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

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

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

1100 # Remove old session parameters

1101 now=`expire now`

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

1103 }

1104 gencookie() (

1105 for kv; do

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

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

1108 done

1109 )

1110 contenttype() {

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

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

1113 }

1114 putheader() {

1115

1116 }

1117 putfooter() {

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

1119 }

1120 getcookie() (

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

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

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

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

1125 done

1126 )

1127 genrandom() {

1128 # $1=columns (default: 10)

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

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

1131 }

1132 genserial() {

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

1134 }

1135 smail() {

1136 # smail rcpts subj (file)

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

1138 # $MAIL_FROM <- From: header value

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

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

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

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

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

1144 }

1145 setviastring() {

1146 table=$1

1147 oifs="$IFS"

1148 IFS="&"

1149 for us in $2; do

1150 k=${us%%=*}

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

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

1153 #echo $k=$v

1154 done

1155 IFS="$oifs"

1156 }

1157 checkdomain() (

1158 # Check the validity of domain by referring DNS

1159 item=$1

1160 err checkdomain $1

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

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

1163 )

1164 pwcheck() {

1165 # $1=passwd

1166 dbpswd=`getpwfield $user pswd`

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

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

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

1170 }

1171 mypwhash() {

1172 mycrypt "`cat`" `genrandom 5`

1173 }

1174 wasureta() {

1175 user=$1

1176 if ! checkdomain $user; then

1177 contenttype; echo

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

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

1180 putfooter

1181 exit 0

1182 fi

1183 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1185 encpswd=`echo $newpswd|mypwhash`

1186 dbsetbyid user $user pswd "$encpswd"

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

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

1189 $msgdir/mail-newaccount.m4 \

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

1191 | smail $user "New Account"

1192 }

1193 checkauth() {

1194 user=`getpar user`

1195 skc=`getpar skey` # from cookie

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

1197 skey="`getpwfield $user skey`"

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

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

1200 return 0

1201 fi

1202 fi

1203 pswd=`getpar pswd`

1204 quser=`sqlquotestr "$user"`

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

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

1207 return 1

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

1209 wasureta "$user"

1210 return 1 # wasureta error

1211 fi

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

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

1214 if pwcheck "$pswd"; then

1215 newsession=`genrandom 50`

1216 dbsetbyid user "$user" skey "$newsession"

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

1218 gencookie "user=$user" "skey=$newsession"

1219 return 0

1220 fi

1221 return 2 # Password mismatch

1222 }

1223 showlogin() {

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

1225 s4name=${S4NAME:-s4}

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

1227 cat $layout/login.m4.html

1228 echo '</body></html>'

1229 ) | _m4 \

1230 -D_BODYCLASS_="login" \

1231 -D_TITLE_="$s4name" \

1232 -D_SYSNAME_="Welcome to $s4name" \

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

1234 exit 0

1235 }

1236 dologin() {

1237 checkauth

1238 st=$?

1239 if [ $st != 0 ]; then

1240 contenttype; echo

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

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

1243 showlogin # and EXIT

1244 fi

1245 }

1246

1247 # Do instant jobs here

1248 dbsetup

1249 trap cleanup INT HUP EXIT TERM PIPE

1250 # trap cleanup INT HUP

1251

1252 err() {

1253 echo "$@" 1>&3

1254 }

1255

1256 cgiinit() {

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

1258 tmpf=tmp/stream

1259 tmpd=`tmpd=$tmpdir mktempd`

1260 tmpfiles=$tmpfiles" $tmpd"

1261 addsession $session

1262 getcookie

1263 case "$REQUEST_METHOD" in

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

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

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

1267 # cat > $tmpf # too much?

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

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

1270 s="`cat tmp/stream`"

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

1272 ;;

1273 esac

1274 case "$CONTENT_TYPE" in

1275 *boundary*)

1276 bndry=${CONTENT_TYPE#*boundary=}

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

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

1279 do

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

1281 #echo u=$us

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

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

1284 # err k=$k v=$v

1285 case "$k" in

1286 *:filename)

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

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

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

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

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

1292 :

1293 fi

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

1295 case "$mimetype" in

1296 [Ii]mage/x-xcf)

1297 bzip2 "$tmpd/$v"

1298 v=${v}.bz2

1299 ;;

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

1301 [Ii]mage/*)

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

1303 ;;

1304 esac

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

1306 then

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

1308 continue

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

1310 replpar text string \

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

1312 continue

1313 fi

1314 ;;

1315 *)

1316 type='string'

1317 ;;

1318 esac

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

1320 setpar "$k" "$type" "$v"

1321 done

1322 ;;

1323 *)

1324 setviastring par "$s"

1325 ;;

1326 esac

1327 }

1328 email4group() {

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

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

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

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

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

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

1335 and s.key='email'

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

1337 query "$sql"

1338 }

1339 email4groupbyuid() {

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

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

1342 uids=`echo "$@"`

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

1344 sql="WITH

1345 grpemails AS (

1346 SELECT gname, user, val email

1347 FROM grp_mem NATURAL JOIN grp_mem_s

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

1349 useremails AS (

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

1351 FROM user

1352 LEFT JOIN user_m

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

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

1355 FROM useremails u LEFT JOIN grpemails g

1356 ON u.name=g.user

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

1358 ## err email4gByid `echo $sql`

1359 query "$sql"

1360 }

1361 collectmembersbyid() {

1362 # Collect user names of group specified by grid

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

1364 query "SELECT user FROM grp_mem \

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

1366 }

1367 collectmembersbyid() {

1368 # Collect user names of group name

1369 qgrp=`sqlquote "$1"`

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

1371 }

1372 collectgecosesbyid() {

1373 # Collect user gecoses of group

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

1375 query<<-EOF

1376 SELECT gecos

1377 FROM gecoses

1378 WHERE name IN (SELECT user FROM grp_mem

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

1380 EOF

1381 }

1382 collectemail() (

1383 # Collect email addresses for group $1

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

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

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

1387 for e; do

1388 if isuser "$e"; then

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

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

1391 else

1392 qgrp=`sqlquote "$e"`

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

1394 gmem="grp_mem"

1395 else

1396 tm=`sqlquote "$TEAM"`

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

1398 fi

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

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

1401 $gmem g left join grp_mem_s s

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

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

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

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

1406 query "$sql"

1407 fi

1408 done

1409 )

1410 sendinvitation() (

1411 # $1=email

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

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

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

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

1416 gecos=`gecos`

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

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

1419 _m4 -D_URL_="$urlbase" \

1420 -D_USER_="$name" \

1421 -D_EMAIL_="$1" \

1422 -D_REGIST_="$regist" \

1423 -D_ADMIN_="$admin" \

1424 $msgdir/mail-invite.m4 \

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

1426 return 0

1427 )

1428 emaildomaincheck() {

1429 case "$1" in

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

1431 *@*)

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

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

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

1435 return 2

1436 fi

1437 return 0

1438 ;;

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

1440 esac

1441 }

1442 invite() {

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

1444 case "$email" in

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

1446 *@*)

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

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

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

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

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

1452 elif sendinvitation $email; then

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

1454 else # Cannot be reached here

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

1456 fi ;;

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

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

1459 esac

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

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

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

1463 fi

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

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

1466 $layout/html.m4.html $layout/invite.m4.html

1467 }

1468 regist() {

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

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

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

1472 echo "bye bye" | html p

1473 reutrn

1474 fi

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

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

1477 cat<<EOF

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

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

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

1481 EOF

1482 return

1483 fi

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

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

1486 # Fake login password to wasureta

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

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

1489 wasureta $email

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

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

1492 addsession $1 # for removal after 1 minute

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

1494 $layout/login.m4.html

1495 return

1496 }

1497 group_safename() {

1498 # Convert $1 to safe group name

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

1500 }

1501 groupupdate() {

1502 gname=`getpar gname`

1503 qgname=`sqlquote "$gname"`

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

1505 # See ALSO same job in showgroup()

1506 newgname=`group_safename "$gname"`

1507 err newgname=$newgname

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

1509 err NewGNAME: gname=$newgname

1510 gname=$newgname

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

1512 replpar gname string "$gname"

1513 fi

1514 # Name confliction check

1515 parow=`getpar rowid`

1516 ## err parow=$parow

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

1518 query "BEGIN EXCLUSIVE;"

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

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

1521 if [ $count -gt 0 ]; then

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

1523 query "END;"

1524 return

1525 fi

1526 par2table $formdir/grp.def

1527 query "END TRANSACTION;"

1528 # Remove orphan

1529 : <<EOF

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

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

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

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

1534 EOF

1535 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1539 sql="delete from blog where id in

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

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

1542 query "$sql";

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

1544 return # and return

1545 fi

1546 fi

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

1548 fi

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

1550 grid=$(query $sql)

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

1552 grp $grid

1553 }

1554 groupclone() {

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

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

1557 case "$1" in

1558 */noteam)

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

1560 esac

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

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

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

1564 return

1565 fi

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

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

1568 return

1569 fi

1570 i=0

1571 while true; do

1572 copy="-copy$i"

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

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

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

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

1577 i=$((i++))

1578 continue

1579 fi

1580 break

1581 done

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

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

1584 query<<-EOF

1585 BEGIN;

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

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

1588 SELECT $newqname, key, val

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

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

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

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

1593 -- Copy members and their configuration --

1594 REPLACE INTO grp_mem SELECT $newqname, user

1595 FROM grp_mem WHERE gname=$qgrp;

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

1597 FROM grp_mem_s WHERE gname=$qgrp;

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

1599 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1600 -- Copy administrators --

1601 REPLACE INTO grp_adm SELECT $newqname, user

1602 FROM grp_adm WHERE gname=$qgrp;

1603 COMMIT;

1604 EOF

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

1606 STOPCLONEMSG=1 groupconf "$newrowid"

1607 }

1608 groupman() {

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

1610

1611 GF_STAGE="grpconf"

1612 GF_STAGE=groupupdate

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

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

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

1616 -D_DUMPTABLE_="syscmd(cat)" \

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

1618 }

1619 userconf() {

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

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

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

1623 }

1624 groupconf() {

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

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

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

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

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

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

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

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

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

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

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

1636 <div>

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

1638 <table>

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

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

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

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

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

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

1645 </table>

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

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

1648 </div>

1649 EOF

1650 fi

1651 }

1652 mems() {

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

1654 kwd=`getpar kwd`

1655 listmember $kwd

1656 }

1657 grps() {

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

1659 kwd=`getpar kwd`

1660 listgroup $kwd \

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

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

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

1664 $layout/form+dump.m4.html

1665 }

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

1667 gpg=`getpar grp`

1668 grid=${1:-$gpg}

1669 grp=`getgroupbyid "$grid"`

1670 ## . ./s4-blog.sh

1671 jg=`getpar joingrp`

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

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

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

1675 fi

1676 htmlheader=$layout/html.m4.html

1677 showgroup "$grid"

1678 }

1679 sql4interestblogs() {

1680 cat<<EOF

1681 CREATE TEMPORARY VIEW interestblogs AS

1682 SELECT blog.rowid rid, id, author

1683 FROM blog

1684 NATURAL JOIN

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

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

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

1688 WHEN (SELECT user FROM grp_mem

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

1690 THEN 0

1691 ELSE 1

1692 END;

1693 EOF

1694 }

1695 listnewblogsql() { # $1=user

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

1697 WHERE user='$user'

1698 AND tblrowid IN

1699 ($blogreadflagrowid,

1700 $blogcutoffflagrowid)),

1701 "0");"`

1702 cat<<EOF

1703 `sql4interestblogs`

1704 WITH article_ctime as (

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

1706 FROM article join article_s s using(id)

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

1708 GROUP BY id

1709 ), blog_title_owner as (

1710 SELECT blg.rid brid, id,

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

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

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

1714 ), blogall as (

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

1716 ), news as (

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

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

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

1720 FROM blogall bl

1721 LEFT JOIN

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

1723 ON bl.brid=al.tblrowid

1724 WHERE atime < bl.ctime

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

1726 LIMIT 10

1727 ) SELECT brid LINK, "新着",

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

1729 ctime, title,

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

1731 FROM news;

1732 EOF

1733 }

1734

1735 search_form() {

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

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

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

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

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

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

1742 @week → 最近一週間

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

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

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

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

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

1748 auth=""

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

1750 case "$1" in

1751 author=*)

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

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

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

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

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

1757 ;;

1758 grp=*)

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

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

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

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

1763 ;;

1764 esac

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

1766 cat<<-EOF

1767 <div class="right">

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

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

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

1771

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

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

1774

1775 </form>

1776 </div>

1777 EOF

1778 }

1779

1780 imgsrc_cache() (

1781 # $1 = directory for cache'ing

1782 # $2 = table (user_m or grp_m)

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

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

1785 dir="$1" tbl="$2"

1786 keycond="$3"

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

1788 ORDER BY rowid DESC LIMIT 1"

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

1790 tmpf=$tmpd/imgsrc_cache.$$

1791 case "$4" in

1792 [Ss]) size=S ;;

1793 [Oo]) size=O ;;

1794 *) size=M ;;

1795 esac

1796 # ImageCache filename storing schema:

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

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

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

1800 valtype=`query "$sql0"`

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

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

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

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

1805 case "$filetype" in

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

1807 esac

1808 fi

1809 cacheimg_S=$dir/S_$filename

1810 cacheimg_M=$dir/M_$filename

1811 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1819 else

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

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

1822 fifo2=$fifo.2

1823 mkfifo $fifo $fifo2

1824 fmt=${filename##*.}

1825 ## [[ NOTE ]]

1826 ## a. convert oldimage newimage

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

1828 ## b is much smaller than a

1829 cat $tmpf | unhexize \

1830 | tee $fifo \

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

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

1833 | tee $fifo2 \

1834 | convert - "$cacheimg_M" &

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

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

1837 | convert - "$cacheimg_S" &

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

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

1840 echo '">'

1841 echo "$sum" > $sumfile

1842 fi

1843 ## Now preparing cache image, done.

1844 ## Store this information to DB

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

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

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

1848 query <<-EOF

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

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

1851 EOF

1852 )

1853

1854 showhome() {

1855 # $1=userRowIdToShow

1856 err showhome \$1=$1

1857 case "$1" in

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

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

1860 esac

1861 ## err ShowHome: uname=$uname

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

1863 gecos=`gecos "$uname"`

1864 ## err SH:gecos=$gecos

1865 GF_VIEWONLY=1

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

1867 search_form_args=""

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

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

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

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

1872 # Display folders

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

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

1875 and type like 'file:%';"

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

1877 nfile=`query "$sql"`

1878 # err nfile=$nfile

1879 if [ $nfile -gt 0 ]; then

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

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

1882 fi

1883 else

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

1885 GROUP BY user;"`

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

1887 search_form_args="author=$uname"

1888 fi

1889 . ./s4-blog.sh

1890

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

1892 search_form "$search_form_args" > $sf

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

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

1895 viewtable $formdir/user.def user $1

1896 echo "</div>"

1897 } > $pf

1898

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

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

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

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

1903 if true; then

1904 tbl=user_m

1905 enticond="name='$uname'"

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

1907 else

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

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

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

1911 echo '">'

1912 }

1913 fi > $imf

1914 fi

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

1916 val='$uname';"`

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

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

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

1920 listblog $uname > $bf

1921 ## unset ddd

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

1923

1924 hometail=$tmpd/tail.$$

1925 mkfifo $hometail

1926

1927 #Calling listgroupbytable, originally here

1928

1929 (

1930 # Display Most Recent Entry

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

1932 shortval=${shortval:-val}

1933

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

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

1936 ctime,

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

1938 (SELECT gecos FROM gecoses

1939 WHERE name=(SELECT val FROM blog_s

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

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

1942 FROM blog b

1943 JOIN

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

1945 FROM article a, article_s s

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

1947 GROUP BY blogid ORDER BY val DESC LIMIT 50

1948 ) m

1949 ON b.id=m.blogid;"

1950 # This should be as follows

1951 : <<EOF

1952 WITH arts AS(

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

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

1955 FROM article a NATURAL JOIN article_s s

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

1957 GROUP by s.id

1958 )

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

1960 FROM arts a0

1961 JOIN

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

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

1964 ORDER BY ctime DESC LIMIT 50;

1965 EOF

1966

1967 cat<<-EOF

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

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

1970 <div class="lcto">

1971 `DT_VIEW=replyblog dumptable html blog`

1972 </div>

1973 EOF

1974 unset DT_SQL

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

1976 # Display NEWS

1977 # 2016-06-26

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

1979 acclog blog $blogreadflagrowid

1980 # echo "全部既読にしました" | html p

1981 fi

1982 # 2016-02-19 Counting NEWS without using dumptable.

1983 sql=`listnewblogsql "$user"`

1984 # echo "$sql" > tmp/listnew

1985 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

1986 cont=`echo "$new10"|grep "^<TR>"|wc -l`

1987 cont=$((cont-1))

1988 err newcount=$cont

1989 if [ $cont -gt 0 ]; then

1990 #echo "全体の新着記事${cont}傑" | html h2

1991 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

1992 echo "<label for=\"new10\" title=\"F\">新着${cont}傑</label><div>"

1993 cat<<-EOF | html form 'action="?home"'

1994 `cgi_checkbox readchk yes 'id="read"'`<label

1995 for="read">新着ふくめて全部読んだことにする</label>

1996 `cgi_submit '確定'`

1997 `cgi_hidden read yes`

1998 EOF

1999 echo "$new10 "

2000 echo "</div>"

2001 else # If news is 0, set log cut off flag

2002 acclog blog $blogcutoffflagrowid # for speed

2003 fi

2004 else # Not My Home ($user != $uname)

2005 : # DT_SQL=

2006 fi

2007 ) > $hometail & # Is background call safe to m4??

2008 #

2009 listgroupbytable $formdir/grp.def $cond |

2010 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2011 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2012 -D_PROFIMG_="spaste(\`$imf')" \

2013 -D_BLOGS_="spaste(\`$bf')" \

2014 -D_SEARCH_="spaste(\`$sf')" \

2015 -D_NBLOG_="$nblog" \

2016 -D_GROUPS_="syscmd(\`cat')" \

2017 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2018 $layout/html.m4.html $layout/home.m4.html

2019

2020 # Record access log

2021 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2022 }

2023 commission() { # $1=grp-rowid $2=user-rowid

2024 contenttype; echo

2025 ## err commission: "$@"

2026 gname=`getgroupbyid $1`

2027 echo "グループ $gname 管理者委任" \

2028 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2029 if [ -n "$2" ]; then

2030 grp_reg_adm "$@"

2031 else

2032 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2033 fi

2034 }

2035 listgroupbytable() {

2036 # $1=deffile $2...=condition

2037 tagline=`grep :tag: $1`; shift

2038 and="${1:+and }" where=${1:+where }

2039 href="<a href=\"$myname?grp+"

2040 echo '<div class="listgroup">'

2041 NGsql="select distinct tag from\

2042 (select gname, max(case key when 'tag' then val end) as tag, \

2043 max(case key when 'ctime' then val end) as ctime\

2044 from grp_s group by gname order by ctime);"

2045 sql="select val from grp_s where key='tag' $and$* group by val;"

2046 ## err ListGRP: query sql="$sql"

2047 for tag in `query "$sql"`

2048 do

2049 ## err ListGrp: tag=$tag

2050 tn=${tagline%%=${tag}*}

2051 tn=${tn##*[ :]}

2052 sql="select rowid||':'||gname as 'グループ名',説明 from

2053 (select (select rowid from grp g where g.gname=grp_s.gname)

2054 as rowid,

2055 gname,

2056 max(case key when 'gecos' then val end) as '説明',

2057 max(case key when 'tag' then val end) as 'tag',

2058 max(case key when 'mtime' then val end) as mtime from grp_s

2059 $where$* group by gname having tag='$tag' order by mtime desc);"

2060 ## err PersonalGroupList= `echo $sql`

2061 echo "<h2>$tn</h2>"

2062 echo '<table class="b listgroup">'

2063 sq -header -html $db "$sql" \

2064 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2065 echo '</table>'

2066 done

2067 echo '</div>'

2068 }

2069 iconhref() (

2070 # $1=icon-file, $2=Href $3=title $4...=anchor

2071 data=`percenthex "$1"`

2072 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2073 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2074 href=$2; title=$3; shift 3

2075 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2076 )

2077 iconhref2() (

2078 # $1=icon-file, $2=Href $3=title $4...=anchor

2079 src=$1

2080 href=$2; title=$3; shift 3

2081 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2082 )

2083 listentry() (

2084 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2085 # Referring variable $iamowner=$grp to attach owner-request links

2086 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2087 cond='' hiddens=''

2088 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2089 if [ -z "$offset" ]; then

2090 offset=`getpar start`; offset=${offset%%[!0-9]*}

2091 offset=$((offset-1))

2092 fi

2093 offset=$((offset + 0)) # change to numeric forcibly

2094 [ $offset -lt 0 ] && offset=0

2095 limit=30

2096 dir=`getcachedir "$1"`

2097 if [ x"$1" = x"user" ]; then

2098 hrb="$myname?home"

2099 deficon=person-default.png

2100 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2101 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2102 gcs=gecos

2103 else # if group

2104 hrb="$myname?grp"

2105 deficon=group-default.png

2106 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2107 gcs=name

2108 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2109 if [ -n "$tagline" ]; then

2110 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2111 ## err tagconv=$tagconv

2112 fi

2113 fi

2114 if [ ! -d $dir ]; then

2115 mkdir -p $dir

2116 fi

2117 if [ ! -s $dir/$deficon ]; then

2118 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2119 fi

2120 if [ -n "$2" ]; then

2121 cond="where nick like '%$2%' or b.name like '%$2%'"

2122 fi

2123

2124 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2125 # grpは呼出し元の動的スコープ変数でよくないな...

2126 ##qgrp=`sqlquote $grp`

2127 getgrp="(select gname from grp where rowid=${rowid:--1})"

2128 sql="select a.rowid, a.$link,

2129 coalesce(b.$gcs, a.$nm) as nick,

2130 quote(a.$nm) as qname,

2131 (SELECT val FROM ${tbl}_s

2132 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2133 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2134 || case when a.$nm in (select gname from grp)

2135 then printf('(%d名)',

2136 (select count(user) from grp_mem where gname=a.$nm))

2137 else ' <'||a.$nm||'>'

2138 end

2139 as name,

2140 b.tag,

2141 case when a.$nm in (select user from grp_adm

2142 where gname=$getgrp) then '(管理者)'

2143 when '$user' in (select user from grp_adm where gname=a.$nm)

2144 then '(ADMIN)'

2145 when '$user' in (select user from grp_mem where gname=a.$nm)

2146 then '(Member)'

2147 when '$iamowner' = '' then ''

2148 else ',not='||a.rowid end as ownerlink,

2149 CASE '$entity'

2150 WHEN 'グループ'

2151 THEN coalesce(

2152 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2153 'open')

2154 ||

2155 CASE WHEN '$user'

2156 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2157 THEN ' ismember'

2158 ELSE ''

2159 END

2160 ELSE 'user'

2161 END regmode

2162 from $tbl a left join

2163 (select $nm as name,

2164 max(case key when 'gecos' then val end) as gecos,

2165 max(case key when 'tag' then val end) as tag,

2166 max(case key when 'mtime' then val end) as mtime,

2167 max(case key when 'wtime' then val end) as wtime,

2168 max(case key when 'login' then val end) as login

2169 from ${tbl}_s group by $nm)

2170 b on a.$nm=b.name $cond $3

2171 order by b.wtime desc, b.login desc,

2172 b.mtime desc, b.tag desc, a.rowid asc"

2173 # Give precedence to newer maintained groups (2016-09-24)

2174 # Note that mtime is stored only in grp_s.

2175 ## err LE:sql.1="$sql"

2176 total=`query "with x as ($sql) select count(*) from x;"`

2177 echo "${entity} 一覧" | html h2

2178 if [ $total -gt $limit ]; then

2179 echo '<div class="right">'

2180 cgi_form $stage <<EOF

2181 <label>次の語を含む${entity}で検索:

2182 `cgi_text kwd $kwd`</label>

2183 EOF

2184 echo '</div>'

2185 fi

2186 hiddens="$hiddens

2187 `cgi_hidden kwd \"$kwd\"`

2188 `cgi_hidden stage \"$stage\"`"

2189 cat<<EOF

2190 <form action="$myname" method="POST">

2191 <p>${total}件中の<input class="hidesub" type="text" name="start"

2192 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2193 <input type="submit" value="確定"></p>

2194 </form>

2195 EOF

2196 if [ $((offset+limit)) -lt $total ]; then

2197 nextbtn=$(

2198 cat<<EOF

2199 <div class="right clear"><form action="$myname" method="POST">

2200 `cgi_submit 次の${limit}件`

2201 $hiddens

2202 `cgi_hidden offset $((offset + limit))`</form></div>

2203 EOF

2204 )

2205 fi

2206 if [ $offset -gt 0 ]; then

2207 prevbtn=$(

2208 cat<<EOF

2209 <form action="$myname" method="POST">

2210 `cgi_submit 前の${limit}件`

2211 $hiddens

2212 `cgi_hidden offset $((offset - limit))`</form>

2213 EOF

2214 )

2215 fi

2216 pnbtn="$nextbtn$prevbtn"

2217 echo $pnbtn

2218

2219 ## err ListEntry: `echo "$sql"\;`

2220 # sq $db here??? 2016-11-28

2221 query "$sql limit $limit ${offset:+offset $offset};" \

2222 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2223 err name=$name owner=$ownerp lnk=$lnk

2224 err newlnk=$lnk regmode=$regmode

2225 icondir=$dir/$id

2226 # Pick up only last icon

2227 echo "<div class=\"iconlist xy$thumbxy $type\">

2228 <p class=\"tag _$tag\">$tag</p>" \

2229 | _m4 $tagconv

2230 if [ -n "$NOSPEEDUP" ]; then

2231 files=`getvalbyid $tbl profimg $id $icondir`

2232 if [ -n "$files" ]; then

2233 icon=`echo "$files"|tail -1`

2234 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2235 else

2236 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2237 fi

2238 elif [ -n "$icon" -a -s "$icon" ]; then

2239 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2240 else

2241 cond="$nm=$qname"

2242 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2243 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2244 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2245 # err "img=[$img]"

2246 if [ -n "$img" ]; then

2247 echo "<a href=\"$hrb+$lnk\">"

2248 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2249 echo "</a>"

2250 else

2251 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2252 fi

2253 fi

2254 echo "<br>$name${ownerp:+<br>$ownerp}"

2255 echo "</div>"

2256 done

2257 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2258 )

2259 listmember() {

2260 listentry user "$@"

2261 }

2262 listgroup() {

2263 listentry group "$@"

2264 }

2265 hexteams() { # $1=gname, $2(optional)=user

2266 cond=${2:+" AND user='$2'"}

2267 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2268 WHERE gname='$1' AND key='team'$cond;"

2269 }

2270 showgroup() { # $1=group-rowid

2271 if [ -z "$1" ]; then

2272 grid=`getpar grid`

2273 grid=${grid%%[!0-9]*}

2274 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2275 else

2276 grid=$1

2277 fi

2278 grp=`getgroupbyid $grid`

2279 qgrp=`sqlquote "$grp"`

2280 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2281 if isgroup "$grp"; then

2282 tf=$tmpd/title.$$

2283 sf=$tmpd/search.$$

2284 bodyclass=`query "SELECT val FROM grp_s

2285 WHERE gname=$qgrp AND key='regmode';"`

2286 if ismember "$user" "$grp"; then

2287 ismember="ismember"

2288 qgrp=`sqlquote "$grp"`

2289 bodyclass="$bodyclass${bodyclass:+ }ismember"

2290 else

2291 ismember="" # bodyclass="group"

2292 fi

2293 bodyclass="$bodyclass grouphome"

2294 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2295 echo "グループ $grp" > $tf

2296

2297 showgroupsub $formdir/grp.def "$grid" | \

2298 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2299 -D_FORM_="syscmd(\`cat')" \

2300 -D_BODYCLASS_="$bodyclass" \

2301 -D_DUMPTABLE_="" \

2302 $htmlheader $sf $layout/form+dump.m4.html

2303 # $htmlheader $layout/form+dump.m4.html

2304 # $htmlheader is defined in grp()

2305 else # if $grp is removed at par2table

2306 listgroup

2307 fi

2308 }

2309 showgroupsub() {

2310 # $1=def-file $2=group-rowid

2311 # Using $ismember

2312 rowid=$2

2313 grp=`getgroupbyid $2`

2314 qgrp=`sqlquote "$grp"`

2315 td=`getcachedir grp/"$2"`

2316 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2317 if [ -z "$rowid" ]; then

2318 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2319 #grp=`sq $db "select gname from grp where rowid=$grp"`

2320 echo "showgroupsub: invalid argument($1 $2)" | html p

2321 return

2322 fi

2323 val=`getvalbyid grp profimg $rowid $tmpd`

2324 enticond="gname=$qgrp"

2325 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2326 if [ -n "$img" ]; then

2327 cat<<-EOF

2328 <p class="groupimg">

2329 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2330 EOF

2331 fi

2332 echo "<div class=\"noprofimg\">"

2333 viewtable $1 grp $rowid

2334 echo "</div>"

2335 if isgrpowner "$user" "$grp"; then

2336 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2337 iamowner=$rowid

2338 colmd=" mode"

2339 fi

2340 if [ -n "$ismember" ]; then

2341 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2342 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2343 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2344 cat<<EOF

2345 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2346 <div class="fold clear">

2347 `cgi_checkbox send yes id="send"`<label

2348 for="send">グループ全員にメッセージ送信</label>

2349 <div>

2350 `cgi_textarea message "" "cols=60"`

2351 `cgi_submit 送信`

2352 `cgi_reset リセット`

2353 </div>

2354 `cgi_hidden grp $rowid`

2355 </div></form>

2356 EOF

2357 fi

2358 # 加入ボタン + 加入者リスト

2359 if [ -n "$ismember" ]; then

2360 ismem='checked' state="(参加中)"

2361 else

2362 nomem='checked' state="(現在非加入)"

2363 fi

2364 # このグループでの加入アドレス

2365 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2366 and key='email';"`

2367 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2368 ## and key='email';"

2369 ##err email=$eml

2370 cat <<EOF

2371 <div class="fold clear">

2372 `cgi_checkbox reg yes id="reg"`<label

2373 for="reg">自身の加入状態を操作する</label>$state

2374 <div>

2375 EOF

2376 cgi_form grp <<EOF

2377 <p>このグループに</p>

2378 <table class="b">

2379 <tr><th>メンバーとして</th><td>

2380 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2381 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2382 <tr><th>参加する場合のメイルアドレス<br>

2383 <small>(メインのアドレスとは違うものにする場合に記入<br>

2384 同じでよい場合は空欄に)</small></th>

2385 <td>`cgi_text email $eml`</td></tr>

2386 </table>

2387 `cgi_hidden grp $rowid`

2388 EOF

2389 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2390 echo "moderated (承認加入の)グループなので実際に参加できるのは

2391 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2392 fi

2393 echo '</div></div>'

2394 echo '<h2>話題一覧</h2>'

2395 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2396 cat<<-EOF

2397 <form class="summary" action="$myname" title="$thelp">

2398 `cgi_hidden owner "$grp"`

2399 `cgi_hidden kwd "@week"`

2400 `cgi_hidden stage searchart`

2401 `cgi_submit "一週間のまとめ"`

2402 </form>

2403 EOF

2404 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2405 colstate="state:稼動状態:frozen=rowclass=凍結"

2406 DT_CHLD=article:blogid \

2407 DT_QOWNER="$qgrp" \

2408 DT_VIEW=replyblog dumptable html blog \

2409 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2410

2411 getgname="(select gname from grp where rowid=$rowid)"

2412 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2413 cm="?commission+$rowid"

2414 thumbxy=50x50 listmember "" "$c" "$rowid" \

2415 |sed -e "s|$<br>$,not=$.*$|\1|" # 間違って押しやすい

2416 # team list

2417 hexteams=`hexteams "$grp"`

2418 if [ -n "$hexteams" ]; then

2419 echo "チーム一覧" | html h2

2420 echo '<div class="dumptable"><table class="b">'

2421 sq $db -html -header<<-EOF

2422 SELECT val TEAM,

2423 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2424 MEMBERS

2425 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2426 EOF

2427 echo '</table></div>'

2428 fi

2429 }

2430 grp_getbodyclass() {

2431 # Get css class name for document.

2432 # `moderated' for moderated groups

2433 # `ismember' for groups where user belongs

2434 # $1=GroupName (w/o quote)

2435 # $user=userNameCurrentlyLogin

2436 ## err grp_getbodyclass: 1="$1"

2437 qgrp=`sqlquote "$1"`

2438 query<<-EOF

2439 SELECT coalesce(

2440 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2441 'open')

2442 ||

2443 CASE WHEN '$user'

2444 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2445 THEN ' ismember'

2446 ELSE ''

2447 END;

2448 EOF

2449 }

2450 grpaction() { # $1=group-rowid

2451 err GRP_ACTION:IN

2452 grid=${1:-`getpar grp`}

2453 grp=`getgroupbyid "$grid"`

2454 if [ -z "$grp" ]; then

2455 echo "無効な指定です。" | html p; return

2456 fi

2457 if ! ismember $user "$grp"; then

2458 echo "加入者のみに許可された操作です。" | html p; return

2459 fi

2460 echo "グループ $grp 個別選択操作" \

2461 | _m4 -D_TITLE_="syscmd(\`cat')" \

2462 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2463 $layout/html.m4.html

2464

2465 isowner=""

2466 isgrpowner "$user" "$grp" && isowner="yes"

2467 usel=`getpar usel`

2468 if [ -n "$usel" ]; then

2469 uids=$(echo `echo $usel`|tr ' ' ',')

2470 ## err grpaction-1: grp=$grp, `echo $sql`

2471 text=`getpar text`

2472

2473 rm=`getpar rm` cfm=`getpar confirm`

2474 ## err rm=$rm cfm=$cfm

2475 if [ x"$rm" = x"yes" ]; then

2476 if [ "$isowner" ]; then

2477 if [ x"$rm$cfm" = x"yesyes" ]; then

2478 # Eliminate

2479 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2480 for tbl in grp_mem grp_mem_s grp_mem_m; do

2481 sql="delete from $tbl $cond;"

2482 # echo "sql=$sql"

2483 query "$sql"

2484 err rmGRPuser "$sql"

2485 done

2486 num=`query "select count(*) from user where rowid in ($uids);"`

2487 #err num=$num

2488 if [ 0$num -gt 0 ]; then

2489 sql="select coalesce(b.val,a.name) from user a left join \

2490 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2491 # err `echo "$sql"`

2492 html pre<<EOF

2493 以下の${num}名のグループ $grp 登録を解除しました。

2494 `query "$sql"`

2495 EOF

2496 fi

2497 else

2498 echo "確認のチェックがないのでやめておきます。" | html p

2499 return

2500 fi

2501 else # not Group Owner

2502 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2503 return

2504 fi

2505 cat<<EOF

2506

2507 EOF

2508 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2509 if [ -z "$text" ]; then # if msg is empty

2510 echo "なにかメッセージを..." | html p

2511 return 0

2512 fi

2513 gecos=`gecos $user`

2514 mkfrom=`getpar mkfrom`

2515 if [ x"$mkfrom" = x"yes" ]; then

2516 safegc=`echo "$gecos" | tr -d '<>@'`

2517 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2518 fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2519 mail_from="$safegc <$fromad>"

2520 else

2521 mail_from="$admin"

2522 fi

2523 MAIL_FROM=$mail_from \

2524 smail "`email4groupbyuid "$grp" $usel` $user" \

2525 "$gecos さんからのメッセージ" <<EOF

2526 $url

2527 のグループ「$grp」のメンバーである $gecos さんから、

2528 あなた宛へのメッセージです。

2529 ----------------------------------------------------------

2530 $text

2531 EOF

2532 if [ $? = 0 ]; then

2533 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2534 sql="select coalesce(b.val, a.name) from

2535 (select name from user where rowid in ($uids)) a

2536 left join user_s b on a.name=b.name and b.key='gecos';"

2537 html pre<<EOF

2538 `query "$sql"`

2539 (送信者である $gecos さんも含まれます)

2540 EOF

2541 err SendDone: `echo $sql`

2542 fi

2543 elif [ x"$rm" = x"commission" ]; then

2544 grp_reg_adm $grid $usel

2545 elif [ x"$rm" = x"addteam" ]; then

2546 team=`getpar team|sed "s/'/''/g"` # for single quotation

2547 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2548 if [ x"$team" != x"$newteam" ]; then

2549 echo "チーム名に使えない文字を除去しました" | html p

2550 team=newteam

2551 fi

2552 if [ -z "$team" -o x"$team" = x"なし" ]; then

2553 cat<<-EOF | html p

2554 有効なチーム名を入力してください。

2555 カンマだけ、「なし」という名前は使えません。

2556 EOF

2557 echo "有効なチーム名を入力してください。" | html p

2558 else

2559 grp_add_team $grid "$team" $usel

2560 fi

2561 elif [ x"$rm" = x"rmteam" ]; then

2562 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2563 rmteam=`getpar rmteam|sed "s/'/''/g"`

2564 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2565 gname='$grp' AND user='$user' AND key='team'\

2566 AND val='$rmteam';\"`" ]; then

2567 grp_rm_team $grid "$rmteam" $usel

2568 else

2569 echo "所属していないチームの除去操作はできません。"|html p

2570 fi

2571 else

2572 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2573 fi

2574 fi

2575 fi

2576 # POST count summary

2577 from=`getpar from`; to=`getpar to`

2578 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2579 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2580 fromtonote="<p>POST集計: $from_input - $to_input</p>"

2581 # New entry

2582 sql="WITH mems AS (

2583 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2584 ON gm.user=g.name

2585 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2586 ), target_article AS (

2587 SELECT id FROM article_s

2588 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2589 ), posts AS (

2590 SELECT author, count(author) post

2591 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2592 WHERE blogid IN (SELECT id FROM blog_s

2593 WHERE key='owner'

2594 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2595 AND key='text'

2596 GROUP BY author

2597 ), teams AS (

2598 SELECT user, group_concat(val, ', ') team

2599 FROM grp_mem_m

2600 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2601 AND key='team'

2602 GROUP BY user

2603 ), user_post AS (

2604 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2605 FROM mems m LEFT JOIN posts

2606 ON m.name=posts.author

2607 GROUP by m.rowid

2608 )

2609 SELECT

2610 CASE

2611 WHEN (SELECT user FROM grp_adm

2612 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2613 AND user=up.name) IS NOT NULL

2614 then 'k'

2615 ELSE ''

2616 END || rowid || ',' || gecos NAME,

2617 post POST, team TEAM

2618 FROM user_post up LEFT JOIN teams t

2619 ON up.name=t.user

2620 ORDER BY gecos;"

2621 ## err grpaction: "`echo \"$sql\"`"

2622 tf=$tmpd/title.$$

2623 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2624 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2625 for=\"cmadmin\">グループ管理者委任</label>

2626 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2627 </p></div>"

2628 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2629 for=\"conf\">グループ登録解除</label>

2630 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2631 <p>この操作による通知は本人に行きません。

2632 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2633 </div>"

2634 # Get team list to which current user belongs into $hexteams

2635 myhexteams=$(hexteams "$grp" "$user")

2636 allhexteams=$(hexteams "$grp")

2637 if [ -n "$myhexteams" ]; then

2638 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2639 for=\"cmrmteam\">チーム属性除去</label>

2640 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2641 を除去します: `cgi_checkbox teamconfirm yes` 確認

2642 <p>この操作による通知は本人に行きません。

2643 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2644 </div>

2645 "

2646 fi

2647 b1='<label> <input type="checkbox" name="usel" value="'

2648 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2649 #b2='"> <span>' b3='</span></label>'

2650 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2651 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2652 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2653 cgi_form grpaction<<EOF \

2654 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2655 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2656 | _m4 -D_TITLE_="spaste(\`$tf')" \

2657 -D_SUBTITLE_="チェック後操作ボタン" \

2658 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2659 $layout/form+dump.m4.html

2660 <p>下でチェックした人を対象として:</p>

2661 <div class="foldtabs">

2662 `cgi_radio rm addteam 'id="cmteam"'`<label

2663 for="cmteam">同じチーム属性を付与</label>

2664 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2665 `cgi_datalist_h teams $allhexteams`

2666 </div>

2667 ${rmteammsg}

2668 `cgi_radio rm send id="sendmsg"`<label

2669 for="sendmsg">メッセージ送信</label>

2670 <div>

2671 `cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"

2672 >差出人を自分に(チェックを外すと相手が返事できない)</label><br>

2673 `cgi_textarea text "" cols=40`

2674 </div>

2675 ${isowner:+$cmmsg$excmsg}

2676 `cgi_radio rm close id="x"`<label for="x">×</label>

2677 </div>

2678 <h4>$grp 参加者一覧</h4>$fromtonote

2679 <table class="td2r">

2680 `sq $db -header -html "$sql"`

2681 </table>

2682 `cgi_hidden grp $grid`

2683 EOF

2684 }

2685 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2686 # Create TEMPORARY VIEW

2687 query<<EOF

2688 CREATE TEMPORARY VIEW writeusers AS

2689 SELECT DISTINCT author FROM article

2690 WHERE id in (

2691 select id from article where blogid=(select id from blog where rowid=$1)

2692 );

2693 CREATE TEMPORARY VIEW movablegroups AS

2694 SELECT g.rowid growid , g.gname

2695 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2696 ON grp.gname=gm.gname -- そのユーザが属している

2697 AND user='$user') g -- グループに絞る

2698 WHERE (SELECT author FROM writeusers

2699 EXCEPT

2700 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2701 IS NULL;

2702 $2

2703 EOF

2704 }

2705 sql4readableblogs() {

2706 # Create view of blogs that can be readable to $user

2707 # Blog is readable when:

2708 # 1: blog owner is an user

2709 # 2: else, 2.1: owner-group where the $user belongs

2710 # 2.2: else, owner-group is not moderated

2711 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2712 cat<<EOF ## | tee tmp/sql.out

2713 CREATE TEMPORARY VIEW readableblogs AS

2714 SELECT blog.rowid rid, id, author

2715 FROM blog

2716 NATURAL JOIN

2717 (SELECT id,

2718 max(CASE key WHEN 'owner' THEN val END) owner,

2719 max(CASE key WHEN 'mode' THEN val END) mode

2720 FROM blog_s GROUP by id) bs

2721 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2722 THEN 1 -- blog owner is an user, READABLE

2723 WHEN (SELECT val FROM grp_s

2724 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2725 AND

2726 (SELECT user FROM grp_mem

2727 WHERE gname=bs.owner AND user='$user') IS NULL

2728 THEN 0

2729 WHEN mode IN ('quiz', 'enquete')

2730 THEN 0 -- "quiz" mode blog cannot be searched

2731 ELSE 1

2732 END;

2733 EOF

2734 }

2735 editheading() { # $1=rowid-of-heading

2736 rowid=${1%%[!A-Z0-9a-z_]*}

2737 if [ -z "$rowid" ]; then

2738 echo "話題番号が未指定です。" | html p

2739 return

2740 fi

2741 owner=`getvalbyid blog owner $rowid`

2742 title=`getvalbyid blog title $rowid`

2743 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2744 | _m4 -D_TITLE_="修正" \

2745 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2746 -D_BLOGS_="" -D_DUMPTABLE_="" \

2747 -D_FORM_="syscmd(\`cat')" \

2748 $layout/html.m4.html $layout/form+dump.m4.html

2749 # Move to group

2750 if isuser "$owner"; then

2751 crview4article $rowid

2752 n=`query "SELECT count(*) FROM writeusers;"`

2753 ## err N=$n

2754 if [ $((n)) -gt 0 ]; then

2755 ## err ROWID=$rowid

2756 sql="SELECT growid || ':' || gname FROM movablegroups;"

2757 cat<<-EOF

2758 <div class="fold">

2759 `cgi_checkbox mv send id="mv"`<label

2760 for="mv">この話題をグループ所有に移動する</label>

2761 <div>

2762 <form action="?mvart" method="POST" enctype="multipart/form-data">

2763 移動先グループ:

2764 <select name="mv2grp">

2765 EOF

2766 query ".mode html"

2767 query<<-EOF |

2768 $sql

2769 .mode list

2770 EOF

2771 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2772 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2773 cat<<-EOF

2774 </select>

2775 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2776 そのグループに加入しているものに限られます)</p>

2777 <p>`cgi_checkbox cfm yes`<label>確認

2778 (この操作は元に戻すことができません)</label></p>

2779 `cgi_hidden blogrowid $rowid`

2780 `cgi_submit 移動`

2781 `cgi_reset Reset`

2782 </form>

2783 </div>

2784 </div>

2785 EOF

2786 fi

2787 # end of isuser "$owner"

2788 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

2789 [ -n "$hexteams" ];}; then

2790 none="`echo なし|hexize`"

2791 cat<<-EOF

2792 <div class="fold">

2793 `cgi_checkbox mv2team send id="mv2team"`<label

2794 for="mv2team">この話題を以下のチームのものにする</label>

2795 <div><p>現在の所属チーム設定:

2796 `query "SELECT

2797 coalesce((SELECT val FROM blog_s

2798 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

2799 AND key='team'),

2800 ':なし');"`</p>

2801 <form action="?mvart" method="POST" enctype="multipart/form-data">

2802 移動先チーム: `cgi_select_h mv2team $none $hexteams`

2803 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

2804 `cgi_hidden blogrowid $rowid`<br>

2805 `cgi_submit 移動`

2806 `cgi_reset Reset`

2807 </form></div></div>

2808 EOF

2809 fi

2810 }

2811 mvart() { # move diary to some group or team

2812 # or move blog of group to team which belong to the group

2813 blogrowid=`getpar blogrowid`

2814 cfm=`getpar cfm`

2815 ##### echo move blog:$blogrowid to $mv2grp | html p

2816 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

2817 . ./s4-blog.sh

2818 if [ -z "$blogrowid" ]; then

2819 echo "無効な指定です(mvart)。" | html p

2820 return

2821 elif [ x"$cfm" != x"yes" ]; then

2822 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

2823 elif { mv2grp=`getpar mv2grp`

2824 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

2825 [ -n "$mv2grp" ]; }; then

2826 crview4article $blogrowid

2827 ########## TRANSACTION BEGIN

2828 query "BEGIN;"

2829 n=`query "SELECT count(*) FROM writeusers;"`

2830 ## err Nwriteuser=$n

2831 if [ $((n)) -gt 0 ]; then

2832 query<<-EOF

2833 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

2834 WHERE key='owner'

2835 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2836 AND $mv2grp IN (SELECT growid FROM movablegroups);

2837 EOF

2838 fi

2839 query "END;"

2840 ########## TRANSACTION END

2841 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

2842 [ -n "$mv2team" ];}; then

2843 # blog owner can move it to ANY team

2844 case "$mv2team" in

2845 'なし')

2846 cat<<-EOF

2847 DELETE FROM blog_s

2848 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

2849 AND key='team';

2850 EOF

2851 ;;

2852 "") ;;

2853 *)cat<<-EOF

2854 BEGIN;

2855 REPLACE INTO blog_s(id, key, val)

2856 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2857 'team', '$mv2team');

2858 REPLACE INTO blog_s(id, key, val)

2859 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

2860 'notify', 'all'); -- Change notify to all

2861 END;

2862 EOF

2863 esac | query

2864 fi

2865 blog_reply $blogrowid

2866 echo yes | html p

2867 }

2868 editart() { # $1=article-rowid $2=blogrowid

2869 rowid=${1%%[!A-Z0-9a-z_]*}

2870 blogrowid=${2%%[!A-Z0-9a-z_]*}

2871 if [ -z "$rowid" -o -z "$blogrowid" ]; then

2872 echo "表示する記事番号が未指定です。" | html p

2873 return

2874 fi

2875 owner=`getvalbyid blog owner $blogrowid`

2876 title=`getvalbyid blog title $blogrowid`

2877 author=`getvalbyid article author $rowid`

2878 ## err EDITart: owner=$owner, author=$author

2879 if isgrpowner "$user" "$owner"; then

2880 : EDIT OK

2881 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

2882 echo "本人か所有者しか編集できません." | html p

2883 return

2884 fi

2885 aid=`query "select id from article where rowid=$rowid;"`

2886 tmpout=$tmpd/editart.$$.out

2887 GF_ACTION="?replyblog+$blogrowid#$aid" \

2888 edittable $formdir/article.def article $rowid \

2889 > $tmpout

2890 rm -f /tmp/editart.out

2891 # Cannot use pipelining to m4 with genform() because of stdin stack

2892 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

2893 -D_FORM_="syscmd(cat $tmpout)" \

2894 -D_SUBTITLE_="`gecos $owner`の「$title」" \

2895 -D_BLOGS_= -D_DUMPTABLE_= \

2896 $layout/html.m4.html $layout/form+dump.m4.html

2897 }

2898 send2mem() {

2899 rowid=`getpar grp`

2900 rowid=${rowid%%[!0-9]*} # Cleaning

2901 if [ -z "$rowid" ]; then

2902 echo "グループが未指定です。" | html p

2903 return

2904 fi

2905 message=`getpar message`

2906 if [ -z "$message" ]; then

2907 echo "文章を入れてください。" | html p

2908 return

2909 fi

2910 grp=`getgroupbyid $rowid`

2911 members=`collectemail $grp`

2912 # smail rcpt subj (file)

2913 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$admin>" \

2914 smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF

2915 $urlbase?grp+$rowid

2916 グループ $grp に所属する

2917 `gecos $user` さんよりメッセージ:

2918

2919 $message

2920 EOF

2921 cat<<EOF

2922 <p>以下のユーザに送信しました。</p>

2923 <pre>

2924 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

2925 </pre>

2926 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

2927 EOF

2928 }

2929 joingrpadmit() {

2930 # $1=yes/no $2=session-key

2931 if [ -z "$2" ]; then

2932 echo "bye bye" | html p; return

2933 fi

2934 t_usr=`session=$2 getpar user`

2935 t_grp=`session=$2 getpar group`

2936 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

2937 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

2938 if [ -z "$t_usr" -o -z "$t_grp" ]; then

2939 echo "無効な加入依頼です。" | html p

2940 echo "有効期限が切れたか、

2941 他の管理者がいる場合は処理済みの可能性があります。" | html p

2942 return

2943 fi

2944 if ! isgrpowner "$user" "$t_grp"; then

2945 echo "グループ管理者のみの機能です。" | html p; return

2946 fi

2947 case $1 in

2948 yes) joingrp "$t_grp" "$t_usr" yes ;;

2949 no) joingrp "$t_grp" "$t_usr" no ;;

2950 *)

2951 echo "無効な指定です($1)。" | html p

2952 return ;;

2953 esac

2954 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

2955 rcpts="`getgroupadminmails $t_grp` $user"

2956 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

2957 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

2958 に

2959 $t_usr

2960 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

2961 しました。"

2962 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

2963 query "delete from session where id='$2';"

2964 echo "$body" | html p

2965 }

2966

2967 joingrprequest() {

2968 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2969 jss="joingrp-`date +%s`-`genrandom 12`"

2970 addsession $jss +${memoplimitdays}days

2971 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

2972 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

2973 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

2974 $url

2975 $user さんから

2976 グループ $1

2977 に加入依頼がありました。

2978

2979 承認する:

2980 $urlbase?joingrpadmit+yes+$jss

2981

2982 白紙に戻す:

2983 $urlbase?joingrpadmit+no+$jss

2984 EOF

2985 echo "管理者に加入依頼を出しました。

2986 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

2987 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

2988 }

2989 joingrp() {

2990 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

2991 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

2992 if isgrpowner "$user" "$1"; then

2993 isowner="yes"

2994 elif [ -n "$5" ]; then

2995 isowner="yes"

2996 else

2997 isowner=""

2998 fi

2999 ## err jg:isgrpowner: isowner="$isowner"

3000 if [ -n "$isowner" ]; then

3001 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3002 elif [ x"$2" != x"$user" ]; then # if user is not login user

3003 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3004 return

3005 elif [ x"$3" = x"no" ]; then

3006 : # Do not pursue those who leave

3007 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3008 : # Member can change own email address for the joining moderated group

3009 else # adding user is $user itself

3010 case `getgroupattr $1 regmode` in

3011 moderated)

3012 joingrprequest "$@" # Request only

3013 return

3014 ;;

3015 *)

3016 ;;

3017 esac

3018 fi

3019 qgname=`sqlquote "$1"`

3020 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3021 cond="where gname=$qgname and user='$2'"

3022 if [ x"$3" = x"yes" ]; then

3023 query "replace into grp_mem values($qgname, '$2');"

3024 # Notify joingrp to admin

3025 action="に加入しました。"

3026 if [ -n "$4" ]; then

3027 if msg=`emaildomaincheck "$4"`; then

3028 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3029 'string', '$4', NULL);"

3030 else

3031 echo $msg

3032 fi

3033 else

3034 query "delete from grp_mem_s $cond and key='email';"

3035 fi

3036 if [ -n "$5" ]; then # as ADMIN

3037 # Coming here means newly created group

3038 sql="select case\

3039 when (select count(*) from grp_mem where gname=$qgname)=1\

3040 then (select user from grp_mem\

3041 where gname=$qgname and user='$user')\

3042 else '' end; "

3043 err NewGrpChk: $sql

3044 if [ -n "`query \"$sql\"`" ]; then

3045 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3046 query "replace into grp_adm values($qgname, '$user');"

3047 fi

3048 fi

3049 else

3050 query "begin;

3051 delete from grp_mem $cond;

3052 delete from grp_mem_s $cond;

3053 delete from grp_mem_m $cond;

3054 delete from grp_adm $cond;

3055 delete from grp_adm_s $cond;

3056 delete from grp_adm_m $cond;

3057 end;"

3058 action="から脱退しました。"

3059 fi

3060 smail "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3061 $url?grp+$grid

3062 $user (`gecos $user`)さんが

3063 グループ $1

3064 $action

3065 EOF

3066 }

3067 grp_add_team() (

3068 # $1=grp-rowid $2=team $3...=user-rowid(s)

3069 grp=`getgroupbyid $1`

3070 team=$2; shift; shift

3071 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3072 { echo "BEGIN;"

3073 for user; do

3074 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3075 '$grp',\

3076 (SELECT name FROM user WHERE rowid=$user),\

3077 'team', 'string', '$team');"

3078 done

3079 echo "END;"

3080 } | query

3081 )

3082 grp_rm_team() (

3083 # $1=grp-rowid $2=team $3...=user-rowid(s)

3084 grid=$1

3085 qgrp=$(sqlquote "`getgroupbyid $grid`")

3086 team=$2; shift; shift

3087 [ -z "$grid" -o -z "$team" ] && return

3088 { echo "BEGIN;"

3089 for user; do

3090 echo "DELETE FROM grp_mem_m\

3091 WHERE gname=$qgrp \

3092 AND user=(SELECT name FROM user WHERE rowid=$user)\

3093 AND key='team' AND val='$team';"

3094 done

3095 cat<<-EOF

3096 DELETE FROM blog_s

3097 WHERE rowid=(

3098 SELECT rowid

3099 FROM blog_s a

3100 WHERE key='team'

3101 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3102 AND NOT EXISTS (SELECT * FROM grp_mem_m

3103 WHERE key='team' AND val=a.val -- a.val=team

3104 AND gname = (SELECT val FROM blog_s b

3105 WHERE a.id=b.id AND key='owner')

3106 ));

3107 EOF

3108

3109 echo "END;"

3110 } | query

3111 )

3112 grp_reg_adm() {

3113 # $1=grp-rowid $2...=user-rowid

3114 grid=$1

3115 grp=`getgroupbyid "$1"`

3116 if [ -z "$grp" ]; then

3117 echo "無効なグループIDです" | html p; return

3118 fi

3119 if ! isgrpowner "$user" "$grp"; then

3120 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3121 fi

3122 shift

3123 for urid; do

3124 newadm=`query "select name from user where rowid=$urid;"`

3125 if [ -z "$newadm" ]; then

3126 echo "指定ユーザIDがおかしいようです。" | html p; return

3127 fi

3128 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3129 err ismember $newadm $grp

3130 if ismember $newadm "$grp"; then

3131 # OK, go ahead

3132 getgname="(select gname from grp where rowid=$grid)"

3133 query "replace into grp_adm values($getgname, '$newadm');"

3134 # confirm insertion

3135 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3136 if [ -n "`query \"$sql;\"`" ]; then

3137 echo "追加完了: $newadm" | html p

3138 else

3139 echo "追加失敗($1 $urid)" | html p

3140 fi

3141 fi

3142 showgroup $grid

3143 done

3144 }

3145 dt_rowhack() {

3146 # From: <TR>

3147 # ....

3148 # <TD>rowclass=foo</TD>

3149 # </TR>

3150 # To: <TR class="foo">....<TD>foo</TD></TR>

3151 sed -e '

3152 /^<TR>/ {

3153 :loop

3154 s/\n//

3155 N

3156 /<\/TR>/ {

3157 s/\n//

3158 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3159 n

3160 }

3161 $q

3162 b loop

3163 }'

3164 }

3165 dumptable() {

3166 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3167 # textのフィールドだけ全てダンプにしたほうがいいか

3168 # $DT_VIEW sets link

3169 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3170 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3171 VIEW=${DT_VIEW-replyblog}

3172 if [ -n "$VIEW" ]; then

3173 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3174 fi

3175 sqlfile=$tmpd/dump.sql

3176 : > $sqlfile # ensure to be empty

3177 printf '.mode html\n.header 1\n' > $sqlfile

3178 # $DT_CHLD=ChildTable:BindColumn

3179 if [ -n "$DT_CHLD" ]; then

3180 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3181 cat<<-EOF >> $sqlfile

3182 -- presql

3183 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3184 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3185 EOF

3186 # Speed up counting of new articles

3187 cat<<-EOF >> $sqlfile

3188 -- presql2

3189 DROP TABLE IF EXISTS _counts;

3190 CREATE TEMPORARY TABLE _counts AS

3191 SELECT $_i, count($_i) cnt

3192 FROM $_t GROUP BY $_i;

3193 /* Prepare NEW count table */

3194 CREATE TEMPORARY TABLE _target AS

3195 SELECT b.rowid trowid, b.id

3196 FROM "$2" b JOIN "$2_s" s

3197 ON b.id=s.id AND s.key='owner'

3198 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3199

3200 DROP TABLE IF EXISTS _children;

3201 CREATE TEMPORARY TABLE _children AS

3202 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3203 FROM (SELECT t.trowid, t.id $_i, a.id

3204 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3205 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3206

3207 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3208 DROP TABLE IF EXISTS _news;

3209 DROP VIEW IF EXISTS _news;

3210

3211 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3212 -- INSERT INTO _news

3213 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3214 CREATE TEMPORARY VIEW _news AS

3215 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3216 FROM (SELECT DISTINCT id FROM _target)

3217 a LEFT JOIN

3218 (SELECT $_i, count(ctime) newcnt

3219 FROM _children x

3220 WHERE ctime > coalesce((SELECT time from myacclog

3221 WHERE tblrowid=x.trowid),

3222 '1970-01-01')

3223 GROUP BY $_i) b

3224 ON a.id=b.$_i;

3225 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3226 EOF

3227 # REMOVE next line until 2019/5/1

3228 cntall="(select count($_i) from $_t where $_i=a.id)"

3229 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3230 # REMOVE next assignment until 2019/5/1

3231 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3232 and id in (select id from $_t where $_i=a.id) \

3233 and val > coalesce((select time from myacclog where \

3234 tblrowid=a.rowid),\

3235 '1970-01-01'))"

3236 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3237 cnt="$cntnew as '新着', $cntall as '総数',"

3238 dt_class=" td2r td3r dumpblogs"

3239 fi

3240 # Construct join expression

3241 eav="" scols=""

3242 pk=`gettblpkey $2`

3243 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3244 substr=${substr:-%s}

3245 for col in ${3:-`gettbl_s_cols $2`}; do

3246 valvar=val

3247 case $col in

3248 gecos) scols="$scols${scols:+, }${col#}"

3249 continue ;; # built-in column name

3250 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3251 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3252 case "$as" in

3253 *:*=*) cnd=${as#*:}

3254 h=${cnd%%=*} v=${cnd#*=}

3255 h=`sqlquotestr "$h"`

3256 v=`sqlquotestr "$v"`

3257 valvar="CASE val WHEN $h THEN $v END"

3258 as=${as%%:*} ;;

3259 esac

3260 ;;

3261 *) as=${col} ;;

3262 esac

3263 ss=`printf "$substr" "$valvar"`

3264 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3265 scols="$scols${scols:+, }b.$as"

3266 done

3267 #case author when '$user' then a.rowid else '---' end as ID,

3268 if [ -n "$DT_SQL" ]; then

3269 echo "$DT_SQL"

3270 else

3271 cat<<-EOF

3272 SELECT a.rowid as LINK, $cnt $scols

3273 FROM $2 a LEFT JOIN

3274 (SELECT $pk,$eav,

3275 max(CASE key

3276 WHEN 'owner'

3277 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3278 as gecos

3279 FROM ${2}_s c GROUP BY $pk)

3280 b ON a.$pk=b.$pk $4;

3281 EOF

3282 fi >> $sqlfile

3283 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3284 sqlog<<-EOF

3285 *** SQL-file: $sqlfile ***

3286 `cat $sqlfile`

3287 EOF

3288 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3289 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3290 cat $sqlfile >> tmp/sql

3291 # query ".read $sqlfile" > $tmpd/foo

3292 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3293 cp $tmpd/foo tmp/

3294 err "----- `gdate +%FT%T.%3N` ------------555555"

3295 ## $ddd LINE exists at the end of this function

3296 fi

3297 printf '.mode list\n.header 0\n' >> $sqlfile

3298 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3299 <div>

3300 <div class="dumptable">

3301 <table class="b$dt_class">

3302 `query ".read $sqlfile"`

3303 </table>

3304 </div>

3305 </div>

3306 EOF

3307 ### `query ".read $sqlfile"`

3308 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3309

3310 ## REMOVE THIS!

3311 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3312 }

3313

3314 par2table() (

3315 # copy current parameters of par into destination table

3316 # $1=definition-file

3317 # Using $user and $session

3318 # Return value:

3319 # 0: Stored successfully

3320 # 1: Insufficient fillings

3321 # 2: No permission to modify the record

3322 # 3: Invalid rowid

3323 # 4: SUCCESS to delete

3324 # 5: Stop deletion for lack of confirm check

3325 # 6: Password length too short

3326 # 7: Password mismatch

3327 # 8: Old password incorrect

3328 rowid=`getpar rowid`

3329 if [ ! -e $1 ]; then

3330 echo "テーブル定義ファイルが見付かりません" | html p

3331 exit 1

3332 fi

3333 tbl=${1%.def}

3334 tbl=${tbl##*/}

3335 if [ -n "$rowid" ]; then # Modify existing entry

3336 if [ x"$tbl" = x"user" ]; then

3337 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3338 elif [ x"$tbl" = x"grp" ]; then

3339 sql="select gname from $tbl where rowid=$rowid;"

3340 ##err p2t:grp:q $sql

3341 isgrpowner "$user" "`query $sql`" && rowowner=$user

3342 elif [ x"$tbl" = x"blog" ]; then

3343 # Check if owner in blog_s

3344 blogowner=`getvalbyid blog owner "$rowid"`

3345 if isgrpowner "$user" "$blogowner"; then

3346 rowowner=$user

3347 else

3348 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3349 fi

3350 else

3351 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3352 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3353 fi

3354 ### err rowowner=$rowowner

3355 if [ x"$user" != x"$rowowner" ]; then

3356 echo "他人のレコードはいじれないの" | html p

3357 return 2

3358 elif [ -z "$rowowner" ]; then

3359 echo "指定したレコードはないみたい" | html p

3360 return 3

3361 fi

3362 rm=`getpar rm` cfm=`getpar confirm`

3363 # Editing existent entry

3364 if [ x"$rm" = x"yes" ]; then

3365 if [ x"$rm$cfm" = x"yesyes" ]; then

3366 query "delete from $tbl where rowid=$rowid;"

3367 return 4

3368 else

3369 echo "消去確認のチェックがないので消さなかったの..." | html p

3370 return 5

3371 fi

3372 fi

3373 fi

3374

3375 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3376 if [ -n "$rowid" ]; then

3377 # Update of existing record

3378 for col in `gettblcols $tbl`; do

3379 val=`getparquote $col`

3380 [ -z "$val" ] && continue

3381 ## err query "update $tbl set $col=$val where rowid=$rowid"

3382 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3383 sql="update $tbl set $col=$val where rowid=$rowid;"

3384 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3385 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3386 ## User name cannot be changed with interface provided with this

3387 ## script. But we offer the trigger to change owner user

3388 ## of blog_s table.

3389 #err "select quote($col) from $tbl where rowid=$rowid;"

3390 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3391 cat<<-EOF | query

3392 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3393 -- should use EXCLUSIVE transaction outside of this.

3394 SAVEPOINT par2table;

3395 $sql

3396 update blog_s set val=$val

3397 where key='owner' and val=$old;

3398 RELEASE SAVEPOINT par2table;

3399 EOF

3400 ## XX: DIRTY Hack Ends here

3401 ## We should keep blog's owner as a single column which has

3402 ## foreign key constraint with primary key of grp/user.

3403 else

3404 query "$sql"

3405 fi

3406 done

3407 # Then, set up $pval for further insertion of tbl_s and tbl_m

3408 for col in `gettblpkey $tbl`; do

3409 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3410 pval="$pval${pval:+, }\"$val\""

3411 done

3412 else

3413 # New entry

3414 # Generate values() for primary keys

3415 for col in `gettblpkey $tbl`; do

3416 # Genuine primary keys for _m and _s

3417 val=`getvalquote $tbl $col`

3418 [ -z "$val" ] && continue

3419 pval="$pval${pval:+, }$val"

3420 done

3421 ##err pval=$pval

3422 for col in `gettblfkey $tbl`; do

3423 # args for values() to insertion into master table

3424 val=`getvalquote $tbl $col`

3425 [ -z "$val" ] && continue

3426 formaster=$formaster"${formaster:+, }$val"

3427 done

3428 formaster="$pval${formaster:+, }$formaster"

3429 ## err formaster=$formaster

3430 if [ -z "$formaster" ]; then

3431 echo "項目を全て埋めてください" | html pre

3432 return 1

3433 fi

3434 ## err "replace into $tbl values($formaster);"

3435 query "replace into $tbl values($formaster);"

3436 ## Insertion to master table, done

3437 fi

3438

3439 for kt in s m; do

3440 tb2=${tbl}_$kt

3441 for col in `gettbl_${kt}_cols $tbl`; do

3442 ptype=`getpartype $col "limit 1"`

3443

3444 # First, check update of existing entries in _m

3445 if [ $kt = m ]; then

3446 # sessID|address.1.22|string|Somewhere-x.y.z

3447 sql=""

3448 ##err dots from query "select var from par where var like '$col.%';"

3449 for v in `query "select var from par where var like '$col.%';"`; do

3450 # v=address.1.22

3451 st_rowid=${v##*.}

3452 origcol=${v%%.*} # original column derived from

3453 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3454 ##case `getpartype $v` in

3455 ## err CASE `gettbl_coltype $tbl/$origcol` in

3456 ## err edit flag = `getpar action.$v`

3457 case `getpar action.$v` in

3458 rm)

3459 if [ x`getpar confirm.$v` = x"yes" ]; then

3460 newsql="delete from $tb2"

3461 else

3462 echo "削除確認未チェック" | html p

3463 fi ;;

3464 edit)

3465 case `gettbl_coltype $tbl/$origcol` in

3466 image|document|binary)

3467 file=$tmpd/`getparfilename $v`

3468 ## err type=file=$file

3469 [ -z "$file" ] && continue

3470 bn=`sqlquotestr "${file##*/}"`

3471 bin="X'"$(hexize "$file")"'"

3472 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3473 type=\"file:$ct\"

3474 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3475 cachedir=`getcachedir "$tbl/$rowid"`

3476 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3477 rm -rf $cachedir

3478 ;;

3479 *)

3480 newsql="update $tb2 set val=(select val from par where var \

3481 like '$col.%.$st_rowid')"

3482 ;;

3483 esac

3484 ;;

3485 *) # maybe "keep", do not modify value

3486 continue

3487 ;;

3488 esac

3489 # err newsql=$newsql

3490 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3491 done

3492

3493 if [ x"$bin" = x"NULL" ]; then

3494 ## err repl:normal sql=`echo $sql`

3495 query "$sql

3496 delete from $tb2 where type='string' and val='';"

3497 ## err repl:normal done

3498 else

3499 sqlfile="$tmpd/sqlf.$$"

3500 echo "$sql" > $sqlfile

3501 ## err repl:sqlfile=`ls -lF $sqlfile`

3502 query ".read $sqlfile"

3503 ## err repl:done

3504 fi

3505 # Rest of kt==m: set multiple mode

3506 nr=`getparcount $col`

3507 else

3508 nr=1 # for kt==s, number of records is 1

3509 fi

3510

3511 i=0

3512 while [ $i -lt $nr ]; do

3513 limit="limit 1 offset $i"

3514 i=$((i+1)) # increase beforehand against continue

3515 val=`getvalquote $tbl $col "$limit"`

3516 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3517 ## err $col=$val

3518 bin=NULL

3519 ## err partype$col=`getpartype $col "$limit"`

3520 case $ptype in

3521 file) file=$tmpd/`getparfilename $col "$limit"`

3522 ## err parfile-$col=$file

3523 [ -z "$file" ] && continue

3524 bin="X'"$(hexize "$file")"'"

3525 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3526 type=\"file:$ct\" ;;

3527 "*"*) continue ;; # foreign table

3528 *) type=\"string\" ;;

3529 esac

3530 case `gettbl_coltype $tbl/$col` in

3531 password) # special care for password

3532 # name={password,pswd1,pswd2}

3533 p1=`getpar pswd1 "$limit"`

3534 if [ -z "$p1" ]; then

3535 continue # SKIP password setting, if p1 is empty

3536 else

3537 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3538 ## err pswd=$pswd

3539 if pwcheck "$pswd"; then

3540 if [ x"$p1" = x"$p2" ]; then

3541 case "$p1" in

3542 ??????????*) ;;

3543 *) echo "パスワードは10字以上にしてください。" | html p

3544 return 6;;

3545 esac

3546 val="\"`echo $p1|mypwhash`\""

3547 else

3548 echo "2つの新パスワード不一致" | html p

3549 return 7

3550 fi

3551 else

3552 echo "旧パスワード違います" | html p

3553 return 8

3554 fi

3555 fi

3556 ;;

3557 esac

3558 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3559 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3560 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3561 if [ x"$bin" = x"NULL" ]; then

3562 ## err Normal-query: `echo $sql`

3563 query "$sql"

3564 else

3565 sqlfile="$tmpd/query.$$"

3566 echo "$sql" > $sqlfile

3567 ## err sqlfile=`ls -lF $sqlfile`

3568 query ".read $sqlfile"

3569 fi

3570 ## err p2t done

3571 done

3572 done

3573 done

3574 return 0

3575 ##err donee

3576 )

3577 genform() {

3578 # $1 = form definition file

3579 # $2, $3 (optional)= table name and ROWID

3580 # If $GF_VIEWONLY set and nonNull, output values without form

3581 # If $GF_ARGS set, use it as content-strings in the form

3582 # If $GF_OWNER set, use it as value of name="owner"

3583 # If $GF_STAGE set, use it as value of name="stage"

3584 forms="" hiddens="" rowid=$3

3585 if [ ! -e "$1" ]; then

3586 echo "そのようなデータベースはないようです($2)。" | html p

3587 return

3588 elif [ -n "$2" ]; then

3589 rec=`query "select * from $2 where rowid='$rowid';"`

3590 if [ -z "$rec" ]; then

3591 pk=`gettblpkey $2`

3592 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3593 rec=`query "select rowid from $2 where $pk='$rowid';"`

3594 rowid=$rec

3595 rec=$3

3596 fi

3597 if [ -z "$rec" ]; then

3598 echo "そんなレコードはないみたいね..." | html p

3599 return

3600 fi

3601 fi

3602 if [ -z "$GF_VIEWONLY" ]; then

3603 rm='<input id="rm" name="rm" type="checkbox"

3604 value="yes"><label for="rm">このエントリの削除</label>

3605 <span>ほんとうに消しますよ(確認)!

3606 <input name="confirm" type=checkbox value="yes">はい</span>'

3607 fi

3608 # Image Cache dir

3609 ## err genform: getcache=$2/$rowid

3610 td=`getcachedir "$2/$rowid"`

3611 while IFS=: read prompt name keytype type args; do

3612 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3613 sp="${args:+ }"

3614 form="" val=""

3615 if [ -n "$rowid" ]; then

3616 # err genform2a: Seeking for "$2.$name, type=$type"

3617 rawval=`getvalbyid $2 $name $rowid $td`

3618 val=`echo "$rawval"|htmlescape`

3619 ## err genform3a: getvalbyid $2 $name $rowid $td

3620 ## err genform3b: val="[$val]" type="$type"

3621 fi

3622 if [ -n "$GF_VIEWONLY" ]; then

3623 is_hidden "$2" "$name" && continue

3624 fi

3625 case "$type" in

3626 text*)

3627 cgiform=cgi_multi_$type

3628 if [ -s $td/$name.count -a -n "$val" ]; then

3629 form=`$cgiform $name $td`

3630 val=$(echo "$val"|

3631 while read fn; do

3632 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3633 </td></tr>$nl"

3634 done)

3635 val="<table>$nl$val$nl</table>"

3636 else

3637 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3638 form=`cgi_$type $name "$rawval" "$args"`

3639 fi

3640 ;;

3641 [Rr][Aa][Dd][Ii][Oo])

3642 fh="<label><input type=\"radio\" name=\"$name\""

3643 form="`echo $args|sed -e \

3644 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3645 ;;

3646 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3647 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3648 ;;

3649 [Ss][Ee][Ll][Ee][Cc][Tt])

3650 fh="<select name=\"$name\">$nl"

3651 form=$(for l in $args; do

3652 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3653 done)

3654 if [ -n "$val" ]; then

3655 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3656 fi

3657 form="$fh$form</select>"

3658 ;;

3659 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3660 if [ -s $td/$name.count ]; then

3661 form=`cgi_multi_file $name $td "$args"`

3662 if [ -n "$val" ]; then

3663 hrfb="$myname?showattc+$2_m"

3664 val=$(echo "$rawval" \

3665 | while read fn; do

3666 data=`percenthex "$td/$fn"`

3667 #ct=`cat $td/$fn.content-type`

3668 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3669 ri=`cat "$td/$fn.rowid"`

3670 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3671 #imgsrc="<img src=\"data:$ct,$data\">"

3672 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3673 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3674 done)

3675 fi

3676 else

3677 form="<input type=\"file\" name=\"$name\" $args>"

3678 if [ -n "$val" ]; then

3679 imgs=$(echo "$rawval"\

3680 |while read fn;do

3681 data=`percenthex "$td/$fn"`

3682 echo "<img src=\"data:image/png,$data\">$fn<br>"

3683 done)

3684 form=$form"<br>$imgs"

3685 val=$imgs # 2015-06-15

3686 else

3687 form="<input type=\"file\" name=\"$name\" $args>"

3688 fi

3689 fi

3690 ;;

3691 [Hh][Ii][Dd][Dd][Ee][Nn])

3692 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3693 args="value=\"$GF_STAGE\""

3694 fi

3695 form="<input type=\"hidden\" name=\"$name\" $args>"

3696 prompt='' # Remove prompt

3697 ;;

3698 [Aa][Uu][Tt][Hh][Oo][Rr])

3699 [ -n "$GF_VIEWONLY" ] && continue

3700 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3701 prompt="" ;;

3702 [Oo][Ww][Nn][Ee][Rr])

3703 [ -n "$GF_VIEWONLY" ] && continue

3704 val=${GF_OWNER:-$val}

3705 val=${val:-$user}

3706 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3707 prompt="" ;;

3708 [Uu][Ss][Ee][Rr])

3709 # XXX: is null $user ok?

3710 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3711 [ -n "$GF_VIEWONLY" ] && continue

3712 form="$user"

3713 ;;

3714 [Pp]assword)

3715 [ -n "$GF_VIEWONLY" ] && continue

3716 form="`cgi_passwd`"

3717 val=""

3718 ;;

3719 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3720 [ -n "$GF_VIEWONLY" ] && continue

3721 if [ -z "$rowid" ]; then

3722 val=`genserial`

3723 fi

3724 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3725 prompt="" ;;

3726 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3727 prompt=""

3728 ;;

3729 parent|path|blog*)

3730 prompt=""

3731 ;;

3732 "*"*)

3733 tail=$tail"``"

3734 continue ;;

3735 esac

3736 if [ -n "$prompt" ]; then

3737 if [ -n "${GF_VIEWONLY}" ]; then

3738 form=$val

3739 else

3740 :

3741 fi

3742 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3743 else

3744 hiddens=$hiddens$nl"$form"

3745 fi

3746 done < $1

3747 # enctype="multipart/form-data"

3748 cat<<EOF

3749 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3750 ${rowid:+$rm}

3751 <table class="b $2">

3752 $forms

3753 </table>$hiddens

3754 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

3755 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

3756 EOF

3757 if [ -z $GF_VIEWONLY ]; then

3758 cat<<EOF

3759 <input type="submit" name="sub" value="OK">

3760 <input type="reset" name="res" value="Reset">

3761 EOF

3762 fi

3763 cat<<EOF

3764 $GF_ARGS</form>

3765 $tail

3766 EOF

3767 }

3768 edittable() {

3769 # $1=form-def $2=table $3 rowid

3770 genform "$@"

3771 }

3772 viewtable() {

3773 GF_VIEWONLY=1 genform "$@"

3774 }

3775 showattc() {

3776 # $1=table_m $2=rowid &optional $3=RawFlag

3777 ## err \$1=$1 \$2=$2 \$3=$3

3778 if ! isfilereadable $user $1 $2; then

3779 contenttype; echo

3780 echo "このファイルは管理者のみしか見られません" | html p

3781 putfooter; exit

3782 fi

3783 idir=`umask 002; mktempd` || exit 1

3784 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

3785 bin=$idir/$myname-$$.bin

3786 sql="select quote(bin) from $1 where rowid='$2';"

3787 ## err showattc: sql: $sql

3788 sq $db "$sql" | unhexize > $bin

3789 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

3790 type=${tv%//*} fn=${tv#*//}

3791 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

3792 ct=${type#file:}

3793 case $ct in # all text/* changed to text/plain

3794 text/*)

3795 charset=`nkf -g $bin|cut -d' ' -f1`

3796 case $charset in

3797 ASCII*) charset="" ;;

3798 esac

3799 if [ -z "$3" ]; then

3800 ct="text/html${charset:+; charset=$charset}"

3801 link="?showattc+$1+$2+raw"

3802 nkf -e $bin | htmlescape | nkf --oc="$charset" \

3803 | sed 's,^,<span></span>,' \

3804 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

3805 -D_LINK_="$link" \

3806 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

3807 exit $?

3808 fi

3809 ct="text/plain${charset:+; charset=$charset}"

3810 ;;

3811 esac

3812 contenttype "$ct"

3813 echo "Content-Disposition: filename=\"$fn\""

3814 echo "Content-Length: " `cat $bin | wc -c`; echo

3815 #echo "Content-Type: " ${type#file:}; echo

3816 cat $bin

3817 }

3818 #

3819 # Some default stupid handler on CGI values

3820 #

3821 default_storedb() {

3822 # ARG: $1=table-def-file

3823 # RET: $tbl=table-name, $col=mail-column, $cols=columns

3824 tbl=`basename $1`

3825 tbl=${tbl%.def}

3826 cols="`grep :text $1|cut -d: -f2`"

3827 col=`echo "$cols"|head -1`

3828 vcol=`getpar $col`

3829 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

3830 if [ -n "$vcol" ]; then

3831 par2table $1

3832 else

3833 return 2 # No insertion occurred

3834 fi

3835 }

3836

3837 default_view() { # $1=def-file

3838 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3839 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

3840 default_storedb "$@"

3841 query "select rowid from $tbl order by rowid desc;" \

3842 | while read rowid; do

3843 viewtable $1 $tbl $rowid

3844 done | _m4 -D_TITLE_="$tbl" \

3845 -D_FORM_="`genform $1`" \

3846 -D_DUMPTABLE_="syscmd(cat)" \

3847 $layout/html.m4.html $layout/form+dump.m4.html

3848 }

3849 default_viewtext() { # $1=def-file

3850 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

3851 default_storedb "$@"

3852 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

3853 | _m4 -D_TITLE_="$tbl" \

3854 -D_FORM_="`genform $1`" \

3855 -D_DUMPTABLE_="syscmd(cat)" \

3856 $layout/html.m4.html $layout/form+dump.m4.html

3857 }

3858 default_smail() {

3859 default_storedb "$@"

3860 if [ $? -eq 2 ]; then

3861 _m4 -D_TITLE_="入力" \

3862 -D_FORM_="`genform $1`" \

3863 -D_DUMPTABLE_="" \

3864 $layout/html.m4.html $layout/form+dump.m4.html

3865 return

3866 fi

3867 cond=""

3868 for pk in `gettblpkey $tbl`; do

3869 pv=$(sqlquote "$(getpar $pk)")

3870 cond="$cond${cond:+ and }$pk=$pv"

3871 done

3872 sql="select rowid from $tbl where $cond;"

3873 rowid=`query "$sql"`

3874 ## err smail1 - "$sql" "-> rowid=$rowid"

3875

3876 while IFS=: read prompt name keytype type args; do # Read from $1

3877 val=`getpar $name`

3878 if [ -n "$val" ]; then

3879 text="$text

3880 $prompt

3881 $name=$val

3882 ---------------------------------------------------------"

3883 fi

3884 case "$type" in

3885 image|document|file)

3886 fn="`getvalbyid $tbl $name $rowid $tmpd`"

3887 fns=$(echo "$fn"|while read fn; do

3888 err mv $tmpd/$fn.orig $tmpd/$fn

3889 mv $tmpd/$fn.orig $tmpd/$fn

3890 rm $tmpd/$fn.rowid # Remove cache flag

3891 ## err "`ls $tmpd/$fn`"

3892 echo $fn

3893 done)

3894 files="$files $fns"

3895 ;;

3896 esac

3897 done < $1

3898 ## err FILES=$files "`ls -lF $tmpd`"

3899 subj="from ${REMOTE_ADDR}"

3900 (echo "$url"

3901 echo "への書き込みがありました。"

3902 echo "------"

3903 echo "$text"

3904 ) | (cd $tmpd &&

3905 err LS="`ls -lF`" &&

3906 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

3907 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

3908 echo "以下の内容で送信しました。" | html p

3909 viewtable $1 $tbl \

3910 `query "select rowid from $tbl order by rowid desc limit 1;"`

3911 echo "戻る" | html a "href=\"?\""

3912 }